След като прегледахме основните функции, референтните клетки и функциите за дата и час, ние се впускаме в някои от по-усъвършенстваните функции на Microsoft Excel. Представяме методи за решаване на класически проблеми в областта на финансите, отчетите за продажбите, транспортните разходи и статистиката.
УЧИЛИЩНА НАВИГАЦИЯТези функции са важни за бизнеса, студентите и тези, които просто искат да научат повече.
Ето пример за илюстриране на функциите за вертикално търсене (VLOOKUP) и хоризонтално търсене (HLOOKUP). Тези функции се използват за превръщането на една или друга стойност в нещо, което е разбираемо. Например, можете да използвате VLOOKUP, за да вземете номер на част и да върнете описанието на елемента.
За да разследваме това, нека се върнем в електронната таблица "Решение Maker" в Част 4, където Джейн се опитва да реши какво да облече в училище. Вече не се интересува от това, което носи, тъй като тя е привлякла ново гадже, така че сега ще носи случайни облекла и обувки.
В електронната таблица на Джейн изброява екипировки във вертикални колони и обувки, хоризонтални колони.
Тя отваря електронната таблица, а функцията RANDBETWEEN (1,3) генерира число между или равно на едно и три, което съответства на трите вида облекла, които тя може да носи.
Тя използва функцията RANDBETWEEN (1,5) за избор между пет вида обувки.
Тъй като Джейн не може да носи номер, трябва да го превърнем в име, затова използваме функциите за търсене.
Използваме функцията VLOOKUP, за да превърнем номера на екипажа в името на екипа. HLOOKUP се превежда от номера на обувката до различните видове обувки в реда.
Таблицата работи по този начин за екипировки:
Excel избира произволен номер от една до три, тъй като тя има три опции за оформление.
След това формулата превежда номера на текста с помощта на = VLOOKUP (B11, A2: B4,2), който използва произволен номер от B11, за да изглежда в диапазона A2: B4. Тогава дава резултата (С11) от данните, изброени във втората колона.
Използваме същата техника за подбор на обувки, освен в случая, когато използваме VOOKUP вместо HLOOKUP.
Почти всеки знае една формула от статистиката - средна - но има и друга статистика, която е важна за бизнеса: стандартно отклонение.
Например, много хора, които са отишли в колеж, са агонизирали над техния резултат SAT. Те биха искали да знаят как се класират в сравнение с другите ученици. Университетите искат да знаят и това, защото много университети, особено престижни, отхвърлят студентите с ниски резултати от SAT.
Как ще изберем или интерпретираме оценките за SAT? По-долу са показани SAT резултати за пет ученици, вариращи от 1870 до 2230.
Важните номера, които трябва да разберете, са:
Средно аритметично - Средната се нарича още "средна".
Стандартно отклонение (STD или σ) - Този номер показва колко широко разпространен е набор от номера. Ако стандартното отклонение е голямо, тогава числата са далеч една от друга и ако е нула, всички числа са еднакви. Може да се каже, че стандартното отклонение е средната разлика между средната стойност и наблюдаваната стойност, т.е. 1,998 и всеки SAT резултат. Обърнете внимание, че е обичайно да се съкращава стандартното отклонение, като се използва гръцкият символ "σ".
Процентил на процента - Когато студентът получи висок резултат, той може да се хвали, че е в топ 99 процента или нещо подобно. "Percentile rank" означава, че процентът на резултатите е по-малък от един конкретен резултат.
Стандартното отклонение и вероятност са тясно свързани. Може да се каже, че за всяко стандартно отклонение вероятността или вероятността този брой да е вътре в този брой стандартни отклонения е:
| 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's е практически нулев, защото 99,73% от резултатите са в рамките на 3 STD's.
Сега нека отново да разгледаме електронната таблица и да обясним как тя работи.
Сега обясняваме формулите:
= Средна (В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 процента от резултатите са под резултата на Уокър.
Поставянето на резултатите в графика улеснява разбирането на резултатите, плюс това, че можете да ги покажете в презентация, за да направите по-ясно изказването си.
Студентите се намират на хоризонталната ос и техните резултати са показани като син графичен график на скала (вертикална ос) от 1600 до 2300.
Процентното класиране е дясната вертикална ос от 0 до 90% и е представена от сивата линия.
Създаването на диаграма е тема за себе си, но ние ще обясним накратко как е създадена горната таблица.
Първо, изберете диапазона от клетки, които да бъдат в диаграмата. В този случай от А2 до С6, защото искаме номерата, както и имената на учениците.
От менюто "Вмъкване" изберете "Графики" -> "Препоръчителни диаграми":
Компютърът препоръчва диаграма "Клъстерна колона, вторична ос". Частта "Вторична ос" означава, че тя черпи две вертикални оси. В този случай тази диаграма е тази, която искаме. Не е нужно да правим нищо друго.
Можете да използвате графиката около нея и да я размерите отново, докато я имате като размера и желаната позиция. След като сте доволни, можете да запазите диаграмата в електронната таблица.
Ако кликнете с десния бутон на мишката върху графиката, а след това - "Select Data", тя ви показва какви данни сте избрали за диапазона.
Функцията "Препоръчителни графики" обикновено ви предпазва от необходимостта да се справяте с такива сложни детайли, като да определите какви данни да включват, как да зададете етикети и как да зададете лявата и дясната вертикална оси.
В диалоговия прозорец "Избор на източник на данни" кликнете върху "резултат" под "Записи за легенда (серии)" и натиснете "Редактиране" и го променете, за да кажете "Резултат".
След това променете серия 2 ("персентил") до "Процентил".
Върнете се в диаграмата си и кликнете върху "Наименование на графиката" и я променете на "SAT резултати". Сега имаме пълна диаграма. Той има две хоризонтални оси: една за SAT резултат (син) и една за кумулативен процент (оранжево).
Транспортният проблем е класически пример за тип математика, наречен "линейно програмиране". Това ви позволява да увеличите или минимизирате стойност, която е предмет на определени ограничения. Той има много приложения към широк спектър от бизнес проблеми, така че е полезно да научите как работи.
Преди да започнем с този пример, трябва да активираме "Excel Solver".
Изберете "Файл" -> "Опции" -> "Добавки". В долната част на опциите на добавките кликнете върху бутона "Отиди" до "Управление: Добавки в Excel".
В резултат от менюто кликнете върху квадратчето за отметка, за да активирате "Добавка за решаване" и кликнете върху "OK".
Да предположим, че изпращаме iPads и се опитваме да запълним нашите дистрибуторски центрове, като използваме възможно най-ниските транспортни разходи. Имаме споразумение с компания за камиони и авиокомпании да превозваме iPads от Шанхай, Пекин и Хонг Конг до центровете за разпространение, показани по-долу.
Цената за доставка на всеки iPad е разстоянието от фабриката до дистрибуторския център до завода, разделен на 20 000 километра. Например, това е 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 правилно, двете матрици - разходи и доставка - трябва да са с еднакъв размер. Можете да заобиколите това ограничение, като направите допълнителни разходи и колони за доставка и редове с нулева стойност, така че масивите да са с еднакъв размер и да нямат въздействие върху общите разходи.
Ако всичко, което трябваше да направим, беше да умножим "разходите" на матриците, "пъти", които не биха били твърде сложни, но трябва да се справим и с ограничения там.
Трябва да изпратим това, което всеки център за дистрибуция изисква. Ние поставяме тази константа в решаването така: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Това означава сумата от доставеното, т.е. сумите в клетките $ B $ 12: $ E $ 12, трябва да бъдат по-големи или равни на това, което всеки център за дистрибуция изисква ($ B $ 13: $ E $ 13).
Ние не можем да превозваме повече, отколкото произвеждаме. Написваме тези ограничения по следния начин: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Сложете по друг начин, това, което изпращаме от всяко предприятие $ F $ 9: $ F $ 11 не може да надвишава (трябва да бъде по-малко или равно на) капацитета на всяко предприятие: $ G $ 9: $ G $ 11.
Сега отидете в менюто "Данни" и натиснете бутона "Solver". Ако бутонът "Solver" не е там, трябва да активирате добавката Solver.
Въведете двете ограничения, описани по-рано, и изберете диапазона "Доставки", който е диапазонът от номера, които искаме да изчислим от Excel. Също така изберете алгоритъма по подразбиране "Simplex LP" и посочете, че искаме да "минимизираме" клетката B15 ("общи транспортни разходи"), където се казва "Set Objective".
Натиснете "Решаване" и Excel записва резултатите в електронната таблица, което искаме. Можете също така да го запазите, за да можете да играете с други сценарии.
Ако компютърът твърди, че не може да намери решение, тогава сте направили нещо, което не е логично, например, може да сте поискали повече iPads, отколкото може да произвеждат растенията.
Тук Excel казва, че е намерил решение. Натиснете "OK", за да запазите решението и да се върнете към електронната таблица.
Как фирмата решава дали да инвестира в нов проект? Ако "нетната настояща стойност" (ННС) е положителна, те ще инвестират в нея. Това е стандартен подход, възприет от повечето финансови анализатори.
Да предположим например, че минната компания Codelco иска да разшири медната мина Andinas. Стандартният подход за определяне дали да се придвижи напред с проект е да се изчисли нетната настояща стойност. Ако NPV е по-голяма от нула, тогава проектът ще бъде печеливш, като се отчитат два входа (1) време и (2) цената на капитала.
На обикновен английски, цената на капитала означава колко ще спечелят тези пари, ако то просто ги остави в банката. Използвате цената на капитала за отстъпка на паричните стойности до сегашната стойност, с други думи $ 100 за пет години може да е 80 долара днес.
През първата година 45 милиона долара са заделени като капитал за финансиране на проекта. Счетоводителите са определили, че тяхната цена на капитала е шест процента.
При започването на добива, паричните средства започват, тъй като компанията открива и продава медта, която произвеждат. Очевидно е, че колкото повече минават, толкова повече пари правят, а прогнозите показват, че паричният им поток се увеличава, докато достигне 9 милиона долара годишно.
След 13 години NPV е $ 3,945,074 USD, така че проектът ще бъде печеливш. Според финансовите анализатори "периодът на изплащане" е 13 години.
"Обикновена таблица" е основно доклад. Ние ги наричаме "осеви" таблици, защото лесно можете да ги превключвате на един вид отчет в друг, без да се налага да правите цял нов отчет. Така че те шарнирен болт на място. Нека да покажем един основен пример, който преподава основните понятия.
Търговците са много конкурентни (това е част от това, че са продавачи), така че те естествено искат да знаят как се сравняват помежду си в края на тримесечието и края на годината, плюс колко ще са комисионните им.
Да предположим, че имаме трима продажби - Карлос, Фред и Джули - всички продаващи нефт. Техните продажби в щатски долари на фискално тримесечие за 2014 г. са показани в електронната таблица по-долу.
За да генерираме тези отчети, ние създаваме осова таблица:
Изберете "Insert -> Pivot Table, тя е отляво на лентата с инструменти:
Изберете всички редове и колони (включително името на продавача), както е показано по-долу:
В дясната страна на електронната таблица се показва диалоговият прозорец на опорната таблица.
Ако кликнете върху всичките четири полета в диалоговия прозорец на осеви маси (квартал, година, продажби и продавач), 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!
