[Excel hardcore] Somme double conditionelle

Ataraxie · 28/09/2011, 16h37

Bonjour

J'ai un soucis que je n'arrive pas a solder, je cherche a faire une somme avec double condition.

J'ai une feuille dans laquelle je fait un extract de base de donnée.
Je souhaite dans une seconde feuille faire la somme d'une colonne, mais trié selon 2 paramètres:
- Un test sur la ligne, pour savoir vérifier la valeur d'une cellule dans la colonne F, qui doit etre égale a la valeur d'une cellule
- Un test sur le mois de la date stoquée colonne F (car je compte faire un tableau récapitulatif mois par mois.)

Donc, ma formule donne un truc du genre (excel en anglais)

Code:

 
=SUMIFS('import DB'!D:D;'import DB'!F:F;$D$3; 'import DB'!F:F;MONTH('import DB'!F:F)=B4)

En B4 j'ai un 1 Qui signifie mois de janvier pour la fonction month.

J'imagine que la technique est pas orthodoxe/fonctionelle, mais comment faire ca en un min d'opération?
(en évitant les tables pivots/extracts en tout genre)

Comme c'est pas clair, je joint un fichier d'exemple

Nota: le fichier est un xlsx renomé en xls (garantit sans macro)
Et toute les données ont été bidonnées

Cyberia · 28/09/2011, 17h58

Je ne peux pas ouvrir ton fichier (vieux excel au boulot), mais pour les multi-conditionnelles j'utilise la fonction sommeprod, et en particulier pour travailler sur des extraction de BDD.

Si je prend un tableau à 3 colonnes A, B et C sur 10 lignes, et que je veux sommer la valeur de la colonne C si la valeur de la colonne A =1, et la valeur de la colonne B=3 ça donne à ça :
=sommeprod((A1:A10=1)*(B1:B10=3)*(C1:C10))

Tu peux rajouter autant de condition que tu veux et choisir la valeur d'une cellule pour la condition (remplacer A1:A10=1 par exemple A1:A10=feuille2!F8 par exemple), mettre des références absolues etc, toute la panoplie classique en fait.

cricri · 28/09/2011, 20h20

il ne faut surtous pas renomer un xlsx en xls en pensant que ca marchera sur les ancienne version
par constre si on laisse en xlsx meme un excel 2003 peut l ouvrir dans certaine condition

http://craftac2.free.fr/t.xlsx

clic droit et enregistrer et le renomer en xlsx ne pas l ouvrir par le lien

Eyce Karmina · 28/09/2011, 21h23

le renommage c'est parce que jol n'accepte pas les xlsx en pièce jointe et qu'il ne fait la vérification que sur l'extension je pense.

belgarionsa · 29/09/2011, 11h35

ouais enfin un enregister sous aurait fonctionner aussi

et sinon pour ouvrir du xlsx avec excel 2003 c'est possible hein, suffit de télécharger le pack de compatibilité excel de crosoft ici

Ataraxie · 29/09/2011, 14h19

Citation :

Publié par Eyce Karmina

le renommage c'est parce que jol n'accepte pas les xlsx en pièce jointe et qu'il ne fait la vérification que sur l'extension je pense.

Exact.
Et j'ai pas pu l'enregistrer en xls compatible a cause de je sais quelle formule qui n'est pas rétrocompatible.

Je vais tester le someprod.

Edit, c'est impec, merci beaucoup!
Avec un vrais xls, avec une vraie mise en page qui va surement sauter et des valeurs qui vont p-e devenir fausse

La formule finale:

Code:

=SUMPRODUCT((MONTH('import DB'!E:E)=Table!B4)*('import DB'!F:F=Table!$D$3)*('import DB'!D:D))

Back to Real Life · 29/09/2011, 16h32

Citation :

Publié par Cyberia

Si je prend un tableau à 3 colonnes A, B et C sur 10 lignes, et que je veux sommer la valeur de la colonne C si la valeur de la colonne A =1, et la valeur de la colonne B=3 ça donne à ça :
=sommeprod((A1:A10=1)*(B1:B10=3)*(C1:C10))

Pas mieux, les multi sommes conditionnelles se font avec Sommeprod comme décrit au dessus

Ataraxie · 20/01/2012, 14h52

Le retour de la vengeance...

J'ai un soucis avec sommeprod.

Un des terme est une double conditionnelle.
Je fait donc un
SOMMEPRODUCT((IF(AND(X>=Y;X<=Z);1;0))*(Date=DateX)*ValeurX)

X étant une référence, DateX la date associée a la ligne de la ref, et valeurX le chiffre a additionner.
Y et Z sont 2 bornes

Le truc étrange c'est que ça somme pour toutes les dates a partir du moment ou la double conditionnelle est vérifié au moins une fois.

C'est un problème de la fonction, ou une faute d'écriture?

cricri · 20/01/2012, 19h53

faute d ecriture mais je vois pas ce que tu veux faire

=sommeprod( matrice1;matrice2) je ne vois pas de ; dans ta formule

Ataraxie · 21/01/2012, 19h37

non pas besoin du, vu que je ne fait pas la somme de 2 matrices.

Le but du somme prod ici, c 'est de faire de la somme d'une matrice, si les conditions de la ligne sont respectées. (Ca somme que si les 2 conditions ne renvoient pas un 0).

Pour bypasser le pb rencontré, je vais rajouter une étape intermédiaire. ou pour chaque ligne, je vais faire le test sur la référence.

Back to Real Life · 21/01/2012, 19h41

Ta double conditionnelle, t'as essayé de le faire en 2 conditions simples, mais sur la même zone ? Ca résoudrait peut-être ton problème.

cricri · 21/01/2012, 20h00

s il y a pas de produit donc une somme conditionnelemnt seulement passe par somme.si

Back to Real Life · 21/01/2012, 20h06

Citation :

Publié par cricri

s il y a pas de produit donc une somme conditionnelemnt seulement passe par somme.si

Non l'astuce de passer par sommeprod permet de tester de multiples conditions sur une même ligne au sein d'une matrice. Le seul soucis de cette technique c'est que le poids des feuilles de calcul explose assez facilement.

Poux · 23/01/2012, 15h25

Citation :

Publié par Back to Real Life

Non l'astuce de passer par sommeprod permet de tester de multiples conditions sur une même ligne au sein d'une matrice. Le seul soucis de cette technique c'est que le poids des feuilles de calcul explose assez facilement.

Sauf que vraisemblablement l'OP utilise une version d'excel >= 2007 et devrait donc utiliser la fonction SOMME.SI.ENS s'il ne compte pas utiliser son fichier sur Excel2003 et moins.

Utiliser SOMMEPROD c'est bien mais pas fait pour traiter autant de données, pour ça il existe les fonctions de bases de données en l'occurrence ici BDSOMME. Après si le temps de recalcule de la feuille n'importe pas on peut éventuellement faire un Quick & Dirty SOMMEPROD.

Sur le fichier d’exemple avec SOMMEPROD j'ai un temps de recalcule de ~4sec. Certes je n'ai qu'un Dual Core @1.8GHz et pas un i7 mais la solution n'est pas un changement de PC (

) mais une utilisation appropriée des formules. Le recalcule de l'exemple que j'ai joint avec SOMME.SI.ENS est instantané.

Si on veut vraiment utiliser SOMMEPROD à la place de BDSOMME ou SOMME.SI.ENS on doit au moins donner un nom aux plages qui sont utilisées dans la formule (ça pourra accélérer le calcul mais ça n'en devient pas pour autant une utilisation appropriée)

N.B.: renommer en xlsx

cricri Alpha & Oméga	il ne faut surtous pas renomer un xlsx en xls en pensant que ca marchera sur les ancienne version par constre si on laisse en xlsx meme un excel 2003 peut l ouvrir dans certaine condition http://craftac2.free.fr/t.xlsx clic droit et enregistrer et le renomer en xlsx ne pas l ouvrir par le lien
28/09/2011, 20h20

Eyce Karmina [m3h-k] Alpha & Oméga	le renommage c'est parce que jol n'accepte pas les xlsx en pièce jointe et qu'il ne fait la vérification que sur l'extension je pense.
28/09/2011, 21h23

belgarionsa [CA] Alpha & Oméga	ouais enfin un enregister sous aurait fonctionner aussi et sinon pour ouvrir du xlsx avec excel 2003 c'est possible hein, suffit de télécharger le pack de compatibilité excel de crosoft ici
29/09/2011, 11h35

Back to Real Life Alpha & Oméga	Citation : Publié par Cyberia Si je prend un tableau à 3 colonnes A, B et C sur 10 lignes, et que je veux sommer la valeur de la colonne C si la valeur de la colonne A =1, et la valeur de la colonne B=3 ça donne à ça : =sommeprod((A1:A10=1)(B1:B10=3)(C1:C10)) Pas mieux, les multi sommes conditionnelles se font avec Sommeprod comme décrit au dessus
29/09/2011, 16h32

cricri Alpha & Oméga	faute d ecriture mais je vois pas ce que tu veux faire =sommeprod( matrice1;matrice2) je ne vois pas de ; dans ta formule
20/01/2012, 19h53

Ataraxie Alpha & Oméga	non pas besoin du, vu que je ne fait pas la somme de 2 matrices. Le but du somme prod ici, c 'est de faire de la somme d'une matrice, si les conditions de la ligne sont respectées. (Ca somme que si les 2 conditions ne renvoient pas un 0). Pour bypasser le pb rencontré, je vais rajouter une étape intermédiaire. ou pour chaque ligne, je vais faire le test sur la référence.
21/01/2012, 19h37

Back to Real Life Alpha & Oméga	Ta double conditionnelle, t'as essayé de le faire en 2 conditions simples, mais sur la même zone ? Ca résoudrait peut-être ton problème.
21/01/2012, 19h41

cricri Alpha & Oméga	s il y a pas de produit donc une somme conditionnelemnt seulement passe par somme.si
21/01/2012, 20h00

Back to Real Life Alpha & Oméga	Citation : Publié par cricri s il y a pas de produit donc une somme conditionnelemnt seulement passe par somme.si Non l'astuce de passer par sommeprod permet de tester de multiples conditions sur une même ligne au sein d'une matrice. Le seul soucis de cette technique c'est que le poids des feuilles de calcul explose assez facilement.
21/01/2012, 20h06

[Excel hardcore] Somme double conditionelle

Connectés sur ce fil