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

  • Tutorial
Cтатья написана в соавторстве с Ренатом Шагабутдиновым.

image

В этой статье речь пойдет о нескольких очень полезных функциях Google Таблиц, которых нет в Excel (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

Очень много букв, но есть разборы интересных кейсов, все примеры, кстати, можно рассмотреть поближе в Google Документе goo.gl/cOQAd9 (файл-> создать копию, чтобы скопировать файл себе на Google Диск и иметь возможность редактирования).

Оглавление:

— Если результат формулы несколько ячеек
— Объединение нескольких диапазонов данных для использования в формулах
— SORT
— Как в SORT добавить заголовки таблицы?
— FILTER
— FILTER, два условия и работа с датой
— Интерактивный график при помощи FILTER и SPARKLINE
— IMPORTRANGE
— Импорт форматирования из исходной таблицы
— IMPORTRANGE как аргумент другой функции
— IMAGE: добавляем изображения в ячейки
— GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках


Если результат формулы занимает больше одной ячейки

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


SORT


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

Синтаксис функции:
=SORT(сортируемые данные; столбец_для_сортировки; по_возрастанию; [столбец_для_сортировки_2, по_возрастанию_2; ...])

Пример на скриншоте ниже, мы ввели формулу только в ячейку D2 и сортируем данные по первому столбцу (вместо ИСТИНА/ЛОЖЬ можно вводить TRUE/FALSE).
(здесь и далее — примеры для российских региональных настроек таблицы, рег. настройки меняются в меню файл → настройки таблицы)

image

Как в SORT добавить заголовки таблицы?

С помощью фигурных скобок {} создаем массив из двух элементов, шапки таблицы A1:B1 и функции SORT, элементы отделяем друг-от-друга с помощью точки с запятой.

image

Как объединить несколько диапазонов данных и отсортировать (и не только)?

Давайте рассмотрим, как можно объединять диапазоны для использования в функциях. Это касается не только SORT, этим приемом можно пользоваться в любых функциях, где это возможно, например в ВПР или ПОИСКПОЗ.

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

image

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

image

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

image

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

image

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

Все разобранные примеры можно рассмотреть поближе в
Google Документе.

FILTER


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

Синтаксис функции:
FILTER(диапазон; условие_1; [условие_2; ...])

Одно условие

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

Введем в ячейку E3 вот такую формулу:
=FILTER(A3:C7;B3:B7=«Наталья Чистякова»)

Обратите внимание, синтаксис немного отличается от привычных формул, вроде СУММЕСЛИН, там диапазон условия и само условие отделялось бы при помощи точки с запятой.

image

Введенная в одну ячейку формула возвращает нам массив из 9-ти ячеек с данными, но после примеров с функцией SORT мы этому уже не удивляемся.

Помимо знака равенства (=) в условиях можно использовать еще >, >=, <> (не равно), <, <=. Для текстовых условий подходят только = и <>, а для чисел или дат можно использовать все эти знаки.

Два условия и работа с датой

Давайте усложним формулу и добавим в нее еще одно условие, по дате продаж, оставим все продажи начиная с 01.02.17

Так будет выглядеть формула, если вводить аргументы условия сразу в нее, обратите внимание на конвертацию текстовой записи даты при помощи ДАТАЗНАЧ:
=FILTER(A3:C7;B3:B7=«Наталья Чистякова»;A3:A7>=ДАТАЗНАЧ(«01.02.17»))

Или вот так, если ссылаться на ячейки с аргументами:
=FILTER(A3:C7;B3:B7=I6;A3:A7>=J6)

image

Интерактивный график при помощи FILTER и SPARKLINE

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

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

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

image

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

Ячейка с выпадающим список станет условием для формулы FILTER, напишем ее.
=FILTER(C3:C7;B3:B7=E2)

И вставим эту формулу в функцию SPARKLINE, которая на основе полученных данных будет рисовать в ячейке график.
=sparkline(FILTER(C3:C7;B3:B7=E2))

image

Так это выглядит в динамике:

gif

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



IMPORTRANGE


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

В каких случаях она может пригодиться?

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

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

Синтаксис формулы следующий:
IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(ключ; диапазон)

spreadsheet_key (ключ) — последовательность символов атрибута «key=» (ключа) в ссылке на таблицу (после «spreadsheets/…/»).

Пример формулы с ключом:
=IMPORTRANGE(«abcd123abcd123»; «sheet1!A1:C10»)

Вместо ключа таблицы вы можете использовать полную ссылку на документ:
=IMPORTRANGE(«docs.google.com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc»; «Лист1!A1:CM500»)

В вашем файле будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.



Если в исходном файле может меняться количество столбцов или строк, вводите во втором аргументе функции открытый диапазон (см. также подраздел «Диапазоны вида A2:A»), например:
Лист1!A1:CM (если будут добавляться строки)
Лист1!A1:1000 (если будут добавляться столбцы)

! Имейте в виду, что если вы загружаете открытый диапазон (например, A1:D), то вы не сможете вставить никакие данные вручную в столбцы A:D в файле, где находится формула IMPORTRANGE (то есть в конечном, куда загружаются данные). Они как бы “зарезервируются” под весь открытый диапазон — ведь его размерность неизвестна заранее.

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

Так, если в ячейку A1 вы введете ссылку на документ (без кавычек), из которого нужно загрузить данные, а в ячейку B1 — ссылку на лист и диапазон (тоже без кавычек), то импортировать данные можно будет с помощью следующей формулы:

=IMPORTRANGE(A1;B1)



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

Импорт форматирования из исходной таблицы


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

image

После нажатия кнопки Копировать в… выберите книгу, в которую будете импортировать данные. Обычно нужная таблица есть на вкладке Недавние (если вы действительно недавно работали с ней).

После копирования листа выделите все данные (нажав на левый верхний угол):

image

И нажмите Delete. Все данные исчезнут, а форматирование останется. Теперь можно ввести функцию IMPORTRANGE и получить полное соответствие исходного листа — как в части данных, так и в части формата:

image

IMPORTRANGE как аргумент другой функции

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

Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.

Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз).

image

Сначала импортируем этот диапазон:
IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»)

А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):
=СРЗНАЧ(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»))
=AVERAGE(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»))

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

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


Функция IMAGE позволяет добавлять в ячейки Google Таблиц изображения.

У функции следующий синтаксис:
IMAGE(URL, [mode], [height], [width])

URL – единственный обязательный аргумент. Это ссылка на изображение. Ссылку можно указать напрямую в формуле, взяв в кавычки:
=IMAGE(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

image

Или же поставить ссылку на ячейку, в которой ссылка хранится:
= IMAGE(B1)

image

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

image

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

image

Аргумент mode может принимать четыре значения (если его пропустить, по умолчанию будет первое):

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

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

image

Четвертый режим может быть удобен, если вам нужно подбирать точный размер изображения в пикселях, меняя параметры height (высота) и width (ширина). Картинка будет сразу обновляться.
Обратите внимание, что при всех режимах, кроме второго, могут оставаться незаполненные области в ячейке, и их можно залить цветом:

image

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


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



Синтаксис функции следующий:
GOOGLETRANSLATE (text,[source_language], [target_language])

text – это текст, который нужно переводить. Можно взять текст в кавычки и записать прямо в формулу, но удобнее сослаться на ячейку, в которой текст записан.
[source_language] – язык, с которого мы переводим;
[target_language] – язык, на который мы переводим.


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



А как быть, если мы хотим переводить на разные языки? И при этом не хотим каждый раз указывать язык исходника вручную?

Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент – текст, язык которого нужно определить:



Как и с любой другой функцией, прелесть здесь в автоматизации. Можно быстро поменять текст или язык; быстро перевести одну фразу на 10 языков и так далее. Конечно, мы понимаем, что это текст онлайн-переводчика – качество будет соответствующим.

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

Подробнее
Реклама
Комментарии 18
  • 0
    Продолжайте публикации далее, хитростей в гугл таблицах много.
    • НЛО прилетело и опубликовало эту надпись здесь
      • 0

        А кто-то создавал web-apps поверх гугл таблиц? Могли бы в кратце поделиться впечатлениями? Для себя буквально недавно открыли google apps script, и были удивленны, что раньше не обращали внимание на столь мощную фичу. Можно создавать полноценные http api, где таблицы играют роль DB. А учитывая, что поддерживаются cross-origin запросы, можно создавать разные single page приложения, где уже в свою очередь данные с таблиц можно хоть в каком виде анализировать, отображать, редактировать. И самое классное это то, что права доступа к таблицам и апи настраиваются раздельно.

        • +1
          Не столько web-apps, сколько скрипты для автоматизации ряда процессов.
          Главное, внимательно посмотрите лимиты, которые гугл выставляет для всех скриптов на apps script. Потому что я однажды начал не с этого и потратил лишнее время на написание скрипта, который априори отрезался бы гуглом как слишком долгий — из-за большого набора данных, которые нужно обработать и записать в таблицу.
          • 0
            Пара слов про то, как ускорить работу таблицы с помощью скрипта.

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

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

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

            Под спойлером пример такого скрипта.
            Заголовок спойлера
            function formulasbor()
            {
              var ss = SpreadsheetApp.getActiveSpreadsheet()
              var sh0 = ss.getSheetByName("sh0")
            
            //f - наша глобальная и тормозящая формула 
             var f = "=sum(a1+a2)"
              
              sh0.getRange("a2:r").clearContent()
              sh0.getRange("a2").setFormula(f)
              
              Utilities.sleep(15000)
              
              var last0 = (sh0.getLastRow() + 1)  
              var range = sh0.getRange("A2:R"+last0) 
              
              range.setValues(range.getValues())
              
            }
            
            

            • +1

              И я пришёл к подобному решению и кстати оно помогает не только с IMPORTRANGE, но и с IMPORTDATA.


              Третий год работает система, которая собирает с 8 файлов (16 разных ответственных за первичку, работают посменно) по 16к строк, потом эти данные суммируются и строится красивый график с 8 сериями за год, и это работает. Я был удивлён, когда увидел как гугл моментально отрисовывает такой наглядный график с тремя тысячами точек. И главное это всё бесплатно и было сделано мной за несколько часов, с учётом создания и раздачи аккаунтов и согласовыванием форм с контрагентами. Построить такую систему на 1С было бы куда дороже.

              • 0
                Я довольно долго работал с Excel (аналитиком в разных московских компаниях), но после того, как столкнулся с Google Табличками, стал ярым фанатом этого решения и возвращаюсь к Excel только если меня просят чем-то помочь.

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

                Кстати, по поводу скрипта — у меня есть еще одна его версия, где скрипт берет ряд формул из одной строки и циклом вставляет эти формулы построчно (каждая строка — посчитанные с помощью importrange показатели из разных док-тов) и заменяет на значения. Такое может помочь если у нас много формул и мы не хотим, чтобы они постоянно обновлялись.
                • 0

                  Я еще с 20 века работал с Exсel.
                  Пользовался очень удобным графическим интерфейсом.
                  Зная как должен проходить график расчётной функции просто "хватал" график в определенной точке и тянул вверх-вниз, а программа пересчитывала таблицу.
                  В 21-м веке функцию отрезали в Exell.
                  Может в Google табличках она сохранена?

                  • 0

                    Я тоже вспомнил такую возможность, кажется в Office 2003 ещё была.
                    Google недавно обновил интерфейс редактирования диаграмм, но этой функции так и не появилось


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


                    Жаль исходники пропали в недрах ЭКЦ МВД.

                • 0
                  Недавно бился с одной задачкой, но не смог решить, возможно у вас был опыт?

                  Не могу понять, как скриптом вставить сразу несколько формул в двумерный массив (разом, по-формуле-то можно)

                  Вот здесь пример:
          • 0

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


            1. Преобразует адрес в свободной форме в ячейке в координаты и ссылку на карту. Функция разбирает JSON.parse() получаемый UrlFetchApp.fetch() от геокодера яндекса. Используется в нашем неком подобии CRM на гугло таблицах.
            2. Проверяет наличие на гуглодиске файла договора DriveApp.searchFiles(), если нет файла оповещает по почте ответственного за реестр договоров, который хранится в таблице.
            3. Анализируем радиосвязи на КВ, получая данные в текстовом виде из программы wsprx. Используются почти все функции из статьи, а так же не указанные здесь очень полезные IMPORTDATA() и SPLIT(). Тут почти всё сделано средствами таблиц, скрипт только обновляет таблицу по триггеру и сообщает, если что-то идёт не так и прохождения долго нет MailApp.sendEmail().
            • 0

              А в какие нибудь квоты упирались? Или может знаете, как много на самом деле гугл дает бесплатно cpu и io?

              • +1

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

          • +1
            О! Функция GOOGLETRANSLATE спасла нас в двух ситуациях:

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

            2. Черновой перевод фраз приложения на другие языки. Где были сомнения позже привлекли переводчика.
            • 0
              большое спасибо за статью!
              надеюсь на продолжение
              • +1

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

                • 0
                  Кстати, сейчас ищу работу в Москве, направление — создание и оптимизация отчетности. Телеграмм в профиле (нет, ну а вдруг?)

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