В Excel вы можете определить ячейку или диапазон для использования в формуле на основе содержимого другой ячейки или формулы?

Question

Что я хочу сделать, это:

• Пусть одна ячейка подсчитает количество элементов, присутствующих в определенном столбце, например, C1 = 129
• Пусть вторая ячейка использует это число как часть определения, например, D1 = AVERAGE($ A $ 1:$ A $(C1)), что означает ($ A $ 1:$ A $ 129)

Почему я хочу сделать именно это:

У меня есть формула, написанная на другом веб-сайте, которая подсчитывает количество различных элементов в столбце. Эта формула не потерпит пробелов, и (afaik) ее нельзя будет сделать для этого. Я использую эту формулу в еженедельном отчете, и количество элементов в столбце меняется от недели к неделе. Вместо того, чтобы вручную изменять значение в нескольких местах, где оно используется (как я делаю сейчас), я бы хотел просто добавить новые данные, чтобы лист подсчитал количество элементов и использовал это число в формуле со списком.

Вот формула, о которой идет речь:

=SUM(IF(FREQUENCY(MATCH(Data!A2:A100,Data!A2:A100,0),MATCH(Data!A2:A100,Data!A2:A100,0))>0,1))

Где "A100" - это нижний экстент столбца, заполненного данными, за эту неделю. На следующей неделе это может быть A200 или A50. Однако на этой неделе, если бы я сменил A100 на A101 (там, где нет данных), формула великолепно завершится ошибкой "Ошибка значения недоступна".

Любой способ сделать то, что мне нужно?

Answer 1

Используйте функцию рабочего листа INDIRECT().

В двух словах, формула =INDIRECT("A1") возвращает любое значение , содержащееся в ячейке A1 . Поэтому все, что вам нужно сделать, это заменить вместо "A1" это статическое значение любым вычислением, которое вы используете, чтобы получить строку / столбец ячейки. Результатом всей формулы будет значение, содержащееся в ячейке, указанной в аргументе функции рабочего листа INDIRECT() .

Вот упрощенный пример

Значение ячейки А1 = 1
Значение ячейки А2 = 2
Значение ячейки А3 = 3
Значение ячейки A4 = пусто / пусто
Значение ячейки A5 = =COUNT(A1:A3) (замените это на вашу формулу со шпаргалкой)
Значение ячейки A6 = =AVERAGE($A$1:INDIRECT("$A$" & INDIRECT("A5")))

Перейдите в "Формулы" -> "Оценить формулу" и просмотрите оценку, если хотите посмотреть, как она работает. По сути, он объединяет строку «$ A $» со значением "3". "3" получается из значения ячейки A5, которое равно 3, потому что подсчет 3 ячеек возвращает 3 в этом примере. Затем мы вызываем INDIRECT() второй раз, чтобы получить значение $A$3 , но на самом деле возвращается ссылка на ячейку! Если вы мне не верите, оцените формулу и посмотрите, во что Excel превращает вызов INDIRECT("$A$3") во время оценки. Это странно, но работает - INDIRECT() ведет себя по-разному в зависимости от того, выводится ли он в ссылку на диапазон или в значение.

Answer 2

Вместо INDIRECT вы также можете рассмотреть возможность использования INDEX . Предполагая, что ваш счет в J3, а данные в столбце A), эта формула будет суммировать все элементы: =SUM(A2:INDEX(A2:A10000,J3))

Для вашей конкретной проблемы это должно работать:

=SUM(IF(FREQUENCY(MATCH(Data!A2:INDEX(A2:A10000,J3),Data!A2:INDEX(A2:A10000,J3),0),MATCH(Data!A2:INDEX(A2:A10000,J3),Data!A2:INDEX(A2:A10000,J3),0))>0,1))

Огромным преимуществом INDEX по сравнению с INDIRECT является то, что он энергонезависимый и, как правило, быстрее при расчете. Нестабильность означает, что Excel нужно вычислять формулу только при изменении любого предшественника. INDIRECT однако, будет рассчитываться при любом расчете / изменении, внесенном в рабочую книгу - таким образом, особенно в большой модели замедление модели значительно!

Answer 3

Можно изменить исходную формулу, чтобы разрешить (но не считать) пробелы, т.е. эту версию

=SUM(IF(FREQUENCY(IF(Data!A2:A100<>"",MATCH(Data!A2:A100,Data!A2:A100,0)),ROW(Data!A2:A100)-ROW(Data!A2)),1))

подтверждено с помощью CTRL+SHIFT+ENTER

..... но вы можете попробовать это тоже - короче и не требует "записи массива"

=SUMPRODUCT((Data!A2:A100<>"")/COUNTIF(Data!A2:A100,Data!A2:A100&""))