QUERY. Функция для создания запросов в Google-Таблицах

Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.

• данные — это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
• запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
• заголовки — количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.

1. Запрос указывается в кавычках.
2. В запросе используются ключевые слова:
   1. SELECT — определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
   2. WHERE — ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
   3. GROUP BY — группирует значения по заданным полям.
   4. PIVOT — позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
   5. ORDER BY — задает сортировку. Например: «ORDER BY C DESC» — сортировка по столбцу C по убыванию.
   6. LIMIT — ограничивает количество возвращаемых строк. Например: «LIMIT 50».
   7. OFFSET — пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
   8. FORMAT — определяет формат определенных столбцов по заданному шаблону.
   9. LABEL — позволяет переименовать столбцы в выдаче. Например, «LABEL MAX(D) ‘Среднее в 2016 году'». Вместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».

   Рассмотрим несколько примеров применения QUERY на практике.

   

   Мнение эксперта

   Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft

   Если у вас возникнут сложности, я помогу разобраться!

   Задать вопрос эксперту

   И последний штрих — если присмотреться к столбцу Сумма, то можно увидеть, что Power Query присвоил ему текстовый тип данных. Если же вы хотите что-то уточнить, обращайтесь ко мне!
   Прежде всего избавимся от четырёх верхних строк, которые не представляют для нас никакой ценности. Для этого, на вкладке Главная выбираем Сократить строки -> Удалить строки -> Удаление верхних строк . Далее, в появившемся окне указываем количество строк, которые необходимо удалить и жмём ОК.
   

   Знакомство с Power Query

   • QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
   • перед датой и перед апострофом нужно написать date;
   • можно и не делать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:

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

   Архитектура Power Query

   Если проводить аналогии, то Power Query можно сравнить с конвейером по подготовке данных для последующего анализа. И весь процесс работы этого конвейера можно условно разделить на четыре стадии:

   Первая стадия работы конвейера Power Query, заключающаяся в получении данных для последующей обработки. В основном, получение данных осуществляется через выпадающее меню Создать запрос на вкладке Данные.

   Пользователю предлагается выбрать из четырёх групп источников:

   

   На практике, чаще всего используется группа Из файла, позволяющая получать данные из книги Excel, из файлов CSV и XML, из текстовых файлов и (целым скопом) из папки.

   

   

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

   

   

   На вкладке Данные выбираем Создать запрос -> Из файла -> Из текста. В открывшемся окне Импорт данных находим нужный нам файл и жмём на кнопку Импорт.

   

   

   Мнение эксперта

   Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft

   Если у вас возникнут сложности, я помогу разобраться!

   Задать вопрос эксперту

   Единственное отличие состоит в том, что вы используете функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ, которая позволяет вам указывать сразу несколько столбцов для проверки их уникальности. Если же вы хотите что-то уточнить, обращайтесь ко мне!
   Если вам нужно пересчитать уникальные числа (с учетом первого вхождения) в диапазоне, с учетом некоторых ограничений, вы можете использовать формулу, основанную на СУММ и ЧАСТОТА, и одновременно применять критерии.

   Excel/Эксель — 3 варианта удаление дубликатов строк через функции и без макросов

   • загрузка данных в таблицу с указанием листа (новый либо существующий);
   • только создание соединения — в случае если мы планируем использовать этот запрос для объединения с другими запросами;
   • добавление в модель данных — если мы хотим загрузить полученные данные прямо в модель данных Power Pivot.

   Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы ввести ее правильно. Как только это будет сделано, Excel автоматически заключит все выражение в , как показано на снимке экрана ниже. Фигурные скобки ни в коем случае нельзя вводить вручную, не получится.

   2 Вариант — Пометить дубликаты строк в Лож или Истина

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

   

   для примера возьмем два столбика с данными, в одном (пример1) дублирующие значения повторяются, а в (примере2) в разнобой и не идут друг за другом.

   

   В примере1 мы должны в стоящей рядом ячейки нажать знак ровно и выбрать первое и нижние значение что бы формула была такая:

   и нажимаем энтер, и у нас в этой ячейки в зависимости от данных должно появится значение Лож или Истина

   

   ЛОЖ — если А1 не будет равно А2

   Истина — если А1 будет ровно А2

   если применить этот вариант на столбце Пример2, то как вы поняли везде будет значение Лож

   

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

   

   Мнение эксперта

   Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft

   Если у вас возникнут сложности, я помогу разобраться!

   Задать вопрос эксперту

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

   Как работает формула подсчета уникальных значений?

   5. Теперь нажмите кнопку прямоугольника, появится диалоговое окно Kutools for Excel , выберите диапазон, содержащий уникальные значения, которые необходимо извлечь, а затем нажмите OK .

   Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции