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

Вход

Регистрация

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

 

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

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет уникальных значений самый быстрый способ (Формулы/Formulas)
Подсчет уникальных значений самый быстрый способ
djon2012 Дата: Воскресенье, 04.06.2017, 10:18 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 73
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте!
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN…. Количество строк в оригинале 1000, значения по строкам A…:KN…. постоянно изменяются. Мой вопрос: существует ли более быстрый способ формулами или макросом произвести эти вычисления?
Спасибо!
К сообщению приложен файл: 3763457.xlsb(34Kb)
 
Ответить
СообщениеЗдравствуйте!
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN…. Количество строк в оригинале 1000, значения по строкам A…:KN…. постоянно изменяются. Мой вопрос: существует ли более быстрый способ формулами или макросом произвести эти вычисления?
Спасибо!

Автор - djon2012
Дата добавления - 04.06.2017 в 10:18
jakim Дата: Воскресенье, 04.06.2017, 11:13 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 801
Репутация: 206 ±
Замечаний: 0% ±

Извините, но я не вижу никакой связи между описанием и приложением.
 
Ответить
Сообщение
Извините, но я не вижу никакой связи между описанием и приложением.

Автор - jakim
Дата добавления - 04.06.2017 в 11:13
gling Дата: Воскресенье, 04.06.2017, 11:16 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1622
Репутация: 394 ±
Замечаний: 0% ±

2010
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN….
Здравствуйте. Но в КО пусто.
 
Ответить
Сообщение
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN….
Здравствуйте. Но в КО пусто.

Автор - gling
Дата добавления - 04.06.2017 в 11:16
djon2012 Дата: Воскресенье, 04.06.2017, 11:43 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 73
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Да действительно прикрепил не тот файл, ошипочка вышла, прикрепляю правильный файл. Извините!
К сообщению приложен файл: 2524172.xlsb(94Kb)
 
Ответить
СообщениеДа действительно прикрепил не тот файл, ошипочка вышла, прикрепляю правильный файл. Извините!

Автор - djon2012
Дата добавления - 04.06.2017 в 11:43
Светлый Дата: Воскресенье, 04.06.2017, 15:21 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 293
Репутация: 73 ±
Замечаний: 0% ±

Excel 2007
Добрый день!
Формула массива (Ctrl+Shift+Enter):
Код
=СУММ(--(ПОИСКПОЗ(A1:KN1;A1:KN1;)=СТОЛБЕЦ(A:KN)))

По быстродействию не скажу, надо на больших массивах проверять.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
Формула массива (Ctrl+Shift+Enter):
Код
=СУММ(--(ПОИСКПОЗ(A1:KN1;A1:KN1;)=СТОЛБЕЦ(A:KN)))

По быстродействию не скажу, надо на больших массивах проверять.

Автор - Светлый
Дата добавления - 04.06.2017 в 15:21
djon2012 Дата: Воскресенье, 04.06.2017, 16:55 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 73
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо Светлый! Проверил ваш вариант на своих данных, но увы работает приблизительно на 70 % медленнее. Есть еще варианты?
 
Ответить
СообщениеСпасибо Светлый! Проверил ваш вариант на своих данных, но увы работает приблизительно на 70 % медленнее. Есть еще варианты?

Автор - djon2012
Дата добавления - 04.06.2017 в 16:55
Pelena Дата: Воскресенье, 04.06.2017, 17:11 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11493
Репутация: 2556 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Вариант
Код
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A1:KN1;A1:KN1))


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеВариант
Код
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A1:KN1;A1:KN1))

Автор - Pelena
Дата добавления - 04.06.2017 в 17:11
MCH Дата: Воскресенье, 04.06.2017, 18:12 | Сообщение № 8
Группа: Админы
Ранг: Старожил
Сообщений: 1797
Репутация: 675 ±
Замечаний: ±

вопрос, все числа целые и не более 52?
если да, то можно еще такой вариант:
Код
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(A1:KN1;СТРОКА($1:$53)-1)>0))

или на базе Вашей формулы:
Код
=СУММПРОИЗВ(--(ЧАСТОТА(A1:KN1;СТРОКА($1:$53)-1)>0))

Должно считать немного быстрее, чем формула от Елены из сообщения выше

UPD:
Проверил на большом количестве данных, формула с ЧАСТОТА существенно быстрее СЧЁТЕСЛИ
 
Ответить
Сообщениевопрос, все числа целые и не более 52?
если да, то можно еще такой вариант:
Код
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(A1:KN1;СТРОКА($1:$53)-1)>0))

или на базе Вашей формулы:
Код
=СУММПРОИЗВ(--(ЧАСТОТА(A1:KN1;СТРОКА($1:$53)-1)>0))

Должно считать немного быстрее, чем формула от Елены из сообщения выше

UPD:
Проверил на большом количестве данных, формула с ЧАСТОТА существенно быстрее СЧЁТЕСЛИ

Автор - MCH
Дата добавления - 04.06.2017 в 18:12
djon2012 Дата: Воскресенье, 04.06.2017, 21:48 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 73
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо вам большое Pelena и MCH за формулы. Я проверил их на скорость выполнения, результаты оказались таковы: формула от Pelena в 19! раз медленнее формулы из моего примера, формула от MCH (вариант СЧЕТЕСЛИ) в 4 раза медленнее, формула от MCH (вариант ЧАСТОТА) в 2,1 раза быстрее. Да действительно формула с ЧАСТОТА значительно быстрее формулы СЧЕТЕСЛИ.
Еще раз СПАСИБО за вашу помощь!!! И шо я бы без вас делал??? hands hands hands yes
 
Ответить
СообщениеСпасибо вам большое Pelena и MCH за формулы. Я проверил их на скорость выполнения, результаты оказались таковы: формула от Pelena в 19! раз медленнее формулы из моего примера, формула от MCH (вариант СЧЕТЕСЛИ) в 4 раза медленнее, формула от MCH (вариант ЧАСТОТА) в 2,1 раза быстрее. Да действительно формула с ЧАСТОТА значительно быстрее формулы СЧЕТЕСЛИ.
Еще раз СПАСИБО за вашу помощь!!! И шо я бы без вас делал??? hands hands hands yes

Автор - djon2012
Дата добавления - 04.06.2017 в 21:48
Michael_S Дата: Понедельник, 05.06.2017, 11:43 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1831
Репутация: 343 ±
Замечаний: 0% ±

Excel2016
формулами или макросом

а если так попробовать:
[vba]
Код
Function Уникальные&(Диапазон As Range)
    Dim Ar(), D As Object, i
    Set D = CreateObject("Scripting.Dictionary")
    Ar = Диапазон.Value
    For Each i In Ar
        D(i) = 1
    Next
    Уникальные = D.Count
End Function
[/vba]


ЯД: 41001136675053
WM: R389613894253
 
Ответить
Сообщение
формулами или макросом

а если так попробовать:
[vba]
Код
Function Уникальные&(Диапазон As Range)
    Dim Ar(), D As Object, i
    Set D = CreateObject("Scripting.Dictionary")
    Ar = Диапазон.Value
    For Each i In Ar
        D(i) = 1
    Next
    Уникальные = D.Count
End Function
[/vba]

Автор - Michael_S
Дата добавления - 05.06.2017 в 11:43
djon2012 Дата: Понедельник, 05.06.2017, 15:16 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 73
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Michael_S спасибо за ваш вариант, обязательно попробую и отпишусь!
 
Ответить
СообщениеMichael_S спасибо за ваш вариант, обязательно попробую и отпишусь!

Автор - djon2012
Дата добавления - 05.06.2017 в 15:16
djon2012 Дата: Понедельник, 05.06.2017, 15:43 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 73
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Michael_S ваш вариант очень удобный, я проверил его на своих данных. Условия проведения расчетов одинаковы для всех тестов приведенных выше постами. Скорость выполнения во всех тестах я сравнивал с формулой из моего примера.Ваш вариант с пользовательской функцией в 4,5 раза медленнее чем вариант формулами из моего примера в 1 посту. Спасибо !!!
 
Ответить
СообщениеMichael_S ваш вариант очень удобный, я проверил его на своих данных. Условия проведения расчетов одинаковы для всех тестов приведенных выше постами. Скорость выполнения во всех тестах я сравнивал с формулой из моего примера.Ваш вариант с пользовательской функцией в 4,5 раза медленнее чем вариант формулами из моего примера в 1 посту. Спасибо !!!

