Сравнить листы в excel на совпадения - сравнение двух таблиц в excel

Сравните два листа (бок о бок) в одной книге Excel

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

Но вы все равно можете провести такое же параллельное сравнение.

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

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

Ниже приведены шаги для сравнения двух листов в Excel:

Откройте книгу, в которой есть листы, которые вы хотите сравнить.
Перейдите на вкладку «Просмотр».
В группе «Окно» нажмите «Новое окно». Это открывает второй экземпляр той же книги.
На вкладке «Просмотр» нажмите «Упорядочить все». Откроется диалоговое окно «Упорядочить окна».
Выберите «По вертикали», чтобы сравнить данные в столбцах (или выберите «По горизонтали», если вы хотите сравнить данные в строках).
Щелкните ОК.

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

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

Как это работает?

Когда вы нажимаете «Новое окно», он снова открывает ту же книгу с немного другим именем. Например, если ваша книга называется «Тест», и вы нажимаете «Новое окно», она назовет уже открытую книгу «Тест — 1», а второй экземпляр — «Тест — 2».

Обратите внимание, что это все та же книга. Если вы внесете какие-либо изменения в любую из этих книг, это отразится на обеих

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

Вы также можете включить синхронную прокрутку, если хотите (нажав на опцию «Синхронная прокрутка» на вкладке «Просмотр»).

Выборка значений из таблицы по условию в Excel без ВПР

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

Поскольку товар имеет фиксированную стоимость, для определения самого продаваемого смартфона можно использовать встроенную функцию МОДА. Чтобы найти наименование наиболее продаваемого товара используем следующую запись:

Для определения общей прибыли от продаж iPhone 5s используем следующую запись:

Функция СУММПРИЗВ используется для расчета произведений каждого из элементов массивов, переданных в качестве первого и второго аргументов соответственно. Каждый раз, когда функция СОВПАД находит точное совпадение, значение ИСТИНА будет прямо преобразовано в число 1 (благодаря двойному отрицанию «–») с последующим умножением на значение из смежного столбца (стоимость).

Сравнение таблиц с помощью приложения Compare Sheets

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

Преимущество приложения Compare Sheets заключается в его простоте использования. Вам не нужно знать специальные команды или формулы, чтобы выполнить сравнение. Просто откройте обе таблицы, выберите нужные опции и нажмите на кнопку “Сравнить”.

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

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

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

В решении каких задач поможет надстройка «Сопоставить столбцы»

Надстройка построчно сканирует ячейки и вычисляет процент одинаковых значений в столбцах. XLTools «Сопоставить столбцы» не подходит для обычного сравнения значений в ячейках — она не предназначена для поиска дубликатов или уникальных значений.

Надстройка «Сопоставить столбцы» имеет другое назначение. Её главная задача — выяснить, насколько, в целом, наборы данных (столбцы) схожи или отличны. Надстройка помогает с анализом большого объёма данных, когда вам нужно посмотреть шире, на макро-уровне, напр. ответить на такие вопросы:

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

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

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

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

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

Первый список — А2:В31 , этот диапазон называется OldList . Второй список — D2:E31 , диапазон называется NewList . Диапазоны были названы с помощью команды Формулы Определенные имена Присвоить имя . Давать названия диапазонам необязательно, но это облегчает работу с ними.

Начнем с добавления условного форматирования к старому списку.

Выделите ячейки диапазона OldList .
Выберите .
В окне Создание правила форматирования выберите пункт под названием Использовать формулу
Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
Нажмите кнопку ОК.

Ячейки в диапазоне NewList используют подобную формулу условного форматирования.

Выделите ячейки диапазона NewList .
Выберите Главная Условное форматирование Создать правило для открытия диалогового окна Создание правила форматирования .
В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
Нажмите кнопку ОК.

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

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

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

«Столбец 1»«Условное форматирование»«Создать правило…»«=СЧЁТЕСЛИ($C$3:$D$12;A3)=0»

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

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

Два

При рассмотрении вопроса, как сравнить два столбца в Excel на совпадения / отличия, нужно сравнить информацию в каждой отдельной строчке на отличия и одинаковые параметры. Сделать такой шаг можно с помощью «ЕСЛИ». Формула вставляется в каждую строчку в соседнем столбике около таблицы Эксель, где размещены основные параметры. После создания записи для 1-й строки ее можно протянуть и копировать на другие строчки.

Если вас интересует, как сравнить столбцы в Excel на совпадения, используйте запись с соответствующей командой — =ЕСЛИ(A2=B2; “Совпадают”; “”). Бывают ситуации, когда необходимо сравнить два столбика и найти отличия. В таком случае используйте иную запись — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). По желанию можно выполнить проверку на совпадения / отличия между двумя секциями с помощью одной формулы. Для этого используется один из следующих вариантов:

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”);
=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”).

При этом в таблице выводится информация о наличии совпадений или отличий.

