如何用Google Sheets Query公式统计多列课程出现次数并返回课程计数列表

Let's get your formula sorted to count how often each unique course appears across columns C2:C21, F2:F21, and I2:I21, then output the results exactly as you need. Your original formula was trying to group across three separate columns, which isn't what we need—first we need to stack all course entries into a single list, then count from there.

Solution 1: Two-column Output (Course Name + Total Count) #

This gives you a clean, readable table with course names in one column and their total occurrences in another:

=QUERY({C2:C21; F2:F21; I2:I21}, "select Col1, count(Col1) where Col1 is not null group by Col1 label Col1 '课程名称', count(Col1) '出现次数'")

Breakdown of how this works: #

• {C2:C21; F2:F21; I2:I21}: Stack the three columns vertically into a single array (the semicolon tells Google Sheets to combine rows, not columns).
• select Col1, count(Col1): Target the first (and only) column of our stacked array, then count how many times each value repeats across all three original columns.
• where Col1 is not null: Filters out empty cells so we don't count blanks as a "course".
• group by Col1: Groups all identical course names together, so each unique course gets one row with its total count.
• label ...: Adds custom headers to make the output easier to scan (you can remove this part if you don't need headers).

Solution 2: Single-column Output (Combined Name + Count) #

If you want the course name and count merged into the same cell (e.g., "Algebra: 5"), use this version instead:

=QUERY({C2:C21; F2:F21; I2:I21}, "select concat(Col1, ': ', count(Col1)) where Col1 is not null group by Col1 label concat(Col1, ': ', count(Col1)) '课程统计'")

Why your original formula didn't work: #

Your initial query referenced C,F,I and tried to group by those columns—but when you stack columns with {...}, the resulting array doesn't use the original column letters (it uses Col1, Col2, etc.). Grouping by C,F,I would have treated combinations of values across the three original columns as unique entries, rather than counting individual courses across all columns.

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