Что значит в гугл таблицах

Гугл таблицы: фильтры, списки, формулы

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

Основы

Если ты новичок в этом бойцовском клубе, то держи несколько правил работы, с которых стоит начать, а уже потом научимся подставлять значение из списка и использовать query вместо ВПР. Если ты играешь уже на уровне «Ветеран», то свапай или скроль дальше.

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

Создать новую таблицу ты можешь, открыв Google Docs в браузере, либо перейти на https://docs.google.com/spreadsheets/. Ранее созданные таблицы будут отображаться списком ниже

Создавать новые и открывать созданные ранее таблицы можно на главной странице сервиса и через «Google Диск» . По умолчанию, созданные через сервис Google Sheets файлы, сохраняются в корне Диска

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

Вся ваша работа, все изменения будут сохраняться автоматически. Об этом вас уведомит информационное сообщение справа от меню. Для этого, естественно, нужен доступ в интернет.

Помимо того, что ваш файл будет отображен в списке недавно просмотренных/созданных, физическая копия дублируется на Goolge Диск — https://drive.google.com/drive/.

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

Если у тебя на работе только допотопный Excel, а на домашнем ноуте прогрессивный Google Sheets, то не переживай — совместимость (в т.ч. обратная) есть. Можно загрузить .xls-файл на Google Диск и просто открыть его в Google таблицах. Для этого в Google Диске нажмите «Создать» и «Загрузить файлы» После загрузки xls-файла откройте его и выберите «Открыть с помощью Google Таблицы»

И наоборот, чтобы Гугл таблицу сохранить так, чтобы потом работать с ней в Excel, в меню «Файл» — «Скачать» выберите файл xlsx или csv

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

Если вам нужно вернуть более старую версию таблицы, то это можно сделать — в Гугл таблицах хранится вся история изменений. Чтобы посмотреть эти версии, кликните на «Все изменения сохранены на Goolge Диске» справа от основного меню

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

Функция «ВПР» (vlookup)

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

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

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

В листе «Пример1» мы разберем самую простую реализацию функции VLOOUKUP. У нас есть лист с данными. Нам необходимо, чтобы функция искала нужное значение в листе с «Данными». Для начала создадим выпадающий список в ячейке А2 ( «Данные» -> «Настроить проверку данных» ). В качестве диапазона выберем первый столбец в листе «Данные»:

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

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

Иногда может потребоваться поиск не точного, а приблизительного значения. За это отвечает четвертый параметр VLOOKUP — сортировка. Для этого нужно поставить значение TRUE , тогда функция вернет значение, ближайшее к запрошенному (меньшее либо равное). См. лист «Пример2».

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

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

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

ВПР позволяет выполнять поиск по нескольким условиям. Например, нам необходимо показать выручку пиццерий у партнёров. Чтобы её вывести нужно учитывать оба этих параметра. Создадим выпадающие списки с ними. Смотрим лист «Пример3».

Теперь пишем формулу в ячейке 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

Функция MATCH в свою очередь содержит тоже два аргумента. Первый — это запрос, т.е. что мы будем вообще искать. Мы ссылаемся на выпадающие списки, т.е. что там выбрано, то и ищем (ячейки G5 и H5). Далее идёт диапазон. В первом случае мы выбрали столбец (ФИО Сотрудника — B4:B12). Во втором строку, где указаны наши «столбцы» (B4:E4)

Важно!

С помощью такой формулы вы не сможете получить данные с двух столбцов — только столбец и строка, т.е. их пересечение. Например, в таблице ниже вы не сможете посчитать выручку, «запихнув» в INDEX два условия MATCH по столбцам «Партнёры» и «Пиццерии».

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

Функция FILTER

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

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

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

Создаем «Лист2» и делаем скелет в нём. Указываем такие же столбцы, как и в оригинальной таблице. Далее в ячейке под столбцом «Партнеры» вставляем функцию = FILTER ( ‘Лист1’!A2:C24 ; ‘Лист1’!A2:A24 = «Партнер1» ) .

После этого таблица автоматически заполнится данными «Партнера 1».

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

• Диапазон — ‘Лист1’!A2:C24; — это тот самый диапазон, который мы будем фильтровать, т.е. все данные будут браться отсюда. В данном случае, это будет вся оригинальная таблица
• Условие — ‘Лист1’!A2:A24=»Партнер1″ — здесь мы указали условие, можно сказать, принцип по которому будет произведена фильтрация. Мы указали условие, что нам нужны данные по таблице там, где будет совпадение «Партнер1» в столбце А.

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

Вариант выше плох тем, что «копировать» всю таблицу нужно будет вручную. Т.е. для каждого партнёра нужна будет своя формула, свой фильтр. Примерно будет выглядеть вот так:

Лучшим вариантом здесь будет выпадающий список, такой, чтобы при выборе партнёра, мы получали данные по пиццериям. Делаем это с помощью опять же с помощью FILTER. Встаем на ячейке H3 в «Листе2» и выбираем «Данные» -> «Настроить проверку данных» . В качестве диапазона выбираем столбец «Партнеры» из «Листа1«.

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

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

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

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

Можно сделать отображение информации по флажкам в чекбоксе. Для этого мы создадим небольшой скелет (Лист3) и добавим три чекбокса ( «Вставка» -> «Флажок» )

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

Теперь в ячейке H1 пишем основную формулу: =FILTER(‘Лист1’!A1:C;TRANSPOSE(F2:F4)) . Она погружает данные с основной таблицы («Лист1»). При этом в условии фильтра указаны наши чекбоксы F2-F4, которые имеют два состояния — FALSE и TRUE. Т.е. данные будут отображаться с учетом состояния чекбоксов

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

Функция «QUERY»

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

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

Итак, у нас есть обычная таблица с несколькими столбцами данных. Лист так и называется — «Данные».

На листе «Проверка» мы с помощью функции UNIQUE отбираем уникальные значения для для столбца А и столбца B

Выбираем нужный диапазон для каждого столбца из листа «Данные»

В итоге, в листе «Проверка» у нас будут два столбца с уникальными значениями. Добавим к ним функцию SORT для, ожидаемо, сортировки. В итоге формулы у нас будут следующие: = SORT ( UNIQUE ( ‘Данные’!A2:A ) ) и =SORT(UNIQUE( ‘Данные’!B2:B ))

Переходим на лист «Отчет». Подписываем наши два столбца, как Партнеры и Пиццерии. После чего, заходим в меню «Данные» и выбираем «Настроить проверку данных».

В открывшемся окне «Проверка данных» нужно нажать на иконку таблицы, чтобы выбрать диапазон данных.

Сам диапазон мы берем из листа «Проверка», где у нас собраны и отсортирована выборка из уникальных значений основной таблицы. Соответственно, для партнеров мы выбираем «Столбец А». Аналогичную операцию проделываем с пиццериями («Столбцом B»)

Не забудьте поставить пункт «Запрещать ввод данных» после выбора диапазона и нажимайте «Сохранить»:

После того, как вы это сделали, у нас будет готов шаблон для выпадающих списков в листе «Отчет»:

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

Теперь мы воспользуемся функцией Query, чтобы выцепить из основной таблицы (лист «Данные») нужные нам элементы. Пусть это будет «Партнёр» и «Город». Пишем такую формулу: =QUERY(‘Данные’!A1:D24;»SELECT * WHERE A = ‘Партнер1’ AND D = ‘Москва’»;1) . И получаем отфильтрованные данные

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

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

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

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

Итоговая формула будет выглядеть вот так: = QUERY ( ‘Данные’!A1:D24 ; «SELECT * WHERE 1=1» & IF ( A2 = «Все партнеры» ; «» ; » AND LOWER(A) = LOWER(‘» & A2 & «‘) « ) & IF ( B2 = «Все пиццерии» ; «» ; » AND LOWER(B) = LOWER(‘» & B2 & «‘) « ) ; 1 )

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

Функция «Importrange»

Главный чит в Google Sheets. Позволяет целиком скопировать данные из одной таблицы в другую. Одной формулой. Форматирование при этом не переносится — только данные. По сути, это обычная ссылка на другую таблицу, а не её «копия». Importrange — отличное решение при импорте данных из одного листа в другой.

Есть условие — для таблицы, на которую вы ссылаетесь, должен быть настроен доступ (разрешение) на извлечение данных. Доступ остается в силе до тех пор, пока пользователь, предоставивший доступ, не будет удален из источника. Imortrange отлично подойдет для того, чтобы работать с данными, к которому у вас есть доступ «Только для просмотра», а также чтобы сделать одну таблицу из множества других (импортировать можно «кусками»)

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

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

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

Полная формула: =IMPORTRANGE(«1J-Wk9BGHM4tVPkaOGuYT8VS3CUUt-AwoEWgrbsCv1vs»; «Лист1!A1:C9»)

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

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

Чтобы исправить эту ошибку необходимо исправить диапазон.

Не правильно: = IMPORTRANGE ( «1J-Wk9BGHM4tVPkaOGuYT8VS3CUUt-AwoEWgrbsCv1vs» ; ‘Лист1’!A1:C9 )

Правильно: =IMPORTRANGE(«1J-Wk9BGHM4tVPkaOGuYT8VS3CUUt-AwoEWgrbsCv1vs»; «Лист1!A1:C9»)

Функция JOIN

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

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

Полная формула: = JOIN ( » « ; A5:B5 ) . В нашем случае разделителем является пробел. Растянем формулу на всю вертикаль таблицы и получим результат

С помощью JOIN можно «копировать» информацию из других ячеек в любой последовательности. Так же функцию можно использовать в связке с FILTER для того, чтобы получить все совпавшие элементы из столбца. Для примера возьмем другую таблицу на втором листе (лист «Пример2»)

Нам нужно посчитать квартальную выручку каждой пиццерии. Т.е. с помощью TEXTJOIN мы соединяем два параметра — месяц и выручку. В качестве диапазона данных (третий аргумент в функции TEXTJOIN) мы используем FILTER. Здесь мы использовали функцию TEXTJOIN (вместо «стандартной JOIN), чтобы избавиться от последнего разделителя в перечислении месяцев (столбец «Квартальная выручка»).

Полная формула: = TEXTJOIN ( «, « ; TRUE ; FILTER ( $C$2:$C$24 ; $B$2:$B$24 = G2 ) ) & SPLIT ( » : « ; » « ) & SUM ( FILTER ( $D$2:$D$24 ; $B$2:$B$24 = G2 ) )

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

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

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

20+ продвинутых функций Google Таблиц (Spreadsheets)

Для работы с текстом, визуализации данных, парсинга сайтов и других задач. Не все, но многие функции поддерживаются и в старом-добром Microsoft Excel.

Не только СУММ и СЦЕПИТЬ: Google Таблицы (или Google Spreadsheets) намного функциональнее и мощнее, чем это может показаться при поверхностном знакомстве.

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

Этот обзор — только часть полезного образовательного контента от центра CyberMarketing. Вас ждут статьи, вебинары и курсы по интернет-маркетингу: SEO, PPC, SMM, веб-аналитике и другим важным тематикам.

IMPORTRANGE

IMPORTRANGE (русскоязычного названия нет) — функция, которая загружает данные из одной Google Таблицы в другую. Принимает два параметра: URL таблицы и диапазон, откуда нужно импортировать данные. Например: =IMPORTRANGE("1iufABCDBDfT5BtDq1RJJw968xEDUWH80uM3u9ByATdoE";"Декабрь 2017!A:B")

Ссылку на таблицу можно вставить целиком или же взять лишь ее уникальный ID. Еще обратите внимание на второй аргумент: кириллическое название листа — без одинарных кавычек, хотя мы используем их, когда ссылаемся на такой лист в таблице.

Главное преимущество по сравнению с элементарным «Копировать → Вставить» — автоматическая загрузка новых данных. И эти новые данные легко сразу же использовать в других функциях или сводных таблицах благодаря возможности Google Spreadsheets задавать открытые диапазоны (к примеру, A2:B вместо A2:B20).

А еще IMPORTRANGE можно вложить в ВПР или QUERY, о которых речь пойдет дальше, или в другие функции, которые работают с диапазонами. Тогда можно будет не содержать дополнительный лист специально под импорт.

IMPORTHTML и IMPORTXML

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

IMPORTHTML — функция, которая может импортировать данные с веб-страницы, если они представлены в виде таблицы или списка. Например, она может выглядеть так: =IMPORTHTML("http://cbr.ru/key-indicators/";"table";2), где:

1. URL или ссылка на ячейку с адресом сайта.
2. Запрос, у которого только два варианта: «table» и «list» для таблиц и списков соответственно.
3. Индекс, порядковый номер элемента. (Не всегда цифра очевидна, придется методом перебора выяснять, под каким именно номером на странице будут нужные данные.)

В данном случае функция выводит таблицу с ценами на драгоценные металлы — это информация с сайта Банка России:

IMPORTXML тоже принимает первым параметром адрес страницы, а вторым — запрос XPath (это специальный язык для работы с XML-документами). Среди прочего эту функцию можно использовать для парсинга метатегов. Так, чтобы получить заголовок страницы, нужно вставить в ячейку текст вида: =IMPORTXML("https://www.ozon.ru/category/tehnika-dlya-krasoty-i-zdorovya-10737/";"//title")

Если взять запрос "//meta[@name=’description’]/@content", Google Таблицы извлекут описание (дескрипшн), а если "//h1" — заголовок первого уровня соответственно. Чтобы выгрузить список ссылок со страницы, подойдет "//a/@href":

Еще есть IMPORTDATA, которая работает с данными в формате CSV (значения, разделенные запятыми) или TSV (значения, разделенные табуляцией), и IMPORTFEED, которая загружает фид RSS или Atom. Но на практике они используются гораздо реже.

Конечно, есть и более удобные инструменты для парсинга метатегов и заголовков, например, Click.ru. Тем более этим функциональность не ограничивается: специалисты активно используют кластеризацию запросов, генерацию объявлений из YML, медиапланирование, создание отчетов и др. Бонус: вознаграждение до 18 % с рекламного оборота.

ВПР (VLOOKUP) и ГПР (GLOOKUP)

ВПР (VLOOKUP) — незаменимая функция для объединения данных из разных источников: листов и даже таблиц (если использовать вложенный IMPORTRANGE). Синтаксис: =ВПР(A2; ‘Отчет’!$A$2:$C; 4; 0), где:

1. запрос, по которому нужно искать (здесь он будет взят из указанной ячейки);
2. диапазон, в первом столбце которого нужно искать;
3. номер столбца (от начала диапазона, а не листа), откуда нужно взять значение;
4. дополнительный параметр, который настраивает точность поиска (по умолчанию 1, но лучше ставить 0, тогда будет возвращаться только точное совпадение).

Допустим, есть два листа: на одном список URL с названиями страниц, на другом — тоже список URL, но с показателями по продажам или трафику. С помощью ВПР легко объединить эти данные в один отчёт.

• Использовать абсолютные ссылки на диапазон (со знаками доллара), иначе при протягивании ВПР они тоже будут меняться, в результате поиск может работать некорректно.
• Третьим параметром передавать номер столбца от начала диапазона, а не от начала листа. (Необязательно ссылаться на весь лист — нужные ячейки могут лежать не в A:B, а в E:F, например).
• ВПР ищет совпадения только в первом столбце диапазона и берет значения только справа от него. В остальных случаях по умолчанию эта функция не справится, но хорошо, что есть другие варианты.

Чтобы функция ВПР возвращала значения не только правее, но и левее первого столбца диапазона, есть лайфхак с использованием массива. Суть: создать виртуальную таблицу, где столбцы будут расположены в порядке, необходимом для корректной работы VLOOKUP.

Например =VLOOKUP(C2;<‘Лист2’!D:D \ ‘Лист2’!B:B \ ‘Лист2’!C:C>;2;0) успешно произведет поиск по четвертому столбцу и передаст данные из второго. Потому что в массиве значения диапазона D:D идут первым столбцом — нет никаких противоречий.

Функция-побратим — ГПР (HLOOKUP) — работает похожим образом, только ищет по строкам, а не столбцам. На практике это может понадобиться гораздо реже.

ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX)

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

Алгоритм такой: MATCH находит значение в диапазоне (строка или столбец) и возвращает его порядковый номер, а INDEX — передает содержимое ячейки, у которой такой же порядковый номер, просто она находится в соседней строке или столбце.

Пример: =INDEX(‘Лист2′!$B$2:$B;MATCH(C3;’Лист2’!$D$2:$D;0)). Сначала запускается MATCH: находит значение из C3 на другом листе в столбце D, затем возвращает порядковый номер. INDEX берет этот номер и ищет по нему уже в столбце B, затем возвращает результат:

• ПОИСКПОЗ (MATCH) может работать только с одной строкой или с одним столбцом. Если попытаться отправить многомерный массив вроде A:D, функция выдаст #Н/Д! Третий параметр функции — метод поиска. Ноль требует точный поиск, показывает, что диапазон никак не отсортирован.
• ИНДЕКС (INDEX) может работать с любыми диапазонами, но в сочетании с ПОИСКПОЗ понадобится только поиск по столбцу. Поэтому третий параметр не используется — в ИНДЕКС передаются только диапазон (столбец, откуда нужно взять значение) и номер строки (его возвращает ПОИСКПОЗ).
• ИНДЕКС и ПОИСКПОЗ оперируют номерами строк/столбцов именно заданных диапазонов, а не листов — важно помнить об этом при работе.

SPARKLINE

Спарклайн — интересный инструмент визуализации, который не требует много места: диаграмма умещается в одну ячейку. Аргументов два: диапазон или массив данных и набор опций (необязательный). В последнем можно задать, например:

• Тип диаграммы (charttype) — по умолчанию line (график), но можно поменять на bar (гистограмму) или column (столбчатую диаграмму).
• Цвет линии или столбцов диаграммы (color) — зеленый (green), желтый (yellow) и любой другой по шестнадцатеричному коду.
• Максимальное (max, ymax) и минимальное (min, ymin) значения по горизонтальной или вертикальной оси.

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

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

Синтаксис: =SPARKLINE(B2:E2;<"charttype"\"column";"color"\"green">) где первым параметром идет диапазон с данными для визуализации, а вторым — массив с набором опций, который в данном случае указывает рисовать столбчатую диаграмму, а не график по умолчанию, и покрасить ее в зеленый цвет:

Но посмотрите внимательно на данные и сами диаграммы на этом примере. Сейчас кажется, что страница №3 сильнее всех просела по трафику в марте, хотя потеря составила всего 721 визит. Тогда как страница №1 потеряла целых 8956 визитов. Чтобы решить такую проблему, нужно как-то связать данные — например, с помощью опций ymin и ymax, которые передают максимальное и минимальное значение по всем страницам: =SPARKLINE(B2:E2;<"charttype"\"column";"color"\"green";"ymax"\MAX($B$2:$E$4);"ymin"\MIN($B$2:$E$4)>) Тогда получается гораздо нагляднее и реалистичнее:

Кстати, если вы увлекаетесь инвестициями, комбинация SPARKLINE и GOOGLEFINANCE поможет изучать динамику котировок акций и курсов валют. На скриншоте — визуализация изменения стоимости акций Google за прошедший год:

ТРАНСП (TRANSPOSE)

ТРАНСП (TRANSPOSE) пригодится, когда нужно транспонировать таблицу (матрицу), то есть поменять строки и столбцы местами. В качестве аргумента можно передать диапазон или массив, например, так: =ТРАНСП(A35:G40)

Допустим, вы выгружаете из Яндекс.Метрики отчет с данными графика — чтобы посмотреть динамику трафика по определенным разделам:

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

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

Конечно, есть и альтернативное решение без использования этой функции: скопировать нужный диапазон, кликнуть правой кнопкой мыши и выбрать «Специальная вставка → Вставить с изменением положения строк и столбцов».

IFS (множественное IF)

IFS (русскоязычного аналога нет) — расширенная версия функции ЕСЛИ (IF), которая позволяет оценивать сразу несколько условий. Возвращает то значение, которое соответствует первому истинному условию (TRUE). То есть сначала проверяет первое условие (слева), если оно истинно — отправляет первое значение, если ложно — идет дальше вправо. Синтаксис: =IFS(условие1; значение1; условие2; значение2; …) Если все условия ложные, вернёт #Н/Д!

Допустим, вы выгрузили (из системы аналитики или CMS) список URL с какими-то дополнительными данными: названиями, датами публикаций, количеством визитов, продажами и т. д. Например, такой:

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

Есть разные варианты решений. Например, правее можно прописать и протянуть функцию =SPLIT(B2;"/") — она разложит URL на составляющие. Далее достаточно посмотреть, в какой ячейке лежит нужная часть адреса, и составить формулу вида: =IFS(I5="green_tea";"Зеленый чай";I5="ulun";"Улун";I2="travyanoy-chay";"Травяной чай") Недостаток такого подхода — множество лишних «технических» ячеек, они могут мешать, их придется скрывать.

Другой способ — вложить в IFS несколько других функций: НЕ (NOT), ЕОШИБКА (ISERROR), НАЙТИ (FIND). Тогда формула примет более сложный вид, но зато не нужны будут никакие дополнительные ячейки: =IFS(NOT(ISERROR(FIND("/green_tea/";B2)));"Зеленый чай";NOT(ISERROR(FIND("/ulun/";B2)));"Улун";NOT(ISERROR(FIND("/travyanoy-chay/";B2)));"Травяной чай")

Почему такая сложная конструкция? Дело в том, что FIND возвращает #Н/Д, если не находит запрос в тексте, а это прерывает проверку всех условий в IFS. Поэтому приходится использовать ISERROR, что возвращает TRUE, если функция FIND выдает ошибку. Но TRUE опять прервет выполнение IFS — ведь условие должно наоборот быть ложным, чтобы начать проверять следующее условие. Поэтому приходится усложнять и добавлять NOT, которая поменяет TRUE на FALSE.

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

REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Эти три функции Google Таблиц предназначены для работы с регулярными выражениями (специальный язык для работы со строками и символами). REGEXMATCH ищет соответствия, REGEXEXTRACT извлекает нужный фрагмент, а REGEXREPLACE заменяет одну часть текста на другую. Синтаксис похожий: первый аргумент — текст, а второй — само регулярное выражение; в REGEXREPLACE есть еще третий — текст, который нужно вставить.

Допустим, нужно из URL конкретной страницы извлечь название сайта. Для этой цели можно использовать такой вариант: =REGEXEXTRACT(C23;"https://(.*?)/") Функция возьмет все символы, что находятся между "https://" и следующим слешем, включая дефисы и точки. Поэтому нормально будут экстрагироваться и домены второго уровня:

С помощью REGEX можно также решить задачу с категориями из предыдущего раздела про IFS. Тогда получится так: =IFS(REGEXEXTRACT(C2;"/catalog/([^/]+)")="travyanoy-chay»;"Травяной чай";REGEXEXTRACT(C2;"/catalog/([^/]+)")="ulun";"Улун";REGEXEXTRACT(C2;"/catalog/([^/]+)")="green_tea";"Зеленый чай")

Почему такой вариант, и как он работает? "/catalog/" — общая часть у всех URL, поэтому можно смело начинать поиск совпадений с нее. Далее нужно взять все символы, что находятся между "/catalog/" и следующим слешем. Конструкция ([^/]+) как раз за это отвечает. Получается, функция ищет любое число любых символов, кроме слеша, на котором она и остановится. ‘^’ здесь используется как оператор отрицания, ‘+’ задаёт 1 или более повторений символов, а круглые скобки — что нужно брать только эту группу, не включая остальные части текста.

ARRAYFORMULA

ARRAYFORMULA (русскоязычного названия нет) — функция для работы с массивами. В качестве параметра принимает формулу массива или другую функцию.

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

Единственное, что в данном случае формула будет заполнять ячейки до самого конца таблицы — а лишние нули это не очень красиво. Решение — дополнительно использовать IF: =ARRAYFORMULA(IF(D2:D<>"";E2:E-D2:D;"")) которое сообщает следующее: если в ячейке D пусто, то и вычитание не нужно, оставить ячейку пустой.

Аналогичным способом ARRAYFORMULA можно использовать вместе с ВПР(VLOOKUP), к примеру: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A;feb!$A:$D;2;0);"")) Только здесь от лишних #N/A до конца таблицы спасает функция ЕСЛИОШИБКА (IFERROR).

Увлечение ARRAYFORMULA (особенно если еще в большом количестве используются такие функции, как VLOOKUP, MATCH, INDEX, QUERY) может существенно замедлять работу Google Таблицы. Ускориться помогает удаление лишних строк (по умолчанию их 1 000, сотни могут совсем не использоваться и только зря обрабатываться функцией ARRAYFORMULA).

SORTN

SORTN — расширенная версия функции SORT, которая может не только сортировать данные по нескольким столбцам, но и ограничивать количество возвращаемых результатов. Параметры:

1. Диапазон для сортировки и вывода. (Впрочем, столбцы, по которому данные сортируются, можно не включать в этот диапазон, указать их отдельно в четвертом параметре.)
2. Количество возвращаемых элементов. (Можно сделать топ-3, топ-5 и т. д.)
3. Режим показа совпадений. (По умолчанию ноль. Единица, например, будет выводить дополнительные строки, — больше, чем указано во втором параметре — если в столбце для сортировки найдутся повторяющиеся значения.)
4. Столбец для сортировки. (Может быть вне диапазона, указанного в первом параметре.)
5. Способ сортировки столбца. ИСТИНА (TRUE) сортирует данные по возрастанию (от меньшего к большему), а ЛОЖЬ (FALSE) – по убыванию (от большего к меньшему).

(Если нужно, дальше можно также задать дополнительные столбцы и варианты сортировки.)

Допустим, есть таблица с показателями трафика за несколько месяцев. И нужно подготовить топ лучших или худших страниц по динамике за последние два. Для этого как раз хорошо подходит функция SORTN.

Пример: =SORTN(A2:F7;3;0;6;TRUE), которая выводит данные из A2:F7, но только первые три строки, отсортированные по шестому столбцу (F) по возрастанию:

Если столбец для сортировки не входит в первый диапазон, нужно передать его четвертым параметром (главное условие — такое же количество элементов, как у первого). Пример: =SORTN(A2:E7;3;0;F2:F7;TRUE)

FILTER

FILTER (опять без русского аналога) — мощная функция Google Таблиц, которая выводит только те строки и столбцы, которые соответствуют заданным условиям. Первым аргументом принимает диапазон, вторым и последующими — условия для фильтрации.

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

Здесь подойдет такой вариант: =FILTER(‘Запросы’!A2:G;’Запросы’!B2:B>50;’Запросы’!G2:G>50), где мы сначала указываем диапазон данных для фильтрации и вывода, затем условия — во-первых, значения в столбце B должны быть больше 50, во-вторых, значения в столбце G тоже должны быть больше 50.

Столбцы или строки, по которым фильтруются данные, не обязаны входить в первый диапазон. Например, нет смысла в столбце, где все значения будут повторяться — а так и будет, если FILTER отбирает данные по какой-то одной единственной категории. Если в этом примере формулы поменять Запросы!A2:G на Запросы!A2:A, ничего не сломается — просто будет выводиться только первый столбец.

Теперь другой, более сложный пример использования FILTER. Допустим, вы сделали копию прайс-листа поставщика, потому что так с данными удобнее работать, но нужно периодически проверять оригинальную таблицу — что нового там появилось и стоит ли обновить свою. И нужно проверять не все позиции, а самые приоритетные и прибыльные. Это можно осуществить, сочетая FILTER с IMPORTRANGE, MATCH и ISERROR. Например, так:

=FILTER(IMPORTRANGE("1znX3hxN9cKEvZyh_0XOj7gHpjYze8p-40cZchiDvTxY";"Каталог!A2:E");IMPORTRANGE("1znX3hxN9cKEvZyh_0XOj7gHpjYze8p-40cZchiDvTxY";"Каталог!A2:A")=1;(ISERROR(MATCH(IMPORTRANGE("1znX3hxN9cKEvZyh_0XOj7gHpjYze8p-40cZchiDvTxY";"Каталог!B2:B");B3:B7;0))))

• IMPORTRANGE подгружает столбец B из листа «Каталог».
• MATCH ищет совпадения между импортируемыми и имеющимися данными (между названиями товаров в скопированном и оригинальном прайс-листах).
• ISERROR вернет FALSE, когда MATCH найдет совпадения, и, соответственно, вернет TRUE, если таких совпадений не будет.

Еще несколько моментов:

• FILTER фильтрует или строки, или столбцы. Чтобы фильтровать их одновременно, можно вложить одну функцию в другую — то есть одна FILTER будет обрабатывать выходные данные из другой FILTER.
• Не очень удобно постоянно копировать и вставлять заголовки из одной таблицы в другую. Но благодаря массиву можно подгружать их автоматически и в правильном порядке. Немного усовершенствованный предыдущий пример: =<‘Запросы’!A1:G1;FILTER(‘Запросы’!A2:G;’Запросы’!B2:B>50;’Запросы’!G2:G>50)>

БДСУММ(DSUM), БСЧЁТА(DCOUNTA), БИЗВЛЕЧЬ(DGET), ДСРЗНАЧ (DAVERAGE)…

Функции БД — серьезные инструменты, когда нужно работать с большим количеством данных и условий, — и стандартные FILTER, СУММЕСЛИ, СРЗНАЧЕСЛИ, ВПР и другие не справляются или не очень удобны в использовании.

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

Здесь лучше всего подойдет ДСРЗНАЧ (DAVERAGE). Синтаксис у этой и остальных Д-функций похожий:

1. Массив или диапазон данных — в общем, таблица, с которой нужно работать. (Первая строка обязательно должна содержать заголовки столбцов!)
2. Столбец, в котором находятся нужные данные. (Можно передать номер столбца, адрес ячейки или даже просто название столбца текстом в кавычках.)
3. Критерии, условия для фильтрации — можно передать их как массивом, так и диапазоном. (Важно: первый элемент должен соответствовать заголовку столбца с искомыми данными, что указан во втором параметре.)

Для начала на отдельном листе нужно подготовить критерии — список типов контента, по которым нужно рассчитать показатели. Затем уже использовать, немного модифицируя, такую формулу: =DAVERAGE(Book!A4:J;8;B1:B13). Она считает среднее арифметическое по всем значениям из столбца №8 диапазона Book!A4:J, которые соответствуют данным из диапазона B1:B13. (Напоминаем: в обоих диапазонах первыми строками идут заголовки. А вместо номера столбца — 8 — можно сослаться на ячейку его заголовка — Book!H4 — или просто передать название текстом — «лайки»).

Репосты и комментарии считаются аналогично, меняется только номер столбца (8→9→10). Ну а среднее число вовлечений легко получить через обычный =AVERAGE (C2:E2).

Показатели для текстовых типов контента можно получить точно так же, единственное — нужно будет снова передавать название заголовка. Писать его ниже необязательно, можно просто добавить через массив: =DAVERAGE(Book!$A$4:$J;8;<"тип контента";$B$15:$B$38>)

Другие функции баз данных работают аналогично, разница в функциональности: так, БСЧЁТА — считает количество числовых и текстовых значений, БДСУММ — соответственно, сумму, БДПРОИЗВЕД — произведение, БИЗВЛЕЧЬ(DGET) — извлекает нужные данные из таблицы.

• Не забывать про заголовки в столбцах/массивах — именно они являются «мостиком» между данными и позволяют находить и считать нужное.
• Нет ограничений по количеству столбцов — можно задать несколько условий для фильтрации (например, не только типы контента, но и тематики). Главное — правильно написать заголовки.
• Не использовать открытый диапазон в критериях — Д-функции не будут игнорировать пустые ячейки, будут искать по ним тоже, что драматично исказит результаты.
• В БСЧЁТ и БСЧЁТА можно указать любой столбец — ведь эти функции считают общее количество, а не производят математические операции с конкретными цифрами.

QUERY

Если FILTER — просто мощная функция, то QUERY — мощнейшая. Она выполняет запросы на языке аналогичном SQL, позволяет строить самые разные отчеты и сводные таблицы, в том числе интерактивные дашборды. Вообще по QUERY стоит писать отдельный большой гайд, поэтому тут рассмотрим лишь часть возможностей.

1. Диапазон ячеек, собственно, база данных. (Можно импортировать из другой таблицы через IMPORTRANGE.)
2. Запрос, записанный на языке API визуализации Google (аналог SQL). Передается в текстовом формате — можно написать в кавычках внутри функции или взять из ячейки.
3. Заголовки — количество строк в верхней части раздела данных, необязательный параметр. (Заголовки можно присоединять и через массив).

QUERY очень чувствительна к синтаксису и порядку написания кляуз — так называют отдельные части запроса, которые отделяются между собой пробелами:

1. SELECT — указывает нужные столбцы и их порядок. Например, " SELECT A, B, D " Здесь сразу же можно создать пользовательский столбец, допустим: " SELECT A, B, C, H+I+J " Если же нужно просто вывести все столбцы, какие есть в исходном диапазоне, достаточно прописать " SELECT * " (Нюанс: если QUERY обрабатывает массив или импортируемый диапазон, нужно в SELECT указывать номер столбца (Col1), а не название (A).)

2. WHERE — задает условия для фильтрации данных. Можно написать " WHERE B > 50 AND D 0. Другой пример: " WHERE F IS NOT NULL OR G IS NULL ", который говорит: "Взять данные, где в столбце F есть какое-то значение или, наоборот, G — пустой". Для сравнения текстовых строк есть свои операторы: например, matches ищет соответствия регулярному выражению, contains — содержание в любом месте строки, starts with — в начале… Пример: " WHERE A=’Маркетинг’ AND B starts with ‘Статья’ " (Строки внутри запроса QUERY передаются в одинарных кавычках.)

3. GROUP BY — условия для группировки данных по строкам. Работает, только когда в SELECT есть агрегирующие функции: sum (считает сумму), avg (рассчитывает среднее), min (находит минимальное значение), max (выдает максимальное значение), count (подсчитывает количество). Допустим: " SELECT A, B, C, avg(H) GROUP BY B, C, A " (Каждый столбец, указанный в SELECT без агрегирующей функции, должен быть указан и в GROUP BY.)

4. PIVOT — работает аналогично GROUP BY, только группирует данные по столбцам, например: " SELECT B, AVG(H) GROUP BY B PIVOT A " (Кстати, помимо агрегирующих, QUERY поддерживает и скалярные функции. Например, day возвращает номер дня из даты, now выдает текущую дату и время, а lower — приводит строку к нижнему регистру.)

5. ORDER BY — отвечает за сортировку результатов. В запросе достаточно перечислить поля и способ сортировки (по умолчанию ASC, то есть по возрастанию, если указать DESC — функция будет сортировать по убыванию.) Пример: " SELECT C, H ORDER BY H DESC "

6. LIMIT — ограничивает количество возвращаемых строк. Так " SELECT * LIMIT 10 " вернет только первые 10 строк, других условий здесь нет. Это удобная кляуза для формирования всяческих топов, аутсайдеров, замены вышеупомянутой SORTN.

7. OFFSET — действует аналогично, только, наоборот, пропускает N-ое количество первых строк. Соответственно " SELECT * OFFSET 10 " будет возвращать все строки, начиная с 11 от начала диапазона.

8. LABEL устанавливает подписи для столбцов. В запросе нужно сначала указать столбец или функцию, затем в одинарных кавычках — новое название. Если меток несколько, они перечисляются через запятую, как и другие параметры кляуз. Например: " SELECT A, avg (H) GROUP BY A LABEL A ‘Тематика’, avg (H) ‘Среднее число лайков’ "

9. FORMAT задает правила форматирования для ячеек в одном или нескольких столбцах. Синтаксис как у LABEL, но в кавычках нужно передавать специальные коды. Так " SELECT A, H FORMAT H ‘ #,## ‘ " будет выводить числа с разделителями разрядов. (Нужные коды можно узнать в разделе "Формат → Числа → Другие форматы".)

Особая прелесть QUERY в том, что запрос целиком — и его отдельные параметры — можно не указывать прямо в функции, а брать из ячеек. Для соединения строк между собой достаточно обычной конкатенации через ‘&’. Пример: " SELECT A, B, C, H WHERE H

Google Таблицы: большой гайд по работе с инструментом

Google Sheets – удобный инструмент, который повсеместно используется компаниями, работающими в digital-сфере. С его помощью удобно планировать (например, контент, финансы или задачи), вести бухгалтерский учет, проводить маркетинговый анализ, строить прогнозы и т.д.

Интерфейс Google Таблиц

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

• Файл: в этом списке вы найдете все операции по созданию, импорту, отправке, перемещению созданных документов. Здесь же вы можете скачать файл в форматах XLSX, PDF, ODS, HTML, CSV, TSV.
• Правка: содержит стандартные функции, для которых чаще всего используются сочетания клавиш (копировать, вставить, вырезать и т.д.), а также удаление/добавление строк и столбцов.
• Вид: всё, что связано с отображением таблицы, например, сетка, масштаб, формулы и диапазоны.
• Вставка: с помощью функций раздела можно добавить изображения, диаграммы, формы, примечания, комментарии и т.д. Кроме того, можно создать второй лист.
• Формат: для корректных расчетов нередко требуются цифры в определенном виде – выбрать его можно в этой части меню. Если необходимо повернуть или перенести надпись в ячейке, изменить внешний вид текста (жирный, курсив, подчеркнутый, зачеркнутый), выровнять абзацы, все эти опции расположены тут.
• Данные: здесь можно отсортировать, применить фильтры, проверить, избавиться от лишних пробелов или свести таблицы.
• Инструменты: если для работы вам потребуются макросы, скрипты или формы, сделать это можно с помощью функций этого раздела.
• Дополнения: здесь вы можете расширить функционал с помощью других сервисов Google.
• Справка: поможет сориентироваться и найти необходимую информацию по использованию Spreadsheets.

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

Панель инструментов Google Sheets

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

• Отменить действие (Command+Z для MacOS или Ctrl+Z для Windows).
• Повторить действие (Command+Y или Ctrl+Y).
• Печать (Command+P или Ctrl+P).
• Копировать форматирование.
• Масштаб – здесь вы увидите выпадающий список, где можно выбрать удобный для работы вид (от 50% до 200%).
• Денежный формат.
• Процентный формат.
• Уменьшить число знаков после запятой (система сама округлит значения).
• Увеличить число знаков после запятой.
• Другие форматы – здесь можно выбрать отображение в виде даты, времени, валюты (с округлением или без) и т.д.
• Шрифт – вид, размер, цвет, выделение с помощью жирного или наклонного, а также возможность зачеркнуть текст.
• Заливка с выбором цвета для ячейки.
• Границы, где можно выбрать стиль, цвет и расположение линий в таблице.
• Выравнивание по горизонтали и вертикали.
• Перенос текста.
• Поворот текста.
• Вставить ссылку (Command+K или Ctrl+K).
• Вставить комментарий (Command+Option+M или Ctrl+Alt+M).
• Вставить диаграмму.
• Создать фильтр.
• Функции – для расчетов и анализа данных.
• Способы ввода, где можно включить экранную клавиатуру.
• Стрелка, скрывающая меню.

Внизу происходит управление листами, которые можно добавлять, удалять, переименовывать, копировать, перемещать и скрывать. Кроме того, вы можете защитить их от изменений.

Настройки доступа

Чтобы файл стал доступен другим пользователям, вы можете поделиться им, нажав на зелёную кнопку в правом верхнем углу. После этого откроется окно, где можно предоставить доступ по email или по ссылке. Открывая таблицу другим, вы можете определить их статус:

• Редактор – дает возможность менять содержимое.
• Комментатор – разрешает оставлять примечания.
• Читатель – позволяет только смотреть без возможности внесения изменений и добавления пометок.

Начало работы с Google Sheets

Начать работу с сервисом можно на его главной странице. Зайдите в свой аккаунт Google. На странице отобразятся все доступные вам документы, отсортированные по дате последнего просмотра: значками помечены те, у которых есть общий доступ с другими пользователями. Вы можете воспользоваться фильтрами, чтобы в списке показывались только созданные вами таблицы.

В верхней части предоставлены образцы для работы: календарь, список дел в виде чек-листа с дедлайнами, бюджеты. Нажав на кнопку «Галерея шаблонов», вы перейдёте в расширенный каталог:

Если необходимо создать пустую таблицу, кликните на лист с плюсом на главной странице. То же самое можно сделать и в интерфейсе диска. Нажмите на кнопку «Создать» и выберите в списке таблицы.

Документы сохраняются автоматически в корневой папке Google Drive. Здесь можно разделить их по папкам с помощью функции «Переместить в…» или простым перетаскиванием.

Как открыть файл Excel в «Google Таблицах»

Сервис позволяет загружать таблицы Excel. Открыть их можно, добавив в Google Диск через функцию создания, или с помощью импорта в Sheets. В пустом документе наведите курсор на раздел «Файл». В выпадающем списке воспользуйтесь одним из двух вариантов:

• Выберите раздел «Импортировать» или «Открыть» и перетащите с компьютера XLSX в окно загрузки.
• Нажмите на клавиатуре сочетание Ctrl+O (в Windows) или Command+O (в MacOs).

Эта связь работает и в обратную сторону: любой созданный документ можно сохранить в формате, поддерживаемом в Excel. Делается это в этом же разделе с помощью функции «Скачать».

Операции с ячейками, строками и столбцами

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

Удалить выделенную строку или столбец можно в разделе «Правка». Здесь же можно выбрать направление перемещения ячеек.

Как закрепить строки

В процессе работы с объемными таблицами для визуального анализа данных может потребоваться, чтобы определенные строки или столбцы оставались на виду при скроллинге. Их можно закрепить с помощью соответствующей опции в списке «Вид».

При выборе параметров «Несколько строк» или «Несколько столбцов» в таблице появится отсечка в виде утолщенной границы. Вы можете передвигать её, чтобы расширить или сузить диапазон закрепленной области.

Как перемещать элементы таблицы

Одна из наиболее удобных функций – свободное перемещение элементов без необходимости копирования. Для перетаскивания содержимого ячеек:

• выделите их;
• наведите курсор на границу области;
• когда из стрелочки он примет вид «руки», нажмите на левую клавишу мыши и поместите в нужное место по логике drag&drop.

История изменений в «Google Таблицах»

В отличие от Excel и другого софта по работе с таблицами, в Google Spreadsheets все изменения сохраняются автоматически – не нужно ничего дополнительно настраивать. Каждое из них фиксируется в истории, найти которую можно в верхней панели.

Кликнув на неё, вы активируете режим, в котором нельзя вносить правки. Здесь можно выбрать любую версию и восстановить её. При этом все корректировки, которые были сделаны после, удалятся. Чтобы не допустить этого, скопируйте раннюю версию: тогда с ней будет намного проще работать.

Функция бэкапа недоступна, если вы не являетесь владельцем файла.

Как удалить и восстановить таблицу

Стереть таблицу можно в два клика – зайти в меню «Файл» и выбрать опцию «Удалить». Второй вариант – выделить документ на диске и нажать на клавишу Delete или значок корзины.

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

Работа с данными в «Google Таблицах»

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

Как редактировать Google Sheets

Выделите нужную ячейку и заполните её текстом или цифрами. Чтобы перейти на следующую строчку, нажмите Enter. Управлять можно с помощью стрелок, мыши, горячих клавиш и их сочетаний.

Если в графе уже добавлена информация, редактировать её необходимо через верхнюю строку, расположенную под панелью с инструментами. В противном случае содержимое удалится.

Как защитить данные от редактирования

Защитить файлы от редактирования можно в настройках доступа. Для этого необходимо выбрать уровень «Читатель». В таком случае пользователь может только открыть документ и ознакомиться с содержимым. Второй вариант – «комментатор»: с такими правами человек может только оставлять пометки, не меняя наполнение документа.

Внутри таблицы вы можете поставить ограничения на лист или диапазон ячеек.

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

Комментарии и примечания

Через контекстное меню можно добавлять подписи к каждой ячейке. Доступны две формы: первая – это комментарии, на которые могут отвечать другие юзеры, вторая – это примечание без возможности дополнить. Каждая из них по-своему отмечается в интерфейсе (жёлтыми и чёрными треугольниками в верхнем левом углу), чтобы можно было визуально их отличить.

Комментарии представляют собой диалог. Здесь вы можете обсудить с другими пользователями содержание, чтобы внести необходимые правки. После того как вы пришли к единому мнению, можно кликнуть на опцию «Вопрос решен». В таком случае ветка обсуждения закрывается.

Форматы данных

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

• текстовый;
• числовой;
• финансовый;
• валютный;
• процентный;
• время;
• дата.

Для аналитики, например, поиска корреляции или средних значений, используются только цифровые данные. Чтобы изменить вид, воспользуйтесь разделом «Формат» и выберите нужные настройки. Если в общем меню нет того, что требуется для оформления, перейдите по вкладке «Другие форматы» – здесь находится полный список.

Условное форматирование данных

Самый простой способ изменения отображение ячейки – опции в панели управления сверху. Здесь вы можете менять внешний вид шрифтов, границ, абзацы и правила переноса текста. Не всегда ручных инструментов достаточно. Для автоматической корректировки используется условное форматирование.

Правила задаются для:

• Отдельных строк и столбцов.
• Ячеек или диапазонов.
• Всей таблицы целиком.

Настроить их можно через раздел «Формат». В открывшемся окне добавляются условия, например, при определённых значениях ячейки перекрашиваются в заданный цвет. Менять можно не только оформление, но и стиль текста.

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

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

Фильтры и сортировка

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

Фильтры помогают скрыть ненужные данные. Это позволяет проще ориентироваться в объемных таблицах. Например, при анализе поведения пользователей на сайте вы можете отображать те факторы, которые приводят к определённой цели. Так вы фиксируете внимание только на нужной информации.

Проверка данных

Чтобы корректно производить расчёты по формулам, необходимо проверять содержимое в ячейках. Например, правильное построение диаграммы по датам невозможно, если где-то будет ошибка в формате. Избежать недочетов поможет опцию «Настроить проверку данных», которую можно найти в соответствующем списке.

Для запуска инструмента необходимо:

• выбрать диапазон;
• задать правило;
• определить действие системы при возникновении ошибки.

Если напротив параметра «показывать текст для справки» поставить галочку, то ошибка будет сопровождаться примечанием из справки. Такие пояснения помогают быстрее решить проблему.

Система может проверять не только соответствие выбранному формату, но и значениям внутри ячеек. Например, вы знаете, что метрика не может быть отрицательной или превышать 100%. Вы можете задать правило, которое запрещает это.

Сводные таблицы

Для получения объективных выводов по результатам маркетинговых компаний специалисты часто используют сводные таблицы. Например, при запуске таргетированной рекламы вы получаете одни цифры (это может быть CPC, CPM или другие метрики), а в системах аналитики отображаются результаты (объем трафика, конверсии CR). С помощью Google Sheets вы можете свести их в один файл, структурировать информацию и создать наглядный отчёт, с которым удобнее работать.

При использовании параметра «Сводная таблица», платформа создаст новый лист и выведет в правой панели редактор для управления данными. Там вам нужно будет выбрать строки, столбцы, значения и настроить фильтры.

Диаграммы и графики в «Google Таблицах»

Визуализация – не самая значимая функция сервиса, но для работы с данными иногда требуются наглядные инструменты. Для этого вы можете добавить на страницу графики или диаграммы.

Выделите нужный диапазон, перейдите в раздел «Вставка» и выберите в нём раздел «Диаграмма». Система автоматически построит следующий рисунок:

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

Здесь же доступны другие настройки:

• Тип накопления, где можно выбрать один из двух параметров – «стандартный» или «нормированный». Это важно учитывать, если для анализа учитывается несколько критериев.
• Диапазон данных, который позволяет корректировать выбранную для создания графика зону ячеек.
• Оси и параметры, используемые для построения.

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

Работа с функциями

Как и в Excel, для обработки данных в Google Sheets доступны функции. Вам необходимо ввести стандартизированную формулу – и программа сделает необходимые расчеты.

Для запуска инструмента требуется ввести «=» в ячейку. Система предложит самые используемые функции. Если из показанных вариантов ни один не подходит, напишите первые буквы. Подсказки помогут найти то, что вам нужно. После этого требуется выбрать массив одним из двух способов:

• Ручным вводом первой и последней ячейки для анализа в скобки.
• Выделением нужного диапазона с помощью мышки.

Маркетологи часто используют функции для расчетов средних показателей, затрат и коэффициентов. Например, если у вас есть данные по затратам на рекламные объявления, количество переходов и совершенных целевых действий, с помощью простых формул вы можете посчитать цену за клик (CPC), коэффициента конверсии и другие показатели эффективности. Ввести функцию нужно только один раз – для остальных данных её достаточно растянуть на другие ячейки в столбце или строке.

Интеграция с другими инструментами

Главное удобство онлайн-сервисов Google – возможности взаимной интеграции. Для обработки данных можно подгружать их автоматически из форм и аналитики. Это ускоряет работу маркетологов, SEO-оптимизаторов, UX-дизайнеров и других специалистов, работающих с сайтами.

Взаимодействие с Google Forms

Для импорта данных из форм в таблицы не требуется дополнительных настроек – их функционал пересекается.

Зайдите в раздел «Инструменты» в Google Sheets и выберите «Создать форму». Система перенаправит на редактор. Под каждый созданный вопрос будет создаваться соответствующий столбец. При заполнении ответы автоматически загружаются в ячейки.

Если вы создавали форму через интерфейс Google Forms, то данные можно импортировать. Зайдите в раздел «Ответы» и нажмите на значок таблицы. Для загрузки можно сделать новый файл или загрузить в готовый.

Формы можно использовать как инструмент для сбора информации при исследованиях юзабилити. Ответы тестировщиков будут автоматически загружаться в созданную вами базу.

Интеграция с Google Analytics

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

Чтобы добавить приложение, перейдите в раздел «Дополнения»:

Выберите в списке Google Analytics и в открывшемся окне нажмите кнопку «Установить». Следуйте инструкции.

В результате в меню дополнений появится соответствующий раздел, где вы сможете подготовить отчёт вручную или настроить автоматизированное создание.

Полезные дополнения Google Sheets

Lucidchart Diagrams: продвинутый облачный инструмент для создания визуальных отчетов с диаграммами, схемами, чартами и т.д.

Mail Merge for Gmail: сервис для настройки персонализированных рассылок через Google Sheets. Помогает правильно организовать онбординг и анализировать его результаты. Частично заменяет функции CRM.

Supermetrics: выгружает аналитику более, чем из 50 источников, включая Яндекс.Метрику.

Calendar to Sheet: переносит мероприятия из календаря в таблицы.

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

Сочетания и горячие клавиши в «Google Таблицах»

Упростить взаимодействие с файлами и их редактирование помогают сочетания клавиш:

• Выделение столбца: Ctrl (для Windows) или Command (для MacOS) + пробел.
• Выделение строки: Shift + пробел.
• Вставка ссылки: Ctrl/Command + K.
• Поиск: Ctrl/Command + F.
• Замена символов: Ctrl/Command + Shift + H.

Можно использовать и привычные сочетания копирования, вставки, начертания шрифта и т.д. Полный список располагается в разделе справки.

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

16 полезных формул Google Таблиц для SEO-специалистов

PromoPult 17 Марта 2020, в 09:50

SEO – это рутина. Иногда приходится делать совсем тоскливые операции вроде удаления «плюсиков» в ключевых словах. Иногда – что-то более продвинутое вроде парсинга мета-тегов или консолидации данных из разных таблиц. В любом случае все это съедает массу времени.

Но мы не любим рутину. Предлагаем 16 полезных функций Google Sheets, которые упростят работу с данными и помогут вам высвободить несколько рабочих часов или даже дней. (Уверены, о существовании некоторых функций вы не догадывались).

1. IF – базовая логическая функция

Это одна из базовых функций, знакомых вам по Excel. Она помогает при решении разных SEO-задач. Формула IF выводит одно значение, если логическое выражение истинное, и другое – если оно ложное.

Пример. Есть список ключей с частотностями. Наша цель – занять ТОП-3. При этом мы хотим выбрать только такие ключи, каждый из которых приведет нам минимум 300 посетителей в месяц.

Определяем, какая доля трафика приходится на третью позицию в органике. Для этого заходим в сервис Advanced webranking и видим, что третья позиция приводит около 10% трафика из органики (конечно, эта цифра неточная, но это лучше, чем ничего).

Составляем выражение IF, которое будет возвращать значение 1 для ключей, который приведут минимум 300 посетителей, и 0 – для остальных ключей:

Обратите внимание, в строке 7 формула выдала ошибку, поскольку значение частотности задано в неверном формате. Для подобных ситуаций есть продвинутая версия функции IF – IFERROR.

Важно: использование в формуле запятой или точки для десятичных дробей определено в настройках ваших таблиц.

2. IFERROR – присваиваем свое значение в случае ошибки

Функция позволяет вывести заданное значение в ячейку, если выдается ошибка.

=IFERROR(ваша формула;»значение в случае ошибки»)

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

Как видите, значение #VALUE! изменило вид на понятное нам «нет данных».

3. ARRAYFORMULA – протягиваем формулу вниз в один клик

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

Достаточно заключить исходную функцию в функцию ARRAYFORMULA, и формула применится ко всем ячейкам ниже. Причем при удалении добавлении строк формула все равно будет работать – без пробелов в расчетах.

Пример. Сделаем автоматическое применение формулы, описанной выше, для всех ячеек диапазона. Для этого заключаем исходную формулу в ARRAYFORMULA:

Обратите внимание, что вместо ячейки B2 мы указали диапазон, для которого применяем формулу (B2:B – это весь столбец B, начиная со второй строки). Если указать одну ячейку, формула не сработает.

Лайфхак. Нажмите сочетание клавиш CTRL+SHIFT+ENTER после ввода основной формулы, и функция ARRAYFORMULA применится автоматически.

ARRAYFORMULA работает не со всеми функциями. Например, она не совместима с GOOGLETRANSLATE и IMPORTXML, о которых расскажем ниже.

4. LEN – считаем количество символов в ячейке

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

В SEO функция LEN применяется, например, при составлении мета-тегов title и description. Символы функция считает с пробелами.

=LEN(ячейка с текстом)

Пример. Нам нужно составить тайтлы для всех страниц сайта. Мы знаем, что в результатах поиска отображается около 55 символов. Наша задача – составить тайтлы так, чтобы самая важная информация была в первых 55 символах. Прописываем формулу LEN для заполняемых ячеек. Теперь мы точно знаем, когда приближаемся к отображаемым 55 символам.

5. TRIM – удаляем пробелы в начале и конце фразы

Когда парсишь семантику из разных источников, часто она содержит «мусорные» элементы – пробелы, плюсики, спецсимволы. Рассмотрим функции, которые помогают быстро почистить ядро. Одна из них – TRIM.

Эта функция удаляет пробелы в начале и конце фразы, указанной в ячейке.

=TRIM(ячейка, в которой нужно удалить пробелы до и после фразы)

Функция удаляет все пробелы до и после фразы – сколько бы их там ни было.

6. SUBSTITUTE – меняем/удаляем пробелы и спецсимволы

Универсальная функция замены/удаления символов в ячейках.

=SUBSTITUTE(где искать;»что искать»;»на что менять»;номер соответствия)

Номер соответствия – порядковый номер встреченного значения на замену, например, первое встреченное заменить, остальные оставить. Опциональный параметр.

Пример. У нас есть выгрузка ключевых фраз из Яндекс.Вордстат. Многие ключи содержат плюсики. Нам нужно их удалить.

Формула будет иметь вид:

• где искать – указали ячейку с данными;
• «что искать» – указали плюсик, который нужно удалить;
• «на что менять» – поскольку символ нужно удалить, мы указали кавычки без символов внутри; если бы нам нужна была замена, здесь бы мы прописали текст, на который нужно заменить плюсик;
• номер соответствия – здесь мы ничего не указали, и функция удалит все плюсы в фразе; если бы мы указали 1, то функция удаляла бы только первый плюсик, если 2 – второй и т. д.

7. LOWER – переводим буквы из верхнего регистра в нижний

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

=LOWER(ячейка, текст в которой нужно перевести в нижний регистр)

8. UNIQUE – выводим данные без дублирующихся ячеек

Функция анализирует выделенный диапазон на предмет полных дублей и выводит только уникальные строки – в том же порядке, что и в исходном диапазоне.

Пример. Мы собрали ключи из Яндекс.Вордстат, поисковых подсказок, парсили слова конкурентов. Естественно, в этом массиве ключей у нас будут дубли. Нам они не нужны. Убираем их с помощью UNIQUE.

Если вы хотите «одним махом» очистить ядро от мусора, используйте бесплатный нормализатор слов. Он убирает дублирующиеся фразы (с учетом перестановок), меняет регистры, удаляет пробелы и спецсимволы. По сути, он делает то же самое, что и функции TRIM, SUBSTITUTE, LOWER и UNIQUE вместе взятые – только в один клик.

9. SEARCH – находим данные в строке

С помощью этой функции вы быстро найдете необходимые вам строки с большом массиве данных.

=SEARCH(«что искать»;где искать)

Функция используется в разных ситуациях:

• выделить ключевые фразы с необходимым интентом (например, брендированные или связанные с определенной тематикой, товаром или услугой);
• найти определенные символы в URL (например, UTM-параметры или знак вопроса);
• найти URL для целей линкбилдинга – например, содержащие слова «guest-post»).

Пример. У нас есть список ключей для интернет-магазина дверей. Мы хотим найти все брендированные запросы и отметить их в таблице. Для этого используем формулу:

Но в таком виде формула при отсутствии слова «porta» в ключе выведет нам #VALUE. Кроме того, при наличии этого слова в искомой ячейке функция будет проставлять номер символа, с которого начинается это слово. Выглядит результат так:

Для получения результата поиска в удобной для нас форме используем дополнительно функции IF и IFERROR:

10. SPLIT – разбиваем фразы на отдельные слова

Функция делит строки на фрагменты, используя заданный разделитель.

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

Пример. У нас есть список доменов. Нам нужно разделить их на названия доменов и расширения. В функции SPLIT в качестве разделителя указываем точку и получаем результат:

11. CONCATENATE – объединяем данные в ячейках

Эта функция, в отличие от предыдущей, объединяет данные из нескольких ячеек.

=CONCATENATE(ячейка 1;ячейка 2;. )

Важно: в формулу можно вставлять не только значения ячеек, но и символы (в прямых кавычках).

Пример. В примере с функцией SPLIT мы разделили домены. Сделаем обратную операцию с помощью CONCATENATE (указываем объединяемые ячейки и между ними указываем разделитель — точку):

12. VLOOKUP – ищем значения в другом диапазоне данных

Функция выполняет поиск ключа в первом столбце диапазона и возвращает значение указанной ячейки в найденной строке.

Пример 1. Есть два массива ключевых фраз, полученных из разных источников. Нужно найти ключи в первом массиве, которые не встречаются во втором массиве. Для этого используем формулу:

• задали диапазон A2:A, из которого берем ключи для сравнения;
• задали диапазон B2:B, с которым сравниваем ключи из столбца А;
• задали номер столбца (1), из которого подтягиваем ключи при совпадениях;
• false – указали, что сортировка нам не нужна.

Функция VLOOKUP часто используется при поиске данных на разных листах или в разных документах.

Пример 2. Мы выгрузили данные из Яндекс.Вебмастера и Google Search Console об индексации страниц сайта. Наша задача – сопоставить данные и определить, какие страницы индексируются в одном поисковике, но не индексируются в другом.

Заносим результаты выгрузок в файл Google Sheets. На одном листе – URL из Google, на втором – из Яндекса.

В ячейке C2 прописываем функцию VLOOKUP. Сразу заключаем в функцию в ARRAYFORMULA для автоматического протягивания вниз:

Теперь мы сразу видим, какие страницы проиндексированы в Google, но не проиндексированы в Яндексе.

• задали диапазон A2:A текущего листа, из которого берем значение для сравнения;
• задали диапазон Yandex!A2:A листа с выгрузкой из Яндекса, с которым будем сравнивать значения URL из Google;
• указали номер столбца листа с выгрузкой из Яндекса, значения из которого подтягиваем при совпадении значений из сравниваемых диапазонов;
• false – указали, что сортировка нам не нужна.

Если же вам нужно проверить одновременно индексацию конкретных страниц в Яндексе и Google, воспользуйтесь инструментом от PromoPult. Загрузите список URL и запустите проверку. Если страница проиндексирована в поисковике, в столбце будет цифра 1, если нет – 0.

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

13. IMPORTRANGE – импортируем данные из других таблиц

Функция позволяет вставить в текущий файл данные из других таблиц.

=IMPORTRANGE(«ссылка на документ»;»ссылка на диапазон данных»)

Пример. Вы продвигаете сайт клиента. Над проектом работает три специалиста: линкбилдер, SEO-специалист и копирайтер. Каждый ведет свой отчет. Клиент заинтересован отслеживать процесс в режиме онлайн. Вы формируете для него один отчет с вкладками: «Ссылки», «Позиции», «Тексты». На эти вкладки с помощью функции IMPORTRANGE подтягиваются данные по каждому направлению.

Преимущество функции в том, что вы открываете доступ только к конкретным листам. При этом внутренние части отчетов специалистов остаются недоступны для клиентов.

14. IMPORTXML – парсим данные с веб-страниц

«Развесистая» функция для парсинга данных с веб-страниц с помощью XPath.

Вот лишь несколько вариантов использования этой функции:

• извлечение метаданных из списка URL (title, description), а также заголовков h1-h6;
• сбор e-mail со страниц;
• парсинг адресов страниц в соцсетях.

Пример. Нам нужно собрать содержимое тегов title для списка URL. Запрос XPath, который мы используем для получения этого заголовка, выглядит так: «//title».

Формула будет такой:

IMPORTXML не работает с ARRAYFORMULA, так что вручную копируем формулу во все ячейки.

Вот другие запросы XPath, которые вам будут полезны:

• выгрузить заголовки H1 (и по аналогии – h2-h6): //h1
• спарсить мета-теги description: //meta[@name=’description’]/@content
• спарсить мета-теги keywords: //meta[@name=’keywords’]/@content
• извлечь e-mail адреса: //a[contains(href, ‘mailTo:’) or contains(href, ‘mailto:’)]/@href
• извлечь ссылки на профили в соцсетях: //a[contains(href, ‘vk.com/’) or contains(href, ‘twitter.com/’) or contains(href, ‘facebook.com/’) or contains(href, ‘instagram.com/’) or contains(href, ‘youtube.com/’)]/@href

Если вам нужно узнать XPath-запрос для других элементов страницы, откройте ее в Google Chrome, перейдите в режим просмотра кода, найдите элемент, кликните по нему правой кнопкой и нажмите Copy / Copy XPath.

15. GOOGLETRANSLATE – переводим ключевики и другие данные

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

=GOOGLETRANSLATE(«текст»; [язык_оригинала]; [язык_перевода])

Например, если нам нужно перевести ключи с русского на английский, формула будет такой:

Если бы мы переводили с английского на русский, то нужно было бы изменить порядок языков:

GOOGLETRANSLATE не работает с ARRAYFORMULA, так что, как и в случае с IMPORTXML, протягиваем формулу вручную.

16. REGEXEXTRACT – извлекаем нужный текст из ячеек

Эта функция позволяет извлечь из строки с данными текст, описанный с помощью регулярных выражений RE2, поддерживаемых Google. Синтаксис регулярных выражений достаточно сложный, больше примеров вы найдете в справке Google.

=REGEXEXTRACT(где искать;”регулярное выражение”)

Пример 1. У нас есть список URL. Нужно извлечь домены. Здесь нам поможет регулярное выражение:

Пример 2. В списке ключевых фраз нужно найти брендированные ключи со словами «porta» и «порта». Для поиска фраз с вхождением любого из этих слов используем регулярное выражение:

Как видите, в таблицах можно кроить и резать данные так, как вам будет нужно, достаточно разобраться в формулах.

Похожие публикации:

1. Как рисовать в гугл документах
2. Как сделать галочку в гугл таблице
3. Не поддерживается на вашем устройстве google play как исправить
4. Сервисы гугл плей удалить данные что будет