检查单元格是否包含列表中的字符串 [英] Check if cell contains strings in a list
问题描述
我有一个 Excel 工作表，其中包含许多要分类的不同条目(费用).为此，我创建了另一个包含不同条目类别(例如杂货、夜间外出等)的工作表，并将每个类别放在一列中.
I have an Excel sheet with a lot of different entries (expenses) that I want to classify. To do that, I created another sheet with different entry categories such as groceries, night out etc., and I put each in a column.
这是我的费用表:
| 描述 | 值 | 类别 |
|---|---|---|
| 卡片 - 3/10 购买 Foetex | 350 | ? |
| 卡 - Mikkeller 3/10 购买 | 350 | ? |
这将是类别表:
| 杂货 | 晚上出去 |
|---|---|
| Foetex | 米克勒 |
| 艾玛 | 致欧尔 |
我想要做的是在费用表的第 3 列的类别表(杂货和夜间外出)上定义类别.因此，类别表中的内容将是费用表的描述列上文本的子字符串.因此，例如，如果描述中有Foetex"在其中，那么类别应该是杂货.
What I want to do is to have the Category defined on the categories sheet (Groceries and Night Out) on the 3rd column of the Expenses sheet. So what is in the categories sheet would be a substring of the text on the description column of the expenses sheet. So if, for example, the description has "Foetex" in it, then the Category should be Groceries.
我尝试过 Match、countif 等，但无济于事.
I have tried Match, countif etc., but to no avail.
有人知道解决办法吗?
推荐答案
对于这个答案，我做了以下假设:
For this answer I have made the following assumptions:
- 工作表被命名为费用"；和类别"，并且在同一个工作簿中
- 上表均从
Cell A1开始
表格费用"中的公式Cell C2(您的类别"列)应该是:
The formula in Sheet "Expenses" Cell C2 (your 'Category' column) should be:
=CONCATENATE(IF(COUNTIF(A2, "*" & Categories!A$2:B$3 & "*")>0, Categories!A$1:B$1, ";")) 然后按 ctrl+enter 将其作为数组公式应用.
=CONCATENATE(IF(COUNTIF(A2, "*" & Categories!A$2:B$3 & "*")>0, Categories!A$1:B$1, "")) then press ctrl+enter to apply it as an array formula.
替换Categories!A$1:B$3 如果类别范围更长或更宽，请确保第二个范围Categories!A$1:B$1 保持不变到顶行.
Replace Categories!A$1:B$3 With your categories range if it is longer or wider, make sure that second range Categories!A$1:B$1 stays stuck to the top row though.
并且记住使用绝对引用来保存当您向下复制公式时的范围变化类别列的其余部分.
And Remember to use absolute references to save the range changing when you copy the formula down the rest of the category column.
这篇关于检查单元格是否包含列表中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
