�������������� ����������������� ��������� !�"$#&%'�

1

EXCEL AVANZADO

ANÁLISIS DE DATOS

El presente curso abarca conceptos teóricos y un taller de práctica concasos de ejemplo, incluyendo asimismo el desarrollo de casos propuestos por losasistentes.

Los temas a desarrollar son los siguientes:

• Barras de herramientas personalizadas• Nombres de Rangos• Bases de datos• Rango de Criterios• Cuadros Combinados (Filtros)• Funciones• Introducción al entorno VBA• Macros• Formularios

INTRODUCCIÓN:

El presente modelo de análisis de datos mediante el uso del programaMicrosoft Excel, se basa en el uso combinado de las herramientas enunciadas en“Temas a desarrollar”.

La finalidad de la utilización de las mismas es la de manipular grandescantidades de información y obtener de manera simple cuadros estadísticossintéticos para un permitir un posterior análisis detallado. Además, posibilita agilizary automatizar la realización de las tareas.

La modalidad de dictado consistirá en enunciar primero los conceptosteóricos involucrados, para continuar desarrollando los cuatro (04) casos prácticosen los cuales se utilizan en forma conjunta todos los temas.

Los archivos de ejemplo a utilizar durante el aprendizaje son lossiguientes: “Horas Extras.xls”, “Cruzamiento de información.xls”, “Formularios dedescargo.xls” y “Torneo de Fútbol.xls”.

Para culminar, se solicitará a cada uno de los asistentes que planteen unproblema surgido de la tarea cotidiana en su oficina de origen, a efectos deplantearlo en términos del presente modelo y resolverlo conjuntamente en clase, conla finalidad de afianzar todo lo aprendido en el curso.

Asimismo, se suministrarán diversos consejos tendientes a imprimirceleridad a nuestro trabajo.

A continuación, se enuncian los conceptos teóricos del curso.

�������������� ����������������� ��������� !�"$#&%'�

2

BARRA DE HERRAMIENTAS PERSONALIZADA

Si bien la utilización de una barra de herramientas personalizada noresulta fundamental para la realización de las tareas propuestas en el curso, puedeser de gran ayuda a efectos de acelerar el acceso a los diversos comandos autilizarse.

Por defecto, Excel nos presenta las barras de herramientas “Estándar” y“Formato”, pero podemos mostrar barras adicionales, modificar las mismas einclusive crear barras personalizadas con íconos referidos a los comandos de usomás frecuente.

Vamos a crear una nueva barra de herramientas con nuestro nombre y leasignaremos los comandos que mas utilizamos. Para ello debemos hacer click en elbotón derecho del mouse, con el puntero situado sobre cualquier parte de cualquierbarra de herramienta visible, y elegir el comando “Personalizar…”. Se nospresentará un cuadro de diálogo como el siguiente:

Debemos elegir la solapa titulada “Barras de herramientas”

�������������� ����������������� ��������� !�"$#&%'�

3

y hacer click sobre el botón “Nueva…”

donde reemplazaremos el texto “Personalizada 1” por nuestro nombre yhacemos click en el botón “Aceptar”. Se nos presenta la siguiente ventana:

�������������� ����������������� ��������� !�"$#&%'�

4

A la barra rectangular con nuestro nombre la arrastramos debajo de labarra de herramientas “Formato” y posteriormente hacemos click en la solapa“Comandos” del cuadro de diálogo, de manera que quede como la siguientepantalla:

�������������� ����������������� ��������� !�"$#&%'�

5

A continuación sólo nos resta hacer click en cada elemento de“Categorías”, para que se nos presente en “Comandos” un listado con los íconosreferidos a los diversos comandos.

Una vez localizado el ícono deseado, hacemos click sobre el mismo y loarrastramos hasta nuestra barra de herramienta personalizada y lo soltamos,quedando automáticamente agregado a la misma.

Repetimos el procedimiento tantas veces como íconos deseamos agregary terminamos haciendo click en el botón “Cerrar”. Nuestra nueva barra deherramientas debe quedar con un aspecto similar al siguiente:

En el ejemplo se agregaron íconos referidos a los siguientes comandos:Estilo, Escenario, Editor de Visual Basic, Grabar macro, Ejecutar macro, Botón,Cuadro combinado, Insertar hoja de cálculo, Inmovilizar secciones, Alternarcuadrícula, Seleccionar celdas visibles, Configurar página… y Pantalla completa.

NOMBRES DE RANGOS

En lugar de escribir las referencias de las celdas cuando utilizamosfunciones, resulta mucho más práctico, fácil de entender y recordar, utilizar nombresde rangos.

�������������� ����������������� ��������� !�"$#&%'�

6

Ello implica que en vez de utilizar una función como la siguiente=suma(b5:h10), empleamos =suma(ventas), donde previamente definimos que elrango ventas se refiere al área de celdas comprendidas entre b5 y h10.

Si hacemos click en el “Cuadro de Nombres” (situado a la izquierda de labarra de fórmulas), se nos presenta un listado de todos los nombres de rangoexistentes en el libro de trabajo, según se aprecia en la siguiente pantalla:

En cuanto elegimos un nombre de rango determinado, en este casoVentas, automáticamente selecciona el área a la que se refiere dicho rango, segúnse aprecia seguidamente:

�������������� ����������������� ��������� !�"$#&%'�

7

A continuación, aprenderemos a crear nuevos nombres de rango. Laforma más común de hacerlo es ir al menú Insertar / Nombre / Definir..., dondepresenta el siguiente cuadro de diálogo:

Donde dice “Nombres en el libro”, debemos ingresar el nombre a otorgaral nuevo rango, y en “Se refiere a” debemos indicar el área que la mismacomprenderá. Terminamos el proceso haciendo click en el botón “Agregar”.

A pesar de que el método descripto no presenta dificultad alguna, sugieroutilizar el siguiente, por producir los mismos resultados más intuitivamente y en untiempo menor.

�������������� ����������������� ��������� !�"$#&%'�

8

Seleccionar el área de celdas a las cuales deseo asignarles un nombre derango determinado. Manteniendo dicha selección, hacer click dentro del Cuadro deNombres, escribir el nombre deseado y presionar la tecla “Enter”.

De esta manera, automáticamente se asignan los nombres de rangos, sintener que estar completando ningún cuadro de diálogo.

Cabe destacar que el método abreviado solamente resulta aplicable paradefinir el nombre de un rango, pero no para modificar el mismo, en cuyo casodeberemos indefectiblemente utilizar el comando definir nombre del menú Insertar.

Al avanzar en el desarrollo de los temas del curso, comprenderemos laenorme importancia de la utilización de los nombres de rangos, ya que los mismosresultarán fundamentales para trabajar tanto con la base de datos como para losrangos de criterios, cuadros combinados y argumentos de funciones.

BASES DE DATOS

Partimos de la premisa de que deseamos obtener información a partir deuna base de datos, la cual puede tener diversos orígenes: a) datos importados de unsistema existente mediante una consulta al mismo, b) archivo de texto u obtenido decualquier aplicativo susceptible de ser convertido al formato de una base de datos oc) la carga manual de los datos dentro de una hoja del libro de trabajo de Excel quedefinimos como una base de datos.

Cuando trabajamos una base de datos dentro de Microsoft Excel,tenemos como limitación la cantidad de registros que se pueden utilizar, ya que nopueden excederse los 65.536 (siempre que grabemos el archivo en formato de Excel97). Esto presupone una gran limitación respecto al uso de cualquier otro softwareespecífico para administrar bases de datos. Además, no podemos utilizar diversastablas como en el caso de los administradores de bases de datos relacionales.

No obstante, considero sumamente útil el uso de Excel para obtenercuadros estadísticos resumen que reflejen en una sola hoja de cálculo, la aplicaciónde una enorme cantidad de criterios de consulta diferentes.

La hoja (o las hojas) que contendrán la base de datos se pueden obtenerimportando una consulta realizada mediante un administrador de bases de datosrelaciones como ser Microsoft Access o Fox Pro, o simplemente cargando dichainformación a mano dentro de la misma planilla. Obviamente, este método resultamucho más útil si contamos con una cantidad importante de datos, a los cualesnormalmente los recibimos de otra aplicación.

En Microsoft Excel, los campos de una base de datos son las columnas ylos registros son las filas.

La condición fundamental para que Excel interprete los datos cargados oimportados como una base de datos, consiste en asignarle al área un nombre derango que indique que la misma corresponde a una base de datos.

�������������� ����������������� ��������� !�"$#&%'�

9

Para ilustrar lo enunciado, se presenta el siguiente ejemplo del archivo"Formularios de Descargo.xls”, en el cual podemos apreciar que la hoja “BaseImpositiva” contiene una base de datos cuyo nombre de rango es “Base_Impositiva”,donde los campos son OI, IMPUESTO, CUIT, NOMBRE, ETC. y tiene un total deveinticinco registros.

RANGOS DE CRITERIOS

Consiste en establecer criterios para poder posteriormente filtrar los datosde nuestra base de datos.

Para comprender el concepto de lo que es un rango de criterio, nosresultará sumamente útil e ilustrativo comenzar analizando los filtros avanzados deExcel, dado que los mismos utilizan rangos de criterios y podremos visualmenteapreciar el efecto producido por ellos.

Abrimos el archivo de ejemplo denominado “Formularios de descargo.xls”y nos situaremos en la hoja “Base Impositiva”. Comenzaremos practicando con unautofiltro. Los mismos nos permiten filtrar rápidamente y en forma visual nuestrabase de datos con sólo elegir un determinado de un menú descolgable o conintroducir una función personalizada en el mismo.

�������������� ����������������� ��������� !�"$#&%'�

10

Para activar el autofiltro, debemos seleccionar la opción “Autofiltro” delcomando “Filtro” del menú “Datos” y nuestra base de datos quedará presentadacomo en la siguiente figura:

Podemos apreciar que a la derecha del nombre de cada campo se agregaun cuadro que me permite filtrar los registros de la base de datos.

Podemos establecer filtros para distintos campos, como por ejemplo elsiguiente: OI > 100.000, Impuesto < 100 y Fecha de Descargo entre el 01/07/1999 yel 30/04/2000.

�������������� ����������������� ��������� !�"$#&%'�

11

Nuesta base de datos quedará filtrada de la siguiente manera:

Podemos apreciar que se presenta en pantalla una menor cantidad deregistros, debido a que solamente quedan visibles los que cumplen con las tres

�������������� ����������������� ��������� !�"$#&%'�

12

condiciones planteadas precedentemente mediante los cuadros de diálogo depersonalizar (se los distingue porque aparece en color azul el encabezado de lasfilas visibles).

Pero existen situaciones en las cuales se requiere plantear situacionesmucho más complejas que la del ejemplo, donde no resultará posible utilizar elautofiltro, por ejemplo si además de ver los impuestos menores a 100 deseopresentar los que sean 301, 302, 351 y 352, estamos ante una situación máscompleja imposible de consultar mediante el autofiltro.

En estos casos, deberemos recurrir a un “Filtro Avanzado...”, al cualaccecdemos desde la opción “Filtro Avanzado” del comando “Filtro” del menú“Datos”, y aparecerá el siguiente cuadro de diálogo:

De la misma surge que tengo la opción de filtrar la base de datos sinmoverla de su posición actual o copiarla a otro lugar (de la misma hoja únicamente).Ello lo decido con el botón de opción situado en el grupo “Acción”. En caso de elegircopiar a otro lugar, se habilita el cuadro que solicita indicar el rango de destino dedicha salida. En caso de elegir filtrar la lista sin moverla a otro lugar, solamente debocompletar la información relativa al rango de la base de datos y la del rango decriterios.

El rango de la lista aparece autocompletado en función del área de trabajoactual, en cambio al “Rango de Criterios” debemos ingresarlo manualmente.

El Rango de Criterios es un área que debe tener “por lo menos” dos filas yuna columna, donde la primera fila debe contener el nombre del campo quedeseamos consultar, según el siguiente ejemplo:

�������������� ����������������� ��������� !�"$#&%'�

13

Creamos una nueva hoja donde A1:A2 es el rango que contiene el criteriopor el cual deseamos filtrar nuestra base de datos, es decir el “Rango de Criterios”.

Como vimos anteriormente, resulta conveniente asignarle nombres a losrangos para facilitar su posterior identificación. En este caso le asignaremos elnombre “Criterio_uno”, que es el que utilizaremos dentro del cuadro de diálogo delFiltro Avanzado, el cual deberá ser completado de la siguiente manera:

Hacemos un click sobre el botón “Aceptar” y nuestra base de datosquedará filtrada de manera tal que únicamente se exhiban los registros que tienen elvalor 30 en el campo Impuesto, dado que ésa es la única condición establecida en elrango de criterio llamado “Criterio_uno”, según se aprecia en el siguiente cuadro:

�������������� ����������������� ��������� !�"$#&%'�

