[Excel]関数式小ネタ集【2022/8/31】

記事

エンタメ・趣味

華月（かづき）

2022/06/08 16:32

こんにちは。華月（かづき）です。

この記事では、Excel関数で組んだ数式のうち、意外とネット検索でも中々出てこないような小ネタをきまぐれに書いていこうかなと思います。

※基本的には、スピルしないVer.でも動く数式メインとして書いています。

※単セルCSE、複セルCSE数式を含みます。

■数式の見方＆文言説明

・数式内の【】

対象のセルや範囲を指定してください。（コピペだけでは動きません！）

・スピルしないVer.

Excel2019以前のバージョンではスピルはしません。

office365、Excel2021、Web版、スマホ版などはスピルします。

・CSE数式

数式を入力後、[Ctrl]キーと[Shift]キーを押しながら[Enter]キーで入力を確定させる数式の事。

１つのセルでCSE確定する「単セルCSE（単一セル配列数式）」と、複数のセルを選択してからCSE確定する「複セルCSE（複数セル配列数式）」があります。

※配列数式の詳細についてはMicrosoft公式ガイドラインをご参照ください。

■数式

▼月ごとの平均を求める（作業列なし）

=AVERAGE(IF(MONTH(【日付範囲】)=【月の数字】,【数値範囲】,""))

▼備考
※【日付範囲】に入力されている日付は、日付データとなっている必要があります。
※【日付範囲】と【数値範囲】の行は揃えます。
※スピルしないVer.の場合は、CSE数式で入力します。

▼特定の列で一番下の入力済セルの行番号を調べる

① 関数で表示された空白（""）は無視したい場合

=MAX((【調べたい列】<>"")*ROW(【調べたい列】))

② 関数で表示された空白（""）も入力済として判断したい場合

=MAX((1-ISBLANK(【調べたい列】))*ROW(【調べたい列】))

▼備考①②
※【調べたい列】は、「A:A」「B:B」という風に列全体で指定します。
　処理が重くなりすぎて、少ない範囲で指定したい場合は、
　式の最初と最後の【調べたい列】部分を揃えればOK。
※スピルしないVer.の場合は、CSE数式で入力します。

▼条件に一致するセル位置のセル番地を調べる

=ADDRESS(MATCH(【行条件】,【行範囲(1～)】,0),MATCH(【列条件】,【列範囲(A～)】,0))

▼備考
※画像例：
=ADDRESS(MATCH(A7,A1:A4,0),MATCH(B7,A1:D1,0),1)
※参照の方式を変えたい場合は、最後の引数（数字）を『1～4』で変更します。
　（1または省略⇒『$C$3』／2⇒『C$3』／3⇒『$C3』／4⇒『C3』）
※スピルしないVer.でも、そのまま入力するだけで反映されます。

▼条件に一致するセル位置の内容を一気に表示する

=TEXTJOIN(",",1,IF((【行範囲】=【行条件】)*(【列範囲】=【列条件】),【検索範囲】,""))

▼備考
※画像例：
=TEXTJOIN(",",1,IF((A2:A6=A9)*(B1:F1=B9),B2:F6,""))
※スピルしないVer.の場合は、CSE数式で入力します。
※TEXTJOIN関数は、Excel2019以降のVer.で使用できます。
※検索対象が１つだけしかない場合など、」「,(カンマ)」などの文字で区切る必要がなければ
　CONCAT関数（Excel2019以降で利用可）でも同様に行えます。
※スピルするVer.で結果をセル毎に分けて表示したい場合は、もうひと手間(？)かかります。

▼検索値のセル番地を一気に表示する

=TEXTJOIN(",",1,IF(【検索範囲】=【検索値】,ADDRESS(ROW(【検索範囲】),COLUMN(【検索範囲】),4),""))

▼備考
※画像例：
=TEXTJOIN(",",1,IF(A1:E5=A8,ADDRESS(ROW(A1:E5),COLUMN(A1:E5),4),""))
※スピルしないVer.の場合は、CSE数式で入力します。
※TEXTJOIN関数は、Excel2019以降のVer.で使用できます。
※検索対象が１つだけしかない場合など、」「,(カンマ)」などの文字で区切る必要がなければ
　CONCAT関数（Excel2016以降で利用可）でも同様に行えます。
※参照の方式を変えたい場合は、最後の引数（数字）を『1～4』で変更します。
　（1または省略⇒『$C$3』／2⇒『C$3』／3⇒『$C3』／4⇒『C3』）
※スピルするVer.で結果をセル毎に分けて表示したい場合は、もうひと手間(？)かかります。