Suuremahuliste keerukate toimingute efektiivsust ei saa reeglina iseloomustada ühe indikaatori W abil, seda peavad abistama teised, täiendavad W 1, W 2,..., W; Mõned neist on soovitav teha rohkem, teised - vähem. Näiteks ettevõtte tulemuslikkuse hindamisel tuleb arvesse võtta mitmeid näitajaid:

• kogu toodete maht

  omahind jne.

Lahinguoperatsiooni analüüsimisel tuleb lisaks põhinäitajale, vaenlasele tekitatud kahju matemaatilisele ootusele, arvestada veel mitmega:

enda kahjud

operatsiooni teostamise aeg,

laskemoona tarbimine jne.

See tulemusnäitajate paljusus, millest mõnda on soovitav maksimeerida ja teisi minimeerida, on iseloomulik igale keerukale operatsioonide uurimisprobleemile. Sel juhul on õige sõnastus "antud kuludega maksimaalse efekti saavutamine" või "antud efekti saavutamine minimaalsete kuludega". Üldjuhul ei ole lahendust, mis muudaks ühe näitaja W 1 maksimumiks ja samal ajal teise näitaja W 2 maksimumiks (või miinimumiks); Pealegi pole sellist lahendust mitme näitaja puhul olemas. Efektiivsuse kvantitatiivne analüüs võib aga olla kasulik ka mitme näitaja puhul, sest see võimaldab teil eelnevalt ära visata selgelt irratsionaalne lahendusvariandid, mis jäävad igas mõttes parimatele valikutele alla.

Vaatame näidet. Analüüsime lahinguoperatsiooni Q, mida hinnatakse kahe näitajaga:

W– lahingumissiooni täitmise tõenäosus;

S- kulutatud vahendite maksumus.

Soovitav on pöörata esimene indikaator maksimumini ja teine ​​miinimumini.

Oletame, et on 20 erinevat lahendusvarianti x 1, x 2,…, x 20. Igaühe puhul on teada mõlema indikaatori W ja S väärtused (vt joonis 1.1).

Jooniselt on näha, et mõnest lahendusvariandist saab koheselt loobuda. Milliseid valikuid tuleks kahe näitaja järgi efektiivsuse hindamisel eelistada. Ilmselgelt need, mis asuvad samaaegselt nii piirkonna paremal kui ka alumisel piiril (joonis 1.1 - punktiirjoon). See. Jäänud on neli valikut: X 16, X 17, X 19, X 20. Neist X 16 on kõige tõhusam, kuid suhteliselt kallis; X 20 on odavaim, kuid mitte nii tõhus. Otsustaja ülesanne on välja mõelda, mis hinnaga saame teatud efektiivsuse kasvu eest maksta või, vastupidi, millise efektiivsuse oleme valmis ohverdama, et mitte kanda liiga suuri materiaalseid kahjusid.

Sx

x

x x x

x x x

x x

x x x

x x

x x

x

x x

Kuna toimingu terviklik hindamine korraga mitme näitaja abil on keeruline, kombineeritakse praktikas mitu näitajat üheks üldistatud näitajaks. Sageli võetakse selliseks kriteeriumiks murdosa; pane lugejasse need näitajad W 1 ,…, W , mida on soovitav suurendada, ja nimetajas - need, mida on soovitav vähendada:

U=
(4)

Tüüpi (4) kriteeriumi üldine puudus on see, et ühe näitaja ebaefektiivsust saab alati kompenseerida teisega (näiteks lahinguülesannete täitmise väike tõenäosus madala laskemoona tarbimise tõttu jne).

Sageli eeldatakse, et liitkriteeriumid on üksikute tulemusnäitajate kaalutud summa:

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

kus α on positiivsed või negatiivsed koefitsiendid.

Positiivsed on seatud nendele näitajatele, mida on soovitav maksimeerida; negatiivne – nende jaoks, mida on soovitav minimeerida. Koefitsientide absoluutväärtused vastavad näitajate tähtsuse astmele. Tüübi (5) kriteeriumil on sama puudus (heterogeensete näitajate vastastikuse kompenseerimise võimalus) ja see võib viia valede soovitusteni. Kuid juhtudel, kui α i ei ole valitud suvaliselt, vaid valitakse nii, et liitkriteerium on parim täitis oma funktsiooni, on selle abil võimalik saada piiratud väärtusega tulemusi.

Mõnel juhul võib mitme näitaja probleemi taandada ühe näitaja probleemiks, kui tuua välja üks (peamine) efektiivsusnäitaja W 1 ja püüda seda maksimeerida ning ülejäänud abinäitajatele W kehtestada vaid mõned vormipiirangud. 2, W 3,...

W
; …W
; W
; ...W

