Excelのシート間集計を効率化する実践テクニック

利用シーン

フォーマットが揃った複数のシート(営業所別シートや年度別シート)を一覧シートに集計したい。経理事務ではよく目にする光景だ。ここで、手作業でシートが追加されるたびにリンクを張り直す方法を取ってはいないだろうか。もしそうなら、大幅に時間短縮が図れる方法があるのでお伝えしたい。

イメージとしては、

各年度のシート(項目A〜Fの月次合計(トータルはN列目))を一覧(集計シート)に項目別、年度別にまとめる、下記のイメージだ。

(各年度)

(集計)

シート間の集計に使えるTipsとしては

  1. Indirect関数
  2. Vlookup関数
  3. シート名置換

Indirect関数を使った集計効率化

各シートで集計項目のセル位置が同じであるなら、Indirect関数を使ったシート間集計が便利だ。

Indirect関数の説明

Indirect関数によるシート間集計の使い方については、このページがわかりやすい。

https://kokodane.com/2013_kan_032.htm

Indirect関数(おすすめ)

集計したいシートを追加して、シートの名前を変更、コピベすればもう作業完了。もはや一瞬で終わると言っても過言ではない。では、実際の使い方を説明していこう。

  1. 集計対象シートの名前を分かりやすい2017、201703などの一意の名称に変更しよう。集計シートの列タイトルにするのが便利。indirectでタイトル行を直接指定できる。
  2. 表の右側の橋に、集計対象シートのセル番地を記載する。例えば、項目Aであれば、N4、B項目であればN5となる。これはオートフィルで簡単にコピベできる。
  3. 集計シートの集計セルに、=iNDIRECT(B$3&”!”&$E4) と入力する。$の位置に注意して、コピペ。

    タイトル行の行固定+!(ビックリマーク)+2番の対象シートのセル位置を連結している。ダブルクオテーション(””)は文字を&で連結するときには使用する合図みたいなものだ。
    各セルの計算式は以下を参照

Vlookup関数(項目の順序が違うときに)

Vlookup関数はシートごとに項目は同じであるが、並び順が違うときに主に使う関数である。詳細な使い方はvlookup関数の実践的な使い方を参照されたい。

シート名の置換で参照するシートを置き換える方法

この方法は、若干力技。集計シートの1列目には手動でリンクを張り、次列以降はコピー、そうするとシート名称は変わっていないから、それを正しく置換により置き換える。急いでいるけど、indirect関数が上手く行かないときは、これで無理やり変えてしまおうという、バックアップ 策に近い。

まとめ

Indirect関数はフォーマットが定まっているシートを集計することに非常に向いている。また、シートが増加した際にもシート名称、タイトル行を変更するだけで対応でき、これまた優秀である。ぜひ、Indirect関数にチャレンジしてもらいたい。

集計作業は早く終わらせて、健康のため早く家に帰って、休んでください。

ではでは、また次回もお願い致します。

関連記事