[XL-2010] Somme d'éléments selon critères multiples [Résolu]

[mft_ghost]

Bonjour,

Je viens aujourd'hui querir votre aide precieuse sur la problematique Excel suivante. Je travaille sur EXCEL 2010 version anglaise.

Suite a extraction d'une base de donnee (comptabilite de plusieurs societes) via BO dans Excel, je souhaiterais exploiter les donnees obtenues.

J'ai besoin de faire la somme d'elements repartis sur plusieurs lignes (avec pour critere de recherche des numeros de comptes comptables que je souhaiterais additionner), si ces elements repondent a des criteres de colonne "nom de societe" (somme de plusieurs comptes repartis sur plusieurs societes).


La formule suivante me permet de rechercher UN numero de compte dans UNE societe (dans l'exemple le compte 4110000 de la societe 2) :

Code :

=HLOOKUP("CIE_2";'BO EXPORT'!$B$7:$CC$547;MATCH(4110000;'BO EXPORT'!$B$7:$B$547;0);FALSE)

Mais lorsque je veux etendre ma recherche a PLUSIEURS comptes sur PLUSIEURS societes et les additionner, ma formule ne fonctionne plus (dans l'exemple les compte 4110000 et 4861200 des societe 1 et 2) :

Code :

=HLOOKUP(OR("CIE_2";"CIE_1");'BO EXPORT'!$B$7:$CC$547;MATCH(OR(4110000;4861200);'BO EXPORT'!$B$7:$B$547;0);FALSE)

J'ai essaye de combiner ma formule avec un SUMIF, malheureusement sans succes (la formule fonctionne toujours pour UN compte et UNE societe, mais pas lorsque je veux etendre les criteres de recherche) :

Code :

=SUMIF('BO EXPORT'!$B$7:$CC$567;HLOOKUP(OR("CIE_1";"CIE_2");'BO EXPORT'!$B$7:$CC$567;MATCH(OR(4110000;4861200);'BO EXPORT'!$B$7:$B$567;0);FALSE))

Pour eclaircir j'ai ajoute un fichier Excel de test.

Merci pour votre aide, et toute solution que vous pourriez proposer !

Bryan

[Daniel.C]

Bonjour,
Ca passe pour deux comptes et deux compagnies, mais au-delà, la formule s'allonge facheusement. Pour plus de simplicité, j'ai mis la formule sur la feuille BO EXPORT :

Code :

=SUMPRODUCT((($C$7:$E$7=$C$7)+($C$7:$E$7=$D$7))*((B8:B13=$B$10)+($B$8:$B$13=$B$13));C8:E13)

formule matricielle (valider avec Ctrl+Maj+Entrée).

[Pierre Fauconnier]

Salut.

Depuis 2007, Excel met à disposition la fonction SUMIFS(PlageSomme;PlageCritère1;Critère1,[PlageCritère2;Critère2];...) te permettant 64 crières.

Les critères s'expriment de façon identique que pour l'ancienne fonction SUMIF() devenue obsolète. Tu peux donc utiliser les opérateurs de comparaison =, >, >=, <, <=, <>. Tu peux également utiliser les jokers ? et *.

Si le critère n'est pas exprimable de cette façon, alors, tu as effectivement SumProduct()

[mft_ghost]

Bonjour et Merci pour vos propositions.

Apres quelques tests et un bon mal de crane , j'arrive pour le moment aux problematiques suivantes en fonction des 2 pistes suivies :

Pour Daniel (et les autres lecteurs ), avec SUMPRODUCT formula :

La formule fonctionne en effet, merci pour cette proposition

MAIS (et oui snif il en fallait un) elle serait effectivement "facilement utilisable" pour une selection limitee a quelques criteres. Dans mon cas je vais monter dans la pratique sur une selection pouvant aller jusqu'a 25 compagnies sur le critere 1 (voire plus dans le temps) et 15 a 25 comptes sur le critere 2 (la aussi non limitatif...).
Du coup ca devient rapidement "fouilli" et difficile a modifier si un changement devait etre opere dans la formule, d'autant que BO EXPORT n'est que la feuille source. La formule va rajouter le chemin de la feuille 'BO EXPORT'! pour chaque critere

J'ai teste la formule avec 4 "criteres 1" et 4 "criteres 2", ca a fonctionne c'est deja un bon point. Sans vouloir paraitre "difficile", je la retiens vraiment comme solution de secours si une formule plus simple ne peut pas etre mise en place Merci !


Pour Pierre (et les autres lecteurs ), avec SUMIFS formula :

La formule presente des attraits certains tels que sa simplicite, et l'utilisation des jokers. Malheureusement meme apres avoir triture la formule dans tous les sens, je n'ai pas reussi a ameliorer les points cruciaux suivants :

1/ La formule le semble fonctionner que lorsque la "sum_range" est definie sur une colonne (ex C:C). Je n'arrive pas a etendre la range a toute une plage de selection (ex C7:G20) car il me retourne le sempiternel #VALUE!

2/ Je n'arrive pas a faire fonctionner la formule sur un croisement de criteres "criteres 1" Lignes + "critere 2" Colonnes. Elle ne semble accepter que des criteres de colonnes/colonnes OU lignes/lignes. Y a-t-il une formulation particuliere qui permette d'utiliser des criteres sur colonne et ligne ? Ci dessous les exemples :

exemple 1, avec criteres lignes 8 et 7 fonctionne mais ne repond pas a mon cas:

Code :

=SUM(SUMIFS(9:9,8:8,{"4110000","4861200"},7:7,"CIE_1"))

exemple 2, avec criteres colonnes B et A fonctionne mais ne repond pas a mon cas:

Code :

=SUM(SUMIFS(C:C,B:B,{"4110000","4861200"},A:A,"CIE_1"))

exemple 3, qui correspondrait plus a mon cas, avec criteres en colonne B et en ligne 7, mais ne fonctionne plus en mix ligne/colonne:

Code :

=SUM(SUMIFS(C:C,B:B,{"4110000","4861200"},7:7,"CIE_1"))

Pour conclure a ce stade avec SUMIFS:
CI DESSOUS un exemple de la formule qui correspondrait tres bien a mon cas sur la feuille BO EXPORT (et serait vraiment tres pratique a utiliser), mais qui me retourne fort desagreablement le resultat #VALUE!

Code :

=SUM(SUMIFS(C8:G20,B8:B20,{"4110000","4861200"},C7:G7,{"CIE_1","CIE_2"}))

Est-il possible de m'aider avec cette ebauche ? Car cette formule la serait vraiment tip-top pour repondre a pbtique en "relative simplicite" !
Merci encore pour votre aide !

[Pierre Fauconnier]

Pour alléger la formule, je ne vois pas beaucoup d'autres solutions que de passer par une construction intermédiaire.

En effet, SUMIF et SUMIFS travaillent sur des vecteurs et n'aiment pas sommer sur une plage multicolonnes. SOMMEPROD et la somme matricielle n'aiment pas INDEX/EQUIV ou RECHERCHEV.

Dès lors, pour rendre ta formule plus simple, et même plus manipulable en cas de changement des valeurs de critères, je te propose de passer par un petit tableau qui reprend une fois chaque donnée d'une colonne et d'ajouter sur la droite une colonne avec OUI ou NON. Tu fais de même pour les autres critères "nombreux" (par exemple pour les comptes et les sociétés).

Dans le tableau de données, tu ajoutes une colonne formulée avec RECHERCHEV ou INDEX/EQUIV et tu places ton critère sur cette colonne.



Pour changer la sélection des critères, tu modifies les OUI/NON du tableau en G2:H5.

[Pierre Fauconnier]

Cela étant, je ne comprends pas trop bien l'organisation des données.

Idéalement, mais je n'ai pas regardé le fichier, il faudrait devoir sommer sur une seule colonne, quitte à multiplier les lignes du tableau.

On pourrait alors s'orienter vers un tableau dynamique et la puissance d'analyse serait décuplée (qui plus est, sans formules).

[Daniel.C]

Si tu n'es pas maître de l'organisation de tes données, tu peux mettre des "x" au dessus et à gauche de tes données. Un exemple avec des x en ligne 4 pour les sociétés sélectionnées et en colonne A pour les comptes sélectionnés. La formule devient :

=SOMMEPROD((C4:Z4="x")*(A8:A20="x");C8:Z20)

Quel que soit le nombre d'items sélectionnés.

[Pierre Fauconnier]

J'ai regardé le fichier.

Perso, je réorganiserais les données (via un peu de vba, ce sont quelques lignes de code) pour exploiter une vraie table de données, car tu pourrais alors utiliser toute la puissance des outils d'Excel.



[EDIT] Original, l'idée de Daniel... Je préfère ma solution qui offre plus de possibilités, notamment via les TCD, mais au pire, si vraiment on renacle à travailler pro en retraitant les données, cette solution est intéressante... [/EDIT]

[mft_ghost]

Merci pour toutes vos propositions.

L'idee des tcd est effectivement interessante, mais je me retrouverai face a une mise en forme plus complexe qu'un simple extract de BO, et des donnees automatiquement mises a jour.

Ci dessous une formule via SUMPRODUCT qui fonctionne (merci a daddy.longlegs pour son aide) :

Code :

=SUMPRODUCT(C9:G21,ISNUMBER(MATCH(B9:B21,{4110000,4861200},0))*ISNUMBER(MATCH(C7:G7,{"CIE_1","CIE_2" },0)))

J'espere qu'elle pourra eventuellement servir a d'autres personnes !

Merci pour votre aide !

[Pierre Fauconnier]

Perso, je reste alors avec un point d'interrogation par rapport à ceci

MAIS (et oui snif il en fallait un) elle serait effectivement "facilement utilisable" pour une selection limitee a quelques criteres. Dans mon cas je vais monter dans la pratique sur une selection pouvant aller jusqu'a 25 compagnies sur le critere 1 (voire plus dans le temps) et 15 a 25 comptes sur le critere 2 (la aussi non limitatif...).

puisque les critères sont en dur dans la solution adoptée (ce qui est déjà criticable au niveau de la maintenance de la formule, et qui sera ingérable pour les multiples critères dont tu parlais ), et donc, nous serions en droit, Daniel et moi, de considérer avoir perdu notre temps à TE chercher une solution pour "beaucoup de critères", là où finalement tu te contentes de deux...

Enfin, au moins m'aura-t-elle permis d'apprécier la solution de Daniel () qui permet bien plus que la solution que tu retiens finalement... et espérons qu'elle servira à d'autres...

[mft_ghost]

Bonjour Pierre,

Permettez moi de lever le point d'interrogation, et vous rassurer tout de suite sur les points suivant :

- J'ai effectivement besoin de beaucoup de criteres, la formule telle que je viens de la poster est sous sa forme utilisee dans mon fichier "test", et ne montre ici que deux criteres. Je suis entrain de la mettre en place dans mon fichier final, ou j'ai effectivement besoin de rajouter de criteres (ex : {7000000,7010000,7014000,7014800,...} pour le critere 1 et {CIE_1,CIE_2,CIE_n,...} pour le critere 2)
Et ceci duplique sur de nombreux postes de compte de resultat et bilans aux combinaisons differentes.


- sur cette base, voici ce que donnait la formule SUMPRODUCT originellement proposee par Daniel (ici avec seulement 4 "critere 1" et 4 "critere 2") :

Code :

=SUMPRODUCT((($C$8:$H$8="CIE_1")+($C$8:$H$8="CIE_2")+($C$8:$H$8="CIE_3")+($C$8:$H$8="CIE_4"))*((B10:B22=4110000)+($B$10:$B$22=4861200)+($B$10:$B$22=4861400)+($B$10:$B$22=4861600)),C10:H22)

La formule devenait rapidement tres lourde et difficile a actualiser si besoin (ce que Daniel a d'ailleurs souligne tout de suite)


- la formule SUMPRODUCT retravaillee permet d'obtenir le meme resultat que la formule de Daniel, mais simplifie tout simplement sa construction

Le meme exemple avec 2*4 criteres

Code :

=SUMPRODUCT(C9:G21,ISNUMBER(MATCH(B9:B21,{4110000,4861200,4861400,4861600},0))*ISNUMBER(MATCH(C8:G8,{"CIE_1","CIE_2","CIE_3","CIE_4"},0)))

Dans cette mouture de la formule, il suffit de rajouter une virgule avec le critere n+1 a rajouter.



En toute sincerite, je ne pense donc pas que ni toi ni Daniel n'ayez perdu du temps a (me) chercher une solution (n'est ce pas la le but de ce forum d'entraide ?).
Les solutions proposees ont permis de faire avancer la problematique que je rencontrais et pour laquelle vous avez gentillement accepte de m'aider.

Je vous remercie au passant tres sincerement de votre temps et de votre aide, ainsi que les personnes qui auraient pu lire le post sans pour autant trouver de solution.

J'espere avoir leve le doute et tes interrogations

Bryan

[Daniel.C]

Bonjour,

Je pense que tu n'as pas vu ma seconde proposition.

[mft_ghost]

Envoyé par Daniel.C

Si tu n'es pas maître de l'organisation de tes données, tu peux mettre des "x" au dessus et à gauche de tes données. Un exemple avec des x en ligne 4 pour les sociétés sélectionnées et en colonne A pour les comptes sélectionnés. La formule devient :

=SOMMEPROD((C4:Z4="x")*(A8:A20="x");C8:Z20)

Quel que soit le nombre d'items sélectionnés.

Merci Daniel !

Je n'avais effectivement pas vu cette proposition glissee furtivement au milieu des posts !

Cette option me parait tres interressante et pouvoir correspondre au besoin..., j'essaie de la mettre en pratique dans mon "gros" fichier pour comparer avec la derniere que j'utilise car elle pourrait encore simplifier la formule.

Excellente idee celles des croix...


Le seul "hic' que je risque de rencontrer en pratique est que la base de donnees va etre utilisee par plusieurs feuilles de mon fichier, n'ayant pas besoin des memes comptes... Je pourrais p-e faire une colonne de "x" par feuille excel si la formule me le permet

Ca donnerait les formules suivantes pour chaque des feuilles... :

feuil1 : =SOMMEPROD((C4:Z4="x")*(A8:A20="x");C8:Z20)
feuil2 : =SOMMEPROD((C4:Z4="x")*(B8:B20="x");C8:Z20)
feuil3 : =SOMMEPROD((C4:Z4="x")*(c8:c20="x");C8:Z20)



Je creuse cette piste aussi, c'est une tres bonne idee

[Pierre Fauconnier]

Salut Brian,

Ma réaction aura été salutaire puisqu'elle t'aura permis de voir la solution de Daniel

A moins qu'il ait encore une autre bonne idée, je reviendrais avec ma proposition de listes à deux colonnes (une liste pour les comptes, une liste pour les CIE) et du RECHERCHEV sur les différents onglets.

Ansi, tu paramètres une seule fois tes choix dans les listes et tes feuilles sont recalculées automatiquement.

[mft_ghost]

Ok ! Merci pour votre aide en tt cas