Pages Menu
TwitterRssFacebook
Categories Menu

Posted by on Aug 25, 2020 in Information Management | 0 comments

Mettre en place des listes en cascade dans une base de données Excel

Mettre en place des listes en cascade dans une base de données Excel

Ce tutoriel est à destination des personnes qui souhaitent mettre en place des listes en cascade dans leur base de données Excel. Nul besoin d’être un crack en Excel pour cet exercice, il suffit simplement de suivre les instructions pas à pas 😉

Les listes en cascades sur Excel sont pertinentes pour saisir des données imbriquées à plusieurs niveaux. L’exemple le plus courant est celui des niveaux administratifs qui peuvent intégrer autant de niveaux d’imbrication que l’on souhaite. 

Prenons un cas d’étude pour illustrer notre propos : lorsqu’un.e chargé.e de projet humanitaire choisit, par exemple, le continent africain, il lui sera proposé uniquement les pays africains ; puis à chaque niveau administratif inférieur, uniquement les provinces du pays choisies seront visibles, puis uniquement les villes de la province choisie, et ainsi de suite.

Bien que très souvent utilisées pour ce type d’information, les listes en cascade peuvent, bien entendu, également être composées d’informations non administratives.

L’objectif d’une liste en cascade est triple :

  1. simplifier la saisie des données pour l’utilisateur,
  2. minimiser les erreurs de saisie, et
  3. gagner du temps de saisie.

Deux manières de réaliser des listes en cascades sont présentées dans ce post de blog :

  • ascendante : en saisissant le plus petit niveau du modèle de données et les niveaux supérieurs étant automatiquement saisis, et
  • descendante : en saisissant un à un les niveaux du modèle de données en partant du plus haut et en descendant dans les niveaux inférieurs.

