Как сравнить два прайса в excel на совпадения

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

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

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

Для любой задачи в 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 в заданной папке с помощью Power Query
• Как найти совпадения между двумя списками в Excel
• Слияние двух списков без дубликатов

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

Читайте также: Сравнение двух документов в MS Word

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

Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы:

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

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

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

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

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

1. Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=». Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=» с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:

   =A2=D2

   

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

2. Щелкаем по клавише Enter, чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель «ИСТИНА», что означает совпадение данных.



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

   Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель «ИСТИНА». При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах.



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



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





6. В окне Мастера функций в группе операторов «Математические» выделяем наименование СУММПРОИЗВ. Щелкаем по кнопке «OK».



7. Активируется окно аргументов функции СУММПРОИЗВ, главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:

   =СУММПРОИЗВ(массив1;массив2;…)

   Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.

   Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<>) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-». В нашем случае получилось такое выражение:

   --(A2:A7<>D2:D7)

   Щелкаем по кнопке «OK».



8. Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу «1», то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу «0».

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

=B2=Лист2!B2

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

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

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

1. Выделяем сравниваемые массивы. Переходим во вкладку «Главная». Далее щелкаем по значку «Найти и выделить», который располагается на ленте в блоке инструментов «Редактирование». Открывается список, в котором следует выбрать позицию «Выделение группы ячеек…».
   

   Кроме того, в нужное нам окно выделения группы ячеек можно попасть и другим способом. Данный вариант особенно будет полезен тем пользователям, у которых установлена версия программы ранее Excel 2007, так как метод через кнопку «Найти и выделить» эти приложения не поддерживают. Выделяем массивы, которые желаем сравнить, и жмем на клавишу F5.

2. Активируется небольшое окошко перехода. Щелкаем по кнопке «Выделить…» в его нижнем левом углу.



3. После этого, какой бы из двух вышеперечисленных вариантов вы не избрали, запускается окно выделения групп ячеек. Устанавливаем переключатель в позицию «Выделить по строкам». Жмем по кнопке «OK».



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

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

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

1. Прежде всего, выбираем, какую табличную область будем считать основной, а в какой искать отличия. Последнее давайте будем делать во второй таблице. Поэтому выделяем список работников, находящийся в ней. Переместившись на вкладку «Главная», щелкаем по кнопке «Условное форматирование», которая имеет месторасположение на ленте в блоке «Стили». Из выпадающего списка переходим по пункту «Управление правилами».



2. Активируется окошко диспетчера правил. Жмем в нем на кнопку «Создать правило».



3. В запустившемся окне производим выбор позиции «Использовать формулу». В поле «Форматировать ячейки» записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (<>). Только перед данным выражением на этот раз будет стоять знак «=». Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу F4. Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:

   =$A2<>$D2

   Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке «Формат…».



4. Активируется окно «Формат ячеек». Идем во вкладку «Заливка». Тут в перечне цветов останавливаем выбор на цвете, которым хотим окрашивать те элементы, где данные не будут совпадать. Жмем на кнопку «OK».



5. Вернувшись в окно создания правила форматирования, жмем на кнопку «OK».



6. После автоматического перемещения в окно «Диспетчера правил» щелкаем по кнопке «OK» и в нем.



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

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

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



2. Выполняем переход во вкладку под названием «Главная». Делаем щелчок по кнопке «Условное форматирование». В активировавшемся списке выбираем позицию «Правила выделения ячеек». В следующем меню делаем выбор позиции «Повторяющиеся значения».



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



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

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

Таким образом, будут выделены именно те показатели, которые не совпадают.

Урок: Условное форматирование в Экселе

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

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

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

=СЧЁТЕСЛИ(диапазон;критерий)

Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.

Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.

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



2. Происходит запуск Мастера функций. Переходим в категорию «Статистические». Находим в перечне наименование «СЧЁТЕСЛИ». После его выделения щелкаем по кнопке «OK».



3. Происходит запуск окна аргументов оператора СЧЁТЕСЛИ. Как видим, наименования полей в этом окне соответствуют названиям аргументов.

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

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

   Затем переходим к полю «Критерий», установив туда курсор. Щелкаем по первому элементу с фамилиями в первом табличном диапазоне. В данном случае оставляем ссылку относительной. После того, как она отобразилась в поле, можно щелкать по кнопке «OK».



4. В элемент листа выводится результат. Он равен числу «1». Это означает, что в перечне имен второй таблицы фамилия «Гринев В. П.», которая является первой в списке первого табличного массива, встречается один раз.



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



6. Как видим, программа произвела вычисление совпадений, сравнив каждую ячейку первой таблицы с данными, которые расположены во втором табличном диапазоне. В четырех случаях результат вышел «1», а в двух случаях – «0». То есть, программа не смогла отыскать во второй таблице два значения, которые имеются в первом табличном массиве.

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

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

1. Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ, а именно сделаем её одним из аргументов оператора ЕСЛИ. Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ. В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию».



2. Открывается окно аргументов функции ЕСЛИ. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0» без кавычек.

   После этого переходим к полю «Значение если истина». Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА. Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:

   СТРОКА(D2)

   Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK».



3. Как видим, первый результат отображается, как «ЛОЖЬ». Это означает, что значение не удовлетворяет условиям оператора ЕСЛИ. То есть, первая фамилия присутствует в обоих списках.



4. С помощью маркера заполнения, уже привычным способом копируем выражение оператора ЕСЛИ на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк.



5. Отступаем от табличной области вправо и заполняем колонку номерами по порядку, начиная от 1. Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.



6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию».



7. Открывается Мастер функций. Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ». Щелкаем по кнопке «OK».



8. Функция НАИМЕНЬШИЙ, окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений», который мы ранее преобразовали с помощью функции ЕСЛИ. Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK».



9. Оператор выводит результат – число 3. Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.



10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС. Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ. После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой (;). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию».



11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK».



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

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ. От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

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

    Жмем на кнопку «OK».



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

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

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

Урок: Как открыть Эксель в разных окнах

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

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

​Смотрите также​​manager123​ вложении. Прикрепленные файлы​ только в действительности​​ значением, введя его​​ изменении размеров таблиц.​Теперь создадим третий запрос,​​ щелкнув по ней​​ параметра. В данном​​ выберите пункт​​ заливки при помощи​​ и в контекстном​ чёрного перекрестия, как​ находятся на одном​ Если у Вас​ статье «Условное форматирование​ (А2, В2, С2,т.д.).​Есть несколько способов,​: файл для примера)​ post_1992.JPG (52.95 КБ)​ она намного больше…​
​ и нажав​​Минусы​ который будет объединять​​ правой кнопкой мыши​ случае, мы применим​
​Continue with the current​ инструмента​ меню выберите​ показано на картинке​ листе​
​ есть несколько рабочих​ в Excel». Получилось​ Получилось так.​как сравнить два столбца​Hugo​drony​drony​Ctrl+Enter​: Требует установленной надстройки​ ​ и сравнивать данных​
​ -​ ее, чтобы подтянуть​ ​ selection​
​Fill​Insert​ ниже:​Вариант В: столбцы находятся​ книг с данными​ так.​Как сравнить даты в​ в Excel на​
​: Там ещё сперва​: Моя функция имеет​: Ну во-первых, здесь​удалить все строки с​​ Power Query (в​ из предыдущих двух.​Обновить (Referesh)​ старые цены в​(Сортировать в пределах​(Цвет заливки) на​(Вставить):​Нажмите и, удерживая левую​
​ на разных листах​ (или только одна​Третий способ.​Excel.​ совпадения​ суммировать нужно по​ узкое применение, т.к.​ не обойтись без​ выделенными ячейками, используя​ Excel 2010-2013) или​ Для этого выберем​.​ новый прайс:​
​ указанного выделения) и​
​ вкладке​Дайте названия столбцам, например,​ ​ кнопку мыши, протащите​
​ или в разных​ огромная таблица), то,​Сравнить значения столбцов в​Можно сравнить даты.​,​ уникальным…​ в ней учтено,​ макроса…​ ​ команду​ ​ Excel 2016. Имена​
​ в Excel на​Плюсы​Те товары, напротив которых​ нажмите кнопку​Home​ «​ границу рамки вниз,​ книгах​ вероятно, Вы захотите​Excel формулой.​ Принцип сравнения дат​
​как сравнить две таблицы​Я сделал с​ что данные для​
​И вопрос -​Главная — Удалить -​​ столбцов в исходных​​ вкладке​: такой подход на​ получилась ошибка #Н/Д​Sort​(Главная), но преимущество​Name​ выделяя все ячейки,​Обработка найденных дубликатов​ сравнить 2 столбца,​Можно сделать в​ тот же –​ Excel​ помощью макроса UniqSummUniversal​ вычетания при совпадении​ почему в примере​ Удалить строки с​ данных не должны​Данные — Получить данные​
​ порядок быстрее работает​ — отсутствуют в​(Сортировка):​ диалогового окна​» и «​ в которые требуется​Показать только повторяющиеся строки​
​ найти повторяющиеся значения,​
​ таблице дополнительный столбец​ выделяем столбцы, нажимаем​,​и затем в​ находятся правее на​
​ в таблице №1​
​ листа (Home -​​ меняться, иначе получим​ — Объединить запросы​ ​ с большими таблицами,​​ старом списке, т.е.​Удалите столбец с формулой,​
​Format Cells​Duplicate?​ вставить формулу. Когда​ в столбце А​ а затем совершить​ и установить в​ на кнопку «Найти​списки​ один файл (4552​​ одну ячейку от​ в третьем столбце​ Delete — Delete​ ошибку «Столбец такой-то​ — Объединить (Data​ чем ВПР.​ были добавлены. Изменения​ он Вам больше​(Формат ячеек) в​» Затем откройте вкладку​ все необходимые ячейки​
​Изменить цвет или выделить​ с ними какие-либо​ ячейках этого столбца​ и выделить». Выбираем​,​ строк) подтянул с​ сравниваемых. Если нужно​ стоит примечание, ведь​ Rows)​ не найден!» при​ — Get Data​ ​Минусы​
​ цены также хорошо​ ​ не понадобится, с​
​ том, что можно​Data​ будут выделены, отпустите​ найденные дубликаты​ действия, например, удалить,​
​ формулу. =А2=В2​
​ функцию «Выделение группы​даты в Excel​ помощью ВПР() данные​ более широкое применение,​
​ «1» нет в​и т.д.​ попытке обновить запрос.​ — Merge Queries​: надо вручную копировать​ видны.​ этого момента у​ настроить одновременно все​(Данные) и нажмите​ кнопку мыши:​Удалить дубликаты из первого​ выделить цветом или​Получится так.​
​ ячеек», ставим галочку​​. Не только сравнить​​ из другого (467​
​ то нужно добавить​​ первом столбце второй​​Если списки разного размера​Типовая задача, возникающая периодически​ — Merge)​ данные друг под​Плюсы​ Вас остались только​ параметры форматирования.​Filter​Подсказка:​ столбца​
​ очистить содержимое. Столбцы​
​Можно условным форматированием окрасить​​ у слов «Отличия​ ​ столбцы в Excel,​​ строк).​ еще как минимум​ таблицы..?..​ и не отсортированы​ перед каждым пользователем​
​или нажмем кнопку​ друга и добавлять​этого способа: просто​ уникальные значения.​Теперь Вы точно не​(Фильтр):​В больших таблицах​В первой пустой ячейке​
​ могут находиться в​ слова «Ложь» другим​ по строкам». Нажимаем​ но и выделить​
​Можно наоборот -​ один параметр в​Если макрос Вас​ (элементы идут в​ Excel — сравнить​Объединить (Merge)​

excel-office.ru

Как в Excel сравнить два столбца и удалить дубликаты (выделить, раскрасить, переместить)

​ столбец с названием​ и понятно, «классика​Вот и всё, теперь​ пропустите ни одной​После этого нажмите меленькую​ скопировать формулу получится​ (в нашем примере​ одной таблице, быть​ цветом или окрасить​ «ОК».​ разницу цветом шрифта,​ от перемена мест…​

​ эту функцию.​ устроит, то можно​ разном порядке), то​ между собой два​на вкладке​ прайс-листа. Если размеры​ жанра», что называется.​ столбец​ ячейки с дубликатами:​ серую стрелку рядом​ быстрее, если использовать​ это ячейка C1)​ смежными или не​ эти ячейки.​Здесь расхождение дат в​ ячеек, т.д. Один​ сумма не меняется​Во вложении пример​ и потрудиться…​ придется идти другим​ диапазона с данными​Power Query​ таблиц изменяются, то​ Работает в любой​А​Отфильтруйте таблицу так, чтобы​

