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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчёт уникальных значений из разных диапазонов по условию - Мир MS Excel

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

Excel 2019
Здравствуйте!

Прошу помочь с решением задачи. Решение должно работать без макросов и VBA, только с помощью формул.
Решение может работать в версии Ексель 2019 или более ранних версий.
Мне необходимо производить подсчёт количества ячеек с уникальными значениями из нескольких диапазонов, критерий подсчётов приводится в отдельном диапазоне.
вывод в отдельную ячейку.

Для примера: мне нужно посчитать количество уникальных значений во всех диапазонах для которых критерий по строке равен "1".

Заранее благодарю за помощь!
К сообщению приложен файл: 4834178.xlsx(9.2 Kb)


Сообщение отредактировал SoulKeeper - Среда, 26.02.2020, 10:50
 
Ответить
СообщениеЗдравствуйте!

Прошу помочь с решением задачи. Решение должно работать без макросов и VBA, только с помощью формул.
Решение может работать в версии Ексель 2019 или более ранних версий.
Мне необходимо производить подсчёт количества ячеек с уникальными значениями из нескольких диапазонов, критерий подсчётов приводится в отдельном диапазоне.
вывод в отдельную ячейку.

Для примера: мне нужно посчитать количество уникальных значений во всех диапазонах для которых критерий по строке равен "1".

Заранее благодарю за помощь!

Автор - SoulKeeper
Дата добавления - 26.02.2020 в 10:50
dude Дата: Среда, 26.02.2020, 15:24 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
что значит уникальные?
отдельно для каждой строки или для всех строк с этим параметром?
 
Ответить
Сообщениечто значит уникальные?
отдельно для каждой строки или для всех строк с этим параметром?

Автор - dude
Дата добавления - 26.02.2020 в 15:24
SoulKeeper Дата: Среда, 26.02.2020, 16:40 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
dude, для всех строк с параметром, если бы для неразрывного диапазона, то есть уже масса готовых решений.
Мне нужно вычислить количество уникальных текстовых значений, например, для всех строк с параметром 1.
 
Ответить
Сообщениеdude, для всех строк с параметром, если бы для неразрывного диапазона, то есть уже масса готовых решений.
Мне нужно вычислить количество уникальных текстовых значений, например, для всех строк с параметром 1.

Автор - SoulKeeper
Дата добавления - 26.02.2020 в 16:40
krosav4ig Дата: Среда, 26.02.2020, 17:08 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Вариант через Power Query + сводная таблица
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Цвет1", type text}, {"Цвет2", type text}, {"Цвет3", type text}, {"Цвет4", type text}, {"Цвет5", type text}, {"Цвет6", type text}, {"Цвет7", type text}, {"Цвет8", type any}, {"Цвет9", type any}, {"Цвет10", type any}, {"Цвет11", type any}, {"Цвет12", type any}, {"Параметр1", Int64.Type}}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Измененный тип", {"Параметр1"}, "Атрибут", "Значение")
in
    #"Другие столбцы с отмененным свертыванием"
[/vba]
К сообщению приложен файл: 4834178.zip(43.9 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеВариант через Power Query + сводная таблица
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Цвет1", type text}, {"Цвет2", type text}, {"Цвет3", type text}, {"Цвет4", type text}, {"Цвет5", type text}, {"Цвет6", type text}, {"Цвет7", type text}, {"Цвет8", type any}, {"Цвет9", type any}, {"Цвет10", type any}, {"Цвет11", type any}, {"Цвет12", type any}, {"Параметр1", Int64.Type}}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Измененный тип", {"Параметр1"}, "Атрибут", "Значение")
in
    #"Другие столбцы с отмененным свертыванием"
[/vba]

Автор - krosav4ig
Дата добавления - 26.02.2020 в 17:08
SoulKeeper Дата: Среда, 26.02.2020, 17:23 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
krosav4ig, даже проверить не могу так как не разбираюсь в этой теме. Четкое условия только формулами.
 
Ответить
Сообщениеkrosav4ig, даже проверить не могу так как не разбираюсь в этой теме. Четкое условия только формулами.

