miércoles, 20 de octubre de 2010

Rangos dinámicos con desref

En un post anterior vimos un poco el funcionamiento de desref. Ahora vamos a avanzar un poco más. Desref nos permite crear rangos dinámicos. Así si la información de un rango aumenta o disminuye lo que deseamos es que se tengan esas modificaciones cada vez que se produzcan. Con un ejemplo lo veremos mejor.

Supongamos que tenemos un rango de datos, por ejemplo A1:A3


Vamos a Insertar / Nombre / Definir y en el cuadro de texto Nombres en el libro: escribimos RangoDin. En el cuadro de texto Se refiere a: ponemos:

=desref(A1;0;0;contara(A1:A100);1)

Al seleccionar las celdas para añadir el rango automáticamente sale con referencias absolutas, la imagen es:



Como argumento hemos introducido la función contara que nos va permitir que el rango solo sea el conjunto de celdas contiguas y con datos. pulsamos en aceptar y ya el rango está definido.

Para ver cuantas celdas ocupa el rango, escribimos su nombre en el cuadro de nombres tal como vemos:



Pulsamos en la tecla intro, con lo que tenemos lo siguiente:

Es un rango de 3 celdas. Ahora introducimos más datos en la columna A:


y volvemos a escribir en el cuadro de nombres rangodin, pulsamos en aceptar, y tenemos:



Es decir, el tamaño del rango es mayor y va en función de las celdas que están ocupadas en la columna A. Mas celdas con datos, rango mas amplio, menos celdas rango más pequeño. La función desref nos ha ayudado a lograrlo.

martes, 12 de octubre de 2010

Función desref

Es una función de referencia de búsqueda de Excel. Nos va a permitir generar rangos de celdas que se puedan contraer o expandir. En este post vamos a ver como funciona la función.

Desref consta de cinco argumentos, de los cuales, el cuarto y quinto son opcionales, es decir, podemos darles valor o no, según nos convenga.

En primer lugar, vamos a usarla con los tres primeros argumentos, son los siguientes:
  1. Ref
  2. Filas
  3. Columnas

Dejamos para otra entrada alto y ancho. El argumento ref  hace referencia a la celda origen. El segundo y tercer argumento muestra el desplazamiento en número de celdas con respecto al argumento ref. Por ejemplo, sí los argumentos filas y columnas son cero el resultado de la función dará el contenido de la celda marcada como referencia en el primer argumento. Entonces:

=desref(A1;0;0)

Si en la celda A1 tenemos el dato función desref, entonces el resultado de la función será función desref, es decir, el mismo.
Veamos más ejemplos. Partimos de los siguientes datos:

En una celda introducimos la siguiente fórmula:

El resultado será, de acuerdo con la tabla, Fotografía puesto que fotografía se encuentra una fila por debajo y una columna después de la celda referencia, A4 (fotografía está en la celda B5).

Otro ejemplo, ahora vamos a cambiar los argumentos para obtener otro resultado. Si queremos obtener el número de alumnos inscritos en el curso de botánica la función será la siguiente:

Ahora nos desplazamos 0 filas y dos columnas hacia la derecha con respecto a la celda A4.El resultado es entonces:

El manejo de la función con tres argumentos es sencillo de usar. En un próximo post tendremos en cuenta a todos los argumentos. Como avanzadilla diremos que el cuarto y quinto argumento sirven para hacer referencia a un rango con un número determinado de filas y columnas de tamaño. Esto permite definir rangos dinámicos, que se expanden automáticamente en función del número de celdas con datos.

martes, 5 de octubre de 2010

Navegar entre paneles de control


En un post anterior comentados como crear un panel de control en Access. Ahora vamos añadir más paneles de control en la misma base de datos para tener organizada la información y tener acceso a ella mediante distintos paneles.
Partimos de la misma base de datos de ese post. Habíamos creado un panel de control, tal como vemos en la siguiente imagen:

 Ahora creamos una nueva página o panel de control. Pulsamos en el botón nueva:


con lo que aparece una ventana que nos pregunta por el nombre de la nueva página:


 Si pulsamos en el botón aceptar el resultado es una nueva página de panel de control tal como muestra el administrador de panel de control:

De esta forma cualquier panel de control que añadamos aparecerá en este administrador. Desde aquí podemos añadir o eliminar botones de cada panel o página. Ahora vamos a añadir un botón al panel principal que nos permita acceder al contenido del panel recién creado. 

Para poder ir de un panel de control a otro, vamos a crear un botón en el primer panel. Primero accedemos al panel de control principal, lo seleccionamos en la lista y pulsamos en el botón modificar. Para añadir un nuevo botón pulsamos en el botón nueva, aparece una ventana que la rellenamos para que tenga el siguiente aspecto:


 Pulsamos en el botón aceptar y ya lo tenemos. Creamos ahora un nuevo botón, que nos permita salir de Access cuando lo pulsemos. Volvemos a pulsar en el botón Nueva y rellenamos los datos como sigue:




 Al seleccionar el comando Cerrar la aplicación, automáticamente desaparece el tercer menú, ya no hace falta. Después de estas acciones la ventana Modificar página del panel de control tiene el siguiente aspecto:
 

 Ahora salimos del panel de control, pulsando dos veces en cerrar. Abrimos el formulario panel de control y su aspecto es el siguiente:

Vamos a probar que funciona. Si pinchamos en el botón Cambio de panel debe llevarnos al segundo panel creado, pulsamos y aparece el panel buscado:

 

 Este panel aparece bastante vacío, pues en él no hemos colocado botones, podemos poner, entre otros, un botón que nos lleve de nuevo al panel principal. Los pasos para hacerlo son los mismos que acabamos de explicar.

sábado, 2 de octubre de 2010

La función buscarv

Normalmente cada negocio puede llevar un registro de sus clientes, proveedores o productos. Esa lista puede ser larga, lo que provoca que la búsqueda de algún dato en la misma se puede convertir en una tarea tediosa. Excel proporciona para estos casos una herramienta muy útil, la función buscarv. Vamos a ver como funciona y como podemos implementarla.

En primer lugar, se muestran los datos a partir de los cuales vamos a construir la función. Podemos imaginarnos una tabla con más filas y columnas, pero bastará para ver como funciona la función. Los datos a partir que vamos a necesitar son los siguientes:

Y ahora creamos las siguientes celdas, que nos ayudarán a conocer los datos que nos interesan de un producto determinado por su código:

Así introduciendo el código del producto que nos interesa obtenemos automáticamente el resto de los datos del  producto. Todos los datos están extraídos de la lista de datos inicial.
Buscarv nos pide cuatro argumentos:
  • Valor buscado
  • Matriz de datos
  • Índice de columna
  • Ordenado
En nuestro ejemplo, Valor buscado será el valor que colocamos en la celda H2. De acuerdo con ese valor, buscarv localizará el nombre o el número de unidades. Para que tenga éxito la búsqueda el dato colocado en H2 debe estar presente en la primera columna de la lista de datos inicial. Si los valores de la primera columna son numéricos, el valor buscado puede ser uno que esté entre esos valores, no es necesario que sean los mismos.
Matriz de datos representa el conjunto de celdas que contienen todos los datos con los que trabaja buscarv.
Indicador de columna es la columna (dada con valor numérico) que contiene el valor que deseamos que buscarv nos muestre como resultado.
Ordenado puede ser verdadero o falso. Hace referencia a la primera columna de la matriz de datos, si está ordenada su valor es verdadero, y si no será falso. Además, si es falso el valor buscado debe ser alguno de la primera columna, no se admite otro valor,  en este caso no devuelve datos.
Introducimos un código en la celda H2. Después nos posicionamos en la celda H3. Escribimos la función buscarv para que nos devuelva el nombre.

Como el código está en H2, hacemos clic en la celda, tecleamos; y como matriz de datos seleccionamos todas las celdas de la tabla de datos inicial. La primera fila contiene los títulos de cada columna, no la tenemos en cuenta, como vamos a copiar la fórmula para las unidades, la fecha de entrada y el precio unidad entonces fijamos su referencia a las celdas con una referencia absoluta. Lo mismo vamos a hacer con la referencia al valor buscado. Esto nos permite copiar la fórmula sin que cambien dichas referencias. Y como tercer argumento escribimos un 2 pues queremos que nos devuelva el nombre del producto. Como ordenado no ponemos nada pues vemos que están ordenados los códigos. En este caso también podemos colocar falso para obtener la referencia exacta del código del producto.

Solo queda copiar la fórmula en las celdas H4; H5 y H6. Para obtener los resultados esperados solo tenemos que cambiar el tercer argumento, indicador de columnas, por el número correspondiente a las unidades, fecha y para el precio (3, 4 y 5 respectivamente). Así completamos la columna H.



Quizás tengas que hacer algún cambio de formato para que los datos aparezcan como nos interesa, formato fecha o formato general. A partir de ahora, poner un nuevo código producto en H2, mostrará automáticamente el nombre, número de unidades, fecha de entrada y el precio unidad de ese nuevo producto. Por ejemplo, si en H2 escribimos P-6 nos quedará:


Resumiendo, podemos decir que buscarv es una buena herramienta para recuperar datos de grandes listas de datos de una forma sencilla y rápida.

Actualizar tablas en Excel

En el siguiente ejercicio vamos a ver como actualizar tablas en excel. Nos centraremos en modificar unos precios con código VBA. Modificare...