Generar reportes por rangos variables

Una operación muy común al momento de recuperar datos es clasificar el resultado por rangos de valores dependiendo de un determinado criterio, ya sea para posteriormente agruparla o simplemente para saber en que categorías se encuentra.

Dentro de la Minería de Datos (datamining), podemos decir que estas operaciones están incluidas en las técnicas de Limpieza y Transformación de datos.

El proceso para identificar y agrupar los rangos es el siguiente:

1. Identificar el rango al que pertenece cada dato.

2. Agrupar por los criterios necesarios más la información del rango (paso opcional).

3. Realizar los cálculos de agregación de información necesarios (Suma, cuentas, promedios, máximos, mínimos, etc).

La forma "tradicional" de identificar el rango es hacer tantas condiciones como rangos existan, lo que lleva a un reporte complicado que depende de la cantidad de rangos, y ni hablar cuando hay que hacer cambios.

Como alternativa podemos realizar dicha operación utilizando una tabla auxiliar (lista) donde se definen los rangos, y luego mediante una misma consulta preguntar a que rango de dicha tabla pertenece cada valor.

Para cada rango se debe de realizar la siguiente pregunta:

valor_a_buscar "mayor o igual" rango_minimo Y valor_a_buscar "menor" rango_minimo

Cuando el resultado es verdadero se debe a que lo encontramos. Es muy importante no dejar huecos entre los rangos ni superponerlos para evitar perder información ni duplicarla respectivamente.

Solución para hojas de cálculo:

1. Se crea una nueva hoja, por ejemplo llamada RANGOS, donde tendremos tres columnas: A tendrá el rango mínimo, B el rango máximo, y C el nombre del rango.

2. Para buscar los rangos se debe de utilizar la BUSCARV (VLOOKUP en ingles), utilizando la característica del cuarto parámetro la cual habilita la búsqueda en rangos. Buscará el dato en la lista, de no encontrarlo nos dará el dato correspondiente al menor valor superior al que estamos buscando, aquí es muy importante tener ordenada la lista ya que de lo contrario no nos funcionará.

En la hoja donde tenemos nuestra información, se crea una nueva columna llamada Rango donde se ingresará la siguiente formula:

2.a. Formula en Microsoft Excel en Español:


2.b. Fórmula en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:


3. En caso de necesitar agrupar la información, una vez identificado el rango, se crea una tabla dinámica que incluya esta nueva columna y se realiza el reporte. Las operaciones de agregación son provistas por la tabla dinámica (en caso de utilizar Microsoft Excel).

Hice un ejemplo con una lista de países agrupando según la información de su población y superficie, aquí queda disponible un documento de Google Docs

Para que se puedan ver las fórmulas ha quedado de libre edición, por lo que si ven algo extraño accede a la versión original (Archivo / Historial de Revisiones).

Solución para manejadores de base de datos:

1. Se crea la tabla para que contenga los rangos como mínimo con los campos que de la tabla de rangos.

2. Definir la información de los rangos ingresando los datos en la nueva tabla.

3. Al reporte (consulta) que se tenga simplemente se le debe de agregar la tabla de rangos y unirla (join) mediante:


O como un colaborador mencionó en sus comentarios:


Ejemplo de reporte detallado:


4. En caso de requerir las agrupaciones, se deben de agrupar los datos y utilizar las operaciones de agregación necesarias.


Como siempre si desean mayor información o aclarar algún tema no duden en comunicarse.

3 comentarios:

Anónimo dijo...

Qué tal! tengo una interrogante, es posible usar la instrucción BETWEEN para utilizar rangos en SQL???

ElData dijo...

Primero que nada me acabo de dar cuenta que al publicar este post, se borraron partes del código. En particular lo que respecta a menor a valor máximo.

Se puede utilizar perfectamente la instrucción BETWEEN (también nos ayuda para evitar el problema de la edición).

Valor_a_buscar BETWEEN rango_minimo and rango_maximo

Solo hay que tener en cuenta como se definen los rangos, el máximo de un rango no debe de ser igual al mínimo del siguiente, ya que si lo son se estará duplicando la información.

SadlyMistaken dijo...

Oh virgen santa! No sabes lo contento que estoy de haber encontrado este post, GRACIAS GRACIAS MIIIIIL GRACIAS...

No sólo por explicarlo en Excel, si no tb en CALC... aunque no sé aún para que sirve el TRUE ese.. pero bueno... ya me enteraré... ERES UN SOL, MIL GRACIAS DENUEVO.