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

 

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

  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Количество без повторений
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
Группа: Друзья
Ранг: Экселист
Сообщений: 9133
Репутация: 2416 ±
Замечаний: 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Еще вариант:
=СЧЁТ(0/(ЕСЛИ(ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415=L$1);ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;))=СТРОКА($I$4:$I$415)-3))
 
Ответить
СообщениеЕще вариант:
=СЧЁТ(0/(ЕСЛИ(ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415=L$1);ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;))=СТРОКА($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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата DjiM, 19.02.2022 в 14:33, в сообщении № 4 ( писал(а)):
Просто пробую как ни будь изменить по вашим примерам L$1, на что то вроде {82;738}, но что то не получается...

Вы на правильном пути. Просто надо было использовать другой разделитель.
=СЧЁТ(0/(ЕСЛИ(ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415={82\738});ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;))=СТРОКА($I$4:$I$415)-3))
 
Ответить
Сообщение
Цитата DjiM, 19.02.2022 в 14:33, в сообщении № 4 ( писал(а)):
Просто пробую как ни будь изменить по вашим примерам L$1, на что то вроде {82;738}, но что то не получается...

Вы на правильном пути. Просто надо было использовать другой разделитель.
=СЧЁТ(0/(ЕСЛИ(ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415={82\738});ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;))=СТРОКА($I$4:$I$415)-3))

Автор - Egyptian
Дата добавления - 19.02.2022 в 14:43
Egyptian Дата: Воскресенье, 20.02.2022, 23:09 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Еще немного поработал над вариантом. Думал, как преобразовать массив чисел-как-текст из М1 в виртуальный массив чисел.
Итак, для L2, M2 и ниже:
=СЧЁТ(0/(ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415=ЕСЛИ({1\0};ЕСЛИОШИБКА(ЛЕВСИМВ(L$1;ПОИСК(" ";L$1));L$1);ЕСЛИОШИБКА(ПРАВСИМВ(L$1;ПОИСК(" ";L$1));2=1))+0)*(ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;)=СТРОКА($I$4:$I$415)-3)))

Для "остальных" с исключениями из М1 и L1:
=СЧЁТ(0/((ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415=ТРАНСП(ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ($F$4:$F$415;{612\82\738};))*ПОИСКПОЗ($F$4:$F$415;$F$4:$F$415;)=СТРОКА($F$4:$F$415)-3;$F$4:$F$415);СТРОКА($A$1:$A$6));""))))*(ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;)=СТРОКА($E$4:$E$415)-3)))

Проверка общего количества в L8, L9
К сообщению приложен файл: 4296265.xls (61.5 Kb)
 
Ответить
СообщениеЕще немного поработал над вариантом. Думал, как преобразовать массив чисел-как-текст из М1 в виртуальный массив чисел.
Итак, для L2, M2 и ниже:
=СЧЁТ(0/(ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415=ЕСЛИ({1\0};ЕСЛИОШИБКА(ЛЕВСИМВ(L$1;ПОИСК(" ";L$1));L$1);ЕСЛИОШИБКА(ПРАВСИМВ(L$1;ПОИСК(" ";L$1));2=1))+0)*(ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;)=СТРОКА($I$4:$I$415)-3)))

Для "остальных" с исключениями из М1 и L1:
=СЧЁТ(0/((ПОИСК($K2;$I$4:$I$415)*($F$4:$F$415=ТРАНСП(ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ($F$4:$F$415;{612\82\738};))*ПОИСКПОЗ($F$4:$F$415;$F$4:$F$415;)=СТРОКА($F$4:$F$415)-3;$F$4:$F$415);СТРОКА($A$1:$A$6));""))))*(ПОИСКПОЗ($E$4:$E$415;$E$4:$E$415;)=СТРОКА($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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Выискивать ошибки формулы в большом объеме данных то еще удовольствие.
А так работает?
=СЧЁТ(0/(ПОИСКПОЗ($E$4:$E$415;ЕСЛИ(($F$4:$F$415=L$1)*(ПОИСК($K2;$I$4:$I$415));$E$4:$E$415);)=СТРОКА($E$4:$E$415)-3))


Update:
Вроде решил задачу.
Для L1-M1
=СУММ((($F$4:$F$415=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(L$1;"и";ПОВТОР(" ";99));СТОЛБЕЦ($A$1:ИНДЕКС($A$1:$IS$1;ДЛСТР(ПОДСТАВИТЬ(L$1;"и";"-"))-ДЛСТР(ПОДСТАВИТЬ(L$1;"и";))+1))*99-98;99)-1)+{1})*ЕЧИСЛО(ПОИСК($K2;$I$4:$I$415))/СЧЁТЕСЛИМН($E$4:$E$415;$E$4:$E$415;$F$4:$F$415;$F$4:$F$415)))

Для остальных (с исключением номеров из L1-M1)
=СУММ((($F$4:$F$415=ТРАНСП(ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ($F$4:$F$415;{566\82\738};))*ПОИСКПОЗ($F$4:$F$415;$F$4:$F$415;)=СТРОКА($F$4:$F$415)-3;$F$4:$F$415);СТРОКА($A$1:$A$7));"")))*ЕЧИСЛО(ПОИСК($K2;$I$4:$I$415))/СЧЁТЕСЛИМН($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
 
Ответить
СообщениеВыискивать ошибки формулы в большом объеме данных то еще удовольствие.
А так работает?
=СЧЁТ(0/(ПОИСКПОЗ($E$4:$E$415;ЕСЛИ(($F$4:$F$415=L$1)*(ПОИСК($K2;$I$4:$I$415));$E$4:$E$415);)=СТРОКА($E$4:$E$415)-3))


Update:
Вроде решил задачу.
Для L1-M1
=СУММ((($F$4:$F$415=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(L$1;"и";ПОВТОР(" ";99));СТОЛБЕЦ($A$1:ИНДЕКС($A$1:$IS$1;ДЛСТР(ПОДСТАВИТЬ(L$1;"и";"-"))-ДЛСТР(ПОДСТАВИТЬ(L$1;"и";))+1))*99-98;99)-1)+{1})*ЕЧИСЛО(ПОИСК($K2;$I$4:$I$415))/СЧЁТЕСЛИМН($E$4:$E$415;$E$4:$E$415;$F$4:$F$415;$F$4:$F$415)))

Для остальных (с исключением номеров из L1-M1)
=СУММ((($F$4:$F$415=ТРАНСП(ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ($F$4:$F$415;{566\82\738};))*ПОИСКПОЗ($F$4:$F$415;$F$4:$F$415;)=СТРОКА($F$4:$F$415)-3;$F$4:$F$415);СТРОКА($A$1:$A$7));"")))*ЕЧИСЛО(ПОИСК($K2;$I$4:$I$415))/СЧЁТЕСЛИМН($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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 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
  • Страница 1 из 1
  • 1
Поиск:

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