Fundamentos de Excel para Finanzas ( PDFDrive ) - fernando mena

Vista previa del material en texto

Fundamentos de Excel para Finanzas 
 
(Adaptado Excel 2016) 
Actualizado Septiembre 2018 
 
 
Alfonso Rodríguez Sandiás 
Grupo Valoración Financiera Aplicada 
www.usc.es/valfinap 
www.usc.es/modeleva 
Universidad de Santiago de Compostela 
Fundamentos de Excel para Finanzas 
2 
TABLA DE CONTENIDOS 
FUNDAMENTOS DE EXCEL PARA FINANZAS ........................................................................................... 1 
1. HOJA DE CÁLCULO, CONTENIDOS Y FORMATOS ....................................................................................... 7 
1.1. Introducción ........................................................................................................................ 7 
1.2. Textos, datos y fórmulas ..................................................................................................... 9 
1.3. Formato ............................................................................................................................. 11 
1.4. Validación.......................................................................................................................... 13 
1.5. Formato condicional ......................................................................................................... 17 
2. COPIADO Y PEGADO DE FÓRMULAS .................................................................................................... 21 
2.1. Copiado/pegado libre, sin bloqueos .................................................................................. 21 
2.2. Copiado/pegado con protección absoluta, bloqueo total ................................................. 22 
2.3. Copiado/pegado con protección relativa (bloqueando columna, pero no fila) ................. 23 
2.4. Copiado/pegado con protección relativa (bloqueando fila, pero no columna) ................. 24 
2.5. Un último ejemplo con un poco de todo ........................................................................... 24 
3. NOMBRES .................................................................................................................................... 25 
3.1. Nombre de celda ............................................................................................................... 25 
3.2. Nombre de rango .............................................................................................................. 26 
3.3. Crear nombres desde la selección ..................................................................................... 27 
3.4. Nombres en activo ............................................................................................................ 28 
4. FUNCIONES ARITMÉTICAS................................................................................................................. 29 
4.1. SUMA ................................................................................................................................ 29 
4.2. SUMA con rango semicerrado........................................................................................... 30 
4.3. CONTAR, CONTARA y CONTAR.BLANCO ........................................................................... 31 
4.4. SUMAR.SI y CONTAR.SI ..................................................................................................... 31 
4.5. SUMAPRODUCTO .............................................................................................................. 33 
4.6. SUMAR.SI.CONJUNTO ....................................................................................................... 33 
5. FUNCIONES ESTADÍSTICAS BÁSICAS ..................................................................................................... 34 
5.1. MAX, MIN y PROMEDIO .................................................................................................... 34 
5.2. Funciones de desviación típica y varianza ......................................................................... 34 
Fundamentos de Excel para Finanzas 
3 
5.3. Funciones de covarianza y correlación .............................................................................. 34 
5.4. JERARQUIA ........................................................................................................................ 35 
5.5. K.ESIMO.MAYOR ............................................................................................................... 35 
5.6. K.ESIMO.MENOR ............................................................................................................... 36 
5.7. PENDIENTE ........................................................................................................................ 36 
5.8. DISTR.NORM.N .................................................................................................................. 37 
5.9. INV.NORM ......................................................................................................................... 37 
5.10. PERCENTIL Y RANGO.PERCENTIL ..................................................................................... 38 
6. MISCELÁNEA ................................................................................................................................. 39 
6.1. TEXTO ................................................................................................................................ 39 
6.2. RAIZ ................................................................................................................................... 39 
6.3. ABS, ENTERO, REDONDEAR ............................................................................................... 39 
6.4. Funciones de números aleatorios ...................................................................................... 40 
6.5. Operaciones con fechas .................................................................................................... 41 
6.6. HOY y FIN.MES .................................................................................................................. 41 
6.7. AÑO, MES, DIA, DIASEM.................................................................................................... 42 
6.8. FECHA.MES ........................................................................................................................ 42 
7. FUNCIONES LÓGICAS ....................................................................................................................... 43 
7.1. La función SI para una condición simple ........................................................................... 43 
7.2. La función MAX como alternativa ..................................................................................... 44 
7.3. Uso de la función SI anidada ............................................................................................. 45 
7.4. Anidamiento múltiple de la función SI .............................................................................. 46 
7.5. Función Y ........................................................................................................................... 47 
7.6. Función O .......................................................................................................................... 47 
7.7. Función NO ........................................................................................................................ 48 
7.8. Función SI.ERROR .............................................................................................................. 48 
8. FUNCIONES DE BÚSQUEDA Y REFERENCIA ............................................................................................ 49 
8.1. ELEGIR ............................................................................................................................... 49 
8.2. La función DESREF ............................................................................................................. 50 
Fundamentos deExcel para Finanzas 
4 
8.3. Función DESREF al detalle ................................................................................................. 51 
8.4. La función COINCIDIR ........................................................................................................ 51 
8.5. La función BUSCARH y BUSCARV ....................................................................................... 53 
8.6. La función INDICE .............................................................................................................. 54 
9. FUNCIONES FINANCIERAS ................................................................................................................. 55 
9.1. La función INT.EFECTIVO ................................................................................................... 55 
9.2. La función TASA.NOMINAL................................................................................................ 55 
9.3. Funciones con anualidades: VA, PAGO, TASA, NPER ......................................................... 56 
9.4. Función VA añadiendo un pago final ................................................................................ 57 
9.5. Función VF añadiendo un pago inicial ............................................................................... 57 
9.6. Las funciones PAGO, PAGOPRIN y PAGOINT ..................................................................... 58 
9.7. VNA ................................................................................................................................... 59 
9.8. TIR ..................................................................................................................................... 59 
9.9. Las funciones VNA.NO.PER y TIR.NO.PER .......................................................................... 60 
9.10. La función TIRM .............................................................................................................. 60 
10. EJEMPLOS DE FUNCIONES ARRAY ..................................................................................................... 61 
11. USO DE LA HERRAMIENTA TABLA ................................................................................................... 63 
11.1. Tablas de un solo input, columna .................................................................................... 63 
11.2. Tablas de un solo input, fila ............................................................................................ 65 
11.3. Tablas de dos inputs, fila y columna ............................................................................... 66 
12. HERRAMIENTA BUSCAR OBJETIVO.................................................................................................... 67 
13. ESCENARIOS ................................................................................................................................ 69 
13.1. Uso del Administrador de Escenarios de Excel ................................................................ 69 
13.2. Elaboración de Escenarios. Un método alternativo ........................................................ 72 
14. SOLVER ...................................................................................................................................... 73 
15. USO DE REFERENCIAS CIRCULARES ................................................................................................... 76 
16. ELABORACIÓN DE GRÁFICOS ........................................................................................................... 78 
16.1. Gráfico de líneas básico .................................................................................................. 78 
16.2. Gráfico de columnas básico ............................................................................................ 82 
Fundamentos de Excel para Finanzas 
5 
16.3. Gráfico de columnas y líneas ........................................................................................... 83 
16.4. Gráfico de columnas y líneas de doble eje vertical.......................................................... 83 
16.5. Gráfico de áreas .............................................................................................................. 84 
16.6. Gráfico de barras............................................................................................................. 85 
16.7. Gráfico de barras tipo tornado........................................................................................ 85 
16.8. Gráfico de dispersión ....................................................................................................... 87 
16.9. Gráfico circular ................................................................................................................ 87 
16.10. Gráfico con rango dinámico .......................................................................................... 89 
17. UNA MACRO SENCILLA .................................................................................................................. 91 
18. FUNCIONES PERSONALIZADAS ......................................................................................................... 93 
18.1. Una función para calcular los impuestos ........................................................................ 93 
18.2. Función para indicar el nombre de la hoja ...................................................................... 96 
18.3. Funciones para mostrar la fórmula de una celda............................................................ 97 
18.4. Función para calcular el VAN .......................................................................................... 98 
18.5. Función para calcular el VAN con ajuste de medio año .................................................. 99 
18.6. Función para calcular el IRB e IRN ................................................................................... 99 
18.7. Funciones para calcular el VAN con tasa de descuento variable .................................. 100 
18.8. Función para calcular el VAN Modificado ..................................................................... 101 
18.9. Función para calcular la TIR No Periódica Modificada. ................................................. 102 
18.10. Función para calcular el valor de un bono con una tasa de interés para la valoración 
variable ........................................................................................................................................... 102 
18.11. Función para calcular el valor de una renta con crecimiento constante durante un 
periodo finito ................................................................................................................................... 103 
18.12. Función para calcular el valor de una renta con un crecimiento constante durante un 
periodo finito seguida de un crecimiento constante ilimitado ........................................................ 104 
18.13. Función para calcular el plazo de recuperación .......................................................... 104 
19. USO DE MACROS CON BUSCAR OBJETIVO Y SOLVER ............................................................................ 105 
19.1. Una macro sencilla para usar Buscar Objetivo ............................................................. 105 
19.2. Una macro para usar Buscar Objetivo con una tabla en columna ................................ 106 
19.3. Una macro para usar Buscar Objetivo con una tabla en fila......................................... 108 
Fundamentos de Excel para Finanzas 
6 
19.4. Una macro para usar Buscar Objetivo con una tabla de dos ejes ................................. 108 
19.5. Una macro para usar Solver .......................................................................................... 109 
20. USO DE LA AUDITORIA DE FÓRMULAS .............................................................................................111 
21. AGRUPAR, DIVIDIR, INMOVILIZAR PANELES, HIPERVÍNCULOS................................................................ 112 
21.1. Agrupar ......................................................................................................................... 112 
21.2. Dividir ............................................................................................................................ 115 
22.3. Inmovilizar paneles ....................................................................................................... 116 
22.4. Hipervínculos ................................................................................................................. 117 
22. IMPRIMIR ................................................................................................................................. 117 
22.1 Selección de datos a imprimir ........................................................................................ 117 
22.2 Encabezados y pie .......................................................................................................... 117 
22.3 Salto de página .............................................................................................................. 118 
23. RECOMENDACIONES BÁSICAS SOBRE LA ELABORACIÓN DE MODELOS FINANCIEROS EN EXCEL ..................... 119 
23.1. Número de hojas de un modelo .................................................................................... 120 
23.2. Alcance del modelo ....................................................................................................... 120 
23.3. Orientación vertical u horizontal de un modelo ............................................................ 120 
23.4. Orientación de izquierda a derecha y de arriba abajo .................................................. 120 
23.5. Acerca de inputs y outputs ............................................................................................ 121 
23.6. Las celdas de inputs deben contener datos, no fórmulas y las celdas de cálculo no deben 
contener datos, solo fórmulas......................................................................................................... 122 
23.7. Introduzca los datos una sola vez ................................................................................. 123 
23.8. Introduzca niveles en “segunda línea” .......................................................................... 123 
23.9. Creación de fórmulas y chequeo ................................................................................... 124 
23.10. Optimice el tiempo y aproveche su trabajo ................................................................ 125 
Fundamentos de Excel para Finanzas 
7 
 
