Если вы когда-нибудь использовали в своих вычислениях в Excel логические функции (типа ЕСЛИ) или функции подстановки (типа ВПР), то, вполне возможно, уже сталкивались с ситуацией, когда две визуально неотличимых ячейки Microsoft Excel почему-то считает неодинаковыми, что автоматически приводит к неработоспособности функций, ломает фильтрацию, сводные таблицы и т.д. Причин для такого может быть много:
- лишние пробелы
- использование английских букв похожих на русские и наоборот (русская «эс» и английская «си», например)
- использование цифр вместо букв (ноль вместо заглавной «О»)
- разные версии тире (короткое, длинное, среднее)
- неразрывный пробел вместо обычного
- невидимые символы переноса строки (Alt+Enter)
- … и т.д.
Вопрос в том, где именно эти отличия находятся и как их быстро найти в исходных данных?
Самое простое — это сделать за пару минут прямо в Excel своими руками универсальный сравниватель, который будет наглядно показывать в чем именно не совпадают две любые заданные текстовые строки:
В жёлтые ячейки нужно будет скопировать и вставить исходные тексты.
Затем каждую строку мы разбираем на отдельные символы с помощью функции ПСТР (MID) в ячейке D4:
Если у вас последние версии Excel 2021 или Office 365, то для подобного также можно использовать новую функцию ПОСЛЕД (SEQUENCE):
Дополнительно имеет смысл вывести над непустыми зелёными ячейками коды каждого символа, чтобы чётко видеть причину отличий, если они будут. Это легко сделать с помощью функции КОДСИМВ (CHAR):
Ну и для пущей наглядности можно добавить ещё и правило условного форматирования, чтобы подсветить красным отличия в конкретных символах. Для этого выделяем наши зелёные ячейки с символами, на которые мы разобрали каждый текст и выбираем Главная — Условное форматирование — Создать правило (Home — Conditional Formatting — Create Rule). В открывшемся окне выбираем последний тип правила Использовать формулу для определения форматируемых ячеек (Use a formula to detect cells to format) и вводим в нижнее поле формулу для проверки результатов посимвольного сравнения:
Обратите особое внимание на знаки доллара, используемые здесь для закрепления ссылок на сравниваемые ячейки — мы фиксируем строки (3 и 6), а не столбцы (D).
Общее количество отличий, т.е. несовпадений можно легко вычислить с помощью вот такой формулы:
Здесь мы поячеечно (т.е. посимвольно) сравниваем наши зеленые диапазоны, получая на выходе логические значения ИСТИНА и ЛОЖЬ (TRUE и FALSE). Затем используется двойное бинарное отрицание — 2 знака минус, выполняющие функцию безобидного двойного умножения на минус 1, что заставляет Excel конвертировать логические значения в 1 и 0 соответственно. Потом останется лишь сложить все получившиеся нули и единицы, но мы, в данном случае, делаем это не с помощью стандартной функции СУММ (SUM), а используем её «старшего брата» — функцию СУММПРОИЗВ (SUMPRODUCT), которая позволяет вводить формулы массива (а это как раз она!) без сочетания клавиш Ctrl+Shift+Enter.
И пару общих рекомендаций вдогон:
- Используйте шрифты, созданные для программирования (Consolas, FiraCode, Monaco…) — их дизайн разработан так, чтобы похожие в других шрифтах символы (например цифра 1 и строчная латинская «L» или ноль и заглавная «О») выглядели по-разному.
- Для быстрого поиска латиницы в русском тексте можно просто задать для ячеек шрифт, в котором кириллица и латиница выглядят по-разному (например, Bauhaus93, Copper и т.п.)
В любом случае, можете просто скачать файл по ссылке в начале этой статьи и просто использовать этот готовый сравниватель для поиска «блох» в ваших данных. С ним это будет сделать гораздо легче.
Ссылки по теме
- Поиск символов латиницы в русском тексте
- Подсветка лишних пробелов
- Поиск точных совпадений с учётом регистра функцией СОВПАД (EXACT)
Как сравнить два документа Word
Данный параметр позволяет сравнить два документа и вывести на экран только несовпадающие фрагменты. Сравниваемые документы не изменяются. При таком способе сравнения различия между документами всегда отображаются в новом, третьем документе.
Если нужно сравнить исправления, сделанные несколькими рецензентами, не следует использовать данный параметр. Выберите команду Объединение исправлений от нескольких авторов в одном документе.
Откройте документы, которые нужно сравнить.
На вкладке Рецензирование в группе Сравнить нажмите кнопку Сравнить.
Выберите пункт Сравнение двух версий документа (юридическое примечание).
В поле Исходный документ укажите документ, который будет использоваться в качестве исходного.
В поле Измененный документ выберите документ, который нужно сравнить с уже открытым документом.
Щелкните Больше, а затем укажите параметры сравнения документов. Рядом с Показывать изменения выберите отображение изменений на уровне знаков или на уровне слов.
Если результаты сравнения не требуется выводить в третьем документе, укажите документ, в котором должны быть отображены изменения.
Важно: Все параметры, выбранные в разделе Больше, будут использоваться по умолчанию при последующем сравнении документов.
Нажмите кнопку ОК.
Если какая-либо из версий документа содержит записанные исправления, на экране отобразится соответствующее сообщение. Чтобы принять исправления и сравнить документы, нажмите кнопку Да.
Откроется новый документ, в котором будут приняты исправления, записанные в исходном документе, а изменения, отмеченные во втором из сравниваемых документов, отобразятся в виде исправлений.
Как сравнить два документа Word на различия
При совместной работе с текстовыми документами не редко возникает необходимость сравнить два документа Word, которые редактировались разными людьми. Например, такая ситуация может возникнуть если вы отправили файл на проверку и не дождавшись исправленной версии файла продолжили редактировать свою версию документа.
Если документ небольшой, то его можно сравнить вручную. Но, с большими текстами такой вариант отнимет слишком много времени. Вместо этого лучше использовать встроенный в Word инструмент, который позволяет сравнить два документа на различия. Сейчас мы расскажем, как это делается в Word 2003, 2007, 2010, 2013 и 2016.
Сравнение документов в Word 2007, 2010, 2013 и 2016
Если вы пользуетесь Word 2007, 2010, 2013 или 2016 то для того, чтобы сравнить два документа на различия вам нужно перейти на вкладку «Рецензирование» и нажать на кнопку «Сравнить» (она находится ближе к правому верхнему углу окна). В результате появится выпадающее меню с двумя пунктами: «Сравнить» и «Объединить». Для сравнивания двух документов Word на различия вам нужно выбрать вариант «Сравнить».
Дальше откроется окно «Сравнение версий». Здесь нужно выбрать два документа Word: «Исходный документ» и «Измененный документ». Для этого воспользуйтесь кнопками с изображением папки, которая находятся рядом с каждых из документов.
После выбора исходного и измененного документа нажмите на кнопку «Больше», чтобы получить доступ к дополнительным настройкам сравнения документов.
В результате перед вами появится список всех доступных настроек. Здесь главное выбрать способ сравнения двух документов. Вы можете сравнивать их по знакам или по словам.
После внесения всех необходимых настроек нажмите на кнопку «ОК» и Word загрузит документы для сравнения. В левой части окна будет отображаться список всех найденных отличий, по центру сравниваемый текст с подсвеченными отличиями, в правом верхнем углу исходный документ, а в правом нижнем углу измененный документ. Изучив эту информацию, вы без труда сможете сравнить два документа Word на различия.
Сравнение документов в Word 2003
Если вы пользуетесь Word 2003, то для сравнения двух документов нужно открыть меню «Сервис» и выбрать пункт «Сравнить и объединить исправления». После чего нужно указать второй документ, с которым нужно сравнить текущий файл.
После этого Word загрузит второй файл и подсветит все изменения.
Здесь вы сможете просто сравнить два файл и ознакомиться со списком изменений либо принять изменения из второго файла. Для этого нужно кликнуть правой кнопкой мыши по исправлению и выбрать пункт «Принять».
Как сравнить два документа Word на различия текста?
Инструкции по программам и сервисам
Как сравнить два документа Word на различия текста?
Добрый день дорогие друзья! В результате создания текстового файла, нам приходится подвергать его многократным изменениям. И примечательно то, что какая-нибудь неокончательная версия документа может быть согласована Заинтересованным Лицом 1. В итоге финальный текстовый файл, который согласован другим ЗЛ 2, может значительно отличаться от уже ранее согласованной версии ЗЛ 1. И такие ситуации могут повторяться каждый день. А ЗЛ может быть и 10. Давайте изучим инструмент, который нас подстрахует и частично автоматизирует процесс сравнения двух документов.
Как в ворде сравнить два документа одновременно?
В продолжении обучающего курса Microsoft Word я вам расскажу, как сравнивать два текстовых документа на отличия одновременно. Знание данной функции редактора MC Word поможет Вам убедиться в неизменности концепции текста или наоборот, выяснить что и где конкретно поменялось. Каждый символ (буква, знак препинания, даже пробел) будет выделен при процедуре сравнения прямо в документе. Итак поехали…
За пример возьмем самую сложную скороговорку, которую мне пришлось повстречать, и хаотично изменим слова в разных частях нашего документа, для того чтобы я смог показать как сравнить два текстовых файла. Один из документов (исходный) я назову «1», а другой соответственно «2».
Как сравнить два документа word и посветить изменения?
- Для начала открываем программу Microsoft Word
- Переходим во вкладку «Рецензирование», в разделе «Сравнение» нажимаем «Сравнить»
- Перед вами появляется выпадающее окно «Сравнение версий» в который сейчас и нужно загрузить два типа документов: исходный и измененный.
- После того как выбрали две версии текстового документа, нажимаете «ОК»
- В новом окне откроются результаты сравнения, и выглядеть это будет примерно так:
В выделенном квадрате с цифрой «1» появятся все удаленные и вставленные слова. Правее, зона с цифрой «2» Показывает сравниваемый документ с наличием в тексте и удаленные и вставленные на их место слова. Рабочая область с цифрой «3» отображает нам исходный документ (до внесения правок), в то время как пространство с цифрой «4» показывает нам измененный документ с внесенными правками.
- Оставшееся рабочее поле с номером «5» это набор инструментов, необходимый для редактирования окончательной версии разрабатываемого документа.
Нажимая «Принять»/«Отклонить» в первый раз, Вы выбираете то слово которое Вы примете или отклоните, во второй раз нажав Вы измените на один из предложенных вариантов.
Красные зачеркнутые слова помечены на удаление, а красный подчеркнутые будут заменены.
Как сравнить два документа если нет ворда?
Что же делать если под рукой нет ворда. Многие люди имея в наличии самый распространённый текстовый редактор, не знают о его огромном потенциале, выходящего за рамки форматирования текста. И если Вы не запомните как сравнить или в у Вас другой текстовый редактор, то выход один, маркировать помеченные на удаление/вставляемые слова одним из следующих способов:
— красным цветом заливать текст на удаление, зеленным на добавление (цвета можете выбрать произвольные, но для этой операции эти самые распространённые).
— зачеркивать удаляемый текст/подчеркивать вставляемый и т.д.
Вдруг если Вы хотите скачать Microsoft Word то переходите на следующую ссылку:
Microsoft word 2016 скачать торрент (torrent) бесплатно для windows 10
Если Вы хотите узнать больше о возможностях ворда, то переходите сюда
Ворд для чайников с инструкциями по работе
По всем остальным вопросам можете обращаться в комментарии.
Многие пользователи часто работают на компьютере с различными текстовыми файлами, при этом в документы вносятся исправления, поэтому бывает необходимо сравнить документы Word. Над одним документом может работать несколько человек, которые правят текст исходного документа, вставляя туда дополнения или убирая лишнее.
Это может быть договор, некий юридический документ, соглашение и т. п. При простом чтении не всегда заметны мелкие исправления, сделанные партнером в период согласования документа.
Содержание:
- Как сравнить два документа Ворд вручную рядом — 1 способ
- Как сравнить два документа Ворд одновременно — 2 способ
- Сохранение измененного документа на компьютере
- Как сравнить 2 документа Word в ABBYY Comparator
- Как сравнить 2 документа в Ворде онлайн
- Выводы статьи
Абзац, предложение, несколько слов или даже одна запятая могут существенно изменить содержание важного документа с соответствующими негативными последствиями. Поэтому необходимо предварительно сравнить документы Ворд, чтобы сразу увидеть все изменения, сделанные партнером.
Это поможет избежать ошибок, например, внесение в текст невыгодных условий, увидеть юридические ловушки перед подписанием и принятием данного документа между обеими сторонами.
Документы можно сравнить визуально, прокручивая их рядом друг с другом при синхронной прокрутке. Чтобы избежать недоразумений и увидеть все изменения, лучше выбрать самый эффективный способ: необходимо сравнить два документа Word и подсветить различия между ними. Вы сразу увидите, какие правки были внесены в исходный документ в период согласования.
Принимая во внимание все вышесказанное, у пользователя возникает закономерный вопрос о том, как сравнить два документа Word. Есть несколько способов для решения этой проблемы. Можно сравнить два открытых документа или два закрытых файла.
В зависимости от ситуации, сравнить текст в двух документах Word можно с помощью двух методов:
- Визуальное сравнение текста документов вручную.
- Автоматическое сравнение содержимого документов с помощью программы или сервиса.
В этой статье вы найдете инструкции о том, как сравнить два документа Word на различия, используя само приложение MS Office, стороннее программное обеспечение, а также узнаете, как сравнить документы Word онлайн.
Это руководство подходит для использования в разных версиях текстового редактора Microsoft Word: в MS Word 2019, MS Word 2016, MS Word 2013, MS Word 2010, MS Word 2007.
Как сравнить два документа Ворд вручную рядом — 1 способ
Сейчас мы разберем, как сравнить 2 документа Word, если они одновременно открыты. В этом случае, мы можем просмотреть открытые документы рядом, расположив их вплотную друг к другу.
Чтобы сравнить исправления в двух открытых документах визуально, нужно сделать следующее:
- Откройте оба документа Word, текст в которых необходимо сравнить.
- В одном из открытых документов перейдите во вкладку «Вид».
- В группе «Окно» нажмите на кнопку «Рядом».
- В открывшемся окне «Сравнить рядом» выберите другой документ, если в это время на ПК открыто несколько файлов Word.
- На экране компьютера появятся два окна открытых документа, расположенные рядом друг с другом.
- Во вкладке «Вид» войдите в группу «Окно».
- Щелкните по опции «Синхронная прокрутка».
Вы можете просмотреть одновременно оба документа с помощью прокрутки, чтобы увидеть отличия.
Есть еще одна подсказка, позволяющая понять, вносились ли изменения в документ Word. В левой части нижней панели окна документа отображается число слов в тексте каждого документа. У вас есть возможность сравнить также количество символов, имеющихся в каждом из документов.
Чтобы выйти из режима просмотра рядом, во вкладке «Вид», в группе «Окно» нажмите на кнопку «Рядом».
Как сравнить два документа Ворд одновременно — 2 способ
А можно ли сравнить версии документов Word, если они не открыты? Конечно, в этом нам поможет функция сравнения, которая называется «Юридическое примечание». При использовании этого способа, нам необходимо сравнить два документа Word и подсветить исправления.
Пройдите последовательные шаги:
- Запустите программу Word на компьютере, открыв в приложении один из требуемых документов или просто пустой документ.
- Перейдите во вкладку «Рецензирование».
- В группе «Сравнение» нажмите на кнопку «Сравнить».
- В открывшемся контекстном меню выберите «Сравнить…».
Это сравнение двух версий документа (юридическое примечание).
- В окне «Сравнение версий» сначала выберите на компьютере «Исходный документ», а затем «Измененный документ».
- Если вам необходимы дополнительные опции сравнения, нажмите на кнопку «Больше».
- Измените параметры сравнения по своему усмотрению.
Включите настройки, по которым необходимо отслеживать изменения. Обратите внимание на параметр «Отображение изменений». Здесь можно выбрать степень сравнения — «по знакам» или «словам».
В разделе «Показать изменения» выбираем место, где разместится это сравнение: «в исходном документе», «в измененном документе», или в «новом документе». Рекомендуемое значение — «в новом документе».
- В окне «Сравнение версий» нажмите на кнопку «ОК».
- На экране откроется программа Word с результатами сравнения в четырех окнах:
- окно исправлений документа;
- окно сравниваемого (объединенного) документа;
- окно исходного документа;
- окно измененного документа.
Справа друг над другом находятся два окошка с исходным (вверху) и измененным (внизу) документами. В центре размещен сравниваемый документ со всеми внесенными изменениями. Слева отображено окно исправлений с информацией и списком изменений.
- Щелкните по вставке в исправлениях, чтобы переместиться к данному исправлению в основном окне.
Измененный текст будет подсвечен цветом. При необходимости, отредактируйте текст в окне объединенного документа.
Для большего удобства вы можете выбрать горизонтальное расположение области «Исправления». Во вкладке «Рецензирование», в группе «Запись исправлений» нажмите на стрелку около кнопки «Область проверки», выберите один из вариантов: «Вертикальная область проверки…» или «Горизонтальная область проверки…».
Сохранение измененного документа на компьютере
После ознакомления с результатами проверки, вы можете сохранить на ПК объединенный документ Word со всеми или только некоторыми исправлениями.
В случае согласия с изменениями, выполните следующие действия:
- Из вкладки «Рецензирование» переместитесь в группу Изменения».
- Нажмите на кнопку «Принять», или нажмите на стрелку для выбора другого подходящего параметра:
- Принять и перейти к следующему.
- Принять исправление.
- Принять все исправления.
- Принять все исправления и прекратить отслеживание.
Если вы не согласны с исправлениями в тексте документа, нажмите на кнопку «Отклонить и перейти к следующему». Щелкните на стрелку для выбора одного из вариантов действий:
- Отклонить и перейти к следующему.
- Отклонить все исправления.
- Отклонить все исправления и перейти к следующему.
- Сохраните документ Word в качестве нового файла на компьютере, а исходный и измененный файлы останутся на вашем ПК в первоначальном виде.
Как сравнить 2 документа Word в ABBYY Comparator
Приложение «ABBYY Сравнение документов» предназначено для сравнения различных типов документов, в том числе файлов Microsoft Office. Это платное приложение находит несоответствия в документах и показывает разницу.
Пройдите шаги:
- В окне программы ABBYY Comparator добавьте в соответствующие области первый и второй документы.
- Нажмите на кнопку «Найти различия».
- В окне приложения отобразится информация об обнаруженных несоответствиях.
В случае необходимости, сохраните на своем ПК отчет о сравнении файлов в формате PDF или DOCX.
Как сравнить 2 документа в Ворде онлайн
Может так случится, что у вас не будет под рукой компьютера с программой Word. Например, вам нужно сравнить файлы с телефона, а в мобильной версии офиса нет подобного функционала.
В этом случае, вы можете сравнить два документа Word онлайн. Существует несколько сервисов в интернете, способных решать эту задачу.
Мы воспользуемся помощью онлайн сервиса Embedika:
- Войдите на страницу «Сравнение документов»: https://compare.embedika.ru/.
- Выберите файлы со своего мобильного устройства или с компьютера, и загрузите поочередно их в первую и во вторую области страницы.
- Нажмите на кнопку «Сравнить».
- Посмотрите на результат этой операции.
Выводы статьи
При работе с документами Word, пользователю может понадобится выполнить сравнение двух редакций документа, если над ним работало несколько человек. В этом случае, необходимо сравнить два документа на соответствия друг другу, чтобы узнать о внесенных исправлениях, сделанных партерами или коллегами. Сделать это можно несколькими способами: в программе Word, используя сторонний софт или онлайн сервис в интернете.
Похожие публикации:
- Как вставить дату и время в Word
- Как поставить ударение над буквой в Ворде — 3 способа
- Как сделать оглавление (содержание) в документе Word
- Как убрать пустую страницу в Ворде — 6 способов
- Как выделить текст в Word: разные способы
Skip to content
На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные совпадения либо уникальные значения. Ладно, обратный отсчет начался!
Мы все время от времени делаем сравнение данных в Excel. Microsoft Excel предлагает ряд опций для сравнения и сопоставления данных, но большинство из них ориентированы на поиск в одной колонке. Встроенный инструмент удаления дубликатов, доступный в Excel 2019-2010, не может справиться с этой задачей, поскольку он не умеет сравнивать данные между двумя столбиками. Кроме того, он может только удалять дубликаты. Других возможностей — таких как выделение или раскраска, увы, нет :-(.
В этом руководстве мы рассмотрим несколько методов сравнения двух столбцов в Excel и нахождения совпадений и различий между ними.
- Как сравнить 2 столбца построчно?
- Построчное сравнение нескольких столбцов.
- Ищем совпадения и различия в двух столбцах.
- Как извлечь данные для совпадающих значений.
- Выделяем цветом совпадения и различия
- Как выделить цветом уникальные значения и дубликаты в нескольких столбцах сразу?
- Как сопоставить два значения в разных столбцах?
- Быстрый способ сравнить два столбца или списка без формул.
Как сравнить 2 столбца в Excel по строкам.
Когда вы выполняете анализ данных в Excel, одной из наиболее частых задач является сравнение данных нескольких колонок в каждой отдельной их строке. Эту задачу можно выполнить с помощью функции ЕСЛИ , как показано в следующих примерах.
1. Проверяем совпадения или различия в одной строке.
Чтобы выполнить такое построчное сравнение, используйте популярную функцию ЕСЛИ, которая сравнивает первые две ячейки каждого из них. Введите её в какой-либо другой столбик той же строки, а затем скопируйте ее вниз, перетащив маркер заполнения (маленький квадрат в правом нижнем углу). При этом курсор изменится на знак плюса:
Чтобы найти в соответствующей строке позиции с одинаковым содержимым, A2 и B2 в этом примере, запишите:
=ЕСЛИ(A2=B2; «Совпадает»; «»)
Чтобы найти позиции в одной строке с разным содержимым, просто замените «=» знаком неравенства:
=ЕСЛИ(A2<>B2; «НЕ совпадает»;””)
И, конечно же, ничто не мешает найти совпадения и различия с помощью одной формулы:
=ЕСЛИ(A2=B2; «Совпадает»; «НЕ совпадает»)
Результат может выглядеть примерно так:
Как видите, одинаково хорошо обрабатываются числа, даты, время и текст.
2. Сравниваем построчно с учетом регистра.
Как вы, наверное, заметили, формулы из предыдущего примера игнорируют регистр при сравнении текстовых значений, как в строке 10 на скриншоте выше. Если вы хотите найти совпадения с учетом регистра, используйте функцию СОВПАД (EXACT в английской версии):
=ЕСЛИ(СОВПАД(A2; B2); «Одинаковый»; «»)
Чтобы найти различия с учетом регистра в одной строке, введите соответствующий текст («Уникальный» например) в третий аргумент функции ЕСЛИ:
=ЕСЛИ(СОВПАД(A2; B2); «Одинаковый»; «Уникальный»)
Сравните несколько столбцов построчно
Мы можем ставить перед собой следующие цели:
- Найти строки с одинаковыми значениями во всех из них.
- Найти строки с одинаковыми значениями в любых двух.
Пример 1. Найдите полное совпадение по одной строке.
Если в вашей таблице три или более колонки, и вы хотите найти строки с одинаковыми записями во всех из них, функция ЕСЛИ с оператором И подойдет для вас:
=ЕСЛИ(И(A2=B2; A2=C2); «Полное совпадение»; «»)
Если в вашей таблице очень много колонок, более элегантным решением будет использование функции СЧЁТЕСЛИ :
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2; $A2)=3; «Полное совпадение»; «»)
где 3 — количество сравниваемых колонок.
Или можно использовать —
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2; $A2)=СЧЁТЗ(A2:C2); «Полное совпадение»; «»)
Пример 2. Найдите хотя бы 2 совпадения в данных.
Если вы ищете способ сравнить данные на предмет наличия любых двух или более ячеек с одинаковыми значениями в одной строке, используйте функцию ЕСЛИ с оператором ИЛИ:
=ЕСЛИ(ИЛИ(A2=B2; B2=C2; A2=C2); «Найдены одинаковые»; «»)
Если есть много данных для сравнения, ваша конструкция с оператором ИЛИ может стать слишком громоздкой. В этом случае лучшим решением было бы добавить несколько функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько раз текущее значение из первой колонки встречается во всех данных, находящихся правее него, второй СЧЁТЕСЛИ определяет то же самое для значения из второй колонки, и так далее. Если счетчик равен 0, возвращается надпись «Все уникальные», в противном случае — «Найдены одинаковые». Например:
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0;»Все уникальные»;»Найдены одинаковые»)
Могу предложить также более компактный вариант выявления совпадений — формула массива:
{=ЕСЛИ(СУММ(СЧЁТЕСЛИ(A2:D2;A2:D2))>4;»Совпадения»;»»)}
или
{=ЕСЛИ(СУММ(СЧЁТЕСЛИ(A2:D2;A2:D2))>СЧЁТЗ(A2:D2);»Совпадения»;»»)}
Попробуйте — получите тот же результат. Также не забудьте нажать Ctrl + Shift + Enter, чтобы ввести всё правильно.
Как сравнить два столбца в Excel на совпадения и различия?
Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые записи), которые находятся в колонке A, но их нет в B. То есть, исходные данные из А мы сравниваем с В.
Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B;$A2)=0 в логический тест ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено хотя бы 1 совпадение).
Например, следующая формула ЕСЛИ/СЧЁТЕСЛИ выполняет поиск значения из A2 по всему столбцу B. Если совпадений не найдено, возвращается «Нет совпадений в B», в противном случае — пустую строку:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B; $A2)=0; «Нет совпадений в В»; «»)
Тот же результат может быть достигнут при использовании функции ЕСЛИ всесте с ЕОШИБКА и ПОИСКПОЗ:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ($A2;$B$2:$B$10;0));»Уникальное»; » Найдено в B»)
Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):
=ЕСЛИ(СУММ(—($B$2:$B$10=$A2))=0; «»;» Найдено в B»)
Если вы хотите, чтобы одно выражение определяло как дубликаты, так и уникальные значения, поместите текст совпадений в пустые двойные кавычки («») в любой из приведенных выше формул. Например:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B; $A2)=0; «Уникальное»; «Дубликат»)
Думаю, вы понимаете, что точно таким же образом можно наоборот сравнивать В с А.
Как сравнить два списка в Excel и извлечь совпадающие данные?
Иногда вам может потребоваться не только сопоставить две колонки в двух разных таблицах, но и извлечь соответствующие записи из второй таблицы. В Microsoft Excel предусмотрена специальная функция для этих целей — функция ВПР.
Кроме того, в отдельной статье мы подробно рассмотрели 4 способа, как сравнить таблицы при помощи формулы ВПР.
В качестве альтернативы вы можете использовать более мощную и универсальную комбинацию ИНДЕКС и ПОИСКПОЗ.
Например, следующее выражение сравнивает названия продуктов в колонках D и A, и если совпадение найдено, соответствующая цифра продаж извлекается из B. Если совпадения не найдено, возвращается ошибка #Н/Д.
=ИНДЕКС($B$2:$B$6;ПОИСКПОЗ($D2;$A$2:$A$6;0))
Сообщение об ошибке в таблице выглядит не слишком красиво. Поэтому обработаем это выражение при помощи ЕОШИБКА:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$6;ПОИСКПОЗ($D2;$A$2:$A$6;0));»»)
Теперь мы видим либо число, либо пустое значение. Никаких ошибок.
Как выделить совпадения и различия в 2 столбцах.
Когда вы сравниваете наборы данных в Excel, вы можете захотеть «визуализировать» элементы, которые присутствуют в одном, но отсутствуют в другом. Вы можете закрасить такие позиции любым цветом по вашему выбору с помощью формул. И вот несколько примеров с подробными инструкциями.
1. Выделите совпадения и различия построчно.
Чтобы сравнить два столбца в Excel и выделить те позиции в первом, которые имеют идентичные записи во втором по той же строке, выполните следующие действия:
- Выберите область, в которой вы хотите выделить.
- Щелкните Условное форматирование> Новое правило…> Используйте формулу.
- Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок таблицы). Пожалуйста, дважды проверьте, что вы используете относительную ссылку на строку (без знака $), как записано выше.
Чтобы выделить различия между колонками A и B, создайте правило с формулой =$B2<>$A2
Если вы новичок в условном форматировании Excel, смотрите пошаговые инструкции в статье Как закрасить строку или столбец по условию.
2. Выделите уникальные записи в каждом столбце.
Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:
- Предметы только в первом списке (уникальные)
- Предметы только во втором списке (уникальные)
- Элементы, которые есть в обоих списках (дубликаты).
О выделении дубликатов — смотрите пример выше. А сейчас рассмотрим, как выделить неповторяющиеся элементы в каждом из списков.
Предположим, что ваш список 1 находится в колонке A (A2:A8), а список 2 — в колонке C (C2:C8). Вы создаете правила условного форматирования с помощью следующих формул:
Выделите уникальные значения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)=0
Выделите уникальные значения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)=0
И получите следующий результат:
3. Выделите дубликаты в 2 столбцах.
Если вы внимательно следовали предыдущему примеру, у вас не возникнет трудностей с настройкой СЧЁТЕСЛИ, чтобы она находила совпадения, а не различия. Все, что вам нужно сделать, это установить счетчик больше нуля:
Вновь используем условное форматирование при помощи формулы.
Выделите совпадения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)>0
Выделите совпадения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)>0
Выделите цветом различия и совпадения в нескольких столбцах
При сравнении значений в нескольких наборах данных построчно, самый быстрый способ выделить одинаковые — создать правило условного форматирования. А самый быстрый способ скрыть различия — воспользоваться инструментом «Выделить группу ячеек», как показано в следующих примерах.
1. Как выделить совпадения.
Чтобы выделить строки, которые имеют одинаковые значения по всей длине, создайте правило условного форматирования на основе одного из следующих выражений:
=И($A2=$B2; $A2=$C2)
или
=СЧЁТЕСЛИ($A2:$C2; $A2)=3
Где A2, B2 и C2 — самые верхние в вашем диапазоне, а 3 — количество колонок для сравнения.
Конечно, можно не ограничиваться сравнением только 3 колонок. Вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбиках.
И еще один способ выделения цветом повторяющихся значений в нескольких столбцах. Снова используем условное форматирование. Выделяем нужную область, затем на ленте в меню условного форматирования выбираем Правила выделения ячеек — Повторяющиеся значения. Определяем желаемое оформление, получаем картину подобную той, что вы видите ниже.
Кстати, на последнем этапе вы можете выбрать не повторяющиеся, а уникальные значения. Способ, конечно, незамысловатый, но, возможно, он вам будет полезен.
2. Как выделить различия.
Чтобы быстро выделить позиции с разными значениями в каждой отдельной строке, вы можете использовать функцию Excel «Выделить группу ячеек».
- Выберите диапазон ячеек, который вы хотите сравнить. В этом примере я выбрал диапазон от A2 до C10.
По умолчанию самая верхняя координата выбранного диапазона является активной ячейкой, и все значения в той же строке будут сравниваться с нею. Она при выделении области имеет белый цвет, а все остальные ячейки выбранного диапазона выделены серым. В этом примере активной является A2, поэтому столбец сравнения — A.
Чтобы изменить столбец сравнения, используйте клавишу TAB для перемещения по диапазону слева направо или клавишу Enter для перемещения сверху вниз. Если нужно перемещаться снизу вверх, то нажмите и удерживайте SHIFT, и вновь используйте ТАВ — будете двигаться не вниз, а вверх. Вы увидите, как ваше белое пятно перемещается, и соответственно изменяется активный столбец.
- На вкладке «Главная» нажмите «Найти и выделить» > « Выделить группу ячеек». Затем выберите «Отличия по строкам» и нажмите «ОК» .
- Позиции, значения которых отличаются от ячеек сравнения в каждой строке, выделяются. Если вы хотите закрасить выделенные ячейки каким-либо цветом, просто щелкните значок «Цвет заливки» на ленте и выберите нужный цвет.
Как сравнить два значения в отдельных столбцах.
Фактически, сравнение двух ячеек — частный случай сравнения двух колонок в Excel построчно, за исключением того, что вам не нужно копировать формулы.
Например, для сравнения ячеек A1 и C1 можно использовать:
Для совпадений: =ЕСЛИ(A1=C1; «Совпадает»; «»)
Для различий: =ЕСЛИ(A1<>C1; «Уникальные»; «»)
Чтобы узнать о некоторых других способах сравнения ячеек в Excel, см. Как сравнивать значения в ячейках Excel .
Для более эффективного анализа данных вам могут потребоваться более сложные формулы, и вы можете найти несколько хороших идей в следующих руководствах:
- Использование функции ЕСЛИ в Excel
- Функция ЕСЛИ: проверяем условия с текстом
Быстрый способ сравнения двух столбцов или списков без формул.
Теперь, когда вы знаете, что предлагает Excel для сравнения и сопоставления столбцов, позвольте мне продемонстрировать вам альтернативное решение, которое может сравнить 2 списка с разным количеством столбцов на предмет дубликатов (совпадений) и уникальных значений (различий).
Надстройка Ultimate Suite умеет искать идентичные и уникальные записи в одной таблице, а также сравнивать две таблицы, находящиеся на одном листе или в двух разных листах или даже в разных книгах.
В рамках этой статьи мы сосредоточимся на функции под названием «Сравнить таблицы (Compare Tables)» , которая специально разработана для сравнения двух списков по любым указанным вами столбцам. Сравнение двух наборов данных по нескольким столбцам является реальной проблемой как для формул Excel, так и для условного форматирования, но этот инструмент легко справляется с этим.
Для начала рассмотрим самый простой случай – сравним два столбца на совпадения и различия.
Предположим, у нас имеется два списка товаров. Нужно сравнить их между собой, как ранее мы делали при помощи формул.
Запускаем инструмент сравнения таблиц и выбираем первый столбец. При необходимости активируем создание резервной копии листа.
На втором шаге выбираем второй столбец для сравнения.
На третьем шаге нужно указать, что именно мы ищем – дубликаты либо уникальные значения.
Далее указываем столбцы для сравнения. Поскольку столбцов всего два, то здесь все достаточно просто:
На пятом шаге выберите, что нужно сделать с найденными значениями – удалить, выбрать, закрасить цветом, скопировать либо переместить. Можно добавить столбец статуса подобно тому, как мы это делали ранее при помощи функции ЕСЛИ. С использованием формул вы кроме того сможете разве что закрасить ячейки. Здесь же диапазон возможностей гораздо шире. Но мы выберем простой и наглядный вариант – заливку ячеек цветом.
Ячейки списка 1, дубликаты которых имеются в списке 2, будут закрашены цветом.
А теперь повторим все описанные выше шаги, только будем сравнивать список 2 с первым. И вот что мы в итоге получим:
Не закрашенные цветом ячейки содержат уникальные значения. Красиво и наглядно.
А теперь давайте попробуем сравнить сразу несколько столбцов. Допустим, у нас есть два экземпляра отчёта о продажах. Они расположены на разных листах нашей книги Excel. Список товаров совершенно одинаков, а вот сами цифры продаж отличаются кое-где.
Действуя совершенно аналогичным образом, как это было описано выше, выбираем эти две таблицы для сравнения. На третьем шаге выбираем поиск уникальных значений, чтобы можно было выбрать и выделить именно несовпадения в данных.
Устанавливаем соответствие столбцов, как это показано на рисунке ниже.
Для наглядности вновь выбираем заливку цветом для несовпадающих значений.
И вот результат. Несовпадающие строки закрашены цветом.
Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.
Вот какими способами вы можете сравнить столбцы в Excel на наличие дубликатов и уникальных значений.
Если у вас есть вопросы или что-то осталось неясным, напишите мне комментарий, и я с радостью уточню это подробнее. Спасибо за чтение!
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
9 способов сравнить две таблицы в Excel и найти разницу — В этом руководстве вы познакомитесь с различными методами сравнения таблиц Excel и определения различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для создания отчета о различиях, выделить… 
Сравнение ячеек в Excel — Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству… 
Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.… 
Как проверить правильность ввода данных в Excel? — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить… 
Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ… 
Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать в отдельной колонке таблицы, происходит рост или снижение продаж.… 
3 примера, как функция ЕСЛИ работает с датами. — На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно применять так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это…
Во время работы с текстовыми документами часто приходится сравнивать их по содержанию. Эта необходимость возникает, если над документом работали несколько человек или документ был отправлен учителю на согласование и редактирование, нужно быстро найти все сделанные второй стороной изменения. Но и сам пользователь может создать несколько копий документа и запутаться: какая версия последняя, и какие исправления были внесены. Рассмотрим все возможности быстрого, а главное автоматического сравнения двух документов.
1. Сравнение двух документов в MSWord (начиная с версий программы MSWord 2007, 2010, 2013)
Воспользуемся функцией Юридическое примечание, позволяющей выполнять автоматическое сравнение документов.
Запускаем программу MS Word. На ленте открываем вкладку Рецензирование, выбираем раздел Сравнить и раскрываем возможные варианты команды Сравнить… (смотри на Рисунок 1), выбираем первую команду – Сравнение двух версий документа (Юридическое примечание).
Рисунок 1 Запуск автоматического СРАВНЕНИЯ двух текстовых документов
Откроется окно, в котором указываете путь к хранению исходного и измененного документов. Для указания пути необходимо воспользоваться пиктограммой с изображением желтой папки, как показано на Рисунке 2.
Рисунок 2 Внешний вид окна Сравнение версий
Командная кнопка Больше>> (Рисунок 2) предоставляет больше дополнительных настроек отображения (подсвечивания) выполненных изменений. При ее активации окно Сравнения версий примет следующий вид (смотри Рисунок 3)
Рисунок 3 Дополнительные возможности при автоматическом сравнении текстовых документов
В открывшемся окне в разделе Параметры сравнения отметить галочками те изменения, которые необходимо отобразить в процессе автоматического сравнения документов.
По умолчанию окно Сравнения выглядит следующим образом.
Рисунок 4. Окно автоматического сравнения двух версий документа
Рассмотрим области окна автоматического сравнения документов в Юридическом примечании.
- Слева приводится Сводка исправлений: что именно было исправлено и кем (если имя пользователя указано в настройках Word).
- В центре – Сравниваемый документ – документ с внесенными исправлениями (показываются красным: добавление и удаление слов).
- Справа сверху: исходный документ, а ниже – измененный документ.
Область сверки Сводка исправлений можно настроить в виде горизонтальной панели рецензирования. Для этого необходимо выполнить последовательно следующие команды: вкладыш Рецензирование => Область проверки => Горизонтальная панель рецензирования.
Рисунок 5. Настройка Горизонтальной панели рецензирования
В этом случае окно автоматического сравнения двух документов будет выглядеть следующим образом
Рисунок 6. Окно автоматического сравнения документов при горизонтальной панели рецензирования
Перемещаться по исправлениям можно как с помощью левой клавиши мыши, кликая на текст в нужном месте документа, так и с помощью Вкладки Рецензирование – Область проверки.
Рисунок 7 Работаем с опцией ИЗМЕНЕНИЯ
Выявленные изменения необходимо либо Принять, чтобы оно осталось в измененном документе и больше не показывалось как исправление, либо Отклонить, если не согласны. Передвигаться по исправлениям можно с использованием клавиш: Назад и Далее (смотри Рисунок 7).
Чтобы отклонить или принять все исправления, используем команду Принять (или Отклонить). С помощью левой клавиши мыши раскрываем список опций команды Принять/Отклонить (стрелочка вниз) и из предложенных вариантов выбираем нужную, например: Применить (Отклонить) все исправления в документе.
Рисунок 8 Окно команды ПРИНЯТЬ
2. Сравнение двух блоков текста в интернет-браузере
При сравнении содержимого двух документов также можно воспользоваться специализированными сервисами, предоставляемыми интернет-браузерами. Они удобны при определении различий в текстах как англоязычных документов, так и при работе с русскоязычными текстами, например: Text-Compare (http://text-compare.com/), TextDiff (http:// www.textdiff.com/) и Quick Diff (http://www.quickdiff.com/). С их помощью можно сравнивать фрагменты текста, таблицы и даже программный код.
Рассмотрим кратко технологию сравнения документов в каждом из трех рекомендуемых сервисов. Общим для них является размещение двух фрагментов в соответствующие окна, а затем инициализация процесса сравнения.
Text-Compare (http://text-compare.com/). Для сравнения двух фрагментов в данном сервисе достаточно скопировать эти фрагменты в соответствующие окна, а затем запустить процесс сравнения (командная кнопка Compare). Различающиеся, а также отсутствующие в одном из сравниваемых документов фрагменты текста будут выделенными (отличающиеся элементы выделяются цветом при сравнении таблиц, в программных кодах при сравнении дополнительно отмечаются строки с отличиями).
Рисунок 9 Окна Text-Compare до выполнения операции сравнения и после ее выполнения
TextDiff (http://www.textdiff.com/). Окно сервиса представляет собой два окна, в которые помещаются сравниваемые фрагменты текста, таблицы или программного кода.
Рисунок 10 Окно сервиса TextDiff
Результаты отображения процесса сравнения с использованием TextDiff представляют один результирующий фрагмент, в котором зеленым цветом выделяется текст, появившийся во втором фрагменте (то есть отсутствующий в первом), а красным – удаленный текст.
Сравним с предыдущим сервисом: при сравнении текстовых блоков и небольших таблиц такой результат может быть удобен, для сравнения больших таблиц такой результат – сложный для вычитки различий, поскольку найденные различия отображаются построчно в текстовом формате без номеров строк и сохранения видимости столбцов.
Рисунок 11 Результата сравнения с использованием сервиса TextDiff
Принцип представления результатов в Quick Diff точно такой же, как и в TextDiff. Отличие состоит в том, что удаленные из второго фрагмента данные не только отображаются другим цветом, но еще и зачеркиваются. При этом к сложностям сравнения текста добавляется проблема – некорректное распознавание кириллицы (смотри Рисунок 12).
Рисунок 12.Окно сервиса Quick Diff после выполнения операции сравнения
Подведем итоги:
- сервис Text-Compare может использоваться для сравнения текстов, таблиц и программного кода безо всяких ограничений;
- сервис TextDiff может использоваться для сравнения текстов, программных кодов и небольших табличных фрагментов;
- сервис Quick Diff прекрасно сравнивает тексты (не на русском языке), программные коды и может использоваться для сравнения небольших табличных фрагментов.
Материал подготовлен Шутилиной Л.А., методистом ГМЦ ДОгМ