こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

-広告-

解決済みの質問

EXCELの関数で

Windows 7 Home Premium SP1  64ビットオペレーティングシステム で、
Microsoft Office Personal 2010 Excel Ver.14.0.7165.5000(32ビット)を使っています。
R1C10 に「生年月日シリアル値」「12345」
RC1 に「今日の年月日」例えば「2016/1/1」
RC2 に「現在の時分」例えば 「10:30」
以上のデータから
RC3 に現在時刻における満年齢を求める方法を教えてください。
満年齢書式は「##0.000」才で表記したいと思っています。
宜しくお教示ください。

投稿日時 - 2016-01-15 11:34:31

QNo.9111608

困ってます

質問者が選んだベストアンサー

 回答No.5です。

>これだけの長い式を全行に記載するのは数千行に及ぶと計算時間がかかるのではないかと心配です。

 現代のパソコンは高速ですので、Windows 7 Home Premium SP1  64ビットオペレーティングシステムが使える様な32bitパソコンであれば、配列数式の様な繰り返し計算を行う様な箇所が何箇所もあるというのであれば兎も角、その様な繰り返しを必要としない処理で、数千行行程度のデータを扱う際に処理が重くなる事を心配する必要は殆ど無いと思います。(多分ですが、数千行程度なら、例え配列数式でも大して重くはならないと思います)
 それに、特に処理が重くなるのは配列数式やSUMPRODUCT関数などの繰り返し処理を(広いセル範囲を繰り返し処理の対象にして)行う場合ですし、その次に重くなりがちなのはMAX関数、MIN関数、RANK関数、LARGE関数、SMALL関数等の「基準となる値を範囲内の全ての値と比較する」という様な類の処理を行う関数ですが、今回の御質問内容の場合、その様な重くなりがちな処理を行う必要が御座いませんので、回答No.5には特に処理が重くなる様な関数を使用している箇所は御座いません。(確認はしておりませんが、おそらく他の方々が回答されている関数も、重くならないという点では同様な筈です)
 ですから、計算時間に関してはその様な心配は無用かと存じます。

 只、どうしても気になる様でしたら、作業列を用いる事で若干高速化を図る事は可能です。
 回答No.5では1個のセル内に入力した関数だけで処理の全てを済ませるために、「現在の日付以前の直近の誕生日が何日になるのか」という事を求める計算を、1つの関数内で2、3回計算し直すという無駄がどうしても生じてしまっていますので、どこか使用していない列を作業列として使用して、関数を使ってその列のセルに「現在の日付以前の直近の誕生日」を一旦、表示させる様にしておいた上で、そのセルの値を用いて「現在時刻における満年齢」を求める様にすれば、「『現在の日付以前の直近の誕生日』という1つの値を使用する必要が生じる度に計算をやり直す」などという無駄が無くなる分だけ、若干計算速度が上がる筈です。
 例えば、今仮に

>R1C10 に「生年月日シリアル値」「12345」
>RC1 に「今日の年月日」例えば「2016/1/1」
>RC2 に「現在の時分」例えば 「10:30」

といった元データが入力されているのがSheet1のR2行目以下の行であり、Sheet2のC2列のセルとSheet2のR2C1セルを作業用のセルとして使用するものとします。
 その場合、まずSheet2のR2C1セルに次の関数を入力して下さい。

=IF(AND(Sheet1!R1C10>=1,Sheet1!R1C10<"9999/12/31"+0),Sheet1!R1C10,"")

 次に、Sheet2のR2C2セルに次の2つの関数の内のいずれか一方を入力して下さい。

=IF(COUNT(R2C1,Sheet1!RC1,Sheet1!RC2)=3,IF(AND(Sheet1!RC1>1,Sheet1!RC1<"9999/12/31"+0,MOD(Sheet1!RC1,1)=0,Sheet1!RC2>=0,Sheet1!RC2<1,SUM(Sheet1!RC1,Sheet1!RC2)>=R2C1),DATE(YEAR(Sheet1!RC1)-(Sheet1!RC1<DATE(YEAR(Sheet1!RC1),MONTH(R2C1),DAY(R2C1))),MONTH(R2C1),DAY(R2C1)),""),"")

或いは

=IF(COUNT(R2C1,Sheet1!RC1,Sheet1!RC2)=3,IF(AND(Sheet1!RC1>1,Sheet1!RC1<"9999/12/31"+0,MOD(Sheet1!RC1,1)=0,Sheet1!RC2>=0,Sheet1!RC2<1,SUM(Sheet1!RC1,Sheet1!RC2)>=R2C1),DATE(YEAR(Sheet1!RC1)-(TEXT(Sheet1!RC1,"""2000/""m/d")-TEXT(R2C1,"""2000/""m/d")<0),MONTH(R2C1),DAY(R2C1)),""),"")

 次に、Sheet2のR2C2セルをコピーしてSheet2のC2列の3行目以下に貼り付けて下さい。

 次に、Sheet1のR2C3セルに次の関数を入力して下さい。

=IF(COUNT(Sheet2!R2C1,Sheet2!RC2)=2,YEAR(Sheet2!RC2)-YEAR(Sheet2!R2C1)+(RC1+RC2-Sheet2!RC2)/(365+ISNUMBER((YEAR(Sheet2!RC2)&"/2/29")+0)),"")

 次に、Sheet1のR2C3セルをコピーしてSheet1のC3列の3行目以下に貼り付けて下さい。


 以上です。

投稿日時 - 2016-01-17 15:35:39

お礼

暫く留守をしていてお礼が遅くなり申し訳ありませんでした。
エラーのチェックもしてくれているようですね。
完璧な回答を、具体的に教えて頂き有難うございました。
この、計算式を活用させていただきます。
有難うございました。

投稿日時 - 2016-01-30 12:59:16

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(11)

ANo.11

 後、回答No.3様も仰っておられる様に閏年の関係が御座いますので、端数を単純に365のみで割るという方式では正確な値を求める事は出来ません。
 私の回答No.2、5、6や、他の方の幾つかの御回答にある方法は、その様な閏年の問題にも対応する様になっておりますが、中には閏年の問題に対応していない方法を回答している回答者もおられる様ですので注意して下さい。(但し、回答No.5で述べました様に、DATEDIF関数にはバグがあるため、回答No.2を始めとして、他の回答者の御回答も含む幾つかの回答の中で、DATEDIF関数を使用しているものは誤った結果が表示される恐れがありますので、なるべく使用しない方が良いと思います)
 尚、366日で割るのは、「今日の年」に閏年がある場合ではなく、あくまで「『現在の日付以前の直近の誕生日』~『次の誕生日』」の期間内に2月29日が存在している場合ですので、何れにしましてもまず「現在の日付以前の直近の誕生日」を求めていなければ「現在時刻における満年齢」を正確に求める事は出来ません。
 そして、何れにせよ「現在の日付以前の直近の誕生日」を求めねばならないのですから、「日数部分と時刻部分の合計を求めてから、その合計値が1年に占める割合を求める」というやり方の方が簡単になりますから、「日数部分の1年に占める割合」と「時刻部分の1年に占める割合」を別々に求めてから後で合計するという方法は関数が複雑になるだけで意味がありません。

投稿日時 - 2016-01-17 16:10:49

-広告-

ANo.9

「RC1=215/9/30」は「R1C10=12345=1933/10/18」より小さいこととExcelで扱える日付が1900/1/1以降であることが原因と思います。
RC1=2015/9/30 であれば計算できるはずです。
尚、他の回答者からの指摘で「YEARFRAC関数は時刻が無視される」とのことです。
これはDATEDIF関数も同じで反映が必要なら +RC2/365 を数式の末尾に追加してください。
生年月日に時刻が無いので現在時刻を反映する意味はないと思います。

投稿日時 - 2016-01-17 08:57:28

お礼

有難うございます。
「RC1=215/9/30」は間違えでした。
生年月日の時刻は0:0:0で考えています。
1分間隔である種の作業をしたときの年齢差を知るために、下3桁が欲しかったのです。

投稿日時 - 2016-01-18 22:27:52

ANo.8

NO3です。
R1C1表示でも数式コピーで可能だと思っていましたが、エラーになってしったようで申し訳ありません。
R1C1表示方式では下記のようになりますのでご確認下さい。
R1C10~R1C12までデータ、R1C13に下記数式

=DATEDIF(RC[-3],RC[-2],"Y")+(DATEDIF(DATE(YEAR(RC[-3])+DATEDIF(RC[-3],RC[-2],"Y"),MONTH(RC[-3]),DAY(RC[-3])),RC[-2],"D")+RC[-1]/"24:00")/(DATEDIF(DATE(YEAR(RC[-3])+DATEDIF(RC[-3],RC[-2],"Y"),MONTH(RC[-3]),DAY(RC[-3])),DATE(YEAR(RC[-3])+DATEDIF(RC[-3],RC[-2],"Y")+1,MONTH(RC[-3]),DAY(RC[-3])),"d"))

投稿日時 - 2016-01-17 06:43:44

お礼

重ね重ね有難うございます。

投稿日時 - 2016-01-18 01:53:39

ANo.7

 YEARFRAC関数は開始日と終了日の間の期間が1年間に対して占める割合を返す関数ですが、日付けのみが計算対象となっているらしく、時刻は無視されてしまう様ですので、注意して下さい。

投稿日時 - 2016-01-15 17:58:46

お礼

有難うございます

投稿日時 - 2016-01-16 23:47:25

ANo.6

>RC3 に現在時刻における満年齢を求める方法を教えてください。
>満年齢書式は「##0.000」才で表記したいと思っています。
端数を算出する場合は1年の日数を何日にするかで値が変わります。
DATEDIF関数を使う方法とYEARFRAC関数を使う方法があります。
また、「現在の時分」を加味しても下3桁では無視される範囲のようです。
R1C3=DATEDIF(RC10,RC1+RC2,"y")+DATEDIF(RC10,RC1+RC2,"yd")/365
R1C3=YEARFRAC(RC10,RC1+RC2,3)
DATEDIF関数で算出した値とYEARFRAC関数で算出した値では1年の日数を同じにしても端数部で一致しません。

投稿日時 - 2016-01-15 16:42:12

お礼

有難ございます。
RC1=215/9/30
RC2=19:37:12
を入れて計算しましたが、結果が変です。

投稿日時 - 2016-01-17 01:44:17

ANo.5

 申し訳御座いません。回答No.4様が仰っておられる

>ANo.2の回答は、DATEDIFのバグが原因で、間違った答えを出します

という話は確かにその通りですので、回答No.2は無視して下さい。
 但し、DATEDIFにバグがあるのは、何も”yd"を使った場合に限った話だけではなく、「DATEDIFに”Y"を使うとバグが出る」場合もあります。

【参考URL】
 なにぬねのーつ > No.50 DATEDIFの謎を追え(1) ~ DATEDIF関数の真実 2007/12/30
  http://www.niji.or.jp/home/toru/notes/50.html

 ですから、回答No.2だけではなく、回答No.4も含むDATEDIF関数を使用している回答は全てバグが出る恐れがあります。
 又、閏年の事も考えますと、日本の場合、誕生日が2月29日の人の年齢は、2月末日の24:00(3月1日の0:00に等しい)に1つ歳が増えるという決まりになっていますので、計算がややこしくなります。
 そこで、DATEDIF関数を使わずに、誕生日が2月29日の場合にも対応した関数を考えましたので、お試し下さい。

=IF(COUNT(R1C9,RC1,RC2)=3,IF(AND(R1C9>1,R1C9<"9999/12/31"+0,RC1>1,RC1<"9999/12/31"+0,MOD(RC1,1)=0,RC2>=0,RC2<1,SUM(RC1,RC2)>=R1C9),YEAR(RC1)-YEAR(R1C9)-(TEXT(RC1,"""2000/""m/d")-TEXT(R1C9,"""2000/""m/d")<0)+(SUM(RC1,RC2)-DATE(YEAR(RC1)-(TEXT(RC1,"""2000/""m/d")-TEXT(R1C9,"""2000/""m/d")<0),MONTH(R1C9),DAY(R1C9)))/(365+ISNUMBER(((YEAR(RC1)-(TEXT(RC1,"""2000/""m/d")-TEXT(R1C9,"""2000/""m/d")<0)+(MONTH(R1C9)>2))&"/2/29")+0)),""),"")

投稿日時 - 2016-01-15 15:35:49

補足

皆さんのご回答を拝見して、非常に複雑になることは間違えないようですね。
これだけの長い式を全行に記載するのは数千行に及ぶと計算時間がかかるのではないかと心配です。
R1C10は固定値ですので、参照させないで直接関数の中に数値を入れた方がいいのでしょうか。
教えていただいた、計算式難問で理解できませんが、R1C10を固定値12328とすると、年の計算値は1933、月の計算値は10、日の計算値は1になります。
頂いた式にこれを代入すると
=IF(COUNT(12328,RC1,RC2)=3,IF(AND(12328>1,12328<"9999/12/31"+0,RC1>1,RC1<"9999/12/31"+0,MOD(RC1,1)=0,RC2>=0,RC2<1,SUM(RC1,RC2)>=12328),YEAR(RC1)-1933-(TEXT(RC1,"""2000/""m/d")-TEXT(12328,"""2000/""m/d")<0)+(SUM(RC1,RC2)-DATE(YEAR(RC1)-(TEXT(RC1,"""2000/""m/d")-TEXT(12328,"""2000/""m/d")<0),10,1))/(365+ISNUMBER(((YEAR(RC1)-(TEXT(RC1,"""2000/""m/d")-TEXT(12328,"""2000/""m/d")<0)+(10>2))&"/2/29")+0)),""),"")
になりましたが、このようにした場合、もう少し簡素化できないでしょうか。
計算結果は2015/9/30 23:59:59 まで81才台で、2015/10/01 00:00:00 で82.000才になることを期待していますが、2015/9/30 19:37:12 で82.000才になるようです。しかし簡略化のためには、この程度の誤差は仕方がないのでしょうか。

また、ダミーの列を作って(列幅0)途中計算を保管すると簡略化できませんか。
重ねての質問で恐縮ですが宜しくお願いします。

投稿日時 - 2016-01-17 01:06:26

お礼

ご丁寧に有難うございます。

投稿日時 - 2016-01-16 23:51:21

ANo.4

=DATEDIF(INT(R1C10),RC1+RC2-MOD(R1C10,1),"Y")+(R1C1+R1C2-DATE(YEAR(R1C1)-(DATE(YEAR(R1C10),MONTH(R1C1),DAY(R1C1))+R1C2<R1C10),MONTH(R1C10),DAY(R1C10))-MOD(R1C10,1))/(R1C1-DATE(YEAR(R1C1)-1,MONTH(R1C1),DAY(R1C1)))

この式は「DATEDIFに”yd"を使うとバグが出る」と言う症状を回避する為、DATEDIFは「満年齢の整数部を求めるだけ」にしています(ANo.2の回答は、DATEDIFのバグが原因で、間違った答えを出します)

また「満年齢の小数部(1年に満たない部分)」は「時間も加味しての日数(小数点付きの日数)を求めて、閏年を加味した1年の日数で割る」という計算をしています。

そのため、ANo.2とは少し違う値(より正確な値)が求まります。

投稿日時 - 2016-01-15 13:36:03

補足

答えは #NUM! になってしまいます。

投稿日時 - 2016-01-17 01:34:18

お礼

ご忠告有難うございます

投稿日時 - 2016-01-16 23:53:10

ANo.3

R1C10はJ1ですね。
J1~L1にデータがありM1に年齢表示したいで良いのでしょうか?

M1式(13列目)
=DATEDIF(J1,K1,"Y")+(DATEDIF(DATE(YEAR(J1)+DATEDIF(J1,K1,"Y"),MONTH(J1),DAY(J1)),K1,"D")+L1/"24:00")/(DATEDIF(DATE(YEAR(J1)+DATEDIF(J1,K1,"Y"),MONTH(J1),DAY(J1)),DATE(YEAR(J1)+DATEDIF(J1,K1,"Y")+1,MONTH(J1),DAY(J1)),"d"))

端数日を日数換算した後に年間日数で割っています。
うるう年は年間日数が366日になるので来年の誕生日までの日数をDATEDIFで計算させています。数式は長くなっていますが、数式事態は単純です。
M1の書式はユーザー定義書式 ##0.000"才"

投稿日時 - 2016-01-15 13:34:17

お礼

有難うございます。
計算してみましたが、答えは#NAME? でした。

J1は固定値ですので、参照させないで直接関数の中に数値を入れた方がいいのでしょうか。
教えていただいた、計算式難問で理解できませんが、J1を固定値12328とすると、年の計算値は1933、月の計算値は10、日の計算値は1になります。
頂いた式にこれを代入すると
=DATEDIF(12328,K1,"Y")+(DATEDIF(DATE(1933+DATEDIF(12328,K1,"Y"),10,1),K1,"D")+L1/"24:00")/(DATEDIF(DATE(1933+DATEDIF(12328,K1,"Y"),10,1),DATE(1933+DATEDIF(12328,K1,"Y")+1,10,DAY(12328)),"d"))
になりましたが、答えは#NAME? です

投稿日時 - 2016-01-17 01:31:28

ANo.2

 RC3 に次の関数を入力されると良いと思います。

=IF(COUNT(R1C10,RC1,RC2)=3,IF(AND(R1C10>1,R1C10<"9999/12/31"+0,RC1>1,RC1<"9999/12/31"+0,MOD(RC1,1)=0,RC2>=0,RC2<1),DATEDIF(R1C10,RC1,"y")+(DATEDIF(R1C10,RC1,"yd")+RC2)/(EDATE(R1C10,(DATEDIF(R1C10,RC1,"y")+1)*12)-EDATE(R1C10,DATEDIF(R1C10,RC1,"y")*12)),""),"")

投稿日時 - 2016-01-15 12:54:23

お礼

有難うございます

投稿日時 - 2016-01-16 23:47:28

ANo.1

https://sites.google.com/site/ekuserudenenreikeisan/

投稿日時 - 2016-01-15 12:05:42

お礼

有難うございます。
自分のEXCELにコピーできませんでした。

投稿日時 - 2016-01-16 23:49:02

-広告-
-広告-
-広告-
-広告-