LOOKUP - ultimul MATCH

trucuri, sfaturi si alte idei pentru imbunatatirea lucrului cu Excel
Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

LOOKUP - ultimul MATCH

Mesaj de gecs » Mie Mar 17, 2010 5:03 pm

Cand vine vorba despre functii de cautare, un procent important dintre utilizatorii de Excel vor indica VLOOKUP (HLOOKUP) ca principala functie de folosit pentru asa ceva. Cu toate astea VLOOKUP si HLOOKUP sunt cele mai rigide functii folosite la cautare, pentru simplul motiv ca valoarea de cautat trebuie sa fie pe prima coloana/rand a range-ului care trebuie furnizat functiilor respective ca al doilea argument.

INDEX + MATCH e o alta solutie folosita la cautare, iar LOOKUP pare cea mai putin folosita optiune. Cu toate astea, functia LOOKUP are o serie de avantaje care merita cunoscute:
- LOOKUP stie sa lucreze cu array-uri, nu numai cu range-uri si stie si sa prelucreze aceste segmente de date (ca si SUMPRODUCT);
- LOOKUP ignora erorile prezente in aceste array-uri (range-uri).

Desi am mai vorbit despre acest tip de formula si in subiectul Relatie intre tabele cu MS Query -> PivotTable 2007, cred ca LOOKUP merita si un subiect separat.

In fisierul atasat am ilustrat posibilitatea da a folosi LOOKUP pentru a gasi ultima aparitie a unei valori intr-un range (atat VLOOKUP, HLOOKUP, cat si INDEX+MATCH vor returna doar prima aparitie!) si, mai mult decat atat, gasirea ultimei aparitii a unei inregistrari care raspunde mai multor conditii si asta fara ca formula sa fie introdusa (confirmata) ca formula CSE.

Ideea acestei formule si o explicatie a functionarii ei o gasiti aici (penultima postare), chiar din partea autorului.