​ с «​ комбинации клавиш. Выделите​ запишем вот такую​ смежными, могут быть​Можно написать такую​​ ячейках строк второй​​ способ сравнения, разместить​ :)​​ работы этой функции.Да,​​Guest​ путем.​ и найти различия​.​ придется делать все​ версии Excel.​содержит только уникальные​ показаны были только​Duplicate?​ ячейку​ формулу:​ расположены на 2-х​

​ формулу в ячейке​​ и третьей.​ две таблицы на​Вообще пар мало​​ я учел, что​​: Если данные в​​Самое простое и быстрое​​ между ними. Способ​

​В окне объединения выберем​​ заново.​Минусы​ данные, которых нет​​ ячейки с повторяющимися​​«, чтобы раскрыть меню​​C1​​=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),»Unique»,»Duplicate»)​​ разных листах или​​ С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) Нажимаем​​Есть еще один​​ одном мониторе одновременно,​

​ — всего 196.​ если нет совпадений​ первых столбцах не​​ решение: включить цветовое​​ решения, в данном​ в выпадающих списках​Power Query — это​тоже есть. Для​ в столбце​ значениями, и выделите​ фильтра; снимите галочки​и нажмите​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));»Unique»;»Duplicate»)​ даже в разных​ «Enter». Копируем формулу​ способ​

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

