Completare automata din lista

Informatii despre cum se utilizeaza Microsoft Excel 2007. Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
awlex
Mesaje: 35
Membru din: Joi Feb 25, 2016 8:37 am

Completare automata din lista

Mesaj de awlex » Mie Aug 03, 2016 9:43 am

Salut vesnic nemultumit eu sunt ...
Bine de data asta vin cu o problema la un NIR as vrea sa fac un NIR putin mai destept care sa il completez cat mai usor ( toate astea din cauza distribuitorului care tot greseste facturile si se tot grabeste ca nu are timp )
Am macheta, eu o facusem sa adaug pretul fara TVA in partea dreapta si sa il ia de acolo in formular si sa calculeze tot dar nu e ok asa din cauza diferentelor si a rotunjirilor.
Asa ca am nevoie sa fac o lista cu toate produsele cum am facut sheet ul 2 si acolo sa am nume produs si pret ... eu as vrea sa completez in formular B14 de exemplu sa scriu "Pai" si el sa caute automat in sheet-ul 2 si sa imi arate Paine provizoriu si eu doar sa dau enter si sa il ia de bun dar in acelasi timp sa ia si pretul de la paine din sheet-ul 2 si sa il puna la F14 ca eu doar sa pun cantitatea in E14...

Sper ca nu sunt prea pretentios :D
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Completare automata din lista

Mesaj de Tzica » Mie Aug 03, 2016 10:47 am

Pai se poate folosind facilitatile oferite de NameManager, combinat cu un Index&Match, si DataValidation.Deci:
1.Definim tabelul in sheet-ul Produse;
2.Definim Name-ul pentru NumeProdus ;
3.In sheet-ul aferent Nir-ului, creeam cu ajutorul dataValidation, preluarea automata de nume;
4.In coloana PretFaraTva, introducem clasicul index&match (il precedam cu un IfError, pentru o prezentare mai agreabila);
5."Fortzam" rotunjirea la doua zecimale prin aplicarea formulei Roundup in coloana F.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Completare automata din lista

Mesaj de TudyBTH » Mie Aug 03, 2016 11:13 am

Sau,

o solutie care sa faca si filtrarea listei dupa caracterul/caracterele introduse o gasesti in fisierul atasat.
Pentru aceasta se creaza o lista si trebuiesc definite doua nume.
Lista am pus-o in sheet Produse si este definita cu formula array (se introduce cu CSE)

Cod: Selectaţi tot

=INDEX(prod;MATCH(0;COUNTIF($E$1:E1;prod)+(INDEX('NRCD-1 pagina'!$B$14:$B$29;COUNT('NRCD-1 pagina'!$E$14:$E$29)+1)<>LEFT(prod;LEN(INDEX('NRCD-1 pagina'!$B$14:$B$29;COUNT('NRCD-1 pagina'!$E$14:$E$29)+1))));0))
aceasta lista contine doar produsele ale caror denumire incepe cu caracterele introduse in NIR, in prima linie ce nu are completata cantitate.
Se defineste prod pentru lista cu produse (A2:A5 in fisierul exemplu) cu formula

Cod: Selectaţi tot

=OFFSET(Produse!$A$2;;;COUNTA(Produse!$A:$A)-1;1)
si fprod pentru lista filtrata cu formula

Cod: Selectaţi tot

=INDEX(prod;MATCH(0;COUNTIF($E$1:E1;prod)+(INDEX('NRCD-1 pagina'!$B$14:$B$29;COUNT('NRCD-1 pagina'!$E$14:$E$29)+1)<>LEFT(prod;LEN(INDEX('NRCD-1 pagina'!$B$14:$B$29;COUNT('NRCD-1 pagina'!$E$14:$E$29)+1))));0))
In NIR la denumire se foloseste Data Validation/List/formula

Cod: Selectaţi tot

=fprod
si se debifeaza Show error alert ...

Trebuie tinut cont ca Excelul are o autocompletare proprie cu elementele introduse deja in aceeasi coloana, de aceea am mutat textul din celulele B7 si B9 in col A.

O solutie mai comoda implica folosirea VBA
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.

awlex
Mesaje: 35
Membru din: Joi Feb 25, 2016 8:37 am

Re: Completare automata din lista

Mesaj de awlex » Mie Aug 03, 2016 12:22 pm

