Extragere date care se termina intr-un anumit caracter
Extragere date care se termina intr-un anumit caracter
Buna,
Am o intrebare referitoare la extragerea datelor dintr-un raport.
Am o lista de referinte de tichete (de tip litere+cifre), si am nevoie ca pe o foaie separata (in acelasi document) sa mi se extraga primele 3 tichete care se termina in cifra 3.
Spre exemplu
Pe foaia "Raport" am urmatoarele tichete :
10463
10459
10441
10425
10398
10389
10383
10380
10378
De aici, vreau ca pe foaia "Sinteza" sa mi se listeze primele 3 tichete care se termina cu 3. Ar trebui sa am 10463, 10383 si N/A.
Am incercat cu =INDEX($A:$A;MATCH("*3";$A:$A;0)), dar imi gaseste doar primul rezultat.
Care ar fi formula potrivita?
Multumesc de ajutor.
Iolanda
Am o intrebare referitoare la extragerea datelor dintr-un raport.
Am o lista de referinte de tichete (de tip litere+cifre), si am nevoie ca pe o foaie separata (in acelasi document) sa mi se extraga primele 3 tichete care se termina in cifra 3.
Spre exemplu
Pe foaia "Raport" am urmatoarele tichete :
10463
10459
10441
10425
10398
10389
10383
10380
10378
De aici, vreau ca pe foaia "Sinteza" sa mi se listeze primele 3 tichete care se termina cu 3. Ar trebui sa am 10463, 10383 si N/A.
Am incercat cu =INDEX($A:$A;MATCH("*3";$A:$A;0)), dar imi gaseste doar primul rezultat.
Care ar fi formula potrivita?
Multumesc de ajutor.
Iolanda
Re: Extragere date care se termina intr-un anumit caracter
Buna ziua
In mod normal eu as fi pus pe o coloana noua o formula pentru extragerea primului caracter din dreapta (functia Right), apoi o filtrare urmata de copy paste special, values pentru rezultatul dorit.
sau as fi folosit un macro.
Altfel, presupunand ca informatia sursa se afla in foaia Raport de la A2:A10 si rezultatul trebuie sa apara in foaia Sinteza incepand tot cu A2, in foaia Sinteza incercati sa folositi o formula CSE (deci se confirma dupa scriere nu cu Enter ci apasand Ctrl+Shift+Enter):
=INDEX(Raport!$A$2:$A$10;SMALL(IF(RIGHT(Raport!$A$2:$A$10;1)="3";ROW(Raport!$A$2:$A$10)-ROW(INDEX(Raport!$A$2:$A$10;1;1))+1);ROW()-1))
dupa care copiati formula in jos cat e necesar. Daca nu va gasi decat 2 valori atunci se va afisa o eroare.
Nota: daca aveti setari regionale USA atunci schimbati peste tot in formula de mai sus ; cu ,
IP
In mod normal eu as fi pus pe o coloana noua o formula pentru extragerea primului caracter din dreapta (functia Right), apoi o filtrare urmata de copy paste special, values pentru rezultatul dorit.
sau as fi folosit un macro.
Altfel, presupunand ca informatia sursa se afla in foaia Raport de la A2:A10 si rezultatul trebuie sa apara in foaia Sinteza incepand tot cu A2, in foaia Sinteza incercati sa folositi o formula CSE (deci se confirma dupa scriere nu cu Enter ci apasand Ctrl+Shift+Enter):
=INDEX(Raport!$A$2:$A$10;SMALL(IF(RIGHT(Raport!$A$2:$A$10;1)="3";ROW(Raport!$A$2:$A$10)-ROW(INDEX(Raport!$A$2:$A$10;1;1))+1);ROW()-1))
dupa care copiati formula in jos cat e necesar. Daca nu va gasi decat 2 valori atunci se va afisa o eroare.
Nota: daca aveti setari regionale USA atunci schimbati peste tot in formula de mai sus ; cu ,
IP
Re: Extragere date care se termina intr-un anumit caracter
Multumesc pentru raspunsul rapid!
Am incercat cum m-ati sfatuit si obtin referinte care incep cu 3.
Ce observ, in schimb:
- acele referinte nu sunt primele 3 din lista, ci cele mai mici 3 referinte din toata lista care se termina cu 3
- am inlocuit in formula constanta 3 cu 5 si nu am obtinut niciun rezultat, desi am tichete care se termina in 5
Tind sa cred ca are o legatura cu functia "SMALL" (cu care nu sunt foarte familiarizata)? Daca nu, care ar fi explicatia si cum pot sa corectez?
inca o data multumesc,
Iolanda
Am incercat cum m-ati sfatuit si obtin referinte care incep cu 3.
Ce observ, in schimb:
- acele referinte nu sunt primele 3 din lista, ci cele mai mici 3 referinte din toata lista care se termina cu 3
- am inlocuit in formula constanta 3 cu 5 si nu am obtinut niciun rezultat, desi am tichete care se termina in 5
Tind sa cred ca are o legatura cu functia "SMALL" (cu care nu sunt foarte familiarizata)? Daca nu, care ar fi explicatia si cum pot sa corectez?
inca o data multumesc,
Iolanda
Re: Extragere date care se termina intr-un anumit caracter
Ataseaza un fisier.
"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
Sir Arthur C. Clarke
Sir Arthur C. Clarke
Re: Extragere date care se termina intr-un anumit caracter
Buna ziua
@ IolandaP
Atasat gasiti fisierul de test in care am obtinut exact rezultatele asteptate, cf. enuntului problemei.
IP
@ IolandaP
Atasat gasiti fisierul de test in care am obtinut exact rezultatele asteptate, cf. enuntului problemei.
Daca vreti sa schimbati "3" cu "5", dupa editarea formulei nu uitati sa apasati Ctrl+Shift+Enter si apoi sa copiati formula in josvreau ca pe foaia "Sinteza" sa mi se listeze primele 3 tichete care se termina cu 3. Ar trebui sa am 10463, 10383 si N/A.
IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Re: Extragere date care se termina intr-un anumit caracter
Multumesc mult pentru raspuns.
Atasez fisierul pe care am lucrat conform sfaturilor dvs (cu toate referintele de lucru), unde la constanta "3" mi se selecteaza primele 3 cele mai mici referinte din lista (in ordine crescatoare) si nu primele 3 referinte care se termina in 3. Pe de alta parte, daca setez constanta "5" nu mi se afiseaza nimic in lista, desi am referinte care se termina in "5".
Inca o data multumesc pentru sfaturi.
Iolanda
Atasez fisierul pe care am lucrat conform sfaturilor dvs (cu toate referintele de lucru), unde la constanta "3" mi se selecteaza primele 3 cele mai mici referinte din lista (in ordine crescatoare) si nu primele 3 referinte care se termina in 3. Pe de alta parte, daca setez constanta "5" nu mi se afiseaza nimic in lista, desi am referinte care se termina in "5".
Inca o data multumesc pentru sfaturi.
Iolanda
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Re: Extragere date care se termina intr-un anumit caracter
Buna ziua
Nu orice formula se preteaza la a folosi drept argument intreaga coloana. Formula propusa de mine e una dintre ele asa ca am pus referintele reale, cf. ultimului fisier atasat de dvs.
De asemenea, formula initiala se baza si pe faptul ca prima formula de extragere a informatiei se va afla pe randul 2; la dvs. se afla pe randul 6 asa ca in fisierul atasat gasiti formulele ajustate cf. precizarilor facute. Verificati daca sunt rezultatele asteptate.
Altfel, poate se va oferi altcineva sa va ofere o solutie bazata pe referinte pentru intreaga coloana. Strict din punctul meu de vedere (formulele folosite fiind mari consumatoare de resurse, poate sunt greu de editat) eu as fi incercat o solutie bazata pe macro
IP
Nu orice formula se preteaza la a folosi drept argument intreaga coloana. Formula propusa de mine e una dintre ele asa ca am pus referintele reale, cf. ultimului fisier atasat de dvs.
De asemenea, formula initiala se baza si pe faptul ca prima formula de extragere a informatiei se va afla pe randul 2; la dvs. se afla pe randul 6 asa ca in fisierul atasat gasiti formulele ajustate cf. precizarilor facute. Verificati daca sunt rezultatele asteptate.
Altfel, poate se va oferi altcineva sa va ofere o solutie bazata pe referinte pentru intreaga coloana. Strict din punctul meu de vedere (formulele folosite fiind mari consumatoare de resurse, poate sunt greu de editat) eu as fi incercat o solutie bazata pe macro
IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Re: Extragere date care se termina intr-un anumit caracter
Multumesc pentru raspuns si promptitudine - este exact ce cautam
E drept ca in formatul actual, formulele sunt greoaie si mai greu de gestionat, insa din pacate nu stapanesc functia macro, altfel as fi plecat cu siguranta de aici.
Inca o data multumesc.
O zi buna,
iolanda
E drept ca in formatul actual, formulele sunt greoaie si mai greu de gestionat, insa din pacate nu stapanesc functia macro, altfel as fi plecat cu siguranta de aici.
Inca o data multumesc.
O zi buna,
iolanda
Re: Extragere date care se termina intr-un anumit caracter
Buna ziua
Daca ramaneti la ideea de formula, cu ce v-as mai putea eventual ajuta, vedeti in fisierul atasat unde:
-am definit in Name Manager (pentru a vedea, apasati Ctrl+F3) un domeniu de celule numit "referinta". Practic aici ar trebui sa editati referintele strict pentru informatia dvs. (Acum se refera la domeniul de celule $A$2:$A$81, modificati doar atunci cand e nevoie, dar in niciun caz nu specificati toata coloana drept referinta.
-am inlocuit formula dvs. (si apoi copiere in jos) din H6 cu cea de mai jos:
=INDEX(referinta;SMALL(IF(--RIGHT(referinta;1)=VLOOKUP($H$5;$A$6:$B$10;2;0);ROW(referinta)-ROW(INDEX(referinta;1;1))+1);ROW()-5))
(la confirmare se apasa Ctrl+Shift+Enter)
IP
Daca ramaneti la ideea de formula, cu ce v-as mai putea eventual ajuta, vedeti in fisierul atasat unde:
-am definit in Name Manager (pentru a vedea, apasati Ctrl+F3) un domeniu de celule numit "referinta". Practic aici ar trebui sa editati referintele strict pentru informatia dvs. (Acum se refera la domeniul de celule $A$2:$A$81, modificati doar atunci cand e nevoie, dar in niciun caz nu specificati toata coloana drept referinta.
-am inlocuit formula dvs. (si apoi copiere in jos) din H6 cu cea de mai jos:
=INDEX(referinta;SMALL(IF(--RIGHT(referinta;1)=VLOOKUP($H$5;$A$6:$B$10;2;0);ROW(referinta)-ROW(INDEX(referinta;1;1))+1);ROW()-5))
(la confirmare se apasa Ctrl+Shift+Enter)
IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Re: Extragere date care se termina intr-un anumit caracter
Sa inteleg ca te-ar fi interesat un cod VBA? sau e OK cu formule.E drept ca in formatul actual, formulele sunt greoaie si mai greu de gestionat, insa din pacate nu stapanesc functia macro, altfel as fi plecat cu siguranta de aici.
"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
Sir Arthur C. Clarke
Sir Arthur C. Clarke