Crear series de días hábiles en Excel

Podemos crear una serie de días hábiles en Excel usando una funcionalidad poco conocida: Rellenar  - Series. Podemos activar este funcionalidad de dos maneras: en la cinta de opciones con Rellenar-Series o con el menú contextual de opciones de autorrelleno.

Supongamos que queremos crear una lista de los días hábiles de agosto del 2015. En la celda A1 ponemos la fecha del primer día hábil del mes de agosto de este año; luego en la cinta de opciones activamos Inicio-Modificar-Rellenar-Series y completamos el formulario de esta manera

Apretamos Aceptar y obtenemos la serie de días hábiles

La fórmula en la columna B es
=ELEGIR(DIASEM(A1;2);"lunes";"martes";"miércoles";"jueves";"viernes";"sábado";"domingo")

Podemos hacer lo mismo con el menú contextual  arrastrando con el mouse

La serie de días hábiles resultante depende de las definiciones regionales del sistema.

Excel reconoce si el valor de la celda es una fecha y ofrece crear series de días corridos, hábiles, meses y años.

Comentarios en celdas - una alternativa

Todo usuario de Excel conoce la posibilidad de insertar comentarios en las celdas de la hoja. Los comentarios son una manera muy práctica de asociar observaciones al contenido de la celda. Un pequeño triángulo rojo en el ángulo superior derecho de la celda nos indica la existencia del comentario

Con el método tradicional es muy fácil  introducir y modificar comentarios, pero en ciertas situaciones pueden presentarse inconvenientes. Por ejemplo, según las deficiones por defecto, el comentario se hace visible cuando apuntamos a la celda que lo contiene. Esto puede ocultar el contenido de celdas contiguas que pueden ser necesarias para las acciones a tomar por el usuario. Otro inconveniente es que el comentario desaparece al dejar de apuntar a la celda lo cual limita su uso para dar instrucciones al usuario.

Una alternativa es usar cuadros de texto u cualquier otra forma, combinados con un poco de código Vba (macros) para hacerlos aparecer o desaparecer.

Veamos cómo aplicar esta técnica al ejemplo de la imagen más arriba.

Empezamos por agregar un icono que indique la posibilidad de recibir instrucciones usando, por ejemplo, una imagen prediseñada

Luego creamos un cuadro de texto con las instrucciones y activamos el panel de selección

Tal como hicimos con el icono de información, agregamos un icono "x" al cuadro de texto que nos servirá para que el usuario pueda cerrar (volver invisible) el cuadro.

En el panel cambiamos el nombre por defecto de los objetos por algo más explícito; por ejemplo, en lugar de "CuadroTexto 3" hacemos un clic sobre el nombre  lo reemplazamos por "ctInstrucciones". Esto nos será útil para simplificar nuestro código

Ahora tenemos que crear el código para volver visible o invisible el cuadro de texto. Activamos la grabadora de macros y ocultamos el cuadro de texto pulsando el "ojo" a la derecha del nombre del objeto en el panel de selección; hacemos lo mismo con la imagen de la "X".

El código resultante es el siguiente:

Sub ocultar_objetos()
'
' ocultar_objetos Macro
'
    ActiveSheet.Shapes.Range(Array("ctInstrucciones")).Visible = msoFalse
    ActiveSheet.Shapes.Range(Array("imCerrar")).Visible = msoFalse
End Sub

Como sucede con todo código creado por la grabadora, podemos simplificarlo a éste:

Sub ocultar_objetos()
'
'
    ActiveSheet.Shapes. _
        Range(Array("ctInstrucciones", "imCerrar")).Visible = msoFalse

End Sub

Ahora necesitamos un código para mostrar los objetos, para lo cual sencillamente copiamos el código anterior cambiando el valor "msoFalse" a "msoTrue"

Sub mostrar_objetos()
'
'
    ActiveSheet.Shapes. _
        Range(Array("ctInstrucciones", "imCerrar")).Visible = msoTrue

End Sub

El último paso es ligar la macro para mostrar los objetos al icono "i" yla macro para volverlos invisibles al icono "x"

Este video demuestra el funcionamiento

El (no tan) misterioso caso de la columna A oculta

