Completare automata din lista

Informatii despre cum se utilizeaza Microsoft Excel 2007. Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
H2SO4
Mesaje: 117
Membru din: Mar Apr 19, 2016 12:50 pm

Re: Completare automata din lista

Mesaj de H2SO4 » Joi Aug 04, 2016 7:11 pm

Tzica scrie: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 ".
Ca sa nu fie asa incarcate acele DV (si pentru ca ai si)
furnizori cu care nu se mai intretin relatii !!)
de ce nu folosesti o metoda usoara de eliminare a datelor care nu te mai intereseaza - fara a le sterge - adica intro coloana (sa zicem A) treci toti furnizorii iar in coloana B pui un "x" in dreptul datelor de interes (este mai usor de intretinut lista) apoi cu ajutorul unei formule in coloana C, preiei doar datele cu bifa din col B. Aceasta coloana o vei folosi pentru lista din DV.
“Tell me and I forget, teach me and I may remember, involve me and I learn.”

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

Re: Completare automata din lista

Mesaj de Tzica » Vin Aug 05, 2016 9:39 am

H2SO4>
Multumesc de sugestie.Nu stiu cum de nu am aplicat asa ceva si in excel (in acces am la furnizori / clienti un camp checkbox Yes/No ) !!! :oops:

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

Re: Completare automata din lista

Mesaj de Tzica » Lun Aug 08, 2016 6:05 pm

H2SO4>

Mi-ai dat de lucru !! As cauta o solutie fara coloana ajutatoare, folosind name managerul....dar nu stiu daca e posibil.Iaca o prima incercare (fireste...nu intoarce decat prima prima valoare gasita in lista).
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 » Mar Aug 09, 2016 9:16 am

Ok.Doar pentru a clarifica , ordona acest topic:
1. Speta cu autocompletare, plecand de la primele doua litere. De departe abordarea lui TudyBTH e cea mai utila.Aceasta abordare e utila, folositoare cand avem de completat liste (exemplul fiin NIR-ul) in care introducem denumiri diferite, iar autocompletul nu poate sa-si faca datoria, neavand introduse denumiri;
2.Eliminare diverse denumiri (speta cu furnizori inactivi).Gandindu-ma mai bine, nu e chiar atat de utila o filtrare a furnizorilor inactivi.Bine...depinde cum folosim.Eu am un pled de cateva mii de randuri, (cearsaf o sa-i zic cand o depasi zecemii), in care am introduse datele despre facturi...nu detaliile facturii (Data, NumarFurnizor,Total etc..) .Practic , la pledul meu de cateva mii de randuri, autocompletul isi face datoria. La sugestia lui H2SO4 , am reusit dar...numai cu coloana ajutatoare.
Nu stiu care din formulele folosite, pentru/in name manager ar fi mai eficienta:
FiltruFurnizor:

Cod: Selectaţi tot

OFFSET(Sheet1!$H$4;0;0;COUNTA(Sheet1!$H:$H)-1)
Sau, mai pe bajbaitelea, Nfilfur (bine...aici e clar, trebuie dragalita si mai in jos formula de pe coloana H)

Cod: Selectaţi tot

Sheet1!$H$4:$H$20
Coloana ajutatoare, coloana H are clasica formula de filtrare , CSE:

Cod: Selectaţi tot

{=IF(ROWS($H$4:H4)>$F$4;"";INDEX(Table1[[#All];[NumeFurnizor]];SMALL(IF((--ISTEXT(Table1[NumeFurnizor])*(Table1[Status]=1));ROW(Table1[NumeFurnizor])-ROW(Table1[[#Headers];[NumeFurnizor]])+1);ROWS($H$3:H3))))}
M-am obisnuit sa folosesc un numar de control, in locul clasicului Ifferor, pentru a limita calcularea formulei.
Ramane simpla curiozitatate...se poate fara coloana ajutatoare ??? fireste..nonvba !
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Completare automata din lista

Mesaj de TudyBTH » Mar Aug 09, 2016 11:33 am

Salut,

Formula de mai jos (folosita in contextul fisierului cu NIR) am adaptat-o pentru a elimina din lista produsele marcate cu "Inactiv" in coloana "Status". Deci se poate adapta pentru filtrarea din coloana H in fisierul cu furnizori.

Cod: Selectaţi tot

=IFERROR(INDEX(prod;MATCH(0;COUNTIF($F$1:F1;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))))+(OFFSET(prod;0;3)="Inactiv");0));"")
rezultand un range ("fprod" aici in NIR) care poate fi definit cu

Cod: Selectaţi tot

=OFFSET(Produse!$F$2;;;COUNTIF(Produse!$F$2:$F$10000;"?*");1)
Selectia furnizorilor cu Data Validation fara coloana ajutatoare implica sortarea tabelului "Furnizori" dupa valoarea din "Status".
Eliminarea coloanei H (cu filtrea) ar necesita executarea filtrarii in Data Validation, lucru ce nu poate fi realizat din doua motive:
  • 1. DV nu recunoaste formulele array
    2. (Chiar daca am scie o formula normala - nu am incercat dar e posibil sa poata fi realizat si in cazul de fata) DV nu accepta un range discontinuu
Deci, avand in vedere ca se pune problema filtrarii unui numar destul de mare de itemuri iar actualizarea listei de furnizori se face relativ rar (fata de frecventa extragerii de date din lista), se poate renunta complet la filtrarea cu formula (coloana H) dar trebuie efectuata sortarea tabelului furnizorilor dupa fiecare actualizare.
In acest caz voi avea un range continuu ("FiltSortProd") ce poate fi folosit in DV fara coloana ajutatoare, definit cu formula (pentru furnizorii activi marcati cu 1 in Status)

Cod: Selectaţi tot

=OFFSET(Table1[NumeFurnizor];;;COUNT(Table1[Status]);1)
In concluzie,
  • - folosirea unei coloane ajutatoare pentru filtrare implica (nu in toate cazurile) folosirea formulelor array care pot incetinii executia la un numar foarte mare de itemuri.
    - pe de alta parte, coloana ajutatoare, pe langa filtrarea itemurilor inactive poate realiza si filtrarea dupa caracterele introduse, lucru foarte util in selectarea dintr-o lista lunga,
    - solutia optima, excluzand VBA, depinde de numarul itemurilor din lista, daca numarul lor nu duce la o viteza prea mare de executie as alege prima varianta, daca nu adoua
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 » Mar Aug 09, 2016 12:19 pm

TudyBTH>
Multumesc pentru rabdare. Cel putin in cazul spetei mele (furnizori), explicatiile sunt mai mult decat clare.Intr-adevar, fara coloana ajutatoare, e un pic mai dificil in sensul actualizarii (selectam in tabel coloana status numai 1 ).

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

Re: Completare automata din lista

Mesaj de TudyBTH » Mar Aug 09, 2016 12:24 pm

Am adaptat formulele pentru fisierul cu furnizori (Table2)
In coloana "Indicator" poate fi orice camp care urmeaza a fi introdus manual (cum era cantitatea in NIR) cu rolul de a marca linia respectiva ca fiind linie completata
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 10, 2016 8:47 am

TudyBTH >
Chapou bas ! Eu deja am fost multumit de formule, faptul ca ai si aprofundat/dezvoltat abordarea e mai mult decat mi-as fi dorit.Multumesc.

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

Re: Completare automata din lista

Mesaj de Tzica » Mar Noi 08, 2016 6:09 pm

Updatez acest topic, pentru a nu mai deschide altul...e doar o alta abordare a aceleasi probleme, dropdownlist ( cu sugestii ) in tabel.

Sursa:OdonareAlfabetica

Deci, de unde mi-as fi dorit fara coloana ajutatoare...am ajuns la..cel putin trei !!. Cu ajutorul link-ului de mai sus, e rezolvata problema ordonarii alfabetice (am folosit ..monstrul de formula de la sfarsit, rezultatul se poate vedea in sheet-ul calculatii coloana C).
In sheet-ul Calculatii:
- Formula din coloana B aloca un numar de ordine, asupra coloanei C (FiltrutblEmitent), in functie de litera /literele introduse in sheet-ul IntroducereDate celula C2;
- In coloana F, "apar" sugestiile, pe baza numarului alocat in coloana B;
- Celula E2 , contine formula folosita pentru Numele Nvalidemitent, sursa pentru DropDownList din foaia Introducere Date.

Problema de care ma lovesc, e ca in cazul transformarii zonei din IntroducereDate in Tabel, nu reusesc sa gasesc o formula care sa imi reinitializeze "sugestiile"... la adaugarea unui nou rand , (completare tip operatiune de exemplu, sau poa sa fie si coloana Data) TipOperatiune . Si asta...numai ca sa scap de ..sortarea tabelului Emitenti , asta e...suntem oameni...mai uitam !
Multumesc .
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Nills
Mesaje: 211
Membru din: Sâm Ian 23, 2016 11:24 am

Re: Completare automata din lista

Mesaj de Nills » Joi Noi 10, 2016 2:41 pm

Se simte lipsa dl. Indigo.
Sunt convins ca dl. Indigo ar fi venit cu o formula mai putin stufoasa de aranjare alfabetica a emitentilor si cu preluare dinamica a datelor.
Trimite-i un mail.
I don't care what you think of me! Unless you think I'm awesome – in which case, you're right! Carry on :D

Închis

Înapoi la “Intrebari despre Excel 2007”