これだけは覚えておきたい！使いこなせると1歩先に進めるExcel関数

【Excel これだけは覚えておきたいシリーズ】
Vol.1　これだけは覚えておきたい！Excelのショートカットキー9選～入門編～

Vol.2　これだけは覚えておきたい！Excelで見やすい資料を作るための3つのコツ

 Vol.3　これだけは覚えておきたい！Excelで画像入りの表をきれいに見せる3つのコツ

 Vol.4　これだけは覚えておきたい！Excelで複数の画像を一括処理する方法

 Vol.5　これだけは覚えておきたい！ビジネスで必須のExcel関数4選

前回の「これだけは覚えておきたい！ビジネスで必須のExcel関数4選」では、Microsoft Excel(以下、Excel) で使う関数の中でも基礎的なものを紹介しました。

今回は応用編ということで、使いこなせると仕事の効率がぐっと向上する2つの関数を紹介します。

【この記事の内容】

・小計がある表を作るときに便利な”SUBTOTAL”　(小計＝とある一部の範囲の合計値)
・ある特定条件に合致したデータを抽出する “VLOOKUP”

小計がある表を作るときに便利な”SUBTOTAL”

まずはSUBTOTALの式を見てみましょう。

= SUBTOTAL ( 集計方法 , 参照1, 参照2 …)

“集計方法”というのは、平均や合計などの機能を指定する箇所です。主に使う集計方法は「1…AVERAGE（平均）」「9…SUM（合計）」の2種類です。

たとえば、集計方法に「9」を指定するとSUMと同じ機能を使えるようになります。

“参照”は集計方法で計算する範囲のことです。「A1:A10」のように範囲指定できますし、「A1,A3,A5」と個別に指定することもできます。

それでは具体例でSUBTOTALの使い方を見てみましょう。今回は東北エリアと関東エリアの売上を小計して、最終的にすべての項目を合計します。

（1）まずは”集計方法”を設定します。今回は小計を出すため「9…SUM（合計）」を選択します。

(２)次に”参照”を設定します。東北エリアの小計を出しますので「D3:D5」を選択します。

（3）すると、東北エリアの小計が出ました。関東エリアも同じ要領で小計を出しましょう。

さて、ここで疑問が浮かんでいるのではないでしょうか。そう、ここまでの作業はすべて”SUM”でやればいいことではないかということです。確かに作業がここまでならばSUMで十分です。

しかし、SUBTOTALが効果を発揮するのはここからです。それでは合計を出してみましょう。

（4）小計と同じように、”集計方法”を「9」とします。

（5）次に参照ですが、「D3:D10」を指定しましょう。D6とD10も合計されて間違った数値が出ると思うかもしれませんが、心配はいりません。

ここがSUBTOTALのすごいところで、SUBTOTALはSUBTOTALを使っているセルを計算上無視してくれます。

つまり、D6とD10は計算に含まれないことになります。これはSUMではできないことです。

SUMを使った計算と見比べてみましょう。D列がSUBTOTALでE列がSUMです。

SUMを使った場合ですと、小計も含んで合計してしまいます。そのため、正しい計算結果になっていません。

したがって、小計を含んだ表を作る際にはSUMよりもSUBTOTALを使ったほうが簡単かつ正確な表を作ることができるのです。

もちろん、範囲指定の工夫次第で、SUMでもSUBTOTALで作った表と同じにすることはできます。

ただし、設定が細かくなるため、エリアや店舗を増減させるなどの設定変更を後で行った場合に調整が難しくなります。

そのため、小計を使う場合はSUBTOTALを使ったほうが便利です。

【無料ダウンロード可能】あなたのPCスキル診断シート

ある特定条件に合致したデータを抽出する”VLOOKUP”

VLOOKUPはExcel関数の中でも分かりづらいことで有名です。しかし、使いこなせると作業効率が一気に改善しますので、ぜひマスターしておきたい関数のひとつです。

VLOOKUPは「ある特定条件に合致したデータを抽出する関数」です。次の画像でざっくりと考え方をまとめてみました。

画像では見切れてしまっていますが、リストのデータは1万件ぐらいあると考えてください。

それほど膨大なデータ量だと、特定の条件に紐付いた情報を地道に目で見つけるのは大変です。そこで”VLOOKUP”の出番です。

それではVLOOKUPの式を見ながら、実際に関数を作ってみましょう。今回はG4に「松村尊則」の点数を抽出するように設定します。

VLOOKUPの式は次のとおりですので、順番に説明していきます。

= VLOOKUP ( 検索値 , 範囲 , 戻り値を含む列番号 , 検索方法 )

（1）まずは”検索値”を設定します。今回はG3セルの名前を検索したいので「G3」と設定します。セル名で指定する必要はなく「”松村尊則”」といった文字列や「5」といった数値で指定することも可能です。

（2）次に”範囲”を指定します。注意すべきなのは、「検索条件」と「抽出したい情報」の両方を含んだ範囲選択をしなければならないことです。

今回はC列が「検索条件」、D列が「抽出したい情報」となっていますので、その両方を含むように指定する必要があります。そのため、今回は「C3:D10000」と指定しています。

（3） “列番号”とは、「抽出したい情報」の列を指定する項目になります。今回は抽出したい情報がD列のテスト1なので”2”を指定することになります。

「”4”じゃないの？」という疑問があるかと思いますが、”2”です。なぜなら、列番号は（2）で設定した”範囲”の中で何列目なのかを指定しなければならないからです。

今回設定した範囲はC～D列ですから、C列が列の始まりで列番号1、D列は列番号2ということになります。

列番号の指定はVLOOKUPを使うにあたってとても分かりづらい点です。もしうまく情報が抽出できていなかったら、列番号を間違えていることがとても多いので、一度見直してみてください。

（4）最後に”検索方法”を設定します。今回は完全一致(表記などが全く同じこと)の条件で抽出したいので”FALSE”を設定します。

ちなみに”TRUE”は近似一致(※)という条件のときに設定しますが、使用される場面も多くないのであまり気にしないで問題ないでしょう。
※検索条件に完全に合致したものがない場合、その1つ前の値を表示する