如何在 Google Sheets 上列出最常用的 3 字串 [英] How to list the most frequent 3-word strings on Google Sheets
问题描述
我有一个行业列表和一个相邻的行业列表来对它们进行分类.我想知道哪些行业最常见,但我无法让 Sheets 将两个词的类别解释为一个.
I have a list of industries with a adjacent list of industries to categorize them. I would like to know which industries are the most common but I don't manage to make Sheets interpret two-word categories as one.
首先,我想知道哪 5 个类别是最常见的.另外我想知道前5个单字(黑色)、二字(红色)和三字(蓝色)类别.
First, I would like to know which 5 categories are the most common overall. Also I would like to know the top 5 one-word (black), two-word (red) and three-word (blue) categories.
另外,我想去掉逗号.
这是我想要实现的目标,以及一个指向我已列出所有数据的 google 表格文档的链接:
Here's what I want to achieve and a link to a google sheets document where I've laid out all the data:
https://docs.google.com/spreadsheets/d/13N8gc4POPhFhTvyqq-UugWS5GCgcONwliacSL8-MAr8/edit#gid=0
如何对这些类别进行分组和列出?
How can these categories be grouped and listed?
推荐答案
总词:
=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ", ")),
"select Col1,count(Col1)
group by Col1
order by count(Col1) desc
limit 5
label count(Col1)''"))
<小时>
总词:
=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ",")),
"select Col1,count(Col1)
group by Col1
order by count(Col1) desc
limit 5
label count(Col1)''"))
<小时>
一个字:
one word:
=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ","))),
"select Col1,count(Col1)
where not Col1 contains ' '
group by Col1
order by count(Col1) desc
limit 5
label count(Col1)''"))
<小时>
两个字:
two words:
=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ","))),
"select Col1,count(Col1)
where Col1 matches '\w+ \w+'
group by Col1
order by count(Col1) desc
limit 5
label count(Col1)''"))
三个字:
=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(B3:B11&",",,99^99), ","))),
"select Col1,count(Col1)
where Col1 matches '\w+ \w+ \w+'
group by Col1
order by count(Col1) desc
limit 5
label count(Col1)''"))
这篇关于如何在 Google Sheets 上列出最常用的 3 字串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!