こんばんは。
下記の式を高速化させたいのですが、どうすればよいのか分からないため質問させていただきます。
Sub 全組み合わせ個数()
Dim XX As Long '計算結果を出力する行数
Dim a1 As Long '組み合わせ
Dim a2 As Long
Dim a3 As Long
Dim a4 As Long
Dim aa1 As Long '昇順にした後の組み合わせ
Dim aa2 As Long
Dim aa3 As Long
Dim aa4 As Long
Dim SS As Long
Dim CBN As Long
Dim I As Long
Dim Cnt As Long
Dim CntClm As Long 'COUNTIFSの列数
Dim CntClm1 As Long 'CntClm - 1 した列数
XX = 50 '表が40数行目まであるため50行目以降に計算結果を出力
For a1 = 2 To 43
For a2 = 2 To 43
For a3 = 2 To 43
For a4 = 2 To 43
Range(Cells(50, 2), Cells(50, 2)) = a1
Range(Cells(50, 3), Cells(50, 3)) = a2
Range(Cells(50, 4), Cells(50, 4)) = a3
Range(Cells(50, 5), Cells(50, 5)) = a4
'昇順に並び替え
With ActiveSheet
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range(Cells(50, 2), Cells(50, 5)), Order:=xlAscending
.Sort.SetRange .Range(Cells(50, 2), Cells(50, 5))
.Sort.Orientation = xlSortRows
.Sort.Apply
End With
aa1 = Range(Cells(50, 2), Cells(50, 2))
aa2 = Range(Cells(50, 3), Cells(50, 3))
aa3 = Range(Cells(50, 4), Cells(50, 4))
aa4 = Range(Cells(50, 5), Cells(50, 5))
'当組み合わせの中で数字が重複している場合に除外
SS = 0
If aa1 = aa2 Then
SS = 1
ElseIf aa2 = aa3 Then
SS = 1
ElseIf aa3 = aa4 Then
SS = 1
Else
SS = 0
End If
'他の組み合わせと当組み合わせの数字が同じ場合に除外
If SS = 0 Then
CBN = aa1 & aa2 & aa3 & aa4
For I = 50 To XX
If Range(Cells(I, 1), Cells(I, 1)) = CBN Then
SS = 1
Else
End If
Next I
End If
If SS = 0 Then
'重複がない組み合わせを1列目に記入
XX = XX + 1
CBN = aa1 & aa2 & aa3 & aa4
Range(Cells(XX, 1), Cells(XX, 1)) = CBN
'その組み合わせの数を行数としてCOUNTIFSの数式に使う
For CntClm = 6 To 1743
CntClm1 = CntClm - 1
Cnt = WorksheetFunction.CountIfs(Range(Cells(aa1, 5), Cells(aa1, CntClm1)), Cells(aa1, CntClm), _
Range(Cells(aa2, 5), Cells(aa2, CntClm1)), Cells(aa2, CntClm), _
Range(Cells(aa3, 5), Cells(aa3, CntClm1)), Cells(aa3, CntClm), _
Range(Cells(aa4, 5), Cells(aa4, CntClm1)), Cells(aa4, CntClm), _
Range(Cells(44, 5), Cells(44, CntClm1)), Cells(44, CntClm), _
Range(Cells(45, 5), Cells(45, CntClm1)), "正")
Cells(XX, CntClm) = Cnt
Next CntClm
End If
Next a4
Next a3
Next a2
Next a1
End Sub
上記の数式はこちらのサイトから全組み合わせの算出方法を真似させて頂き(https://rikei-logistics.com/count-by-macro)、算出した数字を行数に見立ててCOUNTIFSでカウントしたものです。
※コメントは私が書いたため、製作者様の数式の意図と違う可能性があります。
組み合わせの総数が10万通り以上になるため元の表の時間軸を横にしており、そこから43C4通りのデータを1737日分算出しています。
ただ上記の式では1通りの1737日分の計算に1秒弱かかるため、単純計算でも1日以上かかってしまいます。
そのため何か処理を高速化できる方法があれば教えて頂きたいと思い、質問させて頂きました。
一応COUNTIFS単体であればDictionaryによって高速化できるようなのですが、上記の数式で再現できるか分かりませんでした。(掲載サイト:https://officedic.com/excel-vba-countifs-dic/)
お手数ですがご回答頂けますと大変助かります。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.4
- 回答日時:
こんにちは
何をなさりたいのか記載がないので、さっぱりわかりませんけれど・・
(コードを見せられても、本当は、何をしたいのかを推測するのは面倒なので・・)
プロシージャ名を見ると
>Sub 全組み合わせ個数()
となっているので、「組み合わせの個数」を求めたいってことでしょうか?
であるなら、いちいち組み合わせを作らなくても、いわゆる C (=組み合わせ)の計算で直接求めるのが簡単かと。
どうやら数値を連結しているようなので、
①12、②34、③123、④4
の異なる数字がある時に、
①&② ・・・1234
と
③&④ ・・・1234
のような結果の重複も省きたいという場合には、かなり面倒になりそうですけれど・・
>そのため何か処理を高速化できる方法があれば教えて頂きたいと思い~
何を計算したいのかわかりませんけれど、まずは、ロジックを見直すのが一番の改善策ではないかと想像します。
VBAはもともと速くはありませんが、セルへのアクセス(=読み書き)には時間がかかります。
いちいちセルに記入して計算する方法ではなく、メモリ上(=変数)で計算するように変更するだけでも、格段に速くなるものと考えられます。
(Sortや重複の排除などには、シートの計算を利用するのもありと思いますが・・こちらも頻度によります)
No.3
- 回答日時:
エクセルは都度、値を描画するために速度が遅くなります。
先頭で描画停止し、終了前に再開したどうですか。
Application.ScreenUpdating = False ' 描画を停止
Application.ScreenUpdating = True ' 描画を再開
セルの出し入れをするのも遅い理由です。
上で改善しない場合は、セルを一旦配列に移し、配列内で処理してセルに戻す様にしたら良いと思います。
ただし、セルには1回しか出し入れしないので有れば、配列にするだけ時間が無駄になるので止めです。
No.1
- 回答日時:
画面を止める
http://officetanaka.net/excel/vba/speed/s1.htm
まず基本としては画面(セルへの書き出しや並び替え)更新を一時的に停止させ最終結果のみ表示されるようにしてみるとか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 請求書自動作成 3 2022/04/24 01:58
- Excel(エクセル) VBAの指示の内容 昨日こちらでご教示頂いたのですが初心者な為、一つ一つの指示が何をやっているのかわ 2 2022/10/25 18:08
- Visual Basic(VBA) VBA処理追加 こちらでご教示頂いたのですが回答完了させてしまいましたのでこちらからまた質問させてく 2 2022/10/27 09:57
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Excel(エクセル) エクセルで同じ数字同士を自動で線で結ぶVBAを教えてください 6 2022/04/26 23:13
- Visual Basic(VBA) VBAで質問ですが、皆さんはどの様に導き出しているのでしょうか? 6 2022/05/03 21:53
- Visual Basic(VBA) 前回ご教授いただいたコードに覚えたてのループ処理で品名りんごAから順に20回for nextでループ 7 2023/01/13 22:01
- Visual Basic(VBA) ExcelVBAでDo Until loopのネスト、IF文を使って一致する物と一致しない物としたい 11 2022/12/24 17:46
- Visual Basic(VBA) Excelで下記のようにマクロを作ったところ、一回目は実行できたのですが、二回目以降「実行時エラー1 1 2022/03/25 08:08
- Visual Basic(VBA) ExcelVBAのマクロについて。 9 2022/05/04 14:50
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
えエクセルで○は1とし△は0.5で...
-
Excel 英大文字小文字を区別せ...
-
格納したデータを配列のように...
-
EXCELで2つの数値のうち大きい...
-
Excel元に戻す方法を教えてくだ...
-
エクセルで二つの数字の小さい...
-
Excelで隣のセルと同じ内容に列...
-
PowerPointで表の1つの列だけ...
-
「B列が日曜の場合」C列に/...
-
エクセルで最初のスペースまで...
-
エクセルのオートフィルタで最...
-
SUMIFとCOUNTIFを合わせたよう...
-
エクセルでオートフィルタのボ...
-
エクセルで、2種類のデータを...
-
エクセルで時刻(8:00~20:00)...
-
VBAで文字列を数値に変換したい
-
A列がない・・・A列が非表示に...
-
2つのエクセルのデータを同じよ...
-
【エクセル】区切り位置で分割...
-
エクセル 文字数 多い順 並...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
えエクセルで○は1とし△は0.5で...
-
VBAで組み合わせ算出やCOUNTIFS...
-
Excel 英大文字小文字を区別せ...
-
1次システムのゲイン特性について
-
UWSCのCALLで呼び出すファイル...
-
急いでいます。論文での数式の...
-
URLの?以降の名称(~~/index...
-
以下の条件に合う関数を教えて...
-
数学Ⅲ
-
EXCELで2つの数値のうち大きい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
2つのエクセルのデータを同じよ...
-
エクセルで、2種類のデータを...
-
エクセルで時刻(8:00~20:00)...
-
エクセルのオートフィルタで最...
-
エクセル 同じ値を探して隣の...
-
エクセルでオートフィルタのボ...
おすすめ情報