É possível filtrar um array por uma string parcial e listar múltiplas entradas apenas uma vez usando uma única fórmula (sem filtro automático, VBA ou colunas adicionais). Por exemplo, tenho a seguinte planilha:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 |
03| 1 | B/as | V2 |
04| 2 | A/ab | V3 |
05| 3 | B/ab | V4 |
06| 3 | B/as | V5 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V5 |
09| 3 | A/ab | V5 |
Se eu filtrar a coluna B porAula"A/*" mas mostrando seu valor apenas uma vez, o resultado deverá ser:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V2
05| 3 | A/ab | V1 |
06| 3 | B/as | V4 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V2 |
em vez de
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V1
05| 3 | A/ab | V1 | V2
06| 3 | B/as | V4 | V2
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V6 |
Filtre a coluna pela string parcial funciona (adaptadoquedescrição) para algo assim:
...
IF(
ISNUMBER(Search("A/*"; $B$2:$B$9))
...
isso resulta em um array com os índices da lista $B$2:$B$9:
ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}
e assim
IF(IS...): {1;3;4;8;9}
Até agora não encontrei nenhuma maneira de combinar isso com a abordagem de "lista de nomes exclusivos"
`MATCH(0;INDEX(COUNTIF(`
conforme descritoaqui
O pouco que tenho é isso, mas não está funcionando muito bem e causa uma grande carga de CPU, por exemplo, célula C8
{=IFERROR(INDEX(
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
MATCH(0;
INDEX(COUNTIF($C$2:C7;
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
0;0);0));
"error")
Responder1
Resolvi, não perfeitamente (precisa de 3 colunas), mas funciona perfeitamente.
A | B | C | D | E | F | G
-------------------------------------------------------
01| Array |Array | Text | search | search | ordered
02| Source|Source | sought| results | results |
03| #1 | #2 | *a* | #1 | #2 |
04| aa | c12 | | c12 | c12 | c02
05| ca | c13 | | c13 | c13 | c06
06| ad | c06 | | c06 | c06 | c12
07| ee | c11 | | c02 | c02 | c13
08| fa | c02 | | c06 | c25 | c25
09| gg | c12 | | c13 | |
10| ba | c06 | | c06 | |
11| aa | c13 | | c25 | |
12| ad | c06 | | #NUM! | |
13| gt | c12 | | #NUM! | |
14| aa | c25 | | #NUM! | |
Coluna Eliste todos os itens da coluna C se a célula correspondente na coluna B contiver a expressão de D3. Fórmula na célula E4 que é copiada para E5-E14:
{=INDEX(C:C;
SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
$B$4:$B$14));
ROW($B$4:$B$14));
ROWS($E$4:E4)))}
Você precisa pressionar Ctrl-Shift-Enter para inserir a fórmula como uma matriz, mas tome cuidado, pois pode demorar um pouco quando você procura uma tabela grande. Tenho 1300 células, isso demorou mais de um minuto, mas só para inserir a fórmula, transferi-la para as demais células sem demora.
Aí vem o que faz:
- ÍNDICE (arg1,arg2)produzirá o valor do item/célula n (arg2) da coluna C (arg1). N é calculado emPequeno.
- PEQUENO(arg1,arg2)deve retornar o k-ésimo (arg2) menor valor em um conjunto de dados (arg1).
Esta função retorna valores com uma posição relativa específica em um conjunto de dados. É exatamente isso que é necessário para o uso adequado doSEeLINHAS, aninhado mais profundamente. SE (teste lógico,valor se verdadeiro)é a parte principal do truque: ele constrói uma matriz de números de linhas onde a condição IF é verdadeira (observe que oSEnão tem 'outro' valor, será apenasFALSOonde a condição não é verdadeira)
- teste lógico:ÉNÚMERO(PESQUISA($D$3&"/*"; $B$4:$B$14))retorna uma matriz de Verdadeiro e Falso dependendo se SEARCH resulta em um valor numérico ou não para cada célula fornecida no intervalo $B$4:$B$15.
O resultado para o exemplo superior é:- PROCURAR:1, 2, 1, #VALOR, 2, #VALOR, 2, 1, 1, #VALOR, 1
- ÉNÚMERO:VERDADEIRO, VERDADEIRO, VERDADEIRO, FALSO, VERDADEIRO, FALSO, VERDADEIRO, VERDADEIRO, VERDADEIRO, FALSO, VERDADEIRO
- valor se verdadeiro: ROW($B$4:$B$14)) retorna uma matriz preenchida pelos números de linha da matriz $B$4:$B$14
O resultado para o exemplo superior é: 4, 5, 6, 7, 8 , 9, 10, 11, 12, 13, 14
SEcombinaráteste lógico#1 evalor se verdadeiro#2. Todos os valores em #1 serão ignorados onde #2 diz FALSE na mesma posição na matriz #2. Finalmente temos um array, indicando em qual linha da coluna B, dentro do intervalo dado emLINHA(...)a expressão de D3 é encontrada. O resultado para o exemplo superior é: 4,5,6,8,10,11,12,14
- teste lógico:ÉNÚMERO(PESQUISA($D$3&"/*"; $B$4:$B$14))retorna uma matriz de Verdadeiro e Falso dependendo se SEARCH resulta em um valor numérico ou não para cada célula fornecida no intervalo $B$4:$B$15.
LINHAS($E$4:E4)é apenas um truque que lhe dará um número crescente (ou seja, 1 em F2, 2 em F3...). o que é usado emPEQUENOcomoarg2. O resultado na primeira célula (LINHAS (...)=1) será 4 (menor valor), no segundo 5 e assim sucessivamente. No final, cada célula seguinte mostrará o número/posição da linha na coluna B onde a expressão de D3 é encontrada.
Coluna Ffiltra duplicatas, o que foi a parte mais difícil. A coluna F listará "apenas" todos os itens listados na coluna E uma vez.
Essa é a fórmula inserida em F5! (F4 é igual ao E4) usando ctrl-shift-enter:
{=IFERROR(INDEX($C$2:$C$14;
MATCH(0;
COUNTIF($E$4:E4;
$C$2:$C$14);
0));
"")}
Aí vem o que faz:
- CONT.SE(arg1,arg2)resulta em uma matriz do comprimento do intervaloarg1, indicando por 1 onde onde está uma correspondência das entradas emarg2.
O resultado para o exemplo superior é: 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0 - Corresponder(valor de pesquisa,variedade,tipo de partida)procura a primeira ocorrência de 0 na matriz resultante deCONT.SE(...)qual é a 3ª posição no exemplo superior
- valor de pesquisa:0, primeiro novo valor/não duplicado
- variedade:matriz resultante deCONT.SE(...)
- tipo de partida:0 = exatamente
- ÍNDICE(arg2de CONT.SE, Correspondência(...))finalmente mostrará o valor novo/não duplicado que está na 3ª posição do intervaloarg2, o que é c06 no exemplo superior.
Coluna GFinalmente todos os itens serão ordenados em ordem alfabética. Essa é a fórmula inserida no G4 usando ctrl-shift-enter:
{=IFERROR(INDEX($F$4:$F$14;
MATCH(ROWS($G$4:$G4);
COUNTIF($F$4:$F$14;
"<="&$F$4:$F$14);
0));
"")}
Aí vem o que faz:
- CONT.SE(arg1,arg2)é a parte principal do truque: compara os valores de texto fornecidos emarg2com todos os outros valores de texto fornecidos emarg1e retorna sua classificação relativa (ordem alfabética).
O resultado para o exemplo superior é:
3, 4, 2, 1, 5 - LINHAS($E$2:E2)é apenas um truque que lhe dará um número crescente (ou seja, 1 em G2, 2 em G3...). o que é usado emCorrespondercomovalor de pesquisa.
- Corresponder(valor de pesquisa,variedade,tipo de partida)procura a primeira ocorrência deLINHAS (...)na matriz resultante deCONT.SE(...).
O resultado para o exemplo superior é:
4, 3, 1, 2, 5- célula:LINHAS (...)=1 => 4
- célula:LINHAS (...)=2 => 3
- célula:LINHAS (...)=3 => 1
- célula:LINHAS (...)=4 => 2
- célula:LINHAS (...)=5 => 5
- ÍNDICE(arg1 de CONT.SE, Correspondência(...))finalmente mostrará a entrada classificada correspondente ao seuLINHAS (...)resultado. O resultado para o exemplo superior é:
- célula:LINHAS (...)=1 => 4 pol.CONT.SEmatriz => c02
- célula:LINHAS (...)=2 => 3 pol.CONT.SEmatriz => c06
- célula:LINHAS (...)=3 => 1 pol.CONT.SEmatriz => c12
- célula:LINHAS (...)=4 => 2 pol.CONT.SEmatriz => c13
- célula:LINHAS (...)=5 => 5 pol.CONT.SEmatriz => c25
Até aí tudo bem, a etapa final seria combinar tudo em uma coluna. Pelo menos encontrei algunsajudamesclando colunas F&G (mas não farei isso hoje).