Использование формул массива в диапазонах ячеек и расширение в двух направлениях

У меня есть таблица, в которой отслеживаются участники серии текущих исследований. На один лист (Данные) я вручную вношу имена участников каждого исследования. Другой лист (История исследования) должен автоматически заполняться историей каждого участника и общим количеством исследований.

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

Я хотел бы использовать формулу массива (или что-то еще, если бы это работало лучше), чтобы ОБА бесконечно расширять формулы в столбцах BG истории исследования ВНИЗ, чтобы включать новых добавленных участников, А ТАКЖЕ, КАК расширять формулы в столбцах EG ВПРАВО , чтобы включить новые исследования. Однако из-за того, что формулы уже содержат диапазоны (например, в функциях COUNTIF), я не могу заставить формулы массива работать должным образом. Я также не могу понять, как расширить массив в двух направлениях, но, возможно, я тусклый.

Таблица здесь.


google-sheets arrays spreadsheet array-formulas
person Matt    schedule 24.11.2015    source источник
comment
Добро пожаловать в СО. То, как вы задаете вопрос, немного затрудняет вам помощь, потому что, по сути, нет ничего конкретного, с чем нужно было бы делать/помочь. Можете ли вы опубликовать то, что вы пробовали?   -  person lhcgeneva    schedule 25.11.2015
comment
Итак, что касается расширения ячеек COUNTIF вниз, B8 в настоящее время =COUNTIF(E8:8,Unmoderated). Как правило, для формулы массива я бы сделал это =ARRAYFORMULA(COUNTIF(E8:8,Unmoderated)), однако он уже имеет диапазон E8:8 в исходной формуле, поэтому она не работает. Не знаю, как применить диапазон к диапазону.   -  person Matt    schedule 25.11.2015

Ответы (1)


arrow_upward
0
arrow_downward

Ваша цель может быть достигнута многими способами, ключевой проблемой всегда будет волатильность.

Например: Динамическое определенное имя можно использовать для получения диапазона данных с листа DATA (определенного как =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))). Тем не менее, использование функции OFFSET делает динамическое определяемое имя непостоянным и, следовательно, все формулы на листе Study History указывают на него.

В этом решении предлагается использовать функцию ИНДЕКС, чтобы избежать волатильности:

Предположим, что диапазон данных на листе DATA начинается с A1 и это непрерывный диапазон данных (т. е. между ними нет пустых строк и пустых столбцов).

Введите эту формулу в E1, затем скопируйте в E2:E3 и до последнего столбца с записями.

= T( Data!A1 )

Введите эту формулу в E4, затем скопируйте до последнего столбца с записями.

= IF( E$1 = "", "",
COUNTIF( E$8  : INDEX(E:E, 1 + ROW( E$7 ) + COUNTA($A:$A ) - COUNTA( A$1:A$7 ) ), E$3 ) )

Введите эту формулу в E5, затем скопируйте до последнего столбца с записями.

= IF( E$1 = "", "", IF( E$5 = "", "Enter Invited", E$4 / E$5 ) )

Введите эту формулу в B8, затем скопируйте в C8 и до последней строки с записями.

=COUNTIF( $E8 : INDEX(8:8, - 1 + COLUMN( $E8 ) + COUNTA( Data!$1:$1 ) ), B$7 )

Введите эту формулу в D5, затем скопируйте до последней строки с записями

=SUM( $B8:$C8 )

Введите эту формулу в E8, затем скопируйте до последней строки и столбца с записями.

=IF( OR( E$1 = "", $A8 = "" ), "",
IFERROR( CHOOSE( 1 + COUNTIF( Data!A:A, $A8 ), "", E$3 ), "!Err" ) )
person EEM    schedule 26.11.2015