УЧИЛИЩНА НАВИГАЦИЯ
- Защо се нуждаете от формули и функции?
- Дефиниране и създаване на формула
- Относителна и абсолютна клетка и форматиране
- Полезни функции, които трябва да знаете
- Търсене, графики, статистически данни и таблици за завъртане
Тези функции са важни за бизнеса, студентите и тези, които просто искат да научат повече.
VLOOKUP и HLOOKUP
Ето пример за илюстриране на функциите за вертикално търсене (VLOOKUP) и хоризонтално търсене (HLOOKUP). Тези функции се използват за превръщането на една или друга стойност в нещо, което е разбираемо. Например, можете да използвате VLOOKUP, за да вземете номер на част и да върнете описанието на елемента.
За да разследваме това, нека се върнем в електронната таблица "Решение Maker" в Част 4, където Джейн се опитва да реши какво да облече в училище. Вече не се интересува от това, което носи, тъй като тя е привлякла ново гадже, така че сега ще носи случайни облекла и обувки.
В електронната таблица на Джейн изброява екипировки във вертикални колони и обувки, хоризонтални колони.
Тя използва функцията RANDBETWEEN (1,5) за избор между пет вида обувки.
Тъй като Джейн не може да носи номер, трябва да го превърнем в име, затова използваме функциите за търсене.
Използваме функцията VLOOKUP, за да превърнем номера на екипажа в името на екипа. HLOOKUP се превежда от номера на обувката до различните видове обувки в реда.
Таблицата работи по този начин за екипировки:
След това формулата превежда номера на текста с помощта на = 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)
= STDEV.P (В2: В6)
Стандартното отклонение в диапазона B2: B6. ".P" означава STDEV.P се използва за всички резултати, т.е. за цялото население, а не само за подмножество.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, В2)
Графики на резултатите
Поставянето на резултатите в графика улеснява разбирането на резултатите, плюс това, че можете да ги покажете в презентация, за да направите по-ясно изказването си.
Процентното класиране е дясната вертикална ос от 0 до 90% и е представена от сивата линия.
Как да създадете графика
Създаването на диаграма е тема за себе си, но ние ще обясним накратко как е създадена горната таблица.
Първо, изберете диапазона от клетки, които да бъдат в диаграмата. В този случай от А2 до С6, защото искаме номерата, както и имената на учениците.
Функцията "Препоръчителни графики" обикновено ви предпазва от необходимостта да се справяте с такива сложни детайли, като да определите какви данни да включват, как да зададете етикети и как да зададете лявата и дясната вертикална оси.
В диалоговия прозорец "Избор на източник на данни" кликнете върху "резултат" под "Записи за легенда (серии)" и натиснете "Редактиране" и го променете, за да кажете "Резултат".
Пример: Транспортният проблем
Транспортният проблем е класически пример за тип математика, наречен "линейно програмиране". Това ви позволява да увеличите или минимизирате стойност, която е предмет на определени ограничения. Той има много приложения към широк спектър от бизнес проблеми, така че е полезно да научите как работи.
Преди да започнем с този пример, трябва да активираме "Excel Solver".
Активиране на добавката за Solver
Изберете "Файл" -> "Опции" -> "Добавки". В долната част на опциите на добавките кликнете върху бутона "Отиди" до "Управление: Добавки в Excel".
Пример: Изчислете най-ниските разходи за доставка в iPad
Да предположим, че изпращаме iPads и се опитваме да запълним нашите дистрибуторски центрове, като използваме възможно най-ниските транспортни разходи. Имаме споразумение с компания за камиони и авиокомпании да превозваме iPads от Шанхай, Пекин и Хонг Конг до центровете за разпространение, показани по-долу.
Цената за доставка на всеки iPad е разстоянието от фабриката до дистрибуторския център до завода, разделен на 20 000 километра. Например, това е 8,024 км от Шанхай до Мелбърн, което е 8,024 / 20,000 или $.40 на iPad.
Както можете да си представите, разбера, че това може да е много трудно без някаква формула и инструмент. В този случай трябва да изпратим общо 462 000 (F12) общо iPads. Растенията имат ограничен капацитет от 500,250 (G12) единици.
Използване на Solver
Ако всичко, което трябваше да направим, беше да умножим "разходите" на матриците, "пъти", които не биха били твърде сложни, но трябва да се справим и с ограничения там.
Трябва да изпратим това, което всеки център за дистрибуция изисква. Ние поставяме тази константа в решаването така: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Това означава сумата от доставеното, т.е. сумите в клетките $ B $ 12: $ E $ 12, трябва да бъдат по-големи или равни на това, което всеки център за дистрибуция изисква ($ B $ 13: $ E $ 13).
Въведете двете ограничения, описани по-рано, и изберете диапазона "Доставки", който е диапазонът от номера, които искаме да изчислим от Excel. Също така изберете алгоритъма по подразбиране "Simplex LP" и посочете, че искаме да "минимизираме" клетката B15 ("общи транспортни разходи"), където се казва "Set Objective".
Ако компютърът твърди, че не може да намери решение, тогава сте направили нещо, което не е логично, например, може да сте поискали повече iPads, отколкото може да произвеждат растенията.
Тук Excel казва, че е намерил решение. Натиснете "OK", за да запазите решението и да се върнете към електронната таблица.
Пример: Нетна настояща стойност
Как фирмата решава дали да инвестира в нов проект? Ако "нетната настояща стойност" (ННС) е положителна, те ще инвестират в нея. Това е стандартен подход, възприет от повечето финансови анализатори.
Да предположим например, че минната компания Codelco иска да разшири медната мина Andinas. Стандартният подход за определяне дали да се придвижи напред с проект е да се изчисли нетната настояща стойност. Ако NPV е по-голяма от нула, тогава проектът ще бъде печеливш, като се отчитат два входа (1) време и (2) цената на капитала.
На обикновен английски, цената на капитала означава колко ще спечелят тези пари, ако то просто ги остави в банката. Използвате цената на капитала за отстъпка на паричните стойности до сегашната стойност, с други думи $ 100 за пет години може да е 80 долара днес.
През първата година 45 милиона долара са заделени като капитал за финансиране на проекта. Счетоводителите са определили, че тяхната цена на капитала е шест процента.
След 13 години NPV е $ 3,945,074 USD, така че проектът ще бъде печеливш. Според финансовите анализатори "периодът на изплащане" е 13 години.
Създаване на обобщаваща таблица
"Обикновена таблица" е основно доклад. Ние ги наричаме "осеви" таблици, защото лесно можете да ги превключвате на един вид отчет в друг, без да се налага да правите цял нов отчет. Така че те шарнирен болт на място. Нека да покажем един основен пример, който преподава основните понятия.
Пример: Отчети за продажбите
Търговците са много конкурентни (това е част от това, че са продавачи), така че те естествено искат да знаят как се сравняват помежду си в края на тримесечието и края на годината, плюс колко ще са комисионните им.
Да предположим, че имаме трима продажби - Карлос, Фред и Джули - всички продаващи нефт. Техните продажби в щатски долари на фискално тримесечие за 2014 г. са показани в електронната таблица по-долу.
Изберете "Insert -> Pivot Table, тя е отляво на лентата с инструменти:
Ако кликнете върху всичките четири полета в диалоговия прозорец на осеви маси (квартал, година, продажби и продавач), Excel добавя отчет към електронната таблица, който няма смисъл, но защо?
Тук ни дава сумата от 2014 + 2014 + 2014 + 2014 = 24,168, което е глупост. Също така е дадена сумата от кварталите 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Нямаме нужда от тази информация, затова премахваме тези полета, за да ги премахнем от нашата конфигурация.
Пример: Продажби от продавача
Можете да редактирате "Сума от продажби", т.е. "Общо продажби", което е по-ясно. Също така можете да форматирате клетките като валута, точно както бихте форматирали всички други клетки. Първо кликнете върху "Сума от продажби" и изберете "Настройки на поле за стойност".
Пример: Продажби от Продавач и Квартал
Сега да добавим междинни сборове за всяко тримесечие. За да добавите междинни сбори, просто кликнете с левия бутон върху полето "Квартал" и го задръжте и преместете в секцията "Редове". Можете да видите резултата на екранната снимка по-долу:
заключение
Включвайки ви, ви показахме някои от характеристиките на формулите и функциите на Microsoft Excel, които можете да приложите на Microsoft Excel за вашите бизнес, академични или други нужди.
Както вече видяхте, Microsoft Excel е огромен продукт с толкова много функции, че повечето хора, дори и напредналите потребители, не знаят всички тях. Някои хора биха могли да кажат, че това усложнява; смятаме, че е по-изчерпателна.
Надяваме се, като ви представихме много примери от реалния живот, показахме не само функциите, които са налице в Microsoft Excel, но и те научиха нещо за статистиката, линейно програмиране, създаване на графики, използване на произволни номера и други идеи, които сега можете да приемате и да използвате във вашето училище или където работите.
Не забравяйте, че ако искате да се върнете отново и да вземете отново класа, можете да започнете с Урок 1!
