google-sheets - 计算多列中单词的出现次数

Question

我有一个这样的电子表格，其中值 A-E 是来自表单的相同选项:

+------+------+------+
| Opt1 | Opt2 | Opt3 |
+------+------+------+
|   A  |   A  |   B  |
|   B  |   C  |   A  |
|   C  |   C  |   B  |
|   A  |   E  |   C  |
|   D  |   B  |   E  |
|   B  |   E  |   D  |
+------+------+------+

我想进行排名，显示每个选项选择最多的选项。我已经有了这个，其中 Rank 是选项的排名，number 是选项的计数:

+------+------+------+
| Rank | Opt1 | Numb |
+------+------+------+
|   1  |   A  |   2  |
|   1  |   B  |   2  |
|   3  |   C  |   1  |
|   3  |   D  |   1  |
+------+------+------+ (I have 3 of these, one for each option)

我现在想对 3 个选项做一个总结，做相同的排名但加入选项。它会是这样的:

+------+------+------+
| Rank |Opt123| Numb |
+------+------+------+
|   1  |   B  |   5  |
|   2  |   A  |   4  |
|   2  |   C  |   4  |
|   4  |   E  |   3  |
|   5  |   D  |   2  |
+------+------+------+

最简单的方法是从三个排名表中获取数据还是从原始的三个数据列中获取数据？

我将如何做到这一点？

我已经有了获取选项名称、计数和排名的公式，但我不知道如何使它们适用于多列。

我所拥有的(F 列是数据列之一):

另一张纸上的 B 列:

=SORT(UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F)))); RANK(COUNTIF('Form Responses'!F2:F; UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F))))); COUNTIF('Form Responses'!F2:F; UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F))))); TRUE); FALSE)

C栏:

=ArrayFormula(COUNTIF('Form Responses'!F2:F; FILTER(B2:B;NOT(ISBLANK(B2:B)))))

A栏:

=ARRAYFORMULA(SORT(RANK(FILTER(C2:C;NOT(ISBLANK(C2:C))); FILTER(C2:C;NOT(ISBLANK(C2:C))))))

Answer 1

编辑:

合并列:

=TRANSPOSE(split(join(",",D2:D,E2:E),","))

合并 2 列，不是很干净，但有效。 (同这里 Stacking multiple columns on to one? )

完整公式:

=SORT(UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),",")))))); RANK(COUNTIF(TRANSPOSE(split(join(",",D2:D,E2:E),",")); UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),","))))))); COUNTIF(TRANSPOSE(split(join(",",D2:D,E2:E),",")); UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),","))))))); TRUE); FALSE)

转置可以在排序后完成。