Страница телеграм канала Google Таблицы
Google Таблицы
4530 подписчиков
Работа с данными в Google Таблицах. Кейсы, решения и угар. Объясняем на гифках. наша книжка: https://goo.gl/rDHu2N оглавление канала: https://goo.gl/HdS2qn Написать нам: @namokonov, @renat_shagabutdinov
Входит в категории: Технологии
11.03.2019 11:03
Флажки не обязательно использовать активно, меняя в них значение с TRUE / ИСТИНА (флажок есть) на FALSE / ЛОЖЬ (флажка нет).
Их можно использовать и для индикации, а не изменения.
Пример (из практики, хоть и сильно упрощенный): у нас есть список регламентов, с которыми должен ознакомиться сотрудник. Он ставит свою фамилию, когда знакомится с документом, а в соседнем столбце указывается фамилия руководителя, принявшего зачет на знание регламента.
Добавим формулу, которая будет возвращать значение TRUE / ИСТИНА, когда оба столбца заполнены:
=СЧЁТЗ(B2:C2)=2
(эта формула для примера. Главное - речь о формуле, которая возвращает логическое значение, да/нет, ИСТИНА/ЛОЖЬ)
А далее выделяем диапазон и вставляем флажки (Вставка -> Флажок)
И после этого появятся флажки, которые будут "включены" при выполнении условия. заложенного в формулы. При этом щелчком такие флажки, "под которыми" есть формула, не будут изменяться.
Ссылка на Таблицу с примером.
21.02.2019 10:02
Фильтруем две строки от пустых значений и объединяем в одну.
Поехали, разбираем формулу поэтапно:
1) Объединяем две строки в массив:
={A1:F1A2:F2}
2) Разворачиваем массив в столбец функцией TRANSPOSE / ТРАНСП:
=TRANSPOSE({A1:F1A2:F2})
3) Добавляем результат в функцию QUERY, фильтруем от пустых ячеек и разворачиваем обратно с помощью TRANSPOSE / ТРАНСП:
=TRANSPOSE(QUERY(TRANSPOSE({A1:F1A2:F2});"where Col1 is not null"))
Таблица с примером.
P.S. В региональных настройках США, Канады и некоторых других создание массива будет выглядеть так: ={A1:F1,A2:F2}
07.02.2019 15:02
Пересчет QUERY по запросу. Лайфхак.
Уже несколько раз мы сталкивались с тем, что при загрузке данных из других документов с помощью QUERY и вложенной в нее функции IMPORTRANGE бывают задержки с загрузкой.
Особенно если исходный файл очень тяжелый - обновления в нем могут отразиться лишь через некоторое время.
Можно вручную поменять формулу, чтобы произошел перерасчет. Но лучше создать своего рода "кнопку" для принудительного обновления данных.
Например, поставить флажок в любую ячейку, а формулу загрузки данных модифицировать следующим образом:
=ЕСЛИ(ячейка с флажком; QUERY(IMPORTRANGE(1)) ; QUERY(IMPORTRANGE(2)))
Главное - чтобы первая и вторая формулы в ЕСЛИ отличались. Например, можете в первом IMPORTRANGE указать диапазон вида A:E, а во втором - A1:E. Результат будет одинаковый. Но при щелчке на флажок данные будут сразу пересчитываться.
04.02.2019 12:02
SUMIF для нескольких условий в формуле массива. Объединяем диапазоны с помощью амперсанда (&).
(Пример 1) Считаем сумму по нескольким условиям (одна формула будет работать сразу для ряда ячеек):
=ARRAYFORMULA(SUMIF(A2:A15&B2:B15;E2:E4&F2:F4;C2:C15))
(Пример 2) Сумма по нескольким условиям, заполняем табличку с двумя измерениями:
=ARRAYFORMULA(SUMIF(A2:A&B2:B;E8:E13&F7:H7;C2:C))
Напоминаем синтаксис SUMIF: (диапазон условия; условие; диапазон суммирования).
P. S. Если будете внедрять - напишите нам, как будет работать формула на ваших больших датасетах, нам интересно.
03.02.2019 13:02
Друзья, мы решили начать делать дополнение, которое будет считать сложность формул.
Все формулы в Google Таблицах работают с разной скоростью и по-разному влияют на работу Таблицы в целом, разные формулы нагружают ее по-разному.
Как считать "вес" и "сложность" формул - пока не очень понятно, нам нужны ваши идеи. Не стесняйтесь, пишите в наш чат: https://t.me/google_spreadsheets_chat
30.01.2019 16:01
Незадокументированая особенность QUERY.
Друзья, привет! Мы нашли для вас что-то, чего нет в официальной документации. Кляуза skipping n позволяет оставить в диапазоне вывода каждую n-нную строку
Например,
skipping 3 - выводит каждую третью строку
skipping 5 - каждую пятую
Кляуза пишется в конце запроса - как limit (количество строк, которые выведет запрос) и offset (количество строк сверху, которые мы пропускаем и не выводим).
20.01.2019 18:01
Очищаем текст от HTML-тегов c помощью функции REGEXREPLACE.
Функция с регулярным выражением:
=REGEXREPLACE(A1;"<.+?>";"")
Раздел про жадную и ленивую квантификацию на wiki.
Друзья, идею поста подал Павел Мрыкин, его канал: @eWorker. Спасибо ему!
14.01.2019 11:01
Друзья! Если вы работаете с онлайн-кассой и хотите нам немного помочь, то пожалуйста, заполните форму (там всего два вопроса).
12.01.2019 10:01
Теперь в Google Таблицах можно поменять цвет заливки отдельной точки данных - как в Excel.
Все просто:
1. Щелчок правой кнопкой на точку данных, которую вы хотите визуально выделить;
2. Цвет элемента;
3. Выбираем цвет на палитре.
Удивительно, что эта опция появилась только сейчас! Напомним, ранее вся серия могла быть только одного цвета.
Работает это для линейчатых, точечных диаграмм, гистограмм и графиков (для графика можно покрасить отдельным цветом точку).
На комбинированных графиках с двумя осями тоже можно красить элементы.
Новость - по ссылке: https://gsuites.googleblog.com/2019/01/assign-unique-colors-to-chart-elements.html
09.01.2019 10:01
ВПР в массиве вместо тысячи CУММЕСЛИМН
Друзья, привет. В конце прошлого года в своих отчетах я начал переходить на формулы массива. Мне это показалось очень удобным - теперь, если это возможно, я стараюсь написать одну формулу, которая заполнит нужный диапазон, а не делать много одинаковых формул для каждой ячейки.
В статье - разобранный пример такой формулы.
30.12.2018 15:12
Каникулы - хорошее время, чтобы читать, изучать и осваивать новое.
Собрали для вас несколько неплохих книг и статей по таблицам.
Отличная статья по спарклайнам (на английском, но в основном там скриншоты и формулы) - с интересными примерами.
https://www.benlcollins.com/spreadsheets/sparklines-in-google-sheets/
Google Sheet Functions: A step-by-step guide
https://www.amazon.com/Google-Sheet-Functions-step-step-ebook/dp/B01NBHMZI9
Хорошее руководство по формулам в Таблицах. Все показано на примерах. Особенно их много по QUERY.
5 долларов (электронная книга).
Полезные функции Google Таблиц, которых нет в Excel
https://habr.com/post/331360/
Наша статья на Habr, рассказываем с примерами и скриншотами.
Интересные примеры использования FILTER
https://contributor.pw/post/google-sheets-formulas-filter/
Например, выбираем только женские имена из списка.
Всемогущая функция Query — подробное руководство:
https://netpeak.net/ru/blog/vsemogushchaya-funktsiya-query-podrobnoe-rukovodstvo/
Огромная статья про QUERY от Netpeak
Гайд по возможностям Google Таблиц
https://texterra.ru/blog/google-tablitsy-bolshoy-gayd-dlya-novichkov.html
Тут есть о дополнении для интеграции Google Analytics и Google Таблиц.
Going GAS: From VBA to Google Apps Script
https://www.amazon.com/Going-GAS-Google-Apps-Script-ebook/dp/B01BUK6H6O
Единственное пособие по скриптам Google, в том числе в Таблицах. Больше рассчитана на тех, кто переходит в Таблицы с Excel и умеет писать макросы на VBA, но и начинающим тоже пригодится.
The Ultimate Guide to Google Sheets
https://www.amazon.com/Ultimate-Guide-Google-Sheets-spreadsheet-ebook/dp/B01IMV1NH2
Бесплатная электрокнига. Основы работы с таблицами, с формами, немного про формулы, 50 полезных дополнений и другие полезности (допустим, пример создания CRM-системы в таблицах).
Бесплатный онлайн-курс по скриптам
https://courses.benlcollins.com/p/apps-script-blastoff
Бесплатный онлайн-курс по формулам
https://www.benlcollins.com/advanced-formulas-course/
Курс Рената по таблицам - очередной поток стартует 10-го января
https://skillfactory.ru/google-spreadsheets
Всех с наступающим Новым годом! До встречи после праздников.
26.12.2018 10:12
Отправляем СМС из Google Таблицы.
Привет, друзья! В статье в Medium покажем и расскажем, как работать с сервисом по отправке СМС через HTTP API прямо из Google Таблицы.
Все разберем на примере https://smsc.ru, но принцип вы сможете использовать где угодно.
24.12.2018 10:12
Выводим из массива данные, которые отвечают нашему условию: наличию определенного слова в текстовой строке (с учетом регистра и без), или же наличию одного из нескольких слов
Привет, друзья! Сегодня соединяем функции FILTER и REGEXMATCH и получаем простые и мощные решения (смотрите скриншот).
1) Выведем из массива улиц все улицы, содержащие в названии мотор:
=FILTER(A:A,REGEXMATCH(A:A, C2))
2) Сделаем условие независимым от регистра (будут найдены все варианты написания):
=FILTER(A:A,REGEXMATCH(A:A,"(?i)"&E2))
3) Ну и напоследок ищем сразу по ряду условий:
=FILTER(A:A,REGEXMATCH(A:A,"(?i)мотор|акаДЕМИК|адмиРАЛ|АДВОКАТ"))
21.12.2018 11:12
Друзья, привет! Сегодня повторяем наш пост о спарклайнах.
Сегодня поговорим о спарклайнах (графиках в ячейках).
Допустим, у вас есть данные по продажам помесячно, и вы хотите посмотреть динамику по каждому ряду, но при этом не создавать большого количества отдельных диаграмм (это долго и неудобно, они будут занимать много пространства на листе и т.д.). Для этого и нужны спарклайны — мини-графики в ячейках, которые появились в версии Excel 2010.
К счастью, в Таблицах они тоже есть и, в отличие от Excel, реализованы в виде функции (в Excel их нужно вставлять через меню “Вставка”).
Функция так и называется: SPARKLINE.
На гиф посмотрим, как сделать график и гистограмму:
https://goo.gl/IrDT6m
Копируйте таблицу с данными и пробуйте создать спарклайны самостоятельно:
https://goo.gl/fJD0Mk
В документе по ссылке подробная информация по спарклайнам:
https://goo.gl/uZhzYf
И еще один пак с примерами можно найти здесь: ссылка на пост у нас
08.12.2018 20:12
Про нашего бота: @google_spreadsheets_chat_bot
Привет, друзья! Недавно мы решили попрактиковаться в интеграции Google Таблиц и Telegram и создали своего телеграмм бота.
Бота зовут Щитс. Код написан на Google Apps Script и работает на веб-хуках, скрипт развернут как веб-приложение на Google Drive (это значит - бесплатно, за хостинг ничего платить не нужно).
Что умеет (команды бота):
/спасибо @durov - команда для чата, можно тегнуть участника и сказать спасибо, "спасибо" копятся
/идея напишите про ёлки и мандарины - ваша идея сохранится в Таблице с идеями
/поиск query - бот вернет из оглавления нашего канала все посты со словом запроса
/вопрос можно ли удалить Таблицы навсегда? - ваш вопрос сохранится в Таблице с вопросами
/донат - ссылка на наши Яндекс.Деньги и список последних поддержавших канал
Друзья, пользуйтесь! Будем рады обратной связи и предложениям по развитию бота, можете писать их в личные сообщения, либо сохранять в Таблице через команду /идея.