参照するセルに関数が入っていると#VALUE!に

質問

いつもお世話になります。
WIN7 EXCELL2010 です。

年度を変更し時にO列の最終行(参照図ではO24)が変わりその時、最終行以降のセルに関数が入っているとI／J列に#VALUE!が出ます。
年度を変更した時のエラーを解決したいのですがご教示いただけませんでしょうか。

O列は B列から参照した値をN列に取り込みそれを詰めたものがO列です。
この時N列の最終の値をとったO列の最終行以降(O24)のセルに関数を入力していると　I /J 列にエラーが発生し、
その解決策として、この場合で言うと　O25　からは関数を入れずに対応するとエラーがでません。
I ／J 列にそれぞれの日数を求めるのですが参照するのはO列です。

参考
B6 =DATE($B$4,1,1)
B8 =DATE(B$4,1,14-WEEKDAY(DATE(B$4,1,0),3))
G2 =DATE($B$4,H2,1)
I2 =NETWORKDAYS(DATE($B$4,H2,1),DATE($B$4,H2+1,),$Q$1:$Q$49)
J2 =K2-I2
K2 =DAY(DATE(YEAR($B$3),MONTH(G2)+1,0))
M1 =IF(B6="","",B6)
N1 =IF(M1="","",COUNT($M$1:M1))
O1 =IF(COUNT($M$1:$M$42)<ROW(M1),"",SUMIF($N$1:$N$42,ROW(M1),$M$1:$M$42)

回答No.2

kagakusuki · ベストアンサー

回答No.1です。

　申し訳御座いません、J列の値には土日もカウントしなければならない事を見落としておりました。
　ですから、J2セルに入力する関数を次のものと差し替えて下さい。

=IF(AND(COUNT($B$6:$B$29),ISNUMBER($K2)),SUMPRODUCT(((WEEKDAY(DATE($B$4,$H2,ROW(INDIRECT("A1:A"&DAY(DATE($B$4,$H2+1,))))),3)>4)+COUNTIF($B$6:$B$29,DATE($B$4,$H2,ROW(INDIRECT("A1:A"&DAY(DATE($B$4,$H2+1,))))))>0)*1),"")

回答No.1

kagakusuki

ちょっと祝祭日の名称が古いままでしたので、直してみました。

B6セルは「元旦」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,1,1),"")

B7セルは「振替」で、そこに入力する関数は

=IF(ISNUMBER(1/(WEEKDAY(B6)=1)),B6+1,"")

B8セルは「成人」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE(B$4,1,14-WEEKDAY(DATE(B$4,1,0),3)),"")

B9セルは「建国」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,2,11),"")

B10セルは「振替」で、そこに入力する関数は

=IF(ISNUMBER(1/(WEEKDAY(B9)=1)),B9+1,"")

B11セルは「春分」で、、必ず手入力して下さい。

B12セルは「振替」で、そこに入力する関数は

=IF(ISNUMBER(1/(WEEKDAY(B11)=1)),B11+1,"")

B13セルは「昭和」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,4,29),"")

B14セルは「振替」で、そこに入力する関数は

=IF(ISNUMBER(1/(WEEKDAY(B13)=1)),B13+1,"")

B15セルは「憲法」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,5,3),"")

B16セルは「みどり」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,5,4),"")

B17セルは「こども」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,5,5),"")

B18セルは「振替」で、そこに入力する関数は

=IF(AND(ISNUMBER(1/(WEEKDAY(B17)<4)),ISNUMBER(B17)),B17+1,"")

B19セルは「海」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE(B$4,7,21-WEEKDAY(DATE(B$4,7,0),3)),"")

B20セルは「敬老」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE(B$4,9,21-WEEKDAY(DATE(B$4,9,0),3)),"")

B21セルは「国民」で、そこに入力する関数は

=IF(ISNUMBER(1/(B22=B20+2)),B20+1,"")

B22セルは「秋分」で、必ず手入力して下さい。

B23セルは「振替」で、そこに入力する関数は

=IF(ISNUMBER(1/(WEEKDAY(B22)=1)),B22+1,"")

B24セルは「体育」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE(B$4,10,14-WEEKDAY(DATE(B$4,10,0),3)),"")

B25セルは「文化」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,11,3),"")

B26セルは「振替」で、そこに入力する関数は

=IF(ISNUMBER(1/(WEEKDAY(B25)=1)),B25+1,"")

B27セルは「勤労」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,11,23),"")

B28セルは「天皇」で、そこに入力する関数は

=IF(ISNUMBER(($B$4&"/1/1")+0),DATE($B$4,12,23),"")

B29セルは「振替」で、そこに入力する関数は

=IF(ISNUMBER(1/(WEEKDAY(B28)=1)),B28+1,"")

　尚、「子供の日」の次に来るB18セルの振替休日だけは、他の振り替え休日とは関数が異なっておりますから、注意して下さい。

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

=IF(ISNUMBER(($B$4&"/"&$H2&"/1")+0),DAY(DATE($B$4,$H2+1,)),"")

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

=IF(AND(COUNT($B$6:$B$29),ISNUMBER($K2)),COUNTIF($B$6:$B$29,"<"&DATE($B$4,$H2+1,1))-COUNTIF($B$6:$B$29,"<"&DATE($B$4,$H2,1)),"")

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

=IF(ISNUMBER(1/($K2-$J2)),$K2-$J2,"")

　次に、I2～K2の範囲をコピーして、I3～K13の範囲に貼り付けて下さい。
　次に、I14セルに次の関数を入力して下さい。

=SUM(I$2:I$13)

　次に、I14セルをコピーして、J14～K14の範囲に貼り付けて下さい。

　以上です。

　※M列～O列の作業列は不要です。

お礼

ご丁寧にご指導いただき誠に有難うございます。
簡素化されてスッキリして本当にうれしい限りです。

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