Спустя катастрофически долгий период времени с момента публикации моего последнего поста я решил поделиться супер классной формулой Excel, на мой взгляд, когда вы узнаете о ней, вы начинаете задаваться вопросом, как я смог жить без нее. Но, надо сказать, авторство его создания не мое, а скорее всего принадлежит англоязычному ресурсу, о котором я расскажу ниже.
Любой, кто более или менее часто работает с массивами данных в Excel, почти наверняка знает функцию ВПР (см. Мою статью) или функцию ИНДЕКС + ПОИСК, которая решает довольно распространенную проблему объединения двух наборов данных из некоторых совпадающих значений. Использование этих функций, по сути, решает проблему сопоставления и объединения данных в 90% случаев. Если не один, но — объединяемые данные должны точно совпадать. Но бывают случаи, когда требуется перекрытие. Да, VLOOKUP имеет приблизительный поиск совпадений, но он работает недостаточно прозрачно, и поэтому может быть непросто предсказать, почему было найдено похожее слово, а не другое. Как вы понимаете, я начал эту прелюдию неспроста, но чтобы рассказать вам, как решить такую проблему с помощью Excel.
Предположим, у нас есть список товаров, которые нужно как-то сгруппировать:
Проверяем условие для полного совпадения текста.
Мы организуем проверку работоспособности доставки с помощью обычного оператора сравнения «=».
Читайте также: Hiper ep6600 black cat
= ЕСЛИ (G2 = «Готово»; ИСТИНА; ЛОЖЬ)
В этом случае не имеет значения, в какой регистр записываются значения в вашей таблице.
Если вас интересует точное соответствие текстовых значений с учетом регистра, мы рекомендуем использовать функцию EXACT () вместо оператора «=». Убедитесь, что два текстовых значения идентичны, учитывая заглавные буквы отдельных букв.
Вот как это может выглядеть на примере.
Обратите внимание, что если мы используем текст в качестве аргумента, он должен быть заключен в кавычки.
ЕСЛИ + СОВПАД
Если нас интересует полное совпадение текста с заданным условием, включая регистр его символов, то оператор «=» нам не поможет.
Но мы можем использовать ТОЧНУЮ функцию).
Функция ПОИСКПОЗ сравнивает два текста и возвращает ИСТИНА, если они точно совпадают, и ЛОЖЬ, если есть хотя бы одно различие, включая регистр букв. Поясним возможность его использования на примере.
Формула проверки выполнения заказа в столбце H может выглядеть так:
Как видите, варианты «ГОТОВО» и «ГОТОВО» не считаются правильными. Учитываются только завершенные игры. Будет полезно, если важно точное написание текста, например в артикулах товара.
Использование функции ЕСЛИ с частичным совпадением текста.
Выше мы видели, как использовать текстовые значения в функции ЕСЛИ. Но часто бывает, что необходимо определить не полное, а частичное совпадение текста с эталоном. Например, нас интересует город, но его название совершенно не важно.
Первое, что приходит в голову, — это использовать подстановочный знак «?» и «*» (вопросительный знак и звездочка). Однако, к сожалению, этот простой метод здесь не работает.
ЕСЛИ + ПОИСК
В этом нам поможет функция ПОИСК. Определяет, где в тексте находятся нужные символы. Его синтаксис следующий:
= ПОИСК (what_search, where_search, start_with_which_search_character)
Если третий аргумент не указан, поиск начинается с начала, с первого символа.
Функция ПОИСК возвращает либо номер позиции, с которой искомые символы появляются в тексте, либо ошибку.
Но нам нужны логические значения для использования в функции ЕСЛИ.
Здесь на помощь приходит еще одна функция EXCEL: ISNUMBER. Если его аргумент — число, он вернет логическое значение ИСТИНА. Во всех остальных случаях, в том числе, если его аргумент возвращает ошибку, ISNUMBER вернет FALSE.
В результате наше выражение в ячейке G2 будет выглядеть так:
Еще одно важное уточнение. Функция ПОИСК не чувствительна к регистру.
ЕСЛИ + НАЙТИ
Если для нас важны строчные и прописные буквы, то вместо этого нам придется использовать функцию НАЙТИ (в английской версии — НАЙТИ).
Читайте также: Как заблокировать входящий номер на смартфоне
Его синтаксис очень похож на функцию ПОИСК: что мы ищем, где мы ищем, начиная с какой позиции.
Давайте изменим нашу формулу в ячейке G2
То есть, если вам важен случай, просто замените ПОИСК на НАЙТИ.
Таким образом, мы увидели, что, казалось бы, простая функция ЕСЛИ на самом деле предлагает нам много возможностей для работы с текстом.
Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего значения (меньше или больше указанного значения, в зависимости от типа совпадения, указанного в качестве аргумента), указанного в массиве или диапазоне ячеек, и возвращает числовую позицию найденный элемент.
Примеры использования функции ПОИСКПОЗ в Excel
Например, у нас есть последовательный ряд чисел от 1 до 10, записанный в ячейках B1: B10. Функция = ПОИСКПОЗ (3; B1: B10; 0) вернет число 3, поскольку значение поиска находится в ячейке B3, которая является третьей от источника (ячейка B1).
Эта функция полезна в тех случаях, когда вы хотите вернуть не само значение, содержащееся в желаемой ячейке, а ее координату относительно рассматриваемого диапазона. Если для констант используются массивы, которые могут быть представлены как массивы элементов «ключ» — «значение», функция ПОИСКПОЗ возвращает значение ключа, которое не указано явно.
Например, массив содержит элементы, которые могут быть представлены как: 1 — «виноград», 2 — «яблоко», 3 — «груша», 4 — «слива», где 1, 2, 3, 4 — ключи и названия плоды — это ценности. Тогда функция = MATCH («apple»; ; 0) вернет значение 2, которое является ключом второго элемента. Подсчет ведется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.
Функция ПОИСКПОЗ редко используется сама по себе. Рекомендуется использовать вместе с другими функциями, такими как ИНДЕКС.
Формула для поиска неточного совпадения текста в Excel
Пример 1. Найдите положение первого частичного совпадения строки в диапазоне ячеек, в которых хранятся текстовые значения.
Просмотр исходной таблицы данных:
Чтобы найти позицию текстовой строки в таблице, используйте следующую формулу:
Из полученного значения вычитается единица, чтобы результат соответствовал идентификатору записи в таблице.
Сравнение двух таблиц в Excel на наличие несовпадений значений
Пример 2. В Excel хранятся две таблицы, которые на первый взгляд выглядят одинаково. Было решено сравнить столбец того же типа в этих таблицах на предмет несоответствий. Реализуйте способ сравнения двух диапазонов ячеек.
Просмотр таблицы данных:
Чтобы сравнить значения в столбце B: B со значениями в столбце A: A, используйте следующую формулу массива (CTRL + SHIFT + ENTER):
Функция ПОИСКПОЗ ищет логическое значение ИСТИНА в массиве логических значений, возвращаемых функцией ИСПОЛНИТЕЛЬНАЯ (сравнивает каждый элемент диапазона A2: A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ находит значение ИСТИНА, будет возвращена позиция его первого вхождения в массиве. Функция UND вернет FALSE, если она не принимает в качестве аргумента значение ошибки # N / A. В этом случае функция ЕСЛИ вернет текстовую строку «есть», в противном случае — «нет».
Читайте также: Прошивка Huba hb 9818
Чтобы вычислить остальные значения, перетащите формулу вниз из ячейки C2, чтобы использовать функцию автозаполнения. В результате получаем:
Как видите, третьи позиции в списках не совпадают.
Поиск ближайшего большего знания в диапазоне чисел Excel
Пример 3. Находит ближайшее наименьшее число 22 в диапазоне чисел, хранящемся в столбце электронной таблицы Excel.
Просмотр исходной таблицы данных:
Для поиска ближайшего наибольшего значения, указанного во всем столбце A: A (ряд чисел может быть пополнен новыми значениями), воспользуемся формулой массива (CTRL + SHIFT + ENTER):
ПОИСКПОЗ возвращает позицию элемента в столбце A: A, имеющего наибольшее значение из чисел, превышающих число, указанное в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.
Чтобы найти ближайшее меньшее значение, вам просто нужно немного изменить эту формулу, и ее также следует ввести как массив (CTRL + SHIFT + ENTER):
Особенности использования функции ПОИСКПОЗ в Excel
Функция имеет следующий синтаксис:
= ПОИСКПОЗ (искомое_значение; искомое_массив; [тип_соответствия])
- lookup_value — обязательный аргумент, который принимает текстовые, числовые значения, а также логические и справочные данные, которые используются в качестве критерия поиска (для сопоставления значений или для поиска точного совпадения);
- lookup_array — это обязательный аргумент, который принимает данные ссылки на тип (ссылки на диапазон ячеек) или константу массива, в которой выполняется поиск позиции элемента в соответствии с критерием, заданным первым аргументом функции;
- [match_type] — необязательный числовой аргумент, указывающий, как искать в диапазоне ячеек или массиве. Может принимать следующие значения:
- -1 — Находит наименьшее ближайшее значение, заданное параметром lookup_value, в порядке убывания массива или диапазона ячеек.
- 0 — (по умолчанию) Находит первое значение в массиве или диапазоне ячеек (не обязательно отсортированных), которое точно соответствует значению, переданному в качестве первого аргумента.
- 1 — Найдите ближайшее наибольшее значение, заданное первым аргументом в возрастающем массиве или диапазоне ячеек.
- Если текстовая строка была передана как аргумент lookup_value, функция ПОИСКПОЗ вернет позицию элемента в массиве (если есть) без учета регистра. Например, линии «МоскВа» и «Москва» равнозначны. Чтобы различать регистры, вы также можете использовать функцию EXACT.
- Если поиск с использованием этой функции не дал результатов, будет возвращен код ошибки # N / A.
- Если аргумент [match_type] не указан явно или принимает число 0, можно использовать подстановочные знаки для поиска частичного совпадения текстовых значений («?» — заменяет любой символ, «*» — заменяет любое количество символов).
- Если объект данных, переданный в качестве аргумента lookup_array, содержит два или более элементов, которые соответствуют значению поиска, будет возвращена позиция первого вхождения этого элемента.
