返回列表 上一主題 發帖

[發問] 直式改橫式搜尋的公式

[發問] 直式改橫式搜尋的公式

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

測試檔 :   直式改橫式搜尋的公式.rar (2.75 KB)

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

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

執行結果:



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
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

回復 18# 准提部林


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

執行結果:



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
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

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

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

TOP

回復 17# papaya

用VBA自訂函數, 與公式用法相同:
符合條件的值齊聚1個儲存格V1.rar (7.29 KB)

TOP

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

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

原有的表格是模擬題,所以將查詢表移到A8:B8
如圖示 :

再次感謝您^^

TOP

回復 15# papaya


我還用舊版excel, 無法串接太多文字,
建議用vba

TOP

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

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

1_每日出貨箱數不一定,但目前的出貨數不會超過100箱,也就是箱號最多為1~99號。
2_箱號都為純數值編號。
3_同數量的重複個數最多10個。
以上 敬請賜教!
謝謝您^^

TOP

回復 13# papaya


箱號確認只有1~9箱??? 且為純數字???

TOP

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

請問您︰Sheet2!L1的函數公式要如何編撰?
謝謝您!

TOP

        靜思自在 : 做該做的事是智慧,做不該做的事是愚癡。
返回列表 上一主題