COMPUTACIN BSICA PARA EL REA DE CIENCIAS ECONMICAS WP 4 DE EXCEL

Funciones BUSCAR, BUSCARV y BUSCARH Funcin BUSCARACTIVIDAD: Nuestra empresa, dedicada la distribucin y venta de bebidas refrescantes, ha decidido (como mtodo de promocin y va de investigacin de mercado) premiar a aquellos consumidores que enven las etiquetas de los refrescos de dos litros a un determinado apartado de correos. Abre un nuevo Libro de Excel y llmalo Premios. La tabla de correspondencia de premios, que copiars en el rango A9:B13 de la Hoja 1, es la siguiente: N de puntos 500 1000 2000 4000 Premio Una camiseta y una bolsa deportiva Un walkman con auriculares Una torre de msica Un computador

Al cabo de un mes se elabora la lista de los primeros ganadores, incluyendo los puntos obtenidos por cada uno y el premio que les corresponde. Esta lista, antes de introducir los premios conseguidos por los ganadores, presenta la siguiente apariencia: Ganador Antonio Buesa Fernndez Catalina Lago Herrera Roberto Surez Vega Luis Ferrer Mas Ana Snchez Torres Jos Alonso Parra Oliver N de puntos 600 1200 900 2100 500 4050 Premio

Se trata de confeccionar dicha lista, en el rango A1:C7 de la Hoja 1, de modo que el premio conseguido por cada ganador aparezca automticamente en la tercera columna slo con introducir el n de puntos obtenido. PROCEDIMIENTO: Para esto ser necesario recurrir a la funcin BUSCAR. Esta funcin busca la correspondencia con el valor de una tabla en otra tabla distinta. Es til siempre que en la segunda tabla slo haya una correspondencia para cada valor; en nuestro caso, a cada n de puntos corresponde un solo premio. Una vez copiadas las tablas indicadas ms arriba, sitate en la celda C2. Activa el asistente para funciones y selecciona, en Categoras de funciones, Bsqueda y referencia, y en Nombre de la funcin, la funcin BUSCAR. En el cuadro de dilogo "Seleccionar argumentos" selecciona los argumentos "valor_buscado;matriz". En el argumento "valor_buscado", selecciona la celda B2 (que contiene el n de puntos obtenido por el ganador). En el argumento "matriz", selecciona el rango de celdas A10:B13 (donde se establecen las correspondencias de n de puntos con premios. Pulsa INTRO y en la celda C2 aparecer el premio correspondiente. Cuando la funcin no encuentra en la matriz seleccionada ningn valor coincidente con el que hemos introducido, selecciona el que ms se le aproxima por abajo (p.ej, considerar que lo ms aproximado a 900 es 500). Para poder copiar esta frmula a las celdas C3 a C7 es necesario convertir la referencia a la matriz en una referencia absoluta; por tanto, debers modificar la frmula para que quede as: =BUSCAR(C2;$A$10:$B$13). Tambin funcionara con referencias mixtas: =BUSCAR(C2;A$10:B$13). Una vez modificada la frmula, cpiala a las celdas C3 a C7. JGuevara

1

Ahora, introduce los datos "nombre y apellidos" y "n de puntos" en el rango A2:B7 y observa cmo se introducen automticamente los premios correspondientes.

Funciones BUSCARV y BUSCARHEstas funciones son necesarias en aquellos casos en que la matriz en la que realizamos la bsqueda tiene ms de 2 columnas (o filas). En tales casos, se ha de indicar en qu columna (BUSCARV) o fila (BUSCARH) se ha de buscar la correspondencia que queremos1. Funcin BUSCARV Supongamos que en el ejercicio anterior, en la tabla de correspondencias se incluyen los datos relativos a tres promociones diferentes:

N de puntos 500 1000 2000 4000

Premios prom. 1 Una camiseta y una bolsa deportiva Un walkman con auriculares Una torre de msica Un computador

Premios prom. 2 Una entrada para el cine Una entrada para el teatro Una entrada para el ftbol Una entrada para la pera

Premios prom. 3 Una suscripcin a la revista "Pronto" El libro "Mil recetas de cocina" Una vajilla completa Un viaje a Pars para dos personas

Aprovechando los nombres de antes y el n de puntos, supondremos que, en lugar de participar en la promocin 1 lo han hecho en la promocin 2. Cambia a la Hoja 2 del Libro activo, haciendo clic sobre la pestaa correspondiente a la Hoja 2. Los datos se dispondrn del mismo modo que en el ejercicio anterior.: por tanto, copia el contenido del rango A1:C7 de la Hoja 1 en el mismo rango de celdas de la Hoja 2. Haz lo mismo con el rango A9:B13. Luego, cambia (en la Hoja 2) esta ltima tabla hasta que tenga el aspecto de la tabla con las tres promociones. Sitate en la celda C2 y activa el asistente para funciones. En Categoras de funciones, selecciona Bsqueda y referencia. En Nombre de la funcin, selecciona BUSCARV En el argumento Valor_buscado, selecciona la celda B2. En el argumento Matriz_buscar_en, selecciona el rango A10:D13 En el argumento Indicador_columnas, escribe 3 (es decir, la tercera columna de la matriz) En el argumento Ordenado, no es necesario que introduzcas nada Pulsa INTRO. Una vez ms, para poder copiar la frmula a las celdas contiguas ser necesario convertir la referencia a la matriz en una referencia absoluta (o mixta) del modo ya visto antes. Funcin BUSCARH Funciona del mismo modo y en los mismos casos que BUSCARV. La diferencia radica en que BUSCARH se utiliza cuando los datos de la matriz estn dispuestos de forma horizontal.

EJEMPLO:

1 No obstante, dado que estas funciones tambin pueden ser utilizadas en los casos en que la matriz de bsqueda slo tiene 2 columnas (o filas), a partir de ahora prescindiremos de la funcin BUSCAR y nos centraremos en BUSCARV (principalmente) y en BUSCARH

2

COMPUTACIN BSICA PARA EL REA DE CIENCIAS ECONMICAS WP 4 DE EXCELCopia la tabla de correspondencias situada en el rango A10:D13 de la Hoja 2 a la Hoja 3, de forma que los datos se dispongan en horizontal y no en vertical. Para ello, sigue los siguientes pasos: Selecciona el rango A10:D13 de la Hoja 2 y pulsa el botn Copiar Cambia a la Hoja 3 haciendo clic en su pestaa. Sitate en la celda A10. Selecciona Edicin del men principal; elige la opcin Pegado especial En el cuadro de dilogo que aparece, activa la casilla de verificacin Trasponer. Luego pulsa Aceptar. Pulsa la tecla Esc para que desaparezca el borde intermitente alrededor de las celdas copiadas.

En el rango de celdas A1:C7 de la Hoja 3, copia la tabla situada en estas mismas celdas de la Hoja 2. Sitate en la celda C2 y activa el asistente para funciones; selecciona la funcin BUSCARH. A continuacin, opera de la misma forma que con BUSCARV salvo en cuanto a la matriz a seleccionar, que ser A11:D14. El argumento Ordenado en la funcin BUSCARV Como ya se ha visto, el cuarto argumento de la funcin BUSCARV es el de Ordenado. En este argumento no es necesario introducir nada siempre que la tabla de correspondencias en la que se realiza la bsqueda est ordenada en sentido ascendente (en funcin del valor de la primera columna; p.ej, en la tabla de correspondencias de premios en la actividad anterior). Hay casos, sin embargo, en que la tabla en la que se realizar la bsqueda no est ordenada de esta forma. En tales casos, es necesario introducir como cuarto argumento de la funcin la palabra FALSO (con lo que se indica al programa que la tabla en cuestin no est ordenada). EJEMPLO: Inserta una nueva hoja (Hoja 4) en el Libro Premios. Crea en ella el siguiente modelo de pedido (rango A1:D15): HERMANOS LPEZ C/ Romero, 90 Cochabamba PEDIDO N Cd. destinatario FECHA: Destinatario: CONDICIONES Forma envo Forma pago Cantidad Artculo Plazo entrega Lugar entrega Precio unit. Importe total

En la misma hoja, ms abajo, crea la siguiente tabla de correspondencias: Cdigo destinatario T32 Destinatario Talleres Forma envo Areo Forma pago Al contado JGuevara Plazo entrega 24 hs. Lugar entrega Fbrica

3

AK7 N12

Ramrez Mayoristas Centrales El dedal, SL

Camin Tren

Aplazado (30 d./vta.) Al contado

3 das 2 das

Almacn Almacn

A continuacin, en las celdas del modelo de pedido correspondientes a los datos de Destinatario, Forma envo, Forma pago, Plazo entrega y Lugar entrega introduce funciones BUSCARV de forma que al escribir el cdigo del destinatario aparezcan automticamente los datos correspondientes a dicho cdigo. En este caso, dado que la tabla de correspondencias no est ordenada, debers hacer uso del 4 argumento de la funcin, tal como se ha explicado ms arriba.

Funcin BUSCARV: repasoConfecciona en Excel un registro de compras que incluya los siguientes datos: Fecha de la compra Producto comprado Referencia del producto Cantidad comprada Precio unitario Importe de la compra sin IVA Importe del IVA pagado Importe de la compra con IVA

Los datos Producto comprado, Precio unitario y Tipo de IVA aplicable se obtendrn de una tabla adicional que incluir dichos datos en funcin de la referencia de cada producto. La tabla adicional es la siguiente: Referencia A-43 G-01 B-21 Z-36 D-42 Producto PRODUCTO A PRODUCTO B PRODUCTO C PRODUCTO D PRODUCTO E Precio unitario 5 Bs 2,5 Bs 9 Bs 12 Bs 3 Bs Tipo de IVA 16% 4% 4% 7% 16%

Las compras a registrar son las siguientes: 1 de marzo: 40 unidades de la referencia B-21 3 de marzo: 38 unidades de la referencia D-42 y 80 de la Z-36 12 de marzo: 60 unidades de la referencia G-01 15 de marzo: 20 unidades de la referencia A-43 y 30 de la D-42

BUSCARV: REPASO

Versin simple La empresa concesionaria de automviles Punata Motor, S.L. desea disear un libro de trabajo en Excel, denominado AMOTOR.XLS, para llevar en l un registro diario de las ventas de automviles que permita, introduciendo slo el cdigo correspondiente a cada modelo vendido, obtener automticamente tanto el nombre del modelo como todos los datos que permitan calcular el precio de venta; es decir, los extras (aire acondicionado, ABS, direccin asistida, pintura metalizada, llantas de aleacin ligera) y el precio base. Tambin se incluir, lgicamente, en el registro (como ltima columna) el precio final del modelo. Para ello, en la Hoja1 (que llamars Registro ventas) se incluir la informacin relativa tanto a los precios de los diferentes extras como el precio base para cada modelo de coche:

4

Hoja "PRESUPUESTO" COMPUTACIN BSICA PARA EL REA DE CIENCIAS ECONMICAS WP 4 DE EXCELCdigo A B C D E F Modelo Mercedes A Ford A Mercedes B Renault A Ford B Renault B Aire acondic. 225000 125000 175000 300000 250000 260000 ABS 150000 125000 200000 170000 250000 150000 Direccin Pintura asistida metaliz. 130000 100000 150000 150000 75000 150000 35000 45000 40000 25000 15000 50000 Llantas aleacin ligera 20000 20000 10000 10000 15000 24000 Precio base 4500000 3000000 4000000 3750000 2200000 2500000

A continuacin de esta tabla se incluir otra igual pero con dos columnas ms: .- Una al principio para incluir la fecha de la venta .- Otra al final para calcular el precio de venta Las ventas a registrar son las siguientes: 25 de abril: un modelo E y uno C 26 de abril: un modelo A, otro D y otro F 27 de abril: un modelo E y uno B

Versin compleja (incluye funcin SI, combinada con la funcin BUSCARV) La misma empresa de antes desea disear un libro de trabajo en Excel, denominado AMOTOR.XLS, que facilite la elaboracin de presupuestos de ventas. En una hoja del libro, denominada EXTRAS, se encontrar la informacin relativa al precio base y a los precios de cada posible extra para cada modelo de coche (la misma tabla de arriba). En una segunda hoja, llamada PRESUPUESTO, se realizar la elaboracin del presupuesto tal como aparece a continuacin: Modelo Aire acondicionado ABS Direccin Asistida Pintura metalizada Llantas aleacin ligera C SI NO NO SI SI Suma Extras Precio total Mercedes B 175.000 Pts. 0 Pts. 0 Pts. 40.000 Pts. 10.000 Pts.

1.- En la celda B1 se introduce el cdigo del vehculo y en C1 deber aparecer automticamente el nombre del modelo. 2.- En la celda B2 se teclear SI en el caso de que se desee el extra del aire acondicionado y NO en caso contrario. En la celda C2 deber aparecer el precio del extra si se hubiera elegido y 0 Pts. en el supuesto de que se hubiera optado por no incluirlo. 3.- En las celdas del rango C3:C6, las frmulas son similares a la creada para C2, pero ahora para el resto de extras. 4.- En la celda C7 se calcula la suma de los precios de los extras. 5.- En C8 se calcula el precio total del vehculo. Comn a las dos versiones (grficos) A partir de los datos de la Hoja1 (tabla de correspondencias), confecciona los siguientes grficos:

Insertos en la misma hoja, dos grficos circulares mostrando cmo se distribuye el precio total de los dos modelos de Mercedes En una hoja nueva, un grfico de columnas comparando el Precio Base de los diferentes modelos. JGuevara

5

6