[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数SheetからのVLOOKUPについて』(POPO)
5つのシートからVLOOKUPするためISERROR関数を使用しましたが 「数式が長すぎます」というメッセージが出て最大4つのシートまでしか VLOOKUP出来ませんでした。
検索 [A] [B] [1] A 11 [2] H 88 [3] M 333 [4] Q #N/A ⇒666としたい! B3にはIF(ISERROR(IF(ISERROR(IF(ISERROR(VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)),VLOOKUP($A3,Sheet2!$A:$B,2,FALSE) ,VLOOKUP($A3,Sheet1!$A:$B,2,FALSE))),VLOOKUP($A3,Sheet3!$A:$B,2,FALSE),IF(ISERROR(VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)) ,VLOOKUP($A3,Sheet2!$A:$B,2,FALSE),VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)))),VLOOKUP($A3,Sheet4!$A:$B,2,FALSE) ,IF(ISERROR(IF(ISERROR(VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)),VLOOKUP($A3,Sheet2!$A:$B,2,FALSE),VLOOKUP($A3,Sheet1!$A:$B,2,FALSE))) ,VLOOKUP($A3,Sheet3!$A:$B,2,FALSE),IF(ISERROR(VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)),VLOOKUP($A3,Sheet2!$A:$B,2,FALSE) ,VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)))) という数式が入っています。これの流れでIF(ISERRORを追加しSheet5をVLOOKUP しようとすると「数式が流すぎます」というエラーで先に進めなくなります。
どうやったら5つ目のシートの検索が可能かどなたか教えていただけませんか?
------------------------ Sheet1 [A] [B] [1] A 11 [2] B 22 [3] C 33
Sheet2 [A] [B] [1] D 44 [2] E 55 [3] F 66
Sheet3 [A] [B] [1] G 77 [2] H 88 [3] I 99
Sheet4 [A] [B] [1] K 111 [2] L 222 [3] M 333
Sheet5 [A] [B] [1] O 444 [2] P 555 [3] Q 666
作業列は使用できませんかね?
戻り値が数値のようですので、是非はともかくこのように書くことはできると思います。 (JOKEUP)
=LOOKUP(9^99,CHOOSE(CHOOSE({1,2,3,4,5},VLOOKUP(A1,Sheet5!A:B,2,),VLOOKUP(A1,Sheet4!A:B,2,), VLOOKUP(A1,Sheet3!A:B,2,),VLOOKUP(A1,Sheet2!A:B,2,),VLOOKUP(A1,Sheet1!A:B,2,)))
シート名が、例題通りであり、各シートに重複がないことが条件ですが、 こんなのはいかがでしょう? A1 =VLOOKUP(A1,INDIRECT("Sheet" &COUNTIF(Sheet1!$A$1:$B$3,A1)*1+COUNTIF(Sheet2!$A$1:$B$3,A1)*2 +COUNTIF(Sheet3!$A$1:$B$3,A1)*3+COUNTIF(Sheet4!$A$1:$B$3,A1)*4 +COUNTIF(Sheet5!$A$1:$B$3,A1)*5 &"!$A$1:$B$3"),2) 以下コピー (キリキ)(〃⌒o⌒)b
JOKEUPさん、CHOOSE関数がダブってます。 セル番地修正時のコピーミスですよb (キリキ)(〃⌒o⌒)b
キリキ氏、こまぷすむにだ。 (JOKEUP)
=LOOKUP(9^99,CHOOSE({1,2,3,4,5},VLOOKUP(A1,Sheet5!A:B,2,),VLOOKUP(A1,Sheet4!A:B,2,), VLOOKUP(A1,Sheet3!A:B,2,),VLOOKUP(A1,Sheet2!A:B,2,),VLOOKUP(A1,Sheet1!A:B,2,)))
JOKEUP氏、ちょんまね まるすみむにだ。 (キリキ)(〃⌒o⌒)b
JOKEUPさんキリキさん、ありがとうございます。うまく出来ました。 もう一つだけお願いがあるのですが、LOOKUP(9^99,CHOOSE({1,2,3,4,5}までの 数式の意味を教えていただけ無いでしょうか?CHOOSE関数の書式は理解出来るのですが 特に{1,2,3,4,5}の意味が分かりません。シートをもう一つ追加したい場合は {1,2,3,4,5,6,}となるのでしょうか? 宜しくおねがいいたします。 (POPO)
解決したようですが、もう一度自分の式を考えましょう
IF(ISERROR(VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)),IF(ISERROR(VLOOKUP($A3,Sheet2!$A:$B,2,FALSE)), IF(ISERROR(VLOOKUP($A3,Sheet4!$A:$B,2,FALSE)),IF(ISERROR(VLOOKUP($A3,Sheet5!$A:$B,2,FALSE), "該当なし",VLOOKUP($A3,Sheet5!$A:$B,2,FALSE)),VLOOKUP($A3,Sheet4!$A:$B,2,FALSE)), VLOOKUP($A3,Sheet3!$A:$B,2,FALSE)),VLOOKUP($A3,Sheet2!$A:$B,2,FALSE)),VLOOKUP($A3,Sheet1!$A:$B,2,FALSE))
重複がなければ IF(ISERROR(VLOOKUP($A3,Sheet1!$A:$B,2,FALSE)),"",(VLOOKUP($A3,Sheet1!$A:$B,2,FALSE))& IF(ISERROR(VLOOKUP($A3,Sheet2!$A:$B,2,FALSE)),"",(VLOOKUP($A3,Sheet2!$A:$B,2,FALSE))& IF(ISERROR(VLOOKUP($A3,Sheet3!$A:$B,2,FALSE)),"",(VLOOKUP($A3,Sheet3!$A:$B,2,FALSE))& IF(ISERROR(VLOOKUP($A3,Sheet4!$A:$B,2,FALSE)),"",(VLOOKUP($A3,Sheet4!$A:$B,2,FALSE))& IF(ISERROR(VLOOKUP($A3,Sheet5!$A:$B,2,FALSE)),"",(VLOOKUP($A3,Sheet5!$A:$B,2,FALSE))
または
SUMIF(Sheet1!$A$1:$A$3,A1,Sheet1!$B$1:$B$3)+SUMIF(Sheet2!$A$1:$A$3,A1,Sheet2!$B$1:$B$3)+ SUMIF(Sheet3!$A$1:$A$3,A1,Sheet3!$B$1:$B$3)+SUMIF(Sheet4!$A$1:$A$3,A1,Sheet4!$B$1:$B$3)+ SUMIF(Sheet5!$A$1:$A$3,A1,Sheet5!$B$1:$B$3)
SUMPRODUCT、INDIRECT関数を使用して簡単にできますがおすすめしません。
By しげちゃん
CHOOSE({1,2,3,4,5},Sheet5の値,Sheet4の値,Sheet3の値,Sheet2の値,Sheet1の値)は、 第一引数に配列を代入して {Sheet5の値,Sheet4の値,Sheet3の値,Sheet2の値,Sheet1の値}の配列を作成しています。
LOOKUP(9^99,{数値の配列}で(9^99は、9の99乗で配列のどれよりも大きい数値です。) 配列の一番奥のものを戻しています。配列に数値が含まれていない場合には、#N/Aを戻します。 ご提示の数式の趣旨から、Sheetの数の若い順>上のものを優先で、数値を戻しています。
もしも配列数式が、初めての場合には、わかりにくいかもしれません。 普通は、1シートにまとめてから処理するものでしょう。 >{1,2,3,4,5,6,}となるのでしょうか? そうですが、やめた方がよいように思います。 (JOKEUP)
(JOKEUP)さん、分かりやすい説明ありがとうございました。 しかし新たな疑問がわきました。LOOKUP(9^99,たとB列が数字の場合は良いのですが もしB列が文字列だった場合は9^99,がどうなるのでしょうか?何度もすいません。 ぜひご教授願えないでしょうか?(POPO)
数値とか、文字とか面倒なので、混在OKと云うことで、、、、(半平太)
B1セル =VLOOKUP(A1,CHOOSE(LOOKUP(7,{6,5,4,3,2,1}*SIGN(CHOOSE({6,5,4,3,2,1}, MATCH(A1,Sheet1!A:A,0),MATCH(A1,Sheet2!A:A,0),MATCH(A1,Sheet3!A:A,0),MATCH(A1,Sheet4!A:A,0),MATCH(A1,Sheet5!A:A,0),1))), Sheet1!A:B,Sheet2!A:B,Sheet3!A:B,Sheet4!A:B,Sheet5!A:B,IF({1,0},A1,"該当なし")),2,0)
VLOOKUP($A3,Sheet2!$A:$B,2,FALSE) ↑ sheetをわける規則性があれば簡単ですが どうでしょう (規則性) 検索値 頭文字 Aならsheet1とか (頭文字で判別)
>戻り値が数値のようですので、是非はともかくこのように書くことはできると思います。 私の回答は上のようにあらかじめおことわりしています。 (JOKEUP)
皆々様、大変参考になりました。ありがとうございました。(POPO)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.