Сделать нижний ряд автоматически равным ближайшему ряду?

Question

У меня есть таблица из 16 столбцов из 53 строк. Поскольку каждая неделя года проходит, другая строка заполняется данными от 16 человек.

Я хочу, чтобы нижний ряд всегда представлял текущий счет человека (поскольку мы на 9 неделе года, ячейка A53 в настоящее время имеет формулу = A9).

Однако, когда наступит 10-я неделя и будет заполнен десятый ряд, мне придется изменить формулу на = A10. Я хотел бы найти способ автоматического выбора нижней заполненной строки вместо необходимости менять формулу каждую неделю.

Я видел связанный вопрос « Автоматически выбирать последнюю строку в наборе в Excel», но это решение не сработало для меня.

Answer 1

Предполагая, что данные числовые, попробуйте LOOKUP, в A53 скопируйте:

=LOOKUP(9.9E+307,A1:A52)

Answer 2

Вам не нужны никакие дополнительные надстройки - просто введите это в A53:

=INDIRECT("A"&COUNTA(A$1:A$52))

Для вашего случая он вернет последнее непустое значение в диапазоне A1:A52 . Вы можете корректировать диапазон, как вам нравится, но убедитесь, что вы сохраняете абсолютные ссылки на строки, чтобы избежать ошибок в случае, если формула перемещается вниз Однако вы можете автоматически заполнить все 16 столбцов - цикл будет выполняться только в одном столбце.

Answer 3

Обобщая ответы и добавляя мой 2с:

Обычно вам нужно выполнить два шага:

1. Выясните, какую строку вы хотите получить. Следующие варианты

   • =COUNTA($A$1:$A$52) - будет считать все элементы - но игнорировать пробелы. Так что, если кто-то не входит в неделю 7, но неделю 8 и 9, это вернется 8
   • =WEEKNUM(TODAY()) - возвращает номер текущей недели. Он переключит недели сразу в понедельник - даже если данные еще не введены.
   • =MATCH("",$A$1:$A$52,0) - это вернет вам первую пустую строку (поэтому используйте ее вместе с -1 .

2. Когда у вас есть номер строки (давайте используем N для результата / формулы шага 1), вам нужно сказать Excel, чтобы он возвращал N-ю строку. Для этого у вас есть несколько вариантов:

   • =INDIRECT("A"&N) - это создаст адрес, скажем, A9, а затем вернет значение в этой ячейке
   • =OFFSET($A$1,N-1) - это будет использовать A1 в качестве начальной ячейки, сместить ее на N-1 строк и вернуть этот результат
   • =INDEX($A$1:$A$52,N) - это вернет вам N-й элемент диапазона A1:A52.
      

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

Теперь вам нужно только объединить первый и второй шаги в объединенную формулу, например =INDEX($A1:$A$52,WEEKNUM(TODAY())) и все готово.

Ответ Барри =LOOKUP(9.9E+307,A1:A52) - отличный хак: он всегда возвращает вам последнее значение (сверху вниз), которое не пустое.

Answer 4

Если вы установите пакет инструментов анализа, у вас будет доступ к функции WeekNum(серийный, возвратный) .

В случае, если программа всегда запускается на первой неделе года, вы можете настроить косвенный оператор, который использует WeekNum(), чтобы выбрать, откуда получить информацию. Что-то вроде:

= Косвенный (Конкатенация ("A", WeekNum (Today ()))

в ячейке, где вы хотите последние данные. Если программа запускается в другое время, вы можете определить, с какой недели года она началась, и вычесть ее из части формулы WeekNum(). Например, если вы начинаете на 10 неделе (поэтому результаты на 11 неделе представлены в формате A3, принимая заголовок в строке 1):

= Косвенный (объединенный ("A", WeekNum (Today ()- 9))

NB: я не перед машиной с Excel atm, поэтому синтаксис может быть немного не в порядке. Вам также может понадобиться проверить строки заголовка math re: и т.д., Но это должно быть тривиально.