Fundamentos de Excel para Finanzas
(Adaptado Excel 2016 y Office 365) Actualizado 3 de octubre de 2022
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.
TABLA DE CONTENIDOS
FUNDAMENTOS DE EXCEL PARA FINANZAS ... 1
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 ... 15
1.4. Validación ... 17
1.5. Formato condicional ... 23
2.COPIADO Y PEGADO DE FÓRMULAS ... 28
2.1. Copiado/pegado libre, sin bloqueos ... 29
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 ... 32
3.NOMBRES ... 33
3.1. Nombre de celda ... 33
3.2. Nombre de rango ... 35
3.3. Crear nombres desde la selección ... 36
3.4. Nombres con referencia relativa ... 36
3.5. Otros usos de la técnica de asignación de Nombres ... 37
3.6. Nombres en activo ... 38
4.FUNCIONES ARITMÉTICAS ... 40
4.1. SUMA ... 40
4.2. SUMA con rango semicerrado ... 41
4.8. EXP, POTENCIA y PI ... 46
4.9. PRODUCTO y SUMA.CUADRADOS ... 47
4.10. AGREGAR Y SUBTOTALES ... 47
5.FUNCIONES ESTADÍSTICAS BÁSICAS... 49
5.1. MAX, MIN y PROMEDIO ... 49
5.2. Funciones de desviación típica y varianza ... 49
5.3. Funciones de covarianza y correlación ... 50
5.4. JERARQUIA ... 50
5.5. K.ESIMO.MAYOR ... 51
5.6. K.ESIMO.MENOR ... 52
5.7. PENDIENTE ... 52
5.8. DISTR.NORM.N ... 53
5.9. INV.NORM ... 53
5.10. MEDIANA, MODA y MODA.VARIOS ... 54
5.11. PERCENTIL Y RANGO.PERCENTIL ... 55
5.12. FRECUENCIA ... 55
6.MISCELÁNEA ... 56
6.1. TEXTO ... 56
6.2. RAIZ ... 56
6.3. ABS, ENTERO, REDONDEAR ... 57
6.4. Funciones de números aleatorios ... 58
6.5. TRANSPONER ... 59
6.6. La función SECUENCIA ... 59
6.7. Las funciones MULTIPLO.SUPERIOR.MAT y MULTIPLO.INFERIOR.MAT ... 60
6.8. Otras funciones de REDONDEAR, PAR, IMPAR, MAS, MENOS, MULT ... 60
6.9. TRUNCAR vs ENTERO ... 61
6.10. Operaciones con fechas, DIAS ... 61
6.11. HOY, AHORA y FIN.MES... 62
6.12. AÑO, MES, DIA, DIASEM ... 63
6.13. FECHA.MES, FECHA Y FECHANUMERO... 63
7.FUNCIONES LÓGICAS ... 65
7.1. La función SI para una condición simple ... 65
7.2. La función MAX como alternativa ... 66
7.3. Uso de la función SI anidada ... 67
7.4. Anidamiento múltiple de la función SI ... 68
7.5. Función Y ... 69
7.6. Función O ... 69
7.7. Función NO ... 70
7.8. Función SI.ERROR y SI.ND ... 70
7.9. Función SI.CONJUNTO ... 71
8.FUNCIONES DE BÚSQUEDA Y REFERENCIA ... 72
8.1. ELEGIR ... 72
8.2. La función DESREF ... 73
8.3. Función DESREF al detalle ... 74
8.4. La función COINCIDIR y COINCIDIRX ... 76
8.5. La función BUSCARH, BUSCARV y BUSCARX ... 77
8.6. La función INDICE ... 81
8.7. La función CAMBIAR... 83
8.7. Las funciones FILA y COLUMNA, FILAS y COLUMNAS ... 84
8.7. La función INIDRECTO ... 84
8.8. La función ORDENAR y ORDENARPOR ... 85
8.9. La función FILTRAR ... 88
8.10. La función UNICOS ... 91
9.2. La función DERECHA ... 97
9.3. Las funciones ENCONTRAR y HALLAR... 98
9.4. La función EXTRAE ... 98
9.5. La función CONCAT ... 99
9.6. La función UNIRCADENAS ... 99
9.7. La función ESPACIOS ... 100
9.8. La función IGUAL ... 100
9.9. La función LARGO ... 100
9.10. La función MAYUSC ... 100
9.11. La función MINUSC... 101
9.12. La función NOMPROPIO ... 101
9.13. Funciones CARACTER y CODIGO ... 101
9.14. Funciones REEMPLAZAR, SUSTITUIR, REPETIR y T... 102
9.15. Combinación de funciones TEXTO ... 103
9.16. Las funciones INFO, HOJA, HOJAS y CELDA ... 105
9.17. Las funciones ES.PAR, ES.IMPAR, Y ESBLANCO ... 106
9.18. Las funciones ESERROR y ESERR ... 106
9.19. Las funciones ESFORMULA, ESTEXTO, ESNOTEXTO, ESNUMERO, ESNOD, ESLOGICO y NOD 106 9.20. Las funciones TIPO y TIPO.DE.ERROR ... 107
10.FUNCIONES FINANCIERAS ... 107
10.1. La función INT.EFECTIVO ... 107
10.2. La función TASA.NOMINAL ... 108
10.3. Funciones con anualidades: VA, PAGO, TASA, NPER ... 108
10.4. Función VA añadiendo un pago final ... 109
10.5. Función VF añadiendo un pago inicial ... 110
10.6. Las funciones PAGO, PAGOPRIN y PAGOINT ... 110
10.7. VNA ... 111
10.8. TIR ... 112
10.9. Las funciones VNA.NO.PER y TIR.NO.PER... 112
10.10. La función TIRM ... 113
10.11. La función VF.PLAN ... 113
10.11. Las funciones PAGO, PAGOPRIN y PAGOINT revisitadas... 113
11.FÓRMULAS TIPO ARRAY Y FUNCIONES DE BASE DE DATOS Y TIPOS DE DATOS ... 114
11.1. Ejemplos de fórmulas Array ... 114
11.2. Ejemplo de fórmulas Array y su nuevo comportamiento en Excel ... 117
11.3. Uso de funciones BD ... 117
11.4. Uso de Tipo de Datos ... 120
12.USO DE LA HERRAMIENTA TABLA ... 120
12.1. Tablas de un solo input, columna ... 121
12.2. Tablas de un solo input, fila ... 122
12.3. Tablas de dos inputs, fila y columna ... 123
13.HERRAMIENTA BUSCAR OBJETIVO ... 124
14.ESCENARIOS ... 127
14.1. Uso del Administrador de Escenarios de Excel ... 127
14.2. Elaboración de Escenarios. Un método alternativo ... 130
15.SOLVER ... 131
16.USO DE REFERENCIAS CIRCULARES ... 134
17.ELABORACIÓN DE GRÁFICOS ... 136
17.1. Gráfico de líneas básico ... 136
17.2. Gráfico de columnas básico ... 139
17.3. Gráfico de columnas y líneas... 141
17.4. Gráfico de columnas y líneas de doble eje vertical ... 141
17.5. Gráfico de áreas ... 142
17.11. Gráficos más elaborados ... 149
18.UNA MACRO SENCILLA ... 166
19.FUNCIONES PERSONALIZADAS ... 168
19.1. Una función para calcular los impuestos ... 168
19.2. Función para invertir el orden de una serie de datos ... 171
19.3. Función para indicar el nombre de la hoja ... 172
19.4. Funciones para mostrar la fórmula de una celda ... 173
19.5. Función para calcular el VAN ... 174
19.6. Función para calcular el VAN con ajuste de medio año ... 174
19.7. Función para calcular el IRB e IRN ... 175
19.8. Funciones para calcular el VAN con tasa de descuento variable ... 176
19.9. Función para calcular el VAN Modificado ... 177
19.10. Función para calcular la TIR No Periódica Modificada. ... 178
19.11. Función para calcular el valor de un bono con una tasa de interés para la valoración variable ... 178
19.12. Función para calcular el valor de una renta con crecimiento constante durante un periodo finito ... 179
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 ... 180
19.14. Función para calcular el plazo de recuperación ... 180
20.USO DE MACROS CON BUSCAR OBJETIVO Y SOLVER ... 181
20.1. Una macro sencilla para usar Buscar Objetivo ... 182
20.2. Una macro para usar Buscar Objetivo con una tabla en columna ... 182
20.3. Una macro para usar Buscar Objetivo con una tabla en fila ... 184
20.4. Una macro para usar Buscar Objetivo con una tabla de dos ejes ... 184
20.5. Una macro para usar Solver ... 185
21.USO DE LA AUDITORIA DE FÓRMULAS ... 187
22.AGRUPAR, DIVIDIR, INMOVILIZAR PANELES, HIPERVÍNCULOS ... 189
22.1. Agrupar ... 189
22.2. Dividir ... 191
22.3. Inmovilizar paneles ... 192
22.4. Hipervínculos ... 193
23.IMPRIMIR ... 193
23.1 Selección de datos a imprimir ... 193
23.2 Encabezados y pie ... 194
23.3 Salto de página ... 195
24.RECOMENDACIONES BÁSICAS SOBRE LA ELABORACIÓN DE MODELOS FINANCIEROS EN EXCEL ... 195
24.1. Número de hojas de un modelo ... 196
24.2. Alcance del modelo ... 196
24.3. Orientación vertical u horizontal de un modelo ... 197
24.4. Orientación de izquierda a derecha y de arriba a abajo ... 197
24.5. Acerca de inputs y outputs ... 197
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... 202
24.7. Introduzca los datos una sola vez ... 203
24.8. Introduzca niveles en “segunda línea” ... 203
24.9. Creación de fórmulas y chequeo ... 203
24.10. Optimice el tiempo y aproveche su trabajo ... 205
25.ALGUNOS ATAJOS O TRUCOS BÁSICOS DE TECLADO EN EXCEL ... 206
26.NOS HEMOS DEJADO MUCHAS COSAS EN EL TINTERO ... 211
El texto que desarrollamos a continuación no pretende ser una lección exhaustiva de Excel, ni mucho menos. Sólo nos hemos tratado de 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 2022, 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 muchos casos se han incorporado algunas cuestiones que podríamos catalogar en “excel avanzado” y que aún estando presentes en los ficheros de excel no se explican en este texto, para evitar que este texto creciese exponencialmente. No obstante, aquella persona con inquietud puede revisar esos ejemplos y
tratar de “descifrarlos”. Ése es, sin duda, un muy buen método de aprendiuzaje. 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.
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).
Cuando se hacen cálculos numéricos de forma conjunta en una misma celda es importante entender el orden de prioridad de los operadores (+, -, *, etc). La siguiente figura resume esta cuestión.
Orden de prioridad de los operadores Ejemplos de prioridad de los operadores básicos
() Forzar prioridad 14 =2+3*4
1 : Operador de referencia 20 =(2+3)*4
1 Operador de intersección 10 =-2+3*4
2 - Negación -14 =-(2+3*4)
3 % Porcentaje 6 =10*3/5
4 ^ Exponente 6 =10*(3/5)
5 * Multiplicación 6 =(10*3)/5
5 / División 4 =-2^2
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
Texto vari
a
doEn 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.
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
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.
Listas de validación combinadas
Galicia Euskadi Cataluña C.ValencianaAndalucia Extremadura
Coruña Alava Barcelona Alicante Sevilla Cáceres
Lugo Guipuzcoa Gerona Castellon Cordoba Badajoz
Orense Vizcaya Lerida Valencia Granada
Pontevedra Tarragona Almeria
Jaén Malaga Huelva Cádiz
4 3 4 3 8 2 --> $H$42--> =CONTARA(H34:H41)
Escoja autonomia Euskadi
Técnica usada
Escoja provincia metodo 1 Alava SI(D44=C33;C34:C37;SI(D44=D33;D34:D36;SI(D44=E33;E34:E37;SI(D44=F33;F34:F36;SI(D44=G33;G34:G41;H34:H35))))) Chequeo OK =SI.ERROR(COINCIDIRX(D46;DESREF(B33;0;COINCIDIRX(D44;C33:H33);8;1);0);"Corregir selección")
Escoja provincia metodo 2 Alava SI.CONJUNTO(D44=C33;C34:C37;D44=D33;D34:D36;D44=E33;E34:E37;D44=F33;F34:F36;D44=G33;G34:G41;D44=H33;H34:H35) Escoja provincia metodo 3 Alava INDIRECTO($D$44)
Escoja provincia metodo 4 Alava DESREF(B33;1;COINCIDIRX(D44;$C$33:$H$33);8;1)
Escoja provincia metodo 5 Alava DESREF(B33;1;COINCIDIRX(D44;$C$33:$H$33);BUSCARX(D44;C33:H33;C42:H42);1)
Escoja provincia metodo 6 Alava DESREF(B33;1;COINCIDIRX(D44;$C$33:$H$33);8-CONTAR.BLANCO(DESREF(B33;1;COINCIDIRX(D44;C33:H33);8;1));1) Escoja provincia metodo 7 Alava DESREF(B33;1;COINCIDIRX(D44;$C$33:$H$33);CONTARA(DESREF(B33;1;COINCIDIRX(D44;C33:H33);8;1));1) Escoja provincia metodo 8 Alava BUSCARX(D44;C33:H33;C34:H41;"N.D")
Escoja provincia metodo 9 Guipuzcoa INDICE(C34:H41;;COINCIDIRX(D44;C33:H33)) Escoja provincia metodo 10 Guipuzcoa Con VBA avisa de que hay que cambiar
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.
Cambiar formato a los que cumplan una formula
Mínimo de cada fila Mínimo de cada columna
1 7 3 4 1 7 3 4
8 3 5 7 8 3 5 7
9 5 4 6 9 5 4 6
7 8 4 2 7 8 4 2
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, tal como se muestra en la imagen anterior.
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
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
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
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 con la introducción por parte de Excel de la técnica de Spilling (presente en las versiones actuales del programa), se 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.
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
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
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
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.
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.
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
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.