Если стоит задача в Экселе сравнить столбцы с учетом регистра, применяется другая запись. Используйте — =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Альтернативный вариант

Существует еще один способ, как в Эксель сравнить два столбца на совпадения. Задача в том, чтобы определить повторяющиеся параметры в обоих столбцах. Здесь можно использовать упомянутую ранее функцию ЕСЛИ или СЧЕТЕСЛИ. Формула имеет следующий вид =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”). После ввода формулы производится проверка в строчке «В» на факт совпадений с данными в строке «А». При наличии фиксированного количества строк в Эксель можно указать определенный диапазон, к примеру, $B2:$B20.

Больше двух

По-иному обстоит ситуация, если нужно сравнить в столбцы в Excel, когда их больше двух. Программа позволяет сравнивать данные в нескольких столбиках по ряду критериев: находить строчки с одинаковыми значениями во всех или в двух столбцах. Если их больше двух, используйте функции ЕСЛИ и И. При этом сама формула в Эксель приобретает следующий вид — =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “). Как только программе удалось сравнить данные, в последней строке выводится информация о совпадении.

Если столбцов в Эксель более двух, рекомендуется использовать опцию СЧЕТЕСЛИ и ЕСЛИ. При этом сама команда приобретает следующий вид — =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “).

Поиск совпадений в двух и более столбцах

Бывают ситуации, когда в Эксель необходимо сравнить несколько столбцов, но найти совпадения хотя бы в двух из них. В таком случае применяются опции ИЛИ и ЕСЛИ. Для решения задачи делается следующая запись в специальной графе =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “).

В случае, когда в таблице много больше двух столбцов, формула может быть слишком большой, ведь в ней нужно указывать параметры совпадения для каждой вертикальной секции таблицы. Чтобы оптимизировать процесс, нужно использовать другую функцию СЧЕТЕСЛИ. При этом полная запись будет иметь следующий вид: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”).

В этой формуле условно выделяется две части. В первой СЧЕТЕСЛИ позволяет рассчитать число столбцов в строке с параметром А2 в ячейке, а вторая вычисляет это количество в таблице с параметром из В2. При равенстве результата «0» можно говорить, что в каждой ячейке столбца у этой сроки находятся уникальные параметры. При этом формула для Эксель выдает результат «Уникальная строка», а при их отсутствии «Не уникальная …».

Как сопоставить данные в Excel?

Выделить столбцы с данными, в которых нужно вычислить совпадения;
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск значений в списке данных

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

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

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

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

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

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

Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.

Сравните 2 столбца и верните значение из третьего

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

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

=ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ)

Чтобы скрыть ошибки #Н/Д, используйте проверенное решение — функцию IFNA:

=ЕСЛИНА(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), “”)

Вместо пробелов вы можете вернуть любой текст для отсутствующих точек данных — просто введите его в последнем аргументе. Например:

=IFNA(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), “Недоступно”)

Помимо ВПР, эту задачу можно решить с помощью нескольких других функций поиска.

Лично я бы полагался на более гибкую формулу ИНДЕКС ПОИСКПОЗ:

=ЕСЛИНА(ИНДЕКС($E$3:$E$10, ПОИСКПОЗ(A3, $D$3:$D$10, 0)), “”)

Или используйте современный преемник ВПР — функцию XLOOKUP, доступную в Excel 365 и Excel 2021:

=XLOOKUP(A3, $D$3:$D$10, $E$3:$E$10, “”)

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

=ФИЛЬТР(A3:B15, B3:B15″”)

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

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

Выберите столбцы для сравнения.
Напр., столбцы с данными прототипов.
Нажмите кнопку «Сопоставить столбцы» на панели XLTools.
Выберите «Сопоставить с диапазоном эталонных столбцов» > Выберите столбцы эталонных значений.
Напр., столбцы со стандартами.
Отметьте «Столбцы содержат заголовки», если это так.
Отметьте «Показывать процент соответствия», чтобы степень соответствия отображалась в процентах.
В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).
Укажите, куда следует поместить результат: на новый или на существующий лист.
Нажмите ОК > Готово, результат представлен в сводной таблице.

Совет:
чтобы было проще интерпретировать результат, примените к нему условное форматирование:
Выберите сводную таблицу результата > Кликните по пиктограмме Экспресс-анализа > Примените «Цветовую шкалу».

Прочтение результата: прототип Тип 2 на 99% соответствует Стандарту 2, т.е. 99% их параметров в строках совпадают. Продукт 5 ближе всего к Стандарту 3 — 96% их параметров идентичны. В то же время Продукт 4 далёк от соответствия какому-либо из трёх стандартов. Теперь можно сделать вывод, насколько каждый из прототипов отклоняется от целевых эталонных значений.

Используйте Excel для сравнения двух листов

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

Сравните таблицы Excel с помощью формул

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

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

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

= IF (Sheet1! A2 <> Sheet2! A2, «Sheet1:» & Sheet1! A2 & «And Sheet2:» & Sheet2! A2, «Без разницы»)

