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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

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

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

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

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

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

Конец цикла 1

Конец цикла 2

Конец цикла 3

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

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

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

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

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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


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

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

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

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


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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Microsoft Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост
Вы смотрите срез комментариев. Показать все
Автор поста оценил этот комментарий

Сохраню на всякий. Спасибо.

Вы смотрите срез комментариев. Чтобы написать комментарий, перейдите к общему списку