VLOOKUP sau CEILING mai ciudat

Ce este nou in Microsoft Excel 2016?
Informatii despre cum se utilizeaza Microsoft Excel 2016
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
RAMBO
Mesaje: 474
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

VLOOKUP sau CEILING mai ciudat

Mesaj de RAMBO » Mar Iun 18, 2019 8:29 am

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.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Indigo-ONE
Mesaje: 433
Membru din: Mar Dec 11, 2018 8:54 pm

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de Indigo-ONE » Mar Iun 18, 2019 1:48 pm

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.


"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."
Albert Einstein

RAMBO
Mesaje: 474
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de RAMBO » Mie Iun 19, 2019 7:38 am

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.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Indigo-ONE
Mesaje: 433
Membru din: Mar Dec 11, 2018 8:54 pm

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de Indigo-ONE » Mie Iun 19, 2019 7:54 am

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.


"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."
Albert Einstein

RAMBO
Mesaje: 474
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de RAMBO » Mie Iun 19, 2019 8:36 am

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.

Tzica
Mesaje: 639
Membru din: Sâm Aug 11, 2012 10:52 pm

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de Tzica » Mie Iun 19, 2019 9:17 am

=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)))

TudyBTH
Moderator
Moderator
Mesaje: 993
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de TudyBTH » Mie Iun 19, 2019 9:42 am

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))
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

Indigo-ONE
Mesaje: 433
Membru din: Mar Dec 11, 2018 8:54 pm

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de Indigo-ONE » Mie Iun 19, 2019 11:25 am

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


"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."
Albert Einstein

Tzica
Mesaje: 639
Membru din: Sâm Aug 11, 2012 10:52 pm

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de Tzica » Mie Iun 19, 2019 12:13 pm

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...).
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

TudyBTH
Moderator
Moderator
Mesaje: 993
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: VLOOKUP sau CEILING mai ciudat

Mesaj de TudyBTH » Mie Iun 19, 2019 4:58 pm

@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
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

Scrie răspuns

Înapoi la “Intrebari despre Excel 2016”