Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
「日付の新元号対応」について質問されたperiodayoさんへ
投稿日時: 19/04/06 22:58:18
投稿者: xls-hashimoto

「2019/5/1と入力しても平成31年5月1日となります」
 
とのことですが、「令和元年5月1日」と表示させたいのなら
「セルの書式設定」−「表示形式」−「ユーザー定義」で
「種類(T)」を
 
[<43586]ggge"年"m"月"d"日";[<43831]"令和元年"m"月"d"日";ggge"年"m"月"d"日"
 
とすれば、「令和元年12月31日」までは表示できます。
「令和2年1月1日」以降は Microsoft の対応を待つしかありません。
 
[43586]は、「令和元年5月1日」の日付連番です。
[43831]は、「令和2年1月1日」の日付連番です。
 
[<43586]ge"年"m"月"d"日";[<43831]"R元年"m"月"d"日";ge"年"m"月"d"日"
[<43586]yyyy(ge)/m/d;[<43831]yyyy("R元")/m/d;yyyy(ge)/m/d
など必要な書式を設定して、年数の無い「元」年表示ができます。
 
ただし、大正・昭和・平成は「1年」と表示されます。
これらを「元年」表示しなければならないときは、
Excel2013を使い「条件付き書式」を設定するしかないと思います。

回答
投稿日時: 19/04/09 12:41:58
投稿者: 角田
投稿者のウェブサイトに移動

ユーザー定義関数になりますので
「日付データ」セルとは別のセルへの表示でも良ければ
「令和」対応日付変換関数(EraFormat/EraCDate/EraIsDate)を公開しています。
http://addinbox.sakura.ne.jp/Excel_Tips28.htm
( "元年"表記にも対応しています )
 
暫く(今後1年くらい?)は、
「令和」対応済PC未対応PC混在
問題になりますね。

投稿日時: 19/04/09 19:29:39
投稿者: xls-hashimoto

「日付データ」セルとは別のセルへの表示で良く「元年」表示をしたければ、
計算で表示させる方法もあります。
 

日付連番	日付データ	表示データ
[0]	明治33年1月0日	明治33年1月0日
[4595]	大正1年7月30日	大正元年7月30日
[4750]	大正2年1月1日	大正2年1月1日
[9856]	昭和1年12月25日	昭和元年12月25日
[9863]	昭和2年1月1日	昭和2年1月1日
[32516]	平成1年1月8日	平成元年1月8日
[32874]	平成2年1月1日	平成2年1月1日
[43586]	平成31年5月1日	令和元年5月1日
[43831]	平成32年1月1日	令和2年1月1日

 
C2
=IF(B2<4595,"明治"&(YEAR(B2)-1867),
   IF(B2<4750,"大正元",
     IF(B2<9856,"大正"&(YEAR(B2)-1911),
       IF(B2<9863,"昭和元",
         IF(B2<32516,"昭和"&(YEAR(B2)-1925),
           IF(B2<32874,"平成元",
             IF(B2<43586,"平成"&(YEAR(B2)-1988),
               IF(B2<43831,"令和元",
                 "令和"&(YEAR(B2)-2018)))))))))
 &"年"
 &MONTH(B2)&"月"
 &DAY(B2)&"日"
下にコピー
 
式は、見やすいように改行とインデントを付けてあります。
このまま、式をコピーして貼り付ければ入力できます。
 
改行とインデントをしないと、次の様な式になります。
 
C2
=IF(B2<4595,"明治"&(YEAR(B2)-1867),IF(B2<4750,"大正元",IF(B2<9856,"大正"&(YEAR(B2)-1911),IF(B2<9863,"昭和元",IF(B2<32516,"昭和"&(YEAR(B2)-1925),IF(B2<32874,"平成元",IF(B2<43586,"平成"&(YEAR(B2)-1988),IF(B2<43831,"令和元","令和"&(YEAR(B2)-2018)))))))))&"年"&MONTH(B2)&"月"&DAY(B2)&"日"
 
最近は、この様な式の入力は目で追えないので避けています。
 
「元年」表示ではなく「令和1年」表示で良ければ
 
C2
=IF(B2<43586,
   TEXT(B2,"ggge"),
   "令和"&(YEAR(B2)-2018))
 &"年"
 &MONTH(B2)&"月"
 &DAY(B2)&"日"
 
と短い式になります。

投稿日時: 19/04/09 21:10:35
投稿者: xls-hashimoto

元年」表示で式を短くするには、次の様な方法もあります。
 

日付連番	日付データ	表示データ
[0]	明治33年1月0日	明治33年1月0日
[4595]	大正1年7月30日	大正元年7月30日
[4750]	大正2年1月1日	大正2年1月1日
[9856]	昭和1年12月25日	昭和元年12月25日
[9863]	昭和2年1月1日	昭和2年1月1日
[32516]	平成1年1月8日	平成元年1月8日
[32874]	平成2年1月1日	平成2年1月1日
[43586]	平成31年5月1日	令和元年5月1日
[43831]	平成32年1月1日	令和2年1月1日

 
C2
=IF(AND(4595<=B2,B2<4750),"大正元",
   IF(AND(9856<=B2,B2<9863),"昭和元",
     IF(AND(32516<=B2,B2<32874),"平成元",
       IF(AND(43586<=B2,B2<43831),"令和元",
         IF(43831<=B2,"令和"&(YEAR(B2)-2018),
           TEXT(B2,"ggge"))))))
 &"年"
 &MONTH(B2)&"月"
 &DAY(B2)&"日"
 
いずれにしても、Microsoft の対応が待ち遠しいですね。[/code]

回答
投稿日時: 19/04/09 21:33:30
投稿者: んなっと

こんなのも?
暫定的な数式で、すぐに無駄(邪魔?)になると思います。
 
      A         B   C
 1  M33.1.1  明治33年1月1日   1
 2  M45.7.29  明治45年7月29日  4594
 3  T1.7.30  大正元年7月30日  4595
 4  T1.12.31 大正元年12月31日  4749
 5   T2.1.1   大正2年1月1日  4750
 6 T15.12.24 大正15年12月24日  9855
 7  S1.12.25 昭和元年12月25日  9856
 8  S1.12.31 昭和元年12月31日  9862
 9   S2.1.1   昭和2年1月1日  9863
10  S64.1.7  昭和64年1月7日 32515
11   H1.1.8  平成元年1月8日 32516
12  H1.12.31 平成元年12月31日 32873
13   H2.1.1   平成2年1月1日 32874
14  H31.4.30  平成31年4月30日 43585
15  H31.5.1  令和元年5月1日 43586
16 H31.12.31 令和元年12月31日 43830
17  H32.1.1   令和2年1月1日 43831
 
B1
=IF(A1<43586,TEXT(A1,"ggg"&IF(TEXT(A1,"e")="1","元","e")),"令和"&TEXT(TEXT(A1,"yyyy")-2018,"[=1]元;0"))&TEXT(A1,"年m月d日")

回答
投稿日時: 19/04/09 22:07:08
投稿者: んなっと

ちなみに、2007以降の条件付き書式→表示形式で
同一セルに"元年"を実現するときはたしかこんな感じでしたよね。(令和は除きます)
 
 [条件付き書式]
→[数式を使用して、書式設定するセルを決定]
=TEXT(A1,"e")="1"
→[書式(F)]...
 [表示形式]
 [ユーザー定義]
ggg"元年"m"月"d"日"
 
 
表示形式だけによる"元年"対応は、今後どうなるのでしょうか。

投稿日時: 19/04/09 23:08:58
投稿者: xls-hashimoto

「んなっとさん」ありがとうございます。
 
規則性を年数「1」に見つけて「元」とし、その他の年数は「e」と「0」で表示する。
この様なTEXT関数の表示文字列の設定方法が可能だとは初めて知りました。
おかげですっきりしました。この式は Excel2003 でもきちんと表示しました。
 
B1
=IF(A1<43586,TEXT(A1,"ggg"&IF(TEXT(A1,"e")="1","元","e")),"令和"&TEXT(TEXT(A1,"yyyy")-2018,"[=1]元;0"))&TEXT(A1,"年m月d日")
=IF(A1<43586,
    TEXT(A1,"ggg"
                &IF(TEXT(A1,"e")="1",
                    "元",
                    "e"
                    )
         ),
     "令和"
          &TEXT(
                TEXT(A1,"yyyy")-2018,
                                     "[=1]元;0"
                )
    )
    &TEXT(A1,"年m月d日")
 
「角田さん」もありがとうございます。
世の中の大所高所が良くわかりました。

投稿日時: 19/05/08 22:42:25
投稿者: xls-hashimoto

「令和」対応の Windows Update が提供されて問題なく動作したら「解決済」にしようと思い「回答受付中」にしていました。
 
連休中に Windows10Pro 1809 用 Windows Update KB4495667 が提供されたので、仕事開始とともに手動で Update しました。
 
結果、「令和1年5月7日」は間違いなく表示されました。
 
しかし、レイアウトが崩れるという問題が発生しました。
 
円グラフを使い掃除当番表を外側:掃除場所(固定盤)、内側:担当者(回転盤)として作っていましたが、画面に場所名、担当者名が同心円上に並ばなくなりました。
印刷イメージを表示させると同心円上に並び、印刷もされます。
新たに分割数を変えて作ろうとすると、いちいち印刷イメージで確認しないと作れなくなってしまいました。
 
また、結合したセルに文字をきっちり入れて(結合セル幅いっぱい)表示させていたら、文字が大きくなり両端が表示されなくなりました。
 
原因をネットで検索すると次のようなページが見つかりました。
 
警鐘】[改元][Windows][.NET] 「令和」対応パッチで画面が横に伸びる、文字が見切れる ― Windows Update 手動更新はちょっと待った方がいい
https://qiita.com/CodeOne/items/22004635d3c7cc533e5e
 
令和対応のWindows Updateは適用しないでください。
MS UI Gothicに~と同様な令和の合字を入れるときにミスがあったらしく、横幅が以前より広くなっています。
KancolleSnifferはMS UI Gothicを使っていて、フォントに合わせてレイアウトが変わるため横幅が広くなってしまいます。
これを避けるには、以下の更新パッケージをアンインストールしてください。
https://ja.osdn.net/projects/kancollesniffer/news/26014
 
KB4495667 をアンインストールしたら表示は元に戻りました。
 
そこで、この質問はレイアウト崩れが解決するまで「回答受付中」にしておきます。
レイアウト崩れは、Excel2010・2013・2016・2019 で同じように発生しました。

投稿日時: 19/05/15 21:02:16
投稿者: xls-hashimoto

2019/5/15 本日 5月定例の Windows Update があたりました。
 
2度目の Update で KB4494441 があたり、msgothic.ttc の
タイムスタンプが 20195/15 となって Windows10Pro 1809
32bit 上の Excel2013 もレイアウトの崩れが無くなりました。
 
ということで 「periodayoさん」からの返答は無く、スマホ版の
Excel のレイアウトは崩れたままですが、今日でこの質問も
「解決済み」にします。
 
元号の元年表示は、大正・昭和・平成・令和に適用できる
「んなっとさん」の条件付き書式をおすすめします。