脱・初心者 Excelの使い方講座【関数編】｜3つの関数を覚えれば「集計」が圧倒的に効率化

この記事の執筆・監修

テクノロジーアンドデザインカンパニー株式会社のCEO。
日本最大級のプログラミング教育のYouTubeチャンネル「キノコード」や、プログラミング学習サービス「キノクエスト」を運営。
著書「あなたの仕事が一瞬で片付くPythonによる自動化仕事術」や、雑誌「日経ソフトウエア」や「シェルスクリプトマガジン」への寄稿など実績多数。

はじめに

この動画では、3つの関数を覚えれば集計を圧倒的に効率化できる関数たちを紹介します（厳密にはもうプラス1つ）。この関数さえ覚えれば、ローデータを貼り付けるだけで集計が終わってしまうものも出てくると思います。また関数を紹介する中で、小技もいくつか紹介していきます。プログラマであってもエクセルを使う機会が多いですよね。エクセル業務の効率化になれば幸いです。また、エクセル初心者の方は、この動画の説明した内容ができるようになれば、あなたはもう脱・初心者です。動画の内容をぜひ習得してみてください。

こんにちはキノコードです。
みなさん、エクセルでの集計作業は効率化したいけど、何をやったらいいかわからない方いませんか？
私は数年間データに関する仕事をしてきました。
プログラミングも使ってきましたが、エクセルも使ってきておりMOSの資格も保有しています。
そんな私が、あらゆる集計は3つの関数さえ覚えてしまえば、たいていのことに対応できると思いこの動画を作りました。
エクセルでの集計作業を1時間効率化できたなら、出勤日数が250日だとすると250時間の効率化です。
つまり、15000分です。15000分をこの30分の動画で解決できればと思い凝縮の30分動画を作りました。
エクセルを使って集計業務を効率化したいと思っている方はぜひご覧ください。
15000分のためのたった30分です。ぜひ最後までご視聴いただけたら幸いです。

レッスンで使ったファイルはこちら

キノクエストでアカウントの新規登録に進み、メール認証を完了します。

ログインした状態（プラン選択画面が表示されます）で下記のボタンをクリックしてください。

ダウンロードはこちら

SUMIFS、COUNTIFS、AVERAGEIFS

私が勧める関数の1つめは、SUMIFS関数です。
SUMIFS関数は、複数の条件を満たすデータの合計を出したいときに使います。
SUMIFS関数の使い方を覚えてしまえば、複数の条件を満たすデータを数えるCOUNTIFSや、複数の条件を満たすデータの平均をだすAVERAGEIFS関数を使えるようになります。
また、合計を出す関数としてSUMIFS関数の他に、SUM関数やSUMIF関数があります。しかし、これらの関数はSUMIFS関数で補うことができるので、覚える必要がありません。
したがって、SUMIFS関数を最強関数の1つにしました。
この動画では、SUMIFS関数を説明した後に、あわせてCOUNTIFS関数、AVERAGEIFS関数も説明します。
この3つさえ覚えていれば、あらゆる条件の集計ができます。
また、半自動化することで人為的なミスがなくなり、時間の効率化が実現できます。
ちなみに、先ほど少しお話ししましたが、複数の条件ではなく、条件が1つのときに使うのが「SUMIF関数」です。他にも、「COUNTIF関数」、「AVERAGEIF関数」があります。
複数の条件で使いたいときは、関数名の最後に複数形の「S」をつけることを覚えておきましょう。

売上管理表の説明

このレッスンではサンプルデータを使って解説していきます。
サンプルデータは、あるアパレル会社の売上管理表です。
この売上管理表の項目は、売上日、社員番号、氏名、支店、商品分類、商品名、単価（円）、数量、売上金額（円）にしました。
いつ、誰が、どこで、何を、いくら、いくつ売り上げたか分かる表になっています。

SUMIFSの説明

それでは、SUMIFS関数を説明します。
SUMIFS関数は、複数の条件に一致するデータだけを合計するときに使用します。
なお、SUMIFS関数は、Excel 2007以降のバージョンで使うことができます。

変数の設定

