Страницы: 1
RSS
Как подсчитать количество уникальных номеров, Необходимо подсчитать количество уникальных номеров (VBA)
 
Доброго времени суток. Нужна ваша помощь. Есть таблица (200 тыс.строк), в столбце "Номера" записаны как отдельные номера, так и диапазоны номеров, и то и другое может неоднократно повторяться и пересекаться друг с другом. Нужно подсчитать количество уникальных номеров. Желательно VBA, но можно и PQ. Заранее благодарен.
Excel непознаваем как атом.
 
mtts54,

Вариант в PQ:

P.S. Чуть подправил запрос, чтобы результат был корректен и для случаев, когда строки в стартовой таблице повторяются.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 1, 1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Номера", "temp"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","-","..",Replacer.ReplaceText,{"temp"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"temp", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "temp"),
    transform = Table.TransformColumns(#"Split Column by Delimiter", {"temp", each Expression.Evaluate("{"&_&"}", #sections[Section1])}),
    #"Expanded {0}" = Table.ExpandListColumn(transform, "temp"),
    #"Removed Duplicates" = Table.Distinct(#"Expanded {0}"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Технология", "Номера", "Индекс"}, {{"Количество", each Table.RowCount(Table.Distinct(_)), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Индекс"})
in
    #"Removed Columns"
Изменено: Aleksei_Zhigulin - 25.12.2018 22:38:36 (Корректировка запроса)
 
Aleksei_Zhigulin, Стало интересно, можно по подробнее, что делает?
Код
Expression.Evaluate("{"&_&"}", #sections[Section1])}
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Aleksei_Zhigulin,на файле-примере работает отлично. Реальный файл протестирую завтра. Спасибо!
Excel непознаваем как атом.
 
др вариант на PQ
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Александр,

Мы хотим получить список чисел, входящих в диапазон (1-75, например). В PQ этот список чисел можно записать как {1..75}. Но просто передать значение строки = "1..75" и сцепить его с фигурными скобками недостаточно, т.к. PQ воспринимает 1..75 как текст. Expression.Evaluate позволяет из текстового выражения {1..75} получить результат его вычисления, т.е. на выходе мы получаем список чисел от 1 до 75. При этом, если не указать второй параметр, то результатом вычисления будет ошибка. Это можно объяснить так. Если Expression.Evaluate не нужно "внешней" информации, для того, чтобы вычислить выражение, он это успешно делает без второго параметра, например Expression.Evaluate("1+2+3") или Expression.Evaluate("{"&"1..75"&"}"). В противном случае, нужно указать "среду вычисления" (expression environment), иначе Expression.Evaluate не поймёт любые названия функций PQ, названия таблиц, столбцов и т.д. Поскольку в данном примере используются только встроенные функции PQ, можно указать второй параметр #shared. Используемый в примере #sections[Section1] - более универсальный вариант, который должен корректно отработать и с пользовательскими функциями.
 
Цитата
mtts54 написал: Желательно VBA
Код
Function СЧЁТУНИКДИАПАЗОН(iStr$, Optional dlm1$ = ",", Optional dlm2$ = "-")
Dim arr1, arr2, I&, J&
On Error Resume Next
With CreateObject("Scripting.Dictionary")
arr1 = Split(Trim(iStr), dlm1)
If UBound(arr1) > 0 Then
    For I = 0 To UBound(arr1)
        arr2 = Split(arr1(I), dlm2)
        GoSub Arr_2
    Next
Else
    arr2 = Split(arr1(0), dlm2)
    GoSub Arr_2
End If
СЧЁТУНИКДИАПАЗОН = .Count
Exit Function
Arr_2:
    If UBound(arr2) > 0 Then
        For J = Val(arr2(0)) To Val(arr2(1))
            iTemp = .Item(CStr(J))
        Next
    Else
        iTemp = .Item(CStr(arr2(0)))
    End If
    Return
End With
End Function
Изменено: Sanja - 25.12.2018 18:41:29
Согласие есть продукт при полном непротивлении сторон
 
Aleksei_Zhigulin, а List.Numbers() не проще применить? Нет, с точки зрения разных подходов и чтобы заинтересовать начинающих подход интересный, конечно. Но все таки?
И это, вы уж до конца рассказывайте.
#sections - это запись содержащая все имена запросов/функций/параметров в текущей книге, включая запрос, в котором вы к этой записи обращаетесь. Поэтому с этим приемом нужно быть аккуратным, что не влезть в рекурсию, а потом не понимать откуда лезут баги.
Изменено: PooHkrd - 25.12.2018 18:27:14
Вот горшок пустой, он предмет простой...
 
PooHkrd,

ИМХО, в данном примере Expression.Evaluate и проще, и удобнее. Уже готовые списки в условии, как тут устоять.  :) По #sections[Section1] мои знания достаточно ограничены (про рекурсию не знал, спасибо за информацию), но, полагаю, в данном примере использование этого параметра вполне уместно.
 
Aleksei_Zhigulin, Спасибо за пояснения, мне как сказал PooHkrd, полезно знать такие маленькие тонкости :)  
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Доброе время суток.
Ещё один вариант по следам решения Максима Зеленского.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    maker = Table.AddColumn(Source, "Количество", each
        List.Count(List.Distinct(Expression.Evaluate("{" & Text.Replace(Text.From([Номера]), "-", "..") & "}"))),
    Int64.Type)
