— О том, как объединить таблицы с разной структурой в QUERY, сгруппировать и посчитать средние значения,

— О скрипте (функции), который отобразит названия всех листов в книге,

— И о том, как собрать на одном листе Google Таблицы данные из 70-ти листов

Читайте в нашем еженедельном субботнем дайджесте ответов на ваши вопросы: http://telegra.ph/Sprashivaj-7-x-subbota-23-dekabrya-17-12-23

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

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

Хороших выходных! И не забывайте - в закрепленном посте есть оглавление канала со всеми нашими постами.

Друзья, привет. Сегодня поговорим про то, как убрать из таблицы строки и столбцы с нулевой суммой. Делать это будем с помощью обоюдоострой и не знающей пощады функции FILTER.

Слева на скриншоте - исходная таблица, у Алексея и Антона ни по одному месяцу нет продаж, также ни у кого нет продаж в июле и октябре 17.

Формула ниже оставит таблицу без пустых строк (условие: сумма по строке > 0),

=FILTER(A1:G7;B1:B7>0)

Добавим ее результат, как диапазон данных в другой FILTER (условие: сумма по столбцу в исходной таблице > 0) и из таблицы пропадут пустые столбцы (месяца без продаж),

=FILTER(FILTER(A1:G7;B1:B7>0);A2:G2>0)

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

P.S. Спасибо нашему читателю Сергею за отличную идею для поста.

Друзья, первый пост на нашем канале мы опубликовали 6 января 2017, он был про СУММЕСЛИ (SUMIF) и символьные шаблоны "*" и "?" (с помощью которых можно подсчитать значения, если в них частично присутствует наше условие). Первый пост: https://t.me/google_sheets/3.

Совсем недавно мы перешагнули рубеж в 100 постов, в которых мы рассказали про самые разные аспекты работы с Таблицами, оглавление канала, описания постов и ссылки - в закрепленном сообщении. Это был интересный год, друзья, спасибо, что читали нас!

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

И последний пост в этом году, субботняя подборка ответов на ваши вопросы:

В ней:
— О возможностях получения данных о форматировании ячейки с помощью функций
— Функции для проверки данных
— Выделяем ячейки с одинаковыми ссылками одним цветом
— Решение про уникальный порядок сортировки от нашего читателя Ильи

http://telegra.ph/Sprashivaj-prednovogodnyaya-podborka-8-x-subbota-30-dekabrya-17-12-30

Чтобы попасть в следующую подборку, задайте вопрос здесь: https://goo.gl/X8qqnEи

На следующей неделе у нашего канала будут каникулы, вернемся к вам после 7-го января. 🎉

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

Сегодня для вас - небольшое видео про функцию ВПР в Google Таблицах и несколько сочетаний этой функции с другими:
- ВПР и СТОЛБЕЦ для получения данных из всех столбцов исходной таблицы
- ВПР и ПОИСКПОЗ для получения данных из любых столбцов исходной таблицы по их заголовкам (даже если они расположены в ином порядке, чем в исходной таблице)
https://www.youtube.com/watch?v=qJni1mxaYPs

И напоминаем про наш вебинар по функциям QUERY и FILTER, который пройдет 24 января. Приходите, будет полезно:
https://google-tablitsy.timepad.ru/event/633908/

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

Первый кейс 2018 года будет о том, как с помощью одной формулы создать столбец, в котором вы переведете все даты из другого столбца на 2 часа вперед.

(или на 3 часа, 6 дней, умножите на 2, разделите на 86%, посчитаете корень, etc)

Поможет функция FILTER, она охватит весь нужный массив, что особенно актуально, если ваш диапазон может меняться со временем.

Вступление: добавляя к ячейке с датой +1, мы прибавляем целые сутки, 24 часа. Значит +1/24 добавит один час, а +2/24 - 2 часа.

Поехали, вводим формулу в ячейку B2:
=FILTER(A2:A+2/24;A2:A<>"")

FILTER добавит к каждой непустой ячейке столбца А два часа, если размер диапазона в A2:A будет изменяться, то формула автоматически отобразит меньшее или большее кол-во ячеек.

Чтобы умножить каждое значение на два - просто поменяйте +2/24 на *2, а =FILTER(SQRT(A2:A);A2:A<>""), найдет квадратный корень из каждого значения (для дат неактуально, но может пригодиться для математических расчетов)

Новое, измененное значение будет отображаться на той же строке, что и исходное, только если выбранный массив не содержит пустых ячеек внутри. Как их учесть - расскажем в следующих кейсах.

P.S. 24 января на вебинаре мы как раз будем рассматривать и другие кейсы с этой волшебной функций FILTER. Приходите:
https://google-tablitsy.timepad.ru/event/633908/

Друзья, в эту январскую субботу, когда зима наконец стала похожа на зиму, мы выпускаем первую в этом году подборку с ответами.

Расскажем про:
— Google скрипт, запускающийся каждую ночь и защищащий от редактирования заполненный диапазон выбранного листа
— как текст превратить в работающую ссылку на ячейки (и что делать, если название листа с пробелами)
— как объединить значения с одним названием на диаграмме
— немного про cashflow, баланс и прочий p&l в Таблицах

Подборка: http://telegra.ph/YAnvarskaya-podborka-9-x-subbota-13-dekabrya-18-01-13
Вопросы задавайте здесь: https://goo.gl/bRkn39

Привет. Это свершилось. За последние недели сводные в Google Таблицах обновились и в числе прочего добавилась такая полезная опция, как раскрытие данных любой ячейки.
Теперь можно щелкнуть на ячейку дважды - и "провалиться в данные". Появится дополнительный лист с теми данными, которые сформировали число в этой ячейке. Все как в Excel.

