Tentei usar várias combinações de funções, mas parece que não há nenhuma função específica que possa retornar valores não pesquisados/correspondentes. Fazer isso manualmente leva dias devido à grande quantidade de dados que preciso classificar.
Quero que o MS Excel 2003 extraia o restante da Lista A com base na Lista B.
*A Lista A tem 2.000 itens, a Lista B tem apenas 10-30 no máximo
Lista A
No.1----1 2 3 4 5 6 (cada dígito é colocado em 1 célula, sempre 6 dígitos)
No.2----1 1 2 3 4 5 (cada dígito é colocado em 1 célula, sempre 6 dígitos)
No.3 ---- 1 3 4 5 6 7 (cada dígito é colocado em 1 célula, sempre 6 dígitos)
Lista B
No.1 ---- 1 2 3 (cada dígito é colocado em 1 célula, sempre 3 dígitos)
No.2 ---- 1 1 4 (cada dígito é colocado em 1 célula, sempre 3 dígitos)
Não. 3----2 3 5 (cada dígito é colocado em 1 célula, sempre 3 dígitos)
Por exemplo:
Na Lista A, encontre correspondências (se houver) com base na entrada da Lista B e retorne o restante como saída. Se nenhuma correspondência for encontrada, nenhuma saída será necessária.
Lista A
No.1 ---- 1 2 3 4 5 6 (cada dígito é colocado em 1 célula, sempre 6 dígitos)
Com base na Lista B
No.1----1 2 3 (correspondência encontrada (1&2&3 está presente), então seleciono o restante manualmente # # # 4 5 6 ou = 456)
No.2----1 1 4 ( nenhuma correspondência encontrada (1, 1 e 4 não está presente), nenhuma saída)
No.3 ---- 2 3 5 (correspondência encontrada (2, 3 e 5 está presente), então seleciono 1 # # 4 # 6 ou saída = 146)
Imaginei que uso a função COUNT para contar a frequência de cada dígito de 0 a 9 em cada item da Lista A e, em seguida, uso a função IF & AND (especificando qual e quantos dígitos são necessários para se qualificar como uma correspondência) para me dizer qual os itens da Lista B correspondem à Lista A.
Portanto, para cada item da Lista A, preciso que o Excel percorra toda a Lista B e a saída pode variar de nenhuma saída até no máximo 3 saídas.
Também quero poder alterar os valores na Lista B sem alterar a fórmula usada para pesquisar, para poder usar a mesma planilha repetidamente com facilidade.
Até agora, todas as minhas outras tentativas de usar outras funções não conseguiram extrair o restante da maneira que desejo. Se você tiver alguma sugestão, por favor me ensine.
Responder1
OK, aqui está uma solução que funciona, mas pode causar danos cerebrais ao configurá-la. Eu construí um passo de cada vez, calculando um conjunto de coisas que seriam usadas nos próximos cálculos. Depois de ter um modelo funcional, trabalhei de trás para frente, substituindo as fórmulas reais pelas referências de células, para que todas as fórmulas se referissem apenas às suas listas reais e não aos cálculos intermediários. As fórmulas cresceram rapidamente. Na verdade, a primeira tentativa produziu fórmulas que ultrapassaram a capacidade da célula. Dividi em duas mesas, a primeira alimentando a segunda. As tabelas são muito grandes e você ficaria maluco tentando fazer com que todas as referências de células apontassem para os lugares certos para preencher as fórmulas em duas direções nas tabelas. Então adicionei algumas referências indiretas para que as fórmulas possam ser simplesmente copiadas e coladas e funcionem sem limpeza manual. Infelizmente, isso produziu algumas fórmulas bem grandes.
Explicarei isso como um exemplo localizado em locais específicos de uma planilha. Se você precisar localizar as peças em outro lugar, edite todas as referências de linha e coluna na primeira célula e copie e cole para preencher as tabelas. Para sua própria sanidade, configure alguns exemplos conhecidos para poder verificar se as primeiras linhas e colunas de cada tabela estão funcionando antes de preencher tudo. Tome algumas aspirinas profiláticas e começaremos.
Isso se baseia na sua Lista A nas colunas de A a F, com dados começando na linha 1 (2.000 linhas). A lista B está nas colunas H a J com dados começando na linha 1 (30 linhas).
A primeira tabela começa em L1. Esta tabela cria uma lista das posições das entradas da Lista B nos registros da Lista A. Por exemplo:
Position: 1 2 3 4 5 6
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the entry in this table will be: 1 2 5 (stored as a single number: 125)
Se o registro da Lista B não corresponder ao registro da Lista A, haverá um #N/A na célula. O layout desta tabela é assim:
[L] [M] [N] [O]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Na verdade, você precisa colocar os números das linhas como títulos de coluna na linha 2 das colunas M a AP e como rótulos de linha na coluna L. Isso é o que as fórmulas usam como ponteiros. Existem 30 colunas de dados, uma para cada linha de entradas da Lista B, e você terá 2.000 linhas, representando as entradas na Lista A, começando na linha 3. Cada célula da tabela reflete uma entrada da Lista B versus uma entrada da Lista A . Esta é a fórmula para M3:
=MATCH(INDIRECT("H"&M$2),$A1:$F1,0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)
+MATCH(INDIRECT("J"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)+1, , )&":$F"&$L3),0)
Eu quebrei a fórmula aqui para torná-la mais legível, mas é tudo uma fórmula. Verifique se ele está funcionando de M3 a N4 com alguns dados de amostra e copie e cole para preencher a tabela.
A segunda tabela começa em AR1. Esta tabela está estruturada da mesma maneira:
[AR] [AS] [AT] [AU]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Esta tabela funciona de forma semelhante à primeira - cada célula representa os resultados de um registro da Lista B versus um registro da Lista A. Esta tabela contém o restante. Assim, no exemplo que dei para a primeira tabela, o restante seria 359:
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the remainder is: 3 5 9
A fórmula que vai na célula AS3 é:
=IF(ISNA(M3),"",IF(ISERROR(FIND(COLUMN(INDIRECT("a"&$AR3)),M3)),INDIRECT("a"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("b"&$AR3)),M3)),INDIRECT("b"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("c"&$AR3)),M3)),INDIRECT("c"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("d"&$AR3)),M3)),INDIRECT("d"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("e"&$AR3)),M3)),INDIRECT("e"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("f"&$AR3)),M3)),INDIRECT("f"&$AR3),""))
Cada célula nesta tabela conterá o restante ou um caractere nulo se não houver correspondência.
Você queria obter um resumo dos resultados de cada registro da Lista A. Como cada linha da tabela representa um registro da Lista A, o resumo pode ficar no final de cada linha da tabela. As 30 colunas da tabela terminam na coluna BV, portanto os resultados estão na coluna BW. A fórmula para BW3 será:
=AS3&IF(ISBLANK(AS3),""," ")&AT3&IF(ISBLANK(AT3),""," ")& ... &BV3&IF(ISBLANK(BV3),""," ")
Em vez de mostrar todos os 30 termos aqui, mostra apenas os dois primeiros e o último. Siga o mesmo padrão para adicionar o resto. Ele constrói a string de resultados concatenando os resultados de cada correspondência. Se houver um valor em uma célula, ele adiciona um espaço antes do próximo valor. Se você quiser um delimitador diferente, altere o espaço para outra coisa, como espaço de vírgula. Copie esta fórmula na coluna BW para todas as linhas.
Este provavelmente não é o local mais útil para os resultados. Depois de ter tudo funcionando, você pode mover as coisas. Na verdade, se você mover alguma coisa, poderá ter uma limpeza massiva de referências de células. Faria mais sentido apenas criar a saída desejada em outro local e usar referências de células para se referir ao que já está configurado.