IT-помощь
VBA (Excel)
Технические задания заказчика, зачастую, выполнены в приложении EXCEL, таблицы которого могут содержать до несколько тысяч строк, иногда с ошибками в формулах, иногда криво отформатированных. Исправлять такие в ручном режиме, чтобы потом использовать в техническом предложении, - мазохизм высшей пробы.
Автоматизировать процесс работы с таблицей помогает VBA - встроенное в EXCEL средство программирования. Практика показывает, что многие пользователи, работающие с EXCEL, знают и применяют встроенные в EXCEL функции. Но даже при решении такой тривиальной задачи, как например, скрыть (удалить) строки, в которых есть пустые ячейки (допустим, в третьем столбце), могут возникнуть трудности. Если количество строк несколько десятков, можно справиться в ручном режиме, а если - несколько тысяч? Клацать мышкой "до посинения"?
В этом случае можно (и даже нужно) автоматизировать процесс через VBA.
Даже если Вы никогда не занимались программированием и не знаете что такое VBA, просто откройте какую-либо EXCEL-евскую таблицу (естественно, не пустую, а с какими-либо данными), очистите отдельные ячейки в третьем столбце и проделайте 5 нижеследующих шагов. Эти простые шаги позволят понять: 1) как записать последовательность однообразных действий, совершаемых в процессе редактирования таблицы, в виде макрокоманды (так называемого "макроса"), 2) как заставить эту последовательность "прошерстить" всю таблицу, т.е. каждую строку (или каждый столбец, ячейку).
Шаг 1.
Включаем возможность выполнения макросов, если по умолчанию она отключена.
Для Office 2003: Сервис->Макрос->Безопасность->Уровень_безопасности->Средняя->ОК.
Для Office 2010: Файл -> Параметры -> Центр_управления_безопасностью -> Параметры_макросов -> Включить_все_макросы -> ОК. Возвращаемся в меню Файл -> Параметры. Переходим в Настройка_ленты -> Основные_вкладки -> включаем_чекбокс_"Разработчик" -> ОК
Шаг 2.
Записываем макрос.
Для Office 2003: Сервис->Макрос->Начать_запись. В строке "Имя макроса" вводим понятное название макроса, например, "Hiden_rows", нажимаем ОК. Запись пошла.
Для Office 2010: Разработчик->Запись_макроса. В строке "Имя макроса" вводим понятное название макроса, например, "Hiden_rows", нажимаем ОК. Запись пошла.
Выполняем действия, которые необходимы, чтобы скрыть строки с пустой ячейкой в третьем столбце. Достаточно скрыть одну строку. Останавливаем запись и переходим в редактор Visual Basic.
Для Office 2003: Сервис -> Макрос -> Редактор_Visual Basic (либо Сервис -> Макрос -> Макросы -> Выбрать_макрос_Hiden_rows -> Изменить).
Для Office 2010: Разработчик->Visual Basic
Откроется окно редактора, где в левой части - внутренняя структура вашего файла, а в правой - непосредственно текст макроса (программный код):
Шаг 3. Редактируем текст макроса.
а) помещаем его внутри простого цикла: For f = 1 To 10000 ....... Next f Чтобы проверил, как минимум, 10000 строк,
б) перед этим циклом вставляем условие окончание цикла, чтобы выполнение макроса закончилось и курсор вернулся на первую строку, если в текущей строке пойдут пустые первые ячейки: If Cells(f, 1).Value = "" Then Cells(1, 1).Select Exit Sub End If
в) добавляем условие наличия пустой ячейки в третьем столбце: If Cells(f, 3).Value = "" Then ..... End If
г) меняем Rows("7:7") на Rows(f) , чтобы обрабатывалась не только седьмая строка, а каждая в соответствии со значением счетчика f,
д) получаем нечто подобное:
Шаг 4. Запускаем макрос на выполнение.
Переключаемся из редактора на лист таблицы. Можно сразу запустить макрос на выполнение.
Для Office 2003: Сервис->Макрос->Макросы->Выбрать макрос Hiden_rows->Выполнить.
Для Office 2010: Разработчик->Макросы->Выбрать_макрос Hiden_rows->Выполнить.
А можно воспользоваться другой возможностью, предоставляемой VBA, - это добавить на лист элемент управления. В нашем случае можно вставить на лист кнопку и сопоставить ее с макросом Hiden_rows, чтобы одним кликом запускать его на выполнение, а не открывать каждый раз меню и ещё несколько подменю.
Шаг 5. Добавляем на лист кнопку запуска макроса на выполнение.
Для Office 2003: Сервис->Настройка->Панели_инструментов->Включить_чекбокс_"Формы"->Закрыть. На панели "Формы" нажать значок "Кнопка", выбрать место на листе и удерживая левую клавишу выделить зону под кнопку. В открывшемся окне "Назначить макрос объекту" выбрать наш макрос Hiden_rows, нажать ОК. Чтобы переименовать кнопку, ставим курсор внутри кнопки или кликаем правой клавишей по внешней границе кнопки и в подменю выбираем "Изменить текст". Вводим новое название. Нажимаем на кнопку (макрос запускается на выполнение). Все строки с пустыми ячейками в третьем столбце скрыты.
Для Office 2010: Разработчик->Вставить->Элементы_управления_формы->Нажать_значок "Кнопка (элемент управления формы)", выбрать место на листе и удерживая левую клавишу выделить зону под кнопку. В открывшемся окне "Назначить макрос объекту" выбрать наш макрос Hiden_rows, нажать ОК. Чтобы переименовать кнопку, ставим курсор внутри кнопки или кликаем правой клавишей по внешней границе кнопки и в подменю выбираем "Изменить текст". Вводим новое название. Нажимаем на кнопку (макрос запускается на выполнение). Все строки с пустыми ячейками в третьем столбце скрыты.
Если будет аналогичная или похожая задача, открываем файл, в котором макроса нет, параллельно ему открываем файл, где уже записан данный макрос и запускаем макрос на выполнение из меню первого файла.
Хотя это и очень простой макрос, но на его примере, даже не обладая глубокими знаниями VBA, можно не только значительно облегчить себе рутинную работу, но и уяснить алгоритм действий по созданию более сложных проектов, таких, например, как тестирующие программы (и создавать их!). Аналогичные возможности заложены и в Word-е.
Если Вы пробуете свои силы в VBA, или уже уверенно используете его возможности, но что-то не получается, обращайтесь, - поможем.
- Комментарии