Excel Analysis Blog
BETA
Придется немного поработать


Макросы. Урок восьмой

Циклы

Do While

Цикл For Next, безусловно, является очень хорошим и удобным. Но у него есть большой недостаток — нужно определять, начало и конец. Но ведь мы редко будем знать, какой размер таблицы нам придется обрабатывать. Более того, часто будет так, что для ежедневной отчетности размер будет ежедневно изменяться.

В таком случае, мы начинаем работать с моим любимым циклом Do While. Он позволяет выполнять действия до тех пор, пока не будет создано необходимого условия для его окончания. Пример.

Представим, что у нас есть несколько торговых точек в городе. Каждая работает по своему графику и приносит нам ежедневно разную выручку. Ежедневно, мы получаем отчет в таком виде:

Нам нужно узнать общую сумму, которую мы заработали за день. Естественно, делаем это макросом:

Sub test()
    Dim sht As Worksheet
    Dim a As Long
    Dim row As Long
    
    Set sht = ThisWorkbook.Worksheets("Лист1")
    
    a = 0
    For row = 2 To 5
        a = a + sht.Cells(row, 2) ‘таким нехитрым способом я делаю сложение общей суммы
    Next row
    
    MsgBox ("Сегодня мы заработали " & a & " рублей")
    
End Sub

Все нормально. Все работает и все правильно. Но теперь добавим условия, что если Точка в этот день не работает, то она отчет не присылает. А еще в любой момент может открыться новая торговая площадка. Вы понимаете, о чем я?

Имеется ввиду, что каждый день, будет приходить разный диапазон данных. Сегодня 5 строчек, завтра 1, послезавтра 10. Конечно, можно прописать, чтобы цикл For работал по всей странице, с 1 до 1 048 576 строки (максимальная с 2007 версии), но это лишняя трата времени. Цикл работает вхолостую. В этом случае, как раз будет удобнее использовать цикл Do.

Его синтаксис следующий:

  1. Do While — начало цикла.
  2. Условие — а именно, «выполнять цикл, пока выполняется условие»
  3. Loop — окончание цикла.
Sub test2()
    Dim sht As Worksheet
    Dim row As Long
    
    Set sht = ThisWorkbook.Worksheets("Лист1")
       
    row = 2 ' в этом случае, я заранее определяю начало цикла со-второй строки
    'я говорю, что нужно выполнять цикл, пока данные в ячейке не равны пустоте, т.е. пока там есть данные
    Do While sht.Cells(row, 2) <> ""
        'что именно делать. У меня сейчас стоит что значение Row должно быть увелично на 1
        row = row + 1
    Loop 'тут заканчиваю выполнение цикла
        
End Sub

Выполните код пошагово, чтобы понять его принцип. Он начинается со второй строки, где у нас прописана Точка 1. Затем переходит к следующей строке, где числится Точка 2. И т.д. На каждом шаге, наводите мышку на Row, в строке кода

Do While sht.Cells(row, 2) <> "".
Видно, что значение меняется. Меняется адрес ячейки. Когда он дойдет до 6 строки, где у нас нет ничего, то выполнение цикла прекращается.

И, разумеется, внутри цикла я мог прописан любые другие операции. Для нашего случая, это вычисление суммы.

Sub test3()
    Dim sht As Worksheet
    Dim a As Long
    Dim row As Long
    
    Set sht = ThisWorkbook.Worksheets("Лист1")
    
    a = 0
    
    row = 2
    Do While sht.Cells(row, 2) <> ""
        a = a + sht.Cells(row, 2) 'сначала мы прибавляем данные к сумме
        row = row + 1 'а затем увеличиваем значение Row
    Loop
    
    MsgBox ("Сегодня мы заработали " & a & " рублей")
End Sub

ПРИМЕЧАНИЕ. Удобная фишка. При выполнении цикла наводите мышку не на сам Row, он покажет только значение этой переменной, а на переменную sht. Тогда редактор покажет вам содержимое используемой в цикле ячейки.


Как мы видим, такой цикл более удобен, поскольку без разницы, сколько строчек занимает таблица, вычисления будут проходить по всем. Но бывает одна неприятность, когда у нас посередине таблицы образуется пустая строчка, в этом случае, для цикла это означает «остановить вычисления». К сожалению, универсального лекарства здесь нет. Многие программисты мучаются подобным. Это вопрос целостности данных, а не макроса. Если это будут ваши данные, которые вы сами получили, то, скорее всего, такого вы не допустите, а если от чужих людей… Ну можно, например, сначала удалить все пустые строчки.


В остальном цикл Do While хорош всем и используется многими повсеместно. Понятное дело, что условия его выполнения могут быть любыми. Например, нам не известно где начинаются данные. Да, именно так. Шаблон заполняет неумелый стажер, и сегодня он таблицу вставляет с первой строчки, завтра с 10 и т.д. Пример так:

Если я сейчас запущу цикл:

row = 2
Do While sht.Cells(row, 2) <> ""
     a = a + sht.Cells(row, 2) 'сначала мы прибавляем данные к сумме
     row = row + 1 'а затем увеличиваем значение Row
Loop