Need piirangud lisatakse siis kindlaksmääratud tingimuste kompleksi a 1 , a 2 ,… .

Selle probleemi sõnastusega kantakse kategooriasse üle kõik tulemusnäitajad, välja arvatud üks, peamine antud töötingimused. Lahendusvariandid, mis etteantud piiridesse ei mahu, visatakse kohe kõrvale. Saadud soovitused sõltuvad ilmselt sellest, kuidas on valitud toetavate näitajate piirmäärad. Et teha kindlaks, kui palju see mõjutab lõplikke soovitusi lahenduse valimiseks, muutke piiranguid mõistlikes piirides.

Kompromisslahenduse loomiseks on veel üks võimalik viis, mida võib nimetada " järjestikuste järeleandmiste meetod" Olgu tulemusnäitajad järjestatud tähtsuse kahanemise järjekorras: esmalt põhilised W 1, seejärel muud abinäitajad: W 2, W 3,…. Lihtsuse huvides eeldame, et igaüks neist tuleb pöörata maksimumini (kui see nii ei ole, piisab astendaja märgi muutmisest). Kompromisslahenduse koostamise protseduur on järgmine. Esiteks otsitakse lahendust, mis maksimeerib peamise efektiivsusnäitaja W 1 . Seejärel, lähtudes praktilistest kaalutlustest ja täpsusest, millega algandmed on teada (sageli väikesed), määratakse teatud "mööndus" ΔW 1, mida nõustume lubama teise näitaja W 2 maksimeerimiseks. Määrame indikaatorile W 1 piirangu, et see ei oleks väiksem kui W 1 * - ∆W 1 (W 1 * on W 1 maksimaalne võimalik väärtus) ja selle piiranguga otsime lahendust, mis pöörab W 2 maksimumini.

Järgmisena määratakse W 2 indikaatoris jälle "mööndus", mille arvelt saab W 3 maksimeerida jne. See meetod on hea, sest kohe on selge, millise hinnaga saadakse “mööndus” ühes näitajas teises. Samas võib ka väiksemate järeleandmiste hinnaga omandatud vabadus valida lahendus osutuda märkimisväärseks, sest Maksimumi piirkonnas muutub lahenduse efektiivsus tavaliselt väga vähe.

Ühel või teisel viisil, mis tahes vormistamismeetodiga, jääb mitmel indikaatoril põhineva otsuse kvantitatiivse põhjendamise ülesanne täielikult määratlemata ja lõpliku lahenduse valiku määrab "ülema" tahteakt. Teadlase ülesanne on varustada “ülema” piisava hulga andmetega, mis võimaldab iga lahendusvariandi eeliseid ja puudusi igakülgselt hinnata ning nende põhjal lõpliku valiku teha.

Meie funktsiooniõpetuse teises osas VLOOKUP(VLOOKUP) Excelis vaatame mitmeid näiteid, mis aitavad teil kogu oma jõudu suunata VLOOKUP kõige ambitsioonikamate Exceli probleemide lahendamiseks. Näidetes eeldatakse, et teil on juba põhiteadmised selle funktsiooni toimimisest. Kui ei, siis võiksite olla huvitatud selle õpetuse esimesest osast, mis selgitab süntaksit ja põhikasutust VLOOKUP. Noh, alustame.

Otsige Excelis mitme kriteeriumi abil

Funktsioon VLOOKUP Excel on tõeliselt võimas tööriist konkreetse väärtuse otsimiseks andmebaasist. Siiski on oluline piirang – selle süntaks võimaldab otsida ainult ühte väärtust. Mida teha, kui peate otsima mitut tingimust kasutades? Lahenduse leiate altpoolt.

Näide 1: Otsige kahe erineva kriteeriumi alusel

Oletame, et meil on tellimuste loend ja me tahame leida Kauba kogus(kogus), kahe kriteeriumi alusel – Kliendi nimi(Klient) ja Toote nimi(Toode). Asja teeb keeruliseks asjaolu, et iga ostja tellis mitut tüüpi kaupu, nagu on näha allolevast tabelist:

Regulaarne funktsioon VLOOKUP ei tööta selle stsenaariumi korral, sest see tagastab esimese leitud väärtuse, mis vastab antud otsinguväärtusele. Näiteks kui soovite teada kauba kogust Maiustused, ostja tellitud Jeremy Hill, kirjutage üles järgmine valem:

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

– see valem tagastab tulemuse 15 , mis vastab tootele Õunad, kuna see on esimene väärtus, mis vastab.

On lihtne lahendus – looge täiendav veerg, milles kõik vajalikud kriteeriumid ühendada. Meie näites on need veerud Kliendi nimi(Klient) ja Toote nimi(Toode). Pidage meeles, et ühendatud veerg peab alati olema otsinguvahemiku vasakpoolseim veerg, kuna funktsioon on vasakpoolsem veerg. VLOOKUP vaatab väärtust otsides.

