【Excel】クロス集計で数値を取得する最強関数はこれだ！

クロス集計表で任意の数値を取得するという実務でかなりの頻度で出会うこの状況。

集計する関数は色々思い当たるけど、結局最強の関数の組み合わせはどれなんだ？という事で、今回は独断と偏見で最強関数を考えてみました。

はじめに

クロス集計表とは

クロス集計表とは、縦軸と横軸に項目が展開されていて、縦横の項目がクロスする座標それぞれに対応する値が入力されている表です。

つまりこんなの↓

オフィスワークやってると、こんな形の表、腐るほど見ますよね！

評価基準

今回は4つの基準で評価していきます。

式の長さ

式の長さはLEN関数を使って測ります。式が短ければ短いほど加点されます。

関数の数

使っている関数の種類です。使用している関数の種類が少ないほど加点されます。式の長さと比例しそうですが、式が短くても使用している関数が多く、全体的にごちゃごちゃしている事もありますので、評価基準にしています。

処理速度

処理速度はVBAで100回ループ処理をさせ、1回あたりの処理にかかった時間をそれぞれ記録し、そこから算出された平均時間を採用します。

特別点（主観）

筆者の独断と偏見による加点です。見た目の美しさや、一般的に理解されやすい構造になっているかなどを基準に評価していきます。

検証開始

では早速検証していきましょう！

まずは今回使用するデータと関数の紹介から。

使用データ

今回使用するデータはこちら

こんな感じの10万行のデータが3列で計30万ほどのデータを用意しました。

今回はこの表から、C列の10万行目の数値を取得するように全ての関数を設定しました。

エントリー関数

今回エントリーする関数はこちら

ちょっと小さいので、見づらい場合はクリックして拡大してください。

はい、こんな感じで筆者の少ない知識を絞り出して15種類用意しました。

ちょっと無理やり感があるものもありますが、何卒・・・

No1.SUMPRODUCT

ひと昔前まで王道だった関数ですね。式の組み方さえわかれば、ぱっと見でどこから数値を集計してるか分かりやすいので、意外と好きな使い方です。

No2.INDEX,MATCH

言わずもがなの現在の王道の組み合わせですね。実力はいかほど・・・。

No3.OFFSET,MATCH

これも意外と使っている人多いんじゃないですかね？数値部分を参照していない点が、他の関数にはあまりない組み方で美しいですね。

No4.VLOOKUP,MATCH

知名度抜群のVLOOKUP関数を使ってるので、その辺が加点ポイントですかね。

No5.HLOOKUP,MATCH

VLOOKUP関数ときたらこちらも忘れられません。今回は行が10万、列が3と極端に差があるので、そのあたりが両者にどのような影響を与えるでしょうか？

No6.SUM,XLOOKUP,IF

VLOOKUP、HLOOKUPの上位互換のXLOOKUPを使用した組み合わせ。XLOOKUP関数って意外とクロス集計には向いてない？何か他にいい組み合わせありそうな気がする・・・。

No7.XLOOKUP,CHOOSE,MATCH

XLOOKUP関数を使用してもう一つ。ここらへんから少し無理やり感が出てきてますかね(^^;

No8.FILTER

以外にFILTER関数一つで集計可能なんですよね。式もシンプルだし、意外と上位になりそうな気がしてます。

No9.FILTER,HSTACK,SUMIF

各列をSUMIFして、HSTACKで結合して、最後FILTERで絞ってます。なんか、FILTER関数だけでできるなら、それでいいやんって感じですね。

No10.INDIRECT,ADDRESS,MATCH

これも数値部分を参照しないタイプですね。INDIRECTって処理速度はどうなんでしょう？

No11.INDEX,SUM,SEQUENCE,ROWS,COLUMNS

ん～、ここまで関数を使用していると、解読する気にならないですね。今回は10行、3列固定なので、正直ROWS、COLUMNS使わなくてもって感じなのですが、一応行列が増減しても対応できるように使用しました。

No12.VLOOKUP,SORT,MATCH

処理速度向上を目指して、VLOOKUP関数の第4引数を「TRUE」にしてみました。が、そのためにSORT関数を組み入れているので、あまり意味がないかもしれません・・・。

No13.TOCOL,INDEX,MATCH,SUBSTITUTE

関数が長くなりすぎてLET関数で少し省略してますが、どうせ上位にはいかなそうな関数なので、ずるいとか言わないでください。

No14.INDEX,TOCOL,SUBSTITUTE,SWITCH

今回唯一のタイトル行列を参照しない関数という事で、希少性の加点くらいはしておくか・・・。

No15.名前の定義

なんと関数は使っていません！とは言っても、名前の定義を使っているので、ぶっちゃけずる・・いや同じ土俵ではない感じがしますね。

名前の定義を使った集計の補足

今回の主旨とはずれるので、細かい説明は割愛しますが、Excelは2つ以上の範囲を半角スペースで繋げると、重複する範囲の値を返します。

たとえば画像の場合、数式は「6」を返します。

今回はこの仕組みと名前の定義を利用した方法になります。

ちなみに名前の定義の登録方法については【Excel】名前の定義を一括登録する方法をご覧ください。

部門順位

それではまずは各基準ごとのランキングをご覧ください。

式の長さ

「名前の定義」が圧倒的ですね・・・。とは言え、「名前の定義」は例外とするとその次に短いのが「VLOOKUP,MATCH」となりました。

王道の「INDEX,MATCH」は9位と、下位グループになっていますね。

関数の数

FILTER関数、SUMPRODUCT関数が使用関数1つだけという事で1位となりました。

使用関数2つで同率2位が4つで7位までが埋まるという中々ハイレベルな戦いとなり、使用関数3つでもかなり低い評価となりそうです。

処理速度

まさかの名前の定義が3冠をとってしまいました(^^;

ただ、注目すべきは2位の「INDIRECT,ADDRESS,MATCH」です。筆者はINDIRECT関数は処理が重そうなイメージを勝手に持っていましたが、検索系の関数より処理が軽いことがわかりました。

そして、検索系の関数より四則演算系の関数の方が処理が重い傾向にあるんですかね？

総合順位

それでは総合順位を発表します。

特別点（主観）を加味した結果はこちら。

特別点では見た目のすっきりさや、数値部分を参照しないという希少性を評価し、「OFFSET,MATCH」を最高加点としました。

その他、独創性や見た目のすっきりさを基準に独断と偏見で適ｔっ・・・厳正に審査しました。

という事で、総合順位1位は「名前の定義」、ワークシート関数のみだと7種類の関数が同率1位になってしまいましたね・・・。

もう少し深掘る

ん～、なんかすっきりしない結果なので、もう少し考えてみます。

点数を5段階ではなく、順位に紐づく加点形式に変更（1位：15点～15位：1点）。

さらに、やはり関数を評価するのに最重要なのは「式の長さ」と「処理速度」でしょ！ってことで、その2つに絞って評価しなおしてみました（はじめからそうしろ）。

その結果がこちら。

名前の定義は相変わらず1位ですが、2位は式の長さで点数を稼いだ「VLOOKUP,MATCH」、3位は突出したものは無いものの、平均的に点数を稼いだ「FILTER」でした。

ちなみにハイライトが付いているのは項目ごとの最高点を取った関数の組み合わせです（「名前の定義」除く）。

まとめ

はい、という事で「名前の定義」が1位となりました。

王道の「INDEX,MATCH」は思いのほか順位が伸びませんでしたね。

今回は10万行×3列のデータということで、極端に縦にのびたデータを使用していたので、縦横比が変わったら結果も変わってくる可能性がありますし、他にもデータがきれいに並んでいない場合などでも違う結果が出るかもしれないので、あくまで今回の条件での結果という事でご理解ください。

とは言え今回の結果をみて個人的には「式の長さ・処理速度・見た目のわかりやすさ」の観点から「OFFSET,MATCH」が推しになりました。

みなさんの推しはどれですか？

業務代行始めました！

 エクセルでの業務を効率化するツールを作りたいけど、イメージ通りに作れない・・・

 業務で使っている既存のファイルが使いにくいけど、どう改修すればいいかわからない・・・

 エクセルを使った業務は苦手なので、そもそも誰かに代行してほしい・・・

 後から自分でもメンテナンスしたいから、マクロとか高度な機能は使わないで欲しい・・・