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

前回の「これだけは覚えておきたい!ビジネスで必須のExcel関数4選」では、Microsoft Excel(以下、Excel) で使う関数の中でも基礎的なものを紹介しました。今回は応用編ということで、使いこなせると仕事の効率がぐっと向上する2つの関数を紹介します。

スタディ スキルアップ
これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると1歩先に進めるExcel関数
  • これだけは覚えておきたい!使いこなせると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(合計)」を選択します。



(2)次に”参照”を設定します。東北エリアの小計を出しますので「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を使ったほうが便利です。

ある特定条件に合致したデータを抽出する”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つ前の値を表示する



このような流れで入力を行えれば、G3で指定されている「松村 尊則」の点数が表示されます。



抽出したいデータが少ない場合は[Ctrl]+「F」で地道に探していく方法もありますが、数が多い場合などはこのVLOOKUPを使用すると作業が効率化するでしょう。

まとめ:難しい関数に少しずつ慣れていこう


それでは、この記事をまとめます。

・小計を表に含む場合は”SUM”ではなく”SUBTOTAL”を使ったほうが便利
・“VLOOKUP”は複雑だけど、使いこなせれば仕事がとても捗る関数


今回はExcel関数をあまり使ったことのない人にとっては難しい内容だったかもしれません。

しかし、ひとつずつゆっくり紐解いていくと、実はそこまで難しいことはしていないので、まずは使ってみることが大切です。

ぜひ積極的に試してみて、作業の効率化を図ってみてください。

Excel(エクセル)記事まとめ

覚えておけば残業知らず!Excel時短記事まとめ

見づらい資料からサヨナラ!Excel資料作成記事まとめ

覚えて仕事を効率化!Excel関数記事まとめ

PowerPoint(パワーポイント)記事まとめ

Word(ワード)記事まとめ
《編集部》

関連記事

    page top