Fundamentos de Excel para Finanzas

Fundamentos de Excel para Finanzas

(Adaptado Excel 2016 y Office 365)

Actualizado 9 de Febrero de 2021

Alfonso Rodríguez Sandiás

Grupo Valoración Financiera Aplicada

www.usc.es/modeleva

http://www.usc.es/gl/investigacion/proxectos/modeleva/

Universidad de Santiago de Compostela

Este documento está acompañado de seis ficheros:

• Fundamentos I.xls

• Fundamentos II.xls

• Fundamentos III.xls

• Fundamentos IV.xls

• Fundamentos V.xls

• Fundamentos VI Funciones de Usuario.xls

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.

[email protected]

http://www.usc.es/modeleva

http://www.usc.es/gl/investigacion/proxectos/modeleva/

mailto:[email protected]


Alfonso Rodríguez Sandiás 2

TABLA DE CONTENIDOS

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

1.1. Introducción ............................................................................................................................... 10

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

1.3. Formato ...................................................................................................................................... 14

1.4. Validación .................................................................................................................................. 17

1.5. Formato condicional .................................................................................................................. 22

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

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

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

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

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

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

3. NOMBRES ............................................................................................................................................. 33

3.1. Nombre de celda ........................................................................................................................ 33

3.2. Nombre de rango ....................................................................................................................... 34

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

3.4. Nombres con referencia relativa ................................................................................................ 35

3.5. Otros usos de la técnica de asignación de Nombres .................................................................. 36

3.6. Nombres en activo ..................................................................................................................... 38

4. FUNCIONES ARITMÉTICAS ......................................................................................................................... 39

4.1. SUMA ......................................................................................................................................... 39

4.2. SUMA con rango semicerrado ................................................................................................... 40

4.3. CONTAR, CONTARA y CONTAR.BLANCO .................................................................................... 41

4.4. SUMAR.SI y CONTAR.SI .............................................................................................................. 42

4.5. SUMAPRODUCTO ....................................................................................................................... 43

4.6. SUMAR.SI.CONJUNTO y uso avanzado de SUMAPRODUCTO y SUMA ....................................... 44

4.7. COCIENTE y RESIDUO ................................................................................................................. 45

4.8. EXP, POTENCIA y PI .................................................................................................................... 45



4.9. PRODUCTO y SUMA.CUADRADOS .............................................................................................. 46

4.10. AGREGAR Y SUBTOTALES ......................................................................................................... 46

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

5.1. MAX, MIN y PROMEDIO ............................................................................................................. 48

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

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

5.4. JERARQUIA ................................................................................................................................. 49

5.5. K.ESIMO.MAYOR ........................................................................................................................ 50

5.6. K.ESIMO.MENOR ........................................................................................................................ 51

5.7. PENDIENTE ................................................................................................................................. 51

5.8. DISTR.NORM.N ........................................................................................................................... 52

5.9. INV.NORM .................................................................................................................................. 52

5.10. MEDIANA, MODA y MODA.VARIOS ......................................................................................... 53

5.11. PERCENTIL Y RANGO.PERCENTIL .............................................................................................. 54

5.12. FRECUENCIA ............................................................................................................................. 54

6. MISCELÁNEA .......................................................................................................................................... 55

6.1. TEXTO ......................................................................................................................................... 55

6.2. RAIZ ............................................................................................................................................ 55

6.3. ABS, ENTERO, REDONDEAR ........................................................................................................ 56

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

6.5. TRANSPONER ............................................................................................................................. 58

6.6. La función SECUENCIA ................................................................................................................ 58

6.7. Las funciones MULTIPLO.SUPERIOR.MAT y MULTIPLO.INFERIOR.MAT ..................................... 59

6.8. Otras funciones de REDONDEAR, PAR, IMPAR, MAS, MENOS, MULT ........................................ 59

6.9. TRUNCAR vs ENTERO ................................................................................................................. 60

6.10. Operaciones con fechas, DIAS .................................................................................................. 60

6.11. HOY, AHORA y FIN.MES............................................................................................................ 61

6.12. AÑO, MES, DIA, DIASEM .......................................................................................................... 62

6.13. FECHA.MES, FECHA Y FECHANUMERO..................................................................................... 62



7. FUNCIONES LÓGICAS ................................................................................................................................ 64

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

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

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

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

7.5. Función Y .................................................................................................................................... 68

7.6. Función O ................................................................................................................................... 68

7.7. Función NO ................................................................................................................................. 69

7.8. Función SI.ERROR y SI.ND ........................................................................................................... 69

7.9. Función SI.CONJUNTO ................................................................................................................ 70

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

8.1. ELEGIR ........................................................................................................................................ 71

8.2. La función DESREF ...................................................................................................................... 72

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

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

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

8.6. La función INDICE ....................................................................................................................... 80

8.7. La función CAMBIAR................................................................................................................... 82

8.7. Las funciones FILA y COLUMNA, FILAS y COLUMNAS ................................................................ 83

8.7. La función INIDRECTO ................................................................................................................ 83

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

8.9. La función FILTRAR ..................................................................................................................... 87

8.10. La función UNICOS ................................................................................................................... 90

8.11. Algunas combinaciones más .................................................................................................... 92

8.12. Búsquedas y ordenaciones complejas ...................................................................................... 94

8.13. Uso de factores comodín .......................................................................................................... 95

9. FUNCIONES DE TEXTO E INFORMACIÓN ....................................................................................................... 96

9.1. La función IZQUIERDA ................................................................................................................ 96

9.2. La función DERECHA ................................................................................................................... 96



9.3. Las funciones ENCONTRAR y HALLAR......................................................................................... 97

9.4. La función EXTRAE ...................................................................................................................... 97

9.5. La función CONCAT .................................................................................................................... 98

9.6. La función UNIRCADENAS .......................................................................................................... 98

9.7. La función ESPACIOS .................................................................................................................. 99

9.8. La función IGUAL ........................................................................................................................ 99

9.9. La función LARGO ....................................................................................................................... 99

9.10. La función MAYUSC .................................................................................................................. 99

9.11. La función MINUSC................................................................................................................. 100

9.12. La función NOMPROPIO ......................................................................................................... 100

9.13. Funciones CARACTER y CODIGO ............................................................................................. 100

9.14. Funciones REEMPLAZAR, SUSTITUIR, REPETIR y T.................................................................. 101

9.15. Combinación de funciones TEXTO .......................................................................................... 102

9.16. Las funciones INFO, HOJA, HOJAS y CELDA ............................................................................ 104

9.17. Las funciones ES.PAR, ES.IMPAR, Y ESBLANCO ...................................................................... 104

9.18. Las funciones ESERROR y ESERR ............................................................................................. 105

9.19. Las funciones ESFORMULA, ESTEXTO, ESNOTEXTO, ESNUMERO, ESNOD, ESLOGICO y NOD 105

9.20. Las funciones TIPO y TIPO.DE.ERROR ..................................................................................... 106

10. FUNCIONES FINANCIERAS ...................................................................................................................... 106

10.1. La función INT.EFECTIVO ........................................................................................................ 106

10.2. La función TASA.NOMINAL .................................................................................................... 107

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

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

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

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

10.7. VNA ........................................................................................................................................ 110

10.8. TIR .......................................................................................................................................... 111

10.9. Las funciones VNA.NO.PER y TIR.NO.PER............................................................................... 111

10.10. La función TIRM ................................................................................................................... 112



10.11. La función VF.PLAN .............................................................................................................. 112

10.11. Las funciones PAGO, PAGOPRIN y PAGOINT revisitadas...................................................... 112

11. FÓRMULAS TIPO ARRAY Y FUNCIONES DE BASE DE DATOS Y TIPOS DE DATOS .................................................. 113

11.1. Ejemplos de fórmulas Array ................................................................................................... 113

11.2. Ejemplo de fórmulas Array y su nuevo comportamiento en Excel ......................................... 116

11.3. Uso de funciones BD ............................................................................................................... 116

11.4. Uso de Tipo de Datos ............................................................................................................. 119

12. USO DE LA HERRAMIENTA TABLA .......................................................................................................... 119

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

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

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

13. HERRAMIENTA BUSCAR OBJETIVO .......................................................................................................... 123

14. ESCENARIOS ....................................................................................................................................... 126

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

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

15. SOLVER ............................................................................................................................................. 130

16. USO DE REFERENCIAS CIRCULARES .......................................................................................................... 133

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

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

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

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

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

17.5. Gráfico de áreas ..................................................................................................................... 141

17.6. Gráfico de barras ................................................................................................................... 142

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

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

17.9. Gráfico circular ....................................................................................................................... 145

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

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



18. UNA MACRO SENCILLA ......................................................................................................................... 161

19. FUNCIONES PERSONALIZADAS ................................................................................................................ 163

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

19.2. Función para invertir el orden de una serie de datos ............................................................. 166

19.3. Función para indicar el nombre de la hoja ............................................................................. 167

19.4. Funciones para mostrar la fórmula de una celda .................................................................. 168

19.5. Función para calcular el VAN ................................................................................................. 169

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

19.7. Función para calcular el IRB e IRN .......................................................................................... 170

19.8. Funciones para calcular el VAN con tasa de descuento variable ........................................... 171

19.9. Función para calcular el VAN Modificado .............................................................................. 172

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

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

........................................................................................................................................................ 173

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

finito ................................................................................................................................................ 174

19.13. Función para calcular el valor de una renta con un crecimiento constante durante un

periodo finito seguida de un crecimiento constante ilimitado ........................................................ 175

19.14. Función para calcular el plazo de recuperación ................................................................... 175

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

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

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

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

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

20.5. Una macro para usar Solver ................................................................................................... 180

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

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

22.1. Agrupar .................................................................................................................................. 184

22.2. Dividir ..................................................................................................................................... 186

22.3. Inmovilizar paneles ................................................................................................................ 187



22.4. Hipervínculos .......................................................................................................................... 188

23. IMPRIMIR .......................................................................................................................................... 188

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

23.2 Encabezados y pie ................................................................................................................... 189

23.3 Salto de página ....................................................................................................................... 190

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

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

24.2. Alcance del modelo ................................................................................................................ 191

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

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

24.5. Acerca de inputs y outputs ..................................................................................................... 192

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

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

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

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

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

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

26. NOS HEMOS DEJADO MUCHAS COSAS EN EL TINTERO .................................................................................. 206



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, en finanzas, o en otras tareas. Hemos usado como

programa de hoja de cálculo de referencia Excel la versión de office 365

de 2020, pero las cuestiones que desarrollamos son extrapolables en

muchos casos a versiones previas y a otros programas similares. Seguro

que a algún lector 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.

Este documento se acompaña de los correspondientes ficheros. En

donde ha sido posible hemos dejado espacio preparado para que el

lector pueda tratar de realizar las diferentes cuestiones sin “borrar”

nuestra propuesta. Dichas áreas de las hojas de cálculo suelen estar

indicadas con un fondo azul. En algún ejemplo se han incorporado

algunas cuestiones que podríamos catalogar en “excel avanzado” y que

aún estando presentes en los ficheros no se explican en este texto. En



todo caso, en el conocimiento y uso de un programa informático el

concepto de usuario novel y avanzado es relativo.

1. Hoja de cálculo, contenidos y formatos

1.1. Introducción

Excel cuenta con una serie de elementos comunes en su interfaz de

usuario. Las opciones de los menús, así como los botones que existen

en las herramientas, tienen un comportamiento dinámico.

Cada vez que se crea un nuevo libro de Excel nos encontramos con una

hoja de cálculo. Para añadir más hojas basta clicar en el botón “+”

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.



Nota: En determinadas circunstancias dos rangos separados por “;”

equivalen a la unión en un sólo rango, y dos rangos separados por un

espacio en blanco equivalen a la intersección de dichos rangos.

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 las

mismas.

Los menús son bastante intuitivos. Con el puntero o la rueda del ratón

puede desplazarse entre ellos. En el menú Archivo podremos abrir un

archivo, guardarlo, imprimirlo, así como cambiar algunas 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 propósito

introductorio de este texto. Si sabe el nombre de un comando y no lo



encuentra en los menús, puede buscarlo con la lupa (arriba de todo de

la cinta o ribbon de menús).

1.2. Textos, datos y fórmulas

En una celda podremos incorporar básicamente tres cuestiones. Textos,

datos y fórmulas. Usaremos el siguiente ejemplo para su descripción:

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

el contenido de celdas adyacentes. Por ejemplo, los textos que hemos

escrito en el rango B9:B15. En el ejemplo 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.

Excel resuelve las fórmulas según un orden de prioridad. Con

paréntesis puede cambiarse dicho orden. Respecto a los operadores

básicos, resuelve primero los exponenciales, después las

multiplicaciones y divisiones y por último las sumas y restas.

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 clica sobre el nombre de la

función en la mini ventana emergente accederá a ayuda sobre la

misma. Si clica sobre el icono de insertar función mientras está

editando la función abrirá el asistente de dicha función. Si el argumento

está entre corchetes es que es opcional. Los argumentos se separan por

“;”. Si sabemos el nombre de la función podemos invocarla tras poner el

símbolo “=”. Excel nos irá ayudando si no sabemos el nombre exacto

indicándonos las que tienen la raíz del nombre igual que lo que

vayamos escribiendo. Otra alternativa es invocar las funciones desde el

menú, en el botón de insertar función dentro del menú fórmulas lo que

nos llevará a pantallas de ayuda. En dichas pantallas podemos escoger

la categoría 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.

Pueden enlazarse celdas de diferentes hojas de las siguientes formas:

1. Escribiendo el nombre de la hoja origen seguido de “!” antes de la

referencia a la celda.

2. Cogiendo la celda de la otra hoja con el ratón. Pulse = en la hoja

destino y vaya con el ratón a la hoja origen.

3. Copiando y pegando como vínculo.

4. Con el botón derecho del ratón sobre la cruz de área seleccionada,

arrastrando e indicando pegar como vínculo al soltar (para ello es

necesario tener dividida la pantalla o duplicado el fichero para tener

ambas hojas a la vista).

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,

subrayado, con subíndices o superíndices, o cualquier combinación de

todos ellos.



Texto plano

Justificado derecha

Justificado izquierda

Centrado

Negrita

Cursiva

Color de texto

Subrayado

Doble subrayado

Tachado

Superíndice2

Subíndice4

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.

Números

Separador de miles 1.000.000

Separador de miles y decimales 1.000.000,00

Porcentaje y decimales 8,00%

Porcentaje sin decimales 8%

Negativo en rojo, y con signo -100.000

Ocultar los ceros

Negativo en rojo, y (), alineación 100.000,00

Negativo en rojo, y () (-100.000,00)

Fecha 07/05/2020

Independientemente de que tengamos textos o cifras, en las celdas

podemos incluir cuestiones como los bordes o los rellenos.



Rellenos

Bordes

Si lo deseamos podemos eliminar las finas líneas grises que delimitan

las celdas. Para ello en el menú Vista desmarcamos la opción “Líneas de

cuadrícula”. De esta forma sólo se verán las divisiones de celda que

nosotros hayamos diseñado explícitamente con la opción de bordes de

celda.

Antes de acabar este pequeño apartado acerca de los formatos vamos a

ver dos cuestiones que nos parecen de mucha utilidad y que usamos

habitualmente.

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.

Formato personalizado

Formato personalizado 1 Resultado 4

Formato personalizado 2 5 personas =2+3

Original 85.478.912

En miles 85.479 Miles

En millones 85,48 Millones

En el primer ejemplo le hemos indicado que en la celda 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. En

el tercer ejemplo forzamos que una cifra se vea en miles o en millones.

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 C36 y C37 ya no son números, son texto.

Las posibilidades de formato que tiene Excel son innumerables. Nuestra

recomendación general es que la sencillez y sobriedad debe primar para

facilitar la lectura. No obstante, puede haber ocasiones en que deba

usarse todo el potencial de variedades de formato.

1.4. Validación

A la hora de introducir datos, información, en la hoja de cálculo una

técnica interesante consiste en incluir 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 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

posibles se la indicamos referenciando el rango D10:G10.



En la versión de Excel en OneDrive, on line, la lista se va ajustando en

modo “autocompletado”.

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.



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 de 8 años. En D23 indicamos

(celda validada con número enteros entre 1 y 8) cuántos años 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.

También podemos realizar validaciones con referencias mixtas (vea

previamente el apartado de copiado y pegado de fórmulas).



En este caso le indicamos en el rango D28:F30 que el valor introducido

debe ser inferior al que esté en la misma columna en la fila 26. Por ello

bloqueamos la fila y no la columna (D$26).

Las validaciones pueden ser más elaboradas. Concluimos este apartado

con un ejemplo (resuelto con múltiples alternativas que exigen

conocimientos de diversas fórmulas que se verán posteriormente) en el

cual el usuario escoge una autonomía (validando con lista los nombres

de las CCAA de la fila 33) y a partir de ahí puede escoger el nombre de

alguna de las provincias que la componen a partir de otra lista

desplegable referida a la autonomía que haya escogido.

1.5. Formato condicional

En este repaso a algunas de las cuestiones de formato de Excel vamos a

ver las alternativas básicas de 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 e 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.

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.

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.

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.

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



En el XLS de referencia puede encontrar más ejemplos de formato

condicional. Algunos de ellos aplican formatos condicionales

predeterminados por Excel mientras que otros usan fórmulas más

complejas para su establecimiento. También se muestra un ejemplo de

concurrencia y priorización de reglas de formato condicional.

Mostramos a continuación algunos de dichos ejemplos, que no

comentaremos.





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

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

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

No queremos acabar esta sección sin indicar que el nuevo

comportamiento de Excel, con la técnica de Spilling facilita el realizar

cualquiera de las operaciones de los ejemplos anteriores introduciendo

las fórmulas en una sola celda y sin necesidad de copiar y pegar. Para

ello debe referenciar no una celda sino un rango. Excel hará el resto. En

general, esto también es aplicable a las funciones. Siempre que Excel

encuentre un rango donde esperaba inicialmente la referencia a una

sola celda, desbordará el resultado de la fórmula o función en el rango

equivalente necesario, tanto verticalmente como horizontalmente.



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

Para que el nombre tenga ámbito de una hoja concreta en vez de todo el

libro, indíquelo así al crearlo en el menú “Asignar nombre” o bien, si lo

crea directamente en la ventana de nombre, comience su nombre con el

nombre de la hoja seguido de un signo de admiración (!).

Para que el nombre se aplique a fórmulas que habíamos introducido

previamente usar el menú “Aplicar nombres”.

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, no recurrimos a

estas alternativas en este texto, aunque son de gran utilidad cuando se

hacen modelos para terceros, para que éstos puedan seguir con mayor

facilidad los cálculos.

Nota: Con un zoom inferior al 40% los nombres de rango se ven

superpuestos sobre las celdas que ocupan.

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 con referencia relativa

También es posible crear un nombre con referencia relativa, es decir,

que se refiera a una celda diferente en función de cual sea la celda



activa desde la que es invocado. En el siguiente ejemplo el nombre

Capital fue creado teniendo como ceda activa D28 y se refiere a C27. Es

el Capital de final del año anterior. Así, al calcular los intereses en cada

elemento de la columna D se aplica la tasa a la cifra que haya en la fila

anterior y una columna a la izquierda (C27 en este caso).

3.5. Otros usos de la técnica de asignación de Nombres

Si lo desea puede asignar un nombre directamente a un valor concreto

y no a una referencia de celda. En la casilla de “Se refiere a” indique un

valor (tras el signo igual) en lugar de la referencia a una celda.

Igualmente puede introducir una fórmula que incluya una función.



O una función en una fórmula que esté referenciada a una celda de

forma relativa:

En el siguiente ejemplo usamos referencias cruzadas a rangos de

columnas y filas creados “desde la selección”. Le invitamos a que lo

analice y vea las variantes de uso.



3.6. 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 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. Observe que en el listado se

incluyen los nombres de las CCAA que usamos en el último ejemplo del

apartado de validación de datos.

Nota: Algunos usuarios de Excel asignan nombres a celdas no para su

uso en fórmulas sino para usar la ventana de nombre como “ayuda a la

navegación”, como si fuera el índice de un libro.



4. Funciones aritméticas

Comenzamos aquí un nuestro repaso a un buen número de funciones

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. En algunos casos no seguimos

estrictamente la clasificación de Excel. Comenzamos con algunas

dentro de la categoría que hemos denominado aritméticas o de

matemáticas básicas. Son funciones de suma y recuento.

4.1. SUMA

La función SUMA (rango) permite la adición de todas las cuantías

contenidas en el rango de referencia:

Como vemos en el ejemplo, en la celda C11 le hemos indicado que sume

el contenido de C6 hasta C10 y luego hemos copiado/pegado hacia la

columna E. Podríamos haber puesto en C11 la siguiente fórmula, que

suma una a una las celdas = C6 +C7 +C8 +C9 +C10. Además de ser

más rápido con la función SUMA (aprovechando el icono Σ 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 (2020, 2021,

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.

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.

Vamos a aprovechar la función SUMA para mostrar dos cuestiones

referidas a la definición de rangos en Excel. Por un lado, si al indicar un

rango establecemos un “;” entre dos rangos, estaremos consiguiendo el

rango “unión” de los dos rangos previos. Si queremos el rango



“intersección” debemos aplicar un espacio en blanco entre los dos

rangos originales. Veámoslo con un ejemplo:

4.3. CONTAR, CONTARA y CONTAR.BLANCO

Una fórmula que cuenta devuelve el número de celdas de un rango

específico que reúne ciertos criterios.

- CONTAR. Devuelve el número de celdas de un rango que contiene

valores numéricos.

- CONTARA. Devuelve el número de celdas de un rango que no están

en blanco (una celda puede contener texto).

- CONTAR.BLANCO. Devuelve el número de celdas en blanco de un

rango.

En el ejemplo anterior, se puede observar que son 8 las celdas del

rango. De ellas, 5 tienen números, 6 tienen alguna información y 2

están en blanco.



4.4. SUMAR.SI y CONTAR.SI

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

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

ejemplo:

Como podemos observar, tenemos una serie de cuantías y una serie de

vendedores responsables de las mismas. Para calcular la cuantía total

que corresponde a Luis introducimos en la celda F40 la expresión

=SUMAR.SI($C$51:$C$60;E51;$B$51:$B$60). Esta suma condicional

requiere en primer lugar el rango en el que se encuentra la condición a

verificar, en este caso C51:C60, 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 E51,

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 B51:B60, el cual fijamos para facilitar el copiado/pegado hacia

abajo.

Al igual que la función SUMA, con SUMAR.SI podemos insertar nuevas

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

En el caso de CONTAR.SI, cuenta las celdas que cumplen la condición

analizada. En nuestro caso queremos saber el número de operaciones

que ha hecho cada vendedor. Sólo necesitamos el rango de celdas a



comprobar (el mismo que en la función SUMAR.SI) y el criterio que se

desea comprobar (en el caso de las operaciones de Luis, E56).

También podemos (F60) poner una condición del estilo “>=”. En este

caso para contar el número de operaciones que igualan o superan la

cuantía de 400.

En el siguiente ejemplo usamos CONTAR.SI para chequear si un

nombre está repetido en una lista y también para ver si un nombre de

una lista está presente en otra. Además, hemos incorporado las mismas

fórmulas en formato condicional de forma que también nos muestra el

resultado visualmente.

4.5. SUMAPRODUCTO

Esta función, de gran utilidad, 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. Los rangos han de ser

horizontales o verticales en ambos casos. No es posible mezclar rangos

verticales con horizontales. Los rangos pueden tener varias columnas o



varias filas. Por último, pueden ser más de dos rangos los que se

multiplican y suman.

4.6. SUMAR.SI.CONJUNTO y uso avanzado de SUMAPRODUCTO y SUMA

En este apartado vamos a ver la función SUMAR.SI.CONJUNTO así

como un uso avanzado de las funciones SUMA y SUMAPRODUCTO.

Quizás esta última cuestión puede esperar a verla una vez que haya

visto las funciones lógicas. La función SUMAR.SI.CONJUNTO permite

realizar sumas chequeando más condiciones que la función SUMAR.SI,

que sólo permite analizar un criterio.

En nuestro caso queremos analizar dos criterios, el vendedor y si las

ventas son nacionales o exteriores. En el rango G89:H91 situamos la

solución usando SUMAR.SI.CONJUNTO. El primer argumento es el

rango a sumar, en nuestro caso el rango B88:B106. 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 G89 al resto de celdas.



Además, hemos incorporado tres métodos alternativos más para

conseguir el mismo resultado. En un caso con un uso avanzado de la

función SUMA y en otros dos casos con SUMAPRODUCTO.

Por último, también hemos incorporado una interesante aplicación de

SUMAPRODUCTO que permite determinar el agregado de varios

elementos sobre el mismo criterio (en este caso queremos las ventas de

Luis más David). En estos ejemplos el signo “+” trabaja como una

función “O” y el signo “*” como una función “Y”.

4.7. COCIENTE y RESIDUO

Tal como su nombre indica estas funciones muestran el cociente y el

residuo de una operación de división. Es preciso indicar como

argumentos el numerador y el denominador.

4.8. EXP, POTENCIA y PI

La función EXP calcula el resultado de elevar el “número e” a la

potencia que se le indique, que es el único argumento solicitado.

La función POTENCIA eleva el número indicado (primer argumento) a la

potencia indicada (segundo argumento).

Por último la función PI, sin argumentos, nos devuelve el número PI,

3,1415…..



4.9. PRODUCTO y SUMA.CUADRADOS

La función PRODUCTO calcula el producto de todos los valores

incluidos en el rango de argumentos. SUMA.CUADRADOS, eleva al

cuadrado cada uno de los valores del rango y posteriormente los suma

(como si usásemos SUMAPRODUCTO indicando dos veces el mismo

rango).

4.10. AGREGAR Y SUBTOTALES

Se trata de funciones que pueden hacer diferentes operaciones. El

primer argumento que hay que indicarles es la operación deseada. En

nuestro caso le vamos a indicar la 9, que es la SUMA en los dos casos.

Su particularidad es que cuando se ocultan filas o cuando alguna fila

no está en un rango de filtrado, su valor no se incluye en el resultado.

En el primer caso ocultamos las filas 3 a 6. Son un total de 10

elementos con un valor de 10 cada uno. Están seis a la vista y el

resultado de la Suma es 60.

En el segundo caso con filtrado ocultamos los elementos 2, 4 y 6.

Quedan siete elementos y el resultado es 70.



Manejando las opciones de la función AGREGAR se puede conseguir,

por ejemplo, que ignore los elementos de error:



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 MAX.SI.CONJUNTO,

MIN.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO (y PROMEDIO.SI), 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

SUMAR.SI.CONJUNTO).

Posteriormente veremos brevemente las funciones de Bases 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).

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

Con estas funciones se extrae la desviación típica, y la varianza, tanto

muestral como poblacional.



Sólo es preciso indicar el rango en el cual se encuentra la serie de datos

del que queremos conocer la varianza o la desviación típica.

5.3. Funciones de covarianza y correlación

Para conocer la covarianza poblacional o muestral es preciso indicar los

rangos de las dos series de datos implicadas. El orden es indiferente. De

igual forma el coeficiente de correlación (COEF.DE.CORREL) precisa

que le indiquemos, de forma indistinta, los rangos de las dos series de

datos.

5.4. JERARQUIA

La función JERARQUIA o JERARQUIA.EQV 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. Observe que el valor

430 está repetido y comparte la jerarquía 4, no existiendo la jerarquía 5.

Existe una variante JERARQUIA.MEDIA, que otorgaría a ambos valores

la jerarquía 4,5.

5.5. K.ESIMO.MAYOR

La función K.ESIMO.MAYOR nos indica el valor que ocupa una

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

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.

Mostramos en G63:I63 una interesante técnica, que aprovecha los spill

over o desbordamientos de Excel, en este caso para desbordar los tres

valores más altos. Separamos con “\” los puestos buscados (1, 2 y 3), y



los establecemos dentro de una llave. Si los separamos por “;” el

resultado será un vector columna en vez de fila. También puede usar la

nueva función SECUENCIA para generar la serie 1,2,3.

5.6. K.ESIMO.MENOR

De forma análoga a la anterior, la función K.ESIMO.MENOR nos indica

el valor que ocupa una determinada posición jerárquica dentro de una

serie, de menor a mayor.

Debemos indicar en primer lugar la serie de datos y en segundo lugar

que orden jerárquico, de menor a mayor, buscamos. En nuestro caso el

valor de orden 1 es 230, el de orden 10 es 650.

También mostramos la técnica de desbordamiento. En este caso le

indicamos que queremos mostrar el décimo, noveno y octavo, en ese

orden.

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. DISTR.NORM.N

Esta función nos indica la probabilidad acumulada de un determinado

valor dentro de una función normal y dadas su media y su desviación

típica.

Debemos indicarle en primer lugar el valor buscado, a continuación la

media, luego la deviación típica, y por último, con la opción

VERDADERO le indicamos que queremos la probabilidad acumulada.

5.9. INV.NORM

Esta función nos indica el valor que deja una determinada probabilidad

acumulada a su izquierda dentro de una función normal y dadas su

media y su desviación típica.



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. MEDIANA, MODA y MODA.VARIOS

A continuación, mostramos el cálculo de la MEDIANA y la MODA de

una serie de datos. Sólo es necesario indicar el rango de los mismos.

La variante MODA.VARIOS, usa la técnica del desbordamiento para

mostrarnos el resultado cuando una serie de datos tiene varios valores

moda.



En nuestro ejemplo los valores 1, 4 y 7 comparten la moda, al estar

presentes dos veces en la serie de datos.

5.11. PERCENTIL Y RANGO.PERCENTIL

Dados los valores de una serie la función PERCENTIL o PERCENTIL.INC

nos indica el porcentaje de los mismos que son inferiores a un valor

determinado que se analice. (En la figura hemos ocultado las filas entre

16 y 102).

Como vemos, debemos indicar en primer lugar el rango de datos y

posteriormente el valor que queremos testar, en nuestro caso el de la

celda E7.

La función RANGO.PERCENTIL o RNAGO.PERCENTIL.INC nos indica el

valor que dentro de una determinada serie deja por debajo el porcentaje

que nosotros queramos comprobar. Como vemos, debemos indicar en

primer lugar el rango de datos y posteriormente el porcentaje que

queremos testar, en nuestro caso el de la celda E12.

5.12. FRECUENCIA

La función FRECUENCIA hace un recuento de los valores que se

encuentran dentro de terminados intervalos. En el siguiente ejemplo la

aplicamos a los datos usados con la función PERCENTIL. Establecemos

como intervalos las decenas de 0 a 100.



Demos indicar el rango de datos y el rango de intervalos. Observe que

hemos indicado en el rango de intervalos sólo hasta la celda D26. Excel

pondrá en la siguiente celda (E26) el recuento de todos los valores que

superen el último intervalo, en nuestro caso 90. Esta función se

introduce en E18 y se desborda automáticamente a todo el rango.

6. Miscelánea

En este apartado veremos algunas funciones de diferentes categorías

que nos pueden ser útiles, incluidas las operaciones con fechas.

Pertenecen a diferentes categorías de funciones de Excel.

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

ponerse la almohadilla (#), o poner un 0 en su lugar.

6.2. RAIZ

Extrae la raíz cuadrada de un número.



Sólo precisa como argumento la referencia a la celda donde se

encuentra el número en cuestión.

6.3. ABS, ENTERO, REDONDEAR

Estas funciones nos permiten obtener el valor absoluto de un número,

su raíz entera o bien redondearlo (más allá de cuestiones meramente de

formato).

En el caso de REDONDEAR es preciso indicarle a cuántos decimales

queremos que se realice el redondeo. El redondeo se hace desde 0,5

(incluido) hacia arriba a la cifra siguiente y de 0,5 (no incluido) hacia

abajo a la cifra anterior. Existen variantes para redondear siempre por

arriba, por abajo, números pares o números impares.



6.4. Funciones de números aleatorios

La función ALEATORIO, que no requiere ningún argumento, nos da un

número aleatorio entre 0 y 1 cada vez que Excel refresca sus cálculos.

Para forzar la actualización de Excel, pulse F9.

La función ALEATORIO.ENTRE nos da un número aleatorio entre un

valor inferior y otro superior que le hayamos indicado. Esos son los dos

argumentos que deben incorporarse a la función, y en ese orden. Esta

función no opera con datos decimales. En la figura, 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. En

C35 mostramos otra alternativa para obtener un número aleatorio entre

dos cifras decimales.

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

nueva función de Excel MATRIZALEAT.



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úmeros sean enteros.

6.5. TRANSPONER

La función transpone permite convertir un rango o vector horizontal en

vertical o viceversa.

Tenemos una serie de datos en un vector horizontal (C50:G50).

Deseamos transponerlos en vertical en el rango B53:B57. Para ello en

B53 introducimos la función TRANSPONER indicando el rango de

origen. El spill over de Excel hará el resto. Podemos volver a obtener un

rango horizontal transponiendo el rango B53:B57 en C59:G59,

siguiendo el mismo procedimiento. En la columna H mostramos como

usar la función INDICE combinada con la función SECUENCIA para

realizar la transposición.

6.6. La función SECUENCIA

Esta función es una de las novedades de Excel en 2019. Permite

generar rápidamente una matriz de datos. Le indicamos el número de

filas y columna, el elemento inicial y el paso entre cada dos elementos.

El único argumento obligatorio es el número de filas. Los demás si no se

indican tomarán valor 1.



6.7. Las funciones MULTIPLO.SUPERIOR.MAT y MULTIPLO.INFERIOR.MAT

Estas funciones calculan el múltiplo de una cifra de referencia más

próximo a un número dado, ya sea por encima (SUPERIOR) o por

debajo INFERIOR). En el caso de los números negativos puede indicarse

el sentido del redondeo (hacia 0, si no se le indica nada, o alejándose de

0, si se añade el argumento -1).

6.8. Otras funciones de REDONDEAR, PAR, IMPAR, MAS, MENOS, MULT

Hemos visto anteriormente la función REDONDEAR. Existen diversas

variantes que redondean al siguiente PAR, IMPAR, hacia arriba (MAS) o

hacia abajo (MENOS). En estos dos últimos casos es preciso indicar el

número de decimales. En K83 mostramos como redondear una cifra a

millares con el argumento -3.



Mención aparte merece la función REDOND.MULT similar a la función

MULT.INFERIOR.MAT, pero sin versatilidad en los números negativos.

6.9. TRUNCAR vs ENTERO

Anteriormente hemos visto la función ENTERO que nos devuelve la

parte entera de un número, hacia abajo. Si el número es negativo, por

ejemplo – 4,5, el resultado de la función ENTERO es -5. La función

TRUNCAR nos devuelve la cifra eliminándole los decimales.

6.10. Operaciones con fechas, DIAS

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.

La función DIAS nos indica la distancia en días entre dos fechas. Para

que el resultado sea positivo primero debemos indicar la fecha posterior

y luego la anterior.

6.11. HOY, AHORA y FIN.MES

Existen numerosas funciones de fecha. Mostramos dos que suelen ser

muy útiles. La función HOY, que no necesita argumentos, nos indica la

fecha del día en el que nos encontramos. La función AHORA incluye

además del día, la hora, minutos y segundos. Por su parte, la función

FIN.MES nos indica el último día del mes una serie de meses a partir de

una fecha concreta. Es preciso indicarle en primer lugar la fecha de

referencia y en segundo lugar el número de meses hacia delante

(número positivo) o hacia atrás (número negativo) del que queremos

saber el último día del mes.

Veamos el uso de la función AHORA para “sellar” el momento en el que

se incorpora un dato.



En la columna C aparecerá la primera vez que se incluya algo en la

columna B, mientras que en la columna D aparece la fecha en la que se

modifique por última vez. Para que funcione esta técnica debe estar

habilitado el cálculo iterativo.

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

En el siguiente ejemplo extraemos de una fecha el año y el trimestre al

que pertenece usando las funciones AÑO, MES y ENTERO.

También podemos usar REDODNEAR.MAS como alternativa a la

función ENTERO.

6.13. FECHA.MES, FECHA Y FECHANUMERO

Por su parte, la función FECHA.MES nos indica el mismo día del mes

una serie de meses a partir de una fecha concreta. Es preciso indicarle

en primer lugar la fecha de referencia y en segundo lugar el número de



meses hacia delante (número positivo) o hacia a atrás (número negativo)

que queremos avanzar o retroceder en el tiempo.

La función FECHA establece la fecha una vez se le indiquen como

argumentos el año, el mes y el día.

La función FECHANUMERO nos indica el número al que corresponde

una determinada fecha introducida como texto y entre “”. En el ejemplo

hemos vinculado el resultado a una celda adyacente en la que hemos

establecido un formato fecha para que podamos comprobar que la cifra

obtenida con FECHANUMERO efectivamente se refiere a la fecha

indicada.

La función TEXTO es de gran utilidad para trabajar con fechas pues

permite ver una fecha en muy diversos formatos. Mostramos un ejemplo

en el que con la función TEXTO visualizamos le día, día de la semana,

mes, año, o fecha completa de una determinada fecha:



En el fichero Excel de referencia puede usted encontrar muchas más

variantes así como el uso para los formatos de hora.

7. Funciones lógicas

Una de las cuestiones más interesantes de los programas de hoja de

