Aplicaciones prácticas para Excel 2000

Resumen:

Con Excel 2000 es posible crear gráficos, fórmulas, tablas y otras herramientas para organizar cualquier tipo de información. Este programa da respuesta a las principales necesidades de análisis de datos en todos los ámbitos: financiero, empresarial, educativo, y doméstico.

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.

Indice:
Acerca del autor
Agradecimientos

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