Extraire les informations Excel d'une colonne variable

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


Sujets que vous pourriez aussi aimer :


Extraire les données d'une colonne variable

Dans cette formation Excel, nous proposons de bâtir une petite application d'entreprise, à des fins d'analyses stratégiques. Une société effectue le suivi des ventes de ses produits depuis plusieurs années. C'est une petite base de données qui archive les résultats consolidés par article.



Pour une analyse instantanée pertinente, l'objectif consiste à pouvoir comparer les chiffres réalisés entre deux années sélectionnées, par le biais de listes déroulantes. Une mise en forme conditionnelle doit appuyer la lecture des extractions pour faciliter l'interprétation des évolutions.



Il s'agit donc de pourvoir extraire les données selon une information de colonne variable. Les chiffres sont en effet énumérés par champ, représentant chaque année d'activité. A la désignation de l'une d'entre elles, ce sont toutes les informations de sa colonne qui doivent être importées.

Source et présentation de la problématique
Nous proposons de débuter les travaux depuis un classeur source offrant la structure de l'application et la base de données des chiffres d'affaires réalisés.

• Télécharger le classeur comparer-ventes-annuelles.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles. La seconde est nommée Ventes_annuelles. C'est elle qui archive les chiffres réalisés par produit, au cours des différentes années. Elle constitue donc la source à partir de laquelle doivent se réaliser les extractions, selon les choix émis pour les années à comparer.

La première feuille est nommée Comparer_ventes. Elle offre la structure à partir de laquelle nous devons bâtir les formules permettant les comparaisons dynamiques. Vous notez la présence de deux listes déroulantes, respectivement en cellules C4 et D4.



Leur source de données est issue de la deuxième ligne de la feuille Ventes_annuelles.

Importer les informations d'une colonne
Au choix de l'une des années, dans l'une ou l'autre liste, tous les chiffres correspondants doivent être extraits de la feuille source, pour être restitués dans la colonne d'appel. Il s'agit donc de bâtir un calcul d'extraction dynamique. Les lignes à récupérer varient de l'indice 3 à l'indice 15, quelle que soit la colonne. Mais cette colonne précisément est la variable du problème. Elle dépend du choix effectué dans la liste déroulante.

La fonction la plus adaptée à ce type d'extraction est la fonction Adresse. Nous l'avons exploitée dans la formation précédente qui a consisté à finaliser la construction dynamique et automatique des plannings de travail. Selon une information variable de colonne, elle a permis d'extraire les dates de congés du salarié pour les considérer dans la construction. Sa syntaxe est la suivante :

=Adresse(Numéro_de_ligne; Numéro_de_colonne)

Elle retourne les références d'une cellule située au croisement d'un numéro de ligne et d'un numéro de colonne. Nous proposons de la tester dans une cellule arbitraire. Nous supprimerons ensuite ce résultat.

• Cliquer sur la cellule F2 par exemple, pour la sélectionner,
• Taper le symbole = pour démarrer la formule,
• Saisir la fonction donnant les références d'une cellule, suivie d'une parenthèse, soit Adresse(,
• Saisir le chiffre 5 pour désigner la cinquième ligne de la feuille,
• Taper un point-virgule (;) pour passer dans l'argument du numéro de colonne,
• Saisir le chiffre 2 pour pointer sur la deuxième colonne, soit la colonne B,
• Fermer la parenthèse de la fonction Adresse et valider le calcul,

Le résultat fourni est : $B$5. Il s'agit bien des références de la cellule que nous avons désignées par sa ligne et sa colonne. Mais ces références sont traduites en texte par Excel. Si elles avaient été interprétées, nous aurions obtenu le contenu de la cellule B5. Or c'est bien ce que nous cherchons à réaliser pour restituer les ventes des articles, selon l'année spécifiée.

Nous devons donc utiliser la fonction Excel Indirect. Comme son nom l'indique, elle exploite indirectement ce résultat textuel. En d'autres termes, elle l'interprète en références de cellule.

• En F2, modifier la formule précédente comme suit :

=Indirect(ADRESSE(5;2))

Cette fois, après validation, le résultat retourné est bien le contenu de la cellule B5 désignée, soit la référence d'article Ar001. Nous devons répliquer cette technique pour extraire les chiffres, mais ce n'est pas tout. Dans cette démonstration, nous avons utilisé des valeurs statiques de ligne et de colonne, soit des constantes. Et comme l'enseigne le petit livre Excel sur les formules avancées, tout calcul dans Excel doit être bâti sur des variables. Le contexte nous l'impose d'ailleurs. L'extraction doit se produire à partir de la ligne 3 de la feuille Ventes_annuelles. Mais cet indice doit évoluer en même temps que le calcul est répliqué sur les lignes du dessous. Nous exploiterons donc la fonction Excel Ligne en lui passant une cellule de la troisième ligne pour commencer, par exemple A3.

=Ligne(A3)

De cette manière, elle retourne le chiffre 3. Mais comme A3 se transforme en A4 puis A5 etc..., en même temps que le calcul est répliqué vers le bas, l'indice retourné évoluera de la même façon. Cette fonction est donc à imbriquer dans le premier argument de la fonction Adresse.

Concernant le numéro de colonne, il est variable. Il dépend du choix effectué dans la liste déroulante. La solution consiste à réaliser une recherche de l'année choisie dans la deuxième ligne de la feuille Ventes_annuelles. Grâce à la fonction Excel Equiv, cette recherche retournera l'indice de colonne à partir de laquelle les données doivent être extraites.

=Equiv(Valeur_Cherchée; Ligne_de_recherche ; 0)

Maintenant que nous savons comment sélectionner les inconnues du problème, nous pouvons débuter la formule d'extraction.

• Sur la feuille Comparer_ventes, supprimer le contenu de la cellule F2,
• Puis, cliquer sur la cellule C5 pour désigner le point de départ de la première extraction,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction pour interpréter les références, suivie d'une parenthèse, soit Indirect(,
• Saisir le nom de la feuille de recherche suivi d'un point d'exclamation, le tout entre guillemets, ce qui donne : 'Ventes_annuelles!',
• Enfoncer la touche 1 en haut à gauche du clavier pour inscrire le caractère de concaténation,

En effet, nous savons désormais désigner une cellule dynamiquement grâce à la fonction Adresse. Mais n'oublions pas qu'elles sont situées sur une autre feuille. Et selon la syntaxe Excel, ces références doivent être préfixées du nom de la feuille suivi d'un point d'exclamation. Nous réalisons cet assemblage par concaténation (&).

• Saisir la fonction donnant les références, suivie d'une parenthèse, soit Adresse(,
• Saisir la fonction indiquant la ligne d'une cellule, suivie d'une parenthèse, soit Ligne(,
• Sélectionner une cellule de la troisième ligne, par exemple A3,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument du numéro de colonne,
• Saisir la fonction donnant la colonne d'une valeur cherchée, suivie d'une parenthèse, soit Equiv(,
• Sélectionner la cellule C4 pour fournir l'année à rechercher dans le tableau de référence,
• Enfoncer deux fois la touche F4 du clavier pour la figer seulement en ligne, ce qui donne : C$4,

Nous répliquerons ce calcul sur les lignes du dessous pour récupérer les ventes de chaque article, pour l'année désignée. Mais précisément, cette valeur cherchée est toujours située en C4. Elle ne doit donc pas suivre le déplacement en ligne. Par anticipation en revanche, nous savons que nous devons réaliser quasiment la même formule pour l'extraction en seconde colonne. La seule donnée qui varie est justement l'année cherchée qui passe en D4. Nous défigeons la colonne pour qu'elle se déplace naturellement lorsque nous reproduirons la formule sur la droite.

• Taper un point-virgule (;) pour passer dans l'argument de la ligne de recherche,
• Cliquer sur l'onglet Ventes_annuelles en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner l'intégralité de la deuxième ligne par son étiquette,
• Puis, figer cette matrice à l'aide de la touche F4 du clavier, ce qui donne : Ventes_annuelles!$2:$2,

L'année doit immuablement être cherchée sur cette ligne. Elle ne doit surtout pas déplacer ses références en même temps que nous répliquons la formule.

• Taper un point-virgule suivi du chiffre 0, soit : ;0 pour une recherche selon une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,

A ce stade, elle est censée avoir retourné l'information sur la position de la colonne. Comme nous possédons déjà l'indication sur la ligne, la fonction Adresse doit pourvoir retourner la référence de la cellule à extraire. L'extraction de son contenu sera réalisée par la fonction Indirect, tel que nous l'avons démontré précédemment.

• Fermer la parenthèse de la fonction Adresse,
• Fermer la parenthèse de la fonction Indirect qui l'encadre,
• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Tirer la poignée de la cellule sur la droite, jusqu'à la cellule D5 et conserver la sélection active,

Nous obtenons effectivement la première extraction des ventes pour l'année désignée en entête de seconde colonne.

• Puis, double cliquer sur la poignée de la sélection pour reproduire la logique du calcul sur la hauteur du tableau, pour les deux colonnes à la fois,

Toutes les ventes, pour les années respectives, sont parfaitement extraites et restituées pour chacun des articles. Comme nous avons judicieusement figé les références, notamment l'année de recherche, nous avons pu répliquer le calcul partout, sans le refaire.



Nous aurions pu être encore plus efficace, comme l'enseigne le petit livre Excel sur les techniques de calcul. Il se serait agi de présélectionner l'ensemble des cellules concernées par le calcul, soit la plage C5:D17. Nous aurions alors dû bâtir la formule exactement comme nous l'avons fait. Mais surtout à la fin, nous aurions dû la valider par le raccourci clavier CTRL + Entrée. Cette technique permet de répliquer la logique du calcul sur l'ensemble de la plage sélectionnée, sans devoir tirer la poigné donc. La formule que nous avons bâtie est la suivante :

=INDIRECT('Ventes_annuelles!' & ADRESSE(LIGNE(A3); EQUIV(C$4; Ventes_annuelles!$2:$2; 0)))

Si vous changez les années à l'aide des deux listes déroulantes en C4 et D4, vous constatez que l'importation dynamique adapte parfaitement tous les chiffres à comparer. Mais les écarts sont parfois faibles et ne sautent pas forcément aux yeux. Il est encore plus difficile de se faire une idée sur l'évolution globale entre les deux années. Dans un premier temps, nous proposons de réaliser ce calcul statistique de progression. Un emplacement est prévu à cet effet. Il s'agit de la cellule C19. Nous devons effectuer la différence entre les sommes des ventes.

• Cliquer sur C19 pour sélectionner la cellule du calcul,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction pour les additions, suivie d'une parenthèse, soit Somme(,
• Sélectionner toutes les ventes de la seconde année, soit la plage de cellules D5:D17,
• Fermer la parenthèse de la fonction Somme,
• Taper le symbole moins (-) pour enclencher la soustraction,
• Saisir de nouveau la fonction pour les additions, suivie d'une parenthèse, soit Somme(,
• Sélectionner toutes les ventes de la première année, soit la plage de cellules C5:C17,
• Fermer la parenthèse de la fonction Somme et valider le calcul,

Grâce à cette formule :

=SOMME(D5:D17) - SOMME(C5:C17)

Le résultat global est plus clair.



Selon que le résultat est positif ou négatif, nous savons s'il s'agit d'une progression globale des ventes ou d'une régression. L'interprétation est simplifiée. Mais ça ne suffit pas.

Comparer les écarts
Nous souhaitons réaliser cette comparaison visuellement. A ce stade en effet, la lecture ligne à ligne ne simplifie pas les conclusions. Un format dynamique renforcera la pertinence des résultats en simplifiant la lecture des données extraites. Et nous allons voir qu'Excel propose des réglages prédéfinis qui mettent en lumière les évolutions et leurs écarts. Les articles doivent être comparés entre eux, soit ligne à ligne. Nous répliquerons ensuite ces attributs avec une méthode automatisée.

• Sélectionner les deux premiers articles à comparer, soit les cellules C5 et D5,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, pointer sur la rubrique Jeux d'icônes,
• Dans la section Directionnel du sous menu qui apparaît, choisir un visuel comme 3 triangles,

Comme l'illustre la capture ci-dessus, une symbolique évidente vient renforcer la lecture des résultats. Le montant le plus faible est associé à une flèche rouge descendante explicite. Le montant le plus élevé est associé à une flèche verte ascendante, relatant parfaitement la progression des ventes.

Plutôt que de reproduire ces réglages sur chacune des lignes, nous proposons de les répliquer à l'aide de l'outil Reproduire la mise en forme. Il est matérialisé par un pinceau. Il est situé sur la gauche du ruban Accueil, dans la section Presse-papiers.

• Sélectionner les deux cellules desquelles les réglages doivent être prélevés, soit C5 et D5,
• Double cliquer sur le bouton Reproduire la mise en forme dans le ruban Accueil,
• Sélectionner alors C6 et D6 puis relâcher le bouton de la souris,
• Sélectionner ensuite C7 et D7, puis relâcher le bouton de la souris,
• Reproduire cette action pour chacune des paires de cellules jusqu'à la fin du tableau, soit jusqu'aux cellules C17 et D17,
• Enfoncer la touche Echap ou recliquer sur le bouton du ruban Accueil pour désactiver l'outil,

Un simple clic sur l'outil reproduire la mise en forme permet de répliquer les attributs une seule fois. Un double clic permet de les reproduire en cascade, jusqu'à abandon de la commande (Echap). Quoiqu'il en soit, vous avez pu remarquer l'apparition des indicateurs visuels sur chaque ligne, en adéquation avec les valeurs, au fur et à mesure de la reproduction.



Comme l'illustre le résultat de la capture ci-dessus pour deux nouvelles années, le calcul statistique en bas du tableau, annonce la tendance générale. Elle est à la hausse. Les symboles de couleur quant à eux, permettent une étude simplifiée et plus rapide au cas par cas. Malgré cette bonne tenue globale, nous constatons que certaines ventes d'articles sont à la baisse. Ces interprétations s'avèrent précieuses pour envisager les meilleures dispositions à prendre.

Nous pourrions encore renforcer la pertinence de ces résultats dynamiques à l'aide d'un graphique. Les histogrammes sont dédiés dans la mesure où ils permettent de visualiser les évolutions tout en facilitant les comparaisons. Comme vous le savez, avant d'initier la construction d'un graphique, les données à représenter doivent être sélectionnées.

• Sélectionner tout le tableau des ventes, soit la plage de cellules B4:D17,
• Cliquer sur l'onglet Insertion en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Graphiques du ruban, cliquer sur le bouton Insérer un histogramme,
• Dans la liste, choisir le tout premier : Histogramme groupé,

La représentation graphique apparaît instantanément sur la feuille, à côté du tableau des données.

En plus des évolutions et des comparaisons entre articles pour les deux années, elle fait aussi ressortir la différence de volume qu'il peut y avoir entre chaque article. Il s'agit d'une donnée supplémentaire précieuse.

La représentation mérite d'être travaillée pour corriger certains défauts. La légende peut être redéfinie grâce au bouton Sélectionner des données du ruban contextuel Création. L'étiquette Article peut y être désactivée. Elle est en trop. Elle a été considérée car elle fait partie du tableau. Le titre peut être supprimé. L'échelle de l'axe des ordonnées peut être adaptée pour simplifier la lecture. La mise en forme peut être améliorée. Tous ces points sont traités en détail dans la formation Excel pour débuter avec les graphiques.