Ce post n’a pas vocation à expliquer les listes en cascades permettant de visualiser grâce au XLSForm des listes en cascade dans les outils de collecte sur mobile du monde ODK, pour lequel vous trouverez plus d’informations sur le MDC Toolkit (mdc-toolkit.org) et le blog de CartONG (https://blog.cartong.org/2015/08/21/advanced-xls-forms-coding-2/).

Une troisième version permettant de mettre en place des listes en cascades existe également – une variante de la version descendante, avec la fonction INDIRECT. Nous avons cependant fait le choix de ne pas la traiter dans ce post, car elle représentait des limitations trop importantes dans les cas que nous utilisons. Cette variante, en effet, impose de référencer manuellement chaque plage et elle ne prend pas en compte les accents, caractères spéciaux et espaces. Cette version est largement documentée sur internet (voir références ci dessous).

Dans ce post de blog, nous prendrons un exemple administratif fictif dans lequel il y a un pays, avec trois régions, et chaque région a une ou plusieurs sous régions, qui ont elles mêmes une ou plusieurs communes, afin d’expliquer la mise en place de listes en cascade ascendante et descendante. Vous pouvez également télécharger ce tutoriel ci-dessus. Bonne lecture !

Glossaire

Commençons par parcourir les termes clés d’Excel qu’il est essentiel de maîtriser avant de mettre en oeuvre une liste en cascade :

Cellule d’intérêt : la cellule d’intérêt est la cellule où sera saisie l’information grâce à une liste déroulante préconfigurée. Elle se trouve donc dans la feuille de saisie. Dans le cas de la version ascendante, il n’y a qu’une cellule d’intérêt – à savoir la cellule de saisie du plus petit niveau. Dans le cas de la version descendante, il y a autant de cellules d’intérêt qu’il y a de niveaux imbriqués.

Feuille de saisie : la feuille de saisie est la feuille Excel où sont saisies les données par l’utilisateur (en charge de la saisie).

Feuille de paramétrage : la feuille de paramétrage est la feuille Excel où se trouvent les informations nécessaires pour configurer les listes. Celle-ci est uniquement utile pour l’administrateur de la base de données, et non la personne saisissant des données – une bonne pratique est donc de la protéger lorsqu’elle n’est pas en cours de modification, afin d’éviter les mauvaises manipulations.

Formule indirecte de recherche : la formule indirecte est une formule Excel configurée par l’utilisateur dans la version descendante des listes en cascade. Elle permet d’associer deux niveaux entre eux. Elle filtre automatiquement les données et fait appel aux tableaux associés lors de la saisie du niveau inférieur. La formule indirecte de recherche est référencée dans le gestionnaire de noms.

Gestionnaire de noms : le gestionnaire de noms est une fonctionnalité d’Excel qui permet de nommer au sein du fichier des plages (cellules, colonnes, lignes ou encore tableaux). Cette fonctionnalité permet de faire référence facilement à la plage – grâce au nom attribué – lors de saisie de formules ou autres fonctions.

Liste déroulante : c’est une liste de choix permettant aux utilisateurs de sélectionner un élément dans une liste précédemment configurée. Elle intervient dans la configuration des listes des versions ascendante et descendante.

Liste en cascade : une  liste en cascade permet de fractionner la saisie des données via des listes déroulantes, grâce au filtrage des données proposées aux niveaux suivants imbriqués.

Tableau associé : un tableau associé est un tableau à deux niveaux. Le niveau supérieur est à gauche et les données sont répétées autant de fois que nécessaires. Le niveau inférieur est à droite et les données n’apparaissent qu’une fois. Chaque ligne contient donc le niveau supérieur et le niveau inférieur associé. Le tableau associé doit être rangé par ordre alphabétique selon le niveau inférieur. Il intervient dans la configuration de la feuille de paramétrage de la version descendante.

Tableau exhaustif : un tableau exhaustif est un tableau qui contient toutes les informations existantes. Il est exhaustif sur la base du plus petit niveau : ce dernier contient tous les éléments de façon unique. Les niveaux supérieurs sont donc répété autant de fois que nécessaire. Il intervient dans la configuration des listes de la version descendante.

Plus grand niveau : le plus grand niveau correspond au niveau d’imbrication le plus macro des données. Autrement dit, c’est la première catégorie de choix. Dans la version descendante, c’est le premier niveau à être saisi par l’utilisateur.

Plus petit niveau : le plus petit niveau correspond au niveau d’imbrication le plus micro des données. Autrement dit, c’est la dernière catégorie de choix. Dans la version descendante, c’est le dernier niveau à être saisi par l’utilisateur. Dans la version ascendante, c’est le premier et seul niveau à être saisi par l’utilisateur.

Version ascendante

Grâce à une configuration préalable des listes – par la personne en charge de paramétrer la base de données, l’utilisateur chargé de la saisie des données choisit le plus petit niveau parmi une liste déroulante, et les niveaux supérieurs se remplissent automatiquement.

Que faut-il savoir avant de se lancer ?

Cette version de liste en cascade requiert une configuration rigoureuse, mais relativement facile des choix de saisie (listes), et une entrée correcte de la formule recherchev. Cette version impacte la performance du fichier car avec des listes conséquentes, elle alourdit considérablement le fichier final.

Dans quel cas utiliser la version ascendante ?

  • si l’utilisateur chargé de la saisie des données ne connaît que les éléments du plus petit niveau ou alors qu’il est plus simple de ne remplir que le plus petit niveau (par exemple : pour des listes administratives : il connaît donc la commune mais pas la sous région, ou la région exacte à laquelle elle appartient; ou bien pour des données programmes : il connaît le projet mais pas le programme auquel il appartient, etc.),
  • si les éléments du plus petit niveau sont uniques – dans le cas de deux communes de même noms appartenant à deux pays différents, il faudra choisir l’autre version, ou sinon renommer les deux communes de façon à les différencier (ex: Gao_BFA et Gao_MLI),
  • ET si le nombre d’éléments de la liste de choix est limité (pour éviter d’obliger l’utilisateur de choisir une commune parmi une centaine, et donc perdre du temps à chercher la commune dans la liste déroulante).

Pré-requis :

Votre fichier doit contenir deux feuilles :

  • la feuille de saisie, où les données sont saisies ;
  • la feuille de paramétrage, où se trouve le tableau exhaustif des données.

Quelles sont les étapes ?

Les étapes sont donc les suivantes :

  1. configurer le tableau exhaustif des données,
  2. configurer la feuille de saisie des données.

Étape 1 : Configuration préalable des listes dans la feuille de paramétrage

Il convient dans cette première étape de se placer dans la feuille de paramétrage et de faire le tableau exhaustif des données que vous aurez à saisir. A chaque entrée du plus petit niveau sont ajoutées – dans les colonnes correspondantes – les valeurs associées pour les niveaux supérieurs. Ces dernières pourront donc se répéter plusieurs fois.

La personne responsable de la configuration de la base de données a trois sous-étapes de configuration des listes dans la feuille de paramétrage :

  1. le tableau exhaustif doit être ordonnée de gauche à droite du plus petit au plus grand niveau ;
  2. les niveaux sont adjacents ;
  3. le tableau doit être référencé dans le gestionnaire de noms. Cela permet de garder la référence statique des cellules du tableau et ainsi empêcher les erreurs de cellules lors de la recopie des formules dans l’étape 2  de configuration de la feuille de saisie. Pour cela, sélectionnez les cellules du tableau, puis choisissez l’onglet Formules + groupe Noms définis + commande Définir un nom “tableau_complet”.
  4. la liste du plus petit niveau (ici commune) doit être également référencée dans le gestionnaire de noms. Cela est utile lors de la deuxième étape, pour la configuration de la liste déroulante de saisie du plus petit niveau. Référencer la liste des communes permet donc d’avoir une référence statique à cette colonne du tableau, empêchant ainsi les erreurs de cellule lors de la recopie des formules. Pour cela, sélectionnez la colonne COMMUNE du tableau, puis choisissez l’onglet Formules + groupe Noms définis + commande Définir un nom “liste_commune_exhaustive”.

Une fois la configuration des tableaux finalisée, il est conseillé de protéger la feuille de paramétrage afin d’éviter des erreurs, modifications ou suppressions involontaires.

Étape 2 : Configuration de la feuille de saisie

La personne responsable de la configuration de la base de données a deux sous-étapes de configuration de la feuille de saisie :

  1. Il faut configurer une liste déroulante du plus petit niveau dans la colonne de saisie en autorisant la saisie parmi une liste – liste correspondant à la colonne du plus petit niveau dans le tableau référencé à l’étape 1 / sous-étape 3 (ex: commune pour des données administratives par exemple, projet pour des données programmes, etc.). Pour cela, sélectionnez les cellules du tableau (colonne entière), puis choisissez l’onglet Données + groupe Outils de données + commande Validation des données + autoriser “liste” / source “=liste_commune_exhaustive”.
  2. Il faut entrer la formule recherchev dans les cellules des niveaux supérieurs de droite.

Voici comment elle se décompose :

= RECHERCHEV (ce que vous voulez rechercher, où vous voulez le Rechercher, le numéro de colonne dans la plage contenant la valeur à renvoyer, renvoyez une correspondance approximative ou exacte – indiquée comme 1/vrai ou 0/faux).

Syntaxe exacte :

=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;[valeur_proche])

Dans la majeur partie des cas, le dernier indice est faux.

Ainsi, prenons l’exemple administratif :

  1. Colonne sous-région :

=recherchev(cellule_interet;tableau_complet;2;faux)

Se traduisant par : on va chercher dans le tableau_complet ; la valeur qui se trouve dans la 2e colonne [sous-région] et qui correspond à la valeur saisie dans notre cellule d’intérêt [commune].

  1. Colonne région :

    =recherchev(cellule_interet;tableau_complet;3;faux)

Se traduisant par : on va chercher dans le tableau_complet ; la valeur qui se trouve dans la 3e colonne [région] et qui correspond à la valeur saisie dans notre cellule d’intérêt [commune].

  1. Colonne pays :

    =recherchev(cellule_interet;tableau_complet;4;faux)

Se traduisant par : on va chercher dans le tableau_complet ; la valeur qui se trouve dans la 4e colonne [pays] et qui correspond à la valeur saisie dans notre cellule d’intérêt [commune].

Voici comment intégrer une liste en cascade automatique ascendante grâce à le tableau exhaustif des données et la configuration de la feuille de saisie des données.

Version descendante

Grâce à une configuration préalable des listes – par la personne en charge de paramétrer la base de données, l’utilisateur chargé de la saisie des données commence par choisir le plus grand niveau parmi une liste déroulante, et les niveaux inférieurs associés sont automatiquement proposés. L’utilisateur choisi donc chaque niveau l’un après l’autre.

Que faut-il savoir avant de se lancer ?

Cette version de liste en cascade requiert une configuration rigoureuse des listes, une création de formule indirecte de recherche grâce à la formule index + equiv et la configuration de plusieurs listes déroulantes de saisie. Cette version est lourde dans sa configuration en plusieurs étapes, mais elle a pour avantages majeurs un gain de temps et une simplification de la saisie des données.

