Učinkovitost složenih operacija velikih razmjera u pravilu se ne može karakterizirati jednim pokazateljem W, već mu moraju pomoći drugi, dodatni W 1, W 2,..., W; Neki od njih je poželjno učiniti više, drugi - manje. Na primjer, kada se procjenjuje uspješnost poduzeća, potrebno je uzeti u obzir niz pokazatelja:

• punu količinu proizvoda

  cijena koštanja itd.

Pri analizi borbene operacije, osim glavnog pokazatelja, matematičkog očekivanja štete nanesene neprijatelju, potrebno je uzeti u obzir niz dodatnih:

vlastite gubitke

vrijeme izvođenja operacije,

potrošnja streljiva itd.

Ovo mnoštvo pokazatelja uspješnosti, od kojih je neke poželjno maksimizirati, a druge minimizirati, karakteristično je za svaki složeni problem operacijskog istraživanja. U ovom slučaju, točna formulacija je "postizanje maksimalnog učinka uz dane troškove" ili "postizanje danog učinka uz minimalne troškove". U općem slučaju ne postoji rješenje koje bi jedan indikator W 1 pretvorilo u maksimum, a istovremeno drugi indikator W 2 u maksimum (ili minimum); Štoviše, takvo rješenje ne postoji za nekoliko pokazatelja. Međutim, kvantitativna analiza učinkovitosti također može biti korisna u slučaju nekoliko pokazatelja, jer omogućuje vam da unaprijed odbacite jasno iracionalan mogućnosti rješenja koje su inferiorne u odnosu na najbolje mogućnosti u svim pogledima.

Pogledajmo primjer. Analizirajmo borbenu operaciju Q, ocijenjenu s dva pokazatelja:

W– vjerojatnost izvršenja borbene misije;

S- trošak utrošenih sredstava.

Preporučljivo je prvi indikator okrenuti na maksimum, a drugi na minimum.

Pretpostavimo da postoji 20 različitih opcija rješenja x 1, x 2,…, x 20. Za svaki od njih poznate su vrijednosti oba pokazatelja W i S (vidi sl. 1.1).

Slika pokazuje da se neke opcije rješenja mogu odmah odbaciti. Koje opcije treba preferirati pri procjeni učinkovitosti prema dva pokazatelja. Očito oni koji leže istodobno na desnoj i donjoj granici regije (na slici 1.1 - točkasta linija). Da. Preostale su četiri opcije: X 16, X 17, X 19, X 20. Od njih je X 16 najučinkovitiji, ali relativno skup; X 20 je najjeftiniji, ali nije toliko učinkovit. Posao donositelja odluka je utvrditi koju cijenu možemo platiti za određeno povećanje učinkovitosti ili, obrnuto, koju smo učinkovitost spremni žrtvovati kako ne bismo pretrpjeli prevelike materijalne gubitke.

Sx

x

x x x

x x x

x x

x x x

x x

x x

x

x x

Zbog činjenice da je teška sveobuhvatna procjena operacije pomoću nekoliko pokazatelja odjednom, u praksi se nekoliko pokazatelja kombinira u jedan generalizirani pokazatelj. Često se kao takav kriterij uzima razlomak; u brojniku staviti one pokazatelje W 1 ,…, W , koje je poželjno povećati, au nazivniku - one koje je poželjno smanjiti:

U=
(4)

Opći nedostatak kriterija tipa (4) je da se manjak učinkovitosti u jednom pokazatelju uvijek može nadoknaditi drugim (primjerice, mala vjerojatnost izvršenja borbene misije zbog male potrošnje streljiva i sl.).

Često se pretpostavlja da su složeni kriteriji "ponderirani zbroj" pojedinačnih pokazatelja uspješnosti:

U=α
+α
+…+α
(5)

gdje su α pozitivni ili negativni koeficijenti.

Pozitivni su postavljeni za one pokazatelje koje je poželjno maksimizirati; negativni – za one koje je poželjno minimizirati. Apsolutne vrijednosti koeficijenata odgovaraju stupnju važnosti pokazatelja. Kriterij tipa (5) ima isti nedostatak (mogućnost međusobne kompenzacije heterogenih pokazatelja) i može dovesti do netočnih preporuka. Međutim, u slučajevima kada α i nisu odabrani proizvoljno, već su odabrani tako da kompozitni kriterij bude najbolji izvršio svoju funkciju, moguće je uz njegovu pomoć dobiti rezultate ograničene vrijednosti.

U nekim slučajevima problem s više pokazatelja može se svesti na problem s jednim pokazateljem ako se izdvoji jedan (glavni) pokazatelj učinkovitosti W 1 i nastoji ga maksimizirati, a preostalim pomoćnim pokazateljima W nametnuti samo neka ograničenja oblika 2, W 3,...

W
; …W
; W
; ...W

