Î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 arăta 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, adică 1 . Pentru celulă F5 se intoarce al 2-lea cel mai mic element din matrice, adică 3 , si asa 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 si asa 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ă.
- 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.
- 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)
- 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, inserați formula pe care ați creat-o mai devreme ca valoare de căutare pentru optiune noua 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 pentru căutare:
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. Când este combinat cu valoarea din celula D3, formează numele complet calificat al intervalului dorit. 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(FALS).
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 si asa 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 si asa mai departe.
Rezultatul funcțiilor CĂUTARE Vși INDIRECT vor fi urmatoarele:
Dacă datele sunt situate în diferite cărți Excel, atunci trebuie să adăugați numele cărții înainte de intervalul numit, de exemplu:
CĂUTARE V($D$2,INDIRECT($D3&„Carte 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!).
Subiect: Luarea deciziilor pe mai mulți indicatori de criterii.
În practică, de obicei trebuie să alegeți decizie managerială 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ă se caracterizează prin faptul că indicatorii sunt evaluați în diferite unități de măsură și pentru a construi evaluare integrată este necesară trecerea la un singur contor.
Prima problemă este rezolvată prin aplicarea uneia dintre cele patru modificări ale metodei evaluări ale experților, și anume metoda comparării în perechi, care ne permite să cuantificăm semnificația. Esența metodei de comparare în perechi este aceea 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, procedați după cum urmează: cel mai bun pret indicatorul considerat este luat egal cu 1 punct, iar cele mai slabe valori în cotele acestui 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 lgN, 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:
Din cele 6 opțiuni pentru soluții alternative, fiecare dintre acestea fiind evaluată de a 5-a indicatori de criterii trebuie să alegi cea mai bună opțiune.
Evaluați folosind 2 abordări:
în lipsa datelor statistice privind valoarea indicatorilor luați în considerare;
daca 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ă
№ sarcini |
|||||
Nr de alternative |
|||||
№ sarcini |
|||||
Nr de alternative |
|||||
№ sarcini |
|||||
Nr de alternative |
|||||
№ sarcini |
|||||
Nr de alternative |
|||||
№ sarcini |
|||||
Nr de alternative |
|||||
№ sarcini |
|||||
Nr de alternative |
Masa 2.
Date inițiale:
Soluții alternative |
|||||||||||||||
indicatori |
A6 |
||||||||||||||
X 1 |
|||||||||||||||
X 2 |
|||||||||||||||
X 3 |
|||||||||||||||
X 4 |
|||||||||||||||
X 5 |
KPI este un indicator de performanță care vă permite să evaluați în mod obiectiv eficacitatea acțiunilor efectuate. Acest sistem este utilizat pentru evaluarea diverșilor indicatori (activitățile întregii companii, structuri individuale, specialiști specifici). Îndeplinește nu numai funcțiile de control, dar stimulează și activitatea de muncă. Adesea, un sistem de plată este construit pe baza KPI. Aceasta este o metodă de formare a unei părți variabile a salariului.
Indicatori cheie de performanță KPI: Exemple în Excel
Factorul stimulator în sistemul de motivare KPI este recompensa monetară. Poate fi primit de angajatul care a îndeplinit sarcina care i-a fost atribuită. Valoarea bonusului/bonusului depinde de rezultatul unui anumit angajat în perioada de raportare. Cuantumul remunerației poate fi fix sau exprimat ca procent din salariu.
Fiecare întreprindere determină indicatori cheie de performanță și ponderea fiecăruia în mod individual. Datele depind de sarcinile companiei. De exemplu:
- Scopul este de a oferi un plan de vânzări de produse în valoare de 500.000 de ruble pe lună. Indicatorul cheie este planul de vânzări. Sistem de măsurare: valoarea reală a vânzărilor / valoarea vânzărilor planificată.
- Scopul este de a crește cantitatea de expediere în perioada respectivă cu 20%. Indicatorul cheie este cantitatea medie de expediere. Sistem de măsurare: transport mediu efectiv / transport mediu planificat.
- Scopul este de a crește numărul de clienți cu 15% într-o anumită regiune. Indicatorul cheie este numărul de clienți din baza de date a întreprinderii. Sistem de măsurare: numărul real de clienți / numărul planificat de clienți.
De asemenea, compania determină în mod independent răspândirea coeficientului (ponderilor). De exemplu:
- Implementarea planului cu mai puțin de 80% este inacceptabilă.
- Implementarea planului 100% - coeficient 0,45.
- Implementarea planului 100-115% - un coeficient de 0,005 pentru fiecare 5%.
- Fără erori - coeficient 0,15.
- Nu au existat observații în perioada de raportare - coeficient 0,15.
Este doar varianta posibila determinarea coeficienţilor motivaţionali.
Punctul cheie în măsurarea KPI este raportul dintre indicatorul real și cel planificat. Aproape intotdeauna salariu angajat este compus din salariu (partea fixa) si bonus (partea variabila/modificabila). Factorul motivațional influențează formarea variabilei.
Să presupunem că raportul dintre părțile fixe și variabile din salariu este de 50 × 50. Indicatori cheie de performanță și ponderea fiecăruia dintre ei:
Acceptăm următoarele valori ale coeficienților (la fel pentru indicatorul 1 și indicatorul 2):
Tabel KPI în Excel:
Explicatii:
Acesta este un exemplu de tabel KPI în Excel. Fiecare întreprindere își face propriile sale (ținând cont de particularitățile muncii și sistemul de bonusuri).
Matrice KPI și exemplu în Excel
Pentru a evalua angajații cu privire la indicatorii cheie de performanță, se elaborează o matrice sau un acord asupra obiectivelor. Forma generală arată astfel:
- Indicatorii cheie sunt criteriile după care este evaluată munca personalului. Sunt diferite pentru fiecare poziție.
- Ponderile sunt numere în intervalul de la 0 la 1, a căror sumă totală este 1. Ele reflectă prioritățile fiecărui indicator cheie, ținând cont de obiectivele companiei.
- Baza - valoarea minimă permisă a indicatorului. Sub linia de bază - niciun rezultat.
- Norma este nivelul planificat. Ce trebuie să facă un angajat. Mai jos - angajatul nu și-a făcut față îndatoririlor sale.
- Un scop este o valoare spre care să ținești. Peste norme, ceea ce permite îmbunătățirea rezultatelor.
- Fapt - rezultatele reale ale muncii.
- Indicele KPI arată nivelul rezultatului în raport cu norma.
Formula pentru calcularea kpi:
Indicele KPI = ((Fact - Baza) / (Norma - Baza)) * 100%.
Un exemplu de completare a unei matrice pentru un manager de birou:
Raportul de performanță este suma produselor indicilor și ponderilor. Evaluarea performanței angajaților este afișată vizual folosind formatarea condiționată.
Formatare condiționată (5)
Liste și intervale (5)
Macrocomenzi (proceduri VBA) (63)
Diverse (39)
Erori și erori Excel (4)
CĂUTARE V pentru două sau mai multe criterii
Cu siguranță, toți cei care sunt familiarizați cu funcția CĂUTARE VL știe că caută valorile date exclusiv în coloana din stânga a tabelului specificat (mai multe despre CĂUTARE VL pot fi găsite în articolul: Cum să găsiți o valoare într-un alt tabel sau puterea CĂUTARE VL) . De asemenea, mulți oameni știu că VLOOKUP caută doar pe baza unei singure valori.
A ajutat articolul? Distribuie link-ul prietenilor tăi! Lecții video("Bara de jos":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"dreapta","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"dreapta","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2,":1500,":1500, textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; poziție:absolute; sus:0px; stânga:0px; lățime:100%; înălțime:100% ; culoare de fundal:#333333; opacitate:0,6; filtru:a lpha(opacity=60);","titlecss":"display:block; poziție:relativă; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; culoare:#fff;","descriptioncss":"display:block; poziție:relativă; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; culoare:#fff; margin-top:8px;","buttoncss":"display:block; poziție:relativă; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
1. Într-o coloană suplimentară în care vom indica ratingul, introduceți funcția RANK (scrieți în celula = RANK și selectați funcția EXCEL propusă din listă, faceți clic pe fx în bara de formule)
2. Completați argumentele în fereastra care se deschide: „Număr” - specificați prima valoare din tabelul nostru în aceeași linie în care se află formula.
3. „Link” - specificați întreaga matrice de date, adică interval cu toate numerele (valorile vânzărilor).
4. Fixăm limitele acestui interval (apăsați F4 de pe tastatură), astfel încât, atunci când trageți în viitor, adresa intervalului să nu se „mute” și apăsați OK.
5. Extindem formula la toate celulele coloanei „evaluare” în jos.
Când utilizați această funcție, ratingul este calculat automat, iar dacă modificați orice valoare, atunci ratingul va fi recalculat automat.
Dacă ți-a plăcut materialul sau chiar l-ai găsit util, poți mulțumi autorului transferând o anumită sumă folosind butonul de mai jos:
(pentru a transfera cu cardul, faceți clic pe VISA și apoi pe „transfer”)