DEPARTAMENTO DE LENGUAJES Y SISTEMAS INFORMÁTICOS ESCUELA SUPERIOR DE TECNOLOGÍA Y CIENCIAS EXPERIMENTALES

A.D.E.M. Segundo Curso

A25. Informática aplicada a la gestión Curso 2005/2006

Excel Tema 5. Funciones básicas II.

Excel 97

Universitat Jaume I

TABLA DE CONTENIDOS 5.1

Objetivos de esta práctica ........................................................................................3

5.2

Tipos de funciones ...................................................................................................3 5.2.1 Funciones de información............................................................................3 5.2.2 Funciones financieras...................................................................................4 5.2.3 Funciones de búsqueda y referencia ............................................................5 5.2.3.1

Buscarv ........................................................................................5

5.2.3.2

Buscarh ........................................................................................7

5.2.3.3

Coincidir ......................................................................................7

5.2.3.4

Indice ...........................................................................................8

5.2.3.5

Coincidir e Indice anidadas .........................................................9

Tema 5. Funciones básicas II

Página 2

Excel 97

Universitat Jaume I

Tema 5. Funciones básicas II 5.1 Objetivos de esta práctica. • •

Conocer la sintaxis de algunas funciones más específicas de Excel Conocer el comportamiento de estas funciones y emplearlas adecuadamente.

5.2 Tipos de funciones Concretamente, en la presente práctica veremos algunas de las funciones pertenecientes a las siguientes categorías: • • •

Funciones de información Funciones financieras Funciones de búsqueda y referencia

Como en la Práctica 4, para la descripción de las funciones destacaremos sus nombres con MAYÚSCULA Y NEGRITA y sus argumentos con negrita, excepto en el caso de que sean opcionales, que los mostraremos en cursiva.

5.2.1 Funciones de información Las funciones de información devuelven un valor lógico (verdadero o falso) con el que responden a una pregunta sobre una característica de su argumento. Pueden ser bastante útiles si se combinan con la función SI para construir condiciones. A continuación mostramos algunas de ellas:

FUNCIÓN

RESULTADO (DESCRIPCIÓN)

ESBLANCO(valor)

VERDADERO si el valor se refiere a una celda vacía

ESERROR(valor)

VERDADERO si el valor es cualquier valor de error VERDADERO si el valor es cualquier valor de error excepto

ESERR(valor)

#N/A

ESLOGICO(valor)

VERDADERO si el valor es un valor lógico

ESNOD(valor)

VERDADERO si el valor es un valor de error #N/A

ESNOTEXTO(valor)

VERDADERO si el valor no es texto

ESNUMERO(valor)

VERDADERO si el valor es numérico

ESREF(valor)

VERDADERO si el valor es una referencia

ESTEXTO(valor)

VERDADERO si el valor es texto

Tema 5. Funciones básicas II

Página 3

Excel 97

Universitat Jaume I

Ejercicio 5.1: Supongamos que quieres hallar la media aritmética de los valores numéricos de las celdas A1:A5 de una hoja de cálculo mediante el uso de la función PROMEDIO. Sin embargo, no quieres que devuelva por ejemplo el error #DIV/0 en caso de que las celdas del rango no contengan ningún número. Escribe la fórmula correspondiente que calcule la media y que, en el caso de no poder hacerlo, avise del problema mediante un mensaje de error “más amigable” (como, por ejemplo, “Se necesitan valores numéricos para calcular la

media.

5.2.2 Funciones financieras Estas funciones permiten realizar las operaciones clásicas de la matemática financiera, estudios contables, análisis económicos, etc. El grupo de funciones que veremos en esta categoría contiene argumentos comunes a todas ellas, su detalle es el siguiente: Tipo: es la modalidad de vencimiento de los pagos (0 al final del mes y 1 al principio).si se omite es 0. Tasa: es la tasa de interés por período. Nper: es el número total de pagos dentro de una anualidad. Pago: es el pago de cada período, (se compone de amortización de capital y amortización de intereses. Va: es el valor actual del préstamo o el total de pagos a realizar para amortizarlo. Vf: es el valor futuro que deseamos obtener luego de una serie de pagos. El grupo mencionado está compuesto de las siguientes funciones: FUNCIÓN

RESULTADO (DESCRIPCIÓN)

NPER(tasa; pago; va; vf; tipo)

Total períodos con pagos e interés constantes

PAGO(tasa; nper; va; vf; tipo) Cuota de pago con pagos e interés constante TASA(nper; pago; va; vf; tipo) Tasa de interés para un período dado. VA(tasa; nper; pago; vf; tipo)

Valor actual de una inversión

VF(tasa; nper; pago; va; tipo)

Valor futuro de una inversión

Ejercicio 5.2: Copia en una hoja de cálculo la siguiente tabla para calcular la cuota a pagar correspondiente a un préstamo según los términos que se establecen, aplicando la función Pago con sus primeros tres argumentos. Analiza como trabaja la función. A 1

PRÉSTAMO

2

INTERÉS ANUAL

3

AÑOS DE DURACIÓN

4

CUOTA A PAGAR:

Tema 5. Funciones básicas II

B 120.000 14% 3 =PAGO(B2/12;B3*12;-B1)

Página 4

Excel 97

Universitat Jaume I

5.2.3 Funciones de búsqueda y referencia Las funciones de Búsqueda y referencia se utilizan para la recuperación de los datos que presentan una estructura tabular, es decir, dispuestos en filas y columnas. Algunas de estas funciones se pueden observar en la siguiente tabla: FUNCIÓN BUSCARV; BUSCARH;

RESULTADO (DESCRIPCION)

Busca en primera columna de una matriz, se mueve en la fila correspondiente y devuelve valor de celda. Busca en la fila superior de una matriz y devuelve el valor de la celda indicada.

BUSCAR;

Busca los valores en un vector o matriz.

COINCIDIR;

Devuelve la posición relativa de un elemento en una matriz.

COLUMNA;

Devuelve el número de columnas de una referencia.

ELEGIR;

Elige un valor en una lista de valores.

FILA;

Devuelve el número de la fila de una referencia.

HIPERVINCULO; INDICE; TRANSPONER;

Como un acceso directo abre un documento en un servidor de red, intranet o Internet. Utiliza un índice para elegir un valor a partir de una referencia o matriz. Devuelve la traspuesta de una matriz.

Dentro del grupo de funciones que pertenecen a esta categoría, consideraremos las funciones BUSCARV, BUSCARH, COINCIDIR E INDICE.

5.2.3.1 BUSCARV Esta función busca un valor específico en la columna más a la izquierda de una tabla o matriz de valores y devuelve el valor en la misma fila de una columna especificada en la tabla o matriz. SINTAXIS: BUSCARV(Valor_buscado; Matriz_buscar_en; Indicador_columnas; Ordenado)

Admite cuatro argumentos: Valor_buscado es el valor de búsqueda y puede ser un número, una referencia o una cadena de texto; Matriz_buscar_en es el rango de celdas donde se realiza la búsqueda; Indicador_columnas es el número de columna deMatriz_buscar_en desde la cual debe devolverse el valor coincidente y Ordenado es un valor lógico que controla el tipo de búsqueda que se realizará. Si el argumento Ordenado es VERDADERO (o se omite) la función, si no encuentra un valor exacto, devuelve un valor correspondiente al que más se aproxime por defecto su valor de búsqueda (el valor correspondiente al inmediatamente menor que Valor_buscado) Si Ordenado es FALSO, BUSCARV devuelve el valor correspondiente al buscado.

Tema 5. Funciones básicas II

Página 5

Excel 97

Universitat Jaume I

Nota: Hay que realizar algunas observaciones: • El texto escrito en mayúsculas y minúsculas es equivalente •

Si el argumento Ordenado es VERDADERO, los valores de la primera columna de la Matriz_buscar_en deben colocarse en orden ascendente, de lo contrario BUSCARV podría devolver un valor incorrecto.

•

Si Valor_buscado es menor que el menor valor de la primer columna, la función devuelve el error #N/A.

•

Si BUSCARV no encuentra el Valor_buscado y Ordenado es FALSO, devuelve el error #N/A

•

Si Indicador_columnas es menor que 1, BUSCARV devuelve el error #¡VALOR! y si es mayor que el número de columnas de matriz_buscar_en, devuelve el error #¡REF!

Ejercicio 5.3. Copia la tabla que se encuentra a continuación en una hoja nueva y utiliza esta función para averiguar la nota correspondiente a 7,5 con la siguiente llamada: BUSCARV(7,5;A1:B4;2). Escribe la función en la celda C1 de la hoja. Analiza la llamada a la función y el resultado obtenido. A

B

1

0

Suspendido

2

5

Aprobado

3

7

Notable

4

8,5

Sobresaliente

Ahora supongamos que la tabla tuviera el aspecto que se muestra a continuación, (constrúyela en otro rango de la hoja) veríamos cómo el valor devuelto al aplicar la función para 7,5 (en una celda a la derecha de esta tabla) se transforma en Aprobado. Analiza porqué devuelve este resultado. D

E

1

0

Suspendido

2

5

Aprobado

3

8,5

4

7

Sobresaliente Notable

Los ejemplos anteriores se refieren a búsquedas donde interesa la coincidencia exacta (o aproximada para valores intermedios de búsqueda) a los contenidos en la matriz comparación. Aquí hemos utilizado el argumento Ordenado con el valor VERDADERO (explícito u omitido).

Tema 5. Funciones básicas II

Página 6

Excel 97

Universitat Jaume I

Si en cambio, se desea buscar sólo una coincidencia exacta entre los valores de búsqueda y los de la matriz comparación (para obtener sus correspondientes resultados biunívocos), por ejemplo, DNI y los nombres de personas a que pertenecen, .se deberá utilizar el argumento Ordenado en VERDADERO con los valores de la primer columna de la matriz comparación ascendentes, o el argumento Ordenado en FALSO sin importar el orden de los valores de la primer columna. Ejercicio 5.4 Prueba este último caso, copiando la siguiente tabla y utilizando la función con una llamada conveniente para buscar el nombre de distintas personas por intermedio de sus respectivos DNI’s. Prueba con ordenamiento y sin ordenamiento y analiza resultados. DNI

NOMBRE

1

18.543.000

Juan García Giménez

2

21.987.345

Pablo López Caso

3

19.432.654

José Gutiérrez Huerta

4

20.987-123

Francisco Valle Porta

5

19.345.876

Alfonso Villar Gómez

6

15.500.600

Pablo Gómez Heredia

5.2.3.2 BUSCARH Esta función es completamente análoga a la anterior, con la salvedad de que opera en dirección horizontal, es decir, busca un valor en la fila superior de una tabla o una matriz de valores y devuelve un valor en la misma columna de una fila especificada en la tabla o en la matriz. SINTAXIS: BUSCARH(Valor_buscado; matriz_buscar_en; Indicador_filas; Ordenado)

El resto de consideraciones son similares a las vistas para la función BUSCARV. 5.2.3.3 COINCIDIR Esta función devuelve la posición relativa de un elemento en una matriz que coincida con un orden especificado. SINTÁXIS: COINCIDIR(Valor_buscado; Matriz_buscada; Tipo_de_coincidencia)

Admite tres argumentos: Valor_buscado es el valor que se utiliza para encontrar en la tabla el valor deseado, puede ser un número, un texto o una referencia; matriz_buscada es una matriz fila o columna formada por el rango de celdas donde buscar los valores; Tipo_de_coincidencia admite tres valores: 1, 0 ó -1.

Tema 5. Funciones básicas II

Página 7

Excel 97

Universitat Jaume I

1

Para buscar el mayor valor por defecto o el valor exacto (necesita los valores ascendentes)

0

El valor exacto

-1

El menor valor por exceso o el valor exacto, de la coincidencia (necesita los valores descendentes).

Nota: Cabe efectuar las siguientes observaciones: •

La función COINCIDIR no distingue entre mayúsculas y minúsculas cuando hace coincidir valores de texto.

•

Si COINCIDIR no puede encontrar una coincidencia, devuelve el valor de error #N/A.

Ejercicio 5.5. Copia las tablas que se encuentran a continuación y utiliza la función COINCIDIR para averiguar las posiciones relativas del valor “Oficial” en la tabla A1:A6 y del valor 63.000 en la tabla A1:F1, utilizando el tipo de coincidencia en 0.

A 1

Auxiliar

2

Comercial

3

Dibujante

4

Oficial

5

Secretario

6

Técnico

A 1

B

C

D

E

F

30.000 42.000 49.000 54.000 63.000 71.000

5.2.3.4 INDICE Esta función devuelve un valor o la referencia a un valor en una tabla o rango. La función INDICE() tiene dos formas, referencia y matricial. La forma que veremos es la matricial que devuelve el valor de un elemento en una tabla o matriz seleccionado por los índices de número de fila y de columna. SINTÁXIS FORMA MATRICIAL: INDICE(Matriz; Núm_fila; Núm_columna)

Esta función admite tres argumentos: Matriz es un rango de celdas o una matriz de constantes; Núm_fila selecciona en el rango matriz la fila desde la cual se devolverá un valor, si se omite se requiere el argumento Núm_columna; Núm_columna selecciona en el rango matriz la columna desde la cual se devolverá un valor, si se omite, se requiere el argumento Núm_fila.

Tema 5. Funciones básicas II

Página 8

Excel 97

Universitat Jaume I

Varias consideraciones sobre el uso correcto de los argumentos Núm_fila y Núm_columna: •

Si se utilizan ambos argumentos Núm_fila y Núm_columna, INDICE devuelve el valor en la celda de intersección de ambos argumentos.

•

·Si Matriz contiene sólo una fila o columna, el argumento Núm_fila o Núm_columna que corresponde es opcional.

•

Si Matriz tiene más de una fila y más de una columna y sólo utiliza Núm_fila o Núm_columna, INDICE devuelve una matriz con toda una fila o columna.

Nota: Los argumentos Núm_fila y Núm_columna deben indicar una celda contenida en matriz; de lo contrario, INDICE devuelve el valor de error #¡REF! Ejercicio 56. Copia la siguiente tabla y utiliza la función INDICE para obtener los valores de los elementos 2,3 y 3,1. A

B

C

1

Cemento

Grava

Arena

2

Hormigón

Asfalto

Aluminio

3

Madera

Hierro

Bronce

5.3.3.5 COINCIDIR E INDICE ANIDADAS Estas dos funciones se suelen anidar para obtener resultados específicos. Ejercicio 5.7. Copia la tabla que se encuentra a continuación y aplica las funciones COINCIDIR e INDICE en la siguiente estructura anidada para saber que empresa ofrece el mejor precio total: INDICE(B1:C4; 1; COINCIDIR(MIN(B4:C4); B4:C4; 0))

A 1

B

C

Empresa1

Empresa2

2

Producto1

2500,00

385,00

3

Producto2

750,00

2995,00

4

Totales

3250,00 €

3380,00 €

Tema 5. Funciones básicas II

Página 9