57857
Работа в Google Таблицах. Кейсы, решения и угар. контакты: @namokonov @r_shagabutdinov @IT_sAdmin оглавление: goo.gl/HdS2qn заказ работы: teletype.in/@google_sheets/sheet_happens чат: @google_spreadsheets_chat
Переносим форматирование с одного листа на множество
Друзья, представьте ситуацию - у вас много одинаковых листов в Таблице и вдруг вы решили на все листы добавить правила условного форматирования и по разному раскрасить столбцы и строки.
Что делать? Можно обойти каждый лист и вставить форматирование туда.
А можно всё сделать на одном листе (у нас он называется "шаблон") и воспользоваться нашим скриптом.
Скрипт в комментариях ↓
Как получить первые или последние 3 (или сколько угодно) строк из диапазона с данными?
📌 Выводим 3 первые строки :=ARRAY_CONSTRAIN(A1:E9;3;COLUMNS(A1:E9))
📌 Выводим 3 последние строки:=CHOOSEROWS(A1:E9; SEQUENCE(1;3;ROWS(A1:E9);-1))
📌 А создать диапазон диких, но симпатичных привидений нам помогла протянутая формула =REPT("👻";ROW())
Задача из нашего чатика: @google_spreadsheets_chat, скоро 5000 участников, залетайте!
PS Вариантов этих формул может быть множество - напишите в комментариях свои, а мы добавим в Таблицу
Таблица
Сделать случайную сортировку диапазона можно и из интерфейса. Гифка от Михаила Смирнова.
В нашем чате задали вопрос, а зачем вообще сортировать массив данных в случайном порядке?
Представьте: у вас есть Таблица с тысячей ссылок на звонки колл центра. Вы хотите взять случайные 25 звонков для проверки. Перемешиваем массив и берём 25 строк сверху (ну или снизу) 😎
Как использовать таблицы по максимуму, чтобы экономить время на сложных вычислениях? Изучите популярные инструменты на бесплатном мини-курсе по экспресс-погружению в Excel и «Google Таблицы»: https://epic.st/MQ6Td
Вы научитесь:
— Создавать сводные таблицы и делать интерактивные отчёты
— Пользоваться функциями поиска и обрабатывать большие объёмы данных
— Оперативно суммировать значения по заданным критериям
— Отображать данные в удобном для вас виде
— Создавать «умные» таблицы, быстрее работать с информацией, упрощать фильтрацию и поиск данных
Как проходит мини-курс:
— Смотрите видео в удобное время. Мы сделали акцент на практику, поэтому в роликах много подробных примеров
— Выполняете практическое задание после каждого видео
— Получаете полезные материалы с лайфхаками и горячими клавишами
☝️ А в конце вас ждёт прямой эфир с сертифицированным экспертом по Excel Ренатом Шагабутдиновым. Он поделится лайфхаками и ответит на все вопросы.
📎 Получите доступ к курсу в пару кликов и чек-лист «Основные возможности Excel» в подарок!
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
Статьи про Таблицы и скрипты от Михаила Смирнова за четыре года
Друзья, делимся подборкой статей от прекрасного Михаила, которые выходили на нашем канале. От самой первой и до последней.
Google Data Studio на примере небольшой задачи
🚜 Собиратыр-тыр-тыр 🚜
FLATTEN 2: другие недокументированные функции Google Sheets
FLATTEN 2: Other Undocumented Google Sheets Functions
Получение курсов валют в Google Sheets
Исторические данные индекса Московской Биржи (IMOEX) в Google Sheets
Программно даём доступ для IMPORTRANGE к другой таблице
Массово удаляем временные фильтры через Google Sheets API
Как проредить колонки и столбцы диапазонов в Google Sheets
Google Apps Script Library — Библиотеки в Гугл Скриптах
Google Apps Script — Сам себе триггер в библиотеке
Google Apps Script Library — Сам себе триггер в библиотеке (короткая версия)
Google Apps Script — Custom Functions — Полезные Мелочи
Google Sheets — Немного про IMPORTRANGE()
Google Apps Script — Публикация листов
Google Sheets — Время расчёта формулы
Новые функции в Google Sheets (2023-02-02)
PS от автора: имейте в виду, что некоторые из решений теперь делаются проще (например, через lambda-функции), но на момент написания, автору решения казались неплохими
Col1, Col2, ColN в QUERY
Друзья! А теперь в функции QUERY можно сразу обращаться к колонкам как Col1, ColN, где N - номер колонки в диапазоне, который вы в QUERY передаёте.
Напомним, ранее можно было обращаться к столбцам только как A, B, C и так далее, когда источником (первым аргументом) был обычный диапазон.
А ColN — только когда источником выступал массив (например, несколько диапазонов, объединенных через фигурные скобки, VSTACK, или функция IMPORTRANGE, и т.д.)
Как это использовать? Вот так, например.
Таблица для расчета себестоимости на KazanExpress
Друзья, наша читательница Гузель сделала Таблицу с расчетом рентабельности закупки товаров, Таблица помогает ей определять самые выгодные для продажи товары.
Статья с рассказом
Таблица
🔥 Друзья, будут идеи, что добавить к Таблице - напишите в комментариях
150 уникальных пользователей @zadavai_vopros_bot за 4 дня – это здорово, спасибо за то, что участвуете в тестировании!
Помимо общения и создания кода наш GPT-бот может делать и необычные вещи, к примеру, отвечать на отзывы на товары (смотрите на скриншот).
А в режиме "диалог" вы можете отредактировать ответ, который получите, например, попросить ИИ сделать отзыв короче, убрать мат или добавить предложение о возмещении ущерба 😎
Ии-шечка, @zadavai_vopros_bot
Друзья, мы сделали ChatGPT телеграм бота, на вопросы бот отвечает с помощью GPT 3.5, картинки рисует с помощью DALL·E 2, контекст поддерживает.
Можно попробовать, первые десять запросов - бесплатны.
Если бот будет отвечать долго - потерпите, мы поправим это в первую очередь.
Чтобы использовать бота после 10 бесплатных запросов -> 500 рублей / месяц по любым нашим донатным реквизитам и напишите в бота "я оплатил".
Пока возьмем до 20 подписчиков, чтобы посмотреть, как все будет работать, такой ламповый тест для нашего канала.
и для тех, кому сложно визуализировать текст — гифка с демонстрацией работы :)
Читать полностью…
Мир бизнеса стремительно меняется, и для успешного старта в карьере важно быть в курсе актуальных трендов и инструментов. Начните погружение в бизнес-аналитику на бесплатном интенсиве Skillbox «3 дня в роли бизнес-аналитика: основы профессии и первый кейс!»
Успейте зарегистрироваться: 👉 https://epic.st/dei1m
Сразу после регистрации получите два чек-листа «Бизнес-аналитик: начинаем карьеру с нуля» и «Ключевые качества и навыки успешного бизнес-аналитика».
За 3 дня вы:
✅ узнаете, чем занимаются бизнес-аналитики и почему они особенно востребованы в период нестабильности
✅ научитесь работать с интеллект-картами и проектировать диаграммы процессов
✅ поймёте, как собирать и обрабатывать данные, оптимизировать бизнес-процессы
Спикер интенсива — Антон Антипин, основатель и генеральный директор компании Business Set, сертифицированный эксперт в области моделирования бизнес-процессов.
🎁 Не упустите возможность получить подарки! Подключайтесь к прямым эфирам с 31 июля по 2 августа в 19:00 по московскому времени.
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
Отделяем с помощью условного форматирования блоки друг от друга
Друзья, посмотрите на скриншот – в первом столбце у нас название группы.
Допустим, для работы с Таблицей вы хотите отделить группы друг от друга визуально:
1) Выделяем наш диапазон
2) Условное форматирование > создать правило > правило форматирования формулой
3) Вводим формулу =ISODD( MATCH($A2;UNIQUE($A$2:$A$18);0))
4) Выбираем цвет и вуаля, всё работает
Формула простая, мы делаем уникальный список с названиями групп UNIQUE($A$2:$A$18) и ищем номер вхождения каждой группы в этот список, номер будет 1,2,3,4,... Далее мы проверяем номер на нечетность (можете проверять на четность с помощью ISEVEN) и формула выводит true для каждой нечетной группы и условное форматирование окрашивает эти строки.
Таблица, в которой всё работает (можно сделать копию и посмотреть)
---
⭐️ ЗАКАЗАТЬ РАБОТУ
⭐️ Оглавление канала: ты-дыц
Средневзвешенное по условию
Чтобы посчитать среднее по каким-то условиям, подойдет СРЗНАЧЕСЛИМН / AVERAGEIFS.
Но это среднее арифметическое. Оно не всегда подходит. Например, если вам нужно посчитать среднюю цену, правильнее будет учесть "веса" — число проданных по каждой цене товаров или количество отработанных по каждой цене часов.
Для средневзвешенного используем функцию AVERAGE.WEIGHTED. В ней сначала указывается ссылка на показатель, а затем — на веса.
Ну а если нужно считать не все подряд, а только значения по какому-то условию, то аргументы для функции можно получить с помощью функции FILTER.
Ссылка на таблицу с примером
Ура! Теперь можно уничтожить 170 ненужных фильтров одним кликом без скрипта!
В Google Таблицах не только разделили кнопки фильтра (обычный на листе, видный всем) и режима фильтрации (filter views, представления, которые не скрывают строки на листе и могут сохраняться) какое-то время назад, но и добавили опцию для удаления всех фильтров.
Это пригодится, если вы (и ваши коллеги) забываете удалять ненужные фильтры. Ведь если создать фильтр и просто его закрыть, он сохранится под названием ФильтрN, и в файлах с большим числом пользователей таких фильтров мы наблюдали и по 150 штук.
Теперь без скрипта: режимы фильтрации на панели инструментов — Удалить все фильтры.
А для профилактики этого безобразия: если фильтр нужен, меняем имя на что-то содержательное ("Лютые Продажи" или "Маша НЕ УДАЛЯТЬ" или что-то подобное), а если не нужен, то нажимаем на шестеренку, а не крестик, и удаляем.
Подробнее про фильтра можно прочитать тут:
/channel/google_sheets/337
А посмотреть тут:
https://www.youtube.com/watch?v=kHN5sIFLIjw
Как сделать отчет понятным? 4 совета от эксперта
Чем проще понять ваш отчет и сделать по нему выводы, тем выше вас ценит руководитель. В этом помогает визуализация данных.
В Клубе анонимных аналитиков эксперт Алексей Колоколов делится бесплатными рекомендациями с теми, кто хочет сделать отчеты понятными и наглядными для управленцев.
🔹 Как показать интерактивный дашборд в PowerPoint
🔹 Топ-7 проблем с данными в отчетах
🔹 Чем отличаются разные виды отчетов
🔹 Как выбирать диаграммы по смыслу данных
Новые советы и лайфхаки по визуализации выходят на канале регулярно. Бывают бесплатные марафоны и мастер-классы для тех, кто хочет приносить больше пользы бизнесу. Подписывайтесь! /channel/analyst_club
#реклама
Если вы когда-либо делали презентации — вам такое знакомо
Вы открываете документ, начинаете писать — первые пару слайдов всё идёт неплохо. Но потом начинается водопад сомнений: «А какой шрифт выбрать?», «Эта анимация не чересчур?». «А какого цвета буквы должны быть на этом фоне?», «Да как здесь уместить этот огромный график?!»
И вот проходит 2 часа, вы как-то всё доделываете, начинаете пересматривать и понимаете: плохо. Но на переделку времени нет — несёте как есть.
Если вас достал такой сценарий и вы хотите научиться делать эффектные, понятные и красивые презентации — и делать их быстро, — заглядывайте на курс «Мастер сильных презентаций: PowerPoint и Google Slides».
Вас ждёт много практики в PowerPoint и «Google Презентациях», пак продающих шаблонов в подарок и доступ к материалам навсегда. А начать зарабатывать на презентациях вы сможете уже через 1 месяц после старта.
Подробности: https://goo.su/JapW
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
@zadavai_vopros_bot для анализа текста
А нашего GPT-бота можно использовать и для анализа текста.
Отправляем текст
— просим сделать выжимку
— или вывести ключевые слова
— или даже перевести текст на другой язык... попробуйте свой вариант, у вас есть 10 запросов бесплатно (а кто хочет обнулить счетчик, пишите @namokonov)
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы
Сортировка списка в случайном порядке
Чтобы решить такую задачу, будем использовать SORTN и сортировать этой функцией наш список по другому столбцу — виртуальному, состоящему из случайных чисел. Сгенерируем его функцией RANDARRAY. Единственным аргументом будет число элементов в нашем списке — мы его считаем функцией СЧЁТЗ / COUNTA.
Чтобы все работало для любого числа элементов в списке, диапазон задаем не явно (A2:A30, допустим), а через СМЕЩ / OFFSET по тому же самому числу элементов в списке.
OFFSET(первая ячейка;нулевой отступ по строкам; и по столбцам; число элементов, найденное через COUNTA) — выдаст ссылку на диапазон от первой ячейки до последней заполненной.
Далее сортируем его по столбцу, сформированному RANDARRAY.
Так как у нас в формуле трижды используется число элементов (COUNTA(диапазон)) — то мы его заменим на N с помощью функции LET, дабы не повторяться.=let(N; COUNTA(диапазон); SORTN(OFFSET(первая ячейка;;;n);n;;RANDARRAY(n);1))
Таблица с примером
Что мы вам посоветуем из подборки (ну кроме просмотра фильма "основной инстинкт")
Новые функции Таблиц: Новые функции в Google Sheets (2023-02-02)
Про курсы валют (кстати, cbr опять можно достать с помощью IMPORTXML): Получение курсов валют в Google Sheets
Как дать расшарить доступ к IMPORTRANGE с помощью скрипта: Программно даём доступ для IMPORTRANGE к другой таблице
Про библиотеки в GAS: Google Apps Script Library — Библиотеки в Гугл Скриптах
---
⭐️ Оглавление канала: ты-дыц
Умеете строить таблицы? Станьте востребованнее за полгода!
Расти в карьере более чем реально: нужно перейти в IT-сферу, которая постоянно развивается и предлагает лучшие условия по зарплате. Если работаете с цифрами, идеальная профессия для старта в IT — аналитик данных.
Аналитики данных остаются крайне востребованными: только на hh сейчас — более 15 000 вакансий. Обучиться самому важному можно всего за 6 месяцев на курсе «Аналитик данных» от Академии Eduson. В программе — 32 бизнес-кейса и интерактивные тренажеры для максимального погружения.
Курс постепенно проведет вас от продвинутого владения Excel к работе с наиболее востребованными инструментами: SQL, Python и Power BI.
Вас будут обучать профи: основатель аналитического бюро Datalatte, директор по аналитике «Работы.ру», продуктовый аналитик SberDevices. Спикеры параллельно преподают в ведущих вузах России — и понятно объясняют самые сложные темы.
Вводите промокод "GOOGLE_ТАБЛИЦЫ" и получите максимальную скидку 60% → https://www.eduson.tv/~-hPfXg
#реклама
Работа с отчётами, таблицами и графиками отнимает кучу времени? Нужно отчитаться за работу перед начальством в виде ПРЕЗЕНТАЦИИ, а выделить самое важное и наглядно показать результат не получается? А что, если от этого будет зависеть твоя карьера?
Приходи 2 августа в 16:00 на мастер-класс по отчётам и таблицам в PowerPoint или Keynote от академии презентаций Bonnie&Slide.
Научись создавать понятные и эффективные отчёты за счет правильной компоновки данных и шаблонов, чтобы получить повышение на работе:
- Без помощи дизайнера
- По брендбуку или без
- Не тратя более 5 минут на слайд
При регистрации ты получишь "Гайд 10 способов как сделать убойную и понятную презентацию, если много данных".
#реклама
В какой сфере вакансий становится только больше, а зарплаты растут в геометрической прогрессии?
Самая близкая к этому сфера — Data Science. Тут и правда много платят, а задачи разнообразные, но стать таким специалистом не легко, нужно много усилий, чтобы выделиться среди новичков без опыта.
Как начать свой путь в Data Science и получить первую работу? Расскажем без прикрас на бесплатном вебинаре.
Ведущие вебинара:
▪️Анатолий Карпов
— основатель школы Data Science и разработки karpovꓸcourses, работал ведущим аналитиком в Mail.Ru, ВКонтакте и JetBrains.
▪️Оксана Васильева
— руководительница карьерного центра karpovꓸcourses, трудоустроевшего более 1500 учеников
На вебинаре вы узнаете:
- Как перейти в Data Science
- Как понять, какую профессию выбрать: аналитика данных или машинное обучение
- Как пробиться на рынке и получить первый оффер
- С чего начать погружение в профессию
Вебинар пройдет 8 августа в 19.00.
Постройте карьерный трек в Data Science вместе с нами!
Регистрируйтесь по ссылке!
#реклама
Пишем в @zadavai_vopros_bot простой скрипт на Google Apps Script, запускаем и следующим сообщением его редактируем.
Друзья, гифка на две минуты со звуком.
PS! С помощью GPT действительно можно писать скрипты на GAS, а также задавать про программирование, но имейте ввиду, что не всегда то, что вернёт ИИ будет правдой. Поэтому не слепо верим, а пробуем, читаем документацию и пытаемся разобраться.
Как выстроить финансовый учет в Гугл-таблицах
Контролировать деньги малого бизнеса можно без дорогих учетных и аналитических систем. На старте достаточно бесплатного онлайн-инструмента. Для этого нужно:
🔹 Настроить 3 формы учета для грамотной финансовой отчетности.
🔹 Определить 10 главных финансовых метрик для управления бизнесом.
🔹 Научиться правильно и понятно доносить данные до управленца.
Все это вы сделаете на новом курсе Института бизнес-аналитики. Выстроите управленческий учет финансов на Гугл-таблицах и визуализируете данные для руководителя. То есть освоите и закрепите навыки финансового менеджера.
Это не всё! Вы научитесь выгружать финансовые данные из 1С, обрабатывать их и строить для управленца интерактивные дашборды в Power BI. Легко и быстро, с помощью понятных шаблонов. +100 к профессиональной ценности.
В открытой продаже курса еще нет, но доступен предзаказ со скидкой 50%. Предзаказы принимаются только до вечера пятницы, 28 июля.
Смотрите программу на сайте Института бизнес-аналитики и резервируйте место вдвое дешевле! https://alexkolokolov.com/ru/study/1c
#реклама
Импорт только выбранных столбцов из другой таблицы
Если вы хотите выбирать в выпадающем списке заголовки тех столбцов из другой таблицы, которые хотите импортировать, а не грузить все подряд, то можно сделать следующее:
1. Загрузить заголовки из источника (ТРАНСП / TRANSPOSE — чтобы в столбик) на отдельный лист:=ТРАНСП(IMPORTRANGE("ссылка на источник";"Название_листа!1:1"))
2. Настроить проверку данных на основе этого списка — чтобы выбирать в выпадающем списке заголовки
3. Далее нужно сделать формулу, которая в общем виде будет делать следующее: импортировать (IMPORTRANGE) только выбранные столбцы (QUERY). Запрос для QUERY будет вида Select ColN, ColM, ... , где N,M и так далее — порядковые номера нужных нам столбцов, определяемые функцией ПОИСКПОЗ / MATCH
Ловите таблицу с двумя вариантами формул и пользуйтесь на здоровье 🤠
Превращаем лист Таблицы или в xlsx / pdf / csv
Друзья, напоминаем вам про наше абсолютно бесплатное решение.
С его помощью вы сможете конкретный лист Таблицы превратить в XLSX (файл сразу скачается локально) и далее уже руками скопировать полученный файл и отправить поставщику / заказчику / коллегам по работе.
Файл либо сразу скачивается на ваш компьютер, либо сохраняется на Google Диск, в зависимости от скрипта, который вы запустите.
PS И только вчера обсуждали в нашем чатике решение
🚀 Освойте best practices по моделированию архитектуры предприятия на онлайн-курсе «Archimate» в OTUS.
📚Практический курс будет полезен архитекторам, тимлидам, аналитикам, разработчикам и всем, кто хочет научиться читать и моделировать на языке Архимейт..
Оставьте заявку и получите:
— доступ к записям вебинаров и предложение по спеццене на курс;
— крутейший Справочник по связям в Архимейт от руководителя курса.
👉Связи в языке Архимейт - один из самых сложных элементов. Они не читаются интуитивно, и без знаний назначений связей будет сложно разобраться. Получите шпаргалку (справочник), которую можно использовать при чтении назначений связей и правильного чтения схем Архимейт!
💲Курс доступен в рассрочку.
Нативная интеграция. Информация о продукте www.otus.ru
#реклама
Хотели бы попробовать себя в новой профессии? Окунитесь в мир бизнес-анализа, освоив базовые инструменты для понимания данного направления на бесплатном мини-курсе.
Вы научитесь собирать, обрабатывать и анализировать информацию, связанную с работой бизнеса.
Познакомитесь с продакт- и проджект-менеджментом и узнаете, как применять навыки для управления продуктами.
Зарегистрируйтесь на буткемп прямо сейчас и получите чек-лист «Проджект VS продакт: выберите, на чьей вы стороне»: https://epic.st/gMe9jg
Мини-курс состоит из видеоматериалов в записи. Длительность каждого видео — от 30 минут до полутора часов. Теорию сможете закрепить на практике, выполнив практическую работу после каждого видео. По окончании курса будет живой эфир со спикером, который даст обратную связь по проектам.
За время обучения вы:
📌 Составите план по разработке сервиса для доставки еды: определите сроки и ответственных, риски и уровень расходов
📌 Создадите план проекта, опишете его цель и ожидаемый результат, просчитаете бюджет и составите 30 задач
📌 Создадите схему бизнес-процессов и опишете их логику
📌 Проанализируете данные, построите и оформите сводную таблицу в Excel, чтобы оценить динамику и структуру покупок
Вы сделаете 4 проекта по разным направлениям, пополните ими своё портфолио и поймёте, какие навыки стоит развивать, чтобы устроиться по новой специальности или получить повышение.
Разобраться в теме помогут эксперты:
— Коммерческий директор Upmarket с 15-летним опытом в digital-сфере, управлении бизнесом, маркетинговых и HR-коммуникациях Мария Зубарева
— Генеральный директор Business Set с 21-летним опытом в бизнес-аналитике Антон Антипин
— Сертифицированный эксперт по Excel (MOS Excel Expert) Ренат Шагабутдинов
🎁 Подарки: вы получите 7 чек-листов, сертификат на скидку 10 000 рублей, персональную карьерную консультацию, а также электронную книгу Скотта Беркуна «Сделано. Проектный менеджмент на практике».
Записывайтесь и открывайте доступ к курсу в пару кликов!
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
Группировка текстовых элементов в сводной
У вас есть данные по отделам, а вам нужно сгруппировать несколько отделов в департаменты, причем только в сводной, а не в исходных данных?
Выбираем несколько значений (с зажатой клавишей Ctrl) — щелкаем правой кнопкой и нажимаем на загадочную надпись "Сгруппировать сводные таблицы" (Create pivot group)
И теперь в строках сводной будет два уровня! Выбранные элементы сгруппируются в один на верхнем уровне. Можно и другие элементы тоже далее сгруппировать.
Рассчитываемое поле в сводных таблицах
Срезы в сводных таблицах
Сводная по “бесконечному” количеству строк
У вас в сводной таблице есть поле (или несколько) и в строках, и в столбцах. И вы добавляете еще и несколько полей в область значений.
Они автоматически группируются в столбцах, то есть каждый элемент в столбцах теперь будет делиться на несколько столбцов — для каждого поля в области значений.
Но их можно переместить в область строк, если это поможет сделать структуру вашей сводной более компактной. Для этого выберите "Строки" в поле "Значения как", которое появляется в редакторе сводной таблицы, когда у вас более одного поля в области значений.
Рассчитываемое поле в сводных таблицах
Срезы в сводных таблицах
Сводная по “бесконечному” количеству строк
