エクセル自動の年月

ワークシートの名前を
R5.4～R6.3と入力したときに
B2に R5.4
B14に R5.5
B26に R5.6
B38に R5.7
B50に R5.8
B62に R5.9
B74に R5.10
B86に R5.11
B98に R5.12
B110に R6.1
B122に R6.2
B134に R6.3
と出したいのですが関数とかで表示できるのでしょうか

No.5 回答者： goomania 回答日時： 2022/10/30 17:15

ご質問者のいう、

>ワークシートの名前をR5.4～R6.3と入力したときに
というのは
(1)「R5.4」、「R5.5」･･･、「R6.3」というシート名の12個のシートを作成したとき
(2)「R5.4～R6.3」というシート名の1個のシートを作成したとき
のどちらでしょうか?
(1)ならfujillinさんの回答で解決していると思います。
以下は(2)としての回答になります。

すでにmike_gさんが、シート名を取得する方法について回答されておられます。

ただ、大変失礼とは存じますが、mike_gさんの回答の数式だとB2セルに記述してオートフィルでコピーしたときB62セルまではよいのですが、B74セルに「R6.0」と表示されます。これは、ご質問者のいう「R5.10」とは異なり、以降のセルもご質問者のご希望とは一致しないような気がします。

そこで、別案をお示しします。
添付画像を御覧ください。
mike_gさんのご説明のように、作業用のセルを使用すると数式が短くなるのですが、作業用セルを使用していないため数式がとても長くなっています。

ご質問者のご希望を叶える数式として、B2セルに

=IF(MOD(ROW(A1),12)=1,TEXT(EOMONTH((LEFT(TRIM(MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename")))),FIND("～",TRIM(MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename")))))-1)&".1")*1,(ROW(A1)-1)/12),"[$-411]ge.m"),"")

という数式を記述し、これをオートフィル等で下方向へ必要な行までコピーすればよいと思います。

また、mike_gさんがご指摘のように、B3:B13、B15:B25、B27:B37、…、B123:B133 の各範囲の11行は、すべて空白セルなのか、それとも、何かほかの用途で使用するセルなのか不明です。

上記数式はオートフィルでコピーしたとき空白になるようにしていますが、他の用途で使用する場合は、B14、B26、B38、･･･、B122、B134に個別に上記数式をコピーすることになると思います。

また、上記数式ではシート名の両端に万一「空白」があるとシート名から表示値を計算するのに支障があるので、TRIM関数で除去しています。

さらに言えば、R5.4は「令和5年4月」を意味するものと解釈してTEXT関数で"[$-411]ge.m"という書式で表示させています。

これで表面的にはご質問者への回答にはなるのですが、実務の世界では、表示させた年月「R5.4」をさらに参照して別セルの計算に使用する、などということがよくあります。

こういった場合は、TEXT関数で文字列にしてしまうと不便なこともあります。この場合は、B2、B14、B26、B38、･･･、B122、B134の各表示セルの書式をユーザー定義で「[$-411]ge.m」とした上で、TEXT関数を外して、B2セルに

=IF(MOD(ROW(A1),12)=1,EOMONTH((LEFT(TRIM(MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename")))),FIND("～",TRIM(MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename")))))-1)&".1")*1,(ROW(A1)-1)/12),"")

という数式を記述し、これをその他の対象セルへコピーすると、見た目は「R5.4」のように表示され、中身はシリアル値(対象年月の月末日)になります。

注意事項としては、上記数式はシート名の「～」の前の「和暦年月のローマ字表記」しか見ていないということです。従って「～」より後の表記がルールから逸脱していても何のチェックもされません。

つまり、本来「R5.4～R6.3」というシート名にすべきところ「R5.4～R6.4」としたとか、うっかりRの右のTをタイプしてしまい「R5.4～T6.3」などとしてしまってもチェックされず、B2セルに「R5.4」が表示され、12行ごとに翌月の「和暦年月のローマ字表記」が表示されるということになります。

逆に、「～」の前の「和暦年月のローマ字表記」が日付を付加してEXCELで和暦年月日と解釈できないものはNGで、エラーになります。
例えば、「R5-4～R6-3」はOKですが、「R5_4～R6_3」はNGです。

No.4 回答者： mike_g 回答日時： 2022/10/30 00:39

ついでながら、アクティブ・シート名“R5.4～R6.3”は次式で得られます。

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,10)

当該式をセル B1 に入力しておけば、5.4、6.3 は次式で得られようかと。
5.4 ← =RIGHT(LEFT(B1,FIND("～",B1)-1),LEN(LEFT(B1,FIND("～",B1)-1))-1)
6.3 ← =RIGHT(MID(B1,FIND("～",B1)+1,9),LEN(MID(B1,FIND("～",B1)+1,9))-1)

No.3 回答者： mike_g 回答日時： 2022/10/30 00:29

次式を入力したセル B2 を下方にズズーッとオートフィル、てのは如何でしょうか？

=IF(ROW(A1)>12*10-11,"",IF(MOD(ROW(A1),12)=1,"R"&TEXT((5.4+0.1*INT((ROW(A1)-1)/12)),"0.0"),""))

No.2 回答者： mike_g 回答日時： 2022/10/29 23:42

確認させてください。

B3:B13、B15:B25、B27:B37、…、B123:B133 の各範囲の11行は、
すべて空白セルですか？それとも、
B2:B13、B14:B25、B26:B37、…、B122:B133 の各範囲の12行は、
それぞれ R5.4、R5.5、R5.6、…6.2 の同データでも構いませんか？

No.1 回答者： fujillin 回答日時： 2022/10/29 16:11

こんにちは

通常の関数では、他シートのシート名を取得することはできないと思います。

ユーザ定義関数を利用すれば可能です。
1番目から12番目までのシート名を順に参照するだけなら、比較的簡単にできるでしょう。

ただし、どこにどのような順で並んでいるのかが不明の場合には、
　・全シートから和歴表示に該当するシートを取得
　・並べ替えて、順に表示
のような処理を行わないとならないので、それなりに知識が必要になるでしょう。


例えば、以下の3行を標準モジュールに記述しておいて、
Function shname(n As Long) As String
shname = Worksheets(n).Name
End Function

セルに
　=shname(2)
などと入力すれば、2番目のシート名が表示されます。
「2」の部分を可変にすれば、それに応じたシート名が表示されることになります。