Страницы: 1
RSS
Подсчитать количество уникальных значений в диапазоне.
 
Задача: найти количество уникальных значений в диапазоне столбца. Скрин ниже показывает, условно: в первом - 3 (1,2,в), во втором - 2 (1,в), в третьем - 1 (2).
Имеется такое решение:
Код
{=СУММ(ЕСЛИ(ЕПУСТО(A2:A100000);0;1/СЧЁТЕСЛИ(A2:A100000;A2:A100000)))}

Но не устраивает скорость выполнения на больших диапазонах и слабеньких компьютерах. Пересчет замедляет внесение изменений в диапазоны или наоборот. Решение взято отсюда: https://www.planetaexcel.ru/techniques/14/187/. Требуется найти альтернативное решение. Прошу помощи.

И как убрать сообщение в H2: "формула в этой ячейке ссылается на диапазон, к которому прилегают другие значения"?  
Изменено: НСС - 08.11.2019 16:13:11
 
вариант на PQ
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    id = Table.AddColumn(Источник, "id", each 1),
    unpivot = Table.UnpivotOtherColumns(id, {"id"}, "atrib", "value"),
    type_change = Table.TransformColumnTypes(unpivot,{{"value", type text}}),
    count_distinct = Table.Group(type_change, {"atrib"}, {"tmp", each List.Count(List.Distinct([value]))}),
    result = Table.Transpose(count_distinct),
    #"Повышенные заголовки" = Table.PromoteHeaders(result, [PromoteAllScalars=true]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in
    #"Измененный тип"
 
Мне кажется проще вот такой формулой: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A100000;A2:A100000&""))-1
По времени отрабатывает быстро.
Изменено: Ametist69 - 08.11.2019 12:17:16
 
Массивный вариант:
Код
=СЧЁТ(1/(ПОИСКПОЗ(A2:A100000;A2:A100000;)=СТРОКА(1:99999)))
По быстродействию не могу сказать, но теоретически должно быстрее работать.
 
Ametist69 Светлый большое спасибо. Но на 100 000 ячеек все равно притормаживает. Тестил на одном столбце. Если их будет множество - беда. Stics тоже спасибо за подсказку. Но это не совсем подходит.  
 
НСС,
А чем Вас не устроил вариант с доп. столбцом, опубликованный в том же Приеме (Способ 1)? В Вашем примере нет пропущенных строк...
 
Не понятно, о каком варианте идет речь. Если о варианте на PQ, то он мне не подходит, потому что я его не понимаю и поэтому не смогу его адаптировать под свои нужды.  
Изменено: НСС - 10.11.2019 20:09:23
 
НСС, Если вам нужна формула, то быстрее не будет. UDF можно написать. и вариантов полно, от коллекций и словарей до элементарного SQL внутри. Но макросы должны быть включены.
По вопросам из тем форума, личку не читаю.
 
Иллюстрация от Microsoft к сообщению Михаила  - строго по теме Вашей задачи. См. раздел "Dynamic count unique" по ссылке. Для отсортированных диапазонов чемпион - формулы с дополнительным столбцом, серебряный призер - UDF. Для неотсортированных UDF не имеет конкурентов.
Изменено: sokol92 - 08.11.2019 20:35:13
Владимир
 
Цитата
БМВ написал:
Но макросы должны быть включены.
- не обязательно, если код в надстройке.
 
но надстройка должна быть включена :-)
По вопросам из тем форума, личку не читаю.
 
Доброе время суток
Цитата
Stics написал:
вариант на PQ
Ещё один вариант на Power Query
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    result = List.Transform(Table.ColumnNames(Source), each [name = _, uniqueCount = List.Count(List.RemoveNulls(List.Distinct(Table.Column(Source, _))))])
in
    Table.FromRecords(result)
 
НСС,
давайте для начала с Вашего языка на русский переведем понятие "уникальные значения"
как только станет понятным что Вы вкладываете в это словосочетание - тут же станет понятно как из сосчитать
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, судя по скрину, надо посчитать не уникальные значения, а каждое значение по одному разу. не зависимо один раз это значение присутствует  или повторяется несколько раз
Изменено: artyrH - 10.11.2019 20:18:13
 
artyrH,
Цитата
судя по скрину, надо посчитать не уникальные значения
а вот судя по названию темы:
Цитата
Подсчитать количество уникальных значений в диапазоне
посчитать-таки нужно уникальные
и вот когда название темы не совпадает со скрином - я не совсем понимаю кто что считает
интерпретируя слова и скрины, мы рискуем довольно далеко уйти от сути задачи
хотелось бы услышать определение от НСС, а не Вашу интерпретацию, при всем уважении, извините
понимаю, что любой здравомыслящий человек засомневается:
1. Как какой-то Владимир Иванович Даль собрал "Толковый словарь живого великорусского языка", не имея доступа к интернету? КАК???
2. может то, что было уникальным на момент составления словаря Далем, стало обыденным в условиях современной России и не несет былого смысла
Изменено: Ігор Гончаренко - 10.11.2019 21:07:45
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Ігор Гончаренко написал:
не имея доступа к интернету? КАК???
мне аж вспомнился фильм Игры разумов (2018)
Страницы: 1
Наверх