Introducción
Contenido
Características especiales de este libro
Parte I. Rangos y fórmulas de Excel
1. 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
Rangos en 3D
Seleccionar un rango utilizando el comando Ir a
Cuadro de diálogo Ir a Especial
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
Métodos abreviados de teclado para seleccionar por medio de Ir a
Entrada de datos en un rango
Rellenar rangos
Controlador de relleno
Opción Autorrelleno para crear series numéricas y de texto
Creación de listas de autorrelleno personalizadas
Rellenar un rango
Creación de series
Copia avanzada de rangos
Copiar atributos seleccionados de celdas
Combinar el origen y el destino aritméticamente
Transponer filas y columnas
Borrar un rango
Aplicar formato condicional a un rango
Reglas para resaltar celdas
Reglas Superiores e Inferiores
Añadir Barras de datos
Escalas de Color
Conjuntos de Iconos
2. Nombres de rango
Definir un nombre de rango
Cuadro de nombres
Cuadro de diálogo Nombre Nuevo
Definir el ámbito de los nombres de rango
Definir nombres con texto de la hoja
Nombrar constantes
Trabajar con nombres de rangos
Referencia a un nombre de rango
Autorrelleno de nombres
Navegar utilizando nombres de rango
Pegar una lista de nombres de rango en una hoja
Administrador de nombres
Filtrar nombres
Editar las coordenadas de los nombres de rango
Ajustar las coordenadas de forma automática
Primera solución: incluir una celda en blanco al final del rango
Segunda solución: nombrar la fila o la columna entera
Cambiar un nombre de rango
Eliminar un nombre de rango
Usar nombres con el operador de intersección
3. Crear fórmulas básicas
Fundamentos de las fórmulas
Límites de las fórmulas en Excel 2007 y 2010
Introducir y modificar fórmulas
Fórmulas aritméticas
Fórmulas comparativas
Fórmulas de texto
Fórmulas de referencia
Precedencia de los operadores
El orden de precedencia
Control del orden de precedencia
Control de los cálculos
Copiar y mover fórmulas
Formato de referencia relativa
Formato de referencia absoluta
Copiar una fórmula sin ajustar las referencias relativas
Ver las fórmulas de la hoja
Convertir una fórmula en valor
Utilizar nombres de rango en fórmulas
Pegar un nombre en una fórmula
Aplicar nombres a fórmulas
Ignorar referencias relativas y absolutas al aplicar nombres
Utilizar nombres de filas o columnas al aplicar nombres
Nombrar fórmulas
Trabajar con vínculos en las fórmulas
Referencias externas
Actualización de vínculos
Modificar el origen del vínculo
Formato de números, fechas y horas
Formatos numéricos
Cambiar formatos numéricos
Personalizar formatos numéricos
Ocultar ceros
Valores de condición
Formatos de fecha y hora
Personalizar formatos de fecha y hora
Eliminar formatos personalizados
4. Crear fórmulas avanzadas
Trabajar con matrices
Fórmulas matriciales
Análisis de las fórmulas matriciales
Fórmulas matriciales que operan en varios rangos
Constantes matriciales
Funciones que utilizan o devuelven matrices
Iteración y referencias circulares
Consolidar datos en varias hojas
Consolidación por posición
Consolidación por categoría
Aplicar reglas de validación de datos a las celdas
Cuadros de diálogo en la hoja
Ficha programador
Controles de formulario
Insertar un control en una hoja
Vincular un control a un valor de celda
Análisis de los controles de la hoja
Cuadros de grupo
Botones de opción
Casillas de verificación
Cuadros de lista y combinados
Barras de desplazamiento y control de número
5. Solucionar problemas en las fórmulas
Valores de error
#¡DIV/0!
#N/A
#¿NOMBRE?
Evitar el error #¿NOMBRE? al eliminar nombres de rango
#¡NULO!
#¡NUM!
#¡REF!
#¡VALOR!
Otros errores de fórmula
Ausencia de paréntesis
Resultados de fórmula erróneos
Referencias circulares
Gestionar errores de fórmula con SIERROR()
Revisor de errores de fórmula
Elegir una acción de error
Configurar las opciones de Comprobación de errores
Auditoría
Funcionamiento de la auditoría
Rastrear celdas precedentes
Rastrear celdas dependientes
Rastrear celdas de error
Quitar flechas
Evaluar fórmulas
Inspeccionar valores de celda
Parte II. Aprovechar el poder de las funciones
6. Funciones
Funciones de Excel
Estructura de una función
Escribir una función en una fórmula
Insertar función
Cargar Herramientas para análisis
7. Funciones de texto
Funciones de texto en Excel
Caracteres y códigos
Función CAR()
Generar el conjunto de caracteres ANSI
Generar una serie de letras
Función CODIGO()
Generar una serie de letras comenzando por cualquier letra
Convertir texto
Función MINUSC()
Función MAYUSC()
Función NOMPROPIO()
Aplicar formato al texto
Función MONEDA()
Función DECIMAL()
Función TEXTO()
Mostrar cuándo se ha actualizado una hoja por última vez
Manipular texto
Eliminar de una cadena caracteres innecesarios
Función RECORTAR()
Función LIMPIAR()
Función REPETIR(): repetir un carácter
Rellenar una celda
Crear gráficos de texto
Función IZQUIERDA()
Función DERECHA()
Función MED()
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 segundo nombre
Determinar la letra de la columna
Sustituir una subcadena por otra
Función REEMPLAZAR()
Función SUSTITUIR()
Eliminar un carácter de una cadena
Eliminar dos caracteres diferentes de una cadena
Eliminar saltos de línea
8. Funciones lógicas y de información
Funciones lógicas
Función SI()
SI(): el caso más sencillo
Gestionar un resultado FALSO
Evitar dividir entre cero
Realizar varias pruebas lógicas
Funciones SI() anidadas
Calcular primas escalonadas
Función Y()
Clasificar valores en categorías
Función O()
Aplicar formato condicional con fórmulas
Combinar funciones lógicas con matrices
Aplicar una condición a un rango
Operar sólo en celdas que cumplen una condició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
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
Buscar valores no numéricos en un rango
Contar el número de errores en un rango
Ignorar errores en un rango
9. Funciones de búsqueda y referencia
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 ponderados de cuestionarios
Integrar botones de opción y la función ELEGIR()
Búsqueda de valores en tablas
Función CONSULTAV()
Función CONSULTAH()
Buscar la tasa de descuento a un cliente en un rango
Buscar una tasa impositiva en un rango
Encontrar coincidencias exactas
Buscar un número de cliente
Combinar búsquedas de coincidencias exactas con listas desplegables dentro de celdas
Operaciones de búsqueda avanzada
Funciones COINCIDIR() e INDICE()
Buscar un valor mediante cuadros de lista
Utilizar cualquier columna como columna de búsqueda
Crear búsquedas de filas y columnas
Crear búsquedas en varias columnas
10. Funciones de fecha y hora
Gestionar fechas y datos con Excel
Introducir fechas y horas
Excel y los años de dos dígitos
Funciones de fecha de Excel
Devolver una fecha
HOY(): La fecha actual
FECHA(): 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 dentro de X años, meses o días
DIA.LAB(): Una alternativa a los días laborables
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 a partir de su fecha de nacimiento
Devolver la fecha de la enésima aparición de un día laborable en un mes
Calcular fechas de festivos
Calcular la diferencia entre dos fechas
Calcular la edad de una persona
Función SIFECHA()
Calcular la edad de una persona (2ª parte)
DIAS.LAB(): Calcular el número de días laborables entre dos fechas
DIAS360(): Calcular la diferencia entre fechas utilizando un año de 360 días
FRAC.AÑO(): Devolver la fracción de un año entre dos fechas
Funciones de hora de Excel
Devolver una hora
AHORA(): Devolver la hora actual
TIEMPO(): Devolver cualquier hora
VALHORA(): Convertir una cadena en una hora
Devolver partes de una hora
Función HORA()
Función MINUTO()
Función SEGUNDO()
Devolver una hora dentro de X horas, minutos o segundos
Sumar valores de hora
Calcular la diferencia entre dos horas
11. Funciones matemáticas
Funciones de redondeo de Excel
Función REDONDEAR()
Función REDOND.MULT()
Funciones REDONDEAR.MAS() y REDONDEAR.MENOS()
Funciones MULTIPLO.SUPERIOR() y MULTIPLO.INFERIOR()
Determinar el trimestre fiscal en que cae una fecha
Calcular las fechas de Semana Santa
Funciones REDONDEA.PAR() y REDONDEA.IMPAR()
Funciones ENTERO() y TRUNCAR()
Utilizar el redondeo para prevenir errores de cálculo
Definir precios con decimales
Sumar valores
Función SUMA()
Calcular totales acumulados
Sumar sólo los valores negativos o positivos de un rango
Función RESTO()
Una fórmula mejor para las diferencias entre horas
Sumar filas alternas
Primer caso especial: sumar sólo filas impares
Segundo caso especial: sumar sólo filas pares
Determinar si un año es bisiesto
Sombrear filas alternas
Generar números aleatorios
Función ALEATORIO()
Generar números aleatorios de n dígitos
Generar una letra aleatoria
Ordenar valores aleatoriamente
Función ALEATORIO.ENTRE()
12. Funciones estadísticas
Fundamentos de la estadística descriptiva
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 k valores
Realizar cálculos con los últimos k valores
Calcular medidas de variación
Calcular el rango
Calcular la varianza
Calcular la desviación estándar
Trabajar con distribuciones de frecuencia
Función FRECUENCIA()
Distribución normal y función DISTR.NORM()
Forma de la curva I: función COEFICIENTE.ASIMETRIA()
Forma de la curva II: función CURTOSIS()
Herramientas estadísticas de Herramientas para análisis
Función Estadística descriptiva
Determinar el coeficiente de correlación entre datos
Herramienta Histogramas
Herramienta Generación de números aleatorios
Herramienta Jerarquía y percentil
Parte III. Crear modelos de negocios
13. Analizar datos con tablas
Convertir un rango en una tabla
Operaciones básicas con tablas
Ordenar una tabla
Orden más complejo
Ordenar una tabla por orden natural
Ordenar una parte del campo
Ordenar sin artículos
Filtrar datos de tabla
Listas de filtro para filtrar una tabla
Filtros rápidos
Mostrar registros filtrados
Criterios complejos para filtrar una tabla
Crear un rango de criterios
Filtrar una tabla con un rango de criterios
Introducir criterios compuestos
Introducir criterios computados
Copiar datos filtrados a un rango diferente
Hacer referencia a tablas en fórmulas
Especificadores de tablas
Introducir fórmulas de tabla
Funciones de tabla de Excel
Introducción
Funciones que no requieren un rango de criterios
Función CONTAR.SI()
Función SUMAR.SI()
Función PROMEDIO.SI()
Funciones de tabla que aceptan múltiples criterios
Función CONTAR.SI.CONJUNTO()
Función SUMAR.SI.CONJUNTO()
Función PROMEDIO.SI.CONJUNTO()
Funciones de tabla que requieren un rango de criterios
Función BDPROMEDIO()
Función BDEXTRAER()
14. Analizar datos con tablas dinámicas
¿Qué son las tablas dinámicas?
Cómo funcionan las tablas dinámicas
Terminología de las tablas dinámicas
Crear tablas dinámicas
Crear una tabla dinámica a partir de una tabla o de un rango
Crear una tabla dinámica a partir de una base de datos externa
Trabajar con una tabla dinámica y personalizarla
Subtotales de tablas dinámicas
Ocultar los totales generales
Ocultar los subtotales
Personalizar el cálculo de subtotales
Cambiar la operación en el resumen de campos de datos
Cálcular diferencias
Calcular el porcentaje
Calcular el saldo acumulativo
Calcular el í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 de la hoja
15. Herramientas para modelos de negocios de Excel
Análisis y 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
Buscar objetivo
¿Cómo funciona Buscar objetivo?
Ejecutar Buscar objetivo
Optimizar el margen del producto
Las aproximaciones de Buscar objetivo
Análisis para cubrir gastos
Resolver ecuaciones algebraicas
Trabajar con escenarios
Fundamentos de los escenarios
Configurar la hoja para escenarios
Añadir un escenario
Visualizar un escenario
Modificar un escenario
Combinar escenarios
Generar un informe de resumen
Eliminar un escenario
16. Análisis de regresión para registrar tendencias y hacer previsiones
Elegir un método de regresión
Regresión simple en datos lineales
Analizar tendencias utilizando líneas optimizadas
Analizar la ecuación de regresión
Analizar R2
Calcular valores optimizados utilizando TENDENCIA()
Calcular valores optimizados utilizando ESTIMACION.LINEAL()
Analizar las ventas frente a la tendencia de publicidad
Hacer previsiones
Trazar valores previstos
Ampliar una tendencia lineal con el controlador de relleno
Ampliar una tendencia lineal mediante el comando Series
Prever con la ecuación de regresión
Prever con TENDENCIA()
Prever con ESTIMACION.LINEAL()
Regresión simple en datos no lineales
Tendencia exponencial
Trazar una línea de tendencia exponencial
Calcular valores de tendencia exponencial y de previsión
Tendencia exponencial y previsiones mediante la función CRECIMIENTO()
Trabajar con una tendencia logarítmica
Trazar una línea de tendencia logarítmica
Calcular valores de tendencia logarítmica y de previsión
Trabajar con una tendencia de potencia
Trazar una línea de tendencia potencial
Calcular valores de tendencia potencial y de previsión
Utilizar análisis de regresión polinómica
Trazar una línea de tendencia polinómica
Calcular valores de tendencia polinómica y de previsión
Análisis de regresión múltiple
17. Solucionar problemas complejos con Solver
Introducción a Solver
Las ventajas de Solver
¿Cuándo tenemos que utilizar Solver?
Instalar Solver
Utilizar Solver
Añadir restricciones
Guardar una solución como escenario
Definir otras opciones de Solver
Seleccionar el método que utiliza Solver
Controlar el funcionamiento de Solver
Trabajar con modelos de Solver
Dar sentido a los mensajes de Solver
Mostrar los informes de Solver
Informe Responder
Informe Confidencialidad
Informe Límites
Parte IV. Crear fórmulas financieras
18. Fórmulas de préstamo
El valor temporal del dinero
Calcular el pago del préstamo
Análisis de pago de un préstamo
Trabajar con un préstamo tipo globo
Calcular los intereses. Parte I
Calcular los intereses. Parte 2
Calcular capital e intereses acumulativos
Crear un plan de amortización de préstamo
Crear un plan de amortización de tipo fijo
Crear un plan de amortización dinámico
Calcular el plazo del préstamo
Calcular el tipo de interés para un préstamo
Calcular la cantidad que se puede pedir prestada
19. Crear fórmulas de inversión
Trabajar con tipos de interés
Interés compuesto
Interés nominal frente a interés efectivo
Conversión entre tipo nominal y tipo efectivo
Calcular el valor futuro
Valor futuro de una cantidad
El valor futuro de una serie de depósitos
Valor futuro de una cantidad más depósitos
Objetivo de inversión
Calcular el tipo de interés
Calcular el número de períodos necesarios
Calcular el depósito regular necesario
Calcular el depósito inicial necesario
Calcular el valor futuro con tipos de interés variables
20. Crear fórmulas de descuento
Calcular el valor actual
La inflación como factor a tener en cuenta
Calcular el valor presente mediante VA()
Inversión mobiliaria frente a inversión inmobiliaria de compra para alquiler
Compra frente a leasing
Descontar flujos de efectivo
Calcular el valor neto actual
Calcular el valor neto actual mediante VNA()
Valor neto actual con flujos de efectivo diferentes
Valor neto actual con flujos de efectivo no periódicos
Calcular el plazo de recuperación
Plazo de recuperación simple sin descontar
Plazo de recuperación descontado
Calcular la tasa interna de retorno
Función TIR()
Calcular la tasa interna de retorno para flujos de efectivo no periódicos
Calcular múltiples tasas internas de retorno
Índice alfabético
Excel es la hoja de cálculo más utilizada en el mundo. Sin embargo, la mayoría de sus usuarios sólo alcanza a controlar una pequeña parte de sus procesos y comandos. No es necesario ser matemático o economista para poder utilizar las fórmulas y funciones, las herramientas que más refuerzan la fiabilidad y potencia del programa.
Este libro le enseña a aprovechar al máximo las novedades de Excel 2010, desde los minigráficos hasta la nueva versión de la opción Solver. Descubra, además, las formas más efectivas de gestionar tareas cotidianas, como generar números de cliente o proyectar el impacto de la inflación.
Las instrucciones son claras y los ejemplos sencillos. Incluye proyectos completos paso a paso para crear calendarios, proyectar flujos de caja, registrar facturas pendientes de pago o analizar defectos. ¡Ahora es más fácil que nunca convertirse en un experto en Excel!
