エクセル 関数のあわせ技

質問

お世話になります。

最終目的は年齢表示です。

生年月日の元データは他システムからＣＳＶカンマ形式で出力されたものをエクセルで表示しているため、問題箇所があります。

・年月日のいずれかが一桁の場合、「空白」＋「半角数字」＋「年月日」となっています。
　　例えば、昭和２０年５月１日　　　の場合は　
　　　　　　　昭和２０年　５月　１日　となっています。（数字は半角です）

・元データのほとんどはテキスト形式なのですが、一部日付データ形式があります。
　　セル表示は「元号＋年月日形式」ですが、数式バーは西暦表示です
　　例えば、昭和２４年１２月１１日　とセル上は表示されますが、
　　　　　　　　1949/12/11　　　と数式バーでは表示されます。

まず、生年月日データに混じっている空白を消すため、SUBSTITUTE関数を使って別の列に書き出しました。
=IF($J3="","空白行",SUBSTITUTE(AM3," ",""))

これで、昭和２０年　５月　１日は昭和２０年５月１日になりました。

その後、DATEDIF関数で年齢を表示させました。
=IF(ISERROR(DATEDIF(AJ3,today(),"Y"))=TRUE,"",DATEDIF(AJ3,today(),"Y"))

ただし、日付データの　1949/12/11　のセルは、18243　になってしまいました。

これでも年齢計算はできるのですが、生年月日確認用に使いますし、後で印刷時に利用するデータですので、「テキスト形式」として、昭和２４年１２月１１日　と表示させたいのです。

上記の関数式に別の関数を組み合わせればいいのだろうと思いましたが、うまくいきません。

どうか、よろしくご教示ください。

回答No.2

mshr1962 · ベストアンサー

>私がイメージしたものは、
>　=IF($J3="","空白行",SUBSTITUTE(M3," ",""))
>と
>　=TEXT(M3,"ggge年m月d日")
>を　一つの式として合わせる方法なのですが、どうにもうまくいきません。

=IF($J3="","空白行",TEXT(SUBSTITUTE(M3," ","")*1,"ggge年m月d日"))

補足

うまくいきました。

なるほど、ここにtextを混ぜるのでしたか。
ありがとうございました。

補足でお伺いしたいのは、
=IF($J3="","空白行",TEXT(SUBSTITUTE(M3," ","")*1,"ggge年m月d日"))
の途中の　*1　はどのような意味なのでしょうか？

この　*1　は省略しても、同じ結果が得られます。

しかし、何かの意味があるのですよね？

教えていただけるでしょうか

よろしくお願いいたします。

回答No.5

mshr1962

#2です。
＞途中の　*1　はどのような意味なのでしょうか？
TEXT関数は基本的に数値(日付/時刻を含む)を文字付きの書式にするものです。
日付の文字列は一応、TEXT関数で認識は可能ですが *1 することで
数値（シリアル値）に置き換えてます。

*1以外の方法だと文字列を数値にする関数としては下記のものがあります。
VALUE関数、TIMEVALUE関数、DATEVALUE関数

お礼

なるほど。よくわかりました。

ありがとうございました。
勉強になりました

回答No.4

imogasi

この質問は諸点が錯綜してわかりにくい。
＞関数のあわせ技
特に言わなくてもここらの質問はみな、関数組み合わせ型です。
＞最終目的は年齢表示です。
年齢を割り出すなどの計算をするためには、エクセルの関数利用をせざるを得ない。そのとき引数はエクセルの日付シリアル値が入ったセル番地を要求される。
ですから日付シリアル値に統一すべきです。
ですから＞確かに表示形式の設定で解決させる問題かも知れません
は反対です。文字列にしたければ、日付シリアル値が（中間段階にしろ）できた後でTEXT関数で変換することになります。
ーー
＞関数式で表示形式も設定できればいいなと思いまして、質問させていただきました。
文字通りはできません。TEXT関数を通じて実質それに近いことをするだけです。関数はセルの「値」を加工設定するもので、表示形式は別世界のことです。VBAならどちらもできます。
＞のいずれかが一桁の場合、「空白」＋「半角数字」＋「年月日」となっています
意味不明。「年月日」は記述不要？
＞テキスト形式なのですが、一部日付データ形式があります
これが本質問の難題です。
＞数式バーは西暦表示です
このセルが本当の日付シリアル値で見た目は表示形式でそう見えてます。
散らばっているとどこに添えらがあるか、判別しにくい。
日付もセルの値は整数なので。VBAではIsDate関数が使えるが。
＞元データのほとんどはテキスト形式なのですが
テキストでも、=DATEVALUE()関数で日付に直るなら、簡単。
=IF(A1が日付けなら,A1,DATEVALUE(A1))とやってみる価値あり。
平成２年１１月２日などもDATEVALUE関数で日付シリアル値に直る。
そのほかの表現バラエティを調べること。
半角、全角スペースはダメなようです。
＞DATEDIF関数で年齢を表示させました
＝DATEVALUE関数を挟まないと日付シリアル値にならないでしょう。
ほとんどがエクセルの日付の関数は、セルの値が日付シリアル値であるとき働くものだということをお忘れなく。

お礼

恐れ入ります。

下手な表現で申し訳ありませんでした。

また、それを読み取ろうとしていただき、ありがとうございました。

回答No.3

taka0028

関数を組み合わせると以下となります

=IF($J3="","空白行",TEXT(SUBSTITUTE(M3," ",""),"ggge年m月d日"))

J3とM3が違うけどいいのかな？

お礼

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

また、J3とM3が違うということも心配いただき、ありがとうございました。

元データは約20行おきに空白行が混ざっています。
また、ある列では、空白だったり、データが入っていたりします。
具体的には、世帯主の行には住所が入りますが、その下の世帯員の行には住所は入らないのです。

J3　はその約20行おきに出てくる空白行の判別キーとして使用しています（本来は、ここはカナ氏名がありますが、空白行以外は入力されているので、判別に使いました）

M3　は生年月日が質問文の形式（見栄えはいいけど、データとしては使えない形式）で入っています。

質問に乗せなくてすみませんでしたが、作業列はAM列です。（質問文で変な記述をしてしまいました。すみません）

一度このシートで作ったものを、別シートに貼り付けようと思っています。（シート名の記述をエクセルでしてくれるので）

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

回答No.1

zap35

18243と表示されたセルの書式を
　ggge"年"m"月"d"日"
とするだけで昭和２４年１２月１１日と表示されますが、そういう意図の質問ではないのでしょうか？　あるいは
　=TEXT(AJ3,"ggge年m月d日")
で文字列の表示にすることも可能です。

空白入りの文字列から空白を削除したり、DATEDIF関数で満年齢を計算したりすることと、「テキスト形式」で表示することとどのような関係があるかわかりませんでした。

お礼

ありがとうごさいます。

私がイメージしたものは、

　=IF($J3="","空白行",SUBSTITUTE(M3," ",""))
と
　=TEXT(M3,"ggge年m月d日")
を　一つの式として合わせる方法なのですが、どうにもうまくいきません。

確かに表示形式の設定で解決させる問題かも知れません。

関数式で表示形式も設定できればいいなと思いまして、質問させていただきました。