37. Статистический анализ: функции срзнач, медиана, мода, счет, суммесли, счетесли

Excel предлагает широкий диапазон средств для анализа статистических данных.

=СРЗНАЧ (число1;число2;…) Вычисляет среднее арифметическое значение.

=МЕДИАНА (число1;число2;…) Определяет значение, разделяющее некоторое множество чисел на две равные части.

=МОДА (число1;число2;…) Определяет значение, которое чаще всего встречается у множества чисел.

=СЧЕТ (значение1; значение2;…) Подсчитывает количество чисел в списке аргументов.

=СЧЁТЗ (значение1; значение2;…) Подсчитывает количество значений в списке аргументов и непустых ячеек.

=СЧЕТЕСЛИ (диапазон; критерий) определяет кол-во ячеек, удовлетворяющих заданному критерию.

=СУММЕСЛИ (диапазон; критерий; диапазон_суммирования) суммирует, но проверяет каждую ячейку в диапазоне, прежде чем добавить ее к итогу.

38. Подбор параметра. Поиск решения.

Сервис/Подбор параметра

С помощью команды Сервис – Подбор параметра можно определить известную величину, приводящую к требуемому результату.

Пример. Определить, какую максимальную ссуду на 30 лет можно взять, если процентная ставка = 6,5% и вы можете постепенно выплачивать не более 2000 рублей в месяц.

Для работы с командой Подбор параметра необходимо, чтобы в листe находились:

- формула ддя расчета;

- пустая ячейка для искомого значения;

-все прочие величины, встречающиеся в формуле.

Ссылка на пустую ячейку должна присутствовать в формуле; она является той самой переменной, значение которой ищет Excel.

Формула для расчета содержит функцию ППЛАТ. Функция ППЛАТ вычисляет размер периодической выплаты, необходимой для погашения ссуды за указанное число периодов. Эта функция имеет следующий синтаксис.

=ППЛАТ (ставка;число периодов; текущее значение или размер ссуды).

Предположим, 500000 – нач. значени

погашения ссуды за указанное число периодов. Эта функция имеет следующий синтаксис.

=ППЛАТ (ставка;число периодов; текущее значение или размер ссуды).

Предположим, 500000 – нач. значение.

Решить задачу сначала следует с каким-нибудь начальным значением, например, 500000. После ввода формулы в ячейке В4 будет 3160,34.

Затем выполнить следующие действия:

1.сделать ячейку с формулой В4 активной

2.выполнить команду Сервис – Подбор параметра

3.в окне диалога Подбор параметра в поле Установить в ячейке будет адрес $B$4, оставить его без изменения.

4.в поле Значение ввести максимальное значение –2000 (это значение формулы Выплата (с минусом)).

5.в поле Изменяя значение ячейки ввести ссылку на ячейку $B$1 или щелкнуть на ней.

6.Нажать кнопку ОК. На экране появится окно диалога Результат подбора параметра.

7.Ответ на вопрос находится в ячейке, заданной ранее в поле Изменяя значение ячейки, т.е. в ячейке В1.

8.Чтобы сохранить это значение следует нажать кнопку ОК в окне диалога Результат подбора параметра. Поиск решения.

В случаях, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо пользоваться надстройкой Поиск решения.

Поиск решения – это инструмент, кот. может применяться для решения задач, включающих много изменяемых решений и позволяет найти комбинацию переменных, кот. максимизируют или минимизируют значение в целевой ячейке. Здесь же можно задать одно или несколько условий ограничений, кот. должны выполняться при поиске решений. Команда Поиск решений - это надстройка, поэтому перед работой надо убедиться в ее наличии.

Сервис/Надстройка/фл. Пакет анализа

Порядок.

1.Сервис/Поиск решения.

2.Задание цели. В поле Установить целевую ячейку задается цель, кот. должен достичь поиск решения.

3.Задание переменных. Указать изменяемые ячейки.

4.Задание ограничений. В окне диалога Поиск решений нажать кнопку Добавить и заполнить окно диалога Добавление ограничений. Ограничение состоит из трех компонентов: ссылка на ячейку или диапазон, оператор сравнения, значение ограничения. Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения.

После заполнения окна диалога Поиск решений нажать кнопку Выполнить. Поиск решений находит оптимальное решение для заданной целевой ячейки при выполнении всех ограничений и выводит окно диалога Результаты поиска решений. Возможные последующие действия: 1) Сохранить найденные решения, 2) Отмена, кот. восстановит значения, кот. были перед активизацией Поиска решений. 3) Сохранить сценарий для сохранения найденных значений в качестве сценария решения.

Сохранение модели поиска решения.

При обычном сохранении книги, с каждым рабочим листом в книге можно сохранить только один набор значений параметров поиска решения. Однако, пользуясь кнопкой Сохранить модель окна диалога Параметры поиска решения можно сохранить несколько наборов. Для этого нужно выполнить:

1.Выполнить команду Сервис – Поиск решения

2.нажать кнопку Параметры и в окне Параметры поиска решения нажать кнопку Сохранить модель и указать ячейку или диапазон рабочего листа для сохранения параметров поиска решения.

3.Задать пустую ячейку, начиная с которой поиск решения вставит сохраняемые параметры и затем нажать ОК.

4.Чтобы снова использовать сохраненные параметры, нажать кнопку Параметры в окне диалога и затем кнопку Загрузить модель.