Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.
1 Сравнение с помощью простого поиска
При наличии небольшой по размеру таблицы заниматься сравнением можно практически вручную. Для этого достаточно выполнить несколько простых действий.
- Перейти на главную вкладку табличного процессора.
- В группе «Редактирование» выбрать пункт поиска.
- Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
- Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.
Если значение обнаружено, результатом станет выделение нужной ячейки. Однако с помощью такого способа можно работать только с небольшими столбцами. И, если это просто цифры, так можно сделать и без поиска — определяя совпадения визуально. Впрочем, если в колонках записаны большие объемы текста, даже такая простая методика позволит упростить поиск точного совпадения.
2 Операторы ЕСЛИ и СЧЕТЕСЛИ
Еще один способ сравнения значений в двух столбцах Excel подходит для таблиц практически неограниченного размера. Он основан на применении условного оператора ЕСЛИ и отличается от других методик тем, что для анализа совпадений берется только указанная в формуле часть, а не все значения массива. Порядок действий при использовании методики тоже не слишком сложный и подойдет даже для начинающего пользователя Excel.
- Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
- В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
- Протянуть формулу до конца столбца.
Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.
Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.
Тот же способ подойдет и для сравнения сразу большого количества колонок с данными на точное совпадение не только значения, но и строки. Для этого применяется уже не оператор ЕСЛИ, а функция СЧЕТЕСЛИ. Принцип написания и размещения формулы похожий.
Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.
Если поставить вместо тройки двойку, результатом будет поиск только тех совпадений с первой колонкой, которые присутствуют в одном из других столбцов. Причем, тройные дубликаты формула проигнорирует. Так же как и совпадения второй и третьей колонки.
3 Формула подстановки ВПР
Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.
С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».
Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.
5 Сравнение с выделением совпадений цветом
В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.
Порядок действий для применения методики следующий:
- Перейти на главную вкладку табличного процессора.
- Выделить диапазон, в котором будут сравниваться столбцы.
- Выбрать пункт условного форматирования.
- Перейти к пункту «Правила выделения ячеек».
- Выбрать «Повторяющиеся значения».
- В открывшемся окне указать, как именно будут выделяться совпадения в первой и второй колонке. Например, красным текстом, если цвет остальных сообщений стандартный черный. Затем указать, что выделяться будут именно повторяющиеся ячейки.
Теперь можно снять выделение и сравнить совпадающие значения, которые будут заметно отличаться от остальной информации. Точно так же можно выделить, например, и уникальную информацию. Для этого следует выбрать вместо «повторяющихся» второй вариант — «уникальные».
6 Надстройка Inquire
Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.
Для использования способа придется расположить столбцы или целые блоки информации в разных книгах и удалить все остальные данные, кроме сравниваемой информации. Кроме того, для проверки необходимо, чтобы оба файла были одновременно открытыми.
Процесс использования надстройки включает такие действия:
- Перейти к параметрам электронной таблицы.
- Выбрать сначала надстройки, а затем управление надстройками COM.
- Отметить пункт Inquire и нажать «ОК».
- Перейти к вкладке Inquire.
- Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
- В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.
У каждого варианта сравнения — свое цветовое решение. Так, зеленым цветом на примере выделены отличия. У совпадающих данных отсутствует выделение. А сравнение расчетных формул показало, что результаты отличаются все — и для выделения использован бирюзовый цвет.
Читайте также:
- 5 программ для совместной работы с документами
-
Как в Экселе протянуть формулу по строке или столбцу: 5 способов
Skip to content
В этом руководстве вы познакомитесь с различными методами сравнения таблиц Excel и определения различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для создания отчета о различиях, выделить несовпадения с помощью условного форматирования и многое другое.
Когда у вас есть две похожие книги Эксель или, лучше сказать, две версии одной и той же книги, что вы обычно хотите с ними делать в первую очередь? Сравнить их на предмет различий, а затем, возможно, объединить в один файл. Кроме того, такая операция может помочь вам обнаружить потенциальные проблемы, такие как битые ссылки, повторяющиеся записи, несогласованные формулы.
- Визуальное сравнение таблиц.
- Быстрое выделение различий.
- Использование формулы сравнения.
- Как вывести различия на отдельном листе.
- Как можно использовать функцию ВПР.
- Выделение различий условным форматированием.
- Сопоставление при помощи сводной таблицы.
- Сравнение таблиц при помощи Pover Query.
- Инструмент сравнения таблиц Ultimate Suite.
Итак, давайте более подробно рассмотрим различные методы сравнения таблиц Excel и выявления различий между ними.
Просмотр рядом, чтобы сравнить таблицы.
Если у вас относительно небольшие файлы и вы внимательны к деталям, этот быстрый и простой способ сравнения может вам подойти. Я говорю о режиме «Просмотр рядом», который позволяет расположить два окна Excel рядом. Вы можете использовать этот метод для визуального сравнения двух таблиц или двух листов из одной книги.
Сравните 2 книги.
Предположим, у вас есть отчеты о продажах за два месяца, и вы хотите просмотреть их оба одновременно, чтобы понять, какие товары показали лучшие результаты в этом месяце, а какие – в прошлом.
Чтобы просмотреть два файла Эксель рядом, сделайте следующее:
- Откройте оба файла.
- Перейдите на вкладку «Вид» и нажмите кнопку «Рядом». (1) Это оно!
По умолчанию два отдельных окна Excel отображаются горизонтально.
Чтобы разделить окна по вертикали, нажмите кнопку «Упорядочить все» (3) и выберите «Рядом» (4):
В результате два отдельных окна будут расположены, как на скриншоте.
Если вы хотите прокручивать оба листа одновременно, чтобы сравнивать данные строка за строкой, убедитесь, что параметр синхронной прокрутки (2) включен. Он обычно включается автоматически, как только вы активируете режим одновременного просмотра двух книг.
Расположите рядом несколько таблиц Excel.
Чтобы просматривать более двух файлов одновременно, откройте все книги, которые вы хотите сравнить, и нажмите кнопку «Рядом».
Появится диалоговое окно «Сравнить рядом», в котором вы выберете файлы, которые будут отображаться вместе с активной книгой.
Чтобы просмотреть все открытые файлы одновременно, нажмите кнопку «Упорядочить все» и выберите предпочтительное расположение: мозаичное, горизонтальное, вертикальное или каскадное.
Для небольших таблиц вы легко сможете визуально сравнить их данные. Хотя, конечно, риск ошибки из-за человеческого фактора здесь присутствует.
Сравните два листа в одной книге.
Иногда 2 листа, которые вы хотите сравнить, находятся в одной книге. Чтобы просмотреть их рядом, выполните следующие действия.
- Откройте файл, перейдите на вкладку «Вид» и нажмите кнопку «Новое окно».
- Это действие откроет тот же файл в дополнительном окне.
- Включите режим просмотра «Рядом», нажав соответствующую кнопку на ленте.
- Выберите лист 1 в первом окне и лист 2 во втором окне.
Быстрое выделение значений, которые различаются.
Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии или же отсутствии отличий между записями, вам нужно на вкладке «Главная», выбрать кнопку «Найти и выделить», предварительно выделив диапазон, где надо сравнить данные в Эксель.
В открывшемся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».
К сожалению, это нормально работает только для сравнения 2 столбцов (или строк), а не всей таблицы целиком. Кроме того, строки должны быть одинаковым образом отсортированы, поскольку ячейки сравниваются построчно. Если у вас товары отсортированы по-разному, либо вообще различный ассортимент, то никакой пользы от этого метода не будет.
Формула сравнения.
Это самый простой способ соотнесения таблиц в Excel, который позволяет идентифицировать в них ячейки с разными значениями.
Простейший вариант – сопоставление двух таблиц, находящихся на одном листе. Можно соотносить как числовые, так и текстовые значения, всего-навсего прописав в одной из соседних ячеек формулу их равенства. В результате при тождестве ячеек мы получим сообщение ИСТИНА, в противном случае — ЛОЖЬ.
Предположим, у нас имеется два прайс-листа (старый и новый), в которых на некоторые товары различаются цены. При этом порядок следования товаров одинаков. Поэтому мы можем при помощи простейшей формулы прямо на этом же листе сравнить идентичные ячейки с данными.
=G3=C3
Результатом будет являться либо ИСТИНА (в случае совпадения), либо ЛОЖЬ (при отрицательном результате).
Таким же образом можно производить сравнение данных в таблицах, которые расположены на разных листах. Процедура сравнения практически точно такая, как была описана выше, кроме того факта, что при создании формулы придется переключаться между листами. В нашем случае выражение будет иметь следующий вид:
=G3=Лист2!C3
Если ваши таблицы достаточно велики, то довольно утомительно будет просматривать колонку I на предмет поиска слова ЛОЖЬ. Поэтому может быть полезным сразу определить — а есть ли вообще несовпадения?
Можно подсчитать общее количество расхождений и сразу вывести это число где-нибудь отдельно.
=СУММПРОИЗВ(–(C3:C25<>G3:G25))
или можно сделать это формулой массива
{=СУММ(–(C3:C25<>G3:G25))}
Если формула возвращает ноль, значит, данные полностью совпадают. Ну а ежели результат положительный, то нужны более детальные исследования. О них мы и поговорим далее.
Как произвести сравнение на отдельном листе.
Чтобы сравнить два листа Эксель на предмет различий, просто откройте новый пустой лист, введите следующую формулу в ячейку A1, а затем скопируйте ее вниз и вправо, перетащив маркер заполнения:
=ЕСЛИ(Лист1!A1 <> Лист2!A1; “Лист1:”&Лист1!A1&” — Лист2:”&Лист2!A1; “”)
Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от расположения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 в Лист1 и Лист2, формула в B1 будет сравнивать ячейку B1 на обоих листах и так далее. Результат будет выглядеть примерно так:
В результате вы получите отчет о различиях на новом листе. Думаю, это достаточно информативно.
Как вы можете видеть на приведенном выше рисунке, формула сравнивает 2 листа, находит ячейки с разными значениями и отображает различия в соответствующих местах.
Обратите внимание, что в отчете о различиях (ячейка D4) даты представлены числами, поскольку в таком виде они хранятся во внутренней системе Excel, что не очень удобно для анализа различий между ними.
Как сравнить две таблицы при помощи формулы ВПР.
Предположим, у нас снова 2 прайс-листа. Однако, в отличие от предыдущего примера, они содержат разное количество товаров, да и сами товары расположены в произвольном порядке. Поэтому описанный выше способ, когда мы построчно сравнивали две таблицы, здесь не сработает.
Нам необходимо последовательно взять каждый товар из одной таблицы, найти его во второй, извлечь оттуда его цену и сравнить с первоначальной ценой. Здесь нам не обойтись без формул поиска. Поможет нам функция ВПР.
Для наглядности расположим обе таблицы на одном листе.
Формула
=ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)
берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.
Аналогично можно сопоставлять и данные на разных листах. Просто нужно соответствующим образом изменить ссылки в формуле, указав в них имя листа.
Вот еще один пример. Возьмём за основу более новую информацию, то есть второй прайс. Выведем только сведения о том, какие цены и на какие товары изменились. А то, что не изменилось, выводить в итоговом отчёте не будем.
Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «».
=ЕСЛИ(ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)=G3;””;ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0))
Таким образом, в ячейке J3 будет указана старая цена, если ее удастся найти, а также если она не равна новой.
Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —
=ЕСЛИ(J3<>””;F3;””)
а в K3 – его новая цена:
=ЕСЛИ(J3<>””;G3;””)
Ну а далее в L3 просто найдем разность K3-J3.
Таким образом, в отчёте сравнения мы видим только несовпадения значений второй таблицы по сравнению с первой.
И еще один пример, который может быть полезен. Попытаемся сравнить в итоговой таблице оба прайс-листа с эталонным общим списком товаров.
В ячейке B2 запишем формулу
=ЕСЛИ(ЕНД(ВПР(A2;Прайс1!$B$3:$B$19;1;0));”Нет”;ВПР(A2;Прайс1!$B$3:$C$19;2;0))
Так мы выясним, какие цены из второй таблицы встречаются в первой.
Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными —
=ЕСЛИ(ЕНД(ВПР(A2;Прайс2!$B$3:$B$22;1;0));”Нет”;ВПР(A2;Прайс2!$B$3:$C$22;2;0))
Эталонный список находится у нас в столбце A. В результате мы получили своего рода сводную таблицу цен – старых и новых.
Еще несколько примеров использования функции ВПР для сравнения таблиц вы можете найти в этой статье.
Выделение различий между таблицами цветом.
Чтобы закрасить ячейки с разными значениями на двух листах выбранным вами цветом, используйте функцию условного форматирования Excel:
- На листе, где вы хотите выделить различия, выберите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите
Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки. - На вкладке Главная кликните Условное форматирование > Новое правило и создайте его со следующей формулой:
=A1<>Лист2!A1
Где Лист2 – это имя другого листа, который вы сравниваете с текущим.
В результате ячейки с разными значениями будут выделены выбранным вами цветом:
Если вы не очень хорошо знакомы с условным форматированием, вы можете найти подробные инструкции по созданию правила в следующем руководстве: Условное форматирование Excel в зависимости от значения ячейки.
Сравнение при помощи сводной таблицы.
Хороший вариант сравнения — объединить таблицы в единую сводную, и там уже сопоставлять данные между собой.
Вернемся к нашему примеру с двумя прайс-листами. Объединим наши данные на одном листе. Чтобы отличить данные одной таблицы от другой, добавим вспомогательный столбец D и укажем в нем, откуда именно взяты данные:
А теперь приступим к созданию сводной таблицы. Я не буду подробно останавливаться на том, как мы это будем делать. Все шаги подробно описаны в статье Как сделать сводную таблицу в Excel.
Поместим поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений.
Как видно на скриншоте ниже, для каждого товара, встречающегося хотя бы в одном из прайсов, указана цена.
Сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайсов и сортирует их по алфавиту. Причём, без повторов. У новых товаров нет старой цены, у удаленных товаров — новой цены. Легко увидеть изменения цен, если таковые были.
Общие итоги здесь смысла не имеют, и их можно отключить на вкладке Конструктор – Общие итоги – Отключить для строк и столбцов.
Если изменятся цены, то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши – Обновить. А вот если изменится список товаров или добавится новый файл для сравнения, то придется заново формировать исходный массив или же добавлять в него новые данные.
Плюсы: такой подход на порядок быстрее работает с большими объемами данных, чем ВПР. Можно сравнить данные нескольких таблиц.
Минусы: надо вручную копировать данные в одну большую таблицу и добавлять столбец с названием исходного файла.
Сравнение таблиц с помощью Power Query
Power Query – это бесплатная надстройка для Microsoft Excel, позволяющая загружать в него данные практически из любых источников и преобразовывать потом их желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные, а для более ранних версий ее нужно отдельно скачать с сайта Microsoft и установить.
Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl+T или выберем на ленте вкладку Главная – Форматировать как таблицу. Имена созданных таблиц можно изменить на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые генерируются по умолчанию).
Загрузите первый прайс в Power Query с помощью кнопки Из таблицы/диапазона на вкладке Данные.
После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить – Закрыть и загрузить в…
В появившемся затем окне выбираем «Только создать подключение».
Повторите те же действия с новым прайс-листом.
Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем на вкладке Данные – Получить данные – Объединить запросы – Объединить. Все шаги вы видите на скриншоте ниже.
В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части определим способ объединения – Полное внешнее.
После нажатия на ОК должна появиться таблица из четырёх столбцов, где в четвертой колонке нужно развернуть вложенное содержимое с помощью двойной стрелки в шапке.
После нажатия вы увидите список столбцов из второго прайса. Выбираем Товар и Цена. Получаем следующую картину:
А теперь сравним цены. Идем на вкладку Добавление столбца и жмем на кнопку Условный столбец. А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями, которые нужно отобразить:
Теперь осталось вернуться на вкладку Главная и нажать Закрыть и загрузить.
Получаем новый лист в нашей рабочей книге:
Примечание. Если в будущем в наших прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl+Alt+F5 или кнопкой Обновить все на вкладке Данные.
Ведь все данные извлекаются из «умных» таблиц Excel, которые автоматически меняют свой размер при добавлении либо удалении из них какой-либо информации. Однако, помните, что имена столбцов в исходных таблицах не должны меняться, иначе получим ошибку “Столбец такой-то не найден!” при попытке обновить запрос.
Это, пожалуй, самый красивый и удобный способ из всех стандартных. Шустро работает с большими таблицами. Не требует ручных правок при изменении размеров.
Как видите, есть несколько способов сравнить две таблицы Excel, используя формулы или условное форматирование. Однако эти методы не подходят для комплексного сравнения из-за следующих ограничений:
- Они находят различия только в значениях, но не могут сравнивать формулы или форматирование ячеек.
- Многие из них не могут идентифицировать добавленные или удаленные строки и столбцы. Как только вы добавите или удалите строку / столбец на одном листе, все последующие строки / столбцы будут отмечены как отличия.
- Они хорошо работают на уровне листа, но не могут обнаруживать структурные различия на уровне книги Excel, к примеру добавление и удаление листов.
Эти проблемы решаются путем использования дополнений к Excel, о чем мы поговорим далее.
Как сравнить таблицы при помощи Ultimat Suite для Excel
Последняя версия Ultimate Suite включает более 60 новых функций и улучшений, самым интересным из которых является «Сравнение таблиц» – инструмент для сравнения листов или диапазонов данных в Excel.
Чтобы сделать сравнение более интуитивным и удобным, надстройка разработана следующим образом:
- Мастер шаг за шагом проведет вас через процесс и помогает настраивать различные параметры.
- Вы можете выбрать алгоритм сравнения, наиболее подходящий для ваших наборов данных.
- Вместо отчета о различиях сравниваемые листы отображаются в режиме просмотра различий, чтобы вы могли сразу просмотреть все различия и управлять ими по очереди.
Теперь давайте попробуем использовать этот инструмент на наших примерах электронных таблиц из предыдущего примера и посмотрим, отличаются ли результаты.
- Нажмите кнопку «Сравнить листы (Compare Two Sheets)» на вкладке «Данные Ablebits » в группе « Объединить »:
- Появится окно мастера с предложением выбрать два листа, которые вы хотите сравнить на предмет различий.
По умолчанию выбираются все листы, но вы также можете выбрать текущую таблицу 
или определенный диапазон 
, нажав соответствующую кнопку:
- На следующем шаге вы выбираете алгоритм сравнения:
- Без ключевых столбцов (по умолчанию) — лучше всего подходит для сложных документов, таких как счета-фактуры или контракты.
- По ключевым столбцам — подходит для таблиц, организованных по столбцам, которые имеют один или несколько уникальных идентификаторов, таких как номера заказов или артикулы товаров.
- По ячейке — лучше всего использовать для сравнения таблиц с одинаковым макетом и размером, таких как балансы или статистические отчеты.
Совет. Если вы не уверены, какой алгоритм подходит вам, выберите вариант по умолчанию (без ключевых столбцов). Какой бы алгоритм вы ни выбрали, надстройка найдет все различия, только выделит их по-разному (целые строки или отдельные ячейки).
На этом же шаге вы можете выбрать предпочтительный тип соответствия:
- Первое совпадение (по умолчанию) – сравнивает строку на листе 1 с первой найденной строкой на листе 2, которая имеет хотя бы одну совпадающую ячейку.
- Наилучшее совпадение – сравнивает строку на листе 1 со строкой на листе 2, которая имеет максимальное количество совпадающих ячеек.
- Полное совпадение – находит на обоих листах строки, которые имеют одинаковые значения во всех ячейках, и отмечает все остальные строки как уникальные.
В этом примере мы сначала будем искать наилучшее совпадение, используя режим сравнения без ключевых столбцов, который установлен по умолчанию.
- На следующем шаге укажите, какие различия следует выделить, а какие игнорировать, и как помечать различия.
Скрытые строки и столбцы не имеют значения, и мы говорим надстройке игнорировать их:
- Нажмите кнопку «Сравнить (Compare)» и подождите немного, пока программа обработает ваши данные и создаст их резервные копии. Резервные копии всегда создаются автоматически, поэтому вы можете не беспокоиться о сохранности своих данных.
После обработки листы открываются друг рядом с другом в специальном режиме просмотра различий с выбранным способом выделения отличий:
На скриншоте выше различия выделены цветами по умолчанию:
- Красные строки – строки, существующие только на Листе 2 (справа).
- Зеленые ячейки – различные ячейки в частично совпадающих строках.
А вот если мы выберем второй алгоритм сравнения — по ключевому столбцу, то нам будет предложено указать его. В нашем случае вполне можно ключевым столбцом обозначить «Товар».
После этого мы видим немного другой результат сравнения:
Как видите, основным здесь действительно является факт совпадения значений в столбцах B. Строки, в которых нет такого совпадения, сразу выделяются красным или фиолетовым. А вот если совпадение есть, тогда идем в столбец С и сравниваем записанную там цену. Зелёные ячейки как раз и показывают нам товары, которые имеются в обоих прайс-листах, но цена на них изменилась.
Не знаю как вам, но мне второй вариант представляется более информативным.
А что же дальше делать с этим сравнением?
Чтобы помочь вам просматривать различия и управлять ими, на каждом листе есть собственная вертикальная панель инструментов. Для неактивного рабочего листа (справа на нашем скриншоте) эта панель отключена. Чтобы активировать панель инструментов, просто выберите любую ячейку на соответствующем листе.
Используя её, вы последовательно просматриваете найденные различия и решаете, объединить их или игнорировать:
Как только последнее различие будет устранено, вам будет предложено сохранить книги и выйти из режима просмотра различий.
Если вы еще не закончили обработку различий, но хотели бы сделать перерыв, нажмите кнопку «Выйти из просмотра различий» в нижней части панели инструментов и выберите один из следующих вариантов:
- Сохраните внесенные вами изменения и сохраните оставшиеся различия (Save workbooks and keep difference marks),
- Сохраните внесенные вами изменения и удалите оставшиеся различия (Save workbooks and remove difference marks),
- Восстановите исходные книги из резервных копий (Restore workbooks from backup copies).
Вот как вы можете сравнить два листа в Excel при помощи инструмента сравнения Compare Two Sheets (надеюсь, он вам понравился 🙂
Если вам интересно попробовать, полнофункциональная ознакомительная версия доступна для загрузки здесь .
При анализе данных с помощью Excel очень часто приходится искать некоторые значения по определенным критериям и подставлять их в другие таблицы. В таких задачах на помощью приходит функция ВПР, но у нее есть два существенных недостатка. Во-первых, функция ВПР возвращает только первое найденное значение. Например, у меня есть перечень заказов и необходимо получить номера всех заказов, которые оформил конкретный менеджер. Фамилия менеджера выбирается из выпадающего списка (выделено желтым) и ниже должны выводиться соответствующие номера заказов (зеленая область).
Если использовать стандартную функцию ВПР, то она найдет первый подходящий заказ и вернет ТОЛЬКО его номер.
Еще одним недостатком функции ВПР является то, что она ищет значения только в левом крайнем столбце выделенного диапазона. То есть если бы столбец с фамилиями менеджеров находился правее столбца с номерами заказов, то функцию ВПР вообще не удалось бы использовать.
Этот недостаток устраняется с помощью сочетания функций ИНДЕКС и ПОИСКПОЗ, которые являются более гибкой альтернативой функции ВПР, но и эти функции не решают поставленную задачу.
Как же быть, если нужно вернуть все значения, соответсвующие определенному критерию? В этом также поможет функция ИНДЕКС.
Напомню, что функция ИНДЕКС возвращает значение, которое находится в указанном номере строки выделенного диапазона. То есть мы должны выбрать некоторый диапазон значений и вторым аргументом указать номер строки в этом диапазоне. Подчеркиваю, не номер строки листа Эксель, а номер строки выделенного диапазона значений.
Функция ИНДЕКС может возвращать не только одно значение, а массив значений. И именно это нам и нужно. Чтобы функция вернула массив значений, мы должны в нее подставить массив номеров нужным нам строк. Поэтому основной задачей для нас сейчас как раз и будет получение этого массива.
Давайте рассмотрим упрощенную таблицу, чтобы в ней не было отвлекающей информации. У нас есть таблица, состоящая из двух столбцов – Менеджер и Номер заказа. Также есть выпадающий список с фамилиями менеджеров и некоторая область листа, в которую мы будем выводить все заказы, связанные с выбранным менеджером.
Итак, сначала мы должны определить все строки в основной таблице, относящиеся к выбранному в списке менеджеру. Давайте сделаем это с помощью вспомогательного столбца и функции ЕСЛИ.
Если значение из текущей ячейки первого столбца таблицы (А2) равно значению, выбранному в выпадающем списке (L1), то определим номер строки, в котором это значение находится. Для этого воспользуемся функцией СТРОКА, которая как раз и предназначена для решения этой задачи. Так как формулу мы будем протягивать по диапазону, то не забываем зафиксировать ссылку на ячейку с выпадающем списком.
Протягиваем формулу.
Так как мы не указали в функции ЕСЛИ значение, которое появится в случае невыполнения условия, то в соответствующих ячейках выводится логическое выражение ЛОЖЬ. Это неважно, так как нас будут интересовать только цифры.
Мы получили номера строк листа Эксель, но в функцию ИНДЕКС необходимо подставить номер строки в выделенном диапазоне. В данном примере основная таблица располагается в верху листа и в первой строке находится шапка с названиями столбцов, поэтому, чтобы получить необходимые значения, мы можем откорректировать формулу и отнять единицу от полученного значения. В итоге во вспомогательном столбце появится массив необходимых нам чисел.
Если же таблица находится в другой части листа, то нужно будет либо вручную прописать необходимое корректировочное значение (то есть отступ от первой строки листа), либо можно автоматизировать этот процесс с помощью все той же функции СТРОКА, но об этом я расскажу чуть позже, чтобы сейчас не усложнять формулу.
Итак, теперь нам нужно отсортировать столбец, чтобы в начале были цифры. Сделать это можно, например, с помощью функции НАИМЕНЬШИЙ, которая возвращает указанное по счету наименьшее значение в выбранном диапазоне.
Выбираем диапазон (С2:С18) и затем необходимо указать цифру, определяющую, какое по порядку наименьшее число нужно вывести. Если укажем 1, то получим первое наименьшее значение в диапазоне, если 2, то второе, и так далее. Именно таким образом мы и сможем отсортировать полученный список с помощью формулы. Создадим вспомогательный столбец со значениями по порядку и подставим эти значения в функцию НАИМЕНЬШИЙ.
Мы получили необходимый нам перечень номеров строк и можем вернуться к функции ИНДЕКС. Фактически нам нужно подтянуть значения из второго столбца основной таблицы, поэтому в качестве диапазона указываем его. В качестве строки, соответственно, только что рассчитанные значения.
Чтобы избавиться от ошибки ЧИСЛО! воспользуемся функцией ЕСЛИОШИБКА. Обернем ей полученную функцию и в случае ошибки выведем пустоту.
Мы достигли необходимого результата, но для этого пришлось создать несколько вспомогательных столбцов. Давайте свернем все промежуточные вычисления в одну формулу, но это будет не простая формула, а формула массива, поэтому нам нужно будет поменять некоторые ссылки на диапазоны, к которым они относятся. А если точнее, то в функции ЕСЛИ нужно будет заменить относительные ссылки на ячейки столбца А соответствующим диапазоном и не забываем зафиксировать его. Также не забываем сделать формулу формулой массива, нажав сочетание клавиш Ctrl + Shift + Enter. Растянем формулу на весь зарезервированный диапазон таблицы и получаем необходимый результат.
Все вспомогательные столбцы, кроме столбца с нумерацией (столбец F) можно удалить. Давайте сделаем так, чтобы и этот столбец был не нужен. Значения столбца F используются в функции НАИМЕНЬШИЙ и нам нужно сделать так, чтобы подобный ряд чисел создавался автоматически и не зависел от того, где находится таблица с формулами. Для этого можно воспользоваться функцией СТРОКА и определить номер строки листа Эксель первой ячейки основной таблицы. Затем этот номер будем вычитать из номера последующих строк. Чтобы значения «не сползали» при протягивании формулы, зафиксируем ссылку на первую ячейку диапазона.
Все отлично, кроме того, что все значения нужно увеличить на единицу. Дополним формулу и получим нужный нам результат.
Осталось скопировать формулу и подставить ее в формулу массива, после чего и последний вспомогательный столбец можно будет удалить.
Ну и по аналогии можно решить проблему зависимости формулы от расположения исходной таблицы. Сейчас ее заголовки расположены в первой строке листа и поэтому формула четко привязана к этому положению.
Мы можем внести в функцию ЕСЛИ аналогичную формулу с двумя функциями СТРОКА. То есть отнимем от уже имеющейся функции СТРОКА со всем диапазоном столбца номер строки первой ячейки этого диапазона и прибавим единицу.
Теперь формула никак не привязана к положению исходной таблице на листе и она выполняет поставленную задачу – возвращает все искомые значения из указанного диапазона.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel
★ Телеграм
★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”
★ Авторские книги и курсы
Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.
Примеры использования функции ПОИСКПОЗ в Excel
Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).
Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» – «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.
Например, массив {“виноград”;”яблоко”;”груша”;”слива”} содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(“яблоко”;{“виноград”;”яблоко”;”груша”;”слива”};0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.
Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.
Формула для поиска неточного совпадения текста в Excel
Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.
Вид исходной таблицы данных:
Для нахождения позиции текстовой строки в таблице используем следующую формулу:
=ПОИСКПОЗ(D2&”*”;B:B;0)-1
Описание аргументов:
- D2&”*” – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
- B:B – ссылка на столбец B:B, в котором выполняется поиск;
- 0 – поиск точного совпадения.
Из полученного значения вычитается единица для совпадения результата с id записи в таблице.
Пример поиска:
Сравнение двух таблиц в Excel на наличие несовпадений значений
Пример 2. В Excel хранятся две таблицы, которые на первый взгляд кажутся одинаковыми. Было решено сравнить по одному однотипному столбцу этих таблиц на наличие несовпадений. Реализовать способ сравнения двух диапазонов ячеек.
Вид таблицы данных:
Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):
Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».
Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:
Как видно, третьи элементы списков не совпадают.
Поиск ближайшего большего знания в диапазоне чисел Excel
Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.
Вид исходной таблицы данных:
Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):
Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.
Результат расчетов:
Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):
Результат поиска:
Особенности использования функции ПОИСКПОЗ в Excel
Функция имеет следующую синтаксическую запись:
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
Описание аргументов:
- искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
- просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
- [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:
- -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
- 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
- 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.
Скачать примеры ПОИСКПОЗ для поиска совпадения значений в Excel
Примечания:
- Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
- Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
- Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» – замена одного любого символа, «*» – замена любого количества символов).
- Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.
Поиск значений в списке данных
Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Предположим, что вы хотите найти расширение телефона сотрудника, используя его номер эмблемы или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.
Что необходимо сделать
-
Точное совпадение значений по вертикали в списке
-
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
-
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
-
Точное совпадение значений по горизонтали в списке
-
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
-
Создание формулы подступа с помощью мастера подметок (только в Excel 2007)
Точное совпадение значений по вертикали в списке
Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.
Примеры ВРОТ
Дополнительные сведения см. в этой информации.
Примеры индексов и совпадений
Что означает:
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение “Капуста” в массиве B2:B10))
Формула ищет в C2:C10 первое значение, соответствующее значению “Ольга” (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению “Ольга”.
Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.
К началу страницы
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Для этого используйте функцию ВЛВП.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.
Дополнительные сведения см. в этой информации.
К началу страницы
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.
Примечание: Используйте этот подход, если данные в диапазоне внешних данных обновляются каждый день. Вы знаете, что цена находится в столбце B, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортировали по алфавиту.
C1 — это левые верхние ячейки диапазона (также называемые начальной).
MATCH(“Оранжевая”;C2:C7;0) ищет “Оранжевые” в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.
1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.
К началу страницы
Точное совпадение значений по горизонтали в списке
Для этого используйте функцию ГГПУ. См. пример ниже.
Г ПРОСМОТР ищет столбец “Продажи” и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
К началу страницы
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Для этого используйте функцию ГГПУ.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
К началу страницы
Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )
Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).
В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.
-
Щелкните ячейку в диапазоне.
-
На вкладке Формулы в группе Решения нажмите кнопку Под поиск.
-
Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов.
Загрузка надстройки “Мастер подстройок”
-
Нажмите кнопку Microsoft Office
, выберите Параметры Excel и щелкните категорию Надстройки. -
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
-
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
-
Следуйте инструкциям мастера.
, выберите Параметры Excel и щелкните категорию Надстройки.К началу страницы
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
