> Tous les forums > Forum Bureautique
 Décaler de 240 lignes une plage dans une formule à chaque ligne
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
williamsss
  Posté le 14/02/2016 @ 22:25 
Aller en bas de la page 
Petit astucien

Bonjour,

Avec Excel 2007 je tente de voir sur une plage de 240 données par jours quelle est la valeur la plus proche de celle que j'écris en B10. Et idem pour chaque jours donc il faut un décalage de 240 cellules pour ceci.

C'est à dire que pour la formule ci-dessous permettant de savoir quelle est la valeur la plus proche de celle que j'écris en B10

=$B$10+SI(ABS(PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<=" &$B$10))-$B$10)>PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<"&$B$10)+1)-$B$10;PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<"&$B$10)+1)-$B$10;PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<="&$B$10))-$B$10)

il faudrait que la plage de valeurs de cette formule ( données!AG2:AG242 ) soit à chaque fois décalée de 240 lignes, de ligne en ligne.

Comment peut t'on faire pour que cette formule puisse avoir sa plage de données décalées de 240 lignes quand on tire la cellule vers le bas ??

Merci

Williams



Modifié par williamsss le 14/02/2016 22:27
Publicité
ferrand
 Posté le 15/02/2016 à 03:27 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

$Bonjour,

Rien compris à ta formule, mais j'aurais plutôt essayé :

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(données!AG2:AG242-$B$10)=MIN(ABS(données!AG2:AG242-$B$10));LIGNE(données!AG2:AG242);9^9);1))

A valider en matricielle.

Cordialement.



Modifié par ferrand le 15/02/2016 03:28
williamsss
 Posté le 15/02/2016 à 10:39 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien
ferrand a écrit :

$Bonjour,

Rien compris à ta formule, mais j'aurais plutôt essayé :

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(données!AG2:AG242-$B$10)=MIN(ABS(données!AG2:AG242-$B$10));LIGNE(données!AG2:AG242);9^9);1))

A valider en matricielle.

Cordialement.

Bonjour,

Moi non plus je n'ai pas bien compris la formule mais comme je l'avais trouvée sur internet et que ceci fonctionnait alors c'était le principal.

Ta formule donne le même résultat et fonctionne bien mais l'inconvénient c'est que si on tire la cellule vers le bas alors il n'y a pas de décalage de 240 cellules. C'est a dire qu'à la première cellule (F3) où est est mise cette formule c'est dans la plage AG2:AG241 (AG241 et non AG242 comme AG2 est compris dedans les 240 cellules) qu'est recherchée la valeur la plus proche à celle mise dans la cellule B10 puis après à la cellule de dessous (donc F4) avec un décalage de 240 cellules pour la plage de recherche c'est donc dans la plage AG242:AG481 puis après pour F5 c'est dans la recherche s'effectue dans la plage 481:721, etc en se basant toujours la même valeur rentrée ($B$10).

Avec DECALER je n'arrive pas à faire ceci et encore moins avec une formule matricielle comme je ne suis pas si fort sous Excel. Donc voici le fichier http://www.cjoint.com/c/FBpjJ2B1ySp avec dans la page données les valeurs que sur 4 jours au lieu de 365 ou 366 jours.

merci

Williams

ferrand
 Posté le 15/02/2016 à 15:18 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Tu nommes ta plage, ça facilitera un decaler :

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(DECALER(plage;(LIGNE(1:1)-1)*240;)-$B$10)=MIN(ABS(DECALER(plage;(LIGNE(1:1)-1)*240;)-$B$10));LIGNE(DECALER(plage;(LIGNE(1:1)-1)*240;));9^9);1))

Cordialement

williamsss
 Posté le 15/02/2016 à 17:12 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien
ferrand a écrit :

Tu nommes ta plage, ça facilitera un decaler :

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(DECALER(plage;(LIGNE(1:1)-1)*240;)-$B$10)=MIN(ABS(DECALER(plage;(LIGNE(1:1)-1)*240;)-$B$10));LIGNE(DECALER(plage;(LIGNE(1:1)-1)*240;));9^9);1))

Cordialement

N'y aurait-il pas une erreur ??

car en mettant dans la cellule F3 la formule ci-dessous où j'ai nommé la plage "données!$AG$2:$AG$242" par ELEVATION_SOLAIRE voilà que cela me donne la valeur -69,171307 quand je mets en B10 la valeur 13,5040 alors que cela devrait mettre 13,298093 qui est sa valeur la plus dans cette plage de données. De plus cela ne varie pas quand on modifie la valeur dans la cellule B10

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(DECALER(ELEVATION_SOLAIRE;(LIGNE(1:1)-1)*240;)-$B$10)=MIN(ABS(DECALER(ELEVATION_SOLAIRE;(LIGNE(1:1)-1)*240;)-$B$10));LIGNE(DECALER(ELEVATION_SOLAIRE;(LIGNE(1:1)-1)*240;));9^9);1))

