今回は関数だけでシート名を動的に指定する方法です。
この方法は、同じフォーマットの表が継続的に続き、一定のルールで前のシートの値を参照するような場面に使えます。
具体的には、売上管理や人件費管理、予算管理など、ある一定期間で、比較参照するデータがある場合に適してますね。
私も実際によく使いました。覚えておくと便利ですよ！
それでは、売上前年比だけを算出するサンプルを作成しながら説明していきます。サンプルは次のように作成します。
このように、年月でシート管理されている表の、年と月を変えると、万年カレンダーが変化し、それに合わせて対応する前年数値が変化するものを作成します。
この緑色の箇所、万年カレンダーを制御している関数は、過去記事でやってますので、サラッと式の説明だけにします。詳しく知りたい方は、過去記事の万年業務カレンダー（日付固定）をご覧ください。
まず、日付からいきます。表の上の部分、年と月を変えると、その該当月の日付だけ表示させるようにしてます。月によって月末数字が変化するというわけです。
関数式が入っているのは、「２」以降のセルA6より下です。「１」は常に「１」なのでベタ打ちです。
セルA6には、次のように入力しています。
=IF(A5="","",IF(DAY(DATE($A$2,$E$2+1,0))<A5+1,"",A5+1))このまま表の一番下までオートフィルです。
式の意味としては、
「自分より1つ上の数字に＋１をして、月末数字以下なら表示、それ以外は空白を表示する」
となっています。ポイントは月末数字を求める、「来月の0日は月末」というDATE関数の使い方です！
お気づきの方もいらっしゃると思いますが、この式、、、ここではセルA6から入れいますが、本当は、28日以降だけでいいです。なぜなら月の数字が変化するのは28日以降だけですからね。なので2〜27は、１と同じくベタ打ちでもいいです。
まあ、、、これは好き好きなのですが、、、、私は、月に１回だけ整備するような表だと、どこをどうやって作ったのか忘れてしまう（笑）ことが多いので、同じように表記できる箇所はなるべく同じにて、自分が見返しやすいようにしているんです。
なので、、、、みなさんも自分が管理しやすいように、お好きな方法でどうぞ！（←大事！）
そして、次は曜日の表示です。
ここはB列全部関数入ってます。一番上のセルB5は次のようにしています。
=IF(A5="","",TEXT（DATE($A$2,$E$2,A5),"aaa"))これもこのまま下方向に一番下までオートフィルです。
曜日の表示方法は、本当〜〜にたくさんあります。ここでは値を文字列で扱える方法にしました。
余計なお世話（笑）なのかもしれませんが、売上管理の場合、文字列の方がいいと思いますよ。売上って結構な頻度で曜日特性を出したりすると思うんですね。そんなときに使う関数、SUNIFとかAVERAGEIFとか、、、、ここが文字列で絞れるので、使いやすくなります。
当然、日付からWEEKDAY関数使って、数値で絞り込む方法もありです。でも、、、、、この方法だと作成する人や部署によって日曜始まりか月曜始まりかが違ったりしませんか？？確認しようとしたとき、いちいち辿るの面倒くさい！！（笑）
はい、いろいろ書きましたが、、、、今回のサンプルは、この曜日が他の関数に影響することはありませんので、日付と同じく、みなさんのやりやすい、お好きな方法でどうぞ！(←結局これが一番大事！）
さて、次は前年の同曜日の日付を導き出す方法です。
これは関数というより、考え方がポイントですね。
まずは月初だけで考えてみましょう。
図にまとめました。下の図を見てください。
いかがでしょうか？
このように、前年と今年の月初の曜日をWEEKDAY関数で数値にして、その差を出せば、ズレの日数が分かります。そのズレを起点になる日からズラしてあげればいいわけです。やっていることは簡単ですね。
ただ、残念ながら、まだ式がちょっと不完全でして、このままでは使えません。なぜかと言いますと、上の図の前年と今年の曜日の関係が逆になる場合があるからです。
例えば、今年が日曜日で、前年が土曜日の場合、先程の式では、今年から前年を引いてますので、１−７となり、−６のズレとなってしまいます。でも実際は土曜日と日曜日って1日しかずれてませんね。
なので、そのような時には、次のように考えます。
はい、このように逆転した場合は７を足せばいいんです。わかってしまうと単純なことですね。
それでは！これを式にしていきたいところですが、、、、まだ考えておかねばならないことがありまして、、、
実は、この後の処理で、日付の数字だけだと困る点がでてくるんです。
何に困るのかといいますと、、、、月末の箇所を見ながらご説明します。
このように、月初がズレてるので、そのまま月末は翌月にズレ込みます。
月が変わるので、参照先のシートも同じく変えなくてはなりません。
これをあとで万年カレンダーと連動させるわけですが、日付数字だけですと、少し面倒くさくなるんです。なので、少しでも簡単にするため、ここの値は日付としておきます。なので、C列の表示形式は「d」だけに設定をお願いします。
（表示形式 について、詳しくは過去記事関数で万年カレンダー（曜日固定）をつくってみるをご覧ください）
それでは、セルC5に入る関数です。
=IF(A5="","",DATE($A$2-1,$E$2,$A5)+WEEKDAY(DATE($A$2,$E$2,1),1)-WEEKDAY(DATE($A$2-1,$E$2,1),1)
+IF(WEEKDAY(DATE($A$2,$E$2,1),1)-WEEKDAY(DATE($A$2-1,$E$2,1),1),7,0))このまま最後まで下方向にオートフィルです。
関数式は、ちょっと長いですが、先ほど図で説明したことをやっています。
前年の起点日に、今年と前年の曜日のズレを足し、もし、前年と今年の曜日の配置が逆転していたら、さらに７を足すということをしてます。
素直に月末あたりの計算結果をみると、3月32日になるなど、ありえない日付になってますが、、、日付計算の結果というのは、しっかり正規の日付として表示されるようになっています。おそらく、PC側は起点日のシリアル値から計算しているからでしょう。。。。本当のところは知りません（笑）
はい、やっと本題に入ります。シートを動的に指定できるようにします。
使う関数はINDIRECT関数です。
このINDIRECT関数は文字列をセルの参照に変えてくれる関数です。
関数の使い方は過去記事でも書いてますが、、、その記事が長すぎてリンクつけても辿り着けない（笑）と思いますので、ここでも説明しようかと思います。下の図をみてください。
このようにセルC1をINDIRECT関数で指定すると、セルC1に入力されている「A1」という文字列が「セルの参照」に変換してくれるので、結果はセルA1に入力されている「100」になるという仕組みです。
使い方ですが、
＝INDIRECT（文字列,セルの参照形式）
となっています。
このセルの参照形式というは、A1形式とR1C1形式が選択できるというものです。省略もしくはTRUEとするとA1形式、FALSEとするとR1C1形式になります。
A1形式というのは、A1とかB1とC3とか、、、みなさんもよく使っている、通常のアルファベットと数字でセルの参照を表す形式のことです。
そして、今回は馴染みが薄い、R1C1形式使います。これは大昔の参照形式で、RはROW（行）CはCOLUMN（列）の略です。これの便利なところが、RとCのあとに数字をいれれば、座標指定でセルが参照できるので、計算に使えるという点ですね。
確か、Excelの前身、Microsoftスプレッドシートというソフトでは、この方法だけしか使えなかった気が、、、、、まあ、昔のことはどうでもいいです（笑）
では、順番に説明していきます。
まず、前年数値の参照先を考えていきます。
ちょうどいいので、先ほど見たシートの参照が変わる月末ポイントで見てみましょう。
本年の3/19と3/30の前年数値の参照先は上のようになります。
次に、この参照先の数値部分をシートにある数値で表してみます。まずは3/19だけみてみます。
このようになりました。
このCOLUMN関数を使って表している、最後の「６」ですが、列の指定なので、縦方向の行番号はどこでもいいです。$F$1にして絶対参照にしてもいいですし、上の図のように相対参照で表示シートと同じ場所でもよいです。
また、COLUMN関数を使わずに「６」とベタ打ちでもいいです。列は日付が変わっても絶対にかわりませんので問題ありません。
では、なぜここでわざわざシートの列を使っているのかと言いますと、フォーマットの列が削除、挿入されて変更された場合、関数がくずれにくいという理由からです。ただ、崩れにくいだけで、参照先シート、表示シートのどちらかだけ変更されれば崩れてしまいます。。。。まあ、こういった連続する管理表は全体のフォーマットが変わることが多いと思われるので、せめてそれぐらいは対応しておこうってことですね。
それでは、3/30の参照先シートが変わるポイントはどうでしょう？
3/19と同じ関数で見てみます。
このように、C列が前年日付データになっているおかげで、先ほどと同じ関数でできました！先ほど上で説明しましたが、日付数字だけだと面倒になると言ったことは、ご理解いただけたかと思います。
それではこれを関数式にして、表の前年数値の一番上、セルD5に入れるように書いてみると、こうなります
=IFERROR(IF(C5="","",
INDIRECT( YEAR($C5) & "年" & MONTH($C5) & "月!" & "R" & DAY($C5)+4 & "C" & COLUMN(F5),FALSE))
,"") 参照シートがない場合や日付データが空白だった場合、エラー表示がでますので、大外に回避する条件分岐をつけてますが、INDIRECT関数の箇所は上で説明した通りです。
また、先ほど「６」をベタ打ちでもいいと書きましたが、その場合、表示形式をA1形式にすれば、下のように少し簡単にかけます。
=IFERROR(IF(C5="","",
INDIRECT( YEAR($C5) & "年" & MONTH($C5) & "月!" & "F" & DAY($C5)+4))
,"") さらに、ADDRESS関数を使って参照するセルの文字列を作成する方法もあります。
こうなります。
=IFERROR(IF(C5="","",
INDIRECT( ADDRESS( DAY($C5)+4,COLUMN(F5), , ,YEAR($C5) & "年" & MONTH($C5) & "月")))
,"") この方法は、「６」をベタ打ちしない方法と同じ結果になり、フォーマットが変わっても連動できます。
ADDRESS関数は、行番号と列番号で指定したセルの場所を文字列で表示するという関数です。
使い方ですが、
ADDRESS(行番号, 列番号, 絶対・相対参照の指定, セルの参照形式, シート名)
行、列番号以外は省略可能で、シート名は省略するとアクティブシートのことになります。
絶対・相対参照の指定は、関数の結果に$がつくかつかないかの違いです。結果をさらに加工する場合は必要ですが、今回は結果をそのまま指定アドレスとするのでどちらでも問題ありません。
セルの参照方法はさきほとのINDIRECT関数と指定方法も意味も一緒ですが、INDIRECTは指定する方法、ADDRESSは表示する文字列となっています。なので、今回は指定するINDIRECTがA1形式なら、それに合わせてADDRESS側もA1形式に設定すればよいです。
このどの方法でも下方向にオートフィルして使います。
いつも同じこと書いてますが、、、、このように、同じことさせるにも、たくさんの方法があります。
どれをチョイスするのかは、もう好みです（笑）
そして、自分が一番管理しやすい方法をとりましょう！
コピペして使うにしても、コピペ元からすぐにリカバーできるような工夫が欲しいですね〜
このサンプルのような場面のINDIRECT関数を使うなら、私はADDRESS関数を入れる方法を使います。
私、よくシート記号の「！」（エクスクラメーション）忘れちゃうんですよ（笑）
その点、ADDRESS関数は純粋にシート名だけなので、「！」は不要なんです（笑）
理由なんてこんなもんかと（笑）
さて、これで完成になります。今現在、毎回シートを作る手間に悩まされてる方がいらっしゃるなら、この方法は、一気に入力が楽になると思います。ぜひ、参考にしてみてはいかがでしょうか？
逆に、月１回程度しか整備しない書類に、わざわざこんな面倒なことするより、手入力した方が早いよ、、、、と、言う方もいらっしゃるかな。。。。
でも、まあ、せっかくPCにデータが入れてあるなら、紙ベースみたいなことしないで活用しなきゃ！って思うんです。
今後、管理拠点が増えた時用に、作ってみてはいかがでしょうか！？
と、ここまで書いてきましたが、、、、私個人の考えとしては、こういったデータの運用は、シートで管理するのではなく、できればデータ一覧にして、しっかり検索、集計ができる形のデータベースにするのが一番よいと思います。
永続的に営業、業務が続いてゆく中、このように日々累積していくデータは、様々な分析をすることによって、やっと資源化すると思うんですよ。
でも、、、けっこう会社って、こういったシート管理、もっとヒドいとファイル管理になっていることが多いことないですか？続いていくデータがブツ切れに管理されていて、使うときにわざわざ引っ張り出してつなげるみたいな。。。。
まあ、私がたまたまそういう場面に当たっているだけかもしれませんが、、、（笑）
そういったことが多いので、関数でごちゃごちゃやって、なんとかつなげるスキルがついちゃうって、、、、どうよ？って思います。（笑）
それでは、ここまで読んでいただきありがとうございます。
2024年3月31日追記
この記事の方法だけだとシート名に半角スペースが入ると参照できません。下の記事で回避方法書きました。よかったらこの後に読んでいただければ、、、
