Efectos especiales con bordes en Excel

En el trámite de preparar una nota sobre el uso de estilos en Excel, tema importante pero ampliamente ignorado por la mayoría de los usuarios, encontré como lograr un efecto que he visto en varios cuadernos de Excel pero nunca supe cómo hacerlo.

Me refiero a las celdas que aparecen como alto o bajo relieves. Una imagen vale más que cien palabras:

Para lograr este efecto todo lo que tenemos que hacer es poner un fondo gris a un área de celdas, asegurándonos de tener una columna de más a cada lado de la celda y una fila de más por encima y por debajo de las celdas en cuestión.

El fondo del área A1:C5 es gris 25%

La línea de los bordes izquierdo y superior son de color blanco y el color de los bordes inferior y derecho es gris 50%



Para lograr el efecto de bajo relieve sencillamente volvemos blanco el borde inferior y gris 50% el superior.

Aplicando esta técnica podemos crear una tabla como esta:




Debido al fondo gris del rango, las líneas de división se han vuelto invisibles. Si necesitamos agregar líneas divisorias podemos usar el estilo de línea más “liviano” (el primero que aparece de la lista de estilo en el Formato de Celdas y darle el color gris 25%



Estos efectos pueden conmover al más implacable de los jefes!

Technorati Tags: MS Excel

Tipos de usuarios de Excel

El título de esta entrada estaba destinado a ser “¿Qué tipo de usuario de Excel sos?”. El “sos” expresa toda mi argentinidad, pero me parecía un tanto discriminatorio para la mayoría de mis lectores, que no son argentinos.

De acuerdo a las estadísticas que me provee Statcounter, la mayoría de mis lectores son españoles, mexicanos y venezolanos. Así que consideré usar “¿Qué tipo de usuario de Excel eres?”. Pero no podía liberarme de cierta sensación de hipocresía, así que concluí con un título “neutro” como el que encabeza esta nota.

Empecemos por decir que todo usuario de Excel es de hecho un programador (me niego rotundamente a usar “desarrollador” como traducción de “developer”).

De hecho Excel es mucho más que simplemente una hoja de cálculos. En esto, y muchas cosas más, coincido con Stephen Bullen, Rob Bovey y John Green que en su Excelente libro “Professional Excel Development” consideran a Excel una plataforma para el desarrollo de aplicaciones. En la introducción del libro, los autores clasifican los usuarios de Excel en cinco distintos tipos:

• El usuario básico (basic Excel user)

• El usuario avanzado (Excel power user)

• El programador de Vba (Vba developer)

• El programador de Excel (Excel developer)

• El programador profesional de Excel (professional Excel developer)

Supongo que la mayoría de mis lectores caen dentro de las dos primeras categorías.
Dentro de ellas es posible definir sub categorías. Pero antes de ocuparnos de ese tema es interesante ver cómo definen Bullen, Bovey y Green las diferencias entre las últimas tres categorías.

Para ellos el programador de Vba se caracteriza por el uso intensivo y aún exagerado de código Vba en sus cuadernos Excel. Este usuario tiene buenos conocimientos, aunque no avanzados, de Vba y cree que las macros son la mejor solución para todo problema. Por lo general no conocen suficientemente las posibilidades de Excel lo que los lleva a preferir soluciones de Vba.

El programador de Excel, por lo contrario, usa intensivamente las funcionalidades de Excel y las potencia usando Vba a discreción. Estos usuarios son capaces de desarrollar modelos complejos y a la vez eficientes basándose principalmente en Excel , pero llegan a su límite cuando se trata de usar otras aplicaciones o lenguajes junto con Excel.

El programador profesional desarrolla sus modelos basándose principalmente en Excel, pero sabe combinarlo con otras aplicaciones y lenguajes.

Volviendo a las dos primeras categorías es posible, como señalaba más arriba, definir sub categorías.

El primer nivel de usuario básico, el principiante, es aquel que conoce los rudimentos de Excel y las funciones más sencillas, como SUMA, PROMEDIO, CONTAR, etc. Por lo general tiende a crear hojas que replican formularios que llevaba en forma manual. El énfasis está puesto en el formato de la hoja más que en la funcionalidad.

El segundo nivel de usuario básico, el intermedio, conoce y comprende el uso de funciones de búsqueda como BUSCARV, INDICE y COINCIDIR. Conoce y usa Autofiltro y esto lo lleva a entender la necesidad de organizar los datos en forma tabular (listas en términos de Excel 2003, tablas en términos de Excel 2007).

El primer nivel de usuario avanzado usa Excel tanto para crear modelos de cálculo como para administrar datos. Usa funcionalidades avanzadas como nombres (en especial para rangos dinámicos), Filtro Avanzado, Validación de Datos y Formato Condicional. Usa funciones como DESREF y SUMAPRODUCTO.

EL segundo nivel de usuario avanzado, el súper usuario, conoce y usa para sus modelos funciones matriciales y tablas dinámicas y, lo más importante, comprende cuando deben ser usadas. Además tiene ya suficiente conocimientos de Vba para agregar macros y tal vez desarrollar funciones definidas por el usuario (UDF) sencillas.

En resumen, las líneas demarcadoras entre las categorías de usuarios de Excel son:

Básico ----funciones de búsqueda--->Intermedio--->DESREF, SUMAPRODUCTO--->Avanzado--->tablas dinámicas--->Súper Usuario.

¿Qué criterios agregarían o cambiarían? ¿Qué tipo de usuario se consideran ustedes?

Technorati Tags: MS Excel

Buenas y malas prácticas en Excel – colores como datos y número de hojas en un cuaderno.

Hay otros dos puntos en la discusión propuesta por The Universe Divided que merecen ser examinados: el uso de colores como datos (o mejor dicho, meta-datos) y la cantidad de hojas empleadas en un cuaderno.

Excel no viene provisto con fórmulas para realizar cálculos basados en el color de fondo de la celda (o el color de la fuente). Sin embargo a lo largo de los años que vengo ayudando a colegas a desarrollar modelos he visto muchos usuarios que usan en forma intensiva el color de fondo de la celda para señalar un meta-dato. Por ejemplo, esta lista con trabajadores de tres distintos niveles

A pesar que se ha agregado una referencia para indicar al usuario cuál es el nivel del trabajador de acuerdo al color del fondo, éste meta-dato es absolutamente inútil. Por ejemplo, no podemos usar CONTAR.SI para saber cuántos trabajadores de cada nivel hay. En este blog hemos mostrados dos soluciones para efectuar operaciones basándonos en colores, usando funciones XLM y usando funciones UDF (desarrolladas por el usuario – macros). Pero éstas no son soluciones ideales y si estamos diseñando un modelo desde el principio la mejor práctica es evitar el uso de colores como meta-datos.

La otra cuestión es la del número de hojas en un cuaderno. Una práctica común en especial en los usuarios poco experimentados es dividir los datos en hojas de acuerdo a algún criterio aparentemente lógico. Por ejemplo, creamos un modelo para registrar las ventas de una compañía que tiene varios departamentos o líneas de producto, parece lógico crear una hoja para cada departamento o para cada mes (o año).

Esta práctica no conlleva ninguna ventaja o beneficio y sólo hace que los sumarios y los cálculos sean más difíciles de realizar. Para demostrarlo supongamos que tenemos los datos de ventas por mes de una compañía con 20 departamentos. Queremos llevar todos los datos en un cuaderno con una hoja donde ponemos una tabla dinámica. La tabla dinámica nos permite analizar los datos con eficiencia y facilidad.

Una alternativa es crear una hoja para cada mes. Nuestro cuaderno tendrá 13 hojas, una para cada mes y una para contener la tabla dinámica. Como los datos no están en una única lista (o tabla) usamos la opción Rangos de consolidación múltiples. Esto nos obliga a agregar una por una cada hoja al construir la tabla y además los meses no son identificados como campos. Las hojas de nuestro cuaderno se ven así

La tabla dinámica resultante se ve así



Ahora tendremos que cambiar los nombres Elemento 1, Elemento 2, etc. , por los meses correspondientes (teniendo previamente que identificar que elemento corresponde a que mes).

Cada hoja de este cuaderno, sin la hoja Reporte, tiene 42 celdas, en total 504 celdas de datos y su peso es 78.5KB.

La alternativa recomendable es usar una única hoja para los datos. Esta hoja tiene tres campos: Departamento, Mes y Ventas

La tabla dinámica se crea simplemente y se ve así



La hoja datos tiene 723 celdas de datos y el cuaderno pesa 41.5KB, es decir, cerca de la mitad del Multihojas.xls.

La conclusión no es que bajo cualquier circunstancia hay que limitarse a una única hoja para contener los datos. Pero cuando se trate de analizar datos, dado que no hay ninguna herramienta que supere a las tablas dinámicas en eficiencia, la práctica recomendada es poner todos los datos en una única lista.

Otra ventaja, en especial para los usuarios de Excel 2007, es que una única lista permite el uso de tablas (listas en Excel 2003 y anteriores), como ya hemos mencionado en alguna nota anterior.

Technorati Tags: MS Excel

Buenas y malas prácticas en Excel – cuadros, tablas y listas.

El primer punto en la nota sobre malas prácticas en Excel de The Universe Divided, que mencioné en la nota de ayer, es usar cuadros (“using tables” en el original). Me he tomado la libertad de traducir “tables” como “cuadros” (y no tablas como pareciera que tiene que ser) por cierta confusión que existe entre los usuarios de Excel respecto a los términos tablas y listas. En la nota original el autor usar el término “table” como una matriz organizada de datos que se caracteriza por tener encabezamientos para las columnas y para las filas, como en este ejemplo:

A este tipo de matriz organizada de datos la llamaremos “cuadro”, para diferenciarla de listas y tablas.

El uso de cuadros es una mala práctica, por lo menos por dos motivos:

# - Esta forma de organizar datos hace que sea prácticamente imposible usar tablas dinámicas para analizar datos. Y si no podemos usar tablas dinámicas, estamos de hecho renunciando a una de las herramientas más poderosas de Excel. Para poder usar tablas dinámicas eficientemente tenemos que organizar los datos como en una tabla de una base de datos: campos (columnas) y registros (filas de datos). Por ejemplo, si queremos usar los datos del cuadro/matriz para construir una tabla dinámica, esto es lo que sucede

La columna A se convierte en el campo Ventas cuyos valores son los nombres de los meses del año (??!!).

# - No podemos convertir el rango de datos en una lista (Excel 2003) o tabla (Excel 2007). Nuevamente estaríamos renunciando al uso de una excelente herramienta de Excel. Por ejemplo, si aplicamos Datos—Listas, esto es lo que obtenemos Como en el caso de la tabla dinámica, podemos ver que el nombre del cuadro (Ventas) es interpretado como el encabezamiento de un campo, es decir, como parte de los datos.

Y volviendo sobre el tema de la confusión entre listas y tablas, digamos que en términos de Excel es lo mismo. Hasta la versión 2007, la funcionalidad se llama Listas (Datos—Lista). En Excel 2007 esta funcionalidad ha pasado a llamarse Tablas.

Buenas y malas prácticas en Excel – Combinar celdas.

Uno de los blogs que leo con regularidad, Daily Dose of Excel, cita una entrada publicada en The Universe Divided sobre malas prácticas en Excel.

El tema no sólo es interesante e importante sino también controvertido. Suficiente con leer los comentarios en ambas notas para comprobarlo. Uno de los temas que no aparecen en la nota original pero que varios lectores mencionan en los comentarios es el de combinar y centrar celdas.

Según los diseñadores de Excel esta funcionalidad es tan importante que por defecto tiene un icono en la barra de herramientas de Formato

Veamos este ejemplo




Si queremos que el texto de la celda A1 sirva de encabezamiento de las columnas A hasta D, podemos seleccionar el rango y apretar el icono de combinar y centrar




Una mejor alternativa es usar Formato de Celdas-Alineación-Horizontal-Centrar en selección








Aparentemente el resultado con ambos métodos es el mismo. En la fila 1 hemos usado Combinar y centrar y en la fila 2 Centrar en selección. La diferencia es que con el primer método Excel borra el contenido de las celdas que se encuentran a la derecha de la primer celda del rango. De hecho, las celdas B1, C1 y D1 dejan de “existir”. En cambio, al usar Centrar en selección no alteramos la estructura de la hoja.

Entre los motivos por los cuales considero que Combinar y Centrar es una mala práctica en Excel se cuentan:

# - borrar datos inadvertidamente;

# - problemas al tratar de usar los datos en una tabla dinámica;

# - problemas que surgen al tratar de ordenar datos;

# - problemas que surgen al copiar dado que el pegado normal copia también el formato;

# - dificultades al escribir código de Vba

Mi recomendación usar Centrar en la selección y evitar totalmente usar Combinar y centrar.

Technorati Tags: MS Excel

Cronómetro para competencias con Excel.

Dado que Excel nos permite hacer cálculos con tiempo, la tentación de crear un cronómetro para medir tiempos en competencias es muy grande. Y efectivamente, se puede hacer, pero hay que tomar en cuenta algunas desventajas y defectos potenciales de crear un cronómetro con Excel. Sin entrar en detalles técnicos podemos decir que la principal desventaja es que Excel no fue creado para medir tiempos de competencias y por lo tanto no podemos garantizar la precisión de la medición.

Dicho esto, mostraremos en esta nota cómo construir con Excel un cronómetro par competencias.

Empezamos por crear una hoja donde registraremos los competidores y los tiempos.





En la fila 6 hemos puesto tres botones a los cuales le asociaremos macros, cuyo cometido explicaré más adelante.

En las files 8-9 usamos botones en lugar de texto en celdas para facilitar el código de las macros. Estos botones no están asociados a ninguna macro y sólo funcionan como encabezamientos de las columnas.

Nuestro modelo funciona de la siguiente manera:

En la columna A, a partir de la celda A10 introducimos los nombres de los participantes en la competencia.

Una vez registrados los participantes apretamos el botón “Largar”. La hora de largada (hora, minuto, segundos, milésimas de segundo) queda registrada en la celda B6. A medida que los competidores van llegando a la meta, hacemos un doble clic en la celda de la columna B a lado del nombre. Esto hace que en la celda se registre el momento de la llegada.
Una vez terminada la competencia se pulsa el botón Cerrar. Esto hará que la lista de competidores se ordene por orden de llegada y se calcule la diferencia respecto al primero.

Esta es la situación al terminar la competencia


Y ésta después de apretar el botón Cerrar



Todo el proceso puede verse en esta captura de pantalla

Este modelo funciona en base a la función Timer del Vba (Visual Basic for Applications, el lenguaje macro de Excel). La función Timer da la cantidad de segundos transcurridos desde la medianoche (00:00:00). Al apretar el botón Largar, registramos la cantidad de segundos transcurridos desde la medianoche y ponemos el dato en la celda B6. Pero previamente lo dividimos por 86400 para poder expresarlo como hora (hora, minutos, segundos y milésimas de segundo).

Un detalle importante: todas las celdas que contienen tiempos están formadas con este formato personalizado

hh:mm:ss.000

En un módulo común del editor de Vb ponemos este código y lo ligamos al botón Largar

Sub largada()
startTime = Timer
Range("B6") = startTime / 86400
End Sub

Para registrar el momento de llegada de cada competidor necesitamos dos herramientas: un evento que registre que hemos hecho un doble clic en la celda de llegada del competidor y una macro que calcule el momento y lo registre en la celda correspondiente.

El código del evento va en un módulo de la hoja. En el editor de Vb hacemos un doble clic sobre el icono de la hoja Competencia



y en el módulo que se abre ponemos este código

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Partic As Long, partList As Range

Partic = Range("A10").CurrentRegion.Rows.Count - 1

Set partList = Range(Range("A10").Offset(0, 1), _
Range("A10").Offset(Partic, 1))

If Union(Target, partList).Address = partList.Address And _
IsEmpty(Target) Then Call llegada
End Sub
La tarea de este código es detectar si hemos hecho un doble clic sobre alguna de las celdas de la columna B al lado del nombre de un competidor. Además el código controla que la celda esté vacía, es decir que no hayamos registrado el tiempo de llegada previamente. Si se cumplen ambas condiciones, se activa la macro “llegada”. La tarea de esta macro es registrar la hora de llegada y el tiempo transcurrido desde el comienzo de la competencia.

Esta macro va en un módulo común del editor de Vb y su código es:

Sub llegada()
Dim finalTime As Single
finalTime = Timer
ActiveCell = finalTime / 86400
ActiveCell.Offset(0, 1) = ActiveCell - Range("B6")
End Sub
Ahora necesitamos ligar una macro al botón Cerrar. Esta macro debe ordenar los competidores por orden de llegada y calcular las diferencias respecto al primero. El código de esta macro es:

Sub cierre_comp()

Dim Participantes As Long, iX As Long

On Error Resume Next
Range("A10").CurrentRegion.Sort Key1:=Range("C10"), Order1:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
On Error GoTo 0

'calcula la diferencia con el primero

Participantes = Range("A10").CurrentRegion.Rows.Count

For iX = 1 To Participantes - 1
Range("A10").Offset(iX, 3) = Range("A10").Offset(iX, 2) - _
Range("A10").Offset(0, 2)
Next iX
End Sub

Finalmente ligamos este código al botón Reset, para borrar todos los datos y poder comenzar una nueva competición:

Sub reset()
Range("B6").ClearContents
Range("A10").CurrentRegion.ClearContents
End Sub

Con esto hemos terminado de construir nuestro modelo.


Este modelo no es flexible, es decir, si cambiamos la estructura de la hoja al agregar o quitar filas o columnas, las macros dejarán de funcionar correctamente. Además no tiene ningún tipo de control para evitar entradas erróneas. Por ejemplo, necesitamos un control que impida largar la competencia si no se han introducido previamente la lista de competidores.

Un modelo flexible, que puede adaptarse al formato deseado, puede descargarse aquí. También incluye una hoja de instrucciones

Corregir suma de porcentajes redondeados.

Cuando sumamos números que hemos redondeado suele presentarse el problema que, aparentemente, Excel no suma los números correctamente.

Supongamos que queremos repartir un bono entre distintos vendedores, de acuerdo a la parte proporcional de cada uno en el total de las ventas. Dado que somos expertos en Excel sólo nos toma unos momentos preparar el informe

Nuestro jefe nos pide que presentemos el informe con números enteros, prescindiendo de los centavos. Ningún problema! Ajustamos el formato de los números




Pero nuestro jefe tiene ojo de lince y advierte que el total de las sumas a distribuir en la columna D suman 9999! Esto por supuesto no es un “bug” de Excel ni tampoco un problema matemático. Sencillamente, la parte decimal oculta completa el 1 que nos está faltando.

Supongamos ahora que usamos la función REDONDEAR para hacer el ajuste de las sumas. También en este caso nos topamos con el problema

Hay varias formas de solucionar el problema y todo depende del gusto personal de cada uno (o del jefe de cada uno).

Una posibilidad es cargar la diferencia en forma arbitraria a alguno de los vendedores, por ejemplo al último de la lista. Hacemos esto poniendo en la celda del último vendedor la diferencia entre el total a repartir y el total de bonos repartidos a los demás vendedores

En la celda D8 ponemos la fórmula =D9-SUMA(D4:D7), donde D9 es el total a repartir y SUMA(D4:D7) el total de bonos repartidos exceptuando el último vendedor.
Esta fórmula se “autoajusta” y nos garantiza que siempre el total de las sumas redondeadas coincida con la suma a distribuir.

Otra posibilidad es usar una fórmula que asigne automáticamente la diferencia al vendedor con los mejores resultados, en nuestro caso José. Podemos hacer esto usando una columna auxiliar para determinar dinámicamente quién es el vendedor con los mejores resultados usando la función JERARQUIA

En las celdas en el campo Auxiliar (columna D) usamos la fórmula

=JERARQUIA(B4,$B$4:$B$8)

En el campo Distribución (columna E) usamos la fórmula

=REDONDEAR($B$1*C4,0)+(D4=1)

La primer parte de la fórmula calcula el bono del vendedor y lo redondea. La segunda parte controla si es el mejor vendedor (JERARQUIA =1) y en caso de ser cierto agrega 1.

Claro que esta solución es parcial y se aplica sólo al caso que la diferencia sea +1.

Para hacer esta solución más general podemos usar dos campos auxiliares. En el primero calculamos los bonos redondeados; en el segundo usamos JERARQUIA como en el caso anterior. Luego podemos usar la diferencia entre el total de Auxiliar 1 (celda D9) y bono a distribuir (celda B1) para ajustar el resultado poniendo la fórmula

=D4+((E4=1)*($B$1-$D$9))

en el campo Distribución (columna F)

Technorati Tags: MS Excel