Google Таблицы

@google_sheets Like 1

Работа с данными в Google Таблицах. Кейсы, решения и угар. Объясняем на гифках.
★ наша книга: https://www.mann-ivanov-ferber.ru/books/google-tabliczyi-eto-prosto
★ оглавление канала: https://goo.gl/HdS2qn
Написать нам: @renat_shagabutdinov, @namokonov
Channel's geo & Language
Russian, Russian


Contact author
Channel's geo
Russian
Channel language
Russian
Category
Technologies
Added to index
16.07.2017 14:30
Recent update
24.05.2019 01:35
7 101
members
~4.7k
avg post reach
~1.3k
daily reach
~2
posts per week
65.6%
ERR %
8.08
citation index
Forwards & channel mentions
22 mentions of channel
8 post mentions
21 forwards
MS Access Microsoft
3 May, 10:01
Google Ecosystem
24 Apr, 21:20
Google Ecosystem
5 Apr, 22:46
Google Ecosystem
11 Mar, 19:01
Google Ecosystem
24 Feb, 16:58
Google Ecosystem
21 Feb, 22:02
Google Ecosystem
16 Feb, 22:26
ЗаТелеком
11 Jan, 10:11
6 Dec 2018, 10:15
6 Dec 2018, 10:15
Google Ecosystem
25 Nov 2018, 13:13
23 Nov 2018, 04:57
Google Ecosystem
20 Nov 2018, 21:18
Google Ecosystem
6 Nov 2018, 20:56
31 Oct 2018, 09:55
31 Oct 2018, 09:47
/br/
12 Oct 2018, 17:57
Наумости
16 Aug 2018, 13:41
13 Aug 2018, 20:16
BigQuery Insights
7 Aug 2018, 15:44
26 Jul 2018, 16:05
Google Ecosystem
20 Jul 2018, 09:15
TechRocks
27 Jun 2018, 09:00
Product Science
9 Apr 2018, 16:52
13 Feb 2018, 19:01
12 Feb 2018, 08:50
PLUSHEV
23 Nov 2017, 13:00
Correctarium
27 Oct 2017, 13:30
Channels quoted by @google_sheets
Recent posts
Deleted
With mentions
Forwards
Google Таблицы 13 May, 12:35
​​Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу

Друзья, привет! Мы подготовили для вас скрипт. Он умеет переносить письма из gmail-аккаунта юзера, запустившего скрипт в Таблицу. 

Одна строка — одно письмо, при переносе скрипт делает перенесенные письма прочитанными в почтовом ящике.

Чтобы заработало, вам нужно:
1. скопировать Таблицу со скриптом
2. открыть редактор скриптов > дополнительные функции Google и включить Gmail API
3. заполнить первые три строки в коде: 
— ID таблицы и название листа, туда будут вставляться письма
— Поисковый запрос к почтовому ящику, например 
'after:2019/05/12' - письма после 12 мая
'after:2019/05/12 and is:unread' - после 12 мая и непрочитанные
4. запустить функцию run(), ее можно запускать как руками, так и ставить в расписание на регулярный запуск (изменить > триггеры текущего проекта в редакторе скриптов), письма по вашему запросу будут добавляться в Таблицу

P.S. Синтаксис поискового запроса: https://support.google.com/mail/answer/7190, созданный запрос можете проверить в веб-интерфейсе почты gmail.

Спасибо @oshliaer за отличный код!
Read more
​​Друзья, привет!

Допустим, у вас две таблицы: одна с приходом товара, а другая с его расходом.
Построим формулу, которая отобразит разницу этих таблиц по каждому наименованию.

