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

Question

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

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

Критерии:

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

Я пробовал использовать формулу СЧЁТЕСЛИ, но она возвращает только # ячеек, а не # строк.
Думаю, мне нужна вложенная формула ROWS() и IF()? Я также не хочу создавать еще одну матрицу 150 x 360, чтобы справиться с этой проблемой, так как хочу сэкономить место в файле Excel.

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

---

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

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

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

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

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

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

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

• С1=Countif($A2:B361,">0")
• Д1:=Countif($A2:C361,">0")
• Э1:=Countif($A2:D361,">0")
• и т. д...

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

Answer 3

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

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

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

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