Busqueda en excel
Buenos días. A ver si alguien me puede ayudar.
Tengo un matriz de 4 columnas y 40 filas con datos numéricos. Necesito sacar a una celda la cantidad de datos que se repiten en esta matriz. Por ejemplo
1 2 3 4
5 6 7 8
1 9 10 11 1 repetición
1 2 12 13 2 repeticiones (el 1 y el 2)
...
total = 3 repeticiones
Y también me resultaría interesante, poder cambiar automáticamente el formato de la celda que contiene el dato repetido.
Muchas gracias.
Tengo un matriz de 4 columnas y 40 filas con datos numéricos. Necesito sacar a una celda la cantidad de datos que se repiten en esta matriz. Por ejemplo
1 2 3 4
5 6 7 8
1 9 10 11 1 repetición
1 2 12 13 2 repeticiones (el 1 y el 2)
...
total = 3 repeticiones
Y también me resultaría interesante, poder cambiar automáticamente el formato de la celda que contiene el dato repetido.
Muchas gracias.
1 respuesta
Respuesta de jrgces
1
Prueba poniendo esta fórmula en la columna donde deban aparecer el número de repeticiones de cada fila:
=SUMAPRODUCTO(--(CONTAR.SI($A$1:$D1;A1:D1)-1>0))
El número total de repeticiones es sencillo de obtener sumando la columna donde vaya la fórmula anterior.
Para que las celdas con datos repetidos cambien de aspecto:
- Selecciona el rango donde se encuentre la matriz
- haz Formato -> Formato Condicional
- en 'Condición 1' selecciona "Fórmula"
- pon como fórmula =CONTAR.SI($A$1:$D1;A1)-1>0
- pulsa el botón 'Formato' y selecciona el aspecto que deban tener las celdas con repeticiones
=SUMAPRODUCTO(--(CONTAR.SI($A$1:$D1;A1:D1)-1>0))
El número total de repeticiones es sencillo de obtener sumando la columna donde vaya la fórmula anterior.
Para que las celdas con datos repetidos cambien de aspecto:
- Selecciona el rango donde se encuentre la matriz
- haz Formato -> Formato Condicional
- en 'Condición 1' selecciona "Fórmula"
- pon como fórmula =CONTAR.SI($A$1:$D1;A1)-1>0
- pulsa el botón 'Formato' y selecciona el aspecto que deban tener las celdas con repeticiones
Muchas gracias por la rapidez en la respuesta.
El formato condicional funciona perfectamente, pero la fórmula no del todo. Si en la fila no se repite nada, el resultado es 0 = correcto. Pero si se repite un dato el resultado es 2.
He intentado hacerlo en lugar de por filas, con la matriz entera, y el resultado es el mismo, cuenta todas las que se repiten, incluid por así decirlo el original, en el ejemplo que te he puesto antes, él resultado debería ser 3, y sin embargo, da 5.
A ver si lo podemos solucionar. Por cierto, qué función tienen los dos signos negativos antes de la función (Contar. Si)?
El formato condicional funciona perfectamente, pero la fórmula no del todo. Si en la fila no se repite nada, el resultado es 0 = correcto. Pero si se repite un dato el resultado es 2.
He intentado hacerlo en lugar de por filas, con la matriz entera, y el resultado es el mismo, cuenta todas las que se repiten, incluid por así decirlo el original, en el ejemplo que te he puesto antes, él resultado debería ser 3, y sin embargo, da 5.
A ver si lo podemos solucionar. Por cierto, qué función tienen los dos signos negativos antes de la función (Contar. Si)?
No sé, a mí me funciona bien la fórmula, he subido un ejemplo a: http://www.jrgc.es/ejemplos/ejemplo20090223a.xls
Los dos signos negativos son para convertir en números los valores lógicos devueltos por CONTAR. SI para poder sumarlos. Hay más formas de hacer lo mismo, por ejemplo *1 ó ^1
Los dos signos negativos son para convertir en números los valores lógicos devueltos por CONTAR. SI para poder sumarlos. Hay más formas de hacer lo mismo, por ejemplo *1 ó ^1
Muchísimas gracias.
El problema que yo tenía es que estaba copiando mal la fórmula
La correcta (la tuya) =SUMAPRODUCTO(--(CONTAR.SI($A$1:$D1;A1:D1)-1>0))
y yo ponía =SUMAPRODUCTO(--(CONTAR.SI($A$1:$D$1;A1:D1)-1>0))
(Los problemas de las referecias absolutas, y el no fijarse bien en todo)
Y lo de los dos signos negativos, efectivamente yo utilizo *1, no había caído en esa otra posibilidad.
El problema que yo tenía es que estaba copiando mal la fórmula
La correcta (la tuya) =SUMAPRODUCTO(--(CONTAR.SI($A$1:$D1;A1:D1)-1>0))
y yo ponía =SUMAPRODUCTO(--(CONTAR.SI($A$1:$D$1;A1:D1)-1>0))
(Los problemas de las referecias absolutas, y el no fijarse bien en todo)
Y lo de los dos signos negativos, efectivamente yo utilizo *1, no había caído en esa otra posibilidad.
