Указание, какие ячейки анализирует формула, с помощью формулы
Извиняюсь за название, не знаю, как задать этот вопрос. Я пытаюсь указать диапазон данных, которые у меня есть в столбце Excel, где изменяется диапазон соответствующих значений. Таким образом, для визуализации данные могут быть в ячейках A1-A4 с нулями ниже (или данные в ячейках A1-A3, или A1-A25 и т. д.). Используя countif, я могу получить количество ненулевых значений в изменяющемся диапазоне, что полезно, поскольку указывает конечную строку, которую я хочу использовать для своего диапазона. Моя проблема заключается в том, как я использую это значение в формуле. В моей голове (если я помещу формулу countif в B1), диапазон будет выглядеть примерно так: $A$1:$A$(значение из B1), но я не уверен, как применить это на практике. Есть идеи?
Да, реальные данные. Данные основаны на операторе if, что-то вроде =IF(x=T, Y, "")
Ответы 2
Вы можете использовать функции INDIRECT или OFFSET.
Использование INDIRECT является более простым подходом: чтобы указать диапазон, начинающийся в столбце A, строке 1 и заканчивающийся в столбце A, строке COUNTIF(A:A,">0"), вы просто напишите INDIRECT("A1:A" & COUNTIF(A:A, ">0")) - так что, если есть 7 ячеек со значениями больше 0, это становится INDIRECT("A1:A7")
Использование OFFSET более сложное — вам нужно указать, сколько ячеек влево/вниз нужно переместить (0 и 0), сколько строк данных получить (COUNTIF(A:A, ">0")) и сколько столбцов данных получить (1), начиная с начальной страницы. position (A1) - поэтому вместо этого это выглядит примерно так: OFFSET(A1, 0, 0, COUNTIF(A:A, ">0"), 1)
Indirect и Offset являются изменчивыми, и хотя одна изменчивая формула не вызовет проблем, многие замедлят расчеты. Волатильные функции пересчитываются каждый раз, когда Excel пересчитывает, независимо от того, изменились данные или нет.
Я предпочитаю им ИНДЕКС:
$A$1:INDEX(A:A,COUNTIF(A:A,">0"))
Но даже COUNTIF() при слишком частом использовании может замедлить вычисления. Вы можете использовать ПОИСКПОЗ в этом случае:
$A$1:INDEX(A:A,MATCH(0,A:A,0)-1)
Который теперь найдет первый 0 и установит ячейку над ним как последнюю в диапазоне.
Значит, есть фактические данные ниже интересующего диапазона в виде нулей? Или остальная часть столбца пуста? Возможно, некоторые образцы данных и желаемый результат будут полезны для визуализации.