313

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

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

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

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

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

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

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

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

Excelling at Excel вып.2: Циклы в Excel без VBA 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 Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

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

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

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

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

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

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


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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

Найдены возможные дубликаты

+6

Я когда ваял в экселе производство растворителей, такие советы были для меня бесценными.

Но я их находил, в основном, на пленетеэксель (не сочтите за рекламу).

А на пикабу это как то специфично выглядит, тем более второй пост подряд.

Плюсану, конечно, но жду таки котиков и сисек, ну или байку какую нибудь )))

раскрыть ветку 9
+9
Иллюстрация к комментарию
+6

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

И увы, никаких котиков и сисек тут. Только эксель, только хардкор:)

раскрыть ветку 7
+1
Привет, нужен совет. На работе есть табель учеба рабочего времени. Нужно посчитать сколько раз сотрудник выходил в выходные и праздничные дни. В такие дни сотрудник что-то пишет в данной ячейке, соответвующей дню. Но иногда пишет и в других ячейка пл рабочим дням. я пытался посчитать все непустые ячейки, но как из них выделить только непустые по выходным и праздникам? Все выхи и праздники имеют свое форматирование, отличное от остальных ячеек.
раскрыть ветку 6
+3

Интересная статья, но без острой потребности вникать лень.

+2

@ArtemTabolin, привет!

Внезапно для решения рабочих вопросов потребовалось резко изучить VBA. Можешь подсказать книги/курсы/видео для поверхностного изучения синтаксиса и общей логики языка?

раскрыть ветку 5
0

изучал сам, без учебников. просто решал конкретную задачу. плюс был опыт программирования на php и javascript. поэтому логика там схожая...

В интернете полно ресурсов, где можно почитать.

а так синтаксис довольно-таки простой. Через точку пишете адрес объекта, к которому обращаетесь или к свойству этого объекта. Например, ячейки: Лист.Адрес.Свойство:

Sheets("Лист1").Range("A1").Font

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

Вообще очень помогает макро-рекордер. Он правда часто пишет несколько более пространный код, но это удобно, если не знаешь как делать.

Из конструкций обязательно надо изучить IF ... Then и циклы...

Задача какая?

раскрыть ветку 4
0

Пишем прототип бизнес-игры (экономическая стратегия для 24 пользователей по сети)

раскрыть ветку 3
+1

=MOD(ROW(A1), 4)+1

И растягиваем вниз

+1

Во-первых, @ArtemTabolin, спасибо - делаешь хорошее дело!

Во-вторых - сначала я зашел в этот пост, а потом уже заглянул в первый, т.к. первый пропустил, т.к. подумал, что это очередной рекламный пост - меняй название))

В-третьих, явно многие знают, но, на всякий случай допишу - гугл.транслейт прекрасно понимает функции экселя и переводит их на многие (многие, т.к. я не тестил все), и это супер полезно, т.к. самые точные мануалы именно на русском. Всю функцию, конечно, вряд ли переведет, но если по частям - не вопрос, понимает.

раскрыть ветку 1
0

Спасибо.

0

А где пример файла что бы вы живую поглядеть формулы?

раскрыть ветку 1
0

Если вникнуть и понять изложенный подход, готовый файл Вам не понадобиться.

0

А не легче это все будет делать функциями SUMIFS , COUNTIFS и подобными (сорри, не в курсе как они на русском). Или версия екселя только старая доступна?

раскрыть ветку 2
0

Уточните, пожалуйста, как Вы хотите использовать COUNTIFS (СЧЕТЕСЛИМН)?

раскрыть ветку 1
0

COUNTIFS отлично подходит для проверки правильности подсчета. Всегда ставлю во вторую (можно скрытую колонку).

0

Если честно, то меня эксел бесит :)

Просто бррр!..

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

Я прям благоговею перед их создателями, считаю их просто монстрами. И одновременно мне их жалко, жалко их потраченный впустую труд. Что люди не делают, лишь бы не изучать VBA  и SQL :)

И я показываю, им как все эти ужасные формулы на несколько строк переписываются маленьким скриптиком в VBA редакторе.

А если еще на компьютере есть MS Access то, тут вообще возможна истинная магия.

раскрыть ветку 8
0

на одном из проектов заказчик прямо просил не использовать VBA, чтобы простой пользователь мог так или иначе понять логика и проследить ее в ходе трансформации данных. VBA хорош для невидимой черновой работы. Когда же есть большой объем интерактивности с юзером VBA не очень подходит.

раскрыть ветку 5
0
Очень я сомневаюсь, что нагромождение всяческих ВПР  и  СЦЕПИТЬ, ЕСЛИОШИБКА, ЧИСЛО

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

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

раскрыть ветку 4
0

Учить этот ваш VBA труд ещё больший. ну всем дано программистами быть. Для меня это все - пытка просто.

раскрыть ветку 1
0

я знаю, я сам через это прошел.

Иллюстрация к комментарию
0

Подобное вроде решается через индекс, счётесли и поискпоз, без каких-то дополнительных столбцов. Разве нет?

раскрыть ветку 1
+1

Приведите пример по возможности...

0

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

раскрыть ветку 2
+1

Не всегда. Иногда у Вас много разных источников, пусть и имеющих отдельные общие данные (поля, "ключи")

раскрыть ветку 1
0

Для задачи из поста 1 таблицы хватит точно. Но для непосвящённых в Excel и вообще структуру данных это будет выглядеть дичью, но это уже другая проблема

0

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

0

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

раскрыть ветку 30
0

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

раскрыть ветку 29
0

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

раскрыть ветку 28
Похожие посты
Похожие посты не найдены. Возможно, вас заинтересуют другие посты по тегам: