EJERCICIO 8 DE EXCEL

EXCEL EJERCICIO 8

Funciones BUSCAR, BUSCARV y BUSCARH

Estas funciones buscan en una tabla dada la correspondencia con un valor introducido por el usuario. P.ej, en un modelo de nómina podrían buscar el tipo de cotización que corresponde a un trabajador según su categoría profesional. El usuario sólo tendría que introducir la categoría profesional.

Función BUSCAR

ACTIVIDAD: En este modelo resumido de Libro de Facturas Emitidas, al introducir el código del cliente aparecerá su nombre en la columna contigua. Abre un nuevo libro de Excel y guárdalo con el nombre 8ex Búsqueda. En la hoja 1, que llamarás Facturas emitidas, crea, en el rango A2:E9, la siguiente tabla.

Fecha 11-03-2011 11-03-2011 12-03-2011 12-03-2011 12-03-2011 13-03-2011

Nº de factura 32 33 34 35 36 37

Código MIR ROD ARF ROD FOI VALL

Cliente Nombre

Total factura 334,12 € 245,24 € 1230 € 876,50 € 545,25 € 1150,30 €

En la misma hoja, en el rango H2:I12, introduce esta otra tabla (correspondencia código-cliente); la 2ª tabla ocupará una página distinta de la misma hoja; aprovecha los datos del archivo Listas ejercicio 8 contenida en la web del módulo:

1

EJERCICIO 8 DE EXCEL CÓDIGO

NOMBRE

ARF

ARFADELL, SLS

ARR

ARRIBAS, SLS

BIL

BILIASA, SLS

CAB

CABAÑAS, SAS

FOI

FOIXES, SLS

LOP

MAURICIO LOPEZ UTRILLAS

MIR

MARIA LLUISA MIRALLES ROIG

PEÑ

PEÑALBA DE SAN PEDRO, SAS

ROD

PEDRO RODRIGUEZ MARTINEZ

TEJ

RAMON TEJEIRA ROLO

VALL

VALLDEVID, SAS

PROCEDIMIENTO: La función BUSCAR es útil siempre que en la tabla de correspondencias sólo haya una correspondencia para cada valor; en nuestro caso, a cada código sólo corresponde un cliente. 1. Una vez copiadas las tablas indicadas más arriba, haz clic en la celda D4. 2. Activa el asistente para funciones

1. Elige la categoría Búsqueda y referencia. 2. Haz clic en BUSCAR y luego acepta.

2

EJERCICIO 8 DE EXCEL 3. En el cuadro de diálogo Seleccionar argumentos selecciona los argumentos valor_buscado;matriz. Acepta y, luego, configura los argumentos de la función como se indica abajo

C4 (que contiene el código del cliente)

H3:I13 (donde se establecen las correspondencias de códigos con clientes)

Pulsa INTRO (o clic en Aceptar) y en la celda D4 aparecerá el premio correspondiente. Para poder copiar esta fórmula a las celdas D5 a D9 es necesario convertir la referencia a la matriz en una referencia absoluta (dado que la búsqueda se ha de realizar siempre en la misma tabla); por tanto, deberás modificar la fórmula para que quede así: =BUSCAR(C4;$A$12:$B$22). También funcionaría con referencias mixtas: =BUSCAR(C4;A$12:B$22). Una vez modificada la fórmula, cópiala a las celdas D5 a D9. Observa cómo aparecen automáticamente los nombres de los clientes.

Funciones BUSCARV y BUSCARH

Estas funciones son necesarias en aquellos casos en que la matriz o tabla en que hacemos la búsqueda tiene más de 2 columnas (o filas). En tales casos, se ha de indicar en qué columna (BUSCARV: Buscar en Vertical) o fila (BUSCARH: Buscar en Horizontal) se ha de buscar la correspondencia que queremos 1. 1

No obstante, dado que estas funciones también pueden ser utilizadas en los casos en que la matriz de búsqueda sólo tiene 2 columnas (o filas), a partir de ahora prescindiremos de la función BUSCAR y nos centraremos en BUSCARV (principalmente) y en BUSCARH

3

EJERCICIO 8 DE EXCEL A continuación, se muestra una nueva versión (algo libre) del libro de facturas emitidas anterior, en el que aparecen dos datos más del cliente (forma de pago y nº de cuenta):

