Danas ćemo pogledati funkciju Excel − FIND(u engleskoj verziji FIND), koji vam omogućava da pronađete poziciju teksta za pretragu u tekstu u odnosu na prvi znak. Obično, ali ne uvijek, funkcija FIND se koristi u kombinaciji s drugim Excel funkcijama, što ćemo razmotriti u sljedećim člancima, a danas ćemo pokušati razumjeti princip rada ove funkcije.

Recimo da imamo tabelu sa spiskom imena i prezimena.

Naš zadatak je da pronađemo poziciju razmaka u odnosu na prvi znak. Na primjer, uzmimo ime i prezime Dani Estrada, izračunajmo sami gdje je jaz. Postoji prostor 5 znak koji počinje prvim slovom. Sada ćemo to učiniti automatski pomoću funkcije FIND.

Sintaksa funkcije je sljedeća

FIND(tekst_pretraživanja; viewed_text; [početna_pozicija]), gdje

search_text je tekst koji trebamo pronaći u tekstu, u našem slučaju to je razmak, označen je ovako “ “.

viewed_text– ovo je tekst u kojem se traži željeni tekst, u našem slučaju to je ime i prezime ili ćelija A2

start_position je neobavezni argument on specificira s koje pozicije na računu trebamo tražiti tekst. Ovo može biti potrebno, na primjer, ako se tekst za pretragu pojavljuje dvaput i moramo znati poziciju drugog teksta za pretragu.

Napišemo formulu =FIND(" " ;A2 ;1), povučemo je prema dolje i dobijemo rezultat koji nam je potreban.

Ovaj vodič objašnjava glavne prednosti funkcija INDEX I TRAŽI u Excelu, što ih čini privlačnijima u odnosu na VLOOKUP. Vidjet ćete nekoliko primjera formula koje će vam pomoći da se lakše nosite s mnogim složenim zadacima s kojima se funkcija suočava. VLOOKUP nemoćan.

U nekoliko nedavnih članaka, uložili smo sve napore da objasnimo osnove funkcije početnicima. VLOOKUP i pokazati primjere složenijih formula za napredne korisnike. Sada ćemo pokušati, ako ne i odvratiti vas od upotrebe VLOOKUP, onda barem pokaži alternativnim načinima implementacija vertikalnog pretraživanja u Excelu.

Zašto nam ovo treba? – pitate. Da, jer VLOOKUP Pretraživanje nije jedina funkcija pretraživanja u Excelu, a njena brojna ograničenja mogu vas spriječiti da dobijete željene rezultate u mnogim situacijama. S druge strane, funkcije INDEX I TRAŽI– fleksibilniji i imaju niz karakteristika koje ih čine privlačnijima u odnosu na VLOOKUP.

Osnovne informacije o INDEX i MATCH

Pošto je svrha ovog tutorijala da pokaže mogućnosti funkcija INDEX I TRAŽI za implementaciju vertikalnog pretraživanja u Excelu, nećemo se zadržavati na njihovoj sintaksi i primjeni.

Ovdje predstavljamo minimum potreban za razumijevanje suštine, a zatim ćemo detaljno ispitati primjere formula koje pokazuju prednosti korištenja INDEX I TRAŽI umjesto VLOOKUP.

INDEX – sintaksa i upotreba funkcije

Funkcija INDEX(INDEX) u Excelu vraća vrijednost iz niza na datim brojevima reda i stupaca. Funkcija ima sledeću sintaksu:

Svaki argument ima vrlo jednostavno objašnjenje:

• niz(niz) je raspon ćelija iz kojih želite izdvojiti vrijednost.
• red_br(broj_line) je broj reda u nizu iz kojeg želite izdvojiti vrijednost. Ako nije navedeno, tada je potreban argument kolona_br(broj_kolone).
• kolona_br(broj_kolone) je broj kolone u nizu iz koje želite izdvojiti vrijednost. Ako nije navedeno, tada je potreban argument red_br(broj_line)

Ako su navedena oba argumenta, onda funkcija INDEX vraća vrijednost iz ćelije na presjeku navedenog reda i stupca.

Evo najjednostavnijeg primjera funkcije INDEX(INDEKS):

INDEKS(A1:C10,2,3)
=INDEX(A1:C10,2,3)

Formula pretražuje raspon A1:C10 i vraća vrijednost ćelije u 2nd linija i 3rd kolone, odnosno iz ćelije C2.

Vrlo jednostavno, zar ne? Međutim, u praksi ne znate uvijek koji red i stupac trebate, pa vam je stoga potrebna pomoć funkcije TRAŽI.

MATCH - sintaksa i upotreba funkcije

Funkcija MATCH(MATCH) u Excel-u traži određenu vrijednost u rasponu ćelija i vraća relativnu poziciju te vrijednosti u rasponu.

Na primjer, ako je u rasponu B1:B3 sadrži vrijednosti New-York, Paris, London, tada će sljedeća formula vratiti broj 3 , pošto je “London” treći element na listi.

MATCH("London",B1:B3,0)
=MATCH("London";B1:B3;0)

Funkcija MATCH(MATCH) ima sljedeću sintaksu:

MATCH(vrijednost_pretraživanja, niz_potraživanja,)
MATCH(vrijednost_pretraživanja, niz_potraživanja, [tip_podudaranja])

• lookup_value(search_value) je broj ili tekst koji tražite. Argument može biti vrijednost, uključujući boolean, ili referenca ćelije.
• lookup_array(search_array) – opseg ćelija u kojima se vrši pretraga.
• match_type(match_type) – Ovaj argument govori o funkciji TRAŽI, da li želite pronaći tačno ili približno podudaranje:
  • 1 ili nije specificirano– nalazi maksimalna vrijednost, manje ili jednako željenom. Niz koji se pregleda mora biti poređan rastućim redoslijedom, odnosno od najmanjeg prema najvećem.
  • 0 – pronalazi prvu vrijednost jednaku željenoj. Za kombinaciju INDEX/TRAŽI uvijek vam je potrebno točno podudaranje, dakle treći argument funkcije TRAŽI moraju biti jednaki 0 .
  • -1 – pronalazi najmanju vrijednost veću ili jednaku vrijednosti pretraživanja. Niz koji se pregleda mora biti sortiran u opadajućem redoslijedu, odnosno od najvećeg prema najmanjem.

Na prvi pogled, prednost funkcije TRAŽI izaziva sumnje. Ko treba da zna poziciju elementa u rasponu? Želimo znati značenje ovog elementa!

Podsjetimo vas da je relativna pozicija vrijednosti koju tražimo (tj. broj reda i/ili stupca) upravo ono što moramo navesti za argumente red_br(broj_line) i/ili kolona_br(broj_stupca) funkcije INDEX(INDEX). Kao što se sjećate, funkcija INDEX može vratiti vrijednost na raskrsnici datog reda i stupca, ali ne može odrediti koji red i stupac nas zanimaju.

Kako koristiti INDEX i MATCH u Excelu

Sada kada znate osnovne informacije o ove dvije funkcije, vjerujem da već postaje jasno kako funkcioniraju TRAŽI I INDEX mogu raditi zajedno. TRAŽI određuje relativnu poziciju vrijednosti pretrage u datom rasponu ćelija, i INDEX koristi taj broj (ili brojeve) i vraća rezultat iz odgovarajuće ćelije.

Još uvijek nije sasvim jasno? Predstavite funkcije INDEX I TRAŽI u ovom obliku:

INDEX(,(PODRŽAVA ( vrijednost pretrage,kolona u kojoj gledamo,0))
=INDEX( kolona iz koje izdvajamo;(MACH( vrijednost pretrage;kolona u kojoj gledamo;0))

Mislim da će to biti još lakše razumjeti na primjeru. Pretpostavimo da imate sljedeću listu glavnih gradova država:

Nađimo populaciju jednog od glavnih gradova, na primjer, Japana, koristeći sljedeću formulu:

INDEX($D$2:$D$10,MACH("Japan",$B$2:$B$10,0))
=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))

Pogledajmo sada šta radi svaki element ove formule:

• Funkcija MATCH(MATCH) traži vrijednost “Japan” u koloni B, a konkretno – u ćelijama B2:B10, i vraća broj 3 , pošto je “Japan” na trećem mjestu liste.
• Funkcija INDEX(INDEX) koristi 3 za argument red_br(broj_reda), koji specificira iz kog reda treba vratiti vrijednost. One. dobijamo jednostavnu formulu:

  INDEX($D$2:$D$10,3)
  =INDEX($D$2:$D$10,3)

  Formula kaže otprilike ovako: pogledajte ćelije iz D2 to D10 i izdvojite vrijednost iz trećeg reda, odnosno iz ćelije D4, pošto brojanje počinje od drugog reda.

Ovo je rezultat koji dobijete u Excelu:

Važno! Broj redaka i stupaca u nizu koji funkcija koristi INDEX(INDEX), mora odgovarati vrijednostima argumenata red_br(broj_line) i kolona_br(broj_stupca) funkcije MATCH(MAČ). U suprotnom, rezultat formule će biti pogrešan.

Čekaj, čekaj... zašto jednostavno ne možemo koristiti funkciju VLOOKUP(VPR)? Ima li smisla gubiti vrijeme pokušavajući otkriti lavirinte? TRAŽI I INDEX?

VLOOKUP("Japan",$B$2:$D$2,3)
=VLOOKUP("Japan",$B$2:$D$2,3)

U ovom slučaju, nema smisla! Svrha ovog primjera je isključivo u svrhu demonstracije, tako da možete razumjeti kako funkcionira TRAŽI I INDEX rad u parovima. Sljedeći primjeri će vam pokazati pravu snagu paketa. INDEX I TRAŽI, koji se lako nosi sa mnogim teškim situacijama kada VLOOKUP nađe se u ćorsokaku.

Zašto je INDEX/MATCH bolji od VLOOKUP-a?

Prilikom odlučivanja koju formulu koristiti za vertikalno pretraživanje, većina Excel gurua vjeruje u to INDEX/TRAŽI mnogo bolje od VLOOKUP. Međutim, mnogi korisnici Excela i dalje pribjegavaju korištenju VLOOKUP, jer ova funkcija je mnogo jednostavnija. To se događa zato što vrlo mali broj ljudi u potpunosti razumije sve prednosti prelaska VLOOKUP po gomilu INDEX I TRAŽI, i niko ne želi gubiti vrijeme proučavajući složeniju formulu.

4 glavne prednosti korištenja MATCH/INDEX u Excelu:

1. Traži s desna na lijevo. Kao što svaki kompetentni korisnik Excel zna, VLOOKUP ne može gledati ulijevo, što znači da vrijednost koja se traži mora nužno biti u krajnjoj lijevoj koloni opsega koji se ispituje. U slučaju TRAŽI/INDEX, kolona za pretragu može biti na lijevoj ili desnoj strani raspona pretraživanja. Primjer: prikazat će ovu funkciju u akciji.

2. Sigurno dodajte ili uklonite stupce. Formule sa funkcijom VLOOKUP prestati raditi ili vratiti pogrešne vrijednosti ako uklonite ili dodate stupac u tabelu za pretraživanje. Za funkciju VLOOKUP bilo koja umetnuta ili uklonjena kolona će promijeniti rezultat formule jer sintaksa VLOOKUP zahtijeva da navedete cijeli raspon i određeni broj stupca iz kojeg želite izdvojiti podatke.

Na primjer, ako imate sto A1:C10, i želite da preuzmete podatke iz kolone B, tada trebate postaviti vrijednost 2 za argument col_index_num(broj_stupca) funkcije VLOOKUP, ovako:

VLOOKUP("vrijednost traženja",A1:C10,2)
=VLOOKUP("vrijednost traženja";A1:C10;2)

Ako kasnije umetnete novu kolonu između kolona A I B, tada će vrijednost argumenta morati biti promijenjena iz 2 on 3 , inače će formula vratiti rezultat iz novo umetnute kolone.

Koristeći TRAŽI/INDEX Možete ukloniti ili dodati kolone u raspon koji se proučava bez iskrivljavanja rezultata, jer je kolona koja sadrži željenu vrijednost direktno definirana. Zaista jeste velika prednost, posebno kada morate raditi s velikim količinama podataka. Možete dodavati i uklanjati stupce bez brige da ćete morati popraviti svaku funkciju koju koristite VLOOKUP.

3. Ne postoji ograničenje veličine tražene vrijednosti. Koristeći VLOOKUP, zapamtite da je dužina tražene vrijednosti ograničena na 255 znakova, inače rizikujete da dobijete grešku #VRIJEDNOST!(#VRIJEDNOST!). Dakle, ako tabela sadrži dugačke redove, jedino izvodljivo rješenje je korištenje INDEX/TRAŽI.

Recimo da koristite ovu formulu sa VLOOKUP, koji pretražuje ćelije iz B5 to D10 vrijednost specificiranu u ćeliji A2:

VLOOKUP(A2,B5:D10,3,FALSE)
=VLOOKUP(A2,B5:D10,3,FALSE)

Formula neće raditi ako je vrijednost u ćeliji A2 duži od 255 karaktera. Umjesto toga, trebate koristiti sličnu formulu INDEX/TRAŽI:

INDEX(D5:D10,PODRŽAVA (TRUE,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,PODRŽAVA (TRUE,INDEX(B5:B10=A2,0),0))

4. Više velike brzine rad. Ako radite s malim tablicama, razlika u performansama Excela najvjerovatnije neće biti primjetna, posebno u najnovije verzije. Ako radite s velikim tabelama koje sadrže hiljade redova i stotine formula za pretraživanje, Excel će raditi mnogo brže ako koristite TRAŽI I INDEX umjesto VLOOKUP. Općenito, ova zamjena povećava brzinu Excel rad on 13% .

Uticaj VLOOKUP Excel performanse su posebno uočljive ako radna knjiga sadrži stotine složenih formula niza, kao što su VLOOKUP+SUM. Činjenica je da provjera svake vrijednosti u nizu zahtijeva poseban poziv funkcije VLOOKUP. Stoga, što više vrijednosti sadrži niz i što više formula polja sadrži vaša tablica, Excel sporije radi.

S druge strane, formula sa funkcijama TRAŽI I INDEX jednostavno vrši pretragu i vraća rezultat, obavljajući sličan posao znatno brže.

INDEX i MACH - primjeri formula

Sada kada razumijete razloge zašto biste trebali naučiti funkcije TRAŽI I INDEX, pređimo na zabavni dio i vidimo kako možete primijeniti teorijsko znanje u praksi.

Kako pretraživati ​​s lijeve strane koristeći MATCH i INDEX

Bilo koji udžbenik VLOOKUP kaže da ova funkcija ne može gledati lijevo. One. ako kolona koju gledate nije krajnja lijeva u opsegu pretraživanja, onda nema šanse da dobijete iz VLOOKUPželjeni rezultat.

Funkcije TRAŽI I INDEX Excel je mnogo fleksibilniji i nije ga briga gde se nalazi kolona sa vrednošću koju želite da preuzmete. Na primjer, vratimo se na tabelu sa glavnim gradovima država i stanovništvom. Ovaj put ćemo napisati formulu TRAŽI/INDEX, koji će pokazati koje mjesto po broju stanovnika zauzima glavni grad Rusije (Moskva).

Kao što možete vidjeti na slici ispod, formula savršeno radi ovaj posao:

INDEX($A$2:$A$10,MACH("Rusija",$B$2:$B$10,0))

Sada ne biste trebali imati problema s razumijevanjem kako ova formula funkcionira:

• Prvo, upotrijebimo funkciju MATCH(MATCH), koji nalazi poziciju “Rusije” na listi:

  MATCH("Rusija",$B$2:$B$10,0))
  =MATCH("Rusija",$B$2:$B$10,0))

• Zatim postavite raspon za funkciju INDEX(INDEX) iz kojeg treba izdvojiti vrijednost. U našem slučaju jeste A2:A10.
• Zatim spojimo oba dijela i dobijemo formulu:

  INDEX($A$2:$A$10;MATCH("Rusija";$B$2:$B$10,0))
  =INDEX($A$2:$A$10,MATCH("Rusija",$B$2:$B$10,0))

trag: Ispravno rješenje je uvijek koristiti apsolutne reference za INDEX I TRAŽI, tako da se rasponi pretraživanja ne izgube pri kopiranju formule u druge ćelije.

Izračuni koristeći INDEX i MATCH u Excelu (PROSJEČAN, MAX, MIN)

Unutar možete ugnijezditi druge Excel funkcije INDEX I TRAŽI, na primjer, da pronađete minimalnu, maksimalnu ili najbližu prosječnoj vrijednosti. Evo nekoliko opcija za formule u odnosu na tabelu iz:

1. MAX(MAX). Formula pronalazi maksimum u koloni D C isti red:

