Recherche
Publicités
Question aléatoire
Comment créer une règle de filtrage ?

Publicités
Qui est en ligne
16 Personne(s) en ligne (2 Personne(s) connectée(s) sur Fiches Pratiques)

Utilisateurs: 0
Invités: 16

plus...
SmartSection is developed by The SmartFactory (http://www.smartfactory.ca), a division of INBOX Solutions (http://inboxinternational.com)
Fiches Pratiques > Bureautique > EXCEL : Les fonctions utiles > Les fonctions Recherche et Matrices : DECALER
Les fonctions Recherche et Matrices : DECALER
Publié par Sebastien le 11-08-2007 (95286 lus)
Descriptif : Cette fonction permet de renvoyer une plage de cellules qui correspond à un nombre déterminé de lignes et de colonnes. La fonction DECALER n'a pas pour vocation de décaler physiquement des cellules. Elle permet simplement de renvoyer une référence à une plage de cellules. L'intérêt de cette fonction réside dans le fait qu'elle permet de définir des plages de cellules dont les références pourront être modifiées dynamiquement avec les modifications apportées dans la feuille de calcul Excel.

Syntaxte : DECALER(réf;lignes;colonnes;hauteur;largeur)
  • réf : Correspond à la référence à partir de laquelle le décalage doit être effectué.
  • lignes : Correspond au nombre de lignes de décalage par rapport à la première celulle de la plage réf (la cellule supérieure gauche). Un chiffre positif correspond à un décalage vers le bas, un chiffre négatif correspond à un décalage vers le haut.
  • colonnes : Correspond au nombre de colonnes de décalage par rapport à la première celulle de la plage réf (la cellule supérieure gauche). Un chiffre positif correspond à un décalage vers la droite, un chiffre négatif correspond à un décalage vers la gauche
  • hauteur : Correspond au nombre de lignes de la plage de cellules qui doit être renvoyée par la fonction DECALER.
  • largeur : Correspond au nombre de colonnes de la plage de cellules qui doit être renvoyée par la fonction DECALER.
Note : Si les arguments hauteur ou largeur sont omis, les valeurs par défaut des arguments hauteur et largeur sont celles de l'argument réf.

Pour mieux comprendre l'intérêt de cette fonction, nous allons observer deux exemples concrets d'utilisation.



Premier exemple : Soit un tableau représentant le volume des ventes par période. Le but est de créer un graphique représentant ces ventes. Ce graphique devra se mettre à jour automatiquement lorsque une nouvelle période sera saisie dans le tableau.

Aperçu du tableau d'origine :
Tableau d'origine

Note : La feuille de calcul s'appelle RECAP.



Pour simplifier la compréhension de l'exemple, nous allons définir des noms pour nos plages de cellules. Cela permettra une réutilisation simple de ces plages de cellules dans nos formules.
Pour définir un nom, rendez-vous dans la barre de menu d'Excel et cliquez sur Insertion > Noms > Définir.

Aperçu :
Définir les plages de nom



Dans le champ "Noms dans le classeur", mettre PERIODE pour les données de période (le nom est à définir selon les besoins)
Dans le champ "Fait référence à" : saisir la formule suivante :
=DECALER(RECAP!$A$2;;;NBVAL(RECAP!$A:$A)-1)

Pour la deuxième colonne, le nom sera VENTES et la formule sera :
=DECALER(RECAP!$B$2;;;NBVAL(RECAP!$B:$B)-1)

Plage PERIODE :
réf : RECAP!$A$2
lignes : vide
colonnes : vide
hauteur : NBVAL(RECAP!$A:$A)-1
largeur : omis

Plage VENTES :
réf : RECAP!$B$2
lignes : vide
colonnes : vide
hauteur : NBVAL(RECAP!$B:$B)-1
largeur : omis



Explications concernant les critères de la fonction :
La plage que l'on nomme PERIODE débutera à la cellule A2 et aura un nombre de lignes correspondant au nombre de cellules contenant une valeur dans la colonne A. Pour ne pas tenir compte de la ligne de titre, nous retirons 1 à ce nombre de lignes. Ainsi, à chaque nouvelle ligne ajoutée dans le tableau, la plage nommée PERIODE comprendra une ligne de plus. La plage nommée PERIODE est donc dynamique et évolue au fil des saisies dans le tableau. La plage nommée VENTES aura les mêmes caractéristiques.

Aperçu du graphique avant les modifications des données sources :
Aperçu du résultat



Pour rendre le graphique dynamique, il suffit de saisir dans les données sources du graphique le nom des plages mobiles que nous venons de définir. Ainsi, les Valeurs seront représentées par la plage VENTES et les Etiquettes des abscisses (X) seront représentées par la plage PERIODE.

Aperçu :
Modification des données du graphiques



Une fois les modifications effectuées, le graphique sera mis à jour automatiquement à chaque nouvelle entrée dans le tableau.

Aperçu :
Aperçu du résultat



Deuxième exemple : Nous allons réaliser un autre type de graphique. Nous venons de voir comment créer un graphique dont la représentation se met automatiquement à jour lors de la saisie de nouvelles données dans la feuille de calcul. Nous allons recréer le même type de graphique mais en ajoutant un critère. En effet, ce nouveau graphique devra être un graphique glissant sur trois mois. Cela signifie que le graphique devra afficher seulement les trois dernières périodes saisies dans la feuille de calcul.

Note : Il est évidemment possible de créer un graphique glissant sur une période différente de trois, il suffira simplement d'adapter la formule en fonction des besoins de l'utilisateur.

La conception est identique à celle expliquée ci-dessus. Il suffit simplement d'adapter les formules saisies dans les plages de noms pour que seules les trois dernières périodes soient incluses. Pour résumer, cela revient à faire en sorte que les plages PERIODE et VENTES ne prennent que les trois dernières lignes du tableau de données.

Cela se traduit pas une fonction DECALER conçue de la façon suivante :

Dans le champ "Noms dans le classeur", mettre PERIODE pour les données de période (le nom est à définir selon les besoins)
Dans le champ "Fait référence à" : saisir la formule suivante :
=DECALER(RECAP!$A$2:$A$4;NBVAL(RECAP!$A$2:$A$65535)-3;;3;1)

Pour la deuxième colonne, le nom sera VENTES et la formule sera :
=DECALER(RECAP!$B$2:$B$4;NBVAL(RECAP!$B$2:$B$65535)-3;;3;1)

Aperçu :
Saisie des noms de plages


Soit :

Plage PERIODE :
réf : RECAP!$A$2:$A$4
lignes : NBVAL(RECAP!$A$2:$A$65535)-3
colonnes : vide
hauteur : 3
largeur : 1


Plage VENTES :
réf : RECAP!$B$2:$B$4
lignes : NBVAL(RECAP!$B$2:$B$65535)-3
colonnes : vide
hauteur : 3
largeur : 1



Explications concernant les critères de la fonction :
La plage que l'on nomme PERIODE aura pour référence la plage allant de la cellule A2 à la cellule A4. En fonction du nombre de lignes dans le tableau, il faut effectuer un décalage étant égal au nombre de lignes contenant des données dans la plage allant de la cellule A2 à la cellule A65535. Nous retirons trois à ce nombre pour obtenir les trois dernières cellules du tableau.
La plage de destination devra avoir une hauteur de trois lignes pour une seule colonne de largeur.

Aperçu du tableau :
Aperçu du tableau

Reprenons les explications concernant les critères de la fonction et appliquons les au tableau ci-dessus. La plage PERIODE correspond à la plage de cellules A2:A4. Nous allons effectuer un décalage de 3 lignes (nombre de lignes non vides dans la plage A2:A65535) et nous retirons 3 à ce nombre. 3-3 = 0 donc nous ne faisons aucun décalage. En effet, le tableau ne contient que trois périodes, il n'y a donc pas de décalage à effectuer.

Ajoutons une ligne à notre tableau.

Aperçu du graphique dynamique glissant :
Aperçu du tableau

Reprenons les explications concernant les critères de la fonction et appliquons les au tableau ci-dessus. La plage PERIODE correspond à la plage de cellules A2:A4. Nous allons effectuer un décalage de 4 lignes (nombre de lignes dans la plage A2:A65535) et nous retirons 3 à ce nombre. 4-3 = 1. Nous faisons donc un decalage de 1 ligne par rapport à la cellule A2. Notre première cellule sera donc la cellule A3. Notre plage de destination fait 3 lignes pour 1 colonne. La plage PERIODE correspondra donc à la plage A3:A5.
Nous obtenons donc un graphique comprenant les trois dernières lignes du tableau. Le graphique visible sur la capture ci-dessus affiche bien les données des trois dernières périodes saisies dans le tableau (Février, Mars et Avril).

Autres articles dans cette catégorie Date de publication clics
Les fonctions Informations : ESTERREUR
10-03-2014
101969
Les fonctions Informations : EST.PAIR
20-02-2014
15914
Les fonctions Informations : EST.IMPAIR
20-02-2014
12392
Les fonctions Math et Trigo : ARRONDI.AU.MULTIPLE
20-02-2014
18818
Les fonctions Math et Trigo : PLANCHER
10-10-2012
20377
Les fonctions Math et Trigo : PLAFOND
10-10-2012
14579
Les fonctions Date et Heure : DATE
06-09-2010
75662
Les fonctions Date et Heure : DATEVAL
06-09-2010
63436
Les fonctions Date et Heure : DATEDIF
14-09-2008
118620
Les fonctions Recherche et Matrices : DECALER
11-08-2007
95287
Les fonctions Math et Trigo : MOD
16-05-2007
39165
Les fonctions Math et Trigo : ENT
16-05-2007
35629
Les fonctions statistiques : PETITE.VALEUR
29-01-2007
74649
Les fonctions statistiques : GRANDE.VALEUR
29-01-2007
74226
Les fonctions Math et Trigo : TRONQUE
27-01-2007
35157
Les fonctions statistiques : FREQUENCE
23-01-2007
222924
Les fonctions statistiques : NB.SI
07-10-2006
176197
Les fonctions statistiques : NBVAL
07-10-2006
143799
Les fonctions statistiques : NB.VIDE
07-10-2006
45955
Les fonctions statistiques : NB
07-10-2006
63437
Les fonctions mathématiques : SOMME.SI
05-10-2006
129574
Les fonctions Date et Heure : ANNEE
04-10-2006
50737
Les fonctions recherche et matrices : LIGNE
27-09-2006
69287
Les fonctions Math et Trigo : ABS
11-09-2006
26548
Les fonctions date et heure : AUJOURDHUI
11-09-2006
92481
Les fonctions Math et Trigo : ARRONDI
04-09-2006
57762
Les fonctions Math et Trigo : SOMMEPROD
27-08-2006
80338
Les fonctions textes : TEXTE
03-08-2006
125816
Les fonctions textes : SUPPRESPACE
03-08-2006
46569
Les fonctions Math et Trigo : ROMAIN
23-07-2006
209642
Les fonctions textes : CNUM
01-07-2006
76397
Les fonctions textes : REMPLACER
01-07-2006
106453
Les fonctions textes : SUBSTITUE
27-06-2006
87381
Les fonctions Recherche et Matrices : RECHERCHEV
10-06-2006
294321
Les fonctions textes : CHERCHE
03-06-2006
212273
Les fonctions textes : NBCAR
29-05-2006
71815
Les fonctions statistiques : RANG
29-05-2006
97522
Les fonctions Recherche et Matrices : EQUIV
25-05-2006
117436
Les fonctions Recherche et Matrices : INDEX
25-05-2006
92889
Les fonctions statistiques : MAX
23-05-2006
26384
Les fonctions statistiques : MIN
23-05-2006
17121
Les fonctions textes : MINUSCULE
20-05-2006
24441
Les fonctions textes : MAJUSCULE
20-05-2006
25771
Les fonctions textes : NOMPROPRE
19-05-2006
31113
Les fonctions mathématiques : SOUS TOTAL
30-04-2006
126608
Les fonctions mathématiques : SOMME
26-04-2006
31191
Les fonctions statistiques : MOYENNE
26-04-2006
38695
Les fonctions statistiques : MOYENNE REDUITE
26-04-2006
51674
Les fonctions textes : CONCATENER
23-04-2006
148215
Les fonctions textes : DROITE
23-04-2006
50570
Les fonctions textes : STXT
23-04-2006
122198
Les fonctions textes : GAUCHE
23-04-2006
74184
Les fonctions logiques : OU
23-04-2006
259444
Les fonctions logiques : ET
23-04-2006
96326
Les fonctions logiques : SI
23-04-2006
113090
Les commentaires appartiennent à leurs auteurs. Nous ne sommes pas responsables de leur contenu.
Posté Commentaire en débat
Publicités