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).

Mostrar y ocultar series en gráficos de Excel dinámicamente

¿Cómo podemos hacer para mostrar u ocultar series de datos en un gráfico de Excel dinámicamente? Con controles de la barra de formularios, que son sencillos de usar y no requieren programación.
Por ejemplo, supongamos este gráfico

Nuestro objetivo es añadir un elemento que nos permita con un clic mostrar u ocultar cada una de las series.

Veamos los pasos a dar:

Agregamos tres casillas de verificación de la barra de formularios, una para cada serie

Reemplazamos el texto de cada casilla por el nombre de la serie.

Definimos la celda vinculada al control

En este caso he elegido una celda visible, para mostrar el funcionamiento del control. En una implementación real, usaremos una celda oculta.

Hacemos lo mismo con los restantes controles.

Cuando la casilla está activada, el valor de la celda vinculada pasa a ser VERDADERO; cuando no lo está el valor será FALSO. Vamos a aprovechar estos valores en nuestro mecanismo.

El próximo paso consiste en crear cuatro nombres definidos, uno para cada serie y uno para los valores de las categorías (el eje de las X, los meses en nuestro ejemplo).

Para los valores del eje de las X

Mes=dinamico!$B$3:$B$14

Para las series

Ventas =SI(dinamico!$G$20,dinamico!$C$3:$C$14,dinamico!$F$3:$F$14)
Costos=SI(dinamico!$H$20,dinamico!$D$3:$D$14,dinamico!$F$3:$F$14)
Ganancia=SI(dinamico!$I$20,dinamico!$E$3:$E$14,dinamico!$F$2:$F$14)

Como puede verse, estos nombres contienen fórmulas condicionales. Si el valor de la casilla de verificación es VERDADERO, la condición se cumple y el rango es el definido en la tabla de datos; si el valor es FALSO, el rango elegido es F3:F14 que no contiene ningún valor (y por lo tanto no tiene representación en el gráfico).

Ahora tenemos que reemplazar los rangos relevantes en la función SERIES de cada una de las series con los nombres definidos. Esto puede hacerse de varias maneras, por ejemplo:

Seleccionamos la serie VENTAS para que en la barra de las fórmulas aparezca la fórmula SERIES

Reemplazamos los rangos relevantes por los nombres tomando en cuenta no eliminar el signo de admiración (!) que aparece a la izquierda del rango

Apretamos Enter. En lugar del nombre de la hoja aparecerá ahora el nombre del cuaderno

También podemos usar el formulario Modificar Serie de la opción Seleccionar datos

La ventaja de esta técnica es que podemos usar Pegar Nombres (F3) en lugar de tener que escribirlos manualmente. La otra ventaja es que podemos reemplazar el rango de la categorías en el formulario de Editar Rótulos de Eje, sin necesidad de tener que reemplazarlo en la fórmula SERIES de cada serie.

Finalmente, ocultamos las celdas vinculadas, agregamos un cuadro de grupo para agrupar los controles (sólo por cuestiones estéticas) y ya hemos completado nuestro modelo



Descargar el ejemplo.

Calcular con Excel horas trabajadas con intervalos

Una consulta frecuente es cómo calcular con Excel las horas trabajadas en un lapso determinado, tomando en cuenta sólo los días hábiles y las horas de trabajo.

Por ejemplo, cuantas horas de trabajo hay desde el 29/09/2010 hasta el 01/10/2010, tomando en cuenta que la jornada de trabajo es de 08:00 a 17:00.

Excel nos permite hacer el cálculo con relativa facilidad si tomamos en cuenta ciertos detalles.
En nuestro ejemplo, el total trabajado es 90 horas que calculamos con esta fórmula

=DIAS.LAB(B1,B2)*(B4-B3)

En nuestra fórmula usamos DIAS.LAB para calcular los días hábiles entre la fecha inicial y la final. DIAS.LAB no toma en cuenta los sábados y domingos. Las horas trabajadas cada día las calculamos fácilmente como la diferencia entre la hora de cierre y la de comienzo.

Un detalle importante es que la celda que muestra el total de horas (B5) debe tener el formato [hh]:mm

Una situación distinta es cuando el turno de trabajo concluye al día siguiente. Por ejemplo, un turno que empieza a las 22:00 y termina a las 06:00

Recordemos que Excel no puede calcular diferencias de horas negativas.

Modificamos nuestra fórmula para que también pueda calcular las horas en casos como ese

=DIAS.LAB(B1,B2)*RESIDUO(B4-B3,1)

En lugar de la fórmula con RESIDUO, podemos usar también

=DIAS.LAB(B1,B2)*(B4-B3+(B4<B3))

 En caso de querer hacer el cálculo complementario, horas trabajadas descontando las horas de descanso, modificamos levemente nuestra fórmula =DIAS.LAB(B1,B2)*(1-RESIDUO(B4-B3,1))

En este caso, el trabajo se interrumpe de 22:00 a las 06:00 del día siguiente, lo que nos deja un día de trabajo de 16 horas que multiplicado por 10 días laborales nos da 160 horas.