XLOOKUP находит ранги для оценок, игнорируя значения мест, возвращая неправильный ранг
Я постоянно использую 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, динамические сравнения и вычисления на возвращаемых данных и т. д.), поэтому такой простой вариант надрал мне задницу одновременно и унизительно, и ужасающе. Спасибо!
Ребята, вы все потрясающие. Ответ (это текст после конкатенации!) становится очевидным задним числом. Думаю, у меня буквально никогда не было примера, когда оценка была бы ниже 10 (или мы это делали, но никогда не замечали!), поэтому понятия не имел, что создаю такой очевидный крайний случай. Подход @tom-sharpe, заключающийся в простом переносе числа в правильное форматирование, кажется самым простым применением, но ЧЕРТ, я никогда не сталкивался с функцией LET() для использования в одной ячейке ИЛИ фильтрации данных внутри самой формулы. Я многому научился из этого одного вопроса... спасибо всем
Ответы 3
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)
XLOOKUP: точное совпадение или следующее меньшее
- Известно, что такой поиск выполняется медленно, поэтому я добавил аргумент
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.
Просто небольшая вариация Тома и VBasic2008:
=LET(
r_s, FILTER(Ranks[[RANK]:[SCORE]], [@Contract] = Ranks[SERV_ABBR]),
XLOOKUP([@Score], INDEX(r_s, , 2), INDEX(r_s, , 1), , -1)
)
Вы выполнили объединение текста, и XLOOKUP будет работать в текстовом режиме, где A67 меньше A7.