Вы можете изменить эту формулу, чтобы она начиналась с любой ячейки, которая вам нравится. Если данные на вашем листе начинаются с строки B и столбца 3, вы должны изменить формулу, чтобы использовать B3 вместо A2.
Эта формула сравнивает ячейку из Sheet1 с той же ячейкой в Sheet2. Если ячейки совпадают, отобразится лист результатов. Нет разницы. Если они разные, в ячейке будут разные значения для каждого листа.
Щелкните угол ячейки и перетащите указатель мыши по листу к последнему столбцу, в котором есть данные из других сравниваемых листов. Это заполнит формулу сравнения до последнего столбца и автоматически откорректирует ссылки на ячейки.
Выделив ту же строку, щелкните угол последней ячейки и перетащите указатель мыши по листу к последней строке, содержащей данные из других сравниваемых листов. Это заполнит формулу сравнения до последней строки и автоматически откорректирует ссылки на ячейки.
Прокручивая лист, вы увидите, что все ячейки, которые различаются между Sheet1 и Sheet2, отображаются со значениями из каждого листа. Просто пролистайте, чтобы определить все различия.
Использование формул — один из самых простых способов сравнения отдельных листов, поскольку вам не нужно каким-либо образом изменять исходные листы.

Сравните таблицы Excel с условным форматированием

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

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

Вы можете использовать условное форматирование, чтобы выявить различия.

Сначала выделите все ячейки на листе результатов. Выберите Главная меню, затем выберите Условное форматирование из Стили группа и выберите Новое правило.
в Новое правило форматирования в появившемся окне выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать.. В Формат значений, где эта формула истинна поле вставьте следующую формулу.

= Лист1! A2 <> Лист2! A2

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

Применение функции ВПР на практике

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

Порядок действий в данном случае следующий:

Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
Теперь предстоит правильно заполнить аргументы функции:
в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в котором будет выполняться поиск искомого значения

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

Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
в значении аргумента “Интервальный_просмотр” можно указать два значения:
ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
мы выбираем первый вариант, так как нам важна предельная точность

Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
в значении аргумента “Интервальный_просмотр” можно указать два значения:
ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
мы выбираем первый вариант, так как нам важна предельная точность.

Когда все готово, нажимаем OK.

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

Метод 2: удаление повторений при помощи “умной таблицы”

Еще один способ удаления повторяющихся строк – использование “умной таблицы“. Давайте рассмотрим алгоритм пошагово.

Для начала, нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
Во вкладке “Главная” находим кнопку “Форматировать как таблицу” (раздел инструментов “Стили“). Кликаем на стрелку вниз справа от названия кнопки и выбираем понравившуюся цветовую схему таблицы.
После выбора стиля откроется окно настроек, в котором указывается диапазон для создания “умной таблицы“. Так как ячейки были выделены заранее, то следует просто убедиться, что в окошке указаны верные данные. Если это не так, то вносим исправления, проверяем, чтобы пункт “Таблица с заголовками” был отмечен галочкой и нажимаем ОК. На этом процесс создания “умной таблицы” завершен.
Далее приступаем к основной задаче – нахождению задвоенных строк в таблице. Для этого:
- ставим курсор на произвольную ячейку таблицы;
- переключаемся во вкладку “Конструктор” (если после создания “умной таблицы” переход не был осуществлен автоматически);
- в разделе “Инструменты” жмем кнопку “Удалить дубликаты“.
Следующие шаги полностью совпадают с описанными в методе выше действиями по удалению дублированных строк.

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

Как сравнить два столбца и более друг с другом и вычислить процент соответствия

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

Нажмите кнопку «Сопоставить столбцы» на панели XLTools > Выберите «Сопоставить столбцы между собой».
Нажмите ОК >

Совет:

Выберите сводную таблицу результата > Кликните по пиктограмме Экспресс-анализа > Примените «Цветовую шкалу».

Прочтение результата: прототипы Тип 1 и Тип 3 практически идентичны, показатель соответствия на 99% говорит о том, что 99% их параметров в строках совпадают. Тип 2 и Тип 4 схожи менее всего — их параметры совпадают только на 30%.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
Очистите фильтр.
Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

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

Итог

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

При сравнении нескольких сопоставимых объектов в Excel таблицах, данные часто организуют по столбцам, чтобы было удобно сравнивать характеристики этих объектов построчно. Например, модели автомобилей, телефоны, экспериментальные и контрольные группы, ряд магазинов торговой сети и др. При большом числе строк визуальный анализ не может быть достоверным. Функции ВПР, ИНДЕКС, ПОИСКПОЗ (VLOOKUP, INDEX, MATCH) удобны для сравнения данных по ячейкам и не дают общей картины. А как выяснить, насколько в целом столбцы схожи между собой? Идентичны ли столбцы?

Надстройка «Сопоставить столбцы» позволяет сопоставить столбцы и увидеть общую картину:

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

Язык видео: английский. Субтитры: русский, английский

(Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)