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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск уникальных и поиск заполненных - Мир MS Excel

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

2021
Добрый день! Нужна помощь. Таблица очень большая, что бы понять как это сделать состряпал к примеру что требуется. Есть громадная таблица с данными, не обходима табличка результатов, что бы не фильтровать постоянно. В столбце "B" должно отображаться сколько уникальных значений из столбца "Е" найдет в совпадениях A и D. К примеру у "черный" из столбца А получится результат 5 в столбце B, т.к. в столбце Е находится 5 уникальных значений напротив "черный" из столбца D. Аналогично в столбце "С" должно отображаться количество заполненных ячеек, а не уникальных. Заранее большое человеческое спасибо.
К сообщению приложен файл: 5049289.xlsx (11.2 Kb)
 
Ответить
СообщениеДобрый день! Нужна помощь. Таблица очень большая, что бы понять как это сделать состряпал к примеру что требуется. Есть громадная таблица с данными, не обходима табличка результатов, что бы не фильтровать постоянно. В столбце "B" должно отображаться сколько уникальных значений из столбца "Е" найдет в совпадениях A и D. К примеру у "черный" из столбца А получится результат 5 в столбце B, т.к. в столбце Е находится 5 уникальных значений напротив "черный" из столбца D. Аналогично в столбце "С" должно отображаться количество заполненных ячеек, а не уникальных. Заранее большое человеческое спасибо.
Автор - litikovse
Дата добавления - 19.09.2021 в 06:11
Egyptian Дата: Воскресенье, 19.09.2021, 06:47 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
При снятом фильтре в ячейку В3 и вниз:
Код
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET($E$3;ROW($E$3:$E$500)-ROW($E$3);;1));IF(($D$3:$D$500=$A7)*($E$3:$E$500<>"");MATCH("~"&$E$3:$E$500;$E$3:$E$500&"";0)));ROW($E$3:$E$500)-ROW($E$3)+1);1))

В ячейку С3 и вниз:
Код
=SUBTOTAL(3;$F$3:$F$500)
К сообщению приложен файл: 2979898.xlsx (11.5 Kb)
 
Ответить
СообщениеПри снятом фильтре в ячейку В3 и вниз:
Код
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET($E$3;ROW($E$3:$E$500)-ROW($E$3);;1));IF(($D$3:$D$500=$A7)*($E$3:$E$500<>"");MATCH("~"&$E$3:$E$500;$E$3:$E$500&"";0)));ROW($E$3:$E$500)-ROW($E$3)+1);1))

В ячейку С3 и вниз:
Код
=SUBTOTAL(3;$F$3:$F$500)
Автор - Egyptian
Дата добавления - 19.09.2021 в 06:47
litikovse Дата: Воскресенье, 19.09.2021, 06:56 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

2021
Egyptian, Столбец "В" работает как надо, спасибо. А вот в столбце "С" промежуточные итоги считаются в общем, а мне надо что у каждого цвета без фильтра считалось (извиняюсь зря фильтр наверное оставил). Спасибо.
 
Ответить
СообщениеEgyptian, Столбец "В" работает как надо, спасибо. А вот в столбце "С" промежуточные итоги считаются в общем, а мне надо что у каждого цвета без фильтра считалось (извиняюсь зря фильтр наверное оставил). Спасибо.
Автор - litikovse
Дата добавления - 19.09.2021 в 06:56
Egyptian Дата: Воскресенье, 19.09.2021, 07:04 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
litikovse, Ну тогда как-то так:
Без фильтра в C3 и вниз.
Код
=COUNTIFS($F$3:$F$500;"<>";$D$3:$D$500;A3)


Сообщение отредактировал Egyptian - Воскресенье, 19.09.2021, 07:21
 
Ответить
Сообщениеlitikovse, Ну тогда как-то так:
Без фильтра в C3 и вниз.
Код
=COUNTIFS($F$3:$F$500;"<>";$D$3:$D$500;A3)
Автор - Egyptian
Дата добавления - 19.09.2021 в 07:04
litikovse Дата: Воскресенье, 19.09.2021, 07:06 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

2021
Egyptian, Большое спасибо.
 
Ответить
СообщениеEgyptian, Большое спасибо.
Автор - litikovse
Дата добавления - 19.09.2021 в 07:06
litikovse Дата: Воскресенье, 19.09.2021, 08:41 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

2021
Egyptian, а можно сделать так что бы в столбце "С" отображалась иная информация? Допустим если напротив у одного черного из столбца "D" есть повторяющиеся значения в столбце "E" , но в столбце F напротив одного из повторяющегося будет пустая ячейка, то тогда они не суммировались в столбце "с". Может в файле понятнее будет.
К сообщению приложен файл: 3809096.xlsx (11.9 Kb)
 
Ответить
СообщениеEgyptian, а можно сделать так что бы в столбце "С" отображалась иная информация? Допустим если напротив у одного черного из столбца "D" есть повторяющиеся значения в столбце "E" , но в столбце F напротив одного из повторяющегося будет пустая ячейка, то тогда они не суммировались в столбце "с". Может в файле понятнее будет.
Автор - litikovse
Дата добавления - 19.09.2021 в 08:41
Egyptian Дата: Воскресенье, 19.09.2021, 09:41 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
Т.е не считать если хотя бы одна ячейка пустая?
В С3 и вниз
Код
=IF(COUNTIFS($F$3:$F$500;"";$D$3:$D$500;A3)>0;"";COUNTIFS($F$3:$F$500;"<>";$D$3:$D$500;A3))
 