• ​ наши таблицы, выделим​ бесплатная надстройка для​ поиска добавленных в​
  • ​В​ эти ячейки.​ со всех элементов​
  • ​Ctrl+C​В нашей формуле​ книгах.​ по столбцу. Тогда​
• ​сравнить даты в Excel​
  • ​ «Сравнение таблиц Excel».​ строк в примере?​
  • ​ то значение функции​ использовать.​
  • ​ условное форматирование. Выделите​ исходных данных.​

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

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

1. ​ в них столбцы​ Microsoft Excel, позволяющая​ новый прайс товаров​:​Если 2 столбца, которые​

   ​ этого списка, кроме​
​(чтобы скопировать формулу​

   

   ​A1​​Представьте, что у нас​​ в столбце с​- сравнить периоды​Здесь рассмотрим,​​manager123​​ = числу стоящему​​Арина​​ оба диапазона с​Если списки синхронизированы (отсортированы),​ с названиями товаров​ загружать в Excel​ придется делать такую​Как видите, удалить дубликаты​ Вы сравниваете, находятся​Duplicate​ в буфер обмена),​это первая ячейка​ есть 2 столбца​ разницей будут стоять​ дат,есть ли в​как сравнить столбцы в​

   ​: Суммировать то я​ левее от выбранной​​: Спасибо за советы!​​ данными и выберите​ то все делается​ и в нижней​

   ​ данные практически из​
​ же процедуру в​

   ​ из двух столбцов​​ на разных листах​​, и нажмите​​ затем нажмите​​ первого столбца, который​ с именами людей​ цифры. Единица будет​​ указанных периодах одинаковые​​ Excel​​ суммирую, не проблема).​​ ячейки.​ Данные в первых​​ на вкладке​​ весьма несложно, т.к.​ части зададим способ​ любых источников и​ обратную сторону, т.е.​ в Excel при​, то есть в​ОК​Ctrl+Shift+End​ мы собираемся сравнивать.​ – 5 имён​

2. ​ стоять, если есть​ даты и сколько​, выделить разницу цветом,​​ А пар мало​​Арина​ столбцах не повторяются;​Главная — Условное форматирование​ надо, по сути,​​ объединения -​​ трансформировать потом эти​ подтягивать с помощью​ помощи формул –​ разных таблицах, кликните​​.​​(чтобы выделить все​$B$1​ в столбце​ различия, а «нуль»​​ дней в периодах​ символами, т.д.​ потому файл большой​: Спасибо, Drony, со​ в идеале, если​ — Правила выделения​ сравнить значения в​Полное внешнее (Full Outer)​ данные любым желаемым​

   

​ ВПР новые цены​​ это не так​ правой кнопкой мыши​Вот и всё, теперь​ не пустые ячейки​и​​A​​ — данные в​​ совпадают. Этот способ​​Например, несколько магазинов​ был и я​ стилем ссылок проблем​​ во второй таблице​​ ячеек — Повторяющиеся​ соседних ячейках каждой​:​ образом. В Excel​​ к старому прайсу.​​ уж сложно.​ выделенный диапазон и​ Вы видите только​

1. ​ в столбе С)​$B$10000​и 3 имени​​ ячейках одинаковые. Получится​​ может пригодиться, чтобы​

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

1. ​ сдали отчет по​ удалила часть данных​ бы не возникло​​ в первом столбце​​ значения (Home -​ строки. Как самый​После нажатия на​

   ​ 2016 эта надстройка​
​ Если размеры таблиц​

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

2. ​ нет совпадений с​ Conditional formatting -​​ простой вариант -​​ОК​ уже встроена по​
3. ​ завтра поменяются, то​ командой сайта office-guru.ru​

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

​ выберите​А​Ctrl+V​ и последней ячеек​B​Четвертый​ в периодах. Например,​ сравнить эти отчеты​ часть из стало.​ макрос очень помог!​ первым столбцом первой​ Highlight cell rules​

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

​ используем формулу для​должна появиться таблица​ умолчанию на вкладке​ придется корректировать формулы.​Источник: https://www.ablebits.com/office-addins-blog/2013/09/05/compare-two-columns-remove-duplicates/​Delete Row​, которые дублируются в​(чтобы вставить формулу​ второго столбца, с​. Необходимо сравнить имена​

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

​ таблицы, то в​ — Duplicate Values)​​ сравнения значений, выдающую​​ из трех столбцов,​​Данные (Data),​​ Ну, и на​​Перевел: Антон Андронов​​(Удалить строку):​​ столбце​​ во все выделенные​

​ которым будем выполнять​ в этих двух​пособ.​​ не было два​​У нас такая​ так мало. Вот​ понавороченней (выгрузка из​ третьем столбце напротив​​:​​ на выходе логические​​ где в третьем​​а для Excel​

​ действительно больших таблицах​Автор: Антон Андронов​Нажмите​​В​​ ячейки).​ сравнение. Обратите внимание​​ столбцах и найти​​Можно​ сотрудника сразу или​ таблица с данными​ только я пользователь​ 1С оборотной ведомости),​ единицы должно стоять​

​Если выбрать опцию​ значения​​ столбце нужно развернуть​​ 2010-2013 ее нужно​ (>100 тыс. строк)​​Имеем две таблицы (например,​​ОК​. В нашей учебной​Отлично, теперь все повторяющиеся​ на абсолютные ссылки​​ повторяющиеся. Как Вы​​объединить таблицы Excel​ даты учебы, оплаты​ из магазинов. Как​ еще тот)), вы​​ буду их приводить​​ 2, т.к. разница​​Повторяющиеся​​ИСТИНА (TRUE)​ содержимое вложенных таблиц​​ отдельно скачать с​​ все это счастье​ старая и новая​, когда Excel попросит​

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

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

​ между 2 и​, то Excel выделит​или​ с помощью двойной​ сайта Microsoft и​​ будет прилично тормозить.​​ версия прайс-листа), которые​ Вас подтвердить, что​​ всего две, но,​​ «​ и номерам строки​ данные, взятые исключительно​ все одинаковые данные​ пересекались. Об этом​ разных таблиц в​ мне пошагово объяснить​ а затем использовать​ 0 равна 2.​​ цветом совпадения в​​ЛОЖЬ (FALSE)​ стрелки в шапке:​​ установить — получите​​Скопируем наши таблицы одна​ надо сравнить и​​ Вы действительно хотите​​ как Вы понимаете,​Duplicate​ предшествует знак доллара​ для примера. В​

​ соберутся в одну​ способе читайте в​ одну, читайте в​

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

​ как это сделать)​ макрос.​ Но это я​ наших списках, если​:​

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

​ статье «Как связать​​ пжалста))​​drony​ хотела пока оставить;​ опцию​Число несовпадений можно посчитать​ данных из обеих​Power Query​ столбец с названием​С ходу видно, что​ листа и после​

​ встретится намного больше.​В первой ячейке первого​​ абсолютные ссылки для​ имеем дело с​ будет посмотреть и​ даты в Excel».​ таблицы в Excel»​P.S. То что​: Приятно осознавать, что​ если подскажете, что​Уникальные​ формулой:​ таблиц:​.​ прайс-листа, чтобы потом​ в новом прайсе​ этого очистите фильтр.​Чтобы снова отобразить все​​ пустого столбца на​​ того, чтобы адреса​

1. ​ тысячами, а то​ отдельно данные по​Как посчитать разницу​ тут.​ строк в примере​ мой труд оказался​ делать, возможно, я​- различия.​​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​​Названия столбцов в шапке​
2. ​Перед загрузкой наших прайс-листов​
3. ​ можно было понять​ что-то добавилось (финики,​​ Как видите, остались​​ строки столбца​​ листе​​ ячеек оставались неизменными​ и с десятками​
4. ​ магазинам. Как это​​ дат, стаж, возраст,​​Первый способ.​​ столько , просто​​ для тебя полезным.​ бы доработала формулу​Цветовое выделение, однако, не​или в английском варианте​​ лучше, конечно, переименовать​ в Power Query​​ из какого списка​ честнок…), что-то пропало​ только строки с​​А​​Sheet2​
5. ​ при копировании формул.​ тысяч записей.​ сделать, смотрите в​ как прибавить к​Как сравнить два столбца​ … без причины)​
6. ​:)​ сама…​​ всегда удобно, особенно​​ =SUMPRODUCT(—(A2:A20<>B2:B20))​ двойным щелчком на​ их необходимо преобразовать​​ какая строка:​​ (ежевика, малина…), у​

​ уникальными значениями:​, кликните символ фильтра​(в нашем случае​Если Вы хотите найти​Вариант А:​ статье «Как объединить​

​ дате число, т.д.,​ в​
​Hugo​
​manager123​

​Guest​

office-guru.ru

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

​ для больших таблиц.​Если в результате получаем​ более понятные:​ сначала в умные​Теперь на основе созданной​

​ каких-то товаров изменилась​Если 2 столбца расположены​ в столбце​ это столбец В)​ дубликаты в столбце​оба столбца находятся​ таблицы в Excel».​ смотрите в статье​Excel на совпадения.​: Зачем Вам два​

​: Добрый день, помогите,​: ну как нибудь​ Также, если внутри​ ноль — списки​А теперь самое интересное.​ таблицы. Для этого​ таблицы создадим сводную​

• ​ цена (инжир, дыня…).​​ на одном листе​​В​ введите такую формулу:​B​ на одном листе.​Пятый способ.​ «Дата в Excel.​Выделяем столбцы (у​
• ​ одинаковых столбца с​ пожалуйста, решить задачу.​ так​ самих списков элементы​ идентичны. В противном​ Идем на вкладку​
• ​ выделим диапазон с​ через​

​ Нужно быстро найти​, вплотную друг другу​

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

​, который теперь выглядит​=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),»»,»Duplicate»)​, поменяйте ссылки, чтобы​ Например, столбец​Используем​ Формула» здесь.​ нас столбцы А​ номерами? Хватит одного​ И можно ли​

​=B4-СУММЕСЛИ($H$4:$H$16;A4;$I$4:$I$16) (для ячейки​ могут повторяться, то​ случае — в​Добавить столбец (Add Column)​ данными и нажмем​Вставка — Сводная таблица​ и вывести все​ (смежные) или не​ как воронка с​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));»»;»Duplicate»)​

​ формула приняла такой​A​функцию «СЧЕТЕСЛИ» в​Можно сравнить числа.​ и В). На​ с ID/номерами и​ ее вообще воплотить​

​ С4)​​ этот способ не​ них есть различия.​и жмем на​ на клавиатуре сочетание​ (Insert — Pivot​

​ эти изменения.​​ вплотную друг к​ маленькой стрелочкой и​Здесь​ вид:​и столбец​Excel​Функцию выделения ячеек можно​ закладке «Главная» нажимаем​ двух с количеством.​ в Excel?​drony​ подойдет.​ Формулу надо вводить​ кнопку​ Ctrl+T или выберем​ Table)​Для любой задачи в​

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

​ другу (не смежные),​ выберите​Sheet3​=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),»Unique»,»Duplicate»)​B​. Эта функция посчитает​ вызвать клавишей F5.​

​ на кнопку функции​Т.е. когда уже​Есть две таблицы​​: Вот пользовательскаЯ функция​В качестве альтернативы можно​ как формулу массива,​​Условный столбец (Conditional Column)​​ на ленте вкладку​​. Закинем поле​ Excel почти всегда​​ то процесс удаления​​Select all​– это название​​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));»Unique»;»Duplicate»)​​.​​ количество повторов данных​

​ В появившемся окне​ «Найти и выделить»,​ в двух файлах​ (было и стало)в​ :​ использовать функцию​ т.е. после ввода​. А затем в​Главная — Форматировать как​Товар​ есть больше одного​ дубликатов будет чуть​(Выделить все). Либо​ листа, на котором​

​Вместо «​Вариант В:​ их первого столбца,​ «Переход» нажимаем кнопку​ выбираем функцию «Выделение​​ только уникальные с​ каждой есть название​Function DRONY(Cells, Diapason,​СЧЁТЕСЛИ​ формулы в ячейку​​ открывшемся окне вводим​

​ таблицу (Home -​в область строк,​ решения (обычно 4-5).​ сложнее. Мы не​ Вы можете сделать​ расположен 2-ой столбец,​Unique​​Столбцы расположены на​​ сравнив их с​

​ «Выделить…».​​ группы ячеек».​ суммами, всё просто​ товара и его​ Dlyna)​

​(COUNTIF)​​ жать не на​ несколько условий проверки​ Format as Table)​ поле​ Для нашей проблемы​ можем удалить всю​ то же самое​ а​

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

​» и «​ разных листах. Например,​ данными второго столбца.​Второй способ.​В появившемся окне ставим​ — в любом​ количество. Мне нужно​For a =​из категории​Enter​ с соответствующими им​. Имена созданных таблиц​​Прайс​​ можно использовать много​ строку с повторяющимися​ через Ленту, нажав​$A$1:$A$10000​Duplicate​ столбец​​В дополнительном столбце​​Можно в​

​ галочку у слов​ файле в столбце​ сравнить данные по​ 1 To Dlyna​Статистические​, а на​ значениями на выходе:​ можно подкорректировать на​в область столбцов​ разных подходов:​​ значениями, поскольку так​Data​– это адреса​​» Вы можете записать​A​ устанавливаем формулы, они​​Excel сравнить и выделить​​ «Отличия по строкам».​​ «C» рядом с​​ двум таблицам, видеть​​If Cells =​​, которая подсчитывает сколько​

​Ctrl+Shift+Enter​Останется нажать на​ вкладке​​ и поле​​функцию​​ мы удалим ячейки​​(Данные) >​​ ячеек от 1-ой​​ собственные метки, например,​на листе​ разные с разными​ цветом​ Нажимаем «ОК».​​ первым количеством пишите​ в одной строке​ Diapason.Cells(a) Then DRONY​ раз каждый элемент​.​​ОК​

​Конструктор​Ц​​ВПР (VLOOKUP)​ и из второго​​Select & Filter​

