Ordenar datos por criterios externos arbitrarios

A continuación se mostrará una forma sencilla de ordenar una serie de datos por un criterio totalmente arbitrario, y que éste se pueda modificar según las necesidades del momento.

La idea del proceso es la siguiente:

• Crear una tabla que contenga al menos una columna los datos que se deben de ordenar, y en una segunda valores numéricos en la donde se definirá el orden.
• Cruzar la información a ordenar con la nueva tabla de manera de agregar una columna adicional con la información del orden.
• Ordenar los datos por la nueva columna.
• En caso que en el futuro se requiera modificar el orden, simplemente se cambia la numeración de la tabla donde está definido.

Solución para hojas de cálculo:

A modo de ejemplo se mostrará una lista de productos que cada uno de ellos tiene una categoría, se ordenarán los productos según un orden cualquiera que se le asignará a cada categoría. : El mismo está disponible en la siguiente dirección de Google Docs.
Para que se puedan ver las fórmulas ha quedado de libre edición, por lo que si ves algo extraño accede a la versión original (Archivo / Historial de Revisiones).
Para verlo en Microsoft Excel (español) se debe de exportar el documento como xls (Archivo / Exportar).

Los pasos son los siguientes:

1. Crear la lista donde se definirá el orden a aplicar. En el ejemplo se trata de la lista que se encuentra en la hoja Categorías. Definir el orden.
2. Crear una nueva columna en la lista de datos originales para posteriormente cruzar la información (columna Orden Externo).
3. Cruzar la información entre las dos tablas de manera de obtener el valor del orden de la segunda hoja. Esto se realizará utilizando la función BUSCARV (VLOOKUP en ingles):
   

   La formula para la busqueda es la siguiente:
   

   Versiones en español:
   

   =BUSCARV($C2; Categorías!$B$2:$D$9; 3; FALSO)
   

   Versiones en inglés:
   

   =VLOOKUP($C2; 'Categorías'!$B$2:$D$9; 3, 0; false)
   

   Siendo C la columna donde se encuentra la clave de búsqueda. En las columnas B y D de la hoja Categorias estarían la clave a buscar y dato resultante respectivamente.
   

   

4. Ordenar la tabla por la nueva columna calculada, adicional a este orden se le puede incluir otros. Para el caso del ejemplo el orden está definido por la columna Orden Externo y posteriormente Nombre Producto.

Solución para base de datos (SQL):

A nivel de base de datos la solución es más fácil, una vez creada la tabla (supongamos con los mismos nombres que la planilla de cálculo mostrada).
Simplemente se debe de hacer un Join entre ambas tablas y ordenar el resultado por el dato del orden.

SELECT Productos.Codigo, Productos.Nombre_Producto, Productos.Categoria, Categorias.Orden FROM Productos, Categorias WHERE Productos.Categoria = Categorias.Categoria ORDER BY Categorias.Orden, Productos.Nombre_Producto

Comparar o Restar horas de diferentes fechas

En varias ocasiones he visto problemas al realizar operaciones con fechas y horas, más precisamente cuando las horas son de diferentes fechas.

Por ejemplo, al restar dos horas cuando las fechas son diferentes, si intentas comparar las fechas y luego las horas restando unas y otras lo puedes hacer pero lleva un poco de lógica algo compleja.

La solución es extremadamente simple: la fecha y hora se deben de sumar generando un único dato del tipo fecha hora.

Esto se debe a que la mayoría de los sistemas para el almacenar un dato que contiene una fecha o una hora utilizan un mismo tipo de dato que contiene la fecha y hora integrada, y de manera visual por formatos se diferencian uno de otro.

Facilitará operaciones tales como:

- Calcular el tiempo transcurrido entre dos horas de igual o distintas fechas.

- Comparar dos horas de igual o distintas fechas.

- Obtener la fecha y hora máxima de un rango.

- Obtener la fecha y hora mínima de un rango.

- Obtener la fecha y hora media de un rango.

Cálculo en Microsoft Excel, Google Docs, OpenOffice.org Calc, Base de Datos:

Diferencia entre fechas (tiempo transcurrido desde la Hora Inicio de la Fecha Inicio hasta la Hora Fin de la Fecha Fin).

=(FechaFin+ HoraFin) – (FechaInicio + HoraInicio)

Comparar fechas, por ejemplo saber si la Hora de Fin es mayor a la Hora de Inicio.

=(FechaFin+ HoraFin) >= (FechaInicio + HoraInicio)

En programa que tanto las fechas como las horas se almacenan en cadenas de caracteres, a la fecha se le debe de concatenar la hora para poder compararlas.

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:

=BUSCARV(B2;RANGOS!$A$2:$C$6;3;VERDADERO)

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

=VLOOKUP(B2; RANGOS.$A$2:$C$6; 3; 1)

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:

and (valor >= rango_minimo and valor < rango_maximo)

O como un colaborador mencionó en sus comentarios:

and (valor between rango_minimo and (rango_maximo-1))

Ejemplo de reporte detallado:

select td.clave_dato, td.valor_dato, tr.nombre_rango from tabla_datos td, tabla_rangos tr where td.valor_dato >= tr.rango_minimo and td.valor_dato < tr.rango_maximo

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

select tr.nombre_rango, count(*) as cuenta_dato, sum(otro_valor) as suma_dato from tabla_datos td, tabla_rangos tr where td.valor_dato >= tr.rango_minimo and td.valor_dato < tr.rango_maximo group by tr.nombre_rango

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