┃集計もできる！シートを自在に操る技④

あけましておめでとうございます。
今年もよろしくお願いします。

INDIRECT関数を使って集計する、少し実践的な問題を考えてみてください。



【問題８】難易度★★★

月別シート(yymm)があります。
各シートのA列を合計して、「まとめ」シートに表示してください。
シートは大量にあることを前提として考えてください。


【問題８ 解答】
それでは解答をひとつずつ解説します。

Excelで年月を扱う時はシリアル値を利用しましょう。
Excelにおける日付をきちんと理解していない人が多いので、必ず理解してください。
日付と表示形式の関係は実務で必須です。

まずは「まとめ」シートで年月日を用意します。
翌月を求めるときはEDATE関数を使うと便利です。

=EDATE(B1,1)


TEXT関数を使ってシート名を作成します。
このようにすると「1712」シートと「1801」シートを連続して作ることができます。

=TEXT(C1,"yymm")


参照文字列を作成します。
「A:A」はA列を参照する、という意味です。

=C2&"!A:A"


「参照文字列」をINDIRECT関数で「参照」に変えて、SUM関数で合計します。

=SUM(INDIRECT(C3))



各シートの合計をまとめシートに表示することができました。
シートが増えても列をコピペするだけで対応可能です。



INDIRECT関数を使うと、参照エラー(#REF!)で数式が壊れないというメリットもあります。
セルを直接参照していないので、セルが削除されても常に同じ番地を参照してくれるのです。
シートを削除すれば参照エラーになりますが、シートが復活すれば参照エラーはなくなります。

SUMIF関数やVLOOKUP関数の中でもINDIRECT関数を使うことができます。
数式が長くなりがちなので、参照文字列は別セルで作成しておくとメンテナンスがしやすくなります。
可読性を高める工夫をしてください。

HYPERLINK関数も組み合わせて使うと、検証可能性も増します。
ブック内でハイパーリンクをしたい時は、参照文字列の前に「#」をつけます。
ハイパーリンク先から戻るときはALT＋←を使いましょう。

=HYPERLINK("#"&C3,SUM(INDIRECT(C3)))