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

Question

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

260

Mic Fitzgerald 2018-06-05 в 17:40

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

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

Есть 20 уникальных X-Plantценностей. 7 из них используются для MSзначений. Это P2(Производство), N2(Инжиниринг), 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, но затем позже перешла к производству на производственном предприятии и не поддерживалась в ММ.)

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

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

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

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

1

Ах понятно. Спасибо за объяснение вашего замешательства. Эта проблема с отсутствующими пустыми строками состоит в том, что, если бы они были, было бы идеально разделить другой материал. Так как это не так, при создании формулы и флэш-заполнении она будет учитывать разные материалы ms и, следовательно, будет выводить неправильный код. То есть, если операторы для ячеек G2 и G3 анализируют один материал, то выходные данные для G3, если ложь, будут ссылаться на ссылки на ячейки в G4 (ссылающиеся на другой материал). Это происходило в моих итерациях формулы, которая, как я знаю, неверна. Mic Fitzgerald 5 лет назад 0

Итак, вы можете видеть на картинке, которую я добавил, есть несколько разных случаев, которые могут произойти. В строке 85-86 1301386 имеет MS 18 / P2, который обозначает прекращенный или устаревший и производственный, MRPcn, который является инженерным, и X-Plant, который прекращается, так что, вероятно, эта часть должна быть отмечена как 18 или в другие слова устарели. Эта формула работала, чтобы смягчить многие из них, но по большей части, когда какой-то материал демонстрировал 3 или 4 разных MS, это испортило бы следующий материал. Понимаю? Mic Fitzgerald 5 лет назад 0

Правильно, каждый номер детали должен ссылаться на один статус материала, а не на несколько. Существуют четыре разных варианта состояния материала деталей: например, P2 для производства, N2 для проектирования, 18/19/4/1 для устаревшего и 15 для глобального обслуживания / обслуживания. Они могут быть изображены для части, основанной на логике для двух разных точек данных: MRPcn и X-Plant MS. @robinCTS Mic Fitzgerald 5 лет назад 0

Правильно, он всегда может быть рассчитан на основе логики, изложенной выше. Там будет несколько случаев, когда правильная формула будет выводить неправильный MS на основе ссылочных данных. Но это будут небольшие исключения. Существует более 500 MRPcn и только 20 x-plant ms, однако все они относятся только к тем четырем категориям, которые упомянуты выше, и это является причиной того, почему я покрыл их цветом. Поэтому вопрос заключается в том, можно ли сопоставить материал с указанными цветами. Потому что я знаю, что делать 500 различных итераций операторов if абсурдно lol @robinCTS. Mic Fitzgerald 5 лет назад 0

@robinCTS Я могу запускать макросы, но в настоящее время их нет, и это нужно выполнять только один раз в течение, скажем, 5-10 лет. Я могу установить UDF или любую надстройку, которая необходима, но честное предупреждение, вы говорите из моей лиги сейчас. Mic Fitzgerald 5 лет назад 0

Если я могу автоматически заполнить весь список MRPcn и X-Plants и назначить им числовое значение, которое устанавливает разные диапазоны, соответствующие четырем типам MS, как вы думаете, может ли работать CountIf или другая подобная функция для того, что я пытаюсь сделать ? @robinCTS Черт возьми, я так долго! Mic Fitzgerald 5 лет назад 1

@robinCTS правильно, на изображениях это может выглядеть не так, как будто представлены все сценарии Х-растений, но, конечно, из-за того, что имеется более 30 000 строк, есть, конечно, особенные, которые не относятся к типичным четырем категориям, которые у меня есть. классифицировано выше. Таким образом, в этих сценариях ответ тогда лежит в mrpcn. Но при необходимости не нужно их идентифицировать, потому что если хуже становится хуже, есть только несколько таких, которые я могу сделать вручную после фильтрации. Взгляды, которые я понимаю лучше, чем любая формула, как вы предлагаете мне построить вложенный оператор, который будет выводить правильное значение. Mic Fitzgerald 5 лет назад 0

1 ответ на вопрос

0

Accepted Answer · 2018-06-06 10:10:35

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

Напишите UDF для определения цвета условного формата ячейки, а затем создайте формулу с шестнадцатью вложенными IF(AND(),…)операторами или чем-то эквивалентным.
- Этот метод не имеет никаких реальных преимуществ
- Недостатки в том, что использование условного форматирования является медленным, UDF не является простым и требует переоценки формул условного форматирования вручную, и что необходимая формула является длинной, со встроенными «правилами», что затрудняет их просмотр / изменение.
Craft девять (некоторые очень) длинные строк, определенные имени и написать формулу, которая требует только 4 вложенных IF(…)сек
- Преимущество этого метода в том, что не требуется ни условного форматирования, ни каких-либо дополнительных ячеек
- Недостатком является то, что «правила» скрыты в определенных именах и их очень трудно понять / изменить
Используйте три таблицы для определения отображений и используйте формулу, которая требует только трех вложенных 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)

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

1 ответ на вопрос

Похожие вопросы