Idem si je mets la plage dans la formule au lieu du nom de la plage :

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(DECALER(données!$AG$2:$AG$242;(LIGNE(1:1)-1)*240;)-$B$10)=MIN(ABS(DECALER(données!$AG$2:$AG$242;(LIGNE(1:1)-1)*240;)-$B$10));LIGNE(DECALER(données!$AG$2:$AG$242;(LIGNE(1:1)-1)*240;));9^9);1))

Puis si je tire la cellule vers le bas cela met #VALEUR

merci

Williams



Modifié par williamsss le 15/02/2016 17:16
ferrand
 Posté le 15/02/2016 à 22:35 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Tu es sûr que tu as validé en matricielle.

williamsss
 Posté le 15/02/2016 à 23:02 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien
ferrand a écrit :

Tu es sûr que tu as validé en matricielle.

Quant je ne valide pas en matricielle cela donne comme résultat -69,171307 et si je valide en matricielle comme j'avais essayé aussi suite à ton message précédent alors j'ai #VALEUR.

Donc j'étais surpris et ne comprends pas d'où vient l'erreur.

merci

Williams



Modifié par williamsss le 15/02/2016 23:03
ferrand
 Posté le 16/02/2016 à 02:03 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Peut-être que DECALER ne supporte pas cette utilisation. Dans ce cas je pas pas d'autre idée pour le moment.

Si je trouve un moment, j'essaierai de voir sur ton fichier ce qui se passe (je ne l'ai pas encore ouvert )

A+

ferrand
 Posté le 16/02/2016 à 12:29 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

C'est bien decaler qui renâcle !

On va essayer de contourner en modifiant la définition de la plage :

[avant de définir le nom de plage, se positionner en calculs!F3]

=DECALER(données!$AG$2:$AG$241;(LIGNE(calculs!$F3)-3)*240;)

Et dans la formule tu mets seulement le nom de plage, la position de la formule dans la colonne F fera le reste.

N'oublie pas de valider matriciellement.

Cordialement.

williamsss
 Posté le 16/02/2016 à 14:09 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Suivant ta formule matricielle voici comment il faut faire d'après ce qu"une personne m'a dit pour qu'il puisse y avoir un décalage de 240 cellules à chaque fois :

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(DECALER(données!$AG$2;(LIGNES($1:1)-1)*240;;240)-$B$10)=MIN(ABS(DECALER(données!$AG$2;(LIGNES($1:1)-1)*240;;240)-$B$10));LIGNE(DECALER(données!$AG$2;(LIGNES($1:1)-1)*240;;240));9^9);1))

merci bp de ton aide

Williams

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour participer à la discussion.
Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !
Recevoir PC Astuces par e-mail


La Lettre quotidienne +226 000 inscrits
Avec l'actu, des logiciels, des applis, des astuces, des bons plans, ...

Les bonnes affaires
Une fois par semaine, un récap des meilleurs offres.

Les fonds d'écran
De jolies photos pour personnaliser votre bureau. Une fois par semaine.

Les nouveaux Bons Plans
Des notifications pour ne pas rater les bons plans publiés sur le site.

Les bons plans du moment PC Astuces

Tous les Bons Plans
88,08 €Alimentation modulaire Seasonic Focus Plus Gold 650W (garantie 10 ans) à 88,08 € livrée
Valable jusqu'au 05 Décembre

Amazon Espagne fait une promotion sur l'alimentation Seasonic Focus Plus Gold 650W qui passe à 79,33 €. Comptez 8,75 € pour la livraison en France soit un total de 88,08 € livrée alors qu'on la trouve ailleurs à partir de 105 €. Le niveau frise la perfection avec une magnifique certification 80+ OR et un câblage full modulaire, le tout dans un silence de cathédrale. L'alimentation est garantie 10 ans.

 


> Voir l'offre
97,99 €Disque dur Seagate NAS Iron Wolf 4 To à 97,99 €
Valable jusqu'au 05 Décembre

Amazon propose le disque dur Seagate IronWolf 4 To à 97,99 €. Ce disque dur 3.5 pouces SATA III est adapté aux NAS et ordinateurs qui fonctionnent 24h/24. On le trouve ailleurs autour de 130 €. Disque CMR.


> Voir l'offre
-5 €-5 € dès 20 € pour une première livraison en point de retrait Amazon (selon éligibilité)
Valable jusqu'au 05 Décembre

Amazon propose actuellement 5 euros de remise dès 20 euros d'achats avec le code 5RETRAIT pour une première livraison en point de retrait Amazon. Vous pouvez vérifier votre éligibilité à cette offre en vous rendant sur cette page. Vous trouverez la liste des points de retrait sur cette page.


> Voir l'offre

Sujets relatifs
nb de liens dans une plage appartennant à la même ligne Horiz. ou verti...
utiliser résultat formule dans réf. plage cellules
insérer un retour ligne dans une formule excel
Diviser chaque cellule dans plage cellule par 3
utiliser la formule DECALER dans une formule
Inserer lignes entre chaque ligne
sélectionner une ligne sur deux dans une formule
saisie matricielle d'une série de valeurs dans une plage de cellules
Numérotation inversée des lignes dans excel
formule dans userform
Plus de sujets relatifs à Décaler de 240 lignes une plage dans une formule à chaque ligne
 > Tous les forums > Forum Bureautique