#Простыевопросы: —Подскажите, как реализовать вывод 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

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

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

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