Не редко возникает ситуация, когда необходимо перенести данные из одной таблицы в соответствующие ячейки другой. Как говорится, ничего сложного, пока тиких данных немного. В противном случае - это проблематично. А когда данное действие нужно делать несколько раз в неделю, это уже очень сильно напрягает и отнимает уйму времени.
Так и в моем случае, мне понадобилось еженедельно отслеживать позиции своего сайта planero.ru по нужным ключевым словам. И если съем позиций сайта в выдаче Яндекса я осуществляю с помощью небезызвестного Key Collector в автоматическом режиме путем создания экселевского файла такого вида:
В общем, результат работы key collector’а представляет из себя массив данных, который не дает конкретного представления о ситуации в целом. Картину целиком можно увидеть в другой таблице, уже созданной мной, где отражена сама статья с ее продвигаемыми ключевыми словами и позиции, на которых находится мой сайт на дату «02.06.2020». На итог необходимо, при нажатии на кнопку «Заполнить позиции страниц в выдаче», автоматически перенести данные из таблицы key collector’а в мою таблицу, напротив соответствующих ключей, при этом нужно добавить новый столбец с датой съема позиций, и чтобы было совсем наглядно, выделить цветом позиции, которые просели (красным), либо наоборот поднялись (зеленым).
Полностью рабочий вариант макроса переноса данных по условию с одной таблицы в другую по условию можно скачать на сайте статьи.
Ну что ж, начнем воплощать в жизнь идею по автоматизации рутинной работы. И начнем с составления формы, которая будет появляться при нажатии на кнопку «Заполнить позиции страниц в выдаче» для выбора экселевского файла кей коллектора и даты съема позиций.
После напишем процедуру на листе «Статьи» вызова UserForm1 при нажатии на кнопку. Процедура должна автоматически предоставлять данные по всем открытым книгам Эксель, записывать текущую дату и выравнивать форму по центру экрана. Сама же процедура будет выглядеть следующим образом:
Теперь при нажатии на кнопку «Заполнить …» нашему взору представится картина следующего вида:
Дальше напишем макрос для кнопки «Ок» UserForm1 проверяющий правильность выбора файла excel, т.е. наличия в нем необходимых ключевых фраз и в случае некорректного выбора, информирование пользователя с последующим выбором другого файла.
И наконец, реализуем самую главную функцию fpoz, которая и будет осуществлять сравнение данных в двух таблицах, заполнение необходимых строк и выделение цветом соответствующих ячеек. Вкратце сам принцип работы функция выглядит следующим образом:
- Ищем в шапке таблицы выбранной даты
- Добавляем новый столбец c его соответствующим форматированием (в случае если даты выбранной в UserForm1 нет в шапке таблицы)
- Записываем продвигаемые фразы из нашей таблицы в массив
- Находим в файле key collector’а столбец с наименованием «Фраза» и столбце с наименованием «Позиция [Ya]»
- Записываем ключевые слова из файла key collector’а и столбцов «Фраза», «Позиция[Ya]» в массивы
- Сравниваем массивы между собой и при совпадении – записываем значение позиции в соответствующую ячейку нашей таблицы, при этом, в случае если предыдущее значение было больше текущего (поднялась) – выделяем его зеленым. И наоборот при ухудшении позиции (просела) – красным.
Полностью реализованная функция приведена ниже:
На итог у нас получится картина следующего вида:
Теперь копирование позиций напротив соответствующего ключевого слова происходит автоматически, при этом сравниваются с предыдущими значениями этих же позиций, и выделяются цветом: красным – в случае проседания позиции, зеленым – в случае улучшения.
После реализации данного макроса, отпала необходимость каждый раз вручную или с использованием встроенных в эксель функций, например ВПР(), заниматься ручным сопоставлением. Сейчас за нас это делает сам компьютер в течении одной секунды, при этом лишь необходимо нажать на кнопку «Заполнить позиции страниц в выдаче».