Упростите формулу с большим количеством операторов IF/AND/OR, возможно, обнаружив условное форматирование

Question

Я пытаюсь разработать формулу, которая анализирует конкретный материал (соответствующий значению Part Number ), который имеет несколько значений MS связанных с иерархией. Это происходит во многих связанных компаниях, использующих ERP, из-за неправильной документации и надлежащего ведения в основной записи материала. Правильное значение может быть определено исключительно из соответствующих MRPcn и X-Plant , которые всегда согласованы, поскольку основаны на уровне клиента в SAP.

Я условно отформатировал столбец MRPcn чтобы значения классифицировались по цвету согласно легенде. Условное форматирование было выполнено, поскольку существует более 500 уникальных значений MRPcn . Таким образом, я мог бы отфильтровать или написать формулу, используя цвета, если это необходимо.

Есть 20 уникальных ценностей X-Plant . 7 из них используются для значений MS . Эти P2 (производство), N2 (Инжиниринг), 18 (Устаревшая), и 15 (Глобальная OPS / обслуживания). Они также были условно отформатированы. Обратите внимание, что некоторые значения X-Plant не попадают под эти категории, но эти четыре являются наиболее релевантными и согласованными в наборе данных.

Я пытался использовать операторы IF/AND/OR и ранее проделал некоторую продвинутую работу с ними, но формула получится большой и громоздкой из-за большого количества различных уникальных значений, которые должны быть включены. Кроме того, у меня также возникают проблемы даже при создании частичной формулы с использованием небольшого числа значений.

Поскольку правильное значение MS самом деле зависит от категории значения MRPcn и категории значения X-Plant , я надеялся каким-то образом использовать цвет фона ячеек (которые соответствуют категории) для упрощения формулы.

Например, если значения MS детали - P2 и N2 , а MRPcn = C43 (планирование производства) и X-Plant = P2 , мы знаем, что правильным MS является P2 . (Причина, по которой материал может иметь значения MS P2 и N2 , которые являются полярными противоположностями, заключается в том, что деталь, возможно, была инициирована в SAP в качестве детали инженерного планирования во время NPI, но затем в дальнейшем перешла к производству на производственном предприятии и не поддерживается в ММ.)

Вот еще примерные данные, показывающие некоторые исправления для MS:

Глядя на строки 85:86 , часть 1301386 имеет два различных значения MS 18 и P2 которые обозначают «Прекращено / устарело» и «Производство», MRPcn который является инженерным, и X-Plant который прекращен, так что, вероятно, эта часть нуждается в своем MS быть 18 (устарел).

Определения акронимов:

• MM = основной материал
• MRPcn = Контроллер планирования ресурсов материалов
• MS = материальный статус
• NPI = Введение нового продукта.
• X-Plant MS - Статус материала на уровне клиента, идентифицирующий блокировку или статус материала в отношении планирования цепочки поставок

Answer 1

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

1. Напишите UDF для определения цвета условного формата ячейки, а затем создайте формулу с шестнадцатью вложенными выражениями IF(AND(),…) или чем-то эквивалентным.
   • Этот метод не имеет никаких реальных преимуществ
   • Недостатки в том, что использование условного форматирования является медленным, UDF не является простым и требует переоценки формул условного форматирования вручную, и что необходимая формула является длинной, со встроенными "правилами", что затрудняет их просмотр / изменение.
2. Составьте девять (некоторые очень) длинные строки имен и напишите формулу, которая требует только 4 вложенных IF(…)
   • Преимущество этого метода в том, что не требуется ни условного форматирования, ни каких-либо дополнительных ячеек
   • Недостатком является то, что "правила" скрыты в определенных именах и их очень трудно понять / изменить
3. Используйте три таблицы для определения отображений и используйте формулу, которая требует только трех вложенных функций VLOOKUP(…)
   • Плюсы в том, что он не требует условного форматирования, а правила компактны и их очень легко увидеть / изменить.
   • Единственным недостатком является то, что требуется три таблицы

Я покажу, как реализовать третий метод.

Это тестовая таблица, показывающая образец данных из предоставленных снимков экрана, а также три обязательные таблицы с некоторыми заполненными данными (некоторые из которых составлены):

Первая таблица содержит простое сопоставление значений MRPcn с соответствующими категориями.

Вторая таблица содержит сопоставления значений X-Plant с соответствующими категориями. Если значение, такое как один из гипотетических Z s, не принадлежит ни к одной из четырех категорий, значение категории должно быть установлено на что-то уникальное. (Я использовал само значение X-Plant .)

Третья таблица отображает "перекрестное произведение" двух категорий в предыдущих двух таблицах на соответствующие значения MS . Это где "правила" определены. Совокупным продуктом является просто конкатенация каждого из уникальных значений столбца Category таблицы 1 с каждым из уникальных значений столбца Category таблицы 1.

Обратите внимание, что перекрестные продукты не должны быть в каком-то определенном порядке. Также обратите внимание, что для каждой некатегоризируемой записи в таблице 2 необходимо создать четыре записи в таблице 3.


Наконец, как видно на скриншоте, поместите следующую формулу в G2:

=VLOOKUP(VLOOKUP(D2,$I:$J,2,FALSE)&VLOOKUP(F2,$L:$M,2,FALSE),$O:$P,2,FALSE)