cálculo es la posibilidad de introducir funciones lógicas en las fórmulas.

De alguna forma ayudan a romper la “linealidad” de los cálculos.

Existen diferentes funciones condicionales predefinidas en Excel (NO, Y,

O, SI).

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

Empecemos con un en ejemplo sencillo. Deseamos calcular las

comisiones en una serie de operaciones. La comisión alcanza el 5% de

la cuantía de la operación, pero si dicha cuantía es inferior a 1.000 debe

aplicarse una comisión mínima de 50. Veamos una forma de resolverlo:

La función condicional opera con tres elementos o argumentos dentro

del paréntesis: la condición que se analiza, resultado si se cumple y

resultado si no se cumple. En nuestro caso hemos indicado que

compruebe si la cuantía de C8 es inferior a la mínima de C6. Si se

cumpliera dicha condición el resultado habría de ser el contenido de C5



(la comisión mínima) y si no se cumple, esto es, si la cuantía supera la

cifra de 1.000 entonces debe multiplicarse la cifra en cuestión por la

comisión de C4. Por otro lado utilizamos los símbolos $ para permitir

copiar la fórmula hacia la derecha y que opere para las demás cuantías.

La única celda que no fijamos es precisamente la de la cuantía de la fila

8 sobre la que se desea calcular la comisión.

En general todas las fórmulas que se desarrollen con la función SI

responderán a este sencillo esquema, si bien pueden hacerse más

complejas en la medida en la que los argumentos se hagan más

complejos e incorporen, por ejemplo, nuevas funciones condicionales,

que quedarán anidadas. Dentro de un momento le presentaremos un

ejemplo de esto último, pero permítanos indicarle una forma sencilla de

evitar la condicional del ejemplo anterior usando otra función de Excel,

la función MAX.

7.2. La función MAX como alternativa

Dicha función devuelve el valor máximo de los contenidos en una serie

de argumentos. Veámoslo:

Introducimos dos argumentos en la función: por un lado la cuantía de

comisión mínima, $C$14, y por otro lado la comisión calculada como

porcentaje de la cuantía de la operación, $C$13*C17; cuando la

comisión calculada sea superior al mínimo, esa será la respuesta de la

fórmula, pero cuando sea inferior, la fórmula devolverá la cuantía de

comisión mínima pues será superior a la calculada.



Muchas operaciones de una sola condicional pueden simplificarse de

forma similar a la que acabamos de mostrarle, ya sea con la función

MAX o la función MIN, que opera de forma similar devolviendo el valor

mínimo de una serie de elementos.

7.3. Uso de la función SI anidada

En este caso queremos calcular los impuestos de varias empresas en un

sistema fiscal que aplica un 30% de tasa impositiva a los beneficios que

no alcancen los 90.000 euros y un 35% a los que excedan dicha cifra.

Por supuesto el impuesto ha de ser cero si los beneficios son negativos.

Hay multitud de formas de solucionar una cuestión como esta. Vamos a

hacerlo anidando dos condicionales:

Como se puede observar la primera condicional indica que si el

beneficio es menor a cero, los impuestos serán cero. A partir de ahí, de

no cumplirse esta premisa, es decir, si los beneficios fueran positivos,

se abre la siguiente condicional. Esta segunda condición pregunta

acerca de si los beneficios son inferiores o no al tramo de aplicación de

la tasa alternativa; si son inferiores a dicha cifra, aplicaremos la tasa de

C23 a la cifra de beneficio. Si fueran superiores al tramo de aplicación

de la tasa alternativa entonces el impuesto se compondría de dos

sumandos; en el primero de ellos aplicamos la tasa alternativa a ese

tramo de aplicación, mientras que en el segundo sumando aplicamos la

tasa impositiva general al exceso de beneficio sobre el tramo de

aplicación del mínimo. Los símbolos $, como siempre, nos ayudan a

fijar las posiciones que no deben variar al copiar/pegar la fórmula hacia

la derecha, para aplicársela al resto de empresas. Evidentemente la

primera condición podría eliminarse aplicando la función MAX, como se

muestra en la fila 29.



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



En nuestro caso queremos comprobar en qué duplas de valores, o bien

A o bien B (al menos uno de ellos) superan los mínimos establecidos.

Esa circunstancia se da en los tres primeros pares de valores que

muestran el resultado VERDADERO.

7.7. Función NO

La función NO devuelve VERDADERO o FALSO cuando no se cumple o

sí se cumple una condición. Nos da el resultado contrario de lo que se

pregunta.

En nuestro caso nos indica FALSO en la pregunta de si 50 supera a 40

y sin embargo nos indica VERDADERO al chequear 10 frente a 40.

7.8. Función SI.ERROR y SI.ND

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.



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.

Por su parte la función SI.ND es similar a SI.ERROR, sólo que en este

caso opera cuando Excel muestra como resultado de una operación

#N/D, por no poder encontrar un resultado en una operación. Veamos

un ejemplo.

Hemos usado BUSCARV para encontrar las ventas de los meses

indicados. Como en la lista no está Enero, la función devolvería #N/D,

tal como se ve en la celda C98. Como respuesta alternativa le hemos

indicado “No se encuentra”, que es la respuesta obtenida en C97.

7.9. Función SI.CONJUNTO

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

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

Hay que tener en cuenta que en este caso todas las opciones deben ser

indicadas, es decir no permite dejar la condicional con una respuesta

abierta para cuando no se cumpla ninguna de las condiciones. La

nueva función CAMBIAR, que veremos posteriormente, es ligeramente

mejor para nuestro ejemplo.

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.

8.1. ELEGIR

Esta función nos permite seleccionar un dato de entre una serie de

datos siguiendo una posición, o índice, en la serie que se haya indicado.

En el ejemplo contamos con una serie de compras, en las celdas C7:C15

y deseamos establecer los pagos por las mismas según la demora que se

establezca en C4.



Observe que la función elegir establece como primer argumento el

número índice que se utilizará para extraer el dato de la serie. En este

caso $C$4 +1; el sumarle 1 es necesario para permitir que la fórmula

funcione con una demora en pagos de cero, pues la función no admite

el cero como índice pues ningún dato representa la posición cero en la

serie. Lo fijamos con los $ para poder copiar hacia abajo. El resto de

argumentos son las celdas que deben situarse en el orden de extracción

que indicaremos con el índice: en primer lugar nuestra posición 0, esto

es pago al contado, que será la primera posición de la serie, C11, y a

continuación pago a un mes, C10, y así sucesivamente. No fijamos

posiciones en la serie para que se adapten al siguiente mes a medida

que copiemos/peguemos hacia abajo.

8.2. La función DESREF

La función DESREF es, a nuestro modo de ver, una de las funciones

menos conocidas y sin embargo más útiles. Nos permite invocar una

celda a un número de filas de distancia y otro número de columnas de

distancia de una celda referencia que indiquemos. Apliquémoslo a

nuestro ejemplo de compras y pagos.



En este caso indicamos que la celda de referencia son las compras del

propio mes y que debemos coger una celda situada 2 filas arriba (el

símbolo menos antes de $C$19 indica que hay que desplazarse hacia

arriba en las filas; si fuese positivo nos desplazaríamos hacia abajo) y

