Compter le nombre de cellules sans les doublons

Moonshine33 · 6 Septembre 2017

Bonjour,
Dans une base de données, je souhaite compter le nombre de cellules d'une colonne sans compter les doublons, sous condition d'une date située dans une autre colonne. Ci-joint un fichier exemple pour mieux comprendre ma demande.
Ma demande est de calculer par exemple pour le mois de janvier 2017 situé en colonne A, le nombre de cellules différentes situées en colonne B, mais sans compter les récurrences.
En gros, je souhaite pouvoir compléter le tableau que j'ai mis sur la page (où ma valeur sera toujours 4 pour cet exemple, car je n'ai que 4 produits A-B-C-D), mais avec une formule excel ou une macro.
J'ai vu la formule : {=SOMME(SI(PLAGE<>"";1/NB.SI(PLAGE;PLAGE))), mais ça ne fonctionne pas comme je souhaite...
Merci d'avance à ceux qui prendront le temps de m'aider !
Moonshine.

pierrejean · 6 Septembre 2017

Bonjour Mooshine33

Un essai avec une fonction personnalisée

job75 · 6 Septembre 2017

Bonjour Moonshine33, Pierre,

Formule matricielle en F15 :

Code:

=NB(EQUIV({"A"."B"."C"."D"};REPT($B2:$B33;(MOIS($A2:$A33)=--GAUCHE(F14;2))*(ANNEE($A2:$A33)=--DROITE(F14;4)));0))

A valider par Ctrl+Maj+Entrée et tirer vers la droite.

Fichier joint.

A+

job75 · 6 Septembre 2017

Re,

Il est plus classique de mettre des dates en ligne 14, la formule est un peu plus simple :

Code:

=NB(EQUIV({"A"."B"."C"."D"};REPT($B2:$B33;(MOIS($A2:$A33)=MOIS(F14))*(ANNEE($A2:$A33)=ANNEE(F14)));0))

Fichier (2).

A+

KIM · 6 Septembre 2017

Bonjour le fil, job75, Moonshine33,
Bonjour pierrejean,
@pierrejean,
Ta fonction m'a intéressé, j'ai modifié la 1è colonne en texte et supprimé le test du format dans la fonctionne.
Est-il possible de rajouter un 2è critère dans ta fonction pour le calcul du nombre sans doublons car mes données sont sur 3 col et non sur 2 ? voir onglet type.
Merci d'avance
KIM

hbenalia · 6 Septembre 2017

Bonjour à tous,
Un essai dans le fichier attaché avec une formule matricielle contenant deux plages dynamiques...
Cordialement

job75 · 6 Septembre 2017

Re, salut hbenalia,

Par curiosité j'ai testé les diverses solutions (en ajoutant 08/2017 chez pierrejean et hbenalia) :

- pierrejean (post #2) recalcul en 1,22 millième de seconde

- job75 (posts #3 ou 4) recalcul en 0,29 millième de seconde

- hbenalia (post #6) recalcul en 0,38 millième de seconde - mais l'année n'est pas précisée...

A+

zebanx · 6 Septembre 2017

job75 à dit:
Re, salut hbenalia,
- job75 (posts #3 ou 4) recalcul en 0,29 millième de seconde

Code:

=NB(EQUIV({"A"."B"."C"."D"};REPT($B2:$B33;(MOIS($A2:$A33)=MOIS(F14))*(ANNEE($A2:$A33)=ANNEE(F14)));0))

Bonsoir JOB75 , hbnelia, Pierre-Jean, Kim, Moonshine et bonsoir à tous.

@job75
Ta formule est très bien et la plus rapide mais peut-on rendre l'array "A,B,C,D" non limité stp ?

@ Pierre-Jean :
Bonsoir,
superfonction (!), comme souligné par Kim, mais j'ai pas compris , en cas de non "occurence", pourquoi la fonction retourne -1 et non 0 ?

Merci par avance messieurs pour vos réponses et pour les formules / fonctions que vous avez proposées.

bonne soirée
zebanx

job75 · 6 Septembre 2017

Bonsoir zebanx,

zebanx à dit:
peut-on rendre l'array "A,B,C,D" non limité stp ?

On peut remplacer l'array par une plage de cellules, mais comme les autres plages elle doit être limitée.

Bonne nuit.

bcharef · 7 Septembre 2017

Bonjour à toutes et à tous,
Bonjour Moonshine33, pierrejean, job75,Ce lien n'existe plus, hbenalia et zebanx.

Voici un essai avec un Tableau Croisé Dynamique.

Salutations
.

job75 · 7 Septembre 2017

Bonjour à tous,

Juste pour saluer bcharef, très heureux de te revoir cher ami

A+

job75 · 7 Septembre 2017

Re,

Je reviens pour le problème de l'ami KIM, que je salue.

Si vous avez compris mes formules des posts #3 et 4 il n'est guère difficile d'adapter, en G4 :

Code:

=NB(EQUIV({"A"."B"."C"."D"};REPT($C$8:$C$39;$A$8:$A$39&$B$8:$B$39=$F4&G$3);0))

C'est bien sûr ici aussi une formule matricielle à valider par Ctrl+Maj+Entrée.

Fichier joint.

A+

job75 · 7 Septembre 2017

Re,

Si l'on tient au VBA une solution consiste à entrer la formule précédente dans G4:J6 :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With [A7].CurrentRegion
  .Columns(1).Name = "DP"
  .Columns(2).Name = "Type"
  .Columns(3).Name = "NOM"
End With
With [G4:J6]
  .Cells(1).FormulaArray = "=COUNT(MATCH({""A"",""B"",""C"",""D""},REPT(NOM,DP&Type=$F4&G$3),0))"
  .Cells(1).AutoFill .Rows(1)
  .Rows(1).AutoFill .Rows
  .Value = .Value
End With
Application.EnableEvents = True
End Sub

Fichier .xlsm joint.

A+

job75 · 7 Septembre 2017

Re,

Des solutions plus élaborées avec une feuille "Produits" et des tableaux Excel :

Code:

=NB(EQUIV(Tableau1[Produit];REPT(Tableau2[NOM];Tableau2[DP]&Tableau2[Type]=$F4&G$3);0))

Code:

Private Sub Worksheet_Activate()
Worksheet_Change [A1]
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With [G4:J6]
  .Cells(1).FormulaArray = "=COUNT(MATCH(Tableau1[Produit],REPT(Tableau2[NOM],Tableau2[DP]&Tableau2[Type]=$F4&G$3),0))"
  .Cells(1).AutoFill .Rows(1)
  .Rows(1).AutoFill .Rows
  .Value = .Value
End With
Application.EnableEvents = True
End Sub

Fichiers (2).

A+

job75 · 7 Septembre 2017

Re,

Dans la foulée retour sur le fichier de Moonshine33 avec 2 tableaux Excel :

Code:

=NB(EQUIV(Tableau2[Produit];REPT(Tableau1[NOM];(MOIS(Tableau1[Date])=MOIS(G14))*(ANNEE(Tableau1[Date])=ANNEE(G14)));0))

Fichier (3).

A+

Compter le nombre de cellules sans les doublons

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Accro

Pièces jointes

XLDnaute Occasionnel

Pièces jointes

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

Discussions similaires