Seega lisate tabelisse abiveeru ja kopeerite selle valemi kõigisse lahtritesse: =B2&C2. Kui soovite, et string oleks paremini loetav, saate ühendatud väärtused eraldada tühikuga: =B2&” “&C2. Pärast seda saate kasutada järgmist valemit:

VLOOKUP("Jeremy Hill Sweets",$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)

Kus on rakk B1 sisaldab argumendi ühendatud väärtust otsingu_väärtus(otsingu_väärtus) ja 4 - argument veergu_indeksi_arv(veeru_number), st. Otsitavaid andmeid sisaldava veeru number.

Näide 2: VLOOKUP kahe kriteeriumi alusel, kusjuures tabelit vaadatakse teisel lehel

Kui peate põhitabelit värskendama, lisades andmeid teisest tabelist (otsingutabel), mis asub teisel lehel või mõnes muus Exceli töövihikus, saate vajaliku väärtuse koguda otse põhitabelisse sisestatavasse valemisse. .

Nagu eelmises näites, vajate kombineeritud väärtustega otsingutabelis lisaveergu. See veerg peab olema otsingu jaoks määratud vahemiku vasakpoolseim veerg.

Niisiis, valem koos VLOOKUP võiks olla selline:

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

Siin sisaldavad veerud B ja C vastavalt klientide ja tootenimesid ning linki Tellimused!$A&$2:$D$2 määrab tabeli, mida teiselt töölehel otsida.

Valemi loetavamaks muutmiseks võite anda vaadatavale vahemikule nime, mis muudab valemi palju lihtsamaks:

VLOOKUP(B2&" "&C2,tellimused,4,FALSE)
=VLOOKUP(B2&" "Tellimised;4;FALSE)

Valemi toimimiseks tuleb kuvatava tabeli vasakpoolseimas veerus olevad väärtused kombineerida samamoodi nagu otsingukriteeriumides. Ülaltoodud joonisel ühendasime väärtused ja panime nende vahele tühiku, sama tuleb teha funktsiooni esimeses argumendis (B2&” “&C2”).

Pea meeles! Funktsioon VLOOKUP piiratud 255 tähemärgiga, ei saa see otsida väärtust, mis on pikem kui 255 tähemärki. Pidage seda meeles ja veenduge, et otsitava väärtuse pikkus ei ületaks seda piiri.

Olen nõus, et abisamba lisamine ei ole kõige elegantsem ja mitte alati vastuvõetav lahendus. Sama saab teha ka ilma abiveeruta, kuid selleks oleks vaja palju keerukamat valemit koos funktsioonide kombinatsiooniga INDEX(INDEKS) ja MATCH(MATCH).

Me eraldame 2., 3. jne. väärtused VLOOKUP-i abil

Sa juba tead seda VLOOKUP saab tagastada ainult ühe sobiva väärtuse või pigem esimese leitud väärtuse. Aga mis siis, kui seda väärtust korratakse mitu korda vaadeldavas massiivis ja soovite eraldada neist 2. või 3.? Mis siis, kui kõik väärtused? Probleem tundub keeruline, kuid lahendus on olemas!

Oletame, et tabeli üks veerg sisaldab klientide nimesid (kliendi nimi) ja teine ​​​​veergu nende ostetud tooteid (toode). Proovime leida antud kliendi poolt ostetud 2., 3. ja 4. toodet.

Lihtsaim viis on lisada veergu ette abiveerg Kliendi nimi ja täitke see klientide nimedega koos iga nime kordusnumbriga, nt. John Doe1, John Doe2 jne. Teeme nummerdamisega triki funktsiooni kasutades COUNTIF(COUNTIF), arvestades, et klientide nimed on veerus B:

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

Pärast seda saate kasutada tavalist funktsiooni VLOOKUP et leida vajalik tellimus. Näiteks:

• Leiame 2 Dan Brown:

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

• Leiame 3 ostja tellitud kaup Dan Brown:

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

Kui otsite ainult 2 kordamine, saate seda teha ilma abistaja veeruta, luues keerukama valemi:

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

Selles valemis:

• 2 dollarit F$- ostja nime sisaldav lahter (see on muutumatu, pange tähele - link on absoluutne);
• $B$- veerg Kliendi nimi;
• Tabel4– Teie laud (selles kohas võib olla ka tavaline vahemik);
• C16 dollarit– teie tabeli või vahemiku lõpulahter.

See valem leiab ainult teise sobiva väärtuse. Kui teil on vaja ülejäänud kordused välja tõmmata, kasutage eelmist lahendust.

