Функция СЛУЧМЕЖДУ(нижн_граница; верхн_граница)
Как следует из названия этой функции, она позволяет получить случайное число из заданного интервала. При этом тип возвращаемого числа (т.е. вещественное или целое) зависит от типа заданных аргументов.
В качестве примера, сгенерируем случайное значение для переменной Q (объем выпуска продукта).
Введите в любую ячейку ЭТ формулу:
=СЛУЧМЕЖДУ(150; 300) (Результат: 210) .
Если задать аналогичные формулы для переменных P и V, а также формулу для вычисления NPV и скопировать их требуемое число раз, можно получить генеральную совокупность, содержащую различные значения исходных показателей и полученных результатов. После чего нетрудно рассчитатьсоответствующие параметры распределения и провести вероятностный анализ.
Продемонстрируем изложенный подход на решении приведенного выше примера. Перед тем, как приступить к разработке шаблона, целесообразно установить в ЭТ режим ручных вычислений.
Приступаем к разработке шаблона. С целью упрощения и повышения наглядности анализа выделим для его проведения в рабочей книге ППП EXCEL два листа.
Первый лист - "Имитация", предназначен для построения генеральной совокупности (рис. 1.1). Его необходимо создать в точном соответствии с рисунком. Далее необходимо задать имена соответствующим диапазонам ячеек. Учтите, что количество имитационных экспериментов 500, соответственно диапазоны ячеек включают строки с десятой по пятьсот десятую (выделяете диапазоны и затем используйте команду ФОРМУЛЫ – ПРИСВОИТЬ ИМЯ). Имена приведены в таблице 1.3.
Рис. 1.1 - Лист "Имитация"
Таблица 1.3 - Имена ячеек листа "Имитация"
| Адрес ячейки | Имя | Комментарии |
| Блок A10:A510 | Перем_расх | Переменные расходы |
| Блок B10:B510 | Количество | Объем выпуска |
| Блок C10:C510 | Цена | Цена изделия |
| Блок D10:D510 | Поступления | Поступления от проекта NCFt |
| Блок E10:E510 | ЧСС | Чистая современная стоимость NPV |
После того, как создан первый лист «Имитация» и соответствующим диапазонам ячеек назначены имена, необходимо приступить к созданию второго листа «Результаты анализа». Шаблон этого листа приведен на рис. 1.2. Имена ячеек листа приведены в таблице 1.4.
Рис. 1.2 – Лист «Результаты анализа»
Таблица 1.4 - Имена ячеек листа "Результаты анализа"
| Адрес ячейки | Имя | Комментарии |
| B2 | Нач_инвест | Начальные инвестиции |
| B3 | Пост_расх | Постоянные расходы |
| B4 | Аморт | Амортизация |
| D2 | Норма | Норма дисконта |
| D3 | Налог | Ставка налога на прибыль |
| D4 | Срок | Срок реализации проекта |
После того, как два листа созданы и определены имена ячеек и диапазонов, необходимо приступить к вводу соответствующих формул. Эти формулы представлены в таблицах 1.5 и 1.6. Обратите внимание на то, что при указании нижней и верхней границы изменений используется абсолютная адресация ячеек.
Таблица 1.5 - Формулы листа "Имитация"
| Ячейка | Формула |
| A10 | =СЛУЧМЕЖДУ($B$3;$C$3) |
| B10 | =СЛУЧМЕЖДУ($B$4;$C$4) |
| C10 | =СЛУЧМЕЖДУ($B$5;$C$5) |
| D10 | =(B10*(C10-A10)-Пост_расх-Аморт)*(1-Налог)+Аморт |
| E10 | =ПС(Норма;Срок;-D10)-Нач_инвест |
Примечание: Формулы в таблице 5 необходимо скопировать в соответствующие диапазоны до номера строки – 510.
Таблица 1.6 - Формулы листа "Результаты анализа"
| Ячейка | Формула |
| B8 | =СРЗНАЧ(Перем_расх) |
| B9 | =СТАНДОТКЛОНП(Перем_расх) |
| B10 | =B9/B8 |
| B11 | =МИН(Перем_расх) |
| B12 | =МАКС(Перем_расх) |
| C8 | =СРЗНАЧ(Количество) |
| C9 | =СТАНДОТКЛОНП(Количество) |
| C10 | =C9/C8 |
| C11 | =МИН(Количество) |
| C12 | =МАКС(Количество) |
| D8 | =СРЗНАЧ(Цена) |
| D9 | =СТАНДОТКЛОНП(Цена) |
| D10 | =D9/D8 |
| D11 | =МИН(Цена) |
| D12 | =МАКС(Цена) |
| E8 | =СРЗНАЧ(Поступления) |
| E9 | =СТАНДОТКЛОНП(Поступления) |
| E10 | =E9/E8 |
| E11 | =МИН(Поступления) |
| E12 | =МАКС(Поступления) |
| F8 | =СРЗНАЧ(ЧСС) |
| F9 | =СТАНДОТКЛОНП(ЧСС) |
| F10 | =F9/F8 |
| F11 | =МИН(ЧСС) |
| F12 | =МАКС(ЧСС) |
| F13 | =СЧЁТЕСЛИ(ЧСС;"<0") |
| F14 | =СУММЕСЛИ(ЧСС;"<0") |
| F15 | =СУММЕСЛИ(ЧСС;">0") |
| Е18 | =НОРМАЛИЗАЦИЯ(D18;$F$8;$F$9) |
| F18 | =НОРМСТРАСП(E18) |
Лист "Результаты анализа" кроме значений постоянных переменных содержит также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных и вероятности различных событий. Приведем описание этих функций.
Функции МИН() и МАКС() вычисляют минимальное и максимальное значение для массива данных из блока ячеек, указанного в качестве их аргумента.Имена и диапазоны этих блоков приведены в таблице 3.6.
Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке, значения которых удовлетворяют заданному условию. Функция имеет следующий формат:
=СЧЕТЕСЛИ(блок; "условие").
В данном случае, заданная в ячейке F13, эта функция осуществляет подсчет количества отрицательных значений NPV, содержащихся в блоке ячеек ЧСС.
Механизм действия функции СУММЕСЛИ() аналогичен функции СЧЕТЕСЛИ().Отличие заключается лишь в том,что эта функция суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию. Функция имеет следующий формат:
=СУММЕСЛИ(блок; "условие").
Две последние формулы (ячейки Е18и F18) предназначены для проведения вероятностного анализа распределения NPV .