Recherche
Publicités
Question aléatoire
Comment désactiver le service d'affichage des messages ?

Publicités
Qui est en ligne
30 Personne(s) en ligne (10 Personne(s) connectée(s) sur Fiches Pratiques)

Utilisateurs: 0
Invités: 30

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 (86535 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
47777
Les fonctions Informations : EST.PAIR
20-02-2014
9404
Les fonctions Informations : EST.IMPAIR
20-02-2014
7926
Les fonctions Math et Trigo : ARRONDI.AU.MULTIPLE
20-02-2014
12837
Les fonctions Math et Trigo : PLANCHER
10-10-2012
16504
Les fonctions Math et Trigo : PLAFOND
10-10-2012
10714
Les fonctions Date et Heure : DATE
06-09-2010
65759
Les fonctions Date et Heure : DATEVAL
06-09-2010
52136
Les fonctions Date et Heure : DATEDIF
14-09-2008
103224
Les fonctions Recherche et Matrices : DECALER
11-08-2007
86536
Les fonctions Math et Trigo : MOD
16-05-2007
34785
Les fonctions Math et Trigo : ENT
16-05-2007
31073
Les fonctions statistiques : PETITE.VALEUR
29-01-2007
63778
Les fonctions statistiques : GRANDE.VALEUR
29-01-2007
65794
Les fonctions Math et Trigo : TRONQUE
27-01-2007
30423
Les fonctions statistiques : FREQUENCE
23-01-2007
182273
Les fonctions statistiques : NB.SI
07-10-2006
158727
Les fonctions statistiques : NBVAL
07-10-2006
134291
Les fonctions statistiques : NB.VIDE
07-10-2006
41706
Les fonctions statistiques : NB
07-10-2006
58063
Les fonctions mathématiques : SOMME.SI
05-10-2006
115664
Les fonctions Date et Heure : ANNEE
04-10-2006
44022
Les fonctions recherche et matrices : LIGNE
27-09-2006
60397
Les fonctions Math et Trigo : ABS
11-09-2006
22291
Les fonctions date et heure : AUJOURDHUI
11-09-2006
83167
Les fonctions Math et Trigo : ARRONDI
04-09-2006
49533
Les fonctions Math et Trigo : SOMMEPROD
27-08-2006
72584
Les fonctions textes : TEXTE
03-08-2006
117497
Les fonctions textes : SUPPRESPACE
03-08-2006
41187
Les fonctions Math et Trigo : ROMAIN
23-07-2006
202822
Les fonctions textes : CNUM
01-07-2006
64129
Les fonctions textes : REMPLACER
01-07-2006
98806
Les fonctions textes : SUBSTITUE
27-06-2006
70014
Les fonctions Recherche et Matrices : RECHERCHEV
10-06-2006
246780
Les fonctions textes : CHERCHE
03-06-2006
191148
Les fonctions textes : NBCAR
29-05-2006
63300
Les fonctions statistiques : RANG
29-05-2006
85134
Les fonctions Recherche et Matrices : EQUIV
25-05-2006
109026
Les fonctions Recherche et Matrices : INDEX
25-05-2006
86477
Les fonctions statistiques : MAX
23-05-2006
22812
Les fonctions statistiques : MIN
23-05-2006
14261
Les fonctions textes : MINUSCULE
20-05-2006
21148
Les fonctions textes : MAJUSCULE
20-05-2006
22713
Les fonctions textes : NOMPROPRE
19-05-2006
27452
Les fonctions mathématiques : SOUS TOTAL
30-04-2006
109445
Les fonctions mathématiques : SOMME
26-04-2006
27677
Les fonctions statistiques : MOYENNE
26-04-2006
32727
Les fonctions statistiques : MOYENNE REDUITE
26-04-2006
42105
Les fonctions textes : CONCATENER
23-04-2006
140662
Les fonctions textes : DROITE
23-04-2006
46403
Les fonctions textes : STXT
23-04-2006
106958
Les fonctions textes : GAUCHE
23-04-2006
68931
Les fonctions logiques : OU
23-04-2006
233095
Les fonctions logiques : ET
23-04-2006
84117
Les fonctions logiques : SI
23-04-2006
98442
Les commentaires appartiennent à leurs auteurs. Nous ne sommes pas responsables de leur contenu.
Posté Commentaire en débat
Publicités