【転記をなくす①】関数編|FILTER・XLOOKUP・UNIQUEで「写す作業」をゼロに

エクセル(excel)

「写す」のをやめて「参照する」だけで、転記は消せます

受注一覧から担当者ごとの表に写す。
取引先マスタから請求書に住所を写す。

こうした転記は、関数を使えばコピー&貼り付けなしで作れます。元データを直せば転記先も自動で書き換わるので、「直し忘れ」もなくなります。

この記事は「Excelの転記作業をなくす」シリーズの1本目 関数で「写す作業」をゼロにします。

最初にやること:元データを「テーブル」にする

関数で転記をなくすときは、先に元データをテーブルに変換しておくと安心です。
テーブル化しておくと行を足したときに、式の範囲も自動で伸びるからです。

  1. 元データの表の中をどこか1セル選び、Ctrl + Tを押します。
  2. 「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認し、OKを押します。
  3. 「テーブルデザイン」タブの左端で、テーブル名を分かりやすい名前に変えます。

この記事では、次の2つのテーブルを例に使います。

パターン1:1つの値を引いてくる(XLOOKUP)

請求書や見積書に、取引先の住所や窓口を毎回手で写しているなら、XLOOKUP関数で自動化できます。取引先名を1か所入れるだけで、残りの項目が埋まります。

請求書テーブルのA11セルに取引先名を入れたとします。住所欄には次の式を入力します。
=XLOOKUP(A11,取引先[取引先],取引先[住所],"取引先マスタに未登録")

マスタにない取引先名を入れたときは「取引先マスタに未登録」と表示されるので、登録漏れにもすぐ気づけます。(今回はA13 西部印刷 が未登録)

XLOOKUP関数の詳しい使い方は、「VLOOKUPはもう古い?XLOOKUPの使い方と置き換え手順」でも解説しています。

パターン2:条件に合う行をまとめて引いてくる(FILTER)

「受注一覧から佐藤さんの分だけを別シートに写す」といった転記には、FILTER関数が向いています。条件に合う行を、何行でもまとめて取り出せます。

担当者別欄のB8セルに担当者名を入れ、A10セルに次の式を入力します。

=FILTER(受注,受注[担当者]=B8,"該当なし")

条件に合う行が、A10セルから右と下に自動で広がって抽出されます。

パターン3:集計表を自動で作る(UNIQUE・SORT・SUMIFS)

「取引先の名前を拾って集計表に写し、金額を合計する」という作業も、関数だけで自動化できます。新しい取引先が受注一覧に増えると、集計表にも自動で行が追加されます。

  1. 集計用の表のA9~C9セルに、「取引先」「受注金額」「件数」と見出しを入れます。
  2. A10セルに、取引先の一覧を重複なしで並べる式を入れます。

=SORT(UNIQUE(受注[取引先]))

  1. B10セルに、取引先ごとの金額を合計する式を入れます。
    「A10#」は「A10セルから広がった範囲すべて」という意味です。

=SUMIFS(受注[金額], 受注[取引先], A10#)

  1. C2セルに、件数を数える式を入れます。

=COUNTIFS(受注[取引先], A10#)

B10・C10の式も、A列の行数に合わせて自動で下に広がります。式を下にコピーする必要がないので便利です。

まとめ

関数で転記をなくすコツは、「元データをテーブルにして、そこを参照する」ことです。

  • 1つの値を引くなら XLOOKUP
  • 条件に合う行をまとめて引くなら FILTER
  • 集計表を自動で作るなら UNIQUE+SUMIFS

まずは、毎月手で写している表を1つ選んで、式に置き換えてみてください。

次回:②パワークエリ編

支店ごとの売上表や、システムから出力したCSVなど、複数のファイルをまとめる転記は関数では難しくなります。次回は、フォルダに入れて更新ボタンを押すだけで集計できるパワークエリを解説します。

コメント

コメント

-->
タイトルとURLをコピーしました