На коротком видео мы создаем простую сводную таблицу и щелкаем на ее ячейки, чтобы посмотреть, как создаются листы. Кстати, удобно, что сразу появляется всплывающая кнопка "Удалить", ведь обычно эти листы не нужно сохранять надолго.
https://www.youtube.com/watch?v=GZRznzghPUI
(видео без звука)

Хорошей недели, друзья!

Видеоурок: Регулярные выражения в Google Таблицах

Всем привет. Делимся с вами видео, в котором рассмотрим функции REGEXEXTRACTи REGEXREPLACE для работы с регулярными выражениями в Google Таблицах.

С помощью них:
- Извлекаем числа из текстовой ячейки;
- Заменяем текст в ячейке по шаблону.

https://www.youtube.com/watch?v=Tw3_8LEIUlw

Файл с примером - по ссылке в описании видео.

Друзья, сегодня об автоматическом формате данных, найти его можно кликнув на кнопку 123↓ на панели инструментов. Применив его к диапазону ячеек, вы увидите, что целые числа отобразятся без дробной части, а у дробных будет столько знаков после запятой, сколько у них есть на самом деле.

Чтобы воспользоваться этим форматом в функции TEXT(ТЕКСТ), используйте шаблон "@":
=TEXT(A2;"@")

Если в ваших данных разное кол-во знаков после запятой, можно добавить округление:
=TEXT(ROUND(A2;3);"@") (до 3-го знака, например)

И по традиции добавим функцию FILTER, теперь для всего диапазона достаточно одной формулы:
=FILTER(TEXT(ROUND(A2:A;3);"@");A2:A<>"")

Посмотрите на скриншот, в колонках с формулами у целых чисел пропали разряды, а слишком длинные дробные округлены до 3-его знака.

Друзья, привет. Каждую субботу мы отвечаем на ваши вопросы (а задать их можно здесь: https://goo.gl/bRkn39) и эта суббота не станет исключением.

Сегодня мы покажем как:
— заменить в наименованиях товара кириллические значения на латинские (двумя способами!)
— составить для каждой даты в столбце текст вида "01.01 - 07.01.18" (первый день недели - последний)
— сгруппировать продажи по дням в месяца и сделать из этого график
— и еще немного расскажем про FILTER

Подборка: http://telegra.ph/10-yj-vypusk-subbotnih-otvetov-20-yanvarya-2018-01-20

Желаем всем прекрасных выходных!

P.S. Друзья, если среди вас есть владельцы каналов, либо вы можете посоветовать каналы, в которых мы можем прорекламировать свой канал (платно) - напишите, будем благодарны.

Друзья, сегодня с помощью условного форматирования закрасим все счета с победой хозяеев (1:0, 4:3, победа первой команды) в зеленый цвет, а с поражением - в красный.

Начнем:

0) в диапазоне A2:A5 счета спортивного состязания, первый:
3 : 2

1) c помощью функции SPLIT (разделитель ":") разделим текстовую строку со счетом на два элемента:
=SPLIT(a2;":")
если счет будет вида 3-1, то конечно разделителем будет "-" и т.д.

2) счет хозяеев, (счет первой команды), извлекаем с помощью функции INDEX, возвращая первый элемент из массива, который получился в рез-те действия функции SPLIT:
=INDEX(SPLIT(A2;":");1)

счет гостей - второй элемент:
=INDEX(SPLIT(A2;":");2)

3) теперь сравниваем результат первой команды и второй, и поместим получившуюся формулу в правило условного форматирования:
=INDEX(SPLIT(A2;":");1) > INDEX(SPLIT(A2;":");2)
если первая цифра в счете больше - формула вернет TRUE и правило сработает, чтобы выделить поражения хозяев используйте эту же формулу, заменив ">" на "<"

4) формула для правила условного форматирования создается для верхней ячейки диапазона (к которому вы применяете усл. форматирование), т.е. достаточно выделить необходимый диапазон (например, A2:A100), создать в нем правило условного форматирования и написать в него формулу для ячейки A2.

Кстати, календарь матчей, используя IMPORTHTML можно загрузить отсюда: https://www.sports.ru/amkar/calendar/, поставив вместо Амкара любую нужную команду (или с любого другого ресурса, где есть таблица с календарем матчей вашей любимой команды).

P.S. Напоминаем, что завтра мы проводим вебинар по супер-функциям FILTER и QUERY с многими кейсами.
Еще можно купить билет: https://google-tablitsy.timepad.ru/event/633908/
а если вы уже зарегистрировались и у вас есть какие-то вопросы - пишите нам, мы поможем.

В проверке данных можно использовать в качестве числового ограничения не фиксированное значение, а число из ячейки.

Например, запрещать ввод в диапазон чисел больше, чем указанное в определенной ячейке.
Пример - на GIF (открывайте по ссылке):

https://goo.gl/itQJqN

в ячейке A2: =ГИПЕРССЫЛКА("https://t.me/google_sheets"; "Google Таблицы")

Друзья, привет, сегодня в субботней подборке ответов на ваши вопросы:

1) пишем пользовательскую функцию, которая выдергивает из =ГИПЕРССЫЛКА(ссылка;текст) - ссылку
2) функция FILTER, использующая данные выпадающего списка, получаем список всех тематик выбранного спикера
3) поговорим про парсинг с помощью IMPORTXML и квоты

Подборка здесь: http://telegra.ph/Subbotnie-otvety-27-yanvarya-2018-01-27
Вопросы сюда: https://goo.gl/bRkn39