EXCEL FORMULAS Y FUNCIONES

Agradecimientos

Sobre el autor



Introducción

Contenido

Características especiales del libro





Parte I. Domine fórmulas y rangos de Excel



1. Descubra la utilidad de los rangos

Revisión de las Técnicas de Selección de rango de Excel

Seleccionar un rango con el ratón

Seleccionar un rango continuo con el ratón

Seleccionar una fila o columna con el ratón

Seleccionar un rango en modo extendido con el ratón

Seleccionar un rango discontinuo con el ratón

Trucos para seleccionar rangos con el ratón

Seleccionar rangos de celdas con el teclado

Seleccionar un rango continuo con el teclado

Seleccionar una fila o columna con el teclado

Seleccionar un rango discontinuo con el teclado

Trucos para seleccionar rangos con el teclado

Trabajar con rangos en 3D

Técnicas avanzadas de selección de rangos

Seleccionar un rango utilizando el comando Ir a

Utilizar el cuadro de diálogo especial del comando Ir a

Selección de celdas por tipo

Seleccionar celdas contiguas

Selección de celdas por diferencias

Selección de celdas por referencia

Otras opciones de Ir a Especial

Teclas de método abreviado para seleccionar por mediode Ir a

Entrada de datos en un rango

Rellenar rangos

Uso del controlador de relleno

Usar autorrelleno para crear series numéricas y de texto

Creación de listas de autorrelleno personalizadas

Rellenar un rango

Creación de series

Copiar un rango

Usar arrastrar y soltar para copiar un rango

Copiar un rango con el comando copiar

Hacer varias copias de un rango

Insertar una copia de un rango

Copia de rangos avanzada

Mover un rango

Usar arrastrar y soltar para mover un rango

Uso de los comandos del menú para mover un rango

Insertar y eliminar un rango

Insertar una fila o columna entera

Insertar una fila o columna con el controlador de relleno

Insertar una celda o un rango

Insertar un rango con el controlador de relleno

Eliminar una fila o columna entera

Eliminar una celda o rango

Borrar un rango

Borrar un rango con el controlador de relleno

Usar el operador de referencia de Excel

Uso del operador de rango

Uso del operador de intersección

Uso del operador de unión

2. Uso de nombres de rango

Definir el nombre de un rango

Trabajar con cuadros de nombres

Uso del cuadro de diálogo para definir nombres

Definir nombres de rango en diferentes niveles en las hojas

Asignar un nombre a un rango en 3D

Introducir un nombre de rango en 3D manualmente

Introducir un nombre de rango en 3D seleccionandoel rango

Utilizar texto en la hoja de trabajo para definir nombres

Nombrar constantes

Trabajar con nombres de rangos

Referencia al nombre de un rango

Navegar usando nombres de rango

Pegar una lista de nombres de rangos en una hoja de trabajo

Modificar las coordenadas de nombres de rangos

Ajustar coordenadas de nombre de rangos automáticamente

Primera solución: insertar una celda en blanco al finaldel rango

Segunda solución: nombrar la fila o columna entera

Cambiar el nombre de un rango

Eliminar el nombre de un rango

Nombres de rangos y operadores de referencia

Usar nombres con el operador de rango

Usar nombres con el operador de intersección



3. Crear fórmulas básicas

Entender las bases de las fórmulas

Introducir y modificar fórmulas

Uso de fórmulas aritméticas

Uso de fórmulas comparativas

Uso de fórmulas de texto

Uso de fórmulas de referencia

Entender el operador de precedencia

El orden de precedencia

Control de la orden de precedencia

Control de cálculo en una hoja de trabajo

Copiar y mover fórmulas

Entender el formato de referencia relativa

Entender el formato de referencia absoluto

Copiar una fórmula sin ajustar las referencias relativas

Ver las fórmulas de la hoja de trabajo

Convertir una fórmula en un valor

Trabajar con nombres de rangos en fórmulas

Pegar un nombre en una fórmula

Aplicar nombres a fórmulas

Ignorar referencias relativas y absolutas al aplicarnombres

Utilizar nombres de filas o columnas al aplicar nombres

Nombrar fórmulas

Trabajar con vínculos en fórmulas

Entender referencias externas

Actualización de vínculos

Modificar vínculos

Formatear números, fechas y horas

Formatos numéricos

Cambiar formatos numéricos

Personalizar formatos numéricos

Ocultar ceros

Uso de valores condicionales

Formatos de fecha y hora

Personalizar formatos de fechas y horas

Eliminar formatos personalizados



4. Crear fórmulas avanzadas

Trabajar con series

Utilizar fórmulas de series

Entender fórmulas de series

Fórmulas de series que operan varios rangos

Uso de constantes de series

Funciones que utilizan o dan resultados de series

Uso de referencias circulares y de iteración

Consolidar datos en varias hojas

Consolidación por posición

Consolidación por categoría

Aplicar reglas de datos de validación a celdas

Uso de controles de cuadros de diálogo en una hoja de trabajo

Uso de la barra de herramientas Formularios

Agregar un control a una hoja de trabajo

Vincular un control a un valor de celda

Entender el control de una hoja de trabajo



5. Solucionar problemas con fórmulas

Entender valores erróneos de Excel

#DIV/0!

#N/A

#NOMBRE?

#NULO!

#NUM!

#REF!

#VALOR

Solucionar errores de fórmulas

Ausencia de paréntesis

Resultados de fórmula erróneos

Arreglar referencias circulares

Uso del revisor de error de fórmula

Elegir una acción de error

Establecer opciones de Comprobación de errores

Auditoría de una hoja de trabajo

Entender las Auditorías

Rastrear celdas precedentes

Rastrear celdas dependientes

Rastrear errores

Quitar flechas rastreadoras

Evaluar fórmulas

Inspeccionar valores de celda





Parte II. Domine las utilidades de las funciones



6. Uso de funciones

Sobre las funciones de Excel

La estructura de una función

Escribir una función en una fórmula

Uso de la característica Insertar función

Cargar las funciones de herramientas para análisis



7. Trabajar con funciones de texto

Trabajar con caracteres y códigos

Función CARACTER()

Generar el grupo de caracteres ANSI

Generar una serie de letras

Función CODIGO()

Generar una serie de letras empezando por cualquier letra

Convertir texto

Función MINUSC()

Función MAYUSC()

Función NOMPROPIO()

Formatear un texto

Función MONEDA()

Función DECIMAL()

Función TEXTO()

Visualizar cuándo se ha actualizado una hojapor última vez

Manipular texto

Eliminar caracteres innecesarios de una cadena

Función ESPACIOS()

Función LIMPIAR()

Función REPETIR(): repetir un carácter

Rellenar una celda

Crear gráficos de texto

Extraer una subcadena

Función IZQUIERDA()

Función DERECHA()

Función EXTRAE()

Convertir texto a mayúsculas o minúsculas tipo oración

Una fórmula de conversión de fecha

Buscar subcadenas

Funciones ENCONTRAR() y HALLAR()

Extraer un nombre o un apellido

Extraer el nombre, el apellido y la inicial del medio

Determinar la letra de la columna

Sustituir una subcadena por otra

Función REEMPLAZAR()

Función SUSTITUIR()

Quitar un carácter de una cadena

Quitar dos caracteres diferentes de una cadena

Quitar avances de línea



8. Trabajar con funciones lógicas y de información

Sumar inteligencia a funciones lógicas

Uso de la función SI()

SI(): el caso más sencillo

Manejar un resultado FALSO

Evitar dividir entre cero

Realizar diversas pruebas lógicas

Anidar funciones SI()

Calcular primas apiladas

Función Y()

Introducir valores en categorías

Función O()

Combinar funciones lógicas con matrices

Aplicar una condición en un rango

Operar sólo con celdas con una condición en común

Determinar si un valor aparece en una lista

Contar el número de veces que aparece un valoren un rango

Determinar dónde aparece un valor en una lista

Obtener información mediante funciones de información

Función CELDA()

Función TIPO.DE.ERROR()

Función INFO()

Funciones ES

Contar el número de celdas vacías en un rango

Examinar un rango para valores no numéricos

Contar el número de errores en un rango

Ignorar errores cuando trabaja con un rango

Evitar errores de fórmulas de texto



9. Trabajar con funciones de búsqueda y referencia

Entender tablas de búsqueda y referencia

Función ELEGIR()

Determinar el nombre del día de la semana

Determinar el mes del año fiscal

Calcular resultados de cuestionarios ponderados

Integrar botones de opción de hoja y la función ELEGIR()

Búsqueda de valores en tablas

Función BUSCARV()

Función BUSCARH()

Búsqueda de rangos

Buscar una tasa de descuento de un cliente

Buscar una tasa impositiva

Encontrar coincidencias exactas

Buscar el número de cuenta de un cliente

Combinar búsquedas de coincidencias exactas con listas desplegables dentro de las celdas

Operaciones de búsqueda avanzada

Funciones COINCIDIR() e INDICE()

Buscar un valor mediante cuadros de listas de hojas

Utilizar cualquier columna como columna de búsqueda

Crear búsquedas de filas y columnas

Crear búsquedas de varias columnas



10. Trabajar con funciones de fecha y hora

Manejo de fechas y datos con Excel

Introducir fechas y horas

Excel y los años de dos dígitos

Uso de las funciones de fecha de Excel

Devolver una fecha

HOY(): Devolver la fecha actual

FECHA(): Devuelve cualquier fecha

FECHANUMERO(): Convertir una cadena en fecha

Devolver partes de una fecha

Función AÑO()

Función MES()

Función DIA()

Función DIASEM()

Función NUM.DE.SEMANA()

Devolver una fecha a X años, meses o días de ahora

Una alternativa a los días laborables: función DIA.LAB()

Sumar X meses: un problema

Función FECHA.MES()

Función FIN.MES()

Devolver el último día de cualquier mes

Determinar el cumpleaños de una persona según su fechade nacimiento

Devolver la fecha de la enésima aparición de un díalaborable en un mes

Calcular el día Juliano

Calcular la diferencia entre dos fechas

Calcular la edad de una persona

Función SIFECHA()

Calcular la edad de una persona (2ª parte)

Calcular el número de días laborables entre dos fechas: DIA.LAB.NETO()

DIAS360(): Calcular la diferencia entre fechas utilizandoun año de 360 días

Devolver la fracción de un año entre dos fechas:FRAC.AÑO()

Uso de las funciones de hora de Excel

Devolver una hora

Devolver la hora actual: AHORA()

Devolver cualquier hora: NSHORA()

Convertir una cadena en una hora: HORANUMERO()

Devolver partes de una hora

Función HORA()

Función MINUTO()

Función SEGUNDO()

Devolver una hora X horas, minutos y segundosdesde ahora

Sumar valores de hora

Calcular la diferencia entre dos horas



11. Trabajar con funciones matemáticas

Entender funciones de redondeo de Excel

Función REDONDEAR()

Función REDOND.MULT()

Funciones REDONDEAR.MAS() y REDONDEAR.MENOS()

Funciones MULTIPLO.INFERIOR()y MULTIPLO.SUPERIOR()

Determinar en qué fecha cae el trimestre fiscal

Calcular fechas de Semana Santa

Funciones REDONDEA.PAR() y REDONDEA.IMPAR()

Funciones ENTERO() y TRUNCAR()

Utilizar redondeo para prevenir errores de cálculo

Establecer los decimales en precios

Sumar valores

Función SUMAR()

Calcular totales acumulados

Sumar sólo los valores negativos o positivos en un rango

Función RESIDUO()

Una fórmula mejor para las diferencias entre horas

Sumar cada enésima fila

Primer caso especial: Sumar sólo filas impares

Segundo caso especial: Sumar sólo filas pares

Determinar si un año es bisiesto

Crear sombreados en libros mayores

Generar números aleatorios

Función ALEATORIO()

Generar números de n dígitos aleatorios

Generar una letra aleatoria

Ordenar valores aleatoriamente

Función ALEATORIO.ENTRE()



12. Trabajar con funciones estadísticas

Entender estadísticas descriptivas

Contar productos con la función CONTAR()

Calcular promedios

Función PROMEDIO()

Función MEDIANA()

Función MODA()

Calcular la media ponderada

Calcular valores extremos

Funciones MAX() y MIN()

Funciones K.ESIMO.MAYOR() y K.ESIMO.MENOR()

Realizar cálculos con los primeros valores k

Realizar cálculos con los últimos valores k

Calcular medidas de variación

Calcular el rango

Calcular la variación mediante la función VAR()

