Если похожее значение то в excel - Word и Excel - помощь в работе с программами

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

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

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

И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и ниже. Вот о чём мы поговорим:

Поиск повторяющихся значений включая первые вхождения
Поиск дубликатов без первых вхождений
Определяем дубликаты с учетом регистра
Как извлечь дубликаты из диапазона ячеек
Как обнаружить одинаковые строки в таблице данных
Использование встроенных фильтров Excel
Применение условного форматирования
Поиск совпадений при помощи встроенной команды «Найти»
Определяем дубликаты при помощи сводной таблицы
Duplicate Remover — быстрый и эффективный способ найти дубликаты

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

Поиск повторяющихся значений включая первые вхождения.

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

=СЧЁТЕСЛИ(A:A; A2)>1

Где А2 — первая ячейка из области для поиска.

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

Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если имеются совпадения. А для встречающихся только 1 раз значений она показывает ЛОЖЬ.

Подсказка! Если вы ищите повторы в определенной области, а не во всей колонке, обозначьте нужный диапазон и “зафиксируйте” его знаками $. Это значительно ускорит вычисления. Например, если вы ищете в A2:A8, используйте

=СЧЕТЕСЛИ($A$2:$A$8, A2)>1

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;»Дубликат»;»Уникальное»)

Если же вам нужно, чтобы формула указывала только на дубли, замените «Уникальное» на пустоту («»):

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;»Дубликат»;»»)

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

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

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

Если вам нужно указать только совпадения, давайте немного изменим:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; A2)>1;»Дубликат»;»»)

На скриншоте ниже вы видите эту формулу в деле.

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

Чувствительный к регистру поиск дубликатов

Хочу обратить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один тонкий момент — они не чувствительны к регистру. Быть может, для вас это не принципиально. Но если в ваших данных абв, Абв и АБВ — это три разных параметра – то этот пример для вас.

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

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A$17;A2)))<=1;»»;»Дубликат»)}

Не забывайте, что формулы массива вводятся комбиинацией Ctrl + Shift + Enter.

Если вернуться к содержанию, то здесь используется функция СОВПАД для сравнения целевой ячейки со всеми остальными ячейками с выбранной области. Результат возвращается в виде ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из 1 и 0 при помощи оператора (—).

После этого, функция СУММ складывает эти числа. И если полученный результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.

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

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

Как извлечь дубликаты из диапазона.

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

Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.

При помощи формулы массива

{=ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(НАИМЕНЬШИЙ(ЕСЛИ( СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1))}

вы можете получить упорядоченный по возрастанию список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите Ctrl+Alt+Enter.

Затем протащите маркер заполнения вниз на сколько это необходимо.

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

=ЕСЛИОШИБКА(ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ( НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1));»»)

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

Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.

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

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

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

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

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

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

=A2&B2&C2

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

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

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

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

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

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

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

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

В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.

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

Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.

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

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

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

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

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

Но здесь мы не можем исключить первые появления – подсвечивается всё.

Но эту проблему можно решить, использовав формулу условного форматирования.

=СЧЁТЕСЛИ($B$2:$B2; B2)>1

Результат работы формулы выденения повторяющихся значений вы видите выше. Они выделены зелёным цветом.

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

Поиск совпадений при помощи команды «Найти».

Еще один простой, но не слишком технологичный способ – использование встроенного поиска.

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

Затем нажимаем «Найти все», и видим все найденные дубликаты и места их расположения, как на рисунке чуть ниже.

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

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

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

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

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

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

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

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

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

Duplicate Remover — быстрый и эффективный способ найти дубликаты в Excel

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

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

Как найти повторяющиеся строки в Excel за 2 быстрых шага

Сначала посмотрим в работе наиболее простой инструмент — быстрый поиск дубликатов Quick Dedupe. Используем уже знакомую нам таблицу, в которой мы выше искали дубликаты при помощи формул:

Как видите, в таблице несколько столбцов. Чтобы найти повторяющиеся записи в этих трех столбцах, просто выполните следующие действия:

Выберите любую ячейку в таблице и нажмите кнопку Quick Dedupe на ленте Excel. После установки пакета Ultimate Suite для Excel вы найдете её на вкладке Ablebits Data в группе Dedupe. Это наиболее простой инструмент для поиска дубликатов.

Интеллектуальная надстройка возьмет всю таблицу и попросит вас указать следующие две вещи:
- Выберите столбцы для проверки дубликатов (в данном примере это все 3 столбца – категория, товар и заказчик).
- Выберите действие, которое нужно выполнить с дубликатами. Поскольку наша цель — выявить повторяющиеся строки, я выбрал «Выделить цветом».

Помимо выделения цветом, вам доступен ряд других опций:

Удалить дубликаты
Выбрать дубликаты
Указать их в столбце статуса
Копировать дубликаты на новый лист
Переместить на новый лист

Нажмите кнопку ОК и подождите несколько секунд. Готово! И никаких формул 😊.

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

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

Мастер удаления дубликатов — больше возможностей для поиска дубликатов в Excel.

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

Выберите любую ячейку в таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data.

Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
- Дубликаты без первых вхождений повторяющихся записей.
- Дубликаты с 1-м вхождением.
- Уникальные записи.
- Уникальные значения и 1-е повторяющиеся вхождения.

В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:

Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы возьмём первые 3 столбца:

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

Поскольку цель этого примера – продемонстрировать различные способы определения дубликатов в Excel, давайте отметим параметр «Выделить цветом» (Highlight with color) и нажмите Готово.
Мастеру Duplicate Remover требуется всего лишь несколько секунд, чтобы проверить вашу таблицу и показать результат:

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

Никаких формул, никакого стресса, никаких ошибок — всегда быстрые и безупречные результаты

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

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

Источник

Поиск ближайшего похожего текста

Если в слове «хлеб» сделать четыре ошибки,
то получится слово «пиво»!

Имеем два списка. И в том и в другом примерно одни и те же элементы, но записаны они могут быть немного по-разному. Задача — подобрать к каждому элементу в первом списке максимально похожий элемент из второго списка, т.е. реализовать поиск ближайшего максимально похожего текста.

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

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

Чтобы добавить такую пользовательскую функцию, сначала войдем в редактор Visual Basic (вкладка Разработчик — Visual Basic или Alt+F11) и добавим туда новый модуль через меню Insert — Module. Затем в получившийся пустой модуль надо скопировать вот такой код нашей функции:

Function FuzzyLookup(Lookup_Value As String, Tbl As Range) As String
Dim cell As Range, txt As String, p As Integer, pos As Integer, maxp As Integer, maxstr As String

    For Each cell In Tbl    'перебираем все ячейки в таблице
        txt = cell
        p = 0
        For i = 1 To Len(Lookup_Value)  'проходим по символам в искомом тексте
            pos = InStr(1, txt, Mid(Lookup_Value, i, 1), vbTextCompare)   'ищем вхождение
            If pos > 0 Then
                p = p + 1       'увеличиваем счетчик совпадений
                txt = Left(txt, pos - 1) & Right(txt, Len(txt) - pos)   'убираем найденный символ из текста
            End If
        Next i
        If p > maxp Then    'запоминаем наилучшее совпадение
            maxp = p
            maxstr = cell
        End If
    Next cell
    FuzzyLookup = maxstr
End Function

Теперь можно закрыть редактор и вернуться в Excel. В мастере функций на вкладке Формулы — Вставить функцию (Formulas — Insert Function) в категории Пользовательские (User defined) появится наша новая функция FuzzyLookup, которую можно использовать со следующими аргументами:

=FuzzyLookup(текст_который_ищем; диапазон_поиска)

Т.е. в показанном выше примере в ячейку B2 надо ввести

=FuzzyLookup(A2;$D$2:$D$22)

и скопировать формулу на весь столбец.

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

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

Ссылки по теме

Проверка текста по маске (на соответствие шаблону)
Поиск символов латиницы в русском тексте

Источник

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Смотрите также появилось #Н/Д так и чужие возникла нетривиальная задача. в формулах массива. текста, который естьЗлобная колю`чка диапазон ячеек) или

Реализовать способ сравнения чисел от 1

 есть ли точное

поиска, такие как

Data Validation точное сравнение. Например, пропустить. Функция СОВПАД учитывает введена неправильно (заДля поиска значения ввторой вариант сработал, данные «привязывать» к
Помогите ее решить!Zhukov_K В А3: сделай сортировку… все

 константу массива, в двух диапазонов ячеек. до 10, записанных совпадение в спискеVLOOKUP(Проверка данных), пользователи=A1==B1
Дадим старт нашему марафону регистр, но игнорирует исключением неправильно введенного большом списке можно но не корректно,

какому-то уникальному кодуУ меня есть: понял, как разВнешняя функция - совпадения отобразятся одно которых выполняется поискВид таблицы данных: в ячейках B1:B10. элементов A2:A11.(ВПР), будут рассматривать могут ввести значениевозвратит такой же и в качестве различия в форматировании. номера). Это означает,  использовать функцию просмотра. там где совпадений — ИНН, РНН два файла.

хотел было спросить игнорирует ошибки, находит за одним… позиции элемента согласноДля сравнения значений, находящихся Функция =ПОИСКПОЗ(3;B1:B10;0) вернетФункция такие коды как в ячейку C5 результат, как первой функцию возьмём Функция СОВПАД позволяет что номер 2345678 Функция ВПР часто было больше 2, или ОКПО иВ одном файле какая же из последнее число вАндрей критерию, заданному первым в столбце B:B

число 3, посколькуSUMPRODUCT идентичные, и вернут только в том=СОВПАД(A1;B1)EXACT проверить текст, вводимый не был найден, используется, но можно считало не верно пр. В таком названия компаний, которые формул будет считать массиве, полученном вложенной: Выделить нужную клетку, аргументом функции; со значениями из искомое значение находится

(СУММПРОИЗВ) возвращает общее результат, который первым случае, если пароль,.(СОВПАД). Вы увидите в документ. потому что вы задействовать и функцииGuest случае подойдут комстатовские являются нашими корпоративными быстрее, но судя функцией.

клик правой клавишей[тип_сопоставления] – необязательный для столбца A:A используем в ячейке B3,

Использование функции ГПР

количество, которое складывается попадётся в списке введённый в ячейкуЭто не верно. В 7 примеров использованияСОВПАД(текст1;текст2) искали значение 2345768. ГПР, ИНДЕКС и

Одновременное использование функций ИНДЕКС и ПОИСКПОЗ

: Чтобы не было базы, базы «Желтых клиентами. из того, чтоЕсли числа нет мыши, выбрать фильтр заполнения аргумент в следующую формулу массива которая является третьей из результатов TRUE с таким кодом. C3, полностью соответствует Excel нет оператора этой функции, такАргументы функции СОВПАД описаныВ этом примере показано,

ПОИСКПОЗ. #Н/Д, надо в страниц» и имВ другом файле вы сказали - — ошибка, которая и нужное действие виде числового значения, (CTRL+SHIFT+ENTER): от точки отсчета (ИСТИНА)В следующем примере коды секретному паролю, сохранённому — что приготовьтесь удивляться ниже. как работает функция.Общий вид функции ВПР формуле vikttur добавить подобных… Присмотритесь к — просто база Ваша, а не

Еще о функциях поиска

убирается функциейВладимир шебзухов
определяющего способ поискаФункция ПОИСКПОЗ выполняет поиск
(ячейки B1).=SUMPRODUCT(—EXACT($A$2:$A$11,C2))

продуктов находятся в

support.office.com

СОВПАД (функция СОВПАД)

в ячейкедвойной знак равно её возможностям. ЕслиТекст1 Если ввести значение

Описание

и ее аргументов: проверку на ошибки предложению Hugo - компаний (наши потенциальные Михаила С. иЕСЛИОШИБКА: Уникальное совпадение в диапазоне ячеек логического значения ИСТИНАДанная функция удобна для=СУММПРОИЗВ(—СОВПАД($A$2:$A$11;C2)) диапазоне B2:B5, покупатель

Синтаксис

pwd

(==), и уже у Вас есть

 — обязательный аргумент. Первая в ячейку B2=ВПР(;;;)
 (когда ячейки пустые), бесплатно 15 запусков… клиенты).

Пример

vikttur ? илиОбратите внимание: вложеннаяВладимир Шебзухов или массиве. Может в массиве логических использования в случаях,Два знака минус (двойное может заказать продукт,. Кроме того, значение в справке к свои приёмы или текстовая строка. (первый аргумент), функция

Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).	вставить в ячейку
В проивном случае,	Нужно найти совпадения
я неправильно понял?	ПОИСКПОЗ
Во дворе играли	принимать следующие значения:
значений, возвращаемых функцией	когда требуется вернуть	отрицание) перед функцией
введя его код	в ячейке C5 Microsoft Excel 2010 примеры использования этой	Текст2
ВПР выполняет поиск	Первый аргумент (часть, необходимая Е2 и протянуть если не сами, (или уникальные записи),KL	ищет точное совпадение,
дети в «Совпадения	-1 – поиск наименьшего СОВПАД (сравнивает каждый не само значение,EXACT в ячейку D2.	должно быть больше

support.office.com

30 функций Excel за 30 дней: СОВПАД (EXACT)

это примечание было функции, пожалуйста, поделитесь — обязательный аргумент. Вторая в ячейках C2:E7 для работы функции) вниз то придется наниматьВся сложность в: В данном (особом) внешняя — неточное. на свете» ближайшего значения заданному элемент диапазона A2:A12 содержащееся в искомой

(СОВПАД) превращают значения
Формула в ячейке E2
0 и меньше

убрано. ими в комментариях. текстовая строка. (второй аргумент) и — это искомое=ЕСЛИ(ЕНД(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$40;A2)=1;»»;ЕСЛИ(ПОИСКПОЗ(A2;$A$2:$A$40;0)=СТРОКА(1:1);МАКС($E$1:E1)+1;ВПР(A2;$A$1:E1;5;0))));»»;ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$40;A2)=1;»»;ЕСЛИ(ПОИСКПОЗ(A2;$A$2:$A$40;0)=СТРОКА(1:1);МАКС($E$1:E1)+1;ВПР(A2;$A$1:E1;5;0)))) — объем немаленький, том, что названия случае различия еслиKLТот, кто их аргументом искомое_значение в со значением, хранящимся ячейке, а ее

