Расчет удержания в Excel

Мастера Эксель,

У меня есть рабочий лист Excel со списком сотрудников (столбец A), дат (столбец B) и клиентов (столбец C). Я хочу рассчитать коэффициент удержания сотрудников на основе посещенных клиентов.

  • Если клиент посетил сотрудника и вернулся в следующем месяце и посетил того же сотрудника, они считаются удержанными для этого сотрудника.
  • Если клиент посетил сотрудника и вернулся в следующем месяце и посетил другого сотрудника, он считается удержанным.
  • Если клиент зашел в течение одного месяца, но не вернулся в следующем месяце, он считается потерянным.
    Employee  Date      Customer
    Zach      11/2/19   Tyler
    Zach      11/20/19  Tyler
    Zach      10/2/19   Tiffany
    Zach      10/3/19   Tiffany
    George    11/1/19   Tiffany
    George    11/9/19   Tyler
    George    10/2/19   Mike
    George    10/30/19  Michael

Я хочу видеть процент удержания для каждого сотрудника за все месяцы, которые есть в данных.

На данный момент я пытался использовать комбинацию COUNTIFS, чтобы найти решение, но безуспешно.


excel retention excel-formula vba
person Zachary George    schedule 14.01.2020    source источник
comment
Хотите включить некоторые примеры данных, ожидаемый результат, ваш текущий код/формулы?   -  person JvdV    schedule 14.01.2020
comment
@JvdV, добавил. извиняюсь что не включил.   -  person Zachary George    schedule 14.01.2020
comment
Лучше всего добавить как уценку, чтобы другим людям не пришлось воссоздавать ваши данные. Кроме того, укажите свою COUNTIFS попытку и фактический ожидаемый результат (в виде чисел) рядом с вашими данными.   -  person JvdV    schedule 14.01.2020
comment
Нужно ли нам смотреть на номер месяца или количество дней, чтобы определить разницу в месяцах? То есть, если первое посещение 01.10, а следующее посещение 30.11, это считается удержанным или потерянным клиентом?   -  person basic    schedule 14.01.2020
comment
Просто номер месяца, поэтому я сделал =month(Date) и преобразовал в число и использовал его для сравнения.   -  person Zachary George    schedule 14.01.2020
comment
Есть ли шанс, что вы можете загрузить это в базу данных и сделать это там? СУБД прекрасно справится с этой задачей.   -  person Hambone    schedule 14.01.2020
comment
К сожалению, нет, это для малого бизнеса и хочет, чтобы все было просто. Спасибо, хотя @Hambone за предложение.   -  person Zachary George    schedule 14.01.2020
comment
Сохраняются для этого работника и сохраняются в эквиваленте, или они учитываются отдельно? Кроме того, куда вы сообщаете о результатах?   -  person rmbradburn    schedule 14.01.2020
comment
@rmbradburn они одинаковы, поэтому, если клиент посещает другого сотрудника в следующем месяце, удержание сотрудников не снижается. Результатом будет простая таблица, показывающая имя сотрудника и его процент удержания.   -  person Zachary George    schedule 14.01.2020
comment
Я не вижу ни одного примера удержанных сотрудников в ваших данных. Можете ли вы добавить пример этого?   -  person Zac    schedule 14.01.2020

Ответы (1)


arrow_upward
1
arrow_downward

Могу предложить расчет на месте, но можно и в отдельный отчет сконвертировать.

Формула для расчета «Удержания»:

=--(SUMPRODUCT(--(C2=$C$2:$C$11);(--(MONTH(B2)+1=MONTH($B$2:$B$11)))+(--(MONTH(B2)-1=MONTH($B$2:$B$11))))>0)

Формула массива для расчета «% удержания» (для сотрудника по всем клиентам):

{=SUM(IF(A2=$A$2:$A$11; IFERROR(1/(COUNTIFS($A$2:$A$11; A2; $C$2:$C$11; $C$2:$C$11;$D$2:$D$11;1));0); 0))/SUMPRODUCT(1/COUNTIF($C$2:$C$11;$C$2:$C$11))}

введите здесь описание изображения

Изменить:

Просто добавьте пример с процентным отчетом в отдельную таблицу. Та же формула, только перемещенная в новую область:

{=SUM(IF(G2=$A$2:$A$11; IFERROR(1/(COUNTIFS($A$2:$A$11; G2; $C$2:$C$11; $C$2:$C$11;$D$2:$D$11;1));0); 0))/SUMPRODUCT(1/COUNTIF($C$2:$C$11;$C$2:$C$11))}

введите здесь описание изображения

person basic    schedule 14.01.2020
comment
Я получаю сообщение об ошибке, когда копирую и вставляю вашу формулу? Похоже на синтаксическую ошибку? - person Zachary George; 14.01.2020
comment
В зависимости от локализации системы может потребоваться замена точки с запятой на запятую в формулах - person basic; 14.01.2020
comment
Да, запятые сработали. Значит, это правильный расчет удержания клиента? Есть ли способ теперь рассчитать общее удержание каждого сотрудника? - person Zachary George; 14.01.2020
comment
Что вы подразумеваете под полным сохранением? Существующая формула в поле «Удержание%» рассчитывается путем деления уникального количества удержанных клиентов на одного сотрудника на общее количество уникальных клиентов. Поскольку в примере сохраняется только Тиффани, каждому сотруднику, перед которым она появляется, назначается 20% (т.е. 1/5). Можете ли вы добавить нужный расчет к вашему примеру? - person basic; 14.01.2020
comment
Моим желаемым результатом была бы, возможно, отдельная таблица, показывающая удержание каждого уникального сотрудника. Так, может быть, сумма для каждого сотрудника сверх удержания %? - person Zachary George; 14.01.2020
comment
Мы не можем суммировать существующие проценты, потому что они уже общие. Вы должны выяснить, как его рассчитать: 1. Удержанные уникальные клиенты / Всего уникальных клиентов 2. Уникальные клиенты, удержанные каждым сотрудником / Всего уникальных клиентов - текущий расчет 3. Уникальные клиенты, удержанные каждым сотрудником / Всего уникальных клиентов на одного сотрудника 4. Уникальные клиенты, удержанные каждым сотрудником / Всего уникальных удержанных клиентов 5. ... - person basic; 14.01.2020