Упростите формулу с большим количеством операторов IF/AND/OR, возможно, обнаружив условное форматирование
Я пытаюсь разработать формулу, которая анализирует конкретный материал (соответствующий Part Number значение), который имеет несколько значений MS привязан к иерархии. Это происходит во многих связанных компаниях, использующих ERP, из-за неправильной документации и надлежащего ведения в основной записи материала. Правильное значение может быть определено исключительно из соответствующего MRPcn а также X-Plant значения, которые всегда согласованы, так как основаны на уровне клиента в SAP.
Я условно отформатировал MRPcn столбец, поэтому значения классифицируются по цвету в соответствии с легендой. Условное форматирование выполнено, поскольку существует более 500 уникальных MRPcn ценности. Таким образом, я мог бы отфильтровать или написать формулу, используя цвета, если это необходимо.
Есть 20 уникальных X-Plant ценности. 7 из них используются для MS ценности. Это P2 (Производство), N2 (Engineering), 18 / 19 / 4 / 1 (Устарел) и 15 (Глобал Опс / Сервис). Они также были условно отформатированы. Обратите внимание, что некоторые из X-Plant значения не подпадают только под эти категории, но эти четыре являются наиболее релевантными и согласованными в наборе данных.
Я пытался использовать IF / AND / OR заявления и ранее проделали некоторую продвинутую работу с ними, но формула в конечном итоге будет большой и громоздкой из-за большого количества различных уникальных значений, которые должны быть включены. Кроме того, у меня также возникают проблемы даже при создании частичной формулы, использующей лишь небольшое количество значений.
Так как правильное значение MS на самом деле зависит от категории MRPcn значение и категория X-Plant Значение, я надеялся каким-то образом использовать цвет фона ячеек (которые соответствуют категории), чтобы упростить формулу.
Например, если MS значения части P2 а также N2, а также MRPcn знак равно C43 (Планирование производства) и X-Plant знак равно P2 мы знаем правильный MS является P2, (Причина, по которой материал может иметь MS значения P2 а также N2 Полярные противоположности объясняются тем, что деталь, возможно, была инициирована в SAP как деталь инженерного планирования во время NPI, но затем впоследствии перешла к производству на заводе-изготовителе и не была сохранена в MM.)
Вот больше примеров данных, показывающих некоторые исправления MS:
Смотря рядки 85:86, часть 1301386 имеет два разных MS значения 18 а также P2 которые обозначают прекращено / устарели и производство, MRPcn это инженерия, и X-Plant Это прекращено, так что, вероятно, эта часть нуждается в MS быть 18 (Устаревшая).
Определения акронимов:
- MM = основной материал
- MRPcn = Контроллер планирования ресурсов материалов
- MS = материальный статус
- NPI = Введение нового продукта.
- X-Plant MS - Статус материала на уровне клиента, идентифицирующий блокировку или статус материала в отношении планирования цепочки поставок
1 ответ
Поскольку уже существует условное форматирование, я могу придумать как минимум три различных способа решения этой проблемы:
- Напишите UDF для определения цвета условного формата ячейки, а затем создайте формулу с шестнадцатью вложенными вложениями.
IF(AND(),…)заявления или что-то эквивалентное.- Этот метод не имеет реальных преимуществ
- Недостатки в том, что использование условного форматирования является медленным, UDF не является простым и требует переоценки формул условного форматирования вручную, и что необходимая формула является длинной, со встроенными "правилами", что затрудняет их просмотр / изменение.
- Создайте девять (некоторые очень) длинные строки имен и напишите формулу, которая требует только 4 вложенных
IF(…)s- Преимущество этого метода в том, что не требуется ни условного форматирования, ни каких-либо дополнительных ячеек
- Недостатком является то, что "правила" скрыты в определенных именах и их очень трудно понять / изменить
- Используйте три таблицы для определения отображений и используйте формулу, которая требует только три вложенных
VLOOKUP(…)функции- Плюсы в том, что он не требует условного форматирования, а правила компактны и их очень легко увидеть / изменить.
- Единственным недостатком является то, что требуется три таблицы
Я покажу, как реализовать третий метод.
Это тестовая таблица, показывающая образец данных из предоставленных снимков экрана, а также три обязательные таблицы с некоторыми заполненными данными (некоторые из которых составлены):
Первая таблица содержит простое отображение из MRPcn значения в соответствующие категории.
Вторая таблица содержит сопоставления из X-Plant значения в соответствующих категориях. Если значение, например, одно из гипотетических Zs, не принадлежит ни к одной из четырех категорий, значение категории должно быть установлено на что-то уникальное. (Я использовал 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)