manual del usuario - curso de excel uson

Curso de Excel Intermedio y Avanzado.IIS Luis Gastón Figueroa Romero.

Junio de 2003

ContenidoLO BÁSICO DE LAS BASES DE DATOS.....................................................................2

CREAR UNA BASE DE DATOS Y USO DE ADVANCE FILTER................................................2ORDENAR UNA BASE DE DATOS.......................................................................................5EJECUTAR ORDENACIONES MULTIPLES............................................................................6USAR DATA FORM...........................................................................................................7LOCALIZAR REGISTROS....................................................................................................7BORRAR REGISTROS.........................................................................................................8

ADMINISTRACIÓN DE BASES DE DATOS...............................................................9

USAR AUTOFILTER........................................................................................................10USAR CRITERIOS COMPUESTOS.......................................................................................11EXTRAER REGISTROS ÚNICOS.........................................................................................13USAR FUNCIONES DE BASES DE DATOS.........................................................................14CREAR UNA TABLA PIVOTE...........................................................................................15IMPORTAR DATOS..........................................................................................................17

LÓGICA IF......................................................................................................................19

EMPLEAR ANÁLISIS DE TENDENCIA...............................................................................20UTILIZAR GOAL SEEK....................................................................................................21UTILIZAR EL SCENARIO MANAGER................................................................................21AUDITAR UNA HOJA DE CÁLCULO.................................................................................23UTILIZAR LA VALIDACIÓN DE DATOS............................................................................23UTILIZAR LA FUNCIÓN IF...............................................................................................25ANIDAR FUNCIONES IF..................................................................................................26COMBINAR FUNCIONES LÓGICAS...................................................................................26

ADMINISTRACIÓN AVANZADA DE DATOS.........................................................28

UTILIZAR COUNTIF......................................................................................................28UTILIZAR SUMIF...........................................................................................................29UTILIZAR HLOOKUP....................................................................................................29UTILIZAR VLOOKUP....................................................................................................30

VISUAL BASIC...............................................................................................................32

GRABAR UNA MACRO....................................................................................................32DEPURAR UNA MACRO..................................................................................................34EDITAR UNA MACRO......................................................................................................35CREAR UN BOTÓN DE MACRO.......................................................................................35

FUNCIONES DEFINIDAS POR EL USUARIO..........................................................37

CREAR UN PROCEDIMIENTO DE UNA FUNCIÓN...............................................................37UTILIZAR MESSAGE BOX...............................................................................................38Controlar el Flujo del Procedimiento............................................................................39

1


Junio de 2003

Lo básico de las Bases de Datos

Un directorio telefonico es un tipo de base de datos que regularmente usa usted. Cuando trata de localizar un numer, direccion o nombre en particular, esta usted imponiendo criterios a toda la base de datos para extraer los registros que desea. Este es un primer ejemplo del uso de una base de daots para organizar y extraer informacion.

Excel consiste de tres componentes principales: hojas de calculo, graficas que usan los datos de las hojas de calculo y una base de datos con caracteristicas que puede manipular. Puede pensar en una base de datos como un archivo de tarjetas donde guarda informacion interrelacionada. Cada tarjeta tiene un formato comun para ingresar las caracteristicas de cualquier cosa que este catalogado. Usted usa los datos contenidos en la base de datos para ordenarlos segun criterios o filtros especificos.

Debe usar el sentido comun al crear una base de datos, defina una meta logica y sea consistente al ingresar los datos. Debe de tener una idea de como seran usados los datos, de modo que pueda extraer lo que efectivamente necesite.

Cada entrada en un directorio telefonico es un registro por separado. En una hoja de calculo, un registro consiste de un renglon de datos, y cada elemento de los registros existe en campos etiquetados, los cuales en Excel son celdas. Lo mejor es separar sus datos en el mayor numer de elementos, o campos, posibles, de modo que tenga mas opciones al ordenar, extraer y seleccionar sus datos. Por ejemplo, es mejor usar campos separados para la ciudad, el estado y el codigo postal, que combinarlos en un campo. Si desea ordenar sus registros segun el codigo postal y ha combinado los elementos en un campo, se vera en dificultades.

Una base de datos se crea muy a la manera de una hoja de calculo, usando las mismas tecnicas de entrada de datos y formateo. Es entonces simplemente cuestion de definir los rangos de la base de datos, de los criterios y de salidas, de manera que Excel pueda manipular la informacion.

Crear una base de datos y uso de Advance Filter.

Una base de datos es un rango continuo de datos en el cual indaga el programa por informacion. Hace pruebas segun un conjunto definido de filtros, conocidos como criterios y reporta los resultados en un rango de salidas.

Una base de datos la crea del modo en la que crearia una hoja de calculo, usando las mismas tecnicas para ingresar registros y etiquetas de Campo. Las etiquetas de campo se ingresan en el renglon superior de la base de datos y describe los elementos en las columnas de campos de datos. Un registro es un renglon de informacion sobre un cliente, una cuenta o alguna parte en particular, introducido horizontalmente en renglones. Las etiquetas de campo se correlacionan con los datos en los campos.

2


Junio de 2003

El poder de una base de datos es su capacidad de proveerle rapida y precisamente de informacion especifica. La caracteristica Advance Filter le permite filtrar su informacion, extrayendo los datosque cumplan sus criterios. Antes de que pueda indagar un una base de datos, debe definir los rangos List y Criteria. Puede elegir que Excel este filtrando la lista en el lugar mismo, o que lo copie a toro rango. Cada uno de los rangos List y Criteria deben incluir los nombres de los campos de la base de datos.

Cree las etiquetas de campo en el renglon superior de su base de datos. Estas etiquetas de campo deben tener nombres distintos, consistir solo de texto y ocupar solo una celda. No pueden exceder de 255 caracteres de largo. Para definir la tabla de criterios, copie el renglon de las etiquetas de campo de la base de datos e insertelas en un area estrategica de la hoja de trabajo. Entonces puede especificar criterios y efectuar una busqueda.

EJERCICIO #1

a) Crear una base de datos. Abre una hoja en blanco de Excel. Selecciona el rango A1:J1 y presiona el boton de Negrilla. Posicionate en la celda A1 y escribe “# Expediente”, teclea Tab. Escribe “Nombre(s)” y teclea Tab. Escribe “Apellido Paterno” y teclea Tab. Escribe “Apellido Materno” y teclea Tab. Escribe “Sexo” y teclea Tab. Escribe “Edad” y teclea Tab. Escribe “# Faltas” y teclea Tab. Escribe “Asignatura” y teclea Tab. Escribe “Examen” y teclea Tab. Escribe “Trabajo Final” y teclea Tab. Selecciona el rango A:J, luego Format> Column> AutoFit Selection Selecciona la celda A2. Lo anterior te debe de dar por resultado algo parecido a lo siguiente:

Por ultimo se empieza a meter datos en la base de datos y listo.

3


Junio de 2003

b) Utilizar Advance Filter. Abre el archivo Alumnos_DB.xls Como se dara cuenta es la misma base de datos anterior, solo que con

algunos registros que nos ayudaran con este ejercicio. Selecciona el rango A1:J1, luego Edit>Copy. Posicionese en la celda N1, luego Edit>Paste. Seleccione el rango N:W, luego Format>Column>AutoFit Selection Selecione la celda U2 y teclee “Algebra”; este es el criterio que

utilizara para hacer el filtro. Seleccione Data>Filter>Advance Filter. Selecciona el rango N:W, luego Format>Column>AutoFit Selection Selecciona la celda N3. Se abre una ventana de dialogo como la siguiente:

Bajo Action selecciona Copy to another location. En List Range:, selecciona el rango A1:J21. En Criteria Range: selecciona el rango N1:W2. En Copy to: selecciona la celda N4. Haz click en OK. Debes de obtener una pantalla como esta:

4


Junio de 2003

Ordenar una base de datos

Uno de los fundamentos en la administracion de la ase de datos es la ordenacion. Puede usted ordenar su base de datos ya sea ascendente (A a la Z) o descendente (Z a la A). Tambien puede ordenar desde el numero mas grande al menor y viceversa.

