Meklēt, diagrammas, statistika un pagrieziena tabulas

Pārskatot pamatfunkcijas, šūnu atsauces un datuma un laika funkcijas, mēs tagad ieniramies dažās no uzlabotākajām Microsoft Excel funkcijām. Mēs piedāvājam metodes, lai atrisinātu klasiskās problēmas finanšu, pārdošanas ziņojumos, piegādes izmaksās un statistikā.

SKOLAS NAVIGĀCIJA

1. Kāpēc jums ir nepieciešamas formulas un funkcijas?
2. Formulas definēšana un izveide
3. Relatīvā un absolūtā šūnu atsauce un formatēšana
4. Noderīgas funkcijas, kas jums jāzina
5. Meklēt, diagrammas, statistika un pagrieziena tabulas

Šīs funkcijas ir svarīgas uzņēmumiem, studentiem un tiem, kas tikai vēlas uzzināt vairāk.

VLOOKUP un HLOOKUP

Šeit ir piemērs, lai ilustrētu vertikālās meklēšanas (VLOOKUP) un horizontālās meklēšanas (HLOOKUP) funkcijas. Šīs funkcijas tiek izmantotas, lai tulkotu numuru vai citu vērtību kaut kas saprotams. Piemēram, varat izmantot VLOOKUP, lai veiktu detaļas numuru un atgrieztu preces aprakstu.

Lai to izpētītu, atgriezīsimies pie mūsu “Lēmumu veidotāja” izklājlapas 4. daļā, kur Jane mēģina izlemt, ko valkāt skolā. Viņa vairs nav ieinteresēta, ko viņa nēsā, jo viņa ir izkraut jaunu draugu, tāpēc viņa tagad valkā izlases apģērbu un apavus.

Džeinas izklājlapā viņa iekārto apģērbu vertikālās kolonnās un apavos, horizontālās kolonnās.

Viņa atver izklājlapu, un funkcija RANDBETWEEN (1,3) rada skaitli, kas ir vienāds ar vienu vai trīs, kas atbilst trīs veidu apģērbiem, ko viņa var valkāt.

Viņa izmanto funkciju RANDBETWEEN (1,5), lai izvēlētos piecu veidu apavus.

Tā kā Jane nevar valkāt numuru, kas mums ir nepieciešams, lai to pārvērstu par vārdu, tāpēc mēs izmantojam meklēšanas funkcijas.

Mēs izmantojam funkciju VLOOKUP, lai tulkotu apģērba numuru uz apģērba nosaukumu. HLOOKUP no kurpju numura pārvērš rindā dažādus apavu veidus.

Izklājlapu rīki darbojas šādi:

Excel izvēlas izlases numuru no viena līdz trim, jo ​​viņai ir trīs apģērbu iespējas.

Tālāk formula pārvērš numuru uz tekstu, izmantojot = VLOOKUP (B11, A2: B4,2), kas izmanto nejaušu skaitli no B11 vērtības, lai aplūkotu diapazonu A2: B4. Pēc tam tā sniedz rezultātu (C11) no datiem, kas uzskaitīti otrajā slejā.

Mēs izmantojam to pašu tehniku, lai izvēlētos apavus, izņemot šo reizi, izmantojot HLOOKUP vietā VOOKUP.

Piemērs: pamata statistika

Gandrīz visi zina vienu formulu no statistikas - vidējais - 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, ir satraukuši par savu SAT punktu skaitu. Viņi varētu vēlēties uzzināt, kā viņi salīdzina ar citiem studentiem. Arī universitātes to vēlas zināt, jo daudzas universitātes, it īpaši prestižās, samazina studentus ar zemu SAT punktu skaitu.

Tātad, kā mēs, vai universitāte, mērītu un interpretētu SAT rezultātus? Zemāk ir SAT rādītāji pieciem studentiem, sākot no 1,870 līdz 2,230.

Svarīgi saprotamie numuri ir:

Vidējais - Vidējo sauc arī par “vidējo”.

Standarta novirze (STD vai σ) - Šis skaitlis parāda, cik plaši izplatīti skaitļu kopumi. Ja standarta novirze ir liela, tad skaitļi ir tālu viena no otras, un, ja tas ir nulle, visi skaitļi ir vienādi. 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., 1,998 un katru SAT punktu. Lūdzu, ņemiet vērā, ka ir ierasts saīsināt standarta novirzi, izmantojot grieķu simbolu sigma “σ”.

Procentuālais rangs - Kad students saņem augstu punktu skaitu, viņi var lepoties ar to, ka viņi atrodas augstākajā 99 procentilē vai kaut kas tamlīdzīgs. “Procentilais rangs” nozīmē, ka punktu skaits ir zemāks par vienu konkrētu punktu.

Standarta novirze un varbūtība ir cieši saistītas. Varat teikt, ka katrai standarta novirzei varbūtība vai varbūtība, ka šis skaitlis ir šajā standarta noviržu skaitā, ir:

STD	Rezultātu procentuālā daļa	SAT punktu skaits
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 punktu ir 3 STD.

Tagad aplūkosim vēlreiz izklājlapu un paskaidrojiet, kā tā darbojas.

Tagad mēs izskaidrojam formulas:

= VIDĒJAIS (B2: B6)

Visu punktu skaits vidējā diapazonā B2: B6. Konkrētāk, visu punktu kopsumma dalīta ar to cilvēku skaitu, kuri testu veica.

= STDEV.P (B2: B6)

Standarta novirze diapazonā B2: B6. “.P” nozīmē STDEV.P tiek izmantots pār visiem rādītājiem, t.i., visu iedzīvotāju skaitu, nevis tikai apakškopu.

= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)

Tas aprēķina kumulatīvo procentu likmi diapazonā B2: B6, pamatojoties uz SAT punktu skaitu, šajā gadījumā B2. Piemēram, 83 procenti punktu ir zemāk par Walker punktu.

Rezultātu grafiks

Rezultātu ievietošana grafikā atvieglo rezultātu izpratni, kā arī varat to parādīt prezentācijā, lai padarītu jūsu punktu skaidrāku.

Studenti atrodas uz horizontālās ass un to SAT rādītāji tiek parādīti kā zilā josla diagramma skalā (vertikālā ass) no 1600 līdz 2300.

Procentiles rangs ir labās puses vertikālā ass no 0 līdz 90 procentiem, un to attēlo pelēka līnija.

Kā izveidot diagrammu

Diagrammas izveidošana ir tēma, tomēr mēs īsumā paskaidrosim, kā tika izveidota iepriekš minētā diagramma.

Vispirms atlasiet diagrammā esošo šūnu diapazonu. Šajā gadījumā A2 līdz C6, jo mēs vēlamies gan numurus, gan studenta vārdus.

Izvēlnē “Insert” izvēlieties “Charts” -> “Recommended Charts”:

Dators iesaka diagrammu “Klasteru kolonna, sekundārā ass”. „Sekundārā ass” daļa nozīmē, ka tā vērš divas vertikālās asis. Šajā gadījumā šī diagramma ir tā, ko mēs vēlamies. Mums nav jādara neko citu.

Varat izmantot diagrammas pārvietošanu apkārt un atkārtoti izmērīt to, līdz tas ir vajadzīgs kā izmērs un pozīcija. Kad esat apmierināts, varat saglabāt diagrammu izklājlapā.

Ar peles labo pogu noklikšķinot uz diagrammas, tad “Select Data”, tas parāda, kādi dati ir atlasīti diapazonam.

Funkcija “Ieteicamās diagrammas” parasti liek jums izvairīties no tādu sarežģītu detaļu risināšanas, kā noteikt, kādi dati jāiekļauj, kā piešķirt etiķetes un kā piešķirt vertikālās kreisās un labās ass.

Dialoglodziņā “Select Data Source” atlasiet “score” zem “Legend Entries (Series)” un nospiediet “Edit” un nomainiet to, lai pateiktu “Score”.

Pēc tam nomainiet 2. sēriju (“procentile”) uz “Percentile”.

Atgriezieties savā diagrammā un noklikšķiniet uz “Chart Title” un nomainiet to uz “SAT Scores”. Tagad mums ir pilnīga diagramma. Tam ir divas horizontālās asis: viena SAT punktam (zilā krāsā) un viena kumulatīvajam procentam (oranžs).

Piemērs: transporta problēma

Transporta problēma ir klasisks piemērs matemātikas tipam, ko sauc par “lineāro programmēšanu”. Tas ļauj maksimizēt vai samazināt vērtību, kas pakļauta noteiktiem ierobežojumiem. Tam ir daudzas lietojumprogrammas, kas aptver plašas biznesa problēmas, tāpēc ir lietderīgi uzzināt, kā tā darbojas.

Pirms sākam ar šo piemēru, mums ir jāiespējo „Excel Solver”.

Iespējot Solver pievienojumprogrammu

Izvēlieties "File" -> "Options" -> "Add-ins". Pievienojumprogrammu opciju apakšā noklikšķiniet uz pogas “Iet” blakus “Pārvaldīt: Excel pievienojumprogrammas”.

Rezultātu izvēlnē noklikšķiniet uz izvēles rūtiņas, lai iespējotu, “Solver Add-in” un noklikšķiniet uz „OK”.

Piemērs: aprēķināt zemākās iPad piegādes izmaksas

Pieņemsim, ka mēs piegādājam iPad un mēs cenšamies aizpildīt mūsu izplatīšanas centrus, izmantojot mazākās iespējamās transporta izmaksas. Mums ir vienošanās ar pārvadājumu un aviokompāniju nosūtīt iPad no Šanhajas, Pekinas un Honkongas uz tālāk redzamajiem izplatīšanas centriem.

Katra iPad nosūtīšanas cena ir attālums no rūpnīcas līdz sadales centram uz rūpnīcu, kas dalīts ar 20 000 kilometriem. Piemēram, tas ir 8,024 km no Šanhajas līdz Melburnai, kas ir 8,024 / 20 000 vai $ 0,40 par iPad.

Jautājums ir par to, kā mēs nosūtām visus šos iPad no šiem trim augiem uz šiem četriem galamērķiem ar viszemākajām iespējamām izmaksām?

Kā jūs varat iedomāties, saprotot, ka tas varētu būt ļoti grūti bez formulas un rīka. Šajā gadījumā mums ir jāsūta 462 000 (F12) iPad. Augiem ir ierobežota ietilpība 500,250 (G12) vienībās.

Izklājlapā, lai jūs varētu redzēt, kā tas darbojas, mēs esam ievadījuši 1 šūnā B10, kas nozīmē, ka mēs vēlamies nosūtīt 1 iPad no Šanhajas uz Melburnu. Tā kā transporta izmaksas šajā maršrutā ir 0,40 ASV dolāri par iPad, kopējās izmaksas (B17) ir 0,40 ASV dolāri.

Numurs tika aprēķināts, izmantojot funkciju = SUMPRODUCT (izmaksas, nosūtīti) “izmaksas” ir diapazoni B3: E5.

Un “nosūtīti” ir diapazons B9: E11:

SUMPRODUCT reizina “izmaksas” reizē ar “nosūtīto” (B14) diapazonu. To sauc par „matricas reizināšanu”.

Lai SUMPRODUCT darbotos pareizi, abām matricām - izmaksām un piegādātajām - jābūt vienāda lieluma. Šo ierobežojumu var iegūt, veicot papildu izmaksas un piegādes slejas un rindas ar nulles vērtību, lai masīvi būtu vienāda izmēra un netiktu ietekmēta kopējās izmaksas..

Solvera izmantošana

Ja viss, kas mums bija jādara, bija reizināt „izmaksas” reizinātās matricas, kas „nav piegādātas”, kas nebūtu pārāk sarežģīts, bet mums ir jārisina arī ierobežojumi..

Mums ir jāpiegādā katrs izplatīšanas centrs. Mēs šo konstanti iekļāvām šādā risinātājā: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Tas nozīmē, ka summa, kas tiek piegādāta, t.i., kopsummā šūnās $ B $ 12: $ E $ 12 jābūt lielākai vai vienādai ar to, kas nepieciešams katram izplatīšanas centram ($ B $ 13: $ E $ 13).

Mēs nevaram nosūtīt vairāk nekā mēs ražojam. Mēs rakstām, ka šādi ierobežojumi ir šādi: $ 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.

Tagad dodieties uz izvēlni “Dati” un nospiediet pogu „Solver”. Ja “Solver” poga nav, jums ir jāiespējo Solver pievienojumprogramma.

Ierakstiet divus iepriekš aprakstītos ierobežojumus un atlasiet diapazonu “Sūtījumi”, kas ir skaitļu diapazons, kuru mēs vēlamies Excel aprēķināt. Izvēlies arī noklusējuma algoritmu “Simplex LP” un norādiet, ka mēs vēlamies “minimizēt” šūnu B15 (“kopējās piegādes izmaksas”), kur tā saka “Set Objective”.

Nospiediet “Solve” 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 apkārt ar citiem scenārijiem.

Ja dators saka, ka nevar atrast risinājumu, tad jūs esat darījuši kaut ko, kas nav loģisks, piemēram, jūs, iespējams, esat pieprasījuši vairāk iPad, nekā augi var ražot.

Šeit Excel saka, ka tā atrada risinājumu. Nospiediet “OK”, lai saglabātu risinājumu un atgrieztos izklājlapā.

Piemērs: Neto pašreizējā vērtība

Kā uzņēmums izlemj, vai ieguldīt jaunā projektā? Ja “neto pašreizējā vērtība” (NPV) ir pozitīva, tās ieguldīs tajā. Šī ir standarta pieeja, ko izmanto lielākā daļa finanšu analītiķu.

Piemēram, pieņemsim, ka Codelco ieguves uzņēmums vēlas paplašināt Andinas vara raktuvi. Standarta pieeja, lai noteiktu, vai virzīties uz priekšu 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 naudas varētu nopelnīt, ja viņi to atstātu bankā. Jūs izmantojat kapitāla izmaksas, lai diskontētu naudas vērtības pašreizējai vērtībai, citiem vārdiem sakot, $ 100 piecos gados varētu būt 80 ASV dolāri.

Pirmajā gadā kā kapitāls ir paredzēts finansēt 45 miljonus ASV dolāru. Grāmatveži nosaka, ka viņu kapitāla izmaksas ir seši procenti.

Kad viņi sāk kalnrūpniecību, nauda sāk ienākt, kad uzņēmums atrod un pārdod saražoto vara. Acīmredzot, jo vairāk viņi mans, jo vairāk naudas viņi dara, un to prognoze parāda to naudas plūsmas pieaugumu, līdz tas sasniedz 9 miljonus ASV dolāru gadā..

Pēc 13 gadiem NPV ir $ 3,945,074 USD, tāpēc projekts būs rentabls. Pēc finanšu analītiķu domām, „atmaksas periods” ir 13 gadi.

Pivot tabulas izveide

“Pivot table” pamatā ir ziņojums. Mēs tos saucam par pivot tabulām, jo ​​jūs varat viegli pārslēgt tos viena veida ziņojumos uz citu, neizmantojot visu jaunu ziņojumu. Tātad viņi pagrieziens vietā. Parādīsim pamata piemēru, kas māca pamatjēdzienus.

Piemērs: pārdošanas pārskati

Pārdošanas darbinieki ir ļoti konkurētspējīgi (tas ir daļa no pārdevēja), tāpēc viņi, protams, vēlas zināt, kā ceturkšņa beigās un gada beigās tie cits citu savstarpēji maksā, kā arī to, cik daudz viņu komisijas būs.

Pieņemsim, ka mums ir trīs pārdevēji - Carlos, Fred un Julie - visi pārdod naftu. To pārdošanas apjomi dolāros par fiskālo ceturksni 2014. gadā ir parādīti tabulā zemāk.

