如何在Google表格中列出最常用的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.

另外，我想摆脱逗号.

Plus, I would like to get rid of the commas.

这是我要实现的目标，是指向我在其中布置了所有数据的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)''"))

---

总短语:

---

overall phrase:

=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表格中列出最常用的3个单词的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！