Travaillez avec la sémantique, les liens et l'analyse des pages Web: 16 formules Google Sheets utiles pour les professionnels du référencement

Le référencement est une routine. Parfois, vous devez effectuer des opérations très ennuyeuses, comme supprimer les «avantages» des mots clés. Parfois, c'est quelque chose de plus avancé, comme l'analyse des balises META ou la consolidation des données de différentes tables. En tout cas, tout cela mange des tonnes de temps.

Mais nous n'aimons pas la routine. Nous proposons 16 fonctionnalités utiles de Google Sheets qui simplifieront le travail avec les données et vous aideront à libérer quelques heures ou même quelques jours ouvrables. (Bien sûr, vous ne saviez pas l'existence de certaines fonctions).

1. IF - fonction logique de base

C'est l'une des fonctionnalités de base que vous connaissez Excel. Il aide à la résolution de divers SEO-tâches. La formule IF imprime une valeur si l'expression logique est vraie et une autre si elle est fausse.

Syntaxe:

=IF(_;"_";"_") 

Un exemple. Il y a une liste de touches avec fréquences. Notre objectif est d'occuper le TOP-3. Dans le même temps, nous voulons choisir uniquement ces clés, qui nous apporteront au moins 300 visiteurs par mois.

Nous déterminer quel trafic a une troisième position dans la matière organique. Pour ce faire, nous entrons dans le service et voir que la troisième position provoque environ 10% du trafic à partir organique (bien sûr, ce chiffre est inexact, mais il est mieux que rien).

Se compose une expression qui renvoie une valeur de 1 pour les clés qui permettront au moins 300 visiteurs et 0 - pour les autres touches:

 =IF(B2*0.1>=300;"1";"0") 

Veuillez noter qu'à la ligne 7, la formule a généré une erreur, car la valeur de fréquence a été définie dans le mauvais format. Pour de telles situations, il existe une version avancée de la fonction IF - IFERROR.

Remarque: l'utilisation d'une virgule ou d'un point pour les fractions décimales dans la formule est définie dans les paramètres de vos tableaux.

2. IFERROR - attribuer sa valeur en cas d'erreur

La fonction vous permet d'afficher la consigne dans la cellule en cas d'erreur.

syntaxe:

 =IFERROR( ;"   ") 

En utilisant cette fonctionnalité dans l'exemple décrit ci-dessus. Définissez la valeur en cas d'erreur «pas de données».

Comme vous pouvez le voir, la valeur #VALUE! changé la vue en compréhensible "pas de données".

3. ARRAYFORMULA - étirez la formule vers le bas en un seul clic

Lorsque vous travaillez avec des données, vous devez presque chaque fois écrire une formule pour toutes les cellules d'une colonne. "Tirez" en maintenant le bouton gauche de la souris, ou copiez-collez - c'est le siècle dernier.

Il suffit d'envelopper la fonction d'origine dans la fonction ARRAYFORMULA, et la formule s'appliquera à toutes les cellules ci-dessous. De plus, si vous supprimez l'ajout de lignes, la formule fonctionnera toujours - sans espaces dans les calculs.

syntaxe:

 =ARRAYFORMULA( ) 

Un exemple. Nous appliquerons automatiquement la formule décrite ci-dessus pour toutes les cellules de la plage. Pour ce faire, nous concluons la formule originale dans ARRAYFORMULA:

 =ARRAYFORMULA(IFERROR(IF(B2:B*0,1>=300;"1";"0");" ")) 

Veuillez noter qu'au lieu de la cellule B2, nous avons spécifié la plage pour laquelle nous appliquons la formule (B2: B est la colonne B entière, à partir de la deuxième ligne). Si vous spécifiez une cellule, la formule ne fonctionnera pas.

Piratage de la vie. Appuyez sur CTRL + MAJ + ENTRÉE après avoir entré la formule principale et la fonction ARRAYFORMULA sera appliquée automatiquement.

ARRAYFORMULA ne fonctionne pas avec toutes les fonctions. Par exemple, il n'est pas compatible avec GOOGLETRANSLATE et IMPORTXML, dont nous parlerons ci-dessous.

4. LEN - compte le nombre de caractères dans la cellule

Cette fonction est particulièrement utile dans la préparation des annonces de publicité contextuelle - quand il est important de ne pas empiéter dans le nombre alloué de caractères pour le titre, la description, afficher l'URL, des liens rapides et mises à jour.

