Дослідження трендів засобами Excel Х

 

 

Лінії тренда можна проводити на гістограмах, графіках, лінійчатих і XY-крапкових діаграмах.

 

Выберите ряд данных, к которому нужно добавить

линию тренда или

скользящее среднее.

Выберите команду Добавить линию тренда в меню Диаграмма.

На вкладке Тип выберите нужный тип регрессионной линии тренда или линии скользящего среднего.

При выборе типа Полиномиальная введите в поле Степень наибольшую степень для независимой переменной.

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

 

Примечания

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

Если вариант «Скользящее среднее» выбран для точечной диаграммы, результат будет зависеть от порядка расположения значений X во входном диапазоне. Чтобы получить правильный результат, необходимо отсортировать значения X перед построением линии скользящего среднего.

 

Лінії тренду застосовуються для графічного відображення змін у даних і для аналізу прогнозування. Такий аналіз називається також аналізом регресії. За

допомогою аналізу регресії можна продовжити лінію тренду на діаграмі за межі наявних даних із метою прогнозування майбутніх значень. Наприклад, на наведеній нижче діаграмі застосовано просту пряму лінію тренду, яка чітко прогнозує тенденцію до зростання прибутків на наступні чотири квартали:

 

Змінне середнє Можна також створити літію тренду типу змінне середнє, яке

згладжує коливання даних і відображає тенденцію чіткіше.

 

Типи діаграм, які підтримують лінії тренду Лінії тренду можна додати до рядів даних у плоских діаграмах з областями без накопичення, графіках,

гістограмах, стовпчастих, біржових, точкових і бульбашкових діаграмах. Не можна додати лінію тренду до рядів даних у діаграмах з накопиченням, об’ємних, пелюсткових, секторних, поверхневих і кільцевих діаграмах. Якщо діаграма або ряд даних змінюється так, що більше не підтримують пов’язані лінії тренду, наприклад, в разі перетворення плоскої діаграми на об’ємну, то лінії тренду втрачаються.

 

Аппроксимація даних

 

 

Нагадаємо, що регресійний аналіз це вигляд статистичного аналізу, використовуваний для прогнозування. Регресійний аналіз дозволяє оцінити міру зв'язку між змінними, пропонуючи механізм обчислення передбачуваного значення змінною з декількох вже відомих значень.

Лініями тренду можна доповнити ряди даних, представлені на ненормованих плоских діаграмах з областями, лінійчатих діаграмах, гістограмах, графіках, біржових, точкових і бульбашкових діаграмах. Використання лінії тренду того або іншого вигляду визначається типом даних. Не можна доповнити лініями тренду ряди даних на об'ємних діаграмах, нормованих діаграмах, пелюсткових діаграмах, кругових і кільцевих діаграмах.

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

 

Додавання лінії тренду або ковзаючищего середнього до рядів даних

 

 

У Excel використовуються шість різних видів ліній тренду (апроксимація і згладжування), які можуть бути додані в діаграму (мал. 18.11): 1) Лінійна апроксимація (Linear) — це пряма лінія, що щонайкраще описує набір даних. Рівняння прямої у=ах+Ь, де а — тангенс кута нахилу, b — точка пересічення прямої з віссю біля. Лінійна апроксимація застосовується для змінних, які збільшуються або убувають з постійною швидкістю.

2) Логарифмічна апроксимація (Logarithmic) добре описує позитивні, так і негативні величини, які спочатку швидко зростають або убувають, а потім поступово стабілізується. Логарифмічна апроксимація використовує рівняння у=с* lnx+Ь, де з і b константи, In — натуральний логарифм.

3) Поліноміальна апроксимація (Polynomial) використовується для опису величин, що поперемінно зростають і убувають. Її доцільно застосовувати для аналізу великого набору даних нестабільної величини. Міра полінома визначається кількістю екстремумів (максимумів і мінімумів) кривої. Поліном другої міри може описати лише один максимум або мінімум. Поліном третьої міри має один або два екстремуми. Поліном четвертої міри може мати не більше трьох екстремумів. Поліноміальна апроксимація описується рівнянням y=a+ciXi+C2X2++Cigx18, де а, Cj—Cjg — константи. Необхідна міра полінома задається в полі Міра (мал.). Максимальна величина міри — 18.

4) Статечна апроксимація (Power) дає добрі результати, якщо залежність, яка міститься в даних, характеризується постійною швидкістю росту. Прикладом такої залежності може служити графік прискорення автомобіля. Якщо в даних є нульові або негативні значення, використання статечного наближення неможливе. Статечна апроксимація описується рівнянням у=а * хn, де а і n — константи.

