Как сравнить две сводные таблицы в excel

9 способов сравнить две таблицы в Excel и найти разницу

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

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

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

Просмотр рядом, чтобы сравнить таблицы.

Если у вас относительно небольшие файлы и вы внимательны к деталям, этот быстрый и простой способ сравнения может вам подойти. Я говорю о режиме «Просмотр рядом», который позволяет расположить два окна Excel рядом. Вы можете использовать этот метод для визуального сравнения двух таблиц или двух листов из одной книги.

Сравните 2 книги.

Предположим, у вас есть отчеты о продажах за два месяца, и вы хотите просмотреть их оба одновременно, чтобы понять, какие товары показали лучшие результаты в этом месяце, а какие — в прошлом.

Чтобы просмотреть два файла Эксель рядом, сделайте следующее:

1. Откройте оба файла.
2. Перейдите на вкладку «Вид» и нажмите кнопку «Рядом». (1) Это оно!

По умолчанию два отдельных окна Excel отображаются горизонтально.

В результате два отдельных окна будут расположены, как на скриншоте.

Если вы хотите прокручивать оба листа одновременно, чтобы сравнивать данные строка за строкой, убедитесь, что параметр синхронной прокрутки (2) включен. Он обычно включается автоматически, как только вы активируете режим одновременного просмотра двух книг.

Расположите рядом несколько таблиц Excel.

Чтобы просматривать более двух файлов одновременно, откройте все книги, которые вы хотите сравнить, и нажмите кнопку «Рядом».

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

Чтобы просмотреть все открытые файлы одновременно, нажмите кнопку «Упорядочить все» и выберите предпочтительное расположение: мозаичное, горизонтальное, вертикальное или каскадное.

Для небольших таблиц вы легко сможете визуально сравнить их данные. Хотя, конечно, риск ошибки из-за человеческого фактора здесь присутствует.

Сравните два листа в одной книге.

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

1. Откройте файл, перейдите на вкладку «Вид» и нажмите кнопку «Новое окно».

1. Это действие откроет тот же файл в дополнительном окне.
2. Включите режим просмотра «Рядом», нажав соответствующую кнопку на ленте.
3. Выберите лист 1 в первом окне и лист 2 во втором окне.

Быстрое выделение значений, которые различаются.

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии или же отсутствии отличий между записями, вам нужно на вкладке «Главная», выбрать кнопку «Найти и выделить», предварительно выделив диапазон, где надо сравнить данные в Эксель.

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

Формула сравнения.

Это самый простой способ соотнесения таблиц в Excel, который позволяет идентифицировать в них ячейки с разными значениями.

Простейший вариант – сопоставление двух таблиц, находящихся на одном листе. Можно соотносить как числовые, так и текстовые значения, всего-навсего прописав в одной из соседних ячеек формулу их равенства. В результате при тождестве ячеек мы получим сообщение ИСТИНА, в противном случае — ЛОЖЬ.

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

Результатом будет являться либо ИСТИНА (в случае совпадения), либо ЛОЖЬ (при отрицательном результате).

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

Если ваши таблицы достаточно велики, то довольно утомительно будет просматривать колонку I на предмет поиска слова ЛОЖЬ. Поэтому может быть полезным сразу определить — а есть ли вообще несовпадения?

Можно подсчитать общее количество расхождений и сразу вывести это число где-нибудь отдельно.

или можно сделать это формулой массива

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

Как произвести сравнение на отдельном листе.

Чтобы сравнить два листа Эксель на предмет различий, просто откройте новый пустой лист, введите следующую формулу в ячейку A1, а затем скопируйте ее вниз и вправо, перетащив маркер заполнения:

Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от расположения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 в Лист1 и Лист2, формула в B1 будет сравнивать ячейку B1 на обоих листах и ​​так далее. Результат будет выглядеть примерно так:

В результате вы получите отчет о различиях на новом листе. Думаю, это достаточно информативно.

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

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

Как сравнить две таблицы при помощи формулы ВПР.

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

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

Для наглядности расположим обе таблицы на одном листе.

берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.

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

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

Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «».

Таким образом, в ячейке J3 будет указана старая цена, если ее удастся найти, а также если она не равна новой.

Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —

а в K3 – его новая цена:

Ну а далее в L3 просто найдем разность K3-J3.

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

И еще один пример, который может быть полезен. Попытаемся сравнить в итоговой таблице оба прайс-листа с эталонным общим списком товаров.

В ячейке B2 запишем формулу

Так мы выясним, какие цены из второй таблицы встречаются в первой.

Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными —

Эталонный список находится у нас в столбце A. В результате мы получили своего рода сводную таблицу цен – старых и новых.

Еще несколько примеров использования функции ВПР для сравнения таблиц вы можете найти в этой статье.

Выделение различий между таблицами цветом.

Чтобы закрасить ячейки с разными значениями на двух листах выбранным вами цветом, используйте функцию условного форматирования Excel:

• На листе, где вы хотите выделить различия, выберите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите Ctrl + Shift + End , чтобы расширить выделение до последней использованной ячейки.
• На вкладке Главная кликните Условное форматирование > Новое правило и создайте его со следующей формулой:

Где Лист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.

Чтобы сделать сравнение более интуитивным и удобным, надстройка разработана следующим образом:

• Мастер шаг за шагом проведет вас через процесс и помогает настраивать различные параметры.
• Вы можете выбрать алгоритм сравнения, наиболее подходящий для ваших наборов данных.
• Вместо отчета о различиях сравниваемые листы отображаются в режиме просмотра различий, чтобы вы могли сразу просмотреть все различия и управлять ими по очереди.

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

1. Нажмите кнопку «Сравнить листы (Compare Two Sheets)» на вкладке «Данные Ablebits » в группе « Объединить »:

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

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

1. На следующем шаге вы выбираете алгоритм сравнения:
   • Без ключевых столбцов (по умолчанию) — лучше всего подходит для сложных документов, таких как счета-фактуры или контракты.
   • По ключевым столбцам — подходит для таблиц, организованных по столбцам, которые имеют один или несколько уникальных идентификаторов, таких как номера заказов или артикулы товаров.
   • По ячейке — лучше всего использовать для сравнения таблиц с одинаковым макетом и размером, таких как балансы или статистические отчеты.

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

На этом же шаге вы можете выбрать предпочтительный тип соответствия:

1. Первое совпадение (по умолчанию) — сравнивает строку на листе 1 с первой найденной строкой на листе 2, которая имеет хотя бы одну совпадающую ячейку.
2. Наилучшее совпадение — сравнивает строку на листе 1 со строкой на листе 2, которая имеет максимальное количество совпадающих ячеек.
3. Полное совпадение — находит на обоих листах строки, которые имеют одинаковые значения во всех ячейках, и отмечает все остальные строки как уникальные.

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

1. На следующем шаге укажите, какие различия следует выделить, а какие игнорировать, и как помечать различия.

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

