Установить максимум строки для формулы ФИЛЬТР
У меня есть набор данных, в котором я буду использовать формулу ФИЛЬТР для извлечения конкретных значений относительно отдельного человека. Для каждого человека я хочу получить только последние 2 балла. Данные расположены в убывающем хронологическом порядке, поэтому мне просто нужны первые 2 существующие записи.
Используя набор данных, аналогичный приложенному, если бы меня не интересовали только последние 2 балла, я бы использовал что-то вроде
=FILTER(A:C,B:B=B1)B1 для A, B2 для B и т. д.
Но это дало бы мне 6 строк для A, 3 для B и 2 для C. Чтобы стандартизировать это, я хочу учитывать только максимум 2 результатов на человека. Как изменить формулу ФИЛЬТР, чтобы добиться этого?
Ответы 3
Не используйте целые ссылки на столбцы: параметр включения FILTER обрабатывает все переданные ему ячейки, поэтому вы фактически просите его выполнить более миллиона вычислений, даже если у вас есть данные всего на несколько сотен строк.
Вы можете использовать TAKE, если в вашей версии Excel есть такая функция:
=TAKE(FILTER(A1:C12,B1:B12=B2),2)
или еще INDEX с SEQUENCE:
=INDEX(FILTER(A1:C12,B1:B12=B2),SEQUENCE(2),SEQUENCE(,3))
Если вы не знаете, как создать динамическую ссылку на последнюю использованную строку, определите LRow в диспетчере имен следующим образом:
=MATCH("Ω",$B:$B)
после чего первое из вышеперечисленного становится:
=TAKE(FILTER(A1:INDEX(C:C,LRow),B1:INDEX(B:B,LRow)=B2),2)
Работает только с текстом, но делает его еще более удобным для понимания.
Используя трюк, который я узнал от JvdV здесь https://stackoverflow.com/a/73853318/12634230 с использованием REDUCE для суммирования результатов расчета, даже если они являются разливами:
=LET(data,A2:C12,
names,INDEX(data,,2),
DROP(
REDUCE(0,UNIQUE(names),LAMBDA(a,b,
VSTACK(a,TAKE(FILTER(data,names=b),2)))),
1))
умное решение, изначально я думал о чем-то подобном, чтобы создать динамику VSTACK, но я не смог этого сделать. Единственное "ограничение" (но оно было заявлено как допущение) состоит в том, что входной набор данных нужно уже сортировать по годам, но его можно распространить на несортированные данные с учетом года для сортировки на лету. Хороший трюк, чтобы принять во внимание любые другие подобные ситуации
Вот еще один подход, который сохраняет исходный порядок входных данных, поскольку он использует FILTER и не требует сортировки входных данных. Он использует идею ранжирования года в группе элементов под тем же названием:
=LET(set, A2:C12, years, INDEX(set,,1), names, INDEX(set,,2),
rank, MAP(names, years, LAMBDA(n,y, SUMPRODUCT((names=n) * (years > y))+1)),
FILTER(set, rank <= 2)
)
Этот расчет делает волшебство:
SUMPRODUCT((names=n) * (years > y))+1
он занимает years по группе имен. Вот промежуточный результат rank:
он ранжируется в порядке убывания каждый год для одного и того же имени. Самый высокий год будет 1, второй по величине будет 2 и так далее. Поскольку мы хотим подняться на второе место в каждой группе, нам нужно отфильтровать на rank меньше или равно 2.
Тоже хороший подход.
Хороший трюк
"Ω"