Vba excel поиск файлов по маске
Обновлено: 07.07.2024
Печально. Теперь не удивительно, что я ничего не нашёл.
Буду что-нибудь придумывать.
Тем более что у меня область поиска текста в файле ограничена первой сотней ячеек во втором столбце.
Думаю, что справлюсь.
Спасибо за информацию. (в том числе и по рекурсии)
Столкнулся с интересной проблемой при рекурсии.
Если процесс запущен на логическом диске, всё работает нормально.
Если-же запустить в сетевой папке, то начинается путаница с путём вложенных папок.
iFaile и iPath должны быть почти одинаковыми.
Однако почему-то возникает странное несоответствие.
И хотя при этом все файлы в подкаталогах находятся правильно, получить правильное полное имя для iFaile я почему-то не смог.
Последний раз редактировалось КаМММ; 05.02.2010 в 18:42 .
Прикладываю то, что пытался сделать.
За кривизну моего програмного кода прошу не ругать.
Пишу как умею.
Вся система должна просматривать каталог с файлами, и если в каком-то из них во втором столбце находится искомый текст,
то текст ячейки вместе с названием и путём файла вписывается на лист.
Искомый текст берётся из ячейки C1
Вся система работает без открытия просматриваемых файлов. (прописывается ссылка на ячейку в искомом файле, и обновляется значение.)
Вот какраз с формулой - ссылкой и возникают проблемы когда файл находится в сети.
Последний раз редактировалось КаМММ; 05.02.2010 в 18:53 .
Добрый (день вечер ночь Нужное прочитать)
Вот что получилось в итоге всех мучений.
Жаль у меня нет экселя 2007, чтобы протестировать по полной программе.
Если нужен файл могу тоже положить, хотя в нём только две ячейки для масок поиска.(файлы для поиска с искомой информацией в столбце "В" каждый пусть сам себе делает)
Вопрос: Как сделать быстрее работу этого кода.
И извечный: Почему майкрософт такой ГАД? Убрал полезную функцию, из за чего весь этот код пришлось писать!
Последний раз редактировалось КаМММ; 11.02.2010 в 23:09 . Причина: Не поздаровкался сразу.
Ещё раз всем здравствуйте.
По результатам проверки оказывается, что макрос находит не все строки, или не во всех файлах.
Помогите ошибку в коде найти!
Где я напутал?
Специально для примера я создал папку, в которой разместил несколько файлов различных типов и две подпапки. Вот так они выглядят:
Давайте рассмотрим несколько способов получения списка файлов.
Способ 1. Использование функции Dir
Этот способ хорош тем, что в данном случае мы не подключаем дополнительных библиотек, а пользуемся только предустановленным функционалом. Способ подойдет, если вам необходимо просто получить список файлов в папке и не нужно выводить их размер, определять тип файла, дату его создания/изменения и т.п. Тут мы получаем только наименования файлов и больше ничего.
Вот там код данной функции, который выводит на лист 1 перечень файлов.
Тут мы проверяем существование папки с помощью дополнительной функции. Опять таки не используем сторонних библиотек, а используем туже функцию Dir.
Вот так выглядит результат работы функции. Программа записала список файлов на текущий лист Excel.
Здесь мы не делали проверку на тип файла и вывели все файлы которые у нас были.
Если же необходимо отобрать только определенные типы файлов, например Excel файлы, то в нашем коде необходимо сделать дополнительную проверку:
Знак "*" означает любой набор символов. Таким образом, мы учли различные версии файлов Excel (xls, xlsx, xlsm).
Способ 2. Используем объект FileSystemObject
В данном случае мы будем не просто получать названия файлов, но также определять тип файла, получать его размер и дату создания. Для этого нам потребуется использовать объект FileSystemObject. Он предоставляет нам сведения о файловой системе компьютера.
Однако, чтобы начать его использовать придется подключить одну библиотеку. Не пугайтесь, она есть на всех компьютерах с Windows, потому скачивать ничего не придется. Чтобы подключить ее необходимо:
Теперь перейдем к коду. Он немного упростился:
Обратите внимание на переменные. Переменная FSO - это новый экземпляр объекта FileSystemObject. Тут мы его объявляем и сразу создаем. Директива New очень важна, многие тут допускают ошибку. Также создаем объекты MyFolder и iFile - это тоже объекты FileSystemObject
Далее делаем проверку на существование папки. В данном случае нам не нужна дополнительная функция , мы пользуемся методом FolderExists объекта (класса) FileSystemObject.
Ну и остается аналогично перебрать все файлы в директории. Тут удобно использовать цикл For Each - Next.
Результат работы программы следующий:
Способ 3. Создаем функцию, которая возвращает файлы в папке и подпапках
Напоследок разберем функцию, которая будет возвращать нам все файлы в текущей папке и во вложенных папках. Вообще это универсальная функция, которая подойдет на все случаи жизни. Смело копируйте ее в свой проект!
Итак перейдем к коду:
Разберем основные моменты этой функции. На вход она принимает один обязательный аргумент - это путь к папке Path. Также может принимать два необязательных параметра:
- Filter - перечисление списка файлов, которые мы хотим получить. Перечислять необходимо через запятую, например "doc, xls*". Вы можете использовать символ "*" чтобы включить сравнение по шаблону. По умолчанию фильтр отсутствует и возвращаются все типы файлов.
- Nesting - вложенность. Это максимальное число вложенных папок в которые "проваливается" алгоритм. По умолчанию равно 100.
В целом по алгоритму комментарии лишние, все должно быть понятно. Основной момент хочу обратить на строку 38.
Тут мы рекурсивно вызываем эту же функцию. Т.е. получается что функция вызывает саму себя. И это происходит до тех пор пока либо не останется вложенных папок, либо не будет достигнут желаемый уровень вложенности.
Теперь когда наша функция готова, просто используем ее где нам требуется вот так:
Функция нам возвращает коллекцию файлов в папке и подпапках. Вот так:
Пример файла можете скачать по кнопке ниже и использовать в своей работе. Оставляйте комментарии, буду рад на них ответить.
Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.
Поиск перебором значений
Довольно простой в реализации способ. Например, найти в колонке "A" ячейку, содержащую "123" можно примерно так:
Минусами этого так сказать "классического" способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.
Поиск функцией Find
Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую "123" достаточно такого кода:
Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист "Данные";
2-я строка: Осуществляем поиск значения "123" в колонке "A", результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае - будет пустой, т.е. Nothing.
Полностью синтаксис оператора поиска выглядит так:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
What - Строка с текстом, который ищем или любой другой тип данных Excel
After - Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.
LookIn - Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).
LookAt - Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).
SearchOrder - Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)
SearchDirection - Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)
MatchCase - Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)
MatchByte - Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)
SearchFormat - Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.
Чтобы продолжить поиск, можно использовать FindNext (искать "далее") или FindPrevious (искать "назад").
Примеры поиска функцией Find
Пример 1: Найти в диапазоне "A1:A50" все ячейки с текстом "asd" и поменять их все на "qwe"
Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:
Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.
В ниже следующем примере используется другой вариант продолжения поиска - с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.
Пример 3: Продолжение поиска с использованием Find с параметром After.
Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.
Пример 4: Найти все ячейки с шрифтом "курсив" и поменять их формат на обычный (не "курсив")
Хочу обратить внимание на то, что в этом примере я не стал использовать "защиту от зацикливания", как в Примерах 2 и 3, т.к. шрифт меняется и после "прохождения" по всем ячейкам, больше не останется ни одной ячейки с курсивом.
Свойство FindFormat можно задавать разными способами, например, так:
Поиск последней заполненной ячейки с помощью Find
Следующий пример - применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.
Пример 5: Найти последнюю колонку и столбец, заполненные данными
Для поиска функцией Find по маске (шаблону) можно применять символы:
* - для обозначения любого количества любых символов;
? - для обозначения одного любого символа;
- для обозначения символов *, ? и
. (т.е. чтобы искать в тексте вопросительный знак, нужно написать
?, чтобы искать именно звездочку (*), нужно написать
* и наконец, чтобы найти в тексте тильду, необходимо написать
Поиск в скрытых строках и столбцах
Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas
Поиск даты с помощью Find
Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:
Приведу несколько примеров поиска даты.
Пример 7: Найти текущую дату на листе независимо от формата отображения даты.
Пример 8: Найти 1 марта 2018 г.
Искать часть даты - сложнее. Например, чтобы найти все ячейки, где месяц "март", недостаточно искать "03" или "3". Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел - это выбрать формат в котором месяц прописью для ячеек с датами и искать слово "март" в xlValues.
Тем не менее, можно найти, например, 1 марта независимо от года.
Пример 9: Найти 1 марта любого года.
Книги по теме:
Посмотреть все книги по программированию
Комментарии к статье:
| 10.09.17 Дмитрий | Очень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо! |
| 23.11.17 Гость | Спасибо, хорошая статья. |
| 03.12.17 Владимир | Спасибо! Использую в своих проектах. |
| 07.12.17 Эд | Спасибо, очень пригодилась Ваша статья! |
| 19.01.18 Николай | .find не ищет значение в ячейке, если ячейка в скрытой строке. .match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра. |
| 05.02.18 Владимир | Большое спасибо! Очень толково и понятно. |
| 11.03.18 Гость | Здравствуйте, А если мне требуется найти ячейку в определенном столбце с определенным значением, например "строка 1" и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше "строка 1". Можете посоветовать? |
| 26.03.18 Гость | Спасибо! Все бы так описывали! Все доступно и понятно)) |
| 23.05.18 Аркадий | В VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках. |
| 21.07.18 Гость | Спасибо! Уже несколько недель искала подобное! |
| 25.07.18 Joann | Метод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр 'what:=' - передается без проблем, а вот значения ('xlValues', 'xlWhole', . ) для 'LookIn:=', 'LookAt:=', . - приводят к ошибке)? |
| 15.08.18 Марат | Спасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find - хороший инструмент! |
| 28.08.18 Гость | Познавательно и подробно. Спасибо за статью. |
| 16.10.18 Гость | Хорошая статья. Спасибо |
| 29.10.18 Sega | Полезная статейка. Спасибо. |
| 14.11.18 Гость | Статья для начинающих, а тонкости поиска по дате нет ни одного примера |
| 02.02.19 Ибрагим | Чушь полная, плагиат! |
| 01.03.19 inexsu.wordpress.com | Loop While Not c Is Nothing And c.Address <> firstResult Когда c станет Nothing, c.Address даст ошибку Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/ |
| 01.03.19 Администратор | Вы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения "asd" и меняем их на "qwe". При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии. Внес изменения в статье. Спасибо вам за подсказку. |
| 07.04.19 Гость | Добрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find. |
| 07.04.19 Гость | Лучше использовать функцию ВПР |
| 21.05.19 Гость | Очень подробная статья. Отдельное спасибо за множество примеров. |
| 17.10.19 Михаил | Спасибо большое все понятно доступно и очень полезно |
| 31.10.19 Гость | Спасибо. |
| 01.11.19 Гость | ку |
| 01.11.19 Гость | 2 раза |
| 19.11.19 Kamol | Отлично. Поиск с двумя значениями Пример в колонке А="123" и В="456" |
| 25.12.19 Гость | Спасибо! |
| 05.03.20 Гость | Добрый день! Спасибо за код автору Вопрос |
Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"
Николай КАРПЕНКО , канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Работа с шаблонами (или так называемыми масками) — одна из самых полезных функций, когда речь идет о работе с большими базами данных, а критерий поиска информации точно не известен. На практике такая ситуация встречается сплошь и рядом. Элементарный пример. У меня есть реестр выданных (или полученных) налоговых накладных. Реестр большой, несколько тысяч записей. И задача состоит в том, чтобы найти в этом реестре все документы по определенной фирме. Точное название фирмы (как она фигурирует в реестре) я не помню: может это «ООО "Фора"» или «ТОВ "Фора"», как-то так. Но тот факт, что в названии есть слово «Фора», я знаю наверняка. А это означает, что для поиска информации о фирме «Фора» мне нужно использовать не точное название предприятия, а некоторый шаблон. В данном случае он может выглядеть так: «*Фора*». В этом шаблоне на месте символа «*» может стоять любая строка произвольной длины. А сам шаблон описывает названия фирм, где вначале идет любой текст, затем есть слово «Фора», а за ним — снова произвольный текст. Под такой шаблон попадают, например, такие названия: «ТОВ "Фора"», «ООО "Фора"», «Фирма "Фора"», «"Фора", 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. Все зависит от фантазии и творческого подхода к решению проблемы.
Читайте также: