Transformer valeur en case du tableur

[Onecraft]

Bonjour à toutes et tous,

je travaille actuellement sur un traitement de données conséquent pour la première fois de ma vie. J'ai besoin de votre aide

Dans ce travail, je dois effectuer une sorte de traitement de données automatique qui s'ajuste en fonction de la durée de la manipulation (c'est une manip scientifique sur la conductivité électrique).

Une des fonction qu'il faut, c'est pouvoir faire la somme des valeurs d'une colonne, mais cette colonne aura une taille variable en fonction des fichiers à traiter.
Grâce à la fonction EQUIV, je peux trouver la ligne à laquelle s'arrête la manipulation en trouvant une valeur = 0 qui signifie l'arrêt de la manip. Mais j'aimerais, lorsque je veux faire la somme d'une colonne, dire a Excel de faire la somme de la case I22 (toujours la même) à I(numéro de la ligne de fin de manip trouvée par la fonction EQUIV).

Savez vous comment puis-je faire cela ?

Merci d'avance

P-S : il est possible que ce soit excel 2019, je ne sais pas comment voir..

 

[Jacky67]

Bonjour à toutes et tous,

je travaille actuellement sur un traitement de données conséquent pour la première fois de ma vie. J'ai besoin de votre aide

Dans ce travail, je dois effectuer une sorte de traitement de données automatique qui s'ajuste en fonction de la durée de la manipulation (c'est une manip scientifique sur la conductivité électrique).

Une des fonction qu'il faut, c'est pouvoir faire la somme des valeurs d'une colonne, mais cette colonne aura une taille variable en fonction des fichiers à traiter.
Grâce à la fonction EQUIV, je peux trouver la ligne à laquelle s'arrête la manipulation en trouvant une valeur = 0 qui signifie l'arrêt de la manip. Mais j'aimerais, lorsque je veux faire la somme d'une colonne, dire a Excel de faire la somme de la case I22 (toujours la même) à I(numéro de la ligne de fin de manip trouvée par la fonction EQUIV).

Savez vous comment puis-je faire cela ?

Merci d'avance

P-S : il est possible que ce soit excel 2019, je ne sais pas comment voir..

Bonjour,
Difficile de faire sur une image
Néanmoins un essai avec indirect
=SOMME(INDIRECT("I21:I"&EQUIV(0;$I$21:$I$65000;0)+19))

 

[Onecraft]

Hélas, je n'arrive pas à vous transmettre mon dossier, il est "trop volumineux" selon le site. Et comme tout dépend de tout, je ne peux pas isoler une partie du dossier. J'ai essayé votre formule, elle ne marche pas. Pourtant la fonction INDIRECT est tout à fait ce que je cherchais, merci! J'ai essayé de changer l'ordre, etc mais pour l'instant cela ne marche pas.

J'aimerais, si possible, que vous m'expliquiez brièvement pourquoi vous avez mis des guillemets, des dollars, et des "&".
J'ai essayé ceci : =SOMME(I22:I(INDIRECT(EQUIV(0;D1: D30100;0))))
En effet, il faut que la valeur à laquelle est rattachée I pour donner I24373 soit trouvée systématiquement dans la colonne D lorsque le premier 0 de la colonne D sort.
Pourquoi cela ne marcherait pas ? Comment faire comprendre à excel que le I(INDIRECT(EQUIV(0;D1: D30100;0)))) signifie I24373 dans ce cas là ? (cf dossier joint)

Merci beaucoup pour votre aide

 

[mapomme]

Bonjour @Onecraft,
Bienvenue sur XLD ,

Un peu de mal à comprendre quand on n'a pas de fichier.

Une question : Y a-t-il des nombres au delà de la dernière ligne de I contenant 0 ?

Si non, pourquoi ne pas faire simplement ? : =SOMME(I22:I1048576)

 

[Onecraft]

En fait, ce n'est pas sur la colonne I que l'on va se fixer pour trouver le 0 qui donnera la ligne de fin de manip, mais sur la colonne D. Quand le premier 0 de la colonne D sort, soit la 24373ème ligne pour D24373 et pour ce fichier, alors c'est qu'il n'y a plus de données à traiter. Ensuite, il n'y à que des 0 dans cette colonne D. Toutes les autres colonnes à partir de la ligne 24373 donnent des valeurs inutiles car elles ne correspondent pas à la manip, mais continuent de calculer à partir du 0 de la colonne D et d'autres valeurs fixes du tableur.

Ce que je veut faire pour cette somme de la colonne I, c'est faire une somme à partir de la première valeur de cette colonne I jusqu'a I24373, mais ce 24373 on l'a trouvé car c'est en D24373 que le premier 0 apparaît. Hélas, ton idée était bonne, mais effectivement à partir de I24373, une valeur fausse apparaît issue d'un calcul erroné. Je pourrais donner cette valeur comme référence pour que le calcul de la somme colonne I stoppe quand Excel la trouve, mais cette fausse valeur est amenée à changer selon le fichier à traiter, alors que dans la colonne D, quand le premier 0 apparaît c'est toujours la fin de la manip, et on peut dire à toutes les autres colonnes de calculer selon cette première ligne d'apparition du 0 en D.

J'espère que j'ai assez bien expliqué

 

[mapomme]

Re,

Voyez la formule matricielle en I19 :

=SOMME(DECALER(I22;0;0;PETITE.VALEUR(SI(ABS(D21:D33333) < 10^-5;LIGNE(D21:D33333));1)-LIGNE(I22)+1;1))

Je ne recherche pas la valeur 0 dans la colonne D. Comme les valeurs sont issues de calculs, on pourrait avoir des valeurs très petites (de type 0,00000012) sans jamais avoir strictement la valeur 0. Je recherche donc la première valeur dont la valeur absolue est inférieure à 10^-6 (soit < 0,000001)

Le numéro de ligne où se trouve cette valeur est donnée par:
PETITE.VALEUR(SI(ABS(D21:D33333) < 10^-6;LIGNE(D21:D33333));1)

nota : si vos valeurs utiles sont susceptibles de s'étendre au delà la ligne 33 333, alors augmentez cette valeur dans la formule.

Attention ! La formule est matricielle.

 

[Jacky67]

Re,

Voyez la formule matricielle en I19 :

=SOMME(DECALER(I22;0;0;PETITE.VALEUR(SI(ABS(D21:D33333) < 10^-5;LIGNE(D21:D33333));1)-LIGNE(I22)+1;1))

Je ne recherche pas la valeur 0 dans la colonne D. Comme les valeurs sont issues de calculs, on pourrait avoir des valeurs très petites (de type 0,00000012) sans jamais avoir strictement la valeur 0. Je recherche donc la première valeur dont la valeur absolue est inférieure à 10^-6 (soit < 0,000001)

Le numéro de ligne où se trouve cette valeur est donnée par:
PETITE.VALEUR(SI(ABS(D21:D33333) < 10^-6;LIGNE(D21:D33333));1)

Attention ! La formule est matricielle.

Regarde la pièce jointe 1068800

Hello mapomme
Bien vu l'histoire "on pourrait avoir des valeurs très petites (de type 0,00000012) sans jamais avoir strictement la valeur 0. "
Fallait y penser
Et comme souvent, bravo pour la formule

 

[mapomme]

Bonsoir @Jacky67

J'étais parti sur une formule analogue à la tienne. Et quand @Onecraft a dit qu'il n'y arrivait, j'ai pensé à cette histoire de "presque zéro" (quand ça t'arrive et que tu rames pour trouver le pourquoi de la chose, tu t'en souviens !)

Tiens en voyant ton 67, ça m'a donné envie d'en manger une (une classique au fromage blanc, crème fraiche, lardons et oignons - point de fantaisie - loin de moi les gratinées, à la banane, etc). Mais ici pas moyen d'en touver une bonne et puis c'est fermé jusqu'à mardi.

 

[Jacky67]

Bonsoir @Jacky67

J'étais parti sur une formule analogue à la tienne. Et quand @Onecraft a dit qu'il n'y arrivait, j'ai pensé à cette histoire de "presque zéro" (quand ça t'arrive et que tu rames pour trouver le pourquoi de la chose, tu t'en souviens !)

Tiens en voyant ton 67, ça m'a donné envie d'en manger une (une classique au fromage blanc, crème fraiche, lardons et oignons - point de fantaisie - loin de moi les gratinées, à la banane, etc). Mais ici pas moyen d'en touver une bonne et puis c'est fermé jusqu'à mardi.

Je suis bien d'accord avec toi,- point de fantaisie -.
Dommage que tu sois si loin, je t'aurais volontiers invité à une "faite maison".

 

[Onecraft]

Salut!

Merci pour cette belle formule. Quelles sont les avantages de passer par une forme "matricielle" ?
Je suis certain que le 0 est strictement 0 : en fait je copie colle des données brutes d'un logiciel de mesure par exportation. Le procédé est le suivant : on clique sur la zone nom pour aller sur une rubrique que j'ai créé exprès pour coller les données du logiciel, puis on colle ces données brutes. Excel les copie-colle sur les colonnes A, D et E (temps et cond E/S) du bon tableur. Ce sont les colonnes sélectionnées dans l'image 1. Et j'ai étendu ce tableur, pour que si la manip dure plus longtemps, excel prenne toutes les valeurs en compte à partir des données brutes. Mon tableur ici s'étend jusqu'a la ligne 30100, mais les données brutes font environ 25000 lignes. C'est pour ça que quand excel copie colle, à partir de la ligne 24373, il n'y a que des 0, excel copie-colle une valeur qui n'existe pas donc 0.

Par contre, ta formule marche carrément ! Simplement, c'est des vrais 0! Merci beaucoup!!!

 

[Jacky67]

Salut!

Merci pour cette belle formule. Quelles sont les avantages de passer par une forme "matricielle" ?
Je suis certain que le 0 est strictement 0 : en fait je copie colle des données brutes d'un logiciel de mesure par exportation. Le procédé est le suivant : on clique sur la zone nom pour aller sur une rubrique que j'ai créé exprès pour coller les données du logiciel, puis on colle ces données brutes. Excel les copie-colle sur les colonnes A, D et E (temps et cond E/S) du bon tableur. Ce sont les colonnes sélectionnées dans l'image 1. Et j'ai étendu ce tableur, pour que si la manip dure plus longtemps, excel prenne toutes les valeurs en compte à partir des données brutes. Mon tableur ici s'étend jusqu'a la ligne 30100, mais les données brutes font environ 25000 lignes. C'est pour ça que quand excel copie colle, à partir de la ligne 24373, il n'y a que des 0, excel copie-colle une valeur qui n'existe pas donc 0.

Par contre, ta formule marche carrément ! Simplement, c'est des vrais 0! Merci beaucoup!!!

Re..
Oui, la formule de mapomme fonctionne dans tous les cas.
Tu y étais presque avec
"J'ai essayé ceci : =SOMME(I22:I(INDIRECT(EQUIV(0;D1: D30100;0)))) "
=SOMME(INDIRECT("I22:I"&EQUIV(0;D1: D30100;0)))
A condition qu'il n'y ait jamais de zéros entre D1 et D22
Sinon plutôt
=SOMME(INDIRECT("I22:I"&EQUIV(0;D22:D30100;0)+21))
Pour ce qui est de
"vous m'expliquiez brièvement pourquoi vous avez mis des guillemets, des dollars, et des "&".
Les dollars contrairement à la monnaie permettent de figer une colonne s'il est placé devant la lettre et figer la ligne s'il est placé devant le chiffre (inutile dans ce cas, mais ne fait pas de mal)
"&"(esperluette) sert à concaténer texte+texte+chiffre+etc.. (tout en restant du texte)
En ce qui nous concerne
=SOMME(INDIRECT("I22:I"&EQUIV(0;D1: D30100;0)))
On cherche a faire une somme entre i22 et Ix
x étant inconnu, sera calculer par equiv()
Excel ne comprend pas SOMME(I22:I+formule
On utilise "indirect()" qui permet de faire comprendre à Excel une formule sous forme de texte
On aura donc dans ce cas le texte "I22:I"&24352 qui sera transformé par indirect en I22:I24352 et sera calculable par somme()
Un petit coup de F1 sur indirect donne plus d'explication.

Bonne nuit

 

[mapomme]

Quelles sont les avantages de passer par une forme "matricielle" ?

Une formule matricielle est utile quand on veut travailler dans une seule formule sur une plage contigüe de cellules (en général une partie de colonne).

Exemple : chercher dans une plage de valeurs (ex A5:A25) la dernière valeur multiple de 3.
Pour une cellule, c'est facile de savoir si une valeur est multiple de 3. On utilise la fonction MOD :
=MOD(A5;3)=0. Si A5 est multiple de 3, alors l'expression vaut VRAI sinon elle vaut FAUX.
Pour savoir le numéro de ligne, on écrira : SI(MOD(A5;3)=0;LIGNE(A5);"") qui retourne le numéro de la la ligne de A5 si A5 est multiple de 3.

Mais, nous ce qu'on cherche, c'est la dernière valeur de A5:A25 qui soit multiple de 3.
Il faut donc évaluer cette formule pour A5, A6, A7, .. , A25. On aura un tableau d'entier correspondant aux lignes qui contiennent une valeur multiple de 3 parsemé de valeur "" correspondant aux valeurs qui ne sont pas multiples de 3.

Pour indiquer à Excel qu'on veut faire cela pour chaque élément de la plage, on voudrait écrire :
SI(MOD(A5:A25;3)=0;LIGNE(A5:A25);""). Mais Excel ne comprend pas ce qu'on veut de lui. Excel n'évaluera cette formule que pour A5.
En effet MOD ne renvoie qu'une valeur et LIGNE ne renvoie qu'une valeur aussi.

Pour indiquer à Excel qu'on veut en retour, un tableau ou matrice, il faut valider par Ctrl+Maj+Entrée.

Alors, Excel évaluera la formule pour chacune des cellules de A5:A25 et renverra une matrice des résultats du type : {5;"";"";"";9;"";"";"";"";10;11; ... ;"";24;""}

A partir de là, on cherche la plus grande valeur de cette matrice :
GRANDE.VALEUR({5;"";"";"";9;"";"";"";"";10;11; ... ;"";24;""} ; 1) qui retourne 24
On sait donc que 24 est la dernière ligne contenant une valeur multiple de 3 dans la plage A5:A25

Pour trouver cette valeur, on utilisa INDEX : =INDEX (A:A ; 24)

Ce qui donne, avec validation matricielle:
= INDEX (A:A ; 24)
= INDEX ( A:A ; GRANDE.VALEUR({5;"";"";"";9;"";"";"";"";10;11; ... ;"";24;""} ; 1))
= INDEX ( A:A ; GRANDE.VALEUR( SI(MOD(A5:A25;3)=0;LIGNE(A5:A25);"") ; 1))

Les formules matricielles sont rapidement gourmandes en ressources:

• ne pas les multiplier
• les limiter aux plages strictement nécessaires (ne pas utiliser la plage A:A si A5:A25 suffit)
• certaines fonctions ne sont pas compatibles avec la forme matricielle

Les formules matricielles peuvent la plupart du temps être évitées en utilisant des colonnes auxiliaires.

remarque : dans la formule, on trouve la colonne A:A. Ce n'est pas en contradiction avec la limitation des plages. En effet dans cette formule matricielle, les fonctions concernées par un retour matriciel sont MOD et LIGNE et en aucun cas la fonction INDEX.

Bonnet de nuit

 

[Onecraft]

Merci beaucoup à vous deux
Quand on "code" une sorte de formule sur excel, 0 signifie vrai et 1 signifie faux ? Je te remercie énormément pour cette explication. Certes, je ne comprend pas tout, mais tu es très pédagogue

Cela me servira, c'est sur !!

Je marque le topic comme résolu, et merci encore

 

[mapomme]

Quand on "code" une sorte de formule sur excel, 0 signifie vrai et 1 signifie faux ?

Quand VRAI et/ou FAUX interviennent dans des opérations arithmétiques, alors Excel convertit FAUX en 0 et convertit VRAI en 1.

=FAUX+VRAI+FAUX+VRAI retourne 2 (opération arithmétique).
Mais = FAUX & "-Pas" retourne FAUX-PAS (opération sur texte et non arithmétique)

Pour forcer VRAI en 1 et FAUX en 0, on peut utiliser la fonction N() :
= N(FAUX) & " PAS" retourne 0 PAS

 

[Onecraft]

J'en profite pour faire une sorte d'extension de ma demande. Cette fois, je dois faire des graphiques qui correspondent aux opérations du tableur. Mais encore une fois, il faut que le graphique lise une plage de données variable, la limite étant donnée par le 1er 0 de la ligne D, comme précédemment.

Comment dire a Excel de lire deux plage de données jusqu'au 1er 0 de la colonne D qui n'intervient pas dans la représentation graphique de ce même graphique ?

Je vous joins ce à quoi cela devrait ressembler manuellement, lorsqu'on définit les données lues par Excel sans faire quelque chose d'adaptatif.

Merci