Друзья, как думаете, сколько нужно формул, чтобы отобрать из таблицы данные по условию, объединить построчно, добавить текст и превратив всё, например, в 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

Друзья, привет. Недавно довелось переносить в Google Таблицу инструмент для наглядного представления собственных навыков. Полоски - навыки, их длина зависит от кол-ва баллов и чем они длиннее - тем мощнее навык развит.

По ссылке - про то, как сделать самую сложную часть, интерактивные полоски, зависящие от введенного балла, с помощью условного форматирования.

http://telegra.ph/Google-Tablicy-zhivye-shkaly-11-02

Друзья, доброе утро! У вас есть CSV-документ и вы хотите открыть его в Google Таблице. Как это сделать? Можно открыть CSV в текстовом редакторе, скопировать оттуда, вставить в нашу таблицу, распарсить, если будет нужно, но есть варианты проще:

Файл-> Открыть и можно выбрать документ любого подходящего формата как из вашего Google Диска, так и из локального. Он откроется в новой вкладке. Например: http://recordit.co/W6OvKcAw8R

Функция IMPORTDATA (если csv (или tsv) документы доступны по ссылке) У функции единственный аргумент - ссылка на (в кавычках).

P. S. А csv - это такой формат текстового документа, в котором значения разделены запятыми, comma separated values, tsv - значения раздены табами

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

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

Формулы со скриншота:
=QUERY(A2:B5;"select * where B > timeofday '12:00:00'")
=QUERY(A2:B5;"select * where B >= timeofday '"&TEXT(D5;"HH:mm:ss")&"'")

А еще мы запускаем рубрику СПРАШИВАЙ! Каждый день вы можете задавать свои вопросы (вот здесь: https://goo.gl/X8qqnE), а каждую субботу мы будем на них отвечать.

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