Подборка полезных формул для работы с Google Таблицами

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

Как вводить формулы в Google Таблице

Для начала создайте новую таблицу. В сервисе есть уже готовые шаблоны на разные случаи:

https://www.google.ru/intl/ru/sheets/about/

Выберите подходящий вариант или действуйте следующим образом: 

1. Перейдите на страницу sheets.google.com.
2. Кликните на большой цветной значок «+» с подписью «Blank» и дождитесь загрузки пустой таблицы.

Либо просто перейдите по этой ссылке: sheets.google.com/create.

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

Теперь введите формулу. Для этого кликните на нужную ячейку и добавьте в нее знак «=», а также название функции. Функцией называют готовое действие, выраженное, как правило, одним словом. Полный список функций вы найдете здесь. Для того, чтобы вставить в ячейку готовую функцию, кликните «Вставка» → «Функции» и выберите из списка нужное действие.

Помимо функций, формула включает в себя значения ячеек, например, D7.

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

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

Маркетинг

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

Видео выступлений на конференции CALLDAY 2015

Видео выступлений на конференции CALLDAY 2015

Список полезных формул

Разберем самые востребованные формулы таблицы Google.

Сложение, вычитание, умножение, деление

«СУММ» или «SUM» — команда для простой операции сложения. Функция суммирует значение выбранных ячеек:

В ячейке F1 расчет суммы ячеек A1, B1, C1, D1, E1. 

Остальные функции записывают так:

• =MULTIPLY — умножение;
• =MINUS — вычитание;
• =DIVIDE — деление. 

Иногда формулы для сложения, вычитания, умножения и деления удобнее записывать без названия функции, достаточно знаков: «+», «-», «*», «/» — например, =G14*K14.

Прогрессия

Прогрессия или «SEQUENCE» понадобится для нумерации столбцов или строк. Выглядит как номер предыдущей ячейки + 1 — например, =R20+1. 

Напоминаем, что если нужно учесть не диапазон ячеек, а только конкретные, укажите их через «;». 

Округление

Используйте функцию «ОКРУГЛ» или «ROUND», чтобы округлять числа в ячейках.

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

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

Сумма, если ячейки идут не последовательно

Если вам нужно суммировать не все значения подряд, укажите конкретные ячейки, разделяя их символом «;». Чтобы сложить числа в ячейках, которые находятся на разных листах, запишите формулу таким образом: =СУММ(‘Лист2’!A4:A8).

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

Среднее арифметическое

Функция делит сумму чисел в диапазоне ячеек на их количество. Пример формулы: =AVERAGE(A1:F1). 

Склеивание текстовых и числовых значений

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

Вид формулы: =CONCATENATE(A2;B2;C2). Пример:

Второй способ склеивания — ввод функции вручную: =A2&” “&B2&” “&C2&. 

Перенос данных из листов одного файла

Вы можете переносить данные внутри одного файла с помощью формулы вида: =Data!A15 (Data — название листа). То есть введите «=», название листа, восклицательный знак и номер ячейки для копирования. 

Если в названии листа есть пробел или специальные символы, заключите его в одиночные кавычки: =‘____’!__.

Формула массива

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

Пример: =ARRAYFORMULA(A2:C2+A6:C6). 

Сумма ячеек с условием ЕСЛИ

Условие «СУММЕСЛИ» выражается функцией «SUMIF». Формула выглядит так: = SUMIF(‘Лист’!диапазон;критерии;‘Лист’!суммарный диапазон). 

Если условие описано текстом, заключите его в кавычки. Чтобы включить в формулу несколько условий, используйте функцию «БДСУММ».

Подсчет символов

Пригодится, если нужно узнать объем текста или длину строки. Функция «ДЛСТР» или «LEN» подсчитывает количество символов вместе с пробелами. 

После названия функции введите номер ячейки или сам текст.

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

Перевод текста из верхнего регистра в нижний

Например, формула «СТРОЧН(D3)» переведет все буквы из ячейки D3 в нижний регистр. Латиницей функцию пишут так — «LOWER».

Функция «ПРОПИСН» делает все буквы в выбранной ячейке заглавными. «ПРОПНАЧ» переводит первые буквы слов в заглавные. 

Импорт данных из других источников

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

• =IMPORTRANGE(“https://docs.google.com/document/d/1FeVVq”, “лист3!K2:N5”).
• =IMPORTRANGE(URL таблицы, диапазон).

Есть несколько похожих функций:

• IMPORTXML для импорта информации в форматах XML, HTML, CSV, TSV, RSS и ATOM XML.
• IMPORTHTML для импорта данных из таблиц и списков со страницы сайта.
• IMPORTFEED для импорта фида в RSS или Atom.
• IMPORTDATA для импорта данных в формате CSV или TSV.

Перевод текста

Используйте формулу вида GOOGLETRANSLATE(“Hello World”; “en”; “es”), то есть GOOGLETRANSLATE(текст; [язык оригинала; язык перевода]).

Функция «DETECTLANGUAGE» поможет определить язык текста в ячейках. Запишите в формулу номера ячеек через «:» или добавьте конкретные слова — например, так: 

=DETECTLANGUAGE(“Window”). 

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

Объединение ячеек

Формула вида =СЦЕПИТЬ(строка1, [строка2, …]) объединяет ячейки. Если вы укажете диапазон со множеством ячеек по горизонтали и вертикали, то числа из ячеек объединятся по строкам. Формулы =СЦЕПИТЬ(B2:C7) и =СЦЕПИТЬ(B2,C2,B3,C3, … , B7,C7) работают одинаково. 

Разделение фраз

Выводите текст в разные ячейки по нужным вам критериям. Для разделения используйте функцию «SPLIT» или «СЦЕПИТЬ». Формула выглядит следующим образом: 

=SPLIT(текст; разделитель; [тип разделителя]; [удаление пустых ячеек])

Последний параметр — необязательный. В качестве разделителя вы можете использовать любой символ. 

Удаление лишних пробелов

Функция «СЖПРОБЕЛЫ» или «TRIM» удаляет все пробелы, кроме одиночных. Формула выглядит просто: 

=СЖПРОБЕЛЫ(номер ячейки) или =СЖПРОБЕЛЫ(“текст”). 

Работа с графиками и диаграммами

Выделите диапазон ячеек с данными для построения диаграммы или графика. Затем кликните на «Вставку» и выберите нужное действие.

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

Работа с изображениями

Добавляйте картинки в форматах PNG, JPEG или GIF и размером до 50 Мб. Учитывайте, что после вставки изображения текст в ячейку добавить невозможно.

Как добавить изображение:

1. Выберите ячейку для вставки.
2. Кликните «Вставка» → «Изображение». 
3. Выберите способ вставки. 
4. Выберите изображение.
5. Кликните «Открыть».

Либо используйте формулу: =IMAGE(“URL”, 1, [высота в пикселях], [ширина в пикселях]). 

Ошибки ввода

В Google Таблицах каждый пользователь сталкивается с ошибками ввода. Какие-то из них будут очевидными — например, деление на ноль (#DIV/0!). Основная часть ошибок — операционные или технические. Рассмотрим несколько наиболее распространенных:

• #N/A. Возникает, когда невозможно отработать или найти значение в указанном диапазоне.
• #REF! — попытка работы с несуществующей (удаленной) ячейкой. 
• #NAME? — неверно введено название функции. 
• #VALUE! — работа с несовместимыми типами данных. Например, когда пользователь попытался совершить математическое действие с текстом и числами. 
• #NUM! — ввод недопустимого числового значения. Например, денежное значение $2,000 будет считано некорректно, поскольку знак $ — это индикатор абсолютной ссылки, а запятая — разделитель аргументов. 

Коротко о главном

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

Эффективный маркетинг с Calltouch

• Анализируйте весь маркетинг и продажи в одном окне
• Удобные дашборды и воронки от показов рекламы до ROI

Узнать подробнее