Power query как посчитать счетесли
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Функции таблиц
Этот параметр представляет собой список текстовых значений, указывающих имена столбцов результирующей таблицы. Этот параметр обычно используется в функциях построения таблиц, таких как Table.FromRows и Table.FromList.
Критерии сравнения
Критерий сравнения можно указать как одно из следующих значений:
- Числовое значение, указывающее порядок сортировки. Дополнительные сведения: Порядок сортировки
- Чтобы вычислить ключ, используемый для сортировки, можно использовать функцию с одним аргументом.
- Чтобы выбрать ключ и порядок управления, критерий сравнения может быть списком, содержащим ключ и порядок.
- Чтобы полностью управлять сравнением, можно использовать функцию с двумя аргументами, которая возвращает –1, 0 или 1, учитывая связь между левыми и правыми входными данными. Для делегирования этой логики можно использовать метод Value.Compare.
Примеры см. в описании Table.Sort.
Критерий количества или условия
Обычно эти критерии используются в операциях упорядочивания или строк. Он определяет количество строк, возвращаемых в таблице, и может принимать две формы — число или условие.
- Число указывает, сколько значений следует вернуть, а также соответствующую функцию.
- Если указано условие, возвращаются строки, содержащие значения, которые изначально соответствуют условию. После сбоя условия никакие дополнительные значения не учитываются.
Обработка дополнительных значений
Дополнительные значения используются для указания того, как функция должна управлять дополнительными значениями в строке. Этот параметр указывается в виде числа, которое сопоставляется с параметрами ниже.
ExtraValues.List = 0 ExtraValues.Error = 1 ExtraValues.Ignore = 2
Обработка отсутствующих столбцов
Этот параметр используется для указания того, как функция должна обрабатывать отсутствующие столбцы. Этот параметр указывается в виде числа, которое сопоставляется с параметрами ниже.
MissingField.Error = 0 MissingField.Ignore = 1 MissingField.UseNull = 2;
Этот параметр используется в операциях со столбцами или преобразованиями, например в Table.TransformColumns. Дополнительные сведения: MissingField.Type
Порядок сортировки
Порядок сортировки используется для указания того, как должны быть отсортированы результаты. Этот параметр указывается в виде числа, которое сопоставляется с параметрами ниже.
Order.Ascending = 0 Order.Descending = 1
Критерии уравнения
Критерий равенства для таблиц можно указать как:
- значение функции, которое является:
- селектором ключа, определяющим столбец в таблице для применения условий равенства;
- Функция сравнения, используемая для указания типа применяемого сравнения. Можно указать встроенные функции сравнения. Дополнительные сведения: Функции сравнения
Примеры см. в описании Table.Distinct.
Обратная связь
Были ли сведения на этой странице полезными?
Power Query Базовый №17. Группировка
В этом уроке мы научимся выполнять группировку в Power Query. Группировка — это операция, при которой вы находите уникальные значения в одном поле и выполняете агрегирования каких-то других полей. Например, в этом уроке мы найдем сумму продаж на каждый день, а еще посчитаем количество сделок на каждый день, найдем максимальную сделку на каждый день и выполним другие агрегирования.
Группировку можно также выполнять и по нескольким полям. В таком случае мы получим агрегаты для каждого уникального сочетания этих нескольких полей.
Группировка в Power Query — это аналог операции GROUP BY из SQL. Так же это напоминает функции СУММЕСЛИ/СУММЕСЛИМН, СЧЕТЕСЛИ/СЧЕТЕСЛИМН, СРЗНАЧЕСЛИ и другие подобные функции. Если вы искали аналоги этих функций в Power Query, то вам нужно воспользоваться группировкой.
Решение
Задачу можно решить используя только лишь пользовательский интерфейс Power Query.
Нужно выбрать столбец, по которому мы будем делать группировку, перейти на вкладку Главная — Группировать по.
Далее нужно выбрать столбцы для агрегирования и вид агрегирования.
Примененные функции
- Table.RemoveColumns
- Table.Skip
- Table.PromoteHeaders
- Table.SelectRows
- Table.TransformColumns
- Text.Start
- Text.From
- Table.TransformColumnTypes
- Int64.Type
- Table.Group
- List.Sum
- Table.Sort
- Order.Ascending
- Table.AddColumn
Код
let source = Excel.Workbook(File.Contents(Путь), null, true), get_table = source<[Name = "Источник"]>[Data], cols_select = Table.RemoveColumns(get_table, ), rows_skip = Table.Skip(cols_select, 10), tab_promote_headers = Table.PromoteHeaders( rows_skip, [PromoteAllScalars = true] ), rows_skip_2 = Table.Skip(tab_promote_headers, 1), rows_select = Table.SelectRows(rows_skip_2, each ([Время] <> null)), col_text_start = Table.TransformColumns( rows_select, > ), types = Table.TransformColumnTypes( col_text_start, < , , , , , > ), tab_group = Table.Group( types, , < , , > ), tab_sort = Table.Sort(tab_group, >), tab_add_col_profit = Table.AddColumn( tab_sort, "Прибыль", each [Сумма] - [Себестоимость], type number ), value_sum = List.Sum(tab_add_col_profit[Прибыль]), tab_link = tab_add_col_profit, #"tab_add_col_%" = Table.AddColumn( tab_link, "Доля от общей прибыли", each [Прибыль] / value_sum, Percentage.Type ) in #"tab_add_col_%"Этот урок входит в Базовый курс Power Query
Номер урока Урок Описание 1 Зачем нужен Power Query. Обзор возможностей Этот урок сам по себе является мини-курсом. Здесь вы узнаете для каких видов операций с данными создан Power Query. 2 Подключение Excel Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов. 3 Подключение CSV/TXT, таблиц, диапазонов Подключаемся к к файлам CSV/TXT, Excel. 4 Объединить таблицы по вертикали Учимся объединять две таблицы по вертикали — combine. 5 Объединить по вертикали все таблицы одной книги друг за другом Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel. 6 Объединить по вертикали все файлы в папке Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке. 7 Объединение таблиц по горизонтали Учимся объединять таблицы по горизонтали — JOIN, merge. 8 Объединить таблицы с агрегированием Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY. 9 Анпивот (Unpivot) Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными. 10 Многоуровневый анпивот (Анпивот с подкатегориями) Более сложный вариант Анпивота — в строках находится несколько измерений. 11 Скученные данные Данные собраны в одном столбце, нужно правильно его разбить на несколько. 12 Скученные данные 2 Разбираем еще один пример скученных данных. 13 Ссылка на другую строку Как сослаться на другую строку. 14 Ссылка на другую строку 2 Как сослаться на другую строку, используя объединение по горизонтали. 15 Виды объединения таблиц по горизонтали Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN. 16 Виды объединения таблиц по горизонтали 2 Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN. 17 Группировка Изучаем операцию группировки с агрегированием — GROUP BY. 18 Консолидация множества таблиц пользовательской функцией Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции. 19 Деление на справочник и факт Разделим один датасет на два датасета: справочник и факт. 20 Создание параметра Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query. 21 Таблица параметров Создадим целую таблицу параметров и будем их использовать в запросах Power Query. 22 Объединение таблиц по вертикали, когда не совпадают заголовки столбцов Как объединить две таблицы по вертикали, если названия столбцов не совпадают. 23 Поиск ключевых слов Научимся искать ключевые слова в текстовом поле. 24 Поиск ключевых слов 2 Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию. Поиск совпадений в двух списках
Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов — подсчет количества и вывод совпадающих значений в двух списках. Предположим, что у нас есть два диапазона данных, которые мы хотим сравнить:
Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен — Создать (Formulas — Name Manager — Create) . Также можно превратить таблицы в «умные» с помощью сочетания клавиш Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table) .Подсчет количества совпадений
Для подсчета количества совпадений в двух списках можно использовать следующую элегантную формулу:
В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2)) Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек. Во-первых, функция СЧЁТЕСЛИ (COUNTIF) . Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации: =СЧЁТЕСЛИ( Где_искать ; Что_искать ) Обычно первый аргумент — это диапазон, а второй — ячейка, значение или условие (одно!), совпадения с которым мы ищем в диапазоне. В нашей же формуле второй аргумент — тоже диапазон. На практике это означает, что мы заставляем Excel перебирать по очереди все ячейки из второго списка и подсчитывать количество вхождений каждого из них в первый список. По сути, это равносильно целому столбцу дополнительных вычислений, свернутому в одну формулу:
Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции — суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl + Shift + Enter . Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.Вывод списка совпадений формулой массива
Если нужно не просто подсчитать количество совпадений, но и вывести совпадающие элементы отдельным списком, то потребуется не самая простая формула массива:
В английской версии это будет, соответственно: =INDEX(Список1;MATCH(1;COUNTIF(Список2;Список1)*NOT(COUNTIF($E$1:E1;Список1));0)) Логика работы этой формулы следующая:- фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
- фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
- и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент
Не забудьте в конце ввода этой формулы нажать сочетание клавиш Ctrl + Shift + Enter , т.к. она должна быть введена как формула массива.
Возникающие на избыточных ячейках ошибки #Н/Д можно дополнительно перехватить и заменить на пробелы или пустые строки «» с помощью функции ЕСЛИОШИБКА (IFERROR) .
Вывод списка совпадений с помощью слияния запросов Power Query
На больших таблицах формула массива из предыдущего способа может весьма ощутимо тормозить, поэтому гораздо удобнее будет использовать Power Query. Это бесплатная надстройка от Microsoft, способная загружать в Excel 2010-2013 и трансформировать практически любые данные. Мощь и возможности Power Query так велики, что Microsoft включила все ее функции по умолчанию в Excel начиная с 2016 версии.
Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table) :
Excel превратит нашу таблицу в «умную» и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить — Закрыть и загрузить в. (Close & Load To. ) и выбрать в появившемся окне Только создать подключение (Create only connection) :
Затем повторяем то же самое со вторым диапазоном.
И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные — Объединить запросы — Объединить (Get Data — Merge Queries — Merge) :
В открывшемся окне делаем три вещи:
- выбираем наши таблицы из выпадающих списков
- выделяем столбцы, по которым идет сравнение
- выбираем Тип соединения = Внутреннее (Inner Join)
После нажатия на ОК на экране останутся только совпадающие строки:
Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load) :
Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 .
Макрос для вывода списка совпадений
Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer) . Если ее не видно, то отобразить ее можно через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .
В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert — Module и затем скопировать туда код нашего макроса:
Sub Find_Matches_In_Two_Lists() Dim coll As New Collection Dim rng1 As Range, rng2 As Range, rngOut As Range Dim i As Long, j As Long, k As Long Set rng1 = Selection.Areas(1) Set rng2 = Selection.Areas(2) Set rngOut = Application.InputBox(Prompt:="Выделите ячейку, начиная с которой нужно вывести совпадения", Type:=8) 'загружаем первый диапазон в коллекцию For i = 1 To rng1.Cells.Count coll.Add rng1.Cells(i), CStr(rng1.Cells(i)) Next i 'проверяем вхождение элементов второго диапазона в коллекцию k = 0 On Error Resume Next For j = 1 To rng2.Cells.Count Err.Clear elem = coll.Item(CStr(rng2.Cells(j))) If CLng(Err.Number) = 0 Then 'если найдено совпадение, то выводим со сдвигом вниз rngOut.Offset(k, 0) = rng2.Cells(j) k = k + 1 End If Next j End Sub
Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl , оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F8 . Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:
Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.
Ссылки по теме
- Поиск различий в двух списках Excel
- Слияние двух списков без дубликатов (3 способа)
- Что такое макросы, как их использовать, куда копировать код макросов на Visual Basic