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

Сегодня рассмотрим, как можно сделать ежедневную рассылку из Google Таблицы. Пример достаточно простой (мы убрали из него все регулярные выражения), поэтому, разобравшись, вы легко сможете адаптировать его под свои нужды. Все здесь: http://telegra.ph/Rassylator-09-15

📚Оглавление канала: https://goo.gl/HdS2qn

REGEXREPLACE. Находим и заменяем текст в ячейке, соответствующий регулярному выражению

Друзья, сегодня рассмотрим третью функцию Google Таблиц для работы с регулярными выражениями.
Это REGEXREPLACE, позволяющая заменить отдельные фрагменты, соответствующие регулярному выражению, на заданный текст.

Синтаксис:
=REGEXREPLACE (текст, в котором ведем поиск; рег.выражение - что заменяем в тексте ; текст, на который заменяем)

Например, функция:
=REGEXREPLACE(A1;"машин[а-я]{1}";"автомобиль")
Заменит в тексте из ячейки A1 все слова "машина", "машину" и другие ("машин" + любая буква) на "автомобиль".

Смотрим на GIF:
https://goo.gl/k8MtK2

Хорошего дня!

#Простыевопросы: —Подскажите, как реализовать вывод 0 если функция filter ничего не нашла по выбранному диапазону? —Изи! Воспользуйтесь функцией Iferror (Еслиошибка).

Друзья, доброе утро.

Наверняка вы знаете, что с помощью IMPORTHTML можно заимпортировать таблицу из веб-страницы.

Например, формула в ячейке А1 скриншота (https://goo.gl/V4rr86) выведет таблицу из coinmarketcap.com, курс и параметры 100 криптовалют:

🚬 а как вывести не всю таблицу, а только нужные нам поля?

Добавим функцию QUERY и выведем только 5 верхних строк:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT * LIMIT 5")

Только 1-ий, 3-ый столбец (обращаясь к ним Col+ номер столбца в источнике данных) и 10 строк:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT Col1, Col3 LIMIT 10")

Чтобы пропустить часть строк, используйте OFFSET, выведем строки с 11-ой по 15-ую:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT * LIMIT 5 OFFSET 10")

Разумеется, это будет работать не только с выбранной веб-страницей, а вообще с любой, на которой есть таблица или список :)

Хорошего дня!

📚Оглавление нашего канала: https://goo.gl/HdS2qn

Диаграмма "Дерево" для отображения организационной структуры или других иерархий.

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

Данные для нее должны выглядеть несколько необычным образом:
- в первом столбце - дочерние элементы;
- во втором столбце - элементы верхнего уровня;
- в третьем - необязательно - примечания (они будут всплывать при наведении курсора на элемент).

В примере мы создали три строчки с CEO (верхний уровень), у которого три функциональных директора - подчиненных (они указаны в первом столбце).

И по две строчки на двух директоров, где они указаны уже во втором столбце, а их дочерние элементы (подчиненные) - в первом.
Как видите, CEO (самый верхний элемент) в первом столбце вообще не встречается.
А сотрудники, у которых нет подчиненных, наоборот, не встречаются во втором столбце.

Вот такая немного замысловатая структура данных для этой диаграммы.

ГИФ (открывайте по ссылке, пожалуйста): https://goo.gl/Xz6Vr2

Друзья, как думаете, сколько нужно формул, чтобы отобрать из таблицы данные по условию, объединить построчно, добавить текст и превратив всё, например, в 30-ть ссылок апи? Или в поздравительные письма для имениников этого месяца?

Достаточно формулы, которую вы введёте только в одну ячейку. Нам поможет функция FILTER и объденинение диапазонов (и диапазонов с текстом либо с содержимым какой-то ячейки) с помощью амперсанда (&).

Пример в ГИФ: http://recordit.co/dJhWKjSiL7 / Документ: https://goo.gl/Ux7QsP

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

Попробуем ответить на несколько вопросов по этим данным и так уж получилось, что формулы в этот раз вышли довольно угрожающими :)

Подобные, большие и страшные формулы, проще читать изнутри, начиная от вложенных элементов, в примере 1, мы начинаем с того, что используя QUERY отбираем по каждой площадке дату-время последнего чека, далее полученный массив используем в FILTER, как диапазон элементов условия, оставляя в таблице чеки (строки) только за это время.

1) Как вывести последний чек по каждой пиццерии?
=FILTER(A1:D11;NOT(ISERROR(MATCH(B1:B11;QUERY(A2:B11;"select max(B) group by A";0);0))))

2) Как вывести первый чек за сегодня по каждой пиццерии?
=FILTER(A1:D11;NOT(ISERROR(MATCH(B1:B11;QUERY(A1:B11;"select min(B) where B>=date '2017-10-16' group by A";0);0))))

3) И выведем сумму чеков за вчерашний день по каждой точке продаж:
=QUERY(A1:D11;"select A, sum(D) where B>=date '2017-10-15' and B<date '2017-10-16' group by A")

Желаю всем хорошего вечера!

ГУГЛТАБЛИЧКА: https://goo.gl/n7PaJE
📚Оглавление нашего канала: https://goo.gl/HdS2qn

Друзья, мы рады поделиться с вами новостью о выходе нашей книги в издательстве МИФ!

Это первое в России издание, посвященное редактору электронных таблиц от Google.

Книга доступна в электронном формате на сайте МИФа. И - что замечательно - ее выход совпал с запуском "электронной корзины" на сайте издательства, в честь чего скидка на все электронные книги составляет 50% до завтрашнего дня! То есть книгу можно приобрести всего лишь за 199 рублей.

Ну а после окончания акции - для тех наших подписчиков, кто не успел приобрести книгу по акции - мы предоставляем промокод на скидку 20%, которая будет суммироваться с другими скидками. Промокод действует до 30 октября: ndrkjl3

Книга на сайте издательства:
https://www.mann-ivanov-ferber.ru/books/google-tabliczyi-eto-prosto/

P.S. Материал в книге дополнен по сравнению с первой версией. Так что обновилась не только обложка :) добавился материал в главах про сводные, фильтры и спарклайны. Те, кто приобретал предыдущую версию, могут написать нам и получить новую версию бесплатно.

Друзья, всем привет.

Наш сегодняшний кейс о том, как вывести финансовый месяц по нужной нам дате (например, 19-10-2017) из таблицы вида:

дата начала периода | фин. месяц | фин. год
...
15-09-2017 | 08 | 2017
18-10-2017 | 09 | 2017

Нам поможет интервальный просмотр = 1, он есть в функциях ВПР и ПОИСКПОЗ (MATCH). Он ищет ближайшее меньшее значение в диапазоне поиска к искомому. Таблица должна быть отсортирована по столбцу поиска, по возрастанию (можно и формулой).

1) =MATCH(E1;A1:A10;1) - возвращает 2, это номер нужной нам строки
2) =INDIRECT("B"& MATCH(E1;A1:A10;1) ) - добавляем название столбца, который мы хотим вывести и превращаем текст в настоящую ссылку на ячейку с помощью INDIRECT(ДВССЫЛ)

P.S. показатели, равные 1, вводить не обязательно, MATCH(E1;A1:A10;1) = MATCH(E1;A1:A10)
https://goo.gl/d7FE1g

А вот так, с помощью СМЕЩ(OFFSET) и того же MATCH мы можем вывести сразу месяц и год.

Друзья, доброе утро.

Сегодня будем составлять ссылки АПИ (много) и опять с помощью формулы, введенной только в одну ячейку.

Первый пример, простой.

У нас есть:
а) начало ссылки, статичное, то есть, не будет меняться во всех вариантах: https://vk.com/
б) список элементов, которые мы хотим подставить после com/
в) конец ссылки, статичный текст, который будет завершать эту конструкцию

Будем использовать функцию FILTER, программисты Google её волшебным образом объединили с формулой массива и чтобы получить ряд ссылок, нам достаточно ввести формулу, как на скриншоте.

Переводя формулу на русский: мы берем ячейку А2, добавляем все непустые значения из B2:B6 (построчно) и добавляем ячейку С2.

Прекрасно тут то, что если данные в столбце В будут перемежаться с пустыми ячейками (или вы зададите диапазон с запасом, например В2:В10) - формула отсеет все пустые строки и вы увидите тоже самое, что на скриншоте.

P.S. и чтобы формула вообще работала, диапазон вывода (в нашем случае filter( a2& b2:b6 &c2) должен совпадать с диапазоном отбора =filter(a2&b2:b6&c2; b2:b6<>"")
(https://goo.gl/8ij9ff).

Символьные шаблоны в функциях СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

В критериях функций СУММЕСЛИ/SUMIF, СЧЁТЕСЛИ/COUNTIF, СРЗНАЧЕСЛИ/AVERAGEIF можно использовать знак звездочки (*), заменяющий любое количество любых символов (в том числе ноль символов) и знак вопроса (?), заменяющий строго один любой символ.

Например, чтобы посчитать количество ячеек, в которых в любом месте есть слово "машина", нужно использовать функцию:
=СЧЁТЕСЛИ(диапазон; "*машина*")

Для расчета количества ячеек, которые начинаются с числа 100, нужна функция:
=СЧЁТЕСЛИ(диапазон; "100*")

А для заканчивающихся на "100":
=СЧЁТЕСЛИ(диапазон; "*100")

Для суммирования ячеек по условию "только слова из пяти букв":
=СУММЕСЛИ(диапазон; "?????"; диапазон_суммирования)

Для подсчета среднего по условию "любой символ + Pad" (iPad, IPad, 1Pad и так далее):
=СРЗНАЧЕСЛИ(диапазон; "?Pad"; диапазон_усреднения)

Для подсчета количества слов НЕ из восьми букв:
=СЧЁТЕСЛИ(диапазон; "<>????????")

Чтобы искать именно звездочку или знак вопроса, нужно добавить перед знаком тильду (~)
То есть такая функция подсчитает число ячеек, в которых встречается звездочка:
=СЧЁТЕСЛИ(диапазон; "*~")

А такая - только те, которые начинаются на звездочку:
=СЧЁТЕСЛИ(диапазон; "~")

Файл с примерами функций:
https://goo.gl/auadJu

P.S. Аналогично символы работают и в функциях с двумя и более условиями, которые отличаются приставкой МН или S, например: СУММЕСЛИМН/SUMIFS

Извлекаем из ячейки только определенное слово: функции SPLIT и ИНДЕКС

Функция SPLIT разделяет текст на отдельные фрагменты (слова) по заданному разделителю (пробелу).
А ИНДЕКС/INDEX извлекает элемент из диапазона или массива по его порядковому номеру.
Таким образом, сочетание этих функций позволяет извлечь из ячеек любое слово.

Например, если у вас в столбце указаны ФИО, а вам нужны только имена - достаточно разделить ФИО на слова по разделителю "пробел" и извлечь второй элемент:
=ИНДЕКС(SPLIT(ячейка;" ");2)

на GIF-ке мы сначала разбиваем текст по столбцам одной функцией SPLIT. А уже затем извлекаем только определенные элементы с привлечением ИНДЕКСА)

ссылка на GIF: https://goo.gl/6aZfMj