Формула в excel для поиска совпадений

Примеры использования функции ПОИСКПОЗ в Excel

Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

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

Например, массив {«виноград»;»яблоко»;»груша»;»слива»} содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(«яблоко»;{«виноград»;»яблоко»;»груша»;»слива»};0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

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

Видео

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

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

Для более опытных – сразу переходим к сути вопроса.

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

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

К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:

Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.

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

Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая инструкция”

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

1. Выделяем все ячейки нашей таблицы.
2. Во вкладке “Главная” кликаем по кнопке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
3. Откроется перечень, в котором выбираем группу “Правила выделения ячеек“, а внутри нее – пункт “Повторяющиеся значения“.
4. Окно настроек форматирования оставляем без изменений. Единственный его параметр, который можно поменять в соответствии с собственными цветовыми предпочтениями – это используемая для заливки выделяемых строк цветовая схема. По готовности нажимаем кнопку ОК.
5. Теперь все повторяющиеся ячейки в таблице “подсвечены”, и с ними можно работать – редактировать содержимое или удалить строки целиком любым удобным способом.

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

Как сравнить два файла Excel

Нужно сравнить два файла Microsoft Excel? Вот два простых способа сделать это.

Существует множество причин, по которым вам может понадобиться взять один документ Excel и сравнить его с другим. Это может быть трудоемкой задачей

это требует большой концентрации, но есть способы облегчить себе жизнь.

Нужно ли вам внимательно посмотреть вручную или вы хотите, чтобы Excel выполнял некоторые тяжелые работы

от вашего имени, вот два простых способа сравнить несколько листов.

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

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

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

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Обнаружение повторяющихся строк

Мы рассмотрели, как обнаружить одинаковые данные в отдельных ячейках. А если нужно искать дубликаты-строки?

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

Итак, имеются данные о товарах и заказчиках.

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

Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:

=A2&B2&C2

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

=СЧЁТЕСЛИ(D:D;D2)

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

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

Если вас не интересует определенный столбец, просто не включайте его в выражение, находящееся в D. Например, если вам хочется обнаружить совпадающие строки, не учитывая при этом значение Заказчик, уберите из объединяющей формулы упоминание о ячейке С2.

Использование расширенного фильтра

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

1. В разделе «Данные» возле кнопки «Фильтр» кликнуть по слову «Дополнительно». Откроется окно «Расширенный фильтр».

1. Поставить тумблер рядом со строкой «Скопировать результаты в другое место» и нажать на пиктограмму, расположенную около поля «Исходный диапазон».
2. Выделить мышкой диапазон ячеек, где требуется найти дубликаты. Окно выбора автоматически закроется.
3. Далее в строчке «Поместить результат в диапазон» также надо нажать ЛКМ по пиктограмме в конце и выделит любую ячейку вне таблицы. Это будет начальный элемент, в который вставится отредактированная табличка.

1. Установить галочку в строке «Только уникальные записи» и кликнуть «ОК». В итоге рядом с исходным массивом появится отредактированная таблица без дубликатов.

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

Формула проверки наличия дублей в диапазонах

Использование собственной формулы для проверки дубликатов в списке или диапазоне имеет ряд преимуществ, единственная задача — составление такой формулы. Но её я возьму на себя.

Внутри диапазона

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

Так выглядит на практике применение формулы:

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

А дело все в том, что формулу несложно видоизменить и улучшить.

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

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

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

Обратите внимание на пару моментов этой демонстрации:

• диапазон закреплен ($A$1:$B$4), а искомая ячейка — нет (A1).
• именно это позволяет условному форматированию находить все дубликаты в диапазоне

Сравнить две таблицы с помощью макроса VBA

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

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

Сравнение двух таблиц в Excel на совпадение значений в столбцах

​ использовать функцию​shipping_and_handling​ фунтах (Lbs weight),​ возвращать значение из​Подстановка​Примечание:​Предположим, что требуется найти​. Формула возвращает цену​ совпало, но во​ Vikttur 912 мсек​: =ЕСЛИОШИБКА(ПРОСМОТР(2;1/СЧЁТЕСЛИ(Лист1!A2;Лист2!$A$2:$A$5);Лист2!$B$2:$B$5);»-«)​ сделать условным форматированием.​

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

​ в Таблице_1, будут​ символов. Например, строки​ «нет».​ ПОИСКПОЗ возвращает значение​ из присутствующих в​IF​.​ а также стоимость​ одного столбца, одной​.​

​ Данный метод целесообразно использовать​ внутренний телефонный номер​ на другую деталь,​ второй таблице один​ (думаю, причина в​

1. ​Zhukov_K​ В таком варианте​
2. ​ подсвечены синим цветом.​ «МоСкВа» и «москва»​Чтобы вычислить остальные значения​
3. ​ ключа, который явно​ списке позиций. Кроме​(ЕСЛИ), чтобы определить,​Теперь при создании формулы​ обработки и перевозки.​

​ строки или из​Если команда​ при поиске данных​ сотрудника по его​ потому что функция​ город для него​

​ конструкции ЕСЛИ(…) Михаил​: Михаил С., спасибо​ колонки С и​Выделите диапазон первой таблицы:​ являются равнозначными. Для​ «протянем» формулу из​ не указан.​ этого, если они​ какой номер столбца​ Вы можете использовать​ Мы можем использовать​

​ массива (аналог ВПР​Подстановка​ в ежедневно обновляемом​ идентификационному номеру или​ ВПР нашла ближайшее​

​ С 158 мсек​ большое за еще​ D не нужны​ A2:A15 и выберите​ различения регистров можно​ ячейки C2 вниз​Например, массив {«виноград»;»яблоко»;»груша»;»слива»} содержит​ передумают и решат​ нужно использовать. Формула​

1. ​ имя диапазона. Вот​ функцию​ и ГПР). Из​недоступна, необходимо загрузить​ внешнем диапазоне данных.​ узнать ставку комиссионного​
2. ​ число, меньшее или​ первой другой -​
3. ​Silence​ одно решение, в​ формула проще и​ инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать​ дополнительно использовать функцию​
4. ​ для использования функции​ элементы, которые можно​ выбрать другой ковер,​ поиска будет выглядеть​ здесь вместо адреса​ВПР​
5. ​ этих трёх функций,​ надстройка мастера подстановок.​
6. ​ Известна цена в​ вознаграждения, предусмотренную за​ равное указанному (2345678).​ то это не​: Добрый вечер, прошу​

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

​ нем даже ИНДЕКС​ одна.​ правило»- «Использовать формулу​ СОВПАД.​ автозаполнения. В результате​ представить как: 1​ то функция​ следующим образом:​ диапазона указано его​, чтобы найти значение​ вероятнее всего, Вы​Загрузка надстройки мастера подстановок​ столбце B, но​ определенный объем продаж.​ Эта ошибка может​ есть хорошо, ставим​ помочь в написании​ и ПОИСКПОЗ не​

​Выделяем все имена​ для определения форматированных​

​Если поиск с использованием​ получим:​ – «виноград», 2​ВПР​=VLOOKUP(A7,A2:C4,IF(B7=»M»,2,3),FALSE)​ имя:​ веса и определить​ будете использовать​Нажмите кнопку​ неизвестно, сколько строк​ Необходимые данные можно​ привести к неправильному​ Wrong и указываем​

exceltable.com

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

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

Вид таблицы данных:

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

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

В результате расчетов получим:

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

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

Результат расчетов формулы:

Всего было куплено 4 модели iPhone 5s по цене 239 у.е., что в целом составило 956 у.е.

Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

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

C1 — это левые верхние ячейки диапазона (также называемые начальной).

MATCH(«Оранжевая»;C2:C7;0) ищет «Оранжевые» в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.

1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.

К началу страницы

Формулой найти значение по частичному совпадению текста

​ 1, 2, 3,​​Если Вы работаете с​ в столбце A​ из столбца​ веса. Конечно же,​ ней я сфокусируюсь​Параметры Excel​ алфавитном порядке.​​ их правильность. Значения,​ ЛОЖЬ или 0,​ таблице — не​​ листе 1 с​ «диапазона» в СЧЁТЕСЛИ​ первом окошке выбираем​ и на вкладке​Если аргумент [тип_сопоставления] явно​ в диапазоне чисел,​​ 4 – ключи,​ приблизительными совпадениями, то​ и возвращаем цену​Handling​ вес большинства партий​

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

​ пишем ничего.​​ листом 2. В​​ выступает одна ячейка)​

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

​ из столбца B​​для расчёта значений​ товара не будет​ В целом, если​Надстройки​ — это левая верхняя​ затем использовать в​ нет, вместо неправильного​Интересует усложнение формулы​ случае соответствия в​vikttur​​ второе вставляем ее​ цвет. На всех​ принимает число 0,​

​ таблицы Excel.​​ – значения. Тогда​ в таблице. Для​ ​ или C, в​​ в столбце​ иметь такие же​ Вы поймете и​.​ ячейка диапазона (также​​ вычислениях или отображать​ значения формула возвращает​ без утяжеления расчетов​ лист 1 столбец​: Какая разница -​​ =СЧЁТЕСЛИ (A$2:A$20;B2) Затем​ окнах жмем ОК.​ для поиска частичного​​Вид исходной таблицы данных:​​ функция =ПОИСКПОЗ(«яблоко»;{«виноград»;»яблоко»;»груша»;»слива»};0) вернет​​ этого выделите весь​​ зависимости от выбранного​Shipping​

​ ровные значения, поэтому​​ сможете применить функцию​В поле​

​ называемая начальной ячейкой).​​ как результаты. Существует​

​ в ячейку строку​​ в случае огромного​ «ЦЕНА» проставить значение​ одна ячейка или​ жмем “формат” и​Выделите диапазон первого списка:​ совпадения текстовых значений​Для поиска ближайшего большего​ значение 2, являющееся​ диапазон с данными,​ размера ковра. Если​. В данном случае​

​ мы используем в​​ВПР​Управление​Формула​ несколько способов поиска​ ​ «#Н/Д». Это наилучшее​ ​ количества строк. В​​ цены из листа​ сто? В любом​​ выбираем «украшательство» для​​ C2:C15 и снова​ могут быть использованы​ значения заданному во​ ключом второго элемента.​ включая заголовки строк​​ точное совпадение не​ поменяется только номер​ качестве последнего аргумента​, то сможете справиться​​выберите значение​​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​ значений в списке​ решение. В данном​

​ частности, чтобы все​​ 2.​ случае это диапазон.​ совпадений. Ок. Ок.​ выберите инструмент: «ГЛАВНАЯ»-«Условное​ подстановочные знаки («?»​

​ всем столбце A:A​​ Отсчет выполняется не​ в первом столбце.​ найдено, т.е. название​ столбца. Для​TRUE​ и с​Надстройки Excel​ищет значение «Апельсины»​ данных и отображения​ случае «#Н/Д» не​ было так, как​Che79​1/СЧЁТЕСЛИ(Лист1!A2;Лист2!$A$2:$A$5)​​Silenser​ форматирование»-«Создать правило»- «Использовать​ — замена одного​ (числовой ряд может​ с 0 (нуля),​ Заголовки столбцов (шапку)​ ковра в заказе​Shipping​(ИСТИНА), либо вовсе​ГПР​и нажмите кнопку​ в диапазоне C2:C7.​ результатов.​ означает, что формула​

​ сейчас есть в​​: Здравствуйте.​1/число>0, 1/0​: Юзай формулы ЕСЛИ​ формулу для определения​ любого символа, «*»​ пополняться новыми значениями)​ как это реализовано​ можно не выделять.​ не соответствует ни​– это значение​ не указываем его.​

​.​​Перейти​ Начальную ячейку не​Поиск значений в списке​ введена неправильно (за​ ​ I:L по условию​​Не уверен, что​Массив из ошибок​​ и СОВПАД.​ форматированных ячеек:».​ — замена любого​ используем формулу массива​ во многих языках​ На вкладке​ одному из названий​3​ В таком случае​Чтобы с помощью​.​

Другие причины для сравнения книг

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

Средство сравнения электронных таблиц можно использовать не только для сравнения содержимого листов, но и для поиска различий в коде Visual Basic для приложений (VBA). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.

Теги