 |
FÓRMULAS |
EXCEL 2003 |
El presente curso de Excel II presupone tener los conocimientos básicos del curso de introducción Excel I, en el que se imparten los Conceptos introductorios, Datos, Formatos y Edición.
El presente curso consta de una profundización en Excel y del uso de las herramientas básicas que este programa nos ofrece: Fórmulas más importantes y Gráficos básicos. Habiendo cursos específicos de Fórmulas y de Gráficos complejos, en éste, se verán las más usadas de tipo matemático, financiero, texto y algunas otras. Se repasarán también –a lo largo del temario- algunos conceptos básicos del programa que, son importantes para el desarrollo de este curso.
Índice:
1.- Fórmulas y funciones I. Introducción.
1.1.- Introducción de fórmulas y funciones
1.2.- Operadores más utilizados
1.3.- Prioridad de los operadores
1.4.- Utilización del asistente
2.- Fórmulas y funciones II. Continuación.
2.1.- Argumentos de una función
2.2.- Expresiones como argumentos de una función
2.3.- Utilizar Funciones como argumentos de las Funciones
3.- Fórmulas y funciones III. Funciones más usadas.
3.1.- Funciones de fecha y hora
3.2.- Funciones de texto
3.3.- Funciones de búsqueda
3.4.- Funciones financieras
3.5.- Otras funciones
1.- Fórmulas y
funciones I. Introducción.
1.1.- Introducción de fórmulas y
funciones
Una función es una fórmula
predefinida por Excel o por el usuario que, opera con uno o más valores y
devuelve un resultado que aparecerá directamente en la celda o será utilizado
para calcular la fórmula que la contiene.
La sintaxis de cualquier función
es:
nombre_función(argumento1;argumento2;...;argumentoN)
Deben respetarse las siguientes
reglas:
o Si la función va
al comienzo de una fórmula debe empezar por el signo =
o Los argumentos o
valores de entrada van siempre entre paréntesis. No dejes espacios antes o
después de cada paréntesis.
o Los argumentos
pueden ser valores constantes (número o texto), fórmulas o funciones.
o Los argumentos
deben de separarse por un punto y coma ;
Ejemplo: =SUMA(A1:C8)
Tenemos la función SUMA() que
devuelve como resultado la suma de sus argumentos. El operador ":"
nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas
entre la celda A1 y la C8, así la función anterior sería equivalente a:
=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8
Las fórmulas pueden contener más
de una función, y pueden aparecer funciones anidadas dentro de la fórmula.
Ejemplo: =SUMA(A1:B4)/SUMA(C1:D4)
Existen muchos tipos de funciones
dependiendo del tipo de operación o cálculo que realizan. Así hay funciones
matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y
hora, lógicas, de base de datos, de búsqueda y referencia y de información.
Para introducir una fórmula debe
escribirse en una celda cualquiera tal cual introducimos cualquier texto,
precedida siempre del signo =.
1.2.-
Operadores más utilizados en las fórmulas o funciones
Excel permite que en una función tengamos varios operadores para tratar los datos. Los operadores son símbolos que identifica Excel con operaciones aritméticas y es el enlace entre 2 argumentos.
En la tabla podemos ver los
operadores más utilizados.
|
SIMBOLO DEL OPERADOR |
OPERACIÓN QUE REALIZA |
|
+ |
SUMA |
|
- |
RESTA |
|
* |
MULTIPLICA |
|
/ |
DIVIDE |
|
^ |
EXPONENCIACIÓN |
|
& |
UNIÓN / CONCATENAR |
|
= |
Comparación IGUAL QUE |
|
> |
Comparación MAYOR QUE |
|
< |
Comparación MENOR QUE |
|
>= |
Comparación MAYOR IGUAL QUE |
|
<= |
Comparación MENOR IGUAL QUE |
|
<> |
Comparación DISTINTO |
En una fórmula o función pueden
utilizarse tanto operadores como sea necesario teniendo en cuenta siempre que
los operadores hacen siempre referencia a dos argumentos. Pueden crearse
fórmulas verdaderamente complejas.
Veamos un ejemplo.
=((SUMA(A1:A7)*SUMA(B1:B7))(SUMA(C1:C7)*SUMA(D1:D7)))=(F8*SUMA(G1:G5))
1.3.- Prioridad de los
operadores
Hemos visto que una fórmula puede
ser muy compleja, utilizando multitud de operadores. Excel como cualquier
operador matemático tiene unas ciertas reglas para saber que operaciones debe
realizar primero para que el resultado obtenido sea el correcto.
En la siguiente tabla mostramos las precedencias establecidas por Excel.
|
SIMBOLO DEL OPERADOR |
OPERACIÓN QUE REALIZA |
PRECEDENCIA |
|
^ |
EXPONENCIACIÓN |
1 |
|
* |
MULTIPLICA |
2 |
|
/ |
DIVIDE |
2 |
|
+ |
SUMA |
3 |
|
- |
RESTA |
3 |
|
& |
UNIÓN / CONCATENAR |
4 |
|
= |
Comparación IGUAL QUE |
5 |
|
> |
Comparación MAYOR QUE |
5 |
|
< |
Comparación MENOR QUE |
5 |
|
>= |
Comparación MAYOR IGUAL QUE |
5 |
|
<= |
Comparación MENOR IGUAL QUE |
5 |
|
<> |
Comparación DISTINTO |
5 |
Además de esta tabla de
precedencias la precedencia máxima, es decir la operación que antes se evalúa,
es aquella que va entre paréntesis.
Veamos pues como resolvería la
formula que hemos visto como ejemplo:
Podemos ver que hay 10 operaciones
- 5 SUMAS
- 3 MULTIPLICACIONES
- 1 DIVISIÓN
- 1 COMPARACIÓN
Primero resolvería por separado las operaciones de SUMA, después realizaría las operaciones de MULTIPLICACIÓN, seguidamente realizaría la DIVISIÓN y por último la COMPARACIÓN.
1.4.- Utilización del asistente
Una función como cualquier dato se
puede escribir directamente en la celda si conocemos su sintaxis, pero
Excel2003 dispone de una ayuda o asistente para utilizarlas, así nos resultará
más fácil trabajar con ellas.
Si queremos introducir una función
en una celda:
Situarse en la celda donde
queremos introducir la función, desplegar el menú Insertar.
Elegir la opción Función...
O bien, hacer clic sobre el botón
de la barra de fórmulas.
Aparecerá el cuadro de diálogo
Insertar función de la derecha.
Excel 2003 nos permite buscar la función que necesitamos escribiendo una breve descripción de la función necesitada en el recuadro Buscar una función: y a continuación hacer clic sobre el botón “Ir”, de esta forma no es necesario conocer cada una de las funciones que incorpora Excel ya que el nos mostrará en el cuadro de lista Seleccionar una función: las funciones que tienen que ver con la descripción escrita.
Para que la lista de funciones no
sea tan extensa podemos seleccionar previamente una categoría del cuadro
combinado O seleccionar una categoría:, esto hará que en el cuadro de lista
sólo aparezcan las funciones de la categoría elegida y reduzca por lo tanto la
lista. Si no estamos muy seguros de la categoría podemos elegir Todas.
En el cuadro de lista Seleccionar
una función: hay que elegir la función que deseamos haciendo clic sobre ésta.
Observa como conforme
seleccionamos una función, en la parte inferior nos aparecen los distintos
argumentos y una breve descripción de ésta. También disponemos de un enlace
Ayuda sobre esta función para obtener una descripción más completa de dicha
función.
A final, hacer clic sobre el botón “Aceptar”.
2.- Fórmulas y
funciones II. Continuación.
2.1.- Argumentos de una función
Justo por debajo de la barra de fórmulas aparecerá el cuadro de diálogo Argumentos de función, donde nos pide introducir los argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro caso se eligió la función SUMA ().
En el recuadro Número1 hay que
indicar el primer argumento que generalmente será una celda o rango de celdas
tipo A1:B4 . Para ello, hacer clic sobre el botón 
para que el cuadro se haga más pequeño y
podamos ver toda la hoja de cálculo; a continuación seleccionar el rango de
celdas o la celda deseada como primer argumento (para seleccionar un rango de
celdas haz clic con el botón izquierdo del ratón sobre la primera celda del
rango y sin soltar el botón arrástralo hasta la última celda del rango) y
pulsar la tecla INTRO para volver al cuadro de diálogo.
En el recuadro Número2 habrá que
indicar cuál será el segundo argumento. Sólo en el caso de que existiera. Si
introducimos segundo argumento, aparecerá otro recuadro para el tercero, y así
sucesivamente. Cuando tengamos introducidos todos los argumentos, hacer clic sobre
el botón “Aceptar”.
Si por algún motivo insertáramos una fila en medio del rango de una función, Excel expande automáticamente el rango incluyendo así el valor de la celda en el rango. Por ejemplo: Si tenemos en la celda A5 la función =SUMA(A1:A4) e insertamos un fila en la posición 3 la fórmula se expandirá automáticamente cambiando a =SUMA(A1:A5) .
En la Barra de herramientas existe el botón Autosuma que nos permite realizar la función SUMA de forma más rápida.
En la Barra de herramientas existe
el botón Autosuma que nos permite realizar la función SUMA de forma más rápida.
Con este botón tenemos acceso también a otras funciones utilizando la flecha de
la derecha del botón. Al hacer clic sobre
ésta aparecerá la lista desplegable siguiente:
Y podremos utilizar otra función
que no sea la Suma, como puede ser Promedio (calcula la media aritmética),
Cuenta (cuenta valores), Máx (obtiene el valor máximo) o Mín (obtiene el valor
mínimo). Además de poder acceder al diálogo de funciones a través de Más Funciones...
2.2.- Expresiones como
argumentos de una función
Excel permite que en una función tengamos como argumentos expresiones, por ejemplo la suma de dos celdas (A1+A3). El orden de ejecución de la función será primero resolver las expresiones y después ejecutar la función sobre el resultado de las expresiones.
Por ejemplo, si tenemos la siguiente función =Suma((A1+A3);(A2-A4)) donde:
A1 vale 1
A2 vale 5
A3 vale 2
A4 vale 3
Excel resolverá primero las expresiones (A1+A3) y (A2-A4) por lo que obtendremos los valores 3 y 2 respectivamente, después realizará la suma obteniendo así 5 como resultado.
2.3.- Utilizar Funciones como
argumentos de las Funciones
Excel también permite que una
función se convierta en argumento de otra función, de esta forma podemos
realizar operaciones realmente complejas en una simple celda. Por ejemplo
=MAX(SUMA(A1:A4);B3) , esta fórmula consta de la combinación de dos funciones,
la suma y el valor máximo. Excel realizará primero la suma SUMA(A1:A4) y
después calculará el valor máximo entre el resultado de la suma y la celda B3.
3.- Fórmulas y
funciones III. Funciones más usadas.
3.1.- Funciones de fecha y hora
De entre todo el conjunto de
funciones, en este apartado estudiaremos las funciones dedicadas al tratamiento
de fechas y horas.
Y estas son todas las posibles
funciones ofrecidas por Excel.
En varias funciones veremos que el
argumento que se le pasa o el valor que nos devuelve es un "número de
serie". Pues bien, Excel llama número de serie al número de días
transcurridos desde el 0 de enero de 1900 hasta la fecha introducida, es decir
coge la fecha inicial del sistema como el día 0/1/1900 y a partir de ahí
empieza a contar, en las funciones que tengan núm_de_serie como argumento,
podremos poner un número o bien la referencia de una celda que contenga una
fecha.
|
Función |
Descripción Ver detalle |
|
AHORA() |
Devuelve la
fecha y la hora actual |
|
AÑO(núm_de_serie)
|
Devuelve el
año en formato año |
|
DIA(núm_de_serie)
|
Devuelve el
día del mes |
|
DIAS360(fecha_inicial;fecha_final;método) |
Calcula el
número de días entre las dos fechas |
|
DIASEM(núm_de_serie;tipo)
|
Devuelve un
número del 1 al 7 |
|
FECHA(año;mes;día)
|
Devuelve la
fecha en formato fecha |
|
FECHANUMERO(texto_de_fecha)
|
Devuelve la
fecha en formato de fecha |
|
HORA(núm_de_serie)
|
Devuelve la
hora como un número del 0 al 23 |
|
HORANUMERO(texto_de_fecha)
|
Convierte una
hora de texto en un número |
|
HOY() |
Devuelve la
fecha actual |
|
MES(núm_de_serie)
|
Devuelve el
número del mes en el rango del 1 (enero) al 12 (diciembre) |
|
MINUTO(núm_de_serie)
|
Devuelve el
minuto en el rango de 0 a 59 |
|
NSHORA(hora;minuto;segundo)
|
Convierte
horas, minutos y segundos dados como números |
|
SEGUNDO(núm_de_serie)
|
Devuelve el
segundo en el rango de 0 a 59 |
3.2.- Funciones de texto
Excel también tiene un conjunto de
funciones específicas para la manipulación de texto.
Estas son todas las funciones de
texto ofrecidas por Excel.
|
Función |
Descripción Ver Detalle |
|
CARACTER(número)
|
Devuelve el
carácter específicado por el número |
|
CODIGO(texto) |
Devuelve el
código ASCII del primer caracter del texto |
|
CONCATENAR(texto1;texto2;...;textoN)
|
Devuelve una
cadena de caracteres con la unión |
|
DECIMAL(número;decimales;no_separar_millares)
|
Redondea un
número pasado como parámetro |
|
DERECHA(texto;núm_de_caracteres)
|
Devuelve el
número de caracteres especificados |
|
ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial)
|
Devuelve la
posición inicial del texto buscado |
|
ESPACIOS(texto)
|
Devuelve el
mismo texto pero sin espacios |
|
EXTRAE(texto;posicion_inicial;núm_caracteres)
|
Devuelve los
caracteres indicados de una cadena |
|
HALLAR(texto_buscado;dentro_del_texto;núm_inicial)
|
Encuentra una
cadena dentro de un texto |
|
IGUAL(texto1;texto2)
|
Devuelve un
valor lógico (verdadero/falso) |
|
IZQUIERDA(texto;núm_de_caracteres)
|
Devuelve el
número de caracteres especificados |
|
LARGO(texto) |
Devuelve la
longitud del texto |
|
LIMPIAR(texto)
|
Limpia el
texto de caracteres no imprimibles |
|
MAYUSC(texto) |
Convierte a
mayúsculas |
|
MINUSC(texto) |
Convierte a
minúsculas |
|
MONEDA(número;núm_de_decimales)
|
Convierte a
moneda |
|
NOMPROPIO(texto)
|
Convierte a
mayúscula la primera letra del texto |
|
REEMPLAZAR(texto_original;num_inicial;núm_de_caracteres;texto_nuevo)
|
Reemplaza
parte de una cadena de texto por otra |
|
REPETIR(texto;núm_de_veces)
|
Repite el
texto |
|
SUSTITUIR(texto;texto_original;texto_nuevo;núm_de_ocurrencia)
|
Reemplaza el
texto con texto nuevo |
|
T(valor) |
Comprueba que
el valor es texto |
|
TEXTO(valor;formato)
|
Convierte un
valor a texto |
|
VALOR(texto) |
Convierte un
texto a número |
3.3.- Funciones de búsqueda
Comprendamos qué es en sí una
búsqueda, cuando queremos encontrar alguna información de algo no buscamos
directamente por lo que buscamos pues lo desconocemos, realizamos una búsqueda
de una propiedad o algo similar que conocemos que puede tener lo que buscamos.
Por ejemplo, si buscamos a una persona, describimos su aspecto físico, si
buscamos el nº de teléfono de un restaurante, buscamos en la guía de teléfonos
por el nombre del restaurante. Normalmente el dato que queremos encontrar no lo
conocemos por eso buscamos por otros datos que sí conocemos.
Estas son las funciones
disponibles por Excel para realizar búsquedas:
|
Función |
Descripción Ver Detalle |
|
AREAS(ref) |
Devuelve el
número de rangos de celdas contiguas |
|
BUSCAR(...) |
Busca valores
de un rango de una columna o una fila |
|
BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;ordenado)
|
Busca en la
primera fila de la tabla o matriz de valores |
|
BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)
|
Busca un valor
en la primera columna de la izquierda |
|
COINCIDIR(valor_buscado;matriz_buscar_en;tipo_de_coincidencia)
|
Devuelve la
posición relativa de un elemento |
|
COLUMNA(ref) |
Devuelve el
número de columna de una referencia |
|
COLUMNAS(matriz)
|
Devuelve el
número de columnas que componen la matriz |
|
DESREF(ref;filas;columnas;alto;ancho)
|
Devuelve una
referencia a un rango |
|
DIRECCION(fila;columna;abs;a1;hoja)
|
Crea una
referencia de celda en forma de texto |
|
ELEGIR(num_indice;valor1;valor2;...)
|
Elige un valor
o una accion de una lista de valores |
|
FILA(ref) |
Devuelve el
número de fila |
|
FILAS(matriz) |
Devuelve el
número de filas |
|
HIPERvínculo(ubicación_del_vínculo;nombre_descriptivo)
|
Crea un acceso
directo a un documento |
|
IMPORTARDATOSDINAMICOS(camp_datos;tablas_dinámicas;campo1;elemento1;campo2;elemento2...)
|
Extrae datos
almacenados en una tabla dinámica |
|
INDICE(matriz;num_fila;num_columna)
|
Devuelve el
valor de una celda en la intersección de una fila y una columna |
|
INDIRECTO(ref;a1)
|
Devuelve una
referencia especificada |
|
TRANSPONER(matriz)
|
Intercambia
las filas por las columnas en una matriz |
3.4.- Funciones financieras
Excel es una de las herramientas
más potentes para trabajar con información y cálculos financieros.
Todas estas funciones están
agrupadas en la categoría de Financieras.
|
Función |
Descripción y Ejemplo Ver Detalle |
|
DB(costo;valor_residual;vida;periodo;mes)
|
Devuelve la
depreciación de un bien para un período especificado, método de depreciación
de saldo fijo |
|
DDB(costo;valor_residual;vida;periodo;factor)
|
Devuelve la
depreciación de un bien para un período especificado, mediante el método de
depreciación por doble disminución de saldo |
|
DVS(costo;valor_residual;vida;periodo_inicial;periodo_final;factor;sin_cambios)
|
Devuelve la
depreciación de un bien para un período especificado, incluyendo periodos
parciales |
|
INT.PAGO.DIR(tasa;periodo;nper;va)
|
Calcula el
interés pagado durante un período específico de una inversión |
|
NPER(tasa;pago;va;vf;tipo)
|
Devuelve el
número de pagos de una inversión |
|
PAGO(tasa;nper;va;vf;tipo)
|
Devuelve el
pago de un préstamo basado en pagos y tasas de interés constantes |
|
PAGOINT(tasa;periodo;nper;va;vf;tipo)
|
Devuelve el
interés pagado por una inversión durante periodo determinado |
|
PAGOPRIN(tasa;periodo;nper;va;vf;tipo)
|
Devuelve el
pago de un capital de una inversión determinada |
|
SLN(costo;valor_residual;vida)
|
Devuelve la
depreciación por método directo de un bien durante un período dado |
|
SYD(costo;valor_residual;vida;periodo)
|
Devuelve la depreciación
por método de anualidades de un bien durante un período específico |
|
TASA(nper;pago;va;vf;tipo;estimar)
|
Devuelve la
tasa de interés por periodo de un préstamo o una inversión |
|
TIR(valores;estimar)
|
Devuelve la
tasa interna de retorno de una inversión para una serie de valores en
efectivo |
|
TIRM(valores;tasa_financiamiento;tasa_reinversión)
|
Devuelve la
tasa interna de retorno modificada |
|
VA(tasa;nper;pago;vf;tipo)
|
Devuelve el
valor actual de una inversión |
|
VF(tasa;nper;pago;vf;tipo)
|
Devuelve el
valor futuro de una inversión basada en pagos periódicos y constantes más una
tasa de interés constante |
|
VNA(tasa;valor1;valor2;...)
|
Devuelve el
valor neto actual de una inversión a partir de una tasa de descuentos y una
serie de pagos futuros |
3.5.- Otras funciones
Además de las funciones
anteriormente mencionadas, existe un gran abanico de funciones de diferentes
categorías que nos pueden ser de gran utilidad.
|
Función |
Descripción Ver Detalle |
|
Funciones matemáticas y trigonométricas |
|
|
ABS(número) |
Devuelve el
valor absoluto de un número |
|
ALEATORIO() |
Devuelve un
número entre 0 y 1 |
|
COMBINAT(número;tamaño)
|
Devuelve el
número de combinaciones para un número determinado de elementos |
|
COS(número) |
Devuelve el
coseno de un ángulo |
|
ENTERO(número)
|
Redondea un
número hasta el entero inferior más próximo |
|
EXP(número) |
Realiza el
cálculo de elevar "e" a la potencia de un número determinado |
|
FACT(número) |
Devuelve el
factorial de un número |
|
NUMERO.ROMANO(número,forma)
|
Devuelve el
número pasado en formato decimal a número Romano |
|
PI() |
Devuelve el
valor de la constante pi |
|
POTENCIA(número;potencia)
|
Realiza el
cálculo de elevar un número a la potencia indicada |
|
PRODUCTO(número1;número2;...)
|
Devuelve el
resultado de realizar el producto de todos los números pasados como
argumentos |
|
RAIZ(número) |
Devuelve la
raiz cuadrada del número indicado |
|
RESIDUO(número;núm_divisor)
|
Devuelve el
resto de la división |
|
Funciones
estadísticas |
|
|
MEDIA.ARMO(número1;número2;...)
|
Devuelve la
media armónica de un conjunto de números positivos |
|
MAX(número1;número2;...)
|
Devuelve el
valor máximo de la lista de valores |
|
MIN(número1;número2;...)
|
Devuelve el
valor mínimo de la lista de valores |
|
MEDIANA(número1;número2;...)
|
Devuelve la
mediana de la lista de valores |
|
MODA(número1;número2;...)
|
Devuelve el
valor que más se repite en la lista de valores |
|
PROMEDIO(número1;número2;...)
|
Devuelve la
media aritmética de la lista de valores |
|
VAR(número1;número2;...)
|
Devuelve la
varianza de una lista de valores |
|
K.ESIMO.MAYOR(matriz;k)
|
Devuelve el
valor k-ésimo mayor de un conjunto de datos |
|
K.ESIMO.MENOR(matriz;k)
|
Devuelve el
valor k-ésimo menor de un conjunto de datos |
|
Funciones lógicas |
|
|
FALSO() |
Devuelve el
valor lógico Falso |
|
VERDADERO |
Devuelve el
valor lógico Verdadero |
|
SI(prueba_logica;valor_si_verdadero;valor_si_falso)
|
Devuelve un
valor u otro, según se cumpla o no una condición |
|
NO(valor_lógico)
|
Invierte el
valor lógico proporcionado |
|
Y(valor_logico1;valor_logico2;...)
|
Comprueba si
todos los valores son verdaderos |
|
O(valor_logico1;valor_logico2;...)
|
Comprueba si
algún valor lógico es verdadero y devuelve VERDADERO |
|
Función |
Descripción Ver Detalle |
|
Funciones de información |
|
|
ESBLANCO(valor)
|
Comprueba si
se refiere a una celda vacía |
|
ESERR(valor) |
Comprueba si
un valor es un error |
|
ESLOGICO(valor)
|
Comprueba si
un valor es lógico |
|
ESNOTEXTO(valor)
|
Comprueba si
un valor no es de tipo texto |
|
ESTEXTO(valor)
|
Comprueba si
un valor es de tipo texto |
|
ESNUMERO(valor)
|
Comprueba si
un valor es de tipo numérico |
|
TIPO(valor) |
Devuelve un
número que representa el tipo de datos del valor |