Как сделать рейтинг в гугл таблице

Добавил пользователь Дмитрий К.
Обновлено: 04.10.2024

9 функций Google Таблиц, которые пригодятся SEO-специалисту

Google Spreadsheets (Таблицы) — это удобный и бесплатный сервис для работы с таблицами в онлайн и офлайн режиме. Инструмент обладает широким функционалом, благодаря чему пользуется популярностью у специалистов различных сфер, в том числе SEO. С помощью Google Таблиц можно быстро и удобно работать с большим количеством данных, автоматизировать рабочие процессы, собирать информацию с сайтов и многое другое — главное знать, какие формулы использовать для этого.

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

  • 1. REGEXEXTRACT
  • 2. VLOOKUP
  • 3. IMPORTXML
  • 4. IFERROR
  • 5. QUERY
  • 6. SPLIT
  • 7. UNIQUE
  • 8. IMPORTRANGE
  • 9. SPARKLINE

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

1. REGEXEXTRACT

Эта функция спасает, когда вам нужно вытащить из ячейки с текстом только один фрагмент, например, из URL-адресов извлечь только доменное имя.

Функция REGEXEXTRACT: пример применения

В данном случае text — это будет название ячейки, а regular_expression (регулярное выражение) будет следующим:

Функция REGEXEXTRACT: пример применения

Чтобы извлечь домены из других ячеек, переместите курсор в правый угол ячейки с формулой и потяните вниз. И вуаля — всего за несколько минут мы извлекли нужные фрагменты.

Также можно воспользоваться функцией =ARRAYFORMULA, которая работает с массивом данных. Чтобы её использовать, оберните свою формулу в неё:

Функция REGEXEXTRACT: пример применения

Чтобы извлечь конкретный текст, добавьте в формулу этот текст, например:

Вы можете применять и другие регулярные выражения → эта шпаргалка по синтаксису вам поможет.

2. VLOOKUP

VLOOKUP (ВПР) для Google Таблиц — это одна из самых популярных функций среди SEO-специалистов. Она выполняет поиск в строках по вертикали (поэтому и ВПР — вертикальный просмотр) и возвращает значение из определённой ячейки в указанном диапазоне. С помощью VLOOKUP можно переносить и объединять данные из одной таблицы в другую, а также проверять наличие значения в других наборах данных.

Например, у вас есть таблица с двумя вкладками, на которых есть данные. Вам необходимо перенести данные из второй вкладки в первую.

Функция VLOOKUP (ВПР): пример применения

Функция VLOOKUP (ВПР): пример применения

Функция VLOOKUP (ВПР): пример применения

Также есть аналогичная функция, только для горизонтального поиска (HLOOKUP), если вдруг у вас всё повернётся на 90 градусов.

3. IMPORTXML

IMPORTXML — это функция, которая позволяет импортировать данные формата XML, HTML, RSS, CSV и других. С её помощью вы можете парсить данные с сайтов не покидая Google Таблиц, например, извлекать метаданные или контактные данные со страниц.

Рассмотрим на примере. Допустим, нам нужно извлечь заголовок страницы. Тогда формула будет выглядеть так:

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

Функция IMPORTXML: пример применения

Также эту формулу можно использовать для извлечения абсолютно разных данных, главное — подобрать XPath. Например, чтобы узнать количество внешних ссылок со страницы, на место xpath_запрос подставьте "//a".

А чтобы быстро проверить коды ответа сервера страниц, подставьте xpath "//div[@class='ip-entry']//big" и введите URL онлайн-сервиса, который определяет код ответа сервера страниц. Формула будет выглядеть так:

Функция IMPORTXML: пример применения

Да, можно и коротенькую функцию написать для этого, но об этом в другом посте ;)

4. IFERROR

Иногда формулы в Google Таблицах возвращают ошибку, и здесь пригодится функция IFERROR (ЕСЛИОШИБКА), которая позволяет задать значение по умолчанию, если значение содержит ошибку.

Поэтому в случае отсутствия данных мы заменим значение на 0, применив формулу с функциями ВПР и ЕСЛИОШИБКА:

Функция IFERROR: пример применения

5. QUERY

QUERY — функция, позволяющая запрашивать нужные данные из таблицы, по структуре запросов немного похожая на SQL.

  • данные — это диапазон, по которому будет выполняться запрос;
  • запрос — запрос на языке API визуализации Google;
  • заголовки — количество строк с заголовками в верхней части раздела данных. Является необязательным аргументом.

Рассмотрим пример. У нас есть таблица с результатами данных сканирования из Netpeak Spider.

Функция QUERY: пример применения

Нам необходимо из неё вытащить только те страницы, у которых описания длиной менее 50 символов или заголовки — менее 40. Для этого применяем формулу:

Как посчитать среднее значение в Google таблицах?

Когда мы ведем таблицу с данными по продажам — появляется нужда вывести среднее арифметическое столбцов со значениями. Это можно сделать 3 разными способами: формулами и встроенным функционалом в гугл таблицах.

Считаем среднее арифметическое через встроенный функционал в Google таблицах

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

Как посчитать среднее значение в Google таблицах через встроенный функционал?

Этот способ подходит когда данных мало, либо вы хотите одноразово узнать среднее значение по данным. Но что, если данные все время дополняются и надо постоянно мониторить ситуацию?

Скрипты google для автоматизации, создания документов, мини CRM.

воскресенье, 22 октября 2017 г.

Связные списки в google таблицах

Связные списки google - нетривиальная задача. Она не решается с помощью формул google таблиц (я надеюсь, что это только пока). Необходимо обращение к скриптам google.


Подготавливаем лист таблицы из которого будут браться значения для списков. Назовём, например, help.


заполняем нужными нам данными


Создаём лист в котором будет выполняться выбор Глав, Параграфов, Разделов.


В Редакторе скриптов вставляем скрипт

16 комментариев:

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

[adace-ad >Одним из правил хорошего тона в современном мире, помимо умения открывать пиво зажигалкой, считается знание основ Google Sheets. Поэтому давай-ка научимся правильно использовать и читать данные в Гугл таблицах. Неправильно использовать ты и сам сможешь научиться.

Основы

Создание и открытие файла


Как сохранять прогресс?



Совместимость с Excel



Можно откатиться на более раннюю версию


Откроется окно, где вы можете не только выбрать версию, но и увидеть её предварительный просмотр.


[adace-ad >Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки. VLOOKUP Имеет четыре аргумента:

  • Запрос — то, что мы хотим найти в столбце
  • Диапазон — ячейки, внутри которых мы будем искать
  • Индекс – номер столбца (от начала диапазона), где нужно найти то, что мы вписали в запрос
  • Сортировка — логическое значение (истина/ложь). Чтобы вернуть точные совпадения ставьте 0

Как она работает?


Далее в ячейку B2 вставляем формулу: =VLOOKUP(A2;’Данные’!A1:C24;3;0) . Это позволит нам отображать 3 столбец (т.е. выручку) у выбранной нами пиццерии



Неточный поиск (наиболее близкое значение)


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

Полная формула: =VLOOKUP(D6;A6:B10;2;1)

Поиск по нескольким условиям (по двум столбцам)


Теперь пишем формулу в ячейке G2: =ArrayFormula(VLOOKUP(E2&F2;;2;0)) . Первый параметр — значения которые мы ищем, там ссылаемся на выпадающие списки E2 и F2. Далее идёт массив (ArrayFormula), который здесь используется как раз для объединения столбцов. Бэкслеш используется для отделения столбцов.

Функции INDEX и MATCH

Проблема функции ВПР (VLOOKUP), как и его аналога ГПР (HLOOKUP) в том, что они ищут данные по одному столбцу или строке. Но что делать, если нам нужен поиск по нескольким параметрам, т.е. и по строке и по столбцу одновременно? Функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца — это функция INDEX , а в качестве её параметров часто используется MATCH (русскоязычный аналог — ПОИСКПОЗ).

Поиск по нескольким параметрам (ВПР 2D)

Откроем наш пример:



Теперь нам нужно, чтобы при выборе параметров нужные данные подставлялись. Как и писали выше, пишем функцию INDEX, внутри которой мы будем используем функцию MATCH.

Полная формула: =INDEX(B4:E12;MATCH(G5;B4:B12;0);MATCH(H5;B4:E4;0))


Теперь все данные подставляются. Как это работает? Первый параметр в функции INDEX — это массив поиска. Мы выбрали всю таблицу (вместе с названием столбцов) — B4:E12. Далее нужно указать еще два аргумента — строку и столбец. В обоих случаях мы используем для этого функцию MATCH

Важно!


Формула = INDEX ( A2:C24 ; MATCH ( F5 ; A2:A24 ; 0 ) ; MATCH ( G5 ; B2:B24 ; 0 ) ) выдаст ошибку по третьему параметру в формуле. Чтобы использовать оба параметра при поиске см. раздел VLOOKUP чуть выше.

Функция FILTER

[adace-ad >Функцией FILTER можно быстро отфильтровать нужные данные с таблицы, чтобы отобразить их в другом листе или таблице. FILTER отображает только те строки или столбцы в диапазоне, которые соответствуют заданным условиям. Так же можно быстро сделать выпадающий список, который при выборе из него элемента отобразит нужные данные.

Фильтр по нужным данным

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



Как это работает? В примере функция FILTER содержит два аргумента (может быть несколько условий, у нас оно одно)

Фильтр через выпадающий список



У нас получится выпадающий список с партнёрами:


Теперь в ячейке H4 вставляем функцию: = FILTER ( D2:F30 ; D2:D30 = H3 ) .

После этого у нас будут фильтроваться данные в зависимости от выбранного элемента в ячейке H3



Фильтр с чекбоксами


Дальше с помощью функции TRANSPOSE (она используется для транспортировки набора данных в другие ячейки) мы спарсим названия столбцов в наш перечень. Полная функция: =TRANSPOSE(‘Лист1’!A1:C1)



Огромное количество примеров функции ФИЛЬТР вы можете открыть в сборнике сайта Contributor (открыть в Goolge таблицах)

[adace-ad >Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их. Сложность в том, что нужно знать синтаксис SQL, но самую базовую выборку делать очень легко. В сети много примеров бездарного использования функции QUERY, мы же сейчас сделаем крутой выпадающий список с фильтрацией данных

Подготовка. Парсим данные. Делаем выпадающий список.










Основная часть. Делаем запросы. Фильтруем данные.


С помощью формулы IF сделаем заготовку для нашего фильтра.

В итоге у нас получится выбор данных при изменении нашего выпадающего списка для каждого значения


А сейчас объединим эти две формулы, чтобы был один полноценный запрос:


Теперь нужно это всё перенести в функцию query, чтобы выпадающий список фильтровал запросы. Для этого нужно удалить часть запроса в query (на скрине как раз выделена эта часть):


Результатом станет то, что при выбора Партнера и пиццерии (можно оставить все), функция query будет показывать нам отфильтрованные данные из основной таблицы:


Откроем наш пример таблицы:


Функция Imortrange имеет всего два аргумента: ссылка на таблицу (ключ) и диапазон , который будем копировать. Ключ — вот эта часть ссылки на таблицу с которой вы хотите скопировать данные. Его можно скопировать из адресной строки:


Это и будет ключом. Дальше идёт диапазон. Тут собственно мы копируем либо всю таблицу, либо её часть. В нашем примере мы копируем данные на соседний лист, но всё это сработает и в случае, если вы будете это делать на совершенно новую/другую таблицу.


Внутренняя ошибка импорта

Если возникает эта ошибка, значит у вас есть некоторая опечатка в формуле, хотя она по всем правилам написана правильно. Как такое получается?



Функция JOIN

[adace-ad >Объединяет значения в массиве данных, добавляя между ними разделитель. Два обязательных аргумента: разделитель и массив (или одно значение). Самый просто пример использования ниже:


Какой самый простой способ объединения данных различных ячеек в одну? Для этого можно использовать функцию JOIN, которая объединит нужные нам ячейки, а с помощью разделителя придадим удобочитаемый вид.




Где взять еще примеры?

Огромное количество уникальных примеров вы можете найти в канале у Рената Шагабутдинова. Этот человек настоящий сенсей и виртуоз Гугл таблиц, автор трех книг, преподаватель в онлайн-школе Skillbox и человек, который автоматизировал бизнес-процессы в МТС и МИФ. Это действительно уникальная обновляемая (!) коллекция различных скриптов, формул. Обрати внимание, что нужен Телеграм. Ссылки в таблице ведут на канал в телеге, поэтому обзаводись. Открыть примеры в Google Sheets


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

Читайте также: