Comment aller recherche les X dernière valeur d'une colonne ??

[williamss]

Bonjour,

Dans un tableau il y a dans des colonnes avec des cellules vides (suite à une formule avec guillemets) et avec des valeurs retrouvées par la formule.

....|..A
01 | 1
02 | 5
03 |
04 | 8
05 |
06 | 4
07 | 0
08 | 6
09 |
10 | 7
11 | 5

Quelle formule (non matricielle pour que je l'utilise dans dans des formules) faudrait il utiliser pour que dans une cellule où je mettrais cette formule cela nous donnerait le derniers nombre sorti dans la colonnes de A1 à A11 soit ici 5 ??
Puis idem dans une autre cellule avec une autre formule (non matricielle) pour savoir l'avant avant dernier nombre sorti dans la colonne de A1 à A11 soit ici le nombre 6 qui est dans la cellule A8 ??

Merci

Williams

 

[williamss]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour.

Moi, ça fait des jours que je répète sans beaucoup plus de succès que joindre un fichier via un site qui ne les conserve que pour une durée limitée * n'est pas une bonne idée : Un visiteur futur qui tombera sur ce fil pour résoudre un problème identique n'aura plus accès à ce fichier.
C'est sûr : vous, vous aurez été dépanné, mais les autres ?

* alors qu'il est si simple de joindre un fichier directement sur XLD !

Ok, voici le fichier en pièce jointe directment sur XLD.

Si je l'ai fais avec Cjoint c'est question d'habitude.

Williams

 

[Victor21]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Re, williamss

Désolé de donner plus de conseils que de solutions !
Pour rappel :

- ILLUSTREZ VOTRE DEMANDE : Afin de faciliter la compréhension de votre demande il est conseillé de joindre un PETIT fichier qui illustre votre question précise et montre les résultats que vous cherchez à obtenir.

Près de 1500 lignes sur plus de 100 colonnes en Feuil1 avec quelques #valeur et quelques #ref pour agrémenter le tout, mais rien dans la colonne FF.
Autant de lignes sur plus de 250 colonnes vides en Feuil2.

Synthétisez votre problème sur une feuille, une dizaine de lignes et de colonnes, les résultats attendus, le raisonnement pour les atteindre. Vous comme nous y verrons plus clair.

 

[williamss]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Voici le fichier plus petit avec des exemples mais bien sûrs les cellules prises dans le tableau "ECARTS DE SORTIES DES NUMEROS SEULEMENT LORS DES SORTIES" dans les formules du tableau "MOYENNE MOBILE" sont ce qu'il faudrait que d'autres sous formules puissent aller chercher pour donner ce résultat. Seul la cellule du tableau "ECARTS DE SORTIES DES NUMEROS" serra bien mise ainsi dans ces formules.

Donc comme aller chercher ces 9 dernières valeurs sorties (dernière, avant dernière...) dans le tableau ECARTS DE SORTIES DES NUMEROS SEULEMENT LORS DES SORTIES ??

Williams

 

[Victor21]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Re,

Exemple de la cellule Z139. Elle prend en compte les cellules (colonne O) en jaune, pas les autres. Pourquoi ?
Dans le tableau ci-dessous, Les 4 premières jaunes ne font pas partie des 9 dernières, et les 4 dernières blanches ne sont pas prises en compte.

Je crains qu'il ne vous faille encore faire un effort d'explication. Pour ma part, j'abandonne.

12
7
7
4
2
1
1
10
21
6
1
8
5
10
25
11

 

[williamss]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Re Victor,

Désolé j'avais fais des erreurs dans les 2 formule de la colonne Z, donc j'ai rectifié la pièce jointes.

Car il n'y a pas des trous comme le montre votre tableau puisque c'est les 8 cellules avec une valeur qu'il faut aller chercher.

Désolé pour l'erreur.

merci

Williams

 

[piga25]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour,

Pas facile Patrick de tout comprendre.

Mais bon, voir si cela peu convenir.

Pour les valeurs dans le tableau n° 2 (celui du milieu) juste cette formule en N6 et à recopier :

=SI(C6>C7;C6;"")

Puis pour le tableau n° 3 (celui de droite).
Si tout compris : Addition des 8 dernière valeurs de la colonne correspondante dans le tableau 2 + celle correspondante à la ligne dans le tableau 1 et le tout divisé par 10 (rien que cela).
Donc faire un essais en Y139 puis à recopier:

=(SOMMEPROD((LIGNE(N$5:N139)>=GRANDE.VALEUR((N$5:N139<>"")*LIGNE(N$5:N139);8))*1;N$5:N139)+C139)/10

Plus simple avec le fichier.

 

[williamss]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Merci bp Piga25, c'est ce dont je cherchais à réaliser

Si j'ai bien compris une partie de la formule le 8 représente le nombre des derniers valeurs qui sont additionner ??

Williams

 

[piga25]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour williamss

En effet le 8 représente bien le nombre des dernières valeurs qui sont additionner.
Il doit être possible de le remplacer par une référence de cellule, genre A1. Puis dans cette cellule vous indiquerez le nombre des dernières valeurs que vous voulez additionner.

 

[piga25]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Re

Votre fichier modifié. En A1 vous tapez le nombre des dernières valeurs que vous voulez, et le tableau de droite se met automatiquement à jour.
Formule en Y5 à recopier à droite et vers le bas.

=SI(NB(N$5:N6)>=$A$1;(SOMMEPROD((LIGNE(N$5:N139)>=GRANDE.VALEUR((N$5:N139<>"")*LIGNE(N$5:N139);$A$1))*1;N$5:N139)+C139)/10;"")

 

[williamss]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Re Bonjour,

Suite à la formule que vous m'avez montré j'ai pu avancé.

La seul chose dont je tente de faire maintenant c'est de trouver 8 formules qui permettraient de savoir en me basant toujours sur l'avant dernière tableau quel est la dernière, avant dernière, avant... dernière valeur qui est sortie dans ce tableau par rapport à la ligne où se trouvera dans le dernier tableau.

Par exemple pour la formule qui permettrait de savoir la dernier valeur de l'écart sortie dans l'avant dernier tableau si on se trouve à la ligne 130 de la colonne Y (colonne du numéro 1 du dernier tableau) alors cela nous donnerait comme résultat 26 puisque dans la colonne N ( colonne aussi du numéro 1 de l'avant dernier tableau) c'est le dernier numéro sorti entre la ligne 5 et 130. Pour la formule donnant le résultat pour l'avant avant dernier numéro cela donnerait le numéro 18 si on mettrait la formule aussi à la cellule Y130.

Auriez-vous une idées pour ces 8 formules et dont on puisse les incorporer dans une autre formule ??

Merci

Williams

 

[piga25]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour,

Voir si cela peut convenir.
En AJ130 :

=INDEX(N$5:N130;GRANDE.VALEUR((N$5:N130<>"")*LIGNE(N$5:N130);$AJ$1)-4)

Attention formule matricielle a valider par : Ctrl + Maj + Entrée

Le -4 correspond au nombre de ligne avant la première cellule de la colonne à rechercher

 

[williamss]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour,

Voir si cela peut convenir.
En AJ130 :

=INDEX(N$5:N130;GRANDE.VALEUR((N$5:N130<>"")*LIGNE(N$5:N130);$AJ$1)-4)

Attention formule matricielle a valider par : Ctrl + Maj + Entrée

Le -4 correspond au nombre de ligne avant la première cellule de la colonne à rechercher

Merci Piga25,

cette formule va chercher les valeurs telle comme je cherchais à faire mais comme c'est une formule matricielle comment fait-on pour incorporer une formule matricielle dans une autre formule comme celle de ci-dessous :

= RACINE CARRE ( ( 8e_écart - la moyenne générale des 8 écarts)^2 + ( 7e_écart - la moyenne générale des 8 écarts)^2 + ( 6e_écart - la moyenne générale des 8 écarts)^2 + ( 5e_écart - la moyenne générale des 8 écarts)^2 + ( 4 e_écart - la moyenne générale des 8 écarts)^2 + (3e_écart - la moyenne générale des 8 écarts)^2 + ( 2e_écart - la moyenne générale des 8 écarts)^2 + ( 1e_écart - la moyenne générale des 8 écarts)^2 /8)

où dans cette équation "8e_écart" correspond a ce que la formule

=INDEX(N$5:N130;GRANDE.VALEUR((N$5:N130<>"")*LIGNE(N$5:N130);8)-4)

en matricielle va nous chercher et "la moyenne générale des 8 écarts" serrait remplacé par

SOMMEPROD((LIGNE(N$5:N130)>=GRANDE.VALEUR((N$5:N130<>"")*LIGNE(N$5:N130);8))*1;N$5:N130)/8

??

Merci

Williams

 

[piga25]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour,

Pour moi, je ferais un essai en la validant également en matricielle.

 

[CISCO]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour

Williams, je n'ai pas suivi toute l'histoire, mais bon...
Pour être un peu plus sûr des résultats, essayes de te faire un petit fichier, assez simple pour que tu puisses calculer les résultats à la main, puis essayes avec la formule matricielle, puis clique sur "Formule" et sur "Évaluation de formules", puis regarde ce qui se passe, les calculs intermédiaires, étape par étape.

@ plus

 

[williamss]

Re : Comment aller recherche les X dernière valeur d'une colonne ??

Bonjour,

Si je fais un essais avec que le début de la formule pour voir si avec une formule tout en matricielle cela fonctionne, le résultat est bon.

Donc, si je voudrais mettre toutes cette équation, et en matricielle, par exemple à la cellule AJ131 du fichier Excel que tu as donné Piga25 la formule serrait :

=(((INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2+(INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);7)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2+(INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);6)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2+(INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);5)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2+(INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);4)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2+(INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);3)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2+(INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);2)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2+(INDEX(N$5:N131;GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);1)-4)-(SOMMEPROD((LIGNE(N$5:N131)>=GRANDE.VALEUR((N$5:N131<>"")*LIGNE(N$5:N131);8))*1;N$5:N131)/8))^2)/8)^-2

voilà que l'inconvénient c'est que la formule est trop longue

Auriez vous une idée pour réaliser cette formule malgré sa longueur ??

Merci

Williams