¿Como hacer una búsqueda múltiple en Excel?
Tengo una Tabla de 5 columnas con 700 registros. En ellas solo hay números del 1 al 50. Es un histórico de resultado de lotería. Quiero hacer estadística, y para ello hacer búsquedas del tipo:
Ej: Registros que contengan el numero 04 y el 23, Independientemente de si están en la columna 1,2, 3, 4 o 5
¿Cómo puedo realizar estas búsquedas?
2 Respuestas
·
Es una macro un poco aparatosa pero te sirve para buscar cualquier número de coincidencias, desde 1 hasta 5. Incluso más, pero no tendría sentido.
Los datos pueden empezar en cualquier fila, conviene que no haya otras filas con números en las columnas A:E ya que no hay que decir las filas de datos.
En la celda G1 puedes poner "Condiciones" y en H1 "Encontrados"
En G2, G3, G4, G5, G6 pondrás los números que deben coincidir, el primero siempre en G2 y cuando aparezca una celda vacía se considera el final de estos números.
Tras introducir los números ejecuta la macro y en H2 y siguientes te aparecerán los números de fila donde aparecen todos esos números.
La macro se puede mejorar claro, por ejemplo haciendo que queden resaltadas las filas, o menos conflictivo una X en la columna F, aunque eso ya lo dejo a tu gusto, lo principal creo que ya está hecho.
Sub Buscar()
'By ValeroASM
Dim i, j, k, Condiciones(5), NuCondis, Contador As Integer
Dim Encontrado, Encontrado2 As Boolean
Dim Rango As Range
Dim PrimeraDireccion As String
NuCondis = 0
i = 2
While Not IsEmpty(Cells(i, "G"))
j = Val(Cells(i, "G"))
If j >= 1 And j <= 50 Then
Encontrado = False
For k = 1 To NuCondis
If j = Condiciones(k) Then
Encontrado = True
Exit For
End If
Next
If Not Encontrado Then
NuCondis = NuCondis + 1
Condiciones(NuCondis) = j
End If
End If
i = i + 1
Wend
If NuCondis = 0 Then
MsgBox ("No hay numeros, póngalos en G2,G3,...")
Exit Sub
End If
Contador = 0
Columns("H").Clear
Range("H1") = "Encontrados"
Set Rango = Columns("A:E").Find(what:=Condiciones(1), lookat:=xlWhole)
If Not Rango Is Nothing Then
PrimeraDireccion = Rango.Address
Do
k = Rango.Row
Encontrado = True
For i = 2 To NuCondis
Encontrado2 = False
For j = 1 To 5
If Cells(k, j) = Condiciones(i) Then
Encontrado2 = True
Exit For
End If
Next
If Encontrado2 = False Then
Encontrado = False
Exit For
End If
Next
If Encontrado Then
Contador = Contador + 1
Cells(Contador + 1, "H") = k
End If
Set Rango = Columns("A:E").FindNext(Rango)
Loop While Not Rango Is Nothing And Rango.Address <> PrimeraDireccion
End If
End Sub·
Y eso es todo.
Ángel ante todo muchas gracias por tu respuesta/tiempo/atención.
No soy muy diestro con las macros, He creado una nueva macro con el contenido que me ha pasado pero al ejecutarlo no consigo bien el resultado que busco. Parece que la macro no anda muy bien.
Los trabajos hechos no son moco de pavo, yo creo que solo por eso deberías haber puntuado excelente a los dos expertos para empezar.
Esta es una pantalla del uso se han buscado los números 2, 49 y 33 y aparecen en dos filas, la 20 y 117.
Si no eres muy diestro con las macros mejor me pasas el fichero y así veo si está bien adaptada o si sabes que las macros hay que ejecutarlas que no funcionan automáticamente salvo que estén diseñadas para eso. Además podría mejorarla, hacer que funcionara automáticamente, tu dime como quieres que salgan los resultados, resaltando las filas, diciendo la cantidad, etc. Pero por favor, sube la puntuación, que así no vas a ser amigo de los expertos.
Mándame el fichero a
¡Ah, se me olvidó la captura!
·
Por supuesto!! perdón por la ofensa! ya he revalorado las respuestas.
Voy a volver a chequearlo todo y en cualquier modo le paso el fichero a ver que hago mal.
Muchísimas gracias!!!
Ya recibí el fichero y me he puesto a corregirlo. No funcionaba bien ya que has añadido en la columna 1 la fecha y no has modificado lo necesario para que compare las columans 2 a 6 en lugar de 1 a 5.
Me gusta este programa y habría querido probar algún código distinto de comparar los aciertos, pero no tengo tiempo y yo creo que el que hay es bastante rápido y poco se podría mejorar. Así es como quedará tras los cambio para introducir lo que me pedías y para adaptar que ahora los datos estarán en las columnas B a F. También he cambiado lo de los números que introduces tu, ahora cualquiera que haya en las celdas K2 a K6 servirá aunque haya celdas en blanco entre medias.
Sub Buscar()
'By ValeroASM
Dim i, j, k, Condiciones(5), NuCondis, C As Integer
Dim Encontrado, Encontrado2 As Boolean
Dim Rango As Range
Dim PrimeraDireccion As String
NuCondis = 0
For i = 2 To 6
j = Val(Cells(i, "K"))
If j >= 1 And j <= 50 Then
Encontrado = False
For k = 1 To NuCondis
If j = Condiciones(k) Then
Encontrado = True
Exit For
End If
Next
If Not Encontrado Then
NuCondis = NuCondis + 1
Condiciones(NuCondis) = j
End If
End If
Next
If NuCondis = 0 Then
MsgBox ("No hay numeros, póngalos en K2,K3,...")
Exit Sub
End If
Application.ScreenUpdating = False
C = 0
i = Range("M" & Rows.Count).End(xlUp).Row
If i >= 2 Then Range("M2:W" & i).Clear
Set Rango = Columns("B:F").Find(what:=Condiciones(1), lookat:=xlWhole)
If Not Rango Is Nothing Then
PrimeraDireccion = Rango.Address
Do
k = Rango.Row
Encontrado = True
For i = 2 To NuCondis
Encontrado2 = False
For j = 2 To 6
If Cells(k, j) = Condiciones(i) Then
Encontrado2 = True
Exit For
End If
Next
If Encontrado2 = False Then
Encontrado = False
Exit For
End If
Next
If Encontrado Then
C = C + 1
Cells(C + 1, "M") = k
ActiveSheet.Range("A" & k & ":I" & k).Copy Destination:=ActiveSheet.Range("O" & C + 1)
Range("O" & C + 1 & ":T" & C + 1 & ",V" & C + 1 & ":W" & C + 1).Interior.Color = RGB(255, 192, 0)
End If
Set Rango = Columns("B:F").FindNext(Rango)
Loop While Not Rango Is Nothing And Rango.Address <> PrimeraDireccion
End If
Application.ScreenUpdating = True
End SubY eso es todo, ahora mismo te mando el libro ya hecho.
Una captura de pantalla por si hay alguien más siguiendo la pregunta.
Otra posibilidad. Suponiendo que:
1) La tabla con los números está en A1:E700
2) Los dos números a buscar están en G1 y G2
Prueba poniendo esta fórmula en la fila 1 de cualquier columna vacía:
=K.ESIMO.MENOR(SI((1*($A$1:$A$700=$G$1))+(1*($A$1:$A$700=$G$2))+(1*($B$1:$B$700=$G$1))+(1*($B$1:$B$700=$G$2))+(1*($C$1:$C$700=$G$1))+(1*($C$1:$C$700=$G$2))+(1*($D$1:$D$700=$G$1))+(1*($D$1:$D$700=$G$2))+(1*($E$1:$E$700=$G$1))+(1*($E$1:$E$700=$G$2))=2;FILA($A$1:$A$700));FILA())
La fórmula devolverá la primera fila en la que se encuentren ambos números, independientemente de en qué columna estén.
Copiando la fórmula y pegándola hacia abajo, irá mostrando en qué otras filas se encuentran ambos números, si las hay. Cuando no queden más filas empezará a aparecer el error #¡NUM!
Es genial esta fórmula, ¿cómo puedo ampliarla para buscar hasta 5 números? ¿Por qué así solo puedo buescar 2 no?
Suponiendo que:
- Para buscar 2 números se usa G1:G2
- Para buscar 3 números se usa H1:H3
- Para buscar 4 números se usa I1:I4
- Para buscar 5 números es usa J1:J5
Las fórmulas serían:
2 números (hay un pequeño cambio, en la primera función FILA, con respecto a la fórmula anterior):
=K.ESIMO.MENOR(SI((1*($A$1:$A$700=$G$1))+(1*($A$1:$A$700=$G$2))+(1*($B$1:$B$700=$G$1))+(1*($B$1:$B$700=$G$2))+(1*($C$1:$C$700=$G$1))+(1*($C$1:$C$700=$G$2))+(1*($D$1:$D$700=$G$1))+(1*($D$1:$D$700=$G$2))+(1*($E$1:$E$700=$G$1))+(1*($E$1:$E$700=$G$2))=2;FILA($1:$700));FILA())
3 números:
=K.ESIMO.MENOR(SI((1*($A$1:$A$700=$H$1))+(1*($A$1:$A$700=$H$2))+(1*($A$1:$A$700=$H$3))+(1*($B$1:$B$700=$H$1))+(1*($B$1:$B$700=$H$2))+(1*($B$1:$B$700=$H$3))+(1*($C$1:$C$700=$H$1))+(1*($C$1:$C$700=$H$2))+(1*($C$1:$C$700=$H$3))+(1*($D$1:$D$700=$H$1))+(1*($D$1:$D$700=$H$2))+(1*($D$1:$D$700=$H$3))+(1*($E$1:$E$700=$H$1))+(1*($E$1:$E$700=$H$2))+(1*($E$1:$E$700=$H$3))=3;FILA($1:$700));FILA())
4 números:
=K.ESIMO.MENOR(SI((1*($A$1:$A$700=$I$1))+(1*($A$1:$A$700=$I$2))+(1*($A$1:$A$700=$I$3))+(1*($A$1:$A$700=$I$4))+(1*($B$1:$B$700=$I$1))+(1*($B$1:$B$700=$I$2))+(1*($B$1:$B$700=$I$3))+(1*($B$1:$B$700=$I$4))+(1*($C$1:$C$700=$I$1))+(1*($C$1:$C$700=$I$2))+(1*($C$1:$C$700=$I$3))+(1*($C$1:$C$700=$I$4))+(1*($D$1:$D$700=$I$1))+(1*($D$1:$D$700=$I$2))+(1*($D$1:$D$700=$I$3))+(1*($D$1:$D$700=$I$4))+(1*($E$1:$E$700=$I$1))+(1*($E$1:$E$700=$I$2))+(1*($E$1:$E$700=$I$3))+(1*($E$1:$E$700=$I$4))=4;FILA($1:$700));FILA())
5 números:
=k.esimo.menor(si((1*($a$1:$a$700=$j$1))+(1*($a$1:$a$700=$j$2))+(1*($a$1:$a$700=$j$3))+(1*($a$1:$a$700=$j$4))+(1*($a$1:$a$700=$j$5))+(1*($b$1:$b$700=$j$1))+(1*($b$1:$b$700=$j$2))+(1*($b$1:$b$700=$j$3))+(1*($b$1:$b$700=$j$4))+(1*($b$1:$b$700=$j$5))+(1*($c$1:$c$700=$j$1))+(1*($c$1:$c$700=$j$2))+(1*($c$1:$c$700=$j$3))+(1*($c$1:$c$700=$j$4))+(1*($c$1:$c$700=$j$5))+(1*($d$1:$d$700=$j$1))+(1*($d$1:$d$700=$j$2))+(1*($d$1:$d$700=$j$3))+(1*($d$1:$d$700=$j$4))+(1*($d$1:$d$700=$j$5))+(1*($e$1:$e$700=$j$1))+(1*($e$1:$e$700=$j$2))+(1*($e$1:$e$700=$j$3))+(1*($e$1:$e$700=$j$4))+(1*($e$1:$e$700=$j$5))=5;fila($1:$700));fila())
Todas, evidentemente, matriciales.
Me ha encantado este sistema de búsqueda con excel! yo he estado buscando la manera de hacer lo mismo pero en vez de obtener los resultados de la búsqueda en cada una de las filas de cada sorteo, cómo en el ejemplo, lo que quería hacer es introducir una serie de números entre el 1 y el 50 aleatoriamente y que al realizar la búsqueda me indicase la zona del listado donde esos números se encuentran más próximos entre si, es decir el foco o agrupación dónde están. Bien sea resaltandolos o bien mostrando la poción de las lista dónde están y no lo he logrado nose si esto es algo muy complicado o realmente es sencillo de hacer... - Marcos Newman