Pārskatot pamatfunkcijas, šūnu atsauces un datuma un laika funkcijas, mēs tagad peldējamies uz dažām Microsoft Excel uzlabotajām funkcijām. Mēs piedāvājam metodes, lai atrisinātu klasiskās problēmas finansēs, pārdošanas pārskatos, nosūtīšanas izmaksās un statistikā.
SKOLAS NAVIGĀCIJAŠīs funkcijas ir svarīgas uzņēmējiem, studentiem un tiem, kuri vienkārši vēlas uzzināt vairāk.
Šeit ir piemērs, lai ilustrētu vertikālās uzmeklēšanas (VLOOKUP) un horizontālās uzmeklēšanas (HLOOKUP) funkcijas. Šīs funkcijas tiek izmantotas, lai numuru vai citu vērtību tulkotu uz saprotamu. Piemēram, jūs varat izmantot VLOOKUP, lai ņemtu daļu numuru un atgrieztu objekta aprakstu.
Lai to pārbaudītu, atgriezīsimies 4. nodaļā esošajā "Lēmumu veidotāja" izklājlapā, kurā Džeina mēģina izlemt, ko valkāt skolā. Viņai vairs nav interese par to, ko viņa nēsā, jo viņa ir atnākusi pie jauna drauga, tāpēc viņa tagad nēsīs izlases apģērbus un apavus.
Džeinas izklājlapā viņa uzrāda apģērbus vertikālās kolonnās un kurpēs, horizontālās kolonnas.
Viņa atver izklājlapu, un funkcija RANDBETWEEN (1,3) ģenerē skaitli, kas vienāds ar vienu un trīs, vai trīs, kas atbilst trijiem apģērba veidiem, ko viņa var valkāt.
Viņa izmanto funkciju RANDBETWEEN (1,5), lai izvēlētos piecus veidu apavus.
Tā kā Džeina nevar valkāt numuru, mums tas jāpārveido par vārdu, tāpēc mēs izmantojam meklēšanas funkcijas.
Mēs izmantojam VLOOKUP funkciju, lai tulkotu apģērbs numuru apģērbs nosaukumu. HLOOKUP translē no apavu skaita uz dažādiem apavu veidiem rindā.
Izklājlapa darbojas tāpat kā tērpiem:
Excel izlases numuru izvēlas no viena līdz trim, jo viņai ir trīs apģērbs.
Nākamais formulējums pārvērš numuru tekstu, izmantojot = VLOOKUP (B11, A2: B4,2), kas izmanto nejaušo skaitli no B11 vērtības, lai izskatītu diapazonu A2: B4. Pēc tam rezultāts (C11) dod rezultātus, kas uzskaitīti otrajā ailē.
Mēs izmantojam tādu pašu paņēmienu, kā izvēlēties kurpes, izņemot šo laiku, kad mēs izmantojam VOOKUP, nevis HLOOKUP.
Gandrīz visi zina vienu statistikas formulu - vidējo - bet ir vēl viena statistika, kas ir svarīga uzņēmējdarbībai: standarta novirze.
Piemēram, daudzi cilvēki, kas devušies uz koledžu, agonized pār savu SAT rezultātu. Viņi varētu vēlēties uzzināt, kā viņi vērtē, salīdzinot ar citiem studentiem. Universitātes arī vēlas to zināt, jo daudzas universitātes, it īpaši prestižas, izslēdz studentus ar zemiem SAT rādītājiem.
Tātad, kā mēs, vai universitāte, izmērītu un interpretētu SAT rezultātus? Zemāk ir SAT rādītāji pieciem skolēniem, sākot no 1870 līdz 2230.
Svarīgie skaitļi, kas jāsaprot, ir šādi:
Vidēji - Vidējo sauc arī par "vidējo".
Standarta novirze (STD vai σ) - Šis skaitlis parāda, cik plaši izkliedēta virkne numuru. Ja standarta novirze ir liela, tad skaitļi ir tālu viens no otra, un, ja tas ir nulle, visi numuri ir vienādi. Jūs varētu teikt, ka standarta novirze ir vidējā starpība starp vidējo vērtību un novēroto vērtību, t.i., 998 un katru SAT punktu. Lūdzu, ņemiet vērā, ka parasti ir saīsināt standarta novirzi, izmantojot grieķu simbolu sigma "σ".
Procentiles rangs - Kad students saņem augstu rezultātu, viņi var paļauties, ka tie ir augstākā 99 procentu vai kaut kas līdzīgs šim. "Procentiles rangs" nozīmē, ka punktu skaits ir mazāks par vienu konkrētu punktu skaitu.
Standarta novirze un varbūtība ir cieši saistītas. Varat teikt, ka attiecībā uz katru standarta novirzi varbūtība vai iespējamība, ka šis skaitlis ir iekšā šo standarta noviržu skaitu, ir:
| STD | Punktu skaits procentos | SAT rādītāju diapazons |
| 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 |
Kā redzat, iespēja, ka jebkurš SAT rezultāts ir ārpus 3 STD, ir praktiski nulle, jo 99,73 procenti no rezultātiem ir 3 STD.
Tagad atkal apskatīsim izklājlapu un paskaidrosim, kā tā darbojas.
Tagad mēs izskaidrojam formulas:
= VIDĒJS (B2: B6)
Visu rādītāju vidējais rādītājs diapazonā B2: B6. Konkrēti, visu rezultātu summa, kas dalīta ar to cilvēku skaitu, kuri nokārtojuši pārbaudi.
= STDEV.P (B2: B6)
Standarta novirze diapazonā B2: B6. ".P" nozīmē, ka STDEV.P tiek izmantots visos punktos, t.i., visam iedzīvotājam, nevis tikai apakškopai.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Tas aprēķina kumulatīvo procentuālo attiecību diapazonā B2: B6, pamatojoties uz SAT rādītāju, šajā gadījumā B2. Piemēram, 83 procenti no rezultātiem ir zem Walkera vērtējuma.
Rezultātu ievietošana grafikā atvieglo rezultātu izpratni, kā arī varat to parādīt prezentācijā, lai padarītu savu punktu skaidrāku.
Studenti atrodas horizontālajā asī, un viņu SAT rādītāji tiek rādīti kā zilā joslas diagramma skalā (vertikālā ass) no 1600 līdz 2300.
Percentile ranga ir labās puses vertikālā ass no 0 līdz 90 procentiem, un to attēlo pelēkā līnija.
Diagrammas izveide ir pati par sevi tēma, taču mēs īsi paskaidrosim, kā izveidota iepriekšējā diagramma.
Vispirms izvēlieties diagrammu diapazonu šūnām. Šajā gadījumā no A2 uz C6, jo mēs vēlamies gan ciparus, gan studenta vārdus.
No izvēlnes "Ievietot" izvēlieties "Diagrammas" -> "Ieteicamās diagrammas":
Dators iesaka diagrammu "Clustered-Column, Secondary Axis". Daļa "Sekundārā ass" nozīmē, ka tā piesaista divas vertikālas asis. Šajā gadījumā šī diagramma ir tā, kuru mēs vēlamies. Mums nav jādara kaut kas cits.
Jūs varat izmantot pārvietot diagrammu apkārt un no jauna izmērīt to, līdz esat to izmēris un vēlamā pozīcijā. Kad esat apmierināts, varat saglabāt diagrammu izklājlapā.
Ja jūs ar peles labo pogu noklikšķiniet uz diagrammas, pēc tam izvēlieties "Atlasīt datus", tas parāda, kādi dati tiek atlasīti diapazonam.
Iezīme "Ieteicamās diagrammas" parasti neļauj jums rīkoties ar šādām sarežģītām detaļām, piemēram, nosakot, kādus datus iekļaut, kā piešķirt etiķetes un kā piešķirt kreisās un labās vertikālās asis.
Dialoglodziņā "Atlasīt datu avotu" noklikšķiniet uz "score" zem "Legend ieraksti (sērija)" un nospiediet "Rediģēt" un mainiet to, lai teiktu "Rezultāts".
Pēc tam mainiet 2. sēriju ("procentile") uz "Percentile".
Atgriezieties savā diagrammā un noklikšķiniet uz "Chart Title" un mainiet to uz "SAT Scores". Tagad mums ir pilnīga diagramma. Tam ir divas horizontālas asis: viena SAT punktam (zila) un viena kumulatīvā procentuālā daļa (oranža).
Transporta problēma ir klasisks piemērs matemātikas tipam, ko sauc par "lineāru programmēšanu". Tas ļauj maksimizēt vai samazināt vērtību, uz kuru attiecas noteikti ierobežojumi. Tai ir daudzas lietojumprogrammas dažādām biznesa problēmām, tādēļ ir lietderīgi uzzināt, kā tā darbojas.
Pirms mēs sākam ar šo piemēru, mums ir jāiespējo Excel Solver.
Atlasiet "File" -> "Options" -> "Add-ins". Papildinājumu papildinājumu apakšā noklikšķiniet uz pogas "Aiziet" blakus "Pārvaldīt: Excel pievienojumprogrammas".
Parādītajā izvēlnē noklikšķiniet uz izvēles rūtiņas, lai iespējotu, "Solver Add-in" un noklikšķiniet uz "Labi".
Pieņemsim, ka mēs piegādājam iPads, un mēs cenšamies aizpildīt mūsu izplatīšanas centrus, izmantojot iespējami zemākās transporta izmaksas. Mums ir vienošanās ar transporta un aviosabiedrību, lai piegādātu iPads no Šanhajas, Pekinas un Honkongas uz tālāk norādītajiem sadales centriem.
Katra iPad cena ir attālums no rūpnīcas līdz izplatīšanas centram, kas tiek sadalīts ar 20 000 kilometru. Piemēram, tas ir 8 024 km no Šanhajas uz Melburnu, kas ir 8 024/20 000 vai $ 40 par iPad.
Jautājums ir, kā mēs nosūtīt visus šos iPads no šiem trim augiem uz šiem četriem galamērķiem ar viszemāko iespējamo cenu?
Kā jūs varat iedomāties, tas var būt ļoti grūti saprast bez formulas un rīka. Šajā gadījumā mums ir jānosūta 462 000 (F12) kopējo iPads. Augu ierobežotā ietilpība ir 500,250 (G12) vienības.
Izklājlapā, lai jūs varētu redzēt, kā tas darbojas, mēs esam ievadījuši 1 uz šūnu B10, tādēļ mēs vēlamies nosūtīt 1 iPad no Šanhajas uz Melburnu. Tā kā transporta izmaksas šajā maršrutā ir $ 0,40 par iPad, kopējās izmaksas (B17) ir $ 0,40.
Numurs tika aprēķināts, izmantojot funkciju = SUMPRODUCT (izmaksas, nosūtītas), "izmaksas" ir diapazoni B3: E5.
Un "nosūtītas" ir diapazons B9: E11:
SUMPRODUCT reizina "izmaksas" reizes diapazonā "nosūtīts" (B14). To sauc par "matricas reizināšanu".
Lai SUMPRODUCT darbotos pareizi, abām matricām - izmaksām un nosūtītajam - jābūt vienādiem izmēriem. Jūs varat apiet šo ierobežojumu, veicot papildu izmaksas un piegādes kolonnas un rindas ar nulles vērtību, lai masīvi būtu vienādi un neietekmētu kopējās izmaksas.
Ja viss, kas mums bija jādara, bija reizināt matricas "izmaksas" reizes, "nosūtītas", kas nebūtu pārāk sarežģīta, bet mums ir jārisina arī ierobežojumi tur.
Mums ir jānosūta tas, ko pieprasa katrs izplatīšanas centrs. Mēs šo konstantu ievietojām solverā šādi: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Tas nozīmē, ka sūtīto summu, t.i., summām šūnās $ B $ 12: $ E $ 12 jābūt lielākām vai vienādām ar to, ko pieprasa katrs izplatīšanas centrs ($ B $ 13: $ E $ 13).
Mēs nevaram piegādāt vairāk nekā mēs ražojam. Mēs rakstām šādus ierobežojumus: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Vēl viens veids, ko mēs piegādājam no katra auga: $ F $ 9: $ F $ 11 nevar pārsniegt (jābūt mazākam vai vienādam ar) katras iekārtas jaudu: $ G $ 9: $ G $ 11.
Tagad ejiet uz izvēlni "Dati" un nospiediet pogu "Solver". Ja pogai "Solver" nav vietas, jums ir jāiespējo Solver pievienojumprogramma.
Ievadiet abus ierobežojumus, kas ir detalizēti aprakstīti agrāk, un izvēlieties diapazonu "Piegādes", kas ir tādu skaitļu diapazons, ko Excel vēlamies aprēķināt. Arī izvēlieties noklusējuma algoritmu "Simplex LP" un norādiet, ka mēs vēlamies "minimizēt" šūnu B15 ("kopējās piegādes izmaksas"), kur tas saka "Set Objective".
Nospiediet "Atrisiniet" un Excel saglabā rezultātus izklājlapā, ko mēs vēlamies. Varat arī to saglabāt, lai jūs varētu spēlēt ar citiem scenārijiem.
Ja dators saka, ka tas nevar atrast risinājumu, tad esat veicis kaut ko tādu, kas nav loģisks, piemēram, jūs, iespējams, esat pieprasījis vairāk iPad, nekā augi var ražot.
Šeit Excel saka, ka tā ir atradusi risinājumu. Nospiediet "OK", lai saglabātu risinājumu un atgrieztos izklājlapā.
Kā uzņēmums nolemj ieguldīt jaunā projektā? Ja "neto pašreizējā vērtība" (NPV) ir pozitīva, viņi to ieguldīs. Šī ir standarta pieeja, ko izmanto lielākā daļa finanšu analītiķu.
Piemēram, domājams, ka Codelco ieguves kompānija vēlas paplašināt Andinas vara raktuves. Standarta pieeja, lai noteiktu, vai turpināt darbu ar projektu, ir aprēķināt neto pašreizējo vērtību. Ja NPV ir lielāks par nulli, tad projekts būs rentabls, ņemot vērā divus ieguldījumus (1) un (2) kapitāla izmaksas.
Vienkāršā angļu valodā kapitāla izmaksas nozīmē, cik daudz šī nauda tiktu nopelnīta, ja tikko tiktu atstāta bankā. Jūs izmantojat kapitāla izmaksas, lai diskontētu skaidras naudas vērtības pašreizējai vērtībai, citiem vārdiem sakot, piecu gadu laikā 100 ASV dolāri šodien varētu būt 80 ASV dolāri.
Pirmajā gadā, lai finansētu projektu, ir paredzēts 45 miljons dolāru. Grāmatveži nosaka, ka viņu kapitāla izmaksas ir seši procenti.
Sākot ieguvi, nauda sākas, kad uzņēmums atrod un pārdod to ražoto varu. Acīmredzot, jo vairāk viņi man, jo vairāk naudas viņi dara, un viņu prognoze parāda savu naudas plūsmas pieaugumu, līdz tas sasniedz 9 miljonus ASV dolāru gadā.
Pēc 13 gadiem NPV ir USD 3 945 074, tāpēc projekts būs rentabls. Pēc Finanšu analītiķu domām, "atmaksāšanas periods" ir 13 gadi.
Pivot tabula pamatā ir atskaite. Mēs tos saucam par pagrieziena galdiem, jo jūs varat viegli pārslēgt tos no viena ziņojuma veida uz otru, neveidojot pilnīgi jaunu pārskatu. Tātad viņi šarnīrs vietā. Parādīsim piemēru, kas māca pamatjēdzienus.
Pārdošanas cilvēki ir ļoti konkurētspējīgi (tas ir daļa no pārdošanas), tāpēc viņi, protams, vēlas uzzināt, kā viņi cenšas cīnīties viena pret otru ceturkšņa beigās un gada beigās, kā arī to, cik liela būs viņu komisijas nauda.
Pieņemsim, ka mums ir trīs pārdošanas cilvēki - Carlos, Fred un Julie - visi pārdod naftas produktus. To pārdošanas apjoms ASV dolāros par fiskālo ceturksni 2014. gadam ir redzams zemāk redzamajā izklājlapā.
Lai ģenerētu šos pārskatus, mēs izveidojam pagrieziena tabulu:
Atlasiet "Insert -> Pivot Table, kas atrodas rīkjoslas kreisajā pusē:
Atlasiet visas rindas un slejas (ieskaitot pārdevēja vārdu), kā parādīts zemāk:
Pivot tabulas dialoglodziņš parādās izklājlapas labajā pusē.
Ja pogas galda dialoglodziņā (ceturksnis, gads, pārdošana un pārdevējs) mēs noklikšķinām uz visiem četriem laukiem, Excel pievieno izklājlapas atskaiti, kas nav jēga, bet kāpēc?
Kā redzat, mēs esam atlasījuši visus četrus laukus, kurus pievienot pārskatam. Excel noklusējuma darbība ir grupēt rindas pēc teksta laukiem un pēc tam apkopot visas pārējās rindas.
Šeit tas dod mums summu 2014 + 2014 + 2014 + 2014 = 24,168, kas ir absurds. Tas arī ir ceturkšņu summa 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Mums šī informācija nav nepieciešama, tāpēc mēs noņemam no šiem laukiem atlasi, lai tos noņemtu no mūsu pagrieziena galda.
Tomēr "pārdošanas apjoms" (kopējais pārdošanas apjoms) ir atbilstošs, tāpēc mēs to novērsīsim.
Jūs varat rediģēt "pārdošanas summu", kas ir skaidrāks, lai teiktu "Kopējie pārdošanas apjomi". Arī jūs varat formatēt šūnas kā valūtu, tāpat kā jūs formatētu citas šūnas. Vispirms noklikšķiniet uz "Summa pārdošanas" un atlasiet "Value Field Settings".
Nākamajā dialogā mēs mainām nosaukumu uz "Kopējie pārdošanas apjomi", pēc tam noklikšķiniet uz "Numuru formāts" un mainiet to uz "Valūta".
Tad jūs varat redzēt savu roku darbā pivot tabulā:
Tagad pievienosim ceturkšņa starpsummas. Lai pievienotu starpsummas, vienkārši kreiso pogu noklikšķiniet uz lauka "Ceturksnis" un turiet un velciet to uz rindu sadaļu. Jūs varat redzēt rezultātu ekrānuzņēmumā zemāk:
Lai gan mēs to esam pieņēmuši, noņemiet "Ceturkšņa summas" vērtības. Vienkārši noklikšķiniet uz bultiņas un noklikšķiniet uz "Noņemt lauku". Ekrānuzņēmumā tagad varat redzēt, ka esam pievienojuši rindas "Ceturksnī", kas katru ceturksni sadala katra pārdevēja pārdošanas apjomu.
Ņemot vērā šīs prasmes, jūs tagad varat izveidot pivot tabulas no saviem datiem!
Iesaiņošanas procesā esam parādījuši dažas Microsoft Excel formulas un funkcijas, kuras jūs varat pielietot Microsoft Excel savām uzņēmējdarbības, akadēmiskajām vai citām vajadzībām.
Kā jūs redzējāt, Microsoft Excel ir milzīgs produkts ar tik daudzām funkcijām, ka lielākā daļa cilvēku, pat proti lietotāji, tos nezina. Daži cilvēki varētu teikt, ka tas ir sarežģīti; mēs uzskatām, ka tā ir visaptverošāka.
Cerams, ka, parādot daudz reālu piemēru, mēs esam parādījuši ne tikai funkcijas, kas pieejamas programmā Microsoft Excel, bet kas jums ir iemācījušas kaut ko par statistiku, lineāru programmēšanu, diagrammu izveidi, nejaušo skaitļu izmantošanu un citām idejām, ko tagad varat pieņemt un izmantojiet savā skolā vai kur strādājat.
Atcerieties, ka, ja vēlaties atgriezties un atgriezties klasē, jūs varat sākt svaigu ar 1. nodarbību!