Lai izveidotu šos pārskatus, mēs izveidojam galda tabulu:

Atlasiet “Insert -> Pivot Table, tas ir rīkjoslas kreisajā pusē:

Atlasiet visas rindas un kolonnas (ieskaitot pārdevēja nosaukumu), kā parādīts tālāk:

Pivot tabulas dialoglodziņš parādās izklājlapas labajā pusē.

Ja mēs noklikšķinām uz visiem četriem laukiem dialoglodziņā Pivot table (Quarter, Year, Sales un Salesperson), Excel izklājlapai pievieno ziņojumu, kam nav jēgas, bet kāpēc?

Kā redzat, mēs esam atlasījuši visus četrus laukus, kas jāpievieno ziņojumam. Excel noklusējuma uzvedība ir rindu grupēšana pa teksta laukiem un tad visu pārējo rindu summa.

Šeit tas dod mums 2014 + 2014 + 2014 + 2014 = 24,168, kas ir muļķības, summu. Arī tas ir 1 + 2 + 3 + 4 = 10 * 3 = 3 0 ceturkšņu summa. Mums šī informācija nav nepieciešama, tāpēc mēs atceļam šos laukus, lai tos noņemtu no mūsu galda galda.

Tomēr „Pārdošanas summa” (kopējais pārdošanas apjoms) ir atbilstošs, tāpēc mēs to labosim.

Piemērs: pārdevējs

Jūs varat rediģēt „Pārdošanas summu”, kas ir skaidrāks, sakot „Total Sales”. Jūs varat arī formatēt šūnas kā valūtu, tāpat kā jūs formatētu citas šūnas. Vispirms noklikšķiniet uz “Pārdošanas summa” un atlasiet “Vērtības lauka iestatījumi”.

Rezultātā redzamajā dialoglodziņā mēs nomainām nosaukumu uz “Total Sales”, tad noklikšķiniet uz “Number Format” un nomainiet to uz “Currency”.

Pēc tam varat redzēt savu roku darbu pivot tabulā:

Piemērs: pārdevējs un kvartāls

Tagad pievienosim starpsummas par katru ceturksni. Lai pievienotu starpsummas, kreisajā pusē noklikšķiniet uz lauka „Quarter” un turiet un velciet to uz “rindām”. Jūs varat redzēt rezultātu zemāk esošajā ekrānuzņēmumā:

Kamēr mēs to esam, noņemsim vērtību “Ceturkšņa summa”. Vienkārši noklikšķiniet uz bultiņas un noklikšķiniet uz “Noņemt lauku”. Ekrānuzņēmumā jūs tagad varat redzēt, ka esam pievienojuši rindas “Ceturtdaļa”, kas sadala katra pārdevēja pārdošanas apjomu pa ceturkšņiem.

Paturot prātā šīs prasmes, tagad varat izveidot savas tabulas no saviem datiem!

Secinājums

Iesaiņošana, mēs esam parādījuši jums dažas Microsoft Excel formulu un funkciju funkcijas, ko varat lietot Microsoft Excel savā biznesa, akadēmiskajā vai citā vajadzībām.

Kā jūs redzējāt, Microsoft Excel ir milzīgs produkts ar tik daudzām funkcijām, ko lielākā daļa cilvēku, pat pieredzējuši lietotāji, nezina visus. Daži cilvēki var teikt, ka tas padara to sarežģītu; mēs uzskatām, ka tas ir visaptverošāks.

Cerams, ka, iepazīstinot jūs ar daudziem reāliem dzīves piemēriem, mēs esam parādījuši ne tikai Microsoft Excel funkcijas, bet ir iemācījušies jums kaut ko par statistiku, lineāru programmēšanu, diagrammu izveidi, izmantojot izlases numurus un citas idejas, kuras jūs tagad varat pieņemt un izmantot. izmantot savā skolā vai kur strādājat.

Atcerieties, ka, ja vēlaties atkal atgriezties un ņemt klasi, varat sākt svaigu ar 1. nodarbību!