Dans quel cas utiliser la version ascendante ?

  • si l’utilisateur en charge de saisie des données a besoin d’un guidage pour saisir les éléments (parmi plusieurs pays, puis plusieurs région du pays choisi, puis parmi plusieurs sous régions d’une région choisie – pour les données administratives),
  • ET si les différents niveaux ont beaucoup de sous niveaux associés.

Prérequis :

Votre fichier doit contenir deux feuilles :

  • la feuille de saisie, où les données sont saisies ;
  • la feuille de paramétrage, où se trouve les tableaux associés des données.

Quelles sont les étapes ?

Les étapes sont donc les suivantes :

  1. configurer la feuille de paramétrage,
  2. créer une formule indirecte de recherche avec la formule index + equiv,
  3. configurer la feuille de saisie des données.

Étape 1 : Configuration des tableaux dans la feuille de paramétrage

Cette étape de configuration consiste à référencer dans Excel les différents niveaux associés. Dans cet exemple administratif à quatre niveaux, vous devez construire quatre tableaux : la liste initiale du plus grand niveau (pays), et les trois tableaux associés de filtrage des données (pays/région, région/sous-région et sous-région/commune). Ces trois tableaux associés permettent de mettre en relation le niveau inférieur au niveau supérieur.

Attention, pour les sous étapes suivantes, il faut que les données soient ordonnées alphabétiquement selon le plus grand niveau. Attention à bien sélectionner les deux colonnes pour le tri des cellules.

La personne responsable de la configuration de la base de données a donc quatre sous-étapes de configuration des listes dans la feuille de paramétrage :

  1. référencer la liste du plus grand niveau (ici pays).

Pour cela, sélectionnez votre liste de pays, puis choisissez l’onglet Formules + groupe Noms définis + commande Définir un nom “liste_pays_unique”.

  1. référencer les trois listes associés en tant que tableaux (pays/région, région/sous-région et sous-région/commune).

Pour cela, sélectionnez votre tableau associé pays/région, puis choisissez l’onglet Insérer + groupe Tableaux + commande Tableau + cocher “mon tableau a des en têtes”. Réitérez cette sous-étape pour les deux tableaux suivants.

  1. référencez les trois listes à valeurs uniques (région, sous région et commune). Le suffixe “_unique” est une bonne pratique signifiant que dans cette liste, les données n’apparaissent qu’une seule fois dans la colonne.

Pour cela, sélectionnez la colonne “région unique” du tableau associé pays/région, puis choisissez l’onglet Formules + groupe Noms définis + commande Définir un nom “liste_région_unique”. Réitérez cette sous-étape pour les deux colonnes à données uniques suivantes (sous région et commune).

  1. référencer les trois listes aux valeurs répétées ou complètes (pays, région et sous région). Au même titre qu’à la sous-étape 3, le suffixe “_complet” est une bonne pratique signifiant que dans cette liste, les données apparaissent plusieurs fois dans la colonne, autant de fois que le niveau inférieur associé existe.

Pour cela, sélectionnez la colonne “pays complet” du tableau associé pays/région, puis choisissez l’onglet Formules + groupe Noms définis + commande Définir un nom “liste_pays_complet”. Réitérez cette sous-étape pour les deux colonnes à données répétées suivantes (région et sous région).

Comme vous pouvez le remarquer dans l’exemple ci-dessus, une bonne pratique est de surligner les cellules afin de visualiser plus facilement les différents groupes, en jouant sur les tons de couleur et l’accentuation. Il est recommandé de nommer la colonne avec le même nom de référencement des plages. Une fois la configuration des tableaux finalisée, il est conseillé de protéger la feuille de paramétrage afin d’éviter des erreurs, modifications ou suppressions involontaires.

Étape 2 : Création de la formule indirecte de recherche

Une fois que vous avez créé vos listes administratives, il faut créer une formule indirecte de recherche par tableau associé avec la formule index + equiv. Cette formule permet de faire référence aux tableaux associés précédemment créés, qui serviront de base dans l’étape 3 lors de la validation des données (saisie des données). Cette étape se déroule dans la feuille de saisie des données. La formule indirecte de recherche se trouvera intégrée dans le gestionnaire de nom et permettra à Excel de faire le lien entre la feuille de paramétrage et la feuille de saisie.

La partie index de la formule indique quelles sont les régions associées au pays choisi, ou les sous régions associées à la région choisie, ou encore les communes associées à la sous-région choisie. La partie equiv de la formule indique où se trouve la cellule d’intérêt: cette cellule d’intérêt correspond au pays, région ou sous région choisi par la liste déroulante.

