Используйте FILTER, чтобы выбрать точную категорию графических ресурсов, а затем XLOOKUP, чтобы вернуть уровень для наибольшего порогового значения количества, не превышающего указанное вами число. В этом пошаговом примере создаётся полноценный файл Excel для Mac с условными названиями и проверками граничных значений.

Какие версии Excel для Mac поддерживают эту формулу?

В формуле ниже используются LET, FILTER и XLOOKUP. В документации Microsoft указано, что все три функции доступны в Excel для Microsoft 365 для Mac, Excel 2024 для Mac и Excel 2021 для Mac. Microsoft также упоминает эти функции в обзоре новых возможностей Excel 2021 для Mac.

Excel 2016 и Excel 2019 не могут вычислять XLOOKUP; Microsoft прямо указывает на это ограничение. Для этой формулы используйте одну из перечисленных выше версий для Mac. Открытие книги с этой функцией в более старой версии не добавит поддержку функции. См. документацию Microsoft по XLOOKUP.

Если вы не уверены, какая версия установлена, проверьте версию и тип лицензии в диалоговом окне «О программе». Microsoft объясняет, как определить эти сведения, в статье «О Office: какая версия Office у меня используется?».

Настройка минимального количества для каждой категории

Предположим, вы ведёте учёт повторно используемых графических ресурсов и хотите присваивать каждой категории описательный уровень запасов. Здесь действуют два правила: категория должна совпадать, а количество должно достигать минимума для соответствующего уровня. Все названия и количества ниже приведены для примера.

На пустом листе введите эту таблицу в диапазон A1:C7. Заголовки располагаются в строке 1, а шесть строк данных — в строках 2–7. Введите минимальные количества как числа и заполните все ячейки с категориями и уровнями.

Категория Минимальное количество Название уровня
Текстурный тайл 5 Компактный
Текстурный тайл 20 Расширенный
Текстурный тайл 50 Обширный
Штамп-фигура 5 Компактный
Штамп-фигура 30 Расширенный
Штамп-фигура 80 Обширный

Затем введите Категория для проверки в E1, Количество в F1 и Уровень запасов в G1. В E2 укажите Текстурный тайл, а в F2 — число 34. Оставьте столбец D пустым, чтобы отделить справочные данные от входных значений.

Считайте минимальные количества включительными нижними границами. Для текстурного тайла уровень «Компактный» начинается с 5, «Расширенный» — с 20, а «Обширный» — с 50. Поэтому при количестве 34 результатом будет «Расширенный». Для штампа-фигуры те же уровни начинаются с других значений: количество 20 всё ещё соответствует уровню «Компактный».

Перед вводом формулы решите, задают ли ваши границы минимальные или максимальные значения. В этом примере везде используются минимумы. Значения меньше 5 не относятся ни к одному уровню, а последний уровень продолжается выше своего начального значения. Это явные допущения примера; измените справочные данные, если ваши правила отличаются.

Ввод полной формулы для категории и количества

Выберите G2, вставьте следующую полную формулу в строку формул и нажмите Return. Она рассчитана на указанную выше структуру листа; именованные диапазоны и вспомогательные столбцы не нужны.

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

Для категории Текстурный тайл и значения 34 ожидаемый результат — Расширенный. Формула возвращает одну метку, хотя в промежуточных вычислениях используются массивы. В современных формулах динамических массивов используется обычный ввод; Microsoft указывает, что для подтверждения достаточно нажать Enter, в статье «Формулы динамических массивов и поведение разливающихся массивов».

Как фильтр категории сохраняет соответствие между массивами

Выражение categories=category создаёт массив включения с именем keep. Для этого примера оно выбирает три строки текстурного тайла. Оба вызова FILTER используют один и тот же массив и возвращают минимумы 5, 20 и 50 вместе с соответствующими метками «Компактный», «Расширенный» и «Обширный».

Microsoft описывает FILTER как функцию, выбирающую данные с помощью логического массива включения, размерности которого совпадают с исходным диапазоном. Использование одного и того же массива включения для обоих столбцов сохраняет соответствие между ними. См. описание функции FILTER.

Внешняя функция LET присваивает понятные имена исходным диапазонам, входным значениям и массиву включения. Эти имена существуют только внутри формулы; создавать их отдельно в книге не нужно. Это поведение описано в документации Microsoft по LET.

Как количество определяет уровень

Пятый аргумент XLOOKUP, -1, запрашивает точное совпадение количества или ближайшее меньшее значение. Среди отфильтрованных строк текстурного тайла для значения 34 выбирается минимум 20 и возвращается уровень «Расширенный». При точном значении 50 выбирается «Обширный». Microsoft описывает эти режимы в документации по функции XLOOKUP.

Формула также различает неизвестную категорию и количество ниже первого уровня. Функция SUM(--keep) подсчитывает строки с совпадающей категорией. Если таких строк нет, формула возвращает Категория не найдена, не пытаясь выполнить поиск по отфильтрованным данным. Если категория существует, но все её минимумы превышают указанное количество, поиск возвращает Ниже первого уровня.

Проверка границ и изоляции категорий

Заменяйте E2 и F2 следующими парами, по одной за раз. Эти проверки полезнее, чем проверка только среднего значения: они охватывают первый порог, точные границы, различия категорий и отсутствующие категории.

Категория в E2 Количество в F2 Ожидаемый результат
Текстурный тайл 0 Ниже первого уровня
Текстурный тайл 5 Компактный
Текстурный тайл 19 Компактный
Текстурный тайл 20 Расширенный
Текстурный тайл 34 Расширенный
Текстурный тайл 50 Обширный
Штамп-фигура 20 Компактный
Штамп-фигура 34 Расширенный
Штамп-фигура 100 Обширный
Цветовой образец 34 Категория не найдена

Особенно показательны два теста со значением 20. Для текстурного тайла должен возвращаться уровень «Расширенный», а для штампа-фигуры — «Компактный». Если в обоих случаях возвращается «Расширенный», проверьте, использует ли поиск количества строки отфильтрованной категории.

Эти десять ожидаемых результатов проверены с помощью небольшого расчёта на Python, реализующего правило минимального порога. Формула Excel была сверена с синтаксисом в документации Microsoft, но в Excel не запускалась.

Также проверьте сообщения для входных данных: пустая категория или количество, введённое как текст, должны возвращать Введите категорию и числовое количество; отрицательное числовое количество должно возвращать Используйте ноль или положительное число. Для подсчёта ресурсов вводите целые числа. Формула проверяет, что количество является числовым и неотрицательным, но не проверяет, что оно целое.

Разбор неожиданных результатов и расширение листа

Выполняйте эти проверки по порядку, чтобы проблема с форматированием не привела к изменению правил для уровней.

  1. Проверьте текст категории. Сравните входное значение со справочными метками, включая пробелы. В руководстве по устранению проблем с поиском Microsoft называет неожиданно добавленные символы и скрытые пробелы распространёнными причинами ошибок.
  2. Проверьте числовые ячейки. Входные количества и минимумы должны быть числами, а не текстом, который выглядит как число. В том же руководстве Microsoft указывает несовпадение типов данных как одну из проблем поиска.
  3. Проверьте соответствие диапазонов. Диапазоны категорий, минимумов и меток должны охватывать одни и те же строки данных. В этом примере каждый диапазон начинается со строки 2 и заканчивается строкой 7.
  4. Проверьте повторяющиеся правила. Для каждой пары категории и минимального количества должна быть задана одна метка уровня. Если двум строкам соответствуют разные метки для одной границы, устраните эту неоднозначность в справочных данных.
  5. Проверьте трактовку границ. Результат на один уровень выше или ниже ожидаемого может указывать на то, что в справочных данных заданы максимумы, хотя формула рассчитана на минимумы.

Для визуальной проверки введите следующую диагностическую формулу в свободную ячейку, например I2. Если в E2 указано Текстурный тайл, формула должна отобразить три полные строки с этой категорией. Это отдельная диагностическая формула, а не замена G2.

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

Оставьте достаточно места, чтобы результат этой диагностики разлился на три столбца и три строки. Результат отдельного массива разливается в соседние ячейки, а занятые ячейки могут вызвать ошибку #SPILL!. Microsoft объясняет это поведение в статье «Формулы динамических массивов и поведение разливающихся массивов».

Добавляя строки справочной таблицы, расширяйте все три исходных диапазона в основной формуле одновременно. Например, если последней строкой данных станет строка 10, измените определения на categories,$A$2:$A$10, minimums,$B$2:$B$10 и labels,$C$2:$C$10. Затем повторите граничные тесты для добавленной категории.

Чтобы обработать несколько строк ввода, добавьте другие пары категории и количества ниже E2:F2 и скопируйте G2 вниз. Исходные диапазоны останутся фиксированными благодаря знакам доллара в ссылках, а номера строк входных данных будут меняться. Явно задавайте границы для каждой категории и используйте парные тесты категорий, чтобы убедиться, что каждая возвращённая метка соответствует заданному правилу учёта запасов.

Источники