计算日期范围内成功联系的不同人员的数量 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:

一个.客户名称湾尝试日期联系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 部分并将每个条件收集到一个分子中，如果全部为真，则解析为 1，如果有任何为假，则解析为 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 中的任何条件为假，则分子为 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屋！