Excel 2007 Fórmulas y Funciones

Aunque Excel es la hoja de cálculo más utilizada del mundo, la mayoría de usuarios sólo trabaja con una pequeña porción del programa. Los resultados se incrementan notablemente con el uso de fórmulas y funciones. No es necesario ser un experto matemático o economista para poder utilizarlas. Con este libro aprenderá a trabajar con las fórmulas y funciones más utilizadas en Excel para aumentar su productividad a niveles sorprendentes. Está dividido en cuatro partes: dominar fórmulas y rangos en Excel, aprovechar el poder de las funciones, construir modelos empresariales y construir fórmulas financieras. Los tutoriales y ejemplos le ayudarán a adquirir un conocimiento sólido y avanzado en el uso de las fórmulas y funciones. Agradecimientos Sobre el autor Introducción Contenido Características especiales de este libro Parte I. Dominar fórmulas y rangos en Excel 1. Descubra la utilidad de los rangos Técnicas avanzadas de selección de rangos Trucos para seleccionar rangos con el ratón Trucos para seleccionar rangos con el teclado Trabajar con rangos en 3D Seleccionar un rango utilizando el comando Ir a Utilizar el cuadro de diálogo especial del comando 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 Copia de rangos avanzada Copiar atributos de celdas seleccionadas Combinar el origen y el destino aritméticamente Transponer filas y columnas Borrar un rango Aplicando el formato condicional a un rango Resaltar reglas de celdas Creación de Reglas Superiores e Inferiores Añadiendo Barras de datos Escalas de Color Conjunto de Iconos 2. Uso de nombres de rango Definir el nombre de un rango Trabajar con cuadros de nombres Uso del cuadro de diálogo Nombre Nuevo Cambiando el ámbito para definir nombres de rangos en diferentes niveles de las hojas Utilizar texto en la hoja de trabajo para definir nombres Nombrar constantes Trabajar con nombres de rangos Referencia al nombre de un rango Trabajar con el autorrelleno de nombres Navegar usando nombres de rango Pegar una lista de nombres de rangos en una hoja de trabajo Mostrar el Administrador de Nombres Filtrando Nombres Modificar las coordenadas de nombres de rangos Ajustar coordenadas de nombres de rangos automáticamente Cambiar el nombre de un rango Eliminar el nombre de un rango Usar nombres con el operador de intersección 3. Crear fórmulas básicas Entender los fundamentos de las fórmulas Límites de una fórmula en Excel 2007 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 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 Formatos de fecha y hora Eliminar formatos personalizados 4. Crear fórmulas avanzadas Trabajar con series Utilizar fórmulas de series 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 los controles de formularios Agregar un control a una hoja de trabajo Vincular un control a un valor de celda Entender los controles en 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 Solucionando errores de fórmulas con SI.ERROR() 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. Aprovechar el poder 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 Funciones de texto en Excel Trabajar con caracteres y códigos Función CARACTER() Función CODIGO() 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 hoja por ú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 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() Aplicar formato condicional a fórmulas Combinar funciones lógicas con matrices Aplicar una condición a través de 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 valor en 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 y la función ELEGIR() en una hoja de cálculo Búsqueda de valores en tablas Función BUSCARV() Función BUSCARH() Buscar una tasa de descuento de un cliente en un rango Encontrar coincidencias exactas Operaciones de búsqueda avanzada 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 fecha de nacimiento Devolver la fecha de la enésima aparición de un día laborableen 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 utilizando un 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 partes de una hora Función HORA() Función MINUTO() Función SEGUNDO() Devolver una hora X horas, minutos y segundos desde 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 Determinar si un año es bisiesto Crear sombreados en libros mayores Generar números aleatorios Función ALEATORIO() 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() Utilizar 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. Construir modelos empresariales 13. Analizar datos con tablas Convertir un rango en una lista Operaciones de tablas básicas Ordenar una tabla Ordenar una tabla por orden natural Ordenar una parte de un campo Ordenar sin artículos Filtrar datos de una tabla Utilizar Autofiltro para filtrar una tabla Utilizar criterios complejos para filtrar una tabla Introducir criterios computados Copiar datos filtrados en diferentes rangos Hacer referencia a tablas en Fórmulas Usar especificadores de tablas Ingresar fórmulas en tablas Funciones de tabla de Excel Sobre funciones de tabla Funciones que no requieren un rango de criterios Funciones de tabla que aceptan múltiples criterios Funciones de tablas que requieren un rango de criterios 14. Analizar datos con tablas dinámicas ¿Qué son las tablas dinámicas? Cómo trabajan las tablas dinámicas Algunos términos de tablas dinámicas Construyendo tablas dinámicas Construyendo una tabla dinámica desde una tabla o un rango Construyendo una tabla dinámica de una base de datos externa Trabajar con una tabla dinámica y personalizarla Trabajar con subtotales en tablas dinámicas Ocultar totales en una tabla dinámica Ocultar subtotales en una tabla dinámica Personalizar el cálculo de subtotales Cambiar el cálculo del resumen del campo de datos Usar una diferencia de cálculo de resumen Usar un cálculo de resumen de porcentaje Usar un cálculo de resumen del total corriente Usar un resumen de cálculo de Índice Crear cálculos de tablas dinámicas personalizados Crear un campo calculado Crear un elemento calculado Usar los resultados de la tabla dinámica en una fórmula en la hoja de cálculo 15. Utilizar 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 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 16. Utilizar regresión para hacer seguimientos de tendencias y previsiones Elegir un método de regresión Utilizar regresión simple en datos lineales Analizar tendencias utilizando líneas optimizadas Hacer predicciones Utilizar regresión simple en datos no lineales Trabajar con una tendencia exponencial Trabajar con una tendencia logarítmica Trabajar con una tendencia de potencia Utilizar análisis de regresión polinomial Utilizar análisis de regresión múltiple 17. Solucionar problemas complejos con el Solver Una introducción al Solver Las ventajas del 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. Construir fórmulas financieras 18. 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 Parte I 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 19. 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 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 liquidez no periódico Calcular tasas internas de devolución múltiples Índice alfabético