Каждый месяц работник отдела кадров получает список сотрудников вместе с их окладами. Он копирует список на новый лист рабочей книги Excel. Задача следующая: сравнить зарплату сотрудников, которая изменилась по отношению к предыдущему месяцу. Для этого необходимо выполнить сравнение данных в Excel на разных листах. Воспользуемся условным форматированием. Таким образом мы не только автоматически найдем все отличия в значениях ячеек, но и выделим их цветом.

Сравнение двух листов в Excel

В фирме может быть более ста сотрудников, среди которых одни увольняются другие трудоустраиваются, третьи уходят в отпуск или на больничный и т.п. В следствии чего могут возникнуть сложности со сравнением данных по зарплате. Например, фамилии сотрудников будут постоянно в разной последовательности. Как сделать сравнение двух таблиц Excel на разных листах?

Решить эту непростую задачу нам поможет условное форматирование. Для примера, возьмем данные за февраль и март, как показано на рисунке:

Чтобы найти изменения на зарплатных листах:

После ввода всех условий для форматирования Excel автоматически выделил цветом тех сотрудников зарплаты которых изменились по сравнению с предыдущим месяцем.



Принцип сравнения двух диапазонов данных в Excel на разных листах:

В определенном условии существенное значение имеет функция ПОИСКПОЗ. В ее первом аргументе находится пара значений, которая должна быть найдена на исходном листе следующего месяца, то есть «Март». Просматриваемый диапазон определяется как соединение значений диапазонов, определенных именами, в пары. Таким образом выполняется сравнение строк по двум признакам – фамилия и зарплата. Для найденных совпадений возвращается число, что по сути для Excel является истиной. Поэтому следует использовать функцию =НЕ(), которая позволяет заменить значение ИСТИНА на ЛОЖЬ. Иначе будет применено форматирование для ячеек значение которых совпали. Для каждой не найденной пары значений (то есть – несоответствие) &B2&$C2 в диапазоне Фамилия&Зарплата, функция ПОИСКПОЗ возвращает ошибку. Ошибочное значение не является логическим значением. Поэтому исползаем функцию ЕСЛИОШИБКА, которая присвоит логическое значение для каждой ошибки – ИСТИНА. Это способствует присвоению нового формата только для ячеек без совпадений значений по зарплате в отношении к следующему месяцу – марту.

Друзья, рад вас приветствовать на страницах моего блога. Сегодня хочу рассказать вам о 4 способах сравнения файлов по содержимому. Необходимость найти различия возникает довольно часто. Приходится делать сравнение файлов по типу или размеру. Сравнение файлов в Word и Excel тоже весьма распространённая процедура в офисах и конторах, где постоянно приходится работать с текстовыми документами и таблицами.

Но для веб-мастера или владельца сайта часто бывает необходимо сравнить два файла по содержимому. И речь идёт не о текстовых файлах, хотя и они тоже анализируются так же, а о форматах.html, .css, .php и так далее. Тех файлах, где нужно найти изменения в коде. И порой изменение одного лишь знака может обеспечить уйму трудностей с достижением результата.

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

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

Способ 1. Сравнение файлов в программе Total Commander.

Этой программой я пользуюсь давно. Для работы с файлами, на мой взгляд, это одна из лучших программ. Программа Total Commander платная, но можно в течение 30 дней пользоваться и пробной версией, которая по истечении времени также будет работать, но с предложением о покупке. Скачать пробную версию можно .

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

А теперь разберём инструмент сравнения файлов по содержимому, который также позволяет редактировать и копировать данные в процессе анализа.

Итак, запускаете Total Commander – в одной из панелей выбираете первый файл для сравнения – во второй панели открываете папку со вторым файлом.

Откроется новое окно, также разделённое на две панели. В самом верху, в каждой панели будет срока для выбора файла. В одной из них первый файл будет уже выбран (но может быть и пустым). Для выбора файла нужно нажать на небольшую кнопочку со стрелочками (>>) .

Выбрав файлы для сравнения, нажимаете на кнопку «Сравнить» . В каждой панели появится содержимое выбранного файла. Отличия будут отмечены фоновым цветом.

Для внесения изменений в файл достаточно нажать на кнопку «Редактировать» . И можно приступать к внесению изменений. Также здесь будут доступны функции копирования и отката, поиска и изменение кодировки.

Если вы внесли изменения в файл, то после закрытия окна сравнения, будет предложено сохранить результат.

Способ 2. Сравнение файлов в Notepad++.

Если вам приходилось, хоть когда-либо, редактировать файлы html, css и php. То наверняка вы слышали о бесплатном редакторе кода Notepad++. Скачать программу можно .

Эта прекрасная программа, которая к неисчислимым её достоинствам позволяет ещё и файлы сравнивать по их содержимому.

Но для этого потребуется установить небольшой плагин. Делается всё это непосредственно внутри самого редактора.

Итак, запускаете редактор – переходите в пункт меню «Плагины » — «Plugin Manager» — «Show Plugin Manager» .

В новом окне выбираете плагин « Compare» и жмёте кнопку « Install» .

После установки плагина можно приступать к сравнению содержимого файлов. Для этого нужно открыть два файла – перейти в пункт меню «Плагины» — « Compare» — « Compare (Alt+ D)» .

Результат сравнения файлов будет представлен в отдельных панелях. То есть в левой будет первый файл, а в правой второй. Напротив строк, в которых найдены отличия будет предупреждающий знак и подсветка фоновым цветом.

Строка, где найдены малые отличия, будет подсвечиваться жёлтым цветом, и обозначена восклицательным знаком.

Строка, которая полностью отличается, будет отмечена на одной панели красным минусом на другой зелёным плюсом. И подсвечена соответствующим цветом.

Разумеется, найдя отличие, вы сразу можете внести необходимые поправки и сохранить изменения.

Завершить сравнение файлов можно комбинацией клавиш Ctrl+ Alt+ D .

Способ 3. Сравнение содержимого двух файлов в программе WinMerge.

Эта программа позволяет сравнивать не только содержимое файлов, но и содержимое целых папок. Этим она похожа на Тотал Коммандер, только программа бесплатная. Скачать её можно .

Установка программы стандартная и не составит труда. Поэтому на этом останавливаться не будем, а сразу перейдём к процессу сравнения двух файлов.

Запустив программу, нужно выбрать файлы для сравнения. Открываете пункт меню «Файл» — «Открыть» .

Результат сравнения представлен в двух панелях, так же как и в других примерах. Только здесь ещё и выделяется именно тот фрагмент кода, который отличается в конкретной строке.

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

Способ 4. Сравнение файлов с помощью командной строки Windows.

Этот способ позволяет сравнивать содержимое файлов без сторонних программ. Достаточно воспользоваться стандартным инструментом операционной системы Windows.

Также этот способ не позволяет редактировать файлы. Для этого нужно использовать другие инструменты. Но, тем не менее с поставленной задачей вы справитесь без дополнительных программ.

Для этого открываете «Пуск» — «Все программы» — «Стандартные» — «Командная строка» . И вводите вот эту команду:

Fc /N путь к первому файлу путь ко второму файлу

выглядит это так:

Fc /N C:\Мои документы\file1.txt C:\Мои документы\file2.txt

Готовый результат будет выглядеть так:

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

Итак, о способах сравнения файлов я вам рассказал, а теперь предлагаю посмотреть видеоурок в, котором я показываю сравнение двух файлов всеми способами.

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

На этом сегодня всё, всем желаю успехов и хорошего настроения и до встречи в новых статьях и видеоуроках!

С уважением, Максим Зайцев.

Чтение этой статьи займёт у Вас около 10 минут. В следующие 5 минут Вы сможете легко сравнить два столбца в Excel и узнать о наличии в них дубликатов, удалить их или выделить цветом. Итак, время пошло!

Excel – это очень мощное и действительно крутое приложение для создания и обработки больших массивов данных. Если у Вас есть несколько рабочих книг с данными (или только одна огромная таблица), то, вероятно, Вы захотите сравнить 2 столбца, найти повторяющиеся значения, а затем совершить с ними какие-либо действия, например, удалить, выделить цветом или очистить содержимое. Столбцы могут находиться в одной таблице, быть смежными или не смежными, могут быть расположены на 2-х разных листах или даже в разных книгах.

Представьте, что у нас есть 2 столбца с именами людей – 5 имён в столбце A и 3 имени в столбце B . Необходимо сравнить имена в этих двух столбцах и найти повторяющиеся. Как Вы понимаете, это вымышленные данные, взятые исключительно для примера. В реальных таблицах мы имеем дело с тысячами, а то и с десятками тысяч записей.

Вариант А: оба столбца находятся на одном листе. Например, столбец A и столбец B .

Вариант В: Столбцы расположены на разных листах. Например, столбец A на листе Sheet2 и столбец A на листе Sheet3 .

В Excel 2013, 2010 и 2007 есть встроенный инструмент Remove Duplicate (Удалить дубликаты), но он бессилен в такой ситуации, поскольку не может сравнивать данные в 2 столбцах. Более того, он может только удалить дубликаты. Других вариантов, таких как выделение или изменение цвета, не предусмотрено. И точка!

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш. Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

Вариант В: два столбца находятся на разных листах (в разных книгах)

Обработка найденных дубликатов

Отлично, мы нашли записи в первом столбце, которые также присутствуют во втором столбце. Теперь нам нужно что-то с ними делать. Просматривать все повторяющиеся записи в таблице вручную довольно неэффективно и занимает слишком много времени. Существуют пути получше.

Показать только повторяющиеся строки в столбце А

Если Ваши столбцы не имеют заголовков, то их необходимо добавить. Для этого поместите курсор на число, обозначающее первую строку, при этом он превратится в чёрную стрелку, как показано на рисунке ниже:

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Дайте названия столбцам, например, “Name ” и “Duplicate? ” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с “Duplicate? “, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate , и нажмите ОК .

Вот и всё, теперь Вы видите только те элементы столбца А , которые дублируются в столбце В . В нашей учебной таблице таких ячеек всего две, но, как Вы понимаете, на практике их встретится намного больше.

Чтобы снова отобразить все строки столбца А , кликните символ фильтра в столбце В , который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

Изменение цвета или выделение найденных дубликатов

Если пометки “Duplicate ” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1 , чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

Удаление повторяющихся значений из первого столбца

Отфильтруйте таблицу так, чтобы показаны были только ячейки с повторяющимися значениями, и выделите эти ячейки.

Если 2 столбца, которые Вы сравниваете, находятся на разных листах , то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

Нажмите ОК , когда Excel попросит Вас подтвердить, что Вы действительно хотите удалить всю строку листа и после этого очистите фильтр. Как видите, остались только строки с уникальными значениями:

Если 2 столбца расположены на одном листе , вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А , сделайте следующее:

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

Сравним две таблицы имеющих практически одинаковую структуру. Таблицы различаются значениями в отдельных строках, некоторые наименования строк встречаются в одной таблице, но в другой могут отсутствовать.

Пусть на листах Январь и Февраль имеется две таблицы с оборотами за период по соответствующим счетам.

Как видно из рисунков, таблицы различаются:

1. Наличием (отсутствием) строк (наименований счетов). Например, в таблице на листе Январь отсутствует счет 26 (см. файл примера ), а в таблице на листе Февраль отсутствуют счет 10 и его субсчета.
2. Разными значениями в строках. Например, по счету 57 обороты за январь и февраль не совпадают.

Если структуры таблиц примерно одинаковы (большинство наименований счетов (строк) совпадают, количество и наименования столбцов совпадают), то можно сравнить две таблицы. Проведем сравнение двумя способами: один проще в реализации, другой нагляднее.

