Fundamentos de Excel para Finanzas

(Adaptado Excel 2016 y Office 365)

Actualizado 19 de Febrero de 2020

Alfonso Rodríguez Sandiás

Grupo Valoración Financiera Aplicada

[Link]/modeleva

[Link]

Universidad de Santiago de Compostela

Este documento está acompañado de seis ficheros:

• Fundamentos [Link]
• Fundamentos [Link]
• Fundamentos [Link]
• Fundamentos [Link]
• Fundamentos [Link]
• Fichero Funciones de [Link]

Objetivo cero errores: Aunque han sido revisados, tanto el

documento que tiene en sus manos como los XLS de apoyo pueden
contener algún error, ya sea técnico, ya sea de transcripción de la hoja
de cálculo al texto, o también errores en la redacción o tipográficos. No
dude en ponerse en contacto con el autor para alertar de dichos errores
y ayudar a mejorar este trabajo.

[Link]@[Link]
 Fundamentos de Excel para Finanzas

TABLA DE CONTENIDOS

1. HOJA DE CÁLCULO, CONTENIDOS Y FORMATOS ..................................................................................... 8

1.1. Introducción ...................................................................................................................... 8

1.2. Textos, datos y fórmulas ................................................................................................. 10

1.3. Formato ........................................................................................................................... 12

1.4. Validación ........................................................................................................................ 15

1.5. Formato condicional........................................................................................................ 19

2. COPIADO Y PEGADO DE FÓRMULAS .................................................................................................. 23

2.1. Copiado/pegado libre, sin bloqueos ................................................................................ 23

2.2. Copiado/pegado con protección absoluta, bloqueo total ............................................... 24

2.3. Copiado/pegado con protección relativa (bloqueando columna, pero no fila) ............... 25

2.4. Copiado/pegado con protección relativa (bloqueando fila, pero no columna) ............... 26

2.5. Un último ejemplo con un poco de todo.......................................................................... 27

3. NOMBRES ................................................................................................................................... 28

3.1. Nombre de celda ............................................................................................................. 28

3.2. Nombre de rango ............................................................................................................ 29

3.3. Crear nombres desde la selección ................................................................................... 30

3.4. Nombres en activo........................................................................................................... 30

4. FUNCIONES ARITMÉTICAS ............................................................................................................... 31

4.1. SUMA............................................................................................................................... 31

4.2. SUMA con rango semicerrado ......................................................................................... 32

4.3. CONTAR, CONTARA y [Link] .......................................................................... 33

4.4. [Link] y [Link] .................................................................................................... 33

4.5. SUMAPRODUCTO ............................................................................................................ 35

4.6. [Link] ..................................................................................................... 35

5. FUNCIONES ESTADÍSTICAS BÁSICAS ................................................................................................... 36

5.1. MAX, MIN y PROMEDIO .................................................................................................. 36

5.2. Funciones de desviación típica y varianza ....................................................................... 36

5.3. Funciones de covarianza y correlación ............................................................................ 37

2
 Fundamentos de Excel para Finanzas

5.4. JERARQUIA ...................................................................................................................... 37

5.5. [Link] ............................................................................................................. 38

5.6. [Link] ............................................................................................................. 38

5.7. PENDIENTE ...................................................................................................................... 39

5.8. [Link].N ................................................................................................................ 39

5.9. [Link] ....................................................................................................................... 40

5.10. PERCENTIL Y [Link] ................................................................................... 40

6. MISCELÁNEA ............................................................................................................................... 41

6.1. TEXTO .............................................................................................................................. 41

6.2. RAIZ ................................................................................................................................. 41

6.3. ABS, ENTERO, REDONDEAR ............................................................................................. 42

6.4. Funciones de números aleatorios .................................................................................... 43

6.5. TRANSPONER................................................................................................................... 43

6.6. Operaciones con fechas ................................................................................................... 44

6.7. HOY y [Link]................................................................................................................. 45

6.8. AÑO, MES, DIA, DIASEM .................................................................................................. 45

6.9. [Link] ...................................................................................................................... 46

7. FUNCIONES LÓGICAS ..................................................................................................................... 46

7.1. La función SI para una condición simple ......................................................................... 46

7.2. La función MAX como alternativa ................................................................................... 47

7.3. Uso de la función SI anidada ........................................................................................... 48

7.4. Anidamiento múltiple de la función SI............................................................................. 49

7.5. Función Y ......................................................................................................................... 50

7.6. Función O......................................................................................................................... 50

7.7. Función NO ...................................................................................................................... 51

7.8. Función [Link] ............................................................................................................ 51

7.9. Función [Link] ..................................................................................................... 52

8. FUNCIONES DE BÚSQUEDA Y REFERENCIA .......................................................................................... 52

8.1. ELEGIR ............................................................................................................................. 52

3
 Fundamentos de Excel para Finanzas

8.2. La función DESREF ........................................................................................................... 53

8.3. Función DESREF al detalle ............................................................................................... 54

8.4. La función COINCIDIR y COINCIDIRX ................................................................................ 56

8.5. La función BUSCARH, BUSCARV y BUSCARX .................................................................... 58

8.6. La función INDICE ............................................................................................................ 62

8.7. La función CAMBIAR ........................................................................................................ 62

8.7. Las funciones FILA y COLUMNA ....................................................................................... 63

8.7. La función INIDRECTO ..................................................................................................... 63

8.8. La función ORDENAR y ORDENARPOR............................................................................. 64

8.9. La función FILTRAR .......................................................................................................... 66

9. FUNCIONES DE TEXTO ................................................................................................................... 68

9.1. La función IZQUIERDA ..................................................................................................... 68

9.2. La función DERECHA ........................................................................................................ 69

9.3. Las funciones ENCONTRAR y HALLAR .............................................................................. 69

9.4. La función EXTRAE ........................................................................................................... 70

9.5. La función CONCAT .......................................................................................................... 70

9.6. La función UNIRCADENAS................................................................................................ 70

9.7. La función ESPACIOS ........................................................................................................ 71

9.8. La función IGUAL ............................................................................................................. 71

9.9. La función LARGO ............................................................................................................ 71

9.10. La función MAYUSC ....................................................................................................... 72

9.11. La función MINUSC ........................................................................................................ 72

9.12. La función NOMPROPIO ................................................................................................ 72

10. FUNCIONES FINANCIERAS ............................................................................................................. 72

10.1. La función [Link] ............................................................................................... 73

10.2. La función [Link] ............................................................................................ 73

10.3. Funciones con anualidades: VA, PAGO, TASA, NPER ..................................................... 73

10.4. Función VA añadiendo un pago final............................................................................. 74

10.5. Función VF añadiendo un pago inicial ........................................................................... 75

4
 Fundamentos de Excel para Finanzas

10.6. Las funciones PAGO, PAGOPRIN y PAGOINT ................................................................. 76

10.7. VNA ............................................................................................................................... 76

10.8. TIR.................................................................................................................................. 77

10.9. Las funciones [Link] y [Link] ...................................................................... 77

10.10. La función TIRM ........................................................................................................... 78

11. EJEMPLOS DE FUNCIONES ARRAY ................................................................................................... 78

12. USO DE LA HERRAMIENTA TABLA ................................................................................................. 81

12.1. Tablas de un solo input, columna .................................................................................. 81

12.2. Tablas de un solo input, fila ........................................................................................... 83

12.3. Tablas de dos inputs, fila y columna.............................................................................. 84

13. HERRAMIENTA BUSCAR OBJETIVO .................................................................................................. 85

14. ESCENARIOS .............................................................................................................................. 87

14.1. Uso del Administrador de Escenarios de Excel .............................................................. 87

14.2. Elaboración de Escenarios. Un método alternativo ...................................................... 90

15. SOLVER .................................................................................................................................... 91

16. USO DE REFERENCIAS CIRCULARES.................................................................................................. 94

17. ELABORACIÓN DE GRÁFICOS ......................................................................................................... 96

17.1. Gráfico de líneas básico ................................................................................................. 96

17.2. Gráfico de columnas básico........................................................................................... 99

17.3. Gráfico de columnas y líneas ....................................................................................... 101

17.4. Gráfico de columnas y líneas de doble eje vertical ...................................................... 101

17.5. Gráfico de áreas .......................................................................................................... 102

17.6. Gráfico de barras ......................................................................................................... 102

17.7. Gráfico de barras tipo tornado .................................................................................... 103

17.8. Gráfico de dispersión ................................................................................................... 104

17.9. Gráfico circular ............................................................................................................ 105

17.10. Gráfico con rango dinámico ...................................................................................... 107

17.11. Gráficos más elaborados ........................................................................................... 109

18. UNA MACRO SENCILLA............................................................................................................... 118

5
 Fundamentos de Excel para Finanzas

19. FUNCIONES PERSONALIZADAS ..................................................................................................... 120

19.1. Una función para calcular los impuestos .................................................................... 120

19.2. Función para indicar el nombre de la hoja .................................................................. 123

19.3. Funciones para mostrar la fórmula de una celda ........................................................ 124

19.4. Función para calcular el VAN ...................................................................................... 125

19.5. Función para calcular el VAN con ajuste de medio año .............................................. 126

19.6. Función para calcular el IRB e IRN ............................................................................... 126

19.7. Funciones para calcular el VAN con tasa de descuento variable ................................ 127

19.8. Función para calcular el VAN Modificado ................................................................... 128

19.9. Función para calcular la TIR No Periódica Modificada. ............................................... 129

19.10. Función para calcular el valor de un bono con una tasa de interés para la valoración
variable ........................................................................................................................................... 129

19.11. Función para calcular el valor de una renta con crecimiento constante durante un
periodo finito................................................................................................................................... 130

19.12. Función para calcular el valor de una renta con un crecimiento constante durante un
periodo finito seguida de un crecimiento constante ilimitado ........................................................ 131

19.13. Función para calcular el plazo de recuperación ........................................................ 131

20. USO DE MACROS CON BUSCAR OBJETIVO Y SOLVER .......................................................................... 132

20.1. Una macro sencilla para usar Buscar Objetivo ............................................................ 133

20.2. Una macro para usar Buscar Objetivo con una tabla en columna .............................. 134

20.3. Una macro para usar Buscar Objetivo con una tabla en fila ....................................... 135

20.4. Una macro para usar Buscar Objetivo con una tabla de dos ejes ............................... 136

20.5. Una macro para usar Solver ........................................................................................ 136

21. USO DE LA AUDITORIA DE FÓRMULAS ........................................................................................... 138

22. AGRUPAR, DIVIDIR, INMOVILIZAR PANELES, HIPERVÍNCULOS .............................................................. 140

22.1. Agrupar ....................................................................................................................... 140

22.2. Dividir .......................................................................................................................... 142

22.3. Inmovilizar paneles ...................................................................................................... 143

22.4. Hipervínculos ............................................................................................................... 144

23. IMPRIMIR ............................................................................................................................... 144

6
 Fundamentos de Excel para Finanzas

23.1 Selección de datos a imprimir ...................................................................................... 144

23.2 Encabezados y pie ........................................................................................................ 144

23.3 Salto de página............................................................................................................. 145

24. RECOMENDACIONES BÁSICAS SOBRE LA ELABORACIÓN DE MODELOS FINANCIEROS EN EXCEL.................... 146

24.1. Número de hojas de un modelo .................................................................................. 147

24.2. Alcance del modelo ..................................................................................................... 147

24.3. Orientación vertical u horizontal de un modelo .......................................................... 147

24.4. Orientación de izquierda a derecha y de arriba a abajo ............................................. 148

24.5. Acerca de inputs y outputs .......................................................................................... 148

24.6. Las celdas de inputs deben contener datos, no fórmulas y las celdas de cálculo no
deben contener datos, solo fórmulas .............................................................................................. 153

24.7. Introduzca los datos una sola vez................................................................................ 153

24.8. Introduzca niveles en “segunda línea” ........................................................................ 154

24.9. Creación de fórmulas y chequeo ................................................................................. 154

24.10. Optimice el tiempo y aproveche su trabajo............................................................... 155

25. ALGUNOS ATAJOS O TRUCOS BÁSICOS DE TECLADO EN EXCEL ............................................................. 157

7
 Fundamentos de Excel para Finanzas

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, aunque hemos incluido alguna
función de la versión de office 365, 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.

8
 Fundamentos de Excel para Finanzas

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 “+”
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

9
 Fundamentos de Excel para Finanzas

opciones básicas. Muchos usuarios nunca precisan cambiar dichas

opciones.

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:

10
 Fundamentos de Excel para Finanzas

Textos. Suele tratarse de información que se incorpora para

identificar el contenido de celdas adyacentes. Por ejemplo, los textos
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 una serie 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

11
 Fundamentos de Excel para Finanzas

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 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.

12
 Fundamentos de Excel para Finanzas

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
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.

13
 Fundamentos de Excel para Finanzas

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.

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 (&) o Ampersand, 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.

14
 Fundamentos de Excel para Finanzas

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
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

15
 Fundamentos de Excel para Finanzas

que se le presenta. En nuestro caso le hemos indicado en B8 que sólo

puede incluir los valores 1, 2, 3 y 4.

En B10 realizamos el mismo tipo de validación pero la lista de

datos posible se la indicamos referenciando el rango D10:G10.

16
 Fundamentos de Excel para Finanzas

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.

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.

17
 Fundamentos de Excel para Finanzas

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.

El siguiente ejemplo incluye un rango dinámico en el origen de una

lista desplegable.

En D22:K22 hemos establecido un total e 8 años. En D23

indicamos (celda validada con número enteros entre 1 y 8) cuántos años

18
 Fundamentos de Excel para Finanzas

queremos mostrar en la lita desplegable de B22. Validamos B22

indicando que queremos una lista, y en el origen usamos la función
DESREF para establecer que la lista se realiza a partir de C22, en la
misma fila (0), empezando en una columna a la derecha (1), con altura
de una fila (1) y anchura de las columnas indicadas en D23 (en nuestro
caso, 4). El desplegable mostrará cuatro años.

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ú Inicio a 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.

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.

19
 Fundamentos de Excel para Finanzas

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:

En el siguiente ejemplo realizamos el formato condicional para tres

columnas siendo la cifra de referencia diferente para cada una de ellas.

20
 Fundamentos de Excel para Finanzas

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 una determinada fórmula. Por
ejemplo, queremos que en las siguientes filas se marque de forma
especial el valor mínimo de cada fila.

21
 Fundamentos de Excel para Finanzas

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
debajo del promedio. En nuestro caso lo hacemos para los que estén
por encima del promedio.

22
 Fundamentos de Excel para Finanzas

En el XLS de referencia puede encontrar un par de ejemplos más

de formato condicional.

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 (Control +
C para copiar y Control + V para pegar (o simplemente Enter)), 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. También puede introducirse
la misma fórmula simultáneamente en todas las celdas de un rango
seleccionando dicho rango, introduciendo la fórmula en la primera
celda y validando con Control + Enter. Con esta última técnica
podremos evitar la realización de multitud de “copias y pegas”. No
obstante, lo que sigue a continuación es igualmente aplicable.

2.1. Copiado/pegado libre, sin bloqueos

Supongamos que tenemos la siguiente tabla:

23
 Fundamentos de Excel para Finanzas

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
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

24
 Fundamentos de Excel para Finanzas

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)
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

25
 Fundamentos de Excel para Finanzas

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
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

26
 Fundamentos de Excel para Finanzas

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:

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.

27
 Fundamentos de Excel para Finanzas

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.
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

28
 Fundamentos de Excel para Finanzas

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:

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

29
 Fundamentos de Excel para Finanzas

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.

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.

30
 Fundamentos de Excel para Finanzas

4. Funciones aritméticas

Comenzamos aquí un pequeño repaso a las funciones que

consideramos básicas en diferentes apartados. Hay muchas más de las
que vamos a detallar. Excel tiene sus funciones agrupadas en diferentes
categorías. Nuestra sugerencia es que acuda a la herramienta de
introducción de funciones y haga un repaso general a las mismas.
Comenzamos con algunas dentro de la categoría de 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

31
 Fundamentos de Excel para Finanzas

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
Σ 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:

32
 Fundamentos de Excel para Finanzas

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
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 [Link]

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).

- [Link]. 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. [Link] y [Link]

Estas funciones permiten realizar la suma o conteo de una serie de

datos cuando se cumple una determinada condición. Veamos un
ejemplo:

33
 Fundamentos de Excel para Finanzas

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 =[Link]($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 [Link] podemos insertar

nuevas filas (o columnas, según se trate) y la fórmula las “engullirá”.

En el caso de [Link], 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 [Link]) y el criterio
que se desea comprobar (en el caso de las operaciones de Luis, E45).

34
 Fundamentos de Excel para Finanzas

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. [Link]

Esta función permite realizar sumas chequeando más condiciones

que la función [Link], 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.

35
 Fundamentos de Excel para Finanzas

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. Aunque no veamos ejemplos de ellas, indicar que las últimas
versiones de Excel tienen las funciones [Link],
[Link] y [Link], que hacen los
diferentes cálculos pero sobre aquellos valores del rango que cumplan
las diferentes condiciones que se le pueden indicar (de forma similar a
lo mostrado anteriormente en la función [Link]).

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.

36
 Fundamentos de Excel para Finanzas

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
indiferente. De igual forma el coeficiente de correlación
([Link]) 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.

37
 Fundamentos de Excel para Finanzas

5.5. [Link]

La función [Link] nos indica el valor que ocupa una

determinada posición jerárquica dentro de una serie, de mayor a menor.

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. [Link]

De forma análoga a la anterior, la función [Link] 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.

38
 Fundamentos de Excel para Finanzas

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.

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. [Link].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.

39
 Fundamentos de Excel para Finanzas

5.9. [Link]

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.

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 [Link]

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 [Link] nos indica el valor que dentro de

una determinada serie deja por debajo el porcentaje que nosotros

40
 Fundamentos de Excel para Finanzas

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.

Nota: Aunque no las vemos en este documento son de gran interés

las funciones aritméticas y estadísticas tipo base de datos. Comienzan
con las letras BD. Le sugerimos que las “investigue” y verá que pueden
serle de gran utilidad cuando quiere realizar cálculos sobre rangos
usando criterios de filtrado. Por ejemplo, sumar las celdas de un campo
de la base de datos que cumplen un criterio (BDSUMA) o calcular su
promedio (BDPROMEDIO).

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.

41
 Fundamentos de Excel para Finanzas

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).

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.

42
 Fundamentos de Excel para Finanzas

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 [Link] 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, en C34, vemos
un pequeño truco para salvar este inconveniente. Multiplicamos los
valores porcentuales por 10.000, dentro de la función, y dividimos
posteriormente el resultado por 10.000, fuera de la función. Esta
función también cambia el resultado cada vez que Excel se refresca.

Si desea rellenar una matriz con números aleatorios le interesará la

nueva función de Excel MATRIZALEAT en la que deberá indicarle el
número de filas y columnas que desea rellenar (si no indica nada más
los números aleatorios serán entre 0 y 1), el valor mínimo y máximo (si
desea especificarlos, para que no sean 0 y 1) y opcionalmente si quiere
que los número sean enteros.

6.5. TRANSPONER

La función transpone permite convertir un rango o vector

horizontal en vertical o viceversa.

43
 Fundamentos de Excel para Finanzas

Tenemos una serie de datos en un vector horizontal (C37:G37).

Deseamos transponerlos en vertical en el rango B40:B44. Para ello
seleccionamos el rango de destino e introducimos la función
TRANSPONER indicando el rango de origen. Para validar la función
pulsamos simultáneamente Ctrl + Shift + Enter, de forma que la
función se aplicará a todo el rango de destino. El rango de destino
funciona como un bloque, no pueden manipularse sus celdas de forma
individual. Podemos volver a obtener un rango horizontal transponiendo
el rango B40:B44 en C46:G46, siguiendo el mismo procedimiento.

6.6. 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.

44
 Fundamentos de Excel para Finanzas

6.7. HOY y [Link]

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 [Link] 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 atrás (número negativo) del que
queremos saber el último día del mes.

6.8. 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).

45
 Fundamentos de Excel para Finanzas

6.9. [Link]

Por su parte, la función [Link] 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.

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

46
 Fundamentos de Excel para Finanzas

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
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

47
 Fundamentos de Excel para Finanzas

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.

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

48
 Fundamentos de Excel para Finanzas

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.

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 para que 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

49
 Fundamentos de Excel para Finanzas

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.

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.

50
 Fundamentos de Excel para Finanzas

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 [Link]

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.

51
 Fundamentos de Excel para Finanzas

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.

7.9. Función [Link]

Las últimas versiones de Excel han incorporado la función

[Link] que simplifica el anidamiento que hemos visto en el
apartado 7.3.

En este caso se van incorporando de forma intercalada las

diferentes pruebas lógicas y el resultado que habría que incluir en caso
de que dichas pruebas lógicas resultaran verdaderas.

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.

52
 Fundamentos de Excel para Finanzas

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
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.

53
 Fundamentos de Excel para Finanzas

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
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.

54
 Fundamentos de Excel para Finanzas

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”.

La función DESREF también nos permite establecer los límites de

un rango. Observe el siguiente ejemplo:

Queremos que el rango de la función SUMA comience en B161 e

incluya el número de celdas indicado en C157. Como vemos, el cierre
del rango, iniciado en B161, los establece DESREF apoyándose en
B160, indicando que el número de filas es el establecido en C157 y el
número de columnas 0. La función INDICE, que veremos
posteriormente, podría hacer la misma tarea.

55
 Fundamentos de Excel para Finanzas

8.4. La función COINCIDIR y COINCIDIRX

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 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

56
 Fundamentos de Excel para Finanzas

problemas si el valor buscado está repetido en la serie, pues nos dará la

referencia de la primera vez que lo encuentre.

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.

En las últimas versiones de Excel se ha incorporado COINCIDIRX

que hace la búsqueda con coincidencia exacta de forma
predeterminada, lo que suele eliminar la necesidad de añadir ese
argumento. Además también permite que la búsqueda sea no sólo en
“menor que” (cómo COINCIDIR de forma predeterminada) sino también
en “mayor que”, lo que le da mayor versatilidad. En los ejemplos
anteriores simplemente cambie COINCIDIR por COINCIDIRX y omita el
argumento de 0 (pues la coincidencia exacta ahora está
predeterminada). Veámoslo para el primer ejemplo:

57
 Fundamentos de Excel para Finanzas

8.5. La función BUSCARH, BUSCARV y BUSCARX

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 (BUSCARV), en
columna. En las nuevas versiones de Excel existe una función
denominada BUSCARX que mejora a las anteriores. Dado que
BUSCARX todavía es muy desconocida, veamos antes las versiones más
clásicas. 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úsqueda sobre la primera
columna de esta matriz. Seguidamente le indicamos en qué columna de
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

58
 Fundamentos de Excel para Finanzas

primera columna de la izquierda el rango de rentabilidades. BUSCARX

no tiene esta restricción

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.

En el siguiente ejemplo, que realizamos con BUSCARV, la

búsqueda no tiene que ser exacta, de esta forma podemos encontrar la
tasa impositiva según el tramo en el que se encuentre nuestra renta.
Veámoslo:

Tenemos un listado de los tramos de renta con la tasa aplicable.

Hasta 10.000 Euros el 0%, hasta 20.000 el 18%, etc. Hemos puesto un
primer dato de renta muy negativo para que marque la tasa aplicable
para cualquier valor inferior a 10.000. A la derecha hemos establecido
diferentes rentas que resumen las posibles situaciones, buscamos la

59
 Fundamentos de Excel para Finanzas

tasa con BUSCARV incluyendo la opción final VERDADERO, que

implica búsqueda por aproximación. Cuando la renta es inferior a
10.000 nos indica un 0%, inferior a 20.000 un 16%, etc. Observe que
cuando justo es 10.000 le aplica el 16%. Multiplicando la renta por la
tasa tenemos el impuesto. Al fondo de la figura, sobre la renta que se
indique, calculamos el impuesto integrando en una sola fórmula la
operación de búsqueda de la tasa y su multiplicación por la renta.

Veamos ahora el uso de BUSCARX en este último ejemplo. Hemos

intercambiado la posición de la columna de renta y la columna de tasa
para mostrar que BUSCARX ya no tiene la restricción de que la
búsqueda haya de realizarse en la primera columna.

Le indicamos el valor a buscar, la renta de la columna E, el rango

de búsqueda, la columna C (lista de rentas), el rango de resultado
(matriz devuelta), la columna B (tasa); hemos dejado un argumento sin
establecer (de ahí los dos ;) en el que se podría incluir un valor
predeterminado para que la función lo devolviera en caso de error, y por
último con el argumento “-1” le estamos indicando que busque
coincidencia exacta o el siguiente elemento menor.

Pero BUSCARX tiene más virtudes (además de la de no restringir la

búsqueda a la primera columna (o fila). En el siguiente ejemplo le
hemos incorporado el mensaje de error “N.D” en caso de que no
encuentre el valor buscado y, más interesante, hacemos que la función
no devuelva un sólo valor si no todo un rango. Observe:

60
 Fundamentos de Excel para Finanzas

Queremos que una vez escogido el jugador nos indique su posición,

su equipo y su nacionalidad. En C223 introducimos BUSCARX y a la
hora de indicar la matriz devuelta (rango de resultado) no le indicamos
una sola columna sino las tres. Excel pondrá los elementos de las tres
columnas en C223:E223, aunque sólo hemos introducido la función en
la primera de ellas. En caso de no encontrar el valor buscado nos
mostraría “N.D” en la celda C223.

La función BUSCARX, si se anida, nos permite encontrar datos en

la intersección de fila y columna. Veamos un ejemplo:

Queremos que en la fila 242 nos devuelva los datos del año elegido
y de los países elegidos. En C242 establecemos la fórmula y la copiamos
hacia la derecha. La BUSCARX más interna se encarga de localizar los
datos de la columna, los del año, según lo indicado en B242. La

61
 Fundamentos de Excel para Finanzas

BUSCARX externa, la primera que se escribe, localiza la fila concreta, el

país según lo indicado en la fila 241.

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
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.

8.7. La función CAMBIAR

Las últimas versiones de Excel han incorporado la función

CAMBIAR. Esta función permite escoger un valor a través de la
referencia a otra celda.

Tenemos tres escenarios, cada uno de ellos con un valor. Deseamos

que en la celda C129 nos de el valor del escenario indicado en C128. En

62
 Fundamentos de Excel para Finanzas

la función CAMBIAR en primer lugar le indicamos que en C18 está la

referencia, y luego por parejas vamos indicando las celdas con los
nombres de cada escenario y sus valores respectivos. Como en la
función ELEGIR, los rangos pueden ser no adyacentes.

8.7. Las funciones FILA y COLUMNA

La función FILA nos indica el número de fila de una determinada

celda de referencia. La función COLUMNA nos indica el número de
columna de una determinada celda de referencia.

8.7. La función INIDRECTO

La función INDIRECTO nos permite obtener acceso a una

determinada información de forma “indirecta”. Su uso no es “Excel
básico”. Vamos a ver un par de ejemplos. En el primero de ellos la
función INDIRECTO se apoya en una celda que contiene la dirección de
una celda para darnos el contenido de dicha celda.

Como vemos, en C175 ponemos la dirección de celda B179. En

C176, con la función INDIRECTO invocada sobre la celda C175
obtenemos el resultado de la celda B179. También tiene la opción de
que la dirección de celda se indique en modo F1C1 (en lugar del
habitual modo A1).

63
 Fundamentos de Excel para Finanzas

El segundo ejemplo es más elaborado. Usaremos la función

INDIRECTO invocando nombres.

Hemos definido los nombres “Nombre1” y “Nombre2” en los rangos

B189:B194 y C189:C192, respectivamente. En C186 indicamos uno de
ambos nombres. En E189con la función INDIRECTO invocamos la celda
C186 (que contiene el nombre elegido) y en dicha celda se reflejará el
primer valor del rango de dicho nombre y automáticamente se cubrirán
el resto de valores.

8.8. La función ORDENAR y ORDENARPOR

Las versiones de Excel más actuales incluyen las funciones

ORDENAR y ORDENARPOR, que simplifican mucho las tareas de
ordenación. Veamos ORDENAR.

Tenemos información de una serie de personas, con su edad y su

provincia de residencia. Queremos ordenar dicha información, por
ejemplo por edad, en sentido ascendente. En la celda F248
introducimos la función ORDENAR indicando en primer lugar la

64
 Fundamentos de Excel para Finanzas

referencia a toda la tabla de información, en segundo lugar, la columna

sobre la que queremos organizar el ordenamiento (en nuestro caso 2) y
por último con un 1 le indicamos que queremos orden ascendente (con -
1 le indicaríamos orden descendente). Pulsamos Enter y ya se monta
toda la tabla ordenada automáticamente. Excel insertará la misma
función en toda la zona sin necesidad de que le indiquemos nada. No
podemos borrar celdas de la nueva tabla de forma aislada pues, como
en la herramienta TABLA que veremos posteriormente, opera como un
bloque único. Igualmente podíamos haber ordenado por Nombre o
provincia (por orden alfabético).

Si queremos ordenar por varias columnas de forma sucesiva,

usamos ORDENARPOR. En el ejemplo a continuación ordenamos la
información de la tabla anterior primero por Provincia en orden
creciente y después por Nombre en orden decreciente.

En la celda B262 introducimos ORDENARPOR indicando en primer

lugar el rango de toda la tabla, a continuación indicamos el rango de la
primera columna de ordenación (la de la Provincia) seguido de “1”, pues

65
 Fundamentos de Excel para Finanzas

queremos orden ascendente en esa columna, después la segunda

columna de ordenación (la del Nombre) seguido de “-1” pues queremos
orden descendente en esa columna. Pulsamos Enter y la fórmula se
desbordará a todo el rango necesario para cubrir automáticamente toda
la información.

8.9. La función FILTRAR

Las versiones más recientes de Excel incorporan la función

FILTRAR. Permite filtrar la información de una matriz en función de que
se cumpla algún criterio. Veamos un ejemplo.

Queremos filtrar los registros o filas que sean de la provincia

seleccionada en F276 (Lugo). Nos situamos en F278 e insertamos
FILTRAR indicando en primer lugar el rango de la tabla de información,
a continuación la columna por la que queremos filtrar igualando a la
celda de filtrado (F276, Lugo). Como último argumento ponemos
comillas para que en caso de que no haya ningún resultado nos ponga
una fila en blanco. Pulsamos Enter y la fórmula se desbordará
automáticamente para mostrar todos los registros que cumplen el
criterio de que la provincia sea Lugo. El filtrado es de tipo Booleano, es
decir, incluye un determinado registro si cumple el criterio de
VERDADERO y no lo incluye si no lo cumple (FALSO).

Podemos filtrar con más de un criterio. Observe la siguiente figura.

Para ver si se cumplen dos criterio simultáneamente los multiplicamos,
de forma que si el resultado es VERDADERO será porque ambos
criterios lo son:

66
 Fundamentos de Excel para Finanzas

Queremos que filtre a las personas de nuestra lista que son de

Lugo y de menos de 30 años. En B296 insertamos la función FILTRAR
(que se desbordará a las celdas adyacentes hasta donde sea necesario)
indicando en primer lugar el rango de la tabla, y después multiplicando
el rango que contiene las provincias igualando a la celda C293 (Lugo)
por el rango de la columna de edades indicando “<” que la celda C294
(30 años). Cuando se cumplan ambos criterios será VERDADERO y ese
registro se incorporará al resultado.

Las funciones ORDENAR y FILTRAR pueden trabajar juntas. En el

siguiente ejemplo filtramos las personas de más de 40 años y las
ordenamos por orden alfabético de su nombre. En la celda B306
insertamos al función ORDENAR y dentro la función FILTRAR. Con
FILTRAR obtenemos los registros (filas) en las que la edad supera 40
(celda C304). Y después ordenamos por nombre (columna 1 de la tabla)
y en orden ascendente (1).

67
 Fundamentos de Excel para Finanzas

Excel ha incorporado otras funciones. Le puede interesar la función

UNICOS que devuelve los elementos de una tabla eliminado los
registros duplicados u, opcionalmente, registrándolos una sola vez.
Puede combinarse con la función ORDENAR.

9. Funciones de Texto

Este tipo de funciones nos permiten trabajar con los textos

incluidos en las celdas. Pueden ser de gran utilidad cuando tenemos
que extraer información de tablas de datos.

9.1. La función IZQUIERDA

Esta función nos permite extraer un determinado número de

caracteres de la izquierda de un texto. Por ejemplo, si queremos extraer
de un DNI sólo el número (sin la letra):

68
 Fundamentos de Excel para Finanzas

Le hemos indicado que de la celda C6, extraiga los 8 primeros

caracteres, que son los 8 números del DNI.

9.2. La función DERECHA

Esta función nos permite extraer un determinado número de

caracteres de la derecha de un texto.

Le hemos indicado que de la celda C12, extraiga los 4 últimos

caracteres, que son los 4 números.

9.3. Las funciones ENCONTRAR y HALLAR

Son funciones que permiten encontrar la posición de un

determinado elemento dentro de una cadena. HALLAR no distingue
mayúsculas y minúsculas, mientras que ENCONTRAR sí que lo hace.

En D18 y D19 hemos usado ambas funciones para encontrar el

guion (“-“) en la cadena de texto de C18.

En D24 y D25 usamos la función ENCONTRAR para localizar una

letra presente en el texto de C22, comprobando que distingue
mayúsculas de minúsculas.

69
 Fundamentos de Excel para Finanzas

En D24 y D25 usamos la función HALLAR para localizar una letra

presente en el texto de C22, comprobando que no distingue mayúsculas
de minúsculas.

9.4. La función EXTRAE

Esta función permite extraer un determinado número de caracteres

a partir del que se le indique dentro e una cadena.

En este caso hemos extraído 8 elementos empezando en el primero,

para obtener los números del DNI.

9.5. La función CONCAT

Esta función permite concatenar cadenas, información de dos

celdas, o más, en una sola.

En la primera variante simplemente hemos concatenado las tres

celdas. En la segunda hemos incluido un espacio entre los tres
elementos y en la tercera hemos puesto un guion.

9.6. La función UNIRCADENAS

Es una función similar a la anterior. Se le indica en primer lugar

cómo se van a unir las diferentes cadenas, luego se indica si se ignoran
(VERDADERO) o no (FALSO) LAS celdas en blanco.

70
 Fundamentos de Excel para Finanzas

9.7. La función ESPACIOS

Es una función que elimina los espacios innecesarios en un texto.

En el ejemplo deja sólo un espacio para separar cada elemento del
texto.

9.8. La función IGUAL

Es una función que permite comprobar si los textos de dos celdas

son iguales.

En el segundo ejemplo ambos textos no son iguales pues el texto de

la izquierda tiene más espacios en blanco entre los dos elementos que el
de la derecha.

9.9. La función LARGO

Es una función que indica el número de caracteres (incluidos

espacios en blanco) de una celda.

71
 Fundamentos de Excel para Finanzas

En este caso vemos como en el segundo ejemplo el resultado es 10,

5 números, cuatro letras y un espacio en blanco de separación.

9.10. La función MAYUSC

Es una función que convierte un texto a mayúsculas.

9.11. La función MINUSC

Es una función que convierte un texto a minúsculas.

9.12. La función NOMPROPIO

Esta función convierte a mayúsculas la primera letra de cada

palabra y minúsculas el resto.

10. Funciones financieras

En esta sección vamos a repasar con ejemplos las principales

funciones financieras que EXCEL tiene predefinidas.

72
 Fundamentos de Excel para Finanzas

10.1. La función [Link]

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.

10.2. La función [Link]

Esta función realiza la operación contraria a la función

[Link]; 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.

Como vemos, un 12,00% efectivo equivale a un 11,55% nominal

pagadero por cuatrimestres.

10.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.

73
 Fundamentos de Excel para Finanzas

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
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.

10.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.

74
 Fundamentos de Excel para Finanzas

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.

10.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.

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.

75
 Fundamentos de Excel para Finanzas

10.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.

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.

10.7. VNA

La función VNA permite conocer el Valor Actual de una serie de

cuantías no necesariamente constantes.

76
 Fundamentos de Excel para Finanzas

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.

10.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.

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.

10.9. Las funciones [Link] y [Link]

Nos permiten el cálculo del Valor Actual y de la Tasa de

rentabilidad cuando los periodos de tiempo entre cada par de datos no
son constantes, cuando las fechas en que se produce cada flujo
implican una distancia no homogénea entre ellos.

Como podemos observar hemos de indicarle el rango en el cual se

encuentran las fechas correspondientes a cada flujo (el número de
valores en el rango de flujos debe coincidir con el del rango de fechas).

77
 Fundamentos de Excel para Finanzas

10.10. La función TIRM

La función TIRM calcula la TIR Modificada, asumiendo una

determinada tasa de financiación y una determinada tasa de
reinversión.

Esta función actualizada los flujos negativos hasta el presente

usando la tasa de financiación y capitaliza los flujos positivos hasta el
final del horizonte temporal usando la tasa de reinversión.
Posteriormente busca la tasa de interés que iguala la cuantía situada en
el presente con la cuantía situada al final del horizonte temporal.

11. Ejemplos de funciones Array

Las funciones o más bien fórmulas array, o de tipo matricial, son

una herramienta muy potente. A veces pueden ayudarnos a resolver
situaciones complejas aunque hay que tener cuidado con su uso para
evitar cometer errores que pueden ser “de bulto”.

Veamos algunos ejemplos. El primero de ellos para resolver una

cuestión que ya tenemos resuelta con VNA, en este caso:

78
 Fundamentos de Excel para Finanzas

Tenemos una serie de valores en la fila 7 así como una serie de

factores de descuento en la fila 8. La suma actualizada de esos valores,
que podemos calcular con VNA, también podríamos calcularla con
SUMAPRODUCTO.

En D10 diseñamos nuestra propia función suma producto, tipo

array. Le indicamos que sume el rango D7.H7 multiplicado por el rango
D8:H8. Es preciso validar la operación con Ctrl + Shift + Enter. Esta
validación le pondrá corchetes a la fórmula. En las versiones más
recientes de Excel es suficiente una validación normal, aunque por si
acaso, no está de más realizar la validación matricial.

En D11 omitimos el paso de los factores de descuento de la fila 8.

Le indicamos que sume el rango D7:H7 tras haberlo dividido entre 1
más la tasa de descuento y elevado al rango de los periodos (D6.H6).

En el siguiente ejemplo usamos una fórmula array apoyada en la

función PROMEDIO.

Tenemos las ventas o ingresos en euros y las unidades vendidas en

un periodo de cinco años. Queremos calcular el promedio anual de
precios. Podríamos hacerlo sin usar fórmulas array. Es lo que hemos
hecho en la fila 21. Calculamos el precio de cada año y en I21
calculamos el promedio.

79
 Fundamentos de Excel para Finanzas

En D23 usamos una fórmula array para hacer el cálculo de forma

más directa. Le indicamos que haga el promedio del rango de división
de ventas entre el rango de unidades. Validamos con Crtl + Shift +
Enter para obtener el mismo resultado.

En nuestro tercer ejemplo realizamos una fórmula array que tiene

una parte “no array”. Veámoslo.

Tenemos cuatro productos y conocemos las cuantías de unidades

de venta, los precios y costes variables unitarios, así como los costes
fijos de toda la empresa. Deseamos calcular el resultado total. Si lo
hacemos sin recurrir a fórmulas array podemos calcular el resultado de
cada producto en la fila 32. Sumarlos en C33 y luego resta el coste fijo
para obtener el resultado en C36.

Si usamos la fórmula array le indicamos que sume el producto del

vector o rango de unidades por el vector de precio y le reste el producto
del vector de unidades por el vector de costes variables. Fuera de la
función SUMA restamos el coste fijo. Validamos con Crtl + Shift + Enter
para obtener el mismo resultado.

El último ejemplo de fórmula array no lo usaremos para obtener un

valor sino un rango de valores. Veámoslo.

80
 Fundamentos de Excel para Finanzas

Queremos calcular los resultados de cada producto. Podríamos

hacer la fórmula para el primer producto y copiarla hacia la derecha, a
los demás. O podemos marcar el rango D45:G45 e introducir la fórmula
array que se muestra en la imagen que trabaja con los rangos de datos
y validarla con Crtl + Shift + Enter. La fórmula pondrá en cada celda del
vector de resultado el correspondiente a cada columna (en nuestro caso
a cada producto). Una vez más recordamos que las versiones más
recientes de Excel no exigen esa forma de validación. Se marca el rango,
se establece la operación con los diferentes rangos y se valida. La
fórmula aparecerá en la primera celda del rango, tal cual y, difuminada,
en el resto del rango.

12. Uso de la herramienta TABLA

La Herramienta Tabla de Excel se encuentra integrada en el menú

de Datos, dentro del grupo de Herramientas de datos y concretamente
dentro del desplegable de Análisis de Hipótesis. Junto a ella se
encuentran las herramientas de administración de escenarios y
búsqueda de objetivo, que veremos posteriormente. Permite recalcular
una determinada fórmula con diferentes valores para un determinado
input o para dos inputs de forma simultánea. Empecemos con un
ejemplo en el que sólo variamos un input.

12.1. Tablas de un solo input, columna

En este caso queremos calcular cómo se ve afectado el Pago anual

que permite devolver 10.000 Euros durante 5 años al variar la tasa de
interés (inicialmente establecida en el 7%).

81
 Fundamentos de Excel para Finanzas

Comenzamos calculando en C6 el pago con los datos iniciales.

En el rango B12:B18 establecemos una serie de tipos de interés

que queremos chequear, nuestros inputs alternativos. En nuestro caso
desde el 1% al 7%. En la columna de la derecha y una fila arriba, en
C11, vinculamos la celda C6, donde hemos hecho el cálculo original y
que será nuestra cabecera de tabla, el output. A continuación
seleccionamos el rango B11:C18 y acudimos a la herramienta tabla en
el menú. Nos aparecerá una caja de diálogo como la siguiente:

Como tenemos los datos de las tasas de interés alternativas en una

columna, indicamos que la Celda de entrada (columna) es C5 y dejamos
en blanco la Celda de entrada (Fila). Pulsamos Aceptar y la tabla estará
realizada. En cada una de las celdas C12:C18 aparecerá escrito
=Tabla(;C5) entre corchetes. Dichas celdas no pueden eliminarse o
modificarse de forma individual. La Tabla se maneja como un todo.

Al lado de cada tasa de interés aparecerá el correspondiente valor

de la cuota. Una forma de comprobar que está bien realizada es ver que

82
 Fundamentos de Excel para Finanzas

el dato, en este caso, de la celda C18 (el referido al 7%), coincide con el
de la celda C11, referido al 7% original.

En el rango E11:F18 hemos replicado la misma operación sólo que

en este caso la celda F11, la cabecera de la tabla, la hemos formateado
con formato de número personalizado para que ponga el texto “Cuota” y
así la tabla quede con una mejor presentación.

12.2. Tablas de un solo input, fila

Las tablas pueden contener los inputs de la celda que varía en una
fila. Usemos nuestro ejemplo para ver el impacto de diferentes periodos
o vencimientos de la operación. El proceso es similar. En C23
conectamos la celda C6, la que contiene el resultado que queremos
analizar. A su derecha, una fila encima, establecemos los diferentes
años que queremos comprobar, desde 1 a 8. Marcamos el rango
C22:K23, y acudimos a la herramienta Tabla indicando que la Celda de
entrada (Fila) es C4, y dejamos en blanco la Celda de entrada
(columna). Pulsamos Aceptar y ya está. Podemos comprobar que está
bien observando que las celdas C23 y H23 contienen el mismo valor.

También en este caso hemos replicado la operación más abajo

tuneando, en C28, la cabecera de la Tabla, con el texto Cuota.

83
 Fundamentos de Excel para Finanzas

12.3. Tablas de dos inputs, fila y columna

También podemos realizar tablas de doble entrada, esto es, que

sean dos las variables que toman valores cambiantes de forma
simultánea. Juntemos los dos ejemplos anteriores en uno solo.

Como vemos la celda resultado se sitúa en el vértice superior

izquierda de la tabla. A su derecha se introduce el rango de variación de
la variable fila y por debajo el rango de variación de la variable columna.
Luego seleccionamos C34:K41 y seguimos el proceso de antes, pero
indicando ahora tanto la celda de entrada de fila como la de la columna.
Podemos comprobar que la celda H41 contiene el mismo valor que la
original, C34, pues en H41 confluyen la tasa y el horizonte originales.

Hemos replicado la tabla más abajo. En este caso la cabecera de la

Tabla, en C44, la hemos tuneado cambiando el color del número, de
forma que coincida con el color del fondo de la celda, y no se vea.

Las tablas suelen ocupar bastante capacidad de cálculo. En el

menú Fórmulas, en el submenú Cálculo, puede indicarse que las tablas
no se actualicen de forma automática, lo cual libera capacidad. Si

84
 Fundamentos de Excel para Finanzas

hacemos esto debemos acordarnos de actualizarlas manualmente

cuando queramos ver los valores correctos. Para refrescar la hoja de
cálculo pulse F9.

13. Herramienta Buscar objetivo

Las hojas de cálculo facilitan mucho los trabajos de simulación,

pues al modificar un input podemos inmediatamente observar su efecto
sobre cualquier resultado que de una u otra forma dependa de dicho
input. Pero a veces lo que deseamos es hacer el trabajo al revés.
Deseamos conocer qué input es preciso para obtener un ouput
determinado. Para eso podemos apoyarnos en esta herramienta.
Supongamos que tenemos acceso a una línea de financiación al 5%
anual y a 10 años y sólo podemos pagar 6.000 euros al año de cuota.
¿Cuál es el endeudamiento máximo que podemos afrontar?

Comenzamos por establecer la estructura de la operación:

Como vemos hemos puesto un valor de prueba en el apartado de

Capital, en nuestro caso 1.000 Euros.

A continuación nos dirigimos a Buscar objetivo en el grupo Análisis

de Hipótesis del Menú Datos, y rellenamos la caja de diálogo de la
siguiente forma:

85
 Fundamentos de Excel para Finanzas

Observar que en la caja “Definir la celda” situamos la celda que

contiene la fórmula para la cual tratamos de encontrar un objetivo, en
este caso C6, donde se calcula el Pago, en la caja “Con el valor”
introducimos el valor objetivo buscado, y en la caja “Cambiando la
celda” introducimos la celda que Excel deberá modificar tratando de
provocar que la celda objetivo alcance el valor buscado.

Una vez pulsado Aceptar, aparecerá lo siguiente, en este caso:

Nos informa de que ha encontrado una solución, en este caso

46.330, y que la celda objetivo ha alcanzado el valor objetivo.

La celda objetivo debe contener una fórmula, y la celda que se

modifica debe contener un valor. Evidentemente es preciso que la celda
a modificar y la celda objetivo estén vinculadas de alguna forma, y es
preciso que el problema tenga solución. De no ser así, Excel no podrá
encontrarla.

Esta herramienta es de gran utilidad cuando se buscan los puntos

de ruptura en un análisis de sensibilidad.

Como truco que evita introducir la cuantía exacta del objetivo a

través del menú, proponemos lo siguiente:

86
 Fundamentos de Excel para Finanzas

En la celda C8 hemos establecido el objetivo. En la celda C10

calculamos la diferencia entre el valor de la celda resultado (C6) y dicho
Objetivo. A continuación acudimos a Buscar objetivo y le indicamos que
queremos que la celda C10 valga cero cambiando la celda C3, esto es,
que queremos que la celda C6 coincida con el objetivo.

Siempre es más fácil indicar en el menú que queremos un valor de

cero en el objetivo que indicar cualquier otra cifra.

14. Escenarios

14.1. Uso del Administrador de Escenarios de Excel

Excel cuenta con una herramienta que permite diseñar y gestionar

escenarios alternativos en los cuales una serie de inputs pueden tomar
diferentes valores. En cualquier momento podremos activar y trabajar
con uno de dichos escenarios predefinidos. Veamos un ejemplo en el
que estableceremos diversos escenarios para nuestra operación de
préstamo.

87
 Fundamentos de Excel para Finanzas

Una vez establecido el modelo, acudimos a la herramienta

escenarios, dentro de Análisis de Hipótesis en el menú Datos y
pulsamos agregar. Nos aparecerá la siguiente caja de diálogo, en la cual
indicaremos el nombre del primer escenario, así como las celdas que
cambiarán en los escenarios. Recomendamos que el primer escenario
sea el que contiene la información actual. En el ejemplo le
denominamos Base.

Una vez que aceptemos nos aparecerá la siguiente caja de diálogo:

88
 Fundamentos de Excel para Finanzas

Los valores que por defecto nos ofrece son los que tiene en ese
momento la hoja. Dado que queremos que el primer escenario tenga los
valores base, dichos valores nos valen. Pulsamos Aceptar, o agregar e
introducimos los siguientes escenarios de igual modo, variando en las
celdas cambiantes los valores inputs según queramos. En nuestro caso
vamos a introducir dos escenarios más, a los que denominaremos
Optimista y Pesimista. Desde el administrador de escenarios podremos
modificar algún escenario, incluir otros nuevos, indicarle que nos
muestre uno de ellos, eliminarlos, etc.

Para ver toda la información resumida pulsamos Resumen:

Le indicaremos que en el resumen deseamos que aparezca la celda

C6 donde tenemos calculada la cuota. Excel generará una hoja nueva,
denominada Resumen de escenario que contendrá la siguiente
información:
89
 Fundamentos de Excel para Finanzas

Nos indicará los valores de cada celda cambiante en cada escenario

así como los valores de las celdas resultado indicadas (en nuestro caso
sólo una). Si hubiésemos definido nombres para las celdas cambiantes
y celdas resultado, en este resumen aparecerían dichos nombres, y no
la referencia de la celda, lo que sería más clarificador. Este resumen no
es dinámico, no cambia al cambiar algún input o alguna fórmula en el
modelo. Si hacemos cambios en el modelo y queremos ver cómo quedan
los escenarios habremos de volver al administrador de escenarios y
solicitar un nuevo resumen, actualizado. Esta falta de actualización
automática es el principal inconveniente que nosotros achacamos a esta
herramienta.

14.2. Elaboración de Escenarios. Un método alternativo

A modo de ejemplo, para ver las posibilidades que Excel confiere,

veamos cómo podríamos “crear” nuestro propio administrador de
escenarios. Nosotros le denominamos Tabla de Escenarios.

90
 Fundamentos de Excel para Finanzas

Como vemos, colocamos en G4:I6 la información de entrada de los

tres escenarios. En la columna C escogemos (lo hemos hecho con dos
condicionales, podríamos hacerlo con COINCIDIR combinada con
ELEGIR o DESREF) el escenario que se aplicará apoyándonos en la
celda C3 (que está validada para que se despliegue la lista con los
nombres de los escenarios). Los valores de C4:C6 serán los que se
aplicarán en el modelo y cuyos resultados se verán en C7. Los
resultados de todos los escenarios se muestran en las celdas G11:I11
aplicando la herramienta Tabla. En la Herramienta Tabla el elemento
que cambia es la fila, que es la que contiene el nombre de cada
escenario y que debe entrar en la celda C3. Esta tabla de información
de los diferentes escenarios está permanentemente actualizada.
Tenemos a la vista, en la propia hoja, los inputs de cada escenario, así
como los resultados de todos ellos de una forma muy dinámica y
cómoda de trabajar. Podemos ver cualquier escenario en cualquier
momento cambiando la celda C3. Podemos modificar los datos de
cualquier elemento de cualquier escenario en el rango G4:I6. Y el rango
G11:I11 seguirá en todo momento estando actualizado.

15. Solver

La herramienta Solver permite buscar objetivos (como la

herramienta Buscar objetivo) pero modificando más de una celda y
dadas las restricciones que el usuario considere oportunas. Solver es
un complemento de Excel que debe cargarse desde el menú
complementos dentro de las opciones de Excel en el menú Archivo. Una
vez cargado se sitúa en el menú Datos.

Vamos a elaborar un sencillo ejemplo. Una empresa puede

producir Mesas, Sillas y Puertas y consume una serie de unidades de
madera, maquinaria y mano de obra en cada una de ellas. Las unidades
de madera, maquinaria y mano de obra de la empresa son limitadas.
Por otro lado cada una de las mesas, sillas y puertas reporta a la
empresa un determinado margen. La cuestión aquí es decidir cuántas
mesas, sillas y puertas debe producir para conseguir el resultado más

91
 Fundamentos de Excel para Finanzas

alto posible teniendo en cuenta las limitaciones de recursos y la

demanda máxima de cada producto. La siguiente figura muestra la
información de partida.

Estamos ante un clásico problema de programación lineal. Una vez

dispuesta la información de partida. Establecemos el modelo.

En la fila 11 proponemos como primera aproximación producir, por

ejemplo, 10 unidades de cada producto. En la fila 12 calculamos la
diferencia entre las unidades producidas y la demanda, y le
denominamos Excedente. Como todos son negativos significa que no
producimos por encima de nuestra demanda. En la columna G, con
SUMAPRODUCTO calculamos el uso que nuestra producción está
realizando de nuestros recursos dados los consumos de cada uno de los
productos de dichos recursos. En la columna H calculamos la diferencia
entre la disponibilidad de los recursos y su uso. Todas las diferencias
son positivas, lo que implica que no estamos consumiendo más de los
recursos de los que disponemos. En C14 calculamos el resultado, con
SUMPARODUCTO dada la producción prevista y los márgenes de cada
producto.

92
 Fundamentos de Excel para Finanzas

Se trata de conseguir que el resultado de C14 sea el más alto

posible, cambiando las unidades de producción de la fila 11 sin
excedernos en el uso de los recursos limitados y sin que se produzca de
ningún artículo más de lo que se demanda. Acudimos a Solver. Como
muestra la figura, le indicamos que la celda objetivo es C14 y que
buscamos su máximo. Le indicamos que las celdas cambiantes son las
del rango C11:E11, donde se encuentran las cantidades a producir de
cada producto. A través del botón de Agregar añadimos cuatro
restricciones. Por un lado le indicamos que las cuantías de cada
producto que se producirán deben ser números enteros (no podemos
hacer media mesa). También le indicamos que los excedentes de cada
producto (C12:E12) deben ser menores o iguales a cero. No
produciremos por tanto más de los que se puede vender. En tercer
lugar, las diferencias en el uso de los recursos respecto a su
disponibilidad (H5:H7) deben ser mayores o iguales a cero. No podemos
consumir más recursos de los que tenemos. Por último, las cuantías de
producción deben ser positivas.

93
 Fundamentos de Excel para Finanzas

Escogemos como método de resolución Simplex LP, pues en

nuestro caso se trata de un problema de programación lineal, y una vez
que pulsemos Resolver Solver nos indica que encuentra la siguiente
solución:

Se consigue el resultado máximo. Se cubre totalmente la demanda

de puertas, se fabrica una silla menos de las demandadas y 23 mesas
menos de las demandadas. Por otro lado se agotan las unidades de
maquinaria y sobran algunas unidades de madera y de mano de obra.

Como vemos es una herramienta que ofrece grandes posibilidades

para resolver problemas de optimización. Su uso no es complicado pero
es necesario realizar una modelización previa adecuada que facilite la
incorporación de los objetivos y de las restricciones, tal como hemos
hecho en el ejemplo. Si ahora cambiamos algún input, simplemente
volvemos a Solver y le indicamos de nuevo que vuelva a resolver y nos
de la nueva solución.

16. Uso de referencias circulares

A veces al realizar una serie de operaciones podemos encontrarnos

con una referencia circular, un bucle. Una referencia circular es “una
pescadilla que se muerde la cola”. Se produce cuando un resultado es
input de una fórmula cuyo resultado a su vez es input del anterior, sea
esta conexión directa o indirecta. Excel, en principio no puede resolver
estas situaciones, y nos alerta con un mensaje de error como el
siguiente.

94
 Fundamentos de Excel para Finanzas

No obstante en las opciones del menú Archivo, en el submenú

fórmulas, podemos verificar la casilla de “Iteración” y entonces Excel
resolverá el bucle. El inconveniente es que si tenemos otros bucles no
deseados (asumiendo que el anterior si nos interese mantenerlo) Excel
ya no nos avisará de su presencia. Veamos un ejemplo:

En nuestro caso D8 es la suma de C8 y D12, pero a su vez D12

depende de C10 y ésta de D8. Tenemos un bucle. Si tratamos de hacer
esto Excel nos dará el mensaje de error que hemos visto y a
continuación nos mostrará lo siguiente:

95
 Fundamentos de Excel para Finanzas

De esta forma nos indica donde está el bucle, con las líneas azules.
Si creemos que ese bucle es necesario simplemente verificamos la
casilla de iteración, como hemos dicho, y Excel ya resolverá las dos
ecuaciones de forma simultánea, por prueba y error.

Por defecto, Excel no tiene verificada la casilla de iteración en sus

opciones, para que sea el usuario, se supone que avezado, el que lo
haga, asumiendo el riesgo de posibilitar otros bucles, como dijimos
antes, no deseados.

Si queremos conocer las referencias circulares existentes en una

hoja tenemos una opción de menú en la sección de Comprobación de
errores de la Auditoría de fórmulas.

17. Elaboración de gráficos

A veces una imagen vale más que mil números. Podemos apoyar
nuestros resultados diseñando gráficos que resuman la información
manejada. En este epígrafe veremos algunos fundamentos acerca de la
elaboración de gráficos en Excel. La variedad de gráficos que es posible
hacer es enorme, en este apartado veremos alguno de ellos, los que
nosotros usamos de forma más habitual.

17.1. Gráfico de líneas básico

Supongamos que tenemos una determinada cuantía de una

variable para una serie de años.

96
 Fundamentos de Excel para Finanzas

Deseamos hacer un gráfico tipo líneas que muestre la evolución de

la variable. Hay diferentes alternativas. Podemos por ejemplo ir al menú
Insertar y pulsar el icono de Gráfico tipo líneas, escogiendo Líneas con
marcadores.

A partir de ahí aparece el menú de gráficos con dos secciones:

Diseño y Formato.

Dentro de diseño tenemos el botón de seleccionar datos, que nos

llevará al siguiente menú:

En el botón agregar añadimos las series, en nuestro caso sólo una,

indicando el nombre de la serie desde C6, y los valores desde el rango
C7:C14.

97
 Fundamentos de Excel para Finanzas

Y a la derecha en Etiquetas del eje horizontal clicamos en Editar,

para indicarle que el eje X tendrá los rótulos vinculados al rango donde
están los años (B7:B14).

Ya tendremos nuestro gráfico. Ahora en el menú “Agregar elemento

de gráfico” podemos incorporar elementos, por ejemplo, podremos poner
títulos a los ejes y al propio gráfico. A este menú se puede acceder
directamente, una vez seleccionado el gráfico, pulsando en el signo “+”
que aparece a su derecha. También podremos indicarle donde queremos
que aparezcan las leyendas del gráfico, eliminar o no las líneas de
división, etc. Las posibilidades son casi ilimitadas. También podemos ir
al menú Formato del grupo de Herramientas de Gráfico y mejorar
mucho su aspecto. En nuestro caso, además, vinculamos el título del
gráfico al contenido de la celda B4. El resultado es el siguiente:

Gráfico de líneas Básico

160
150
140
Euros

130
120
110
100
2007 2008 2009 2010 2011 2012 2013 2014
Año

Variable

Podemos editar partes concretas del gráfico. Por ejemplo podemos

situarnos en el eje X y con el botón derecho del ratón modificar su

98
 Fundamentos de Excel para Finanzas

aspecto o su configuración. También podemos seleccionar el elemento y

pulsar en el icono de edición que aparecerá a la derecha del gráfico. Si
en este caso le indicamos en opciones el eje que la posición del mismo
es “En marcas de graduación” la serie arrancará desde el eje vertical.
También podemos indicarle a la serie que la línea se suavice para que
no marque tanto los ángulos de cambio de dirección. También podemos
seleccionar un punto concreto de la serie y editarlo, cambiando el
marcador, su color o tamaño. Nuestro consejo es que una vez que haya
realizado el gráfico “juegue” con las diferentes opciones para ver las
posibilidades.

También podemos iniciar el proceso seleccionando los datos y

acudiendo posteriormente a insertar gráfico, de forma que ya nos
ahorramos algún paso en el proceso. Excel no propondrá una
determinada configuración del gráfico y nosotros la podemos modificar
o ajustar según nuestras necesidades.

Añadir una nueva serie es muy fácil. Basta acudir a Seleccionar

Datos en la zona de Diseño e incorporar la nueva serie. O bien, de
forma más sencilla seleccionando la serie, le damos a copiar,
seleccionamos el gráfico y le damos a pegar.

17.2. Gráfico de columnas básico

En este caso vamos a hacer un gráfico con dos series, de tipo

columna. La información de partida es la siguiente:

99
 Fundamentos de Excel para Finanzas

Seleccionamos el rango C25:D33 y clicamos en insertar gráfico tipo

columna, en nuestro caso el primero, columna agrupada. Tendremos
que indicarle en Datos de Origen los rótulos del eje X. Si dichos rótulos
no hubieran sido cifras (en nuestro caso lo son, pues son años),
podríamos haber seleccionado también la columna B antes de insertar
el gráfico y ya hubiera absorbido esa información para el eje. Un truco
es no tener cabecera en la columna de rótulos, en nuestro caso eliminar
el texto “años” de B25, y de esta forma ·Excel ya entiende que esa
columna no es una serie del gráfico, sino que contiene las etiquetas del
eje X.

Realizamos las mismas cuestiones de rotulación que en el gráfico

anterior. En este gráfico entre las cuestiones interesantes está la
posibilidad de aumentar o disminuir el espacio entre las columnas así
como su superposición. Seleccione una de las series en el gráfico y en el
botón derecho del ratón pulse dar formato a serie de datos. En las
opciones de serie podrá controlar el ancho del intervalo y la
superposición.

También hay gráficos de columnas que apilan las series, ya sea con
sus valores o en relación al 100% del valor conjunto. Además, puede
usted forzar que si el dato es negativo la columna tenga un color
diferente. Le animamos a que investigue sobre ellos con nuestro
ejemplo.

100
 Fundamentos de Excel para Finanzas

17.3. Gráfico de columnas y líneas

En ocasiones no deseamos que todas las series del gráfico sean del
mismo tipo. Por ejemplo podemos querer que una serie sea en columnas
y otra en líneas.

Excel 2016 ya tiene su propio gráfico prediseñado para esta tarea.

Debemos acudir al icono de “Insertar Gráfico Combinado”. Nosotros
hemos escogido el primero de ellos, columna apilada y línea. Hemos
decidido, en esta ocasión, dejar la leyenda en la parte derecha del
gráfico.

17.4. Gráfico de columnas y líneas de doble eje vertical

A veces las variables que van a ir en el gráfico son muy diferentes

(por ejemplo una variable en Unidades monetarias y otra en porcentaje)
y debemos realizar el gráfico con dos ejes Y. Veamos un ejemplo:

Una vez hecho el gráfico con un solo eje Y, seleccionamos una de

las series y en dar formato a Serie de datos le indicamos en opciones de
serie que trace la serie en Eje secundario.

101
 Fundamentos de Excel para Finanzas

En el ejemplo observar que hemos cambiado el formato del eje Y de

la derecha, sin decimales, a pesar de que los datos de origen de la
variable 2 tienen dos decimales. Podemos realizar esta tarea en el menú
Número dentro del menú de Dar formato al eje, del botón secundario
del ratón si tenemos seleccionado dicho eje.

También podíamos haber hecho un gráfico tipo líneas (en las dos
series) con doble eje vertical. Pruebe a hacerlo.

17.5. Gráfico de áreas

Otro tipo de gráfico es el de áreas. Es un gráfico interesante por

ejemplo para ver la evolución de dos variables cuando una es, de alguna
forma, parte de la otra (por ejemplo la evolución de los ingresos y los
beneficios de una empresa). Veamos un ejemplo en el visualizamos la
evolución de los ingresos y los costes de una empresa para una serie de
años.

También en este caso podemos apilar, como en las columnas o dar

formatos que simulen 3D.

17.6. Gráfico de barras

Otro gráfico interesante es el gráfico de barras horizontales.

Veamos nuestro ejemplo.

102
 Fundamentos de Excel para Finanzas

El matiz que hemos incluido en esta ocasión es el que en cada

barra figure el valor. Para ello una vez hecho el gráfico seleccionar la
serie y en el botón derecho del ratón clicar Agregar etiqueta de datos. Le
animamos a que clique en la etiqueta de datos y la edite para ver las
opciones que están a su disposición.

17.7. Gráfico de barras tipo tornado

El denominado gráfico de tornado se establece a partir de un

gráfico de barras al que se le hacen una serie de modificaciones. Es un
gráfico útil para mostrar los resultados de cualquier análisis de
sensibilidad. Veamos un ejemplo. Tenemos un determinado modelo
cuyo resultado es 400.000. A partir de ahí analizamos el impacto de que
diferentes variables que influyen en el modelo suban o bajen y
obtenemos los siguientes datos.

Para realizar el gráfico seleccionamos toda el área, la columna de

nombres de variables incluida y la serie con la cabecera de Baja y Sube

103
 Fundamentos de Excel para Finanzas

también, e insertamos un gráfico tipo barras. El resultado será el

siguiente:

Para convertirlo en un gráfico de tornado realizamos los siguientes

pasos. Seleccionamos el eje vertical y le indicamos categorías en orden
inverso en las opciones del eje. Además le indicamos que las Etiquetas
del eje irán en posición Bajo. Ahora seleccionamos el eje horizontal y le
indicamos que el eje vertical cruza en ”Valor del eje” 400.000, que era
nuestro resultado base. A continuación seleccionamos una de las series
y le indicamos que van superpuestas, para que aparezcan a la misma
altura. Po último, añadimos un título. Y ya está. Si cambiamos los
datos recordar que hay que cambiar el punto de cruce de un eje con el
otro.

17.8. Gráfico de dispersión

El siguiente gráfico que vamos a ver es el de dispersión. Es muy

útil, por ejemplo, para visualizar las rentabilidades de dos acciones, o
de una acción y un índice bursátil. En nuestro ejemplo contamos con
las rentabilidades durante ocho años del Ibex y de la Acción X. El
gráfico de dispersión nos permitirá ver cómo se relacionan ambas
rentabilidades.

104
 Fundamentos de Excel para Finanzas

Seleccionamos el rango de las dos rentabilidades y acudimos a

insertar gráfico, en este caso de dispersión, sólo con marcadores. El eje
vertical será el correspondiente a la serie de la derecha (la acción X) y el
eje horizontal será el correspondiente a la serie de la izquierda (el Ibex).
En nuestro caso, hemos vinculado los rótulos de los ejes a las celdas
que contienen los textos Ibex y Acción X para que se actualicen si se
cambian sus nombres. En algunos casos podemos desear incluir
etiquetas en cada punto de dispersión, y que cada punto se refiera a
una categoría (en nuestro caso el año). Para hacerlo de forma sencilla
debemos añadir las etiquetas y en las opciones de las mismas
seleccionar “valor de las etiquetas” y enlazar el rango con los años.

17.9. Gráfico circular

El último tipo de gráfico que vamos a ver es el circular, o gráfico

tipo tarta. En nuestro caso tenemos las ventas de gamusinos en seis
ciudades gallegas.

105
 Fundamentos de Excel para Finanzas

Marcamos la zona, tanto los nombres de las ciudades como las

cuantías y acudimos a insertar gráfico, en este caso el circular más
sencillo. En nuestro caso le hemos indicado que agregue la etiqueta con
los datos.

En la siguiente variante le indicamos que desglose los tres últimos

datos (los que se corresponden con ciudades de la provincia de la
Coruña, en una barra adjunta).

Para ello debemos seleccionar el gráfico circular con subgráfico de

barras. En Opciones de serie le indicamos que el segundo trazado
contiene los últimos tres valores, seleccionados por posición. Además, le
indicamos que añada la etiqueta de datos y en el formato de la etiqueta
le indicamos que queremos que muestre el nombre de la categoría (de la
ciudad). Para completarlo quizás el último tuneado pueda ser sustituir
el “otros” del gráfico circular por el texto “Coruña Provincia”. Para ello
editamos directamente esa etiqueta.

106
 Fundamentos de Excel para Finanzas

Este gráfico permite que el desglose no sea con una columna sino
con otro gráfico circular. Trate de hacerlo.

17.10. Gráfico con rango dinámico

Como último ejemplo vamos a ver cómo realizar un gráfico que

incorpore series con un rango dinámico, es decir, que podamos
indicarle cuantos valores de la serie se muestran en el gráfico.
Contamos con la siguiente información:

Nuestro propósito es poder elegir en la celda D97 cuántos años

queremos mostrar en el gráfico. Para conseguirlo vamos a crear tres
nombres, el nombre Cabecera, el nombre Serie1 y el nombre Serie2. A
continuación vemos como hemos creado el nombre Serie1.

107
 Fundamentos de Excel para Finanzas

Hemos usado DESREF. Además de permitir encontrar una celda

concreta DESREF permite seleccionar un rango, sea vector (como en
nuestro caso) o sea matriz. Sobre la posición C199, donde se encuentra
el texto Serie1, le indicamos que queremos empezar una fila más abajo
(1), en la misma columna (0) y darle la altura de filas indicada en D197
y anchura de una columna (1).

De igual forma se crearían los nombres Serie2 y Cabecera,

apoyándonos con DESREF en D199 y B199 respectivamente. Se podría
haber usado también la función INDICE.

A continuación, hacemos el grafico con todos los datos, con el

rango B199:D209. Por último modificamos, en el menú de seleccionar
datos, tanto el origen de las series como el eje X, tal como se muestra a
continuación, sustituyendo el rango de datos original por el nombre
correspondiente que hemos creado anteriormente.

También se puede seleccionar la serie en el gráfico y cambiar en la

barra de fórmulas el rango original por el nombre que hemos otorgado,
cuidando de mantener en nombre de la hoja.

108
 Fundamentos de Excel para Finanzas

El resultado final es el siguiente:

17.11. Gráficos más elaborados

En el fichero XLS de Fundamentos IV se han incluido varias hojas

más con gráficos. Ha continuación vamos a mostrar, solamente los
gráficos de las mismas. Le invitamos a que investigue dichas hojas.

En la primera de ellas, Gráficos 2, se muestran diferentes

alternativas para ilustrar con gráficos datos referidos a orígenes y
aplicaciones de fondos, o cuestiones similares. Las siguientes figuras
muestran diferentes ejemplos con los mismos datos de partida.

Gráfico de barras

Aplicación 70 120 160

Origen 100 200 50

0 50 100 150 200 250 300 350 400

Origen 1 Origen 2 Origen 3 Aplicación 1 Aplicación 2 Aplicación 3

109
 Fundamentos de Excel para Finanzas

Gráfico de anillos

70
50
100
160

200 120

Origen 1 Origen 2 Origen 3 Aplicación 1 Aplicación 2 Aplicación 3

Gráfico de columnas
400
350
50
300
160
250
200 200
150
120
100

50 100
70
0
Origen Aplicación

Origen 1 Origen 2 Origen 3

Aplicación 1 Aplicación 2 Aplicación 3

Gráfico circular

100
160

200
120

70 50

Origen 1 Origen 2 Origen 3 Aplicación 1 Aplicación 2 Aplicación 3

110
 Fundamentos de Excel para Finanzas

Gráfico de barra centrado

-160 -120 -70 100 200 50

-400 -300 -200 -100 0 100 200 300 400

Aplicación 1 Aplicación 2 Aplicación 3 Origen 1 Origen 2 Origen 3

111
 Fundamentos de Excel para Finanzas

Gráfico de barras hélice

Aplicación 3 160

Aplicación 2 120

Aplicación 1 70

Origen 3 50

Origen 2 200

Origen 1 100

0 50 100 150 200

112
 Fundamentos de Excel para Finanzas

En la segunda hoja, Gráficos 3, mostramos diferentes sistemas

para marcar puntos de inflexión (por ejemplo, el paso de datos
históricos a estimados) en los gráficos.

Gráfico de dispersión con punto de corte

2.000
1.800
1.600
1.400
1.200
1.000
800
600
400
200
0
1998 2000 2002 2004 2006 2008 2010 2012 2014 2016 2018 2020

Gráfico de líneas con punto de corte

2.000
1.800
1.600
1.400
1.200
1.000
800
600
400
200
0
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018

Gráfico de columnas con punto de corte

1.600

1.400

1.200

1.000

800

600

400

200

0
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018

113
 Fundamentos de Excel para Finanzas

Gráfico de áreas con punto de corte

1.600

1.400

1.200

1.000

800

600

400

200

0
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018

De forma similar se puede hacer, por ejemplo, un gráfico de

columnas que muestre en columnas con colores diferentes los datos en
función del valor que tengan. En el siguiente ejemplo marcamos con
colores diferentes los valores que disten más de media desviación típica
por arriba, por abajo, o en el medio, respecto al promedio de los datos.

Gráfico de columnas con colores según valores

8,0%
7,0%
6,0%
5,0%
4,0%
3,0%
2,0%
1,0%
0,0%
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018

Crec. Bajo Crec. Medio Crec. Alto

114
 Fundamentos de Excel para Finanzas

En la hoja de Gráficos 4 mostramos sendos gráficos (uno de líneas

y otro de columnas) en los que se identifica automáticamente el valor
más alto y el más bajo de la serie.

Gráfico con etiquetas de valores más altos y más bajos

1.800 1.600
1.600
1.400
1.200
Ingresos

1.000
800
900
600
400
200
0
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
Año

Gráfico con etiquetas de valores más altos y más bajos

1.800 1.600
1.600
1.400
1.200
Ingresos

1.000 900

800
600
400
200
0
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018

Año

En la hoja de Gráficos 5 mostramos un gráfico combinado que

permite mostrar la evolución anual y la media de diferentes variables.

Evolución anual y media de Dos Variables

12% 7%
7,00%
7%
10% Variable B
7%
8% 7%
7%
Anual

6%
Variable A 7%
6,75%
4% 7%
7%
2%
7%
0% 7%
2015 2016 2017 2018 Media Variable A Media Variable B

115
 Fundamentos de Excel para Finanzas

Evolución anual y media de Tres Variables

14% 14%
12,10%
12% Variable X 12%

10% 10%
8,50% 8,50%
Variable Z
8% 8%
Anual

6% Variable Y 6%

4% 4%

2% 2%

0% 0%
2015 2016 2017 2018 Media Variable X Media Variable Y Media Variable Z

Evolución anual y media de Cuatro Variables

9% 7%
Variable 2 6,25%
8%
6%
7%
Variable 3 5%
4,50%
6% 5,50% 4,25%
5% Variable 1 4%
Anual

4% 3%
3%
2%
2% Variable 4
1%
1%
0% 0%
2015 2016 2017 2018 Media Variable 1 Media Variable 2 Media Variable 3 Media Variable 4

En la hoja Gráficos 6 incluimos un gráfico que tiene doble eje

vertical (ya visto en los gráficos básicos) pero que incorpora además un
doble eje horizontal.

Distribución normal
11 13 14 16 17 19 20 22 23 25 26 28 29
14% 120

12% 100
10%
80
8%
60
6%
40
4%

2% 20

0% 0
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

En la hoja Gráficos 7 incluimos un gráfico que tiene la

particularidad de mostrar dos datos muy diferentes para cada categoría
en un eje horizontal.

116
 Fundamentos de Excel para Finanzas

Unidades Precio

80 Vendedor 1 14

90 Vendedor 2 13

80 Vendedor 3 14

70 Vendedor 4 15

67 Vendedor 5 16

77,4 Media 14,3

En la hoja Gráficos 8 incluimos un gráfico de dispersión tipo

burbuja, de dos series, en el cual el tamaño de la burbuja depende de
una tercera serie, en el que además le hemos indicado que cambie los
colores entre los diferentes puntos.

Gráfico de burbujas
25%
Empresa 1
20% Empresa 2
Rentabilidad

15% Empresa 4 Empresa 3

Empresa 5

10%

5%

0%
0% 1% 2% 3% 4% 5% 6% 7%
Crecimiento

En la hoja Gráficos 9 elaboramos un simple gráfico de columnas

que con un poco de “tueno” ilustra de una forma muy evidente el nivel
de cumplimiento en un determinado objetivo

Gráfico de cumplimiento de objetivos

Enero Febrero Marzo Abril Mayo Junio Julio Agosto Septiembre Octubre Noviembre Diciembre

Objetivo Real

117
 Fundamentos de Excel para Finanzas

En la hoja Gráficos 10 elaboramos gráfico de barras que permite

elaborar un sencillo cronograma de tareas encadenadas. El eje
horizontal debe modificarse para que empiece en la fecha de inicio de la
primera tarea.

Gráfico de cronograma
01/01/2020 16/01/2020 31/01/2020 15/02/2020 01/03/2020 16/03/2020 31/03/2020 15/04/2020 30/04/2020

Tarea 1 10

Tarea 2 12

Tarea 3 10

Tarea 4 20

Tarea 5 15

Tarea 6 12

Tarea 7 14

Tarea 8 20

18. Una macro sencilla

Aunque no es nuestro propósito adentrarnos en las procelosas

aguas de la programación, queremos mostrar un par de pinceladas
acerca de macros y funciones personalizadas. Son cuestiones que
entrarían en un uso más avanzado de Excel del que se plantea en este
documento. Dedicaremos no obstante tres apartados a estas cuestiones.
En este punto veremos un ejemplo de una macro muy sencilla. En el
siguiente apartado repasaremos algunos ejemplos de funciones
personalizadas, y por último nos adentraremos en el uso de macros
para trabajar con buscar objetivo, dada la gran utilidad que creemos
puede tener en algunos casos.

En cuanto a las macros podríamos “definirlas” como rutinas

pregrabadas. Esto es, grabamos una tarea para que más tarde podamos
volver a realizar idéntica rutina.

Por ejemplo, supongamos que tenemos la siguiente tabla de

información

118
 Fundamentos de Excel para Finanzas

Deseamos hacer un gráfico, de forma que podamos actualizarlo

rápidamente al cambiar la información. En realidad podríamos hacer el
gráfico y cuando cambiásemos los datos el gráfico se actualizaría. Pero
en nuestro caso no deseamos que el gráfico esté presente salvo que lo
“invoquemos”. Usaremos una macro. Para ello, antes de empezar a
hacer el gráfico vamos al menú Programador y pulsamos en Grabar
Macro y le damos un nombre (en nuestro caso le llamaremos Graf). A
partir de ahí lo que hagamos hasta pulsar el botón de detener
grabación, quedará grabado. Procedemos a hacer el gráfico y pulsamos
detener grabación. Y ya está. En este momento nuestra hoja está así:

Seleccionamos el gráfico y lo borramos. Ahora para rehacer el

gráfico tal como lo hemos diseñado basta ir al menú de Programador, al
botón de Macros. Como podemos observar aparece la lista de las

119
 Fundamentos de Excel para Finanzas

macros que tenemos grabadas en esta hoja. Seleccionamos la macro

Graf y le damos a Ejecutar, y el gráfico aparecerá de nuevo; la rutina
que grabamos se repetirá.

En general, las macros pueden ser muy útiles para repetir tareas
repetitivas y que no resolvamos de otra forma porque no podemos o
porque no nos interesa.

Para facilitar la ejecución de una macro, Excel permite asignarle

una tecla que en conjunción con la tecla Control permite su realización
inmediata. A nosotros nos parece más interesante asignarle un botón
en la propia hoja. Veamos una forma sencilla de hacerlo. Simplemente
hagamos un cuadrado con la herramienta de dibujo y añadámosle el
texto “Hacer Gráfico”. Con el botón secundario del ratón le indicamos
que queremos asignarle una macro, y le asignamos la macro “Graf”. A
partir de ahora cada vez que pulsemos este “botón” la macro se
ejecutará.

19. Funciones personalizadas

Excel tiene multitud de funciones predefinidas, pero quizás no

tenga una para realizar la operación que a nosotros nos interese. Es
muy posible que podamos resolverlo en una fórmula, pero podemos
preferir diseñar una función que podamos invocar cada vez que
tengamos que realizar un cálculo similar.

19.1. Una función para calcular los impuestos

Podemos desear calcular los impuestos de una empresa teniendo

en cuenta la existencia de diferentes tramos impositivos. Con varias
condicionales anidadas podríamos resolverlo, pero hagamos una
función que automatice el proceso.

La función hemos de escribirla en VBA. Pulsando ALT F11

entramos en la herramienta de VBA de Excel.

En el menú Insertar indicaremos Insertar Módulo. En la ventana

que se abrirá procedemos a escribir nuestra función. En este caso
escribiremos:

120
 Fundamentos de Excel para Finanzas

Function Impuestos(beneficio, basereducida, tasared, tasa)

If (beneficio < 0) Then Impuestos = 0

If (beneficio <= basereducida) And (beneficio > 0) Then Impuestos =

beneficio * tasared

If (beneficio > basereducida) Then Impuestos = (basereducida *

tasared) + (beneficio - basereducida) * tasa

End Function Iniciamos con la expresión Function para indicar que

estamos definiendo una Función. A continuación ponemos el nombre
con el que dicha función se invocará (en nuestro caso Impuestos), y
entre paréntesis los parámetros o argumentos de dicha función. En
nuestro caso necesitaremos cuatro argumentos:

beneficio: la base imponible del año en curso.

basereducida: el tramo de aplicación de la tasa impositiva

reducida.

tasared: la tasa impositiva reducida

tasa: la tasa impositiva general

Y a continuación le indicamos como debe operar la función:

Si la base imponible es menor que cero, la función debe devolver el

valor cero.

Si la base imponible es menor que el tramo de aplicación de la tasa

impositiva reducida, y mayor que cero, la función debe devolver el
producto de la base imponible por la tasa impositiva reducida.

Si la base imponible es mayor que el tramo de aplicación de la tasa

impositiva reducida, entonces la función impuestos debe devolver el
producto de la tasa impositiva reducida por el tramo de aplicación más
la tasa impositiva general por el diferencia entre la base imponible y el
tramo de aplicación de la tasa reducida.

Concluimos con End Function para indicar que se ha acabado la

definición de la función. Y quedará como sigue:

121
 Fundamentos de Excel para Finanzas

Procedemos a guardar y ahora podremos comprobar en nuestra

hoja de cálculo que en Insertar función, en la categoría de “Definidas
por el Usuario” aparece la nueva función, que podremos aplicar
inmediatamente, debiendo respetar el orden de entrada de los
parámetros tal como han sido definidos en las operaciones.

Si queremos que en insertar función aparezca un poco de ayuda

podemos ir a Macro en el menú herramientas y escribir el nombre de
nuestra función y una vez hecho esto pulsar opciones, lo que nos
permitirá escribir un texto que aparecerá después cuando indiquemos
esta función en el menú insertar:

Veamos cómo usar nuestra función.

En C3:C6 situamos los parámetros fijos (tramo reducido, tasa

reducida y tasa normal). En la fila 10 ponemos diferentes valores de la
base imponible y en la fila 11, comenzando con la celda C11, escribimos
nuestra función. El único parámetro que no bloqueamos es el de la base

122
 Fundamentos de Excel para Finanzas

imponible, el primero, para que al copiar la función hacia la derecha

aplique los mismos criterios a las bases imponibles el resto de años.

En la inmensa mayoría de las circunstancias usted no necesitará

diseñar sus propias funciones, pero bueno, nunca está de más saber
que podría usted hacerlo, aunque VBA es un mundo aparte.

19.2. Función para indicar el nombre de la hoja

Las funciones que se muestran a continuación están en el fichero

“Fichero Funciones de Usuario”.

La primera función que planteamos nos permitirá indicar desde

una celda el nombre la hoja. Es la función Ponhoja. Dicha función
requiere un único argumento, una celda de la hoja de cálculo. En el
siguiente ejemplo en la celda C2, y referenciando la celda A1 (o
cualquier otra) se indica el nombre la hoja.

123
 Fundamentos de Excel para Finanzas

El código de la función es el siguiente:

19.3. Funciones para mostrar la fórmula de una celda

En este caso se trata de un grupo de funciones que nos permiten

indicar desde una celda cuál es la fórmula de otra celda. Las últimas
versiones de Excel cuentan con una función para esta tarea,
FORMULATEXTO. En el ejemplo que mostramos a continuación vemos
funciones de usuario que realizan diferentes variantes.

Queremos ver el contenido de la celda D6. En E8 vemos cómo nos

lo muestra la función de Excel, FORMULATEXTO. En E7 vemos el
mismo resultado con la función de usuario PonFormula. También
vemos las alternativas de indicar que la fórmula se refiere a la celda de
abajo, arriba, la izquierda o la derecha.

Además, en C11 y C12 mostramos otras dos variantes que además

de indicarnos el contenido de la celda nos indican a qué celda se refiere.

El código de estas funciones de usuario es el siguiente.

124
 Fundamentos de Excel para Finanzas

En todos los casos el único argumento que necesitan estas

funciones es la celda que se desea mostrar.

19.4. Función para calcular el VAN

Esta función, VAN, permite calcular el VAN incluyendo en el rango

de flujos el desembolso.

Como sabemos la función VAN calcula el Valor Actual y es preciso

agregarle (restarle) posteriormente el desembolso para calcular el VAN.
La función de usuario VAN corrige esta cuestión. Su código es el
siguiente:

125
 Fundamentos de Excel para Finanzas

Esta función requiere dos argumentos, la tasa de descuento y el

rango de todos los flujos (incluido el desembolso).

19.5. Función para calcular el VAN con ajuste de medio año

Esta función, VANMedioAño, realiza el denominado ajuste de medio

año en el cálculo del VAN. Mostramos un cálculo alternativo para
comprobar que la función de usuario opera correctamente.

El código de la función es el siguiente:

Esta función requiere dos argumentos, la tasa de descuento y el

rango de todos los flujos (incluido el desembolso).

19.6. Función para calcular el IRB e IRN

Las funciones IRB e IRN nos permiten determinar el Índice de

Rentabilidad Bruto y el Índice de Rentabilidad Neto. Ambas funciones
requieren como argumentos la tasa de descuento y el rango de todos los
flujos (incluido el desembolso). Veamos un ejemplo:

126
 Fundamentos de Excel para Finanzas

El código de ambas funciones es:

19.7. Funciones para calcular el VAN con tasa de descuento variable

Las funciones VANTasaVariable y VANTasaVariableAcum nos

permiten determinar el VAN cuando la tasa de descuento es variable,
independiente para cada año, o bien es variable acumulativa.

127
 Fundamentos de Excel para Finanzas

En nuestro ejemplo mostramos las fórmulas financieras de cada

caso, así como alternativas de cálculo para comprobar la corrección de
la alternativa propuesta. Ambas fórmulas requieren como argumentos
el rango de tasas y el rango de flujos (incluido el desembolso). Sus
códigos son:

19.8. Función para calcular el VAN Modificado

Excel cuenta con una función para calcular la TIR Modificada, pero
no cuenta con una función para calcular el VAN Modificado. Nuestra
propuesta es la función VANModificado. Esta función requiere que se le
indique la tasa de descuento, la tasa de reinversión y el rango de todos
los flujos (incluido el desembolso).

En el ejemplo mostramos un cálculo alternativo para poder

comprobar la corrección de la función propuesta.

128
 Fundamentos de Excel para Finanzas

19.9. Función para calcular la TIR No Periódica Modificada.

Excel cuenta con una función para la TIR (TIR), otra función para
la TIR modificada (TIRM) y otra función para la TIR no periódica
([Link]). La función de usuario TIRMNOPER nos permite calcular
la TIR modificada no periódica.

Requiere como argumentos el rango de flujos, el rango de fechas, la

tasa de descuento y la tasa de reinversión.

19.10. Función para calcular el valor de un bono con una tasa de interés
para la valoración variable

Esta función (ValorBono) permite calcular el valor de un bono

asumiendo que la estructura de tipos de interés no es plana, es decir,
que la tasa de interés aplicable en la valoración del flujo de cada año es
diferente. Requiere como argumentos el nominal del bono, la tasa de

129
 Fundamentos de Excel para Finanzas

cupón anual, el rango de tasas de interés para los diferentes años y la

prima de reembolso, si la hubiera.

En cuanto a su código:

19.11. Función para calcular el valor de una renta con crecimiento

constante durante un periodo finito

Un cálculo que a veces es muy necesario es el del valor actual de

una renta con crecimiento constante durante un horizonte limitado. La
función ValorUnCrecimientoFinito se encarga de dicha tarea.

Tenga en cuenta que el primer flujo a valorar en el ejemplo sería el

del año 1, que será el del momento cero con un crecimiento del 6%. El

130
 Fundamentos de Excel para Finanzas

último flujo a valorar sería el del año 5, el del momento cero con un
crecimiento del 6% durante cinco años.

Los argumentos de la función son la tasa de descuento, el flujo

previo, el porcentaje de crecimiento y la duración de la renta.

19.12. Función para calcular el valor de una renta con un crecimiento

constante durante un periodo finito seguida de un crecimiento constante
ilimitado

La función ValorDosCrecimientos resuelve el cálculo del valor de

una renta que tiene un crecimiento determinado durante un número de
años y posteriormente tiene otro crecimiento ilimitado en el tiempo.

Los argumentos de la función son la tasa de descuento, el flujo

previo, el crecimiento inicial, la duración del crecimiento inicial y el
crecimiento residual.

19.13. Función para calcular el plazo de recuperación

La función PlazoRecuperacion nos permite calcular el plazo de

recuperación de un proyecto. El argumento necesario es el rango de
flujos, incluido el desembolso. Como argumento opcional podemos
añadir la tasa de descuento y de esta forma calculará el plazo de

131
 Fundamentos de Excel para Finanzas

recuperación descontado. En ambos casos calcula el plazo que podemos

denominar “exacto”. Si se quiere se puede redondear, al alza, para que
el resultado sea un número entero de años.

En cuanto a su código.

20. Uso de macros con buscar objetivo y solver

Aunque ya comentamos que en este documento no

profundizaremos en el tratamiento de las macros, si hemos considerado
oportuno incluir en este apartado una serie de ejemplos que
consideramos de gran utilidad. En esta sección vamos a mostrar cómo
se puede automatizar el uso de la herramienta Buscar objetivo e incluso
también el uso de Solver a través de macros VBA. Aquí mostraremos
ejemplos sencillos en los que el código está personalizado con el ejemplo
para poder seguirlo mejor. Además de las macros básicas que
describiremos a continuación, en el fichero de Fundamentos V hemos
dejado diferentes variantes que quizás le interese explorar.

132
 Fundamentos de Excel para Finanzas

Por último, en el fichero Funciones de Usuario, hemos dejado

varias macros que se pueden usar de forma general e introduciendo los
diferentes campos a través de un formulario. Dichas macros se
encuentran en el módulo de VBA de dicho fichero titulado
BuscarObjetivo y en los correspondientes formularios.

20.1. Una macro sencilla para usar Buscar Objetivo

La herramienta Buscar objetivo requiere ser “recargada” cada vez

que quiere utilizarse, lo que a veces es un engorro. Si vamos a usarla de
forma reiterada solicitando siempre el mismo tipo de análisis (se supone
que con inputs diferentes) podemos automatizar su uso. En la siguiente
figura recogemos nuestro ejemplo. Deseamos usar Buscar objetivo para
determinar el número de unidades (el input) que, dado unos precios y
costes, consigue un determinado BDT Objetivo (Objetivo). Una vez
establecido el modelo de cálculo de ingresos y costes realizamos la
macro cuyo texto puede observarse en el recuadro de la figura.
Posteriormente asignamos dicha macro al recuadro (Pulsar), para
facilitar su utilización.

Nuestra macro se denomina buscar objetivo (Sub buscarobjetivo) e

invoca a GoalSeek, estableciendo como celda resultado el BDT, como
valor objetivo el indicado en BDT Objetivo y como celda cambiante o
input el número de unidades. Si cambiamos el BDT objetivo o alguno de
los otros inputs del modelo basta volver a pulsar en el recuadro para
que la macro se ejecute de nuevo y se obtenga el nuevo valor necesario
para conseguir el objetivo.

133
 Fundamentos de Excel para Finanzas

20.2. Una macro para usar Buscar Objetivo con una tabla en columna

Supongamos que deseamos usar Buscar objetivo de forma

reiterada cambiando un input del modelo. En nuestro ejemplo anterior,
cambiando el precio de referencia. Deseamos conocer el número de
unidades necesarias para conseguir un determinado BDT, para un
rango de precios determinado. Para ello deberíamos ir cambiando el
precio, invocar buscar objetivo, apuntar el resultado, y vuelta a
empezar. Podemos automatizar dicha tarea dentro de un bucle. Como
se puede observar en la siguiente figura, el modelo básico es el mismo
del ejemplo anterior. En el rango columna B18:B26 hemos establecido
los precios sobre los que queremos realizar el análisis. Hemos
denominado ha dicho rango tamacolumna. Deseamos que el número de
unidades necesarias para obtener el objetivo correspondiente a cada
precio se sitúen justo a su derecha en el rango C18:C26 que hemos
denominado columnaresultado. En la macro, que puede verse en el
recuadro, comenzamos limpiando los contenidos que tenga la columna
resultado:

Range("columnaresultado").ClearContents

y contando los valores de la columna en la que tenemos los precios,

es decir el número de filas, que asignamos a la variable num_row:

num_row = Range("tamacolumna").Count

134
 Fundamentos de Excel para Finanzas

Posteriormente realizamos un bucle con la estructura:

For Row = 1 To num_row

Next Row

en el cual, en primer lugar, la macro va asignando a la variable

precio cada uno de los precios de la columna:

Range("Precio") = Range("tamacolumna").Cells(Row, 1)

A continuación, ejecuta Buscar objetivo:

Range("BDT").GoalSeek Goal:=Range("BDTOBJ"),
ChangingCell:=Range("CUANTIA")

y acaba el bucle situando en la columna de resultados el resultado

obtenido:

Range("columnaresultado").Cells(Row, 1) = Range("CUANTIA")

De esta forma tenemos las unidades necesarias para copnseguir el

BDT Objetivo según cada precio.

20.3. Una macro para usar Buscar Objetivo con una tabla en fila

De forma similar a la anterior podemos realizar la misma

estructura para una tabla en fila.

El código es muy similar. El bucle ahora trabaja sobre la fila en vez

de sobre la columna.

135
 Fundamentos de Excel para Finanzas

20.4. Una macro para usar Buscar Objetivo con una tabla de dos ejes

A veces podemos requerir el determinar un resultado con buscar

objetivo teniendo dos variables sobre las que deseamos realizar el
análisis. Se tratará de ver el dato necesario de un input para obtener un
resultado según los valores que tengan otros dos inputs.

Para realizar esta tarea debemos realizar un doble bucle, uno para
la variable en columna y otro para la variable en fila. En el ejemplo de la
figura, una vez establecida una tasa con el primer bucle se invoca
buscar objetivo con cada uno de los años de duración de la operación
de préstamo mediante el segundo bucle.

Y se van colocando los resultados dentro de la tabla.

20.5. Una macro para usar Solver

A veces podemos requerir un uso muy frecuente de Solver,

cambiando inputs y viendo los resultados que nos ofrece. Para facilitar
esta tarea podemos invocar el uso de Solver a través de código, dándole
también a través del mismo código las especificaciones necesarias.

136
 Fundamentos de Excel para Finanzas

En el siguiente ejemplo usamos una macro, vinculada a un “botón

de lanzamiento” para ver qué proyectos deben realizarse maximizando
el VAN total, sujeto a una restricción financiera, a una restricción de
complementariedad y a la restricción de que los proyectos se pueden
realizar sólo de forma entera o no realizarse.

El código comienza situando un valor 0 en todos los proyectos, lo

que implica que cuando Solver empiece a buscar la solución parta de
una situación en la que no se realiza ninguno:

Range("proyectos") = 0

A continuación, borramos las especificaciones que pueda tener

Solver en ese momento:

SolverReset

El código de instrucciones Solver se corresponde a esta estructura

en la que hemos denominado proyectos al rango J4:J9, y restriccion1 y
limite1 se refiere a la restricción presupuestaria y restriccion2 y limite2
a la incompatibilidad entre dos proyectos:

137
 Fundamentos de Excel para Finanzas

21. Uso de la auditoria de fórmulas

La auditoría de fórmulas sirve para rastrear errores en la hoja o

para determinar los efectos que pueden producirse al variar una celda.
La auditoría de fórmulas se encuentra dentro del menú Fórmulas.
Veamos un ejemplo:

La auditoría de fórmulas nos permite conocer, una vez situados en

una celda cuáles son sus celdas precedentes (inputs) y cuales sus
dependientes. Por ejemplo si nos situamos en la celda C3, la del IPC, y
le indicamos rastrear dependientes nos marcará lo siguiente:

138
 Fundamentos de Excel para Finanzas

Como podemos observar no hay flecha hasta las ventas de 2016, de

forma que así podemos detectar el error de no haber incluido el IPC en
esta variable ese año.

A veces puede ser que no recordemos que existen otras zonas de la

hoja que necesitan un dato concreto. Antes de eliminarlo es bueno
hacer un rastreo para evitar ”males mayores”.

Igualmente podemos hacer un rastreo de precedentes, lo que nos

puede ayudar a entender una operación o las vinculaciones entre
variables. Situados sobre los costes fijos del 2016 nos indica que los
costes fijos del 2016 están afectados por los del 2015 y por el IPC.

Evidentemente a mayor complejidad del trabajo más útil es esta

herramienta, que también rastrea hacia las otras hojas del fichero,
aunque no sea la hoja en la que se encuentra la celda en cuestión. Le
animamos a que investigue las opciones que están a su alcance en la
barra de Auditoría de fórmulas. Para fórmulas complejas es

139
 Fundamentos de Excel para Finanzas

especialmente útil la herramienta “Evaluar fórmula”. Las últimas

versiones de Excel incluyen el menú Consulta (que es preciso habilitar),
que permite hacer un análisis de la composición de un libro de Excel,
de sus fórmulas, del contenido de sus celdas, etc, así como de las
relaciones entre las celdas.

En los trabajos de chequeo o comprobación de un modelo también

son útiles la herramienta de “Ventana de inspección” y “Cámara”.

22. Agrupar, dividir, inmovilizar paneles, hipervínculos

Estas utilidades nos permiten mejorar nuestro trabajo con la hoja,

especialmente cuando son muchas las filas y columnas implicadas.

22.1. Agrupar

Esta utilidad de Excel nos permite ocultar o mostrar rápidamente

información presente en la hoja. Fijémonos en el siguiente ejemplo:

Tenemos toda la información de ventas durante un año en las

cuatro provincias gallegas, desglosada por localidades (hemos puesto
cuatro localidades por provincia, pero en Galicia existen más de 300
ayuntamientos) y por meses. La información se muestra también con
agregados provinciales y trimestrales, así como los totales de la
Comunidad Autónoma y del año. Vamos a definir niveles de esquema
que nos permitan ver la información agrupada o expandida.

140
 Fundamentos de Excel para Finanzas

Marcamos las columnas CDE y acudimos al menú Datos, y en

Esquema seleccionamos agrupar. Realizamos idéntico proceso para
GHI, KLM, y OPQ. Al acabar seleccionamos C:R y agrupamos, también.

Con las filas seleccionamos las filas 4:7 y agrupamos, y así

sucesivamente con las filas de las provincias y al acabar seleccionamos
desde la fila 4 a las 23 y agrupamos de nuevo.

Nuestra hoja tendrá ahora este aspecto:

Pulsando en los símbolos menos podremos agrupar las filas o

columnas que queramos. O bien podemos indicar el nivel que queremos
presentar. Si le indicamos nivel 1 para filas y nivel 1 para columnas
sólo aparecerá el total anual de Galicia, celda S24.

Si agrupamos al nivel 2 aparecerán los totales provinciales y el

total Galicia por trimestres y total anual.

El nivel 3 nos mostrará todo.

Veamos un posible agrupamiento, nivel 3 en fila, todas, nivel 2 en

columna, sólo trimestres y total:

141
 Fundamentos de Excel para Finanzas

La principal funcionalidad de este sistema es que nos permite ver

la información agrupada como nos interese, sin perder la posibilidad de
tener, escondida debajo, el resto.

22.2. Dividir

Esta opción se encuentra en el menú Vista. Permite dividir la hoja

de cálculo en varios sectores lo que facilita el trabajo con hojas muy
grandes.

Supongamos que tenemos una hoja de cálculo que ocupa muchas

columnas. Necesitamos hacer operaciones conectando celdas que están
en las primeras columnas con celdas de las últimas columnas. Para
evitar el “mareo” de andar de izquierda a derecha, dividimos la hoja
horizontalmente, y tendremos una sección izquierda y otra derecha, al
mismo tiempo en pantalla, con sus propias barras de desplazamiento:

142
 Fundamentos de Excel para Finanzas

También podíamos haber hecho la división de forma que

tuviéramos una sección superior y otra inferior, o incluso cuatro
secciones.

22.3. Inmovilizar paneles

Es una utilidad semejante, también se establece desde el menú

Vista, pero que lo que hace es mantener una determinada zona
permanentemente en pantalla. Habitualmente se aplica para que los
rótulos de información, estén siempre visibles. Utilicemos el ejemplo de
antes del epígrafe de agrupar. ¿Cómo hacerlo? Nos situamos en la celda
C4 y le indicamos en el menú ventana, inmovilizar paneles, quedando
inmovilizadas las columnas A y B y las filas 1, 2 y 3.

Como vemos hemos inmovilizado las columnas de la izquierda, y

así, aunque nos vayamos hacia la derecha siempre sabremos a que
localidad corresponde cada cifra. Al tener inmovilizadas las filas
superiores siempre sabemos a qué fecha se refiere una columna.

143
 Fundamentos de Excel para Finanzas

22.4. Hipervínculos

Cuando tenemos hojas con mucha información nos puede interesar

crear hipervínculos que nos ayuden a navegar por la misma. Nos
situamos en una determinada celda y le indicamos con el botón
secundario del ratón, hipervínculo, seleccionamos marcador y le
indicamos la celda y de que hoja es el destino de este vínculo, y
aceptamos. Ahora el texto de la casilla seleccionada para realizar el
hipervínculo aparece en azul y subrayado, si pulsamos dicho texto nos
enviará a la celda destino. Esto facilita la navegación en hojas grandes
añadiendo comodidad al uso de los modelos, especialmente por parte de
aquellos que son usuarios, pero no lo han desarrollado. Nosotros hemos
hecho un hipervínculo para ir desde la casilla B3 a la H100 y luego para
volver de H100 a B3.

23. Imprimir

Excel cuenta con todas las opciones necesarias a la hora de

imprimir los datos y los gráficos. Es posible imprimir tanto hojas
completas como parte de ellas, definiendo, lógicamente, parámetros
como los saltos de página, un factor de aumento o reducción, el número
de copias, etc.

23.1 Selección de datos a imprimir

Los datos que deseamos imprimir deben seleccionarse previamente,

puesto que cuando no se ha efectuado una selección, por defecto
imprime todo el contenido de la hoja.

Debe marcarse en primer lugar el rango a imprimir.

Posteriormente, en Diseño de página acudimos a Área de impresión, y
establecer área de impresión, de tal forma que tan sólo se dé salida a la
información, ya sean datos o gráficos, que exista en dicha área.

23.2 Encabezados y pie

En las páginas impresas pueden establecerse un título, fecha,

número de página, etc. Estos elementos pueden disponerse en la parte
superior de la página o bien en la inferior.

144
 Fundamentos de Excel para Finanzas

Debemos ir al menú configurar página en diseño de página, en la

flecha del fondo a la derecha y nos abrirá el siguiente menú.

Seleccionamos en ella la opción Encabezado y pie de página.

Debajo de los paneles que muestran la vista previa del encabezado y pie
existen listas desplegables con algunos modelos predefinidos. Si
ninguno de estos modelos se ajusta a sus preferencias puede pulsar el
botón personalizar encabezado o pie de página para indicar de forma
manual los parámetros deseados.

23.3 Salto de página

Una vez elegida la información que deseamos imprimir, y

establecidas las opciones que afectan al tamaño del papel, su
orientación, número de copias, etc., llegará el momento de comprobar

145
 Fundamentos de Excel para Finanzas

dónde se efectuarán los saltos de página. Esto es importante cuando se

trabaja con hojas de cálculo grandes.

Debemos acudir Ver Sal Pág. En el menú Vista en la sección Vistas

de libro.

Vemos que se reduce el tamaño de los datos y aparecen unas

líneas de división de página, así como la indicación del número de
página. Para ajustar los saltos de página debemos arrastrar con el ratón
y establecer los saltos de página deseados. Para volver a la vista normal
debemos elegir la opción normal en el menú Vistas de libro. No debe
olvidarse que en una página entran los datos que entran, y que Excel
no es un programa de edición de textos. No obstante, siempre existe la
opción de modificar el tamaño de la letra o incluso al imprimir se puede
modificar el escalado.

24. Recomendaciones básicas sobre la elaboración de modelos

financieros en Excel

Los programas de hoja de cálculo constituyen un gran soporte para

el trabajo en el área de finanzas de la empresa. El uso adecuado de los
mismos permite explotar de forma exponencial los conocimientos que se
tengan en dicha materia. No obstante, como cualquier otro
instrumento, si su uso no es el adecuado puede provocar errores y
pérdidas de tiempo, que creemos que con unos pocos principios básicos
podrían reducirse.

Veamos un pequeño resumen de las cuestiones básicas que

creemos que deberían tenerse en cuenta. Este texto, en todo caso, no
146
 Fundamentos de Excel para Finanzas

pretende ser un patrón oro para el resto de usuarios de programas de

hoja de cálculo.

El objetivo general de los consejos que vamos a enumerar es

minimizar el que podríamos denominar “riesgo de hoja de cálculo”, es
decir, el riesgo de que los cálculos no sean correctos o que la
interpretación de cálculos y resultados en un modelo sea incorrecta.
Como en cualquier otra actividad humana, sea profesional o no,
siempre hay pautas para mejorar la “mecánica” de trabajo. Esa es la
intención que nos mueve a sugerir el seguir unas determinadas pautas.

24.1. Número de hojas de un modelo

Al empezar a trabajar en Excel lo primero que debemos decidir es si

nuestro “trabajo” o modelo va a ocupar una hoja o varias de un libro de
Excel. Hay que buscar un equilibrio entre la navegabilidad (hojas muy
grandes no son cómodas de navegar) y la sobrefragmentación (un
número excesivo de hojas fragmenta demasiado la información y
dificulta la visión integral de la misma).

En este sentido, un elemento importante es la elección de los

nombres de las hojas que se utilizarán. Deberían ser suficientemente
claros e identificativos, y no muy largos para permitir tener el mayor
número de pestañas de hojas a la vista, en todo momento.

24.2. Alcance del modelo

Otra cuestión a decidir es cuán general va a ser nuestro modelo, es

decir, qué nivel de flexibilidad va a tener. A mayor flexibilidad mayor
número de inputs se permitirán, y mayor complejidad en la
formulación, que ha de adaptarse a inputs cambiantes. Debemos tener
claro el alcance desde el primer momento pues marcará toda la
formulación.

24.3. Orientación vertical u horizontal de un modelo

Otra cuestión previa que deberíamos decidir es si vamos a orientar

nuestro trabajo en horizontal o en vertical. Un cuadro de amortización
de un préstamo posiblemente debamos hacerlo en vertical, siendo cada

147
 Fundamentos de Excel para Finanzas

fila un periodo diferente, mientras que un plan financiero irá en

horizontal, siendo cada columna un periodo diferente. Se trata de que la
orientación ayude a entender y usar el modelo.

24.4. Orientación de izquierda a derecha y de arriba a abajo

Al igual que en la escritura occidental, los modelos deben fluir de

izquierda a derecha y de arriba a abajo. Los inputs deben estar en la
parte superior y hacia la izquierda, y a partir de ellos deben irse
abriendo las diferentes zonas de cálculo de cada modelo. Debe tratar de
evitarse (salvo cuando sea imprescindible) la existencia de loops de
información que hagan que los datos circulen por el modelo de forma
desordenada, vertical u horizontalmente. Este tipo de práctica dificulta
la detección de errores y la comprensión del modelo. La información
debe fluir de la forma más sencilla y directa posible de forma que ayude
a la comprensión del modelo y no que la entorpezca.

24.5. Acerca de inputs y outputs

Los Inputs y Outputs principales deben estar bien identificados. La

mejor opción es utilizar un determinado color para el fondo o relleno de
la celda. En nuestro caso hemos optado por usar fondo verde para los
inputs y fondo amarillo para los outputs principales. Pero se puede
buscar otra forma de resaltar dichas celdas, como por ejemplo
marcarlas con bordes. De ser ésta la opción utilizada deberíamos
eliminar las líneas de división en el menú Vista de Excel, para que
resalten más las celdas con borde.

Además, en celdas adyacentes debe situarse información sobre los

mismos. No se pueden dejar inputs u outputs en medio de una hoja sin
que el usuario sepa inmediatamente qué concepto representan.
También puede ayudar el uso de las utilidades de “Insertar comentario”
así como las validaciones de datos. La validación de datos es
especialmente necesaria cuando un input tiene limitaciones en cuanto a
los valores que puede tomar. De esta forma se evita que el modelo
realice cálculos con inputs inadecuados y por tanto ofrezca resultados
incorrectos.

148
 Fundamentos de Excel para Finanzas

Si el tamaño del modelo lo permite, lo ideal es que los inputs y los

outputs básicos (en el análisis de un proyecto, podría ser el VAN, la TIR,
etc.) estén siempre visibles en pantalla. De esta forma cualquier cambio
de un input se refleja inmediatamente en el output y podemos
apreciarlo sin navegar en su búsqueda. En modelos muy pequeños, que
entran en una sola pantalla, esta circunstancia ya está garantizada. El
problema surge cuando el modelo ya es algo grande, y no entra en una
sola pantalla.

Debe usarse un formato adecuado tanto de inputs como de

outputs, que evite errores de interpretación. Las cifras monetarias se
leen mejor si llevan separador de miles y habitualmente no será
necesario incluir decimales (en algunos casos sí, como en el precio de
un bono, o en general en variables input que puedan tener valores no
enteros). El separador de miles es fundamental para una lectura rápida
de las cifras y evitar “leer” cien mil cuando realmente se trata de diez
mil, o viceversa. En cuanto a las cifras porcentuales, si son inputs,
deben incluir en el formato decimales suficientes, y si son outputs
básicos, también. De no ser así pueden producirse errores de
interpretación. El usuario puede pensar que el modelo está usando una
tasa de interés, por ejemplo, del 8%, y en realidad estar usando el 7,6%
o el 8,4%. O creer que la rentabilidad obtenida es un 9% y realmente
que sea el 8,6% o el 9,4%. A veces también puede ser muy útil el
formato condicional, que se adapte a los valores de una celda y
favorezca análisis más rápidos e inmediatos de los resultados
obtenidos.

En general, en el formato de un modelo, puede “jugarse” con los

colores de textos, tramas, y líneas, pero sin que acaben siendo un
elemento de despiste para el usuario. El formato debe ayudar a la
comprensión y uso de un modelo, no dificultarla. En las zonas de
cálculo, por ejemplo en una tabla en la que se muestre una cuenta de
resultados, es innecesario y entorpecedor incluir formatos de moneda, y
habitualmente tampoco aporta nada incluir decimales. El formato

149
 Fundamentos de Excel para Finanzas

contable, con su forma de indicar los números negativos, con el signo

menos muy desplazado a la izquierda, tampoco es de gran ayuda, más
bien todo lo contrario. Lo ideal es un formato lo más limpio posible, que
ayude a leer la información reduciendo la probabilidad de
interpretaciones inadecuadas. A partir de ahí, es posible usar formatos
más o menos creativos para mejorar presentaciones a terceros. Un
elemento que no debemos olvidar es que los modelos se suelen imprimir
para su análisis y algunos formatos (exceso de colores) pueden ser muy
adecuados sobre la pantalla, pero serlo menos trasladados al papel.

Veamos un ejemplo de un modelo que realiza bien los cálculos y

con un orden de la información adecuado, incluso con un formato
general adecuado, y sin embargo con carencias serias en cuanto a
formatos de números, de inputs y outputs.

Veamos las carencias:

En la celda C4, el input del valor residual, una cifra muy grande,
incluye decimales, que en principio para un input así son innecesarios,
y no incluye el separador de miles. Hay que contar los ceros para saber
si son 100.000 euros o 10.000 o 1.000.000.

La celda C10, el input de los costes fijos tampoco tiene separador

de miles. Además, las celdas B10 y B11, que se refieren a costes fijos y
costes variables tienen un rótulo (CF, CV) que aunque se adivina por el

150
 Fundamentos de Excel para Finanzas

contexto, quedaría mejor si contuviera el texto completo y no la simple

abreviatura. No hay ninguna razón que justifique no poner el texto
completo en este caso. Lo mismo cabe decir del concepto de tasa de
descuento, k, en la celda F2 de la figura.

La celda de crecimiento del precio, D9, carece de decimales en el

valor porcentual. El lector verá ahí un 3%. Pues he de decirle que en
realidad la celda contiene un 2,5%. Este tipo de error es especialmente
grave pues el usuario puede pensar que el modelo está trabajando con
unos inputs y en realidad estarlo haciendo con otros. En la celda C9
hemos dejado sin decimales el precio. Depende de qué cuantía estemos
hablando puede ser oportuno o no poner decimales. Con elementos de
precio pequeño puede ser más razonable que se pueda necesitar incluir
decimales, mientras que con productos o servicios de alto precio no. Si
tiene dudas sobre una cuestión de este tipo, inclúyalos. Tenga en
cuenta, además, que, en un producto de alto precio, si el input tuviera
decimales que el usuario no estuviera viendo, el efecto en el resultado
final sería mínimo. Sin embargo, en productos de escaso precio, los
decimales tienen un gran impacto, dado que pueden representar una
parte importante del precio.

Igual que en el caso anterior, en la celda G5, la referida al Fondo de

Maniobra Operativo, faltan decimales. En realidad, esa celda contiene
un 20,4%. En la celda F5, dado lo largo del texto, creemos que las siglas
y simplificación del rótulo están justificados. En todo caso, si hay dudas
acerca de la perfecta comprensión del rótulo debe cambiarse el mismo
para evitarlas.

En G3, la tasa de reinversión, que es un dato porcentual, está

introducido en un formato de número inadecuado, y además con pocos
decimales. En realidad, esa celda contiene un 6,4%.

Para acabar con la zona de inputs, la celda D11, está coloreada

como una celda input y realmente el modelo no lo prevé así. Un usuario
despistado podría pensar que dicha celda admite el crecimiento en

151
 Fundamentos de Excel para Finanzas

costes variables, que es lo que da a entender su formato dada la

posición que ocupa.

En cuanto a los outputs, el único correcto es el IRN (Índice de

Rentabilidad Neto). El VAN, en la celda G8 carece de separador de miles
y, dadas las cifras de las que se trata no parece necesario que incluya
decimales. Sí son necesarios sin embargo los decimales en la TIRM (TIR
Modificada) de la celda G10. ¿Se atrevería el lector a aventurar si la
TIRM está por encima o por debajo de ese 15% que se muestra? En la
celda G9, la TIR, ni tiene el formato adecuado (debería ser porcentual)
ni los decimales necesarios.

Si nos fijamos en la zona de cálculos, la fila 16, costes variables,

contiene decimales innecesarios y no contiene separador de miles.

En la fila 19, el BAIT, contiene decimales innecesarios y el símbolo

de la moneda (el euro), totalmente innecesario y que entorpece la
limpieza y lectura.

En la fila 23 el formado sitúa los símbolos “-“ de las cifras

negativas, muy a la izquierda en cada celda, lo que provoca que a veces
se puedan pasar por alto. No es un formato adecuado para una tabla de
información. Observe como en la celda C22 el signo negativo se aprecia
mucho mejor.

Las celdas H24 y H25 carecen de separador de miles, y en el primer

caso, además, tienen decimales innecesarios. Cuando se da formato a
una tabla suele ocurrir que elementos que están un poco aislados
queden sin el formato adecuado, como ha sido en este caso.

Creemos que las siglas en la zona de cálculos están correctamente

utilizadas pues se refieren a conceptos de uso común en esa forma
abreviada en el ámbito financiero (BAIT, NOPAT, BDT), y no deberían
generar ningún tipo de duda a ningún usuario.

Esperamos que este pequeño ejemplo, y nuestras aclaraciones

sobre el mismo, ayuden a que el lector capte la importancia que este
tipo de cuestiones tienen, que van más allá de la estética.

152
 Fundamentos de Excel para Finanzas

24.6. Las celdas de inputs deben contener datos, no fórmulas y las celdas
de cálculo no deben contener datos, solo fórmulas

En las celdas input debe haber solamente datos. A veces es

necesario calcular un input a partir de otros inputs. En ese caso
podemos plantear si es oportuna una zona de inputs intermedios. Tal es
el caso de cálculos acerca del tipo de interés por periodo cuando
permitimos en un préstamo diferentes posibilidades de periodificación
anual.

En la medida de lo posible hay que evitar introducir cifras en las

fórmulas. Las cifras deben estar en celdas que a su vez se referencien
en las fórmulas. De esta forma se evita el tener que retocar una fórmula
al cambiar un dato de entrada. En todo hay excepciones, claro. Por
ejemplo, si queremos calcular los ingresos diarios debemos dividir la
cifra de ingresos entre 365, y ese dato puede entrar directamente en la
fórmula (salvo que el año sea bisiesto, esa cifra, 365, no la vamos a
cambiar nunca).

Si hay algo que ni nos gusta ni nos parece en absoluto adecuado es

“trampear tablas”. Por ejemplo, imaginemos que en un cálculo de los
costes fijos, una vez hecha la suma de sus componentes, le sumamos
para un determinado periodo una cuantía dentro de la propia fórmula.
En este caso esa cuantía, además de la dificultad para ser detectada por
un usuario diferente, o por nosotros mismos con el paso del tiempo, se
convierte en un peso muerto, una rémora desvinculada e insensible a
las variaciones del modelo.

24.7. Introduzca los datos una sola vez

Cuando trabajamos con un modelo, es muy posible que algún dato,

alguna variable, participe en diferentes zonas del proceso o modelo, por
ejemplo, la tasa de interés, o la tasa impositiva. Si para cada una de
estas tareas creamos una celda de entrada del dato nos veremos
obligados a comprobar constantemente que tenemos el mismo valor en
todas ellas. Por supuesto, es mucho más cómodo introducir el dato una
sola vez y que desde ese punto se distribuya u opere en todos aquellos

153
 Fundamentos de Excel para Finanzas

lugares del modelo en que sea necesario. A la comodidad añadimos la

seguridad de que estamos aplicando el valor que queremos, el mismo,
en todos los casos.

Como corolario hemos de añadir que cuando un dato circula por

un modelo pasando de un proceso u operación a otra, y deseamos
realizar algún cambio hemos de realizarlo “aguas arriba” para
garantizar que todo el modelo actualiza el cambio propuesto.

24.8. Introduzca niveles en “segunda línea”

Las celdas nivel interactúan con la entrada de datos facilitando la

simulación. Su función es similar a una llave de agua. Por ejemplo una
celda nivel que regule la producción permite desde un caudal prefijado
(el 100%), aumentar la producción (valores superiores al 100%) o
reducirla (valores inferiores al 100%). En este punto queremos incidir
sobre la importancia de cuál es el valor que tiene la variable, en este
caso la producción, en su situación de partida. Dicho valor debe estar
perfectamente identificado. Nuestra propuesta es que la interacción
entre el valor de entrada y el valor de la celda nivel se produzca en
segunda línea, “aguas abajo”, para que las modificaciones en el valor
base se puedan realizar con facilidad, y a partir de ahí todo el modelo
quede condicionado por el nivel.

24.9. Creación de fórmulas y chequeo

Las fórmulas deben ser lo más sencillas y directas posible, lo que

evitará muchos errores. Si un cálculo es muy complejo puede ser
adecuado fragmentarlo en varias celdas, al menos mientras se está
diseñando. Una vez que se ha comprobado que funciona, puede tratar
de integrarse en una sola fórmula compacta. Nunca utilice una fórmula
o una función que no entienda claramente. Puede funcionar en el caso
que está realizando pero no con otros inputs. Si Excel tiene una función
ya diseñada para un determinado propósito, úsela.

Al escribir las fórmulas no escriba las referencias de las celdas,

“cójalas” con el cursor o con el ratón. De esta forma evitará numerosos

154
 Fundamentos de Excel para Finanzas

errores y además reforzará su conocimiento sobre la operativa del

modelo: estará trabajando con conceptos, no con celdas. El uso de
nombres en las celdas es una ayuda interesante en este sentido.

Si es posible debe crearse cada fórmula una sola vez, y copiarla

desde ahí al resto de sitios donde sea necesaria. Para ello es preciso
tener un buen conocimiento de las técnicas de copiado y pegado con
referencias absolutas y relativas (use para ello la tecla F4, y no inserte
directamente los símbolos dólar).

En modelos horizontales deberíamos tratar de tener la misma

fórmula en toda la fila. En modelos verticales deberíamos tratar de
tener la misma fórmula en toda la columna. De esta forma se reducen
los posibles errores y se facilita la realización de modificaciones.

Siempre que sea posible realice algún tipo de control de validez de

los cálculos obtenidos. Por ejemplo mediante un chequeo de
redundancia (cross check), calculando el mismo resultado con
formulaciones alternativas, o bien comprobado el cumplimiento de
fundamentos teóricos (por ejemplo el chequeo del cuadre de un
balance).

Además se puede chequear la validez del modelo poniendo datos

extremos en los inputs, viendo si el modelo responde como debe a
alteraciones en los inputs, poniendo inputs para los que ya conozcamos
el resultado (cuando ya se sabe un resultado por otros medios).

En todo caso, el primer chequeo debe ser el chequeo lógico: ¿El

resultado se corresponde con lo que se esperaba?

También puede ser interesante en ocasiones el chequeo de

resultado parcial, en fórmulas complejas.

24.10. Optimice el tiempo y aproveche su trabajo

Si queremos realizar una suma de dos cantidades posiblemente lo

más cómodo sea utilizar una calculadora. Si es posible que dicha
operación se repita, con distintas cantidades, puede que ya

155
 Fundamentos de Excel para Finanzas

encontremos más útil establecer una fórmula en un archivo de una hoja

de cálculo que, una vez introducidas las cifras, nos dé su suma.

A medida que las operaciones se complican mayor utilidad tendrá

para nosotros el haber predefinido un modelo (inicialmente una simple
suma) en la hoja de cálculo. Por otro lado, cuando una tarea que se
realice pueda que se convierta en repetitiva, deberíamos, si es posible,
automatizarla.

Creemos oportuno que se aproveche al máximo el trabajo realizado.

Para ello creemos imprescindible incluir en los modelos:

• Gráficos vinculados que permitan realizar un mejor análisis,

permitiendo “poner en valor” lo que uno ha hecho. Los
gráficos deben ser los más adecuados para cada caso y
deben estar correctamente titulados y rotulados de forma que
la información aportada sea clara. El gráfico “ideal” debería
poder entenderse y analizarse totalmente sin necesidad de
más información que la que el gráfico en sí mismo aporta.

• Tablas de sensibilidad que ayuden a analizar hasta qué

punto el resultado depende de los inputs usados, y por tanto
hasta qué punto la decisión que el modelo propone sobre un
problema es la decisión adecuada.

Por último, si queremos realizar un trabajo más profesional

debemos incluir cuestiones como:

• Validación de datos en los inputs

• Nombres de celdas

• Ayudas a la navegabilidad (inmovilización de paneles,

agrupaciones, hipervínculos)

• Proteger nuestro modelo, para ello, en el menú Revisar se

encuentra la opción Proteger Hoja y Proteger Libro que
permite diferentes tipos de protección. La más habitual es
impedir que el usuario, si no dispone de la clave de acceso,

156
 Fundamentos de Excel para Finanzas

realice modificaciones en la formulación. De esta forma sólo

podría cambiar los inputs, evitando que “estropee el modelo”.

25. Algunos atajos o trucos básicos de teclado en Excel

A pesar de que cada vez más el ratón es el elemento básico para

interactuar con el ordenador, hay algunos trucos o atajos de uso del
teclado que consideramos, en algunos casos, de gran utilidad, dentro
del casi incontable número de atajos que Excel ofrece. En la siguiente
dirección tiene un listado muy completo de los atajos en el teclado en
español: [Link] Nosotros
vamos a referirnos a aquellos que creemos más interesantes.

A) Comencemos con los que podemos denominar trucos de

desplazamiento y selección, que son especialmente útiles cuando se
trabaja con tablas de datos de gran tamaño.

• Control + Flechas de desplazamiento: nos llevará a la última

celda hacia la derecha, izquierda, arriba o abajo, en función de la
tecla de desplazamiento usada, del rango en el que estemos.
Puede conseguirse lo mismo pulsando la tecla Fin y después (no
simultáneamente) la tecla de desplazamiento deseada. Excel
considera rango homogéneo o compacto a aquellas celdas conexas
rodeadas por celdas vacías. La tecla Inicio nos lleva al inicio de la
fila activa.

• Control + Shift + Flechas de desplazamiento Si también

pulsamos simultáneamente la tecla Shift (mayúsculas) en vez de
llevarnos a las celdas antes indicadas seleccionará el rango entre
nuestra posición actual y las celdas antes indicadas.

• Control + E: seleccionará toda la zona de datos actual hasta

encontrar zonas vacías.

• Control + barra espaciadora: selecciona toda la columna de la

hoja (no sólo la del rango actual).

157
 Fundamentos de Excel para Finanzas

• Shift + barra espaciadora: selecciona toda la fila de la hoja (no

sólo la del rango actual).

• Shift + flechas de dirección: selecciona el rango desde la

posición de partida y la posición a la que lleguemos con las
flechas de dirección.

• Si tenemos una fila entera de la hoja seleccionada podemos

insertar otra fila con Control + “+”. (si tenemos varias
seleccionadas insertará un número igual de filas). Con Control +
“-“·elimina la fila o filas seleccionadas.

• Control + Inicio nos devuelve a la celda A1

• Control + Fin nos lleva justo debajo de la última celda usada

abajo y a la derecha.

• Control + Av Pág y Control + Re Pag nos lleva a la siguiente hoja

o a la anterior hoja, respectivamente, del libro abierto.

• La tecla Escape cancela la selección y también nos permite salir

sin más de una celda sin validar lo que estuviéramos haciendo.

B) Además de para seleccionar y desplazarnos a lo largo de la hoja

existen otros atajos interesantes al editar o trabajar con un libro:

• Para copiar y pegar podemos usar Control + C (Copiar) y Control

+ V (pegar, o simplemente Enter). Con Control + X antes de
pegar, se elimina la información de su ubicación original.

• Si queremos forzar dos líneas o más en una celda, al estar

editando presionamos Alt + Enter cada vez que queramos
avanzar línea.

• Alt + Shift + 0: invoca la función SUMA sobre el rango más

próximo o sobre las diferentes columnas de un rango si han sido
seleccionadas. Incluso si seleccionamos todo el rango incluyendo
la primera fila libre posterior y la primera columna libre posterior
se establecerán sumas por columna y fila para todo el rango.

