Страницы: 1 2 След.
RSS
Сравнение значений в таблице по нескольким условиям
 
Всем здравствуйте!
Помогите пожалуйста справиться с задачей либо формулами либо макросами, не смог найти решения.
Суть такая:
есть две таблицы в каждой по 5 столбцов
Таблица 1
12 3 4 5
67 89 10
Таблица 2
54 3 2 1
109 8 7 6
57 11 10 9
35 9 10 1
необходимо сверить каждую строку таблицы 1 со строками таблицы 2 и найти одинаковые значения, причем в каждой строке нужно сверять все ячейки, чтобы независимо от порядка расположения значений в ячейках таблицы 2 он находил их.
т.е. если в таблице 1, в пяти ячейках идет порядок 1 2 3 4 5, а во второй таблице этот порядок будет 5 4 3 2 1 или 5 3 1 4 2, он считает их равными.
И еще один момент, в каждой строке не могут повторяться значения, т.е в каждой строке все 5 значений будут разными.
Всем заранее благодарен за помощь
Изменено: pvolodya - 13.09.2018 23:51:03
 
Цитата
pvolodya написал:
есть две таблицы
Где эти таблицы? Никто их не видит. Почитайте правила и прикрепите файл-пример.
 
Нужен ваш файл-пример с исходными данными и ожидаемым результатом.
Алексей М.
 
Спасибо, хороший вариант, гораздо проще чем мой

Казанский, cgасибо, тоже интересный вариант, о таком я не подумал бы даже, я сначала хотел просто суммировать, но при простом суммировании, могут значения совпасть, а Ваш способ думают тоже подойдет

Одно решение нашел, но там слишком много действий нужно сделать.
Но там нужно колдовать со второй таблицей сначала, выставлять значения все по порядку возрастания. Это долгий путь
 
Цитата
pvolodya написал:
выставлять значения все по порядку возрастания
Это можно сделать внутри формулы, а потом суммировать с разными коэффициентами:
Код
=СУММПРОИЗВ(НАИМЕНЬШИЙ(A2:E2;{1:2:3:4:5});100^{1:2:3:4:5})
Маловероятно, что числа, полученные таким способом, совпадут для разных строк.
 
Еще вариант решения
Код
=ТЕКСТ(СУММ(Ч(МУМНОЖ(СЧЁТЕСЛИ(A2:E2;I$2:M$9);{1:1:1:1:1})=5));"1;;")
Алексей М.
 
Я как вариант рассматривал использование наименьший, чтоб выстроить во второй таблице все цифры в порядке по возрастанию, ну а дальше объединить отдельно в первой таблице значения и во второй и пробежаться по значениям с помощью впр
 
Цитата
АlехМ написал:
Еще вариант решенияКод ? 1=ТЕКСТ(СУММ(Ч(МУМНОЖ(СЧЁТЕСЛИ(A2:E2;I$2:M$9);{1:1:1:1:1})=5));"1;;")
Спасибо, хороший вариант, гораздо проще чем мой
 
Спасибо, хороший вариант, гораздо проще чем мой

Казанский, cgасибо, тоже интересный вариант, о таком я не подумал бы даже, я сначала хотел просто суммировать, но при простом суммировании, могут значения совпасть, а Ваш способ думают тоже подойдет
 
Таблица 2 в примере небольшая. Протестируйте предложенные формулы на сотни или больше строк в таблице 2
Алексей М.
 
Цитата
АlехМ написал:
Протестируйте предложенные формулы на сотни или больше строк в таблице 2
Уже протестировал, конечно виснет страшно)) у меня тысячи строк, но я знал, что будут подвисания, при выполнении всего этого с помощью макроса думаю быстрее бы работало, но к сожалению не умею писать их, похоже долго мне придется ждать расчетов), завис эксель
Изменено: pvolodya - 14.09.2018 22:56:24
 
Цитата
pvolodya написал:
если в таблице 1, в пяти ячейках идет порядок 1 2 3 4 5, а во второй таблице этот порядок будет 5 4 3 2 1 или 5 3 1 4 2, он считает их равными
Следовательно, можно сравнивать суммы пяти ячеек в первой и второй таблицах?
 
Цитата
Юрий М написал:
Следовательно, можно сравнивать суммы пяти ячеек в первой и второй таблицах?
К сожалению все так просто не выйдет,
как пример в первой таблице будут значения 1 2 3 4 10, а во второй 1 2 3 5 9, следовательно сумма будет и в первой и второй одинаковая
 
Проверьте вариант.
 
Все разобрался с кодом, вот в этих строках достаточно было изменить значения:
Код
Arr1 = Range(Cells(2, 1), Cells(LastRow, 6)).Value
   LastRow = Cells(Rows.Count, 10).End(xlUp).Row
   Arr2 = Range(Cells(2, 9), Cells(LastRow, 13)).Value
