Hi~
這個問題不好解決
所以公式比較多 @@
下面一個一個步驟說明
1.A4 欄位建立公式，用作排序，由小到大排序
=LEN(SUBSTITUTE(B4,"*",""))
這個是為了保證函數在搜尋的時候找到的事那個 "最符合" 的那筆資料
假設有個
AB52**
以及
AB5234
那麼用
AB5234
搜尋的時候，有可能會配對到
AB52**
這時候就是我們不希望的，所以要先排序去除米字號後的資料表
這樣才能比對到最符合的那筆資料
2.當然就是排序拉，由小到大
選擇下方的資料範圍後
注意是從 A4 儲存格開始選擇，這樣才會依照那個欄位排序
點選上方 "資料" >> "排序與篩選" >> "從最小到最大排序"
3.在 E4 儲存格輸入陣列公式
=LARGE(IF(ISERROR(SEARCH($B$4:$B$11,F4)),0,1)*ROW($B$4:$B$11),1)
注意這是陣列公式，輸入完後要按 Ctrl + Shift + Enter
有看到 Excel 幫你在公式兩側加入 { } 這樣的大括號才是陣列公式
4.在 G4 和 H4 輸入公式，找到欄位資料
=INDIRECT(ADDRESS($E4,3))
=INDIRECT(ADDRESS($E4,4))
以上報告完畢~
如果有疑問請再與本人連繫
感激~~~~~
=LEN(SUBSTITUTE(B4,"米","")) 發現您好像少打了一個星號
請問
另外我不能理解的是，你是怎麼利用Search與row互乘後，再丟進去Large裡面在陣列裡可以算排序值.
因為不管Search怎麼找都是找到#VALUE!，最後再由ISERROR判斷成True或False好在IF裡面發揮效用，最後在取出1與0，與ROW互乘。
ROW到底要怎判斷你是取哪一個值，然後符合之後讓它結果1米5或1米9等到順序？
ROW不是單純的從你給他的數字像B4:B11來判斷哪一行？正確不是只能取得 4？ B5:B11會變5。
感謝提醒，忘了在星號 * 前面加跳脫符號 \，所以 Markdown 把他弄掉了 XD
已經修正
因為這是陣列公式，用個例子來說明一下
假設要找 AB2125
那麼 SEARCH() 會找出 {1,0,0,0,0,0,0,0}
回傳的資訊會是一個陣列，而不是單純的數值
然後再去乘以 ROW()
因為 ROW 內也是那個範圍的列號，所以實際上列是
{4,5,6,7,8,9,10,11}
將這兩個互乘就變成陣列 {4,0,0,0,0,0,0,0}
因為 1 是代表有比對到，那 ROW 是範圍的列號陣列
所以會剔除 沒有比對到的列號 0
最後再用 LARGE 取出最大的那個數 (其實 MAX 函數也可以)
就是比對到的列號
簡單說
SEARCH() => {1,#ERR!,#ERR!,#ERR!,#ERR!,#ERR!,#ERR!,#ERR!}
IF(ISERROR()) => {1,0,0,0,0,0,0,0}
ROW() => {4,5,6,7,8,9,10,11}
互乘後 => {4,0,0,0,0,0,0,0}
LARGE(或 MAX) => 4 => 列號
對了，這邊的 IF(ISERROR()) 不能使用 IFERROR() 代替
因為如果比對到的 SEARCH() 不是 1
那麼乘上 ROW() 就會錯誤
所以要用 IF(ISERROR()) 來分別輸出 0 或 1
結合前幾位網友提供的功能, 大概整理如下(使用原發問者的表格)
A4 = search("~*",B4)
(將原本的材料庫清單的 * 取消)
E4 = len(A4)
(取材料庫辨識用的長度)
I4 = large(if(iserror(search(A:A,F4)),0,1)*E:E,1)
(輸入後改按 Ctrl-Shift-Enter 用陣列計算, 取出符合的產品長度最大者.)
G4 = vlookup(left($F4,$I4),$A:$D,3,false)
H4 = vlookup(left($F4,$I4),$A:$D,4,false)
(將 F4 欄只取符合的長度(I4), 再去比對 A 欄)
這樣原本的材料庫表格就不用排序, 假如有 AB12＊＊ 跟 AB123＊ , 會挑 AB123＊ 這列來 vlookup .
最後再把 A, E, I 依原產品零組件表數量, 複製到其它列.
G, H 則是依本次工作用量, 複製到其它列.
謝謝各位回答, 但只能選出一個最佳解答>< 其他大大抱歉了
(使用LibreOffice的寫法)
A4 = LEFT(B4,SEARCH("*",B4)-1)
(捨去 * 後面的部份)
E4 = LEN(A4)
(取原產品號的長度)
G4 = VLOOKUP(LEFT($F4,MIN($E:$E)),$A:$D,3,FALSE())
H4 = VLOOKUP(LEFT($F4,MIN($E:$E)),$A:$D,4,FALSE())
(用產品的前幾碼去比對通用型號前幾碼, 如果相符, 再撈取陣列的第 3 欄)
考慮:
大大您好, 可能我表示希望與G4來舉例不是很恰當, 因為右表的資料有經過排序, 所以剛好對應到了左表。
若是要以G9來做示範要如何解? 實際上的資料筆數很多, 謝謝回答
先在 A4 欄與 E4 欄使用函式, 可以產生無星號的字串與長度.
再依照您的 B 欄數量, 把 A4 複製到 A5:A11 , E4 複製到 E5:E11
再把 G4 使用函式, 可以正常找到的話, 再複製到 G4:H11 即可.
您好, 我做出來的結果如下圖。請問是哪裡出了錯呢?
A4儲存格輸入公式
=IF(ISERROR(FIND("",B4)),B4,LEFT(B4,FIND("",B4)-1))
G4儲存格輸入公式
=IF(ISNA(VLOOKUP(LEFT(F4,6),$A$4:$D$12,3,0)),IF(ISNA(VLOOKUP(LEFT(F4,5),$A$4:$D$12,3,0)),IF(ISNA(VLOOKUP(LEFT(F4,4),$A$4:$D$12,3,0)),"",VLOOKUP(LEFT(F4,4),$A$4:$D$12,3,0)),VLOOKUP(LEFT(F4,5),$A$4:$D$12,3,0)),VLOOKUP(LEFT(F4,6),$A$4:$D$12,3,0))
H4儲存格輸入公式
=IF(ISNA(VLOOKUP(LEFT(F4,6),$A$4:$D$12,4,0)),IF(ISNA(VLOOKUP(LEFT(F4,5),$A$4:$D$12,4,0)),IF(ISNA(VLOOKUP(LEFT(F4,4),$A$4:$D$12,4,0)),"",VLOOKUP(LEFT(F4,4),$A$4:$D$12,4,0)),VLOOKUP(LEFT(F4,5),$A$4:$D$12,4,0)),VLOOKUP(LEFT(F4,6),$A$4:$D$12,4,0))
iThome鐵人賽
看影片追技術