Построение диаграмм и графиков

В программе Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Построение графического изоб­ражения производится на основе ряда данных. Так называют группу ячеек с дан­ными в пределах отдельной строки или столбца. На одной диаграмме можно отобра­жать несколько рядов данных.

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

Для построения диаграммы обычно используют «Мастер диаграмм», запускаемый щелчком по кнопке стандартной панели инструментов. Часто удобно заранее выделить область, содержащую данные, которые будут отображаться на диаграмме, но задать эту информацию можно и в ходе работы мастера.

Выбор типа диаграммы. На первом этапе работы мастера выбирают форму диаграммы. Доступные формы перечислены в списке Тип на вкладке «Стандартные». Для выбранного типа диаграммы справа имеется несколько вариантов представления данных, из кото­рых следует выбрать наиболее подходящий. На вкладке «Нестандартные» отобража­ется набор полностью сформированных типов диаграмм с готовым форматирова­нием.

Выбор данных. Второй этап работы мастера служит для выбора данных, по которым будет стро­иться диаграмма. Если диапазон данных был выбран заранее, то в области предварительного просмотра в верхней части окна мастера появится при­близительное отображение будущей диаграммы. Если данные образуют единый прямоугольный диапазон, то их удобно выбирать при помощи вкладки «Диапазон данных». Если данные не образуют единой группы, то информацию для отрисовки отдельных рядов данных задают на вкладке «Ряд». Предварительное представление диаграммы автоматически обновляется при изменении набора отображаемых дан­ных.

Оформление диаграммы. Третий этап работы мастера состоит в выборе оформления диаграммы. На вкладках окна мастера задаются:

• название диаграммы, подписи осей;

• отображение и маркировка осей координат;

• отображение сетки линий, параллельных осям координат;

• описание построенных графиков;

• отображение надписей, соответствующих отдельным элементам данных на графике;

• представление данных, использованных при построении графика, в виде таблицы.

Размещение диаграммы. На последнем этапе работы мастера указывается, следует ли использовать для размещения диаграммы новый рабочий лист или один из имеющихся. Обычно этот выбор важен только для последующей печати доку­мента, содержащего диаграмму. После щелчка на кнопке Готово диаграмма стро­ится автоматически и вставляется на указанный рабочий лист.

Редактирование диаграммы. Готовую диаграмму можно изменить. Она состоит из набора отдельных элемен­тов, таких, как сами графики (ряды данных), оси координат, заголовок диаграммы, область построения и прочее. При щелчке на элементе диаграммы он выделяется маркерами, а при наведении на него указателя мыши — описывается всплываю­щей подсказкой. Открыть диалоговое окно для форматирования элемента диа­граммы можно через меню «Формат» (для выделенного элемента) или через контек­стное меню. Различные вкладки открывшегося диалогового окна позволяют изменять параметры отображения выбранного элемента данных.

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

Технологии анализа данных в Microsoft Excel

К стандартным технологиям анализа данных, реализуемым в Microsoft Excel,относятся:

1) представление исходных данных в виде списков (базы данных) Microsoft Excel для целей анализа;

2) фильтрация списков (базы данных) Microsoft Excel по различным условиям;

3) использование встроенных функций Microsoft Excel для формирования эко­номико-математических моделей;

4) подбор параметров модели по заданному значению функционала;

5) многовариантные расчеты и анализ чувствительности модели с помощью сценариев;

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

7) методы математического программирования для решения оптимизационных задач;

8) статистическая обработка экономической информации с помощью Пакета ана­лиза:

1) графические методы решения экономических задач и представления резуль­татов анализа;

2) агрегирование и своды исходных данных в виде списков (базы данных) Mic­rosoft Excel и др.

Экономико-математические модели, реализуемые в среде Microsoft Excel, могут основываться на встроенных функциях Microsoft Excel, а также функциях поль­зователей на языке Visual Basic. Наиболее популярны для использования в экономико-математических моделях встроенные функции категории финансовые.

Технология подбора параметра

Подбор параметра является основным методом исследования области допусти­мых значений для параметров моделей. Если функционал имеет несколько пара­метров, подобный анализ выполняется последовательно для каждого параметра в отдельности, при этом задаваемое значение функции остается неизменным. После подбора можно сравнить полученные результаты подбора с точки зрения их реалистичности. При подборе параметра допускается использование каскада взаимосвязанных формульных выражений, связывающих значения различных ячеек, в том числе находящихся на других листах или в других рабочих книгах. Существенное ог­раничение для применения этого подхода — в каждый момент времени исследу­ется только один параметр функции.

Сценарный подход

В электронной таблице Microsoft Excel можно сохранять наборы значений пара­метров моделей в виде сценариев. Сценарий — именованный набор значений ука­занных ячеек листа рабочей книги. Сценарный подход обеспечивает решение задач типа «ЧТО ЕСЛИ», не ограничивая число одновременно изменяемых па­раметров для каждого такого набора. Сценарии используются для подстановки значений параметров в ячейки таблицы и вычисления зависящих от них фор­мул.

Ограничений на общее число сценариев на одном листе рабочей книги нет. Сце­нарии содержат лишь ячейки одного листа рабочей книги, при этом допускается объединение сценариев нескольких листов и даже сценариев других рабочих книг при подготовке отчетов. В отчете может быть выведено максимум 251 сце­нарий.

Значения ячеек сценария хранятся в скрытом виде, но могут использоваться для подстановки в ячейки. Можно многократно редактировать состав и значения параметров, входящих в сценарий, добавлять новые и удалять существующие сценарии. Сценарий можно снабдить комментарием.

Таблицы подстановки

Технология таблицы подстановки используется для изучения влияния значений параметра на результат несколько функций либо значений двух параметров на результат одной функции. Эта технология обеспечивает решение задач анализа типа «ЧТО ЕСЛИ», ограничивая число од­новременно изменяемых параметров модели. Значения параметра (параметров) и результирующей функции представлены на листе в явном виде. Можно про­сто изменять значения параметра (параметров) и автоматически вычислять зна­чения функции.

Таблица подстановок для одного параметра. Параметр одновременно может использоваться для вычисления различных функций. На листе рабочей книги готовится массив значений параметра в виде век­тор -строки или вектор - столбца. Вводятся формулы различных функций, кото­рые используют данный параметр. Для вычисления этих функций создается массив формул.

Таблица подстановок для двух параметров. На листе рабочей книги готовятся два массива значений параметра, один из ко­торых — вектор-строка, другой — смежный вектор-столбец. Вводится формула функции, которая использует параметры. Для вычисления этой функции созда­ется массив формул.

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

Microsoft Excel обеспечивает решение задач линейного и нелинейного програм­мирования ограниченной размерности. Модель задачи задается в диалоговом окне поиск решения. Модель использует целевую функцию, которая записывается виде формулы в отдельной ячейке. Для целевой функции указывается: макси­мизация, минимизация или равенство фиксированному значению. В процессе поиска решения изменяются значения в указанных ячейках, соответствующих переменным, при соблюдении ограничений.

Дополнительные настройки оптимизации выполняются в диалоговом окне «Па­раметр поиска решения». В частности, можно ограничить время выполнения по­иска решения и выполнения промежуточных вычислений, максимальное вре­мя — не более 9 часов, точность, с которой найденное решение соответствует целевому значению, допустимое отклонение для переменных от оптимального значения. Для задач с нелинейной целевой функцией задается параметр сходи­мости, который влияет на прекращение поиска. Если относительные изменения значения целевой функции за последние пять итераций меньше указанного чис­ла, поиск прекращается. Выполняется установка типа модели — линейная, если целевая функция линейная. Можно выводить результаты итераций, выполнять автоматическое масштабирование параметров модели.

При решении задачи можно выбрать метод экстраполяции оценок переменных для каждого шага поиска — линейная или квадратичная (для задач с нелиней­ной целевой функцией). Метод численного дифференцирования для целевой функ­ции — прямые или центральные разности (для задач с нелинейной целевой функцией). Метод поиска — метод Ньютона или метод сопряженных градиентов (больше итераций). Основным огра­ничением модели является максимальное число переменных — 200. Несколько оптимизационных моделей на одном листе можно сохранять и загружать по мере необходимости.

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

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

Статистический анализ и прогнозирование

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

Описательная статистика — самый распространенный прием анализа числовых данных, с помощью которого вычисляются статистические оценки случайной величины.

Пакет анализа обеспечивает наиболее быстрый способ формирования описательной статистики. Исходные данные для анализа располагаются в ячейках строк или столбцов таблицы и мо­гут иметь метки. Для входного интервала указывается ориентация — по строкам или столбцам, наличие метки строки или столбца.

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

Метод скользящего среднего. Метод скользящего среднего предполагает, что все наблюдения временного ряда имеют oдинaкoвую значимость («вес») для прогноза. Каждое значение временного ряда кроме начальных, участвует в формировании нескольких прогнозных значений.

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

Построение базы данных

Библиотеки были одними из первых, кто способствовал разработке электронных систем хранения информации, называемых базами данных, в которых поиск осуществляется по определенным критериям. Набор информации, составленный определенным образом, в Excel называется списком. Каждый столбец представляет определенную категорию информации (в терминологии базы данных называемую полем). Каждая строка представ­ляет собой набор информации об одном лице, теме или другом единичном элементе. В базах данных строки называют записью. Запись содержит информацию о конкретном элементе. Все записи, включенные в один файл, представляют файл базы данных (в терминологии Excel — список).

Электронная таблица и база данных. Электронные таблицы лучше всего подходят для экспериментов с данными типа "что-если". Базу данных (в случае Excel — список) следует использовать, если конечным результатом будет выделение или сорти­ровка определенной информации. Например, если вы ведете электронную чековую книжку, то лучше использовать про­грамму базы данных. В этом случае вы сможете выделить все записи, в которых содержатся чеки, не облагаемые налогом; определите, сколько было заплачено в этом году за электроэнергию и др. Список инвентаря также удобнее вести в форма­те базы данных или списка. При этом вы легко сможете ответить на вопрос: Как много на складе содержится товара, ко­личество которого меньше минимально допустимого, и где я могу купить дополнительную партию? Использование списка придает Excel некоторые свойства СУБД. Подобными возможностями располагает и Access.