QUERY на основе нескольких диапазонов данных

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

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

В общем случае для нескольких источников данных синтаксис следующий:
=QUERY({диапазон 1;диапазон 2;...};"SELECT ...";[заголовки])

Допустим, у нас есть таблица со списком книг на одном листе - и со списком учебников на другом.
Объединим их в функции QUERY:
=QUERY({'Книги'!A1:D;'Учебники'!A2:D}...

В запросе укажем  SELECT * - то есть выберем все столбцы из исходных диапазонов.

В качестве условия укажем  WHERE Col2 <> '', то есть все строки, в которых столбец 2 непустой (а значит, все строки с данными из двух таблиц)

Отсортируем по убыванию продаж 2016 года, которые у нас в четвертом столбец:
ORDER BY Col4 DESC

И в итоге получим следующую функцию:
=QUERY({'Книги'!A1:D;'Учебники'!A2:D};"SELECT * WHERE Col2 <> '' ORDER BY Col4 DESC";0)

Обратите внимание, что при использовании нескольких диапазонов данных в QUERY столбцы указываются в формате "ColN", где N - номер столбца.

Ссылка на файл с примером:
https://goo.gl/55UvGk

Всех поздравляем с праздниками!

Привет, друзья!

Сегодня на повестке дня файл с двумя кейсами.

📌В прошлом посте мы коснулись объединения диапазонов для формулы QUERY. Сегодня продолжим про объединение, на вкладке "поиск в нескольких списках" в ячейке В2 формула, которая ищет значение из ячейки А2 сразу в двух столбцах, чтобы этого добиться нужно просто объединить в формуле диапазоны при помощи фигурных скобок и ";". Как вы понимаете, не обязательно ограничиваться двумя диапазонами, их может быть столько, сколько нужно. Если значение есть в списках, формула возвращает "ОК", если значения нет, то ошибку.


📌📌Второй кейс мы уже обсуждали несколько месяцев назад, но он мне кажется занимательным и я его повторю. На вкладке "выпадающий список.." показана реализация выпадающего списка (назовем его список №2), позиции которого зависят от того, что выбирает пользователь в выпадающем списке №1. Как это можно использовать? Ну, например вы отмечаете, кто оплатил платежку, в одном списке выбираете отдел, а в другом появляются авторизованные на оплату сотрудники этого отдела.


Примеры (файл-> создать копию, чтобы можно было работать) https://goo.gl/hLUDbc

📉😢→☎️→😎👌🏿→📈
Друзья, и вдогонку: если вам нужна помощь в реализации вашего проекта в Google Документах, обращайтесь по контактам ниже. Проект может быть как сложным со скриптами (js) и большим количеством документов, так и простым.

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

Если нужна консультация, если вы хотите посмотреть решения, которые мы уже внедряли - пишите, все обсудим @namokonov, @renat_shagabutdinov

Друзья, вчера вышла моя статья на habrahabr.ru про опыт создания отчетности с нуля (использовались только Google Таблички) в одном стартапе, я рассказал о некоторых хитростях, которые упростили работу.

https://habrahabr.ru/post/328820/

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

Чтобы было удобнее пользоваться каналом - мы сделали оглавление: https://goo.gl/HdS2qn

Друзья, привет. Сегодня - несколько слов о дополнениях.

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

Большинство дополнений устанавливаются непосредственно в Таблицах, а не в браузер - и поэтому будут работать на разных компьютерах в разных браузерах.
В мобильных приложениях дополнения не работают, к сожалению.

Смотрите на GIF процесс установки дополнения Power Tools:
https://goo.gl/qYKMnz

5 полезных дополнений для Таблиц:

1  CropSheet
Чтобы делать документы легче и быстрее, стоит удалять все ненужные строки и столбцы.
Для этого есть  бесплатное дополнение CropSheet с очень простым функционалом:
Первая функция - “обрезка” листа по данным (все пустые строки и столбцы удалятся), вторая - по выделению (все, что ниже и правее выделения, будет удалено).

2 Sheet Navigator
Это бесплатное дополнение облегчает навигацию по листам - список их названий появляется справа в отдельном окне
Особая ценность в том, что дополнение может отображать и скрытые листы

3 Remove Duplicates
Простое дополнение с важной функцией, которая есть по умолчанию в Excel, но не в Google Таблицах. Речь про удаление и выделение дубликатов (или уникальных значений).

4 Solver
Solver - это аналог “поиска решения”, надстройки Excel для решения оптимизационных задач. Версия для Google Таблиц сделана теми же разработчиками и является бесплатной.

5 Power Tools
Power Tools - надстройка-мультитул, целый сборник небольших полезных опций.
Два примера (возможностей гораздо больше):
- Суммирование по цвету ячейки;
- Конвертация текста в даты и числа

Друзья, привет! 15 июня я проведу бесплатный вебинар по трем очень полезным функциям Google Таблиц, которых нет в Excel -  IMPORTRANGE (связываем документы друг с другом), QUERY (строим SQL-запросы прямо в таблицах) и IMAGE (вставляем в ячейки изображения).  С примерами из практики. Приходите!
http://netology.ru/free-lessons/dif-google-sheets-150617

P.S. Кроме того, открылась запись на курс по Google Таблицам там же на Нетологии. Я анонсировал его ранее, но поторопился :) Запись только-только открыли: http://netology.ru/programs/google-sheets

Ренат

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

Сегодня наглядно рассмотрим одно из главных преимуществ Google Таблиц перед MS Excel, а именно формулы, работающие с таблицами (QUERY и FILTER) и позволяющие применить на них критерии отбора и вывести на рабочий лист результат.

В примере (https://goo.gl/EWqQFg) простая задача с отбором из таблицы и я попробовал показать, как решить ее и с помощью FILTER и с помощью QUERY.

Если резюмировать, то когда нам нужно отобрать данные из таблицы по одному-нескольким условиям и если не нужно выводить дополнительные колонки или группировать данные или использовать "ИЛИ (or)" в условиях - то вполне можно все это сделать с помощью формулы FILTER. Как бонус - не нужно писать SQL запрос.

Как обычно, чтобы можно было поработать с док-ом: файл-> скачать копию.

📚Оглавление канала, все темы и даты постов: https://goo.gl/HdS2qn

💬Друзья, если у вас есть интересная тема и вы хотите, чтобы мы ее разобрали - пишите в ЛС, контакты в профиле канала.

Доброе утро.

Сегодня показываем, как одной функцией ВПР (VLOOKUP) подтянуть данные сразу из трех столбцов. Для этого нужно сделать функцию массива (в Google Таблицах это означает "вписывание" вашей функции внутрь другой функции ARRAYFORMULA, а не ввод с помощью Ctrl+Shift+Enter как в Excel).

А в аргументе "Номер столбца" в ВПР нужно указать не один номер, а массив из нескольких - в фигурных скобках через косую черту, например:
{2\3}

Пример - на GIF:
https://goo.gl/uZCUUd

А на десерт - мини-инструкция по импорту/экспорту между Таблицами и Excel:
http://shagabutdinov.ru/business-hacks/excel-sheets/

Хорошего дня.

Всем привет!

Если в вашей таблице десятки и сотни строк (операции, сотрудники, клиенты, продукты, запчасти, сделки и т.д.) и много столбцов, вам будет непросто сравнивать соседние строки: они станут сливаться. Выход — чередующаяся заливка строк, которую очень легко сделать в Excel с помощью встроенных инструментов (Главная → Форматировать как таблицу).

Такой инструмент относительно недавно появился и в Google Таблицах.  Меню -> Формат -> Чередование цветов:

📚 Друзья, оглавление канала, все темы и даты постов: https://goo.gl/HdS2qn

Друзья, сегодня я покажу, как в массиве можно выделить ячейки с лишними пробелами в тексте (например, "жаркий[ ][ ]июнь 2017")

1) выделяем столбец для поиска (или сразу несколько)
2) открываем условное форматирование
3) выбираем "ваша формула" и вводим её: =сжпробелы(A1)<>A1 (где А1 - первая ячейка выбранного ранее диапазона)

http://recordit.co/zmuIUCFayr.gif
(чтобы гифка открылась на весь экран, нажимайте на адрес ссылки, а не на картинку)

📍Наверное, вы уже догадались, что с помощью формулы =сжпробелы (сжать пробелы) можно и избавиться от лишних пробелов в ячейках.
http://recordit.co/mifMLD1Tvc.gif

📌Файл с примером: https://goo.gl/15a55X

Всем привет! Друзья, мы обновили статью о самой мощной функции Google Таблиц - QUERY - и добавили в нее кейсов.
Пользуйтесь на здоровье:
http://shagabutdinov.ru/business-hacks/query_sheets/

Всем хорошего воскресенья!

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

📚Сначала немного теории. Для таблиц ячейки с датой и временем - это числа, в которых дни - целая часть, один день - единица, а время - дробная. Например, 21-06-17 12:00:00 - это 42907,5. А 22-06-17 00:00:00 - 42908.

📌Ну а теперь к делу, если время - дробная часть, то дату проще всего отделить с помощью округления вниз, функция =ОКРВНИЗ(значение, точность округления). По умолчанию точность будет равна 1, так что в нашем примере ее вводить не обязательно.

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

http://recordit.co/fk0WrkTh8c.gif (нажимайте на ссылку, чтобы гифка открылась в браузере в полном размере и с элементами управления)

Друзья, привет!
🗞Мы написали статью на хабр: https://habrahabr.ru/post/331360/
В ней подробно, с примерами, про некоторые функции Таблиц, которых нет в Excel: (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE).

Будете искать информацию по этим функциям - заходите.

📚 Друзья, оглавление канала, все темы и даты постов: https://goo.gl/HdS2qn

🎈🎈🎈Друзья, привет.
Сегодня утром мы преодолели пугающее число в 666 подписчиков. Кому-то может показаться, что это немного, но мы рады каждому читателю, который сможет взять у нас что-то полезное и сделать свою работу с таблицами лучше , чтобы сберечь время для близких людей и любимых дел

✉️Сегодня мы хотим задать вам пару вопросов и обратиться за помощью с выбором новых тем, актуальных для вас:  https://goo.gl/LHCz14
Это займет всего 1-2 минуты. Будем благодарны за ваши ответы.

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

Евгений и Ренат.

Всем привет!

Как правило, в Таблицах в одном документе одновременно работает несколько человек.
И часто сразу нескольким пользователям нужно фильтровать данные.
Можно ставить фильтры так, чтобы они не были видны остальным пользователям - и не мешали им работать.

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

А вот Режим фильтрации виден только одному пользователю. Несколько пользователей могут создавать свои, разные и независимые фильтры благодаря этой опции. И не мешать друг другу работать с документом.
Режим фильтрации можно вызвать, нажав на маленькую кнопку со стрелкой рядом с иконкой фильтра и нажав на пункт “Создать новый фильтр” в выпадающем меню.
https://goo.gl/14FubJ

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

P.S. Друзья, напоминаю , что уже скоро - 30 июня - стартует мой онлайн-курс в Нетологии по Google Таблицам:
http://netolo.gy/dzi
будем учиться всему - от азов и простых формул до визуализации и продвинутых функций.
По промокоду googlesheets - скидка 20% :)