
【期間限定無料公開】目で突き合わせるのは、もう終わりにする。Excel突合と集計の実務レシピ
1. 読者がいま困っている場面
月末。2つの表を並べて、目で見比べて、違うところにマーカーを引く。注文一覧と商品マスタ。名簿と出欠リスト。先月もやった、あの作業です。
VLOOKUP(別の表から値を引いてくる関数)を使ってみたこともある。でも、出てくるのは「#N/A」(見つかりません、という意味のエラー表示)の行列。どこが間違っているのか分からなくて、結局、目で見比べる方法に戻ってしまった。集計のほうも同じで、フィルターをかけて、範囲を選んで、SUM(合計を出す関数)を入れて、条件が変わるたびにやり直し。気づけば夕方になっています。
私も長いことそうでした。関数が苦手なのではなく、「どの場面で、どの関数を、どういう順番で使うか」の型を持っていなかっただけだと、後になって分かりました。この記事は、その型のほうを渡す記事です。
2. このnoteが向いていない人
先に外しておきます。
VBA(Excelを自動で動かすプログラム)やPower Query(外部データをまとめて取り込む機能)を本格的に学びたい人。この記事は関数の範囲で完結します。
統計分析やグラフの見せ方を知りたい人。ここで扱うのは「突き合わせる」と「集計する」だけです。
Googleスプレッドシートだけを使っていて、Excelは一切使わない人。考え方は似ていますが、数式の細部が違う箇所があります。
逆に、「VLOOKUPで一度つまずいた」「集計のたびにフィルターと格闘している」という人には、そのまま効く内容のはずです。
3. 有料部分に入っているものの一覧
有料部分には、次のものが入っています。
状況別の分かれ方(A:2つの表を突き合わせたい/B:条件で集計したい/C:日付・月別に集計したい)と、どれを選ぶかの基準
手順8つ(1手順1動作、すべて数式の実物つき)
VLOOKUPで値を引く基本形と、コピーで壊れない書き方
IFERROR(エラーが出たら別の表示に変える関数)で「見つからない」を整える方法
XLOOKUP(VLOOKUPの新しい版の関数。使えるExcelの条件つき)
SUMIFS・COUNTIFS・AVERAGEIFS(条件に合うものだけを合計・件数・平均する関数の組)による複数条件の集計
作業列を使った月別集計
エラーの原因別対処(#N/Aが出る4大原因、範囲ずれ、日付が文字列になっているケースなど)
コピペで使える数式テンプレ3種(9章に掲載)
テンプレ1:突合セット
テンプレ2:複数条件の集計セット
テンプレ3:月別集計セット
例に使う表と数字は、すべて仮のものです。自分の仕事の表に置き換えて読んでください。
4. 全体の流れを3ステップで俯瞰
進め方は3ステップです。
ステップ1:やりたいことを言葉にする。「別の表から持ってきたい」のか「条件に合う分だけ集計したい」のか。ここが決まれば関数はほぼ決まります。
ステップ2:早見表で関数を選ぶ(下に置いておきます)。
ステップ3:エラーは「関数のせい」にせず、原因から直す。#N/Aの多くは、関数の書き方ではなくデータ側の問題です。
やりたいこと別の早見表:
別の表から、名前や単価を持ってきたい → VLOOKUP/XLOOKUP(突合)
条件に合う金額だけ合計したい → SUMIFS
条件に合う件数を数えたい → COUNTIFS
条件に合う平均を出したい → AVERAGEIFS
月別に集計したい → 作業列+SUMIFS
6.状況別の分かれ方と、どれを選ぶかの基準
A:2つの表を突き合わせたい
手元に「注文一覧」があって、別の「商品マスタ」から商品名や単価を持ってきたい、という形です。2つの表に共通する列(商品コードなど「キー」になる列)が存在するのが前提。やることは「キーを探して、横の値を引いてくる」ので、使うのはVLOOKUPかXLOOKUPです。
B:条件で集計したい
1つの表の中で、「4月の、担当・山田さんの、分類が文具の分だけ合計したい」という形です。行をくっつける必要はなく、条件に合う行だけを拾って計算します。使うのはSUMIFS(合計)、COUNTIFS(件数)、AVERAGEIFS(平均)の3兄弟です。
C:日付・月別に集計したい
Bの条件に「月」が絡む形です。日付そのものは「2026/4/1」のように1日単位で入っていることが多く、そのままでは月でくくれません。作業列(計算のために追加する補助の列)で「2026/04」という年月の形に変換してから、SUMIFSで集計するのが安定します。
どれを選ぶかの基準は、「表が2つあるか、1つか」です。2つならA。1つならBかCで、月に丸める必要があるならC。迷う場面の大半は、実はAとCの複合です(マスタから単価を引いてきてから、月別に集計する)。その場合は、Aを先に終わらせて1つの表にしてから、Cに進んでください。順番を逆にすると、だいたい詰まります。私も詰まりました。
7.手順を1つずつ、実物つきで
例の表は仮のものです。シート「商品マスタ」:A列=商品コード、B列=商品名、C列=単価。データは2〜6行目。シート「注文」:A列=商品コード、B列に商品名を引きたい。C列=個数。
手順1:キーになる列を決める
2つの表に共通する列を1つ決めます。ここでは商品コード。名前で突き合わせると同姓同名や表記ゆれで壊れるので、コードやIDなど「ぶれない列」を選んでください。
手順2:VLOOKUPを、まず1セルだけ入れる
注文シートのB2に、次の数式を入れます。
=VLOOKUP($A2,商品マスタ!$A$2:$C$6,2,FALSE)
読み方は「A2のコードを、マスタのA2:C6の中で探して、2列目(商品名)を返せ。FALSEは完全一致で探せ、という指定」です。最後のFALSEを省くと近似一致という別の探し方になり、意図しない値が返ることがあります。ここは省略しないのが無難です。
手順3:下にコピーする前に、$の付き方を確認する
数式の中の「$」は、コピーしても範囲をずらさないための固定の印です。探す範囲(商品マスタ!$A$2:$C$6)には$を付け、探す値($A2)は列だけ固定します。$が無いまま下にコピーすると、範囲が1行ずつずれていき、途中から#N/Aが連なります。この記事の読者のつまずきで、一番多いのがこれだと思います。
手順4:IFERRORで「見つからない」の表示を整える
マスタに無いコードは#N/Aになります。見た目を整え、後で数えやすくするために、こう書き換えます。
=IFERROR(VLOOKUP($A2,商品マスタ!$A$2:$C$6,2,FALSE),"マスタ未登録")
これで、見つからない行には「マスタ未登録」と表示されます。エラー表示のまま提出するのと、言葉で表示するのとでは、受け取る側の印象がだいぶ違います。
手順5:新しいExcelなら、XLOOKUPも候補に入れる
Microsoft 365など新しいExcelでは、XLOOKUPが使えます。同じことを、こう書けます。
=XLOOKUP($A2,商品マスタ!$A$2:$A$6,商品マスタ!$B$2:$B$6,"マスタ未登録")
「探す列」と「返す列」を別々に指定する形で、見つからない場合の表示も数式の中に含められます。職場のExcelで使えるかどうかは、関数を入力する途中で候補に出てくるかで確認してください。出てこなければ、手順2〜4のVLOOKUPの形がそのまま使えます。
手順6:SUMIFSで、複数条件の合計を出す
今度は集計です。シート「売上」に、A列=日付、B列=担当者、C列=分類、D列=金額が入っているとします。「2026年4月の、山田さんの、文具の合計」は、こう書きます。
=SUMIFS(D:D,A:A,">=2026/4/1",A:A,"<2026/5/1",B:B,"山田",C:C,"文具")
月を指定するのに「>=2026/4/1」と「<2026/5/1」で挟むのがミソです。日付は期間で挟むと、月末が何日か(30日か31日か)を気にしなくて済みます。
手順7:COUNTIFSで、件数を数える
合計ではなく件数なら、COUNTIFSです。条件の並べ方はSUMIFSとほぼ同じで、合計する列の指定が要りません。
=COUNTIFS(A:A,">=2026/4/1",A:A,"<2026/5/1",B:B,"山田",C:C,"文具")
平均ならAVERAGEIFSで、書き方はSUMIFSと同じ並びです。この3つは「条件の指定のしかた」が共通なので、1つ覚えると残りも楽になります。
手順8:作業列を作って、月別に集計する
売上の表に作業列(ここではE列)を追加し、各行の日付を年月の形に直します。E2に入れる数式はこれです。
=TEXT(A2,"yyyy/mm")
「2026/04」のような表示になります。これを下までコピーしたら、月ごとの合計はSUMIFSで取れます。
=SUMIFS(D:D,E:E,"2026/04")
月を変えたければ、最後の「2026/04」を書き換えるだけです。ピボットテーブル(表を集計し直す機能)でも同じことはできますが、毎月同じ形の集計を繰り返す仕事なら、数式で型を作っておくほうが翌月が楽です。
8.つまずきやすい箇所と、その場での対処
#N /Aが出る原因1:表記ゆれと、目に見えない空白
コードの後ろに空白が入っている、半角と全角が混ざっている、といったケースです。見た目は同じなのに一致しません。対処は、まず両方の表のキー列を目でよく見ること。それでも見つからなければ、作業列にTRIM(前後の空白を取り除く関数)をかけてから突き合わせると、原因が空白かどうかを切り分けられます。
#N /Aが出る原因2:数値と文字列の不一致
商品コード「101」が、片方の表では数値、もう片方では文字列として入っているケースです。セルの左上に小さな緑の三角が出ているものは、文字列で入っている合図のことが多いです。対処は、どちらか一方にそろえること。文字列側を選んで「数値に変換」が早いですが、元データを触れない場合は、検索値のほうに「&""」を付けて文字列化する(例:$A2&"")など、数式側で合わせる方法もあります。
#N /Aが出る原因3:範囲が足りていない
マスタに新しい商品が増えたのに、数式の範囲が昔の行数(例:$C$6まで)のまま、というケースです。新しく増えた行だけ#N/Aになります。対処は単純で、範囲を広げておくこと。最初から少し大きめの範囲(例:$A$2:$C$100)にしておくと、この事故は減ります。
コピーしたら下の行だけ金額がおかしい
だいたい$の付け忘れです。範囲が一緒にずれているので、途中の行から数字がずれます。数式を上から下まで流し込んだ後に、最後の行の数式を1つ開いて、範囲が正しい場所を指しているか確認する癖をつけてください。10秒の確認で、提出後の手戻りが1回消えます。
日付の条件が効かない(集計が0になる)
日付が「日付として」ではなく文字として入力されていると、">=2026/4/1" の条件に引っかからず、合計が0になります。見分け方は、日付のセルを選んだときに右寄せか左寄せか。日付は通常右寄せ、文字は左寄せです。対処は、入力し直すのが確実です。量が多い場合は、日付を直す前に、そもそもその列がいつから文字になっていたかを確認してください。途中からだけおかしい、ということがよくあります。
9.そのまま使えるテンプレート3つ
<>の中は、自分の表に合わせて書き換える部分です。
テンプレ1:突合セット(VLOOKUP+IFERROR)
=IFERROR(VLOOKUP($A2,マスタ!$A$2:$C$100,2,FALSE),"マスタ未登録")
使い方:商品名なら「返したい列番号」は2、単価なら3、という具合に、欲しい列の番号だけ変えて使います。「マスタ未登録」の文言は、自分の職場で通じる言葉に変えて大丈夫です。
テンプレ2:複数条件の集計セット
合計: =SUMIFS(金額列,日付列,">=開始日",日付列,"<終了日",担当列,"担当者",分類列,"分類")
件数: =COUNTIFS(日付列,">=開始日",日付列,"<終了日",担当列,"担当者",分類列,"分類")
使い方:条件は後ろに「条件列,条件」の組を足すほど細かくできます。逆に、条件を1組消せば「担当者別の月合計」のような、一段ゆるい集計にも使えます。
テンプレ3:月別集計セット
作業列に入れる数式: =TEXT(日付セル,"yyyy/mm")
月別合計: =SUMIFS(金額列,作業列,"2026/04")
使い方:集計表のほうに「2026/04」「2026/05」と月を縦に並べておき、月別合計の数式の最後を、その月のセル参照に変えて下にコピーすると、1年分でも数分で並びます。毎月の定例集計は、この形にしておくと翌月から一気に楽になります。
10.まとめ
突合と集計は、実は「関数の知識」よりも「型と順番」の問題でした。やりたいことを言葉にする。早見表で関数を選ぶ。エラーが出たら、関数の書き方より先にデータ側を疑う。この3つが回り始めると、月末の景色が変わってきます。
最初は、テンプレをそのままコピペするところからで十分です。自分の表に合わせて書き換えるうちに、どの部分が何をしているかは自然に分かってきます。目で見比べてマーカーを引く作業から卒業する日が、思ったより早く来るはずです。
11.関連記事のご案内
あわせて読みたい記事を紹介します。
議事録は「型」で乗り切る。会議の種類別テンプレと、決定事項・宿題の書き分け方
初めての請求書は、テンプレを拾う前に「自分のケース」を決める
もちろん、この記事のテンプレだけでも月次の集計は十分に回ります。関連記事は、困った場面が来たときの選択肢の1つとして置いておいてください。