月別・店舗別に分かれたシートを、月末に1枚へ転記して集計する――。転記・集計は中小企業の表計算作業でいちばん多い困りごとのひとつです。「複数シートを1枚に」と言っても、やりたいことは大きく2種類あります。①同じ形のシートの数値を合計したい、②シートを縦につないで1つの一覧にしたい。方法が違うので、まず自分のケースを確認してください。
各シートのレイアウトが完全に同じ場合、3D参照で一括合計できます。集計シートのセルに次のように入力します。
=SUM(1月:12月!B2)
「1月」から「12月」までの全シートのB2を合計します。対象シートはブック内で連続して並んでいる必要があります。途中に集計シート自体を挟まないよう、シートの並び順に注意してください。
シートごとに項目の並びが少し違う場合は、標準機能の「統合」が使えます。
行を積み重ねて1つの表にしたい場合はPower Queryが本命です。一度作れば、元シートが増えても[すべて更新]で再集計できます。
Microsoft 365のExcelなら、関数でも縦結合できます。結果はスピル(自動で広がる配列)として表示されます。
=VSTACK(東京!A2:C101, 大阪!A2:C101, 福岡!A2:C101)
見出し行を含めるかどうか、行数がシートごとに違う場合は範囲の取り方を工夫してください。
| 方法 | 向いているケース | 備考 |
|---|---|---|
| 串刺し集計 | 同じ形のシートの合計 | 一番手軽。シートの並び順に依存 |
| 統合 | 項目の並びが違うシートの合計 | 標準機能。手動実行が必要 |
| Power Query | 縦結合して一覧化、毎月繰り返す | 更新ボタンで再実行できる |
| VSTACK | 縦結合(Microsoft 365) | 関数だけで完結する |
参照:Microsoft サポート(3D参照による集計、統合機能、Power Query、VSTACK関数)。
「毎週の転記・集計をなくしたい」「CSVの整形を自動化したい」「古いGASを直したい」といったご相談を承っています。1ファイル・1処理の小さな作業から対応し、作業前に必ずメッセージで内容を確認して、対応範囲と金額を先にお伝えします。検証記録と手順書付きで納品します。
相談してみる(Coconala)※出品ページは準備中です。公開まではこのボタンは仮のリンクです。