En SEO, la fonction LEN est utilisée, par exemple, lors de la composition de balises META de titre et de description. La fonction des symboles compte avec les espaces.

Syntaxe:

 =LEN(  ) 

Un exemple. Nous devons créer des titres pour toutes les pages du site. Nous savons qu'environ 55 caractères sont affichés dans les résultats de recherche. Notre tâche est de composer des titres afin que les informations les plus importantes soient dans les 55 premiers caractères. LEN prescrit formule pour les cellules remplies. Maintenant, nous savons avec certitude lorsque nous approchons des 55 caractères affichés.

5. TRIM - supprimer les espaces au début et à la fin d'une phrase

Lorsque vous analysez la sémantique de différentes sources, elle contient souvent des éléments «poubelles» - des espaces, des plus, des caractères spéciaux. Considérez les fonctionnalités qui aident à nettoyer rapidement le noyau. L'un d'eux est TRIM.

Cette fonction supprime les espaces au début et à la fin de la phrase spécifiée dans la cellule.

Syntaxe:

 =TRIM(,         ) 

La fonction supprime tous les espaces avant et après l'expression - à quoi que ce soit.

6. SUBSTITUTION - modifier / supprimer des espaces et des caractères spéciaux

remplacement Universal / suppression des caractères dans les cellules.

Syntaxe:

 =SUBSTITUTE( ;" ";"  "; ) 

Numéro de correspondance - le numéro de série de la valeur atteinte à remplacer, par exemple, remplacez la première valeur atteinte, laissez le reste. Paramètre facultatif.

Exemple. Nous avons un déchargement de phrases clés de Yandex.Wordstat. La plupart des touches contiennent un signe plus. Nous devons les supprimer.

La formule serait la suivante:

 =SUBSTITUTE(B12;"+";"";) 

Qu'avons-nous fait:

• où chercher - indiqué une cellule avec des données;
• «Que rechercher» - a indiqué un signe plus qui doit être supprimé;
• «Que changer» - puisque le caractère doit être supprimé, nous avons indiqué des guillemets sans caractères à l'intérieur; si nous avions besoin d'un remplacement, nous écririons ici le texte pour lequel nous devons remplacer le signe plus;
• Numéro de la conformité - ici on ne précise rien, et supprimer tous les avantages de la phrase; si nous avons spécifié 1, la fonction supprimerait uniquement le premier signe plus, si 2 - le second, etc.

7. BAS - traduire en majuscules lettres en bas

Lors de la compilation des clés et de l'analyse à partir de différentes sources (par exemple, à partir des balises META des concurrents), il peut s'avérer qu'elles auront des lettres majuscules. Pour apporter les clés sous une forme unifiée, vous devez traduire toutes les lettres en minuscules. Pour ce faire, utilisez la fonction LOWER.

Syntaxe:

 =LOWER(,        ) 

8. UNIQUE - données de sortie sans cellules en double

La fonction analyse la plage sélectionnée pour les prises complètes et affiche uniquement les lignes uniques - dans le même ordre que dans la plage d'origine.

Syntaxe:

 =UNIQUE( ) 

Un exemple. Nous avons récupéré les clés de Yandex.Wordstat, des conseils de recherche, analysé les mots des concurrents. Naturellement, dans ce tableau de clés, nous aurons des doublons. Nous ne les avons pas besoin. Retirez-les en utilisant l'UNIQUE.

Si vous voulez nettoyer le noyau des ordures d'un seul coup, utilisez le mot libre normalisateur . Il supprime les phrases en double (sous réserve de permutations), change la casse, supprime les espaces et les caractères spéciaux. En substance, il fait la même chose que les fonctions TRIM, SUBSTITUTE, LOWER et UNIQUE combinées - en un seul clic.

9. RECHERCHE - trouver les données dans la ligne

Avec cette fonction, vous trouverez rapidement les lignes dont vous avez besoin avec une grande quantité de données.

Syntaxe:

 =SEARCH(« »; ) 

La fonction est utilisée dans différentes situations:

• mettre en évidence des phrases-clés avec l'intention nécessaire (par exemple, de marque ou liés à un sujet, d'un produit ou d'un service);
• Rechercher des caractères spécifiques dans l'URL (par exemple, les paramètres UTM ou un point d'interrogation)
• rechercher des URL à des fins de création de liens - par exemple, contenant les mots «guest-post»).

Un exemple. Nous avons une liste de clés pour le magasin de portes en ligne. Nous voulons trouver toutes les requêtes de marque et les marquer dans le tableau. Pour ce faire, utilisez la formule:

 =SEARCH("porta";A1) 

