计算在日期范围内成功联系的不同人员的数量Excel [英] Counting number of distinct people successfully contacted in a date range Excel

问题描述

下午好，

我有一个很大的数据表，其中包含尝试与客户联系的帐户.每个条目都标有:

I have a large data table which contains accounts of attempts to contact clients. Each entry is marked with:

a.客户名称 b.尝试联系的日期 C.接触尝试是否成功(S)(U)

a. The name of the client b. The date contact was attempted c. Whether the contact attempt was successful (S) or not (U)

下面是数据示例，其中的机密名称已替换

Here's an example of the data, with confidential names replaced

我需要计算每个月成功联系的不同人数，并将其显示在这样的表格中:

I need to calculate the number of distinct people successfully contacted each month, and display it in a table like this:

请注意，我不想计算他们成功联系的时间，即使他们至少已经联系一次.

Note that I don't want to count the number of time's they've been successfully contacted, just if they have at least been contacted once.

我尝试了以下公式:

{=SUMPRODUCT(IF((EncounterDate<=DU4)*(EncounterDate>=DT4), 
1/COUNTIFS(EncounterDate, "<="&DU4, EncounterDate, ">="&DT4, 
EncLastFirstName, EncLastFirstName, CMService, "S"), 0))}

DU4-月的最后日期
DT4-每月的第一个日期

DU4 - Last Date of month
DT4 - First Date of month

我在使用此公式时出现Div/0错误.知道我做错了什么，或者应该怎么做吗?

I'm getting a Div/0 error with this formula. Any idea what I'm doing wrong, or what I should be doing instead?

谢谢，谢谢！

推荐答案

您将得到一个#DIV/0!，因为您的除法的分母可以为零，并且对第一个未处理的错误使用公式短路".如果有任何行的结果为零，

You're getting a #DIV/0! because the denominator of your division can be zero and formulas 'short circuit' on the first unhandled error. If any row delivers a result of zero from,

COUNTIFS(EncounterDate, "<="&DU4, EncounterDate, ">="&DT4, EncLastFirstName, EncLastFirstName, CMService, "S")

...然后您将得到一个#DIV/0!和1/COUNTIFS(...).

... then you get a #DIV/0! with 1/COUNTIFS(...).

我的解决方案是删除IF部分，并将每个条件收集到一个分子中(如果全为true则解析为1，如果全为false则解析为0).此外，将标准分子的反数添加到除法运算的分母中；例如如果(CMService="S")在分子中，则将(CMService<>"S")添加到分母中.在这些情况下，分母将为非零，分子将为零.零除以零仍然是零(例如0/100 == 0)，所以分母是什么都没有关系，并且永远不会出现#DIV/0!错误.

My solution is to remove the IF portion and collect each criteria into a numerator that resolves as 1 if all true and 0 if any false. Additionally, add the inverse of the criteria numerator to the denominator of the division operation; e.g. if (CMService="S") is in the numerator, then add (CMService<>"S") to the denominator. In these cases, the denominator will be non-zero and the numerator will be zero. Zero divided by anything is still zero (e.g. 0/100 == 0) so it doesn't matter what the denominator is and you will never get a #DIV/0! error.

如果分子和Countifs中的所有条件均为真，则分子为1，并且分母中未添加任何内容.如果分子和countifs中的任何条件为false，则分子为0，并且已向分母中添加了某些内容以确保不会出现#DIV/0!.

If all conditions in the numerator and countifs are true, the numerator is 1 and nothing has been added to the denominator. If any conditions in the numerator and countifs are false, the numerator is 0 and something has been added to the denominator to ensure that no #DIV/0! will occur.

在DV4中，作为标准公式没有 CSE，

In DV4 as a standard formula without CSE,

             [<~~~~~~~~~~~~~~~~~ numerator portion ~~~~~~~~~~~~~~~~~>] / [<~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ denominator portion ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~>]+[<~~~~~~~~~~ denominator adjustment portion ~~~~~~~~~~>]
=sumproduct(((EncounterDate>=DT4)*(EncounterDate<=DU4)*(CMService="S"))/(countifs(EncLastFirstName, EncLastFirstName, EncounterDate, ">="&DT4, EncounterDate, "<="&DU4, CMService, "S")+(EncounterDate<DT4)+(EncounterDate>DU4)+(CMService<>"S")))

使用这种基本数学运算抵消所有#DIV/0!的方法，只要将分母的调整数保持为分子的倒数，就可以增加或减少条件数.

With this method of cancelling out any #DIV/0!'s with basic maths, you can increase or decrease the number of criteria as long as you keep the denominator adjustment to the inverse of the numerator.

顺便说一句，您的问题中的预期结果是错误的.

BTW, the expected results in your question are wrong.

这篇关于计算在日期范围内成功联系的不同人员的数量Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！