Recherche
Publicités
Question aléatoire
Comment afficher la commande Exécuter dans le menu Démarrer de Windows Vista ?

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

Utilisateurs: 0
Invités: 11

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 (86907 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
49383
Les fonctions Informations : EST.PAIR
20-02-2014
9587
Les fonctions Informations : EST.IMPAIR
20-02-2014
8087
Les fonctions Math et Trigo : ARRONDI.AU.MULTIPLE
20-02-2014
13079
Les fonctions Math et Trigo : PLANCHER
10-10-2012
16638
Les fonctions Math et Trigo : PLAFOND
10-10-2012
10795
Les fonctions Date et Heure : DATE
06-09-2010
66137
Les fonctions Date et Heure : DATEVAL
06-09-2010
52457
Les fonctions Date et Heure : DATEDIF
14-09-2008
103710
Les fonctions Recherche et Matrices : DECALER
11-08-2007
86908
Les fonctions Math et Trigo : MOD
16-05-2007
34915
Les fonctions Math et Trigo : ENT
16-05-2007
31210
Les fonctions statistiques : PETITE.VALEUR
29-01-2007
64122
Les fonctions statistiques : GRANDE.VALEUR
29-01-2007
66099
Les fonctions Math et Trigo : TRONQUE
27-01-2007
30575
Les fonctions statistiques : FREQUENCE
23-01-2007
183921
Les fonctions statistiques : NB.SI
07-10-2006
159504
Les fonctions statistiques : NBVAL
07-10-2006
134643
Les fonctions statistiques : NB.VIDE
07-10-2006
41851
Les fonctions statistiques : NB
07-10-2006
58366
Les fonctions mathématiques : SOMME.SI
05-10-2006
116470
Les fonctions Date et Heure : ANNEE
04-10-2006
44215
Les fonctions recherche et matrices : LIGNE
27-09-2006
60727
Les fonctions Math et Trigo : ABS
11-09-2006
22398
Les fonctions date et heure : AUJOURDHUI
11-09-2006
83464
Les fonctions Math et Trigo : ARRONDI
04-09-2006
49769
Les fonctions Math et Trigo : SOMMEPROD
27-08-2006
72795
Les fonctions textes : TEXTE
03-08-2006
117919
Les fonctions textes : SUPPRESPACE
03-08-2006
41374
Les fonctions Math et Trigo : ROMAIN
23-07-2006
203015
Les fonctions textes : CNUM
01-07-2006
64452
Les fonctions textes : REMPLACER
01-07-2006
99169
Les fonctions textes : SUBSTITUE
27-06-2006
70350
Les fonctions Recherche et Matrices : RECHERCHEV
10-06-2006
248973
Les fonctions textes : CHERCHE
03-06-2006
191997
Les fonctions textes : NBCAR
29-05-2006
63565
Les fonctions statistiques : RANG
29-05-2006
85574
Les fonctions Recherche et Matrices : EQUIV
25-05-2006
109433
Les fonctions Recherche et Matrices : INDEX
25-05-2006
86739
Les fonctions statistiques : MAX
23-05-2006
22945
Les fonctions statistiques : MIN
23-05-2006
14337
Les fonctions textes : MINUSCULE
20-05-2006
21230
Les fonctions textes : MAJUSCULE
20-05-2006
22807
Les fonctions textes : NOMPROPRE
19-05-2006
27616
Les fonctions mathématiques : SOUS TOTAL
30-04-2006
110019
Les fonctions mathématiques : SOMME
26-04-2006
27804
Les fonctions statistiques : MOYENNE
26-04-2006
32905
Les fonctions statistiques : MOYENNE REDUITE
26-04-2006
42443
Les fonctions textes : CONCATENER
23-04-2006
140913
Les fonctions textes : DROITE
23-04-2006
46541
Les fonctions textes : STXT
23-04-2006
107570
Les fonctions textes : GAUCHE
23-04-2006
69182
Les fonctions logiques : OU
23-04-2006
234485
Les fonctions logiques : ET
23-04-2006
84741
Les fonctions logiques : SI
23-04-2006
98999
Les commentaires appartiennent à leurs auteurs. Nous ne sommes pas responsables de leur contenu.
Posté Commentaire en débat
Publicités