QUERY. Функция для создания запросов в Google-Таблицах
Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.
- данные — это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
- запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
- заголовки — количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.
- Запрос указывается в кавычках.
- В запросе используются ключевые слова:
- SELECT — определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
- WHERE — ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
- GROUP BY — группирует значения по заданным полям.
- PIVOT — позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
- ORDER BY — задает сортировку. Например: «ORDER BY C DESC» — сортировка по столбцу C по убыванию.
- LIMIT — ограничивает количество возвращаемых строк. Например: «LIMIT 50».
- OFFSET — пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
- FORMAT — определяет формат определенных столбцов по заданному шаблону.
- LABEL — позволяет переименовать столбцы в выдаче. Например, «LABEL MAX(D) ‘Среднее в 2016 году'». Вместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».
Рассмотрим несколько примеров применения QUERY на практике.
[expert_bq id=»1570″]И последний штрих — если присмотреться к столбцу Сумма, то можно увидеть, что Power Query присвоил ему текстовый тип данных. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Прежде всего избавимся от четырёх верхних строк, которые не представляют для нас никакой ценности. Для этого, на вкладке Главная выбираем Сократить строки -> Удалить строки -> Удаление верхних строк . Далее, в появившемся окне указываем количество строк, которые необходимо удалить и жмём ОК.
Знакомство с Power Query
- QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
- перед датой и перед апострофом нужно написать date;
- можно и не делать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:
Вытягивание уникальных значений из диапазона была и остается одной из старейших тем обсуждений в среде Excel. Хотя в программе реализовано несколько эффективных возможностей идентификации повторяющихся значений, а статей на эту тему написано еще больше, сегодня мы поговорим о возможности создания в Excel динамического списка уникальных значений с помощью формул массивов.
Архитектура Power Query
Если проводить аналогии, то Power Query можно сравнить с конвейером по подготовке данных для последующего анализа. И весь процесс работы этого конвейера можно условно разделить на четыре стадии:
Первая стадия работы конвейера Power Query, заключающаяся в получении данных для последующей обработки. В основном, получение данных осуществляется через выпадающее меню Создать запрос на вкладке Данные.
Пользователю предлагается выбрать из четырёх групп источников:
На практике, чаще всего используется группа Из файла, позволяющая получать данные из книги Excel, из файлов CSV и XML, из текстовых файлов и (целым скопом) из папки.
Также, на вкладку Данные вынесена команда Из таблицы, позволяющая получать данные из умной таблицы или диапазона, расположенных на активном листе.
На вкладке Данные выбираем Создать запрос -> Из файла -> Из текста. В открывшемся окне Импорт данных находим нужный нам файл и жмём на кнопку Импорт.
[expert_bq id=»1570″]Единственное отличие состоит в том, что вы используете функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ, которая позволяет вам указывать сразу несколько столбцов для проверки их уникальности. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если вам нужно пересчитать уникальные числа (с учетом первого вхождения) в диапазоне, с учетом некоторых ограничений, вы можете использовать формулу, основанную на СУММ и ЧАСТОТА, и одновременно применять критерии.Excel/Эксель — 3 варианта удаление дубликатов строк через функции и без макросов
- загрузка данных в таблицу с указанием листа (новый либо существующий);
- только создание соединения — в случае если мы планируем использовать этот запрос для объединения с другими запросами;
- добавление в модель данных — если мы хотим загрузить полученные данные прямо в модель данных Power Pivot.
Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы ввести ее правильно. Как только это будет сделано, Excel автоматически заключит все выражение в , как показано на снимке экрана ниже. Фигурные скобки ни в коем случае нельзя вводить вручную, не получится.
2 Вариант — Пометить дубликаты строк в Лож или Истина
Этот вариант самый простой и отсюда сразу вылетает птичка которая ограничит этот вариант в действии, а именно в том, что им можно воспользоваться если у вас все дублирующие значения идут по порядку, а не в разнобой по всему документу
для примера возьмем два столбика с данными, в одном (пример1) дублирующие значения повторяются, а в (примере2) в разнобой и не идут друг за другом.
В примере1 мы должны в стоящей рядом ячейки нажать знак ровно и выбрать первое и нижние значение что бы формула была такая:
и нажимаем энтер, и у нас в этой ячейки в зависимости от данных должно появится значение Лож или Истина
ЛОЖ — если А1 не будет равно А2
Истина — если А1 будет ровно А2
если применить этот вариант на столбце Пример2, то как вы поняли везде будет значение Лож
Этот вариант хорош только в редких случаях, но его тоже надо знать, его ограничение в том что эта формула сравнивает себя и следующее значение, тоесть она применима только одновременно к двум ячейкам, а не ко всему столбцу. Но если у вас данные как с столбце Пример2, тогда читайте дальше )
[expert_bq id=»1570″]В этом руководстве вы узнаете, как подсчитывать уникальные значения в Excel с помощью формул и как это делать в сводной таблице. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Позже в этом руководстве мы обсудим несколько других подходов для подсчета уникальных значений разных типов. И поскольку в основном это вариации этой базовой формулы, имеет смысл присмотреться к ней поближе. Если вы понимаете, как это работает, вы можете настроить его для своих данных. Если кого-то не интересуют технические детали, можете сразу перейти к следующему примеру.Как работает формула подсчета уникальных значений?
5. Теперь нажмите кнопку прямоугольника, появится диалоговое окно Kutools for Excel , выберите диапазон, содержащий уникальные значения, которые необходимо извлечь, а затем нажмите OK .