Найти строку, где сумма Достигнута из единого объединенного столбца (а не диапазон ячеек)

Вопрос задан: 2 месяца назад Последняя активность: 2 месяца назад
up 1 down

Я пытаюсь запустить формулу для определения, в котором строка общая сумма будет достигнута.

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

Ниже приведен пример Google лист (редактируемый - не стесняйтесь) где вы можете увидеть диапазон и рабочую формулу (как ниже). Помощь получать это из версий одноклеточных на вершине было бы очень полезно. Я получаю ошибку как с строки() и индекс() формулы в том, что «аргумент должен быть диапазон».

Если есть другой способ сделать это, кроме одноклеточных я имел, что не требует привязки диапазона (например, с помощью фильтра), то я открыт к нему.

Мой желаемый результат должен быть в состоянии вытащить получить вторую колонку (дата) в момент, когда будет достигнута сумма (может быть по формуле INDEX & MATCH я использовал или альтернативы). Это говорит мне самую раннюю дату, впадающий нужной суммы.

1 ответ

Возможно, для Вашего проекта будут необходимы бесплатные векторные карты. На нашем сайте представлены карты для всех стран.

Реклама

up 2 down accepted

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

Единственный подход, который я знаю, это многократно последовательных значений с помощью треугольной матрицы, как это:

1 0 0 ...
1 1 0 ...
1 1 1 ...

так что вы получите только сумму первого значения, первые 2 значения, затем 3 значения до п.

Это формула в F5:

=ArrayFormula(match(E14,mmult(IF(ROW(A1:INDEX(A1:ALL1000,COUNT(split(A5,",")),COUNT(split(A5,","))))>=
COLUMN(A1:INDEX(A1:ALL1000,COUNT(split(A5,",")),COUNT(split(A5,",")))),1,0),TRANSPOSE(SPLIT(A5,",")))))

И формула F6 просто

=to_date(INDEX(TRANSPOSE(SPLIT(B5,",")),F5,1))

Найти строку, где сумма Достигнута из единого объединенного столбца (а не диапазон ячеек)

РЕДАКТИРОВАТЬ

Вы, наверное, догадались, что приведенная выше формула была заимствована из Excel, где вы пытаетесь избежать летучие функции, такие как смещение и Косвенное.

Я понял, так как разместить этот ответ, что он может быть улучшен двумя способами:

(1) При использовании Offset или Косвенный, что позволяет избежать необходимости определить диапазон произвольного размера, как A1: ALL1000

(2) По подразумевая 2D массив путем сравнения вектора строк и столбцов, а не на самом деле определения 2D массива. Это даст вам что-то вроде этого в F5:

=ArrayFormula(match(E14,mmult(IF(ROW(indirect("A1:"&address(COUNT(split(A5,",")),1)))>=
COLUMN(indirect("A1:"&address(1,COUNT(split(A5,","))))),1,0),TRANSPOSE(SPLIT(A5,",")))))

которые могут быть дополнительно упрощено до:

=ArrayFormula(match(E14,mmult(IF(ROW(indirect("A1:A"&COUNT(split(A5,","))))>=
COLUMN(indirect("A1:"&address(1,COUNT(split(A5,","))))),1,0),TRANSPOSE(SPLIT(A5,",")))))