Jump to content

Como encontrar valores duplicados em dois conjuntos e especificar quais são esses valores em uma lista.


Recommended Posts

Olá, Boa Tarde

 

estou com esse problema e nem sei se é possível resolver no excel;

 

tenho em uma coluna dois conjuntos (VERMELHO E VIOLETA) de valores exemplificados a seguir:

 

A1

 

10

2

3

4

50

6

7

8

9

10

 

10

20

30

40

50

60

70

80

90

100

 

Eu já tenho uma forma de saber quantos valores repetidos existem no conjunto vermelho (2) e quantos tem no violeta (0). E ainda quantos tem repetidos ao todo (5).

 

Agora o que quero saber é se existe uma forma de o excel me retornar quais são os valores que estão duplicados em cada conjunto e ao todo em uma lista.

 

a resposta que eu quero para o exemplo dado é uma lista com os valores 10 e 50.

 

Já fico grato se alguém tentar ajudar.

 

 

 

 

 

 

 

Link to comment
Share on other sites

Boa noite,

Minha colaboração mais recente foi em 28 ou 29/07/2018, pois estou empenhado na minha capacitação técnica.

 

Reconheço que meu relacionamento com o EXCEL é do tipo extremista, mas também reconheço que:

Sempre existe uma (e muitas vezes existem várias) maneiras de solucionar o problema.


Eis minha colaboração bem simplista de solução, considerando o que interpretei apenas lendo o que você postou, OK?

Caso seja necessário você também pode modificá-la, por exemplo, usando TRANSPOR(), MENOR(), ...

Dependendo da sua necessidade você também pode modificar a fórmula, dispensando as colunas auxiliares A e B

evidentemente que a fórmula modificada ficará maior.

 

Spoiler

10    =CONT.SE(A$1:A$10;A1)    =CONT.SE(A$1:A$23;A1)        =SE(C1<2;"";SE(CONT.SE(A1:A$23;A1)=C1;A1;""))
2    =CONT.SE(A$1:A$10;A2)    =CONT.SE(A$1:A$23;A2)        =SE(C2<2;"";SE(CONT.SE(A2:A$23;A2)=C2;A2;""))
3    =CONT.SE(A$1:A$10;A3)    =CONT.SE(A$1:A$23;A3)        =SE(C3<2;"";SE(CONT.SE(A3:A$23;A3)=C3;A3;""))
4    =CONT.SE(A$1:A$10;A4)    =CONT.SE(A$1:A$23;A4)        =SE(C4<2;"";SE(CONT.SE(A4:A$23;A4)=C4;A4;""))
50    =CONT.SE(A$1:A$10;A5)    =CONT.SE(A$1:A$23;A5)        =SE(C5<2;"";SE(CONT.SE(A5:A$23;A5)=C5;A5;""))
6    =CONT.SE(A$1:A$10;A6)    =CONT.SE(A$1:A$23;A6)        =SE(C6<2;"";SE(CONT.SE(A6:A$23;A6)=C6;A6;""))
7    =CONT.SE(A$1:A$10;A7)    =CONT.SE(A$1:A$23;A7)        =SE(C7<2;"";SE(CONT.SE(A7:A$23;A7)=C7;A7;""))
8    =CONT.SE(A$1:A$10;A8)    =CONT.SE(A$1:A$23;A8)        =SE(C8<2;"";SE(CONT.SE(A8:A$23;A8)=C8;A8;""))
9    =CONT.SE(A$1:A$10;A9)    =CONT.SE(A$1:A$23;A9)        =SE(C9<2;"";SE(CONT.SE(A9:A$23;A9)=C9;A9;""))
10    =CONT.SE(A$1:A$10;A10)    =CONT.SE(A$1:A$23;A10)        =SE(C10<2;"";SE(CONT.SE(A10:A$23;A10)=C10;A10;""))
                =SE(C11<2;"";SE(CONT.SE(A11:A$23;A11)=C11;A11;""))
                =SE(C12<2;"";SE(CONT.SE(A12:A$23;A12)=C12;A12;""))
                =SE(C13<2;"";SE(CONT.SE(A13:A$23;A13)=C13;A13;""))
10    =CONT.SE(A$14:A$23;A14)    =CONT.SE(A$1:A$23;A14)        =SE(C14<2;"";SE(CONT.SE(A14:A$23;A14)=C14;A14;""))
20    =CONT.SE(A$14:A$23;A15)    =CONT.SE(A$1:A$23;A15)        =SE(C15<2;"";SE(CONT.SE(A15:A$23;A15)=C15;A15;""))
30    =CONT.SE(A$14:A$23;A16)    =CONT.SE(A$1:A$23;A16)        =SE(C16<2;"";SE(CONT.SE(A16:A$23;A16)=C16;A16;""))
40    =CONT.SE(A$14:A$23;A17)    =CONT.SE(A$1:A$23;A17)        =SE(C17<2;"";SE(CONT.SE(A17:A$23;A17)=C17;A17;""))
50    =CONT.SE(A$14:A$23;A18)    =CONT.SE(A$1:A$23;A18)        =SE(C18<2;"";SE(CONT.SE(A18:A$23;A18)=C18;A18;""))
60    =CONT.SE(A$14:A$23;A19)    =CONT.SE(A$1:A$23;A19)        =SE(C19<2;"";SE(CONT.SE(A19:A$23;A19)=C19;A19;""))
70    =CONT.SE(A$14:A$23;A20)    =CONT.SE(A$1:A$23;A20)        =SE(C20<2;"";SE(CONT.SE(A20:A$23;A20)=C20;A20;""))
80    =CONT.SE(A$14:A$23;A21)    =CONT.SE(A$1:A$23;A21)        =SE(C21<2;"";SE(CONT.SE(A21:A$23;A21)=C21;A21;""))
90    =CONT.SE(A$14:A$23;A22)    =CONT.SE(A$1:A$23;A22)        =SE(C22<2;"";SE(CONT.SE(A22:A$23;A22)=C22;A22;""))
100    =CONT.SE(A$14:A$23;A23)    =CONT.SE(A$1:A$23;A23)        =SE(C23<2;"";SE(CONT.SE(A23:A$23;A23)=C23;A23;""))
 

 

 

dois _031 _ninja_jmn - Encontrar valores duplicados em dois conjuntos e especificar eles em uma lista.jpg

Edited by dois
erro na fórmula da coluna E
  • Like 1
Link to comment
Share on other sites

@dois 

 

Entendi a solução que você elaborou para o problema que coloquei. Aliás, parabéns pelo raciocínio. No entanto tenho algo a mais que não mencionei que torna meu problema mais complicado e só percebi ao ver sua resolução aqui.

 

Tenho vários desses conjuntos e sempre estou querendo comparar dois a dois ou três a três.

 

percebi que quando você coloca o conjunto para verificação da quantidade de repetidos você fixa a parte da coluna (aqui>>=CONT.SE(A$1:A$10;A1) e aqui>>=CONT.SE(A$1:A$23;A1)). e quando eu vou estender para o restante da planilha (para avaliar os proximos conjuntos de dados)  tenho que mudar a parte fixa de forma manual para o seguinte conjunto de dados (aqui>>=CONT.SE(A$14:A$23;A14) e aqui>>=CONT.SE(A$14:A$35;A14)). Para a quantidade de dados que eu tenho se torna inviável fazer isso de forma manual.

 

tem alguma forma de fixar o conjunto de dados como vc fez mas ao mudar para um segundo conjunto de dados mudar também a parte fixa?

 

Desculpa de não consegui deixar tão claro quanto eu gostaria. é difícil explicar.

Link to comment
Share on other sites

@ninja_jmn

Sem problemas,... sempre somos todos aprendizes em alguma coisa.

Também por isso procurei ser cuidadoso nas palavras.

 

É preciso entender o problema por completo pra sugerir a solução, Então:

1.) Até quantos Conjuntos serão analisados ?

2.) Os Conjuntos tem quantidade variável de Elementos ?

3.) Os Conjuntos estão condicionados sempre na Coluna A ou pode ser em Colunas individuais ?

4.) Você precisa das informações nas Colunas auxiliares ?

5.) O resultado precisa ser na Vertical, Horizontal, sem intervalo entre as Dezenas,... ?

 

 

  • Like 1
Link to comment
Share on other sites

1.) Até quantos Conjuntos serão analisados ?

Tenho 313 conjuntos atualmente. sempre no mesmo formato.

image.thumb.png.23ff44949aa46157b8ccb16a6937b6cd.png

se houvesse um jeito de encontrar o resultado para os que já existem de forma automática os próximos poderia fazer de forma manual.

 

2.) Os Conjuntos tem quantidade variável de Elementos ?

 A quantidade é fixa. Sempre 10. Mas organizados da forma que coloquei. Existem células vazias entre eles.

 

3.) Os Conjuntos estão condicionados sempre na Coluna A ou pode ser em Colunas individuais ?

Estão todos em uma única coluna.

 

4.) Você precisa das informações nas Colunas auxiliares ?

Preciso só da que mostra os valores que são comuns aos dois conjuntos, ou, quando comparar três conjuntos, os valores que são comuns aos três conjuntos.

 

5.) O resultado precisa ser na Vertical, Horizontal, sem intervalo entre as Dezenas,... ?

Pode ser na horizontal sim. 

 

Link to comment
Share on other sites

@ninja_jmn

Assim fica fácil!

 

Estou assistindo ao jogo do Brasil x EUA, depois posto minha sugestão.

 

Nota:

Neste caso me parece que o mais lógico é que o resultado seja na VERTICAL

para evitar uso de colunas desnecessárias,  pode ser assim?

Coluna A = Dados a analisar

Coluna B = Dezenas repetidas com intervalos vazios entre elas

Coluna C = Dezenas repetidas SEM intervalos vazios entre elas

 

 

  • Thanks 1
Link to comment
Share on other sites

image.thumb.png.4ec73573319c0940c03786d4063bf202.png

 

Eu já tenho uma fórmula que me diz quantos valores diferentes existem em cada conjunto. Seja de 10, 20, 30, ...

=SOMA(SE(FREQÜÊNCIA(A2:A24;A2:A24)>0;1))

 

Neste caso me parece que o mais lógico é que o resultado seja na VERTICAL

para evitar uso de colunas desnecessárias,  pode ser assim?

Coluna A = Dados a analisar

Coluna B = Dezenas repetidas com intervalos vazios entre elas (seriam só as dezenas que estão repetidas no conjunto de 10?) Pode ser da forma que vc colocou, com interevalos.

Coluna C = Dezenas repetidas SEM intervalos vazios entre elas (seriam as dezenas que estão repetidas no conjunto de 20?) Pode ser da forma que vc colocou,  sem intervalos.

 

 

Edited by ninja_jmn
Faltou colocar a fórmula
Link to comment
Share on other sites

@ninja_jmn

Somente agora vi sua postagem editada.

 

Tentando entender o problema:

1.) São até 313 Conjuntos sempre de 10 Dezenas cada

2.) Identificar as dezenas repetidas/duplicadas em Conjuntos 2 a 2 ou em Conjuntos 3 a 3

3.) Os Conjuntos 2 a 2 ou os Conjuntos 3 a 3 são selecionados dentre 313

4.) Pequena quantidade de dezenas repetidas/duplicadas

 

Modifiquei a planilha mas em Tópico não é permitido anexar, então disponibilizei na área de downloads aqui no forum.

Por favor, me diga se te atende ou o que precisa alterar.

 

 

  • Like 1
Link to comment
Share on other sites

5 horas atrás, ninja_jmn disse:

1.) Até quantos Conjuntos serão analisados ?

Tenho 313 conjuntos atualmente. sempre no mesmo formato.

 

se houvesse um jeito de encontrar o resultado para os que já existem de forma automática os próximos poderia fazer de forma manual.

 

 

Caso sua necessidade seja analisar COMBIN(313;2)=48.828, COMBIN(313;3)=5.061.836,

basta criar uma Macro.

 

  • Like 1
Link to comment
Share on other sites

@dois

Primeiramente, muito obrigado de verdade pela atenção e disponibilidade em resolver este meu problema.

 

 

Ficou muito boa a planilha elaborada. Ela me serve bem. Mas agora que a vi tive outra ideia.

 

1º os conjuntos que serão avaliados 2 a 2 são sempre o último com o anterior. E  os conjuntos que serão avaliados 3 a 3 são sempre o último com os dois anteriores. ou seja, na planilha que vc criou, seria o 7º comparado ao 6º, o sexto ao 5º, o 5º ao quarto, e assim por diante. Sendo que  como eu continuo lançando conjuntos de 10 dezenas diariamente. quando eu lançasse um novo conjunto este já seria comparado ao anterior e aos 2 anteriores.

obs: se para os conjuntos que eu já tenho (313) ficasse automático os próximos não teria problema se tivesse que mudar a fórmula de forma manual (digo isso só pra facilitar).

2°também tenho uma planilha que já não edito mais, que é o contrário. o primeiro conjunto é comparado ao anterior (2º), o 2º ao 3º, o 3º ao 4º, e assim por diante, gostaria de algo semelhante pra ela também se possível.

3º Não sei se você colocou os resultados na horizontal por comodidade, mas ficaria muito melhor para mim se os resultados fossem na vertical aparecendo nas coluna E (quando comparasse 2 a 2) e F (quando comparasse 3 a 3). Eu gostaria que os resultados aparecessem ao lado de cada conjunto nas colunas E e F, ou outra coluna próxima.  Gostei muito da condição de ficar colorido evidenciando as dezenas repetidas encontradas.

4º Já as quantidades de valores repetidos encontrados poderiam aparecer na coluna G a quantidade encontrada será no máximo 10.

5º os dados na minha planilha tem sempre o mesmo formato. Entendi que para as fórmulas funcionarem existe a exigência de ter 2 células vazias antes de cada conjunto. mas preciso que elas se adequem ao formado dos meus dados.

sempre começam assim: 3 células vazias, inicia os dados (10 valores), com uma célula vazia entre cada 1, finaliza com outra célula vazia. então mais 3 células vazias, inicia os dados (10 valores), com uma célula vazia entre cada 1, finaliza com outra célula vazia. sempre assim.

 

image.thumb.png.8a5c804004aa660a0a7814290b81182b.png

 

poderia só explicar o que são os dados da coluna C? eles são só para fazer a planilha funcionar é isso?

 

obs: nem é tão importante, pois dá pra corrigir facinho, mas só encontrei um erro na planilha. onde devia aparecer 6º cj (G8) aparece 5º cj.

 

 

Edited by ninja_jmn
Mencionar Membro
Link to comment
Share on other sites

1 hora atrás, ninja_jmn disse:

Questão 1

poderia só explicar o que são os dados da coluna C? eles são só para fazer a planilha funcionar é isso?

 

Questão 2

obs: nem é tão importante, pois dá pra corrigir facinho, mas só encontrei um erro na planilha. onde devia aparecer 6º cj (G8) aparece 5º cj.

 

 

Questão 1

É isso mesmo.

E na coluna D é a quantidade de Conjuntos em que o Valor da Coluna C está presente;

Por exemplo: o Valor/Dezena 10 está presente 2 vezes no Conjunto 1, porém é contado somente 1 vez,

pois a necessidade é considerar "Presente em 1 Conjunto",

por isso que a contagem da quantidade total de cada Valor está no intervalo de células G14:R14

onde será contado 2 vezes.

 

Também facilita outras análises e adaptações; Exemplos:

- Usar letras ao invés de números

- Maior Sequência

- Maior intervalo

- Naipes

- Setores

- Par/Impar

- ...

 

Questão 2

oooooooopsssssssssss!!!

Me desculpe, nem lembrei de testar isso.

É facinho mesmo de resolver e na próxima versão estará corrigido;

Nesta versão, você pode modificar somente na célula G8:

De...: =SE($G$2<1;"";SE(H8="";"";$G$2-CONT.NÚM(H$8:$H9)))

Para: =SE($G$2<1;"";SE(H8="";"";$G$2))

 

No demais, a maioria tá fácil.

vou analisar o que ainda não entendi e te dou um retorno.

 

  • Like 1
Link to comment
Share on other sites

@ninja_jmn

Confesso que não entender o objetivo traz alguns obstáculos,

Então agradeço se puder me ajudar:

1.) No #print está em destaque que os Valores variam de 01 a 25, porém no exemplo inicial estão entre 1 e 100.

      Podemos continuar entre 1 e 100 para atender todas as Modalidades ?

2.) Pode postar #print com os resultados pra me facilitar a conferência ?

3.) Pelo que entendi são Conjuntos: 2 a 2 (Atual -1), 3 a 3 (Atual -1,-2), 2 a 2 (Atual +1), 3 a 3 (Atual +1,+2); É isso mesmo?

4.) Já comparou esta análise utilizando COMBIN(313;2)=48.828, COMBIN(313;3)=5.061.836 ?

      (Alguns estudos semelhantes que fiz para a LOTOFACIL resultaram em +-5% mais favorável)

 

Link to comment
Share on other sites

Confesso que não entender o objetivo traz alguns obstáculos,

 

o objetivo é saber por quantos resultados seguidos um mesmo valor pode continuar aparecendo entre os 10 valores do próximo conjunto, quais são esses valores e quero saber o máximo de vezes que pode acontecer e quando acontece eventualmente nos seguintes resultados ao continuar atualizando a planilha.

 

Então agradeço se puder me ajudar:

1.) No #print está em destaque que os Valores variam de 01 a 25, porém no exemplo inicial estão entre 1 e 100.

      Podemos continuar entre 1 e 100 para atender todas as Modalidades ?

 

Inicialmente postei a coluna errada, queria a de 25 no máximo e acabei postando a de 100. pode continuar usando a de 100 se depois eu puder editar para no máximo 25.

 

2.) Pode postar #print com os resultados pra me facilitar a conferência ?

Postei a mesma planilha da imagem na área de downloads, tem comentários nela. Não entendi exatamente do que precisa nesse ponto.

 

3.) Pelo que entendi são Conjuntos: 2 a 2 (Atual -1), 3 a 3 (Atual -1,-2), 2 a 2 (Atual +1), 3 a 3 (Atual +1,+2); É isso mesmo?

Acho que na planilha que postei fica mais claro.

 

4.) Já comparou esta análise utilizando COMBIN(313;2)=48.828, COMBIN(313;3)=5.061.836 ?

Não. ainda não fiz nada semelhante.

      (Alguns estudos semelhantes que fiz para a LOTOFACIL resultaram em +-5% mais favorável)

  • Like 1
Link to comment
Share on other sites

@ninja_jmn

Entendi o objetivo. Então:

1.) Salvo engano podemos continuar com Valores de 1 a 100 e sem necessidade de edição

2.) Você pode postar este Exemplo com as Colunas E, F, G já com os resultados ?

      Isso me facilita bastante a conferência.

3.) Eu ainda não fiz a análise do Exemplo

4.) Conhecido o objetivo concluímos que não se aplica esta análise

 

A planilha pode ser com 5 abas ou prefere assim numa única aba ?

aba 1 = Dados dos Conjuntos com 10 Dezenas cada

aba 2 = Análises de Conjuntos: 2 a 2 (Atual -1)

aba 3 = Análises de Conjuntos: 3 a 3 (Atual -1,-2)

aba 4 = Análises de Conjuntos: 2 a 2 (Atual +1)

aba 5 = Análises de Conjuntos: 3 a 3 (Atual +1,+2)

 

Link to comment
Share on other sites

A planilha pode ser com 5 abas ou prefere assim numa única aba ?

aba 1 = Dados dos Conjuntos com 10 Dezenas cada

aba 2 = Análises de Conjuntos: 2 a 2 (Atual -1)

aba 3 = Análises de Conjuntos: 3 a 3 (Atual -1,-2)

aba 4 = Análises de Conjuntos: 2 a 2 (Atual +1)

aba 5 = Análises de Conjuntos: 3 a 3 (Atual +1,+2)

 

O que der menos trabalho, mas preferência por tudo em uma única aba. para facilitar visualização, com possibilidade de ocultar colunas.

Link to comment
Share on other sites

20 minutos atrás, ninja_jmn disse:

A planilha pode ser com 5 abas ou prefere assim numa única aba ?

aba 1 = Dados dos Conjuntos com 10 Dezenas cada

aba 2 = Análises de Conjuntos: 2 a 2 (Atual -1)

aba 3 = Análises de Conjuntos: 3 a 3 (Atual -1,-2)

aba 4 = Análises de Conjuntos: 2 a 2 (Atual +1)

aba 5 = Análises de Conjuntos: 3 a 3 (Atual +1,+2)

 

O que der menos trabalho, mas preferência por tudo em uma única aba. para facilitar visualização, com possibilidade de ocultar colunas.

 

 

Nestes casos costumo utilizar o recurso de Ocultar/Reexibir Colunas.

 

 

dois _034 _ninja_jmn - Ocultar Colunas_Linhas.jpg

 

 

dois _035 _ninja_jmn - Reexibir Colunas_Linhas.jpg

  • Like 1
Link to comment
Share on other sites

Bom dia

 

Conseguiu fazer algo da planilha?

 

Eu fiquei até tarde ontem tentando encontrar uma solução e percebi que o meu real problema é não consegui encontrar uma formula que seja capaz de fixar o fragmento de coluna a ser analisado e ao estender para o próximo fragmento a ser analisafo a formula variar e se adequar ao próximo fragmento. Pq quando eu fixo ou fragmento a fórmula sempre quer retornar o resultado para a parte q fixei primeiro. Sinceramente já pensei em fazer de forma manual essa mudança de fórmula para fazer cada análise. Mas ao lembrar que tenho 313 conjuntos desanima. E ainda mais se for fazer para análises 2a2 e 3a3 também.

 

Resumindo pode me dar uma dica de como posso fixar parte de uma colyna em uma formula. Mas ao estender essa fórmula para as demais células abranger outro fragmento? Estava pensando em algo como retornar a linha e usar isso pra definir o fragmento fixo.

 

 

Link to comment
Share on other sites

@ninja_jmn

Bom dia!

 

Eu também ainda não fiz nada porque fiquei descontrolado com meu palpite pra LOTOFACIL da Independência.

Quase nem dormi e estou agora terminando algumas verificações pra voltar a dormir.

 

Fique tranquilo que a planilha ficará conforme você necessita.

Pra mim é um Exercício resolver sem uso de Macro, mas isso SE eu conseguir resistir a tentação.

 

SE eu acordar disposto e o clássico Palmeiras x Coringão não estiver bonito,

retomo os trabalhos nela, OK?

 

OBS: Talvez usar INDIRETO() resolva.

 

  • Like 1
Link to comment
Share on other sites

 

9 horas atrás, ninja_jmn disse:

Valeu @dois

 

Fico muito agradecido por dispor seu tempo para elaborá-la.

 

 

 

Eu também te agradeço, pois seu pedido me ajudou muitíssimo nos meus estudos.

Veja uma planilha onde está me ajudando. Também por isso demorei em postar a sua,

pois não resisti e estava exercitando o que me ajudou aprender.

 

muitíssimo obrigado!

 

 

dois _ninja_jmn - Encontrar valores duplicados em Conjuntos _Agradecimento _s=1711.jpg

 

 

dois _ninja_jmn - Encontrar valores duplicados em Conjuntos _Agradecimento _s=1710.jpg

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...