Google Таблицы

@google_sheets Like 1 6 000

Работа с данными в Google Таблицах. Кейсы, решения и угар
контакты, заказы:
@namokonov
@renat_shagabutdinov
оглавление: https://goo.gl/HdS2qn
наша книга: https://www.mann-ivanov-ferber.ru/books/google-tabliczyi-eto-prosto
чат: @google_spreadsheets_chat
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
advertising
TGStat Bot
Bot to get channel statistics without leaving Telegram
TGAlertsBot
Monitoring of keywords in channels and chats
SearcheeBot
Your guide in the world of telegram channels
14 044
members
~5.3k
avg post reach
~2.3k
daily reach
~3
posts per week
37.7%
ERR %
15.41
citation index
Forwards & channel mentions
37 mentions of channel
38 post mentions
38 forwards
Word Hacks
12 Jun, 11:01
1С Hacks
29 May, 11:20
english yopta
7 Apr, 13:00
ЗаТелеком
3 Feb, 11:35
Volond Apps
2 Feb, 01:14
Galaktik_Bot
8 Jan, 12:27
SkillFactory
9 Dec 2019, 13:01
16 Nov 2019, 23:03
8 Nov 2019, 21:16
31 Oct 2019, 23:35
14 Oct 2019, 16:22
4 Oct 2019, 22:43
30 Sep 2019, 22:12
18 Sep 2019, 21:02
12 Sep 2019, 20:43
6 Sep 2019, 21:56
2 Sep 2019, 22:33
24 Aug 2019, 23:37
23 Aug 2019, 19:37
23 Aug 2019, 15:15
PLUSHEV/ПЛЮЩЕВ
23 Aug 2019, 12:05
16 Aug 2019, 19:45
31 Jul 2019, 22:48
24 Jul 2019, 21:22
22 Jul 2019, 20:57
18 Jul 2019, 00:01
15 Jul 2019, 23:16
11 Jul 2019, 20:30
Recent posts
Deleted
With mentions
Forwards
Google Таблицы 19 Sep, 12:50
ДОПУСКАТОР 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
Read more
Google Таблицы 19 Sep, 12:50
Google Таблицы 18 Sep, 12:30
BI-аналитика: как освоить это направление, какие навыки и знания требуются? Попробуй себя в новой профессии и реши, продолжать ли развиваться в ней!

Прими участие в бесплатном онлайн-интенсиве: ▶️ https://clc.am/csxnhA

На занятиях ты научишься:
✔️ ориентироваться в разных видах баз данных и пирамидах метрик;
✔️ выбирать данные для анализа;
✔️ подключаться к Excel и Google Sheets через Power BI;
✔️ создавать функции в Power BI.

🎁 Всех, кто дойдёт до конца, ждёт подарок — электронная книга Кея Петерсона и Дэвида Колба «Век живи — век учись» от издательства «МИФ»!

#реклама
Read more
Google Таблицы 15 Sep, 09:09
Массивный ВПР

Дано: список специалистов с их ставками за час работы.
И другая таблица с разбивкой: сколько часов работы какого специалиста нужно на определенный проект.

Задача: одной формулой получить стоимость всего проекта.

Решение: будем перемножать весь диапазон с количеством часов специалистов на функцию ВПР, которая вернет массив с их ставками:

=СУММПРОИЗВ(B2:F2;ВПР($B$1:$F$1;'Специалисты и ставки'!$A:$B;2;0))

СУММПРОИЗВ / SUMPRODUCT перемножает элементы массивов и возвращает сумму этих произведений.
Первый аргумент - это часы специалистов, а второй - функция ВПР, которая по заголовкам столбцов подтянет ставки из другого листа.

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

В случае с ВПР на листе со ставками может быть любое количество специалистов в любом порядке, а подтягивать их можно на любое количество листов с работами, где будет только часть специалистов — опять же, в любой последовательности.
Таблица с примером

===
Еще наше про ВПР:
​​ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)
ВПР (VLOOKUP) по нескольким условиям
ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium.
ВПР по нескольким диапазонам
ВПР с интервальным просмотром = 1
Перекрестный ВПР (ищем по строке и заголовку)
Видео про функцию ВПР в Google Таблицах

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat
Read more
Google Таблицы 15 Sep, 09:09
Forwarded from: Google Таблицы
КАК МЫ ПРОВЕЛИ ЛЕТО

