Los 5 métodos para hallar los textos más repetidos en un rango

Es común encontrar en Internet fórmulas capaces de hallar los 5 o los 10 valores más altos de un rango e incluso los que más se repiten siempre que se trate de números, pero cuando lo que necesitas es una lista de los textos más repetidos, ya no encuentras tanta información.

En el tutorial de hoy hablaré de 5 métodos diferentes para mostrar una matriz de los textos que más se repiten en un rango de celdas:

• Tres fórmulas: una de ellas para versiones de Excel que no admiten matrices dinámicas.
• Un método con tablas dinámicas.
• Otro con Power Query.

Para los ejemplos voy a trabajar con la siguiente base de datos. Tiene 3.337 inmuebles vendidos entre el 1 de enero de 2005 y el 31 de diciembre de 2021. La inmobiliaria cuenta con 7 comerciales que cubren las 4 provincias de Galicia.

La empresa quiere conocer los dos vendedores que mayor número de ventas han hecho (ojo, no mayor volumen de venta) y los 3 tipos de inmuebles más vendidos entre 2005 y 2021.

Fórmula para versiones diferentes a 365 y 2021

No todo el mundo tiene Office 365 instalado en sus ordenadores, por tanto, estuve buscando la forma de hallar los dos vendedores con más inmuebles vendidos sin tener que recurrir a las matrices dinámicas.

Observa la fórmula:

=INDICE(I$2:I$3338;MODA(SI(1-CONTAR.SI(L3;I$2:I$3338);COINCIDIR(I$2:I$3338;I$2:I$3338;))))

= INDICE(
    I$2:I$3338;
    MODA(SI(
             1
           - CONTAR.SI(
               L3;
               I$2:I$3338);
           COINCIDIR(
             I$2:I$3338;
             I$2:I$3338;
             ))))

Aquí encuentras cuatro funciones:

• COINCIDIR: usada para asignar un valor a los textos únicos. Es decir, si tienes los textos “a”, “b”, ”c”, ”a”, ”c”, la función devuelve el resultado 1, 2, 3, 1, 3, asignando el 1 a la “a”, el 2 a la “b”, etc.
• CONTAR.SI: utilizada para crear una lista con valores booleanos. Cuando se encuentra el primer elemento de la lista, devuelve 1. En los demás casos devuelve cero. Cuando se arrastra la fórmula hacia abajo, cuenta las repeticiones del segundo elemento de la lista, y así sucesivamente.
• MODA: devuelve la posición del valor que más se repite en el rango. A medida que se arrastra la fórmula hacia abajo va devolviendo el segundo valor más repetido, el tercero, etc.
• INDICE: es la función que devuelve el valor que se encuentra en la posición.

En este método no existe un número de resultados preestablecido. Tendrás que arrastrar hacia abajo la fórmula según los que quieras mostrar.

Fórmula para Excel 365 y Excel 2021

Como en estas versiones de Excel ya es posible utilizar matrices dinámicas, voy a aprovecharme de ello. Echa un vistazo a la siguiente fórmula:

=INDICE(UNICOS(ORDENARPOR(I1:I3338;CONTAR.SI(I1:I3338;I1:I3338);-1));{1;2})

= INDICE(
    UNICOS(ORDENARPOR(
             I1:I3338;
             CONTAR.SI(
               I1:I3338;
               I1:I3338);
             -1));
    {1;2})

Es una fórmula más simple y rápida que la anterior.

• CONTAR.SI: Con ella obtengo una matriz con el número de veces que se repite cada elemento. Aunque el resultado se devuelve ordenado de mayor a menor, esta ordenación no servirá para conocer el nombre del vendedor.
• ORDENARPOR: Ordena los valores de la columna de vendedores. La matriz devuelta contiene el mismo número de registros que el rango original, estén repetidos o no.
• UNICOS: Esta lista de nombres repetidos se reduce a valores únicos. Sigue devolviendo todos los valores de la columna.
• INDICE: Selecciono el primer y el segundo valor de la lista anterior.

Fórmula para Excel 365 y Excel 2021 Beta

Los más afortunados tenemos disponibles algunas funciones antes que el resto de usuarios debido a que estamos inscritos en el programa Insiders de Office. Si es tu caso podrás usar la siguiente fórmula:

=LET(r;I2:I3338;TOMAR(UNICOS(ORDENARPOR(r;CONTAR.SI(r;r);-1));2))

= LET(
    r;
    I2:I3338;
    TOMAR(
      UNICOS(ORDENARPOR(
               r;
               CONTAR.SI(
                 r;
                 r);
               -1));
      2))

Como puedes comprobar he simplificado la fórmula encerrándola en la función LET. Tienes más información acerca de LET en la ayuda de Office.

Con una tabla dinámica

Si no has podido implementar las fórmulas anteriores o prefieres un método más sencillo, te gustará el método de las tablas dinámicas. Te explico los pasos que debes seguir según los datos del ejemplo:

1. Haz clic en cualquiera de las celdas de la tabla.
2. Ve a la ficha Insertar y haz clic en Tabla dinámica.

En el cuadro aparece relleno el rango que ocupa la información que vas a usar. Asegúrate de señalar el botón Nueva hoja de cálculo. Haz clic en Aceptar.

3. Cuando aparezcan la estructura de la tabla dinámica y los campos, arrastra el campo Vendedor hasta el área Filas.

4. Vuelve a arrastrar el campo Vendedor al área Valores. Este paso crea una nueva columna al lado dela columna del vendedor con el número de veces que se repite cada elemento en el rango.
5. La tabla dinámica muestra ahora todos los vendedores, pero solo necesitas los dos con más ventas.

6. Para finalizar debes aplicar el filtro que deje en la tabla únicamente los dos valores más altos de la columna de la derecha (recuerda que son número de ventas, no importes). Haz clic en el botón de la columna Etiqueta de fila y selecciona Filtros de valor > Diez mejores.

7. En el cuadro de diálogo que aparece solo debes cambiar el número de resultados que quieres visualizar.

Los controles que ves en el cuadro anterior indican que se mostrarán los dos elementos de la tabla más altos que haya en la columna Cuenta de Vendedor. Haz clic en Aceptar.

Aquí tienes el resultado.

Con Power Query

El último método que te enseño para conocer los vendedores que más inmuebles vendieron es con Power Query. En este caso transformaré los datos agrupándolos.

1. Con una celda de los datos originales seleccionada haz clic en la ficha Datos y a continuación en De una tabla o rango (puede que en tu versión de Excel se vea diferente).

2. Confirma el cuadro de diálogo con la información que aparece. Casi siempre es correcta:

3. Ya en la ventana de Power Query haz clic en la ficha Inicio y a continuación en el botón Agrupar por.

4. En el cuadro de diálogo Agrupar por, selecciona la columna Vendedor. A continuación dale un nombre a la nueva columna que se creará (por ejemplo Inmuebles vendidos) y asegúrate de seleccionar la operación Recuento de filas. Una vez configurado todo haz clic en Aceptar.

Power Query hace su magia y…. Voilà.

5. La tabla anterior parece estar ordenada, pero para asegurarte de que siempre se ordenará, no está de más agregar un paso para esta operación. Haz clic en la flecha del encabezado de la columna Inmuebles vendidos y a continuación haz clic en Orden descendente.

6. El último paso de la transformación es eliminar todas las filas excepto las dos primeras (las que tienen los valores más altos). Desde la ficha Inicio haz clic en el botón Conservar filas y a continuación, en Conservar filas superiores.

¿Cuántas filas deseas conservar? ¿dos, verdad?, escríbelo en el cuadro y acepta.

El trabajo en Power Query ha finalizado, ahora solo hay que cargarlo en Excel.

7. Haz clic en Cargar y cerrar. En un segundo volverás a Excel y se mostrará el resultado:

Al principio comenté que la empresa quería conocer, además de los dos vendedores con mayor número de ventas, los tres tipos de inmuebles vendidos en el periodo. ¿Te atreves a calcularlo? Te dejo el archivo para que lo hagas.

Resumen

En esta publicación te he mostrado cinco formas de crear una lista con las n palabras que más se repiten (el nombre de los vendedores). Además de estos, también podrías utilizar filtros avanzados, una macro o una función personalizada escrita con VBA. Casi siempre encontrarás dos o más maneras de hacer la misma tarea.