エクセル 生年月日

質問

おはようございます。
またまた教えて頂きたいです。
生年月日を1つのセルに８桁入れたいのですが
元号を、1=明治、2=大正、3=昭和、4=平成として

例
昭和30年11月1日　→「03301101」（「」＝セルとします）

このように入力したいのですが

（03）元号→【01～04のみに制限したい】
（30）年→→【元号によって制限したい】
（11）月→→【01～12のみに制限したい】
（01）日→→【01～31のみに制限したい】

というような条件がつけたいのですが、いい数式が思いつきません。

現状は「04301101」平成30年11月1日とミスがあったり
日にちも31日以降でも入ってしまうので条件がつけたいです。

各セルに分けてつなごうとしても「0」が入らなかったりで困っています。

どうぞご教授下さい。お願いしますmm(＿ ＿)mm

どのようにしたら、ミスなく入力できるようになるか教えてください。

回答No.1

mshr1962 · ベストアンサー

>というような条件がつけたいのですが、いい数式が思いつきません。
「データ」「入力規則」にて「入力値の種類」を「整数」、「データ」を「次の値の間」で
元号A2→「最小値」「1」、「最大値」「4」
月C2→「最小値」「1」、「最大値」「12」

「データ」「入力規則」にて「入力値の種類」を「ユーザー設定」で
年B2→「数式」「=AND(B2>0,B2<=CHOOSE(A2,45,15,63,TEXT(TODAY(),"e")*1),MOD(B2,1)=0)」
日D2→「数式」「=AND(D2>0,D2<=DAY(DATEVALUE(CHOOSE(A2,"M","T","S","H")&B2&"/"&C2+1&"/1")-1),MOD(B2,1)=0」

>各セルに分けてつなごうとしても「0」が入らなかったりで困っています。
E2=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"00")&TEXT(D2,"00")

※各セルのセルの書式設定の表示形式はユーザー定義で「00」としてください。
※尚、年号が増えた場合、月以外の入力規則は変更が必要です。
例　平成が25年で終わって新年号が"J"として
元号A2→「最小値」「1」、「最大値」「5」
年B2→「数式」「=AND(B2>0,B2<=CHOOSE(A2,45,15,63,25,TEXT(TODAY(),"e")*1),MOD(B2,1)=0)」
日D2→「数式」「=AND(D2>0,D2<=DAY(DATEVALUE(CHOOSE(A2,"M","T","S","H","J")&B2&"/"&C2+1&"/1")-1),MOD(B2,1)=0」

お礼

お礼が遅くなりすみません。

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

参考にし、無事完成しました。

回答No.8

tom04

こんばんは！
参考になるかどうか判りませんが・・・
一つの案です。
ちょっと手間がかかるかもしれませんが、元号・年・月・日をすべて入力規則のリスト設定します。

準備段階として、↓の画像のようにG～J列に表を作成しておきます。
G2～J2セルの表示形式は文字列として「01」・「02」・・のように入力しています。
明治の列は1～45まで・大正の列は1～15まで・昭和の列は1～64まで・平成の列は1～22まで連番を入れています。

当方使用のExcel2003の場合です。
G3～G48セルを範囲指定　→　名前定義　→　明治　としてOK
H4～H18セルを範囲指定　→　名前定義　→　大正　としてOK
I4～I67セルを範囲指定　→　名前定義　→　昭和　としてOK
J4～J25セルを範囲指定　→　名前定義　→　平成　としてOK

A2セル以降適当なセルまで範囲指定　→　データ　→　入力規則　→　リスト　を選択
元の値で　G1～J1　を範囲指定　→　OK
B2セル以下を範囲指定　→　データ　→　入力規則　→　リスト　→　元の値の欄に
=INDIRECT(A2)　としてOK

C2セル以下を範囲指定　→　データ　→　入力規則　→　リスト　→　作成している表内の　1～12　のセルを範囲指定　→　OK

D2セル以下を範囲指定　→　同様にリストから　作成している表の　1～31　を範囲指定し、OK

これですべてのセルがリスト表示されます。

最後にE2セルに
=IF(COUNTBLANK(A2:D2),"",HLOOKUP(A2,$G$1:$J$2,2,0)&TEXT(DATE(HLOOKUP(A2,$G$1:$J$3,3,0)+B2,C2,D2),"eemmdd"))
という数式を入れオートフィルで下へコピーすると画像のような感じになります。

尚、すべてリスト内から選択する必要はなく、判っている数字に関しては直接入力しても構いません。
この場合、仮にA列で「大正」を選択しB列に「16」などその年号にない数値を入力すると注意画面が自動で表示されます。

そして、D列の日付に関しては小の月でも31と入力できますが、結果のD列には3/1のデータが表示されると思います。

以上、長々と書いてしまいました。
参考になれば良いのですが他に良い方法があればごめんなさいね。m(__)m

お礼

お礼が遅くなりました。

皆様にはとても感謝します。

今後とも是非お願いします。

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

回答No.7

shinkami

ＡNo.３ のshinkamiです。

もしかしてあなたは公務員？
役所には元号を数字で表現する書類が沢山残っていますね
そんな詮索はどうでもよいことです。本題に入ります。

日付は通常の入力にして、これをテキストに変換する方法にしましょう
この方が入力ミスは軽減されるのでは？

最初 Ａ1～Ｂ5に添付のようなデータを用意します。

Ａ８に日付を入力するとＢ８にテキスト変換されます。
Ｂ８には次の式を記述します。

=IF(A8>$A$4,"04"&YEAR(A8)-$B$5,IF(A8>$A$3,"03"&YEAR(A8)-$B$4,IF(A8>$A$2,"02"&YEAR(A8)-$B$3,"01"&YEAR(A8)-$B$2)))&TEXT(MONTH(A8),"0#")&TEXT(DAY(A8),"0#")

お礼

お礼が遅くなりました。

再度回答すみません。

皆様にはとても感謝します。

今後とも是非お願いします。

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

回答No.6

kagakusuki

例えば、04220229の様に、

元号→【01～04のみに制限したい】
月→→【01～12のみに制限したい】
日→→【01～31のみに制限したい】

という条件は満たしているものの、実際には有り得ない値が、入力される事も防ぐ事が出来る方法です。
　勿論、【元号によって制限したい】にも対応していて、例えば03640107や04010108は入力可ですが、03640108や04010107は入力不可になります。
　尚、Excelでは明治33年1月1日よりも前の日付けは、扱う事が出来ませんので注意なさって下さい。

　E2セルに直接8桁の数字を入力する場合には、以下のような操作を行って、E2セルに書式と入力規則を設定して下さい。

E2セルにカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中から、[セルの書式設定]を選択してクリック
　　↓
現れた[セルの書式設定]ウィンドウの[表示形式]タグをクリック
　　↓
[分類]欄の中から、[文字列]を選択してクリック
　　↓
[セルの書式設定]ウィンドウの[OK]ボタンをクリック
　　↓
メニューの[データ]をクリック
　　↓
選択肢の[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの「設定」タグをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中の「ユーザー設定」をクリック
　　↓
現れた「数式」欄の中に以下の数式を入力

=IF(AND(ISNUMBER(MATCH(LEFT(E2,2),{"01","02","03","04"},0)),ISNUMBER(VALUE(LOOKUP(LEFT(E2,2),{"01","02","03","04";"M","T","S","H"})&MID(E2,3,2)&"."&MID(E2,5,2)&"."&MID(E2,7,2)))),TEXT(VALUE(LOOKUP(LEFT(E2,2),{"01","02","03","04";"M","T","S","H"})&MID(E2,3,2)&"."&MID(E2,5,2)&"."&MID(E2,7,2)),"g")=LOOKUP(LEFT(E2,2),{"01","02","03","04";"M","T","S","H"}),FALSE())

　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック

お礼

お礼が遅くなりました。

皆様にはとても感謝します。

今後とも是非お願いします。

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

回答No.5

MackyNo1

No4の回答の補足です。

＞各セルに分けてつなごうとしても「0」が入らなかったりで困っています。

TEXT関数で表示できますが、補助列？のA～D列を使わずに直接E2セルに０から始まる数字を入力する方式のほうが簡単だと思います。

この場合は、E2セルの表示形式を「文字列」にして入力し、E2セルに以下のようなユーザー設定の入力規則を設定します。

=AND(CHOOSE(LEFT(E2,2)*1,"m","t","s","h")=TEXT((CHOOSE(LEFT(E2,2)*1,"m","t","s","h")&TEXT(RIGHT(E2,6),"00!/00!/00"))*1,"g"),(LEFT(E2,2)*1=4)*MID(E2,3,2)*1<=TEXT(TODAY(),"e")*1)

お礼

お礼が遅くなりました。
再度回答すみません。
皆様にはとても感謝します。
今後とも是非お願いします。
ありがとうございました。

回答No.4

MackyNo1

基本的にわざわざセルを分割して入力しないほうが簡単に条件付き書式を設定できますが、例示のデータのように4つのセルに分割して入力するなら、A2：D2セルを選択して「データの入力規則」で「ユーザー設定」にして以下の数式を入力してください。

=AND(TEXT((CHOOSE($A2,"m","t","s","h")&$B2&"/"&$C2&"/"&$D2)*1,"ge")=CHOOSE($A2,"m","t","s","h")&$B2,($A2=4)*$B2<=TEXT(TODAY(),"e")*1)

上記の数式は平成1年1月2日などの実際に存在しない日を入力した場合にも対応できるようにしてあります。

また、明治の場合は、シリアル値の開始日である明治33年1月1日以前の日も入力できないようにしています。

お礼

お礼が遅くなりました。

皆様にはとても感謝します。

今後とも是非お願いします。

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

回答No.3

shinkami

入力規則を設定します。
EXSEL2007 では [データ]→[入力規則]→[設定]と入ります。

添付は年号の設定ですが リスト欄には連続したセル内に設定することもできます。

>年→【元号によって制限したい】
これは別に年のエラー列でIF文で制限することになります。
以上ですが下記のように年号は年の途中で変わります。
年号ごとの最終日
・明治45年7月29日
・大正15年12月24日
・昭和64年1月7日

ここからは提案です。
日付はセル一つで処理します。（明治33年1月1日未前は非対応ですが）
昭和30年11月1日　→「s30/11/1」（m=明治、t=大正、s=昭和、h=平成)
セルA1が日付のデータとすると
年=year(A1) 但し西暦
月=month(A1)
日=day（A1)
これのメリットは
・日付の表示形式は書式の表示形式で和暦、西暦どちらでも可能です
・表示形式を工夫すると曜日も含めることが出来ます。
・期間計算が日付の引き算でできる
・満年齢の関数がある
・各年号でオーバーした日付を入れると年号が繰り上がる
・うるう年にも対応している
？但し月末以上の日付には対応していない(文字列となる）

お礼

お礼が遅くなりました。
皆様にはとても感謝します。
今後とも是非お願いします。
ちなみに公務員ではないです。
ありがとうございました。

回答No.2

chiizu2

セルの入力規則を利用してください
使用方法は
Excel 2003まで
http://allabout.co.jp/gm/gc/297787/
Excel 2007以降
http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-ny_kis.html

設定はA2～D2の各セルごとに
A2(年号) 入力値の種類→整数 データ→次の値の間 最小値→1 最大値→4
B2(年) 入力値の種類→ユーザー設定 数式→=IF(A2=1,B2<=45,IF(A2=2,B2<=15,IF(A2=3,B2<=64,IF(A2=4,B2<=22,FALSE))))
上記の式では小数でも入力できますが平成22.5年と間違うことはないでしょうから
実用上は問題ないと思います
C2(月) 入力値の種類→整数 データ→次の値の間 最小値→1 最大値→12
D2(日) 入力値の種類→整数 データ→次の値の間 最小値→1 最大値→31
としてください

お礼

お礼が遅くなりました。

皆様にはとても感謝します。

今後とも是非お願いします。

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