Calcular la desviación estándar con las funciones DESVESTP()y DESVEST()

Trabajar con distribuciones de frecuencia

Función FRECUENCIA()

Entender la distribución normal y la función DISTR.NORM()

Forma de la curva I: función COEFICIENTE.ASIMETRIA()

Forma de la curva II: función CURTOSIS()

Usar herramientas estadísticas de Herramientas para análisis

Utilizar la herramienta Estadística descriptiva

Determinar el coeficiente de correlación entre datos

Trabajar con histogramas

Utilizar la herramienta Generación de números aleatorios

Trabajar con jerarquía y percentil





Parte III. Cree modelos empresariales



13. Analizar datos con listas

Convertir un rango en una lista

Operaciones de lista básicas

Ordenar una lista

Ordenar por más de tres claves

Ordenar una lista por orden natural

Ordenar una parte de un campo

Ordenar sin artículos

Filtrar datos de lista

Utilizar Autofiltro para filtrar una lista

Opciones del criterio Autofiltro

Establecer criterios de Autofiltro personalizados

Mostrar registros filtrados

Utilizar criterios complejos para filtrar una lista

Crear un rango de criterios

Filtrar una lista con un rango de criterios

Introducir criterios compuestos

Introducir criterios computados

Copiar datos filtrados en diferentes rangos

Resumiendo datos de lista

Crear subtotales automáticos

Crear una lista para subtotales automáticos

Mostrar subtotales

Agregar más subtotales

Anidar subtotales

Trabajar con símbolos de esquema de subtotales

Quitar subtotales

Funciones de lista de Excel

Sobre funciones de lista

Funciones que no requieren un rango de criterios

Utilizar CONTAR.SI()

Utilizar SUMAR.SI()

Funciones de lista que requieren un rango de criterios

Utilizar BDPROMEDIO()

Utilizar BDEXTRAER()



14. Usar herramientas de modelos empresariales de Excel

Utilizar análisis qué pasa si

Crear una tabla de datos de una entrada

Añadir más fórmulas a la tabla de entrada

Crear una tabla de dos entradas

Modificar tablas de datos

Trabajar con Buscar objetivo

¿Cómo funciona Buscar objetivo?

Funcionamiento de Buscar objetivo

Optimizar el margen del producto

Nota en aproximaciones sobre Buscar objetivo

Realizar un análisis de operación nula

Resolver ecuaciones algebraicas

Buscar objetivo con gráficas

Trabajar con escenarios

Entender escenarios

Establecer su hoja para escenarios

Agregar un escenario

Visualizar un escenario

Modificar un escenario

Combinar escenarios

Generar un informe de resumen

Eliminar un escenario



15. Utilizar regresión para hacer seguimientos de tendenciasy previsiones

Elegir un método de regresión

Utilizar regresión simple en datos lineales

Analizar tendencias utilizando líneas optimizadas

Trazar una línea de tendencia optimizada

Entender la ecuación de regresión

Entender R2

Calcular valores optimizados utilizando TENDENCIA()

Calcular valores optimizados utilizandoESTIMACION.LINEAL()

Analizar las ventas frente a la tendencia de publicidad

Hacer predicciones

Trazar valores pronosticados

Ampliar una tendencia lineal con el controlador de relleno

Ampliar una tendencia lineal mediante el comando Series

Predecir con la ecuación de regresión

Predecir con TENDENCIA()

Predecir con ESTIMACION.LINEAL()

Utilizar regresión simple en datos no lineales

Trabajar con una tendencia exponencial

Trazar una línea de tendencia exponencial

Calcular valores de línea de tendencia y de predicciones exponenciales

Tendencia y predicción exponencial utilizando la función CRECIMIENTO()

Trabajar con una tendencia logarítmica

Trazar una línea de tendencia logarítmica

Calcular valores de predicción y de tendencia logarítmicos

Trabajar con una tendencia de potencia

Trazar una línea de tendencia de potencia

Calcular valores de tendencia y predicción de potencia

Utilizar análisis de regresión polinomial

Trazar una línea de tendencia polinómica

Calcular valores de predicción y tendencia polinómicos

Utilizar análisis de regresión múltiple



16. Solucionar problemas complejos con Solver

Una introducción a Solver

