【Excel】関数の組み合わせ方｜答えを次の関数に渡すだけ。仕事で使える7つの例

IF関数もCOUNTIF関数も、1つずつなら書ける。

でも、2つが重なった数式を見ると、急に読めなくなる。

=IF(COUNTIF($C$2:C2,C2)>1,"重複","")

こういう式ですね。

組み合わせ方の考え方は、じつはひとつだけなんです。

ある関数で出した答えを、次の関数の材料に使う。

COUNTIFで数えた数を、IFで判断する。MATCHで探した位置を、INDEXに渡す。どの組み合わせも、この形でできています。

この記事では、仕事でそのまま使える組み合わせを7つ紹介します。前半の4つはどのバージョンのExcelでも使える関数、後半の3つはMicrosoft 365の新しい関数です。

やりたいことの見出しだけ見て、必要なところから読んでください。

1. 名簿の重複登録を見つけたい（IF＋COUNTIF）
2. 電話番号から名前を調べたい（INDEX＋MATCH）
3. 氏名から姓だけを取り出したい（LEFT＋FIND）
1. #VALUE!が出たとき
4. 名簿の一番下の人を表示したい（OFFSET＋COUNTA）
5. 1万円以上の出費を1つのセルにまとめたい（TEXTJOIN＋FILTER）
6. 3か月分の家計簿から食費だけを取り出したい（FILTER＋VSTACK）
7. 金額の大きい出費トップ3を出したい（TAKE＋SORT＋VSTACK）
おまけ：長くなった数式はLET関数で整理する
組み合わせるときのコツ
まとめ

1. 名簿の重複登録を見つけたい（IF＋COUNTIF）

会員名簿を管理していると、同じ人を2回登録してしまうことがあります。

電話番号がC列に入っているとして、D2にこう入れて下までコピーします。

=IF(COUNTIF($C$2:C2,C2)>1,"重複","")

先にCOUNTIFだけを見てください。

=COUNTIF($C$2:C2,C2)

範囲の始まりのC2にだけドルマークを付けています。こうすると、下にコピーしたときに始まりは動かず、終わりだけが伸びていきます。3行目ならC2:C3、15行目ならC2:C15。

つまり、先頭から自分の行までに、同じ電話番号が何件あるかを数えているんですね。

結果は、ほとんどが1になります。自分しかいない、ということです。2以上になった行は、自分より上に同じ番号がある行です。

この数字をIFに渡して、1より大きければ「重複」、そうでなければ何も表示しない（ダブルクォーテーション2つ）としたのが、最初の数式です。

ひとつ注意があります。家族で同じ電話番号を使っている場合もあるので、「重複」と出た行は、名前や住所と合わせて確かめてから消してください。

2. 電話番号から名前を調べたい（INDEX＋MATCH）

電話がかかってきたときに、番号からお客様の名前を出したい。

検索というとVLOOKUPを思い浮かべる方が多いと思います。でもVLOOKUPは、検索する列より左にある列は取り出せません。今回は電話番号がC列、氏名がB列。名前のほうが左にあります。

こういうときに使えるのが、INDEXとMATCHの組み合わせです。I2に電話番号を入れて、こう書きます。

=INDEX(B:B,MATCH(I2,C:C,0))

これも内側から見ます。

=MATCH(I2,C:C,0)

MATCHは、探しているデータが範囲の何番目にあるかを返す関数です。最後の0は「完全に一致するものを探す」という指定です。動画の名簿では、8が返りました。C列の8番目にその番号がある、ということです。

でも、知りたいのは位置ではなく名前ですよね。

INDEXは、範囲の何番目にあるデータを取り出す関数です。=INDEX(B:B,8) なら、B列の8番目の名前が出ます。

この8を自分で打つ代わりに、MATCHの答えをそのまま入れたのが最初の数式です。MATCHで場所を探して、INDEXでその場所の名前を取り出す。役割分担になっています。

Microsoft 365なら、XLOOKUP関数1つでも同じことができます。

3. 氏名から姓だけを取り出したい（LEFT＋FIND）

「山田太郎」から「山田」だけを取り出したい。

左から文字を取り出すのはLEFT関数です。=LEFT(B2,2) なら左から2文字で「山田」になります。

でも、姓の文字数は人によって違います。2文字の人もいれば、3文字の人もいる。

そこで、姓と名のあいだの空白を手がかりにします。

=LEFT(B2,FIND("　",B2)-1)

FINDは、探している文字が何文字目にあるかを返す関数です。「山田　太郎」なら空白は3文字目なので、3が返ります。姓はその1つ手前までなので、1を引いて2。この2を、LEFTの文字数に渡しています。

これなら姓が2文字でも3文字でも、空白の位置に合わせて取り出してくれます。

#VALUE!が出たとき

空白の種類が合っていないと、#VALUE!エラーになります。

Excelでは、全角スペースと半角スペースは別の文字です。名簿が全角スペースで区切られているなら、数式の中も全角スペースにします。半角で区切られているなら半角です。

名簿の中で全角と半角が混ざっていると、一部の行だけエラーになります。

4. 名簿の一番下の人を表示したい（OFFSET＋COUNTA）

会員が何人いて、最後に登録されたのは誰か。

人数は、空白でないセルを数えるCOUNTA関数で出せます。B列の1行目は「氏名」という見出しなので、1を引きます。

=COUNTA(B:B)-1

この人数を、OFFSET関数に渡します。

=OFFSET(B1,COUNTA(B:B)-1,0)

OFFSETは、基準のセルから何行・何列ずれた場所を参照するかを指定する関数です。会員が20人なら、B1から20行下＝B21。列はずらさないので0です。

人数を数式で出しているので、会員を1人追加すれば、人数は21になって、表示される名前も新しい人に変わります。数式は触りません。

ただし、この方法は途中に空白の行がないことが前提です。空白行があると、その分だけ数がずれて、一番下の人より上を指してしまいます。

ここから後半です。Microsoft 365の新しい関数を使います。

前半は「数」や「位置」を次の関数に渡していましたが、後半は表そのものを渡していきます。考え方は同じです。

例として、8月・9月・10月とシートが分かれた家計簿を使います。各シートは、A列が日付、B列が費目、C列が内容、D列が金額、2行目から11行目まで10件ずつ入っている、という想定で書いています。

5. 1万円以上の出費を1つのセルにまとめたい（TEXTJOIN＋FILTER）

10月の出費のうち、1万円以上のものは何だったか。

条件に合うデータだけを取り出すのはFILTER関数です。

=FILTER(C2:C11,D2:D11>=10000)

金額が1万円以上の行の「内容」だけが取り出されます。

ただ、結果は縦に並びます。1つのセルにまとめたいときは、TEXTJOIN関数を外側にかぶせます。

=TEXTJOIN("、",TRUE,FILTER(C2:C11,D2:D11>=10000))

TEXTJOINは、複数の文字を区切り文字でつなげる関数です。1つ目が区切り文字（今回は読点）、2つ目のTRUEは「空のセルは飛ばす」という指定、3つ目がつなげたい文字。そこにFILTERの結果を渡しています。

月ごとの振り返りメモを作るときに便利です。

6. 3か月分の家計簿から食費だけを取り出したい（FILTER＋VSTACK）

シートが月ごとに分かれていると、3か月分をまとめて絞り込めません。

先に、表を1つにまとめます。複数の表を縦に積み重ねるのがVSTACK関数です。

=VSTACK('8月'!A2:D11,'9月'!A2:D11,'10月'!A2:D11)

8月・9月・10月の30件が、縦に並びます。

これをFILTERに渡して、費目が食費の行だけにします。

=FILTER(VSTACK('8月'!A2:D11,'9月'!A2:D11,'10月'!A2:D11),VSTACK('8月'!B2:B11,'9月'!B2:B11,'10月'!B2:B11)="食費")

長く見えますが、VSTACKが2回出てくるだけです。

1つ目のVSTACKは、取り出す元の表（3か月分）。2つ目のVSTACKは、条件に使う費目の列（3か月分）。その費目が「食費」の行だけを、FILTERが残しています。

この数式が読みにくいと感じたら、記事の最後のLET関数で整理できます。

7. 金額の大きい出費トップ3を出したい（TAKE＋SORT＋VSTACK）

3か月分の中で、大きかった出費を3件。

使う関数は3つです。

=TAKE(SORT(VSTACK('8月'!A2:D11,'9月'!A2:D11,'10月'!A2:D11),4,-1),3)

内側から順番に見ていきます。

まずVSTACKで、3か月分を1つの表にまとめます。

次にSORTで並べ替えます。2つ目の「4」は、並べ替えの基準にする列の番号です。日付・費目・内容・金額の4列目なので4。3つ目の「-1」は大きい順（降順）です。小さい順なら1にします。

この時点で、30件が金額の大きい順に並んでいます。

最後にTAKEで、先頭から指定した行数だけ取り出します。3なら上位3件です。

まとめる、並べ替える、取り出す。3つの作業を、順番に1つの数式にしているんですね。

おまけ：長くなった数式はLET関数で整理する

6番の数式は、VSTACKが2回出てきて読みにくい。

LET関数を使うと、数式の中で使う範囲や計算結果に、名前を付けられます。

=LET(家計,VSTACK('8月'!A2:D11,'9月'!A2:D11,'10月'!A2:D11),費目,VSTACK('8月'!B2:B11,'9月'!B2:B11,'10月'!B2:B11),FILTER(家計,費目="食費"))

1つ目のVSTACKに「家計」、2つ目のVSTACKに「費目」という名前を付けています。

そうすると、最後の部分はこうなります。

FILTER(家計,費目="食費")

家計の中から、費目が食費のものを取り出す。何をしている数式なのか、読めば分かります。

あとから見返したときや、ほかの人に渡すときに助かります。

組み合わせるときのコツ

最後に、書き方のコツをひとつだけ。

最初から全部を1つの数式にしないことです。

まず内側の関数だけを、空いているセルに入れてみる。COUNTIFなら数が、MATCHなら位置が、正しく返っているかを確かめる。それから外側の関数を足していく。

こうしておくと、途中でエラーが出ても、どの関数で間違えたのかがすぐ分かります。

この記事で数式を「内側から」説明してきたのも、同じ理由なんです。

まとめ

やりたいこと	組み合わせ
重複登録を見つける	IF＋COUNTIF
電話番号から名前を調べる	INDEX＋MATCH
姓だけを取り出す	LEFT＋FIND
一番下の人を表示する	OFFSET＋COUNTA
条件に合う内容を1セルにまとめる	TEXTJOIN＋FILTER
複数シートから条件で取り出す	FILTER＋VSTACK
上位3件を出す	TAKE＋SORT＋VSTACK
長い数式を読みやすくする	LET