Страница телеграм канала Google Таблицы
Google Таблицы
4530 подписчиков
Работа с данными в Google Таблицах. Кейсы, решения и угар. Объясняем на гифках. наша книжка: https://goo.gl/rDHu2N оглавление канала: https://goo.gl/HdS2qn Написать нам: @namokonov, @renat_shagabutdinov
Входит в категории: Технологии
06.11.2019 09:11
OR / AND в функции FILTER
Друзья, в FILTER можно комбинировать логические условия OR и AND. Для этого нужно сделать следующее:
1) Взять каждое условие в круглые скобки
2) Соединить условия разделителем:
OR (ИЛИ): +
AND (И): * или ; или , (в зависимости от региональных настроек разделителем может выступать точка с запятой или запятая; звездочка будет работать при любых настройках)
Примеры — на скриншоте
Таблица с примером
03.11.2019 15:11
Функция QUERY. Формируем запрос в WHERE ко всем колонкам формулой
Привет! Запрос в QUERY — текстовая строка и поэтому его всегда можно сформировать формулой.
Представьте — в ваших данных сто колонок и вы хотите написать запрос и вывести строки, в которых каждая ячейка будет больше 0.
Можно перечислить все сто колонок руками: WHERE Col1>0 and Col2>0 and ColN>0 and Col100>0, а можно написать формулу, которая создаст эту текстовую строку.
На скриншоте показываем, как это сделать.
Таблица с примером
31.10.2019 09:10
Библиотека небольших скриптов onEdit()
Друзья, привет, в этом проекте мы собираем небольшие скрипты onEdit(), про которые когда-то писали на канале. А ниже — два новых скрипта, спасибо нашему чату за идеи.
1) Изменяем название листа в зависимости от введенного в ячейку A1:
function onEdit(e) {
var ss = e.source;
var sheet = ss.getActiveSheet();
var r = e.range;
var v = e.value;
if(r.getA1Notation() === A1 && sheet.getName() !== v){sheet.setName(v)}
}
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;
if(column == 3 && value.match(/лазер/gi)){
sheet2.getRange("A" + lr + ":" + "C" + lr).setValues(
sheet1.getRange("A" + row + ":" + "C" + row).getValues()
)}
}
28.10.2019 07:10
Срезы в Google Таблицах
Итак, друзья, Google Таблицы продолжают обновляться - и некоторое время назад в них появились срезы.
Они похожи на срезы в Excel. Там их можно применять к сводным таблицам и "умным таблицам" (которые вызываются опцией "Форматировать как таблицу").
Срез - это отдельно вынесенный фильтр, примененный к одному столбцу. Удобно и наглядно.
В Google Таблицах Срезы можно применять к обычным диапазонам, сводным таблицам и диаграммам.
Вызываются срезы через меню Данные -> Срезы.
После создания среза выберите столбец, по которому будут фильтроваться данные. Срезов может быть несколько на одну таблицу - если вам нужно фильтровать по нескольким столбцам.
Если вы хотите работать не с обычным диапазоном, а со сводной - создайте срез на листе со сводной и не забудьте убедиться, что активирована галочка "Применить к сводным таблицам" в настройках среза.
Там же в боковой панели среза на вкладке "Дополнительные" можно отредактировать оформление - например, поменять цвет фона, как в нашем примере.
Если хотите применить срез к диаграмме - создайте его на листе с диаграммой. При этом фильтровать можно и данные на другом листе. Главное, чтобы это были те данные, на основе которых диаграмма построена.
25.10.2019 15:10
В Москве 14 и 15 ноября 2019 пройдет самая большая в России конференция по маркетинговой и продуктовой аналитике «Матемаркетинг»
Друзья, напоминаем про "Матемаркетинг" - конференцию, на которой с мастер-классом будет представлен и наш канал.
Расскажу на примерах о том, как применять Таблицы для создания панелей показателей и для эффективной работы с аналитикой. О тех полезных опциях, котрых нет в Excel.
А в целом конференция «Матемаркетинг-2019» - это 2000 маркетинговых аналитиков, performance-маркетологов, стратегов и представителей онлайн-бизнеса; десять (!) отдельных треков - от сквозной маркетинговой аналитики и нюансов A/B тестирования до аналитической инфраструктуры.
Ключевая особенность «Матемаркетинга» — практическая направленность и работа с реальными задачами. Спикерами выступают специалисты-практики.
Подробная программа конференции доступна по ссылке (кстати, это Google Таблица ;) )
24.10.2019 11:10
Всё, что касается Google Таблиц можно обсудить в нашем чате: @google_spreadsheets_chat
Присоединяйтесь!
14.10.2019 15:10
Копируем данные из сотен Таблиц с помощью Advanced Google Services
Максимальное время работы одного GAS скрипта — 6 минут. Это значит, что если вы с помощью Spreadsheet Service пытаетесь обойти сотни Таблиц и скопировать из них тысячи строк — времени может не хватить и ваш скрипт завершится по таймауту.
Есть альтернатива, есть более быстрый способ манипулировать файлами, обращаться к ним, копировать данные, удалять данные и наводить всяческий хаос, как мы любим — это Advanced Script Services. У вас будут те же 6 минут для работы скрипта, но за это время вы сможете успеть больше.
Мы подготовили для вас пример: скрипт 100 раз открывает оглавление нашего канала, объединяет данные в один массив и вставляет результат в Таблицу. Еще Advanced API грузит только заполненные ячейки, чтобы это побороть и не нарушить итоговую схему данных — скрипт дописывает пустые ячейки в те строки массива, где они нужны.
Официальная документация
Перед использованием службу нужно активировать
Таблица с примером
11.10.2019 17:10
Вытащим из предложения все товары и посчитаем их стоимость в рублях
Привет, друзья! Представьте, у нас есть текстовая строка:
купили: товар 1, товар 2, товар 6
Рассказываем, как в этой строке оставить только товары, вытащить каждый отдельно, сопоставить с прайс-листом и посчитать общую сумму.
Всё на скриншоте.
Таблица с примером и формулами
09.10.2019 11:10
Курс по Excel и Google Таблицам "С нуля до PRO"
Друзья, рад сообщить, что подготовил часть нового большого курса Skillbox.
Часть, посвященную Google Таблицам - на базовом и продвинутом уровне (от основ работы с формулами до всех кляуз QUERY и записи макросов).
Более 60 видео с примерами и домашними заданиями.
Первая половина курса посвящена Excel, как вы уже догадались. Тоже системно - от основ до VBA.
Так что это отличный способ либо закрыть пробелы в табличном образовании, либо основательно изучить оба редактора с нуля.
И сегодня у Skillbox началась распродажа, которая продлится несколько дней - серьезная скидка на все курсы, включая Excel+Таблицы.
Изучить программу и зарегистрироваться можно по ссылке.
С уважением, Ренат
07.10.2019 17:10
Почти три года и сотни публикаций, кейсов, формул — вот такой путь к 10 000 подписчиков.
Друзья, благодарим вас за поддержку! И двигаемся дальше.
А сегодня - про простую функцию, которая может быстро сформировать массив хоть из 10 000 чисел.
Это SEQUENCE.
Она простая — у нее следующие аргументы:
число строк, число столбцов, первое значение и шаг.
=SEQUENCE(10000;1;1;1) - это один столбец с числами от 1 до 10000.
=SEQUENCE(3;3;0;100) - матрица 3 на 3 от 0 до 800.
P.S. Дата в Таблицах — это тоже число, поэтому с помощью функции вы сможете формировать массивы и из дат
04.10.2019 12:10
Друзья, ниже мы отобрали для вас избранные посты нашего канала:
Функции:
— Учимся извлекать из текста нужное. База знаний регулярных выражений для 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 (для связывания нескольких таблиц)
— Видеоурок по фильтрам и режиму фильтрации
— Про виды доступа к документам
— Совместная работа с фильтрами
Полное оглавление нашего канала: https://goo-gl.ru/5Kc3
Наш чат: @google_spreadsheets_chat
30.09.2019 16:09
2. Добавляем диапазон данных в QUERY и группируем по новому столбцу, считаем сумму столбца B
=QUERY({ARRAYFORMULA(IF(A:A<>"";IF(LEFT(A:A;1)="В";"Имена на В";"Другие имена");""))A1:B};
"Col1, sum(Col3) group by Col1")
Некоторые поинты:
1. ARRAYFORMULA (функция массива) нужна, чтобы обработать в IF больше одной ячейки внутри одной формулы
2. AND в функции массива не работает (можете проверить) и приходится использовать вложенный IF (см. формулу в пункте 1)
3. Чтобы обращаться внутри QUERY к колонкам как Col1, Col2 - достаточно формулой изменить диапазон или просто взять его в фигурные скобки {}