Как Оставить Только Уникальные Значения в Столбце Excel Power Query • Преобразование данных

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 присвоил ему текстовый тип данных. Если же вы хотите что-то уточнить, обращайтесь ко мне!
    Прежде всего избавимся от четырёх верхних строк, которые не представляют для нас никакой ценности. Для этого, на вкладке Главная выбираем Сократить строки -> Удалить строки -> Удаление верхних строк . Далее, в появившемся окне указываем количество строк, которые необходимо удалить и жмём ОК.
    99-1-список сотрудников

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

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

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

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

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

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

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

    Как Оставить Только Уникальные Значения в Столбце Excel Power Query • Преобразование данных

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

    Как Оставить Только Уникальные Значения в Столбце Excel Power Query • Преобразование данных

    Как Оставить Только Уникальные Значения в Столбце Excel Power Query • Преобразование данных

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

    Как Оставить Только Уникальные Значения в Столбце Excel Power Query • Преобразование данных

    Как Оставить Только Уникальные Значения в Столбце Excel Power Query • Преобразование данных

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

    Как Оставить Только Уникальные Значения в Столбце Excel Power Query • Преобразование данных

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

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

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

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

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

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

    excel дубли

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

    удалить дубли в excel

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

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

    как убрать дубли в excel

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

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

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

    как найти дубли в столбце в excel

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

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

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector