Liens de recherches internet par calculs Excel

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


Sujets que vous pourriez aussi aimer :


Liens de recherches Web

Cette petite formation Excel propose de découvrir une astuce intéressante. Il s'agit de construire des URL personnalisées, sur la base de fragments de textes.



L'intérêt est de pouvoir offrir des résultats de recherche Web dynamiques et automatiques, grâce à des mots clés référencés et recoupés. Cette méthode permet d'enrichir les données archivées, par des informations externalisées, constamment actualisées.

Source et objectif
Pour la mise en place de la solution et des démonstrations, nous proposons de réceptionner un classeur Excel. Il offre des données descriptives permettant de questionner le Web.

• Télécharger le classeur lien-hypertexte.xlsx en cliquant sur son lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons un petit tableau de données tout à fait suffisant pour les manipulations. Des produits y sont référencés entre les colonnes B et G et les lignes 5 et 16. Ces articles sont identifiés dans une catégorie, mentionnée en colonne B, et repérés par une marque, inscrite en colonne C. Nous trouvons ensuite la désignation et le prix. Cette désignation décrit le produit par définition. Nous devons exploiter ces mots clés pour produire une recherche Web associée, par calcul Excel. Et il apparaît important d'y inclure l'information sur la marque, par concaténation donc.

Excel propose une fonction tout à fait inhabituelle et fort utile qui se nomme Lien_hypertexte :

=LIEN_HYPERTEXTE(Adresse;[Nom_à_afficher])

En premier argument, nous devons lui indiquer l'adresse à atteindre au clic sur l'information encapsulée dans la cellule. Et pour un affichage explicite, en deuxième argument facultatif, nous pouvons mentionner le texte à inscrire à la place de l'URL.

Pour produire des URL pertinentes et fonctionnelles, il est tout d'abord important de remarquer le mode de construction de certains moteurs de recherche. Nous suggérons d'exploiter les résultats offerts par deux piliers du Web : Amazon et Google.

Nous suggérons de réaliser la recherche sur les mots clés : Philips oneblade, dans l'un et l'autre moteur.

Sur Amazon tout d'abord, vous remarquez que les résultats sur la marque et la gamme sont restitués dans la page de recherche. Ils concernent des rasoirs.



Une Url personnalisée a automatiquement été construite. Si vous consultez la barre d'adresse, vous remarquez la présence d'une partie fixe enchainée par l'énumération des mots clés de recherche.

Pour produire dynamiquement des résultats de recherche depuis une base de données Excel, nous devons nécessairement préfixer chaque Url de la partie statique : https://www.amazon.fr/s?k=. Puis, l'énumération des mots clés doit suivre. Ils doivent être séparés les uns des autres par le symbole plus (+).



Si vous tapez les mêmes mots clés dans la barre d'adresse depuis la page d'accueil du moteur Google, vous obtenez tout d'abord des résultats très proches. Et vous remarquez ensuite que l'URL restituée propose elle aussi une construction remarquable. Elle est préfixée d'une partie statique qui lui est prope : https://www.google.com/search?q=. Puis, comme précédemment, vient l'énumération des mots clés, séparés d'un symbole plus (+).

Construire des URL dynamiques par formule
C'est donc dans le premier argument de la fonction Excel Lien_hypertexte que nous devons reconstruire ces adresses. Il s'agit d'assembler une partie statique stricte à une partie dynamique, nourrie des informations du tableau de données.

Néanmoins, des fonctions de traitement de texte s'avèrent nécessaires. Les désignations des articles sont parfois longues. Nous devons limiter l'énumération des mots clés. Le cas échéant, nous risquons de noyer le thème essentiel, voire de produire des recherches infructueuses. Nous exploiterons donc la fonction Excel Gauche : =Gauche(Texte_à_prélever;Nombre_de_caractères). Elle permet de prélever un bout d'une chaîne de texte en partant du début. Cette information est à renseigner en premier paramètre. En deuxième argument, il suffit alors de lui indiquer sur quelle longueur prélever.

Les espaces doivent être remplacés par des signes plus (+) afin de produire l'enchaînement des mots clés de recherche. Nous exploiterons donc la fonction Excel Substitue: =Substitue(Texte; Texte_à_remplacer; Texte_de_remplacement). Il s'agit de lui passer la chaîne des termes recherchés et concaténés en premier argument. En deuxième paramètre, nous devons indiquer le texte à remplacer, l'espace ici. Puis, en dernier argument, nous devons lui préciser le terme de remplacement, le symbole plus (+) donc.

• Sélectionner la case du premier lien à produire, soit la cellule F5,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir la fonction pour les URL, suivie d'une parenthèse, soit : LIEN_HYPERTEXTE(,
• Entre guillemets, coller la partie statique des recherches Amazon, soit :

'https://www.amazon.fr/s?k='

• Ajouter ensuite le Et commercial (&) pour la concaténation avec la partie dynamique,

Souvenez-vous, il s'agit de réunir la marque et le modèle en remplaçant les espaces par le symbole plus (+).