Автор - djon2012
Дата добавления - 05.06.2017 в 15:43
pabchek Дата: Вторник, 06.06.2017, 15:13 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 745
Репутация: 168 ±
Замечаний: 0% ±

Excel 2007
Если скорость гораздо более важна компактности и красивости, могу предложить модификацию формулы Михаила ( MCH). Всё то же самое, только прежде создать массив констант в именованных диапазонах
Код
=СУММПРОИЗВ(--(ЧАСТОТА(A1:KN1;массив_-1)>0))

Я пробовал, увеличил Ваш файл до 100тыс. строк эта формула работала в 4 раза быстрее Вашей исходной
К сообщению приложен файл: 3551444.xlsb(95Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 06.06.2017, 15:14
 
Ответить
СообщениеЕсли скорость гораздо более важна компактности и красивости, могу предложить модификацию формулы Михаила ( MCH). Всё то же самое, только прежде создать массив констант в именованных диапазонах
Код
=СУММПРОИЗВ(--(ЧАСТОТА(A1:KN1;массив_-1)>0))

Я пробовал, увеличил Ваш файл до 100тыс. строк эта формула работала в 4 раза быстрее Вашей исходной

Автор - pabchek
Дата добавления - 06.06.2017 в 15:13
Michael_S Дата: Вторник, 06.06.2017, 15:58 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1831
Репутация: 343 ±
Замечаний: 0% ±

Excel2016
Ваш вариант с пользовательской функцией в 4,5 раза медленнее чем вариант формулами из моего примера в 1 посту.

Да, я тоже проверял. На маленьких массивах UDF проигрывает, на больших - в зависимости от количества уникальных.
Вот так должна раза в три быстрее быть
[vba]
Код
Function Уникальные&(Диапазон As Range)
    Dim Ar(), D As Object, i
    Set D = CreateObject("Scripting.Dictionary")
    Ar = Диапазон.Value
    For Each i In Ar
        If Not D.exists(i) Then D(i) = 1
    Next
    Уникальные = D.Count
End Function
[/vba]


ЯД: 41001136675053
WM: R389613894253


Сообщение отредактировал Michael_S - Вторник, 06.06.2017, 15:58
 
Ответить
Сообщение
Ваш вариант с пользовательской функцией в 4,5 раза медленнее чем вариант формулами из моего примера в 1 посту.

Да, я тоже проверял. На маленьких массивах UDF проигрывает, на больших - в зависимости от количества уникальных.
Вот так должна раза в три быстрее быть
[vba]
Код
Function Уникальные&(Диапазон As Range)
    Dim Ar(), D As Object, i
    Set D = CreateObject("Scripting.Dictionary")
    Ar = Диапазон.Value
    For Each i In Ar
        If Not D.exists(i) Then D(i) = 1
    Next
    Уникальные = D.Count
End Function
[/vba]

Автор - Michael_S
Дата добавления - 06.06.2017 в 15:58
djon2012 Дата: Вторник, 06.06.2017, 18:09 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 73
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте!
Спасибо за Ваши варианты Michael_S и pabchek, я проверил их на своих данных. Результаты следующие: Ваша пользовательская функция Michael_S в сравнении с изложенной 4 постами выше по скорости осталась почти такой же, если точнее то приблизительно на 5% быстрее, Ваша pabchek по скорости такая же как формула от MCH с функцией ЧАСТОТА.
Спасибо!!!
 
Ответить
СообщениеЗдравствуйте!
Спасибо за Ваши варианты Michael_S и pabchek, я проверил их на своих данных. Результаты следующие: Ваша пользовательская функция Michael_S в сравнении с изложенной 4 постами выше по скорости осталась почти такой же, если точнее то приблизительно на 5% быстрее, Ваша pabchek по скорости такая же как формула от MCH с функцией ЧАСТОТА.
Спасибо!!!

Автор - djon2012
Дата добавления - 06.06.2017 в 18:09
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет уникальных значений самый быстрый способ (Формулы/Formulas)
Страница 1 из 11
Поиск:

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