LOOKUP - ultimul MATCH

trucuri, sfaturi si alte idei pentru imbunatatirea lucrului cu Excel

Re: LOOKUP - ultimul MATCH

Mesajde gecs » Mie Aug 04, 2010 9:51 pm

Tot cu LOOKUP: MAX cu mai multe conditii ;)

Daca vrem sa aflam valoarea maxima de pe o coloana unde avem valori numerice dintr-un range cu mai multe coloane iar randurile din range trebuie sa satisfaca anumite conditii pe celelalte coloane, foloseam o formula CSE:

=MAX((Coloana1=Valoare1)*(Coloana2=Valoare2)*...*(ColoanaN=ValoareN)*Coloana_Valori_Numerice)

confirmata cu Ctrl+Shift+Enter.

Folosind acelasi LOOKUP(2,1/...) putem avea o formula obisnuita, confirmata doar cu Enter, nu una CSE:

Cod: Selectaţi tot
=LOOKUP(2,1/((Coloana1=Valoare1)*(Coloana2=Valoare2)*...*(ColoanaN=ValoareN)*(Coloana_Valori_Numerice=MAX((Coloana1=Valoare1)*(Coloana2=Valoare2)*...*(ColoanaN=ValoareN)*Coloana_Valori_Numerice))),Coloana_Valori_Numerice)


Bineinetles ca in formula de mai sus se poate inlocui semnul "=" cu ">", "<", ">=", "<=" sau "<>" sau pot aparea si alte functii de prelucreare a datelor pentru realizarea expresiilor logice care compun conditiile.

Daca inlocuim expresiile care descriu conditiile logice cu formularile "Conditie1" si "Conditie2"... "ConditieN" obtinem urmatoarea forma generala:

=LOOKUP(2,1/(Conditie1*Conditie2*...*ConditieN*(Coloana_Valori_Numerice=MAX(Conditie1*Conditie2*...*ConditieN*Coloana_Valori_Numerice))),Coloana_Valori_Numerice)

Bineinteles ca range-urile folosite in Conditie1... ConditieN si Coloana_Valori_Numerice trebuie sa aiba aceeasi dimensiune.
Avatar utilizator
gecs
Moderator
Moderator
 
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesajde mrlpls » Mie Dec 08, 2010 11:08 pm

în fişierul ataşat "Exemple_Lookup.xls", în foaia "Interogări" când dau clik pe celula B2 apare un buton de selecţie în dreapta celulei. cine poate să-mi spună şi mie cum se face să apară acel buton de selecţie.
mrlpls
Moderator
Moderator
 
Mesaje: 404
Membru din: Mie Dec 08, 2010 10:50 pm

Re: LOOKUP - ultimul MATCH

Mesajde Dr. Cloud » Joi Dec 09, 2010 6:06 pm

Ceea ce ai văzut tu acolo se numeşte Data Validation.
Uite aici câteva exemple (video) - Validarea datelor -pentru a înţelege cum se creează un astfel de meniu.
Dr. Cloud
 
Mesaje: 3329
Membru din: Mar Oct 02, 2012 11:19 am

Re: LOOKUP - ultimul MATCH

Mesajde gecs » Joi Dec 09, 2010 6:09 pm

Selectezi celula respectiva -> mergi in menu la Data -> Validation... (pentru versiunile anterioare lui 2007) sau in tab-ul Data -> grupul Data Tools -> Data Validation (pentru versiunile peste 2007, inclusiv) si gasesti raspunsul.
Avatar utilizator
gecs
Moderator
Moderator
 
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: LOOKUP - ultimul MATCH

Mesajde mrlpls » Joi Dec 09, 2010 9:04 pm

mersi.
foarte multe lucruri interesante pe aici. acum imi dau seama cât de puțin știu din acest program.
mrlpls
Moderator
Moderator
 
Mesaje: 404
Membru din: Mie Dec 08, 2010 10:50 pm

Re: LOOKUP - ultimul MATCH

Mesajde adof » Mie Mar 14, 2012 3:50 pm

va rog
dacă se poate un exemplu de Lookup
simplu de adunare
a unor celule care sunt în dreptul celulelor care îndeplinesc condiţia
cerută

trebue sa adun toate celulele de pe coloana cu concediu programat (sau realizat)din luna respectiva
dacă gaseste funcţia "mecanic"

am incercat cu If dar daccă sterg un rând din tabel o ia razna toate calculele

atasez fisierul exemplu
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
adof
 
Mesaje: 420
Membru din: Mie Iul 14, 2010 12:42 pm
Localitate: Sibiu

Re: LOOKUP - ultimul MATCH

Mesajde Catalin B. » Mie Mar 14, 2012 6:24 pm

Introdu in G10 formula: (dupa care o poti copia in jos si la dreapta)
=SUMIF('CO REALIZAT'!$C$7:$C$16;'CO pe funcţii'!$C10;'CO REALIZAT'!E$7:E$16)
Foloseste data validation list pentru a introduce meseriile, ca sa nu ai erori de calcul .
Mi-a placut exprimarea cu "Nr. fiinte" in loc de numar de persoane. (totusi nu cred ca si cateii de paza au concediu la voi :) )
Probleme să fie, că soluţii se găsesc...
Catalin B.
Moderator
Moderator
 
Mesaje: 747
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: LOOKUP - ultimul MATCH

Mesajde amador » Mar Sep 10, 2013 6:36 am

Va salut!
Ma chinuie de 1 saptamana o formula de extragere a unor date dintr-un tabel.
Am nevoie de o formula HLOOKUP (sau orice altceva care sa faca ceea ce am nevoie)
In primul tabel am datele care vor fi salvate din facturile emise.
In cel de al doilea imi trebuie o formula care sa imi actualizeze stocul.
Problema mea este ca produsele dintr-o singura factura sunt in numar de maxim 12.
Trebuie sa identific criteriul in fiecare factura si apoi cantitatea sa o adaug la vanzari in dreptul produsului respectiv.
Daca cineva ma poate ajuta ii multumesc anticipat.
Credeti ca s-ar putea rezolva cu lookup aceasta problema?
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
amador
 
Mesaje: 14
Membru din: Vin Aug 23, 2013 6:35 am

Re: LOOKUP - ultimul MATCH

Mesajde DR.ACCESS » Joi Feb 20, 2014 11:18 am

Buna ziua,
Din pacate la modul in care este organizat tabelul sursa nu cred ca pe viitor v-ar putea "salva" vreo formula.
Inteleg ca doriti sa inregistrati vanzarile intr-un tabel dar, din pacate, nu respectati conditiile de organizare a datelor. Un tabel nu trebuie sa contina 2 coloane cu aceeasi semnificatie, in al doilea rand, aveti o multime de informatii care se repeta si care, in timp, vor mari considerabil dimensiunea fisierului. Facturile sunt inregistrate pe coloane! Gresit! Numarul de coloane al Excelului este sensibil mai mic decat numarul de randuri!
Intrucat nu aveti experienta in organizarea datelor, presupun ca Access-ul nu va este cunoscut, cu toate ca ar fi programul ideal pentru dumneavoastra.
Dar sa revenim la Excel. Vom simula o baza de date relationala in Excel!
Facem un tabel (unul singur) in care inregistram Clientii. Cu toate informatiile aferente. Asignam unui client un cod unic (puteti folosi CUI)
Un alt tabel cu produse (denumire, si/sau cod produs, stoc initial si stoc actualizat)
In tabelul cu facturi introduceti o coloana cu numarul (codul) clientului de tip Data validation List (lista fiind formata din CUI client din tabelul de clienti), o coloana cu cod_Produs (tot cu Data validation List avand ca sursa codul produsului din tabelul de produse) ca sa nu mai scrieti toate informatiile aferente produsului pe mii de randuri.
Si abia de aici incolo puteti apela la functii de tip LOOKUP,Vlookup, Hlookup SUMIF, SUMIFS etc. pentru a putea face raportarile si centralizarile necesare. Inainte de a utiliza o formula care sa va ajute la actualizarea stocurilor, va recomand sa va organizati mai bine datele.
Mult succes!
Dr.Access
MCT, MCTS
MOS Master Instructor
DR.ACCESS
Moderator
Moderator
 
Mesaje: 219
Membru din: Lun Sep 05, 2011 5:06 pm

Re: LOOKUP - ultimul MATCH

Mesajde cip.st » Mar Iun 24, 2014 9:55 am

eu am oareșce reticențe în a utiliza lookup, deoarece help-ul spune că funcționează correct doar pe liste sortate, lucru care nu este întotdeauna la îndemână.
##For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order. If this is not possible, consider using the VLOOKUP, HLOOKUP, or MATCH functions.

Pentru a extrage a n-a înregistrare ce îndeplinește o condiție (sau un set de condiții, oricum ar fi scrise - cu ajutorul funcțiilor and(), or(), xor() sau sub forma unui produs * aka and * sau a unei sume de condiții * aka or *) utilizez o formulă adaptată după un exemplu de pe net. (OBS. cu ajutorul acestei formule se pot extrage subseturi de date dintr-un set mai mare).

Formula generică este:
{=IFERROR(INDEX(coloana_din care_extrag_date,SMALL(IF(coloana_după_care se filtrează condiție celulă_valoare condiție,ROW(coloana_din care_extrag_date)-ROW(prima_celula_din_coloana_din care_extrag_date)+1),valoare_n)),"")}

un exemplu:
zona a2:c1000 date indiferent dacă sunt sau nu corect sortate
presupunem:
B2:B1000 zona de unde vrem să extragem date
C2:C1000 zona asupra căreia aplicăm filtrul
F1 - celula cu valoarea filtrului
F2 - n, unde n reprezintă a N-a valoare din listă ce îndeplinește criteriul dat
formula este:
{=IFERROR(INDEX(B$2:B$1000,SMALL(IF($C$2:$C$1000>=$F$1,ROW(B$2:B$1000)-ROW(B$2)+1),$F$2)),"")}
cip.st
Moderator
Moderator
 
Mesaje: 516
Membru din: Vin Iun 06, 2014 1:43 pm
Localitate: Iași

Anterior

Înapoi la Tips and Tricks Excel

Cine este conectat

Utilizatorii ce navighează pe acest forum: Niciun utilizator înregistrat şi 3 vizitatori