[[20070427203222]]　『不思議なVLOOKUP』（代奈）　ページの最後に飛ぶ

『不思議なVLOOKUP』（代奈）

 職場の同僚から「この式の意味を教えて」と言われ見てみたんですが。
 理解不能というより「何これ？」状態です。。。(ーー;)

 通常VLOOKUP関数は、検査値より右に範囲があるものですが、
 左を検索するらしいのです。

 例えば以下のような表があるとします。

   A列   B列      C列　　D列　
 1 あ	A	A	
 2 い	B		
 3 う	C			
 4 え	D			
 5 お	E			
 6 か	F			
 7 き	G			
 8 く	H			
 9 け	I			
10 こ	J

 そして、D1セルに以下の式をいれると D1には「あ」が返るんです。

 =VLOOKUP($C1,IF({TRUE,FALSE},$B$1:$B$10,$A$1:$A$10),2,FALSE)

 これはどういう事でしょうか？
 VLOOKUPの範囲がIF式で、IF式の論理式が双頭の鷲？　はて？
 数式に精通された皆様、是非この謎を解き明かして下さい。
 お願いしますm(__)m
 (代奈)

 これはなかなかにワザですねぇ。
試しに数式バーでIF({TRUE,FALSE},$B$1:$B$10,$A$1:$A$10)の部分を範囲指定して
F9キーで部分計算させてみると、
=VLOOKUP($C1,{"A","あ";"B","い";"C","う";"D","え";"E","お";"F","か";"G","き";"H","く";"I","け";"J","こ"},2,FALSE)
　
VLOOKUPの引数「範囲」で任意の配列を作成するテクニックのようです。
$B$1:$B$10,$A$1:$A$10　が交互に配列になっています。
B列を検索してA列の値を返す、という場合・・・
通常なら、INDEX関数とMATCH関数で、ってなるところですが、
このワザなら、検索する列が必ずしも左端列でなくてもいいですね。
(みやほりん)(-_∂)b

 衝突したので、がさっと書き換えて・・・

 IF関数が１行ごとに
 　　　　 TRUE　 FALSE
 　１行目 B1=Ａ　A1=あ
 　２行目 B2=Ｂ　A2=い
 　３行目 B3=Ｃ　A3=う
 　４行目 B4=Ｄ　A4=え
 と言う配列を返してくれているみたいですね。

 例えば、FALSE,FALSE　にすると
 　　　　 FALSE　 FALSE
 　１行目 A1=あ　A1=あ
 　２行目 A2=い　A2=い
 　３行目 A3=う　A3=う
 　４行目 A4=え　A4=え
 ってな配列に。

 (HANA)

 これを見たのが、今回が３度目です。かなり有名なのかもしれません。
 IF({TRUE,FALSE},というところが、みそですね。
 =VLOOKUP(C1,CHOOSE({2,1},A1:A10,B1:B10),2,)などとも書けますね。　(JOKEUP)

 みやほりんさん、HANAさん、JOKEUPさん、有難うございます！
 なるほどなるほど、配列なんですね。。。とても分かりやすいです！
 JOKEUPさんの、IFをCHOOSEに置き換える方法だと違和感なく利用できそうです！
 ありがとうございました(*^◇^*)/
 (代奈)
 p.s. JOKEUPさん、お久しぶりです！
　　　お元気そうでとっても嬉しいです(^^)

 VLOOKUP関数でこのような使い方があったのかとビックリしております。
 理屈は全く理解出来ていないのですが、便利な使い方なので理屈抜きで
 式の構成だけを、丸暗記しようとしています。が、ひとつ疑問が出てき
 ましたので、宜しくお願い致します。

     	[A]	[B]	[C]	[D]	[E]
 [1]	1月	あ	A	A	あ
 [2]	2月	い	B		
 [3]	3月	う	C		
 [4]	4月	え	D		
 [5]	5月	お	E		
 [6]	6月	か	F		
 [7]	7月	き	G		
 [8]	8月	く	H		
 [9]	9月	け	I		
 [10]	10月	こ	J

 のような場合に、検索値が D1 にあった場合
 E1=VLOOKUP($D1,IF({TRUE,FALSE},$C$1:$C$10,$B$1:$B$10),2,FALSE)
 とすると "あ" がかえってきますよね。
  "1月" を返したい場合は、
 E1=VLOOKUP($D1,IF({TRUE,FALSE},$C$1:$C$10,$A$1:$A$10),2,FALSE)
 とするしか仕方がないのでしょうか？
 列番号を "3" とかにして出来ないものなのでしょうか？
    (KI)

 ホントに凄い数式ですよね～
 このVLOOKUPの列番号を表す2は
実際の表の２列目をさすのではなく
=VLOOKUP($D1,IF({TRUE,FALSE},$C$1:$C$10,$B$1:$B$10),2,FALSE)
　　　　　　　　~~~~~~~~~~~~
IF関数のTRUE,FALSEという配列のの２番目
つまり　{"あ","１月":"い","２月"：・・・・}という配列の
２番目という意味になると思いますので、
KI さんのおっしゃるような列番号では出来ないのかと思います。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　(oni)yonasan hinto please!

 JOKEUPさんご紹介のCHOOSE関数を使うものでは駄目なのですかね？
=VLOOKUP($D1,CHOOSE({3,2,1},$A$1:$A$10,$B$1:$B$10,$C$1:$C$10),3,FALSE)

 (HANA)

 oni様、早速の回答有難うございます。
 やはりそうなんですか、何となくそんな感じがしてたんですが・・・
 今一イメージが湧かなくて悩んでいました。
 配列って本当に難しいですね。もっと勉強しないと・・・。

 HANA様、回答有難うございます。
 CHOOSE関数の方も勉強したいと思います。
 また、素人な質問をさせて頂くことがあるかと思いますが
 その時には宜しくお願いしますね。
 有難うございました。
   (KI)

コメント返信：

[ 一覧(最新更新順) ]

[[20070427203222]] 『不思議なVLOOKUP』（代奈） ページの最後に飛ぶ

[[20070427203222]]　『不思議なVLOOKUP』（代奈）　ページの最後に飛ぶ