Sommes partielles sur grande quantité de données

Version imprimable

20/04/2017, 00h04
Jimmy92

1 pièce(s) jointe(s)

Sommes partielles sur grande quantité de données

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
20/04/2017, 12h35
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:

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

Cellule D2

Code:

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

Cellule E2

Code:

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

a appliquer sur toutes les lignes
-passer par du code VBA
20/04/2017, 16h32
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:

=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.
20/04/2017, 22h20
unparia

1 pièce(s) jointe(s)

Pour illustrer ce que j'ai écrit --->>
Pièce jointe 266667
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
21/04/2017, 10h46
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
21/04/2017, 11h16
unparia

Citation:

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".
21/04/2017, 14h57
Pierre Fauconnier

1 pièce(s) jointe(s)

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

Citation:

=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));"")

Pièce jointe 266945
21/04/2017, 16h18
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,
21/04/2017, 17h16
unparia

Citation:

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 ;)

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:

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

21/04/2017, 20h18
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.
21/04/2017, 20h20
Pierre Fauconnier

Citation:

Envoyé par unparia

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 :mouarf: :mouarf: :mouarf:
21/04/2017, 22h53
unparia

Citation:

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 :

Citation:

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 :

Citation:

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

Citation:

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 :

Citation:

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.:pleure:
http://www.rhinfo.com/thematiques/ap...un-visionnaire
22/04/2017, 07h44
unparia

Revenons de bon matin à nos moutons.
Tout ce que j'ai conçu depuis le début l'étais sur la base d'une "vision" différente de la réalité : je "voyais les mots "début" et "fin" en colonne A et en regard du 1er et du dernier nombre de chaque groupe, ce qui faisait qu'il n'y avait aucune cellule vide entre les groupes en colonne B, raison pour laquelle j'en ajoutais une en vue de mon traitement.
Ayant ensuite (la copie d'écran de Pierre Fauconnier) découvert que la disposition était un peu différente, j'ai "tripotaillé" ma propre conception pour "coller" à cette autre réalité sans tout recommencer.

Mais si j'avais pu avoir auparavant la copie d'écran qui apporte un éclairage de la disposition des données autre que celui que je "voyais", j'aurais d'emblée proposer ceci, qui laisse totalement intacte la feuille de calcul. La chose est possible puisqu'avec cette disposition des données les groupes de nombres en colonne B sont déjà séparés (par deux cellules vides).

Tout peut donc rester en l'état (les mots et la disposition) avec cette "découverte" ainsi :
- formule en C2 :

Code:

=SI(ESTNUM(B2);SOMME(C1;B2);"")

- formule en D2 :

Code:

=SI(OU(ESTNUM(B3);B3="");"";C2)

Puis tirer vers le bas chacune de ces deux formules

Voilà.

Ceci étant dit : je préfère de beaucoup ma propre "vision" des choses (telle qu'elle était).
22/04/2017, 08h01
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 😉
22/04/2017, 10h49
unparia

Citation:

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)
24/04/2017, 10h59
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