Formulario con listas anidadas en Excel

Estos formularios tienen datos estructurados y contarán con la característica especial de tener listas desplegables anidadas o dependientes entre sí. Esto quiere decir que el contenido de una lista despleglable dependera de otra. 

Por ejemplo, cuando se complete el dato país, región, ciudad, etc. Se tratará de información que se encuentra enlazada, por lo que sería prudente que el usuario fuera viendo sólo las opciones de acuerdo a elecciones previas. 

Es así como una fundamental característica es que limita al usuario a completar con información que se encuentra enlazada, por tanto, se disminuye la cantidad errores que se podrían cometer en el llenado.

En concreto, usando listas desplegables se resuelven los siguientes problemas:

-Facilita la entrada de datos, es decir, limita la entrada a determinados elementos que define un usuario.

-Disminuye la probabilidad de error al ingresar los datos.

-La dificultad fundamental que resuelve es cuando existe información importante que debe ir en el formulario y el usuario no dispone de ella, por tanto, estas listas entregan los lineamientos para poder realizar el llenado correctamente.

Algunas aplicaciones

-Documento de trabajo en alguna organización para seleccionar el responsable de un area pero dependiendo del area seleccionada. Para que asi no existan referencias erroneas por culpa del usuario.
-Orden de pago, en la que sea necesaria seleccionar algunos codigos, y es dificil que el usuario memorice esos codigos. Por lo que podrian atarse a la selección de otros datos.
-Control de inventario, ya que cada material en el inventario puede tener diferentes responsables y areas asociadas, por lo tanto al seleccionar determinado material de inmediato restringimos los responsable asociados y areas a esta.
-Entre muchos otros casos!

Tutorial

Para ejemplificar crearemos unos formulario con listas dependientes que consisten en seleccionar primero una ciudad y luego seleccionar un distrito pero que se encuentre dentro de la ciudad previamente seleccionada.

Para crear las listas dependientes en el formulario descrito se requiere:

1.- Se recomienda tener dos hojas para realizar las listas desplegables dependientes en el formulario; la primera es Principal donde estará el formulario. La segunda, Datos donde se encontrará la información de las listas:

2.- Lo que resolveremos en este tutorial, es que al seleccionar una ciudad en una primera lista, en la segunda lista de inmediato aparezcan los distrintos de esa ciudad en la primera lista. Para esto el paso recomendamos el paso anterior.

3.- En la hoja Datos se debe copiar la información que aparecerá posteriormente en las listas:

.

4.- Ahora, trabajando en la hoja Datos debemos darle nombre a las los rangos de cada lista, en particular a la que se anidarna las demás; seleccionamos el rango B3:E3 y le colocamos el nombre de CIUDADES, ya que este será los datos generales que filtraremos, es decir serán los mandantes, al seleccionar uno de ellos debe mostrarnos su lista de Distritos.

5.- Ahora realizamos la parte más importante de todo el proceso, seleccionamos el rango B4:B9 y le colocamos el nombre de la ciudad en este caso TRUJILLO y pulsamos “Enter”:

6.- Procedemos de la misma manera con el resto de la información, seleccionamos el rango c4:c10 y le colocamos el nombre de la ciudad en este caso LIMA, pulsamos enter; lo mismo se hace con el resto.

Con el paso anterior hemos asignado nombres a los rangos que contienen la información que validaremos.

7.- Ahora vamos a trabajar en la hoja Principal, si estas fueran campos del formulario Nos situamos en la celda D2, luego nos vamos al menu “Datos” -> “Validación de Datos”, así:

Fijate en el origen, esto enlazara a las ciudades mandantes. Si nos situamos en D2 aparecerá una pestañita para elegir ciudades.

8.- Ahora nos situamos en la celda D4, y vamos a crear las famosas validaciones dependientes, nos vamos al menu “Datos”->”Validación de Datos”. Coloca los datos tal y como se muestran en la imagen.

Observación

=INDIRECTO($D$2) significa en la celda D2 la lista mandante es decir la CIUDAD, que obviamente cambiará según elija el usuario, esta fórmula lo que hace es referenciar al nombre del rango que contiene la información, si en D2 dice TRUJILLO, entonces se validaran los datos correspondientes al rango llamado TRUJILLO; asi de acuerdo a los valores del mandante.

9.- Debería quedar así:

Tips

-Espacios en blanco por diferencias de tamaño en las selecciones:

Para solucionar este problema tenemos que usar rangos dinámicos. Normalmente usamos la función DESREF para crear rangos dinámicos. El problema en nuestro caso es que la función INDIRECTO solo acepta rangos como argumento. Para superar este problema usaremos esta fórmula en la creación de la lista desplegable dependiente de la celda en donde estén los Centros de Costos.

=DESREF(INDIRECTO(SUSTITUIR(B2," ","_")),0,0,CONTARA(INDIRECTO(SUSTITUIR(B2," ","_"))),1)

Video: Listas desplegables dependientes en Excel

 

Para más información 

Recomendamos:

-http://jldexcelsp.blogspot.com/

-http://www.youtube.com/user/alheva?feature=watch

Leer más...

Control de Acciones con Macros: Excel y Access

¿Qué es una Macro?

Macro se define como un conjunto de acciones que permite automatizar procesos o tareas. Por ejemplo, nos permiten abrir o cerrar formularios, mostrar u ocultar herramientas,  o generar métodos abreviados de teclado al ejecutar tareas automáticamente a partir de la base de datos.  

Para Access, una macro define una forma estructurada de acciones que un usuario desea realizar para dar respuesta a un evento determinado. Por ejemplo, se podría generar un informe a partir de otro elemento. Para Excel, una macro corresponde a un conjunto de comandos almacenados disponibles para ejecutar una acción. 

Controles con Macros

Controles son elementos que permiten ejecutar comandos al hacer click sobre ellos. En palabras simples, crear una macro consiste en asociar una o varias acciones a alguno de los elementos habilitados para esto, ejemplo de ello puede ser asociar cierta acción a un botón, un combobox, la realización de una consulta, una tabla, abrir una hoja, cambiarla, ejecutar un archivo que este fuera del excel o access, la ejecución de cierta acción al apretar una imagen, entre muchas otras acciones que se pueden realizar con un macro asociada a ellas. Al utilizar macros, se integran acciones programadas a través de Visual Basic.

A continuación se ejemplifica y muestra mediante un ejemplo en excel y access el funcionamiento de macros al apretar un botón, si bien es cierto existen muchas acciones posibles de realizar, en el siguiente enlace de Control de Acciones  se puede investigar con mayor detalle el funcionamiento de otras como: etiquetas, combobox, listbox, imagen, entre otras.

Control de Acciones en Excel

Excel ofrece varias maneras de ejecutar una macro, nosotros presentaremos un modo fácil a través de un ejemplo con un botón:

-Antes que todo, asegúrese de que tenga activada la celda programador en su Excel. En caso contrario, diríjase a la esquina izquierda superior de la hoja y  haga click en el botón de office. Luego, en “Opciones” busque “Más Frecuentes” y active la casilla “Programador”.   

- Para mostrar cómo crear un botón de acciones con macros en Excel, utilizaremos una base que contiene las ventas de distintos  proveedores de acuerdo a los productos vendidos durante un mes. Asumiremos que durante todos los meses se realiza la misma operación, para que así la macro sea válida cada vez que se desee calcular. De esta forma, se espera rellenar la siguiente tabla cada vez que se apreté el botón correspondiente a la macro. Lo primero será ir a la barra de herramientas, programador y en éste seleccionar insertar, para luego seleccionar el primer botón, tal como se muestra en la imagen. 

- Al  insertar el botón, se podrá elegir el tamaño de éste. Automáticamente se desplegará una ventana llamada “Asignar Macro”, donde se puede cambiar el nombre a alguno distintivo, para luego presionar grabar.

- Al seleccionar grabar, se desplegará otra ventana para grabar la macro, en la que se deberá aceptar. Al final de la hoja, deberá aparecer un icono como el que se muestra abajo, que indica que lo que estamos haciendo se está grabando.

- Dado que queremos calcular las ventas totales, realizamos una suma de acuerdo a la base y luego la copiamos para todos los proveedores. Al mismo tiempo, calculamos el total de las ventas y el porcentaje de ellas.

- Tras realizar todo aquello que buscamos a través de la macro, se debe detener, apretando el mismo botón que indica que la macro se estaba grabando.

- Cada vez que queramos replicar la realización, se deberá apretar el botón que creamos y la información se calcula de manera automática, sin la necesidad de realizar las mismas operaciones.

- Para ver la macro que hemos generado se debe ir a vista --> macros --> ver macros, como se muestra a continuación: 

- Finalmente, se desplegará una lista con todas las macros que están disponibles y se debe seleccionar la que corresponde a la generada por uno. La macro que se ha creado de acuerdo a lo que hicimos corresponde a la siguiente:

Control de Acciones en Access

En el caso de Access, dependiendo del tipo de archivo que esté relacionado a la macro (informe, formulario, etc.), se hará uso de los controles de modo diferente para cada caso. A continuación, se describe bajo un escenario de Informe, en el cual se puede crear el control  (en este sencillo caso ejemplificamos con un botón) y después generar la macro asociada a él.

- Primero, la vista bajo la cual se debe estar trabajando en el programa debe ser de diseño, para así poder crear los controles de acción de las macros. Además, para un mejor trabajo, las opciones  “Seleccionar” y “Utilizar Asistentes para Controles” deben estar activadas.  

- Luego, para crear un control de acciones, se pulsa la opción “Botón disponible” en la pestaña “Diseño”, y se expande el comando según se requiera. El nombre del comando puede ser modificado.  

- A continuación, para asociarlo con una macro es necesario generar una. Esto último se lleva a cabo al hacer click con el botón derecho del mouse sobre el comando (o botón) recién creado, y pulsando la opción generar evento. Después, se abre una nueva viñeta que entrega tres opciones: Generador de macro, de Expresiones o de Códigos, escogiendo la primera opción para proceder.  

-Tras haber procedido con el paso anterior, se abrirá una nueva ventana que permitirá generar distintas acciones al hacer click en el botón creado.  

- En el siguiente ejemplo simple, la acción ocupada será “Abrir Tabla” y los campos del “Argumento de la acción” se completan según los datos que se busca representar, en este caso el “Nombre de la Tabla” corresponde a “TipoCombustible”. En seguida se guarda y se cierra la pestaña asociada a la macro, se cambia la opción de vista a “Vista Informe”, y se ejecuta el botón que realizará las acciones indicadas.  

Para observar el ejemplo de un caso de Formularios, se adjunta el link del siguiente video:   

Leer más...