#GoogleТаблицы — это аналог #Excel с веб-интерфейсом и возможностями для удобной совместной работы. Они похожи, и все же в Таблицах есть свои особенности, функции, плюсы и минусы. Работать в Google Таблицах просто и мы покажем как.
Вывод названия месяца из даты в именительном падеже
— МЕСЯЦ/MONTH возвращает номер месяца (от 1 до 12) на который выпадает заданная дата — ВЫБОР/CHOOSE достает из списка с названиями месяца значение по индексу — Формула сможет работать в ARRAYFORMULA, сразу для многих ячеек (см. скриншот, там диапазон A1:A10)
Скрипт для работы с Google Календарем. Напоминалка про дни рождения.
Сегодня гостевой пост, скрипт от нашего подписчика @mike59. Это уже второй скрипт от него на нашем канале, первый был тоже про Google Календарь.
Передаем слово автору: Написал код, который облегчает жизнь молодым отцам. Ведь в то время как все бабушки отлично помнят день рождения своего внука/внучки и поздравляют с этим событием ежемесячно - мне никак не удавалось удержать это в памяти. Решил слегка автоматизировать процесс и заодно разобраться как работать с датами в Google Apps Script. И конечно же сделать, чтобы эта напоминалка появлялась заблаговременно, а не в день рождения малыша!
Друзья, в FILTER можно комбинировать логические условия OR и AND. Для этого нужно сделать следующее:
1) Взять каждое условие в круглые скобки 2) Соединить условия разделителем: OR (ИЛИ):+ AND (И):* или ; или , (в зависимости от региональных настроек разделителем может выступать точка с запятой или запятая; звездочка будет работать при любых настройках)
Функция QUERY. Формируем запрос в WHERE ко всем колонкам формулой
Привет! Запрос в QUERY — текстовая строка и поэтому его всегда можно сформировать формулой.
Представьте — в ваших данных сто колонок и вы хотите написать запрос и вывести строки, в которых каждая ячейка будет больше 0.
Можно перечислить все сто колонок руками: WHERE Col1>0 and Col2>0 and ColN>0 and Col100>0, а можно написать формулу, которая создаст эту текстовую строку.
Друзья, привет, в этом проекте мы собираем небольшие скрипты onEdit(), про которые когда-то писали на канале. А ниже — два новых скрипта, спасибо нашему чату за идеи.
1) Изменяем название листа в зависимости от введенного в ячейку A1: function onEdit(e) { var ss = e.source; var sheet = ss.getActiveSheet(); var r = e.range; var v = e.value;
2) Пользователь написал «лазер» в третьем столбце — отправим эту строку на другой лист function onEdit(e) { var ss = e.source; var sheet1 = ss.getActiveSheet(); var sheet2 = ss.getSheetByName("сюда"); var lr = sheet2.getLastRow() + 1; var range = e.range; var row = range.getRow(); var column = range.getColumn(); var value = e.value;
Итак, друзья, Google Таблицы продолжают обновляться - и некоторое время назад в них появились срезы.
Они похожи на срезы в Excel. Там их можно применять к сводным таблицам и "умным таблицам" (которые вызываются опцией "Форматировать как таблицу").
Срез - это отдельно вынесенный фильтр, примененный к одному столбцу. Удобно и наглядно.
В Google Таблицах Срезы можно применять к обычным диапазонам, сводным таблицам и диаграммам. Вызываются срезы через меню Данные -> Срезы.
После создания среза выберите столбец, по которому будут фильтроваться данные. Срезов может быть несколько на одну таблицу - если вам нужно фильтровать по нескольким столбцам.
Если вы хотите работать не с обычным диапазоном, а со сводной - создайте срез на листе со сводной и не забудьте убедиться, что активирована галочка "Применить к сводным таблицам" в настройках среза. Там же в боковой панели среза на вкладке "Дополнительные" можно отредактировать оформление - например, поменять цвет фона, как в нашем примере.
Если хотите применить срез к диаграмме - создайте его на листе с диаграммой. При этом фильтровать можно и данные на другом листе. Главное, чтобы это были те данные, на основе которых диаграмма построена.
В Москве 14 и 15 ноября 2019 пройдет самая большая в России конференция по маркетинговой и продуктовой аналитике «Матемаркетинг»
Друзья, напоминаем про "Матемаркетинг" - конференцию, на которой с мастер-классом будет представлен и наш канал. Расскажу на примерах о том, как применять Таблицы для создания панелей показателей и для эффективной работы с аналитикой. О тех полезных опциях, котрых нет в Excel.
А в целом конференция «Матемаркетинг-2019» - это 2000 маркетинговых аналитиков, performance-маркетологов, стратегов и представителей онлайн-бизнеса; десять (!) отдельных треков - от сквозной маркетинговой аналитики и нюансов A/B тестирования до аналитической инфраструктуры. Ключевая особенность «Матемаркетинга» — практическая направленность и работа с реальными задачами. Спикерами выступают специалисты-практики.
Подробная программа конференции доступна по ссылке (кстати, это Google Таблица ;) )
Только сегодня на сайте конференции действует скидка 25%.
Копируем данные из сотен Таблиц с помощью Advanced Google Services
Максимальное время работы одного GAS скрипта — 6 минут. Это значит, что если вы с помощью Spreadsheet Service пытаетесь обойти сотни Таблиц и скопировать из них тысячи строк — времени может не хватить и ваш скрипт завершится по таймауту.
Есть альтернатива, есть более быстрый способ манипулировать файлами, обращаться к ним, копировать данные, удалять данные и наводить всяческий хаос, как мы любим — это Advanced Script Services. У вас будут те же 6 минут для работы скрипта, но за это время вы сможете успеть больше.
Мы подготовили для вас пример: скрипт 100 раз открывает оглавление нашего канала, объединяет данные в один массив и вставляет результат в Таблицу. Еще Advanced API грузит только заполненные ячейки, чтобы это побороть и не нарушить итоговую схему данных — скрипт дописывает пустые ячейки в те строки массива, где они нужны.
Друзья, рад сообщить, что подготовил часть нового большого курса Skillbox. Часть, посвященную Google Таблицам - на базовом и продвинутом уровне (от основ работы с формулами до всех кляуз QUERY и записи макросов). Более 60 видео с примерами и домашними заданиями.
Первая половина курса посвящена Excel, как вы уже догадались. Тоже системно - от основ до VBA.
Так что это отличный способ либо закрыть пробелы в табличном образовании, либо основательно изучить оба редактора с нуля.
И сегодня у Skillbox началась распродажа, которая продлится несколько дней - серьезная скидка на все курсы, включая Excel+Таблицы.
Почти три года и сотни публикаций, кейсов, формул — вот такой путь к 10 000 подписчиков. Друзья, благодарим вас за поддержку! И двигаемся дальше.
А сегодня - про простую функцию, которая может быстро сформировать массив хоть из 10 000 чисел. Это SEQUENCE. Она простая — у нее следующие аргументы: число строк, число столбцов, первое значение и шаг.
=SEQUENCE(10000;1;1;1) - это один столбец с числами от 1 до 10000. =SEQUENCE(3;3;0;100) - матрица 3 на 3 от 0 до 800.
P.S. Дата в Таблицах — это тоже число, поэтому с помощью функции вы сможете формировать массивы и из дат
Друзья, ниже мы отобрали для вас избранные посты нашего канала:
Функции: — Учимся извлекать из текста нужное. База знаний регулярных выражений для REGEXEXTRACT →→ — Памятка по синтаксису QUERY / FILTER / SUMIFS →→ — Функция FILTER. Список условий выбираем диапазоном прямо с листа →→ — Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы →→ — SUMIF для нескольких условий в формуле массива →→ — Формулой достаем изображения из поисковой выдачи Яндекса →→ — ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце) →→ — ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой) →→ — Автоматически создаем фразы по определенным шаблонам в Google Таблицах →→ — Пользовательские числовые форматы, добавляем к числам 🔥🔥🔥→→
Скрипты: — Скрипт. Распознаем текст на изображениях. OCR в Google Docs →→ — Простой скрипт копирования / фильтрации (поможет, когда IMPORTRANGE перестает работать) →→ — Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →→ — Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →→ — Скрипт, отправляем письма на электропочты из диапазона, который вы выделяете →→ — Простой скрипт для ежедневной рассылки из Google Таблицы →→ — Важный скрипт. Связанные выпадающие списки из кэша →→
Проекты: — Telegram bot + Google Sheets (используя вебхуки) →→ — Эмоджи форматирование выполнения плана (IMAGE, IFS) →→ — Создаем красивое расписание групповых занятий →→ — Создание оглавления в телеграм-канале: как автоматически загружать и сортировать публикации из вашего рабочего файла →→ — Google Форма + Google Таблица для проведения тестирования →→ — Применение IFTTT для отправки сообщений с данными из Таблиц →→ — Googlefinance, Парето и графики (#готовое решение) →→ — Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям →→ — Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY) →→
Для новичков: — Мы на Хабре: "Функции Таблиц, которых нет в Excel": https://habrahabr.ru/post/331360/ — Советы по оптимизации Таблиц →→ — Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц) →→ — Видеоурок по фильтрам и режиму фильтрации →→ — Про виды доступа к документам →→ — Совместная работа с фильтрами →→
Коллеги, внутри QUERY на данный момент нельзя использовать доступную в большинстве SQL-диалектов функцию CASE.
Но — вы можете с помощью IF и массива создать виртуальный столбец, присоединить его к своим данным и уже эту конструкцию сделать диапазоном QUERY. И, например, группировать по новому столбцу.
1) Определяем, начинаются имена на «B» или нет (скриншот наверху): =ARRAYFORMULA(IF(A:A<>"";IF(LEFT(A:A;1)="В";"Имена на В";"Другие имена");""))
2. Добавляем диапазон данных в QUERY и группируем по новому столбцу, считаем сумму столбца B
=QUERY({ARRAYFORMULA(IF(A:A<>"";IF(LEFT(A:A;1)="В";"Имена на В";"Другие имена");""))\A1:B}; "Select Col1, sum(Col3) group by Col1")
Некоторые поинты: 1. ARRAYFORMULA (функция массива) нужна, чтобы обработать в IF больше одной ячейки внутри одной формулы 2. AND в функции массива не работает (можете проверить) и приходится использовать вложенный IF (см. формулу в пункте 1) 3. Чтобы обращаться внутри QUERY к колонкам как Col1, Col2 - достаточно формулой изменить диапазон или просто взять его в фигурные скобки {}
Последнее место работы — Яндекс.Такси 🚕, аналитик по ценообразованию: репрайсил тарифы в городах МО, определял города в балансе и придумывал процедуры для них, а еще сделал тысячу разных калькуляторов.
— Перед этим работал аналитиком в компаниях Marriott, Lotte, Автомир
— Частенько бываю продакт оунером, мы разрабатываем для внешних заказчиков софт на C++/Python/GAS и строим ERP и CRM-системы в Таблицах (отзыв нашего клиента Григория)
IF внутри условия FILTER, отбираем ВСЕ значения или конкретное
Друзья, мы нашли полезный хинт в функции FILTER: если в условии написать диапазон условия = диапазон условия, то функция выведет все строки из диапазона вывода.
Можно использовать для выпадающих списков в интерактивных дашбордах, которые вы строите с помощью FILTER.
=FILTER(A2:B25;A2:A25=IF(E1="ВСЁ";A2:A25;E1))
P. S. А вот так в FILTER можно написать ИЛИ: =FILTER(A2:B25;(A2:A25<>"Майер")+(B2:B25<500000))