• Inscrire la fonction de remplacement suivie d'une parenthèse, soit SUBSTITUE(,
• Sélectionner la première marque, soit la cellule C5,
• Enfoncer trois fois la touche F4 du clavier pour ne figer que la colonne, soit : $C5,

Sur la hauteur du tableau, chaque Url devra se nourrir de la marque associée. Donc la cellule doit bien suivre le déplacement en ligne. Mais, pour adapter facilement le calcul à la recherche Google, nous répliquerons la logique sur la droite. Et son Url devra bien pointer sur cette cellule. De fait, la colonne ne doit pas se déplacer.

• Ajouter le Et commercial suivi d'un espace entre guillemets, soit & ' ',

L'objectif est de séparer la marque des mots clés par un espace. Ces derniers doivent être assemblés.

• Ajouter un nouveau Et Commercial (&) pour poursuivre la concaténation,
• Saisir la fonction pour prélever par la début, suivie d'une parenthèse, soit GAUCHE(,
• Cliquer sur le texte à prélever, soit la désignation en D5,
• Pour les mêmes raisons que précédemment, enfoncer trois la touche F4, ce qui donne : $D5,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur de la fonction Gauche,
• Saisir le nombre 25 par exemple et fermer la parenthèse, soit : 25),

Comme nous l'évoquions, nous limitons ainsi l'énumération des mots clés de recherche sur les 25 premiers caractères de la désignation de l'article.

• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer de la fonction Substitue,
• Saisir un espace entre guillemets, soit : ' ',
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Saisir le symbole plus entre guillemets, soit : '+',
• Fermer la parenthèse de la fonction Substitue,
• Taper un point-virgule pour atteindre le second argument de la fonction Lien_Hypertexte,
• Saisir de nouveau la fonction pour prélever du début, suivie d'une parenthèse, soit : Gauche(,
• Cliquer sur la première désignation, soit sur la cellule D5,
• Enfoncer trois fois la touche F4 du clavier, pour la figer seulement en colonne, soit : $D5,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur,
• Saisir le nombre 20 par exemple et fermer la parenthèse de la fonction Gauche, soit : 20),

De cette manière, le lien hypertexte sera décrit par les 20 premiers caractères de la désignation.

• Ajouter le Et commercial (&),
• Puis, inscrire trois points entre guillemets, soit : '...',

De cette façon, nous indiquons à l'utilisateur que l'information sur la désignation est tronquée.

• Fermer la parenthèse de la fonction Lien_Hpertexte,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons la cellule du résultat active.



Le résultat s'affiche aussitôt. Comme vous le constatez, il s'agit bien d'un lien cliquable. Si vous pointez dessus, une info-bulle apparaît. Elle résume la construction de l'url par la formule Excel. Nous demandons bien l'affichage du moteur de recherche d'Amazon : https://www.amazon.fr/s?k=, en lui passant les mots clés de recherche, issus de la base de données, et séparés par des symboles plus (+).

Si vous cliquez sur le lien hypertexte, vous êtes effectivement redirigé vers la page d'Amazon. Et les résultats proposés concordent avec l'article associé, référencé sur la même ligne. De plus, comme cette construction est dynamique, nous n'avons plus qu'à répliquer la formule, pour obtenir les liens adaptés à tous les autres articles.

• Double cliquer sur la poignée de la cellule F5,

Tous les liens hypertextes concordants sont aussitôt construits.



Ils affichent bien les 20 premiers caractères des désignations respectives. Et si vous cliquez sur le lien de la ligne 11 par exemple, vous obtenez une proposition des dentifrices Elmex, selon la marque et la désignation recherchées.



La syntaxe complète de la formule que nous avons bâtie, est la suivante :

=LIEN_HYPERTEXTE('https://www.amazon.fr/s?k=' & SUBSTITUE($C5 & ' ' & GAUCHE($D5;25);'';'+');GAUCHE($D5;20) & '...')

Il nous reste à adapter la recherche pour le moteur Google. Seule la partie statique change, à plus forte raison que nous avons judicieusement figé les cellules impliquées dans le calcul.

• Sélectionner de nouveau le premier résultat de calcul, soit la cellule F5,
• Tirer sa poignée sur la droite jusqu'en cellule G5,

A ce stade, le résultat proposé est fort naturellement strictement identique.

• Dans la barre de formule de la cellule, remplacer la partie statique d'Amazon par la suivante :

https://www.google.com/search?q=

• Puis, valider la modification par le raccourci clavier CTRL + Entrée,
• Ensuite, double cliquer sur la poignée du résultat pour répliquer la logique sur le tableau,

Les désignations affichées sont les mêmes. Les liens de redirection eux, conduisent désormais vers le moteur de recherche Google, en lui passant les mots clés associés.



Et si vous cliquez sur le lien Google en ligne 11, vous obtenez de nouveau des résultats de recherche sur les dentifrices Elmex. Mais cette fois, ils sont délivrés sous une autre forme.