麻辣家族討論版版 - Powered by Discuz! Board

標題: VLOOKUP問題 [打印本頁]

作者: 074063 時間: 2015-7-13 21:32 標題: VLOOKUP問題

本帖最後由 074063 於 2015-7-13 21:35 編輯

[attach]21379[/attach]

請問各位大大如何參照資料A1:F7, 在B11輸入產品名,
A13:B17帶出對應資料, 若空白則跳過不帶出(如品名-C)

作者: dragonhsu 時間: 2015-7-14 09:45

回復 1# 074063
[attach]21381[/attach]
應該是這樣子

作者: 074063 時間: 2015-7-14 23:37

回復 2# dragonhsu

感謝dragonhsu大大解答
　但是我想要的結果如下
－－－－－－－－－－－－－－－－－－－－－
　產品 A005
　品名-A  yt
　品名-B  yy
　品名-C  ii
　品名-E vv
－－－－－－－－－－－－－－－－－－－－－
比如品名-D右欄對應無資料，則不顯示（或刪除）品名-D那一行

作者: dragonhsu 時間: 2015-7-15 13:13

回復 3# 074063
[attach]21388[/attach]
不顯示版本

作者: 074063 時間: 2015-7-15 16:13

[attach]21389[/attach][attach]21390[/attach]回復 4# dragonhsu

請問有辦法不留空白欄位依序排列嗎, 如圖

作者: JBY 時間: 2015-7-15 16:48

回復 5# 074063
[attach]21391[/attach]
1. B11, 驗證 \ 清單 \ 輸入 : =$A$2:$A$6

2. A13, 輸入陣列公式後, 向下複製至A17 :

{=TEXT(OFFSET(A$1,,SMALL(IF(T(OFFSET(A$1,MATCH($B$11,$A$2:$A$6,0),COLUMN(A:E)))<>"",COLUMN(A:E),1000),ROWS(A$1:A1))),";;;@")}

3. B13, 輸入陣列公式後, 向下複製至B17 :

{=TEXT(OFFSET(A$1,MATCH($B$11,$A$2:$A$6,0),SMALL(IF(T(OFFSET(A$1,MATCH($B$11,$A$2:$A$6,0),COLUMN(A:E)))<>"",COLUMN(A:E),1000),ROWS(A$1:A1))),";;;@")}

作者: 074063 時間: 2015-7-15 18:06

本帖最後由 074063 於 2015-7-15 18:15 編輯

回復 6# JBY

感謝JBY大大解答 :)
　請問是否可以不吝教導公式語法, 假如資料在另一工作表　list！A:W , 公式要如何修改

作者: JBY 時間: 2015-7-15 21:21

本帖最後由 JBY 於 2015-7-15 21:29 編輯

......請問是否可以不吝教導公式語法, 假如資料在另一工作表　list！A:W , 公式要如何修改.....

1. 資料在另一工作表　list！A:W , 公式

2. A13, 輸入陣列公式後, 向下複製至A34 :

{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A1)))&""}

3. B13, 輸入陣列公式後, 向下複製至B34 :

{=OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A1)))&""}

作者: 074063 時間: 2015-7-15 21:38

回復 8# JBY

COLUMN(A:Z),50 請問這個是什麼

　A：Z表示什麼？　50和1000差別在那？

作者: JBY 時間: 2015-7-15 22:22

回復 JBY

COLUMN(A:Z),50 請問這個是什麼

　A：Z表示什麼？　50和1000差別在那？
074063 發表於 2015-7-15 21:38

1. 你的資料在另一工作表　list！A1:W6 , 資料共計 22 欄。

2. 公式 , 用 COLUMN(A:Z) ={1,2,3,4,……..24,25,26}, 共計 26 欄, 超出資料欄的3 欄, 作為除錯用,

當然你可以用 COLUMN(A:AX) ={1,2,3,4,……..48,49,50} 共計 50 欄, 超出的28 欄, 作為除錯用……….

作者: JBY 時間: 2015-7-15 22:24

範例 :

1. 如果 A1:W6, 都有資料。

2. A13, 輸入陣列公式後, 向下複製至A38 :
{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A1)))&""}

3. A34 公式 :
{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A22)))&""}
=OFFSET(list!A$1,,SMALL({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,50,50,50,50},22))&""
=OFFSET(list!A$1,,22)&""
返回 W1 最後產品, 品名

4. A35 公式 :
{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A23)))&""}
=OFFSET(list!A$1,,SMALL({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,50,50,50,50},23))&""
=OFFSET(list!A$1,,50)&""
返回 A$1 後第50欄 (AY1) 資料, AY1 應該是空白的。
=0&""
=""
=空白

作者: 074063 時間: 2015-7-15 23:06

回復 11# JBY

感謝大大的解答！小弟還需消化一會:)

作者: hcm19522 時間: 2015-10-31 10:02

http://blog.xuite.net/hcm19522/twblog/353362071

作者: Andy2483 時間: 2024-3-1 09:37

本帖最後由 Andy2483 於 2024-3-1 10:17 編輯

謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,將二維結果陣列在字典提取/編輯/放回...達到想要的效果,學習方案如下,請各位前輩指教
執行前:
[attach]37524[/attach]

執行結果:
[attach]37525[/attach]

Option Explicit
Sub TEST()
Dim Brr, Crr(1 To 200, 1 To 2), A, Z, i&, j%, R&, c%, T$, xR As Range
'↑宣告變數:&是長整數,%是短整數,沒有指定是通用型變數
Set Z = CreateObject("Scripting.Dictionary")
'↑令Z變數是字典
Brr = Range([IV1].End(xlToLeft), [A65536].End(xlUp))
'↑令Brr變數是帶入區域儲存格值的二維陣列
For i = 2 To UBound(Brr)
'↑設順迴圈!令i從2 到Brr陣列縱向最大索引列號
T = Trim(Brr(i, 1)): A = Z(T): R = Z(T & "/r")
'↑令T變數是i迴圈列1欄Brr陣列值:令A變數是以變數查Z字典回傳的item值
'令R變數是 T變數連接"/r"字串組成的新字串為key,查Z字典回傳的item值
If Not IsArray(A) Then A = Crr: R = 1: A(R, 1) = Brr(1, 1): A(R, 2) = Brr(i, 1)
'↑如果A變數不是二維陣列!就令A變數變為同Crr的二維陣列:令R變數=1:令R變數列1欄A陣列值是 1列1欄Brr陣列值
'令R變數列2欄A陣列值是 i迴圈列1欄Brr陣列值
For j = 2 To UBound(Brr, 2)
'↑設順迴圈!令j從2 到Brr陣列橫向最大索引欄號
   If Brr(i, j) = "" Then GoTo j01
   '↑如果i迴圈列j迴圈欄Brr陣列值是空字元!就跳到標示j01位置繼續執行
   R = R + 1
   '↑令R變數累加1
   A(R, 1) = Brr(1, j)
   '↑令R變數列1欄A陣列值是 1列j迴圈欄Brr陣列值
   A(R, 2) = Brr(i, j)
   '↑令R變數列2欄A陣列值是 i迴圈列j迴圈欄Brr陣列值
j01: Next
Z(T) = A: Z(T & "/r") = R
'↑令key是 T變數,的item值以 A變數放回Z字典中
Next
Set xR = [A11]
'↑令xR變數是物件 A11 儲存格
For Each A In Z.KEYS
'↑設逐項迴圈!令A變數是 Z字典裡的key
If Not IsArray(Z(A)) Then GoTo A01
'↑如果以A變數查Z字典得item不是陣列!就跳到標示 A01位置繼續執行
xR.Resize(Z(A & "/r"), 2) = Z(A)
'↑令區域儲存格以二維陣列值寫入
Set xR = xR(1, 4)
'↑令xR變數變為向右移動自身格算起的第4欄儲存格
A01: Next
End Sub

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)