[[20070912153852]]　『検索値の下の段はVLOOKUPでは抽出できませんか？』（komu）　ページの最後に飛ぶ

『検索値の下の段はVLOOKUPでは抽出できませんか？』（komu）

      [A]       [B]         [C] 
 --------------------------------
 [1] 会社名    部･店名     金 額
 --------------------------------
 [2] AAA商会   原金額      10,000
 [3]           埼  玉       2,000
 [4]           鹿児島       1,000
 [5]           東京保有額   7,000
 [6]
 [7] BBB商事   原金額      20,000
 [8]           大  阪       5,000
 [9]           東京保有額  15,000
 [10]   :         :           :
 [11]   :         :           :

 というSHEETから別のSHEETに原金額(C2)を移すのは
 =VLOOKUP(B1,'SHEET1'!A2:C5,3,0)で出来るのですが、
 検索値である「AAA商会」はA2のセルにしか記載していない場合、
 その下の埼玉や2,000、更にその下の鹿児島、1,000という数字を
 別SHEETに抽出する方法はありませんか？
 VLOOKUPだと検索値の横しか抜き出せないので悩んでいます。

 つまり、SHEET2のB1に入力規制でリストから選択にして「AAA商会」
 を選んだ際に自動的にSHEET1から数字･支店名を移し､下記のような
 表をつくりたいのですが、何か良い方法はありませんか？

      [A]        [B]       [C]     [D]
 -------------------------------------
 [1] 会社名     AAA商会   原金額  10,000
 -------------------------------------
 [2] 
 [3] 部･店名     金 額
 ----------------------        
 [4]  埼  玉     2,000
 [5]  鹿児島     1,000 
 [6]  東京保有額 7,000

 宜しくお願いします。

 VLOOKUP関数ではありませんが、、、
 こんなのは如何でしょう？
 ※作業列を使用しています。
　
Sheet1	[A]	[B]　　　　　　　[C]	[D]
[1]	会社名	部･店名　　	金額	
[2]	AAA商会	原金額　　	10,000	
[3]		埼玉　　　	2,000	AAA商会
[4]		鹿児島　　	1,000	AAA商会
[5]		東京保有額	7,000	AAA商会
[6]				
[7]	BBB商事	原金額　　	20,000	
[8]		大阪　　　	5,000	BBB商事
[9]		東京保有額	15,000	BBB商事
　
 D2 =IF(COUNTA(A2:B2)=1,INDEX($A$1:A2,MATCH(9^99,CODE($A$1:A2))),"")
　　↑配列数式
　　　Ctrl + Shift + Enter で確定
　　　｛　数式　｝となればOK。
　　以下必要範囲までコピー
　
Sheet2	[A]　　　　	[B]	[C]	[D]
[1]	会社名　　	AAA商会	原金額	10,000
[2]				
[3]	部･店名　　	金額		
[4]	埼玉　　　	2,000		
[5]	鹿児島　　　	1,000		
[6]	東京保有額	7,000
　
 A4 =IF(COUNTIF(Sheet1!$D$1:$D$20,$B$1)<ROW(A1),"",
    INDEX(Sheet1!B$1:B$20,SMALL(IF(Sheet1!$D$1:$D$20=$B$1,ROW($A$1:$A$20),""),ROW(A1))))
　　↑こちらも配列数式です。
　　必要範囲までコピー
　
 ※配列数式は、データ量によっては重たくなるかもしれません。。。
 　叩き台程度って事でｗ
 （キリキ）（〃⌒o⌒)b

 キリキさん、返信ありがとうございます。
 が・・・、さっぱり意味が分かりません。
 簡単に説明いただけると非常に助かります。

 あと、配列数式は少し使ったことがあるのですが、
 いまひとつ理解できず、配列数式を使わない方法も、
 もしあれば教えてください。

 意味とは？
 配列数式のことでしょうか？
 式の内容でしょうか？
　
 配列数式はこちらを参考にしてみてください。
【エクセル(Excel)「配列数式」講座】
http://pc21.nikkeibp.co.jp/special/hr/
　
 式の内容は、、、
 何処がわかりません？
　
 １．の式
　=IF(COUNTA(A2:B2)=1,INDEX($A$1:A2,MATCH(9^99,CODE($A$1:A2))),"")
　~~1 ~~~~~~~~~~~~~~2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~5
                                    ~~~~~~~~~~~~~~~~~~~~~~~~4
                                               ~~~~~~~~~~~~3
 1.IF関数です。（省略しますｗ
 2.COUNTA関数です。
