Qiitaの記事を書くのは初めてなので、読みにくいところ等あると思いますがご容赦ください。私自身のメモも兼ねて、知っておくと（多分）ちょっと便利なExcel関数をまとめて紹介します。
はじめに：名前の定義をしよう
名前を付けることにより、定義した名前で範囲指定ができるようになり、作業が格段に楽になります！
やり方は簡単。範囲を選択して、左上のボックスで任意の文字列を入力するだけです。入力した名前の一覧は「数式」の「名前の管理」から確認できます。
A列にIDという名前を付ける
A3:A15を選択
左上の欄に名前を入力
今回はA列にID、B列に年齢、C列に性別、D列に実験結果、A:D列に実験1という名前を付けました。
条件付け：COUNTIF, SUMIF, AVERAGEIF
| 関数 | 機能 |
|---|---|
| COUNTIF(範囲, 検索条件) | 範囲内に存在するセルのうち、検索条件に一致するセルの個数を返します |
| SUMIF(範囲, 検索条件) | 範囲内に存在するセルのうち、検索条件に一致するセルの合計を返します |
| AVERAGEIF(範囲, 検索条件) | 範囲内に存在するセルのうち、検索条件に一致するセルの平均を返します |
どの関数も、「～IF」を「～IFS」に変えることができます。IFSは条件を並列に書ける関数でpythonでいうelifのような役割です。これを使うことで「30歳以上の男性の数をカウントする」といった2つ以上の条件による絞り込みが簡単にできます。
30歳以上の男性の数を抽出
=COUNTIFS(年齢, “>=”&F2, 性別, G2)
先ほど名前の定義をしたので、数式内で名前を指定するだけで範囲指定ができています。便利！
※条件の部分は「”>=30”」と直接書いても問題ないですが、「“>=”&F2」のように書くことで閾値を簡単に変えることができ、視認性も向上します。
検索：XLOOKUP
| 関数 | 機能 |
|---|---|
| VLOOKUP(検索値, 範囲, 列番号, [検索方法]) | 範囲の先頭行を上から検索し、検索値に一致した行の指定列番号の値を返します |
| HLOOKUP(検索値, 範囲, 行番号, [検索方法]) | 範囲の先頭列を左から検索し、検索値に一致した列の指定行番号の値を返します |
| XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード]) | 検索範囲内を左上から検索し、検索値に対応した戻り範囲の値を返します。一致するものが見つからなかった場合は[見つからない場合]を表示します |
言葉で説明すると分かりにくいですが、VLOOKUPは縦向きの表（表1）を検索する関数、HLOOKUPは横向きの表（表2）を検索する関数だと考えてください。それぞれ範囲の先頭行（列）に検索値を置いておく必要があり、さらに戻り値が何列（行）目にあるか数える必要があります。
表１（縦向き）
表２（横向き）
このような小さい表なら上記2つを使っても問題ないですが、100行×50列などの表になると……戻り値は何列目だっけ？と混乱し、何度も数え直すことになります（経験談）
ここで役に立つのがXLOOKUPです！
XLOOKUPは検索範囲と戻り範囲を別々に指定します。すなわち、検索値が先頭になくてもいいし、戻り範囲は「何行目」という形ではなく直接指定できるのです。助かる……！
ついでに検索値が見つからなかった場合のメッセージなども表示できます。便利すぎる。
年齢が30歳の人を抽出
=XLOOKUP(F2, 年齢, 実験1, "なし")
30歳の参加者のデータが抽出されました！今回は一致するデータが見つからなかった場合のメッセージも入力したので、たとえばIDが100番目のデータを検索しようとすると「なし」と表示されます。
条件に合う全要素を抽出：FILTER
XLOOKUPは縦でも横でも検索してくれる優秀な関数ですが、一つ欠点があります。
そうです。条件に一致したセルが一つ検索できた瞬間、作業を中止してしまうのです。つまり、30歳以上の男性をすべて抽出したくても、最初の一人しか表示してくれません……。
こんなとき役立つのがFILTER！
| 関数 | 機能 |
|---|---|
| FILTER(範囲, 検索条件, [見つからない場合]) | 範囲内を検索し、条件に一致した範囲を返します。一致するものが見つからなかった場合は[見つからない場合]を表示します |
FILTERはXLOOKUPと同様に縦または横方向に検索してくれるうえに、条件と一致するすべての行（列）を出力してくれます。
30歳以上の男性を抽出
=FILTER(実験1,(年齢>=F2)*(性別=G2),"なし")
できました！ちゃんと30歳以上の男性4人を全て抽出できています。
おまけ
ここでExcelを使い慣れている皆さんは「フィルター」ボタンを使えばよくない？と思ったかもしれません。確かに多くの場合はフィルターボタンと変わりません。しかし、FILTER関数を使うとたとえば次のような時に役立ちます。
- 元の表とは別に抽出結果を表示したい時
- 和集合（AまたはB）を使いたい時
30歳以上、または男性を抽出
=FILTER(実験1,(年齢>=F2)+(性別=G2),"なし")
このように、和集合であっても問題なくデータを抽出できます。ちなみに、和集合や積集合について書くときは条件式を()で囲わないとエラーになります。うっかりしがちなので注意。
おわりに
ここまで読んでいただきありがとうございました。独学のため間違っているところやもっと効率的にできるよ！という部分があるかもしれません。もしあったらぜひ教えてください