Mais sous cette forme, la formule en l'absence du mot "porta" dans la clé nous montrera #VALUE! .. De plus, si ce mot est dans la cellule désirée, la fonction inscrira le numéro du caractère par lequel ce mot commence. Résultat ressemble à ceci:

Pour obtenir le résultat de la recherche sous une forme qui nous convient, nous utilisons en outre les fonctions IF et IFERROR:

 =IFERROR(IF(SEARCH("porta";A1)>0;"";"0")) 

10. SPLIT - décomposer des phrases en mots séparés

La fonction divise les chaînes en fragments à l'aide du délimiteur spécifié.

syntaxe:

 =SPLIT(;"") 

Il convient de garder à l' esprit que la deuxième moitié du texte divisé prend la colonne suivante. Donc, si vous avez un tableau serré, vous devez ajouter une colonne vide avant d'appliquer la formule.

Un exemple. Nous avons une liste de domaines. Nous devons les diviser en noms de domaine et extensions. Dans la fonction SPLIT, spécifiez un point comme séparateur et obtenez le résultat:

11. CONCATENER - combiner les données dans les cellules

Cette fonction, contrairement à la précédente, combine les données de plusieurs cellules.

Syntaxe:

 =CONCATENATE( 1; 2;...) 

Important: non seulement les valeurs des cellules, mais aussi les caractères (entre guillemets) peuvent être insérés dans la formule.

Un exemple. Dans l'exemple avec la fonction SPLIT, nous avons divisé les domaines. Faisons l'opération inverse en utilisant CONCATENATE (spécifiez les cellules à fusionner et spécifiez le séparateur - période entre elles):

12. RECHERCHEV - recherche de valeurs dans une plage de données différente

La fonction recherche la clé dans la première colonne de la plage et renvoie la valeur de la cellule spécifiée dans la ligne trouvée.

Syntaxe:

 =VLOOKUP(;;_;[]) 

Exemple 1: Il y a deux phrases clés du tableau, provenant d'une variété de sources. Vous devez trouver les clés du premier tableau qui ne se trouvent pas dans le second tableau. Pour ce faire, utilisez la formule:

 =VLOOKUP(A2:A;B2:B;1;false) 

Ce que nous avons fait:

• définir la plage A2: A, à partir de laquelle nous prenons les clés pour la comparaison;
• définissez la plage B2: B, avec laquelle nous comparons les clés de la colonne A;
• définissez le numéro de colonne (1), à partir duquel nous resserrons les clés pour les correspondances;
• faux - ont indiqué que nous ne avons pas besoin de tri.

La fonction RECHERCHEV est souvent utilisée lors de la recherche de données sur différentes feuilles ou dans différents documents.

Exemple 2. Nous avons téléchargé des données depuis Yandex.Webmaster et Google Search Console sur l'indexation des pages sur un site. Notre tâche consiste à comparer les données et à déterminer quelles pages sont indexées dans un moteur de recherche, mais pas indexées dans un autre.

Nous téléchargeons les résultats des téléchargements dans un fichier Google Sheets. Sur une feuille se trouve l'URL de Google, sur la seconde de Yandex.

Dans la cellule C2, nous écrivons la fonction RECHERCHEV. Immédiatement conclure en fonction ARRAYFORMULA pour tirer automatiquement:

 =ARRAYFORMULA(VLOOKUP(A2:A;Yandex!A2:A;1;false)) 

Maintenant, nous voyons immédiatement quelles pages sont indexées dans Google, mais pas indexées dans Yandex.

Qu'avons-nous fait:

• définir la plage A2: A de la feuille actuelle, à partir de laquelle nous prenons la valeur pour comparaison;
• définir la plage Yandex! A2: Une feuille avec déchargement de Yandex, avec laquelle nous comparerons les valeurs URL de Google;
• indiqué le numéro de colonne de la feuille avec déchargement de Yandex, les valeurs dont nous tirons lorsque les valeurs des plages comparées coïncident;
• false - indique que nous n'avons pas besoin de tri.

Si vous devez vérifier l'indexation de pages spécifiques en même temps dans Yandex et Google, utilisez l' outil de PromoPult. Téléchargez la liste des URL et exécutez la vérification. Si la page est indexé dans un moteur de recherche, la colonne sera le numéro 1 - sinon 0.

Comment utiliser l'outil, et dans quelles situations il est utile de lire ce guide .

13. IMPORTRANGE - importer des données à partir d'autres tables

