Analiza comparativă: concept, tipuri și exemple. Calculul KPI în exemple și formule Excel Realizat pe mai multe criterii simultan

Subiect: Luarea deciziilor pe mai mulți indicatori de criterii.

În practică, este de obicei necesar să alegeți o decizie de management nu după un criteriu, ci după mai multe. Prin urmare, valorile lor într-o evaluare comparativă sunt multidirecționale, adică. pe un indicator, alternativa câștigă, iar pe alții pierde.

În aceste condiții, este necesar să se reducă sistemul considerat de evaluare a indicatorilor la unul complex, pe baza căruia se va lua o decizie.

Pentru a construi o evaluare cuprinzătoare, trebuie rezolvate două probleme:

Prima problemă este că indicatorii de criterii luați în considerare sunt de semnificație inegală;

A doua problemă este caracterizată de faptul că indicatorii sunt evaluați în diferite unități de măsură, iar pentru a construi o evaluare cuprinzătoare, este necesar să treceți la un singur contor.

Prima problemă se rezolvă prin aplicarea uneia dintre cele patru modificări ale metodei evaluărilor experților, și anume metoda comparației perechi, care ne permite să cuantificăm semnificația. Esența metodei de comparare în perechi este că un expert (specialist, potențial investitor, consumator) efectuează o evaluare în perechi a indicatorilor de criterii luați în considerare, determinând el însuși gradul lor de importanță sub forma unui scor. După aceea, după efectuarea procesării corespunzătoare a informațiilor primite, se calculează coeficientul de semnificație pentru fiecare dintre indicatorii de criterii luați în considerare.

A doua problemă este rezolvată prin utilizarea unui singur contor pentru indicatoarele private. Cel mai adesea, un punctaj este folosit ca un astfel de metru. În acest caz, evaluarea se realizează în două abordări:

- prima abordare utilizat în lipsa datelor statistice privind valoarea indicatorilor luați în considerare;

- a doua abordare utilizat în prezența datelor statistice (limite de modificare) asupra valorii indicatorilor luați în considerare.

Când utilizați prima abordare pentru a converti în puncte, se face următoarele: cea mai bună valoare a indicatorului luat în considerare este egală cu 1 punct, iar cele mai slabe valori sunt în fracțiuni din acest punct. Această abordare este simplă, oferă o evaluare obiectivă, dar în același timp nu ține cont de cele mai bune realizări care se află în afara opțiunilor luate în considerare.

Pentru a elimina acest neajuns, sunt necesare informații cu privire la limitele de modificare a indicatorului luat în considerare. Dacă este disponibilă, se utilizează a doua abordare. În acest caz, o scară de conversie este construită pentru a converti în puncte. În acest caz, sistemul de notare este selectat folosind prevederile teoriei statisticii conform formulei Sturges:

n = 1 + 3,322 lg N , Unde

N este numărul de observații statistice;

n este sistemul de notare acceptat obținut folosind regulile de rotunjire.

Conversia în puncte se realizează pe baza scalei de conversie construită utilizând procedura de interpolare a datelor tabelare.

Exercițiu:

Dintre cele 6 opțiuni pentru soluții alternative, fiecare dintre acestea fiind evaluată de 5 indicatori de criterii, este necesar să alegeți cea mai bună opțiune.

Evaluați folosind 2 abordări:

1) în lipsa datelor statistice privind valoarea indicatorilor luați în considerare;

2) dacă este disponibil.

Limitele de modificare a indicatorilor sunt stabilite pentru următorul număr de observații (N):

Pentru variantele pare N = 8;

Evaluarea semnificației trebuie efectuată pe baza unei evaluări în pereche în funcție de interpret.

Tabelul 1.

Opțiuni de sarcină

numărul locului de muncă	1	2	3	4	5
Nr de alternative	1,2,3,4,5,6	2,4,8,9,11,15	1,3,5,7,9,10	4,6,8,12,13,14	1,5,10,11,12,15
numărul locului de muncă	6	7	8	9	10
Nr de alternative	6,7,10,11,14,15	3,4,5,8,9,10	7,8,9,10,13,15	1,2,3,13,14,15	2,4,5,7,12,13
numărul locului de muncă	11	12	13	14	15
Nr de alternative	1,7,8,9,10,11	6,9,12,13,14,15	2,5,7,9,10,11	7,8,9,10,11,12	1,2,3,4,8,9
numărul locului de muncă	16	17	18	19	20
Nr de alternative	1,2,3,10,12,13	2,5,7,8,10,15	1,6,7,12,13,14	3,4,5,6,10,14	2,8,11,12,14,15
numărul locului de muncă	21	22	23	24	25
Nr de alternative	1,2,6,7,9,10	3,5,8,9,13,14	4,7,8,10,11,12	5,6,7,8,11,13	8,9,10,11,12,13
numărul locului de muncă	26	27	28	29	30
Nr de alternative	1,3,4,10,11,15	2,3,5,8,9,15	1,4,7,11,13,15	2,6,7,8,12,14	1,10,11,12,8,4

Masa 2.

Date inițiale:

№№	Soluții alternative
indicatori	A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11	A12	A13	A14	A15
X 1	5	10	15	6	11	16	7	14	18	20	19	8	21	13	10
X 2	10	9	8	8	5	7	4	9	5	8	7	7	6	3	2
X 3	4	3	5	10	6	5	11	7	7	9	8	12	8	5	9
X 4	1	2	3	4	4	3	2	1	1	3	4	2	2	4	3
X 5	10	14	13	11	12	20	21	23	17	18	19	24	22	16	18

Tabelul 3

Exemplu:

Sunt prezentate patru variante de soluții alternative, fiecare dintre acestea fiind evaluată prin 5 indicatori criterii. Pe baza condițiilor sarcinii, este necesar să alegeți cea mai bună opțiune.

În prima etapă, este necesară cuantificarea semnificației fiecărui indicator. Se folosește metoda de comparare pereche, care se bazează pe evaluări ale experților.

Pe baza acestei evaluări, se întocmește un tabel - o matrice și se calculează coeficientul de semnificație -Kзi.

Evaluarea cantitativă a semnificației indicatorilor se determină astfel: dacă, într-o evaluare de pereche, un expert (specialist, potențial investitor, consumator) a dat preferință unuia dintre factori, atunci numărul factorului care este preferat se pune în rândul și coloana matricei de evaluare cantitativă (vezi Tabelul 4). După aceea, pentru fiecare linie, se determină numărul de preferințe acordate unuia sau altuia factor în evaluarea lor pe perechi și suma lor (Σпi). Apoi coeficientul de semnificație se calculează după următoarea formulă:

Cuantificarea semnificației indicatorilor:

Tabelul 4

X1	x2	X3	X4	X5	ΣPi		Kzi
X1	1	1	3	1	5	3		0,2
x2	1	2	2	2	5	3		0,2
X3	3	2	3	4	5	2		0,133
X4	1	2	4	4	5	2		0,133
X5	5	5	5	5	5	5		0,333
∑∑Pi					15	1

Prima abordare.

Prima abordare a conversiei în puncte se caracterizează prin faptul că cea mai bună valoare a indicatorului este luată egală cu 1 punct, cea mai proastă este estimată în fracțiuni din acest punct. Această abordare este simplă, oferă o evaluare comparativă obiectivă, dar ia în considerare cele mai bune realizări care nu sunt incluse în opțiunile comparative.

Cod index	Scor în puncte				Kzi	Scor în puncte ținând cont de Ksi
	A1	A2	A3	A4		A1	A2	A3	A4
X1	0,3	0,35	0,7	1	0,2	0,06	0,07	0,14	0,2
x2	0,89	0,45	1	0,89	0,2	0,178	0,09	0,2	0,178
X3	0,91	1	0,64	0,82	0,133	0,121	0,133	0,085	0,110
X4	0,25	0,5	1	0,33	0,133	0,033	0,066	0,133	0,043
X5	1	0,52	0,48	0,61	0,333	0,333	0,173	0,159	0,203
Evaluare cuprinzătoare					0,725	0,532	0,717	0,73 4

De exemplu: Х1А1: 6/20=0,3

Х2А1: 8/9=0,89

Concluzie: folosind prima abordare, cea mai bună opțiune dintre alternative va fi opțiunea A4, deoarece are cea mai înaltă evaluare cuprinzătoare. Urmează opțiunile A1, A3, A2.

A doua abordare.

Elimină deficiențele primei abordări, dar utilizarea acesteia necesită informații cu privire la limitele de modificare a indicatorului luat în considerare. În același timp, este construită o scară de conversie pentru conversia în puncte. Sistemul de notare este ales pe baza prevederilor teoriei statisticii și depinde de numărul de observații care stau la baza formării limitelor de modificare a indicatorilor.

Să presupunem că, în exemplul nostru, au fost făcute 8 observații (N=8), care au făcut posibilă stabilirea următoarelor limite pentru modificarea indicatorilor calitativi (vezi Tabelul 3).

În prezența acestor indicatori, se construiește o scară de conversie în puncte.

- formula Sturges,

unde N este numărul de observații.

In consecinta, evaluarea indicatorului de calitate se va face dupa un sistem in 4 puncte, i.e. n = 4.

- gama de variatii,

unde - valorile maxime și minime din limitele de modificare a indicatorului i.

Etapa de schimbare a indicatorului.

Scara de conversie în puncte este un tabel în care pentru fiecare punct sunt indicate limitele de schimbare a indicatorilor. La traducerea valorilor indicatorilor în puncte de pe această scară, dacă valoarea indicatorului se află în interval, atunci se utilizează procedura de interpolare a datelor tabelare.

Scara de conversie în puncte

În continuare, indicatorii de calitate ai tuturor produselor sunt evaluați în puncte. De exemplu, conform alternativei A1: valoarea numerică a indicatorului este luată din datele inițiale, apoi folosind scara de conversie în puncte, se determină intervalul în care această valoare scade. După aceea, se acordă un punctaj: din valoarea numerică a indicatorului, limita inferioară a modificării indicatorului în acest interval se scade cu un pas și se adaugă intervalul anterior. Conform indicatorilor X4, X5 - de la limita superioară a modificării indicatorului în acest interval, valoarea numerică a indicatorului este împărțită la un pas și se adaugă intervalul anterior.

Valorile obținute sunt rezumate în tabelul următor.

indicator	Scor în puncte				Kzi	Scor în puncte ținând cont de Ksi
	A1	A2	A3	A4		A1	A2	A3	A4
X1	0,2	0,4	1,8	3	0,2	0,04	0,08	0,36	0,6
x2	3	1	3,5	3	0,2	0,6	0,2	0,7	0,6
X3	2,33	2,66	1,33	2	0,134	0,313	0,357	0,179	0,268
X4	0	2,34	4	1,67	0,134	0	0,314	0,536	0,224
X5	3,04	1,44	1,12	1,92	0,334	1,02	0,481	0,374	0,642
Evaluare cuprinzătoare					1,973	1,432	2,149	2,334

Concluzie: folosind a doua abordare, cea mai bună opțiune dintre alternative va fi opțiunea A4, deoarece are cea mai înaltă evaluare cuprinzătoare. Urmează opțiunile A3, A2, A1.

Acesta este un capitol dintr-o carte: Michael Girvin. Ctrl+Shift+Enter. Stăpânirea formulelor matrice în Excel.

Selectări bazate pe una sau mai multe condiții. O serie de funcții Excel utilizează operatori de comparație. De exemplu, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF și AVERAGEIFS. Aceste funcții fac selecții bazate pe una sau mai multe condiții (criterii). Problema este că aceste funcții pot doar adăuga, număra și medie. Și dacă doriți să impuneți condiții pentru căutare, de exemplu, valoarea maximă sau abaterea standard? În aceste cazuri, deoarece nu există nicio funcție încorporată, trebuie să inventați o formulă matrice. Adesea, acest lucru se datorează utilizării operatorului de comparare a matricei. Primul exemplu din acest capitol arată cum se calculează valoarea minimă într-o singură condiție.

Să folosim funcția IF pentru a selecta elementele unui tablou care îndeplinesc o condiție. Pe fig. 4.1 în tabelul din stânga există o coloană cu numele orașelor și o coloană cu ora. Este necesar să găsiți timpul minim pentru fiecare oraș și să plasați această valoare în celula corespunzătoare din tabelul din dreapta. Condiția de selecție este numele orașului. Dacă utilizați funcția MIN, puteți găsi valoarea minimă a coloanei B. Dar cum selectați numai acele numere care se aplică numai Auckland? Și cum copiați formulele în coloană? Deoarece Excel nu are o funcție MINESLI încorporată, trebuie să scrieți o formulă originală care combină funcțiile IF și MIN.

Orez. 4.1. Scopul formulei: pentru a selecta timpul minim pentru fiecare oraș

Descărcați nota în format sau în format

După cum se arată în fig. 4.2, ar trebui să începeți să introduceți formula în celula E3 cu funcția MIN. Dar nu poți să te dispute Numărul 1 toate valorile coloanei B!? Doriți să selectați numai acele valori care sunt legate de Auckland.

După cum se arată în fig. 4.3, în pasul următor, introduceți funcția IF ca argument Numărul 1 pentru MIN. Puneți un IF în interiorul unui MIN.

Prin poziționarea cursorului în punctul în care este introdus argumentul expresie_log funcția IF (Fig. 4.4), selectați intervalul cu numele orașelor A3:A8, apoi apăsați F4 pentru a face referințele de celule absolute (vezi, de exemplu, pentru mai multe detalii). Apoi tastați operatorul comparativ, semnul egal. În cele din urmă, veți selecta celula din stânga formulei, D3, lăsând referința la aceasta relativă. Condiția formulată vă va permite să selectați numai Aucklands atunci când vizualizați intervalul A3:A8.

Orez. 4.4. Creați un operator de matrice într-un argument expresie_log funcțiile IF

Deci ați creat un operator de matrice cu un operator de comparație. În orice moment în timpul procesării matricei, operatorul matrice este un operator de comparație, astfel încât rezultatul său va fi o matrice de valori TRUE și FALSE. Pentru a verifica acest lucru, selectați matricea (pentru a face acest lucru, faceți clic pe argumentul din sfatul cu instrumente expresie_log) și apăsați F9 (Fig. 4.5). De obicei folosești un singur argument expresie_log, returnând fie TRUE, fie FALSE; aici, matricea rezultată va returna mai multe valori TRUE și FALSE, astfel încât funcția MIN va selecta numărul minim doar pentru acele orașe care se potrivesc cu valoarea TRUE.

Orez. 4.5. Pentru a vedea o serie de valori TRUE și FALSE, faceți clic pe argumentul din sfatul cu instrumente expresie_logși apăsați F9

În a doua parte a tutorialului nostru despre funcție CĂUTARE V(CĂUTARE V) în Excel, vom analiza câteva exemple care vă vor ajuta să direcționați toată puterea CĂUTARE V pentru a rezolva cele mai ambițioase sarcini Excel. Exemplele presupun că aveți deja cunoștințe de bază despre cum funcționează această caracteristică. Dacă nu, ați putea fi interesat să începeți cu prima parte a acestui tutorial, care explică sintaxa și utilizarea de bază. CĂUTARE V. Ei bine, să începem.

Căutați în Excel după mai multe criterii

Funcţie CĂUTARE Vîn Excel este un instrument foarte puternic pentru a efectua căutări pentru o anumită valoare într-o bază de date. Cu toate acestea, există o limitare semnificativă - sintaxa sa vă permite să căutați o singură valoare. Ce se întâmplă dacă doriți să căutați după mai multe condiții? Soluția o vei găsi mai jos.

Exemplul 1: Căutați după 2 criterii diferite

Să presupunem că avem o listă de comenzi și vrem să găsim Cantitatea de mărfuri(Cant.), pe baza a două criterii - Numele clientului(Client) și Numele produsului(Produs). Problema este complicată de faptul că fiecare dintre cumpărători a comandat mai multe tipuri de mărfuri, după cum se poate observa din tabelul de mai jos:

funcție obișnuită CĂUTARE V nu va funcționa în acest scenariu, deoarece va returna prima valoare pe care o găsește care se potrivește cu valoarea de căutare dată. De exemplu, dacă doriți să aflați cantitatea unui articol Dulciuri comandat de cumparator Jeremy Hill, scrie următoarea formulă:

CĂUTARE V (B1, $A$5:$C$14,3,FALSE)
=CĂUTARE V (B1, $A$5:$C$14,3,FALSE)

- această formulă va returna rezultatul 15 corespunzătoare produsului merele, pentru că este prima valoare care se potrivește.

Există o soluție simplă - creați o coloană suplimentară în care să combinați toate criteriile dorite. În exemplul nostru, acestea sunt coloanele Numele clientului(Client) și Numele produsului(Produs). Nu uitați că coloana îmbinată trebuie să fie întotdeauna coloana din stânga din intervalul de căutare, deoarece funcția este coloana din stânga CĂUTARE V ridică privirea când caută o valoare.

Deci, adăugați o coloană auxiliară la tabel și copiați următoarea formulă peste toate celulele sale: =B2&C2. Dacă doriți ca șirul să fie mai lizibil, puteți separa valorile combinate cu un spațiu: =B2&” „&C2. După aceea, puteți utiliza următoarea formulă:

CĂUTARE V ("Jeremy Hill Sweets", $A$7:$D$18,4,FALSE)
=CĂUTARE(„Jeremy Hill Sweets”, $A$7:$D$18,4,FALSE)

CĂUTARE V (B1, $A$7:$D$18,4,FALSE)
=CĂUTARE V (B1, $A$7:$D$18,4,FALSE)

Unde este celula B1 conține valoarea concatenată a argumentului valoare_căutare(lookup_value) și 4 - argument col_index_num(număr_coloană), adică numărul coloanei care conține datele de preluat.

Exemplul 2: CĂUTARE V după două criterii, tabelul fiind vizualizat pe o altă foaie

Dacă trebuie să actualizați tabelul principal (Tabelul principal) adăugând date din al doilea tabel (Tabelul de căutare), care se află pe o altă foaie sau într-un alt registru de lucru Excel, atunci puteți colecta valoarea dorită direct în formula pe care o introduceți în masa principală.

Ca și în exemplul anterior, veți avea nevoie de o coloană auxiliară în tabelul de căutare cu valorile combinate. Această coloană trebuie să fie coloana din stânga din intervalul de căutare.

Deci formula cu CĂUTARE V ar putea fi asa:

CĂUTARE V(B2&" "&C2,Comenzi!$A&$2:$D$2,4,FALSE)
=CĂUTAREV(B2&" "Comenzi!$A&$2:$D$2;4;FALSE)

Aici, coloanele B și C conțin numele clienților și, respectiv, numele produselor și linkul Comenzi!$A&$2:$D$2 definește un tabel de căutat într-o altă foaie.

Pentru a face formula mai lizibilă, puteți da un nume intervalului de vizualizare, iar apoi formula va părea mult mai simplă:

CĂUTARE V(B2&" "&C2,Comenzi,4,FALSE)
=CĂUTAREV(B2&" "Comenzi;4;FALSE)

Pentru ca formula să funcționeze, valorile din coloana din stânga a tabelului vizualizat trebuie combinate în același mod ca și în criteriile de căutare. În figura de mai sus, am combinat valorile și am pus un spațiu între ele, așa cum trebuie să faceți în primul argument al funcției (B2&” “&C2).

Tine minte! Funcţie CĂUTARE V limitat la 255 de caractere, nu poate căuta o valoare care are mai mult de 255 de caractere. Țineți cont de acest lucru și asigurați-vă că lungimea valorii dorite nu depășește această limită.

Sunt de acord că adăugarea unei coloane auxiliare nu este soluția cea mai elegantă și nu întotdeauna acceptabilă. Puteți face același lucru fără coloana de ajutor, dar asta ar necesita o formulă mult mai complexă, cu o combinație de funcții INDEX(INDEX) și MECI(MECI).

Extragem al 2-lea, al 3-lea etc. valori folosind VLOOKUP

Știi deja asta CĂUTARE V poate returna o singură valoare potrivită, mai exact prima găsită. Dar dacă această valoare se repetă de mai multe ori în matricea vizualizată și doriți să extrageți a doua sau a treia dintre ele? Dacă toate valorile? Problema pare complicată, dar soluția există!

Să presupunem că o coloană a tabelului conține numele clienților (Numele clientului), iar cealaltă coloană conține produsele (Produs) pe care aceștia le-au cumpărat. Să încercăm să găsim al 2-lea, al 3-lea și al 4-lea articol achiziționat de un anumit client.

Cel mai simplu mod este să adăugați o coloană auxiliară înaintea coloanei Numele clientuluiși completați-l cu numele clienților cu numărul de repetare al fiecărui nume, de exemplu, John Doe1, John Doe2 etc. Vom face trucul cu numerotarea folosind funcția COUNTIF(COUNTIF), dat fiind că numele clienților sunt în coloana B:

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

După aceea, puteți utiliza funcția normală CĂUTARE V pentru a găsi comanda necesară. De exemplu:

• Găsim al 2-lea Dan Brown:

  CĂUTARE V ("Dan Brown2", $A$2:$C$16,3,FALSE)
  =CĂUTARE(„Dan Brown2”, $A$2:$C$16,3,FALSE)

• Găsim al 3-lea articol comandat de client Dan Brown:

  CĂUTARE V ("Dan Brown3", $A$2:$C$16,3,FALSE)
  =CĂUTARE(„Dan Brown3”, $A$2:$C$16,3,FALSE)

Dacă doar cauți al 2-lea repetare, o puteți face fără coloana auxiliară creând o formulă mai complexă:

DACĂ EROARE(CĂUTARE V($F$2,INDIRECT(„$B$”&(POTRIVIRE($F$2,Tabel4,0)+2)&”:$C16”),2,FALSE),””)
=DACĂ EROARE(CĂUTAREV($F$2;INDIRECT(„$B$”&(POTIRE($F$2;Tabel4;0)+2)&":$C16");2;ADEVĂRAT);"")

In aceasta formula:

• $F$2- o celulă care conține numele cumpărătorului (este neschimbat, vă rugăm să rețineți - linkul este absolut);
• $B$- coloana Numele clientului;
• Tabelul 4- Masa ta (acest loc poate fi, de asemenea, o gamă obișnuită);
• 16 USD- celula finală a tabelului sau a intervalului dvs.

Această formulă găsește doar a doua valoare de potrivire. Dacă trebuie să extrageți repetările rămase, utilizați soluția anterioară.

Dacă aveți nevoie de o listă cu toate potrivirile - funcția CĂUTARE V acesta nu este un ajutor, deoarece returnează doar o valoare la un moment dat. Dar Excel are o funcție INDEX(INDEX), care poate face față cu ușurință acestei sarcini. Cum va arăta o astfel de formulă, veți afla în exemplul următor.

Recuperați toate repetările valorii dorite

Așa cum sa menționat mai sus, CĂUTARE V nu poate extrage toate valorile duplicate din intervalul scanat. Pentru a face acest lucru, aveți nevoie de o formulă ceva mai complexă, formată din mai multe funcții Excel, precum INDEX(INDEX), MIC(MIC) și RÂND(LINIA)

De exemplu, formula de mai jos găsește toate repetările valorii din celula F2 în intervalul B2:B16 și returnează rezultatul din aceleași rânduri din coloana C.

(=IFEROARE(INDEX($C$2:$C$16,MIC(DACĂ($F$2=B2:B16,RÂND(C2:C16)-1,""),RÂND()-3)),"") )
(=IFEROARE(INDEX($C$2:$C$16,MIC(DAC($F$2=B2:B16,RÂND(C2:C16)-1,"");RÂND()-3)),"") )

Introduceți această formulă matrice în mai multe celule adiacente, cum ar fi celulele F4:F8 după cum se arată în figura de mai jos. Numărul de celule trebuie să fie egal sau mai mare decât numărul maxim posibil de repetări ale valorii căutate. Nu uitați să faceți clic Ctrl+Shift+Enter pentru a introduce corect formula matricei.

Dacă sunteți interesat să înțelegeți cum funcționează, să ne aprofundăm puțin în detaliile formulei:

Partea 1:

DACĂ($F$2=B2:B16,RÂND(C2:C16)-1,"")
DACĂ($F$2=B2:B16;RÂND(C2:C16)-1;"")

$F$2=B2:B16– comparați valoarea din celula F2 cu fiecare dintre valorile din intervalul B2:B16. Dacă se găsește o potrivire, atunci expresia ȘIR (C2:C16)-1 returnează numărul liniei corespunzătoare (valoarea -1 vă permite să nu includeți linia antetului). Dacă nu există potriviri, funcția DACĂ(IF) returnează un șir gol.

Rezultatul funcției DACĂ(DACĂ) va exista o astfel de matrice orizontală: (1,"",3,"",5,"","","","","","","",12,"","", "" )

Partea 2:

RÂND ()-3
STRING()-3

Aici funcția RÂND(LINE) acționează ca un numărător suplimentar. Deoarece formula este copiată în celulele F4:F9, scădem numărul 3 de la rezultatul funcției pentru a obține valoare 1 într-o celulă F4(linia 4, scade 3) pentru a obține 2 într-o celulă F5(linia 5, scade 3) și așa mai departe.

Partea 3:

MIC(DACĂ($F$2=B2:B16,RÂND(C2:C16)-1,""),RÂND()-3))
MIC(DACĂ($F$2=B2:B16,RÂND(C2:C16)-1,"");RÂND()-3))

Funcţie MIC(MIC) revine al n-lea cea mai mică valoare din matricea de date. În cazul nostru, ce poziție (de la cea mai mică) să revină este determinată de funcție RÂND(LINIE) (vezi partea 2). Deci, pentru o celulă F4 funcţie MIC((matrice),1) se intoarce 1(cel mai mic) element de matrice, de ex. 1 . Pentru celulă F5 se intoarce al 2-lea cel mai mic element din matrice, adică 3 , și așa mai departe.

Partea 4:

INDEX($C$2:$C$16,MIC(DACĂ($F$2=B2:B16,RÂND(C2:C16)-1,""),RÂND()-3))
INDEX($C$2:$C$16;MIC(DACĂ($F$2=B2:B16;RÂND(C2:C16)-1;"");RÂND()-3))

Funcţie INDEX(INDEX) returnează pur și simplu valoarea unei anumite celule dintr-o matrice C2:C16. Pentru celulă F4 funcţie INDEX($C$2:$C$16,1) va reveni merele, Pentru F5 funcţie INDEX($C$2:$C$16;3) va reveni Dulciuriși așa mai departe.

Partea 5:

IFEROARE()
IFEROARE()

În cele din urmă, punem formula în interiorul funcției DACA EROARE(IFEROARE), deoarece este puțin probabil să fiți mulțumit de mesajul de eroare #N / A(#N/A) dacă numărul de celule în care este copiată formula este mai mic decât numărul de valori duplicate din intervalul care este vizualizat.

Căutare 2D după rând și coloană cunoscute

Efectuarea unei căutări 2D în Excel implică căutarea unei valori după un număr cunoscut de rând și coloană. Cu alte cuvinte, extrageți valoarea celulei la intersecția unui anumit rând și coloană.

Deci, să ne întoarcem la tabelul nostru și să scriem o formulă cu o funcție CĂUTARE V, care va găsi informații despre costul lămâilor vândute în luna martie.

Există mai multe moduri de a efectua o căutare 2D. Verifică opțiunile și alege-o pe cea care ți se potrivește cel mai bine.

Funcțiile VLOOKUP și MATCH

Puteți folosi o mulțime de funcții CĂUTARE V(CĂUTARE V) și MECI(MATCH) pentru a găsi valoarea la intersecția câmpurilor Numele produsului(șir) și Lună(coloana) a matricei în cauză:

CĂUTARE V(„Lămâi”, $A$2:$I$9,POTRIVIRE(„Mar”,$A$1:$I$1,0),FALSE)
=CĂUTARE(„Lămâi”, $A$2:$I$9,POTRIVIRE(„Mar”,$A$1:$I$1,0),FALSE)

Formula de mai sus este o funcție obișnuită CĂUTARE V, care caută o potrivire exactă a valorii „Lămâi” în celulele de la A2 la A9. Dar, deoarece nu știți în ce coloană sunt vânzările din martie, nu veți putea seta numărul coloanei pentru al treilea argument al funcției. CĂUTARE V. În schimb, se folosește funcția MECI pentru a defini această coloană.

MATCH(„Marte”, $A$1:$I$1,0)
MATCH(„Mar”;$A$1:$I$1;0)

Tradusă în limbajul uman, această formulă înseamnă:

• Caut personaje "Mar" - argument valoare_căutare(valoare_căutare);
• Privind în celulele de la A1 la I1 - argument matrice_căutare(matrice_căutare);
• Se returnează potrivirea exactă - argument tip_potrivire(tip_potrivire).

Folosind 0 în al treilea argument, spui funcții MECI căutați prima valoare care se potrivește exact cu valoarea pe care o căutați. Aceasta este echivalentă cu valoarea FALS(FALSE) pentru al patrulea argument CĂUTARE V.

Acesta este modul în care puteți crea o formulă de căutare bidirecțională în Excel, cunoscută și sub numele de căutare bidimensională sau căutare bidirecțională.

Funcția SUMPRODUCT

Funcţie SUMPRODUS(SUMPRODUCT) returnează suma produselor matricelor selectate:

SUMPRODUS(($A$2:$A$9="Lămâi")*($A$1:$I$1="Mar"),$A$2:$I$9)
=SUMPRODUS(($A$2:$A$9="Lămâi")*($A$1:$I$1="Mar");$A$2:$I$9)

Funcțiile INDEX și MATCH

În articolul următor voi explica aceste funcții în detaliu, așa că deocamdată puteți doar să copiați această formulă:

INDEX($A$2:$I$9,MATCH(„Lămâi”,$A$2:$A$9,0),MATCH(„Mar”,$A$1:$I$1,0))
=INDEX($A$2:$I$9;POTRIBUIRE(„Lămâi”;$A$2:$A$9;0);POTITARE(„Mar”;$A$1:$I$1;0))

Intervalele denumite și operatorul de intersecție

Dacă nu vă plac toate acele formule Excel complexe, s-ar putea să vă placă acest mod vizual și memorabil:

Când introduceți un nume, Microsoft Excel va afișa un tooltip cu o listă de nume care se potrivesc, la fel ca atunci când introduceți o formulă.

1. Clic introduce si verifica rezultatul

În general, oricare dintre metodele de mai sus alegeți, rezultatul unei căutări bidimensionale va fi același:

Folosind mai multe VLOOKUP într-o singură formulă

Se întâmplă ca tabelul principal și tabelul de căutare să nu aibă o singură coloană în comun, iar acest lucru vă împiedică să utilizați funcția obișnuită CĂUTARE V. Totuși, există un alt tabel care nu conține informațiile care ne interesează, dar are o coloană comună cu tabelul principal și tabelul de căutare.

Să aruncăm o privire la următorul exemplu. Avem un tabel principal cu o coloană SKU (nou), unde doriți să adăugați o coloană cu prețurile corespunzătoare dintr-un alt tabel. În plus, avem 2 tabele de căutare. Primul (Tabelul de căutare 1) conține numere actualizate SKU (nou)și numele produselor, iar al doilea (Tabelul de căutare 2) - numele produselor și numerele vechi SKU (vechi).

Pentru a adăuga prețuri din al doilea tabel de căutare la tabelul principal, trebuie să efectuați o acțiune cunoscută sub numele de dublu CĂUTARE V sau imbricate CĂUTARE V.

1. Scrieți o funcție CĂUTARE V, care găsește numele produsului în tabel tabelul de căutare 1 folosind SKU, ca valoare dorită:

   CĂUTARE V (A2, SKU_nou, 2, FALSE)
   =CĂUTAREV(A2;SKU_nou;2;FALSE)

   Aici New_SKU- interval denumit $A:$B in masa tabelul de căutare 1, A 2 - aceasta este coloana B, care conține numele mărfurilor (vezi imaginea de mai sus)

2. Scrieți o formulă pentru a introduce prețuri dintr-un tabel tabelul de căutare 2 bazate pe nume cunoscute de produse. Pentru a face acest lucru, lipiți formula pe care ați creat-o mai devreme ca valoare de căutare pentru noua funcție CĂUTARE V:

   CĂUTARE V (CĂUTARE V (A2, SKU_ nou, 2, FALSE), Preț, 3, FALSE)
   =CĂUTAREV(CĂUTAREV(A2,SKU_nou,2,FALSE),Preț,3,FALSE)

   Aici Preț- interval denumit $A:$C in masa tabelul de căutare 2, A 3 este coloana C care conține prețuri.

Figura de mai jos arată rezultatul returnat de formula creată de noi:

Înlocuirea dinamică a datelor din diferite tabele folosind VLOOKUP și INDIRECT

Mai întâi, să clarificăm ce înțelegem prin „Înlocuirea dinamică a datelor din tabele diferite” pentru a ne asigura că ne înțelegem corect.

Sunt situații când există mai multe foi cu date de același format și este necesară extragerea informațiilor necesare dintr-o anumită foaie, în funcție de valoarea care este introdusă într-o celulă dată. Cred că este mai ușor să explic asta cu un exemplu.

Imaginați-vă că aveți rapoarte de vânzări pentru mai multe regiuni cu aceleași produse și în același format. Doriți să găsiți cifrele de vânzări pentru o anumită regiune:

Dacă aveți doar două astfel de rapoarte, atunci puteți utiliza o formulă rușinos de simplă cu funcții CĂUTARE VȘi DACĂ(IF) pentru a selecta raportul dorit de căutat:

CĂUTARE V ($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=CĂUTAREV($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

• $D$2 este o celulă care conține numele produsului. Rețineți că folosim referințe absolute aici pentru a evita modificarea valorii de căutare atunci când copiați formula în alte celule.
• $D3 este o celulă cu numele regiunii. Folosim o referință de coloană absolută și o referință de rând relativă, deoarece intenționăm să copiem formula în alte celule din aceeași coloană.
• FL_SalesȘi CA_Vânzări– denumirile tabelelor (sau intervalelor denumite) care conțin rapoartele de vânzări corespunzătoare. Desigur, puteți utiliza numele obișnuite ale foilor și referințele la intervalul de celule, de exemplu „Foaie FL”!$A$3:$B$10, dar intervalele denumite sunt mult mai convenabile.

Cu toate acestea, atunci când există multe astfel de tabele, funcția DACĂ nu este cea mai buna solutie. În schimb, puteți utiliza funcția INDIRECT(INDIRECT) pentru a reveni la intervalul de căutare dorit.

După cum probabil știți, funcția INDIRECT este folosit pentru a returna un link dat de un șir de text, care este exact ceea ce avem nevoie acum. Deci, înlocuiți cu îndrăzneală în formula de mai sus expresia cu funcția DACĂ pentru a lega cu funcția INDIRECT. Iată o combinație CĂUTARE VȘi INDIRECT funcționează excelent cu:

CĂUTARE V ($D$2,INDIRECT($D3&"_Vânzări"),2,FALSE)
=CĂUTAREV($D$2;INDIRECT($D3&"_Vânzări");2;FALSE)

• $D$2- aceasta este o celulă cu numele produsului, este neschimbată din cauza legăturii absolute.
• $D3 este celula care conține prima parte a numelui regiunii. În exemplul nostru, aceasta FL.
• _Vânzări- partea comună a numelui tuturor intervalelor sau tabelelor denumite. Combinat cu valoarea din celula D3, formează numele complet calificat al intervalului necesar. Mai jos sunt câteva detalii pentru cei care sunt noi în funcție INDIRECT.

Cum funcționează INDIRECT și VLOOKUP

Mai întâi, permiteți-mi să vă reamintesc sintaxa funcției INDIRECT(INDIRECT):

• A1 dacă argumentul este ADEVĂRAT(ADEVĂRAT) sau nespecificat;
• R1C1, Dacă FALSE(MINCIUNĂ).

Deci, să revenim la rapoartele noastre de vânzări. Dacă vă amintiți, atunci fiecare raport este un tabel separat situat pe o foaie separată. Pentru ca formula să funcționeze corect, trebuie să vă denumiți tabelele (sau intervalele), iar toate numele trebuie să aibă o parte comună. De exemplu, așa: CA_Vânzări, FL_Vânzări, TX_Vânzăriși așa mai departe. După cum puteți vedea, toate numele conțin „_Sales”.

Funcţie INDIRECT conectează valoarea din coloana D și șirul de text „_Sales”, spunând astfel CĂUTARE Vîn ce tabel să caute. Dacă celula D3 conține valoarea „FL”, formula va căuta în tabel FL_Vânzări, dacă „CA” este în tabel CA_Vânzăriși așa mai departe.

Rezultatul funcțiilor CĂUTARE VȘi INDIRECT vor fi urmatoarele:

Dacă datele se află în diferite cărți Excel, atunci trebuie să adăugați numele cărții înaintea intervalului numit, de exemplu:

CĂUTARE V($D$2,INDIRECT($D3&„Carnet de lucru1!_Vânzări”),2,FALSE)
=CĂUTAREV($D$2;INDIRECT($D3&"Workbook1!_Vânzări");2;FALSE)

Dacă funcţia INDIRECT se referă la un alt registru de lucru, acel registru de lucru trebuie să fie deschis. Dacă este închis, funcția va raporta o eroare. #ref!(#REF!).

Să presupunem că doriți să însumați valori cu mai mult de o condiție, cum ar fi suma vânzărilor unui produs într-o anumită regiune. Acesta este un caz de utilizare bun pentru funcția SUMIFS într-o formulă.

Atenție la acest exemplu în care avem două condiții: este necesară cantitatea de vânzări carne(din coloana C) la Sud zona (din coloana a).

Iată o formulă care poate fi folosită pentru a realiza:

=SUMIFS(D2:D11; a2: A11; „Sud”; C2: C11; „carne”)

Rezultatul este valoarea 14.719.

Luați în considerare o reprezentare mai detaliată a fiecărei părți a formulei.

SUMIFS este o formulă aritmetică. Calculează numerele, care în acest caz sunt în coloana D. Primul pas este să specificați locația numerelor.

=SUMIFS(D2:D11,

Cu alte cuvinte, doriți ca formula să însumeze numerele din acea coloană dacă îndeplinesc anumite condiții. Acest interval de celule este primul argumentîn această formulă, primul element de date pe care funcția îl ia ca intrare.

Apoi trebuie să găsiți datele care îndeplinesc cele două condiții și să introduceți prima condiție, specificând locația datelor (a2:A11) pentru funcție, precum și care este condiția „Sud”. Observați virgulele dintre argumentele individuale.

=SUMIFS(D2:D11;A2:A11;„Sud”;

Ghilimelele din jurul cuvântului „Sud” indică faptul că acestea sunt date text.

În cele din urmă, introduceți argumente pentru a doua condiție - intervalul de celule (C2:C11) care conțin cuvântul „Carne” și cuvântul în sine (închis între ghilimele), astfel încât Excel să le poată potrivi. Încheiați formula închizând paranteza de închidere ) , apoi apăsați Enter. Rezultatul este din nou la 14.719.

=SUMIFS(D2:D11;A2:A11;„Sud”, C2:C11, „Carne”)

Pe măsură ce tastați funcția SUMIFS în Excel, dacă nu vă amintiți aceste argumente, Ajutorul este gata. După introducerea formulei = SUMIFS ( Completarea automată a formulei va apărea sub formulă, iar lista de argumente va fi listată în ordinea corectă.

Aruncă o privire la imaginea formulelor de autocompletare și a listei de argumente din exemplul nostru interval_sumă este D2:D11, coloana numerelor de însumat. intervalul_criterii1- a2. A11 - coloana de date care contine condiția 1"Sud".

Pe măsură ce tastați formula, restul argumentelor (nu sunt afișate aici) vor apărea în completarea formulei; interval_condiție2 este intervalul C2:C11, care este coloana de date în care condiția 2- „Carne”.

Dacă faceți clic SUMIFS completarea formulei va deschide un articol de unde puteți obține mai multe informații.

Încearcă să exersezi

Dacă doriți să experimentați cu funcția SUMIFS, iată câteva exemple de date și o formulă care utilizează funcția.

În acest registru de lucru Excel pentru web, puteți lucra cu exemple de date și formule chiar aici. Schimbați valorile și formulele sau adăugați-le pe ale tale pentru a vedea cum se schimbă instantaneu rezultatele.

Copiați toate celulele din tabelul de mai jos și lipiți-le în celula A1 a unei noi foi Excel. Puteți ajusta lățimea coloanelor pentru a face formulele să se afișeze mai bine.

Regiune	Vanzator		Vânzări
Vest
Oriental	Pesotsky
De Nord	Pesotsky	Lactat
	Marinova
Oriental	Pesotsky
De Nord

În acest articol, veți afla despre 5 proprietăți utile ale tabelelor pivot care vă vor ajuta să analizați rapid și în detaliu situația (folosind exemplul analizei bazei de clienți). O sa inveti:

1. Cum se grupează datele;
2. Ce indicatori pot fi calculați la rezumarea datelor;
3. Cum se calculează simultan mai mulți indicatori pentru un parametru atunci când rezumăm datele;
4. Ce opțiuni de calcul suplimentare puteți utiliza atunci când rezumați datele?
5. Despre posibilitatea de sortare.

Și pe baza acestei analize, vom atinge cea mai puternică tehnică de planificare a promoțiilor de vânzări pe piețele FMCG.

Să începem cu un tabel pivot. Să luăm un tabel simplu de vânzări către clienți pe zi.

Plasați cursorul în colțul din stânga sus al tabelului nostru, apoi accesați meniul „Insert” și faceți clic pe butonul „Pivot Table”:

În caseta de dialog Creare tabel pivot, faceți clic pe OK:

Avem un tabel pivot pe o foaie nouă:

Prima caracteristică utilă a unui tabel pivot pentru analiza afacerii - gruparea datelor

Deci, avem expedieri către clienți pe zi, vrem să înțelegem în ce gamă de expedieri avem vânzări maxime. Pentru a face acest lucru, trebuie să grupăm expedierile în intervale.

Trageți câmpul „Shipping value” în zona „Line names” a tabelului pivot (ține apăsat câmpul „Shipping_amount” cu butonul stâng al mouse-ului și trage-l în secțiunea „Line names” a tabelului pivot):

Am afișat toate expedierile în coloana din stânga a rezumatului. Acum setăm cursorul la expedierile noastre (ca în figură):

Accesați meniul Excel „Date” și faceți clic pe butonul „Grup”.

În caseta de dialog care apare, setați pasul de grupare la „5000” (puteți introduce oricare) și faceți clic pe „OK”

Obținem volume de vânzări grupate cu un anumit pas:

Pentru a grupul arăta frumosși perceput, apăsați din nou butonul „Grup”.și setați manual valori egale, pentru valoarea „începând de la” - „-15.000” (sub valoarea minimă, un multiplu de 5000) „la” - „45.000” (mai mare decât grupul maxim, un multiplu de 5000).

Primim date grupate în funcție de cantitatea de expediere:

A doua caracteristică utilă a tabelelor pivot pentru analiza afacerii -
capacitatea de a calcula diferiți parametri rezumativi pe câmpuri din tabelul sursă

Deci, transporturile au fost grupate, acum să vedem ce volume de vânzări se încadrează pe fiecare gamă de transporturi. Pentru a face acest lucru, să însumăm expedierile în rezumat.

Faceți clic stânga pe câmpul „Shipping_amount” și trageți-l în câmpul rezumat „Valori”:

Rezumatul a calculat implicit „Cantitate după câmp Shipment_amount”, adică. numărul de înregistrări din tabelul nostru original de pe foaia „Date”. Deoarece Deoarece tabelul nostru conține informații despre vânzările către clienți pe zi, indicatorul nostru „Cantitate în câmpul Shipment_amount” este numărul de expedieri către clienți.

Ca rezultat, în tabelul pivot vedem numărul de expedieri către clienți în diferite intervale de expediere:

Cum putem obține cantitatea de expedieri din numărul de expedieri?

Facem clic stânga pe câmpul „Cantitate după câmp Valoarea expedierii” din zona tabelului pivot „Valori”, iar în meniul care se deschide, selectăm „Parametrii câmpurilor valorice...”

În fereastra care se deschide, selectați operația de reducere a datelor care ne interesează (Suma, cantitate, medie, maximă, minimă...). Selectați operația de care avem nevoie „suma” și faceți clic pe „OK”.

Obținem volumul total de vânzări pentru fiecare interval de expediere:

Acestea. vedem câte vânzări scad la expedieri în intervalul de la 0 la 5.000 de ruble, de la 5.000 la 10.000 de ruble. etc. Și este clar că volumul maxim al transporturilor se încadrează în intervalul de la zero la 5000 de ruble.

3 proprietate - capacitatea unui câmp de a calcula diferite operațiuni de reducere a datelor

Acum am dori să vedem câte transporturi și câte livrări medii avem în fiecare dintre intervale. Pentru a face acest lucru, folosim tabelul pivot pentru a calcula numărul de expedieri și transporturile medii.

În zona tabelului pivot „Valori” tragem câmpul „Shipping_amount” de încă 2 ori, iar în parametrii câmpului de valoare pentru al doilea selectam „cantitate”, iar pentru al treilea câmp selectam „medie”. ".

Obținem pentru fiecare gamă de expedieri volumul vânzărilor, numărul de expedieri și transportul mediu:

Acum putem vedea în ce interval de expedieri volumul maxim de vânzări și numărul maxim de expedieri. În exemplul nostru, acesta este pentru intervalul de la 0 la 5000 de ruble. și pe cât posibil volumul vânzărilor și numărul de expedieri.

A patra proprietate a tabelelor pivot - capacitatea de a efectua calcule suplimentare

Pentru claritatea analizei datelor, haideți să adăugăm încă 2 parametri - „Partajare în funcție de volumul vânzărilor pentru fiecare grup” și „Proporția numărului de expedieri pentru fiecare grup”.

Pentru a face acest lucru, în câmpul tabelului pivot „Valori” trageți câmpul „Suma de livrare” de încă 2 ori

Mai mult, pentru un parametru din meniul „Parametrii câmpului de valori” () vom selecta operația „suma”, iar pentru a doua operație „cantitate”.

Obținem următorul tabel:

Acum mergem din nou la „Parametrii câmpurilor de valoare” și intrăm în fila „Calcule suplimentare”:

Selectați în câmpul „Calcule suplimentare” articolul „Cota din suma totală”

Obținem un tabel în care pentru fiecare gamă de expedieri către clienți vedem volumul vânzărilor, numărul de expedieri, livrarea medie, ponderea vânzărilor pentru fiecare grup și ponderea numărului de expedieri pentru fiecare grup:

5 proprietate utilă - sortarea

Acum, pentru claritate, de la grupa maximă la cea minimă după volumul vânzărilor, vom sorta. Pentru a face acest lucru, plasați cursorul în câmpul cu volumul vânzărilor pe grupuri și faceți clic pe butonul „sortați de la maxim la minim”:

Se poate observa că grupul maxim în ceea ce privește volumul vânzărilor și numărul de expedieri este grupul „de la 0 la 5000 de ruble”. vânzările medii din acest grup sunt de 1971 de ruble.

Notă! Livrarea medie pentru toți clienții este semnificativ diferită de 86% din expedieri. În plus, diferă semnificativ

• pentru toate grupurile, transportul mediu este de 2.803 de ruble. (în total linie).
• Și pentru 86% din transporturi, 1.971 de ruble.

Aceasta este o diferență serioasă, iar dacă stimulăm vânzările pe baza a 86% din transporturi și media pentru acestea - 1.971 de ruble, atunci acțiunile noastre vor fi mai precise, iar efectul este mult mai mare, deoarece. Vom putea interesa numărul maxim de clienți.

Acest exemplu arată o tehnică puternică de planificare a promovării pieței de masă și de prognoză a vânzărilor, care vă poate ajuta să aveți un impact mare și să faceți diferența.

Dacă aveți întrebări, vă rugăm să contactați.

Previziuni exacte pentru tine!

Alăturaţi-ne!

Descărcați aplicații gratuite de prognoză și Business Intelligence:

• Novo Forecast Lite- automată calculul prognozei V excela.
• 4analitica- Analiza ABC-XYZși analiza emisiilor în Excela.
• Qlik Sense Desktop și QlikViewPersonal Edition - sisteme BI pentru analiza și vizualizarea datelor.

Testați caracteristicile soluțiilor plătite:

• Novo Forecast PRO- prognoza in Excel pentru matrice mari de date.