Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Универсальный ВПР макросом
 
#1
18.01.2016 21:32:40
Помогите, пожалуйста. Часто нужно сравнить две любые таблицы по общему столбцу (которые обычно находятся в разных файлах и столбец общий не всегда имеет одинаковый номер по счету). Т.е. таблицы могут быть разного вида, и сравнивать нужно по разным столбцам. Например, в одной это может быть столбец B а в другой столбец для сравнения это AA.

Упрощенный и идеальный пример (если это не разные книги, а соседние листы) - это файл index.xlsb
Вариант тот же макросом - index2.xlsb

Формулу можно протянуть и как угодно под себя поменять. Просьба помочь с макросом. Не могу сделать правильно следующее:
1. Таблицы могут быть в разных книгах и могут иметь разные столбцы для сравнения (а не только первый). То есть было бы хорошо, чтобы макрос выдал, что с чем сравнить и что откуда и как подтянуть (rng = Application.InputBox("Select cell(s)",type:=8);
2. Таблицы для сравнения могут иметь фильтры по столбцам. Поэтому хотелось бы сравнить только видимый диапазон и извлечь и подтянуть только видимые значения. Если формулу тянуть -то запариться при большом количестве строк.

То есть, как в первом файле формулой реализовано - для столбца или для протягивания на диапазон (несколько столбцов), то хотелось бы допилить в макросе. К сожалению, не очень выходит.

Почему подумал, что нужен макрос - строк может быть много. Формула не справляется с объемом.
Изменено: w00t - 18.01.2016 21:37:32
 
 
 
#2
18.01.2016 21:46:32
Можете поглядеть такой вариант универсального ВПР:
http://excelvba.ru/programmes/Lookup
Надстройки для Excel  Парсинг сайтов  Мониторинг цен конкурентов
 
 
 
#3
18.01.2016 22:14:17
Видел ранее. Но, здесь ничего нельзя поменять в коде и довольно много лишних опций. Почти близко к итогу мой короткий пример, хотелось бы, чтобы чуть помогли экселисты его немного скорректировать. Чем проще, тем лучше. Появилось стандартное окошко по Application.InputBox("Select cell(s)",type:=8) - ввести вручную нужный диапазон для сравнения в одной табличке, во второй, выбрать что подтягивать и куда подтягивать.

Просто я пытался, но не все дальше выходит сделать. Большой продвинутый макрос с формами не нужен, несколько строчек кода скорректировать бы :/
 
 
 
#4
18.01.2016 22:27:13
Цитата
w00t написал:
Если формулу тянуть -то запариться при большом количестве строк.
1. "нарисовать" нужную формулу в первой ячейке "под шапкой таблицы",
2. сделать двойной щелчок по "хвосту" мыши в этой ячейке, за который Вы "тянете" мышь, традиционно копируя формулу. :)
Изменено: Мотя - 18.01.2016 22:28:47
 
 
 
#5
18.01.2016 22:29:46
Мотя, так то да. Имел в виду если фильтр стоит на листе, и нужно вставить формулу только в необходимые фильтрованные ячейки столбца либо строки. Но и это можно сделать без ручного протягивания. Ладно, вдруг кто поможет немного макрос допилить. Формула в данном случае не вариант, при большом количестве строк.
Изменено: w00t - 18.01.2016 22:36:48
 
 
 
#6
18.01.2016 22:31:15
Как появляется "соседний" лист в файле для сравнения?
После рукопашного копирования?
 
 
 
#7
18.01.2016 22:40:59
Да, так точно. Изначально этот лист был листом не данной, а другой книги. И в идеале его бы в другой книге и оставить.
То есть одна книжка с табличкой, и вторая с совершенно порой другой табличкой. Их может объединять полностью или частично какой-то один столбец разным порядковым номером по счету.

Формула идеально описывает нужды, макрос же частично. Все, что в идеале поправить бы в макросе - возможность диалогового окошка выбора жестко сейчас зафиксированных регионов. У меня это криво получается сделать, несмотря на простоту, возможно туплю уже под вечер.

Ну и то что сейчас в макросе сверка по жестко зафиксированным листам, а сделать выбор - какая табличка в какой книге. Например, диапазон такой-то в книге test1 с диапазоном таким-то в книге test2 по столбцу А для первой таблицы и по столбцу AA второй таблицы. И из второй таблицы подтянуть либо диапазон рядом с первой табличкой (несколько столбцов), либо один столбец (прошу извинить за неясность изложения))
Изменено: w00t - 18.01.2016 22:42:08
 
 
 
#8
18.01.2016 22:44:03
В чем тогда принципиальная разница в ситуациях: один файл / два файла?
Можно в "макросный" файл делать считывание информации сразу из 2 (1) файлов.
Изменено: Мотя - 18.01.2016 22:45:58
 
 
 
#9
18.01.2016 22:48:54
Нив чем. Логика одна - сравнить. Я про то, что в моем пример во второй книжке есть макрос. Не совсем получается внести в него некоторые изменения - чтобы там были не фиксированные Range, а макрос выдавал диалог выбора - например, запустил макрос, выделил или указал вручную в окошке диапазон и в какой книге он находится. Т.е. макрос в текущей книге и есть вторая отдельная книга. При этом выбор диапазонов посредством диалоговых окошек макроса (выделением мышью, либо ctrl+shift+стрелочка вниз, либо ручного указания, как возможно будет).

UPQ *Можно в "макросный" файл делать считывание информации сразу из 2 (1) файлов./
Ага. Просто речь к тому, что раз 50 макрос запустить и раз 50 менять надо данные. Выделить диапазон в диалоговом окне проще. Ну и так понимаю он сравнивает сейчас по 1 столбцу, а таблички иметь могут разный по счет общий столбец и быть в разных книгах. Может со стороны выглядит, что код почти готов и надо чуть изменить. Так оно почти и есть (выглядит просто), но даже когда пытаешься разбираться а не бездумно писать ту и спрашивать чтоб сделали, не всегда просто довести до ума самостоятельно.
Изменено: w00t - 18.01.2016 22:54:16
 
 
 
#10
18.01.2016 22:58:38
На мой взгляд...
Можно как с диалоговым окном, так и без него.
На листе типа "Реквизиты отчета" в оговоренных ячейках указывать буквы (№ столбов).
Информация из этих ячеек будет "описывать" таблицу сравнения.
Поскольку речь идет о сравнении двух таблиц, то будет две последовательности ячеек с описанием столбов:
1. таблица ведомая - нужен столб, по которому будет выполняться поиск, и столбы, из которых нужно "подтягивать" информацию,
2. таблица ведущая - нужен столб, по которому будет выполняться поиск.
Изменено: Мотя - 18.01.2016 23:07:23
 
 
 
#11
18.01.2016 23:10:44
Да, это было бы еще лучше, иметь служебный лист под данные цели, чем каждый раз вводить в диалоговом окошке диапазон. Для одной книги и для второй столбцы.
Цитата
1. таблица ведомая - нужен столб, по которому будет выполняться поиск, и столбы, из которых нужно "подтягивать" информацию,
2. таблица ведущая - нужен столб, по которому будет выполняться поиск.
Ага. Но только видимые строки, наподобие как текущий макрос отрабатывает (по крайней мере если нашлось совпадение - то подтянуть и вставить только для видимых по фильтру строк). На первой таблице к которой тянем фильтр может в 50% случаев стоять, а во второй таблице -из которой тянем и с которой сравниваем - очень редко (практически никогда).
Изменено: w00t - 18.01.2016 23:15:34
 
 
 
#12
18.01.2016 23:18:05
Можно в файле index.xlsM "содержать" 3 листа:
1. "Реквизиты отчета",
2. "Таблица основная",
3. "Таблица БД".
Макрос:
1. запрашивает два файла,
2. копирует информацию с 1-ых листов этих файлов на свои листы,
3. выполняет сравнение поиск и "ваяет" результат.
При этом для основной таблицы придется указать, в какой столб ваять результат.
Как я поняла, в Вашем примере в базовом листе один столб поиска и один столб для "подтягивания" информации.
Изменено: Мотя - 18.01.2016 23:25:26
 
 
 
#13
18.01.2016 23:34:33
Цитата
Мотя написал:
При этом для основной таблицы придется указать, в какой столб ваять результат
В какой столб или несколько столбов (если нужно подтянуть не 1 а пару или больше столбов со второго листа).

Ну и сравнение по столбцам, которые могут иметь различный номер по счету. Столбец поиска один, только в первой табличке и второй его позиции могут не совпадать (A в первой или AB во второй, к примеру).

Пока виртуально хороший вариант, но почему формулу меняю - долго работает. Вес файлика одного обычно от 20 мегабайт (без форматирования в xlsb),

Обычно одну таблицу одного файла сравниваю с тремя таблицами трех других файлов (максимум с 5-6 табличками,каждая в отдельной книге). То есть нужную табличку в нужной одной книге сверить с табличкой второй книги, подтянуть, потом второй табличкой другой книги и подтянуть и еще с третьей и подтянуть так же в одну первую книгу к таблице. Тут немного дольше будет. По идее можно и отдельную книгу для этих целей. Но если три-пять раз он будет запрашивать основной файл, который с другими сверяю, то это грустно. Можно просто макрос и служебный столбец засунуть сразу в нужный файл. И далее уже в него подтягивать нужные листы нужных книг. То есть запрашивать не два файла а один (с чем сверяем текущую табличку в нашей книге). Хотя это мое видение, оно может быть не оптимальным, но кажется что так лучше было бы.
Изменено: w00t - 18.01.2016 23:42:46
 
 
 
#14
19.01.2016 00:08:07
Цитата
w00t написал: подтянуть не 1 а пару или больше столбов со второго листа
Сколь угодно.
Цитата
формулу меняю - долго работает
Формулы Ваши - с Вас и спрос. :D
Цитата
Обычно одну таблицу одного файла сравниваю с тремя таблицами трех других файлов (максимум с 5-6 табличками,каждая в отдельной книге).
Файлов можно "зачитать" сколь угодно.
Можно "молча" - сколько есть в указанном Вами каталоге - макрос попросит указать каталог вместо перечня файлов.
Изменено: Мотя - 19.01.2016 00:33:56
 
 
 
#15
19.01.2016 00:17:50
Цитата
Мотя написал: Файлов можно "зачитать" сколь угодно. Можно "молча" - сколько есть в указанном Вами каталоге.
Это очень хорошо было бы. Только таблички могут отличаться не только от первой, но и между собой (столбцы местами поменяны, например; или лишний столбец или наоборот, не хватает столбца), поэтому наверное лучше "по одному" сверять (настройки то для пары- приемник/источник). Пакетная сверка это круто, но потребует отдельные настройки для каждого файлика (в 10% всех случаев).
 
 
 
#16
19.01.2016 00:26:38
Можно на листе "Реквизиты отчета" задействовать столько столбов описания файлов загрузки, сколько нужно.
Макрос сам найдет последний столб.
Ячейки описания столбов "внутри" файлов загрузки заполняться будут по вертикали (вниз).
При этом в качестве "шапки" на этом листе придется использовать имена файлов, т.к. при "молчаливом" считывании файлов может наступить противоречие с описанием реквизитов файлов.
Изменено: Мотя - 19.01.2016 00:27:30
 
 
 
#17
19.01.2016 00:43:59
Да вроде бы все так. В файлах для сверки (и куда тянуть и из каких тянуть) может быть несколько листов, но лишние всегда можно удалить в принципе вручную, оставив только по одному листу. То есть те листы, которые нужно - оставить для сверки.

Я просто уже боюсь представить сложность и уровень кода (это если вдруг еще добавить указание листов, на которых есть нужные таблички в сравниваемых книгах), поэтому о этой и других ранее озвученных фишках даже не спрашивал.
 
 
 
#18
19.01.2016 00:52:52
Ну, это ведь всего лишь двумерная матрица:
1. по горизонтали - количество файлов,
2. по вертикали - описание файлов - имена файлов, названия листов, № (буква) столба поиска, №№ (буквы) столбов "подтягивания".  :D
Понятно, что делая экспорт из ПО, Вы непременно посмотрите на структуру таблиц в файлах.
Изменено: Мотя - 19.01.2016 00:56:33
 
 
 
#19
19.01.2016 01:06:10
Да, все так. Вроде бы все полностью логично теперь, не то что я километр текста написал.
 
 
 
#20
19.01.2016 01:08:16
Цитата
w00t написал: Я просто уже боюсь представить сложность и уровень кода
Я код пишу очень тривиальным способом: я владею только основами структурного программирования, которое всем поголовно "насаждали" во всех не гуманитарных вузах. :)
Правда, стараюсь оптимизировать свой код: все выполняю ТОЛЬКО В ОПЕРАТИВНОЙ ПАМЯТИ компьютера. :D
Изменено: Мотя - 19.01.2016 05:42:38
 
 
 
#21
19.01.2016 01:19:02
Присылайте в почту файлы выгрузки - 2(3) шт.
Опишите в отдельном файле матрицу про файлы.
Попробую завтра вечером "сваять" макрос.
 
 
 
#22
19.01.2016 01:48:21
Единственный момент, который очень сложно (не вполне реально сделать). Нравится эксель, но там где применяю какие-то свои или не вполне знания - нереально что-то скопировать при всем желании. Именно по этой причине приложил пример двух экселевских файликов.

Как вариант, попробую завтра натыкать и отправить нечто очень близкое к реальности. Но это будет больше похоже на копипаст любого текста для увеличения объема данных. Честно - файлы порой бывают самые различные, стандарта собственно нет и для разных задач они могут меняться. Но, к примеру, не встречал еще такого, чтобы столбец по которому сравниваю - имел пустые ячейки, поэтому обычно его в тот же ВПР легко протянуть до конца всех строк ctrl+shift+стрелка вниз. А все прочие столбцы (output и inpur range) - они могут иметь пустые строки, но это и не критично в данном случае. Другое дело, что может стоять фильтр и тогда вставить данные корректно только в то что видимо.

Хотя, если брать пустые строки - да, может быть такой случай, когда есть какая-то пустая строка вставленная в середине любой таблицы, или пустой столбец. Но это можно предварительно вручную проверить. Это к возможным отказам макроса на непредвиденные ситуации. ВПР иногда отказывается сравнивать, к примеру, если в одном файле 12345 как число а в другом как текст. Но это предварительно так же можно сделать, привести столбец сравнения к общему виду.
Изменено: w00t - 19.01.2016 03:34:29
 
 
 
#23
19.01.2016 12:05:30
Цитата
Мотя написал: Присылайте в почту файлы выгрузки
Доброго дня! Через почту не особо вышло, прикладываю здесь. На листе 1 три примера (три варианта). И на последнем листе пояснение что и как. Немного повозился, чтобы сделать более-менее массив данных побольше, точный к реальным таблицам. Иногда бывает нужно сверить один лист книги с произвольными листами этой же, а иногда и в разных книгах сверяемые могут находится, на разных листах.

Там же в пояснении в трех пунктах ближе к концу отразил суть. Не уверен просто, что если что-то менее гибкое но более массивное - то это лучше. Может просто функцию на VBA? Листики предварительно обработать не сложно, вопрос то исключительно в трех пунктах.

В пример могут быть дубли по сверяемому столбцу. В таком случае сначала делаю сводную табличку (для файлов где есть такие дубли), чтобы оставить уникальные значения и формулой подтягиваю первое попавшееся из старой таблички с дублями. Получаю итоговую табличку для сравнения. Я потому и говорю, что функция может быть в данном случае будет более полезной и универсальной, чем макрос... Формулы, которые привел, применяются порой самыми разными способами, но все варианты формул указал. Обычное сравнение диапазонов с подтягивание чего надо куда надо и с проверкой на ошибки..
Изменено: w00t - 19.01.2016 12:26:55
 
 
 
#24
19.01.2016 12:49:04
Добрый день
Сомневаюсь, что UDF будет быстрее работать
Когда-то делал макросами замену ВПРу, может подойдет
 
 
 
#25
19.01.2016 15:39:17
Практически, за исключением того, что данным макросом можно подтянуть только один столбец. То если нужно будет 15-20 столбцов - то придется повторить процедуру столько же раз. Ну и, если несколько таблиц нужно сверить с основной (не нашли в первой - то ищем во второй, не нашли во второй - ищем в третьей) - то довольно сложно будет расширить код на эти условия.

Посмотрим, вдруг Мотя еще поможет. склоняюсь к переходу постепенному в Acceess, но на данном этапе нужен только Excel, к сожалению. Задачка то обычная - иметь возможность быстро посчитать ЕСЛИОШИБКА(ИНДЕКС(Range;(ПОИСКПОЗ(Cell;Range;0));"-") на огромном диапазоне. Но только что таких "вложений" формулы в формулу может быть до 5 и есть необходимость либо столбец подтягивать, либо диапазон.

Файлики, которые в первом сообщении приложил, конкретно этот с двумя макросами - вроде более-менее работает, нет лишь только возможности выбирать диапазоны (забиты в коде), да нет возможности выполнить несколько проверок подряд таких (если не нашло в одном диапазоне - поискало и подтянуло со второго а если не нашло то дальше сколько надо). Уж если не получится вложенные проверки, то хотя б выбор диапазона помочь сделать.
Изменено: w00t - 19.01.2016 17:35:21
 
 
 
Страницы: 1
Читают тему
Наверх