如何在 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屋！