Fonction DECALER et plage dynamique

Venez découvrir tous les tutoriels, modèles et autres foires aux questions afin de maîtriser rapidement votre suite bureautique favorite.

Modérateur : Vilains modOOs

Règles du forum
Aucune question dans cette section !
Cette section est faite pour présenter les tutoriels. Si vous avez une question sur l'installation, le fonctionnement ou l'utilisation, vous devez poster dans la section du module où se produit le problème.

Ce tutoriel vous a-t-il aidé ou répondu à votre problème ?

Oui
9
100%
Non
0
Aucun vote
En partie
0
Aucun vote
 
Nombre total de votes : 9

Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Fonction DECALER et plage dynamique

Message par Jeff »

Jeff a écrit :Ce tutoriel est sous licence GNU F.D.L. (Licence de Documentation Libre GNU)
en français : http://cesarx.free.fr/gfdlf.html, officiel : http://www.gnu.org/licenses/licenses.html#GPL
Vous êtes libre de le reproduire, le recopier, le réutiliser, l'améliorer, le modifier, le distribuer à condition de lui attribuer les mêmes libertés
et de citer son origine : ForumOpenOffice.org
©2014 Jeff
Présentation
Renvoie la valeur correspondant à un décalage de cellule d'un certain nombre de lignes et de colonnes à partir d'un point de référence donné.
La fonction DECALER ne permet pas seulement renvoyer la valeur correspondant à un décalage de cellule d'un certain nombre de lignes et de colonnes à partir d'un point de référence donné, mais permet également de créer par formule des plages de cellule.

Ainsi, il est possible dynamiquement de choisir une matrice (plage) de cellule.
On parle alors de plages dynamiques (plus rarement appelées « plages glissantes »)

Cette plage de cellule calculée par formule va pouvoir être exploitée dans les fonctions natives de Calc, les listes de validité, le formatage conditionnel, les noms…

Syntaxe :
DECALER(référence;lignes;colonnes;hauteur;largeur)
référence est la référence à partir de laquelle la fonction recherche la nouvelle référence.
En clair, c’est la cellule qui sert de point de départ.

lignes est le nombre de lignes par lequel la référence a été corrigée vers le bas (valeur positive) ou vers le haut (valeur négative).

colonnes (facultatif) est le nombre de colonnes par lequel la référence a été corrigée vers la droite (valeur positive) ou vers la gauche (valeur négative).

hauteur (facultatif) est la hauteur verticale pour une zone qui débute à la nouvelle position de la référence.

largeur (facultatif) est la largeur horizontale pour une zone qui débute à la nouvelle position de la référence.

Index du tutoriel :
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Première et deuxième dimension : Lignes et colonnes

Message par Jeff »

Il suffit d’indiquer les 3 premiers paramètres de la fonction.

Par exemple la formule suivante =DECALER(C10;8;3) va donner comme point de départ la cellule C10, puis il est renseigné 8 lignes de décalage (vers le bas) et 3 colonnes (vers la droite) :
Cliquer sur l’image pour zoomer<br /><br />La cellule A1 contient la formule qui renvoie le contenu de F18
Cliquer sur l’image pour zoomer

La cellule A1 contient la formule qui renvoie le contenu de F18
Le décalage négatif en ligne ou colonne suit la même logique.
Ce décalage peut être renseigné en « dur » dans la formule, ou bien être renseigné par une cellule annexe ; dans tous les cas, on veillera à ne pas « sortir » des limites de la feuille de calcul.
Dans l’exemple suivant, le décalage en colonne est renseigné par la cellule C4, mais si le paramètre mentionné en C4 est erroné, ce paramètre est alors limité par une fonction MAX :
Cliquer sur l’image pour zoomer<br /><br />La cellule A1 contient la formule qui renvoie le contenu de A8
Cliquer sur l’image pour zoomer

La cellule A1 contient la formule qui renvoie le contenu de A8
N’importe quelle fonction retournant un chiffre peut être utilisée pour renseigner le décalage en ligne et/ou en colonne (SOMME, NB.SI, NBVAL, EQUIV etc…) ; de même, si la référence est un texte qui peut être reconnu comme adressage valide, l’usage d’INDIRECT permet d’interpréter ce texte comme référence de point de départ.
Dans l’exemple suivant, la cellule C10 est nommée « Départ » (voir tuto sur les noms), les décalages sont calculés par formule :
Cliquer sur l’image pour zoomer<br /><br />La cellule A1 contient la formule qui renvoie le contenu de A3
Cliquer sur l’image pour zoomer

La cellule A1 contient la formule qui renvoie le contenu de A3
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Troisième dimension : largeur et hauteur

Message par Jeff »

En spécifiant une largeur et/ou une hauteur, la fonction DECALER va alors retourner une plage, une matrice.

Par exemple, à partir de C10, on veut afficher 10 lignes en hauteur, et 2 colonnes en largeur.
DECALER(référence;lignes;colonnes;hauteur;largeur)
La formule qui vient naturellement à l’esprit est :

Code : Tout sélectionner

=DECALER(C10; ; ;10;2)
Validée seulement avec la touche Entrée, cette formule renvoie logiquement l’erreur #VALEUR ; en effet, dès lors que l’on demande une matrice, la validation doit-être matricielle, la formule sera donc validée par Ctrl + Maj + Entrer :
Cliquer sur l’image pour zoomer<br /><br />La validation matricielle en A1 renvoie le contenu de la plage C10:D19
Cliquer sur l’image pour zoomer

La validation matricielle en A1 renvoie le contenu de la plage C10:D19
Deux points à mentionner :
  1. Lorsqu’aucun décalage de ligne ou de colonne n’est mentionné, la cellule de départ fait naturellement partie de la sélection.
  2. Les paramètres ligne et colonne peuvent être omis si les autres paramètres indiqués suffisent au calcul de la matrice (dans le cas contraire, ces paramètres peuvent donc être forcés à 0)
Il est donc possible :
  1. De calculer (ou renseigner) un décalage en ligne et/ou en colonne vers le haut, le bas , la gauche, la droite.
  2. De calculer (ou renseigner) une largeur et une hauteur de plage de cellule

    :arrow: c’est donc bien l’usage de plage dynamique ! :)
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Fonctions natives et plage dynamique

Message par Jeff »

Toutes les fonctions de Calc nécessitant une plage (c’est-à-dire quasiment toutes les fonctions ;)) peuvent recevoir une matrice dynamique pour plage.

L’exemple suivant propose de fournir une matrice dynamique à une fonction de recherche.

Lorsque DECALER fournie une plage à une fonction, telle que RECHERCHEV utilisée avec une plage dynamique, la fonction n’a absolument pas besoin de validation matricielle, DECALER fournie une plage de la même façon que l’on peut soi-même indiquer une plage de données.

Le fil de discussion servant de support : [Résolu] RechercheV sur plusieurs matrices potentielles

Comme toute fonction de recherche nécessitant une plage, RECHERCHEV peut utiliser une plage dynamique comme matrice de recherche.

Syntaxe de RECHERCHEV :
=RECHERCHEV(critère_de_recherche;matrice;index;ordre_de_tri)
matrice désigne la plage utilisée pour le critère de recherche et le résultat, c’est cet élément qui va pouvoir être dynamique.

Dans l’exemple suivant, l’utilisateur va renseigner en cellule A1 la matrice voulue pour la recherche, la fonction DECALER va alors créer la matrice correspondante, pour cet exemple, il s’agit de « Matrice3 ».
La valeur recherchée dans la matrice choisie est indiquée en A4, si cette valeur n’existe pas en G13:G16, c’est la valeur immédiatement inférieure qui est retenue pour retourner le contenu présent en H13:H16.

