Я постоянно использую xlookup, так что меня это совершенно сбивает с толку. Использование простой формулы XLOOKUP для сравнения списка значений и определения результирующего ранга из списка рангов. Если я ТОЛЬКО сравниваю столбец оценок, ранги будут отображаться правильно. Однако мне нужно динамически запускать это по нескольким контрактам одновременно, поэтому мне определенно нужно использовать подход «[критерии1] и [критерии2]» (который я широко использовал и обычно не представляет проблем).
Вот минимальные баллы (столбец G) для достижения каждого ранга (столбец F).
Версия xlookup, которая работает должным образом, просматривая только одно значение и массив:
=xlookup([@score],ranks[score], ranks[rank],,-1)
Однако, когда я добавляю это поле контракта, Excel внезапно перестает узнавать разницу между цифрой 7, 67,84 и 71,75, возвращая ранг, присвоенный 67,84, что явно неверно.
=XLOOKUP([@Contract]&[@Score],Ranks[SERV_ABBR]&Ranks[SCORE],Ranks[RANK],,-1)
Это происходит каждый раз, когда баллы перемещаются с одного места на другое, а затем фиксируется и отлично работает, когда баллы находятся в диапазоне 10 и выше, корректно возвращая 1-й процентиль до тех пор, пока балл не достигнет 67,84, как и ожидалось. Так что же я делаю не так, что заставляю Excel игнорировать разницу между 7 и 70?
Я неоднократно перестраивал обе таблицы и переписывал формулы с нуля. Я проверил, что в полях Contract/Serv_Abbr в каждой таблице нет лишних пробелов (включая рабочие функции =(trim(clean()) как в полях значения, так и в полях массива. Я проверил, что столбцы оценок в обеих таблицах заполнены числами с двумя знаками после запятой.
Я ценю любую помощь, которую вы можете оказать. Обычно я использую НАМНОГО более сложные версии этих формул (вложенные операторы if, динамические сравнения и вычисления на возвращаемых данных и т. д.), поэтому такой простой вариант надрал мне задницу одновременно и унизительно, и ужасающе. Спасибо!
AS7 в алфавитном порядке больше, чем AS67.84, поэтому вы получите неправильный ответ.
Вы можете попробовать отформатировать числа следующим образом:
=XLOOKUP([@Contract]&TEXT([@Score],"00.00"),Ranks[Serv_Abb]&TEXT(Ranks[Score],"00.00"),Ranks[Rank],,-1)
заставить 7 представиться как 07.00
Возможно, я бы предпочел отфильтровать AS, а затем выполнить поиск по номеру, чтобы избежать проблемы:
=XLOOKUP([@Score],FILTER(Ranks[Score],Ranks[Serv_Abb]=[@Contract]),
FILTER(Ranks[Rank],Ranks[Serv_Abb]=[@Contract]),,-1)
search_mode2 - binary search (sorted ascending order).XLOOKUP только после фильтрации обоих столбцов по контракту.=LET(
f,Ranks[SERV_ABBR]=[@Contract],
s,FILTER(Ranks[SCORE],f),
r,FILTER(Ranks[RANK],f),
XLOOKUP([@Score],s,r,,-1,2))
Rank (Score) по возрастанию для каждого SERV_ABBR.