Encontrar el registro textual más repetido
A) Experto (a):
Pido tu colaboración para lo siguiente:
Tengo en excel una columna de nombres de personas (sólo texto); necesito que en una celda cualquiera, me coloque el primer (1er.) Nombre más repetido, en otra celda el segundo (2º) nombre más repetido, en otra el tercer (3er.) Nombre más repetido, y así sucesivamente. ¿Cuál fórmula debo usar, teniendo en cuenta que no son registros numéricos sino exclusivamente textuales?
Pido tu colaboración para lo siguiente:
Tengo en excel una columna de nombres de personas (sólo texto); necesito que en una celda cualquiera, me coloque el primer (1er.) Nombre más repetido, en otra celda el segundo (2º) nombre más repetido, en otra el tercer (3er.) Nombre más repetido, y así sucesivamente. ¿Cuál fórmula debo usar, teniendo en cuenta que no son registros numéricos sino exclusivamente textuales?
Respuesta de Enrique
1
No me quedo muy claro lo q quieres hacer, si pusieran un ejemplo o lo pudieras explicar un poco mas claro, te ayudo con mucho gusto.
En una Columna de sólo nombres de personas, necesito definir cuál es el primer nombre que más se repita, el segundo que más se repita y así sucesivamente. CUÁL ES LA FÓRMULA INDICADA PARA ELLO?
Se me ocurren 2 soluciones para lo que planteas:
1ro.- La menos trabajosa: una tabla dinamica, insertas una tabla dinamica y arrastras los Nombres(es el titulo)) tanto al eje de etiqueta de fila como al de valores, si por defecto no te lo cuenta pues le das clic derecho a la tabla y en la opcion "Resumir Valores por" y le pones recuento.
2do.- La mas trabajosa: Saques una lista de los nombres sin repetidos, lo puedes hacer de la siguiente manera: selecciona tu lista(digamos columna A2:A100) y te vas a la pestaña Datos y le das clic en quitar duplicados, imaginemos que tu nueva lista este en la columna B y comienze en B2 y termine en B20; colocas esta formula en C2:
=contar.si($A$2:$A$100,B2) y arrastras la formulas hacia abajo.
1ro.- La menos trabajosa: una tabla dinamica, insertas una tabla dinamica y arrastras los Nombres(es el titulo)) tanto al eje de etiqueta de fila como al de valores, si por defecto no te lo cuenta pues le das clic derecho a la tabla y en la opcion "Resumir Valores por" y le pones recuento.
2do.- La mas trabajosa: Saques una lista de los nombres sin repetidos, lo puedes hacer de la siguiente manera: selecciona tu lista(digamos columna A2:A100) y te vas a la pestaña Datos y le das clic en quitar duplicados, imaginemos que tu nueva lista este en la columna B y comienze en B2 y termine en B20; colocas esta formula en C2:
=contar.si($A$2:$A$100,B2) y arrastras la formulas hacia abajo.
2 respuestas más de otros expertos
Respuesta de prozac
1
Hay varias formas de hacerlo. Quizá la mas clara es generar primero una tabla dinámica y después ordenar el resultado en orden descendente.
Para realizar la tabla dinámica, dependiendo de la versión de Excel se hace a través de Datos/insertar Tabla en 2003 y gráficos dinámicos o en Insert/Tabla Dinámica en Excel 2007. Hay otros posts míos y supongo que de otros sobre como insertar tablas dinámicas por lo que solo me centro en los detalles para el caso.
Una forma sencilla es seleccionar todo el rango antes de entrar en el asistente. Es necesario que la primera celda del rango seleccionado sea el nombre del campo. En este caso podemos escribir (si no lo tenemos) "Nombres" en esa celda y debajo de ella los nombres que vamos a comprobar.
En el diseño, dentro del aistente, tenemos que arrastrar el nombre de campo "Nombres" a la columna izquierda de la tabla y soltarlo ahí. Después hacemos lo mismo sobre el centro de la tabla en la zona de datos. La etiqueta cambia a "cuenta de Nombres" que es justo lo que nos interesa. Al acabar tendremos nuestra tabla con dos columnas, una con los nombres y al lado el número de veces que aparece cada uno.
Por último solo queda seleccionar la columna con los números y pulsar el icono ordenar (el que tiene za hacia abajo) para que nos ponga el primero el que mas veces sale, luego el segundo...
Para realizar la tabla dinámica, dependiendo de la versión de Excel se hace a través de Datos/insertar Tabla en 2003 y gráficos dinámicos o en Insert/Tabla Dinámica en Excel 2007. Hay otros posts míos y supongo que de otros sobre como insertar tablas dinámicas por lo que solo me centro en los detalles para el caso.
Una forma sencilla es seleccionar todo el rango antes de entrar en el asistente. Es necesario que la primera celda del rango seleccionado sea el nombre del campo. En este caso podemos escribir (si no lo tenemos) "Nombres" en esa celda y debajo de ella los nombres que vamos a comprobar.
En el diseño, dentro del aistente, tenemos que arrastrar el nombre de campo "Nombres" a la columna izquierda de la tabla y soltarlo ahí. Después hacemos lo mismo sobre el centro de la tabla en la zona de datos. La etiqueta cambia a "cuenta de Nombres" que es justo lo que nos interesa. Al acabar tendremos nuestra tabla con dos columnas, una con los nombres y al lado el número de veces que aparece cada uno.
Por último solo queda seleccionar la columna con los números y pulsar el icono ordenar (el que tiene za hacia abajo) para que nos ponga el primero el que mas veces sale, luego el segundo...
Gracias por tu inmediata respuesta; pero te comento: aunque manejo muy bien el excel, no sé manejar tablas dinámicas; existirá a caso otra forma de obtener lo que necesito pero de manera mucho más breve y sencilla???
Puedes usar la función CONTAR. SI, pero entonces tienes que ir uno por uno indicando en la condición cada nombre. Me parece mucho más engorroso y que lleva más tiempo que la tabla dinámica.
Respuesta de jrgces
1
Para que la fórmula me fuese más fácil de escribir y editar le he aplicado el nombre Datos al rango donde se encuentran los textos. La fórmula entonces es:
=INDICE(Datos;COINCIDIR(K.ESIMO.MAYOR(SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/100);FILA());SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/100);0))
Y hay que ponerla a partir de la fila 1 de cualquier columna vacía, teniendo en cuenta que:
- Se trata de una fórmula matricial, por lo que hay que introducirla pulsando mayúsculas control entrada al mismo tiempo.
- Quizás haya que sustituir los puntos y comas por comas; esto dependerá del separador de listas que esté establecido en la configuración regional del equipo
- Si hay empates en las veces que aparecen los textos, la fórmula devolverá primero el situado más arriba, luego el siguiente, etcétera.
- Cuando no queden más textos comenzará a aparecer el error #¡NUM!
=INDICE(Datos;COINCIDIR(K.ESIMO.MAYOR(SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/100);FILA());SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/100);0))
Y hay que ponerla a partir de la fila 1 de cualquier columna vacía, teniendo en cuenta que:
- Se trata de una fórmula matricial, por lo que hay que introducirla pulsando mayúsculas control entrada al mismo tiempo.
- Quizás haya que sustituir los puntos y comas por comas; esto dependerá del separador de listas que esté establecido en la configuración regional del equipo
- Si hay empates en las veces que aparecen los textos, la fórmula devolverá primero el situado más arriba, luego el siguiente, etcétera.
- Cuando no queden más textos comenzará a aparecer el error #¡NUM!
amigo, muchas gracias por tu ágil respuesta; he probado la fórmula siguiendo cuidadosamente tus indicaciones y no me ha dado resultado; por lo cual agradecería me colaboraras con lo siguiente:
- EL RANGO DE TRABAJO ES E2:E207
- Recuerda que son sólo datos textuales, no números.
- Revisé la configuración regional de mi equipo y siempre he trabajado con punto y coma, por lo cual no hay problema por ello.
- Importante: Las Celdas del Rango al igual que la celda en donde va la fórmula, ¿DEBEN ESTAR EN CATEGORÍA GENERAL, Ó TEXTO??
Agradezco tu pronta respuesta... de ello depende gran parte de mi una Encuesta que debo presentar...
muchas gracias por tu paciencia.
- EL RANGO DE TRABAJO ES E2:E207
- Recuerda que son sólo datos textuales, no números.
- Revisé la configuración regional de mi equipo y siempre he trabajado con punto y coma, por lo cual no hay problema por ello.
- Importante: Las Celdas del Rango al igual que la celda en donde va la fórmula, ¿DEBEN ESTAR EN CATEGORÍA GENERAL, Ó TEXTO??
Agradezco tu pronta respuesta... de ello depende gran parte de mi una Encuesta que debo presentar...
muchas gracias por tu paciencia.
El problema es que el rango de datos no comienza en la fila 1 sino en la 2, por lo que hay que modificar un poco la fórmula:
=INDICE(Datos;COINCIDIR(K.ESIMO.MAYOR(SI(COINCIDIR(Datos;Datos;0)=FILA(Datos)-1;CONTAR.SI(Datos;Datos)-FILA(Datos)/100);FILA());SI(COINCIDIR(Datos;Datos;0)=FILA(Datos)-1;CONTAR.SI(Datos;Datos)-FILA(Datos)/100);0))
El rango con nombre Datos debería referirse a E2:E207 para que la fórmula funcione.
=INDICE(Datos;COINCIDIR(K.ESIMO.MAYOR(SI(COINCIDIR(Datos;Datos;0)=FILA(Datos)-1;CONTAR.SI(Datos;Datos)-FILA(Datos)/100);FILA());SI(COINCIDIR(Datos;Datos;0)=FILA(Datos)-1;CONTAR.SI(Datos;Datos)-FILA(Datos)/100);0))
El rango con nombre Datos debería referirse a E2:E207 para que la fórmula funcione.