Recherche
Publicités
Question aléatoire
Comment ajouter un compte utilisateur supplémentaire dans Windows Vista ?

Publicités
Qui est en ligne
41 Personne(s) en ligne (17 Personne(s) connectée(s) sur Fiches Pratiques)

Utilisateurs: 0
Invités: 41

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 (87187 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
50718
Les fonctions Informations : EST.PAIR
20-02-2014
9719
Les fonctions Informations : EST.IMPAIR
20-02-2014
8199
Les fonctions Math et Trigo : ARRONDI.AU.MULTIPLE
20-02-2014
13290
Les fonctions Math et Trigo : PLANCHER
10-10-2012
16738
Les fonctions Math et Trigo : PLAFOND
10-10-2012
10864
Les fonctions Date et Heure : DATE
06-09-2010
66454
Les fonctions Date et Heure : DATEVAL
06-09-2010
52722
Les fonctions Date et Heure : DATEDIF
14-09-2008
104135
Les fonctions Recherche et Matrices : DECALER
11-08-2007
87188
Les fonctions Math et Trigo : MOD
16-05-2007
35024
Les fonctions Math et Trigo : ENT
16-05-2007
31332
Les fonctions statistiques : PETITE.VALEUR
29-01-2007
64407
Les fonctions statistiques : GRANDE.VALEUR
29-01-2007
66379
Les fonctions Math et Trigo : TRONQUE
27-01-2007
30686
Les fonctions statistiques : FREQUENCE
23-01-2007
185373
Les fonctions statistiques : NB.SI
07-10-2006
160078
Les fonctions statistiques : NBVAL
07-10-2006
134960
Les fonctions statistiques : NB.VIDE
07-10-2006
41989
Les fonctions statistiques : NB
07-10-2006
58553
Les fonctions mathématiques : SOMME.SI
05-10-2006
117063
Les fonctions Date et Heure : ANNEE
04-10-2006
44399
Les fonctions recherche et matrices : LIGNE
27-09-2006
60966
Les fonctions Math et Trigo : ABS
11-09-2006
22514
Les fonctions date et heure : AUJOURDHUI
11-09-2006
83690
Les fonctions Math et Trigo : ARRONDI
04-09-2006
50006
Les fonctions Math et Trigo : SOMMEPROD
27-08-2006
72998
Les fonctions textes : TEXTE
03-08-2006
118240
Les fonctions textes : SUPPRESPACE
03-08-2006
41517
Les fonctions Math et Trigo : ROMAIN
23-07-2006
203198
Les fonctions textes : CNUM
01-07-2006
64728
Les fonctions textes : REMPLACER
01-07-2006
99446
Les fonctions textes : SUBSTITUE
27-06-2006
70657
Les fonctions Recherche et Matrices : RECHERCHEV
10-06-2006
250855
Les fonctions textes : CHERCHE
03-06-2006
192701
Les fonctions textes : NBCAR
29-05-2006
63792
Les fonctions statistiques : RANG
29-05-2006
85969
Les fonctions Recherche et Matrices : EQUIV
25-05-2006
109806
Les fonctions Recherche et Matrices : INDEX
25-05-2006
86949
Les fonctions statistiques : MAX
23-05-2006
23052
Les fonctions statistiques : MIN
23-05-2006
14408
Les fonctions textes : MINUSCULE
20-05-2006
21328
Les fonctions textes : MAJUSCULE
20-05-2006
22902
Les fonctions textes : NOMPROPRE
19-05-2006
27714
Les fonctions mathématiques : SOUS TOTAL
30-04-2006
110482
Les fonctions mathématiques : SOMME
26-04-2006
27898
Les fonctions statistiques : MOYENNE
26-04-2006
33097
Les fonctions statistiques : MOYENNE REDUITE
26-04-2006
42777
Les fonctions textes : CONCATENER
23-04-2006
141141
Les fonctions textes : DROITE
23-04-2006
46659
Les fonctions textes : STXT
23-04-2006
108030
Les fonctions textes : GAUCHE
23-04-2006
69330
Les fonctions logiques : OU
23-04-2006
235557
Les fonctions logiques : ET
23-04-2006
85281
Les fonctions logiques : SI
23-04-2006
99468
Les commentaires appartiennent à leurs auteurs. Nous ne sommes pas responsables de leur contenu.
Posté Commentaire en débat
Publicités