Récolter les données avec une barre de défilement

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


Sujets que vous pourriez aussi aimer :


Barre de défilement personnalisée

Excel offre la possibilité d'exploiter un certain nombre de contrôles, directement depuis la feuille d'un classeur. Parmi eux, on compte la barre de défilement. Et grâce à cette nouvelle astuce, nous allons voir comment la faire interagir avec les données d'un tableau.



Dans l'exemple finalisé illustré par la capture, nous parcourons les données d'un tableau, grâce à une barre de défilement verticale. A chaque clic sur la flèche du bas, les informations du commercial suivant sont remontées dans une ligne située juste au-dessus du tableau. A chaque clic sur la flèche du haut, ce sont les informations du commercial précédent qui sont remontées.

Classeur source
Pour la démonstration de cette nouvelle astuce Excel, nous suggérons de débuter l'étude à partir d'un classeur existant et hébergeant ce tableau.

• Télécharger le classeur progressbar.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons effectivement le tableau mais la barre de défilement est absente. Elle est à créer. Ainsi, nous comprendrons mieux son mécanisme. Une ligne vide est bien positionnée au-dessus de ce tableau. Elle est en attente des données à réceptionner au fil de la consultation interactive.

Créer et lier la barre de défilement
La barre de défilement à créer doit être liée à une cellule de la feuille. A chaque clic, celle-ci recevra la valeur numérique d'incrémentation ou de décrémentation. Et c'est cette valeur que nous utiliserons par calcul afin de récupérer la ligne du commercial concerné par la position transmise. Pour la suite de la mise en oeuvre, le ruban développeur est nécessaire. S'il n'est pas présent dans votre environnement, la formation pour débuter la programmation en VBA Excel explique, entre autres, comment l'afficher.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Insérer,
• Dans les propositions, choisir la barre de défilement,

• Puis, la tracer à la verticale juste à droite du petit tableau des contraintes dynamiques,
• Dès lors, cliquer droit sur cette barre de défilement,
• Dans le menu contextuel, choisir l'option Format de contrôle,
• Dans l'onglet Contrôle de la boîte de dialogue, régler la valeur minimale sur 1,
• Puis, régler la valeur maximale sur 11,

Ce tableau est en effet composé de 11 commerciaux à parcourir.

• En bas de la boîte de dialogue, cliquer dans la zone Cellule liée,
• Dès lors, sur la feuille, cliquer par exemple sur la cellule A11,

Nous définissons ainsi l'emplacement pour déposer la valeur numérique correspondant au clic de l'utilisateur sur l'une ou l'autre flèche de la barre de défilement.

• Valider ces réglages en cliquant sur le bouton Ok de la boîte de dialogue,

Désormais, chaque clic sur la barre de défilement est effectivement mémorisé en cellule A11, sous forme de correspondance de position. Comme cette cellule est formatée en noir sur fond noir, il faut la sélectionner et consulter sa barre de formule pour le constater. En effet, sa vocation est simplement de servir d'intermédiaire au calcul final pour récolter les données.

Récupérer les données au défilement
Désormais, à chaque clic sur la barre de défilement, nous devons importer les informations du commercial correspondant dans la ligne vide située au-dessus du tableau. Cette importation peut se faire dans la mesure où la ligne et la colonne de la donnée sont connues. Et c'est bien le cas. La colonne est celle de la précision à retourner. Pour le nom, il s'agit de la deuxième et elle correspond à la colonne de la cellule attendant le calcul. La ligne est fonction de la position retournée en cellule A11. Comme vous le savez, par rapport à une cellule de référence, c'est la fonction Excel Decaler qui permet de déplacer le curseur en ligne et en colonne pour pointer sur l'information souhaitée.

• Sélectionner la cellule de la première donnée à importer, soit : B8,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner le premier titre du tableau (Nom) en cliquant sur sa cellule B10,

Par rapport à ce titre en effet, nous savons que le premier nom à récupérer est placé une ligne plus bas, correspondant au premier clic sur la flèche du bas dans la barre de défilement.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Dès lors, désigner la position incrémentée en cliquant sur la cellule liée A11,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $A$11,

En effet, nous allons répliquer ce calcul sur la droite pour obtenir toutes les autres informations attachées. Et ce décalage en ligne ne doit pas suivre le déplacement. Son information est immuablement située en cellule A11. En revanche, c'est judicieusement que nous n'avons pas figé la cellule B10. Au gré de la réplication, le point de départ pointera bien sur le bon titre : Le prénom puis le nombre de commandes etc...

• Taper maintenant un point-virgule (;) pour passer dans l'argument du décalage en colonne,
• Saisir le chiffre zéro (0) puis fermer la parenthèse de la fonction Decaler,

En effet, le point de départ de chaque plage est situé sur la même rangée que celle du calcul. Il est donc bien placé et ne doit subir aucun décalage à l'horizontale.

• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons active la cellule du résultat pour l'exploiter dans l'enchaînement. Comme vous pouvez le voir, un nom est effectivement restitué. Et si vous remontez tout en haut de la liste avec la première flèche de la barre de défilement, vous importez bien dynamiquement le premier nom des commerciaux.

• Tirer la poignée du résultat sur la droite jusqu'en cellule I8,

Toutes les informations attachées sont automatiquement récoltées et ce, à l'aide d'une astuce et d'une formule à la syntaxe très simple : =DECALER(B10;$A$11;0). Si vous agissez avec la barre de défilement, vous constatez que toutes les informations suivent le déplacement.



Bien sûr, certaines cellules méritent d'être alignées et formatées en fonction des données recueillies. De plus, la balise active peut être utile pour supprimer les bordures de séparation du fait de la réplication.

Couleurs dynamiques sur les résultats
Pour parfaire la solution, nous suggérons d'appliquer des couleurs dynamiques significatives en fonction des résultats des commerciaux importés par défilement. Lorsque le montant de la prime est nul, la ligne 8 doit ressortir sur un fond tirant vers le rouge. Lorsqu'il est supérieur à 10000, il doit ressortir sur un fond vert. Deux règles de mise en forme conditionnelle sont donc nécessaires. Les critères doivent porter sur la cellule I8.

• Sélectionner toutes les données de la ligne, soit la plage de cellules B8:I8,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, opter pour l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Dans la zone de saisie du dessous, taper le symbole égal (=) pour initier la règle,
• Cliquer alors sur la cellule de la prime importée, soit : $I$8,

Elle apparaît complètement figée par défaut et c'est ce que nous souhaitons. En effet, toutes les données de la ligne sont concernées par la couleur. Mais la condition elle doit nécessairement être vérifiée uniquement par rapport à cette cellule.

• Construire alors le critère suivant : >10000,

Ainsi donc, lorsque la prime est jugée satisfaisante, nous souhaitons faire ressortir la ligne en vert.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert assez pâle,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la seconde liste déroulante, choisir un vert foncé pour la couleur du texte,
• Valider ces attributs de format avec le bouton Ok,
• Puis, valider la création de la règle de mise en forme, de nouveau par le bouton Ok,

Sur cette même plage de données, une seconde règle doit être bâtie dans l'enchaînement. Sur ce critère : =$I$8=0, c'est une dominante de rouge qui doit être appliquée à la ligne.



Désormais, si vous parcourez de nouveau les commerciaux avec la barre de défilement, vous avez le plaisir de constater que des indicateurs de couleur fort utiles se déclenchent dynamiquement.