en la propia columna C, pues indicamos 0 columnas (si el número de

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. En la columna

G hemos usado DESREF marcando la referencia inicial como un vector,

lo que ha generado un spill over.

Veamos un ejemplo en el cual DESREF trabaja en columnas en vez de

en filas.

Ahora el cero se lo ponemos al indicador de filas.

8.3. Función DESREF al detalle

Veamos un ejemplo de uso de DESREF extrayendo de una matriz una

celda a partir de la celda central siguiendo las indicaciones de filas y

columnas que se le hagan.



Como vemos en la celda C45 le indicamos que extraiga desde la

posición D49 (la celda referencia) 0 filas (C43) hacia abajo y dos

columnas (C44) a la izquierda (por el signo menos). El resultado es,

naturalmente, “celda 0.-2”.

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 B59 e incluya

el número de celdas indicado en C55. Como vemos, el cierre del rango,

iniciado en B59, lo establece DESREF apoyándose en B58, indicando

que el número de filas es el establecido en C55 y el número de

columnas 0. La función INDICE, que veremos posteriormente, podría

hacer la misma tarea. En G56 mostramos otra alternativa, usando

DESREF directamente, no como parte de un rango.



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 ejemplo, usamos COINCIDIR y

COINCIDIRX para encontrar el lugar que ocupa en la serie el valor

indicado.

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.

En ambos casos, dentro de la función COINCIDIR tras marcar el rango

de búsqueda añadimos el valor 0, para indicar que la búsqueda tiene

que ser exacta, esto es que no busque un valor que se aproxime sino

justo el valor indicado. Esta función puede crearnos problemas si el

valor buscado está repetido en la serie, pues nos dará la referencia de la

primera vez que lo encuentre.



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

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

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 F109, le indicamos que busque el valor mínimo de la

columna en la que tenemos las rentabilidades, esto es,

MIN(B107:B114). A continuación, le indicamos la matriz que contiene

toda la información (B107:C114). 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

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

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:

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

equipo y su nacionalidad. En C167 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 C167:E167, 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 C167.

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 188 nos devuelva los datos del año elegido y de

los países elegidos. En C188 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 B188. La

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

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

En C191, usamos BUSCARX por triplicado para encontrar el año con el

valor máximo del país seleccionado.

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

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

C196:E200. 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 fila 3, columna 2.

Añadimos un par de ejemplos más. En la fila 205 incluimos la función

INDICE dentro de una SUMA. El argumento 0 para indicar las filas hace

que las recoja todas. En la fila 206 al señalar incialmente un vector fila

puede omitirse el argumento de fila y el argumento que se incluya ya

será la columna.

En el siguiente ejemplo usamos INDICE como parta de una referencia

de forma que el resultado será la direción de la celda en vez de su valor.

Ello permite que la función SUMA obtenga el resultado deseado.

Veamos ahora un interesante ejemplo de uso de INDICE y COINCIDIRX

para una búsqueda combinada.



Usando el ampersand (&) COINCIDIRX nos da la posición de la fila que

se corresponde a Cristina en la columna B y Lugo en la C. INDICE,

aplicado sobre la columna D, nos indica la cuantía. Es una alternativa a

la función SUMAR.SI.CONJUNTO.

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 C246 nos informe del valor del escenario indicado en C245.

En la función CAMBIAR en primer lugar le indicamos que en C245 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, FILAS y COLUMNAS

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. Por su parte la función FILAS

indica el número de filas presentes en un rango y la función

COLUMNAS el número de columnas de un rango.

8.7. La función INIDRECTO

La función INDIRECTO nos permite obtener acceso a una determinada

información de forma “indirecta”. 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 C259 ponemos la dirección de celda B263. En C260,

con la función INDIRECTO invocada sobre la celda C259 obtenemos el

resultado de la celda B263. También tiene la opción de que la dirección

de celda se indique en modo F1C1 (en lugar del habitual modo A1).

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

invocando nombres.



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

correspondientes. En C275 indicamos uno de ambos nombres. En E278

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

Podríamos (ver columna H) haber puesto el nombre directamente.

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 F289

introducimos la función ORDENAR indicando en primer lugar la

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

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á (spilling o derrame) a todo el rango necesario para cubrir

automáticamente toda la información.

Si sólo deseamos que aparezca la columna de nombres en la respuesta:

Si sólo deseamos que aparezcan los cuatro primeros (en nuestro caso de

menos a más edad) en la respuesta:

Y si deseamos una ordenación aleatoria:



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 F317 (Lugo, en el ejemplo). Nos situamos en F319 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 (F317, 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). En el ejemplo

mostramos como hemos anidado la función FILTRAR dentro de un

PROMEDIO.

Si se desea, el criterio de filtrado puede escribirse directamente (en el

ejemplo con Orense) sin referenciarlo a otra celda.



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

ver si se cumplen dos criterios simultáneamente los multiplicamos, de

forma que si el resultado es VERDADERO será porque ambos criterios

lo son:

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

de menos de 30 años. En B336 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 C333 (Lugo)

por el rango de la columna de edades indicando “<” que la celda C334

(30 años). Cuando se cumplan ambos criterios será VERDADERO y ese

registro se incorporará al resultado.

También podemos filtrar en vez de por una provincia, por dos. Para ello

usamos el operador + (que aquí funciona como una “O””)



También podemos filtrar un rango sin que aparezca la columna de

filtrado en la solución.

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 B357

insertamos la función ORDENAR y dentro la función FILTRAR. Con

FILTRAR obtenemos los registros (filas) en las que la edad supera 40

(celda C355). Y después ordenamos por nombre (columna 1 de la tabla)

y en orden ascendente (1).



8.10. La función UNICOS

La función UNICOS elimina los elementos duplicados de un rango.

En el primer caso, columna E, nos devuelve una sola vez cada uno de

los elementos. Los elementos duplicados aparecen una sola vez. En el

segundo caso, columna G, nos devuelve sólo aquellos elementos que

aparecen una sola vez en la lista original. Los duplicados no aparecen.

La función UNICOS puede buscar “combinaciones únicas” si trabaja

sobre varias columnas (pruebe a cambiar los datos de partida del

ejemplo):

La función UNICOS puede combinarse con la función ORDENAR.



En este caso ordenamos los nombres sin que aparezcan duplicados.

La función UNICOS puede trabajar sobre un par de rangos

simultáneamente. Observe la siguiente aplicación, combinada también

con ORDENAR.

UNICOS trabaja con las dos columnas gracias al uso de &. Además,

hemos aprovechado para poner una coma que separe apellidos y

nombre. Con ORDENAR, los ordenamos por orden alfabético de

apellido.

También podemos combinarla con SUMAR.SI con una característica

interesante disponible en las versiones actuales de Excel:



En la columna E, con UNICOS tenemos los nombres de los comerciales

una sola vez. En la columna F, con SUMAR.SI tenemos las ventas

totales de cada comercial. Lo interesante es que la función desborda

desde la celda F421 sólo hasta donde haga falta. Por ello hemos

indicado en el rango E421#. En este caso acabará en E427, pues debajo

ya no hay nada y no devolverá ceros a partir de F428.

8.11. Algunas combinaciones más

Vamos a ver algunas combinaciones más de funciones. Comencemos

por la función BUSCARX y la función SUMA.

Se busca un rango (no una celda) en otro rango lo que permite envolver

el resultado dentro de una SUMA.

También podemos poner a trabajar simultáneamente la funciones

ORDENAR, UNICOS y FILTRAR. Con FILTRAR nos quedamos con



aquellos registros de color Azul. Con UNICOS eliminamos las

repeticiones y con ORDENAR los ordenamos alfabéticamente.

En el siguiente ejemplo usamos dos alternativas para extraer los datos

de una serie de registros una serie de meses.

La primera alternativa es usar la función BUSCARX. La segunda

combina las funciones INDICE, COINCIDIRX y SECUENCIA. La primera

alternativa es más sencilla, pero hay que copiarla a lo largo de las filas.

La segunda alternativa es más compleja pero requiere ser introducida

sólo una vez.



8.12. Búsquedas y ordenaciones complejas

A veces se buscan ordenaciones más complejas, en las que hay que

usar otras alternativas. En los próximos ejemplos no mostramos las

formulaciones usadas. Invitamos al lector a que las vea en los XLS que

acompañan este texto.

En el siguiente ejemplo tenemos un rango de datos, en los que algunas

celdas están vacías. En primer lugar, queremos recolocarlos omitiendo

dichas celdas vacías. En segundo lugar queremos invertir su posición,

sin incluir las celdas vacías. Por último, queremos invertir su posición,

incluyendo las celdas vacías. Mostramos varias alternativas, más o

menos complejas, para obtener el resultado deseado.

Muchas veces se quiere ordenar una serie (por ejemplo, de nombres)

pero cambiando la letra de inicio. Veamos alguna solución:

Otra situación interesante se da cuando deseamos obtener de un

registro de datos el valor de la primera aparición en una serie, o la

última o alguna intermedia. El siguiente ejemplo muestra cómo

podemos hacerlo. Incluimos un desglose por pasos.



En el siguiente ejemplo mostramos como usar las funciones FILTRAR y

ORDENAR sobre una matriz, filtrando los elementos vacíos.

8.13. Uso de factores comodín

Al realizar búsquedas Excel puede usar factores comodín, (* , ?).

Básicamente el “*” sustituye a cualquier texto, mientras que el “?”

sustituye un carácter. En el siguiente ejemplo mostramos su uso con

diferentes funciones. Es de destacar que en el caso del uso con las

funciones de Bases de Datos los resultados difieren respecto al uso en

el resto de funciones. Invitamos al lector a que analice el

funcionamiento de dichos comodines.



9. Funciones de Texto e Información

Vamos a ver en este apartado las funciones de Texto e Información.

Comencemos por las 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):

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.

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.



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.

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.

9.13. Funciones CARACTER y CODIGO

La función CARACTER indica el carácter tipográfico vinculado a un

número del código usado por el ordenador y la función CODIGO indica

el código de un determinado carácter tipográfico. Veamos algunos

ejemplos.



9.14. Funciones REEMPLAZAR, SUSTITUIR, REPETIR y T

La función RREMPLAZAR reemplaza dentro de un texto a partir de una

posición, un determinado número de caracteres, con el texto que se le

indique. En el ejemplo reemplazamos empezando en el tercer carácter y

un solo carácter por un “-“. De esta forma la A de la tercera posición

desaparece y en su lugar tenemos el guion.

La función SUSITUIR sustituye dentro de un texto el texto que se

indique con un nuevo texto, en todas sus apariciones o en la que se le

indique. En el primer caso ha sustituido las dos A por el guion (al no

haber especificado otra cosa). En los otros dos casos sólo realiza la

sustitución en la aparición indicada (la segunda o la primera).

La función REPETIR repite un determinado texto el número de veces

que se le indique.

Por último, la función T devuelve el Texto de una celda si dicha celda

contiene un texto y devuelve vacío (“”) si dicha celda no contiene un

texto, es decir si contiene un número o una fórmula.



9.15. Combinación de funciones TEXTO

Las funciones de Texto pueden combinarse de forma muy diversa lo que

les otorga una gran operatividad. Vamos a ver algunos ejemplos.

La primera combinación nos permite presentar diferentes elementos y

sus cuantías de forma que la línea de unión generada con el guion bajo

se ajuste.

En el siguiente ejemplo, combinando funciones podemos obtener los

diferentes bloques de contenido de un texto que incluye cifras, letras y

un guion.

Veamos ahora una aplicación cuando tenemos tres elementos en una

celda separados por guiones.

En el siguiente caso usamos una compleja combinación para convertir

un nombre seguido de dos apellidos en dos apellidos y un nombre



(incorporando una coma). La función nos advertirá en caso de que el

nombre o alguno de los apellidos sea compuesto.

La fórmula en D146: = SI(LARGO(SUSTITUIR(B146;" ";"**"))-

LARGO(B146)>2;"Nombre y/o Apellido compuesto";DERECHA(B146;LARGO(B146)-

ENCONTRAR(" ";B146))&", "&IZQUIERDA(B146;ENCONTRAR(" ";B146)))

A continuación, vemos, paso a paso, y también ensambladas, las

funciones que nos permiten obtener el nombre de la hoja en la que

estemos trabajando.

La función CELDA (que veremos posteriormente) nos da la ruta del

archivo y la hoja. ENCONTRAR nos permite localizar el corchete que

cierra el nombre del archivo, a partir del cual con EXTRAE obtenemos el

nombre de la hoja (con un máximo de los 31 caracteres permitidos).

A continuación, vemos la alternativa para extraer el nombre de un

fichero.

En el fichero Excel de referencia el lector puede encontrar algunas

aplicaciones más.

Pasamos a continuación a las funciones de la categoría de Información.

Dichas funciones nos aportan información útil sobre diferentes ámbitos,

el sistema, el archivo, o una celda.



9.16. Las funciones INFO, HOJA, HOJAS y CELDA

INFO nos devolverá diferente información sobre el sistema, el directorio,

etc, en función del argumento que se le indique: el directorio en el que

se encuentra el archivo en uso, la primera celda visible, la versión del

sistema operativo, el modo de cálculo, la versión de EXCEL y el entorno

operativo. Veámoslo:

La función HOJA indica el número de la hoja dentro de un archivo y la

función HOJAS indica el número de hojas de un archivo.

La función CELDA nos aporta información de una celda según el

argumento indicado:

En el ejemplo mostramos la dirección de la celda, su contenido, la

columna y la fila en la que se encuentra, el tipo de información (en este

caso devuelve una r, de rótulo) y el nombre del archivo al que pertenece.

9.17. Las funciones ES.PAR, ES.IMPAR, Y ESBLANCO

Estas funciones nos devuelven VERDADERO o FALSO si una celda de

referencia contiene un número par, impar, o está en blanco,



respectivamente. Observe que la celda en blanco es considerada como

par.

9.18. Las funciones ESERROR y ESERR

Estas funciones devuelven VERDADERO o FALSO en función de que en

una celda haya un error (ESERROR) o un error distinto de #N/D

(ESERR).

9.19. Las funciones ESFORMULA, ESTEXTO, ESNOTEXTO, ESNUMERO,

ESNOD, ESLOGICO y NOD

Estas funciones devuelven VERDADERO o FALSO, en función de que el

contenido de una celda sea fórmula, texto, no texto, número, #N/D o un

valor lógico.

La función NOD, por su parte, devuelve #N/D.



9.20. Las funciones TIPO y TIPO.DE.ERROR

La función TIPO devuelve un 1 si una celda contiene un número, 2 si

contiene texto, 4 si contiene un valor lógico y 16 si contiene un error.

Por su parte la función TIPO.DE.ERROR devuelve un número diferente

en función del tipo de error que tenga una celda. En la siguiente figura

mostramos la operativa de ambas funciones.

10. Funciones financieras

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

financieras que EXCEL tiene predefinidas.

10.1. La función INT.EFECTIVO

Esta función permite calcular el tipo de interés efectivo una vez se le

indique el tipo de interés nominal y el número de periodos de cálculo de

intereses por año.



Como vemos, un 11,55% nominal pagadero por cuatrimestres (3 pagos

al año) se convierte en un 12,00% efectivo.

10.2. La función TASA.NOMINAL

Esta función realiza la operación contraria a la función INT.EFECTIVO;

permite calcular el tipo de interés nominal una vez se le indique el tipo

de interés efectivo y el número de periodos de cálculo de intereses por

año.

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.

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.

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.

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.

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. Esta función determina la TIR a través de un proceso

iterativo, comenzando en el 10% (salvo que se indique una estimación

inicial diferente). Dará un mensaje de error si no lo resuelve con 20

iteraciones.

10.9. Las funciones VNA.NO.PER y TIR.NO.PER

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

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



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.

10.11. La función VF.PLAN

La función VF.PLAN calcula el valor final de una cuantía que genera

intereses a lo largo de una serie de periodos a diferentes tasas de

interés sen cada periodo.

10.11. Las funciones PAGO, PAGOPRIN y PAGOINT revisitadas

El comportamiento spill over de Excel simplifica el uso de las funciones.

Usaremos la función SECUENCIA para establecer los periodos.



11. Fórmulas tipo Array y funciones de Base de Datos y Tipos

de Datos

11.1. Ejemplos de fórmulas 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:

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

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.

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.

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.



11.2. Ejemplo de fórmulas Array y su nuevo comportamiento en Excel

Las versiones más actuales de Excel han revolucionado el

comportamiento de las fórmulas matriciales. Ya no necesitan validación

especial, es decir, pueden validarse con Enter y además desbordan

hasta donde sea necesario. Observe el siguiente ejemplo. La fórmula se

introduce en D53 y desde ahí desborda a todo el rango.

O el siguiente en el que se multiplican dos rangos y se construye la

matriz de resultados introduciendo sólo la fórmula en la primera celda.

11.3. Uso de funciones BD

Las funciones BD son funciones que operan sobre una tabla a modo de

base de datos permitiendo calcular la suma, promedio, etc, de un

determinado campo y exigiendo que se cumplan determinados criterios.

En los siguientes ejemplos trabajamos sobre una tabla que hemos

denominado Ventas. Dicha tabla tiene los siguientes campos,

correspondientes a las columnas: Fac, Tipo, Provincia, Producto,

Importe y Trimestre. Mostramos, en primer lugar, el inicio y final de la

tabla.



Veamos ahora algunos ejemplos de funciones BD. Nos centraremos en

BDSUMA. En algunos casos mostramos formas alternativas de

conseguir los mismos resultados con la función SUMA combinada con

FILTRAR.

Indicamos en BDSUMA en primer lugar el nombre de la base de datos o

tabla (Ventas en nuestro caso), a continuación el campo sobre el que

queremos operar (el campo Importe, en nuestro caso, entre “”) y el

criterio. Para indicar el criterio tenemos que poner en una celda el

nombre del campo y debajo el criterio deseado. Pueden ser varios

campos con sus consiguientes criterios.

A continuación, mostramos el uso de BDSUMA cuando un criterio

puede tener varias opciones. En este caso hay que tener en cuenta que

si alguna de las opciones se deja en blanco toda la base de datos

cumplirá dicho criterio y no hará selección. Hemos dejado escrito “n” en



nuestro ejemplo en una tercera posible opción en cada criterio para

evitar esa circunstancia.

En la fila 24 mostramos diferentes opciones para referenciar la columna

en la que deseamos establecer la suma.

A continuación, mostramos el uso de las funciones BDPROMEDIO,

BDCONTAR (para campos numéricos), BDCONTARA (para todo tipo de

campos) y BDEXTRAER. Estas funciones requieren los mismos

argumentos que la función BDSUMA.

En el siguiente ejemplo mostramos la formulación necesaria para que la

búsqueda en la base de datos se haga con un criterio “Y”.



Un uso más avanzado permite establecer con una fórmula el criterio

que se desea satisfacer.

11.4. Uso de Tipo de Datos

Las últimas versiones de Excel incorporan la herramienta Tipo de Datos

(que puede operar también como una función). Los datos pueden ser

geográficos o referirse a empresas. Veamos un par de ejemplos.

En B4 escribimos ITX y le indicamos en Datos, Tipo de Datos, que se

trata de un dato empresarial. Una vez que Excel reconozca que ITX se

refiere a Inditex ya podemos extraer los diferentes parámetros. Lo

mismo con el ayuntamiento de Santiago de Compostela, en B8. En este

caso se trata de un dato geográfico. Podemos obtener los datos

indicando la celda, un punto y a continuación el dato que deseamos.

Alternativamente podemos usar la función FIELDVALUE.

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

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

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

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:

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.

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:

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:



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. Se podrían invocar los escenarios desde un botón de la

barra de acceso rápido establecido a tal efecto, lo que dinamizaría un

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

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

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.

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. Si quiere ver como va progresando la búsqueda de

la solución, en el menú opciones en la pantalla de Solver habilite la

casilla “Mostrar resultados de iteraciones”.

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.



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:



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.

Si deseamos ver cada iteración paso a paso pongamos las opciones de

cálculo en manual, indiquemos iteraciones máximas 1, y vayamos

pulsando F9 poco a poco.

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.



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.



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:

100

110

120

130

140

150

160

2007 2008 2009 2010 2011 2012 2013 2014

Euro

s

Año

Gráfico de líneas Básico

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



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:



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. También-n

puede poner una imagen en las columnas. Observe:



20

40

60

80

100

120

140

160

180

2007 2008 2009 2010 2011 2012 2013 2014

Mill

on

es

de

Eu

ros

Año

Ventas de conservas, imágenes

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.

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.

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

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.

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.

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.

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.

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.



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



100 200 50

70 120 160

0 50 100 150 200 250 300 350 400

Origen

Aplicación

Gráfico de barras

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

100

200

5070

120

160

Gráfico de anillos


100

200

50

70

120

160

0

50

100

150

200

250

300

350

400

Origen Aplicación

Gráfico de columnas

Origen 1 Origen 2 Origen 3

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



100

200

5070

120

160

Gráfico circular


-70-120-160 100 200 50

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

Gráfico de barra centrado

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



100

200

50

70

120

160

0 50 100 150 200

Origen 1

Origen 2

Origen 3

Aplicación 1

Aplicación 2

Aplicación 3

Gráfico de barras hélice



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.

0

200

400

600

800

1.000

1.200

1.400

1.600

1.800

2.000

1998 2000 2002 2004 2006 2008 2010 2012 2014 2016 2018 2020

Gráfico de dispersión con punto de corte

0

200

400

600

800

1.000

1.200

1.400

1.600

1.800

2.000

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

Gráfico de líneas con punto de corte

0

200

400

600

800

1.000

1.200

1.400

1.600

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



0

200

400

600

800

1.000

1.200

1.400

1.600

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

Gráfico de áreas con punto de corte

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.

0,0%

1,0%

2,0%

3,0%

4,0%

5,0%

6,0%

7,0%

8,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 colores según valores

Crec. Bajo Crec. Medio Crec. Alto



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.

1.600

900

0

200

400

600

800

1.000

1.200

1.400

1.600

1.800

200

0

200

1

200

2

200

3

200

4

200

5

200

6

200

7

200

8

200

9

201

0

201

1

201

2

201

3

201

4

201

5

201

6

201

7

201

8

Ingr

eso

s

Año

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

1.600

900

0

200

400

600

800

1.000

1.200

1.400

1.600

1.800

200

0

200

1

200

2

200

3

200

4

200

5

200

6

200

7

200

8

200

9

201

0

201

1

201

2

201

3

201

4

201

5

201

6

201

7

201

8

Ingr

eso

s

Año

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

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.

7,00%

6,75%

Variable A

Variable B

7%

7%

7%

7%

7%

7%

7%

7%

7%

7%

0%

2%

4%

6%

8%

10%

12%

2015 2016 2017 2018 Media Variable A Media Variable B

An

ual

Evolución anual y media de Dos Variables



12,10%

8,50% 8,50%

Variable X

Variable Y

Variable Z

0%

2%

4%

6%

8%

10%

12%

14%

0%

2%

4%

6%

8%

10%

12%

14%

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

An

ual

Evolución anual y media de Tres Variables

5,50%4,50%

6,25%

4,25%

Variable 1

Variable 2

Variable 3

Variable 4

0%

1%

2%

3%

4%

5%

6%

7%

0%

1%

2%

3%

4%

5%

6%

7%

8%

9%

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

An

ual

Evolución anual y media de Cuatro Variables

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.

11 13 14 16 17 19 20 22 23 25 26 28 29

0

20

40

60

80

100

120

0%

2%

4%

6%

8%

10%

12%

14%

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

Distribución normal

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.



80

90

80

70

67

77,4

14

13

14

15

16

14,3

Vendedor 1

Vendedor 2

Vendedor 3

Vendedor 4

Vendedor 5

Media

Unidades Precio

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.

Empresa 1

Empresa 2

Empresa 3Empresa 4Empresa 5

0%

5%

10%

15%

20%

25%

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

Ren

tab

ilid

ad

Crecimiento

Gráfico de burbujas

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

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

Gráfico de cumplimiento de objetivos

Objetivo Real



En la hoja Gráficos 10 elaboramos un 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.

10

12

10

20

15

12

14

20

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

Tarea 2

Tarea 3

Tarea 4

Tarea 5

Tarea 6

Tarea 7

Tarea 8

Gráfico de cronograma

En la hoja Gráficos 11 elaboramos un gráfico semicircular para

comparar los resultados de dos elecciones.

40

24

20

9

72016

35

26

20

11

82020

Resultado elecciones

Partido A Partido B Partido C Partido D Partido E

En la hoja Gráficos 12 incluimos un par de ejemplos de los nuevos

gráficos geográficos de Excel.



En la hoja Gráficos 13 incluimos un par de ejemplos de gráficos con

pirámides poblacionales.

10% 8% 6% 4% 2% 0% 2% 4% 6% 8% 10%

0-4

10-14

20-24

30-34

40-44

50-54

60-64

70-74

80-84

90 e máis

Pirámide de Galicia, 2011, en %

Homes Mulleres

En la hoja Gráficos 14 incluimos gráfico que compara valores reales con

los presupuestados añadiendo un icono.

24% 7%

5% 17%

9%

6%

14% 5% 28%

0

100

200

300

400

500

600

700

800

900

1.000

Dpto. 1 Dpto. 2 Dpto. 3 Dpto. 4 Dpto. 5 Dpto. 6 Dpto. 7 Dpto. 8 Dpto. 9

Real vs Presupuesto

Presupuesto Real



En la hoja Gráficos 15 incluimos un sistema que permite seleccionar el

gráfico que se muestra de una serie de ellos disponibles.

En la hoja Gráficos 16 hemos montado un par de gráficos de cascada.

Mostramos uno de ellos:

En la hoja Gráficos 17 hemos elaborado un gráfico de superficie.



En la hoja Gráficos 18 hemos elaborado un par de gráficos de barras y

columnas con un indicador móvil.

En la hoja Gráficos 19 hemos elaborado un sencillo gráfico cuya única

particularidad es que el eje X tiene la información en dos líneas, para

identificar mes y trimestre.

En la hoja Gráficos 20 mostramos gráficos con diferentes rangos

dinámicos.



En hoja Mapa 3D hemos elaborado un mapa geográfico 3D que puede

animarse en video.

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



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

También incluimos una macro para sellar una fecha y hora.

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:

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:

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

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 invertir el orden de una serie de datos

Las funciones pueden ser mucho más complejas que nuestra simple

función de impuestos. Mostramos a continuación la aplicación de



sendas funciones para invertir los datos de un rango sin ignorar e

ignorando las celdas en blanco. Mostramos como combinarla con la

función FILTRAR. Estas funciones tienen operatividad de

desbordamiento.

Mostramos el código de la función Invertir:

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

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

“Fundamentos VI 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.



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

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

En todos los casos el único argumento que necesitan estas funciones es

la celda que se desea mostrar.

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



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



El código de ambas funciones es:

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



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



19.10. 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 (TIR.NO.PER).

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



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



ú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.13. 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.14. 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

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.

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.



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



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.



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.



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:



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:



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



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.



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:



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:



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.

A veces puede interesarnos ver simultáneamente dos hojas del mismo

libro. Para ello vaya a nueva ventana en el menú vista y posteriormente

a organizar todo para decidir como disponer en pantalla las ventanas

(dos o más) abiertas.

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.

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.

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

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.

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

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

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

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.

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

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

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

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: https://exceltotal.com/atajos-de-teclado-en-excel/. 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

https://exceltotal.com/atajos-de-teclado-en-excel/



rango entre nuestra posición actual y las celdas antes

indicadas.

• Control + E (o Control + *): 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).

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

• Control + Tab nos permite cambiar entre los ficheros Excel

abiertos.

• Control + Enter nos permite mantenernos en la misma celda

al aceptar una fórmula o nos permite introducir y copiar una

fórmula en un rango si lo hemos seleccionado previamente.



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.

• 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 ventana 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 y, si estamos

en un gráfico, con un elemento del gráfico seleccionado, abre

el menú de diseño de dicho elemento.

• Control + Shift +1, establece sobre las celdas seleccionadas

un formato con identificación de millares y dos decimales.

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

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

• F6: Un Loop que activa la etiqueta de hoja, zona zoom y hoja

de nuevo

• 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

• Shift + F10. Abre el menú contextual (botón derecho del

ratón)

•

• Si queremos crear una hoja nueva Shift + F11.

• Para abrir el editor de VBA Alt + F11



26. Nos hemos dejado muchas cosas en el tintero

Este documento, que con el tiempo se ha ido haciendo más y más

extenso, en ningún caso es un repaso exhaustivo a Excel. Somos

conscientes de que algunos de los temas tratados ya no pueden recibir

el calificativo de “conocimientos básicos”. No todo el material mostrado

tiene, a nuestro modo de ver, la misma utilidad, pero eso, al fin y al

cabo, dependerá de las necesidades de cada cual. Hay muchas

cuestiones que nos hemos dejado en el tintero, y no queremos finalizar

sin mencionar algunas de ellas que, aunque en nuestro caso usamos

muy esporádicamente, pueden ser de utilidad.

• El menú Consulta, activado como complemento COM

• El menú Análisis de datos y previsión

• La información disponible en la barra de estado

• Análisis Rápido de datos y la herramienta Ideas

• Las características de vista, sin barras de fórmulas, sin cabeceras

de filas y columnas, sin etiquetas de pestañas, etc.

• La herramienta Ir a especial y buscar

• Gráficos de personas, que exigen su instalación como

complemento

• Las vinculaciones entre Excel y Word o Power Point

• Las herramientas de ordenar y filtrar avanzado

• Las herramientas de relleno automático y texto en columnas

• La posibilidad de hacer operaciones 3D (con varias hojas

simultáneamente)

• Las herramientas de formulario y rellenar

• La cámara y la ventana de inspección

• Insertar notas

• Insertar Minigráficos



• Trabajar con tablas

• Tablas dinámicas y gráficos dinámicos

• Obtener datos desde otras fuentes, especialmente desde la web.

• Y mención especial, por eso lo hemos dejado para el final, tienen

todas las herramientas que Excel ha incorporado para la

obtención y tratamiento de datos: obtener y transformar datos y

el uso del modelo de datos y Power Pivot. Nosotros no las usamos

en nuestra actividad habitual, pero son fundamentales para

cualquiera que necesite trabajar con muchos datos de forma

profesional.

Documents

Fundamentos de Excel para Finanzas