エクセル２００３ 予定表

質問

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

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

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

回答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行目の値、即ち、項目名を求めている訳です。

お礼

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

回答No.2

kagakusuki

>周期到達日以降も表示させたい。

　これは先走りが過ぎてしまい、大変失礼致しました。

　それでしたら、C9セルに次の関数を入力してから、C9セルをコピーして、C9～AG18の範囲に貼り付けて下さい。

=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)))

お礼

自分が思っていた通りに表示されているのでびっくりです。
で、勉強の為に聞きたいのですが、式がつながり過ぎて、どの式がどの式につながっているのかさっぱり分かりません。
よろしければ、少し教えて頂けないでしょうか？
宜しくお願い致します。

回答No.1

kagakusuki

前回の御質問

【参考URL】
　エクセル２００３　予定表 【OKWave】
　　http://okwave.jp/qa/q8351573.html

でも思っておりましたが、説明が滅茶苦茶だと思います。
　前回の御質問に対して回答した際には、私は御質問文に記されている内容からパターンを読み取る事で、質問者様がどの様な事をおやりになりたいと思っておられるのかを推測して回答したため、前回の御質問におけるshintaro-2様の御回答に対する補足を読んでいなかったのですが、その補足内容を改めて読み直してみました処、そちらも又、説明が無茶苦茶で、

>(項目aaが1/2に達していないので)

と書かれている後に、

>(項目aaが1/2に達してたので)

等と書かれておるなど、内容が矛盾しております。

　今回の御質問内容も、おそらくは

>２０行目の項目に対応する警告発生日が、４行目の日付に存在したら、警告発生日を含めてそれ以降の４行目の日付の列の９～１８行目に項目を表示させたいのです。

という事ではなく、
「C21～T21のセル範囲に入力されている警告発生日の中に、各列の4行目に入力されている日付以前の日付が入力されているものがあれば、その該当する警告発生日が入力されている列（複数の場合もあります）の中の、20行目の所に入力されている項目名を全て取り出して、4行目に入力されている日付の下の行番号９～１８行の所に、漏らさずに表示させたい」
という事ではないでしょうか？
　但しこれは、この質問No.8355264で質問者さまが仰っておられる条件を、日本語の文章として意味が通じる様に意訳しただけの内容であり、質問者様が仰っておられると思われる、その条件のままでは、4行目に警告発生日以降の日付が入力されている全ての列において項目名は表示される事になりますから、周期到達日以降の日付となっている列においても、項目名は消える事なく表示されるという事になりますが、それで宜しいのでしょうか？

　前回の御質問内容から考えて、おそらくはそういう事ではなく、
「各列の4行目に入力されている日付と比較して『C21～T21のセル範囲に入力されている警告発生日の中で先述の4行目の日付以降（4行目の日付も含む）の日付となっていて、尚且つ、その警告発生日の下の行番号22行の所に入力されている周期到達日が先述の4行目の日付よりも前の日付（4行目の日付を含まない）となっている』という条件を満たしている列があれば、その列（複数の場合もあります）の20行目の所に入力されている項目名を全て取り出して、先述の4行目に入力されている日付の下の行番号9～18行の範囲内に、漏らさずに表示させたい」
という事なのではないかと思います。

　仮に、上記の私の推測が間違ってはいないものとした場合には、次の様な方法となります。

　まず、C9セルに次の様な関数を入力して下さい。

=IF(OR(ROWS($9:9)>C$6,ISERROR(1/C$6)),"",INDEX($C$20:$T$20,SUMPRODUCT((COUNTIF(OFFSET($B$21,,1,1,COLUMN($C$21:$T$21)-COLUMN($B$21)),"<="&C$4)-COUNTIF(OFFSET($B$22,,1,1,COLUMN($C$22:$T$22)-COLUMN($B$22)),"<="&C$4)<ROWS($9:9))*1)+1))

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

　以上です。

お礼

前回に引き続き回答して頂きありがとうございます。
説明が滅茶苦茶で申し訳ありません。入力している時、何度も読み直して入力しているつもりなんですが、ここに投稿していると、同じような御指摘を何度も受けてしまいます。日本語って難しいですね。
で、今回の提示していただいた式を貼り付けてみました。
少し自分が思っている事と違うので、違う部分の説明をさせて頂きます。

１つ目は、aa/bb/ccの順ではなく、9行目にeeと一度表示させたらeeの表示行を9行目に固定させたい。２つ目は、周期到達日以降も表示させたい。の２点です。自分で修正を試みようと思いましたが、式が長くてどうすれば自分の思ったようになるのか分かりません。kagakusukiさんに頼りっぱなしで申し訳ないのですが、宜しくお願い致します。