Discussion :

[Theo67000]

Bonjour,

Je suis en train de réaliser un fichier excel avec des onglets base de données et un onglet pour l'utilisateur qui piochera dans la base de données ce dont il à besoin.

Dans mon tableau j'ai une première colonne avec des menus déroulants où l'utilisateur choisi l'objet dont il a besoin puis dans les colonnes suivantes avec des fonctions recherche j'affiche des valeurs numériques lié à l'objet sélectionné.

Jusque là rien de compliqué. Mais je veux automatiser une fonction sous total par un raccourci clavier exemple "CTRL + Q" ou autre qui insère une ligne sommant toutes les valeurs situées entre le sous total précédent et celui ajouté.

Et ensuite je veux faire de la même manière une fonction total qui prendra en compte toutes les valeurs (sans prendre les sous totaux).

Je n'ai que des bases en VBA et en macro sur excel c'est pourquoi j'ai besoin d'un peu d'aide...

Merci d'avance pour vos réponses et bonne journée

[78chris]

Bonjour

Juste quelques remarques

Attention aux raccourcis : la quasi totalité des possibilités est utilisée par Excel...

Il faut aussi prévoir les fausses manips et donc la suppression... de ces lignes...

[Theo67000]

Bonjour chris et merci pour votre réponse !

J'ai déjà un fichier dans mon entreprise qui utilise "CTRL + q" pour appeler des fonctions et celà ne semble pas poser de problèmes

Sinon que pensez-vous judicieux de faire pour appeler ces fonctions sans raccourcis clavier?

Merci d'avance, bonne journée !

Théo

[Menhir]

Jusque là rien de compliqué. Mais je veux automatiser une fonction sous total par un raccourci clavier exemple "CTRL + Q" ou autre qui insère une ligne sommant toutes les valeurs situées entre le sous total précédent et celui ajouté.

Et ensuite je veux faire de la même manière une fonction total qui prendra en compte toutes les valeurs (sans prendre les sous totaux).

Pourrais-tu faire une copie d'écran pour montrer comment sont organisées tes données ?

[Theo67000]

Bonjour Menhir,

Voila à quoi va ressembler le tableau en (modulo quelques modifications de formes)



la colonne "désignation" fait appelle à la base de donnée avec un menu déroulant et toute les autres colonnes affiche les nombres correspondant avec des fonctions recherche.

Ce que je voudrais c'est pouvoir insérer des fonctions "sous total" qui font le somme de ces nombres pour pouvoir créer des sous ensembles.

Bonne journée

[catapoulpe]

Bonjour Theo67000

Tu souhaites pouvoir insérer une ligne, sous la ligne "Pompe Double à Débit fixe" avec les valeurs suivantes :
Désignation : "Sous-total" / Nombre d'appareils : 5 / Alarme TA : 5 / Signalisation TS : 5 / Mesure TM : 7 etc...

C'est bien cela ?

Tu pourrais ajouter un item "Sous-total" à ta liste déroulante Désignation et modifier les formules du tableau de manière à "aller chercher la valeur" lorsque la Désignation est celle d'un article, et à effectuer la somme lorsque la Désignation est "Sous-total".

Cela te parle-t-il ?

[Theo67000]

Bonjour catapoulpe,

Je n'y avait pas pensé ! mais ça me semble être une bonne idée.

Avec la fonction sous-total on peut choisir de sommer toutes les valeurs à partir du sous total précédent ?

Merci de votre réponse !

[Theo67000]

J'ai changer la formule dans mes cellules de recherche et j'ai mis ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI($B15=Feuil1!$C$62;SOUS.TOTAL(9;$D$11:INDIRECT("D"&LIGNE()-1));SIERREUR($C15*RECHERCHEV($B15;Feuil1!$C:$I;2;FAUX);""))

Feuil1!$C$62 étant la ligne "sous total" que j'ai rajouté dans ma base de données.

1) Cette formule fonctionne mais si je l'étend à tout mon tableau, tout mes sous totaux retombe à 0 y compris ceux qui marchaient auparavant je ne sais pas pourquoi ...


2) et j'aimerai modifier ma balise de départ du sous total ($D$11) par "cellule en dessous du sous total précédent"


Avez-vous des idées svp ? merci d'avance

[catapoulpe]

Re