Pour mettre en place les listes déroulantes en cascade via la version descendante, il n’est pas nécessaire de comprendre entièrement le sens de la formule, mais l’essentiel est d’en maîtriser la logique. De plus, cette étape demande d’être très rigoureux. Une simple virgule ou parenthèse en moins ou en trop empêcherait le fonctionnement de la liste en cascade, ou vous bloquerait lors de la création des listes indirecte (message d’erreur d’Excel).

Voici comment elle se décompose (voir graphiques) :

=INDEX(liste_niveau_inférieur_unique;EQUIV(cellule_interet;liste_niveau_supérieur_complet;0)):INDEX(liste_niveau_inférieur_unique;EQUIV(cellule_interet;liste_niveau_supérieur_complet;1))

La personne responsable de la configuration de la base de données a donc une seule sous-étape de configuration des listes dans la feuille de paramétrage :

  1. référencer les trois fonctions indirectes de recherche dans le gestionnaire de noms.

Pour la fonction de recherche entre le pays et la région, sélectionnez la cellule d’intérêt de la feuille de saisie (la cellule de choix du pays), puis choisissez l’onglet Formules + groupe Noms définis + commande Définir un nom “recherche_pays_region” et entrer la formule “=INDEX(liste_region_unique;MATCH(FEUILLE_SAISIE!$A2;liste_pays_complet;0)):INDEX(liste_region_unique;MATCH(FEUILLE_SAISIE!$A2;liste_pays_complet;1))”.

Réitérer cette sous-étape pour les deux niveaux d’associations suivants (région/sous région et sous région/commune).

Les éléments sont mis en valeur sur les graphiques suivants.

Étape 3 : Configuration de la feuille de saisie

Cette troisième étape se déroule dans la feuille de saisie des données. Ainsi, une fois sur la feuille de saisie, il faut configurer les liste déroulantes dans la colonne de saisie. Il y a deux cas de figure : (i) le premier choix qui n’est pas impacté par le filtrage automatique, et (ii)  les choix suivants qui sont impactés par le filtrage automatique.

La personne responsable de la configuration de la base de données a donc deux sous-étapes de configuration des listes dans la feuille de paramétrage :

(i) Le premier choix est une liste déroulante “simple”  :

  1. Il faut configurer une liste déroulante du plus grand niveau dans la colonne de saisie en autorisant la saisie parmi une liste – liste correspondant à la colonne du plus grand niveau dans le tableau référencé à l’étape 2 / sous-étape 1.

Pour cela, sélectionnez les cellules du tableau (colonne entière), puis choisissez l’onglet Données + groupe Outils de données + commande Validation des données + autoriser “liste” / source “=liste_pays_unique”.

(ii) Les choix suivants sont des listes déroulantes “indirectes” faisant appel à la formule indirecte pour le filtrage automatique :

  1. Il faut configurer une liste déroulante dans la colonne de saisie de la région en autorisant la saisie parmi une liste – liste référencée à l’étape 1 / sous-étape 1.

Pour cela, sélectionnez la colonne région, puis choisissez l’onglet Données + groupe Outils de données + commande Validation des données + autoriser “liste” / source “=recherche_pays_region”. Réitérez pour les niveaux région/sous région (troisième choix) et sous région/commune (quatrième choix).

Vous avez donc intégré une liste en cascade ascendante grâce aux tableaux associés des données, des fonctions de recherche et la configuration de la feuille de saisie des données.

Formules

Référencer :

Onglet Formules + groupe Noms définis + commande Définir un nom “…”

Configurer une liste déroulante :

Sélectionner les cellules, puis choisir l’onglet Données + groupe Outils de données + commande Validation des données + autoriser “liste” / source “…”

recherchev :

=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;[vrai/faux])

index + equiv :

=INDEX(liste_niveau_inférieur_unique;EQUIV(cellule_interet;liste_niveau_supérieur_complet;0)):INDEX(liste_niveau_inférieur_unique;EQUIV(cellule_interet;liste_niveau_supérieur_complet;1))

Références

Support Microsoft :

Version descendante avec INDIRECT + REMPLACER :

Document diffusé sous licence Creatives Commons CC-BY 4.0

Les icônes utilisées dans ce document ont été créés par Larea et businessicons13 de Noun Project

Post a Reply

Your email address will not be published. Required fields are marked *