Fecha 11-03-2011 11-03-2011 12-03-2011 12-03-2011 12-03-2011 13-03-2011

Nº de factura Código 32 33 34 35 36 37

MIR ROD ARF ROD FOI VALL

Cliente Nombre

Forma de pago

Nº de cuenta

Total factura 334,12 € 245,24 € 1230 € 876,50 € 545,25 € 950,30 €

En la hoja 2 del libro 8ex Búsqueda crea dicha tabla en el rango A2:G9. Llama BUSCARV y BUSCARH a la hoja. Puedes copiar la de la hoja 1 y luego, modificarla (borrando el contenido de la columna Nombre. En la página siguiente se incluye la tabla de correspondencia con los datos dispuestos de dos maneras diferentes: en columnas y en filas (en la segunda, se ha reducido mucho el tamaño de la fuente por razón de espacio). Para el nombre y la forma de pago del cliente utilizarás la primera, con la función BUSCARV; para el nº de cuenta, la 2ª, con la función BUSCARH. En columnas (para la función BUSCARV). Crea esta tabla en la hoja 3 (rango A2:D13) y llama a la hoja Datos BUSCARV (aprovecha los datos del archivo Listas ejercicio 8 contenida en la web del módulo):

4

EJERCICIO 8 DE EXCEL CÓDIGO

NOMBRE

Forma de pago

MIR

MARIA LLUISA MIRALLES ROIG Aplazado 30 días

FOI

FOIXES, SL

Aplazado 30 días

LOP

MAURICIO LOPEZ UTRILLAS

Aplazado 30 días

ROD

PEDRO RODRIGUEZ MARTINEZ Aplazado 30 días

BIL

BILIASA, SL

Aplazado 30 días

TEJ

RAMON TEJEIRA ROLO

Aplazado 30 días

ARF

ARFADELL, SL

Contado

ARR

ARRIBAS, SL

Contado

CAB

CABAÑAS, SA

Contado

VALL

VALLDEVID, SA

Contado

PEÑ

PEÑALBA DE SAN PEDRO, SA

Contado

Nº de cuenta 0075-1600-44000000033 0075-1600-46000000531 1235-1000-43000000489 1235-1600-44000000028 1245-1000-48000000550 6485-1100-70000000668 0075-1000-45000000546 0420-2000-85000000052 1235-2000-85000000475 1245-2000-85000000066 9658-2000-87000000550

En filas (para la función BUSCARH). Crea esta tabla en la hoja 4 (A2:L5) y llama a la hoja Datos BUSCARH.

CÓDIGO

MIR

FOI

NOMBRE

MARIA LLUISA MIRALLES ROIG

FOIXES, SL

MAURICIO PEDRO LOPEZ RODRIGUEZ BILIASA, SL UTRILLAS MARTINEZ

Forma de pago

Aplazado 30 días

Aplazado 30 días

Aplazado 30 días

Nº de cuenta

LOP

ROD

Aplazado 30 días

BIL

Aplazado 30 días

TEJ RAMON TEJEIRA ROLO Aplazado 30 días

ARF

ARR

CAB

ARFADELL, ARRIBAS, SL SL

CABAÑAS, SA

Contado

Contado

Contado

VALL

PEÑ

PEÑALBA DE VALLDEVID, SAN PEDRO, SA SA

Contado

Contado

0075-1600- 0075-1600- 1235-1000- 1235-1600- 1245-1000- 6485-1100- 0075-1000- 0420-2000- 1235-2000- 1245-2000- 9658-20004446434448704585858587000000033 000000531 000000489 000000028 000000550 000000668 000000546 000000052 000000475 000000066 000000550

Para copiar la tabla de la hoja 3 en la hoja 4: -

Selecciona la tabla de la hoja 3 y haz clic en Copiar Ve a la hoja 4 y selecciona la celda inicial (donde deba empezar la tabla). Haz clic con el botón derecho y elige la opción Pegado especial. En el cuadro de diálogo, activa la casilla Trasponer y, luego, pulsa Aceptar.

5

EJERCICIO 8 DE EXCEL

ACTIVIDAD A REALIZAR Y PROCEDIMIENTO Se trata de que, al introducir el código del cliente, aparezcan automáticamente el nombre, la forma de pago y el nº de cuenta del mismo.

BUSCARV: 1. Si no lo habías hecho ya, borra el contenido del rango D4:D9 de la tabla de la hoja 2 BUSCARV y BUSCARH) 2. Sitúate en la celda D4 de dicha hoja y activa el asistente para funciones. 3. En el apartado Buscar una función escribe buscarv y haz clic en Ir. Con la función BUSCARV seleccionada, pulsa en Aceptar. Configura el cuadro de argumentos como sigue y luego acepta:

El valor buscado es el código del cliente, es decir C4.

Cuando queramos encontrar una coincidencia exacta (la mayoría de las veces), aquí hemos de escribir FALSO. Si buscamos una coincidencia sólo aproximada, lo dejamos en blanco o escribimos VERDADERO.

Ve a la hoja Datos BUSCARV y selecciona el rango que se indica; a continuación , pulsa F4 para introducir el signo dólar

El nombre que buscamos está en la columna nº 2 de la matriz seleccionada

Nota: en este caso hay una coincidencia exacta entre los códigos de una y otra tabla, por lo que, en el 4º argumento escribimos FALSO. Si, en cambio, por ejemplo, quisiéramos buscar un número con decimales en una columna de números enteros, sólo podríamos encontrar el valor más aproximado; en tal caso, dejaríamos el 4º argumento vacío o escribiríamos VERDADERO. 4. Arrastra la función de la celda D4 hasta la celda D9 5. Repite esta operación para obtener la forma de pago. Todo es igual salvo el indicador de columnas, que en este caso será 3 (la tercera columna de la tabla de correspondencias). 6

EJERCICIO 8 DE EXCEL

BUSCARH: La diferencia con BUSCARV radica en que BUSCARH se utiliza cuando los datos de la matriz (la tabla de correspondencias) están dispuestos en filas y no en columnas. El procedimiento es exactamente el mismo que en BUSCARV, solo que seleccionando, en este caso, la función BUSCARH. Por lo demás:  El valor buscado es el mismo (C3)  La matriz será en este caso el rango B2:L5 de la hoja 4 (Datos BUSCARH)  El argumento Indicador_columnas en este caso es Indicador_filas. En este caso, la fila en que buscamos es la 4 (la del nº de cuenta).  En Ordenado habrá que escribir FALSO (buscamos una coincidencia exacta).

Repaso de la función BUSCARV Las funciones BUSCARV y BUSCARH no se han de utilizar necesariamente en celdas contiguas. Son muy útiles para facilitar el llenado de ciertos impresos: facturas, albaranes, pedidos, etc.

ACTIVIDAD A REALIZAR Inserta una nueva hoja (Hoja 5) en el Libro Premios y llámala Pedido. Crea en ella el siguiente modelo de pedido (rango A2:D18):

7

EJERCICIO 8 DE EXCEL

ESTIL GARDEN Camí de Gràcia, s.n. 07620 LLUCMAJOR PEDIDO Nº

FECHA:

Cód. destinatario

Destinatario: CONDICIONES

Forma envío Forma pago Cantidad

Plazo entrega Lugar entrega Artículo

Precio unit.

Importe total

En la misma hoja, a la derecha, en otra página (en I2:N6), crea la siguiente tabla de correspondencias (los datos están en el libro Listas del ejercicio 8 contenida en la web del módulo): Código destinatario

ARF ARR CAB TEJ

Destinatario

ARFADELL, SL ARRIBAS, SL CABAÑAS, SA RAMÓN TEJEIRA ROLO

Forma envío

Camión Tren Aéreo Furgoneta

Forma pago

Plazo entrega

Lugar entrega

Contado Contado Contado Aplazado 30 días

3 días 2 días 24 hs.

Almacén Tienda Almacén

2 días

Tienda

A continuación, en las celdas del modelo de pedido correspondientes a los datos de Destinatario, Forma envío, Forma pago, Plazo entrega y Lugar entrega introduce funciones BUSCARV de forma que al escribir el código del destinatario aparezcan automáticamente los datos correspondientes a dicho código. Para probarlo, introduce algún código de cliente en el pedido.

8