Excel usa las etiquetas de nombre de campo como las claves de ordenacion. Por ejemplo, si selecciona usted cualquier celda en la columna que desea usar de criterio, y luego abre el dialogo Sort (Data>Sort). Excel pone el nombre del campo en la ordenacion por campos. Puede elegir de una lista de nombres de campos con el proposito de ordenar la base de datos.

EJERCICIO #2: Ordenar registros de acuerdo a criterios.

Confirme que este abierto el archivo casa.xls Haga click en la celda B2 Haga click en el boton de Sort Ascending. Los registros se ordenan

por el numero de pies cuadrados de forma ascendente, del menor al mayor.

Haga click en el boton de Undo. Haga click en la celda F1 Haga click en el boton Sort Descending. Los registros se ordenan

segun el precio en forma descendente, del precio mas alto al mas bajo. Haga click en el boton de Undo. Seleccione Data>Sort…

Haga click en la flecha de Sort by Seleccione Recamaras Seleccione Ascending bajo Sort by Haga click en OK. Los registros se ordenan de manera ascendente. Haga click en la celda A1 Guarde su trabajo.

5


Junio de 2003

Ejecutar ordenaciones multiples

En el ejercicio anterior, usted efectuo lo que equivalia a una ordenacion de la base de datos a una sola clave. Especifico una clave de ordenacion sencilla, basando la ordenacion en una de las etiquetas de campo. Sin embargo, tambien puede efectuar ordenaciones multiples utilizando claves secundarias. Las ordenaciones multiples, en forma ascendente o descendente, rompen con cualquier lazo entre registros con claves primaria identicas.

El orden en el cual Excel acomoda las bases de datos depende de que columnas de datos o referencias a campos hay en el area de claves. El programa ordena la base de datos segun la primera clave. Cuando completa la reordenacion de los registros, ordena los subgrupos creados por la primera ordenacion usando la informacion de la clave secundaria.

EJERCICIO #3: Efectuar Ordenaciones Multiples.

Confirme que este abierto el archivo casa.xls Seleccione Data>Sort… Haga clic en la flecha Sort by… Seleccione Pies Cuadrados Confirme que este seleccionado Ascending bajo Sort by Haga clic en la flecha Then by Seleccione Precio Seleccione Descending junto a Precio Haga clic en OK Guarde su trabajo.

Usar Data Form

El commando Form, hallado en el menu Data, crea tanto el formulario de busqueda como el de entrada de datos, para la administracion de los registros de la base de datos. Antes de seleccionar el commando Form, confirme que el selector de celdas este dentro del rango List, de otro modo Excel no encontrara las etiquetas de campo para crear el formulario.

El formulario opera en uno de dos modos. Excel construye inicialmente un formulario de edicion para propositos de administracion de registros. Usted puede añadir nuevos registros, borrar registros y especificar criterios para encontrar registros en este modo. En este ejercicio, usara usted Data Form para navegar en la base de datos, y añadir un nuevo registro.

6


Junio de 2003

EJERCICIO #4: Usar el Data Form.

Confirme que este abierto el archivo casa.xls Seleccione Data>Form Haga clic dos veces en Find Next. Haga clic dos veces en Find Prev. Haga clic en New. Teclee 106 E. 16th St. <Tab> Teclee 1275 <Tab> Presione Shift+Tab Teclee 1250 <Tab> Teclee 2 <Tab> 2 <Tab> Regadera <Tab> 235000 <Tab> 3-3-96. Haga clic en Close Muevase para abajo para ver el ultimo registro. Guarde su trabajo.

Localizar Registros

Puede localizar registros especificos usando criterios de coincidencia exacta. Los criterios de coincidencia exacta pueden expresarse como una cadena de tezto o expresiones matematicas. Por ejemplo, quizas desea localizar una casa en la vase de datos con un domicilio especifico. Simplemente haga clic en el boton Criteria en el formulario, ingrese la direccion en el campo especificado, y haga clic en los botones desea localizar una casa en la vase de datos con un domicilio especifico. Simplemente haga clic en el boton Criteria en el formulario, ingrese la direccion en el campo especificado, y haga clic en los botones Find Prev y Find Next para mostrar los registros que coinciden con el criterio.

Puede incluso especificar criterios multiples para localizar registros. Por ejemplo, puede usted desear localizar todas las casas en una base de datos que tengan tres recamaras, dos baños y cueste $220,000. Ingrese esta informacion en los campos del formulario, haga clic en el boton de Find Prev o Find Next y se mostraran los registros que cumplan este criterio.

EJERCICIO #5: Localizar registros es una base de datos.

Confirme que este abierto el archivo casa.xls Seleccione Data>Form. Haga clic en Criteria. Haga clic en el campo Recamaras. Teclee 4. Haga clic en Find Next. Haga clic en Find Next. Haga clic en Find Prev. Haga clic en Close.

7


Junio de 2003

Borrar Registros

A medida que crece su ase de datos, los registros que se vuelven obsoletos despedician especio precioso en su archivo. Excel incluye un metodo seguro y efectivo para borrar estos registros indeseables.

Use criterios para remover registros especificos; especifique lso criterios en Data Form para localizar los registros, borrelos entonces. Una vez que haya borrado un registro, no podra deshacer la accion. No obstante, puede cerrar el documento sin guardar los cambios, preservando asi la base de datos en su forma original. De lo contrario, la informacion se borrara definitivamente.

EJERCICIO #6: Borrar Registros de una Base de Datos.

Confirme que este abierto el archivo casa.xls Seleccione Data>Form. Haga clic en Criteria Haga clic en el campo Precio. Teclee 56000 Haga clic en Find Next Haga clic en Delete. Haga clic en OK. Haga clic en Delete. Haga clic en OK. Haga clic en Find Prev. Haga clic en Find Next. Haga clic en Close. Haga clic en el boton Undo. Cierre el archivo SIN GRABAR los cambios.

8


Junio de 2003

Administración de Bases de Datos

Una base de datos creciente refleja el aumento de sus negocios. Probablemente usted tiene varias bases de datos: Clientes, Cuentas por cobrar, listas de servicios y precios, inventarios, etc. Mientras continua añadiendo registros, analizarlos se torna una tarea compleja. Excel encausa esta situacion al permitirle ver grupos de registros basados en criterios especificos.

Los comandos AutoFilter y AdvancedFilter le permiten ver y extraer facilmente registros de la base de datos. La caracteristica AutoFilter obra al permitirle especificar registros suyos como una serie de listas desplegables. Usted simplemente selecciona un elemento hallado en la base de datos bajo el nombre de campo y Excel mostrara todos los elementos que cumplan el mismo criterio que el elemento seleccionado.

Puede mejorar sus busquedas buscando criterios compuestos para crear enunciados condicionales. No es menester que tengan buenas habilidades matematicas para escribir estas declaraciones, solo un entendimiento basico de sintaxis algebraica. Sin embargo es importante saber la constitucion general de los registros en la base de datos, de modo que pueda construir correctamente los enunciados de criterio. La exploracion y uso de la logica Y/O se abarca en este modulo. El operador Y busca y extraer datos que caen dentro de un conjunto de valores. Los datos deben de cumplir ambas condiciones antes de que Excel los extraiga. Si especifica usted una condicion O, entonces Excel muestra los datos si cumplen con cualquiera de las condiciones fijadas en los criterios.

Excel puede efectuar tambien calculos con informacion de la base de datos. Puede usted sumar, encontrar el promedio y encontrar los valores maximos y minimos de su datos.

Excel tiene 12 funciones de base de datos que tienen la letra D precediendo sus nombres para distinguirlas del resto de sus funciones matematicas. Las funciones de bases de datos son unicas, requieren tres conjuntos de argumentos o referencias a celdas para calcular los resultados.

Puede tambien organizar, analizar y resumir los registros de la base de datos con tablas pivote. Use esta caracteristica para reorganizar sus nombres de campos, registros y elementos al cambiar su posicion relativa de renglon o columna.

Puede gastar una Buena cantidad de tiempo y esfuerzo construyendo su base de datos. Afortunadamente, Excel le confiere el poder de administrar su base de datos con facilidad.

Usar AutoFilter

Una base de datos puede contener cientos, sino miles de registros, siendo dificil el verle registros seleccionados basados en criterios especificos. La caracteristica AutoFilter le permite ver registros de la base de datos basados en elementos bajo un nombre particular de campo.