　A列が空欄で、B列のみ文字が入っているものを使用したいため、上記の数式をIF関数の条件にしています。
 3.上記条件で「真」の場合はこちらになります。
 　CODE関数で、入力されているものの「先頭文字」の番号を返します。
 　入力されていない場合は「#VALUE!」が返ります。
 4.その番号が何処まで入力されているかを調べるために、MATCH関数を使用し、
 　範囲内での相対的な位置を表す数値を返します。
 5.その位置を、INDEX関数の範囲の中から抽出しています。
　
 そして「偽」の場合は、勿論「空欄」が返ります。
　
 ２．の式
 =IF(COUNTIF(Sheet1!$D$1:$D$20,$B$1)<ROW(A1),"",
  INDEX(Sheet1!B$1:B$20,SMALL(IF(Sheet1!$D$1:$D$20=$B$1,ROW($A$1:$A$20),""),ROW(A1))))
　
 こちらは長いので、考え方のみでｗ
 COUNTIF関数で、指定した B1 のものがいくつあるかを数えています。
 そして、その数が ROW関数（入力してある「行の位置」）より大きかったら「偽」のため「空欄」を返します。
 「真」だったら、Sheet1!$D$1:$D$20 の中に B1 と同じ物を探し、あればその入力されている ROW関数（行の位置）を、無ければ ""（空欄）を返します。
 その出てきた回答に対し、SMALL関数で小さい順に抽出し、INDEX関数でそのセルに入力されたものを返しています。
　
 こんな感じなんですが。。。
 説明へたで、ごめんなさいね・・・
　
 >配列数式を使わない方法も、もしあれば教えてください。
 できるかもしれませんが、、、
 σ(^o^;)には、考える時間も、閃きもありませんでした。。。
　
 時間が無いので、考えていませんが、、、
[[20060512220025]]『VLOOKUP関数の結果を別の列に縦書きに詰めて表示したい。』（sarai）
 のLOOKUPさんの数式がヒントになるかもしれません。
　
 （キリキ）（〃⌒o⌒)b

 キリキさん、
 スペッシャルサンキュウーでございます。
 丁寧な説明でようやっと意味が分かりました。

 配列数式なしの方法はまだ見れてませんが、一先ず御礼まで。
 <m(__)m>

 上記過去ログを参考に配列ではないものを考えてみましたｂ
　
 表は、最初の表をそのまま使用するとして、、、
 Sheet1
 D2 =IF(AND(COUNTA(A2:B2)=1,A2=""),IF(INDEX($A$1:A2,MAX(INDEX((($A$1:A2>0)*ROW($A$1:A2)),)))=Sheet2!$B$1,"○",""),"")
　以下コピー
　
 Sheet2
 A4 =IF(COUNTIF(Sheet1!$D$1:$D$20,"○")<ROW(A1),"",VLOOKUP("○",IF({1,0},Sheet1!$D$1:$D$20,Sheet1!B$1:B$20),2,))
　B4へコピー
 A5 =IF(COUNTIF(Sheet1!$D$1:$D$20,"○")<ROW(A2),"",
    VLOOKUP("○",IF({1,0},INDEX(Sheet1!$D$1:$D$20,MATCH($A4,Sheet1!$B$1:$B$20,)+1,):Sheet1!$D$20,INDEX(Sheet1!B$1:B$20,MATCH($A4,Sheet1!$B$1:$B$20,)+1,):Sheet1!B$20),2,))
　A:B列の必要範囲まで下にコピー
　
 INDEX関数も、データが多いと重いかもしれませんがｗｗｗ
 ※こちらに関しては、説明はしませんので頑張ってヘルプとにらめっこして下さい＾＾
 　わからなかったところだけお聞きしてくださいな～♪
 （キリキ）(〃⌒o⌒)b

 D1=IF(B1="","",INDEX(Sheet2!C2:C20,MATCH($B$1,Sheet2!$A$2:$A$20,0))&"")
A4=IF(B1="","",INDEX(Sheet2!B2:B20,MATCH($B$1,Sheet2!$A$2:$A$20,0)+1)&"")
A5=IF(A4="","",INDEX(Sheet1!B$2:B$20,MATCH($B$1,Sheet2!$A$2:$A$20,0)+ROW(A2)))&""
下へフィールコピー
A4：A2０選択で右へフィールコピー

 普通にenterで　（JET)
MATCH($B$1,Sheet2!$A$2:$A$20,0)+ROW(A2)
                                ^^^^^^^検索行（AAA商会で検索）その下の行選択

 ROW(A2)＝２　ROW(A３)＝３　ROW(A４)＝４　と下の行へ変化

 [5]           東京保有額   7,000
 [6]　　　　　　　　　　　　　　　　　　この行空白行でやってます。
 [7] BBB商事   原金額      20,000　　　（それぞれの会社切れ目に空白行）

 なるほど。
 σ(^o^;)は、難しく考えすぎていたようですね＾＾；
　
 （キリキ）（〃⌒o⌒)b

コメント返信：

[ 一覧(最新更新順) ]

[[20070912153852]] 『検索値の下の段はVLOOKUPでは抽出できませんか？』（komu） ページの最後に飛ぶ

[[20070912153852]]　『検索値の下の段はVLOOKUPでは抽出できませんか？』（komu）　ページの最後に飛ぶ