1) умножаем таблицу с расходом на -1,
=ARRAYFORMULA(F7:G13*-1)
2) текстовые поля вернут ошибку, поэтому оборачиваем умножение в ЕСЛИОШИБКА (IFERROR), возвращая в случае ошибки исходный диапазон: =ARRAYFORMULA(IFERROR(F7:G13*-1;F7:G13))
3) объединяем таблицы с расходом и приходом: =ARRAYFORMULA({B7:C12;IFERROR(F7:G13*-1;F7:G13)})
4) и наконец: полученный массив используем в QUERY, группируем по первому столбцу, суммируя второй: =QUERY(ARRAYFORMULA({B7:C12;IFERROR(F7:G13*-1;F7:G13)});"select Col1, sum(Col2) group by Col1")


Таблица с примером.
Read more
​​Друзья, привет!

Допустим, у вас две таблицы: одна с приходом товара, а другая с его расходом.
Построим формулу, которая отобразит разницу этих таблиц по каждому наименованию.

1) умножаем таблицу с расходом на -1,
G7:H13*-1
2) текстовые поля вернут ошибку, поэтому оборачиваем умножение в ЕСЛИОШИБКА (IFERROR), возвращая в случае ошибки исходный диапазон: IFERROR(G7:H13*-1;G7:H13)
3) объединяем таблицы с расходом и приходом: {B7:C12;IFERROR(G7:H13*-1;G7:H13)}
4) и наконец: полученный массив используем в QUERY, группируем по первому столбцу, суммируя второй: =QUERY({B7:C12;iferror(G7:H13*-1;G7:H13)};"select Col1, sum(Col2) group by Col1")

Таблица с примером.
Read more
​​#промо Единственный канал о профессиональной разработке баз данных Microsoft Access. На канале мы научим всем тонкостям создания приложений корпоративного уровня, которых ты не встретишь ни на одних курсах. Автор покажет, как создать таблицы, запросы, красивые формы, презентабельные отчеты, анализировать данные, применять VBA и многое другое на канале @access_ms
Read more
Google Таблицы 26 Apr, 11:40
​​​​​​Подсчет ингредиентов на лету.

Есть две Таблицы, продажи блюд и разбивка каждого блюда по ингредиентам. Сделаем формулу, которая совместит таблицы и покажет суммарный вес каждого проданного ингредиента.

Формула целиком: =ARRAYFORMULA(QUERY({{F3:F9}\{VLOOKUP(E3:E9;B3:C6;2;0) * G3:G9}};"select Col1, sum(Col2) where Col2>0 group by Col1"))

Расшифровка каждого этапа - на скриншоте.
Таблица с примером
Read more
Google Таблицы 24 Apr, 12:07
​​​​Подсчет ингредиентов на лету.

Есть две Таблицы, продажи блюд и разбивка каждого блюда по ингредиентам. Сделаем формулу, которая совместит таблицы и покажет суммарный вес каждого проданного ингредиента.

Формула целиком: =ARRAYFORMULA(QUERY({{F3:F9}\{VLOOKUP(E3:E9;B3:C6;2;0) * G3:G9}};"select Col1, sum(Col2) where Col2>0 group by Col1"))

Расшифровка каждого этапа - на скриншоте.
Таблица с примером
Read more
​​​​Ищу квартиру на Павелецкой

Привет, друзья! Ищу для себя съемную квартиру в Москве, (однушку-двушку), идеально будет в районе Павелецкой / Таганской, но кольцевые станции тоже смотрю. На долгий срок.

Комиссия на ваш выбор: от консультации по Google Таблицам до вина и денег 🥳

Пишите: @namokonov
​​Друзья, у нас есть прайс-лист с наименованиями товаров. К каждому товару мы хотим подобрать картинку и вставить ее в Таблицу. Первый путь - можно загуглить каждое изображение руками, выгрузить и вставить эту картинку в Таблицу. Вариант рабочий, но придется потратить значительное время, особенно, если наименований у вас много.

Второй путь - вы можете вообще ничего не гуглить, а воспользоваться функцией IMPORTXML, обратиться к поиску картинок Яндекса (или Google), достать ссылку на изображение оттуда и с помощью IMAGE превратить его в картинку.

Разберем все по пунктам:
1) Формируем ссылку для поиска по картинкам Яндекса:
"https://yandex.ru/images/search?text="&a4 (a4 - ссылка на ячейку с названием товара)

2) Вытаскиваем из веб-страницы поиска все ссылки на изображения:
importxml("https://yandex.ru/images/search?text="&A4;"//img/@src")

3) Оставляем одну ссылку (например, вторую сверху):
index(importxml("https://yandex.ru/images/search?text="&A4;"//img/@src");2)

4) Превращаем ее в картинку (и это итоговая формула):
=IMAGE(index(importxml("https://yandex.ru/images/search?text="&A4;"//img/@src");2))

Ссылка на таблицу.
Read more
​​Автоматически создаем фразы по определенным шаблонам в Google Таблицах

Разберем, как с помощью формул в Google Таблицах автоматически создавать фразы по определенным шаблонам, в которые будут подставляться параметры из таблицы.

Статья в Medium.
Read more
Google Таблицы 25 Mar, 12:40
​​Сегодня гостевой пост:
Изменяем наш Google Calendar с помощью скриптов.


Передаем слово автору:

Меня, как пользователя телефона с системой Android, всегда не очень устраивал тот факт, что в моём календаре были обозначены дни рождения контактов, но без указания возраста человека.

Также и с юбилеями контактов - вроде бы юбилей есть, а сколько времени прошло с этого события непонятно.

Переходить в сам контакт и смотреть год рождения или дату юбилея, а потом что-то рассчитывать - на это времени никогда не было.

Решил сам себе упростить жизнь и написал Google Apps Script, который сначала ищет эти события в специальном календаре, который по умолчанию есть у всех и выводит эти события. А зачем на втором шаге рассчитывает возраст для конкретных контактов и уже создает события в основном календаре.

Вот ссылка на скрипт:
https://script.google.com/d/1oDswSXp_UleDXiTFushWnqziHi1Dlkb6x_neteWWKfEnjXttUUIPyRTd/edit?usp=sharing
Read more
Google Таблицы 11 Mar, 11:52
​​Флажки не обязательно использовать активно, меняя в них значение с TRUE / ИСТИНА (флажок есть) на FALSE / ЛОЖЬ (флажка нет).
Их можно использовать и для индикации, а не изменения.

Пример (из практики, хоть и сильно упрощенный): у нас есть список регламентов, с которыми должен ознакомиться сотрудник. Он ставит свою фамилию, когда знакомится с документом, а в соседнем столбце указывается фамилия руководителя, принявшего зачет на знание регламента.

Добавим формулу, которая будет возвращать значение TRUE / ИСТИНА, когда оба столбца заполнены:
=СЧЁТЗ(B2:C2)=2
(эта формула для примера. Главное - речь о формуле, которая возвращает логическое значение, да/нет, ИСТИНА/ЛОЖЬ)

А далее выделяем диапазон и вставляем флажки (Вставка -> Флажок)
И после этого появятся флажки, которые будут "включены" при выполнении условия. заложенного в формулы. При этом щелчком такие флажки, "под которыми" есть формула, не будут изменяться.

Ссылка на Таблицу с примером.
Read more
Google Таблицы 21 Feb, 10:53
​​Фильтруем две строки от пустых значений и объединяем в одну.

Поехали, разбираем формулу поэтапно:
1) Объединяем две строки в массив:
={A1:F1\A2:F2}

2) Разворачиваем массив в столбец функцией TRANSPOSE / ТРАНСП:
=TRANSPOSE({A1:F1\A2:F2})

3) Добавляем результат в функцию QUERY, фильтруем от пустых ячеек и разворачиваем обратно с помощью TRANSPOSE / ТРАНСП:
=TRANSPOSE(QUERY(TRANSPOSE({A1:F1\A2:F2});"where Col1 is not null"))

Таблица с примером.

P.S. В региональных настройках США, Канады и некоторых других создание массива будет выглядеть так: ={A1:F1,A2:F2}
Read more
​​Пересчет QUERY по запросу. Лайфхак.

Уже несколько раз мы сталкивались с тем, что при загрузке данных из других документов с помощью QUERY и вложенной в нее функции IMPORTRANGE бывают задержки с загрузкой.
Особенно если исходный файл очень тяжелый - обновления в нем могут отразиться лишь через некоторое время.

Можно вручную поменять формулу, чтобы произошел перерасчет. Но лучше создать своего рода "кнопку" для принудительного обновления данных.
Например, поставить флажок в любую ячейку, а формулу загрузки данных модифицировать следующим образом:

=ЕСЛИ(ячейка с флажком; QUERY(IMPORTRANGE(1)) ; QUERY(IMPORTRANGE(2)))

Главное - чтобы первая и вторая формулы в ЕСЛИ отличались. Например, можете в первом IMPORTRANGE указать диапазон вида A:E, а во втором - A1:E. Результат будет одинаковый. Но при щелчке на флажок данные будут сразу пересчитываться.
Read more
​​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. Если будете внедрять - напишите нам, как будет работать формула на ваших больших датасетах, нам интересно.
Read more
Друзья, мы решили начать делать дополнение, которое будет считать сложность формул.

Все формулы в Google Таблицах работают с разной скоростью и по-разному влияют на работу Таблицы в целом, разные формулы нагружают ее по-разному.

Как считать "вес" и "сложность" формул - пока не очень понятно, нам нужны ваши идеи. Не стесняйтесь, пишите в наш чат: https://t.me/google_spreadsheets_chat
Google Таблицы 30 Jan, 16:30
​​Незадокументированая особенность QUERY.

Друзья, привет! Мы нашли для вас что-то, чего нет в официальной документации. Кляуза skipping n позволяет оставить в диапазоне вывода каждую n-нную строку

Например,
skipping 3 - выводит каждую третью строку
skipping 5 - каждую пятую

Кляуза пишется в конце запроса - как limit (количество строк, которые выведет запрос) и offset (количество строк сверху, которые мы пропускаем и не выводим).
Read more
Google Таблицы 20 Jan, 18:59
​​​​Очищаем текст от HTML-тегов c помощью функции REGEXREPLACE.

Функция с регулярным выражением:
=REGEXREPLACE(A1;"";"")

Раздел про жадную и ленивую квантификацию на wiki.

Друзья, идею поста подал Павел Мрыкин, его канал: @eWorker. Спасибо ему!
Read more
Google Таблицы 14 Jan, 11:21
​​Друзья! Если вы работаете с онлайн-кассой и хотите нам немного помочь, то пожалуйста, заполните форму (там всего два вопроса).
Google Таблицы 12 Jan, 10:56
​​Теперь в Google Таблицах можно поменять цвет заливки отдельной точки данных - как в Excel.

Все просто:
1. Щелчок правой кнопкой на точку данных, которую вы хотите визуально выделить;
2. Цвет элемента;
3. Выбираем цвет на палитре.

Удивительно, что эта опция появилась только сейчас! Напомним, ранее вся серия могла быть только одного цвета.
Работает это для линейчатых, точечных диаграмм, гистограмм и графиков (для графика можно покрасить отдельным цветом точку).
На комбинированных графиках с двумя осями тоже можно красить элементы.

Новость - по ссылке: https://gsuiteupdates.googleblog.com/2019/01/assign-unique-colors-to-chart-elements.html
Read more
ВПР в массиве вместо тысячи CУММЕСЛИМН

Друзья, привет. В конце прошлого года в своих отчетах я начал переходить на формулы массива. Мне это показалось очень удобным - теперь, если это возможно, я стараюсь написать одну формулу, которая заполнит нужный диапазон, а не делать много одинаковых формул для каждой ячейки.

В статье - разобранный пример такой формулы.
Read more