エクセル２００３ 予定表

質問

いつも回答して頂きとても感謝しております。

・２０行目に項目
・２１行目に警告発生日　が入力してあります。

で、この２０～２１行目の情報を元に、２０行目の項目に対応する警告発生日が、４行目の日付に存在したら、警告発生日を含めてそれ以降の４行目の日付の列の９～１８行目に項目を表示させたいのです。どのような方法があるのでしょうか？宜しくお願い致します。

回答No.3

kagakusuki · ベストアンサー

>で、勉強の為に聞きたいのですが、式がつながり過ぎて、どの式がどの式につながっているのかさっぱり分かりません。
>よろしければ、少し教えて頂けないでしょうか？

　まず考え方を説明致します。

　当たり前の話ですが、項目名が現れる順番は警告発生日の日付が古い順となります。
　そして、

>１つ目は、aa/bb/ccの順ではなく、9行目にeeと一度表示させたらeeの表示行を9行目に固定させたい。２つ目は、周期到達日以降も表示させたい。

という条件により、
「１度表示された項目名は、それ以降の日付が入力されている列においても常に同じ列に表示され続け、途中で消える事は無い」
という事になりますから、警告発生日の古い項目名から先に表示され、先に表示されたものが上の行に表示され、後から表示されたものが下の行に表示されるのですから、
「9行目～18行目に表示される項目名の順番も又、警告発生日が古いものが上、若いものが下になる様に表示しなければならない」
という事になります。（各行に表示する項目名は、警告発生日によって最初から決まっている）
　そして、9行目～18行目の各行において決まっている項目名を、項目名毎に決まっている警告発生日に達した処で、表示する様にすれば良い訳です。

　次に関数の中身に関して説明致します。

=IF(OR(ROWS($9:9)>COUNTIF($21:$21,"<="&C$4),ISERROR(1/(YEAR(C$4)>1904))),"",INDEX($20:$20,MATCH(SMALL($21:$21,ROWS($9:9)),$21:$21,0)))

という関数の先頭で使われているIF関数において、

OR(ROWS($9:9)>COUNTIF($21:$21,"<="&C$4),ISERROR(1/(YEAR(C$4)>1904)))

という部分が判定式の部分であり、

""

という部分が「判定が真の場合に行う処理」の部分であり、

INDEX($20:$20,MATCH(SMALL($21:$21,ROWS($9:9)),$21:$21,0))

という部分が「判定が偽の場合に行う処理」の部分です。
　判定式の所ではOR関数でまとめられた２つの判定が行われていて、2つの判定の内の後半に記述されている方の

ISERROR(1/(YEAR(C$4)>1904))

という判定式は、「その関数が入力されている列において、4行目に入力されている値が、日付のデータでない場合においてTRUEとなる関数」です。

YEAR(C$4)

という具合にC4セルの値をYEAR関数をで処理する場合、C4セルに入力されている値が、万が一、文字列や負の数といった日付には変換出来ない値であった場合には、年数を求める事が出来ずにエラーとなりますから、エラーとなった事をISERROR関数を使って検知する事で、IF関数の「真の場合」の所に記述されている

""

という表示、即ち何も表示しない状態とし、入力ミス等で日付ではない変な値が入力されていた場合であっても、エラーが表示される様な事態となる事を回避している訳です。
　尚、4行目の日付欄に何も入力されていない場合、空欄は数値の0として扱われ、Excelが日付データを扱う際には数値の0は1900年1月0日1904年1月1日と見做されますから、C4セルが空欄となっている場合には、

YEAR(C$4)

の値が1904となる恐れがあります。
　そこで、4行目のセルが空欄である場合には表示を空欄とするために、ISERROR関数の中身を

1/(YEAR(C$4)>1904)

とする事で、YEAR関数の結果が1904年以下となった場合には、次の様に

1/(YEAR(C$4)>1904)
　　↓
1/(YEAR("")>1904)
　　↓
1/(YEAR(0)>1904)
　　↓
1/(1904>1904)
　　↓
1/(FALSE)
　　↓
1/(0)
　　↓
1/0
　　↓
#DIV/0!

ISERROR関数の中身がエラーとなる様にしている訳です。

　次に、判定式の前半である

ROWS($9:9)>COUNTIF($21:$21,"<="&C$4)

という部分の中の、左辺のROWS関数

ROWS($9:9)

によって、9行目～18行目の範囲内において上から数えて何行目になるのかを計算しています。
　一方、右辺の

COUNTIF($21:$21,"<="&C$4)

という部分は、21行目に入力されている複数の警告発生日の中に、４行目に入力されている日付以下の日付（より古い日付）が何日あるのかという事をカウント、即ち、４行目の日付の日には警告発生日に達している項目の数をカウントしています。
　ですから、

ROWS($9:9)>COUNTIF($21:$21,"<="&C$4)

という部分は、その関数が入力されている行が、行番号9行の行を第１行とした場合の何行目に当たるのかという行数を数えて、その行数が、警告発生日に達している項目の数よりも多い場合には、TRUEを返す事により、セルの表示をIF関数の「真の場合」の所に入力されている値である空欄としている訳です。
　そして、４行目に入力されている値が日付データであり、尚且つ、関数が入力されている行の（9行目から数えた）行数が、警告発生日に達している項目の数以下である場合には、IF関数の「偽の場合」の処理へと進む事になります。

　「判定が偽の場合に行う処理」の部分である

INDEX($20:$20,MATCH(SMALL($21:$21,ROWS($9:9)),$21:$21,0))

というINDEX関数の中の

SMALL($21:$21,ROWS($9:9))

という部分は、（9行目から数えた）行数が１行目の場合には、21行目に入力されている数値の中で、１番小さな値、即ち１番古い警告発生日を返します。
　同様に、2行目においては2番目に古い警告発生日を返し、3行目においては3番目に古い警告発生日を返し、・・・・・10行目においては10番目に古い警告発生日を返します。
　次に、

MATCH(SMALL($21:$21,ROWS($9:9)),$21:$21,0)

という部分において、SMALL関数で求めた警告発生日をMATCH関数の検索値とする事で、21行目の中の何列目に、該当する警告発生日が入力されているのかを求めます。
　次に、そうして求めた警告発生日が入力されている列番号を表す値を、

INDEX($20:$20,MATCH(SMALL($21:$21,ROWS($9:9)),$21:$21,0))

の様に、INDEX関数における列番号を指定する値として用いる事により、「9行目から数えた行数」番目に古い日付が入力されている列の、20行目の値、即ち、項目名を求めている訳です。

お礼

返事が遅れてしまい申し訳ありませんでした。
事細かく教えて下さりありがとうございます。今後の式を書く時の参考に出来る様に自分のスキルが上がればと思っています。本当にありがとうございました。