Обложка канала

Google Таблицы. Страница 8

17221 @google_sheets
Открыть

#GoogleТаблицы — это аналог #Excel с веб-интерфейсом и возможностями для удобной совместной работы. Они похожи, и все же в Таблицах есть свои особенности, функции, плюсы и минусы. Работать в Google Таблицах просто и мы покажем как.

  • Google Таблицы

    Оглавление Таблицы скриптами Друзья, привет! В первом посте 2023 года решим классическую проблему – выведем список всех листов Таблицы вместе с ссылками на каждый лист. Базовый скрипт. function sheets() { //текущая таблица const ss = SpreadsheetApp.getActive(); //определяем ссылку на текущую таблицу const ssUrl = ss.getUrl(); //проходим по всем листам Таблицы в цикле и формируем массив [ссылка на лист, название листа] return ss.getSheets().map(sheet => [`${ssUrl}#gid=${sheet.getSheetId()}`, sheet.getName()]); } Скрипт будет работать как пользовательская функция, просто вставьте =sheets() на лист в любую ячейку, как на скриншоте. Хак, чтобы оглавление, которое выводится обновлялось почаще – передаём в функцию любой аргумент из листа, =sheets(k1), например. Вставляем оглавление как значения при открытии Таблицы. Добавляем код ниже. function onOpen() { const arr = sheets(); const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName('оглавление') || ss.insertSheet('оглавление'); sh.clearContents(); sh.getRange(1, 1, arr.length, arr[0].length).setValues(arr); } Вставляем оглавление гиперссылками (будет один столбец) при открытии Таблицы function onOpen() { const arr = sheets().map(f => [SpreadsheetApp.newRichTextValue() .setText(f[1]) .setLinkUrl(f[0]) .build()]); const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName('оглавление') || ss.insertSheet('оглавление'); sh.clearContents(); sh.getRange(1, 1, arr.length, arr[0].length).setRichTextValues(arr); }

  • Google Таблицы

    Ведёте аналитику в Google таблицах, но их функционала уже не хватает для эффективной работы? На курсе «Аналитик данных» от школы karpovꓸcourses за 5 месяцев вы научитесь всему необходимому для решения и автоматизации ваших рабочих задач. Здесь вы не только изучите теорию, но и на практических кейсах отточите навыки работы со всеми актуальными инструментами для анализа данных. В программу курса входят: – Python и SQL – Git и Airflow – Теория вероятностей – Статистика и A/B-тесты – Продуктовая аналитика – Визуализация данных в Tableau Обучение построено на реальных задачах индустрии и проходит в формате буткемпа, когда максимум знаний даётся за минимальный срок. Преподаватели — специалисты с опытом работы в топовых IT-компаниях, таких как VK, Яндекс.Go и Jetbrains. Переходите по ссылке и записывайтесь на курс до 11 января — по промокоду GOOGLEDA36 вы получите скидку 10% на полную оплату курса. Кстати, на сайте можно пройти бесплатную демоверсию. #реклама

  • Google Таблицы

    Друзья, с наступающим вас! Посты, которые вспоминали в нашем чате (@google_spreadsheets_chat) чаще всего: >> Крутейшая таблица с подсказками про query >> Памятка по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах) >> Условное форматирование. Выделяем дубликаты и не только >> Избранные посты нашего канала >> Как посчитать >> Памятка с советами, позволяющими ускорить работу документа >> Памятка по условиям, с помощью которых можно отобрать (для функции SUMIFs/СУММЕСЛИМН, например) пустые и непустые ячейки >> СОБИРАТОР 4.0 >> Важный скрипт. Связанные выпадающие списки из кэша >> Считаем сумму по каждой строке / столбцу в формуле массиве >> Народный Telegram бот❗️ Отправляем сообщения прямо из Таблицы >> OR / AND в функции FILTER >> ОТПРАВЛЯТОР 2.0 Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию >> Объединяем любое количество диапазонов Таблицы в одной функции >> ​​ARRAYFORMULA для автоматической нумерации списков (счетчик) >> ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты >> SUMIF для нескольких условий в формуле массива. Объединяем диапазоны с помощью амперсанда (&) >> ​​В правиле условного форматирования можно использовать данные другого листа.

  • Реклама

  • Google Таблицы

    Как делать полезные для бизнеса дашборды: 5 советов от практика Интерактивный дашборд помогает руководителю быстро делать выводы и принимать решения. Но только если он разработан правильно. Директор Института бизнес-аналитики Алексей Колоколов рассказал, как сделать дашборд понятным и наглядным. 1️⃣ Размещайте элементы диаграмм так, чтобы понять данные было проще. Например, легенду — над диаграммой. 2️⃣ Не перегружайте виджет кучей мер, выбирайте две важных. Остальные добавляйте во всплывающие подсказки. 3️⃣ Избегайте чрезмерной плотности диаграмм — это делает дашборд «тяжелым» и усложняет восприятие данных. 4️⃣ Не меняйте оси для одного и того же показателя, если он используется на нескольких диаграммах. 5️⃣ Ну и главное: выбирайте диаграммы не «для красоты», а по смыслу ваших данных. В своем Клубе анонимных аналитиков Алексей Колоколов дает много рекомендаций для тех, кто работает с корпоративной отчетностью. Подписывайтесь и учитесь делать дашборды, которые станут надежным пультом управления бизнесом: https://t.me/analyst_club #реклама

  • Google Таблицы

    Лучшие посты - 2022 Всего мы опубликовали чуть более 100 постов за этот год. Их всегда можно найти в нашем оглавлении, ну а сегодня решили выбрать для вас пачку хороших постов. Это не топ по просмотрам или по перепостам. А то, о чем мы - по нашей субъективной оценке полезности - решили напомнить. Вдруг кто-то пропустил что-нибудь полезное или присоединился к нам по ходу года. Схватка двух ёкодзун. Сравнение Google Таблиц и Excel (ну ладно, это было перед НГ-2022, но все остальные посты точно из этого года) Видео: Пользовательские числовые форматы в Google Таблицах ВСТАВЛЯТОР: вставляем формулу / скрипт сразу же вставляет вместо неё значения Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела Проектная диаграмма - Условное форматирование ВПР-им с разных листов Sheets Api, берём данные Sheets Api 2, вставляем данные Достаём ссылки на фотографии товара из карточки WB Задача: посчитать количество значений (или что-то еще, не столь важно - мы рассмотрим на примере счета) в каждой строке одной формулой. JOIN / TEXTJOIN по каждой строке в новых реалиях Находим последнее значение с помощью XLOOKUP Сумма по строке в новых реалиях, c условием

  • Google Таблицы

    Аналитика личных финансов: марафон для себя, а не для работы Как навести порядок в личных финансах: 5 уроков от эксперта Новогодний подарок от Института бизнес-аналитики — бесплатный марафон «Аналитика личных финансов». Чтобы каждый, кто обычно делает отчеты для заказчиков, позаботился о себе. И своих деньгах. Марафон пройдет с 4 по 10 января. Вести уроки будет приглашенный эксперт по личным финансам. Вы научитесь: ▪️ распределять расходы не только по «шаблону в Excel» ▪️ заполнять свой бюджет за 1,5 часа в месяц ▪️ составлять финансовый план и модель семейного бюджета ▪️ анализировать личные доходы, расходы и накопления ▪️ визуализировать данные банковской выписки на дашборде Марафон подойдет как для новичков, так и для тех, кто уже пытался вести свой бюджет. Проверенные техники, понятное изложение уроков и практические задания для отработки новых навыков. Все это — бесплатно. Посмотреть программу и записаться на участие можно на сайте Института бизнес-аналитики: https://bit.ly/3WPWeKB #реклама

  • Google Таблицы

    Задача: считаем нарастающий итог до заданного месяца и за заданный год формулой. Дано: кривая и некрасивая выгрузка, где добавляются по 2 столбца на каждый месяц - деньги и штуки. Мы хотим указывать в ячейках год и месяц и получать нарастающий итог за соответствующий период (с начала года и до выбранного месяца). Как бы вы решали такую задачу? Вот вариант решения: =SUM(QUERY(TRANSPOSE(FILTER($F3:$CW3;REGEXMATCH($F$2:$CW$2;"Деньги.*"&$B$1)));"limit "&MATCH($B$2;{"Январь" ; "Февраль"; "Март"; "Апрель"; "Май";"Июнь";"Июль";"Август";"Сентябрь";"Октябрь";"Ноябрь";"Декабрь"};0))) Схематично: =SUM(QUERY(TRANSPOSE(FILTER(строка с данными;REGEXMATCH(Заголовки;"Фиксированная часть заголовка.*"Номер года из ячейки)));"limit "&MATCH(Выбранный месяц в ячейке;{Массив с месяцами, чтобы получить номер выбранного};0))) С помощью REGEXMATCH выбираем только столбцы с заголовком "Деньги" (или "Штуки") и выбранным годом, между годом и штуками добавляем любой текст (.*), чтобы все месяцы попали в выборку. Выборка формируется по этому условию с помощью FILTER. Дальше транспонируем (делаем массив вертикальным), и с помощью QUERY и кляузы limit в ней получаем первые N значений (N определяется по порядковому номеру месяца - просто через MATCH / ПОИСКПОЗ определяем, каким по порядку в массиве названий месяцев идет выбранный пользователем месяц). Ну и дальше суммируем это безобразие с помощью SUM / СУММ. Таблица с формулой

  • Google Таблицы

    Ежедневно компании собирают большие объёмы данных, чтобы оценить состояние бизнеса и сделать прогнозы. Их интересует динамика продаж, качество продукции, эффективность сотрудников, конкуренты и т.д. Но сами по себе — без анализа и интерпретации — эти данные бесполезны. Поэтому навыки обработки, анализа и визуализации данных высоко ценятся на рынке. По данным американского журнала Entrepreneur, 82% вакансий требуют свободного владения Excel, и таким специалистам платят на 17% больше. Освоить Excel и Google Таблицы можно на онлайн-курсе «Excel + Google Таблицы с нуля до PRO» от Skillbox. Всего за четыре месяца вы научитесь автоматизировать работу с помощью скриптов и макросов. Skillbox предлагает рассрочку всем пользователям платформы. Необязательно платить всю сумму сразу, вы сможете разбить её на небольшие платежи и каждый месяц понемногу инвестировать в своё будущее. Узнать подробнее о курсе и получить скидку до 60% в честь Нового года можно здесь: https://clc.to/l6W0zw #реклама

  • Google Таблицы

    Проверка данных стала наряднее! Теперь: — Правила настраиваются в боковой панели (как, например, условное форматирование). Боковая панель вызывается, как раньше, меню "Данные" (Data) — "Настроить проверку данных" (Data Validation). Или Alt-D + V. — Нажимаем Add Rule (Добавить правило), чтобы добавить новое правило, существующие правила видим в списке, можно наводить курсор на каждое и будет выделяться соответствующий диапазон. Удалить правило можно в его настройках, нажав Remove Rule, или по иконке с корзинкой в списке всех правил проверки. — Если настраиваем выпадающий список со значениями не из диапазона, а прямо в проверке данных, то больше не нужно перечислять значения через запятую — они вводятся в отдельных полях, чтобы добавить новое, просто нажмите Enter. — Вариантам в выпадающем списке (что из диапазона, что введенным в проверке) теперь можно присваивать цвета! Так что значения в ячейках выглядят более нарядно, если цвет несет какой-то смысл в ваших данных — легче считывать информацию.

  • Google Таблицы

    Прокачайте свои навыки в программировании и узнайте всё о профессии SQL-разработчика на бесплатном онлайн-интенсиве Skillbox. Подключайтесь к прямым эфирам с 26 по 28 декабря в 19:00 по московскому времени: https://clc.to/_XIfyg. ● Научитесь работать с базами данных — и получите навык, необходимый каждому программисту. ● Поймёте, как оптимизировать рутинные задачи при помощи SQL-запросов и получить повышение. ● Прокачаете знания SQL. ● Узнаете, как стать более востребованным специалистом. ● Сможете использовать сложные конструкции SQL. Разобраться в теме поможет профи — специалист по анализу данных, сертифицированный SQL-разработчик от Microsoft Мкртич Пудеян. 8 лет работал специалистом по хранилищам данных в Tele2. 🎁 Всем зарегистрировавшимся пришлём на почту чек-лист базовых навыков по SQL, которые требуют на собеседованиях. Подключайтесь к прямым эфирам и используйте возможность получить сертификат на скидку 10 000 рублей на любой курс Skillbox. В конце интенсива подарим участникам электронную книгу издательства МИФ. #реклама

  • Google Таблицы

  • Google Таблицы

    Минус на минус дает число: превращаем текст в число для дальнейших вычислений Этот вопрос недавно поднимался в нашем чате, и мы решили рассказать об этом всем. Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE, они не будут готовы к употреблению сразу - это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке - там ноль, хотя внешне вроде бы числа извлеклись правильные. Как превратить текст в число в Google Таблицах (и в Excel тоже)? 1 С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения =--REGEXEXTRACT(...) 2 С помощью функции VALUE/ЗНАЧЕН. 
    =ЗНАЧЕН(REGEXEXTRACT(...))
    3 С помощью еще какой-нибудь математической операции, не меняющей значение, например, умножения на единицу =REGEXEXTRACT(...)*1

  • Google Таблицы

    Как за 1 год вырасти в доходе до 250 тыс руб? 20 декабря Алексей Колоколов проведет вебинар, где поделится своим опытом. Он начинал с нуля в 2009, не умея программировать, зная лишь Excel. Теперь он признанный эксперт по PowerBI, у него за плечами 70 реализованных проектов для Siemens, Schneider Electric, Lego и др. Алексей издал 2 книги, преподает Business Reporting на МВА в США. Кому будет полезен этот вебинар: Новичкам в ИТ - узнаете, как на самом деле устроена индустрия BI-аналитики, оцените ваши перспективы Специалистам с опытом работы с данными - поймете, как развивать свою карьеру в современных реалиях За 1,5 часа успеем разобрать главные вопросы: - За что заказчики готовы платить выше рынка? - Какие навыки развивать, чтобы получить работу в Европе и Америке? - Какой стек технологий выбирать (PowerBI, Tableau, Qlik, Open Source)? - Какие зарплаты в России и Европе? У Вас есть уникальная возможность получить ответы на свои вопросы от эксперта-практика. Ждём Вас во вторник, 20 декабря 2022 года в 19:00 МСК! Регистрируйтесь по ссылке bit.ly/3PyecPe и получите запись интервью с Дата-инженером из Amazon. #реклама

  • Google Таблицы

    Простейший onEdit скрипт накопления с комментариями Сегодня персонально ответим на вопрос Евгения из нашего чата и покажем простой скрипт для накопления суммы. Работает так: вводим что-то в ячейку, скрипт проверяет, число ли это, проверяет в какую ячейку и на какой лист ввели и если все правильно, то добавляет это число к аккумулятору. Код с комментариями: function onEdit(e) { //определяем лист, который редактируется const sheet = e.source; //умножаем значение, которое ввёл пользователь на 1. чтобы преобразовать из текста в число const value = e.value * 1; //ЕСЛИ полученное значение число, ЕСЛИ пользователь ввёл его на "Лист1" и ЕСЛИ в ячейке "B1" if (!isNaN(value) && sheet.getSheetName() == 'Лист1' && e.range.getA1Notation() == 'B1' && e.value) { //ТО определяем ячейку накопленного итога const range = sheet.getRange('B2'); //берём из нее значение const old_value = range.getValue(); //и добавляем наше число к нему range.setValue(e.value * 1 + old_value); } }; Таблица И наш чат, в котором можно задавать вопросы, на которые мы иногда отвечаем даже на канале 😎

  • Google Таблицы

    Как сделать данные в Excel наглядными и понятными Подсказки, чек-листы и лайфхаки есть на канале Клуба анонимных аналитиков. Ведет его Алексей Колоколов, директор Института бизнес-аналитики и эксперт по визуализации данных. Вот примеры постов: ❗️Топ-7 ошибок в формах отчетности и как их решить https://t.me/analyst_club/210 ❗️Как сделать каскадную диаграмму в старом Excel https://t.me/analyst_club/417 ❗️5 способов врать с помощью диаграмм https://t.me/analyst_club/152 ❗️Чек-лист для новичков: как проверить диаграмму https://t.me/analyst_club/146 Но фишка канала не только в лайфхаках по Excel. Он про аналитику данных бизнеса и визуализацию, которая помогает управленцам. А это уже — новый уровень. Подписывайтесь! https://t.me/analyst_club #реклама

  • Реклама

  • Google Таблицы

    Если без авокадо с круассаном (или иероглифов, или огонька, или чего угодно еще) в оформлении таблиц никуда, можно использовать функцию CHAR / СИМВОЛ, которая возвращает символ по коду. Как узнать, какой код у символа? Можно просто ввести формулу, которая будет возвращать символы для всех номеров, например, до 150000: 
    =ArrayFormula(CHAR(SEQUENCE(150000;1)))
    Номер строки, в котором будет символ, и будет его номером. Запоминаем и используем в будущем. Ловите таблицу с этой формулой - ссылка. Листайте и смотрите, вдруг что-то пригодится! Либо можно использовать сайт graphemica.com. Ищите там нужный символ и копируйте номер "html entity (decimal)". Вот пример.

  • Google Таблицы

    Хотите освоить профессию, которая позволит работать удалённо: принимать решения, организовывать, управлять и реализовывать успешные проекты? Попробуйте себя в профессии проджект-менеджера на бесплатном онлайн-интенсиве с 15 по 17 декабря в 16:00 по московскому времени! Бесплатная регистрация по ссылке 🔜https://clc.to/819pmA За 3 дня вы: ✔️ поймёте, подходит ли вам профессия проджект-менеджера; ✔️ узнаете, как определять цели проекта, формировать эффективную команду и работать с бюджетом; ✔️ узнаете, с чего начать карьеру и как развиваться, чтобы стать востребованным специалистом. Спикером выступит коммерческий директор UPMARKET, экс-директор группы по работе с клиентами сегмента Big Business, член совета директоров холдинга Ingate, специалист с опытом более 10 лет в диджитал-сфере и управлении бизнесом — Мария Зубарева. На интенсиве вас ждёт практическое задание. Создадите план проекта: определите название, цель, ожидаемый результат, задачи и риски. 🎁 Каждый, кто оставит заявку на участие в интенсиве, получит чек-лист по запуску проекта с нуля. Всем, кто дойдёт до конца, — в подарок электронная книга Адель Линн «Сила эмоционального интеллекта» издательства МИФ. Сделайте шаг к востребованной профессии: подключайтесь к прямым эфирам, задавайте вопросы эксперту и получите сертификат на 10 000 рублей на любой курс образовательной платформы Skillbox! #реклама

  • Google Таблицы

    Приводим данные к правильному виду или три REGEXREPLACE Друзья, сегодня решаем задачу наших клиентов: в данных (столбец A) есть разные варианты написания размеров, через х,Х (латиница),*,Х,х (кириллица) и мы хотим привести все их к написанию через х (строчная латиница). Параллельно убираем пробелы и меняем точки в размерах на запятые. Поехали: 1) сначала поборем пробелы, табуляцию и перенос строк: REGEXREPLACE(A2:A12;"\s";"") Про \s 2) берём результат и меняем все варианты х|\*|X|Х на х (строчную латиницу). Не забываем, что звездочка - метасимвол в регулярных выражениях и для точного поиска её нужно экранировать \* REGEXREPLACE(REGEXREPLACE(A2:A12;"\s";"");"х|\*|X|Х";"x") 3) и напоследок меняем все точки (опять спецсимвол и нужно экранировать, \.) на "," =ARRAYFORMULA( REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2:A12;"\s";"");"х|\*|X|Х";"x");"\.";",")) С помощью формулы массива заставляем формулу работать сразу на всём диапазоне данных. Если будут идеи как это решить иначе - напишите в комментарии :) Материалы: Компактная памятка про регулярки от Vitalich "Народная" база с примерами регулярных выражений (внутри не всё идеально, но некоторое понимание получите) PS короткий вариант от @vitalich: REGEXREPLACE(...;"(?i)[хx*]";"x")