エクセル関数についての質問です。

質問

会社の設立年数について100社程の一覧表を作成しております。
設立年数については、「DATEDIF」関数を使って計算しているのですが、設立年数については平成はH、昭和はS、大正はT、明治はM、というように和暦を記号化して使っております。（様式が統一されているため）
明治20年代から43年代にかけて設立した会社が10社程あって、年代の新しい順に計算していったのですが、明治33年以前の31年、30年、25年、21年に設立した会社の計算を行うと、「＃VALUE!」の表示が出て計算できません。明治33年を西暦に直すとちょうど1900年になりますが、それ以前が「エラー」になってしまいます。33年以前のものについてはほかの関数式があるのでしょうか？どうやったら解決出来るか、わかる方がおりましたら、ご教授ください。よろしくお願いいたします。

回答No.7

kagakusuki · ベストアンサー

明治における天保歴（旧暦）からグレゴリオ暦（現用されている暦）への切り換えにより、明治5年12月2日の翌日が、明治6年1月1日になりましたから、明治5年以前の日付には29日のずれが存在しています。

【参考URL】
　旧暦 - Wikipedia
　　http://ja.wikipedia.org/wiki/%E6%97%A7%E6%9A%A6

　その事を考慮して、明治5年以前の日付けには、29日を加える様にしました。
　今仮に、A1セルに現在の日付が入力されていて、A2セルに会社の創立日が入力されている場合には、その会社の設立日から現在までの経過年数を求める式は次の様になります。

=IF(ISNUMBER(SUBSTITUTE(ASC(A2),"M","S")+0),IF(AND(ASC(LEFT(A2,1))="M",MID(A2,2,2)+0<33,DATEDIF(("M"&MID(A2,2,2)+32&MID(A2,FIND(".",ASC(A2)),9))+(MID(A2,2,2)+0<6)*29,$A$1,"Y")+32,DATEDIF(A2,$A$1,"Y")),"")

　尚、この式は、あくまで

M33.1.1

の形式で日付が入力されている場合に有効なもので、

M33/1/1

M33年1月1日

の形式で入力されている場合には対応していません。（Ｍ３３．０１．０１ 等、"."で区切っている場合には、半角文字でも全角文字でも対応しています）

　余談ですが、グレゴリオ暦では1900年は閏年にはなりませんから、1900年2月29日以前からの経過日数を求める場合には、その事を考慮しないと、1日分のずれが発生しますが、年数や月数を求める場合には、影響しないため、上記の関数でも考慮していません。

お礼

懇切丁寧なご回答をいただきありがとうございました。実際にいくつかやってみましたら、うまくできました。今回の質問で、いろいろなことを学ぶことができました。ありがとうございました！

回答No.8

kagakusuki

「[No.5補足]へのコメント、」に対する補足

　質問者様が御覧になっている、このＱ&Ａサイトは、「OKWave」を始めとする多数のＱ&Ａサイトが提携して、質問や回答の投稿内容を共有しています。
　この投稿内容を共有しているサイトの事を「パートナーサイト」と言います。
　このＱ&Ａサイトのパートナーサイトの内、中心となっているサイトが、mike_g様が仰っている「OKWave」です。
　もう一つの「教えて!goo」も登録者数の多い、パートナーサイトの１つです。
　添付画像を表示するサービスは、一部のパートナーサイトでは行われておりませんから、御使用されているパートナーサイトによっては、添付画像を見る事が出来ない場合もあります。
　そのため、mike_g様は
「どのパートナーサイトから御覧になっておられるのでしょうか？」
という意味で

>貴方は何処から？

と仰っておられるのです。

　尚、このＱ&Ａサイトにアクセス出来るのは、パソコンだけではなく、携帯電話等のモバイル機器からもアクセス出来ますから、必ずしも。

>貴方のパソコンでは

とは限らないと思います。

　因みに、「OKWave」や「教えて!goo」に登録していなくとも、投稿せずに見る事だけならば可能ですから、「OKWave」等のサイトで、この御質問が掲載されているページを開けば、質問者様が使用されている機器が画像表示に対応している場合には、ANo.5の添付画像も御覧になる事も出来ると思います。
　但し、モバイル機器の機種によっては、画像表示の機能が対応していない機種もあるかも知れません。
　尚、「OKWave」における、この御質問が掲載されているページのURLは、以下の通りです。

http://okwave.jp/qa/q6606523.html

お礼

ご助言ありがとうございました。mike_g様からの内容が良くわからなかったのですが、この説明でよくわかりました。ありがとうございました。

回答No.6

noname#204879

[No.5補足]へのコメント、

え？貴方のパソコンでは「添付図」が表示されてないと？
[教えて!goo]や[OKWave]からは見えますけど。貴方は何処から？

お礼

私の拙い質問に対してご回答していただき、ありがとうございました。OKWaveに
登録してすぐの質問だったため「補足入力」や、やり取りが良くつかめず皆さんにご迷惑をおかけしましたが、こんなに多くの方たちからご回答を頂き感謝しております。ありがとうございました。

回答No.5

noname#204879

添付図参照

D2: =(MID(A2,2,FIND(".",A2)-2)+1867*(LEFT(A2,1)="M")+1911*(LEFT(A2,1)="T")+1925*(LEFT(A2,1)="S")+1988*(LEFT(A2,1)="H"))
E2: =MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)
F2: =MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,2)

B2: =DATEDIF(DATE(D2+2000,E2,F2),DATE(YEAR(TODAY())+2000,MONTH(TODAY()),DAY(TODAY())),"Y")

補足

ご回答ありがとうございます。誠に申し訳ありませんが、できましたら「添付図」を表示していただけないでしょうか。よろしくお願いいたします。

回答No.4

KURUMITO

例えばA1セルにはM30.3.21と入力されており、B1セルにはH23.3.21と入力されているとして、C1セルにお望みの表示をさせるのでしたら次の式を入力します。

=IF(ISNUMBER(A1),DATEDIF(A1,B1,"Y")&"年"&DATEDIF(A1,B1,"YM")&"ヶ月",DATEDIF(DATE(MID(A1,2,FIND(".",A1)-2)+100-33,MID(A1,FIND(".",A1)+1,FIND(".",MID(A1,FIND(".",A1)+1,10))-1),MID(A1,FIND(".",A1)+1+FIND(".",MID(A1,FIND(".",A1)+1,10)),10)),DATE(YEAR(B1)+100,MONTH(B1),DAY(B1)),"Y")&"年"&DATEDIF(DATE(MID(A1,2,FIND(".",A1)-2)+100-33,MID(A1,FIND(".",A1)+1,FIND(".",MID(A1,FIND(".",A1)+1,10))-1),MID(A1,FIND(".",A1)+1+FIND(".",MID(A1,FIND(".",A1)+1,10)),10)),DATE(YEAR(B1)+100,MONTH(B1),DAY(B1)),"YM")&"ヶ月")

お礼

ご回答ありがとうございました。全てではありませんが2～3やって見ましたらうまくできました。ありがとうございました！

回答No.3

noname#204879

》 「DATEDIF」関数を使って計算しているのですが、設立年数については
》 平成はH、昭和はS、大正はT、明治はM、というように和暦を記号化して
》 使っております

DATEDIF関数を使用するには、開始日(設立日)として年月日を指定する必要がありますが、和暦でどのように記号化しているのか、具体的に明示してください。

平成、昭和、大正、明治の元旦は、それぞれ H01.01.01、S01.01.01、T01.01.01、M01.01.01 のように、(明治33年1月1日以降も以前も)設立日は９桁の半角文字列で統一して入力されていると考えてよろしいですか？

補足

質問に目を通していただいてありがとうございます。確認の平成1年1月20日はH1.1.20で表示しています。数字が一桁の場合は0表示しないでやっております。
よろしくお願いいたします。

回答No.2

kmetu

参考に

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1325064478

お礼

参考の資料ご送付ありがとうございました。大変参考になりました！

回答No.1

Saturn5

難しい問題です。
Ｅｘｃｅｌは日付を内部で1900年1月1日から積算した値で、
（時間は小数部分で）保持しています。
たとえば、2011/3/20と入力して、表示形式を数値に変えると
40662とい表示されます。これは1900年1月1日から数えて
40662日目ということです。
私も１０年くらい前、ある年の住民台帳を作っていたとき、
数人の1800年代生まれの人がいて困りました。

さて、これの解決は難しいです。
設立年数だけで良ければ西暦の年、月、日を分けて入力して、
そこから直接計算するくらいしか解決方法が思いつきません。

お礼

ご回答ありがとうございました。皆さんのおかげで多くの方法を知ることができ、この項目については大変勉強になりました。ありがとうございました！