Functia pmt si what if analysis – DATATABLE alternativa la functia pmt

Trei video tutoriale la rand :

Excel Magic Trick 253: Data Table 1 Variable What If Analysis

254,

255

Highline excel 24 vorbeste despre order of precedents.

Formula inputs of assumptions table, Scenario Manager,Scenario Report, Adaugare Buton Scenario,Excel Goal Seek

Scenario Manager- stocheaza mai multe variabile.

Fie tabela de Vanzari si Assumptions:

Din chelt_unu se selecteaza range-ul de celule se apasa butonul de sum se sterge si se introduce formula data de semnul egal ca in chelt_unu.jpg de mai sus.

Apoi vom apasa tasta ctrl si enter pentru a popula relativ toate celule cu o referinta mixta de celule efectul fiind dat mai jos:

Calculam acum net income/veniturile vor fi date mereu de diferenta dintre vanzari si totalul cheltuielilor:

Daca schimbam valorile din assumptions table si valorile din vanzari si cheltuieli lunare, precum si venituri se vor schimba desigur.

Accesam scenario manager:

Desigur se poate modifica in tabela assumptions valorile si adauga un nou set numit de exemplu Set2 urmand aceiasi pasi din printscreen-urile de mai sus.

Valorile vor fi updatate in momentul in care alegem Set2 si dam clic Show va updata setul ales din Scenario manager.

Am creat trei scenarii in Scenario Manager:

Acum vom adauga un buton numit Add Scenario Button:

Clic dreapta pe ribbon:

In continuare vom adauga un raport plecand de la cele trei seturi create in Scenario Manager:

Excel Goal Seek:

trebuie sa avem intr-o celula o formula, si valoarea finala a formulei care dorim sa o schimbam a.i unul din parametri formulei sa fie updatat automat. Cam la aceasta e utila what if analysis goal seek:

YTLE#103: Excel Goal Seek


Data Analysis Pivot Tables

Inainte de a completa tabelul formatam celulele ca accounting cu trei zecimale selectand celulele dand clic dreapta di alegand format cells si completam apoi conform printscreen-ului de mai jos:

Vom porni de la dataset-ul anterior de la subtotal si trebuie sa avem in minte aceasta imagine de mai jus inainte de a crea un tabel pivot:

Cu ajutorul unui tabel pivot vom sumariza Vanzarile pentru fiecare Reprezentant de vanzari in parte.

Inserarea unui pivot table:

Ca sa inserati un pivot table aveti doua metode:

Metoda_unu: Din meniu:


Metoda_doi:  Combinatia de taste alt,d si p si dati next next next.

Cum inseram un pivot table metoda_unu:

Daca aveti data dragati la row label si daca nu vreti toate datele clic dreapta si alegeti group si permiteti sa fie afisate in pivot table doar anumite date cuprinse intr-un interval.

Highline Excel Class 20: Pivot Tables 20 Examples

 

Data Analysis Subtotals

Fie tabelul de mai jos:

Vom ordona crescator dupa coloanele Regiune si Reprezentant vanzari :

Acum facem subtotal dupa coloana Regiune -facem totaluri folosind in cazul de mai jos functia sum la fiecare Regiune deci noi vom vedea vanzarile dupa Regiune :


Apoi facem subtotal dupa coloana Reprezentant vanzari:



Cand faceti al doilea subtotal e bine sa debifati Replace Current Subtotals lucru care eu nu l-am facut si am inlocuit vechiul subotal facut dupa regiune.

Subtotal – Remove all – stergeti subtotals facute.

Tasta F5 Special si listati doar celulele vizibile cand vreti sa copiati outline la subtotal intr-un alt worksheet separat.

Sau din meniul:

Highline Excel Class 18: Subtotals



Adaugarea unui filtru/filtru avansat in excel

Fie tabelul:

CTRL si L scurtatura ca sa stocam datele pentru tabel. Acelasi efect are si daca apasam combinatia de taste CTRL si T.

CTRL si SHIFT si sageata jos/sus daca vrem sa selectam toate inregistrarile dintr-o coloana rapid.

Dataset se numeste un set de date care alcatuieste o lista/tabel. Tabelul se numeste lista in excel.

Efect:

Sa facem un filtru dupa coloana Regiunea Brasov ; el imi va ascunde celelalte inregistrari care nu se incadreaza in cautare:


Ca efect o sa-mi arate doar acele inregistrari dupa criteriul de cautare al Regiunii care este Brasov:

Ca sa inlaturati filtrul dupa regiune:

Dorim sa facem un filtru numeric dupa coloana COGS:

Stabilim criteriul >500:

Se urmeaza aceeasi etapa pentru stergerea filtrului.

Filtru care arata primele zece inregistrari:

Se alege filtru top ten.

Pot filtra si dupa culoare folosind Filter by color.

Deasemenea si sortarile se pot face dupa criterii de culoare a celulei, ascendent,desecendent etc.

Crearea unui filtru avansat – ADVANCED FILTER:

Se specifica range-ul celulelor,criteria range aleg A3-D3 si la copy celles to aleg celula unde sa-mi faca

filtrul avansat:



Mai multe detalii despre filtre avansate aici:

Highline Excel Class 19: Advanced Filter Extract Data 9 Examples


Despre filtre obisnuite gasiti la video-uri youtube acest tutorial:

Highline Excel Class 17: Filter, Filtering 8 Examples


Data analysis sortare coloane tabela

Fie urmatoarea tabela:

Exista mai multe feluri in care putem sorta:

a.  Selectam o celula dintr-o coloana si ordonam crescator sau descrescator dam clic dreapta:

b. Navigam in tabul Data:

c. Clic pe butonul Sort din tabul Data:

Selectam o celula din coloana COGS, o sortam ascending/crescator, la fel facem si cu o celula din coloana Client. de la dreapta la stanga sortam una cate una coloanele ca sa nu existe confuzii.

Tabelul de mai jos va avea sortate crescator cele 2 coloane COGS,si Client :

De fapt noi sortam crescator preturile platite pe produse in ordinea crescatoare a clientilor coloanele sortate crescator sunt marcate cu rosu.

Sortare de la stanga la dreapta:

Similar se face si cu row2 numai ca se alege la ultima optiune in loc de largest to smallest se alege Z to A, deoarece ordonam descrescator string-uri in row2 nu numere ca in cazul row1.


Highline Excel Class 16: Sort, Sorting 10 Examples