Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск одинаковых строк в таблице по нескольким условиям, альтернатива СУММЕСЛИМН, ускорить вычисления
 
#1
01.01.2018 16:36:25
Прошу помочь в решении задачи по поиску одинаковых строк в таблице по содержимому ячеек, находящихся в нескольких столбцах.
Задачу решаю, применив формулы СУММЕСЛИМН. Упрощённый пример прилагаю (было и результат). Настоящая таблица на 100 тыс.строк. Если применять к одной таблице одновременно несколько СУММЕСЛИМН (с различным набором условий) - время выполнения расчётов получается около трёх часов.
Подскажите пожалуйста способ решения подобных задач, не затратный по времени. Благодарю!
Изменено: aesp - 02.01.2018 09:47:11
 
 
 
#2
01.01.2018 17:25:54
Вы неправильно использовали формулу.
Код
=СУММЕСЛИМН($X:$X;$C:$C;$C3;$E:$E;$E3;$K:$K;$K3;$L:$L;$L3;$N:$N;$N3;$O:$O;$O3;$R:$R;$R3;$W:$W;$W3)
 
 
 
#3
01.01.2018 17:37:17
oldy7,  вы считаете что убрав 3 и 18 расчет будет быстрее от 3х до 18ти раз?

aesp,  Возможно следует пересмотреть подход, может использовать сводную таблицу....
По вопросам из тем форума, личку не читаю.
 
 
 
#4
01.01.2018 17:47:01
БМВ, нет, но не три часа... Есть вариант, что в оригинальном файле ТС много вкладок и там тоже многоэтажные формулы. Если так , то кроме сводной только макрос (паблик словарь, туда загружаем все данные, потом по одномерному массиву суммируем).
 
 
 
#5
01.01.2018 17:49:49
сомневаюсь...как сводная таблица покажет задвоенные строки?
 
 
 
#6
01.01.2018 17:55:53
Цитата
aesp написал: покажет задвоенные строки?
Давайте поговорим не о подходах, а о вашей главной цели - что из чего, с какой целью вы желаете получить?!
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#7
01.01.2018 18:08:34
aesp, согласен с Z, да собственно к этому и подводил.
  1. Ответ на вопрос темы - функция достаточно оптимизирована,
  2. Зачем использовать доп столбец и сумму - не понятно, если можно просто
  3. =COUNTIFS($C$3:$C$18;$C3;$E$3:$E$18;$E3;$K$3:$K$18;$K3;$L$3:$L$18;$L3;$N$3:$N$18;$N3;$O$3:$O$18;$O3;$R$3:$R$18;$R3;$W$3:$W$18;$W3)
  4. Если нужно оптимизировать поиск дубликатов, то это другая тема.
По вопросам из тем форума, личку не читаю.
 
 
 
#8
01.01.2018 18:10:27
Цитата
Z написал:
что из чего, с какой целью
вроде как описал задачу в первом посте: найти одинаковые строки по нескольким условиям, выбрать их из основной таблицы и поместить например на отдельный лист...
 
 
 
#9
01.01.2018 18:14:57
Цитата
БМВ написал:
это другая тема.
спасибо, однако, ищу альтернативу (отличную от СУММЕСЛИМН) своему способу поиска дубликатов строк :( .
 
 
 
#10
01.01.2018 18:20:56
БМВ, спасибо за наводку на СЧЁТЕСЛИМН. Это упрощает мой способ, но, думаю, не сэкономит время расчётов.
 
 
 
#11
01.01.2018 18:32:05
Цитата
aesp написал: выбрать их из основной таблицы и поместить например на отдельный лист...
Это и есть ваша КОНЕЧНАЯ цель?!
А, мо быть, сделать проще - грохнуть дубли и оставить только уникальные?
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#12
01.01.2018 18:42:08
Цитата
Z написал:
КОНЕЧНАЯ цель
можно и "грохнуть" на основном листе, но "проявить" по одной строке из задвоенных на другой лист. ГЛАВНАЯ задача увеличить скорость этих операций с трёх часов до минут.
 
 
 
#13
01.01.2018 18:48:23
aesp, а почему на втором листе не две строки с данными, а четыре? И почему в столбце О значение 382, а не 477?
 
 
 
#14
01.01.2018 18:56:31
PowerQuery, как вариант.
По вопросам из тем форума, личку не читаю.
 
 
 
#15
01.01.2018 19:22:41
Цитата
Юрий М написал:
а почему на втором листе
оставил четыре строки для наглядности (исправил наименование листа) - потом удалить дубликаты - не проблема.
нет второй строки со значением 477
Изменено: aesp - 01.01.2018 19:27:22
 
 
 
#16
01.01.2018 21:44:43
Может быть задача состоит в том, чтобы вывести УНИКАЛЬНЫЕ строки?
 
 
 
#17
02.01.2018 08:41:15
Юрий М, нет, нужно именно отделить "зерна" от "плевел", оставив на одном листе уникальные и поместив задвоения на другой лист. Нужно не потерять в сумме ни одной строки. Я с помощью СЧЕТЕСЛИМН помечаю строки цифрами 2 и 1. Т.е. те, что задвоились и уникальные. Этого тоже достаточно. Вопрос во времени, необходимой для подсчета!
 
 
 
#18
02.01.2018 10:15:05
aesp,  Не ускорить Ваш метод. Countifss ранее предложенный чуть меньше операций позволит сделать и на какой-то процент время сократится, но глобально -нет. при сотнях тысяч строк набор сравнений очень большой.

Не зная что это за данные трудно судить по этой выборке, но возможно есть поле, два, по которым можно отсечь те записи, что точно не дублируются? для примера по ID
Код
=IF(COUNTIFS($W$3:$W$18;$W3)>1;COUNTIFS($C$3:$C$18;$C3;$E$3:$E$18;$E3;$K$3:$K$18;$K3;$L$3:$L$18;$L3;$N$3:$N$18;$N3;$O$3:$O$18;$O3;$R$3:$R$18;$R3;$W$3:$W$18;$W3);1)
То есть первый проход выделит потенциальные дубликаты, а второй уточнит, но что-то мне подсказывает, что данная оптимизация уже в самой функцмм встроенной есть.

Сделал еще один вариант с использованием PowerQuery.
Изменено: БМВ - 02.01.2018 10:30:09
По вопросам из тем форума, личку не читаю.
 
 
 
#19
02.01.2018 10:44:10
БМВ, спасибо Вам большое. Пошёл вникать в способы анализа с Power Query :D  
 
 
 
Страницы: 1
Наверх