А что означает вот это [B]Cells(Rows.Count, 10) ?
 
Это мы находим последнюю строку по столбцу 10 (J) для второй таблицы.
 
Как быстро макрос обработал Ваши "тысячи строк"? )
 
у меня там десятки тысяч, не смог дождаться ) все висло по страшному

Цитата
Юрий М написал: Это мы находим последнюю строку по столбцу 10 (J) для второй таблицы.
а почему именно с 10 столбца ищет, а не с 9, значения ведь идут с 9 столбца?
 
Доброе время суток
Версия на Power Query. 60000 строк в левой таблице, 50000 в правой - отработало на нетбуке 2011 года за 14 секунд. Левая на листе "Сравнить", правая - "С этой", результат на листе "Отчёт".
 
Цитата
Андрей VG написал:
Версия на Power Query
Объясните пожалуйста, а как это запустить в excele ?
Никогда не пользовался этим
 
Цитата
pvolodya написал:
Никогда не пользовался этим
Power Query — обзор и обучение
 
Не должно виснуть, странно...

Цитата
pvolodya написал:
а почему именно с 10 столбца ищет, а не с 9, значения ведь идут с 9 столбца?
Можно по любому из этих пяти столбцов.
 
pvolodya, немного переделал: было много лишних итераций в циклах. Проверьте этот вариант.
 
Привет!
Без PQ, чиста VBA. На 60 тыщ строк 18 сек.
Сравнение прайсов, таблиц - без настроек
 
Цитата
Юрий М написал:
немного переделал: было много лишних итераций в циклах. Проверьте этот вариант.
Спасибо большое за помощь, но похоже у меня с компом что-то не то или все таки когда несколько тысяч значений, макрос медленно перебирает, нужен наверное более быстрый проц для него, вот что происходит при запуске:
 
Благодарю, отлично отработал макрос, за 108 секунд отработал мое количество значений. Ролик по решению моей задачки лайкнул)

Цитата
Inexsu написал: Привет!Без PQ, чиста VBA. На 60 тыщ строк 18 сек.
Благодарю, отлично отработал макрос, за 108 секунд отработал мое количество значений. Ролик по решению моей задачки лайкнул)
Изменено: pvolodya - 16.09.2018 08:34:12
 
Цитата
Inexsu написал:
Без PQ, чиста VBA. На 60 тыщ строк 18 сек.
Что-то долго коллега. На тех же моих тестовых данных
Цитата
Андрей VG написал:
60000 строк в левой таблице, 50000 в правой
отработало 2,7 секунды. Не думаю, что у вас железо такой допотопной конфигурации. Хотя всё может быть...
 
Цитата
Андрей VG написал:
Что-то долго коллега. На тех же моих тестовых данных
Реально быстро, за 5 секунд сверил, но почему то, когда я копирую все свои значения в ваши таблички, очень долго почему то перенос идет и тоже подвисания. Минут по 15-20 переносятся мои данные
Изменено: pvolodya - 16.09.2018 01:53:26
 
Привет!
Цитата
Андрей VG написал:
Что-то долго коллега
Дык у Вас код = оптимизация. У меня = maintainable.
Взял комп чуть мощнее. Влупил обеим таблицам по 60 тыщ строк.
Закомментил у себя индикацию.
Ваш 1,279297, мой 1,671875.
Сравнение прайсов, таблиц - без настроек
 
Цитата
Inexsu написал:
Ваш 1,279297, мой 1,671875.
Где-то так и должно было быть. На прирост больше чем в 1,5 раза и не рассчитывал. По факту, ы на ПК с процессором, разгоняемым до 3,4Ггц тестировали? Странно, что данным ТС отношение такое. У него ваш 108 секунд, мой 5. Что до правильности обслуживаемости кода. Ну, не считаю столь тщательную разбивку кода особо нужной. Зачем выносить swap в отдельную функцию? Там временные затраты на вызов и возврат сопоставимы с действием - зачем тратить время? У себя бы, с точки зрения переиспользования кода, вынес бы в функцию получение ключа по упорядоченным значениям строки. К стати, ваш код
Код
        For x = LBound(arr_2d, 2) To UBound(arr_2d, 2)
            str = str & CStr(arr_2d(y, x))
        Next
        arr_2d_String(y, 1) = str

не будет ли ошибки в такой сцепке. Положим одна строка 2; 2123; 4999, а другая 22;123; 4999 - обе будут иметь один и тот же ключ словаря при прямой сцепке после сортировки.
Цитата
pvolodya написал:
Минут по 15-20 переносятся мои данные
В данные вставляете как значения или вместе со всеми формулами? Вполне они могут тормозить. В представленной книги автопересчёт формул включен.
Изменено: Андрей VG - 16.09.2018 10:28:08
Страницы: 1 2 След.
Наверх