Kui vajate kõigi vastete loendit, kasutage funktsiooni VLOOKUP Siin pole abi, kuna see tagastab korraga ainult ühe väärtuse - see on kõik. Kuid Excelil on funktsioon INDEX(INDEX), mis saab selle ülesandega hõlpsalt hakkama. Kuidas selline valem välja näeb, saate teada järgmisest näitest.

Kõigi soovitud väärtuse korduste toomine

Nagu eelnevalt mainitud, VLOOKUP ei saa vaadata vaadatavast vahemikust kõiki dubleerivaid väärtusi. Selleks on vaja veidi keerukamat valemit, mis koosneb mitmest Exceli funktsioonist, nt INDEX(INDEX), VÄIKE(VÄIKE) ja RIDA(LINE)

Näiteks leiab allolev valem kõik lahtris F2 oleva väärtuse esinemised vahemikus B2:B16 ja tagastab veerus C samade ridade tulemuse.

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

Sisestage see massiivi valem mitmesse külgnevasse lahtrisse, nt. F4:F8, nagu on näidatud alloleval joonisel. Lahtrite arv peab olema võrdne otsinguväärtuse maksimaalse võimaliku korduste arvuga või sellest suurem. Ärge unustage klõpsata Ctrl+Shift+Enter massiivi valemi õigeks sisestamiseks.

Kui soovite mõista, kuidas see toimib, sukeldume valemi üksikasjadesse pisut sügavamale:

1. osa:

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

$F$2=B2:B16– võrrelge väärtust lahtris F2 iga väärtusega vahemikus B2:B16. Kui leitakse vaste, siis avaldis RIDA(C2:C16)-1 tagastab vastava rea ​​numbri (väärtus -1 võimaldab päiserea ära jätta). Kui vasteid pole, siis funktsioon KUI(IF) tagastab tühja stringi.

Funktsiooni tulemus KUI(KUI) on selline horisontaalne massiiv: (1",",3,"",5,"",",",",",",",",",",12," ","","")

2. osa:

RIDA()-3
RIDA()-3

Siin on funktsioon RIDA(ROW) toimib täiendava loendurina. Kuna valem on kopeeritud lahtritesse F4:F9, lahutame arvu 3 funktsiooni tulemusest väärtuse saamiseks 1 lahtris F4(rida 4, lahuta 3), et saada 2 lahtris F5(rida 5, lahuta 3) ja nii edasi.

3. osa:

VÄIKE(IF($F$2=B2:B16,RIDA(C2:C16)-1,""),RIDA()-3))
VÄIKE(IF($F$2=B2:B16,RIDA(C2:C16)-1,""),RIDA()-3))

Funktsioon VÄIKE(VÄIKE) tagastab nth andmemassiivi väikseim väärtus. Meie puhul määrab funktsioon, millise positsiooni (väikseimast) tagastada RIDA(LINE) (vt 2. osa). Niisiis, raku jaoks F4 funktsiooni VÄIKE((massiiv);1) naaseb 1 massiivi (väikseim) element, st 1 . Raku jaoks F5 naaseb 2 massiivi väikseim element, st 3 , ja nii edasi.

4. osa:

INDEKS($C$2:$16C$,VÄIKE(IF($F$2=B2:B16,RIDA(C2:C16)-1,""),RIDA()-3))
INDEX($C$2:$C$16,VÄIKE(IF($F$2=B2:B16,RIDA(C2:C16)-1;""),RIDA()-3))

Funktsioon INDEX(INDEX) tagastab lihtsalt massiivi konkreetse lahtri väärtuse C2:C16. Raku jaoks F4 funktsiooni INDEKS($C$2:$C$16,1) tuleb tagasi Õunad, Sest F5 funktsiooni INDEKS($C$2:$C$16,3) tuleb tagasi Maiustused ja nii edasi.

5. osa:

IFERROR()
IFERROR()

Lõpuks paneme valemi funktsiooni sisse IFERROR(IFERROR), kuna tõenäoliselt ei jää te veateate üle rahule #N/A(#N/A), kui lahtrite arv, millesse valem kopeeritakse, on väiksem kui vaadatud vahemikus olevate duplikaatväärtuste arv.

Kahemõõtmeline otsing teadaoleva rea ​​ja veeru järgi

Kahemõõtmelise otsingu sooritamine Excelis hõlmab väärtuse otsimist teadaoleva rea ​​ja veeru numbri abil. Teisisõnu, saate lahtri väärtuse konkreetse rea ja veeru ristumiskohast.

Nii et läheme oma tabeli juurde ja kirjutame üles funktsiooniga valem VLOOKUP, kust leiab infot märtsis müüdavate sidrunite maksumuse kohta.

