Текстовые функции Google Таблиц
В этой заметке описаны основные текстовые функции Google Таблиц. На скриншоте — четыре столбца:
Ниже — описание этих функций.
В скобках указаны названия функций-аналогов из Excel.
TRIM (СЖПРОБЕЛЫ) удаляет лишние пробелы между словами, а также до первого и после последнего слова в тексте. Остается лишь по одному пробелу между словами. Единственный аргумент — ячейка с текстом (теоретически можно и текст в кавычках указать прямо в самой формуле, но на практике вряд ли такое понадобится)
UPPER (ПРОПИСН) и LOWER (СТРОЧН) меняют регистр текста на верхний и нижний соответственно. У них тоже один аргумент.
Функция LEN (ДЛСТР) определяет длину текста. Учитываются все символы, включая пробелы. Ее можно использовать как формулу массива — см. строку 7 в примере. В таком случае удастся посчитать сумму длин текстов из целого диапазона.
Функция FIND (в Excel 2 аналога — НАЙТИ учитывает регистр, ПОИСК — нет) определяет позицию вхождения слова или символа в текст.
Первый аргумент — текст, который мы ищем, второй — ячейка с текстом, в котором будем искать. Есть третий — необязательный — аргумент: позиция начала поиска. Искать можно не с начала. В примере слово кефир в исходном тексте стоит на 26 позиции.
Функция SUBSTITUTE (ЗАМЕНИТЬ) меняет в тексте одно слово (символ, текст) на другое.
Например, функция ниже:
=SUBSTITUTE(«пробежал я давеча марафон за 3:15″;»3:15»;»2:55″)
выдаст такой приятный результат:
пробежал я давеча марафон за 2:55
Функция TEXT (ТЕКСТ) нужна в тех случаях, когда вы с помощью формул составляете текстовую фразу, в которой используются числовые значения или даты из ячеек вашей таблицы.
В примере:
=»сегодня «&B13
выдает дату в неотформатированном виде, т.е. как число:
А функция TEXT позволяет получить красивый результат:
=»сегодня «&TEXT(B14;»DD/MM/YYYY»)
Функции LEFT (ЛЕВСИМВ) И RIGHT (ПРАВСИМВ) вырезают из текста (первый аргумент) определенное (второй аргумент) количество знаков.
=ЛЕВСИМВ(«Слово»;3) = «Сло»
Функция MID (ПСТР) вырезает из текста символы, начиная с определенной позиции (второй аргумент):
=MID(«Машина»;3;2) = «ши»
Функция SPLIT (Аналог в Excel — инструмент «Текст по столбцам», вкладка «Данные» на ленте)
разделяет текст (первый аргумент) по определенному разделителю (второй аргумент).
Разделитель — ибо это текст — нужно указать в кавычках (в примере это пробел):
=SPLIT(B21;» «)
9 простых, но важных лайфхаков по работе с Google Таблицами
Были времена, когда «эксель» считали лишь размером одежды, а бухгалтера пересчитывали сметы за электронными таблицами на калькуляторе. Сейчас же решения строятся на всестороннем анализе цифр, а Big Data давно стали мировым трендом. Да и работа в Google Spreadsheets давно стала нормой и обыденностью. Вот только многие ограничиваются общими знаниями о них, не вникая глубже в возможности. Так появляются стереотипы о том, что Google Spreadsheets — это сложно, нудно и лучше с ними дела лишний раз не иметь.
Основатель Ringostat Александр Максименюк — настоящий фанат Google Spreadsheets и знает о таблицах чуть более, чем все. С его подачи (а также просто потому, что это очень удобно) мы работаем в Spreadsheets постоянно: составляем медиапланы и отчеты, строим прогнозы, графики и дашборды разных уровней. За время работы у нас накопилось солидное количество лайфхаков, частью из которых мы делимся в этой статье.
1. Нейминг
Многие забывают прописывать названия документов или относятся к этой части работы, как персонаж известного мультфильма: «И так сойдет!». Документов становится все больше и вот уже команда утопает в хаосе безымянных файлов, выискивая один-единственный нужный.
Называйте документы так, чтобы сразу найти нужный, стукнув кулаком по клавиатуре в четыре часа утра после бурной вечеринки. Шутка, конечно, но прописать ключевые слова в названии таблицы будет совсем не лишним. У нас в Ringostat такой шаблон оформления названий:
Что в документе / Зачем он нужен / Кому он нужен / Ключевые слова
Например, если оформить документ так, его можно будет найти, вбив в поиске любое слово, связанное с таблицами и оформлением:
2. Горячие клавиши
Совет «используйте горячие клавиши» настолько банален, что его дают даже тренеры личностного роста и тайм-менеджмента. Доведенное до автоматизма использование горячих клавиш экономит время и требует меньшей концентрации внимания, чем поиск нужной функции в меню и выпадающих окнах. Если вы пока не освоили горячие клавиши, начните со списка ниже. А если уже встали на путь работы без мышки, все равно пробегитесь по списку из справки, там много полезного.
3. Оформление
4. Валидация
Если вы работаете с большим массивом цифр, велика вероятность ошибиться по невнимательности. Например, если среди диапазона чисел притаится одна ячейка с процентом, таблица не сможет посчитать формулу.
Во избежание таких недочетов, используйте проверку данных. Она пригодится как при ручном вводе значений, так и при «подтягивании» информации извне. Чтобы начать валидацию, выбираем в меню «Данные — Проверка данных» и настраиваем ее под свои нужды.
Можно запретить любые данные — хоть даты в неправильном формате, хоть текст без буквы «а». При проверке уже заполненных ячеек, валидация указывает на ошибки. Позже эта функция, в зависимости от настройки, либо блокирует ввод некорректных данных, либо предупреждает об ошибке.
5. Предсказания
В электронных таблицах многое строится на подсчете, формулах и функциях. Вы наверняка знакомы с такими формулами как SUM, AVERAGE или ROUND, поэтому сегодня мы расскажем нечто более захватывающее.
Функция FORECAST, или ПРЕДСКАЗ в русской локализации, выстраивает прогноз того, как будут заполнены последующие клетки, исходя из заполнения предыдущих. Машинный подсчет будет несколько груб, но он может обрисовать тенденцию.
Попробуем предсказать изменение аудитории сайта. Для этого возьмем данные о посещении и запишем их рядом с номером соответствующего месяца. Добавим еще пару строк для месяцев, которые будем предсказывать:
Затем в ячейке B8, где мы хотим увидеть данные на будущее, прописываем такую формулу:
ПРЕДСКАЗ — это сама формула;
A8 — это показатель, для которого будет рассчитываться результат формулы;
$B$2:$B$8 — это диапазон известных данных, на основе которых Google Spreadsheets составляет прогноз;
В результате таблица показывает, как аудитория сайта вырастет, если продолжать работать в обычном темпе.
6. Диаграммы
В предыдущем примере нам достаточно было «на глаз» оценить показатели, чтобы понять: аудитория растет, при том почти равномерно. Но для работы с сотнями и тысячами показателей, беглого взгляда на цифры будет мало. Тут на помощь приходят диаграммы, которые преобразуют массив информации в понятную схему.
Каждая из категорий имеет свои подвиды, есть также инструменты для создания блок-схем, лепестковых диаграмм других нестандартных решений.
Чтобы построить диаграмму, выбираем в меню «Вставка — Диаграмма». Поверх таблицы появится активное поле элемента, а в правой части экрана откроется меню «Редактор диаграмм». Выберите внешний вид графика, в поле «Диапазон данных» отметьте цифры, которые будут отражаться в виде графика, в нашем случае — столбец с количеством посетителей. В поле диаграммы появляется синяя линия, иллюстрирующая разницу между показателями. Значение оси Y автоматически подтянулось, исходя из разряда чисел. В поле «Ось X» выбираем столбец с номерами месяцев, после чего на диаграмме появляется сетка координат и можно сориентироваться, какому месяцу соответствует каждый показатель.
7. Чек-листы
В Google Spreadsheets есть возможность превратить ячейку в чекбокс, благодаря чему таблица становится еще и чек-листом. Для этого выбираем в меню «Вставка — Флажок». Например, при подготовке выставочного стенда, чекбоксы можно добавить прямо в смету и мониторить готовность к мероприятию в одном документе. Чекбоксы пригодятся и для мониторинга эффективности команды: сотрудники могут записывать недельный план в общую таблицу и отмечать задачи по мере выполнения. А подсчитать продуктивность поможет формула:
ЕСЛИОШИБКА — сама формула.
СЧЁТЕСЛИ — формула подсчета выполненных задач
B1:B5 — ячейки, в которых находятся чекбоксы
«TRUE» — уточнение, что чекбокс должен быть отмечен
СЧЁТЗ — формула подсчета поставленных задач
A1:A5 — ячейки, в которые должны быть вписаны задачи
В итоге таблица сама посчитает, насколько сотрудник выполнил план
8. Работа с текстом
9. Волшебная ARRAYFORMULA
Используйте эти хаки и вы полюбите Google Spreadsheets так же, как любим их мы 🙂
Google Sheets формулы
В данной статье мы поговорим о том, что такое формула в Гугл таблицах и как ими пользоваться — как вставлять и удалять, переносить и копировать, рассмотрим формулы массивов в Гугл таблицах, научимся считать используя формулы.
Какие бывают типы формул в Гугл таблицах
Формулы в Гугл таблицах бывают двух видов: простые и комплексные. Простые как правило содержат константы, ссылки на ячейки внутри рабочего листа и операторы. Комплексные формулы могут содержать гораздо больше данных – ссылки на ячейки, одновременно несколько функций, константы, операторы, имена диапазонов.
Элементы формул в Гугл таблицах
Давайте поближе познакомимся с элементами формул в гугл таблицах.
Константы – это какие-то фиксированные данные, чаще всего цифры.
Операторы – это символы вычитания, сложения, деления и умножения, символом деления служит так называемый слеш /.
Ссылки на ячейки – очень часто формулы содержат ссылки на ячейки внутри рабочего листа или ссылки на ячейки на других листах рабочей книги. Ссылки бывают трех типов – относительные, абсолютные и смешанные.
В данном случае мы вычитаем содержание ячейки A4 из ячейки A2. При копировании данной простой формулы в другую ячейку, поменяются адреса ячеек и в формуле. Например, если скопировать эту ячейку и вставить в ячейку C1, то мы увидим, что адреса в ссылках внутри формулы также сменились: 
Абсолютные ссылки выглядят таким образом: =$A$2-$A$4 
Теперь куда бы мы не скопировали нашу простую формулу, то ссылки по прежнему будут указывать на ячейки A2 и A4: =$A$2-$A$4 
Смешанные ссылки имеют такой вид: A$2-A$4 
Скопировав данную формулу в нашу новую ячейку C1, мы получим следующий результат: =С$2-С$4 
Как видим, столбец сменился на C, потому что ссылка столбца относительная, а строки остались по прежнему 2 и 4, поскольку на строки мы указали абсолютные ссылки.
Имена диапазонов – как и в excel, в гугл таблицах диапазонам и отдельным ячейкам можно давать имена. Делается это для того, чтобы было легче проводить вычисления в больших таблицах.
Кроме того, если именованные диапазоны даже перенести в другое место, то результаты вычислений останутся не тронутыми в отличие от диапазонов с содержанием относительных ссылок. Более подробно именованные диапазоны мы рассмотрим чуть ниже.
Функции – так же как и в excel, гугл таблицы имеют функции. Например, СУММ (SUM), СЧЕТ(COUNT) и т.д. Все это элементы формул, которые могут творить чудеса в умелых руках, ниже мы рассмотрим несколько примеров простых и комплексных формул.
Как в Гугл таблицах вставить формулу, отредактировать ее и скопировать
Или нажмите F2 на вашей клавиатуре.
Для редактирования формулы необходимо так же выделить ячейку с формулой, которую вы хотите отредактировать и нажмите F2 на клавиатуре или же поставьте курсор в строку формул.
Чтобы удалить формулу просто выделите ячейку с формулой и нажмите Delete на вашей клавиатуре.
Скопировать формулу можно двумя способами:
Но что, если нам формула в новой ячейке не нужна, а только значение? Для того, чтобы вставить в новую ячейку только значение ячейки где есть формула, нам надо сделать пару действий.
После того как мы вставили данные в новую ячейку появится вот такая подсказка: 
По нажатию на нее откроется небольшое контекстное меню: 
В нем нам необходимо выбрать пункт Вставить только значения.
Как создать формулу в Гугл таблицах с данными расположенными на разных листах
Бывают случаи, когда необходимо посчитать сумму значений диапазонов, которые расположены на разных листах вашей Гугл таблице.
Для того, чтобы посчитать в Гугл таблицах значения, которые находятся на другом листе, необходимо использовать формулу, в состав которой входит ссылка на тот самый лист.
Например, у нас есть диапазон данных B4:B8 на первом листе: 
Обратите внимание на то, что ссылку на Лист1 необходимо взять в одинарные кавычки, иначе формула не будет работать. После ссылки на необходимый лист ставим символ восклицательного знака и диапазон ячеек значения которых мы хотим посчитать.
Весь диапазон мы закрываем в скобки и перед ними подставляем функцию СУММ, которая будет суммировать все значения в данном диапазоне.
Но что, если нам необходимо посчитать значения с нескольких листов в нужных нам диапазонах?
Например, посчитать данные из вышеуказанного диапазона B4:B8 на первом листе и данные диапазона B4:B6 на втором листе. И все это суммировать в одной ячейке.
Формулы в Гугл таблицах – примеры
В предыдущих разделах статьи мы уже рассмотрели несколько примеров формул содержащих ссылки, в том числе и ссылки на другие листы.
В данном же разделе статьи мы рассмотрим самые простейшие формулы, формулы содержащие именованные диапазоны и формулы массива в Google таблицах.
Простая формула в Гугл таблицах
Простая формула, как правило это формула содержащая пару констант и операторы:
Данная формула выполнит умножение содержимого в ячейках A2 и B4 в нашей Гугл таблице.
А вот пример формулы в которой есть константа и ссылка на ячейку:
Такая формула сложит цифру 25 и содержимое ячейки C3.
Формулы с именованными диапазонами в Гугл таблицах
Именованные диапазоны упрощают жизнь человеку работающему с большим массивом данных в таблицах, в том числе и Гугл таблицах.
Для начала, нам необходимо дать диапазонам имена, а потом уже мы их будем использовать в формуле.
Итак, для того, чтобы диапазону в Гугл таблицах дать имя, необходимо выделить диапазон, которому мы хотим дать имя, потом кликнуть правой кнопкой мыши и в контекстном меню выбрать пункт Определить именованный диапазон. 
После этого откроется сайдбар справа, где нам будет предложено ввести имя диапазона. По умолчанию в поле для имени будет написано ИменованныйДиапазон1 (если, конечно, это первый диапазон, которому мы даем имя, в рамках данной таблицы): 
Давайте дадим ему название «Январь». Предположим, что это расчет доходов за январь.
Ниже указан будет наш выделенный диапазон на данном листе. Его можно поменять и в том числе сам Лист. Жмем Готово ниже.
Теперь этот диапазон можно использовать при расчетах в формулах. Например, мы хотим суммировать весь доход за Январь. Для этого добавим ячейку Январь и рядом с ней в ячейку введем следующую формулу: =СУММ(Январь) — по умолчанию Гугл таблицы все формулы переводят на английский вариант, в данном случае СУММ превратилось в SUM: 
Теперь куда бы мы не переносили наш диапазон Январь, он всегда будет содержать именно те данные, которые в нем указаны и мы можем делать расчеты не задумываясь о том, в каких ячейках и листах теперь он находится. Удобно, не так ли?
Давайте теперь попробуем произвести расчет формулы с нашим диапазоном, но перенесем расчет на другой лист:
Как видите, все тот же диапазон, то же имя, но другой лист. И никаких затруднений с ячейками и номерами листов! Точно так же мы можем переместить (не скопировать!) наш диапазон в другое место или даже лист. Для этого просто нажимаем сочетание клавиш Ctrl + X для того, чтобы вырезать диапазон и Ctrl + V для того, чтобы вставить в другом месте.
Формулы массива в Гугл таблицах
Массивы как правило включают в себя несколько функций и используются массивы для более сложных вычислений.
Например, у нас есть таблица с данными по продажам нескольких менеджеров и мы хотим узнать максимальные продажи конкретного менеджера, конкретного продукта: 
Мы получили значение максимальной продажи Груш менеджером по продажам Максимом.
Вот и подошла к концу данная статья, надеюсь она была полезна. Если я что-то пропустил или у вас есть дополнительные вопросы, то задавайте, я постараюсь ответить на них.
«Google Таблицы»: большой гайд для новичков
Сложно представить работу интернет-маркетолога без электронных таблиц. Раньше основным инструментом был Excel от MS Office, но сейчас все больше специалистов переходят на «Google Таблицы». И их можно понять:
«Google Таблицы» — очень многогранный и функциональный инструмент с кучей возможностей и сценариев использования. По одним только формулам можно написать отдельную книгу. Чтобы не превращать материал в монструозный лонгрид, который никто не дочитает и до середины, я не буду рассматривать продвинутые возможности, вроде работы со скриптами, вдаваться в мелкие подробности и делать объемные инструкции по каждой функции.
Этот гайд покажет, что вообще можно делать в «Google Таблицах», поможет найти способы применения в своей работе и понять, в каком направлении копать, чтобы научиться работать в программе эффективно.
Начало работы с Google Sheets
Создавать новые и открывать созданные ранее таблицы можно на главной странице сервиса и через «Google Диск».
На главной Google Sheets отображаются все таблицы, которые вы когда-либо открывали. По умолчанию они отсортированы по дате просмотра. Чтобы открыть существующую таблицу, кликните по ней один раз. Новый файл можно создать, кликнув плюс в правом нижнем углу.
С «Google Диска» таблицы открываются по двойному клику. Чтобы создать новый файл, нажмите «Создать» в левом верхнем углу или вызовите контекстное меню правым кликом. Тут также можно начать работу с пустой таблицей или выбрать один из шаблонов Google.
Сохранять таблицы не надо — все автоматически сохраняется на «Google Диске» в процессе работы. Закончив, можно просто закрыть файл — данные не потеряются. По умолчанию созданные через сервис Google Sheets файлы сохраняются в корне Диска. Чтобы переместить его в папку, кликните по иконке рядом с названием таблицы, и выберите место назначения. Прежде чем перемещать только что созданную таблицу, ее надо переименовать.
Переносить файлы можно и на «Google Диске» — с помощью drag&drop или кликнув по нему правой кнопкой и выбрав «Переместить в. ».
Как открыть файл Excel в «Google Таблицах»
Google Sheets поддерживает таблицы Excel. Открыть такой файл в сервисе можно двумя способами:
Это работает и в обратную сторону — файлы Google Sheets скачиваются на компьютер в форматах, который поддерживает Excel: XLSX и CSV. Для этого зайдите в меню «Файл» → «Скачать как» и выберите формат. Также можно сохранить таблицу в формате ODT, чтобы открыть в программе Open Office.
Сложные таблицы со множеством формул, графиков и диаграмм при переносе в другой формат могут открываться некорректно. Загрузив файл в Google Sheets или скачав в Excel, проверьте, чтобы все отображалось и работало правильно.
Панель инструментов Google Sheets
В сервисе Google этот элементы выглядит проще, чем в Excel. Однако, если вы привыкли работать с программой MS Office, понадобиться время, чтобы привыкнуть.
Кратко пройдемся по инструментам на панели. О некоторых из них мы поговорим подробнее позже, в соответствующих разделах.
Как и в Excel, панель инструментов в «Google Таблицах» поделена на блоки:
В нижней части находится панель для переключения между листами и операций с ними. Чтобы создать новую страницу в текущем файле, кликните «+» в левом нижнем углу. По клику на стрелку рядом с заголовком листа откроется меню, где можно переименовать, удалить, переместить, скопировать и скрыть лист, а также защитить его от редактирования и изменить цвет ярлыка.
Ярлык скрытого листа не будет отображаться на общей панели. Чтобы посмотреть и вернуть его, перейдите в меню «Вид» → «Скрытые листы» и найдите в списке нужный.
Операции с ячейками, строками и столбцами
Элементы таблицы — строки, столбцы и ячейки — можно добавлять, удалять и скрывать через меню «Изменить» или контекстное меню, которое открывается правым кликом по выделенному элементу. Для некоторых действий нужно выбрать, что произойдет с окружающими элементами после удаления или вставки — куда сдвинутся данные, строки и столбцы.
Как закрепить строки
При работе с большими массивами данных полезно всегда держать перед глазами заголовки строк и столбцов. Для этого их можно закрепить. Установите курсор в ячейку нужной строки или столбца, откройте меню «Вид» → «Закрепить» и выберите один из параметров.
Как перемещать элементы таблицы
Строки, столбцы и ячейки в «Google Таблицах» можно менять местами и двигать по всему листу.
Чтобы переместить строку или столбец, выделите их кликом по заголовку, откройте меню «Изменить» и выберите «Переместить строку / столбец» влево, вправо, вверх или вниз.
Кроме того, строки, столбцы и ячейки можно перемещать простым перетаскиванием по принципу drag&drop. Для этого наведите курсор на край выделенного элементам так, чтобы он принял вид «руки», захватите элемент левым кликом и перетащите в нужное место, не отпуская кнопку мыши.
История изменений в «Google Таблицах»
В Google Sheets автоматически сохраняется не только текущая версия, но и вся история изменений. Чтобы открыть ее, кликните на строчку «Все изменения сохранены на Диске» справа от Главного меню.
В режиме истории редактировать таблицу нельзя, зато справа можно выбрать, просмотреть и, при желании, восстановить любую из предыдущих версий.
Бывает, что восстанавливать предыдущую версию нельзя — это не ваша таблица и не вы ее редактировали. Если вы нажмете «Восстановить», все изменения, которые внес другой человек после определенного момента, удалятся. Работать с прошлой версией в режиме истории тоже затруднительно — невозможно даже нормально выделить и скопировать данные, не то что воспользоваться фильтрами и формулами. На этот случай в Google Sheets есть возможность скопировать более раннюю версию.
По клику программа создаст копию таблицы в том виде, в котором вам нужно.
Совместный доступ
Одно из неоспоримых преимуществ «Google Таблиц» перед Excel — возможность совместной работы. Чтобы открыть свой файл коллегам, кликните синюю кнопку «Настройки доступа» в правом верхнем углу и задайте настройки.
Вы можете дать доступ к файлу отдельным пользователям, тем, у кого есть ссылка, или всему интернету. Также есть разные уровни: «Редактирование», «Комментирование», «Просмотр».
Лайфхак: если в таблицах вы ссылаетесь на чужие сайты и не хотите, чтобы содержимое файла увидели посторонние, не открывайте доступ по ссылке. Владельцы сайтов, на которые вы ссылаетесь, могут получить ссылку на файл из отчетов по источникам переходов, и открыть его.
Как удалить и восстановить таблицу
Чтобы удалить «Google Таблицу», откройте меню «Файл» и выберите пункт «Удалить».
Удалить таблицы в интерфейсе «Google Диска» и на главной странице сервиса Google Sheets можно, не открывая их. В первом случае выделите ненужный файл и кликните иконку корзины на верхней панели, во втором — откройте меню файла и найдите пункт «Удалить».
Работа с данными в «Google Таблицах»
Как редактировать Google Sheets
Данные в таблицу можно вводить с клавиатуры, добавлять через «копировать» → «вставить» и импортировать из разных источников.
Чтобы внести данные в таблицу, просто установите курсор на ячейку и начинайте вводить с клавиатуры. Перемещаться по листу можно с помощью мыши, стрелок и горячих клавиш — о них подробнее в конце статьи. Чтобы отредактировать данные в ячейке, кликните по ней два раза, нажмите Enter или поставьте курсор в нужное место в строке формул.
Как защитить данные от редактирования
Вы можете запретить коллегам редактировать данные во всей таблице, на отдельных листах, диапазонах и даже ячейках. Запрет редактирования всей таблицы настраивается через уровни доступа, а для ячеек и диапазонов есть отдельный инструмент.
Вызовите контекстное меню правым кликом и выберите пункт «Защитить диапазон» или откройте меню «Данные» и кликните «Защищенные листы и диапазоны». В открывшейся справа форме кликните «Добавить лист или диапазон». Введите описание. Если хотите закрыть от редактирования диапазон, кликните иконку таблицы на соответствующей вкладке и выделите нужные ячейки на листе. Чтобы защитить целый лист, перейдите на другую вкладку и выберите его в списке. Нажмите «Задать разрешения» и настройте доступ.
Вы можете прописать ограниченный список пользователей, которые имеют право редактировать указанные листы и ячейки, или ограничиться предупреждением. В последнем случае при попытке отредактировать защищенный диапазон программа будет выводить на экран предостерегающее сообщение.
Комментарии и примечания
В «Google Таблицах» можно пометить ячейки комментариями и примечаниями. Ячейки с примечаниями выделяются черным треугольником в правом верхнем углу, с комментарием — желтым. Основное отличие в том, что на комментарий могут ответить другие пользователи, а на примечание — нет.
Комментарии можно закрыть, кликнув «Вопрос решен», примечание — только удалить через меню «Изменить», или вызвав контекстное меню правым кликом.
Чтобы добавить примечание, выделите нужную ячейку, кликните правой кнопкой и найдите пункт «Вставить примечание». Там же находится функция «Вставить комментарий», но через иконку на панели инструментов будет быстрее.
Форматы данных
Данные — текст и цифры, которые вы вносите в таблицу, могут отображаться в разных форматах. Вариантов представления данных несколько десятков, но все их можно свести к 7 основным:
Все остальные форматы — производные от основных. Валюту можно отобразить с десятыми или округлить до целых, заменить «р.» перед цифрами на другую валюту. Дата отображается в виде «2.12.2018» или «воскресенье, 2 декабря 2018 г.» — и еще в десяти разных вариациях. Тоже самое с другими форматами.
Чтобы изменить формат данных в ячейке, строке, столбце или диапазоне, выделите их, откройте меню «Формат» → «Числа» и выберите нужный.
В пункт «Числа» вынесены часто используемые, остальные спрятались в пункте «Другие форматы».
Условное форматирование данных
В «Google Таблицах» можно изменить внешний вид текста и ячеек — шрифт, кегль, начертание, цвет шрифта и заливки, выравнивание, цвет и стиль границ, правила переноса текста. Все необходимые инструменты находятся на панели над рабочей областью, о которой мы говорили выше.
Иногда нужно, чтобы форматирование ячеек менялось автоматически в зависимости от того, какие данные в них находятся. Для этого в «Google Таблицах» используются правила условного форматирования. Их можно задать для всей таблицы, отдельного диапазона, строки, столбца и даже одной ячейки.
Чтобы настроить правила, перейдите в меню «Формат» → «Условное форматирование». Справа появится диалоговое окно. По клику на «Добавить правило» откроются настройки. В зависимости от задачи вы можете настроить правило, по которому ячейки будут перекрашиваться в один цвет, или создать градиент. Правило для форматирования ячеек одним цветом настраивается в 3 шага:
Всего в Google Sheets 18 условий форматирования для текстовых данных, чисел и дат. Так, можно выделить ячейки, которые содержат любые данные, определенные слова, числа меньше заданного значения и т. д.
Для создания более сложных правил нужно выбрать условие «Ваша формула» и использовать функции. Эта тема тянет на отдельную большую статью, поэтому здесь останавливаться не будем. Лучше рассмотрим на примере использование стандартных условий.
Вы ведете контекстную рекламы и выгружаете в таблицы показатели для анализа эффективности. Чтобы вычислить малоэффективные объявления, нужно отыскать строки с CTR менее 1 %. Создайте правило для соответствующего столбца, по которому ячейки со значением меньше единицы будут подсвечиваться цветом.
Градиент пригодится, когда нужно, например, визуализировать отклонение данных в большую и меньшую сторону от оптимального значения.
Фильтры и сортировка
Инструменты для сортировки, фильтрации, проверки и группировки содержимого таблицы находятся в пункте «Данные» главного меню.
Сортировка. Вверху выпадающего списка находятся кнопки для быстрой сортировки листа в прямом или обратном алфавитном порядке по столбцу с выделенной ячейкой. Числовые значения отсортируются от меньшего к большему и наоборот соответственно.
Если в таблице есть строка заголовков, при быстрой сортировке они тоже сместятся. Чтобы сохранить их на месте, выберите пункт «Сортировка диапазона» в меню «Данные». Он станет активным, когда вы выберете нужный диапазон. По клику откроется диалоговое окно с выбором столбца и порядка сортировки — прямой или обратный. Чтобы сохранить строку заголовков на месте, поставьте галочку рядом с «Данные со строкой заголовка».
Фильтры скрывают из таблицы данные, которые сейчас не нужны. Это удобно, когда работаешь с большими массивами — посмотреть публикации по отдельной площадке или типу контента в объемном контент-плане, проанализировать данные по достижению одной цели в аналитическом отчете.
В меню «Данные» есть два инструмента для фильтрации содержимого таблицы: «Создать фильтр» и «Фильтры. » Если один пользователь отфильтрует содержимое таблицы с помощью первого инструмента, у других они тоже отсортируются. Поэтому если вам нужно скрыть часть данных только для себя, чтобы не мешать коллегам, выберите «Данные» → «Фильтры. » → «Создать новый фильтр».
По клику включится режим фильтрации — все настройки, которые вы сделаете, сохранятся. Режимов фильтрации можно создать несколько, все они появятся в выпадающем меню «Фильтры. ». Чтобы в них проще было ориентироваться, присвойте каждому понятное название. По умолчанию присваиваются имена «Фильтр 1», «Фильтр 2» и т. д.
Лайфхак: Если нужно показать другому пользователю данные в том виде, в котором вы их отфильтровали с помощью режима фильтрации, просто включите его и отправьте скопированную ссылку.
В таблицах с режимом доступа «Только просмотр» функция «Создать фильтр» неактивна. Чтобы отобразить данные так, как вам нужно, выберите «Данные» → «Фильтры. » → «Создать новый временный фильтр».
Проверка данных
Инструмент проверяет, соответствуют ли данные в ячейке заданным параметрам. Например, в одном столбце должны быть только даты. Чтобы проверить, нет ли там ошибок, выберите в меню «Данные» пункт «Проверка данных» и задайте настройки:
В правом верхнем углу ячеек с неверными данными появятся красные метки, при наведении на которые появляется окно с пояснениями.
Если данные в проверяемом диапазоне часто редактируются, вы можете предотвратить ошибки на будущее. Выберите в разделе «Для неверных данных» пункт «запрещать ввод данных», и когда кто-то попытается ввести в столбец что-то кроме допустимой даты, система выведет предупреждение и не даст отредактировать ячейку. По умолчанию сервис показывает стандартный текст предупреждения, но вы можете задать свой. Для этого поставьте в настройках галочку рядом с «Показывать текст справки для проверки данных» и введите свой вариант в поле.
Помимо соответствия данных формату даты можно задать десятки разных правил для числовых, текстовых и других данных. Например, вы можете убедиться, что числа в диапазоне меньше, больше либо равны определенному значению. Как и в случае с условным форматированием, функция проверки данных помимо предусмотренных системой правил поддерживает ввод своих формул.
Сводные таблицы
Сводные таблицы — это популярный инструмент анализа данных. Пригодится, когда нужно структурировать и представить в наглядном виде большие массивы информации, чтобы было удобнее делать выводы.
Приведу пример. Вы ведете сквозную аналитику для салона красоты — скрупулезно фиксируете все данные по заказам вплоть до источника рекламы, с которого пришел лид. За год получается длинная таблица на тысячи строк. Задача: выяснить, какой канал принес больше всего прибыли за прошлый год, чтобы грамотно перераспределить рекламный бюджет. С помощью сводных таблиц мы получим простой и наглядный отчет буквально в пару кликов.
Чтобы создать такой отчет, выделите исходную таблицу, зайдите в меню «Данные» и выберите пункт «Сводная таблица». Программа создаст новый пустой лист и откроет редактор справа. В разделе строки кликните «Добавить» и выберите столбец с рекламными каналами. В разделе «Значения» добавьте столбец с данными о прибыли по каждому заказу. Программа просуммирует прибыль по каждому каналу и сведет в таблицу. Если в разделе «Столбцы» выбрать параметр «Услуги», таблица покажет, какую прибыль принес каждый канал по каждой услуге. Также можно фильтровать данные по любому столбцу из исходной таблицы, например, скрыть из сводной таблицы данные по отдельной услуге. Для этого в раздел «Фильтры» нужно добавить столбец «Услуги», открыть список и снять галочки с тех услуг, данные по которым не хотим учитывать.
Диаграммы и графики в «Google Таблицах»
Для визуализации данных существуют более функциональные и удобные инструменты — Google Data Studio, Power BI и другие. Однако иногда бывает полезно добавить диаграмму или график прямо в таблицу, чтобы наглядно представить данные.
Рассмотрим процесс создания диаграмм и настройки этого инструмента на примере сводной таблицы, которую мы сделали в предыдущем пункте. Переходим на нужную вкладку, заходим меню «Вставка» и выбираем пункт «Диаграмма». По умолчанию программа создает вот такой график. Справа вместо «Редактора сводной таблицы» откроется «Редактор диаграмм».
В визуальное представление попала строка «Итого» — сумма прибыли по всем каналам. Поскольку ее значение в несколько раз больше, чем по каждому каналу в отдельности, остальные столбцы диаграммы на фоне выглядят слишком мелко — анализировать и сравнивать неудобно. Поэтому для начала уберем эту строку с графика, отредактировав диапазон — кликаем по пиктограмме таблицы в строке «Диапазон данных» «Редактора диаграмм» и выбираем нужный.
Посмотрим, что еще мы можем сделать с диаграммой:
Основные манипуляции с диаграммой собраны на вкладке «Данные». На вкладке «Дополнительные» можно поменять внешний вид графика:
Настроек много, но все они интуитивно понятны.
Созданные в Google Sheets диаграммы можно сохранить как изображение и опубликовать на сайте, встроив в страницу код. Для этого откройте выпадающее меню в правом верхнем углу диаграммы и выберите соответствующие пункты.
Работа с функциями
В «Google Таблицах» также, как и в Excel, есть функции — формулы, которые обрабатывают данные в таблице — суммируют, сравнивают, проверяют на соответствие условиям и т. д.
Все функции вводятся по одному принципу:
Данные для вычислений можно ввести вручную с клавиатуры или указать ссылки на ячейки и диапазоны, из которых их нужно взять. Также можно вводить названия именованных диапазонов, но предварительно их нужно создать. Это бывает полезно, когда часто используете в вычислениях одни и те же диапазоны данных. Чтобы создать именованный диапазон, зайдите в меню «Данные» или вызовите контекстное меню правым кликом и выберите соответствующий пункт. Справа откроется редактор именованных диапазонов. Кликните «+Добавить диапазон» и введите название. Если вы не выделили нужные ячейке раньше, нажмите на иконку таблицы и сделайте это, затем кликните «Готово».
Рассмотрим на примере простой функции, которую часто используют SEO-шники и специалисты по контекстной рекламе — ДЛСТР — вычисляет длину строки.
Подробно описывать каждую функцию не будем — их около 400. Скажем лишь, что в с помощью формул здесь можно сделать все тоже самое, что и в Excel. Конечно, там есть формулы, которых нет в сервисе Google, и наоборот, но таких не много. Например, функции GOOGLETRANSLATE, которая переводит текст с одного языка на другой, в Excel нет.
О полезных для интернет-маркетологов функций электронных таблиц MS Office мы уже писали, все они отлично работают в сервисе Google. Принцип работы этих функций тот же, но может отличаться синтаксис. Список всех формул Google Sheets с описанием и синтаксисом есть в справочнике.
Интеграция с другими инструментами Google
Неоспоримое преимущество Google Таблиц для интернет-маркетологов в том, что они могут взаимодействовать и обмениваться данными с другими сервисами Google. Посмотрим на примере двух продуктов — Google Формы и Google Analytics.
Взаимодействие с Google Forms
Обмен данными между «Формами» и «Таблицами» даже не нужно дополнительно настраивать — эта возможность есть в сервисах по умолчанию.
Чтобы создать новую «Google Форму» из интерфейса «Таблиц», зайдите в меню «Инструменты» → «Создать форму». По клику в новой вкладке откроется редактор Google Forms. Создадим простенькую форму с тремя вопросами со свободным ответом и вернемся в таблицу. Там появился новый лист «Ответы на форму (1)», в котором уже созданы 4 столбца. Три из них соответствуют вопросам формы — «ФИО», «Телефон» и «Адрес», четвертый — «Отметка времени» — в нее система внесет дату и время заполнения формы. Первая строка закреплена, чтобы при просмотре большого количества ответов заголовки столбцов всегда были на виду. Все, больше ничего настраивать не нужно — при заполнении формы, ответы сохранятся в таблице автоматически.
Если вы не создали таблицу для ответов заранее, все равно сможете выгрузить их в «Таблицу». Для этого в режиме редактирования формы перейдите на вкладку «Ответы» и кликните пиктограмму Google Sheets в правом верхнем углу.
По клику создается новая таблица с таким же названием, как и у формы.
У этой простой возможности массы способов использования в интернет-маркетинге. Информацию, которую вы собираете через формы, будь то ответы соискателей на вакансию, клиента по проекту или целевой аудитории по продукту, удобно просматривать в «Таблицах».
Интеграция с Google Analytics
Обмен данными с Google Analytics в Таблицах реализован через дополнение. Чтобы его подключить, откройте меню «Дополнения», выберите пункт «Установить дополнения» и найдите в открывшемся окне Google Analytics. Если не увидите его на первом экране, быстрее будет через поиск, потому что аддонов немало.
Наведите мышку на дополнение GA и кликните на появившуюся кнопку «Бесплатно+».
Во всплывающем окне выбираем аккаунт Google, у которого есть доступ к нужным проектам в «Аналитиксе», и подтверждаем разрешение на доступ. После этого в меню «Дополнения» появится пункт “Google Analytics”.
Попробуем выгрузить данные из GA в таблицу. Заходим в меню «Дополнения» → “Google Analytics” → “Create new report”. Справа откроется окно редактора отчетов. Заполняем название отчета (1), выбираем аккаунт (2), ресурс (3) и представление (4), затем — метрики (5) и параметры (6), которые хотим отобразить в отчете. Допустим, нам надо выгрузить в таблицу данные о посещениях по страницам и источникам трафика. Вводим метрики, параметры и кликаем “Create report”.
По клику в таблице автоматически создается новый лист “Report Configuration” с параметрами отчета. Чтобы создать сам отчет, еще раз заходим в «Дополнения» → “Google Analytics” и кликаем “Run reports”. Программа создаст новый лист и выгрузит запрашиваемые данные.
С полученными данными можно работать — сортировать, фильтровать, обрабатывать с помощью формул и отображать в сводных таблицах.
Следующий уровень мастерства — автоматизированные отчеты. Вы можете сначала создать отчет в «Таблицах» — настроить колонки и строки, прописать формулы в зависимости от конкретной задачи, затем настроить автоматическую выгрузку данных. Этот процесс можно отнести к продвинутым возможностям таблиц, поэтому здесь мы не будем подробно его описывать.
Полезные дополнения Google Sheets
Помимо интеграции с Google Analytics, у таблиц есть и другие полезные дополнения. О некоторых из них мы уже писали в статье «30 плагинов для Google Документов, Таблиц и Презентаций, которые ускорят вашу работу». Также рекомендуем обратить внимание еще на несколько аддонов:
Все аддоны устанавливаются одинаково: меню «Дополнения» → «Установить дополнения».
Сочетания и горячие клавиши в «Google Таблицах»
Если выучить и довести до автоматизма использование хотя бы трети горячих клавиш, скорость работы в «Google Таблицах» можно увеличить раза в 1,5.
Всего сервис поддерживает более сотни горячих клавиш. Вот несколько самых полезных для Windows:
Также в Таблицах работают общие для Windows и Mac сочетания для стандартных действий — копировать, вставить, вырезать, печать, найти и т. д. Полный список горячих клавиш — в справке по «Таблицам».
Вот мы и рассмотрели ключевые возможности Google Sheets.
Активно пользуетесь Таблицами и знаете хитрости и лайфхаки, которые ускоряют и упрощают работу? Будет здорово, если вы поделитесь ими в комментариях и поможете другим работать эффективнее 😉
В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров