Что такое маска поиска как она записывается excel
Обновлено: 05.07.2024
Если для фильтрации данных требуются сложные условия (например, Type = "Produce" OR Salesperson = "Егоров"), можно использовать диалоговое окно Расширенный фильтр.
Чтобы открыть диалоговое окно Расширенный фильтр, щелкните Данные > Расширенные.
Расширенный фильтр
Продавец = "Егоров" ИЛИ Продавец = "Грачев"
Тип = "Фрукты" И Продажи > 1000
Тип = "Фрукты" ИЛИ Продавец = "Грачев"
(Продажи > 6000 И Продажи < 6500) ИЛИ (Продажи < 500)
(Продавец = "Егоров" И Продажи >3000) ИЛИ
(Salesperson = "Buchanan" AND Sales > 1500)
Продавец = имя со второй буквой "г"
Обзор
Действие команды Дополнительно отличается от действия команды Фильтр в некоторых важных аспектах.
Она отображает диалоговое окно Расширенный фильтр, а не меню "Автофильтр".
Вы вводите расширенные условия в отдельном диапазоне условий на листе над диапазоном ячеек или таблицей, которую нужно отфильтровать. В качестве источника расширенных условий в Microsoft Office Excel используется отдельный диапазон условий в диалоговом окне Расширенный фильтр.
Образец данных
Этот пример данных используется для всех процедур, используемых в этой статье.
Данные включают четыре пустые строки над диапазоном списка, которые будут использоваться в качестве диапазона условия (A1:C4) и диапазон списка (A6:C10). Диапазон условий содержит названия столбцов и по крайней мере одну пустую строку между значениями условий и диапазоном списка.
Чтобы работать с данными, выберите их в таблице ниже, скопируйте, а затем вкопируйте в ячейку A1 нового Excel таблицы.
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
<= (знак «меньше или равно»)
Меньше или равно
Использование знака равенства для ввода текста или значения
Поскольку знак "равно"(=) используется для указать формулу при введите текст или значение в ячейку, Excel его значение. однако это может привести к непредвиденным результатам фильтрации. Чтобы указать оператор сравнения равенства для текста или значения, введите условия в качестве строкового выражения в соответствующей ячейке в диапазоне условий:
=''= ввод ''
где ввод — искомый текст или значение. Например:
Вводится в ячейку
Вычисляется и отображается
Учет регистра
При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. в разделе Условия с подстановочными знаками.
Использование заранее определенных имен
Вы можете назвать диапазон Условия,и ссылка на диапазон автоматически появится в поле Диапазон условия. Вы также можете определить имя База данных для фильтруемого диапазона списка и имя Извлечь для области, в которой нужно вставки строк, и эти диапазоны автоматически появятся в полях Диапазон списка и Копировать в соответственно.
Создание условий с помощью формулы
В качестве условия можно использовать значение, вычисленное с помощью формулы. Обратите внимание на важные моменты, указанные ниже.
Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:
=''= ввод ''
Не используйте название столбца в качестве названия условия. Либо оставьте название условия пустым, либо используйте название, не являющееся названием столбца в диапазоне списка (в последующих примерах: "Среднее арифметическое" и "Точное совпадение").
В формуле, которая используется для условий, необходимо использовать относительную ссылку для ссылки на соответствующую ячейку в первой строке данных.
Все остальные ссылки в формуле должны быть абсолютными.
Несколько условий, один столбец, любое из условий истинно
Логическое выражение: (Продавец = "Егоров" ИЛИ Продавец = "Грачев")
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким условиям для одного столбца, введите условия непосредственно одно под другим в разных строках диапазона условий. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.
Чтобы временно переместить диалоговое окно Расширенный фильтр, пока вы выбираете диапазон критериев, нажмите кнопку Свернуть .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько условий, несколько столбцов, все условия истинны
Логическое выражение: (Тип = "Фрукты" И Продажи > 1000)
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$2.
Чтобы временно переместить диалоговое окно Расширенный фильтр, пока вы выбираете диапазон критериев, нажмите кнопку Свернуть .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько условий, несколько столбцов, любое из условий истинно
Логическое логика: (Тип = "Фрукты" ИЛИ Продавец = "Грачев")
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, где любое условие может быть истинным, введите эти условия в разных строках диапазона условий. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет: При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Чтобы временно отодвинуть диалоговое окно Расширенный фильтр, пока вы выбираете диапазон критериев, нажмите кнопку Свернуть .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько наборов условий, один столбец во всех наборах
Логическое выражение: ( (Продажи > 6000 И Продажи < 6500 ) ИЛИ (Продажи < 500) )
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет: При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$D$3.
Чтобы временно отодвинуть диалоговое окно Расширенный фильтр, пока вы выбираете диапазон критериев, нажмите кнопку Свернуть .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько наборов условий, несколько столбцов в каждом наборе
Логическое выражение: ( (Продавец = "Егоров" И Продажи > 3000) ИЛИ (Продавец = "Грачев" И Продажи > 1500) )
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельных столбцах или строках. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.Чтобы временно переместить диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условия, нажмите кнопку Свернуть .
Используя пример, получим следующий отфильтрованный результат для диапазона списка:
Условия с подстановочными знаками
Логическое выражение: Продавец = имя со второй буквой "г"
Чтобы найти текстовые значения с совпадающими знаками в некоторых из позиций, выполните одно или несколько действий, описанных ниже.
Чтобы найти строки, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства (=). Например, если ввести условие Бел, будут найдены строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
Воспользуйтесь подстановочными знаками.
Используйте
Чтобы найти
Любой символ
Например, если найти "кузнецов" и "смайл"
Любое количество символов
Например, если найти "*-восток", будут "северо-восток" и "Юго-восток"
(тильда), за которой следует ?, * или
Вопроси метка, звездочка или тильда
Например, fy91
? соответствует результат "ан91?"
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
В строках под названиями столбцов введите условия, которым должен соответствовать результат. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет: При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Чтобы временно отодвинуть диалоговое окно Расширенный фильтр, пока вы выбираете диапазон критериев, нажмите кнопку Свернуть .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Николай КАРПЕНКО , канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Работа с шаблонами (или так называемыми масками) — одна из самых полезных функций, когда речь идет о работе с большими базами данных, а критерий поиска информации точно не известен. На практике такая ситуация встречается сплошь и рядом. Элементарный пример. У меня есть реестр выданных (или полученных) налоговых накладных. Реестр большой, несколько тысяч записей. И задача состоит в том, чтобы найти в этом реестре все документы по определенной фирме. Точное название фирмы (как она фигурирует в реестре) я не помню: может это «ООО "Фора"» или «ТОВ "Фора"», как-то так. Но тот факт, что в названии есть слово «Фора», я знаю наверняка. А это означает, что для поиска информации о фирме «Фора» мне нужно использовать не точное название предприятия, а некоторый шаблон. В данном случае он может выглядеть так: «*Фора*». В этом шаблоне на месте символа «*» может стоять любая строка произвольной длины. А сам шаблон описывает названия фирм, где вначале идет любой текст, затем есть слово «Фора», а за ним — снова произвольный текст. Под такой шаблон попадают, например, такие названия: «ТОВ "Фора"», «ООО "Фора"», «Фирма "Фора"», «"Фора", ltd» и т. д. Иными словами, шаблон «*Фора*» задает не точное, а приблизительное название предприятия для поиска его в реестре. А это именно то, что нам нужно. Остается реализовать такой поиск на практике.
Здесь ситуация такова. В программе Excel (а речь идет именно о ней!) есть разные способы решения этой задачи. Например, поиск и выбор данных можно организовать при помощи автофильтра. В этом случае шаблон для поиска нужно ввести в настройках фильтра, и такая возможность в программе Excel предусмотрена. Другое дело, когда нужно не просто выбрать данные, а еще и обработать их. Для примера, обратимся к базе данных, фрагмент которой показан на рис. 1. Предположим, мы хотим написать формулу, которая просуммирует все значения из колонки «Сумма с НДС» для контрагента «ТОВ "Фора"». Нам известно, что название этого контрагента в базе может отличаться (например, в названии может быть лишний пробел). Но слово «Фора» в нем есть всегда, и оно является ключевым для организации поиска.
Поэтому для решения задачи нам понадобится функция, которая умеет сравнивать текстовые строки по заданному шаблону. И тогда, при наличии такой функции, применить любой инструмент обработки данных уже не составит труда.
Создать функцию для сравнения строк совсем несложно, если воспользоваться возможностями языка Visual Basic for Application (VBA) программы MS Excel. Все, что нам придется сделать, — это открыть редактор Visual Basic и написать в нем буквально десяток строк. А в результате мы сможем существенно расширить свои возможности по обработке данных и заодно закрыть довольно-таки большой пробел в работе с текстом, который есть в стандартных средствах MS Excel.
Создаем функцию проверки текста по шаблону
Итак, мы решили, что для создания функции сравнения строк нам нужно написать небольшую программу на языке Visual Basic, который входит в состав MS Excel. Разумеется, программирование на VBA не является профильной задачей бухгалтера. Поэтому мы не будем углубляться в тонкости работы программы. Нам достаточно в общих чертах знать, как она выглядит и как вставить ее в документ MS Excel. Кроме того, полный текст функции мы выложим на сайте редакции, где вы сможете скачать его без каких-либо ограничений. Приступим.
Если вы работаете с Excel 2010, то первое, что нужно сделать для работы с VBA, — это включить вкладку «Разработчик» (по умолчанию эта возможность отключена). Для этого делаем так.
1. Открываем программу Excel. Загружаем файл с базой данных, как на рис. 1. В нашем примере она называется «РеестрНН.xls».
2. Вызываем меню «Файл → Параметры» и выбираем раздел «Настройка ленты». Откроется окно, изображенное на рис. 2.
3. В группе параметров «Настройка ленты:» включаем флажок слева от строки «Разработчик» (рис. 2).
4. В окне настроек нажимаем «ОК». В составе основного меню Excel 2010 появится пункт «Разработчик» (рис. 3).
5. Щелкаем левой кнопкой на разделе «Разработчик» главного меню. Лента примет вид, как на рис. 3.
6. В группе «Код» щелкаем на иконке «Visual Basic» (рис. 3). Откроется окно, изображенное на рис. 4. Это и есть редактор Visual Basic.
В левой части окна показана структура проекта для текущей рабочей книги. В этот проект нам и нужно добавить текст своей функции.
Важно! В Excel 2003 для вызова Visual Basic войдите в меню «Сервис → Макрос → Редактор Visual Basic». На экране появится окно, изображенное на рис. 4.
Теперь наша задача — вставить в документ «РеестрНН.xls» текст функции для сравнения строк. Делаем так.
2. Сохраняем результат на жесткий диск.
3. Открываем скачанный файл в любом текстовом редакторе. Можно воспользоваться программой «Блокнот» или процессором MS Word. Перед вами должен появиться такой текст:
′ Функция для сравнения текстовых строк по маске
Function MskCmp(Tekct As String, Maska As String, Reg As Boolean)
If Not Reg Then
If Tekct Like Maska Then
4. Нажимаем «Ctrl+A» (выделить все).
5. Комбинацией «Ctrl+C» копируем текст в буфер обмена.
6. Переходим в окно Visual Basic (рис. 4).
7. Вызываем меню «Insert → Module». Откроется окно для добавления текста программы.
8. Нажимаем «Ctrl+V» (вставляем текст из буфера обмена).
9. Нажимаем кнопку «Сохранить» и закрываем редактор Visual Basic. Функция добавлена.
Прежде чем идти дальше посмотрим, что мы получили на самом деле. В результате работы с Visual Basic у нас появится дополнительная функция с именем «MskCmp()». У нее три параметра: текст (или ячейка с текстом), который мы будем проверять на соответствие шаблону, второй параметр — это сам шаблон. И, наконец, последний, третий параметр — это признак контроля регистра символов «Reg». Если этот параметр равен «0», то регистр функция контролировать не будет. Если параметр равен «1», то поиск будет выполняться с учетом регистра. То есть синтаксис нашей функции выглядит так: «=MaskCompare(Tekct; Maska; Reg), где «Tekct» — анализируемый текст, «Maska» — шаблон, «Reg» — признак контроля регистра символов. Главным и единственным критерием для поиска данных у функции «MskCmp()» является шаблон «Maska». Он может состоять из любых символов и специальных знаков (или так называемых символов подстановки). К таким символам относятся:
«*» — означает, что на месте этого знака может находиться произвольное количество любых символов;
«?» — на месте этого знака может стоять один символ;
«[Список_символов]» — такая запись в шаблоне заменяет любой символ из указанного списка;
«[!Список_символов]» — означает, что в строке могут находиться любые символы, кроме перечисленных в списке.
Вот примеры нескольких шаблонов, которые можно использовать в функции «MskCmp()»:
«. » — слова из 5 букв;
«м*н» — слова, которые начинаются на «м» и заканчиваются на «н»;
«*[аостр]*» — все слова, содержащие хотя бы одну из букв «а», «о», «с», «т», «р»;
«*[abcdefghijklmnopqrstuvwxyz]*» — все слова, в которых есть английские буквы.
Применяем функцию MskCmp() для обработки базы данных
Функция для проверки текста у нас есть. Можно использовать ее для решения нашей задачи. Напомню, что мы хотим в базе данных на рис. 1 посчитать итоговые значения для контрагентов, в названии которых есть слово «Фора». Делаем так.
1. Открываем документ с базой «РеестрНН.xls» (рис. 1).
2. Перед началом базы данных добавляем рабочую строку. Она будет первой на рабочем листе.
3. Заполняем ячейки рабочей строки, а именно: в «B1» вводим текст «Фирма:». В ячейку «C1» вводим название контрагента, для которого нужно посчитать итоговые значения в базе данных. В нашем примере — это фирма «Фора».
4. Становимся на ячейку «J2», вводим название заголовка «Пр». В этой колонке мы напишем признак суммирования.
5. Становимся на ячейку «J4», щелкаем на значке «fx». Откроется окно «Мастер функций», изображенное на рис. 5.
6. В этом окне щелкаем на списке «Категория:», выбираем значение «Определенные пользователем». В списке «Выберите функцию:» появится список доступных функций пользователя для текущего документа.
7. В этом списке выбираем вариант «MskCmp» и нажимаем «ОК». Откроется окно «Аргументы функции», изображенное на рис. 6.
8. В нем заполняем параметры, как показано на рисунке. Здесь адрес «E4» — это ссылка на ячейку с наименованием предприятия. Формула «"*"&$C$1&"*"» формирует маску для поиска, используя название контрагента из ячейки «C1». Сейчас в «C1» записано значение «Фора». Поэтому формула «"*"&$C$1&"*"» сформирует маску «*Фора*», которая выберет все предприятия, в названии которых встречается текст «Фора». Регистр при поиске мы не проверяем, поэтому параметр «Reg» вводим равным «0».
9. В окне «Аргументы функции» нажимаем «ОК». В ячейке «J4» появится формула «=MskCmp(E4;"*"&$C$1&"*";0)», а ее значение в ячейке «J4» будет «Истина». Все верно: в «E4» записано название «ТОВ "Фора"» и функция «MskCmp()» это правильно определила.
10. Копируем формулу вниз по колонке «J» на всю высоту базы данных.
Все, что нам остается, — добавить формулы суммирования с учетом значений в колонке «J». Сделать это можно при помощи функции «Сумм если()». Делаем так.
1. Становимся на ячейку «G1».
2. Вводим формулу выборочного суммирования: «=Суммесли($J4:$J10000;ИСТИНА;G4:G10000)». Эта формула просматривает значения блока «$J4:$J1000» (считаем, что в базе будет не более 1000 записей). Если в какой-то ячейке блока она находит значение «Истина», формула берет содержимое из соответствующей ячейки блока «G4:G10000» и добавляет его к общей сумме. В результате эта формула посчитает сумму значений из колонки «G», которые отмечены в столбце «J» как «Истина». Для примера на рис. 7 мы таким образом получим итоги по контрагенту «Фора».
3. Копируем содержимое «G1» в буфер обмена и вставляем содержимое из буфера в ячейки «H1» и «I1». Результат нашей работы показан на рис. 7.
Что мы получили на самом деле? В ячейке «C1» нашей таблицы записано приблизительное название контрагента. Мы использовали это название для формирования маски. В колонке «Пр» записана функция «MskCmp()», которая сверяет названия предприятий из колонки «E» с маской для поиска. Если текст из «C1» содержится в названии предприятия, в соответствующей ячейке колонки «Пр» появится значение «ИСТИНА». В противном случае там будет «ЛОЖЬ». Теперь, имея такой признак, получить итоги уже не составит труда.
Функцию «MskCmp()» можно с успехом использовать для анализа и проверки правильности данных. Такая задача очень актуальна при работе с большими таблицами, к которым планируется применить инструменты обработки данных и подведения итогов. Вот всего пара ситуаций, которые часто встречаются в практической работе.
Пример 1. В реестре, изображенном на рис. 7, нужно проверить правильность наименований в колонке «E». Мы хотим убедиться, что все названия предприятий набраны русскими буквами. Дело в том, что визуально текст «ТОВ "Фора"» и «ТОВ "Фopа"» выглядит одинаково. Но во втором случае буквы «o» и «p» введены в латинице. И, разумеется, при подведении итогов, при сортировке или другой обработке данных эти отличия компьютер «увидит». И при обработке базы будет получен неверный результат. Решить проблему можно так.
1. Находим свободный столбец. Пусть это будет колонка «К». Озаглавим ее как «Контроль».
2. В ячейку «K2» вводим формулу «=MaskCompare(E4;"*[A-z]*";0)» и копируем ее на всю высоту таблицы. Теперь все как на ладони. Все строки, помеченные в колонке «K» значением «Истина», содержат в названии предприятия латинские символы.
Пример 2. В базе данных на рис. 2 нужно проверить, чтобы все коды ИНН содержали только числа. Сделать такую проверку для содержимого ячейки «F4» поможет формула «=MaskCompare(F4;"*[0123456789]";0)». Далее копируем ее на всю высоту таблицы — и задача решена. Все строки, которые будут отмечены значением «ЛОЖЬ», содержат ошибку в коде ИНН.
Пример 3. Лишние пробелы в названиях и других ключевых полях базы данных всегда являются источником ошибок. Чтобы проверить наличие двух пробелов в колонке «Наименование» реестра налоговых накладных, можно воспользоваться формулой «=MaskCompare(E4;"* *";0)». В этой формуле маска «"* *"» — это символ «*», затем идут два пробела, а после них опять символ «*». Заполняем этой формулой рабочую колонку и смотрим на результат. Строки, где в рабочей колонке появятся значения «Истина», содержат в названии предприятия лишний пробел.
Как видите, использование масок и шаблонов со специальными символами открывает широчайшие возможности по обработке данных в программе Excel. Все зависит от фантазии и творческого подхода к решению проблемы.
Предположим, что вы хотите найти расширение телефона сотрудника, используя его номер эмблемы или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.
Что необходимо сделать
Точное совпадение значений по вертикали в списке
Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.
Примеры ВРОТ
Дополнительные сведения см. в этой информации.
Примеры индексов и совпадений
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))
Формула ищет в C2:C10 первое значение, соответствующее значению "Ольга" (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению "Ольга".
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Для этого используйте функцию ВЛВП.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
Дополнительные сведения см. в этой информации.
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.
Примечание: Используйте этот подход, если данные в диапазоне внешних данных обновляются каждый день. Вы знаете, что цена находится в столбце B, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортировали по алфавиту.
C1 — это левые верхние ячейки диапазона (также называемые начальной).
MATCH("Оранжевая";C2:C7;0) ищет "Оранжевые" в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.
1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.
Точное совпадение значений по горизонтали в списке
Для этого используйте функцию ГГПУ. См. пример ниже.
Г ПРОСМОТР ищет столбец "Продажи" и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Для этого используйте функцию ГГПУ.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )
В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.
Щелкните ячейку в диапазоне.
На вкладке Формулы в группе Решения нажмите кнопку Под поиск.
Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов.
Загрузка надстройки "Мастер подстройок"
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.
Описание
Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы "n" в слове "printer", можно использовать следующую функцию:
Эта функция возвращает 4, так как "н" является четвертым символом в слове "принтер".
Можно также находить слова в других словах. Например, функция
возвращает 5, так как слово "base" начинается с пятого символа слова "database". Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.
Эти функции могут быть доступны не на всех языках.
Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ПОИСК и ПОИСКБ описаны ниже.
Искомый_текст Обязательный. Текст, который требуется найти.
Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Замечание
Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (
Если аргумент начальная_позиция опущен, то он полагается равным 1.
Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой "МДС0093.МужскаяОдежда". Чтобы найти первое вхождение "М" в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — "МДС0093"). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Читайте также: