具有多列的Excel中的唯一值

I'm trying to count unique values from one worksheet to another in excel and am having a bit of trouble. The way it currently works is it counts ALL instances of an event (multiple entries of the event for the same date), when they should be counted as one.

我试图在excel中计算从一个工作表到另一个工作表的唯一值,并且遇到一些麻烦。它当前的工作方式是计算事件的所有实例(同一日期的事件的多个条目),何时应将它们计为一个。

Worksheet #1 looks a bit like this:

工作表#1看起来有点像这样:

DateCalled   Time      Type        LoggedBy       ID        EventDate       EventTime
10/2/2012   10:00AM   Expected       Tom         mjones     10/3/2012     11:00 - 14:00
10/2/2012   10:00AM   Expected       Tom         mjones     10/3/2012     14:00 - 17:00
  .          .         .            .            .            .               .
  .          .         .            .            .            .               .

Worksheet #2 looks a bit like this:

工作表#2看起来有点像这样:

  IDname        ID       TotalCount  Type1-Count   Type2-Count   Type3-Count
Mike Jones     mjones        9            1            7             1

I need the formula on worksheet #2 to count the unique number of entries (e.g., more than one entry per day per ID) and add them to each Type-Count. Currently the formula in each Type-Count column is this:

我需要工作表#2上的公式来计算唯一的条目数(例如,每个ID每天多于一个条目)并将它们添加到每个类型计数中。目前,每个Type-Count列中的公式是:

=COUNTIFS(Worksheet1!$E:$E,$B14,Worksheet1!$C:$C,"Type2")

Where column E is ID on Worksheet1, B14 is the individual's ID on Worksheet2, and Column C is the Type on Worksheet1.

如果E列是Worksheet1上的ID,则B14是Worksheet2上的个人ID,而C列是Worksheet1上的Type。

I've tried a few nested IF/COUNTIF statements in SUM but I can't really come up with something that works.

我在SUM中尝试了一些嵌套的IF / COUNTIF语句,但我无法想出一些有用的东西。

1 个解决方案

#1

This "array formula" should give you the number of different dates for each name/type2 combination

这个“数组公式”应该为您提供每个名称/类型2组合的不同日期的数量

=COUNT(1/FREQUENCY(IF(Worksheet1!$C$2:$C$100="Type2",IF(Worksheet1!$E$2:$E$100=$B14,Worksheet1!$A$2:$A$100)),Worksheet1!$A$2:$A$100))

confirmed with CTRL+SHIFT+ENTER

用CTRL + SHIFT + ENTER确认

Note: best to restrict the ranges if you can to make the formula quicker

注意:如果您可以更快地制定公式,最好限制范围

#1