Как подсчитать количество строк в массиве ТОЛЬКО ЕСЛИ сумма чисел для каждой строки> 0 в Excel?

Question

У меня есть массив из 150 столбцов х 360 строк со случайными числами (скажем, от А2 до ET361) в Excel.

Как рассчитать для каждого столбца (т. Е. От ячейки B1 до ET1), сколько строк больше нуля для столбцов до него?

Критерии:

B1 необходимо рассчитать количество ячеек (от A2 до A361), которые> 0.
C1 должен вычислить количество строк (A2:B2, A3:B3, ..., A361:B361), где сумма каждой строки> 0.
D1 должен вычислить количество строк (A2:C3, ..., A361:C361), где сумма каждой строки> 0.

Я попытался использовать формулу COUNTIF, но она возвращает только количество ячеек, а не количество строк.
Я думаю, что мне нужны вложенные формулы ROWS() и IF()? Я также не хочу создавать еще одну матрицу 150 x 360 для решения этой проблемы, поскольку хочу сэкономить место в своем файле Excel.

Я также не хочу использовать макросы и VBA, поскольку они усложняют мою электронную таблицу.

---

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

Мне нужно для каждой ячейки в матрице вычислить количество строк над ней, для которых сумма столбцов для каждой строки больше нуля. Решение Барри в этом случае не будет работать (я проверял его), так как формула «промежуточный итог» не работает для ячеек, имеющих формулу «промежуточный итог».

Есть ли у нас другие альтернативы?

Answer 1

Хотя я не смог придумать решение с одной формулой (может быть, кто-то еще!), Я придумал что-то, что занимает гораздо меньше места в электронных таблицах, чем другая матрица 150 x 360.

Основная идея состоит в том, чтобы вычислить кумулятивные итоги в каждой строке для одного столбца данных, а затем использовать их в таблице данных («анализ« что, если »») для генерации счетчиков для всех столбцов.

Отправной точкой является столбец вычислений для строк в одном столбце данных.

Как показано на скриншоте ниже, я настроил рабочий лист с 10 столбцами данных.

Вспомогательная колонна

Справа от данных я установил вспомогательный столбец L.

Ячейка L1 содержит COUNTIF строк в этом столбце, сумма которых больше нуля.

Для сумм строк вместо простой суммы столбцов в каждой строке (опять же, только для столбца A) я использую сумму диапазона, возвращаемого функцией OFFSET . Эта функция имеет вид

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

Ячейка L3 имеет первое из выражений SUM(OFFSET(...)) . Он вычисляет сумму строк для диапазона, который составляет 0 строк вниз от ячейки A2 и 0 столбцов справа, с высотой 1 строки и шириной, равной значению в ячейке L2. В этом случае L2 имеет значение 1.

Эта формула копируется в 360 строк, в каждом случае вычисляется сумма высоты строки в диапазоне 1 и ширины, определяемой значением в ячейке L2.

Например, если значение в L2 было изменено на 2, то формулы в столбце будут вычислять суммы по строкам значений в столбцах A и B для каждой из 360 строк. И ячейка L1 будет показывать количество строк в диапазоне A2:B361 с суммой, большей 0.

Таблица данных

Функциональность таблицы данных Excel позволяет быстро определить влияние на вычисление изменения значения одного (или двух) входных данных для этого расчета. Он настраивается с помощью кнопки « What-If Analysis в разделе « Data Tools Data » на вкладке « Данные » на ленте.

На прилагаемом рисунке показана настройка таблицы данных.

Таблица данных будет создана в диапазоне R1:S10. В верхней части таблицы в ячейке S1 находится ячейка результата, для которой будут меняться входные данные. В этом случае ячейка результата содержит формулу =L1 , которая является просто ссылкой на формулу COUNTIF в верхней части вспомогательного столбца L.

Я предварительно ввел значения «что-если» в ячейках R2:R10. Показанные значения - 1, 2, ..., 9 - представляют ширину диапазонов, которые вернет OFFSET. И "входная ячейка столбца" - это ячейка L1 , ячейка, которая определяет ширину строк, которые суммируются в столбце помощника.

В двух словах, мы вводим ширину 1-9 (эквивалентно столбцам "A", «A:B», «A:C» и т.д.), А таблица данных вычисляет количество строк, сумма которых превышает 0 для каждого из этих промежутков столбца.

Последняя картинка показывает окончательные результаты. Таблица данных рассчитала количество строк для каждого столбца входных данных, т. Е. Количество построчных сумм (предыдущих столбцов), которые больше 0. Эти подсчеты были возвращены в ячейках S2:S10 таблицы данных. Я перенес подсчеты в первый ряд исходных данных, используя функцию TRANSPOSE .

Пример рабочего листа со всеми расчетами доступен здесь.

Answer 2

Функция OFFSET позволяет вам выделять отдельные строки в пределах диапазона ... и затем вы можете суммировать каждую строку с помощью SUBTOTAL и считать строки> 0 с помощью SUMPRODUCT , поэтому эта формула в B1, скопированная по всей строке , должна выполнять работу без вспомогательных ячеек

=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)

При этом используется методика, аналогичная описанной здесь [здесь нет фильтрации, но необходимо использовать SUBTOTAL для суммирования каждого диапазона, сгенерированного OFFSET]

Это даст вам те же результаты, что и решение Чаффа

Answer 3

Если я правильно понимаю, что вы спрашиваете, вы хотите, чтобы в верхней строке для каждого столбца отображалось общее количество отдельных ячеек со значением> 0 во всех столбцах перед ним. Правильно?

Если это так, это довольно просто, используя CountIf и знак $ чтобы заблокировать ссылку.

В ячейку B1 =CountIf($A2:A361,">0") . Нажмите и перетащите вправо. Знак $ блокирует A так, что он всегда считает все между столбцом A и текущим столбцом. Формула будет выглядеть следующим образом:

• C1 =Countif($A2:B361,">0")
• D1: =Countif($A2:C361,">0")
• E1: =Countif($A2:D361,">0")
• так далее...

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