担当者ごとに受注一覧を分けるとき、毎回オートフィルターで絞り込んで、コピーして、別のシートに貼り付けて…ってやっていませんか?月末にこれを何人分もやると、地味に時間がかかるんですよね😅
結論から書くと、転記先のシートにこの式を1つ入れるだけです!
=FILTER(受注,受注[担当者]=B1,"該当なし")B1 の名前を変えれば、表が勝手に入れ替わります。コピーも貼り付けも要りません♪ 元の一覧を「テーブル」にしておけば、行が増えても式を直す必要もなし。
ただ、実際に作ってみると「#スピル!」が出たり、日付が 46296 みたいな数字になったりと、つまずくところがいくつかありました。その直し方も、試した順に書いていきますね。FILTER 関数がない Excel 向けの書き方も最後に載せています。
🎬 実際の Excel 画面で、最初から最後まで操作している動画もあります(5分半くらい)。テーブルの作り方やエラーの直し方も入っているので、動画で見たい方はこちらからどうぞ♪
できあがりはこんな感じです
「担当者別」シートの B1 に「佐藤」と入れると、受注一覧から佐藤さんの7件だけが並びます。
B1 を「鈴木」に変えると、鈴木さんの5件にパッと入れ替わります。式はそのままです✨
作り方は3ステップ
1. 元の表を「テーブル」にしておく
まず元の表(ここでは「受注一覧」シートの A1:F21)のどこかを選んで、Ctrl + T を押します。「先頭行をテーブルの見出しとして使用する」にチェックが入っているのを確かめて OK です。
次に、リボンの「テーブルデザイン」タブの左端にある「テーブル名」を 受注 に変えます。これで式の中で 受注(表全体)や 受注[担当者](担当者の列)と書けるようになります。
2. 転記先のシートに見出しと条件のセルを用意する
「担当者別」シートの A1 に「担当者」、B1 に「佐藤」と入れます。3行目には元の表と同じ見出し(受注日・担当者・取引先・商品・数量・金額)を並べておきます。
3. A4 に FILTER 関数を1つだけ入れる
=FILTER(受注,受注[担当者]=B1,"該当なし")受注:取り出したい表(テーブル全体)受注[担当者]=B1:残す行の条件。「担当者の列が B1 と同じ行」という意味"該当なし":1行も当てはまらないときに出す文字
Enter を押すと、A4 から右下に向かって結果が広がって表示されます(これを「スピル」と言います)。B5 から下に式をコピーする必要はありません。これだけで完成です🙌
仕上げ:日付と金額の表示をそろえる
…と言いたいところなんですが、式を入れた直後は、受注日が 46296 のような数字で、金額もカンマなしで表示されました。FILTER は値だけを持ってきて、元の表の表示形式までは持ってきてくれないんです。
A 列(A4 から下の、行が増えそうなところまで)を選んで Ctrl + 1 →「日付」、F 列は「数値」で「桁区切り(,)を使用する」にチェックを入れれば直ります。結果がまだ入っていない下の行まで先に設定しておくのがコツです。行が増えたときもそのままきれいに表示されますよ😊
うまくいかないときは(実際に出たエラーと直し方)
「#スピル!」と出たら、結果が広がる場所に何か入っています
英語版の Excel では #SPILL! ですが、日本語版ではカタカナで #スピル! と出ます。初めて見ると、何のエラーかわかりにくいですよね(^^;試しに C8 に「メモ」と1つ書いただけで、表がまるごと消えてこの表示になりました。
じゃまになっているセル(ここでは C8)の中身を消すと、すぐに元どおり表示されます。
「#CALC!」と出たら、当てはまる行が1つもありません
3つ目の引数("該当なし")を省いた =FILTER(受注,受注[担当者]=B1) のまま、一覧にいない名前(サンプルでは「山本」)を B1 に入れたら #CALC! になりました。3つ目の引数を付けておけば、代わりに「該当なし」と表示されます。
「名前は一覧にあるのに、なぜか該当なしになる…」というときは、B1 や元の表の名前の前後に空白が入っていないか見てみてください。B1 を「佐藤 」(後ろに空白)にしてみたら、佐藤さんの行があるのに「該当なし」になりました。見た目ではまったくわからないので、これは本当に気づきにくいです(>_<)
受注日が数字のまま → 表示形式を「日付」に
上の「仕上げ」のとおりです。数字になっていても壊れているわけではなく、Excel の中での日付の持ち方(1900年1月1日からの日数)がそのまま見えているだけなので安心してください。
応用編:条件を増やす・並べ替える・列をしぼる
ここからはちょっと応用です。どれもサンプルで結果の行数を数えて、手で数えた件数と合っていることを確かめました✅
「佐藤さん かつ 1万円以上」だけ出したい
条件をかっこで囲んで * でつなぎます。I1 に下限の金額(10000)を入れておく例です。
=FILTER(受注,(受注[担当者]=B1)*(受注[金額]>=I1),"該当なし")佐藤さんの7件のうち、1万円以上の3件だけが残りました。
「佐藤さん または 田中さん」を出したい
「または」のときは + でつなぎます。
=FILTER(受注,(受注[担当者]="佐藤")+(受注[担当者]="田中"),"該当なし")佐藤さん7件+田中さん4件で、11件になりました。
金額の大きい順に並べたい
FILTER をまるごと SORT で包みます。6 は「6列目(金額)で」、-1 は「大きい順に」という意味です。
=SORT(FILTER(受注,受注[担当者]=B1,"該当なし"),6,-1)受注日・取引先・金額だけ転記したい
CHOOSECOLS で、残したい列の番号を並べます。Microsoft 365 の Excel で使える関数です。
=CHOOSECOLS(FILTER(受注,受注[担当者]=B1,"該当なし"),1,3,6)FILTER 関数がない Excel を使っている場合
FILTER 関数は Microsoft 365 と Excel 2021 以降の関数なので、それより前の Excel では使えません。その場合は、INDEX と AGGREGATE を組み合わせた式を、表の大きさの分だけコピーして使います。A4 にこの式を入れて、A4:F23 にコピーしてください(元の表がテーブルではなく、受注一覧シートの A2:F21 にある場合の例です)。
=IFERROR(INDEX(受注一覧!A$2:A$21,AGGREGATE(15,6,(ROW(受注一覧!$A$2:$A$21)-1)/(受注一覧!$B$2:$B$21=$B$1),ROW()-3)),"")
ちょっと長いので、何をしているか分けて書きますね。
(ROW(…)-1)/(担当者の列=$B$1):条件に合う行は「表の中で何行目か」、合わない行はエラーになるAGGREGATE(15,6,…,ROW()-3):エラーは無視して、小さい順に1番目・2番目…の行番号を取り出す(4行目に置いた式なのでROW()-3が1になる)IFERROR(…,""):当てはまる行がなくなったら空欄にする
Microsoft 365 の Excel で、この式の結果が FILTER と1セルずつ同じになり、余った行が空欄になることを確かめました(古い版の Excel そのものでは試せていないので、その点だけご了承ください m(_ _)m)。
サンプルファイルはこちら
この記事で使ったファイルです。「受注一覧」「担当者別」(FILTER 版)「古い版向け」(INDEX+AGGREGATE 版)の3シートが入っています。データはすべて架空のものです。
マクロは入っていません。ダウンロードしたファイルが「保護ビュー」で開いたら、「編集を有効にする」を押すと式が動きます。
月末の「担当者ごとに分ける作業」、これで少しでもラクになったらうれしいです(*^^*)
この記事の式は、Microsoft 365 の Excel(ビルド 16.0.20527.20150、Windows 11)で、2026年11月8日にすべて試しました。
毎月の転記や集計を、ボタン1つで終わるようにしたい場合は、ココナラで Excel の自動化のご依頼も受けています。
