ITLearning/Dr.Excel

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

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

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

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

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.

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

Ș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!

O formula cu functia SUMPRODUCT pentu numarare valori unice:
=SUMPRODUCT((B1:B1500<>"")/COUNTIF(B1:B1500;B1:B1500&""))

@ dip,

Ai raspuns la un mesaj din 2014

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))