SEO ist eine Routine. Manchmal müssen Sie sehr trostlose Vorgänge ausführen, z. B. das Entfernen der „Pluspunkte“ in Schlüsselwörtern. Manchmal ist es etwas fortgeschritteneres, wie das Parsen von Meta-Tags oder das Konsolidieren von Daten aus verschiedenen Tabellen. In jedem Fall kostet dies viel Zeit.
Aber wir mögen keine Routine. Wir bieten 16 nützliche Google Sheets-Funktionen, die die Arbeit mit Daten vereinfachen und Ihnen helfen, einige Arbeitsstunden oder sogar Tage zu sparen. (Sicher, Sie wussten nichts über die Existenz einiger Funktionen).
1. IF - Grundlogikfunktion
Dies ist eine der Grundfunktionen, die Ihnen aus Excel bekannt sind. Es hilft bei der Lösung verschiedener SEO-Probleme. Die IF-Formel gibt einen Wert aus, wenn der logische Ausdruck wahr ist, und einen anderen, wenn er falsch ist.
Syntax:
=IF(_;"_";"_")
Ein Beispiel. Es gibt eine Liste von Tasten mit Frequenzen. Unser Ziel ist es, die TOP-3 zu besetzen. Gleichzeitig möchten wir nur solche Schlüssel auswählen, von denen jeder mindestens 300 Besucher pro Monat bringt.
Wir bestimmen, wie viel Verkehr auf die dritte Position im Bio-Sektor fällt. Gehen Sie dazu zu diesem Dienst und stellen Sie fest, dass die dritte Position etwa 10% des organischen Verkehrs ausmacht (diese Zahl ist natürlich ungenau, aber dies ist besser als nichts).
Wir erstellen einen IF-Ausdruck, der für die Schlüssel den Wert 1 zurückgibt, was zu mindestens 300 Besuchern führt, und für die verbleibenden Schlüssel den Wert 0:
=IF(B2*0.1>=300;"1";"0")
Bitte beachten Sie, dass in Zeile 7 die Formel einen Fehler generiert hat, da der Frequenzwert im falschen Format eingestellt wurde. Für solche Situationen gibt es eine erweiterte Version der IF-Funktion - IFERROR.
Bitte beachten Sie: Die Verwendung eines Kommas oder Punktes für Dezimalbrüche in der Formel wird in den Einstellungen Ihrer Tabellen definiert.
2. IFERROR - Weisen Sie im Fehlerfall Ihren Wert zu
Mit dieser Funktion können Sie den Sollwert in der Zelle anzeigen, wenn ein Fehler ausgegeben wird.
Syntax:
=IFERROR( ;" ")
Wir verwenden diese Funktion im oben beschriebenen Beispiel. Stellen Sie im Fehlerfall den Wert „keine Daten“ ein.
Wie Sie sehen können, ist der Wert #VALUE! änderte die Ansicht auf verständlich "keine Daten".
3. ARRAYFORMULA - Dehnen Sie die Formel mit einem Klick nach unten
Beim Arbeiten mit Daten müssen Sie fast jedes Mal eine Formel für alle Zellen in einer Spalte schreiben. Ziehen Sie es mit gedrückter linker Maustaste oder kopieren Sie es - dies ist das letzte Jahrhundert.
Es reicht aus, die ursprüngliche Funktion in die ARRAYFORMULA-Funktion zu packen, und die Formel gilt für alle folgenden Zellen. Wenn Sie das Hinzufügen von Zeilen löschen, funktioniert die Formel weiterhin - ohne Leerzeichen in den Berechnungen.
Syntax:
=ARRAYFORMULA( )
Ein Beispiel. Wir werden die oben beschriebene Formel automatisch für alle Zellen im Bereich anwenden. Dazu schließen wir die ursprüngliche Formel in ARRAYFORMULA:
=ARRAYFORMULA(IFERROR(IF(B2:B*0,1>=300;"1";"0");" "))
Bitte beachten Sie, dass wir anstelle von Zelle B2 den Bereich angegeben haben, für den wir die Formel anwenden (B2: B ist die gesamte Spalte B, beginnend mit der zweiten Zeile). Wenn Sie eine Zelle angeben, funktioniert die Formel nicht.
Life Hack. Drücken Sie nach Eingabe der Hauptformel STRG + UMSCHALT + EINGABETASTE. Die Funktion ARRAYFORMULA wird automatisch angewendet.
ARRAYFORMULA funktioniert nicht mit allen Funktionen. Zum Beispiel ist es nicht kompatibel mit togetLETRANSLATE und IMPORTXML, die wir unten diskutieren werden.
4. LEN - Zählen Sie die Anzahl der Zeichen in der Zelle
Diese Funktion ist besonders nützlich, wenn Sie kontextbezogene Werbeanzeigen erstellen - wenn es wichtig ist, die zugewiesene Anzahl von Zeichen für Überschriften, Beschreibungen, Anzeige-URLs, Quicklinks und Verfeinerungen nicht zu überschreiten.
In der Suchmaschinenoptimierung wird die LEN-Funktion beispielsweise beim Erstellen von Titel- und Beschreibungs-Meta-Tags verwendet. Die Symbolfunktion zählt mit Leerzeichen.
Syntax:
=LEN( )
Ein Beispiel. Wir müssen Titel für alle Seiten der Site erstellen. Wir wissen, dass in den Suchergebnissen ungefähr 55 Zeichen angezeigt werden. Unsere Aufgabe ist es, Titel so zu verfassen, dass die wichtigsten Informationen in den ersten 55 Zeichen enthalten sind. Wir schreiben die LEN-Formel für die gefüllten Zellen. Jetzt wissen wir genau, wann wir uns den angezeigten 55 Zeichen nähern.
5. TRIM - Entfernen Sie Leerzeichen am Anfang und Ende einer Phrase
Wenn Sie Semantik aus verschiedenen Quellen analysieren, enthält sie häufig "Müll" -Elemente - Leerzeichen, Pluszeichen, Sonderzeichen. Berücksichtigen Sie Funktionen, mit denen Sie den Kernel schnell bereinigen können. Einer von ihnen ist TRIM.
Diese Funktion entfernt Leerzeichen am Anfang und Ende der in der Zelle angegebenen Phrase.
Syntax:
=TRIM(, )
Die Funktion entfernt alle Leerzeichen vor und nach der Phrase - egal wie viele es gibt.
6. ERSATZ - Leerzeichen und Sonderzeichen ändern / löschen
Universelle Funktion zum Ersetzen / Löschen von Zeichen in Zellen.
Syntax:
=SUBSTITUTE( ;" ";" "; )
Korrespondenznummer - Die Seriennummer des zu ersetzenden Met-Werts. Ersetzen Sie beispielsweise den ersten Met-Wert. Lassen Sie den Rest. Optionaler Parameter.
Ein Beispiel. Wir haben eine Entladung von Schlüsselphrasen aus Yandex.Wordstat. Viele Schlüssel enthalten Pluspunkte. Wir müssen sie entfernen.
Die Formel sieht folgendermaßen aus:
=SUBSTITUTE(B12;"+";"";)
Was haben wir getan:
- wo gesucht werden soll - eine Zelle mit Daten angegeben;
- "Worauf zu achten ist" - zeigte ein Pluszeichen an, das entfernt werden muss;
- "Was zu ändern ist" - da das Zeichen gelöscht werden muss, haben wir Anführungszeichen ohne Zeichen angegeben. Wenn wir einen Ersatz benötigen würden, würden wir hier den Text schreiben, für den wir das Pluszeichen ersetzen müssen.
- Übereinstimmungsnummer - hier haben wir nichts angegeben, und die Funktion löscht alle Pluspunkte in der Phrase; Wenn wir 1 angeben, löscht die Funktion nur das erste Pluszeichen, wenn 2 - das zweite usw.
7. UNTER - Übersetzen Sie Buchstaben von Groß- in Kleinbuchstaben
Beim Kompilieren von Schlüsseln und Parsen aus verschiedenen Quellen (z. B. aus Meta-Tags von Wettbewerbern) kann sich herausstellen, dass diese Großbuchstaben enthalten. Um die Schlüssel in einer einheitlichen Form zu bringen, müssen Sie alle Buchstaben in Kleinbuchstaben übersetzen. Verwenden Sie dazu die LOWER-Funktion.
Syntax:
=LOWER(, )
8. EINZIGARTIG - Daten ohne doppelte Zellen ausgeben
Die Funktion analysiert den ausgewählten Bereich auf vollständige Einstellungen und zeigt nur eindeutige Linien an - in derselben Reihenfolge wie im ursprünglichen Bereich.
Syntax:
=UNIQUE( )
Ein Beispiel. Wir haben Schlüssel von Yandex.Wordstat gesammelt, Suchtipps und die Wörter der Konkurrenten analysiert. Natürlich werden wir in dieser Reihe von Schlüsseln Duplikate haben. Wir brauchen sie nicht. Wir entfernen sie mit EINZIGARTIG.
Wenn Sie den Müll auf einen Schlag von Müll befreien möchten, verwenden Sie den kostenlosen Wortnormalisierer . Es entfernt doppelte Phrasen (vorbehaltlich Permutationen), ändert die Groß- und Kleinschreibung, entfernt Leerzeichen und Sonderzeichen. Im Wesentlichen funktioniert es genauso wie die Funktionen TRIM, SUBSTITUTE, LOWER und UNIQUE zusammen - mit nur einem Klick.
9. SUCHEN - Finden Sie die Daten in der Zeile
Mit dieser Funktion finden Sie schnell die Zeilen, die Sie mit einer großen Datenmenge benötigen.
Syntax:
=SEARCH(« »; )
Die Funktion wird in verschiedenen Situationen verwendet:
- Markieren Sie Schlüsselbegriffe mit der erforderlichen Absicht (z. B. mit einer Marke oder einem bestimmten Thema, Produkt oder einer bestimmten Dienstleistung).
- Suchen Sie bestimmte Zeichen in der URL (z. B. UTM-Parameter oder ein Fragezeichen).
- Suchen Sie nach URLs für den Linkaufbau (z. B. mit den Worten "Gastbeitrag").
Ein Beispiel. Wir haben eine Liste mit Schlüsseln für den Online-Türladen. Wir möchten alle Markenabfragen finden und in der Tabelle markieren. Verwenden Sie dazu die Formel:
=SEARCH("porta";A1)
In dieser Form zeigt die Formel in Abwesenheit des Wortes "porta" im Schlüssel #WERT an. Wenn sich dieses Wort in der gewünschten Zelle befindet, gibt die Funktion außerdem die Nummer des Zeichens an, mit dem dieses Wort beginnt. Das Ergebnis sieht folgendermaßen aus:
Um das Suchergebnis in einer für uns geeigneten Form zu erhalten, verwenden wir zusätzlich die Funktionen IF und IFERROR:
=IFERROR(IF(SEARCH("porta";A1)>0;"";"0"))
10. SPLIT - Teilen Sie Phrasen in separate Wörter auf
Die Funktion unterteilt Zeichenfolgen mithilfe des angegebenen Trennzeichens in Fragmente.
Syntax:
=SPLIT(;"")
Es ist zu beachten, dass die zweite Hälfte des geteilten Textes die nächste Spalte einnimmt. Wenn Sie also eine enge Tabelle haben, müssen Sie eine leere Spalte hinzufügen, bevor Sie die Formel anwenden.
Ein Beispiel. Wir haben eine Liste von Domains. Wir müssen sie in Domainnamen und Erweiterungen unterteilen. Geben Sie in der SPLIT-Funktion einen Punkt als Trennzeichen an und erhalten Sie das Ergebnis:
11. KONKATENIEREN - Kombinieren Sie die Daten in den Zellen
Diese Funktion kombiniert im Gegensatz zur vorherigen Daten aus mehreren Zellen.
Syntax:
=CONCATENATE( 1; 2;...)
Wichtig: In die Formel können nicht nur die Werte der Zellen, sondern auch die Zeichen (in Anführungszeichen) eingefügt werden.
Ein Beispiel. Im Beispiel mit der SPLIT-Funktion teilen wir die Domänen auf. Lassen Sie uns die umgekehrte Operation mit CONCATENATE ausführen (geben Sie die zusammenzuführenden Zellen an und geben Sie die Trennperiode zwischen ihnen an):
12. VLOOKUP - Suche nach Werten in einem anderen Datenbereich
Die Funktion sucht in der ersten Spalte des Bereichs nach dem Schlüssel und gibt den Wert der angegebenen Zelle in der gefundenen Zeile zurück.
Syntax:
=VLOOKUP(;;_;[])
Beispiel 1. Es gibt zwei Arrays von Schlüsselphrasen, die aus verschiedenen Quellen stammen. Sie müssen die Schlüssel im ersten Array finden, die im zweiten Array nicht gefunden werden. Verwenden Sie dazu die Formel:
=VLOOKUP(A2:A;B2:B;1;false)
Was haben wir getan:
- Stellen Sie den Bereich A2: A ein, aus dem wir die Schlüssel zum Vergleich nehmen;
- Stellen Sie den Bereich B2: B ein, mit dem wir die Schlüssel aus Spalte A vergleichen.
- Stellen Sie die Spaltennummer (1) ein, von der aus wir die Tasten für Übereinstimmungen festziehen.
- false - zeigt an, dass wir keine Sortierung benötigen.
Die VLOOKUP-Funktion wird häufig verwendet, wenn nach Daten auf verschiedenen Blättern oder in verschiedenen Dokumenten gesucht wird.
Beispiel 2. Wir haben Daten von Yandex.Webmaster und der Google Search Console heruntergeladen, um Seiten auf einer Website zu indizieren. Unsere Aufgabe ist es, die Daten zu vergleichen und festzustellen, welche Seiten in einer Suchmaschine indiziert sind, in einer anderen jedoch nicht.
Wir laden die Ergebnisse der Uploads in eine Google Sheets-Datei hoch. Auf einem Blatt befindet sich die URL von Google, auf dem zweiten von Yandex.
In Zelle C2 schreiben wir die VLOOKUP-Funktion. Schließen Sie sofort eine Funktion in ARRAYFORMULA ab, um automatisch herunterzuziehen:
=ARRAYFORMULA(VLOOKUP(A2:A;Yandex!A2:A;1;false))
Jetzt sehen wir sofort, welche Seiten in Google indiziert sind, aber nicht in Yandex.
Was haben wir getan:
- Stellen Sie den Bereich A2: A des aktuellen Blattes ein, aus dem wir den Wert zum Vergleich entnehmen.
- Stellen Sie den Bereich Yandex! A2 ein: Ein Blatt mit Entladung von Yandex, mit dem wir die URL-Werte von Google vergleichen;
- gab die Spaltennummer des Blattes beim Entladen von Yandex an, die Werte, aus denen wir aufrufen, wenn die Werte aus den verglichenen Bereichen übereinstimmen;
- false - zeigt an, dass wir keine Sortierung benötigen.
Wenn Sie die Indizierung bestimmter Seiten in Yandex und Google gleichzeitig überprüfen müssen, verwenden Sie das Tool von PromoPult. Laden Sie die Liste der URLs herunter und führen Sie die Prüfung durch. Wenn die Seite in einer Suchmaschine indiziert ist, hat die Spalte die Nummer 1, wenn nicht - 0.
Lesen Sie dieses Handbuch, wie Sie dieses Tool verwenden und in welchen Situationen es nützlich ist.
13. IMPORTRANGE - Daten aus anderen Tabellen importieren
Mit dieser Funktion können Sie Daten aus anderen Tabellen in die aktuelle Datei einfügen.
Syntax:
=IMPORTRANGE(" ";" ")
Ein Beispiel:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d//"," !A2:A25")
Ein Beispiel. Sie bewerben die Website eines Kunden. Drei Spezialisten arbeiten an dem Projekt: Link Builder, SEO-Spezialist und Texter. Jeder führt einen Bericht. Der Kunde ist daran interessiert, den Prozess online zu überwachen. Sie erstellen dafür einen Bericht mit Registerkarten: "Links", "Positionen", "Texte". Die Daten in jeder Richtung werden mit der Funktion IMPORTRANGE auf diese Registerkarten gezogen.
Der Vorteil der Funktion besteht darin, dass Sie nur auf bestimmte Blätter zugreifen können. Gleichzeitig bleiben die internen Teile von Fachberichten für Kunden unzugänglich.
14. IMPORTXML - Analysiert Daten von Webseiten
Spreading-Funktion zum Parsen von Daten von Webseiten mit XPath.
Syntax:
=IMPORTXML("url";"xpath-")
Hier sind nur einige Verwendungszwecke für diese Funktion:
- Extrahieren von Metadaten aus der URL-Liste (Titel, Beschreibung) sowie von h1-h6-Headern;
- Sammeln von E-Mails von Seiten;
- Analysieren der Adressen von Seiten in sozialen Netzwerken.
Ein Beispiel. Wir müssen den Inhalt der Titel-Tags für die Liste der URLs sammeln. Die XPath-Abfrage, mit der wir diesen Header abrufen, sieht folgendermaßen aus: "// title".
Die Formel wäre:
=IMPORTXML(A2;"//title")
IMPORTXML funktioniert nicht mit ARRAYFORMULA. Kopieren Sie die Formel daher manuell in alle Zellen.
Hier sind andere XPath-Abfragen, die Sie möglicherweise nützlich finden:
- Entladen Sie die Header H1 (und analog h2-h6): // h1
- Beschreibung der Meta-Tags analysieren: // meta [@ name = 'description'] / @ content
- Analysieren von Meta-Tags Schlüsselwörter: // Meta [@ name = 'Schlüsselwörter'] / @ Inhalt
- E-Mail-Adressen extrahieren: // a [enthält ( href , 'mailTo:') oder enthält ( href , 'mailto:')] / @ href
- Extrahieren Sie Links zu Profilen in sozialen Netzwerken: // a [enthält ( href , 'vk.com/') oder enthält ( href , 'twitter.com/') oder enthält ( href , 'facebook.com/') oder enthält ( href , 'instagram.com/') oder enthält ( href , 'youtube.com/'))/@href
Wenn Sie die XPath-Anforderung für andere Elemente der Seite ermitteln müssen, öffnen Sie sie in Google Chrome, wechseln Sie in den Code-Anzeigemodus, suchen Sie das Element, klicken Sie mit der rechten Maustaste darauf und klicken Sie auf XPath kopieren / kopieren.
15. togetLETRANSLATE - Übersetzen Sie Schlüsselwörter und andere Daten
In mehrsprachigen Projekten müssen Sie häufig Schlüsselphrasen übersetzen. Dies geschieht am bequemsten mit der Funktion togetLETRANSLATE direkt in der Tabelle.
Syntax:
=GOOGLETRANSLATE(«»; [_]; [_])
Wenn wir beispielsweise Schlüssel aus dem Russischen ins Englische übersetzen müssen, lautet die Formel:
=GOOGLETRANSLATE(A1;"ru";"en")
Wenn wir vom Englischen ins Russische übersetzen würden, müssten wir die Reihenfolge der Sprachen ändern:
=GOOGLETRANSLATE(A1;"en";"ru")
TogetLETRANSLATE funktioniert nicht mit ARRAYFORMULA, daher strecken wir die Formel wie im Fall von IMPORTXML manuell.
16. REGEXEXTRACT - Extrahieren Sie den gewünschten Text aus den Zellen
Mit dieser Funktion können Sie Text, der mit den von Google unterstützten regulären RE2-Ausdrücken beschrieben wurde, aus einer Datenzeichenfolge extrahieren. Die Syntax regulärer Ausdrücke ist recht kompliziert. Weitere Beispiele finden Sie in der Google-Hilfe .
Syntax:
=REGEXEXTRACT( ;” ”)
Beispiel 1. Wir haben eine Liste von URLs. Domänen müssen extrahiert werden. Hier hilft uns der reguläre Ausdruck:
^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)
Beispiel 2. In der Liste der Schlüsselphrasen müssen Sie Markenschlüssel mit den Wörtern "porta" und "port" finden. Verwenden Sie den regulären Ausdruck, um nach Phrasen zu suchen, die eines dieser Wörter enthalten:
(?i)(\W|^)(porta|)(\W|$)
Wie Sie sehen können, können Sie in den Tabellen die Daten nach Bedarf ausschneiden und ausschneiden. Verstehen Sie einfach die Formeln.