Задача: сравнить 2 таблицы Excel и отобразить изменения.
Исходные данные: дана таблица на одном листе со старыми значениями и новыми.
Решение: написать многоразовый макрос в VBA Excel, который будет сравнивать старые значения с новыми и подсвечивать изменения.
1. Разрабатываем макрос для решения проблемы
Очень важно при написании многоразовых макросов, упорядочить данные. Автор таблицы просто поместил данные на один лист. Таблицы разные по размерности, такое нам не подходит.
Упорядочиваем данные Excel таблицы
Разделю данные по листам и создам лист результата:
Автор пометил в колонках рядом с таблицей, где старые данные и новые. Перемещаю их в соответствующие вкладки.
Во вкладку "Сравнение" я перенёс только этапы и удалил все дубликаты.
Чтобы удалить все дубликаты, надо выделить диапазон и перейти во вкладку "Данные" -> "Удалить дубликаты".
Создаю 3 новые колонки: "Новое", "Старое", "Статус". В колонке "Новое" прописываю формулу:
=СЧЁТЕСЛИ(Новое!A2:A871;Сравнение!A2)
В колонке "Старое":
=СЧЁТЕСЛИ(Старое!A2:A872;Сравнение!A2)
В колонке "Статус":
=ЕСЛИ(B2=C2;"ОК";"ERROR")
Теперь есть статусы, которые показывают изменения в шифрах., но только количество, не наименование самих шифров.
Выделяю весь диапазон формул и вставляю "Как значение":
Это нужно мне для того, чтобы макрос смог обработать все эти строки.
Данные подготовлены! Теперь приступлю к написанию макроса
Разрабатываем макрос проверки данных
Сначала мне нужно, чтобы макрос понимал, откуда ему брать данные, прописываю:
У меня есть 2 варианта обработки
- Сравнивать ячейки.
- Сравнивать массивы.
Первый вариант очень медленный, так как экселю придётся каждый раз обращаться к ячейке и считывать данные, поэтому буду использовать второй вариант.
col_str - количество именно заполненных ячеек, в A1 у нас лежит заголовок, который по идеи не нужно считать, но мы его всё же посчитаем.
db_staroe - тут назначаю диапазон .Range("A2:B" & col_str), именно с А2 и до колонки B, строки col_str. Если в col_str не заложить заголовок, то придётся прибавить +1 к col_str .Range("A2:B" & col_str +1).
db_paste - это массив, куда будет складываться результат, для конечной выгрузки. Разберём, что именно там прописано:
ReDim db_paste(1 To UBound(db_srav), 1 To 1)
ReDim - служит для перезаписи массива
1 To UBound(db_srav) - размер всегда начинается с 1 до "UBound(db_srav)". UBound(db_srav) даёт нам количество элементов в массиве db_srav.
После идёт 1 To 1, это колонки. Пока нам нужна одна колонка результатов.
4 массива создано, приступаю к написанию циклов и условий.
Я даю наименование переменным так, как мне удобно. Это мой стиль, можно называть их как угодно. К примеру sheet_star можно поменять на Sasha и ничего не изменится. db_srav можно поменять на koshka.
Написания логики обработки данных - самая сложная часть. У нас будет предполагаться тройной цикл с условиями.
Создаю первый - главный цикл:
Цикл будет идти с шагом 1, до максимального количества элементов db_srav.
Для удобного написания и восприятия кода, пропишу переменную на каждую колонку массива.
db_srav(i,1) - в скобках (i,1), i - значит итерация цикла, начинается с 1 и так до конца UBound(db_srav); 1 - значит колонка в массиве. Для визуализации, покажу вам, что это за элемент db_srav(5, 1):
в db_srav(5, 4) ,будет лежать статус "ОК". 5 это строка, 4 - столбец.
Создам два условия, для статуса "ОК" и для статуса "ERROR":
Статус "ОК" у нас учитывает только количество элементов, а нам нужно ещё сравнить наименования. Создаю цикл:
Цикл в статусе "ОК":
Создаю второй и третий цикл, чтобы обойти db_staroe и db_new. В третьем цикле есть условие "Условия, сравниваем":
If etap_srav = etap_new And etap_srav = etap_star Then - Если в трёх массивах, при определённой итерации i этапы равны, то тогда мы заходим внутрь и попадаем в конечное условие.
If name_star = name_new Then - если шифры равны, то тогда мы присваиваем переменной "OK" и выходим с цикла i2. Если нет, то присваиваем "Не ок". Нам нужно знать результат работы наших циклов, как раз эта переменная и будет нам давать этот результат.
Обрабатываем наш результат:
If status_poisk <> "OK" Then - Если статус не равен "OK", то тогда условие выполняется. Далее идёт цикл, он для определения, в первый раз мы сюда заходим или нет:
If db_paste(i, 1) = "" Then - "" кавычки означают пустое множество.
db_paste(i, 1) - это статус изменений.
db_paste(i, 2) - это раскрытие статуса, в каких шифрах именно произошли изменения.
status_poisk = "OK" и Z = 0, специально переназначаю переменные в исходный вид, чтобы случайно дополнительно не залететь в цикл.
Обработка результата статуса "OK" завершена, давайте посмотрим на наш результат:
В этапах 7.19 и в 7.23, есть изменения в 6 шифрах. Сделаю ручную проверку:
Шифры в данных этапах были полностью изменены, макрос отработал на 100%.
Переходим к обработке статуса "ERROR"
Пока я описывал обработку результатов "ОК", то понял, что смысла в обработке статуса "ERROR" нет, так как там есть явные не соответствия, которые можно обнаружить с помощью встроенного условного форматирования:
Вытащить этап на отдельный лист и использовать это форматирование.
Выгрузка отработки макроса
Обращаемся к нашему листу сравнения sheet_srav и вставляем туда наш массив.
.Range("D2:F" & UBound(db_paste) + 1), плюс один здесь из-за того, что диапазон вставки данных начинается не с D1, а с D2.
Почему перед Range ставится точка? Потому что если вы поставите без точки, то With sheet_srav не сработает и данные вставятся туда, откуда вы запустили макрос. Поэтому я везде указываю конкретно к какому листу надо обращаться.
Заключение
У меня получилось достать все подводные камни статуса "ОК". Помните, я создал объекты с листами и воспользовался внутренней функцией подсчёта заполненных строк?
Это даёт нам многоразовую систему проверок. Автору достаточно просто вставить в эти вкладки новые данные и он снова сможет воспользоваться этим макросом.
А как бы вы решили эту задачу? Пытались бы вы написать макрос или попробовали бы решить всё формулой?
P.S. Данную задачу можно было решить на Python, но его я применяю, когда нужно провести более 1 трлн. итераций.
Прошу поддержать мой канал лайком и подпиской! Не забудь оставить комментарий или прислать свою задачу 😉 На выполнения задачи ушло примерно 2,5 часа.