VBA初心者です。ご教授ください。

VBA初心者です。ご教授ください。

会社で必要な集計表を作っています。

以下のVBAを使用しているのです。いろいろなネットの記載を見て何とかたどり着きました。
SUMIFSの関数を使い商品毎、店舗ごとに売上数を出しています。1万行くらいあるので時間がかかってしまい苦労しています。スピードをさらに速くすることは可能ですか？SUMIFSではなくVLOCKUPを使うと早くなるということがネットに書いてあったのですがVLOCKUPで複数条件を設定するのは可能ですか？

わかりにくい質問で申し訳ありません。

ご教授いただければと思います。

ちなみに
N列 商品コード
U1:AJ1とAL1:BA1 店舗コードそれぞれおなじもの

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

Sub ⑫店別()

Sheets("home").Select Rows(1).Insert

Sheets("SYS").Select
Range("A1:P2").Copy Sheets("home").Range("U1")

Sheets("home").Select
Range("U1:AJ2").Copy Sheets("home").Range("AL1")



'****************************************
' 店別在庫
'****************************************

Dim LastRow As Long, wS As Worksheet
Set wS = Worksheets("zaiko")
With Worksheets("home")
LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
With Range(.Cells(3, "AL"), .Cells(LastRow, "BA"))
.Formula = "=SUMIFS(zaiko!$G:$G,zaiko!$C:$C,$N3,zaiko!$A:$A,AL$1)"
.Value = .Value
End With
End With


'****************************************
' 店別売上 売上0なら空白
'***************************************

With Worksheets("home")
LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
With Range(.Cells(3, "U"), .Cells(LastRow, "AJ"))
.Formula = "=IF((SUMIFS(uriage!$I:$I,uriage!$C:$C,$N3,uriage!$A:$A,U$1))=0," & """ """ & ",SUMIFS(uriage!$I:$I,uriage!$C:$C,$N3,uriage!$A:$A,U$1))"
.Value = .Value
End With
End With



'****************************************
' 在庫が0の場合はセルがグレー
'***************************************

Dim i As Integer, j As Integer

N = Sheets("home").Cells(Rows.Count, "N").End(xlUp).Row

For i = 3 To N
For j = 21 To 36

If Cells(i, j + 17).Value = 0 Then
Cells(i, j).Interior.ColorIndex = 15
Else

End If

Next j
Next i
End Sub

No.1 回答者： GooUserラック 回答日時： 2017/01/27 14:25

中を確認するのが面倒なのでとりあえず、自動計算を停止するだけでも早くなると思います。

お試し下さい。

「Sub ⑫店別()」のすぐ後に「Application.Calculation = xlManual」
「End Sub」のすぐ前に「Calculate」「Application.Calculation = xlAutomatic」を追加してみてください

No.2 ベストアンサー 回答者： WindFaller 回答日時： 2017/01/27 16:23

こんにちは。

昨日
http://oshiete.goo.ne.jp/qa/9608002.html
でも書いたばかりの話ですが、

Microsoft に、このような話が出ています。
『Excel 2010 のパフォーマンス: 計算パフォーマンスの強化』
https://msdn.microsoft.com/ja-jp/library/office/ …

『Excel 2010 のパフォーマンス: パフォーマンスの問題を最適化するヒント』
https://msdn.microsoft.com/ja-jp/library/office/ …

話の流れは、揮発性関数の話から始まっていますが、昔のExcel(2003以前)の数式の書き方とは違うという趣旨で話が展開しています。リンクのサイトは専門的で、読んでいるだけでは、なんとも想像の付かない部分があります。

ただ、SumIf であろうが、VlookUp であろうが、VBAを専門としている人には、あまり大きく違うようには思えないのです。

そのコードは、長くここの常連でいる人の書き方に似ていますから、酷評は避けますが、その数式を貼り付けるというところで、もうひと工夫が必要だと思います。それ以上は、全面的な改変になってしまいます。

当面は、画面の切り替わりそうなところで、この*/** を入れてください。

Application.ScreenUpdating = False 　'*
With Range(.Cells(3, "AL"), .Cells(LastRow, "BA"))
.Formula = "=SUMIFS(zaiko!$G:$G,zaiko!$C:$C,$N3,zaiko!$A:$A,AL$1)"
.Value = .Value
End With
Application.ScreenUpdating = True '**


Application.ScreenUpdating = False 　'*
With Range(.Cells(3, "U"), .Cells(LastRow, "AJ"))
.Formula = "=IF((SUMIFS(uriage!$I:$I,uriage!$C:$C,$N3,uriage!$A:$A,U$1))=0," & """ """ & ",SUMIFS(uriage!$I:$I,uriage!$C:$C,$N3,uriage!$A:$A,U$1))"
.Value = .Value
End With
Application.ScreenUpdating = True '**

この回答へのお礼

いろいろ参考になりました。もうちょっと勉強してみます。

お礼日時：2017/01/31 10:45

No.3 回答者： ママチャリ 回答日時： 2017/01/28 10:40

計算を効率的に行うために作業列を使ったりしますよね。

今回のケースでは、それと同じように作業ピボットテーブルを作ってみてはいかがでしょうか？集計自体は圧倒的に早くなります。
後は、ピボットテーブルで集計した結果を目的のシートに表示するだけです（とは言っても、1万行×数十列の転記が必要なので、そこそこ時間はかかります）
目的シートへの表示は、GETPIVOTDATA関数を使います。マクロ内でセルに数式を設定していますが、そこをGETPIVOTDATAの式に変えるイメージです。
実際に手作業でやってみると、式のイメージが分かるを思います。