Keresések, diagramok, statisztikák és Pivot táblák
Miután áttekintettük az alapfunkciókat, a hivatkozásokat és a dátum- és időfunkciókat, most merülünk fel a Microsoft Excel néhány fejlettebb szolgáltatására. Bemutatjuk a pénzügyi, az értékesítési jelentések, a szállítási költségek és a statisztikák klasszikus problémáinak megoldására szolgáló módszereket.
SCHOOL NAVIGÁCIÓ- Miért van szüksége képletekre és funkciókra?
- Formula meghatározása és létrehozása
- Relatív és abszolút cella hivatkozás és formázás
- Hasznos funkciók, amelyeket ismerni kell
- Keresések, diagramok, statisztikák és Pivot táblák
Ezek a funkciók fontosak a vállalkozások, a diákok és azok számára, akik többet akarnak megtudni.
VLOOKUP és HLOOKUP
Itt van egy példa a függőleges keresési (VLOOKUP) és a vízszintes keresési (HLOOKUP) funkciók illusztrálására. Ezeket a funkciókat egy szám vagy más érték lefordítására használják. Például a VLOOKUP segítségével egy alkatrészszámot vehet fel és visszaadhatja az elem leírását.
Ennek megvizsgálásához térjünk vissza a 4. rész „döntéshozó” táblázatához, ahol Jane megpróbálja eldönteni, mit kell viselni az iskolába. Már nem érdekli, hogy mit visel, hiszen új barátjával leszállt, így most véletlenszerű ruhákat és cipőket visel..
Jane táblázatkezelőjében függőleges oszlopokban és cipőkben, vízszintes oszlopokban felsorolja a ruhákat.
Megnyitja a táblázatkezelőt, és a RANDBETWEEN (1,3) függvény egy-három számot generál, amely megfelel a háromféle viselet típusának..
A RANDBETWEEN (1,5) funkciót ötféle cipő közül választja.
Mivel a Jane nem tud számot viselni, hogy ezt egy névre alakítsuk át, ezért keresési funkciókat használunk.
A VLOOKUP funkciót használjuk a felszerelés számának lefordítására a felszerelés nevére. A HLOOKUP a cipőszámról a sorban lévő különböző típusú cipőkre fordítja.
A táblázatkezelő a következőképpen működik:
Az Excel egy véletlen számot választ ki egytől háromig, mivel három felszerelési lehetőséggel rendelkezik.
Ezután a képlet a szövegnek a = VLOOKUP (B11, A2: B4,2) segítségével fordítja le, amely véletlen számot használ a B11 értéktől az A2: B4 tartományban. Ezután megadja az eredményt (C11) a második oszlopban felsorolt adatokból.
Ugyanezt a technikát használjuk a cipők kiválasztására, kivéve ezúttal a VOOKUP helyett a HLOOKUP helyett.
Példa: Alapstatisztika
Szinte mindenki ismeri a statisztikából egy képletet - átlag -, de van egy másik statisztika is, amely fontos az üzleti életben: szórás.
Például, sok olyan személy, aki elment a főiskolára, megdöbbentette a SAT pontszámát. Lehet, hogy tudni akarják, hogyan rangsorolnak a többi diákhoz képest. Az egyetemek is ezt szeretnék tudni, mert sok egyetem, különösen a tekintélyes egyetemek, alacsony SAT-pontszámú diákokat fordítanak le.
Szóval hogyan, vagy egyetem, mérni és értelmezni SAT pontszámok? Az alábbiakban a SAT pontszámok öt diákra vonatkoznak, 1,870 és 2,230 között.
A megértendő fontos számok a következők:
Átlagos - Az átlagot „átlagnak” is nevezik.
Standard eltérés (STD vagy σ) - Ez a szám azt mutatja, hogy a számok mennyire szétszórtak. Ha a szórás nagy, akkor a számok messze vannak egymástól, és ha nulla, akkor az összes szám azonos. Azt mondhatjuk, hogy a szórás az átlagérték és a megfigyelt érték közti különbség, azaz 1,998 és minden SAT pontszám. Kérjük, vegye figyelembe, hogy a görög szimbólum sigma „σ” használatával gyakori a standard szórás rövidítése.
Százalékos rang - Amikor egy hallgató magas pontszámot kap, akkor a legmagasabb 99 százalékpontban, vagy ilyesmiben. A „százalékos rang” azt jelenti, hogy a pontszámok aránya alacsonyabb, mint egy adott pontszám.
A standard eltérés és a valószínűség szorosan összefügg. Azt mondhatjuk, hogy az egyes szórások esetében az a valószínűség vagy valószínűség, hogy ez a szám a standard eltérések számán belül van:
| STD | A pontszámok százalékos aránya | SAT pontszámok tartománya |
| 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 |
Mint látható, gyakorlatilag nulla az esély, hogy a SAT-pontszám 3 STD-n kívül esik, mivel a pontszámok 99,73 százaléka 3 STD-n belül van.
Most nézzük meg újra a táblázatot, és magyarázzuk el, hogyan működik.
Most megmagyarázzuk a képleteket:
= ÁTLAG (B2: B6)
A B2: B6 tartományon belüli összes pontszám átlaga. Pontosabban, az összes pontszám összege osztva a tesztet elfogadó személyek számával.
= STDEV.P (B2: B6)
A szórás a B2: B6 tartományban. A ".P" azt jelenti, hogy a STDEV.P-t az összes pontszámon, azaz a teljes populáción, és nem csak egy részhalmazon használjuk.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Ez kiszámítja a kumulatív százalékot a B2: B6 tartományban a SAT pontszám alapján, ebben az esetben B2. Például a pontszámok 83 százaléka a Walker pontszámánál van.
Az eredmények grafikus ábrázolása
Az eredmények grafikonba helyezése megkönnyíti az eredmények megértését, és azt is bemutathatja egy prezentációban, hogy pontossá tegye a pontot.
A diákok a vízszintes tengelyen helyezkednek el, és SAT-pontszámuk kék oszlopdiagramként jelenik meg egy skálán (függőleges tengely) 1 600 és 2 300 között.
A százalékos rangsor a jobb oldali függőleges tengely 0 és 90 százalék között van, és a szürke vonal képviseli.
Diagram létrehozása
A diagram létrehozása önmagában egy téma, de röviden elmagyarázzuk, hogyan jött létre a fenti táblázat.
Először válassza ki a diagramban található cellák tartományát. Ebben az esetben A2-től C6-ig, mert a számokat és a hallgató nevét is szeretnénk.
A „Beszúrás” menüből válassza a „Diagramok” -> „Ajánlott táblázatok” lehetőséget:
A számítógép „fürtözött oszlop, másodlagos tengely” táblázatot javasol. A „Másodlagos tengely” rész azt jelenti, hogy két függőleges tengelyt rajzol. Ebben az esetben ez a diagram az, amit akarunk. Nem kell mást tennünk.
Használhatja a diagram mozgatását és méretét, amíg meg nem jelenik a kívánt méretben és pozícióban. Ha elégedett, akkor elmentheti a táblázatot a táblázatba.
Ha jobb egérgombbal rákattint a diagramra, akkor az „Adatok kiválasztása”, azt mutatja meg, hogy milyen adatokat választ ki a tartományhoz.
Az „Ajánlott táblázatok” funkció rendszerint kihagyja magát attól, hogy olyan bonyolult részletekkel kell foglalkoznia, hogy meghatározza, hogy milyen adatokat kell beilleszteni, hogyan kell hozzárendelni a címkéket, és hogyan rendelje hozzá a bal és jobb függőleges tengelyeket.
Az „Adatforrás kiválasztása” párbeszédpanelen kattintson a „pontszám” pontra a „Legend bejegyzések (sorozat)” alatt, majd nyomja meg a „Szerkesztés” gombot, és változtassa meg, hogy azt mondja: „Pontszám”.
Ezután módosítsa a 2. sorozatot („százalék”) „Percentile” -re.
Térjen vissza a diagramra, és kattintson a „Chart Title” -re, és változtassa meg a „SAT Scores” -ra. Most már van egy teljes diagramunk. Két vízszintes tengelye van: egy SAT pontra (kék) és egy kumulatív százalékra (narancs).
Példa: a közlekedési probléma
A közlekedési probléma egy klasszikus példa a „lineáris programozás” -nak nevezett matematika típusára. Ez lehetővé teszi, hogy maximalizálja vagy minimálisra csökkentsék egy bizonyos korlátozás alá eső értéket. Számos alkalmazással rendelkezik az üzleti problémák széles skálájához, ezért hasznos megtanulni, hogyan működik.
Mielőtt elkezdenénk ezzel a példával, engedélyeznünk kell az „Excel Solver” -t.
Solver-bővítmény engedélyezése
Válassza a „File” -> „Options” -> „Add-ins” menüpontot. A bővítmények opcióinak alján kattintson az „Excel” bővítmények kezelése melletti „Go” gombra.
A kapott menüben kattintson a jelölőnégyzetre a "Solver Add-in" engedélyezéséhez, majd kattintson az "OK" gombra.
Példa: A legalacsonyabb iPad szállítási költség kiszámítása
Tegyük fel, hogy iPad-eket szállítunk, és a lehető legkisebb szállítási költségekkel próbáljuk kitölteni terjesztési központjainkat. Megállapodást kötöttünk egy fuvarozó és légitársasággal, hogy szállítson iPad-eket Sanghajból, Pekingből és Hong Kongból az alábbi disztribúciós központokba.
Az egyes iPad-ek kiszállításának ára a gyártól az elosztóközpontig terjedő távolság a növény elosztása 20 000 kilométerrel. Például 8244 km-re van Sanghajtól Melbourne-ig, ami 8,024 / 20,000 vagy $ 0,40 iPadonként.
A kérdés az, hogy hogyan szállítjuk ezeket a iPad-eket ebből a három üzemből ezekre a négy célállomásra a lehető legalacsonyabb áron?
Amint el tudod képzelni, nagyon nehéz lenne megfogalmazni valamilyen képlet és eszköz nélkül. Ebben az esetben 462 000 (F12) teljes iPad-t kell szállítanunk. A növények korlátozott kapacitása 500.250 (G12) egység.
A táblázatban, hogy lássuk, hogyan működik, az 1-et a B10 cellába írta, ami azt jelenti, hogy 1 iPad-et akarunk szállítani Sanghajból Melbourne-be. Mivel az útvonal mentén a szállítási költségek iPadon 0,40 dollár, a teljes költség (B17) 0,40 dollár.
A számot a = SUMPRODUCT (költség, szállított) „költség” függvény segítségével számítottuk ki a B3: E5 tartományok között.
És a „szállított” a B9: E11 tartomány:
A SUMPRODUCT a „költségeket” szorozza a „szállított” tartományba (B14). Ezt „mátrix szorzásnak” nevezzük.
Ahhoz, hogy a SUMPRODUCT megfelelően működjön, a két mátrixnak - a költségeknek és a szállítottnak - azonos méretűnek kell lennie. A korlátozást úgy érheti el, hogy többletköltségeket és nulla értékű oszlopokat és sorokat szállít, hogy a tömbök azonos méretűek legyenek, és nincs hatással a teljes költségre..
A Solver használata
Ha mindössze annyit kellett tennünk, hogy a „költségeket” idéző mátrixokat „szállították”, akkor ez nem lenne túl bonyolult, de ott kell kezelnünk a megszorításokat is.
El kell szállítanunk azt, amit minden elosztóközpont igényel. Ezt a konstansot úgy helyeztük el, mint ez: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Ez azt jelenti, hogy a szállított összegek összege, azaz a $ B $ 12: $ E $ 12 cellákban lévő összes összegnek nagyobbnak vagy egyenlőnek kell lennie azzal, amit az egyes terjesztési központok igényelnek ($ B $ 13: $ E $ 13).
Nem tudunk többet szállítani, mint amit gyártunk. Ezt a korlátozást írjuk: $ 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.
Most lépjen az „Adat” menübe, majd nyomja meg a „Solver” gombot. Ha a „Solver” gomb nincs ott, engedélyeznie kell a Solver bővítményt.
Írja be a korábban részletezett két korlátozást, és válassza ki a „Szállítmányok” tartományt, amely a számok tartománya, amelyet az Excel kiszámít. Válasszuk ki az „Simplex LP” alapértelmezett algoritmust is, és jelezzük, hogy a „B15” cellát („teljes szállítási költség”) szeretnénk „minimalizálni”, ahol azt mondja, hogy „Állítsa be a célt”.
Nyomja meg a „Solve” gombot, és az Excel az eredményeket elmenti a táblázatba, ami azt akarjuk. Ezt is elmentheti, így más forgatókönyvekkel is játszhat.
Ha a számítógép azt mondja, hogy nem talál megoldást, akkor valamit nem logikusnak tettél, például, hogy több iPad-et kértél, mint a növények..
Itt az Excel azt mondja, hogy megoldást talált. Nyomja meg az „OK” gombot a megoldás megtartásához és a táblázatba való visszatéréshez.
Példa: Nettó jelenérték
Hogyan dönti el a vállalat, hogy befektessen-e egy új projektbe? Ha a „nettó jelenérték” (NPV) pozitív, akkor befektetni fognak. Ez a legtöbb pénzügyi elemző szokásos megközelítése.
Tegyük fel például, hogy a Codelco bányavállalata az Andinas rézbányáját szeretné bővíteni. A szabványos megközelítés annak meghatározására, hogy egy projektet továbblép-e, a nettó jelenérték kiszámítása. Ha az NPV nagyobb, mint nulla, akkor a projekt nyereséges lesz, ha két bemenet (1) idő és (2) a tőke költsége van.
Egyszerűen angolul a tőke költsége azt jelenti, hogy mennyi pénzt keresne, ha éppen elhagynák a bankban. A tőkeköltséget a készpénzértékek diszkontálására használjuk a jelenlegi értékre, vagyis 100 dollár öt év alatt 80 dollár lehet.
Az első évben 45 millió dollárt helyeztek tőkébe a projekt finanszírozására. A könyvelők megállapítják, hogy a tőkeköltségük hat százalék.
A bányászat kezdetekor a készpénz elkezdődik, amikor a vállalat megkeresi és értékesíti a gyártott rézet. Nyilvánvaló, hogy minél többet keresnek, annál több pénzt csinálnak, és előrejelzésük azt mutatja, hogy a pénzáramuk növekszik, amíg el nem éri az évi 9 millió dollárt.
13 év után az NPV $ 3,945,074 USD, így a projekt nyereséges lesz. A pénzügyi elemzők szerint a „visszafizetési időszak” 13 év.
Pivot táblázat létrehozása
A „pivot tábla” alapvetően egy jelentés. Pivot tábláknak nevezzük őket, mert könnyen át tudod váltani az egyik típusú jelentést a másikra anélkül, hogy teljes új jelentést kellene készíteni. Szóval ők tengely a helyén. Mutassunk be egy alapvető példát, amely az alapfogalmakat tanítja.
Példa: Értékesítési jelentések
Az értékesítési munkatársak nagyon versenyképesek (ez része egy értékesítőnek), így természetesen szeretnék tudni, hogyan árulnak egymás ellen a negyedév végén és az év végén, és hogy mennyi jutalékuk lesz.
Tegyük fel, hogy három értékesítőnk van - Carlos, Fred és Julie - minden kőolajat értékesítenek. Értékesítésük dolláronként a pénzügyi évenként a 2014-es évre az alábbi táblázatban látható.
A jelentések létrehozásához egy pivot táblát hozunk létre:
Válassza az „Insert -> Pivot Table” pontot, amely az eszköztár bal oldalán található:
Válassza ki az összes sort és oszlopot (beleértve az eladó nevét) az alábbiak szerint:
A pivot tábla párbeszédpanel megjelenik a táblázat jobb oldalán.
Ha az összes négy mezőt a pivot tábla párbeszédablakban (Negyed, Év, Értékesítés és Értékesítő) kattintjuk, az Excel jelentést ad hozzá a táblázatkezelőhöz, amely nincs értelme, de miért?
Mint látható, minden négy mezőt választottunk a jelentéshez. Az Excel alapértelmezett viselkedése a sorok csoportosítása mezőkkel, majd az összes többi sor összege.
Itt adjuk meg a 2014 + 2014 + 2014 + 2014 = 24,168 év összegét, ami értelmetlen. Adott az 1 + 2 + 3 + 4 = 10 * 3 = 3 0 negyedévek összege is. Nincs szükségünk erre az információra, ezért töröljük ezeket a mezőket, hogy eltávolítsuk őket a pivot táblázatunkból.
Az „Értékesítés összege” (teljes értékesítés) azonban releváns, ezért ezt meg fogjuk oldani.
Példa: Értékesítés értékesítése
Szerkesztheted az „Értékesítés összegét”, ami a „Teljes értékesítés” kifejezésre vonatkozik, ami világosabb. Szintén formázhatja a cellákat valutaként, mint bármilyen más cellát. Először kattintson az „Értékesítés összege” elemre, és válassza az „Érték mező beállításai” lehetőséget.
A kapott párbeszédablakban a nevet „Total Sales” -re változtatjuk, majd kattintsunk a „Number Format” -ra, és váltsuk át a „Currency” -ra.
Ezután láthatja a kézműves munkát a pivot táblázatban:
Példa: Értékesítési és negyedévi értékesítés
Most minden negyedévben adjunk hozzá egy részletet. Részösszegek hozzáadásához kattintson a bal egérgombbal a „Negyed” mezőre, és tartsa lenyomva a „sorok” szekciót. Az alábbi képen látható az eredmény:
Miközben mi vagyunk, távolítsuk el a „Negyedik összeg” értékeket. Egyszerűen kattintson a nyílra, majd kattintson a „Mező eltávolítása” gombra. A képernyőképen most már láthatjuk, hogy hozzáadtuk a „Negyedik” sorokat, amelyek negyedévenként lebontják az eladók értékesítését.
Ezekkel a készségekkel szem előtt tartva frissítheti a saját adataiból álló pivot táblákat!
Következtetés
A csomagolás során megmutattuk néhány Microsoft Excel képletének és funkciójának néhány funkcióját, amelyeket a Microsoft Excel alkalmazással alkalmazhat üzleti, tudományos vagy egyéb igényekre.
Amint láttuk, a Microsoft Excel hatalmas termék olyan sok olyan funkcióval, amit a legtöbb ember, még a fejlett felhasználók is, nem tudnak mindegyikükről. Vannak, akik azt mondják, hogy bonyolult; úgy véljük, átfogóbb.
Remélhetőleg számos valós példát bemutatva nemcsak a Microsoft Excelben elérhető funkciókat mutattuk be, hanem valamit a statisztikákról, a lineáris programozásról, a diagramok készítéséről, véletlenszámokról és más olyan ötletekről tanítottuk, amelyeket most elfogadhat és használja az iskolában vagy a munkahelyén.
Ne feledje, hogy ha vissza akarsz menni, és újra megtennéd az osztályt, akkor frissen kezdhetsz az 1. leckével!
