Массив последовательных чисел в MS 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}, причем этот массив будет динамическим: в зависимости от длины диапазона Список будет формироваться массив последовательных чисел соответствующего размера.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4 (1 оценка)
Яндекс.Метрика