34653
Работа в Google Таблицах. Кейсы, решения и угар. контакты: @namokonov @r_shagabutdinov оглавление: goo.gl/HdS2qn заказ работы: teletype.in/@google_sheets/sheet_happens чат: @google_spreadsheets_chat
Заниматься ли аналитикой как профессией — вот в чем вопрос! Объясняем, почему всё же да.
Что такое аналитика сегодня?
Это зарплаты от 60 000 рублей на начальном уровне, возможность удаленной работы и перспективы роста доходов…
Да, но это же еще и очень сложно, запутанно и вообще только для технарей?
Не совсем так!
На бесплатном онлайн-курсе «Аналитика за шесть уроков» эксперты-практики из Changellenge >> Education докажут, что начать учиться аналитике может перейти практически каждый — независимо от возраста и базового образования. На мини-курсе вы:
✔️Изучите азы основных аналитических программ;
✔️Решите базовые задачи от экспертов-практиков;
✔️Разберетесь с направлениями и выберите подходящее для себя;
✔️Получите сертификат о прохождении курса и пошаговый план по переходу в новую профессию.
И примете решение о продолжении новой карьеры!
Регистрируйтесь по ссылке https://u.to/37RkIA
Реклама. changellenge-education.com ИНН 7716917009 erid: 2Vtzquj3Akq
#реклама
Работа с отчётами, таблицами и графиками отнимает кучу времени? Нужно отчитаться за работу перед начальством в виде ПРЕЗЕНТАЦИИ, а выделить самое важное и наглядно показать результат не получается? А что, если от этого будет зависеть твоя карьера?
Записывайся на мастер-класс по отчётам и таблицам в PowerPoint или Keynote от академии презентаций Bonnie&Slide и научись создавать понятные и эффективные отчёты за счет правильной компоновки данных и шаблонов, чтобы получить повышение на работе:
- Без дизайнеров
- По брендбуку или без
- Не тратя более 10 минут один слайд
Регистрация тут - ссылка
🎁Все, кто зарегистрируется, получат гайд "10 способов как сделать убойную понятную презентацию, когда много данных" в подарок!
Реклама. ООО «БОННИ И СЛАЙД». ИНН 9701259086.
#реклама
Дано: в ячейке есть символы, нам надо получить текстовую строку с ними же, но отсортированными.
Решение: создаем виртуальный массив из всех символов — для этого создаем последовательность (функция SEQUENCE) чисел от единицы до числа символов в ячейке (LEN / ДЛСТР), извлекаем символы с помощью ПСТР / MID — сформированная последовательность выступает номерами символов, которые мы этой функцией извлекаем.
Далее сортируем полученный массив (SORT) и сразу же собираем его обратно в текстовую строку (CONCATENATE / СЦЕПИТЬ).
Получится формула:=CONCATENATE(SORT(MID(ячейка;SEQUENCE(LEN(ячейка));1)))
В Google Документах можно использовать переменные — увы, только в корпоративных аккаунтах Google Workspace (и в учебных), но не в личных бесплатных.
Для чего это нужно? У вас есть шаблонный документ или письмо, где нужно менять только определенные параметры (ФИО, даты и так далее). Причем некоторые параметры (ФИО сотрудника, например) фигурирует в нескольких местах. С переменной можно изменить значение в одном месте и не проверять, везде ли вы все исправили во всем документе.
Открываем квадратную скобку, вводим название переменной, закрываем скобку и нажимаем Tab. Другой вариант: меню Вставка -> Умные чипы -> Переменные.
Переменную теперь можно вставлять столько раз, сколько нужно в документ, и менять значение в одном месте. Поменять названия переменных или удалить их можно в боковой панели "Переменные" (ее можно открыть, нажав на стрелку справа сверху у любой существующей переменной).
УКРАШАТОР | переносим форматирование из источника во много Таблиц
Друзья, мы подготовили для вас скрипт, который поможет перенести форматирование из шаблона в другие Таблицы.
Как использовать:
1) копируйте таблицу к себе
2) заполняйте лист настройки: вводим откуда скрипту брать шаблон с форматированием и куда его нужно будет вставлять
3) запускайте скрипт из меню с 🔥, скрипт пройдется по каждой таблице из списка "куда" и поставит дату и время в столбце C, если перенести форматирование получится
* Переносим только заливку ячеек, цвет шрифта, размер шрифта и сам шрифт, если нужно что-то еще - можете добавить в 16-21 строки кода по аналогии. Название методов можно подсмотреть вот здесь.
А обязательно ли вообще перечислять все методы или можно перенести форматирование "разом"? Можно, с помощью sheets api, попозже покажем вам пример, ну или вы покажите его в комментариях.
⭐️ ЗАКАЗАТЬ РАБОТУ
Выделяем цветом формулы по какому-то признаку
Вы хотите выделить визуально формулы массива или формулы, ссылающиеся на какой-то лист, или формулы с определенными функциями.
напоминаем, что получить текст формулы можно с помощью функции FORMULATEXT. Ну а далее искать в этом тексте какой-то признак можно с помощью функций REGEXMATCH или НАЙТИ / FIND, ПОИСК / SEARCH.
И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь!
Определенные функции — по их названию. Например, XLOOKUP:=НАЙТИ("XLOOKUP";FORMULATEXT(ячейка)
Формулы массива можно выделить по наличию функции ArrayFormula:=REGEXMATCH(FORMULATEXT(первая ячейка форматируемого диапазона));"ArrayFormula")
Ссылки на лист с названием - по этому самому названию:=НАЙТИ("название листа";FORMULATEXT(первая ячейка ...))
На скриншоте в условном форматировании следующая формула, с помощью которой выделяем ячейки с ссылками на "Лист3":=REGEXMATCH(FORMULATEXT(B2);"Лист3")
💼 Освойте профессию с нуля и найдите работу через 6 месяцев!
Если думаете сменить специальность или начать карьеру с нуля, ищите возможность быстро получить нужные навыки. Сейчас важно как можно скорее выходить на первый заработок.
Например, на курсе Skillbox вы получите востребованную профессию BI-аналитика.
Это практический курс, над которым работали аналитики банка «Открытие». На нём вы научитесь:
💪 настраивать систему аналитики;
💪 создавать прикладные дашборды в Power BI;
💪 анализировать поведение пользователей с помощью API и Google Analytics;
💪 работать с данными в SQL и Python.
В конце курса у вас будет 3 проекта в портфолио.
Мы гарантируем ваше трудоустройство — или вернём деньги.
Узнать подробнее о курсе и получить скидку до 50% можно здесь: https://epic.st/pdP5h
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
erid: LjN8K5RVR
Начните 2024 с победы в розыгрыше полезных подарков.
🎁 1 место. Курс «Excel Pro» от онлайн-школы Action Digital School. За 55 часов получите навыки, которые помогут ускорить работу и выделят вас среди конкурентов.
🎁 2–3 места. Сертификат на 10 000 рублей в интернет-магазин «Дарить Легко». Порадуете себя покупками на Ozon, в Золотом Яблоке, Tefal, Леонардо или в сотнях других магазинов.
🎁 4–5 места. Сертификат на 5 000 рублей в интернет-магазин «Дарить Легко».
🎁 6–10 места. Подписка Telegram Premium на три месяца, чтобы получить доступ к эксклюзивным функциям приложения.
Для участия необходимо подписаться на Telegram-каналы
@glavbuh_school
@digitalschoola
@lifehackoftheday
а затем нажать кнопку «Участвовать» под этим постом.
Итоги подведем случайным образом при помощи бота 5 февраля в 19:00 по московскому времени.
▶️▶️▶️ УЧАСТВОВАТЬ◀️◀️◀️
Реклама. НОЧУ ОДПО "АКТИОН". ИНН 7706218347.
#реклама
Приглашаем на вебинар-демонстрацию возможностей Мегаплана 31 января
Лучше один раз увидеть Мегаплан в деле. На вебинаре покажем возможности сервиса и расскажем, как он поможет вашей компании
Регистрируйтесьна наш вебинар >>
Реклама. ООО "МЕГАПЛАН". ИНН 7725681416.
#реклама
121 из 200!
Друзья, доброе утро, а давайте поднажмем немного, нам не хватает 79 бустов. Пожалуйста!
Обещаем, что сторис будем создавать только по делу (ну либо если придумаем очень хорошую шутку 🙈)
/channel/google_sheets?boost
❤️
UTM-метки. Извлекаем все параметры и их значения формулой
Справедливый комментарий к прошлому посту: удобнее получать отдельные параметры (source, medium, etc.), а не все метки разом.
Можно так:
1. Взять все из ссылки после знака вопроса (с помощью комбинации текстовых функций или INDEX+SPLIT или REGEXEXTRACT — в примере последний вариант). Получится хвост ссылки с метками, где параметры и их значения разделены знаком "равно" (=), а пары разделены амперсандом (&).
2. Разделить их на пары по амперсанду с помощью SPLIT .
3. Транспонировать, чтобы в итоговом результате было два столбца — с параметрами и значениями. Функция TRANSPOSE.
4. Разделить по знаку "равно" с помощью SPLIT. Засунуть ее в ArrayFormula, ибо иначе получим результат только для первой пары.
Есть идеи по сокращению и оптимизации формулы? Добро пожаловать в комменты!=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(REGEXEXTRACT(A2;"\?(\S+)");"&"));"="))
Достаём курс евро из Московской Биржи.
Друзья, привет, Михаил поделился ссылкой, по которой можно вытянуть текущий курс евро к рублю из MOEX:
https://iss.moex.com/iss/engines/currency/markets/selt/boards/CETS/securities/EUR_RUB__TOM.json?iss.only=securities,marketdata&securities.columns=PREVDATE,PREVPRICE&marketdata.columns=TIME,LAST
Что с этой ссылкой делать дальше? Доставать данные скриптом в Таблицу, например:
function eurRub() {
const url = 'https://iss.moex.com/iss/engines/currency/markets/selt/boards/CETS/securities/EUR_RUB__TOM.json?iss.only=securities,marketdata&securities.columns=PREVDATE,PREVPRICE&marketdata.columns=TIME,LAST'
const r = JSON.parse(UrlFetchApp.fetch(url));
const curr = r.marketdata.data[0][1] || r.securities.data[0][1];
SpreadsheetApp.getActive()
.getRange("лист5!a1")
.setValue(curr);
};
Наш скрипт достанет либо цену последней сделки, либо, если её нет, например, не было торгов - последнюю актуальную цену.
Создаём QR-код в Таблице и Документе
Друзья, привет! В Таблицах QR-код создаётся формулой, которую нужно ввести в ячейку, как это сделать, мы рассказывали вот здесь: /channel/google_sheets/912
А чтобы добавить QR в Google Документ нужен скрипт, например тот, который будет ниже.
Что скрипт делает:
1) ищет в тексте документа "###"
2) находит и вставляет вместо "###" QR-код, созданный из ссылки t.me/google_sheets
3) изменяет размеры изображения на 75 и 75 пикселей
4) и добавляет к самой картинке исходную ссылку
function createQRCode() {
var Doc = DocumentApp.getActiveDocument();
var totalElements = Doc.getNumChildren();
for (var j = 0; j < totalElements; ++j) {
var element = Doc.getChild(j);
el = element.getText();
if (el == '###') {
element.removeFromParent();
var url = 't.me/google_sheets';
var blob = UrlFetchApp.fetch("https://chart.googleapis.com/chart?cht=qr&chs=300x300&chl=" + encodeURIComponent(url)).getBlob();
Doc.insertImage(j, blob)
.setHeight(75).setWidth(75)
.setLinkUrl(url)
};
};
ссылкой/" в коде и из этого формировать QR-код.
Суммируем каждую N-ую ячейку
Допустим, надо суммировать только каждый N столбец.
Можно использовать такую формулу:
=SUMPRODUCT((MOD(COLUMN(диапазон);N)=0)*(диапазон))
(MOD(COLUMN(диапазон);N)=0)
=SUMPRODUCT((MOD(COLUMN(C2:R2)-2;4)=0)*(C2:R2))
Превращаем дату-как-текст в дату
Для этого есть целая функция — DATEVALUE / ДАТАЗНАЧ.
Любой текст она не осилит (и не только такой вольный текст, как "Позавчера" или "первый день года", но даже "17 октябрь 2022", увы — см 4 строку на скриншоте). Но текст, максимально похожий на стандартные даты, превратит в настоящие. Если года в текстовой строке нет — будет использован текущий (см строку 6). Если есть время — останется только дата (строка 8).
Альтернатива — умножить дату на единицу.
Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида t.me/канал
Для этого извлечем текст "### subscribers", который есть по такой ссылке — с помощью IMPORTXML.
Ну а потом удалим (заменим на ничто) все не-цифры (\D) с помощью REGEXREPLACE.
Чтобы получить из 20 500 subscribers —> 20500.
Все вместе:=REGEXREPLACE(IMPORTXML(A2;"//div[3]");"\D";"")
Если у вас не ссылки вида t.me/канал, а только @имя_канала, то можно собрать ссылку прямо в формуле, удалив @:=REGEXREPLACE(IMPORTXML("/channel/"&SUBSTITUTE(A3;"@";"");"//div[3]");"\D";"")
Геокодер / Достаем по адресу координаты, используя скрипты
Друзья, недавно у нас заказали скрипт и заказчик (Иван, спасибо ❤️), разрешил его выложить на канал для всех.
Скрипт небольшой, но полезный, он собирает адрес из первого и второго столбца и если в столбце G стоит галочка "обновить", то идет в Maps.newGeocoder(), достает по этому адресу координаты, адрес еше раз (чтобы можно было сравнить адрес, которые запрашивали и который вернулся), JSON с остальным данными и выключаем галочку.
Пример работы на гифке, гифка со звуком.
В день на одном аккаунте есть где-то 300-400 бесплатных запросов, поэтому если задача запросить много адресов - либо используйте много аккаунтов гугл, либо запрашивайте несколько дней.
Таблица со скриптом
PS Посмотрел на JSON еще раз - еще из него можно достать индекс адреса, который вы запрашиваете
Хотите стать разработчиком и начать карьеру в сфере программирования? Но нет опыта и непонятно, с чего начать? Тогда курс Skillbox «Профессия 1С-разработчик» идеально вам подойдёт!
Стартуйте в профессии сейчас, пока на рынке не хватает разработчиков 1С.
На курсе вы научитесь:
— автоматизировать бизнес-процессы;
— разрабатывать системы учёта;
— создавать и дорабатывать функционал;
— настраивать отчёты для руководства;
— проектировать архитектуру баз данных;
— интегрировать 1С с другими системами;
— разрабатывать авторскую конфигурацию.
После прохождения курса вы получите сразу два документа — сертификат Skillbox и свидетельство фирмы 1С.
Узнать подробнее о курсе и получить скидку до 50% можно здесь: https://epic.st/phBX4
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
Как научиться работать с базами данных и упрощать задачи? Запишитесь на бесплатный мини-курс Skillbox. За 5 дней вы с нуля пройдёте основы SQL, научитесь обрабатывать, анализировать и красиво визуализировать данные на реальных кейсах. Подходит для любого уровня подготовки!
Зарегистрируйтесь прямо сейчас и получите полезный гайд о профессии: https://epic.st/JeoII
Что будем делать:
— Писать запросы на языке SQL.
— Проводить аналитику для бизнеса.
— Разрабатывать автоматизированную отчётность в Excel.
— Обрабатывать данные в Power Query.
— Визуализировать показатели в Excel: создавать красивые графики, диаграммы и метрики.
— Применять инструменты Excel для анализа данных.
Спикер — Мкртич Пудеян, специалист по анализу данных в «Газпромбанке». Сертифицированный SQL-разработчик от Microsoft, 8 лет работал специалистом по хранилищам данных в Tele2.
🎉 Всех участников ждут бонусы: 5 полезных статей по SQL и Excel, персональная карьерная консультация, год бесплатного изучения английского языка и скидка 10 000 рублей на любой курс.
🌟Учитесь с нами — откройте дверь в мир знаний за пару кликов!
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
Приглашаем вас на бесплатный мини-курс по бизнес-аналитике, на котором вы научитесь анализировать процессы в компании и оптимизировать их. Никакой воды, только необходимая теория и реальные практические задания: https://epic.st/eYufmi
В программе:
🔹 Знакомимся с профессией и изучаем основы бизнес-анализа
🔹 Разрабатываем и внедряем стратегии развития бизнеса
🔹 Описываем и оптимизируем бизнес-процессы
Как проходит мини-курс?
— Смотрите видео в удобное время — за 4 занятия изучите видеоуроки
— Получаете полезные материалы — чек-листы, гайды и другие бонусы
— Выполняете практическую работу после каждого видео
— Посещаете итоговый вебинар. Спикер в прямом эфире поделится профессиональными секретами, разберёт работы участников и ответит на вопросы
🎁 Участников ждут крутые подарки!
— Год изучения английского языка
— Персональная карьерная консультация
— Сертификат на скидку 10 000 рублей на любой курс
— 5 полезных чек-листов
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
Граждане, внимание!
Похоже, Гугл раскатывает новую функциональность: для использования функций импорта необходимо дать подтверждение (см. картинку).
Мы это заметили в таблице, где давно использовалась и работала функция IMPORTXML(). До нажатия кнопки "Allow access" импорт висел поломанный, после нажатия - всё ок, всё импортится, как раньше.
Из-за такой штуки у вас могут сломаться или уже сломались зависимые таблицы. Имейте в виду - надо сходить в источник и дать разрешение.
Не таблицами едиными! Сегодня про Google Диск (Drive).
Вы знали, что там можно использовать операторы, позволяющие сделать поиск более точным?
Вот некоторые из них.
title:название файла
"Поиск фразы из нескольких слов"
-Исключение Другие слова
-owner:me
to:ivanova@company.com
from:ivanova@company.com
Слово Around(n) Другое_слово
формула around(3) таблицаЧитать полностью…
Определяем первую и последнюю даты квартала по его номеру.
Ловите формулы для определения первой и последней даты квартала по номеру года и номеру квартала. Может пригодиться, если у вас отчеты с функциями SUMIFS, QUERY и другими, куда вы отправляете даты в качестве условий, а пользователю предлагаете выбирать только номер года/квартала в ячейках.
Начало квартала:
DATE(год; квартал * 3 - 2 ;1)
EOMONTH(DATE(год; квартал * 3 ;1);0)
TEXT(DATE(год; квартал * 3 - 2 ;1) ; "yyyy-mm-dd")Читать полностью…
Друзья, нам осталось всего 17 голосов, поднажмём?
Читать полностью…
Эх, дороги..
Друзья, к нам недавно пришел наш клиент, с такой задачей:
Задача от классной гитарной школы Guitardo
1) есть первая точка (долгота и широта)
2) есть еще сто точек, также с долготой и широтой
Нужно к первой точке найти ближайшую по расстоянию следующую точку, к этой точке - ближайшую следующую и так далее, пока точки не закончатся. Таким образом, в конце, мы получаем список точек от первой до последней с минимальным расстоянием друг от друга.
Как решили
Скриптом, конечно. В основе функция, которая ищет расстояние по прямой, ей нужно задать долготу и широту двух точек. А помогает функции скрипт, который начинает от первой точки, сопоставляет ее с остальными, находит ближайшую точку, дальше сопоставляет с остальными её и до конца, пока точки не закончатся.
Таблица со скриптом
Вытаскиваем utm из ссылки (а точнее — все, что после знака вопроса)
Для этого можно воспользоваться следующим регулярным выражением:\?(\S+)
Извлекаем выражение (круглые скобки) из не-пробелов, идущее после знака вопроса (\? = знак вопроса, а без слеша это был бы квантификатор (ноль или один символ)
Вся функция будет такая:=REGEXEXTRACT(A2;"\?(\S+)")
Хорошее про регулярные выражения:
Компактная памятка про регулярки от Vitalich
Извлекаем числа, едим пончики
Волшебство "найти и заменить". Приводим mm-dd к dd-mm
🤯 Каждый день рутина — офис, таблицы, отчёты. Ещё и платят мало. В то же время 1С-разработчики работают на удалёнке и получают до 300 000 р.
Самое время стать одним из них!
Чтобы освоить 1С, не требуется опыт в IT и знания английского! На онлайн-курсе «Профессия 1С-разработчик» от Skillbox вы всего за 6 месяцев научитесь:
автоматизировать бизнес-процессы;
проектировать архитектуру баз данных;
разрабатывать конфигурации и системы учёта;
интегрировать «1С» с другими системами.
В конце курса вы получите свидетельство 1C и помощь в трудоустройстве.
Где вы сможете работать:
💰 1С используют более 12 000 компаний, среди которых Лукойл, Сибур и Газпром.
🌴 Есть удалёнка и фриланс.
Открываем бесплатный доступ к первым 5 модулям. Вы учитесь совершенно бесплатно и решаете, стоит ли продолжать: https://epic.st/Bl3-LS
☃️🎁Начните год с новых знаний! Новогодняя скидка до 60% и второй курс в подарок!
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
Интересуетесь бизнес-аналитикой? Хотите узнать подробнее о профессии и её перспективах? Получить практические навыки?
Если на все вопросы вы ответили «да», тогда стоит скорее записаться на мини-курс «Бизнес-аналитик за 4 дня: быстрый старт в профессии»: https://epic.st/kMP_PZ
Вместе разберёмся в задачах бизнес-аналитика и соберём информацию о процессах компании, научимся определять проблемные места бизнеса и проектировать стратегические карты. Опишем и оптимизируем бизнес-процессы, оценим результаты бизнеса с помощью сбалансированной системы показателей.
🎁 Участников ждут крутые подарки!
— Год бесплатного изучения английского языка
— Персональная карьерная консультация
— Сертификат на скидку 10 000 рублей на любой курс
— 5 полезных чек-листов
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
🟢В Системе Финансовый директор появился новый мастер-класс о работе с формулами в Excel.
Эксперты собрали в 7 простых видеоинструкциях самое важное: от базовых правил до лайфхаков, которыми владеют единицы финансистов.
Пройдя мастер-класс, Вы узнаете, как работать с формулами в Excel: как их создавать, как очистить ячейки и сохранить формулы, как найти ячейки без формул, как скрыть и защитить формулы и так далее.
Закажите бесплатный доступ к мастер-классу по ссылке: https://bit.ly/47rpjkd
Реклама. Erid 2VtzqvYFvkm
🤔 Только 6% пользователей знают все фишки «Google Таблиц» и Excel.
Возможно, все функции таблиц знать не обязательно, но часть из них сделает вашу жизнь проще. Например, если вы:
🤓 Студент. В Excel и «Google Таблицах» можно использовать формулы, создавать диаграммы, настраивать макросы.
📈 Бухгалтер или экономист. Таблицы позволяют быстро создавать отчёты и фильтровать большие объёмы данных.
💻 Digital-специалист. Инструменты нужны, чтобы строить прогнозы, разрабатывать маркетинговые планы, сравнивать эффективность рекламных кампаний.
💰 Предприниматель. Вам таблицы помогут планировать продажи, считать рентабельность проектов, прогнозировать показатели роста бизнеса.
Освоить инструменты можно на онлайн-курсе «Excel + Google Таблицы с нуля до PRO» от Skillbox.
Новогодняя скидка до 60%! Второй курс в подарок при покупке!
Переходите по ссылке, чтобы узнать подробности: https://epic.st/yWhot9
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880