După examinarea funcțiilor de bază, a referințelor de celule și a funcțiilor de dată și oră, 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.
ȘCOALĂ NAVIGAȚIEAceste funcții sunt importante pentru mediul de afaceri, studenți și pentru cei care doresc să afle mai mult.
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 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ă. Nu mai este interesată de ceea ce poartă, din moment ce 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.
Din moment ce 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 aleatoriu de la unul la trei, deoarece are trei opțiuni pentru tinuta.
În continuare, formularul traduce numărul în text utilizând = VLOOKUP (B11, A2: B4,2) care utilizează numărul aleatoriu din B11 pentru a se uita în intervalul A2: B4. Se dă apoi rezultatul (C11) din datele din coloana a doua.
Folosim aceeași tehnică pentru a alege pantofii, cu excepția cazului în care folosim VOOKUP în loc de HLOOKUP.
Aproape toată lumea cunoaște o formulă din statistici - media - dar există o altă statistică importantă pentru afaceri: abaterea standard.
De exemplu, mulți oameni care au plecat la facultate au agonizat peste scorul SAT. 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 scăzute la 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, de asemenea, numită "medie".
Deviația standard (STD sau σ) - Acest număr arată cât de dispersat este un set de numere. Dacă deviația standard este mare, atunci numerele sunt foarte îndepărtate și dacă este zero, toate numerele sunt aceleași. Ați putea spune că deviația standard este diferența medie dintre 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. Puteți 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 în 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.
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 scală (axa verticală) de la 1.600 la 2.300.
Poziția percentilă este axa verticală din dreapta de la 0 la 90% și este reprezentată de linia gri.
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 din diagramă. În acest caz, A2-C6, deoarece dorim numerele, precum și numele studenților.
Din meniul "Inserare" selectați "Diagrame" -> "Diagrame recomandate":
Computerul 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 în mărime ș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" vă împiedică de obicei să vă confruntați cu detalii atât de complicate cum ar fi determinarea datelor pe care trebuie să le includeți, cum să atribuiți etichete și cum să alocați axele verticale stânga și dreapta.
În fereastra de dialog "Selectați sursa 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ă") până la "Percentilă".
Reveniți la graficul dvs. și faceți clic pe "Diagrama Titlu" și îl modificați în "Scoruri SAT". Acum avem o diagramă completă. Are două axe orizontale: una pentru scorul SAT (albastru) și una pentru procentul cumulat (portocaliu).
Problema de transport este un exemplu clasic de tip matematic numit "programare liniară". Aceasta vă permite să maximizați sau să minimalizaț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 cu acest exemplu, trebuie să activați "Solverul Excel".
Selectați "Fișier" -> "Opțiuni" -> "Suplimente". În partea de jos a opțiunilor de completare, dați clic pe butonul "Mergeți" de lângă "Gestionați: Adăugări Excel".
În meniul rezultat, faceți clic pe caseta de selectare pentru a activa "Solver Add-in" și faceți clic pe "OK".
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 iPad-uri de la Shanghai, Beijing și Hong Kong la centrele de distribuție prezentate mai jos.
Prețul pentru expedierea fiecărui iPad este distanța de la fabrica la centrul de distribuție la instalație împărțită la 20.000 kilometri. De exemplu, este de 8.024 km de la Shanghai la Melbourne, care este 8.024 / 20.000 sau $ .40 pe iPad.
Întrebarea este cum putem livra toate aceste iPad-uri de la aceste trei fabrici către aceste patru destinații la cel mai mic cost posibil?
După cum vă puteți imagina, imaginându-vă că acest 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ă dorim 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) "costurile" 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 obține această limitare prin efectuarea de 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.
Dacă tot ce trebuia să facem era să multiplicăm "costurile" matricilor "expediate" care nu ar fi prea complicate, dar trebuie să facem și constrângeri acolo.
Trebuie să livrăm ceea ce cere fiecare centru de distribuție. Am pus acea constanta in rezolvatorul astfel: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Aceasta înseamnă suma sumelor livrate, adică totalurile din celule $ 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: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Altfel, ceea ce livrăm de la fiecare plantă, $ F $ 9: $ F $ 11 nu poate depăși (trebuie să fie mai mică sau egală cu) capacitatea fiecărei fabrici: $ G $ 9: $ G $ 11.
Acum, mergeți la meniul "Date" ș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 devreme ș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ă vrem să "minimizăm" celula B15 ("costuri 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.
Dacă 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.
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ă avansezi 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 limba engleză, costul capitalului înseamnă cât de mult ar câștiga acești bani dacă 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 minierele, 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 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, astfel încât proiectul va fi profitabil. Potrivit analiștilor financiari, "perioada de returnare" este de 13 ani.
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ă.
Vânzătorii sunt foarte competitivi (face parte din vânzări), astfel încât aceștia, în mod natural, doresc 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 de masă (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 de 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 și suma sferturilor 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Nu avem nevoie de aceste informații, deci deselectăm aceste câmpuri pentru a le elimina din tabelul pivot.
"Cuantumul vânzărilor" (vânzările totale) este pertinent, totuși, deci vom rezolva asta.
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 format orice alte celule. Primul clic pe "Suma vânzărilor" și selectați "Setări câmp valoare".
În dialogul care rezultă, schimbăm numele în "Vânzări totale", apoi dă clic pe "Formatul numărului" și îl schimbi în "Valută".
Apoi puteți vedea lucrarea dvs. în pivot:
Acum, să adăugăm subtotaluri pentru fiecare trimestru. Pentru a adăuga subtotal doar faceți clic stânga pe "Quarter" câmp și țineți-l și glisați-l la "rânduri" secțiune. Puteți vedea rezultatul în imaginea de mai jos:
În timp ce ne aflăm la aceasta, să eliminăm valorile "Sum of Quarter". Trebuie doar să dați clic pe săgeată și să dați clic pe "Eliminați câmpul". În captura de ecran puteți vedea acum că am adăugat rândurile "Quarter", care descompune vânzările fiecărui vânzător pe trimestru.
Cu aceste abilități proaspete în minte, puteți crea acum tabele pivot de la propriile date!
Î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 din nou și să luați din nou clasa, puteți începe proaspăt cu Lecția 1!
