Discussion :

[Jimmy92]

Bonjour,
Je souhaiterais faire les sommes des valeurs se trouvant dans les cellules de la colonne 'B' entre les marqueurs 'début' et 'fin', et afficher les résultats en colonne 'C' à côté des marqueurs 'fin', sachant que cette feuille contient au moins 15000 lignes.
Merci à l'avance,
Jmy

[Jean-Philippe André]

Salut,

Deux approches :
- passer par des formules

J'utilise ici les colonnes C, D et E pour l'exemple
Cellule C2

Code :

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

=SI(B2<>"fin",B2,SOMME(INDIRECT(D1&":"&E1,VRAI)))

Cellule D2

Code :

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

=SI(B2="début","B"&LIGNE(B2),D1)

Cellule E2

Code :

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

=Si(B2="début","B"&LIGNE(B2),"B"&LIGNE(B2))

a appliquer sur toutes les lignes
-passer par du code VBA

[unparia]

Bonjour
A noter que s'il laissait une ligne blanche avant chaque mot DEBUT en colonne A, il lui suffirait d'écrire cette formule dans C2 :

Code :

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

=SI(B2<>"";SOMME(C1;B2);"")

et de l'étirer vers le bas autant que nécessaire pour obtenir non seulement le total de chaque groupe là où "FIN", mais également les sous-totaux partiels sur chaque ligne de chaque groupe.

J'ai écrit ici "avant le mot DEBUT de la colonne A" uniquement pour que l'on "visualise" ce que je dit. On pourrait ensuite (ils ne serviraient à rien), effacer ces mots DEBUT et FIN, la seule chose qui compte étant qu'une ligne vierge sépare chaque groupe.

[unparia]

Pour illustrer ce que j'ai écrit --->>

on a là deux formules (l'une en C2 et l'autre en D2) à tirer vers le bas.
Et si l'on ne veux pas voir les sous-totaux des groupes, on masque la colonne C
Rien de plus simple que cela

[Jimmy92]

Merci beaucoup pour vos solutions respectives. Je n'ai pas encore pris le temps de mettre en oeuvre la première (celle de jean-Philippe), mais j'y compte bien...
En ce qui concerne la seconde en substituant les marqueurs de début et de fin par des lignes blanches, je la trouve très simple et permet en prime d'alléger le fichier et d'améliorer la visibilité du résultat. Il reste maintenant à trouver le moyen de faire cette substitution au préalable de ce traitement. Serait-ce trop vous demander?
Encore merci pour votre réactivité à tous deux.
Cordialement, Jmy

[unparia]

Il reste maintenant à trouver le moyen de faire cette substitution au préalable de ce traitement. Serait-ce trop vous demander?

quelle "substitution" ?
Veux-tu dire que ta feuille est déjà remplie d'un très grand nombre de lignes (trop grand pour envisager de le faire manuellement), avec des "DEBUT" et des "FIN" et sans ligne vide au dessus de chaque "DEBUT" et que tu veux maintenant ajouter une ligne vide au-dessus de chaque "DEBUT" puis faire disparaître les "DEBUT" et "FIN" ?
Sois très précis sur ce que tu entends par "substitution".

[Pierre Fauconnier]

Salut.

Tu pourrais essayer la formule suivante en C1 et la tirer vers le bas. Il faut adapter la plage B1:B26 à ta plage réelle... Formule matricielle à valider avec CTRL+SHIFT+ENTER

=SI(B1="fin";SOMME((LIGNE($B$1:$B$26)>MAX(($B$1:$B1="Début")*LIGNE($B$1:$B1)))*(LIGNE($B$1:$B$26)<LIGNE())*SIERREUR(CNUM($B$1:$B$26);0));"")

[Jimmy92]

Réponse à 'unparia:
C'est exactement ça, il faut remplacer les 2 lignes "FIN" et "DEBUT" qui se suivent par un ligne unique vide, avant d'appliquer la formule de calcul des sommes partielles.
Mais étant donné la taille de la feuille (plus de 10000 lignes) il n'est pas envisageable de le faire manuellement.

Réponse à Pierre:
Je vais également tester ta formule mais à laquelle je ne comprends pas grand chose pour l'instant.

Merci à tous,

[unparia]

C'est exactement ça, il faut remplacer les 2 lignes "FIN" et "DEBUT" qui se suivent par un ligne unique vide, avant d'appliquer la formule de calcul des sommes partielles.
Mais étant donné la taille de la feuille (plus de 10000 lignes) il n'est pas envisageable de le faire manuellement.

Je n'ouvre jamais de classeur tiers et ignore donc l'aspect de ta feuille :
Mets-ici une copie d'écran de ce que tu as et une copie d'écran de ce que tu veux obtenir (avec effacement des "DEBUT" et "FIN" et insertion d'une ligne vide et je te fais cela par VBA, d'un seul coup (transformation de ta feuille + mes formules)
Ce sera très vite fait

[unparia]

Voilà déjà en partant du principe que la copie d'écran de Pierre Fauconnier correspond à ta feuille
Exécute ceci sur la feuille en cause :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Dim dl as long, I as long
dl = Range("B" & Rows.Count).End(xlUp).Row
  For i = dl To 2 Step -1
    If Not IsNumeric(Range("B" & i).Value) Then
       If Range("B" & i) = "début" Then
         Range("B" & i).EntireRow.Delete
       Else
         Range("B" & i).Value = ""
       End If
     End If
   Next
   Range("C2:C" & dl).Formula = "=IF(B2<>"""",SUM(C1,B2),"""")"
   Range("D2:D" & dl).Formula = "=IF(C3="""",C2,"""")"
   Columns(3).Hidden = True ' uniquement si tu veux en plus masquer la colonne C des sous-totaux

[Pierre Fauconnier]

Je n'ouvre jamais de classeur tiers et ignore donc l'aspect de ta feuille[...]

Tchôôôôdi... Tu as un don d'extralucidité pour avoir pu, dans ton premier message, représenter quasiment la situation présentée dans le classeur de Jimmy sans l'avoir ouvert... Je te tire mon chapeau

[unparia]

Tu as un don d'extralucidité pour avoir pu, dans ton premier message, représenter quasiment la situation présentée dans le classeur de Jimmy sans l'avoir ouvert... Je te tire mon chapeau

ce n'était pas difficile sur la base de la demande :

Je souhaiterais faire les sommes des valeurs se trouvant dans les cellules de la colonne 'B' entre les marqueurs 'début' et 'fin', et afficher les résultats en colonne 'C' à côté des marqueurs 'fin'

Mais si tu relis ce que j'avais écrit :

A noter que s'il laissait une ligne blanche avant chaque mot DEBUT en colonne A ,

J'ai écrit ici "avant le mot DEBUT de la colonne A" uniquement pour que l'on "visualise" ce que je dit. On pourrait ensuite (ils ne serviraient à rien), effacer ces mots DEBUT et FIN, la seule chose qui compte étant qu'une ligne vierge sépare chaque groupe.

et regarde mon impression d'écran (plus haut)
Tu verrais que je "visualisais" la feuille un peu différemment (je "voyais" les mots début et fin en colonne A, en face du 1er et du dernier élément de chaque groupe et non au-dessus) et non comme il les avait disposés (encadrant ses groupes en colonne B)
Mais finalement : je n'étais pas si loin, non ? et il m'a donc fallu ne changer que très peu (ajouter, du coup, des suppressions de ligne, alors que dans ma "vision" je voyais au contraire des insertions de lignes vides) comme le montre mon autre message :

et que tu veux maintenant ajouter une ligne vide au-dessus de chaque "DEBUT" puis faire disparaître les "DEBUT" et "FIN"

Non : je ne suis pas un visionnaire ***** (juste un lecteur ... )

Je me suis finalement basé sur ta propre capture d'écran (que j'ai supposée correspondre à la feuille du demandeur), qui m'a fait comprendre quelle était la vraie disposition des données. C'est tout.

