Aplicaciones prácticas para Excel 2000 presenta todas las sugerencias y técnicas para dominar una de las herramientas más poderosas de hoja de cálculo: las fórmulas. Con claras explicaciones sobre operadores, anidados y funciones, y numerosos ejemplos prácticos este libro comparte soluciones demostradas para los desafíos típicos de Excel, desde el trabajo con fechas para realizar búsquedas en tablas, hasta la creación de fórmulas matriciales. Esta guía le ayudará a utilizar sus hojas de cálculo y obtener el máximo partido de Excel.
John Walkenbach es una autoridad en el software de hojas de cálculo, es el diseñador de Power Utility Pak, un galardonado complemento para Microsoft Excel.
Incluye CD-ROM, el cual contiene excelentes ejemplos adaptables a sus propias necesidades.
Introducción
Por qué escribir este libro
Lo que se debe saber
Lo que se necesita
Convenios que emplea este libro
Convenios de teclado
Convenios del ratón
Significado de los iconos
Organización del libro
Parte I: Información básica
Parte II: Empleo de funciones en las fórmulas
Parte III: Fórmulas matriciales
Parte IV: Diversas técnicas para fórmulas
Parte V: Apéndices
Acerca del CD-ROM que acompaña al libro
Parte I. Información básica
1. El núcleo de Excel
Historia de Excel
Todo empezó con VisiCalc
Después apareció Lotus
Microsoft entra en escena
Versiones de Excel
Concepto del modelo objeto de Excel
Utilización de los libros de trabajo
Hojas de cálculo
Hojas de gráfico
Hojas de macro XLM
Hojas de diálogo
Interfaz de usuario de Excel
Menús
Cuadros de diálogo
Barras de herramientas
Arrastrar y colocar
Teclas de método abreviado
Presentación personalizada en pantalla
Entrada de datos
Selección de objeto y celda
Formato de celdas
Formato de número
Formato de estilo
Fórmulas de hoja de cálculo y funciones
Objetos de la capa de dibujo
Formas
Objeto Vínculos de imagen
Controles de cuadros de diálogo
Gráficos
Personalización en Excel
Macros
Barras de herramientas
Programas de complementos
Herramientas para análisis
Acceso a bases de datos
Esquemas
Administrador de escenarios
Herramientas para análisis
Tablas dinámicas
Auditoria
Complemento Solver
Opciones de protección
Proteger las fórmulas
Proteger la estructura de un libro de trabajo
2. Acciones básicas de las fórmulas
Introducir y modificar fórmulas
Elementos de una fórmula
Introducir una fórmula
Pegar nombres
Espacios y divisiones de líneas
Limitación de las fórmulas
Ejemplos de fórmulas
Modificar fórmulas
Emplear operadores
Operadores de referencia
Ejemplos de fórmulas que emplean operadores
Precedencia del operador
Paréntesis anidados
Cálculo de fórmulas
Referencias de celdas y rangos
Crear una referencia absoluta
Hacer referencia a otras hojas o libros de trabajo
Realizar una copia exacta de una fórmula
Convertir fórmulas en valores
Ocultar fórmulas
Errores de las fórmulas
Referencias circulares
Buscar objetivo
Ejemplo de búsqueda de objetivo
Algo más acerca de la búsqueda de objetivo
3. Empleo de nombres
Elementos del nombre
Métodos para la creación de nombres de celdas y rangos
Crear nombres empleando el cuadro de diálogo Definir nombre
Crear nombres con ayuda del Cuadro de nombres
Crear nombres automáticamente
Nombrar filas o columnas enteras
Nombres creados por Excel
Crear nombres de múltiples hojas
Ámbito de un nombre
Crear nombres al nivel de hoja de cálculo
Combinar nombres al nivel de hoja de cálculo y de libro de trabajo
Referenciar nombres de otro libro de trabajo
Trabajar con nombres de rangos y celdas
Crear una lista de nombres
Emplear nombres en las fórmulas
Emplear operadores de intersección con los nombres
Emplear el operador de rango con nombres
Referenciar una sola celda de un rango de múltiples celdas con nombre
Aplicar nombres a fórmulas existentes
Aplicar nombres automáticamente cuando se está creando una fórmula
Desaplicar nombres
Eliminar nombres
Eliminar celdas o rangos con nombre
Redefinir nombres
Cambiar nombres
Visualizar rangos con nombre
Emplear nombres en los gráficos
Mantenimiento de los nombres de celdas y rangos en Excel
Insertar una fila o una columna
Eliminar una fila o una columna
Cortar y pegar
Problemas potenciales del empleo de nombres
Problemas con nombres al copiar hojas
Problemas con nombres al eliminar hojas
Secretos para comprender los nombres
Nombrar constantes
Nombrar constantes de texto
Emplear funciones de hoja de cálculo en fórmulas con nombre
Emplear referencias de celda y rango en fórmulas con nombre
Emplear fórmulas con nombre con referencias relativas
Técnicas avanzadas que emplean nombres
Emplear la función INDIRECTO en un rango con nombre
Emplear la función INDIRECTO para crear un rango con nombre con una dirección fija
Emplear matrices en fórmulas con nombre
Crear una fórmula dinámica con nombre
Parte II. Empleo de funciones en las fórmulas
4. Introducción a las funciones de hoja de cálculo
¿Qué es una función?
Simplificar las fórmulas
Realizar cálculos que, de otro modo, serían imposibles
Acelerar las tareas de modificación
Proporcionar capacidad de toma de decisiones
Algo más acerca de las funciones
Tipos de argumento de función
Nombres como argumentos
Columnas o filas enteras como argumentos
Valores literales como argumentos
Expresiones como argumentos
Otras funciones como argumentos
Matrices como argumentos
Métodos para introducir una función en una fórmula
Introducir una fórmula manualmente
Emplear el cuadro de diálogo Pegar función para introducir una función
Más sugerencias para la introducción de funciones
Categorías de función
Funciones Financieras
Funciones de Fecha y hora
Funciones Matemáticas y trigonométricas
Funciones Estadísticas
Funciones de Búsqueda y referencia
Funciones de Base de datos
Funciones de Texto
Funciones Lógicas
Funciones de Información
Funciones de Ingeniería
Funciones Definidas por usuario
Otras categorías de función
Funciones de Herramientas para análisis
5. Manipulación de texto
Acerca del texto
¿Cuántos caracteres se puede introducir en una celda?
Números como texto
Funciones de texto
Determinar si una celda contiene texto
Trabajar con códigos de carácter
Determinar si dos cadenas son idénticas
Unir dos o más celdas
Presentar valores formateados como texto
Presentar valores de moneda formateados como texto
Repetir un carácter o cadena
Crear un histograma de texto
Relleno de un número
Eliminar espacios sobrantes y caracteres no imprimibles
Contar caracteres de una cadena
Cambiar un texto a mayúsculas o minúsculas
Extraer caracteres de una cadena
Reemplazar texto por otro texto
Buscar y encontrar dentro de una cadena
Buscar y reemplazar dentro de una cadena
Fórmulas de texto avanzadas
Contar caracteres específicos de una celda
Contar las veces que aparece una subcadena en una celda
Expresar un número como ordinal
Determinar una letra de columna para un número de columna
Extraer el nombre de archivo de una especificación de ruta de acceso
Extraer la primera palabra de una cadena
Extraer la última palabra de una cadena
Extraer todo el texto menos la primera palabra de una cadena
Extraer nombres, segundos nombres y apellidos
Eliminar el tratamiento del nombre
Contar el número de palabras de una celda
6. Trabajar con Fechas y Horas
Fechas y Horas de Excel
Conocer los números de serie de fecha
Introducir fechas
Conocer los números de serie de horas
Introducir horas
Formatear fechas y horas
Problemas con las horas
Funciones relacionadas con fechas
Presentar la fecha actual
Presentar cualquier fecha
Generar una serie de fechas
Convertir una cadena de no-fecha en una fecha
Calcular el número de días entre dos fechas
Calcular el número de días laborables entre dos fechas
Desplazar una fecha utilizando sólo días laborables
Calcular el número de años entre dos fechas
Calcular la edad de una persona
Determinar el día del año
Determinar el día de la semana
Determinar la fecha del domingo más reciente
Determinar el primer día del mes después de una fecha
Determinar la n-ésima aparición de un día de la semana en el mes
Contar las veces que aparece un día de la semana
Calcular fechas de días festivos
Determinar el último día del mes
Determinar si un año es bisiesto
Convertir un año en números romanos
Crear un calendario en un rango
Funciones relacionadas con horas
Presentar la hora actual
Presentar cualquier hora
Sumar horas que exceden las 24 del día
Calcular la diferencia entre dos horas
Convertir un número de cuatro cifras en una fecha
Convertir horas, minutos o segundos decimales en una hora
Añadir horas, minutos o segundos a una hora
Conversión entre dos zonas horarias
Redondear valores de hora
Trabajar con valores no-hora del día
7. Técnicas para contar
Contar y sumar celdas de hoja de cálculo
Contar o sumar registros de una base de datos y de tablas dinámicas
Fórmulas básicas para contar
Contar el número total de celdas
Contar las celdas en blanco
Contar celdas que no están en blanco
Contar celdas numéricas
Contar celdas no-texto
Contar celdas de texto
Contar valores lógicos
Valores de error de un rango
Fórmulas avanzadas de contar
Contar celdas empleando la función CONTAR.SI
Contar celdas empleando un criterio múltiple
Determinar la entrada que se produce con más frecuencia
Contar las veces que aparece un texto específico
Contar el número de valores únicos
Crear una distribución de frecuencia
Fórmulas para sumar
Sumar todas las celdas de un rango
Computar una suma acumulada
Sumar los "n valores más altos"
Sumas condicionales empleando un solo criterio
Sumar sólo valores negativos
Sumar valores basándose en un rango diferente
Sumar valores basándose en una comparación de texto
Sumar valores basándose en una comparación de fechas
Sumas condicionales empleando criterios múltiples
Emplear el criterio Y
Emplear el criterio O
Emplear el criterio Y junto con el criterio O
8. Búsquedas
¿Qué es una fórmula de búsqueda?
Funciones relacionadas con búsquedas
Funciones básicas de búsqueda
Función BUSCARV
Función BUSCARH
Función BUSCAR
Combinar las funciones COINCIDIR e ÍNDICE
Funciones de búsqueda especializadas
Buscar un valor exacto
Buscar un valor hacia la izquierda
Efectuar una búsqueda que diferencia entre mayúsculas y minúsculas
Elegir entre múltiples tablas de búsqueda
Determinar las notas correspondientes a puntuaciones de examen
Calcular la media de puntuación de una nota
Efectuar una búsqueda en dos direcciones
Efectuar una búsqueda de dos columnas
Determinar la dirección de un valor dentro de un rango
Buscar un valor utilizando la coincidencia más cercana
Buscar un valor utilizando una interpolación lineal
9. Bases de datos y listas
Lista de hoja de cálculo o base de datos
¿Para qué se emplean las listas?
Autofiltro
Elementos básicos de Autofiltro
Contar y sumar datos filtrados
Emplear Filtro avanzado
Determinar un rango de criterio
Filtrar una lista
Especificar criterios de Filtro avanzado
Especificar un solo criterio
Especificar criterios múltiples
Especificar criterios de cálculo
Utilizar funciones Base de datos con listas
Resumir una lista con una tabla de datos
Crear subtotales
Cuadro de diálogo Subtotales
Acerca de la función SUBTOTALES
10. Cálculos financieros
Valor temporal del dinero
Cálculos de préstamo
Funciones de hoja de cálculo para calcular información de un préstamo
Ejemplo de cálculo de préstamo
Pagos de tarjetas de crédito
Crear un cuadro de amortización de préstamo
Resumir opciones de préstamo utilizando una tabla de datos
Calcular un préstamo con pagos irregulares
Cálculos de inversiones
Valor futuro de un solo depósito
Valor futuro de una serie de depósitos
Cálculos de depreciación
11. Diversos cálculos
Conversiones de unidades
Utilizar tablas de conversión de unidades
Convertir unidades métricas
Conversiones de medidas de longitud
Conversiones de medidas de peso
Conversión de medidas de capacidad
Conversión de medidas de superficie
Conversión de medidas de volumen
Conversión de medidas de fuerza
Conversión de medidas de energía
Conversiones de tiempo
Conversiones de temperatura
Resolver triángulos rectángulos
Cálculos de área, superficie, circunferencia y volumen
Calcular el área y el perímetro de un cuadrado
Calcular el área y el perímetro de un rectángulo
Calcular el área y el perímetro de un círculo
Calcular el área de un trapecio
Calcular el área de un triángulo
Calcular la superficie y el volumen de una esfera
Calcular la superficie y el volumen de un cubo
Calcular la superficie y el volumen de un cono
Calcular el volumen de un cilindro
Calcular el volumen de una pirámide
Resolver ecuaciones simultáneas
Redondear números
Fórmulas básicas de redondeo
Redondear al múltiplo más cercano
Redondear valores bursátiles
Trabajar con monedas fraccionarias
Utilizar las funciones ENTERO y TRUNCAR
Redondear a un entero par o impar
Redondear a los n primeros dígitos
Parte III. Fórmulas matriciales
12. Introducción a las matrices
Fórmulas matriciales
Fórmula matricial de múltiples celdas
Fórmula matricial de una sola celda
Crear una constante matricial
Elementos de las constantes matriciales
Dimensiones de una matriz
Matrices horizontales de una dimensión
Matrices verticales de una dimensión
Matrices de dos dimensiones
Nombrar constantes matriciales
Trabajar con fórmulas matriciales
Introducir una fórmula matricial
Seleccionar un rango de fórmula matricial
Modificar cualquier fórmula matricial
Ampliar o reducir una fórmula matricial de múltiples celdas
Fórmulas matriciales de múltiples celdas
Crear una matriz a partir de los valores de un rango
Crear una constante matricial a partir de los valores de un rango
Realizar operaciones en una matriz
Utilizar funciones con una matriz
Transponer una matriz
Generar una matriz de enteros consecutivos
Fórmulas matriciales de una sola celda
Contar caracteres de un rango
Sumar los tres valores menores de un rango
Contar las celdas de texto de un rango
Eliminar fórmulas intermedias
Utilizar una matriz en lugar de una referencia de rango
13. Hacer magia con las fórmulas matriciales
Más fórmulas matriciales de una sola celda
Sumar un rango que contiene errores
Contar el número de valores de error de un rango
Sumar basándose en una condición
Sumar los n valores superiores de un rango
Calcular la media aritmética excluyendo ceros
Precisar si un valor determinado aparece en un rango
Contar el número de diferencias entre dos rangos
Devolver la situación del valor máximo de un rango
Encontrar la fila de la n-ésima ocurrencia de un valor en un rango
Devolver el texto más largo de un rango
Determinar si un rango contiene valores válidos
Sumar los dígitos de un entero
Sumar valores redondeados
Sumar cada valor n-ésimo de un rango
Eliminar caracteres no numéricos de una cadena
Determinar el valor más próximo de un rango
Devolver el último valor de una columna
Devolver el último valor de una fila
Clasificación jerárquica de datos con una fórmula matricial
Crear una tabla cruzada dinámica
Más fórmulas matriciales de múltiples celdas
Devolver sólo valores positivos de un rango
Devolver las celdas no vacías de un rango
Invertir el orden de las celdas de un rango
Ordenar un rango de valores dinámicamente
Devolver una lista de elementos únicos de un rango
Desplegar un calendario en un rango
Devolver una matriz a partir de una función personalizada de VBA
Parte IV. Diversas técnicas para fórmulas
14. Referencias circulares intencionadas
Conocer las referencias circulares
Corregir una referencia circular accidental
Conocer las referencias circulares indirectas
Referencias circulares intencionadas
Conocer las iteraciones
Cómo determina Excel las configuraciones de cálculo e iteración
Ejemplos de referencias circulares
Imprimir la hora de una entrada de celda
Calcular el valor más alto introducido en un período de tiempo
Generar enteros únicos aleatorios
Resolver una ecuación recursiva
Resolver ecuaciones simultáneas utilizando una referencia circular
Problemas potenciales con referencias circulares intencionadas
15. Técnicas para gráficos
Representar datos en gráficos
Conocer la fórmula SERIES
Crear vínculos de celdas
Gráfico de progreso hacia un objetivo
Crear un gráfico de Gantt
Crear un histograma comparativo
Crear un gráfico de cuartil
Trazar cada n-ésimo punto de datos
Actualizar una serie de datos automáticamente
Trazar los últimos n puntos de datos
Trazado interactivo de datos
Trazado basado en la fila activa
Seleccionar datos desde un cuadro combinado
Trazar funciones con una variable
Trazar funciones con dos variables
Crear diseños increíbles
Trabajar con líneas de tendencia
Líneas de tendencia lineal
Líneas de tendencia no lineales
Trucos útiles para los gráficos
Guardar múltiples gráficos en una hoja de gráfico
Ver un gráfico incrustado en una ventana
Cambiar el valor de una hoja de cálculo arrastrando los puntos de datos
Emplear gráficos animados
Crear un gráfico de "reloj"
Dibujar con un gráfico XY
16. Tablas dinámicas
Acerca de las tablas dinámicas
Un ejemplo de tabla dinámica
Datos apropiados para una tabla dinámica
Crear una tabla dinámica
Paso 1: especificar la ubicación de los datos
Paso 2: especificar los datos
Paso 3: completar la tabla dinámica
Agrupar elementos de tabla dinámica
Crear un campo calculado o un elemento calculado
Crear un campo calculado de una tabla dinámica
Insertar un elemento calculado en una tabla dinámica
17. Formato condicional y validación de datos
Formato condicional
Especificar un formato condicional
Tipos de formato que se pueden aplicar
Especificar condiciones
Trabajar con formatos condicionales
Fórmulas de formato condicional
Utilizar funciones personalizadas en fórmulas de formato condicional
Validación de datos
Especificar los criterios de validación
Tipos de criterios de validación que se pueden aplicar
Utilizar fórmulas para reglas de validación de datos
Utilizar fórmulas de validación de datos para aceptar sólo entradas específicas
18. Crear Megafórmulas
¿Qué es una Megafórmula?
Crear una Megafórmula: un ejemplo sencillo
Ejemplos de Megafórmulas
Utilizar una Megafórmula para eliminar segundos nombres
Utilizar una Megafórmula para devolver la posición del último carácter de espacio
Utilizar una Megafórmula para determinar la validez de un número de tarjeta de crédito
Ventajas e inconvenientes de las Megafórmulas
19. Herramientas y métodos para la depuración de fórmulas
¿Depurar una fórmula?
Fórmulas: problemas y soluciones
Paréntesis desparejados
Celdas rellenas con ##########
Celdas en blanco que no están vacías
Fórmulas que devuelven un error
Problemas de referencias absolutas o relativas
Problemas de precedencia del operador
Las fórmulas no se calculan
Valores reales frente a valores presentados
Errores de número debidos a la coma flotante
Errores de "vínculos fantasmas"
Errores de referencia circular
Herramientas de auditoría de Excel
Identificar celdas de un tipo determinado
Visualizar las fórmulas
Rastrear las relaciones entre las celdas
Rastrear valores de error
Corregir errores de referencia circular
Otras herramientas de auditoría
Power Utility Pak
Spreadsheet Detective
Excel Auditor
Parte V. Apéndices
A. Introducción a VBA
Acerca de VBA
Introducción al Editor de Visual Basic
Activar el Editor de VB
Componentes del Editor de VB
Utilizar la ventana Proyecto
Utilizar ventanas Código
Introducir un código VBA
Guardar un proyecto
B. Contenido del CD-ROM
Estructura del CD-ROM
\Capítulos
\Power Utility Pak
\Sound-Proof
Power Utility Pak 97
Instalación
Desinstalación
Sound-Proof
Instalación
Empleo
Limitaciones de la demostración
Índice alfabético