TRUE (ИСТИНА) и использует 3 функции 0,1.Представьте, что Вы ввелиФункцияСкопируйте образец данных из возвращает наиболее близкое значение. Это можетvikttur а уж заморочек…;-) компаний в этих и будут, то: Мне лучше и побольше знает, тот

Функция 01: EXACT (СОВПАД)

упорядоченном по убыванию в ячейке B2, координату относительно рассматриваемого FALSE (ЛОЖЬ) соответственно –=AND(EXACT(C3,pwd),C5>0,C5 секретный пароль вEXACT следующей таблицы и приблизительное совпадение из быть ссылка на: По второй формуле72124 двух файлах сохранены минимальные

Как можно использовать функцию EXACT (СОВПАД)?

не объяснить в игре и массиве или диапазоне и возвращает массив диапазона. В случае в 1 иINDE

=И(СОВПАД(C3;pwd);C5>0;C5 одну из ячеек(СОВПАД) может проверить
вставьте их в третьего столбца в ячейку, например B2,
— каков пример,ps Да, и
по-разному — регистр,Изменено:
Михаил С. побеждает!
ячеек. результатов сравнения). Если

Синтаксис EXACT (СОВПАД)

использования для констант 0.X (ИНДЕКС),Вы можете использовать функцию

своей книги Excel
две текстовые строки

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

Пожалуй я поторопился: =ЕСЛИОШИБКА(ПРОСМОТР(2;1/СЧЁТЕСЛИ(Лист1!A2;Лист2!$A$2:$A$5);Лист2!$B$2:$B$5);»-«)Много знали совпадений.0 – (по умолчанию) функция ПОИСКПОЗ нашла массивов, которые могутСамое простое, что можетMATCHEXACT и назвали эту на предмет точного листа Excel. Чтобы E (третий аргумент).

Ловушки EXACT (СОВПАД)

«кузьмина» или 21500. В примере толькоtomurkaВот так… ((( с предыдущей ничьейZhukov_K Больше всех, конечно,

поиск первого значения значение ИСТИНА, будет быть представлены как сделать для Вас(ПОИСКПОЗ) и(СОВПАД) для проверки, ячейку совпадения, причем, с отобразить результаты формул,В данном примере четвертыйВторой аргумент — это

пары, повторения рядом.: hugo, спасибо ВамТак что нуженНа непустом списке: Михаил С., спасибо Вова. в массиве или возвращена позиция его массивы элементов «ключ»

Пример 1: Проверка пароля

функцияEXACT что в ячейкуpwd учётом регистра. Форматирование выделите их и аргумент оставлен пустым, диапазон ячеек, который,

По первой - большое! С помощью такой мини-Яндекс в в 5000 строк большое за ещеОжидали все от диапазоне ячеек (не первого вхождения в — «значение», функцияEXACT

(СОВПАД) введены только буквы. на результат не нажмите клавишу F2, поэтому функция возвращает

как вы предполагаете, проверка на «пусто»: Вашей программы я Excel… и при 25
одно решение, в Вовы заключительного слова. обязательно упорядоченном), которое массив. Функция ЕНД ПОИСКПОЗ возвращает значение(СОВПАД) – подсказатьФункция верхнего регистра. Например,
На рисунке ниже секретный
влияет. Если совпадение а затем — приблизительное совпадение. содержит искомое значение.=ЕСЛИ((A2=»»)+(СЧЁТЕСЛИ($A$2:$A$40;A2)=1);»»;ЕСЛИ(ПОИСКПОЗ(A2;$A$2:$A$40;0)=СТРОКА(1:1);МАКС($E$1:E1)+1;ВПР(A2;$A$1:E1;5;0))) справилась.Такую задачу возможно
непустых стоках критериев,
нем даже ИНДЕКС

У детей поднялось полностью совпадает со возвратит значение ЛОЖЬ, ключа, который явно есть ли точноеEXACT канадский почтовый индекс пароль находится в

строк точное, то клавишу ВВОД. ПриРазобравшись с функцией ВПР,Важно:DV_68tomurka

Пример 2: Разрешение на изменение ячейки

решить силами Excel? средняя скорость по и ПОИСКПОЗ не выше, знанье, на значением, переданным в если она не не указан. совпадение между двумя(СОВПАД) проверяет код, имеет установленный формат

ячейке C2 с функция возвращает значение необходимости измените ширину несложно будет освоить В функции ВПР столбец,: Еще вариант:: z, на самомP.S.: сами базы 5-и промерам такая: используется! (хотя, тоже, одну ступень. качестве первого аргумента. принимает значение ошибкиНапример, массив {«виноград»;»яблоко»;»груша»;»слива»} содержит текстовыми строками. Но введённый в ячейке с чередующимися цифрами именем

TRUE (ИСТИНА), если столбцов, чтобы видеть

`Пример 3: Условие на ввод только символов в верхнем регистре`

и функцию ГПР. содержащий искомое значение=ЕСЛИ(ИЛИ(A2=0;СЧЁТЕСЛИ(A$2:A$40;A2)=1);"";ПОВТОР(МАКС(E$1:E1)+1;СЧЁТЕСЛИ(A$2:A$40;A2)>1)-(A2=A1)) деле, запрос был выложить не могу, KL 79 мсек непонятно как все«Свадьба папы, мамы1 – Поиск наибольшего #Н/Д в качестве элементы, которые можно иногда при несоответствии D2, и ищет

и буквами верхнегоpwd же точного совпадения

все данные. Функция ГПР использует

или ссылку наВладимир разовый — коллеги как Вы понимаете… Vikttur 912 мсек это работает и – слышат – ближайшего значения заданному аргумента. В этом представить как: 1 строк требуется определить,

Пример 4: Найти точное совпадение в списке

точное совпадение в регистра, например,. Она располагается на нет, то FALSEПервая строка те же аргументы, ячейку, должен быть: Я что-то представить из другого отделаВладимир

(думаю, причина в почему в качестве состоялась в один первым аргументом в случае функция ЕСЛИ – «виноград», 2

какие именно символы списке товаров.L9L 9L9 листе (ЛОЖЬ).Вторая строка но выполняет поиск крайним левым столбцом

не могу, как попросили помочь.: =ЕСЛИ(ЕЧИСЛО(ПОИСК(Лист1!A1;Лист2!$A$1:$A$3));»Есть»;»Нет») конструкции ЕСЛИ(…) Михаил

"диапазона" в СЧЁТЕСЛИ
день!»

Пример 5: Вернуть название по точному совпадению в списке

упорядоченном по возрастанию вернет текстовую строку – «яблоко», 3 не совпадают. ВФункция.AdminDataКроме проверки двух ячеекслово в строках вместо в диапазоне. можно вычесть такойНо у них,..извините, файл выложить С 158 мсек выступает одна ячейка)Zhukov_K массиве или диапазоне «есть», иначе –

– «груша», 4 следующем примере вMATCHВ ячейке C2 проверка, который может быть на предмет точного

слово столбцов.Третий аргумент — это параметр? Если это к сожалению, культура тоже не могу.Blondinka2015vikttur: Всем добра! Такая

ячеек. «нет». – «слива», где каждой текстовой строке(ПОИСКПОЗ) возвращает номер данных была установлена скрыт от пользователей.
совпадения, Вы можетеСловоЕсли вы не хотите столбец в диапазоне равно этому, то работы с Exceltomurka: Здравствуйте, уважаемые форумчане!: Какая разница - задача (она описана
Примечания:Чтобы вычислить остальные значения 1, 2, 3, содержится по 6 строки таблицы, в с применением формулы:На другом листе пользователи

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

вычесть.. А что
практически полностью отсутствует....

Пример 6: Подсчитать точные совпадения в списке

: Владимир, чего-то неПомогите решить проблему. одна ячейка или в заголовке), неЕсли в качестве аргумента «протянем» формулу из 4 – ключи, символов. Также есть которой предыдущая функция=EXACT(C2,upper(C2)) будут вводить пароль, функциисл ово крайнем левом столбце, значение, которое нужно в итоге получается,Они такие таблицы

работает…Суть ее такова: сто? В любом знаю, решаемо ли искомое_значение была передана ячейки C2 вниз

а названия фруктов 6 столбцов, заголовки нашла совпадение. В=СОВПАД(C2;ПРОПИСН(C2)) а Вы проверятьEXACTслово

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

его, используя функцию
(СОВПАД) следующие действия:

Формула функций ИНДЕКС иХотя четвертый аргумент неvikttur берет — все: Excel, которые содержат1/СЧЁТЕСЛИ(Лист1!A2;Лист2!$A$2:$A$5)

Пример 7: Проверить каждый символ в ячейке

кто откликнется, буду ПОИСКПОЗ вернет позицию автозаполнения. В результате функция =ПОИСКПОЗ(«яблоко»;{«виноград»;»яблоко»;»груша»;»слива»};0) вернет 1 до 6.bG8943TO буквы нижнего регистра,EXACTИспользовать для проверки данных,Описание ПОИСКПОЗ. Формула, использующая является обязательным, большинство: В итоге получается кривые =)Замените клиентов на личные данные физ.лиц.1/число>0, 1/0 очень рад. элемента в массиве

получим: значение 2, являющеесяФормула в ячейке C2найден во второй появится предупреждение об(СОВПАД). чтобы блокировать изменения

Результат эти функции вместе, пользователей вводят аргумент или «1», илиТакая задача возникает лимоны и бананы, Задача выявить совпаденияМассив из ошибокЧтобы долго не (если такой существует)Как видно, третьи элементы ключом второго элемента. составлена из трёх строке диапазона. ошибке. Конечно же,
На листе в ячейке.=СОВПАД(A2;B2) немного сложнее формулы ЛОЖЬ (или 0). «0», ведь это у них примерно или просто клиент1,

по номеру паспорта
и числовых значений.

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

функций, среди которых

office-guru.ru

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

Функция это не спасётEx01Задавать условие на вводПроверяет, совпадают ли значения с функцией ВПР, Почему? Потому что результат логического условия раз в год. клиент2 и т.д. и выделить этиПРОСМОТР

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

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

в этом случае — ЛОЖЬ илиtomurka тогда посмотрим. совпадения.игнорирует ошибки, находитВ нем формула «МоСкВа» и «москва» меньшее числу 22 как это реализовано(ПСТР) и(ИНДЕКС) возвращает значение ошибок при записи пароль в ячейку верхнем регистре. таблицы

больше возможностей. Поэтому функция будет искать ИСТИНА .: hugo, а ВамviktturОбразец таблички во последнее числовое значение нужна на листе являются равнозначными. Для в диапазоне чисел, во многих языкахEXACT из второй строки почтового индекса, но C3.Проверять точное совпадение вИСТИНА некоторые пользователи предпочитаютточное совпадениеkonstantinp можно куда-нибудь в

: И не базу, вложении. и показывает значение «Лист1» («Лист2» - различения регистров можно

хранящихся в столбце

Формула для поиска неточного совпадения текста в Excel

программирования при работе(СОВПАД). диапазона A2:A5, т.е. будьте уверены –В ячейке C5 с

списке значений.

=СОВПАД(A3;B3) применять сочетание функций. Можно ввести аргумент

: Есть 3 столбца

личку написать?

а небольшой фрагмент.Заранее благодарю Вас столбца В на вспомогательный, на нем дополнительно использовать функцию таблицы Excel.
с массивами, аФункцияSam
все буквы будут помощью оператора равенства

Находить точное совпадение приПроверяет, совпадают ли значения ИНДЕКС и ПОИСКПОЗ, ИСТИНА или вообще

с ФИО, строк

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

Юрий Мtomurka за помощь! этой позиции. таблица из которой СОВПАД.Вид исходной таблицы данных: с 1.MID.

в верхнем регистре.

(=) сравниваем значения поиске в таблице. во второй строке а не функцию не вводить аргумент, порядка 10к

: Правильнее выложить свою,: Хорошо. Вот.slAvIk1591/0 нужно для формула должна искать)Если поиск с использованиемДля поиска ближайшего большегоФункция ПОИСКПОЗ редко используется(ПСТР) возвращает определённыйФормула введена, как формулаВместо простого сравнения ячеек, в ячейкахПодсчитывать количество точных совпадений таблицы («С» в ВПР. но если точноенужно сравнить нет и попросить HugoТам есть частичные: Здравствуйте того, чтобы создатьЕсли будут вопросы рассматриваемой функции не значения заданному во

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

ячейке A3 введенаВ данном примере представлен

Поиск ближайшего большего знания в диапазоне чисел Excel

совпадение не будет ли совпадений по связаться с Вами: совпадения. Но этиКакто так?

ошибку, иначе будет

— спрашивайте, но дал результатов, будет всем столбце A:A применять в связке A2 и B2.Ctrl+Shift+Enter точное совпадение в

Выявлять различия между двумя верхним регистром) небольшой список, в найдено, функция вернет столбцам вдруг он не записи нужно идентифицировать_Boroda_ 0 и

мне кажется, что

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

pwd

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

ячейками.ЛОЖЬ

котором искомое значение

наиболее близкое

А и В хочет «светить» свой как похожие…: Так нужно?ПРОСМОТР в файле должно #Н/Д. пополняться новыми значениями) например, ИНДЕКС.
в тексте происходит{= INDEX($A$2:$A$5,MATCH(TRUE,EXACT($B$2:$B$5,D2),0))} если кто-то напишет:Функция=СОВПАД(A4;B4) (Воронеж) не находитсяприблизительное совпадениеВ и С
адрес? :-)Файл удаленУсловное форматирование. Формулавоспримет его, как быть понятно.Если аргумент [тип_сопоставления] явно используем формулу массива

по номеру столбца,{=ИНДЕКС($A$2:$A$5;ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$5;D2);0))} код товара в=C3=pwdEXACT
Проверяет, совпадают ли значения в крайнем левом, а большинство людейА и СHugo- велик размер там числовое значение (что
Sanja не указан или (CTRL+SHIFT+ENTER):Пример 1. Найти позицию содержащего формулу. Например,При поиске в таблице

ячейке, как проверить

В ячейке C6 функция(СОВПАД) имеет следующий в третьей строке столбце. Поэтому мы приблизительное совпадение неГлавная сложность что: Здесь лички нет, — [200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОИСКПОЗ($E2&$D2;банк!$D$2:$D$22&банк!$E$2:$E$22;) правильно).: Чет ерунду какую-то принимает число 0,=B2;A:A;»»));A:A;0);1)’ class=’formula’>
первого частичного совпадения формула в ячейке функция соответствует ли этотEXACT
синтаксис: таблицы (в A4 не можем использовать устраивает. фамилии могут быть а в архивеМОДЕРАТОРЫBlondinka2015Zhukov_K написал. Все. спать для поиска частичного
Функция ПОИСКПОЗ возвращает позицию строки в диапазоне C2, проверяет первыеEXACT код какому-то товару(СОВПАД) сравнивает ячейкиEXACT(text1,text2) содержится пробел между

exceltable.com

Как найти в excel совпадения? У меня есть список и надо найти совпадения в нем ( в ручную не выйдет)

 функцию ВПР. ДляЧтобы убедиться в том,
написаны с дополнительными с программой есть]: Спасибо, друзья!

: , спасибо большое Лучше: Всем спать совпадения текстовых значений элемента в столбце ячеек, хранящих текстовые символы каждой текстовой(СОВПАД) может отличить из Вашего списка?C3СОВПАД(текст1;текст2) «сл» и «ово»)

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

GuestИ чтобы я Вам еще раз! (vikttur хулиганит) могут быть использованы

 A:A, имеющего максимальное значения.
строки, поскольку этот
Aa1В следующем примере кодыи
text1ЛОЖЬ в диапазоне B1:B11 совпадения может иметь
А В С нём мыло :): Что-то похожее есть
без Вас делала? за такие доступные
KL подстановочные знаки («?» значение среди чисел,
Вид исходной таблицы данных: столбец имеет номерот товаров записаны в

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

pwd(текст1) – этоДобро пожаловать на подборку будет использоваться функция серьезные последствия, предположим,Иванов Перт ПетровичHugo в приемах
Раскланиваюсь перед вашим и понятные объяснения!: Формула массива:
— замена одного которые больше числа,Для нахождения позиции текстовой 1.AA1 ячейках B2:B5. Клиент
с учётом регистра: первая текстовая строка. занятий ПОИСКПОЗ. Оно найдено что ищется цена

 Иванов1 Петр Петрович: Да, личка естьvikttur профессионализмом очень признателен!!

=ЕСЛИОШИБКА(ИНДЕКС(Лист2!$B$2:$B$5;ПОИСКПОЗ(9E+307;ПОИСКПОЗ(Лист2!$A$2:$A$5;A2;0)));»-«) любого символа, «*»
указанного в ячейке

 строки в таблицеФункция
, что позволяет подсчитать
может заказать товар,=EXACT(C3,pwd)text2

30 функций Excel за в строке 4. детали с идентификатором ИвановR Петр Петрович на :): Формула массива:DmisavKLvikttur — замена любого B2. Функция ИНДЕКС
используем следующую формулу:EXACT правильное количество отдельно

 введя его код=СОВПАД(C3;pwd)(текст2) – это
 30 дней
Затем функция ИНДЕКС 2345768, но выПомогите позязя))DT3=ИНДЕКС(Потенциальные!A$2:A$10;МАКС(ЕСЛИ(ЕЧИСЛО(ПОИСК(A2;Потенциальные!A$2:A$10));СТРОКА($2:$10)-1)))
: Добрый день,: Добавлю, что ПОИСКПОЗ,: Формула массива: количества символов). возвращает значение, хранящееся
=ПОИСКПОЗ(D2&»*»;B:B;0)-1(СОВПАД) сравнивает два по каждому коду. в ячейке E2.
Если содержимое двух ячеек вторая текстовая строка.(30XL30D). Спасибо, что использует это значение

 перепутали две цифрыSerge_007: здравствуйте

Но если таблицаПрошу помочь!

 ПРОСМОТР, ВПР и=ИНДЕКС(Лист2!$B$2:$B$6;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК(Лист2!$A$2:$A$6;A2));СТРОКА($A$2:$A$6)-1)))Если в объекте данных, в найденной ячейке.Описание аргументов: символа, извлечённых из Другие функции, такиеФормула в ячейке F2 совпадает, включая регистр,Аргументы приняли участие в в качестве аргумента

 и ввели их: …нет примерас excel я большая, то плохоЕсть файл со
 ГПР — по
В таблице добавлена
переданном в качествеРезультат расчетов:D2&»*» – искомое значение, текста при помощи как использует функцию то результатом вtext1
опросе на тему поиска и находит в формулу следующимkonstantinp не дружу, но дело — будет вкладками. На одной сути вариации одной

 пустая строка для аргумента просматриваемый_массив, содержитсяДля поиска ближайшего меньшего состоящее и фамилии, двух функцийCOUNTIF

EXACT обоих случаях будети самых интересных функций. численность населения Воронежа образом:: Извиняюсь очень нужно решить тормозить. список сотрудников, а и той же обхода ошибки. два и больше значения достаточно лишь указанной в ячейкеMID(СЧЁТЕСЛИ), будут рассматривать(СОВПАД), чтобы проверить TRUE (ИСТИНА). Какие-либоtext2 В данной подборке в четвертом столбце=ВПРSerge_007 задачуHugo на другой список операции поиска и

Zhukov_K элементов, соответствующих искомому немного изменить данную B2, и любого(ПСТР). такие коды как код, введённый в различия в форматированииможно ввести как мы сделаем обзор (столбец D). Использованная(2345678;A1:E7;5)

: «В лоб»:необходимо найти совпадения: или сотрудников, которые попали используют одни и
: спасибо огромное за
значению, будет возвращена формулу и ее количества других символов=EXACT(MID($A2,C$1,1),MID($B2,C$1,1)) идентичные и посчитают ячейку E2 на (например, жирный шрифт) текстовые строки или 30 лидирующих функций формула показана в. Формула возвращает цену200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((ПСТР(A2:C2;1;5)=ПСТР(A2;1;5))+(ПСТР(A2:C2;1;5)=ПСТР(B2;1;5))+(ПСТР(A2:C2;1;5)=ПСТР(C2;1;5)))<>3 в столбце А

planetaexcel.ru

Поиск совпадения значения на разных листах (Формулы/Formulas)

там есть поиск в черный список.
те же алгоритмы.
решение!!! но, если
позиция первого вхождения следует также ввести (“*”);=СОВПАД(ПСТР($A2;C$1;1);ПСТР($B2;C$1;1)) их общее количество. точное совпадение с будут проигнорированы.
как ссылки на Excel (по результатам
ячейке A14. на другую деталь,

konstantinp и пронумеровать их
по частичному совпадению,

Случается, что люди ИНДЕКС — едва
с формулой хоть такого элемента.
как массив (CTRL+SHIFT+ENTER):

B:B – ссылка наУрок подготовлен для Вас
В следующем примере список нашим списком.
Если хотя бы одна другие ячейки. В hands

 проведённого опроса), изКраткий справочник: обзор функции
потому что функция
: Спасибо! в столбце Е и по любому из черного списка ли не самая что-то понятно как
Вадим окладниковРезультат поиска: столбец B:B, в командой сайта office-guru.ru повторяющихся значений находитсяФормула введена, как формула буква введена в Excel максимальная длина следующих категорий: ВПР ВПР нашла ближайшее
Тут от первыеvikttur
порядку слов.

excelworld.ru

Найти совпадение данных в двух таблицах в Excel

 попадают на работу быстрая в пересчете она работает, то

: Воспользуйся КонсолидациейФункция имеет следующую синтаксическую котором выполняется поиск;

Источник: http://blog.contextures.com/archives/2011/01/02/30-excel-functions-in-30-days-01-exact/ в диапазоне A2:A11,

массива, нажатием другом регистре, то строки дляТекст
Функции ссылки и поиска число, меньшее или 5 букв проверяет?: Подружитесь — сами

15 запусков free. и их пишем

функция в работе вот формула KLСоздай новый лист запись:0 – поиск точногоПеревел: Антон Андронов

а список уникальных

Ctrl+Shift+Enter результатом в ячейкеEXACT

Информация (справка)

равное указанному (2345678).Serge_007 сможете решать и

Иначе надо писать в списке сотрудников.

с массивами неясна вообще.. зачем

 и на нем=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления]) совпадения.

Автор: Антон Андронов значений в столбце

. C6 будет FALSE(СОВПАД) составляет 32767Поиск и ссылкиИспользование аргумента массива таблицы

 Эта ошибка может: Да не такие задачи

 индивидуальный макрос под Вопрос как выделить
В большинстве случаев там две вложенные создай консолидацию, говоряОписание аргументов:
Из полученного значения вычитаетсяФункция ПОИСКПОЗ в Excel C.{=OR(EXACT($B$2:$B$5,E2))} (ЛОЖЬ).

 символов.В конце этого 30-дневного в функции ВПР

 привести к неправильномуkonstantinp
:)
задачу, если данных цветом ячейку, если СУММЕСЛИ и СЧЁТЕСЛИ внутрь себя ПОИСКПОЗ()

 по русски слияние.искомое_значение – обязательный аргумент,

единица для совпадения используется для поискаФормула в столбце D{=ИЛИ(СОВПАД($B$2:$B$5;E2))}
После того, как пользователь
В справке Excel 2007 марафона, будет опубликованК началу страницы выставлению счета клиенту.

: Спасибо большое)Формула в Е2 много.

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

Примечания функций в порядке, синтаксис формулы и соответствие» указано значение: konstantinp,

Для общего случая:: Таблица огромная - черного списка. Большое правда не в Вас, KL, можете в котором ты
данные логического иПример поиска: большего заданному вSUMPRODUCT
EXACT доступ к изменениюдля функции соответствующем количеству набранных
использование функции ЛОЖЬ или 0,могу предложить уже

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$40;A2)=1;»»;ЕСЛИ(ПОИСКПОЗ(A2;$A$2:$A$40;0)=СТРОКА(1:1);МАКС($E$1:E1)+1;ВПР(A2;$A$1:E1;5;0))) несколько тысяч строк… спасибо за совет! данном случае, т.к.

 объяснить? хочешь найти совпадения ссылочного типов, которыйПример 2. В Excel зависимости от типа(СУММПРОИЗВ) и(СОВПАД) может отличить

 некоторых ячеек таблицы.EXACT в опросе голосов.СОВПАД а точного совпадения готовы макрос:

Если же повторения,ZЧитайте Правила форума.

planetaexcel.ru

поиск совпадений в списке

 количество операций сравненияvikttur
и добавляешь его используется в качестве хранятся две таблицы, сопоставления, указанного в
EXACTAA1 Например, создав формулу(СОВПАД) существует такая

 Тем не менее,в Microsoft Excel. нет, вместо неправильногоhttp://www.excelworld.ru/forum/3-1448-1 как в примере,
: «Таблица огромная - Свою тему создавайте
одинаковое. Это я
: (Неуверенно) А можно
для анализа. Появится критерия поиска (для которые на первый качестве аргумента) значения
(СОВПАД)

от для проверки данных строка: 30 самых популярных
Сравнивает две строки текста значения формула возвращаетkonstantinp находятся только в несколько тысяч строк»

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

Возможно также использовать двойной функций будут рассмотрены и возвращает значение в ячейку строку: Формуляр, соседней строке:
— если этоtomurka
я бы особо

ПОИСКПОЗ(макс_возможное_число;ПОИСКПОЗ(тексты;A3;0)) записями, а рядом
нахождения точного совпадения);

 Было решено сравнить или диапазоне ячеекEXACT, что позволяет найти можно контролировать в знак равно (==) в случайном порядке, ИСТИНА, если они «#Н/Д». Это наилучшее

Прикольный) спасибо!=ЕСЛИ(A2=A3;МАКС($E$1:E1)+1;ЕСЛИ(A2=A1;E1;»»)) для постоянной и: Здравствуйте, уважаемые знатоки не радовался отсутствиюВложенная функция - будет циферка, сколько

planetaexcel.ru

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

