Deuxième étape dans la conception de notre fichier Excel pour trouver et maitriser les catégories cachées de Netflix : nous allons créer les liens à la volée pour chaque catégorie.
Je vous ais peut être laissé sur votre faim à la fin du premier article. Le jeu de mot n’est pas très fin. Je vous l’accorde.
Cela demande un peut de théorie. Le fichier implémente cinq formules.
- NBCAR
- STXT
- TROUVE
- LIEN_HYPERTEXTE
- GAUCHE
On sort des formules classique SOMMES ou SI. Nous avons besoin du niveau supérieur. Rien de trop compliqué pourtant. Chacune des formules a une syntaxe assez semblable. Elle comprend plusieurs attributs.
Le premier est la cellule qui va faire l’objet d’un traitement. Les autres formules détaillent les conditions dans laquelle cela se passe. La seule exception ici LIEN_HYPERTEXTE qui crée les liens.
Ne paniquez pas. Cela reste très simple. J’y revient dans la fin de l’article.
Le fichier Excel est en téléchargement à la fin de cette étape. Ce n’est pas la version finale. Celle-là, avec un outil de recherche, sera disponible dans le prochain article. On se limite aujourd’hui à générer les liens en série.
Cette semaine nous allons partir de la liste brute récupérée chez PhonAndroid et traiter les données. Cela se fera en deux étapes :
- L’extraction du code permettre à Excel de générer les liens
- La création des liens hypertextes
A la cherche de la taille du code
La liste de codes que récupérée reprend les titres de catégorie puis le code des catégories entre parenthèses.
Je pourrais vous proposer une série de formules imbriquées qui donneraient le lien directement. Cela peut se faire. Mais on peut aussi avancer en mettant un pas devant l’autre et dédier une formule à chaque étape. D’une part, vous me suivrez plus facilement dans mes explications. D’autre part, le jour où il faut réparer ou faire évoluer la formule, une longue formule un cauchemar à analyser.
La première étape consiste à isoler le code de catégorie, ce numéro qui se cache entre deux parenthèses.
Excel ne sait pas lire : c’est un tableur. Ou en tout cas, il a des notions de lecture très limitées.
Cependant, il y a une chose que Excel sait très bien faire : compter. Du coup, nous allons lui demander de compter les caractères.
Première étape : combien de caractères il y a-t-il avant la première parenthèse ? Pour cela, je vais utiliser 3 fonctions :
- NBCAR Pour compter le nombre de caractères
- STXT pour traiter les caractères après la première parenthèse
- TROUVE pour trouver la première parenthèse
Ma première formule me donne le nombre de caractères du code. En effet le nombre de caractères d’un code de catégorie peut varier de 3 à 6 d’après les listes te que j’ai trouvées. Cela n’a rien de scientifique. Je n’ai trouvé nulle part une confirmation de cela. D’autre part, on ne peut pas s’attendre à ce que Netflix documente cette information.
Le code se trouvant après la parenthèse d’ouverture, Je vais demander de rechercher cette parenthèse et de compter les caractères qui la suive pour ensuite soustraire une unité.
La fonction TROUVE me permet de trouver la parenthèse. J’y reviens plus tard. La fonction STXT traite tout ce qui suit.
- Le premier argument de STXT est la donnée brute.
- Le second argument est le nombre de caractère à partir duquel la formule retourne le contenu. C’est la formule TROUVE qui va s’en charger (et ce sera le contenu de l’
- Le troisième argument est arbitrairement fixé sur 10 pour cette étape.
Mais je ne vous ai pas encore présenté la formule TROUVE. Son but est de trouver la parenthèse. Elle va retourner la position du caractère recherché.
Dernier détail, si je laisse la formule telle qu’elle, Excel va aussi compter la parenthèse de fin. J’ajoute un -1 après éviter cela.
=NBCAR(STXT(A5;TROUVE(DEBUT_CODE;A5)+1;10))-1
Vous remarquez que j’utilise le nom de champs DEBUT_CODE que je vous ai demandé de créer lors de la mise en place du fichier. Vous pouvez tirer la formule vers le pas. Nous avons maintenant le nombre de caractères du code catégorie de Netflix pour chacun des catégories.
Tirez la formule vers le bas pour traiter toutes les lignes.
Nous allons maintenant récupérer le fameux code.
Extraction du code Netflix
Mes 2 premiers arguments sont identiques à l’étape précédente.
Le 3e argument est différent. Il s’agit du nombre de caractères que je veux retourner. Le but est simple. Je souhaite retourner uniquement le code secret et non pas la parenthèse qui le suit. Pour cela, mon 3e argument sera le résultat de l’étape précédente.
=STXT(A5;TROUVE(DEBUT_CODE;A5)+1;B5)
Nous connaissons maintenant le nombre de caractères du code secret et le code secret en lui-même.
Nous pouvons maintenant créer les liens vers ces catégories. Vous connaissez peut-être la manière de créer un lien dans Excel vous sélectionnez cellule vous allez dans Insertion, lien et ensuite vous choisissez “autre” dans le menu présenté et vous indiquez le lien vers lequel le texte dans la cellule va diriger.
La création des liens
C’est une fonction très pratique mais dans notre cas vous n’avez pas créé à la main tous les liens c’est pour ça que nous utilisons un fichier Excel pour les créer à la volée. Pour cela, nous allons utiliser la fonction LIEN_HYPERTEXTE. Cette une fonction assez simple avec 2 attributs :
- L’URL
- Le texte du lien.
Dans mon fichier, j’ai indiqué sur les 2 premières ligne 2 valeurs importantes le caractère comme je recherche et la partie fixe de l’url de Netflix.
Mon premier attribut de la fonction LIEN_HYPERTEXTE sera donc le fait de concaténer le contenu de la cellule tu viens url et le code secret que nous avons récupéré de la liste de code.
J’utilise ici la valeur dans le champs URL. Il s’agit de la partie de l’URL commune à tout les liens.
=LIEN_HYPERTEXTE(URL&C5)
Si vous laisser ainsi le fichier est déjà utilisable il suffit de trouver dans la liste la catégorie de Netflix qui vous intéresse et de cliquer sur le lien sur la droite. C’est en tout cas la théorie. En pratique, le résultat n’est pas garanti à 100%. Pour des raisons que je ne connais pas Netflix rediriges parfois notre requête sur la page d’accueil du compte. Mais, rassurez-vous, il y a une solution à ça. Je vous l’explique à la fin d’un tutoriel
Maintenant que vous voyez le fichier plus ou moins fonctionnel nous allons commencer à embellir. Revenons sur la fonction LIEN_HYPERTEXTE. Comme expliqué plus haut, elle a deux 2 attributs. Nous avons déjà utilisé le 1er (l’URL). Regardons maintenant ce que peut faire le 2e qui est le texte du lien. Nous allons donc utiliser ce 2e attribut et lui donner la valeur dans la cellule qui contient le nom de la catégorie sur la même ligne.
=LIEN_HYPERTEXTE(URL&C5)
Le fichier commence à avoir dans la gueule. Non ? Mais il y a moyen de faire encore mieux. Nous n’avons pas besoin du code entre parenthèse dans le texte des liens. Nous allons demander à Excel de l’enlever.
Rappeler vous qu’Excel est meilleur en calcul qu’en lecture. Nous lui demandons dès lors de compter le nombre de caractères depuis la gauche et de ne retourner que le nombre que l’on souhaite. Il y a une formule pour cela. Elle s’appelle GAUCHE.
Pour compter les caractères, nous allons utiliser la formule NBCAR que nous avons déjà utilisé lors de l’extraction du code.
Nous connaissons déjà le nombre de caractères du code. Nous savons aussi qu’il y a une parenthèse avant et après celui-ci. Le nombre de caractères que nous allons demander à Excel de compter est le nombre de caractères de la formule auquel nous allons déduire le nombre de caractères du code ainsi que deux (les parenthèses).
=LIEN_HYPERTEXTE(URL&C3;GAUCHE(A3;NBCAR(A3)-B3-2))
Nous avons maintenant un tableau avec des liens effectifs et compréhensibles pour des humains.
Que faire pour que les liens fonctionnent à tout les coups ?
La première chose à savoir est que les listes trouvées sur le web ne sont pas toujours exemptes d’erreurs. Si certaines références sont fausses, les liens aussi et il ne conduira nulle part.
D’autre part, le fait que le lien soit fait depuis l’application Excel semble poser un problème à Netflix. Dans certains cas, vous arrivez à la page d’accueil de Netflix et non sur la catégorie. Je n’ai pas d’explication à cela. Si vous en avez une, n’hésitez pas à faire entendre votre voix dans les commentaires.
La solution que j’ai trouvée est de sauver le fichier Excel sur OneDrive et d’y accéder avec votre navigateur internet via Excel Online.
Le tout fonctionne. Notre objectif du jour (créer des liens) est atteint)
Maintenant, nous pourrions tirer encore de ces données avec Excel. Plutôt que d’avoir une liste de catégories et de liens conduisant vers sa catégorie pourquoi n’irions-nous pas plus loin ? Pourquoi ne pas créer un moteur de recherche qui pourrait vous proposer ces catégories ? Pourquoi ne pas répliquer le fonctionnement de Google ou lorsque vous commencez à taper une recherche, vous est proposé une série de recherches.
C’est l’histoire d’un prochain article.
Vouvez télécharger le fichier Excel sur Excel Online. N’oublie pas qu’il ne fait que générer les liens.
Je sais que ce genre d’article a un public mixte entre les aficionados de Netflix et d’Excel. L’un peut allez avec l’autre mais pas forcément. Du coup j’essaye de ne pas être trop rapide dans les explication.
N’hésiter pas à laisser vos commentaires si vous aimer (ou pas) mes explications. Vous pouvez aussi vous inscrire à la newsletter pour ne pas manquer le prochain article.