Excel。INDIRECT関数でシート名を見出しにした別シートの表に集計。だけど、日付だとエラーになるのでどうしたらいいの？
＜SUM+INDIRECT関数&TEXT関数＞
各月ごとに店舗集計されたシートがあります。
それぞれ、シート名は、1月・2月・3月としてあります。
そして、集計するシートが、売上集計シートです。
この売上集計シートのB2には、1月の合算値を算出して表示したいわけです。
シートごとに合計値を算出して、セル参照でもいいのですが、どのみちセル参照の作業が必要ならば、セル参照も合計値を求めることも、まとめて出来れば作業効率がいいわけですね。
しかも、その式をオートフィルでコピーできれば、さらに便利ですね。
通常ならば、売上集計のB2の数式は次のように作るはずです。
=SUM('1月'!B2:B6)
シート名の「1月」が入っているのはいいのですが、この数式をオートフィルを使って数式をコピーしても、シート名は勝手に、2月・3月と変わりませんから、当然おかしな数値が計上されてしまいます。
このぐらいのデータなら、根性をいれればどうにかできないこともありませんが、たいへんです。
そこで、オートフィルを使って数式をコピーするには、どうしたらいいのか、考えると、自動的に、引数内の1月が2月と変わってくれればいいわけですね。
そこで、登場するのが、「INDIRECT関数」です。
この関数は、文字を直接使用することができるという、知っていると、とても便利な関数です。
準備としては、A2:A4にシート名と同じ文字を入力しておきます。
では、INDIRECT関数を使った数式を作って確認してみましょう。
B2の数式は、
=SUM(INDIRECT(A2&"!b2:b6"))
合計値を算出したいので、SUM関数を使います。
引数内のINDIRECT(A2&"!b2:b6")は、A2は1月という文字だとイメージします。
「1月」という文字に「!b2:b6」を結合しますので、「&」を使って結合させます。
あとは、オートフィルを使って数式をコピーすれば、完成です。
このINDIRECT関数は便利なのですが、シート名が次のようになると、工夫が必要になります。
シート名の「1月」を「2020年1月」と変更します。
A2も「2020年1月」と変更してみると、「#REF!」というエラーになります。
シート名もセルも2020年1月なのに、エラーになってしまったのでしょうか？
その原因は、INDIRECT関数は『文字』を参照します。シート名は2020年1月という文字なのですが、A2は、2020/1/1と日付でExcelは認識しています。
つまり、A2は文字ではなくて日付になってしまったので、エラーになったわけです。
【TEXT関数を使って日付を文字に】
日付を文字列に変更するといいわけですね。このような場合に登場する関数が「TEXT関数」です。TEXT関数は、表示形式を設定できる関数です。
では、数式を次のように修正することで算出することができます。
=SUM(INDIRECT(TEXT(A2,"yyyy年m月")&"!b2:b6"))
このように数式を修正することで、シート名が日付でも対応することができます。
INDIRECT関数は便利なのですが、ちょっと注意が必要なケースもあります。