Ta će ograničenja tada biti uključena u kompleks specificiranih uvjeta a 1 , a 2 ,… .

Ovom formulacijom problema svi pokazatelji uspješnosti, osim jednog, glavnog, prenose se u kategoriju zadanim uvjetima rada. Opcije rješenja koje se ne uklapaju unutar zadanih granica odmah se odbacuju. Dobivene preporuke očito će ovisiti o tome kako su odabrane granice za prateće pokazatelje. Kako biste odredili koliko to utječe na konačne preporuke za odabir rješenja, mijenjajte ograničenja unutar razumnih granica.

Postoji još jedan mogući način izgradnje kompromisnog rješenja, koji se može nazvati " metoda uzastopnih ustupaka" Neka se pokazatelji uspješnosti poredaju po važnosti: prvo glavni W 1, zatim ostali pomoćni: W 2, W 3,…. Radi jednostavnosti, pretpostavit ćemo da svaki od njih treba okrenuti na maksimum (ako to nije slučaj, dovoljno je promijeniti predznak eksponenta). Postupak konstruiranja kompromisnog rješenja je sljedeći. Prvo se traži rješenje koje maksimizira glavni pokazatelj učinkovitosti W 1 . Zatim se, na temelju praktičnih razmatranja i točnosti s kojom su poznati početni podaci (često male), dodjeljuje određeni "ustupak" ΔW 1, koji smo suglasni dopustiti kako bismo maksimizirali drugi pokazatelj W 2 . Indikatoru W 1 namećemo ograničenje tako da ne bude manje od W 1 * - ∆W 1 (W 1 * je najveća moguća vrijednost W 1), i s tim ograničenjem tražimo rješenje koje pretvara W 2 do maksimuma.

Zatim se ponovno dodjeljuje "ustupak" u indikatoru W 2, na čiju cijenu se W 3 može maksimizirati, itd. Ova metoda je dobra jer je odmah jasno po kojoj se cijeni dobiva “ustupak” u jednom pokazatelju u drugom. Pritom se sloboda izbora rješenja, stečena čak i uz manje ustupke, može pokazati značajnom, jer U području maksimuma, učinkovitost rješenja obično se vrlo malo mijenja.

Na ovaj ili onaj način, s bilo kojom metodom formalizacije, zadatak kvantitativnog obrazloženja odluke na temelju nekoliko pokazatelja ostaje nedovoljno definiran, a konačni izbor rješenja određen je voljnim činom "zapovjednika". Posao istraživača je pružiti "zapovjedniku" dovoljnu količinu podataka, omogućujući mu da sveobuhvatno procijeni prednosti i nedostatke svake opcije rješenja i na temelju njih donese konačan izbor.

U drugom dijelu našeg vodiča za funkcije VLOOKUP(VLOOKUP) u Excelu pogledat ćemo nekoliko primjera koji će vam pomoći da usmjerite svu svoju snagu VLOOKUP za rješavanje najambicioznijih Excel problema. Primjeri pretpostavljaju da već imate osnovno znanje o tome kako ova funkcija radi. Ako niste, možda bi vas zanimalo započeti s prvim dijelom ovog vodiča, koji objašnjava sintaksu i osnovnu upotrebu VLOOKUP. Pa, počnimo.

Pretražujte u Excelu koristeći više kriterija

Funkcija VLOOKUP Excel je stvarno moćan alat za traženje određene vrijednosti u bazi podataka. Međutim, postoji značajno ograničenje - njegova sintaksa omogućuje pretraživanje samo jedne vrijednosti. Što ako trebate tražiti pomoću nekoliko uvjeta? Rješenje ćete pronaći u nastavku.

Primjer 1: Pretraživanje pomoću 2 različita kriterija

Recimo da imamo popis narudžbi i želimo ga pronaći Količina robe(kol.), na temelju dva kriterija – Ime klijenta(Kupac) i Naziv proizvoda(Proizvod). Stvar je komplicirana činjenicom da je svaki od kupaca naručio više vrsta robe, što je vidljivo iz donje tablice:

Redovita funkcija VLOOKUP neće raditi u ovom scenariju jer će vratiti prvu pronađenu vrijednost koja odgovara danoj vrijednosti pretraživanja. Na primjer, ako želite znati količinu artikla Slatkiši, po narudžbi kupca Jeremy Hill, zapišite sljedeću formulu:

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

– ova formula će vratiti rezultat 15 , koji odgovara proizvodu Jabuke, jer je to prva vrijednost koja odgovara.

Postoji jednostavno zaobilazno rješenje - izradite dodatni stupac u kojem ćete kombinirati sve potrebne kriterije. U našem primjeru to su stupci Ime klijenta(Kupac) i Naziv proizvoda(Proizvod). Upamtite da spojeni stupac uvijek mora biti krajnji lijevi stupac u rasponu pretraživanja, budući da je to lijevi stupac u kojem funkcija VLOOKUP izgleda kada traži vrijednost.

