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

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

17221 @google_sheets
Открыть

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

  • Google Таблицы

    Простой onEdit() скрипт переноса строки

    Недавно в нашем чате спросили – как пользователь Таблицы может переносить строки из одного листа на другой?

    Отвечаем. Вот здесь Таблица со скриптом.

    Скрипт работает так – активируете на листе "отсюда" флажок в третьем столбце – эта строка удаляется из листа "отсюда" и значения вставляются на лист "сюда".

    Код с комментариями: https://pastebin.com/yhbU0FDf 🤗

  • Google Таблицы

    ​​Динамическая диаграмма в Google Таблицах

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

    Допустим, у нас почти те же данные - выручка за несколько лет по месяцам, только мы добавим к ним данные по прибыли. То есть показателей становится несколько.

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

    Реализуется все не так элегантно, как в случае со спарклайном.
    Для диаграммы данные придется вывести в ячейки рабочего листа. Сделаем это с помощью функции СМЕЩ, как и в предыдущем примере.
    (Напомним, в Excel для создания таких диаграмм СМЕЩ можно вводить прямо в функцию РЯД в диаграмме)

    На самом деле, мы воспользуемся двумя функциями СМЕЩ, соединенными в массив: одна будет выводить даты из первого столбца по выбранному диапазону, а вторая — данные из второго или третьего столбца в зависимости от выбранного пользователем показателя.
    (Будем в зависимости от выбора менять значение смещения по столбцам в функции СМЕЩ)

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

    И еще нашей диаграмме не хватает заголовка. Если в Excel прямо из заголовка диаграммы можно было сослаться на ячейку, как в обычной формуле, например =A1 и в A1 ввести формулу, которая будет формировать текст заголовка, то в Таблицах возможен только фиксированный текст. Так что остается сформировать динамический заголовок с помощью формулы в ячейке над диаграммой.

    Будем брать название показателя и добавлять к нему фразу "за период с янв 17 по дек 19". Периоды будем оформлять с помощью функции ТЕКСТ / TEXT, которая меняет форматирование числовых данных по заданному шаблону.

    Файл с примером: копируйте себе на диск, изучайте, разбирайте формулы.

  • Google Таблицы

    Умение визуализировать данные позволяет аналитику эффективно донести до заказчика свои выводы по проделанной работе.

    26 мая в 19:00 (мск) в рамках шестимесячного онлайн-курса «Профессия: Аналитик» ребята из ProductStar проводят бесплатный вебинар «Основы и инструменты визуализации данных».

    👩‍🏫 Кто выступит?

    Вадим Шмыгов, Business Intelligence Analyst в Invento Labs

    👨‍🏫 О чём пойдет речь?

    — Ответим на вопрос, зачем визуализировать данные и что такое инфографика,
    — Поговорим про типологию визуализаций и этапы работы,
    — Сделаем обзор основных инструментов для визуализации данных.

    🔥 Два самых активных участника вебинара получат сертификат на бесплатное обучение в ProductStar.

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

    Зарегистрироваться на вебинар 👉 @ProductStarAnalyticsBot

    #реклама

  • Реклама

  • Google Таблицы

    Выведем все пары
    Декартово произведение строк

    Привет! Декартово произведение – все возможные пары элементов двух массивов. Если всё равно непонятно – просто посмотрите на скриншот )

    Я подготовил для вас пользовательскую функцию, она будет работать с любым количеством строк. Чтобы воспользоваться – добавьте код функции в редактор скриптов Таблицы.

    function RENE(range1, range2){
    return []
    .concat(...range1
    .map(y => []
    .concat(...[range2
    .map(h => [y[0], h[0]]
    )]))).filter(t => t[0]);
    }

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

    =ARRAYFORMULA(TRANSPOSE(SPLIT(
    {REPT(JOIN("^";A3:A5)&"^";4);REPT( JOIN("^";B3:B6)&"^";3)};
    "^")))

    Таблица с примером
    Наш чат, там препарируем и не такие формулы 🔪

  • Google Таблицы

    Хочешь улучшить свое портфолио и получить продвижение по работе всего за 4 месяца? Тогда курс «Excel + Google-таблицы с нуля до PRO» как раз для тебя. Спеши занять вакантное местечко.
    🎉 Хочешь сэкономить? Skillbox дарит всем скидку 40% на курс!

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

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

    👉 Все подробности по ссылке: https://clc.to/dDJkDg

    #реклама

  • Google Таблицы

    Динамический спарклайн: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет - на русском языке)

    Друзья, привет. Астрологи провозгласили месяц спарклайнов – продолжим развлекаться с маленькими графиками.

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

    Создадим формулу, которая будет строить спарклайн по выбранному количеству месяцев и начиная с выбранного месяца.
    Для этого нужна будет функция СМЕЩ, формирующая ссылку на динамический диапазон. Зададим точку отсчета - первый месяц с данными; высотой диапазона будет выбранная пользователем в ячейке длительность периода, точкой отсчета (отступом по строкам) – позиция выбранного месяца.

    =СМЕЩ(первая ячейка с данными; ПОИСКПОЗ(выбранный в выпадающем списке месяц - точка отсчета; диапазон с месяцами в наших данных;0)-1;0; выбранная длительность периода; 1)

    Вычитаем единицу из позиции выбранного месяца, т.к. для января , который первый в диапазоне, нам отступать никуда не нужно.
    Последний     аргумент- это ширина, она равна единице.
    0 в функции СМЕЩ - это отступ по столбцам, он здесь не нужен. Берем данные из одного и того же столбца с показателем.

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

    Помня о том, что в случае со спарклайнами нельзя добавить ось и подписи данных, выведем отдельно минимальное и максимальное значение за выбранный период - просто будем использовать СМЕЩ как аргумент функций МИН и МАКС. Так мы будем понимать масштаб, понимать, какому значению соответствует минимальный и максимальный столбцы.

    Ну и добавим еще немного интерактивности - сделаем возможность вводить цвет спарклайна в целом и максимальной точки руками и на русском языке.
    Для этого будем в аргументе функции SPARKLINE переводить текст с названием цвета на английский с помощью GOOGLETRANSLATE.

    Напомним параметры такого спарклайна:
    charrtype (тип) – column (столбчатый)
    color – цвет спарклайна
    highcolor – цвет максимального значения

    Ссылка на файл с примером

    Другие посты про спарклайны:
    – Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.
    – Спарклайн с условием
    – Видеоурок: Синтаксис и примеры
    – Что такое спарклайны?

  • Google Таблицы

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

    20 мая в 19:00 (мск) в рамках шестимесячного онлайн-курса «Профессия: Аналитик», ребята из ProductStar проводят бесплатный вебинар «Как измерить неизмеримое».

    👨‍🏫 Кто выступит?

    Сергей Кадомский, Product Manager в Craft.co

    🎓 О чем пойдет речь?

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

    🔥 Два самых активных участника вебинара получат сертификат на бесплатное обучение в ProductStar.

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

    Зарегистрироваться на вебинар 👉 @ProductStarAnalyticsBot

    #реклама

  • Google Таблицы

  • Google Таблицы

    Правильно выбранная BI-система поможет бизнесу расти.

    В определённый момент у компании накапливается много данных, которые нужно структурировать, чтобы грамотно планировать бизнес. На внедрение Business Intelligence системы требуется много временных и денежных ресурсов, поэтому важно правильно выбрать подходящую систему.

    На бесплатном занятии в Нетологии разберут три самые популярные BI-системы — Qlik, Power BI, Tableau и их основные особенности, а также обсудят, какая из них подойдёт конкретно под ваши задачи.

    Регистрируйтесь → http://netolo.gy/fwV

    #реклама

  • Google Таблицы

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

    Продолжим рассматривать примеры спарклайнов и сегодня посмотрим на несколько параметров:
    — ymin и ymax - позволяют провести виртуальную ось, отсечь часть значений (например, все отрицательные) на спарклайне-графике типа line;
    — negcolor позволяет задавать цвет всех отрицательных значений в столбчатом спарклайне типа column
    — highcolor и lowcolor - выбрать цвета для точек с макс и мин значениями;
    — firstcolor и lastcolor - задать цвет для крайних точек

    Напомним, что цвета можно задавать не только с помощью их обозначений на английском (red = красный), но и с помощью hex-кодов, что обеспечивает доступ к большему разнообразию.
    Вот вам код для цвета "Королевский пурпурный Крайола": 7851a9

    А вообще любой код можно узнать, вбив название цвета в Яндексе.

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

    Другие посты про спарклайны:
    Спарклайн с условием
    Видеоурок: Синтаксис и примеры
    Что такое спарклайны?

  • Google Таблицы

    Как выделиться на фоне остальных кандидатов и получить работу аналитика?

    13 мая в 19:00 (мск) в рамках шестимесячного онлайн-курса «Профессия: Аналитик», ребята из ProductStar проводят бесплатный вебинар «Как подготовиться и пройти собеседование на аналитика».

    👨‍🏫 Кто выступит?

    Артур Самигуллин, ex-DS & Analytics Team Lead в Modulbank

    🎓 О чем пойдет речь?

    — Разберём, как проходит собеседование на аналитика.
    — Узнаем, как правильно подготовиться к интервью.
    — Проанализируем основные ошибки кандидатов на собеседовании.

    🔥 Два самых активных участника вебинара получат сертификат на бесплатное обучение в ProductStar.

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

    Зарегистрироваться на вебинар 👉 @ProductStarAnalyticsBot

    #реклама

  • Google Таблицы

    Геотаргетинг, СДЭК, находим объекты на карте в Таблице Google

    Сегодня публикуем статью Романа Игнатова, по совместительсту — модератора 🎖 нашего чата.

    Рома написал скрипт, по выбранному адресу отображающий в Таблице виджет Яндекс Карт с наложенными точками пунктов выдачи заказов СДЭК. При выборе пункта его название сохраняется в Таблицу.

    Этот скрипт — отличный пример работы с внешними API. Еще эта реализация показывает, что в html-окно, которое выводят Таблицы можно дейстительно поместить что угодно. Виджет карт — не предел.

    Статья со всеми ссылками

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

  • Google Таблицы

    ​​Спарклайн с условием

    Благодаря тому, что спарклайны в Google Таблицах реализованы в виде одноименной функции - к ним легко добавить условия с помощью другой функции, например ЕСЛИ / IF или IFS.

    Простой пример — выбирать цвет спарклайна в зависимости от значения, от того, выше среднего оно или нет.

    1. Формируем горизонтальный спарклайн, тип и максимальное значение указываем в массиве прямо в функции (charttype = bar, max = функция МАКС по всему диапазону данных)

    2. В качестве цвета (параметр color1) указываем функцию ЕСЛИ. Условие — то, что конкретное значение в строке с формулой больше среднего. Если это так, выбираем зеленый, иначе — красный.

    Напомним, что цвета в спарклайнах можно указывать и в виде hex-кодов: например, dc143c для малинового.

    3. Добавляем функцию ЕСЛИОШИБКА / IFERROR, чтобы в строках без данных не было ошибки.

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

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

  • Google Таблицы

    Excel + Google Таблицы с нуля до PRO на Skillbox

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

    Женя подготовил курс по скриптам в Google Таблицах, а Ренат — по макросам в Excel.

    Эти курсы дополнили уже существовавший курс "Excel+Google Таблицы с нуля до PRO". Теперь большой курс включает в себя 150 онлайн-уроков и все темы от основ работы до продвинутых формул! В том числе десятки новых уроков — по скриптам и макросам. С файлами примеров, дополнительными материалами, домашними заданиями.

    Посмотреть программу и зарегистрироваться

  • Google Таблицы

    Сравнение списков в Таблицах
    Способ 3 — сравниваем функциями FILTER / MATCH

    Формула, которая разбирается на скриншоте:
    =FILTER(B3:B10;ISNA(MATCH(B3:B10;C3:C8;0)))
    и
    =FILTER(B3:B10;NOT(ISNA(MATCH(B3:B10;C3:C8;0))))

    >> Таблица с примерами

  • Реклама

  • Google Таблицы

    Как понять ценность продукта для пользователей? Самый эффективный показатель — продуктовые метрики.

    ⏰ В среду, 29 апреля в 19:00 (мск) ребята из ProductStar проводят бесплатный интенсив для начинающих аналитиков: «Простыми словами о метриках».

    👩‍🏫 Кто выступит?

    Михаил Морозов, Analytics Team Lead в Skyeng

    👨‍🏫 О чем пойдет речь?

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

    🔥 Два самых активных участника вебинара получат сертификат на бесплатное обучение в ProductStar.

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

    Зарегистрироваться на вебинар 👉 @ProductStarAnalyticsBot

    #реклама

  • Google Таблицы

    ^ $ не обязательно добавлять перед каждым элементом — достаточно добавить по границам перечислений:
    ^(Вася|Петя)$

    @vitalich, спасибо!

  • Google Таблицы

    Вдогонку, про функции с регулярными выражениями

    Как говорят у нас в деревне, два поинта:

    1) Точное совпадение. Регулярное выражение Вася|Петя найдет строку "Вася", строку "Петя", а еще строку "Вася и его частушки под гармонь", так как поиск будет производиться по подстроке.

    Для того, чтобы искать только точное совпадение — добавляем перед каждым элементом ^ и $ после.

    Например, соберём из С1:D1 регулярное выражение:
    ="^"&JOIN("$|^";C1:D1)&"$"

    // ^Вася$|^Петя$

    2) Числа в диапазоне данных. В функции REGEXETRACT / REGEXMATCH / REGEXREPLACE вы можете передавать только текстовую строки. Хотите поработать с числами — предварительно приведите их к тексту, для этого возьмите диапазон в =TEXT(диапазон/ячейка;"@")

    Формула со скриншота
    =FILTER(A1:A5; REGEXMATCH(TEXT(A1:A5;"@") ; "^"&JOIN("$|^";C1:E1)&"$"))

    Таблица