Прегледи, графикони, статистика и обртне табеле

Прегледавши основне функције, референце ћелија и функције датума и времена, сада улазимо у неке од напреднијих функција програма Мицрософт Екцел. Представљамо методе за решавање класичних проблема у финансијама, продајним извештајима, трошковима транспорта и статистици.

СЦХООЛ НАВИГАТИОН

1. Зашто су вам потребне формуле и функције?
2. Дефинисање и креирање формуле
3. Релативна и апсолутна референца ћелије и обликовање
4. Корисне функције које бисте требали знати
5. Прегледи, графикони, статистика и обртне табеле

Ове функције су важне за пословање, студенте и оне који желе само да науче више.

ВЛООКУП и ХЛООКУП

Ево примера за илустрацију функција вертикалног тражења (ВЛООКУП) и хоризонталног тражења (ХЛООКУП). Ове функције се користе за превођење броја или друге вриједности у нешто што је разумљиво. На пример, можете да користите ВЛООКУП да бисте узели број дела и вратили опис ставке.

Да бисмо то истражили, вратимо се нашој „табели одлука“ у четвртом делу, где Јане покушава да одлучи шта да обуче у школу. Више није заинтересована за оно што носи, јер је добила новог дечка, тако да ће сада носити наочаре и ципеле.

У Јанеиној табеларној табели, она наводи одела у вертикалним колонама и ципелама, хоризонталне колоне.

Она отвара табелу и функција РАНДБЕТВЕЕН (1,3) генерише број између или једнак једном и три који одговарају три врсте одела које може да носи.

Она користи функцију РАНДБЕТВЕЕН (1,5) да би изабрала између пет врста ципела.

Пошто Јане не може да носи број који нам је потребан да ово претворимо у име, користимо функције претраживања.

Користимо функцију ВЛООКУП за превођење бројева опреме на име. ХЛООКУП се преводи од броја ципела до различитих врста ципела у реду.

Ова табела ради овако за одела:

Екцел бира насумични број од један до три, пошто има три опције.

Затим формула преводи број у текст користећи = ВЛООКУП (Б11, А2: Б4,2) који користи случајни број од Б11 да гледа у опсегу А2: Б4. Затим даје резултат (Ц11) из података наведених у другој колони.

Користимо исту технику за бирање ципела, осим што овај пут користимо ВООКУП умјесто ХЛООКУП.

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

Скоро сви знају једну формулу из статистике - просјечну - али постоји још једна статистика која је важна за пословање: стандардна девијација.

На пример, многи који су отишли ​​на колеџ су се мучили због свог САТ резултата. Можда желе знати како се рангирају у односу на друге студенте. Универзитети то желе знати и зато што многи универзитети, посебно они престижни, одбијају студенте са ниским САТ бодовима.

Како бисмо ми, или универзитет, мјерили и интерпретирали САТ резултате? Испод су САТ резултати за пет студената у распону од 1.870 до 2.230.

Најважнији бројеви које треба разумети су:

Просек - Просек се такође назива "средња".

Стандардно одступање (СТД или σ) - Овај број показује колико је широко распрострањен низ бројева. Ако је стандардна девијација велика, онда су бројеви удаљени и ако је нула, сви бројеви су исти. Могло би се рећи да је стандардна девијација просечна разлика између просечне вредности и посматране вредности, тј. 1,998 и сваке САТ вредности. Имајте на уму да је уобичајено скраћивање стандардне девијације помоћу грчког симбола сигма “σ.”

Перцентиле Ранк - Када ученик добије високу оцјену, они се могу похвалити да су у врху 99 посто или нешто слично. “Перцентилни ранг” значи да је проценат бодова нижи од једног одређеног резултата.

Стандардна девијација и вероватноћа су уско повезане. Можете рећи да за сваку стандардну девијацију, вероватноћа или вероватноћа да је тај број унутар тог броја стандардних девијација је:

СТД	Проценат бодова	Распон САТ резултата
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

Као што видите, шанса да је било који САТ резултат изван 3 СТД-а је практично нула, јер је 99,73% резултата унутар 3 СТД-а.