Par exemple, si A1 contient le texte « Matrice3 », DECALER va devoir renvoyer la matrice G13:H16.
Dans cette matrice, la valeur recherchée est 25 ; comme cette valeur n’existe pas, c’est la valeur 21 qui est retenue, RECHERCHEV doit alors retourner comme résultat « B »
Cliquer sur l’image pour zoomer
Cliquer sur l’image pour zoomer
=RECHERCHEV(critère_de_recherche;matrice;index;ordre_de_tri)
  • critère_de_recherche : A4
  • matrice : Création de la plage dynamique :
    • DECALER(référence;lignes;colonnes;hauteur;largeur)
      • référence : F1 va être le point de départ du décalage
      • lignes : pour savoir de combien de lignes il faut décaler la référence, on peut par exemple rechercher le terme présent en A1 dans la plage F1:F13, ce qui donne :

        Code : Tout sélectionner

        EQUIV(A1;F1:F13)
        Pour le texte recherché « Matrice3 », EQUIV retourne 13, car c’est à la treizième ligne que ce texte est trouvé ; or, il ne faut pas décaler notre référence de 13 ligne, celle-ci étant déjà en ligne 1, il n’est pas souhaité d’obtenir un décalage jusqu’en ligne 14, le résultat d’EQUIV doit alors être ajusté en ôtant 1 au résultat.
        Dans ce cas présent, les textes sont dans l’ordre alphabétique, ce qui risque de ne pas être toujours le cas, il faut donc préciser le dernier paramètre d’EQUIV à 0 pour trouver le bon texte même si l’ordre alphabétique n’est pas respecté.
        Le décalage en ligne va donc être :

        Code : Tout sélectionner

        EQUIV(A1;F1:F13;0)-1
        La référence de départ F1 est donc maintenant décalée de 12 lignes, et renvoie le contenu de F13
      • colonnes : il suffit maintenant de se décaler d’une colonne pour obtenir le début de la matrice de recherche.
        Le paramètre colonne est donc à 1.
        La référence de départ F1, décalée de 12 lignes donne le contenu de F13, décalée d’une colonne donne le contenu de G13
      • hauteur : la matrice de recherche, quelle qu’elle soit dans cet exemple, a une hauteur de 4 lignes.
        Le paramètre hauteur est donc à 4
        La référence de départ F1, décalée de 12 lignes donne le contenu de F13, décalée d’une colonne donne le contenu de G13, sur une hauteur de 4 lignes donne le contenu de G13:G16.
      • largeur : la matrice de recherche, quelle qu’elle soit dans cet exemple, a une largeur de 2 colonnes.
        Le paramètre largeur est donc à 2
        La référence de départ F1, décalée de 12 lignes donne le contenu de F13, décalée d’une colonne donne le contenu de G13, sur une hauteur de 4 lignes donne le contenu de G13:G16, sur une largeur de 2 colonnes donne le contenu de G13:H16.
    La matrice dynamique dans laquelle la recherche sera effectuée est donc DECALER(F1;EQUIV(A1;F1:F13;0)-1;1;4;2)
    Pour prévenir tout risque d’erreur, dans ce cas présent, toutes les références peuvent être absolues (aucune incrémentation de ligne et/ou de colonne souhaitée(s)), la formule devient :

    Code : Tout sélectionner

    DECALER($F$1;EQUIV($A$1;$F$1:$F$13;0)-1;1;4;2)
  • index : il est demandé à RECHERCHEV de chercher dans la seconde colonne, ce paramètre est donc à 2.
  • ordre_de_tri : paramètre non renseigné, car on ne cherche pas le critère exact dans une liste qui est triée.
    :arrow: la formule finale de RECHERCHEV est :

    Code : Tout sélectionner

    =RECHERCHEV(A4;DECALER($F$1;EQUIV($A$1;$F$1:$F$13;0)-1;1;4;2);2)
Cliquer sur l’image pour zoomer
Cliquer sur l’image pour zoomer
Le classeur exemple est en fin de fil, onglet 'Plage_dyn_Recherche'
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Plage dynamique nommée

Message par Jeff »

La fonction DECALER avec ses 5 paramètres alourdit l’aspect visuel des formules.
Comme mentionné dans le tutoriel sur l’usage des noms dans Calc, il est bien sûr tout à fait possible d’attribuer un nom à une fonction DECALER, pour gagner en lisibilité.
Par exemple, la formule du post précédent =RECHERCHEV(A4;DECALER($F$1;EQUIV($A$1;$F$1:$F$13;0)-1;1;4;2);2) est plus explicite ainsi :

Code : Tout sélectionner

=RECHERCHEV(A4;matrice_de_recherche;2)
À noter que le dialogue d’insertion des noms ne permet pas de valider matriciellement celui-ci, mais bien entendu, Calc interprète correctement l’aspect matriciel de la formule.
Cliquer sur l’image pour zoomer
Cliquer sur l’image pour zoomer

Le classeur exemple est en fin de fil, onglet 'Plage_dyn_nommee_Recherche'
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Une liste de validité obtenue avec une plage dynamique

Message par Jeff »

Le tutoriel sur les liste déroulante est disponible en cliquant ici

À noter que le dialogue de validité ne permet pas de valider matriciellement la matrice dynamique, mais bien entendu, Calc interprète correctement l’aspect matriciel de la formule.

Dans l’exemple suivant, on souhaite obtenir la plage correspondant au texte saisi en A1 :
Cliquer sur l’image pour zoomer
Cliquer sur l’image pour zoomer

DECALER(référence;lignes;colonnes;hauteur;largeur)
  • référence : pour peu que l’on décale correctement, n’importe quelle cellule peut faire office de point de départ ; pour éviter d’avoir à corriger les différents éléments calculés, la référence retenue est $B$2.
  • lignes : paramètre superflu, toutes les données commencent en ligne 2, tout comme la référence.
    Ce paramètre n’est donc pas renseigné.
  • colonnes : Tout comme l’exemple avec RECHERCHEV, le plus simple est à nouveau d’employer EQUIV pour chercher la colonne voulue.
    EQUIV va chercher le contenu de A1, dans la matrice C1:G1 :
    EQUIV($A$1;$C$1:$G$1;0)
  • hauteur : On pourrait se contenter d’indiquer un nombre de ligne maximal, mais autant calculer la hauteur ;) :
    partant du principe que la matrice de données n’excédera jamais la ligne 15, la formule (matricielle ! ;)) qui permet de trouver la ligne maximale renseignée dans la plage C2:G15 est :

    Code : Tout sélectionner

    MAX(($C$2:$G$15<>"")*COLONNE($C$2:$G$15))
    Cette formule seule, validée matriciellement renvoie 7 (le dernier indice de ligne renseigné dans l’exemple), il faut donc appliquer une correction de -1 concernant la hauteur de plage.
  • largeur : Paramètre non renseigné, la largeur est donc de 1 colonne.
La fonction DECALER avec ses arguments est donc :

Code : Tout sélectionner

=DECALER($B$2;;EQUIV($A$1;$C$1:$G$1;0);MAX(($C$2:$G$15<>"")*COLONNE($C$2:$G$15))-1)
C’est donc cette formule qui faut renseigner comme plage de cellule source de données dans le menu de validité :
Cliquer sur l’image pour zoomer
Cliquer sur l’image pour zoomer
La liste de validité, ici en A9, est alors parfaitement fonctionnelle :
Cliquer sur l’image pour zoomer
Cliquer sur l’image pour zoomer


Le classeur exemple est en fin de fil, onglet 'Plage_dyn_par_validite'

Un autre exemple de liste de validité, obtenue avec une plage dynamique, est exposé par gerard24 :
Affichage d'une liste partielle

Pour gagner encore en lisibilité, on peut également combiner les listes, et l’usage des noms exposé dans le post précédent ; la liste sera alors basée sur le nom comprenant la fonction DECALER.

À noter qu’une liste de validité, par son côté « affichage dynamique », est l’idéal pour contrôler le bon fonctionnement des différents paramètres, à l’opposé d’une validation matricielle de la même formule DECALER, qui oblige une certaine gymnastique à chaque adaptation de paramètre (sélection de la matrice, suppression de l’aspect matriciel, ressaisie de formule).
C’est donc une solution à privilégier lors de l’élaboration de plage dynamique.
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Les limites d’emploi des plages dynamiques

Message par Jeff »

Actuellement, avec Apache OpenOffice 4.0.1 et le format ODF 1.2 étendu :
  • Lorsque l’on valide matriciellement la fonction DECALER avec ses paramètres de largeur et/ou longueur, tout ajout/suppression de ligne et/ou colonne ne sera plus pris en compte ; il conviendra alors de ressaisir et valider matriciellement la fonction.
Une fonction DECALER, nommée ou non, ne peut pas servir de plage dynamique dans les cas suivants : Hauteur ou largeur négative, dans le cas où la référence est en fin de plage :
  • Logiquement, les décalages en hauteur et largeur ne peuvent pas être négatifs, bien que la fonction employée telle quel par AOO 4.0.1 avec le format ODF1.2 étendu ne renvoie pas d’erreur. Certains tableurs reconnaissent une telle syntaxe (issue 124448)
(si ces fonctionnalités vous intéressent, merci d’apporter vos votes sur les issues mentionnées)

Classeur-exemple ayant servit à la conception de ce tutoriel :
Tuto_Decaler_plag_dyn.ods
(13.53 Kio) Téléchargé 1699 fois
Aucune question dans ce fil (voir bandeau rouge en haut de page), les questions sur les plages dynamiques et/ou la fonction DECALER sont à poser en section tableur
Répondre