in
    maker
Изменено: Андрей VG - 25.12.2018 19:41:18
 
Андрей VG,

Вообще красота! Действительно, список же может быть и в формате {1,3..5,10,12..15}, как-то не подумал об этом. Также получается, что я заблуждался насчёт обязательности второго параметра Expression.Evaluate, если в выражении используются функции PQ (Александр, извиняюсь за дезинформацию).
 
Цитата
Aleksei_Zhigulin написал:
извиняюсь за дезинформацию
Не извиняйтесь. Её не было. Если бы Андрей применил функцию в кавычках
Код
"Text.Replace(Text.From([Номера]), "-", "..")"

тогда ему пришлось бы указывать среду вычисления. Среда указывается для функции Expression.Evaluate, чтобы она понимала имена функций и параметров в выражении заданном явно в текстовом виде. Скажем если вы будете в текстовом виде задавать название запроса или пользовательской функции из текущей книги, то нужно указывать среду вычисления в виде #section, если же используется функция из стандартной библиотеки, тогда применяется #shared, если нужно использовать и то и другое, тогда нужно комбинировать из этих записей общую запись и её имя указывать как среду. Кладезь мудроты здесь.
В его же выражении функции выдают на выходе текстовый результат, при этом сами они вызываются как обычно.
Изменено: PooHkrd - 26.12.2018 12:36:46
Вот горшок пустой, он предмет простой...
 
Aleksei_Zhigulin, протестировал Ваше решение на реальных данных в 1,5 тыс строк - всё считает верно. Узнал много полезного. Еще раз спасибо! Попробую и другие предлагаемые решения.
Excel непознаваем как атом.
 
PooHkrd,

Да, точно, спасибо. Собственно, и в моём варианте второй параметр Expression.Evaluate необязателен, как выяснилось. Ошибка возникала при другой вариации кода.
Изменено: Aleksei_Zhigulin - 26.12.2018 12:48:38
 
mtts54,

Не за что. На Вашем месте я бы использовал вариант Андрея VG, там нет ничего лишнего.
 
В варианте от Андрей VG, у меня вылетает ошибка. Не пойму, в чем дело.
Excel непознаваем как атом.
 
mtts54, вместо Source в формуле, напишите Источник. Должно заработать. Или первый шаг переименуйте в Source оно, собственно без разницы.
Изменено: PooHkrd - 26.12.2018 13:45:44
Вот горшок пустой, он предмет простой...
 
mtts54, Наименование измените :) Source на Источник
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Андрей VG,всё работает отлично, спасибо! И с наступающим Новым Годом!
Excel непознаваем как атом.
 
Было подчеркнуто
Цитата
mtts54 написал: Желательно VBA
а мой вариант даже не рассматривается (сообщение #7)?
Согласие есть продукт при полном непротивлении сторон
 
Sanja, ха, а ведь да :)  
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Sanja,очень даже рассматривается. Поднятый вопрос является промежуточным этапом обработки данных. Предыдущие и последующие этапы работают на VBA от кнопочек и поэтому весьма желательно и этот этап пройти на VBA.
Цитата
mtts54 написал:
Попробую и другие предлагаемые решения.
Пока просто не хватает времени протестировать.  :(  Спасибо за Ваши усилия и потраченное время на то, чтобы мне помочь. О результатах напишу.
Excel непознаваем как атом.
 
Sanja,протестировал на реальном файле 47,2 тыс.строк. Отработал за 1мин20сек, расхождений в подсчетах с вариантом Андрей VG на PQ нет. Думаю, что на больших файлах буду применять PQ, в остальных случаях (коих большинство), вполне справится VBA. Еще раз спасибо!
Excel непознаваем как атом.
Страницы: 1
Наверх