Corresponde um número de item ao número de item abreviado apropriado no Excel

Question

Eu tenho uma lista de números de item abreviados com dados de taxa de comissão correspondentes. Quando eu faço o download de dados de vendas, ele contém números de itens completos. Eu quero corresponder o número do item completo para a abreviação correta (outra guia na pasta de trabalho). A abreviação correta corresponde ao início do número do item correspondente - as abreviações variam em tamanho entre 3 e 5 caracteres.

Como posso recuperar a abreviação apropriada, considerando o número completo do item?

    

Answer 1

Aqui está uma abordagem que aborda os comprimentos variados dos números de itens e códigos abreviados, bem como a possibilidade de que códigos abreviados de comprimentos diferentes compartilhem alguns números iniciais ou letras.

Ele usa três colunas auxiliares que correspondem exatamente a VLOOKUPs no código abreviado dos primeiros 3, 4 e 5 caracteres, respectivamente, de cada item. Uma correspondência pode ser encontrada em qualquer, todos ou (no caso de dados inválidos) nenhuma dessas pesquisas para um código abreviado.

A quarta coluna de cálculo contém as fórmulas que selecionam entre os resultados dos três VLOOKUPs .

As regras de seleção são simples. Se uma única correspondência for encontrada na lista de códigos abreviados, o código correspondente será retornado. Se duas ou três correspondências forem encontradas, a correspondência com o maior número de caracteres será retornada. Se nenhuma correspondência for encontrada para um item, as fórmulas retornarão o valor de erro # N / A.

As expressões de seleção na quarta coluna dos cálculos são fórmulas de matriz e devem ser inseridas com a combinação de teclas Controle - Shift - Enter (isto é, a fórmula na primeira linha deve ser inserida e depois copiada para a parte inferior da lista de números de itens).

Para as fórmulas de exemplo abaixo, uma lista de números de itens completos está nas células A2:A10 da Planilha 1. Uma lista de códigos abreviados está nas células A2:A10 da Planilha 2. As fórmulas auxiliares estão nas colunas B-D da Sheet1 e as fórmulas de array de seleção estão na coluna E dessa planilha. (A fórmula de matriz de exemplo deve estar totalmente visível movendo a barra de rolagem para a direita.)

As fórmulas

Cell B2:   =VLOOKUP(LEFT($A2,3),Sheet2!$A$2:$A$10,1,0)
Cell C2:   =VLOOKUP(LEFT($A2,4),Sheet2!$A$2:$A$10,1,0)
Cell D2:   =VLOOKUP(LEFT($A2,5),Sheet2!$A$2:$A$10,1,0)

Cell E2:   =IFERROR(INDEX($B2:$D2,1,MAX(NOT(ISERROR($B2:$D2))*TRANSPOSE(ROW($1:$3)))),#N/A)

Vou explicar brevemente como funciona a quarta fórmula.

• NOT(ISERROR($B2:$D2)) retorna uma matriz de três elementos de valores VERDADEIRO / FALSO, com VERDADEIRO para as colunas auxiliares (em uma linha específica) que possuem uma correspondência e FALSE caso contrário. Por exemplo, {TRUE, TRUE, FALSE}.

• TRANSPOSE(ROW($1:$3)) simplesmente fornece a matriz {1, 2, 3}, cada número indicando uma das colunas auxiliares.

• O produto deles - NOT(ISERROR($B2:$D2))*TRANSPOSE(ROW($1:$3))) - produz uma matriz com os valores 1, 2 ou 3 para as colunas com correspondência e zero caso contrário, como em {1, 2, 0}.

• A função MAX retorna o maior número dessa matriz, o que corresponde à coluna auxiliar que retornou a correspondência com o maior número de caracteres. Na matriz {1, 2, 0}, MAX retorna 2, para uma correspondência na coluna C, a segunda das colunas auxiliares.

• INDEX , em seguida, seleciona o código abreviado na coluna C.

• Finalmente, a função IFERROR retorna # N / A se nenhum código abreviado correspondente for encontrado.

Folha1

Folha2

    

Answer 2

Se o número do item for sempre seguido por dois dígitos / caracteres, como nos dados do exemplo (057GK12, 057GK20 e 057GK31), o seguinte fornecerá o número do item:

=LEFT(A1,LEN(A1)-2)

Se você estiver tentando encontrar a célula que tem a abreviação, faça algo assim:

=MATCH(LEFT(A1,LEN(A1)-2),Sheet2!B:B,0)

Se você estiver tentando encontrar o valor completo da abreviação, faça algo assim:

=MATCH(B1&"??",Sheet1!A:A,0)

Estes apenas fornecem o primeiro resultado na coluna. Isso também pressupõe que os números de itens e abreviações sejam formatados como texto e não como valores.