[[20070427203222]] 『不思議なVLOOKUP』(代奈) >>BOT

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

 

『不思議な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関数が1行ごとに
      TRUE  FALSE
  1行目 B1=A A1=あ
  2行目 B2=B A2=い
  3行目 B3=C A3=う
  4行目 B4=D A4=え
 と言う配列を返してくれているみたいですね。

 例えば、FALSE,FALSE にすると
      FALSE  FALSE
  1行目 A1=あ A1=あ
  2行目 A2=い A2=い
  3行目 A3=う A3=う
  4行目 A4=え A4=え
 ってな配列に。

 (HANA)

 これを見たのが、今回が3度目です。かなり有名なのかもしれません。
 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は
実際の表の2列目をさすのではなく
=VLOOKUP($D1,IF({TRUE,FALSE},$C$1:$C$10,$B$1:$B$10),2,FALSE)
        ~~~~~~~~~~~~
IF関数のTRUE,FALSEという配列のの2番目
つまり {"あ","1月":"い","2月":・・・・}という配列の
2番目という意味になると思いますので、
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)

コメント返信:

[ 一覧(最新更新順) ]


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