Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выбрка уникальных значений в разрезе нескольких столбцов - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбрка уникальных значений в разрезе нескольких столбцов (Формулы/Formulas)
Выбрка уникальных значений в разрезе нескольких столбцов
gwelw Дата: Понедельник, 30.10.2023, 22:23 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

2016
Добрый день.
Есть таблица на 5 столбцов

Можно ли как-то сделать сводную таблицу с помощью формул, где выборка будет по искоммоу столбцу,
а в остальных будет подсчет уникальных значений каждого из них?

По примеру из вложения:
Необходимо сделать выборку для каждого уникального "Софта".
Подсчитать кол-во клиентов для которых он установлен + кол-во уникальных урлов
и отдельно сматчить коды услуг в строчку, что бы на выходе получилось что-то типа:
win11 2 2 k11, k12
win10 1 1 k13
AntiMW 3 2 k49,k50
и т.д.

Сложность еще в том, что в таблице под миллион строк и любые эксперименты с формулами убивают мне эксель)
Я так понимаю необходимо сделать сначала выборку уникальных по одному столбцу на второй лист, и потом уже как-то на основе этой выборки делать остальные?
Или как-то через сводные это реализуется?
К сообщению приложен файл: 1269699.xlsx (10.2 Kb)


Gwel

Сообщение отредактировал gwelw - Понедельник, 30.10.2023, 22:23
 
Ответить
СообщениеДобрый день.
Есть таблица на 5 столбцов

Можно ли как-то сделать сводную таблицу с помощью формул, где выборка будет по искоммоу столбцу,
а в остальных будет подсчет уникальных значений каждого из них?

По примеру из вложения:
Необходимо сделать выборку для каждого уникального "Софта".
Подсчитать кол-во клиентов для которых он установлен + кол-во уникальных урлов
и отдельно сматчить коды услуг в строчку, что бы на выходе получилось что-то типа:
win11 2 2 k11, k12
win10 1 1 k13
AntiMW 3 2 k49,k50
и т.д.

Сложность еще в том, что в таблице под миллион строк и любые эксперименты с формулами убивают мне эксель)
Я так понимаю необходимо сделать сначала выборку уникальных по одному столбцу на второй лист, и потом уже как-то на основе этой выборки делать остальные?
Или как-то через сводные это реализуется?

Автор - gwelw
Дата добавления - 30.10.2023 в 22:23
Hugo Дата: Вторник, 31.10.2023, 00:43 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
"Софтов" много?
На миллион формулы будут подвешивать...
Как сводной или PQ не подскажу, знаю как макросом (быстро/просто), но это не по теме.


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
Сообщение"Софтов" много?
На миллион формулы будут подвешивать...
Как сводной или PQ не подскажу, знаю как макросом (быстро/просто), но это не по теме.

Автор - Hugo
Дата добавления - 31.10.2023 в 00:43
AlienSphinx Дата: Вторник, 31.10.2023, 07:12 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 6 ±
Замечаний: 0% ±

можно в power pivot, 3 простые меры
[vba]
Код
n_clients:=DISTINCTCOUNT('Таблица1'[Клиент])
n_urls:=DISTINCTCOUNT('Таблица1'[Урл на софт])
svs_codes:=CONCATENATEX(DISTINCT('Таблица1'[Код услуги]), 'Таблица1'[Код услуги], ", ")
[/vba]
К сообщению приложен файл: 2472539.xlsx (141.2 Kb)


Сообщение отредактировал AlienSphinx - Вторник, 31.10.2023, 07:13
 
Ответить
Сообщениеможно в power pivot, 3 простые меры
[vba]
Код
n_clients:=DISTINCTCOUNT('Таблица1'[Клиент])
n_urls:=DISTINCTCOUNT('Таблица1'[Урл на софт])
svs_codes:=CONCATENATEX(DISTINCT('Таблица1'[Код услуги]), 'Таблица1'[Код услуги], ", ")
[/vba]

Автор - AlienSphinx
Дата добавления - 31.10.2023 в 07:12
gwelw Дата: Вторник, 31.10.2023, 11:45 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

2016
"Софтов" много?


Порядка 2000тыс уникальных. На самом деле не принципиально чем это делать, макросами, Пивотом или ВБА. Топик формул, потому что первые в голову пришли они)

power pivot

Что бы меры завести надо сначала в модель данных всю таблицу перенести же?


Gwel

Сообщение отредактировал gwelw - Вторник, 31.10.2023, 12:03
 
Ответить
Сообщение
"Софтов" много?


Порядка 2000тыс уникальных. На самом деле не принципиально чем это делать, макросами, Пивотом или ВБА. Топик формул, потому что первые в голову пришли они)

power pivot

Что бы меры завести надо сначала в модель данных всю таблицу перенести же?

Автор - gwelw
Дата добавления - 31.10.2023 в 11:45
AlienSphinx Дата: Вторник, 31.10.2023, 13:39 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 6 ±
Замечаний: 0% ±

надо сначала в модель данных всю таблицу перенести же?
да, надо в модель. Если так некомфортно, то можно прямо в PQ это же сделать
[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(
        Source, "Софт",
        {{"clients", each List.Count(List.Distinct([Клиент]))},
        {"urls", each List.Count(List.Distinct([Урл на софт]))},
        {"services", each Text.Combine(List.Distinct([Код услуги]), ", ")}}
    )
in
    group
[/vba]
К сообщению приложен файл: 1269699_pq.xlsx (17.6 Kb)
 
Ответить
Сообщение
надо сначала в модель данных всю таблицу перенести же?
да, надо в модель. Если так некомфортно, то можно прямо в PQ это же сделать
[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(
        Source, "Софт",
        {{"clients", each List.Count(List.Distinct([Клиент]))},
        {"urls", each List.Count(List.Distinct([Урл на софт]))},
        {"services", each Text.Combine(List.Distinct([Код услуги]), ", ")}}
    )
in
    group
[/vba]

Автор - AlienSphinx
Дата добавления - 31.10.2023 в 13:39
gwelw Дата: Вторник, 31.10.2023, 14:26 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

2016
да, надо в модель.

Эксель пишет что памяти на такое не хватает:)
Напрямую через PQ с трудом, но съело.
Не мог еще PQ запустить у себя, в примере входные данные поменял, норм подтянулось, спасибо!


Gwel
 
Ответить
Сообщение
да, надо в модель.

Эксель пишет что памяти на такое не хватает:)
Напрямую через PQ с трудом, но съело.
Не мог еще PQ запустить у себя, в примере входные данные поменял, норм подтянулось, спасибо!

Автор - gwelw
Дата добавления - 31.10.2023 в 14:26
Hugo Дата: Вторник, 31.10.2023, 17:11 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
gwelw, попробуйте макросом, может понравится ))
[vba]
Код

Sub svod()
    Dim a, i&, t$, d1 As Object, d2 As Object, d3 As Object
    Dim el, col
    
    Set d1 = CreateObject("Scripting.Dictionary"): d1.comparemode = 1
    Set d2 = CreateObject("Scripting.Dictionary"): d2.comparemode = 1
    Set d3 = CreateObject("Scripting.Dictionary"): d3.comparemode = 1
    
    'тут можно взять данные в массив любым способом
    a = Range("E2", Cells(Rows.Count, "A").End(xlUp)).Value

    On Error Resume Next
        For i = 1 To UBound(a)
            t = a(i, 2)
            If Not d1.exists(t) Then
            d1.Add t, New Collection
            d2.Add t, New Collection
            d3.Add t, New Collection
            End If
            d1.Item(t).Add a(i, 1), a(i, 1)
            d2.Item(t).Add a(i, 5), a(i, 5)
            d3.Item(t).Add a(i, 4), a(i, 4)
        Next
    On Error GoTo 0

    ReDim a(1 To d1.Count + 1, 1 To 4)
    i = 1
    a(i, 1) = "Названия строк"
    a(i, 2) = "n_clients"
    a(i, 3) = "n_urls"
    a(i, 4) = "svs_codes"

    For Each el In d1.keys
    t = "": i = i + 1
        For Each col In d3.Item(el)
            t = t & ", " & col
        Next
    a(i, 1) = el
    a(i, 2) = d1.Item(el).Count
    a(i, 3) = d2.Item(el).Count
    a(i, 4) = Mid(t, 3)
    Next
    
Workbooks.Add.Sheets(1).[a1:d1].Resize(d1.Count + 1) = a
End Sub

[/vba]
Вместо Distinct тут коллекции. В словарях.


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
Сообщениеgwelw, попробуйте макросом, может понравится ))
[vba]
Код

Sub svod()
    Dim a, i&, t$, d1 As Object, d2 As Object, d3 As Object
    Dim el, col
    
    Set d1 = CreateObject("Scripting.Dictionary"): d1.comparemode = 1
    Set d2 = CreateObject("Scripting.Dictionary"): d2.comparemode = 1
    Set d3 = CreateObject("Scripting.Dictionary"): d3.comparemode = 1
    
    'тут можно взять данные в массив любым способом
    a = Range("E2", Cells(Rows.Count, "A").End(xlUp)).Value

    On Error Resume Next
        For i = 1 To UBound(a)
            t = a(i, 2)
            If Not d1.exists(t) Then
            d1.Add t, New Collection
            d2.Add t, New Collection
            d3.Add t, New Collection
            End If
            d1.Item(t).Add a(i, 1), a(i, 1)
            d2.Item(t).Add a(i, 5), a(i, 5)
            d3.Item(t).Add a(i, 4), a(i, 4)
        Next
    On Error GoTo 0

    ReDim a(1 To d1.Count + 1, 1 To 4)
    i = 1
    a(i, 1) = "Названия строк"
    a(i, 2) = "n_clients"
    a(i, 3) = "n_urls"
    a(i, 4) = "svs_codes"

    For Each el In d1.keys
    t = "": i = i + 1
        For Each col In d3.Item(el)
            t = t & ", " & col
        Next
    a(i, 1) = el
    a(i, 2) = d1.Item(el).Count
    a(i, 3) = d2.Item(el).Count
    a(i, 4) = Mid(t, 3)
    Next
    
Workbooks.Add.Sheets(1).[a1:d1].Resize(d1.Count + 1) = a
End Sub

[/vba]
Вместо Distinct тут коллекции. В словарях.

Автор - Hugo
Дата добавления - 31.10.2023 в 17:11
cmivadwot Дата: Среда, 01.11.2023, 01:12 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 84 ±
Замечаний: 0% ±

365
gwelw, тоже поковырял.. пробовал 2500 строчек.. через формулы больше 5 минут висит.... в файле на 500 на 1-2 минуты. Со сводной удобней.... но если виснет то не вариант. Макрос Hugo то быстро, то помедленней))))
К сообщению приложен файл: formula_dop_stolbec_funkcija_s.xlsm (70.7 Kb)
 
Ответить
Сообщениеgwelw, тоже поковырял.. пробовал 2500 строчек.. через формулы больше 5 минут висит.... в файле на 500 на 1-2 минуты. Со сводной удобней.... но если виснет то не вариант. Макрос Hugo то быстро, то помедленней))))

Автор - cmivadwot
Дата добавления - 01.11.2023 в 01:12
Hugo Дата: Среда, 01.11.2023, 13:43 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
Макрос быстро, менее секунды, раз 5 выполнил.. Тормозить может создание книги - ну так можно выводить в уже созданную, или на текущий лист, или на уже добавленный лист, не суть.
А файл завис, пришлось выключить пересчёт и тогда его открывать.
Вот время 10 раз без выгрузки в книгу массива:
0,015625
1,171875E-02
1,171875E-02
0,0078125
1,171875E-02
0,0078125
1,171875E-02
1,171875E-02
0,0078125
1,171875E-02


excel@nxt.ru
webmoney: E265281470651 Z422237915069


Сообщение отредактировал Hugo - Среда, 01.11.2023, 13:46
 
Ответить
СообщениеМакрос быстро, менее секунды, раз 5 выполнил.. Тормозить может создание книги - ну так можно выводить в уже созданную, или на текущий лист, или на уже добавленный лист, не суть.
А файл завис, пришлось выключить пересчёт и тогда его открывать.
Вот время 10 раз без выгрузки в книгу массива:
0,015625
1,171875E-02
1,171875E-02
0,0078125
1,171875E-02
0,0078125
1,171875E-02
1,171875E-02
0,0078125
1,171875E-02

Автор - Hugo
Дата добавления - 01.11.2023 в 13:43
cmivadwot Дата: Среда, 01.11.2023, 14:00 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 84 ±
Замечаний: 0% ±

365
Hugo, да макрос быстро, мне осталось только не понятно... Количество пользоватей.. должны быть только уникалиные.. или один может иметь разный софт и в итоге его нужно считать как два)))
 
Ответить
СообщениеHugo, да макрос быстро, мне осталось только не понятно... Количество пользоватей.. должны быть только уникалиные.. или один может иметь разный софт и в итоге его нужно считать как два)))

Автор - cmivadwot
Дата добавления - 01.11.2023 в 14:00
Hugo Дата: Среда, 01.11.2023, 16:09 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
должны быть только уникалиные

win11 16
win10 7
правильно ведь.

Цитата
или один может иметь разный софт и в итоге его нужно считать как два

там как ключ идёт софт, к нему считаются все остальные - сколько кого на каждый софт.
Можно отдельно посчитать всех уникальных юзеров.


excel@nxt.ru
webmoney: E265281470651 Z422237915069


Сообщение отредактировал Hugo - Среда, 01.11.2023, 16:25
 
Ответить
Сообщение
должны быть только уникалиные

win11 16
win10 7
правильно ведь.

Цитата
или один может иметь разный софт и в итоге его нужно считать как два

там как ключ идёт софт, к нему считаются все остальные - сколько кого на каждый софт.
Можно отдельно посчитать всех уникальных юзеров.

Автор - Hugo
Дата добавления - 01.11.2023 в 16:09
cmivadwot Дата: Среда, 01.11.2023, 17:35 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 84 ±
Замечаний: 0% ±

365
Hugo, я прогон по вымышленным делал на 500... Результат разный. Хз. Иванов и Петров в таблице встречаются 77 раз.
К сообщению приложен файл: 5876372.jpg (17.0 Kb) · 4209370.jpg (31.3 Kb)


Сообщение отредактировал cmivadwot - Среда, 01.11.2023, 17:39
 
Ответить
СообщениеHugo, я прогон по вымышленным делал на 500... Результат разный. Хз. Иванов и Петров в таблице встречаются 77 раз.

Автор - cmivadwot
Дата добавления - 01.11.2023 в 17:35
Hugo Дата: Среда, 01.11.2023, 17:46 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
cmivadwot, на скринах ничего не понять, файл опять завис - не успел отключить пересчёт ((
Напишите понятно словами как нужно считать, что там не правильно сейчас в макросе, какой результат нужен по этому файлу?
Только если будет пример - уберите все формулы ))


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
Сообщениеcmivadwot, на скринах ничего не понять, файл опять завис - не успел отключить пересчёт ((
Напишите понятно словами как нужно считать, что там не правильно сейчас в макросе, какой результат нужен по этому файлу?
Только если будет пример - уберите все формулы ))

Автор - Hugo
Дата добавления - 01.11.2023 в 17:46
cmivadwot Дата: Среда, 01.11.2023, 18:01 | Сообщение № 14
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 84 ±
Замечаний: 0% ±

365
Hugo, автор темы не я))) вечером уменьшу до 100 строк и выложу, чтоб не подвисало. Скрины с телефона... На телефоне читабельно. На скрине результат по макросу и формулам. Словами примерно так - если попадаются 2 ивановых с виндой 10 и одинаковыми "к". То макрос их считает за одного.


Сообщение отредактировал cmivadwot - Среда, 01.11.2023, 18:05
 
Ответить
СообщениеHugo, автор темы не я))) вечером уменьшу до 100 строк и выложу, чтоб не подвисало. Скрины с телефона... На телефоне читабельно. На скрине результат по макросу и формулам. Словами примерно так - если попадаются 2 ивановых с виндой 10 и одинаковыми "к". То макрос их считает за одного.

Автор - cmivadwot
Дата добавления - 01.11.2023 в 18:01
Hugo Дата: Среда, 01.11.2023, 18:41 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
2 ивановых с виндой 10 и одинаковыми "к"

и сразу вопрос - ключ у нас винда, значит считаем сцепку фио+услуга? Или что такое "к"?
Тогда
[vba]
Код
d1.Item(t).Add a(i, 1) & "|" & a(i, 4), a(i, 1) & "|" & a(i, 4)
[/vba]
и на примере результат не изменился, но это частный случай.


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
Сообщение
2 ивановых с виндой 10 и одинаковыми "к"

и сразу вопрос - ключ у нас винда, значит считаем сцепку фио+услуга? Или что такое "к"?
Тогда
[vba]
Код
d1.Item(t).Add a(i, 1) & "|" & a(i, 4), a(i, 1) & "|" & a(i, 4)
[/vba]
и на примере результат не изменился, но это частный случай.

Автор - Hugo
Дата добавления - 01.11.2023 в 18:41
cmivadwot Дата: Среда, 01.11.2023, 19:28 | Сообщение № 16
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 84 ±
Замечаний: 0% ±

365
Hugo, накосячено у меня с количеством урл. посчитано количество услуг "к".. вот на 100
К сообщению приложен файл: 100formula_dop_stolbec_funkcij.xlsm (50.6 Kb)
 
Ответить
СообщениеHugo, накосячено у меня с количеством урл. посчитано количество услуг "к".. вот на 100

Автор - cmivadwot
Дата добавления - 01.11.2023 в 19:28
Hugo Дата: Среда, 01.11.2023, 19:37 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
Не вижу где накосячено. Всё сходится.
Где какое число неправильное, какое должно быть и почему?


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеНе вижу где накосячено. Всё сходится.
Где какое число неправильное, какое должно быть и почему?

Автор - Hugo
Дата добавления - 01.11.2023 в 19:37
cmivadwot Дата: Среда, 01.11.2023, 19:47 | Сообщение № 18
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 84 ±
Замечаний: 0% ±

365
Hugo, у меня в формуле считает количество услуг "к" у клиента, а потом они перечисляются, а нужно уникальные УРЛ, а потом перечислять услуги "к". Файл открылся? в файле , где сводные... уже вставлено.. посчитано, что получается по формуле, и что получается по макросу ...


Сообщение отредактировал cmivadwot - Среда, 01.11.2023, 19:48
 
Ответить
СообщениеHugo, у меня в формуле считает количество услуг "к" у клиента, а потом они перечисляются, а нужно уникальные УРЛ, а потом перечислять услуги "к". Файл открылся? в файле , где сводные... уже вставлено.. посчитано, что получается по формуле, и что получается по макросу ...

Автор - cmivadwot
Дата добавления - 01.11.2023 в 19:47
Hugo Дата: Среда, 01.11.2023, 19:50 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
Открылся, главное ничего не проверять ))

Названия строк n_clients n_urls svs_codes
win11 16 2 k11, k12
win10 7 1 k13
AntiMW 24 2 k49, k50
win12 1 1 k13

Что тут неправильно, как должно быть?


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеОткрылся, главное ничего не проверять ))

Названия строк n_clients n_urls svs_codes
win11 16 2 k11, k12
win10 7 1 k13
AntiMW 24 2 k49, k50
win12 1 1 k13

Что тут неправильно, как должно быть?

Автор - Hugo
Дата добавления - 01.11.2023 в 19:50
cmivadwot Дата: Среда, 01.11.2023, 20:03 | Сообщение № 20
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 84 ±
Замечаний: 0% ±

365
Hugo, вот с 1 формулой остальное значения... должно не тупить
К сообщению приложен файл: 8738614.xlsm (48.6 Kb)
 
Ответить
СообщениеHugo, вот с 1 формулой остальное значения... должно не тупить

Автор - cmivadwot
Дата добавления - 01.11.2023 в 20:03
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбрка уникальных значений в разрезе нескольких столбцов (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!