#GoogleТаблицы — это аналог #Excel с веб-интерфейсом и возможностями для удобной совместной работы. Они похожи, и все же в Таблицах есть свои особенности, функции, плюсы и минусы. Работать в Google Таблицах просто и мы покажем как.
Функция ВЫБОР и номер квартала А еще про то, как организовать данные правильно Допустим, в таблице есть данные за 12 месяцев, и на каждый из них приходится несколько столбцов (e.g. факт, прогноз, отклонение и т.д.)
Наша задача — суммировать данные за текущий квартал. Сначала определим номер квартала по текущему месяцу: ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3
Эта история станет первым аргументом функции ВЫБОР / CHOOSE. А все последующие аргументы — что возвращать, если первый аргумент равен 1 (первый квартал), 2 и так далее: =ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3; суммируем данные по 1 кварталу; суммируем по 2; ...; по 4)
В качестве аргументов функции ВЫБОР могут быть другие функции — то есть мы выбираем их списка функций в зависимости от значения первого аргумента: =ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3;0);СУММ(D3;G3;J3);СУММ(M3;P3;S3);СУММ(V3;Y3;AB3);СУММ(AE3;AH3;AK3))
Можно было поступить и иначе — СУММ оставить снаружи, а внутри функции ВЫБОР собирать нужные ячейки в массивы. =СУММ(ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3;0);{D3;G3;J3};{M3;P3;S3};...;...))
Эту задачу наверняка можно решить и другими способами (например, горизонтальный FILTER для отбора столбцов с фактом и потом нужных месяцев?) — предлагайте варианты в комментариях!
Про организацию данных Друзья, посмотрите на скриншот с данными — это хороший пример того, как не стоит организовывать данные (хотя в реальной жизни — а это пример именно оттуда — мы все равно сталкиваемся с чужими неидеальными таблицами, свои лучше проектировать правильно с самого начала).
Во-первых, в верхней строке месяцы введены без года в текстовом формате. Вспомните ли вы в следующем году, когда зайдете в Таблицу, какой это год? Что будете делать, когда появится январь следующего года? Будь это даты, их было бы проще обрабатывать формулами в будущем.
Во-вторых, одна объединенная ячейка с названием месяца на три столбца (факт, прогноз, откл). Такой подход усложнит работу с формулами. Придется выдумывать костыли, вроде разных диапазонов для функции FILTER или формулы, которая сначала соберет полную строку с месяцами, а потом с использованием этой строки вы сможете отфильтровать данные.
Поэтому – не создавайте себе проблем, приводите данные сразу к правильному виду базы данных - поля (столбцы), по которым должен происходить отбор, должны быть всегда заполнены. Дата должна быть в соответствующем формате, хотите убрать год и оставить только месяц – это можно будет сделать форматированием (любые даты и числа можно отображать в любом мыслимом виде при помощи пользовательских форматов, о которых мы писали подробно).
Извлекаем из массива фразы, даже если они написаны с ошибками
Привет! У нас есть чат, в него можно приходить с вопросами. Недавно туда пришла Марина и спросила: Например, у меня есть 200 фраз. Среди них есть фразы со словами крем, глаза, ретинол. Мне их нужно найти в этом списке. При этом часть этих слов может быть написана с ошибками, типа крэм, ритинол и пр. И мне нужно вытащить все фразы с этими словами, в том числе и с ошибками.
Марине помог @vitalich, показав, как с помощью =FILTER и простых регулярных выражений можно вытащить всё, что нужно.
На скриншоте: формула в ячейке C2 берет регулярное выражение из ячейки C1 и выводит в C3 и ниже все, что ему соответствует (cre одна любая буква m).
🧞♂️Превращатор Листа Таблицы в xlsx / pdf / csv — файлмгновенно скачивается —либо сохраняется на Google Диск
Друзья, подготовили для вас комбайн превращений – скрипт, который при запуске определяет лист, на котором вы находитесь и превращает его в файл выбранного формата.
Если конвертируете в xlsx – скрипт вставит все формулы на листе как значения, чтобы ячейки, которые ссылаются на другие листы не потеряли своих значений.
Как установить: Открываете вашу Таблицу, Инструменты > Редактор скриптов, вставляете код: pastebin.com/iuKKSFrM, сохраняете и перезагружаете Таблицу. После перезагрузки в Таблице появится меню "🧞♂️ – скрипты".
SEQUENCE — выводим заголовки с датами текущего месяца
И снова о прекрасной функции SEQUENCE, которая, кстати, появилась и в Excel — пока только для подписчиков Office 365 (на русском — ПОСЛЕД).
Эта функция генерирует массив чисел по заданным параметрам - высоте, ширине, первому значению и шагу.
Аргументы функции: число строк, число столбцов, первое значение и шаг.
Например, такая функция выведет столбец с числами от 1 до 10000: =ПОСЛЕД(10000;1;1;1)
Так как даты — это числа, то можно выводить и их. Допустим, нам нужны все даты текущего месяца в строку. Понадобится такая конструкция: =ПОСЛЕД(1 строка; число дней в месяце; дата начала месяца; шаг=1)
Первый день текущего месяца можно получить так (текущий год + текущий месяц + первое число): =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)
А количество дней в месяце — так: =КОНМЕСЯЦА(СЕГОДНЯ();0)
Остается все собрать в одну конструкцию. И как тут не напомнить про то, что в строке формул можно использовать пробелы и переход на следующую строку (Alt+Enter), чтобы делать длинные формулы проще для восприятия.
Конечно, внутри функции ДАТА можно подставить любой месяц вместо текущего, и тогда будут выведены даты этого месяца (см ГИФ)
В Таблицах можно воспроизводить видео со звуком. Как это можно использовать? К примеру, вы сможете положить несколько видео-инструкций для своих коллег в Таблицу.
Чтобы видео заиграло – создаём скриптами модальное окно с emded-ссылкой на видео.
= Как заставить решение работать: 1) копируете Таблицу 2) открываете редактор скриптов в ней > триггеры > создаёте триггер на изменение Таблицы, выбираете функцию onEditTrig и сохраняете 3) готово – скрипт будет запускаться из Таблицы, после того, как вы активируете один из чекбоксов
За тридцать лет существования MS Excel превратился в мощный инструмент для работы с массивами данных.
Однако мало кому известна его полная функциональность — пользователи применяют простые формулы, но не знают приёмы, значительно облегчающие работу.
На бесплатном курсе Нетологии вы научитесь применять инструменты Excel, которые в разы увеличат вашу производительность. Вы узнаете, как наглядно представлять результаты своей работы и как заставить Excel работать вместо вас.
Отбираем три последних строки по имени и считаем среднее (ниже будет сложно!)
Представьте – вашим сотрудникам ставят оценки и вам нужно посчитать среднее по каждому сотруднику из его последних трёх оценок.
На скриншоте – разбор такой формулы.
В её основе (пункт 1 на скриншоте) – функция COUNTIFS/СЧЁТЕСЛИМН в массиве, она добавляет к диапазону виртуальный столбец с нумерацией по ключу, начиная от последнего элемента (у нас ключ – только имя, но при необходимости вы сможете использовать несколько ключей, просто добавив по аналогии условия в COUNTIFS). Подробнее про принцип работы формулы нумерации здесь.
Вторая часть (пункты 2-3 на скриншоте) – обработка получившего массива с новым столбцом внутри QUERY – оставляем последние три вхождения, группируем и считаем среднюю оценку.
Хочешь узнать, чем занимается BI-аналитик, и научиться визуализировать данные? Открой для себя новую профессию на бесплатном онлайн-интенсиве Skillbox: 🔜https://clc.am/Cb-84g
📊 На занятиях ты попробуешь себя в роли BI-аналитика, узнаешь, как выбирать данные для анализа, научишься строить отчёты в Power BI.
🎓 Прямые эфиры ведёт руководитель отдела аналитики компании «Сантехника-Онлайн», соучредитель performance-агентства Mello и проекта Pixelbuddha Антон Астахов.
🎁 Участвуй в интенсиве, прокачивай знания и получи в подарок электронную книгу «Век живи — век учись», а также те, кто дойдет до конца и выполнит все домашние задания, получит сертификат на 15 000 рублей на любой курс.
Зачастую одни и те же текстовые данные воспринимаются Excel или Таблицами как разные, если они из разных источников (например: одно и то же название товара в выгрузке из 1С и в ваших таблицах будет разным из-за того, что там отличаются... пробелы! Пробелы бывают разными, увы).
Внешне это понять сложно - нужно смотреть на код символа. Его можно определить с помощью функции КОДСИМВ / CODE. Если у внешне одинаковых символов отличаются коды, значит, для Таблиц это разные символы и текстовые ячейки с ними будут тоже считаться разными.
Ну а чтобы достать определенный символ из текста, нужна ПСТР / MID. Если мы хотим сразу разбить текст по символам одной формулой, сделаем формулу массива, и в качестве второго аргумента ПСТР (какой по порядку символ извлекать) укажем функцию SEQUENCE, которая создаст массив от единицы до числа, соответствующего количеству символов в ячейке (его находим с помощью ДЛСТР / LEN): =ArrayFormula(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1))
Чтобы получить массив кодов, а не сами символы, добавим сверху КОДСИМВ: =ArrayFormula(КОДСИМВ(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1)))
Делюсь таблицей с этими формулами, которую делал для одного из слушателей курса на Skillbox: подставляйте текстовые строки в зеленые ячейки и сразу увидите разбивку по символам и их кодам, а условное форматирование "подсветит" ячейки в строках с разными символами. Ячейки с формулами в таблице выделены оранжевым.
Друзья, хотим рассказать/напомнить про возможность применения символьных шаблонов в функции ВПР.
Мы неоднократно рассказывали про применение * (любое количество любых символов, в том числе и нулевое, то есть ничего) и ? (любой один обязательный символ) в функциях семейства СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН), СЧЁТЕСЛИ(МН).
Но их можно использовать и в ВПР. Если мы хотим найти не ячейку с точным совпадением с искомым значением, а ячейку, соответствующую определенной маске, и тут пригодятся звездочка и знак вопроса.
Например, такая функция ВПР будет искать ячейку, которая начинается со слова "Google" и вернет данные из второго столбца таблицы
=ВПР("Google*";Таблица;2;0)
А такая — ячейку, в которых будет встречаться слово iPad с любыми тремя знаками после этого слова через пробел и отделенных пробелом от дальнейшего текста (то есть iPad Pro и IPad Air подойдут, а iPad 10.2 не подойдет)
=ВПР("*ipad ??? *";Таблица;2;0)
Обратите внимание, что если в таблице есть несколько ячеек, которые соответствуют нашему значению — (в данном примере начинающихся с Google и заканчивающихся чем угодно) — будет найдено только первое вхождение по порядку.
P.S. А заодно напомним про РЕГИСТР — он в функции ВПР не учитывается. Даже если в функции будет ipad или iPad, а в таблице для поиска IPAD — все найдется.
Еще посты про ВПР: – ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце) – ВПР (VLOOKUP) по нескольким условиям – ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium. – ВПР по нескольким диапазонам – ВПР с интервальным просмотром = 1 – Перекрестный ВПР (ищем по строке и заголовку) – Видео про функцию ВПР в Google Таблицах
Все ещё считаете, что «умные» электронные таблицы это сложно? Пройдите бесплатный онлайн-интенсив и убедитесь, что базовые вычисления в Excel можно освоить за 3 дня!
✒️ специфику обычных и сводных таблиц; ✒️ функции подсчёта и суммирования, поиска и подстановки данных; ✒️ 10 простых приёмов, как сэкономить время работы в Excel.
Все участники интенсива получат в подарок электронную книгу издательства МИФ «Век живи — век учись». Авторы трёх лучших работ выиграют сертификат на 5 000 рублей, который можно потратить на покупку любого курса в Skillbox.
Друзья, привет! С середины прошлого года мы занимаемся созданием своего приложения для синхронизации онлайн-касс «Эвотор» и таблиц.
Судьба у приложения получилась непростой: сменилось 3 разработчика, но сейчас всё отлично — приложение готово и выложено в магазин Google.
Полезные функции: – чеки вашего бизнеса загружаются в таблицу в «плоском формате» (одна позиция в чеке — одна строка) – загрузка происходит автоматически каждый час – чеки подставляются в отчёты, сейчас их три (общий отчёт по продажам / топ товаров / топ товаров по магазинам) – есть модуль отправки данных в «Телеграм» — сможете настроить регулярную отправку диапазонов Таблиц по расписанию
Планы на февраль: – новый отчёт «на сколько хватит каждого товара» – несколько ABC-оценок товаров
Покупка и отличное предложение для первых клиентов: Стоимость приложения — 5000 рублей за 4 месяца использования.
Сейчас мы набираем ограниченную группу пользователей, до 5 человек. С каждым из пяти мы создадим личный чат для саппорта и фидбэка (сможете написать, какого функционала не хватает конкретно вам, и мы будем добавлять самые популярные варианты)
Участвуйте в специальной программе «Учеба без забот» от GeekBrains и начните проходить курс без первоначального взноса. Вы сможете получить первые знания в новой профессии, присмотреться к рынку труда (или даже найти работу) и оплачивать обучение только после нескольких месяцев.
ВЫГРУЖАТОР: СОЗДАЁМ РЕЕСТР ФАЙЛОВ ИЗ ПАПКИ, ВЛОЖЕННОСТЬ – УЧИТЫВАЕТСЯ
Выпускаем вторую версию скрипта, с помощью которого вы сможете выгрузить реестр файлов в Таблицу. Теперь поиск идёт и по вложенным папкам.
КАК РАБОТАЕТ: – копируйте Таблицу с кодом; – вводите ID или URL папки начала поиска в ячейку B1; – запускайте скрипт из меню [СКРИПТЫ];
Если в B1 напишете root – то выгрузится весь диск, это сработает только для небольших дисков, < 10 000 файлов, из-за максимальной продолжительности выполнения скрипта в 6 минут.
Помимо остальных столбцов – в столбце E – вся информация по файлу. Из этой длинной строки вы сможете вытащить, к примеру, владельца файла или дату его создания.