次に、エクセルのファイル名やシート名を変数に代入していきましょう。
変数にしておくことで、ファイルが変わった時など、ここだけ編集すれば良いことになり楽です。
まず、ファイル名を記述しましょう。
import_fileという変数にします。
イコールを書いてエクセルのファイル名を記述します。
ファイル名は、シングルクオテーションでくくればよいです。
エクセルファイルはjupyter labのファイルがある同じディレクトリに置いておきましょう。
次に、シート名を代入する変数を記述しましょう。
excel_sheet_name01を2019年09月のワークシートを、excel_sheet_name02に2020年09月のワークシート名を代入します。
イコールを書いて、エクセルのシート名を記述します。

SUMの弱点

SUMIFS関数の説明をする前に、SUM関数とSUMIF関数について触れます。
まず、SUM関数についてです。SUM関数は、集計するときに使うことが多いと思います。
しかし、SUM関数で、条件ごとに集計するとなると手間がかかります。
実際に、やってみましょう。
例えば、河野さんの売上を集計したいとします。
その場合、まず、フィルタで並び替えをします。
次に、範囲を設定します。
もし、翌月になって、データが増えた場合、範囲を再設定しなければいけません。
SUMIFS関数を使えば、並べ替えをしなくとも、条件ごとの合計値を算出することができます。
このように、手動の作業をなるべく減らすことで、業務の効率もあがります。
次に、SUMIF関数についてです。SUMIF関数は、SUMIFS関数があるため覚えなくて良いと考えます。
理由は、SUMIF関数は一つしか条件を入れられないためです。
一方、SUMIFS関数は、条件が1つでも、複数でも使うことができます。

SUMIFSの構文

SUMIFS（合計対象範囲, 条件範囲1, 条件1 , 条件範囲2, 条件2, …）
これだけでは分かりにくいと思います。
試しに、氏名ごとの売上金額合計額を求めてみましょう。

SUMIFSの構文（重複を除くリスト）

まず、「M9」のセルに重複を除いた氏名リストを作ります。
「データ」タブをクリックし、詳細設定をクリックして、「フィルターオプションの設定」を開きます。
まず、抽出先の「指定した範囲」を選びます。
次に、リスト範囲を入力します。
リスト範囲の入力欄を選択し、「氏名」をクリックします。
「Shift」＋「Ctrl」＋「↓」を押して、氏名を含むすべての値を選択状態します。
「Shift」＋「↓」で1セルずつ範囲選択すると時間がかかるので、「Shift」＋「Ctrl」＋「↓」を押しましょう。
「Shift」＋「Ctrl」＋「矢印」は、必要な範囲を一気に選択できます。便利ですよね。
ちなみに、「Ctrl」＋「矢印」をすると、値があるところまで囲むことができます。
このように、値がある一番下のセルまでが範囲になりました。
途中に値がないセルがあると、値がないセルの手前まで囲むことができます。
次に、抽出範囲に重複を除くリストの開始セルを記入します。
最後に、「重複するレコードを無視する」にチェックし、「OK」ボタンをクリックします。
簡単に、正確に重複を除くリストができました。

SUMIFSの構文

続いて、氏名の右セルを選択して、SUMIFS関数を入力します。
まずイコールを書きます。そのあとに、SUMIFSを書いて、丸括弧を書きます。
丸括弧の中に、合計対象範囲を書きます。
合計対象範囲は、合計したい値が入力されているセルの範囲のことです。
個人がいくら売り上げたか調べるために、合計対象範囲には「売上金額（円）」欄を選択します。
ここでは、「売上金額（円）」欄を全て選択するため、列番号「J」をクリックします。
「J:J」と表示されます。これですべての列を選択しているということになります。
列全てを選択することで、新しい行が追加されてもSUMIFSで入力する必要がなくなります。
私自身が列全てを選択せずに、大きなミスをしたことがあります。
皆様も必要あれば、列全てを選択する方法を試してみてください。
次に、カンマを書いて、条件範囲1を書きます。
条件範囲1は、検索対象のセルの範囲のことです。
ここには、氏名を含むすべての範囲を指定します。
ここも「売上金額（円）」と同様に、「氏名」欄を全て選択するため、列番号「D」をクリックします。
次に、カンマを書いて、条件1を書きます。
条件1は、条件範囲1のセルを検索するための条件のことです。条件には、数値や文字列を書きます。
条件1は、氏名を選択します。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。

前年比を作成（インデックスの並び替え：sort_index）

