Extragere date care se termina intr-un anumit caracter

Ce este nou in Microsoft Excel 2016?
Informatii despre cum se utilizeaza Microsoft Excel 2016
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
IolandaP
Mesaje: 4
Membru din: Vin Noi 23, 2018 2:19 pm

Extragere date care se termina intr-un anumit caracter

Mesaj de IolandaP » Vin Noi 23, 2018 2:25 pm

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

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de IPP » Vin Noi 23, 2018 3:02 pm

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

IolandaP
Mesaje: 4
Membru din: Vin Noi 23, 2018 2:19 pm

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de IolandaP » Vin Noi 23, 2018 3:44 pm

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

Indigo-99
Mesaje: 60
Membru din: Dum Sep 02, 2018 8:21 am

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de Indigo-99 » Vin Noi 23, 2018 4:01 pm

Ataseaza un fisier.
"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
Sir Arthur C. Clarke

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de IPP » Vin Noi 23, 2018 4:11 pm

Buna ziua

@ IolandaP
Atasat gasiti fisierul de test in care am obtinut exact rezultatele asteptate, cf. enuntului problemei.
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.
Daca vreti sa schimbati "3" cu "5", dupa editarea formulei nu uitati sa apasati Ctrl+Shift+Enter si apoi sa copiati formula in jos

IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

IolandaP
Mesaje: 4
Membru din: Vin Noi 23, 2018 2:19 pm

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de IolandaP » Mie Noi 28, 2018 11:56 am

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

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de IPP » Mie Noi 28, 2018 12:50 pm

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

IolandaP
Mesaje: 4
Membru din: Vin Noi 23, 2018 2:19 pm

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de IolandaP » Mie Noi 28, 2018 1:20 pm

Multumesc pentru raspuns si promptitudine - este exact ce cautam :D

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

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de IPP » Mie Noi 28, 2018 1:34 pm

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

Indigo-99
Mesaje: 60
Membru din: Dum Sep 02, 2018 8:21 am

Re: Extragere date care se termina intr-un anumit caracter

Mesaj de Indigo-99 » Joi Noi 29, 2018 12:52 am

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.
Sa inteleg ca te-ar fi interesat un cod VBA? sau e OK cu formule.
"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
Sir Arthur C. Clarke

Scrie răspuns

Înapoi la “Intrebari despre Excel 2016”