Varianta de la Tzica este cam ce vreau eu dar ...
Nu luati in calcul pretul fara TVA, pe mine ma intereseaza sa pun pretul cu TVA ca aici este problema cu furnizorul greseste la calcule rotunjiri sau la sume iar mie trebuie sa imi dea ca in anexa de preturi care iam oferit-o, preturile le voi trece cu tva in sheet-ul 2 de unde sa le ia automat in NIR
Deci in coloana G din NIR nu voi trece nimic.
Am observat ca daca pur si simplu adaug inca 5 produse se lungeste tabelul din sheet-ul 2 dar in NIR nu le mai recunoaste cu pretul, eu la produse voi avea aproape 90 produse ...
Cu datavalidation va fi cam greu pentru 90 de produse sa dau scrool la fiecare in parte eu mam gandit ca este o varianta sa apara singur scrisul ... de exemplu
daca pe aceeasi coloana am scris "Iaurt sticla de 900 Gr" si intr-o alta celula pe aceeasi coloana scriu "Ia" apare cu albastru numele si daca dau enter ia numele complet ... la asta ma refeream eu ca sa scrie singur numele ...

Varianta lui TudyBTH nu prea o inteleg si nici nu face ce i-mi doream eu ... sau poate nu stiu eu cum sa fac...

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

Re: Completare automata din lista

Mesaj de TudyBTH » Mie Aug 03, 2016 1:51 pm

Buna,

Varianta pe care am propus-o face exact acelasi lucru ca varianta d-lui Tzica, in plus filtreaza lista din Data Validetion, afisand in lista doar produsele ale caror denumiri incep cu litera/literele introduse in prima linie libera din NIR. Se considera prima linie libera, prima linie ce nu are completata cantitatea primita.
Deci, presupunem ca s-a ajuns la pozitia 3 din NIR. Daca in B16 (Denumire la poz 3) nu am introdus nici o litera, cand apasati pe sageata din dreapta apare o lista cu toate produsele.
Capture1.PNG
Daca se introduce litera "c", lista se restrange la produsele a caror denimire incepe lu litera "c" (ceapa, cartofi)
Capture2.PNG
Daca se introduc literele "ca" lista se restrange la produsele a caror denumire incepe cu "ca" (Cartofi)
Capture3.PNG
In mesajul anterior nu am introdus pretul pentru ca nu am inteles care dintre preturi este trecut in lista (cu sau fara TVA). Aveti in atasament un fisier actualizat cu pretul si unitatea de masura.
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.

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

Re: Completare automata din lista

Mesaj de Tzica » Mie Aug 03, 2016 3:22 pm

awlex >
"Am observat ca .... in NIR nu le mai recunoaste cu pretul". Probabil nu ati dat click pe eticheta cu EnableEditing (sugestia mea fiind lucrata in Ex2010).
"preturile le voi trece cu tva in sheet-ul 2 de unde sa le ia automat in NIR ". Pai... e cam aceeasi metoda, trebuie doar schimbata formula (formulele ) din sheet-ul NIR.Adica in coloana F punem Index Match asupra tabelului din sheet-ul 2 (pentru preluare valoare cu TVA), iar in coloana G punem formula de calcul pentru obtinerea PU fara TVA.
Altfel....io as acorda atentie asupra propunerii lui TudyBTH , e mult mai eleganta ! Si eu m-am lovit de problema "autocomplet" la DataValidation, dar nu am avut rabdarea (mintea !) sa caut o formula adecavata.
Sunt sigur ca daca reveniti cu intrebari TudyBTH o sa va indrume.

TudyBTH >
Io lucrez cu datavalidation de ...mai mult de 100 de item-uri (furnizori cu care nu se mai intretin relatii !!) deci, multumesc de formule . Probabil saptamana viitoare o sa pot sa le aplic si la "cearsafurile mele ".

PS.
Personal...nu ma omor dupa titulatura domnule, nici in viata reala, deci din punctul meu de vedere, e ok si fara ea. :-)

awlex
Mesaje: 35
Membru din: Joi Feb 25, 2016 8:37 am

Re: Completare automata din lista

Mesaj de awlex » Mie Aug 03, 2016 3:30 pm

da acum face cum ai spus dar daca adaug cateva campuri da o eroare si dispar produsele...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Completare automata din lista

Mesaj de Tzica » Mie Aug 03, 2016 3:50 pm

awlex>
Dap.Ai dreptate...nu se improspateaza lista din NIR. :-(
Dar sa avem rabdare ... .

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

Re: Completare automata din lista

Mesaj de TudyBTH » Mie Aug 03, 2016 3:55 pm

Formulele care fac filtrarea "cauta" literele introduse in prima linie necompletata (libera), in coloana B (Denumirea bunurilor ...).
Se considera prima linie libera, prima linie ce nu are completata cantitatea primita.
Deci, pentru ca formulele sa "stie" ca ati completat o linie din NIR si sa treaca la urmatoarea trebuie sa completati cantitatea primita.
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.

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

Re: Completare automata din lista

Mesaj de Tzica » Joi Aug 04, 2016 8:25 am

TudyBTH >
:oops:
De acord, singura mea scuza este ca , sunt prins cu altele.Saptamana viitoare aveam de gand sa aprofundez formulele. :oops: :oops:

Închis

Înapoi la “Intrebari despre Excel 2007”