Discussion :

[a.faurie]

Bonjour,
Je construit actuellement des modèles de DPGF (Décomposition Globale et forfaitaire) pour mon entreprise.

je suis arrivé comme un grand a mettre en forme automatiquement les cellules via les mises en formes conditionnelles en fonction des numéro de chapitre 1; 1,1; 1,1,1
mais j'aimerais que les calculs des sous-totaux se fassent seules.

je pense qu'il est possible de faire cela par une formule du genre :

Code :

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

=SI(A7="";D7*E7;SOMME.SI(A7:A150;"";F8:F150)

le problème c'est que le 150 peux être variable c'est la dernière ligne du chapitre. je me suis servi du nombre de virgule contenu dans la colonne A pour faire la mise en forme, peut-être est-il possible de réutiliser cette info pour le sous-totale?

merci pour vos réponses

[jfontaine]

Bonjour

Et pourquoi pas comme cela

Code :

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

=SI(A7="";D7*E7;SOMME.SI(A:A;"";F:F))

[a.faurie]

Bonjour,
non ça marche pas car il fait la somme de toutes les ligne ayant pour repère """ de toute la feuille.
ce que je veux c'est la somme de toutes les lignes ayant pour repère """ mais que jusqu'au repère de même niveau suivant.

[jfontaine]

Pourtant cela sort le meme résultat que ta formule

Code :

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

=SI(A7="";D7*E7;SOMME.SI(A7:A150;"";F7:F150)

[a.faurie]

Bonjour,
oui je suis d'accord cela sort le même résultat. mais il sont faut tous les 2.
le problème c'est qu'il faut que la formule trouve seule la fin de la zone à sommée.
cette zone sera délimité
- en partie haute par la ligne à la quelle la cellule est située (la ligne 20 ne va pas sommer les lignes 19 et moins)
- en partie basse elle sera délimité par un nouveau chapitre du même ordre (le chapitre 1 se fini au chapitre 2) ou par un niveau superieur (le chapitre 1.1 se fini au chapitre 1.2 ou au chapitre 2)

ce que je n'arrive pas a faire c'est a récupérer l'adresse du chapitre suivant.

[jfontaine]

La recherche des adresses pour borner un somme est très compliqué en formule (du moins pour mois).

Pour ma part je gère cela soit en VBA soit en "trichant".
Tu ajoutes une colonne A que tu masqueras après.
Dans cette colonne tu reportes pour chaque ligne à sommer le code REP. (ex, des 1 sur les lignes 8 à 14)
Il suffira ensuite d'utiliser la formule Somme.Si en utilisant la condition de la colonne A
pour avoir la meme somme qu'en G15

Code :

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

=SOMME.SI(A:A;1;G:G)

Et ainsi de suite

[a.faurie]

Après de nombreux essais je suis arrivé a sortir cette formule qui marche :
=SI(A64="";D64*E64;
SI(1+NBCAR($A64)-NBCAR(SUBSTITUE($A64;",";""))=1;SOMME.SI(A64:INDIRECT(CONCATENER("A";SIERREUR(MIN(EQUIV(1;G65:G206;0))+CELLULE("ligne";A64)-1;5500)));"";F64:INDIRECT(CONCATENER("F";SIERREUR(MIN(EQUIV(1;G65:G206;0))+CELLULE("ligne";A64)-1;5500))));
SI(1+NBCAR($A64)-NBCAR(SUBSTITUE($A64;",";""))=2;SOMME.SI(A64:INDIRECT(CONCATENER("A";SIERREUR(MIN(EQUIV(1;G65:G206;0);EQUIV(2;G65:G206;0);EQUIV(1;G65:G206;0))+CELLULE("ligne";A64)-1;5500)));"";F64:INDIRECT(CONCATENER("F";SIERREUR(MIN(EQUIV(1;G65:G206;0);EQUIV(2;G65:G206;0);EQUIV(1;G65:G206;0))+CELLULE("ligne";A64)-1;5500))));
SI(1+NBCAR($A64)-NBCAR(SUBSTITUE($A64;",";""))=3;SOMME.SI(A64:INDIRECT(CONCATENER("A";SIERREUR(MIN(EQUIV(3;G65:G206;0);EQUIV(2;G65:G206;0);EQUIV(1;G65:G206;0))+CELLULE("ligne";A64)-1;5500)));"";F64:INDIRECT(CONCATENER("F";SIERREUR(MIN(EQUIV(3;G65:G206;0);EQUIV(2;G65:G206;0);EQUIV(1;G65:G206;0))+CELLULE("ligne";A64)-1;5500))));0))))

effectivement c'est trés (trés trés...) compliqué en plus j'ai du ajouter une colonne qui me calcule le niveau de titre de chaque ligne.

bref déjà merci pour les réponses ça ma bien aidé !

mais y reste un problème et pas des moindre si je rajoute une ligne il n'y a plus la formule sur la ligne et ça marche plus !
y a t'il un moyen ou un autre de pouvoir lorsque qu'une ligne étirer la cellule du dessus ou du dessous ?

[Lucorah]

Salut,

Tu vas vraiment utiliser cette formule ???
Bonjour la maintenance du fichier.... j'espère que tu es en CDI

Je pense que tu pourrais écrire une petite fonction en VBA qui te ferais ça de manière simple... et compréhensible !
Ou alors utiliser une formule simple, ça coûte pas grand chose de l'ajuster au besoin et c'est dans les compétences de la plupart des gens qui utilisent Excel.


Le mieux est l'ennemi du bien, ta formule en est une bonne illustration

[a.faurie]

Et attent tu as rien vue la formule final c'est :
=SI(DPGF[[#Cette ligne];[REP]]="";DPGF[[#Cette ligne];[QTE]]*DPGF[[#Cette ligne];[Prix Unitaire € HT]];
SI(1+NBCAR(DPGF[[#Cette ligne];[REP]])-NBCAR(SUBSTITUE(DPGF[[#Cette ligne];[REP]];",";""))=1;
SOMME.SI(INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1)):INDIRECT(CONCATENER("A";
MIN(
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";DPGF[#Totaux])-1))
+CELLULE("ligne";DPGF[[#Cette ligne];[REP]])-1);
"";
INDIRECT(CONCATENER("F";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1)):INDIRECT(CONCATENER("F";
MIN(
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1))))
+CELLULE("ligne";DPGF[[#Cette ligne];[REP]])-1)));

SI(1+NBCAR(DPGF[[#Cette ligne];[REP]])-NBCAR(SUBSTITUE(DPGF[[#Cette ligne];[REP]];",";""))=2;
SOMME.SI(INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1)):INDIRECT(CONCATENER("A";
MIN(
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";DPGF[#Totaux])-1);
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";DPGF[#Totaux])-1))
+CELLULE("ligne";DPGF[[#Cette ligne];[REP]])-1);
"";
INDIRECT(CONCATENER("F";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1)):INDIRECT(CONCATENER("F";
MIN(
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1)));
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1))))
+CELLULE("ligne";DPGF[[#Cette ligne];[REP]])-1)));

SI(1+NBCAR(DPGF[[#Cette ligne];[REP]])-NBCAR(SUBSTITUE(DPGF[[#Cette ligne];[REP]];",";""))=3;
SOMME.SI(INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1)):INDIRECT(CONCATENER("A";
MIN(
SIERREUR(EQUIV(3;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";DPGF[#Totaux])-1);
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";DPGF[#Totaux])-1);
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";DPGF[#Totaux])-1))
+CELLULE("ligne";DPGF[[#Cette ligne];[REP]])-1);
"";
INDIRECT(CONCATENER("F";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1)):INDIRECT(CONCATENER("F";
MIN(
SIERREUR(EQUIV(3;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1)));
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1)));
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[[#Cette ligne];[REP]])+1);1):INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("G";CELLULE("ligne";DPGF[#Totaux])+1);1))))
+CELLULE("ligne";DPGF[[#Cette ligne];[REP]])-1)));0))))

je suis d'accord cette formule est bien trop compliqué ! mais je voulais allez au bout de ce problème.
néamoins cette formule est plutot stable.
en tout cas merci pour tes réponse.
A bientot pour de nouvelles formules de fous !