9


Junio de 2003

Por ejemplo, la base de datos que usara en este ejercicio contiene los nombres de campo, direccion, pies cuadrados, recamaras, baños, precio, fecha de lista y fecha de venta. Cuando se activa AutoFilter, aparece un boton de fecha proximo a cada nombre de campo. Si hace clic en una flecha de boton, se abre una lista de elementos. Si hace clic en la flecha del boton precio, por ejemplo, vera usted una lista de precios de casa bajo el nombre de campo como elementos de la columna. Cuando selecciona usted cualquier elemento, AutoFilter muestra automaticamente aquellos elementos que cumplan los criterios.

Al seleccionar la opcion (Top 10…) en cualquier lista AutoFilter se abre el cuadro de dialogo Top 10 AutoFilter. El cuadro de dialogo le permite mostrar los valores superior e inferior en la lista, sin ordenar.

EJERCICIO #7: Usar AutoFilter

Confirme que este abierto el archivo casa.xls Seleccione Data>Filter>AutoFilter Haga clic en la flecha Recamaras Seleccione 3 Haga clic en la flecha Recamaras Seleccione (All…)

Haga clic en la flecha Precio Seleccione (Top 10…) Haga clic en OK Haga clic en la flecha Precio Seleccione (Top 10…) Teclee 5 Haga clic en OK Haga clic en la flecha Precio Seleccione (Top 10…) Teclee 10 Haga clic en la flecha de Items Seleccione Percent. Haga clic en OK

10


Junio de 2003

Haga clic en la flecha Precio Seleccione (All…) Guarde su trabajo

Usar criterios compuestos

En el ejercicio previo, efectuo lo que se conoce como busquedas de coincidencia exacta. Hay veces que no desea que se defina tan sencillamente sus datos. Por ejemplo, ¿Que hay si desea saber que casa de listan entre $105,000 y $225,000 y han estado en el Mercado por mas de dos meses? Puede obtener esta informacion usando enunciados condicionales. Puede personalizar la caracteristica AutoFilter para crear estas busquedas complejas.

Un operador le permite encontrar registros basados en valores numericos. El escoger los operadores algebraicos correctos le permitira mejorar sus busquedas y construir enunciados condicionales.

Puede tambien usar operadores logicos en sus criterios, estos le permiten construir enunciados compuestos.

Cuando un enunciado compuesto especifica que los registros deben cumplir, antes de ser extraidos, ambas condiciones fijadas, esta usted usando logica Y. Cuando los registros cumplen cualquiera de las condiciones en un enunciado compuesto, esta usted empleando logica O.

EJERCICIO #8: Usar criterios compuestos.

Confirme que el archivo casa.xls este abierto. Confirme que este activo AutoFilter. Haga clic en la flecha Precio. Seleccione (Custom…) Haga clic en la flecha donde dice equals. Seleccione Is greater than or equal to. Haga clic en la flecha del control que esta arriba en la derecha. Seleccione $125000

11


Junio de 2003

Haga clic en el contro de abajo en la izquierda. Seleccione Is less than or equal to Haga clic en la flecha del control de abajo a la derecha Seleccione $220000 Confirme que este seleccionada la opcion AND

Haga clic en OK Haga clic en la flecha Precio. Seleccione (Custom…) Haga clic en el control que dice Is greater than or equal to Seleccione Is less than Haga clic en el control que dice Is less than or equal to Seleccione Is greater than Seleccione Or

Haga clic en OK Seleccione Data>Filter>AutoFilter. Deje el archivo abierto.

Extraer registros únicos

La extraccion encuentra todos los registros que cumplan criterios especificos, lugo filtra la lista en el lugar mismo o copia cada registro coincidente en un area de extraccion especial. Puede tambien emplear una extraccion unica para encontrar registros. Una extraccion unica no lista datos duplicados en la salida. Encontrara util esta

12


Junio de 2003

caracteristica en bases de datos extensas donde un registro puede ser introducido accidentalmente mas de una vez.

Otro uso comun de la extraccion unica es listar solo una porcion de cada registro que cumpla el criterio. Por ejemplo, puede desear una lista de todos los precios de las casas en la base de datos. Esto implica redefinir los rangos de base de datos, de criterios y de salidas para filtrar un conjunto de elementos especificos de esos registros.

EJERCICIO #9: Extraer registros unicos.

Confirme que el archivo casa.xls este abierto. Posicionese en la celda A25 Seleccione el rango A25:H25 Haga clic en el boton Copy Seleccione la celda A26 Presione Enter (Ahora tiene dos registros identicos). Seleccione la celda A1 Seleccione el rango A1:H1 Haga clic en el boton Copy Seleccione la celda K1 Presione Enter Seleccione la celda M2 Teclee 6 y presione Enter Presione Control + Home Seleccione Data>Filter>Advanced Filter…

Seleccione Copy to another location bajo Action. Haga clic en la flecha de Criteria range: Seleccione el rango $K$1:$R$2 En el campo Copy to:, teclee $K$4:$R$4 Marque la cajita de Unique records only Haga clic en OK Deje abierto el archivo.

Usar funciones de Bases de Datos

Excel incluye funciones que incluyen funciones que efectuan un amplio rango de calculos de informacion de la base de datos. El usar estas funciones implica un conjunto

13


Junio de 2003

estandar de argumentos; deben de ingresarse condiciones en las formulas. La sintaxis para una funcion de base de datos es:

=Nombre de Funcion(base de datos, campo, criterio)

Las funciones que efectuan calculos en base de datos tienen la letra d como prefijo a sus nombres para distinguirlas de otras, tal como dsum y daverage. El argumento de base de datos consiste en el rango con nombre o del de lista incluyendo los nombres de campo, que contiene toda la base de datos. El argumento campo indica que campo se usa en la funcion. El argumento criterio, consiste de la direccion de la celda o rango que contiene los criterio de la base de datos.

Las tres funciones que usara en el ejercicio son: =dmax, =dmin e =dsum. Las funciones =dmax e =dmin, regresan los valores maximo y minimo en un campo que cumplan los criterios, mientras que =dsum calcula la suma de valores en un campo seleccionado.

EJERCICIO #10: Usar funciones de Base de Datos.

Confirme que el archivo casa_A.xls este abierto. Seleccione la celda M2 Teclee 3 y presione Enter Seleccione la celda I3 Teclee Maximo y presione Enter Teclee Minimo y presione Enter Teclee Total y presione Enter Haga clic en la celda J3 Teclee =DMAX (A1<F4>:H25<F4>,F1<F4>,M1<F4>:M2<F4>) y

presione Enter Copie la formula hasta J5 Seleccione la celda J4 Presione la tecla <F2> En lugar de DMAX, teclee DMIN y presione Enter Seleccione la celda J5 Presione la tecla <F2> En lugar de DMAX, teclee DSUM y presione Enter Seleccione el rango J3:J5 Haga clic en el boton Comma Style y quite todos los decimales. Haga clic en la celda M2 Teclee 1 y presione Enter Deje abierto el archivo.

Crear una Tabla Pivote

El wizard Pivot Table le permite organizar los registros de una base de datos, o lista, en un formato de tabulaciones cruzadas. Cuando selecciona los nombres de campo

14


Junio de 2003

para situarlos en la tabla pivote. Excel resume solo aquellos elementos hallados bajo los nombres de campo seleccionados.

Puede organizar los datos por el nombre de campo en las areas ROW, COLUMN, DATA y PAGE. Si pone un nombre de campo en el area de ROW, aparece un resumen de esos elementos como etiquetas de renglon bajo el lado izquierdo de la tabla pivote. Analogamente, si pone un nombre de campo de la base de datos en el area COLUMN, los elementos aparecen a lo largo de la parte superior de la tabla pivote, como etiquetas de columna.

El area de datos de una tabla pivote efectua calculos en los nombres de campo los cuales contienen elementos numericos. Puede sumar, promediar, contra o encontrar los valores maximo y minimo de los elementos numericos de la base de datos. Finalmente, al poner el nombre de campo en el area PAGE alista en la tabla los datos, un elemento a la vez.

