Использование макросов Microsoft Excel для создания циклов
Использование макросов циклов
При обработке данных бывают очень полезны макросы, называемые циклами, которые автоматически выполняют одни и те же действия повторно. Пользователю, работающему с большими массивами данных или с массивами разных размеров, циклы позволяют сэкономить немало времени и сил.
Цикл Do…Loop
В цикле Do…Loop производится подсчет строк в диапазоне данных до обнаружения пустой строки.
Предположим, вы хотите подсчитать количество строк в диапазоне данных, который может быть небольшим, а может — весьма солидным. Для этого следует использовать цикл Do…Loop. Он выполняет заданное действие столько раз, сколько необходимо. При этом подсчитывается число всех строк, найденных в диапазоне. Или предположим, что требуется выполнить одно действие в двух диапазонах данных, содержащих различное число строк. И в этом случае будет полезен цикл Do…Loop. Он выполнится столько раз, сколько необходимо для каждого из диапазонов. Как цикл определит, сколько именно раз следует повторить действие? Из заданных вами условий. Повтор прекратится, как только обнаружится определенный фрагмент данных, например пустая строка или некий текст.
В качестве условия выхода из цикла Do…Loop применяется условие While или условие Until. Цикл выполняется, пока заданное утверждение остается истинным (While) или пока оно не становится истинным (Until). Так, чтобы задать цикл, прекращающийся при обнаружении пустой ячейки в первом столбце, следует использовать условие While:
В соответствии с этим условием While цикл выполняется до тех пор, пока обрабатываемая им ячейка не окажется пустой. Обрабатываемая строка задана номером x, и первая ячейка этой строки имеет координаты (x,1). Комбинация знаков <> означает «не равно». Кавычки, между которыми ничего нет, обозначают пустую ячейку.
Если цикл требуется выполнять до обнаружения ячейки, содержащей число 365, используйте условие Until. В любом случае именно вы предоставляете программе сведения, помогающие ей определить, когда следует выйти из цикла.
Цикл For Each…Next
Цикл For Each…Next позволяет выделить более темным шрифтом каждое слово «ОК» в тексте выбранного фрагмента.
Цикл For Each…Next используется для выполнения некоторого действия над каждой ячейкой диапазона данных. Предположим, например, что в выделенном диапазоне требуется повсюду изобразить слово «ОК» более темным шрифтом по сравнению с остальным текстом. Код будет выглядеть примерно следующим образом:
Здесь «MyCell» указывает текущую ячейку, обрабатываемую в цикле, а «For Each» означает, что в цикле перебираются все ячейки выделенного фрагмента. При обнаружении ячейки, содержащей только слово «ОК», это слово выделяется темным шрифтом. (Внешний вид текста контролируется свойством Font, а атрибут Bold означает полужирное начертание.)
Вложенные циклы
Циклы Do…Loop и For Each…Next весьма эффективны в простейших случаях. Теперь мы немного повысим уровень сложности, перейдя к рассмотрению вложенных циклов. Они используются, когда необходимо выполнить какое-либо действие в нескольких диапазонах данных или несколько раз в одном диапазоне данных. Аналогией вложенных циклов может служить вращение Земли вокруг Солнца. Один полный оборот вокруг Солнца (год) можно считать внешним циклом, а один оборот Земли вокруг своей оси (сутки) — внутренним циклом, вложенным во внешний цикл. В течение каждого года выполняется 365 внутренних циклов, и каждое 1 января внешний цикл повторяется:
Разумеется, этот код не будет выполняться в Excel, но он наглядно показывает, что каждый большой цикл (оборот вокруг Солнца) включает 365 маленьких циклов (оборотов Земли вокруг своей оси).
Или же рассмотрим процедуру, реально выполнимую в Excel. Предположим, что необходимо удалить с листа лишние повторяющиеся строки. Для этого достаточно сравнить первую строку с каждой последующей и удалить все дубликаты, если они будут обнаружены, затем сравнить вторую строку с каждой последующей и т. д. Внешний цикл обеспечивает повторение этого процесса для каждой строки. Во внутреннем цикле будет производиться фактическое сравнение и удаление строк.
Использование свойства Cells в циклах
Столбцы обозначаются числами, а не буквами.
Данные, содержащиеся в ячейке, можно получать в коде макроса цикла двумя разными способами. Один из них предусматривает использование свойства Cells, а второй — использование свойства Range. В VBA обычно бывает легче и удобнее работать со свойством Cells, поскольку изменять описываемые им значения проще. Свойство Range идентифицирует строки и столбцы на листе с помощью чисел и букв, а в свойстве Cells и строки, и столбцы обозначаются числами. Добавление +1 к таким числам позволяет легко переходить в цикле от строки к строке и от столбца к столбцу, а вот реализовать в коде переход от одной буквы к следующей за ней не так просто.
Совет. При необходимости в электронной таблице можно задать отображение номеров столбцов вместо буквенных обозначений. Выберите в меню Сервис команду Параметры и перейдите на вкладку Общие. Установите флажок «Стиль ссылок R1C1». Если позже потребуется вернуть прежние обозначения, снимите этот флажок.
Упражнение
Для выполнения упражнения загрузите файл macros1
Упражнение 1. Подсчет с использованием цикла Do…Loop
Цикл Do…Loop будет использован для подсчета количества строк в диапазоне данных.
- Откройте файл практического занятия, выделите код в первом текстовом поле листа (от строки Sub CountRows() до End Sub) и скопируйте его в буфер обмена.Совет. После запуска редактора Visual Basic данные инструкции будут закрыты новым окном. Чтобы инструкции были всегда доступны, напечатайте эту страницу (щелкните правой кнопкой мыши и выберите в контекстном меню команду Печать).В этом фрагменте кода переменной x присваивается номер текущей обрабатываемой строки; x + 1 обозначает следующую строку. Переменной z присваивается текущее число подсчитанных строк, начиная с 0, когда еще ничего не было сосчитано. Цикл обрабатывает поочередно каждую строку, пока не дойдет до пустой строки. При каждом переходе к следующей строке число строк, записанное в переменной z, увеличивается на 1. В итоге подсчитанное количество строк выводится в сообщении; это значение берется из переменной z.Значение z отображается в сообщении в окружении двух текстовых фрагментов. Все это объединяется в одну фразу с помощью знаков &.
- Щелкните вне текстового поля с кодом и выделите ячейку C3 (или, если включен стиль ссылок R1C1, выделите строку 3 и столбец 3).
- Откройте редактор Visual Basic и вставьте новый модуль.Как это сделать? Выделите в меню Сервис пункт Макрос и выберите команду Редактор Visual Basic. В меню редактора Insert выберите команду Module.
- Вставьте код, скопированный на шаге 1, в новый модуль.
- Щелкните в строке Sub CountRows() и нажмите клавишу F5, чтобы выполнить макрос.
- Цикл Do…Loop выполняет необходимые действия, и на экране появляется сообщение о том, что в заданном диапазоне содержится шесть строк.
- Нажмите кнопку ОК, чтобы закрыть окно сообщения, и вернитесь в Excel, выбрав в меню File команду Close and Return to Microsoft Excel.
Примечание. В этом упражнении переменная z хранит текущее число строк, x — текущий номер строки, а y — номер столбца (в данном случае это номер 3, что соответствует столбцу C).
Упражнение 2. Подсчет ячеек с использованием цикла For Each…Next
- Выделите код во втором текстовом поле листа (начиная со строки Sub CountCells() и до End Sub) и скопируйте его в буфер обмена.Подсказка. Чтобы увидеть второе текстовое поле, начинающееся со строки 27, возможно, потребуется прокрутить лист.В этом фрагменте кода переменной z присваивается подсчитанное количество строк, начиная с 0, когда еще ничего не было сосчитано. С помощью этого кода поочередно перебираются все ячейки выделенного диапазона. При каждом переходе к следующей ячейке их общее число, записываемое в переменной z, увеличивается на 1. В итоге подсчитанное количество ячеек выводится в сообщении; это значение берется из переменной z.
- Щелкните вне текстового поля с кодом и выделите диапазон C3:D8, значения от «Светлана» до «Шашков». Если включен стиль ссылок R1C1, выделите фрагмент со строки 3, столбца 3 до строки 8, столбца 4.
- Откройте редактор Visual Basic и вставьте новый модуль.Подсказка. О том, как это сделать, см. в первом упражнении.
- Вставьте код, скопированный на шаге 1, в новый модуль.
- Щелкните в строке Sub CountCells() и нажмите клавишу F5.
- Готово! Появляется сообщение о том, что в выделенном фрагменте содержится 12 ячеек.
- Нажмите кнопку ОК, чтобы закрыть окно сообщения. Закройте редактор Visual Basic и книгу. Будет предложено сохранить книгу; можете сделать это, если хотите вернуться к ней позже.
В ходе следующего практического занятия вы займетесь вложенными циклами. Пока же, если у вас сформировалось общее представление о том, как работают эти макросы циклов, этого более чем достаточно.
Примечание. Если появится сообщение Microsoft Visual Basic «Compile error» (ошибка компиляции), не беспокойтесь. Это просто означает, что код в модуле не в точности совпадает с кодом, который было предложено скопировать и вставить. Удалите код из модуля и скопируйте его туда снова.
Понравился материал? Подпишитесь на обновления сайта.
Мой блог находят по следующим фразам
• майкрософт офис 2007 скачать бесплатно
• программа для востоновления данных после быстрого форматирование
• сайты торренты бесплатные
• компьютерные курсы в Москве для пенсионеров
• office 2007 rus скачать одним файлом бесплатно
• программа для востановления данных после форматирования
***
Постовой
Возможно, Вас заинтересует также информация по следующим ключевым словам, которую обычно ищут на моем сайте
• включение компьютера по сети
• скачать microsoft office 2007
• программы для ограничения доступа детей в интернет
• Скачать програму для ноутбука для электросети
• автоматическое включение и выключение компьютера
• программы для восстановление флешки после форматирования