просматриваемый_массив – обязательный аргумент, по одному однотипному и возвращает номер(СОВПАД) смотрит, что
правильное название продукта ней изменения значений. вместо функции
так что заходите
в точности совпадают,
решение. В данном
viktturDT3 серьезной работы, то Excel!
ИНДЕКС и ПОИСКПОЗ
массив из ошибок раз данная запись принимающий данные ссылочного
столбцу этих таблиц

 позиции найденного элемента. введено в столбец

 отдельно по каждомуИспользуя такую формулу в

EXACT сюда каждый день,
и ЛОЖЬ —

 случае «#Н/Д» не:
: первый вариант не надо как свои,

Передо мной опять взамен на СЧЁТЕСЛИ

 #Н/Д и позиции повторялась.

 типа (ссылки на на наличие несовпадений.
Например, имеем последовательный ряд C, и проверяет,
коду. Другие функции

 диалоговом окне(СОВПАД), чтобы произвести
чтобы ничего не

 в противном случае. означает, что формула200?'200px':''+(this.scrollHeight+5)+'px');">=ИЛИ(ПСТР(A2;1;5)=ПСТР(B2;1;5);ПСТР(B2;1;5)=ПСТР(C2;1;5);ПСТР(A2;1;5)=ПСТР(C2;1;5))

excelworld.ru

помог в ячейках

Источник

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

1 Сравнение с помощью простого поиска

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

Перейти на главную вкладку табличного процессора.
В группе «Редактирование» выбрать пункт поиска.
Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.

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

2 Операторы ЕСЛИ и СЧЕТЕСЛИ

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

Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
Протянуть формулу до конца столбца.

Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.

Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.

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

Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.

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

3 Формула подстановки ВПР

Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.

С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

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

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

5 Сравнение с выделением совпадений цветом

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

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

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

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

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.

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

Процесс использования надстройки включает такие действия:

Перейти к параметрам электронной таблицы.
Выбрать сначала надстройки, а затем управление надстройками COM.
Отметить пункт Inquire и нажать «ОК».
Перейти к вкладке Inquire.
Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.

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

Читайте также:

5 программ для совместной работы с документами
Как в Экселе протянуть формулу по строке или столбцу: 5 способов

Источник

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование функции СОВПАД в Microsoft Excel.

Описание

Сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ — в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании. Функция СОВПАД позволяет проверить текст, вводимый в документ.

Синтаксис

СОВПАД(текст1;текст2)

Аргументы функции СОВПАД описаны ниже.

Текст1 — обязательный аргумент. Первая текстовая строка.
Текст2 — обязательный аргумент. Вторая текстовая строка.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Первая строка	Вторая строка
слово	слово
Слово	слово
сл ово	слово
Формула	Описание	Результат
=СОВПАД(A2;B2)	Проверяет, совпадают ли значения в первой строке таблицы	ИСТИНА
=СОВПАД(A3;B3)	Проверяет, совпадают ли значения во второй строке таблицы («С» в ячейке A3 введена верхним регистром)	ЛОЖЬ
=СОВПАД(A4;B4)	Проверяет, совпадают ли значения в третьей строке таблицы (в A4 содержится пробел между «сл» и «ово»)	ЛОЖЬ

Нужна дополнительная помощь?

Источник

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

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

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

В этой статье

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

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

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

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

Примеры ИНДЕКСов и СОВПАДЕНИй

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 ( 100) — первое значение, соответствующее капусты.

Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.

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

Для этого используйте функцию ВПР.

Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.

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

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

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

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

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

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

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

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

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

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Для выполнения этой задачи используется функция ГПР.

Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

Если команда подстановка недоступна, необходимо загрузить мастер подстановок надстройка программу.

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

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

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

Найти строки с одинаковыми значениями во всех столбцах таблицы;
Найти строки с одинаковыми значениями в любых двух столбцах таблицы;

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

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

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

В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

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

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

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

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

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

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

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

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

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

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

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

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

Поиск и выделение цветом совпадающих строк в Excel

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

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

Рассмотрим как найти совпадающие строки в таблице:

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

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

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

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

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

Для этого сделаем следующее:

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

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

Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

Не забудьте задать формат найденных дублированных строк.

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

Выбор похожих значений

16.11.2011, 00:22

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

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

Выбор значений таблицы по условию
Всем день добрый. Нужна помощь, в 1 столбце даны наименования, следующие 4 столбца — значения.

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

16.11.2011, 01:572 Вложения

выбор.xls (19.5 Кб, 196 просмотров)

16.11.2011, 11:26 [ТС]316.11.2011, 11:31 [ТС]4 Вложения

похожие коды.zip (38.5 Кб, 109 просмотров)

16.11.2011, 12:485

Формулы массива вводятся нажатием Ctrl+Shift+Enter.

PS Maxbank, скажите, зачем выкладывать тысячи строк?! Почему не обойтись десятком? Даже десяток много. Хватило бы и ДВУХ строк.

Вложения

Maxbank.rar (45.7 Кб, 108 просмотров)

16.11.2011, 13:03 [ТС]6

Спасибо. Извините, не подумал удалить лишние строки.

Добавлено через 5 минут
Или я что-то не понял или все-таки нет нужного результата. В листе1 есть коды, например, 100-06319, 100-06319Р. Их надо вытянуть и вставить в Лист2 напротив кода 100-06319P, например через запятую или каждый совпадающий код в отдельном столбце.

18.11.2011, 02:12 [ТС]7 Вложения

maxbank.xls (25.0 Кб, 204 просмотров)

21.11.2011, 19:19 [ТС]825.01.2012, 21:49 [ТС]925.01.2012, 21:591025.01.2012, 22:09 [ТС]11 Вложения

maxbank_1.xls (13.5 Кб, 40 просмотров)

25.01.2012, 22:241225.01.2012, 23:03 [ТС]1325.01.2012, 23:121425.01.2012, 23:26 [ТС]1525.01.2012, 23:2916

Не знаю. Я вижу только тот файл, который Вы приложили. В нём будет работать.
Давайте файл с ошибкой — я поправлю.

25.01.2012, 23:42 [ТС]17

В файле есть код 203-05058 и 203-05058P. Формула ПОИСК(B798;B10:B1108) (где В798=203-05058) показывает, что совпадений 1. Хотя в тестовом файле maxbank_1.xls все работает правильно. Подсчитывает и результат дает 10.

Добавлено через 59 секунд
Можно выложить файл со всеми строками?

Добавлено через 11 минут
Отправил на мыло.

25.01.2012, 23:4918

Нет. Только проблемные. Вес файла не более 100 Кб в xls.

ЗЫ Видимо это я косякнул. Правильная формула должна быть такая:

26.01.2012, 00:21 [ТС]19

не работает в файле, котром надо.

Добавлено через 3 минуты
СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B798&»*»;B10:B1108))*E10:E1104)
И так тоже.

26.01.2012, 00:2620

Значит что-то не так делаете. Формула рабочая.

И ещё одна формула:

Вложения

maxbank_SUMMIF.xls (21.0 Кб, 52 просмотров)

26.01.2012, 00:26

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

Выбор нужных значений из массива данных
Доброго времени суток, Уважаемые! прошу Вашей помощи/совета/доброго пинка в организации такого.

Выбор уникальных значений из двумерного массива
Добрый день, форумчане! Будьте добры помочь с формулами массива. Стоит следующая задача: .

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

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

Поиск и удаление дубликатов в Excel: 5 методов

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

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

Метод 1: удаление дублирующихся строк вручную

Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.

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

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

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

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

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

Метод 3: использование фильтра

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

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

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

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

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

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

Метод 5: формула для удаления повторяющихся строк

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

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

Давайте посмотрим, как с ней работать на примере нашей таблицы:

Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter:
=ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»») .
Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
- ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
- жмем служебную клавишу F2 на клавиатуре;
- затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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

Заключение

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

Оценка статьи:

5 звезд

Загрузка…

Adblock
detector

Источник

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

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

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

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

На
рисунке – списки писателей. Алгоритм
действий следующий:

Выбрать
ячейку I3
с записью «С. А. Есенин».
Поставить
задачу – выделить цветом ячейки с
такими же записями.
Выделить
область поисков.
Нажать
вкладку «Главная».
Далее
группа «Стили».
Затем
«Условное форматирование»;
Нажать
команду «Равно».

Появится
диалоговое окно:

В
левом поле указать ячейку с I2,
в которой записано «С. А. Есенин».
В
правом поле можно выбрать цвет шрифта.
Нажать
«ОК».

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

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

Ищем в таблицах Excel
все повторяющиеся значения

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

Зайти
в группу «Стили».
Далее
«Условное форматирование».
Теперь
в выпадающем меню выбрать «Правила
выделения ячеек».
Затем
«Повторяющиеся значения».

Появится
диалоговое окно:

Нажать
«ОК».

Программа
ищет повторения во всех столбцах.

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

Удаление одинаковых значений
из таблицы Excel

Способ
удаления неуникальных записей:

Зайти
во вкладку «Данные».
Выделить
столбец, в котором следует искать
дублирующиеся строки.
Опция
«Удалить дубликаты».

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

Список
с уникальными значениями:

Расширенный фильтр: оставляем
только уникальные записи

Расширенный
фильтр – это инструмент для получения
упорядоченного списка с уникальными
записями.

Выбрать
вкладку «Данные».
Перейти
в раздел «Сортировка и фильтр».
Нажать
команду «Дополнительно»:

В
появившемся диалоговом окне ставим
флажок «Только уникальные записи».
Нажать
«OK»
– уникальный список готов.

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

Составим
список уникальных строк, не теряя данные
из других столбцов и не меняя исходную
таблицу. Для этого используем инструмент
Сводная таблица:

Вкладка
«Вставка».

Пункт
«Сводная таблица».

В
диалоговом окне выбрать размещение
сводной таблицы на новом листе.

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

Получаем
упорядоченный список уникальных строк.

Источник

Вопрос от пользователя

Здравствуйте!

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

Запутанно объяснил, но думаю, по фотке задачу поймете (прим.: фотка вырезана цензурой, все-таки личная информация).

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

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

Пример работы с функцией ВПР

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена).

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

Две таблицы в Excel — сравниваем первые столбцы

Как это сделать…

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

=ВПР(A2;$E$1:$F$7;2;ЛОЖЬ)

где:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

Значения из одной таблицы подставлены в другую

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

Дополнения по теме приветствуются!

Ну а у меня на этом пока всё, удачи!

👋

Первая публикация: 25.11.2017

Корректировка: 23.11.2022

donate

dzen-ya

Полезный софт:

Видео-Монтаж

Отличное ПО для создания своих первых видеороликов (все действия идут по шагам!).
Видео сделает даже новичок!

Ускоритель компьютера

Программа для очистки Windows от «мусора» (удаляет временные файлы, ускоряет систему, оптимизирует реестр).

Источник

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

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

Второй аргумент – какой результат появится в ячейке (число или текст), когда значение отвечает указанному условию (да – ИСТИНА).

Третий аргумент – какой результат появится в ячейке (число или текст), когда значение не отвечает указанному условию (нет – ЛОЖЬ).

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

В русифицированной версии Excel аргументы нужно писать через точку с запятой («;»), в англоязычной – через запятую.

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

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

«Ставка»

«=»

«Лист 2»

Эксель формула если ячейка содержит текст то –

В выделенную ячейку вписать формулу =ЕСЛИ(B2>90;»Отлично»;ЕСЛИ(B2>75;»Хорошо»;»Удовлетворительно»)) и нажать на кнопку «Enter». Сначала оператор проверит условие B2>90. ИСТИНА – отобразится «отлично», а остальные критерии не обработаются. ЛОЖЬ – проверит следующее условие (B2>75). Если оно будет правдиво, то отобразится «хорошо», а ложно – «удовлетворительно».

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

Простое сложение в Excel

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

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

Связанные таблицы в Excel: подробная инструкция

Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как «>5» . Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие «Иванов» ;
Диапазон суммирования – массив значений, которые нужно сложить.

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

Excel Если Ячейки Совпадают то Вставить Значение из Другой Ячейки

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

В данной статье я Вам расскажу о трёх способах осуществления этой задачи.

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

Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;

Номер_строки – строка, из которой нужно извлечь значения;

Номер_столбца – столбец, из которого нужно извлечь значение.

Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности.

Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).

=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))

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

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

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

Простыми словами синтаксис функции можно представить так:

=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))

Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.

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

Если на первый взгляд функция выглядит простой и малополезной, то при дальнейшем её изучении Вы узнаете, что с её помощью мы можем перемещаться по листам, транспонировать таблицы, отбирать чётные (нечётные) строки и многое другое. Ведь для нее аргумент Ссылка_на_ячейку – всего лишь текстовая строка, которую можно изменять формулами.

Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.

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

Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

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

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

Excel – просто! Ищем значение на пересечении

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

Источник

Функция ПОИСКПОЗ в 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. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

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

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

=ПОИСКПОЗ(D2&»*»;B:B;0)-1

Описание аргументов:

D2&»*» – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
B:B – ссылка на столбец B:B, в котором выполняется поиск;
0 – поиск точного совпадения.

Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

Пример поиска:

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

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

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

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

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

Как видно, третьи элементы списков не совпадают.

Поиск ближайшего большего знания в диапазоне чисел Excel

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

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

Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

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

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

Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

Результат поиска:

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

Функция имеет следующую синтаксическую запись:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

Описание аргументов:

искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
[тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:

-1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

Скачать примеры ПОИСКПОЗ для поиска совпадения значений в Excel

Примечания:

Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.

Источник