Google Sheets双词短语频率计算公式求助

Absolutely! You can pull this off with a combination of Google Sheets array functions—no scripts needed. Let’s break this down step by step, assuming your search terms are in column A (starting at A2, with A1 as a header):

First, we’ll generate every consecutive two-word pair from each search term. Paste this formula into an empty column (say, B2):

=ARRAYFORMULA(TRIM(FLATTEN(IFERROR(SPLIT(REGEXREPLACE(A2:A, "\s+", " "), " ")&" "&OFFSET(SPLIT(REGEXREPLACE(A2:A, "\s+", " "), " "),,1)))))

Let’s break down what this does:

• REGEXREPLACE(A2:A, "\s+", " "): Normalizes multiple spaces between words into a single space (handles messy input).
• SPLIT(..., " "): Splits each search term into individual words.
• OFFSET(..., ,1): Shifts the split words one column to the right, so we pair each word with the next one in the term.
• &" "&: Joins each word with its next neighbor into a two-word phrase.
• FLATTEN: Turns all the nested phrase lists into a single column.
• TRIM: Cleans up any accidental leading/trailing spaces.
• IFERROR: Skips cells with only one word (since they can’t form a two-word phrase) or empty cells.

Now we’ll count how often each phrase appears. You can do this with two separate formulas, or combine them into one:

Option 1: Two separate columns (easier to read) #

• In C2, get unique phrases:

  =UNIQUE(B2:B)
  

• In D2, count occurrences for each unique phrase:

  =ARRAYFORMULA(COUNTIF(B2:B, C2:C))
  

Option 2: One combined formula (no intermediate column) #

Paste this into an empty column to get a two-column table of phrases and their counts:

=ARRAYFORMULA(
  LET(
    phrases, TRIM(FLATTEN(IFERROR(SPLIT(REGEXREPLACE(A2:A, "\s+", " "), " ")&" "&OFFSET(SPLIT(REGEXREPLACE(A2:A, "\s+", " "), " "),,1)))),
    unique_phrases, UNIQUE(phrases),
    counts, COUNTIF(phrases, unique_phrases),
    {unique_phrases, counts}
  )
)

The LET function makes this cleaner by storing intermediate values, so we don’t repeat the phrase-extraction logic multiple times.

If your column A has:

• "best coffee shop"
• "coffee shop near me"
• "morning coffee"

The extracted phrases will be:

• "best coffee"
• "coffee shop"
• "coffee shop"
• "shop near"
• "near me"
• "morning coffee"

And the frequency count will show:

• "best coffee": 1
• "coffee shop": 2
• "shop near": 1
• "near me": 1
• "morning coffee": 1

内容的提问来源于stack exchange，提问作者jhanrahan78