Быстрый старт
Если вы уже уверенно пользуетесь Microsoft Excel, освоить Google Таблицы вам будет несложно. Но в Google есть особые функции, которых нет у конкурента — и именно они могут упростить и ускорить вашу работу. Давайте познакомимся с самыми интересными возможностями.
1. QUERY: Мгновенный анализ и обработка данных
Функция QUERY в Google Таблицах работает ничуть не хуже Power Query в Excel, но всё делается сразу в ячейке — без отдельных окон! Просто задайте нужные условия специальным "языком запросов".
QUERY: Синтаксис
Формула QUERY в Google Таблицах принимает три параметра:
где
QUERY: Примеры
Если вы не писали запросы на SQL, синтаксис может показаться сложным. Но после пары попыток вы удивитесь, как это удобно!
Допустим, у нас есть таблица сотрудников T_Staff. Нужно вывести только тех, кто прошёл обучение (то есть у кого в столбце D стоит "Y").
Просто введите формулу в нужную ячейку:
Обратите внимание: сам запрос пишется в двойных кавычках, а текстовые значения — в одинарных.
Теперь сложнее задача: нужны только те сотрудники, кто прошёл обучение И заработал больше 2 700 000 рублей (или $30 000 при курсе ≈ 90 руб).
Здесь D='Y' AND E>2700000 — то есть мы получаем имена только тех, у кого в D стоит "Y", а доход в E превышает 2 700 000.
Теперь — подсчёт: сколько сотрудников прошли обучение, а сколько — нет?
Воспользуйтесь формулой:
Здесь SELECT D выводит значения из столбца D, COUNT(D) считает их, а GROUP BY D группирует результаты по "Y" и "N".
А что если узнать, помогает ли обучение зарабатывать больше? Посчитаем средний доход для обеих групп.
В формуле SELECT D — группируем по обучению, AVG(E) — считаем средний доход, GROUP BY D — разбиваем по "Y"/"N".
Как видим, прошедшие обучение сотрудники заработали в среднем на 450 000–500 000 рублей больше.
Функция QUERY поддерживает агрегатные функции: avg(), count(), max(), min(), sum(). Возможности запроса включают: select, where, group by, pivot, order by, limit, offset, label, format, options.
2. IMPORTRANGE: Мгновенный перенос данных между файлами Google Таблиц
В Excel для импорта между таблицами часто используют Power Query, а Google Таблицы позволяют сделать это в один клик с помощью IMPORTRANGE.
IMPORTRANGE: Синтаксис
Синтаксис формулы состоит из двух аргументов:
Параметр a — ссылка на исходный файл (URL или ссылка на ячейку с адресом).
Параметр b — диапазон в этом файле: это может быть координата ячейки, имя листа, диапазон или таблица (всегда в кавычках), или ссылка на ячейку с диапазоном (без кавычек).
IMPORTRANGE: Примеры
Посмотрим на работу IMPORTRANGE на реальном примере.
Пусть нужно скопировать список имён из файла 1 в файл 2.
Выберите ячейку во втором файле и вставьте формулу:
Все ссылки и диапазоны указывайте обязательно в кавычках.
В примерах для простоты показана только часть адреса — в работе вставляйте полный URL.
Если подключаете файлы впервые, можете увидеть ошибку #REF!. Кликните на ячейку с формулой.
При наличии доступа Google Таблицы попросят разрешить импорт — просто подтвердите, нажав «Разрешить доступ».
Если доступа к источнику нет, появится сообщение об ошибке. В этом случае перейдите по ссылке, откройте исходный файл и запросите права на редактирование.
Сразу после получения доступа данные появятся во втором файле.
Плюс в том, что если данные в первом файле меняются, значения во втором обновятся сами. Однако если диапазон "уехал" или расширился, формулу нужно будет скорректировать вручную.
По умолчанию данные подтягиваются с первого листа. Чтобы вывести что-то с другого листа — добавьте его имя перед диапазоном через "!", например: "Лист2!A2:A11".
Если формула получилась слишком длинной, можно подставлять адрес файла из отдельной ячейки:
То же работает для диапазонов: поместите нужный диапазон в отдельную ячейку (например, "A2:A11"), и подставьте её во втором параметре.
Если нужный диапазон в исходнике — это именованный диапазон или таблица, просто впишите его название (или имя таблицы) в кавычках.
3. GOOGLETRANSLATE: Молниеносный перевод текста прямо в таблице
Как и следует из названия, функция GOOGLETRANSLATE моментально переводит текст из ячейки на любой нужный язык. Аналогичная функция есть и у Excel — это TRANSLATE.
GOOGLETRANSLATE: Синтаксис
Структура формулы выглядит так:
где
Список кодов языков ищите на сайте Google Developers.
Параметр b (язык-источник) можно не указывать — определится сам. Если не задать c, перевод будет производиться на язык, установленный в вашей системе по умолчанию.
GOOGLETRANSLATE: Примеры
Самый простой вариант: допустим, у вас есть несколько ячеек с фразами на разных языках. Хотите перевести на русский (или любой другой) — формула поможет.
Чтобы перевести текст из A1 в B1, введите формулу:
и нажмите Enter.
Так как язык не указан, Google сам определит, что в A1 — например, китайский. Без цели перевода текст переведётся на язык, установленный по умолчанию.
Протяните формулу вниз — так вы легко переведёте целый столбец, даже если там перемешаны разные языки.
Теперь пример посложнее: допустим, в колонке C — пользовательские данные, а язык перевода зависит от того, что выбрано в B1.
Сначала создайте отдельный лист — вставьте туда список языков с их кодами, который можно взять с сайта Google прямо в ячейку A1.
Затем переведите названия языков на нужный язык: формулу автоперевода впишите в C1, нажмите Enter и согласитесь на автозаполнение вниз.
Дальше — создайте выпадающий список языков используя переведённые названия через проверку данных.
Теперь ставьте формулу GOOGLETRANSLATE к столбцу A — перевод будет сразу меняться в зависимости от выбранного языка. В B4 впишите формулу (разбейте на строки для наглядности):
Протяните формулу вниз, выберите в B1 нужный язык и убедитесь, как всё работает.
В B3 можно сделать автоперевод заголовка выбранного языка, а в C3 — перевести "Ответ" на нужный язык, чтобы весь интерфейс переводился с вами:
4. SPARKLINE: Мини-графики прямо в ячейке!
Графики наглядно показывают тенденции, но часто занимают слишком много места. В Google Таблицах есть «волшебная» функция SPARKLINE — она создаёт мини-график прямо внутри одной ячейки.
В Excel такие мини-графики добавляются через меню, а в Google Sheets — одной формулой, плюс возможностей настройки тут больше.
Sparkline сам заполняет всю ячейку. Хотите сделать график более заметным — увеличьте высоту строки или ширину столбца.
SPARKLINE: Синтаксис
Синтаксис с виду простой, но количество настроек заметно расширяет возможности:
Параметр a — ваши данные для графика, b (необязателен) — оформление (в фигурных скобках). Если нужен простой график, хватит первого; для большей наглядности используйте параметры.
SPARKLINE: Линейные графики
Для простой малой линии укажите просто диапазон с данными:
Изменять вид можно с помощью параметра b: настройки и их значения перечисляются через точки с запятой внутри фигурных скобок.
Вот какие настройки для мини-графика доступны:
Настройка
Описание
Возможные значения
Пример формулы
"xmin"
Минимум по оси X
Любое число, без кавычек
"xmax"
Максимум по оси X
Любое число, без кавычек
"ymin"
Минимум по оси Y
Любое число, без кавычек
"ymax"
Максимум по оси Y
Любое число, без кавычек
"color"
Цвет линии
Название цвета или hex-код (например, "green", "#52D978")
"empty"
Что делать с пустыми ячейками
"zero" — считать нулями, "ignore" — не учитывать
"nan"
Как считать нечисловые данные
"convert" — считать нулём, "ignore" — не учитывать
"rtl"
Порядок слева-направо
true — с одной стороны, false — с другой
"linewidth"
Толщина линии
Любое число, без кавычек
Пример: минималистичный красный мини-график, игнор пустых ячеек и увеличенная толщина линии:
SPARKLINE: Столбчатые и Win-Loss мини-графики
Чтобы сделать мини-график в столбцах, укажите в параметрах b тип chart: "column":
Для win-loss-графика используйте chart: "winloss":
Вот список дополнительных настроек для столбцов и win-loss:
Настройка
Описание
Возможные значения
Пример
"color"
Цвет столбцов
Название цвета/hex-код
"lowcolor"
Минимальное значение цветом
Название цвета/hex-код
"highcolor"
Максимальное значение цветом
Название цвета/hex-код
"firstcolor"
Цвет первого столбца
Название цвета/hex-код
"lastcolor"
Цвет последнего столбца
Название цвета/hex-код
"negcolor"
Цвет отрицательных значений
Название цвета/hex-код
"empty"
Обработка пустых ячеек
"zero" или "ignore"
"nan"
Обработка нечисловых данных
"convert" или "ignore"
"axis"
Показывать ось
true/false
"axiscolor"
Цвет оси
Название цвета/hex-код
"ymin"
Минимум по y (только для column)
Любое число
"ymax"
Максимум по y (только для column)
Любое число
"rtl"
Порядок справа-налево
true/false
Например, чтобы максимум был зелёным, остальные — серые, минимум по y — 50:
SPARKLINE: Мини-гистограммы (bar charts)
Мини-гистограммы отличаются от обычных графиков — они не делят оси X и Y, а показывают ваши значения одной полосой, выделяя пропорции.
Например, чтобы сравнить результаты команд за первую и вторую недели — просто введите:
в D2 и скопируйте вниз — сразу видно, чья команда выиграла.
Вот какие настройки доступны для мини-гистограмм:
Настройка
Описание
Возможные значения
Пример
"max"
Максимум по всей полосе
Любое число
"color1"
Первый цвет
Название цвета/hex-код
"color2"
Второй цвет
Название цвета/hex-код
"empty"
Обработка пустых ячеек
"zero" или "ignore"
"nan"
Что делать с нечисловыми данными
"convert" или "ignore"
"rtl"
Порядок построения
true/false
Рекомендую всегда ставить параметр "max", чтобы удобно сравнивать значения разных строк.
В этом варианте видно, какая команда выиграла по каждой неделе, а общий итог отображается цветом (укажем зелёный и жёлтый для наглядности):
Что выбрать — Google Таблицы или Excel? Всё зависит от ваших задач, привычек и необходимости совместной работы: кому-то важен мощный анализ Excel, а кто-то не представляет жизнь без удобства и скорости Google Таблиц.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru