如何使用 Google 表格创建按计数分组的李克特量表结果表

发布于 2025-01-15 11:45:19 字数 459 浏览 3 评论 0原文

我想获取对 Likert 调查项目的答复，并创建每个答复计数的汇总表（文本标签：强烈不同意、不同意、中立、同意、强烈同意）。

（示例数据集和手动创建的所需结果表可在此中找到sheet)

我一次只能做一个问题与查询。

=QUERY(likertTable,"select B,count(B) group by B",1)

当未为问题选择一个选项时，创建 QUERY 函数数组并不总是有效，从而导致行大小不匹配。

有没有办法使用单个 QUERY 或其他（例如 VLOOKUP、COUNTIF）公式创建所需的表？

原文

I want to take responses to Likert survey items and create a summary table of the counts of each response (text labels: strongly disagree, disagree, neutral, agree, strongly agree).

(sample data set & manually created desired results table available in this sheet)

I am only able to do one question at a time with QUERY.

=QUERY(likertTable,"select B,count(B) group by B",1)

Creating an array of QUERY functions does not always work when one option is not selected for a question, resulting in mismatched row size.

Is there a way to create the desired table with a single QUERY—or other (e.g., VLOOKUP, COUNTIF)—formula?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只想待在家 2025-01-22 11:45:19

根据条件 F3:F7 的顺序尝试一下

=query(arrayformula(split(flatten(B2:D2&"~"&vlookup(B3:D,{$F$3:$F$7,row($F$3:$F$7)},2,0)),"~")), 
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1")

Try this, based on the order of criteria F3:F7

=query(arrayformula(split(flatten(B2:D2&"~"&vlookup(B3:D,{$F$3:$F$7,row($F$3:$F$7)},2,0)),"~")), 
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1")

回复收藏 0 原文

笨死的猪 2025-01-22 11:45:19

尝试：

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(B3:D="",,B2:D2&"×"&B3:D)), "×"), 
 "select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1"))

进行排序，手动添加“排序列”并传递另一个QUERY：

=QUERY({ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(B3:D="",,B2:D2&"×"&B3:D)), "×"),
"select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1")),
{"sort";4;2;3;5;1}},
"select Col1,Col2,Col3,Col4 order by Col5",1)

try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(B3:D="",,B2:D2&"×"&B3:D)), "×"), 
 "select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1"))

to sort, add "sort column" manually and pass through another QUERY:

=QUERY({ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(B3:D="",,B2:D2&"×"&B3:D)), "×"),
"select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1")),
{"sort";4;2;3;5;1}},
"select Col1,Col2,Col3,Col4 order by Col5",1)

回复收藏 0 原文

~没有更多了~