​​Считаем, сколько раз символ встречается в строке

Привет, друзья! Наша цель—подсчитать, сколько раз в каждой ячейке диапазона встречается буква "а".

Сначала воспользуемся формулой ПОДСТАВИТЬ(), аргументом будет выбранный символ, подставлять вместо него будем "" (ничего). Это уберет из ячейки все буквы "а".

Дальше с помощью ДЛСТР() посчитаем количество символов в исходной строке и количество символов в той же строке, но без искомого символа. И вычтем одно из другого.


Итоговая формула:
=длстр(a4)-длстр(подставить(a4;"а";""))

P.S. Чтобы учесть прописные:
=длстр(a4)-длстр(подставить(строчн(a4);"а";""))

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

​​Выводим ряд чисел или дат. Одной формулой.

Привет, друзья! Сегодня мы покажем, как с помощью функции СТРОКА() / ROW() вывести ряд дат или чисел с любым шагом.

1. Числа от 5 до 20
=ArrayFormula(СТРОКА(A5:A20))

Разберем функцию по частям:
СТРОКА(A5:A20) возвращает массив {5, 6, 7 ... 20}.
ArrayFormula превращает формулу в функцию массива, без нее выведется только первое значение (5).

2. Текущая дата + 60 дней
=ArrayFormula(СТРОКА(A1:A60)+TODAY()-1)

Добавляем вчерашнюю дату today()-1 к массиву {1, 2, 3 .. 60}.

3. Числа от 1 до 10, шаг 0,5
=ArrayFormula(СТРОКА(A2:A20)*0,5)

Умножаем массив {2, 3, 4 … 20} на 0,5. Получается диапазон {1, 1.5 ... 10}.

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

​​Оглавление канала | Чат | Наша книга| Донаты
Заказы, консультации: @namokonov, @renat_shagabutdinov

Новые возможности Google Таблиц

В конце июля прошла конференция Google Next cloud, на которой в числе прочего анонсировали и новшества в Google Таблицах:

—5 миллионов ячеек в документе
—Срезы (Slicers)
—Улучшения графиков
—Сводные таблицы
—Интеграция с BigQuery и другими сервисами
—Еще новшества одной строкой

Предлагаем вам перевод статьи с описанием нововведений.

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

Google, на конференции Google Cloud NEXT анонсировал появление в Google BigQuery алгоритмов машинного обучения.

BigQuery—мощная облачная база данных, часть Google Cloud Platform. BG можно интегрировать с другими решениями от Google, например с нашими Таблицами.

Во-первых, мы хотим вам дать ссылку на статью Introduction to BigQuery ML (англ.)

Во-вторых, поделиться каналом, который полностью посвящен BigQuery.
С кейсами и примерами решений, новостями и лайфхаками.

Канал так и называется: @BigQuery

​​Друзья, здравствуйте!
Сегодня отличнейший кейс из нашего чата:

Нам нужно извлечь название города из строки (столбец A).
Все названия городов, которые могут встречаться в строках у нас есть (столбец D).

Извлекать будем с помощью =REGEXEXTRACT()

Эта функция позволяет использовать в запросе регулярное выражение. Нам нужно только перечислить в запросе все города со знаком "|" (или).

Запрос можно написать текстом. Допустим, у нас три города:
=REGEXEXTRACT(A2;"Moscow|Rostov|Samara")

Но если городов много—лучше добавить функцию =JOIN, она позволит объединить в одну строку все варианты городов из столбца D с выбранным нами разделителем (|) =JOIN("|";D2:D15)

Итоговая формула:
=REGEXEXTRACT(A2;JOIN("|";$D$2:$D$15))

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

​​Страх и ненависть в колл-центре.

Друзья, сегодня—особенная формула. Мы ее показываем только для того, чтобы вы знали, на какие чудеса способны Таблицы.

У нас Таблица звонков колл-центра. Номера телефонов и статусы.

Нужно:
1. исключить номера со статусами BUSY, PROPER, ANSWERED
2. отфильтровать оставшиеся по статусу NO ANSWER
3. сгруппировать то, что осталось и вывести только номера, на которые совершили больше пяти звонков

Все решается одной формулой:

  =query(query(filter(A2:A;isna(match(A2:A;filter(A2:A;regexmatch(B2:B;"ANSWERED|PROPER|BUSY"));0));B2:B="NO ANSWER");"select Col1, count(Col1) group by Col1");"select Col1 where Col2>=5")

Препарируем формулу в Medium | Таблица с примером

​​onEdit() скрипт для выпадающих связанных списков 🔥🔥

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

Пример—из школы танцев. Нам нужно в зависимости от выбранного в столбце A партнёра сформировать в ячейке столбца B выпадающий список из партнёрш, которые с нашим партнером танцуют (Таблица в диапазоне D2:G4).

Наш скрипт будет запускаться при редактировании первого столбца "Листа1" (специально вывели и столбец и лист в условие скрипта, чтобы вы смогли поменять на то, что нужно вам).

Чтобы скрипт заработал после копирования Таблицы, вам нужно открыть Инструменты > Редактор скриптов и запустить его в первый раз вручную, авторизовав.

Таблица c примером / и сам скрипт

function onEdit(e)
{
var range = e.range
var sheet = e.source.getActiveSheet();

if(range.getColumn()===1 && sheet.getName()==='Лист1'){
var key = e.value
var listdata = sheet.getRange("D1:G4").getValues().filter(function(row){return row[0] === key})[0]
var list = []
for(x=1; x<listdata.length; x++){list.push(listdata[x])}

range = range.offset(0, 1)
var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
range.setDataValidation(rule);
}
}

​​Ищем доступные по ссылке документы (включая Таблицы) на Google Диске перед тем, как их найдут другие.

Друзья, привет! Пост написан по мотивам недавней истории с документами Google Диска, попавшими в индексацию.

Мы подготовили для вас специальную Таблицу. Скрипт в Таблице найдет на вашем Google Диске все файлы с формой доступа:

- anyoneCanFind (индексируется)
- anyoneWithLink (потенциально может индексироваться)

Как это работает:
1. Сделайте копию нашей Таблицы (файл>создать копию)

2. Запустите скрипт из меню Скрипты ↓↓ (посмотреть код можно открыв инструменты>редактор скриптов в Таблице)

3. Скрипт выведет на лист все ваши потенциально индексируемые файлы и ссылки на них. Особенно стоит обратить внимание на форму доступа "anyoneCanFind"—это ваши документы, которые можно найти через поисковики.

4. Кол-во документов для вывода ограничено 300, если вам не хватит—измените цифру в пятой строке коде. Но, не забывайте, что у GAS скриптов есть суточные квоты и на вывод несколько тысяч документов их может не хватить :)

​​Друзья, сегодня рекомендуем вам мощный дружественный канал, на котором вы найдете
—полезные материалы по аналитике (как работает Google Analytics на самом деле?)
—ссылки на хорошие курсы по аналитическим инструментам и программированию

Школа бородатого веб-аналитика: @schoolWA

​​Звездочки и тильды ~ в СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН)

Друзья, мы обратили внимание, что функции СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН) не работают, если в текстовых значениях есть звездочки * и тильды ~
Вот такая незадача. Если что-то одно из этих символов - нет проблем. Если оба символа сразу - то такие строки не будут попадать в расчет.

Как быть? Можно обойти эту проблему многими вариантами, вот пара самых простых:

1. С помощью функции FILTER. По сути, вместо точки с запятой между диапазоном и условием ставите знак "равно" =, и добавляете снаружи функцию СУММ/SUM:

 =СУММ(FILTER(C:C;A:A=E2))

2. С помощью QUERY. Можно сразу рассчитать сумму по всем значениям с помощью кляузы GROUP BY.

Можно придумывать и экзотические варианты.
Например, удалять один из двух символов из исходного столбца с помощью формулы массива:

 =СУММЕСЛИ(ARRAYFORMULA(ПОДСТАВИТЬ(A:A;"~";""));ПОДСТАВИТЬ(E2;"~";"");C:C)

Вот этот фрагмент будет возвращать исходный столбец А, но без знаков "тильда":

 ARRAYFORMULA(ПОДСТАВИТЬ(A:A;"~";""))

Во втором аргументе то же самое будем делать с критерием (искомым названием товара) из E2.
И далее все это обрабатывать СУММЕСЛИ.

P.S. Увы, все эти варианты работают медленнее, чем простая СУММЕСЛИ.

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

Telegram bot + Google Sheets (используя вебхуки)

Друзья, привет! Сегодня создаем Telegram бота, связанного с Google Таблицей.

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

После того, как вы поймете, как работает код—вы сможете адаптировать его под свои нужды. Например, пишете боту в Telegram и он отправляет письмо с данными из Таблицы работнику, или начинает собирать несколько Таблиц в одну и конвертирует её в PDF, в общем - всё, что вы сможете придумать :)

План действий:
1. Создадим бота в Telegram и получим токен авторизации.
2. Напишем Google Apps Script, управляющий ботом. Вставим в него токен.
3. Развернем скрипт как веб-приложение.
4. Зарегистрируем вебхук.
5. Всё, будем пользоваться 😎

https://medium.com/@grclubpoker/bot-happens-telegram-bot-google-sheets-on-webhooks-e415509a6213

​​Создаем диаграмму Ганта с помощью формул (визуализируем график работ)

Друзья, мы подготовили для вас Таблицу с решением. Детализация — по месяцам и по дням (смотрите вторую вкладку).

В каждой ячейке отчёта использована функция QUERY.

Детализация до месяца:

=if(isna(QUERY($A$1:$C;"select * where
(A='"&$E2&"' and month(B)+1<="&month(F$1)&" and month(C)+1>="&month(F$1)&")";0));0;1)

Детализация до дня:

=if(isna(QUERY($A$1:$C;"select * where
(A='"&$E2&"' and B <= date '"&text(F$1;"yyyy-mm-dd")&"' and C >= date '"&text(F$1;"yyyy-mm-dd")&"')";0));0;1)

В формуле проверяются два условия:
1. проект начинается или заканчивается в выбранную дату гистограммы
2. проект продолжается в выбранную дату гистограммы

Если любое из условий выполняется — формула возвращает 1 для этого проекта и месяца.

Условное форматирование:
Выводить 1 и 0 не обязательно, можно создать на основе нашей формулы правило условного форматирования. Для этого добавьте после формулы =1 (или измените 0;1 на false;true) Правило закрасит все ячейки, для которых формула будет истиной.

Таблица с решением

​​Считаем рабочие дни между двумя датами в 2018 правильно. Учтём все выходные, праздники и переносы.

Друзья, привет. Функция ЧИСТРАБДНИ.МЕЖД / NETWORKDAYS.INTL считает рабочие дни между двумя датами.

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

Cтуктура выходных задается строкой, начиная с понедельника. 0 - рабочий день, 1 - выходной. Строка "0000000" будет означать, что все дни на каждой неделе - рабочие. Такая структура нам и нужна, нет, мы не против выходных, совсем наоборот - мы хотим их учесть правильно.

В Таблице с примером в столбце E:E мы выписали все праздники и выходные дни (сб и вс), с учетом всех переносов по производственному календарю РФ за 2018 (получилось каких-то 118 дней).

Этот массив мы добавили в диапазон дней-исключений (последний аргумент ЧИСТРАБДНИ.МЕЖД) и теперь можем легко посчитать правильное количество рабочих дней между любыми датами в 2018.

 =NETWORKDAYS.INTL("01-01-18";"31-01-18";"0000000";$E$2:$E) 
//17

 =NETWORKDAYS.INTL("01-01-18";"31-12-18";"0000000";$E$2:$E) 
//247

Без дней-исключений и без выходных:

 =NETWORKDAYS.INTL("01-01-18";"31-01-18";"0000000")

//ожидаемые 31

Считаем рабочие дни между двумя датами в 2018 правильно. Учтём все выходные, праздники и переносы.

Видеурок. WHERE в функции QUERY.

Друзья, сегодня вашему вниманию видеоурок из курса по Таблицам в Skillfactory.
Он посвящен кляузе WHERE в функции QUERY.

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

P.S. Увы, в нашей копии этого видео не очень громкий звук - и только в моно-режиме на одно ухо. Надеемся, это не помешает восприятию материала :)

Видео | Google Док (описание + скриншоты)

​​Выводим ближайшие к заданному числу (дате) значения из диапазона.

У нас есть массив элементов (смотрите скриншот), отберём из него три ближайших значения к числу три.

Поехали!
1. Вычитаем из каждого элемента массива наше число:

 =ArrayFormula({A3:A11-3})

2. Берем модуль результата и добавим к нему столбец исходного массива:

 =ArrayFormula({A3:A11\abs(A3:A11-3)})

3. Добавляем результат в формулу QUERY, сортируем по столбцу с модулем Col2, оставляем верхние три элемента limit 3 и выводим только столбец исходного массива Col1.

Итоговая формула:

 =query(ArrayFormula({A3:A11\abs(A3:A11-D1)});
 "Select Col1 order by Col2 limit 3")

Бинго, у нас все получилось правильно 🤘

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

​​Спарклайны: некоторые примеры применения

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

— Гистограмма
— Выше определенного уровня или нет?
— Позиционное сравнение
— Выигрыш/проигрыш: визуализация попыток спортсменов

​​Расписание групповых занятий в Google Таблицах.
Выделяем тренеров и сортируем по времени.

Друзья, доброе утро. Недавно для нашего клиента — крупной московской школы плавания мы создали расписание тренеров (или групповых занятий).

К расписанию было всего два требования :  простота в чтении и минимизация ошибок при заполнении.

Мы публикуем для вас сокращенный вариант расписания (в оригинале тренеров и локаций было больше).

Красочно выделяем тренеров
За простоту чтения у нас отвечает возможность выбрать (и тем самым выделить) тренера чекбоксом ✅ напротив его фамилии. Это—простое правило условного форматирования (формулой), его нужно создать для каждого тренера. Для Иванова оно будет =and($A$4=A3;$B$4=true), диапазон правила при это должен начинаться с А3, иначе все уедет и выделится другая ячейка.


Фильтр: достаем из расписания выбранную дату и сортируем по времени
Такую возможность мы сделали на отдельном листе. Сначала, с помощью =FILTER мы достаем день, выбранный в выпадающем списке:
=FILTER('Расписание'!F3:AJ; 'Расписание'!F1:AJ1=B1)

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

Итоговая формула:
=query({'Расписание'!D3:E\filter('Расписание'!F3:AJ; 'Расписание'!F1:AJ1=B1)};"SELECT Col2, Col1, Col3 WHERE Col3 is not null order by Col2";0)

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