5 функций google таблиц, которые вы должны знать

Знакомимся с Google Docs

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

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

Все данные в примерах ниже вымышлены.

Как сделать простой выпадающий список в Гугл таблицах

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

Лист на котором будет отображаться результат я так и назвал Результат, а лист, который сразу был под названием Лист 2, я назвал Данные, на нем я размещу исходные данные.

После того как мы сделали эти простые действия, приступим к заполнению данных. Для этого перейдем на лист который мы назвали Данные и добавим некоторые данные, у меня это Ягоды, Фрукты и Овощи, расположенные по порядку в ячейках A1:A3:

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

Теперь переходим в панели меню по следующему пути: Данные -> Проверка данных:

Откроется вот такое контекстное меню:

В котором мы видим следующие пункты:

• Диапазон ячеек – здесь мы видим название нашего листа и адрес ячейки в которой будет наш выпадающий список на данном листе;
• Правила – здесь мы будем задавать правила для отображения нашего списка. По умолчанию значение стоит Значения из диапазона, оно нам как раз и нужно, так что ничего не трогаем и оставляем как есть. А вот в поле справа от значения нам необходимо указать путь до наших данных на втором листе, в нашем случае это: ‘Данные’!A1:A3Слово Данные – это ссылка на лист с нашими исходными данными, взятая в одинарные кавычки, затем восклицательный знак и номера ячеек с нашими данными.
• Ниже мы видим чек бокс Показывать раскрывающийся список в ячейке – он выделен по умолчанию и это значит, что справа ячейки с нашим выпадающим списком будет треугольничек. Если он вам по каким-то причинам не нужен, то снимите чек бокс.
• Для неверных данных – здесь два радио бокса: показывать предупреждение и запрещать ввод данных. По умолчанию стоит показывать предупреждение и это значит, что если вы введете не соответствующее значение из исходных данных, то всплывет сообщение с ошибкой.А если выберете запрещать ввод данных, то при неверном (несоответствующем) исходным данным значении появится предупреждающий pop-up с текстом «Данные, которые вы ввели в ячейку A3, не соответствуют правилам проверки».
• Оформление – в данном пункте мы видим чекбокс «Показывать текст справки для проверки данных:» и ниже поле, где нам предлагается готовый вариант сообщения, который можно исправить на свое. Именно это сообщение будет всплывать при введении не правильных значений, по умолчанию стоит: «Введите значение из диапазона ‘Данные’!A1:A3»

Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:

Полезные функции Google Таблиц, которых нет в Excel +31

• 21.06.17 17:00

•
Sianuk
•
#331360
•
Хабрахабр

•
Tutorial

•

•

15700

Google API, Алгоритмы, Data Mining, Big Data

Cтатья написана в соавторстве с Ренатом Шагабутдиновым.
В этой статье речь пойдет о нескольких очень полезных функциях Google Таблиц, которых нет в Excel (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)
Очень много букв, но есть разборы интересных кейсов, все примеры, кстати, можно рассмотреть поближе в Google Документе goo.gl/cOQAd9 (файл-> создать копию, чтобы скопировать файл себе на Google Диск и иметь возможность редактирования).Если результат формулы занимает больше одной ячейки
Сначала про важную особенность отображения результатов формул в Google Таблицах. Если ваша формула возвращает более одной ячейки, то весь этот массив отобразится сразу и займет столько ячеек и столбцов, сколько для него потребуется (в Excel для этого нужно было бы во все эти ячейки ввести формулу массива). На следующем примере посмотрим, как это работает.

SORT

Как в SORT добавить заголовки таблицы?Как объединить несколько диапазонов данных и отсортировать (и не только)?Google Документе

FILTER

Одно условиеДва условия и работа с датойИнтерактивный график при помощи FILTER и SPARKLINEВыпадающий список.

• выделяем ячейку Е2;
• меню Данные > Проверка данных;
• правила: Значение из диапазона и в диапазоне выбираем столбец с сотрудниками из исходных данных, не переживайте, что фамилии повторяются, в выпадающем списке останутся лишь уникальные значения;

IMPORTRANGE

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

!

IMAGE: добавляем изображения в ячейки

1. изображение растягивается до размеров ячейки с сохранением соотношения сторон;
2. изображение растягивается без сохранения соотношения сторон, целиком заполняя
3. изображение вставляется с оригинальным размером;
4. вы указываете размеры изображения в третьем и четвертом аргументам функции и . , , соответственно, нужны только при значении аргумента mode = 4. Они задаются в пикселях.

GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках

text – это текст, который нужно переводить. Можно взять текст в кавычки и записать прямо в формулу, но удобнее сослаться на ячейку, в которой текст записан.
– язык, с которого мы переводим;
– язык, на который мы переводим.Евгений Намоконов и Ренат Шагабутдинов, а еще мы ведем канал в телеграмме, где разбираем разные кейсы с Google Таблицами, если вам интересно — заглядывайте в гости, ссылку можно найти в моем профиле.

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

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

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

Синтаксис:

=ARRAYFORMULA(исходная формула)

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

=ARRAYFORMULA(IFERROR(IF(B2:B*0,1>=300;»1″;»0″);»нет данных»))

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

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

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

Пять Формул Google Таблиц Которые Стоит Попробовать

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

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

1. =TRIM

Когда вы открываете данные внутри электронных таблиц, то они не всегда могут быть аккуратны. В них могут быть пробелы.

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

Чтобы использовать TRIM, просто напечатайте =TRIM, и укажите ячейку, которая нуждается в обработке. Нажмите ввод, и вы быстро очистите данные, что вы можете видеть на примере ниже.

Функция TRIM быстро уберет промежуточные и конечные пробелы в ячейке.

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

2. =PROPER

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

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

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

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

3. =GOOGLEFINANCE

Для тех кто следить за своим пакетом акции, функция  =GOOGLEFINANCE — просто спаситель. Это функция, которая обращается непосредственно к сервису Google Finance, и извлекает от туда информацию о состоянии рынков акций, паевых фондов, рынков облигаций и др.

Функция GOOGLEFINANCE может еще использовать различные данные касающиеся безопасности, но вот здесь мы рассмотрим обычный пример со стоимостью акций компании Apple:

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

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

Google Таблицы Как Отслеживать Данные на Рынке Акций в Google Таблицах с помощью функции GOOGLEFINANCE Andrew Childress

4. =GOOGLETRANSLATE

Сервис Google Переводчик — очень полезный инструмент, который позволяет быстро сделать перевод. Но еще лучше, когда вы можете сделать этот перевод прямо в таблице, благодаря функции =GOOGLETRANSLATE.

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

Вместо того чтобы постоянно перескакивать из таблицы на страниц Google Переводчика, копировать и вставлять туда-сюда слова, просто используйте функцию =GOOGLETRANSLATE прямо в таблице.

Простейший вариант использования функции — это просто напечатать =GOOGLETRANSLATE(A2), чтобы указать функции, чтобы она перевела текст в ячейке А2. Google, попытается угадать язык и представить перевод на языке, вашего аккаунта.

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

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

5. =RANDBETWEEN

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

Чтобы использовать =RANDBETWEEN, откройте скобки и укажите нижний предел для величин и верхний предел. Например для генерации величин в диапазоне от 1 до 100, необходимо использовать = RANDBETWEEN(1,100). Посмотрите пример на скриншоте ниже.

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

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

Коды для импорта других данных

Функция IMPORTRANGE — не единственный способ импорта данных. Вот некоторые другие варианты ссылок:

