Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Присвоение группы для списка значений, пропорционально их доли их количества в списке
 
#1
27.03.2020 15:14:59
Добрый день! Есть задача, простая на первый взгляд, но как решить не знаю. Есть список значений, который необходимо распределить на 3 группы:
Группа А = первые 15% значений
Группа Б = следующие 35% значений
Группа С = оставшиеся 50% значений
Важно! % считается не от суммы значений а от их количества.

Есть следующие сложности:
1) В списке есть нулевые значения, которые нужно исключить при присвоении группы.
2) Сортировка не должна влиять на итоговый результат.
3) Пороги должны быть динамическими. Например в файле-примере 149 не нулевых значений. Группа А = 15% * 149 = 22,35, но мы должны присвоить группу 26 значениям, т. к. 22 значение = 26 значению = 90.
Изменено: Murderface_ - 27.03.2020 17:13:27 (добавил решения)
 
 
 
#2
27.03.2020 15:23:55
Murderface_, а где желаемый результат в файле? добрый день
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
27.03.2020 15:31:05
Mershik, дополнил пример.
 
 
 
#4
27.03.2020 15:57:32
Коллеги, прошу прощения! Не совсем правильно указал желаемый результат для Группы Б, будет + 2 значения = 71. Обновил пример.
 
 
 
#5
27.03.2020 16:50:37
Murderface_, Murderface_,

не уверен опробуйте в D2
Код
=ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС($A:$A;2;1):ИНДЕКС($A:$A;ЦЕЛОЕ(СЧЁТЕСЛИ($A:$A;">0")*0,15);1);Таблица1[@Значение])>=1;"Группа А";
ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС($A:$A;2;1):ИНДЕКС($A:$A;ЦЕЛОЕ(СЧЁТЕСЛИ($A:$A;">0")*0,35)+2+ЦЕЛОЕ(СЧЁТЕСЛИ($A:$A;">0")*0,15);1);Таблица1[@Значение])>=1;"Группа Б";
ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС($A:$A;2;1):ИНДЕКС($A:$A;ЦЕЛОЕ(СЧЁТЕСЛИ($A:$A;">0")*0,35)+ЦЕЛОЕ(СЧЁТЕСЛИ($A:$A;">0")*0,15)++ЦЕЛОЕ(СЧЁТЕСЛИ($A:$A;">0")*0,5);1);Таблица1[@Значение])>=1;"Группа С";
"не в группе")))
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#6
27.03.2020 16:58:27
Mershik, спасибо! К сожалению, при сортировке не по убыванию результат некорректный  :(  
 
 
 
#7
27.03.2020 17:03:30
Murderface_, так приведите пример нормальный - вы же тут уже стопятьсот лет) и что сейчас не правильно конкретно?
Изменено: Mershik - 27.03.2020 17:04:05
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#8
27.03.2020 17:08:07
Вроде у самого получилось сделать, только вот с дополнительной таблицей  :(  
 
 
 
#9
27.03.2020 17:09:50
Mershik, так я же в первом сообщении про сортировку писал  :)  А в файле специально ее оставил, чтобы проще было решение подобрать. Пятница, уже голова не варит.
 
 
 
#10
27.03.2020 17:32:34
Код
=ПРОСМОТР(РАНГ.СР([@Значение];[Значение]);МУМНОЖ(({1:2:3:4}>={1;2;3;4})*ОКРВВЕРХ(СЧЁТЕСЛИ([Значение];">0")*{0;15;35;50}%;1);{1:1:1:1});"Группа "&{"А":"Б":"С"})

На всякий случай, в PQ
Код
let
    Source     = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[Значение]],
    Filtered   = Table.SelectRows(Source, each [Значение] <> 0),
    Sorted     = Table.Sort(Source,{{"Значение", Order.Descending}}),
    Indexed    = Table.AddIndexColumn(Sorted, "i", 0),
    RowCount   = Table.RowCount(Filtered),
    ABC        = List.Transform({0.15,0.35,0.50},each Number.Round(RowCount*_)),
    fn=(i)     =>List.Sum(List.Range(ABC,0,i)),
    Cumulative = List.Buffer(List.Generate(()=>[i=1,s=fn(i)], each [i]<=3,each [i=[i]+1,s=fn(i)],each [s])),
    Grouped    = Table.Group(Indexed, {"i"}, {{"Доля", each Table.RowCount(_)/RowCount, Percentage.Type},{"Количество",each Table.RowCount(_), type number}},0,(a,b)=>List.PositionOf(Cumulative,b[i])+1)
in
    Grouped
 
 
 
#11
27.03.2020 17:36:16
Андрей Лящук, ого, выглядит круто! Спасибо! В понедельник попробую разобрать ваше решение.
 
 
 
#12
27.03.2020 17:46:59
небольшая поправка по формуле
Код
=ЕСЛИ([@Значение]>0;ПРОСМОТР(РАНГ([@Значение];[Значение]);МУМНОЖ(({1:2:3:4}>={1;2;3;4})*ОКРВВЕРХ(СЧЁТЕСЛИ([Значение];">0")*{0;15;35;50}%;1);{1:1:1:1});"Группа "&{"А":"Б":"С"});"Нет в рейтинге")
 
 
 
Страницы: 1
Наверх