☰

檢查清單中儲存格以哪個關鍵字開頭並傳回符合的關鍵字

tag-icon • tag-icon microsoft-excel worksheet-function
檢查清單中儲存格以哪個關鍵字開頭並傳回符合的關鍵字

我正在嘗試獲取產品列表並從產品名稱的開頭提取其製造商。每個產品名稱都以其製造商開頭。由於某些商品的名稱正文中有其他製造商，因此情況變得更加複雜；我需要看看該項目以什麼開頭。我正在與 50,000 多個商品和 3,000 多個製造商打交道。到目前為止我的公式是：

=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)

這有時有效，但有時無效。例如，在下面的工作表中，第 2 行和第 3 行是正確的，但第 4 行不正確。A4“Mike's Fun Toys”（在儲存格中）的儲存格中的結果 B4應該是“Mike's”，但它顯示為“Fun”。

包含按字母順序倒序列出的製造商的工作表

（以下是您可以複製和貼上的文字形式的資料：

+---+---------+-----------------+---------------+
|   |    A    |        B        |       C       |
+---+---------+-----------------+---------------+
| 1 | Formula | Items           | Manufacturers |
+---+---------+-----------------+---------------+
| 2 | Brown   | Brown Cat Toys  | Mike's        |
| 3 | Cat     | Cat Fun Toys    | Fun           |
| 4 | Fun     | Mike's Fun Toys | Cat           |
| 5 |         |                 | Brown         |
+---+---------+-----------------+---------------+

）

但是當我更改列（製造商）的順序時 C：

按（正常）字母順序列出製造商的工作表

第 4 行變得正確（「Mike 的」），但第 2 行出錯。

A 列從頭到尾都有公式。預期成績：

A2 - Brown
A3 - Cat
A4 - Mike's

無論 Column 的順序如何，如何使公式起作用 C？

答案1

為了不理解 RyanMark 公式的讀者的利益，它是

  • 尋找每個製造商名稱在給定產品名稱中的位置。這導致
    • 1以產品名稱開頭的製造商名稱的值（因為它出現在1st 字元），
    • 產品名稱中出現的其他製造商名稱的編號較高（因為它們出現較晚，位置編號較高），以及
    • #VALUE!產品名稱中未出現的製造商名稱的錯誤代碼。
  • 將上述每一項取反（用 1 除以它），得到
    • 1以產品名稱開頭的製造商名稱（這是我們要找的名稱），
    • 產品名稱中出現的其他製造商名稱的正數較小（因為 1 除以大於 1 的數字所得的比率小於 1），且
    • #VALUE!產品名稱中未出現的製造商名稱的錯誤代碼。
  • 使用LOOKUP來尋找1上面的內容。

例如，對於 cell A4（對應於 cell 中的“Mike's Fun Toys” B4），在第一張圖像中，我們按順序得到：

  • 1，因為“Mike's”( C2) 開始“Mike's Fun Toys”，
  • 8，因為「Fun」( C3) 出現在「Mike's Fun Toys」的第 8 個字元處，並且
  • #VALUE!和#VALUE!，因為「Cat」( C4) 和「Brown」( C5) 沒有出現在「Mike's Fun Toys」。

將其反轉會得到1、  0.125( 1/8) #VALUE!和#VALUE!。然後它1在該數組中查找。這個「應該」有效，因為1這是第一個結果，而「Mike's」是 Column 中的第一個名字 C。

該問題可以在幫助頁面中看到LOOKUP：

為了抬頭為了使函數正常運作，正在尋找的資料必須按升序排序。

而且顯然1後面的0.125不是按升序排序的。

正如LOOKUP建議的那樣，我們可以使用 來解決這個問題MATCH。您想要的公式使用與您的公式相同的基本方法（除了沒有反轉，這是不必要的），是

=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))

第三個參數MATCH稱為“match_type”。我已將其設為0此處，這意味著MATCH將查找數組中恰好為 的第一個元素1，並且不會假設數組已排序。

這是一個陣列公式，所以輸入時必須按++ Ctrl。ShiftEnter

相關內容