Varias veces me han preguntado cómo segmentar valores en Excel, es decir, dada una lista de valores, cómo clasificarlos según diferentes rangos. Esta tarea en Excel puede hacerse de varias maneras pero aquí os vamos a enseñar a hacerlo con las funciones COINCIDIR e INDICE.
Información inicial
Inicialmente necesitaremos el listado de valores que queremos segmentar. Este listado estará formado por cualquier valor numérico: número de elementos, cantidades de dinero… lo que sea. En la siguiente table veremos los valores de un lista que van entre 1 y 1.000.000.
Además de esta tabla necesitaremos una tabla de configuración en la que tenemos un valor asociado a una cadena de texto, el segmento al que está asociado el valor.
En la imagen anterior hemos creado cuatro segmentos que no tienen que ser proporcionales necesariamente. Y a ellos hemos asociado un segmento que es una cadena de texto en el que se forma por el valor de la filaen la que se encuentra y el siguiente valor. Es decir, el segmento va desde su valor hasta el siguiente.
Utilizar la función COINCIDIR
Lo primero que necesitaremos hacer será asociar un valor de la tabla de configuración de la columna valores a los valores de la tabla de datos que queremos segmentar. Para ello usaremos la función COINCIDIR con una pequeña variación respecto de su uso normal.
La función COINCIDIR está formada por tres argumentos como ya hemos visto en otras ocasiones. En esta ocasión, el tercer argumento llamado “tipo de coincidiencia” lo marcaremos como 1 en vez de 0 (que se usa para buscar el valor Exacto). Cuando el Tipo de Coincidiencia es igual a 1, la función COINCIDIR nos devuelve el valor más cercano (por debajo) al valor buscado.
La función que en este caso usaremos se puede ver en la siguiente imagen:
Donde la función será:
=COINCIDIR(A6;valores de la tabla de configuración;1).
En el ejemplo que vemos a continuación, el valor COINCIDIR del número 5.703 será 3, es decir, está asociándolo al valor 5.000 de la Tabla de Configuración.
Utilizar la función ÍNDICE
Después de saber a qué fila en la tabla de configuración asociaremos cada uno de los valores de la tabla de datos usaremos la función INDICE de la misma manera en la que lo hacemos en la siguiente imagen:
Es decir, la fórmula que usamos es:
=INDICE(segmentos de la tabla de configuración;COINCIDIR(A6;valores de la tabla de configuración;1))
Donde la función INDICE nos devolverá el segmento asociado a la fila que nos devuelve la función COINCIDIR, en este caso será: “Entre 5.000 y 50.000”.
Si quieres descargarte el ejemplo que hemos usado en este tutorial haz click en el siguiente enlace: Segmentar valores en Excel
Mas sobre Excel: