=SUBTOTAL(номер функции ; диапазон ; [еще диапазон]; ...)
Номер функции определяет, какая операция будет производиться. Функций всего 11 - стандартный набор, который, например, есть и в вычислениях сводных таблиц Excel (в Google к нему в сводных еще добавляется подсчет уникальных значений).
Вот базовые функции (кроме них есть еще стандартное отклонение и дисперсия):
1 и 101 - среднее
2 и 102 - количество чисел
3 и 103 - количество значений
4 и 104 - максимум
5 и 105 - минимум
6 и 106 - произведение
9 и 109 - сумма
Каждая функция бывает в двух вариантах - коротком (9 или 11, например) и длинном из трех цифр (109 или 111).
Короткий вариант - подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.
Длинный вариант - подсчет только отфильтрованных строк, без скрытых вручную.
Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.
Для столбцов функция работать не будет. То есть если применить ее к горизонтальному диапазону и скрыть столбцы, то они все равно попадут в расчет при любом коде функции.
Таблица с примером (Создать копию)
P.S. В Excel функция работает аналогично. Кроме того, если вы создаете "Таблицу" (Ctrl + T) и затем строку итогов (Total Row) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".
Google Таблицы. Страница 19
17221
@google_sheets
#GoogleТаблицы — это аналог #Excel с веб-интерфейсом и возможностями для удобной совместной работы. Они похожи, и все же в Таблицах есть свои особенности, функции, плюсы и минусы. Работать в Google Таблицах просто и мы покажем как.
-
Google Таблицы
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL: подсчет только отфильтрованных строк Эта функция позволяет производить вычисление только с видимыми строками. У нее такой синтаксис: -
Google Таблицы
Знание Excel и Google-таблиц нужно во многих профессиях. Вы бухгалтер или экономист — нужно делать расчеты и проектировать отчеты. Маркетолог — нужно планировать продажи и сравнивать эффективность рекламных кампаний. Также таблицы нужны менеджерам, предпринимателям и даже медсестрам! Научитесь работать с таблицами на базовом и продвинутом уровнях всего за 4 месяца с курсом от Skillbox! После обучения ваше резюме пополнится такими навыками: ➕ создание диаграмм и спарклайнов; ➕ создание макросов для VBA; ➕ работа с функциями проверки данных; ➕ прогнозирование различных показателей; ➕ умение быстро фильтровать большие массивы. Новогодняя распродажа – скидки до 60% весь декабрь. 💥При покупке этого курса в декабре каждый получит скидки от партнёров: Asus – 10% на ноутбуки. Литрес – 25% на все книги + 2 книги в подарок. Clinique – 40% на всё, кроме новогодних коллекций. КЭСПА - 10% на занятия c преподавателем. Переходите: clc.to/G9GM0Q и выбирайте подарок себе или близкому человеку со скидкой до 60% #реклама 
-
Google Таблицы
Custom Functions: пользовательские функции в Google Apps Script Когда в Таблицах не хватает встроенных функций, можно быстро написать свою. Как это сделать, описано в официальной документации. Пару замечаний по пользовательским функциям написал Михаил Смирнов в новой статье. Благодарим Михаила за полезный материал! telegra.ph/Google-…hi-11-04 -
Реклама
-
Google Таблицы
Хотите освоить визуализацию данных на реальных задачах — попробуйте бесплатный курс-симулятор от Нетологии и Yandex.Cloud. Ваш персонаж — аналитик в крупной ритейл-компании, а каждое занятие — история с прописанным сюжетом. Вы построите интерактивную карту клиентов в DataLens, соберете отчёт-конструктор расходов в Excel, сравните показатели в Power BI, а в конце наведёте красоту в Tableau. Узнаете много нового и познакомитесь с основными инструментами визуализации данных. А ещё ваш дашборд может выиграть приз, подробнее → https://netolo.gy/hkO #реклама -
Google Таблицы
DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы | @google_sheets Друзья, привет! И у нас чёрная пятница – выкладываем для вас наш большой скрипт совершенно бесплатно. — Хотите из Таблицы контролировать свои рабочие файлы и папки и следить за тем, кому они расшарены? — Пришел новый сотрудник? Вы сможете добавить его к нужным файлам за несколько минут. — Старый сотрудник перестал оправдывать доверие и вы решили с ним попрощаться? Удалим его из файлов, пока он ничего не удалил сам :) 🔥 Таблица со скриптом (делайте копию) 🔥 Инструкция P.S. А без помощи Игоря из Японии это решение бы не состоялось, спасибо ему! 🎎 ❗️❗️ Друзья, у кого не заработал скрипт – удалите в редакторе скриптов Drive Api и активируйте заново (не только в редакторе скриптов, но и в проекте); 
-
Google Таблицы
Удалили нужный файл на Google Drivе? Рассказываем, что делать. Друзья, Александр Иванов написал про то, что делать, если вы случайно удалили нужный файл – категорически рекомендуем: gdriveru.blogspot.com/2021/11/restorefiles2.html ★ Чат: @google_spreadsheets_chat ★ Больше постов в оглавлении нашего канала: goo.gl/HdS2qn -
Google Таблицы
Выгружатор постов / просмотров / картинок из каналов Telegram Сегодняшнее решение будет актуально для владельцев каналов Telegram, а также для тех, кто хочет быстро спарсить текст и просмотры постов прямо в Таблицу. Как воспользоваться: 0. Посмотрите гифку :) 1. Копируете Таблицу с кодом к себе; 2. Заполняете лист настройки – вам нужно ввести ссылку на стартовый пост и количество постов от стартового, которое скрипт попробует спарсить; 3. Запускаете скрипт из меню с 🐧 (наверное, это скворец); 4. Скрипт вставит результат на лист "логи" и обновит дату / время на листе "настройки"; Как скрипт в принципе работает: 1. Берёт стартовый пост (например, t.me/google_sheets/739), берёт количество постов, которое мы хотим выгрузить (например, 3); 2. Создаёт массив ссылок добавляя к стартовому посту 1, [t.me/google_sheets/739, t.me/google_sheets/740, t.me/google_sheets/741] Поэтому, если на канале был пост, потом его удалили и эта ссылка попала в массив - по ссылке не вернется ничего❗️; 3. Далее весь массив ссылок запрашивается с помощью fetchAll(); Общая квота запросов на google-аккаунт – 20 000 запросов на бесплатном и 100 000 на google workspace, но если вы запросите сразу 1000 постов – то по части запросов сервер Telegram может не вернуть ничего, даже если посты были, подумав про вас и про ваш IP-адрес нехорошее, поэтому – запрашивайте умеренно ❗️; 4. Из результата по каждой ссылке с помощью регулярных выражений скрипт достаёт текст поста / просмотры / картинку; Только код: pastebin.com/RbGjK7ju Таблица с кодом: Таблица Документация: fetchAll, квота запросов в день PS Идеи что добавить к функционалу – напишите в комментариях. -
Google Таблицы
Простые лайфхаки для Google Диска / Документов / Презентаций
Друзья, не Таблицами едиными жив человек!
Мы решили собрать несколько простых приемов и горячих клавиш для тех, кто пользуется Google Диском и Документами/Презентациями.
Сгруппировали их так:
- Google Диск в целом
- То, что работает в Таблицах/Презентациях/Документах
- Документы + Презентации
- Только Документы
- Только Презентации
Пишите в комментариях, какие приемы и клавиши помогают вам — мы добавим их в статью!
Ссылка на статью в телетайп (отличие от предпросмотра – скриншоты не будут искажены): https://teletype.in/@renat_shagabutdinov/J0TKzjV7M
=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты -
Google Таблицы
29–31 марта в 19:00 по московскому времени руководитель проектов в Partners in Performance Russia Александр Савицкий расскажет много нового и интересного о профессии операционного менеджера.
Регистрируйтесь: 🔜 https://clc.am/6wqvTw
⚡️ Ты узнаешь, кто такой операционный менеджер и чем он занимается.
⚡️ Поймёшь разницу между управлением проектами и операционным менеджментом.
⚡️ Определишь для себя, что такое управление, основанное на фактах, и узнаешь, как внедрить его в работу.
⚡️ Сможешь повысить свою эффективность или освоить востребованную профессию.
🎁🎁🎁 Авторы трёх лучших работ получат сертификат на 30 000 рублей на любой курс в Skillbox.
#реклама -
Google Таблицы
Идём на веб-страницу → находим ссылку на нужный EXCEL файл → добавляем в Таблицу и всё это скриптами
Друзья, у Романа Игнатова, старожила и модератора нашего чата вышла очень полезная статья, делимся:
https://ignatov-script.blogspot.com/2019/01/google-i.html -
Google Таблицы
Создаешь отчетность? Работаешь с анализом данных или статистикой? Пришло время подняться на ступень выше и стать продуктовым аналитиком!
Информации для анализа уже слишком много для Excel или Google Таблиц. Крупные компании переходят на собственные системы аналитики. Создают отчеты, дашборды, графики и отслеживают изменения в режиме реального времени.
Для этого требуется отдельный сотрудник со знаниями программирования и опытом создания подобных проектов. На курсе «Профессия Продуктовый аналитик» дается все необходимое для входа в профессию.
Ведущие эксперты отрасли обучат основам языков Python, SQL, R для работы с данными.
Научат работать с разными видами аналитики:
1. Веб−аналитика. Узнаете, как анализировать поведение пользователей, эффективность сайта и трафика. Сможете строить систему метрик для продукта и повышать отдачу от рекламы.
2. Аналитика мобильных приложений. Научитесь оценивать эффективность рекламных каналов, отслеживать выручку и привлекать аудиторию. Настроите аналитику на реальном приложении, улучшите показатели монетизации и конверсии.
3. Сквозная аналитика. Узнаете как объединять данные из разных систем в единую инфраструктуру. Сможете собирать данные из разных систем и находить инсайты для развития продуктов.
Переходи прямо сейчас https://clc.am/jikmcg и регистрируйся на курс.
#реклама
-
Google Таблицы
Скрипт внутри Google Формы:
— берём последний ответ и отправляем его в Таблицу
— отправляем в телеграм чат
— отправляем на электропочты
Друзья, доброе утро, мы решили писать скрипты везде, даже внутри Google Формы. Там тоже можно. Сегодня у нас пример простого скрипта, который берёт последний ответ (последнее заполнение Формы), добавляет к нему наш текст и отправляет эту строку в Таблицу, которую мы указали в коде.
Целевой скрипт, код с подробными комментариями: https://pastebin.com/WesF5ky3
Чтобы скрипт работал автоматически и запускался при каждом новом заполнении формы (вот как на гифке строка в Таблицу добавилась как только я нажал "отправить") скрипт нужно поставить на триггер: триггеры → добавление триггера → выбираем название целевой функции → при отправке формы.
Добавляем отправку в телеграм / на почту
Вот здесь: https://pastebin.com/V48MQ4kS простые функции отправки в телеграм чат и на емейл. Мы снабдили функции подробными комментариями и даже новичок (если прочитает всё внимательно) сможет разобраться.
Совместив этот код с целевой функцией вы сможете отправлять сообщение о том, что форма заполнена не только в Таблицу, а еще всем заинтересованным товарищам, как на почту, так и в чаты мессенджера. В тексте сообщения, конечно, можно использовать ответы формы, как к ним обратиться – мы показываем в целевой функции.
P.S. Чтобы провалиться в редактор скриптов формы – открываем её на редактирование → жмём кебаб (три точки справа - сверху) → редактор скриптов.
=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты -
Google Таблицы
Ошибки в формулах Google Таблиц
А-а-а! Формула не работает! Что делать (кроме как сходить к коллеге, у которого больший опыт в Таблицах)?
В сегодняшней статье разбираем:
- какие типы ошибок бывают в Google Таблицах и почему каждая из них может случиться;
- несколько типовых ошибок со сложными формулами;
- несколько простых приемов, которые помогут написать/разобрать/починить сложную формулу;
- функции, которые помогают "отлавливать" ошибки или отображать код ошибки/наличие конкретной ошибки.
Благодарим Михаила Смирнова за помощь в подготовке статьи
Для тех, кто в веб-версии и не видит ссылку: https://teletype.in/@renat_shagabutdinov/fgeGs-67J -
Google Таблицы
Работаем со вшитыми (богатыми 😊) в ячейки ссылками
Примеры простых скриптов
Друзья, встречалась вам ситуация, когда приходилось обработать столбец, в котором были вшитые в текст ссылки?
Такие ссылки нельзя отделить от текста с помощью формул, но можно используя скрипты, а именно метод RichTextValue.
Простой скрипт: выделяем диапазон ссылок → запускаем скрипт → получаем три столбца (текст отдельно, ссылка отдельно, формула с ссылкой и текстом):function fromUrl() {
//определяем активный диапазон, активный лист и забираем значения
const range = SpreadsheetApp.getActiveRange();const sheet = SpreadsheetApp.getActiveSheet();const richText = range.getRichTextValues();
//наполняем в цикле массив: текст, ссылка, формулаconst values = richText.map(g => [g[0].getText(), g[0].getLinkUrl(), `=HYPERLINK("${g[0].getText()}";"${g[0].getLinkUrl()}")`]);
//вставляем, ориентируясь на границы выделенного диапазонаsheet.getRange(range.getRow(), range.getColumn() + 1, values.length, values[0].length)
.setValues(values)
}
И обратное превращение: выделяем диапазон [текст, ссылка] → получаем вшитые в текст ссылкиfunction toUrl() {
//определяем активный диапазон, активный лист и забираем значения, создаём пустой массив
const range = SpreadsheetApp.getActiveRange();const sheet = SpreadsheetApp.getActiveSheet();const values = range.getValues();let richText = [];
//наполняем массив в цикле, добавляя вшитые ссылки (RichTextValueBuilder)values.forEach(g => {
richText.push([SpreadsheetApp
.newRichTextValue()
.setText(g[0])
.setLinkUrl(g[1])
.build()]);
});
//вставляемsheet.getRange(range.getRow(), range.getColumn() + 3, values.length, 1)
.setRichTextValues(richText)}
Таблица с кодом, в которой все работает
Код с комментариями отдельно (если сложно читать в посте)
=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты -
Google Таблицы
Отправлятор СМС 2 (+ добавили автоматическую отправку)
Друзья, привет! Когда-то давно мы написали пост про отправку СМС из Таблицы: в нём про сервис, который можно использовать и как формировать правильные ссылки для отправки.
В первой версии на каждую ссылку нужно было кликнуть, перейти и после этого сообщение отправлялось, сейчас апдейт – больше вам кликать никуда не надо, мы добавили для этого скрипт.
Инструкция
Таблица со скриптом
P. S. Спасибо нашему чату за идею.
Предлагайте в комментарии темы для будущих постов, про самое интересное напишем на канале.
=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты -
Реклама
-
Google Таблицы
Пользовательские форматы в Google Таблицах
Друзья, посмотрите на эту красоту на скриншоте. В столбце D "Прирост" нет условного форматирования или картинок. Это обычный числовой формат! Но не простой, конечно, а пользовательский. Пользовательские форматы - мощная штука, позволяющая создавать числовой формат специально под ваши задачи с помощью специальных кодов. Например, у формата ячеек на скриншоте код такой:[Color10]+0% * ▲;[Color53]-0% * ▼Чтобы разобраться со всем этим - заглядывайте в нашу статью. Там все о пользовательских форматах в Таблицах (Кстати, в Excel они работают практически аналогично):
— где они настраиваются
— какие символы используются в пользовательских числовых форматах
— как настроить отдельные форматы для положительных и отрицательных чисел, нуля и текста
— как добавить цвет в форматы
— как добавить условия в пользовательских форматах - и какие там есть ограничения
— пользовательские форматы для номеров телефонов
— округление до тысяч и миллионов с помощью формата
— выравнивание по десятичной запятой
— заполнение ячейки определенным символом
— функция ТЕКСТ
— пользовательские форматы даты и времени
https://shagabutdinov.ru/custom_format/
Файл со всеми примерами из статьи
=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты -
Google Таблицы
СОБИРАТОР 4.0
В прошлом году мы выпустили свой СОБИРАТОР – интерфейс для сбора других Таблиц через sheets api (самый быстрый скриптовый способ).
Задаёте правила сбора Таблиц в другие Таблицы и собираете, к примеру, 50 Таблиц ваших продавцов в одну сводную Таблицу и 30 Таблиц в другую сводную Таблицу, на лету фильтруя и оставляя только нужные столбцы.
Сегодня выпускаем обновление:
— теперь можно указать частоту загрузки каждой таблицы в минутах;
— можно выбрать тип копирования данных (formula / formatted_value / unformatted_value);
— решена проблема с добавлением новых данных: раньше они (иногда) вставлялись со сдвигом;
Главное:
Решение стало полностью автоматическим – теперь один раз заполняете конфиг сбора, выставляете частоту обновления в минутах, ставите скрипт в расписание и вуаля – данные собираются регулярно и уже без вашего участия.
СОБИРАТОР
ИНСТРУКЦИЯ
=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты -
Google Таблицы
Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц).
Недавно в нашем чате спросили–а как запускать скрипты, если у меня ведройд.
Автор вопроса имеет ввиду, что у него телефон на базе платформы Android c приложением Таблицы и там в Таблицах не работают скрипты, привязанные к кнопкам и не видны меню скриптов.
Но способ запускать скрипты есть:
1) создаём функциюstarter()с параметромe(объектом события), эта функция сможет определить ячейку / лист, которые редактируются сейчас.
В функции – ЕСЛИ, проверка – редактируется ли ячейка на Листе1 ячейка А1. Если истина – запускаем нашу целевую функциюhellogirls().function starter(e) {
if (e.source.getActiveSheet().getName() == 'Лист1' && e.range.getA1Notation() == 'A1') { hellogirls();}
2) Ставим функциюstarter()на триггер (редактор скриптов > триггеры > добавление триггера > starter > при редактировании Таблицы);
3) Профит! Теперь при изменение чекбокса на Лист1 запускается функцияhellogirls(). В том числе с мобильного устройства!