formula care numara inregistrarile unice

Ce este nou in Microsoft Excel 2010?
Informatii despre cum se utilizeaza Microsoft Excel 2010
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
Închis
IrinaF
Mesaje: 41
Membru din: Mie Sep 30, 2009 10:10 am

formula care numara inregistrarile unice

Mesaj de IrinaF » Joi Mai 29, 2014 10:49 am

Buna ziua,

as vrea sa pot afisa intr-o celula numarul inregistrarilor unice dintr-o coloana a unui tabel
mai mult, as vrea sa numere inregistrarile unice din coloana respectiva, conditionate de existenta unei alte valori in alta coloana a tabelului

exemplul din fisier:
as vrea sa numar - cate aparitii diferite sunt pe coloana A (6)
- cate aparitii diferite sunt pe coloana A, dar care sa contina valoarea "1" in coloana B (5)


se pot face aceste lucruri doar cu formule, fara VBA?
Multumesc
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: formula care numara inregistrarile unice

Mesaj de IPP » Joi Mai 29, 2014 3:32 pm

Buna ziua

In fisierul atasat gasiti o propunere care se bazeaza pe o coloana suplimentara.

Pentru prima situatie (numararea unicatelor), am folosit coloana D. in D3 formula (ce va fi copiata apoi pe coloana in jos) =IF(COUNTIF($A$3:A3;A3)=1;A3;"dublura") iar numarul de unicate se afla in G3 folosind formula =COUNTIF(D3:D13;"<>dublura")

Pentru a doua situatie (numararea unicatelor cu o conditie suplimentara), am folosit coloana E. In E3 formula (ce va fi copiata apoi pe coloana in jos) =IF(COUNTIF($A$3:A3;A3)=1;IF(B3=1;A3)) iar numarul de unicate se afla in E3 folosind formula =COUNTIF(E3:E13;"<>false")

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

IrinaF
Mesaje: 41
Membru din: Mie Sep 30, 2009 10:10 am

Re: formula care numara inregistrarile unice

Mesaj de IrinaF » Vin Mai 30, 2014 7:52 am

multumesc
numai ca a doua formula nu returneaza corect. vezi exemplu pentru valoarea "u" din coloana A care este considerata dublura pentru 1 (col B) daca a fost anterior asociata valorii 2 (col B) - am modificat si atasat in fisier. era normal ca pe "u" sa il numere ca inregistrare pentru 1, nu sa il considere dublura.

mai mult, ideea era daca se putea face totul fara coloanele ajutatoare, pentru ca parametrul 1 sa poata fi modificat cu altul. si totul sa functioneze ca un filtru.

Multumesc
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: formula care numara inregistrarile unice

Mesaj de IPP » Vin Mai 30, 2014 8:32 am

Buna ziua

Din cate stiu, pentru o filtrare normala este obligatorie existenta unui camp ale carui valori sa stea la baza criteriului de filtrare. Chiar daca formula propusa de mine nu corespunde cred ca tot veti avea nevoie de una pentru a completa o coloana suplimentara. In ceea ce priveste parametrizarea, acea formula poate sa-si extraga parametrul si dintr-o celula predefinita.
Inca n-am auzit de o filtrare avand la baza direct o formula, cu atat mai putin una de care spuneti ca aveti nevoie.
Iar daca vorbim de filtrare in adevaratul sens al cuvantului, nu simpla ascundere de randuri, si daca veti opta pentru o solutie vba tot va fi nevoie de o coloana suplimentara in care codul macro sa scrie si sa ofere criteriul de filtrare....

Altfel, daca am inteles bine ce doriti (desi incep sa ma indoiesc de asta), incercati sa folositi un pivot table. In fisierul atasat, gruparea se face dupa Numar (iar aici puteti sa filtrati ce doriti) respectiv Id (unde de asemenea puteti filtra ce doriti). Valorile din pivot sunt numarate. Astfel, pornind de la primul fisier atasat de dvs., rezultatul poate sa apara sub forma: "pentru numarul 1 aveti 5 id-uri in dreptul carora 1 apare cate o data (pentru id c, n, t) respectiv de cate 2 ori pentru id u si x

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

Indigo
Mesaje: 774
Membru din: Sâm Sep 26, 2009 8:05 pm

Re: formula care numara inregistrarile unice

Mesaj de Indigo » Vin Mai 30, 2014 4:15 pm

mai mult, ideea era daca se putea face totul fara coloanele ajutatoare, pentru ca parametrul 1 sa poata fi modificat cu altul. si totul sa functioneze ca un filtru.
Se poate si fara coloane ajutatoare dar formulele sunt matriceale (se introduc prin apasarea simultana a tastelor Control, Shift si Enter, dupa ce se introduce formula in celula , apesi tasta F2 si apoi tastele Control, Shift si Enter - vei vedea ca apar accolade inaintea si la finalul formulei. Asta in caz ca nu stiai deja.)

Pentru numararea itemurilor unice de pe col A foloseste aceasta formula:

=SUM(IF(MATCH($A$3:$A$13;$A$3:$A$13;0)>=(ROW($A$3:$A$13)-ROW($A$3)+1);1;0))

Iar pentru
cate aparitii diferite sunt pe coloana A, dar care sa contina valoarea "1" in coloana B
foloseste formula:

=SUM(IF(FREQUENCY(IF($B$3:$B$13=$H$3;MATCH($A$3:$A$13;$A$3:$A$13;0)); ROW($A$3:$A$13)-ROW($A$3)+1);1))

in H3 ve pune numarul pe care il cauti in col B.

vezi in fisierul atasat.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

IrinaF
Mesaje: 41
Membru din: Mie Sep 30, 2009 10:10 am

Re: formula care numara inregistrarile unice

Mesaj de IrinaF » Lun Iun 02, 2014 9:23 am

Formulele de tip array sunt perfece pentru mine, mai ales ca funtioneaza si pe tabel dinamic.
Solutia cu pivot ar fi fost de rezerva.
Multumesc foarte mult pentru ajutor.

IrinaF

cip.st
Moderator
Moderator
Mesaje: 550
Membru din: Vin Iun 06, 2014 1:43 pm
Localitate: Iași

Re: formula care numara inregistrarile unice

Mesaj de cip.st » Mie Iun 18, 2014 3:15 pm

Știam că știu excel cam la nivel de campion olimpic faza pe sate, dar formulele de mai sus m-au dat pe spate...
Indigo, te rog, fii bun și explică-mi pe îndelete cum funcționează că nu mă prind nici bătut. De aplicat am aplicat cea cu număratul înregistrărilor unice (la completarea D394 aveam o coloană suplimentară cu countifuri) dar cu formula ta e meserie!

dip
Mesaje: 201
Membru din: Sâm Feb 06, 2010 11:09 pm
Localitate: Brasov

Re: formula care numara inregistrarile unice

Mesaj de dip » Mar Oct 17, 2017 10:39 am

O formula cu functia SUMPRODUCT pentu numarare valori unice:
=SUMPRODUCT((B1:B1500<>"")/COUNTIF(B1:B1500;B1:B1500&""))
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: formula care numara inregistrarile unice

Mesaj de Nills » Mar Oct 17, 2017 1:59 pm

@ dip,

Ai raspuns la un mesaj din 2014 :lol:

si bineinteles ca mai sunt si alte formule:
=SUM(IF(A3:A15<>"",1/COUNTIF(A3:A15,A3:A15))) evident formule array

dar daca nu sunt celule goale atunci formula se poate simplifica:
=SUM(1/COUNTIF(A3:A13,A3:A13)) evident tot array formula
iar cea cu sumproduct...
=SUMPRODUCT(1/COUNTIF(B3:B13;B3:B13))
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 2010”