Fijar vínculos entre hojas de cuadernos Excel

Al crear vínculos entre rangos de distintos cuadernos, como vimos en esta nota, puede presentarse un problema al introducir cambios en el cuaderno de origen.

Vamos a mostrarlo con un ejemplo. Supongamos dos cuadernos, Origen y Base. En la celda C2 de la hoja1 de Base creamos un vínculo a la celda C2 de la hoja1 de Origen

En la barra de las fórmulas podemos ver que el valor se refiere a la celda C2 del cuaderno Origen.

Guardamos y cerramos el cuaderno Base. Abrimos el cuaderno Origen y movemos le valor de la celda C2 a la celda D2. Guardamos el cuaderno Origen y lo cerramos.

Al volver a abrir el cuaderno Base veremos que el cambio en Origen no se refleja en la celda vinculada y por lo tanto muestra un valor erróneo

Esto se debe a que efectuamos el cambio en Origen después de haber cerrado el cuaderno Base. El vínculo en Base sigue refriéndose a la celda C2 de Origen.

Una solución a este problema es mantener ambos cuadernos abiertos hasta finalizar de realizar todos los cambios.

Una solución más segura es usar nombres. En nuestro caso creamos un nombre que se refiere al rango C2 en la hoja Origen (“DatoParaBase”)

Si movemos el valor de la celda C2 en origen y guardamos el cuaderno, al abrir Base veremos que la referencia se ajusta automáticamente (en lugar de la referencia C2 aparece el nombre que se refiere al rango)

Esta técnica funcionará también si Origen esta cerrado al abrir Base.

Seguir leyendo...

Gráfico de columnas con formato dinámico

Al presentar datos en forma gráfica suele surgir la necesidad de resaltar uno o varios puntos de una serie.

Supongamos esta situación: presentamos las ventas de un año y queremos resaltar los mese en los que las ventas han caído por debajo de un cierto límite.

En el gráfico del ejemplo podemos ver con facilidad en qué meses las ventas han caído por debajo de límite establecido. Si se trata de un gráfico estático, donde los datos no cambian, nos basta con dar formato a los puntos de la serie en cuestión.

Pero si queremos mostrar distintos escenarios, cambiando el valor del límite por ejemplo, tenemos que convertir nuestro gráfico en dinámico. Es decir, que el color de los puntos de la serie cambie de acuerdo a la relación al valor del límite.

El principio básico para lograr este tipo de gráfico es separar los puntos de la serie (los meses) en dos series: por encima y por debajo del límite. Esto lo haceos creando un tabla auxiliar en una rango oculto

Los valores de la columna B (sobre el límite) los calculamos con la fórmula:

=SI(F5>=$F$2,F5,NOD())

Los valores de la columna C (debajo del límite) con:

=SI(F5<$F$2,F5,NOD()) 

Al representar vemos dos detalles “indeseables” en el gráfico:

1. El formato numérico del eje de las Y 
2. Las columnas no están ordenadas en forma simétrica. 

El primer detalle lo corregimos usando formato personalizado para los valores del eje

Este formato presenta los valores por miles: ###,###, "M";(###,###,)" M";0

El segundo problema se debe a que estamos representando dos series de datos en el gráfico de manera que para cada mes hay dos valores. Este problema lo solucionamos asignando una de las series al eje Y secundario

Al hacerlo so nos presenta un tercer problema: las escalas de los ejes Y no coinciden!

Podemos corregir esto manualmente con el menú de formato del eje. Pero al volver a cambiar el valor del límite o cualquier otro dato, no enfrentaremos con el mismo problema. Una solución es fijar los valores de ambos ejes. Una solución más general es programar un evento que corrija el valor del eje secundario de manera que siempre coincida con el primario.

Empezamos por definir un nombre que se refiera al rango de los datos (rngDatos en nuestro ejemplo se refiere a la celda que contiene el límite y a las celdas con los valores de las ventas).

También cambiamos el nombre por defecto del objeto gráfico (Gráfico 1) usando el Panel de Selección

En el módulo Vba de la hoja ponemos este código


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngActCell As Range

    Application.ScreenUpdating = False
   
    If Union(Target, Range("rngVentas")).Address = Range("rngVentas").Address Then
   
    Set rngActCell = ActiveCell
   
     ChartObjects("grfVentas").Activate
        ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = _
                ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
   
    rngActCell.Select
   
    End If
   
    Application.ScreenUpdating = True
   
End Sub




Un último paso es ocultar el eje secundario

El archivo del ejemplo se puede descargar aquí.

Seguir leyendo...

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

Seguir leyendo...