Друзья, ниже - подборка наших летних скриптов. Полезные, бесплатные и с говорящими названиями.

Налетайте, не благодарите и делитесь с друзьями:

ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах t.me/google_sheets/563

ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней t.me/google_sheets/564

ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске t.me/google_sheets/565

ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное t.me/google_sheets/567

TELEGRAM BOT (+ полная инструкция): t.me/google_sheets/556

СОБИРАТОР (копируем и вставляем много данных через sheets api) t.me/google_sheets/536

===

📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat
Read more
Аналитик данных — специалист, который умеет извлекать полезную информацию из различных источников и на её основе находить наиболее выгодные решения для бизнеса.

В программе профессиональной переподготовки «Аналитик данных» вы изучите основы баз данных и работу с ними на языке SQL, язык Python и его применение для сбора, визуализации и анализа данных, статистику и машинное обучение, а также конкретные аналитические и продуктовые подходы, которые понадобятся при работе над реальными задачами.

В результате прохождения программы вы получите актуальный технический стек современного аналитика для работы в ведущих IT-компаниях.

Записывайтесь прямо сейчас:
https://cs.hse.ru/dpo/analyst

#реклама
Read more
КАК МЫ ПРОВЕЛИ ЛЕТО

Друзья, ниже - подборка наших летних скриптов. Полезные, бесплатные и с говорящими названиями.

Налетайте, не благодарите и делитесь с друзьями:

ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах t.me/google_sheets/563

ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней t.me/google_sheets/564

ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске t.me/google_sheets/565

ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное t.me/google_sheets/567

TELEGRAM BOT (+ полная инструкция): t.me/google_sheets/556

СОБИРАТОР (копируем и вставляем много данных через sheets api) t.me/google_sheets/536

===

📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat
Read more
Пять мини-лайфхаков в Google Таблицах

Если вам нужно вернуть только первое значение из массива, возвращаемого FILTER, поставьте перед функцией +
=FILTER(A:C; C:C < 2000) — все значения меньше 2000
=+FILTER(A:C; C:C < 2000) — первое значение меньше 2000

Клавиша F4 — ей мы бы дали звание самой недооцененной. Это повтор последнего действия. Работает не для всего, но вставка строк/столбцов, форматирование и многое другое — очень ускоряет работу. Кстати, работает и в Excel. На Mac: ⌘ + Y или ⌘ + Shift + Z или Fn + F4.

Ctrl+Shift+V — вставка только значений. Еще один наш фаворит среди горячих клавиш. Как же это ускоряет замену формул на значения. Вот бы такое в Excel (да, окно "Специальная вставка" там можно вызвать Ctrl+Alt+V, но потом нужно еще выбрать "Значения" в нем). Кстати, сочетание работает во всех приложениях Google Диска — Презентациях, Документах. На Mac: ⌘ + Shift + V.

/copy — вставьте слово "copy" после ссылки на Таблицу (или другой док Google Диска) и отправляйте ссылку тем, кому нужно создавать копию, а не пользоваться исходной таблицей — сразу будет открываться страница с кнопкой "Создать копию".

Хотите наглядно оформить дэшборд с отклонениями "план-факт" — используйте диаграмму "Сводка" (хотя это не совсем диаграмма) — в качестве диапазона данных используйте два значения, факт и целевое/прошлый период/другой базис для сравнения.
Можно настроить отклонения в % или абсолюте (см скриншот).

===
📕📗📘 Оглавление канала
Attached file
Read more
Google Таблицы 27 Aug, 11:55
УДАЛЯТОР / ДОБАВЛЯТОР
Следим за количеством пустых строк в выбранных Таблицах


Тормозит Таблица, а вы боитесь лезть в формулы и что-то оптимизировать?

Есть способ проще - физически удаляем пустые ячейки. Это поможет, ведь даже пустые ячейки имеют вес и при пересчёте формул на них требуется время.

Мы придумали для вас решение (таблица со скриптом), с помощью него вы сможете поддерживать нужное количество строк и столбцов:

Работает так:
1. заносите ссылки на Таблицы
2. по каждой Таблице указываете, сколько пустых строк / столбцов должно остаться
3. запускаете скрипт, скрипт открывает каждый лист каждой таблицы
4. удаляет столбцы и строки, учитывая запас, который вы задали
5. если потребуется – то добавляет строки / столбцы до запаса

Хорошей практикой будет поставить скрипт на регулярное выполнение на каждую ночь. Так вам не придётся его запускать руками и у вас будет автоматический механизм, который будет следит за правильным количеством ячеек в ваших Таблицах.

===
📕📗📘 Оглавление канала
Read more
Google Таблицы 26 Aug, 09:59
Всем привет 🙌

Ребята из ProductStar вместе с Miro, Booking.com, Skyeng и др. известными вам компаниями собрали курс, который поможет вам вырасти в Senior Product & CPO.

Спикеры курса:
— Михаил Карпов, ex-Product Director в Skyeng
— Алексей Авдей, Site Director в Сбербанк
— Илья Красинский, CEO в Rick.ai
— и многие другие профессионалы продакт-менеджмента 😉

🔸 Вы изучите Hard Skills Senior продакта: приоритизация, Unit-экономика, CustDev и т.д.

🔸 Прокачаете Soft Skills Senior продакта: работа с командами разработки, аналитики и маркетинга, защита своих идей и проч.

🔸 Научитесь управлять командой и ресурсами, а также изучите лайфхаки быстрого прототипирования и проверки гипотез!

До конца августа стоимость курса составляет всего 14600 р!

Узнать подробнее о курсе 👉 https://bit.ly/2YxeLQ0

Но это ещё не всё! Кроме «Level Up: Senior Product & CPO», в летней распродаже участвуют более 14 онлайн-курсов со скидками до -70% 🔥

Не упустите такой шанс, оставляйте заявочку 👉 https://bit.ly/2YxeLQ0

#реклама
Read more
Google Таблицы 24 Aug, 09:55
ПОЛЬЗОВАТЕЛЬСКИЕ ФУНКЦИИ
Выводим все листы Таблицы и диапазоны с данными

/**
* Выводим листы и диапазоны
* @customfunction
*/
function листы_диапазоны() {

return
[['Лист', 'Диапазон с данными', 'Границы листа']].concat(SpreadsheetApp.getActive().getSheets()
.map(g => [g.getName(), g.getDataRange().getA1Notation(), g.getRange(1, 1, g.getMaxRows(), g.getMaxColumns()).getA1Notation()]))
}

Как работает? Положите код функции в редактор скриптов Таблицы, после введите в ячейку название функции листы_диапазоны.

Для принудительного обновления – оберните название функции в ЕСЛИ (IF) и добавьте ссылку на чекбокс. Активируете чекбокс – функция пересчитается.
=IF(A1; листы_диапазоны())

🔥Другие функции из гифки:
– выводим имя книги
– текущий лист и ссылка на него
– все листы книги и ссылки на них
https://pastebin.com/8Rr3fgZc

===
📕📗📘 Оглавление канала
Read more
Google Таблицы 17 Aug, 09:39
ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное

Представьте – вам нужно зайти на 200 однотипных веб-страниц и достать из них адрес объекта или как в нашем примере – ссылку на изображение.

Так себе перспектива, да?

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

Как всё работает:
0) копируйте Таблицу
1) заполните столбец A: ссылки, которые надо обработать
2) заполните столбец B: регулярное выражение, которое извлечет из кода веб-страницы нужное
3) запускайте!

Скрипт проходит строки по одной, вставляя данные и включая чекбокс, обрабатываются только строки с выключенным чекбоксом.

Про регулярку:
Ссылка, которую достаём, в коде страницы выглядит так:
data-large="/upload/d5/b4/19/37/d.jpeg" class="my-foto">

Нам нужно всё, что после data-large=" и до "

Регулярка будет такой:
data-large="(.+?)"


regex101.com/ – проверить выражение
rexegg.com/regex-tools.html – научиться

💊💡(скрипты, боты и отчёты на заказ)
Read more
Google Таблицы 14 Aug, 14:26
QUERY по дате, представленной в виде текста

Допустим, у вас есть столбец с текстовыми строками, содержащими даты (в виде 05.06.2020, далее до 15.06.2025 или чего-то подобного)

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

Итак, вам нужно доставать все строки, в которых встречается дата за определенный месяц и год (числа любые).
Будем вводить в ячейке A1 дату, а извлекать из нее текстовую строку вида "MM.ГГГГ" будем следующей конструкцией:

ТЕКСТ(МЕСЯЦ(A1);"00" )&"." &ГОД(A1)

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

=QUERY(диапазон_с_данными;"select * WHERE G contains '"&ТЕКСТ(МЕСЯЦ(A1);"00")&"."&ГОД(A1)&"'";1)

Таблица с примером
Read more
Google Таблицы 10 Aug, 10:33
ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске

Хотите добавить к пятидесяти рабочим файлам нового сотрудника как редактора или оперативно убрать сотрудника, который не прошёл испытательный срок и вообще не подходит по духу и грозится все удалить?

Сегодняшнее решение (тут) поможет вам с этим разобраться.

Советы и правила:
– Скрипт работает и с файлами и с папками
– Можно указывать как URL, так и ID файла или папки
– Если даёте доступ к папке, то он распространится на вложенные в папку папки и файлы
– После обработки всей строки скрипт добавит дату и время в столбец E, при следующем запуске скрипт начнёт с первой строки БЕЗ даты и времени (это сделано для того, чтобы обрабатывать много файлов)

Поэтому, если нужно обработать строку – очистите
столбец E
– Чтобы добавлять / удалять сотрудников вы должны быть по крайней мере редактором

===
а еще уменьшаем время, которое вы тратите на рутину (скрипты, боты и отчёты на заказ)

📕📗📘 Оглавление канала
Read more
ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней

Нерадивые сотрудники заходят в старые сделки и меняют контрагента или сумму случайно? Или специально, пытаясь запутать вас и выгадать что-то для себя? 🐽

Мы создали скрипт, который поможет в таких случаях.

Работает он так – с помощью функции findRow находим номер последней строки со вчерашней датой, далее с помощью delProtection удаляем защищенный диапазон, далее с помощью createProtection создаём диапазон начиная со второй строки листа и по последнюю строку со вчерашней датой.

Таблица со скриптом здесь, код отдельно здесь, чтобы все работало у вас – уносите код к себе в Таблицу и заполните 4, 5, 6, 11 строку в редакторе скриптов: это название рабочего листа, емейлы, которые добавляем в закрытый диапазон, название защищенного диапазона и номер колонки с датой на вашем листе.

==
Разработка Таблиц, скриптов и ботов (от 10к рублей)

📕📗📘 Оглавление канала
Read more
ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах

Друзья, выкладываем наше готовое решение для замены одних значений на другие в выбранных Таблицах.

Краткая инструкция:
1) делайте копию Таблицы со скриптом, чтобы унести её к себе на диск
2) заполняйте словарь (значения, которые мы ищем и значения, на которые мы будет их заменять) и вносите ссылку на Таблицу со словарём и название листа в ячейки a3, b3
3) в диапазоне a5:c укажите таблицы, листы и диапазоны, в которых требуется произвести замену
4) в диапазоне d5:d отметьте чекбоксами те строки, которые требуется обработать сейчас
5) и запустите скрипт нажатием на кнопку

==
Скрипт создан при поддержке lesollp.ru, спасибо им,

а еще мы пишем любые скрипты, создаём телеграм ботов на заказ (тыц)

📕📗📘 Оглавление канала
Read more
​​С помощью UX-анализа можно понять, как пользователь взаимодействует с вашим сайтом — с какими трудностями сталкивается, почему уходит с него.

Но как научиться правильно использовать данные, полученные в ходе этого анализа?

Узнаете 5 августа в 19:00 (мск) на бесплатном вебинаре ProductStar «Введение в UX-анализ: как создать прототип сервиса с минимальными знаниями в дизайне и коде»!

👨‍🏫 Вместе с Артемом Денисовым, аналитиком-проектировщиком, ex-системным аналитиком «Альфа-Банка» и «Райффайзенбанка», мы разберем:

— Как визуализировать гипотезы с помощью Figma, Axure и веб-аналитики,
— Как протестировать сервис на пользователях без затрат времени программистов,
— Как эффективно реализовать выигрышную гипотезу.

На вебинаре ребята из ProductStar разыгра.n 4 сертификата номиналом 10.000₽ на свои курсы.

🔥 Участие бесплатное, но регистрация обязательна.

Зарегистрироваться на вебинар 👉 @ProductStarAnalyticsBot
Read more
Google Таблицы 23 Jul, 10:55
Помогаем маме: Google Таблица для отправки посылок Amazon и Ebay

Друзья, мама нашего подписчика Антона активно торгует на Amazon и Ebay. Ежедневно ей приходится отправлять много посылок.

Антон создал решение для отправки и по нашей просьбе рассказал о нём в статье на медиум