Dans un tableau Excel (Excel 2000) de plus de 1500 lignes, j’ai une colonne avec des noms (environ 30 noms différents) et des colonnes avec des nombres.

Je voudrais faire pour chaque nom la somme des nombres (par colonne).

Exemple :

A B C D

Durand : 2 : 1 : 4

Martin : 0 : 1 : 1

Martin : 1 : 1 : 3

Durand : 1 : 2 : 1

...........

Résultat (somme par colonne pour chaque nom) :

Durand : 3 : 3 : 5

Martin : 1 : 2 : 4

...........

Je suppose qu’il faut que je fasse une formule pour chaque nom ?

Quelle formule ?

Meci

pas facile de représenter un tableau Excel en tapant des espaces !

A est la colonne des noms

B est la premières colonne des nombres

C est la deuxième colonne des nombres

etc.

Salut le Forum

Pour facilité la tâche des intervenants, merci de joindre un fichier.

Pour joindre un fichier, tu envois ton fichier (sans données confidentielles)

sur http://cjoint.com/ , et tu recopies le lien obtenu à ton prochain message.

Mytå

Bonjour,

En pareil cas, tu mets un classeur modèle sur le forum [l'héberger sur http://cjoint.com/ par exemple et mettre lien dans un post], ce qui permet de te fournir des solutions circonstanciés et facilement utilisables pour toi lorsqu'on te retourne le classeur...

A défaut, je vais supposer que ta liste de noms va de A1 à A1500, les nombres que tu veux additionner pour chaque nom sont dans les colonnes B, C et D.

On va poursuivre sur la même feuille pour l'exemple et je vais supposer que tu as dressé une liste des différents noms (30 ou plus ou moins, peu importe) en colonne I. Tu as donc cette liste en I1 à I30 par exemple. Dans les colonnes J, K et L tu vas donc totaliser les nombres des colonnes respectivement B, C et D pour chaque nom.

En J1, en face de ton premier nom (en I1) tu mets la formule suivante :

=SOMME.SI($A$1:$A$1500;$I1;B$1:B$1500)

Tu tires cette formule sur les colonnes K et L, ce qui la recopie sur la ligne 1 et tu tires ensuite l'ensemble jusqu'à la ligne 30, ce qui la recopie sur toute la plage de calcul.

Attention aux références, là où des $ et là où pas de $ pour que ta formule s'adapte parfaitement et calcule sans erreur pour chaque cas.

Voilà pour la partie la plus simple, qui répond à ta question.

Tu peux évidemment adapter selon la configuration de ton tableau...

Je poursuis sur une question que tu n'as pas posé, mais qui peut avoir son utilité pour une telle liste. Tu veux peut-être savoir rapidement combien de noms différents figurent en A dans ta liste (sans avoir à les compter si tu n'as pas pris soin d'en dresser préalablement la liste).

Non sans saluer Mitå dont je viens d'apercevoir le post au passage, d'autant plus qu'on entre là dans un type de formules qu'il manipule avec brio et qui sont toujours une source d'inspiration

C'est un peu plus compliqué que les sommes conditionnelles mais même sans comprendre complètement la formule, il demeure assez facile de l'appliquer :

{=SOMME(1/NB.SI(A1:A1500;A1:A1500))}

Il s'agit d'une formule matricielle, donc à valider par la combinaison Ctrl+Maj+Entrée, ce qui rajoute les {}.

Tu peux la mettre où cela t'intéresse ; pour suivre mon exemple, je l'avais placé en G1, à titre de vérification (avant de lister les noms en I avec une formule également...)

Dernier volet : établir la liste des différents noms avec une formule : je vais utiliser la colonne H pour calculer un paramètre qui sera utilisé par la formule de listage des noms en colonne I (toujours en suivant mon exemple, à adapter à ton cas précis).

Comme j'ai commencé ligne 1, sans ligne d'en-tête, j'aurais un décalage de ma colonne H que je n'aurais pas eu en commençant ligne 2 (j'aurais alors fait appel à la ligne précédente, mais ceci est adaptable selon ton cas...)

Donc en H, je laisse H1 vide et en H2 je tape la formule suivante :

=NB.SI($A$1:$A$1500;I1)+H1

Elle va afficher 0, tant qu'il n'y a aucun nom dans I1, mais lorsqu'il y aura un nom, elle calculera le nombre d'occurrences de ce noms dans la liste en A et y ajoutera les occurences des noms précédant, soit pour le premier, 0.

Si la formule précédente qui donne le nombre de noms affiche 30 par exemple, je sais que j'aurais 30 noms à lister et je vais tirer ma formule de H2 à H31. A chaque ligne elle donnera le nombre total d'occurrence des noms déjà listés dans la liste A.

Lorsque tous les noms seront listés, le résultat de H31 correspondra au total d'éléments de la liste A (1500 dans notre exemple), ce qui est une vérification.

Venons-en à la formule de listage des noms : il s'agit ici encore d'une formule matricielle, donc à valider par Ctrl+Maj+Entrée :

En I1 je tape donc la formule suivante :

{=INDEX($A$1:$A$1500;PETITE.VALEUR(EQUIV($A$1:$A$1500;$A$1:$A$1500;0);1+H1))}

Je la tire jusqu'en H30 (dans notre exemple) pour obtenir la liste de tous les noms différents.

Pour comprendre le fonctionnement de cette formule :

- La fonction EQUIV, appliquée à chaque élément de la liste A va fournir la ligne de la première occurence de chaque nom dans cette liste. Le nom en A1 va renvoyer 1 mais toutes les occurences de ce nom vont aussi renvoyer 1. Si en A2, il y a un nom différent de A1, cela va renvoyer 2, et toutes les occurences du nom en A2 vont aussi renvoyer 2, ainsi de suite pour toute la liste.

- La fonction PETITE.VALEUR, elle renvoie la valeur du rang qu'on lui indique. On sait que la plus petite sera 1, la première, mais si la suivante est 2, son rang va dépendre du nombre de 1 renvoyés par EQUIV. Son rang correspondra au nombre total de 1 +1. Ainsi de suite pour les valeurs suivantes correspondant aux noms différents successifs.

- Le nom est ensuite renvoyé par INDEX en lui indiquant la ligne au moyen de PETITE.VALEUR, incrémentée par paliers successifs pour chaque nom.

Bon courage pour la suite.

Je suis très impressionné par la précision de vos réponses qui semblent tout à fait correspondre à ce que je demandais.

Il me faut un peu de temps pour les analyser et les essayer

Ne vous formalisez donc pas si je ne réponds pas tout de suite.

(et ne cherchez pas trop à préciser avant que j’essaye !).

Je reviendrai bien sûr dès que j’aurai essayé.

MERCI

ferrand,

J’ai appliqué (et adapté) la formule de ton premier message (=SOMME.SI…….) à mon tableau.

Ca fonctionne parfaitement et c’est exactement ce que je voulais faire !

Tes perfectionnements dans tes messages suivants sont bien sûr très intéressants mais je ne les ai pas utilisés (je les garde sous le coude pour plus tard éventuellement).

Je t’envoie le tableau (lien) par message privé avec quelques explications pour que tu voies l’application.

Je te remercie encore et je note bien sûr comme résolu.

Bien que marqué résolu, je reviens sur le sujet:

Comme expliqué à ferrand par message privé, il y a quelques erreurs qui sont dues à des défauts de saisie des nom dans le tableau principal.

La formule utilisée : =SOMME.SI($B$2:$B$1700;$P53;D$2:D$1700)

exige l’orthographe exacte et le nom complet dans la colonne B (du tableau principal) et la même chose dans le tableau de calcul.

Ma question est la suivante :

serait-il possible de n’entrer dans le tableau de calcul qu’une partie du nom et que le calcul se fasse sur toutes les cellules comportant au minimum cette partie du nom ?

(à ma charge bien entendu de mettre suffisamment de lettre pour qu’il n’y ai pas confusion de noms).

J'ai bien compris que l'idéal serait de corriger les erreurs d'orthographe dans le tableau principal, mais il y a 1500 lignes !!...

merci d’avance

mm91 a écrit :

ferrand,

Tes perfectionnements dans tes messages suivants sont bien sûr très intéressants mais je ne les ai pas utilisés (je les garde sous le coude pour plus tard éventuellement).

Bonjour,

J'ai un peu regardé. Pas de difficulté majeure dans l'application que tu as faite.

Pour la suite cela présentera plus de difficultés : cellules vides (qui provoquent division par zéro dans autres formules), cellules fusionnées et mentions hétérogènes... On y parvient sans trop de mal sur une colonne de noms, pour l'autre les résultats ne seraient pas vraiment utiles... Avec le tableau actuel, l'amélioration que tu peux apporter sans difficulté consiste à utiliser des plages nommées et baculer ta statistique sur une autre feuille.

Pour d'autres améliorations, il faudra repenser un peu la conception du tableau. Sans doute utiliser VBA pour rendre l'utilisation plus confortable, et prévoir de ne pas laisser s'accumuler les formules pour des calculs dont les résultats ne se modifieront plus.

ferrand,

j'ai l'impression que nos deux derniers posts se sont croisés (11h17 et 11h 30).

Je n'ai pas l'impression que tu réponde à ma dernière question:

"serait-il possible de n’entrer dans le tableau de calcul qu’une partie du nom et que le calcul se fasse sur toutes les cellules comportant au minimum cette partie du nom ?"

à moins que je n'ai pas compris ?

Bonsoir,

Je n'ai en effet pas répondu à ta question car pas vue au moment où j'ai commencé mon post.

Tu peux en effet utiliser les caractères "jokers" : "*" et "?".

Exemples :

- tu mets ROUS* et ça prendra tous les noms commençant par ROUS

- tu mets *OUS* et ça prendra tous les noms qui comportent OUS (là ça va en faire plusieurs !)

Le * prend la place de plusieurs caractères ou aucun.

Le ? prend la place d'un seul caractère.

Ceci étant, dans le cas où il n'y a pas trop de noms à rectifier tu peux utiliser le filtre automatique pour les détecter (Données > Filtre automatique ou onglet Données et Filtrer selon ta version d'Excel). Commande à lancer en étant positionné sur une quelconque cellule de la ligne 1 de A à N. Si la différence n'affecte pas l'initiale, les variantes vont être proches dans l'ordre alphabétique et en cliquant sur la petite flèche pour filtrer tu trouveras facilement, il suffit alors de décocher l'ensemble et cocher les noms qui doivent être identiques pour n'afficher que ceux-là et corriger ceux qui diffèrent...

Ça pourrait être plus rapide que de rectifier chaque nom avec des "jokers".

Super !

en fait, comme je ne veux (pour l'instant) ne modifier que le tableau de calcul (et non le tableau principal), j'ai utilisé la méthode des jockers.

en mettant une "*" avant et après chaque nom (du tableau de calcul), ça résoud pratiquement tous les problèmes: on prend en compte les cellules avec plusieurs noms ou plusieurs caractères, ou nom + prénom, etc.

(voir éventuellement le tableau jaune dans le lien que je t'ai donné en MP)

Je retiens bien sûr la deuxième méthode (filtre automatique) pour le jour où je me déciderai à corriger tout le tableau principal.

MERCI

(je renote comme résolu)