Макрос сравнение двух книг excel
Я взял коды из stackoverflow и хотел разработать макрос для сравнения двух книг excel с несколькими листами и выделения значений ячейки, которые отличаются.
Я могу создавать новые листы, но я не могу скопировать и выделить измененные данные на отдельные листы excel.
Текущий код копирует и выделяет различия, но делает все это на одном листе, перезаписывая предыдущие скопированные и выделенные данные.
Я думаю, что ваш лучший ответ будет заключаться в том, чтобы создать новый лист, в котором перечислены изменения, предпочтительно в новой книге.
Затем вы должны использовать объектную переменную типа Excel.Решетка и перебирать листы в своей книге:
Итерация по каждому рабочему листу в книге Excel с использованием VBA
Я оставлю вас заполнить логику захвата значения и цикл сравнения: и я отмечаю, что вы используете эффективный метод захвата данных, когда вы поднимаете диапазон ячеек в массив за один вызов на каждом листе и повторяете массив.
Самый эффективный метод вывода - написать массив на листе в одном "хите"; однако необходимость форматирования целевого листа по ячейкам снижает коэффициент усиления.
[Отредактировано: дополнительно материал по запросу]
В качестве сноски вы можете удалить ненужные листы с помощью этого фрагмента VBA:
Однако этот код содержит предупреждение: имена листов будут отличаться от "Sheet1" и т.д. В международных версиях MS-Office. И было бы неловко, если бы один из листов в изучаемой книге назывался "Sheet2".
Вы можете попробовать удалить листы по порядковым номерам wbkC.Worksheets(1). Удалить: wbkC.Worksheets(2). Удалить и т.д., Но это может смущать, если ординалы не там, где вы ожидаете, что они будут после выполнения сравнения и создание новых листов.
Я позволю вам найти практические примеры неожиданного поведения в ордерах контейнера объектов.
. Поэтому ответ заключается в удалении листов в wbkC перед операциями над книгами "A" и "B". Для этого есть некоторые тайные точки защитного кодирования:
Вы не можете удалить последний лист: мой совет будет состоять в необходимости и переименуйте его "Контроль" или "Аудит" и используйте его для записи имен файлов "A" и "B" с именем пользователя и отметка времени.
Сравнение значений двух книг
Здравствуйте. Имеется два списка (в аттаче) требуется: сравнить значения из колонки Штрихкод, при.
Сравнение столбцов открытых книг
у меня есть открытые книги и надо сравнить столбцы "Е" в этих книгах, названия книг неизвестны.
Сбор ячеек из разных книг в одну строку новой книги
Как человек не разбирающийся прошу помощи в создании макроса. Есть задача собрать данные для.
А сравнивать нужно все миллиарды ячеек (usedrange -оно такое непредсказуемое. ), или интересует конкретная область?
Что делать с форматами/заливками/цветами/шрифтами/выделениями жирным?
И как Вы решите, что важнее - 1234 или 4321? Или например "аор" или "aop" (это совершенно разные слова!)?
1. Сравнивать листы с совпадающими именами
2. Хорошо бы диапазон задавать, но можно и все подряд, файлы не очень большие, да и подождать можно.
3. Не обращать внимание на форматы/заливки/цвета/шрифты/выделениями жирным
4. Что важнее решает пользователь через диалог.
Это два файла в которых ведут записи два пользователя, результат работ надо объединять, раз в два-три дня.
Общий доступ дать к одному файлу нельзя, т.к. один из пользователей имеет ограничение к доступу к информации в первом файле (не все нужно ему видеть).
Откуда производится сравнение: из одной из этих книг, или из третьей?
Что значит: "если еще и редактировать значение ячейки "на-лету""? Иметь возможность вводить третье значение вместо предложенных двух? И куда: в обе ячейки или в какую-то одну?
Что значит: "объединять сможет"? Имеется ввиду сложение значений? Или объединение ячеек? Если второе, то как вы себе это представляете?
Что значит: "по желанию юзера сохраняет файл/ы"? По-моему, если кто-то куда-то вносит изменения, то они должны однозначно сохраняться, иначе зачем эти изменения вносить?
Количество и порядок листов одинаковы.
>Откуда производится сравнение: из одной из этих книг, или из третьей?
Лучше из одной, но для меня это не важно, могу из любой.
>Что значит: "если еще и редактировать значение ячейки "на-лету""? Иметь возможность вводить третье значение вместо предложенных двух? И куда: в обе ячейки или в какую-то одну
Да, иметь возможность вводить третье значение. В какую-то одну.
Это нужно Для исправления или объединения ручками значений в одном из файлов
>Что значит: "объединять сможет"? Имеется ввиду сложение значений? Или объединение ячеек? Если второе, то как вы себе это представляете?
Программа ничего не решает и не объединяет, у нее следующие реакции по результатам сравнения ячеек "первого" и "второго" файлов
1. Ячейки совпали - молчим, сравниваем следующую пару
2. Ячейки не совпали, спрашиваем юзера, какой значение поместить во "второй" файл
-из "первого" файла
-из "второго"
-редактированное руками юзера.
> Что значит: "по желанию юзера сохраняет файл/ы"? По-моему, если кто-то куда-то вносит изменения, то они должны однозначно сохраняться, иначе зачем эти изменения вносить?
согласен, но бывает, юзер передумал или увидел что он ошибся.
В Microsoft Excel есть действительно интересная функция, известная как «Сравнить файлы», которая позволяет сравнивать два конкретных файла или книги и помогает выделить эти изменения после сравнения. В этой статье мы объясним, как сравнить две книги Excel.
Зачем сравнивать таблицы Excel?
Есть много ситуаций, когда вам может понадобиться сравнить два листа Excel:
- У вас возникли проблемы с поиском обновлений, внесенных вашими коллегами в ваши книги Excel.
- Когда у вас есть две версии книги, например, предыдущая и текущая, иногда требуется продемонстрировать эти изменения вашему руководителю в компании.
- Возможно, вам придется проанализировать свой предыдущий рост. Для этого вам необходимо проверить изменения данных в текущем файле по сравнению с предыдущим файлом.
Проблема еще больше, когда несколько человек работают над одной книгой в Microsoft Excel и, следовательно, может быть несколько версий одного и того же файла. Во всех таких сценариях вы должны сравнить предыдущую и текущую книги, и это будет хорошей идеей, прежде чем продолжить.
Что можно сравнить в двух книгах Excel?
Функция сравнения фактически связана с различными функциями, с помощью которых она может дать нам правильные результаты. Всего существует 14 областей, в которых работает эта функция, и чаще вы можете выбирать по своему усмотрению. Вы можете установить или снять отметку с нужных опций. Ниже приведены области, в которых работает эта функция:
Ошибки формул SysGen
Ошибка имен SysGen
Подключение для передачи данных
Защита листа / книги
В каждой из этих областей книга сравнения предоставляет вам действительно быстрый метод одновременного сравнения двух похожих книг.
Как сравнить две книги Excel?
Первый шаг в сравнении двух книг Excel для активации вкладки «Запрос» в Microsoft Excel. Если у вас этого нет прямо сейчас, просто выполните следующие действия:
Активация вкладки запроса в Microsoft Excel
- Перейдите в меню «Файл> Параметры». Это откроет вам небольшое окно «Параметры Excel».
- Теперь посетите раздел «Надстройки». Вы увидите раздел активных и неактивных надстроек приложений.
- В разделе «Неактивные надстройки приложений» щелкните надстройку «Запросить» и активируйте ее.
- Выберите надстройку «Запросить» и посмотрите в нижней части «Управление». В раскрывающемся списке выберите «Надстройки COM», нажмите кнопку «Перейти…», а затем нажмите «ОК».
- Теперь на ленте появится вкладка «Запрос».
Включение вкладки "Запрос" в Excel
Шаги по сравнению двух книг Excel
Допустим, у нас есть две книги для сравнения их данных. Здесь мы используем «предыдущую» рабочую книгу и «текущую» рабочую книгу. В «Раннюю» рабочую книгу мы внесли некоторые изменения и сохранили ее под новым названием «Текущая» рабочая книга. Более ранняя рабочая книга содержит старые данные, а «Текущая» содержит некоторые измененные данные.
- Шаг 1. У нас есть более ранняя рабочая тетрадь, подобная этой:
Предыдущая рабочая тетрадь
- Шаг 2: Вот наша текущая рабочая книга, в которой, как вы можете видеть, есть некоторые измененные данные.
Текущая рабочая тетрадь
- Шаг 3: Теперь перейдите к любой из рабочих тетрадей. Щелкните вкладку «Запрос», а затем в разделе «Сравнение» щелкните «Сравнить файлы».
Выберите "Сравнить" на вкладке "Запрос".
- Шаг 4: Вы увидите окно сравнения, в котором вас попросят выбрать файл сравнения и с каким файлом вы хотите его сравнить. В нашем случае для более раннего файла используется опция «Сравнить », а для текущего файла – опция «Кому». Вы можете легко обменивать файлы, нажав на кнопку «Поменять файлы».
Выберите файлы для сравнения
Результаты сравнения двух файлов Excel
Когда у вас будет больше изменений, Excel сгруппирует изменения по разным категориям и перечислит все изменения между двумя сравниваемыми файлами.
Статьи по Теме:
Что нельзя сравнивать?
В некоторых областях эта функция не будет работать напрямую. Эта функция, прежде всего, находит все листы в книге с одинаковыми именами и одинаковыми именами. Он сравнивает значение ячейки по ячейке на каждом листе. Проекты VBA, форматы ячеек, комментарии, диаграммы, объекты в этой функции не сравниваются.
Эта функция работает только с двумя книгами, не более того. Как вы видели выше, в окнах сравнения вы можете добавить только 2 книги. Итак, это могло быть ограничением этой функции. Если какая-либо книга защищена, эта функция не позволит сравнивать этот файл. Сначала вам нужно снять защиту, затем вы можете использовать его для сравнения.
Заключение
В приведенном выше руководстве мы поделились тем, что вы можете сравнивать, как сравнивать и что нельзя сравнивать с помощью функции сравнения Microsoft Excel. Теперь убедитесь, что вы сравниваете похожие файлы, чтобы получить правильный результат. Вы не сможете использовать объект, проект VBA и другие подобные вещи, как описано выше. Вы можете одновременно сравнивать только две книги.
Допустим, у вас есть две книги Excel или две версии одной и той же книги, которые вы хотите сравнить. А может быть, вам нужно найти потенциальные проблемы, такие как введенные вручную (а не вычисляемые) итоги или формулы с ошибками. С помощью Microsoft средство сравнения электронных таблиц можно запустить отчет о различиях и проблемах, которые она находит.
Важно: средство сравнения электронных таблиц доступна только в Office профессиональный плюс 2013, Office профессиональный плюс 2016, Office профессиональный плюс 2019 или Приложения Microsoft 365 для предприятий.
Открытие средства сравнения электронных таблиц
На экране "Начните"нажмитекнопку средство сравнения электронных таблиц . Если вы не видите параметр средство сравнения электронных таблиц, начните вводить средство сравнения электронных таблиц ,а затем выберите его параметр.
Помимо средства сравнения электронных таблиц имеется и соответствующая программа для Access — средство сравнения баз данных (Майкрософт). Кроме того, Office профессиональный плюс версии или Приложения Microsoft 365 для предприятий.
Сравнение двух книг Excel
Выберите Главная > Сравнить файлы.
Появляется диалоговое окно Compare Files (Сравнение файлов).
Щелкните значок синей папки рядом с полем Compare (Сравнить), чтобы перейти к более ранней версии книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.
Щелкните значок зеленой папки рядом с полем To (С чем), чтобы перейти к книге, которую нужно сравнить с более ранней версией, а затем нажмите кнопку ОК.
Совет: Можно сравнивать два файла с одинаковыми именами, если они хранятся в разных папках.
В левой области выберите элементы, которые нужно включить в результаты сравнения книг: установите или снимите флажки у таких элементов, как Formulas (Формулы), Macros (Макросы) или Cell Format (Формат ячеек). Или просто выберите вариант Select All (Выделить все).
Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле "Compare" (Сравнить) (обычно это более старый файл), а книга в правой части — файлу, указанному в поле "To" (С чем) (обычно это более новый файл). Подробные сведения отображаются в области под двумя частями таблицы. Изменения выделяются разными цветами в соответствии с их типом.
Интерпретация результатов
Примечание: Даже если лист скрыт, он все равно сравнивается и отображается в результатах.
Различия разного типа выделяются с помощью цвета заливки ячейки или цвета шрифта текста. Например, ячейки с введенными значениями (не с формулами) выделяются заливкой зеленого цвета в расположенных рядом частях таблицы и шрифтом зеленого цвета в области результатов. В левой нижней части указаны условные обозначения, поясняющие значения цветов.
В приведенном здесь примере результаты 4-го квартала (Q4) в более ранней версии не являются окончательными. Последняя версия книги содержит окончательные цифры для 4-го квартала в столбце E.
В результатах сравнения ячейки E2:E5 в обеих версиях выделены зеленым фоном, что означает изменение введенного значения. В результате изменения этих значений изменились и вычисляемые результаты в столбце YTD — ячейки F2:F4 и E6:F6 выделены сине-зеленым фоном, что означает изменение вычисленного значения.
Вычисляемый результат в ячейке F5 тоже изменился, но гораздо важнее то, что в более ранней версии его формула содержала ошибку (суммировались только ячейки B5:D5, а значение Q4 пропускалось). После обновления книги формула в ячейке F5 была исправлена и теперь имеет вид =СУММ(B5:E5).
Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit (Размер ячеек по размеру данных).
Надстройка средства диагностики в Excel
Помимо средства сравнения электронных таблиц в Excel 2013 есть надстройка средства диагностики электронных таблиц, при включении которой появляется вкладка "Inquire" (Запрос). На этой вкладке можно проанализировать книгу, посмотреть на связи между ячейками, листами и другими книгами, а также убрать с листа лишнее форматирование. Если в Excel открыты две книги, которые нужно сравнить, можно запустить средство сравнения электронных таблиц с помощью команды "Сравнить файлы".
Если вкладка Inquire (Запрос) не Excel вкладки Inquire (Запрос) в Excel, см. включить надстройку Inquire(Запрос) . Подробнее об инструментах этой надстройки см. в статье Возможности средства диагностики электронных таблиц.
Дальнейшие действия
Если у вас есть критически важные Excel книги или базы данных Access в организации, можно установить средства управления электронными таблицами и базами данных Майкрософт. Microsoft сервер аудита и управления предоставляет мощные функции управления изменениями для файлов Excel и Access и дополняется сервером Microsoft средство обнаружения и оценки риска Server, который предоставляет функции инвентаризации и анализа, помогая снизить риск, связанный с использованием средств, разработанных конечными пользователями в Excel и Access.
Читайте также: