[[20190117100233]] 『INDEX MATCH 関数』(MOMO) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『INDEX MATCH 関数』(MOMO)

INDEX MATCH 関数で、算出されるのが日付の場合、
書式を標準にしておくと 43515 と算出されるため、
書式を日付にして 2019/2/19 と算出されるようにしたのですが、
もし、算出されるのが日本語 例えば 1週間  と言った場合、
この書式設定にしておくと 該当なしで算出されないのですが、
どのような書式設定にすれば 日付も、文字も両方算出されるようになるでしょうか。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 どのような式でデータがどうなっているかを書いてみてくれ。
(ねむねむ) 2019/01/17(木) 10:11

このような感じです。
B2=AAAが 4パターンあるため(B2=BBB、CCC、DDD)、
この後計算式の後に 続きます。

=+IF(B2="AAA",+IF(ISERROR(0/INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0))),"該当なし",INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0)))

宜しくお願い致します。

(MOMO) 2019/01/17(木) 10:20


 0/INDEXの結果をISERR → 0/文字はエラー → なんで「該当なし」
(GobGob) 2019/01/17(木) 10:27

 ISERRORやめて、「OKな文字」と「数値」で判断すればいいんでない?

 どっかにOK文字リスト作って

 =IF(B2="AAA",IF(OR(COUNTIF(OKリスト,INDEX(・・・,MATCH(・・・))),COUNT(INDEX(・・・,MATCH(・・・)))>0),INDEX(・・・,MATCH(・・・)),"該当なし"))

 みたいな感じ。
(GobGob) 2019/01/17(木) 10:30

ありがとうございます。
以下のようにやってみましたが エラーとなります。
OKリストは別シートのA列に該当しそうな文字をいくつか入れました。

=IF(B2="AAA",IF(OR(COUNTIF(OKリスト!$A$1:$A$20,INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0))),IF(B2="BBB",IF(OR(COUNTIF(OKリスト!$A$1:$A$20,INDEX('関西'!$D$3:$UC$370,MATCH($P2,'関西'!$D$3:$D$370,0),MATCH($Q2,'関西'!$D$3:$UC$3,0))),+IF(B2="CCC",IF(OR(COUNTIF(OKリスト!$A$1:$A$20,INDEX('北海道'!$D$3:$UC$368,MATCH($P2,'北海道'!$D$3:$D$368,0),MATCH($Q2,'北海道'!$D$3:$UC$3,0))),IF(B2="DDD",+IF(OR(COUNTIF(OKリスト!$A$1:$A$20,INDEX('九州'!$D$3:$UC$368,MATCH($P2,'九州'!$D$3:$D$368,0),MATCH($Q2,'九州'!$D$3:$UC$3,0))),"該当なし"))))
(MOMO) 2019/01/17(木) 10:47


 IFの順位を逆にしてるからね。。。

 最初の提示   =IF(エラー判断,エラー処理,INDEX処理)
 オイラ案     =IF(文字、数値判断,INDEX処理,エラー処理)

 なので、 真の場合がINDEX処理せず B2セルを見るほうの処理に変化したりしてるね。
(GobGob) 2019/01/17(木) 11:01

 とりあえずB2が「AAA」の場合の確認だけ

 ↓ だと何か問題ありますか?

 =IF(B2="AAA",IFERROR(INDEX(関東!$D$3:$UC$374,MATCH($P2,関東!$D$3:$D$374,0),MATCH($Q2,関東!$D$3:$UC$3,0)),"該当なし"),"")

 表示形式〜ユーザー定義 yyyy/m/d;;;@

 以上です
(笑) 2019/01/17(木) 11:06

(笑)様

計算式で1箇所だけやってみたら FALSE となりました。
(MOMO) 2019/01/17(木) 11:18


GobGob様

これでもFALSEになります

=IF(B2="AAA",IF(OR(COUNTIF(OKリスト!$A$1:$A$20,INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0))),COUNT(INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0)))>0,INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0))),"該当なし"))
(MOMO) 2019/01/17(木) 11:37


 ORの後ろのカッコ「)」がないけど。
(GobGob) 2019/01/17(木) 11:43

 どんな式で試したんですか?
 こっちが提示した式なら FALSE なんかになるわけないと思うんですけど・・・。

 ついでに確認
 ・B2以外にも「AAA」とかを入力するセルがありますか?
 ・シートによって微妙に行数が異なってますけど、検索の数式では全部同じ行数にすると何かまずいことがありますか?

 以上です
(笑) 2019/01/17(木) 11:53

(笑)様
行数は 同じで大丈夫です。
B2以外ありません。

先程 記載いただいた↓ で再度やってみたら できました。

=IF(B2="AAA",IFERROR(INDEX(関東!$D$3:$UC$374,MATCH($P2,関東!$D$3:$D$374,0),MATCH($Q2,関東!$D$3:$UC$3,0)),"該当なし"),"")

