Permitir que uma célula contendo vários valores de origem de consulta delimitados também contenha valores sem pesquisa

Question

Permitir que uma célula contendo vários valores de origem de consulta delimitados também contenha valores sem pesquisa

#1 resposta do (2 votos)

1

Pesquisar vários valores em uma tabela diferente, dada uma célula contendo vários valores delimitados

Por favor, abra o link para ver a pergunta respondida anteriormente.

Dado esse contexto, existe uma fórmula que poderia retornar diretamente um valor de texto localizado na coluna E em vez de executar a pesquisa?

Se a coluna E contiver uma combinação de números e valores de texto, a fórmula poderia retornar o nome associado ao número por meio de uma pesquisa e o valor do texto diretamente?

Exemplo:

E3 contém 2;3;Johnny → F3 resultado da fórmula B;C;Johnny

microsoft-excel worksheet-function lookup

por Lucio 28.06.2018 / 10:40

1 resposta

Tags microsoft-excel worksheet-function lookup

Argumento de linha de comando Putty para mudar o título Localizar e substituir coluna específica, mas somente quando é igual a um valor específico

score 2 · Accepted Answer

A nova fórmula é um pouco mais longa que a original, já que a função MID(…) precisa ser copiada e usada mais duas vezes.

Matrizentra(Ctrl+Desloca+Enter)aseguintefórmulaemF2ecopia-cola/fill-downnorestodacoluna:

{=
  TEXTJOIN(
  ";",
  TRUE,
  IF(
    ISNUMBER(
      --MID(
        SUBSTITUTE(E2,";",REPT(" ",99)),
        99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
        +(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
        99
      )
    ),
    INDEX(
      (B:B),
      N(IF(1,
        MATCH(
          --MID(
            SUBSTITUTE(E2,";",REPT(" ",99)),
            99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
            +(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
            99
          ),
          (A:A),
          0
        )
      ))
    ),
    TRIM(
      MID(
        SUBSTITUTE(E2,";",REPT(" ",99)),
        99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
        +(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
        99
      )
    )
  )
)}

Observe que a alteração na fórmula é apenas uma função IF() adicionada que verifica se o valor extraído é um número ou texto e o processa de maneira diferente. Um valor de texto é retornado como está, enquanto um valor numérico é usado para executar uma pesquisa como antes.

A fórmula simplificada do Excel 2016 (somente Windows) modificada é:

{=TEXTJOIN(";",TRUE,IF(ISNUMBER(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b")),INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))),FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b")))}