しかし、8月25日のデータがここにあり綺麗にならなんでいないです。
したがって、インデックスで並び替えをしましょう。
インデックスで並び替えるにはsort_indexを使います。
データフレームのあとにsort_indexです。
実行してみます。
8月25日が上に来て綺麗に並んでいます。

SUMIFS（条件2つ）

今までは条件が1つだけの場合をみてきました。次に、複数の条件を書いてみましょう。
商品分類ごとに合計を求めるため、商品分類の条件を追加しましょう。
すでに先ほどの記述をコピペします。
「F2」を押して、入力できるようにします。
条件1のあとに、カンマを書いて、条件範囲2を書きます。
「商品分類」欄を全て選択するため、列番号「F」をクリックします。
最後に、カンマを書いて、条件2を書きます。
条件2は、「ボトムズ」にしましょう。
文字列なので、ダブルクォーテーションでくくります。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。

SUMIFS（絶対参照）

次に、セルの参照を使って、集計対象を簡単に変更できる方法を説明します。
今まではボトムスの集計をしていました。
色が変わっているセルにボトムスと入力すればボトムスが集計され、トップスと入力すればトップスが集計されるようにします。
やってみましょう。
まず、この範囲をコピペします。
「F2」を押して、先ほど作った関数を修正します。
「”ボトムス”」を消して、色をついているセルを選択します。
オートフィルを使ってみます。
オートフィルを使ったところが「0」になってしまいましたね。
「F2」を押して、関数の中身をみてみましょう。
相対参照になっているため、下にずれて参照してしまってますね。
絶対参照にすると、オートフィルを使ったときに参照先がずれなくなります。
「F4」を押して、絶対参照にしてみましょう。
それでは、オートフィルを使って、反映させます。
正しく反映されました。
では、「ボトムス」から「トップス」に変更してみましょう。
このように「トップス」の集計ができました。便利ですね。

SUMIFS（プルダウンリスト）

先ほど、「トップス」と手入力しました。
手入力は面倒だし、ミスをする可能性があります。
入力規則を使って選択できるようにしましょう。
ここで「商品分類」という別シートを作ります。
プラスを押して、新しいシートを作ります。
シート名をダブルクリックし、シート名を「商品分類」にします。
次に、このシートに重複を除いた商品分類リストを作ります。
先ほどのフィルターオプションを使ったやりかたではなく、もっとシンプルなやり方で作ってみます。
商品分類のE列をコピー。
商品分類のワークシートに貼り付け。
データのタブ。
重複の削除です。
これで、重複を除く商品分類リストができました。
次に、プルダウンリストを作ります。
「サンプルデータ」シートに戻りたいと思いますが、ワークシートの移動をショートカットでやってみましょう。
windowsの場合、「Ctrl」＋「Page Up」です。macの場合もcontrolとpage upです。Macの場合は、Optionキーと左矢印です。
ちなみに、「Ctrl」＋「Page Down」を押すと、右のワークシートに移動します。Macの場合は、Optionキーと右矢印です。
ショートカットは、マウスに持ち替えることなく操作ができるため、作業時間の短縮になります。
【後から追加する】テーブルを作ってから、プルダウンリストを作る。
さて、「サンプルデータ」シートの「トップス」と入力されているセルを選択します。
次に、「データ」タブをクリックし、「データの入力規則」をクリックします。
「入力値の種類」の中の「リスト」を選択します。
最後に、「元の値」を選択し、「商品分類」シートの商品分類リストを範囲選択します。
「OK」ボタンを選択すると、プルダウンリストの出来上がりです。
プルダウンリストを変えてみましょう。
集計結果が変わりましたね。

SUMIFS（期間指定）

次に、期間を入力をするとその期間の集計ができる方法をみていきましょう。
関数は先ほど使った関数と同じままです。

=SUMIFS(J:J,D:D,L26,F:F,$L$16)
=SUMIFS(J:J,D:D,L26,B:B,”<=”&$L$24)

まず、期間は2月1日以降の売上を集計してみましょう。
K2のセルに2月1日と入力します。
次に、条件範囲2の部分を商品分類E列から、売上日のA列に変更します。列全てを選択しましょう。
そして、条件2の部分「”>=”&$K$2」とします。絶対参照にするのを忘れないようにしましょう。
オートフィルを使って、全体に反映させて完了です。

SUMIFS（期間指定2）

=SUMIFS(J:J,D:D,L26,B:B,”<=”&$L$24)
=SUMIFS(J:J,D:D,L26,B:B,”>=”&$L$33,B:B,”<=”&$M$33)

例えば、上司から、2月分の売上を集計して欲しいと言われた場合はどのようにすればよいでしょうか？
スタートの2月1日の日付はあるので、隣のセルに2月28日のセルを作りましょう。
そして、条件を追加して、2月1日以上、2月28日以下としましょう。したがって、条件範囲3に「A列」を指定します。
そしてカンマを書いて、条件3に「”<=”&$L$2」を書きます。
エンターです。
期間指定して集計することができました。
オートフィルで反映させましょう。
2月と3月の集計をしたい場合は、このように変更すればいいですし、1年間の集計をしたい場合はこのように変更すればよいです。

SUMIFS（売上表作成）

さらに今までの条件に加えて、商品分類ごとに売上表を作ってみましょう。
「商品分類」シートの商品分類を範囲選択し、コピーします。
「サンプルデータ」シートに戻ります。
次に、行と列を入れ替えて貼り付けます
右クリックをおして「形式を選択して貼り付け」をします。
アルファベットの「E」を押して、「行列を入れ替える」を選択します。
「Enter」を押して、貼り付けます。

=SUMIFS($J:$J,$D:$D,$L42,$F:$F,M$41)

F2を押して、関数を修正します。
合計対象範囲は、「売上金額（円）」欄を全て選択します。右方向へオートフィルを使ったときに参照列が変わってしまうので絶対参照に変更しておきましょう。
条件範囲1も氏名の列を選択。これも絶対参照にします。
条件1は、K4になっています。
右にずらした時は固定しておきたい一方、下にずらしたときは移動したいです。したがって、列だけ固定をしましょう。
したがって、Kの前だけ絶対参照のドルマークです。
この絶対参照や相対参照については、別のレッスンにて詳しく説明します。
動画ができたら右上のカードと概要欄にURLを追加します。
話を戻します。売上日を指定しているA列も絶対参照とします。
エンターを押して確定しましょう。
オートフィルをしてすべての行に反映させます。
次に今までの条件に加えてボトムスのみ集計してみましょう。
ボトムスの列にも関数をコピペして反映させます。
F2を押して、関数を修正します。
条件範囲3に商品分類の列を指定。
条件3にボトムスを指定したいので、ボトムスの文字列が記載されているM3のセルを指定しましょう。
下にずらした時には参照先を変更したくないので行は絶対参照にします。
しかし、右にずらした時には参照先を変更したいので列は相対参照にします。
エンターを押して確定しましょう。
上下に反映させてみましょう
集計できているようです。
ボトムスからトップスの3つの列の合計が、合計の列と一致しています。
間違えなく集計ができているようです。

SUMIFS実践7

ここまで、SUMIFS関数を使って、条件を変えたり・増やしたり、いろいろな集計方法を試してきました。
その際、集計の時に列の全体を選択してきました。
もちろん、列のすべてを選択せずに、現在あるデータを範囲指定しても集計することはできます。
でも、なぜ列全体を選択するのでしょうか？
例えば、新しいデータを追加した場合、再度、範囲指定をする必要があります。
しかし、これだと、面倒だし、何より集計ミスが起きる可能性があります。
実際、私は仕事をしていた時に、範囲指定を忘れていて集計ミスをして、お客様や関係者にご迷惑をかけたことがあります。
それ以降、私は、列番号をクリックして、全て選択するようにしました。
ミスを起こさないために私がやっている工夫です。
必要に応じて皆さんも使ってみてください。

ここまでで半分くらいまできました。
SUMIFS関数の理解は深まりましたでしょうか？
キノコードでは、エクセルの機能についてのレッスン、グラフ作成のレッスンもアップしていく予定です。
グラフのレッスンについては、見やすいグラフ、綺麗なグラフを作成するコツも紹介していきます。
また、キノコードではExcelでの集計自動化、グラフも含めたレポートの自動化のレッスンも配信しています。
気に入って下さった方は、ぜひチャンネル登録をお願いします。

COUNTIFSの説明

では、次に進めていきます。
次に、COUNTIFS関数を説明します。
COUNTIFS関数は、指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を求めるときに使います。
なお、SUMIFS関数と同様に、COUNTIFS関数はExcel 2007以降のバージョンで使うことができます。
またSUMIFS関数と同様に、COUNTIF関数は覚えなくてよいと私は考えます。
なぜなら、COUNTIF関数は1つしか条件を入れることができないからです。
一方、COUNTIFS関数は、条件が1つでも、複数でも使うことができます。
それでは、COUNTIFS関数の構文について説明します。

COUNTIFSの構文

COUNTIFS（検索条件範囲1, 検索条件1 [, 検索条件範囲2, 検索条件2,] …）
これだけでは分かりにくいと思います。
試しに、氏名ごとに売上回数を求めてみましょう。
氏名の右セルを選択して、COUNTIFS関数を記述。
丸括弧の中に、検索条件範囲1を書きます。
検索条件範囲1は、検索対象のセルの範囲のことです。
氏名ごとに売り上げた回数を調べるので、検索条件範囲1には「氏名」の列C列を選択します。
次に、カンマを書いて、検索条件1を書きます。
検索条件1は、検索条件範囲1のセルを検索するための検索条件のことです。
検索条件1には、数値や文字列、あるいはセルをしています。
今回は、氏名が入力されているセルを選択します。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。
最後に、オートフィルを使って、全体に反映させて完了です。

COUNTIFS実践1

次に、条件を追加し、100000円以上売り上げた回数を求めてみましょう。
「F2」を押して、入力できるようにします。
検索条件範囲1の部分は同じです。
カンマを書いて、検索条件範囲2を書きます。
「売上金額（円）」を全て選択するため、列番号「I列」を指定します。
次に、カンマを書いて、検索条件2に「”>=”&100000」を書きます。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。
このように、COUNTIFSは、一定の金額以上売り上げた回数を調べるときに便利です。

AVERAGEIFSの説明

次に、AVERAGEIFS関数を説明します。
AVERAGEIFS関数は、指定した範囲内で複数の条件に合致するデータの平均を求めるときに使用します。
なお、AVERAGEIFS関数はExcel 2010以降のバージョンで使うことができます。
それでは、AVERAGEIFS関数の構文について説明します。
AVERAGEIFS（平均対象範囲, 条件範囲1, 条件1 [, 条件範囲2, 条件2,] …）

AVERAGEIFS実践1

AVERAGEIFS関数の構文は、SUMIFS関数の構文とほぼ一緒です。
氏名ごとの売上金額の平均額を求めてみましょう。
イコールを書いて、AVERAGEIFSを書いて、丸括弧を書きます。
丸括弧の中に、平均対象範囲を書きます。
平均対象範囲は、平均を求めたい値が入力されているセルの範囲のことです。
個人の売上平均を調べるたいので、平均対象範囲には「売上金額（円）」のI列を選択します。
次は、条件範囲1です。今回は氏名の条件で絞りたいので、氏名の列のC列を選択。
次に、カンマを書いて、検索条件1を書きます。
検索条件1は、検索条件範囲1のセルを検索するための検索条件のことです。検索条件1には、数値や文字列を書きます。
今回は、氏名が入力されているセルを選択します。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。

AVERAGEIFS実践2

2月の売上の平均を算出してみましょう。
大丈夫です。SUMIFS関数のときに期間を指定したやり方と一緒です。
では、実際にやってみましょう。
SUMIFS関数のところで作ったものをそのままコピペします。
「F2」を押して、入力できるようにします。

’=SUMIFS(J:J,D:D,L26,B:B,”>=”&$L$33,B:B,”<=”&$M$33)
’=AVERAGEIFS(J:J,D:D,L72,B:B,”>=”&$L70,B:B,”<=”&$M70)

SUMIFSをAVERAGEIFSに変更すれば、出来上がりです。
あとは、オートフィルを使って、全体に反映させます。
簡単に期間を指定して集計することができました。

VLOOKUPの説明

次に紹介する関数はVLOOKUP関数です。
VLOOKUP関数は、検索条件に一致したデータを指定した範囲の中から取り出すことができます。
例えば、2つの表があって、一方の表には、社員番号、名前、所属先が書いてあり、もう一方の表には社員番号だけあったとします。
その場合、社員番号をキーに名前や所属をひっぱってくることができます。
SUMIFS関数などと同様に、集計ではよく使う関数です。

