Кевину нужно создать формулу, которая суммирует каждую четвертую ячейку подряд. Он знает, что может использовать такую формулу, как = A6 + E6 + I6 + M6 и т. Д., Но это становится громоздким, если на листе много столбцов.
Есть несколько способов. может подойти к этой проблеме. Один из способов – добавить на лист дополнительную информацию, чтобы указать, какие ячейки следует включить в сумму. Например, в этом примере вас интересует суммирование ячеек в строке 6 рабочего листа. Если вы можете добавить несколько индикаторов в строку 5, их можно будет использовать в формуле в качестве «триггеров». Например, введите цифру 1 над каждой ячейкой, которую вы хотите включить в сумму (столбцы A, E, I, M и т. Д.). Затем вы можете использовать следующую формулу:
= SUMPRODUCT (A5: X5, A6: X6)
Формула в основном умножает все, что находится в строке 5 по строке 6, а затем суммирует результаты. Поскольку в столбцах, которые вы хотите просуммировать, только единицы, это все, что входит в окончательную сумму.
Если вы не хотите добавлять строку индикатора на свой рабочий лист, вам необходимо посмотреть на разные решения. Вы по-прежнему можете использовать функцию СУММПРОИЗВ в следующей формуле:
= СУММПРОИЗВ ((MOD (COLUMN (6: 6), 4) = 1) * (6: 6))
Эта формула использует функцию MOD для возврата остатка от деления. В этом случае делится номер столбца ячейки на значение 4. В результате остаток будет равен 0, 1, 2 или 3. Каждая четвертая ячейка в строке будет иметь одинаковый остаток. Таким образом, столбец A (также известный как столбец 1) будет иметь значение MOD, равное 1 (1, деленное на 4, равно 0, с 1 оставшимся), как и столбцы E, I, M и т. Д.
Обратите внимание, что формула сравнивает, равно ли значение MOD 1 или нет. Если это так, то сравнение возвращает True (1); если это не так, возвращается False (0). Затем это умножается на ячейку в шестой строке. Наконец, СУММПРОИЗВ суммирует все эти умножения и дает желаемый результат.
Хотя эта формула дает сумму каждой четвертой ячейки в шестой строке, ее можно легко изменить, чтобы получить сумму для каждой третьей ячейки. , пятая ячейка или любой желаемый интервал. Просто измените 4 в функции MOD на желаемый интервал.
Если вы хотите выбрать другую ячейку в каждом «кластере» из четырех ячеек для суммирования, то все, что вам нужно сделать, это изменить значение, сравниваемое в функции MOD. В этом примере только первая ячейка в каждом кластере из четырех будет иметь MOD 1 (A, E, I, M и т. Д.). Если вместо этого вы хотите суммировать каждую четвертую ячейку, начиная, скажем, с ячейки C, тогда вы должны изменить значение сравнения с 1 на 3. Почему? Поскольку C является третьей ячейкой в кластере и будет иметь MOD 3, как и каждая четвертая ячейка после этого (G, K, O и т. Д.).
Единственное, что нужно для этого Общее правило состоит в том, если вы хотите суммировать четвертую ячейку в каждом кластере из четырех ячеек. Например, вы можете просуммировать ячейки D, H, L, P и т. Д.. В этом случае используемое значение сравнения не будет равно 4, поскольку при выполнении операции MOD, которая включает деление на 4, никогда не будет остатка 4. Вместо этого значение сравнения будет равно 0, как показано ниже:
= SUMPRODUCT ((MOD (COLUMN (6: 6), 4) = 0) * (6: 6))
Если вы предпочитаете работать с формулами массива, вы можете используйте несколько более короткий вариант приведенной выше формулы:
= SUM (IF (MOD (COLUMN (6: 6), 4) = 1,6: 6))
Обратите внимание, что формулу следует вводить, нажимая Ctrl + Shift + Enter . Затем он появится на панели формул в фигурных скобках ({}) вокруг формулы. Здесь применяются те же примечания по модификации, что и для делителя MOD и значения сравнения, что и для функции SUMPRODUCT.
Оба этих формульных подхода (SUMPRODUCT и формула массива) суммируют каждую четвертую ячейку во всем строка. Если вместо этого вы хотите ограничить количество ячеек, из которых выводится сумма, частью строки, просто замените 6: 6 (оба экземпляра) правильным диапазоном. Таким образом, если вы хотите суммировать только каждую четвертую ячейку в диапазоне A6: Z6, вы должны использовать этот диапазон в формуле.
Если вы делаете много суммирования таким образом, и вы примените его не только к диапазонам в строке, но и к диапазонам в столбце, вы можете подумать о создании пользовательской функции для суммирования. Следующая простая функция сделает свое дело:
Function SumEveryFourth (MyRange As Range) Dim x As Integer SumEveryFourth = 0 For x = 1 To MyRange.Cells.Count If (x Mod 4) = 1 Затем SumEveryFourth = SumEveryFourth + MyRange.Cells (x) .Value End If Next xEnd Function
Функция проверяет переданный ей диапазон, а затем суммирует каждую четвертую ячейку, начиная с первой ячейки в диапазоне . Если вы предпочитаете, чтобы он суммировал каждую вторую ячейку в диапазоне, измените значение сравнения в операторе If, как обсуждалось ранее в этом совете. (Поскольку в этой функции используется операция Mod, и она работает так же, как функция рабочего листа MOD, то для определения того, какая ячейка в каждом кластере должна быть суммирована, используются те же значения сравнения.)
Пользовательская функция отлично работает как с ячейками в строке, так и с ячейками в столбце. Вам просто нужно убедиться, что вы передаете ему нужный диапазон, как показано здесь:
= SumEveryFourth (C3: C57)
Источник: