Recherche
Publicités
Question aléatoire
Comment définir une adresse IP fixe dans Windows Vista ?

Publicités
Qui est en ligne
12 Personne(s) en ligne (5 Personne(s) connectée(s) sur Fiches Pratiques)

Utilisateurs: 0
Invités: 12

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 (88713 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
57514
Les fonctions Informations : EST.PAIR
20-02-2014
10393
Les fonctions Informations : EST.IMPAIR
20-02-2014
8743
Les fonctions Math et Trigo : ARRONDI.AU.MULTIPLE
20-02-2014
14099
Les fonctions Math et Trigo : PLANCHER
10-10-2012
17255
Les fonctions Math et Trigo : PLAFOND
10-10-2012
11299
Les fonctions Date et Heure : DATE
06-09-2010
67877
Les fonctions Date et Heure : DATEVAL
06-09-2010
54147
Les fonctions Date et Heure : DATEDIF
14-09-2008
106340
Les fonctions Recherche et Matrices : DECALER
11-08-2007
88714
Les fonctions Math et Trigo : MOD
16-05-2007
35547
Les fonctions Math et Trigo : ENT
16-05-2007
31938
Les fonctions statistiques : PETITE.VALEUR
29-01-2007
65787
Les fonctions statistiques : GRANDE.VALEUR
29-01-2007
67602
Les fonctions Math et Trigo : TRONQUE
27-01-2007
31316
Les fonctions statistiques : FREQUENCE
23-01-2007
190499
Les fonctions statistiques : NB.SI
07-10-2006
162832
Les fonctions statistiques : NBVAL
07-10-2006
136071
Les fonctions statistiques : NB.VIDE
07-10-2006
42534
Les fonctions statistiques : NB
07-10-2006
59294
Les fonctions mathématiques : SOMME.SI
05-10-2006
119962
Les fonctions Date et Heure : ANNEE
04-10-2006
45366
Les fonctions recherche et matrices : LIGNE
27-09-2006
62313
Les fonctions Math et Trigo : ABS
11-09-2006
23052
Les fonctions date et heure : AUJOURDHUI
11-09-2006
84911
Les fonctions Math et Trigo : ARRONDI
04-09-2006
51012
Les fonctions Math et Trigo : SOMMEPROD
27-08-2006
73963
Les fonctions textes : TEXTE
03-08-2006
119676
Les fonctions textes : SUPPRESPACE
03-08-2006
42153
Les fonctions Math et Trigo : ROMAIN
23-07-2006
204029
Les fonctions textes : CNUM
01-07-2006
66160
Les fonctions textes : REMPLACER
01-07-2006
100754
Les fonctions textes : SUBSTITUE
27-06-2006
72192
Les fonctions Recherche et Matrices : RECHERCHEV
10-06-2006
260072
Les fonctions textes : CHERCHE
03-06-2006
196363
Les fonctions textes : NBCAR
29-05-2006
64939
Les fonctions statistiques : RANG
29-05-2006
87635
Les fonctions Recherche et Matrices : EQUIV
25-05-2006
111244
Les fonctions Recherche et Matrices : INDEX
25-05-2006
87915
Les fonctions statistiques : MAX
23-05-2006
23522
Les fonctions statistiques : MIN
23-05-2006
14746
Les fonctions textes : MINUSCULE
20-05-2006
21760
Les fonctions textes : MAJUSCULE
20-05-2006
23294
Les fonctions textes : NOMPROPRE
19-05-2006
28175
Les fonctions mathématiques : SOUS TOTAL
30-04-2006
113049
Les fonctions mathématiques : SOMME
26-04-2006
28337
Les fonctions statistiques : MOYENNE
26-04-2006
33887
Les fonctions statistiques : MOYENNE REDUITE
26-04-2006
44235
Les fonctions textes : CONCATENER
23-04-2006
142203
Les fonctions textes : DROITE
23-04-2006
47226
Les fonctions textes : STXT
23-04-2006
110575
Les fonctions textes : GAUCHE
23-04-2006
70010
Les fonctions logiques : OU
23-04-2006
240153
Les fonctions logiques : ET
23-04-2006
87671
Les fonctions logiques : SI
23-04-2006
101696
Les commentaires appartiennent à leurs auteurs. Nous ne sommes pas responsables de leur contenu.
Posté Commentaire en débat
Publicités