Manejo de escenarios con Excel

Uno de los usos frecuentes de Excel es crear modelos de predicción de resultados o presupuestos según van cambiando ciertas variables. En ingles existe el término "what if" para describir este tipo de análisis. En el proceso de escribir esta nota he buscado un equivalente en castellano a esta expresión. La ayuda en línea de Excel traduce el término a "y si", que por algún motivo me parece menos acertado que su equivalente literal en inglés. Tal vez sería mas expresivo usar "qué pasaría si", pero supongo que a los traductores les debe haber parecido excesivamente largo.
Mi inclinación personal es usar la expresión "análisis de escenarios" y precisamente Escenarios es el nombre de una las herramientas que Excel pone a disposición de los usuarios.

Según mi experiencia personal, esta herramienta es poco conocida y aún menos usada a pesar que puede ahorrarnos tiempo y errores.

De acuerdo a la ayuda de Excel "Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir automáticamente en la hoja de cálculo"



La funcionalidad se encuentra en el menú Herramientas, junto con Buscar Objetivo y Solver. Esto no es casualidad ya que estas herramientas también permiten realizar análisis de sensibilidad y de escenarios. Bajo el menú Datos se encuentra otra herramienta para realizar análisis de sensibilidad, las Tablas.

Antes de usar la herramienta Escenarios necesitamos construir un modelo cuyos resultados sean sensibles al cambio en una serie de variables. Esta herramienta acepta hasta 32 variables.

Veamos un uso posible de Escenarios con un ejemplo. Supongamos una empresa cuyos ingresos son en euros (todas sus ventas son en Europa), los materiales son adquiridos en dólares y el resto de los ingresos y gastos son en pesos. El cuadro de pérdidas y ganancias de semejante empresa será afectado por los cambios en las cotizaciones de las monedas, aún si no hay cambios en la actividad real de la empresa.

Queremos investigar cuáles serán los resultados del año para este empresa de acuerdo a distintos escenarios para las tasa de cambio. El modelo que construimos será éste:



En las celdas B2 y B3 tenemos las tasas de cambio actuales del euro y el dólar. Estas celdas serán las variables en nuestro modelo.
En el cuadro de pérdidas y ganancias proyectado tenemos los montos en la moneda original en la columna C y los montos convertidos a pesos en la columna D.

Con las tasas de cambio actuales, predecimos un porcentaje de beneficio bruto del 42%. ¿Que pasaría si esta tasa cambiaran? Supongamos tres posibilidades (escenarios) posibles:

1 - euro = 4.50; dólar = 3.30
2 - euro = 4.10; dólar = 3.10
3 - euro = 4.10; dólar = 3.30

Para crear los escenarios con la herramientas abrimos el menú Herramientas-Escenarios



Apretamos el botón Crear e ingresamos los datos. Al primer escenario lo llamaremos Devaluación, al segundo Revaluación y al tercero Mixto.



Al apretar el botón Aceptar se abre este formulario donde ingresamos los valores de las variables correspondientes al escenario



Apretamos aceptar y en el formulario que se abre volvemos a apretar Agregar para insertar el próximo escenario (Devaluación). Volvemos a realizar el proceso para agregar Mixto.



Cuando terminamos de ingresar los datos de todos los escenarios podemos apretar el botón Mostrar. Esto hará que los resultados en el modelo cambien de acuerdo al escenario elegido. Por ejemplo, si elegimos el escenario Revaluación y apretamos Mostrar, veremos en la hoja que el porcentaje de la ganancia bruto cae al 39%. Así podemos elegir cada vez un escenario distinto y ver el resultado directamente en la hoja.
Hay que tener en cuenta que al cerrar el formulario, las celdas variables conservan el valor del último escenario elegido.
Pero la real utilidad de usar Escenarios se evidencia al apretar el botón Resumen. Se abre este formulario



En la ventanilla Celdas de Resultado ponemos las celdas que queremos que aparezcan en el resumen. Si queremos que aparezca todo el total de ingresos, el total de gastos y el beneficio bruto, elegimos las celdas D10, D16 y D17



Al apretar Aceptar Excel crea una nueva hoja donde podemos ver un cuadro que compara los tres escenarios con la situación actual



Uno de los inconvenientes es que Excel usa la dirección de las celdas como nombre de la fila, lo que hace que la tabla sea prácticamente ilegible, es especial si se trata de un modelo con muchas líneas.
La técnica para superar este inconveniente es poner nombres a las celdas que estamos interesados en mostrar


Beneficio_bruto = Hoja1!$D$17
Dólar = Hoja1!$B$3
Euro = Hoja1!$B$2
Total_de_Gastos = Hoja1!$D$16
Total_de_Ingresos = Hoja1!$D$10

Volvemos a generar el resumen y obtenemos



Escenarios permite consolidar escenarios de distintas hojas, a condición que los modelos sean idénticos. Para combinar escenarios hay que apretar el botón Combinar que abre un formulario donde ingresamos los libros y las hojas que queremos combinar.
Como habrán notado, la hoja Resumen usa Agrupación y Esquema para ocultar ciertas filas. Si mostramos estas filas podemos ver el nombre del creador del escenario y la fecha en que fue creado



Dado que Excel crea una hoja cada vez que activamos la opción Resumen, podemos crear varias hojas que guarden distintos escenarios.
También podemos crear una tabla dinámica del escenario usando la opción Informe de tabla dinámica en el formulario de Resumen.

Technorati Tags: MS Excel

Calcular intereses por mes y días en Excel (o como construir una mega-fórmula)

Digamos que queremos calcular intereses por la demora en el pago de una cuenta o factura. Supongamos también que la tasa de interés cambia cada mes. Dados estos datos, lo que buscamos es desglosar los días transcurridos entre la fecha de vencimiento y la del pago efectivo por mes.

Lo que buscamos es crear una especie de calculadora en una hoja de Excel tal que si ingresamos el monto adeudado y las fechas, nos haga el cálculo del interés a pagar. Nuestra hoja se vería así (el archivo se puede descargar aquí)




Nuestro enfoque será resolver el modelo con fórmulas. Empecemos por señalar que tanto los datos en las celdas B2 y B3 como en el rango A6:A17 son fechas y no texto. Es decir, estamos trabajando con fechas que son números, como ya hemos explicado en el pasado.
Por ejemplo, si seleccionamos la celda A6, donde vemos la palabra "enero", veremos en la barra de fórmulas que en realidad es la fecha 01/01/2008



Para calcular los días por mes de la fecha de iniciación hasta la fecha de pago usamos una "mega-fórmula". Baste con ver la fórmula que usamos para calcular, dinámicamente, los días del mes de febrero que caen entre las fechas de principio y fin del intervalo para entender por qué la llamamos "mega"-fórmula

=SI(MES(A6)=MES($B$2),MES(A6)<=MES($B$3)),DIA(FIN.MES(A6,0)),0)-SI(MES($B$2)=MES(A6),$B$2-A6,0),SI(SI(MES($B$3)=MES(A6),$B$3-A6,0)<>0,SI(MES($B$3)=MES(A6),$B$3-A6,0)+1,0))

Para usar la función EOMONTH (o FIN.MES, de acuerdo a las definiciones del sistema) hay que tener instalado el complemento Analysis Toolpak.

La técnica que propongo para construir esta mega-fórmula consiste en crear las fórmulas parciales en columnas auxiliares y luego unirlas en una única fórmula.



La primer columna auxiliar es C donde calculamos la cantidad de días que caen dentro del mes de la fecha inicial con esta fórmula

=SI(MES($B$2)=MES(A6),$B$2-A6,0)

La función MES nos permite ver si el mes de la línea es el mes de la primer fecha. En caso afirmativo calculamos la diferencia de días entre la fecha de inicio (B2) y el primer día del mes (A6). En caso de coincidir el mes el resultado es 0.

En la columna D hacemos lo mismo pero en relación a la fecha de pago efectivo (B3), con la fórmula

=SI(MES($B$3)=MES(A6),$B$3-A6,0)

En la columna E calculamos los días de los meses que caen entre ambas fechas con la fórmula

=SI(Y(MES(A6)>=MES($B$2),MES(A6)<=MES($B$3)),DIA(EOMONTH(A6,0)),0)

La función EOMONTH (FIN.MES) nos permite calcular con exactitud los días de cada mes, también en años bisiestos.

En la columna F calculamos el total de días de cada mes con una resta sencilla

=E6-C6


A esta altura del partido tenemos calculados los todos los días por mes, excepto para el último mes del período. Para esto u Samos la columna auxiliar G con esta fórmula

=SI(D6<>0,D6+1,0)

Ahora podemos combinar todas estas fórmulas intermedias en una única mega-fórmula. Primero nos proponemos la fórmula en palabras, lo que nos ayuda a crear la lógica. Nos fijamos en las columnas auxiliares y decimos:

1 - si el mes de la fila no es el de la fecha de pago, calculamos los días restando la columna C de E (E-C);
2 - si el mes es el de la fecha de pago tomamos el resultado de la columna D y le sumamos 1.

En la columna H empezamos a crear la mega-fórmula

=SI(MES(A6)<MES($B$3),F6,G6)



Necesitamos que toda la fórmula esté expresada en términos de celdas en la columna A y B para lo cual empezaremos reemplazando F6 y G6 por sus equivalentes, las fórmulas que hemos puesto en las columnas auxiliares. Al reemplazar F6 y G6 nos queda

=SI(MES(A6)<MES($B$3),E6-C6,SI(D6<>0,D6+1,0))



Ahora tenemos que ocuparnos de E6, C6 y D6, que al reemplazarlas por las fórmulas de las columnas auxiliares nos dan la mega-fórmula que estamos buscando.

Es de notar que esta fórmula supone que las fechas de principio y final caen en el mismo año.

Para ver cómo construir un modelo real para el cálculo de interese por mora, consulten esta nota.

Quien esté interesado en recibir el archivo con el ejemplo y las fórmulas, debe dirigir el pedido a mi mail.



Technorati Tags: MS Excel

¿De quién es el próximo cumpleaños?

Ya he comentado que cálculos de fechas y horas es uno de los temas más consultados por los lectores de este blog. No es casualidad. La forma en que Excel trata las fechas y las horas no es intuitiva.
Un lector me pedía ayuda con un modelo para encontrar el nombre de la persona cuyo cumpleaños fuera el más cercano a la fecha corriente.

Consideremos esta lista de nombres con sus respectivas fechas de nacimiento



Si la fecha corriente es el 7 de octubre, la persona cuyo cumpleaños es el más cercano es, obviamente, María. Si le fecha fuera el 10 de octubre, el próximo cumpleañero sería Pedro.

Hacer este cálculo con Excel parece sencillo, diferencia en días entre dos fechas, pero tiene sus bemoles.
Excel representa cada fecha con un número entero que indica la cantidad de días transcurridos desde el 1ro. de enero de 1900. Por lo tanto, no podemos usar SIFECHA o usar una resta para calcular el intervalo entre la fecha actual y la fecha del cumpleaños de los miembros de la lista.
Tampoco podemos representar fechas en Excel sin el año, por lo que explico más arriba. Así que nuestra aproximación al problema será convertir todas las fechas de nacimiento a fechas del año corriente. Esto lo hacemos con la función FECHA creando una columna auxiliar (Aux 1)



Ahora creamos la columna auxiliar Aux2 donde calculamos la diferencia con la fecha de referencia, que hemos puesto en la celda B9



En la columna Aux2 vemos claramente que María es la cumpleañera, ya que la diferencia es 0 (el cumpleaños coincide con la fecha de referencia).
Como no siempre hay alguien que cumpleaños, necesitamos una forma de encontrar quien es el más cercano. A esta altura de los acontecimientos estaríamos tentados a usar la función MIN() lo cual es una buena idea pero con un pequeño problema. Pongamos como fecha de referencia el 10 de octubre



Si observamos con atención veremos que el próximo cumpleaños es el de Pedro. Pero si usamos el resultado de MIN(D2:D6) para encontrar el cumpleañero, el resultado será José ya que -252 es el menor de los números en el rango. Para corregir esto tenemos que sumar 365 (un año, expresado en días) a los resultados de Aux2 cuya fecha de nacimiento ya ha pasado.
En lugar de esto, cambiaremos de aproximación. Lo que haremos es crear una columna auxiliar donde calculamos la fecha (incluido el año) del próximo cumpleaños. Para esto usamos esta fórmula

=FECHA(2008+(C2<$B$9),MES(B2),DIA(B2))



La expresión lógica (C2<$B$9) calcula si la fecha del cumpleaños es anterior o posterior a la fecha de referencia. En caso de ser posterior (menor que la fecha de referencia) la expresión da VERDADERO que es sumado como 1 al año corriente.

Con esta aproximación, la columna Aux2 es innecesaria. Para obtener el nombre del cumpleañero usamos esta fórmula con INDICE y COINCIDIR

=INDICE(A2:A6,COINCIDIR(MIN(D2:D6),D2:D6,0))



Podemos reducir el número de columnas auxiliares a una reemplazando C2 en la fórmula de Aux3 por la fórmula de Aux1

=FECHA(2008+(FECHA(2008,MES(B2),DIA(B2))<$B$9),MES(B2),DIA(B2))



Otra corrección necesaria, si queremos usar este modelo también el próximo año, es reemplazar el "2008" en la fórmula por un calculo dinámico del año corriente

=FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2),DIA(B2))<$B$9),MES(B2),DIA(B2))

Finalmente, si queremos prescindir totalmente de columnas auxiliares, podemos usar esta fórmula matricial (matricial = introducir pulsando simultáneamente Ctrl+Enter+Mayúsculas)

={INDICE(A2:A6,COINCIDIR(MIN(FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2:B6),DIA(B2:B6))<$B$9),MES(B2:B6),DIA(B2:B6))),FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2:B6),DIA(B2:B6))<$B$9),MES(B2:B6),DIA(B2:B6)),0))}

Technorati Tags: MS Excel

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

Encabezamiento y pie de página en Excel.

Excel, tal como Word, permite incluir encabezamientos y pies de páginas que aparecen al imprimir las hojas. De esta manera podemos incluir información sobre las hojas o el cuaderno que estamos imprimiendo.
El proceso de agregar estos encabezamientos y pies de páginas en Excel es sencillo. Abrimos el diálogo de configuración de página con el menú Archivo- Configurar página



o pulsando el icono de vista preliminar



Si usamos este segundo método, pulsamos el botón Configurar para abrir el diálogo.



Tanto en el encabezamiento como para el pie de página existen tres secciones: derecha, izquierda y central. Para definir el contenido apretamos el botón de la zona deseada, nos ubicamos en la sección correspondiente y usamos el o los botones correspondientes.

Las posibilidades son (en Excel 2003):

&[Página]
&[Páginas]
&[Fecha]
&[Hora]
&[Ruta de acceso]&[Archivo]
&[Archivo]
&[Etiqueta]

Para incluir texto personalizado sencillamente lo escribimos en la sección deseada. Para cambiarle el formato a la fuente usamos el botón de formato (A).
También podemos incluir una imagen. Una vez incluida podemos usar el botón de configuración de imagen para cambiarle el tamaño.

Podemos combinar las distintas posibilidades, por ejemplo si la impresión incluye muchas hojas podemos definir un pie de página como este



con este resultado



También podemos ordenar la información en varias líneas usando Enter para crea una nueva.
Hasta aquí hemos resumido información básica que seguramente la mayoría de mis lectores conoce.

Ahora pasemos a algunas cuestiones más avanzadas.

A veces queremos agregar dinámicamente el nombre del usuario, tal como aparece en el sistema. Esto nos permite identificar quien guardó la página. Para esta tarea tenemos que usar una macro como ésta, donde usamos la función Environ de Vba para obtener el nombre del usuario

Sub Footer_user()
    Dim PS As PageSetup, WS As Worksheet
    
    Set PS = ActiveSheet.PageSetup
    
    PS.CenterFooter = Environ("username")
    
    
End Sub



Los encabezamientos y pies de página se definen a nivel de hoja. Para definirlos simultáneamente para más de una hoja tenemos que seleccionarlas previamente, abrir el menú Archivo-Configurar Página y apretar el botón Aceptar.
Para seleccionar varias hojas hacemos un clic a la etiqueta de cada una de las hojas a seleccionar mientras mantenemos apretado la tecla Ctrl. También podemos usar la tecla Mayúsculas para seleccionar un rango de hojas marcando la primer y la última hoja en el rango.
Para seleccionar todas las hojas del cuaderno usamos el menú contextual que se abre apuntado a la etiqueta de la hoja y apretando al botón derecho del mouse.



Para copiar la configuración de página de un cuaderno a otro tenemos dos posibilidades:

1 - Manualmente, movemos una hoja del cuaderno de origen al cuaderno al cual queremos copiar la configuración (asegurándonos que marcamos la opción "crear una copia"). Luego procedemos como señalamos más arriba.

2 - Usando una macro como ésta

Sub CopyHeaderFooter()
   Dim PS As PageSetup, WB As Workbook, WS As Worksheet
   Set PS = ActiveSheet.PageSetup
   For Each WB In Workbooks
     For Each WS In WB.Worksheets
       With WS.PageSetup
         .LeftHeader = PS.LeftHeader
         .CenterHeader = PS.CenterHeader
         .RightHeader = PS.RightHeader
         .LeftFooter = PS.LeftFooter
         .CenterFooter = PS.CenterFooter
         .RightFooter = PS.RightFooter
       End With
     Next
   Next
End Sub


Esta macro (tomada del sitio VitalNews) copia la configuración de página de la hoja activa a todas las hojas de todos los cuadernos abiertos al momento de correrla.

Technorati Tags: MS Excel

Autocompletar en Validación de Datos

Toda persona que haya usado Excel por algún tiempo conoce la funcionalidad Validación de Datos. Una de las características más "sexy" de esta funcionalidad es la posibilidad de crear listas desplegables con facilidad.

En notas anteriores ya hemos mostrado como podemos mejorar aún más esta función creando listas desplegables ordenadas o listas desplegables dependientes.

Pero lo que falta en validación de datos es la funcionalidad autocompletar. Esto es importante cuando tenemos listas con varias decenas o centenas de miembros.

Supongamos que creamos una lista desplegable con todos los países del mundo (o casi todos, según Wikipedia) . Esta lista tendrá 247 miembros. Para facilitar la búsqueda de un país determinado podemos ordenar la lista alfabéticamente, pero de todas maneras si buscamos Zimbabue o Yemen tendremos que pulsar el ratón o el teclado decenas de veces.
La solución es imitar en la lista de validación de datos el comportamiento Autocompletar como existe las celdas de una hoja. La idea es que si pulsamos B aparezca Bahamas, si pulsamos Br aparezca Brasil, etc.

Para lograr esto podemos incorporar a la hoja de Excel un control ActiveX. En este caso usaremos un cuadro combinado (ComboBox).



En nuestro modelo hemos puesto el cuadro combinado sobre la celda B1 y enseguida mostraremos como dirigimos la elección de la lista a la celda B3



Para poder seleccionar el objeto activamos primero el modo de diseño



Luego seleccionamos el cuadro combinado con un clic y abrimos el menú de las propiedades del objeto



En LinkedCell ponemos B3, el ListFillRange ponemos Hoja2!A1:A247 (que es donde tenemos la lista de países) y en MatchEntry ponemos 1-fmMatchEntryComplete



También podemos cambiar otras propiedades como la fuente y el aspecto del objeto.

Cerramos el cuadro de propiedades y cancelamos el modo de diseño haciendo clic sobre el icono.

Ahora al abrir la ventanilla del cuadro combinado vemos los primeros 8 miembros de la lista (también esta propiedad puede ser cambiada en el cuadro de propiedades del objeto). Los miembros aparecen en el orden en que se encuentran en el rango de origen.



Pero lo más interesante es que podemos escribir directamente en la ventanilla y los países aparecerán de acuerdo a las letras que hayamos escrito

Al poner B aparece automáticamente Bahamas



Si agregamos ahora "r", el valor se convierte en Brasil. Nótese que el valor en la celda ligada, B3, se actualiza instantáneamente

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Technorati Tags: MS Excel