<< Предыдущая

стр. 64
(из 106 стр.)

ОГЛАВЛЕНИЕ

Следующая >>

Диалоговое окно Подбор параметра
Установить в

Знаденме;

рад
Изменяя значение .ячейки:




В поле Значение (То value) введите искомое число дохода 90000.
В поле Изменение значения параметра (By changing cell) укажите ссылку на
ячейку, содержащую параметр, значение которого требуется подобрать
для получения требуемого результата, в данном примере меняется тираж
книги, величина которого задается в ячейке В1. На эту ячейку прямо или
косвенно должна ссылаться формула, содержащаяся в ячейке, адрес кото-
рой указан в поле Установить в ячейке (Set cell).

Рис. 18.9 Результат подборе параметра
Диалоговое окно Результат подбора
Подбор параметра для ячы*и В9.
параметра Раив(«в найдено.

ПодБирэамаа значение: 9DOX1
Текущее эначаниа: КСОО
Анализ^данных в Excel 2003 351

• Нажмите кнопку ОК. Откроется диалоговое окно Результат подбора пара-
метра (Goal Seek Status) (рис, 18.9). Если подбор параметра требует много
. времени, то кнопка Пауза (Pause) в этом окне позволяет выполнить поша-
говый процесс поиска. Кнопка Продолжить (Continue) позволяет возобно-
вить автоматизированный подбор.

Визуальный подбор параметра с помочью диаграммы
Аргумент, обеспечивающий необходимое значение параметра, можно найти
с помощью гистограммы, линейчатой диаграммы, графика. На рис. 18.10 пока-
зана гистограмма затрат на тираж. Для визуального подбора параметра, устано-
вите указатель мыши ниже верхнего края столбца тиража, и выполните два
одиночных щелчка мышью. Перетащите верхний средний квадратик в требуе-
мое положение: 90000. После того как вы отпустите кнопку мыши, на экране
отобразится диалоговое окно Подбор параметра с заполненными полями Уста-
новить в ячейке (Set cell) и Значение (То value). Введите ссылку на ячейку В1
в поле Изменяя значение параметра (By changing cell). Нажмите кнопку ОК. От-
кроется диалоговое окно Результат подбора параметра (Goal Seek Status), рас-
смотренное выше.

Рис. 16.10
Использование
гистограммы для
выбора заданного
значения
параметра




Решение уравнений

Запомните
Диалоговое окно, отображаемое на экране после выборе? команду ПоАбо|>яс1-
рамвтра (Goal Seek) в мейкэСврвиё (Tools), позволяет решать уравнения с одним
меизвестмЬЕМ.

Для решения задач с несколькими неизвестными и набором ограничений
следует использовать надстройку Поиск решения (Solver).
3S2 Глава 18

В качестве примера решим уравнение
3 2
2х -4х +3х=27
Для решения уравнения выполните следующие действия:
• Запиш ите в ячей ку А1 число 0, а уравнение в ячейку В1:
Л
=2*АГЗ-4*А1 2+3*А1-27+А1.
• Выберите команду Подбор параметра (Goal Seek) в меню Сервис (Tools)
в меню Сервис.
• В поле Установить в ячейке (Set cell) введите В1, в поле Значение, (То
value) — 1, в поле Изменяя значение параметра (By changing cell) дайте ссыл-
ку на ячейку А1.
• Нажмите кнопку ОК. Откроется диалоговое окно Результат подбора пара-
метра (Goal Seek Status).
• Нажмите кнопку ОК. В ячейке А1 будет приведено значение 2,939, т.е.
х=3.
Если уравнение имеет несколько корней, то измените число 0, выбранное
в качестве начального приближения, например, на 0,5 или 2.


Аппроксимация данных
Запомните
Для анализа данных и графического отображения тенденции их изменение
в Excel используются линии тренда. Используя регрессионный анализ, мож*-
продлить линию, тренда в диаграмме за пределы реальных Данных для предска^
/˜ занйя будущих знамении!

Напомним, что регрессионный анализ это вид статистического анализа, ис-
пользуемый для прогнозирования. Регрессионный анализ позволяет оценить
степень связи между переменными, предлагая механизм вычисления предпола-
гаемого значения переменной из нескольких уже известных значений.
Линиями тренда можно дополнить ряды данных, представленные на ненор-
мированных плоских диаграммах с областями, линейчатых диаграммах, гисто-
граммах, графиках, биржевых, точечных и пузырьковых диаграммах. Использо-
вание линии тренда того или иного вида определяется типом данных. Нельзя
дополнить линиями тренда ряды данных на объемных диаграммах, нормиро-
ванных диаграммах, лепестковых диаграммах, круговых и кольцевых диаграм-
мах.
Более ясно закономерность в развитии данных показывает сглаженная кри-
вая. Она строится по точкам скользящего среднего, где под скользящим сред-
ним подразумевается последовательность средних чисел, каждое из которых
вычислено по некоторому подмножеству ряда данных.

Добавление линии тренда или скользящего
среднего к рилам данных
В Excel используются шесть различных видов линий тренда (аппроксимация
и сглаживание), которые могут быть добавлены в диаграмму (рис. 18.11):
1) Линейная аппроксимация (Linear) — это прямая линия, наилучшим об-
разом описывающая набор данных. Уравнение прямой у=ах+Ь, где а —
Анализ данных в Excel 2003 353

