如何根据自定义规则对excel项目进行分组? [英] How to group excel items based on custom rules?
问题描述
我有一组数据(网站管理员工具搜索查询),其中包含以下标题:
查询|印象|点击|日期
示例Google电子表格 here 。
我想添加一个名为类别
,并根据将在列A上搜索字符串的自定义规则对所有查询进行分类。
例如:
如果A2包含字符串笔记本电脑,然后在其旁边的类别上写笔记本电脑
到目前为止尝试了一个公式来做到这一点,但我不知道这是最简单的方法。此外,如果有很多分类规则,公式会变得很长,难以管理。
= IF(ISNUMBER(SEARCH (A2)),笔记本电脑,
pre>
IF(ISNUMBER(SEARCH(notebook,A2)),laptop,
IF(ISNUMBER 电话,
IF(ISNUMBER(SEARCH(galaxy s,A2)),phone,
other)))
您可以建议一种更好的方式来做到这一点,我可以使用以下格式在一张表格中使用规则:
Query_contains | Category_is
其中
Query_contains
是从初始工作表中需要匹配的字符串,而类别
将是需要填入列D的值。解决方案好的,我更改了你的表格....
假设你的所有数据在单元格A1:C9中,那么您在单元格F1中有以下表格:G5
Search_For:类别:
笔记本电脑
iphone手机
银河手机
笔记本电脑
现在,在单元格D2中,输入以下公式:
= IFERROR(INDEX(G $ 2:G $ 5,MATCH (TRUE,ISNUMBER(搜索(F $ 2:F $ 5,A2)),0)),其他)
而输入它作为一个数组公式意义,一旦你输入它,点击 CTRL + SHIFT + ENTER <大骨节病>。
然后,您可以从单元格D2拖动公式,并将其提供给您所需的结果(当然,您可以增加F& G列中的列表如果需要的话)。
希望这样做可以!
I have a set of data (webmaster tools search queries) which is in excel with the following header:
Query | Impressions | Clicks | Date
Sample google spreadsheet here.
I want to add in an extra column called
Category
and categorize all the queries according to custom rules that will search for a string on column A. Ex:
if A2 contains the string 'laptop' then write 'laptop' on the category next to it
So far I have tried a formula to do this but I'm not sure this is the easiest way. Also, if there are lots of categorization rules the formula gets really long and unmanageable.
=IF(ISNUMBER(SEARCH("laptop",A2)),"laptop", IF(ISNUMBER(SEARCH("notebook",A2)),"laptop", IF(ISNUMBER(SEARCH("iphone",A2)),"phone", IF(ISNUMBER(SEARCH("galaxy s",A2)),"phone", "other")))
Can you suggest a better way of doing this where I can have the rules in one sheet in this format:
Query_contains | Category_is
where
Query_contains
would be the string that needs to be matched in column A from the initial sheet the andCategory
would be the value that needs to be filled into column D.解决方案Ok, I changed your sheet a bit....
Supposing all your data was in cells A1:C9, then you had the following table in cells F1:G5
Search_For: Category: laptop Laptop iphone Phone galaxy Phone notebook Laptop
Now, in cell D2, put in the following formula:
=IFERROR(INDEX(G$2:G$5,MATCH(TRUE,ISNUMBER(SEARCH(F$2:F$5,A2)),0)),"other")
And enter it as an array formula Meaning, once you enter it, hit CTRL+SHIFT+ENTER.
You can then drag the formula from cell D2 down and it should give you the desired results (and you can, of course, increase the list in columns F & G as necessary).
Hope this does the trick!!
这篇关于如何根据自定义规则对excel项目进行分组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!