Количество без повторений

Дата: Пятница, 18.02.2022, 23:22 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 1 ±

Замечаний: 0% ±

Excel 2016

Здравствуйте.
Требуется на каждый город посчитать количество без повторений по условию.
Я это делаю по фильтру, думаю так понятнее объяснить…
Выбираю город Москва, далее где условия выбираю допустим значение 612. После в строке Е выбираю все что получилось после фильтра и копирую в пустое место. Нажимаю удалить дубликаты и потом уже смотрю по количеству сколько осталось и вношу эти данные.
Возможно ли это сделать как ни будь по формуле?

К сообщению приложен файл: 9002167.xls (49.5 Kb)

Nic70y

Дата: Суббота, 19.02.2022, 07:42 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8759

Репутация: 2273 ±

Замечаний: 0% ±

Excel 2010

формула массива

Код

=СУММ(--(ЕСЛИОШИБКА(ПОИСКПОЗ($E$4:$E$415;ЕСЛИ($F$4:$F$415=L$1;ЕСЛИ(ЕЧИСЛО(ПОИСК($K2;$I$4:$I$415));$E$4:$E$415)););)=СТРОКА($I$4:$I$415)-3))

К сообщению приложен файл: 22.xls (56.0 Kb)

ЮMoney 41001841029809

Дата: Суббота, 19.02.2022, 13:00 | Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

Еще вариант:

Код

=COUNT(0/(IF(SEARCH($K2;$I$4:$I$415)*($F$4:$F$415=L$1);MATCH($E$4:$E$415;$E$4:$E$415;))=ROW($I$4:$I$415)-3))

Дата: Суббота, 19.02.2022, 14:33 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 1 ±

Замечаний: 0% ±

Excel 2016

Спасибо, на много упрощает задачу!
А возможно ли так же посчитать количество без повторений, как на примере в строках М и N. Просто пробую как ни будь изменить по вашим примерам L$1, на что то вроде {82;738}, но что то не получается...

Дата: Суббота, 19.02.2022, 14:43 | Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата DjiM, 19.02.2022 в 14:33, в сообщении № 4 (

)

Просто пробую как ни будь изменить по вашим примерам L$1, на что то вроде {82;738}, но что то не получается...

Вы на правильном пути. Просто надо было использовать другой разделитель.

Код

=COUNT(0/(IF(SEARCH($K2;$I$4:$I$415)*($F$4:$F$415={82\738});MATCH($E$4:$E$415;$E$4:$E$415;))=ROW($I$4:$I$415)-3))

Дата: Воскресенье, 20.02.2022, 23:09 | Сообщение № 6

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

Еще немного поработал над вариантом. Думал, как преобразовать массив чисел-как-текст из М1 в виртуальный массив чисел.
Итак, для L2, M2 и ниже:

Код

=COUNT(0/(SEARCH($K2;$I$4:$I$415)*($F$4:$F$415=IF({1\0};IFERROR(LEFT(L$1;SEARCH(" ";L$1));L$1);IFERROR(RIGHT(L$1;SEARCH(" ";L$1));2=1))+0)*(MATCH($E$4:$E$415;$E$4:$E$415;)=ROW($I$4:$I$415)-3)))

Для "остальных" с исключениями из М1 и L1:

Код

=COUNT(0/((SEARCH($K2;$I$4:$I$415)*($F$4:$F$415=TRANSPOSE(IFERROR(LARGE(IF(ISNA(MATCH($F$4:$F$415;{612\82\738};))*MATCH($F$4:$F$415;$F$4:$F$415;)=ROW($F$4:$F$415)-3;$F$4:$F$415);ROW($A$1:$A$6));""))))*(MATCH($E$4:$E$415;$E$4:$E$415;)=ROW($E$4:$E$415)-3)))

Проверка общего количества в L8, L9

К сообщению приложен файл: 4296265.xls (61.5 Kb)

Дата: Понедельник, 21.02.2022, 19:47 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 1 ±

Замечаний: 0% ±

Excel 2016

Претензий не имею, не подумайте... На примере все верно считалось... На работе сегодня попробовал, редко, но некоторые места не так считает.
Что выделено зеленым, это я поставил с работы данные и теперь результат в L2 и так же L5 – не верный, должно быть 26.
А что не так, не пойму, почему не досчитывает…

К сообщению приложен файл: 111111.xls (56.0 Kb)

Сообщение отредактировал DjiM - Понедельник, 21.02.2022, 19:49

Дата: Понедельник, 21.02.2022, 19:59 | Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 1 ±

Замечаний: 0% ±

Excel 2016

хм... а по формуле Nic70y, верно считает, буду разбираться дальше....

Дата: Вторник, 22.02.2022, 00:50 | Сообщение № 9

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

Выискивать ошибки формулы в большом объеме данных то еще удовольствие.
А так работает?

Код

=COUNT(0/(MATCH($E$4:$E$415;IF(($F$4:$F$415=L$1)*(SEARCH($K2;$I$4:$I$415));$E$4:$E$415);)=ROW($E$4:$E$415)-3))

Update:
Вроде решил задачу.
Для L1-M1

Код

=SUM((($F$4:$F$415=TRIM(MID(SUBSTITUTE(L$1;"и";REPT(" ";99));COLUMN($A$1:INDEX($A$1:$IS$1;LEN(SUBSTITUTE(L$1;"и";"-"))-LEN(SUBSTITUTE(L$1;"и";))+1))*99-98;99)-1)+{1})*ISNUMBER(SEARCH($K2;$I$4:$I$415))/COUNTIFS($E$4:$E$415;$E$4:$E$415;$F$4:$F$415;$F$4:$F$415)))

Для остальных (с исключением номеров из L1-M1)

Код

=SUM((($F$4:$F$415=TRANSPOSE(IFERROR(LARGE(IF(ISNA(MATCH($F$4:$F$415;{566\82\738};))*MATCH($F$4:$F$415;$F$4:$F$415;)=ROW($F$4:$F$415)-3;$F$4:$F$415);ROW($A$1:$A$7));"")))*ISNUMBER(SEARCH($K2;$I$4:$I$415))/COUNTIFS($E$4:$E$415;$E$4:$E$415;$F$4:$F$415;$F$4:$F$415)))

Проверка общего кол-ва в L9-L10.

К сообщению приложен файл: 0990021.xls (62.5 Kb)

Сообщение отредактировал Egyptian - Вторник, 22.02.2022, 01:22

Дата: Четверг, 24.02.2022, 12:53 | Сообщение № 10

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

Жалко будет если окончательное решение в рамках примера пропадет, поэтому выкладываю его.

* в ячейке М1 вводятся значения в текущем формате через "и";
* в ячейке L1 сделан выпадающий список с отфильтрованными значениями (в нем, во избежание двойных расчетов не присутствуют значения из М1)
* в ячейке N1 показываются какие значения не попадают в список "остальных"
* в ячейках О2:О3 выведены добавочные проверки по кол-вам
* в столбце Р показаны все номера, кроме уже задействованных М1 и L1
* в столбце Q отфильтрованные значения для выпадающего списка
* в столбце S все доступные на данный момент номера (условия)

Важно. Для корректной работы формул нужен офис не ниже 2010.

К сообщению приложен файл: 111111.xlsx (20.8 Kb)

Сообщение отредактировал Egyptian - Четверг, 24.02.2022, 13:33