sábado, 24 de septiembre de 2011

Modelización de datos en Excel (5)


Finalizamos esta serie con este post dedicado a presentar el archivo de Excel de la aplicación de ejemplo para gestionar las nóminas de una pyme, que ha servido de guía al lector a lo largo de estos 5 artículos:

https://www.dropbox.com/s/s1b4hcqzvnf45e3/Modelizaci%C3%B3n%20de%20datos%20en%20Excel%20%285%29.xlsm?dl=0



A continuación haremos unas puntualizaciones de apoyo para que el lector (con el archivo abierto de forma paralela) pueda comprobarlas:

  • El esquema final adoptado es un diagrama de copo de nieve con 4 tablas:
    • Áreas (Tabla de dimensión)
    • Trabajadores (Tabla de dimensión)
    • Cuentas de Tesorería (Tabla de dimensión)
    • Nóminas (Tabla de hechos)
  • Cada tabla se ha alojado en una Hoja de Excel distinta, especialmente es conveniente esto para que los registros de la tabla de hechos pueda crecer de forma prácticamente ilimitada.
  • Excepto el campo ID Nombre Trabajador, que se ha aprovechado para que sea una clave primaria, el resto de claves primarias son cadenas alfanuméricas de 3 caracteres en mayúsculas a elección del usuario, pero que es conveniente sean lo más descriptivas posibles. Por ejemplo, para el Área de Administración de la empresa, asignamos el código ADM. Debemos estar seguros de asignar un nombre definitivo, pues a posteriori no deberá cambiarse.
  • La tabla de hechos Nóminas tiene las claves externas de ID Nombre trabajador e ID Tesorería, pero también se ha incluido la clave externa de ID Área para crear una relación con la tabla de dimensión Trabajadores. El motivo se explica en el siguiente punto.
  • En la tabla de hechos Nóminas han de estar representados todos los campos importantes sobre los cuales queremos solicitar informes (como mínimo, los campos propios de la tabla de Nóminas, más todas las claves externas). La razón no es otra que casi todos los filtros e informes de tablas y/o gráficos dinámicos va a tener su origen de datos en esta tabla o en parte de ella.
  • No toda, pero la mayoría de información que queramos obtener de la aplicación, la podremos conseguir mediante los Filtros que podemos activar desde el botón que a tal efecto hay en el grupo "Ordenar y filtrar" de la Ficha "Datos".
  • Si frecuentemente queremos un informe concreto, podemos desarrollar una tabla dinámica (con su gráfico correspondiente si es necesario) a la cual podremos acceder de una forma inmediata. Unas de las ventajas que podemos conseguir con estas tablas es la posibilidad de agrupar las fechas por meses, trimestres, años, etc., además de poder trabajar por más de una dimensión a la vez (es decir, podemos obtener información de las comisiones que se pagan por área y, dentro de cada área, saber a que trabajadores se les paga y qué cantidad. En el archivo de Excel, se han desarrollado, a modo de ejemplo, 3 tablas dinámicas, una de ellas con gráfico incluido.
  • El archivo tiene un acabado "en bruto", puesto que su finalidad es servir como ejemplo. Para una utilización cómoda (además de agregar los campos que una empresa en concreto necesitara), debería 
    • Tener un panel con enlaces o botones para navegar por las distintas tablas e informes con más comodidad.
    • Tener botones para "Nuevo registro" y "Eliminar Registro" en todas las tablas, de forma que el código de las macros de dichos botones tuviese instrucciones para que las columnas de claves quedaran bloqueadas al usuario.
  • El usuario puede ver la forma de relacionar las tablas y las funciones empleadas (Listas desplegables con "Validación de datos" con la función DESREF como protagonista para obtener un rango variable atendiendo a la agregación de registros)
Nota: El archivo tiene formato .xlsm para que en un futuro pudiera albergar macros. (si tiene dudas sobre tipos de archivos, haga click en "Guardar archivos en Excel".


  José Manuel Pomares Medrano


viernes, 23 de septiembre de 2011

Modelización de datos en Excel (4)


Abordamos en este artículo el segundo y último de los esquemas de modelización de datos que habíamos empezado a estudiar: el Esquema o diagrama en Copo de Nieve.

Esquema o diagrama en Copo de Nieve

Una vez estudiado el Esquema o diagrama de Estrella, nos damos cuenta de que, a veces, las tablas de dimensión necesitan otras tablas de dimensión auxiliares.

El motivo es que, una de las características o dimensiones que puede tener un trabajador, es el área o departamento al que pertenece dentro de la empresa. Si para nosotros fuese importante este detalle, necesitamos construir una tabla de dimensión Áreas con su correspondiente campo de clave primaria y luego agregar en la tabla de dimensión Trabajadores un campo de clave externa para poder relacionar ambas tablas.


Es decir, cuando estamos en la tabla de dimensión Trabajadores, necesitamos cumplimentar un campo que exprese a qué área funcional de la empresa pertenece el trabajador de ese registro. Como en otros casos, esto se soluciona con una lista desplegable en el campo de la clave externa, cuyo rango origen será el campo de clave primaria en la tabla de dimensión Áreas.

Vemos a continuación como, mediante tablas de dimensión auxiliares de otras tablas de dimensión, se forma una estructura en forma de un copo de nieve, replicando la estructura de dentro hacia afuera de forma piramidal.


Todo lo dicho en el artículo Modelización de datos en Excel (3) sobre claves primarias y externas y sobre la forma en la que se relacionan las tablas, es válido para la relaciones entre las tablas de dimensión .

Finalmente, el esquema de tablas de nuestro ejemplo de una aplicación en Excel para gestionar las nóminas, es la siguiente:


En el próximo post, veremos como ha quedado el archivo Excel ya prácticamente terminado y funcionando, con todas las relaciones realizadas (listas deplegables y demás fórmulas y funciones necesarias) y algunos informes de ejemplo realizados con tablas y gráficos dinámicos, resaltando las ventajas de haber construido una aplicación con una estructura "normalizada" que, entre otras ventajas...

  • Evita redundancias
  • Posibilita un funcionamiento ilimitado en el tiempo tan solo agregando registros y sin necesidad de copiar y pegar constantemente en nuevas hojas o libros.
  • Tiene la flexibilidad adecuada para no ser necesario diseñar y confeccionar un informe cada vez que los administradores de la empresa quieran obtener alguna información para la toma de decisiones.
Artículos relacionados:

Modelización de datos en Excel
Modelización de datos en Excel (2)
Modelización de datos en Excel (3)
Modelización de datos en Excel (5)


  José Manuel Pomares Medrano


Modelización de datos en Excel (3)


Continuando con  el artículo Modelización de datos en Excel (2) e intentando acercar las técnicas del Business Intelligence al diseño de aplicaciones en Excel, vamos a ver ahora las formas de relacionar las tablas que hemos aprendido a conformar. Es evidente que las tablas creadas deben tener relación entre ellas, de hecho, lo hacen mediante campos clave.

Claves externas (foreign keys)

Para que cada registro de una tabla de hechos quede indentificado por una dimensión, es necesario agregar a tal efecto un campo en dicha tabla (la de hechos) que adopte uno de los valores de la clave primaria que tiene la tabla de dimensión, de forma que exista una especie de "cordón umbilical" entre ambas tablas. Las claves que se agregan en la tabla de hechos se llaman claves externas o foreign keys y sí pueden tener duplicados, evidentemente:


A este tipo de relación de la llama "relación de uno a varios"

Por lo tanto, cada tabla de dimensión necesita tener relacionado su campo de clave primaria con un campo de clave externa que debe haber a tal efecto en la tabla de hechos. Es una especie de cordón umbilical que sirve para que queden relacionadas.

Centrándonos en el ejemplo que iniciamos en artículos anteriores y con perspectiva práctica, podemos adivinar que cuando estamos en la tabla de hechos, el campo de ID Tesorería lo cumplimentaremos mediante una lista desplegable cuyo origen de datos es la columna ID Tesoreria de la tabla de dimensión


Para recordar como hacer una lista desplegable adecuada para este caso, ver los artículos:


A estas alturas, es necesario explicar que existe otro motivo por el cual es necesaria la creación de campos de clave primaria con valores únicos. De hecho, algunos lectores se preguntarán:


¿Por qué es necesario el campo ID Tesorería si se podría haber aprovechado el campo Tesorería para convertirlo en campo de clave primaria asegurándonos de no repetir un mismo nombre? 

La respuesta es que, si lo hiciesemos así, en el futuro (por ejemplo) el banco podría cambiar de nombre y entonces las claves externas en la tablas de hechos, se verían sin sus "hermanas gemelas":


Es fácil adivinar los problemas que crearíamos si en un futuro cambiáramos por otra distinta la clave primaria "AAA": en la tabla de hechos habria varios registros sin la dimensión "hermana". Es decir romperíamos la llamada "integridad referencial". Con claves primarias que sean independientes de las descripciones, se salvaguarda la posibilidad de actualizar las segundas, dejando siempre intactas las primeras.

En resumen: las claves (primarias y externas), no pueden cambiarse una vez diseñada la aplicación y puesta en marcha. Esto es solucionable en Excel mediante una macro que bloquee las celdas de claves después de haber añadido un registro.

Pero demos un paso adelante y veamos como, mediante la relación de unas tablas con otras, podemos formar esquemas o diagramas que, según su forma tienen unas u otras características:

Esquema o diagrama de "Estrella"

Hasta ahora, podemos concluir que las 3 tablas que hemos construido tienen la siguiente relación:



La tabla de dimensión de Trabajadores y la tabla de hechos quedan relacionadas por el campo clave "ID Nombre trabajador". La tabla de dimensión de Cuentas de Tesorería y la Tabla de Hechos quedan relacionadas por el campo clave "IDTesorería" (como hemos visto anteriormente en este artículo).

El caso que nos ocupa es sencillo y apenas necesita 2 tablas de dimensión, pero en caso de que el ejemplo fuera más complicado debería ampliarse con otras tablas de dimensión:


Esta filosofía de relacionar las tablas (modelizar los datos) se llama "Esquema o diagrama de Estrella", debido a la forma adoptada por las tablas de dimensión (alrededor de la tabla de hechos en forma de estrella).

Nuestro siguiente artículo será estudiar el "Esquema o diagrama de Copo de Nieve".

Artículos relacionados:

Modelización de datos en Excel
Modelización de datos en Excel (2)
Modelización de datos en Excel (4)
Modelización de datos en Excel (5)


  José Manuel Pomares Medrano



Modelización de datos en Excel (2)


Durante este tutorial vamos a trabajar sobre un ejemplo supuesto muy sencillo:

Un Director Financiero quiere construir una aplicación en Excel para tener un control de las nóminas que se van generarndo mes a mes de los empleados (producción, comerciales, administrativos y gerencia) y sus remuneraciones (fijas, horas extra y comisiones), así como de los pagos realizados a los trabajadores.

¿Cómo se modelizan los datos en Excel?

Como se decía en el artículo precedente a este (Modelización de datos en Excel), para modelizar y montar una estructura de datos adecuada, es necesario organizar los datos agrupándolos, según unos criterios determinados, mediante tablas de dos tipos:

1) Tablas de hechos (fact tables)

En estas tablas deben ir los datos que representen los hechos principales a medir y a vigilar mediante indicadores. Suelen ser los datos que se multiplican en mayor cantidad a medida que pasa el tiempo, luego también suele ser imprescindible que en una tabla de hechos exista como mínimo un campo de fecha y otro que represente el hecho en sívalorado en euros. En otras palabras (y simplificando para entenderlo mejor), los datos de las tablas de hechos suelen expresar: ¿cúando ha ocurrido un hecho? y ¿qué valor tiene el hecho?.

En nuestro ejemplo de aplicación para el control de nóminas, el hecho principal es que cada mes se generan una serie de nóminas en la empresa. Las nóminas podrán ser de un trabajador u otro, pagarse mediante una cuenta de tesorería u otra, pero cada mes se generan nóminas... y así... indefinidamente. Es una tabla que multiplicará sus registros muy rápidamente y cada registro (cada hecho) es una nómina que está asociada a una fecha de generación (el último día de cada mes). En definitiva, la Tabla de Hechos será la tabla de nóminas generadas con los siguientes campos, por ejemplo:


Fecha Nómina: La fecha de devengo de la nómina. Normalmente el último día del mes.
Devengos: Suma de las cantidades principales que componen la nómina (salario base, antigüedad, etc.)
Horas: Horas extras a incluir en la nómina.
Incentivos: Comisiones y similares por productividad o ventas.
S.Social: Deducción de la Seguridad Social que va a cargo del trabajador
IRPF: Deducción del IRPF que corresponde al trabajador por su nómina.


Podemos observar que, los datos que de momento se han incluido en la tabla de hechos son los que cumplen las siguientes condiciones:

  • Todos los meses varían. Es decir, la fecha, el importe devengando, el IRPF, etc... siempre varían en cada nómina. Podría hacerse una lista muy larga de fechas e importes, sólo limitada por la duración de la vida de la empresa.
  • Las cantidades (devengos, horas, comisiones e IRPF) y la fecha de cada nómina son lo que queremos cuantificar y son los datos protagonistas.

2) Tablas de dimensión (dimensión tables)

Cada una de las tablas de dimensión debe contener los valores que definen a los hechos diciendo quién los ha realizado y qué características tienen.


Por ejemplo, el dato: Comisión = 105,56 € en 31/01/2011 no tiene utilidad "per se", sino que necesita de otros datos complementarios (dimensiones) que expliquen quién ha recibido la comisión y por qué entidad financiera se pagará. Las dimensiones son aquellos conceptos por los que después se consultará en los informes: es frecuente que un Director Financiero quiera consultar las comisiones por cada comercial, las nóminas por cada área o departamento, etc.


En otras palabras (y simplificando para entenderlo mejor), las tablas de dimensión suelen decir ¿qué tipo de hecho ha ocurrido? ¿quién hay detrás del hecho?

Claves primarias (primary keys)

Las tablas de dimensión necesitan que uno de los campos sea una clave primaria o primary key para que cada registro quede identificado inequívocamente. Esto significa que en ese campo NO PUEDE HABER DUPLICADOS. En el mundo del Business Intelligence, las claves primarias suelen ser números enteros que se generan mediante autonumeración (por norma y aunque haya campos en cuyos registros no haya duplicados), pero en Microsoft Excel y dado que no es una aplicación diseñada como base de datos relacional, debemos flexibilizar algunas normas y esta es una de ellas: si en una tabla de dimensión hay un campo que contiene datos para los que no va a haber duplicados, lo aprovecharemos como clave primaria, pero si no lo hay, agregaremos uno "ad hoc" cuyo contenido sea preferentemente significativo, no demasiado largo y por supuesto, sin duplicados. Veremos esto a continuación y sobre la práctica de ejemplo.

Para nuestro ejemplo y de momento, construiremos 2 tablas de dimensión:

1) La tabla de trabajadores será una de las dimensiones que defina quién está detrás de cada nómina y los datos íntimamente relacionados con cada trabajador (fecha de nacimiento, situación laboral, etc.)


ID Nombre trabajador: Apellidos y Nombre del trabajador, que será la clave primaria (ID), puesto que es imposible que nos veamos obligados a poner un mismo nombre a 2 trabajadores de una misma empresa. Es decir, no habrá duplicados.
Fecha nacimiento: Fecha de nacimiento del trabajador.
Situación: Alta, Baja o Enfermedad.
Ultima fecha: Última fecha de cambio de situación.

2) La tabla de cuentas de tesoreria


ID Tesorería: Código de la cuenta de tesorería. Este campo se ha creado "ad hoc" porque es ciertamente posible que una empresa pueda tener dos cuentas de tesorería en una misma entidad financiera, o bien que maneje varias cuentas de efectivo de caja (como es el caso del ejemplo). Por lo tanto, el nombre de la tesorería (que es el siguiente que explicamos) no nos sirve como clave primaria.
Nombre tesorería: Nombre de la entidad financiera o de la cuenta de efectivo.
Cuenta: Nº de cuenta de tesorería.

En el siguiente post, nos adentramos algo más en otras teorías del Business Intelligence para conocer las principales formas (esquemas o diagramas) que hay de relacionar las tablas que hemos creado. Concretamente veremos el Esquema en Estrella y el Esquema en Copo de Nieve. Previamente se estudiarán las claves que debe contener una tabla de hechos (claves externas).

Artículos relacionados:

Modelización de datos en Excel
Modelización de datos en Excel (3)
Modelización de datos en Excel (4)
Modelización de datos en Excel (5)


  José Manuel Pomares Medrano


jueves, 22 de septiembre de 2011

Modelización de datos en Excel


Iniciamos con este post, una serie de 5 artículos (quizás los más importantes de este blog y los que primero deberían ser leídos porque componen la base que todo usuario de Excel debería, al menos, conocer mínimamente), dedicados a conocer la forma ideal de organizar y estructurar los datos (modelizar) en un libro de excel con el fin de evitar los errores que más frecuentemente se comenten a la hora de diseñar una aplicación de Excel en el ámbito de la Dirección Financiera, Comercial o en cualquier otro contexto.

¿Cuales son los problemas más habituales al diseñar nuestras hojas de cálculo?

Cuando detectamos la necesidad de diseñar una aplicación en Excel para gsetionar determinados procesos en cualquier área de una empresa, nos abalanzamos sobre la Hoja1 en blanco que genera automáticamente Excel al abrirlo y empezamos a "pintar"aquello que nosotros queremos ver en un momento dado. Una vez hecho, hacemos doble click en la pestaña de la hoja y le asignamos un nombre, por ejemplo: "Factura Enero" o bien "Nóminas Enero". La imagen de lo descrito, bien podría ser similar a este sencillo ejemplo:


Tenemos la sensacion de que está todo controlado hasta que llega Diciembre y necesitamos sumar todos los totales mensuales para obtener el anual. Para ello estamos obligados a crear una nueva hoja consolidando todo. Aun así, nos llegarán más problemas con esta estructura de datos en la que cada mes corresponde a una hoja:

  • ¿Y si el gerente nos pide los totales entre Julio de un año y Junio del siguiente año... tendremos que construir otra tabla para consolidar todos los meses?
  • ¿Y si queremos saber los totales por trabajador... también tendremos que construir otra tabla a tal efecto?
  • ¿Y si el Director de Producción nos pide un resumen de los sueldos de todos los trabajadores de su departamento, de los últimos 5 meses... tendremos que diseñar otra tabla para poder satisfacer la necesidad del responsable del área productiva?
  • ¿Y si queremos saber el IRPF trimestral... tendremos que construir otra tabla para ello?
  • ¿Y cuando llevemos 3 años acumulados... tendremos que manejar 36 pestañas (una por cada mes)?

De esta forma, nos pasaremos los días continuamente construyendo informes y más informes con hojas y más hojas en un libro... siempre copiando y pegando en el mejor de los casos. (por no hablar de algunos usuarios que agravan hasta el límite todo lo comentado, alojando en cada libro o archivo un año completo... o peor aun en cada libro o archivo un mes sólamente... o mucho peor..¡¡¡¡Horror!!!! en cada archivo, una factura)

La mayoría de las veces, se da la circunstancia de que hay que escribir una y otra vez un mismo nombre de cliente, trabajador, etc. o actualizar una fecha en cada hoja o en cada tabla, con la consecuente pérdida de tiempo y de fiabilidad, puesto que todo esto se presta a multitud de errores y olvidos.

¿Por qué ocurre esto?

Todos estos errores ocurren porque a la hora de diseñar nuestro trabajo en Excel, lo hacemos pensando gráficamente en lo que necesitamos al final (una tabla con unos determinados campos) y simple y directamente lo plasmamos construyendo una tabla a imagen y semejanza de nuestro primer pensamiento. Es decir, empezamos construyendo al principio lo que queremos como objetivo final. Como dice el refrán: pan para hoy (rapidez) y hambre para mañana (lentitud, problemas y errores): la arquitectura de los datos no es la correcta para que pueda ser consultada de forma dinámica y desde varios puntos de vista o dimensiones.

¿Qué es modelizar los datos?

Modelizar significa organizar todos los datos que tenemos a nuestra disposición, agrupándolos según unos criterios determinados, mediante tablas. Cada grupo de datos (tabla) contiene uno o varios campos que tienen alguna relación con algún/os campo/s de otras tablas.

La dificultad y el "arte" de modelizar estriba en decidir qué datos pueden y deben estar agrupados en una misma tabla y, posteriormente, decidir como se van a relacionar estas tablas. Para ello, entre otros requisitos, es fundamental conocer a fondo la empresa y el contexto de necesidades que mueve a diseñar una aplicación en Excel.

¿Como se modelizan los datos?:  Este es el principal contenido de nuestro siguiente artículo...


  José Manuel Pomares Medrano

sábado, 17 de septiembre de 2011

Cómo automatizar una acción al abrir Excel


Es muy frecuente que cada vez que entramos a una aplicación de Excel , tengamos que realizar una o varias acciones siempre de la misma forma. Por ejemplo, seleccionar una hoja, poner la fecha del día en un determinado campo, mostrar un cuadro de diálogo, etc.

En este artículo, vamos a ofrecer unos primeros pasos muy sencillos en programación Visual Basic for Appliations (VBA) en Excel, para conseguir que al abrir Excel, se ejecute la acción deseada. 

Pongamos un sencillo ejemplo, en el que deseamos que al abrir Excel se realicen las siguientes acciones:

  1. Seleccionar la Hoja3.
  2. Introducir la fecha del día en la celda A1 de la Hoja3.
  3. Activar el modo Pantalla completa de Excel.
(Además, deseamos que el usuario no perciba en pantalla que se realiza ninguna de las dos primeras acciones)

Es posible conseguir esto de dos formas. En este artículo lo vamos a hacer creando un procedimiento para el evento "Open" de un libro (también se puede hacer creando una macro y asignándole el nombre de "Auto_open", aunque esta opción puede tener alguna incompatibilidad en algunas circunstancias: Limitaciones de una macro Auto_open.)

En resumen, el proceso es decirle a Excel mediante código VBA, que al abrir el libro (Workbook), ejecute las instrucciones que nosotros queramos. Esto se hace introduciendo dicho código con las instrucciones, en la colección de objetos "ThisWorkbook" (que también está considerado como un objeto) e indicando que ocurran cuando se abra (Open). Veamoslo paso a paso:

1) Creamos un nuevo libro de Excel e inmediatamente guardamos el archivo dándole el nombre que deseemos (en la imagen de abajo se llama "Libro1") y seleccionando el tipo de archivo: "Libro de Excel habilitado para macros". Hay que recordar que si deseamos hacer este ejercicio en un archivo que ya teníamos, este debe estar guardado con el formato anteriormente mencionado (habilitado para macros).


2) Hacemos click en la Ficha "Programador" (si no la tenemos activada y no sabemos como hacerlo, ver: La ficha "Programador") y después en Visual Basic (a la izquierda). Con esto, se abre el editor de Visual Basic y deberíamos ver el panel "Explorador de proyectos" a la izquierda. (si no es así, podemos abrirlo con Ctrl+R o bien desde el comando "Explorador de proyectos" del menú "Ver").

3) Dependiendo de los complementos y archivos de Excel que tengamos habilitados y abiertos, veremos más o menos "Proyectos" en el panel abierto en el punto 1), pero entre ellos, debe estar el del archivo de Excel al que queremos asignar el código. Por ejemplo:



4) Como vemos en la imagen anterior, aparece la colección de objetos "ThisWorkbook", como un objeto más dentro de "Microsoft Excel Objetos". Pues debemos hacer doble click en "ThisWorkbook" para abrir a la derecha la ventana de código y seleccionar en el desplegable de la izquierda (Objetos) "ThisWorkbook" y en el desplegable de la derecha (Procedimientos) "Open", obteniendo lo siguiente:



5) Vemos que automáticamente se escriben las instrucciones Sub y End Sub (además de otras palabras clave y parámetros) como inicio y final del procedimiento. Nuestro código debemos situarlo entre las dos líneas que se han generado automáticamente y  por lo tanto, debe quedar todo así:


Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    Sheets("Hoja3").Select

    Range("A1") = Now()

    Application.DisplayFullScreen = True



End Sub


6) Cerramos el editor de Visual Basic, cerramos y guardamos el libro al salir y volvemos a abrir el libro. Podemos comprobar que, automáticamente y sólo con la apertura del archivo, Excel selecciona la Hoja3 (Sheets("Hoja3").Select), escribe en la celda A1 la fecha y hora actual (Range("A1") = Now()) y además lo visualizamos a pantalla completa (Application.DisplayFullScreen = True). También hemos podido comprobar que, de todo el proceso, sólo vemos el resultado final gracias que  Excel no va actualizando en pantalla lo que va procesando interiormente (Application.ScreenUpdating = False)

(Sólo tenemos que presionar ESC para salir del modo pantalla completa)


Esto es tan sólo un ejemplo sencillísimo, pero basta con escribir un código diferente diseñado con alguna finalidad y situarlo en el lugar explicado (entre las instrucciones Sub y End Sub), para obtener la automatización de una tarea.

En siguientes artículos pondremos más ejemplos de código. También en xltoday.net hay muchos ejemplos de código VBA para copiar y pegar en nuestro "Thisworkbook", así como en Cyta.com y en Ron's Excel Tips .


  José Manuel Pomares Medrano


La función DESREF en Excel


Cuando queremos hacer referencia a una celda o rango de celdas, conocemos varias posibilidades de hacerlo pero, ¿qué ocurre si dicha celda o rango puede variar de posición o de tamaño?

En el siguiente enlace, se puede descargar un archivo de Excel donde podemos encontrar el ejercicio que seguiremos en este tutorial (Ejercicio 1) y otro ejercicio muy útil en la práctica (Ejercicio 2) donde se hace una lista desplegable, cuyo origen de datos es un rango variable generado por la función DESREF.

https://www.dropbox.com/s/5osjuctb0u6xrl6/La%20funci%C3%B3n%20DESREF%20en%20Excel.xlsm?dl=0

Por ejemplo, si un rango está compuesto por una lista de artículos de almacén, es normal que varíe su tamaño, pues los artículos del almacén de una empresa aumentan o disminuyen.

Por otro lado, un rango que varía de posición y tamaño, pudiera ser uno que se refiera a un período de fechas que varía a elección del usuario (por ejemplo, mediante unas Listas desplegables). Por ejemplo, seleccionar un período entre Enero y Marzo....

Rango 1


o entre Febrero y Junio:

Rango 2


En estos casos, si la celda o el rango puede tener posiciones y tamaños distintos (como los aludidos anteriormente con los ejemplos), hay una forma de hacer referencia a ellos mediante la función DESREF. Esta función no es del todo fácil de entender, precisamente por que alude a referencias que varían en el espacio y es necesario entender cómo se manejan "las coordenadas".

La función DESREF devuelve una selección de celda o rango, cuya situación se define con las coordenadas que se escriben en parámetros separados por el signo punto y coma. La sintaxis de la función es:


Por ejemplo, si quisieramos sumar los importes del Rango 2 de la imagen anterior, deberíamos escribir:

                                               =SUMA ( DESREF (A1;1;1;1;5) )

Donde... se suma un rango cuyo extremo superior izquierdo empieza a definirse 1 fila más hacia abajo y 1 columna más a la derecha comenzando a contar desde la celda A1 (celda de referencia). Por último, los últimos  parámetros significan que el rango tiene 1 fila de alto y 5 columnas de ancho.
(Es necesario saber que los parametros de "filas" y "columnas" pueden ser positivos o negativos (cuando son negativos, el nº de filas se cuenta hacia arriba y nº de columnas se cuenta hacia la izquierda).

Una vez entendido esto, nos damos cuenta de que, hasta ahora sólo hemos conseguido definir un rango según unas "coordenadas" fijas. Pero lo interesante es que algunas de las coordenadas varíe en función de alguna variable que podamos definir como usuario (en definitiva, que el rango sea dinámico). Por ejemplo, podemos cambiar en la fórmula anterior:

                                              =SUMA ( DESREF (A1;1;B4-1;1;B5) )

... con lo que conseguimos que el rango tenga su inicio en una celda que depende del valor de otra celda en la que nosotros podemos introducir un valor a nuestra elección:

Ejercicio 1


Vemos en el Ejercicio 1 que, poniendo el nº de mes en B4 y la cantidad de meses que deseamos en B5, obtenemos la suma del rango en B6. Este es un ejemplo muy simple y con fines didácticos, pero no demasiado aplicable en la práctica. 


  José Manuel Pomares Medrano


Listas desplegables en Excel (4)


Después de conocer el resto de formas de generar una lista desplegable en Excel en los anteriores artículos de esta serie, nos adentramos en la última de las posibilidades, que es conseguir una lista desplegable (en este caso, Excel lo llama "Cuadro combinado") desde el comando "Insertar" que hay en el grupo "Controles" de la Ficha "Programador".

(si en su aplicación Excel no está visible la Ficha "Programador", vea el artículo: La Ficha "Programador" en Excel)


Una vez hacemos click en el control de formulario (segundo por la izquierda arriba), podemos "dibujar" el control donde queramos en nuestra hoja de cálculo, con tan solo arrastrar en diagonal de arriba a abajo y de izquierda a derecha.


Podemos ver ejemplos de cuadros combinados en esta imagen del CME Pymes:


Varias son las características que tenemos que resaltar de estas listas desplegables realizadas con cuadros combinados en Excel:

  • La lista desplegable ofrece al usuario una serie de items a elegir y, una vez realizada la selección, el valor no queda introducido en ninguna celda, tan sólo es presentado en pantalla por el cuadro combinado. Lo que sí hace la lista desplegable es generar un número que asocia al item elegido (asocia un número de orden a cada uno de los items que presenta la lista desplegable) y lo introduce en una celda que nosotros elegimos. Una vez ocurrido esto, podemos condicionar cualquier objeto, acción, cálculo, etc. al número asociado al item que se ha elegido. En pocos casos esto constituye una ventaja frente a las listas desplegables generadas con "Validación de datos".
  • Los cuadros combinados pueden colocarse en cualquier posición de una hoja de cálculo, inlcuso encima de gráficos, imágenes o formas.
  • Se puede elegir si se imprimirá o tan sólo lo veremos en pantalla.
  • Ofrecen la posibilidad de que si se insertan filas o columnas, el cuadro combinado se desplace (quedando ligado a una celda concreta) o que mantenga su posición respecto sin desplazarse.
  • Es posible elegir la cantidad de items que se mostrarán sin necesidad de utilizar una barra de desplazamiento (en las listas desplegables realizadas con "Validación de datos" se muestran hasta 8 items sin necesidad de recurrir a la barra de desplezammiento. Si hay más items, es necesaria dicha barra))

Todo lo anterior es posible configurarlo haciendo click con el botón derecho en el cuadro combinado, eligiendo "Formato de control..."  y seleccionando/cumplimentando las opciones adecuadas en el cuadro de diálogo que aparece:


Después de todo lo anterior, queda claro que utilizar un cuadro combinado para generar una lista desplegable debe estar motivado principalmente por cuestiones "gráficas" y de posición.






viernes, 16 de septiembre de 2011

Nombres de celdas o de rangos en Excel


Cuando tenemos que referirnos al contenido que hay en una determinada celda o en un grupo de celdas, lo podemos hacer escribiendo su referencia. Por ejemplo, =Hoja!B1 (en el caso de la celda B1 que está en la Hoja1) o por ejemplo =B1:B5 (en el caso de un rango de celdas que está en la hoja actual).

A veces, hacer estas referencias de la forma que todos conocemos (haciendo click en la celda o seleccionando el rango deseado mientras escribimos una fórmula en una celda) es rápido y eficiente, pero en otras ocasiones, es posible que aparezcan algunos inconvenientes:


  • Hacer referencia a una celda o rango seleccionándola directamente allí donde se encuentre, pudiera ser más molesto o lento de lo deseado.
  • Si tenemos que revisar una fórmula que tiene referencias a una celda o a un rango, probablemente no vamos a reconocer a primera vista que tipo de datos contiene esa referencia. Por ejemplo, la fórmula =B4 + C4  no nos dice que contienen las celdas B4 y C4.

Para solucionar esto, existe la posibilidad de asignar un nombre descriptivo a las celdas o rangos.



Siguiendo el ejemplo del último punto anterior y suponiendo que B4 contiene una base imponible y que C4 contiene el IVA, podemos asignar nombres a B4 y a C4, de forma que podamos relacionar esas celdas con sus conceptos cuando tengamos que revisar la fórmula. De esta forma y una vez asignados los nombres "Base" e "IVA", para sumarlos en una fórmula sólo tenemos que poner: =Base+IVA.



De la misma forma, si tenemos un rango donde aparecen las ventas realizadas por una serie de vendedores, por ejemplo, F5:F24, podemos asignarle el nombre Ventas y en adelante cuando tengamos que operar con el rango sólo tenemos que poner directamente (por ejemplo)  =SUMA(Ventas) o PROMEDIO(Ventas), etc.




  José Manuel Pomares Medrano

jueves, 15 de septiembre de 2011

Listas desplegables en Excel (3)


Además de la opción de generar listas desplegables vista en el post "Listas desplegables en Excel (2)" , tenemos la posibilidad de generarlas mediante el cuadro de diálogo "Validación de datos" que está en la ficha "Datos" de la cinta de opciones y seleccionando en el campo "Permitir:" la opción "Lista":




Con esta opción, tenemos las siguientes ventajas:

  • Los items de la lista desplegable pueden ser:

    • Elegidos arbitrariamente por nosotros e introducidos a mano, separados por el signo punto y coma, en el campo "Origen:" del cuadro de dialogo de "Validación de datos".


    • Elegidos de un rango de celdas de la hoja activa o de otra hoja (en Excel 2003 sólo de la hoja activa).



    • Elegidos de un rango variable (con más o menos items) mediante la función DESREF. Es decir, podemos tener una lista desplegable con los nombres de nuestros clientes, pero si añadimos un cliente, automáticamente aparecerá después en nuestra lista desplegable.


    • Elegidos de un rango al que le hemos asignado un nombre (Ver: Nombres de celdas o de rangos en Excel). Si utilizamos esta opción para el origen de los items, podemos permitir que se introduzcan valores que no están incluidos en la lista siempre y cuando:
      • Se active la casilla "Omitir blancos".
      • En el rango exista una celda en blanco al menos.


  • Las listas desplegables realizadas con "Validación de datos" se crean en una celda y el valor elegido queda introducido en ella directamente.
  • Podemos utilizar mensajes informativos para que el usuario tenga información, por ejemplo, de que información debe introducir en la celda. Esto se consigue en la pestaña "Mensaje de entrada" del cuadro de diálogo que ya conocemos de "Validación de datos":


      Con el resultado de...


  • Podemos utilizar mensajes de error para el caso de que el usuario no seleccione correctamente un item o bien intente introducir un valor no adecuado. Esto se consigue de forma muy similar al mensaje de entrada, pero en la pestaña "Mensaje de error" del cuadro de diálogo que ya conocemos de "Validación de datos".
  • Si deseamos, por ejemplo, que una tabla contenga listas desplegables en todos las celdas de una columna, podemos replicar las listas desplegables tan sólo con utilizar "Autorrellenar".

Ver Listas desplegables en Excel
Ver Listas desplegables en Excel (2)
Ver Listas desplegables en Excel (4)

 

miércoles, 14 de septiembre de 2011

Listas desplegables en Excel (2)


Desarrollamos con más detalle y otra metodología (video-tutorial) la primera de las clases de listas desplegables que podemos utilizar en Excel.

Nos referimos a las listas desplegables generadas mediante el menú contextual que obtenemos con el botón derecho del ratón:


Cuando tenemos una serie de items introducidos en un rango de celdas a lo largo de una columna (de forma similar a la imagen anterior), Excel reconoce la posibilidad de que el usuario pueda necesitar introducir algunas veces más alguno de los items ya utilizados. De esta forma, cuando hacemos click con el botón derecho en la celda inmediatamente inferior a la última de la lista, obtenemos un menú contextual como el siguiente:


Podemos ver que la antepenúltima opción es "Elegir de la lista desplegable...". Una vez que la seleccionamos, obtenemos una lista de los items que hay en las celdas inmediatamente superiores a la celda donde hemos obtenido el menú desplegable.

Tenemos que tener en cuenta que:
  • Si la lista que ya tenemos en Excel tiene algún item repetido, sólo lo muestra una vez en la lista desplegable.
  • La lista desplegable que generamos, sólo contiene items que ya hayan sido introducidos en la lista que tenemos ya en Excel.
  • Si la lista que ya tenemos en Excel está fraccionada (no tiene todos los items seguidos), sólo aparecerán en la lista desplegable los items contiguos del último tramo de más abajo.
  • Si hacemos click en otra celda que no sea la inmediatamente inferior a la lista que ya tenemos en Excel, no se generará ninguna lista desplegable.
  • Si la lista que ya tenemos en Excel contiene items de fechas o números, estos no se incluirán en la lista desplegable. Sólo se inlcuyen en las listas desplegables generadas mediante el menú contextual, los items que son cadenas de texto (o texto mezclado con números).
Veamos un video-tutorial con todo esto:




  José Manuel Pomares Medrano

La ficha "Programador" en Excel


Muchas de las opciones avanzadas que tenemos a nuestra disposición en Microsoft Excel, están en la ficha "Programador". Entre las principales, se encuentran aquellas que están referidas a la automatización de tareas (macros), programación en Visual Basic for Applications (VBA) y utilización de controles de formulario (botones, cuadros combinados, listas desplegables, casillas de verificación, etc.).



Si su aplicación Excel 2007 o 2010 no tiene la ficha "Programador" se debe a que no queda habilitada durante la instalación, pero puede habilitarla para que quede visible:


  • En Excel 2007, debemos hacer click en el Botón de Office / Opciones de Excel / Más frecuentes / Mostrar ficha de programador en la cinta de opciones.



  • En Excel 2010, debemos hacer click en Archivo / Opciones / Personalizar cinta de opciones . Una vez obtenemos el cuadro de diálogo, seleccionamos "Fichas principales" de la lista desplegable que hay arriba a la derecha y después activamos la casilla "Programador" que hay en el panel de Fichas Principales:




  • En el caso de Excel 2003 no existe ficha "Programador" puesto que no hay cinta de opciones, pero a la mayoría de comandos podemos acceder desde el menú Herramientas / Macro...  y también haciendo click en el menú Ver / Barras de herramientas / Cuadro de controles


  José Manuel Pomares Medrano