Google Таблицы

20 января 2023 09:58

Обращаемся к GPT3 из Таблицы

Друзья, взрыв популярности ИИ не прошёл мимо нас, сегодня показываем Таблицу с интерфейсом для обращения к API GPT3. На GPT3 базируются модели искусственного интеллекта, на данный момент из API можно обращаться к четырем моделям (самой современной, которую выложили недавно - нет, но и текущие что-то могут, смотрите гифку).

Чтобы заработала Таблица для обращения к ИИ:
1) Копируем Таблицу себе

2) Регистрируемся и получаем API-ключ (с российской симкарты может не получиться, можете попробовать через виртуальную), ключ вставьте в редактор скриптов

3) Во второй строке скопированной Таблицы выбираем модель и пишем вопрос в "prompt". Максимальное количество токенов, это что-то вроде смысловых единиц в вопросе и ответе, для text-davinci-003 это 4000, для остальных моделей - 2048. Если вместо ответа увидите ошибку про количество токенов - уменьшайте.

4) Запускаем: Скрипты > Отправляем запрос. Скрипт добавит в диапазон G:I ответ ИИ, либо ошибку, если что-то не так

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

Описание API GPT (сможете почитать про модели, токены, температуру, запросы)
Обращаемся к сократителю ссылок из Таблицы

👇 Делитесь интересными ответами в комментариях

Google Таблицы

18 января 2023 12:55

Как сделать отчет понятным? 4 совета от эксперта

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

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

1️⃣ 5 лайфхаков для работы в Excel
2️⃣ Топ-7 проблем с данными в отчетах
3️⃣ Чем отличаются разные виды отчетов
4️⃣ Как выбирать диаграммы по смыслу данных

Новые советы и лайфхаки по визуализации выходят на канале регулярно. Бывают бесплатные марафоны и мастер-классы для тех, кто хочет освоить новые профессиональные навыки, чтобы приносить больше пользы бизнесу. Подписывайтесь! /channel/analyst_club

#реклама

Google Таблицы

12 января 2023 11:03

F2: переход к ссылке в формуле

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

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

Google Таблицы

09 января 2023 08:35

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

По данным американского журнала Entrepreneur, 82% вакансий требуют свободного владения Excel, и таким специалистам платят на 17% больше.

Освоить Excel и Google Таблицы можно на онлайн-курсе «Excel + Google Таблицы с нуля до PRO» от Skillbox. Всего за четыре месяца вы научитесь автоматизировать работу с помощью скриптов и макросов.

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

Переходите: https://clc.to/0878HQ и приобретайте курс со скидкой со скидкой до 60% до конца января!

#реклама

Google Таблицы

06 января 2023 09:20

Оглавление Таблицы скриптами

Друзья, привет!

В первом посте 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 Таблицы

31 декабря 2022 12:38

Друзья, с наступающим вас!

Посты, которые вспоминали в нашем чате (@google_spreadsheets_chat) чаще всего:

>> Крутейшая таблица с подсказками про query

>> Памятка по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах)

>> Условное форматирование. Выделяем дубликаты и не только

>> Избранные посты нашего канала

>> Как посчитать

>> Памятка с советами, позволяющими ускорить работу документа

>> Памятка по условиям, с помощью которых можно отобрать (для функции SUMIFs/СУММЕСЛИМН, например) пустые и непустые ячейки

>> СОБИРАТОР 4.0

>> Важный скрипт. Связанные выпадающие списки из кэша

>> Считаем сумму по каждой строке / столбцу в формуле массиве

>> Народный Telegram бот❗️ Отправляем сообщения прямо из Таблицы

>> OR / AND в функции FILTER

>> ОТПРАВЛЯТОР 2.0
Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию

>> Объединяем любое количество диапазонов Таблицы в одной функции

>> ​​ARRAYFORMULA для автоматической нумерации списков (счетчик)

>> ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты

>> SUMIF для нескольких условий в формуле массива. Объединяем диапазоны с помощью амперсанда (&)

>> ​​В правиле условного форматирования можно использовать данные другого листа.

Google Таблицы

29 декабря 2022 08:02

Лучшие посты - 2022

Всего мы опубликовали чуть более 100 постов за этот год. Их всегда можно найти в нашем оглавлении, ну а сегодня решили выбрать для вас пачку хороших постов. Это не топ по просмотрам или по перепостам. А то, о чем мы - по нашей субъективной оценке полезности - решили напомнить. Вдруг кто-то пропустил что-нибудь полезное или присоединился к нам по ходу года.

Схватка двух ёкодзун. Сравнение Google Таблиц и Excel (ну ладно, это было перед НГ-2022, но все остальные посты точно из этого года)

Видео: Пользовательские числовые форматы в Google Таблицах

ВСТАВЛЯТОР: вставляем формулу / скрипт сразу же вставляет вместо неё значения

Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела

Проектная диаграмма - Условное форматирование

ВПР-им с разных листов

Sheets Api, берём данные

Sheets Api 2, вставляем данные

Достаём ссылки на фотографии товара из карточки WB

Задача: посчитать количество значений (или что-то еще, не столь важно - мы рассмотрим на примере счета) в каждой строке одной формулой.

JOIN / TEXTJOIN по каждой строке в новых реалиях

Находим последнее значение с помощью XLOOKUP

Сумма по строке в новых реалиях, c условием

Google Таблицы

24 декабря 2022 10:30

Задача: считаем нарастающий итог до заданного месяца и за заданный год формулой.

Дано: кривая и некрасивая выгрузка, где добавляются по 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 Таблицы

23 декабря 2022 07:50

Проверка данных стала наряднее!

Теперь:
— Правила настраиваются в боковой панели (как, например, условное форматирование). Боковая панель вызывается, как раньше, меню "Данные" (Data) — "Настроить проверку данных" (Data Validation).
Или Alt-D + V.

— Нажимаем Add Rule (Добавить правило), чтобы добавить новое правило, существующие правила видим в списке, можно наводить курсор на каждое и будет выделяться соответствующий диапазон. Удалить правило можно в его настройках, нажав Remove Rule, или по иконке с корзинкой в списке всех правил проверки.

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

— Вариантам в выпадающем списке (что из диапазона, что введенным в проверке) теперь можно присваивать цвета! Так что значения в ячейках выглядят более нарядно, если цвет несет какой-то смысл в ваших данных — легче считывать информацию.

Google Таблицы

21 декабря 2022 08:41

Накопление с помощью onEdit(), из комментариев

Скрипт накопления для любой ячейки одного столбца (на гифке)

Скрипт накопления для выборочных ячеек (их указываете в коде)

Всем спасибо за комментарии и вопросы, друзья!

Google Таблицы

18 декабря 2022 11:52

Как за 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 Таблицы

16 декабря 2022 13:44

Как сделать данные в Excel наглядными и понятными

Подсказки, чек-листы и лайфхаки есть на канале Клуба анонимных аналитиков. Ведет его Алексей Колоколов, директор Института бизнес-аналитики и эксперт по визуализации данных.

Вот примеры постов:

❗️Топ-7 ошибок в формах отчетности и как их решить
/channel/analyst_club/210
❗️Как сделать каскадную диаграмму в старом Excel
/channel/analyst_club/417
❗️5 способов врать с помощью диаграмм
/channel/analyst_club/152
❗️Чек-лист для новичков: как проверить диаграмму
/channel/analyst_club/146

Но фишка канала не только в лайфхаках по Excel. Он про аналитику данных бизнеса и визуализацию, которая помогает управленцам. А это уже — новый уровень. Подписывайтесь! /channel/analyst_club

#реклама

Google Таблицы

14 декабря 2022 11:10

Хотите освоить профессию, которая позволит работать удалённо: принимать решения, организовывать, управлять и реализовывать успешные проекты? Попробуйте себя в профессии проджект-менеджера на бесплатном онлайн-интенсиве с 15 по 17 декабря в 16:00 по московскому времени!

Бесплатная регистрация по ссылке 🔜https://clc.to/819pmA
За 3 дня вы:

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