EDIT : et je suis finalement un lecteur pas si bon que cela, car si j'avais lu plus attentivement encore le tout premier message, j'aurais également vu que les mots début et fin étaient en colonne B et ... aurais alors d'emblée "visualisé" parfaitement sa feuille, y compris le fait que tout commençait en ligne 2 (ce qui ressortait de l'intervention de Jean-Philippe André)
Comme quoi, je ferais mieux de lire plus attentivement encore ...

***** si je l'étais, je mettrais ce don à profit pour deviner mieux le comportement de la louvine (à la pêche) . Mais là, c'est beaucoup plus difficile et non (encore ?) à ma portée.
http://www.rhinfo.com/thematiques/ap...un-visionnaire

[Pierre Fauconnier]

Voici l'explication de ma formule. Au passage, cette formule est unique, ne nécessite pas de masquage de colonnes intermédiaires ni de vba...

D'abord, c'est quoi une formule matricielle?
C'est une formule qui, parce qu'elle est validée par CTRL+SHIFT+ENTER, va "boucler" sur toutes les lignes des vecteurs utilisés, en prenant en compte, à chaque boucle, les cellules qui sont à la même position dans chaque vecteur utilisé par la formule. L'intérêt de la matricielle est qu'elle permet d'éviter formules et colonnes de calcul intermédiaires, et parfois, comme dans le cas présent, d'éviter le vba de préparation comme te le propose unparia.

On va, en plus, s'appuyer sur le fait que les valeurs booléennes VRAI et FAUX sont transtypées en valeurs numériques lors de leur utilisation comme opérandes dans des formules arithmétiques. On notera aussi la base de l'arithmétique booléenne, qui veut que la multiplication des valeurs logiques corresponde au ET logique, l'addition de ces valeurs permettant de simuler le OU logique. On s'appuiera dès lors sur ces particularités, et notamment sur le fait que 1 est neutre pour la multiplication alors que 0 est absobant (1 * a = a et 0 *a = 0), et que 0 est neutre pour l'addition ( 0+ a = a).

On remarquera donc que dans la formule matricielle, on n'utilise pas une seule ligne, mais bien le vecteur colonne complet sur lequel on travaille, puisque la matricielle va travailler en boucle sur chaque ligne des vecteurs. Ici, le seul vecteur utilisée est B1:b26, mais tes recherches sur les matricielles et notamment sur SOMMEPROD te montreront que l'on utilise souvent plusieurs vecteurs colonne. D'autres pseudo matricielles ne te sont peut-être pas inconnues, je citerai pour exemple NB.SI.ENS, SOMME.SI.ENS, RECHERCHEV, INDEX/EQUIV, ...).

Cela étant posé, le traitement par formule que je te propose s'effectue comme suit, en tenant compte de l'illustration en bas de message

1. On teste que l'on est sur une cellule contenant "fin" en colonne B, pour n'effectuer le calcul matriciel que sur ces lignes, car les formules matricielles sont gourmandes, vu qu'elles bouclent sur toutes les lignes de la plage. Si on n'est pas sur une ligne "fin", le SI renvoie une chaine vide, simulant visuellement l'absence de donnée.

2. On va utiliser le numéro de la ligne sur laquelle la formule est calculée. Dans l'illustration qui suit, c'est la colonne F.

3. On va tester que l'on est sur une cellule "début" en B. On notera ici que lorsque l'on souhaite une fonction SI dont les valeurs de retour sont VRAI ou FAUX, il n'est pas nécessaire d'utiliser la fonction. Il suffit d'écrire la condition. Ainsi, en G1, on a la formule =B1="début" qui renvoie FAUX. En ligne 6, =B6="début" qui renvoie VRAI.

4. On peut donc multiplier les résultats en F et G pour obtenir soir 0 (cas où G est FAUX) ou le numéro de la ligne (cas ou G est VRAI => on est sur une ligne Début).

