Страницы: 1
RSS
Подсчет количества пересечение значений между собой по уникальным критериям
 
Друзья, добрый день!
Помогите пожалуйста решить задачу.
У нас есть 3 сервисных центра и телефонные номера абонентов, которые в них звонили.
Задача заключается в том, чтобы определить количество звонков абонента в разные сервисные центры.
В примере есть телефонный номер абонента 79621272700, данный клиент предпринимал попытки дозвониться в 3 сервисных центра.
Сервисный центр 1 - 2 попытки дозвониться
Сервисный центр 2 - 1 попытка дозвониться
Сервисный центр 3 - 1 попытка дозвониться
Нам необходимо подсчитать в столбце D, количество уникальных (первых) попыток дозвониться в каждый из сервисных центров, с соответствующим порядковым номером, т.е. найти пересечения уникального номера абонента между сервисными центрами и подсчитать их количество.
Желтой заливкой выделил количества обращений, так как предполагаю это должно выглядеть.

Пытался решить эту задачу счетом, к сожалению ничего не вышло =(
Есть ли варианты решения данной задачи?
Заранее спасибо!
 
slamp182,https://excel2.ru/articles/podschet-unikalnyh-tekstovyh-znacheniy-v-ms-excel
Не бойтесь совершенства. Вам его не достичь.
 
slamp182, здравствуйте!

Выражение вида
=СЧЁТЕСЛИМН($B$3:$B$11;$B3;$C$3:$C$11;$C3)
возвратит Вам количество одновременных совпадений конкретного сервисного центра и конкретного номера телефона.
Если таблицу предварительно отсортировать сначала по сервисным центрам, а потом по номерам телефонов, то выражение
=ЕСЛИ(($B2=$B3)*($C2=$C3)=1;"";СЧЁТЕСЛИМН(...))
вернёт пустые (нечисловые!) значения во все строки-дубликаты

Тогда формула =СЧЁТЕСЛИМН($C$3:$C$11;телефон;$D$3:$D$11;">0") вернёт количество сервисных центров, задействованных конкретным телефонным номером, если вспомогательное выражение будет записано в ячейки столбца D.

То ли это, что Вам нужно?

============UPDATE================
Для неотсортированной таблицы во вспомогательный столбец можно записывать выражение вида:
=1/СЧЁТЕСЛИМН($B$3:$B$11;$B3;$C$3:$C$11;$C3)
А затем суммировать данные этого столбца для каждого телефона формулой СУММЕСЛИ()

============UPDATE-2==============
Прикреплён файл
Изменено: IKor - 03.09.2021 09:33:39
 
Цитата
IKor написал:
То ли это, что Вам нужно?
Большое спасибо за Ваш вариант решения задачи, но что-то у меня не выходит применить ваши формулы.
Возможно это не совсем то что нужно / либо я не совсем понятно расписал условия задачи (скорее второе) =)

Попробую расписать максимально подробно.

Клиент с номером 79621272700
Позвонил в Сервисный центр 1 - в столбце кол-во обращений, ему присвоено значение 1
Потом клинт позвонил еще раз в Сервисный центр 1, в данном случае этому звонку не присваивается номер, поскольку обращение является уже не уникальным.
Далее клиент позвонил в Сервисный центр 2 - в столбце кол-во обращений, ему присвоено значение 2
Третий звонок клиент совершил в Сервисный центр 3 - ему присвоено значение 3
Предположим, если четвертый по счету раз клиент позвонит опять в Сервисный центр 3, ему не должно присваиваться значение 4, поскольку уникальных звонков в сервисный центр 3, клиент совершил всего 1, четвертый звонок по счету, который пришелся на сервисный центр 3, является уже не уникальным.
В выгрузке еще присутствуют даты обращения, но я решил не усложнять и так запутанную задачу.


 
Изменено: slamp182 - 02.09.2021 15:17:02
 
Цитата
Mershik написал:
https://excel2.ru/articles/podschet-unikalnyh-tekstovyh-znacheniy-v-ms-excel
Спасибо! Попробую ознакомиться с этим способом
 
slamp182,
Я прикрепил ко вчерашнему сообщению файл примера.

В оригинальном запросе Вы просили определить количество задействованных сервисных центров для конкретного номера телефона. Результат - это одно число.
Теперь Вы просите для заданного телефона возвращать несколько чисел: {1;2;3} - если хотя бы по одному разу были задействованы все три сервисных центра. Нужно ли менять порядок чисел, если телефон обзванивал их не по порядку? Требуется ли возвращать только значимые (числовые результаты - как показано выше) или вместе с ними можно перемешать пустые значения, чтобы размер массива соответствовал количеству записей в оригинальной таблице?
Куда следует выводить результаты расчёта?

Таких вопросов можно придумать множество: поэтому всем будет проще, если Вы продемонстрируете (сделанный вручную) необходимый результат на наиболее разнообразном примере исходных данных.
 
Вариант PQ
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Сервисный центр", type text}, {"Номер телефона", type text}}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Измененный тип", "Индекс", 0, 1),
    #"Сортированные строки" = Table.Sort(#"Добавлен индекс",{{"Номер телефона", Order.Ascending}, {"Сервисный центр", Order.Ascending}}),
    #"Сгруппированные строки" = Table.Group(#"Сортированные строки", {"Сервисный центр", "Номер телефона"}, {{"Количество", each _, type table}}),
    #"Сгруппированные строки1" = Table.Group(#"Сгруппированные строки", {"Номер телефона"}, {{"Количество", each _, type table}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки1", "Пользовательская", each Table.AddIndexColumn([Количество],"№ в группе",1,1)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"Количество"}),
    #"Развернутый элемент Пользовательская" = Table.ExpandTableColumn(#"Удаленные столбцы", "Пользовательская", {"Сервисный центр", "Количество", "№ в группе"}, {"Сервисный центр", "Количество", "№ в группе"}),
    #"Развернутый элемент Пользовательская.Количество" = Table.ExpandTableColumn(#"Развернутый элемент Пользовательская", "Количество", {"Индекс"}, {"Индекс"}),
    #"Сортированные строки1" = Table.Sort(#"Развернутый элемент Пользовательская.Количество",{{"Индекс", Order.Ascending}}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Сортированные строки1",{"№ в группе"})
in
    #"Другие удаленные столбцы"
Изменено: Msi2102 - 03.09.2021 10:59:20
 
IKor, большое спасибо за Ваш интерес к данной задаче, Msi2102, предложил рабочую версию файла, воспользуюсь ей.
Соглашусь, задачу не очень разборчиво поставил.

Msi2102, большое спасибо за Ваш вариант решения данной задачи! Возьму в работу его!
Такие штуки я к сожалению писать не умею =(, но есть к чему стремиться =)  
Изменено: vikttur - 03.09.2021 13:21:28
Страницы: 1
Наверх