☰

MS excel - 根據關鍵字分配“類別”

tag-icon • tag-icon microsoft-excel
MS excel - 根據關鍵字分配“類別”

我有一個包含費用的 Excel 文件（花費的金額在一欄中），在下一欄中我有簡短的描述，主要由多個單字組成。我想“簡化”描述，並為每個描述分配一個或兩個單詞，該單詞位於其旁邊的另一列中。問題是描述不“統一”，例如我可以有“商務午餐”、“在XXX餐廳的商務晚餐”、“與記者一起喝咖啡”等字符串，我想將這些描述指定為“食物” “ 標籤。還有遵循類似模式的不同類別。

我的想法是創建另一張桌子（在不同的紙上） - 在一列中，我有“咖啡”、“午餐”、“晚餐”等關鍵字，在它們旁邊的列中，我標記了我想要分配的標籤，即「食物」 」。我使用 vlookup 函數進行近似匹配，但它返回了錯誤的結果。由於某種原因，列表中單字的順序似乎會影響結果，即使存在部分匹配（字串中的一個單字完全匹配），vlookup 也會忽略它並返回其他內容。例如，我有“xxx 酒店停車”，表中有“停車”-“旅行費用”對，vlookup 返回“食物”標籤。

你能幫我解決這個問題嗎？ （您建議有不同的方法嗎？）

答案1

您需要FIND()and/orSEARCH()函數。用法：

FIND(find_text, within_text)

                                                                傳回第二個文字字串中                                                                 第一個文字字串的起始位置（從位置 1 開始）

因此FIND("lunch", "lunch with customer")傳回 1，並FIND("lunch", "business lunch")傳回 10 #VALUE!。  除了區分大小寫和不區分大小寫之外，SEARCH()類似。所以FIND()FIND()SEARCH()

FIND("lunch", "Lunch with customer")返回#VALUE!
但
SEARCH("lunch", "Lunch with customer")返回 1

我假設您想要使用SEARCH()不區分大小寫的。

您需要設定一個像這樣的陣列：

                                                        關鍵字列表

最好在單獨的工作表中執行此操作；我們就這麼稱呼它吧Key-Sheet。然後，在資料表上：如果您的自由格式描述位於列中A （從 cell 開始A1），請在 cell 中輸入以下內容B1：

=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))

然後按Ctrl+ Shift+ Enter, 使其成為「數組公式」。 （它將在公式欄中顯示在大括號中。）說明：

  • SEARCH('Key-Sheet'!$A$1:$A$7,$A1)– 對於關鍵表列中的每個關鍵字（「咖啡」、「午餐」、「晚餐」等），在資料表A目前行、列的描述中搜尋它A大批包含{ #VALUE!; 10; #VALUE!; ... } （七個元素（在本範例中），每個關鍵字一個；第二個元素顯示「lunch」的結果，位於 中'Key-Sheet'!A2）。
  • IFERROR(…,LEN($A1)+1)– 將#VALUE!值替換為15，其中LEN("business lunch")+1不可能是 的有效回傳值SEARCH()（事實上，它高於 的任何可能的有效回傳值SEARCH()），但它是一個有效的數字。所以現在我們的數組是 { 15; 10; 15; …}。
  • MIN(…)– 從陣列中提取最小值：在本例中，10.一般來說，這將是（第一次）成功返回SEARCH()。
  • =MATCH(…, …)– 請注意，第二個參數MATCH()與上面的第一個參數相同。所以我們10在數組中尋找{ #VALUE!; 10; #VALUE!; …}。這將返回 的位置10，即 2，對應於資料表（「商務午餐」）上包含「午餐」的事實A1，該位置位於 Key-Sheet 的第 2 行。

若要取得費用類別，只需在BKey-Sheet 的欄位中建立索引即可。將單元格設定C1為=OFFSET('Key-Sheet'!$B$1,B1-1,0). （這不必是數組公式。）

                                費用數據

請注意（如上所述），如果費用說明包含多個關鍵字，則只會尋找第一個關鍵字。

如果你不想理會中間值，你可以直接計算

=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)

這做需要是一個數組公式。

PSFIND()和SEARCH()函數有一個可選的第三個參數：

SEARCH(find_text, within_text, [start_num])

所以

SEARCH("cigar", "Sometimes a cigar is just a cigar.")返回 13
但
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)返回 29

我看不出你有任何理由使用它。

答案2

正如泰森所說，“接近/大約”。匹配不是為了言語。引用幫助文件：

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

這表示如果您在“1,2,5,8,12”中查找值“7”，則傳回的值將是“5”，這是最接近 7 且不大於 7 的值。

如果沒有對單字和語法分析進行某種廣泛的程式設計和評估，就沒有簡單的方法可以做你想做的事情。

你應該做的是訓練自己在最初輸入數據時輸入某種“類別代碼”，然後使用備註欄來“附加詳細信息”..比如“01-食品和飲料”，“帶老闆出去去”他的生日晚餐」。

如果您已經擁有大量數據，這可能很難做到，您可以採取一些技巧來加快速度（儘管您仍然需要進行大量手動整理）。

首先新增一列，用於檢查單字“park”的描述，如果找不到則返回 0，如果找到則返回 1.. 類似於“=If(Search("park",A1)>1,1,0)” （然後自動將公式複製到資料的所有行中）。然後，您可以按該列對整個表格進行排序，這樣您的資料就會分成兩組：其中包含「park」的描述和不包含「park」的描述。新增另一列，例如其中包含“食物”的列。然後，在「food」和「park」之間，您可以（使用兩列）將其分為四組：沒有任何一個單字的組、有「food」的組、有「park」的組以及兩者都有的組。

重複執行此操作，您可以快速整理出明顯屬於某個類別或另一個類別的群組，用類別代碼標記它們，並從那時起在進行其他單字搜尋時忽略它們，直到所有內容都被分類。

相關內容