Он остановится сразу же, на первой строчке, т.е. он пустая. Для этого случая цикл нам придется прогонять два раза:

Sub test4() 'данные на Листе2
    Dim sht As Worksheet
    Dim a As Long
    Dim row As Long
    
    Set sht = ThisWorkbook.Worksheets("Лист2")
    
    a = 0
    
    row = 1
    Do While sht.Cells(row, 2) = ""
        row = row + 1
    Loop
    
    row = row + 1 'пропускаю заголовки
    
    Do While sht.Cells(row, 2) <> ""
        a = a + sht.Cells(row, 2)
        row = row + 1
    Loop
    
    MsgBox ("Сегодня мы заработали " & a & " рублей")
End Sub

Как вы видите, я делал по следующей логике:

Я не знаю, с какой строчки начинается код. Поэтому за старт я беру саму первую строку.

row = 1

Затем я иду по листу циклом Do While. Условием я ставлю, что его нужно выполнять до тех пор, пока строчки пустые.

Do While sht.Cells(row, 2) = ""
     row = row + 1
Loop

Этот цикл останавливается на 6 строке. Соответственно в этот момент Row=6. И теперь я знаю, где начало таблицы.

Но также я помню, что в самом начале, пишут заголовки. Т.е. на 6-ой строке данных нет, ее считать не нужно. Поэтому я переношу начало цикла еще на одну строчку вниз.

row = row + 1

И теперь, я могу дальше работать привычно, а именно:

Do While sht.Cells(row, 2) <> ""
     a = a + sht.Cells(row, 2)
     row = row + 1
Loop

Для условий цикла можно использовать и другие способы сравнения:

  • <>”” — не равен пустоте
  • =”” — равен пустоте
  • <>0 — не равен нулю (надеюсь все понимают, что ноль и пустота — это разные вещи)
  • <>2
  • . =3 — эти два пункта странные, но тоже возможны. Он так прекрасно будет работать.
  • >5 — ну это будет нормально, только если мы 100% уверены, что у нас все числа идут по порядку

  • И, разумеется, можно использовать переменные
  • <> переменная

Как вы скорее всего догадались, можно делать вложенные циклы. Только не забываем, что счетчик, скорее всего, для каждого нужен свой. Пример:

Нам нужно подсчитать, общую сумму дохода для каждого. Заранее видно, что количество статей доходов у всех различное. В итоге нам нужно получить следующую таблицу.

Делаем через цикл Do

Sub test5() 'данные на листе3
    Dim sht As Worksheet
    Dim a As Long
    Dim rowFIO As Long, rowDohod As Long
    Dim FIO As String
    
    Set sht = ThisWorkbook.Worksheets("Лист3")
    
    'здесь запускаем основноый цикл, который обработаем всю таблицу
    rowFIO = 2
    Do While sht.Cells(rowFIO, 1) <> ""
        <>'считываем имя сотрудника, заносим в переменную
        FIO = sht.Cells(rowFIO, 1)
        a = 0 'обнуляем переменную А. Для первого шага это необязательно, но ведь мы будет использовать ее для всех
                'сотрудников
        rowDohod = rowFIO 'начало внутреннего цикла
        Do While sht.Cells(rowDohod, 1) = FIO 'выполняем внутренний цикл, пока не изменится фамилия
            a = a + sht.Cells(rowDohod, 3) 'считаем сумму дохода
            rowDohod = rowDohod + 1 'переходим к следующей статье дохода
        Loop
        sht.Cells(rowDohod, 4) = a 'после того, как закончился цикл, по статьям дохода, записываем сумму в 4-й столбец
        
'если я сейчас пропишу конструкцию rowFIO = rowFIO + 1, то он перейдет на вторую строчку, а там тоже Иванов, мы его
'уже подсчитали. Поэтому я возьму, и присвою ему данные переменной, которая считала статьи доходов.
        rowFIO = rowDohod 'переходим к следующей фамилии
    Loop
End Sub

Пожалуйста, выполняйте этот код пошагово. Чтобы видеть, как и что меняется. Наводите мышку на переменные, смотрите, как они меняются.

Но, в результате, вы увидите, что сделано все немного не так.

Данные смещены вниз. Все дело в этой строчке (для примера берем Иванова):

Do While sht.Cells(rowDohod, 1) = FIO 
      a = a + sht.Cells(rowDohod, 3)
      rowDohod = rowDohod + 1 
Loop
sht.Cells(rowDohod, 4) = a 

Цикл Do шел до тех пор, пока в sht.Cells(rowDohod, 1) находится Иванов. В тот момент, когда rowDohod был равен 4. У нас была строка Иван, Аванс. Для него условия были приемлимы и он перешел на следующий шаг. В нем rowDohod стал равен 5.

На этом шаге, он увидел, что фамилия сменилась и прекратил выполнение цикла. Затем он должен был занести подсчитанную сумму в правый столбец, вот только rowDohod на тот момент уже равен 5. Поэтому и смещение. Решение данной проблемы, может быть запись суммы через конструкцию.

sht.Cells(rowDohod - 1, 4) = a

Приступаем к самостоятельной работе.

Скачать примеры урока

Скачать самостоятельное задание