Excel:乘数的唯一值 [英] Excel: Unique Values with Multiplier

问题描述

我有一个包含两列用户和流派的Excel表

I have an Excel Table with two Columns Users and Genres

Users   Genre               
1   Action              
3   Action  Mystery Thriller        
1   Action  Adventure   Drama       
1   Action  Adventure   Fantasy     

我想获得每种流派的所有用户数 如果为一个流派分配了一个以上的用户，则应将用户数乘以

I would like to get all the number of users who are in each genre If there is more than one user assigned to a genre then the number of users should be multiplied

例如

动作应为1 + 3 + 1 + 1 = 6

Action should have 1 + 3 + 1 + 1 = 6

神秘应该有3

惊悚片应该有3

冒险应该有1 +1 = 2

Adventure should have 1 + 1 = 2

戏剧应该有1

幻想应该有1

推荐答案

尝试以下数组公式:

=SUM(IF(ISNUMBER(FIND(" " & F1 & " "," " & $B$1:$B$7 & " ")),$A$1:$A$7))

其中$A$1:$A$10是您的用户范围和$B$1:$B$10流派范围.

where $A$1:$A$10 is your users range and $B$1:$B$10 genre range.

请注意，由于它是一个数组公式，因此应按 CTRL + SHIFT + ENTER 进行计算.

Note, since it's an array formula, you should press CTRL+SHIFT+ENTER to evaluate it.

这是测试工作簿，其中包含分步教程如何应用此公式(有关详细信息，请参见@Stephenmelb答案.)

Here is test workbook with step by step tutorial how to apply this formula (see @Stephenmelb answer for details).

这篇关于Excel:乘数的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！