Страницы: 1
RSS
Макрос для сравнения столбцов с одинаковыми заголовками, находящимися в разных документах
 
Всем доброго времени суток!
Имею задачу, часть из которой решила самостоятельно, но дальше моих знаний по макросам не хватило, с чем и прошу о помощи :oops:
Подзадача №1.
Есть отчёт, который автоматически формируется довольно коряво, и его приходится постоянно приводить к нормальному виду вручную. Я написала макрос, который создаёт фильтр, и по нему в !!!указанных в коде макроса диапазонах!!! удаляёт всё ненужное. Но, конечно, диапазоны в разных отчётах разные, и лучше бы его как-то доработать, чтобы каждый раз руками значения не вводить. Диапазон для дальнейших изменений можно получить следующим образом - после удаления пустых столбцов номер последнего столбца можно найти на 3-й строке через ctrl+shift+стрелка вправо. А номер последней строки по символу звёздочки (*), которая всегда находится во втором столбце с заголовком "Дата". Начинается диапазон с ячейки A3. Думаю, что можно обойтись и без создания фильтра. Так как будет известен диапазон, то в нём будут прописаны следующие условия - нужно удалить все строки, во втором столбце которых находится значение "Дата", а также все пустые строки.
Подзадача №2.
После коррекции отчёта в нём на 3-й строке будут заголовки. Имеется второй файл "Справочник", в котором заголовки находятся также на 3-й строке. Нужно проверить правильность данных трёх столбцов из отчёта по следующим заголовкам: "Департамент", "Отдел", "№ ID" по таким же названиям столбцов из файла "Справочник". Для этого создаются три новых листа в файле отчёта. В лист "Департамент" копируется столбец из отчёта с заголовком "Департамент", и все ячейки сравниваются с "справочными". Если какой-то ячейки нет в диапазоне "справочного" департамента, то она, к примеру, выделяется красной заливкой. Аналогично для столбца "Отдел". И наконец, "№ ID" - тут уже копируются столбцы "Департамент" и "№ ID". Они зависимы друг от друга и значения из обеих ячеек должны совпадать со значениями из ячеек справочника.
P.S. красным шрифтом выделила ячейки, в которых специально допущены ошибки (для проверки)
Заранее огромное спасибо!
 
Привет!
Желательно приложить ещё и то, что нужно..
Во вложении прототип решения "Подзадача №1".
Изменено: Inexsu - 08.04.2019 23:59:42
Сравнение прайсов, таблиц - без настроек
 
Inexsu,очень интересное решение, спасибо! А почему мы ищем звёздочку и при поиске крайнего столбца, и при поиске крайней строки? Она же по идее требуется только для нахождения номера строки? :oops:
И что нужно ещё приложить?
 
Привет!
Цитата
criss написал:
почему мы ищем звёздочку и при поиске крайнего столбца, и при поиске крайней строки? Она же по идее требуется только для нахождения номера строки?
Это прекрасное совпадение.
Коды поиска последней нижней строки и крайнего правого столбца придумали до Вашей задачи.
Да, эти коды нам, пока, не пригодились. :-)
Цитата
criss написал:
что нужно ещё приложить?
Вы приложили исходные данные, свои рассуждения .. а как должен выглядеть результат для "Подзадача №1! ? Очень помогает найти решение.
Сравнение прайсов, таблиц - без настроек
 
Inexsu, в исходном отчёте, который я скинула, при нажатии кнопки отчёт и принимает требуемый вид. Ваше решение также полностью решает эту «Подзадачу №1» :)
Осталось сообразить, как решить вторую подзадачу. Пока и сама думаю, но безуспешно :(
Изменено: criss - 08.04.2019 09:45:37
 
Inexsu, а нет, я всё же ошиблась... После выполнения Вашего макроса в итоговом отчёте удаляются все строки, в которых есть значение «Дата». А нужно, чтобы первая строка с этим значением оставалась, так как это будет заголовок.
 
Обновил файл в сообщении 2
Сравнение прайсов, таблиц - без настроек
 
Inexsu, большое спасибо, как у компьютера буду, посмотрю
 
Inexsu, супер, практически полностью задача решена, но есть одна проблемка...  Столбец "№ ID" зависим от значений и столбца "Департамент", и "Отдел". Т.е. если в одном из столбцов департамента или отдела допущена ошибка, то "№ ID" тоже должен выделяться красным. Ну и конечно сам по себе должен совпадать с справочным, но это реализовано.
 
Ещё раз обновил файл в сообщении 2
Сравнение прайсов, таблиц - без настроек
 
Inexsu, это шедевр, огромное Вам спасибо!)

Сегодня пыталась внедрить этот макрос в файл, в котором порядка 150 тысяч строк, но макрос выполнялся около часа, и так и не выполнился (excel просто завис). Вероятно это из-за того, что на 150 тысяч строк там пустых строк около 100 тысяч, но если вручную их удалять, то на это тратится минут 10. В связи с чем и вопрос - можно ли тут как-то ускорить работу макроса? Может screenupdating использовать, или ещё какие-нибудь действенные варианты?
 
В макросах не разбираюсь, но из любопытства попытался решить с помощью powerquery:
каждый столбец отдельными запросами очистил от пустых значений
добавил индекс
затем соединил запросы в один
отменил свертывание других столбцов кроме индекса
затем свел обратно в столбцы без агрегирования и получил таблицу из подзадачи 1
фильтранул, чтоб заголовок встречался 1 раз
поднял строки в заголовки

Сработало. Но только благодаря одинаковому кол-ву значений для каждого поля - индексы совпали. Растянул на 5000 строк - отработало быстро. Я к тому, что может можно решать не только макросом.

Со справочником PQ тоже легко сравнит с помощью левых/правых соединений.
 
Цитата
criss написал:
ещё какие-нибудь действенные варианты?
Да. Уже придумано много хитростей для ускорения.
Сравнение прайсов, таблиц - без настроек
 
Цитата
Inexsu написал:
Да. Уже придумано много хитростей для ускорения.
А какие из них лучше использовать в данной задаче?
Хотелось бы конечно не через Query, а макросом, чтобы как можно более автоматизированным было решение...
Страницы: 1
Наверх