こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

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

投稿日時 - 2017-04-16 19:07:35

QNo.9318282

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(13)

ANo.13

>予定表示欄2 B4=IF(COUNTIF(date,B2)>1,INDEX(event,MAX(INDEX((date=B2)*ROW(date),0))),"")の部分が難解ですが、その部分をもう少し勉強します。
この数式の一部は誤りです。
正しくは次の数式になります。
予定表示欄2 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)が返ることになります。
これらの論理をあなたが理解できなければ仕様変更したときに数式を変えることができなくなります。
丸写しの流用は慎むようにして頂ければ幸いです。

投稿日時 - 2017-04-18 00:17:44

お礼

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

投稿日時 - 2017-04-18 08:48:18

ANo.12

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

誤記
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()),""),"")

済みませんでした

投稿日時 - 2017-04-17 18:56:23

お礼

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

投稿日時 - 2017-04-17 21:58:45

ANo.11

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

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

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()),""),"")


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

投稿日時 - 2017-04-17 18:46:53

お礼

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

投稿日時 - 2017-04-17 22:50:31

ANo.10

回答No.7の修正です。

予定表示欄2 B4=IF(COUNTIF(date,B2)>1,INDEX(event,MAX(INDEX((date=B2)*ROW(date),0))),"")

予定表示欄2 B4=IF(COUNTIF(date,B2)>1,INDEX(event,LARGE(INDEX((date=B2)*ROW(date),0),COUNTIF(date,B2)-1)),"")

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

投稿日時 - 2017-04-17 12:12:54

お礼

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

投稿日時 - 2017-04-17 22:38:18

ANo.9

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

投稿日時 - 2017-04-17 10:29:25

お礼

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

投稿日時 - 2017-04-17 22:25:51

ANo.8

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

投稿日時 - 2017-04-17 09:20:46

お礼

直しました。多謝。

投稿日時 - 2017-04-17 22:23:27

ANo.7

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

投稿日時 - 2017-04-17 09:07:49

お礼

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

投稿日時 - 2017-04-17 22:22:17

ANo.6

失礼
崩壊、させる
とは

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

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


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

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

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

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

大丈夫、かな?

投稿日時 - 2017-04-17 03:06:37

ANo.5

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

此って
プロの、難易度で
公開すると
カレンダー業界を
崩壊させかねない
ので
ファイルでの、動作確認済み
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),""),"")

動作確認済み、です。

投稿日時 - 2017-04-17 02:33:20

お礼

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

投稿日時 - 2017-04-17 22:08:57

ANo.4

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

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

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

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

投稿日時 - 2017-04-16 23:09:43

お礼

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

投稿日時 - 2017-04-17 22:05:20

ANo.3

ああ、因みに
横向きの、検索は
HLOOUP、です

しかし、
INDEXや、LOOKUP系、
に、よる
詮索、抽出は、

データを、抱えさせる
其の、可能性が
あります、ので

出来れば、避けたく
思います

どの、関数を
使うか、には
拘らなくても、構いませんか?

投稿日時 - 2017-04-16 22:18:51

お礼

早速にありがとうございます。よろしくお願いします。関数種類にこだわりません。/
カレンダーは次のとおりです。/
第1行:日 月 火 水 木 金 土/
第2行:日付/ 
第3行:予定表示セル、その日の予定1を表示。(各セルにIFERROR)/
第4行:予定表示セル、その日の予定2を表示/
第5行:予定表示セル、その日の予定3を表示。/
1日を日曜日とすれば次の5行は8日の分です。/ 
そのセルの下方に予定記入表(縦長の表です。)/
 第1列・第2列の順に書きます。/
 第1行 5月1日 予定A(この行だけが表示される)/
 第2行 5月5日 予定B/
 第3行 5月1日 予定C(後から書き込んだこの行が無視される。)/
 表を送る方法を知らないので失礼。

投稿日時 - 2017-04-16 23:24:49

ANo.2

・具体例を3日分作って補足で挙げられませんか。
シートの部分の画像を張り付けるとか。
文章だけだと、構成がわかりにくい。
・IFERROR(VLOOKUPB5,event1,2,FALSE),"")
=IFERROR(VLOOKUP(B5,event1,2,FALSE),"")のミスだろう。
・eventのセル範囲は具体的には。

投稿日時 - 2017-04-16 22:17:46

お礼

早速にありがとうございます。よろしくお願いします。
カレンダーは次のとおりです。/
第1行:日 月 火 水 木 金 土/
第2行:日付/ 
第3行:予定表示セル、その日の予定1を表示。(各セルにIFERROR)/
第4行:予定表示セル、その日の予定2を表示/
第5行:予定表示セル、その日の予定3を表示。/
1日を日曜日とすれば次の5行は8日の分です。/ 
そのセルの下方に予定記入表(縦長の表です。)/
 第1列・第2列の順に書きます。/
 第1行 5月1日 予定A(この行だけが表示される)/
 第2行 5月5日 予定B/
 第3行 5月1日 予定C(後から書き込んだこの行が無視される。)/
 表を送る方法を知らないので失礼。

投稿日時 - 2017-04-16 23:22:30

ANo.1

時間頂けますか?

投稿日時 - 2017-04-16 20:58:29

お礼

早速にありがとうございます。時間の件は結構です。
よろしくお願いします。
カレンダーは次のとおりで3す。
第1行:日 月 火 水 木 金 土
第2行:日付 
第3行:予定表示セル、その日の予定1を表示。(各セルにIFERROR)
第4行:予定表示セル、その日の予定2を表示
第5行:予定表示セル、その日の予定3を表示。
1日を日曜日とすれば次の5行は8日の分です。 
そのセルの下方に予定記入表(縦長の表です。)
 第1列・第2列の順に書きます。
 第1行 5月1日 予定A(この行だけが表示される)
 第2行 5月5日 予定B
 第3行 5月1日 予定C(後から書き込んだこの行が無視される。)
 表を送る方法を知らないので失礼。

投稿日時 - 2017-04-16 23:19:28