El texto que desarrollamos a continuación no pretende ser una 
lección exhaustiva de Excel, ni mucho menos. Sólo nos hemos atrevido 
a describir las cuestiones esenciales que consideramos de gran utilidad 
para aquellas personas que deben recurrir al uso de hojas de cálculo 
con asiduidad. Hemos usado como programa de hoja de cálculo de 
referencia Excel, en su versión 2016, pero las cuestiones que 
desarrollamos son perfectamente extrapolables a otros programas 
similares. Seguro que a muchos usuarios algunas cuestiones les 
parecerán muy elementales y seguro que nos hemos dejado otras 
muchas en el tintero. 
En las diferentes figuras hemos tratado de mostrar las fórmulas 
incorporadas para que el lector pueda seguir mejor el desarrollo de los 
ejemplos. Para ello hemos usado dos funciones personalizadas, 
PONFORMULA y PONFORMULACELDA que nos indican el contenido de 
una celda. La primera nos indica simplemente el contenido de la celda, 
la segunda nos indica además la celda de que se trata. La versión de 
Excel de Office 2016 incorpora la función FORMULATEXTO, que realiza 
la misma tarea que nuestra función personalizada PONFORMULA. La 
siguiente figura muestra el uso de estas tres funciones. 
 
1. Hoja de cálculo, contenidos y formatos 
1.1. Introducción 
Excel cuenta con una serie de elementos comunes en su interfaz de 
usuario. Las opciones de los menús, así como los botones que existen 
en las herramientas, tienen un comportamiento dinámico. 
Cada vez que se crea un nuevo libro de Excel nos encontramos con 
una hoja de cálculo. Para añadir más hojas basta clicar en el botón “+” 
Fundamentos de Excel para Finanzas 
8 
situado a la derecha de la última hoja o bien usar el botón derecho del 
ratón estando situado encima de la etiqueta de alguna de las hojas. 
Para eliminar una hoja basta clicar en el botón derecho del ratón 
estando situado la etiqueta de la hoja que se desea eliminar y clicar 
“eliminar”. Es pertinente otorgar un nombre identificativo a cada hoja 
clicando en la etiqueta de la misma. Cada hoja puede contener datos 
distintos o también pueden estar relacionadas entre sí. 
Una gran parte de la ventana de trabajo está ocupada por las 
celdas, el área de la hoja de cálculo en la que podemos introducir 
textos, datos o fórmulas. Cada celda se identifica con una referencia 
única que indica en primer lugar la columna en la que se encuentra 
(con una letra) y en segundo lugar la fila en la que se encuentra con un 
número. Un grupo de celdas colindantes se denomina rango. El rango 
se identifica con la celda superior izquierda y la inferior derecha, 
separadas por “:”. Por ejemplo, el rango B2:D6 contendrá las celdas de 
la fila 2, desde B hasta D, y hacia abajo hasta la fila 6. 
 
Una vez creado o abierto un libro, comenzaremos a introducir 
datos en las celdas. Aplicaremos formato a esos datos, copiaremos, 
moveremos celdas, etc. Las flechas de desplazamiento del cursor nos 
permiten desplazarnos por la hoja. Como luego veremos, si le resulta 
más sencillo puede asignar nombres a las celdas y rangos para hacer 
referencia a ellas. 
Los menús son bastante intuitivos. En el menú Archivo podremos 
abrir un archivo, guardarlo, imprimirlo, así como cambiar algunas 
opciones básicas. Muchos usuarios nunca precisan cambiar dichas 
opciones. 
Fundamentos de Excel para Finanzas 
9 
En el menú Inicio se encuentran las opciones vinculadas a 
cuestiones de formato. Acudiremos al menú Insertar cuando queramos 
incluir un gráfico o algún otro objeto. El menú Fórmulas nos permite 
fundamentalmente la inclusión de funciones propias de Excel. El menú 
Datos nos da acceso a determinadas herramientas para el tratamiento 
de datos. En nuestro caso haremos uso de las opciones de Validación 
de Datos y de Análisis de hipótesis. En el menú revisar se nos permite 
realizar cuestiones como la protección del libro o la revisión ortográfica. 
En el menú Vista podremos configurar la forma en que se ven algunas 
características de la hoja de cálculo. Otros menús, por ejemplo 
Consulta y Power Pivot, no los utilizaremos, pues escapan al cariz 
introductorio de este texto. 
 
1.2. Textos, datos y fórmulas 
En una celda podremos incorporar básicamente tres cuestiones. 
Textos, datos y fórmulas. Usaremos el siguiente ejemplo para su 
descripción: 
 
Textos. Suele tratarse de información que se incorpora para 
identificar el contenido de celdas adyacentes. Por ejemplo, los textos 
Fundamentos de Excel para Finanzas 
10 
que hemos escrito en el rango B9:B15. Nos permite identificar el 
contenido de las celdas que se encuentran a su derecha. 
Datos. Son los inputs de nuestras operaciones. Habitualmente se 
tratará de cifras. En nuestro ejemplo las cifras de 20, 30 y 40 del rango 
C9:C11. 
Fórmulas. Comienzan con el signo “=”. Recogen las operaciones que 
queremos realizar. En el ejemplo mostramos las tres posibilidades 
básicas. En C12 hemos indicado que se sumen las celdas C9, C10 y 
C11, introduciendo la fórmula de forma directa, tal como la planteamos 
conceptualmente. En C13 realizamos la misma operación pero usando 
una función de Excel. Excel cuenta con numerosas funciones que 
ayudan a realizar unaserie de cálculos ya preestablecidos. En nuestro 
caso hemos recurrido a la función SUMA. Las funciones no tienen por 
qué introducirse de forma aislada. En la celda D15 incluimos la función 
PROMEDIO (que calcula la media de los datos que se le indiquen) 
dentro de nuestro cálculo del triple del promedio al cuadrado. 
Una gran parte del potencial de Excel proviene del uso de sus 
funciones. En este documento repasaremos algunas de ellas. Las 
funciones requieren ser invocadas por el nombre identificativo que tiene 
cada una de ellas y luego, entre paréntesis, es preciso indicar los 
argumentos de dicha función. En el caso de la función SUMA y de la 
función PROMEDIO sólo requieren un argumento, el rango de los datos 
que queremos sumar o promediar. Una vez introducido el nombre Excel 
nos muestra una ayuda en pantalla que nos indica qué hace la función 
y qué argumentos necesita. Si el argumento está entre corchetes es que 
es opcional. Los argumentos se separan por “;”. Si sabemos el nombre 
de la función podemos invocarla tras poner el símbolo “=”. Excel nos irá 
ayudando si no sabemos el nombre exacto indicándonos las que tienen 
la raíz del nombre igual que lo que vayamos escribiendo. Otra 
alternativa es invocar las funciones desde el menú, en el botón de 
insertar función dentro del menú fórmulas lo que nos llevará a 
pantallas de ayuda. En dichas pantallas podemos escoger la categoría 
Fundamentos de Excel para Finanzas 
11 
de la función que queremos insertar ofreciendo después mucha ayuda 
sobre la función que hayamos escogido. Cuando usted se familiarice 
con una función no usará los menús para insertarla y lo hará, sin 
duda, directamente desde la barra de fórmulas. 
 
1.3. Formato 
El formato de las celdas es importante. Los botones del formato 
permiten realizar las operaciones más habituales: tipo de letra, tamaño, 
color, fondos, bordes, etc. 
 
Lo que podemos denominar texto plano puede formatearse con 
tipos de letra, color, justificación a izquierda o derecha, en negrilla o 
cursiva, etc. 
 
En cuanto a los números existen multitud de formatos. Los más 
recomendables son aquellos que facilitan la lectura y dificultan la 
confusión. En ese sentido es adecuado usar separador de miles y no 
Fundamentos de Excel para Finanzas 
12 
incluir decimales en aquellos casos que no es necesario. Por ejemplo, 
¿qué nos aportan los decimales en una cuenta de resultados? Sin 
embargo, al introducir un precio de un producto es posible que la cifra 
decimal sea necesaria. Con los porcentajes, tanto cuando se trata de 
inputs o datos como de outputs o resultados, suele ser apropiado 
incluir decimales para evitar confusiones. En el caso de las fechas 
existen numerosos formatos alternativos. 
 
Independientemente de que tengamos textos o cifras, en las celdas 
podemos incluir cuestiones como los bordes o los rellenos. 
 
Si lo deseamos podemos eliminar las finas líneas grises que 
delimitan las celdas. Para ello en el menú Vista desmarcamos la opción 
“Líneas de cuadrícula”. De esta forma sólo se verán las divisiones de 
celda que nosotros hayamos diseñado explícitamente con la opción de 
bordes de celda. 
Antes de acabar este pequeño apartado acerca de los formatos 
vamos a ver dos cuestiones que nos parecen de mucha utilidad y que 
usamos habitualmente. 
Fundamentos de Excel para Finanzas 
13 
Por un lado el formato personalizado de número. Se accede a dicha 
opción en la ficha número del menú Inicio. Permite indicar un formato 
concreto a una celda. 
 
En el primer ejemplo le hemos indicado que la celda C28 ponga el 
texto resultado, independientemente de la cifra o fórmula que 
incluyamos. En nuestro caso nos ayudará a veces para mejorar la 
apariencia de alguna tabla. En el segundo ejemplo le hemos indicado 
que añada el texto “personas” al contenido de la celda. Así nos indica en 
nuestro caso “5 personas”. En cualquiera de los dos ejemplos vistos sólo 
se está afectando al formato de la celda, no a su contenido ni 
operatividad. 
Por último, a veces puede ser de ayuda el uso del operador de 
conexión de Excel (&) que permite mezclar textos y celdas mejorando la 
información que se ofrece. 
 
En nuestro ejemplo vinculamos el texto “El precio es” con el 
contenido de una celda. En la segunda variante usamos la función 
TEXTO para mejorar la forma en la que el contenido de la celda se 
muestra. Tenga en cuenta que las celdas C33 y C34 ya no incluyen 
cifras, ya no son operativas. 
Las posibilidades de formato que tiene Excel son innumerables. 
Nuestra recomendación general es que la sencillez y sobriedad debe 
primar para facilitar la lectura. No obstante puede haber ocasiones en 
que deba usarse todo el potencial de variedades de formato. 
1.4. Validación 
A la hora de introducir datos, información, en la hoja de cálculo 
una técnica interesante consiste en restringir en las celdas destinadas a 
Fundamentos de Excel para Finanzas 
14 
ese propósito restricciones en cuanto a la información que pueden 
albergar, reduciendo así la posibilidad de que un determinado cálculo 
se haga con un input inapropiado. Para realizar las tareas de validación 
debemos seleccionar la celda o celdas a las que afectará y acudir, 
dentro del menú Datos al submenú Validación de datos dentro del 
grupo de Herramientas de Datos. Dicha ficha tiene tres zonas. En la 
primera se indica la Configuración, el tipo de validación. En la segunda 
el Mensaje de entrada, el mensaje que el usuario verá cuando se sitúe 
sobre la celda en cuestión. En la tercera el Mensaje de error, el menaje 
que el usuario obtendrá si trata de introducir información que no esté 
permitida en la celda. 
Vemos un primer ejemplo en el que le indicamos que en la celda B4 
sólo pueden incluirse números enteros entre 1 y 10. 
En B6 realizamos el mismo tipo de validación, pero el mínimo y el 
máximo no lo introducimos manualmente, sino que lo vinculamos a las 
celdas D6 y E6 respectivamente. 
 
Otra validación que podemos realizar es indicando que el usuario 
sólo puede escoger la entrada de una celda de una determinada lista 
que se le presenta. En nuestro caso le hemos indicado en B8 que sólo 
puede incluir los valores 1, 2, 3 y 4. 
Fundamentos de Excel para Finanzas 
15 
 
En B10 realizamos el mismo tipo de validación pero la lista de 
datos posible se la indicamos referenciando el rango D10:G10. 
 
También podemos realizar la validación indicando que sólo puede 
introducirse información decimal. En nuestro caso le hemos indicado 
entre el 0% y el 20%. Observe que el 20% se introduce como 0,2. 
Fundamentos de Excel para Finanzas 
16 
 
De forma similar podemos indicar que un input haya de ser mayor 
o menor que una determinada cifra, o incluso que una determinada 
fecha, como en el ejemplo siguiente. 
 
Por último, también puede hacerse una validación personalizada, 
mediante una fórmula. En nuestro ejemplo le indicamos que la entrada 
de la celda no puede coincidir con el mínimo de un determinado rango. 
Fundamentos de Excel para Finanzas 
17 
 
En general las validaciones de celdas input tienen dos ventajas. Por 
un lado evitan el que el usuario introduzca un valor inadecuado en una 
celda. Por otro lado, usando adecuadamente la opción de Información 
de entrada se podrá ofrecer una interesante información “en línea” 
acerca del uso del modelo en cuestión. 
1.5. Formato condicional 
En este repaso a algunas de las cuestiones de formato de Excel 
vamos a ver brevemente una herramienta que es muy útil para ayudar 
a visualizar o analizar los resultados de un determinado trabajo, cual es 
Formato condicional. Esta herramienta permite que una celda tenga un 
formato diferente en función de su propio contenido incluso en función 
del contenido de otra celda. Hay muchísimas posibilidades dentro de 
esta opcionalidad. Para acceder al formato condicional debemos 
dirigirnos dentro del menú Inicioa la etiqueta Formato condicional 
dentro del submenú Estilos. Dentro de dicha etiqueta podremos crear 
una nueva regla de formato condicional o editar una existente. Veamos 
algunos ejemplos. 
Fundamentos de Excel para Finanzas 
18 
En nuestro primer ejemplo en el rango B7:K7 situamos diferentes 
cifras. En la celda E5 situamos otra cifra. Nuestra propuesta será que a 
las celdas del rango B7:K7 se les modifique el formato cuando su valor 
coincida con el de la celda E5. 
 
Le hemos indicado que ponga el número en rojo y negrilla y con un 
fondo azulado. En primer lugar seleccionamos el rango B7:K7. 
Acudimos a Nueva regla. Como se puede ver en la siguiente figura 
hemos creado la regla dentro del grupo “Aplicar formato únicamente a 
las celdas que contengan”. En dicha sección le indicamos que la celda 
de referencia es E5, con los símbolos $. En el botón formato escogemos 
el tipo de formato que queremos aplicar. 
 
De forma similar podemos indicarle que queremos que modifique el 
formato a celdas que contengan valores por encima del de referencia: 
 
Fundamentos de Excel para Finanzas 
19 
En el siguiente ejemplo realizamos el formato condicional para tres 
columnas siendo la cifra de referencia diferente para cada una de ellas. 
 
Puede hacerse columna a columna, lo cual es laborioso. Lo mejor 
es seleccionar toda la tabla y realizar el formato condicional de una sola 
vez. Como podemos ver en la siguiente figura sólo es necesario eliminar 
uno de los símbolos dólar, el que antecede a la letra B (en este caso). 
Ver el apartado sobre copiado y pegado y el papel de los símbolos $. 
 
Una opción más compleja es la de establecer un formato 
condicional en función de que se cumpla un determinada fórmula. Por 
Fundamentos de Excel para Finanzas 
20 
ejemplo, queremos que en las siguientes filas se marque de forma 
especial el valor mínimo de cada fila. 
 
Para ello seleccionamos el rango B34:E37 y acudimos a la opción 
de “Utilice una fórmula que determine las celdas para aplicar formato” 
en el menú de crear regla. Introducimos las fórmula 
“=B34=MIN($B34:$E34)”.Cuando dicha fórmula sea cierta, es decir, 
cuando la celda de referencia sea el mínimo de su fila aplicará el 
formato condicional. Observar que en el rango de la función MIN se ha 
utilizado bloqueado parcial (ver el apartado de copiar y pegar), de forma 
que la fórmula se aplique fila a fila. Cambiando el bloqueo se podría 
aplicar por columnas. 
 
La última variante que vamos a ver es aplicar la opción de que 
cambie el formato a los valores en función de que estén por encima o 
Fundamentos de Excel para Finanzas 
21 
debajo del promedio. En nuestro caso lo hacemos para los que estén 
por encima del promedio. 
 
2. Copiado y pegado de fórmulas 
Antes de comenzar este apartado, recordar que para copiar y pegar 
información de unas celdas a otras pueden utilizarse diferentes 
sistemas: el menú edición, los iconos gráficos de la barra de 
herramientas, la combinación de teclas de método abreviado, el menú 
rápido del botón derecho del ratón y, cuando la zona de pegado está al 
lado de la de copiado, el arrastre del ratón. 
2.1. Copiado/pegado libre, sin bloqueos 
Supongamos que tenemos la siguiente tabla: 
 
Tenemos información de unidades vendidas y precios de venta para 
tres meses diferentes. Deseamos calcular los ingresos de cada mes. 
Para ello es suficiente con realizar la operación en la celda C7 para el 
primer mes y copiar la fórmula hacia la derecha para aplicarla a los 
meses 2 y 3. ¿Qué fórmula debemos introducir en este caso? 
Simplemente debemos introducir en C7 la fórmula =C5 * C6. Cuando 
Fundamentos de Excel para Finanzas 
22 
copiemos dicha fórmula hacia la derecha automáticamente en D7 
aparecerá la fórmula =D5 * D6, y así sucesivamente. Dado que hemos 
introducido inicialmente en C7 tanto C5 como C6 “limpias”, esto es, sin 
protección, Excel asume que deseamos repetir la operación pero con los 
datos de la columna correspondiente. Lo mismo hubiera ocurrido si 
deseamos copiar una fórmula hacia abajo o arriba, esto es de unas filas 
a otras (por supuesto también se mantiene el criterio si al copiar de 
forma horizontal nos movemos hacia columnas de la izquierda y no de 
la derecha). Este sistema de copiado/pegado es, quizás, el más 
utilizado. No se bloquea ni la columna ni la fila. 
2.2. Copiado/pegado con protección absoluta, bloqueo total 
En otras ocasiones puede que nos interese fijar una celda y que la 
misma no se modifique en la operación de copiado/pegado. Veamos un 
ejemplo: 
 
En este caso tenemos una serie de datos de ventas de diferentes 
vendedores y para diferentes meses. Deseamos calcular la comisión a la 
que tiene derecho cada uno de ellos y en cada uno de los meses 
sabiendo que en todos los casos se les debe aplicar el 20%, de la celda 
C11. 
Para ello en la celda C18 introducimos la fórmula = $C$10 * C13, y 
copiamos hacia la derecha, hasta la columna E y luego hacia abajo 
hasta la fila 20 (o primero hacia abajo y luego hacia la derecha). Al 
ponerle los símbolos $ antes de la letra C y del número 11 ($C$11) 
Fundamentos de Excel para Finanzas 
23 
estamos protegiendo (bloqueando) la columna C y la fila 10; ello 
significa que aunque nos movamos en la horizontal (a través de 
columnas) Excel va a respetar la columna C y no la variará, y aunque 
nos movamos en la vertical (a través de filas) Excel va a respetar la fila 
11 y no la variará. Por tanto, copiemos donde copiemos la fórmula en la 
hoja siempre respetará la posición C11, que es la que contiene nuestro 
dato de comisión “universal” a aplicar. 
Por comodidad, para poner los símbolos $, una vez puesto =C11, y 
antes de poner el operador de multiplicación presionar la tecla de ayuda 
F4 y Excel pondrá automáticamente los símbolos $. 
2.3. Copiado/pegado con protección relativa (bloqueando columna, pero 
no fila) 
A veces no es necesario, o no deseamos, proteger o bloquear tanto 
la columna como la fila. Observemos el siguiente ejemplo: 
 
En este caso deseamos calcular los impuestos a pagar conociendo 
los beneficios de tres empresas en dos años diferentes y la tasa 
impositiva aplicable (común para las tres empresas, pero diferente para 
cada año). Para ello en la celda C32 introducimos la fórmula = $C28 * 
C25 y la copiamos/pegamos hacia la derecha hasta la columna E. La 
columna C, que es la que contiene la tasa impositiva, queda bloqueada; 
de esta forma al copiar hacia la derecha se respeta dicha columna. Sin 
embargo, al no haber bloqueado la fila 28, al copiar hacia abajo, al año 
Fundamentos de Excel para Finanzas 
24 
2, Excel coge la celda C29, que es la que contiene la tasa impositiva del 
segundo año. Para introducir los símbolos $, una vez introducido en 
C32 =C28, y antes de introducir el operador de producto pulsar 
repetidas veces la tecla de ayuda F4 hasta que aparezca la combinación 
deseada. 
2.4. Copiado/pegado con protección relativa (bloqueando fila, pero no 
columna) 
En el siguiente ejemplo nos interesa proteger o bloquear la fila pero 
dejar libre la columna: 
 
En este caso el porcentaje de comisión para cada vendedor es el 
mismo en todos los meses pero diferente para cada vendedor. En la 
celda C46 introduciremos la fórmula = C$38 * C40, de esta forma 
protegeremos la fila 38 pero no la columna. Al copiar hacia la derecha 
entrarán en la fórmula las comisiones de los vendedores 2 y 3, pero al 
copiar hacia abajo la fila 38, en la cual está el porcentaje de comisión, 
permanecerá inalterada a través del área de copiado/pegado. 
2.5. Un último ejemplo con un poco de todo 
En este caso deseamos calcular a cuánto ascendería una cuantía 
de 1.000 euros colocada a diferentes tipos de interés durante una serie 
de años. Veámoslo: 
Fundamentos de Excel para Finanzas 
25 
 
En la celda C57, para un año y el 10% introducimos la siguiente 
fórmula =$C$54*(1+C$56)^$B57.Protegemos tanto fila como columna 
en C54, pues la cuantía es común para todos los cálculos. Protegemos 
la fila 56 en C$56 y no la columna para facilitar el copiado/pegado a 
columnas adyacentes asumiendo tipos de interés diferentes. Por último, 
en el caso del exponente del número de años de la operación, 
protegemos la columna en $B57 pues la columna B es la que contiene 
los años, pero no la fila para que al copiar/pegar hacia abajo asuma los 
diferentes años para los cuales nos interesa realizar el cálculo. 
Un último consejo. Si no necesita que el bloqueo sea parcial, 
entonces realice bloqueos absolutos. Así reducirá usted la posibilidad 
de error, reducirá la posibilidad de estar bloqueando justo al revés de lo 
deseado. 
3. Nombres 
En este apartado tratamos una cuestión que a veces puede 
ayudarnos a clarificar y simplificar nuestro trabajo, abordamos la 
definición de nombres. 
3.1. Nombre de celda 
Es posible dar un nombre a una celda concreta. Para ello nos 
situamos en dicha celda y en la barra de fórmulas, a la izquierda de 
todo, donde figura la ubicación de la misma posicionamos el ratón, con 
ello la referencia de dicha celda parpadeará y se situará a la izquierda. 
Fundamentos de Excel para Finanzas 
26 
Escribimos entonces el nombre deseado y pulsamos Retorno. También 
podemos definir nombres (o borrarlos o reubicarlos) desde el menú. 
Debemos ir al menú Fórmulas, a la sección de Nombres definidos y a la 
etiqueta Asignar nombre. 
En el siguiente ejemplo hemos definido a la celda C4 con el nombre 
Precio. Así, para invocar dicha celda ahora podremos utilizar su 
ubicación, C4, o su nombre, Precio. 
 
Como podemos observar al realizar los cálculos en C7:E7, aparece 
en todos ellos Precio. No fue necesario introducirla en todas las celdas. 
Bastó introducir en C8 la siguiente fórmula =Precio * C16, y 
copiar/pegar hacia la derecha. Hemos conseguido lo mismo que si 
hubiéramos puesto =$C$4 * C6, esto es, con protección absoluta. 
Entre las ventajas de este sistema están el que, para invocar ese 
dato, el de la celda C4, desde cualquier hoja de cálculo del mismo libro 
será suficiente utilizar el nombre Precio (esto es, los nombres son 
válidos para todo el libro salvo que se indique lo contrario en las 
especificaciones en el menú de Asignar nombre). Por otro lado, si los 
nombres son claros y significativos facilita la comprensión de las 
fórmulas. 
3.2. Nombre de rango 
También es posible definir un nombre para un rango de celdas y no 
para una sola celda. La forma de definir un nombre para un rango es 
igual que para una celda. Seleccionamos el rango y realizamos las 
mismas operaciones que indicamos para la definición de un nombre 
para una celda individual. Veamos un ejemplo: 
Fundamentos de Excel para Finanzas 
27 
 
En este caso hemos nombrado al rango C11:E11 con el nombre 
Unidades y la celda C4 sigue siendo la celda Precio. 
En la fila 12, simplemente le indicamos que multiplique los 
nombres y en la columna C cogerá por un lado el Precio (único) y por el 
otro el valor de la cuantía de unidades que le corresponda. En el caso 
de las unidades es como si tuviéramos un bloqueo de fila y no de 
columna. 
Podríamos poner más ejemplos, combinando nombres de celdas y 
nombres de rango, pero seguro que usted ya ha cogido el hilo y es capaz 
de explorar por sí mismo las diferentes posibilidades y utilizar las que le 
resulten más cómodas y/o útiles. Por nuestra parte, pocas veces 
recurrimos a estas alternativas, aunque son de gran utilidad cuando se 
hacen modelos para terceros, para que éstos puedan seguir con mayor 
facilidad los cálculos. 
3.3. Crear nombres desde la selección 
En el siguiente ejemplo calculamos la cuota de un préstamo a 
partir de una determinada tasa de interés, una determinada duración 
de la operación y una cuantía. Usamos la función PAGO, que veremos 
en la sección de funciones financieras. 
Fundamentos de Excel para Finanzas 
28 
 
En vez de ir nombrando una a una las celdas, en este caso lo que 
hemos hecho es seleccionar el rango B18:C20 y acudir al menú “Crear 
desde la selección” en el submenú de Nombres definidos dentro del 
menú fórmulas. Le hemos indicado que cree los nombres a partir de los 
valores de la columna izquierda, en nuestro caso la B, que es la que 
contiene lo rótulos de nuestras variables. 
3.4. Nombres en activo 
A veces puede interesarnos conocer los nombres que tenemos 
activos en nuestra hoja de cálculo. Para ello nos situamos al fondo de la 
hoja, en nuestro caso en B28 y acudimos al menú Utilizar en la 
fórmula, dentro de Nombres definidos. 
Al fondo se encuentra la opción Pegar Nombres y dentro de la 
misma la opción Pegar lista. Clicamos en la misma y tendremos el 
resultado que se puede observar en la figura adjunta, el listado de 
nombres junto con la referencia de la celda a la que se refieren. 
Fundamentos de Excel para Finanzas 
29 
 
4. Funciones aritméticas 
Comenzamos aquí un pequeño repaso a las funciones que 
consideramos básicas en diferentes apartados, en primer lugar, con 
algunas aritméticas. 
4.1. SUMA 
La función SUMA (rango) permite la adición de todas las cuantías 
contenidas en el rango de referencia: 
 
Como vemos en el ejemplo, en la celda C11 le hemos indicado que 
sume el contenido de C6 hasta C10 y luego hemos copiado/pegado 
hacia la columna E. Podríamos haber puesto en C11 la siguiente 
fórmula, que suma una a una las celdas = C6 +C7 +C8 +C9 +C10. 
Además de ser más rápido con la función SUMA (aprovechando el icono 
Fundamentos de Excel para Finanzas 
30 
Σ de la barra de herramientas) la principal ventaja de esta función sobre 
la suma individualizada es que si ahora añadimos nuevos conceptos de 
gasto, la función se autoajustará, sin necesidad de retocarla. Con la 
suma individualizada deberíamos añadir el nuevo concepto a la 
fórmula. 
Permítanos un par de consejos. En primer lugar, inserte las nuevas 
filas en medio de las previas. Si realiza la inserción al final del rango, en 
nuestro caso en la fila 11 original donde está el total gastos, la función 
suma no cogerá la nueva fila, que será la nueva fila 11 y quedará fuera 
del rango (filas 6 a 10). En segundo lugar, cuando use el icono de Σ 
compruebe siempre que el automatismo efectivamente está cogiendo el 
rango que usted desea. Recuerde que puede corregir el rango de SUMA 
que el automatismo de Excel le propone. Un error habitual es el que se 
produce cuando en una fila tenemos los números de año (2016, 2017, 
etc) y debajo una serie de cifras, y al sumarlas sumamos por error el 
número identificativo del año. 
4.2. SUMA con rango semicerrado 
En el siguiente ejemplo vamos a aprovechar lo ya visto en lo 
referente a copiado/pegado con protección de celdas. Veamos el 
siguiente ejemplo: 
 
Como vemos, para calcular las ventas acumuladas de nuestros tres 
vendedores, en la celda C20 introducimos la fórmula =SUMA($C16:C16) 
que luego copiamos hacia la derecha y hacia abajo. Al fijar la columna 
Fundamentos de Excel para Finanzas 
31 
C del inicio de rango de suma pero no fijar la del final de rango 
conseguimos que a medida que nos desplazamos entre trimestres vayan 
acumulándose las ventas. No fijamos la fila pues al copiar/pegar hacia 
abajo querremos que coja los datos de los otros dos vendedores. 
4.3. CONTAR, CONTARA y CONTAR.BLANCO 
Una fórmula que cuenta devuelve el número de celdas de un rango 
específico que reúne ciertos criterios. 
- CONTAR. Devuelve el número de celdas de un rango que 
contiene valores numéricos. 
- CONTARA. Devuelve el número de celdas de un rango 
que no están en blanco (una celda puede contener texto). 
- CONTAR.BLANCO. Devuelve el número de celdas en 
blanco de un rango. 
 
En el ejemplo anterior, se puede observar que son 8 las celdas del 
rango. De ellas, 5 tienen número, 6 tienen alguna información y 2 están 
en blanco. 
4.4. SUMAR.SI y CONTAR.SIEstas funciones permiten realizar la suma o conteo de una serie de 
datos cuando se cumple una determinada condición. Veamos un 
ejemplo: 
Fundamentos de Excel para Finanzas 
32 
 
Como podemos observar, tenemos una serie de cuantías y una 
serie de vendedores responsables de las mismas. Para calcular la 
cuantía total que corresponde a Luis introducimos en la celda F40 la 
expresión =SUMAR.SI($C$40:$C$49;E40;$B$40:$B$49). Esta suma 
condicional requiere en primer lugar el rango en el que se encuentra la 
condición a verificar, en este caso C40:C49, que es donde situamos los 
nombres de los vendedores de cada operación. Fijamos las celdas con 
los símbolos $ pues al copiar/pegar hacia abajo para los totales de 
David y Sara las celdas de referencia son las mismas. A continuación 
introducimos E40, la celda donde se encuentra la condición a verificar, 
en este caso que el vendedor sea Luis. No fijamos dicha celda para 
facilitar que al copiar/pegar hacia abajo se aplique a los otros dos 
vendedores. El último elemento de la fórmula es el rango donde se 
encuentran los datos que hay que sumar si se cumple la condición; en 
este caso el rango es B40:B49, el cual fijamos para facilitar el 
copiado/pegado hacia abajo. 
Al igual que la función SUMA, con SUMAR.SI podemos insertar 
nuevas filas (o columnas, según se trate) y la fórmula las “engullirá”. 
En el caso de CONTAR.SI, cuenta las celdas que cumplen la 
condición analizada. En nuestro caso queremos saber el número de 
operaciones que ha hecho cada vendedor. Sólo necesitamos el rango de 
celdas a comprobar (el mismo que en la función SUMAR.SI) y el criterio 
que se desea comprobar (en el caso de las operaciones de Luis, E45). 
Fundamentos de Excel para Finanzas 
33 
4.5. SUMAPRODUCTO 
Esta función permite multiplicar los valores de dos rangos, dato a 
dato y realizar la suma de los resultados. 
 
Como vemos, sólo es preciso indicar los dos rangos que queremos 
multiplicar. Deben tener igual número de datos. 
4.6. SUMAR.SI.CONJUNTO 
Esta función permite realizar sumas chequeando más condiciones 
que la función SUMAR.SI, que sólo permite analizar un criterio. 
 
En nuestro caso queremos analizar dos criterios, el vendedor y si 
las ventas son nacionales o exteriores. El primer argumento es el rango 
a sumar, en nuestro caso el rango B66:B75. A continuación se van 
introduciendo los rangos de criterios y el criterio a cumplir, y así 
sucesivamente. Hemos realizado bloqueos parciales para facilitar el 
copiado de la fórmula desde G66 al resto de celdas. 
Fundamentos de Excel para Finanzas 
34 
5. Funciones estadísticas básicas 
5.1. MAX, MIN y PROMEDIO 
Estas tres funciones extraen el valor máximo, mínimo y la media 
aritmética de una determinada serie de datos de un rango. 
 
Sólo es preciso indicar el rango en el cual se encuentran los datos a 
analizar. 
5.2. Funciones de desviación típica y varianza 
Con estas funciones se extrae la desviación típica, y la varianza, 
tanto muestral como poblacional. 
 
Sólo es preciso indicar el rango en el cual se encuentra la serie de 
datos del que queremos conocer la varianza o la desviación típica. 
5.3. Funciones de covarianza y correlación 
Para conocer la covarianza poblacional o muestral es preciso 
indicar los rangos de las dos series de datos implicadas. El orden es 
Fundamentos de Excel para Finanzas 
35 
indiferente. De igual forma el coeficiente de correlación 
(COEF.DE.CORREL) precisa que le indiquemos, de forma indistinta, los 
rangos de las dos series de datos. 
 
5.4. JERARQUIA 
La función JERARQUIA nos indica el puesto que ocupa un dato 
dentro de una serie en función de su valor, ya sea en orden ascendente 
o descendente. 
 
Debemos indicar el dato que queremos chequear, en segundo lugar 
la serie a la que pertenece, y por último indicaremos con un 0 si el 
orden que buscamos es descendente o un 1 si es ascendente. En 
nuestro ejemplo hemos pedido la jerarquía descendente. 
5.5. K.ESIMO.MAYOR 
La función K.ESIMO.MAYOR nos indica el valor que ocupa una 
determinada posición jerárquica dentro de una serie, de mayor a menor. 
Fundamentos de Excel para Finanzas 
36 
 
Debemos indicar en primer lugar la serie de datos y en segundo 
lugar que orden jerárquico, de mayor a menor, buscamos. En nuestro 
caso el valor de orden 1 es 650, el de orden 10 es 230. 
5.6. K.ESIMO.MENOR 
De forma análoga a la anterior, la función K.ESIMO.MENOR nos 
indica el valor que ocupa una determinada posición jerárquica dentro 
de una serie, de menor a mayor. 
 
Debemos indicar en primer lugar la serie de datos y en segundo 
lugar que orden jerárquico, de menor a mayor, buscamos. En nuestro 
caso el valor de orden 1 es 230, el de orden 10 es 650. 
5.7. PENDIENTE 
La función PENDIENTE nos da la pendiente de la recta que mejor 
se ajusta a la nube de puntos generada entre dos variables, una 
independiente (x) y otra dependiente (y), según la ecuación y = a + b x. 
Es el término “b” de la ecuación de regresión lineal. 
Fundamentos de Excel para Finanzas 
37 
 
Debemos indicar en primer lugar el rango de la serie de la variable 
dependiente y luego el rango de la serie de la variable independiente. En 
este caso el orden de las series sí es importante. 
5.8. DISTR.NORM.N 
Esta función nos indica la probabilidad acumulada de un 
determinado valor dentro de una función normal y dadas su media y su 
desviación típica. 
 
Debemos indicarle en primer lugar el valor buscado, a continuación 
la media, luego la deviación típica, y por último, con la opción 
VERDADERO le indicamos que queremos la probabilidad acumulada. 
5.9. INV.NORM 
Esta función nos indica el valor que deja una determinada 
probabilidad acumulada a su izquierda dentro de una función normal y 
dadas su media y su desviación típica. 
Fundamentos de Excel para Finanzas 
38 
 
Debemos indicarle en primer lugar el valor de probabilidad 
acumulada buscado, a continuación la media y luego la deviación 
típica. 
5.10. PERCENTIL Y RANGO.PERCENTIL 
Dados los valores de una serie la función PERCENTIL nos indica el 
porcentaje de los mismos que son inferiores a un valor determinado que 
se analice. (En la figura hemos ocultado las filas entre 16 y 102). 
 
Como vemos, debemos indicar en primer lugar el rango de datos y 
posteriormente el valor que queremos testar, en nuestro caso el de la 
celda E7. 
La función RANGO.PERCENTIL nos indica el valor que dentro de 
una determinada serie deja por debajo el porcentaje que nosotros 
queramos comprobar. Como vemos, debemos indicar en primer lugar el 
rango de datos y posteriormente el porcentaje que queremos testar, en 
nuestro caso el de la celda E12. 
Fundamentos de Excel para Finanzas 
39 
6. Miscelánea 
En este apartado veremos algunas funciones de diferentes 
categorías que nos pueden ser útiles, incluidas las operaciones con 
fechas. 
6.1. TEXTO 
La función TEXTO permite convertir un determinado valor en texto, 
con un formato especificado. 
 
En los dos ejemplos que presentamos convertimos sendas cifras 
con el formato que deseamos. En ambos casos debe indicarse la 
referencia a la celda y posteriormente, entre “” indicar el formato 
deseado. 
6.2. RAIZ 
Extrae la raíz cuadrada de un número. 
 
Sólo precisa como argumento la referencia a la celda donde se 
encuentra el número en cuestión. 
6.3. ABS, ENTERO, REDONDEAR 
Estas funciones nos permiten obtener el valor absoluto de un 
número, su raíz entera o bien redondearlo (más allá de cuestiones 
meramente de formato). 
Fundamentos de Excel para Finanzas 
40 
 
En el caso de REDONDEAR es preciso indicarle a cuántos 
decimales queremos que se realice el redondeo. El redondeo se hace 
desde 0,5 (incluido) hacia arriba a la cifra siguiente y de 0,5 (no 
incluido) hacia abajo a la cifra anterior. Existen variantes para 
redondear siempre por arriba, por abajo, números pares o números 
impares. 
6.4.Funciones de números aleatorios 
 
La función ALEATORIO, que no requiere ningún argumento, nos da 
un número aleatorio entre 0 y 1 cada vez que Excel refresca sus 
cálculos. Para forzar la actualización de Excel, pulse F9. 
La función ALEATORIO.ENTRE nos da un número aleatorio entre 
un valor inferior y otro superior que le hayamos indicado. Esos son los 
dos argumentos que deben incorporarse a la función, y en ese orden. 
Esta función no opera con datos decimales. En la figura vemos un 
Fundamentos de Excel para Finanzas 
41 
pequeño truco para salvar este inconveniente. Multiplicamos los valores 
porcentuales por 100, dentro de la función, y dividimos posteriormente 
el resultado por 100, fuera de la función. Esta función también cambia 
el resultado cada vez que Excel se refresca. 
6.5. Operaciones con fechas 
Dada una fecha podemos saber cuál será la fecha un número de 
días superior (sumar) o podemos saber la distancia en días entre dos 
fechas (restar). El día 1 es el 1 de Enero de 1900, y partir de ahí se 
establecen todos los demás. 
 
En nuestro ejemplo hemos hecho una suma y una resta. 
6.6. HOY y FIN.MES 
Existen numerosas funciones de fecha. Mostramos dos que suelen 
ser muy útiles. La función HOY, que no necesita argumentos, nos 
indica la fecha del día en el que nos encontramos. Por su parte, la 
función FIN.MES nos indica el último día del mes una serie de meses a 
partir de una fecha concreta. Es preciso indicarle en primer lugar la 
fecha de referencia y en segundo lugar el número de meses hacia 
delante (número positivo) o hacia a atrás (número negativo) del que 
queremos saber el último día del mes. 
Fundamentos de Excel para Finanzas 
42 
 
6.7. AÑO, MES, DIA, DIASEM 
Estas funciones extraen de una determinada fecha información 
referida al año, el mes, el día del mes y el día de la semana de que se 
trate. 
 
La única que requiere un argumento adicional a la celda de 
referencia es la función DIASEM. Nosotros le hemos indicado un 2, que 
significa que el lunes es un 1, el martes es un 2 etc. Si se omite, por 
defecto Excel interpretaría semanas comenzando el domingo (1) y 
acabando el sábado (7). En nuestro caso el 5-feb-2014 es el año 2014, 
mes 2, día 5 y fue un miércoles (3). 
6.8. FECHA.MES 
Por su parte, la función FECHA.MES nos indica el mismo día del 
mes una serie de meses a partir de una fecha concreta. Es preciso 
indicarle en primer lugar la fecha de referencia y en segundo lugar el 
número de meses hacia delante (número positivo) o hacia a atrás 
(número negativo) que queremos avanzar o retroceder en el tiempo. 
Fundamentos de Excel para Finanzas 
43 
 
7. Funciones lógicas 
Una de las cuestiones más interesantes de los programas de hoja 
de cálculo es la posibilidad de introducir funciones lógicas en las 
fórmulas. De alguna forma ayudan a romper la “linealidad” de los 
cálculos. Existen diferentes funciones condicionales predefinidas en 
Excel (NO, Y, O, SI). 
7.1. La función SI para una condición simple 
Empecemos con un en ejemplo sencillo. Deseamos calcular las 
comisiones en una serie de operaciones. La comisión alcanza el 5% de 
la cuantía de la operación, pero si dicha cuantía es inferior a 1.000 debe 
aplicarse una comisión mínima de 50. Veamos una forma de resolverlo: 
 
La función condicional opera con tres elementos o argumentos 
dentro del paréntesis: la condición que se analiza, resultado si se 
cumple y resultado si no se cumple. En nuestro caso hemos indicado 
que compruebe si la cuantía de C8 es inferior a la mínima de C6. Si se 
cumpliera dicha condición el resultado habría de ser el contenido de C5 
(la comisión mínima) y si no se cumple, esto es, si la cuantía supera la 
cifra de 1.000 entonces debe multiplicarse la cifra en cuestión por la 
comisión de C4. Por otro lado utilizamos los símbolos $ para permitir 
Fundamentos de Excel para Finanzas 
44 
copiar la fórmula hacia la derecha y que opere para las demás cuantías. 
La única celda que no fijamos es precisamente la de la cuantía de la fila 
8 sobre la que se desea calcular la comisión. 
En general todas las fórmulas que se desarrollen con la función SI 
responderán a este sencillo esquema, si bien pueden hacerse más 
complejas en la medida en la que los argumentos se hagan más 
complejos e incorporen, por ejemplo, nuevas funciones condicionales, 
que quedarán anidadas. Dentro de un momento le presentaremos un 
ejemplo de esto último, pero permítanos indicarle una forma sencilla de 
evitar la condicional del ejemplo anterior usando otra función de Excel, 
la función MAX. 
7.2. La función MAX como alternativa 
Dicha función devuelve el valor máximo de los contenidos en una 
serie de argumentos. Veámoslo: 
 
Introducimos dos argumentos en la función: por un lado la cuantía 
de comisión mínima, $C$14, y por otro lado la comisión calculada como 
porcentaje de la cuantía de la operación, $C$13*C17; cuando la 
comisión calculada sea superior al mínimo, esa será la respuesta de la 
fórmula, pero cuando sea inferior, la fórmula devolverá la cuantía de 
comisión mínima pues será superior a la calculada. 
Muchas operaciones de una sola condicional pueden simplificarse 
de forma similar a la que acabamos de mostrarle, ya sea con la función 
MAX o la función MIN, que opera de forma similar devolviendo el valor 
mínimo de una serie de elementos. 
Fundamentos de Excel para Finanzas 
45 
7.3. Uso de la función SI anidada 
En este caso queremos calcular los impuestos de varias empresas 
en un sistema fiscal que aplica un 30% de tasa impositiva a los 
beneficios que no alcancen los 90.000 euros y un 35% a los que 
excedan dicha cifra. Por supuesto el impuesto ha de ser cero si los 
beneficios son negativos. Hay multitud de formas de solucionar una 
cuestión como esta. Vamos a hacerlo anidando dos condicionales: 
 
Como se puede observar la primera condicional indica que si el 
beneficio es menor a cero, los impuestos serán cero. A partir de ahí, de 
no cumplirse esta premisa, es decir, si los beneficios fueran positivos, 
se abre la siguiente condicional. Esta segunda condición pregunta 
acerca de si los beneficios son inferiores o no al tramo de aplicación de 
la tasa alternativa; si son inferiores a dicha cifra, aplicaremos la tasa de 
C23 a la cifra de beneficio. Si fueran superiores al tramo de aplicación 
de la tasa alternativa entonces el impuesto se compondría de dos 
sumandos; en el primero de ellos aplicamos la tasa alternativa a ese 
tramo de aplicación, mientras que en el segundo sumando aplicamos la 
tasa impositiva general al exceso de beneficio sobre el tramo de 
aplicación del mínimo. Los símbolos $, como siempre, nos ayudan a 
fijar las posiciones que no deben variar al copiar/pegar la fórmula hacia 
la derecha, para aplicársela al resto de empresas. Evidentemente la 
primera condición podría eliminarse aplicando la función MAX. 
Sin duda usted podrá diseñar, por sí mismo, fórmulas alternativas 
que garanticen, en cualquier caso un resultado correcto. 
Fundamentos de Excel para Finanzas 
46 
En anidamientos complejos es aconsejable trazar sobre papel el 
esquema de decisión para tratar de entenderlas cuando uno no ha sido 
su creador, o los ha creado hace mucho tiempo. 
7.4. Anidamiento múltiple de la función SI 
En este caso vamos a anidar un total de cuatro sencillas 
condicionales. La función SI nos habilita dos resultados. Al anidar 
cuatro funciones SI tendremos 5 posibles resultados. Veamos el 
ejemplo: 
 
Establecemos en el rango C35:C45 las ventas de una serie de 
meses. En la celda C32 indicamos con cuantos meses de demora se 
producen los cobros de dichas ventas. En la columna D establecemos 
los cobros. Para ello vamos abriendo condicionales. Si la demora es 
cero, se cobran en cada mes las ventas del propio mes, si la demora es 
1, se cobran las ventas del mes anterior, y así sucesivamente. En la 
celda C32 hemos validado paraque sólo pueda introducirse 0, 1, 2 ,3 y 
4. Observe que en la condicional el 4 no aparece. Aunque no 
hubiéramos validado, la condicional le aplicaría una demora de cuatro 
meses siempre que no se indicara 0, 1, 2, o 3. Es conveniente dejar la 
función condicional de esta forma, para evitar que quede sin resultado 
posible si no se cumple ninguna de las condiciones y no dejamos una 
puerta de salida. Nuestra ruta de escape, en este caso, es el C35 del 
final de la última condición, el valor que adoptará la fórmula si no se 
cumple ninguna de las condiciones previas. 
Fundamentos de Excel para Finanzas 
47 
Nuestra recomendación general es que siempre trate de diseñar la 
función SI sobre el papel, con lenguaje ordinario, y una vez que la tenga 
clara, que la traslade a la hoja de cálculo. También reconocemos aquí 
que no somos entusiastas de la ayuda de Excel para crear este tipo de 
funciones desde el menú insertar función. Ayuda que es muy útil, sin 
embargo, en otros casos. 
Como siempre hay formas alternativas de realizar lo que acabamos 
de desarrollar. Puede ver alguna de ellas en la sección dedicada a 
funciones de búsqueda y referencia, más adelante. 
7.5. Función Y 
La función Y devuelve VERDADERO o FALSO cuando se cumplen o 
no todas las condiciones que se incluyen en sus argumentos. 
 
En nuestro caso queremos comprobar en qué duplas de valores de 
A y B ambos superan los mínimos establecidos. Esa circunstancia sólo 
se da en la primera dupla, la de la fila 54, la única que muestra el 
resultado VERDADERO. 
7.6. Función O 
La función O devuelve VERDADERO o FALSO cuando se cumplen o 
no alguna de las condiciones que se incluyen en sus argumentos. 
Fundamentos de Excel para Finanzas 
48 
 
En nuestro caso queremos comprobar en qué duplas de valores, o 
bien A o bien B (al menos uno de ellos) superan los mínimos 
establecidos. Esa circunstancia se da en los tres primeros pares de 
valores que muestran el resultado VERDADERO. 
7.7. Función NO 
La función NO devuelve VERDADERO o FALSO cuando no se 
cumple o sí se cumple una condición. Nos da el resultado contrario de 
lo que se pregunta. 
 
En nuestro caso nos indica FALSO en la pregunta de si 50 supera 
a 40 y sin embargo nos indica VERDADERO al chequear 10 frente a 40. 
7.8. Función SI.ERROR 
Esta función nos permite indicarle a Excel un posible resultado 
alternativo cuando un determinado cálculo genera un error. El primer 
argumento es el cálculo que queremos realizar y el segundo es el 
resultado que deseamos establecer como salida alternativa en caso de 
error. 
Fundamentos de Excel para Finanzas 
49 
 
En nuestro caso le hemos indicado que en caso de error Excel 
muestre “N.D”. De esta forma evitamos que en las columnas D y E, al 
dividir entre 0 los intereses nos indique un error (no es posible dividir 
entre cero). El valor alternativo puedes ser otra fórmula, otro cálculo. 
8. Funciones de búsqueda y referencia 
Este tipo de funciones nos pueden ayudar a buscar información y 
trasladarla de la forma más conveniente. Veamos algunas de ellas. 
8.1. ELEGIR 
Esta función nos permite seleccionar un dato de entre una serie de 
datos siguiendo una posición, o índice, en la serie que se haya indicado. 
En el ejemplo contamos con una serie de compras, en las celdas 
C7:C15 y deseamos establecer los pagos por las mismas según la 
demora que se establezca en C4. 
 
Observe que la función elegir establece como primer argumento el 
número índice que se utilizará para extraer el dato de la serie. En este 
Fundamentos de Excel para Finanzas 
50 
caso $C$4 +1; el sumarle 1 es necesario para permitir que la fórmula 
funcione con una demora en pagos de cero, pues la función no admite 
el cero como índice pues ningún dato representa la posición cero en la 
serie. Lo fijamos con los $ para poder copiar hacia abajo. El resto de 
argumentos son las celdas que deben situarse en el orden de extracción 
que indicaremos con el índice: en primer lugar nuestra posición 0, esto 
es pago al contado, que será la primera posición de la serie, C11, y a 
continuación pago a un mes, C10, y así sucesivamente. No fijamos 
posiciones en la serie para que se adapten al siguiente mes a medida 
que copiemos/peguemos hacia abajo. 
8.2. La función DESREF 
La función DESREF es, a nuestro modo de ver, una de las 
funciones menos conocidas y sin embargo más útiles. Nos permite 
invocar una celda a un número de filas de distancia y otro número de 
columnas de distancia de una celda referencia que indiquemos. 
Apliquémoslo a nuestro ejemplo de compras y pagos. 
 
En este caso indicamos que la celda de referencia son las compras 
del propio mes y que debemos coger una celda situada 2 filas arriba (el 
símbolo menos antes de $C$19 indica que hay que desplazarse hacia 
arriba en las filas; si fuese positivo nos desplazaríamos hacia abajo) y 
en la propia columna C, pues indicamos 0 columnas (si el número de 
Fundamentos de Excel para Finanzas 
51 
columnas fuese positivo nos desplazaríamos hacia la derecha y si fuese 
negativo hacia la izquierda). Al dejar sin fijar la celda de referencia 
posibilitáremos su copia/pegado hacia el resto de meses. 
Veamos un ejemplo en el cual DESREF trabaja en columnas en vez 
de en filas. 
 
Ahora el cero se lo ponemos al indicador de filas. 
8.3. Función DESREF al detalle 
Veamos un ejemplo de uso de DESREF extrayendo de una matriz 
una celda a partir de la celda central siguiendo las indicaciones de filas 
y columnas que se le hagan. 
 
Como vemos en la celda C45 le indicamos que extraiga desde la 
posición D49 (la celda referencia) 0 filas (C43) hacia abajo y dos 
columnas (C44) a la izquierda (por el signo menos). El resultado es, 
naturalmente, “celda 0.-2”. 
8.4. La función COINCIDIR 
La función coincidir sirve para encontrar la posición (física) de un 
valor dentro de una serie. En el siguiente, en C60, usamos COINCIDIR 
Fundamentos de Excel para Finanzas 
52 
para encontrar el lugar que ocupa en la serie C57:G57 el valor indicado 
en C59. 
 
En nuestro caso la cifra 220 está en la tercera posición. 
En el siguiente ejemplo combinamos COINCIDIR con ELEGIR para 
determinar qué empresa tiene la mayor y menor rentabilidad de una 
serie de empresas. 
 
Con las funciones MAX y MIN encontramos la rentabilidad máxima 
y la mínima. Con COINCIDIR extraemos su posición en la serie. Por 
último, con ELEGIR extraemos el nombre de la empresa que ocupa esa 
misma posición en la serie B66:B73. 
En ambos casos, dentro de la función COINCIDIR tras marcar el 
rango de búsqueda, (C66:C73), añadimos el valor 0, para indicar que la 
búsqueda tiene que ser exacta, esto es que no busque un valor que se 
aproxime sino justo el valor indicado. Esta función puede crearnos 
problemas si el valor buscado está repetido en la serie, pues nos dará la 
referencia de la primera vez que lo encuentre. 
Fundamentos de Excel para Finanzas 
53 
En el siguiente ejemplo combinamos COINCIDIR con DESREF para 
la misma tarea que antes. Con las funciones MAX y MIN encontramos 
la rentabilidad máxima y la mínima. Con COINCIDIR extraemos su 
posición en la serie. Por último, con DESREF extraemos el nombre de la 
empresa ese número de filas hacia abajo desde la posición B78. 
 
8.5. La función BUSCARH y BUSCARV 
Veamos otra función que nos permite resolver la búsqueda que 
acabamos de ver con la función COINCIDIR. Se trata de la función 
BUSCAR. Esta función tiene una variante para búsqueda horizontal, en 
filas (BUSCARH), y otra para búsqueda vertical, en columna, que nos 
gustan más que la función BUSCAR. Comencemos con un ejemplo de 
BUSCARV (para búsqueda vertical), 
 
Como vemos, en F93, le indicamos que busque el valor mínimo de 
la columna en la que tenemos las rentabilidades, esto es, 
MIN(B91:B98). A continuación, le indicamos la matriz que contiene toda 
la información (B91:C98). Excel realizará la búsquedasobre la primera 
columna de esta matriz. Seguidamente le indicamos en qué columna de 
Fundamentos de Excel para Finanzas 
54 
la matriz está el resultado que queremos mostrar, en este caso en la 
columna 2. Esto es, busca la rentabilidad mínima pero no nos devuelve 
ese valor sino el que se encuentre en la misma fila pero en la columna 
2. Por último, le indicamos FALSO para que realice la búsqueda en la 
matriz tal como está, sin reordenar la primera columna de mayor a 
menor. 
Observe que respecto al ejemplo anterior de la función COINCIDIR 
hemos cambiado de orden las columnas de rentabilidad y nombre de las 
empresas. Ello es debido a que la búsqueda siempre se realiza en la 
primera columna de la matriz, y por ello hemos tenido que poner como 
primera columna de la izquierda el rango de rentabilidades. 
Veamos ahora un ejemplo realizando la búsqueda en una fila 
(BUSCARH). Tratamos de conocer los años en los que se produce el 
mayor y el menor endeudamiento en una serie. 
 
Como podemos ver, con BUSCARH el procedimiento es igual al de 
antes. Le indicamos que busque el valor mínimo (cosa que hará en la 
primera fila) y que nos devuelva el valor de la misma columna pero de la 
fila 2 de la matriz. Añadimos FALSO por idéntica razón que antes. 
8.6. La función INDICE 
La función INDICE permite extraer un dato de una tabla indicando 
un número de fila y columna. A menudo, las funciones COINCIDIR e 
ÍNDICE se utilizan juntas para realizar búsquedas. 
En el siguiente ejemplo se pretende conocer el número de mujeres 
que hay en el grupo 3. En primer lugar se indica el rango de búsqueda, 
C112:E116. Posteriormente, la primera función COINCIDIR indica el 
número de fila en el que se encuentra el Grupo 3, y la segunda indica el 
Fundamentos de Excel para Finanzas 
55 
número de columna en la que se encuentran las mujeres. Así la función 
devuelve, dentro de la matriz especificada, el valor de la fila 3 columna 
2. 
 
9. Funciones financieras 
En esta sección vamos a repasar con ejemplos las principales 
funciones financieras que EXCEL tiene predefinidas. 
9.1. La función INT.EFECTIVO 
Esta función permite calcular el tipo de interés efectivo una vez se 
le indique el tipo de interés nominal y el número de periodos de cálculo 
de intereses por año. 
 
Como vemos, un 11,55% nominal pagadero por cuatrimestres (3 
pagos al año) se convierte en un 12,00% efectivo. 
9.2. La función TASA.NOMINAL 
Esta función realiza la operación contraria a la función 
INT.EFECTIVO; permite calcular el tipo de interés nominal una vez se le 
indique el tipo de interés efectivo y el número de periodos de cálculo de 
intereses por año. 
Fundamentos de Excel para Finanzas 
56 
 
Como vemos, un 12,00% efectivo equivale a un 11,55% nominal 
pagadero por cuatrimestres. 
9.3. Funciones con anualidades: VA, PAGO, TASA, NPER 
A continuación, veremos cuatro funciones que nos permiten 
conocer todos los elementos vinculados al valor actual de una renta 
constante: cuantía de la renta (PAGO), valor actual de la serie de rentas 
(VA), número de periodos (NPER) y tasa de interés (TASA). Conocidos 
tres de los elementos podemos conocer el cuarto. 
 
En C23 calculamos el Valor Actual (con VA) de la renta conociendo 
la tasa, el número de operaciones y la cuantía de la renta. Dado que 
Excel usa el concepto de partida y contrapartida, si queremos que el VA 
sea positivo hemos de introducir el Pago, la renta, en negativo, o bien 
poner antes de VA el signo menos. Debemos introducir la tasa, el 
número de periodos y la renta, en ese orden. 
En D22 calculamos la cuantía de la renta (con PAGO) conociendo 
los otros tres datos. En este caso ponemos en signo negativo el valor 
actual de la renta. Debemos introducir la tasa, el número de periodos y 
el VA de la renta, en ese orden. 
En E20 calculamos la tasa (con TASA). Debemos introducir el 
número de periodos, la cuantía de la renta y el VA de la renta, en ese 
orden. Como puede observarse, ponemos con signo negativo el VA, pues 
Fundamentos de Excel para Finanzas 
57 
dicho parámetro y las rentas deben tener signo opuesto dada su 
naturaleza de entrada/salida de fondos. De no ser así la operación no 
tendría sentido (con cuotas y cuantía en la misma dirección de entrada 
o salida). 
En F21 calculamos el número de periodos (con NPER). Como 
vemos, los argumentos de la función son la tasa de interés, la renta, y el 
VA, en ese orden. 
9.4. Función VA añadiendo un pago final 
A veces a una serie de anualidades contantes se le añade un último 
pago. Es un argumento opcional que podemos incorporar a cualquiera 
de las fórmulas del apartado anterior. En nuestro caso veámoslo 
aplicado a la función VA. 
 
En este caso, por variar, hemos puesto el signo menos antes de la 
función. Incorporamos el último argumento, C30, el pago final. 
Estamos calculando el Valor Actual de una serie de cuantías 
constantes más una última cuantía final. El PAGO (la renta constante) 
y el VF (el pago final) tienen el mismo signo. 
9.5. Función VF añadiendo un pago inicial 
Aunque no la hemos visto antes, también existe la función VF, que 
permite conocer el valor final de una renta constante. La veremos ahora 
añadiendo la presencia de un pago inicial. 
Fundamentos de Excel para Finanzas 
58 
 
También en este caso rigen las mismas convenciones en cuanto a 
los dignos que ya hemos indicado antes. Estamos calculando el Valor 
Final de una serie de cuantías constantes más una primera cuantía 
inicial. El PAGO (la renta constante) y el VA (el pago inicial) tienen el 
mismo signo. 
En todas las funciones que acabamos de ver existe un último 
argumento opcional. Nosotros lo hemos omitido, lo que implica que 
Excel asume que nuestra operación es pos pagable. Si queremos que 
considere todas las anualidades como prepagables deberíamos incluir 
un 1 como argumento final. 
9.6. Las funciones PAGO, PAGOPRIN y PAGOINT 
Estas tres funciones nos permiten calcular la cuota, la parte 
correspondiente a amortización y a intereses, respectivamente, de una 
operación de préstamo de pagos constantes, dada una cuantía, una 
tasa de interés y un plazo. La primera de ellas, PAGO, es independiente 
del periodo para el que se calcule (pues es constante), y ya la hemos 
visto antes, mientras que tanto el principal como los intereses son 
diferentes para cada periodo y por tanto debe indicarse el periodo de 
que se trate. 
 
Fundamentos de Excel para Finanzas 
59 
Como podemos observar PAGOPRIN y PAGOINT incorporan la celda 
B47 entre los argumentos, para indicar que deseamos conocer dichas 
cuantías para el primer periodo del préstamo. En los tres casos 
ponemos la cuantía, C43, con signo negativo para que el resultado 
aparezca en positivo. El préstamo y los pagos asociados tienen sentidos 
opuestos. En un caso el dinero va del prestamista al prestatario y en el 
otro va en sentido contrario. 
Estas funciones también tienen opciones que nos permiten 
indicarle si los intereses se pagan por anticipado, o si una parte de la 
operación se deja para una amortización final, al acabar la vida de la 
operación. 
9.7. VNA 
La función VNA permite conocer el Valor Actual de una serie de 
cuantías no necesariamente constantes. 
 
En la función VNA el primer argumento es la tasa de descuento y el 
segundo argumento es el rango de datos, que VNA asume son 
pospagables. 
9.8. TIR 
La función TIR extrae la tasa interna de rentabilidad de una serie 
de cuantías, de las cuales al menos una ha de ser negativa. 
 
Fundamentos de Excel para Finanzas 
60 
Al menos uno de dichos datos ha de tener signo negativo. 
Habitualmente, en una inversión, el primero de ellos. Si los datos son 
anuales, la tasa de rentabilidad obtenida será anual, si son mensuales, 
será mensual, etc. 
9.9. Las funciones VNA.NO.PER y TIR.NO.PER 
Nos permiten el cálculo del Valor Actual y de la Tasa de 
rentabilidad cuando los