VLOOKUPの構文

それでは、VLOOKUP関数の構文について説明します。

=VLOOKUP(検索値,範囲,列番号, 検索方法)

それでは、実際に、社員番号をキーに、氏名と支店を抽出してみましょう。まずは氏名から抽出してみましょう。
初めに、イコールを書いて、VLOOKUPを書きます。
次に、丸括弧を書いて、検索値を書きます。
検索値は、社員番号にします。
次に、範囲を書きます。
範囲は、社員番号から支店を範囲にするため、列番号「B」から「D」を選択します。
「B:D」になりました。
次に、列番号を書きます。
この列番号は、先ほど指定した範囲の何番目にある番号を書きます。
左から数えると、社員番号の列番号「B」で「1」、氏名が「C」で「2」、支店が「D」が「3」です。
今回は氏名を抽出したいので、「2」を書きます。
次に、検索方法を書きます。
範囲から検索値を探すときの検索方法を指定します。
検索方法には2種類あります。
1つ目は「FALSE」です。「完全一致」のデータのみ対象にします。
2つ目は「TRUE」です。「近似値」のデータを対象にします。
今回は、「FALSE」を入力します。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。
次に支店を抽出します。
先ほどのVLOOKUP関数の列番号を三にすればオッケーです。
オートフィルを使って全体に反映させましょう。
完成です。

VLOOKUP（キーを二つ以上でも大丈夫）

次に、氏名から社員IDを抽出する場面を考えてみます。
しかし、データをみてみると、男性の「井上真」と女性の「井上真」がいます。
このように、氏名の中には同姓同名の方がいるため、氏名だけでは一意になりません。
ちなみに、一意とは、すべての値が重複しないことをいいます。
例えば、「1、2、3、4、5」の数字は一意と言えます。
一方、「1、1、2、2、3」の数字は、1と2が重複しているため、一意ではありません。
なお、一意のことをユニークとも言います。
話を戻します。
VLOOKUP関数では、検索値が一意でなければ正しくデータ抽出することができません。
そこで、CONCATENATE関数（コンカチネート）を使って、氏名と性別を結合して、一意になる文字列を作ります。
そして、VLOOKUP関数の検索値のところでも、コンカチネートを使って氏名と性別を組み合わせた一意の文字列を作ります。それをキーに抽出していきます。
それでは、やっていきましょう。
D列にコンカチネート関数を書いて、氏名と性別を結合して、一意になる文字列を作ります。
イコールを書いてコンカチネート。氏名のB2、コンマ、C2です。
これで氏名と性別からなる一意の文字列ができました。
オートフィルを使って反映させます。
VLOOKUP関数の検索値ところにも、カンカチネート関数を書きます。
範囲はD列からE列です。
社員IDを抽出したいので、列番号は2。検索方法はFALSEです。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。
VLOOKUP関数の使い方は、理解できましたでしょうか。
VLOOKUP関数は使うシーンが多いです。何度も実践して慣れていきましょう。

INDEX、MATCHの説明

最後に、INDEX関数とMATCH関数を組み合わせた使い方をみていきましょう。
先ほど説明したように、VLOOKUP関数は便利です。
しかし、VLOOKUP 関数は検索値より右側の列からしか抽出できません。
しかし、VLOOKUP 関数は左端の列からしか検索できません。
また、ピボットテーブルのような表を作ることもできますし、SUMIFSでは数値しか抽出できませんが、文字列も抽出することができます。
今までデータに関する仕事をしてきた私がぜひおすすめする関数です。

INDEX構文

それでは、INDEX関数とMATCH関数を説明していきます。
INDEX関数は、指定された行と列が交差する位置にあるセルを求めることができます。
それでは、実際にやってみましょう。
=INDEX(配列, 行番号, [列番号])
インデックス関数を使って、赤のセルと、青のセルを抽出してみましょう。まずは赤のセルを抽出してみます。
まず、イコールを書いて、INDEXを書きます。
次に、丸括弧を書いて、配列を書きます。
配列には、データの範囲を指定します。
データの最初のセル「A1」を選択し、最後のセル「I219」を選択します。
次に、行番号を書きます。
行番号は、データの範囲の最初の行から数えて2番目なため、「2」を書きます。
次に、列番号を書きます。
列番号は、データの範囲の最初の列から数えて6番目なため、「6」を書きます。
これで「Enter」です。
赤いセルのロングパンツが表示されました。
青のセルを抽出してみます。
これは、10行目で、9列目です。
エンターを教えて表示させてみましょう。

