Головна

Приклади обчислень з використанням стандартних функцій

  1. Список похідних найпростіших елементарних функцій
  2. II. Випишіть з тексту приклади вживання в англійському реченні неособистих форм дієслова.
  3. II. Випишіть з тексту приклади вживання в англійському реченні неособистих форм дієслова.
  4. XII. МЕДИКО-ПСИХОЛОГІЧНА ДІАГНОСТИКА: ПОРУШЕННЯ ПСИХІЧНИХ ФУНКЦІЙ, станів, МОВНОЇ ДІЯЛЬНОСТІ І ОСОБИСТІСНИХ ВЛАСТИВОСТЕЙ
  5. Аналіз витрат з використанням коефіцієнта кореляції
  6. Аналіз лінійних ланцюгів гармонійного струму з використанням комплексного перетворення (методом комплексних амплітуд)
  7. Аналіз простих резистивних ланцюгів з використанням законів Кірхгофа.

Підсумовування.Для найпростішого підсумовування використовують функцію СУММ, синтаксис якої суми (А), де: А - список від 1 до 30 аргументів. Аргумент може бути осередком, діапазоном осередків, числом або формулою. Посилання на порожні клітинки, текстові або логічні значення ігноруються. Фактично дана функція замінює безпосереднє підсумовування з використанням оператора складання (+). Формула суми (В2: В5), зазначена в осередку В6 (рис. 4.13), відповідає формулі = В2 + В3 + В4 + В5.


Мал. 4.13. Підсумовування з використанням функції СУММ

Для виконання вибіркового підсумовування стовпців чисел, наприклад потрібно скласти тільки числа в стовпцях В і D, а стовпець C виключити формула складання матиме вигляд: = сума (В2: В5; D2: D5). Для виконання підсумовування осередків діапазону, які відповідають заданим умовам, слід використовувати функцію СУММЕСЛИ, що має наступний синтаксис:

СУММЕСЛІ (діапазон; критерій; діапазон_суммірованія), де:

· Діапазон - діапазон адрес обчислюваних осередків;

· Критерій - критерій у вигляді числа, виразу або тексту, що визначає підсумовувані осередки. Наприклад, критерій може бути виражений як 24, "> 22";

· Діапазон_суммірованія - фактичні комірки для підсумовування.

Осередки в області діапазон_суммірованія підсумовуються, якщо відповідні їм осередки в аргументі «діапазон» відповідають критеріям. Якщо діапазон_суммірованія опущений, то підсумовуються осередки в аргументі «діапазон». Excel пропонує додаткові функції, які можна застосовувати для аналізу даних з використанням умов. Наприклад, для підрахунку числа появ текстового рядка або числа в межах діапазону комірок, використовуйте функцію СЧЁТЕСЛІ. Для отримання формули, яка повертає в залежності від виконання умови одне з двох значень, наприклад винагорода за вказаною обсягом продажів, використовуйте функцію ЯКЩО. На рис. 4.14 представлений приклад підсумовування ставок комісійних закуповуваного устаткування, значення яких перевищують 12000.

Мал. 4.14. підсумовування з використанням функції СУММЕСЛИ

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

Зведення в ступінь.Для зведення в ступінь використовують функцію РІВЕНЬ, синтаксис якої ступеня (число; ступінь), де:

· Число - підстава, яке може бути будь-яким дійсним числом;

· Ступінь - показник ступеня, в яку зводиться підстава.

Замість функції РІВЕНЬ для зведення в ступінь можна також використовувати оператор ^, наприклад 7 ^ 2. Негативні числа можна підносити до степеня, значення якої є цілим числом, інших обмежень немає.

Мал. 4.15. Вибіркове підсумовування з використанням функції

"СУММЕСЛИ"

Тригонометричні функції.В Excel можна виконувати як прямі, так і зворотні тригонометричні функції. Синтаксис для прямих тригонометричних функцій має єдиний вид і, наприклад, для функції SIN синтаксис наступний: SIN (А), де: А - кут в радіанах.

Синтаксис для всіх зворотних тригонометричних функцій також має однаковий вигляд і для функції АSIN синтаксис - АSIN (А), де:

А - число, що дорівнює синусу визначається кута.

