Друзья, доброе утро! У вас есть 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

Доброе утро!

Сегодня суббота и мы публикуем становящуюся традиционной подборку ответов на ваши вопросы.

В ней мы:
— Считаем галочки
— Считаем ячейки по цвету их заливки и сортируем
— Добавляем пустые строки после результата формул
— Связываем IMPORTRANGE и QUERY, а также транспонируем формулы, вставляем в Google Документ обновляемую часть из Таблицы и другое :)

http://telegra.ph/Sprashivaj-subbota-18-oe-noyabrya-17-11-17

Свой вопрос можно задать здесь: https://goo.gl/X8qqnE.

Если на ваш вопрос не ответили, то возможно он попадет в следуюущую подборку. Хороших выходных, друзья!

Друзья, хотим рассказать про отличный и добрый канал: @correctarium_channel

А там: советы о правописании русского и английского, про редкие слова и много интересного про язык.

Ежедневно. Лаконично. Со вкусом. Наша гуглтабличная рекомендует.

#нерекламааискренняярекомендация

https://xn--r1a.website/correctarium_channel/245

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

Сегодня учимся создавать в Таблицах автоматическую ссылку для отправки письма контрагенту.

Адрес и тему письма будем брать из таблицы - реестра сделок.

Пошаговое описание и файл с примером - по ссылке:
http://telegra.ph/Kak-sozdat-knopku-dlya-otpravki-pisma-nuzhnomu-kontragentu-v-Google-Tablicah-11-22

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

Бувально пару предложений для всех, кто пришел: мы пишем про Google Таблицы, пишем уже год, осветили много интересных тем, оглавление канала можно посмотреть здесь: https://goo.gl/HdS2qn.

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

А еще сегодня суббота и выходит наша традиционная подборка ответов на ваши вопросы: http://telegra.ph/Sprashivaj-3-x-subbota-25-oe-noyabrya-17-11-25

Сегодня вопросов немного, поэтому тем, кому показалось мало, рекомендуем наши прошлые подборки:
http://telegra.ph/Sprashivaj-11-noyabrya-17-11-10
http://telegra.ph/Sprashivaj-subbota-18-oe-noyabrya-17-11-17

А задать свой вопрос можно здесь: https://goo.gl/X8qqnE

Хороших выходных!

"Условное форматирование" с иконками с помощью функции IMAGE

Увы, в Google Таблицах нет условного форматирования с наборами значков, как в Excel.
Но есть функция IMAGE, отображающая в ячейке любое изображение - достаточно ссылки на него.

Воспользуемся этим и будем отображать в ячейке иконку с ростом продаж, если 2017 год выше 2016, иконку с падением, если 2017 год ниже 2016, и оставим ячейку пустой в случае их равенства.

Для решения этой задачи с тремя условиями подойдет функция IFS(логическое выражение 1; значение 1; логическое выражение 2; значение 2;...)

В нашем случае - со следующей структурой:
=IFS(2017>2016; IMAGE(иконка с ростом); 2017=2016; ""; 2017<2016; IMAGE(иконка с падением))

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

Про вставку изображений смотрите также:
IMPORTXML: загружаем изображения с веб-страницы - https://xn--r1a.website/google_sheets/88
Добавляем к изображению ссылку - https://xn--r1a.website/google_sheets/16

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

Сегодня продолжим говорить про функцию IMAGE, которая позволяет отображать в ячейке Таблицы изображение из интернета. Если мы зададим второй аргумент функции =4, то следующими двумя аргументами можно принудительно задать выcоту и ширину изображения в пикселях. Небольшой нюанс - изображение заполняет ячейку и если вы укажете его размеры бОльшими, чем размеры ячейки - вы увидите только часть изображения.

Как это можно использовать? Например, при создании в Таблицах инфографики, для того, чтобы размеры изображений были пропорциональны их значениям.

Давайте визуализируем несколько стадий жизни нашего канала. Наибольшая картинка - это канал сейчас и все наши 2558 подписчика. Формула:
=IMAGE("ссылка на картинку";4;135;135)

Для всех остальных картинок будет меняться только расчет стороны квадрата, он будет считать по формуле:
135/SQRT(Квадратный корень, 2558/кол-во подписчиков)

Формула целиком для 1000 подписчиков:
=IMAGE("ссылка на картинку";4;135/sqrt(2558/1000);135/sqrt(2558/1000))

P.S. все написанное выше - для квадратных картинок, где ширина=высота, а S=a².

Таблица с примером: https://goo.gl/7hr737