Las últimas semanas he estado muy ocupado como ya habrán notado buena parte de mis lectores por la demora en responder a sus consultas o por las pocas publicaciones en el blog. Buena parte del tiempo que suelo dedicarle al blog ha sido invertido en el desarrollo del modelo de la factura con base de datos, que espero publicar en breve (y de paso, gracias por adelantado a los correctores por las observaciones y sugerencias que me ha hecho llegar).

Este jueves viajo a Berlín, la ciudad natal de mi madre, por una semana. Por este motivo y aunque trataré de hacerlo, no puedo prometer responder a las consultas que me lleguen.

Mientras tanto dedicaré esta nota al problema de la columna A que ha sido ocultada y no parece haber forma de volver a mostrarla. En realidad no se trata de un problema y mucho menos de un bug. El usuario desprevenido, en particular el principiante, puede pasar por alto algunos detalles y no ver la forma de hacerlo.

Supongamos que alguien no ha mandado este cuaderno

Normalmente seleccionamos las columnas adyacentes a la columna o columnas ocultas y las mostramos con Celdas-Formato-Mostrar Columnas o con el menú contextual (seleccionar las columnas y usar el botón derecho del mouse). Pero en este caso parece que no hay forma de seleccionar la columna A. Hay varias formas de hacerlo:

• hacemos un clic sobre la letra de la columna B para seleccionar toda la columna y arrastramos el mouse hacia la izquierda; de esta manera seleccionamos ambas columnas.
• Lo mismo podemos hacer usando Ir a-Referencia (apretando F5 o Ctrl-I).
• En el cuadro de nombres (en la imagen de arriba, el rectángulo arriba de la letra B donde se ve "B1") escribimos "A1" y apretamos ENTER. Esta acción selecciona la celda A1; luego usamos Celdas-Formato-Mostrar Columnas.
• Usamos el triángulo que aparece a la izquierda de "B" y arriba de "1" para seleccionar todas la hoja y usamos alguna de las opciones mencionadas para mostrar las columnas. Esta acción, a diferencia de las anteriores, mostrará todas las columnas ocultas de la hoja.

Hay una situación en la cual estos métodos no producen el resultado esperado. Cuando ocultamos una columna Excel define el ancho a 0

Al usar "Mostrar", Excel reestablece el ancho de la columna. Ahora supongamos que, intencionadamente o no, establecemos el ancho de la columna a 0.08 (o 0,08 según usen el punto o la coma como separador)

Como pueden apreciar, la diferencia con el caso anterior es prácticamente imperceptible. De hecho la columna está oculta pero los métodos para mostrarla no funcionarán ya que el ancho no es cero. Para mostrarla tenemos que, sencillamente, cambiar el ancho.

Transponer rangos que contienen referencias

Excel facilita la tarea de transponer rangos, es decir, invertir el orden filas/columnas. Todo lo que hay que hacer es seleccionar el rango, copiarlo (Ctrl+C) y pegarlo en otra área usando la opción Transponer de Pegado Especial.
El proceso funciona sin problemas si el rango a transponer contiene sólo valores

y por lo general también si el rango contiene fórmulas. En el ejemplo de arriba, la celda C3 (Enero/Norte) contiene la fórmula

=SUMAPRODUCTO((Mes=$B3)*(Sucursal=C$2)*Ventas)

donde "Mes", "Sucursal" y "Ventas" son nombres definidos que se refieren a los rangos que contienen los datos.

En la tabla transpuesta las ventas de Enero de la zona norte se encuentran en la celda C13 que contiene la fórmulas

=SUMAPRODUCTO((Mes=C$12)*(Sucursal=$B13)*Ventas)

Pero hay casos en que Excel puede dar resultados incorrectos como en este ejemplo. Los datos en el rango D2:E6 se refieren a los datos del rango B2:B11

El valor en D2 está ligado a B2; E2 a B7; D3 a B3; etc.

Dado que las referencias son relativas, al transponer el rango Excel "reordena" las referencias

Podemos transponer copiando sólo lo valores (Pegado Especial-Valores-Transponer), pero si queremos conservar las referencias/fórmulas podemos aplicar esta técnica que publicó Chandoo en su blog:

1. Seleccionamos el rango a transponer (D2:E6 en nuestro ejemplo);
2. usamos Buscar y Reemplazar (Ctrl+L) para reemplazar los "=" por "#"
3. copiamos (Ctrl+C) y transponemos el rango modificado;
4. en el rango transpuesto reemplazamos los "#" por "="

Al reemplazar los "=" por "#", las referencias se convierten en constantes; al reemplazar los "#" por "=" en el rango transpuesto, convertirmos las constantes nuevamente en referencias. Este video muestra el proceso

Este método es útil sólo si los símbolos "=" aparecen al principio de la referencia/fórmula.

Como usar la calculadora del Windows en Excel

Aprovechando un momento de descanso en mi viaje de trabajo, publico este tip rápido. ¿Se acuerdan de la calculadora del Windows?

A pesar que Excel nos permite hacer todo cálculo que queramos, a veces es prático tener esta calculadora a nuestra disposición.

Para activar la calculadora no tenemos que salir de Excel, sino que podemos agregarla a la barra de acceso rápido.

Este video muestra el proceso

Tal vez parezca supérfluo agregarla a la barra de acceso rápido, pero la calculadora tiene muchas más funciones y usos de lo que aparenta.

Por ejemplo, si tenemos que realizar conversiones entre unidades (Ctrl+U)

O diferencia entre fechas (Ctrl+E)

También podemos convertirla en calculadora científica, de programadores, etc.

Una alternativa interesante son las funcionalidades que aparecen bajo el rótulo Worksheets

Por ejemplo, para calcular la cuota de un préstamo

Truco para consolidar datos de varias hojas Excel

Excel permite la creación de rangos tridimensionales, tal como mostré en el pasado (apretar el enlace para ver la nota). Un rango tridimensional es aquel que se extiende a más de una hoja. En este ejemplo tenemos un cuaderno con cinco hojas (ya he señalado que separa datos de esta manera es una mala práctica pero a los efectos del ejemplo ignoraré mis propias recomendaciones).

Las hojas "Ventas…" contienen los totales de ventas de cada sucursal para cada año; en la hoja "Totales" queremos consolidar el valor de la celda B6 de todas las hoja de ventas

En lugar de la técnica que mostré en la nota mencionada más arriba, podemos usar este truco:

En la celda C3 de la hoja Totales introducimos =SUMA('*'!B6)

Al cerrar el paréntesis y apretar Enter, Excel lo transforma en

=SUMA('Ventas 2009:Ventas 2012'!B6)

Como puede apreciarse, todas las hojas del cuaderno, excepto la activa, son incluidas en el rango tridimensional.

Ahora supongamos que el cuaderno contiene también hojas para los costos de ventas, con la misma estructura (los totales en la celda B6). Para que estas hojas no aparezcan en la fórmula podemos condicionar el comodín con un criterio, por ejemplo

=SUMA('Ventas*'!B6

Esta técnica no está limitada a una única celda. Podemos usar, por ejemplo,

=SUMA('Ventas*'!B2:B5

Tampoco está limitada a la función SUMA (la lista de funciones puede verse en la nota ya mencionada).

Intercalar columnas y filas en Excel – un atajo

A menudo surge la necesidad de intercalar columnas o filas en una tabla de Excel. Por ejemplo, en esta tabla de ventas queremos agregar el segundo trimestre

Es decir, intercalar una columna entre B y C y otra entre C y D

Chandoo publica un atajo para realizar esta tarea con un solo clic:

Manteniendo la tecla Ctrl apretada seleccionamos la columna C y después la columna D (un clic para cada selección); abrimos el menú contextual (clic al botón derecho del mouse) y activamos Insertar

Lo mismo podemos hacer con las filas. Supongamos que queremos insertar filas entre Sucursal 1, Sucursal 2 y Sucursal 3; apretando la tecla Ctrl seleccionamos una después de la otra las filas 3, 4 y 5 y usamos el menú Insertar como en el caso anterior

Control de saldos de bancos con Excel.

Ariel me consulta cómo hacer para manejar en una única tabla los movimientos y saldos de varios bancos. Supongamos esta tabla

¿Cómo calculamos el saldo? Como prefiero usar una única fórmula para la columna propongo poner en la celda F2 ésta

=SUMA($D$2:D2)-SUMA($E$2:E2)

y copiarla a lo largo del campo

Préstese atención a las referencias semi-absolutas en los rangos de la fórmula.

El problema se nos presenta, obviamente, al filtrar la lista para ver los movimientos y el saldo de uno de los bancos

El saldo correcto es 2,495.00. Podemos solucionar esta situación usando SUBTOTALES. En F2 ponemos

=SUBTOTALES(9,$D$2:D2)-SUBTOTALES(9,$E$2:E2)

y la copiamos al todo el rango de la columna

SUBTOTALES suma sólo los valores de las celdas visibles, con lo que al filtrar el resultado será el correcto

Podemos mejorar notablemente nuestro modelo convirtiendo el rango de los datos en una “tabla” (o “lista” en Excel 2003).

Las tablas/listas tienen varias ventajas:

• Formato automático
• Las fórmulas en las columnas son copiadas automáticamente al agregar filas
• La fila de totales
• Actualización automática de todo objeto ( fórmulas, gráficos, tablas dinámicas) basados en la tabla

El archivo con el ejemplo se puede descargar aquí.

Actualización automática de tablas dinámicas

Ha pasado casi un mes desde que publiqué mi última nota. Antes que mis lectores (y Google!) piensen que he abandonado el blog, publico una nota breve sobre actualización automática de tablas dinámicas en Excel.

Excel no tiene un método incorporado para actualizar tablas dinámicas en forma automática. Cambios en la base de datos se reflejarán en el reporte dinámico sólo cuando apretemos el icono Actualizar (o Alt+F5) o Actualizar Todo (Ctrl+Alt+F5) si queremos actualizar todas las tablas dinámicas del cuaderno

Para evitar tener que actualizar manualmente las tablas dinámicas de nuestro cuaderno (pensemos en un dashboard donde hacemos uso intensivo de tablas dinámicas) podemos crear un evento. El código es muy sencillo

Private Sub Worksheet_Deactivate()
    ActiveWorkbook.RefreshAll
End Sub

En este caso usamos el evento Worksheet_Deactivate de la hoja “datos” (la que contiene la base de datos del reporte dinámico). De esta manera nos aseguramos que cada actualizamos la base de datos, al pasar a otra hoja las tablas dinámicas se actualizan.

Esta técnica supone que la base de datos se encuentra en una hoja del cuaderno que contiene los reportes dinámicos. En el caso en que las tablas dinámicas se basen en fuentes de datos externas, usamos el mismo código pero lo disparamos usando el evento Worksheet_Activate de alguna de las hojas que contienen los reportes dinámicos.

Definir el rango a imprimir según valor en celda

El lector Boris me consulta:

En una celda con datos tipo lista tengo 2 opciones: Factura, Boleta. ¿Es posible hacer una formula con la función SI, para definir el área de impresión, dependiendo de esas dos opciones?

Una vez más aclaremos que las funciones sólo saben hacer cálculos; no podemos hacer cambios estructurales en la hoja o el cuaderno con funciones.

Sin embargo sí podemos definir el rango del área de impresión basándonos en el valor de una celda. Lo haremos usando la técnica que mostré en la nota sobre área de impresión dinámica en Excel sin macros.

El truco consiste en usar nombres. Supongamos que definimos dos rango a imprimir: Area_1 y Area_2

Lo que hemos llamado Area_1 o Area_2 en el ejemplo puede ser factura y recibo o escenario 1 y escenario 2, etc.

Definimos cada uno de los rangos con nombres

El próximo paso es definir una lista desplegable sencilla con validación de datos y ponerla en una celda donde el usuario pueda elegir el rango a imprimir

El último paso es modificar la definición del nombre Area_de_impresion . Esto lo haremos con el asistente de nombres. En la definición del nombre predefinido "Area_de_impresión" ponemos la fórmula

=INDIRECTO(Hoja1!$C$2)

Si al abrir el Administrador de nombres "Área _de_impresión" no aparece, podemos crearlo definiendo un rango arbitrario en "Área de impresión" del menú Configurar página.

La fórmula =INDIRECTO(Hoja1!$C$2) traduce el texto que aparece en la celda C2, donde hemos puesto la lista desplegable, por el rango definido por el nombre que coincide con el valor de la celda.

Este video muestra el funcionamiento

Mostrar los criterios de Autofiltro en una celda

Una de las funcionalidades más populares de Excel es el Autofiltro. Con esta herramienta podemos filtrar una tabla de acuerdo criterios lógicos aplicados a los valores de una o más columnas de la tabla.
Un lector me consulta cómo se puede hacer para que el criterio aplicado aparezca en una celda por encima del encabezamiento de la columna. Por ejemplo, partiendo de esta lista

ver en las celdas correspondientes de fila 2 los criterios aplicados para filtrar la lista

Como puede verse, estamos usando una función definida por el usuario (UDF).

Esta función fue desarrollada por el guru de Excel Stephen Bullen.

Para poder usar esta función tenemos que poner este código en un módulo común del editor de Vba, preferentemente en el Personal.xlsb (Personal.xls en Excel 97-2003)

Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen
    Dim Filter As String
   
    Application.Volatile True
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " y " & .Criteria2
                Case xlOr
                    Filter = Filter & " o " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = Filter
End Function

Esta función utiliza un único argumento, "Rng", que es cualquier celda de la columna sobre se aplica el Autofiltro.

Hay que tener en cuenta que esta función fue desarrollada antes de la aparición de Excel 2007. Por ejemplo, si elegimos más de dos criterios en una misma columna, por ejemplo ver las zonas Norte, Sur y Oeste, la función no podrá mostrar el criterio aplicado

Tip: en Excel 2007, al apuntar con el mouse al icono del autofiltro podemos ver una ventanilla que nos muestra los criterios aplicados (como en la imagen de arriba).

Diseño clásico de tablas dinámicas en Excel 2007

En relación a la nota anterior sobre el tema, un lector me consultaba si era posible cambiar el diseño por defecto de las tablas dinámicas en Excel 2007 de manera que se abrieran con el diseño "clásico" (Excel 2003) y que también se pudieran arrastrar los campos, como era posible en el Excel Clásico.

En la nota anterior pasé por alto un detalle importante que da respuesta a las dos inquietudes de mi lector.

Al crear una tabla dinámica en Excel 2007, el diseño por defecto es el siguiente

Para cambiar el diseño al del Excel Clásico, en lugar de usar los estilos de tablas dinámicas como mostramos en la nota anterior, podemos usar el formulario Opciones de Tablas Dinámicas

En la pestaña "Mostrar" marcamos la opción "Diseño de tabla dinámica clásica". Esta opción no sólo cambia el diseño de la tabla sino que también permite arrastrar los campos, incluidos los campos de página, tal como lo hacíamos en las versiones anteriores de Excel. Una vez activada esta opción la tabla se verá así

Nótese que ahora los rótulos de los campos de columnas son visibles y es posible arrastrarlos. Lo mismo con los campos de página.

Este video muestra el proceso



Otro "tip" para los nostálgicos del Excel Clásico. En Excel 2007 se puede activar el "viejo" asistente de tablas dinámicas apretando el atajo de teclado Alt+T+B.

Tablas dinámicas – cambiar el diseño de Excel 2007 a Excel Clásico

Quienes emigran al nuevo Excel (2007/10) del Excel Clásico (97-2003) deben adaptarse a muchos cambios. Uno de ellos es en el diseño por defecto de las tablas dinámicas.

Partiendo de la misma base de datos obtenemos estos resultados

en Excel Clásico

en Excel 2007

Además de algunas diferencias estéticas, saltan a la vista dos detalles:

• los botones a la izquierda de los rótulos de fila en la versión 2007 que no están presentes en las versiones anteriores
• en Excel 2007 los totales de de cada rótulo de fila están en la fila inmediata superior al detalle del los valores, mientras que en Excel Clásico están al final.

A muchos usuarios les cuesta, o no quieren, acostumbrarse a este nuevo diseño. Si queremos obtener las tablas dinámicas en el nuevo Excel con el diseño del Excel Clásico podemos hacer lo siguiente

1 – cambiar el diseño de la tabla al formato tabular



2 – Cambiar el estilo

3 – Quitar los botones +/-

El resultado final