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

Вход

Регистрация

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

 

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

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

Excel 2010
Здравствуйте!
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN…. Количество строк в оригинале 1000, значения по строкам A…:KN…. постоянно изменяются. Мой вопрос: существует ли более быстрый способ формулами или макросом произвести эти вычисления?
Спасибо!
К сообщению приложен файл: 3763457.xlsb (33.7 Kb)
 
Ответить
СообщениеЗдравствуйте!
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN…. Количество строк в оригинале 1000, значения по строкам A…:KN…. постоянно изменяются. Мой вопрос: существует ли более быстрый способ формулами или макросом произвести эти вычисления?
Спасибо!
Автор - djon2012
Дата добавления - 04.06.2017 в 10:18
jakim Дата: Воскресенье, 04.06.2017, 11:13 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

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

2010
Цитата djon2012, 04.06.2017 в 10:18, в сообщении № 1 ()
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN….
Здравствуйте. Но в КО пусто.

ЯД-41001506838083
 
Ответить
Сообщение
Цитата djon2012, 04.06.2017 в 10:18, в сообщении № 1 ()
В моем примере в столбце KO формулами вычисляется количество уникальных значений по строкам A…:KN….
Здравствуйте. Но в КО пусто.
Автор - gling
Дата добавления - 04.06.2017 в 11:16
djon2012 Дата: Воскресенье, 04.06.2017, 11:43 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Да действительно прикрепил не тот файл, ошипочка вышла, прикрепляю правильный файл. Извините!
К сообщению приложен файл: 2524172.xlsb (94.2 Kb)
 
Ответить
СообщениеДа действительно прикрепил не тот файл, ошипочка вышла, прикрепляю правильный файл. Извините!
Автор - djon2012
Дата добавления - 04.06.2017 в 11:43
Светлый Дата: Воскресенье, 04.06.2017, 15:21 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
Формула массива (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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо Светлый! Проверил ваш вариант на своих данных, но увы работает приблизительно на 70 % медленнее. Есть еще варианты?
 
Ответить
СообщениеСпасибо Светлый! Проверил ваш вариант на своих данных, но увы работает приблизительно на 70 % медленнее. Есть еще варианты?
Автор - djon2012
Дата добавления - 04.06.2017 в 16:55
Pelena Дата: Воскресенье, 04.06.2017, 17:11 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19176
Репутация: 4413 ±
Замечаний: ±

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

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВариант
Код
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A1:KN1;A1:KN1))
Автор - Pelena
Дата добавления - 04.06.2017 в 17:11
MCH Дата: Воскресенье, 04.06.2017, 18:12 | Сообщение № 8
Группа: Админы
Ранг: Старожил
Сообщений: 2003
Репутация: 751 ±
Замечаний: ±

вопрос, все числа целые и не более 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 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
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Цитата djon2012, 04.06.2017 в 10:18, в сообщении № 1 ()
формулами или макросом

а если так попробовать:
[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]
 
Ответить
Сообщение
Цитата djon2012, 04.06.2017 в 10:18, в сообщении № 1 ()
формулами или макросом

а если так попробовать:
[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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Michael_S спасибо за ваш вариант, обязательно попробую и отпишусь!
 
Ответить
СообщениеMichael_S спасибо за ваш вариант, обязательно попробую и отпишусь!
Автор - djon2012
Дата добавления - 05.06.2017 в 15:16
djon2012 Дата: Понедельник, 05.06.2017, 15:43 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

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

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

"Учиться, учиться и еще раз учиться!"
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
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Цитата djon2012, 05.06.2017 в 15:43, в сообщении № 12 ()
Ваш вариант с пользовательской функцией в 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, 05.06.2017 в 15:43, в сообщении № 12 ()
Ваш вариант с пользовательской функцией в 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 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 из 1
  • 1
Поиск:

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