Aprender Excel financiero y para MBA

Aprender

Excel financiero y para MBA con 100 ejercicios prácticos

Aprender Excel financiero y para MBA con 100 ejercicios prácticos

© 2014 MEDIAactive

Primera edición, 2014

© 2014 MARCOMBO, S.A.

Gran Via de les Corts Catalanes, 594

08007 Barcelona

www.marcombo.com

«Cualquier forma de reproducción, distribución, comunicación pública o transformación de esta obra sólo puede ser realizada con la autorización de sus titulares, salvo excepción prevista por la ley. Diríjase a CEDRO (Centro Español de Derechos Reprográficos, www.cedro.org) si necesita fotocopiar o escanear algún fragmento de esta obra».

ISBN: 978-84-267-2334-5

Presentación
APRENDER EXCEL FINANCIERO Y PARA MBA CON 100 EJERCICIOS PRÁCTICOS

100 ejercicios prácticos resueltos que conforman un recorrido por las herramientas de Microsoft Excel más relevantes para administración y finanzas. Si bien es imposible recoger en las páginas de este libro todas las prestaciones de Excel 2013, hemos escogido las más utilizadas en el ámbito empresarial. Una vez realizados a conciencia los 100 ejercicios que componen este manual, el lector será capaz de gestionar complejas hojas de cálculo y trabajar con herramientas avanzadas de tratamiento de datos.

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 una de las funciones 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 o de los resultados que deberían obtenerse y con recuadros IMPORTANTE que ofrecen información complementaria sobre cada uno de 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 el programa y sacar el máximo partido de las múltiples prestaciones administrativas que ofrece.

LOS ARCHIVOS NECESARIOS

En el caso de que desee utilizar los archivos de ejemplo de este libro 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 ha sido concebido para usuarios iniciados en el trabajo con Excel, aunque no es necesario que sea un usuario avanzado para aprovechar el manual. Por otra parte, si es usted un experto en el programa, le resultará también muy útil para consultar determinados aspectos complejos o repasar funciones específicas que podrá localizar en el sumario.

Cada ejercicio está tratado de forma independiente, por lo que no es necesario que los realice por orden (aunque así se lo recomendamos, puesto que hemos intentado agrupar aquellos ejercicios con temática común). De este modo, si necesita realizar una consulta puntual, podrá dirigirse al ejercicio en el que se trata el tema y llevarlo a cabo sobre su propio libro de Excel.

EXCEL

Excel es el programa líder en gestión de hojas de cálculo y es una herramienta indispensable en cualquier empresa, pues ofrece múltiples y avanzadas herramientas de tratamiento de datos.

Este manual le ayudará a sacar máximo provecho a la aplicación a través del uso de herramientas de análisis que darán un aspecto impresionante a sus informes. Hablamos, por ejemplo, de tablas, gráficos, minigráficos, formatos condicionales, esquemas y subtotales.

Además, conocerá las funciones más relevantes para el ámbito empresarial, tanto para el trabajo con datos textuales como para valores numéricos y conocerá las herramientas del grupo análisis, que le permiten plantear de forma clara, organizada y sencilla diversos escenarios.

Finalmente, aprenderá a trabajar con macros, lo que optimizará sin lugar a dudas la realización de operaciones habituales y repetitivas en sus hojas de cálculo.

Cómo funcionan los libros “Aprender...”

Índice

001 Nombrar celdas y rangos

002 Las celdas precedentes y las dependientes

003 Insertar referencias a otras hojas y libros

004 Editar varias hojas simultáneamente

005 Referencias circulares y cálculo manual

006 El relleno automático y el relleno flash

007 Convertir datos en tabla

008 Usar la función subtotales

009 Crear esquema por subtotales de la tabla

010 Crear un esquema de forma automática

011 Crear fórmulas de matriz

012 La edición de fórmulas de matriz

013 Crear constantes de matriz

014 Calcular con constantes de matriz

015 Aplicar formato condicional

016 Aplicar análisis instantáneo

017 Insertar en celda controles de formulario

018 Utilizar el pegado especial

019 Crear y editar un gráfico en Excel (I)

020 Crear y editar un gráfico en Excel (II)

021 Crear gráficos de anillas

022 Crear gráficos de cotización

023 Crear gráficos combinados

024 Gráficos de burbujas y radiales

025 Trabajar con los minigráficos

026 Crear tablas dinámicas

027 Utilizar segmentación de datos

028 Crear un gráfico dinámico

029 Dividir una celda en varias columnas

030 Eliminar duplicados de una serie de datos

031 Establecer validaciones de datos

032 Crear una lista de validación de datos

033 Consolidar datos de varias tablas

034 Crear un histograma o diagrama de Pareto

035 Hacer análisis con Tablas de datos (I)

036 Hacer análisis con Tablas de datos (II)

037 Trabajar hipótesis en diversos escenarios

038 Realizar un resumen de escenario

039 Formular una hipótesis para alcanzar un objetivo

040 Ajustar para lograr un objetivo con Solver (I)

041 Ajustar para lograr un objetivo con Solver (II)

042 Ajustar para lograr un objetivo con Solver (III)

043 Buscar un valor en una fila

044 Buscar con referencias relativas a una celda

045 Usar referencias relativas a una celda

046 Funciones DIRECCIÓN E INDIRECTO

047 Trasponer datos en una matriz

048 Concatenar, largo izquierda, hallar

049 Extrae, reemplazar y sustituir

050 Convertir valores en texto y texto en valores

051 Calcular precio variable con función SI

052 Otras funciones lógicas: Y, O y NO

053 Redondeos controlados (I)

054 Redondeos controlados (II)

055 Sumar bajo condiciones

056 Sumar acumulados por fila

057 Calcular cuotas para un préstamo

058 Calcular inversión necesaria

059 Calcular amortización e intereses

060 Calcular el valor actual de una inversión

061 Calcular el valor futuro de una inversión

062 Calcular plazo de inversión o préstamo

063 Calcular la tasa de interés asumible

064 Calcular el valor presente neto

065 Calcular tasa interna de retorno

066 El valor presente neto no periódico

067 Calcular TIR para flujos no periódicos

068 Calcular tasa interna de retorno múltiple

069 Analizar la tasa interna de retorno múltiple

070 Calcular el valor futuro con tasas variables

071 Retorno sobre inversión con interés variable

072 Calcular interés efectivo e interés nominal

073 Calcular el pago a capital acumulado

074 Calcular el pago de intereses acumulado

075 Decidir si es mejor comprar o contratar

076 Estimar depreciación lineal

077 Calcular depreciación acelerada

078 Depreciación acelerada y por meses

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

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

081 Calcular depreciación acumulada

082 Trabajar con fechas en Excel

083 Realizar cálculos con fechas

084 Trabajar con horas en Excel

085 Extraer horas, minutos y segundos

086 Realizar cálculos con horas

087 Sumar horas trabajadas

088 Combinar horas con otros datos numéricos

089 Calcular promedio, mediana y moda

090 Otras medidas de tendencia central

091 Definir jerarquías

092 Percentiles, frecuencias y K.ESIMO.MAYOR

093 Usar las principales funciones matemáticas

094 Obtener información sobre datos

095 Grabar una macro

096 Ejecutar y modificar una macro

097 Aplicar una macro de otro libro

098 Crear botón de acceso rápido para macro

099 Insertar botón de macro en una hoja

100 Establecer la seguridad para macros

001 Nombrar celdas y rangos

HABITUALMENTE NOS REFERIMOS a celdas y rangos por la identificación de su columna y su fila, pero también podemos darles nombres de manera que luego podamos referirnos a ellos en fórmulas y funciones a partir de los nombres establecidos.

IMPORTANTE

El primer caracter del nombre de una celda o un rango debe ser una letra, un guión bajo o una barra invertida. Después del primer caracter, el nombre puede usar letras, números, puntos y guiones bajos. Un nombre no distingue entre mayúsculas y minúsculas, y no puede ser idéntico a una referencia de celda, ni tener espacios en blanco. Puede tener hasta 255 caracteres (¡que son muchos!).

1. En este primer ejercicio, descargue de nuestra web el archivo Ejemplo1 y abra su hoja Ventas mensuales.

2. Otorgaremos un nombre para las celdas donde se establecen las comisiones y el descuento. Seleccione la celda B26.

3. Active la ficha Fórmulas y haga clic sobre el botón Asignar nombre del grupo Nombres definidos.

También puede acceder al cuadro Nombre nuevo desde la opción Definir nombre del menú contextual de una celda o un rango de celdas seleccionado.

4. El cuadro Nombre nuevo asigna como nombre el texto de la celda más próxima, que es Descuento y en este caso es perfecto. El siguiente campo, Ámbito, se refiere a la ubicación donde el nombre será recocido. Mantenga la opción Libro seleccionada, para que el nombre afecte a todo el archivo.

5. Puede además añadir algún comentario. Escriba por ejemplo el texto Distribuidores y pulse el botón Aceptar.

Los comentarios añadidos a un nombre pueden ayudar más adelante a identificar mejor el contenido de la celda o rango.

6. Pulse en la celda B22 e introduzca la siguiente fórmula: =B21* y pulse sobre la celda B26 para insertarla en la función.

7. En la fórmula no se inserta la referencia a la celda sino el nombre que le hemos asignado, que es Descuento. Aplique la fórmula y copie esta celda en las dos contiguas: C22 y D22.

Los nombres actúan como referencias absolutas.

8. Ubíquese en la celda D22 y compruebe que el nombre actúa como referencia absoluta sin necesidad de usar el signo $.

9. Seleccione las celdas B2, a B21 y pulse de nuevo el botón Asignar nombre.

10. En este caso el campo Nombre muestra el texto de la cabecera del rango de celdas seleccionado. Seleccione el ámbito Ventas mensuales para que tenga efecto sólo en la hoja activa, confirme que el campo Hace referencia a indique el rango correcto y pulse el botón Aceptar.

11. Haga un doble clic en la celda E21, seleccione en la barra de direcciones el rango utilizado como argumento de la función (B2:B20) y escriba la palabra Enero para seleccionar el rango en cuestión.

12. Tal como lo hizo en pasos anteriores para crear el rango enero, cree los rangos febrero y marzo.

13. Seleccione la celda B27 y pulse en el campo Nombre, a la izquierda de la Barra de fórmulas.

14. Éste se hace editable. Introduzca el texto Comisiones y pulse la tecla Retorno.

También puede asignar un nombre a una celda o un rango introduciéndolo en el campo Nombre.

15. También este sencillo método le permite dar un nombre a una celda. En este caso se aplica automáticamente a todo el libro.

IMPORTANTE

El Administrador de nombres le permite crear un nuevo nombre desde este cuadro, editar el nombre seleccionado, borrarlo o cambiar el rango de celdas al que se refiere. Además, si pulsa en el botón que está a la derecha del campo Se refiere a, el cuadro se minimiza para permitirle seleccionar un nuevo rango de celdas.

002 Las celdas precedentes y las dependientes

LAS CELDAS PRECEDENTES SON AQUELLAS a las que hace referencia una fórmula y las dependientes aquellas que son utilizadas en las fórmulas de otras celdas. Con los comandos del grupo Auditoría de fórmulas de la ficha Fórmulas de la Cinta de opciones, resulta muy fácil ubicar a unas y a otras de manera gráfica, no importa lo compleja que pueda ser la hoja de cálculo con la que está trabajando.

1. En este ejercicio continuaremos trabajando con la hoja Ventas mensuales del archivo Ejemplo1, para conocer los comandos del grupo Auditoría de fórmulas que nos permiten mostrar en la hoja la vinculación entre celdas con fórmulas. Seleccione la celda B21.

2. Pulse sobre el comando Rastrear precedentes del grupo Auditoría de fórmulas en la ficha Fórmulas.

El comando Rastrear precedentes muestra en qué fórmulas ha sido usada la referencia a la celda seleccionada.

3. Automáticamente aparece una flecha azul que parte de la celda B2 y acaba en la celda seleccionada. En este caso indica que el rango que comienza con la celda B2 es precedente a la celda B21.

4. Muestre ahora las celdas precedentes de B22.

5. Efectivamente, son precedentes de esta celda el rango enero, la celda B22 y la celda B26.

6. También es posible mostrar gráficamente las fórmulas a las que nutre una celda con datos o, lo que es lo mismo, mostrar las celdas dependientes. Haga clic en la celda B26.

7. Pulse ahora el comando Rastrear dependientes del grupo Auditoría de fórmulas.

El comando Rastrear dependientes muestra en qué fórmulas ha sido usada la referencia a la celda seleccionada.

8. En este caso la flecha azul parte de la celda seleccionada y señala las celdas B22 y C22 y D22, pues B26 es uno de los valores implicados en las tres fórmulas. Podríamos comprobarlo pulsando directamente sobre cada una de estas celdas y leyendo la fórmula en la Barra de fórmulas pero en esta ocasión, utilizaremos otro de los comandos de auditoría de fórmulas. Haga clic sobre el comando Mostrar fórmulas, situado a la derecha del comando Rastrear precedentes.

9. Se muestra en cada celda la fórmula que contiene en lugar de su resultado. Para desactivar el comando Mostrar fórmulas, pulse de nuevo en su botón.

10. Seleccione de nuevo la celda B22 y despliegue el comando Quitar flechas del grupo Auditoría de fórmulas pulsando en su botón de punta de flecha.

11. Seleccione la opción Quitar un nivel de precedentes.

12. La flecha que venía del rango enero desaparece, pues este es precedente de forma indirecta, en un segundo nivel. Ahora pulse directamente en el botón Quitar flechas para borrar todas las flechas de la hoja.

13. Para terminar, guarde los cambios pulsando el comando Guardar de la Barra de herramientas de acceso rápido.

IMPORTANTE

El resto de comandos incluidos en el grupo Auditoría de fórmulas nos permiten localizar errores comunes en fórmulas y depurarlas evaluando cada una de sus partes.

003 Insertar referencias a otras hojas y libros

EN HOJAS DE CÁLCULO MAS COMPLEJAS es posible que necesite crear referencias a celdas de otra hoja e incluso, de otro libro. Al realizar un cambio en los datos de origen, se actualizan también los datos de destino, es decir, los que contienen la referencia externa.

1. En la ficha Vista, grupo Ventana de la Cinta de opciones pulse el botón Nueva ventana.

2. Ahora en la barra de títulos de la aplicación se lee Ejemplo1:2 para indicar que es una segunda ventana del mismo libro. Pulse el botón Organizar todo.

3. En el cuadro Organizar Ventanas, seleccione las opciones Vertical y Ventanas del libro abierto.

4. Ahora puede ver las dos ventanas del libro simultáneamente. Active la hoja Ventas_1T en la segunda ventana.

5. Así será más cómodo, aunque podría realizar el ejercicio saltando de un archivo al otro. Active la hoja Ventas_1t en la ventana 2, introduzca en la celda B2 de esta hoja la función SUMA(), y como argumentos seleccione con un arrastre las celdas B2:D2 de la hoja Ventas mensuales. En la función se indicará entre comillas simples más un signo de admiración al cierre el nombre de la hoja antes del rango (‘Ventas mensuales’!B3:B24).

6. Copie y pegue ésta fórmula en el rango B3:B21 para que se muestren todos los totales.

7. Ahora cree un nuevo libro en blanco y colóquelo sobre la segunda ventana. En la celda A1 de este libro escriba la palabra Descuentos y, el la celda B2 escriba Comisiones. Luego pulse en B1, introduzca un signo Igual (=) y haga clic en la celda B26 de la hoja activa en el libro Ejemplo1.

8. Aparece entre corchetes el nombre del libro, seguido del nombre de la hoja. Ambos datos están entre comillas simples e, inmediatamente después de éstas, un signo de admiración de cierre y la referencia de la celda pegada. Ésta es la sintaxis que deberá usar para teclear una referencia externa. Pulse la tecla Retorno para introducir la referencia y compruebe cómo se muestra el resultado.

9. En la Ventas mensuales del archivo Ejemplo1, ubíquese en la celda B27 y pulse el comando Copiar (ficha Inicio, grupo Portapapeles).

10. Seleccione la celda B2 de la hoja1 del libro nuevo, despliegue el comando Pegar del grupo Portapapeles en la ficha Inicio y, de la lista de opciones de pegado que aparece, seleccione la opción Pegar vínculo, el segundo icono de la sección Otras opciones de pegado.

Evidentemente, si cambia el contenido de las celdas B26 y B27 de la hoja Ventas mensuales en el libro Ejemplo1, cambiarán también los valores de las celdas B1 y B2 en la Hoja1 del libro Nuevo1.

11. Se pega el valor de la celda y observe la Barra de fórmulas que se ha creado una referencia externa usando la misma sintaxis que ya conoce. Guarde el nuevo libro con el nombre Nuevo1 y ciérrelo, y cierre también la ventana Ejemplo1:2.

004 Editar varias hojas simultáneamente

AL TRABAJAR CON LIBROS COMPLEJOS DE varias hojas, puede ser de utilidad realizar una edición conjunta de todas de forma simultánea. Para lograrlo, sólo tendrá que seleccionar todas las hojas que desee modificar y trabajar sobre algunas de ellas. Los cambios se reflejarán en todas.

1. En este ejercicio cambiaremos y unificaremos el estilo de las tres hojas del libro Ejemplo1 y lo primero que deberemos hacer es seleccionarlas. Haga clic en la pestaña Ventas mensuales, pulse la tecla Mayúsculas y, sin soltarla, haga clic en la hoja Ventas_1T.

2. La palabra Grupo en la cabecera nos indica que hay un grupo de hojas activas. Para comenzar, ajustaremos el formato de los datos. Con un arrastre de su borde, en la cabecera, ajuste el ancho de la columna A de modo que todos los contenidos quepan cómodamente en ella.

La palabra Grupo en la cabecera de la aplicación nos confirma que hemos agrupado las pestañas y que las hojas correspondientes se editarán en conjunto.

3. Seleccione las celdas A1 y B1, despliegue el comando Color de relleno del grupo Fuente en la ficha Inicio y escoja algún color que le agrade. Luego cambie también el color de fuente.

4. Cambie el tamaño de la fuente a 20 y modifique la fuente desde el primer comando del mismo grupo de herramientas.

5. Ahora seleccione las filas 1 y 2, muestre su menú contextual con el botón izquierdo del ratón y escoja la opción Insertar.

6. Ha creado dos filas. Escriba en la fila A1 su nombre o el de su empresa, por ejemplo, y ajuste el estilo de la celda a su gusto. Sepa que no podrá agrupar un conjunto de celdas, puesto que es imposible agrupar celdas de otras hojas y es lo que interpreta la aplicación.

7. Cuando haya terminado, pulse sobre las otras dos pestañas del documento y compruebe el resultado obtenido. Ya arreglamos lo que falta más adelante.

8. Regrese a la hoja Ventas mensuales y, con ayuda da la tecla Control, seleccione también la hoja Ventas_1T con un clic en su pestaña.

9. A continuación seleccione la celda B25 e introduzca la siguiente fórmula: =(B23-B24)*comisiones y pulse la tecla Retorno para que se realice el cálculo.

Al agrupar hojas, los cambios que modifiquemos en la hoja visible se aplican en todas las seleccionadas.

10. Una vez calculado el total mensual para comisiones y descuento, calcularemos en la celda B26 el ingreso real. Introduzca la fórmula que viene a continuación: =B23-B24-B25 y pulse Retorno.

11. Pulse en la pestaña Clientes VIP y compruebe que no se ha efectuado ningún cambio adicional en la misma.

12. Haga clic en la celda Ventas_1T y seleccione la celda B25 para comprobar que la fórmula se ha aplicado correctamente en esta celda.

13. Luego repita el procedimiento en la celda B26 y guarde los cambios para terminar.