英文:
Excel Partial Match on Another Column and Categorize
问题
如何检查一个列中的单词是否包含另一列中的关键词，然后在Excel中进行分类？
我正在尝试对我的信用卡账单进行分类。
- 第一个Excel表格包含所有商店、供应商的交易清单，以及额外的关键词。
- 第二个Excel表格包含关键词和它们的分类。
尝试使用Vlookup、部分匹配，不太确定。每个月信用卡账单中可能有200笔交易，以及50个类别的查找。
资源如下。
Excel表格1：
| 信用卡描述 | 金额 | 类别（尝试填写） |
|---|---|---|
| 商店亚马逊 | 25 | 设备 |
| 供应商麦当劳德克萨斯 | 4 | 食品 |
| 枫叶医生办公室 | 47 | 药品 |
| 翡翠面包店 | 9 | 食品 |
Excel表格2：
| 供应商 | 类别 |
|---|---|
| 亚马逊 | 设备 |
| 麦当劳 | 食品 |
| 医生 | 医疗 |
| 面包店 | 食品 |
资源：
https://www.automateexcel.com/formulas/vlookup-contains-partial-match/
英文:
How do I check if one word in a column contains keyword in another column, and then categorize using Excel?
I am trying to categorize my credit card statement.
- First Excel sheet has transaction list of all the stores, vendors, with extra keywords.
- Second Excel Sheet has keywords and their categories.
Trying to use Vlookup, partial matches, not sure. There could be 200 transactions in a credit card statement each month, and 50 categories lookup.
Resource below.
Excel Sheet 1:
| Credit Card Description | Amount | Category (trying to fill in) |
|---|---|---|
| Store Amazon | 25 | Equipment |
| Vendor McDonalds TX | 4 | Food |
| Maple Doctor Office | 47 | Medicine |
| Emerald Bakery | 9 | Food |
Excel Sheet 2:
| Vendor | Category |
|---|---|
| Amazon | Equipment |
| McDonalds | Food |
| Doctor | Medical |
| Bakery | Food |
Resources:
https://www.automateexcel.com/formulas/vlookup-contains-partial-match/
答案1
得分: 2
Try FILTER() function with COUNTIFS().
=@FILTER($H$2:$H$5,COUNTIFS(A2,"*"& $G$2:$G$5 & "*"))
To fill down result automatically can use MAP() or BYROW(). Try-
=MAP(A2:A5,LAMBDA(x,FILTER(H2:H5,COUNTIFS(x,"*"& G2:G5 & "*")))
For Excel 2016:
=LOOKUP(,-FIND(" "&Sheet2!$A$2:$A$10&" "," "&A2&" "),Sheet2!$B$2:$B$10)
=IFERROR(INDEX($H$2:$H$5,AGGREGATE(15,6,ROW($1:$5)/(COUNTIFS(A2,"*"& $G$2:$G$5 & "*")),ROW($1:$1))),"")
英文:
Try FILTER() function with COUNTIFS().
=@FILTER($H$2:$H$5,COUNTIFS(A2,"*"& $G$2:$G$5 & "*"))
To fill down result automatically can use MAP() or BYROW(). Try-
=MAP(A2:A5,LAMBDA(x,FILTER(H2:H5,COUNTIFS(x,"*"& G2:G5 & "*"))))
For Excel 2016:
=LOOKUP(,-FIND(" "&Sheet2!$A$2:$A$10&" "," "&A2&" "),Sheet2!$B$2:$B$10)
=IFERROR(INDEX($H$2:$H$5,AGGREGATE(15,6,ROW($1:$5)/(COUNTIFS(A2,"*"& $G$2:$G$5 & "*")),ROW($1:$1))),"")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论