Автор - SoulKeeper
Дата добавления - 26.02.2020 в 17:23
krosav4ig Дата: Среда, 26.02.2020, 17:54 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
массивная формула
Код
=СУММ(ЕСЛИОШИБКА(1/МУМНОЖ(--(ИНДЕКС(ЕСЛИ(Лист1!$M$2:$M$25=O2;Лист1!$A$2:$L$25;);Ч(ИНДЕКС(ОСТАТ((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1);ЧСТРОК(Лист1!$A$2:$M$25))+1;0));Ч(ИНДЕКС(ОТБР((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1)/ЧСТРОК(Лист1!$A$2:$M$25))+1;0)))=ТРАНСП(Т(+ИНДЕКС(ЕСЛИ(Лист1!$M$2:$M$25=O2;Лист1!$A$2:$L$25;);Ч(ИНДЕКС(ОСТАТ((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1);ЧСТРОК(Лист1!$A$2:$M$25))+1;0));Ч(ИНДЕКС(ОТБР((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1)/ЧСТРОК(Лист1!$A$2:$M$25))+1;0))))));СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))^0);))
К сообщению приложен файл: 2091223.zip(47.0 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениемассивная формула
Код
=СУММ(ЕСЛИОШИБКА(1/МУМНОЖ(--(ИНДЕКС(ЕСЛИ(Лист1!$M$2:$M$25=O2;Лист1!$A$2:$L$25;);Ч(ИНДЕКС(ОСТАТ((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1);ЧСТРОК(Лист1!$A$2:$M$25))+1;0));Ч(ИНДЕКС(ОТБР((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1)/ЧСТРОК(Лист1!$A$2:$M$25))+1;0)))=ТРАНСП(Т(+ИНДЕКС(ЕСЛИ(Лист1!$M$2:$M$25=O2;Лист1!$A$2:$L$25;);Ч(ИНДЕКС(ОСТАТ((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1);ЧСТРОК(Лист1!$A$2:$M$25))+1;0));Ч(ИНДЕКС(ОТБР((СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))-1)/ЧСТРОК(Лист1!$A$2:$M$25))+1;0))))));СТРОКА($A$1:ИНДЕКС(A:A;ЧСТРОК(Лист1!$A$2:$M$25)*(ЧИСЛСТОЛБ(Лист1!$A$2:$M$25)-1)))^0);))

Автор - krosav4ig
Дата добавления - 26.02.2020 в 17:54
Светлый Дата: Среда, 26.02.2020, 20:26 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1742
Репутация: 478 ±
Замечаний: 0% ±

Excel 2013, 2016
Мой вариант. Тоже массивная:
Код
=СЧЁТ(1/(ПОИСКПОЗ(Т(СМЕЩ(A1;Ч(ИНДЕКС(СТРОКА(12:299)/12;))+99*(ИНДЕКС(M2:M25;Ч(ИНДЕКС(СТРОКА(12:299)/12;)))<>N1);Ч(ИНДЕКС(ОСТАТ(СТРОКА(12:299);12);))));Т(СМЕЩ(A1;Ч(ИНДЕКС(СТРОКА(12:299)/12;))+99*(ИНДЕКС(M2:M25;Ч(ИНДЕКС(СТРОКА(12:299)/12;)))<>N1);Ч(ИНДЕКС(ОСТАТ(СТРОКА(12:299);12);))));)=СТРОКА(12:299)-11))-1
Пока не оптимизированная.
*Исправил диапазон.
**В N1 искомая 1.


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

Сообщение отредактировал Светлый - Среда, 26.02.2020, 21:07
 
Ответить
СообщениеМой вариант. Тоже массивная:
Код
=СЧЁТ(1/(ПОИСКПОЗ(Т(СМЕЩ(A1;Ч(ИНДЕКС(СТРОКА(12:299)/12;))+99*(ИНДЕКС(M2:M25;Ч(ИНДЕКС(СТРОКА(12:299)/12;)))<>N1);Ч(ИНДЕКС(ОСТАТ(СТРОКА(12:299);12);))));Т(СМЕЩ(A1;Ч(ИНДЕКС(СТРОКА(12:299)/12;))+99*(ИНДЕКС(M2:M25;Ч(ИНДЕКС(СТРОКА(12:299)/12;)))<>N1);Ч(ИНДЕКС(ОСТАТ(СТРОКА(12:299);12);))));)=СТРОКА(12:299)-11))-1
Пока не оптимизированная.
*Исправил диапазон.
**В N1 искомая 1.

Автор - Светлый
Дата добавления - 26.02.2020 в 20:26
dude Дата: Среда, 26.02.2020, 21:16 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
Код
=SUM(IF(FREQUENCY(IF(($M$2:$M$25=O2)*($A$2:$L$25>0),MATCH($A$2:$L$25,$Q$1:$AH$1)),$Q$2:$AH$2)>0,1))

с доп строкой
К сообщению приложен файл: 9705902.xlsx(9.7 Kb)
 
Ответить
Сообщение
Код
=SUM(IF(FREQUENCY(IF(($M$2:$M$25=O2)*($A$2:$L$25>0),MATCH($A$2:$L$25,$Q$1:$AH$1)),$Q$2:$AH$2)>0,1))

с доп строкой

Автор - dude
Дата добавления - 26.02.2020 в 21:16
SoulKeeper Дата: Понедельник, 02.03.2020, 12:09 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
krosav4ig, у меня голова лопается от такой формулы :(, хотя она и работает. Не хватает мне мозгов оптимизировать этого монстра под мою задачу.
 
Ответить
Сообщениеkrosav4ig, у меня голова лопается от такой формулы :(, хотя она и работает. Не хватает мне мозгов оптимизировать этого монстра под мою задачу.

Автор - SoulKeeper
Дата добавления - 02.03.2020 в 12:09
alexa1965 Дата: Понедельник, 02.03.2020, 13:30 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 260
Репутация: 20 ±
Замечаний: 0% ±

2003> 2010> 2016
простым счет если много, только в столбце вывод стоят искомые значения параметра
Код
=СЧЁТЕСЛИМН(M$2:M$25;O2;A$2:A$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;B$2:B$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;C$2:C$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;D$2:D$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;E$2:E$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;F$2:F$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;G$2:G$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;H$2:H$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;I$2:I$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;J$2:J$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;K$2:K$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;L$2:L$25;"*?")
К сообщению приложен файл: 5223426.xlsx(9.4 Kb)


Главное не быть балабастиком
 
Ответить
Сообщениепростым счет если много, только в столбце вывод стоят искомые значения параметра
Код
=СЧЁТЕСЛИМН(M$2:M$25;O2;A$2:A$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;B$2:B$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;C$2:C$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;D$2:D$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;E$2:E$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;F$2:F$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;G$2:G$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;H$2:H$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;I$2:I$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;J$2:J$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;K$2:K$25;"*?")+СЧЁТЕСЛИМН(M$2:M$25;O2;L$2:L$25;"*?")

Автор - alexa1965
Дата добавления - 02.03.2020 в 13:30
SoulKeeper Дата: Понедельник, 02.03.2020, 13:38 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
alexa1965, так считаются все значения по условию, но не уникальные.
 
Ответить
Сообщениеalexa1965, так считаются все значения по условию, но не уникальные.

Автор - SoulKeeper
Дата добавления - 02.03.2020 в 13:38
alexa1965 Дата: Понедельник, 02.03.2020, 13:41 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 260
Репутация: 20 ±
Замечаний: 0% ±

2003> 2010> 2016
но не уникальные

Да... согласен, - не прав


Главное не быть балабастиком
 
Ответить
Сообщение
но не уникальные

Да... согласен, - не прав

Автор - alexa1965
Дата добавления - 02.03.2020 в 13:41
SoulKeeper Дата: Воскресенье, 12.04.2020, 12:21 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Коллеги, я сдаюсь!
Предложите, пожалуйста, решение в виде отдельной пользовательской функции.
Аргументами функции должны быть диапазон массива и столбец в котором находятся условия поиска.
 
Ответить
СообщениеКоллеги, я сдаюсь!
Предложите, пожалуйста, решение в виде отдельной пользовательской функции.
Аргументами функции должны быть диапазон массива и столбец в котором находятся условия поиска.

Автор - SoulKeeper
Дата добавления - 12.04.2020 в 12:21
SoulKeeper Дата: Суббота, 18.04.2020, 19:20 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Коллеги, я помню что кто-то хотел предложить вариант функции на VBA. С большим почтением отнесусь к подобному предложению решения этой проблемы.


Сообщение отредактировал SoulKeeper - Суббота, 18.04.2020, 19:20
 
Ответить
СообщениеКоллеги, я помню что кто-то хотел предложить вариант функции на VBA. С большим почтением отнесусь к подобному предложению решения этой проблемы.

Автор - SoulKeeper
Дата добавления - 18.04.2020 в 19:20
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт уникальных значений из разных диапазонов по условию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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