Dakle, dodajete pomoćni stupac u tablicu i kopirate ovakvu formulu u sve njene ćelije: =B2&C2. Ako želite da niz bude čitljiviji, spojene vrijednosti možete razdvojiti razmakom: =B2&” “&C2. Nakon toga možete koristiti sljedeću formulu:

VLOOKUP("Slatkiši Jeremy Hill",$A$7:$D$18,4,FALSE)
=VLOOKUP("Jeremy Hill Sweets";$A$7:$D$18;4;FALSE)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

Gdje je ćelija B1 sadrži ulančanu vrijednost argumenta tražena_vrijednost(vrijednost_pretrage) i 4 – argument col_index_num(broj_kolone), tj. Broj stupca koji sadrži podatke koje treba dohvatiti.

Primjer 2: VLOOKUP na temelju dva kriterija s pregledom tablice na drugom listu

Ako trebate ažurirati glavnu tablicu dodavanjem podataka iz druge tablice (Tražilačka tablica), koja se nalazi na drugom listu ili u drugoj Excel radnoj knjizi, tada možete prikupiti potrebnu vrijednost izravno u formuli koju umetnete u glavnu tablicu .

Kao u prethodnom primjeru, trebat će vam pomoćni stupac u tablici pretraživanja s kombiniranim vrijednostima. Ovaj stupac mora biti krajnji lijevi stupac u rasponu navedenom za pretraživanje.

Dakle, formula sa VLOOKUP može biti ovako:

VLOOKUP(B2&" "&C2,Nalozi!$A&$2:$D$2,4,FALSE)
=VLOOKUP(B2&" "Nalozi!$A&$2:$D$2;4;FALSE)

Ovdje stupci B i C sadrže imena kupaca i nazive proizvoda te vezu Narudžbe!$A&$2:$D$2 definira tablicu za traženje na drugom radnom listu.

Kako bi formula bila čitljivija, rasponu koji gledate možete dati naziv, što će učiniti formulu mnogo jednostavnijom:

VLOOKUP(B2&" "&C2,Nalozi,4,FALSE)
=VLOOKUP(B2&" "Porudžbine;4;FALSE)

Da bi formula radila, vrijednosti u krajnjem lijevom stupcu tablice koju gledate moraju se kombinirati na isti način kao u kriterijima pretraživanja. Na gornjoj slici kombinirali smo vrijednosti ​​i stavili razmak između njih, isto se mora učiniti u prvom argumentu funkcije (B2&” “&C2”).

Zapamtiti! Funkcija VLOOKUP ograničen na 255 znakova, ne može tražiti vrijednost koja je duža od 255 znakova. Imajte to na umu i pobrinite se da duljina vrijednosti koju tražite ne prelazi ovo ograničenje.

Slažem se da dodavanje pomoćnog stupca nije najelegantnije i ne uvijek prihvatljivo rješenje. Možete učiniti istu stvar bez pomoćnog stupca, ali to bi zahtijevalo puno složeniju formulu s kombinacijom funkcija INDEKS(KAZALO) i USPOREDI SE(UTAKMICA).

Izdvajamo 2., 3. itd. vrijednosti koristeći VLOOKUP

To već znate VLOOKUP može vratiti samo jednu odgovarajuću vrijednost, odnosno prvu pronađenu. Ali što ako se ova vrijednost ponavlja nekoliko puta u nizu koji gledate, a vi želite izdvojiti drugu ili treću od njih? Što ako sve vrijednosti? Problem se čini kompliciranim, ali postoji rješenje!

Pretpostavimo da jedan stupac tablice sadrži imena kupaca (Customer Name), a drugi sadrži proizvode (Product) koje su kupili. Pokušajmo pronaći 2., 3. i 4. proizvod koji je kupio određeni kupac.

Najjednostavniji način je dodati pomoćni stupac prije stupca Ime kupca i ispunite ga imenima kupaca s brojem ponavljanja svakog imena, npr. John Doe1, John Doe 2 itd. Hajdemo napraviti trik s numeriranjem pomoću funkcije COUNTIF(COUNTIF), s obzirom da su imena kupaca u stupcu B:

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

Nakon toga možete koristiti normalnu funkciju VLOOKUP pronaći narudžbu koja vam je potrebna. Na primjer:

• Pronašli smo 2 Dan Brown:

  VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

• Pronašli smo 3 robu koju je naručio kupac Dan Brown:

  VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

Ako tražite samo 2 ponavljanja, to možete učiniti bez pomoćnog stupca stvaranjem složenije formule:

IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Tablica4,0)+2)&":$C16"),2,FALSE),"")
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Tablica4,0)+2)&":$C16"),2,TRUE)"")

U ovoj formuli:

