Commande de SOUS-TOTAL autonome
Bonjour,
Je travaille actuellement sur un modèle de fichier EXCEL qui me sert à faire des déboursés / devis.
J'ai ajouté une colonne dans mon tableau dans laquelle je voudrais paramétrer des "COMMANDES" qui, si activées, influeront sur les calculs habituels de la ligne.
Pour le moment je coince sur les commandes SOUS-TOTAL et SOUS-DETAIL :
Je cherche une formule EXCEL me permettant d'automatiser la sélection des postes à additionner dans un poste de SOUS-TOTAL sans avoir à renseigner moi-même les cellules qu'elle doit comptabiliser.
En gros, je veux que, quand j'indique "SOUS-TOTAL" dans ma case de commande, le prix affiché soit la somme de tous les prix que j'indique en "SOUS-DETAIL" se trouvant directement en dessous (qu'il y en ait 1 ou 20), et que l'addition s'arrête à la première ligne qui n'est pas un "SOUS-DETAIL".
Un fichier exemple plus parlant.
Bonjour
Un exemple de formule en D6
=SIERREUR(SOMMEPROD(((B7:DECALER(B6;EQUIV(B6;B7:$B$100;0)-1;0))="SOUS-DETAIL")*(D7:DECALER(B6;EQUIV(B6;B7:$B$100;0)-1;2)));SOMMEPROD((B7:$B$100="SOUS-DETAIL")*(D7:$D$100)))Cette formule se copie-colle dans les cellules de la colonne D correspondantes à "SOUS-TOTAL"
Attention aux signes $.
Si tu as 40 000 lignes, tu remplaceras le 100 par 40 000
Cordialement
Merci les amis, vous êtes des cracks.
La solution d'Amadéus est exactement ce que je recherche.
Je vais mettre un certain temps à la décortiquer mais elle colle exactement à ce que je dois faire
La solution de eriic est différente et comporte un truc sympa en plus : les petits + qui permettent de masquer immédiatement les sous-details... Mais je ne maîtrise absolument pas la commande Grouper et je risque de faire des conneries; en plus je ne sais pas si elle s'automatise ou si je dois la paramétrer à chaque fois que je crée un devis.
Bref je vous remercie tous les 2 pour votre aide.
Grace à vous j'avance.
Bon après usage je confirme mes premières impressions.
La formule est bien plus efficace que la commande Plan.
La commande Plan fout le bordel dans mon tableau de manière irréversible (ajoute des lignes, des colonnes, des cases... Et après !!!! pas de commande annuler ou retour en arrière ou CTRL+Z !!!! Aaaaargh)
La formule est plus performante mais je n'arrive pas à la faire fonctionner dans mon tableau, je ne sais pas pourquoi.
Dans le fichier fournit elle marche bien, je l'ai même améliorée en remplaçant les données fixes de la formule DECALER par des références plus souples (j'ai remplacé la coordonnée de déplacement de colonne 2 par une formule de type COLONNE(X)-COLONNE(Y).
Comme ça quand je déplace les colonnes ou quand je copie la formule, le calcul de décalage continue a fonctionner.
Parcontre je ne sai spas pourquoi, les modificatiosn dans le fichier fonctionnent, mais quand j'importe la formule dans mon tableau (avec les colonnes au bon endroit) j'obtiens des valeurs #NA ou #VALEUR
Je vais bien finir par trouver l'erreur mais bon
Bon pour ceux que ça intéresse j'ai fini par trouver une solution alternative à la formule très complexe d'Amadeus (que je n'arrive pas a reporter sur mon tableau et qui comporte une erreur en cas d'absence de SOUS-DETAIL en dessous) et à la commande Plan.
Il s'agit d'une méthode artisanale mais bon, elle me détruit moins de neurones quand j'essaye de la compredre/adapter.
J'ai tout simplement ajouté 2 colonnes de petite taille à mon tableau (masquées à l'état définitif).
La première est un compteur qui part d'en haut et numérote toutes les cases de 0 à 100, de manière invariable (ne souffre pas les copier-coller ou copier-insérer--> A l'aide de la commande DECALER appliquée sur la case elle-même).
La seconde est un second compteur qui affiche :
- devant les postes en SOUS-DETAIL = 1 + le contenu de la case d'en dessous si la référence est aussi un SOUS-DETAIL
- devant les postes en SOUS-TOTAL le contenu de la cellule d'en dessous (dont la valeur finit par être le nombre de lignes de SOUS-DETAIL qui sont empilés dessous)
La formule finale est une SOMME(départ de la cellule de prix sur la ligne : fin sur la même cellule DECALEE en colonne de la valeur affichée trouvée à l'aide de mes 2 petites colonnes)
J'aurai aussi put faire cet exercice à l'aide d'une seule colonne :
Au lieux de me numéroter par rapport à une colonne "compteur" j'aurai put me numéroter sur les coordonnées des LIGNES elles-même... Mais j'ai déjà très mal à la tête...
Je joins un fichier d'explication de ma méthode artisanale (noob ?) pour ceux qui tomberont un jour sur ce post et voudrons la connaitre.
Bisous les amis
Bonjour,
La commande Plan fout le bordel dans mon tableau de manière irréversible (ajoute des lignes, des colonnes, des cases...)
Ajout des lignes, oui, c'est ce que tu veux. Par contre ajout de colonnes ???
Et après !!!! pas de commande annuler ou retour en arrière ou CTRL+Z !!!! Aaaaargh
Il te suffit de sélectionner tes colonnes, d'aller au même endroit et de choisir Supprimer tout.
eric