HSTACKは速い？遅い？実際に試してみた結果
FILTER関数を使う上で、列位置が連続していない場合の方法について、数式ごとの効率（処理速度）を確認してみました。
| № | 数式 | 説明 |
| 1 | FILTER(HSTACK(,,)) | 対象としたい列をHSTACKでつなげてからFILTERする。 |
| 2 | HSTACK(FILTER(),FILTER(),FILTER()) | 1列ごとにFILTERした後で、結果をHSTACKでつなげる。 |
| 3 | FILTER(FILTER()) | 縦の抽出条件でFILTERした後に、横の列をFILTERする。 |
| 4 | FILTER() , FILTER() , FILTER() | 1列ずつFILTERした結果をセルに出力する。 |
テストデータ
以下のテストでは、100万件のデータでテストしました。
ここから、以下のパターンでテストしました。
・2列で10万件の抽出
・2列で1件の抽出
・3列で10万件の抽出
・3列で1件の抽出
・2列で10万件の抽出
・2列で1件の抽出
・3列で10万件の抽出
・3列で1件の抽出
テストに使用したVBA
2列抽出のVBA
Sub test1()
Dim t As Single, s As String, i As Long, f As Long
Dim ary(1 To 10, 1 To 4) As Double
Application.Calculation = xlCalculationManual
s = Range("L1").Value
For i = 1 To 10
For f = 1 To 1
Range("H1:J1").Clear
Application.Calculate
DoEvents
t = Timer
Select Case f
Case 1:
Range("H1").Formula2Local = "=FILTER(HSTACK(A:A,C:C),F:F=""" & s & """)"
Case 2
Range("H1").Formula2Local = "=HSTACK(FILTER(A:A,F:F=""" & s & """),FILTER(C:C,F:F=""" & s & """))"
Case 3
Range("H1").Formula2Local = "=FILTER(FILTER(A:C,F:F=""" & s & """),{1,0,1})"
Case 4
Range("H1").Formula2Local = "=FILTER(A:A,F:F=""" & s & """)"
Range("I1").Formula2Local = "=FILTER(C:C,F:F=""" & s & """)"
End Select
Application.Calculate
ary(i, f) = Timer - t
DoEvents
Next
Next
Range("L3").Resize(UBound(ary, 1), UBound(ary, 2)).Value = ary
End Sub
3列抽出のVBA
Sub test2()
Dim t As Single, s As String, i As Long, f As Long
Dim ary(1 To 10, 1 To 4) As Double
Application.Calculation = xlCalculationManual
s = Range("L1").Value
For i = 1 To 10
For f = 1 To 4
Range("H1:J1").Clear
Application.Calculate
DoEvents
t = Timer
Select Case f
Case 1:
Range("H1").Formula2Local = "=FILTER(HSTACK(A:A,C:C,E:E),F:F=""" & s & """)"
Case 2
Range("H1").Formula2Local = "=HSTACK(FILTER(A:A,F:F=""" & s & """),FILTER(C:C,F:F=""" & s & """),FILTER(E:E,F:F=""" & s & """))"
Case 3
Range("H1").Formula2Local = "=FILTER(FILTER(A:E,F:F="""
& s & """),{1,0,1,0,1})"
Case 4
Range("H1").Formula2Local = "=FILTER(A:A,F:F=""" & s & """)"
Range("I1").Formula2Local = "=FILTER(C:C,F:F=""" & s & """)"
Range("J1").Formula2Local = "=FILTER(E:E,F:F=""" & s & """)"
End Select
Application.Calculate
ary(i, f) = Timer - t
DoEvents
Next
Next
Range("L3").Resize(UBound(ary, 1), UBound(ary, 2)).Value = ary
End Sub
結果の表示
2列で10万件の抽出
| a | ←F列がこの値を抽出 | |||
| 数式 | FILTER( HSTACK(,)) |
HSTACK( FILTER(), FILTER()) |
FILTER( FILTER()) |
FILTER(), FILTER() |
| 1回目 | 1.20312500 | 1.31250000 | 0.96875000 | 1.41406250 |
| 2回目 | 1.21875000 | 1.35156250 | 1.14843750 | 1.42968750 |
| 3回目 | 1.29687500 | 1.38281250 | 1.10156250 | 1.41406250 |
| 4回目 | 1.33593750 | 1.37500000 | 1.09375000 | 1.40625000 |
| 5回目 | 1.28906250 | 1.33593750 | 1.01562500 | 1.25000000 |
| 6回目 | 1.21875000 | 1.28125000 | 1.03125000 | 1.34375000 |
| 7回目 | 1.28906250 | 1.36718750 | 0.96875000 | 1.23437500 |
| 8回目 | 1.18750000 | 1.27343750 | 1.02343750 | 1.34375000 |
| 9回目 | 1.20312500 | 1.27343750 | 1.04687500 | 1.38281250 |
| 10回目 | 1.23437500 | 1.28125000 | 1.02343750 | 1.37500000 |
| 平均 | 1.24765625 | 1.32343750 | 1.04218750 | 1.35937500 |
| 順位 | 2 | 3 | 1 | 4 |
2列で1件の抽出
| aaa | ←F列がこの値を抽出 | |||
| 数式 | FILTER( HSTACK(,)) |
HSTACK( FILTER(), FILTER()) |
FILTER( FILTER()) |
FILTER(), FILTER() |
| 1回目 | 0.52343750 | 0.53125000 | 0.27343750 | 0.39062500 |
| 2回目 | 0.47656250 | 0.53125000 | 0.27343750 | 0.39843750 |
| 3回目 | 0.51562500 | 0.51562500 | 0.28906250 | 0.42187500 |
| 4回目 | 0.50000000 | 0.50781250 | 0.28906250 | 0.39843750 |
| 5回目 | 0.54687500 | 0.51562500 | 0.27343750 | 0.40625000 |
| 6回目 | 0.49218750 | 0.52343750 | 0.27343750 | 0.39062500 |
| 7回目 | 0.50000000 | 0.53906250 | 0.28125000 | 0.42187500 |
| 8回目 | 0.51562500 | 0.53125000 | 0.26562500 | 0.40625000 |
| 9回目 | 0.56250000 | 0.53906250 | 0.29687500 | 0.41406250 |
| 10回目 | 0.53906250 | 0.49218750 | 0.28906250 | 0.40625000 |
| 平均 | 0.51718750 | 0.52265625 | 0.28046875 | 0.40546875 |
| 順位 | 3 | 4 | 1 | 2 |
3列で10万件の抽出
| a | ←F列がこの値を抽出 | |||
| 数式 | FILTER( HSTACK(,,)) |
HSTACK( FILTER(), FILTER(), FILTER()) |
FILTER( FILTER()) |
FILTER(), FILTER(), FILTER() |
| 1回目 | 1.93750000 | 2.21875000 | 1.57812500 | 2.74218750 |
| 2回目 | 2.00000000 | 2.14062500 | 1.60156250 | 2.65625000 |
| 3回目 | 1.86718750 | 2.07031250 | 1.66406250 | 2.60937500 |
| 4回目 | 1.96093750 | 2.27343750 | 1.61718750 | 2.66406250 |
| 5回目 | 2.02343750 | 2.19531250 | 1.56250000 | 2.54687500 |
| 6回目 | 2.01562500 | 2.21093750 | 1.65625000 | 2.59375000 |
| 7回目 | 1.88281250 | 2.09375000 | 1.64062500 | 2.59375000 |
| 8回目 | 1.94531250 | 2.13281250 | 1.49218750 | 2.63281250 |
| 9回目 | 1.90625000 | 2.11718750 | 1.67187500 | 2.75000000 |
| 10回目 | 2.03125000 | 2.08593750 | 1.61718750 | 2.80468750 |
| 平均 | 1.95703125 | 2.15390625 | 1.61015625 | 2.65937500 |
| 順位 | 2 | 3 | 1 | 4 |
3列で1件の抽出
| aaa | ←F列がこの値を抽出 | |||
| 数式 | FILTER( HSTACK(,,)) |
HSTACK( FILTER(), FILTER(), FILTER()) |
FILTER( FILTER()) |
FILTER(), FILTER(), FILTER() |
| 1回目 | 0.76562500 | 0.77343750 | 0.28125000 | 0.50781250 |
| 2回目 | 0.64843750 | 0.75000000 | 0.28125000 | 0.54687500 |
| 3回目 | 0.65625000 | 0.75781250 | 0.32031250 | 0.55468750 |
| 4回目 | 0.67968750 | 0.77343750 | 0.28906250 | 0.53906250 |
| 5回目 | 0.63281250 | 0.76562500 | 0.28125000 | 0.52343750 |
| 6回目 | 0.58593750 | 0.75000000 | 0.27343750 | 0.52343750 |
| 7回目 | 0.64843750 | 0.78906250 | 0.28906250 | 0.52343750 |
| 8回目 | 0.71093750 | 0.78906250 | 0.28906250 | 0.54687500 |
| 9回目 | 0.68750000 | 0.71875000 | 0.27343750 | 0.53906250 |
| 10回目 | 0.68750000 | 0.79687500 | 0.27343750 | 0.55468750 |
| 平均 | 0.67031250 | 0.76640625 | 0.28515625 | 0.53593750 |
| 順位 | 3 | 4 | 1 | 2 |
まとめ
100万件でのテストでこの程度の差異ですので、ほぼ誤差と言っても良いとは思いますが。
それでも、詳細を見れば明らかな差異は見て取れます。
全体の傾向
=FILTER(FILTER(A:E,F:F="a"),{1,0,1,0,1})
不連続な列のFILTERでは、この数式を基本として使うようにすると良いと思います。
今回の結果に対して、簡単に理論的な説明をしておきます。
それでも、詳細を見れば明らかな差異は見て取れます。
- 平均して常に速いのは縦横FILTERであるのは間違いないようです。
FILTER(FILTER())：縦の抽出条件でFILTERした後に、横の列をFILTERする。 - HSTACKしてからFILTERと、FILTERしてからHSTACKの比較
この比較では、微差ではありますが常にHSTACKしてからFILTERの方が速いようです。 - 1列ずつFILTERした結果をセルに出力
出力行数が多いと他に比べて遅くなりますが、出力行数が少ない時は割と速いようです。
=FILTER(FILTER(A:E,F:F="a"),{1,0,1,0,1})
不連続な列のFILTERでは、この数式を基本として使うようにすると良いと思います。
なぜ縦横FILTER（FILTER(FILTER())）がHSTACKよりも速いのか
その理由はデータ処理における基本的な原則に基づいています。
それは、「先にデータ量を減らしたほうが効率的である」という原則です。
横FILTER（数式3）は、最初に行の絞り込みで配列サイズを100万行から10万行などに劇的に削減してから、後の列抽出処理を行うため効率的です。
一方、HSTACK（数式1）は、絞り込み前の100万行を元に新しい配列を再構築するコストが大きく、速度低下を招きます。
その理由はデータ処理における基本的な原則に基づいています。
それは、「先にデータ量を減らしたほうが効率的である」という原則です。
横FILTER（数式3）は、最初に行の絞り込みで配列サイズを100万行から10万行などに劇的に削減してから、後の列抽出処理を行うため効率的です。
一方、HSTACK（数式1）は、絞り込み前の100万行を元に新しい配列を再構築するコストが大きく、速度低下を招きます。
今回の結果を見る限り、少なくとも、むやみにHSTACKすれば良いという事では無いのは分かってもらえたと思います。
HSTACKやVSTACKで件数が多い場合は、この記事を少しだけでも思い出してもらえればうれしいです。
HSTACKやVSTACKで件数が多い場合は、この記事を少しだけでも思い出してもらえればうれしいです。
サイト内の関連記事
REDUCE+VSTACKが遅い理由と解決策
・LAMBDAヘルパー関数について ・REDUCE+VSTACKのサンプル ・REDUCE+VSTACKが遅い理由と解決方法 ・ブロック・リダクションの実例
同じテーマ「エクセル関数応用」の記事
QRコード、バーコード作成の覚え書き
GROUPBY関数が最強すぎる！Excelの集計作業が爆速に！
セル参照を戻り値とする関数
REDUCE+VSTACKが遅い理由と解決策
HSTACKは速い？遅い？実際に試してみた結果
条件付きMEDIAN関数を作る｜LAMBDA関数で汎用〇〇IFSを実現
複数列の直積（デカルト積、クロスジョイン）
フィボナッチ、トリボナッチ、テトラナッチ数列を1数式で作成
表データから複数条件による複合抽出 (横AND/縦OR)
配列を自在に回転させる数式
掛け算(*)を使わない掛け算｜足し算(+)を使わない足し算
新着記事NEW ・・・新着記事一覧を見る
最長連続出現数（ランレングス）の算出｜エクセル練習問題(2025-11-15)
SQL基礎問題11：連続期間の開始月と終了月を抽出｜SQL入門(2025-11-14)
セル数式における「再帰」の必要性｜エクセル雑感(2025-11-10)
掛け算(*)を使わない掛け算｜足し算(+)を使わない足し算｜エクセル関数応用(2025-11-10)
配列を自在に回転させる数式｜エクセル関数応用(2025-11-09)
非正規化（カンマ区切り）の結合と集計：最適な手法は？｜エクセル雑感(2025-11-06)
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計｜SQL入門(2025-11-06)
SQL基礎問題9：特定商品購入者の平均購入金額｜SQL入門(2025-11-04)
SQL基礎問題8：バスケット分析・ペア商品の出現回数｜SQL入門(2025-11-04)
SQL基礎問題7：成績表から各教科の最高点と最低点を抽出｜SQL入門(2025-11-02)
アクセスランキング ・・・ ランキング一覧を見る
1.生成AIパスポート試験 練習問題（四肢択一式）｜生成AI活用研究
2.最終行の取得（End,Rows.Count）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.RangeとCellsの使い方｜VBA入門
7.FILTER関数（範囲をフィルター処理）｜エクセル入門
8.日本の祝日一覧｜Excelリファレンス
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.セルのクリア（Clear,ClearContents）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.