• $F$2– ćelija koja sadrži ime kupca (nepromijenjeno je, imajte na umu – poveznica je apsolutna);
• $B$- stupac Ime kupca;
• Tablica4– Vaš stol (na ovom mjestu može biti i običan asortiman);
• $C16– krajnja ćelija vaše tablice ili raspona.

Ova formula pronalazi samo drugu odgovarajuću vrijednost. Ako trebate izdvojiti preostala ponavljanja, upotrijebite prethodno rješenje.

Ako trebate popis svih podudaranja, koristite funkciju VLOOKUP Tu nema pomoći, jer vraća samo jednu po jednu vrijednost - to je to. Ali Excel ima funkciju INDEKS(INDEX), koji se lako može nositi s ovim zadatkom. Kako će takva formula izgledati saznat ćete u sljedećem primjeru.

Dohvaćanje svih ponavljanja željene vrijednosti

Kao što je gore navedeno, VLOOKUP ne može izdvojiti sve duplicirane vrijednosti iz raspona koji se gleda. Da biste to učinili, trebat će vam malo složenija formula sastavljena od nekoliko Excel funkcija, kao što su INDEKS(INDEKS), MALI(MALA) i RED(CRTA)

Na primjer, formula u nastavku pronalazi sva pojavljivanja vrijednosti iz ćelije F2 u rasponu B2:B16 i vraća rezultat iz istih redaka u stupcu C.

(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"") )
(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;"");ROW()-3));"") )

Unesite ovu formulu polja u više susjednih ćelija, npr. F4:F8, kao što je prikazano na donjoj slici. Broj ćelija mora biti jednak ili veći od najvećeg mogućeg broja ponavljanja tražene vrijednosti. Ne zaboravi kliknuti Ctrl+Shift+Enter za točan unos formule polja.

Ako ste zainteresirani za razumijevanje kako to funkcionira, zaronimo malo dublje u detalje formule:

1. dio:

IF($F$2=B2:B16,RED(C2:C16)-1,"")
IF($F$2=B2:B16;RED(C2:C16)-1;"")

$F$2=B2:B16– usporedite vrijednost u ćeliji F2 sa svakom od vrijednosti u rasponu B2:B16. Ako se pronađe podudaranje, izraz RED(C2:C16)-1 vraća broj odgovarajućeg reda (vrijednost -1 omogućuje izostavljanje retka zaglavlja). Ako nema podudaranja, funkcija AKO(IF) vraća prazan niz.

Rezultat funkcije AKO(AKO) postojat će horizontalni niz poput ovog: (1,"",3,"",5,"","",,"",,"",,"",,"",12," ","",,"")

2. dio:

RED()-3
RED()-3

Evo funkcije RED(RED) služi kao dodatni brojač. Budući da se formula kopira u ćelije F4:F9, oduzimamo broj 3 iz rezultata funkcije da biste dobili vrijednost 1 u ćeliji F4(redak 4, oduzmi 3) da dobiješ 2 u ćeliji F5(redak 5, oduzmi 3) i tako dalje.

dio 3:

SMALL(IF($F$2=B2:B16,RED(C2:C16)-1,""),RED()-3))
SMALL(IF($F$2=B2:B16,RED(C2:C16)-1,""),RED()-3))

Funkcija MALI(MALA) se vraća nth najmanja vrijednost u nizu podataka. U našem slučaju, koju poziciju (od najmanje) vratiti određuje funkcija RED(LINE) (vidi 2. dio). Dakle, za ćeliju F4 funkcija MALO((niz);1) vraća 1(najmanji) element niza tj 1 . Za ćeliju F5 vraća 2 najmanji element niza, tj 3 , i tako dalje.

dio 4:

INDEKS($C$2:$C$16,MALO(AKO($F$2=B2:B16,RED(C2:C16)-1,""),RED()-3))
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;""),ROW()-3))

Funkcija INDEKS(INDEX) jednostavno vraća vrijednost određene ćelije u nizu C2:C16. Za ćeliju F4 funkcija INDEKS($C$2:$C$16,1)će se vratiti Jabuke, Za F5 funkcija INDEKS($C$2:$C$16,3)će se vratiti Slatkiši i tako dalje.

5. dio:

IFERROR()
IFERROR()

Na kraju smo formulu stavili unutar funkcije IFERROR(IFERROR), jer je malo vjerojatno da ćete biti zadovoljni porukom o pogrešci #N/A(#N/A) ako je broj ćelija u koje se kopira formula manji od broja dupliciranih vrijednosti u pregledanom rasponu.

Dvodimenzionalno pretraživanje po poznatom retku i stupcu

Izvođenje dvodimenzionalnog pretraživanja u Excelu uključuje traženje vrijednosti pomoću poznatog broja retka i stupca. Drugim riječima, dohvaćate vrijednost ćelije na sjecištu određenog retka i stupca.

Pa idemo do naše tablice i zapišimo formulu s funkcijom VLOOKUP, koji će pronaći informacije o cijeni limuna prodanih u ožujku.

