Somme avec plage dynamique (en fonction d'un critère répété)
Bonjour à tous,
je requiers votre aide pour écrire une formule pour calculer automatiquement les différents sous-totaux d'un devis, en bornant ma plage de somme par :
- début = nom du sous-ensemble
- fin = Sous-Total HT -1 case.
La difficulté que je rencontre réside dans la répétition du critère de fin "Sous-Total HT" d'un sous-ensemble à l'autre. Je n'arrive pas à trouver la bonne rédaction pour prendre en compte le Sous-Total HT qui vient après le nom de espace, qui me sert de début de plage, et non pas le 1er Sous-Total de la colonne.
Ma formule (en H37) jusqu'à présent est la suivante :
=SOMME(
INDEX($H:$H;EQUIV(R$14;$A:$A;0);0):
INDEX($H:$H;EQUIV("Sous-Total HT";$G:$G;0)-1;0))
Explications : INDEX(EQUIV) me permet de déterminer dynamiquement la coordonnée en colonne H, de ma référence sur la même ligne en colonne A, soit Espace 1.
De la même manière, le second INDEX(EQUIV) me permet de déterminer la coordonnée en colonne H de "Sous-Total HT", -1 ligne pour arrêter la correctement la fin de ma somme (sinon, réf. circulaire).
J'ai le sentiment qu'il faut conditionner le choix du Sous-Total HT auquel la formule doit s'arrêter en utilisant la condition suivante : coordonnées de Sous-Total HT > Coordonnées de mon critère de réf (= titre du sous-ensemble Espace 1, Espace 2, etc).
J'espère que mes explications sont assez claires, c'est la première fois que je poste sur un forum. Je joins en même temps le fichier exemple.
Merci à tous ceux qui prendront le temps de regarder ma demande.
Aurélien
Bonjour,
Un essai possible.
=SOMME(INDIRECT(ADRESSE(EQUIV(R$14;$A:$A;0);8)&":"&ADRESSE(EQUIV("Sous-Total HT";$G:$G;0)-1;8)))
Oiseau bleu
Bonsoir l'oiseau bleu,
J'ai testé ta formule et le résultat est le même : il s'arrête au premier Sous Total HT en G30 et ne prends pas en compte le suivant.
De ce que j'ai compris dans ta formule :
- Adresse renvoie une coordonnée de cellule en fonction du numéro de ligne et colonne.
- Indirect affiche le contenu de cette coordonée de cellule.
Pour moi, il faut réussir à lui conditionner qu'il ne peut pas prendre le Sous-Total HT avant le nom Espace 2. Ce qui revient aussi à dire, qu'il doit prendre le prochain Sous-Total HT après Espace 2. En rajoutant peut etre une condition du style :
INDEX(H:H;EQUIV("Sous-Total HT" ;G:G;0)) > INDEX(H:H;EQUIV(R14;A:A;0))
?
Sauf que pour l'instant je n'arrive qu'à m'en servir comme test logique et pas comme une condition de calcul...
Bonjour,
Un autre essai.
=SIERREUR(SOMME(DECALER(INDEX($H:$H; EQUIV(R$14; $A:$A; 0)); 1; 0):DECALER(INDEX($H:$H; EQUIV("Sous-Total HT"; $G:$G; 0)-1; 0); 1; 0)); 0)
Oiseau bleu
formule en H30
=SOMME(DECALER(H30;
-1;;
MIN(0;SIERREUR(AGREGAT(14;6;LIGNE($G$16:$G30)/($G$16:$G30="Sous-total HT");2);16)-LIGNE()+1);))explication = on fait de la somme à partir de H30 décaler -1 = H29 et pour un nombre de lignes négatives : la ligne 30 contient "Sous-total", quelle est l'avant-dernière et s'elle n'existe pas ligne 16 (= pour le premier sous-total). Il n'y a pas de sous-total avant la ligne 30, donc c'est 16, 16-30+1 = -13 lignes à partir de H29, H29 incl = H17:H29.
pour la cellule H37 : H37 décaler - 1 ligne = H36, le sous-total précédent = ligne 30, 30-37+1 = -6 lignes = H31:H36
Dans vos commentaires, vous voulez encore 1 ou 2 lignes supplémentaires inutiles en dessous du dernier sous-total, donc ou lieu de +1, vous prenez +2 ou +3.
Bonjour L'oiseau bleu,
Merci pour cette nouvelle tentative. Les fonctions DECALER sont astucieuses pour borner la plage, mais malheureusement le problème reste inchangé : le premier Sous-Total retenu reste celui de l'espace 1, et non celui de l'espace 2, quand je recherche le sous-total de l'espace 2 :(
Bonjour BsALV,
merci pour ta réponse.
Ta formule fonctionne, et je t'en remercie. Elle s'adapte dynamique à la longueur de la plage de somme, quelque soit les lignes que je rajoute ou enlève, mais il faut reparamétrer chaque sous-total, et sur de gros devis, ça peut être assez long. De plus, ce tableur sera utilisé par quelqu'un de débutant, et je trouve la paramétrage de la formule trop compliquée pour elle ; Je cherche une formule générale, qui soit plus rapide à gérer que relire chaque sous-total pour vérifier que la plage de somme est juste.
Je garde ton commentaire avec sa formule enregistrée, et je vais continuer de réfléchir. je te remercie de nouveau.
Rebonjour,
j'en ai également parlé à un ami qui m'a trouvé cette solution qui pour l'instant correspond le plus à mes besoin : simple à comprendre, adaptative et avec un seul paramètre à changer à chaque fois :
en H30 :
=SOMME(
INDEX($H:$H;EQUIV(R$14;$A:$A;0);0):
INDEX($H:$H;LIGNE($H37)-1;0))
Ligne ($H37) se lie toujours avec le sous-total de mon espace en question, et l'adaption suit si je rajoute ou enlève des lignes.
Merci à vous deux pour vos recherches et pour les formules que vous m'avez fait découvrir :)
re,
Je cherche une formule générale, qui soit plus rapide à gérer que relire chaque sous-total pour vérifier que la plage de somme est juste.
Je trouve votre formule trop fragile pour un débutant, surtout la référence R$14 qu'il faut modifier chaque fois.
Ici une autre solution "facile" avec ce "sous-total" contesté
Bonjour Bart,
Merci d'avoir pris le temps de réfléchir à tout ça.
J'avoue ne pas avoir compris à quoi servait la colonne J. De ce que je comprends, ça compte par ligne, combien de fois la réf "Sous-Total HT" est apparue jusqu'à présent.
Pour votre formule en colonne L, je dois également avouer que je ne la comprends pas, mais en effet ça marche :D Je décortique et comprends ca :
- Une somme
- début de la plage de somme = Ligne juste après Sous-Total HT. S'il n'y a pas de réf. "Sous-Total HT) avant (fonction SI Erreur) alors considérer la ligne 17.
- je ne comprends pas la fonction agregat. ni ce qu'elle fait, ni comment elle marche, malgré quelques recherches basiques.
- fin de plage de somme : La cellule actuelle de la formule somme -1.
Effectivement, plus besoin de se soucier du contenu du tableau en annexe à droite, merci beaucoup :D
re,
ces 2 cellules en colonne J contiennent une formule qui n'a pas besoin des chiffres de la colonne auxiliaire L, donc si vous ne voulez pas voir cette colonne, c'est la solution.
Il faut apprendre à utiliser la fonction (dans le ruban) > formules > évaluer formules et puis chaque fois évaluer et on voit comment Excel traite cette formule (fonction énormément intéressant) !!!
LIGNE($G$16:$G37)/($G$16:$G37="sous-total HT") -> cette ligne est une division avec au dénominateur un test pour voir si le contenu d'une cellule est "sous total HT" , un test est Vrai (=1) ou Faux(=0) et diviser par 0 est une erreur (important). Le nominateur est la ligne de la cellule. Réultat de cette ligne est une matrice de 22 éléments (37-16+1) dont 20 sont une erreur (parce que le contenu de ces cellules <> "Sous-Total HT" et seulement les éléments qui correspondent avec les cellules G30 et G37 ne le sont pas. Leur nominateur est 30 et 37, leur dénominateur est 1, donc vous avez une matrice avec 20 erreurs + les valeurs 30 et 37
AGREGAT(14;6; -> Maintenant, le 2eme argument est "6" et cela veut dire que la fonction agregat ignore toutes les valeurs d'erreurs, donc notre matrice de 22 éléments était déjà réducé à 2 éléments, cad 30 et 37. Le premier arguement de cet aggregat est ici "14" = equivalent à la fonction "Large" et (dans cet image) 2 lignes plus bas vous voyez ce "2", donc on cherche la 2eme plus grande valeur dans notre matrice (réducé), qui est ici 30.
+1 = on fait +1 à cette valeur 30 = 31 parce qu'on veut la ligne après ce "Sous-Total"
SIERREUR(...... ;17) --> si on ne trouve pas la 2eme plus grande, ce qui est le cas pour la ligne 30, parce qu'il n' y a pas d'autres sous-total avant cette ligne, on doit prende 17, donc J30 commence à la ligne 17
INDEX($H:$H; ...) on prend dans la colonne H la cellule x
=SOMME(INDEX($H:$H;
SIERREUR(
AGREGAT(14;6;
LIGNE($G$16:$G37)/($G$16:$G37="sous-total HT")
;2)
+1;
17))
:DECALER($H37;-1;;;))Bonjour Bart,
Merci vraiment pour cette explication. J'avoue ne pas avoir compris à 100% mais ça éclaircit pas mal de choses.
A noter, pour ceux qui voudraient également utiliser cette formule, il faut accorder, au moins pour la 1ere fois, les coordonnées de cette ligne :
LIGNE($G$16:$G37)/($G$16:$G37="sous-total HT"). Si votre Sous-Total est en H30 par exemple, il faut remplacer H37 par H30,
De même, dans :DECALER($H37;-1;;;)) il faut remplacer H37 par H30, sinon cela crée une référence circulaire.
Bart peux tu me confirmer si c'est juste ce que tu écris ici :
"donc on cherche la 2eme plus grande valeur dans notre matrice (réducé), qui est ici 30."
La 2e plus grande valeur c'est pas plutôt 37 ?
re,
je mes suis trompé, LARGE est en anglais, Grande.Valeur est en français https://support.microsoft.com/fr-fr/office/fonction-grande-valeur-3af0af19-1190-42bb-bb8b-01672ec00a..., donc dans cet exemple grande.valeur avec argument 1 est 37 et avec argument 2 = 30
Si vous consultez l'aide d'agregat (https://support.microsoft.com/fr-fr/office/agregat-agregat-fonction-43b9278e-6aa7-4f17-92b6-e19993fa...) si on utilise "14" c'est aussi "grande.valeur"
Concernant ces formules la colonne est absolute, la ligne relative pour ce 37 (voir $G37 et $H37, sans $ devant le chiffre) donc si vous copier la formule et puis vous la collez, il ne faut pas modifier. Exemple vous copiez G37:H37 (CTRL+C), vous vous mettez dans G45 et vous collez (CTRL+V) et les 37 sont modifié en 45.