Corrección automática de rangos en fórmulas

Supongamos esta sencilla tabla que muestra las ventas de una tienda por departamento



Vemos que faltan los datos del Departamento 3. Al insertar una línea entre los departamentos 2 y 4, el rango de la fórmula =SUMA(B2:B5) se corrige automáticamente



Veamos qué pasa si queremos agregar el Departamento 6, inmediatamente debajo del Departamento 5. Seleccionamos la fila 7 e insertamos una nueva fila. En este caso, Excel no corrige el rango de la fórmula



En ciertos casos, al agregar los datos del nuevo departamento, éstos no serán incluidos en la suma! Si tenemos habilitada la opción Extender formato de rangos y fórmulas en el menú Opciones y la tabla tiene más de cinco líneas, Excel modificará el rango de la fórmula automáticamente. Pero si pegamos los datos que hemos copiado de otra hoja o fuente, o no tenemos habilitada la opción, Excel no modificará el rango de la fórmula.

Cuando creamos cuadernos que serán usados por otros, no podemos estar seguros que al agregar líneas o hacer otras modificaciones los usuarios presten atención a este potencial problema.

La solución es usar la función DESREF para crear un rango que se adapte automáticamente cada vez que se agreguen líneas en cualquier ubicación del rango de la fórmula.

En nuestro caso la fórmula será

=SUMA(B2:DESREF(B8,-1,0))



Como ven, el primer argumento de la función, el "ancla", se refiera a la celda que contiene la fórmula. El segundo indica una referencia a una fila hacia arriba.

El mismo caso puede darse cuando insertamos una columna en un rango donde sumamos a los largo de una fila. Por ejemplo, si transponemos nuestra tabla, la fórmula será

=SUMA(B2:DESREF(G2,0,-1))

Technorati Tags: MS Excel

Cuadro de texto flotante en Excel

En ésta y en las siguientes notas me ocuparé de responder a consultas que recibo con cierta frecuencia de mis lectores. Una de ellas es cómo crear un cuadro de texto flotante en una hoja de Excel.
La idea es crear un cuadro de texto flotante que se actualice de acuerdo al valor presente en una celda o en un rango de ellas.
Empecemos por el caso más sencillo: un cuadro de texto flotante ligado a una única celda.

Para ligar el valor de la celda A1 a un cuadro de texto empezamos por crear el cuadro de texto con el icono correspondiente de la barra de dibujo. Luego, con el cuadro de texto seleccionado, introducimos manualmente la referencia a la celda A1 en la barra de fórmulas



A partir de ese momento todo valor en la celda A1 parecerá automáticamente en el cuadro de texto.

Para convertirlo en flotante programamos un evento, de manera que el cuadro siempre aparezca en la vecindad de la celda activa en la hoja. En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Shapes("Text Box 1").Top = Target.Top
End Sub


Este método no crea un cuadro realmente flotante, ya que si navegamos en la hoja usando la barra de navegación, la rueda del Mouse o cualquier otro método que no implique seleccionar una celda en la zona visible de la hoja, el cuadro desaparece junto con la celda activa. Pero basta con seleccionar una celda en la zona visible para que el cuadro vuelva a aparecer.

Para complicar un poco las cosas supongamos que queremos ligar no una celda sino un rango de celdas al cuadro de texto.
Posiblemente estén tentados a emplear el método descrito más arriba poniendo a la referencia al rango. Por ejemplo, supongamos que tenemos una tabla en el rango A1:A6 con los datos de ventas de cinco años



Al apretar Enter veremos que sólo el contenido de A1 (o de la celda superior izquierda del rango) aparece en el cuadro de texto.
La solución es usar la herramienta llamada cámara fotográfica de Excel. Lo primero que hacemos es poner el icono de la cámara fotográfica en alguna de las barras de herramientas, tal como lo explico en la nota del enlace.
Seleccionamos el rango A1:A6 y apretamos el icono de la cámara



El marcador del Mouse se tomará la forma de una cruz pequeña. Señalamos algún área de la hoja para pegar la imagen generada por la cámara



En la barra de fórmulas podemos ver que Excel ha creado una referencia absoluta al rango. Cada vez que cambiemos algún valor en el rango, éste se reflejará automáticamente en la imagen.

Ahora tenemos que adaptar el código del evento para que se refiera a este objeto. Al seleccionar la imagen el nombre del objeto aparece en el cuadro de nombres



Pero estamos usando la versión en castellano de Excel y Visual Basic sólo "habla" ingles. Así que tenemos que a averiguar el nombre en inglés. Una forma de hacerlo es grabar una macro en la cual seleccionamos el objeto (cuidándonos que antes de empezar a grabar la imagen no esté seleccionada). En nuestro caso el nombre es, obviamente, "Picture 2" (=Imagen 2 que aparece en el cuadro de nombres).
El código del evento Worksheet_SelectionChange será ahora

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Shapes("Picture 2").Top = Target.Top
End Sub


Como en el caso del cuadro de texto, la imagen aparecerá siempre en la vecindad de la celda activa en la hoja.

Esta técnica puede ser útil cuando queremos que ciertos datos estén permanentemente visibles sin necesidad de movernos en la hoja todo el tiempo.

Actualización: una técnica mejorada para que el objeto se desplace también horizontalmente puede verse en esta nota

Technorati Tags: MS Excel

Agregando valores a un cuadro combinado (ComboBox).

Después de publicar mi nota sobre autocompletar en Validación de Datos, algunos lectores me señalan que no logran encontrar la propiedad ListFillRange donde tienen que poner la referencia al rango que contiene los valores de la lista desplegable. Esto tanto con el control cuadro combinado como con el control cuadro de lista.

Existen ciertas diferencias entre los controles que agregamos directamente en una hoja de Excel y aquellos que agregamos a un UserForm en el editor de Visual Basic.

Cuando ponemos un control ActiveX directamente en la hoja vemos las propiedades del control activando el modo Diseño y abriendo el cuadro de propiedades (ambos iconos aparecen en la barra de Cuadro de Controles). Todo esto lo hemos mostrado en la nota mencionada.

Cuando creamos un cuadro combinado (ComboBox) o cuadro de lista (ListBox) sobre un Userform tenemos que activar el panel de propiedades del editor de Vb (VBE) para ver las propiedades




La propiedad que define los valores de cuadro o de la lista es RowSource. Para agregar valores al cuadro podemos usar una referencia a un rango en una hoja de Excel o podemos hacerlo usando código Vba.

Para ejemplificar el primer caso supongamos que en rango A1:A12 de la Hoja1 tenemos una lista de los meses del año, que podemos crear fácilmente con el menú Opciones-Listas Personalizadas.
En la propiedad RowSource del control ComboBox1 ponemos la referencia, con la sintaxis apropiada (ver el signo de exclamación entre el nombre de la hoja y la referencia al rango)



Para comprobar si nuestro control ha recibido los valores, seleccionamos el UserForm y apretamos F5. El UserForm aparecerá en la hoja activa (en realidad "sobre" la hoja) y podemos apretar la flecha para ver los valores disponibles



Otra posibilidad es agregar los valores usando programación. También aquí se nos abren dos posibilidades.
Si los valores se encuentran en un rango de una hoja, como en el caso que acabamos de mostrar, podemos usar un código como éste

Sub agregar_valores()

UserForm1.ComboBox1.RowSource = "Hoja1!A1:A12"

UserForm1.Show
End Sub

Al correr esta macro se abre el UserForm y podemos ver que los valores son los del rango.
Otra posibilidad es agregar los valores usando el método Add Item de Vba:

Sub add_val()
With UserForm1.ComboBox1
.RowSource = " "
.AddItem "Enero"
.AddItem "Febrero"
.AddItem "Marzo"
.AddItem "Abril"
.AddItem "Mayo"
.AddItem "Junio"
.AddItem "Julio"
.AddItem "Agosto"
.AddItem "Setiembre"
.AddItem "Octubre"
.AddItem "Noviembre"
.AddItem "Diciembre"
End With

UserForm1.Show
End Sub

En resumen, el manejo de las propiedades de los controles es distinto dependiendo si los hemos creado directamente en la hoja con la barra de Controles o han sido programados con Visual Basic.



Technorati Tags: MS Excel