Calculer un nombre de cellules différentes d'une colonne

[Nicolas06]

Bonjour à tous,

Je cherche à calculer le nombre de sites différents présents dans la colonne C de l'onglet Ferroviaire de mon fichier ci-joint, mais je n'y arrive pas.
Pourtant, j'ai bien réussi la même manipulation pour calculer le nombre de société différentes de la colonne B, en utilisant un gestionnaire de nom + une formule (voir fichier joint)

Quelqu'un peut il m'aider ???

Merci d'avance,
Nicolas

 

[R@chid]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonsoir,
pour les Societes :

=SOMME(SI(Sociétés_Ferroviaire<>"";1/NB.SI(Sociétés_Ferroviaire;Sociétés_Ferroviaire)))

elle renvoie 15

pour les Sites :

=SOMME(SI(Sites_Ferroviaire<>"";1/NB.SI(Sites_Ferroviaire;Sites_Ferroviaire)))

elle renvoie 48

Formules Matricielles @ valider par Ctrl+Maj+Entree

J'ai pas compris pourquoi le -2

Amicalement

 

[Compte Supprimé 979]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonsoir

La différence c'est que dans un cas tu as des cellules vides et pas dans l'autre

Utilises cette formule

=SOMME(SI(FREQUENCE(SI(NBCAR(Sites_Ferroviaire)>0;EQUIV(Sites_Ferroviaire;Sites_Ferroviaire;0);""); SI(NBCAR(Sites_Ferroviaire)>0;EQUIV(Sites_Ferroviaire;Sites_Ferroviaire;0);""))>0;1))

En la validant par CTRL+MAJ+ENTREE

Edit : oups, salut Rachid 😉
Comme je dis toujours pourquoi faire simple quand je peux faire compliquer 😛😱

A+

 

[R@chid]

Re : Calculer un nombre de cellules différentes d'une colonne

Salut Bruno,
Pour sa premiere formule, je voie qu'on n'a pas besoin de FREQUENCE,
donc on peut la simplifier comme ca :

=SOMMEPROD(N(EQUIV(Sociétés_Ferroviaire;Sociétés_Ferroviaire;0)=LIGNE(INDIRECT("1:"&LIGNES(Sociétés_Ferroviaire)))))

@ valider par simple Entree

Pour les Sites et du fait qu'on des cellules vides :

=SOMME(SI(Sites_Ferroviaire<>"";N(EQUIV(Sites_Ferroviaire;Sites_Ferroviaire;0)=LIGNE(INDIRECT("1:"&LIGNES(Sites_Ferroviaire))))))

@ valider par Ctrl+Maj+Entree

Amicalement

 

[Nicolas06]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonsoir Rachid et Bruno,

Tout d'abord merci de vous être occupés de mon cas à cette heure tardive !
Cependant, je ne parviens pas à faire marcher vos formules, seule 1 fonctionne sur les 5 proposées (cf fichier joint)
Pourriez vous y jeter un oeil svp ? (peut-être une mise en forme non respectée??)

Merci beaucoup à vous🙂
Nicolas

 

[R@chid]

Re : Calculer un nombre de cellules différentes d'une colonne

Rebonsoir,
Je repete encore une fois, pourquoi le -1????
Les formules Sont des matricielles il faut les valider par Ctrl+Maj+Entree
Voir PJ
Amicalement

 

[Nicolas06]

Re : Calculer un nombre de cellules différentes d'une colonne

@rachid

ps : pour la formule qui calcule le nb de sociétés différentes en colonne B, je retranche -2 à la fin de celle-ci car je veux qu'une des sociétés n'apparaisse pas dans les résultats, et je retranchais encore -1 car il y avait en fait une faute d'orthographe sur une société qui fait que la formule la comptait 2 fois (faute corrigée dans la dernière version du fichier, où je ne retranche donc que -1)

A+

 

[R@chid]

Re : Calculer un nombre de cellules différentes d'une colonne

Re,
Ok ajoutes le -1 et valides les formules qui ne sont pas avec sommeprod par Ctrl+Maj+Entree.
Amicalement

 

[Nicolas06]

Re : Calculer un nombre de cellules différentes d'une colonne

Re,

ok rachid, je viens de comprendre pour les formules, je pensais qu'en validant par Ctrl+Maj+Entree à n'importe quel endroit sur la feuille, les résultats se mettaient à jour, mais il faut en fait être sur la formule pour qu'elle calcule le résultat

Merci encore pour votre aide, cette astuce m'aidera dans le futur
Longue vie à ce forum!!!!

A+

 

[Misange]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonjour

une solution avec un tcd
il faut ajouter une colonne supplémentaire au tableau (qui peut être masquée)
contenant la formule
=SI(NB.SI(B$2:B2;B2)>1;0;1)
tirée vers le bas.
il suffit alors de mettre l'étiquette somme pour cette nouvelle colonne dans le tcd et le tour est joué.
Idem pour compter le nombre de sociétés, le nombre de sites
et pour compter le nombre de combinaisons uniques entre les sociétés et les sites
=SI(SOMMEPROD((C$2:C2=C2)*(B$2:B2=B2))>1;0;1)

Si tu veux supprimer des sociétés de ton calcul, tu fais l'opération de décompte ensuite (ou alors tu n'intègres pas ces sociétés dans ton tableau)

Au fait, sachant que tu utilises 2010, tu n'as pas besoin d'utiliser la fonction decaler pour définir tes noms de façon dynamique. Tu transformes ta table de données initiale en tableau (onglet accueil/style/mettre sous forme de tableau). Ca crée automatiquement des noms dynamiques pour chaque colonne. Pour sélectionner toutes les données d'une colonne, il te suffit alors de cliquer en haut de la colonne.
Essayer cette fonctionnalité c'est ne plus pouvoir s'en passer tellement ça gagne de temps et ça évite des erreurs.
Ce lien n'existe plus

 

[Nicolas06]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonjour tout le monde,

Rachid :
Pour info, ta première formule matricielle pour calculer le nb de sociétés différentes :
=SOMME(SI(Sociétés_Ferroviaire<>"";1/NB.SI(Sociétés_Ferroviaire;Sociétés_Ferroviaire)))

marche mieux que la seconde, car elle fonctionne toujours même après avoir effacer une cellule en colonne B, après avoir ajouter une ligne supplémentaire, ou bien quand la colonne B comprend des cellules vides. Elle est donc très efficace dans mon cas de figure.

Cependant, il y aurait une amélioration à apporter à la formule calculant le nb de sites différents, car après vérification la formule ne retient pas les sites du même nom appartenant à des sociétés différentes (par ex dans mon cas, les sociétés Alstom Transport et GHH Valdunes ont toutes 2 un site à Valenciennes, mais la formule n'en retient qu'un au lieu de 2, le résultat devrait donc être de 49 au lieu de 48)

Misange: Merci pour ta proposition, mais les résultats obtenus ne sont pas les résultats attendus (tu trouves 14, 93, 56 et il faudrait trouver 14 et 49), et pour des raisons pratiques je ne souhaite pas passer par des colonnes intermédiaires, je préfère une formule directe

A+

 

[R@chid]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonjour,
Salut Misange

marche mieux que la seconde, car elle fonctionne toujours même après avoir effacer une cellule

J'ai pas de conditions pour les cellules vides, j'ai juste simplifie la tienne pour te montrer qu'on peut le faire sans FREQUENCE().

Pour 49 a la place de 48 j'ai pas compris, je compte les noms differents..

Voir PJ
AMicalement

 

[Misange]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonjour tout le monde,

Misange: Merci pour ta proposition, mais les résultats obtenus ne sont pas les résultats attendus (tu trouves 14, 93, 56 et il faudrait trouver 14 et 49), et pour des raisons pratiques je ne souhaite pas passer par des colonnes intermédiaires, je préfère une formule directe

A+

Je n'avais pas tenu compte des cellules vides.
Modifie la formule par
=SI(OU([@SITE]="";NB.SI(C$2:C2;C2)>1);0;1)

Je comprends que tu préfères une formule directe. Je te mets juste en garde sur une chose : si ton tableau est appellé à devenir très gros, il est ++++ efficace de passer par un TCD, sachant que les matricielles sur des grandes plages entraînent des temps de calcul vraiment très très longs. Mais c'est toi qui connait l'usage de ton outil.

 

[Nicolas06]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonjour Rachid,

Pour 49 à la place de 48, je m'explique : Mon fichier est fait d'un listing de sociétés présentes sur plusieurs sites en France. Parfois, il peut donc arriver que diverses sociétés aient une structure dans la même ville, celles-ci sont donc bien distinctes puisque appartenant à 2 sociétés différentes, bien qu'elles aient le même nom.
Dans mon cas, c'est par exemple le cas d'Alstom Transport et GHH Valdunes, qui ont tout 2 un site sur Valenciennes (lignes 64 et 208 du fichier joint).
Hors, ces 2 sites sont différents, et je voudrai donc que la formule le prenne en compte, pour avoir une visibilité totale du nombre de site différents. Dans cet exemple précis, le résultat devrait donc être de 49 au lieu de 48.

Si tu as une idée de formule qui puisse répondre à cela je suis preneur🙂

Merci beaucoup,
Nicolas

ps : j'ai vu que tu as modifié la formule en D2 pour qu'elle fonctionne aussi bien que celle en C2, du coup, laquelle me conseilles tu d'utiliser ? Est-ce aussi bien l'une que l'autre ?

 

[Nicolas06]

Re : Calculer un nombre de cellules différentes d'une colonne

Bonjour Misange,

Merci pour cette dernière formule, elle convient tout à fait à la situation.
Il est vrai que les formules matricielles prennent des fois un peu de temps avant d'afficher un résultat
Dans mon cas, cela fonctionne pour l'instant très bien, mais je garde ta proposition de TCD si dans un avenir mes fichiers viendraient à grossir au point de ne plus pouvoir utiliser les formules matricielles.

Par contre, je ferai la même remarque qu'à Rachid (cf post précédent) : Pour le nb de sites différents, il faudrait que le résultat soit de 49 et non de 48, en prenant en compte le fait que plusieurs sociétés puissent avoir un site du même nom, mais qui reste différents à mes yeux (cf post précédent site Valenciennes Alstom Transport et GHH Valdunes).

Est-ce possible avec le TCD ?

Autre question, comment as tu fait pour créer : [@SOCIETE] et [@SITE] ?

Merci beaucoup
A+