Ответить
СообщениеТ.е не считать если хотя бы одна ячейка пустая?
В С3 и вниз
Код
=IF(COUNTIFS($F$3:$F$500;"";$D$3:$D$500;A3)>0;"";COUNTIFS($F$3:$F$500;"<>";$D$3:$D$500;A3))
Автор - Egyptian
Дата добавления - 19.09.2021 в 09:41
litikovse Дата: Воскресенье, 19.09.2021, 14:37 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

2021
Egyptian, Нет. вот так расписал в таблице, незнаю как иначе объяснить, но вроде должно быть понятно
К сообщению приложен файл: 1783837.xlsx (12.6 Kb)
 
Ответить
СообщениеEgyptian, Нет. вот так расписал в таблице, незнаю как иначе объяснить, но вроде должно быть понятно
Автор - litikovse
Дата добавления - 19.09.2021 в 14:37
Egyptian Дата: Воскресенье, 19.09.2021, 15:43 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
Еще попытка, хотя боюсь я утратил логическую нить((
Код
=SUM(IFERROR(IF(($D$3:$D$500=A3);1/COUNTIFS($E$3:$E$500;$E$3:$E$500;$F$3:$F$500;"";$D$3:$D$500;$D$3:$D$500));""))
 
Ответить
СообщениеЕще попытка, хотя боюсь я утратил логическую нить((
Код
=SUM(IFERROR(IF(($D$3:$D$500=A3);1/COUNTIFS($E$3:$E$500;$E$3:$E$500;$F$3:$F$500;"";$D$3:$D$500;$D$3:$D$500));""))
Автор - Egyptian
Дата добавления - 19.09.2021 в 15:43
litikovse Дата: Воскресенье, 19.09.2021, 15:57 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

2021
Egyptian, В "С3" дал нужный результат, но при протягивание нето
 
Ответить
СообщениеEgyptian, В "С3" дал нужный результат, но при протягивание нето
Автор - litikovse
Дата добавления - 19.09.2021 в 15:57
Egyptian Дата: Воскресенье, 19.09.2021, 20:47 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
litikovse, Заполните пожалуйста вручную, чтобы я хоть как-то сориентировался.
 
Ответить
Сообщениеlitikovse, Заполните пожалуйста вручную, чтобы я хоть как-то сориентировался.
Автор - Egyptian
Дата добавления - 19.09.2021 в 20:47
litikovse Дата: Понедельник, 20.09.2021, 06:17 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

2021
Egyptian, Приложил файл. Номера повторяться будут только у одного цвета, у разных цветов не будет повторяться номер.
К сообщению приложен файл: 8477882.xlsx (13.6 Kb)
 
Ответить
СообщениеEgyptian, Приложил файл. Номера повторяться будут только у одного цвета, у разных цветов не будет повторяться номер.
Автор - litikovse
Дата добавления - 20.09.2021 в 06:17
Egyptian Дата: Среда, 22.09.2021, 12:41 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
litikovse, Чтобы вы не подумали, что я просто слился, к сожалению, решение не нашел. Может кто-нибудь заинтересуется и найдет его.

Update.
Смотрю на решение ниже и думаю, что был гораздо ближе, чем предполагал))


Сообщение отредактировал Egyptian - Четверг, 23.09.2021, 11:57
 
Ответить
Сообщениеlitikovse, Чтобы вы не подумали, что я просто слился, к сожалению, решение не нашел. Может кто-нибудь заинтересуется и найдет его.

Update.
Смотрю на решение ниже и думаю, что был гораздо ближе, чем предполагал))
Автор - Egyptian
Дата добавления - 22.09.2021 в 12:41
Светлый Дата: Четверг, 23.09.2021, 09:29 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Попробуйте эту формулу массива:
Код
=СУММ(ЕСЛИ(СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"")=0;1/СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"<>"))*(D$3:D$37=A3))
У Вас в строке 23 26 написано Считается, а не должно.
*Формулу исправил.
**Или так:
Код
=СУММ(ЕСЛИ(СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"");;(D$3:D$37=A3)/СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"<>")))
К сообщению приложен файл: 8477882-1.xlsx (14.6 Kb)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 23.09.2021, 10:09
 
Ответить
СообщениеПопробуйте эту формулу массива:
Код
=СУММ(ЕСЛИ(СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"")=0;1/СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"<>"))*(D$3:D$37=A3))
У Вас в строке 23 26 написано Считается, а не должно.
*Формулу исправил.
**Или так:
Код
=СУММ(ЕСЛИ(СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"");;(D$3:D$37=A3)/СЧЁТЕСЛИМН(D$3:D$37;D$3:D$37;E$3:E$37;E$3:E$37;F$3:F$37;"<>")))
Автор - Светлый
Дата добавления - 23.09.2021 в 09:29
litikovse Дата: Пятница, 24.09.2021, 12:31 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

2021
Светлый, Громадное спасибо!!! Да, в строке 26 ошибся, извиняюсь. Еще раз спасибо.
 
Ответить
СообщениеСветлый, Громадное спасибо!!! Да, в строке 26 ошибся, извиняюсь. Еще раз спасибо.
Автор - litikovse
Дата добавления - 24.09.2021 в 12:31
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск уникальных и поиск заполненных (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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