Excel集計でのINDEX・MATCH関数が超便利

データの集計に大活躍のExcel。
関数を知れば知るほど「Excelってすげー！！便利ー！！」となりますよね。

この間、とある集計業務を行っていて感動したので備忘録としてまとめます。

やりたかったこと

オブジェクティブグループではQiita-Organizationに参加しており、各社員ごとのいいね・記事投稿数を毎月集計しており、下記のような表があります。

この表を基に2023年9月～2024年8月分の年間での社員ごとのいいね・記事投稿数を集計したかった、というのが目的です。

最初に試したこと

社員ごとの情報を取得したく、横に欲しい数があるので無難にVLOOKUPを使ってみました。
まず年間集計のひな形を作って、社員・月ごとにまとめてSUMしようと頭の中で描いていました。

取れてる、取れてる。
いいじゃん、このままやろう～と2024年10月分を取得しようとしました。

…お気づきになられたでしょうか。

2024年9月分：=VLOOKUP($H4,$C$3:$F$8,3)
2024年10月分：=VLOOKUP($H4,$C$9:$F$11,3)

検索範囲を基の表の月に合わせて変えていますね。
実際の集計表はもっといろいろな情報があり、数も多いです。
月ごとに検索範囲をいちいち指定し直さなくてはいけない…

めんどくさい、無理だ！！！！！！

さて、どうしようか

集計作業をしている時に上司と別件で通話しながら作業をしていました。

私「すいません、画面共有していいですか？めんどくさいですよねこれ…なんかいい方法ないですかね…」
上司『あーーーこれはめんどくさいね！INDEXとMATCH使えば？』

私「あ、聞いたことあるけど使ったことないかもです…ありがとうございます！
　集中してやりたいので通話切ります！ありがとうございました！！！」

そもそもちゃんと何度か使っていれば、最初からINDEXとMATCHを使っていたのでやっぱり慣れと存在を知っておくって必要だなと感じました。

VLOOKUPで「めんどくさいな～絶対なんかあるだろうもっと便利な方法…」と思ってもパッと浮かばなくて、最終的に同じ方法で集計ができたとしても調べるまでに時間がかかっていたと思います。

なので、有識者の意見って本当にありがたいし、素直にめんどくさいんでなんかないですかねって聞く質問の重要性も大切だなと改めて痛感しました。

INDEXとMATCHの合わせ技

• INDEX : 行番号と列番号で指定されるテーブルまたは配列の要素の値を返す（参考）

• MATCH : 範囲 のセルの範囲で指定した項目を検索し、その範囲内の項目の相対的な位置を返す（参考）

上記から、MATCHでいいね数の位置を探してもらって、INDEXで実際の値を取得しようと考えました。

実際の表と式がこちらです。

MATCHでしていること

書式：MATCH(検査値, 検査範囲, [照合の型])
検索値：年間集計表の「社員一覧から取得したい社員H4 ・ 取得したい集計日I3」
検索範囲：月間集計表の「社員名C3:C16・集計日B3:B16」
照合の型：完全一致で0を指定

INDEXでしていること

書式：INDEX(配列, 行番号, [列番号])
配列：月間集計表全体B3:F16
行番号：MACTCHで検索した行番号
列番号：いいね数を取得したいので4列目として4を指定

見やすさ重視のために、セルの絶対参照などは行わない式で記載しましたが実際はできるセルには行って、年間集計表全体にオートフィルして完了。

めちゃくちゃ楽～～～！！！！

話逸れますが、オートフィルのことを口頭で言う時に「それでバーーーっとして」って言ってしまいますのですが同じ方いませんか？

仕上げにIFNAの合わせ技とSUMでの集計

オートフィルして気付いたのですが、新入社員で月間集計表に名前がない場合は#N/Aが返されてしまうんですよね。

単純に月間集計表側にGさんがいないので「いないよ～」とMATCHに判定されます。

逆に新入社員だからか～と一目でわかるのですが、年間集計をSUMで取得したいので全てを数値にする必要があります。
なのでIFNAを使って「#N/Aの場合は0を設定してね」としました。

• IFNA：IFNA 関数は、数式が #N/A エラー値を返す場合に指定した値を返す。それ以外の場合は、数式の結果を返す。(参考)

IFNAでしていること

書式：IFNA(値, value_if_na)
　　　※value_if_na：数式が #N/A エラー値に評価された場合に返す値を指定
値：INDEXとMATCHで設定した値
value_if_na：数値としたいので0を指定

材料は全て揃ったので、月ごとの集計を取りたいので横にSUMして終わり。
月間集計表にない月も指定している例で作った表のためI4:N4には数値を入力しています。
実際に作ったものは全て上記で説明した関数で数値が設定されています。

おわりに

実際いろいろ調べて試行錯誤しながら作りました。
ただ自分が慣れている・使いこなせる関数だけではなくて、使ったことないな～という関数も積極的に使っていくと今後の役に立ちます。

また関数の存在自体を知らなくては冒頭でも書いた通り調べるにも時間がかかるので、定期的に公式などでExcel関数を読んでおくことも大事だなと。

MicroSoft公式Excel関数をブクマしておくと便利です。

オブジェクティブグループでもExcelについての記事も多いので、参考に読んでいただけると嬉しいです。

【Excel】XLOOKUPなら左側からも取り出せる
データ検証に使えるExcel関数まとめ
これからエクセルVBAの勉強を始めたい方へ
元ニートExcel使えなくてAIに泣きつく。
Excelショートカットキー集〜ラクしようぜ〜

また、Xの投稿も平日は毎日行っています！

IT関連の小ネタや便利技から、日常のアニメ・ゲーム布教なども幅広く投稿してるので、ご興味のある方は是非フォロー・いいねをお願いします。