Excelling at Excel вып.2: Циклы в Excel без VBA⁠⁠

Немного теории. Циклом называется конструкция, которая некоторое (определяемое) количество раз выполняет заданные действия. Например, Вам нужно перебрать некий массив данных и выделить в нем пустые поля. В программировании это реализуется при помощи циклов. В VBA наиболее частым вариантом является конструкция For i = 0 to n … Next i.

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

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

Задача: свести это в одну таблицу для последующей обработки через ту же сводную таблицу. То есть требовалось получить вот такое представление:

Можно конечно было сделать это все руками, но предполагалось, что количество проектов и данные в таблицах могут меняться, поэтому нужно было придумать гибкое решение.

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

В нашем примере получалось три цикла (в порядке от младшего к старшему): тип исполнителя (цикл 1), статья затрат (цикл 2), проект (цикл 3). Алгоритм выглядит примерно так:

Цикл 3 (проект)

Цикл 2 (статья)

Цикл 1 (тип исполнителя)

Конец цикла 1

Конец цикла 2

Конец цикла 3

Так бы примерно выглядела бы и структура кода VBA для реализации этих трех циклов, но в самом Excel так сделать нельзя. Что же делать?

Давайте еще раз обратимся к сути цикла: это повторение какого либо действия определенное количество раз. Теперь рассмотрим это на примере одного цикла – цикла 1 (тип исполнителя).

Допустим, у нас 4 возможных типа исполнителя. Они у нас на отдельном листе «Тип исполнителя». Соответственно, нам надо перебрать все эти четыре значения по одному. Как? Во-первых, мы должны определить, что их именно 4. Для этого воспользуемся функцией COUNTA (СЧЁТА).

ВАЖНО! Не забудем вычесть заголовок.

Во-вторых, нам надо оформить перебор значений от 1 до 4. Вернее, до значения полученного из COUNTA (СЧЁТА). Это именно столько «шагов» должен сделать наш цикл.

Увы, без вспомогательных столбцов здесь не обойтись. Добавляем их слева от результирующей таблицы и в первой строке в ячейке А2 смело ставим 1. В ячейке А3 и ниже мы пропишем следующую формулу:

Получаем бесконечное повторение от 1 до 4. Теперь нам остается получить значение на каждому «шагу» цикла. Это можно сделать при помощи функции OFFSET (СМЕЩ), в которой значения столбца А мы будем использовать в качестве второго параметра (смещение по строкам).

Теперь добавим второй цикл – цикл 2 (статья). Подход такой же за исключением одного «НО»: переключать значение мы будем не сразу после предыдущего как в цикле 1, а по достижении максимального значения в цикле 1 (тип исполнителя). Для этого нам нужно формула, описывающая такую логику:

«Если значение типа исполнителя равно количество типов, то

если предыдущее значение статьи равно количеству статей, то 1,

если не равно, то предыдущее значение + 1,

если не равно, то предыдущее значение».

Вот так это выглядит в экселе:

Цикл 3 (проект) оформляется схожим образом с циклом 2 (статья). Но «триггером» для переключения на новое значение будет уже два условия одновременно: максимальное значение количества статей и максимальное количество типов исполнителей. В формуле выполнение этих двух условий мы оформим через функцию AND (И) равную TRUE (ИСТИНА).

Осталось только добавить формулы СМЕЩ в ячейки с данными.

Необходимо не забыть «остановить» цикл. В противном случае вы получите то, что ниже:

Чтобы этого избежать в формуле в столбце А мы специально вставили в одном из возможных исходов значение «» (пусто), чтобы этим самым «остановить» бесконечный цикл. Теперь при протягивании формулы будут выводиться пустые ячейки. В формул остальных ячеек (в т.ч. со СМЕЩ) следует добавить:

Собственно, все. Теперь можно спокойно создавать на основе полученной таблицы сводную таблицу. Единственное, что придется в фильтре убрать пустые значения.