30 функций Excel за 30 дней: День первый – СОВПАД

Принимайте участие в челлендже «30 функций Excel за 30 дней». Здесь вы найдёте информацию о 30 лучших функциях Excel из категорий:

1. Текстовые данные.
2. Работа с информацией.
3. Поиск и ссылки.

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

Функция первая: СОВПАД

Данная функция необходима, чтобы проверить, совпадают ли текстовые данные в строках. Следует отметить, что регистр может повлиять на результат, а вот курсив, кегль и т.д. (другими словами, форматирование) – нет. Если данные идентичны, то в итоге вы получите значение ИСТИНА, а в противном случае – ЛОЖЬ.

В каких случаях не обойтись без данной функции

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

1. Проверить данные для последующего разрешения внесения изменений в документ.
2. Задать обязательный ввод данных в верхнем регистре.
3. Определить соответствие кода товара.
4. Найти точное совпадение в списке.
5. Узнать количество точных совпадений.
6. Определить различия между ячейками.

Синтаксис функции СОВПАД

Для использования функции в строке формул нужно ввести:

СОВПАД(аргумент1;аргумент2)

Аргумент1 – первая строка.

Аргумент2 – вторая строка.

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

Ловушки при использовании функции СОВПАД

В справочном разделе Excel 2007 написано, что функцию можно активировать другим способом. «Введите двойной знак равенства (==) для сравнения данных. Так, =C1==D1 даст такой же результат, как и СОВПАД(C1;D1)».

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

Пример №1: Проверка правильности пароля

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

На расположенном ниже скриншоте пароль вписан в ячейку C2. Её можно найти на листе под названием AdminData, который другие люди не видят.

На листе Ex01 другой пользователь вписывает комбинацию в ячейку C3.

В C4 необходимо сравнить данные из C3 со значением из поля «pwd»:

=C3=pwd

При помощи функции СОВПАД(), сравниваем данные из C3 со значением из поля «pwd»:

=СОВПАД(C3;pwd)

Если они будут идентичными, то в C5 появится слово ИСТИНА. Снова следует отметить, что форматирование (кегль, цвет и т.д.) будет игнорироваться. 

Однако, при несовпадении значений в ячейке C5 отобразится результат ЛОЖЬ.

Пример №2: Разрешить внесение изменений в данные 

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

Сначала пользователю необходимо ввести верный пароль в ячейку C3. Если он совпадёт с данными из поля «pwd», то ему можно будет вписывать новые значения в C5. Кроме того, можно установить дополнительные условия для данной ячейки. В данном случае значение должно быть выше нуля и ниже 0,1:

=И(СОВПАД(С3;PWD);С5>0;С5<0,1)

Пример №3: Обязательное написание данных только заглавными буквами 

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

В C2 используется проверка значений по данной формуле:

СОВПАД(С2;ПРОПИСН(С2))

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

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

Иногда может понадобиться не просто сравнить 2 ячейки, а найти совпадающие значения в большом списке. Например, вам нужно убедиться, что введённая комбинация соответствует определённому коду товара. 

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

Чтобы найти совпадающие данные, в ячейке F2 необходимо активировать функцию СОВПАД().

Для использования формулы массива нажмите сочетание клавиш Ctrl + Shift + Enter:

{=ИЛИ(СОВПАД($B$2:$B$5;E2))}

Пример №5: Вернуть необходимое название по предоставленным данным

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

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

В ячейке B2 одновременно взаимодействуют следующие функции:

• СОВПАД() проверяет значение, введённое в поле D2. Затем она ищет ячейку с точно таким же кодом.
• Затем ПОИСКПОЗ() определяет строку с верным результатом.
• ИНДЕКС() возвращает значение во второй строке A2: A5.

Пример №6: Определение количества соответствий в списке

Снова следует отметить, что функция СОВПАД() различает Ci3 и CI3, что поможет вам рассчитать правильное количество совпадений. Другие подобные функции, например СЧЁТЕСЛИ(), обработают данные коды как одинаковые и отобразят их общее количество. 

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

Для достижения цели необходимо вписать 2 функции в столбец D:

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

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

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

Иногда вам может понадобиться узнать, какие символы в двух строках не совпадают. В этом также поможет функция СОВПАД(). В данном примере на каждой строке расположены 6 символов, а цифры от 1 до 6 используются в качестве заголовков. 

В ячейке E2 одновременно используются 2 функции:

• Функция ПСТР() возвращает какой-либо символ из столбца А или B, при этом опираясь на указанное в заголовке число. В данном случае, в формуле в ячейке C2 на соответствие должен быть проверен первый символ во всех строках, поскольку в поле C1 число соответствует единице. 
• СОВПАД() поможет вам сравнить символы, определённые функцией ПСТР(). 

=СОВПАД(ПСТР($A2;C$1;1);ПСТР($B2;C$1;1))

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