Trikovi u Excelu 106. deo – Pretraga podataka po dve vrednosti

Pretraživanje po dve vrednosti

PROBLEM KORISNIKA: Moram da pretražim podatke po dve vrednosti. Treba da istovremeno nađem odgovarajuću šifru kompanije i cost centar (Slika 1.).

REŠENJE: Za ovaj problem ima tri rešenja: (a) nadovezani ključ, (b) funkcija OFFSET ili (c) funkcija SUMIFS. Nadovezani ključ će moći da se upotrebi samo ukoliko vam je dopušteno da dodate novu kolonu levo od kolone C. Funkcija SUMIFS će raditi samo ukoliko je vrednost koju treba dobiti numerička. Funkcija OFFSET će raditi samo ukoliko su zajedno sortirane sve šifre kompanije kako je ovde prikazano.

    Pomoću nadovezanog ključa (Slika 2) unećete novu kolonu pre iznosa u koloni C. Treba da spojite kolonu A, jedinstveni separator i kolonu B. Na primer, =A2&”-“&B2 daće ključ 100-1010.

    Znak separatora možete odrediti po sopstvenom izboru. U stvarnosti biste mogli da imate dve kombinacije kompanije i centra koje bi izgledale isto kada se spoje. Tu nejasnoću bi sprečilo korišćenje crtice između njih (Slika 3).

     Kada u lookup tabeli imate nadovezani ključ, tada možete da “u letu” objedinite ključna polja (Slika 4) u VLOOKUP formulu:

=VLOOKUP(G2&””&G3,$C$ 2:$D$22,2,FALSE)

    Kao što je ranije pomenuto, ovaj metod funkcioniše samo ako možete da u svoje podatke dodate nadovezani ključ. Lepo je da kolonu C sakrijete tako da je niko ne vidi, ali u njoj morate da imate odgovarajuće polje.

ALTERNATIVNA STRATEGIJA: Ako je vrednost koju pokušavate da dobijete numerička, možete da koristite funkcije DSUM ili SUMIFS (Slika 5). Za više informacija pogledajte “Izračunavanje na osnovu više uslova”.

ALTERNATIVNA STRATEGIJA: Upotrebite funkciju OFFSET. Čistunci će tvrditi da je OFFSET nestabilna funkcija i da može da uspori vreme izračunavanja. Ipak, funkcija OFFSET često će rešiti probleme tamo gde treba da se pozovete na opseg podataka koji se pomera ili menja veličinu (Slika 6).

Funkcija OFFSET koristi se da pokaže na neki opseg podataka. Dok se izračunava formula izračunava se i mesto i veličina opsega.

    Funkcija OFFSET dopušta pet parametara. Najmanje jedan od četiri krajnja parametra treba da bude formula koja se izračunava “u letu”. Kada se funkcija OFFSET podesi da dâ neki opseg ćelija, tada ćete se naći u situaciji da koristite OFFSET unutar neke druge funkcije kao što je SUM ili, u ovom slučaju, unutar funkcije VLOOKUP.

    Sintaksa je =OFFSET(Referentna ćelija, koliko redova ispod nje, koliko kolona desno od nje, koliko redova je visoka, koliko kolona je visoka). Na primer, mogli biste da pođete od B1 kao referentne ćelije, da se spustite N redova, pomerite udesno za 0 kolona, i da stavite da je opseg visok 7 redova i širok 2 kolone.

    Na sledećoj slici, funkcija MATCH u ćeliji F5 izračunava gde započinje lookup tabela za ovu kompaniju. Funkcija COUNTIF u ćeliji F6 izračunava koliko lookup tabela treba da bude visoka. Oba ova broja će ući u funkciju OFFSET koja je radi ilustracije prikazana u ćeliji F7. Stvarna formula se nalazi u ćeliji F9, gde se funkcija OFFSET koristi da se opiše lookup tabela u formuli VLOOKUP.