MATCHの構文

次に、MATCH関数を説明します。
MATCH関数は、指定した範囲内で探したい数値や文字列がどの位置にあるか求めることができます。
=MATCH(検査値, 検索範囲 , 照合の種類)
それでは、実際にやってみましょう。
「河野利香」さんが何行目にあるか求めていきます。
まず、イコールを書いて、MATCHを書きます。
次に、検査値を書きます。
検査値は、氏名、今回の場合だと「河野さん」にします。
次に、検索範囲を書きます。
検索範囲は、氏名列を指定します。
最後に、照合の種類を書きます。
照合の種類は、完全一致させたいので、「0」にします。
これで「Enter」を押すと、結果が表示されます。
「河野利香」さんは複数ありますが、一番最初にでてくるのは「2」行目であることが分かりました。

INDEX&MATCH実践1

ここからINDEX関数とMATCH関数を組み合わせて、VLOOKUP関数のように表示してみたいと思います。
氏名をキーに支店を抽出したいと思います。
まず、INDEX関数を書きます。
配列は先ほどと同じで、セル「A1」から「I219」を選択します。
次に、カンマを書いて、行番号を書きます。
行番号は、MATCH関数のところで調べたので、そのまま使います。
最後に、カンマを書いて、列番号を書きます。
支店の列番号は、データの範囲の最初の列から数えて4番目なため、「4」を書きます。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。
VLOOKUP関数と同様の結果がでました。

INDEX&MATCH実践2

次に、VLOOKUP関数ではできなかった、左側を検索してみます。
氏名の左側にある、社員番号を検索してみます。
それでは、さきほど作った関数をコピペします。

=INDEX($B$2:$J$219,MATCH(L108,$D$2:$D$219,0),4)
=INDEX($B$2:$J$219,MATCH(L108,$D$2:$D$219,0),2)

INDEX関数の列番号だけ変えるとできあがります。
社員番号は2列目なので、列番号を「2」にします。
これだけです。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。検索値より左側の値を取得できました。便利です。
オートフィルを使って、全体に反映させて完了です。

INDEX&MATCH実践3

最後に、ピボットテーブルのような表を作ってみます。
それでは、氏名と社員番号と支店がある表を作ってみましょう。
まず、さきほど作った関数をそのまま使います。
次に、行番号のMATCH関数の検索値を絶対参照にします。
下にずらした時に参照先を変えて、右にずらしたときには参照先を変えたくないので、列だけ絶対参照にします。
次に、列番号もMATCH関数にしてみましょう。

=INDEX($B$1:$J$201,MATCH($L142,$D:$D,0),MATCH(M$141,$B$1:$J$1,0))

集計したい表のカラムの文字列を使って抽出したいと思います。検索値はL3とします。下にずらしたときに行の参照先は変えずに、右にずらしたときに参照先を変えるので、行だけを絶対参照にします。
次に、カンマを書いて、検索範囲を書きます。
検索範囲は、「売上日」から「売上金額」を指定します。
こちらも「F4」を押して、絶対参照にします。
最後に、照合の種類を「0」にします。
これで「Enter」を押すと、結果が表示されます。
結果が表示されました。
オートフィルを使って、全体に反映させて完了です。
ピボットのような表ができました。
ピボットとは違い列や行を挿入することが可能です。何かと使い勝手がいいので重宝します。

まとめ

これでレッスンは終わりです。
今回のレッスンも最後まで観ていただき、ありがとうございました。
ここで紹介した関数を使いこなせるようになると業務効率化ができると思います。
またキノコードでは、初心者でもわかるPythonというプログラミング言語のレッスンも配信しています。
Pythonができるようになるとエクセル作業の自動化が可能です。
気になる方はぜひご視聴ください。

キノコードでは、他にもエクセルのレッスンをアップしていく予定です。
そして、私は数年間データに関することやレポーティングを仕事としてきました。皆さんのお役に立つレッスンを上げていきたいと思っています。
気に入ってくださった方は、新着動画が通知されるようになりますので、チャンネル登録お願いします。