Статистичні функції в Excel, приклади використання.

Статистичнікції функції призначені для проведення різного роду статистичних обчислень. Для реалізації лінійної регресії використовуються: ЛИНЕЙН (відомі у, відомі х) – використовуючи метод найменших квадратів, функція вираховує пряму лінію, яка найкращим чиним апроксимує наявні дані; МАКС – повертає максимальне значення із списку аргументів; МИН - повертає мінімальне значення із списку аргументів; ПРЕДСКАЗ (новий х, відомі у, відомі х) – повертає передбачене значення функції в точці х на основі лінійної регресії для масивів відомих значень х і у або інтервалів даних; ТЕНДЕНЦИЯ (відомі х, відомі у, нові х, константа) – повертає значення у відповідності до лінійного тренду. Нелінійна регресі реалізовується через: РОСТ (відомі х, відомі у, нові х, нові у, константа) – апроксимує експериментальною кривою відомі значення у і відомі значення х і повертає відповідні цій кривій значення для значення х, які визначаються аргументом нові значення х; ЛГРФПРИБЛ (відомі у, відомі х) – вираховує експотенційну криву, яка апроксимує дані, і повертає масив, який описує цю криву.

51. Статистичні функції виконують операції по обчисленню параметрів випадкових величин або їх розподілень, представлених множиною чисел, наприклад, стандартного відхилення, середнього значення, медіани і т.п. До категорії Статистические віднесено 71 функцію, що дає змогу виконувати різноманітні розрахунки: Функція СРЗНАЧ(діапазон) повертає середнє значення (середнє арифметичне) діапазону клітинок, що еквівалентне сумі вмісту всіх клітинок діапазону, поділеній на кількість клітинок у діапазоні; Функція МАКС(МИН)(діапазон) використовується для визначення найбільшого (меншого) значення з діапазону; Функція НАИБОЛЬШИЙ(МЕНЬШИЙ)(діапазон;n) повертає n-не найбільше(меньше) значення з вміщених у діапазон клітинок; Функція ДИСП(число1; число2;...) розраховує дисперсію випадково вибраних n чисел, причому їх кількість не може бути більшою від 30; Функція КВАДРОТКЛ (число1; число2;...); розраховує суму квадратів відхилень точок даних від їх середнього. До статистичних функції також входять функції прогнозування: =Предсказ(новий х;відомі у;відоміх) – короткостроковий одиничний лінійний прогноз; =Тенденция(відомі у;відомі х;нові х) – довгостроковий одиничний лінійний прогноз;ЛИНЕЙН(відомі у; відомі х) знаходить коефіцієнти(аn,.. а2, а1, b, де а – впливаючі фактори), які користувач використовує для підстановки у формулу багатофакторної лінійної регресії. Наприклад, у комірках А1:А100 введено значення і треба знайти середнє значення. Формула =СРЗНАЧ(А1:А100) повертає середнє значення діапазону А1:А100. А формула =НАИБОЛЬШИЙ (А1:А100;2) – дасть нам друге найбільше значення з цього діапазону.

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

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

У середовищі MS Excel для однофакторного прогнозування використовують такі функції: =Предсказ(новий х;відомі у;відоміх) – короткостроковий лінійний прогноз; =Тенденция(відомі у;відомі х;нові х) – довгостроковий лінійний прогноз; =Рост(відомі у; відомі х; нові х) – довгостроковий нелінійний прогноз. Для багатофакторного прогнозування використовують функції: 1)=Линейн(відомі у; відомі х) – коротко/довгостроковий лінійний прогноз, 2)=Лгрфприбл(відомі у; відомі х) - коротко/довгостроковий нелінійний прогноз. Ці дві функції знаходять коефіцієнти(аn,.. а2, а1, b, де а – впливаючі фактори), які користувач використовує для підстановки у формули багатофакторної регресії.

Функція Коррел використовується для розрахунку коефіцієнта кореляції.

52. Графічний аналіз - це процедура представлення текстових і цифрових даних у графічному вигляді з метою отримання додаткової або нової інформації про досліджуваному процесі, об'єкт або явище. В Excel графічний аналіз та інтерпретація проводяться за допомогою спеціальнихграфічних об'єктів - діаграм. Діаграма - це графічно представлена ​​залежність однієї величини від іншої.

Основні типи діаграм в Excel:

1. Гістограма (стовпчикова діаграма) являє собою набір вертикальних стовпчиків,висота яких визначається значеннями даних. Застосовується для відображення одного або кількох рядів даних і зазвичайвикористовується для зіставлення числових
даних, а також для подання зміни даних у часі або за категоріями.

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

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

4. Кругові діаграми використовуються для представлення одного ряду даних і демонструють співвідношення між цілим і його частинами і відповідають напитання: яку частину цілого складає той чи інший його компонент.