1. = ImportXML (https://www.techjunkie.com/google-sheets-reference-file/, ): Это позволит импортировать данные из любого формата структурированных данных, таких как XML, HTML, TSV, CSV, ATOM XML и RSS-каналы.
2. = IMPORTHTML (url, query, index) скопирует список или таблицу со страницы HTML.
3. = IMPORTFEED (URL, , , ) добавит ленту RSS или ATOM.
4. = IMPORTDATA (url) — лучший способ импортировать файл CSV или TSV из одной ссылки. В одной электронной таблице может быть максимум 50 команд IMPORTDATA.
5. = HYPERLINK (URL, ) — это ссылка на любую веб-страницу в Интернете. Вы можете ссылаться на несколько типов ссылок, таких как http: //, https: //, ftp: //, gopher: //, news: // и другие. Если вы используете протокол, который не поддерживается Sheets, ваша метка ссылки будет отображаться, но ссылка не будет прикреплена.

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

Совет: Используйте Руководство

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

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

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

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

Узнайте больше способов использовать Google Sheets

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

К счастью, здесь, в TechJunkie, у нас есть множество статей, которые помогут вам максимально использовать возможности Google Sheets, в том числе «Как рассчитать время в Google Sheets», «Как вычесть в Google Sheets формулу» и «Как включить темный режим в Google Sheets».

У вас есть другие способы связать Google Sheets или рабочие книги друг с другом? Расскажите нам о них ниже, если вы делаете!

Группируем данные с помощью GROUP BY и PIVOT

Сгруппировать данные, используя QUERY, можно с помощью двух ключевых слов: GROUP BY и PIVOT, ниже рассмотрим примеры с ними.

Таблица, с которой мы будем работать:

Задачей будет вывести сумму продаж по каждой тематике, то есть сгруппировать данные по столбцу B.

Начнем с GROUP BY, текст функции будет таким:

=QUERY(‘Книги’!A1:C6;»select B, sum(C) group by B»)

Обратите внимание: чтобы функция работала, помимо группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий случай, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count()

Результат нашей формулы:

С помощью GROUP BY возможна группировка и по нескольким столбцам, для этого просто перечислите их, как в функции ниже и не забудьте добавить эти столбцы в SELECT:

Группировка с помощью PIVOT.

Обратите внимание, что здесь в SELECT не нужно писать столбец B, по которому данные будут сгруппированы

Пока отличие в том, что сгрупированные элементы расположены по столбцам, а не по строкам, как в GROUP BY.

Добавим еще один столбец для группировки.

Видите — два сгруппированных столбца отображаются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там каждый столбец группировки занимает отдельный столбец, то в PIVOT получается нечто вроде сводной таблицы с уникальными полями из нескольких элементов. По этим полям, кстати, потом можно довольно просто искать нужное значение с помощью ГПР или ПОИСКПОЗ.

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

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

Синтаксис:

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

Пример:

=IMPORTRANGE(«https://docs.google.com/spreadsheets/d/ХХХХХХХХ/»,»имя листа!A2:A25″)

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

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

IMPORTXML

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

Синтаксис:

IMPORTXML («URL»;»xpath_запрос»)

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

=IMPORTXML(«https://example.com/page1″,»//title»)

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

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

А чтобы быстро проверить коды ответа сервера страниц, подставьте xpath «//div//big» и введите URL онлайн-сервиса, который определяет код ответа сервера страниц. Формула будет выглядеть так:

=IMPORTXML(СЦЕПИТЬ("https://2ip.ru/server-response/?host=",A2),"//div//big")

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

Как создать выпадающий список и как с ним работать

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

Создание выпадающего списка

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

• Левой кнопкой мыши (ЛКМ) кликаем на нужную ячейку или выделяем сразу несколько вниз.
• По выделенному участку щелкаем правой кнопкой мыши (ПКМ) и в меню выбираем “Проверка данных”.
• В окне “Проверка данных” выставляем значения по таблице ниже ↓

Жмем на сохранить.

Готово. Теперь вы знаете, как создать выпадающий список в Google Таблицах.

Настраиваем окно “Проверка данных”	Что делать
Диапазон ячеек	Это поле не трогаем
Правила	Значения из диапазона. Меняем на “Значение из списка” → вводим необходимые значения. Показывать раскрывающийся список в ячейке. Если оставить флажок — на ячейке закрепится значок . Если убрать — и список будет отображаться по двойному щелчку ЛКМ.
Для неверных данных	Оставить “Показывать предупреждение” или изменить на “Запрещать ввод данных”,* чтобы создать выпадающий список в Гугл Таблице.
Оформление	Поставить флажок на “Показывать текст справки”. Откроется поле с предупреждением по умолчанию. Вы можете заменить его на свой текст.

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

Еще о работе с выпадающим списком

С тем, как сделать в Гугл Таблицах выпадающий список, мы разобрались. Осталось упомянуть еще несколько вариантов настроек, доступных для использования. В окне “Проверка данных”, в строке “Правила”, вы можете выбрать следующие настройки:

• Число → В диапазоне (Не в диапазоне, Больше, Больше или равно, Меньше, Меньше или равно и т. д.) → вписать числа.
• Текст → Содержит (Не содержит, Равняется, Является допустимым URL / адресом электронной почты) → вписать нужный текст.
• Дата → Является допустимой датой (Равняется, До, После, Указана или до и т. д.) → указать дату.

Строим сводную таблицу со средними/максимальными значениями по тематикам

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

QUERY(‘Книги’!A1:D;»SELECT avg(C), avg(D) pivot B»;1)

Мы используем похожий диапазон (в отличие от предыдущего в нем есть продажи за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тематика).

Полученный результат транспонируем для удобного отображения (с помощью функции TRANSPOSE (ТРАНСП)):

Можно использовать и другие функции вместо avg (среднего), например max (максимальные значения):

Или отобразить и среднее, и максимум, но только по столбцу D:

SELECT avg(D), max(D)

Как связать диапазон ячеек

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

Чтобы связать несколько ячеек в Excel, выполните следующие действия.

• В исходной вкладке с данными (Лист1), выделите ячейки, на которые вы хотите сослаться.
• Скопируйте ячейки (Ctrl/команда + С, или щелкните правой кнопкой мыши и выберите копия).
• Перейти на другую вкладку (Sheet2) и нажмите на ячейку (или ячейки), где вы хотите разместить ссылки.

Щелкните правой кнопкой мыши по ячейке (ям) и выберите Специальная паста…

В левом нижнем углу меню выберите Вставить ссылку,

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

Ссылки на данные из других таблиц Google

Google Таблицы – Пример ссылки на ячейку на другом листе

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

=Лист2!D8

«Лист2» — это имя листа с данными, а «B5» — это ячейка, на которую вы хотите сослаться. Между ними ставится восклицательный знак. Если вы хотите ссылаться на данные из совершенно другой таблицы, вы должны использовать функцию IMPORTRANGE, например:

IMPORTRANGE(«https://docs.google.com/spreadsheets/d/1QZj_0Gyqvd»; «Лист1!A1:C10»)

Как Создавать Формулы и Функции в Google Таблицах

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

После того, как вы напечатали знак равенства, вы можете вводить простую формулу, например такую, как =D3+E3, чтобы выполнить вычисления с двумя ячейками.

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

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

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

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

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

Синтаксис:

=IF(логическое_выражение;»значение_истина»;»значение_ложь»)

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

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

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

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

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

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

Зачем нужна функция SPLIT (или Data — Text to Columns)

SPLIT (; «»; TRUE) выводит текст, разделенный определенными символами, в разные ячейки.

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

Допустим, в категориях есть теги. Задача — выгрузить все теги и проверить их на дубли, адекватность и упущенные теги. Для этого нужны:

• список категорий (URL), где есть теги;
• path-запрос, который позволит вытянуть со страниц URL и анкоры тегов;
• сервис для сканирования (например, Netpeak Spider).

Часть итоговой таблицы (URL и анкоры страниц тегов):

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

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

Данную формулу можно заменить опцией таблиц Google: Data > Text to Columns (Данные > Разделить на колонки). Перед применением следует выделить весь диапазон, к которому нужно применить функционал.

Создание ссылки

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

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

Откроется окно с созданной ссылкой. Скопируйте ее и можно отправить другому человеку. Когда он по ней перейдет, то откроется таблица в левом верхнем углу которой будет не привычное «1, 2, 3…» и «A, B, C…», а адрес той ячейки, что была крайней левой в выбранном вами диапазоне.

Второй вариант – как сделать слово или число в ячейке ссылкой. Снова выделите ее и выберите в контекстном меню «Вставить ссылку».

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

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