Оценка значений, включая NA() - Excel 365

Оценка значений, включая NA() – Excel 365

На чтение 1 мин Опубликовано 02.10.2024

Содержание

Вопрос или проблема
Ответ или решение
Объяснение:
Данный подход:

Вопрос или проблема

Допустим, у меня есть следующий массив чисел:

$222.71 
$370.00 
$224.93 
#N/A
$230.00 
$175.00 
$160.00 
$248.00 
$150.00

Я хотел бы оценить числа выше, используя некоторые произвольные критерии. Например, если значение находится в пределах 15% от минимума, оно получает оценку 3; если оно в пределах 50% от минимума, оно получает оценку 2; если оно выше 50% от минимума или NA(), оно получает оценку 1.

Я хотел бы, чтобы формула была гибкой и простой для поддержания, так как может быть много других условий, основанных на относительных значениях.

=LET(
    values, A1:A9,
    minValue, MIN(FILTER(values, ISNUMBER(values))),
    rank, IF(ISNUMBER(values), IF(values <= minValue * 1.15, 3, IF(values <= minValue * 1.5, 2, 1)), 1),
    rank)

Вышеописанное работает, но это сложно читать и поддерживать.

Использование SWITCH делает его более читаемым и проще в обслуживании, но оно выглядит слишком длинным:

=LET(
    values, A1:A9,
    minValue, MIN(FILTER(values, ISNUMBER(values))),
    rank, IFERROR(
        SWITCH(TRUE(),
        values <= minValue * 1.15, 3,
        values <= minValue * 1.5, 2,
        1),
    1),
    rank
)

Существуют ли более лучшие, более элегантные способы сделать это?

Ответ или решение

Конечно! Давайте улучшим вашу формулу, сделав её более читаемой и удобной для поддержки. Мы можем использовать функцию LET для определения переменных, а также FILTER для обработки значений, включая NA(). Кроме того, вместо использования SWITCH можно интегрировать условие в CHOOSE, чтобы минимизировать запутанность формулы.

Вот более элегантный подход:

=LET(
    values, A1:A9,
    minValue, MIN(FILTER(values, ISNUMBER(values))),
    scores, IF(ISNUMBER(values), 
                IF(values <= minValue * 1.15, 3, 
                   IF(values <= minValue * 1.5, 2, 1)), 
                NA()),
    scores
)

Объяснение:

LET: Мы используем эту функцию для определения переменных, что делает формулу более чистой и облегчает понимание.
values: Это диапазон ваших данных.
minValue: Мы находим минимальное значение среди чисел в массиве, исключая NA().
scores: Этот массив будет хранить оценки. Здесь мы используем IF для проверки, является ли каждое значение числом:
- Если значение меньше или равно 15% от минимального значения, оно получает оценку 3.
- Если значение меньше или равно 50% от минимального значения, оно получает оценку 2.
- В противном случае оно получает оценку 1.
- Если значение не является числом (например, NA()), мы возвращаем NA().

Данный подход:

Упрощает добавление новых условий, так как вы можете легко расширить условный оператор IF.
Сохраняет читаемость формулы и делает её более понятной для других пользователей, работающих с вашим файлом Excel.

Теперь формула остаётся компактной, а изменения условий можно вносить без особых трудностей. Надеюсь, это поможет вам эффективно работать с вашими данными в Excel!