如何使用单元格优先级重复列表计算唯一值 - Google Spreadsheets [英] How to Count Unique Values From a Repeated List in Priority with a Cell - Google Spreadsheets

问题描述

我有四列的电子表格：



<预LANG = XML> question_id USER_ID unique_question_ids unique_user_ids
X 11 X的＃？ （= 2）
X 12 Y？ （= 3）
X 12
X 12
Y 13
Y 14
Y 15

前两列是问题及其相应的用户，包括两者的重复。



目标是这个：我想计算每个独特问题的唯一身份用户数。



我首先找到了使用UNIQUE函数找到的unique_questions。这列出了unique_question_ids列中唯一的问题（即X，Y）



现在我想计算每个独特问题的唯一用户数量？另一个问题是我不知道X和Y从哪里开始，但是它们仍然按顺序排序（真正的电子表格非常大）。



我该怎么做呢？我想我可以使用COUNTIFS，但这不计入唯一值。我还考虑使用一个函数来返回问题列中X或Y所在的范围，然后在下一列（即user_id）中计算唯一值。但我找不到一个返回列中值的单元格范围的函数。我也在Google Spreadsheets上这样做。



任何想法或想法都会受到赞赏，谢谢

解决方案

I have a spreadsheet with four columns:

question_id  user_id   unique_question_ids   # of unique_user_ids
 X            11        X                     ? (=2)
 X            12        Y                     ? (=3)
 X            12
 X            12
 Y            13
 Y            14
 Y            15

The first two columns are questions and their corresponding users and include repeats of both.

The objective is this: I want to count the number of unique users for each unique question.

I started with first finding the unique_questions which I found using the UNIQUE function. This listed what questions are unique in the unique_question_ids column (i.e. X, Y)

Now I want to count the number of unique users that each unique question has? The other problem is that I do not know where X and Y start, however they are still sorted in order (real spreadsheet is very large).

How would I go about doing this? I am thinking I could use COUNTIFS but this doesn't count for unique values. I also thinking of using a function that would return the range of where X or Y is located in the question_id column, and then count in the next column (i.e. user_id) for unique values. But I cannot find a function that returns the cell range of a value in a column. I am also doing this on Google Spreadsheets.

Any thoughts or ideas would be appreciated, thanks

解决方案

这篇关于如何使用单元格优先级重复列表计算唯一值 - Google Spreadsheets的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！