Extragere prima valoare <>0 dintr-un tabel.

[Berti]

Salutare stimati forumisti!

Doresc sa extrag o valoare dintr-un tabel, va descriu pe scurt despre ce-i vorba, dar probabil fisierul atasat este mai concludent.

Capul de tabel orizontal este format dintr-o secventa de timpi (din 8 in 8 ore), iar in capul de tabel vertical se regasesc numele produselor,
iar in corpul tabelului se regasesc cantitatile realizate in intervalul de 8 ore pentru produsul in cauza.

Stiind intervalul orar, doresc sa aflu ce produse s-au fabricat si cate bucati.

Am incercat ceva cu Match, dar m-am incurcat la idetificarea primei valori diferite de 0, oare cum s-ar putea face un offset conditionat? Iar indetificarea produsului n-am idee cum s-o abordez.

Va multumesc pentru timpul acordat si va doresc o saptamana lejera!

[Berti]

In ideea ca nu-i frumos sa iei totul de-a gata, atasez ce am reusit sa obtin pana acum.

-am identificat numarul coloanei pentru data dorita (match)
-am reusit sa aflu randul primei valori diferite de zero de pe o coloana (match si index)

Dar nu reusesc sa linkuiesc cele doua formule, adica sa-mi returneze numarul randului pentru prima valoare diferita de zero din coloana identificata de prima formula.

Se pare ca mai departe ma descurc, cu index am extras atat numarul de bucati fabricate cat si produsul in cauza. Singura problema ramane cea descrisa mai sus.Multumesc!

[alcoool2]

Salut!
Pentru tine am varianta VBA dupa cu urmeaza:

Cod: Selectaţi tot

Sub cauta()
Dim coloana As Integer
Dim randul As Integer
Dim ora As Date
Dim produsul As String

ora = InputBox("introduceti intervalul orar", "cerinta", Default)
produsul = InputBox("introduceti produsul", "cerinta", Default)

For i = 3 To 46 'schimba 46 cu numarul de intervale orare
    If Sheet1.Cells(6, i) = ora Then coloana = i
Next

For i = 7 To 12 'schimba 12 cu numarul de produse
    If Sheet1.Cells(i, 2) = produsul And Sheet1.Cells(i, coloana) <> "0" Then randul = i
Next

raspuns = Sheet1.Cells(randul, coloana)
MsgBox raspuns

End Sub

sper sa iti foloseasca

[Berti]

Salut, multumesc pentru efortul depus dar nu-i tocmai ce doresc si prefer sa mergem pe o varianta mai simpla, de preferat fara VBA, crezi ca ai putea arunca o privire peste fisierul atasat a doua oara (v2), poate reusesti sa ma indrumi cum sa link-uiesc cele doua formule intre ele.

[gecs]

Ai nevoie de formule CSE ca cele din H25 si M25, copiate in jos pe coloana, in atatea celule cate produse ai, sau estimezi ca vor aparea in timpul cat vei folosi fisierul.

Am modificat definitia numelui timpi asociindu-i un range dinamic, cum tot range-uri dinamice sunt asociate si noilor nume produse si productie. Si pentru numerotarea de pe coloana G am folosit o formula care returneaza sirul vid ("") daca rezultatul de returnat e mai mare decat numarul de randuri al range-ului asociat numelui produse. Cele trei formule lucreza impreuna pentru ca formulele de pe coloanele H si M refera valorile de pe coloana G, de pe acelasi rand.

Formula din H25 e:

Cod: Selectaţi tot

=IF(SUMPRODUCT(--(INDEX(productie,,MATCH($B$25,timpi,0))>0))<$G25,"",INDEX(produse,SMALL(IF(INDEX(productie,,MATCH($B$25,timpi,0))=0,ROWS(produse)+ROW($B$6)+1,ROW(produse)),$G25)-ROW($B$6)))

confirmata cu Ctrl+Shift+Enter, nu doar cu Enter.

Formula din M25 e:

Cod: Selectaţi tot

=IF(SUMPRODUCT(--(INDEX(productie,,MATCH($B$25,timpi,0))>0))<$G25,"",INDEX(productie,SMALL(IF(INDEX(productie,,MATCH($B$25,timpi,0))=0,ROWS(produse)+ROW($B$6)+1,ROW(produse)),$G25)-ROW($B$6),MATCH($B$25,timpi,0)))

confirmata cu Ctrl+Shift+Enter, nu doar cu Enter.

[Berti]

Gecs, multumesc pentru solutie, e foarte ingenioasa dar totodata si complicata (pentru mine).

E ok, am s-o folosesc pe asta, dar cu titlu educativ te-as ruga sa te uiti putin si peste solutia gasita de mine intre timp,
e practic un workaround mai intortocheat si sunt curios daca se poate renunta la formulele din coloana AV, folosind in B28 doar o singura formula de tip CSE?

Practic cum inlocuiesc array-ul functiei index cu o coloana de formule? Nu stiu daca m-am exprimat bine... sper sa se inteleaga din fisier mai exact.

[gecs]

Citeste cu atentie ce scrie in help-ul functiilor OFFSET, INDEX si MATCH. Sunt cateva elemente specifice fiecarei functii in parte, care simplifica lucrurile in multe situatii. Uita-te si la subiectul Functia OFFSET, ca poate-ti foloseste si ce srie pe acolo.

Formula ta (in AV7):

Cod: Selectaţi tot

=OFFSET($B$7:$AT$18,0,$B$27-1,ROWS($B$7:$AT$18),1)

1. primul argument al lui OFFSET e bine sa fie o referinta la o singura celula - formula ta e echivalenta cu:

   Cod: Selectaţi tot

   =OFFSET($B$7,0,$B$27-1,ROWS($B$7:$AT$18),1)

   nu are nicio relevanta pentru primul argument ca folosesti un range cu doua dimensiuni, functia oricum ia ca referinta de folosit din acest range prima celula din cea mai dinstanga coloana si cel mai de sus rand a range-ului cu doua dimensiuni.
2. Nu inteleg de ce ai folosit pentru numele timpi un range care include si celula B6 - nu cauti niciodata cu MATCH o valoare in celula aia.
3. Al treilea argument din formula ta e de fapt tot un MATCH, pentru ca referi celula $B$27 unde ai formula:

   Cod: Selectaţi tot

   =MATCH(B25,timpi,0)

   Daca in rnage-ul asociat numelui timpi nu includeai si celula B6, nu era nevoie nici de -1 ala din al treilea argument.
4. Al patulea argument, iar nu inteleg de ce e un range cu doua dimensiuni - putea foarte bine sa fie ROWS($B$7:$B$18)
5. Daca nu foloseai ca prim argument un range cu doua dimensiuni, al cincilea argument putea lipsi)

In concluzie, formula ta ar putea fi simplificata la:

Cod: Selectaţi tot

=OFFSET($C$7,0,MATCH($B$25,timpi,0),ROWS($B$7:$B$18))

cu conditia ca definitia numelui timpi sa fie:

Cod: Selectaţi tot

=Sheet1!$C$6:$AT$6

Solutia oferita de mine pleaca de la premisa ca atat numarul valorilor de pe randul 6 cat si al celor de pe coloana B, poate varia in timp si nu listeaza valorile egale cu 0. Eu as folosi in locul formulei tale de pe coloana AV (folosind numele definite in fisierul atasat de mine):

Cod: Selectaţi tot

=INDEX(productie,ROW()-ROW($B$6),MATCH$(B$25,timpi,0))

Nu e posibil ca folosind o formula intr-o singura celula, ea sa genereze valori si in alte celule. Se poate folosi o formula de tip array pe un range de celule - se selecteaza AV7:AV18 si se introduce formula:

Cod: Selectaţi tot

=INDEX(productie,,MATCH($B$25,timpi,0))

si se confirma cu Ctrl+Shift+Enter, daca vrei sa fie musai o formula CSE, dar la fel de bine se poate introduce formula completa (si cu valorile pentru randuri)

Cod: Selectaţi tot

=INDEX(productie,ROW()-ROW($B$6),MATCH$(B$25,timpi,0))

confirma doar cu Ctrl+Enter, ca aceeasi formula sa fie inscrisa in toate celulele selectate.

[Berti]

Multumesc Gecs pentru timpul acordat.

Cred ca pentru moment am sa folosesc solutia furnizata de tine, ramane de vazut daca am sa folosesc nume definite sau range-uri... doresc sa extrag datele din mai multe sheet-uri, iar la adaugarea unui sheet nou e mai usor un search&replace decat sa tot definesc nume.

[Berti]

O ultima rugaminte, am incercat atat cu nume definite cat si cu range-uri si nu reusesc sa extrag datele dintr-un alt sheet.
Adica datele sunt in Sheet1, iar selectia datei si returnarea rezultatelor sa se faca pe Sheet2. De ce nu se poate folosi formula specificata de tine si in alt sheet?