Aujourd’hui je donne la parole à quelqu’un que vous connaissez sûrement : Jambonbuzz (ou plutôt Vincent Lahaye) que vous connaissez sûrement via son blog webmarketing. Il est référenceur professionnel depuis 2005 et actuellement chargé du référencement des sites du réseau Miwim (cashback et codes promos).
La parole m’est donnée sur ce beau blog qui délivre de nombreux conseils, je vais donc tâcher d’être à la hauteur de la qualité rédactionnelle de Florian en continuant ce qu’il a l’habitude de vous proposer : de bonnes astuces !
Aujourd’hui on va s’intéresser à un outil extrêmement puissant pour le SEO que tout le monde a touché au moins une fois dans sa vie : j’ai nommé Excel. Notez que la plupart des astuces que je donne ici peuvent fonctionner sur un tableur google docs mais le temps de calcul est beaucoup plus long.
On commence du plus simple pour aller au plus complexe. Le but est de vous faciliter la vie pour la plupart de vos tâches avec les formules à votre disposition.
1 – NBCAR : sûrement la plus simple et la plus pratique des fonctions. En tapant dans une cellule vide NBCAR(cellule) vous obtiendrez le nombre de caractères dans la cellule concernée.
Ex : NBCAR(A1) : nombre de caractères dans A1
Cette fonction est idéale pour préparer ses titles, ses descriptions et/ou ses inscriptions annuaire. Un passage obligatoire !
2 – CONCATENER ou & : la formule CONCATENER(texte1;texte2) est très pratique car elle vous permet de coller plusieurs textes de différentes cellules dans une seule. Personnellement je préfère l’esperluette qui est bien plus rapide à taper et donne le même résultat.
Exemple : =CONCATENER(A1;A2) ou =A1&A2
Vous pouvez bien sur rajouter du texte dans votre formule : =A1& » texte avec espace au début et a la fin »&A2
Pro Tip : Parfois, vous allez avoir besoin de transformer les données d’une ligne en colonne (ou réciproquement). Vous pouvez facilement le faire grâce au collage de type « transposer » disponible dans les options de collage spécial.
3 – =LIEN_HYPERTEXTE(cellule du lien;Nom) : idéal si vous avez deux colonnes comportant respectivement le nom d’un site et son adresse internet. Avec cette formule, vous pourrez faire en sorte que les titres soient cliquables directement dans votre tableur.
Pro Tip : Si vous souhaitez économiser une colonne, vous pouvez copier coller la formule en cliquant sur « coller des valeurs ». Idéal pour conserver le lien mais sans la formule !
4 – RECHERCHEV(texte cherché;table;numéro colonne;approximation) : La formule pratique ! Imaginons que vous ayez à votre disposition une liste de 10 URL précises et un tableau type export analytics qui vous liste toutes les URL de votre site et le nombre de pages vues ou d’objectifs réalisés.
Il vous suffit de faire une recherche verticale du contenu d’une cellule sur votre matrice de données (le tableau d’export analytics) et de dire la valeur de quelle colonne vous souhaitez afficher. En gros ça donne, si votre URL est en A1, votre tableau en E1;G5 (avec les données recherchées en colonne F) : =recherchev(A1;E1:G5;2;0). Le 0 final sert à chercher votre contenu de façon précise. Vous pouvez aussi mettre « Vrai » ou « Faux » mais le binaire va plus vite 
Pro Tip : Pour éviter de décaler le tableau de données si vous copiez votre formule, appuyez sur F4 lors de sa sélection. Vous figerez les colonnes et elles resteront fixes sur chaque copie de formule. Les cellules figées sont symbolisées par des $ dans la formule : le premier fige la colonne, le second la ligne.
5 – SI(Condition;Si vrai;Si Faux) : La petite soeur de la formule précédente. Ici on cherche à définir si une condition est vrai ou faux pour afficher un contenu selon le résultat. Par exemple si on cherche à savoir si A1=B1 on doit écrire (dans une nouvelle cellule) =SI(A1=B1; »Vrai »; »Faux »)
Pro Tip 1 : Pour dire si A1 est différent de B1 vous pouvez utiliser != ou <> suivant les versions
Pro Tip 2 : Pour afficher une cellule vide, il suffit juste d’écrire « ». Ex : SI(A1=B1; »OK »; »") -> Affichera une cellule vide si A1!=B2
6 – SUPPRESPACE() : la fonction magique d’Excel pour supprimer les espaces vides au début ou a la fin d’une chaine de texte. Généralement la plupart des erreurs d’une recherchev ou d’un si(A=B) viennent de là. Attention toutefois, supprespace ne supprime pas les retours chariot (qui sont desfois symbolisé comme un espace)…
7 – ESTERREUR(formule) : Cette formule est très pratique pour éclaircir votre feuille. Elle se présente sous cette forme : =SIERREUR(formule qui peut faire une erreur;règle à utiliser en cas d’erreur) En cas de valeur non trouvé dans une recherche vous pouvez avoir #N/A. Il est possible de rapidement les supprimer en tapant =ESTERREUR((SI…); »"). Les erreurs seront remplacées par une cellule vide. Evidemment, cette fonction est aussi très pratique quand elle est couplée à un SI=
8 – ESTTEXTE(formule) : Ici on cherche à savoir si la formule va nous ressortir un texte. Sa petite soeur est ESTNUM. =ESTTEXTE(A1) renvoie la valeur VRAI si A1 est constitué de texte
9 – GAUCHE / DROITE (CELLULE;Nombre de caractères) : Idéal pour gérer de grandes chaines de caractères ces formules vous permettent d’extraire un nombre de caractères déterminé. Par exemple pour le texte « essai.htm » en A1, si vous souhaitez virer le .htm il suffit de faire =gauche(A1;5).
Pro Tip : Soyez imaginatif ! Si vous souhaitez supprimmer le dernier caractère d’une colonne comportant des chaines de caractères de longueur différentes il faudra combiner deux formules : =gauche(A1;NBCAR(A1)-1). En gros on choisit le A1 et on calcul son nombre de caractères au total auquel on retire un pour retirer le dernier caractère. Il ne reste plus qu’à copier la formule vers le bas et la taille s’adaptera automatiquement.
10 – STXT (CELLULE;Caractère de départ;nombre de caractères) : Simple en apparence, cette formule peut se relever vite complexe mais faire des miracles. Grâce à cette formule, vous allez extraire une partie d’une chaine de caractère en déterminant la position de départ et le nombre de caractères à extraire. Par exemple pour extraire « nom page » de /categorie/nompage.htm il faudra écrire : =stxt(A1;11;7)
11 – CHERCHE : La formule CHERCHE va être la clé manquante à toutes vos combinaisons automatiques. Cette formule vous permet de renvoyer la position d’un caractère particulier dans une chaîne de caractères.
Ex : Je cherche à me positionner derrière mon paramètre t= sur l’url : www.abc.com/index.php?t=8 placé en A1. Il faut donc faire : =cherche(« t= »;A1)+2…
Bon ok, le +2 c’est vraiment pas sympa. En fait la valeur retournée sera à partir de la première lettre trouvé ; si vous souhaitez vous mettre après la recherche il faut donc additionner par le nombre de caractères tapés dans votre recherche.
Evidement, ces formules sont moyennement efficaces utilisées toutes seules. Leur puissance se retrouve une fois combineés. En cadeau, je vous offre donc cette formule de bourrin qui vous permet d’extraire le mot clé tapé dans Google lorsque vous disposez du referer complet de celui-ci. Normalement vous devez être capable de la comprendre en reprenant tous les points marqués ci-dessus, de quoi épater vos collègues !
=SIERREUR(SIERREUR(STXT(A5;CHERCHE(« q= »;A5)+2;CHERCHE(« &e »;A5)-2-CHERCHE(« q= »;A5));STXT(A5;CHERCHE(« q= »;A5)+2;NBCAR(A5)-CHERCHE(« q= »;A5))); »")
Je traduis :
- Première formule : si la formule suivante est une erreur, alors n’affiche rien
- Deuxième formule : Si la formule suivante est une erreur, chercher une autre formule
- Troisième formule : Extrais-moi le caractère après q= en A5 et calcule moi le nombre de caractère de la variable grâce à l’écart entre deux variable ici &e. En cas d’erreur, gère le cas où le q= est en fin de chaîne de caractères.
Attention, je ne suis pas responsable des rides dues à vos sourcils trop froncés….
Et le deuxième cadeau bonus pour les lecteurs de ce blog, une formule plus facile qui vous servira sûrement un jour : réduire une URL au nom de domaine qui remplace parfaitement la fonction « trim to root » de Scrapebox. Attention, la seul condition du bon fonctionnement de cette formule est que toutes vos URLs finissent par /.
=STXT(A1;CHERCHE(« // »;A1;1)+2;CHERCHE(« / »;A1;CHERCHE(« // »;A1;1)+8)-(CHERCHE(« // »;A1;1)+2))
Cette-fois ci je ne dis rien, ça vous fera votre petit TP 
Si vous arrivez à maîtriser cette petite dizaine de fonctions, vous allez pouvoir faire des miracles avec de longues listes Excel. Evidemment, certains traitements sont plus pratiques en expressions régulières mais les deux outils ne sont pas concurrents, ils sont complémentaires.
Bien sur il existe de nombreuses astuces supplémentaires sur Excel, comme les très puissant tableaux croisés dynamique (Pivot Table). Mais ce sera pour une prochaine fois !
Le petit mot de Florian : Merci à Vincent (ou plutôt Jambonbuzz, parce que ça reste le meilleur pseudo jamais trouvé) pour son excellent billet qui va me demander quelques heures de concentration Reviens quand tu veux !
EDIT : Il y a un petit souci avec les quotes, celles-ci sont remplacées par des guillemets par WordPress, désolé du dérangement…
Articles similaires :
Merci à toi de m’avoir donné la possibilité d’écrire ici, on est bien accueilli donc je reviendrais sûrement
Pour les lecteurs, je peux vous donner un petit défi. Il y a moyen de simplifier la deuxième fonction bonus (trim to root). A vous de la trouver ^^
6 juin 2011 à 10 h 21 min
Pratique tout cela, je réalise à quel point je perdais du temps avant ^^
Merci !
6 juin 2011 à 11 h 02 min
Excellent article sur les possibilités SEO d’un tableau Excel que je sous exploite visiblement. Les fonction proposées sont intéressantes et ouvrent de nouvelles perspectives…
6 juin 2011 à 11 h 07 min
Merci pour cet excellent article. A la lecture, je me rends compte que je sous exploite totalmeent Excel pour le référencement de certains sites, la formule de fin est juste géniale ^^
J’aurais ajouté la fonction GAUCHE et DROITE qui permet de supprimer un certain nombre de caractères en début ou en fin de chaîne, je m’en sers assez régulièrement.
6 juin 2011 à 11 h 14 min
Excellente idée d’article
Et dire que j’utilisai certaines d’entre elles au boulot quotidiennement ^^
6 juin 2011 à 11 h 53 min
En voilà vraiment un excellent article à bookmarker d’urgence.
J’avoue que j’en connaissais une bonne partie, même si certaines m’étaient inconnues.
Si je ne devais retenir qu’une seule formule, ce serait le SI d’Excel, car il permet de presque tout faire.
Et si je devais en ajouter une, je rajouterais la création de tableaux croisés dynamique qui permettent d’associer plusieurs données et/ou plusieurs tableaux en même temps (notamment issus de différents logiciels).
6 juin 2011 à 12 h 30 min
Je ne connaissais pas tout, loin de là, donc merci les gars (sauf pour le mal de crâne avec les formules à la fin, mais bon…)
6 juin 2011 à 15 h 12 min
Merci à tous pour vos encouragements
@Daniel : Le tableaux croisés dynamiques permet trop de choses pour être ajouté ici, une prochaine fois ^^
@Paul : L’article est long je le conçois je te propose donc de revoir le point 9
6 juin 2011 à 15 h 36 min
Un grand merci ! Il n’y a pas d’équivalent OpenOffice, pardon, Libre Office ?
6 juin 2011 à 15 h 45 min
La plupart des astuces, je les connaissais, mais d’autres, je l’avoue, m’étaient inconnus encore.
J’essaye d’ailleurs de faire un petit spinning via excel, j’y suis presque
6 juin 2011 à 15 h 57 min
Bon article, je me sers de certaines de ces fonctions mais euh en anglais ( len, concatenate)
ne pas oublier aussi certaines fonctions basiques avec des rechercher et remplacer par exemple qui sont simples a faire et tres utiles
6 juin 2011 à 17 h 27 min
superbe liste, merci !
6 juin 2011 à 17 h 35 min
Utile pour ceux qui gère leur SEO via des feuilles excel ! Perso ce logiciel m’a toujours paru austère.
Ca fait du bien de voir des articles qui « sortent de l’ordinaire » (surtout par les temps qui courent).
6 juin 2011 à 17 h 36 min
Et bien ah ce que je vois Vincent est un pro d’excel
J’utilise la plupart de ces fonctions qui sont indispensable pour tous bons référenceurs. Et une fois combiné c’est très puissant et je trouve assez intuitif.
)
Ta première fonction bonus est vraiment tip top (et fais assez mal au crâne aussi
Deuxième étapes les tableaux croisé dynamique et la troisième l’export de donnée GA via son API (t’a du boulot
)
Sinon pour les curieux voici trois liens vers d’autres tutos Excel SEO :
- Fonctions Excel et google doc dont les référenceurs raffolent
- Tuto Excel pour le référecement
- Guide Excel pour le SEO
6 juin 2011 à 17 h 50 min
Merci pour cette liste je connaissais déjà la plupart des fonctions.
@MikiWeb
Merci pour les liens proposés en plus et je suis d’accord, on attend la suite maintenant avec les tableaux croisé dynamique.
6 juin 2011 à 17 h 56 min
Merci pour ce très bon article. Je connaissais certaines de ces fonctions mais je n’avais jamais vraiment pensé pour leur utilité en SEO et référencement … Si un second article utilisant les tableaux croisés dynamiques (un bonheur avec Office 2010 !) arrive alors je serais plus que preneur.
6 juin 2011 à 20 h 46 min
Merci beaucoup, je bookmark de ce pas. Effectivement, de nombreuses fonctions sont utiles pour la création ou éclaircir des fichiers CSV.
6 juin 2011 à 22 h 59 min
Merci beaucoup car cela va beaucoup m’aider, on est toujours preneur de bons tuyaux pratiques…c’est rare et très appréciable
7 juin 2011 à 0 h 32 min
Des formules bien pratique. On se rend compte que bien utilisé, Excel peut faire gagner beaucoup de temps, quel que soit le domaine.
7 juin 2011 à 9 h 12 min
Je reviens ici pour remercier les nouveaux commentaires. J’essaierai de faire un point sur les tableaux croisés dynamique même s’il est difficile de le faire comprendre sans passer par les vidéos.
Je pense aussi que des exemples d’utilisations peuvent être une bonne chose, certains ne voyant pas de rapport entre ces formules et leurs usages en ref.
Enfin je voulais remercier tout le monde pour avoir autant tweeter cet article (c’est pas une raison pour arrêter
) et avoir permis à Florian d’être en home de Wikio temporairement.
Comme quoi, l’union fait la force
7 juin 2011 à 12 h 45 min
Moi qui ne suis pas trop « outils bureautique » et ai tendance à chercher ou créer un script pour tous ces usages, ça me donnerait presque envie de rouvrir un tableur !
Rassurez-moi, tout le monde dit Excel par abus de langage, ça marche aussi avec Openoffice / Libreoffice ?
7 juin 2011 à 14 h 35 min
Si j’ai bien compris le fond de votre message, il faut être un bon technicien pour être un bon SEO. Et c’est vrai que c’est ce qui ressort dans la certification CESEO : le référenceur sait avant tout maîtriser le serveur, la programmation et est malin. C’est très important !
7 juin 2011 à 17 h 35 min
@Pierre :
Non, ce n’est pas un abus de langage. La plupart des formules citées ici ne fonctionnent pas de la même façon sur un autre tableur qu’Excel. Pareil, ici ce sont les formules pour Excel en Français, ce ne sont pas du tout les mêmes en anglais (par ex STXT = MID)
J’avoue être un peu déçu par la rapidité d’execution des logiciels libres lorsque les données sont trop importantes, d’où ma préférence pour Excel.
8 juin 2011 à 10 h 30 min
Personnellement je trouve que ces formules facilitent énormément ma tâche d’organisation.
J’espère en lire plus.
Bon courage.
22 juin 2011 à 17 h 25 min
Merci pour les rappels excel
8 juillet 2011 à 13 h 03 min
Salut Florian,
Pour ma version de excel, afin de compter les lettres d’une cellule je dois utiliser la fonction =len(A1) et non =NBCAR(A1)
Merci encore pour ce post bien sympa
22 août 2011 à 18 h 46 min
Bonjour, J’apprécie les exemples proposés pour mieux utiliser Excel et j’aurais besoin d’une formule particulière : avec la condition « SI » à la réponse « VRAI » je voudrais que la cellule qui répond à ce critère se colorie en rouge, soit le libellé soit la cellule elle même.
Est-ce possible? Merci de votre réponse.
G. Leclercq
13 novembre 2011 à 20 h 00 min
Florian, merci bien pour l’article! Dans les bonnes main Excel est vraiment LE premier outil de référencement. Lesdites formules sont encore quelques petites astuces pour automatiser notre travail quotidien et économiser le temps.
Avec ce genre d’articles on comprend jusqu’à quel point on sait mal le fameux Excel:)
Bonne journée!
27 décembre 2011 à 17 h 59 min
J’avoue que j’avais complètement oublié la fonction ESTTEXTE. Intéressant résumé des possibilités d’Excel pour le SEO.
27 décembre 2011 à 19 h 49 min
Excellent (surtout la fonction STXT). C’est exactement ce dont j’avais besoin pour trier mon fichier de prospect
Comme quoi faire de la veille ca apporte pas mal d’infos intéressantes…
26 janvier 2012 à 10 h 50 min
Je cherchais récemment une solution pour extraire le nom de domaine d’une url et je suis tombé sur cet excellent article de Vincent
Pour le petit cadeaux d’extraction du domaine, j’ai trouvé une solution plus simple via le plugin SEO tools pour Excel que j’avais présenté dans un billet:
http://www.1ere-position.fr/blog/fan-de-seo-accro-dexcel-seo-tools-pour-excel-est-fait-pour-toi
Le plugin propose une fonction urlProperty permettant de faire ça plus rapidement ex:
=UrlProperty(A1; »domain ») => domaine.com
ou encore pour retourner les sous domaines le cas échéant
=UrlProperty(A1; »host ») => sub.domaine.com
La liste des paramètres disponibles pour cette fonction très utile
http://nielsbosma.se/projects/seotools/functions/urlproperty/
a+
23 mars 2012 à 10 h 54 min