知っておくとちょっと便利なExcel関数

Qiitaの記事を書くのは初めてなので、読みにくいところ等あると思いますがご容赦ください。私自身のメモも兼ねて、知っておくと（多分）ちょっと便利なExcel関数をまとめて紹介します。

# はじめに：名前の定義をしよう

名前を付けることにより、定義した名前で範囲指定ができるようになり、作業が格段に楽になります！

やり方は簡単。範囲を選択して、左上のボックスで任意の文字列を入力するだけです。入力した名前の一覧は「数式」の「名前の管理」から確認できます。

#### A列にIDという名前を付ける ####
A3:A15を選択
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/279f8869-8768-b2b4-f811-9892e49c3acc.png" width=50%>

左上の欄に名前を入力
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/2c6995de-9c74-71d5-edc3-b7d05ae15773.png" width=50%>

今回はA列にID、B列に年齢、C列に性別、D列に実験結果、A:D列に実験1という名前を付けました。

# 条件付け：COUNTIF, SUMIF, AVERAGEIF

|関数|機能|
| --- | --- |
| COUNTIF(範囲, 検索条件) | 範囲内に存在するセルのうち、検索条件に一致するセルの<font color=ff2222>**個数**</font>を返します |
| SUMIF(範囲, 検索条件) | 範囲内に存在するセルのうち、検索条件に一致するセルの<font color=ff2222>**合計**</font>を返します |
| AVERAGEIF(範囲, 検索条件) | 範囲内に存在するセルのうち、検索条件に一致するセルの<font color=ff2222>**平均**</font>を返します |

どの関数も、「～IF」を「～IFS」に変えることができます。IFSは条件を並列に書ける関数でpythonでいうelifのような役割です。これを使うことで「30歳以上の男性の数をカウントする」といった2つ以上の条件による絞り込みが簡単にできます。

#### 30歳以上の男性の数を抽出 ####
=COUNTIFS(年齢, “>=”&F2, 性別, G2)
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/4c445d33-0f9a-a996-a1ff-77bc439eb106.png" width=75%>
先ほど名前の定義をしたので、数式内で名前を指定するだけで範囲指定ができています。便利！

※条件の部分は「”>=30”」と直接書いても問題ないですが、「“>=”&F2」のように書くことで閾値を簡単に変えることができ、視認性も向上します。

# 検索：XLOOKUP
|関数|機能|
| --- | --- |
| VLOOKUP(検索値, 範囲, 列番号, [検索方法]) | 範囲の**先頭行**を**上**から検索し、検索値に一致した行の指定列番号の値を返します |
| HLOOKUP(検索値, 範囲, 行番号, [検索方法]) | 範囲の**先頭列**を**左**から検索し、検索値に一致した列の指定行番号の値を返します |
| XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード]) | 検索範囲内を**左上**から検索し、<font color=ff2222>**検索値に対応した戻り範囲**</font>の値を返します。一致するものが見つからなかった場合は[見つからない場合]を表示します |

言葉で説明すると分かりにくいですが、VLOOKUPは縦向きの表（表1）を検索する関数、HLOOKUPは横向きの表（表2）を検索する関数だと考えてください。それぞれ範囲の先頭行（列）に検索値を置いておく必要があり、さらに戻り値が何列（行）目にあるか数える必要があります。

表１（縦向き）
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/64cafb0a-fd85-29b1-12f4-a40e9f1d1215.png" width=45%>
表２（横向き）
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/e47aa348-59f2-170c-88ae-f02f33766626.png" width=55%>

このような小さい表なら上記2つを使っても問題ないですが、100行×50列などの表になると……戻り値は何列目だっけ？と混乱し、何度も数え直すことになります（経験談）

ここで役に立つのが**XLOOKUP**です！

XLOOKUPは検索範囲と戻り範囲を別々に指定します。すなわち、検索値が先頭になくてもいいし、戻り範囲は「何行目」という形ではなく直接指定できるのです。助かる……！
ついでに検索値が見つからなかった場合のメッセージなども表示できます。便利すぎる。

#### 年齢が30歳の人を抽出 ####
=XLOOKUP(F2, 年齢, 実験1, "なし")
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/67d45829-461c-dafc-0daf-3c1f9a6569d5.png" width=80%>
30歳の参加者のデータが抽出されました！今回は一致するデータが見つからなかった場合のメッセージも入力したので、たとえばIDが100番目のデータを検索しようとすると「なし」と表示されます。
 
# 条件に合う全要素を抽出：FILTER

XLOOKUPは縦でも横でも検索してくれる優秀な関数ですが、一つ欠点があります。

そうです。条件に一致したセルが一つ検索できた瞬間、作業を中止してしまうのです。つまり、30歳以上の男性をすべて抽出したくても、最初の一人しか表示してくれません……。

こんなとき役立つのが**FILTER**！
|関数|機能|
| --- | --- |
| FILTER(範囲, 検索条件, [見つからない場合]) | 範囲内を検索し、条件に一致した<font color=ff2222>**範囲**</font>を返します。一致するものが見つからなかった場合は[見つからない場合]を表示します |

FILTERはXLOOKUPと同様に縦または横方向に検索してくれるうえに、条件と一致するすべての行（列）を出力してくれます。

#### 30歳以上の男性を抽出 ####
=FILTER(実験1,(年齢>=F2)*(性別=G2),"なし")
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/01c891da-9313-2222-d805-2a5d57305201.png" width=80%>
できました！ちゃんと30歳以上の男性4人を全て抽出できています。

## おまけ ##

ここでExcelを使い慣れている皆さんは「フィルター」ボタンを使えばよくない？と思ったかもしれません。確かに多くの場合はフィルターボタンと変わりません。しかし、FILTER関数を使うとたとえば次のような時に役立ちます。

- 元の表とは別に抽出結果を表示したい時
- 和集合（AまたはB）を使いたい時

#### 30歳以上、または男性を抽出 ####
=FILTER(実験1,(年齢>=F2)+(性別=G2),"なし")
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3955022/b49d1c51-1ca9-579b-1c6e-857d7713c065.png" width=80%>

このように、和集合であっても問題なくデータを抽出できます。ちなみに、和集合や積集合について書くときは条件式を()で囲わないとエラーになります。うっかりしがちなので注意。

# おわりに

ここまで読んでいただきありがとうございました。独学のため間違っているところやもっと効率的にできるよ！という部分があるかもしれません。もしあったらぜひ教えてください