Cateva consideratii asupra fisierului atasat:
- in foaia "Livrari" e un tabel cu date ipotetice care simuleaza o serie de livrari de la mai multi furnizori - pentru aceeasi data pot exista mai multe livrari de la acelasi furnizor;
- in foaia "Furnizori" e lista de furnizori folosita la Data Validation in foile Livrari" (A:A) si "Interogari" (B2, B7);
- in foaia "Interogari" sunt folosite formulele bazate pe LOOKUP (si SUMPRODUCT ;) ) - campurile cu fond galben contin liste formate cu Data Validation, iar valorile acestori campuri sunt folosite ca argumente in formulele din foaie.
- in foaia "date_u" se creaza lista de date unice existente in foaia "Livrari" - lista folosita mai departe in formula de la Data Validation (D7) in foaia "Interogari";
- pentru a face formulele mai mai usor de inteles am folosit nume asociate range-urilor din foaia "Livrari" ("date_liv", "furnizat", cantitate", corespunzatoare datelor din coloanele A, B si C)

Modificand valorile din celulele cu fond galben din foaia "Interogari" veti putea vedea cum se modifica rapoartele create cu ajutorul lui LOOKUP.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesaj de gecs » Sâm Mar 20, 2010 9:43 am

Folosind aceeasi logica si acelasi LOOKUP se poate afla si pozitia (randul), sau informatia asociata pentru penultima sau antepenultima paritie a unei valori intr-un range si tot fara ca formula sa fie de tipul CSE.

De exemplu avem un range (A1:A100) in care sunt inscrise nume ("Ion", "Ana", "Gheorghe" etc. - numele se repeta), iar pe coloana B (B1:B100) valori numerice. Ca sa aflam valoarea numerica de pe coloana B asociata ultimei aparitii a numelui "Ion" pe coloana A, putem folosi:

Cod: Selectaţi tot

=LOOKUP(2,1/(A1:A100="Ion"),B1:B100)
Pentru a afla valoarea numerica de pe coloana B asociata penultimei aparitii a numelui "Ion" pe coloana A, adaugam la conditia de la numitorul expresiei "1/..." conditia ca randurile de dupa ultima aparitia a valorii "Ion" pe coloana A sa fie excluse din rezultate (inclusiv randul ultimei aparitii a valorii "Ion") si formula va arata asa:

Cod: Selectaţi tot

=LOOKUP(2,1/((A1:A100="Ion")*(ROW(A1:A100)<LOOKUP(2,1/(A1:A100="Ion"),ROW(A1:A100)))),B1:B100)
iar pentru penultima aparitie:

Cod: Selectaţi tot

=LOOKUP(2,1/((A1:A100="Ion")*(ROW(A1:A100)<LOOKUP(2,1/((A1:A100="Ion")*(ROW(A1:A100)<LOOKUP(2,1/(A1:A100="Ion"),ROW(A1:A100)))),ROW(A1:A100)))),B1:B100)
...si putem continua asa, cat ne permite versiunea de Excel pe care o folosim :lol:

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesaj de gecs » Joi Apr 22, 2010 11:32 am

Si inca una!

Pornind de la formula care rezolva problema pusa in acest subiect (postarea #4):

Cod: Selectaţi tot

=-LOOKUP(1,-RIGHT(TRIM(LEFT(A1,FIND("g/L",A1)-1)),{1,2,3,4,5,6}))
(fata de originalul formulei de mai sus am modificat referinta D1 in A1)
se poate imagina o generalizare a acestei formule pentru returnarea ultimului sau primului numar inclus intr-un sir de caractere.

Inainte insa de a scrie formulele respective, cred ca formula de mai sus merita analizata.

LOOKUP admite doua forme:
- forma "vector": LOOKUP(valoare_de_cautat, vector_in_care_se_cauta,vector_din_care_se_returneaza_rezultatele)
- forma "array": LOOKUP(valoare_de_cautat,array_in_care_se_cauta_si_din_care_se_returneaza_si_rezultatele)
Formula de mai sus foloseste a doua forma a lui LOOKUP si mai trebuie precizat ca se bazeaza pe o alta caracteristica a comportamentului acestei functii: cand valoarea cautata nu se gaseste in array, dar este mai mare decat toate valorile din array, LOOKUP returneaza ultima valoare din array, exceptand erorile, iar daca in array se gasesc valori mai mari decat cea cautata, se va returna ultima dintre valorile mai mici decat cea cautata.

Plecand de la analogia formulei de mai sus cu forma "array" a lui LOOKUP se constata ca in formula de mai sus e cautata valoarea 1 intr-un array, dar care e array-ul? Array-ul e format prin apelarea functiei RIGHT() folosind un array ca al doilea argument. Functia RIGHT(sir,n) returneaza din sirul de caractere sir portiunea din dreapta a sirului formata din n caractere. Prin apelarea acestei functii in forma din formula de mai sus se creaza array-ul format din 6 elemente, care sunt tot siruri de caractere extrase din dreapta sirului de caractere furnizat ca prim argument: 1 - sirul format din ultimul caracter din dreapta al sirului, 2 - sirul format din ultimele doua caractere din dreapta ale sirului, 3 - sirul format din ultimele 3..., ..., 6 - sirul format din ultimele 6 caractere din dreapta ale sirulu. In aceasta situatie s-a estimat ca numarul cautat (pe care il va returna formula in final) nu va avea mai mult de 6 cifre (inclusiv separatorul zecimal) - daca se doreste se poate mari numarul de cifre, iar exprimarea array-ului sub forma {1,2,3,4,5,6} poate fi inlocuita cu forma ROW($1:$6).

Primul argument al functiei RIGHT() in formula de mai sus e reprezentat de portiunea din sirul aflat in A1care incepe cu primul caracter al acestui sir si se termina inainte de "marker-ul" cautat in sir cu functia FIND().

Recapituland cele de mai sus, putem spune asa:
- functia FIND() cauta in sirul initial (cel din A1) aparitia sirului "g/L" si returneaza numarul pozitiei acestui sir (n) in sirul initial, iar expresia n - 1 e folosita apoi ca al doilea argument al functiei LEFT() care extrage din sirul initial primele n - 1 caractere, pe care le paseaza functiei TRIM() iar rezultatul returnat de TRIM() e folosit ca prim argument in functia RIGHT(), cea care creaza array-ul format din atatea elemente cate sunt cuprinse in array-ul furnizat ca al doilea argument acelaiasi functii RIGHT().

Buun, dar care e rolul celor doi de "minus" ?! Aici e "schmekeria" ;) Minusul din fata lui RIGHT are un dublu rol: forteaza conversia din sir de caractere in numeric - daca elementul nu poate fi convertit rezulta eroare, dar schimba si semnul numarului rezultat din conversie, pentru a-l face <1. Cu ocazia acestei analize observam ca exista si o limitare a folosirii acestei formule: ea va returna intotdeauna un numar POZITIV, deci daca numarul cautat in sirul initial este negativ, va mai trebui sa facem o verificare legata si de semnul din fata acestui numar in sirul initial. Prin adaugarea acestui "-" in fata lui RIGHT(), array-ul care reprezinta al doilea argument al functiei LOOKUP se va transforma din array de siruri in array de valori numerice si erori. Cum LOOKUP() ignora erorile, se va returna ultima dintre valorile din array mai mici decat valoarea cautata (1). Fiindca aceasta valoare va fi negativa, minusul din fata lui LOOKUP() schimba semnul acestei valori.

Iata acum si formele "generalizate" ale formulei, in care nu e necesara prezenta unui "marker" in sirul initial:
- pentru returnarea ultimului numar existent intr-un sir:

Cod: Selectaţi tot

=-LOOKUP(1,-RIGHT(LEFT(A1,LOOKUP(1,-MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1),ROW(INDIRECT("$1:$"&LEN(A1))))),ROW($1:$10)))
- pentru returnarea primului numar existent intr-un sir:

