Массив последовательных чисел в EXCEL

Для построения формул массива иногда используют числовую последовательность, например {1:2:3:4:5:6:7}, вводимую непосредственно в формулу. Эту последовательность можно сформировать вручную, введя константу массива , или с использованием функций, например СТРОКА() . Также с помощью формул можно динамически изменять длину числовой последовательности.

Создание массива последовательных чисел

Сформируем массив последовательных чисел для решения конкретного примера: подсчитаем сумму 3-х наибольших значений .

Пусть исходный список содержит числовые значения (см. Файл примера ).

Сумму 3-х наибольших значений можно подсчитать с помощью формулы =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;{1:2:3}))

Чтобы не вводить массив {1:2:3} вручную, можно использовать следующий подход. Введите в диапазон ячеек B1:B3 значения 1, 2 и 3 соответственно. Выделив любую пустую ячейку, в Строке формул введите =B1:B3, затем выделите B1:B3 и нажмите клавишу F9 . Формула B1:B3 будет заменена значениями, содержащимися в этих ячейках, т.е. {1:2:3} . Теперь достаточно скопировать этот массив в Буфер обмена ( CTRL+C ) и вставить его в нужную формулу.

Другим подходом для формирования числовой последовательности является использование формулы СТРОКА(A1:A3)

В итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;СТРОКА(A1:A3)))

Но у этого подхода есть два недостатка:

• при вставке новых строк на лист перед строками 1-3, формула автоматически модифицируется в СТРОКА(A2:A4) или СТРОКА(A1:A4) и соответственно формируется неправильный массив {2:3:4} или {1:2:3:4} . Исправить это может формула СТРОКА(ДВССЫЛ("A1:A3"))
• формула возвращает статичный массив.

Массив последовательных чисел переменной длины можно сформировать с помощью формулы = СТРОКА(ДВССЫЛ("A1:A"&G2)) , где в ячейке G2 пользователем задается последний элемент последовательности (т.е. сумму скольких наибольших требуется найти). В итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(Список;СТРОКА(ДВССЫЛ("A1:A"&G2))))

Динамический массив последовательных чисел

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

Создадим Динамический диапазон Список ( =СМЕЩ(Лист1!$A$2;;;СЧЁТ(Лист1!$A$2:$A$15)) ), который будет содержать все значения исходного списка и автоматически расширяться при добавлении новых значений в столбец А . Введем 9 значений (см. рисунок выше).

Массив последовательных чисел будем формировать с помощью следующей формулы: СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))

Здесь функция ЧСТРОК() возвращает количество значений в исходном списке (9). После выполнения функции СТРОКА() получим массив последовательных чисел {1:2:3:4:5:6:7:8:9} , причем этот массив будет динамическим: в зависимости от длины диапазона Список будет формироваться массив последовательных чисел соответствующего размера.