Pagina 1 din 1

VLOOKUP sau CEILING mai ciudat

Scris: Mar Iun 18, 2019 8:29 am
de RAMBO
Buna ziua.
Fie fisierul Exemplu.xlsx. Exista un mic tabel (Nomenclator) cu doua coloane. In K1:K5 sunt niste valori introduse de mana cu titlu de exemplu. Ideea este ce formula ar trebui in K1:K5 pentru a returna acele valori tinand cont de urm conditii: cauta valoarea din H1:H5 in nomenclator coloana greutate si returneaza valoarea corespondenta din nomenclator coloana pret. Suna simplu (un fel de VLOOKUP), DAR, valorile din H1:H5 nu sunt intotdeauna exact match cu valorile din col greutate, iar valoarea returnata trebuie sa fie in acest caz corecpondentul imediat urmator ca marime din coloana pret. Nu stiu daca am explicat pe inteles, sper sa se inteleaga din fisier rangeul K1:K5, unde le-am introdus de mana.
Multumesc.

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mar Iun 18, 2019 1:48 pm
de Indigo-ONE
Salut,
Fie fisierul Exemplu.xlsx.
Fie.
In K1:K5 sunt niste valori introduse de mana cu titlu de exemplu.
Nu sunt. Si nici nu ar fi normal sa fie mai multe valori...asta dupa explicatiile tale...
DAR, valorile din H1:H5 nu sunt intotdeauna exact match cu valorile din col greutate,...
care valori?

In explicatii spui ceva iar in fisier nu se regasesc explicatiile date....

Solutia ar fi simpla dupa cum am inteles eu, dar deoarece nu imi place sa fac presupuneri si sa tot refac formule/coduri VBA, deoarece nu au fost expuse corect si toate datele problemei, ma abtin sa iti ofer vreo solutie.

P.S. Ar trebui sa stii cum se pune problema avand in vedere numarul de postari si vechimea pe site.

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 7:38 am
de RAMBO
OK. Mea culpa. Fisierul nu era complet (am uitat un save). Incerc sa reexplic cu un nou fisier. Sunt doua tabele: nomenclator si Table2. In nomenclator coloana greutate sunt niste valori ce reprezinta niste praguri. In Table2 coloana valori input sunt niste valori numerice ce reprezinta niste greutati (returnate de un cantar). In coloana "valori ce ar trebui returnate de formula" am scris eu manual valorile corespondente din nomenclator (coloana pret) conform incadrarii functie de greutate. Cu alte cuvinte la prima vedere ar fi un banal VLOOKUP, dar daca folosesc aceasta formula nu returneza valorile corecte, pentru ca daca greutatea depaseste cat de putin valoarea unui prag, atunci pretul corespondent va fii cel al pragului imediat urmator and so on.
Sper ca acum am facut un pic de lumina si imi cer scuze pentru neintelegeri.

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 7:54 am
de Indigo-ONE
In al doilea fisier, la valoarea 40 din coloana (valori input) ai pus 77,43 ceea ce nu corespunde cu cerintele tale...
pentru ca daca greutatea depaseste cat de putin valoarea unui prag, atunci pretul corespondent va fii cel al pragului imediat urmator and so on.
daca ai gresit si acolo nu ar trebui sa fie o valoare mai mare, si rezultatul sa fie zero (0) atunci foloseste urmatoarea formula:
Este o formula matriceala (se introduce cu Ctrl+Shift+Enter :) )

In H2
=MIN(IF((nomenclator[greutate]>=G2);nomenclator[pret]))
sau
=MIN(IF((nomenclator[greutate]>=G2),nomenclator[pret]))
functie de setarile regionale.

valori input valori ce ar trebui returnate de formula
1-----------5
1,01-------5,64
5,99-------9,5
40---------77,43
18,1-------41,3

cele cu rosu si ingrosate nu sunt corecte (nu sunt conforme cu cerintele tale.

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 8:36 am
de RAMBO
Asta e. Multumesc. Intr-adevar la valoarea 40 (mai mare decat cel mai mare prag) pretul va fii cel maximal posibil, adica cel corespondent ultimului prag, adica 77.43 indiferent daca sau cu cat se depaseste ultimul prag. Sa vad daca pot modifica formula in acest sens (deocamdata nu am reusit).
PS: chiar si cu un exemplu inca gresit (valorile cu rosu) tot ai prins ideea. Jos palaria.

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 9:17 am
de Tzica
=IF($H$1=Tabel1[greutate];
VLOOKUP($H$1;Tabel1[[greutate]:[pret]];2;FALSE);
IF($H$1>=Tabel1[greutate];
INDEX(Tabel1[[greutate]:[pret]];MATCH(MAX(Tabel1[greutate]);Tabel1[greutate];0);2);
INDEX(Tabel1[[greutate]:[pret]];MATCH($H$1;Tabel1[greutate];-1);2)))

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 9:42 am
de TudyBTH
Buna,

Avand in vedere ca in 'nomenclator' nu se introduc date de catre utilizator si deci aceasta lista AR PUTEA fi mentinuta ordonata, poti folosi urmatoarea formula (normala, fara Ctrl+Shift+Enter):

Cod: Selectaţi tot

=INDEX(nomenclator[pret];IFERROR(MATCH([@[valori input]];nomenclator[greutate];-1);1))
Lista nomenclator trebuie sa fie ordonata DESCRESCATOR.

Sugestia mea este insa sa definesti nomenclatorul astfel incat sa nu mai fie nevoie de exceptii. Adica sa introduci pretul pentru pragul minim.
Untitled.jpg
in acest caz, cu lista ordonata CRESCATOR:

Cod: Selectaţi tot

=INDEX(nomenclator4[pret];MATCH([@[valori input]];nomenclator4[greutate];1))

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 11:25 am
de Indigo-ONE
PS: chiar si cu un exemplu inca gresit (valorile cu rosu) tot ai prins ideea. Jos palaria.
Eu insist pe ideea de a avea cerinte verificate, deoarece primesti o formula apoi ... ooopss acolo era gresit sau am uitat sa spun ca ... si in felul acesta cel/cei care te ajuta pierd timp cu ajustari de formule sau chiar cu constructia altor formule. :lol:

Pentru partea cu "daca in valori input apare o valoare mai mare decat cea mai mare valoare din greutate ..."

=MIN(IF((nomenclator[greutate]>=[@[valori input]]);nomenclator[pret];MAX(nomenclator[pret])))

@TudyBTH

In fisierul atasat de tine in a doua foaie, formula folosita nu da rezultatele cerute. Vezi valoarea din dreptul valorii 1.01

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 12:13 pm
de Tzica
Avand in vedere, cerintele ( valoare mai mare, si / sau neexistenta in tabel), cred ca ar trebui folosit clasicul IF... in diverse combinatii.
Pentru a putea specula acel "mai mare, apropiat de valoarea introdusa" ( acel -1 din formula) tabelul trebuie ordonat in ordine descrescatoare.
Am abordat si formula CSE, si o aplicare a functiei Aggregate. ( Cei drept cam dodoloatze formulele..dar...).

Re: VLOOKUP sau CEILING mai ciudat

Scris: Mie Iun 19, 2019 4:58 pm
de TudyBTH
@indigo
La mine, pentru 1.01 intoarcse 5.64. Mi se pare corect.

Exista intradevar in a doua foaie o abatere de la reguli, la valorile exacte ale pragului (vezi valoarea 1.00)

Eu raman la parerea ca cel mai simplu este sa redefineasca tabelul nomenclator astfel incat sa poata scoate valorile dorite cu un simplu LOOKUP

Cod: Selectaţi tot

=LOOKUP([@[valori input]];nomenclator6[greutate];nomenclator6[pret])
vezi foaia a 3-a