Cod: Selectaţi tot

=-LOOKUP(1,-LEFT(MID(A1,LEN(A1)-LOOKUP(2,1/MID(A1,LEN(A1)-ROW(INDIRECT("$1:$"&LEN(A1)))+1,1),ROW(INDIRECT("$1:$"&LEN(A1))))+1,256),ROW($1:$10)))

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesaj de gecs » Lun Mai 31, 2010 7:29 pm

M-am mai gandit si am ajuns la concluzia ca e bine sa ignorati al doilea mesaj de la acest subiect pentru ca am imaginat o varianta de LOOKUP cu care se poate returna a n-a pozitie a aparitiei unei valori intr-un range, tot cu o formula obisnuita, bazata pe acelasi LOOKUP().

In fisierul atasat, modificand valoarile din D1 si D2, in G2 se obtine numarul randului celei de-a n-a aparitii (unde n e inscris in D2) a valorii din D1 in range-ul de valori aflat pe coloana A. Pentru a usura intelegerea formulei, dar si pentru a-i mari gradul de versatilitate, range-ul de valori de pe coloana A e denumit "range1", iar definitia numelui foloseste o formula care refera un range dinamic ( =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)) ).

Formula arata asa:

Cod: Selectaţi tot

=LOOKUP(2,1/((range1=$D$1)*(COUNTIF(OFFSET(INDEX(range1,1),0,0,ROW(INDIRECT("1:"&ROWS(range1)))),"="&$D$1)=$D$2)),ROW(range1))
Simplificand formula, aceasta ar arata cam asa:
=LOOKUP(2,1/conditie,range)unde conditie poate include orice conditie imaginabila in legatura cu range-ul unde facem cautarea, sau cu range-uri asociate acestuia (poate fi vorba de coloanele unui tabel intreg), iar range este range-ul din care LOOKUP returneaza valoarea asociata pozitiei gasite in range-ul (range-urile) in care se face cautarea - in cazul acesta, ROW(range1) va returna un array cu numerele randurilor pe care se afla range-ul asociat numelui "range1".

Partea ce mai "stufoasa" a formulei e cea pe care am botezat-o mai sus "conditie". Analizand aceasta secventa se observa ca e vorba de un produs. Acest produs se poate interpreta precum conditie1*conditie2, unde conditie1 si conditie2 sunt de fapt array-uri de valori booleene, ambele array-uri trebuind sa aiba acelasi numar de elemente. Prin inmultirea acestor array-uri de TRUE si FALSE se va forta conversia valorilor booleene in 0 si 1 (1 pentru TRUE si 0 pentru FALSE), iar array-ul rezultat va avea acelasi numar de valori, dar acestea vor fi 0 sau 1. Impartirea lui 1 la acest array va avea ca rezultat un array de valori de 1 (de fapt o singura valoare de 1) si erori #DIV/0!, iar cum LOOKUP ignora erorile, singura posibilitate care-i ramane e sa returneze numarul randului unde se afla valoarea cautata care indeplineste conditiile.

Formula functioneaza si in versiunea 2003, asa ca am atasat fisiere pentru ambele versiuni (2003 si 2007).

L.E. In formula de mai sus se poate inlocui secventa "INDEX(range1,1)" cu "range1" pur si simplu, formula devenind:

Cod: Selectaţi tot

=LOOKUP(2,1/((range1=$D$1)*(COUNTIF(OFFSET(range1,0,0,ROW(INDIRECT("1:"&ROWS(range1)))),"="&$D$1)=$D$2)),ROW(range1))
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Avatar utilizator
Dr.Excel
Site Admin
Site Admin
Mesaje: 1997
Membru din: Sâm Ian 24, 2009 1:45 pm
Localitate: Bucharest
Contact:

Re: LOOKUP - ultimul MATCH

Mesaj de Dr.Excel » Lun Mai 31, 2010 9:12 pm

iti dai seama ca topicul asta trebuie sa apara in TOP Excel ;)

apropo, gecs parca te te-ai oferit sa faci TOP-ul pentru Excel, asa-i? :D

MCT, MCITP
MOS Master Instructor
IT Learning

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesaj de gecs » Lun Mai 31, 2010 9:45 pm

Nu stiam cine a descarcat deja unul dintre fisiere :lol:
Dr.Excel scrie:apropo, gecs parca te te-ai oferit sa faci TOP-ul pentru Excel, asa-i? :D
Pai e gata facut - ce-i mai trebuie? :D

Acuma serios, de luni de zile ma chinui sa gasesc o formula de cautare, care sa nu fie formula CSE, in care sa se poata controla conditia legata de specificarea numarului de ordine al aparitiei valorii respective in range-ul in care se cauta... LOOKUP() asta e mai tare ca orice functie de cautare si stie sa prelucreze array-uri mai bine decat alte functii. De exemplu, daca folosesti OFFSET() intr-unul din argumentele lui SUMPRODUCT() primesti o eroare de mai mare dragu'... :( LOOKUP() nu numai ca nu se "impiedica" de OFFSET(), dar stie sa ignore si erorile... mai are putin si invata sa spele si vasele, sa duca si copii la scoala si sa vorbeasca si la telefon cu soacra... :lol:

smcsa
Mesaje: 2805
Membru din: Mar Sep 29, 2009 7:29 pm
Localitate: Timisoara

Re: LOOKUP - ultimul MATCH

Mesaj de smcsa » Dum Iun 06, 2010 3:26 pm

Eu vreau una care sa vorbeasca cu nevasta atunci cand ai treaba. Acum sa te vad :lol:

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesaj de gecs » Dum Iun 06, 2010 3:49 pm

=LOOKUP(DATEDIF(A1,TODAY(),"Y"),{1,3,5,11,20,30},{"Imediat Bursucel!","Cum spui tu, Bursucel","Bursucel, mai am putina treaba","Da draga, da' ai putina rabdare!","Nu vezi ca am treaba?!","Nu pleca Bursucel!"})
unde in A1 e data casatoriei ;)

smcsa
Mesaje: 2805
Membru din: Mar Sep 29, 2009 7:29 pm
Localitate: Timisoara

Re: LOOKUP - ultimul MATCH

Mesaj de smcsa » Dum Iun 06, 2010 3:55 pm

Imi da eroare !
Diferenta mare intre data casatoriei si data curenta nu va dreptul sa comentati. Incercati schimbarea persoanei in cauza
Nici nu stiam ca Excel-ul meu stie romaneste :lol:

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesaj de gecs » Dum Iun 06, 2010 4:08 pm

Da eroare doar in primul an de casatorie, dar atunci parca n-ai niciun motiv sa... LOOKUP, nu? ;)

Închis

Înapoi la “Tips and Tricks Excel”