Слід звернути увагу, що всі тригонометричні обчислення виконуються для кутів, представлених в радіанах. Для перекладу в градуси необхідно використовувати функції перетворення або самостійно переводити значення, використовуючи функцію Пі (). Функція Пі () вставляє значення числа ПІ, функція не має аргументів, але дужки необхідно вказувати. Наприклад, для обчислення значення синуса кута в градусах, потрібно його помножити на Пі () / 180. На рис.4.16. наведено приклад обчислення функції SIN (A) для аргументів, заданих в градусах (від 0 до 90 градусів).

Розрахунок середніх значень.У простому випадку для розрахунку середнього арифметичного значення використовують функцію СРЗНАЧ, що має наступний синтаксис:

СРЗНАЧ (А), де:

А - список від 1 до 30 елементів, середнє значення яких потрібно знайти. Елемент може бути коміркою, діапазоном осередків, числом або формулою.

Знаходження екстремальних значень.Для знаходження екстремальних значень (найбільшого або найменшого) в масиві даних використовуються функції МАКС і МІН. Синтаксис функції МАКС:

Максим (А), де:

А - список від 1 до 30 аргументів, серед яких потрібно знайти найбільше значення. Елемент може бути коміркою, діапазоном осередків, числом або формулою. функція МІН має такий же синтаксис, що і функція МАКС. Функції МАКС і МІН визначають тільки крайні значення, але не показують, в якій комірці ці значення знаходяться.

Мал. 4.16. Обчислення функції SIN (А)

На рис. 4.17 показаний приклад визначення максимального значення вартості туру.

Мал. 4.17. Знаходження максимальної вартості туру

Розрахунок кількості осередків.Для визначення кількості осередків, що містять числові значення, можна використовувати функціюРАХУНОК,маєсінтаксіс:

Рахунок (А), де:

А - список аргументів від 1 до 30, серед яких потрібно визначити кількість осередків, що містять числові значення. Елемент може бути коміркою, діапазоном осередків, числом або формулою. У таблиці на рис.4. х наведено приклад визначення в таблиці числових значень в осередках А2: А6.

Рис.4.18. Розрахунок кількості осередків, що містять числа, з використанням функції "РАХУНОК"

Для визначення кількості осередків, що містять числові, текстові, логічні значення, слід використовувати функцію СЧЕТЗ, синтаксис якої СЧЕТз (А), де:

А - список від 1 до 30 елементів, серед яких потрібно визначити кількість осередків, що містять будь-які значення. Елемент може бути коміркою, діапазоном осередків, числом або формулою.

Помилки в формулах Excel.Якщо формула побудована неправильно, Excel формує відповідну помилку, основними причинами появи яких наступні:

· # Значить! - Використовується неприпустимий тип аргументу;

· # СПРАВ / 0! - У формулі виконується розподіл на нуль;

· # ІМ'Я? - Excel не може визначити що використовується у формулі ім'я;

· #ПОСИЛАННЯ! - Використовується неприпустима посилання на осередок;

· # Н / Д - невизначені дані, при некоректному визначенні аргументів функції;

· # ПУСТО! - Задано перетин двох областей, що не мають загальних вічок.

Виникнення помилок може пояснюватися також і рядом інших причин, з якими можна ознайомитися в фірмових матеріалах.

4.3.4. Копіювання даних, адресація осередків

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

1. Застосування буфера обміну. Буфер обміну - це область оперативної пам'яті, призначена для тимчасового зберігання інформації. Копійований або переміщується інформація міститься в буфер обміну після її виділення та застосування до виділеного боці команд Копіювати або Вирізати. З буфера обміну інформація вставляється в позначене місце командою Вставити.

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

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

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

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

Відносної називається посилання, автоматично змінюється при копіюванні містить її формули. Зміна відбувається таким чином, що зберігається відстань по обох частин адреси між осередком, на яку проводиться посилання і тим осередком, звідки вона виконується. Відносне посилання виглядає як звичайний адресу осередки, що складається з стовпця і рядка, наприклад, А8. При зміні позиції комірки, що містить формулу, змінюється і посилання. При копіюванні формули вздовж рядків або стовпців посилання автоматично коректується. Наприклад, при копіюванні формули з відносними посиланнями з осередку С1 в осередку С2 і С3 посилання у формулі автоматично змінюється: = A1 + В1 на = A2 + В2 і = А3 + С3 відповідно. На ріс.4.19 показаний приклад копіювання формули з використанням відносної посилання.

Абсолютною називається посилання, що не змінює посилань при копіюванні формули, що містить їх. Запис абсолютної посилання виходить із запису відносної посилання додаванням знака долара $ в загальному випадку як перед заголовком стовпця, так і перед заголовком рядка, наприклад $ A $ 1. Розглянемо приклад, який показує використання абсолютних посилань (рис.4.20.). При копіюванні формули обчислення відсотка продажів товару окремо по кожному місяцю необхідно змінювати посилання в чисельнику формули і не змінювати в знаменнику, що і забезпечує використання абсолютної адресації.

  A B C  
 = A1 + B1  до копіювання
 = A2 + B2  після копіювання
 = A3 + B3  після копіювання
       

Ріс.4.19. Копіювання формули з відносною посиланням

Для січня ця частка може бути обчислена за формулою = B1 / B4, всі посилання в цій формулі відносні. Тому при копіюванні цієї формули в осередку С2 і С5 отримаємо помилку "# СПРАВ / 0!". Це станеться тому,

  A B C D
   місяць  товар  Відсоток по місяцях  Примітка
 січень  = B1 / B $ 4 (32%)  До копіювання формули
 лютий  = B2 / B $ 4 (43%)  після копіювання
 Березень  = B3 / B $ 4 (25%)  після копіювання
 Разом  = B2 + B3 + B4 (16)  (100%)  
       

Рис.4.20. Копіювання формули з абсолютною посиланням

що обидва посилання будуть змінені і для комірки С2 отримаємо: B1 / B5, а комірка В5 не заповнена, тобто містить значення 0. Щоб зробити формулу коректної для копіювання, посилання на В4 повинна бути абсолютною. Для цього перед номером рядка 4 необхідно помістити знак $. Таке посилання на осередок називається змішаною, тобто відносної для адреси стовпця і абсолютної - для адреси рядків таблиці. При необхідності посилання можна зробити абсолютної як для рядків, так і для стовпців таблиці, наприклад $ B $ 4.

Змішані посилання.Змішана посилання містить абсолютний стовпець і відносну рядок або абсолютну рядок і відносний стовпець. Абсолютне посилання стовпців має вигляд $ A1, $ B1 і т. Д, а абсолютне посилання рядка має вигляд A $ 1, B $ 1 і т. Д. При зміні позиції комірки, що містить формулу, відносна посилання змінюється, а абсолютна не змінюється. При копіюванні формули вздовж рядків і стовпців відносна посилання автоматично коректується, а абсолютне посилання не коригується. Для швидкої зміни типу посилання використовується клавіша F4. Введіть в осередок А1 будь-яке число, а в клітинку А2 введіть формулу = А1. Потім натискайте клавішу F4 і зверніть увагу, що після кожного натискання клавіші тип посилання буде змінюватися (рис.4.21).

Мал. 4.21. Зміна типу посилання

4.4. Побудова діаграм і графіків в Excel

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

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

Типи діаграм.Excel дозволяє створювати великий набір різних діаграм, що включають більше 30 типів діаграм різних різновидів. Вибір типу діаграми визначається завданнями, які розв'язуються при її створенні (ріс.4.22). Для вибору відповідного варіанту діаграми рекомендується подивитися різні типи і види для кожного завдання і вибрати найкращий варіант. Крім вбудованих типів діаграм, користувач може створювати, зберігати, а потім використовувати власні типи діаграм. Один з типів діаграм є стандартним, тобто він використовується за умовчанням при створенні діаграм. Зазвичай стандартної діаграмою є плоска гістограма.

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

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

Якщо необхідно побудувати діаграму для всіх даних таблиці, то можна виділити одну будь-яку клітинку, а потім клацнути по стрілці кнопки Тип діаграми панелі Діаграми і вибрати тип створюваної діаграми.

Найбільш зручним способом створення діаграми є використання Майстра діаграм,викликається натисканням кнопки Майстер діаграм  на панелі стандартна, Після чого з'явиться діалогове вікно Майстер діаграм, що забезпечує побудову діаграм за 4 кроку.

1. Вибір типу та виду діаграми.після виклику майстра діаграмв діалоговому вікні Майстер діаграм (крок 1 з 4): тип діаграми слід вибрати тип і вид діаграми. Діалогове вікно має дві вкладки: стандартні и нестандартні. у вкладці стандартні розташовані стандартні типи діаграм Excel і їх різновиди. Для перегляду зовнішнього вигляду обраної діаграми слід натиснути і утримувати кнопку Перегляд результату. Обраний тип і вид діаграми можна буде змінити в подальшому при редагуванні та оформленні діаграми.

2. Вибір джерела даних.У діалоговому вікні Майстер діаграм (крок 2 з 4): джерело даних діаграми необхідно вибрати джерело даних для діаграми (ріс.4.22), діалогове вікно має дві вкладки: діапазон даних и ряд.

Мал. 4.22. Вибір джерела даних діаграми

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

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

Зміст вкладки Ряд залежить від типу обраної діаграми, а також від вибору варіанта побудови рядів даних.

Після закінчення роботи з джерелами даних діаграми в діалоговому вікні Майстер діаграм (крок 2 з 4): джерело даних діаграми слід натиснути кнопку Далі.

3. Вибір параметрів діаграми.Виконується на 3-емшаге вокне Майстер діаграм (крок 3 з 4): параметри діаграми. На даному етапі майстром пропонується визначити заголовок створюваної діаграми, використовувати лінії сітки, включити легенду в будь-якому місці діаграми, дати імена осях Х і У, визначити підписи даних і клацнути далі для переходу на останній четвертий крок.

4. Розміщення діаграми. Виконується в вікні Майстер Діаграм (крок 4 з 4): розміщення діаграми.На цьому кроці користувач визначає, де помістити діаграму: на поточному робочому аркуші або на окремому аркуші книги. Після натискання кнопки Готово Excel створить діаграму. Потім користувач може внести зміни даних у вихідній таблиці, що автоматично відіб'ється на побудованої діаграмі.

4.5. Обробка табличних даних в Excel

4.5.1. угруповання даних

При обробці і аналізі таблиць великої розмірності часто потрібно виділяти дані, що відносяться до однієї групи і це можна реалізувати засобами Excel шляхом створення так званої структури. Дана структура дозволяє показувати і приховувати рядки додаткових відомостей про місячному обсязі продажів, що особливо важливо при великому значенні рядків, що не вміщається на одному екрані. На рис. 4.23 у верхній таблиці рядки розбиті на дві групи: 1 і 2 квартали, кнопки "+" і "-" розкривають і приховують групи, а кнопки 1 і 2 - рівні груп. На ріс.4.24 (нижньої таблиці) дані по 1-го кварталу приховані, а по 2-го кварталу відкриті. Кожна з груп формується окремо. В меню дані виберіть команду Група і структура, а потім - створення структури. Виділіть рядки або стовпці, які містять відомості.

Ріс.4.23. угруповання даних

Рядки або стовпці відомостей зазвичай прилягають до рядка або колонку з підсумкові формули або заголовки. Наприклад, для розглянутого прикладу виділіть рядки 3 5 і 7 9. У загальному випадку необхідно продовжувати виділення і угруповання рядків або стовпців відомостей і виконання команди «Групувати» до тих пір, поки не будуть створені всі необхідні рівні структури. Дана структура дозволяє показувати і приховувати рядки додаткових відомостей про місячному обсязі продажів. Якщо підсумкові рядки розташовані над рядками даних або підсумкові стовпці розташовані зліва від стовпців даних, змініть параметри розташування.

4.5.2. Сортування і фільтрація даних

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

Сортування даних.Найкраще, якщо сортований список матиме заголовки стовпців. Сортування або упорядкування списків значно полегшує пошук і аналіз даних. Після сортування записи відображаються в порядку, визначеному значеннями стовпців за алфавітом, зростанню, зменшенням та іншими критеріями. Найбільш просто сортування виділених осередків виконується при використанні кнопок сортування по зростанню і зменшенням (ріс.4.24).

Ріс.4.24. виконання сортування

Для виконання сортування курсор необхідно помістити в стовпці, по якому виконується сортування. Вибравши на панелі інструментів "Сортування від А до Я", даний список буде відсортований за одну колонку, тобто по полю Турфірма. Для виконання сортування за кількома стовпцями можна також використовувати дані кнопки сортування, але більш ефективним інструментом сортування є команда дані> Сортування.На робочу аркуші з'являється діалогове вікно Сортування,вкотором можна задати три ключа сортування. Це дозволить розставити рядки за обраним критерієм сортування (зростання або спадання) в стовпці В, а всередині груп інших стовпців з однаковими значеннями стовпчика В розставить по обраному критерію в стовпці С (ріс.4.24). Таким чином, виконання сортування в загальному випадку включає наступні етапи:

· Необхідно виділити осередок у списку, який потрібно впорядкувати;

· в меню дані виберіть команду Сортування;

· Вибір стовпців сортування в полях Сортувати за и Потім по.

· Виберіть інші параметри сортування та натисніть кнопку OK.

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

· автофильтр, Включаючи фільтр по виділеному, для простих умов відбору;

· розширений фільтр для більш складних умов відбору.

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

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

Для використання автофільтра необхідно виділити якусь

осередок вихідної таблиці і виконати команду Дані-Фільтр-Автофільтр.

Ріс.4.25. Результати роботи турагентів

У кожному осередку верхнього рядка з'являються кнопки зі стрілками, що відбивають наявність якого-небудь списку (рис. 4.26).

Ріс.4.26. Таблиця вихідних даних

Якщо клацнути по кнопці-стрілці в стовпці «місто», то з'являється список з усіма введеними в таблицю містами і можна вибрати цікавий місто. Виберемо місто СПб для аналізу діяльності в ньому агентів (ріс.4.27).

Ріс.4.27. виконання фільтрації

Excel залишив в відфільтрованої таблиці тільки результати роботи представників цього міста. Аналогічно можна проаналізувати дані по інших містах таблиці або зробити фільтрацію по агентам і т. П. Отримані таблиці можна відформатувати, роздрукувати або передати по електронній пошті .. Щоб вийти з режиму фільтрації потрібно виконати команду дані-Фільтр-відобразити Все.

Для відфільтрованих таблиць можна виконати обробку даних: обчислити суми, твори, так, як ніби ніяких прихованих рядків в таблиці немає. Якщо помістити курсор в вільну комірку якогось стовпця, виділити потрібні комірки і натиснути на кнопку автосуммирования «сигму», то виконається спеціальна функція Проміжні висновки. Ця функція, наприклад буде підсумовувати тільки рядки, які видно, а інші невидимі пропускає. Якщо фільтр зміниться, то зміняться і проміжні результати. Поля, за якими встановлено фільтр, відображаються із позначкою воронки. Якщо підвести покажчик миші до такої воронці, то буде показано умова фільтрації.


4.6. Об'єднання електронних таблиць

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

Ріс.4.28. об'єднання таблиць

Дві таблиці називаються пов'язаними, коли в осередках одній з них присутні посилання на комірки іншого. Нехай таблиця Прайс-лист містить ціни на продавані книги, а таблиця Калькуляція - кількість і вартість кожної книги. Обидві таблиці належать одній книзі і розміщені на Лист1 і Лист2 (ріс.4.28). Вартість книг на Аркуш2 в таблиці Калькуляція обчислюється з використанням значення ціни на Лист1. Формула для обчислення вартості книг на Аркуш2 включає ім'я Аркуш1 і адреси осередків, розділених знаком оклику: = В4 * Лист 1! В 4. Якщо посилання на осередок здійснюється з іншої книги, то на заслання додається повне ім'я книги, тобто шлях до файлу і ім'я файлу. При цьому ім'я книги полягає в квадратні дужки, а повне ім'я файлу в апострофи.

4.7. Аналіз даних за допомогою зведених таблиць

Зведені таблиці - це дуже потужний і ефективний засіб Excel для роботи з даними, вони призначені для наочного перегляду та аналізу даних великих таблиць, так як традиційними засобами робити це складно, а іноді і практично неможливо. Зведеними називаються таблиці, що містять дані аналізованої таблиці таким чином, щоб зв'язки між ними відображалися найбільш наочно. Зведена таблиця створюється на основі відформатованого списку значень, тому, перш ніж створювати зведену таблицю, необхідно підготувати відповідним чином дані. Зведені таблиці дозволяють структурувати дані, представлені в невпорядкованою формі в таблицях, автоматично сформувати будь-які підсумкові результати за обраними критеріями для отриманого документа. Як приклад побудови зведеної таблиці, з якого буде зрозуміла технологія процесу, розглянемо перетворення вихідної невпорядкованою інформації про продаж турів за один квартал трьома турфірмами (ріс.4.29).

Ріс.4.29. Побудова зведеної таблиці

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

Для створення зведеної таблиці виконується команда Дані - Зведена таблиця,в результаті чого на екрані з'являєтьсяМайстер зведених таблиць, реалізований дану процедуру за три кроки:

· На першому кроці вибирається джерело даних, для нашого прикладу вони зберігаються в базі даних листа Excel і натискається кнопка Далі;

· На другому кроці визначається діапазон адрес вихідних даних і виконується перехід до кроку 3 командою Далі;