Для побудови д-ми треба виділити значення, на основі яких буде побудована д-ма. Вставка → Діаграма: у вікні вибираємо тип д-ми з переліку → Далее; визначаємо, де розміщені значення (у рядках або в стовпцях) → Диапазон значень, на базі яких будується д-ма → Далее; якщо необхідно, створюється назва д-ми (осей Х, У) → Далее; визначити розташування д-ми (на окремому листі чи на поточному) → Готово. Цю д-му можна редагувати: клацнути правою кнопкою миші, у меню вибираєш потрібний напрямок редагування (тип, назва, легенда, текстове пояснення до кожного об’єкту д-ми).

53. Автофільтр. За доп. Цієї ф-ії викон. Пошук і виведення на екран лише тих значень, які задовольняють умові. Для викон.: курсор у БД, ком. Данные → Фильтр → Автофильтр. Зверху кожного стовпця з’являються кн. Списків. Якщо треба створити умову тільки в одному стовпці, його попередньо виділяють. Для того, щоб зазначити умови філ-ня, треба розкрити списки в потрібному стовпці. З відфільтрованими зн-ми можна вик. Розрахунки. Для відміни дії команди Автофільтр: ком. Данные → Фильтр → Автофильтр.

Расширенный фильтр. Ф-ція дозволяє з існуючої БД створити іншу, зн-ня якої задовольняють умові. Для цього спочатку створюють критерії, який розміщений поза зн-ми БД. Критерій складається з назви поля і умови, розміщеної під відповідною назвою поля. Копіюють назви полів у вільний рядок; під потрібною назвою пишуть умову (=, <, >); встановлюють курсор у БД; ком. Данные → Фильтр → Расширенный Фильтр, у вікні актив. параметр “Скопировать рез-т в другое место”. У полі “Исходный диапазон”, зазначають діапазон всієї БД, включаючи назви полів. У полі “Диапазон условий” зазначають діапазон комірок, в яких створено критерій. У полі “Поместить рез-ты в диапазон” зазначають адресу вільної комірки. ОК. З’являється нова БД.

54. Зведені таблиці є одним з найбільш могутніх засобів MS Excel з аналізу баз даних, розміщених у таблицях чи списках. Зведена таблиця не просто групує й узагальнює дані, але і дає можливість провести глибокий аналіз наявної інформації. Створюючи зведену таблицю, користувач задає імена полів, що розміщаються в її рядках і стовпцях. Допускається також завдання поля сторінки, що дозволяє працювати зі зведеною таблицею, як зі стопкою аркушів. Зведені таблиці зручні при аналізі даних з кількох причин:

- дозволяють створювати узагальнюючі таблиці, що надають можливість групування однотипних даних, підведення підсумків, підведення статичних характеристик записів;

- легко перетворюються;

- дозволяють виконувати автоматичний добір інформації;

- на основі зведених таблиць будуються діаграми, що динамічно перебудовуються разом зі зміною зведеної таблиці.

Опишемо покроковий процес створення зведеної таблиці на прикладі таблиці Замовлення. Для цього:

1. Виберіть команду Дані | Зведена таблиця. На екрані з'явиться перше вікно Майстра зведених таблиць.

2. У першому вікні Майстра зведених таблиць під заголовком Створити таблицю на основі даних, що знаходяться треба вказати джерело даних для створення зведеної таблиці.

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

4. Спочатку натисніть кнопку Макет для створення структури зведеної таблиці. На екрані відобразиться вікно Майстер зведених таблиць і діаграм-макет. Перш ніж створювати макет зведеної таблиці, треба визначитися, яка інформація стане вводитися в області рядків, стовпців, даних і сторінок зведеної таблиці.

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

55. Макроси - програми, побудовані на основі спеціальної мови програмування VBA, що використовується в пакеті Microsoft Оffice і дає змогу виконувати рутинні дії, які часто повторюються. Розрізняють системні макроси і макроси користувача. Макроси можна створювати 2 шляхами: За допомогою макрорекордера та за допомогою редактора VBA, самостійно записуюючи команди. Макроси, що записуються за допомогою макрорекордера: макроси з абсолютними посиланнями та макроси з відносними посиланнями. Для створення макросу: Сервіс → Макрос → Начать запись; вказуємо ім’я макроса, під яким він буде зберігатися; з’являється панель “Остановка макроса” (містить дві кнопки: Остановить запись, Относительная ссылка, яка за умовчуванням активна); після натискання кнопки “Остановка макроса” макрос створено. Для активізації: Сервис → Макрос → Макросы, де у переліку вибираємо потрібний макрос → Выполнить; Способи виконання макросів: 1) Комбінація: Сервіс-Макрос-макроси…(з списку вибирається потрібний) 2) На робочому аркуші створюється графічний елемент, якому призначають виконання макросу 3) Поставити кнопку на панель інструментів і призначити їй макрос 4) Призначення макросу пункту в головному меню 5) Створення нового пункту меню в який включається підпункт з макросами.

Використання: Багато типових завдань, які виконуються в Excel, наприклад, введення даних продажу за певний день або формули на аркуш, потрібно виконати лише один раз. Трапляються й інші завдання, такі як зміна формату для діапазону клітинок, які часто повторюються, але їх легко виконати за допомогою стандартних засобів Excel. Проте завжди можна пригадати одне-два завдання, які потрібно часто виконувати та які потребують багатьох дій.

Приміром, може бути необхідно виділити на аркуші деякі важливі клітинки, створюючи звіт для ваших співпрацівників. Радше ніж виконувати всі ці дії вручну, значно доцільніше створити для них макрос, або послідовність записаних операцій. Крам того, якщо ви обізнані в мові програмування Microsoft Visual Basic® для додатків (VBA), можна самостійно написати такий макрос. У будь-якому разі, коли такий макрос створено, його можна запускати, редагувати і при потребі видалити.

56.

Сценарії входять до складу набору команд, іноді званого інструментами аналізу «що-як». Сценарій являє собою сукупність значень, які зберігаються в Microsoft Excel і можуть автоматично підставлятися на аркуш. Можливе призначення сценаріїв — прогнозування результатів моделі аркуша. Можна створювати та зберігати різні групи значень, а потім переходити до будь-якого з цих нових сценаріїв, щоб переглянути різні результати.

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

У вищенаведеному прикладі сценарію призначається ім'я «Песимістичний», для клітинки B1 установлюється значення 50 000грн., а для клітинки B2 — 13 200грн.

Другому сценарію призначається ім'я «Оптимістичний», значення B1 змінюється на 150 000грн., а значення B2 — на 26 000грн.

Підсумкові звіти по сценаріях. Для порівняння декількох сценаріїв можна створити звіт, який зіставляє їх на одній сторінці. Сценарії у звіті можуть бути розташовані один поруч з одним або підсумовані у звіті зведеної таблиці.

Створення сценаріїв:

1. У меню Сервіс виберіть команду Сценарії.

2. Натисніть кнопку Додати.

3. У полі Назва сценарію введіть назву сценарію.

4. У полі Змінювані клітинки введіть посилання на клітинки, які потрібно змінити.

ПРИМІТКА Щоб зберегти вихідні значення змінних клітинок, перед створенням сценарію, який змінюватиме ці клітинки, створіть інший сценарій, в якому використовуватимуться вихідні значення клітинок.

5. У групі Захист установіть потрібні параметри.

6. Натисніть кнопку OK.

7. У діалоговому вікні Значення клітинок сценарію введіть потрібні значення змінних клітинок.

8. Щоб створити сценарій, натисніть кнопку OK.

9. Якщо потрібно створити додаткові сценарії, натисніть кнопку Додати ще раз та повторіть вищенаведену процедуру. По завершенні створення сценаріїв натисніть кнопку OK, а потім у діалоговому вікні Диспетчер сценаріїв натисніть кнопку Закрити.

57. Посиланням однозначно визначається клітина або група клітин листа, а також cпрощується пошук значень або даних, які використовуються у формулах. З допомогою посилань можна використовувати у формулі дані, які знаходяться у різних місцях листа, а також використовувати значення однієї і й тієї ж клітини у декількох формулах. Крім того, можна посилатися на клітини, які знаходяться на інших листах книги або в іншій книзі. Посилання на клітини других книг називаються зовнішніми.

По замовчуванню в Microsoft Excel використовуються посилання типу A1, в якому стовбчики позначаються буквами від A до IV (256 стовбчиків максимально), а рядки числами - від 1 до 65536. Щоб вказати посилання на клітину, введіть букву заголовку стовбця, а після цього номер рядка. Наприклад, D50 є посиланням на клітини, розташовані на перетині стовбчика D з 50-м рядком. Щоб посилатися на діапазони клітин, введіть посилання на верхню ліву клітину діапазону, поставте двокрапку:, а після цього посилання на праву нижню клітину діапазону.

Розглянемо варіанти можливих посилань. На клітину стовбчика A в 10-му рядку можна посилатися як на A10, а на діапазон клітин в стовбчику A з 10-го рядку по 20-й як на A10:A20. Діапазон клітин у 15-му рядку зі стовбчика B по стовбчик E можна позначити B15:E15, а всі клітини у 5-му рядку як 5:5. На всі клітини між 5-й і та 10-м рядками включно можна посилатися як 5: 10, а на всі клітини у стовбчику H як на H:H. На всі клітини між стовбчиками H та J включно як H:J.

Можна також скористуватися стилем, в якому і стовбчики, так і рядки листа пронумеровані. Цей стиль, що називається R1C1. Він найбільш корисний при обчисленні позиції рядка і стовбчика у макросах, а також у відносних посиланях. У стилі R1C1, після букви R вказується номер рядка клітини, після букви C – номер стовбчика.

У залежності від поставленоого завдання можна використовувати або відносні посилання, які посилаються на клітини відносно позиції формули, або абсолютні посилання, які посилаються завжди на одину і ту ж клітину. Для абсолютних посилань використовують знак $ перед назвою стовбчика або клітини, наприклад $B$15.

Дата добавления: 2015-11-14