Uso de tablas en hojas protegidas

No es necesario que nos extendamos sobre las bondades del uso de Tablas (Listas en Excel 2003). Sin embargo existe un inconveniente que aún no ha sido tratado por Microsoft. En una hoja protegida las tablas dejan de expandirse automáticamente.

En general usamos tablas para introducir datos en una base de datos plana y que los objetos que hayamos creado a partir de la tabla (gráficos por ejemplo) se adapten automáticamente y/o para evitar la necesidad de copiar fórmulas a lo largo de una columna cuando agregamos filas. En este tipo de situaciones no existe una necesidad real de proteger la hoja. La necesidad puede surgir si tenemos una o más columnas en la tabla con fórmulas y queremos evitar que el usuario las pueda modificar o queremos ocultarlas.

Si bien el menú de protección incluye las posibilidades Insertar Columnas e Insertar Filas,

la tabla dejará de agregarlas automáticamente en una hoja protegida.

Una solución posible es agregar filas en la tabla de antemano. Esto es relativamente razonable si el modelo tiene lógicamente un número definido de filas (por ejemplo, comparación de ventas-plan por mes de un año determinado).

Una solución más dinámica es usar una macro, más precisamente un evento. Supongamos este modelo

El rango A1:E8 lo hemos definido como tabla y debe expandirse automáticamente a medida que agregamos datos. La tabla H1:I6 contiene el plan de ventas mensual de las sucursales y la usamos en la fórmula de la columna D de la tabla

=BUSCARV([@Sucursal],tblPlanMensual,2,0)

Para que la tabla se autoexpanda, también en una hoja protegida, abrimos el módulo del editor Vba de la hoja (clic con el botón derecho a la pestaña de la hoja)

y ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    With ActiveSheet
        .Unprotect
        .ListObjects("Tabla1").Resize Target.CurrentRegion
        .Protect
    End With
 
End Sub

Explicación del código:
Las tres primeras líneas del código interrumpen el evento si:

“If Target.Count > then Exit Sub” – el rango seleccionado incluye más de una celda.

“If Target.Row = 1 Then Exit Sub” - si la fila es 1

“If Target.Column <> 1 Then Exit Sub” – si la columna no es A.

El resto del código quita la protección (.Unprotect), expande el rango de la tabla (.Resize Target.CurrentRegion) y vuelve a proteger la hoja.

“Tabla1” es el nombre de la tabla, visible en el menú Tabla cuando activamos alguna de sus celdas

Es una buena práctica cambiar el nombre por defecto (Tabla1) por un nombre más significativo. El cuaderno con el código puede descargarse aquí.

Formato condicional para resaltar máximos y mínimos según criterios

Hace casi un mes que no publico nada en el blog. No por pereza o desidia sino por una inesperada carga de trabajo. Cuento con la comprensión de los lectores que han quedado sin respuesta a sus consultas.

Uno de mis lectores me consulta cómo usar formato condicional para resaltar mínimos (o máximos). Supongamos una lista de precios donde varios productos aparecen con precios distintos

Cómo extraer los máximos y mínimos según criterios (producto, en nuestro caso), ya hemos visto en la nota que publiqué en abril de año pasado. En nuestro ejemplo la solución sería

Las fórmulas para calcular los máximos y mínimos son matriciales (se introducen apretando simultáneamente las teclas Ctrl-Mayúsculas-Enter):

• para los máximos: =MAX(($A$2:$A$13=D2)*$B$2:$B$13)

• para los mínimos: =MIN(SI(($A$2:$A$13=D2),$B$2:$B$13))

Podemos usar las mismas fórmulas para aplicar el formato condicional. Por ejemplo, para resaltar los mínimos en la lista de precios

En formato condicional no hace falta introducir la fórmula en forma matricial.

Calcular temporadas altas y bajas con Excel

La consulta que generó esta nota es:

[…si introduzco una fecha de entrada y una de salida a un hotel que devuelva un mensaje como temporada alta, media o baja.]

Hay varias formas de hacer el cálculo y si bien ninguna es demasiado complicada, la explicación excede el marco de un comentario (y el lector se merece una respuesta).

El asunto de las temporadas de turismo (alta, media o baja) no parece seguir ninguna regla establecida fuera de una muy elemental: cuando hay mucha demanda, la temporada es alta; cuando hay poca demanda es baja y en los demás casos es media. Así que las fechas de las temporadas dependen no sólo de las estaciones del año, sino del la ubicación geográfica y de la rama de la industria turística, por lo menos.

Dicho esto veamos cómo resolver la cuestión con Excel. Empecemos por el caso sencillo en que el año está dividido en dos temporadas: alta y baja. Podemos expresar las temporadas en esta tabla

Como vemos, las fechas en la matriz son calculadas con la fórmula

=FECHANUMERO("01/07/"&$C$2)

El número de año en la celda C2 lo calculamos con la fórmula =AÑO(B10)

Finalmente, en la celda C10 usamos la fórmula

=SI(Y(B10>=C5;B10<=D5);"Alta";"Baja")

Dado que nuestro modelo es simplista, toda fecha que no caiga dentro del intervalo 01-Jul-2012 a 30-Sep-2012 (la temporada alta), es temporada baja.

Pero, por supuesto, la realidad es más complicada y el año puede dividirse en varias temporadas altas, bajas y medias. En ese caso debemos usar otro modelo.

Nuestra primera aproximación es un modelo sencillo donde usamos una tabla de 366 días (para cubrir también los años bisiestos) y a cada día le asignamos la temporada correspondiente

En la celda A4 usamos nuevamente la función FECHANUMERO y en la subsiguiente =A4+1. Al copiar esta última en las próximas 364 filas, obtenemos el año. Luego ponemos en la columna B el nombre de la temporada.

En la celda F4 usamos =BUSCARV(E4;$A$4:$B$369;2;0)

Como en el caso anterior usamos =AÑO(E4) para extraer el año de la fecha investigada y el resultado lo usamos para armar la tabla anual.


Una solución más elegante es la siguiente

En el rango A5:A11 ponemos un texto con el día y mes de comienzo de la temporada, como 01/01/

En el rango B5:B11 usamos la fórmula =FECHANUMERO(A5&$B$2), que nos asegura obtenerla fecha con el año correspondiente.

En B4 volvemos a usar =AÑO(F4).

Finalmente, en G4 usamos =INDICE(C5:C11;COINCIDIR(F4;B5:B11))

Como usamos COINCIDIR con el argumento "Tipo_de_coincidencia" omitido, es importante que el rango B5:B11 esté ordenado en forma ascendente.

El cuaderno con los ejemplos puede descargarse aquí.