Функция ПОСЛЕД динамических массивов в Excel

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Генерация диапазона последовательных чисел

У меня есть классный трюк.[1] На семинарах Power Excel я показываю, как можно получить последовательность натуральных чисел. Вводите в ячейку число 1, выделяете ячейку, нажимаете Ctrl, кликаете на маркер в правом нижнем углу, и протаскиваете его вниз. Excel заполняет ячейки числами 2, 3, 4, … Когда я впервые увидел функцию ПОСЛЕД, я подумал, что она была создана для тех, кто не знаком с этим трюком. Но, как вы увидите ниже и в главе 11, функция ПОСЛЕД является ключом к превращению многих других функций Excel в функции массива.

Функция ПОСЛЕД() генерирует массив чисел. Ее синтаксис

Рис. 34. Синтаксис функция ПОСЛЕД()

Скачать заметку в формате Word или pdf, примеры в формате Excel

Если ПОСЛЕД(10) генерирует столбец чисел от 1 до 10, то более сложная формула =ПОСЛЕД(10;3;5;10), позволяет создать массив из 10 строк, 3 столбцов, начиная с числа 5, с шагом 10:

Рис. 35. Генерация последовательности чисел

Использование ПОСЛЕД внутри других функций

Допустим вы взяли кредит с погашением на основе аннуитетных платежей (т.е., каждый период вы платите одну и туже сумму). Разовый платеж идет в погашение основного долга и процентов. Ваша задача – рассчитать ту часть, которая пойдет в погашение процентов, за первые пять период. В предыдущих версиях Excel вы бы использовали табличку, с явным указанием номера платежа в ячейках А6:А10:

Рис. 36. Пять формул для расчета процентных платежей за пять месяцев

Или вы могли бы использовать счетчик СТРОКА(1:1) внутри формулы: =ПРПЛТ($B$3/12;СТРОКА(1:1);$B$2;$B$1).

Сейчас достаточно ввести одну формулу в ячейку D6, и формула динамического массива разольется в столбец из пяти строк. Если нужен результат по первым шести периодам, просто измените аргумент функции ПОСЛЕД(6).

Рис. 37. Пять значений, как результат одной формулы динамического массива

Пока Excel не всегда наследует форматирование ячейки с формулой на разливаемый диапазон. Поэтому иногда вам потребуется отформатировать динамический диапазон вручную. Разработчики исправят этот баг позже.

Чтобы получить сумму процентных платежей за первые пять периодов (без вывода значения каждого платежа), «оберните» предыдущую формулу в функцию суммы: =СУММ(ПРПЛТ($B$3/12;ПОСЛЕД(5);$B$2;$B$1))

Более того, например, вы хотите узнать, сколько процентов вы будете платить в течение 4-го года погашения кредита. Для этого вам не нужно строить таблицу. Используйте в формуле аргументы, определяющие платежи с 37-го по 48-й: =СУММ(ПРПЛТ($B$3/12;ПОСЛЕД(12;1;37;1);$B$2;$B$1)). Здесь функция ПОСЛЕД(12;1;37;1) сгенерит один столбец из 12 строк, начинающихся со значения 37 с шагом 1.

Римские числа

Благодаря функциям динамического массива практически каждая формула Excel теперь может стать формулой массива. И это без необходимости использования Ctrl + Shift + Enter! Как вам такой массив римских чисел!?

Рис. 38. Римские числа, полученные с помощью одной формулы массива

Для тех, кто не знаком с творчеством автора, Билл Джелен любит использовать римские числа в ироническом контексте))

Рис. 39. Некоторые обложки книг Билла Джелена

[1] Повествование от первого лица – автора книги Билла Джелена.

Добавить комментарий

Ваш адрес email не будет опубликован.