La fonction vous permet d'insérer dans les données du fichier en cours d'autres tables.

Syntaxe:

 =IMPORTRANGE("  ";"   ") 

Un exemple:

 =IMPORTRANGE("https://docs.google.com/spreadsheets/d//"," !A2:A25") 

Exemple. Vous faites la promotion du site d'un client. Trois spécialistes travaillent sur le projet: link builder, SEO SEO et copywriter. Tout le monde tient un rapport. Le client souhaite suivre le processus en ligne. Vous créez un rapport pour celui-ci avec des onglets: "Liens", "Positions", "Textes". Les données dans chaque direction sont tirées vers ces onglets à l'aide de la fonction IMPORTRANGE.

L'avantage des fonctions que vous accordez l'accès uniquement à des listes spécifiques. Dans le même temps, les parties internes des rapports spécialisés restent inaccessibles aux clients.

14. IMPORTXML - analyser les données des pages Web

fonction « branchue » aux données Parse des pages Web en utilisant XPath.

Syntaxe:

 =IMPORTXML("url";"xpath-") 

Voici quelques utilisations de cette fonctionnalité:

• extraire les métadonnées de la liste URL (titre, description), ainsi que les en-têtes h1-h6;
• collecte de courriels à partir de pages;
• analyser les adresses des pages des réseaux sociaux.

Un exemple. Nous devons recueillir le contenu du titre de tags pour la liste d'URL. La requête XPath, que nous utilisons pour obtenir ce titre, ressemble à: «// titre».

La formule serait:

 =IMPORTXML(A2;"//title") 

ImportXML ne fonctionne pas avec ARRAYFORMULA, donc copier manuellement la formule à toutes les cellules.

Voici d'autres requêtes XPath que vous pourriez trouver utiles:

• têtes de H1 déchargent (et par analogie - h2-h6): // h1
• analyser la description des balises meta: // meta [@ name = 'description'] / @ content
• analyser les balises meta mots-clés: // meta [@ name = 'keywords'] / @ content
• extraire les adresses e-mail: // a [contient ( href , 'mailTo:') ou contient ( href , 'mailto:')] / @ href
• extraire des liens vers des profils sur les réseaux sociaux: // a [contient ( href , 'vk.com/') ou contient ( href , 'twitter.com/') ou contient ( href , 'facebook.com/') ou contient ( href , 'instagram.com/') ou contient ( href , 'youtube.com/'))/@href

Si vous avez besoin de découvrir la demande XPath pour d'autres éléments de la page, ouvrez-la dans Google Chrome, accédez au mode d'affichage du code, recherchez l'élément, faites un clic droit dessus et cliquez sur Copier / Copier XPath.

15. GoogleTranslate - traduire des mots-clés et d'autres données

Dans les projets multilingues ont souvent de traduire des phrases clés. Pour ce faire, vous pouvez utiliser la fonction GOOGLETRANSLATE directement dans le tableau.

Syntaxe:

 =GOOGLETRANSLATE(«»; [_]; [_]) 

Par exemple, si nous devons traduire des clés du russe vers l'anglais, la formule serait:

 =GOOGLETRANSLATE(A1;"ru";"en") 

Si nous traduisons de l'anglais en russe, il serait alors nécessaire de changer l'ordre des langues suivantes:

 =GOOGLETRANSLATE(A1;"en";"ru") 

GoogleTranslate ne fonctionne pas avec ARRAYFORMULA, de sorte que, comme dans le cas de ImportXML, étendre une formule à la main.

16. REGEXEXTRACT - extraire le texte souhaité des cellules

Cette fonction vous permet d'extraire du texte décrit à l'aide d'expressions régulières RE2 prises en charge par Google à partir d'une chaîne de données. La syntaxe des expressions régulières est assez compliquée, vous pouvez trouver plus d'exemples dans l' aide de Google .

syntaxe:

 =REGEXEXTRACT( ;” ”) 

Exemple 1. Nous avons une liste d'URL. Besoin d'extraire des domaines. nous contribuerons ici l'expression régulière:

 ^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+) 

Exemple 2. Dans la liste des phrases clés, vous devez trouver des clés de marque avec les mots "porta" et "port". Pour rechercher des phrases contenant l'un de ces mots, utilisez l'expression régulière:

 (?i)(\W|^)(porta|)(\W|$) 

Comme vous pouvez le voir, les tableaux peuvent être des données de coupe et coupe que vous aurez besoin d'assez pour comprendre les formules.