​ до последней в​ «​

​Sheet2​ условиями. Или в​ячейки с разными данными​В таблице выделились все​ «=ВПР(» и жмёте​ сколько было данного​ = Cells.Offset(0, 1)​​ из второго списка​Если с отличающимися ячейками​и выгрузить получившийся​(я оставлю стандартные​ена​- искать названия​​ столбца тоже. Итак,​​(Сортировка и фильтр)​​ этом 2-ом столбце.​​Не найдено​​и столбец​

​ формуле указываем конкретные​с помощью условного​ ячейки с разными​ на значёк fx​ товара и сколько​ — Diapason.Offset(0, 1).Cells(a)​ встречался в первом:​ надо что сделать,​​ отчет в Excel​​Таблица1​

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

​ данными так. Excel​ в строке формул​ его стало, но​

​ Else DRONY =​Полученный в результате ноль​ то подойдет другой​ с помощью все​

​и​Как видите, сводная таблица​​ прайс-листа в старом​​ уникальные записи в​Clear​​ ячейки столбца​​Найдено​на листе​ нужно посчитать в​Итак, мы выделяем​ сравнила данные в​

​ — т.е. вызываете​​ название товара в​​ 0​ и говорит об​ быстрый способ: выделите​ той же кнопки​​Таблица2​ автоматически сформирует общий​​ и выводить старую​​ столбце​​(Очистить), как показано​

​B​

​«, или оставить только​Sheet3​ столбце.​ столбцы с данными​ ячейках двух столбцов​ мастера формул (в​ таблицах расположены хаотически​If Cells =​ отличиях.​ оба столбца и​​Закрыть и загрузить (Close​​, которые получаются по-умолчанию).​​ список всех товаров​​ цену рядом с​

​А​​ на снимке экрана​(как и в​ «​.​Подробнее смотрите такие​ (без названия столбцов).​ — данные ячейки​

​ англ. версии VLOOKUP()).​​ и некоторые наименования​ Diapason.Cells(a) Then Exit​И, наконец, «высший пилотаж»​ нажмите клавишу​ & Load)​Загрузите старый прайс в​ из старого и​ новой, а потом​, сделайте следующее:​ ниже:​

planetaexcel.ru

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

​ варианте А).​Duplicate​В Excel 2013, 2010​ формулы в статье​ На закладке «Главная»​ А2 и данными​Далее указываете что​ имеющиеся в одной​ Function​ — можно вывести​

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

​F5​на вкладке​ Power Query с​ нового прайс-листов (без​ ловить отличия​Отфильтруйте таблицу так, чтобы​Если пометки «​У нас получается вот​» и ввести символ​ и 2007 есть​ «Функция «СЧЕТЕСЛИ» в​ в разделе «Стили»​​ ячейки В2, т.д.​​ искать, где искать​​ таблице могут не​​Next a​

​ отличия отдельным списком.​, затем в открывшемся​

​Главная (Home)​

​ помощью кнопки​ повторений!) и отсортирует​

​объединить два списка в​ отображались только дублирующиеся​Duplicate​ такой результат:​ пробела вместо второго​ встроенный инструмент​ Excel».​ нажимаем на кнопку​Если мы сейчас нажмём​ (выделяете два столбца​​ встречаться в другой.​​End Function​​ Для этого придется​​ окне кнопку​

​:​Из таблицы/диапазона (From Table/Range)​ продукты по алфавиту.​ один и построить​ значения, и выделите​» не достаточно для​​Отлично, мы нашли записи​​ значения. В последнем​Remove Duplicate​​Этот способ сравнения​​ «Условное форматирование». Из​​ на мышь, то​ в другом файле​​ То есть:​Синтаксис : =Drony(A11;$F$2:$F$11;10)​ использовать формулу массива:​​Выделить (Special)​Красота.​с вкладки​ Хорошо видно добавленные​​ по нему потом​​ эти ячейки. Кликните​

​ Ваших целей, и​ в первом столбце,​ случае ячейки, для​(Удалить дубликаты), но​

• ​ можно применить при​ появившегося списка выбираем​
• ​ выделения ячеек исчезнут.​​ целиком или ограниченный​
• ​если шк-было и​=Drony(«ячейка»;»диапазон»;»длина диапазона»)​Выглядит страшновато, но свою​​-​
• ​Причем, если в будущем​Данные (Data)​ товары (у них​​ сводную таблицу, где​ по ним правой​ Вы хотите отметить​ которые также присутствуют​ которых дубликаты найдены​
• ​ он бессилен в​

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

​ сравнении двух прайсов.​ функцию «Создать правило».​ Чтобы эти выделенные​ диапазон), из какого​ шк-стало есть в​ячейка — то,​

​ работу выполняет отлично​Отличия по строкам (Row​ в прайс-листах произойдут​или с вкладки​ нет старой цены),​ наглядно будут видны​ кнопкой мыши и​​ повторяющиеся ячейки другим​ во втором столбце.​ не будут, останутся​ такой ситуации, поскольку​ Смотрите статью «Как​В строке «Формат…» пишем​ ячеек оставить, мы​​ столбца брать данные​

​ обеих таблицах, то​​ что нужно проверить.​​ ;)​ differences)​ любые изменения (добавятся​Power Query​​ удаленные товары (у​​ отличия​

​ в контекстном меню​ цветом шрифта, заливки​ Теперь нам нужно​ пустыми, и, я​ не может сравнивать​ сделать прайс-лист в​ такую формулу. =$А2<>$В2.​ можем закрасить эти​

​ (2), последний параметр​ в итоговой таблице​​диапазон — то,​ ​Арина​​. В последних версиях​​ или удалятся строки,​​(в зависимости от​ них нет новой​использовать надстройку Power Query​ выберите​

​ или каким-либо другим​ что-то с ними​ полагаю, такое представление​

​ данные в 2​ Excel».​ Этой формулой мы​ ячейки или изменить​ укажите 0.​

​ это выглядит как:​ где нужно проверить.​: Здравствуйте, подскажите, пожалуйста,​

planetaexcel.ru

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

​ Excel 2007/2010 можно​​ изменятся цены и​ версии Excel). После​ цены) и изменения​
​ для Excel​Clear contents​ способом…​ делать. Просматривать все​ данных наиболее удобно​ столбцах. Более того,​Чтение этой статьи займёт​ говорим Excel, что​ цвет шрифта в​Затем даблкликом «протягиваете»​ шк-было/кол-во-было/шк-стало/кол-во-стало;​длина диапазона -количество​ как можно решить​ также воспользоваться кнопкой​ т.д.), то достаточно​ загрузки вернемся обратно​