Спикером выступит коммерческий директор UPMARKET, экс-директор группы по работе с клиентами сегмента Big Business, член совета директоров холдинга Ingate, специалист с опытом более 10 лет в диджитал-сфере и управлении бизнесом — Мария Зубарева.

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

🎁 Каждый, кто оставит заявку на участие в интенсиве, получит чек-лист по запуску проекта с нуля. Всем, кто дойдёт до конца, — в подарок электронная книга Адель Линн «Сила эмоционального интеллекта» издательства МИФ.

Сделайте шаг к востребованной профессии: подключайтесь к прямым эфирам, задавайте вопросы эксперту и получите сертификат на 10 000 рублей на любой курс образовательной платформы Skillbox!

#реклама

Google Таблицы

12 декабря 2022 10:50

Какую математику нужно знать, чтобы попасть в IT?

Многие люди, которые хотели попасть в IT, отказывались от этой идеи, когда вспоминали свои школьные оценки по математике.

У нас есть хорошая новость: математика – это интересно, не так уж сложно и доступно всем. В Яндекс Практикуме есть бесплатный тренажер по математике для тех, кто хочет освежить знания, и полноценный математический курс для аналитиков данных.

На вебинаре «Какая математика нужна аналитику и специалисту по Data Science» спикер Ольга Матушевич, наставник курса «Анализ данных» расскажет:

— какую именно математику нужно знать, чтобы зайти в IT;
— как нормально хорошо зарабатывать;
— как решать интересные и сложные задачи;
— как стать супер-экспертом;
— примеры реальных рабочих задач на каждом из этих уровней;
— список тем/учебников/материалов под каждый уровень.

Приходите 13 декабря в 15:00

#реклама

Google Таблицы

09 декабря 2022 10:04

Все хотят в IT, но не могут войти

В РОЦИТ (крупнейшей общественной организации, занимающейся интернет-технологиями) подсчитали, что треть россиян хочет работать в IT, но при этом большинство не понимает, как это сделать.

Почему такой повышенный интерес к IT сфере? Количество IT-продуктов увеличивается, бизнесу нужно создавать качественные сервисы, чтобы преуспевать, ведь пользователи становятся все более требовательными.

Академия Eduson создала курс «Python-разработчик» , который позволит вам освоить все необходимые актуальные навыки и работать из любой точки мира со средней зарплатой 130 000 рублей. Над обучением трудились эксперты по разработке в «Домклик» и менторы по Python из Рамблера. Плюсы курса:

▪️ Теория понятным языком на примерах из жизни;
▪️ Погружение в среду разработки;
▪️ Поддержка кураторов 24/7 на год;
▪️ Диплом гособразца и возможность налогового вычета;
▪️ Карьерные консультации и отправка резюме компаниям партнерам;
▪️ Навык не просто писать код, а программировать.

После прохождения курса вы соберете портфолио, подготовитесь к отбору и будете уверенно программировать. Сейчас можно выучить язык программирования с очень приятной скидкой в 55% по промокоду ТАБЛИЦЫ.

Переходите по ссылке, чтобы ознакомиться с подробностями.

#реклама

Google Таблицы

19 января 2023 10:15

Простой скрипт: убираем выпадающие списки (проверка данных) из выбранных ячеек

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

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

Скрипт нужно положить в скрипты Таблицы (Расширения > Apps Script).

function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Скрипты')
.addItem('Удалить проверку данных', 'del_dv').addToUi()
};

function del_dv() {
const range = SpreadsheetApp.getActiveRange();
range.clearDataValidations(); //добавиьте .clearContent(), чтобы очищать из ячеек значения
}

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

Google Таблицы

16 января 2023 08:02

Обновляем книжно-табличный обзор (renat_shagabutdinov/excellent_books">целиком он по ссылке).

Сегодня - две книги-сборника отдельных лайфхаков: одна по Excel, другая по Таблицам. Будут полезны всем - и новичкам, и продолжающим.
И одна книга по узкой теме, на которую, казалось бы, нечего написать на целую книгу - но на самом деле есть что: это книга про "таблицы" и списки Excel.

Spice Up Your Sheet Life

100 гугло-табличных лайфхаков на самые разные темы - от одного из главных экспертов. Форматирование, функции, сводные, диаграммы, скрипты - тут всего понемногу. И есть действительно нетривиальные вещи.

Бен дарит книгу за подписку на рассылку. Рассылку отдельно рекомендуем, кстати!
Ссылка на PDF.

MrExcel 2022: Boosting Excel
Книга на Амазоне
Новинка от одного из лучших авторов в теме Excel.

Книга позиционируется как дополнительный источник для тех, кто уже использует Excel и довольно интенсивно. Так как основ и структурного изложения по темам тут нет. Зато есть почти 130 приемов (даже больше, так как ко многим приемам есть дополнения).

Есть новшества Excel последних лет: LET, LAMBDA, ПРОСМОТРX, опции для совместной работы (как представления или история версий). Есть и Power Query и модель данных Power Pivot,
Много неочевидных вещей, как у всегда у автора - то, что зачастую не встретишь в других книгах.

Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables
Книга на Амазоне

Книга целиком посвящена "таблицам" Excel и их предшественнику - спискам. То есть тому инструменту, который в русскоязычной среде называют "умными таблицами", а в интерфейсе Excel - "таблицами" / tables.

Тут все детали про ссылки на таблицы, их взаимодействие, про изменение размеров таблиц, сортировку и фильтрацию, форматирование, использование как источник в простых сводных и Power Query и даже про работу с таблицами в VBA (макросах) и в Excel Online!

Перевод есть на сайте Сергея Багузина. Спасибо ему за эту работу! Сергей использовал в переводе свои скриншоты (!!) с русскоязычным интерфейсом.

Google Таблицы

10 января 2023 08:44

Средний чек

Друзья, привет! Сделаем упражнение по расчету средних чеков для диапазона C3:C100. Посмотрите на этот диапазон на скриншоте.

Средний чек по всему диапазону
Считается очень незамысловато:
=AVERAGE(C3:C100)

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

Накопительный средний чек
Посложнее, пишем формулу массива (за формулу спасибо Михаилу Cмирнову):
=ARRAYFORMULA(IF(C3:C="";"";SCAN(0; C3:C; LAMBDA(acc; cur; acc + cur)) / SEQUENCE(ROWS(C3:C))))

Разбираем:
1) =ARRAYFORMULA(IF(C3:C="";""; формула будет работать только для заполненных ячеек в C3:C;
2) SCAN(0; C3:C; LAMBDA(acc; cur; acc + cur)) считаем сумму чеков для каждой ячейки от начала диапазона (подробно эту формулу разбирали здесь);
3) / SEQUENCE(ROWS(C3:C)) и делим сумму чеков для каждой строки на количество чеков от начала диапазона до этой строки (смотрите в G:G на скриншоте, вывел для наглядности функцию туда).

Для каждой ячейки получаем средний чек, столбцец E:E на скриншоте, ура.

Придумаете другие варианты - напишите в комментарии.

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Google Таблицы

06 января 2023 09:20

Не забываем про новые формулы – возьмём два столбца, которые выводит пользовательская функция (столбец с ссылками на листы и столбец с названиями листов) и превратим их с помощью BYROW в один массив с гиперссылками:

=BYROW(sheets();LAMBDA(ROW;HYPERLINK(INDEX(ROW;0;1);INDEX(ROW;0;2))))

Спасибо Александру Иванову за идею.

🔥 Что-то непонятно – приходите в наш чат и задавайте вопросы.

Google Таблицы

05 января 2023 08:55

Ведёте аналитику в Google таблицах, но их функционала уже не хватает для эффективной работы?

На курсе «Аналитик данных» от школы karpovꓸcourses за 5 месяцев вы научитесь всему необходимому для решения и автоматизации ваших рабочих задач.

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

В программу курса входят:
– Python и SQL
– Git и Airflow
– Теория вероятностей
– Статистика и A/B-тесты
– Продуктовая аналитика
– Визуализация данных в Tableau

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

Преподаватели — специалисты с опытом работы в топовых IT-компаниях, таких как VK, Яндекс.Go и Jetbrains.

Переходите по ссылке и записывайтесь на курс до 11 января — по промокоду GOOGLEDA36 вы получите скидку 10% на полную оплату курса.

Кстати, на сайте можно пройти бесплатную демоверсию.

#реклама

Google Таблицы

30 декабря 2022 12:02

Как делать полезные для бизнеса дашборды: 5 советов от практика

Интерактивный дашборд помогает руководителю быстро делать выводы и принимать решения.
Но только если он разработан правильно.

Директор Института бизнес-аналитики Алексей Колоколов рассказал, как сделать дашборд понятным и наглядным.

1️⃣ Размещайте элементы диаграмм так, чтобы понять данные было проще. Например, легенду — над диаграммой.
2️⃣ Не перегружайте виджет кучей мер, выбирайте две важных. Остальные добавляйте во всплывающие подсказки.
3️⃣ Избегайте чрезмерной плотности диаграмм — это делает дашборд «тяжелым» и усложняет восприятие данных.
4️⃣ Не меняйте оси для одного и того же показателя, если он используется на нескольких диаграммах.
5️⃣ Ну и главное: выбирайте диаграммы не «для красоты», а по смыслу ваших данных.

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

#реклама

Google Таблицы

27 декабря 2022 17:01

Аналитика личных финансов: марафон для себя, а не для работы
Как навести порядок в личных финансах: 5 уроков от эксперта

Новогодний подарок от Института бизнес-аналитики — бесплатный марафон «Аналитика личных финансов». Чтобы каждый, кто обычно делает отчеты для заказчиков, позаботился о себе. И своих деньгах.

Марафон пройдет с 4 по 10 января. Вести уроки будет приглашенный эксперт по личным финансам. Вы научитесь:

▪️ распределять расходы не только по «шаблону в Excel»
▪️ заполнять свой бюджет за 1,5 часа в месяц
▪️ составлять финансовый план и модель семейного бюджета
▪️ анализировать личные доходы, расходы и накопления
▪️ визуализировать данные банковской выписки на дашборде

Марафон подойдет как для новичков, так и для тех, кто уже пытался вести свой бюджет. Проверенные техники, понятное изложение уроков и практические задания для отработки новых навыков. Все это — бесплатно.

Посмотреть программу и записаться на участие можно на сайте Института бизнес-аналитики: https://bit.ly/3WPWeKB

#реклама

Google Таблицы

23 декабря 2022 14:01

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

По данным американского журнала Entrepreneur, 82% вакансий требуют свободного владения Excel, и таким специалистам платят на 17% больше.

Освоить Excel и Google Таблицы можно на онлайн-курсе «Excel + Google Таблицы с нуля до PRO» от Skillbox. Всего за четыре месяца вы научитесь автоматизировать работу с помощью скриптов и макросов.

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

Узнать подробнее о курсе и получить скидку до 60% в честь Нового года можно здесь: https://clc.to/l6W0zw

#реклама

Google Таблицы

22 декабря 2022 09:34

Прокачайте свои навыки в программировании и узнайте всё о профессии SQL-разработчика на бесплатном онлайн-интенсиве Skillbox. Подключайтесь к прямым эфирам с 26 по 28 декабря в 19:00 по московскому времени: https://clc.to/_XIfyg.

● Научитесь работать с базами данных — и получите навык, необходимый каждому программисту.
● Поймёте, как оптимизировать рутинные задачи при помощи SQL-запросов и получить повышение.
● Прокачаете знания SQL.
● Узнаете, как стать более востребованным специалистом.
● Сможете использовать сложные конструкции SQL.

Разобраться в теме поможет профи — специалист по анализу данных, сертифицированный SQL-разработчик от Microsoft Мкртич Пудеян. 8 лет работал специалистом по хранилищам данных в Tele2.

🎁 Всем зарегистрировавшимся пришлём на почту чек-лист базовых навыков по SQL, которые требуют на собеседованиях. Подключайтесь к прямым эфирам и используйте возможность получить сертификат на скидку 10 000 рублей на любой курс Skillbox. В конце интенсива подарим участникам электронную книгу издательства МИФ.

#реклама

Google Таблицы

19 декабря 2022 08:02

Минус на минус дает число: превращаем текст в число для дальнейших вычислений

Этот вопрос недавно поднимался в нашем чате, и мы решили рассказать об этом всем.

Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE, они не будут готовы к употреблению сразу - это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке - там ноль, хотя внешне вроде бы числа извлеклись правильные.

Как превратить текст в число в Google Таблицах (и в Excel тоже)?

1 С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
=--REGEXEXTRACT(...)

2 С помощью функции VALUE/ЗНАЧЕН.

=ЗНАЧЕН(REGEXEXTRACT(...))

Google Таблицы

17 декабря 2022 11:50

Простейший 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 Таблицы

15 декабря 2022 08:01

Если без авокадо с круассаном (или иероглифов, или огонька, или чего угодно еще) в оформлении таблиц никуда, можно использовать функцию CHAR / СИМВОЛ, которая возвращает символ по коду.

Как узнать, какой код у символа?
Можно просто ввести формулу, которая будет возвращать символы для всех номеров, например, до 150000:

=ArrayFormula(CHAR(SEQUENCE(150000;1)))

Google Таблицы

13 декабря 2022 13:00

Приводим данные к правильному виду или три 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
"Народная" база с примерами регулярных выражений (внутри не всё идеально, но некоторое понимание получите)

Google Таблицы

10 декабря 2022 09:20

Как выделить / найти все формулы на листе?

Можно просто нажать Ctrl + `
Или в меню: Вид - Показать - Формулы (View - Show - Formulae), или Alt+V + S + A.

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

Но если вам нужно выделить ячейки с формулами цветом, чтобы их сразу визуально считывать (или, наоборот, ячейки без формул) - можно воспользоваться условным форматированием и функцией ISFORMULA - как на скриншоте.

Смотрите также:
Скрипт для вывода всех формул в таблице

Google Таблицы

06 декабря 2022 08:50

Простой post-запрос, простое обращение к API

Привет! У нас часто спрашивают — а как написать обращение из Таблиц к API? Давайте мы вам покажем. Есть ресурс, который позволяет сокращать ссылки: https://bitly.com/, там можно зарегистрироваться и получить API-ключ.

Ключ получили, далее смотрим документацию и видим, что для сокращения ссылки нам нужно отправить post-запрос на адрес https://api-ssl.bitly.com/v4/shorten с нашим API-ключом и с длинной ссылкой в payload.

Минимальный необходимый код:
function easy_post(){
const url = 'https://...' //ссылка, которую сокращаем
const token = '4d280..' //наш токен

let params = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({"long_url": url}),
'headers': {
'Authorization': 'Bearer ' + token,
}
};

var r = UrlFetchApp.fetch('https://api-ssl.bitly.com/v4/shorten', params);
r = JSON.parse(r);
console.log(r);
}

В r получаем такой объект:
{ created_at: '2022-12-04T06:34:47+0000', id: 'bit.ly/3umWEM9', link: 'https://bit.ly/3umWEM9', custom_bitlinks: [], long_url: 'https://docs.', archived: false, tags: [], deeplinks: [], references: { group: 'https://api-ssl.bitly.com/v4/groups/Bmbui906n47' }}

Чтобы достать короткую ссылку, достаточно обратиться к этому объекту как r['link'].

Хорошо, ребят, вы мне показали, а что с этим делать дальше?
Например, пишем такой скрипт — кликаем в таблице на ячейку с ссылкой, запускаем скрипт, а он сокращает ссылку и вставляет в соседнюю ячейку результат. Таблица с примером / код в pastebin.

Либо, как мы сделали недавно для нашего клиента - обращаемся к ответам формы, в ответах - отдельные части длинной ссылки, внутри скрипта собираем ссылку, далее сокращаем её и все это вставляем в Таблицу. Кладём скрипт на событие "приход нового ответа формы".