5. Dans la formule matricielle, on a (LIGNE($B$1:$B$26)>MAX(($B$1:$B1="Début"). On remarque ici l'absence du $ sur MAX($B$1:$B1). $B1 va donc devenir, ligne après ligne, $B2, $B3, ... On va donc tester que la ligne sur laquelle on se trouve porte un numéro supérieur au numéro de la ligne "début" inférieure la plus proche. On notera donc que, jusque à la ligne 6, c'est la ligne 2 qui est considérée puisque de H1 à H6, on a soit 0 soit 2. Le MAX de cette plage est donc bien le numéro de ligne "début" inférieure. A partir de la ligne 7 et jusqu'à la ligne 13, c'est 7 le max puisque l'on a soit 0, soit 2, soit 7, et ainsi de suite. Cela est illustré en colonne J, où l'on peut plus ou moins se représenter cette réalité. La réalité matricielle trouve ici les limites de sa représentation ligne par ligne, mais tu peux imaginer que pour le premier bloc, On aurait FAUX en I1:I2, VRAI pour la suite. Pour le second bloc, on aurait FAUX en I1:I7, puis VRAI pour le reste, etc.

6. (LIGNE($B$1:$B$26)<LIGNE()). C'est un test semblable qui va déterminer si l'on se trouve avant la ligne de fin du bloc, pour chaque bloc. Ainsi, si on imagine la colonne J pour le premier bloc, on aura VRAI pour J1:J5, puis FAUX pour le reste. Pour le second bloc, on aura VRAI pour J1:J12, et FAUX pour le reste, etc. La colonne I représente cette partie de la formule, ligne par ligne.

7. Comme on est en train de multiplier des valeur (Des VRAI et/ou des FAUX avec des valeurs numériques, celles de la colonne B), on doit capturer l'erreur qui surviendra lorque en B, on aura "début" ou "fin" qui ne sont pas numérique et qui vont poser des problèmes à la matricielle. SIERREUR(...) permet cela en renvoyant soit la valeur de B lorsqu'elle est numérique, soit 0 lorsqu'elle ne l'est pas. C'est la colonne K et la partie SIERREUR(CNUM($B$1:$B$26);0)) de la matricielle.

8. L'arithmétique booléenne permet alors de multiplier I*J*K, de sorte que dès que FAUX est rencontré, on renvoie 0. C'est la colonne L qui illustre ici cette multiplication. A nouveau, par bloc, il faut imaginer le résultat. Pour le bloc 1, on a donc des VRAI en I et J pour les lignes 3:5, de sorte que l'on réaliser VRAI * VRAI * B = 1 * 1 * B. On aura donc une matrice 0;0;2;1;3;0;... (avec des 0 pour toutes les autres lignes), et donc la somme donnera bien 6, soit la valeur totale du bloc.

J'espère que cette façon de découper les choses t'aura permis de comprendre la formule matricielle utilisée.

[Pierre Fauconnier]

Oui mais cela fait deux formules et une colonne intermédiaire qu'il faut masquer. Personnellement je préfère m'en passer lorsque c'est possible, ce qui est présentement le cas 😉

[unparia]

Oui mais cela fait deux formules et une colonne intermédiaire qu'il faut masquer

Qu'il "faut masquer" ... ou pas ...
Il est assez fréquent, notamment en matière de suivi de consommation par rubriques budgétaires, que les sous-totaux partiels deviennent un peu plus qu'un luxe.
Je ne connais pas la finalité du projet de Jimmy92, mais (sans être "extralucide" ) je ne serais pas surpris que ce soit précisément un projet de cette nature (suivi de consommation par poste budgétaire)

[Jimmy92]

Merci 'unparia et à tous pour toutes ces explications très précises et circonstanciées.

J'ai introduit les formules les plus simples dans toutes celles que vous m'avez communiquées et cela fonctionne parfaitement, sans modification du fichier initial.
en C2: =SI(ESTNUM(B2);SOMME(C1;B2);"")

en D2: =SI(B2="Fin Prog";C1;"")


Merci encore, j'ai vraiment apprécié vos différentes implications et compétences pointues sur le sujet.

NB: La prochaine fois, j'ai bien compris qu'il faut joindre une copie d'écran et non le fichier lui-même.
Jmy