Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Количество без повторений - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Количество без повторений (Формулы/Formulas)
Количество без повторений
DjiM Дата: Пятница, 18.02.2022, 23:22 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте.
Требуется на каждый город посчитать количество без повторений по условию.
Я это делаю по фильтру, думаю так понятнее объяснить…
Выбираю город Москва, далее где условия выбираю допустим значение 612. После в строке Е выбираю все что получилось после фильтра и копирую в пустое место. Нажимаю удалить дубликаты и потом уже смотрю по количеству сколько осталось и вношу эти данные.
Возможно ли это сделать как ни будь по формуле?
К сообщению приложен файл: 9002167.xls(49.5 Kb)
 
Ответить
СообщениеЗдравствуйте.
Требуется на каждый город посчитать количество без повторений по условию.
Я это делаю по фильтру, думаю так понятнее объяснить…
Выбираю город Москва, далее где условия выбираю допустим значение 612. После в строке Е выбираю все что получилось после фильтра и копирую в пустое место. Нажимаю удалить дубликаты и потом уже смотрю по количеству сколько осталось и вношу эти данные.
Возможно ли это сделать как ни будь по формуле?

Автор - DjiM
Дата добавления - 18.02.2022 в 23:22
Nic70y Дата: Суббота, 19.02.2022, 07:42 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 7722
Репутация: 1852 ±
Замечаний: 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
 
Ответить
Сообщениеформула массива
Код
=СУММ(--(ЕСЛИОШИБКА(ПОИСКПОЗ($E$4:$E$415;ЕСЛИ($F$4:$F$415=L$1;ЕСЛИ(ЕЧИСЛО(ПОИСК($K2;$I$4:$I$415));$E$4:$E$415)););)=СТРОКА($I$4:$I$415)-3))

Автор - Nic70y
Дата добавления - 19.02.2022 в 07:42
Egyptian Дата: Суббота, 19.02.2022, 13:00 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 384
Репутация: 132 ±
Замечаний: 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))
 
Ответить
СообщениеЕще вариант:
Код
=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))

Автор - Egyptian
Дата добавления - 19.02.2022 в 13:00
DjiM Дата: Суббота, 19.02.2022, 14:33 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 1 ±
Замечаний: 0% ±

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

Автор - DjiM
Дата добавления - 19.02.2022 в 14:33
Egyptian Дата: Суббота, 19.02.2022, 14:43 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 384
Репутация: 132 ±
Замечаний: 0% ±

Excel 2013/2016
Просто пробую как ни будь изменить по вашим примерам 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))
 
Ответить
Сообщение
Просто пробую как ни будь изменить по вашим примерам 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))

Автор - Egyptian
Дата добавления - 19.02.2022 в 14:43
Egyptian Дата: Воскресенье, 20.02.2022, 23:09 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 384
Репутация: 132 ±
Замечаний: 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)
 
Ответить
СообщениеЕще немного поработал над вариантом. Думал, как преобразовать массив чисел-как-текст из М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

Автор - Egyptian
Дата добавления - 20.02.2022 в 23:09
DjiM Дата: Понедельник, 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
 
Ответить
СообщениеПретензий не имею, не подумайте... На примере все верно считалось... На работе сегодня попробовал, редко, но некоторые места не так считает.
Что выделено зеленым, это я поставил с работы данные и теперь результат в L2 и так же L5 – не верный, должно быть 26.
А что не так, не пойму, почему не досчитывает…

Автор - DjiM
Дата добавления - 21.02.2022 в 19:47
DjiM Дата: Понедельник, 21.02.2022, 19:59 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 1 ±
Замечаний: 0% ±

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

Автор - DjiM
Дата добавления - 21.02.2022 в 19:59
Egyptian Дата: Вторник, 22.02.2022, 00:50 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 384
Репутация: 132 ±
Замечаний: 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
 
Ответить
СообщениеВыискивать ошибки формулы в большом объеме данных то еще удовольствие.
А так работает?
Код
=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.

Автор - Egyptian
Дата добавления - 22.02.2022 в 00:50
Egyptian Дата: Четверг, 24.02.2022, 12:53 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 384
Репутация: 132 ±
Замечаний: 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
 
Ответить
СообщениеЖалко будет если окончательное решение в рамках примера пропадет, поэтому выкладываю его.

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

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

Автор - Egyptian
Дата добавления - 24.02.2022 в 12:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Количество без повторений (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!