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

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

Подобные, большие и страшные формулы, проще читать изнутри, начиная от вложенных элементов, в примере 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), а каждую субботу мы будем на них отвечать.

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

Друзья, добрый вечер!

Первая подборка ответов готова: http://telegra.ph/Sprashivaj-11-noyabrya-17-11-10.

Темы:
— Условное форматирование
— FILTER \ QUERY
— SUMIFs для разных листов одной формулой
— IMPORTHTML или как постоянно загружать данные из веб-сайта
— а также немного про работу с API, JSON, квоты машинного времени, скрипты и прочее

Чтобы попасть в следующие подборки, задайте свой вопрос здесь: https://goo.gl/N3UaEa (только не отправляйте файлы, пжл)

Хороших выходных! А всем, кто в пути - удачно добраться домой ✈️

Как один столбец с данными развернуть в таблицу из нескольких столбцов?

Друзья, добрый вечер. В субботний пост с ответами на вопросы не попал интересный кейс, исправляем это.

На скриншоте данные в столбце "В", развернем его в таблицу из 5-ти столбцов.

Процесс по шагам в ГИФКЕ: http://recordit.co/BrPfeWXfKK
Итоговая формула: =TRANSPOSE((OFFSET($B$1:$B$5;(ROW()-1)*5;)))

Ниже - объяснения основных моментов.

Используем функцию СМЕЩ(OFFSET), она позволяет отклоняться на выбранное кол-во строк (или столбцов) от заданного диапазона. Заданный диапазон у нас - первые 5 элементов таблицы - $B$1:$B$5, его нужно зафиксировать с помощью $$.

Кол-во строк, на которые формула будет опускаться вниз от заданного диапазона при каждом копировании на новую строку будем рассчитывать с помощью формулы (ROW()-1)*5. Например, для формулы, вставленной в первую строку, мы получим (1-1)*5 = 0, СМЕЩ никуда не опустится и вернет исходный диапазон B1:B5, для второй строки - (2-1)*5 = 5, СМЕЩ вернет диапазон на 5 строк ниже исходного, B6:B10, и так для каждой новой строки.

СМЕЩ будет возвращать данные так же, как они представлены - построчно, развернем их в одну строку с помощью функции ТРАНСП(TRANSPOSE).

P.S. Даже если в ваших Google Таблицах язык формул - русский, вы все равно можете вводить формулы на английском. И наоборот. Все будет работать.

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