Căutări, diagrame, statistici și tabele pivot

După examinarea funcțiilor de bază, a referințelor de celule și a funcțiilor datei și orei, acum se aruncă cu capul în unele dintre cele mai avansate caracteristici ale programului Microsoft Excel. Prezentăm metode pentru a rezolva problemele clasice în domeniul finanțelor, rapoartelor de vânzări, costurilor de expediere și statisticilor.

ȘCOLAREA NAVIGAȚIEI

1. De ce aveți nevoie de formule și funcții?
2. Definirea și crearea unei formule
3. Referință celulară relativă și absolută și formatare
4. Funcții utile pe care ar trebui să le cunoașteți
5. Căutări, diagrame, statistici și tabele pivot

Aceste funcții sunt importante pentru mediul de afaceri, pentru studenți și pentru cei care doresc să afle mai mult.

VLOOKUP și HLOOKUP

Iată un exemplu pentru a ilustra funcțiile de căutare verticală (VLOOKUP) și de căutare orizontală (HLOOKUP). Aceste funcții sunt folosite pentru a traduce un număr sau o altă valoare în ceva care este de înțeles. De exemplu, puteți utiliza VLOOKUP pentru a obține un număr de piesă și pentru a returna descrierea elementului.

Pentru a investiga acest lucru, să ne întoarcem la foaia de calcul "Decizia Maker" din Partea 4, unde Jane încearcă să decidă ce să poarte la școală. Ea nu mai este interesată de ceea ce poartă, deoarece a aterizat pe un nou prieten, așa că va purta acum costume și pantofi aleatorii.

În foaia de calcul a lui Jane, ea menționează costumele în coloane și pantofi verticali, coloane orizontale.

Ea deschide foaia de calcul și funcția RANDBETWEEN (1,3) generează un număr între sau egal cu unul și trei corespunzând celor trei tipuri de tinute pe care le poate purta.

Folosește funcția RANDBETWEEN (1,5) pentru a alege între cinci tipuri de pantofi.

Deoarece Jane nu poate purta un număr, trebuie să convertim acest nume într-un nume, așa că folosim funcții de căutare.

Folosim funcția VLOOKUP pentru a traduce numărul de echipă în numele echipei. HLOOKUP se traduce de la numărul de pantofi la diferitele tipuri de pantofi din rând.

Foaia de calcul funcționează astfel pentru echipamente:

Excel alege un număr aleator de la unul la trei, deoarece are trei opțiuni pentru tinuta.

Apoi formularul traduce numărul în text folosind = VLOOKUP (B11, A2: B4,2), care folosește numărul aleatoriu din B11 pentru a privi în intervalul A2: B4. Apoi rezultă rezultatul (C11) din datele enumerate în coloana a doua.

Folosim aceeași tehnică pentru a alege pantofii, cu excepția cazului în care folosim VOOKUP în loc de HLOOKUP.

Exemplu: Statistici de bază

Aproape toată lumea cunoaște o formulă din statistici - media - dar există o altă statistică importantă pentru afaceri: abaterea standard.

De exemplu, multe persoane care au plecat la facultate au agonizat peste scorul lor SAT. Ei ar putea dori să știe cum se situează în comparație cu ceilalți studenți. Universitățile vor să știe și acest lucru, deoarece multe universități, în special cele de prestigiu, dau curs studenților cu scoruri slabe în SAT.

Deci, cum am putea, sau o universitate, să măsurăm și să interpretăm scorurile SAT? Mai jos sunt scorurile SAT pentru cinci studenți, variind de la 1.870 la 2.230.

Numerele importante pe care trebuie să le înțelegeți sunt:

In medie - Media este deasemenea menționată ca "medie".

Deviația standard (STD sau σ) - Acest număr arată cât de dispersat este un set de numere. Dacă abaterea standard este mare, atunci numerele sunt foarte îndepărtate și dacă este zero, toate numerele sunt aceleași. S-ar putea spune că deviația standard este diferența medie între valoarea medie și valoarea observată, adică 1.998 și fiecare scor SAT. Rețineți că este comună abrevierea deviației standard folosind simbolul grec sigma "σ."

Rata percentilei - Când un elev primește un scor mare, se pot lăuda că se află în top 99 de procente sau ceva de genul acesta. "Rata procentuală" înseamnă procentajul scorurilor este mai mic decât un punct special.

Abaterea standard și probabilitatea sunt strâns legate. Se poate spune că pentru fiecare abatere standard, probabilitatea sau probabilitatea ca acest număr să se încadreze în acest număr de deviații standard este:

STD	Procentaj de scoruri	Gama de scoruri SAT
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

După cum puteți vedea, șansa ca orice scor SAT să se afle în afara celor 3 STD-uri este practic zero, deoarece 99,73% din scoruri sunt în cadrul a 3 STD-uri.

Acum, să ne uităm din nou la foaia de calcul și să explicăm cum funcționează.

Acum explicăm formulele:

= AVERAGE (B2: B6)

Media tuturor scorurilor din intervalul B2: B6. Mai exact, suma tuturor scorurilor împărțită la numărul de persoane care au luat testul.

= STDEV.P (B2: B6)

Abaterea standard pe intervalul B2: B6. ".P" înseamnă STDEV.P este utilizat peste toate scorurile, adică întreaga populație și nu doar un subset.

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

Aceasta calculează procentul cumulat pe intervalul B2: B6 pe baza scorului SAT, în acest caz B2. De exemplu, 83% din scoruri sunt sub scorul lui Walker.

Graficul rezultatelor

Punerea rezultatelor într-un grafic facilitează înțelegerea rezultatelor, plus puteți să le arătați într-o prezentare pentru a vă face mai clar punctul.

Elevii sunt pe axa orizontală, iar scorurile SAT sunt afișate ca un grafic albastru pe o scară (axa verticală) de la 1.600 la 2.300.

Clasamentul percentilei este axa verticală din dreapta de la 0 la 90% și este reprezentată de linia gri.

Cum se creează o diagramă

Crearea unei diagrame este un subiect în sine, însă vom explica pe scurt cum a fost creată schema de mai sus.

Mai întâi, selectați intervalul de celule care urmează să fie în diagramă. În acest caz, A2 la C6 pentru că vrem numerele, precum și numele studenților.

Din meniul "Inserare" selectați "Diagrame" -> "Diagrame recomandate":

Calculatorul recomandă o diagramă "Coloană clustită, Axă secundară". Partea "axă secundară" înseamnă că trage două axe verticale. În acest caz, această diagramă este cea pe care o dorim. Nu trebuie să facem nimic altceva.

Puteți utiliza mutați graficul și re-dimensionați-l până când îl aveți ca dimensiune și în poziția dorită. După ce sunteți mulțumit, puteți salva graficul în foaia de calcul.

Dacă dați clic dreapta pe diagramă, apoi pe "Selectați date", vă arată ce date sunt selectate pentru interval.

Caracteristica "Recomandări grafice" de obicei vă împiedică să vă confruntați cu detalii atât de complicate cum ar fi determinarea datelor care trebuie incluse, modul de alocare a etichetelor și modul de atribuire a axelor verticale stânga și dreaptă.

În caseta de dialog "Selectare sursă de date", faceți clic pe "scor" din meniul "Legend Entries (Series)" și apăsați "Editare" și schimbați-o pentru a indica "Scor".

Apoi schimbați seria 2 ("percentilă") la "Percentilă".

Întoarceți-vă la diagramă și dați clic pe "Diagrama Titlu" și schimbați-o la "Scoruri SAT". Acum avem o diagramă completă. Are două axe orizontale: una pentru scorul SAT (albastru) și una pentru procentul cumulat (portocaliu).

Exemplu: problema de transport

Problema transportului este un exemplu clasic de tip matematic numit "programare liniară". Aceasta vă permite să maximizați sau să minimizați o valoare supusă anumitor constrângeri. Are multe aplicații pentru o gamă largă de probleme de afaceri, deci este util să înveți cum funcționează.

Înainte de a începe acest exemplu, trebuie să activați "Solverul Excel".

Activați add-in Solver

Selectați "Fișier" -> "Opțiuni" -> "Suplimente". În partea de jos a opțiunilor de completare, faceți clic pe butonul "Deplasați" de lângă "Gestionați: adnotări Excel".

În meniul rezultat, faceți clic pe caseta de selectare pentru a activa "Solver Add-in" și faceți clic pe "OK".

Exemplu: Calculați cele mai mici costuri de transport iPad

Să presupunem că vindem iPad-uri și că încercăm să umplem centrele de distribuție folosind cele mai mici costuri de transport. Avem un acord cu o companie de transport de marfă și de transport aerian pentru a transporta iPads de la Shanghai, Beijing și Hong Kong la centrele de distribuție prezentate mai jos.

Prețul pentru a expedia fiecare iPad este distanța de la fabrica la centrul de distribuție la plantă împărțită la 20.000 de kilometri. De exemplu, este de 8,024 km de la Shanghai la Melbourne, care este de 8.024 / 20.000 sau $ .40 pe iPad.

Întrebarea este cum vom livra toate aceste iPad-uri de la aceste trei fabrici la aceste patru destinații la cel mai mic cost posibil?

După cum vă puteți imagina, imaginarea acestui lucru ar putea fi foarte dificilă, fără o formulă și un instrument. În acest caz, trebuie să expediem 462.000 (F12) iPad-uri totale. Plantele au o capacitate limitată de 500.250 (G12).

În foaia de calcul, pentru a vedea cum funcționează, am scris 1 în celula B10, ceea ce înseamnă că vrem să transportați un iPad de la Shanghai la Melbourne. Deoarece costurile de transport de-a lungul acelei rute sunt de 0,40 USD pe iPad, costul total (B17) este de 0,40 USD.

Numărul a fost calculat folosind funcția = SUMPRODUCT (costuri, expediate) "costuri" sunt intervalele B3: E5.

Și "expediate" sunt domeniile B9: E11:

SUMPRODUCT multiplică "costurile" în intervalul "livrat" (B14). Aceasta se numește "multiplicare matriceală".

Pentru ca SUMPRODUCT să funcționeze corect, cele două matrice - costurile și expedițiile - trebuie să aibă aceeași dimensiune. Puteți trece prin această limitare, făcând costuri suplimentare și coloane de transport și rânduri cu valoare zero, astfel încât matricele să aibă aceeași dimensiune și să nu aibă nici un impact asupra costurilor totale.

Utilizând Solver

Dacă tot ce trebuia să facem era să multiplicăm "costurile" matricelor "expediate" care nu ar fi prea complicate, dar trebuie să facem față și constrângerilor acolo.

Trebuie să livrăm ceea ce cere fiecare centru de distribuție. Am pus acea constantă în rezolvatorul astfel: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Aceasta înseamnă suma sumelor livrate, adică totalurile din celulele $ B $ 12: $ E $ 12, trebuie să fie mai mare sau egală cu ceea ce cere fiecare centru de distribuție ($ B $ 13: $ E $ 13).

Nu putem nava mai mult decât producem. Noi scriem acele constrângeri ca acesta: $ 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.

Acum mergeți la meniul "Data" și apăsați butonul "Solver". Dacă nu există butonul "Solver", trebuie să activați add-in-ul Solver.

Introduceți cele două constrângeri detaliate mai sus și selectați intervalul "Livrări", care este intervalul de numere pe care doriți să-l calculați Excel. De asemenea, alegeți algoritmul implicit "Simplex LP" și indicați că dorim să "minimalizăm" celula B15 ("costurile totale de expediere"), unde se afișează "Set Obiectiv".

Apăsați "Rezolvați" și Excel salvează rezultatele în foaia de calcul, ceea ce dorim. Puteți, de asemenea, salva acest lucru, astfel încât să puteți juca cu alte scenarii.

În cazul în care computerul spune că nu poate găsi o soluție, atunci ați făcut ceva care nu este logic, de exemplu, este posibil să fi solicitat mai multe iPad-uri decât pot produce plantele.

Aici Excel spune că a găsit o soluție. Apăsați "OK" pentru a păstra soluția și a reveni la foaia de calcul.

Exemplu: valoarea actuală netă

Cum decid o companie să investească într-un nou proiect? Dacă "valoarea actuală netă" (NPV) este pozitivă, ei vor investi în aceasta. Aceasta este o abordare standard adoptată de majoritatea analiștilor financiari.

De exemplu, să presupunem că firma minieră Codelco dorește să extindă mina de cupru Andinas. Abordarea standard pentru a determina dacă trebuie să progresăm cu un proiect este de a calcula valoarea actuală netă. Dacă NPV este mai mare decât zero, atunci proiectul va fi profitabil datând două intrări (1) timp și (2) costul capitalului.

În limbajul englez, costul capitalului înseamnă cât de mult ar câștiga acești bani dacă tocmai l-ar fi lăsat în bancă. Utilizați costul capitalului pentru a reduce cuantumul valorilor în numerar până la valoarea actualizată, cu alte cuvinte, 100 de dolari în cinci ani ar putea fi de 80 de dolari astăzi.

În primul an, 45 de milioane de dolari sunt scoase din circuitul agricol ca capital pentru finanțarea proiectului. Contabilii au stabilit că costul capitalului lor este de șase la sută.

Pe măsură ce încep mineritul, banii încep să vină în timp ce compania găsește și vinde cuprul pe care îl produc. Evident, cu cât sunt mai mulți, cu atât mai mulți bani fac și previziunile arată că fluxul lor de numerar crește, până când ajunge la 9 milioane de dolari pe an.

