Цикл в excel 2003
Пару недель назад передо мной встала проблема разработки электронной таблицы, просчитывающую диапазоны рабочего времени в зависимости от рабочего графика и производственного календаря. С первого взгляда задача нетрудная, но в ходе проектирования я понял, что избежать использования циклов не удастся. Особую специфику здесь придавало условие заказчика, что готовый продукт должен быть именно
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 i = 1 To 10 Total = Total + iArray(i) Next i
В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.
В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение . Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.
For d = 0 To 10 Step 0.1 dTotal = dTotal + d Next d
Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.
Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:
For i = 10 To 1 Step -1 iArray(i) = i Next i
Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.
Цикл «For Each»
Цикл For Each похож на цикл For … Next, но вместо того, чтобы перебирать последовательность значений для переменной-счётчика, цикл For Each выполняет набор действий для каждого объекта из указанной группы объектов. В следующем примере при помощи цикла For Each выполняется перечисление всех листов в текущей рабочей книге Excel:
Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox “Найден лист: ” & wSheet.Name Next wSheet
Оператор прерывания цикла «Exit For»
Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.
Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:
For i = 1 To 100 If dValues(i) = dVal Then IndexVal = i Exit For End If Next i
Цикл «Do While» в Visual Basic
Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:
‘Процедура Sub выводит числа Фибоначчи, не превышающие 1000 Sub Fibonacci() Dim i As Integer ‘счётчик для обозначения позиции элемента в последовательности Dim iFib As Integer ‘хранит текущее значение последовательности Dim iFib_Next As Integer ‘хранит следующее значение последовательности Dim iStep As Integer ‘хранит размер следующего приращения ‘инициализируем переменные i и iFib_Next i = 1 iFib_Next = 0 ‘цикл Do While будет выполняться до тех пор, пока значение ‘текущего числа Фибоначчи не превысит 1000 Do While iFib_Next < 1000 If i = 1 Then ‘особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else ‘сохраняем размер следующего приращения перед тем, как перезаписать ‘текущее значение последовательности iStep = iFib iFib = iFib_Next End If ‘выводим текущее число Фибоначчи в столбце A активного рабочего листа ‘в строке с индексом i Cells(i, 1).Value = iFib ‘вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub
В приведённом примере условие iFib_Next < 1000 проверяется в начале цикла. Поэтому если бы первое значение iFib_Next было бы больше 1000, то цикл бы не выполнялся ни разу.
Другой способ реализовать цикл Do While – поместить условие не в начале, а в конце цикла. В этом случае цикл будет выполнен хотя бы раз, не зависимо от того, выполняется ли условие.
Схематично такой цикл Do While с проверяемым условием в конце будет выглядеть вот так:
Do … Loop While iFib_Next < 1000
Цикл «Do Until» в Visual Basic
Цикл Do Until очень похож на цикл Do While: блок кода в теле цикла выполняется раз за разом до тех пор, пока заданное условие выполняется (результат условного выражения равен True). В следующей процедуре Sub при помощи цикла Do Until извлекаются значения из всех ячеек столбца A рабочего листа до тех пор, пока в столбце не встретится пустая ячейка:
iRow = 1 Do Until IsEmpty(Cells(iRow, 1)) ‘Значение текущей ячейки сохраняется в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop
В приведённом выше примере условие IsEmpty(Cells(iRow, 1)) находится в начале конструкции Do Until, следовательно цикл будет выполнен хотя бы один раз, если первая взятая ячейка не пуста.
Однако, как было показано в примерах цикла Do While, в некоторых ситуациях нужно, чтобы цикл был выполнен хотя бы один раз, не зависимо от первоначального результата условного выражения. В таком случае условное выражение нужно поместить в конце цикла, вот так:
Do … Loop Until IsEmpty(Cells(iRow, 1))
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов
Правила перепечаткиЕще больше уроков по Microsoft Excel
Оцените качество статьи. Нам важно ваше мнение:
Добрый день!
Неделю пытаюсь разобраться с макросами в Excel — выходит признаюсь честно плохо…
Решение задачи так и не нашла. Надеюсь вы сумеете мне подсказать куда конкретнее направить свою активность.
Задача передо мной стоит такая:
ячейка A1 содержит значение, которое я с помощью формул разбиваю на диапазон ячеек (задействовано 276 ячеек) целыми числами. Но при разбивке образуется дельта округления. Так вот мне необходимо эту дельту целыми числами доразнести в уже заполненные формулами ячейки например по порядку.
Например если дельта = 117, а диапазон 276, то в первые 117 ячеек прибавляем по 1, а остальные не трогаем.
Подскажите, пожалуйста, возможно ли вообще это описать для Excel?
Источник
Цикл For… Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For… Next.
Цикл For… Next в VBA Excel предназначен для выполнения группы операторов необходимое количество раз, заданное управляющей переменной цикла – счетчиком. При выполнении цикла значение счетчика после каждой итерации увеличивается или уменьшается на число, указанное выражением оператора Step, или, по умолчанию, на единицу. Когда необходимо применить цикл к элементам, количество которых и индексация в группе (диапазон, массив, коллекция) неизвестны, следует использовать цикл For Each… Next.
Синтаксис цикла For… Next
For counter = start To end [ Step step ] [ statements ] [ Exit For ] [ statements ] Next [ counter ] |
For счетчик = начало To конец [ Step шаг ] [ операторы ] [ Exit For ] [ операторы ] Next [ счетчик ] |
В квадратных скобках указаны необязательные атрибуты цикла For… Next.
Компоненты цикла For… Next
| Компонент | Описание |
| counter | Обязательный атрибут. Числовая переменная, выполняющая роль счетчика, которую еще называют управляющей переменной цикла. |
| start | Обязательный атрибут. Числовое выражение, задающее начальное значение счетчика. |
| end | Обязательный атрибут. Числовое выражение, задающее конечное значение счетчика. |
| Step* | Необязательный атрибут. Оператор, указывающий, что будет задан шаг цикла. |
| step | Необязательный атрибут. Числовое выражение, задающее шаг цикла. Может быть как положительным, так и отрицательным. |
| statements | Необязательный** атрибут. Операторы вашего кода. |
| Exit For | Необязательный атрибут. Оператор выхода из цикла до его окончания. |
| Next [ counter ] | Здесь counter – необязательный атрибут. Это то же самое имя управляющей переменной цикла, которое можно здесь не указывать. |
*Если атрибут Step отсутствует, цикл For… Next выполняется с шагом по умолчанию, равному 1.
**Если не использовать в цикле свой код, смысл применения цикла теряется.
Примеры циклов For… Next
Вы можете скопировать примеры циклов в свой модуль VBA, последовательно запускать их на выполнение и смотреть результаты.
Простейший цикл
Заполняем десять первых ячеек первого столбца активного листа Excel цифрами от 1 до 10:
Sub test1() Dim i As Long For i = 1 To 10 Cells(i, 1) = i Next End Sub |
Простейший цикл с шагом
В предыдущий цикл добавлен оператор Step со значением 3, а результаты записываем во второй столбец:
Sub test2() Dim i As Long For i = 1 To 10 Step 3 Cells(i, 2) = i Next End Sub |
Цикл с отрицательными аргументами
Этот цикл заполняет десять первых ячеек третьего столбца в обратной последовательности:
Sub test3() Dim i As Long For i = 0 To -9 Step -1 Cells(i + 10, 3) = i + 10 Next End Sub |
Увеличиваем размер шага до -3 и записываем результаты в четвертый столбец активного листа Excel:
Sub test4() Dim i As Long For i = 0 To -9 Step -3 Cells(i + 10, 4) = i + 10 Next End Sub |
Вложенный цикл
Внешний цикл последовательно задает индексы первых десяти строк активного листа, а вложенный цикл складывает числа в первых четырех ячейках строки с текущем индексом и записывает сумму в ячейку пятого столбца. Перед запуском вложенного цикла с накопительным сложением, пятую ячейку соответствующей строки обнуляем, чтобы в случае нахождения в ней какого-либо числа, оно не прибавилось к итоговой сумме.
Sub test5() Dim i1 As Long, i2 As Long For i1 = 1 To 10 ‘Пятой ячейке в строке i1 присваиваем 0 Cells(i1, 5) = 0 For i2 = 1 To 4 Cells(i1, 5) = Cells(i1, 5) + Cells(i1, i2) Next Next End Sub |
Выход из цикла
В шестой столбец активного листа запишем названия десяти животных, конечно же, с помощью цикла For… Next:
Sub test6() Dim i As Long For i = 1 To 10 Cells(i, 6) = Choose(i, “Медведь”, “Слон”, “Жираф”, “Антилопа”, _ “Крокодил”, “Зебра”, “Тигр”, “Ящерица”, “Лев”, “Бегемот”) Next End Sub |
Следующий цикл будет искать в шестом столбце крокодила, который съел галоши. В ячейку седьмого столбца цикл, пока не встретит крокодила, будет записывать строку «Здесь был цикл», а когда обнаружит крокодила, запишет «Он съел галоши» и прекратит работу, выполнив команду Exit For. Это будет видно по ячейкам рядом с названиями животных ниже крокодила, в которых не будет текста «Здесь был цикл».
Sub test7() Dim i As Long For i = 1 To 10 If Cells(i, 6) = “Крокодил” Then Cells(i, 7) = “Он съел галоши” Exit For Else Cells(i, 7) = “Здесь был цикл” End If Next End Sub |
Результат работы циклов For… Next из примеров:
Результат работы циклов For… Next
Такие данные на активном листе Excel вы получите, если последовательно запустите на выполнение в редакторе VBA все семь подпрограмм из примеров, демонстрирующих работу циклов For… Next.
Цикл с дробными аргументами
Атрибуты start, end и step могут быть представлены числом, переменной или числовым выражением:
For i = 1 To 20 Step 2 For i = a To b Step c For i = a – 3 To 2b + 1 Step c/2 |
В результате вычисления значения переменной вне цикла или выражения внутри его может получиться дробный результат. VBA Excel округлит его до целого числа, используя бухгалтерское округление:
‘Значения атрибутов до округления For i = 1.5 To 10.5 Step 2.51 ‘Округленные значения атрибутов For i = 2 To 10 Step 3 |
Старайтесь не допускать попадания в тело цикла For… Next неокругленных значений аргументов, чтобы не получить непредсказуемые результаты его выполнения. Если без дробных чисел не обойтись, а необходимо использовать обычное округление, применяйте в коде VBA функцию рабочего листа WorksheetFunction.Round для округления числа перед использованием его в цикле For… Next.
Источник
Встречаются ситуации, когда от программы VBA требуется совершить несколько раз подряд один и тот же набор действий (то есть повторить несколько раз один и тот же блок кода). Это может быть сделано при помощи циклов VBA.
К циклам VBA относятся:
- Цикл For
- Цикл Do While
- Цикл Do Until
Далее мы подробно рассмотрим каждый из этих циклов.
Оператор цикла «For» в Visual Basic
Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each.
Цикл «For … Next»
Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:
Total = Total + iArray(i)
Next i
В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.
В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение 1. Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.
dTotal = dTotal + d
Next d
Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.
Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:
iArray(i) = i
Next i
Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.
Цикл «For Each»
Цикл For Each похож на цикл For … Next, но вместо того, чтобы перебирать последовательность значений для переменной-счётчика, цикл For Each выполняет набор действий для каждого объекта из указанной группы объектов. В следующем примере при помощи цикла For Each выполняется перечисление всех листов в текущей рабочей книге Excel:
For Each wSheet in Worksheets
MsgBox “Найден лист: ” & wSheet.Name
Next wSheet
Оператор прерывания цикла «Exit For»
Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.
Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:
If dValues(i) = dVal Then
IndexVal = i
Exit For
End If
Next i
Цикл «Do While» в Visual Basic
Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:
Sub Fibonacci()
Dim i As Integer ‘счётчик для обозначения позиции элемента в последовательности
Dim iFib As Integer ‘хранит текущее значение последовательности
Dim iFib_Next As Integer ‘хранит следующее значение последовательности
Dim iStep As Integer ‘хранит размер следующего приращения
‘инициализируем переменные i и iFib_Next
i = 1
iFib_Next = 0
‘цикл Do While будет выполняться до тех пор, пока значение
‘текущего числа Фибоначчи не превысит 1000
Do While iFib_Next < 1000
If i = 1 Then
‘особый случай для первого элемента последовательности
iStep = 1
iFib = 0
Else
‘сохраняем размер следующего приращения перед тем, как перезаписать
‘текущее значение последовательности
iStep = iFib
iFib = iFib_Next
End If
‘выводим текущее число Фибоначчи в столбце A активного рабочего листа
‘в строке с индексом i
Cells(i, 1).Value = iFib
‘вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1
iFib_Next = iFib + iStep
i = i + 1
Loop
End Sub
В приведённом примере условие iFib_Next < 1000 проверяется в начале цикла. Поэтому если бы первое значение iFib_Next было бы больше 1000, то цикл бы не выполнялся ни разу.
Другой способ реализовать цикл Do While – поместить условие не в начале, а в конце цикла. В этом случае цикл будет выполнен хотя бы раз, не зависимо от того, выполняется ли условие.
Схематично такой цикл Do While с проверяемым условием в конце будет выглядеть вот так:
…
Loop While iFib_Next < 1000
Цикл «Do Until» в Visual Basic
Цикл Do Until очень похож на цикл Do While: блок кода в теле цикла выполняется раз за разом до тех пор, пока заданное условие выполняется (результат условного выражения равен True). В следующей процедуре Sub при помощи цикла Do Until извлекаются значения из всех ячеек столбца A рабочего листа до тех пор, пока в столбце не встретится пустая ячейка:
Do Until IsEmpty(Cells(iRow, 1))
‘Значение текущей ячейки сохраняется в массиве dCellValues
dCellValues(iRow) = Cells(iRow, 1).Value
iRow = iRow + 1
Loop
В приведённом выше примере условие IsEmpty(Cells(iRow, 1)) находится в начале конструкции Do Until, следовательно цикл будет выполнен хотя бы один раз, если первая взятая ячейка не пуста.
Однако, как было показано в примерах цикла Do While, в некоторых ситуациях нужно, чтобы цикл был выполнен хотя бы один раз, не зависимо от первоначального результата условного выражения. В таком случае условное выражение нужно поместить в конце цикла, вот так:
…
Loop Until IsEmpty(Cells(iRow, 1))
Оцените качество статьи. Нам важно ваше мнение:
Источник