Porqué usar colores para codificar información en una hoja de cálculo es mala idea (parte 2)

Otra de las razones para no usar la codificación mediante colores de información en una hoja de cálculo es que he filtrado por colores es propio de Microsoft Excel.

Por lo tanto, si pasamos nuestra hoja con colores, a un usuario que use otro programa de hoja de cálculos, cómo puede ser LibreOffice Calc, este no podrá filtrar la información que le estamos mostrando y tendrá que limitarse a ir pasando las celdas para mirar los datos.

Cómo podemos ver en Microsoft Excel sí que se muestra la opción de filtrar por colores.

Seleccionando un color podremos ver solo las filas correspondientes de ese mismo color.

Una situación todavía peor sería que enviaremos nuestra hoja de cálculo ya filtrada.

Dado que LibreOffice Calc, no tiene filtrado por colores se da la peculiaridad de que tampoco permite eliminar el filtrado, que no permitiría acceder al resto de los datos de la hoja.

En la siguiente imagen os muestro una hoja de cálculo filtrada por el color naranja en Microsoft Excel y luego abierta en LibreOffice Calc.

Porqué usar colores para codificar información en una hoja de cálculo es mala idea (parte 1)

Ayer os traía el ejemplo de una hoja de cálculo que me habían hecho llegar en la que se codifica a el año de edición de los libros mediante una leyenda de colores, como si fuese un gráfico.

Aparte de la enorme cantidad de tiempo que puede ser precisa para codificar un listado de esa manera, sin cometer errores, vamos a analizar qué otras cosas pueden ir mal.

Por ejemplo, no todas las hojas de cálculo ni formatos de fichero soportan la misma cantidad de colores.

Comencemos con el mismo ejemplo.

A día de hoy sigo recibiendo gran cantidad de ficheros en formato Microsoft Excel 2003, los de la extensión XLS.

Una de las características de este formato, es que solo soportaba 64 colores.

Por lo tanto, si alguien guarda nuestro fichero en ese formato, verá aparecer la siguiente imagen.

En Cristiano, esta imagen viene diciendo que se reducirá la paleta de colores, usando solamente los que soporta el formato XLS.

Dependiendo de los colores elegidos, puede:

1 - No pasar nada.

2 - Que los colores cambien por otros.

3 - Que los colores más parecidos se fusionen.

En los dos primeros casos no es demasiado problema, pero el tercero produce una perdida de información.

Filtrado por gran cantidad de colores en Microsoft Excel

Hoy he recibido una hoja de cálculo, que seguramente fué realizada con gran esfuerzo y buena intención, pero con un resultado algo peligroso.

De trataba de una hoja de cálculo con información de libros.

El año de edición se había codificado usando un color de fondo distinto para cada año.

La información del año se aportaba a modo de leyenda, en unas columnas al lado.

A continuación una simulación de la mencionada hoja.

Cuando se aplica un filtro, solo se muestran, por defecto, los primeros cinco colores que encuentra.

En caso de que encuentre más colores, se muestra el texto "Más colores de celda..."

Si pulsamos esa opción se nos muestra el resto de los colores.

Pudiendo de esta forma filtrar por cualquier color.

Sin embargo este método para codificar información mediante el color de las celdas no es aconsejable, ya que además de incómodo de crear es difícil de comprender.

fds

fd

df

df

dff

Promover encabezados en Power Query, para Microsoft Excel y Power BI

El segundo paso que se suele realizar al importar datos con Power Query, tanto para Microsoft Excel como para Power BI, es promover la primera fila como encabezados.

Esto hace que los datos de la primera fila, se traten como nombres de columna y no como datos.

De no hacerlo, todas las columnas serían identificadas como texto.

En lenguaje M la instrucción para realizar este proceso es Table.PromoteHeaders.

Ejemplo para Microsoft Excel:

= Table.PromoteHeaders(Origen)

Ejemplo para Power BI:

= Table.PromoteHeaders(Origen, [PromoteAllScalars=true])

En este caso, para ver esta instrucción, deberemos seleccionar el paso denominado "Encabezados promovidos" que, usualmente, es el segundo paso de la importación.

En la imagen el segundo paso para Excel.

Y la instrucción correspondiente.

El segundo paso para Power BI.

Y la instrucción.

Aunque difieren ligeramente ambas instrucciones, es posible usar cualquiera de las dos en ambos casos.

Cambio de fichero cargado mediante lenguaje M en Microsoft Power BI

Ayer veíamos la instrucción para cargar un fichero CSV con lenguaje M en Power BI.

En caso de tener que aplicar el mismo proceso a un fichero con un nombre distinto o ubicado en otra carpeta, es posible modificar la carga del fichero con solo modificar la instrucción de carga del fichero.

Para ellos solo es preciso modificar la ruta del fichero, el resto de los cambios se aplicaran sobre los datos del nuevo fichero.

En este caso solo hemos modificado el texto resaltado en rojo.

Fichero original (todo.csv):

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])

Fichero secundario (todoModificado.csv):

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todoModificado.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])bi

El proceso es sencillo.

Primero abrimos el archivo de Power BI.

Luego debemos editar la consulta desde el menú contextual del botón derecho.

La instrucción que se nos muestra se corresponde al paso que tengamos seleccionado en Power BI, que por defecto, es el último realizado.

Seleccionamos el primer paso, para que se nos muestre la instrucción en lenguaje M correspondiente a la carga del fichero CSV.

Antes de la modificación vemos los datos del primer CSV.

Modificamos el nombre del fichero CSV e, importante, pulsamos la tecla intro, para aplicar el cambio.

Inmediatamente se cargan los.datos del nuevo fichero.

Es un truco útil cuando se pretende aprovechar parte de los pasos de un fichero.

Carga csv en power query para Microsoft Excel y Power Bi

Si una herramienta es interesante en el mundillo del análisis de datos esta es Power Query y su lenguaje M.

Esto se debe principalmente a que es una herramienta común tanto a Microsoft Excel como a Power BI.

Hoy os traigo la instrucción que generan Excel y Power BI para cargar el mismo fichero CSV.

Microsoft Excel:

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";",Encoding=1252])

Power BI:

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])

Como podéis ver las diferencias son mínimas, tanto que cualquiera de los dos códigos funcionaría sin problema en la otra herramienta.

En Excel se hizo la importación desde "Obtener y transformar".

La fórmula se encuentra en el primer paso.

Y es exactamente la que os mostraba.

En Power BI, cargamos el fichero CSV desde "Obtener datos".

En la siguiente ventana pulsamos directamente en el botón "Cargar"

Y vemos que, también en el primer paso, se muestra la misma fórmula en lenguaje M.

En este caso la única diferencia, es que Power BI, tiene resaltado de color, como podéis ver en la captura.

ELT vs ETL en Microsoft Excel

En informática a la hora de cargar y tratar datos existen dos conceptos ETL y ELT.

ETL (Extract, Transform, Load)

ELT (Extract. Load, Transform)

En Microsoft Excel, lo más habitual es cargar todos los datos y luego filtrar los datos que se desean, lo que corresponde al proceso ELT (Extract. Load, Transform).

Para ello los cargamos desde "Obtener datos externos"

Este proceso tiene como inconveniente que se cargan datos que no interesan en el análisis, y que luego hay que filtrar o si se realizan cálculos, tenerlos en cuenta para no incluirlos en los cálculos.

Esto aumenta el tiempo de cálculo y reduce el número de filas de interés que podemos tratar.

En el peor de los casos, no podremos cargar todos los datos, ya que también se tendrán en cuenta datos que no interesan, pero que cuentan entre el millón de líneas que se pueden cargar en Microsoft Excel.

También se puede aplicar el método ETL (Extract, Transform, Load), mediante el uso del componente Power Query, que nos permite aplicar filtros y transformaciones a los datos antes de cargarlos.

Para ello se cargan los datos desde el grupo "Obtener y transformar".

Usando este método podremos aplicar las transformaciones a los datos originales, e importar un máximo de un millón de filas de datos de interés, tras haber eliminado los datos no necearios.

El método a utilizar dependerá de la cantidad de elementos del conjunto de dato original, y de lo importante que sea el tiempo de proceso de las fórmulas de la hoja de cálculo.

Informes en Solver

Cuando se usa la herramienta Solver de Microsoft Excel, una de las cosas que se puede hechar de menos es entender que cálculos ha realizado la herramienta.

Si Solver para Microsoft Excel encuentra una solución, o si se para el proceso mediante la tecla ESC, es posible seleccionar unos informes.

Se pueden seleccionar, dependiendo del método de resolución, dos informes.

Reponder o Población.

El de población tiene el siguiente aspecto.

Mientras que el de respuestas tiene el que se muestra a continuación. 

El informe de respuestas está disponible para todos los métodos de cálculo.

Factura de un importe exacto con solver en Microsoft Excel

Un  caso que me han pedido resolver hace tiempo con Microsoft Excel, es a partir de un listado de productos conseguir generar un lote por un importe exacto.

En el ejemplo, tenemos que generar un lote de 500,00 € en el que es obligatorio que se incluya al menos un ejemplar de cada uno de los diez primeros artículos.

La cantidad máxima de ejemplares por título es de 2 ejemplares.

En el video podéis ver el proceso completo.

En la columna C hemos indicado el número mínimo de ejemplares exigido.

En la columna E, será donde solver introduzca datos de partida.

Y en la columna D, sumaremos las dos columnas anteriores, obteniendo el total de ejemplares a incluir en el listado.

La columna F calculará el importe por línea, que se acumulará en la celda I1.

Con los datos de partida, el listado asciente a 169,54 €, ahora necesitamos seleccionar artículos por el resto del importe.

La configuración de Solver es la que se muestra, indicando el valor exacto para la celda I1, cambiando las celdas de la columna E, a la que también aplicaremos como restricción que los valores sean enteros.

La columna D tendrá dos restricciones, mayor o igual que cero y menor o igual que el número de artículos máximo permitido de la misma referencia.

Para este ejemplo el mejor método de resolución es Simplex LP.

Encontrar un valor aproximado, es relativamente sencillo hacerlo a mano, pero encontrar el valor exacto es prácticamente imposible sin usar Solver.

Depósito prismático con la menor cantidad de material calculado mediante Solver

En este caso calcularemos un depósito prismático, con lados rectangurales.

Los criterios son tres:

1 - Una capacidad mínima de 1000 litros.

2 - El uso de la mínima cantidad de material

3 - Para facilitar las mediciones trabajaremos en centímetros

En el vídeo de Youtube podéis ver el proceso completo.

En este caso, partiendo de las dimensiones de Alto, Ancho y Fondo, calculamos, tanto el volumen del depósito, como la superficie, lo que nos dará la cantidad de material necesario.

En este caso vamos a intentar crear el depósito que menos material precise para su construcción.

La fórmulas usadas son la siguientes:

En solver, indicamos que deseamos que la casilla B9, tenga el valor mínimo, esta casilla muestra el total de material necesario.

Y en el campo "cambiando las celadas de variables", seleccionaremos las casillas correspondiente a las medida de Alto, Ancho y Fondo.

En cuanto a las restricciones, indicaremos que el volumen sea mayor o igual a 1000.

En esta ocasión no indicaremos que deseamos el menor posible, porque al usar la mínima cantidad de material, también obtendremos el mínimo volumen.

La otra restricción hará que las medidas Alto, Ancho y Fondo sean en valores enteros.

La configuración de solver queda como se muestra.

En un primer intento vamos a lanzar solver con el método "Siplex LP"

Vemos que en esta ocasión, solver nos indica que no es posible encontrar la solución con este método porque no se cumplen las condiciones de linealidad.

Cambiamos el método de resolución a "GRG Nonlinear" y ejecutamos.

En este caso si que obtenemos un resultado.

El resultado óptimo para este caso es la fabricación de un depósito cubico con todos los lados iguales.

Este ejemplo es solo una aproximación, pudiendo mejorare con muchos más criterios, como puede ser, usar los precios en lugar de la cantidad de material, incluyendo cortes, mano de obra de soldadura y desperdicios, así como intentar usar chapas enteras para minimizar los desperdicios de material.

Solver métodos de resolución

La herramienta de Microsoft Excel, Solver dispone de tres métodos de resolución de problemas.

Es posible cambiar entre ellos en la sección "Método de resolución" seleccionando el que mejor se adapte mediante el cuadro desplegable.

Las opciones son:

  - GRG Nonlinear

  - Simplex LP

  - Evolutionary

En la misma pantalla se explica cuando usar cada uno de ellos.

A pesar de que el método "GRG Nonlinear" es el método por defecto, para la mayoría de los problemas es suficiente y más rápido usar el "Simplex LP"

A la derecha del desplegable encontramos un botón que nos lleva a una pantalla de configuración de los métodos de resolución.

La primera pestaña es común a todos lo métodos.

La segunda es para el método "GRG Nonlinear"

Y la tercera para el método "Evolutionary"

El mejor enfoque me suele resultar comenzar por el método "Simlex LP", y si con  este no se encuentra el resultado deseado, pasar a uno de los otros dos.