久々の投稿です。
最近、TEXT関数の便利さに気づき色々と活用しています。
TEXT関数を使うことで以前はできなかった事を実現できるようになってきました。
本日紹介するものもその1つです!

まずは、実現したいことの説明から。

excel01-201703

excel02-201704

こんな感じに月別に「YYYYMM」のシートを作成した際に、前月分の値との差分(黄色のセルの部分)の算出が容易に行えるようになります。

TEXT関数の便利さを知る前は、下記のように毎回、黄色のセル内の数式でシート名(YYYYMM)の部分を書き直していましたが、

=B2-‘201612‘!B2

これから説明する方法を使えばその処理が不要となります。

それでは早速レシピの紹介です。

  1. シート名の取得
    これは以前に記事にもしていますが、下記のおまじないを入力してもらえばOKです。
    =RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1)))
    excel03-sheetname
  2. 取得したシート名の1か月前を算出
    ここが今回のレシピの肝となります。
    =TEXT((MID(E2,1,4)&”/”&MID(E2,5,2)&”/01″)-1,”yyyymm”)
    excel04-text分解して説明すると、
    MID(E2,1,4)&”/”&MID(E2,5,2)&”/01″)
    で、「1」で取得したシート名の年月の1日を取得しています。(図の例だと「2017/04/01」)
    そこから「-1」することで、1日前の年月日を取得できます。(図の例だと「2017/03/31」)
    それをTEXT関数で「yyyymm」に直すことで、1か月前のシート名を取得できます。(図の例だと「201703」)
  3. 1か月前のシート名を元に対象シートから値を取得
    ここまでくればあとはINDIRECT関数を使うことで、別シートの値を動的に取得してくることができます。
    =B2-INDIRECT($F$2&”!B2″)
    excel05-indirectちなみにここで1点注意が必要で、上記の「”!B2″」の部分はExcel君にはただの文字列としか認識されないので、C2セルをコピーしてC3セルに張り付けても「”!B2″」が「”!B3″」のように自動変換されることはありません。
    下記のように1行ずつ書き直す必要があります。
    excel06-indirect2少々面倒ですが、1回セットすればそれ以後は必要ないので、ミスの無いようご対応ください。

ここまで設定すれば、今後、201705、201706・・・と毎月シートを増やしていく際も基本的にシート名を変更して残高部分を見直すだけで済むので作業効率がグンっとUpするはず!?です。

以上、ご参考になれば幸いです。