Простой вариант сравнения 2-х таблиц

Сначала определим какие строки (наименования счетов) присутствуют в одной таблице, но отсутствуют в другой. Затем, в таблице, в которой меньше строк отсутствует (в наиболее полной таблице), выведем отчет о сравнении, представляющий собой разницу по столбцам (разница оборотов за январь и февраль).

Основным недостатком этого подхода является, то, что отчет о сравнении таблиц не включает строки отсутствующие в наиболее полной таблице. Например, в рассматриваемом нами случае, наиболее полной таблицей является таблица на листе Январь, в которой отсутствует счет 26 из февральской таблицы.

Чтобы определить какая из двух таблиц является наиболее полной нужно ответить на 2 вопроса: Какие счета в февральской таблице отсутствуют в январской? и Какие счета в январской таблице отсутствуют в январской?

Это можно сделать с помощью формул (см. столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));"Нет";"Есть") и =ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));"Нет";"Есть")

Сравнение оборотов по счетам произведем с помощью формул: =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7

В случае отсутствия соответствующей строки функция ВПР() возвращает ошибку #Н/Д, которая обрабатывается связкой функций ЕНД() и ЕСЛИ() , заменяя ошибку на 0 (в случае отсутствия строки) или на значение из соответствующего столбца.

С помощью можно выделить расхождения (например, красным цветом).

Более наглядный вариант сравнения 2-х таблиц (но более сложный)

По аналогии с задачей решенной в статье можно сформировать список наименований счетов, включающий ВСЕ наименования счетов из обоих таблиц (без повторов). Затем вывести разницу по столбцам.

Для этого необходимо:

1. С помощью =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));"") сформировать в столбце А перечень счетов из обоих таблиц (без повторов);
2. С помощью =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , где Список -

Казалось бы простая задача - сравнение таблиц. Точнее сравнение двух столбцов таблицы на совпадения или различия. Логично предложить что Excel идеальное средство решения проблемы, но увы простого бесплатного сравнение таблиц в Excel я не нашёл, разве что кроме примитивного «строка1=строка2». В реальности необходима некоторая обработка строк до сравнения, так как они могут содержать лишние пробелы, знаки препинания и так далее. Как итог было решено написать утилиту сравнивающую два текстовых файла построчно и с обработкой строк по выбору пользователя…

Обработка строк именно через файлы была выбрана как универсальная. Неважно какой источник данных, просто ли список или таблица Excel. Обычно всё можно скопировать в текстовый файл. Итак приступим непосредственно к программе.

Скачайте и распакуйте программу. В исходном варианте она содержит три файла «Compare.exe» - сама программа. «Список 1.txt» и «Список 2.txt» - пустые текстовые файлы. Как раз в них нужно вставить ваши строки для сравнения. Запускаем:

По умолчанию на мой взгляд стоят оптимальные настройки сравнения. Окно с примером того, как будет работать сравнения таблиц создано только для подгонки настроек под ваши задачи и общего понимания происходящего. Не сравнивайте реальные данные в примере, т.к. эти окошки вмещают не более 32КБайт текста, остальное обрубается без предупреждения. Вы можете получить ошибочный результат ! Программа имеет подсказку и при наведении мыши в окне показывается краткое описание настройки или элемента.

После того, как наиграетесь с примером сравнения - скопируйте ваши данные для сравнения в файлы «Список 1.txt» и «Список 2.txt» и с выбранными ранее настройками нажмите кнопку «Обработать файлы». Во время обработки файлов на кнопке появляется надпись «Идёт обработка» красным цветом, дождитесь окончания этого процесса. По окончанию загляните в место откуда запустили программу, в папке с программой в зависимости от настроек появляются файлы указанные в примере сравнения. При каждом новом сравнении или открытие/закрытие программы все файлы кроме «Список 1.txt» и «Список 2.txt» удаляются.

И немного о скорости сравнения. Большинство реальных задач решаются практически мгновенно. Ну а мои тесты такие (процессор типа Intel Core под сокет LGA 775 c частотой 2ГГц):

Сравнение 2-х списков размером 1Мб каждый (25 символов на строку и 39 тысяч строк в каждом списке), для сравнения очевидно нужно каждую строку 1-ого списка сравнить со всеми строками другого. Итого получаем 1,521 миллиарда сравнений строк. Время выполнения около 20 секунд. Расход памяти менее 10Мб.

Сравнение 2-х списков размером 10Мб каждый (25 символов на строку и 390 тысяч строк в каждом списке). Итого получаем 152,1 миллиарда сравнений строк. Время выполнения около часа. При этом программа отхватывает себе около 200Мб оперативной памяти. Хотя такие размеры - это уже территория баз данных. В этой программе все разумные способы повышения скорости я уже использовал.

Алгоритм работы и параметры сравнения

Алгоритм работы таков, что при любых настройках из строк удаляются всё символы, кроме латинских и русских букв, цифр и знаков точки и запятой. Разумеется удаляются все лишние пробелы между словами и пробелы по краям строки.

Искать совпадающие строки и Искать отличающиеся строки - тут всё понятно, будут искаться совпадающие строки или отличающиеся. Совпадающие будут записываться в файл «Совпадающие.txt». При поиске отличающихся строк они будут записываться в два файла «Несовпадения 1.txt» и «Несовпадения 2.txt» для Списков 1 и 2 соответственно. Так же в области примера сравнения вместо одного окна появляется два.

ВНИМАНИЕ! При сравнении списков на совпадение есть особенность, так как одинаковые строки имеются в обоих списках, в результат попадают строки из списка 1. Более аккуратно оформленный текст помещайте в список 1 при сравнении на совпадения.

Исправлять ошибки раскладки клавиатуры - до Punto Switcher тут конечно далеко. Имеются ввиду ошибки набора похожих букв не в той раскладке (C,H,P и т.д.). Например русской «с» и латинской. Они находятся на одной клавише и если слово начинается с «с», то можно набрать первую букву в английской раскладке, а потом переключиться на русскую или же наоборот. Алгоритм замены такой, что если в слове русских букв больше чем английских, то английские меняются на русские и наоборот.

Исправлять Ё(ё) на Е(е) - просто все «ё» заменяются на «е».

Сравнивать без учёта регистра - все буквы стают большими.

Сравнивать по уникальным строкам - Если параметр включён, то сначала списки проверяются на совпадающие строки. Если строка повторяется например 5 раз, то один экземпляр этой строки остаётся в списке для сравнения, а 4-е отправляются в список «повторов». Повторы для каждого списка слов свои.

Без этого параметра строки сравниваются как бы попарно. Например при сравнении на совпадающие строки если в 1-ом списке будет 2-е одинаковые строки, а во 2-ом списке ещё 3-и такие строки, то результатом будут только две строки, т.к. 3-ей строке не нашлось пары с которой бы она совпадала. Если для этого примера переключить сравнения на не совпадающие строки, то в результат несовпадающих попадёт одна строка из списка 2-а, т.к. она ни с чем не совпадает.

Используя сравнение по уникальным строкам можно найти повторяющиеся строки в списке. Для этого можно например заполнить строками только файл «Список 1.txt» и сравнить с пустым файлом «Список 2.txt» и тогда в файле «ПовторСп1.txt» появятся потворяющиеся строки из списка 1.

Повторы только для строк из результата - работает только вместе со сравнением по уникальным строкам. Без этого параметра в списки повторов попадают все повторяющиеся строки. Если включить, то в повторы будут попадать только строки присутствующие в результате. Количество строк попавших в повторы аналогично и равно количеству повторений в начальном списке минус 1.

Удалять точки и запятые и Удалять все пробелы - просто удаляются и всё тут.