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 » текст функции для сравнения строк. Делаем так.
1. Заходим на сайт по адресу http://www.bk.factor.ua/ru/publications/14/8/1106.html , скачиваем файл с текстом программы.
3. Открываем скачанный файл в любом текстовом редакторе. Можно воспользоваться программой «Блокнот» или процессором MS Word. Перед вами должен появиться такой текст:
5. Комбинацией « Ctrl+C » копируем текст в буфер обмена.
7. Вызываем меню « Insert → Module ». Откроется окно для добавления текста программы.
8. Нажимаем « Ctrl+V » (вставляем текст из буфера обмена).
9. Нажимаем кнопку « Сохранить » и закрываем редактор Visual Basic. Функция добавлена.
« * » — означает, что на месте этого знака может находиться произвольное количество любых символов;
« # » — на месте этого символа может находиться любая цифра (от « 0 » до « 9 »);
« [Список_символов] » — такая запись в шаблоне заменяет любой символ из указанного списка;
« [!Список_символов] » — означает, что в строке могут находиться любые символы, кроме перечисленных в списке.
Вот примеры нескольких шаблонов, которые можно использовать в функции « MskCmp() »:
« м*н » — слова, которые начинаются на « м » и заканчиваются на « н »;
« *[аостр]* » — все слова, содержащие хотя бы одну из букв « а », « о », « с », « т », « р »;
Применяем функцию MskCmp() для обработки базы данных
Функция для проверки текста у нас есть. Можно использовать ее для решения нашей задачи. Напомню, что мы хотим в базе данных на рис. 1 посчитать итоговые значения для контрагентов, в названии которых есть слово « Фора ». Делаем так.
2. Перед началом базы данных добавляем рабочую строку. Она будет первой на рабочем листе.
3. Заполняем ячейки рабочей строки, а именно: в « B1 » вводим текст « Фирма: ». В ячейку « C1 » вводим название контрагента, для которого нужно посчитать итоговые значения в базе данных. В нашем примере — это фирма « Фора ».
4. Становимся на ячейку « J2 », вводим название заголовка « Пр ». В этой колонке мы напишем признак суммирования.
5. Становимся на ячейку « J4 », щелкаем на значке « fx ». Откроется окно « Мастер функций », изображенное на рис. 5.
6. В этом окне щелкаем на списке « Категория: », выбираем значение « Определенные пользователем ». В списке « Выберите функцию: » появится список доступных функций пользователя для текущего документа.
7. В этом списке выбираем вариант « MskCmp » и нажимаем « ОК ». Откроется окно « Аргументы функции », изображенное на рис. 6.
9. В окне « Аргументы функции » нажимаем « ОК ». В ячейке « J4 » появится формула « = MskCmp(E4;»*»&$C$1&»*»;0) », а ее значение в ячейке « J4 » будет « Истина ». Все верно: в « E4 » записано название « ТОВ «Фора» » и функция « MskCmp() » это правильно определила.
10. Копируем формулу вниз по колонке « J » на всю высоту базы данных.
Все, что нам остается, — добавить формулы суммирования с учетом значений в колонке « J ». Сделать это можно при помощи функции « Сумм если() ». Делаем так.
3. Копируем содержимое « G1 » в буфер обмена и вставляем содержимое из буфера в ячейки « H1 » и « I1 ». Результат нашей работы показан на рис. 7.
Функцию « MskCmp() » можно с успехом использовать для анализа и проверки правильности данных. Такая задача очень актуальна при работе с большими таблицами, к которым планируется применить инструменты обработки данных и подведения итогов. Вот всего пара ситуаций, которые часто встречаются в практической работе.
1. Находим свободный столбец. Пусть это будет колонка « К ». Озаглавим ее как « Контроль ».
2. В ячейку « K2 » вводим формулу « = MaskCompare(E4;»*[A-z]*»;0) » и копируем ее на всю высоту таблицы. Теперь все как на ладони. Все строки, помеченные в колонке « K » значением « Истина », содержат в названии предприятия латинские символы.
Пример 2. В базе данных на рис. 2 нужно проверить, чтобы все коды ИНН содержали только числа. Сделать такую проверку для содержимого ячейки « F4 » поможет формула « = MaskCompare(F4;»*[0123456789]»;0) ». Далее копируем ее на всю высоту таблицы — и задача решена. Все строки, которые будут отмечены значением « ЛОЖЬ », содержат ошибку в коде ИНН.
Как видите, использование масок и шаблонов со специальными символами открывает широчайшие возможности по обработке данных в программе Excel. Все зависит от фантазии и творческого подхода к решению проблемы.
[expert_bq id=»1570″]запустите Мастер создания масок ввода, выберите одну из масок как отправную точку как описано в предыдущем разделе и затем перейдите во второе окно мастера. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Программа позволяет формировать отчёты по трафику на основании данных биллинговой системы. В качестве исходных данных программа принимает: логин абонента дату начала и окончания периода сбора статистики Особенности программы: формирование отчётов по всему трафику, или только по оплачиваемому создание листа итогов с диаграммой (сразу видно, с какого сайта был.Ячейки в Excel. Операции
Во второй части стоит 1, а в третей — #. Маска предназначена для ввода телефонных номеров и сохранения их в БД вместе с литералами маски (в данном случае двумя скобками, пробелом и дефисом), в ней вместо знака подчеркивания в качестве заполнителя используется знак номера (#).
