Показвания, графики, статистически данни и обобщени таблици

След като прегледахме основните функции, препратките на клетките и функциите за дата и час, сега се потопим в някои от по-напредналите функции на 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) от данните, изброени във втората колона.

Използваме същата техника, за да изберем обувки, но този път използваме VOOKUP вместо HLOOKUP.

Пример: Основна статистика

Почти всеки знае една формула от статистиката - средна - но има друга статистика, която е важна за бизнеса: стандартно отклонение.

Например, много хора, които са ходили в колежа, са агонизирали със своите SAT резултати. Те биха искали да знаят как се класират в сравнение с другите студенти. Университетите искат да знаят и това, защото много университети, особено престижни, отхвърлят учениците с нисък SAT резултат.

И така, как бихме могли ние, университет, да измерим и интерпретираме резултатите от СБ? По-долу са поставени резултати от SAT за пет студенти, вариращи от 1,870 до 2,230.

Важните номера за разбиране са:

Средно аритметично - Средната стойност също се нарича „средна стойност“.

Стандартно отклонение (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..

Сега да разгледаме отново електронната таблица и да обясним как работи.

Сега ще обясним формулите:

= Средна (В2: В6)

Средната стойност на всички резултати в диапазона B2: B6. По-конкретно, сумата от всички резултати, разделена на броя на хората, които са взели теста.

= STDEV.P (В2: В6)

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

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

Това изчислява кумулативния процент над диапазона B2: B6 въз основа на SAT резултата, в този случай B2. Например, 83% от точките са под резултат на Уокър.

Графиране на резултатите

Поставянето на резултатите в графика прави по-лесно разбирането на резултатите, както и че можете да го покажете в презентация, за да направите вашата точка по-ясна.

Учениците са на хоризонталната ос и техните SAT стойности са показани като синя графика на скала (вертикална ос) от 1600 до 2300.

Ранжирането на проценти е дясната вертикална ос от 0 до 90 процента и е представена от сивата линия.

Как да създадете диаграма

Създаването на диаграма е тема за себе си, но ще обясним накратко как е създадена горната таблица.

Първо изберете диапазона от клетки, които ще бъдат в диаграмата. В този случай от A2 до C6, защото искаме числата, както и имената на учениците.

От менюто "Вмъкване" изберете "Графики" -> "Препоръчителни диаграми":

Компютърът препоръчва диаграма "Клъстерна колона, вторична ос". Част „Вторична ос“ означава, че тя изтегля две вертикални оси. В този случай тази диаграма е тази, която искаме. Не е нужно да правим нищо друго.

Можете да използвате преместване на графиката и да я преоразмерите, докато го имате като размер и в желаната позиция. След като сте доволни, можете да запазите графиката в електронната таблица.

Ако щракнете с десния бутон върху графиката, а след това върху „Избор на данни“, тя ще ви покаже какви данни са избрани за обхвата.

Функцията "Препоръчителни диаграми" обикновено ви освобождава от необходимостта да се справяте с такива сложни детайли като определянето на какви данни да се включат, как да присвоите етикети и как да присвоите левите и десните вертикални оси.

В диалоговия прозорец „Избор на източник на данни“ кликнете върху „Резултат“ в „Легенди за въвеждане (серии)“ и натиснете „Редактиране“ и го променете, за да каже „Резултат“.

След това променете серия 2 („процентил“) на „Percentile“.

Върнете се на графиката си и кликнете върху „Заглавието на диаграмата“ и я променете на „SAT Scores“. Сега имаме пълна таблица. Тя има две хоризонтални оси: една за SAT резултат (синя) и една за кумулативен процент (оранжев).

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

Проблемът при транспортирането е класически пример за тип математика, наречен „линейно програмиране“. Това ви позволява да увеличите или сведете до минимум стойността си при определени ограничения. Той има много приложения за широк спектър от бизнес проблеми, така че е полезно да научите как работи.

Преди да започнем с този пример, трябва да активираме "Excel Solver".

Активиране на добавката на Solver

Изберете “Файл” -> “Опции” -> “Добавки”. В долната част на опциите на добавките кликнете върху бутона „Go“ до „Manage: Excel Add-ins“.

В полученото меню кликнете върху квадратчето за отметка, за да активирате „Solver Add-in“ и кликнете върху „OK“.

Пример: Изчислете най-ниските разходи за доставка на iPad

Да предположим, че ние доставяме iPads и се опитваме да запълним нашите дистрибуционни центрове с възможно най-ниските транспортни разходи. Имаме споразумение с компания за превоз на товари и авиокомпании, за да доставим iPads от Шанхай, Пекин и Хонг Конг до центровете за разпространение, показани по-долу.

Цената за доставка на всеки iPad е разстоянието от фабриката до разпределителния център до централата, разделено на 20 000 километра. Например, от Шанхай до Мелбърн е 8,024 км, което е 8,024 / 20 000 или $ 40 за iPad.

Въпросът е как да изпратим всички тези iPad от тези три завода до тези четири дестинации на възможно най-ниска цена?

Както можете да си представите, разбирането за това може да бъде много трудно без някаква формула и инструмент. В този случай трябва да изпратим 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.

Сега отидете в менюто “Data” и натиснете бутона “Solver”. Ако бутонът "Solver" не е там, трябва да активирате добавката Solver.

Въведете двата ограничения, описани по-рано, и изберете диапазон „Доставка“, който е диапазонът от числа, които искаме Excel да изчисли. Също така изберете алгоритъма по подразбиране „Simplex LP“ и посочете, че искаме да „минимизираме“ клетката B15 („общи разходи за доставка“), където се казва „Задаване на цел“.

Натиснете “Solve” и Excel съхранява резултатите в електронната таблица, което искаме. Можете също да запазите това, за да можете да играете с други сценарии.

Ако компютърът каже, че не може да намери решение, тогава сте направили нещо, което не е логично, например, може да сте поискали повече iPads, отколкото растенията могат да произвеждат.

Тук Excel казва, че е намерил решение. Натиснете “OK”, за да запазите решението и да се върнете към електронната таблица.

Пример: Нетна настояща стойност

Как една компания решава дали да инвестира в нов проект? Ако „нетната настояща стойност“ (NPV) е положителна, те ще инвестират в нея. Това е стандартен подход, възприет от повечето финансови анализатори.

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

На обикновен английски език, цената на капитала означава колко ще спечелят тези пари, ако просто я напуснат в банката. Използвате цената на капитала, за да намалите паричните стойности до сегашната стойност, с други думи, 100 долара за пет години може да са $ 80 днес.

През първата година за финансиране на проекта са заделени 45 млн. Долара. Счетоводителите са определили, че цената им на капитал е шест процента.

Тъй като те започват да добиват, парите започват да идват, тъй като компанията открива и продава медта, която произвеждат. Очевидно е, че колкото повече са моите, толкова повече пари правят, а прогнозата им показва, че техният паричен поток нараства, докато достигне 9 милиона долара годишно..

След 13 години, NPV е $ 3,945,074 USD, така че проектът ще бъде печеливш. Според финансовите анализатори „периодът на изплащане“ е 13 години.

Създаване на Pivot таблица

„Сводната таблица“ е в основата си доклад. Наричаме ги осеви таблици, защото можете лесно да ги превключите към един вид отчет в друг, без да се налага да правите цял нов отчет. Така че те шарнирен болт на място. Да покажем основен пример, който учи основните понятия.

Пример: отчети за продажбите

Хората от продажбите са много конкурентни (това е част от това, че са продавач), така че те естествено искат да знаят как се справят един с друг в края на тримесечието и в края на годината, както и колко ще бъдат техните комисионни.

Да предположим, че имаме трима продавачи - Карлос, Фред и Джули - всички продават петрол. Продажбите им в долари за фискално тримесечие за 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!