1か月前のシートの値と比較(TEXT、INDIRECT関数)

久々の投稿です。
最近、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するはず!?です。

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

サブコンテンツ

公開中のAndroidアプリ(全て無料です)

ビジネス

おじさんのメモ帳 おじさんのメモ帳
シンプルなメモ帳
日報 日報
日報作成補助ツール
議事録 議事録
議事録作成補助ツール

学習(国語・算数)

漢字かな 漢字かな
漢字の読みの学習に
ひらがなプラス ひらがなプラス
かな・カナの学習に
暗算百問 暗算百問
毎日暗算で頭の体操
あんざん25 あんざん25
両手で計算してみよう

学習(理科・社会)

記号かな 地図記号
地図記号の学習に
魚かな 魚かな
海の生物の学習に
動物かな 動物かな
陸上の生物の学習に
野菜かな 野菜かな
植物な食物の学習に
元素記号 元素記号
元素記号の学習に

その他

こづかい帳 こづかい帳
計算力と金銭感覚を育もう
お絵かきプラス お絵かきプラス
メモや絵日記等に

このページの先頭へ