ISKOLAI NAVIGÁCIÓ
- Miért van szükség a képletekre és funkciókra?
- A képlet meghatározása és létrehozása
- Relatív és abszolút cella hivatkozás és formázás
- Hasznos funkciók Önnek tudnia kell
- Keresések, diagramok, statisztikák és pivot táblák
Ezek a funkciók fontosak az üzleti életben, a diákokban és azokban, akik csak többet akarnak tanulni.
VLOOKUP és HLOOKUP
Íme egy példa a függőleges keresés (VLOOKUP) és a vízszintes keresés (HLOOKUP) függvények bemutatására. Ezeket a funkciókat egy szám vagy más érték lefordítására használják, ami érthető. Például a VLOOKUP használatával részszámot vehet fel és visszaadhatja az elemleírást.
Ennek vizsgálatához térjünk vissza a 4. rész "Döntés-készítő" táblázatába, ahol Jane próbálja eldönteni, hogy mit viseljen az iskolában. Már nem érdekli, amit visel, mivel új barátjával leszállt, ezért véletlenszerű ruhákat és cipőket visel.
Jane táblázatban felsorolja a függőleges oszlopokban és cipőkben lévő ruhákat, a vízszintes oszlopokat.
A RANDBETWEEN (1,5) funkciót ötféle cipő közül választja.
Mivel Jane nem viselhet számot, ezért ezt nevekké kell átalakítanunk, tehát keresési funkciókat használunk.
A VLOOKUP függvényt használjuk arra, hogy lefordítsuk a felszerelés számát a ruhák nevére. A HLOOKUP a cipő számától a cipő különböző típusaiig fordul elő.
A táblázat a ruhákhoz hasonlóan működik:
Ezután a képlet átszámítja a számot a következőre: = VLOOKUP (B11, A2: B4,2), amely véletlenszerűen számolja a B11 értékét az A2: B4 tartományban. Ezután adja meg az eredményt (C11) a második oszlopban felsorolt adatokból.
Ugyanazt a technikát alkalmazzuk cipő kivételezésére, kivéve ez alkalommal a HLOOKUP helyett VOOKUP alkalmazást.
Példa: Alapstatisztika
Szinte mindenki ismeri az egyik képletet a statisztikákból - átlag -, de van egy másik statisztika is, ami fontos az üzleti életben: a standard deviáció.
Például sok olyan személy, aki a főiskolára ment, aggodalommal töltötte el a SAT pontszámát. Lehet, hogy szeretnék tudni, hogy a többi diákhoz képest hogyan rangsorolnak. Az egyetemek ezt is szeretnék tudni, mert sok egyetem, különösen a tekintélyes egyetemek elutasítják az alacsony SAT pontszámokkal rendelkező diákokat.
Tehát hogyan vagyunk, vagy egy egyetemen mérjük és értelmezzük a SAT pontszámokat? Az alábbiakban a SAT pontszámok öt diákra terjednek, 1,870-2,230 között.
Átlagos - Az átlag is "átlag".
Standard deviáció (STD vagy σ) - Ez a szám azt mutatja meg, hogy a számok mennyire szétszóródtak. Ha a standard deviáció nagy, akkor a számok messze vannak egymástól, és ha nulla, akkor az összes szám megegyezik. Azt lehet mondani, hogy a szórás az átlagérték és a megfigyelt érték közötti átlagos különbség, azaz 1,998 és minden SAT pontszám. Felhívjuk a figyelmet arra, hogy a szó szerinti eltérést a görög "σ" szimbólummal rövidítjük.
Százalékos rang - Amikor egy diák kap magas pontszámot, akkor megdicsérhet, hogy a legjobb 99 percentilis vagy valami ilyesmi. A "százalékos rang" azt jelenti, hogy a pontszámok százalékos aránya alacsonyabb, mint egy adott pontszám.
A standard eltérés és valószínűség szorosan összefügg. Azt mondhatjuk, hogy minden egyes szórás esetében annak a valószínűsége vagy valószínűsége, hogy ez a szám a szóban forgó számú szóráson 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 |
Amint látja, az a valószínűség, hogy bármelyik SAT pontszám kívül esik 3 STD-ben, gyakorlatilag nulla, mert a pontszámok 99,73 százaléka 3 STD-ben van.
Most nézzük újra a táblázatot, és magyarázza el, hogyan működik.
= ÁTLAG (B2: B6)
= STDEV.P (B2: B6)
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Az eredmények ábrázolása
Az eredmények grafikonon történő megjelenítésével könnyebb megérteni az eredményeket, és egy prezentációban is megmutathatja annak pontosabb megfogalmazását.
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 jelzi.
Hogyan készítsünk egy diagramot?
A grafikon létrehozása önmagában egy téma, de röviden meg fogjuk magyarázni, hogy a fenti táblázat hogyan jött létre.
Először válassza ki a táblázatban szereplő cellák tartományát. Ebben az esetben A2-C6, mert szeretnénk a számokat, valamint a diák nevét.
A "Ajánlott grafikonok" funkció általában arra kényszeríti Önt, hogy olyan bonyolult részletekkel foglalkozzon, mint a címkék hozzárendelésére, a címkék hozzárendelésére és a bal és jobb oldali függőleges tengelyek hozzárendelésére.
Az "Adatforrás kiválasztása" párbeszédablakban kattintson a "Jegyzetek bejegyzései (sorozatok)" pontra, és nyomja meg a "Szerkesztés" gombot, majd módosítsa a "Pontszám" kifejezést.
Példa: A közlekedési probléma
A szállítási probléma egy klasszikus példa egy olyan matematika típusra, amelyet "lineáris programozásnak" neveznek. Ez lehetővé teszi, hogy maximalizáljuk vagy minimalizáljuk az értékeket, amelyek bizonyos korlátozásoknak vannak kitéve. Számos alkalmazáshoz számos üzleti probléma áll rendelkezésre, ezért hasznos megtudni, hogyan működik.
Mielőtt elkezdenénk ezzel a példával, engedélyeznünk kell az "Excel Solver" -t.
Engedélyezze a Solver bővítményt
Válassza a "File" -> "Options" -> "Add-ins" lehetőséget. A bővítmények beállításainak alján kattintson a "Kezelés: Excel bővítmények" melletti "Go" gombra.
Példa: Számítsa ki a legalacsonyabb iPad szállítási költségeket
Tegyük fel, hogy az iPad-eket szállítjuk, és megpróbáljuk kitölteni az elosztó központjainkat a lehető legalacsonyabb szállítási költségekkel. Megegyezést kötöttünk egy tehergépkocsi-szállítmányozóval és egy légitársasággal, hogy az alábbi iPad-okat szállítsák Sanghajból, Pekingből és Hongkongból az alább felsorolt terjesztési központokba.
Az egyes iPad-ekre vonatkozó ár a gyárból az elosztóközpontig az üzemig terjedő távolság 20 000 kilométerrel. Például 8,024 km-re van Sanghajból Melbourne-be, amely 8,024 / 20,000 vagy 40 dollár.
A kérdés az, hogy miként szállítjuk le ezeket a három növényt ezekre a négy célállomásra a lehető legalacsonyabb költségek mellett?
Ahogy el tudod képzelni, kitalálni, hogy ez nagyon nehéz lehet egy képlet és eszköz nélkül. Ebben az esetben 462 000 (F12) teljes iPadsot kell szállítanunk. A növények korlátozott kapacitása 500.250 (G12) egység.
A megoldó használata
Ha mindössze annyit kell tennünk, hogy megszorozzuk a mátrixok "költség" idejét "szállítják", ami nem lenne túl bonyolult, de ott is kénytelenek foglalkozni.
Meg kell szállítanunk, amit az egyes elosztóközpontok megkövetelnek. Ezt a konstansot a megoldóhoz hasonlóan tesszük: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Ez azt jelenti, hogy a szállítmány összege, vagyis a $ B $ 12: $ E $ 12 cellák összesített értéke nagyobb vagy egyenlő azzal, amit az egyes elosztóközpontok megkövetel ($ B $ 13: $ E $ 13).
Írja be a korábban részletezett két korlátot, és válassza ki a "Szállítmányok" tartományt, amely az Excel által kiszámolt számok tartománya. Vegye fel az "Simplex LP" alapértelmezett algoritmust, és jelezze, hogy "minimalizálni" akarjuk a B15 cellát ("teljes szállítási költség"), ahol azt mondja: "Célkitűzés".
Ha a számítógép azt mondja, hogy nem talál megoldást, akkor valami olyat csináltál, ami nem logikus, például több iPadsot is kért, mint a növények.
Itt Excel azt mondja, hogy megtalálta a megoldást. 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önt egy cég egy új projektbe való beruházásról? Ha a "nettó jelenérték" (NPV) pozitív, befektetni fognak benne. Ez a szabványos megközelítés a legtöbb pénzügyi elemző számára.
Tegyük fel például, hogy a Codelco bányavállalat ki akarja terjeszteni az Andinas rézbányát. A projekt alapjául szolgáló általános megközelítés a nettó jelenérték kiszámítása. Ha az NPV nagyobb, mint nulla, akkor a projekt két input (1) idő és (2) tőkeköltség alapján nyereséges lesz.
A sima angol nyelvben a tőke költsége azt jelenti, hogy mennyi pénzt keresne, ha csak a bankban hagyta. A tőke költségét a készpénzértékek diszkontálására a jelenlegi értékre használja, más szóval $ 100 öt év alatt 80 dollár lehet.
Az első évben 45 millió dollárt különítenek el tőkeként a projekt finanszírozására. A könyvelők úgy határoznak, hogy a tőke költsége hat százalék.
13 év után az NPV 3 945 074 USD, így a projekt nyereséges lesz. Pénzügyi elemzők szerint a "visszafizetési időszak" 13 év.
Pivot Table létrehozása
A "pivot table" alapvetően egy jelentés. Mi pivot táblázatoknak nevezzük őket, mivel könnyen átválthat egy jelentéstípust a másikra anélkül, hogy egy egész új jelentést kellene készítenie. Szóval ők tengely a helyén. Mutassunk meg egy alapvető példát, amely az alapfogalmakat tanítja.
Példa: Értékesítési jelentések
Az értékesítési emberek nagyon versenyképesek (így részesei az értékesítőknek), így természetesen szeretnék tudni, hogy miként járulnak hozzá egymáshoz a negyedév végén és az év vége felé, és hogy mekkora jutalékok lesznek.
Tegyük fel, hogy van három értékesítési emberünk - Carlos, Fred és Julie - mindazok, akik kőolajat árulnak el. A 2014-es pénzügyi negyedévre vonatkozó dolláronkénti értékesítésüket az alábbi táblázatban mutatjuk be.
A jelentések létrehozásához egy pivot táblát hozunk létre:
Válassza a "Insert -> Pivot Table elemet, ez az eszköztár bal oldalán található:
Ha mind a négy mezőt a pivot táblázat párbeszédpaneljén (negyedév, év, értékesítés és értékesítő) négyszer kattintva adjuk hozzá a táblázathoz egy jelentést, amelynek nincs értelme, de miért?
Itt megadja nekünk a 2014 + 2014 + 2014 + 2014 = 24.168 év összegét, ami ostobaság. Szintén megadta az 1 + 2 + 3 + 4 = 10 * 3 = 3 0 negyedek összegét. Nem szükséges ez az információ, ezért töröljük ezeket a mezőket, hogy eltávolítsuk őket a forgóasztalunkból.
Példa: Salesman Sales
Szerkesztheti az "Értékesítési összeg" értéket, amely a "Teljes értékesítés" kifejezést jelenti, ami egyértelműbb. Továbbá formázhatja a cellákat pénznemként, mint bármely más cellát. Először kattintson az "Értékesítés összege" lehetőségre, és válassza az "Értékmezőbeállítások" lehetőséget.
Példa: Salesman és Quarter értékesítése
Most vegyünk fel részösszegeket minden negyedévre. A részösszegek hozzáadásához csak kattintson a "Negyed" mező bal egérgombbal, és tartsa lenyomva és húzza a "sorok" szakaszba. Az eredmény az alábbi képernyőképen látható:
Következtetés
Összefoglalva, bemutattuk Önnek a Microsoft Excel formuláinak és funkcióinak néhány jellemzőjét, amelyekkel a Microsoft Excel alkalmazható üzleti, tudományos vagy egyéb igényeire.
Mint láttuk, a Microsoft Excel olyan hatalmas termék, amely olyan sok funkciót tartalmaz, amelyet a legtöbb ember, még a fejlett felhasználók sem ismerik. Vannak, akik azt mondják, hogy ez bonyolult; úgy érzi, hogy átfogóbb.
Remélhetőleg számos valós példát bemutatva bemutattuk nemcsak a Microsoft Excel programban rendelkezésre álló funkciókat, de tanított neked valamit a statisztikákról, a lineáris programozásról, a grafikonok létrehozásáról, a véletlenszámok használatáról és egyéb olyan ötletekről, amelyeket most elfogadhat és használhatja az iskolában, vagy ahol dolgozik.
Ne felejtse el, hogy ha vissza szeretne menni, és újra elkezdi az órát, akkor az 1. lecke!