ここから、、+IF(B2="BBB",..... と 3つ続けて最後↓ ここで 数式が正しくない と出てしまいました。

MATCH($Q2,'九州'!$D$3:$UC$3,0))"該当なし",""))
(MOMO) 2019/01/17(木) 11:56


訂正

・・・・・・MATCH($Q2,'九州'!$D$3:$UC$3,0)),"該当なし"),"") です
(MOMO) 2019/01/17(木) 12:02


=IF(B2="AAA",IFERROR(INDEX('関東'!$D$3:$UC$374,
MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関西'!$D$3:$UC$3,0)),
+IF(B2="BBB",IFERROR(INDEX('関西'!$D$3:$UC$374,
MATCH($P2,'関西'!$D$3:$D$374,0),MATCH($Q2,'Kなさい'!$D$3:$UC$3,0))
+IF(B2="CCC",IFERROR(INDEX('北海道'!$D$3:$UC$374,MATCH($P2,
'北海道'!$D$3:$D$374,0),MATCH($Q2,'北海道'!$D$3:$UC$3,0)),
+IF(B2="DDD",IFERROR(INDEX('九州'!$D$3:$UC$374,MATCH($P2,'九州
'!$D$3:$D$374,0),MATCH($Q2,'九州'!$D$3:$UC$3,0))"該当なし"),""))

これですと 数式中に対応するかっこがありません とエラーになります。
(MOMO) 2019/01/17(木) 12:53


 どこでもいいですけど、例えばA2セルにでも ↓ の式を入れる。

 A2 =IF(B2="","",IF(B2="AAA","関東",IF(B2="BBB","関西",IF(B2="CCC","北海道","九州"))))

 B2セルは入力規則のリストにして、4つ以外のものは入力できないようにしておく。

 検索の数式
 =IF(B2="","",IFERROR(INDEX(INDIRECT(A2&"!D3:UC374"),MATCH(P2,INDIRECT(A2&"!D3:D374"),0),MATCH(Q2,INDIRECT(A2&"!D3:UC3"),0)),"該当なし"))

 表示形式〜ユーザー定義 yyyy/m/d;;;@

 行数は、一番多い関東シートの「374」にしています。
 これで検証してみてください。

 参考までに VLOOKUP でもできないことはないです(横長の表なのがちょっとアレですけど)

 =IF(B2="","",IFERROR(VLOOKUP(P2,INDIRECT(A2&"!D3:UC374"),MATCH(Q2,INDIRECT(A2&"!D3:UC3"),0),FALSE),"該当なし"))

 以上です
(笑) 2019/01/17(木) 13:03

(笑)様

ありがとうございます。

A3に 計算式を入れ、

検索の数式を

=IF(B2="","",IFERROR(INDEX(INDIRECT(A3&"!D3:UC374"),MATCH(P2,INDIRECT(A3&"!D3:D374"),0),MATCH(Q2,INDIRECT(A3&"!D3:UC3"),0)),"該当なし"))

とし、表示形式を設定しましたが、
該当なし と算出されました。
実際は 6週間 と検索されるはずです。

先程の、
=IF(B2="AAA",IFERROR(INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0)),"該当なし"),"") で 6週間と出たのですが、、 何が違うのかわかりません。

(MOMO) 2019/01/17(木) 13:42


 「該当なし」ということはエラーになってるってことですね。
 IFERROR関数を外して ↓ の式だと何というエラーになりますか?

 =INDEX(INDIRECT(A3&"!D3:U374"),MATCH(P2,INDIRECT(A3&"!D3:D374"),0),MATCH(Q2,INDIRECT(A3&"!D3:U3"),0))

 それとシート名は本当に「関東、関西、北海道、九州」なんですか?

 一応 ↓ の式でもどうなるか試してみてください。

 =IF(B2="","",IFERROR(INDEX(INDIRECT("'"&A3&"'!D3:UC374"),MATCH(P2,INDIRECT("'"&A3&"'!D3:D374"),0),MATCH(Q2,INDIRECT("'"&A3&"'!D3:UC3"),0)),"該当なし"))
                                     ~~~~~~~~~                              ~~~~~~~~~~                               ~~~~~~~~~
 以上です
(笑) 2019/01/17(木) 14:22

(笑)様

 =INDEX(INDIRECT(A3&"!D3:U374"),MATCH(P2,INDIRECT(A3&"!D3:D374"),0),MATCH(Q2,INDIRECT(A3&"!D3:U3"),0))

→この式ですと #REF! となります。

=IF(B2="","",IFERROR(INDEX(INDIRECT("'"&A3&"'!D3:UC374"),MATCH(P2,INDIRECT("'"&A3&"'!D3:D374"),0),MATCH(Q2,INDIRECT("'"&A3&"'!D3:UC3"),0)),"該当なし"))

→この式ですと、「6週間」と算出されました!
 ありがとうございます。
  "'"を入れることで変わるのですね・・
  また、!D3:UC374 は絶対値にしなくても算出されるのですか?

(MOMO) 2019/01/17(木) 14:39


 >また、!D3:UC374 は絶対値にしなくても算出されるのですか?

 $D$3:$UC$374 のように「$」を付けて絶対参照にしなくてもいいのか、ということ?

 "'!D3:UC374"
 "'!D3:D374"
 "'!D3:UC3"

 上記は全て "" で囲って文字列にしています。
 なので、その式を他のセルにコピーしても範囲は変化しません。
 → 絶対参照にする必要はない(してもしなくても同じ)

 というか、その式をどこかにコピーするんですか?
 コピーしないんだったら、文字列でなくても絶対参照にする必要はありませんよ。

 参考まで
(笑) 2019/01/17(木) 15:34

(笑)様

すみません、
昨日出来たのですが、実は この計算式をあるシートに作っておき、
マクロで 他のシートの列に計算式をコピー 一番下まで貼付け(毎日変動)
をしていまして、
A3セルに 計算式を入れると 実際に計算式をコピーするシートのA3セルを読んでしまい
データが全て該当なし になってしまいます。
他の方法はありませんでしょうか。
(MOMO) 2019/01/18(金) 10:07


その前に 教えていただいた計算式でやってみたところFALSEになりました。
AAA からBBB へ検索に移行するときは  +  ではないのでしょうか。

=IF(B2="AAA",IFERROR(INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0)),+IF(B2="BBB",IFERROR(INDEX('関西'!$D$3:$UC$374,MATCH($P2,'関西'!$D$3:$D$374,0),MATCH($Q2,'関西'!$D$3:$UC$3,0)),+IF(B2="8Z1",IFERROR(INDEX('北海道'!$D$3:$UC$374,MATCH($P2,'北海道'!$D$3:$D$374,0),MATCH($Q2,'北海道'!$D$3:$UC$3,0)),+IF(B2="3Z1",IFERROR(INDEX('九州'!$D$3:$UC$374,MATCH($P2,'九州'!$D$3:$D$374,0),MATCH($Q2,'九州'!$D$3:$UC$3,0)),"該当なし"),"")))))))
(MOMO) 2019/01/18(金) 12:05


間違えました  これで FALSEです。

=IF(B2="AAA",IFERROR(INDEX('関東'!$D$3:$UC$374,MATCH($P2,'関東'!$D$3:$D$374,0),MATCH($Q2,'関東'!$D$3:$UC$3,0)),+IF(B2="BBB",IFERROR(INDEX('関西'!$D$3:$UC$374,MATCH($P2,'関西'!$D$3:$D$374,0),MATCH($Q2,'関西'!$D$3:$UC$3,0)),+IF(B2="CCC",IFERROR(INDEX('北海道'!$D$3:$UC$374,MATCH($P2,'北海道'!$D$3:$D$374,0),MATCH($Q2,'北海道'!$D$3:$UC$3,0)),+IF(B2="DDD",IFERROR(INDEX('九州'!$D$3:$UC$374,MATCH($P2,'九州'!$D$3:$D$374,0),MATCH($Q2,'九州'!$D$3:$UC$3,0)),"該当なし"),"")))))))
(MOMO) 2019/01/18(金) 12:06


 ↓ の式で話を進めます。

 =IF(B2="","",IFERROR(INDEX(INDIRECT("'"&A3&"'!D3:UC374"),MATCH(P2,INDIRECT("'"&A3&"'!D3:D374"),0),MATCH(Q2,INDIRECT("'"&A3&"'!D3:UC3"),0)),"該当なし"))

 >A3セルに 計算式を入れると 実際に計算式をコピーするシートのA3セルを読んでしまい 

 状況が理解できてませんが、ではどのシートのA3セルを読めばいいんですか?

 例えば「Sheet1のA3セル」で固定なら

 INDIRECT("'"&Sheet1!$A$3&"'!D3:UC374")
              ~~~~~~~~~~~
 このようにシート名を付けておけばいいのでは?(3か所全部、A3は絶対参照にすること)

 こういうことじゃないんですかね?

 ところで、B2セルはどのシートのB2?

 以上です
(笑) 2019/01/18(金) 13:07

(笑)様

初歩的なことを思いつかず本当にお恥ずかしいです。
シート名でA3を絶対値で指定したら できました。
本当にありがとうございます。

B2セルは 元データを貼り付けるシートのB2です。
そのシートのセルに、計算式を作っているシートから、計算式をコピーして
貼り付けしています。
(MOMO) 2019/01/18(金) 13:45


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.