INDEX($C$2:$C$10,MACH(MAX($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MACH(MAX($D$2:I$10),$D$2:D$10,0))

Rezultat: Peking

2. MIN(MIN). Formula pronalazi minimum u koloni D i vraća vrijednost iz kolone C isti red:

INDEX($C$2:$C$10,MACH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MACH(MIN($D$2:I$10),$D$2:D$10,0))

Rezultat: Lima

3. PROSJEČNO(PROSJEČNO). Formula izračunava prosjek raspona D2:D10, zatim pronalazi najbliži njemu i vraća vrijednost iz stupca C isti red:

INDEX($C$2:$C$10,PODRŽAVA (PROSJEK($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,PODRŽAVANJE(PROSEK($D$2:D$10),$D$2:D$10,1))

Rezultat: Moskva

Stvari koje treba zapamtiti kada koristite funkciju AVERAGE sa INDEX i MATCH

Korištenje funkcije PROSJEČNO u kombinaciji sa INDEX I TRAŽI, kao treći argument funkcije TRAŽI najčešće ćete morati naznačiti 1 ili -1 u slučaju da niste sigurni da opseg koji gledate sadrži vrijednost jednaku prosjeku. Ako ste sigurni da takva vrijednost postoji, stavite 0 da nađete tačno podudaranje.

• Ako naznačite 1 , vrijednosti u koloni za traženje trebaju biti poređane rastućim redoslijedom, a formula će vratiti maksimalnu vrijednost manju ili jednaku prosjeku.
• Ako naznačite -1 , vrijednosti u koloni za traženje treba da budu poređane u opadajućem redoslijedu i minimalna vrijednost veća ili jednaka prosjeku će biti vraćena.

U našem primjeru, vrijednosti u stupcu D su poredani uzlaznim redoslijedom, tako da koristimo tip usporedbe 1 . Formula INDEX/SEARCHPOZ vraća „Moskva“, pošto je stanovništvo grada Moskve najbliže prosječnoj vrijednosti (12.269.006).

Kako koristiti INDEX i MATCH za pretraživanje poznatog reda i stupca

Ova formula je ekvivalentna dvodimenzionalnom pretraživanju VLOOKUP i omogućava vam da pronađete vrijednost na sjecištu određenog reda i stupca.

U ovom primjeru formula INDEX/TRAŽIće biti vrlo slične formulama o kojima smo već raspravljali u ovoj lekciji, sa samo jednom razlikom. Pogodi koji?

Kao što se sjećate, sintaksa funkcije INDEX(INDEX) dozvoljava tri argumenta:

INDEX(niz,broj_reda,)
INDEX(niz, broj_reda, [broj_stupca])

I čestitam onima od vas koji su pogodili!

Počnimo tako što ćemo zapisati obrazac formule. Da bismo to učinili, uzmimo formulu koja nam je već poznata INDEX/TRAŽI i dodajte mu drugu funkciju TRAŽI, koji će vratiti broj kolone.

INDEX(Vaša tabela ,(MATCH(, kolona za pretraživanje,0)),(MACH(, red za pretraživanje,0))
=INDEX(Vaša tabela ,(MATCH( vrijednost za vertikalno pretraživanje,kolona za pretraživanje,0)),(MACH( vrijednost za horizontalno pretraživanje,red za pretraživanje,0))

Imajte na umu da za dvodimenzionalno pretraživanje morate navesti cijelu tablicu u argumentu niz(niz) funkcije INDEX(INDEX).

Isprobajmo sada ovaj obrazac u praksi. U nastavku vidite listu najnaseljenijih zemalja na svijetu. Recimo da je naš zadatak da saznamo broj stanovnika Sjedinjenih Država u 2015. godini.

U redu, hajde da zapišemo formulu. Kada trebam stvoriti složenu formulu u Excelu s ugniježđenim funkcijama, prvo zapišem svaku ugniježđenu funkciju posebno.

Dakle, počnimo s dvije funkcije TRAŽI, koji će vratiti brojeve redaka i stupaca za funkciju INDEX:

• MATCH za kolonu– gledamo u koloni B, odnosno u rasponu B2:B11, vrijednost koja je navedena u ćeliji H2(SAD). Funkcija će izgledati ovako:

  MEČ($H$2,$B$1:$B$11,0)
  =MACH($H$2,$B$1:$B$11,0)

  4 , pošto je “SAD” 4. element liste u koloni B(uključujući naslov).

• MATCH za niz– tražimo vrijednost ćelije H3(2015) u redu 1 , odnosno u ćelijama A1:E1:

  MEČ($H$3,$A$1:$E$1,0)
  =MATCH($H$3,$A$1:$E$1,0)

  Rezultat ove formule će biti 5 , pošto je „2015“ u 5. koloni.

Sada ubacujemo ove formule u funkciju INDEX i voila:

INDEX($A$1:$E$11,PODRŽAVA($H$2,$B$1:$B$11,0),PODRŽAVA($H$3,$A$1:$E$1,0))
=INDEX($A$1:$E$11,MACH($H$2,$B$1:$B$11,0),MACH($H$3,$A$1:$E$1,0))

Ako zamijenite funkcije TRAŽI na osnovu vrijednosti koje vraćaju, formula će postati laka i razumljiva:

INDEX($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))

Ova formula vraća vrijednost na raskrsnici 4th linije i 5 kolona u opsegu A1:E11, odnosno vrijednost ćelije E4. Samo? Da!

Višekriterijsko pretraživanje sa INDEX i MATCH

U tutorijalu na VLOOKUP pokazali smo primjer formule sa funkcijom VLOOKUP za pretragu koristeći više kriterijuma. Međutim, značajno ograničenje ovog rješenja bila je potreba za dodavanjem pomoćne kolone. Dobre vijesti: formula INDEX/TRAŽI može pretraživati ​​vrijednosti u dvije kolone, bez potrebe za kreiranjem pomoćne kolone!

Pretpostavimo da imamo listu narudžbi i želimo da pronađemo iznos na osnovu dva kriterijuma − ime kupca(Kupac) i proizvod(Proizvod). Stvar se komplikuje činjenicom da jedan kupac može kupiti više različitih proizvoda odjednom, a imena kupaca u tabeli na listu Tabela za traženje raspoređeni po slučajnom redoslijedu.

Evo formule INDEX/TRAŽI rješava problem:

(=INDEX("Tabela za traženje"!$A$2:$C$13,MATCH(1,(A2="Tabela za traženje"!$A$2:$A$13)*
(B2="Tabela traženja"!$B$2:$B$13),0),3))
(=INDEX("Tabela za traženje"!$A$2:$C$13;MATCH(1,(A2="Tabela za traženje"!$A$2:$A$13)*
(B2="Tabela traženja"!$B$2:$B$13);0);3))

Ova formula je složenija od drugih o kojima smo ranije raspravljali, ali je naoružana poznavanjem funkcija INDEX I TRAŽI Pobijedit ćeš je. Najteži dio je funkcija TRAŽI, mislim da to prvo treba objasniti.

MATCH(1,(A2="Tabela za traženje"!$A$2:$A$13),0)*(B2="Tabela za traženje"!$B$2:$B$13)
MATCH(1;(A2="Tabela za traženje"!$A$2:$A$13);0)*(B2="Tabela za traženje"!$B$2:$B$13)

U gornjoj formuli, vrijednost koju tražimo je 1 , a niz pretraživanja je rezultat množenja. Dobro, šta da množimo i zašto? Pogledajmo sve redom:

• Uzmite prvu vrijednost u koloni A(Kupac) na listu Glavni sto i uporedite ga sa svim imenima kupaca u tabeli na listu Tabela za traženje(A2:A13).
• Ako se pronađe podudaranje, jednačina se vraća 1 (TAČNO), a ako nije - 0 (LAŽ).
• Zatim radimo isto za vrijednosti stupaca B(Proizvod).
• Zatim množimo dobijene rezultate (1 i 0). Dobit ćete samo ako se pronađu podudaranja u obje kolone (tj. oba kriterija su tačna). 1 . Ako su oba kriterijuma netačna, ili je samo jedan od njih zadovoljen, dobićete 0 .

Sada razumete zašto smo pitali 1 , koja je željena vrijednost? Tačno je da je funkcija TRAŽI vratio poziciju tek kada su ispunjena oba kriterijuma.

Imajte na umu: U ovom slučaju, morate koristiti treći neobavezni argument za funkciju INDEX. To je neophodno jer u prvom argumentu specificiramo cijelu tablicu i moramo reći funkciji iz kojeg stupca da preuzme vrijednost. U našem slučaju ovo je kolona C(Sum), i tako smo ušli 3 .

I konačno, jer moramo provjeriti svaku ćeliju u nizu, ova formula mora biti formula niza. To možete vidjeti po vitičastim zagradama u kojima je zatvoren. Dakle, kada završite s unosom formule, ne zaboravite kliknuti Ctrl+Shift+Enter.

Ako je sve urađeno kako treba, dobićete rezultat kao na slici ispod:

INDEX i MATCH u kombinaciji sa IFERROR u Excelu

Kao što ste vjerovatno već primijetili (više puta), ako unesete pogrešnu vrijednost, na primjer, onu koja nije u nizu koji se pregledava, formula INDEX/TRAŽI prijavljuje grešku #N/A(#N/A) ili #VRIJEDNOST!(#VRIJEDNOST!). Ako takvu poruku želite zamijeniti nečim razumljivijim, možete umetnuti formulu sa INDEX I TRAŽI u funkciju IFERROR.

Sintaksa funkcije IFERROR vrlo jednostavno:

IFERROR(vrijednost,vrijednost_ako_greška)
IFERROR(vrijednost;vrijednost_ako_greška)

Gdje je argument vrijednost(vrijednost) je vrijednost koja se provjerava radi greške (u našem slučaju rezultat formule INDEX/TRAŽI); i argument vrijednost_ako_greška(value_if_error) je vrijednost koja se vraća ako formula izbaci grešku.

Na primjer, možete umetnuti u funkciju IFERROR ovako:

IFGREŠKA(INDEX($A$1:$E$11,MACH($G$2,$B$1:$B$11,0),MACH($G$3,$A$1:$E$1,0)),
"Podudaranja nisu pronađena. Pokušajte ponovo!") =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MACH($G$3,$A$1 : $E$1;0));
"Nema pronađenih podudaranja. Pokušajte ponovo!")

A sada, ako neko unese pogrešnu vrijednost, formula će dati ovaj rezultat:

Ako želite da ostavite ćeliju praznu u slučaju greške, možete koristiti navodnike (“”) kao vrijednost drugog argumenta funkcije IFERROR. ovako:

IFGREŠKA(INDEX(niz,PODRŽAVANJE(vrijednost_potraži,niz_potraži,0),"")
IFGREŠKA(INDEX(niz,PODRŽAVANJE(vrijednost_pretraživanja,pogledani_niz,0),"")

Nadam se da vam je barem jedna formula opisana u ovom udžbeniku bila korisna. Ako ste naišli na druge probleme pretraživanja za koje niste mogli pronaći odgovarajuće rješenje među informacijama u ovoj lekciji, slobodno opišite svoj problem u komentarima, a mi ćemo ga svi zajedno pokušati riješiti.

Recimo da vaš izvještaj sadrži tabelu s puno podataka u mnogo kolona. Provođenje vizuelne analize takvih tabela je izuzetno teško. A jedan od zadataka rada sa izvještajem je analiza podataka koji se odnose na naslove redova i kolona koji se odnose na određeni mjesec. Na prvi pogled, ovo je vrlo jednostavan zadatak, ali se ne može riješiti samo jednim standardna funkcija. Da, naravno možete koristiti alat: “HOME” - “Uređivanje” - “Pronađi” CTRL + F da otvorite prozor za pretraživanje vrijednosti na Excel list. Ili kreirajte pravilo uvjetnog oblikovanja za tablicu. Ali tada će biti nemoguće izvršiti daljnja izračunavanja s dobivenim rezultatima. Stoga je potrebno kreirati i pravilno primijeniti odgovarajuću formulu.

Pronalaženje vrijednosti u Excel nizu

Rješenje problema izgleda otprilike ovako:

• u ćeliju B1 upisujemo podatke koji nas zanimaju;
• ćelija B2 će prikazati zaglavlje kolone koje sadrži vrijednost ćelije B1
• ćelija B3 će prikazati ime reda koji sadrži vrijednost ćelije B1.

Zapravo trebate izvršiti pretragu koordinata u Excelu. čemu ovo služi? Često moramo dobiti koordinate tablice po vrijednosti. Pomalo kao analiza obrnute matrice. Konkretan primjer ukratko izgleda otprilike ovako. Postavljeni cilj u brojevima je početna vrijednost koju trebate odrediti ko je i kada najbliži ovom cilju. Na primjer, koristimo jednostavnu matricu podataka sa izvještajem o broju prodatih roba za tri kvartala, kao što je prikazano na donjoj slici. Važno je da se svi numerički pokazatelji podudaraju. Ako ne želite ručno kreirati i popunjavati Excel tabela od nule, onda na kraju članka možete preuzeti gotov primjer.

Uzastopno ćemo razmotriti opcije rješenja različite složenosti, a na kraju članka - konačni rezultat.

Pronalaženje vrijednosti u Excel koloni

Prvo, naučimo kako dobiti zaglavlja stupaca tablice prema vrijednosti. Da biste to učinili, slijedite ove korake:

1. U ćeliju B1 unesite vrijednost preuzetu iz tabele 5277 i označite njenu pozadinu plavom bojom kako bi polje za unos bilo čitljivo (kasnije ćemo unijeti druge brojeve u ćeliju B1 kako bismo eksperimentirali s novim vrijednostima).
2. U ćeliju C2 unesite formulu da dobijete zaglavlje stupca tabele koji sadrži ovu vrijednost:
3. Nakon što unesete formulu, pritisnite kombinaciju prečaca CTRL+SHIFT+Enter za potvrdu, jer formula mora biti izvršena u nizu. Ako je sve urađeno ispravno, duž rubova trake formule pojavit će se vitičaste zagrade ( ).

Pronalaženje vrijednosti u Excel stringu

Sada dobijamo broj reda za istu vrijednost (5277). Da biste to učinili, u ćeliju C3 unesite sljedeću formulu:

Nakon što unesete formulu za potvrdu, pritisnite kombinaciju tipki CTRL+SHIFT+Enter ponovo i dobijete rezultat:

Formula je vratila broj 9 – pronašla je zaglavlje reda lista koristeći odgovarajuću vrijednost tabele. Kao rezultat, imamo punu adresu vrijednosti D9.



Kako dobiti naslov kolone i naslov reda u tabeli

Sada hajde da naučimo kako da dobijemo koordinate trenutne tabele, a ne celog lista, po vrednosti. Jednom riječju, trebamo pronaći po vrijednosti 5277 umjesto D9 da dobijemo zaglavlja:

• za kolonu tabele – mart;
• za liniju – Proizvod4.

Da bismo riješili ovaj problem, koristit ćemo formulu s vrijednostima koje smo već dobili u ćelijama C2 i C3. Da bismo to uradili radimo ovo:

Kao rezultat, interne koordinate tabele se dobijaju po vrednosti – mart; Proizvod 4:

Pronalaženje istih vrijednosti u Excel rasponu

Da bismo provjerili prisustvo duplikata među vrijednostima tablice, kreirat ćemo formulu koja nas može obavijestiti o prisutnosti duplikata i prebrojati njihov broj. Da biste to učinili, unesite formulu u ćeliju E2:

Štaviše, za opseg sekcije tabele kreiraćemo pravilo uslovnog oblikovanja:

Kao što vidite, ako postoje duplikati, formula za naslove uzima naslov od prvog duplikata horizontalno (s lijeva na desno). A formula za dobijanje imena (broja) reda uzima broj od prvog duplikata po vertikali (od vrha do dna). Da popravim ovu odluku postoje 2 načina:

U ovom slučaju mijenjamo formule ili jednu ili drugu, ali ne obje odjednom. Vrijedi podsjetiti da bi stara formula trebala ostati u ćeliji C3:

Ovdje su vertikalne koordinate prvog duplikata ispravno prikazane (od vrha do dna) - I7 za list i kolovoz; Stavka 2 za sto. Ostavimo ovu opciju za sljedeći konačni primjer.

Pronalaženje najbliže vrijednosti u Excel rasponu

Ova tabela još uvek nije savršena. Uostalom, kada analizirate, morate tačno znati sve njegove vrijednosti. Ako formula ne pronađe broj unet u ćeliju B1 u tabeli, onda se vraća greška - #VRIJEDNOST! Bilo bi idealno da formula, ako originalni broj nije u tabeli, odabere najbližu vrednost koju tabela sadrži. Da biste kreirali takav program za analizu tabela, unesite novu formulu u ćeliju F1:

Nakon čega slijedi u svim ostalim formulama, promijenite vezu umjesto B1 treba da bude F1! Također morate promijeniti vezu u uslovnom formatu. Odaberite: “HOME” - “Styles” - “Conditional Formatting” - “Manage Rules” - “Edit Rule”. I ovdje u parametrima navedite F1 umjesto B1. Da biste provjerili rad programa, unesite u ćeliju B1 broj koji nije u tabeli, na primjer: 8000. Ovo će dovesti do konačnog rezultata:

Sada možete unijeti bilo koju početnu vrijednost, a program će sam odabrati najbliži broj koji tabela sadrži. Zatim prikazuje zaglavlje kolone i ime reda za trenutnu vrijednost. Na primjer, ako unesete broj 5000, dobićemo novi rezultat:

Naš Excel program je pronašao najbližu vrijednost od 4965 originalnoj vrijednosti - 5000. Takav program može biti koristan za automatsko rješavanje različitih analitičkih problema u poslovnom planiranju, postavljanju ciljeva, pronalaženju racionalnog rješenja itd. A rezultirajući redovi i kolone vam omogućavaju da dodatno proširite računske mogućnosti ove vrste izvještaja koristeći nove Excel formule.

Često, kada radite s podacima u Excelu, morate selektivno brisati ili sakrivati ​​dio informacija, najčešće su to redovi ili stupci koji sadrže ili ne sadrže određene riječi, slova, brojeve, simbole ili njihove kombinacije. Ovi pomažu u tome standardnim sredstvima Excel poput pretraživanja, filtera i naprednog filtera. Ako ovi alati nisu dovoljni za rješavanje problema, VBA priskače u pomoć.

Kako pronaći date riječi u Excel stringovima? Pronađite riječi, slova, brojeve i simbole u Excelu

Najjednostavniji način traženja date riječi, slova, broja, simbola ili njihove kombinacije je standardna pretraga. Svi parametri pretraživanja unose se u dijaloški okvir „Pronađi i zamijeni“, koji se može pozvati iz glavnog menija ili kombinacijom prečaca „Ctrl+f“ (gdje je f prvo slovo engleska riječ naći - naći). Pored redovnog pretraživanja, možete izvršiti i pretragu sa zamjenom.

Koristite filtere da biste pronašli određenu riječ, slovo, broj ili simbol u Excelu

Možete koristiti filtriranje da odaberete redove koji sadrže informacije koje definira korisnik. U programu Excel 2007 i novijim, na primjer, pored uobičajenog filtera teksta, postoji filter po boji popune ćelije i boji fonta. Filter teksta vam omogućava da koristite uslove kao što su "jednako...", "nije jednako...", "počinje sa...", "završava sa...", "sadrži...", “ne sadrži...”. Nakon što su svi potrebni redovi filtrirani, s njima možete izvršiti bilo koju radnju, uključujući brisanje redova.

Kako programski pronaći i izbrisati određene redove u Excelu koristeći VBA Like operator?

Iz raznih razloga standardno Excel alati nisu uvijek pogodni za rješavanje određenih problema. Ispod je programski kod za makro koji vam omogućava da pronađete tekst naveden u predlošku u ćelijama korištenog raspona i izbrišete cijeli red aktivnog radnog lista koji sadrži ćeliju sa navedenim tekstom. Tekst za pretragu se dodjeljuje varijabli "Template" pomoću specijalni znakovi koji odgovaraju uzorku .

Sub Delete_Strok_Po_Shablonu() Dim r As Long, FirstRow As Long, LastRow As Long Dim Region As Range, iRow As Range, Cell As Range Dim Template As String Template = "ovdje unesite tekst za pretragu" Postavite Region = ActiveSheet.UsedRange FirstRow = Region Red LastRow = Region.Row - 1 + Region.Rows.Count Za r = LastRow To FirstRow Korak -1 Postavite iRow = Region.Row(r - FirstRow + 1) Za svaku ćeliju u iRow.Cells Ako je predložak poput ćelije onda redovi. ( r).Delete End If Next Cell Next r End Sub

Ovaj makro koristi nejasnu pretragu za pronalaženje potrebnih fraza i VBA operator poređenja Like, koji vam omogućava da uporedite nizove sa šablonom. Kada upoređuje nizove, ovaj operator razlikuje velika i mala slova, a rezultat poređenja ovisi o instrukciji Opcija Usporedi .

VBA funkcije Instr i Find se također mogu koristiti za rješavanje sličnih problema.

Izvodi slične radnje Excel dodatak, čija upotreba vam omogućava da unesete traženi tekst bez specijalni znakovi u dijaloškom okviru i navedite različita područja pretraživanja.

Dobar dan dragi stanovnici Habra!

S vremena na vrijeme, neki (a možda i više od nekih) od nas se moraju nositi sa zadacima obrade malih količina podataka, od sastavljanja i analize kućnog budžeta do bilo kakvih proračuna za posao, učenje itd. Možda je najprikladniji alat za to Microsoft Excel(ili možda drugi analozi, ali su manje uobičajeni).

Pretraga mi je dala samo jedan članak na Habréu na sličnu temu – “Talmud koristeći formule u Google SpreadSheet-u”. To daje dobar opis osnovne stvari za rad u excelu (iako se ne radi 100% o samom excelu).

Tako se, akumulirajući određeni skup zahtjeva/zadataka, javila ideja da ih otkucamo i predložimo moguća rješenja(iako nije sve moguće, ali brzo daje rezultate).

Govorit ćemo o rješavanju najčešćih problema s kojima se korisnici susreću.

Opis rješenja je strukturiran na sljedeći način: dat je slučaj koji sadrži početni zadatak, koji se postepeno usložnjava, a za svaki korak je dato detaljno rješenje sa objašnjenjima. Nazivi funkcija će biti dati na ruskom jeziku, ali će originalni naziv na ruskom biti dat u zagradama pri prvom spominjanju. engleski(pošto prema iskustvu, velika većina korisnika ima instaliranu rusku verziju).

Case_1: Logičke funkcije i funkcije podudaranja
“Imam skup vrijednosti u tabeli i potrebno je da se kada se ispuni određeni uslov/skup uslova prikaže određena vrijednost” (c) Korisnik

Podaci se obično prikazuju u obliku tabele:

stanje:

• ako je vrijednost u koloni "Količina" veća od 5,
• tada trebate prikazati vrijednost "Nije potreban nalog" u koloni "Rezultat",

U tome će nam pomoći formula “IF”, koja se odnosi na logičke formule i može proizvesti u rješenju bilo koje vrijednosti koje unaprijed upišemo u formulu. Imajte na umu da se sve tekstualne vrijednosti pišu pomoću navodnika.

Sintaksa formule je sljedeća:
IF(logički_izraz, [vrijednost_ako_tačno], [vrijednost_ako_netačno])

• Logički izraz je izraz koji daje vrijednost TRUE ili FALSE.
• Value_if_true - vrijednost koja se ispisuje ako je logički izraz istinit
• Value_if_false - vrijednost koja se ispisuje ako je Boolean izraz lažan

Sintaksa formule za rješenje:

=IF(C5>5, “Nije potrebna narudžba”, “Potrebna narudžba”)

Na izlazu dobijamo rezultat:

Dešava se da je uslov složeniji, na primer, ispunjenje 2 ili više uslova:

• ako je vrijednost u koloni “Količina” veća od 5, a vrijednost u koloni “Vrsta” je “A”

U ovom slučaju, ne možemo se više ograničiti na korištenje samo formule „IF“, potrebno je dodati drugu formulu u njenu sintaksi. I ovo će biti još jedna logična formula "I".
Sintaksa formule je sljedeća:
AND(boolean_value1, [boolean_value2], ...)

• Boolean_value1-2, itd. - uslov koji treba testirati, čija procjena rezultira vrijednošću TRUE ili FALSE

Izlaz rezultata u ćeliju D2:

=IF(AND(C2>5,B2=“A”),1,0)

Tako, koristeći kombinaciju 2 formule, nalazimo rješenje za naš problem i dobivamo rezultat:

Pokušajmo zakomplicirati zadatak - novi uvjet:

• ako je vrijednost u koloni "Količina" 10, a vrijednost u koloni "Vrsta" je "A"
• ili je vrijednost u koloni Količina veća ili jednaka 5, a vrijednost vrste je B
• tada trebate prikazati vrijednost “1” u koloni “Rezultat”, inače “0”.

Sintaksa rješenja će biti sljedeća:
Izlaz rezultata u ćeliju D2:

=IF(OR(AND(C2=10,B2=“A”); AND(C2>=5,B2=”B”)),1,0)

Kao što vidite iz unosa, formula IF ima jedan uslov ILI i dva uslova I uključena u nju. Ako barem jedan od uslova 2. nivoa ima vrijednost “TRUE”, tada će rezultat “1” biti prikazan u koloni “Rezultat”, u suprotnom će biti “0”.
rezultat:

A sada da pređemo na sledeću situaciju:
Zamislimo da bi u zavisnosti od vrijednosti u stupcu „Stanje“ trebalo da se prikaže određeno stanje u koloni "Rezultat" ispod je korespondencija između vrijednosti i rezultata.
stanje:

• 1 = A
• 2 = B
• 3 = B
• 4 = G

Prilikom rješavanja problema pomoću funkcije "IF", sintaksa će biti sljedeća:

=IF(A2=1,"A", IF(A2=2,"B", IF(A2=3,"C", IF(A2=4,"D",0))))

rezultat:

Kao što vidite, pisanje takve formule ne samo da nije baš zgodno i glomazno, već može potrajati i neko vrijeme da je neiskusni korisnik uredi u slučaju greške.
Nedostatak ovog pristupa je što je primjenjiv za mali broj uvjeta, jer će se svi morati unijeti ručno i naša formula “naduvana” do velikih veličina, međutim, pristup se odlikuje potpunom “svejednošću” vrijednosti i svestranost upotrebe.

Alternativno rješenje_1:
Koristeći formulu CHOOSE
Sintaksa funkcije:
SELECT(broj_indeksa, vrijednost1, [vrijednost2], ...)

• Index_number - broj odabranog argumenta vrijednosti. Indeksni broj mora biti broj između 1 i 254, formula ili referenca na ćeliju koja sadrži broj između 1 i 254.
• Vrijednost1, vrijednost2,... - vrijednost od 1 do 254 argumenta vrijednosti, od kojih funkcija “SELECT” koristeći indeksni broj, bira vrijednost ili radnju koju treba izvršiti. Argumenti mogu biti brojevi, reference ćelija, specifična imena, formule, funkcije ili tekst.

Kada ga koristimo, odmah unosimo rezultate uslova u zavisnosti od navedenih vrednosti.
stanje:

• 1 = A
• 2 = B
• 3 = B
• 4 = G

Sintaksa formule:

=IZBOR(A2, “A”, “B”, “C”, “D”)

Rezultat je sličan rješenju lanca funkcija IF iznad.
Sljedeća ograničenja primjenjuju se prilikom primjene ove formule:
U ćeliju „A2“ (indeksni broj) mogu se unijeti samo brojevi, a vrijednosti rezultata će biti prikazane uzlaznim redoslijedom od 1 do 254 vrijednosti.
Drugim riječima, funkcija će raditi samo ako ćelija “A2” sadrži brojeve od 1 do 254 u rastućem redoslijedu, a to nameće određena ograničenja pri korištenju ove formule.
One. ako želimo da se pri navođenju broja 5 prikaže vrijednost “G”,

• 1 = A
• 2 = B
• 3 = B
• 5 = G

tada će formula imati sljedeću sintaksu:
Ispis rezultata u ćeliju B2:

=IZBOR(A31, “A”, “B”, “C”, “D”)

Kao što vidite, moramo ostaviti vrijednost “4” u formuli praznom, a rezultat “G” prenijeti na serijski broj “5”.

Alternativno rješenje_2:
Ovdje dolazimo do jednog od najpopularnijih Excel funkcije, čije ovladavanje automatski pretvara svakog kancelarijskog radnika u „iskusnog korisnika Excela“ /sarkazam/.
Sintaksa formule:
VLOOKUP(vrijednost_pretraživanja, tabela, broj_stupca, [interval_pretraživanja])

• Search_value – vrijednost koju traži funkcija.
• Tabela je raspon ćelija koje sadrže podatke. U tim ćelijama će se vršiti pretraga. Vrijednosti mogu biti tekstualne, numeričke ili logičke vrijednosti.
• Column_number - broj kolone u argumentu “Table” iz kojeg će se izvesti vrijednost ako postoji podudaranje. Važno je shvatiti da se kolone ne broje duž opće mreže listova (A.B,C,D, itd.), već unutar niza navedenog u argumentu „Tabela“.
• Interval_lookup - određuje da li funkcija treba pronaći točno ili približno podudaranje.

Važno: Funkcija VLOOKUP traži podudaranje samo na prvom jedinstvenom zapisu ako je lookup_value prisutna u argumentu tablice više puta i ima različita značenja, tada će funkcija “VLOOKUP” pronaći samo PRVO podudaranje, rezultati za sva ostala podudaranja neće biti prikazani Upotreba formule “VLOOKUP” povezana je s drugim pristupom u radu s podacima, odnosno formiranjem “direktorija”. ”.
Suština pristupa je kreiranje "direktorija" korespondencije argumenta "Searched_value" određenom rezultatu, odvojeno od glavnog niza, u kojem su upisani uslovi i njihove odgovarajuće vrijednosti:

Zatim se u radnom dijelu tabele upisuje formula sa vezom na ranije popunjenu referentnu knjigu. One. u imeniku u koloni “D” se traži vrijednost iz kolone “A” i kada se pronađe podudarnost, vrijednost iz kolone “E” se prikazuje u koloni “B”.
Sintaksa formule:
Ispis rezultata u ćeliju B2:

Rezultat:

Sada zamislite situaciju u kojoj trebate povući podatke u jednu tablicu iz druge, ali tabele nisu identične. Pogledajte primjer ispod

Vidi se da se redovi u kolonama “Proizvod” u obje tabele ne poklapaju, međutim, to nije prepreka za korištenje funkcije “VLOOKUP”.
Ispis rezultata u ćeliju B2:

Ali pri rješavanju se suočavamo sa novi problem– prilikom „razvlačenja“ formule koju smo napisali desno od kolone „B“ do kolone „E“, moraćemo ručno da zamenimo argument „broj_kolone“. Ovo je radno intenzivan i nezahvalan zadatak, stoga nam u pomoć dolazi još jedna funkcija - „KOLONA“ (COLUMN).
Sintaksa funkcije:
COLUMN([link])

• Referenca je ćelija ili raspon ćelija za koje želite vratiti broj stupca.

Ako koristite zapis kao što je:

tada će funkcija prikazati broj tekuće kolone (u čijoj je ćeliji upisana formula).
Rezultat je broj koji se može koristiti u funkciji “VLOOKUP”, koju ćemo koristiti i dobiti sljedeću formulu:
Ispis rezultata u ćeliju B2:

=VLOOKUP($A3,$H$3:$M$6, COLUMN(),0)

Funkcija "COLUMN" će odrediti broj tekuće kolone, koju će koristiti argument "Column_Number" za određivanje broja kolone za pretraživanje u direktoriju.
Alternativno, možete koristiti sljedeću konstrukciju:

Umjesto broja "1", možete koristiti bilo koji broj (i ne samo oduzeti, već ga dodati i rezultirajućoj vrijednosti) da biste dobili željeni rezultat, ako ne želite da se pozivate na određenu ćeliju u koloni sa broj koji nam je potreban.
Rezultirajući rezultat:

Nastavljamo razvijati temu i kompliciramo uvjet: zamislite da imamo dva direktorija s različitim podacima o proizvodima i trebamo prikazati vrijednosti u tablici s rezultatom ovisno o tome koji je tip imenika naznačen u "Imeniku" kolona
stanje:

• Ako je u koloni “Imenik” naveden broj 1, podatke treba povući iz tabele “Imenik_1”, ako je broj 2, onda iz tabele “Imenik_2” u skladu sa navedenim mjesecom

Rešenje koje mi odmah pada na pamet je sledeće:

=IF($B3=1; VLOOKUP($A3,$G$3:$I$6; COLUMN()-1,0); VLOOKUP($A3,$K$3:$M$6; COLUMN()-1;0 ))

Pros: naziv direktorija može biti bilo koji (tekst, brojevi i njihova kombinacija), nedostaci - ne uklapa se dobro ako ima više od 3 opcije.
Ako su brojevi imenika uvijek brojevi, ima smisla koristiti sljedeće rješenje:
Izlaz rezultata u ćeliju C3:

=VLOOKUP($A3, SELECT($B3,$G$3:$I$6,$K$3:$M$6), COLUMN()-1,0)

Pros: formula može uključivati ​​do 254 imena direktorija, kontra - njihovo ime mora biti strogo numerički.
Rezultat za formulu pomoću funkcije SELECT:

Bonus: VLOOKUP zasnovan na dvije ili više karakteristika u argumentu “search_value”.
stanje:

• Zamislimo da, kao i uvijek, imamo niz podataka u tabelarnom obliku (ako ne, onda podatke donosimo u njega trebamo dobiti vrijednosti iz niza na osnovu određenih karakteristika i smjestiti ih u drugi tabelarni oblik); .

Obje tabele su prikazane u nastavku:

Kao što se vidi iz obrazaca tabele, svaki artikal ne samo da ima naziv (koji nije jedinstven), već pripada određenoj klasi i ima sopstvenu opciju pakovanja.
Koristeći kombinaciju imena i klase i pakovanja, možemo kreirati novu karakteristiku za to, u tabeli sa podacima kreiramo dodatnu kolonu „Dodatna karakteristika“ koju popunjavamo sledećom formulom:

Pomoću simbola "&" kombiniramo tri karakteristike u jednu (razdjelnik između riječi može biti bilo šta, ili nikako, glavna stvar je koristiti slično pravilo za pretraživanje)
Analog formule može biti funkcija "CONCATENATE", u kom slučaju će izgledati ovako:

=CONCATENATE(H3;"_";I3;"_";J3)

Nakon što je kreiran dodatni atribut za svaki zapis u tablici podataka, nastavljamo s pisanjem funkcije pretraživanja za ovaj atribut, koja će izgledati ovako:
Izlaz rezultata u ćeliju D3:

=IFGREŠKA(VLOOKUP(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

U funkciji “VLOOKUP” kao argument “search_value” koristimo istu kombinaciju tri karakteristike (name_class_packing), ali je već uzimamo u tablicu za popunjavanje i unosimo direktno u argument (alternativno, možemo odabrati vrijednost za argument u dodatnoj koloni u tabeli koju treba popuniti, ali ova radnja neće biti potrebna).
Podsjećam vas da je korištenje funkcije “IFERROR” neophodno ako željena vrijednost nije pronađena, a funkcija “VLOOKUP” će nam dati vrijednost “#N/A” (više o tome u nastavku).
Rezultat je na slici ispod:

Ova tehnika se može koristiti za veći broj karakteristika, jedini uslov je jedinstvenost rezultirajućih kombinacija ako se to ne ispuni, rezultat će biti netačan;

Case_3 Traženje vrijednosti u nizu ili kada nam VLOOKUP ne može pomoći

Razmotrimo situaciju u kojoj moramo razumjeti da li niz ćelija sadrži vrijednosti koje su nam potrebne.
zadatak:

• kolona “Uvjet pretraživanja” sadrži vrijednost i morate odrediti da li je prisutna u koloni “Search Array”

Vizuelno sve izgleda ovako:

Kao što vidimo, funkcija “VLOOKUP” je ovdje nemoćna, jer Ne tražimo tačno podudaranje, već prisutnost vrijednosti koja nam je potrebna u ćeliji.
Za rješavanje problema potrebno je koristiti kombinaciju nekoliko funkcija, i to:
"ako"
"AKO GREŠKA"
"NIŽA"
"PRONAĐI"

Po redu o svima, već smo ranije govorili o “IF”, pa idemo na funkciju “IFERROR”.

IFERROR(vrijednost, greška_vrijednost)

• Vrijednost je argument koji se provjerava za greške.
• Value_on_error - vrijednost koja se vraća ako postoji greška prilikom izračunavanja formule. Moguće su sljedeće vrste grešaka: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? i #PRAZNO!.

Važno: ova formula je gotovo uvijek potrebna kada radite s nizovima informacija i referentnih knjiga, jer Često se dešava da vrijednost koju tražite nije u direktoriju, a u tom slučaju funkcija vraća grešku. Ako je greška prikazana u ćeliji i ćelija je uključena, na primjer, u proračun, tada će se pojaviti i s greškom. Osim toga, ćelijama u kojima je formula vratila grešku mogu se dodijeliti različite vrijednosti, što olakšava njihovu statističku obradu. Također, u slučaju greške, možete obavljati i druge funkcije, što je vrlo zgodno kada radite s nizovima i omogućava vam da gradite formule uzimajući u obzir prilično razgranate uvjete.

"NIŽA"

• Tekst - tekst pretvoren u mala slova.

Važno: funkcija "LOWER" ne zamjenjuje znakove koji nisu slova.
Uloga u formuli: budući da funkcija “PRONAĐI” pretražuje i uzima u obzir velika i mala slova teksta, potrebno je sav tekst konvertovati u ista velika i mala slova, inače “čaj” neće biti jednak “čaj” itd. Ovo je relevantno ako vrijednost registra nije uvjet za traženje i odabir vrijednosti, inače se formula “NIŽA” ne može koristiti, pa će pretraga biti preciznija.

Pogledajmo sada pobliže sintaksu funkcije FIND.

FIND(tekst_pretraživanja, pregledani_tekst, [početna_pozicija])

• Search_text - tekst koji treba pronaći.
• Search_text - tekst u kojem želite pronaći traženi tekst.
• Početna_pozicija - znak od kojeg treba započeti pretragu. Prvi znak u tekstu "view_text" je numerisan 1. Ako broj nije naveden, podrazumevano je 1.

Sintaksa formule rješenja će izgledati ovako:
Ispis rezultata u ćeliju B2:

=IF(IFGREŠKA(PRONAĐI(LINIJ(A2), LINIJA(E2),1),0)=0,"neuspešno","bingo!")

Analizirajmo logiku formule korak po korak:

1. LOWER(A2) – pretvara argument Search_Text u ćeliji A2 u tekst malim slovima
2. Funkcija FIND započinje traženje konvertovanog argumenta Search_Text u nizu Search_Text, koji je konvertovan pomoću LOWER(E2) funkcije, također u tekst malim slovima.
3. Ako funkcija pronađe podudaranje, tj. vraća serijski broj prvog znaka podudarne riječi/vrijednosti, aktivira se uvjet TRUE u formuli “IF”, jer rezultirajuća vrijednost nije nula. Kao rezultat toga, kolona “Rezultat” će prikazati vrijednost “Bingo!”
4. Ako, međutim, funkcija ne pronađe podudaranje, tj. serijski broj prvog znaka podudarne riječi/vrijednosti nije naznačen i umjesto vrijednosti se vraća greška, pokreće se uvjet uključen u formulu “IFERROR” i vraća vrijednost jednaku “0”, što odgovara na uslov LAŽ u formuli “IF”, jer rezultirajuća vrijednost je “0”. Kao rezultat, vrijednost “fail” će biti prikazana u koloni “Result”.

Kao što se može vidjeti iz gornje slike, zahvaljujući funkcijama “LOW” i “FIND” pronalazimo željene vrijednosti bez obzira na slučaj znakova i lokaciju u ćeliji, ali moramo obratiti pažnju na red 5. .
Pojam za pretraživanje je postavljen na "111", ali niz za pretraživanje sadrži vrijednost "1111111 kolačića", ali formula vraća rezultat "Bingo!" To se događa jer je vrijednost "111" uključena u seriju vrijednosti "1111111", kao rezultat je pronađeno podudaranje. Inače ovo stanje neće raditi.

Case_4 Traženje vrijednosti u nizu na osnovu nekoliko uslova ili kada nam VLOOKUP još više ne može pomoći

Zamislimo situaciju u kojoj trebate pronaći vrijednost iz "Tabele s rezultatom" u dvodimenzionalnom nizu "Direktorijum" prema nekoliko uslova, odnosno po vrijednosti "Naziv" i "Mjesec".
Tablični oblik zadatka će izgledati ovako:

stanje:

• Podatke je potrebno povući u tabelu sa rezultatom u skladu sa podudarnošću uslova „Ime“ i „Mjesec“.

Za rješavanje ovog problema prikladna je kombinacija funkcija “INDEX” i “SEARCH”.

Sintaksa funkcije INDEX

INDEX(niz, broj_reda, [broj_stupca])

• Niz - raspon ćelija iz kojih će biti prikazane vrijednosti ako se njihovi uvjeti pretraživanja podudaraju.
• Ako niz sadrži samo jedan red ili jednu kolonu, argument row_number ili column_number, respektivno, nije obavezan.
• Ako niz zauzima više od jednog reda i jednog stupca, a dat je samo jedan od argumenata row_number i column_number, tada funkcija INDEX vraća niz koji se sastoji od cijelog reda ili cijelog stupca argumenta niza.
• Line_number - broj reda u nizu iz kojeg želite da vratite vrijednost.
• column_number - broj kolone u nizu iz koje želite da vratite vrijednost.

Drugim riječima, funkcija vraća iz navedenog niza u argumentu “Niz” vrijednost koja se nalazi na sjecištu koordinata navedenih u argumentima “Row_Number” i “Column_Number”.

Sintaksa funkcije MATCH

MATCH(vrijednost_pretraživanja, niz_potraživanja, [tip_podudaranja])

• Lookup_value je vrijednost koja odgovara vrijednostima u argumentu lookup_array. Argument lookup_value može biti vrijednost (broj, tekst ili Boolean) ili referenca na ćeliju koja sadrži takvu vrijednost.
• Search_array - opseg ćelija u kojima se vrši pretraga.
• match_type je neobavezan argument. Broj je -1, 0 ili 1.

Funkcija MATCH traži specificirani element u rasponu ćelija i vraća relativnu poziciju tog elementa u rasponu.
Suština korištenja kombinacije funkcija "INDEX" i "SEARCH" je da tražimo koordinate vrijednosti po njihovom nazivu duž "koordinatnih osa".
Y osa će biti kolona "Ime", a osa X će biti red "Mjeseci".

Dio formule:

MEČ($A4,$I$4:$I$7,0)

vraća broj duž Y ose, u ovom slučaju će biti jednak 1, jer vrijednost "A" je prisutna u traženom rasponu i ima relativnu poziciju "1" u tom rasponu.
dio formule:

MEČ(B$3,$J$3:$L$3,0)

vraća #N/A jer vrijednost "1" nije u opsegu koji se gleda.

Tako smo dobili koordinate tačke (1; #N/A) koje funkcija “INDEX” koristi za traženje u argumentu “Niz”.
Potpuno napisana funkcija za ćeliju B4 bi izgledala ovako:

=INDEX($J$4:$L$7, PODRŽI ($A4,$I$4:$I$7,0), PODRŽI (B$3,$J$3:$L$3,0))

U suštini, kada bismo znali koordinate potrebne vrijednosti, funkcija bi izgledala ovako:

=INDEX($J$4:$L$7,1,#N/A))

Budući da argument “Broj_kolone” ima vrijednost “#N/A”, rezultat za ćeliju “B4” će biti odgovarajući.
Kao što se može vidjeti iz rezultirajućeg rezultata, sve vrijednosti u tabeli sa rezultatom ne odgovaraju referentnoj knjizi i kao rezultat vidimo da su neke od vrijednosti u tabeli prikazane kao „#N/A” , što otežava korištenje podataka za dalje proračune.
rezultat:

Da bismo neutralizirali ovaj negativni učinak, koristimo funkciju “IFERROR”, o kojoj smo čitali ranije, i zamijenimo vrijednost vraćenu prilikom greške s “0”, tada će formula izgledati ovako:

=IFGREŠKA(INDEX($J$4:$L$7, PODRŽI ($A4,$I$4:$I$7,0), PODRŽI (B$3,$J$3:$L$3,0)),0)

Demonstracija rezultata:

Kao što možete vidjeti na slici, vrijednosti “#N/A” više ne ometaju naše naknadne proračune koristeći vrijednosti u tabeli rezultata.

Case_5 Pronalaženje vrijednosti u rasponu brojeva

Zamislimo da brojevima koji su uključeni u određeni raspon trebamo dati određeni znak.
stanje:
Ovisno o cijeni proizvoda, treba mu dodijeliti određenu kategoriju
Ako je vrijednost u rasponu

• Od 0 do 1000 = A
• Od 1001 do 1500 = B
• Od 1501. do 2000. = B
• Od 2001 do 2500 = G
• Više od 2501 = D

Funkcija LOOKUP vraća vrijednost iz reda, stupca ili niza. Funkcija ima dva sintaktička oblika: vektor i oblik niza.

LOOKUP(vrijednost_traži, vektor_potraži, [vektor_rezultata])

• lookup_value je vrijednost koju funkcija LOOKUP traži u prvom vektoru. Lookup_value može biti broj, tekst, boolean, ime ili vrijednost reference.
• Watch_vector je raspon koji se sastoji od jednog reda ili jedne kolone. Vrijednosti u argumentu lookup_vector mogu biti tekst, brojevi ili logičke vrijednosti.
• Vrijednosti u argumentu view_vector moraju biti u rastućem redoslijedu: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; u suprotnom, funkcija LOOKUP može vratiti netačan rezultat. Tekst u dnu i velika slova smatra se ekvivalentnim.
• result_vector je raspon koji se sastoji od jednog reda ili stupca. Rezultat_vektor mora imati istu veličinu kao i lookup_vector.

=PREGLED(E3,$A$3:$A$7,$B$3:$B$7)

Argumenti “View_vector” i “Result_vector” mogu se napisati u obliku niza - u ovom slučaju, ne morate ih prikazati u zasebnoj tabeli na Excel listu.
U ovom slučaju funkcija će izgledati ovako:
Ispis rezultata u ćeliju B3:
=VIEW(E3;(0;1001;1501;2001;2501);("A","B","C","D","D"))

Slučaj_6 Zbir brojeva po karakteristikama

Da biste zbrojili brojeve na osnovu određenih karakteristika, možete koristiti tri različite funkcije:
SUMIF – zbroji samo po jednom atributu
SUMIFS – zbir višestrukih karakteristika
ZBIRNI PROIZVOD – zbir više karakteristika
Postoji i opcija koja koristi "SUM" i funkciju formule niza, kada se formula "SUM" podigne na niz:
((=SUM(()*()))
ali ovaj pristup je prilično nezgodan i u potpunosti je pokriven funkcionalnošću formulom “SUMPRODUCT”.
Sada za više detalja o sintaksi “SUMPRODUCT”:

PROIZVOD (niz1, [niz2], [niz3],...)

• Niz1 je prvi niz čije komponente treba pomnožiti, a zatim dodati rezultate.
• Niz2, niz3… - od 2 do 255 nizova čije se komponente moraju pomnožiti, a zatim dodati rezultati.

stanje:

• Pronađite ukupne troškove pošiljaka za svaki proizvod za određeni period:

Kao što se vidi iz tabele sa podacima, da bi se izračunao trošak, potrebno je cenu pomnožiti sa količinom, a dobijenu vrednost, primenom uslova selekcije, preneti u tabelu sa rezultatom.
Međutim, formula SUMPROIZ dozvoljava da se takvi proračuni izvrše unutar formule.
Ispis rezultata u ćeliju B4:

=SUMPROIZVOD(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Pogledajmo formulu u dijelovima:
– postaviti uslov odabira u koloni “Naziv” tabele sa podacima o koloni “Naziv” u tabeli sa rezultatom

($K$3:$K$11>=B$3)*($K$3:$K$11 – postavljamo uslov na osnovu vremenskog okvira, datum je veći ili jednak prvom danu tekućeg mjeseca, ali manji od prvog dana sljedećeg mjeseca. Slično, uslov je u tabeli sa rezultatom, niz je u tabeli sa podacima.
– pomnožite kolone “Količina” i “Cijena” u tabeli sa podacima.
Nesumnjiva prednost ove funkcije je slobodan redoslijed uvjeta snimanja, oni se mogu pisati bilo kojim redoslijedom, to neće utjecati na rezultat.
rezultat:

Sada zakomplikujmo uslov i dodajmo uslov da se izbor za naziv "kolačići" javlja samo u klasama "mali" i "veliki", a za naziv "roluje" sve osim klase "sa džemom":

Ispis rezultata u ćeliju B4:

=SUMPROIZVOD(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
Formuli za odabir kolačića dodan je novi uvjet:

(($I$3:$I$11=“mali”)+($I$3:$I$11=“veliki”))

– kao što vidite, dva ili više uslova u jednoj koloni se odvajaju u posebnu grupu pomoću simbola „+“ i stavljaju uslove u dodatne zagrade.
Formuli za odabir po rolama dodat je i novi uslov:

=SUMPROIZVOD(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 “sa džemom”);($L$3:$L$11)*($K$3:$K$11))

ovo:

($I$3:$I$11<>"sa džemom")

– u stvari, u ovoj formuli je bilo moguće napisati uslov odabira na isti način kao i kod odabira pomoću kolačića, ali tada bi u formuli morali navesti tri uslova, u ovom slučaju je lakše napisati izuzetak - nije jednako "sa džemom" za ovo koristimo vrijednost "<>».
Uopšteno govoreći, ako su grupe karakteristika/klase poznate unapred, onda je bolje kombinovati ih u ove grupe, kreirajući referentne knjige, nego upisivati ​​sve uslove u funkciju, naduvavajući je.
rezultat:

E, tu smo došli do kraja našeg kratkog priručnika, koji bi zapravo mogao biti mnogo duži, ali cilj je i dalje bio da pružimo rješenje za najčešće situacije, a ne da opišemo rješenje pojedinih (ali mnogo zanimljivijih slučajeva). ).
Nadam se da će priručnik pomoći nekome u rješavanju problema koristeći Excel, jer će to značiti da moj rad nije bio uzaludan!

Hvala na vašem vremenu!