Fonction: pointage d'une cellule mobile dans une formule
Bonjour,
Je ne suis pas un expert d'Excel, je m'en sers rarement, je n'ai jamais touché à une macro de ma vie.
Cependant, je dois réaliser un travail dans lequel j'ai plusieurs dizaines de milliers de données à traiter.
Mes données sont toutes sur la même feuille, regroupées par blocs.
Chaque bloc est séparé par 4 lignes sur lesquelles j'ai sur la cellule de l'une de ces lignes la moyenne des valeurs du bloc, sur celle en dessous l'écart-type, sur celle encore en dessous la borne supérieure, et celle encore en dessous la borne inférieure.
(Donc j'ai la moyenne, écart-type, borne + et borne - sur les 4 lignes qui séparent chacun de mes blocs)
Pour chaque bloc, je purifie les données dans une colonne juxtaposée de la façon suivante:
=SI(ET(N2<N$36;N2>N$37);N2;"")
N2 = la valeur en question sur la ligne 2
N$36 = borne + du bloc en question
N$37 = borne - du bloc en question
Autrement dit, je veux que pour chaque donnée, si celle-ci est en dehors des bornes, la case affichée soit vide.
Ce sont des blocs de 20 lignes, donc je fais glisser la première ligne sur les 19 suivantes et c'est bon (jusque là pas de problème).
Le problème, c'est que si je copie/colle les 20 cellules de mon premier bloc dans l'espace alloué au même traitement pour mon second bloc, le pointage des bornes se fait toujours sur N36 et N37 (les bornes du premier bloc), alors que je voudrais qu'il se fasse sur celles du 2ème bloc (qui se trouvent dans la 22ème et la 23ème ligne suivante).
En gros, je voudrais qu'à chaque copié/collé, il y ait un pointage des bornes qui se décale de 22 et 23 lignes à chaque fois).
Dans l'absolu, il me suffirait de changer manuellement dans la formule le numéro de ligne de chaque borne pour tous les blocs, mais il y a un nombre astronomique de blocs donc ça me prendrait un temps fou.
Auriez-vous une astuce accessible pour un profane d'Excel comme moi SVP?
Bonjour
Si tu envoyais sur une feuille Excel 3 ou 4 de ces tableaux pour juger de la mise en forme et te donner cette formule DECALER que tu attends
Cordialement
Bonjour
Quand tu fais le copier/coller essayes sans avoir la ligne dans ta formule de figée
Enlèves les $
=SI(ET(N2<N$36;N2>N$37);N2;"")
devient
=SI(ET(N2<N36;N2>N37);N2;"")
Oui, j'avais déjà essayé en fait.
Mais si je fais ça, lorsque je copie la formule au sein d'un même bloc, à chaque ligne le pointage de cellule descend d'un cran, or il faut qu'il reste le même au sein d'un même bloc.
Bonsoir
Désolé pour ma réponse trop rapide
mais il y a des trucs que je ne comprends pas dans ton énoncé
D'après ce que tu écris le premier bloc commence à la ligne 2
Chaque bloc fait 20 lignes ---> donc 2 à 21
Ton max et ton Min en 36 et 37
Qu'y a t' il entre 22 et 33 ?
Ton deuxième bloc commence après le Min du bloc 1 --->Ligne 38
Bloc de 20 lignes de 38 à 57
ligne 58 = Moyenne
ligne 59 = Ecart-type
ligne 60 = Max
ligne 61 = Min
mais
il y ait un pointage des bornes qui se décale de 22 et 23 lignes
ligne Max (bloc 1) = ligne 36 + 22 =58 ---> Moyenne bloc 2
ligne Min (bloc 1) = Ligne 37 + 23 =60 ---> Max bloc 2
Le mieux c'est de mettre en ligne un fichier
A suivre
Oui je me suis trompé, les blocs sont de 32 lignes (dans ma tête j'étais encore dans un autre traitement quand j'ai rédigé mon message).
Je joins un fichier avec exemple (parce que le fichier complet dépasse de loin 200ko).
Sur le fichier, j'ai appliqué ma formule sur le premier bloc dans la colonne O. Je voudrais donc pouvoir l'appliquer par copier/coller dans les blocs suivants en pointant la case des bornes de chaque bloc.
Bonjour
Un essai avec une formule compliquée (je ne sais pas faire simple
A tester
Ouhla, merci pour cette belle formule.
Ca semble en bonne voie, toutefois dans les colonnes avec les cellules des bornes reportées, quand je copie/colle en faisant glisser, le pointage se fait 2 lignes trop bas, ce qui fait un décalage de 2 lignes supplémentaires à chaque bloc suivant.
Plus concrètement: le N36 et N37 est pris comme référence jusqu'à la ligne 39 (les 2 premières lignes du bloc 2), le N74 N 75 est pris en référence jusqu'à la ligne 77 (les 4 premières lignes du bloc 3), N112 et N113 jusqu'à la ligne 115 (les 6 premières lignes du bloc 4), etc...
Bonjour
Ne t'occupes pas des formules en colonnes R et S (ce ne sont que des tests)
Ta formule en colonne Q est la seule à copier
Pour info :
Modifies les formules en R et S
Remplaces 38 par 36
Et bien, à première vue, ça m'a l'air de fonctionner parfaitement!
Je te remercie beaucoup, ça me sauve la vie!
j'espère que je n'aurais pas un autre traitement dans le même genre à faire parce que je serais bien incapable de refaire une telle formule.
Merci beaucoup!
Bonsoir je suis nouveau et rebondis sur le sujet des bornes flottantes. Mon probleme est legerement different j'ai un mur de case avec fonctions diverses et variées concernant une base de données gréée comme suis :
nous diront ligne 5 = année 2008 et toute les lignes qui suivent représentent l'activité de l'année (plus il y a de ligne plus il y a eu d'activité), nous dirons ligne 83 = année 2009 (par exemple egalement) et ainsi de suite il nous fallait mettre toutes les années ensembles les une en dessus des autres chacune suivies de lignes activité. Nous aurons par ailleurs ligne 125 pour 2010 et 160 pour 2011 bref il n'y a pas de regle ni de bloc fixe et si parfois on change via insert ou supression de ligne...
Là ou il y a impasse c'est que la plupart de ces cellules colonnes par colonne font reference aux lignes années comme delimitation de calculs par exemple pour une plage concernant les bornes de 2009 nous dirons que l'activité va de la ligne 84 à 124 incluse
Dans le cas present le souci est que des confrères veulent le même tableau mais leur activité est dissemblable ce qui veut dire qu'un tel aura 2009 en ligne 52 un autre 2011 en ligne 200 et cela aura une enorme incidence sur la foultitude de cellule colonne par collonne chacune remplies de formules.
Je ne connais pas s'il existe un moyen simple d'identifier les lignes année par pourquoi pas imaginer un bouton de paramettrage type deroulant me permettant de dire année 2011 = ligne 180 et toute mes fonctions qui se mettraient à jour automatiquement (on peut rever) oh dieux excel qu'as tu révu dans mon cas ?
Merci de votre aides...
Bonjour
sans support pas facile a comprendre
Bonne journée
Bonjour,
A présent, j'aurais besoin de complexifier un peu la formule que vous m"avez gentiment concocté.
Je vais essayer d'être clair sur mon problème parce que c'est un peu compliqué à expliquer.
En fait, mes blocs ne comportent plus tous le même nombre de lignes, certains en possèdent toujours 32, mais d'autres en possèdent maintenant 29.
Du coup la formule fonctionne toujours pour mes blocs de 32, mais par pour mes blocs de 29.
En effet, la formule a été conçue pour s'appliquer sur une cellule automatiquement sélectionnée toutes les 36 lignes (parce que j'ai 4 lignes qui sont allouées à des données qui n'appartiennent pas à mes blocs).
Il faudrait donc qu'elle s'applique en sélectionnant soit la cellule située située à la 36ème ligne, soit la cellule située à la 33ème ligne, et ce en fonction du nombre d'items situés dans le bloc.
Pour illustrer visuellement le problème, je prend un exemple:
Bloc 1:
- 842164
- 184515
- 145123
- 123151
- X
- Y
Bloc 2:
- 4896465
- 475464
- X
- Y
Dans une colonne annexe, il faut que les données plus grandes que "X" ou plus petites que "Y" (qui sont des valeurs dépendantes des données de chaque bloc) soient supprimées (remplacées par un champ vierge), les autres devant êtres gardées (recopiées telles quelles).
Je précise que les blocs de 29 lignes et 32 lignes ne sont pas alternés de façon identique (par exemple il peut y avoir 3 blocs de 32 à la suite, puis 5 à la suite, puis 2 à la suite (ça ne suit une loi précise).
Je vous joins un fichier exemple, la formule est à appliquer à la colonne Q (en jaune), à partir de la colonne N (elle doit supprimer les valeurs en dehors des bornes)
Je sais que je commence à demander beaucoup sans macro, mais ça me sauverait une fois de plus la vie.
Merci d'avance.