Las ventajas de Solver

¿Cuándo tenemos que utilizar Solver?

Cargar Solver

Utilizar Solver

Agregar restricciones

Guardar una solución como un escenario

Establecer otras opciones de Solver

Controlar Solver

Seleccionar el método que utiliza Solver

Trabajar con modelos de Solver

Dar sentido a los mensajes de Solver

Mostrar los informes de Solver

El informe Respuesta

El informe Sensibilidad

El informe Límites





Parte IV. Cree fórmulas financieras



17. Construir fórmulas de préstamos

Entender el valor temporal del dinero

Calcular el pago del préstamo

Análisis de pagos de préstamo

Trabajar con un préstamo balón

Calcular costes de interés

Calcular el capital y el interés

Calcular costes de interés. Parte 2

Calcular capital e interés acumulativo

Crear un plan de autorización de préstamo

Crear un plan de amortización de tipo fijo

Crear un plan de amortización dinámico

Calcular el plazo de un préstamo

Calcular el tipo de interés para un préstamo

Calcular cuánto puede pedir prestado



18. Crear fórmulas de inversión

Trabajar con tasas de interés

Entender el interés compuesto

Interés nominal frente a efectivo

Conversión entre tasa nominal y efectiva

Calcular el valor futuro

La suma total de un valor futuro

El valor futuro de una serie de depósitos

El valor futuro de una suma total más depósitos

Trabajando hacia un objetivo de inversión

Calcular la tasa de interés requerida

Calcular el número de periodos requerido

Calcular el depósito regular necesario

Calcular el depósito inicial necesario

Calcular el valor futuro con tasas de interés variables



19. Trabajar con bonos

Diccionario de términos de bonos

Calcular rendimiento de bonos

Función RENDTO()

Las funciones RENDTO.PER.IRREGULAR.1()y RENDTO.PER.IRREGULAR.2()

Calcular precios de bonos

Función PRECIO()

Funciones PRECIO.PER.IRREGULAR.1() y PRECIO.PER.IRREGULAR.2()

Calcular la duración de un bono

Calcular el vencimiento del capital de un bono

Trabajar con cupones

Calcular el pago del cupón

Trabajar con fechas de cupones

Calcular los intereses devengados del bono

Calcular el interés del bono restante

Trabajar con bonos de cupón cero

Trabajar con letras del tesoro

Calcular el rendimiento de una letra del tesoro

Calcular el precio de una letra del tesoro



20. Crear fórmulas de descuento

Calcular el valor actual

Tomar en cuenta la inflación

Calcular el valor presente mediante VA()

Inversión de ingresos frente a comprar una propiedad en renta

Comprar frente a un leasing

Descontar flujo de liquidez

Calcular el valor actual neto

Calcular el valor actual neto mediante VNA()

Valor actual neto con flujo de liquidez cambiante

Valor actual neto con flujo de liquidez no periódico

Calcular el periodo de devolución

Un periodo de devolución simple sin descontar

Calcular la tasa interna de devolución

Punto de devolución exacto sin descuento

Periodo de devolución descontado

Calcular la tasa interna de devolución

Utilizar la función TIR()

Calcular la tasa interna de devolución para un flujo de liquidezno periódico

Calcular tasas internas de devolución múltiples



Índice alfabético

La mayor parte de los usuarios de Excel sólo utilizan una pequeña parte de todo el potencial del programa. Si controlaran mejor la creación de fórmulas y el uso de funciones, ahorrarían mucho tiempo y obtendrían mejores resultados en su trabajo. Desafortunadamente, esta es la parte más compleja y desalentadora para los que se inician en Excel, envuelta en los misterios de las matemáticas, las finanzas y el complicado lenguaje de las hojas de cálculo.



Este libro desmitifica las fórmulas de las hojas de cálculo y presenta las funciones más útiles de forma clara y sencilla. No sólo abarca funciones para la construcción de fórmulas de nivel avanzado e intermedio sino que también enseña por qué estas funciones son útiles y muestra cómo aplicarlas en situaciones cotidianas.



Alcanzará gran precisión en los cuatro pilares principales que debe dominar en Excel: rangos, fórmulas, funciones y herramientas de análisis de datos, evitando complicadas teorías y encontrando soluciones adecuadas e inmediatas.