Board logo

標題: [發問] 直式改橫式搜尋的公式 [打印本頁]

作者: papaya    時間: 2019-9-10 02:58     標題: 直式改橫式搜尋的公式

[attach]31240[/attach]  [attach]31241[/attach]
函數INDEX+SMALL+IF
想將直式表格搜尋,改為橫式表格搜尋
請問︰
Sheet2! L2的公式要怎麼編寫?
請各位幫忙! 謝謝!

測試檔 :  [attach]31242[/attach]
作者: KCC    時間: 2019-9-10 17:11

函數INDEX+SMALL+IF
想將直式表格搜尋,改為橫式表格搜尋
請問︰
Sheet2! L2的公式要怎麼編寫?
請各 ...
papaya 發表於 2019-9-10 02:58



    好久沒寫,湊了一下

    L2={INDEX($A$2:$J$2,,SMALL(IF($B$4:$J$4=K$2,TRANSPOSE(ROW($2:$10)),1000),ROW(A1)))&""}
   陣列公式
作者: papaya    時間: 2019-9-11 00:48

回復 2# KCC
[attach]31247[/attach]
謝謝幫忙!答案正確^^
再請教
L2={INDEX($A$2:$J$2,,SMALL(IF($B$4:$J$4=K$2,TRANSPOSE(ROW($2:$10)),1000),ROW(A1)))&""}
為什麼是 ,,,而不是 ,
還有&""沒有作用,#REF!沒有辦法顯示空白,公式中同樣的&""有什麼差別呢?
能否煩請解惑。謝謝您^^
作者: KCC    時間: 2019-9-11 11:00

回復  KCC

謝謝幫忙!答案正確^^
再請教
L2={INDEX($A$2J$2,,SMALL(IF($B$4J$4=K$2,TRANSPOSE(ROW ...
papaya 發表於 2019-9-11 00:48


陣列公式
={INDEX($2:$2,,SMALL(IF($B$4:$J$4=K$2,TRANSPOSE(ROW($2:$10)),256),ROW(A1)))&""}

&"" 是用來指到空格時,excel會顯示 0 ,用&""讓他變成空字串
xls為2003(含)已下版本,欄最多就256,所以把1000改成 256,另index()的第一個參數範圍擴大為整個 2:2

這種寫法很怪,建議維持原來直式才是正規,若原始資料都是橫式,不妨透過轉置改成直式,才是正途
作者: papaya    時間: 2019-9-11 12:44

本帖最後由 papaya 於 2019-9-11 12:49 編輯

回復 4# KCC
將1000改成256,#REF!還是沒有變成空白;
還有~如果將公式中的&""移除,答案是一樣的,所以覺得&""沒有作用。
格式是公司長期沿用,我個人無法擅自更改^^"

這樣就可以了~非常感謝您的熱心指導。
作者: KCC    時間: 2019-9-11 16:49

回復  KCC
將1000改成256,#REF!還是沒有變成空白;
還有~如果將公式中的&""移除,答案是一樣的,所以覺 ...
papaya 發表於 2019-9-11 12:44


index()的第一個參數要改,我有說明,請詳閱
作者: 准提部林    時間: 2019-9-12 10:44

Sheet2
A1:=INDEX('Sheet1(直式)'!$A:$E,COLUMN(A$1),ROW(A1))  右拉至J2, 下拉5行

K2:=IF('Sheet1(直式)'!F2="","",'Sheet1(直式)'!F2)  右拉一格/下拉
作者: papaya    時間: 2019-9-12 15:50

本帖最後由 papaya 於 2019-9-12 16:02 編輯

回復 7# 准提部林
准提版主:
您的公式很精簡,易懂實用,感謝幫忙^^

可能是我沒有說清楚~
Sheet2是公司現在所使用的表格格式,
因為我在爬文時,找到的公式都適用於如Sheet1直式的格式表格,所以我只好以公司的橫式格式改為直式的格式表格來發問,希望能以直式格式的公式改為適用橫式格式單純的發問;
也就是說我需要的是Sheet2!L2的解題公式(因為公司現在並沒有存在Sheet1的表格)。
如果您不介意,煩請您指教!謝謝您!
作者: 准提部林    時間: 2019-9-12 16:57

L2/陣列公式:
=LOOKUP("z",IF({1,0},"",SMALL(IF($B$4:$J$4=K$2,$B$2:$J$2),ROW(A1))&""))
作者: papaya    時間: 2019-9-12 18:54

回復 9# 准提部林
准提版主:
感謝再次回覆,答案正確。

請教您一下:公式中的"z"英文字母,代表什麼意義?
好像改為"a',答案也正確,公式編寫的很獨特很奧妙^^
作者: 准提部林    時間: 2019-9-13 09:32

回復 10# papaya


"a" ~ "z"  比數字大即可
作者: papaya    時間: 2019-9-13 10:15

回復 11# 准提部林

感謝您的說明。
瞭解了!

中秋快樂^^
作者: papaya    時間: 2019-9-22 13:56

回復 9# 准提部林
符合條件的值齊聚1個儲存格
[attach]31285[/attach]
准提版主:
不好意思,我想將Sheet1!L1的公式,再進階為如Sheet2的L1︰將符合條件的值,齊聚放置在1個儲存格,
這樣就毋需再預留許多個儲存格。

請問您︰Sheet2!L1的函數公式要如何編撰?
謝謝您!
作者: 准提部林    時間: 2019-9-22 17:33

回復 13# papaya


箱號確認只有1~9箱??? 且為純數字???
作者: papaya    時間: 2019-9-22 20:51

本帖最後由 papaya 於 2019-9-22 21:00 編輯

回復 14# 准提部林
准提版主 :
謝謝回應。

1_每日出貨箱數不一定,但目前的出貨數不會超過100箱,也就是箱號最多為1~99號。
2_箱號都為純數值編號。
3_同數量的重複個數最多10個。
以上 敬請賜教!
謝謝您^^
作者: 准提部林    時間: 2019-9-23 09:55

回復 15# papaya


我還用舊版excel, 無法串接太多文字,
建議用vba
作者: papaya    時間: 2019-9-23 12:25

回復 16# 准提部林
准提版主 :
謝謝您的回應。

VBA非常棒,但是我不懂,怕不好(會)維護。
我有仔細查看公司以往的紀錄,重複數量的最高紀錄是6個,
如果不行,看貴EXCEL版能容許有幾個,就幾個。其它不足的,我再想辦法。

原有的表格是模擬題,所以將查詢表移到A8:B8
如圖示 :
[attach]31288[/attach]
再次感謝您^^
作者: 准提部林    時間: 2019-9-23 19:52

回復 17# papaya

用VBA自訂函數, 與公式用法相同:
[attach]31289[/attach]
作者: papaya    時間: 2019-9-23 23:38

本帖最後由 papaya 於 2019-9-23 23:50 編輯

回復 18# 准提部林
准提版主:
真棒的解答。
這樣我只要不動程式碼,只要改公式查詢的位址就可以了。
感謝您耐心的解題和指導。
作者: Andy2483    時間: 2023-5-31 08:58

回復 18# 准提部林


    謝謝論壇,謝謝前輩
後學藉此帖學習前輩的方案,方案學習心得註解如下,請前輩再指導

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


Function GetCTNo(xA As Range, xB As Range, xNo)
'↑自訂函數GetCTNo(),宣告變數:(xA,xB)是儲存格變數,xNo是通用型變數
'函數結果是字串

Dim i%, TT$
'↑宣告變數:i是短整數,TT是字串變數
For i = 1 To xA.Count
'↑設順迴圈!i從1 到xA變數數量(儲存格數量)
    If xA(i) = xNo Then TT = TT & "," & xB(i)
    '↑如果xA i迴圈儲存格的值與 xNo變數相同!
    '就令TT變數將xB i迴圈儲存格的值納入到後方,以逗號間隔

Next i
GetCTNo = Mid(TT, 2)
'↑令GetCTNo函數回傳 TT變數從第2字元開始的後方字串
End Function
作者: Andy2483    時間: 2023-5-31 10:17

本帖最後由 Andy2483 於 2023-5-31 10:28 編輯

謝謝論壇,謝謝各位前輩
後學藉此帖練習VBA陣列與字典,學習方案如下,請各位前輩指教

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


Option Explicit
Sub TEST()
Dim Brr, Crr, Y, j%, T$, T1$, R%
'↑宣告變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y變數是 字典
Brr = Range([B5], Cells(1, Columns.Count).End(1))
'↑令Brr變數是二維陣列,以1~5列資料帶入陣列中(不包含標題欄與空欄)
ReDim Crr(1 To UBound(Brr, 2), 1 To 2)
'↑令Crr變數是 二維空陣列,縱向同Brr橫向範圍,橫向1~2
For j = 1 To UBound(Brr, 2)
'↑設順迴圈
   T = Brr(4, j): T1 = Brr(2, j)
   '↑令T變數是 第4列陣列值,令T1變數是 第2列陣列值
   If Y(T) = "" Then R = R + 1: Y(T) = R: Crr(R, 1) = T: Crr(R, 2) = T1: GoTo j01
   '↑如果T變數是第1次納入Y字典,就令R變數累加1(紀錄列號),
   '令T變數在Y字典裡的item("")換成R變數,
   '令Crr陣列第1欄放 數量,令Crr陣列第2欄放 第1個箱號
   '跳到標示j01位置繼續執行

   Crr(Y(T), 2) = Crr(Y(T), 2) & "," & T1
   '↑程序會跑到這位置!都是第2次以上出現的key,
   '令Crr陣列第2欄繼續累積箱號,以逗號隔開

j01: Next
[I8:J8] = [{"數量","箱號"}]: [I9].Resize(R, 2) = Crr
'↑令儲存格第8列是標題列,令Crr陣列從[I9]開始寫入儲存格裡
Set Y = Nothing: Erase Brr, Crr
'↑令釋放變數
End Sub


'===================================================
'標題欄迴圈中處理的方案


Option Explicit
Sub TEST_1()
Dim Brr, Crr, Y, j%, T$, T1$, R%
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([A5], Cells(1, Columns.Count).End(1))
ReDim Crr(1 To UBound(Brr, 2), 1 To 2)
For j = 1 To UBound(Brr, 2)
   T = Brr(4, j): T1 = Brr(2, j)
   If Y(T) = "" Then R = R + 1: Y(T) = R: Crr(R, 1) = T: Crr(R, 2) = T1: GoTo j01
   Crr(Y(T), 2) = Crr(Y(T), 2) & "," & T1
j01: Next
[I8].Resize(R, 2) = Crr
Set Y = Nothing: Erase Brr, Crr
End Sub




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