Ya que Excel completa la tabla picote, puede reorganizar los datos. Puede cambiar la orientacion de las etiquetas de campo simplemente al arrastrarlas a diferentes posiciones. Puede tambiern añadir elementos a la tabla pivote.

EJERCICIO #11: Crear una Tabla Pivote.

Confirme que el archivo casa_A.xls este abierto. Seleccione Data>Pivot Table Report…

Asegurese que Microsoft Excel list or database este seleccionado. Haga clic en Next> Asegurese que el rango seleccionado bajo Range: sea el de la base de

datos. Haga clic en Next> Haga clic en Layout…

15


Junio de 2003

Arrastre el boton de Recamaras hasta el area de Row Arrastre el boton de Price hasta el area de Column Arrastre el boton de Pies Cuadrados hasta el area Data Haga doble clic en Sum of Pies Cuadrados en el area de Data Seleccione Max bajo Summarize by: Haga clic en OK Haga clic en Existing Worksheet Haga clic en la pestaña Sheet 2 y seleccione la celda A1 Haga clic en Finish

Importar Datos

Si es necesario exportar e importar los datos a otras aplicaciones, puede usar archivos de texto como un metodo para intercambiar informacion. La mayoria de las aplicaciones, prescindiendo de la plataforma, aceptaran archivos de texto en formato ASCII.

Pede exportar facilmente a archivo ya sea a un archivo de texto delimitado por tabulaciones, delimitado por espacion, o delimitado por comas al seleccionar el tipo apropiado de archivo de la lista Save As Type, hallada en el cuadro de dialogo Save As. El

16


Junio de 2003

texto delimitado tiene un registro por linea, donde los valores de los campos se separan usando, ya se a caracteres especiales o espacios y tabulaciones fisicas.

El wizard Text Import puede asistirles cuando importa archivos ASCII a hojas de trabajo de Excel. Cuando abre un archivo de texto delimitado, se activa el Wizard: . El texto delimitado tiene un registro por linea, donde los valores de los campos se separan usando, ya se a caracteres especiales o espacios y tabulaciones fisicas.

El wizard Text Import puede asistirles cuando importa archivos ASCII a hojas de trabajo de Excel. Cuando abre un archivo de texto delimitado, se activa el Wizard: Text Import. Un area de prevista en el cuadro de dialogo le permite determinar el tipo original de datos (Delimitada o de anchura fija) los delimitadores usados (Tabulaciones, puntos y comas, comas, espacios u otros), y el formato de datos (general, texto, o flecha) que desee aplicar a los datos importados. Puede elegir el eximir los datos de ser importados.

EJERCICIO #12: Importar Datos.

Confirme que Excel este abierto en un archivo Nuevo. Haga clic en el boton Open. Confirme que este seleccionada la carpeta Ejercicios Haga clic en la flecha Files of type: Seleccione Text Files Seleccione Casa_A.txt

Haga clic en Open Confirme que Delimited este seleccionado bajo Original Data Type.

17


Junio de 2003

Haga clic en Next> Confirme que Tab este seleccionado bajo Delimiters.

Haga clic en Next>

Navegue en la parte de Data Preview, cerciorese de todo este bien. Haga clic en Finish Cierre el archivo.

18


Junio de 2003

Lógica IF

Excel puede hacer mas que tan solo calcular y organizar nùmeros. El programa incluye características que analizan los datos de cierta forma para predecir ciertos resultados. Este análisis y si se realiza al utilizar las operaciones y funciones lògicas Scenario Manager, Goal Seek.

Por ejemplo, si por los últimos tres años ha guardado un registro cuidadoso de las ganancias y perdida de su empresa, puede predecir una tendencia. Una tendencia es una serie de datos que sigue un patrón especifico. Excel le permite calcular una serie linear y de crecimiento utilizando opciones especiales.

Una vez que predijo las tendencias , el Scenario Manager puede darle varios resultados. Suponga que predijo los escenarios de mejor resultado y peor resultado v para los siguientes tres años . El Scenario Manager puede guardar varios juegos de datos y le permite ver la actualización instantanea de varios resultados.

Excel tambien utiliza varias funciones lógicas especiales para determinar si el resultado de una prueba en particular es Verdadero o Falso. Estas funciones lógicas prueban el contenido de una celda o un rango de celdas contra ciertas condiciones , despues muestran un resultado basándose en lo que se obtuvo en la prueba. Por ejemplo, puede hacer que la funcion IF muestre un resultado en texto o numèrico en una celda dependiendo con lo obtenido. Tambièn puede refinar sus pruebas al combinar las funciones lógicas. Las funciones AND, OR y NOT le permiten probar varios juegos de condiciones.

Si tiene preguntas que comienzan con what if ( y si), entonces utilice el poder analítico de Excel para encontrar las respuestas. La habilidad del programa para calcular tendencias. manejar varios escenarios y realizar pruebas lógicas hacen que esto sea muy sencillo.

Emplear análisis de Tendencia

Una tendencia es un pátron economico que ocurre en un periodo de tiempo definido. Por ejemplo, un dueño de un negocio puede haber notado las fluctuaciones en las ganancias durante varios años . El dueño se preguntara "si este patron continua, que ganancias o perdidas resultaran . Excel es completamente capaz de analizar dichas tendencias.

Existen dos tipos de tendencias. La primera es un patron lineal. L os patrones lineales pueden ser tendencias aritmeticas o exponenciales . Una tendencia aritmética es una simple suma de valores: 2, 4, 6, 8. Una tendencia exponencial simple es una multiplicacion de valores: 2, 4, 16, 256, 65536. La funcion TREND detecta estos patrones y otros mas complejos, como 1, -3, 8.25, 21. En el ejercicio, explorara tanto la tendencia lineal como la de crecimiento.

19


Junio de 2003

La funcion estadistica TRND consiste de cuatro argumentos, tres de los cuales son opcionales . El primer es known-y' s, que es un renglon o columna de valores donde la relacion se conoce. El segundo es known-x' s, como known-y' s, este nuevamente es un renglon o columna de datos numericos existentes conocidos. El tercer argumento new-x' s, son las direcciones de la celda donde desea ver una extension del patron actual. El ultimo argumento, const, regresa un valor logico, ya sea , VERDADERO O FALSO, que determina si se forzara la constante o si se igualara a cero.

Tambien puede utilizar el cuadro de dialogo Series para crear patrones lineales. Seleccione las celdas que contiene el patron y las celdas en donde desea continuar la tendencia. S eleccione la opcion Trend en el cuadro de dialogo y Excel analiza la tendencia existente, extendiendola al rango vacio.

EJERCICIO #13: Emplear Analisis de Tendencia

Confirme que el archivo Financial.xls este abierto. Seleccione el rango B2:B10 Arrastre la manija AutoFill a B15 Seleccione el rango C2:C10 Arrastre la manija AutoFill a C15 Haga clic, dos veces, en el boton Undo Seleccione el rango B2:C15 Seleccione Edit>Fill>Series… Confirme que Linear se seleccione bajo Type Haga clic en Trend. Haga clic en OK. Haga clic en el boton Undo. Seleccione Edit>Fill>Series… Haga clic en Trend. Seleccione Growth bajo Type. Haga clic en OK. Haga clic en el boton de Undo. Seleccione la celda B11. Teclee =TREND(B$2:B$10,$A$2:$A$10,$A11,0) Arrastre la manija de AutoFill hasta C15. Guarde su trabajo

Utilizar Goal Seek

Normalmente una formula calcula un resultado basandose en los registros o entradas numericas en un rango. Si se cambian las entradas numericas, la formula calcula nuevamente el resultado. Sin embargo, que pasa si desea que la formula calcule un total especifico.

El resultado que se puede tomar con "trabajar hacia atras", o cualcular hacia atras, se conoce como goal seek. Excel busca cierto resultado que elige al forzar el total a

20


Junio de 2003

cambiar. La caracteristica Goal Seek le permite especificar una celda de formula, un valor de destino y una celda de variable para buscar cierto resultado. Goal Seek, resuelve el problema al ajustar el valor en la celda de ajuste hasta que la formula calcula el resultado que desea.

Debe definir tres variables para un Goal Seek: la celda de la formula, el valor y la celda de ajuste. L a celda de la formula es la celda que contiene la formula real. El valor es el resultado que desea que calcule la formula . L a celda de ajuste es el valor dependiente de la formula que desea cambiar.

EJERCICIO #13: Utilizar Goal Seek

Confirme que el archivo Financial_A.xls este abierto. Haga clic en la celda E16 Seleccione Tools>Goal Seek… Teclee 1500000 bajo To value: Teclee C16 bajo By changing cell: Haga clic en OK Haga clic en OK Guarde su trabajo.

Utilizar el Scenario Manager

Cada juego de variables que cicla a traves de un modelo What-if (˜y-si) se conoce como scenario. Un scenario es un juego de valores que representan un resultado posible. Por ejemplo puede definir un juego de valores como un scenario lo mejor y otro scenario como lo peor. El scenario manager le permite guardar y nombrar estas versiones para referencias futuras. Para guardar los valores en cada version en rangos con nombre o referencias absolutas de direcciones de celda.

Registra un juego de datos dentro de un rango y le asigna un nombre de scenario. Entonces guarda cualquier juego subsecuente de datos por su propio nombre de scenario. El scenario manager le permite cambiar entre los juegos de datos para que pueda ver las diferencias.

EJERCICIO #14: Utilizar el Scenario Manager.

Confirme que el archivo Financial_A.xls este abierto. Seleccione Tools>Scenarios… Haga clic en Add… Teclee Best Case. Seleccione el rango B16:C16 bajo Changing cell:

21


Junio de 2003

Haga clic en OK Haga clic en Close Seleccione la celda B16 Teclee 180000 y presione la tecla <Tab> Teclee 120000 Seleccione el rango B16:C16 Seleccione Tools>Scenarios… Haga clic en Add… Teclee Worst Case. Haga clic en OK dos veces. Seleccione Best Case bajo Scenarios… Haga clic en Show Si es necesario, mueva el cuadro de dialogo para ver los resultados. Haga clic en Close. Guarde su trabajo.

Auditar una Hoja de Cálculo

Puede asegurarse de la precision de las formulas y encontrar errores en hojas de trabajo complejas con las opciones Auditing. Cuando auditan una hoja de calculo, Excel determina la relacion entre las formulas y las celdas con las que tiene referencia.

Las celdas precedentes son las que dan los datos a la formula, mientras que las dependientes son aquellas celdas que contienen las formulas que se referencian a otras celdas. Cuando Excel localiza las celdas precedentes o dependientes , aplica flechas de rastreo a dichas celdas. Las flechas de rastreo muestran la relacion entre la celda activa y las celdas relacionadas.

Cuando rasrea las celdas precedentes, primero debe seleccionar la celda de la formula para que Excel pueda aplicar las flechas de rastreo. De la misma forma, una celda o rango que suministra los datos a la formula se debe seleccionar antes de rastrear las celda dependientes. Si aparece un error de mensaje, como #DIV/0! o #VALUE en la celda, debe seleccionar la celda antes de que Excel pueda rastrear el error.EJERCICIO #15: Auditar una Hoja de Calculo.

22


Junio de 2003

Confirme que el archivo Financial_A.xls este abierto. Haga clic en la celda E16 Seleccione Tools>Auditing>Trace Precedents. Seleccione Tools>Auditing>Show Auditing Toolbar. Haga clic en la celda B6 Haga clic en el boton Trace Dependents. Haga clic en la celda C12. Haga clic en el boton Trace Dependents. Haga clic en el boton Remove All Arrows. Quedese en esa pantalla.

Utilizar la validación de Datos

La validacion de datos le proporciona el usuario las claves visuales de tipos de datos aceptables y valores que pueden ocupar una celda que selecciono un rango. Si un usuario ingresa un tipo de datos que no sea valido, aparece un mensaje de error.

El cuadro de dialogo Data Validation le permite definir el criterio de validacion, un mensaje de entrada y un mensaje de error. El criterio de validacion puede ser cualquier valor, un numero entero, decimal, una lista, una fecha, o cierta cantidad de texto. Tambien puede definir criterios personalizados para validar la entrada de datos.

Para auditar una hoja de calculo para entrada que no son validas, haga clic en el boton Circle Invalid Data en la barrar de herramientas Auditing. Aparece un circulo rojo alrededor de cualquier entrada que viole el criterio de validacion y puede localizar facilmente y corregir dicha entrada.

EJERCICIO #16: Utilizar la validacion de Datos.

Confirme que el archivo Alumnos_DB.xls este abierto. Seleccione el rango A2:A21. Presione Ctrl+1 y bajo Category seleccione Text. Seleccione Data>Validation.

Haga clic en la flecha Allow: Seleccione Text length.

23


Junio de 2003

Haga clic en la flecha Data: Seleccione equal to Haga clic en el campo Length: Teclee 7 Haga clic en el tabulador Input.

Haga clic en el campo Title: Teclee Recuerde: <Tab> No puede ser diferente a 7 digitos. Haga clic en el Tabulador Error Alert

Haga clic en la flecha de Style: Seleccione Warning. Haga clic en el campo Title: Teclee Advertencia! <Tab> El campo debe tener solo 7 digitos. Haga clic en OK Haga clic en la celda A8 Teclee 95270893 Haga clic en Yes Seleccione Tools>Auditing>Show Auditing Toolbar. Haga clic en el boton Circle Invalid Data Cierre el archivo.

Utilizar la función IF

Puede utilizar una funcion logica IF para probar los datos y reportar diferentes resultados basandose en ellos. Cuando utiliza IF en una formula, revisa las condiciones

24


Junio de 2003

que se especifican en la celda o en el rango, despues muestra el resultado basandose en lo que salio.

Una funcion IF consiste de tres argumentos: la prueba, un argumento IF True (si es verdadero) y un argumento IF False (si es falso). Si las condiciones no pasan la prueba y son falsas, IF muestra un resultado alternativo.

Tambien puede utilizar las funciones IF para registrar texto y letras dentro de las celdas. Por ejemplo, puede escribir una funcion IF para probar el contenido de una celda, despues ingresar un texto especifico o dejar la celda en blanco. El texto y las letras se designan con las comillas ("" ). L o que se teclea o no se teclea entre las comillas aparece en la celda despues de la prueba.

EJERCICIO #17: Utilizar la funcion IF

Confirme que el archivo Financial_B.xls este abierto. Seleccione la celda E1 Teclee Estatus y presione Enter. Teclee =IF(D2>0,”Utilidad”,”Perdida”) Copie la formula hasta la celda E15 Seleccione la celda F1 Teclee Utilidad <Tab> Perdida y presione Enter. Teclee =IF(E2<F4, 3 veces> =F1<F4, 2 veces>, B2 <F4, 3 veces> -

C2 <F4, 3 veces>, “”) Copie la formula hasta la celda G15. Haga clic en la celda F16 Haga clic en el boton de Sumatoria en la barra de herramientas. Copie la formula a la celda G16 Cierre el archivo.

Anidar Funciones IF

Puede anidar, o colocar una funcion IF dentro de otra, mejorando el proceso logico de la formula. Las funciones IF anidadas le permite crear pruebas logicas refinadas.

Por ejemplo considere la siguiente funcion IF anidada:=IF(B24>10000, B24 *B26 , IF(B32>4,B24*B26,B24*F26)).

Esta prueba depende en si el valor en B24 es o no mayor a 10,000. Si lo es, la forma multiplica a B24 por otro valor en B26. Sin embargo, si el valor es menor o igual a 10,000, la prueba IF anidada calcula con el valor en B32. Si B32 es mayor que 4, B24 se multiplica por B26 , de otra forma B24 se multiplica por F26 si el valor en B32 es menor o igual a 4.

25


Junio de 2003

Puede anidar hasta siete funciones IF dentro de una misma formula. El uso de los IF anidados es dependiente en su habilidad en saber utilizar los argumentos y la sintaxis correcta.

EJERCICIO #18: Anidar funciones IF

Confirme que el archivo Alumnos_DB2.xls este abierto. Seleccione la celda K1 Teclee Regularizacion? Teclee =IF (I14<60, "Si", IF (H14="Matematicas I", IF (G14>3,

"Si", "No"), IF (G14>1, "Si", "No"))) Copie la formula hasta la celda K21 Por ultimo mueva el criterio en las celdas correspondientes. Cierre el archivo.

Combinar Funciones Lógicas

Mientras que las funciones IF anidadas pueden llevar a cabo pruebas muy refinadas, usted puede crear formulas mas eficientes al combinar otras funciones logicas con la funcion logicas son AND, OR y NOT.

La sintaxis correcta para combinar cualquier funcion logica con la funcion IF es la siguiente:

=IF (logical-function-name (logical_test,value_if_true,value_if_false))

La funcion logica AND requiere que todas las condiciones que se prueban en el valor de True ( verdadero). Si todas las condiciones son verdaderas, entonces la formula IF muestra el argumento value-if –true. (valor-si es-verdadero) si cualquiera de las condiciones no pasa la prueba, entonces se calcula el valor a partir del argumento value-if-False (valor-si es-Falso).

L a funcion logica OR prueba condiciones multiples para determinar si cualquiera de ellas es Verdadera . S i cualquier condicion resulta verdadera, entonces se calcula el valor apartir del argumento value-if—true (valor si es verdad). Si ninguna condicion resulta verdadera, entonces se muestra el argumento value if false.

La formula de la funcion logica NOT da un valor inverso para las condiciones que probo. En este sentido, una condicion Verdadera se muestra como falsa , y viceversa. No puede utilizar NOT cuando quiere asegurarse que un valor no es igual a otro valor en particular.

EJERCICIO #19: Combinar IF y funciones logicas.

Confirme que el archivo Alumnos_DB2.xls este abierto. Seleccione la celda K1.

26


Junio de 2003

Teclee Aprobo/Regularizacion y presione Enter. Teclee =IF(AND(G2<3,I2>=60),"Aprobo","Regularizacion") Copie la formula hasta la celda K21. Copie la celda K1 a L1 y presione Enter. Teclee =IF(OR(G2>4,I2<60),"Regularizacion","Aprobo") Seleccione la celda J23. Teclee Aprobo y presione Enter Teclee Regularizacion y presione Enter Seleccione la celda K23. Compare y saque conclusions Cierre el archivo.

Administración Avanzada de Datos

Aunque un programa de hojas de calculo es muy util cuando se refiere a organizar datos numericos, algunas veces localizar la informacion puede ser dificil, especilmente cuando la hoja de calculo es muy grande. Tal vez quiera crear una macro que utilice informacion particular repetidamente, y por lo tanto necesita acceso rapido a los datos. Para resolver este problema, Excel tiene capacidades que le permiten encontrar los datos basandose en un criterio en particular. Por ejemplo, puede ser el responsable de la nomina de la empresa y necesita saber la cantidad exacta que va a deducir de cada salario.

Si tales datos existen dentro de una tabla, los puede extraer dentro de una tabla, los puede extraer utilizando las funciones de busqueda. Las funciones tales como HLOOKUP Y VLOOKUP le pueden ayudar a rastrear diferentes tipos de datos. Puede utilizar las funciones de busqueda para buacar ventas, direcciones, numeros telefonicos y casi cualquier cosa. Si tiene una base de datos con cientos de registros, definitivamente necesitara utilizar las funciones de busqueda.

Si necesita analizar los datos que estan influenciados por diversas variables, Excel tambien le ayuda en ellos. Por ejemplo, tal vez querra analizar el costo de un prestamo con diferentes tasas de interes, cantidades y terminos, el efecto de los descuentos en su inventario y sus ventas, o la comision de un ejecutivo de cuenta. Para este tipo de cuenta puede crear tablas What IF con una o dos variables. Puede utilizar estas tablas para calcular instantaneamente los efectos de las diferentes variables en sus cantidades. Tambien puede utilizar las tablas What IF para calcular escenarios, contestando preguntas complejas, tales como Que pasa si necesito pedir un prestamo de cierta cantidad en dolares o Que pasa si descuento todo mi inventario en un veinte porciento.

27


Junio de 2003

Utilizar COUNTIF

Dentro de lo que comprende la administracion avanzada de Base de Datos, en ocasiones nos encontramos con el hecho de contar los registros que cumplan con un criterio especifico, por ejemplo en el directorio telefonico queremos saber cuantos contratos tenemos con personas cuyo apellido paterno es Martinez; para ayudarnos con esta situacion, Excel cuenta con una funcion llamada COUNTIF.

La sintaxis para esta funcion es la siguiente:

=COUNTIF( <rango o campo>,<criterio>)

El rango es especificamente el campo de datos que contiene la informacion que deseamos contar, asi como el criterio que nos ayudara a lograr esta tarea; el criterio puede ser numerico (999999), alfanumerico (lfd998fdak7676) o algunas condicionantes como los simbolos de <,>,<>,<= ó >=, solo que para este ultimo caso (asi como el alfanumerico), debe de estar entre comillas (“”).

EJERCICIO #20: Utilizar COUNTIF.

Confirme que el archivo Produccion.xls este abierto. Seleccione la celda G4 Teclee Turno <Tab> Frecuencia y presione Enter. Teclee Mat y presione Enter Teclee Vesp y presione Enter Seleccione la celda H5 Teclee la formula =COUNTIF($B$2:$B$125,G5) Copie la formula hasta la celda H6. Cierre el archivo.

Utilizar SUMIF

En el ejercicio anterior se vio lo que es contar registros dado algun criterio, ahora lo que se vera es la suma de valores en el registro dado algun criterio. Esto se refiere a que lo que deseamos hacer es sumar las cantidades de un campo, pero no todo el campo, sino aquellos registros que cumplan con las caracteristicas necesarias para lograr el resultado que estamos buscando.

La funcion de Excel que nos ayuda a llevar a cabo esto es SUMIF, cuya sintaxis es la siguiente:

=SUMIF(<rango>,<criterio>,<rango de suma>)

rango es el conjunto de datos donde se encuentra el criterio, o dicho de otra manera, es el conjunto de datos de donde determinaremos cuales registros se van a

28


Junio de 2003

sumar; criterio es la condicion que nosotros mismos damos para determinar cuales seran los registros a sumar, el tipo de condicion aplica igual que en countif; y por ultimo el rango de suma se refiere al conjunto de datos sobre la cual se efectuara la suma.

EJERCICIO #21: Utilizar SUMIF

Confirme que el archivo Produccion_A.xls este abierto. Seleccione la celda J4. Teclee Produccion <Tab> Minutos <Tab> Prod/Min y teclee Enter En la celda J5 teclee =SUMIF($B$2:$B$125,$G5,C$2:C$125) Copie la formula hasta la celda K6 En la celda L5 teclee =J5/K5 Copie la formula hasta L6 Cierre su trabajo.

Utilizar HLOOKUP

Si utiliza una hoja de calculo como una tabla de datos que reporta informacion cambiante, encontrara que las funciones de busqueda son muy utiles. Tal vez le cobra a sus clientes diferentes precios basandose en el tiempo de su contrato, o la distancia de viajes. Puede tener diferentes precios para areas de servicio en una region. El proposito de una busqueda es que le permite ingresar una de esas cantidades, tiempos o zaonas diferentes en una celda de comparacion, y mostrar rapidamente las informaciones correspondientes. Por ejemplo, una busqueda puede agrupar el nombre de un ejecutivo de cuenta con varios rangos de comision basandose enla cantidad de venta.

Una busqueda horizontal trabaja buscando el renglon superior de la tabla de datos de forma horizontal. Una vez que encontro la etiqueta que se asemeja al valor de la busqueda que se encuentra en la celda de comparacion, se mueve hacia abajo de la columna.

L as sintaxis de la funcion HLOOKUP: es HLOOKUP (lookup-value, table-array, ro w-index-num, range-lookup). Las funcion HLOOKUP tiene cuatro juegos de argumentos:

1.- El argumento lookup-value es la referencia o celda de comparacion.

2.- El argumento Table-array es el rango dela tabla de datos donde se busca.

3.- El argumento row-index-num indica cuantas celdas se debe mover hacia abajo ( debe ser 1 o mas de 1)

4.-El argumento range-lookup es un argumento logico que da el valor de Verdadero or False. Este argumento es opcional.

29


Junio de 2003

EJERCICIO #22:Utilizar HLOOKUP

Confirme que el archivo Produccion_B.xls este abierto. Seleccione la celda B8 Teclee Oper2 y presione Enter Teclee =HLOOKUP($B$8,$A$1:$DU$5,2,0) Copie la formula hasta B12 Seleccione B10 Presione la tecla <F2> y cambie en la formula 2 por 3, luego Enter Presione la tecla <F2> y cambie en la formula 2 por 4, luego Enter Presione la tecla <F2> y cambie en la formula 2 por 5, luego Enter Listo, ahora cambia a B8 y teclee Oper14 Cierre el archivo.

Utilizar VLOOKUP

Una busqueda vertical trabaja de forma similar que una busqueda horizontal. La diferencia es que busca los datos en la tabla verticalmente, tratando de agrupar la informacion de comparacion. Una vez que encuentra una etiqueta que se asemeja en la columna izquierda de la tabla de datos, da los valores de esas celdas hacia los valores de esas celdas hacia los valores de esta etiqueta en un cierto rango.

S e utiliza la funcion VLOOKUP para realizar una busqueda vertical. Vlookup utiliza 4 juegos de argumentos, el ultimo de los cuales es opcional. La sintaxis de la funcion es:

VLOOKUP (look-up, table array, col-index-num, rango-lookup)

1.- El argumento lookup-values es la referencia o direccion de la celda de comparacion.

2.- E l argumento Table-array es el rango de tabla de datos donde se va a buscar.

3.- El argumento col-index-num indica cuantas columnas debe mover a la derecha, despues que encuentra una etiqueta que se asemeje.

4.- El argumento opcional range-lookup, es un valor logico, ya sea verdadero o falso que especifica una igualdad exacta. Si se omite el argumento, VLOOKUP da la semejanza aproximada o mas cercana que puece encontrar en la tabla.EJERCICIO #23:Utilizar VLOOKUP

Confirme que el archivo Produccion_C.xls este abierto. Seleccione la celda H3 Teclee Oper25 y presione Enter Teclee =VLOOKUP($H$3,$A$1:$E$125,2) Copie la formula hasta H7

30


Junio de 2003

Seleccione H5 Presione la tecla <F2> y cambie en la formula 2 por 3, luego Enter Presione la tecla <F2> y cambie en la formula 2 por 4, luego Enter Presione la tecla <F2> y cambie en la formula 2 por 5, luego Enter Listo, ahora cambia a H3 y teclee Oper15 Cierre el archivo.

Visual Basic

En nuestra vida de trabajo diario, encontramos que con el paso del tiempo empezamos a realizar algunos reportes de manera rutinaria, es decir, que el metodo de preparacion de un reporte es igual cada vez que lo hago, y es en ese momento cuando empezamos a preguntarnos, ¿Por que no existira un boton para que haga todo esto?

En Excel tenemos la facilidad de crear Macros, es decir, tenemos la ventaja de que ese trabajo rutinario, sea convertido en una tarea automatica, que podemos facilmente ejecutar desde el menu, o podemos asignarle la macro a algun boton de accion. En pocas palabras, podemos hacer nuestro sueño realidad.

La herramienta que se usa para hacer esto es Visual Basic, que es el lenguaje de programacion que soporta todas las aplicaciones de Office, ya que las Macros NO son exclusivas de Excel, tambien se pueden hacer en los otros programas de Office como son Word, Power Point y Access

En este modulo veremos como facilmente podemos crear, depurar y editar Macros, asi mismo veremos como asignar Macros a botones de accion que nos ayuden a que la ejecucion de estas sea todavia mas sencilla.

Grabar una Macro

El grabar una Macro, es el primer paso a la automatizacion de tareas y a la vez el primero hacia la eficientizacion de nuestro trabajo. Se vera la manera mas facil de llevar a cabo la grabacion de una Macro.

EJERCICIO #24: Grabar una Macro.

31


Junio de 2003

Abra un archivo Nuevo en Excel. Seleccione Tools>Macros>Record a new Macro…

Teclee Abrir_Archivos bajo Macro name: Si lo desea cambia la descripcion bajo Description: Haga clic en OK Haga clic en el boton Open Asegurese que este dentro de la carpeta de Ejercicios Abra el archivo Macro1.xls Seleccione toda la base de datos. Presione Ctrl+C Cambiese al archivo que esta creando (Book1.xls) Presione Enter. Seleccione la celda A134 Regrese al archivo Macro1.xls Presione Ctrl+F4 Haga clic en el boton Open Asegurese que este dentro de la carpeta de Ejercicios Abra el archivo Macro2.xls Seleccione toda la base de datos (Ahora desde A2, sin titulos). Presione Ctrl+C Cambiese al archivo que esta creando (Book1.xls) Presione Enter. Seleccione la celda A263 Regrese al archivo Macro2.xls Presione Ctrl+F4 Haga clic en el boton Open Asegurese que este dentro de la carpeta de Ejercicios Abra el archivo Macro3.xls Seleccione toda la base de datos (Ahora desde A2, sin titulos). Presione Ctrl+C Cambiese al archivo que esta creando (Book1.xls) Presione Enter. Seleccione la celda A395 Regrese al archivo Macro3.xls Presione Ctrl+F4 Haga clic en el boton Open

32


Junio de 2003

Asegurese que este dentro de la carpeta de Ejercicios Abra el archivo Macro4.xls Seleccione toda la base de datos (Ahora desde A2, sin titulos). Presione Ctrl+C Cambiese al archivo que esta creando (Book1.xls) Presione Enter. Seleccione la celda A1 Regrese al archivo Macro4.xls Presione Ctrl+F4 Seleccione la celda H7 Teclee Iniciales <Tab> Produccion y presione Enter Seleccione la celda I8 Teclee =SUMIF($A$2:$A$528,H8,$C$2:$C$528) Seleccione el rango H7:I8 Haga clic en el boton de Fill Color Seleccione el color amarillo Haga clic en el boton de Borders. Seleccione All Borders. Seleccione la celda G2 Seleccione Tools>Macros>Stop Recording… Seleccione el rango A:I Presione Ctrl + <-> Seleccione la celda A1 Haga clic en el boton Run Macro de Visual Basic Toolbar Asegurese que Abrir_Archivos este seleccionada Haga clic en Run Comentarios Guarde su trabajo en la carpeta de Ejercicios bajo el nombre

DebugMacro.xls Cierre el archivo.

Depurar una Macro

En muchas ocasiones, al crear (grabar) una Macro, cometemos errores o simplemente algunas condiciones en el archivo ya no son las mismas desde que grabamos la Macro, por lo que aparecen advertencia de error en la Macro.

Incluso a veces ni siquiera hemos corrido la Macro y queremos ver si corre de manera adecuada; para ver paso a paso como se va desarrollando la Macro, tenemos la tecla F8 para ayudarnos con este proceso de depuracion, incluso nos ayuda a comprender lo que va haciendo la Macro en cada linea, por lo que facilmente podemos identificar que comando en Visual Basic hace que en Excel.

EJERCICIO #25: Depurar una Macro.

Abra el archivo DebugMacro.xls

33


Junio de 2003

Seleccione el rango A:I Presione Ctrl + <-> y seleccione la celda A1 Haga clic en el boton Run Macro Asegurese que Abrir_Archivos este seleccionada Haga clic en el boton de Edit Haga la ventada de Visual Basic tan pequeña que le permita ver al

mismo tiempo el codigo de la Macro y Excel. Presione la tecla <F8> y vea como va avanzando la Macro en la

medida que va presionando esta tecla. Marca un error! Haga clic en Debug. Cambie “Book1” por “DebugMacro” Presione <F8> Siga asi hasta que termine de recorrer la Macro Guarde el Archivo. Cierre el archivo.

Editar una Macro

Ahora veamos que hay que hacer si lo que queremos es cambiar algo en la Macro, la edicion de una Macro puede ser a cualquier nivel, y es tan compleja como el cambio mismo que se requiere para la Macro que se esta usando.

La edicion de una Macro se lleva a cabo en Visual Basic, la edicion la hacemos directamente en el codigo, agregando lineas, suprimiendolas o simplemente cambiandolas. En el ejemplo veremos la opcion de agregar lineas a una Macro para que realize otra tarea alterna.

EJERCICIO #26: Editar una Macro.

Abra el archivo EditMacro.xls Hacer clic en Enable Macros… Hacer clic en el boton Run Macro Asegurese que Abrir_Archivos este seleccionada. Haga clic en Run ¿No se obtuvieron los resultados deseados? Hacer clic en el boton Run Macro Asegurese que Abrir_Archivos este seleccionada. Haga clic en Edit Haga clic antes de ChDir y presione Enter Presione una vez la flecha de desplazamiento hacia arriba Teclee Cells.Select y presione Enter Teclee SelectionClearContents y presione Enter Teclee Range(“A1”).Select y cierre la ventana de Visual Basic Hacer clic en el boton Run Macro

34


Junio de 2003

Asegurese que Abrir_Archivos este seleccionada. Haga clic en Run ¿Que paso ahora? Guarde el archivo

Crear un Botón de Macro

Ahora, por ultimo en este modulo, aprenderemos como crear una boton de Macro, en otras palabras, aprenderemos a como asignar una macro a un boton para que esta corra sin tener que estar entrando al menu a buscar el nombre de la Macro.

EJERCICIO #27: Asignar una Macro a un boton de accion.

Asegurese que este abierto el archivo EditMacro.xls Haga clic en Command Button en Control Toolbox Toolbar Dibuje un rectangulo con el Mouse en la hoja, el cual sera el boton. Haga doble clic en el boton (lo llevara a Visual Basic) Entra las lineas Sub y End Sub teclee <Tab>Abrir_Archivos Haga clic con el boton derecho del mouse en el boton de accion Seleccione Properties Teclee Correr Macro en el campo de Caption Cierre la ventana de Properties Haga clic en el boton Design Mode Haga clic en el boton Correr Macro

35


Junio de 2003

Funciones Definidas por el UsuarioCrear un procedimiento de una función

Una formula definida por el usuario es escrita por el usuario y almacenada en un modulo de Visual Basic. Se utiliza con mayor frecuencia para realizar calculos especiales. Crear una funcion definida por el usurario al combinar expersiones matematicas, funciones existentes y el codigo Visual Basic. Las funciones definidas por el usuario dan un valor basandose en los datos que el usuario le proporciona, mientras que las macros que estan grabadas llevan a cabo acciones que alteran las celdas, las hojas de trabajo, etc.

Las funciones definidas por el usuario comienzan y terminan con las palabras clave Function y End Function. Las macros grabadas son en realidad subrutinas y estan designadas por las palabras claves Sub y End Sub. Tambien puede designar una funcion definida por el usuario como Publica o Privada.

La sintaxis de una funcion definida por el usuario consiste del nombre, los argumentos, el codigo, las expresiones y el valor de respuesta. Debe nombrar los procedimientos de su funcion como lo haria con las macros. Los argumentos son los datos que usted da, para que la funcion pueda calcular un resultado. El codigo y las expresiones le informan a la funcion que calculos debe realizar. El valor de respuesta es el resultado que quiere que aparezca despues de que la funcion termina el calculo.

Para especificar un valor de respuesta, siga el nombre de la funcion con una expresion.Por ejemplo, Comission=Subtotal10%, donde subtotal es un rango nombrado en la hoja de trabajo que contiene los datos necesarios para completar los argumentos de la funcion.Tambien puede utilizar las direcciones de la celda, Ej: Comission=E20*10%.

EJERCICIO #28: Crear procedimiento de una Funcion

Abra el archivo Financial_A.xls Seleccione la celda E16 Seleccione Insert>Name>Define… Teclee Utilidad bajo Names in Workbook: Haga clic en OK

36


Junio de 2003

Presione la tecla Alt+F11 Seleccione Insert>Module Seleecione Insert>Procedure Teclee Comision bajo Name: Seleccione Function bajo Type Haga clic en OK Haga clic en () en Public Function Comision () Teclee Utilidad y presione la flecha hacia abajo Teclee Comision=Utilidad*0.01 Seleccione View>Microsoft Excel Haga clic en la celda E18 Teclee =Comision(Utilidad) y presione Enter Seleccione nuevamente la celda E18 Haga clic en el boton Currency Style ($) Guarde su trabajo.

Utilizar Message Box

En muchas ocasiones, desea empezar la Macro con algun mensaje de Bienvenida, alguna instruccion, advertencia o simplemente haciendo una pregunta Si/No. En Visual Basic es muy sencillo realizar esta tarea y nos ayuda mucho al momento de darle un poco de presentacion amigable a nuestro reporte.

La sintaxis que usaremos en este curso es la siguiente:

MsgBox (mensaje [,tipo de boton][,titulo])

El mensaje, es lo que usted quiere que la ventana diga al momento de aparecer. El tipo de boton indica, como su nombre lo dice, el tipo de boton que se usara para esta occasion (El boton con un boton de OK es el que esta por default) y el titulo, es el titulo que tendra la ventana al momento de aparecer. Dentro de tipo de boton, encontramos una gran variedad, en la siguiente tabla los tenemos todos:

Tipo de Boton Valor DescripcionVbOKOnly 0 Despliega solo el boton OK

VbOKCancel 1 Despliega boton de OK y CancelVbAbortRetryIgnore 2 Despliega boton Abort, Retry & Ignore

VbYesNoCancel 3 Despliega boton Yes, No & CancelVbYesNo 4 Despliega boton Yes & No

VbRetryCancel 5 Despliega boton Retry & CancelVbCritical 16 Despliega icono de Critical Message

VbQuestion 32 Despliega icono de Warning QueryVbexclamation 48 Despliega icono de Warning MessageVbInformation 64 Despliega icono de Information Message

Estos botones generan valores constantes, los cuales son:

37


Junio de 2003

Boton Constante DescripcionVbOK 1 OK

VbCancel 2 CancelVbAbort 3 AbortVbRetry 4 RetryVbIgnore 5 Ignore

VbYes 6 YesVbNo 7 No

EJERCICIO #29: Utilizar MsgBox

Confirme que EditMacro.xls este abierto. Presione las teclas Alt+F11( para ir a Visual Basic) Haga clic antes de Cells.Select Presione la tecla Enter y luego flecha hacia arriba Teclee msg1=msgbox(“Bienvenidos a esta Macro,¿Deseas correr

esta Macro?”,vbyesno,”Ventana de Mensaje”) y luego flecha hacia abajo.

Presione las teclas Alt+F11 (para volver a Microsoft Excel) Corra la Macro y haga clic en Yes. Vuelva a correr la Macro y haga clic en No. Guarde su trabajo, lo usara en el siguiente ejercicio.

Controlar el Flujo del Procedimiento

Los comandos del control de flujo dirigen el comporatamiento de las funciones definidas por el usuario. Instruyen a la funcion para que se detengan, , para que reporte los errores con los que se encontro, o para que tome desiciones basandose en ciertos criterios.

Este proceso de toma de decisiones se puede llevar a cabo a traves del uso de pruebas logicas. L os argumentos IF, Then y Else en el procedimiento controlan las acciones basandose en las condiciones con las que se encontro. Se ejecuta cierta linea del codigo dependiendo del resultado que encontro. Por ejemplo tal vez quiera calcular diferentes descuentos para venta de mayoreo o para clientes en general, con cada tipo de cliente representando por un codigo, en especial. Dependiendo en el codigo que ingreso, la funcion decide que descuento debe utilizar para calcular el resultado.

La sintaxis de este proceso de decision se divide en tres partes, Esta la prueba:Que codigo de cliente se ingresa. Si la funcion definida por el usuario encuentra

un codigo, ejecuta una serie de instrucciones. Si encuentra un codigo diferente, ejecuta una serie de instrucciones alternativa.

EJERCICIO #30: Controlar el flujo del Procedimiento.

38


Junio de 2003

Confirme que EditMacro.xls este abierto. Presione las teclas Alt+F11( para ir a Visual Basic) Haga clic antes de Cells.Select Presione la tecla Enter y luego flecha hacia arriba Teclee if msg1=7 then y presione Enter Teclee <Tab> End y presione Enter Teclee end if y presione flecha hacia abajo. Presione las teclas Alt+F11 (para volver a Microsoft Excel) Corra la Macro Haga clic en Yes Vuelva a correr la Macro Haga clic en No Guarde su trabajo.

39

manual del usuario - curso de excel uson

Documents