тангенс угла наклона, b — точка пересечения прямой с осью у. Линейная
аппроксимация применяется для переменных, которые увеличиваются
или убывают с постоянной скоростью.
2) Логарифмическая аппроксимация (Logarithmic) хорошо описывает поло-
жительные, так и отрицательные величины, которые вначале быстро рас-
тут или убывают, а затем постепенно стабилизируется. Логарифмическая
аппроксимация использует уравнение у=с • lnx+Ь, где с и b константы,
In — натуральный логарифм.
3) Полиномиальная аппроксимация (Polynomial) используется для описа-
ния величин, попеременно возрастающих и убывающих. Ее целесообраз-
но применять для анализа большого набора данных нестабильной вели-
чины. Степень полинома определяется количеством экстремумов (мак-
симумов и минимумов) кривой. Полином второй степени может описать
только один максимум или минимум. Полином третьей степени имеет
один или два экстремума. Полином четвертой степени может иметь не
более трех экстремумов. Полиномиальная аппроксимация описывается
уравнением y=a+ciXi+C2X2++C]8X]8, где а, С]—с\$ — константы. Требуемая
степень полинома задается в поле Степень (рис.). Максимальная величи-
на степени — 18.
4) Степенная аппроксимация (Power) дает хорошие результаты, если зави-
симость, которая содержится в данных, характеризуется постоянной ско-
ростью роста. Примером такой зависимости может служить график уско-
рения автомобиля. Если в данных имеются нулевые или отрицательные
значения, использование степенного приближения невозможно. Степен-
ная аппроксимация описывается уравнением у=а • хп, где а и п — кон-
станты.
5) Экспоненциальную аппроксимацию (Exponential) следует использовать
в том случае, если скорость изменения данных непрерывно возрастает.
Однако для данных, которые содержат нулевые или отрицательные зна-
чения, этот вид приближения неприменим. Экспоненциальная аппрок-
симация описывается уравнением у= а • ebx, где а и b — константы.
6) Линейная фильтрация (Moving average) позволяет сгладить колебания
данных и таким образом более наглядно показать характер зависимости.
Такая линия тренда строится по определенному числу точек (она задается
параметром Точки (Period). Элементы данных усредняются, и получен-
ный результат используется в качестве среднего значения для приближе-
ния. Так, если параметр Точки равен 2, первая точка сглаживающей кривой
определяется как среднее значение первых двух элементов данных, вторая
точка — как среднее следующих двух элементов и так далее. Для расчета
скользящего среднего используется уравнение у= (Ai+Aj_i++Aj_n+i)/n.

Добавление линии тренда к рядам данных
Для добавления линии тренда к рядам данных выполните следующие действия:
• выделите ряд данных, к которому нужно добавить линию тренда или
скользящее среднее;
• выберите команду Добавить линию тренда (Add Trendline) в меню Диаграм-
ма (Chart). На вкладке Тип (Туре) выберите нужный тип регрессионной
линии тренда или линии скользящего среднего (рис. 18.11);

12 Microsoft Office 2003
Глава 18
354


Рис. 18.11 Линия тренде

Выбор.линии тренда

и сглаживание)-




Рис. 18.12 ВСГЭРКЗ - .Фзщат сджис Диграмма ?|<но
Диаграмма с нанесенной линией
тренда




• при выборе типа Полиномиальная (Polynomial) введите в поле Степень
(Order) наибольшую степень для независимой переменной;
• при выборе типа Скользящее среднее (Moving Average) введите в поле Точки
(Period) число точек, используемых для расчета скользящего среднего.
Все ряды данных диаграммы, поддерживающей линии тренда перечислены
в поле Построен на ряде (Based On Series). Для добавления линии тренда к дру-
гим рядам выберите нужное имя в поле, а затем выберите нужные параметры.
Если вариант Скользящее среднее (Moving Average) выбран для точечной диа-
граммы, результат будет зависеть от порядка расположения значений X во
входном диапазоне. Чтобы получить правильный результат, необходимо отсор-
тировать значения X перед построением линии скользящего среднего.
Анализ данных в Excel 2003 355

Надежность линии тренда
Оценка надежности линии тренда к фактическим данным выполняется по
показателю определенности или величине R в квадрате. R может изменяться от
О до 1. Чем больше величина этого показателя, тем достовернее линия тренда.
Значение R2 автоматически рассчитывается Excel при подборе линии тренда
к данным. Это значение можно отобразить на диаграмме.-
Для вывода значения R-квадрат для линии тренда щелкните эту линию,
и выберите команду Выделенная линия тренда (Selected Trendline) в меню Фор-
мат (Format). На вкладке Параметры (Options) установите флажок поместить
на диаграмму величину достоверности аппроксимации (R*2) (Display R-squared
value on chart) (рис. 18.13). Отметим, что для скользящего среднего значение
R-квадрат не может быть отображено.
Рис. 18.13 Формат линии тренда
Вывод значения R-квадрат для линии
тренда
-апрс*Е1**(р)«зщвЙ (егпажвнной
Линвй-ы* 9*яд1)
тсматичатое;

С

ПрСТМЭЗ

. впдред из:

домна;


Г" гересачениэ кривой с осью V В 1Йч<в1
I (КОзьвагь [равнение шдиагрмче
m алприч:мн.-л|ми




Консолидация данных
Консолидация данных состоит в создании итоговой таблицы, позволяющей
обобщить однородные данные. Например, можно произвести суммирование
данных по товарам одних и тех же наименований, хранящихся на разных скла-
дах (рис. 18.14). При консолидации значения, приведенные в итоговой табли-
це, могут рассчитываться на основе исходных данных с использованием раз-
личных функций Excel.
Исходные данные (области) могут располагаться на одном или разных лис-
тах, в других открытых книгах. При консолидации можно создать связи, обес-
печивающие автоматическое обновление данных в итоговой таблице (области
назначения) при изменении данных в исходных областях.
Консолидацию данных можно произвести по расположению ячеек, содер-
жащих исходные данные, по категориям, с помощью трехмерных ссылок, свод-
ной таблицы и т.д. При этом во всех исходных диапазонах данные должны быть
расположены в одинаковом порядке. Так, если мы рассматриваем количество
товаров одного наименования на разных складах, то во всех отчетах, представ-


i:
Глава 18
356

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

Рис. 18.14 1 Microsoft Excel - Consplidate.»le
3 jOQ fX|
Пример таблицы |уц кмвгл Оормв"
РМРШИ rjr-36'.a С^ЕЛ-Х а*тые ?жнс Ораиз -вх
исходных данных
для консолидации
; Anal Cyr
:ft. Код товаэа

<< Предыдущая

стр. 64
(из 106 стр.)

ОГЛАВЛЕНИЕ

Следующая >>