А вот так, с помощью СМЕЩ(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

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

Сегодня повторяем январский пост, надеемся, что многим из вас он будет полезен.

8 советов о том, как ускорить работу вашего документа в Google Таблицах:

1) Удалите неиспользуемые строки на каждой вкладке (по умолчанию создается 1000 строк - если у вас на вкладке сейчас используется 200, удалите лишние 800, а при необходимости добавите нужное кол-во) и столбцы (аналогично). Для этого можно пользоваться надстройкой Crop Sheet - а можно и сделать это вручную;

2) Оптимизируйте количество вкладок (если есть несколько вкладок с маленькими таблицами или списками - попробуйте объединить их в одну);

3) Осторожней с ресурсоемкими формулами, если есть формулы поиска данных (ВПР/VLOOKUP, ИНДЕКС/INDEX, ПОИСКПОЗ/MATCH и другие, сохраняйте часть формул как значения (если не нужно будет эти значения обновлять). Например, если у вас подтягиваются данные за много месяцев с помощью VLOOKUP - оставляйте текущий месяц формулами, а остальные данные сохраните как значения;

4) И с форматированием документа (особенно с условным!), не стоит заливать строки/столбцы цветом целиком (и вообще стараться избегать излишнего форматирования);

Проверьте, нет ли условного форматирования на большом диапазоне ячеек;

5) Не стоит ставить фильтр на все столбцы;

6) Очистите примечания и удалите комментарии, если их много и они не нужны;

7) Проверьте, нет ли проверки данных на большом диапазоне ячеек;

8) Бонусный скрипт для оптимизации: иногда в документах все же приходится использовать ресурсоемкие формулы, которые ничем не заменить, например, может потребоваться собирать в один файл данные из 20 разных документов формулой IMPORTRANGE. Если ничего не предпринять, то работа с таким документом может стать мучительной, формулы будут постоянно обновляться и все начнет тормозить.

В таких случаях мы предлагаем следующее решение - написать скрипт, который будет вставлять формулы в требуемые ячейки, а потом сразу же заменять их на значения (как если бы в Excel мы сохраняли данные как значения с помощью специальной вставки или макроса). Такой скрипт можно запускать как вручную, так и по расписанию, скажем, каждые два часа и в этом случае необязательно даже находиться в файле, скрипт отработает и в оффлайн режиме. Вот тут мы рассматриваем пример такого скрипта: https://habrahabr.ru/post/331360/#comment_10277466

Как искать значение с помощью функции ВПР/VLOOKUP сразу в нескольких таблицах?

Просто объедините их в массив с помощью фигурных скобок:

=ВПР(искомое_значение; {диапазон 1; диапазон 2; диапазон 3;...}; номер_столбца; 0)

Таблицы могут быть и на одном листе, и на нескольких (и даже в разных файлах, если импортировать их с помощью IMPORTRANGE).

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

Файл с примером:

https://goo.gl/QL2Pqx