Simplifique uma fórmula com várias instruções IF / AND / OR, possivelmente detectando formatação condicional

Question

Estou tentando desenvolver uma fórmula que analise um material específico (correspondente a um valor Part Number ) que tenha vários valores de MS vinculados a uma hierarquia. Isso acontece em muitas empresas relacionadas ao uso de ERP devido a documentação incorreta e manutenção adequada no mestre de materiais. O valor correto pode ser determinado apenas pelos valores MRPcn e X-Plant correspondentes, que são sempre consistentes, pois são baseados no nível do Cliente no SAP.

FormateicondicionalmenteacolunaMRPcnparaqueosvaloressejamcategorizadosporcordeacordocomalegenda.Aformataçãocondicionalfoifeitaporquehámaisde500%dosvaloresMRPcnexclusivos.Dessaforma,eupoderiafiltrarouescreverumafórmulausandoascores,senecessário.

Existem20valoresúnicosX-Plant.7delessãousadosparaosvaloresMS.EstessãoP2(Produção),N2(Engenharia),18/19/4/1(Obsoleto)e15(GlobalOps/Service).Estestambémforamcondicionalmenteformatados.ObservequealgunsdosvaloresdeX-Plantnãoseenquadramexatamentenessascategorias,masessesquatrosãoosmaisrelevanteseconsistentesemtodooconjuntodedados.

EutenhotentadoutilizarasexpressõesIF/AND/ORejátrabalheiumpoucocomisso,masafórmulaserágrandeedifícildevidoaograndenúmerodediferentesvaloresexclusivosqueprecisamserincorporados.Alémdisso,tambémestoutendoproblemasparacriarumafórmulaparcialusandoapenasumpequenonúmerodevalores.

ComoovalorcorretodeMSdependerealmentedacategoriadovalorMRPcnedacategoriadovalorX-Plant,esperavaaproveitardealgumaformaacordefundodascélulas(quecorrespondemàcategoria)parasimplificarafórmula.

Comoexemplo,seosvaloresdeMSdeumapeçaforemP2eN2eMRPcn=C43(Planejamentodaprodução)eX-Plant=P2,sabemosocorretoMSéP2.(ArazãopelaqualummaterialpodetervaloresMSdeP2eN2,quesãoopostospolares,éporqueapeçapodetersidoiniciadanoSAPcomopartedeplanejamentodeengenhariaduranteNPI,masmaistardefoimovidaparaproduçãoemumafábricaenãomantidanoMM.)

AquiestãomaisdadosdeamostramostrandoalgumascorreçõesparaMS:

Observando as linhas 85:86 , a parte 1301386 tem dois valores MS diferentes de 18 e P2 que denotam Descontinuado / Obsoleto e Produção, MRPcn que é Engenharia e X-Plant que é Descontinuado, então é provável que essa parte precise que seu MS seja 18 (Obsoleto).

Definições de acrônimos:

• MM = mestre de materiais
• MRPcn = controlador de planejamento de recursos de materiais
• MS = Status do material
• NPI = Nova Introdução ao Produto.
• X-Plant MS - Status do material no nível do cliente identificando um bloqueio ou status de um material em relação ao planejamento da cadeia de suprimento

Answer 1

Como já existe formatação condicional, posso pensar em pelo menos três formas diferentes para resolver este problema:

1. Escreva um UDF para detectar a cor do formato condicional de uma célula e crie uma fórmula com mais de dezesseis instruções IF(AND(),…) aninhadas ou algo equivalente.
   • Este método não tem vantagens reais
   • Os contras são que a formatação condicional é lenta, a UDF não é simples e requer a reavaliação manual das fórmulas de formatação condicional, e que a fórmula exigida é longa, com as "regras" embutidas, dificultando a visualização / modificar
2. Crie nove (muito) longas cadeias de nome definidas e escreva uma fórmula que requer apenas 4 IF(…) s aninhadas
   • A vantagem desse método é que nem a formatação condicional nem as células extras são necessárias
   • A desvantagem é que as "regras" estão ocultas em nomes definidos e são muito difíceis de entender / modificar
3. Use três tabelas para definir os mapeamentos e use uma fórmula que requer apenas três funções VLOOKUP(…) aninhadas
   • Os profissionais são que não exigem formatação condicional, e as regras são compactas e muito fáceis de ver / modificar
   • A única desvantagem é que são necessárias três tabelas

Vou mostrar como implementar o terceiro método.

Esta é uma planilha de teste que mostra uma amostra dos dados das capturas de tela fornecidas, além das três tabelas obrigatórias com alguns dos dados preenchidos (alguns dos quais são compostos):

AprimeiratabelacontémummapeamentosimplesdosvaloresdeMRPcnparaascategoriasapropriadas.

AsegundatabelacontémosmapeamentosdosvaloresdeX-Plantparasuascategoriasapropriadas.Seumvalor,comoumdosZshipotéticos,nãopertenceraumadasquatrocategorias,ovalordacategoriaprecisaráserdefinidocomoalgoúnico.(EuuseiovalorX-Plantemsi.)

Aterceiratabelamapeiao"produto cruzado" das duas categorias nas duas tabelas anteriores para os valores MS apropriados. É aqui que as "regras" são definidas. O produto cruzado é simplesmente a concatenação de cada um dos valores exclusivos da coluna Category da tabela 1 com cada um dos valores exclusivos da coluna Category da tabela 1.

Observe que os produtos cruzados não precisam estar em nenhuma ordem específica. Observe também que, para cada entrada não categorizada na tabela 2, quatro entradas precisam ser criadas na tabela 3.


Por fim, como pode ser visto na captura de tela, coloque a seguinte fórmula em G2 :

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