☰

Como criar uma nova caixa de opções no filtro do Excel e aplicar um único filtro para múltiplas colunas no Excel?

tag-icon • tag-icon microsoft-excel
Como criar uma nova caixa de opções no filtro do Excel e aplicar um único filtro para múltiplas colunas no Excel?

Eu tenho uma planilha do Excel. Na coluna A há uma lista de objetos (cerca de 50 deles); em outras colunas listadas estão as características desses objetos. As colunas E, F, G são de particular importância para mim. Eles contêm valores numéricos como 1, 7, etc., valores de letras numéricas como 16B, 17C, e também existem valores separados por vírgula que contêm pelo menos alguns valores numéricos e/ou de letras numéricas, por exemplo, 24, 13B, 15Gou 17A, 20B, 16. Também existem algumas células vazias nessas colunas. Aqui está um print screen doplanilha.

Gostaria de usar filtros nas colunas E, F, G.

É assim que eu gostaria que os filtrosser.

E é assim que eles atualmentesão.

Por exemplo, gostaria de filtrar a coluna F por um valor de letras numéricas como '17C'. Idealmente, o que resta seriam células que contêm '17C', por exemplo, 17C, 17C, 24A, 16B, 17B, 17C, 24A, 24F, etc.

Além disso, gostaria de filtrar a coluna F por um valor numérico como '6'. Idealmente, o que resta seriam células que contêm '6', por exemplo 1, 2, 3, 6, 12, 13, 15, 20, , 6, mas não 6E, 7C, 13C, nem 6A, nem 16B, 17A, 19C.

Gostaria de poder filtrar todos os valores possíveis usados ​​nas colunas, ou seja, em 1, 1B, 2, 2D, 2E,… , 20, 24A, 24F, etc.

Gostaria de aplicar essa filtragem nas colunas E, F e G.

Seria maravilhoso se eu pudesse viver sem colunas auxiliares. Mas se isso for impossível de fazer sem eles, então eu os usaria com prazer.

Espero ter conseguido expressar minha luta com clareza.

Responder1

Selecione todas as três colunas e selecione o botão de filtro na faixa de opções. Você pode desmarcar tudo e selecionar 13 em cada coluna.

Agora que li seus detalhes atualizados, tente selecionar todas as três colunas, Formatação condicional, Destacar regras de célula, Texto que contém "13".

Responder2

Nunca ouvi falar de tal recurso nativo do Excel. No entanto, construí uma solução que – acredito – dá os resultados desejados.

Supondo que o layout da coluna mostrado na sua pergunta seja preciso:

  • Na célula I2, insira a fórmula=OR(AND(E2=$H$1,E2<>""), LEFT(E2, LEN($H$1)+1)=($H$1&","), RIGHT(E2, LEN($H$1)+1)=(" "&$H$1), NOT(ISERROR(SEARCH(" "&$H$1&",", E2)))) =OR(AND(E2=$H$1,E2<>""), LEFT(E2, LEN($H$1)+1)=($H$1&","), RIGHT(E2, LEN($H$1)+1)=(" "&$H$1), NOT(ISERROR(SEARCH(" "&$H$1&",", E2))))

    Esta será uma coluna auxiliar. Discutirei essa fórmula com mais detalhes um pouco mais tarde. Por enquanto, observe que ele faz referência E2diversas vezes. Isso está testando o valor em  E2; ou seja, coluna  E.

  • Arraste/preencha a célula I2para a direita, até cell K2. Agora a fórmula J2está testando Column  F e  K2está testando Column  G.

    Colunas auxiliares podem ser colocadas onde você quiser. Você pode colocar as fórmulas acima em Columns  e  X, se quiser - ou , e .YZAEAFAG

  • Na célula H2, digite =OR(I2:K2). Obviamente, se você realocou as colunas auxiliares I- , ajuste isso para corresponder.JK
  • Selecione as células da coluna auxiliar ( H2até K2ou onde quer que você as tenha colocado) e arraste/preencha para cobrir todas as linhas que contêm dados.
  • Coluna de filtro H.

Agora,

  • Insira um valor alfanumérico, como 1B,  17A ou  24, na célula H1.
  • Ative o filtro na Coluna Hpara mostrar apenasverdadeirovalores. Agora você verá apenas as linhas onde Column  Fou contém o valor inserido  .GHH1

Não filtrado:

Filtrado:

OK; a fórmula nas colunas auxiliares (descritas acima como I, Je  K) se parece com 

=OU(expressão 1,expressão 2,expressão 3,expressão 4)
onde as quatro expressões são:

  • AND(E2=$H$1,E2<>""). Isso testa se o valor em Column  E é simplesmente igual ao valor em H1(mas não em branco). Por exemplo, isso será TRUE E3se o valor em H2for 24.
  • LEFT(E2, LEN($H$1)+1)=($H$1&","). Isso testa se o valor em Column  Ecomeça com o valor em  H1. Mais precisamente, se o valor em Coluna  E começa com o valor  H1seguido por uma vírgula. Então, por exemplo, for F3this será TRUE para 16B mas FALSE para 1or 16.

  • RIGHT(E2, LEN($H$1)+1)=(" "&$H$1). Imagem espelhada do acima; isso testa se o valor em Column  Etermina com o valor em  H1. Mais precisamente, se o valor em Column  Etermina com o valor em  H1 precedido por um espaço. Então, por exemplo, for F3this será TRUE para  19C mas FALSE para 9Cor  C.

    Observe que os três testes acima não diferenciam maiúsculas de minúsculas, porque no Excel não diferenciam maiúsculas de minúsculas.string1 = string2

  • NOT(ISERROR(SEARCH(" "&$H$1&",", E2))). Isso testa se o valor em Column  Econtém o valor em  H1. Mais precisamente, se o valor em Coluna  Econtém o valor  H1 precedido de espaço e seguido de vírgula. Isso não diferencia maiúsculas de minúsculas, porque a SEARCHfunção não diferencia maiúsculas de minúsculas.

Portanto, a fórmula in I2testa se o valor in E2contém o valor in H1de alguma forma.

Portanto , a fórmula H2testa se qualquer valor na linha 2 (Colunas  Eou ) contém o valor em .FGH1

Se você deseja gerar uma lista dos valores alfanuméricos em Columns  Ee (por exemplo,  , , , , F,  , , , etc.), isso pode ser feito, mas não farei isso agora.G1B2D2E5C2416B17A19C

informação relacionada