EXCEL数式について質問です。

質問

EXCEL数式について質問です。アドバイスを頂きたいです。

(1)やりたいこと。

　作業者毎のイベントをマスタ化し、
　スケジュール表に合う日付にマスタからのイベントを抽出したい。

　EXCEL数式またはVBAにて抽出

(2)方策
　・B1セルにて作業者名を選択する。

　・B3～F3セルは1週間のスケジュール表
　　B5～F5セルに　AさんイベントH2:H5　BさんイベントK2:K5
　　を表示させたい。
　　（B3～F3とI2:I5（Aさん日付）K2:K5（Bさん日付）を照合する）

以上の件アドバイスよろしくお願い致します。

回答No.3

kagakusuki · ベストアンサー

>もしイベント欄が２つ３つ欲しい場合にはどのようにすればよろしいでしょうか。

　幾つかの方法が考えられますが、いずれも一長一短があります。
　まず、共通部分に関して説明致します。

　今仮に、作業者の氏名の入力欄がSheet1のB1セルであり、Sheet1のB列～H列にスケジュール表があり、
　スケジュール表は、行番号で3行が日付欄、4行が曜日欄、そして、5行目以下が抽出したイベントの表示欄であるものとします。
　又、Sheet1のJ列がAさんのイベント内容欄、K列がAさんのイベントの日取り欄、L列がBさんのイベント内容欄、M列がBさんのイベントの日取り欄、N列がCさんのイベント内容欄、O列がCさんのイベントの日取り欄であり、
J1セルには「Aさんイベント」、L1セルには「Bさんイベント」、N1セルには「Cさんイベント」と入力されているものとします。

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

=IF(ISNUMBER(1/DAY($B$3)),$B$3+COLUMN()-COLUMN($B$3),"")

　次に、Sheet1のB4セルをコピーして、Sheet1のC3～H4の範囲に貼り付けて下さい。
　次に、Sheet1のB3～H3のセル範囲にあるセルの書式設定の表示形式を[日付]の

3月14日

として下さい。
　次に、Sheet1のB4～H4のセル範囲にあるセルの書式設定の表示形式を[ユーザー定義]の

aaa

として下さい。
　これで、Sheet1のB3セルにその週の最初の日付を入力するだけで、C3～H3には、その週の他の日付が表示され、その下の4行目には各曜日が表示されます。

　以下は、各方法によって異なる点に関する説明です。

【方法その1】
　作業列を必要とする方法です。
　今仮に、Sheet2のA列を作業列として使用するものとします。

　まず、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/DAY(INDEX(Sheet1!$J:$O,ROW(),MATCH(Sheet1!$B$1&"イベント",Sheet1!$J$1:$O$1,0)+1))),INDEX(Sheet1!$J:$O,ROW(),MATCH(Sheet1!$B$1&"イベント",Sheet1!$J$1:$O$1,0)+1)+COUNTIF(INDEX(Sheet1!$J$1:$O$1,MATCH(Sheet1!$B$1&"イベント",Sheet1!$J$1:$O$1,0)+1):INDEX(Sheet1!$J:$O,ROW(),MATCH(Sheet1!$B$1&"イベント",Sheet1!$J$1:$O$1,0)+1),INDEX(Sheet1!$J:$O,ROW(),MATCH(Sheet1!$B$1&"イベント",Sheet1!$J$1:$O$1,0)+1))*10000000,"")

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。

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

=IF(ISERROR(1/(INDEX($J:$O,MATCH(B$3+ROWS($5:5)*10000000,Sheet2!$A:$A,0),MATCH($B$1&"イベント",$J$1:$M$1,0))<>"")),"",INDEX($J:$O,MATCH(B$3+ROWS($5:5)*10000000,Sheet2!$A:$A,0),MATCH($B$1&"イベント",$J$1:$M$1,0)))

　次に、Sheet1のB5セルをコピーして、Sheet1のB5～H5の範囲に貼り付けて下さい。
　次に、Sheet1のB5～H5の範囲をコピーして、同じ列の（行番号で）6行目以下に貼り付けて下さい。

【方法その2】
　この方法は作業列を設ける必要はありませんが、元データとなるイベントの日取りが順序良く（昇順でも降順でも、どちらであっても構いません）並んでいる必要があります。

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

=IF(ISNUMBER(1/(INDEX($J:$O,MATCH(B$3,OFFSET($K:$K,,MATCH($B$1&"イベント",$J$1:$O$1,0)-1),0)+ROWS($5:5)-1,MATCH($B$1&"イベント",$J$1:$O$1,0))<>"")/(INDEX($J:$O,MATCH(B$3,OFFSET($K:$K,,MATCH($B$1&"イベント",$J$1:$O$1,0)-1),0)+ROWS($5:5)-1,MATCH($B$1&"イベント",$J$1:$O$1,0)+1)=B$3)),INDEX($J:$O,MATCH(B$3,OFFSET($K:$K,,MATCH($B$1&"イベント",$J$1:$O$1,0)-1),0)+ROWS($5:5)-1,MATCH($B$1&"イベント",$J$1:$O$1,0)),"")

　次に、Sheet1のB5セルをコピーして、Sheet1のB5～H5の範囲に貼り付けて下さい。
　次に、Sheet1のB5～H5の範囲をコピーして、同じ列の（行番号で）6行目以下に貼り付けて下さい。

【方法その3】
　この方法は作業列を設ける必要はありませんし、日取りが順不同に入力されていても構いませんが、関数のネストレベルが7を上回っているため、Excel2007以降のバージョンでなければ使用する事が出来ませんし、SUMPRODUCT関数を使用しているため、元データの行数が数千行にもなる場合には、計算処理のに要する時間が長くなり過ぎる恐れがあります。

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

=IF(ISNUMBER(1/(ROWS($5:5)<=COUNTIF(OFFSET($K:$K,,MATCH($B$1&"イベント",$J$1:$O$1,0)-1),B$3))),INDEX($J:$O,SUMPRODUCT((COUNTIF(OFFSET($J$1,1,MATCH($B$1&"イベント",$J$1:$O$1,0),ROW($J$1:INDEX($J:$J,MATCH(9E+99,OFFSET($K:$K,,MATCH($B$1&"イベント",$J$1:$O$1,0)-1))-ROW($J$1)))),B$3)<ROWS($5:5))*1)+ROW($J$1)+1,MATCH($B$1&"イベント",$J$1:$O$1,0))&"","")

　次に、Sheet1のB5セルをコピーして、Sheet1のB5～H5の範囲に貼り付けて下さい。
　次に、Sheet1のB5～H5の範囲をコピーして、同じ列の（行番号で）6行目以下に貼り付けて下さい。

お礼

ありがとうございました。
非常に役に立ちました。＾＾

回答No.2

kagakusuki

まず、B5セルに次の数式を入力して下さい。

=IF(ISERROR(1/(INDEX($H:$K,MATCH(B$3,OFFSET($I:$I,,MATCH($B$1&"イベント",$H$1:$K$1,0)-1),0),MATCH($B$1&"イベント",$H$1:$K$1,0))<>"")),"",INDEX($H:$K,MATCH(B$3,OFFSET($I:$I,,MATCH($B$1&"イベント",$H$1:$K$1,0)-1),0),MATCH($B$1&"イベント",$H$1:$K$1,0)))

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

　以上です。

補足

ご回答りがとうございました。

もうひとつ質問お願い致します。

もしイベント欄が２つ３つ欲しい場合にはどのようにすればよろしいでしょうか。

Aさんイベント

練習 12/19
マッサージ 12/19

　　　　　12/19
イベント　 (月）
　　　　　練習
　　　　　マッサージ

などのようです。

以上よろしくお願い致します。

回答No.1

bvltiggeari

こんな感じでやってみるのはどうでしょう？

ワークシート側
１．B1セルは入力規則を用いてドロップダウンを作る
２．ドロップダウンにて値が変わったらマクロ実行

マクロ側
１．B3～F3の値を取得
２．B1の値を取得
３．B1の値でチェックする列がI列なのかK列なのかを決める
４．対象の列にB3～F3の値があるか上からチェック
５．あれば左隣の値をB5以降に入れる

どんなアドバイスが欲しいのか分からなかったので、
処理プロセスのアドバイスということで(^^;