După 13 ani, NPV este de 3.945.074 USD, deci proiectul va fi profitabil. Potrivit analiștilor financiari, "perioada de returnare" este de 13 ani.

Crearea unei tabele pivot

O "masă pivot" este în esență un raport. Noi le numim tabele pivot pentru ca le puteti schimba cu usurinta un tip de raport la altul fara a fi nevoie sa faceti un intreg nou raport. Astfel ei pivot la loc. Să arătăm un exemplu de bază care învață conceptele de bază.

Exemplu: rapoarte de vânzări

Vânzătorii sunt foarte competitivi (făcând parte din vânzări), așa că, în mod firesc, vor să știe cum se opresc unul față de celălalt la sfârșitul trimestrului și sfârșitul anului, plus cât de mult vor fi comisioanele.

Să presupunem că avem trei oameni de vânzări - Carlos, Fred și Julie - toți cei care vând petrol. Vânzările lor în dolari pe trimestru fiscal pentru anul 2014 sunt prezentate în foaia de calcul de mai jos.

Pentru a genera aceste rapoarte, vom crea un tabel pivot:

Selectați "Insert -> Pivot Table, este în partea stângă a barei de instrumente:

Selectați toate rândurile și coloanele (inclusiv numele vânzătorului) după cum se arată mai jos:

Caseta de dialog pivot tabelă apare în partea dreaptă a foii de calcul.

Dacă faceți clic pe toate cele patru câmpuri din caseta de dialog Pivot table (Quarter, Year, Sales și Salesperson), Excel adaugă un raport la foaia de calcul care nu are sens, dar de ce?

După cum puteți vedea, am selectat toate cele patru câmpuri pentru a adăuga la raport. Excelentul comportament al lui Excel este de a grupa rândurile prin câmpuri de text și apoi suma celorlalte rânduri.

Aici ne dă suma anului 2014 + 2014 + 2014 + 2014 = 24,168, ceea ce este un nonsens. De asemenea, a dat suma sferturilor 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Nu avem nevoie de aceste informatii, deci deselectam aceste campuri pentru a le elimina din masa pivot.

"Suma vânzărilor" (vânzările totale) este pertinentă, totuși, deci o vom rezolva.

Exemplu: vânzări de către vânzător

Puteți edita "Suma vânzărilor" care să spună "Total vânzări", care este mai clară. De asemenea, puteți să formatați celulele ca monedă, la fel cum ați forma orice alte celule. Primul click pe "Suma vânzărilor" și selectați "Setări câmp valoare".

În dialogul rezultat, schimbăm numele în "Vânzări totale", apoi dă clic pe "Formatul numărului" și îl schimbi în "Valută".

Apoi, puteți vedea manualele dvs. în tabelul de pivot:

Exemplu: Vânzări prin vânzător și trimestru

Acum, să adăugăm subtotaluri pentru fiecare trimestru. Pentru a adăuga subtotal doar faceți clic stânga pe câmpul "Quarter" și țineți-l și glisați-l în secțiunea "rânduri". Puteți vedea rezultatul în imaginea de mai jos:

În timp ce ne aflăm la aceasta, să eliminăm valorile "Sum of Quarter". Pur și simplu faceți clic pe săgeată și faceți clic pe "Eliminați câmpul". În ecranul de ecran, acum puteți vedea că am adăugat rândurile "Quarter", care descompune vânzările fiecărui vânzător în fiecare trimestru.

Cu aceste abilități proaspete în minte, puteți crea acum tabele pivot de la propriile date!

Concluzie

Încărcarea, ne-am arătat câteva dintre caracteristicile formulelor și funcțiilor Microsoft Excel pe care le puteți aplica Microsoft Excel pentru nevoile dvs. de afaceri, academice sau alte nevoi.

După cum ați văzut, Microsoft Excel este un produs enorm, cu atât de multe caracteristici pe care majoritatea oamenilor, chiar și utilizatorii avansați, nu le cunosc pe toți. Unii ar putea spune că acest lucru este complicat; simtim că este mai cuprinzător.

Sperăm că, prin prezentarea multor exemple din viața reală, am demonstrat nu doar funcțiile disponibile în Microsoft Excel, ci v-au învățat ceva despre statistici, programare liniară, crearea de diagrame, folosind numere aleatorii și alte idei pe care le puteți adopta și utilizați în școală sau unde lucrați.

Amintiți-vă, dacă doriți să vă întoarceți și să luați din nou clasa, puteți începe proaspăt cu Lecția 1!