Чтобы поиск в инвентаре Excel выбирал наиболее конкретное подходящее правило, сначала определите подходящие строки, а затем подсчитайте, сколько полей в них задано. Верните количество из единственной строки с наивысшей оценкой и отметьте случаи равенства. В этом руководстве показано, как получить такой результат для искусственных художественных материалов в Excel для Mac.

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

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

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

Что означает «самое конкретное» в вашем инвентаре?

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

Это явная политика книги, выбранная для данного примера. Excel не выводит такую политику из ваших описаний. Здесь все атрибуты имеют одинаковый вес: правило, задающее материал и отделку, имеет ту же степень конкретности, что и правило, задающее материал и размер. Если оба правила подходят и имеют наивысшую оценку, формула сообщает о равенстве.

Обычная функция XLOOKUP сама по себе не может определить такой приоритет. По документации Microsoft, по умолчанию она возвращает первый найденный элемент. Обратный порядок поиска меняет, с какого конца начинается поиск, но не ранжирует правила по степени конкретности. Поэтому формула сначала вычисляет выигрышную строку, а затем запрашивает ее количество с помощью XLOOKUP. Функция XLOOKUP.

Настройте правила для искусственных художественных материалов

На пустом листе введите эти заголовки и значения в диапазон A1:D6. Ячейки, оставленные пустыми в таблице ниже, должны оставаться пустыми и в Excel. Все метки и количества приведены только для примера.

Строка листа A: Материал B: Отделка C: Размер D: Количество
2 30
3 Плитка 18
4 Плитка Матовая 12
5 Плитка Матовая Маленький 7
6 Диск Большой 10

Правило в строке 2 — намеренное значение по умолчанию: оно не задает атрибутов, поэтому его степень конкретности равна нулю. В строке 3 задан один атрибут, в строке 4 — два, а в строке 5 — три. Эти количества являются альтернативными назначенными значениями, поэтому поиск возвращает одно значение. Он не суммирует значения по умолчанию и по исключениям.

Введите заголовки Материал, Отделка, Размер и Результат в H1:K1. Затем введите Плитка в H2, Матовая в I2 и Маленький в J2. Заполните все три входных атрибута. В этой схеме пустые значения служат условиями без ограничений только в таблице правил; при неполных входных данных выводится сообщение.

Используйте числовые значения количества, включая числовой ноль, если он уместен. Ограничьте диапазоны только строками с правилами. Если включить в них пустые строки ниже таблицы, при этой политике сопоставления появятся дополнительные правила без заданных атрибутов.

Введите формулу для выбора выигрышного правила

Выберите K2, введите полную формулу ниже и нажмите Return. Она использует таблицу правил и входные ячейки, заданные выше. Вычисления с динамическими массивами выполняются внутри формулы, итоговый результат занимает одну ячейку. Согласно документации Microsoft, формулы динамических массивов вводятся обычным способом, без устаревших сочетаний клавиш для ввода формул массива. Формулы динамических массивов и поведение разливающихся массивов.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

При указанных примерах входных данных K2 должна возвращать 7. Подходят четыре правила, но только в строке 5 заданы все три атрибута.

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

LET присваивает локальные имена диапазонам и промежуточным результатам, благодаря чему процесс выбора проще проверить. По документации Microsoft, эти имена действуют только в пределах формулы. Функция LET.

Для каждого атрибута выражение соответствия проверяет, пуста ли ячейка правила или совпадает ли она с входным значением. Сложение объединяет эти два варианта, а сравнение преобразует результат в логическую проверку. Умножение трех проверок атрибутов требует выполнения всех условий. В примерах для FILTER Microsoft использует такой же прием умножения для объединения критериев массива с логикой AND. Функция FILTER.

specificity подсчитывает заполненные поля правила. Унарный оператор, примененный дважды, преобразует каждую проверку на непустое значение в число; получаются оценки от нуля до трех. IF присваивает неподходящим строкам оценку минус один для ранжирования, а MAX затем находит наивысшую оценку среди подходящих. Минус один гарантирует, что неподходящая строка окажется ниже даже правила без ограничений с оценкой ноль. Функция IF, Функция MAX.

winner помечает только подходящие строки с наивысшей оценкой. Перед возвратом количества формула проверяет, не отсутствуют ли совпадения и нет ли нескольких победителей. Последняя числовая проверка намеренно отклоняет пустое количество и число, сохраненное как текст. По документации Microsoft, ISNUMBER не преобразует текстовые числа в числовые значения. Числовой ноль остается допустимым результатом. Функции IS.

Проверьте результаты, прежде чем расширять лист

Меняйте значения H2:J2, вводя каждую из приведенных ниже комбинаций. Это ожидаемые результаты, выведенные из примеров правил, а не данные о фактических запасах.

Материал Отделка Размер Ожидаемый результат Условие победы
Плитка Матовая Маленький 7 Заданы все три поля
Плитка Матовая Большой 12 Материал и отделка
Плитка Глянцевая Маленький 18 Только материал
Диск Глянцевая Большой 10 Материал и размер
Дуга Матовая Маленький 30 Правило без ограничений по умолчанию

Затем временно замените значения в строке 6 на материал Плитка, отделку Матовая, размер Маленький и количество 9. После восстановления исходных входных данных результат должен измениться на Равенство: проверьте правила: теперь два правила задают все три совпадающих атрибута. Даже если значения количества совпадают, результатом все равно будет равенство, поскольку выбор правила неоднозначен. После этого восстановите исходную строку 6.

Чтобы проверить ветвь отсутствия совпадений, временно измените четыре диапазона правил в формуле так, чтобы они начинались со строки 3, исключив правило по умолчанию. Для входных значений Дуга, Матовая, Маленький должно появиться сообщение Подходящее правило не найдено. По завершении верните начало диапазонов на строку 2. Чтобы проверить количество, очистите D5, сохранив исходные входные данные. Ожидаемый результат — Проверьте количество в правиле; затем восстановите 7.

Просмотрите пересекающиеся правила и разрешите равенство

Чтобы увидеть все правила, подходящие для текущих входных данных, введите отдельную формулу ниже в M2. Заранее очистите диапазон M2:P6. Результат содержит подходящие строки в исходном порядке; ранжирования он не выполняет.

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER возвращает строки, отобранные логическим массивом включения, а последний аргумент задает сообщение на случай отсутствия подходящих строк. Пустые исходные ячейки в результатах этой проверки могут отображаться как нули; продолжайте считать пустые ячейки в исходной таблице правил условиями без ограничений. Функция FILTER.

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

Разрешите равенство, исправив дубликат, сузив область пересечения правил или задав дополнительную политику приоритетов. Например, правила, задающие материал и отделку, а также материал и размер, здесь получают оценку два. Книга не может определить, какой атрибут важнее, пока вы не зададите это предпочтение явно. При использовании описанной в этой статье политики перемещение строк не должно влиять на результат.

Расширьте поиск, не меняя его смысла

Чтобы добавить другой материал, введите его три атрибута в H3:J3 и скопируйте K2 в K3. Ссылки на входные данные смещаются вместе с формулой, а знаки доллара фиксируют диапазоны правил. Добавляя правила после строки 6, последовательно расширьте все четыре диапазона в основной формуле и оба измерения формулы просмотра.

Храните каждый атрибут в отдельном столбце и используйте согласованные метки без случайных пробелов. Эта формула сравнивает целые значения полей; она не ищет фрагменты внутри объединенного описания материала. Проверка количества подтверждает, что выбранное значение является числовым. Дополнительные требования к инвентарю, например неотрицательное целое количество, необходимо применять к исходным данным.

В результате поиск выполняется по понятной последовательности: требуется полный набор входных данных, определяются подходящие правила, находится наибольшая степень конкретности, равенство отклоняется, возвращается выбранное числовое количество. Благодаря этому общие правила по умолчанию и подробные исключения можно использовать вместе, а неразрешенные случаи остаются заметны.

Источники