Excelの転記・集計に時間がかかる|関数・ピボットテーブル・Power Queryの使い分け

事務職・OA・業務改善

「毎月10個のCSVを開いて売上表へ貼り付けている」 「Excelの関数・ピボットテーブル・Power Queryを使えば楽になると聞いた。でもどれを覚えるべき?」

月末になると同じ転記や集計を繰り返す。貼り付け忘れや数式の範囲漏れが怖くて確認にも時間がかかる。そんな仕事はExcelの機能を組み合わせると改善できる場合があります。

選び方の基本は「何を繰り返しているか」で分けることです。

  • 関数: 商品IDから商品名を照合する、部署などの条件で金額を合計する
  • ピボットテーブル: 月別・部署別・商品別など集計する切り口を変える
  • Power Query: 毎月増えるCSVやExcelを取り込み、列の形をそろえて一つの表にする

これらはどれか一つだけを選ぶ必要はありません。Power Queryで明細をまとめ、関数でマスターと照合し、ピボットテーブルで集計する使い方もできます。

ただし作っただけでは完成ではありません。翌月の新しいファイルを追加して更新できるか、取り込んだ件数と合計が一致するか、エラー時に別の担当者が直せるかまで確認することが大切です。

この記事では2026年11月4日時点のMicrosoft公式資料をもとに、Windows版のExcel for Microsoft 365を主な操作例として使い分けを解説します。Excel 2016・2019やMac・Webでは使える機能が異なるため、その確認方法も紹介します。

まず元ファイルと完成表を整理し、手作業の場所を見つける

最初から関数名を検索するのではなく、毎月何を開いて何を作っているかを一枚に整理しましょう。

例えば売上集計の担当者なら、作業の流れは次のようになります。

工程 現在の作業 困っていること 改善候補
① ファイル集め 各部署の月別CSVを開く どの月を取り込んだか分からない Power Query
② 列の整理 日付・商品コードなどを並べ直す 出力元の列順が変わる Power Query
③ 商品照合 商品マスターから名前や単価を転記 コピー間違い・商品IDの不一致 検索関数
④ 部署別集計 部署名を選んで金額を足す 集計する部署・月が増える ピボットまたはSUMIFS
⑤ 提出用の確認 数字をコピーして報告表に貼る 新しい行の集計漏れ 件数・合計・更新確認

このとき「作業が遅い」という印象だけでなく、次の情報も控えてください。

  • 入力元のファイル名・場所・数と更新頻度
  • 一行が何を表すのか(例:売上明細1件)
  • 完成表で必要な部署・月・金額などの項目
  • 顧客ID・商品IDなど照合に使うキー
  • コピー・整形・計算・検算のどこに時間がかかるか
  • 誤集計すると誰にどのような影響があるか
  • 担当するPCのOS・Excelのバージョン・共有フォルダーの権限

元のCSVと完成した報告表は同じファイルを直接上書きせず、作業用コピーで試してください。業務の正式な数値を変更する場合は会社の承認・保存ルールを守りましょう。

一行の意味とキーを決めておく

例えば「売上明細」の一行を受注明細1件と定義します。

売上伝票に同じ商品IDが何度も出るのは普通ですが、商品マスター側の商品IDは同じコードに対して一行だけにする必要があります。

照合の基準が曖昧なまま自動化すると、手作業の間違いを速く再現するだけになることがあります。

関数・ピボットテーブル・Power Queryをどう使い分ける?

主な役割を比較しましょう。

やりたいこと 先に検討する方法 具体例 注意点
同じキーで別表から情報を引く XLOOKUP・VLOOKUPなど 商品IDから商品名を取得 キー重複・空欄・未一致
条件に合う金額を計算する SUMIF・SUMIFSなど 営業部の売上額を合計 条件の表記ゆれ・対象範囲
部署・月・商品ごとに集計し直す ピボットテーブル 部署を行、月を列、金額を値にする データソース範囲と更新
同形式の複数CSVを毎月追加する Power Query 9月・10月のCSVを縦につなぐ 重複ファイル・列名変更・型
複数表をキーで結合して整形する Power Queryのマージ機能 売上と商品マスターを結合 結合キーが一意か、未一致の行
一つの月次報告を繰り返し作る 機能を併用 取込→照合→集計→更新 更新順と検算を決める

小さな表なら関数、大きな表なら必ずPower Queryという線引きではありません。

関数はセルでの検索・計算に向いています。ピボットは集計の見せ方を変える作業に向いています。Power Queryは元ファイルの取込・変換を毎回同じ手順で行いたいときに役立ちます。

MicrosoftのXLOOKUP関数、ピボットテーブルの作成、Power Queryの概要も、それぞれの役割を案内しています。

1行ずつ別表の値を調べるなら関数

商品IDが書かれた行に商品名を表示したいなら検索関数が候補です。

同じ商品の取引行が増えても、マスターから対応する情報を引く仕組みにできます。

ただし検索した値が返ったからといってキーが一意である証明にはなりません。 XLOOKUPは既定で最初に一致した項目を返すため、商品マスターのID重複を別に確認しましょう。

Excel 2016・2019ではXLOOKUPが使えないとMicrosoftが案内しています。古いExcelではVLOOKUPやINDEX・MATCHなど、利用できる関数を検討します。

集計する切り口を変えたいならピボット

月別・部署別・商品別の合計が必要なら、同じ明細から集計軸を変えられるピボットテーブルが便利です。

行に部署、列に月、値に金額を置くと集計表を作れます。

ただし「データの個数」になっていないか「合計」になっているかを確認してください。金額列が文字列として取り込まれていると想定と違う結果になることがあります。

毎月ファイルを貼り合わせるならPower Query

同じ列構成のCSVが増える仕事では、Power Queryでフォルダー内のファイルをまとめて取り込み、必要な列・型・行を整理する方法があります。

Microsoftは複数のファイルを含むフォルダーからのインポートを公式に説明しています。

翌月も同じ形式のファイルが追加されるなら、手作業のコピーを繰り返す代わりに更新する流れを作れます。

取り込むファイルの構造が同じこと、対象ファイルを正しく選ぶことが前提です。

一つの売上集計で「照合・集計・取込」を分けて考える

次の架空の売上明細を使って考えます。すべて説明用のデータです。

日付 部署 商品ID 数量 単価 金額
2026/09/01 営業部 P01 2 3,000 6,000
2026/09/02 総務部 P02 1 5,000 5,000
2026/10/03 営業部 P01 3 3,000 9,000
2026/10/04 総務部 P03 2 4,000 8,000
合計 28,000

商品マスターには次の3行があるとします。

商品ID 商品名 単価
P01 文具セット 3,000
P02 収納ケース 5,000
P03 作業用品 4,000

本番では金額を税込・税抜のどちらで集計するかも統一してください。ここでは単純に「数量×単価」を金額としています。

ステップ1|商品IDの照合にはXLOOKUP

例えば売上明細をExcelのテーブルにし、テーブル名を「売上明細」「商品マスター」とした場合です。

商品名を取得する式の例は次のとおりです。

=XLOOKUP([@商品ID],商品マスター[商品ID],商品マスター[商品名],”未登録”,0)

これは「今の行の商品IDをマスターから完全一致で探し、商品名を返す」という意味です。XLOOKUPが使えるExcelでの例です。

同様に単価を取り込みたい場合は戻り列を商品マスター[単価]に変えます。

ただし未登録IDの行があったら「未登録」のまま確認対象にする方が安全です。見つからない値を一律0円にすると未照合の明細が売上から消えたことに気付けない場合があります。

さらに商品マスターで商品IDが重複していないか、IDの先頭ゼロや空白が失われていないかを確認しましょう。

例えば商品ID「0012」は文字列、「12」は数値として取り込まれると一致しないことがあります。変換前に列の扱いを決めてください。

ステップ2|決まった部署の合計ならSUMIFS

