Пошук, діаграми, статистика та зведені таблиці

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

ШКОЛА НАВІГАЦІЇ

1. Навіщо потрібні формули і функції?
2. Визначення та створення формули
3. Відносна і абсолютна посилання на клітинку і форматування
4. Корисні функції, які ви повинні знати
5. Пошук, діаграми, статистика та зведені таблиці

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

VLOOKUP і HLOOKUP

Ось приклад для ілюстрації функцій вертикального пошуку (VLOOKUP) і горизонтального пошуку (HLOOKUP). Ці функції використовуються для перекладу числа або іншого значення в щось зрозуміле. Наприклад, ви можете використовувати VLOOKUP, щоб взяти номер деталі та повернути опис елемента.

Щоб дослідити це, давайте повернемося до нашої "Таблиці рішень" в Частині 4, де Джейн намагається вирішити, що носити в школі. Вона більше не зацікавлена ​​в тому, що вона носить, оскільки вона висадила нового хлопця, тому вона тепер буде носити випадкові костюми та взуття.

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

Вона відкриває електронну таблицю, а функція RANDBETWEEN (1,3) генерує число між або дорівнює одному і трьом, що відповідає трьом типам одягу, які вона може носити..

Вона використовує функцію RANDBETWEEN (1,5), щоб вибрати серед п'яти типів взуття.

Оскільки Джейн не може носити номер, який нам потрібно конвертувати в ім'я, ми використовуємо функції пошуку.

Ми використовуємо функцію VLOOKUP для перекладу номера одягу до назви одягу. HLOOKUP перекладається з номера взуття на різні типи взуття в рядку.

Таблиця працює так, як для одягу:

Excel вибирає випадкове число від одного до трьох, оскільки вона має три варіанти спорядження.

Далі формула переводить число в текст, використовуючи = VLOOKUP (B11, A2: B4,2), який використовує випадкове число значення з B11 для перегляду в діапазоні A2: B4. Потім він дає результат (C11) з даних, перелічених у другому стовпці.

Ми використовуємо ту ж саму техніку для вибору взуття, за винятком того, що замість HLOOKUP використовуємо VOOKUP.

Приклад: Основна статистика

Майже кожен знає одну формулу зі статистики - середню - але є ще одна статистика, яка важлива для бізнесу: стандартне відхилення.

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

Отже, як би ми, або університет, вимірюємо та інтерпретуємо результати СБ? Нижче наведено оцінки SAT для п'яти студентів, які коливаються від 1870 до 2230.

Важливими числами для розуміння є:

Середня - Середнє також називається "середнім".

Стандартне відхилення (STD або σ) - Це число показує, наскільки широко розкиданий набір чисел. Якщо стандартне відхилення велике, то цифри знаходяться далеко один від одного, і якщо він дорівнює нулю, всі числа однакові. Можна сказати, що стандартне відхилення є середньою різницею між середнім значенням і спостережуваним значенням, тобто 1,998 і кожним рахунком SAT. Зверніть увагу, що загальне скорочення стандартного відхилення використовується грецьким символом sigma “σ”.

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

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

STD	Відсоток балів	Діапазон SAT балів
1	68%	1,854-2,142
2	95%	1,711-2,285
3	99,73%	1,567-2,429
4	99,994%	1,424-2,572

Як ви можете бачити, ймовірність того, що будь-яка оцінка SAT виходить за межі 3 STD, практично нульова, оскільки 99,73% балів знаходяться в межах 3 STD.

Тепер давайте поглянемо на таблицю і пояснимо, як вона працює.

Тепер пояснюємо формули:

= СЕРЕДНЯ (B2: B6)

Середнє значення всіх балів у діапазоні B2: B6. Зокрема, сума всіх балів ділиться на кількість людей, які пройшли тест.

= STDEV.P (B2: B6)

Стандартне відхилення в діапазоні B2: B6. ".P" означає STDEV.P використовується для всіх балів, тобто, для всього населення, а не тільки для підмножини.

= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)

Це обчислює кумулятивний відсоток у діапазоні B2: B6, заснований на рахунку SAT, у цьому випадку B2. Наприклад, 83% балів нижче балів Уокера.

Графік результатів

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

Студенти знаходяться на горизонтальній осі, а їхні показники SAT відображаються у вигляді синьої гістограми за шкалою (вертикальна вісь) від 1600 до 2300.

Рейтинг ранжиру є правою вертикальною віссю від 0 до 90 відсотків і представлений сірою лінією.

Як створити діаграму

Створення діаграми - це сама тема, однак ми коротко пояснимо, як була створена вищенаведена діаграма.

По-перше, виберіть діапазон комірок, які мають бути на графіку. У цьому випадку A2 до C6, тому що ми хочемо, щоб номери, а також імена студента.

У меню "Вставити" виберіть "Графіки" -> "Рекомендовані діаграми":

Комп'ютер рекомендує діаграму "Кластерна колонка, вторинна вісь". Частина «Вторинної осі» означає, що вона малює дві вертикальні осі. У цьому випадку ця діаграма є тією, яку ми хочемо. Ми не повинні робити нічого іншого.

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

Якщо клацнути правою кнопкою миші на діаграмі, а потім вибрати "Вибрати дані", вона покаже вам, які дані вибрано для діапазону.

Функція «Рекомендовані діаграми», як правило, звільняє вас від необхідності мати справу з такими складними деталями, як визначення, які дані включати, як призначати мітки, і як призначати ліву і праву вертикальні осі.

У діалоговому вікні «Вибір джерела даних» клацніть «оцінка» в розділі «Записи легенди (серії)» і натисніть «Редагувати» і змініть його, щоб сказати «Оцінка».

Потім змініть серію 2 ("процентиль") на "Percentile".

Поверніться на свій графік і клацніть на заголовку "Chart Title" і змініть його на "SAT Scores". Тепер у нас є повна діаграма. Він має дві горизонтальні осі: одну для рахунку SAT (синю) і одну для кумулятивного відсотка (помаранчеву).

Приклад: Транспортна проблема

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

Перш ніж почати роботу з цим прикладом, потрібно ввімкнути "Excel Solver".

Увімкнути надбудову Solver

Виберіть "Файл" -> "Параметри" -> "Надбудови". У нижній частині параметрів надбудов натисніть кнопку "Перейти" поруч із пунктом "Керування: надбудови Excel".

У отриманому меню клацніть прапорець, щоб увімкнути, "Solver Add-in", і натисніть "OK".

Приклад: Розрахувати найнижчі витрати на доставку iPad

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

Ціна відправки кожного iPad - це відстань від заводу до розподільного центру до заводу, розділеного на 20 тисяч кілометрів. Наприклад, це 8,024 км від Шанхая до Мельбурна, що складає 8,024 / 20 000 або $ 40 на iPad.

Питання полягає в тому, як ми поставляємо всі ці iPads з цих трьох заводів до цих чотирьох пунктів призначення з найменшими витратами?

Як ви можете собі уявити, з'ясувати, що це може бути дуже важко без якоїсь формули та інструменту. У цьому випадку ми повинні відправити 462,000 (F12) загальних iPads. Установки мають обмежену потужність 500,250 (G12) одиниць.

У електронній таблиці, щоб ви могли бачити, як вона працює, ми ввели 1 в комірку B10, що означає, що ми хочемо відправити 1 iPad з Шанхая до Мельбурна. Оскільки транспортні витрати по цьому маршруту становлять $ 0,40 за iPad, загальна вартість (B17) становить $ 0,40.

Число було обчислено за допомогою функції = SUMPRODUCT (витрати, відправлені) "витрати" - це діапазони B3: E5.

А «відвантажені» - це діапазон B9: E11:

SUMPRODUCT помножує "витрати" у діапазон "відвантажених" (B14). Це називається "матричне множення".

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

Використання Solver

Якщо все, що ми повинні були зробити, було помножити матриці "витрат" раз "відправлені", що не було б занадто складним, але ми повинні мати справу з обмеженнями, а також.

Ми повинні відправляти те, що вимагає кожен центр розподілу. Ми ставимо цю константу в вирішувач так: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Це означає, що сума відвантаженого, тобто суми в клітинках $ B $ 12: $ E $ 12, повинна бути більшою або рівною тому, що потрібно кожному центру розподілу ($ B $ 13: $ E $ 13).

Ми не можемо доставляти більше, ніж виробляємо. Ми пишемо, що такі обмеження: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.

Тепер перейдіть до меню «Дані» і натисніть кнопку «Solver». Якщо клавішу "Solver" немає, потрібно ввімкнути надбудову Solver.

Введіть два обмеження, описані раніше, і оберіть діапазон "Відправлення", який є діапазоном чисел, які ми хочемо, щоб Excel обчислив. Також виберіть алгоритм за замовчуванням “Simplex LP” і вкажіть, що ми хочемо “мінімізувати” клітинку B15 (“загальні витрати на доставку”), де написано “Set Objective”.

Натисніть "Вирішити" і Excel збереже результати в електронній таблиці, що ми хочемо. Ви також можете зберегти це, щоб можна було грати з іншими сценаріями.

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

Тут Excel говорить, що знайшла рішення. Натисніть "OK", щоб зберегти рішення і повернутися до електронної таблиці.

Приклад: Чиста приведена вартість

Як компанія вирішує, чи інвестувати в новий проект? Якщо “чиста теперішня вартість” (NPV) є позитивною, вони інвестують у неї. Це стандартний підхід більшості фінансових аналітиків.

Наприклад, припустимо, що гірничодобувна компанія Codelco хоче розширити мідний рудник Андінас. Стандартний підхід для визначення того, чи слід рухатися вперед з проектом, - це обчислення чистої поточної вартості. Якщо NPV більше нуля, то проект буде прибутковим з урахуванням двох входів (1) часу і (2) вартості капіталу.

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

У перший рік, 45 млн. Дол. США виділено як капітал для фінансування проекту. Бухгалтери визначили, що їхня вартість капіталу становить шість відсотків.

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

Через 13 років NPV становить $ 3,945,074 USD, тому проект буде прибутковим. За даними фінансових аналітиків, термін «окупності» становить 13 років.

Створення зведеної таблиці

«Зведена таблиця» - це в основному звіт. Ми називаємо їх зведеними таблицями, тому що ви можете легко перемикати один тип звіту в інший без необхідності вносити весь новий звіт. Так вони поворот на місці. Покажемо основний приклад, який викладає основні поняття.

Приклад: Звіти про продажі

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

Припустимо, у нас є три продавці - Карлос, Фред і Джулі - всі продають нафту. Їх продаж у доларах за фіскальний квартал за 2014 рік наведено в таблиці нижче.

Щоб створити ці звіти, ми створюємо зведену таблицю:

Виберіть "Вставити -> Зведена таблиця, вона знаходиться з лівого боку панелі інструментів:

Виберіть усі рядки та стовпці (включаючи ім'я продавця), як показано нижче:

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

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

Як ви можете бачити, ми вибрали всі чотири поля для додавання до звіту. Поведінка Excel за замовчуванням групує рядки за текстовими полями, а потім підсумовує всі інші рядки.

Тут це дає нам суму 2014 + 2014 + 2014 + 2014 = 24,168, що є нісенітницею. Також це дало суму чверті 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Ця інформація не потрібна, тому ми знімаємо виділення з цих полів, щоб видалити їх з нашої зведеної таблиці.

«Сума продажів» (загальний обсяг продажів) є доречною, тому ми це виправимо.

Приклад: Продаж від продавця

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

У діалоговому вікні, що з'явилося, ми змінюємо назву на "Загальні продажі", потім на "Формат номера" і змінюємо на "Валюта".

Потім ви можете побачити свою ручну роботу у зведеної таблиці:

Приклад: Продажі від продавця і кварталу

Тепер давайте додамо проміжні підсумки для кожного кварталу. Щоб додати проміжні підсумки, просто клацніть лівою кнопкою миші на полі "Квартал" і утримуйте та перетягніть її до розділу "рядки". Результат можна побачити на знімку нижче:

Поки ми на це, давайте видалимо значення "Сума кварталу". Просто натисніть стрілку та натисніть "Видалити поле". Тепер на знімку вікна ви можете побачити рядки "Квартал", які розбивають продажі кожного продавця за квартал.

З урахуванням цих свіжих навичок тепер ви можете створювати зведені таблиці зі своїх даних!

Висновок

Завершуючи, ми показали вам деякі особливості формул і функцій Microsoft Excel, які ви можете застосувати Microsoft Excel до ваших бізнес, академічних або інших потреб.

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

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

Пам'ятайте, що якщо ви хочете повернутися назад і взяти клас знову, ви можете почати свіже з уроку 1!