Comment puis-je facilement (cad sans macro) faire la somme
des 3 dernières cellules non vides d'une plage (en partant de la dernière
et en remontant) ?
"emmanuel.guyonneau" wrote in message news:big6t4$c73$
Bonjour à tous
Comment puis-je facilement (cad sans macro) faire la somme des 3 dernières cellules non vides d'une plage (en partant de la dernière et en remontant) ?
Merci de votre aide
Salut Emmanuel,
La formule MATRICIELLE suivante (saisir avec Ctrl-Maj-Entrée):
"emmanuel.guyonneau" <emmanuel.guyonneau@wanadoo.fr> wrote in message
news:big6t4$c73$1@news-reader5.wanadoo.fr...
Bonjour à tous
Comment puis-je facilement (cad sans macro) faire la somme
des 3 dernières cellules non vides d'une plage (en partant de la dernière
et en remontant) ?
"emmanuel.guyonneau" wrote in message news:big6t4$c73$
Bonjour à tous
Comment puis-je facilement (cad sans macro) faire la somme des 3 dernières cellules non vides d'une plage (en partant de la dernière et en remontant) ?
Merci de votre aide
Le num
Super ça marche au poil !! J'étais pas prêt de trouver une formule comme ça tout seul ...
Merci
"Daniel.M" a écrit dans le message news:
Salut Emmanuel,
La formule MATRICIELLE suivante (saisir avec Ctrl-Maj-Entrée):
A vrai dire, la formule de Daniel ne fonctionne que si 'Plage' est en colonne et débute en ligne 1. Dans un cas plus général, il faudrait prendre la formule, également matricielle : =SOMME(N(DECALER(plage;GRANDE.VALEUR(SI(plage<>"";LIGNE(INDIRECT("1:"&LIGNES (plage))));{1;2;3})-1;;;)))
C'est cette dernière formule que je vais essayer de commenter.
NB : a) dans ce qui suit, 'Plage' ne contient que des valeurs numériques, ou bien des cellules vides. b) prenons l'exemple où 'Plage' est composée des 7 cellules :
<vide> 20 30 <vide> 10 50 <vide>
Dans ce cas, la formule souhaitée doit renvoyer 30+10+50, somme des trois dernières cellules non vides de 'plage'.
1) Il faut commencer par chercher la position de toutes les valeurs non vides de la plage 'Plage' : =SI(Plage<>"";{1;2;3;4;5;6;7}) Attention : comme il s'agit d'une formule matricielle, les arguments de la fonction SI sont matriciels. cela signifie que : l'expression Plage<>"" est une matrice logique, de même dimension que 'Plage', et qui, dans notre exemple, vaut FAUX VRAI VRAI FAUX VRAI VRAI FAUX
2) La constante matricielle {1;2;3;4;5;6;7} doit être calculée en fonction du nombre d'éléments de 'plage'. Il s'agit d'une astuce classique. Pour cela, on commence par former la chaîne de caractères : "1:"&LIGNES(plage) Dans notre exemple, cette chaîne vaut : "1:7" Ensuite, il faut transformer cette chaîne en référence de plage : INDIRECT("1:"&LIGNES(plage)) Dans notre exemple, cette référence est celle des 7 premières lignes : 1:7 Il suffit alors de former la matrice souhaitée par : LIGNE(INDIRECT("1:"&LIGNES(plage)))
3) L'expression SI(plage<>"";LIGNE(INDIRECT("1:"&LIGNES(plage)))) renvoie donc soit la position de la valeur numérique si la cellule n'est pas vide et sinon la valeur logique FAUX, soit dans notre cas : FAUX 2 3 FAUX 5 6 FAUX
4) Donc si tu suis bien, on se trouve avec une formule qui est maintenant, dans notre cas : =SOMME(N(DECALER(plage;GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};{1;2;3})-1;;;) ))
Il faut maintenant isoler la position des trois dernières valeurs numériques : GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};1) renvoie 6, position de la dernière valeur non vide GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};2) renvoie 5, position de l'avant dernière valeur non vide etc, et donc, sous forme matricielle : GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};{1;2;3} renvoie la matrice {6;5;3}, position des trois dernières valeurs non vides
5) Il faut récupérer maintenant les valeurs elles mêmes (et non leur position). C'est le rôle de DECALER : DECALER(plage;{6;5;3}-1;;;))) renvoie une matrice de trois valeurs {50;10;30} qu'il faut sommer. Ouf !
Bernard
NB : L'expression LIGNE(Plage) de la formule originale de Daniel renvoie les numéros de lignes (et non les positions) des cellules de 'Plage', ce qui ne revient au même que si 'Plage' débute en ligne 1... 2) le rôle de la fonction N() est plus difficile à expliquer. Disons qu'on l'emploie pour pallier certains 'défauts' des fonctions SOMME et SOMMEPROD
Salut Jacky
"Jacky" <Jacky.jaeg@wanadoo.fr> a écrit dans le message news:
ufnBSCLbDHA.1740@TK2MSFTNGP10.phx.gbl...
A vrai dire, la formule de Daniel ne fonctionne que si 'Plage' est en
colonne et débute en ligne 1. Dans un cas plus général, il faudrait prendre
la formule, également matricielle :
=SOMME(N(DECALER(plage;GRANDE.VALEUR(SI(plage<>"";LIGNE(INDIRECT("1:"&LIGNES
(plage))));{1;2;3})-1;;;)))
C'est cette dernière formule que je vais essayer de commenter.
NB :
a) dans ce qui suit, 'Plage' ne contient que des valeurs numériques, ou bien
des cellules vides.
b) prenons l'exemple où 'Plage' est composée des 7 cellules :
<vide>
20
30
<vide>
10
50
<vide>
Dans ce cas, la formule souhaitée doit renvoyer 30+10+50, somme des trois
dernières cellules non vides de 'plage'.
1) Il faut commencer par chercher la position de toutes les valeurs non
vides
de la plage 'Plage' :
=SI(Plage<>"";{1;2;3;4;5;6;7})
Attention : comme il s'agit d'une formule matricielle, les arguments de la
fonction SI sont matriciels. cela signifie que :
l'expression Plage<>"" est une matrice logique, de même dimension que
'Plage', et qui, dans notre exemple, vaut
FAUX
VRAI
VRAI
FAUX
VRAI
VRAI
FAUX
2) La constante matricielle {1;2;3;4;5;6;7} doit être calculée en fonction
du nombre d'éléments de 'plage'. Il s'agit d'une astuce classique. Pour
cela, on commence par former la chaîne de caractères :
"1:"&LIGNES(plage)
Dans notre exemple, cette chaîne vaut :
"1:7"
Ensuite, il faut transformer cette chaîne en référence de plage :
INDIRECT("1:"&LIGNES(plage))
Dans notre exemple, cette référence est celle des 7 premières lignes :
1:7
Il suffit alors de former la matrice souhaitée par :
LIGNE(INDIRECT("1:"&LIGNES(plage)))
3) L'expression
SI(plage<>"";LIGNE(INDIRECT("1:"&LIGNES(plage))))
renvoie donc soit la position de la valeur numérique si la cellule n'est pas
vide
et sinon la valeur logique FAUX, soit dans notre cas :
FAUX
2
3
FAUX
5
6
FAUX
4) Donc si tu suis bien, on se trouve avec une formule qui est maintenant,
dans notre cas :
=SOMME(N(DECALER(plage;GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};{1;2;3})-1;;;)
))
Il faut maintenant isoler la position des trois dernières valeurs numériques
:
GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};1) renvoie 6, position de la dernière
valeur non vide
GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};2) renvoie 5, position de l'avant
dernière valeur non vide
etc, et donc, sous forme matricielle :
GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};{1;2;3} renvoie la matrice {6;5;3},
position des trois dernières valeurs non vides
5) Il faut récupérer maintenant les valeurs elles mêmes (et non leur
position). C'est le rôle de DECALER :
DECALER(plage;{6;5;3}-1;;;)))
renvoie une matrice de trois valeurs {50;10;30} qu'il faut sommer. Ouf !
Bernard
NB :
L'expression LIGNE(Plage) de la formule originale de Daniel renvoie les
numéros de lignes (et non les positions) des cellules de 'Plage', ce qui ne
revient au même que si 'Plage' débute en ligne 1...
2) le rôle de la fonction N() est plus difficile à expliquer. Disons qu'on
l'emploie pour pallier certains 'défauts' des fonctions SOMME et SOMMEPROD
A vrai dire, la formule de Daniel ne fonctionne que si 'Plage' est en colonne et débute en ligne 1. Dans un cas plus général, il faudrait prendre la formule, également matricielle : =SOMME(N(DECALER(plage;GRANDE.VALEUR(SI(plage<>"";LIGNE(INDIRECT("1:"&LIGNES (plage))));{1;2;3})-1;;;)))
C'est cette dernière formule que je vais essayer de commenter.
NB : a) dans ce qui suit, 'Plage' ne contient que des valeurs numériques, ou bien des cellules vides. b) prenons l'exemple où 'Plage' est composée des 7 cellules :
<vide> 20 30 <vide> 10 50 <vide>
Dans ce cas, la formule souhaitée doit renvoyer 30+10+50, somme des trois dernières cellules non vides de 'plage'.
1) Il faut commencer par chercher la position de toutes les valeurs non vides de la plage 'Plage' : =SI(Plage<>"";{1;2;3;4;5;6;7}) Attention : comme il s'agit d'une formule matricielle, les arguments de la fonction SI sont matriciels. cela signifie que : l'expression Plage<>"" est une matrice logique, de même dimension que 'Plage', et qui, dans notre exemple, vaut FAUX VRAI VRAI FAUX VRAI VRAI FAUX
2) La constante matricielle {1;2;3;4;5;6;7} doit être calculée en fonction du nombre d'éléments de 'plage'. Il s'agit d'une astuce classique. Pour cela, on commence par former la chaîne de caractères : "1:"&LIGNES(plage) Dans notre exemple, cette chaîne vaut : "1:7" Ensuite, il faut transformer cette chaîne en référence de plage : INDIRECT("1:"&LIGNES(plage)) Dans notre exemple, cette référence est celle des 7 premières lignes : 1:7 Il suffit alors de former la matrice souhaitée par : LIGNE(INDIRECT("1:"&LIGNES(plage)))
3) L'expression SI(plage<>"";LIGNE(INDIRECT("1:"&LIGNES(plage)))) renvoie donc soit la position de la valeur numérique si la cellule n'est pas vide et sinon la valeur logique FAUX, soit dans notre cas : FAUX 2 3 FAUX 5 6 FAUX
4) Donc si tu suis bien, on se trouve avec une formule qui est maintenant, dans notre cas : =SOMME(N(DECALER(plage;GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};{1;2;3})-1;;;) ))
Il faut maintenant isoler la position des trois dernières valeurs numériques : GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};1) renvoie 6, position de la dernière valeur non vide GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};2) renvoie 5, position de l'avant dernière valeur non vide etc, et donc, sous forme matricielle : GRANDE.VALEUR({FAUX;2;3;FAUX;5;6;FAUX};{1;2;3} renvoie la matrice {6;5;3}, position des trois dernières valeurs non vides
5) Il faut récupérer maintenant les valeurs elles mêmes (et non leur position). C'est le rôle de DECALER : DECALER(plage;{6;5;3}-1;;;))) renvoie une matrice de trois valeurs {50;10;30} qu'il faut sommer. Ouf !
Bernard
NB : L'expression LIGNE(Plage) de la formule originale de Daniel renvoie les numéros de lignes (et non les positions) des cellules de 'Plage', ce qui ne revient au même que si 'Plage' débute en ligne 1... 2) le rôle de la fonction N() est plus difficile à expliquer. Disons qu'on l'emploie pour pallier certains 'défauts' des fonctions SOMME et SOMMEPROD
Daniel.M
Salut Bernard (heureux de voir que tu reviens, on en profitera!),
Je salue ta générosité.
A vrai dire, la formule de Daniel ne fonctionne que si 'Plage' est en colonne et débute en ligne 1. ... NB : L'expression LIGNE(Plage) de la formule originale de Daniel renvoie les numéros de lignes (et non les positions) des cellules de 'Plage', ce qui ne
revient au même que si 'Plage' débute en ligne 1...
Exact. Merci de préciser.
... Dans un cas plus général, il faudrait prendre la formule, également matricielle :
Ou =SOMME(N(DECALER(Plage;GRANDE.VALEUR(SI(Plage<>"";LIGNE(Plage));{1;2;3}) -LIGNE(XX);;;))) ' Ici XX => Première cellule de la plage.
Ou =SOMME(N(DECALER(Plage;GRANDE.VALEUR(SI(Plage<>"";LIGNE(Plage));{1;2;3}) -CELLULE("ligne";Plage);;;)))
2) le rôle de la fonction N() est plus difficile à expliquer. Disons qu'on l'emploie pour pallier certains 'défauts' des fonctions SOMME et SOMMEPROD
C'est surtout le problème de DECALER() qui ne retourne pas toujours de matrice alors que N(DECALER()) l'oblige. Une incongruité, comme d'autres tel le traitement des matrices dans RANG/ SOMME.SI/ NB.SI, qui démontre bien que certaines fonctions XL ont bénéficié de 'meilleurs concepteurs' que d'autres.
Salutations amicales,
Daniel M.
Salut Bernard (heureux de voir que tu reviens, on en profitera!),
Je salue ta générosité.
A vrai dire, la formule de Daniel ne fonctionne que si 'Plage' est en
colonne et débute en ligne 1.
...
NB :
L'expression LIGNE(Plage) de la formule originale de Daniel renvoie les
numéros de lignes (et non les positions) des cellules de 'Plage', ce qui
ne
revient au même que si 'Plage' débute en ligne 1...
Exact. Merci de préciser.
... Dans un cas plus général, il faudrait prendre
la formule, également matricielle :
Ou
=SOMME(N(DECALER(Plage;GRANDE.VALEUR(SI(Plage<>"";LIGNE(Plage));{1;2;3})
-LIGNE(XX);;;)))
' Ici XX => Première cellule de la plage.
Ou
=SOMME(N(DECALER(Plage;GRANDE.VALEUR(SI(Plage<>"";LIGNE(Plage));{1;2;3})
-CELLULE("ligne";Plage);;;)))
2) le rôle de la fonction N() est plus difficile à expliquer. Disons qu'on
l'emploie pour pallier certains 'défauts' des fonctions SOMME et SOMMEPROD
C'est surtout le problème de DECALER() qui ne retourne pas toujours de
matrice alors que N(DECALER()) l'oblige. Une incongruité, comme d'autres tel
le traitement des matrices dans RANG/ SOMME.SI/ NB.SI, qui démontre bien que
certaines fonctions XL ont bénéficié de 'meilleurs concepteurs' que
d'autres.
Salut Bernard (heureux de voir que tu reviens, on en profitera!),
Je salue ta générosité.
A vrai dire, la formule de Daniel ne fonctionne que si 'Plage' est en colonne et débute en ligne 1. ... NB : L'expression LIGNE(Plage) de la formule originale de Daniel renvoie les numéros de lignes (et non les positions) des cellules de 'Plage', ce qui ne
revient au même que si 'Plage' débute en ligne 1...
Exact. Merci de préciser.
... Dans un cas plus général, il faudrait prendre la formule, également matricielle :
Ou =SOMME(N(DECALER(Plage;GRANDE.VALEUR(SI(Plage<>"";LIGNE(Plage));{1;2;3}) -LIGNE(XX);;;))) ' Ici XX => Première cellule de la plage.
Ou =SOMME(N(DECALER(Plage;GRANDE.VALEUR(SI(Plage<>"";LIGNE(Plage));{1;2;3}) -CELLULE("ligne";Plage);;;)))
2) le rôle de la fonction N() est plus difficile à expliquer. Disons qu'on l'emploie pour pallier certains 'défauts' des fonctions SOMME et SOMMEPROD
C'est surtout le problème de DECALER() qui ne retourne pas toujours de matrice alors que N(DECALER()) l'oblige. Une incongruité, comme d'autres tel le traitement des matrices dans RANG/ SOMME.SI/ NB.SI, qui démontre bien que certaines fonctions XL ont bénéficié de 'meilleurs concepteurs' que d'autres.
Salutations amicales,
Daniel M.
Jacky
Merci pour toutes ces explications, je vais essayer de décortiquer cela demain matin, car la fatigue du soir ne me permet plus de me concentrer sur une formule aussi complexe(pour moi) Merci à tous de votre patience
"emmanuel.guyonneau" wrote in message news:big6t4$c73$
Bonjour à tous
Comment puis-je facilement (cad sans macro) faire la somme des 3 dernières cellules non vides d'une plage (en partant de la dernière
et en remontant) ?
Merci de votre aide
Merci pour toutes ces explications, je vais essayer de décortiquer cela
demain matin, car la fatigue du soir ne me permet plus de me concentrer sur
une formule aussi complexe(pour moi)
Merci à tous de votre patience
"Jacky" <Jacky.jaeg@wanadoo.fr> a écrit dans le message news:
ufnBSCLbDHA.1740@TK2MSFTNGP10.phx.gbl...
Merci pour toutes ces explications, je vais essayer de décortiquer cela demain matin, car la fatigue du soir ne me permet plus de me concentrer sur une formule aussi complexe(pour moi) Merci à tous de votre patience
"emmanuel.guyonneau" wrote in message news:big6t4$c73$
Bonjour à tous
Comment puis-je facilement (cad sans macro) faire la somme des 3 dernières cellules non vides d'une plage (en partant de la dernière
et en remontant) ?
Merci de votre aide
Daniel.M
Salut Bernard,
C'est surtout le problème de DECALER() qui ne retourne pas toujours de matrice alors que N(DECALER()) l'oblige.
Merci pour cette précision. N() n'a donc pas qu'un rôle de "brosse à numériser". Mais bizarrement, dans le cas présent, on tombe bien sur la bonne matrice quand on évalue DECALER(...) :-o
Oui mais cela est le résultat de la 'méthode F9', i.e. utilisation de F9 pour générer des résultats intermédiaires dans la ligne de formule. Or, il ne faut pas toujours s'y fier. Si Excel utilisait toujours une méthode standard de résolution des expressions ('parsing' en anglais, avec BNF ou Infix ou autre), on pourrait être assuré que ce qu'on VOIT après évaluation intermédiaire (par F9) est bel et bien ce qu'Excel voit quand il traite la formule. Malheureusement, cette cohérence n'existe pas toujours. D'autres critères (difficiles à saisir quand on ne voit pas le code C de l'implantation concrète) entrent en ligne de compte.
C'est pourquoi DECALER() ne réagit pas correctement en certaines occasions. C'est pourquoi RECHERCHEV() ne retourne pas toujours des matrices. C'est pourquoi une formule fondamentalement matricielle, comme =SOMME(NB.SI($A$1:$A$10;$C$1:$C$3)), mais saisie de façon NORMALE (Entrée seulement) donnera un résultat différent dépendant de la ligne sur laquelle on l'inscrit. Ici, par exemple, je plaiderais fortement pour une évaluation indépendante de la ligne (i.e. qui retourne toujours le premier terme de la matrice) mais ce n'est pas le choix MS (qui aligne les termes retournés sur la ligne de la formule!). C'est une option qui se défend : elle permet plus facilement l'erreur de l'usager qui aurait oublié la saisie en matricielle mais ce faisant, elle met à mal la cohérence dans l'évaluation des expressions d'une formule (même chose pour RECHERCHEV avec une matrice comme premier argument).
Bon, c'est l'heure tardive et je me trouve 'heavy'. Alors, salutations et dodo,
Daniel M.
Salut Bernard,
C'est surtout le problème de DECALER() qui ne retourne pas toujours de
matrice alors que N(DECALER()) l'oblige.
Merci pour cette précision. N() n'a donc pas qu'un rôle de "brosse à
numériser". Mais bizarrement, dans le cas présent, on tombe bien sur la
bonne matrice quand on évalue DECALER(...) :-o
Oui mais cela est le résultat de la 'méthode F9', i.e. utilisation de F9
pour générer des résultats intermédiaires dans la ligne de formule. Or, il
ne faut pas toujours s'y fier.
Si Excel utilisait toujours une méthode standard de résolution des
expressions ('parsing' en anglais, avec BNF ou Infix ou autre), on pourrait
être assuré que ce qu'on VOIT après évaluation intermédiaire (par F9) est
bel et bien ce qu'Excel voit quand il traite la formule.
Malheureusement, cette cohérence n'existe pas toujours. D'autres critères
(difficiles à saisir quand on ne voit pas le code C de l'implantation
concrète) entrent en ligne de compte.
C'est pourquoi DECALER() ne réagit pas correctement en certaines occasions.
C'est pourquoi RECHERCHEV() ne retourne pas toujours des matrices.
C'est pourquoi une formule fondamentalement matricielle, comme
=SOMME(NB.SI($A$1:$A$10;$C$1:$C$3)), mais saisie de façon NORMALE (Entrée
seulement) donnera un résultat différent dépendant de la ligne sur laquelle
on l'inscrit. Ici, par exemple, je plaiderais fortement pour une évaluation
indépendante de la ligne (i.e. qui retourne toujours le premier terme de la
matrice) mais ce n'est pas le choix MS (qui aligne les termes retournés sur
la ligne de la formule!). C'est une option qui se défend : elle permet plus
facilement l'erreur de l'usager qui aurait oublié la saisie en matricielle
mais ce faisant, elle met à mal la cohérence dans l'évaluation des
expressions d'une formule (même chose pour RECHERCHEV avec une matrice comme
premier argument).
Bon, c'est l'heure tardive et je me trouve 'heavy'. Alors, salutations et
dodo,
C'est surtout le problème de DECALER() qui ne retourne pas toujours de matrice alors que N(DECALER()) l'oblige.
Merci pour cette précision. N() n'a donc pas qu'un rôle de "brosse à numériser". Mais bizarrement, dans le cas présent, on tombe bien sur la bonne matrice quand on évalue DECALER(...) :-o
Oui mais cela est le résultat de la 'méthode F9', i.e. utilisation de F9 pour générer des résultats intermédiaires dans la ligne de formule. Or, il ne faut pas toujours s'y fier. Si Excel utilisait toujours une méthode standard de résolution des expressions ('parsing' en anglais, avec BNF ou Infix ou autre), on pourrait être assuré que ce qu'on VOIT après évaluation intermédiaire (par F9) est bel et bien ce qu'Excel voit quand il traite la formule. Malheureusement, cette cohérence n'existe pas toujours. D'autres critères (difficiles à saisir quand on ne voit pas le code C de l'implantation concrète) entrent en ligne de compte.
C'est pourquoi DECALER() ne réagit pas correctement en certaines occasions. C'est pourquoi RECHERCHEV() ne retourne pas toujours des matrices. C'est pourquoi une formule fondamentalement matricielle, comme =SOMME(NB.SI($A$1:$A$10;$C$1:$C$3)), mais saisie de façon NORMALE (Entrée seulement) donnera un résultat différent dépendant de la ligne sur laquelle on l'inscrit. Ici, par exemple, je plaiderais fortement pour une évaluation indépendante de la ligne (i.e. qui retourne toujours le premier terme de la matrice) mais ce n'est pas le choix MS (qui aligne les termes retournés sur la ligne de la formule!). C'est une option qui se défend : elle permet plus facilement l'erreur de l'usager qui aurait oublié la saisie en matricielle mais ce faisant, elle met à mal la cohérence dans l'évaluation des expressions d'une formule (même chose pour RECHERCHEV avec une matrice comme premier argument).
Bon, c'est l'heure tardive et je me trouve 'heavy'. Alors, salutations et dodo,