部署が「営業部」の金額だけを合計したい場合、SUMIFS関数を使えます。

テーブル名が「売上明細」なら式の例は次のとおりです。

=SUMIFS(売上明細[金額],売上明細[部署],”営業部”)

上の架空データなら営業部は15,000円、総務部は13,000円で合計28,000円です。

部署名の「営業部」と「営業 部」が混在している場合などは意図した集計にならない可能性があります。

部署や年月を細かく変えて集計するときは、SUMIFSを増やす方法とピボットを作る方法を比べましょう。

Microsoft「SUMIFS関数」は複数条件に該当する数値の合計を行う関数として案内しています。

ステップ3|部署別・月別の一覧はピボット

売上明細の範囲からピボットテーブルを作ると、行に「部署」、列に「月」、値に「金額の合計」を配置できます。

この例なら営業部が15,000円、総務部が13,000円、総合計28,000円です。

日付が日単位のままなら「月」の列を元データで用意したり、適切なグループ化を行ったりして集計します。日付列が文字列になっていないかも確認してください。

元データの見出しは1行にそろえ、空白の見出しや途中の小計行を入れない方が扱いやすくなります。

ステップ4|翌月のCSVはPower Queryで追加する

毎月のCSVに「日付・部署・商品ID・数量・金額」のように同じ列があれば、Power Queryのフォルダー接続で取り込む方法が候補です。

Excel for Microsoft 365のWindowsデスクトップ版では、通常は[データ]→[データの取得]→[ファイルから]→[フォルダーから]で対象を選びます。

  • 元データ専用フォルダーを用意する
  • 同じ構造の確定済みCSVだけを保存する
  • 一時ファイル・バックアップ・過去の再出力を除外する
  • 列名と型をそろえて結合する
  • 結合後の明細をExcelテーブルなどへ読み込む
  • 翌月CSVを追加したらクエリを更新し、件数と金額を照合する

「9月の速報版」と「9月の確定版」を両方取り込むと二重計上される場合があります。古い集計結果を同じフォルダーに保存することも避けましょう。

Power Queryの更新は元ファイルの中身の正しさまでは保証しません。

毎月のCSVをまとめる具体的な画面操作と検算方法は次の記事で説明しています。

毎月増えるCSVをExcelで一つの表にまとめたい|Power Queryのフォルダー結合と更新手順
月別CSVをPower Queryのフォルダー結合で一つの表にまとめる方法を解説。対象ファイルの絞り込み、先頭ゼロと文字コード、件数・金額の検算、翌月の更新手順を整理します。

Excelの版・OS・権限によって使える機能は違う

会社のExcelがいつの製品かを確認せずに最新の操作手順を当てはめると、ボタンや関数が見つからない場合があります。

最初にExcel for Microsoft 365・Excel 2024・2021・2019・2016などのバージョンと、Windows・Mac・Webのどこで使うかを確認しましょう。

利用環境 事前に確認すること
Windows版Microsoft 365・Excel 2024など XLOOKUP、ピボット、データの取得・Power Queryの利用可否
Windows版Excel 2016・2019 XLOOKUPは非対応。VLOOKUP等で代替。Power Queryの接続先・機能を確認
Microsoft 365のMac版Excel Power Queryの取込元と更新機能にWindowsとの差がある
Web版Excel Power Queryは利用できる機能・データソース・更新方法に制限がある
会社の共有フォルダーやクラウド アクセス権、認証、ファイルパス、接続先の利用許可
別担当者のPCで運用 そのPCでも同じクエリを更新できるか

Microsoftの「ExcelバージョンのPower Queryデータソース」にはWindows・Mac・Webの対応状況が示されています。

Mac・Web・WindowsのExcelならPower Queryもすべて同じように使えると考えないでください。

例えば会社のPCでは接続できても、別の担当者は共有フォルダーの権限がなく更新できない場合があります。利用する製品版だけでなくデータの保存場所と権限が関係します。

XLOOKUPが使えない場合

MicrosoftのXLOOKUP公式ページは、Excel 2016とExcel 2019ではXLOOKUPが使用できないと説明しています。

その環境なら、VLOOKUP・INDEXとMATCHなど使える関数を検討してください。

例えば商品マスターの左端列に商品IDがあり、2列目が商品名ならVLOOKUPで完全一致を指定する方法があります。

=VLOOKUP([@商品ID],商品マスター[[商品ID]:[商品名]],2,FALSE)

ただしこの式も列配置やテーブル名が例と異なれば変更が必要です。配布する相手のExcelで開いて結果を確認しましょう。

翌月の追加・重複・未一致を試し、旧手順と照合する

集計結果が表示されても、正しいとは限りません。

新しい方法を導入するときは正式な元データを直接変更せず、コピーしたExcel・CSVで旧手順と新手順を比較しましょう。

最低限チェックする5つの数字と状態

確認すること 確認方法 間違いの例
対象期間 最古・最新の日付を照合 先月分が抜けている
明細件数 元ファイルの件数合計と突合 1ファイル未取込、重複取込
金額合計 CSV側の総額と出力表を比較 金額列が文字列、除外行の漏れ
未一致件数 商品IDの照合で「未登録」を数える 新しい商品IDがマスターにない
重複と更新日時 ファイル名・伝票番号・更新結果を確認 同じ月のCSVを二度読み込んでいる

数値は旧手順と必ず一致するとは限りません。旧手順が誤っていた可能性もあるため、差が出た場合はどの行が原因か追跡してください。

特にPower Queryで取り込むときは、数値の先頭ゼロ、日付形式、全角半角の違い、不要な空白が変換に影響します。

「エラーがある行をすべて削除して件数を合わせる」という方法は避けましょう。削除した行が重要な取引なら誤集計になります。

ピボットテーブルは追加行が自動的に集計されるとは限らない

元データへ新しい行を追加したのに、ピボットの結果が先月までのままということがあります。

まずピボットテーブルの[更新]を実行します。それでも追加行が出ない場合はデータソースの範囲とフィルターを確認してください。

例えば元の参照範囲がA1:D100だったのに101行目へ明細を追加した場合、通常のセル範囲を参照しているピボットではその行が範囲外になることがあります。

Excelテーブルを元データにする方法もありますが、更新操作が不要になるとは限りません。

Microsoftの「ピボットテーブルのデータを更新する」も更新操作について説明しています。

追加行が反映されないときの更新と範囲変更の具体例はこちらで確認できます。

Excelのピボットテーブルに追加した行が出ない|更新と元データ範囲の確認
Excelのピボットテーブルに新しい行が反映されないときは?更新とデータソース範囲の変更を使い分ける手順を解説。Excelテーブル、フィルター、外部接続の確認と集計金額の検算方法まで紹介。

Power Queryからピボットへつないだ場合は順番に注意

Power Queryで元ファイルを結合し、その出力をピボットの元にしている場合です。

① 元ファイルを確認 → ② Power Queryを更新 → ③ 取り込み済みの表を確認 → ④ ピボットを更新 → ⑤ 件数と合計を照合という順番を決めておきましょう。

Excelの[すべて更新]で複数の接続をまとめて更新できる場合もありますが、接続の設定や更新が完了したかを確認する必要があります。

Microsoft「Excelで外部データ接続を更新する」では手動更新・接続の設定・ピボットとの関係が案内されています。

「更新ボタンを押したから確認不要」ではなく、最終的な出力表に当月分が含まれているか確かめてください。

更新手順を記録し、別の担当者へ引き継ぐ

毎月のコピーが減っても、作成者しか更新できない仕組みでは担当変更の際に困ります。

作業ブックには、最低限次の情報を残しておきましょう。

引継ぎ項目 記載する内容
元ファイルの置き場所 共有フォルダーの場所とアクセス権
ファイルの命名ルール 毎月のCSV名と速報・確定の区別
必須列と型 日付・部署・商品ID・金額などの列定義
更新手順 追加・クエリ更新・ピボット更新の順番
出力先 提出用集計表と保存・配布方法
検算の記録 対象期間・件数・金額・未一致・確認者
エラー時の対応 どのステップを見て誰に問い合わせるか

更新後の出力表を手入力で直した場合、次回の更新で変更が消える可能性があります。

訂正が必要なら、元データを修正するか、承認された補正表で管理するなど再実行できる方法を決めてください。

CSVの列名が変更されたらクエリが止まる場合もある

例えば先月まで「金額」だった列が今月から「売上額」になった場合です。

Power Queryの変換ステップが古い「金額」という列を参照していると、「テーブルの列が見つかりませんでした」というエラーになる場合があります。

このときは原本のコピーを取ってからクエリの適用したステップを確認し、どの時点で列名が一致しなくなったか調べます。

列名を修正して更新できたとしても、金額合計と対象件数の再確認が必要です。

列名変更によるエラーを安全に直す具体的な手順は次の記事で説明しています。

ExcelのPower Queryで「テーブルの列が見つかりません」と出る|列名変更後の修正手順
Excel Power Queryで「テーブルの列が見つかりません」と表示されたときの修正手順。元CSVの列名変更と古い参照を照合し、最初にエラーとなる適用ステップを特定してMコードや列名変更を修正。再読み込み後の件数・合計の検算まで解説します。

別の担当者のPCで一度更新してもらう

引き継ぐ前に作成者以外が実際に更新を実行できるか確認しましょう。

同じExcelのバージョンでも共有フォルダーのパスや接続権限が異なることがあります。

別担当者が説明書どおりに更新し、当月の件数・合計・未一致を確認できれば、手順書の不足も発見しやすくなります。

どれを使うか迷うときのよくある質問

関数を全部Power Queryへ置き換えるべき?

いいえ。行ごとの小さな検索や条件集計は関数で分かりやすく管理できる場合があります。

Power Queryは取込・整形・結合を減らすために選び、関数はExcel上で必要な照合や計算をするなど併用を考えましょう。

ピボットテーブルだけで月次CSVをまとめられる?

ピボットテーブルは集計のための機能です。CSVを毎月コピーして一つにする前処理が必要な場合は、Power Queryで取り込む方法を検討します。

ピボットを作る前に元の表が正しくまとまっているか確認してください。

Power Queryなら翌月のCSVを入れるだけで何もしなくていい?

フォルダー内へ新しいCSVを追加した後は、通常は更新操作と結果の検算が必要です。

元の列名・データ型・ファイル構造が変わると更新に失敗したり、思わぬ集計結果になったりする場合があります。

XLOOKUPはどのExcelでも使える?

いいえ。Microsoftの案内ではExcel 2016と2019では使えません。利用する版に合わせてVLOOKUPなどを検討してください。

更新後の数字が旧手順と合わない場合は?

対象期間・明細件数・重複ファイル・未一致キー・金額列の型・集計範囲を順番に確認しましょう。旧手順の間違いが見つかる可能性もあります。

差があるまま正式な報告へ使わず、原因を記録して確認を受けてください。

まとめ|「取込・照合・集計」を分ければ毎月の作業を見直せる

毎月のExcel転記・集計を減らすなら、まず作業のどこに時間がかかり、何を完成させる必要があるかを整理しましょう。

  • 同じキーで別表から情報を引くなら検索関数
  • 部署・月・商品別の集計を変えるならピボットテーブル
  • 複数の月次ファイルを取り込み整形するならPower Query

これらは併用できます。元ファイルを整理してから取込・照合・集計の役割を決めることが大切です。

最初の1か月分をコピーで試し、翌月の追加・重複・列名変更までテストしてください。 件数・合計・未一致を照合し、他の担当者でも更新できる手順を残せれば、長く使える業務改善につながります。

参考資料・公式情報

本記事は2026年11月4日時点のMicrosoft公式資料をもとにしています。操作例はWindows版Excel for Microsoft 365を主な想定とします。表示名や利用できるデータソースは製品・バージョン・権限によって異なります。