あの〜毎日の取引データから請求明細書作るのって、やっぱり関数でゴリゴリがんばるしかないんですか〜？
関数だけじゃなくて、Power Queryとかピボットテーブルっていう強い味方もいるんだよ〜。うまく使い分けるのがポイントだよ！
取引先ごとの請求明細書をExcelで作るとき、「SUMIF関数などを駆使して手作業でまとめてる…」なんてこと、ありませんか？
関数での作成も可能ですが、件数が多くなるとだんだん管理が大変になってきますよね。
そこで今回は、関数を最小限に抑え、Power Queryとピボットテーブルを活用して、スッキリ＆効率的に請求明細書を作る方法をご紹介します。
関数で作る方法との違いや、それぞれのメリット・デメリットも比較しながら、作成の流れを紹介していきます！
今回使うデータはこんな感じ！
今回使うのは、「取引先」「担当者名」「日付」「商品」「単価」「数量」「売上」の情報が入った取引データです。
さらに、管理しやすいように「年」と「月」の項目も用意してあります。
このデータはテーブル形式になっていて、名前は「取引テーブル」と付けています！
【関数編】SUMIFSなどを使った請求明細書作成
- FILTER関数とUNIQUE関数で取引先や品目情報を取り出す
- そこからSUMIFS関数で日付ごとの金額を集計
- 取引先・年・月をセルに入力して、そこを元にデータを絞り込む
- メリット：見た目やレイアウト、計算方法を自由にカスタマイズできる
- デメリット：関数の組み方がちょっと複雑。取引先が増えるとメンテナンスが大変…。
作成した帳票がこちら。
入力セル
この3つを入力すると、6行目以降に対象データがズラッと出てくる仕組みです。
関数の中身をちょっと解説
明細の行取得（A列：日付、B列：商品、C列：単価）
=UNIQUE(FILTER(取引テーブル[[日付]:[単価]],
(取引テーブル[取引先]=B1) *
(取引テーブル[年]=B2) *
(取引テーブル[月]=B3)
))- FILTER関数で、「取引先・年・月」が一致するデータだけを取り出しています。
条件を「*」でつなげることで、AND条件として絞り込みできるんです。 - UNIQUE関数を使えば、重複を除いた明細リストが作れます。
D列：数量
=SUMIFS(取引テーブル[数量],
取引テーブル[取引先], $B$1,
取引テーブル[日付], A7,
取引テーブル[商品], B7)- SUMIFSで、「取引先 × 日付 × 商品」の組み合わせごとに数量を合計！
同じ商品が複数回出てきても、ちゃんと合算されます。
E列：金額
=SUMIFS(取引テーブル[売上],
取引テーブル[取引先], $B$1,
取引テーブル[日付], A7,
取引テーブル[商品], B7)- 売上金額の集計も同じくSUMIFSでOK！
- 売上は「単価 × 数量」の計算が済んでいる想定ですが、もちろん「単価列 × 数量列」で計算しても問題ありません。
関数で作る方法は、自分好みに細かく作り込めるのが魅力ですが、管理のしやすさで言うとちょっと手間がかかる場面も。
【Power Query編】データの整形はおまかせ！自動化でラクしよう
- 明細データを Power Query で読み込んで、「取引先・日付・商品ごと」に整理
- 「グループ化」機能を活用して、取引先ごとの表を作っちゃいます
- メリット：データの更新がめちゃ簡単！しかも自動でやってくれる！
- デメリット：更新のたびに「更新」ボタンを押す必要がある
入力セルをパラメータにしよう！
関数のときと同じように、「取引先・年・月」の入力セルを準備します。
でも今回は、それを Power Queryで使えるパラメータに変換していきます！
入力セルの設定（名前付き範囲）
- B1：取引先 → Param_取引先
- B2：年 → Param_年
- B3：月 → Param_月
１．B1セルを選択 して、メニューの「数式」→「名前の定義」をクリック
２．名前に「Param_取引先」と入力
同様にB2セルに「Param_年」、B3セルに「Param_月」と名前を付けておきます。
Power Query側でパラメータを作成！
先ほど名前を付けたセルをPower Queryでパラメータ化します。
１．先ほど名前をつけたセルを選択して「データ」→「テーブルまたは範囲から」をクリック
２．表示されたデータを右クリック → 「ドリルダウン」を選択（これで1つの値に）
３．「Param_年」「Param_月」も同様に作成
手順１～２を繰り返します。
４．読み込みは「接続専用」にしておきましょう（シートに出さなくてOK）
データを整えるステップ！
Power Query エディターで「取引テーブル」を開いて加工していきます。
１．グループ化
メニューの「変換」→「グループ化」を選択して、
「取引先・日付・商品」でグループ化！
「数量」と「売上」は合計を出します。
２．フィルターをかける
次のような式で、指定された取引先・年・月だけに絞り込みます。
フィルターされた行 = Table.SelectRows(グループ化された行, each
[取引先] = Param_取引先 and
[年] = Param_年 and
[月] = Param_月
),※ グループ化のすぐあとにこのフィルターを追加しています。
最後にデータを読み込もう！
加工が終わったら、テーブルの読み込み先を A6セル に指定。
あとは「取引先・年・月」を入力して、「更新」ボタンをポチッと押すだけ！
Power Queryは一度セットアップしてしまえば、あとは更新するだけ！
関数で悩んでた方にはかなりおすすめです！
入力セルを変更したら必ず「更新」してください！
集計の表示もできます。
更新をすると列幅が毎回変わってしまいます。勝手に調整されないためには「プロパティ」で「列の幅を調整する」のチェックを外しておきましょう。
１．「テーブルデザイン」→「プロパティ」をクリック
２．「列の幅を調整する」のチェックを外す
【ピボットテーブル編】関数なしでカンタン作成！
- 「取引テーブル」をもとにピボットテーブルを作成
- 「取引先・年・月」で絞り込み
- 「日付」「商品」で分類しながら
- 「数量」「売上」を集計します！
- レイアウトを「表形式」にすると、明細書っぽい見た目になります
- メリット： 関数いらず／ドラッグ操作で感覚的に作れる／集計が一瞬で終わる
- デメリット： 細かいレイアウト調整や装飾はやりづらいかも
ピボット設定はこうします。
- フィルター
「取引先、年、月」 - 行（分類）
「日付、商品、単価」 - 値（集計）
「合計/数量」「合計/売上」
この設定で作れば、請求明細っぽい表がパッとできあがります！
データの更新も簡単なので、毎月の処理がかなり時短になりますよ〜。
「個数」と「金額」の表示名、ちょっとだけ工夫してます！
ピボットテーブルの「個数」と「金額」は、もともと「合計 / 数量」「合計 / 売上」っていう名前で表示されます。
でもそのままだとちょっと見づらいので、表示名をわかりやすく変更！とはいえ、「数量」って名前はすでに元の取引テーブルで使ってるので、そのまま使うとエラーになるんです…
なので今回は、あえて「個数」という別の名前に変えてあります。
同じく「売上」も「金額」に変えて、見やすさアップ！
ピボットテーブルも更新をすると列幅が毎回変わってしまいます。勝手に調整されないためには「オプション」で「更新時に列幅を自動調整する」のチェックを外しておきましょう。
１．右クリックメニューの「ピボットテーブル オプション」をクリック
または、「ピボットテーブル分析」→「オプション」をクリック
２．「更新時に列幅を自動調整する」のチェックを外す
まとめ
請求明細書を作る方法って、実はいろいろあるんです。
それぞれに「得意なこと・苦手なこと」があるので、目的に合わせて使い分けるのがコツ！
関数を使うパターン
「見た目にこだわりたい！」とか「きっちりした帳票を作りたい！」ってときにピッタリ。
自分好みのレイアウトにできるのが魅力です。
ただし、関数が増えるとメンテナンスはちょっと面倒かも。
Power Queryを使うパターン
データの量が多いときや、毎月のように繰り返し作業があるときに大活躍！
一度仕組みを作っておけば、あとは「更新」するだけでOKなのがうれしいポイントです。
ピボットテーブルを使うパターン
とにかくサクッと集計したい！ってときはこれ！
関数いらずで簡単にまとまるので、スピード重視派におすすめ。
ただし、帳票っぽく見せたいときはちょっと工夫が必要です。
どれが一番ってわけじゃなくて、「どんな帳票を作りたいか」「どれくらいの作業量があるか」で選べばOK！
状況に合わせてうまく使い分けていきましょう！
いくつか方法があるなんて知らなかった！いいこと聞いちゃった！
早速作ってみようと思います！
