Как суммировать все значения в столбцах, используя заданную периодичность столбцов?
Поставлена задача: произвести суммирование всех значений в столбцах, придерживаясь определенного периода, который задан в отдельной ячейке.
Т.е., если в ячейке стоит число — 2, то суммировать значения каждого второго столбца таблицы, если стоит число — 3, то каждого третьего; и т.д.
Бывает так, что таблица состоит из списка с текстовыми обозначениями и цифрами напротив них, вот их и нужно как-то просуммировать, минуя столбцы с текстом.
=SUM(FILTER(C12:G21;MOD(COLUMN(C12:G21);$B$5)=0))
- $B$5 — ячейка с заданным периодом — 3. На примере суммируем каждый 3й столбец.
- C12:G21 — заданный диапазон, из которого будут суммироваться столбцы.
- Функция COLUMN — COLUMN(C12:G21) — определяет порядковый номер первого столбца из выбранного диапазона C12:G21 = 3 (столбец C — 3й по счету).
- Функция MOD — MOD(COLUMN(C12:G21);$B$5) — определяет остатки от деления каждого 3его столбца, начиная от столбца — A: берет порядковый номер первого столбца из диапазона C12:G21 — 3 и делит на значение из ячейки $B$5 — 3, получая = 0, так как остатка от деления 3/3 — нет, точно так же, как 6/3 (следующий 3й столбец F).
- Функция FILTER — FILTER(C12:G21;MOD(COLUMN(C12:G21);$B$5)=0) — берет заданный диапазон с данными C12:G21 и отображает все значения из столбцов C и F, так как они определились функцией MOD по нулевым остаткам от деления (т.е., они являются каждым 3м столбцом в диапазоне C12:G21, начиная от столбца A).
- Функция SUM — суммирует то, что отфильтровано функцией FILTER: все значения из каждого 3его столбца в диапазоне C12:G21. В столбце C = 10; в столбце F = 40; по сумме все вместе = 50.
Данные будут суммироваться только в пределах выбранного диапазона — C12:G21. Если другие столбцы будут заполнены данными (столбцы I; L) — формула их проигнорирует.
Минус данного метода в том, что отсчет столбцов идет со столбца A = 1, а не со столбца C (если мы за основу нашей базы данных берем выделенный диапазон C12:G21).
Как суммировать все значения в столбцах, используя заданную периодичность столбцов в пределах выбранного диапазона?
У нас есть определенный диапазон C12:G21, который начинается не с первого столбца таблицы (не со столбца — A). Как просуммировать все данные из определенных столбцов в пределах выбранного диапазона с данными?
=SUM(FILTER(C12:G21;MOD(SEQUENCE(1;COLUMNS(C12:G21));$B$5)=0))
- $B$5 — ячейка с заданным периодом = 3. На примере нам надо просуммировать все значения, находящиеся в каждом 3м столбце выбранного диапазона.
- C12:G21 — выбранный диапазон с данными. Таблица, из которой мы будем суммировать числа, находящиеся в каждом 3м столбце.
- Функция COLUMNS — COLUMNS(C12:G21) — определяет количество столбцов в диапазоне C12:G21 = 5 (C = 1; D = 2; E = 3; F = 4; G = 5).
- Функция SEQUENCE — SEQUENCE(1;COLUMNS(C12:G21)) — строит прогрессию с заданным шагом: в данном случае, она в строку записывает порядковые номера столбцов, определенные функцией COLUMNS: 1, 2, 3, 4, 5.
- Функция MOD — MOD(SEQUENCE(1;COLUMNS(C12:G21));$B$5) — определяет остатки от деления порядковых номеров столбцов в диапазоне C12:G21: какие выстроила в строку функция SEQUENCE: берет порядковый номер столбца C = 1, делит на значение из ячейки $B$5 — 3 => 1/3 = 0,333333 (0,3 в периоде) — остаток от деления = 1; и так перескакивает по всем порядковым номерам столбцов до столбца G.
- Функция FILTER — FILTER(C12:G21;MOD(SEQUENCE(1;COLUMNS(C12:G21));$B$5)=0) — фильтрует весь диапазон с данными C12:G21 и выводит каждый 3й столбец из выбранного диапазона — столбец E в данном случае. Он определяет его по остатку от деления, равной 0 (через функцию MOD).
- Функция SUM — суммирует все числа каждого 3его столбца, которые отфильтровала функция FILTER = 30 (в столбце E — 3*10 = 30).
В данном методе учитываются порядковые номера столбцов только в пределах выбранного диапазона C12:G21 (беря столбец C = 1), а не как в предыдущем варианте (со столбца A = 1).
