vlookupで２番目の登録データを検索表示したい

質問

ｗ７、ＯＦＦＩＣＥ2007の初心ユーザーです。
ＰＣの画面に１カ月カレンダーを描き、各日付の下のセルにその日の予定を書きこむことを考えております。
１）７×５の月間カレンダー（日付、予定表示欄１、予定表示欄２）と、予定記入表（当月日付とその日の予定事項の２列）を作ります。
２）カレンダーの月指定セルをＢ５、範囲の名前をevent1 、予定を第２列としてカレンダー内の予定表示欄１には、
「＝IFERROR(VLOOKUPB5,event1,2,FALSE),"")」
と書き込みました。週の毎日の日付の記入欄にも複写します。
３）予定記入表には、日付の順序によらず、ランダムに書き足すように思いつく順に記入します。
４）vlookup 関数の検索方法を　２　と指定したので日付順序は気にしなくて良いのですが、同じ日に２個以上重要な予定がある場合などは、２番目以降の日付と予定は
無視されてしまします。
　そこでお尋ねします。予定表示欄１には最初に登録した予定を、予定表示欄２　には後日付け足した予定を、３番目以降では表示欄に＊印なり「予定記入表を見よ。」とでも表示したいのです。よろしくお願いします。

回答No.13

bunjii

>予定表示欄２ B4=IF(COUNTIF(date,B2)>1,INDEX(event,MAX(INDEX((date=B2)*ROW(date),0))),"")の部分が難解ですが、その部分をもう少し勉強します。
この数式の一部は誤りです。
正しくは次の数式になります。
予定表示欄２ B4=IF(COUNTIF(date,B2)>1,INDEX(event,LARGE(INDEX((date=B2)*ROW(date),0),COUNTIF(date,B2)-1)),"")
名前の定義を変えてあることは理解できていますよね？
dateの範囲にB2と同じ日付が1より多い（2以上）ときINDEX関数で2番目に一致する予定事項を抽出します。
LARGE関数の第1引数はINDEX関数で前処理を行ってその結果を配列のままLARGE関数へ渡し、COUNTIF関数で2番目に検出した位置（行番号）を算出しています。
例えばC4の数式で前処理部分は次の数式になります。
INDEX((date=C2)*ROW(date),0) → {0;2;0;4;0;0;0;0;0;・・・・・・}
このINDEX関数の第2引数を0または無指定にすると配列全体を返しますので論理式の(date=C2)に配列のROW(date)を乗ずることでdateの日付がC2と一致する位置の行番号と不一致の日付は0となる配列が返されます。
LARGE関数の第2引数は配列の値から大きい順の何番目か指定するものであり、COUNTIF関数で日付が一致する個数を調べてそこから1を減ずれば2番目に見つかった位置になります。
C4の例ではCOUNTIF関数の戻り値が2ですから1を減じて配列の値で1番大きい値の行番号（4）を外側のINDEX関数の第2引数へ返します。
C4へは配列名eventの4行目（予定C）が返ることになります。
これらの論理をあなたが理解できなければ仕様変更したときに数式を変えることができなくなります。
丸写しの流用は慎むようにして頂ければ幸いです。

お礼

アフターケア付きで感激です。
解説も付けていただき多謝。
私の数少ない知り合いに見せるものですから、公開というまでにも達しない
（せいぜい娘に見せる程度でしょう）から、ご心配は御無用ですし、ご趣旨も了解です。ありがとうございました。

回答No.12

Nouble

済みません
早速、間違えてました
お詫びの上
訂正させて、ください

誤記
B5に
=IF(CHOOSE(WEEKDAY(DATE($B$2,$C$2,1),2),"月","火","水","木","金","土","日")=B$4,1,
IF(ISNUMBER(A$5),A$5+1,"")
)

B6に
=IF(B5<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,2,FALSE()),""),"")

B7に
=IF(B6<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,2,FALSE()),""),"")

B8に
=IF(B7<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,2,FALSE()),""),"")

正記
B5に
=IF(CHOOSE(WEEKDAY(DATE($B$2,$C$2,1),2),"月","火","水","木","金","土","日")=B$4,1,
IF(ISNUMBER(A$5),A$5+1,"")
)

B6に
=IF(B5<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,2,FALSE()),""),"")

B7に
=IF(B6<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,3,FALSE()),""),"")

B8に
=IF(B7<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,4,FALSE()),""),"")

済みませんでした

お礼

早速にご教示多謝です。
iferrorやchoose関数から勉強しますので、結果が活用されるのは大分先になると思います。ありがとうございました。

回答No.11

Nouble

済みません
思うより
全然、簡単…
でしたね

此なら
万年カレンダー部を、省けば
公開して、十分
ですね

https://1drv.ms/x/s!AjviygfJDgV_kWcEoySAkjefZXqh
という
データ構造の、時

B5に
=IF(CHOOSE(WEEKDAY(DATE($B$2,$C$2,1),2),"月","火","水","木","金","土","日")=B$4,1,
IF(ISNUMBER(A$5),A$5+1,"")
)

B6に
=IF(B5<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,2,FALSE()),""),"")

B7に
=IF(B6<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,2,FALSE()),""),"")

B8に
=IF(B7<>"",IFERROR(
HLOOKUP(B$5,予定表!$A$3:$AE$6,2,FALSE()),""),"")

横に、フィルする
等で
工夫して、完成させます
末日処理は内緒です

お礼

お世話様でした。小生はweekday関数で万年暦を作り、予定表から
この万年暦にiferror関数で転記させました。第２予定が無視されるのは困ると注文されたのです。ありがとうございました。

回答No.10

bunjii

回答No.7の修正です。
誤
予定表示欄２ B4=IF(COUNTIF(date,B2)>1,INDEX(event,MAX(INDEX((date=B2)*ROW(date),0))),"")
正
予定表示欄２ B4=IF(COUNTIF(date,B2)>1,INDEX(event,LARGE(INDEX((date=B2)*ROW(date),0),COUNTIF(date,B2)-1)),"")

修正後の検証結果を画像で添付します。

お礼

小生のカレンダーでは、横列に日～土まで、縦行に「日付、予定表示欄１，２、３」ワンセットで５週分です。貴殿の御作と同じイメージです。
ありがとうございました。

回答No.9

imogasi

補足していただいてありがとう。
まだじっくり読んでいないのですが、昨日考えて、取り急ぎ参考に。
データを横方向に流しているので、ＶＬＯＯＫＵＰ関数でなく、
ＨＬＯＯＫＵＰ関数利用が適当の問題ではないかと思った。
https://dekiru.net/article/4447/
HLOOKUP関数で範囲を横方向に検索する
ーー
説明例　Ａ１：Ｅ７　　Ｆ列より右列は省略
Ａ３に日付を利用者が入力、Ａ４以下のセルに当日行事が出る　＜ーー関数で
ー 　2017/4/1 　2017/4/2 　2017/4/3 　2017/4/4
2017/4/4 　ｐｐｐ 　aaa 　　bbb 　　　　週間会議
週間会議 　ｋｋ 　bbb 　xxx 　　　Ａ社売り込み
Ａ社売り込み 　　　hhh 　　製品開発会議
製品開発会議 　　　　　　ポスター発注
ポスター発注
ーー
Ａ４セルに式を入れる　
=HLOOKUP($A$3,$B$2:$X$20,ROW()-2,FALSE)
下方向に式を複写
結果
上記例の通り。
ーー
取り急いでいるので、また本筋を強調するため、
HLOOKUPの前につける関数は省略（余分な０を出さないための）
日付の問題（日付シリアル値で統一されているか？）
も考慮から外した

お礼

例題でやってみます。ありがとうございました。

回答No.8

bunjii

回答No.7の予定表示欄２の数式に誤りがありました。
時間が無いので訂正の数式を午後までお待ちください。

お礼

直しました。多謝。

回答No.7

bunjii

他の回答者へのお礼に提示されている模擬データを参考に回答させていただきます。
範囲の名前を日付の列と予定の列を区別すればVLOOKUP関数以外の抽出法が容易になります。
日付の列をdateとし、予定をeventにしたとき次のような数式で良いと思います。
予定表示欄１ B3=IFERROR(VLOOKUP(B2,date:event,2,FALSE),"")
予定表示欄２ B4=IF(COUNTIF(date,B2)>1,INDEX(event,MAX(INDEX((date=B2)*ROW(date),0))),"")
予定表示欄３ B5=IF(COUNTIF(date,B2)>2,"※","")
貼付画像はExcel 2013で検証した結果ですがExcel 2007でも再現できます。
尚、J列をdate、K列をeventと命名しました。
B3:B5セルを右へH列までコピーし、B3:H5を纏めてB7:H9へコピーしたものです。

お礼

予定表示欄２ B4=IF(COUNTIF(date,B2)>1,INDEX(event,MAX(INDEX((date=B2)*ROW(date),0))),"")の部分が難解ですが、その部分をもう少し勉強します。ありがとうございました。

回答No.6

Nouble

失礼
崩壊、させる
とは

もう
何処に、でも
既に、転がっている

此の、様な
中では
大げさ、ですかね?

定番な事、しか
して、ないし

僕も
此の、様な
場で
教わった、内容

詰まり
ご好意で、頂いた
スキル…、だし

お求め、頂ける
なら…
此の、式
一つ位、なら…

大丈夫、かな?

回答No.5

Nouble

出来ました
でも、解るかどうか…

此って
プロの、難易度で
公開すると
カレンダー業界を
崩壊させかねない
ので
ファイルでの、動作確認済み
https://1drv.ms/i/s!AjviygfJDgV_kWOQ3LNpoBS43CwL
ですが、
データ構造と、主たる式、
だけに
しますね

解説も
しない事に、します
し、
申し訳、ないが
又著作権も、主張しないと
まずい気が、します。

故に、
許可、無く
改ざん、引用、転用、其の他、
を、
一切、禁止します。
済みません、
迷惑が、掛かるので
堪えてください。

では、行きますね
添付の、ような
データ構造の、時

=IF(B5<>"",IFERROR(
OFFSET(予定表!$A$2,
SMALL(
INDEX(
(B5=INDIRECT("予定表!"&CHAR(65-1+MATCH($C$2,予定表!$A$3:$W$3,0))&"$4:"&CHAR(65-1+MATCH($C$2,予定表!$A$3:$W$3,0))&"$103"))*ROW($A$1:$A$100)
+(B5<>INDIRECT("予定表!"&CHAR(65-1+MATCH($C$2,予定表!$A$3:$W$3,0))&"$4:"&CHAR(65-1+MATCH($C$2,予定表!$A$3:$W$3,0))&"$103"))*999999999999999,,),
ROW($A1))+1,
MATCH($C$2,予定表!$A$3:$W$3,0),
1,1),""),"")

動作確認済み、です。

お礼

一読して分るほど関数を勉強しておりません。新顔が多いので、勉強します。ありがとうございました。

回答No.4

bunjii

>カレンダー内の予定表示欄１には、「＝IFERROR(VLOOKUPB5,event1,2,FALSE),"")」と書き込みました。
数式に誤りがあります。（カッコが1つ抜けています）
=IFERROR(VLOOKUPB5,event1,2,FALSE),"")
　　　　　↓
=IFERROR(VLOOKUP(B5,event1,2,FALSE),"")
また、B5にはカレンダーの月指定の値と言っていますが日付の誤りではないでしょうか？

>４）vlookup 関数の検索方法を　２　と指定したので日付順序は気にしなくて良いのですが、同じ日に２個以上重要な予定がある場合などは、２番目以降の日付と予定は無視されてしまします。
「検索方法を　２　と指定した」は誤りです。値を返す列番号を2と指定した」が正しい表現です。検索方法の指定はTRUEまたはFALSEです。
VLOOKUP関数の仕様なので検索値（第1引数）に一致する値が複数のときは行番号の小さい方のみが返り値になります。

>そこでお尋ねします。予定表示欄１には最初に登録した予定を、予定表示欄２には後日付け足した予定を、３番目以降では表示欄に＊印なり「予定記入表を見よ。」とでも表示したいのです。
VLOOKUP関数の代わりにINDEX関数を使って2番目以降の予定を抽出すれば良いでしょう。
具体的な数式はevent1の模擬データを提示して頂けないと回答できません。

質問の文言には表現の誤りがあり、予定入力表の具体的な例や予定表示欄1、予定表示欄2とカレンダーの配置が示されていないので数式を提示することが困難です。

お礼

早速にお世話様です。index関数を勉強します。それで分らない場合には
また質問しますのでよろしく。
ありがとうございました。