5) Експоненціальну апроксимацію (Exponential) слід використовувати в тому випадку, якщо швидкість зміни даних безперервно зростає. Проте для даних, які містять нульові або негативні значення, цей вигляд наближення непридатний. Експоненціальна апроксимація описується рівнянням у= а • ebx, де а і b — константи.

6) Лінійна фільтрація (Moving average) дозволяє згладити коливання даних і таким чином наочніше показати характер залежності. Така лінія тренду будується по певному числу крапок (вона задається параметром Тонкі (Period). Елементи даних усереднюються, і отриманий результат використовується як середнє значення для наближення. Так, якщо параметр Тонкі рівний 2, перша точка згладжуючої кривої визначається як середнє значення перших двох елементів даних, друга крапка — як середнє наступних двох елементів і так далі. Для розрахунку ковзаючого середнього використовується рівняння у= (Aj+Aj_i++Aj_n+i) /n.

 

Додавання лінії тренду до рядів даних

 

 

Для додавання лінії тренду до рядів даних виконаєте наступні дії:

 

виділите ряд даних, до якого потрібно додати лінію тренду або ковзаюче середнє;

виберіть команду Додати лінію тренду (Add Trendline) у меню Діаграма (Chart). На вкладці Тип (Турі) виберіть потрібного типа регресійної лінії тренду або лінії ковзаючого середнього (мал. 18.11);

 

 

Мал. 18.11 Вибір лінії тренду

 

 

Мал. 18.12 Діаграма з нанесеною лінією тренду

 

 

при виборі типа Поліноміальна (Polynomial) введіть в поле Міра (Order) найбільшу міру для незалежної змінної;

при виборі типа Ковзаюче середнє (Moving Average) введіть в поле Крапки (Period) число крапок, використовуваних для розрахунку ковзаючого середнього.

 

 

Всі ряди даних діаграми, що підтримує лінії тренду перераховані в полі Побудований на ряду (Based On Series). Для додавання лінії тренду до інших рядів виберіть потрібне ім'я в полі, а потім виберіть потрібні параметри. Якщо варіант Ковзаюче середнє (Moving Average) вибраний для точкової діаграми, результат залежатиме від порядку розташування значень X у вхідному діапазоні. Аби отримати правильний результат, необхідно відсортувати значення X перед побудовою лінії ковзаючого середнього.

 

Надїжакностюк лінії тренду

 

 

Оцінка надійності лінії тренду до фактичних даних виконується по показнику визначеності або величині R в квадраті. R може змінюватися від 0 до 1. Чим більше величина цього показника, тим достовірніше лінія тренду. Значення R2 автоматично розраховується Excel при підборі лінії тренду до даних. Це значення можна відображувати на діаграмі.

Для виведення значення R-квадрат для лінії тренду клацніть цю лінію, і виберіть команду Виділена лінія тренду (Selected Trendline) у меню Формат (Format). На вкладці Параметри (Options) встановите прапорець помістити на діаграму величину достовірності апроксимації (R*2) (Display R-squared value on chart) (мал. 18.13). Відзначимо, що для ковзаючого середнього значення R-квадрат не може відображувати.

 

 

Електронна таблиця Excell X

 

 

то такое Excel?

Excel – это табличный процессор. Табличный процессор - это прикладная программа, которая предназначена для создания электронных таблиц и автоматизированной обработки табличных данных.

 

Что такое электронная таблица в Excel?

Электронная таблица – это электронная матрица, разделенная на строки и столбцы, на пересечении которых образуются ячейки с уникальными именами. Ячейки являются основным элементом электронной таблицы, в которые могут вводиться данные и на которые можно ссылаться по именам ячеек. К данным относятся: числа, даты, время суток, текст или символьные данные и формулы.

 

Что такое обработка табличных данных в Excel?

К обработке данных относится:

проведение различных вычислений с помощью формул и функций, встроенных в редактор;

построение диаграмм;

обработка данных в списках (Сортировка, Автофильтр, Расширенный фильтр, Форма, Итоги, Сводная таблица);

решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии "что - если" и другие задачи);

статистическая обработка данных, анализ и прогнозирование (инструменты анализа из надстройки "Пакет анализа").

 

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

 

 

Область применения Excel: планово – финансовые и бухгалтерские расчеты, учет материальных ценностей, системы поддержки принятия решений (СППР) и другие области применения.

 

Создание новой рабочей книги в Excel

Обучение работе с Excel необходимо начинать с изучения окна приложения Excel. При запуске Excel открывается окно приложения, в котором отображается новая рабочая книга – Книга 1.

 

Окно приложения Excel имеет пять основных областей:

строка меню;

панели инструментов;

строка состояния;

строка ввода;

область окна рабочей книги.

 

Основная обработка данных в Excel осуществляется при помощи команд из строки меню. Панели инструментов Стандартная и Форматирование являются встроенными панелями MS Excel, которые располагаются под строкой меню и содержат определенные наборы пиктограмм (кнопок). Основная часть пиктограмм предназначена для выполнения наиболее часто используемых команд из строки меню.

 

Строка формул в Excel используется для ввода и редактирования значений, формул в ячейках или диаграммах. Поле имени – это окно слева от строки формул, в котором выводится имя активной ячейки. Пиктограммы: X, V, fx, расположенные слева от строки формул - это кнопки отмены, ввода и вставка функции соответственно.

 

Строка состояния окна приложения Excel расположена в нижней части экрана. Левая часть строки состояния указывает сведения о состоянии рабочей области электронной таблицы (Готово, Ввод, Правка, Укажите). Кроме того, в левой части строки состояния кратко описываются результаты выполненной команды. В правой части строки состояния выводятся результаты вычислений (при выполнении автоматических вычислений с помощью контекстного меню строки состояния) и отображаются нажатые клавиш Ins, Caps Lock, Num Lock, Scroll Lock.

 

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

 

Кнопки прокрутки ярлычков осуществляют прокрутку ярлычков рабочей книги. Крайние кнопки осуществляют прокрутку к первому и последнему ярлычку рабочей книги. Внутренние кнопки осуществляют прокрутку к предыдущему и следующему ярлычку рабочей книги.

 

Основные понятия электронной таблицы: заголовок столбца, заголовок строки, ячейка, имя ячейки, маркер выделения, маркер заполнения, активная ячейка, строка формул, поле имени, активная область листа.

 

 

Рабочая область электронной таблицы состоит из строк и столбцов, имеющих свои имена. Имена строк – это их номера. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы. Имена столбцов – это буквы латинского алфавита сначала от А до Z, затем от АА до AZ, ВА до BZ и т.д.

 

 

Максимальное количество строк и столбцов электронной таблицы определяется особенностями используемой программы и объемом памяти компьютера, например, в табличном процессоре Excel 256 столбцов и более 16 тысяч строк.

 

 

Пересечение строки и столбца образует ячейку электронной таблицы, имеющую свой уникальный адрес. Для указания адресов ячеек в формулах используются ссылки (например, А6 или D8).

 

Ячейка – область, определяемая пересечением столбца и строки электронной таблицы, имеющая свой уникальный адрес.

 

Адрес ячейки определяется именем (номером) столбца и именем (номером) строки, на пересечении которых находится ячейка, например А10. Ссылка – указание адреса ячейки.

 

Активной ячейка - это выделенная ячейка, имя которой отображается в поле имени. Маркером выделения называется полужирная рамка вокруг выделенной ячейки. Маркер заполнения - это черный квадрат в правом нижнем углу выделенной ячейки.

 

Активная область листа - это область, которая содержит введенные данные.

 

В электронных таблицах можно работать как с отдельными ячейками, так и с группами ячеек, которые образуют блок. Блок ячеек – группа смежных ячеек, определяемая с помощью адреса.

 

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

 

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

 

Пример задания адресов ячейки и блоков в электронной таблице:

адрес ячейки, находящейся на пересечении столбца F и строки 9, выражается ссылкой F9;

адрес блока, образованного в виде части строки 1 - B1:E1;

адрес блока, образованного в виде столбца C - C1:C21;

адрес блока, образованного в виде прямоугольника - A3:G10

 

Работа с файлами в Excel

Сохранение и присвоение имени рабочей книге.

 

При сохранении рабочей книги в Excel открывается окно диалога "Сохранение документа". В этом окне необходимо указать: имя файла, тип файла, выбрать диск и папку, в которой будет храниться рабочая книга. Таким образом, книга с входящими в нее рабочими листами сохраняется в папке на диске в виде отдельного файла с уникальным именем. Файлы книг имеет расширение xls.

 

Открытие рабочей книги в Excel

 

Для открытия рабочей книги в Excel, надо выбрать команду Файл / Открыть или щелкнуть на кнопке Открыть на стандартной панели инструментов. Excel выведет окно диалога "Открытие документа" в нем можно выделить требуемый файл и щелкнуть на кнопке Открыть.

 

Закрытие рабочей книги и выход из Excel

 

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