Excel: Нахождение мин / макс N числа предыдущих записей

Question

Скажем, у меня есть набор числовых данных, от А1 до А100.

Если в D5 я бы хотел найти минимальное из последних 5 значений в A1:A100, я использовал формулу =min(A1:A5) Так что в D6 это будет =min(A2:A6) и т.д.

Если бы в B1 я хотел ввести целое число, которое контролировало диапазон последних N значений, так что если N = 3, то D5 было бы =min(A3:A5) а D6 =min(A4:A6)

Как я могу сформировать общую формулу в D5, которая учитывает N(переменное) число предыдущих терминов в A1:A100, где N - мой ввод в B1 (я хочу иметь возможность изменить это)?

Я не имею ни малейшего понятия, как с этим справиться, любая помощь будет оценена.

Answer 1

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

=MIN(INDIRECT("A" & ROW()-$B$1+1 & ":A" & ROW(),1))

(Некоторые пробелы добавлены для удобства чтения). Вы можете скопировать или перетащить это вниз по странице по мере необходимости.

• При этом используется функция ROW() для получения строки текущей ячейки.
• $ B $ 1 - это ваша ссылка на ячейку, содержащую размер диапазона ($ блокирует ссылку на ячейку, чтобы она не менялась при копировании формулы).
• +1 регулирует от размера диапазона до начального номера строки.
• & Объединяет фрагменты строки
• Последняя 1 необязательна в Excel (требуется в LO Calc, что я и использовал для проверки формулы). Он определяет стиль адресации ячеек, используемый в формуле.

Подробнее о функции INDIRECT можно узнать здесь.

Я заполнил несколько легко проверяемых чисел в столбце А, и результат выглядит так:

Если вы измените функцию с MIN на MAX, вы получите это: