Quedan rigurosamente prohibidas, sin la autorización escrita de los titulares del copyright, bajo las sanciones establecidas en las leyes, la reproducción total o parcial de esta obra por cualquier medio o procedimiento, incluidos la reprografía y el tratamiento informático, así como la distribución de ejemplares mediante alquiler o préstamo públicos.

Presentación

APRENDER FÓRMULAS Y FUNCIONES PARA EXCEL 2010 CON 100 EJERCICIOS PRÁCTICOS

100 ejercicios prácticos resueltos que conforman un recorrido por las más sofisticadas herramientas y funciones del programa. Si bien es imposible recoger en las páginas de este libro todas las posibilidades de cálculo, procesamiento y análisis de datos que ofrece Excel 2010, una vez realizados los 100 ejercicios que componen este manual, el lector conocerá aquellas fundamentales para el usuario ya iniciado que desea dar un paso adelante y sacar máximo provecho a la aplicación.

LA FORMA DE APRENDER

Nuestra experiencia en el ámbito de la enseñanza nos ha llevado a diseñar este tipo de manual, en el que cada función se ejercita mediante la realización de un ejercicio práctico. Dicho ejercicio se halla explicado paso a paso y pulsación a pulsación, a fin de no dejar ninguna duda en su proceso de ejecución. Además, lo hemos ilustrado con imágenes descriptivas de los pasos más importantes y, sobre todo, los resultados que deberían obtenerse y con los recuadros IMPORTANTE, que ofrecen información complementaria sobre los temas tratados en los ejercicios.

Gracias a este sistema se garantiza que una vez realizados los 100 ejercicios que componen el manual, el usuario será capaz de desenvolverse cómodamente con las herramientas más avanzadas de análisis de datos de Excel 2010 y sacar el máximo partido de sus múltiples prestaciones.

LOS ARCHIVOS NECESARIOS

En el caso de que desee utilizar los archivos de ejemplo de este libro, cosa que recomendamos, puede descargarlos desde la zona de descargas de la página de Marcombo (www.marcombo.com) y desde la página específica de este libro.

A QUIÉN VA DIRIGIDO EL MANUAL

Este libro está destinado a usuarios ya iniciados en el uso básico de Excel, puesto que comienza directamente con la introducción de fórmulas. Si es usted un experto en el programa, le resultará muy útil para consultar particularidades sobre el uso de ciertas herramientas y funciones y para conocer útiles modelos de análisis.

Aunque cada ejercicio está tratado de forma independiente, le recomendamos seguir el orden del libro, puesto que en muchos ejercicios se trabaja de forma progresiva sobre las mismas hojas de cálculo. Además, hemos intentado agrupar aquellos ejercicios con temática común. De cualquier manera, los ejercicios son independientes y si necesita realizar una consulta puntual, podrá dirigirse al ejercicio en el que se trata el tema y llevarlo a cabo sobre su propio proyecto.

APRENDER FÓRMULAS Y FUNCIONES PARA EXCEL 2010 CON 100 EJERCICIOS PRÁCTICOS

Con este manual aprenderá a utilizar diferentes tipos de referencias y nombres de celdas y rangos. Podrá representar sus datos en tablas de datos, tablas dinámicas, matrices, toda clase de gráficos, minigráficos y formatos condicionales.

Trabajará con las avanzadas herramientas de datos de la aplicación que permiten, por ejemplo, ubicar y corregir errores de planteamiento así como crear criterios de validación. Realizará complejos análisis de hipótesis con cada uno de los sistemas que ofrece Excel 2010 y, finalmente, conocerá y usará desde diferentes perspectivas una inmensa cantidad de funciones para muy diversos tipos de análisis, tanto numéricos, como lógicos y de texto. Entre muchas otras cosas, calculará cada uno de los valores implicados en un préstamo bancario o en una inversión, valorará si le conviene económicamente alquilar o comprar un equipo, conocerá complejos métodos de búsqueda de datos para tablas de gran tamaño, concatenará datos y usará las más importantes funciones estadísticas que ofrece el programa.

Cómo funcionan los libros “Aprender…”

Índice

001 Introducir fórmulas

002 Trabajar con referencias

003 Editar una fórmula

004 Usar autorrelleno y referencias absolutas

005 Usar referencias mixtas

006 Insertar funciones: Usar SUMA

007 Otras funciones de Autosuma

008 Calcular subtotales

009 Nombrar celdas y rangos

010 Administrar y usar nombres (I)

011 Administrar y usar nombres (II)

012 Trabajar con referencias

013 Celdas precedentes y celdas dependientes

014 Usar referencia externa a libro abierto

015 Referencias circulares y cálculo manual

016 Crear y editar un gráfico de columnas

017 Crear y editar gráficos circulares

018 Crear y editar un minigráfico

019 Dar a los datos formato de tabla

020 Trabajar con subtotales en una tabla

021 Aplicar formato condicional

022 Administrar reglas de formato condicional

023 Crear informes de tablas dinámicas

024 Modificar informes de tablas dinámicas (I)

025 Modificar informes de tablas dinámicas (II)

026 Crear gráficos dinámicos

027 Editar un gráfico dinámico

028 Crear fórmulas de matriz

029 Modificar fórmulas de matriz

030 Trabajar con constantes de matriz

031 Operaciones reales con constantes de matriz

032 Dividir texto en columnas

033 Quitar duplicados

034 Definir criterios de validación de datos

035 Crear una lista desplegable de datos

036 Consolidar datos de varias tablas

037 Analizar en tabla de datos de una variable

038 Analizar en tabla de datos de dos variables

039 Analizar margen neto en diversos escenarios

040 Crear un resumen de escenarios

041 Ajustar una variable para alcanzar objetivo

042 Ajustar variables a objetivo con Solver (I)

043 Ajustar variables a objetivo con Solver (II)

044 Interpretar un error en una fórmula

045 Introducción al uso de funciones

046 Manipular caracteres con funciones de texto

047 Extraer, sustituir y reemplazar texto

048 Cambiar minúsculas y mayúsculas

049 Convertir valores en texto y texto en valores

050 Buscar desde el valor de la primera columna

051 Buscar usando referencias relativas (I)

052 Buscar usando referencias relativas (II)

053 Mostrar dirección y usar referencia indirecta

054 Transponer un rango con una función

055 Calcular precio variable con función SI

056 Conocer las otras funciones lógicas

057 Redondear un valor (I)

058 Redondear un valor (II)

059 Crear sumas condicionales

060 Sumar el acumulado para cada fila

061 Calcular las cuotas de un préstamo

062 Calcular cuotas para alcanzar una inversión

063 Estimar parte amortizada e intereses

064 Calcular el valor actual de una inversión

065 Calcular el valor futuro de una inversión

066 En cuánto tiempo podría pagar

067 Estimar la tasa de interés ideal

068 Calcular el valor presente neto de inversión

069 Calcular la tasa interna de retorno

070 El valor presente neto no periódico

071 Calcular TIR para flujos no periódicos

072 Calcular tasa interna de retorno múltiple

073 Analizar la tasa interna de retorno múltiple

074 Calcular el valor futuro para tasas variables

075 Retorno sobre inversión de interés variable

076 Calcular interés efectivo e interés nominal

077 Calcular el pago a capital acumulado

078 Calcular el pago de intereses acumulado

079 ¿Comprar o alquilar?

080 Estimar depreciación lineal

081 Calcular depreciación acelerada

082 Depreciación acelerada y por meses

083 Depreciación por disminución variable (I)

084 Depreciación por disminución variable (II)

085 Calcular depreciación acumulada

086 Entender las fechas como lo hace Excel

087 Extraer datos de una fecha

088 Contar a partir del día de hoy y hasta hoy

089 Trabajar con días laborables

090 Entender las horas como lo hace Excel

091 Extraer horas, minutos y segundos

092 Calcular con horas

093 Sumar horas trabajadas

094 Realizar un cálculo de tiempo muy complejo

095 Calcular promedio, media y mediana

096 Otras formas de medir tendencias centrales

097 Establecer jerarquías

098 Percentiles, frecuencias, los de moda y los 10 más

099 Usar algunas funciones matemáticas

100 Obtener información sobre sus datos

001	Introducir fórmulas

LAS FÓRMULAS TRABAJAN CON OPERADORES que indican a la aplicación la operación que debe realizar con determinados valores. Al introducir estos operadores aritméticos en las celdas de una hoja de Excel, la convertimos en una calculadora capaz de realizar operaciones de gran complejidad.

IMPORTANTE

Las fórmulas pueden introducirse directamente en la celda o bien en la Barra de fórmulas y deben ir precedidas del signo Igual (=) que las identifica como tales. Una vez ha introducido una fórmula, también puede aplicarla usando las teclas de desplazamiento. De este modo, además de confirmar la entrada, puede ubicarse en la celda que más le interese.

1 La escritura de fórmulas en Excel 2010 es muy sencilla y, en general, bastante lógica. En este ejercicio introduciremos algunas fórmulas muy simples utilizando valores constantes, para conocer los operadores básicos con los que trabaja la aplicación. Para comenzar, abra la aplicación o, si ya la tiene abierta, cree un nuevo libro en blanco.
2 Seleccione la celda A1 e introduzca el signo Igual (=). Cuando el primer caracter introducido en una celda es el signo Igual (=), la aplicación entiende que se introducirá una fórmula.
3 Llamamos operadores aritméticos a los símbolos que indican la operación matemática que se debe realizar. La suma de dos valores constantes es una de las fórmula más simples que puede introducir y usa simplemente el sigo Más (+). Escriba la fórmula =170+30 directamente desde su teclado y pulse el botón Introducir, que es el que muestra el signo de verificación en la Barra de fórmulas. Las fórmulas en Excel siempre comienzan con un signo Igual (=).El botón Introducir permite aplicar la fórmula sin necesidad de desplazarse a otra celda de la hoja.Aunque al aplicar una fórmula ésta es sustituida en la celda por el resultado de la operación, en la Barra de fórmulas puede ver la fórmula siempre que la celda esté seleccionada
4 El programa muestra el resultado de la operación en la celda, aunque aún puede verse en la Barra de fórmulas el contenido real de la celda A1, es decir, la fórmula introducida. Pulse la tecla Desplazamiento hacia abajo para seleccionar la celda A3.
5 Introduciremos una resta en la celda seleccionada y para ello usaremos como operador un guión corto. Introduzca desde su teclado la fórmula =893-94 (siempre sin punto final) y pulse la tecla Retorno para aplicar la fórmula.
6 Al usar la tecla Retorno, se realiza el cálculo y se selecciona automáticamente la casilla inferior. El asterisco (*) es el signo utilizado para expresar una multiplicación. Introduzca desde su teclado la fórmula =155*1015.
7 Pulse la tecla Tabulador para introducir la fórmula y al mismo tiempo desplazarse a la celda de la derecha, es decir, B3.
8 Como ve, la celda en cuestión muestra el producto obtenido. Pulse la tecla Retorno.
9 Automáticamente se ubica en la siguiente celda de la primera columna, ya que es en la que está trabajando, donde introduciremos una división usando el signo la barra inclinada (/). Introduzca la fórmula =999/3 y pulse el botón Introducir de la Barra de fórmulas.Puede moverse de una celda a la otra con clics de ratón, o usando las teclas de desplazamiento, la tecla Tabulador o la tecla Retorno.
10 Nuevamente la aplicación calcula en resultado en la celda, mientras continúa mostrando la fórmula en la Barra de fórmulas. Pulse Ctrl.+G para abrir el cuadro de diálogo Guardar como, introduzca el nombre Libro de prueba y guárdelo en la ubicación que prefiera.

IMPORTANTE

Además de los operadores aritméticos que hemos usado en este ejercicio, puede usar el acento circunflejo (^) para indicar una ponencia. Así, la fórmula =3^3 dará como resultado 9.

002	Trabajar con referencias

UNA FÓRMULA PUEDE TRABAJAR CON CONSTANTES o también puede contener referencias que la vinculen a otras celdas. Para ello simplemente deberá usar, en lugar del valor de la variable, el nombre de la celda que la contiene, es decir, su referencia.

IMPORTANTE

Observe que al crear una referencia a otra celda que contiene una fórmula, la aplicación asume para la referencia el valor que da como resultado la fórmula de la celda referida.

1 En este ejercicio crearemos fórmulas con referencias a los datos que introdujimos en el ejercicio anterior. Seleccione con un clic la celda B2.
2 En la celda seleccionada, introduciremos una fórmula igualmente sencilla pero con referencia a la celda C8. Haga clic en la Barra de fórmulas e introduzca desde su teclado la fórmula =A1+10.
3 Observe que al escribir la referencia de la celda A1, Excel ha marcado de color azul los bordes de la celda en cuestión. Pulse la tecla Retorno para hacer efectiva la entrada de la fórmula y ver cómo ésta se aplica de inmediato.Al introducir en una fórmula una referencia a otra celda, tanto la celda en cuestión como su referencia se tiñen de un mismo color que las distingue.
4 Introduciremos ahora una nueva fórmula utilizando únicamente referencias. Escriba la fórmula =A2-A3.
5 Al trabajar con varias referencias, cada una se marca en color un color diferente, en este caso azul y verde, al ser introducidas en la fórmula. Pulse la tecla Retorno para hacer efectiva la fórmula y seleccione de nuevo la celda B2, que ya muestra el resultado de la resta. Si selecciona una celda en la que ha aplicado una fórmula previamente, puede ver en la Barra de opciones su contenido real.
6 La Barra de fórmulas muestra la fórmula introducida, tal como pasaba al trabajar con valores absolutos. Pulse la tecla Desplazamiento hacia abajo para seleccionar la celda B3, introduzca el signo = y haga un clic en la celda A3.
7 Como ha introducido un signo Igual antes de seleccionar la celda A3, ésta se muestra resaltada por un borde parpadeante y su nombre se añade automáticamente a la celda que está editando, como otra variable de la fórmula. Introduzca una barra inclinada (/) y pulse en la celda A2.
8 Ahora la última celda seleccionada (la A2)muestra el borde parpadeante mientras que la celda A3 muestra bordes azules, tal como sucedía al introducir su nombre manualmente. Pulse la tecla Retorno.
9 Se realiza el cálculo y se selecciona la celda B4. Una fórmula también puede comenzar por un signo menos. En ese caso el primer valor será negativo. En la celda B4, introduzca el signo - y pulse en la celda A1.
10 El valor de la variable -A1 que ha introducido de este modo, en este caso es igual a -200. Introduzca ahora *A4 y pulse la tecla Retorno.
11 Se muestra el resultado, que es negativo. Pulse en la celda B4.
12 Observe que aunque Excel le permitió crear una fórmula sin introducir primero el signo =, éste ha sido incorporado automáticamente por la aplicación al confirmar la entrada de la fórmula. Seleccione la celda C1.
13 Puede, evidentemente, crear fórmulas más complejas usando la misma técnica. Ya sea introduciendo las referencias manualmente o haciendo clics en las celdas correspondientes, introduzca la siguiente fórmula: =A2*B2/03-A4. Excel siempre utiliza un color distinto para indicar las celdas que corresponden a cada referencia.
14 Pulse la tecla Retorno para ver el resultado y guarde los cambios.

003	Editar una fórmula

LAS FÓRMULAS SE EDITAN de la misma forma que se editaría un texto: reescribiéndolas directamente en la celda o haciéndolo desde la Barra de fórmulas. Probablemente la forma más rápida de hacerlo es con un doble clic sobre la celda que la contiene. De esta forma el punto de inserción aparece dentro de la celda para realizar los cambios oportunos.

IMPORTANTE

Cuando introducimos el nombre de una celda en una fórmula, creamos una referencia que indica que la fórmula se calculará en función del contenido de dicha celda (o del resultado de la operación que ésta contenga). Si el contenido de la celda a la que se hace referencia cambia, el resultado del cálculo de la segunda fórmula también cambiará automáticamente.

1 En este ejercicio veremos distintas formas de modificar una fórmula una vez ha sido introducida. Seleccione la celda A1 y haga clic en la Barra de fórmulas de manera que el cursor se sitúe al final de la fórmula. La fórmula insertada en la celda A1 está formada por constantes, es decir, números o valores introducidos directamente desde el teclado.
2 Elimine el valor usando la tecla Retroceso e introduzca en su lugar el valor 200.
3 Pulse la tecla Retorno para confirmar la modificación en la fórmula y compruebe cómo el valor de la celda A1 se actualiza al momento.
4 Ahora pulse Ctrl.+Z mientras observa atentamente los valores de las celdas B1 y B4. Al cambiar la fórmula en la celda A1, cambia su resultado, así como el de todas las celdas que hacen referencia a ésta.
5 Al modificar uno de los valores a los que estas celdas hacen referencia, se han actualizado los resultados de cada una de ellas de forma automática. Pulse Ctrl.+Mayúsculas+Z para rehacer y compruebe una vez más la actualización automática de las celdas que contienen referencias a A1.
6 Active la celda B1 y seleccione con un arrastre de ratón la referencia A1 en la Barra de fórmulas.
7 Haga un clic sobre la celda A4.
8 La celda se ve nuevamente enmarcada en el borde parpadeante y su referencia en la fórmula es de color azul. Pulse la tecla Retorno para confirmar la modificación.
9 Haga ahora un doble clic en la celda B3.
10 La fórmula que contienen vuelve a mostrarse en la propia celda, que además se hace editable. Seleccione la primera referencia (A3) y sustitúyala por la referencia D1.
11 Pulse la el botón Introducir.
12 Dado que la celda D1 está vacía, su valor numérico equivale a 0 y, por lo tanto, el resultado de la fórmula de la celda B3 es ahora igual a 0. Si ahora quisiera eliminar esta fórmula y sustituirla por otra, bastaría con situarse en la celda que la contiene y escribir la nueva fórmula. En lugar de esto seleccione nuevamente la celda A1 y pulse la tecla F2.
13 También de este modo la fórmula que contiene se hace editable. Pulse F9.
14 Tanto en la celda como en la Barra de fórmula puede ver que la fórmula ha sido sustituida por el valor que daba como resultado (343). Haga ahora un doble clic sobre la celda B3 y, cuando se haga editable, seleccione el texto D1.
15 Pulse la tecla F9 y observe cómo en este caso sólo el segmento seleccionado de la fórmula es sustituido por el valor de la celda a la que hacía referencia.

IMPORTANTE

Evidentemente, si sustituye una fórmula por su resultado usando la tecla F9, los cambios posteriores en la celdas a las que la fórmula hacía referencia no afectarán al valor de la celda en cuestión.

12 3 4 Weiter

Aprender fórmulas y funciones con Excel 2010 con 100 ejercicios prácticos

Andere haben auch gelesen:

Weitere Bücher von diesem Autor