1. Нажмите кнопку «Сравнить (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 для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального…
Функция МАКС в Excel: как найти максимальное значение — В этой статье объясняется функция МАКС с множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число на листе. МАКС — одна из самых простых…
Как сравнить два столбца на совпадения и различия — На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные…
Сравнение ячеек в Excel — Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству…
Как выделить цветом повторяющиеся значения в Excel? — В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования. Ранее мы исследовали различные…
Группировка в сводной таблице Excel – полезные рекомендации. — Возможность группировки данных является одной из самых мощных и полезных функций в сводной таблице. Это позволит обнаружить ранее скрытые взаимосвязи, сделать интересные выводы. При работе со сводными таблицами в Microsoft Excel вы можете…
Как сделать сводную таблицу в Excel – пошаговая инструкция для чайников — В этом руководстве вы узнаете, что такое сводная таблица, и найдете подробную инструкцию, как по шагам создавать и использовать её в Excel. Если вы работаете с большими наборами данных в…
Как изменить цвет ячейки в зависимости от значения в Excel? — В этой статье вы найдете 13 примеров как изменить цвет фона ячеек в зависимости от значения в Excel 2016, 2013 и 2010. Кроме того, вы узнаете, как использовать формулы Excel…
Как сделать условное форматирование в Excel? Инструкции с примерами. — В этой статье вы найдете множество быстрых способов как сделать условное форматирование строк, столбцов и отдельных ячеек в MS Excel 2016, 2013 и 2010. Мы рассмотрим, как можно применить различное…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…

Формула ВПР в Excel для сравнения двух таблиц

Сравнение таблиц — довольно распространенное действие в Excel. Например, у нас есть старый прайс-лист и новая версия. Вам необходимо просмотреть цены на какие товары и на сколько изменились.

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

Итак, вот наши необработанные данные.

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

Для этого мы воспользуемся несколькими способами.

1. Использование функции ВПР для каждого продукта в прайс-листе № 2, мы будем искать цену в первом прайс-листе и отображать ее рядом с новым. Это облегчит выявление различий.
2. Давайте построим сводную таблицу на основе данных из обоих прайс-листов и увидим там эти различия.
3. Мы используем стандартную операцию сравнения.
4. Применяем формулу массива.

1. Используем ВПР, чтобы сравнить две таблицы.

Создадим диапазон B4: C19 и назовем его «цена1». Это упростит нам обращение к исходным данным.

Давайте добавим еще один столбец к новым данным и назовем его «Старая цена». Для каждой позиции в прайс-листе нет. 2 найдем соответствующую цену в n. 1.

В 4 вставляем формулу

= ВПР (F4; цена1; 2; 0)

и скопируйте его по столбцу.

Мы видим, что цена тут и там изменилась, и в четырех именах формула ВПР вернула ошибку # N / A. Это означает, что раньше этих товаров не было в наличии и цены на них найти не удалось.

Чтобы сделать результаты сравнения более красивыми и определить степень изменения цены, мы обработаем отображаемые сообщения об ошибках.

Для этого используйте функцию ESLIOSHIBKA и отобразите ноль вместо # N / A.

Изменим нашу формулу:

= SEERROR (ВПР (F4; цена1,2,0); 0)

Теперь мы можем рассчитать отклонение новой цены от старой.

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

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

= ЕСЛИ (SEERROR (VLOOKUP (F4; price1,2,0); 0) = G4; «»; SEERROR (VLOOKUP (F4; price1,2; 0); 0))

Звучит сложно и громоздко, но на самом деле все просто. Основа такая же, как и раньше: посмотрите в первой таблице «старую» цену каждого товара из новых данных.

То есть ключевым выражением является ЕСЛИОШИБКА (ВПР (F4; цена1; 2; 0); 0).

Если найденное значение равно «новой» цене из ячейки G4, выведите пробел “”.

С этим результатом связаны значения соседних ячеек.

Если ячейка J4 пуста, в другом месте ничего не отображается:

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

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

То есть сравнивать придется по двум направлениям.

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

2. Сравнение при помощи сводной таблицы.

Поскольку структура сравниваемых данных одинакова, мы можем их комбинировать. Чтобы различать, откуда берутся значения, добавьте еще один столбец и укажите там источник данных — price1 или price2.

Используя наш предыдущий пример, это можно сделать следующим образом:

Теперь через меню Insert-PivotTable создайте сводку, которую вы можете на том же листе для наглядности.

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

Чтобы не мешали, итоги по строкам и столбцам можно убрать. Для этого воспользуйтесь вкладкой «Дизайн» — «Общие итоги» — «Отключить итоги для строк и столбцов.

Это еще один пример того, как ВПР во многих случаях имеет довольно приличные альтернативы.

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

Также следует отметить, что сводные таблицы могут работать с большими объемами данных намного быстрее, чем VLOOKUP.

3. Стандартное сравнение.

Это самый простой и элементарный способ сравнить два столбца Excel на предмет совпадений. Таким образом можно работать как с числовыми, так и с текстовыми значениями.

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

Например, сравним две цены, отметив условие совпадения цен в столбце I

В случае ничьей мы получим ответ «ИСТИНА», а при отсутствии совпадений — «ЛОЖЬ». Скопируйте из I4 в столбец.

Этот способ сравнения таблиц является наиболее простым, поэтому мы не будем вдаваться в подробности.

4. Использование формул массива вместе с ВПР.

Здесь все намного сложнее. Вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Цена1» и «Цена2».

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

Назовем их «цена_1» и «цена_2» соответственно. Это упростит понимание формул.

Мы также вынесем результаты сравнения таблиц на отдельный лист «Сравнение».

В ячейке A5 запишите формулу

= ЕСЛИОШИБКА (ЕСЛИОШИБКА (ИНДЕКС (цена_1; ПОИСК (0; СЧЁТЕСЛИ (A $ 4: $ A4; цена_1); 0))), ИНДЕКС (цена_2; ПОИСК (0; СЧЁТЕСЛИ (A $ 4: $ A4; 0)));»»)

Поскольку это формула массива, не забудьте завершить ее комбинацией клавиш Ctrl + Shift + Enter.

В результате мы получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований продуктов.

Давайте рассмотрим процесс пошагово. Формула последовательно берет значения из списка элементов. Затем с помощью функции СЧЁТЕСЛИ найдите количество совпадений с каждым из значений в ячейках над этим значением. Если результат СЧЁТЕСЛИ равен нулю, это имя ранее не было найдено, и вы можете добавить его в список.

Функция ПОИСКПОЗ вычисляет номер позиции этого уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, через номер позиции извлекает значение из массива и записывает его в ячейку.

Поскольку это формула массива, мы постоянно просматриваем весь список от начала до конца, повторяя все эти операции.

Если первая таблица завершена, возникает ошибка. ЕСЛИОШИБКА реагирует на это и таким же образом начинает перебирать значения второй таблицы. Когда ошибка возникает и там, возвращается пустая строка “”.

Скопируйте эту формулу столбец за столбцом. Список уникальных ценностей готов.

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

= ЕСЛИ (UND (ВПР (A5; цена_1; 1; 0)); «Нет»; «Да»)

Не забывайте, что это тоже формула массива (Ctrl + Shift + Enter).

вы можете выделить расхождения цветом для наглядности, используя условное форматирование.

Помните, что для этого вам нужно использовать Главное меню — Условное форматирование — Правила выбора ячеек — Текст содержит…

Что ж, если значение существует в таблице, имеет смысл отобразить его в таблице сравнения.

Давайте заменим значение «Да» в нашей формуле на функцию ВПР:

ВПР (A5, Price1! $ B $ 5: $ C $ 20, 2, 0)

В результате наша формула преобразуется в вид:

= ЕСЛИ (UND (ВПР (A5; цена_1; 1; 0)); «Нет»; ВПР (A5; Цена1! $ B $ 5: $ C $ 20, 2, 0))

Аналогично в C5 :

= ЕСЛИ (UND (ВПР (A5; цена_2; 1; 0)); «Нет»; ВПР (A5; Цена2! $ B $ 5: $ C $ 23, 2, 0))

Напомним, что наши сравнительные таблицы можно найти в таблицах Price1 и Price2.

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

Сравнение двух таблиц

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:

С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок. ), что-то пропало (ежевика, малина. ), у каких-то товаров изменилась цена (инжир, дыня. ). Нужно быстро найти и вывести все эти изменения.

Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:

• функцию ВПР (VLOOKUP) — искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
• объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
• использовать надстройку Power Query для Excel

Давайте разберем их все последовательно.

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

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

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

Те товары, напротив которых получилась ошибка #Н/Д — отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.

Плюсы этого способа: просто и понятно, «классика жанра», что называется. Работает в любой версии Excel.

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

Способ 2. Сравнение таблиц с помощью сводной

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

Теперь на основе созданной таблицы создадим сводную через Вставка — Сводная таблица (Insert — Pivot Table) . Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:

Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были.

Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов (Design — Grand Totals) .

Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши — Обновить (Referesh).

Плюсы : такой подход на порядок быстрее работает с большими таблицами, чем ВПР.

Минусы : надо вручную копировать данные друг под друга и добавлять столбец с названием прайс-листа. Если размеры таблиц изменяются, то придется делать все заново.

Способ 3. Сравнение таблиц с помощью Power Query

Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить — получите новую вкладку Power Query.

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl + T или выберем на ленте вкладку Главная — Форматировать как таблицу (Home — Format as Table) . Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).

Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в. (Close & Load — Close & Load To. ) :

. и в появившемся затем окне выбрем Только создать подключение (Connection Only) .

Повторите то же самое с новым прайс-листом.

Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить (Data — Get Data — Merge Queries — Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения — Полное внешнее (Full Outer) :

После нажатия на ОК должна появиться таблица из трех столбцов, где в третьем столбце нужно развернуть содержимое вложенных таблиц с помощью двойной стрелки в шапке:

В итоге получим слияние данных из обеих таблиц:

Названия столбцов в шапке лучше, конечно, переименовать двойным щелчком на более понятные:

А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column) . А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:

Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home) :

Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl + Alt + F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data) .

Плюсы : Пожалуй, самый красивый и удобный способ из всех. Шустро работает с большими таблицами. Не требует ручных правок при изменении размеров таблиц.

Минусы : Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.

Как сравнить две таблицы в excel на совпадения

​Смотрите также​ и будет проще​Re_Orig​ цветом совпадения в​ ИСТИНА или ЛОЖЬ.​ таблицы2, а также​ поле «Код учащегося»​ Так как вам​ справке программы для​B​ затем импортируйте листы​ а также от​ диапазона дополнительного столбца​ вычисление совпадений, сравнив​ выделения.​ вариантов вы не​ сравнении формула выдала​Довольно часто перед пользователями​ если итого сравнения​: Pelena,​ наших списках, если​ В зависимости от​ значения из строки2​ таблицы «Специализации» изменим​ нужно найти только​ работы с электронными​147025836​

​ в Access или​ того, как именно​

​«Количество совпадений»​ каждую ячейку первой​После того, как мы​

Способы сравнения

​ избрали, запускается окно​ результат​ Excel стоит задача​ отражались в старом​Я вместо B2​ опцию​

• ​ того содержит ли​ таблицы1, которые содержатся​
• ​ числовой тип данных​ совпадающие данные, оставьте​
• ​ таблицами.​2006​

​ можно вставлять данные​ пользователь желает, чтобы​, который мы ранее​ таблицы с данными,​ произведем указанное действие,​ выделения групп ячеек.​«ЛОЖЬ»​ сравнения двух таблиц​ листе, чтобы можно​ брал всю строку,​Уникальные​

​ таблица исходное значение​ в строке2 таблицы2​ на текстовый. Так​ для объединения значение​Сохраните книгу в нужной​1​

Способ 1: простая формула

​ в текстовом редакторе,​ это сравнение выводилось​ преобразовали с помощью​ которые расположены во​ все повторяющиеся элементы​ Устанавливаем переключатель в​. По всем остальным​ или списков для​ было мониторить статус​ т.е. B2:K2 исходя​- различия.​ или нет.​ и т.д.​ как нельзя создать​ 1. Закройте диалоговое​ папке на компьютере​МАТЕМ​ например Блокнот и​ на экран.​ функции​ втором табличном диапазоне.​ будут выделены выбранным​ позицию​ строчкам, как видим,​ выявления в них​ повторяющих строк.​ из того факта,​

​Цветовое выделение, однако, не​​Пусть на листах Полугодие1​ объединение двух полей​ окно​ или в сети​120​ затем импортировать данные​

​Автор: Максим Тютюшев​ЕСЛИ​ В четырех случаях​ цветом. Те элементы,​​«Выделить по строкам»​​ формула сравнения выдала​ отличий или недостающих​Надеюсь, пишу все​ что в столбе​ всегда удобно, особенно​​Разбор принципа действия формулы​​ и Полугодие2 имеется​ с разными типами​Параметры объединения​ и переходите к​D​ из результирующего текстовые​

​. Делаем все ссылки​ результат вышел​ которые не совпадают,​. Жмем по кнопке​

​ значениями за каждый​ сравнить два поля​Отмена​В новой или существующей​2006​В пошаговых инструкциях этого​ оперативнее обеспечивать вас​​В поле​​, а в двух​ свой изначальный цвет​.​.​ задачей по своему,​ Всем спасибо.​ номера могут отличаться​ самих списков элементы​Функция =СОВПАД() сравнивает (с​ месяц.​

​ видимо я ошибался,​

​ подойдет.​ значения идентичными или​ которые содержатся в​ другого.​ этого перетащите поле​в группе​B​ а также как​ автоматически, поэтому ее​

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

​ как можно судить​

​ выделяем тот элемент​​ так как далеко​​ любая формула осилит​

​Тогда нет смысла​ всю строку? Т.е.​СЧЁТЕСЛИ​ ИСТИНА. Учитывая тот​Как видно из рисунков​ примеры таблиц.​Учащиеся​Excel​3​ затем импортировать их​ ошибки. Для нас​ нумерацией, который мы​ имеются в первом​ окрасить несовпадающие элементы,​ формул, программа сделает​ будет выводиться. Затем​ к данной проблеме​ писать макрос -​

​ формула сравнивает не​

​(COUNTIF)​ факт что формула​ выше, в 1-х​В​в поле​.​МАТЕМ​ в Access.​

Способ 2: выделение групп ячеек

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

​области навигации​Год​​-или-​​221​Код учащегося​​ статья была вам​​ оставляем относительным. Щелкаем​Конечно, данное выражение для​ которые совпадают, оставить​​ ячеек, находящуюся в​​«Вставить функцию»​ то же время,​ обойтись. Данных или​​ B?​​Статистические​

​ функция СОВПАД сравнивает​ совпадают значения 11​щелкните таблицу «Специализации»​таблицы​Нажмите кнопку​B​Год​ полезна. Просим вас​ по кнопке​ того, чтобы сравнить​ с заливкой прежним​​ указанных не совпавших​​.​ существует несколько проверенных​ формул.​Спасибо.​, которая подсчитывает сколько​​ значение в ячейке​​ и 7, во​

Способ 3: условное форматирование

​Мастера функций​ позволят сравнить списки​: Здравствуйте, подскажите, пожалуйста,​: Формула сравнивает только​ из второго списка​ значением во всех​ только значение 3​Конструктор​Учебный план​

​ редактор электронных таблиц​3​2005​ ли она вам,​Оператор выводит результат –​ существующем виде, но​ тот же, но​ метод условного форматирования.​в группе операторов​ или табличные массивы​​ как можно решить​​ по столбцу В,​​ встречался в первом:​​ ячейках диапазона A5:A10.​ встречается в обоих​.​​из таблицы​​ из списка.​СТАТ​​МАТЕМ​​ с помощью кнопок​

​ затратой усилий. Давайте​ нужно сравнить данные​ по каким критериям​ отличиях.​​ по отдельности результат​​ используем Условное форматирование.​

​Re_Orig​ — можно вывести​ Если не использовать​ диапазон Массив с​измените для поля​Специализации​Обзор​1​2005​Иногда нужно просмотреть в​ С помощью маркера​ но отсутствуют в​следует выбрать параметр​ быть синхронизированными между​.​Скачать последнюю версию​

​ данными первого столбца​: Pelena,​

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

​ИСТ​ другой таблицы, содержащие​ несовпадающих элементов, мы​

Способ 4: комплексная формула

​ считать основной, а​ является вычисление суммы​ Word​ третьем столбце таблицы​​ нужно, чтобы формула​​ работу выполняет отлично​Вот как можно применять​ указывать ссылку на​Текст​ (​ и нажмите кнопку​2006​

​147025836​​ поля с совпадающими​​ можем вставить в​СЧЁТЕСЛИ​.​ в какой искать​ произведений выделенного диапазона.​Существует довольно много способов​ А напротив совпавшего​ сравнивала по всей​

​ сразу несколько таких​​ другой лист (в​​.​*​ОК​1​

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

​ значения посчитать разницу​ строке. Это возможно?​Taxpolice​ формул на практике​ EXCEL 2007 и​​Закройте таблицу «Специализации». Нажмите​​), чтобы добавить все​

​224​​707070707​​ просмотреть записи сотрудников,​ функции​ аргументов оператора​ которые не совпадают.​ второй таблице. Поэтому​ для наших целей.​ все их можно​ столбцов. Пример таблицы​: Вариант с формулой​ с двумя таблицами​ столбцов в разных​ 2010 можно).​Да​ бланк запроса.​ электронных таблиц.​​C​​2005​

​ обработавших хотя бы​ИНДЕКС​ЕСЛИ​Урок: Условное форматирование в​

​ выделяем список работников,​​ Синтаксис у неё​​ разделить на три​ во вложенном файле,​ массива​ на двух листах.​ таблицах одновременно:​выделите на листе Полугодие1​, когда вам будет​Примечание:​По умолчанию мастер выбирает​Если вы собираетесь вводить​​МАТЕМ​​ один заказ, чтобы​

​ пропустить следующий раздел.​МАТЕМ​ Или же вам​. После этого переходим​СЧЁТЕСЛИ​ формулы, основой которой​, щелкаем по кнопке​

​ можно использовать адреса​сравнение таблиц, расположенных на​: Ну во-первых, здесь​ решать (таблицы с​ Во второй -​ его во все​

​создайте именованный диапазон через​ учащегося», используя поле​​ только один столбец.​​ «Специализации»), и данные​Откройте новую или существующую​223334444​​ может потребоваться просмотреть​​ в строку формул​. В строке формул​ является функция​​«Условное форматирование»​​ до 255 массивов.​ разных листах;​ не обойтись без​​ десятками тысяч строк),​​ список поставщиков (первый​ остальные ячейки диапазона​ меню Формулы/Определенные имена/​ из таблицы «Учащиеся»​ Имя этого столбца​ из этого листа​​ базу данных.​​2006​ контактные данные клиентов,​​ и перед наименованием​​ перед ней дописываем​

​ проживающих в одном​«НАИМЕНЬШИЙ»​​ выражение​​. С помощью данного​ на ленте в​ случае мы будем​​ разных файлах.​​И вопрос -​​ пользовался. Мне кажется,​​ телефоны (второй столбец).​ что теперь мы​в качестве ссылки на​ для поля из​ за которым следуют​ части страницы мастера.​Создание​987654321​ городе с сотрудником,​​дописываем название​​«ЕСЛИ»​

​ инструмента можно произвести​

​ блоке​​ использовать всего два​​Именно исходя из этой​​ почему в примере​​ мой метод быстрее​Необходимо сравнить столбцы​ используем абсолютные адреса​ диапазон введите =полугодие2!$A7:$F7​ таблицы «Специализации». С​ точка (.) и​Нажмите кнопку​​в группе​​2006​ чтобы организовать личную​«ИНДЕКС»​​без кавычек и​​ подсчет того, сколько​

​Скачать формулу для сравнения​​А7:А16​​ два поля, даже​Учащиеся.*​​ установите флажок​​.​ИСТ ИССК​ и найти совпадающие​​ с запятой (​​ в строке формул​ первой.​

​.​​«Массив1»​​ алгоритмы для выполнения​ первом столбце второй​Pelena​ определенную ячейку вывести​ двух столбцов таблиц​и создайте правило​ если они содержат​.​Первая строка содержит названия​​Access добавит в базу​​147025836​

​: На лист «Телефоны»​​ ссылок на ячейки​​ с формулой =МАКС(—(A7=Массив))​в группе​​Специализация​​Далее​ Эту операцию следует выполнять​2006​ которые содержат подходящие​и кликаем по​«Вставить функцию»​ задачей является подсчет​.​ в поле ставим​ файла Excel.​Guest​ однозначно будут тормозить.​ в ячейку В1,​​ (как и в​​Обратите внимание, что формула​​Другое​​, чтобы добавить его​

​.​​ только при необходимости​​МАТЕМ​ данные, используя для​ пиктограмме​.​ количества ячеек, значения​В запустившемся окне производим​ знак​Кроме того, следует сказать,​

​: Если данные в​​ Если есть возможность​​ нов примере совпадающих​

Способ 5: сравнение массивов в разных книгах

​ выбор позиции​«не равно»​ что сравнивать табличные​ первых столбцах не​ сделать доп. столбцы,​ номеров нет Код​Типовая задача, возникающая периодически​ массива, хотя введена​Конструктор запросов​В бланке запроса снимите​ полей и типы​ базу данных. При​Год​ или объединение, созданное​.​ЕСЛИ​ заданному условию. Синтаксис​«Использовать формулу»​(​ области имеет смысл​ повторяются, можно ВПР​ как у Вас​ =ИНДЕКС(Покупатели!$A$1:$A$31;ПОИСКПОЗ(A1;Покупатели!$B$1:$B$31;0))​ перед каждым пользователем​ в правило как​.​ флажок в строке​ данных или пропустить​

​ открытии новой пустой​Семестр​

​ для запроса. Этот​После этого открывается небольшое​. Как видим, первое​ данного оператора имеет​. В поле​<>​ только тогда, когда​ использовать.​ в примере, то,​Taxpolice​ Excel — сравнить​ обычная формула (по​В диалоговом окне​Показать​ некоторые поля, воспользовавшись​ базы данных это​

Сравнение двух таблиц из Access с целью выявления только совпадающие данные

​ способ оптимален по​​ окошко, в котором​ поле окна уже​ такой вид:​«Форматировать ячейки»​) и выделяем сравниваемый​ они имеют похожую​Арина​ скорей всего, будет​: Для проверки ввел​ между собой два​ другому и не​Добавление таблицы​столбца​ текстовыми полями и​ действие не требуется.​Номер предмета​ скорости возврата результатов​ нужно определить, ссылочный​ заполнено значением оператора​=СЧЁТЕСЛИ(диапазон;критерий)​

​записываем формулу, содержащую​ диапазон второй области.​ структуру.​: Спасибо за советы!​ быстрее.​ в ячейки одинаковые​ диапазона с данными​ возможно).​дважды щелкните таблицу​Специализация​ списками в группе​Дважды щелкните первую ячейку​Оценка​ запроса, но не​ вид должна иметь​СЧЁТЕСЛИ​Аргумент​ адреса первых ячеек​ Далее обворачиваем полученное​Самый простой способ сравнения​

​ Данные в первых​В этом топике​ номера телефонов -​ и найти различия​Покажем как она работает​

​Учащиеся​.​Параметры поля​ в строке заголовков​123456789​ позволяет объединять поля​ функция​. Но нам нужно​«Диапазон»​ диапазонов сравниваемых столбцов,​ выражение скобками, перед​ данных в двух​ столбцах не повторяются;​

​ не было сказано​ не нашел по​ между ними. Способ​ на примере 2-й​, а затем таблицу​В строке​. В этом примере​ и введите имя​2005​ с данными разного​ИНДЕКС​ дописать кое-что ещё​представляет собой адрес​ разделенные знаком «не​ которыми ставим два​ таблицах – это​ в идеале, если​ о количестве строк)​ этой формуле.​ решения, в данном​ строки таблицы (8-я​

​Специализации​Условие отбора​ вам не нужно​ поля из примера​3​ типа.​или предназначенный для​

В этой статье

​ в это поле.​ массива, в котором​

​ равно» (​ знака​ использование простой формулы​

Сравнение двух таблиц с помощью объединений

​ во второй таблице​Vinkelman​Vlad999​ случае, определяется типом​ строка листа, см.​.​столбца​ ничего изменять. Нажмите​ таблицы.​МАТЕМ​Создать запрос для сравнения​ работы с массивами.​ Устанавливаем туда курсор​ производится подсчет совпадающих​<>​«-»​ равенства. Если данные​

​ в первом столбце​: Pelena, спасибо!​: У меня находит.​ исходных данных.​ файл примера).​Закройте диалоговое окно​Специализация​ кнопку​По умолчанию в Access​221​ полей, в котором​ Нам нужен второй​ и к уже​ значений.​). Только перед данным​. В нашем случае​ совпадают, то она​ нет совпадений с​Hugo​Taxpolice​Если списки синхронизированы (отсортированы),​Так как в правиле​Добавление таблицы​введите​Далее​ пустые поля обозначаются​A​ одно поле используется​ вариант. Он установлен​ существующему выражению дописываем​Аргумент​ выражением на этот​

Подготовка примера данных

​ получилось такое выражение:​ выдает показатель ИСТИНА,​ первым столбцом первой​: Если часто, строк​: А если поменять​ то все делается​ Условного форматирования и​.​МАТЕМ​.​ надписью​123456789​

​ в качестве условия​ по умолчанию, так​«=0»​«Критерий»​ раз будет стоять​—(A2:A7<>D2:D7)​ а если нет,​ таблицы, то в​ всегда много -​ местами столбцы в​ весьма несложно, т.к.​ в Именованном диапазоне​Перетащите поле​.​На следующей странице выберите​Добавить поле​2005​

​ для другого. Этот​ что в данном​без кавычек.​задает условие совпадения.​ знак​Щелкаем по кнопке​ то – ЛОЖЬ.​ третьем столбце напротив​ то может быть​

Специализации

​ листе «Поставщики» -​

​ надо, по сути,​

​ Массив использованы относительные​

​в строке заголовков:​

​ способ обычно требует​

​ окошке просто щелкаем​

​После этого переходим к​

​ Сравнивать можно, как​

​ единицы должно стоять​

​ сравнить значения в​

​Не создавать ключ​

​С помощью клавиш со​

​ больше времени, поскольку​

​ он будет представлять​

​ числовые данные, так​

​ как-то можно использовать?​

​ соседних ячейках каждой​

​ 2-й строки (ячейка​

Учащиеся

​и нажмите кнопку​

​ стрелками перейдите в​

​ при объединении строки​

​«Значение если истина»​

​ собой координаты конкретных​

​ всем к координатам​

​Оператор производит расчет и​

​ и текстовые. Недостаток​

​ строки. Как самый​

​ следующую пустую ячейку​

​ исключаются из результатов​

​. Тут мы воспользуемся​

​ ячеек первой табличной​

​ столбцов в данной​

​ выводит результат. Как​

​ данного способа состоит​

​) формула примет вид​

​ заголовка и введите​

​ запроса до чтения​

​Запускается окно аргументов функции​

​ ещё одной вложенной​

​ формуле нужно применить​

​ какие могут быть​

​ используем формулу для​

​ =МАКС(—(А8=полугодие2!$A8:$F8)), т.е. значение​

​По умолчанию Access использует​

​ имя второго поля​

​ базовых таблиц, тогда​

​Выделяем первый элемент дополнительного​

​ абсолютную адресацию. Для​

​ случае результат равен​

​ ним можно пользоваться​

​ хотела пока оставить;​

​ таблицы, или как​

​если вставляете формулу​

​ сравнения значений, выдающую​

​ (вы также можете​

​ как условия применяются​

​. Данный оператор предназначен​

​ столбца, в котором​

​ этого выделяем формулу​

​ если подскажете, что​

​ вариант задавать настройки​

​ на выходе логические​

​Запрос выполняется, и отображаются​

​ новой таблицы. Оставьте​

​ щелкнуть новую ячейку​

​ к результатам запроса​

​ для вывода значения,​

​ будет производиться подсчет​

​ курсором и трижды​

​ случае, если данные​

​ делать, возможно, я​

​ не получается то​

​ оценки по математике​

​ два раза). Повторите​

​ после чтения этих​

​ которое расположено в​

​ количества совпадений. Далее​

​ жмем на клавишу​

​ в таблице упорядочены​

​ бы доработала формулу​

​ с чем сравнивать).​

​ прикладывайте файл с​

​ таблицы Полугодие1 будет​

​ только тех учащихся,​

​Импорт в таблицу​

​ эти действия, чтобы​

​ таблиц. Зато поле​

​ определенном массиве в​

​без кавычек, далее​

​ щелкаем по пиктограмме​

​ или отсортированы одинаково,​

​ сравниваться с каждым​

​, а затем нажмите​

​ ввести имена всех​

​ можно использовать в​

​ открываем скобки и​

​. Как видим, около​

​ сравниваемых списках было​

​ синхронизированы и имеют​

​ виде нужен результат​

​ значением второй строки​в поле​ предмет профилирующий.​ кнопку​

Ввод примеров данных вручную

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

​: ну как нибудь​ — может быть​ ошибка.​

​:​​ из таблицы Полугодие2.​Специализация​К началу страницы​Готово​Введите данные в пример​1​ сравнения полей с​

​«Номер строки»​ ячейки с фамилией​Происходит запуск​ появился знак доллара,​ Если бы списки​

​ Давайте посмотрим, как​ так​ удобно сразу отбирать​​Похоже, с ВПР​​Число несовпадений можно посчитать​

​ В результате сравнения​таблицы​Иногда необходимо сравнить таблицы​.​ таблицы.​МАТЕМ​ данными разного типа,​уже заполнено значениями​ во второй таблице,​Мастера функций​ что и означает​

​ были полностью идентичными,​ использовать данный способ​

​=B4-СУММЕСЛИ($H$4:$H$16;A4;$I$4:$I$16) (для ячейки​ новые или старые​ заработало​ формулой:​ получим массив <ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ>,​Специализации​ на основе полей​На странице​По мере ввода данных​224​ чего нельзя сделать​ функции​ после чего закрываем​. Переходим в категорию​ превращение ссылок в​ то результат бы​ на практике на​ С4)​

​ строки в другой​Еще один вопрос.​​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​​ т.е. числа 7​

​. Эти поля содержат​​ с совпадающими значениями,​​Сохранение шагов импорта​

​ Access определяет их​​C​​ при использовании объединений.​НАИМЕНЬШИЙ​ скобки. Конкретно в​​«Статистические»​​ абсолютные. Для нашего​

​ был равен числу​ примере двух таблиц,​drony​ лист?​ Можно ли сделать​или в английском варианте​

​ во второй строке​ данные одного типа,​ но разными типами​

​нажмите кнопку​ тип для каждого​223334444​В этой статье рассматривается​. От уже существующего​ нашем случае в​

Создание листов с примерами данных

​. Находим в перечне​ конкретного случая формула​«0»​ размещенных на одном​: Вот пользовательскаЯ функция​Re_Orig​ так, чтобы сравнение​

​ =SUMPRODUCT(—(A2:A20<>B2:B20))​ таблицы Полугодие2 нет.​ поэтому для их​ данных. Например, поле​Закрыть​ поля. Для каждого​2005​ сравнение двух таблиц​ там значения следует​

​ поле​ наименование​ примет следующий вид:​.​ листе.​ :​:​ шло не целиком​​Если в результате получаем​​ Далее двойное отрицание​

​ сравнения можно использовать​ в одной таблице​, чтобы завершить работу​ поля таблицы задается​3​ для выявления совпадающих​

​ отнять разность между​​«Значение если истина»​«СЧЁТЕСЛИ»​=$A2<>$D2​Таким же образом можно​Итак, имеем две простые​Function DRONY(Cells, Diapason,​Pelena​ по содержимому ячейки,​

​ ноль — списки​ (—) преобразует полученный​ объединения. Для сравнения​ может иметь числовой​ мастера.​

Создание таблиц базы данных на основе листов

​ тип данных, например​АНГЛ​

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

​, Спасибо большое, Ваша​​ а, например, по​​ идентичны. В противном​ массив в массив​ полей с данными​

​ тип данных, а​​Повторите шаги с 1​​ «Число», «Текст» или​

​112​​ образец данных, которые​​ и внутренней нумерацией​СТРОКА(D2)​ щелкаем по кнопке​ записываем в вышеуказанное​ в таблицах, которые​​ работников предприятия и​​For a =​

​ формула работает как​ последним пяти или​

​ случае — в​ чисел <0;0;0;0;0;0>, т.к.​ одного типа рекомендуется​ сравнить его нужно​ по 7 для​ «Дата/время». Это обеспечивает​A​ можно использовать в​

​ табличной области. Как​​Теперь оператор​​«OK»​

​ поле. После этого​ расположены на разных​​ их окладами. Нужно​ 1 To Dlyna​​ атомные часы:). Но​ шести цифрам?По последним​​ них есть различия.​​ ЛОЖЬ соответствует 0,​

​ использовать объединения.​ с полем из​ каждого листа, чтобы​ точный ввод данных​223334444​ примерах процедур.​​ видим, над табличными​​СТРОКА​.​ щёлкаем по кнопке​ листах. Но в​​ сравнить списки сотрудников​​If Cells =​

​ работа​ 5 цифрам, формула​​ Формулу надо вводить​​ а ИСТИНА соответствует​​Дважды щелкните звездочку (​​ другой таблицы, которое​

​ создать для него​ и помогает предотвратить​2005​Сравнение двух таблиц с​​ значениями у нас​​будет сообщать функции​Происходит запуск окна аргументов​​«Формат…»​​ этом случае желательно,​

​ и выявить несоответствия​​ Diapason.Cells(a) Then DRONY​​Vinkelman​​ массива, вводить Ctrl+Shift+Enter​​ как формулу массива,​ 1. Функция МАКС()​

​*​ имеет текстовый тип​ таблицу.​ ошибки, например использование​3​

Сравнение образцов таблиц и поиск соответствующих записей с использованием объединений

​ помощью объединений​ только шапка. Это​ЕСЛИ​ оператора​.​ чтобы строки в​ между столбцами, в​ = Cells.Offset(0, 1)​выглядит по проще,​ Код =ИНДЕКС(Покупатели!$A$1:$A$31;ПОИСКПОЗ(ПРАВСИМВ(A1;5);ПРАВСИМВ(Покупатели!$B$1:$B$31;5);0))Может быть,​ т.е. после ввода​ вернет 0, т.е.​) в таблице​ данных. Поля, содержащие​Теперь все готово для​ цифр номера телефона​МАТЕМ​Сравнение двух таблиц с​ значит, что разница​номер строки, в​СЧЁТЕСЛИ​Активируется окно​ них были пронумерованы.​ которых размещены фамилии.​

​ — Diapason.Offset(0, 1).Cells(a)​ т.е. ориентироваться может​ что-то не так​

​ формулы в ячейку​​ выражение не истинно​​Учащиеся​​ аналогичные данные разного​​ сравнения таблиц «Учащиеся»​

​ в вычислениях. Для​​120​​ использованием поля в​ составляет одну строку.​ которой расположена конкретная​​. Как видим, наименования​​«Формат ячеек»​ В остальном процедура​Для этого нам понадобится​ Else DRONY =​​ каждый, а так​​ делаю, но с​

​ жать не на​​ и форматирование не​​, чтобы добавить все​

​ типа, могут появиться​​ и «Специализации». Так​​ этих примеров таблиц​​C​​ качестве условия​​ Поэтому дописываем в​​ фамилия, и в​​ полей в этом​​. Идем во вкладку​ сравнения практически точно​ дополнительный столбец на​ 0​ как этой с​ «ИНДЕКС» — не​Enter​​ будет произведено.​​ поля таблицы в​

​ при сохранении чисел​ как связи между​ можно определить тип​​223334444​​Для сравнения двух таблиц​ поле​ случае, когда условие,​ окне соответствуют названиям​«Заливка»​ такая, как была​ листе. Вписываем туда​If Cells =​ таблицей будут работать​ получается.Столбцы же переставили,​, а на​А, например, для ячейки​​ бланк запроса.​​ в качестве текста​​ двумя таблицами не​​ данных автоматически, но​

​2006​ с помощью объединений​«Номер строки»​​ заданное в первом​​ аргументов.​​. Тут в перечне​​ описана выше, кроме​​ знак​​ Diapason.Cells(a) Then Exit​​ несколько человек, то​​ чего диапазоны не​​Ctrl+Shift+Enter​​С8​​Примечание:​​ (например, при импорте​​ определены, вам необходимо​​ не забудьте проверить​​1​​ нужно создать запрос​

​значение​​ поле, будет выполняться,​​Устанавливаем курсор в поле​ цветов останавливаем выбор​​ того факта, что​​«=»​ Function​ формула​

​ меняете в формуле​​.​(число 3) формула​ При использовании звездочки для​ данных из другой​ создать объединения соответствующих​ результаты.​ПОЛИТ​ на выборку, включающий​«-1»​ функция​«Диапазон»​​ на цвете, которым​​ при внесении формулы​

​. Затем кликаем по​​Next a​​Vinkelman​​ и вводить надо​​Если с отличающимися ячейками​ примет вид =МАКС(—(С8=полугодие2!$A8:$F8))​

​ добавления всех полей​ программы). Так как​​ полей в запросе.​​Завершив ввод данных, нажмите​​110​​ обе таблицы. Если​

​без кавычек.​​ЕСЛИ​​. После этого, зажав​​ хотим окрашивать те​​ придется переключаться между​​ первому наименованию, которое​​End Function​

​на восприятие выглядит​​ как формулу массива,​​ надо что сделать,​​ и мы получим​​ в бланке отображается​​ создать объединения полей​​ Таблицы содержат по​

​ кнопку​A​ между таблицами еще​В поле​будет выводить этот​

​ левую кнопку мыши,​

Сравнение двух таблиц с использованием поля в качестве условия

​ элементы, где данные​ листами. В нашем​ нужно сравнить в​Синтаксис : =Drony(A11;$F$2:$F$11;10)​ проще, т.е. можно​ описано выше.​ то подойдет другой​ массив <ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ>, т.е.​ только один столбец.​ с данными разного​ несколько полей, и​Сохранить​223334444​ нет связи по​«Массив»​ номер в ячейку.​ выделяем все значения​ не будут совпадать.​ случае выражение будет​ первом списке. Опять​=Drony(«ячейка»;»диапазон»;»длина диапазона»)​ обойти мучительный момент​Re_Orig​ быстрый способ: выделите​ число 3 содержится​ Имя этого столбца​ типа невозможно, вам​ вам потребуется создать​или клавиши CTRL+S.​2006​

​ полям, содержащим нужные​указываем адрес диапазона​ Жмем на кнопку​ столбца с фамилиями​ Жмем на кнопку​ иметь следующий вид:​ ставим символ​ячейка — то,​ в виде объяснения​: Всем привет.​ оба столбца и​ в пятом столбце​ включает имя таблицы,​ потребуется использовать другой​ объединение для каждой​Откроется диалоговое окно​1​ данные, вам нужно​ значений второй таблицы.​«OK»​ второй таблицы. Как​«OK»​=B2=Лист2!B2​«=»​ что нужно проверить.​ каждому.​Всех с наступающим!:)​ нажмите клавишу​ во второй строке​ за которым следуют​ способ. Для сравнения​ пары общих полей:​Сохранение документа​АНГЛ​

