dimanche 21 décembre 2014

Excel - Fonction DECALER

Formule Excel - DECALER - Fonction RECHERCHE et REFERENCE


A quoi peut donc bien servir la formule DECALER?

La fonction DECALER n'a pas pour rôle de décaler physiquement les cellules dans la feuille ni de modifier la sélection. Elle renvoie simplement une référence. La fonction DECALER peut être utilisée avec toutes les fonctions exigeant une référence comme argument.


Cette fonction Excel, renvoie une référence à une plage qui correspond à un nombre déterminé de lignes et de colonnes d'une cellule ou plage de cellules. La référence qui est renvoyée peut être une cellule unique ou une plage de cellules. Vous pouvez spécifier le nombre de lignes et de colonnes à renvoyer.

Syntaxe: DECALER(réf;lignes;colonnes;hauteur;largeur)

On utilise la Fonction d'Excel DECALER pour définir des plages de cellules de façon dynamique.

Vous avez, dans vos travaux Excel, surement déjà défini des plages nommées afin de les réutiliser dans des validations de données ou autres. L'inconvénient c'est que vos plages sont fixes et difficiles à manipuler. DECALER va vous permettre d'utiliser ces plages même si vous rajoutez de nouveaux éléments par la suite.

Prenons l'exemple d'une liste de noms que nous réutilisons dans une validation de données, comme dans la feuille de temps.

Sur la Feuil1, nous avons en A1 à A7 la liste suivante:



Nous voulons utiliser cette liste par la suite. Nous pourrions la nommer directement en sélectionnant A2 à A7 et définir le nom PRENOMS. Cette façon de procéder nous limite, puisque si nous ajoutons un prénom à cette liste, il nous faudra modifier l'étendue de la plage nommée. Nous allons voir qu'avec DECALER ce problème n'existe plus.

Nous allons utiliser la formule suivante =DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1), qui se traduit ainsi, on démarre de A1, on descend d'une ligne, on reste dans la même colonne, on évalue le nombre de cellules de la colonne A qui contiennent une valeur « NBVAL » et on retire la ligne d'entête, avec une largeur d'une colonne.

Si nous tapons cette formule dans la feuille nous obtenons #VALEUR, Excel n'est pas capable d'afficher le résultat puisqu'il s'agit d'une plage de cellules.


Nous allons donc utiliser cette formule avec un NOM. Dans le Menu FORMULE/GESTIONNAIRE DE NOMS nous créons un NOUVEAU NOM. Dans la boite de dialogue nous entrons Prénoms et notre formule =DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1) dans Fait référence à: et nous validons.


Maintenant si nous cliquons sur le petit carré à droite de la formule, Excel nous affiche la zone sélectionnée dans la feuille, zone qui correspond à nos prénoms sans l'entête.


Si nous créons une Validation de données à partir de notre liste nommée Prénoms, tous les prénoms s'afficheront et nous pourrons sélectionner celui que nous voulons.
Maintenant si j'ajoute un nouveau prénom dans ma liste, il apparait instantanément dans ma liste de Validation.

Une liste de Validation qui s'ajuste c'est bien, mais si notre liste fait plusieurs centaines de lignes, cela devient vite pénible de rechercher une valeur dans une liste qu'il faut faire défiler jusqu'à l’information voulue. C'est encore DECALER qui va nous aider à simplifier quelque peu notre recherche dans cette liste.

Donc nous avons toujours notre liste de prénoms, mais cette fois nous allons ajouter A, B, C, D, etc, et ensuite trier notre liste par ordre alphabétique comme ceci.


Notre validation précédente va nous afficher exactement cette liste. Mais nous voulons quand nous saisissons D uniquement avoir David et Denis à l'affichage. Alors nous allons créer un nouveau NOM, contenant =DECALER(Prénoms;EQUIV(F8&"*";Prénoms;0)-1;;NB.SI(Prénoms;F8&"*")).
Cette nouvelle formule fait référence à la formule DECALER précédente et rajoute la possibilité d'afficher les prénoms correspondant à la première lettre demandée. Ainsi si nous entrons D dans la zone de recherche Excel affichera Davis et Denis uniquement.
Décortiquons notre formule, La référence cette n'est plus A1 mais Prénoms, la ligne est cette fois la position relative de notre recherche dans Prénoms "EQUIV(F8&"*";Prénoms;0)-1)", et la hauteur est déterminée par le nombre de cellules non vide dans la liste, répondant à la condition spécifiée "NB.SI(Prénoms;F8&"*")". F8 est la cellule ou nous allons positionnez notre Validation de données.
Donc nous créons un nouveau nom, Prénoms2, qui fait référence à notre formule, =DECALER(Prénoms;EQUIV(F8&"*";Prénoms;0)-1;;NB.SI(Prénoms;F8&"*")) et nous l'utilisons dans notre nouvelle Validation de données, pour obtenir un affichage comme montré par l'image ci-dessous.


Il vous suffit de rajouter une fonction de tri automatique par VBA pour avoir votre liste toujours correctement triée.

Si vous souhaitez voir d'autres formules Excel consultez cette page.


Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF


Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner
Pays/territoire : Bry-sur-Marne, France

0 commentaires :

Enregistrer un commentaire