​ будет лишь обновить​​ в Excel из​Общие итоги в такой​ последовательно.​Очистите фильтр.​

​ дубликаты, как показано​​ таблице вручную довольно​Теперь давайте скопируем нашу​ удалить дубликаты. Других​ 10 минут. В​ ячейках столбца А​ раздела «Шрифт» на​ Сохраняете этот файл​ встречается в шк-стало,​ диапазоне.​Есть 2 таблицы,​ & Select) -​ наши запросы сочетанием​ Power Query командой​ таблице смысла не​Если вы совсем не​Выделите все ячейки в​ выше, выделите все​ неэффективно и занимает​ формулу во все​ вариантов, таких как​

​ следующие 5 минут​​ не равны данным​ закладке «Главная», пока​
​ под именем «Итог»​ то в итоговой​

​C​​ цвета, не предусмотрено.​ сравнить два столбца​

​ В, то окрасить​​ Например, так.​P.S. Если раздражают/мешают​ шк-стало ячейка должна​ попробовать…​ таблицы А с​на вкладке​Обновить все (Refresh All)​ в… (Close &​ вкладке​ загляните сначала сюда​, начиная с ячейки​

​Ctrl+1​​Если Ваши столбцы не​, вплоть до самой​ И точка!​ в Excel и​ эти ячейки в​Или так.​ возможные «н/д» в​ оставаться пустой или​drony​ данными первого столбца​Главная (Home)​на вкладке​ Load — Close​
​Конструктор — Общие итоги​ и почитайте или​А1​, чтобы открыть диалоговое​ имеют заголовков, то​ нижней строки, которая​Далее я покажу Вам​ узнать о наличии​ красный свет.​

​Сравнить данные в нескольких​​ ячейках без пары​ с знаком 0​: В приведенном примере​ таблицы Б. Если​Excel выделит ячейки, отличающиеся​
​Данные (Data)​ & Load To…)​ — Отключить для​ посмотрите видеоурок по​вплоть до самой​ окно​ их необходимо добавить.​

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

planetaexcel.ru

Сравнение данных двух таблиц (упорядочить данные)

​ обозначающее первую строку,​​. Для этого наведите​

​ таблице, предварительно выделив​​manager123​Исходные данные и​ пересохранить ее к​ значения посчитать разницу​ еще визуально отформатировать​ из всех. Шустро​ Only)​Если изменятся цены (но​ для подтягивания данных​Sort A to Z​ ячеек в строках​ превратится в чёрную​ правый нижний угол​Сравниваем 2 столбца и​ мощное и действительно​ другие условия для​
​ все три столбца​: Hugo, спасибо, большое​ что должно получиться​ стиль А1 (обычный).​

​ можете изменить цвет​​Кликните правой кнопкой мыши​, указатель примет форму​Вариант А: оба столбца​

planetaexcel.ru

​ больших массивов данных.​