Excel vous trouve les catégories cachées de Netflix

Dans le dernier article je vous ai laissé avec un fichier Excel fonctionnel. Il permet d’atteindre une catégorie de Netflix en cliquant sur un lien.

Si vous n’avez pas déjà vu les étapes précédentes, n’hésitez pas à y faire un tour :

• La mise en place du fichier
• Les liens à la volée vers Netflix

Nous allons maintenant passer à l’étape suivante. Nous allons créer un moteur de recherche dans ce fichier Excel pour trouver une catégorie parmi la liste et se voir proposer un lien vers cette catégorie.

Un tableau dans un tableau Excel

Si vous n’avez pas encore lu les 2 autres articles nous permettant d’arriver à ce point n’hésitez pas à la lire ou les relire :

• Préparation du fichier et des données
• Expression du code de catégorie et création des liens

L’étape d’aujourd’hui va vous permettre d’avoir un fichier fonctionnel et qui permet de chercher dans la base de données. Il y aura encore une autre étape lors d’un article ultérieur traitant de la maintenance de cette liste ou de cette base de données. Cela dit, le fichier est utilisable en tant que tel à la fin de cet article.

Avant cela, autre chose. Nous allons améliorer la présentation du fichier Excel.

Nous avons maintenant un tableau reprenons la liste des catégories sur plusieurs champs. Nous allons le transformer en tableau. Et cela, pour 2 raisons :

• Pour créer plus facilement des formules
• Pour maintenir les données à l’avenir

Pour cela, sélectionnez l’ensemble du tableau des catégories.

Ensuite, rendez-vous dans le menu « insertion ». La 3e option en partant de la gauche l’option tableau.

Lorsque vous sélectionnez cette option, Excel vous demande de confirmer :

• La zone
• Le fait qu’il y ait ou pas des en-têtes.

Nous avons sélectionné toute la zone. Nos en-têtes sont dans la zone et effectivement il y a des en-têtes. Vous pouvez cocher la case.

Après que cela ce choix, Excel reformate tout le tableau. Je ne vais pas rentrer dans les détails du design mais tout peut être customisé. Cependant, ce n‘est pas le sujet de l’article et ce n’est pas la raison pour laquelle je vous suggère fait faire ce tableau

En revanche, nous allons nommer ce tableau. Pour cela, cliquer sur n’importe quelle cellule du tableau que nous venons de créer. Cela fait apparaitre les options du tableau dans les menus d’Excel. Cliquer sur l’onglet “Tableau”.

A gauche de l’écran apparait le nom actuel du tableau : Tableau1. Nous allons le renommer en “Catégories”.

Vous devez penser que le gros du travail va se faire sur l’onglet recherche. Pas du tout.  Mais, nous pouvons déjà mettre en place certains éléments pour faciliter le travail que nous allons faire dans l’onglet « Catégories ».

Au milieu de la feuille, nous allons utiliser deux cellules. La première va tout simplement avoir la mention « Recherchez : »

La seconde sera laissée vide mais nous allons renommer son nom de champ en RECHERCHE

C’est tout pour le moment point nous reviendrons sur l’onglet recherche par la suite.

Recherche à résultats multiples

Revenons maintenant sur l’onglet « catégories ». Nous allons créer une liste de choix dynamiques sur cet onglet de recherche. Pour cela il faut que la liste existe quelque part. Ce quelque part sera sur la page l’onglet des catégories

Pour créer une liste dynamique, il nous faut une fonction dynamique. J’ai nommé : la fonction FILTRE.

Cette fonction est un ajout récent de la version Office 365 (ou maintenant Microsoft 365). Elle disponible dans Excel depuis le début de l’année 2020. Si vous utilisez office 2016 où précédent vous n’avez pas accès à cette formule.

Mais que permet de faire cette fonction ? Si vous utilisez les fonction RECHERCHEV, RECHERCHEH où RECHERCHEX vous connaissez bien un des points négatifs de ces fonctions : elles ne vous retournent qu’une option qu’une réponse. Que faire s’il y a plusieurs réponses à tourner ?

Plusieurs solutions existaient auparavant en utilisant du Javascript. Ce n’est pas la panacée de tout le monde et je dois être honnête ce n’est pas la mienne non plus.

C’est pourquoi cette nouvelle fonction Excel est intéressante. Elle permet de filtrer et de retourner plusieurs réponses.

Le premier argument est la zone de donnée. Pour nous il s’agit de la colonne des liens de notre tableau catégories. Vous voyez maintenant pourquoi il est plus facile d’écrire une formule de la sorte. Vous indiquez le nom du tableau et ensuite, entre crochets, le nom de la colonne de ce tableau qui vous intéresse.

Le second argument est une matrice booléenne. En gros une zone ou une formule permettant de définir une partie des données comme valables (TRUE). Si le résultat de la formule est FALSE, il ne sera pas pris en compte.

Cette matrice booléenne prend forme ici avec la formule CHERCHE. La syntaxe de celle-ci est simple. – 1^er argument : que chercher

• 2^ème argument : ou chercher
• 3^ème argument : la valeur à afficher si la valeur est trouvée (1).

Vous l’aurez bien compris, la formule Excel CHERCHE va afficher un à chaque fois que le mot que l’on recherche.se trouve dans le titre d’une catégorie. Sinon, elle retourne une erreur.

Que faire alors ? Utiliser une autre formule Excel : la formule ESTNUM. Celle-ci va retourner une valeur TRUE s’il y a un numéro et FALSE s’il y a un message d’erreur. J’englobe donc la formule CHERCHE dans une formule ESTNUM.

=FILTRE(Catégories[Lien];ESTNUM(CHERCHE(RECHEHERCHE;Catégories[Lien];1));””)

Maintenant, testons la formule. Rendez-vous sur l’onglet « recherche ». Vous allez y indiquez le mot « série » dans le champ de recherche. Rappelez-vous : celui que vous nous avons nommé à une étape précédente.

Que constatez-vous sur l’onglet catégorie ? La formule Excel FILTRE a débordé sur les cellules en dessous de celle où nous l’avons placée. C’est ce qui change avec les formules dynamiques.

C’est une réelle avancé dans l’utilisation d’Excel.

Elle ne se limitent pas à leur cellule. Elles prennent la place nécessaire à l’affichage de leurs réponses. Dans notre cas vous avez plusieurs choix relatifs au mot-clé « série ».

Maintenant comment faire pour transformer cela en liste de choix. Nous allons utiliser une fonction qui existe depuis des années dans Excel : la validation des données.

Validation des données dynamiques

Retournons sur l’onglet recherche. Nous allons nous occuper du champs RERCHERCHE.

Sélectionnez-le et rendez-vous dans le menu « donnée » et option « validation des données ».

Cette fonction existe depuis des années et permet de restreindre le contenu admissible par une cellule ou une plage de cellule. Imaginez que vous gérez avez un tableau de factures avec une colonne devant contenir le nom d’un fournisseur.

Vous allez vite vous rendre compte que tout le monde n’indique pas chaque fournisseur de la même manière. Certains vont indiquer le nom complet, d’autre vont omettre d’indiquer la forme juridique (S.A., S.à r.l) ou encore vont indiquez une abréviation ou une simplification du nom (exemple : EY au lieu de Ernst & Young).

Bonne chance pour ne pas louper la facture attribuée à EY en recherchant les factures Ernst & Young. Pour le coup Ernst & Young a réellement changé de nom il y a quelques années pour devenir EY. Du coup le problème existe toujours… mais dans le sens inverse.

Dans la fenêtre qui s’affiche, nous allons sélectionner “liste”. Dans la case permettant d’indiquer l’emplacement des données indiquez juste la cellule.

Vous allez me dire que normalement nous devrions indiquer une série de cellules. C’est tout à fait exact à un détail près. Mais nous ne le connaissons pas le nombre de cellules sur lequel vont se répandre la formule Excel FILTRE.

Heureusement, Microsoft a pensé à cela. Il suffit d’ajouter un Joker et la liste va s’étendre en fonction du nombre de réponse. Ce joker c’est le caractère « # ».

Le texte à indiquer dans cette zone est donc :

=Catégories!$F$2#

Cependant, la configuration de la validation des données ne s’arrête pas là. Dans le troisième onglet se cache le message à afficher en case de “mauvaise manipulation”. Par défaut cette option est cochée.

Nous n’en avons pas besoin et devons dès lors la décocher.

RECHERCHEX est semblable à RECHERCHEV mais a été développée 30 ans plus tard. Son utilisation est dès lors bien plus compréhensible pour le commun des mortels. Sa syntaxe est simple

1. Objet de la recherche
2. Colonne ou l’objet de la recherche doit être trouvé
3. Colonne ou la valeur à retourner se trouve
4. Message si pas de réponse
5. Mode de correspondance
6. Mode de recherche

Nous allons utiliser les éléments 1 à 5.

1. Le champ que nous avons nommé « RECHERCHE »
2. La colonne « Lien » du tableau « Catégories »
3. La colonne « Code de catégorie » du tableau « Catégories »
4. L’indication « Pas de référence » (pas de recherche)
5. 0

Cela donne la formule ci-dessous :

=RECHERCHEX(RECHERCHE;Catégories[Lien];Catégories[Code de catégorie];”Pas de référence”;0)

Vous comprenez maintenant pourquoi ne vous ait fait créer le tableau “Catégories” comprenant toutes les données sur Netflix. La formule est plus compréhensible et facile à créer. Excel parle votre langue :-).

Le mode de correspondance 0 est en fait une correspondance parfaite. De cette manière on est sûr que RECHERCHEX retourne la valeur recherchée et pas une autre valeur proche.

Maintenant que nous avons la catégorie, nous allons afficher le lien avec un formule que nous avons vu lors du précèdent article :

=LIEN_HYPERTEXTE(URL&G19;RECHERCHE))

Ici aussi j’abuse des noms de champs. Et j’aurai certainement pu donner un nom à la cellule dans laquelle nous retrouvons le numéro de catégorie.

Cependant, il y a autre chose qui doit vous frapper. Si la cellule « RECHERCHE » n’a pas de valeur, Excel affiche une erreur. Pour éviter cela, nous allons ajouter une condition avec un formule SI.

Notre condition sera la présence de « Pas de référence » dans la cellule retournant la référence de la catégorie. Si cette condition existe, la formule affiche « Choisissez une catégorie ». Sinon elle affiche le lien en relation avec la catégorie.

=SI(G19=”Pas de référence”;”Choisissez une catégorie”;LIEN_HYPERTEXTE(URL&G19;RECHERCHE))

Vous pouvez cliquer sur le lien vers la catégorie Netflix. Il fonctionne !

La page de recherche est maintenant propre et prête à être utilisée.

Si vous n’avez pas tout suivi et qu’un exemple vous parle plus, le fichier Excel final se trouve sur Excel Online.

Bon binge-watching. N’hésitez pas à poser vos questions ou suggestions dans les commentaires de cet article.