Tout d'abord la fonction SOUS.TOTAL ne me semble pas utile dans ton cas. Elle n'a un intérêt que si l'on veut varier le calcul effectué : par exemple tantôt SOMME, tantôt MOYENNE, ou MIN, ou MAX... auquel cas on fait varier le premier argument. Si ton sous-total est toujours une somme, utilise directement la fonction SOMME.

Voici la formule matricielle que j'ai trouvée. A taper en D11 et valider par Ctrl+Shift+Entrée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI($C11<>"Sous-total";S11;SOMME(DECALER(D11;-1;0;1-(MAX(SI($C$10:$C11="Sous-total";LIGNE($C$10:$C11);LIGNE($C$10)))-MAX(SI($C$10:$C10="Sous-total";LIGNE($C$10:$C10);LIGNE($C$10)))))))

Note que :
J'ai écrit directement "Sous-total" à la place de ta référence Feuil1!$C$62
Je n'ai pas reproduit ta formule de recherche. Tu dois la saisir à la place de S11. (Je parle de la formule de recherche "toute seule", qui commence par SIERREUR.)

Tu devrais ensuite pouvoir l'étendre à tout ton tableau.

Sinon j'ai trouvé une solution beaucoup plus simple et non matricielle, mais qui nécessite d'ajouter une colonne à ton tableau.

Edit : Précision. La formule que je t'ai présentée s'étend verticalement et horizontalement, mais ta formule de recherche ne peut s'étendre que verticalement. Si tu veux étendre horizontalement il faudra la modifier, ou reprendre à la main l'argument "no_index_col" dans la première ligne de chaque colonne (puis tu pourras étendre vers le bas).

[Pierre Fauconnier]

Salut

[...]la fonction SOUS.TOTAL ne me semble pas utile dans ton cas. Elle n'a un intérêt que si l'on veut varier le calcul effectué : par exemple tantôt SOMME, tantôt MOYENNE, ou MIN, ou MAX... [...]

Il me semble que la première utilité de SOUS.TOTAL n'est pas de faire varier l'analyse, mais bien

• de ne tenir compte que des lignes affichées, en excluant les lignes masquées par filtre (et éventuellement par masquage manuel);
• de permettre une opération arithmétique en ne tenant compte que des sous-totaux précédents.

Elle aurait donc toute sa pertinence ici, puisqu'elle permettra de réaliser des sous-totaux et une somme finale qui ne reprend que lesdits sous-totaux

EDIT]
Par contre, s'il faut pouvoir insérer les sous-totaux à différents endroits sans saisir les formules manuellement, je ne vois que du VBA pour réaliser l'opération si les valeurs doivent être saisies à la main. Si les valeurs sont récupérées via index/equiv ou recherchev, on pourrait tenter une matricielle.


Il serait intéressant que Theo précise bien sa demande.

[catapoulpe]

Il me semble que la première utilité de SOUS.TOTAL n'est pas de faire varier l'analyse, mais bien

• de ne tenir compte que des lignes affichées, en excluant les lignes masquées par filtre (et éventuellement par masquage manuel);

Aaah d'accord. Je n'ai pas pris le temps de bien m'informer sur cette fonction avant de répondre. Merci pour cette précision et la patience avec laquelle tu rectifies sujet après sujet les erreurs des personnes qui comme moi débutent et s'exercent...

• de permettre une opération arithmétique en ne tenant compte que des sous-totaux précédents.

Elle aurait donc toute sa pertinence ici, puisqu'elle permettra de réaliser des sous-totaux et une somme finale qui ne reprend que lesdits sous-totaux

Effectivement. Pourrait-elle être utilisée dans la formule que j'ai proposée, à la place de la fonction SOMME? (Faut que je teste)

Par contre, s'il faut pouvoir insérer les sous-totaux à différents endroits sans saisir les formules manuellement, je ne vois que du VBA pour réaliser l'opération. Il serait intéressant de bien préciser ta demande.

J'ai bricolé ci-dessus une solution sans VBA. En conditionnant le Sous-total à une valeur de la colonne "Désignation". Et en utilisant une formule matricielle pour renseigner la fonction DECALER qui détermine la plage à additionner.

Mais je débute complètement avec les fonctions matricielle. Mes proposition ne sont donc pas immunisées contre les énormités :-)

[Theo67000]

Bonjour,

J'ai essayer pendant une bonne heure mais je n'arrive pas à faire marcher la formule j'ai toujours le résultat "#valeur!", je ne vois pas d'où vient l'erreur.

La difficulté de cette formule est dans la borne supérieure de la somme qui doit être la case inférieure au sous total précédent.

C'est ce que vous avez voulu faire avec la fonction "DECALER" mais en regardant la définition de la formule j'ai vu que le troisième critère de la formule doit être un nombre positif, or dans votre formule il est écrit (1-MAX....) est ce que cette valeur négative fait échouer la formule ?

Je ne vois pas comment exprimer la borne "sous total précédent -1 ligne", si vous avez des idées ....

MErci en tout cas catapoulpe de m'aider

[Pierre Fauconnier]

Salut.

Voici un exemple qui fonctionne avec une petite matricielle...

Pour l'exemple, j'ai placé les tableaux qui alimentent les listes et Index/Equiv sur la même feuille que celle du résultat. Il faut adapter à ton cas.

Le principe est le suivant:
On teste que l'on est sur une ligne de total ou pas.
Si pas, un simple index/equiv permet de trouver les bonnes valeurs.
Si oui, on détermine si il y a déjà une ligne de sous-total avant celle que l'on traite.
Si non, on calcule la somme des valeurs depuis le début du tableau jusqu'à la ligne précédant celle que l'on traite;
Si oui, on repère la ligne du dernier sous-total et avec DECALER, on détermine la plage dont on doit calculer la somme




Le total final s'obtient quant à lui par une simple matricielle (en considérant un nombre de lignes fixes pour la saisie des lignes et sous-totaux.

[catapoulpe]

Pour moi, la fonction DECALER fonctionne avec des valeurs négatives pour les arguments "ligne", "colonne" ou "hauteur" :



As-tu bien validé la fonction avec ctrl+shift+entrée avant de l'étendre vers le bas ?

En attendant, voici la solution non matricielle :
Il s'agit d'ajouter une colonne (je l'ai mise à gauche de la désignation, en B11) avec la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(DECALER($C11;-1;0)="Sous-total";1;DECALER(B11;-1;0)+1)

ou plus simple (mais peut-être moins robuste si tu déplaces des lignes) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI($C10="Sous-total";1;B10+1)

Tu obtiens dans la colonne B une numérotation qui s'incrémente à chaque ligne et qui recommence à 1 après chaque sous-total.

Et ensuite en D11 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI($C11<>"Sous-total";S11;SOMME(DECALER(D11;-($B11)+1;0;$B11-1)))

Pareil, je n'ai pas reproduit ta fonction de recherche, il faut la saisir à la place de S11
Ma formule peut s'étendre verticalement et horizontalement

Sinon j'avais bien compris la question du début de la plage après le sous-total précédent. Je n'ai trouvé qu'une formule matricielle pour répondre à ce besoin en une seule formule (càd sans colonne intermédiaire et "étendable" au moins vers le bas)

Enfin, après les remarques de Pierre Fauconnier je suis en train d'essayer de trouver mieux, et de faire non seulement le sous-total, mais aussi le total.

[Pierre Fauconnier]

Je pense que RECHERCHEV passe mal en matricielle, c'est pourquoi je préfère INDEX/EQUIV... ( mon billet de blog sur les avantages INDEX/EQUIV par rapport à RECHERCHEV)

[Theo67000]

Pierre,

J'ai réussi à faire fonctionner votre formule avec une RECHERCHEV àa fonctionne pour le premier sous total mais pas le deuxième,

Je vous joint mon fichier si vous pouvez jeter un oeil rapidement ...

Je ne comprend pas pourquoi mon deuxième sous total ne fonctionne pas...

Merci en tout cas pour votre aide énorme !


Ps: pour l'instant je n'ai reporté la formule que sur la première colonne et entre les deux sous-totaux

[Pierre Fauconnier]

Dans le DECALER, il faut comprendre que moi, je suis parti d'un tableau qui commençait en ligne 2 pour les données, alors que toi tu commences en ligne 11.

Dès lors, ta formule devrait tenir compte de ce décalage car ici, tu commences à la ligne 25 pour ton second sous-total, soit après ce sous-total.

Donc, en D11, tu dois utiliser

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(B11="Sous-total";SI((MAX(LIGNE(B$10:B10)*(B$10:B10="Sous-total"))-1)>0;SOMME(DECALER($D$10;MAX(LIGNE(B$10:B10)*(B$10:B10="Sous-total"))-9;0;MAX(LIGNE(B10))-MAX(LIGNE(B$10:B10)*(B$10:B10="Sous-total"))));SOMME(D$10:D10));SIERREUR($C11*RECHERCHEV($B11;Feuil1!$C:$I;2;FAUX);""))

Pour comprendre comment la formule arrive au résultat, tu peux utiliser l'audit de formule. il montre pas à pas comment Excel arrive au résultat affiché. C'est une aide très pertinente pour voir ce qui cloche dans une formule...

[catapoulpe]

Voici la formule matricielle que j'ai trouvée. A taper en D11 et valider par Ctrl+Shift+Entrée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI($C11<>"Sous-total";S11;SOMME(DECALER(D11;-1;0;1-(MAX(SI($C$10:$C11="Sous-total";LIGNE($C$10:$C11);LIGNE($C$10)))-MAX(SI($C$10:$C10="Sous-total";LIGNE($C$10:$C10);LIGNE($C$10)))))))

Note que :
J'ai écrit directement "Sous-total" à la place de ta référence Feuil1!$C$62
Je n'ai pas reproduit ta formule de recherche. Tu dois la saisir à la place de S11. (Je parle de la formule de recherche "toute seule", qui commence par SIERREUR.)

Donc

La même formule que ci-dessus, en intégrant ta fonction RECHERCHEV, j'ai juste changé l'emplacement de la plage de recherche :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(DROITE($C11;5)<>"total";SIERREUR(RECHERCHEV($C11;$I$10:$L$15;2;FAUX);"");SOMME(DECALER(D11;-1;0;1-(MAX(SI($C$10:$C11="Sous-total";LIGNE($C$10:$C11);LIGNE($C$10)))-MAX(SI($C$10:$C10="Sous-total";LIGNE($C$10:$C10);LIGNE($C$10)))))))

Cette formule peut s'étendre vers le bas. Pour la reporter dans les autres colonnes, il faut d'abord changer l'argument "index col" de la fonction RECHERCHEV, avant de pouvoir étendre vers le bas. Cette formule ne calcule que les sous-totaux, aux lignes ou "Sous-total" apparaît dans la colonne "Désignation". Pour le total, j'ai saisi une formule à part : une SOMME.SI.ENS (non matricielle) car cette fonction n'est pas gênée par les lignes vides.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI.ENS(D11:D25;$C11:$C25;"Sous-total")

Sinon

Toujours la même, mais cette fois en ajoutant la possibilité de calculer le total si "Total" apparaît dans la colonne "Désignation"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(DROITE($C11;5)<>"total";SIERREUR(RECHERCHEV($C11;$I$10:$L$15;2;FAUX);"");SI($C11="total";SOMME.SI.ENS(D$10:D11;$C$10:$C11;"Sous-total");SOMME(DECALER(D11;-1;0;1-(MAX(SI($C$10:$C11="Sous-total";LIGNE($C$10:$C11);LIGNE($C$10)))-MAX(SI($C$10:$C10="Sous-total";LIGNE($C$10:$C10);LIGNE($C$10))))))))

Cette fois-ci, pas besoin de saisir une formule différente pour la ligne total. Comme celle ci-dessus, ma formule s'étend vers le bas et vers la droite, à la fonction RECHERCHE près.

Restrictions :
Avec les conditions que j'ai mises, les items de la liste déroulante ne doivent pas se terminer par la chaîne de caractères "total" (sauf Total et Sous-total, bien sûr).
Il s'agit d'insérer un seul total dans le tableau.

[Pierre Fauconnier]

catapoulpe...

Toi, tu pars de la ligne de sous-total et tu remontes, moi je pars de la première ligne et je cherche celle du sous-total précédent.

Comme quoi, plusieurs techniques pour un même résultat (et loin des "énormités" dont tu parlais précédemment... ). En plus, ta solution illustre le DECALER remontant avec des valeurs négatives, chose peu connue par les utilisateurs de cette formule...

[Theo67000]

Merci beaucoup à vous !!!!!!!!!!!!

Ca fonctionne j'ai pu saisir les formules et les adapter à toutes les colonnes de mon tableau !

Merci 1000 fois pour le temps que vous m'avez consacré, pour mon premier post sur ce forum je suis ravi !

Bonne fin de journée à vous et à bientôt sur ce forum !