А еще мы запускаем рубрику СПРАШИВАЙ! Каждый день вы можете задавать свои вопросы (вот здесь: 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://t.me/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://t.me/google_sheets/88
Добавляем к изображению ссылку - https://t.me/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

Друзья, привет! Зима пришла, но никак не смогла повлиять на наши традиционные субботние ответы на ваши вопросы. Подборка выйдет через несколько часов, а один вопрос заслужил отдельного поста:

⁉️ А как можно вывести последнее время редактирования таблицы именно для определенной или каждой вкладки?

Скрипт, в студию! Он выведет в ячейку А1 каждой вкладки (подставьте любую нужную) дату и время ее последнего редактирования.

function onEdit(e)
{
var range = e.range
range.getSheet().getRange("a1").setValue(new Date())
}

Инструкцию по установке тоже не забыли, в гифке: http://recordit.co/2HWL3fd2SA

А вот и субботние ответы на ваши вопросы:
http://telegra.ph/Zima-prishla-subbota-11-30

Сегодня на арене:
1) разделение проекта, как способ спрятать часть информации от сотрудника
2) вводим в ячейку несколько символов, видим список из всех подходящих вариантов и выбираем нужный
3) с помощью QUERY уменьшаем таблицу до нескольких столбцов
4) как подставить в формулу номер строки или букву столбца из текущей ячейки
5) градиентное условное форматирование внутри групп и другие вопросы

Свой вопрос можно задать здесь: https://goo.gl/X8qqnE. И представляйтесь, пожалуйста :)

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

Друзья, доброе утро.

Сегодня я расскажу о прекрасной функции Таблиц - "Публикация в интернете".

С помощью нее вы можете опубликовать весь документ либо выбранные вами вкладки, доступ к ним получат все пользователи, у которых будет ссылка, доступ - только для чтения и панель инструментов Google Таблиц открываться не будет. Данные по ссылке будут обновляться вслед за изменениями в Таблице. Можно выбрать разные форматы: веб-страница, PDF, CSV и даже Excel.

Примеры использования:
- если вы хотите расшарить для клиентов часть вкладок рабочего док-та и им не нужно будет вносить правки
- если вы создали таблицу, в которой с помощью формул считаются разные рейтинги и хотите опубликовать их для всех сотрудников (например, топ10 продавцов, продавших больше всех пакетов в Магните)
- когда вы хотите встроить на ваш сайт диаграмму с продажами вашей компании или текущей стоимостью портфеля ценных бумаг (возможность встройки тоже есть, через фрейм, код которого нужно будет вставить на веб-страницу)
- (пример от нашей читательницы): через веб-форму можно собирать фамилии участников мероприятия, сохранять на лист Таблицы и его публиковать

How-to:
Файл-> Опубликовать в интернете -> далее выбираем, публиковать весь файл или выбранные вкладки -> далее опубликовать или встроить

Результат по ссылке (длинную ссылку можно сократить с помощью goo.gl до короткой и удобной): https://goo.gl/ABRTFx

Заказать работу: teletype.in/@google_sheets/sheet_happens