158
 Fundamentos de Excel para Finanzas

• Si estamos en la celda debajo de una columna con información

(por ejemplo nombres) y pulsamos Alt + flecha hacia abajo
veremos que se nos abre una ventanita con la información de la
columna ordenada alfabéticamente y que nos puede ayudar a
introducir un nuevo registro.

• Si no arrepentimos de una acción podemos deshacerla pulsando

Control + Z. Y si nos arrepentimos de haberla deshecho podemos
rehacerla pulsando Control + Y.

• Control + G guarda el libro activo con el nombre actual.

• Control + A, abre el menú de abrir libro.

• Control + U, crea un libro nuevo.

• Para poner un rango en negrita, subrayado o cursiva podemos

pulsar Control + n, s y k, respectivamente.

• Con Control + P se activa el menú de impresión.

• Con Control + T, se establece una Tabla con el rango activo.

• Con Control + D, se copia la fórmula hacia la derecha en el rango

seleccionado (también copia los formatos).

• Con Control + J, se copia la fórmula hacia abajo en el rango

seleccionado (también copia los formatos).

• Control + 1. Abre el menú de formato de celda.

• Si tras escribir en una celda queremos ir hacia la derecha

podemos validar con el Tabulador en vez de con Enter. Si
queremos ir hacia arriba validamos con Shift + Enter en vez de
con Enter.

• Tecla de Suprimir. Borra el contenido de la celda o rango

seleccionado.

• Tecla de Retroceso. Borra el contenido de la celda y entra en

edición de la celda.

159
 Fundamentos de Excel para Finanzas

• Si pulsamos Control + , (coma) se indicará la fecha actual en la

celda activa de forma estática. Interesante para que quede
constancia del día que se hizo un determinado trabajo (no
confundir con la función HOY, que genera la fecha actual pero
cambia si la fecha cambia). Con Control + Shift + . (punto) se
pone la hora de forma estática (esto nos parece menos útil que lo
anterior).

C) Por último, también podemos usar las teclas de ayuda o

función, las teclas F.

• F1. Abre la Ayuda de Excel

• F2. Entra en la celda seleccionada o pasa a modo edición de la

celda en la que estamos trabajando.

• F3. Muestra el cuadro de diálogo de Pegar Nombre.

• F4. Repite la última acción. También sirve para bloquear

referencias al editar una fórmula (poner los símbolos $).

• F5. Abre la herramienta de Ir a.

• F9. Recalcular fórmulas de los libros abiertos.

• F11. Elabora un gráfico básico con los datos seleccionados y lo

sitúa en una hoja nueva. Con Alt + F1 el gráfico se sitúa en la
misma hoja.

• F12. Abre el menú Guardar Cómo.

• Shift + F2. Inserta un comentario en la celda activa

• Si queremos crear una hoja nueva Shift + F11.

• Para abrir el editor de VBA Alt + F11

160