14

Este fue un ejemplo muy sencillo para comprender la idea de lo que es unrango de criterios, pero podemos hacerlo muy complejo. Para ello deberemosaprender la forma en que utilizo los operadores condicionales “y” y “o” para poderdefinir múltiples criterios simultáneamente.

Para utilizar la condición “y” debo agregar columnas a mi rango de criterio.En cambio para utilizar la condición “o” debo agregar filas. Para comprenderlo mejorcontinuaremos modificando el ejemplo anterior.

Vamos a suponer que deseo obtener información de los registros cuyoimpuesto sea 30 y que la orden de intervención esté comprendida entre 100000 y200000. Para ello deberemos modificar el rango de criterios de la siguiente manerañ

�������������� ����������������� ��������� !�"$#&%'�

15

Posteriormente, asignamos al área A1:C2 el nombre de rango“Criterio_dos” e invocamos nuevamente el cuadro de diálogo correspondiemente alfiltro avanzado, completándolo de la siguiente manera:

Hacemos click en el botón “Aceptar” y nuestra base de datos quedaráfiltrada como se ilustra seguidamente:

�������������� ����������������� ��������� !�"$#&%'�

16

Podemos apreciar que se aplicaron ambos criterios en forma conjunta, yaque al agregar columnas al rango de criterios, uní las expresiones con uncondicional “y”.

Para terminar de complicar el ejemplo, vamos a pedir que además de lascondiciones anteriores me muestre los registros que tengan impuesto 180 sinimportar que número de orden de intervención tengan. Para ello, debemos modificarel rango de criterios de la siguiente manera:

�������������� ����������������� ��������� !�"$#&%'�

17

Posteriormente, asignamos al área A1:C3 el nombre de rango“Criterio_tres” e invocamos nuevamente el cuadro de diálogo correspondiemente alfiltro avanzado, completándolo de la siguiente manera:

Hacemos click en el botón “Aceptar” y nuestra base de datos quedaráfiltrada como se ilustra seguidamente:

�������������� ����������������� ��������� !�"$#&%'�

18

Podemos apreciar que se aplicaron todos los criterios en forma conjunta,ya que al agregar una fila al rango de criterios, unimos las expresiones anteriores ala nueva con un condicional “o”.

Cuando en las hojas de Salida de nuestras planillas utilizemos funcionesque extraigan información de nuestra base de datos, deberemos utilizar como unode los argumentos de dichas funciones los nombres de rangos de criterios queaprendimos a crear en el presente apartado.

CUADROS COMBINADOS (FILTROS)

Nuestro objetivo es crear cuadros combinados que nos desplieguen unalista predeterminada de valores entre los cuales elegir para seleccionardeterminados registros o para modificar los criterios con los cuales filtraremosnuestra base de datos.

A efectos de traducir la información obtenida mediante la utilización deeste control, debemos utilizar complementariamente la función BUSCARV(), cuyasintaxis y particularidades se exponen en el apartado correspondiente a lasfunciones.

De todas maneras, para comprender la interrelación entre esta función ylos cuadros combinados, plantearemos un ejemplo muy simple, con el cualcomprenderemos el funcionamiento de los mismos.

�������������� ����������������� ��������� !�"$#&%'�

19

Abrimos un libro de Excel en blanco e ingresemos los datos que seconsignan en la siguiente pantalla:

Nuestro objetivo es introducir un menú descolgable (cuadro combinado),que me permita seleccionar alguno de los apellidos listados y me exhiba informaciónsobre el agente elegido en las celdas B1 a C13.

Nuestro primer paso consistirá en elegir de nuestra barra de herramientaspersonalizada o de la barra Formularios el botón de “Cuadro Combinado”, y una vezseleccionado procedemos a dibujar su forma sobre la Hoja 1, por ejemplo en lacelda F2, quedando de la siguiente manera:

�������������� ����������������� ��������� !�"$#&%'�

20

Para eliminar la selección del cuadro combinado debemos presionar latecla “Esc” o hacer click en cualquier celda de la hoja de cálculo.

Al hacer click en la flecha del extremo derecho del cuadro combinado,observamos que no despliega ningún valor, debido a que primero debemos indicarleel rango de donde extraer dichos datos.

A efectos de facilitar nuestra tarea, vamos a definir tres nombres derango, a los cuales indicaremos con un color de relleno de celda específico paradiferenciarlos en el ejemplo. Asimismo, escribiremos un número de orden a laizquierda de cada apellido.

A la celda B1 (color celeste) la definimos con el nombre de rango “Salida”;al rango B2:B7 (color verde) lo definimos con el nombre de rango “Lista” y al rangoA1:D7 (color amarillo) lo definimos con el nombre de rango “Base”.

�������������� ����������������� ��������� !�"$#&%'�

21

A continuación hacemos un click derecho en el cuadro combinado yelegimos el comando “Formato de control...”, el cual nos presentará el siguientecuadro de diálogo:

�������������� ����������������� ��������� !�"$#&%'�

22

En “Rango de entrada” debemos consignar el área donde se encuentranlos valores que yo deseo que mi cuadro combinado despliegue al hacer click en laflecha del extremo derecho del control. En este caso, deseo utilizar el rango “Lista”(color verde).

En “Vincular con la celda”, debo elegir una única celda en la cual sereflejará el resultado de mi elección en el cuadro combinado. En este caso, deseoutilizar el rango “Salida” (color celeste).

En líneas de unión verticales debo indicar la cantidad máxima de valoresque debe exhibir por pantalla el cuadro combinado, y si activo la casilla deSombreado 3D, nuestro comando tendrá un efecto que simula un botón con relieve.

Completemos el cuadro de diálogo de la siguiente manera:

A continuación hacemos click en el botón “Aceptar”, y ya estamoscondiciones de probar el funcionamiento de nuestro cuadro de control.

Hacemos click en la flecha del mismo y observamos los valores quedispliega.

�������������� ����������������� ��������� !�"$#&%'�

23

Por ejemplo, elijamos el segundo elemento del listado (Gómez), yobservemos los efectos que produce sobre nuestra planilla de cálculo:

�������������� ����������������� ��������� !�"$#&%'�

24

Podemos apreciar que la única variante producida es el número 2 queaparece en la celda B2. Esto se debe a que lo único que realiza el cuadrocombinado es devolverme en una celda (Salida) el número de orden que tiene elvalor elegido dentro del listado desplegable. En este caso Gómez es el segundoelemento de una lista de seis valores.

Para traducir ese número de orden en información relevante, deberemosapelar al uso de la función BUSCARV(). La misma posee cuatro argumentos. Elprimero es el valor que deseo buscar dentro de un rango, el segundo es el área endónde debe buscar y extraer los datos y el tercero es el número de columna en lacual se encuentran los datos que deseo extraer.

En la celda C11 escribimos: =BUSCARV (Salida;Base;2;0) yautomáticamente despliega el apellido del agente seleccionado en el cuadrocombinado.

�������������� ����������������� ��������� !�"$#&%'�

25

Copiamos la fórmula de la celda C11 en las celdas C12 y C13 y en lasmismas cambiamos el segundo argumento por 3 y por 4 respectivamente y sedespliega la totalidad de la información para el agente seleccionado. A continuación,elegimos al agente Rojas para comprobar que nuestra función trabaje correctamentey obtenemos lo siguiente:

�������������� ����������������� ��������� !�"$#&%'�

26

Se observa que toda la información correspondiente a un registrodeterminado se actualiza automáticamente en función del valor escogido en elcuadro combinado.

FUNCIONES

Entre las funciones a utilizar se destacan las siguientes:

• CONTAR.SI• SUMAR.SI• SI• ESERROR• BUSCARV• BDCONTAR• BDSUMA• BDPROMEDIO• BDEXTRAER

Cabe destacar que las funciones contienen argumentos dentro de losparéntesis. Algunos de ellos son base de datos o criterios, a los cuales aprendimosanteriormente a asignarles un nombre de rango para facilitar su consulta omodificación.

Estadísticas:

�������������� ����������������� ��������� !�"$#&%'�

27

CONTAR.SI(rango;criterio): Cuenta las celdas en el rango que coinciden con unadeterminada condición, donde rango es el rango del que se desea contar el númerode celdas que no estén en blanco y criterio es la condición en forma de número,expresión o texto que determina qué celdas deben contarse.

Matemáticas:

SUMAR.SI(rango;criterio;rango suma): Suma las celdas que cumplen con undeterminado criterio o condición, donde rango es el rango de celdas que se deseaevaluar, criterio es la condición que determina que celdas deben sumarse (puedeestar en forma de número, texto o expresión) y rango suma son las celdas que sevan a sumar (si se omite, se usarán las celdas en el rango).

Lógicas:

SI(prueba lógica; valor si verdadero; valor si falso): Devuelve un único valor si unacondición especificada (prueba lógica) se evalúa como verdadero y otro valor si seevalúa como falso. Valor si verdadero es el valor que se devolverá si prueba lógicaes verdadero. Si se omite, devolverá verdadero. Puede anidar hasta siete funcionessi. Valor si falso es el valor que se devolverá si prueba lógica es falso. Si se omite,devolverá falso.

Información:

ESERROR(valor): Devolverá verdadero si valor es cualquier valor de error(#N/A,#¡VALOR!, #¡REF!, #¡DIV/0, #¡NUM!, #¿NOMBRE?, #NULO), donde valor es el valorque se desea probar. Puede referirse a una celda, un nombre o un nombre que serefiere a una celda, fórmula o valor.

Búsqueda y referencia:

BUSCARV(valor buscado; matriz buscar en; indicador columnas; ordenado): Buscaun valor en la columna a la izquierda de una tabla y devuelve un valor en la mismafila desde una columna especificada. De forma predeterminada, la tabla se ordenade forma ascendente. Valor buscado es el valor buscado en la primera columna dela tabla y puede ser un valor, referencia o una cadena de texto. Matriz buscar en esuna tabla de texto, números o valores lógicos en los cuales se recuperan datos,pudiendo ser una referencia a un rango o un nombre de rango. Indicador columnases el número de columna de matriz buscar en desde la cual debe devolverse el valorque coincida. La primera columna de valores en la tabla es la columna 1. Ordenadoes un valor lógico. Para encontrar la coincidencia más cercana en la primeracolumna (ordenada en forma ascendente) = verdadero (1), para encontrar lacoincidencia exacta = falso (0).

Base de datos:

BDCONTAR(base de datos; nombre de campo; criterios): Cuenta las celdas quecontienen número en el campo (columna) de registros de la base de datos quecumplen con las condiciones especificadas.

�������������� ����������������� ��������� !�"$#&%'�

28

BDSUMA(base de datos; nombre de campo; criterios): Suma los números en elcampo (columna) de registros de la base de datos que coinciden con las condicionesespecificadas.

BDPROMEDIO(base de datos; nombre de campo; criterios): Obtiene el promedio delos valores de una columna, lista o base de datos que cumplen las condicionesespecificadas.

BDEXTRAER(base de datos; nombre de campo; criterios): Extrae de una base dedatos un único registro que coincide con las condiciones especificadas.

En todos los casos, base de datos es el rango de celdas que compone lalista o base de datos. Una base de datos es una lista de datos relacionados. Nombrede campo es el rótulo entre comillas dobles de la columna o un número querepresenta la posición de la columna en la lista. Criterios es el rango de celdas quecontiene las condiciones especificadas, el cual incluye un rótulo de columna y unacelda bajo el rótulo para una condición.

El resto de las funciones de Bases de datos tienen una estructura similara las arriba descriptas.

Existe la posibilidad de anidar las funciones entre sí de manera tal quenos permita evaluar diversas condiciones en un solo paso.

Ejemplos extraídos del archivo de ejemplo “Torneo de Fútbol.xls”:

=SI(F2="";0;SI(F2>F3;3;SI(F2=F3;1;0)))

=SI(ESERROR(BDEXTRAER(Base_de_datos;'BaseResultados'!F$1;Local1));"-.-";BDEXTRAER(Base_de_datos;'BaseResultados'!F$1;Local1))

La estructura de las funciones es siempre la siguiente:

=NOMBRE(argumento1;argumento2;...)

Es decir que debe ir precedida del signo igual, siguiendo por el nombre dela función y entre paréntesis se colocan los argumentos, separados por un punto ycoma (o el carácter que cumpla con la función de ser separador de listas). Losargumentos pueden ser uno o varios, según la función de que se trate.

Con la finalidad de mejorar la presentación de nuestras funciones, sobretodo cuando las utilizemos para títulos o encabezados, nos conviene concatenar elresultado de nuestras funciones con texto fijo. Para ello deberemos utilizar elsímbolo ampersand (&). Encontraremos ejemplos del uso de esta opción en la hojade salida del archivo “Horas Extras.xls”.

Asimismo, resulta sumamente útil definir funciones que evalúen si es queel resultado de una función provoca un error, de manera tal de presentar un valor o

�������������� ����������������� ��������� !�"$#&%'�

29

un mensaje que reemplaze dicho error. Esto se justifica cuando se trata de cuadrosgerenciales que requieren una buena presentación, donde resultaría inaceptableincluir los caracteres de los mensajes de error. Ejemplos del uso de esta opción losencontramos en la hoja Salida Fecha del archivo “Torneo de Fútbol.xls”.

Por último, resulta fundamental escribir la función deseada en formacorrecta la primera vez, dado que con el fin de evitar escribirla repetidas veces demanera similar, nos conviene copiar la misma y pegarla en el resto de las celdas quevayan a contener una función similar. Posteriormente deberemos cambiar la parteque se modifica de los nombres de rango, mediante el uso del comando Reemplazardel menú Edición.

ENTORNO VBA

Antes de analizar el funcionamiento de las macros y los formularios,resulta conveniente presentar una breve introducción al entorno de programaciónutilizado en Microsoft Excel, el cual se denomina Visual Basic for Applications (VBA).

Accedemos al mismo mediante el menú Herramientas / Macro... / Editorde Visual Basic y se nos presenta una ventana independiente del programaMicrosoft Excel. Esta ventana tiene una estructura muy similar a la de MicrosoftVisual Basic 5.0 o 6.0. La gran diferencia entre Visual Basic y VBA es que VisualBasic es programa que mediante el lenguaje de programación Basic y el uso deherramientas visuales permite generar programas ejecutables para distribuir entrelos usuarios; en cambio VBA, permite automatizar las tareas y se ejecuta sólo desdeMicrosoft Excel (no lo hace en forma independiente).

La pantalla principal de VBA contiene los siguientes elementos:

�������������� ����������������� ��������� !�"$#&%'�

30

Existen algunos conceptos claves que debemos comprender para podercomenzar recién a trabajar con Visual Basic, como ser las definiciones de objetos,propiedades, métodos y eventos.

Partimos de la premisa de que debemos considerar como un objeto acualquier elemento que utilizemos. Por ejemplo, un formulario es un objeto, un botónde comando es un objeto, una celda es un objeto, un rango es un objeto, una hojaes un objeto, un libro de trabajo es un objeto, la aplicación misma de Excel es unobjeto.

Todos los objetos tienen propiedades, métodos y eventos.

MACROS

Las macros nos permiten automatizar una serie de tareas repetitivas, sinnecesidad de conocer el lenguaje de programación Basic. A tales efectos, MicrosoftExcel tiene una grabadora de macros que genera automáticamente el código de lastareas que realizamos a partir del momento en que la activamos.

Resulta conveniente utilizar la misma durante la fase inicial de nuestroaprendizaje, hasta que nos familiaricemos con lenguaje de programación.

�������������� ����������������� ��������� !�"$#&%'�

31

Para activar la grabadora de macros, debemos ir al menú Herramientas /Macro / Grabar nueva macro...

Utilizando el mismo lenguaje de programación que para las macros,también podemos definir funciones personalizadas, que nos permitan realizaroperaciones que requieran utilizar un cálculo específico no previsto en las funcionesincluídas en Microsoft Excel.

El procedimiento para crear una función personalizada es el siguiente:

1) Abrir el Editor de Visual Basic.2) Insertar un módulo (Insertar / Módulo).3) Elegir el comando Procedimiento... del menú Insertar, el cual nos

presentará la siguiente ventana:

4) Ingresar el nombre deseado en el cuadro Nombre, hacer click enFunción y luego hacer click en el botón aceptar. Por ejemplo:Superficie.

5) Escribir un código como el de los ejemplos (Celsius y MiSuperficie) dela siguiente ventana para definir la función:

Analicemos la sintaxis de la misma. Para definir una función, debemosencerrar el código entre una sentencia Function y End Function.

�������������� ����������������� ��������� !�"$#&%'�

32

La palabra que acompaña a Function es el nombre que deseamosasignarle a la nueva función y entre paréntesis se consignan los argumentos de lamisma, separados por comas.

A continuación se escribe la expresión que permite arribar a un resultado,utilizando a tales efectos los operandos que resulten necesarios para realizar elcálculo deseado.

FORMULARIOS

La finalidad de los mismos consiste en permitir que el usuario puedaacceder a los diversos objetos que componen nuestra planilla con el menor esfuerzoposible y permitiendo que manipulen con facilidad la información de nuestra base dedatos sin necesidad de contar con un conocimiento profundo del funcionamiento deMicrosoft Excel.

Es decir, que a la hora de decidir incluir un formulario en nuestro archivo yde elegir los objetos que deberá contener este último, debemos pensar en lasacciones que deberá llevar a cabo el usuario final.

En caso de que el usuario final del trabajo sea uno mismo, no siempre sejustifica el tiempo que insume diseñar el formulario y controlar que funcione bien, sianalizamos el beneficio adicional que se obtiene mediante su uso.

Para agregar un formulario a nuestro archivo, debemos utilizar el Editorde Visual Basic y

RESUMEN DEL METODO PROPUESTO

1) Abrimos un nuevo libro de trabajo en Microsoft Excel.2) Agregar tantas hojas como sean necesarias (por lo menos debemos tener

cuatro), de manera tal de contar con una primera para que contenga la basede datos, una segunda para los rangos de criterios, una tercera para loslistados a incluír en los filtros y una cuarta para el cuadro que contenga lasalida estadística deseada. Ejemplo de nombres de las cuatro hojas: Base,Criterios, Filtros y Salida.

3) En la hoja que cumple la función de base de datos, seleccionar todo el áreade datos posicionándose en la celda A1 y presionando las teclas Shift, Controly End. Manteniendo dicha selección, hacemos click dentro del cuadro denombres y le asignamos un nombre de rango representativo, por ejemplo“Base_de_datos”.

4) En la hoja salida diseñamos el cuadro de doble entrada que permitirá mostrarla información estadística resumen de nuestra base de datos. Le asignamoslos títulos, bordes y configuración de página y dejamos lista la estructura a laespera de escribir las funciones que extraigan información de la base.

5) En función a la necesidad de información de la hoja Salida, definir los rangosde criterios que permitirán realizar las diversas consultas a la base. Debemosdefinir un rango por cada criterio diferente. Esta es la parte mas larga ytediosa de la elaboración de nuestra planilla, dado que según la complejidad

�������������� ����������������� ��������� !�"$#&%'�

33

de la misma pueden llegar a resultar necesarios cientos de nombresdiferentes, pero este esfuerzo adicional se verá largamente recompensado encuanto gracias a dichos nombres de rango, las funciones de la hoja salidacalculen automáticamente todos los valores deseados. Es aconsejable utilizarnombres de rango lo más estandarizados posible, de manera tal que sunombre refleje algún atributo de los datos que contiene. Esto nos facilitaráenormemente el reemplazo de los nombres de los rangos cuando elaboremosel cuadro de salida.

6) En caso de resultar necesario contar con cuadros combinados (listas) queexhiban un listado de valores entre los cuales elegir, se deberá definir elcontenido de los mismos en la hoja Filtros. Los mismos pueden resultarnecesarios para actualizar automáticamente texto o ciertos valores de la hoja.El uso más importante de los mismos es para utilizar el valor obtenidomediante el uso de la función BUSCARV() dentro de un rango de criteriodefinido previamente, de manera tal que un único nombre de rango de criteriome permita realizar diversas consultas según cuál sea el valor elegido delcuadro combinado.

7) El último paso consiste en escribir las funciones dentro del cuadro estadísticode la hoja Salida. A tal efecto resulta imprescindible utilizar los nombres derangos (de base de datos y de criterios) definidos previamente comoargumentos de las funciones que utilizaremos. Resulta conveniente escribircorrectamente la primera función y luego copiarla a las restantes celdas,modificando únicamente la parte que varía del nombre de cada rangomediante el comando Reemplazar del menú Editar. Para ello resultaimprescindible haber utilizado nombres de rango tipíficados según lo indicadoen la parte final del punto 5) del presente apartado.

CONSEJOS

Una vez concluída nuestra tarea, podemos proceder a realizar una seriede acciones tendientes a proteger nuestro archivo contra errores involuntarios de losusuarios que podrían llegar a alterar el funcionamiento del mismo. A continución seenuncian algunos ejemplos:

• Proteger las celdas que no deseamos que sean borradas o modificadasaccidentalmente.

• Si se distribuye a otros usuarios, ocultar hojas Criterios y Filtros, a efectos deevitar lo indicado precedentemente. Para ocultar las solapas de las hojasdebemos recurrir al menú Formato / Hoja / Ocultar.

•