Как сделать цикл в excel?
Пару недель назад передо мной встала проблема разработки электронной таблицы, просчитывающую диапазоны рабочего времени в зависимости от рабочего графика и производственного календаря. С первого взгляда задача нетрудная, но в ходе проектирования я понял, что избежать использования циклов не удастся. Особую специфику здесь придавало условие заказчика, что готовый продукт должен быть именно
таблицей БЕЗ макросов и дополнительных модулей (функций пользователя).
Стандартный набор функций Excel не поддерживает ничего похожего на циклы, однако выход нашелся – использование циклических ссылок и итераций.
Первый шаг, чтобы начать работать с циклическими ссылками – это разрешить итеративные вычисления в Excel. В версии Microsoft Office XP это делается в разделе меню «Сервис – Параметры»:
В открывшемся окне активируем соответствующий флажок:
Так же обратите внимание на поле «Предельное число итераций» — оно определяет, сколько раз будет проходить вычисление по всем циклическим ссылкам, если вы сами не поставите ограничение.
!Информация: В Excel 2007 включение итеративных вычислений производится в пункте меню «Файл – Параметры Excel» в разделе «Формулы».
Рассмотрим пример использования итеративных вычислений в виде цикла.
Впишите в ячейку А1 формулу:
Если значение предельного числа итераций равно 100, то результатом вычислений в ячейке А1 будет равно 100. Теперь изменим формулу:
Как видите число итераций мы ограничили числом в ячейке А2, а именно 10. Чтобы проиллюстрировать, как происходят итерации, добавим формулу в ячейку B2:
Как видите, каждый этап итерации (новое значение А1) добавляется к ячейке B1 до тех пор пока происходят итерации.
!Замечание: Возможно, результат в B2 будет не таким – для того, чтобы исправить это сделайте следующее: после ввода формулы в ячейку B1 перейдите в ячейку A1, установите курсор в поле редактирования формулы и нажмите Enter – произойдет пересчет итераций. Для корректной работы итераций необходимо задать ряд дополнительных условий, ограничивающих их количество и задающее условие сброса текущего значения поля в исходное значение. Пересчет всех ячеек с циклическими ссылками происходит каждый раз при изменении ячеек, от которых они зависят.
!Дополнительные ссылки:
Встречаются ситуации, когда от программы VBA требуется совершить несколько раз подряд один и тот же набор действий (то есть повторить несколько раз один и тот же блок кода). Это может быть сделано при помощи циклов VBA.
К циклам VBA относятся:
- Цикл For
- Цикл Do While
- Цикл Do Until
Далее мы подробно рассмотрим каждый из этих циклов.
Оператор цикла «For» в Visual Basic
Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each.
Цикл «For … Next»
Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:
В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.
В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение . Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.
Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.
Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:
Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.
Цикл «For Each»
Цикл For Each похож на цикл For … Next, но вместо того, чтобы перебирать последовательность значений для переменной-счётчика, цикл For Each выполняет набор действий для каждого объекта из указанной группы объектов. В следующем примере при помощи цикла For Each выполняется перечисление всех листов в текущей рабочей книге Excel:
Оператор прерывания цикла «Exit For»
Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.
Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:
Цикл «Do While» в Visual Basic
Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:
В приведённом примере условие iFib_Next < 1000 проверяется в начале цикла. Поэтому если бы первое значение iFib_Next было бы больше 1000, то цикл бы не выполнялся ни разу.
Другой способ реализовать цикл Do While – поместить условие не в начале, а в конце цикла. В этом случае цикл будет выполнен хотя бы раз, не зависимо от того, выполняется ли условие.
Схематично такой цикл Do While с проверяемым условием в конце будет выглядеть вот так:
Цикл «Do Until» в Visual Basic
Цикл Do Until очень похож на цикл Do While: блок кода в теле цикла выполняется раз за разом до тех пор, пока заданное условие выполняется (результат условного выражения равен True). В следующей процедуре Sub при помощи цикла Do Until извлекаются значения из всех ячеек столбца A рабочего листа до тех пор, пока в столбце не встретится пустая ячейка:
В приведённом выше примере условие IsEmpty(Cells(iRow, 1)) находится в начале конструкции Do Until, следовательно цикл будет выполнен хотя бы один раз, если первая взятая ячейка не пуста.
Однако, как было показано в примерах цикла Do While, в некоторых ситуациях нужно, чтобы цикл был выполнен хотя бы один раз, не зависимо от первоначального результата условного выражения. В таком случае условное выражение нужно поместить в конце цикла, вот так:
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов
Правила перепечаткиЕще больше уроков по Microsoft Excel
Оцените качество статьи. Нам важно ваше мнение:
Добрый день!
Неделю пытаюсь разобраться с макросами в Excel — выходит признаюсь честно плохо…
Решение задачи так и не нашла. Надеюсь вы сумеете мне подсказать куда конкретнее направить свою активность.
Задача передо мной стоит такая:
ячейка A1 содержит значение, которое я с помощью формул разбиваю на диапазон ячеек (задействовано 276 ячеек) целыми числами. Но при разбивке образуется дельта округления. Так вот мне необходимо эту дельту целыми числами доразнести в уже заполненные формулами ячейки например по порядку.
Например если дельта = 117, а диапазон 276, то в первые 117 ячеек прибавляем по 1, а остальные не трогаем.
Проход по диапазону ячеек
При использовании Visual Basic часто требуется выполнить один и тот же блок операторов в каждой ячейке диапазона. Для этого необходимо объединить оператор цикла и один или несколько методов для идентификации каждой ячейки по отдельности и выполнить операцию.
Один из способов пройти по диапазону — использовать цикл For. Next со свойством Cells. С помощью свойства Cells можно заменить номера индексов ячеек счетчиком циклов (или другими переменными или выражениями). В следующем примере индекс строки заменяется переменной counter . Процедура проходит по диапазону ячеек C1:C20, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01.
Еще один простой способ пройти по диапазону — использовать цикл For Each. Next с коллекцией ячеек, указанной в свойстве Range. Visual Basic автоматически присваивает объектную переменную для следующей ячейки при каждом выполнении цикла. Следующая процедура проходит по диапазону ячеек A1:D10, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01.
Если вы не знаете границы диапазона, по которому нужно пройти, можно использовать свойство CurrentRegion, чтобы возвратить диапазон, окружающий активную ячейку. Например, при запуске на листе следующая процедура выполняет проход по диапазону, окружающему активную ячейку, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Excel. Как в Excel сделать циклическую формулу? И будет ли «работать» формула?
Чтобы формулы работали в цикле — должны быть включены итерации.
Ячейка выполняет действия со своими же значеними
В ячейку D2 вволите значение и ячейка A2 сама себя пересчитывает
B2=ABS(B2-D2)
В ячейку А1 вводим любую информацию
Автоматически в В1 зафиксируется время
Интересное решение предложил Michael_S
Но этот способ требует дополнителный столбец (который можно закрыть или расположить на «далеких » адресах. напр AZ1 )
В1=ЕСЛИ (A1<>C1;ТДАТА ();B1)
С1=ЕСЛИ (B1;A1)
Столбец «В» — имеет формат «Дата»
Общий недостаток для всех всех циклических формул:
должны быть включены итерации.
но иногда, при запуске файла, интерация принимает значение по умолчанию ( т. е. выкл)
и тогда машина начинает ругаться и выскакивать сообщение о циклических ссылках.
Прийдется вручную включать итерации.