Страница телеграм канала Google Таблицы
Google Таблицы
14171 подписчиков
Работа с данными в Google Таблицах. Кейсы, решения и угар. Объясняем на гифках. наша книжка: https://goo.gl/rDHu2N оглавление канала: https://goo.gl/HdS2qn Написать нам: @namokonov, @renat_shagabutdinov
Входит в категории: Технологии
26.10.2020 09:10
ОТПРАВЛЯТОР: отправляем ваши отчеты в теле письма по расписанию
Друзья, представьте - вы отвечаете в своей компании за создание и поддержку отчётов. Вы настроили формулы, импорты из других таблиц, получили красивые отчёты и они у вас обновляются самостоятельно.
Сегодняшнее решение от нашей команды покажет, как настроить автоматическую отправку этой красоты всем заинтересованным людям на почту.
Таблица со скриптом
Как всё работает:
Заполняете лист "настройки", одна строка в нём - одно правило отправки диапазона. Вам нужно ввести таблицу, откуда берём данные, лист и диапазон, кому отправляем письмо, как часто его отправляем и тему письма
Дальше вы можете либо руками запустить скрипт (из "/ меню скриптов /"), либо поставить скрипт test на ежедневное выполнение, скажем, от 12 до 13.
Как заставить скрипт запускаться каждый день:
инструменты > редактор скриптов > кликаем на часы > "добавление триггера" > выбираем функцию test > триггер по времени > по дням > выбираем нужный час
Что происходит после запуска скрипта?
Скрипт запустился и определяет, если ли что-то, что нужно отправить сегодня и не было ли сегодня это уже отправлено (проверяются столбцы "дни недели отправки" и "дата и время последней отправки").
Если такие строки есть, то скрипт их обрабатывает: забирает из указанных диапазонов значения и с помощью простого скрипта превращает их в html-таблицу, далее формируется письмо и улетает адресатам.
Если всё получилось - в столбце "дата и время последней отправки" появляется дата и время когда письмо было отправлено.
==
Идейный вдохновитель и спонсор этого решения - Сергей М из нашего чата, спасибо ему
22.10.2020 10:10
Хотели бы узнать для себя полезные функции Excel, а также быстро работать с формулами? Обучаем практичным лайфхакам в работе с электронными таблицами на бесплатном онлайн-интенсиве: https://clc.am/01Ux8A.
Темы интенсива:
Сводные таблицы: умные и полезные инструменты Excel.
Функции подсчёта и суммирования, а также функции поиска и подстановки данных.
10 простых приёмов для быстрой работы в Excel.
Полученные знания закрепим выполнением практического задания, которое в прямом эфире разберёт наш эксперт. Вместе вы обсудите процесс выполнения работы, трудные моменты, а также сможете получить ответы на интересующие вопросы, которые касаются домашней работы.
Участники интенсива получат в подарок электронную книгу от издательства МИФ «Век живи, век учись». Авторы трёх лучших работ получат сертификат на 5 000 рублей, который можно будет потратить на покупку любого курса в Skillbox.
#реклама
19.10.2020 12:10
3D сумма, но в Таблицах
Друзья, в MS Excel есть функция 3D сумма, она позволяет суммировать ячейку от одного листа до другого, не перечисляя листы, которые будут между. Например, смотрите на гифку - от листа "янв" до "апрель".
В Таблицах такой встроенной функции нет, но мы для вас её повторили с помощью пользовательской функции (скрипт, который вы сможете запускать из листа), код ниже:
function SumSum(shFrom, shTo, cell){
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
let sum = 0;
let x = ss.getSheetByName(shFrom).getIndex();
let y = ss.getSheetByName(shTo).getIndex();
for(x; x <= y; x++){
const sheet = sheets[x-1];
sheet ? sum += sheet.getRange(cell).getValue() : ;
}
return sum;
}
Давайте попробуем разобрать все по строкам:
название функции и аргументы, которые будем в неё передавать из листа (лист начала, листа конца, ячейка, все должно быть в кавычках)
function SumSum(shFrom, shTo, cell){
определяем переменные ss, sheets, x, y и кладём в них текущую активную Таблицу, массив листов, индекс первого листа, индекс последнего листа:
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
let x = ss.getSheetByName(shFrom).getIndex();
let y = ss.getSheetByName(shTo).getIndex();
определяем sum – тут будет собираться наша сумма
let sum = 0;
начало нашего цикла, начинаем от индекса первого листа и продолжаем до индекса последнего листа включительно
for(x; x <= y; x++){
каждый шаг цикла
открываем Лист
const sheet = sheets[x-1];
проверяем, существует ли лист, если существует - достаём из него нужную нам ячейку и добавляем к sum
sheet ? sum += sheet.getRange(cell).getValue() : ;
завершаем цикл
}
возвращаем sum (в ячейку) и завершаем функцию
return sum;
}
==
Формула для запуска из листа должна выглядеть как-то так:
=SumSum("янв"; "апрель";"a1")
===
Оглавление канала
Канал: @google_sheets
Чат: @google_spreadsheets_chat
14.10.2020 10:10
Повторяем диапазон N раз
Друзья, сегодня отличная формула из нашего чата от Михаила Смирнова: повторяем диапазон столько раз, сколько нужно.
Формула такая:
=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1; C1); A2:A5)))
Как она работает:
1) формируем строку, где количество элементов – количество будущих повторов нашего диапазона. Например, { 1 2 3 }, формируется формулой:
=SEQUENCE(1; 3)
2) собираем ЕСЛИ в массиве, в условии – сформированная строка, в TRUE (если условия выполняются) - исходный диапазон:
=ARRAYFORMULA(IF(SEQUENCE(1; 3); A2:A5))
3) Что получилось? ЕСЛИ создает столько столбцов с диапазоном, сколько символов в строке, которую мы задали в первом шаге (смотрите правую часть гифки).
Чтобы объединить все столбцы - добавляем FLATTEN (результат в середине гифки).
P. S. А еще можно добавить FILTER, чтобы отфильтровать от пустых ячеек:
=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1; C1); FILTER(A2:A;A2:A<>""))))
===
Оглавление канала
Канал: @google_sheets
Чат: @google_spreadsheets_chat
09.10.2020 15:10
Полезные горячие клавиши в Таблицах
Вооружайтесь мизинцем (ибо все начинается с Ctrl) и берите в работу несколько полезных сочетаний горячих клавиш в Таблицах. А чтобы посмотреть их все, нажимайте Ctrl + /.
Ctrl + A — выделение всей таблицы, к которой относится активная ячейка
Ctrl + ` — отображение формул в ячейках
Ctrl + — очистка форматирования (только стилевого, не числового) выделенных ячеек
Ctrl + Backspace — возврат к активной ячейке (если вы пролистали документ и сейчас активная ячейка не на экране)
Ctrl + D — заполнение вниз. Допустим, у вас есть столбец с данными, вы ввели одну формулу рядом с ним и хотите ее "протянуть". Выделяйте диапазон и используйте эти горячие клавиши (другой вариант — двойной щелчок по маркеру в правом нижнем углу ячейки, но хорошо, когда есть альтернатива на клавиатуре)
И тройка приемов:
Необязательно использовать функции И / AND для логических операций. Можно заменять ее на умножение. Например, такая формула выдаст единицу, если выполняются оба условия — в ячейке A1 находится текст "Москва", а в A2 число меньше 100. Иначе формула будет возвращать ноль.
=(A1="Москва") * (A2<100)
(про И / ИЛИ в формулах массива писали еще здесь)
В функции ВПР / VLOOKUP можно использовать * для замены любого количества символов.
=ВПР("Москва*";диапазон с данными;2;0) — и мы ищем первую ячейку, которая начинается на слово Москва, забирая данные из второго столбца диапазона с данными.
Если вы хотите считать среднее / сумму за любой период, указанный в ячейке, введите начало диапазона как фиксированную ячейку, а конец диапазона задайте функцией ИНДЕКС со вторым параметром равным длине периода:
=СРЗНАЧ(начало диапазона с данными:ИНДЕКС(диапазон с данными;кол-во периодов))
=СРЗНАЧ(B2:ИНДЕКС(B2:B;$E$1))
===
Оглавление канала
Канал: @google_sheets
Чат: @google_spreadsheets_chat
08.10.2020 13:10
Хочешь с нуля освоить Excel и узнать все тонкости Google-таблиц? Тогда поспеши на курс от Skillbox, который научит тебя всем необходимым навыкам всего за 4 месяца.
Занимайся в любое удобное время всего несколько часов в неделю
Все подробности по ссылке: https://clc.am/BmEekA
Чему ты научишься в процессе обучения:
строить прогнозы;
правильно оформлять данные и наглядно их предоставлять;
создавать макросы для языка VBA;
работать с внешними источниками данных и инструментами фильтрации.
Пройдя курс, ты с легкостью улучшишь свое портфолио и выйдешь на новый уровень заработка.
Skillbox дарит скидку 40% на стоимость обучения!
#реклама
01.10.2020 15:10
API OZON ИЗ GOOGLE ТАБЛИЦЫ
Друзья, недавно у нашего читателя Дмитрия была задача научиться менять цены на свои товары используя API OZON для продавцов, обращаясь к нему из Google Таблицы.
Дмитрий со всем справился и написал об этом отличную статью (ссылка на Таблицу с кодом там тоже есть).
Читайте про API и приходите в наш чат обсуждать
Статья в MEDUIM
29.09.2020 12:09
Находим и выводим
Друзья, сегодня мы расскажем, как вывести блок с определенным словом с помощью формулы.
Используем QUERY
В запросе QUERY нельзя просто взять и отфильтровать по яблокам в первом столбце – в этом случае выведется только одна строка. Но, в QUERY есть магические кляузы OFFSET и LIMIT, они определяют, сколько строк отступить сверху при выводе массива и сколько строк вывести всего.
Поэтому:
1. с помощью MATCH / ПОИСКПОЗ находим позицию Яблок в столбце A
=MATCH("яблоки" ; A:A ; 0) //5
2. создаём текстовую строку запроса с результатом этой функции
="limit 3 offset " & MATCH("яблоки" ; A:A ; 0) //limit 3 offset 5
3. итоговая формула, объединяем запрос и QUERY
=QUERY(A1:C11; "limit 3 offset " & MATCH("яблоки" ; A:A ; 0) - 1 ; 0) // -1 чтобы строка с яблоками также попала в выборку
4. заголовков в наших данных нет, поэтому последний аргумент = 0
P.S. Если вам нужны не все столбцы, а только некоторые — вместо * укажите их номера. Например, A, C для вывода первого и третьего столбцов.
19.09.2020 12:09
ДОПУСКАТОР 2
Убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке.
Недавно к нам пришёл Андрей и объяснил, что ни с кем не хочет делиться своими файлами и попросил добавить в допускатор то, что в первом предложении.
Мы добавили, а теперь делимся с вами. Таблица со скриптом.
Как всегда всё просто:
1. делаете копию Таблицы
2. переходите на лист "убираем всех"
3. вставляете ссылки на файлы в A:A
4. выключаете чекбоксы в B:B
5. выбираете, что нужно сделать по каждой ссылке: убрать пользователей кроме себя И / ИЛИ закрыть доступ по ссылке
6. запускаете скрипт и происходит магия
Про код – мы получаем все ячейки с данными рабочего листа, дальше проходим по каждой строке с ссылкой и выключенным чекбоксом. Если выбрано "удалять всех" – получаем массив всех редакторов / читателей файла и каждого удаляем. Если выбрано "закрыть доступ" – меняем форму доступа на DriveApp.Access.PRIVATE, DriveApp.Permission.NONE и файл становится недоступен по ссылке для всех.
Код отдельно: https://pastebin.com/JzardKXe
Документация:
– про работу с листом
– про работу с файлами
– про доступ по ссылке
– про цикл forEach (но конечно можно использовать любой доступный вам)
===
Оглавление канала
Канал: @google_sheets
Чат: @google_spreadsheets_chat
18.09.2020 12:09
BI-аналитика: как освоить это направление, какие навыки и знания требуются? Попробуй себя в новой профессии и реши, продолжать ли развиваться в ней!
Прими участие в бесплатном онлайн-интенсиве: https://clc.am/csxnhA
На занятиях ты научишься:
ориентироваться в разных видах баз данных и пирамидах метрик;
выбирать данные для анализа;
подключаться к Excel и Google Sheets через Power BI;
создавать функции в Power BI.
Всех, кто дойдёт до конца, ждёт подарок — электронная книга Кея Петерсона и Дэвида Колба «Век живи — век учись» от издательства «МИФ»!
#реклама
15.09.2020 09:09
Массивный ВПР
Дано: список специалистов с их ставками за час работы.
И другая таблица с разбивкой: сколько часов работы какого специалиста нужно на определенный проект.
Задача: одной формулой получить стоимость всего проекта.
Решение: будем перемножать весь диапазон с количеством часов специалистов на функцию ВПР, которая вернет массив с их ставками:
=СУММПРОИЗВ(B2:F2;ВПР($B$1:$F$1;Специалисты и ставки!$A:$B;2;0))
СУММПРОИЗВ / SUMPRODUCT перемножает элементы массивов и возвращает сумму этих произведений.
Первый аргумент - это часы специалистов, а второй - функция ВПР, которая по заголовкам столбцов подтянет ставки из другого листа.
Обратите внимание, что порядок специалистов на листах отличается. С функцией ВПР меньше вероятность ошибки, чем если бы мы просто перемножали два массива (такая схема возможна только если порядок и количество специалистов на 2 листах строго совпадают).
В случае с ВПР на листе со ставками может быть любое количество специалистов в любом порядке, а подтягивать их можно на любое количество листов с работами, где будет только часть специалистов — опять же, в любой последовательности.
Таблица с примером
===
Еще наше про ВПР:
– ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)
– ВПР (VLOOKUP) по нескольким условиям
– ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium.
– ВПР по нескольким диапазонам
– ВПР с интервальным просмотром = 1
– Перекрестный ВПР (ищем по строке и заголовку)
– Видео про функцию ВПР в Google Таблицах
===
Оглавление канала
Канал: @google_sheets
Чат: @google_spreadsheets_chat