Formations sur la fonction Excel Decaler

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


La fonction Excel Decaler

L'enjeu des applications professionnelles est de rendre les solutions dynamiques. Les analyses et calculs doivent s'adapter à des contextes évolutifs et des données en perpétuel mouvement. La fonction Excel Decaler réalise ces prouesses, souvent avec une simplicité déconcertante. Elle livre des outils capables de s'ajuster à toutes les situations. Bref, cette fonction Decaler ne peut être ignorée. Elle est le couteau Suisse dont il ne faut jamais se séparer. Cette série de mises en situation permet de l'étudier sous toutes les coutures au travers de nombreux cas pratiques pour aboutir des solutions saisissantes. Des sources sont offertes au téléchargement gratuit au début de chaque chapitre.

01/20 : Isoler les dernières informations d'une liste Excel



Extraire les N dernières valeurs d'une colonne grâce aux fonctions Excel Decaler et Ligne dans une formule répliquée pour incrémenter les positions. Nous travaillons sur une base de données Excel synthétisant les chiffres d'affaires réalisés mois par mois au cours de l'année. L'utilisateur peut définir le nombre des derniers mois qu'il souhaite isoler par le biais d'une liste déroulante. Aussitôt, un calcul impliquant les fonctions Decaler et Ligne dresse la liste de ces chiffres dans un ordre inversé, pour partir du dernier. Pour cela, nous opérons un décalage en ligne en cohérence avec le nombre demandé. Enfin, nous dressons un rendu visuel dynamique repérant les enregistrements depuis la source de données, pour corroborer la liste inversée des chiffres isolés.

Lien de la formation | Lien de la vidéo

02/20 : Somme des ventes Excel sur un mois variable



Bilan consolidé des ventes réalisées par articles regroupés sur un mois variable grâce à la fonction Excel Decaler pour déplacer les bornes de l'analyse. Nous travaillons sur un tableau des ventes réalisées sur des articles au cours des 12 mois de l'année. Ces références sont répétées à de nombreuses reprises car les ventes n'ont pas été faites en une seule fois. C'est pourquoi nous avons besoin de consolider ces chiffres sur les références et sur un mois désigné. Pour cela, nous exploitons la fonction de somme conditionnelle Somme.Si. Le critère est posé sur la référence à recouper dans la plage des articles à analyser. La colonne de la somme varie en revanche selon le numéro de mois choisi. Pour pointer dessus dynamiquement, nous exploitons la fonction Excel Decaler en troisième argument de la fonction Somme.Si. Nous opérons un déplacement à l'horizontale du même nombre que celui indiqué par le numéro de mois. Enfin, nous faisons ressortir les données du mois et de la référence ayant conduit au meilleur score par des règles de mise en forme conditionnelle. Ainsi, nous venons renforcer la pertinence du bilan dynamique sur les chiffres consolidés.

Lien de la formation | Lien de la vidéo

03/20 : Synthèses Excel sur un nombre variable de mois



Sommes des chiffres d'affaires sur un nombre de mois dynamique grâce à une liste déroulante et à la fonction Decaler pour ajuster les bornes du calcul. Nous travaillons sur une base de données des ventes réalisées pour chaque article de l'entreprise au cours de chacun des mois de l'année. Pour obtenir des bilans précis et des synthèses ciblées, l'utilisateur peut choisir un numéro de mois à l'aide d'une liste déroulante. Aussitôt, toutes les ventes par articles se cumulent sur la période définie. Et dans le même temps, la somme est calculée sur la période exclue. Pour produire ces consolidations dynamiques, nous exploitons la fonction Excel Decaler. Tantôt, elle permet de déplacer la borne de fin. Tantôt elle permet de décaler la borne de départ. Ainsi, nous réalisons des statistiques sur des plages de cellules dont la largeur est variable.

Lien de la formation | Lien de la vidéo

04/20 : Sommer les N premières valeurs avec Excel



Additionner les chiffres d'affaires des N premiers mois définis par une liste déroulante grâce à la fonction Excel Decaler pour ajuster la hauteur de la plage. Nous travaillons sur un tableau consolidant les chiffres d'affaires pour chaque mois de l'année. A des fins d'études statistiques, l'utilisateur peut définir un nombre de mois à isoler par le biais d'une liste déroulante. Aussitôt, grâce à un calcul imbriquant les fonctions Somme et Decaler, nous livrons le total des CA pour la période. C'est l'argument de la hauteur exploitant le nombre choisi dans la fonction Decaler qui permet d'ajuster automatiquement la plage pour la somme.

Lien de la formation | Lien de la vidéo

05/20 : Calculs de synthèse sur une période variable



Sommes et moyennes des ventes selon un mois inscrit en texte choisi avec une liste déroulante et cherché dans le tableau pour ajuster la largeur de la plage. Nous travaillons sur un tableau rappelant les chiffres réalisés par article au cours de chacun des mois de l'année. Pour obtenir une synthèse sur une période précise à analyser, l'utilisateur peut désigner un mois par le biais d'une liste déroulante. Mais comme cette information est textuelle, nous devons chercher sa position dans le tableau source à l'aide de la fonction Equiv. Imbriquée dans l'argument de la largeur de la fonction Decaler, elle permet d'ajuster la plage sur laquelle les calculs sont entrepris pour livrer les statistiques.

Lien de la formation | Lien de la vidéo

06/20 : Ajuster dynamiquement la plage du calcul avec Excel



Adapter la position en colonne et la hauteur de la plage de cellules pour consolider les résultats sur des critères recoupés avec à la fonction Decaler. Nous travaillons sur une base de données recensant les chiffres d'affaires réalisés par une société pour chaque mois de plusieurs années. L'utilisateur définit un mois seuil et une année. Sur ces conditions recoupées, nous calculons la somme des ventes réalisées pour ces premiers mois et pour l'année définie. Pour cela, nous cherchons les positions des critères dans le tableau Excel afin d'ajuster l'emplacement et la dimension de la plage de calcul, grâce à la fonction Excel Decaler.

Lien de la formation | Lien de la vidéo

07/20 : Repérer les N meilleures valeurs dans une plage mouvante



Règle de mise en forme conditionnelle Excel bâtie sur un nom de plage de cellules construit avec la fonction Decaler pour déplacer et ajuster ses bornes. Nous travaillons sur une base de données des chiffres d'affaires réalisés chaque mois sur plusieurs années. L'utilisateur peut émettre deux contraintes pour produire des statistiques affinées. Il définit tout d'abord l'année à analyser. Puis, il spécifie les N derniers mois sur lesquels l'étude doit porter. Les données concernées varient du fait de la position en colonne mais aussi en hauteur. Aucune règle de mise en forme conditionnelle prédéfinie n'est en mesure de faire ressortir les meilleurs scores sur une plage dont la position et les bornes ne sont pas connues à l'avance. Donc, nous construisons cette plage à l'aide de la fonction Decaler de manière à ce qu'elle s'ajuste en fonction des critères recoupés. Dès lors, une règle de mise en forme conditionnelle exploitant la fonction Grande.Valeur sur cette plage mouvante est capable de faire ressortir les 3 meilleurs chiffres d'affaires.

Lien de la formation | Lien de la vidéo

08/20 : Moyenne des N meilleures valeurs sur plage variable



Calcul Excel réalisant la moyenne des grandes valeurs au nombre indéfini sur une plage de cellules variant en position et hauteur grâce à la fonction Decaler. L'utilisateur souhaite connaître la moyenne des 3 meilleurs chiffres d'affaires sur une année choisie et sur un nombre de mois défini. Ce nombre désigne les N derniers mois de l'année spécifiée. Nous déplaçons le point de départ de la plage sur le dernier chiffre grâce à la fonction NbVal. Nous ajustons sa position en colonne grâce à la fonction Equiv. Celle-ci recherche l'année stipulée dans la ligne de titre du tableau. Enfin, nous ajustons la hauteur de la plage en fonction du nombre de mois inscrit. La fonction Grande.Valeur peut alors extraire les trois meilleurs chiffres et les passer à la fonction moyenne pour le calcul final.

Lien de la formation | Lien de la vidéo

09/20 : Zone d'impression automatiquement ajustée avec Excel



Zones d'impression extensibles en fonction des données ajoutées à la suite du tableau grâce aux fonctions Excel Decaler et NbVal pour ajuster la hauteur. Nous savons définir des zones d'impression avec Excel. Associées à des réglages de mises en page, elles permettent de produire des éditions maîtrisées. Mais, leurs bornes sont fixes et statiques. Lorsque nous travaillons sur des données en constante évolution, elles ne permettent pas d'intégrer les nouvelles informations dans l'édition préréglée. Pour pallier ce défaut, nous exploitons le gestionnaire de noms. Nous constatons que la zone d'impression est identifiée comme une plage nommée. Nous retravaillons ce nom grâce à la fonction Excel Decaler. Et nous ajustons la hauteur de cette plage grâce à la fonction NbVal, capable de compter les éléments contenus dans une colonne. Il en résulte la hauteur désormais dynamique de la zone d'impression. A chaque ajout de données et à chaque nouvelle édition, nous constatons que les dernières entrées sont en effet intégrées dans l'aperçu avant impression.

Lien de la formation | Lien de la vidéo

10/20 : Impression dynamique ajustée à la zone utile



Agrandir dynamiquement la zone d'impression avec la fonction Excel Decaler pour éditer le tableau jusqu'à la dernière colonne portant une cellule non vide. Nous travaillons sur un tableau de saisie des ventes réalisées par article pour chacun des mois de l'année. Certains mois sont totalement renseignés. D'autres ne le sont que partiellement. Et certaines colonnes sont totalement vides. Nous souhaitons produire une impression dynamique utile. Elle ne doit éditer que les ventes effectivement inscrites. Et cette zone d'impression doit évoluer en même temps que les données. Pour cela, nous créons une première zone d'impression statique. Puis nous retravaillons sa plage dans le gestionnaire de noms. Pour ce faire, nous exploitons la fonction Decaler. Dans son argument de la largeur variable, nous imbriquons les fonctions Max et Si que nous faisons agir sur toutes les données du tableau. L'objectif est de déceler la dernière colonne à porter au moins une valeur. De fait, nous créons une édition dynamique et évolutive, capable de s'adapter au gré des informations renseignées.

Lien de la formation | Lien de la vidéo

11/20 : Edition automatique de la période choisie dynamiquement



Au choix d'un mois de départ et d'un mois de fin, la zone d'impression Excel s'ajuste automatiquement pour offrir l'édition isolée de la période demandée. Pour cela, nous retravaillons la plage d'impression avec la fonction Excel Decaler. Nous réglons son décalage en colonne en fonction de la position du mois, trouvée grâce à la fonction Equiv. Nous ajustons sa hauteur variable grâce à la fonction NbVal. Nous paramétrons enfin sa largeur en faisant la différence entre la position des deux mois, toujours décelée grâce à la fonction Equiv. Enfin, nous créons une règle de mise en forme conditionnelle multicritère. Elle marque sur un fond jaune la plage dynamique destinée à l'édition ajustée.

Lien de la formation | Lien de la vidéo

12/20 : Choisir dynamiquement le tableau Excel à imprimer



Zone d'impression ajustée en ligne et colonne mais aussi en largeur et hauteur pour définir la plage à éditer du bon tableau selon les critères posés. Nous travaillons sur une feuille dotée de trois tableaux. Chaque tableau correspond à un continent différent. Ils résument les ventes réalisées par destination et pour chaque mois de l'année. L'utilisateur peut actionner trois leviers. Il s'agit du continent, du mois de début et du mois de fin. Aussitôt, la plage à éditer est mise en valeur. Et effectivement, seules les données choisies pour le tableau sélectionné sont proposées à l'impression.

Lien de la formation | Lien de la vidéo

13/20 : Choix multiples dépendants et automatisés



Listes déroulantes dépendantes sur toutes les lignes du tableau Excel grâce à la fonction Decaler pour ajuster les contenus variables aux choix émis. Nous travaillons sur une base de données des salariés. Les sites d'activité dépendent du service affecté à l'employé. C'est la fonction Excel Decaler qui permet d'ajuster dynamiquement la plage servant à nourrir la liste déroulante dans la source de données. Pour cela, elle adapte le positionnement en ligne selon le service et la hauteur en fonction du nombre de sites.

Lien de la formation | Lien de la vidéo

14/20 : Adapter dynamiquement la vue d'un graphique Excel



Changer la représentation graphique des ventes au choix d'un mois dans une liste déroulante grâce à la fonction Excel Decaler dans la source de données. Nous travaillons sur un tableau des ventes réalisées par les commerciaux. Au choix du mois par l'utilisateur, le graphique Excel s'ajuste pour représenter ces montants sur la période désignée. Pour cela, nous créons une plage de cellules que nous travaillons avec la fonction Decaler. Nous déplaçons le pointeur sur la bonne colonne par recherche de la position du mois dans le tableau, grâce à la fonction Equiv. Nous exploitons alors cette plage variable comme source de données du graphique en troisième argument de la fonction Serie.

Lien de la formation | Lien de la vidéo

15/20 : Adapter la vue du graphique au clic dans une colonne



Changer la représentation graphique des données numériques en fonction de la colonne des ventes cliquée grâce aux fonctions Excel Decaler et Cellule. Nous adaptons l'application précédente. Elle permettait de modifier la source de données du graphique en fonction du mois choisi par l'utilisateur par le biais d'une liste déroulante. Désormais, cette modification dynamique doit intervenir au clic de la souris. Mais, pour déclencher le recalcul sur cet événement, nous commençons par créer une procédure VBA triviale. Ensuite, nous exploitons la fonction Excel Cellule avec son paramètre Colonne dans l'argument du décalage en colonne de la fonction Decaler. Il en résulte un réajustement de la plage de données sur la colonne du mois cliqué et une adaptation en cohérence de la vue du graphique.

Lien de la formation | Lien de la vidéo

16/20 : Insérer automatiquement des données dans un graphique



Intégrer automatiquement de nouvelles valeurs ajoutées à la suite du tableau dans la représentation graphique Excel avec la fonction Decaler pour la hauteur. Nous poursuivons les travaux précédents lors desquels nous étions parvenus à ajuster la vue du graphique en fonction du mois choisi par le biais d'une liste déroulante. Désormais, nous devons permettre l'intégration automatique de nouveaux commerciaux ajoutés par saisie à la suite du tableau. La source de données du graphique est composée de deux arguments clés. Le premier concerne la colonne des commerciaux pour l'axe des abscisses. Le deuxième concerne la colonne des ventes pour la représentation graphique des valeurs. Nous retravaillons la plage du premier argument avec la fonction NbVal. Ainsi, sa hauteur s'adapte au nombre de commerciaux. De la même manière, nous retravaillons le quatrième argument de la fonction Decaler pour la colonne des ventes. Elle doit elle aussi ajuster sa hauteur. Ainsi, chaque nouvelle entrée saisie à la suite du tableau est directement intégrée dans la représentation graphique Excel.

Lien de la formation | Lien de la vidéo

17/20 : Graphique Excel auto-ajusté à la période et au vendeur



Représentation graphique des ventes s'adaptant automatiquement et dynamiquement aux critères émis sur les mois pour la période et le vendeur à isoler. Nous exerçons nos travaux sur un tableau relatant les ventes réalisées par les commerciaux d'une entreprise. Un graphique statique préconçu est proposé. Il relate les chiffres d'un vendeur en particulier pour toute la période. Pour le faire réagir aux conditions recoupées, nous retravaillons la syntaxe de sa série. Pour cela, nous créons une première plage de cellules auto-ajustable à la période définie grâce à la fonction Decaler. Puis, nous créons une seconde plage qui permet de pointer sur les ventes du commercial, toujours pour cette même période. La fonction Equiv est essentielle dans la fonction Decaler pour repérer les positions. Enfin, nous remplaçons les arguments de la série du graphique par ces plages nommées. Dès lors, la représentation réagit automatiquement à chaque levier actionné. Et pour renforcer l'effet visuel des interprétations et analyses, nous créons une règle de mise en forme conditionnelle. Sur un raisonnement similaire, elle permet dans le même temps de surligner dans le tableau, les ventes effectivement représentées sur le graphique.

Lien de la formation | Lien de la vidéo

18/20 : Calculs sur des plages alternées de longueurs dynamiques



Sommes et moyennes toutes les N valeurs grâce à un décalage cyclique généré par la fonction Excel Decaler sur une hauteur dynamique inscrite par l'utilisateur. Le point de référence des opérations est fixé sur la première donnée de la plage des valeurs numériques. La différence entre la ligne en cours et la ligne de départ est ramenée à l'échelle du cycle par multiplication avec cette contrainte dynamique. Ce résultat fournit le décalage en ligne pour pointer sur la nouvelle cellule de départ. La hauteur de la plage variable alternée puise elle aussi sa dimension sur cette contrainte. Nous renforçons la solution par une mise en forme conditionnelle capable de surligner dynamiquement les plages alternées impliquées dans les calculs.

Lien de la formation | Lien de la vidéo

19/20 : Réunir les données de plusieurs colonnes par calcul Excel



Regrouper et consolider les informations de plusieurs rangées dans une seule colonne grâce à la fonction Excel exerçant des décalages sur des pas incrémentés. A partir d'une liste de personnes énumérées sur un nombre variable de lignes et ventilées sur plusieurs colonnes, nous reconstruisons l'énumération linéaire des noms dans une seule colonne. Et pour cela, nous construisons une unique formule que nous répliquons à la verticale. Grâce à la fonction Excel Decaler, nous exerçons des déplacements en ligne jusqu'à la fin de chaque rangée. Lorsque cette borne inférieure est détectée, c'est un décalage horizontal qui est opéré. L'analyse est ainsi naturellement déplacée sur la colonne suivante pour poursuivre le prélèvement des noms sans interruption.

Lien de la formation | Lien de la vidéo

20/20 : Dates des événements sur calendrier Excel



Extraire les dates des événements choisis grâce à une liste déroulante par recherche et extraction dans le calendrier Excel avec les fonctions Equiv et Decaler. Nous travaillons sur un calendrier énumérant tous les événements recensés dans une colonne. La date d'échéance est marquée d'une croix dans la rangée du jour correspondant. C'est la recherche de cette croix qui permet d'obtenir la date. Mais encore faut-il l'exercer sur la bonne ligne. Pour cela, nous exploitons la fonction Excel Decaler afin de déplacer l'analyse précisément sur la ligne de l'événement dans le planning.

Lien de la formation | Lien de la vidéo