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, Цикл, Без макросов, Длиннопост
Вы смотрите срез комментариев. Показать все
Автор поста оценил этот комментарий

Ребят, как табель в экселе посчитать, если руководство требует записей типа 7/5 в одной клетке (7 часов в день, из них 5 ночных)?

раскрыть ветку (30)
Автор поста оценил этот комментарий

Легко. Формула выглядит так: [что-то1]&"/"&[что-то2], где вместо [что-то1] и [что-то2] формулы с расчетами или ссылки на ячейки. Например, A1&"/"&A2

раскрыть ветку (29)
Автор поста оценил этот комментарий

Нифига не понял. Где можно про это почитать? Я сам "7/5" делаю текстовым, а в сумме возвращаю к числу через СУММЕСЛИ, но хочется чисто математическое решение.

раскрыть ветку (28)
Автор поста оценил этот комментарий

У Вас проблема посчитать или данные представить? Если данные изменяемые в конечной ячейке, то формула выглядит к примеру так СУММЕСЛИ(...)&"/"&СУММЕСЛИ(...)

раскрыть ветку (27)
Автор поста оценил этот комментарий

Сложно объяснить, что мне нужно. Есть массив-строка из текста вида 11, 10, 7/5,4/2, 6/5 ,3/2 и так далее. Нужно отдельно посчитать сумму из целых чисел и чисел слева от слеша(дневные часы) и сумму правых от слеша чисел (ночные часы).

раскрыть ветку (26)
Автор поста оценил этот комментарий

Чтоб вам уже пусто было с вашими колхозно-уебанскими представлениями данных!!!! Сука, 21 век на дворе, а вы до сих пор в экселях пишете так, словно мемориальный ордер и сличительную ведомость в заготконторе Улуковского филиала Гомельского райпо заполняете!!!

Короче, либо формула суммирования будет выглядеть также уебански, как ваше колхозное предсавление данных, либо относительно простыми формулами "если" совместно с текстовыми раскидываем столбец на 2 вспомогательных с ночными и дневными часами по отдельности, а там уж хоть автосуммированием суммируйте -- все элементарно будет. Первый вариант с уебанской формулой  не проверял.

раскрыть ветку (4)
Автор поста оценил этот комментарий

Братка, ты не ругайся. Я, тащем то, инженер, а меня заставили делать графики и табеля,причем именно в екселе. Мне что, их вручную считать?

раскрыть ветку (3)
Автор поста оценил этот комментарий
Вобщем, я за второй способ: 2 дополнительных скрытых столбца в которых ваши дроби  раскладываются на ночные и дневные. Так проще проверить и довольно легко реализовать.
Иллюстрация к комментарию
раскрыть ветку (2)
Автор поста оценил этот комментарий

О, спасибо, братка. Порадовал юмором и дал отличный совет. Попробую попробовать.

раскрыть ветку (1)
Автор поста оценил этот комментарий

Со вторым столбцом косякнул. Там такая формула должна быть =ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК("/";A1));0)*1

Автор поста оценил этот комментарий
А чем обосновано требование записывать числа дробью именно в одной клетке? Почему нельзя выделить 2 столбца под это дело? Или, например, даже три: в двух будут записываться числитель и знаменатель раздельно для расчетов, а в третьем - выводиться текстом в той форме, какая нужна для печати.
раскрыть ветку (19)
Автор поста оценил этот комментарий

Форма, братка, такая! Предоставлена вышестоящей бухгалтерией. Шаг влево, шаг права - много визгов и криков. А самое бредовое то, что бух берет мой табель, распечатывает и поклеточно перепечатывает в 1C. На мой вопрос - какого хера я должен делать именно в такой форме,  ответ - а вдруг прокурорская проверка и у нас твой табель не по форме.

раскрыть ветку (18)
Автор поста оценил этот комментарий

Так тебе это в штатном табеле  формы Т-13 надо считать?

раскрыть ветку (17)
Автор поста оценил этот комментарий

Да. Немного только шапку изменили. Твоя формула, к сожалению, не работает.

раскрыть ветку (15)
Автор поста оценил этот комментарий
Чтоб моя формула да не работала??? Я ж ее сам проверил! Кинь файл на файлообменник какой -- посмотрю
раскрыть ветку (13)
Автор поста оценил этот комментарий

Я уже с работы ушел. Теперь в понедельник, если не забуду. Но все равно спасибо.

Автор поста оценил этот комментарий

Попросил парней с работы помочь. Вот ссылка https://cloud.mail.ru/public/GcE1/2rnG6KuXa

раскрыть ветку (11)
Автор поста оценил этот комментарий

@NObiniak, попробуйте формулу массива.

Для получения дневных:

{=SUM(IFERROR(LEFT("массив данных";SEARCH("/";"массив данных")-1)/1;"массив данных"))}

для получения ночных:

=SUM(IFERROR(RIGHT("массив данных";LEN("массив данных")-SEARCH("/";"массив данных"))/1;0))

Автор поста оценил этот комментарий
Иллюстрация к комментарию
Автор поста оценил этот комментарий

Попробуйте формулу массива сделать. А содержание типа: сумм если в ячейке есть / то значение левее слеша, если нет, то целиком

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