· На третьому кроці, що є найголовнішим, здійснюється вибір структури створюваної зведеної таблиці, крок починається з натискання кнопки макет (Див. Ріс.4.29), після чого на екрані з'являється вікно, представлене на ріс.4.30.

Ріс.4.30. Макет зведеної таблиці

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

· Сторінка - поле використовується для заголовка сторінки зведеної таблиці;

· Стовпець - поле використовується в якості заголовка стовпців зведеної таблиці;

· Рядок - поле використовується для заголовків рядків зведеної таблиці;

· Дані - поле, в якому відображається підсумкові результати зведеної таблиці.

На ріс.4.31 наведена результуюча зведена таблиця для розглянутого прикладу. використовуючи кнопки в таблиці (чорні трикутники),

Ріс.4.31. результуюча зведена таблиця

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

4.8. Рішення типових задач засобами Excel

4.8.1. підбір параметрів

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

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

Розглянемо задачу підбору параметра на прикладі аналізу обсягу продажів турів відповідно до таблиці на ріс.4.32. В осередку В5 приведена формула розрахунку прибутку, в якій вартість туру є змінною, а інші параметри константами, тому потрібну прибуток в 11000 у. е. в даному прикладі будемо отримувати шляхом підбору вартості туру (осередок В2). Для підбору шуканого параметра помістіть курсор в комірку В5 необхідно виконати командуСервіс-Підбір параметра, В результаті чого з'явиться вікно для підбору параметра (рис 4.32). Введіть значенняприбутку 11000 уе, адреса змінною осередки В2 і натисніть ОК. В результаті перерахованих дій отримані наступні значення (ріс.4.33).

Ріс.4.32. Вікно підбору параметра

Ріс.4.33. Результат підбору параметра


4.8.2. аналіз і прогнозування даних

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

Для такої постановки завдання її математична формулювання може виглядати наступним чином. Є залежність змінної E від змінної X, отримана шляхом експерименту E = F (X). Потрібно побудувати аналітичне опісаніедляфункціі T = F (X), де T (X) - деяка функція від X, що щонайкраще описує спостережувані експериментальні значення E. Зазвичай T = F (X) слід вибирати так, щоб мінімізувати суму квадратів різниць між експериментальними і теоретичними значеннями E і T, т. е. мінімізувати деякий функціонал:

де n - число спостережень. При вирішенні такого завдання головною проблемою є вибір певної математичної функції, що дозволяє найбільш достовірно описувати отримані експериментальні дані і прогнозувати очікувані результати. В Excel існує можливість розраховувати найбільш підходящу лінію, яка з деякою точністю описує експериментальні дані, яку називають лінією тренда. Лінія тренда - статистичний інструмент, який представляє собою лінію T, побудовану на основі даних діаграми Е з використанням деякої апроксимації. У деяких випадках цими розрахованими результатами можна скористатися для аналізу тенденцій ринку збуту деякої продукції і короткострокового прогнозування.

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

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

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

Ріс.4.34. Підбір лінії тренду

Потім у вікні лінія тренда виберете кнопку а потім визначте прогноз, а також вкажіть необхідність відображення на графіку отриманого аналітичного рівняння і коефіцієнта детермінації R2. Ступінь наближення апроксимуючої функції до експериментального закону зміни оцінюється за допомогою коефіцієнта детермінації R2. Чим ближче значення даного коефіцієнта до 1, чим вище ступінь близькості. Як випливає з підібраного математичного опису коефіцієнт R2= 0.9846, що відповідає дуже хорошому вибору методу апроксимації, а вид отриманого рівняння наведено на графіку.

Досить актуальною є задача побудови аналітичних залежностей для функцій від двох і більше змінних, Для подібного роду залежностей апроксимацію можна виконати, використовуючи функції з статистичної групи: ЛИНЕЙН і ЛГРФПРІБЛ. Функція ЛИНЕЙН виконує статистичну оцінку для ряду з використанням методу найменших квадратів для обчислення апроксимуючої залежності. Функція повертає масив, що описує отриману функцію.

 




Універсальний код - Unicode | Растрова графіка | Векторна графіка | фрактальна графіка | Коротка історія розвитку комп'ютерів 1 сторінка | Коротка історія розвитку комп'ютерів 2 сторінка | Коротка історія розвитку комп'ютерів 3 сторінка | Коротка історія розвитку комп'ютерів 4 сторінка | Коротка історія розвитку комп'ютерів 5 сторінка | Коротка історія розвитку комп'ютерів 6 сторінка |

© um.co.ua - учбові матеріали та реферати