Excel_2010

Рисунок 9.22. Пример заполненных таблиц данных

Данные из Таблицы данных не могут быть перемещены

Удаление данных возможно только из всей Таблицы данных целиком.

Создание таблицы с двумя переменными

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

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

1.Создайте базовую таблицу, включив в нее необходимую для расчета формулу–прототип

(Рисунок 9.23).

При создании формулы убедитесь, что она использует ссылки на две ячейки ввода, значения которых должны изменяться (в нашем примере – это ячейка В4 с процентной ставкой и ячейка B3 со сроком кредитования).

Рисунок 9.23. Базовая таблица

170

2.Измените базовую таблицу (Рисунок 9.24):

a)Введите один список входных значений в столбец под формулой-прототипом.

b)Введите второй список в одну строку с формулой-прототипом справа от нее.

Рисунок 9.24. Ввод списка входных значений

3.Выделите диапазон ячеек, содержащих формулу (A9), строку и столбец значений (A10:A17 и B9:F9), а также ячейки, в которые нужно поместить вычисленные значения (B10:F17).

4.На вкладке Данные в группе Работа с данными выберите команду Анализ "что если" и

выберите пункт Таблица данных.

5.В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для входных значений в строке – B5.

6.В поле Подставлять значения по строкам в введите ссылку на ячейку ввода для входных значений в столбце – B4.

7.Нажмите кнопку ОК.

Рисунок 9.25. Результирующая Таблица данных с двумя переменными

Ускорение вычислений для листов, содержащих таблицы данных

1.Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Формулы.

2.В разделе Параметры вычислений в группе Вычисление выберите вариант

Автоматически, кроме таблиц данных.

171

Рисунок 9.26. Настройка параметров вычислений

Если выбран этот параметр вычисления, таблицы данных не пересчитываются при пересчете остальной части книги.

Чтобы пересчитать таблицу данных вручную, выделите ее формулы и нажмите клавишу [F9].

9.1.5.Поиск решения

Общие сведения

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

Традиционными задачами, решаемыми с помощью надстройки Поиск решений, например, являются:

1.Ассортимент продукции. Сколько можно выпустить определенного товара при ограничении сырья.

2.Штатное расписание. Как составить штатное расписание для достижения лучшего результата и при наименьших расходах.

3.Планирование перевозок. Как минимизировать затраты на перевозки.

4.Составление смеси. Как составить смесь заданного качества при наименьших затратах. Все эти задачи имеют три общих свойства:

У них имеется только одна цель: или минимизировать затраты, или максимизировать прибыль.

Имеются ограничения на сырьё, время и т.д.

Имеется набор входных значений, влияющий на ограничения

Таким образом, основными компонентами механизма поиска решения являются:

Целевая ячейка– ячейка, содержащая формулу, значение которой нужно оптимизировать;

Ячейки переменных решения – ячейки, которые содержат значения, изменяемые поиском решения для достижения желаемой цели;

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

Ячейки ограничения – это ячейки, которые содержат значения, используемые для ограничения возможных решений.

172

Рисунок 9.30. Настройка параметров Поиска решения

5.В поле В соответствии с ограничениями введите любые ограничения, которые требуется применить. Для этого выполните указанные ниже действия.

a)В окне диалога Параметры поиска решения нажмите кнопку Добавить.

b)В окне диалога Добавление ограничения в поле Ссылка на ячейку введите имя или ссылку на ячейку или диапазон ячеек, на значения которых налагаются ограничения

(Рисунок 9.31).

c)Выберите в раскрывающемся списке требуемый тип отношения ( <=, =, >=, int, bin или dif), которое нужно использовать между ссылкой и ограничением.

Если выбрать вариант int, в поле Ограничение появится значение целое число. Если выбрать вариант bin, в поле Ограничение появится значение двоичное число. Если выбрать вариант dif, в поле Ограничение появится значение все разные.

Рисунок 9.31. Добавление ограничений

d)Если было выбрано отношение <=, = или >=, в поле Ограничение введите число, ссылку на ячейку (или имя ячейки) или формулу.

e)Выполните одно из указанных ниже действий.

Чтобы принять данное ограничение и добавить новое, нажмите кнопку Добавить.

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

6.Чтобы изменить или удалить существующее ограничение, выполните указанные ниже действия:

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

b)Выполните одно из указанных ниже действий.

Чтобы изменить выбранное ограничение нажмите кнопку Изменить и внесите изменения

Чтобы удалить выбранное ограничение нажмите кнопку Удалить.

175

7.Нажмите кнопку Найти решение. В исходной таблице отобразится результат поиска решения.

8.В окне диалога Результаты поиска решения выполните одно из следующих действий

(Рисунок 9.32):

Чтобы сохранить результат решения на листе установите переключатель Сохранить найденное решение.

Чтобы восстановить исходные значения установите переключатель Восстановить исходные значения.

Рисунок 9.32. Окно диалога Результаты поиска решения

Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение не найдено, будут доступны только некоторые отчеты или они вообще не будут доступны.

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

9.Нажмите кнопку ОК.

Просмотр промежуточных результатов поиска решения

Если вы хотите просмотреть пошаговое выполнение поиска решения, выполните следующие действия:

1.Откройте окно диалога Параметры поиска решения и определите основные компоненты, необходимые для поиска решения.

2.После постановки задачи нажмите кнопку Параметры.

3.В окне диалога Параметры установите флажок Показывать результаты итераций и

нажмите кнопку ОК (Рисунок 9.33).

176

Рисунок 9.33. Окно диалога Параметры

4.В окне диалога Параметры поиска решения нажмите кнопку Найти решение.

5.В окне диалога Показать предварительное решение выполните одно из указанных ниже действий:

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

Чтобы продолжить процесс поиска решения и просмотреть следующий вариант решения, нажмите кнопку Продолжить.

Рисунок 9.34. Окно диалога Показать предварительное решение

9.2. Анализ данных с помощью описательной статистики

Microsoft Excel имеет большое число статистических функций. Некоторые являются встроенными, некоторые становятся доступны после установки надстройки Пакет анализа.

9.2.1.Добавление надстройки Пакет анализа

1.На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

2.В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти.

3.В окне диалога Надстройки в поле Доступные надстройки установите флажок рядом с пунктом Пакет анализа и нажмите кнопку ОК (Рисунок 9.35).

177

Рисунок 9.35. Добавление надстройки пакет анализа

На вкладке Данные в группе Анализ будет добавлена команда Анализ данных.

9.2.2.Описательная статистика

Базовые понятия и постановка задачи

Описательная статистика (Descriptive statistics) - техника сбора и суммирования количественных данных, которая используется для превращения массы цифровых данных в форму, удобную для восприятия и обсуждения.

Цель описательной статистики - обобщить первичные данные, полученные в результате наблюдений и экспериментов. Для аналитика это невероятно полезный инструмент, поскольку позволяет быстро получать статистические сведения для больших баз данных или рабочих листов

Excel.

С помощью этого инструмента программа Excel создает таблицу со стандартными статистическими сведениями для каждой группы значений в указанном вами списке. В этой таблице указываются: среднее значение, стандартная ошибка, медиана, мода, стандартное отклонение, дисперсия выборки, эксцесс, асимметричность, интервал, минимум, максимум, сумма, количество, наибольшее значение, наименьшее значение и доверительный интервал.

Рассмотрим следующий пример (Рисунок 9.36):

Имеем:

Стоимость набора питания из 25 продуктов по некоторым городам центрального региона России по состоянию на декабрь 2010 г.

Необходимо:

Рассчитать основные показатели описательной статистики и сделать соответствующие выводы.

178

Рисунок 9.36. Пример исходной таблицы данных

Использование Описательной статистики

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

1.На вкладке Данные в группе Анализ нажмите кнопку Анализ данных.

2.В окне диалога Анализ данных в списке Инструменты анализа выберите Описательная статистика и нажмите кнопку ОК (Рисунок 9.37).

Рисунок 9.37. Выбор инструмента анализа

3.В окне диалога Описательная статистика в разделе Входные данные в поле Входной интервал укажите имя или ссылку на диапазон анализируемых ячеек. В нашем примере – это диапазон B2:В11 (Рисунок 9.38).

4.Установите флажок Метки если первая строка входного интервала содержит заголовки.

Если заголовки отсутствуют, флажок Метки следует снять. В этом случае будут автоматически созданы стандартные названия для данных выходного диапазона.

Рисунок 9.38. Настройка параметров в окне диалога Описательная статистика

179