Как вернуть размещение номеров по критериям из таблицы?
Я столкнулся с проблемой, которую не могу понять. Мне нужно вернуть размещение списка значений на основе отдельного критерия.
У меня есть таблица, которая выглядит так:
Я ищу способ автоматического заполнения столбца места с помощью формулы. Я дошел до:
=LET(
data,$B$3:$D$10,
gender_col,2,total_col,3,
f,FILTER(data,CHOOSECOLS(data,gender_col)=$C3),
s,SORT(f,total_col,-1),
c,CHOOSECOLS(s,3),
c
)
Это сортирует итоги по убыванию, но я не понял, как вернуть размещение.
Ответы 4
Вот один из способов достижения желаемого результата (без итерации LAMBDA()):
=LET(
_Data, A15:C22,
_Gender, INDEX(_Data,,2),
_Male, FILTER(_Data,_Gender = "m"),
_SortedM, HSTACK(SORT(_Male,3,-1),SEQUENCE(ROWS(_Male))),
_Female, FILTER(_Data,_Gender = "f"),
_SortedF, HSTACK(SORT(_Female,3,-1),SEQUENCE(ROWS(_Female))),
_Place, VLOOKUP(TAKE(_Data,,1),CHOOSECOLS(VSTACK(_SortedM,_SortedF),1,-1),2,0),
IF(_Place=4,"",_Place))
Старый метод:
=REDUCE("Place",UNIQUE(B15:B22),LAMBDA(a,b,
LET(c,FILTER(C15:C22,B15:B22=b),
d,MATCH(c,SORT(c,,-1),0),
VSTACK(a,IF(d<4,d,"")))))
@ScottCraner Сэр, тогда на самом деле это не будет работать, работает только в сложенном виде! также в этом отношении другой ответ не будет работать.
=MAP(B2:B9,C2:C9,LAMBDA(b,c,SUM(N(FILTER(C2:C9,B2:B9=b)>c))+1))
Или, если вы хотите исключить выше 3:
=MAP(B2:B9,C2:C9,LAMBDA(b,c,LET(s,SUM(N(FILTER(C2:C9,B2:B9=b)>c))+1,IF(s<4,s,""))))
@MayukhBhattacharya Нет, я вообще-то предполагал, что этого не должно быть, поскольку ОП отфильтровал это в своей собственной попытке, сортировка не имеет значения, но у каждого пола есть свой «рейтинг».
@MayukhBhattacharya, при моем тестировании этот метод работает, если данные сортируются только по столбцу имени, смешивая пол.
Не совсем, сэр, пожалуйста, попробуйте смешать один раз и посмотрите, обнаружите ли вы обманные рейтинги или нет. Не думайте, что ОП этого ищет.
мне кажется хорошо:imgur.com/a/ayE8G1w
Я получаю то же, что и Скотт. Не уверен, правильно ли я понимаю.
Ах, я понял, если есть два м с одинаковой суммой, то место дублируется, а одно место пропускается.
На самом деле это правда.
@ScottCraner =MAP(B2:B9,C2:C9,LAMBDA(b,c,LET(s,SUM(N(FILTER(C2:C9,B2:B9=b)>c))+1,IF(s<4,s,"")))) будет ранжироваться по тому же баллу/полу в том же ранге и пропускать следующий ранг вместо предыдущего. Я думаю, что это лучший способ справиться с дубликатами.
да, только что удалил свой точный дубликат комментария.
И отредактировал его в своем ответе
Первоначально это действительно работало для моих нужд. К сожалению, мои требования изменились. Меня попросили использовать Google Таблицы вместо Excel, что значительно усложняет задачу.
@Harlan Методология, которую я предоставил в своем ответе, также будет работать в Sheets, хотя вы можете отформатировать этот столбец как number перед вводом формулы.
Рейтинг: N первых сумм в каждой категории
N - top
Amounts - total
Category - gender
=LET(data,A2:C9,gender_col,2,total_col,3,top,3,
g,CHOOSECOLS(data,gender_col),
d,HSTACK(SEQUENCE(ROWS(g)),g,CHOOSECOLS(data,total_col)),
sr,DROP(REDUCE("",UNIQUE(g),LAMBDA(rr,r,LET(
s,TAKE(SORT(FILTER(d,g=r),3,-1),,1),
rs,ROWS(s),
ra,EXPAND(SEQUENCE(IF(rs<top,rs,top)),rs,,""),
VSTACK(rr,HSTACK(ra,s))))),1),
r,SORTBY(TAKE(sr,,1),DROP(sr,,1)),
r)
- Это эффективный способ использования для больших наборов данных (10 000, 100 000).
- Если строк категорий меньше, чем
top, ранжируются только они. - Это позволяет использовать более двух категорий (полов). Добавление большего количества категорий означает добавление большего количества стопок внутри
REDUCE, что снизит эффективность.
Если ваши данные находятся в таблице, вы можете ввести эту формулу в первую ячейку столбца Place, и она автоматически заполнит остальную часть столбца.
Предварительная сортировка не требуется, если вы не желаете этого по другим причинам.
=LET(
r, XMATCH(
C2,
SORT(FILTER($C$2:$C$9, B2 = $B$2:$B$9), 1, -1),
-1,
-1
),
IF(r <= 3, r, "")
Предполагается ли это, что списки m и f расположены друг над другом, а не перемешаны?