Подстановочные знаки в условиях

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

Подстановочные знаки позволяют искать частичные совпадения текста в SUMIF, COUNTIF и AVERAGEIF. Всю работу выполняют два специальных символа: звёздочка и вопросительный знак.

Их всего два:

• * звёздочка соответствует любому количеству символов, в том числе ни одному
• ? вопросительный знак соответствует ровно одному символу

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

Поставьте звёздочку в конце, чтобы разрешить любое окончание. Чтобы подсчитать все коды в A2:A10, начинающиеся с AB, добавьте звёздочку после AB.

Это соответствует значениям AB, AB1, ABXYZ и любым другим, начинающимся с этих двух букв. Звёздочка заменяет всё, что находится после них.

Поставьте звёздочку в начале, чтобы разрешить любое начало. Чтобы просуммировать значения в B2:B10, где подпись в столбце A заканчивается словом «Север», поставьте перед ним звёздочку.

Так будут найдены «Крайний Север», «Верхний Север» и просто «Север». Звёздочка поглощает всё, что находится перед этим словом.

Поставьте звёздочки по обеим сторонам текста, чтобы искать его в любом месте ячейки. Чтобы подсчитать должности в A2:A10, содержащие слово «менеджер»:

Так будут найдены «Менеджер по продажам», «Менеджер по IT» и «Помощник менеджера». Две звёздочки разрешают любой текст до и после нужного слова.

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

"A??" соответствует A12 и A99, но не A1 и не A123, потому что длина должна быть ровно три символа.

Чтобы создать условие «начинается с» на основе ячейки, объедините ячейку со звёздочкой с помощью амперсанда. Если D1 содержит AB, найдите всё, что начинается с этого текста.

& присоединяет звёздочку к значению в D1, создавая "AB*". Теперь частичное совпадение задаётся ячейкой, а не жёстко заданным текстом.

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

Здесь значение в D1 обрамляется как "*value*", поэтому изменение D1 сразу меняет искомый формулой текст. Это основа простой сводки с поиском по мере ввода.

Что делать, если в данных действительно содержится * или ? и нужно найти этот символ буквально? Поставьте перед ним тильду ~.

Так "~?" находит буквальный вопросительный знак, а "~*" — буквальную звёздочку. Тильда сообщает электронной таблице, что следующий символ нужно воспринимать как обычный текст.

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

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

Представьте, что в столбце A находятся коды SALES-01, SALES-02 и HR-01, а в столбце B — бюджеты. Чтобы просуммировать весь отдел продаж, найдите любой код, начинающийся с SALES и продолжающийся любыми символами.

Одна SUMIF с условием "SALES*" сразу объединяет все подкатегории, поэтому перечислять их по отдельности не нужно. Замените префикс, чтобы получить сводку по другому отделу.

Теперь Вы умеете сопоставлять частичный текст в математических функциях IF. Помните:

• * соответствует любому количеству символов; ? — ровно одному.
• Используйте "AB*" для совпадения в начале, "*North" — в конце, а "*Manager*" — для поиска внутри текста.
• Объединяйте ячейку с помощью амперсанда, например D1&"*".
• Экранируйте буквальный * или ? с помощью тильды и помните: подстановочные знаки работают только с текстом.

На этом условные вычисления с SUMIF, COUNTIF и AVERAGEIF завершены.