Postoji nekoliko načina za izvođenje dvodimenzionalnog pretraživanja. Upoznajte se s dostupnim opcijama i odaberite onu koja vam najviše odgovara.

Funkcije VLOOKUP i MATCH

Možete koristiti hrpu funkcija VLOOKUP(VLOOKUP) i TRAŽI(MATCH) za pronalaženje vrijednosti na sjecištu polja Naziv proizvoda(niz) i Mjesec(stupac) dotičnog niza:

VLOOKUP("Limuni",$A$2:$I$9,MATCH("ožujak",$A$1:$I$1,0),FALSE)
=VLOOKUP("Limuni",$A$2:$I$9,MATCH("Ožujak",$A$1:$I$1,0),FALSE)

Gornja formula je regularna funkcija VLOOKUP, koji traži točno podudaranje vrijednosti "Limuni" u ćelijama od A2 do A9. Ali budući da ne znate u kojem se stupcu nalazi prodaja za ožujak, nećete moći navesti broj stupca za treći argument funkcije VLOOKUP. Umjesto toga koristi se funkcija TRAŽI za definiranje ovog stupca.

MATCH("ožujak",$A$1:$I$1,0)
MATCH("ožujak",$A$1:$I$1,0)

Prevedeno na ljudski jezik, ova formula znači:

• Tražimo simbole “Mar” – argument tražena_vrijednost(vrijednost_pretraživanja);
• Tražimo argument u ćelijama od A1 do I1 traženi_niz(pregled_niza);
• Vraćanje točnog podudaranja - argument vrsta_podudaranja(vrsta_podudaranja).

Korištenje 0 u trećem argumentu kažete funkciji TRAŽI potražite prvu vrijednost koja točno odgovara traženoj vrijednosti. Ovo je ekvivalentno vrijednosti NETOČNO(FALSE) za četvrti argument VLOOKUP.

Ovako možete stvoriti formulu za dvosmjerno pretraživanje u Excelu, koje je također poznato kao dvodimenzionalno pretraživanje ili dvosmjerno pretraživanje.

Funkcija SUMPROIZVOD

Funkcija SUMPROIZVOD(SUMPRODUCT) vraća zbroj umnožaka odabranih nizova:

SUMPRODUCT(($A$2:$A$9="Limuni")*($A$1:$I$1="Ožujak"),$A$2:$I$9)
=SUMPROIZVOD(($A$2:$A$9="Limuni")*($A$1:$I$1="Ožujak");$A$2:$I$9)

Funkcije INDEX i MATCH

Detaljno ću objasniti ove funkcije u sljedećem članku, tako da za sada možete jednostavno kopirati ovu formulu:

INDEX($A$2:$I$9,MATCH("Limuni",$A$2:$A$9,0),MATCH("Ožujak",$A$1:$I$1,0))
=INDEX($A$2:$I$9,MATCH("Limuni",$A$2:$A$9,0),MATCH("Ožujak",$A$1:$I$1,0))

Imenovani rasponi i operator presjeka

Ako niste ludi za svim tim kompliciranim Excel formulama, mogao bi vam se svidjeti ovaj vizualni i nezaboravni način:

Dok unosite naziv, Microsoft Excel će prikazati opis alata s popisom podudarnih imena, baš kao kada unosite formulu.

1. Klik Unesi i provjerite rezultat

Općenito, koju god od gore navedenih metoda odabrali, rezultat dvodimenzionalnog pretraživanja bit će isti:

Koristimo nekoliko VLOOKUP-ova u jednoj formuli

Događa se da glavna tablica i tablica pretraživanja nemaju niti jedan zajednički stupac, što onemogućuje korištenje uobičajene funkcije VLOOKUP. Međutim, postoji još jedna tablica koja ne sadrži informacije koje nas zanimaju, ali ima zajednički stupac s glavnom tablicom i tablicom za pretraživanje.

Pogledajmo sljedeći primjer. Imamo glavnu tablicu sa stupcem SKU (novo), gdje je potrebno dodati stupac s pripadajućim cijenama iz druge tablice. Osim toga, imamo 2 tablice pretraživanja. Prva (Tražilačka tablica 1) sadrži ažurirane brojeve SKU (novo) i nazive proizvoda, a drugi (Lookup table 2) – nazive proizvoda i stare brojeve SKU (stari).

Da biste dodali cijene iz druge tablice pretraživanja u glavnu tablicu, morate izvršiti radnju poznatu kao dvostruko VLOOKUP ili ugniježđeni VLOOKUP.

1. Napiši funkciju VLOOKUP, koji pronalazi naziv proizvoda u tablici Pregledna tablica 1 korištenjem SKU, kao željena vrijednost:

   VLOOKUP(A2,Novi_SKU,2,FALSE)
   =VLOOKUP(A2,Novi_SKU,2,FALSE)

   Ovdje New_SKU– imenovani raspon $A:$B u stolu Pregledna tablica 1, A 2 – ovo je stupac B koji sadrži nazive proizvoda (pogledajte gornju sliku)

2. Zapišite formulu za umetanje cijena iz tablice Pregledna tablica 2 na temelju dobro poznatih naziva proizvoda. Da biste to učinili, umetnite formulu koju ste ranije stvorili kao vrijednost pretraživanja za novu funkciju VLOOKUP:

   VLOOKUP(VLOOKUP(A2,Novi_SKU,2,FALSE),Cijena,3,FALSE)
   =VLOOKUP(VLOOKUP(A2,Novi_SKU,2,FALSE),Cijena,3,FALSE)

   Ovdje Cijena– imenovani raspon $A:$C u stolu Pregledna tablica 2, A 3 je stupac C koji sadrži cijene.

Slika ispod prikazuje rezultat koji je vratila formula koju smo izradili:

Dinamička zamjena podataka iz različitih tablica pomoću VLOOKUP-a i INDIRECT-a

Prvo, razjasnimo što mislimo pod izrazom "Dinamička zamjena podataka iz različitih tablica" kako bismo bili sigurni da se ispravno razumijemo.

Postoje situacije kada postoji više listova s ​​podacima istog formata, te je potrebno izdvojiti potrebne podatke iz pojedinog lista ovisno o vrijednosti koja se upisuje u pojedinu ćeliju. Mislim da je to lakše objasniti na primjeru.

Zamislite da imate izvješća o prodaji za nekoliko regija s istim proizvodima i u istom formatu. Morate pronaći podatke o prodaji za određenu regiju:

Ako imate samo dva takva izvješća, onda možete koristiti nečuveno jednostavnu formulu s funkcijama VLOOKUP I AKO(IF) za odabir željenog izvješća za pretraživanje:

VLOOKUP($D$2,IF($D3="FL",FL_Prodaja,CA_Prodaja),2,FALSE)
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

• $D$2– ovo je ćelija koja sadrži naziv proizvoda. Imajte na umu da ovdje koristimo apsolutne reference kako bismo izbjegli promjenu vrijednosti pretraživanja prilikom kopiranja formule u druge ćelije.
• $D3– ovo je ćelija s nazivom regije. Koristimo apsolutnu referencu za stupac i relativnu referencu za redak jer planiramo kopirati formulu u druge ćelije u istom stupcu.
• FL_Sales I CA_Prodaja– imena tablica (ili imenovanih raspona) koje sadrže odgovarajuća izvješća o prodaji. Naravno, možete koristiti obične nazive listova i reference raspona ćelija, npr. ‘FL list’!$A$3:$B$10, ali imenovani rasponi mnogo su praktičniji.

Međutim, kada postoji mnogo takvih tablica, funkcija AKO– ovo nije najbolje rješenje. Umjesto toga, možete koristiti funkciju POSREDNO(INDIRECT) za vraćanje željenog raspona pretraživanja.

Kao što vjerojatno znate, funkcija POSREDNO koristi se za vraćanje veze s tekstualnim nizom, što je upravo ono što nam sada treba. Dakle, slobodno zamijenite izraz s funkcijom u gornjoj formuli AKO na vezu s funkcijom POSREDNO. Ovo je kombinacija VLOOKUP I POSREDNO odlično radi u paru:

VLOOKUP($D$2,INDIRECT($D3&"_Prodaja"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"_Prodaja"),2,FALSE)

• $D$2– ovo je ćelija s nazivom proizvoda, nepromijenjena je zbog apsolutne veze.
• $D3– ovo je ćelija koja sadrži prvi dio naziva regije. U našem primjeru ovo je FL.
• _Prodajni– zajednički dio naziva svih imenovanih raspona ili tablica. U kombinaciji s vrijednošću u ćeliji D3, čini puni naziv željenog raspona. Ispod su neki detalji za one koji nemaju iskustva s funkcijom POSREDNO.

Kako rade INDIRECT i VLOOKUP

Najprije ću vas podsjetiti na sintaksu funkcije POSREDNO(INDIREKTNO):

• A1, ako je argument jednak PRAVI(TRUE) ili nije navedeno;
• R1C1, Ako FALSE(LAŽ).

Dakle, vratimo se našim izvještajima o prodaji. Ako se sjećate, svaki izvještaj je zasebna tablica koja se nalazi na posebnom listu. Da bi formula radila ispravno, morate dati imena svojim tablicama (ili rasponima), a sva imena moraju imati zajednički dio. Na primjer, ovako: CA_Prodaja, FL_Prodaja, TX_Prodaja i tako dalje. Kao što vidite, sva imena sadrže "_Sales".

Funkcija POSREDNO povezuje vrijednost u stupcu D i tekstualni niz “_Sales”, čime izvještava VLOOKUP u koju tablicu pogledati. Ako je vrijednost "FL" u ćeliji D3, formula će pretraživati ​​tablicu FL_Prodaja, ako je "CA" u tablici CA_Prodaja i tako dalje.

Rezultat funkcija VLOOKUP I POSREDNO bit će sljedeće:

Ako se podaci nalaze u različitim Excel radnim knjigama, morate dodati naziv radne knjige prije imenovanog raspona, na primjer:

VLOOKUP($D$2,INDIRECT($D3&"Radna knjiga1!_Prodaja"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"Radna knjiga1!_Prodaja"),2,FALSE)

Ako funkcija POSREDNO odnosi se na drugu radnu knjigu, tada ta radna knjiga mora biti otvorena. Ako je zatvorena, funkcija će prijaviti pogrešku #REF!(#VEZA!).

KPI je pokazatelj učinka koji vam omogućuje objektivnu procjenu učinkovitosti izvršenih radnji. Ovaj sustav se koristi za ocjenu različitih pokazatelja (aktivnosti cijele tvrtke, pojedinačnih struktura, specifičnih stručnjaka). Ne samo da obavlja kontrolne funkcije, već i potiče radnu aktivnost. Često se sustav nagrađivanja gradi na temelju KPI-ja. Ovo je način formiranja varijabilnog dijela plaće.

KPI ključni pokazatelji uspješnosti: primjeri u Excelu

Stimulativni faktor u KPI sustavu motivacije je novčana nagrada. Može ga dobiti zaposlenik koji je izvršio postavljeni zadatak. Visina bonusa/bonusa ovisi o uspješnosti rada pojedinog zaposlenika u izvještajnom razdoblju. Visina naknade može biti fiksna ili izražena u postotku od plaće.

Svako poduzeće određuje ključne pokazatelje uspješnosti i težinu svakog pojedinačno. Podaci ovise o ciljevima tvrtke. Na primjer:

1. Cilj je postići plan prodaje proizvoda od 500.000 rubalja mjesečno. Ključni pokazatelj je plan prodaje. Sustav mjerenja: stvarni iznos prodaje / planirani iznos prodaje.
2. Cilj je povećati količinu pošiljaka u razdoblju za 20%. Ključni pokazatelj je prosječna količina pošiljke. Sustav mjerenja: stvarna prosječna isporuka / planirana prosječna isporuka.
3. Cilj je povećati broj klijenata za 15% u određenoj regiji. Ključni pokazatelj je broj klijenata u bazi podataka poduzeća. Sustav mjerenja: stvarni broj klijenata / planirani broj klijenata.

Poduzeće također samostalno određuje raspon koeficijenata (pondera). Na primjer:

1. Ispunjenje plana manje od 80% je nedopustivo.
2. Ispunjenost plana 100% - koeficijent 0,45.
3. Ispunjenje plana 100-115% - koeficijent 0,005 za svakih 5%.
4. Nema grešaka – koeficijent 0,15.
5. U izvještajnom razdoblju nije bilo komentara – koeficijent 0,15.

Ovo je samo moguća opcija za određivanje motivacijskih koeficijenata.

Ključna točka u mjerenju KPI-ja je omjer stvarnog pokazatelja prema planiranom. Gotovo uvijek se plaća zaposlenika sastoji od plaće (fiksni dio) i bonusa (varijabilni/varijabilni dio). Koeficijent motivacije utječe na formiranje varijable.

Pretpostavimo da je omjer stalnog i varijabilnog dijela plaće 50 × 50. Ključni pokazatelji uspješnosti i težina svakog od njih:

Prihvatimo sljedeće vrijednosti koeficijenata (isti za indikator 1 i indikator 2):

KPI tablica u Excelu:

Objašnjenja:

Ovo je primjer KPI tablice u Excelu. Svako poduzeće izrađuje svoje (uzimajući u obzir karakteristike rada i sustav bonusa).

KPI matrica i primjer u Excelu

Za procjenu zaposlenika prema ključnim pokazateljima uspješnosti, sastavlja se matrica ili dogovor o ciljevima. Opći obrazac izgleda ovako:

1. Ključni pokazatelji su kriteriji po kojima se ocjenjuje rad osoblja. Oni su različiti za svaku poziciju.
2. Ponderi su brojevi u rasponu od 0 do 1, čiji je ukupni zbroj 1. Oni odražavaju prioritete svakog ključnog pokazatelja, uzimajući u obzir ciljeve tvrtke.
3. Baza – prihvatljiva minimalna vrijednost indikatora. Ispod osnovne razine – bez rezultata.
4. Norma – planirana razina. Nešto što zaposlenik mora učiniti. Ispod - zaposlenik se nije uspio nositi sa svojim dužnostima.
5. Cilj je vrijednost kojoj treba težiti. Nadstandardni pokazatelj koji vam omogućuje poboljšanje rezultata.
6. Činjenica – stvarni rezultati rada.
7. KPI indeks pokazuje razinu rezultata u odnosu na normu.

Formula za izračunavanje kpi:

KPI indeks = ((stvarno - baza) / (norma - baza)) * 100%.

Primjer popunjavanja matrice za voditelja ureda:

Koeficijent učinka je zbroj umnožaka indeksa i pondera. Ocjene učinka zaposlenika jasno su prikazane pomoću uvjetnog oblikovanja.

Recimo da želite zbrojiti vrijednosti s više od jednog uvjeta, kao što je zbroj prodaje proizvoda u određenoj regiji. Ovo je dobra upotreba funkcije SUMIFS u formuli.

Obratite pozornost na ovaj primjer gdje imamo dva uvjeta: potreban iznos prodaje meso(iz stupca C) do Jug područja (iz stupca a).

Ovdje je formula koja se može koristiti za postizanje:

= SUMIFS (D2: D11; a2: A11; "Jug"; C2: C11; "meso")

Rezultat je vrijednost 14,719.

Pogledajmo detaljniji prikaz svakog dijela formule.

SUMIFS je aritmetička formula. Izračunava brojeve, koji su u ovom slučaju u stupcu D. Prvi korak je određivanje položaja brojeva.

=SUMIFS(D2:D11,

Drugim riječima, želite da formula zbroji brojeve u tom stupcu ako ispunjavaju određene uvjete. Ovaj raspon ćelija je prvi argument u ovoj formuli, prvi podatkovni element koji funkcija koristi kao ulaz.

Zatim trebate pronaći podatke koji zadovoljavaju dva uvjeta i unijeti prvi uvjet, navodeći za funkciju lokaciju podataka (a2: A11), a također da je uvjet "Jug". Obratite pažnju na zareze između pojedinačnih argumenata.

=SUMIFS(D2:D11,A2:A11,"Jug";

Navodnici oko riječi "Jug" označavaju da se radi o tekstualnim podacima.

Na kraju upisujete argumente za drugi uvjet - raspon ćelija (C2:C11) koje sadrže riječ "Meso" kao i samu riječ (u navodnicima) kako bi ih Excel mogao spojiti. Dovršite formulu zatvaranjem zagrade ) , a zatim pritisnite Enter. Rezultat je opet 14,719.

=SUMIFS(D2:D11,A2:A11,"Jug",C2:C11,"Meso")

Dok upisujete funkciju SUMIFS u Excelu, ako se ne sjećate ovih argumenata, pomoć je spremna. Nakon unosa formule = SUMIFS ( Formule za automatsko dovršavanje pojavit će se ispod formule, a popis argumenata bit će naveden potrebnim redoslijedom.

Pogledajte sliku automatskog dovršavanja formule i popisa argumenata u našem primjeru raspon_zbroja je D2:D11, stupac brojeva koje je potrebno zbrojiti. raspon_kriterija1- a2. A11 - stupac podataka koji sadrži uvjet1"Južnjak".

Dok unosite formulu, preostali argumenti (nisu ovdje prikazani) pojavit će se u automatskom dovršetku formule; raspon_uvjeta2 je raspon C2:C11, što je stupac podataka koji sadrži uvjet2- "Meso".

Ako kliknete SUMIFS Kada automatski dovršite formulu, otvorit će se članak u kojem možete dobiti više informacija.

Pokušajte vježbati

Ako želite eksperimentirati s funkcijom SUMIFS, evo nekoliko primjera podataka i formule koja koristi tu funkciju.

U ovoj radnoj knjizi programa Excel za web možete raditi s oglednim podacima i formulama upravo ovdje. Promijenite vrijednosti i formule ili dodajte svoje kako biste vidjeli trenutnu promjenu rezultata.

Kopirajte sve ćelije iz donje tablice i zalijepite ih u ćeliju A1 novog Excel lista. Možete prilagoditi širinu stupaca kako bi se formule bolje prikazale.

VPR na temelju dva ili više kriterija

Sigurno svatko tko je upoznat s funkcijom VLOOKUP zna da ona traži zadane vrijednosti isključivo u lijevom stupcu navedene tablice (više o VLOOKUP-u možete pročitati u članku: Kako pronaći vrijednost u drugoj tablici ili VLOOKUP snaga) . Također, mnogi znaju da VLOOKUP pretražuje samo na temelju jedne vrijednosti.

Je li članak pomogao? Podijelite link sa svojim prijateljima! Video lekcije

("Donja traka":("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":"lijevo","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"desno","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"desno","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:apsolute; top:0px; left:0px; width:100%; height:100% ; boja pozadine:#333333; neprozirnost:0,6; filter:alpha(opacity=60);","titlecss":"display:block; položaj: relativno; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff;","descriptioncss":"display:block; položaj: relativno; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff; margin-top:8px;","buttoncss":"display:block; položaj: relativno; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))