Сада ћемо поново погледати табелу и објаснити како то ради.

Сада ћемо објаснити формуле:

= ПРОСЈЕК (Б2: Б6)

Просек свих резултата у распону Б2: Б6. Конкретно, збир свих резултата подијељен са бројем људи који су положили тест.

= СТДЕВ.П (Б2: Б6)

Стандардна девијација у опсегу Б2: Б6. "П" значи СТДЕВ.П се користи за све резултате, тј. За целу популацију, а не само за подскуп..

= ПЕРЦЕНТРАНК.ЕКСЦ ($ Б $ 2: $ Б $ 6, Б2)

Ово израчунава кумулативни проценат преко опсега Б2: Б6 на основу САТ резултата, у овом случају Б2. На пример, 83% резултата је испод Валкер-овог резултата.

Графички приказ резултата

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

Ученици су на хоризонталној оси, а њихови САТ резултати су приказани као плави бар граф на скали (вертикална оса) од 1.600 до 2.300.

Рангирање перцентила је десна вертикална оса од 0 до 90 процената и представљена је сивом линијом.

Како створити графикон

Креирање графикона је тема за себе, међутим, укратко ћемо објаснити како је направљен горњи графикон.

Прво изаберите опсег ћелија које ће се налазити у графикону. У овом случају А2 до Ц6 јер желимо бројеве као и имена ученика.

Из менија “Инсерт” изаберите “Цхартс” -> “Рецоммендед Цхартс”:

Рачунар препоручује графикон "Цлустеред-Цолумн, Сецондари Акис". Дио "секундарне осовине" значи да извлачи двије вертикалне оси. У овом случају, ова карта је она коју желимо. Не морамо да радимо ништа друго.

Можете користити померање графикона около и променити величину док га не добијете као величину и на позицији коју желите. Када будете задовољни можете да сачувате графикон у табели.

Ако десним тастером миша кликнете на графикон, а затим изаберете „Изабери податке“, он ће вам показати који су подаци изабрани за опсег.

Функција „Препоручене карте“ обично вас избацује из потребе да се бавите тако компликованим детаљима као што су одређивање података које треба укључити, како додијелити ознаке и како додијелити лијеве и десне вертикалне оси.

У дијалогу “Селецт Дата Соурце” изаберите “сцоре” под “Легенд Ентриес (Сериес)” и притисните “Едит” и промените га да бисте изговорили “Сцоре”.

Затим промените серију 2 (“перцентил”) у “Перцентиле”.

Вратите се на графикон и кликните на “Цхарт Титле” и промените га у “САТ Сцорес”. Сада имамо комплетан графикон. Има две хоризонталне осе: једну за САТ резултат (плаву) и једну за кумулативни проценат (наранџаста).

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

Проблем саобраћаја је класичан пример типа математике названог "линеарно програмирање". Ово вам омогућава да повећате или минимизирате вредност која подлеже одређеним ограничењима. Има много апликација за широк спектар пословних проблема, тако да је корисно сазнати како то ради.

Пре него што почнемо са овим примером морамо да омогућимо „Екцел Солвер“.

Омогући Солвер Адд-Ин

Изаберите “Филе” -> “Оптионс” -> “Адд-инс”. На дну опција додатака кликните на дугме „Иди“ поред ставке „Управљање: Екцел додаци“.

На резултирајућем менију, кликните на поље за потврду да бисте омогућили „Солвер Адд-ин“ и кликните на „ОК“.

Пример: Израчунајте најниже иПад трошкове транспорта

Претпоставимо да испоручујемо иПадс и покушавамо да попунимо наше дистрибутивне центре користећи најниже могуће трошкове транспорта. Имамо уговор са компанијом за транспорт и авио-компаније да испоручимо иПадс из Шангаја, Пекинга и Хонг Конга у дистрибутивне центре приказане у наставку.

Цена за испоруку сваког иПад-а је удаљеност од фабрике до дистрибутивног центра до постројења подељеног на 20.000 километара. На пример, то је 8.024 км од Шангаја до Мелбурна, што је 8.024 / 20.000 или $ .40 по иПад-у.

Питање је како ћемо испоручити све ове иПад-ове из ова три постројења на ове четири дестинације по најнижој могућој цијени?

Као што можете замислити, схватити да ово може бити врло тешко без неке формуле и алата. У овом случају морамо испоручити 462,000 (Ф12) укупних иПада. Постројења имају ограничен капацитет од 500.250 (Г12) јединица.

У табели, тако да можете да видите како ради, куцали смо 1 у ћелију Б10, што значи да желимо да испоручимо 1 иПад из Шангаја у Мелбоурне. Пошто су трошкови превоза дуж те руте $ 0.40 по иПад-у, укупни трошак (Б17) је $ 0.40.

Број је израчунат помоћу функције = СУМПРОДУЦТ (трошкови, испоручени) "трошкови" су распони Б3: Е5.

А "испоручен" је опсег Б9: Е11:

СУМПРОДУЦТ множи “трошкове” ​​пута распон “испоручен” (Б14). То се назива "множење матрица."

Да би СУМПРОДУЦТ радио исправно, две матрице - трошкови и испоруке - морају бити исте величине. Ово ограничење можете заобићи тако што ћете направити додатне трошкове и доставити колоне и редове са нултом вредношћу тако да поља буду исте величине и да нема утицаја на укупне трошкове.

Коришћење решивача

Ако је све што је требало да урадимо било да помножимо матрице "трошкова" пута "отпремљених" које не би биле превише компликоване, али морамо се носити и са ограничењима тамо.

Морамо да доставимо оно што сваки дистрибутивни центар захтева. Ту константу стављамо у решивач овако: $ Б $ 12: $ Е $ 12> = $ Б $ 13: $ Е $ 13. То значи да сума испорученог, тј. Укупних износа у ћелијама $ Б $ 12: $ Е $ 12, мора бити већа или једнака ономе што сваки дистрибутивни центар захтева ($ Б $ 13: $ Е $ 13).

Не можемо послати више него што производимо. Пишемо та ограничења овако: $ Ф $ 9: $ Ф $ 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.

Сада идите у мени “Дата” и притисните дугме “Солвер”. Ако дугме „Солвер“ не постоји, морате омогућити додатак Солвер.

Укуцајте два раније описана ограничења и изаберите опсег „Пошиљке“, који је опсег бројева које желимо да Екцел израчуна. Такође изаберите подразумевани алгоритам „Симплек ЛП“ и назначите да желимо да „минимизирамо“ ћелију Б15 („укупни трошкови испоруке“), где пише „Постави циљ“.

Притисните “Солве” и Екцел спрема резултате у табелу, што је оно што желимо. Можете и да сачувате ово тако да се можете играти са другим сценаријима.

Ако рачунар каже да не може да пронађе решење, онда сте урадили нешто што није логично, на пример, можда сте захтевали више иПада него што биљке могу произвести..

Овде Екцел каже да је нашао решење. Притисните “ОК” да задржите решење и вратите се у табелу.

Пример: нето садашња вредност

Како компанија одлучује да ли да инвестира у нови пројекат? Ако је „нето садашња вредност“ (НПВ) позитивна, они ће улагати у њу. Ово је стандардан приступ који је прихватио већина финансијских аналитичара.

На пример, претпоставимо да Цоделцо рударска компанија жели да прошири рудник бакра Андинас. Стандардни приступ за одређивање да ли да се крене даље са пројектом је да се израчуна нето садашња вредност. Ако је НПВ већи од нуле, онда ће пројект бити профитабилан с обзиром на два улаза (1) вријеме и (2) трошак капитала.

На обичном енглеском језику, трошак капитала значи колико би тај новац зарадио да су га оставили у банци. Трошкове капитала користите за дисконтовање готовинских вриједности на садашњу вриједност, другим ријечима, 100 долара за пет година може бити данас 80 долара.

У првој години, 45 милиона долара је издвојено као капитал за финансирање пројекта. Рачуновође су утврдиле да је њихов трошак капитала шест посто.

Када почну да рударују, новац почиње да долази када компанија пронађе и прода бакар који производе. Очигледно, што су више моји, то више новца зарађују, а њихова прогноза показује да се њихов новчани ток повећава док не достигне 9 милиона долара годишње.

Након 13 година, НПВ износи 3.945.074 УСД, тако да ће пројекат бити профитабилан. Према финансијским аналитичарима, период отплате је 13 година.

Креирање Пивот табеле

“Пивот табела” је у основи извештај. Ми их зовемо пивот табеле, јер их можете лако пребацити на једну врсту извештаја на другу без потребе за прављењем новог извештаја. Тако да пивот на месту. Покажимо основни примјер који подучава основне појмове.

Пример: Извештаји о продаји

Продавци су веома конкурентни (то је део продавца), тако да природно желе да знају како се слажу једни с другима на крају тромесечја и краја године, плус колико ће им бити провизија.

Претпоставимо да имамо три продавача - Царлоса, Фреда и Јулие - који продају нафту. Њихова продаја у доларима по фискалном кварталу за 2014. годину приказана је у табели испод.

Да бисмо генерисали ове извештаје, направили смо стожерну табелу:

Изаберите “Инсерт -> Пивот Табле, налази се на левој страни траке са алаткама:

Изаберите све редове и колоне (укључујући име продавца) као што је приказано у наставку:

Дијалог окретна табела појављује се на десној страни прорачунске табеле.

Ако кликнемо на сва четири поља у дијалогу окретне табеле (Четвртина, година, продаја и продавач), Екцел у табелу додаје извештај који нема смисла, али зашто?

Као што видите, одабрали смо сва четири поља за додавање у извјештај. Екцелово подразумевано понашање је груписање редова по текстуалним пољима, а затим збрајање свих осталих редова.

Овде нам даје суму 2014 + 2014 + 2014 + 2014 = 24,168, што је бесмислица. Такође је дала и суму квартала 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Ове информације не требамо, па поништавамо одабир ових поља како бисмо их уклонили из наше стожерне табеле..

"Збир продаје" (укупна продаја) је битан, па ћемо то поправити.

Пример: Продаја од стране продавца

Можете да измените “Сум оф Салес” да кажете “Тотал Салес”, што је јасније. Такође, можете форматирати ћелије као валуту као што бисте форматирали било које друге ћелије. Прво кликните на “Сум оф Салес” и одаберите “Валуе Фиелд Сеттингс”.

У дијалогу који је резултирао промјенимо назив у "Укупна продаја", затим кликнемо на "Бројчани формат" и промијенимо у "Валута".

Тада можете видети ручни рад у пивот табели:

Пример: Продаја по продавцу и кварталу

Сада додајте субтотале за сваки квартал. Да бисте додали субтотале, кликните левим тастером миша на поље „Куартер“ и задржите га и превуците у одељак „редови“. Можете видети резултат на слици испод:

Док смо на томе, уклонимо вредности "Сум оф Куартер". Једноставно кликните на стрелицу и кликните на дугме „Уклони поље“. На снимку екрана сада можете да видите да смо додали редове „Четвртина“, који разлажу продају сваког продавца за четвртину.

Имајући у виду све ове вештине, сада можете креирати пивот табеле из сопствених података!

Закључак

Завршили смо, показали смо вам неке од карактеристика формула и функција програма Мицрософт Екцел које можете да примените у Мицрософт Екцелу на ваше пословне, академске или друге потребе.

Као што сте видели, Мицрософт Екцел је огроман производ са толико могућности да већина људи, чак и напредни корисници, не знају све. Неки људи би могли рећи да је то комплицирано; сматрамо да је свеобухватнији.

Надамо се да ћемо вам представити много примера из стварног живота, али смо показали не само функције које су доступне у Мицрософт Екцел-у, већ смо вас научиле нешто о статистици, линеарном програмирању, креирању дијаграма, користећи случајне бројеве и друге идеје које сада можете да усвојите и користити у вашој школи или тамо гдје радите.

Запамтите, ако желите да се вратите и поново узмете час, можете почети свеже са Лекцијом 1!