Google Таблицы поддерживает множество функций, таких как сбор данных с веб-сайтов, создание полуавтоматических рабочих процессов SEO, управление большими наборами данных, автоматизация отслеживания информационных кампаний и многое другое.

В этом руководстве я познакомлю вас с некоторыми из этих встроенных функций, которые позволяют выполнять одну из наиболее распространенных задач SEO — получать наиболее часто используемые ключевые слова из заданного набора заголовков .

Прежде чем я начну, я хочу, чтобы вы поняли, как отслеживание количества ключевых слов может помочь на странице SEO (поисковая оптимизация). При правильном использовании он может помочь привлечь поисковые системы и повысить видимость страницы в результатах поиска. Игнорирование этого может привести к тому, что страница сама не попадет в рейтинг, что приведет к тому, что она не появится в первых результатах поиска. Как владелец контента вы не хотите попадать в такую ​​ситуацию. Давайте сразу же приступим.

Часто используемые функции

Есть несколько разных способов сделать это, и они включают комбинацию встроенных функций. Я перечисляю здесь наиболее часто используемые функции, некоторые из которых я буду использовать в следующих ниже примерах.

ФУНКЦИЯ ИСПОЛЬЗОВАНИЕ
ARRAYFORMULA Позволяет отображать значения, возвращаемые из формулы массива, в несколько строк и / или столбцов и использовать функции без массива с массивами
CONCATENATE Добавляет строки друг к другу
COUNTA Возвращает количество значений в наборе данных.
IFERROR Возвращает первый аргумент, если это не значение ошибки, в противном случае возвращает второй аргумент, если он присутствует, или пустое поле, если второй аргумент отсутствует.
JOIN Объединяет элементы одного или нескольких одномерных массивов с использованием указанного разделителя.
QUERY Выполняет запрос языка запросов API визуализации Google по данным
REGEXEXTRACT Извлекает совпадающие подстроки в соответствии с регулярным выражением
REPT Возвращает указанный текст, повторяющийся несколько раз.
SPLIT Делит текст вокруг указанного символа или строки и помещает каждый фрагмент в отдельную ячейку в строке
TRANSPOSE Транспонирует строки и столбцы массива или диапазона ячеек

Чтобы ознакомиться с более подробным руководством по использованию этих функций, прочтите некоторые из наших статей на эту тему.

Использование функции QUERY

Функция QUERY () может быть самой мощной из функций, перечисленных выше, и в сочетании с несколькими другими функциями она позволяет нам эффективно управлять данными и манипулировать ими, используя возможности SQL. Давайте посмотрим, как именно, на примере.

Используя функцию IMPORTFEED (), я выбрал 25 заголовков дневных новостей из Google News в формате ATOM (альтернатива веб-каналу RSS).

 

Посмотрите на строку формул, чтобы узнать, как использовать функцию IMPORTFEED (). Первый вход относится к исходному каналу, из которого вы хотите получить данные. Вторая запись, «заголовок элементов», описывает, что вы хотите извлечь из ленты, а затем в конце вы указываете количество записей, которые вы хотите импортировать. На нашем веб-сайте вы можете найти подробную статью о том, как использовать функцию IMPORTFEED () и другие распространенные методы очистки веб-страниц.

Наша цель здесь — определить наиболее часто встречающиеся ключевые слова в сегодняшних новостях. Я перешел в ячейку D2, чтобы вставить свою формулу:

 

Прежде чем перейти к результату, позвольте мне разбить формулу на более мелкие части. Я начну с самой внутренней функции, используемой здесь, это JOIN ().

  • JOIN (”“, B2: B) объединяет все записи в столбце B, разделенные пробелом. Обратите внимание, что мы указали диапазон B2: B, чтобы в будущем количество записей новостей обновлялось.
  • SPLIT (<результат сверху>, ”“) разбивает большой кусок текста из вывода JOIN () на слова, разделенные пробелом, и в этом случае каждое слово обрабатывается как отдельный объект.
  • TRANSPOSE (<результат сверху> & {“”; ””}) преобразует каждое из слов в предыдущем диапазоне в одну запись столбца (каждое слово обрабатывается как точка данных в одном столбце слов).
  • И, наконец, QUERY (<результат сверху>, запрос) выполняет требуемую операцию. Обратите внимание, что запрос заключен в кавычки. Результат TRANSPOSE () рассматривается как данные, по которым будет выполняться запрос. Давайте разберем запрос на более мелкие части, чтобы лучше понять:
    • select Col1, count (Col2) — выбирает два столбца, где Col1 — это список слов, а count (Col2) — агрегирование счетчика, выполняемое для первого столбца
    • group by Col1 — выполняет агрегирование счетчика на уровне Col1 (в данном случае ключевого слова)
    • упорядочить по количеству (столбец 2) desc — сортирует результаты в порядке убывания на основе количества ключевых слов (столбец 2).
    • limit 25 — выбирает только первые 25 строк
    • метка Col1 ‘Word’, count (Col2) ‘Frequency’ — имена столбцов результатов как ‘Word’ и ‘Frequency’

Результат, если вы правильно выполнили шаги, будет выглядеть следующим образом:

 

Чтобы еще больше уточнить результат, вы можете добавить несколько условий:

  • Преобразуйте все содержимое в ВЕРХНИЙ футляр для легкого и надежного поиска
  • Добавьте в запрос предложение, чтобы исключить местоимения и союзы

Окончательный результат будет выглядеть так:

 

Использование функций INDEX, MATCH и COUNTIF

Если вам удобнее работать с общими функциями Excel, чем с QUERY (), вы можете использовать INDEX () и MATCH (), чтобы найти ключевое слово с наибольшим количеством вхождений. Единственное предостережение здесь заключается в том, что он принесет вам одно ключевое слово, которое встречается чаще, чем любое другое. А не группу ключевых слов с соответствующими счетчиками, как при использовании QUERY (). Давайте посмотрим на пример.

Воспользуемся той же функцией IMPORTFEED (), чтобы получить заголовки статей на сайте.

 

Когда у вас есть заголовки, переходите к получению списка ключевых слов, как в предыдущем примере. Мы будем использовать:

= TRANSPOSE (SPLIT (JOIN (»«; B2: B); »«))

чтобы получить список ключевых слов в виде записей в одном столбце (в данном случае столбец C). Используйте функцию UPPER () в столбце D, чтобы убедиться, что все ключевые слова указаны в верхнем регистре, обеспечивая безошибочный подсчет частоты:

= UPPER(C2) 

Ниже приводится то, что вы получите, запустив две перечисленные выше функции. Теперь наша цель — найти ключевое слово, которое чаще всего встречается в столбце D.

 

И мы достигаем этого, используя пару формул, приведенных ниже:

Наиболее распространенное слово 

= ARRAYFORMULA( INDEX( D2:D81, MATCH( MAX( COUNTIF( D2:D81, D2:D81)), COUNTIF(D2:D81, D2:D81), 0)))

Частота

= ARRAYFORMULA (MAX (COUNTIF (D2: D81, D2: D81)))

Разобьем эти две формулы на более мелкие компоненты:

  • COUNTIF (D2: D81, D2: D81) передает набор слов из D2: D81 в диапазон D2: D81 и подсчитывает появление каждого слова
  • MAX () выбирает максимально возможное количество
  • INDEX() выбирает слово, которое соответствует слову с наибольшим количеством

При применении двух функций вы должны получить результат, как показано ниже:

 

Это почти все. У вас есть все необходимое, чтобы начать работу с подсчетом ключевых слов в Google Таблицах.

Я бы порекомендовал поиграть с приведенными выше формулами и другими многочисленными формулами Google Таблиц и посмотреть, что вы можете придумать. 

Рекомендуем! Пройти очень эффективный Онлайн-курс «Гугл-таблицы» и «Excel» для начинающих.