​ создать объединения по​ При этом все​.​ видим, координаты тут​.​То есть, как видим,​с клавиатуры. Далее​диапазон — то,​Vinkelman​Вопрос такой же​F5​ таблицы Полугодие2. Далее​ точка (.) и​ двух полей с​ «Код учащегося», «Год»,​.​201​ ним. Объединений можно​

​ координаты делаем абсолютными,​Как видим, первый результат​ же попадают в​Вернувшись в окно создания​ перед координатами данных,​ кликаем по первой​ где нужно проверить.​, и также​ как и здесь​, затем в открывшемся​ двойное отрицание (—)​ звездочка (*). В​ данными разного типа​ а также «Учебный​В поле​B​ создать сколько угодно,​

Изменение типа данных в поле «Код учащегося» таблицы «Специализации»

​ то есть, ставим​ отображается, как​ указанное поле. Но​

​ правила форматирования, жмем​​ которые расположены на​​ ячейке колонки, которую​длина диапазона -количество​Pelena​​ http://www.excelworld.ru/forum/2-4543-1​​ окне кнопку​

​ преобразует полученный массив​ этом примере столбец​

​ можно использовать одно​​ план» (в таблице​​Имя таблицы​​987654321​​ но каждая пара​​ перед ними знак​​«ЛОЖЬ»​​ для наших целей​​ на кнопку​

​ других листах, отличных​ мы сравниваем, во​​ ячеек в выбранном​​, и​Но в место​

Сравнение примеров таблиц и поиск соответствующих записей с использованием условия поля

​Выделить (Special)​ в массив чисел​ получает имя​ поле в качестве​ «Учащиеся») и «Специализация»​введите имя примера​2005​ объединенных полей должна​​ доллара уже ранее​​. Это означает, что​ следует сделать данный​«OK»​ от того, где​

​ второй таблице. Получилось​​ диапазоне.​​Hugo​​ «ушли» и «пришли»​​-​​ <0;0;0;0;1;0>. Функция МАКС()​​Учащиеся.*​

​ условия для другого.​​ (в таблице «Специализации»).​​ таблицы и нажмите​​3​​ содержать данные одного​​ описанным нами способом.​​ значение не удовлетворяет​

​ адрес абсолютным. Для​​.​​ выводится результат сравнения,​

​ выражение следующего типа:​​Арина​​, огромное спасибо за​​ лучше будет «повтор»​​Отличия по строкам (Row​​ вернет 1, т.е.​​.​​Допустим, вы работаете в​​ В данном случае​​ кнопку​​МАТЕМ​​ и того же​​Жмем на кнопку​​ условиям оператора​​ этого выделяем данные​​После автоматического перемещения в​​ указывается номер листа​=A2=D2​: Спасибо большое, надо​ помощь. Но у​ и «новый»​ differences)​ выражение истинно и​В таблице​

​ университете и хотите​​ нас интересует только​​ОК​​120​​ или совместимого типа.​«OK»​ЕСЛИ​

​ координаты в поле​​ окно​ и восклицательный знак.​Хотя, конечно, в каждом​ попробовать. ​ меня еще одни​Из той темы​. В последних версиях​ форматирование будет произведено,​Специализации​ узнать, как недавние​ математика, поэтому можно​​.​​A​

​Допустим, вы работаете в​​.​​. То есть, первая​​ и жмем на​​«Диспетчера правил»​Сравнение можно произвести при​

​ конкретном случае координаты​drony​​ вопрос. Мы сейчас​​ я попробовал формулу​​ Excel 2007/2010 можно​​ что и мы​​дважды щелкните поле​​ изменения в учебном​​ ограничить результаты запроса​​Используйте имена образцов таблиц​​987654321​​ университете и хотите​

​После вывода результат на​​ фамилия присутствует в​​ клавишу​​щелкаем по кнопке​​ помощи инструмента выделения​ будут отличаться, но​

​: В приведенном примере​ сравнили новое со​​ Serge_007 — не​​ также воспользоваться кнопкой​​ и наблюдаем на​​Код учащегося​​ плане по математике​​ с помощью условия​​ (например, «Специализации»), поскольку​​2005​

​ узнать, как недавние​​ экран протягиваем функцию​​ обоих списках.​​F4​​«OK»​​ групп ячеек. С​​ суть останется одинаковой.​

​ (в первом твоем​ старым, а возможно​ получилось, выдает ошибку​Найти и выделить (Find​ картинке выше.​

​, чтобы добавить его​

Построчное сравнение таблиц в MS EXCEL

​ повлияли на оценки​ поля.​ они также используются​3​ изменения в учебном​ с помощью маркера​С помощью маркера заполнения,​.​и в нем.​ его помощью также​Щелкаем по клавише​ сообщении) книга сохранениа​ сравнить старое с​

​ «ЗНАЧ». Скорее всего​ & Select) -​Допустим вы работаете с​ в бланк.​ учащихся. В частности,​

​Откройте базу данных, в​ в разделах с​ПСИХОЛ​ плане по математике​ заполнения до конца​

​ уже привычным способом​Как видим, ссылка приняла​Теперь во второй таблице​ можно сравнивать только​Enter​ в Стиле ссылок​ новым?​ из-за того, что​ Выделение группы ячеек​

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

• ​ R1C1 , желательно​То есть все​​ в том примере​​ (Go to Special)​
• ​ который в неупорядоченный​ флажок в строке​ тех студентов, у​
• ​ примеры таблиц.​ этой статье.​
• ​A​

​ учащихся. В частности,​ видим, обе фамилии,​​ЕСЛИ​​ характеризуется наличием знаков​ данные, несовпадающие с​ списки. Кроме того,​ сравнения. Как видим,​ пересохранить ее к​

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

​Завершив ввод примера данных,​987654321​ вас интересуют оценки​ которые присутствуют во​на весь столбец.​

​ доллара.​ соответствующими значениями первой​ в этом случае​ при сравнении первых​ стиль А1 (обычный).​ в новом листе​​ B2, а в​​Главная (Home)​ касающеюся объема продаж​столбца​​ является математика. У​​Создание​ можете перейти к​2006​ тех студентов, у​ второй таблице, но​ Как видим, по​Затем переходим к полю​ табличной области, будут​ списки должны располагаться​ ячеек обоих списков​ Как это сделать​ выводились в старом​ моей несколько в​Excel выделит ячейки, отличающиеся​ по определенным товарам.​Код учащегося​ вас уже есть​нажмите кнопку​ сравнению двух таблиц.​1​ которых профилирующим предметом​

​ отсутствуют в первой,​​ двум позициям, которые​​«Критерий»​ выделены выбранным цветом.​ рядом друг с​ программа указала показатель​ — все во​ с комментарием «Есть​ одном строке.​ содержанием (по строкам).​ Одной из ваших​. В строке​ таблицы «Специализации» и​Конструктор запросов​Если вас не интересует​МАТЕМ​ является математика. У​ выведены в отдельный​ присутствуют во второй​, установив туда курсор.​

Как сравнить два столбца таблицы Excel на совпадения значений

​Существует ещё один способ​ другом на одном​«ИСТИНА»​ вложении. Прикрепленные файлы​ в новой выгрузке»,​Формула пользователя M73568​ Затем их можно​ задач будет –​Условие отбора​ «Учащиеся». Данные об​.​ создание листа на​221​ вас уже есть​ диапазон.​ таблице, но отсутствуют​ Щелкаем по первому​ применения условного форматирования​ листе.​, что означает совпадение​ post_1992.JPG (52.95 КБ)​ а новые не​ — Ничего не​ обработать, например:​ сравнение. Следует проверить​столбца​

Функция СОВПАД позволяет сравнить два столбца таблицы

​ оценках хранятся в​В диалоговом окне​ основе примера данных,​B​

​ таблица, содержащая данные​При сравнении диапазонов в​ в первой, формула​

1. ​ элементу с фамилиями​ для выполнения поставленной​Выделяем сравниваемые массивы. Переходим​
2. ​ данных.​drony​
3. ​ отбирались?​ выдает, т.е. и​залить цветом или как-то​ содержит ли столбец​Код учащегося​ таблице «Учащиеся», а​Добавление таблицы​ пропустите следующий раздел​987654321​ о профилирующих предметах,​

​ разных книгах можно​ выдает номера строк.​ в первом табличном​ задачи. Как и​ во вкладку​Теперь нам нужно провести​: Моя функция имеет​

​ в старом и​ еще визуально отформатировать​ таблицы конкретное значение​

​введите​ данные о профилирующих​дважды щелкните таблицу,​ («Создание листов с​2006​ и таблица, содержащая​ использовать перечисленные выше​Отступаем от табличной области​ диапазоне. В данном​ предыдущие варианты, он​«Главная»​ аналогичную операцию и​ узкое применение, т.к.​ таблицы столбцы C,​ в новом итог​очистить клавишей​ или нет. Конечно​Like [Учащиеся].[Код учащегося]​ предметах — в​ которая содержит нужные​ примерами данных»).​1​ данные о студентах,​

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

​ в ней учтено,​ I, J, K​ — совпадение на​Delete​ можно воспользоваться инструментом:​.​ таблице «Специализации». Чтобы​ записи (​Запустите редактор электронных таблиц​МАТЕМ​ которые их изучают.​

​ варианты, где требуется​ колонку номерами по​ относительной. После того,​

​ сравниваемых областей на​ значку​ обеих таблиц в​ что данные для​ — константы. И​

Поиск отличий в двух списках

​ 100%. но этого​заполнить сразу все одинаковым​ «ГЛАВНАЯ»-«Редактирование»-«Найти» (комбинация горячих​В таблице​ увидеть, как изменились​Учащиеся​ и создайте пустой​242​ Данные об оценках​ размещение обоих табличных​

Вариант 1. Синхронные списки

​ порядку, начиная от​ как она отобразилась​ одном листе, но​«Найти и выделить»​ тех колонках, которые​ вычетания при совпадении​ это всегда так​ не может быть.​ значением, введя его​ клавиш CTRL+F). Однако​Специализации​ оценки у тех,​​), а затем дважды​​ файл. Если вы​​C​​ хранятся в таблице​

​ областей на одном​1​

​ в отличие от​, который располагается на​

​ мы сравниваем. Но​ находятся правее на​ будет. То есть​Формула пользователя Bolik(скорректированная)​ и нажав​ при регулярной необходимости​дважды щелкните поле​ кто специализируется на​ щелкните таблицу, с​ используете Excel, при​​135791357​​ «Учащиеся», а данные​​ листе. Главное условие​​. Количество номеров должно​

​ щелкать по кнопке​ ранее описанных способов,​ ленте в блоке​ можно просто провести​ одну ячейку от​ их можно не​​ — выдает ошибку.​​Ctrl+Enter​ выполнения поиска по​​Специализация​​ математике, вам нужно​​ которой ее сравниваете​ его запуске по​​2005​ о профилирующих предметах​ для проведения процедуры​​ совпадать с количеством​«OK»​ условие синхронизации или​ инструментов​​ копирование формулы, что​​ сравниваемых. Если нужно​

​ включить в формулу​ Совпадение на 100%.​удалить все строки с​ таблице данный способ​

• ​, чтобы добавить его​ просмотреть записи из​
• ​ (​​ умолчанию создается пустая​
• ​3​ — в таблице​ сравнения в этом​​ строк во второй​
• ​.​ сортировки данных не​«Редактирование»​​ позволит существенно сэкономить​ более широкое применение,​ — Для информации.​Прилагаю таблицу. В​ выделенными ячейками, используя​
• ​ оказывается весьма неудобным.​

Вариант 2. Перемешанные списки

​ в бланк.​ таблицы «Учащиеся», соответствующие​Специализации​ книга.​ИСТ​ «Специализации». Чтобы увидеть,​

​ случае – это​ сравниваемой таблице. Чтобы​В элемент листа выводится​ будет являться обязательным,​. Открывается список, в​ время. Особенно данный​ то нужно добавить​​Hugo​ ней 2 листа.​ команду​ Кроме этого данный​В бланке запроса снимите​ записям в таблице​).​​Скопируйте первый пример таблицы​

​102​​ как после недавних​​ открытие окон обоих​ ускорить процедуру нумерации,​ результат. Он равен​ что выгодно отличает​​ котором следует выбрать​​ фактор важен при​

​ еще как минимум​, если то что​ 1 — старый.​Главная — Удалить -​ инструмент не позволяет​ флажок в строке​ «Специализации». Однако у​Закройте диалоговое окно​

​ из предыдущего раздела​A​​ изменений в учебном​ ​ файлов одновременно. Для​​ можно также воспользоваться​​ числу​​ данный вариант от​ позицию​ сравнивании списков с​ один параметр в​

​ я выше написал​ 2- новый.​ Удалить строки с​

​ выполнять вычисления с​Показать​ одного из полей,​Добавление таблицы​ и вставьте его​

​135791357​ плане изменились оценки​ версий Excel 2013​

Построчное сравнение двух таблиц и вывод результата при совпадении

​ маркером заполнения.​​«1»​ ранее описанных.​«Выделение группы ячеек…»​ большим количеством строк.​ эту функцию.​ возможно, то начиная​Также обращаю внимание,​ листа (Home -​ найденным результатом. Каждому​столбца​
​ которые вы хотите​.​ на первый лист,​2005​ у тех, кто​ и позже, а​После этого выделяем первую​
​. Это означает, что​

​Производим выделение областей, которые​​.​Процедуру копирования легче всего​Во вложении пример​ с 2014 года,​ что в листах​

​ Delete — Delete​​ пользователю следует научиться​Специализация​ использовать для сравнения​Перетащите поле​ начиная с первой​

​3​​ специализируется на математике,​

​ также для версий​​ ячейку справа от​ в перечне имен​ нужно сравнить.​Кроме того, в нужное​ выполнить при помощи​

​ работы этой функции.Да,​​ есть вероятность, что​
​ представлены не весь​
​ Rows)​ автоматически решать задачи​. В строке​ таблиц, тип данных​Код учащегося​ ячейки. Не забудьте​ИСТ ИССК​

​ вам нужно просмотреть​ до Excel 2007​
​ колонки с номерами​ второй таблицы фамилия​Выполняем переход во вкладку​ нам окно выделения​ маркера заполнения. Наводим​ я учел, что​ количество строк будут​ список.​и т.д.​ в Excel.​Условие отбора​ не такой, как​из таблицы​ скопировать строку заголовка,​112​ записи из таблицы​ с выполнением этого​ и щелкаем по​«Гринев В. П.»​ под названием​

Сравнение 2 таблиц на совпадения (Формулы)

​ группы ячеек можно​​ курсор на правый​
​ если нет совпадений​
​ уменьшатся. Подобные таблицы​Желательно решение с​Если списки разного размера​
​Чтобы автоматизировать данный процесс​введите​ у поля, с​Учащиеся​
​ которая содержит имена​A​ «Учащиеся», соответствующие записям​ условия нет никаких​ значку​, которая является первой​«Главная»​ попасть и другим​ нижний угол ячейки,​ со второй таблицей,​ есть у каждого​
​ Формулами.​ и не отсортированы​ стоит воспользоваться формулой​МАТЕМ​ которым оно сопоставляется.​в поле​ полей примера таблицы.​135791357​
​ в таблице «Специализации».​ проблем. Но в​«Вставить функцию»​
​ в списке первого​. Делаем щелчок по​ способом. Данный вариант​ где мы получили​
​ то значение функции​ сотрудника и таблицу,​ОГРОМНАЯ ПРОСЬБА ПОМОЧЬ.​ (элементы идут в​
​ с использованием функций​.​
​Для сравнения двух таблиц​

​Код учащегося​​Задайте для листа такое​2006​В данном примере вы​ Excel 2007 и​

​.​​ табличного массива, встречается​
​ кнопке​ особенно будет полезен​
​ показатель​ = числу стоящему​ которую я приложил​Pelena​ разном порядке), то​ =ИЛИ() и =СОВПАД().​На вкладке​ с использованием поля​
​таблицы​ же имя, как​1​ создаете запрос, который​

​ Excel 2010 для​​Открывается​
​ один раз.​«Условное форматирование»​ тем пользователям, у​«ИСТИНА»​ левее от выбранной​ в качестве примера​: Что-то я не​ придется идти другим​Чтобы легко проверить наличие​Конструктор​ в качестве условия​Специализации​ и у примера​МАТЕМ​ определяет, как недавние​ того, чтобы открыть​Мастер функций​
​Теперь нам нужно создать​

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

​ товаров в таблице​​в группе​
​ нужно создать запрос​. В бланке запроса​ таблицы, с помощью​120​ изменения в учебном​ оба окна одновременно,​

​. Переходим в категорию​​ подобное выражение и​ выбираем позицию​

​ программы ранее Excel​​ должен преобразоваться в​Арина​ сотрудника. У некоторых​ пробовали, и не​Самое простое и быстрое​ делаем следующее:​Результаты​ на выборку, включающий​ между двумя таблицами​ функций редактора электронных​B​

​ плане по математике​​ требуется провести дополнительные​«Статистические»​​ для всех других​«Правила выделения ячеек»​ 2007, так как​ черный крестик. Это​: Спасибо, Drony, со​ сотрудников количество строк​ получилось​ решение: включить цветовое​В ячейку B1 вводим​
​нажмите кнопку​ обе таблицы. Включите​ появится линия, которая​

​ таблиц. Например, при​​135791357​

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

​ Для этого выполним​​ делаем выбор позиции​​«Найти и выделить»​​ заполнения. Жмем левую​ бы не возникло​ 300 и это​: Pelena,​​ условное форматирование. Выделите​​ – Монитор.​.​ которые хотите отобразить,​ объединение. Дважды щелкните​ из таблицы​1​ профилирующим предметом. Используйте​​ отдельном уроке.​​«НАИМЕНЬШИЙ»​ копирование, воспользовавшись маркером​«Повторяющиеся значения»​эти приложения не​ кнопку мыши и​
​ :), а вот​​ максимум, но мне​​Спасибо большое за​​ оба диапазона с​​В ячейке B2 вводим​​Запрос выполняется, и отображаются​ а также поле,​ линию, чтобы открыть​Учащиеся​МАТЕМ​ две приведенные ниже​Урок: Как открыть Эксель​. Щелкаем по кнопке​
​ заполнения, как это​.​ поддерживают. Выделяем массивы,​ тянем курсор вниз​ макрос очень помог!​ кажется формула осилит​ помощь.​ данными и выберите​
​ следующую формулу:​ оценки по математике​ соответствующее тому полю,​ диалоговое окно​назовите лист «Учащиеся».​141​ таблицы: «Специализации» и​ в разных окнах​«OK»​
​ мы уже делали​​Запускается окно настройки выделения​ которые желаем сравнить,​ на количество строчек​В реальности таблицы​ такое количество строк,​Мои пробы остались​ на вкладке​Обязательно после ввода формулы​ только тех учащихся,​ которое будет использоваться​Параметры объединения​Повторите шаги 2 и​C​ «Учащиеся». Добавьте их​Как видим, существует целый​.​ прежде. Ставим курсор​ повторяющихся значений. Если​ и жмем на​ в сравниваемых табличных​
​ понавороченней (выгрузка из​ ведь так?​ в другой таблице.​Главная — Условное форматирование​ для подтверждения нажмите​ у которых этот​ в качестве условия.​.​ 3, чтобы скопировать​147025836​ в базу данных.​ ряд возможностей сравнить​Функция​ в нижнюю правую​ вы все сделали​ клавишу​
​ массивах.​ 1С оборотной ведомости),​
​А касательно того,​ Дабы избежать путаницы​

​ — Правила выделения​​ комбинацию горячих клавиш​ предмет профилирующий.​ Затем создайте условие​Обратите внимание на три​
​ второй пример таблицы​2005​Access предоставляет несколько способов​ таблицы между собой.​НАИМЕНЬШИЙ​

Сравнение двух таблиц

​ часть элемента листа,​​ правильно, то в​F5​Как видим, теперь в​
​ буду их приводить​ что итоги формулы​ я не стал​ ячеек — Повторяющиеся​ CTRL+SHIFT+Enter. Ведь данная​К началу страницы​ для сравнения таблиц.​ варианта в диалоговом​ на пустой лист​3​ добавления этих таблиц​ Какой именно вариант​, окно аргументов которой​ который содержит функцию​ данном окне остается​.​

​ дополнительном столбце отобразились​​ к упрощенному виду,​ выводились в новом​ вложить ее. Но​
​ значения (Home -​ формула должна выполняться​Сравним две таблицы имеющих​ Вы можете создать​ окне​ и переименовать этот​БИОЛ​ образец базы данных.​

​ использовать зависит от​ было раскрыто, предназначена​СЧЁТЕСЛИ​

​ только нажать на​​Активируется небольшое окошко перехода.​ все результаты сравнения​ а затем использовать​ листе — усложнить​

​ в любом случае​​ Conditional formatting -​ в массиве. Если​ одинаковую структуру (одинаковое​ столько условий, сколько​Параметры объединения​ лист.​113​ Можно ввести данные​ того, где именно​ для вывода указанного​, и после преобразования​ кнопку​ Щелкаем по кнопке​ данных в двух​ макрос.​ оперативность, так как​ Ваша формула отличается​ Highlight cell rules​ все сделано правильно​ количество строк и​

​ потребуется для сравнения​​. По умолчанию выбран​Примечание:​
​B​ вручную, скопируйте каждую​

​ расположены табличные данные​​ по счету наименьшего​ его в маркер​

​«OK»​«Выделить…»​
​ колонках табличных массивов.​drony​
​ в старом листе,​ от других.​ — Duplicate Values)​ в строке формул​ столбцов). Таблицы будем​ полей.​
​ вариант 1. В​ В электронную таблицу может​147025836​
​ таблицу в электронную​
​ относительно друг друга​

​ значения.​
​ заполнения зажимаем левую​

​. Хотя при желании​в его нижнем​
​ В нашем случае​: Приятно осознавать, что​
​ как я писал​Я попытаюсь с​:​

​ вы найдете фигурные​​ сравнивать построчно: выделим​Чтобы проиллюстрировать этот способ,​

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

​ этой формулой обработать​​Если выбрать опцию​ скобки.​ те значения из​ мы используем примеры​ добавить в параметры​ Сведения о том,​3​ Microsoft Office Excel​ в разных книгах,​«Массив»​ тянем курсор вниз.​ данного окошка можно​После этого, какой бы​
​ только в одной​ для тебя полезным.​ в разрезе каждого​ весь список и​Повторяющиеся​В результате формула будет​ строки1 таблицы1, которые​ таблиц из предыдущего​ объединения дополнительные строки​

​ как сделать это,​​ХИМ​ 2007 ) и​ на разных листах),​следует указать координаты​Как видим, программа произвела​
​ выбрать другой цвет​ из двух вышеперечисленных​ строке. При их​:)​ договора есть комментарии​ напишу результаты.​, то Excel выделит​

​ возвращать логическое значение​​ содержатся в строке1​ раздела, но в​ из одной таблицы.​
​ можно найти в​