エクセルに詳しい方教えて下さい

質問

エクセルに詳しい方教えて下さい
4220101
と7桁で表示されている生年月日を、
平成22/01/01または
H22/01/01
と日付表示に直したいのですが、良い方法を教えて下さい。
平成=4
昭和=3
大正=2
明治=1
となっているデータで、数字の頭に元号の該当数字が入ります。

また、TEXT関数についてですが、
4220101のセル値を、
4/22/01/01に変換する式は=TEXT(セル,"0!/00!/00!/00")で合ってますか？
(セル,"0/00/00/00")だとエラーになりました。
0の後ろにつく!はどんな意味があるのかを知りたいです。

回答No.2

tom04

No.1です！
＞ざっくりことばで教えていただけると理解できるかもしれません

とありましたので、再びお邪魔しました。

質問にTEXT関数などのことが書いてあり、ある程度の関数に関しては理解されていらっしゃる前提での投稿でした。
あまりに細かいことを説明しても、余計なお世話になりかねないと思い数式の一例で終わってしまっていました。

生年月日の表示だということなので、年齢計算などを行うにはシリアル値の方が良いと思い、前回の方法になりました。
シリアル値とは1900年1月1日を「1」として1日に「1」ずつプラスしていきますので、
仮に今日の場合は「40419」というシリアル値になります。
年齢計算するにはこのシリアル値が使うのが一般的です。
尚、明治32年（1899年）以前に生まれた方はエラーになります。

前置きが長くなりましたが前回の数式の説明をします。
DETE関数を使っていますので、「年」「月」「日」の数値が必要になります。
画面のfxのアイコンをクリックし、DATE関数のダイアログボックスを出します。
↓のような画面が出ますので、

「年」の欄に　INDEX($E$2:$E$5,LEFT(A2,1))+RIGHT(LEFT(A2,3),2)
数式の意味はE2～E5セルのA2の左1文字目に表示されている（4の場合は平成→4行目）の1988に
A2セルの左3文字の右2文字をプラス　→　1988+22＝2010　になります。
他の元号も同様の考え方です。

月に関してはA2セルの右4文字の左2文字　→　01　となります。
日も同様に　A2セルの右2文字　→　01　です。

これでB2セルは平成22年01月01日のシリアル値になりますので、
B2セル上で右クリック　→　セルの書式設定　→　表示形式　→　日付　でOKなのですが
希望の　平成22/01/01　の項目に適応するものがないと思いますので、
ユーザー定義で　ggge/mm/dd
とすると　「平成2201/01」と表示されます。
これをオートフィルで下へコピーしたのが前回の方法です。

以上、ながながと失礼しました。m(__)m

お礼

ご丁寧にありがとうございます！
理解できました。

回答No.1

tom04

こんにちは！
一例です。

今後のことを考えると、シリアル値表示の方が何かと便利だと思いますので、
↓の画像のようにやってみました。
まず、明治～平成のシリアル値の年を出すための表を作成しておきます。

そして、B2セルに
=IF(A2="","",DATE(INDEX($E$2:$E$5,LEFT(A2,1))+RIGHT(LEFT(A2,3),2),LEFT(RIGHT(A2,4),2),RIGHT(A2,2)))

これはシリアル値になりますので、セルの表示形式のユーザー定義から
ggge/mm/dd
としておきます。

これをオートフィルで下へコピーすると
画像のような感じになります。
以上、他に良い方法があれば読み流してくださいね。m(__)m

お礼

早速ありがとうございます。
初歩的なレベルのため、教えていただいた関数の組み合わせの内容を理解するのが難しいですが、教えていただいた通りにやってみますね！！
ざっくりことばで教えていただけると理解できるかもしれません

ありがとうございました。