Excel Поиск Значений на Разных Листах Книги • Дата и время

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

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

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

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

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

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

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

Поиск различий в двух столбцах Excel

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Для создания списка, содержащего найденные значения, воспользуемся формулой массива ИНДЕКС Список;НАИМЕНЬШИЙ ЕСЛИ ЕСЛИОШИБКА ПОИСК G 6;Список ;0 1;СТРОКА Список -СТРОКА A 9 ;30 ; СТРОКА ДВССЫЛ A1 A ЧСТРОК Список. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
СОВЕТ: О поиске текстовых значений с использованием подстановочных знаков читайте в статье Поиск текстовых значений в списках. Часть2. Подстановочные знаки . В статье Выделение ячеек c ТЕКСТом с применением Условного форматирования приведено решение аналогичной задачи с использованием Условного форматирования .
Несовпавшие данные в Microsoft Excel

Поиск ТЕКСТовых значений в EXCEL с выводом их в отдельный список. Часть1. Обычный поиск. Примеры и описание

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

Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:

Задача

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

Excel Поиск Значений на Разных Листах Книги • Дата и время

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

Выведем в отдельный диапазон все значения Исходного списка, в которых содержится текст-критерий (например, слово дрель ). Критерий вводится в ячейку С6 .

Excel Поиск Значений на Разных Листах Книги • Дата и время

Для создания списка, содержащего найденные значения, воспользуемся формулой массива :

=ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))

Алгоритм работы формулы следующий (для просмотра промежуточных шагов работы формулы воспользуйтесь клавишей F9 ):

  • Функция ПОИСК() , перебирая элементы исходного списка, определяет, содержится ли в нем значение-критерий. Если значение не содержится, то возвращается ошибка #ЗНАЧ! В противном случае возвращается числовое значение, соответствующее номеру начальной позиции вхождения критерия в значение из списка (здесь нам не важен номер позиции, важно, что это число);
  • Функция ЕСЛИОШИБКА() используется для подавления ошибки #ЗНАЧ! заменяя ее на число 0;
  • Функция ЕСЛИ() заменяет числовые значения, возвращенные функцией ПОИСК() , на номер позиции значения в списке. Если значение =0 (соответствует ошибке #ЗНАЧ!), то возвращается число 30. В принципе, вместо 30 можно указать любое число, которое больше номера последней заполненной позиции Исходного списка (это нужно для правильной сортировки функцией НАИМЕНЬШИЙ() );
  • Функция НАИМЕНЬШИЙ() сортирует массив номеров строк по возрастанию;
  • Функция ДВССЫЛ() возвращает массив последовательных чисел ;
  • Функция ИНДЕКС() возвращает текстовые значения из Исходного списка, из строк, номера которых были получены на предыдущем шаге.

Для скрытия ошибок #ССЫЛКА!, возвращаемой формулой массива, к диапазону С10:С19 применено правило Условного форматирования .

Excel Поиск Значений на Разных Листах Книги • Дата и время

Как сравнить два столбца в Excel на совпадения - ЭКСЕЛЬ ХАК
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции ЕСЛИ . Рассмотрим как это работает на примерах ниже.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
В тех случаях, когда в нашей таблице слишком много столбцов наша формула с функцией ИЛИ будет очень большой, так как в ее параметрах нам нужно указать критерии совпадения между каждым столбцом таблицы. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.

Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel | Exceltip

Для создания списка, содержащего найденные значения, воспользуемся формулой массива : =ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))

Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

повторяющиеся значения excel

повторяющиеся значения excel

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

Как в Excel найти слово или фразу? MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение — Сообщество взаимопомощи учителей

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C200:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

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

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

Переход в окно управления правилами условного форматирования в Microsoft Excel

Диспетчер правил условного форматирования в Microsoft Excel

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

Переход в окно выбора формата в Microsoft Excel

Выбор цвета заливки в окне формат ячеек в Microsoft Excel

Окно создания правила форматирования в Microsoft Excel

Применение правила в диспетчере правил в Microsoft Excel

Несовпадающие данные отмечены с помощью условного форматирования в Microsoft Excel

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

Выделение сравниваемых таблиц в Microsoft Excel

Переход к условному форматированию в Microsoft Excel

Окно настройки выделения повторяющихся значений в Microsoft Excel

Повторяющиеся значения выделены в Microsoft Excel

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

Настройка выделения уникальных значений в Microsoft Excel

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

Уникальные значения выделены в Microsoft Excel

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Но ВПР работает только с одной таблицей и нет никакой возможности средствами самой функции просмотреть искомое значение на нескольких листах. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
В результате вычисления данного блока у нас получается массив из количества повторений искомого значения на каждом из указанных листов: СЧЁТЕСЛИ(;A2) . Поэтому следующий блок
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ(«’»& $E1500:$E$5 &»’!A1:A50»); A2 )>;0;0)
работает именно с этим:
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(; A2 )>0;0)
Читать подробнее про СЧЁТЕСЛИ

Магия Excel: 10 самых полезных «фишек» для работы с таблицами — БизнесБизнес

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

Нужно нажать на стрелочку в том столбце, в котором будет выполняться фильтр. В нашем случае нажимаем стрелочку в столбце Слова и пишем символы, которые мы будем искать — «замок». То есть мы выведем только те строки, в которых есть слово «замок».

Видеоурок по теме

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

Excel Поиск Значений на Разных Листах Книги • Дата и время

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Excel Поиск Значений на Разных Листах Книги • Дата и время

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Кстати, такая возможность есть
на всех страницах нашего сайта

Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию.

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

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

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

Сравнить листы в Excel на совпадения - Офис Ассист
Для создания списка, содержащего найденные значения, воспользуемся формулой массива : =ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
ИНДЕКС Список; НАИМЕНЬШИЙ ЕСЛИ ЕСЛИОШИБКА ПОИСК C 6;Список ;0 0;СТРОКА Список -СТРОКА A 9 ;30 ; СТРОКА ДВССЫЛ A1 A ЧСТРОК Список. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:

Как найти совпадения в двух листах excel

  • Найти все — выполнит поиск всех совпадений с указанной фразой. В окне ниже появится список, в котором будет указана фраза, содержащая искомые символы, а также место в документе, где символы были найдены.

Рассмотрим одну из полезных опций, предлагаемую программой Microsoft Excel. Кстати, лицензионную версию этой программы вы можете купить в нашем интернет-магазине со скидкой. Цены и версии можно посмотреть здесь.

Оформление

Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».

Excel Поиск Значений на Разных Листах Книги • Дата и время

На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:

Excel Поиск Значений на Разных Листах Книги • Дата и время

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

Excel Поиск Значений на Разных Листах Книги • Дата и время

На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь →

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).

Протягивание

  • Текст вместо чисел
  • Отрицательные числа там, где их быть не может
  • Числа с дробной частью там, где должны быть целые
  • Текст вместо даты
  • Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

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

Видео: Поиск совпадений в Excel

ВПР по всем листам (43,0 KiB, 14 152 скачиваний)

Если необходимо найти какое-либо значение в большой таблице очень часто применяется функция ВПР. Но ВПР работает только с одной таблицей и нет никакой возможности средствами самой функции просмотреть искомое значение на нескольких листах. Если поиск необходимо осуществить только по двум листам, то можно схитрить:
=ВПР( A1 ;ЕСЛИ(ЕНД(ВПР( A1 ;Лист2!A1:B10;2;0));Лист3!A1:B10;Лист2!A1:B10);2;0)

А когда листов больше? Можно плодить ЕСЛИ. Но это во-первых совсем не наглядно и во-вторых очень непрактично, т.к. при добавлении или удалении листов придется править всю мега-формулу. Да и при работе с количеством листов более 10 есть большой шанс, что длина формулы выйдет за пределы допустимой.

Есть небольшой прием, который поможет искать значение в указанных листах. Для начала необходимо создать на листе список листов книги, в которых искать значение. В приложенном к статье примере они записаны в диапазоне $E$2:$E$5 .
=ВПР( A2 ;ДВССЫЛ(«’»&ИНДЕКС( $E$2:$E$5 ;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ(«’»& $E$2:$E$5 &»’!A1:A50″); A2 )>0;0))&»’!A:B»);2;0)

Формула вводится в ячейку как формула массива — т.е. сочетанием клавиш Ctrl+Shift+Enter. Это очень важное условие. Если формулу не вводить в ячейку как формулу массива, то необходимого результата не получить.
Попробую кратенько описать принцип работы данной формулы.

ВПР по всем листам (43,0 KiB, 14 152 скачиваний)

ДВССЫЛ нам нужна для преобразования текстового представления ссылок на листы в действительные. Подробно не буду останавливаться на принципе работы ДВССЫЛ, просто приведу этапы вычислений:
СЧЁТЕСЛИ(ДВССЫЛ(«’»& $E$2:$E$5 &»’!A1:A50»); A2 )

В результате вычисления данного блока у нас получается массив из количества повторений искомого значения на каждом из указанных листов: СЧЁТЕСЛИ(;A2) . Поэтому следующий блок
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ(«’»& $E$2:$E$5 &»’!A1:A50»); A2 )>;0;0)
работает именно с этим:
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(; A2 )>0;0)
Читать подробнее про СЧЁТЕСЛИ

в результате чего мы получаем позицию имени листа в массиве имен листов $E$2:$E$5 , с помощью ИНДЕКС получаем имя листа и подставляем это имя уже к ДВССЫЛ, а она в ВПР:
=ВПР( A2 ;ДВССЫЛ(«’»&ИНДЕКС(;1)&»’!A:B»);2;0) =>
=ВПР( A2 ;ДВССЫЛ(«’Лист2′!A:B»);2;0) =>
=ВПР( A2 ;’Лист2′!A:B;2;0)

Что нам и требовалось. Теперь если в книгу будут добавлены еще листы, то необходимо будет всего лишь дописать их к диапазону $E$2:$E$5 и при необходимости этот диапазон расширить. Так же можно задать диапазон $E$2:$E$5 как динамический и тогда необходимость в правке формулы отпадет вовсе.

Используемые в формуле величины:
A2 — ссылка на ячейку с искомым значением. Т.е. указывается то значение, которое требуется найти на листах.

$E$2:$E$5 — диапазон с именами листов, в которых требуется осуществлять поиск указанного значения ( A1 ).

«’!A:B» — диапазон для аргумента ВПР — Таблица. В первом столбце этого диапазона на каждом из указанных листов ищется указанное значение ( A2 ). При нахождении возвращается значение из указанного столбца. Читать подробнее про ВПР>>

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

ВПР по всем листам (43,0 KiB, 14 152 скачиваний)

Так же можно искать по нескольким листам разных книг , а не только по нескольким листам одной книги. Для этого необходимо будет в списке листов вместе с именами листов добавить имена книг в квадратных скобках: [Книга1.xlsb]Май
[Книга1.xlsb]Июнь
[Книга2.xlsb]Май
[Книга2.xlsb]Июнь
Перечисленные книги обязательно должны быть открыты

Решил добавить простенькую функцию пользователя(UDF) для тех, кому проще «общаться» с VBA, чем с формулами. Функция ищет указанное значение во всех листах книги, в которой записана(даже в скрытых):

Как сравнить несколько столбцов на совпадения в одной строке Excel
Представим, что наша задача выявить из таблицы с данными в несколько столбцов те строки, в которых данные совпадают или повторяются как минимум в двух столбцах. В этом нам помогут функции ЕСЛИ и ИЛИ . Напишем формулу для таблицы, состоящей из трех столбцов с данными:
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
На вкладке Главная на Панели инструментов нажимаем на пункт меню Условное форматирование — Правила выделения ячеек — Повторяющиеся значения ;. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.

Задача

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C200:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: