Функция СЛМАССИВ динамических массивов в Excel

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

Функция СЛМАССИВ генерирует массив случайных чисел. Ее синтаксис:

Рис. 40. Синтаксис функции СЛМАССИВ()

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

Обратите внимание, что все пять аргументов являются необязательными.

Например, =СЛМАССИВ(10) генерирует 10 строк и 1 столбец случайных чисел. =СЛМАССИВ(;5) генерирует одну строку и пять столбцов. =СЛМАССИВ(2;3;1;9;ИСТИНА) генерирует две строки и три столбца целых чисел в диапазоне от 1 до 9.

Если используются не более двух первых аргументов, случайные числа генерятся также, как и при использовании функции =СЛЧИС(), т.е. возвращают десятичное число, большее или равное 0 и меньшее 1. Аргументы минимум и максимум позволяют имитировать функцию =СЛУЧМЕЖДУ(). А аргумент целое задает только целые случайные числа.

Рис. 41. Генерация случайных целых чисел от 1 до 9

Случайная сортировка с использованием СЛМАССИВ

Сложные сценарии, такие как случайное тестирование на наркотики или случайная выборка без повторов, значительно упрощаются, если использовать функцию СЛМАССИВ(). Допустим, вы хотите случайным образом выбрать четверых людей из 13. Воспользуйтесь формулой =ИНДЕКС(СОРТПО(A4:A16;СЛМАССИВ(СЧЁТЗ(A4:A16)));ПОСЛЕД(4))

Рис. 42. Случайна выборка

Если вы следите за изложением с использованием вложенного файла Excel, понажимайте F9, чтобы увидеть, как меняется список имен в выборке.

Использование СЛМАССИВ() для моделирования и симуляции

Функция СЛМАССИВ() отлично подходит для проведения анализа методом Монте-Карло. Просто укажите, сколько вам нужно строк и столбцов случайных чисел.

Врежется ли астероид в Землю? В августе 2018 г. Excel MVP Oz du Soleil симулировал в Excel 100 000 испытаний того, ударит ли астероид по имени Бенну по Земле в 2182­–2196 годах, создав новую туристическую достопримечательность под названием кратер Бенну.

Рис. 43. Астероид Бенну, снятый с космического аппарата «Осирис-Рекс». Фото NASA

Его файл использовал 100 000 ячеек со 100 000 формулами. Файл весил 3195 КБ, и на его пересчет уходило 13 секунд. Каждая формула возвращала 0 – астероид не ударит по Земле, или 1 – удар придется в Землю. В 100 000 испытаниях Бенну попадал на Землю примерно 44 раза, или удар имел 1 шанс из 2273. Когда появились новые формулы динамических массивов, я смог заменить 100 000 вычислительных ячеек одной формулой массива:

=СУММ(ВПР(НОРМ.ОБР(СЛМАССИВ(100000);$H$4;$H$5);$N$23:$O$179;2;ИСТИНА))

Размер файла сократился до 37 КБ, а время пересчета сократилось до 6,5 секунд. Вы можете изучить модель в приложенном файле.

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

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