Kahemõõtmelise otsingu tegemiseks on mitu võimalust. Tutvu saadaolevate võimalustega ja vali endale sobivaim.

Funktsioonid VLOOKUP ja MATCH

Saate kasutada mitmeid funktsioone VLOOKUP(VLOOKUP) ja OTSING(MATCH), et leida väärtus väljade ristumiskohas Toote nimi(string) ja Kuu kõnealuse massiivi (veerg):

VLOOKUP("Sidrunid",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=VLOOKUP("Sidrunid",$A$2:$I$9,MATCH("Mar",$A$1:$I $1,0),FALSE)

Ülaltoodud valem on tavaline funktsioon VLOOKUP, mis otsib täpset vastet väärtusele „Lemons” lahtrites A2 kuni A9. Kuid kuna te ei tea, millises veerus on märtsi müügid, ei saa te määrata funktsiooni kolmanda argumendi veeru numbrit VLOOKUP. Selle asemel kasutatakse funktsiooni OTSING selle veeru määratlemiseks.

MATCH("märts", $A$1:$I $1,0)
MATCH("märts", $A$1:$I $1,0)

Inimkeelde tõlgituna tähendab see valem:

• Otsime sümboleid “Mar” – argument otsingu_väärtus(otsingu_väärtus);
• Argumendi otsimiseks otsime lahtreid A1 kuni I1 otsingu_massiiv(vaate_massiiv);
• Täpse vaste tagastamine – argument vaste_tüüp(vaste_tüüp).

Kasutades 0 kolmandas argumendis ütlete funktsiooni OTSING otsige esimest väärtust, mis vastab täpselt otsingu väärtusele. See on samaväärne väärtusega VALE(FALSE) neljanda argumendi jaoks VLOOKUP.

Nii saate luua Excelis kahesuunalise otsingu valemi, mida nimetatakse ka kahemõõtmeliseks või kahesuunaliseks otsinguks.

SUMPRODUCT funktsioon

Funktsioon SUMPRODUCT(SUMPRODUCT) tagastab valitud massiivide korrutiste summa:

SUMPRODUCT(($A$2:$A$9="sidrunid")*($A$1:$I$1="märts"),$A$2:$I$9)
=SUMMATOODE(($A$2:$A$9="Sidrunid")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX ja MATCH funktsioonid

Järgmises artiklis selgitan neid funktsioone üksikasjalikult, nii et praegu saate lihtsalt selle valemi kopeerida:

INDEKS($A$2:$I$9,MATCH("Sidrunid",$A$2:$A$9,0),MATCH("Märt",$A$1:$I$1,0))
=INDEKS($A$2:$I$9,MATCH("Sidrunid",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

Nimetatud vahemikud ja ristmikuoperaator

Kui te pole hull kõigi nende keerukate Exceli valemite järele, võib teile meeldida see visuaalne ja meeldejääv viis:

Nime sisestamisel kuvab Microsoft Excel tööriistaspikri koos sobivate nimede loendiga, täpselt nagu valemi sisestamisel.

1. Klõpsake Sisenema ja kontrolli tulemust

Üldiselt on kahemõõtmelise otsingu tulemus sama, ükskõik millise ülaltoodud meetoditest valite:

Kasutame ühes valemis mitut VLOOKUPPI

Juhtub, et põhitabelil ja otsingutabelil pole ühtki ühist veergu ja see takistab tavapärase funktsiooni kasutamist VLOOKUP. Siiski on veel üks tabel, mis ei sisalda meid huvitavat teavet, kuid millel on ühine veerg põhitabeli ja otsingutabeliga.

Vaatame järgmist näidet. Meil on veeruga põhitabel SKU (uus), kuhu tuleb teisest tabelist lisada vastavate hindadega veerg. Lisaks on meil 2 otsingutabelit. Esimene (otsingutabel 1) sisaldab värskendatud numbreid SKU (uus) ja tootenimed ning teine ​​(Otsingutabel 2) – tootenimed ja vanad numbrid SKU (vana).

Teisest otsingutabelist põhitabelisse hindade lisamiseks peate tegema toimingu, mida nimetatakse topelt VLOOKUP või pesastatud VLOOKUP.

1. Kirjutage funktsioon VLOOKUP, mis leiab tabelist toote nimetuse Otsingutabel 1 kasutades SKU, soovitud väärtusena:

   VLOOKUP(A2,Uus_SKU,2,VÄÄR)
   =VLOOKUP(A2,Uus_SKU,2,VÄÄR)

   Siin Uus_SKU- nimega vahemik $A:$B laual Otsingutabel 1, A 2 – see on veerg B, mis sisaldab toodete nimetusi (vt ülaltoodud pilti)

2. Kirjutage üles valem hindade sisestamiseks tabelist Otsingutabel 2 tuntud tootenimede põhjal. Selleks sisestage uue funktsiooni otsinguväärtuseks varem loodud valem VLOOKUP:

   VLOOKUP(VLOOKUP(A2,Uus_SKU,2,FALSE),hind,3,FALSE)
   =VLOOKUP(VLOOKUP(A2,Uus_SKU,2,FALSE),hind,3,FALSE)

   Siin Hind- nimega vahemik $A:$C laual Otsingutabel 2, A 3 on veerg C, mis sisaldab hindu.

Allolev joonis näitab meie loodud valemiga tagastatud tulemust:

Erinevate tabelite andmete dünaamiline asendamine VLOOKUP ja INDIRECT abil

Esiteks selgitame, mida me mõtleme väljendi „Erinevate tabelite andmete dünaamiline asendamine” all, et olla kindel, et mõistame üksteist õigesti.

On olukordi, kus sama vormingu andmetega on mitu lehte ja sõltuvalt lahtrisse sisestatud väärtusest on vaja konkreetselt lehelt vajalik teave eraldada. Ma arvan, et seda on lihtsam näitega selgitada.

Kujutage ette, et teil on müügiaruanded mitme piirkonna kohta samade toodetega ja samas vormingus. Peate leidma konkreetse piirkonna müüginumbrid:

Kui teil on ainult kaks sellist aruannet, saate kasutada uskumatult lihtsat funktsioonidega valemit VLOOKUP Ja KUI(IF), et valida otsimiseks soovitud aruanne:

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

• $ D $ 2– see on lahter, mis sisaldab toote nime. Pange tähele, et me kasutame siin absoluutseid viiteid, et vältida valemi teistesse lahtritesse kopeerimisel otsinguväärtuse muutmist.
• D3 dollarit– see on lahter piirkonna nimega. Kasutame veeru jaoks absoluutset ja rea ​​suhtelist viidet, kuna plaanime kopeerida valemi teistesse sama veeru lahtritesse.
• FL_Sales Ja CA_Müük– vastavaid müügiaruandeid sisaldavate tabelite (või nimeliste vahemike) nimed. Muidugi saab kasutada tavalisi lehenimesid ja lahtrivahemiku viiteid, nt. „FL Sheet”!$A$3:$B$10, kuid nimega vahemikud on palju mugavamad.

Kui aga selliseid tabeleid on palju, siis funktsioon KUI– see pole parim lahendus. Selle asemel saate funktsiooni kasutada KAUDNE(INDIRECT) soovitud otsinguvahemiku tagastamiseks.

Nagu te ilmselt teate, funktsioon KAUDNE kasutatakse tekstistringiga lingi tagastamiseks, mis on täpselt see, mida me praegu vajame. Seega asendage avaldis ülaltoodud valemis oleva funktsiooniga KUI funktsiooniga lingile KAUDNE. See on kombinatsioon VLOOKUP Ja KAUDNE töötab suurepäraselt paaris:

VLOOKUP ($D$2, KAUDNE($D3&"_Müük"),2,FALSE)
=VLOOKUP($D$2,KAUDNE($D3&"_Müük"),2,VÄÄR)

• $ D $ 2– see on lahter toote nimega, see on absoluutse seose tõttu muutumatu.
• D3 dollarit– see on lahter, mis sisaldab piirkonna nime esimest osa. Meie näites on see FL.
• _Müük– kõigi nimetatud vahemike või tabelite nime ühine osa. Koos lahtris D3 oleva väärtusega moodustab see soovitud vahemiku täisnime. Allpool on mõned üksikasjad neile, kellel pole selle funktsiooniga kogemusi KAUDNE.

Kuidas INDIRECT ja VLOOKUP töötavad

Esiteks lubage mul teile meelde tuletada funktsiooni süntaksit KAUDNE(KAUDNE):

• A1, kui argument on võrdne TÕSI(TRUE) või määramata;
• R1C1, Kui FALSE(VALE).

Nii et pöördume tagasi meie müügiaruannete juurde. Kui mäletate, on iga aruanne eraldi tabel, mis asub eraldi lehel. Valemi korrektseks toimimiseks peate andma oma tabelitele (või vahemikele) nimed ja kõigil nimedel peab olema ühine osa. Näiteks nii: CA_Müük, FL_Müük, TX_Müük ja nii edasi. Nagu näete, sisaldavad kõik nimed "_Sales".

Funktsioon KAUDNEühendab väärtuse veerus D ja tekstistringi "_Sales", andes seeläbi aru VLOOKUP millisest lauast vaadata. Kui väärtus “FL” on lahtris D3, otsib valem tabelist FL_Müük, kui tabelis on „CA”. CA_Müük ja nii edasi.

Funktsioonide tulemus VLOOKUP Ja KAUDNE saab olema järgmine:

Kui andmed asuvad erinevates Exceli töövihikutes, peate töövihiku nime lisama enne nimetatud vahemikku, näiteks:

VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

Kui funktsioon KAUDNE viitab teisele töövihikule, siis peab see töövihik avatud olema. Kui see on suletud, teatab funktsioon veast #REF!(#LINK!).

KPI on tulemusnäitaja, mis võimaldab objektiivselt hinnata tehtud toimingute tõhusust. Seda süsteemi kasutatakse erinevate näitajate hindamiseks (kogu ettevõtte tegevus, üksikud struktuurid, konkreetsed spetsialistid). See mitte ainult ei täida juhtimisfunktsioone, vaid stimuleerib ka töötegevust. Tihti on tasustamissüsteem üles ehitatud KPI-de alusel. See on palga muutuva osa moodustamise meetod.

KPI peamised jõudlusnäitajad: näited Excelis

KPI motivatsioonisüsteemi stimuleeriv tegur on rahaline tasu. Selle saab kätte töötaja, kes on täitnud talle pandud ülesande. Preemia/preemia suurus sõltub konkreetse töötaja töötulemustest aruandeperioodil. Töötasu suurus võib olla fikseeritud või väljendatud protsendina palgast.

Peamised tulemusnäitajad ja nende kaalu määrab iga ettevõte eraldi. Andmed sõltuvad ettevõtte eesmärkidest. Näiteks:

1. Eesmärk on saavutada toote müügiplaan 500 000 rubla kuus. Põhinäitaja on müügiplaan. Mõõtmissüsteem: tegelik müügisumma / planeeritud müügisumma.
2. Eesmärk on kasvatada perioodil saadetiste mahtu 20%. Põhinäitaja on keskmine saadetise summa. Mõõtesüsteem: tegelik keskmine saadetis / planeeritud keskmine saadetis.
3. Eesmärk on suurendada klientide arvu teatud piirkonnas 15%. Põhinäitaja on klientide arv ettevõtte andmebaasis. Mõõtesüsteem: tegelik klientide arv / planeeritud klientide arv.

Ettevõte määrab ka koefitsiendi (kaalud) hajumise iseseisvalt. Näiteks:

1. Plaani täitmine alla 80% on vastuvõetamatu.
2. Plaani täitmine 100% - koefitsient 0,45.
3. Plaani täitmine 100-115% - koefitsient 0,005 iga 5% kohta.
4. Vigadeta – koefitsient 0,15.
5. Aruandeperioodil märkusi ei olnud – koefitsient 0,15.

See on ainult üks võimalik variant motivatsioonikoefitsientide määramiseks.

KPI mõõtmise võtmepunkt on tegeliku ja kavandatava näitaja suhe. Peaaegu alati koosneb töötaja palk palgast (püsiosa) ja lisatasust (muutuv/muutuv osa). Motivatsioonikoefitsient mõjutab muutuja kujunemist.

Oletame, et palga püsi- ja muutuva osa suhe on 50 × 50. Peamised tulemusnäitajad ja nende kaal:

Aktsepteerime järgmisi koefitsiente (näitaja 1 ja indikaatori 2 puhul sama):

KPI tabel Excelis:

Selgitused:

See on Exceli KPI tabeli näidis. Iga ettevõte moodustab oma (võttes arvesse töö iseärasusi ja preemiasüsteemi).

KPI maatriks ja näide Excelis

Töötajate hindamiseks peamiste tulemusnäitajate alusel koostatakse maatriks ehk eesmärkide kokkulepe. Üldine vorm näeb välja selline:

1. Põhinäitajad on kriteeriumid, mille alusel personali tööd hinnatakse. Need on iga positsiooni jaoks erinevad.
2. Kaalud on numbrid vahemikus 0 kuni 1, mille kogusumma on 1. Need kajastavad iga põhinäitaja prioriteete, võttes arvesse ettevõtte eesmärke.
3. Alus – indikaatori aktsepteeritav minimaalne väärtus. Alla algtaseme – tulemust pole.
4. Norm – planeeritud tase. Midagi, mida töötaja peab tegema. Allpool - töötaja ei tulnud oma tööülesannetega toime.
5. Eesmärk on väärtus, mille poole püüelda. Standardist kõrgem näitaja, mis võimaldab teil tulemusi parandada.
6. Fakt – tegelikud töötulemused.
7. KPI indeks näitab tulemuste taset normi suhtes.

KPI arvutamise valem:

KPI indeks = ((tegelik – baas) / (norm – baas)) * 100%.

Näide büroojuhi maatriksi täitmisest:

Toimivuskoefitsient on indeksite ja kaalude korrutiste summa. Töötajate tulemuslikkuse reitingud kuvatakse selgelt tingimusvormingu abil.

Oletame, et soovite liita väärtused rohkem kui ühe tingimusega, näiteks toote müügisumma konkreetses piirkonnas. See on funktsiooni SUMIFS hea kasutamine valemis.

Pange tähele seda näidet, kus meil on kaks tingimust: nõutav müügisumma liha(veerust C) kuni Lõuna alad (veerust a).

Siin on valem, mida saab kasutada:

= SUMIFS (D2: D11; a2: A11; "lõuna"; C2: C11; "liha")

Tulemuseks on väärtus 14 719.

Vaatame valemi iga osa üksikasjalikumat esitust.

SUMIFS on aritmeetiline valem. Arvutab arvud, mis antud juhul on veerus D. Esimese sammuna tuleb määrata numbrite asukoht.

=SUMIFS(D2:D11,

Teisisõnu, soovite, et valem summuks selles veerus olevad arvud, kui need vastavad teatud tingimustele. See lahtrivahemik on esimene argument selles valemis esimene andmeelement, mida funktsioon sisendina kasutab.

Seejärel peate leidma kahele tingimusele vastavad andmed ja sisestama esimese tingimuse, määrates funktsiooni jaoks andmete asukoha (a2: A11) ja ka selle, et tingimus on “Lõuna”. Pange tähele üksikute argumentide vahel olevaid koma.

=SUMIFS(D2:D11,A2:A11"lõuna";

Jutumärgid sõna "Lõuna" ümber näitavad, et tegemist on tekstiandmetega.

Lõpuks sisestate teise tingimuse argumendid – lahtrite vahemiku (C2:C11), mis sisaldavad sõna "liha" ja ka sõna ennast (jutumärkides), et Excel saaks need sobitada. Täitke valem, sulgedes sulgud ) ja seejärel vajutage sisestusklahvi. Tulemuseks taas 14 719.

=SUMIFS(D2:D11,A2:A11"lõuna",C2:C11"liha")

Kui te neid argumente ei mäleta, kui sisestate Excelis funktsiooni SUMIFS, on spikker valmis. Pärast valemi sisestamist = SUMIFS ( Valemi alla kuvatakse automaatse täitmise valemid ja argumentide loend kuvatakse vajalikus järjekorras.

Vaadake meie näites valemi automaatse täitmise ja argumentide loendi pilti summa_vahemik on D2:D11, arvude veerg, mis tuleb summeerida. kriteeriumi_vahemik1- a2. A11 - andmeveerg, mis sisaldab tingimus1"Lõunamaa".

Valemi sisestamisel kuvatakse ülejäänud argumendid (siin pole näidatud) Valemi automaattäitmises; vahemiku_tingimused2 on vahemik C2:C11, mis on andmete veerg, mis sisaldab tingimus2- "Liha".

Kui klõpsate SUMIFS Kui täidate valemi automaatselt, avaneb artikkel, kust saate lisateavet.

Proovige harjutada

Kui soovite funktsiooni SUMIFS katsetada, on siin mõned näidisandmed ja funktsiooni kasutav valem.

Selles Exceli veebitöövihikus saate siin töötada näidisandmete ja valemitega. Muutke väärtusi ja valemeid või lisage oma, et näha kohe tulemuste muutumist.

Kopeerige kõik allolevast tabelist olevad lahtrid ja kleepige need uue Exceli lehe lahtrisse A1. Valemite paremaks kuvamiseks saate kohandada veergude laiust.

VPR kahe või enama kriteeriumi alusel

Kindlasti teavad kõik, kes tunnevad funktsiooni VLOOKUP, et see otsib määratud väärtusi eranditult määratud tabeli vasakpoolsest veerust (VLOOKUPi kohta saate rohkem lugeda artiklist: Väärtuse leidmine teisest tabelist või VLOOKUP tugevus) . Samuti teavad paljud, et VLOOKUP otsib ainult ühe väärtuse alusel.

Kas artikkel aitas? Jaga linki oma sõpradega! Videotunnid

("Alumine riba":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmarginright":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 "texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":150 textcss":"display:block; padding:12px; text-align:left;","textbgcss":"kuva:plokk; asukoht: absoluutne; ülemine: 0px; vasak: 0px; laius: 100%; kõrgus: 100% ; background-color:#333333; läbipaistmatus:0,6; filter:alpha(opacity=60);","titlecss":"display:block; asend:suhteline; font:paks 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; asend:suhteline; font: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; värv:#fff; margin-top:8px;","buttoncss":"display:block; asend:suhteline; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))