• Author / Uploaded
• NadiaYaninaBulacio

• Categories
• Microsoft Excel
• Tabla (base de datos)
• Macro (informática)
• Fórmula
• Informática

Citation preview

Curso de Excel Avanzado por Quique Arranz Como ya sabrás, Microsoft Excel es una herramienta con una increíble capacidad para realizar casi infinitas tareas relacionadas con el cálculo numérico y la gestión de datos. En este curso de Excel avanzado gratis queremos que conozcas todas estas funcionalidades y pueda servirte para ver que, quizás, sólo estás en la punta del iceberg de Excel, y que por debajo hay muchas cosas que no puedes ver. Es te curso es valido para todas las versiones de Excel, desde Excel 2007, Excel 2010 hasta la última versión de Excel. Espero que con este tutorial de Excel Avanzado puedas mejorar rápidamente en el uso de Excel, o al menos, conocer toda la capacidad que tiene. Si bien es cierto que es imposible saber usar todas las funcionalidades de Excel es importante conocer aquellas que son más importantes para nosotros y manejarlas con destreza. Todo lo que puedas hacer por tú parte, además de este curso de Excel avanzado, será ir ganándole terreno a Excel para convertirte en un usuario avanzado de Excel. No dejes pasar y aprovecha a hacer uno de los mejores cursos excel Avanzado totalmente gratis.

Contenido [Ocultar] 









1 Clase 1: Funciones Avanzadas de Excel o

1.1 Fórmulas avanzadas generales

o

1.2 Fórmulas avanzadas de referencia

o

1.3 Rastrear precedentes y dependientes

o

1.4 Evaluar fórmula

o

1.5 Creación de nombres

o

1.6 Opciones de cálculo de Excel

o

1.7 Ejercicios Excel Avanzado

2 Clase 2: Gráficos Avanzados de Excel o

2.1 No hagas chapuzas en tus gráficos

o

2.2 Gráficos interactivos de Excel

o

2.3 Gráficos con varios ejes o gráficos combinados

o

2.4 Crear plantillas para gráficos

3 Clase 3: Excel avanzado – funcionalidades súper útiles o

3.1 Eliminar duplicados

o

3.2 Filtros avanzado de Excel

o

3.3 Validación de datos

4 Clase 4: Excel avanzado – Gestión y análisis de datos o

4.1 Tablas dinámicas

o

4.2 Gráficos dinámicos

o

4.3 Obtener datos externos

o

4.4 Análisis de hipótesis

5 Clase 5: Macros de Excel o

5.1 Grabar una macro



o

5.2 Guardar un libro de Excel con macros

o

5.3 Modificar una macro grabada

6 Clase final del curso de Excel avanzado o

6.1 Tómate un descanso

o

6.2 Comparte en las redes sociales

CLASE 1: FUNCIONES AVANZADAS DE EXCEL Como ya has podido ver en la Mega-Guía de las fórmulas de Excel existen muchas funciones de Excel. Conocer todas la funciones es un ejercicio que lleva años. Yo mismo, que nunca soy buen ejemplo, creo que conozco más del 80% de las funciones pero hay algunas, como las funciones estadísticas, que estoy casi seguro de que no las voy a usar nunca. En este curso gratis de Excel vamos a hacer un repaso de aquellas funciones que sí o sí, tienes que conocer de memoria si quieres ser un usuario avanzado de Excel. No importa el tiempo que te lleve dominarlas pero su uso debería ser para ti tan familiar como escribir. Y no lo digo por decir, simplemente es verdad, escribir funciones en Excel debe ser para ti, que quieres ser un usuario de Excel avanzado, tan fácil como escribir en español sin faltas de ortografía… aunque es normal que de vez en cuanto te surjan dudas y tengas que recurrir a la ayuda de Excel. FÓRMULAS AVANZADAS GENERALES Condicionales Las fórmulas condicionales son de lo más potente que podemos encontrar en Excel para hacer cálculos sencillos. Las funciones que nos parcen más indicadas para este curso de Excel avanzado online son: 

CONTAR.SI.CONJUNTO: nos permite contar celdas de un rango que cumplen unos determinados criterios. Estos criterios los cumplen dichas celdas o cualquier rango de celdas.



SUMAR.SI.CONJUNTO: idem que la anterior pero en vez de contar los resultados, los suma. Como es lógico, esta fórmula de Excel sólo se aplica cuando tengamos valores numéricos que poder sumar.



PROMEDIO.SI.CONJUNTO: idem que el anterior pero haciendo la media de los valores numéricos.

Otras Algunas otras funciones son muy importantes en el día a día con Excel y merece la pena que las tengas muy claras para aprender Excel avanzado. Estas funciones son: 

SUMAPRODUCTO: esta fórmula devuelve como resultado la multiplicación de uno o varios rangos de celdas multiplicadas una a una.



TEXTO: esta fórmula es una de mis favoritas por la versatilidad que aporta. Permite modificar el formato de un número o fecha de manera muy sencilla. Ideal para cuadros de texto o para añadir valores numéricos en el título de un gráfico de manera automática.



FECHA.MES: devuelve una fecha futura o anterior sumando o restando un número de meses a una fecha seleccionada.



DIAS.LAB: devuelve el número de días que hay entre dos fechas.



SUSTITUIR: devuelve una cadena de texto en la que se sustituye uno o varios caracteres.

FÓRMULAS AVANZADAS DE REFERENCIA Las fórmulas de referencia en Excel son las que normalmente se le atascan más a la gente. Algunas son un poco más complejas y otras más sencillas. En general, estas fórmulas de Excel son un pilar básico en cualquier tutorial de Excel avanzado.



BUSCARV: busca un valor en la primera columna de una tabla y devuelve un valor en la misma fila de una columna especificada.



DESREF:



INDICE:



COINCIDIR:



IMPORTARDATOSDINAMICOS

RASTREAR PRECEDENTES Y DEPENDIENTES Para manejarse con las fórmulas hemos previsto que en este tutorial de Excel avanzado esté un breve capítulo sobre rastrear precedentes y dependientes. Para acceder a estas funcionalidades de Excel avanzado deberás ir a la pestaña Fórmulas >> Grupo Auditoría de Fórmulas como está señalado en la siguiente imagen:

Rastrear precedentes Rastrear precedentes es una funcionalidad que permite averiguar todas las celdas que están vinculadas con una fórmula en Excel. Por ejemplo, en la siguiente imagen, con una fórmula muy sencilla que hace la suma de A1 y A3 las flechas que se ven son las que van desde las celdas A1 y A3 hasta la celda seleccionada de la que queremos rastrear los precedentes.

Este es un caso muy sencillo pero esta funcionalidad de Excel avanzado es muy interesante en hojas que tenemos muchos datos y fórmulas y, por desgracia, no están tan ordenados como deberían… yo particularmente la uso mucho cuando tengo que entender como está hecha una hoja de Excel que me haya mandado un compañero. Rastrear dependientes Es similar al concepto anterior que acabos de explicar pero al revés. En vez de identificar las celdas de las que provienen los resultados de una fórmula en este caso sabremos en qué celdas está influyendo una celda en concreto. En la siguiente imagen vemos que la celda A6 influye sobre la celda C5 que contiene una fórmula.

Eliminar flechas

Pero si nos pasamos con el tema rastrear dependientes y precedentes nuestro Excel se convertirá en una locura innavegable así que convendrá eliminar dichas flechas. Para ello haz click en el botón “Quitar flechas” o si lo prefieres, selecciona del desplegable las flechas que quieras quitar.

EVALUAR FÓRMULA Este pequeña funcionalidad es muy poco usada en Excel pero los usuarios de Excel avanzado no le tienen miedo, sino todo lo contrario, es una herramienta muy apreciada que te permite: 

Encontrar errores en fórmulas complejas



Entender los resultados parciales en fórmulas anidadas



Ir viendo los resultados dentro de una fórmula poco a poco

Para usar el “evaluador” de fórmulas en Excel deberemos colocarnos justo encima de la celda que queremos evaluar y hacer click en el botón “Evaluar fórmula” dentro del grupo “Auditoría de fórmulas”. Una vez hecho click nos aparecerá una ventana como la siguiente:

Esta herramienta requiere un poco de práctica para poder usarla pero no hay nada en este tutorial avanzado de Excel que nos queramos guardar así que te lo vamos a contar. Las diferentes funcionalidades son: 

Evaluar: haciendo click sobre este botón verás el resultado de la celda o función subrayadas. Es muy útil para ir descubriendo los valores que hay en cada una de las referencias.



Paso a paso para entrar: Si una de las celdas referenciadas tiene a su vez una serie de fórmulas este botón nos permitirá ver las fórmulas que hay en dicha fórmula e ir evaluándolas.

CREACIÓN DE NOMBRES Cuando usas un montón de fórmulas (o incluso cuando usas pocas) puede resultar muy conveniente utilizar los “nombres” de Exce para referirse a un rango. Este rango puede ser de una sóla celda o de un conjunto de celdas. Por ejemplo, si multiplicamos una serie de valores por el IVA podemos hacer: =A1*(1,21) o podremos escribir también =A1*(1 + IVA) donde IVA es un nombre de Excel en el que hemos guardado el valor de una celda o conjunto de celdas.

Si quieres saber más sobre los “nombres” de Excel te recomiendo que leas el siguiente artículo sobre el tema: Crear nombres en Excel OPCIONES DE CÁLCULO DE EXCEL Hemos considerado que en este curso de Excel avanzado online no debía faltar una breve explicación sobre las opciones de cálculo de Excel. En Microsoft Excel podemos modificar cuando hacemos ciertos cálculos. Las opciones de cálculo son: 

Automático: todos los cálculos de las hojas de Excel (fórmulas, tablas de datos, formato condicional…) se realizarán siempre que se modifique una celda de Excel. Esto no aplicará para las tablas dinámicas que a no ser que se modifiquen o se refresquen de manera manual no se actualizarán.



Automático excepto para tablas de datos: lo anteriormente dicho excepto para tablas de datos. Las tablas de datos son un método avanzado de análisis de hipótesis que explicamos en el punto 4 de este curso de Excel avanzado.



Manual: todos las fórmulas, tablas, y otros elementos que deban calcularse sólo se calcularán cuando manualmente se haga click en esta opción.

La modificación del método de cálculo puede ser especialmente útil en las siguientes circunstancias: 

Cuando exista un gran número de fórmulas normales pero complejas o varias fórmulas matriciales que requieran de un periodo de cálculo amplio (con más de 1 décima de segundo cualquier usuario de Excel se desesperará).



Cuando existan grandes tablas de datos que ralenticen el cálculo del resto de la hoja de cálculo.



Y en general, cuando exista una circunstancia que haga razonable cambiar el método de cálculo de manual a automático.

Para acceder a las opciones del método de cálculo iremos a la pestaña Fórmulas >> Opciones del cálculo (normalmente se encuentra a a la derecha de la pestaña en la esquina superior derecha). EJERCICIOS EXCEL AVANZADO En el aprendizaje de Excel nos encontramos con un problema. La falta de ejercicios para practicar. Si necesitas ejercicios de Excel avanzado resueltos te recomiendo que visites nuestra sección de Ejercicios donde encontrarás un montón de ejercicios avanzados de Excel para que te puedas convertir en un experto de Excel sin ningún tipo de problema. Ejercicios Excel avanzado CLASE 2: GRÁFICOS AVANZADOS DE EXCEL Como ya sabrás a estas alturas de tú vida con Excel hay casi infinitas posibilidades con los gráficos de Excel. Es más, nosotros tenemos una sección de Excel en la que hablamos de gráficos avanzados pero el mejor blog en castellano que he visto es uno de Jorge Dunkelman que es sobre exclusivamente sobre gráficos aunque todo hay que decirlo, está un poco desactualizado 🙂 En esta clase de nuestro curso de Excel Avanzado gratis vamos a intentar hacer una explicación más o menos profunda sobre gráficos avanzados y algunas técnicas que te ayudarán a convertirte en un maestro de los gráficos. NO HAGAS CHAPUZAS EN TUS GRÁFICOS La diferencia entre hacer un buen gráfico y un mal gráfico lo es TODO. Puede suponer que la cara de la persona que lo mire sea esta:

Y desde luego estoy seguro de que no es lo que quieres conseguir. La diferencia entre hacer un gráfico mediocre y un gráfico interesante que llame la atención es cuestión de unos pocos minutos y algo de práctica. Algunas cosas que debes tener en cuenta para hacer un buen gráfico en Excel son: 

No hagas un gráfico que no estuvieras dispuesto a enseñarle a tú jefe.



No hagas un gráfico con mil colores. Es mejor ser sobrio en casi todos los aspectos de tú vida, y en un gráfico también.



No dejes que el título del gráfico haya que descifrarlo. Pon un título que describa el gráfico. Y si tienes que poner un subtítulo ponlo también.



No hagas un gráfico que tengas que explicar. Pon unidades, nombre a los ejes y nombre a las series de datos.



Quita las linea de cuadrícula del fondo. En la mayoría de los casos no sirven para nada más que para hacer que tú gráfico sea muy cutre.

Una imagen de un gráfico antes y después de darle una manita de “chapa y pintura” puede hacerte reflexionar pero antes quiero mostrarte lo que no son buenos gráficos. A veces poner en contexto a la gente es una buena manera de ayudarles a darse cuenta de que lo que están haciendo hasta ahora no es lo mejor que pueden hacer.

En la siguiente imagen he preparado dos gráficos. Primero he preparado el gráfico como a mi me gusta y después he usado un gráfico predefinido de Microsoft Excel. ¿A qué no sabes cuál es el que es el predefinido por Excel? ¿El negro o el blanco? Si quieres, puedes contestar en los comentarios 🙂

GRÁFICOS INTERACTIVOS DE EXCEL

Una de las cosas que más me gustan de Excel es poder hacer que un gráfico pueda cambiar con un selector. Es decir, elegir qué todas se enseñan… pero no sólo con un selector sino con una barra de “scroll” como la de las hojas de Excel o los navegadores de Internet. Interesante verdad… para ello hay que tener un montón de conceptos en la cabeza y es un apartado que puede resultar bastante complejo. Este apartado de Excel avanzado daría para un montón de discusiones así que te animo a que le eches un vistazo a este par de artículos que creo que te pueden interesar un montón: 

Gráficos con selector



Gráficos con barra de desplazamiento

Si los has leído (aunque sea por encima) te habrás quedado con ganas de más, seguramente de mucho más así que te animo a que le eches un vistazo a la sección de gráficos y veas todos los que te interesan. GRÁFICOS CON VARIOS EJES O GRÁFICOS COMBINADOS Cuando uno aprende a hacer este tipo de gráficos se convierte en una auténtica máquina de Excel. Dominar los gráficos con dos ejes verticales es una tarea que antes o después tendrás que aprender a hacer en este curso Excel avanzado gratis si quieres convertirte en un verdadero experto de Excel. Imagina que tienes dos series de datos diferentes. Una que es sobre los litros de cerveza que se bebe en un país y otra que es el % de cerveza que se consume respecto a otras bebidas alcohólicas. El resultado, si lo ponemos en una gráfica con un mismo eje sería el siguiente:

Como puedes ver, en el gráfico anterior, la barra de % de cerveza vs. otros no se ve en el gráfico. En cambio, si ponemos dicha serie en otro eje (seleccionamos una serie, hacemos click con el botón derecho y seleccionamos “Cambiar tipo de gráficos de series…”) aparecerá la siguiente ventana:

Donde en la esquina inferir derecha veremos un recuadro donde podemos señalar qué series queremos que se vayan al eje secundario. Si elegimos la serie naranja de % cerveza vs. otros y aplicamos un poco de formato para mejorar la apariencia del gráfico el resultado será mucho más legible e interesante.

CREAR PLANTILLAS PARA GRÁFICOS

Pero además, si siempre haces los mismos gráficos y retoques, no hace falta que estés todo el día haciéndolos. Excel te da la posibilidad de que guardes un modelo de gráfico que hayas modificado tú como plantilla para que después puedas reutilizarlo siempre que quieras, en el mismo libro en el que estás trabajando o posteriormente en otro libro. Personalmente yo suelo crear un montón de plantillas. Después me vienen muy bien como punto de partida para no tener que modificar todo el gráfico. Para crear una plantilla de un gráfico en Excel a partir de un gráfico ya creado deberemos hacer click con el botón derecho sobre el gráfico y seleccionar la opción de “Guardar como plantilla”.

Una vez hecho esto se abrirá una ventana para elegir el nombre del gráfico y guardarlo. Si queremos reutilizar dicha plantilla crearemos un gráfico cualquiera y después, modificaremos el tipo de gráfico. De todos los tipos de gráficos seleccionaremos la carpeta que pone “Plantillas” y seleccionaremos el gráfico que hayamos guardado previamente.

CLASE 3: EXCEL AVANZADO – FUNCIONALIDADES SÚPER ÚTILES Una de las clases más importantes en un curso excel avanzado son las funcionalidades que puedes descubrir. Quizás ya supieras que existen pero nunca te habías atrevido a tocarlas. En este tutorial de Excel avanzado queremos que aprendas todo lo que puedas para sacarle el máximo partido a Excel. Ya se que son muchas cosas las que estamos explicando en este mega tutorial de Excel avanzado pero no te preocupes, no tienes porque aprender a usarlas todas hoy. Simplemente saber que existen. ELIMINAR DUPLICADOS

Hace tiempo grabamos un vídeo que explica perfectamente esta funcionalidad… te recomiendo que le eches un vistazo. Apenas dura un minuto y creo que es una muy buena manera de aprender también a usar Excel. FILTROS AVANZADO DE EXCEL Lo más probable es que a estas alturas del mundo de Excel ya sepas moverte con total soltura con los filtros de datos. Probablemente hayas pasado muchas horas filtrando datos pero hay un tipo de filtro que según nuestro parecer no puede dejar de mencionarse en un curso avanzado de Excel. Inicialmente los filtros avanzados pueden resultar algo complejos pero lee con atención y verás como no es para tanto. ¿Dónde está el filtro avanzado? El filtro avanzado se encuentra en la pestaña Datos dentro del grupo Ordenar y Filtrar como puedes ver en la siguiente imagen:

¿Qué necesito para hacer un filtro avanzado? Para hacer un filtro avanzado en Excel necesitas tener una tabla con datos y un rango en el que tengas las mismas cabeceras que en la tabla inicial y los criterios por los que quieres filtrar en la tabla. Puedes organizarlo como te enseñamos a continuación:

Como puedes ver en la imagen anterior, tenemos una tabla a la izquierda y a la derecha las cabeceras con el filtro preparado para filtrar por la clase “A”. ¿Cuál es el resultado del filtro avanzado? El resultado de un filtro avanzado es un filtro pero que puede tener las siguientes características: 

Se puede filtrar con más de un filtro a la vez. Por ejemplo, podríamos poner Clase A y Edad 11.



Las celdas de los criterios pueden ir referenciadas a fórmulas de Excel, de ahí su potencia.



El filtro que se realice se puede hacer sobre la tabla original o en otro sitio. Este otro sitio puede ser en la misma hoja en la que está la tabla de datos original o en otra tabla nueva.

Si quieres aprender más sobre los filtros avanzados te recomiendo que leas el artículo: ¿Cómo se usan los filtros avanzados? VALIDACIÓN DE DATOS ¿Nunca te has preguntado cómo se hace una lista desplegable en Excel? ¿O cómo se bloquea una lista para que sólo puedas meter un rango de números en concreto? Pues bien, todo esto y mucho más se hace con la validación de datos. Esta funcionalidad se encuentre en la cinta de opciones en la pestaña de Datos en el grupo “Herramientas de datos”. Lo que se puede hacer con esta funcionalidad avanzada de Excel es una auténtica pasada y daría para hablar y mucho. ¿Qué se puede hacer con validación de datos? Las opciones son casi infinitas en cuanto a limitar valores en celdas, el límite es la imaginación pero hay una serie de opciones que vienen predeterminadas: 

Crear una lista de valores desplegable a partir de un rango de celdas o incluyendo los valores manualmente.



Permitir sólo números enteros.



Permitir sólo números decimales



Permitir fechas



Permitir hora



Permitir una cadena de texto con una longitud máxima



Otros

Como puedes ver, una validación de datos es muy útil en formularios para evitar que el usuario meta datos en las celdas sin tener cuidado con el formato. Para aprender más sobre la validación de datos te recomiendo que leas este super artículo sobre el tema: Validación de datos CLASE 4: EXCEL AVANZADO – GESTIÓN Y ANÁLISIS DE DATOS Esta es, sin duda, una de mis clases favoritas de Excel. Es con la que más suelo disfrutar pues todos los alumnos de un curso avanzado de Excel, al terminar esta clase, suelen tener una cara parecida a la siguiente:

Y espero que tú también acabes así… “flipando”. TABLAS DINÁMICAS De las tablas dinámicas se ha hablado mucho, muchísimo, pero muy poca gente se mueve con la soltura que debería para ser un experto en el tema. Como breve definición te diré: las tablas dinámicas son la manera más rápida de organizar información sin tener que usar ni una sola función, simplemente organizando los datos de una tabla de manera súper visual. Para hacer una tabla dinámica partiremos de una tabla con datos como la que teníamos en el apartado de filtrar datos, la seleccionaremos e iremos a la Pestaña Insertar. A la izquierda del todo encontremos un botón que pone “Tabla dinámica”. Haciendo click en este botón podremos insertar una tabla dinámica de manera rápida y sencilla en Excel. Al hacerlo aparecerá un menú como el siguiente:

Y mediante el sistema de “arrastrar y soltar” podremos ir organizando nuestra tabla dinámica. Nosotros, por ejemplo, hemos hecho lo siguiente:

Donde como puedes ver, en el apartado FILAS hemos puesto la Clase y el Nombre (cabeceras de nuestra tabla) para que nos lo ordene de esta manera. El resultado de esta tabla dinámica es:

Una manera muy rápida de ordenar por clases y después según los nombres. Aunque también lo podríamos haber hecho según las edades. Y como verás este cambio se tarda en hacer menos de 15 segundos.

Y rápidamente podemos ver, según las edades de una serie de personas, en qué clase se encuentran así como saber cuántos hay de cada uno de los tipos y subtipos. En fin, que las tablas dinámicas, una vez que aprendas a usarlas son de lo mejorcito que se ha inventado hasta ahora en el mundo del análisis de datos. Si quieres aprender más sobre las tablas dinámicas no pases por alto el Mega tutorial de tablas dinámicas. GRÁFICOS DINÁMICOS Otra de las grandes aportaciones de las tablas es poder hacer gráficos dinámicos. ¿Qué es esto? Es muy sencillo, es un gráfico que está ligado a los datos de una tabla dinámica y que nos permite ver de forma gráfica y muy rápidamente, un conjunto de datos. El único problema de los gráficos dinámicos es además su virtud y es que se modifican en cuanto se modifica la tabla dinámica. La parte buena de este tipo de gráfico es que puedes usar las diferentes opciones que incluye para filtrar el propio gráfico. Esto influirá en la tabla dinámica pero, si no te importa, es una muy buena opción para trabajar con datos. En la siguiente imagen puede ver un gráfico dinámico con sus diferentes elementos. La parte importante se encuentra en la zona inferior del gráfico donde encontraremos los filtros de la tabla dinámica y podremos ampliar o disminuir la información mostrada.

OBTENER DATOS EXTERNOS En este tutorial avanzado de Excel creemos que es fundamental comentar el tema de la obtención de datos de fuentes externas a Excel para poder trabajar con ellos. Las típicas fuentes para poder trabajar con ellos son: 

Desde Accesss



Desde un web



Desde un archivo de texto



Desde otras fuentes: …

Además, si trabajas con datos de muchas otras fuentes puedes hacer extracciones de datos mediante las consultas. Pero esto consideramos que está fuera de un curso de Excel avanzado online gratis normal pues es muy específico y requeriría de mucho tiempo para poder explicarlo en condiciones. Lo bueno de poder obtener datos de una fuente externa es que no necesitas trabajar exclusivamente en dicha fuente externa (digamos en Access) sino que puedes seleccionar una o varias partes de los datos y trabajar con ellos en Excel para hacer un reporte, un análisis de datos o cualquiera de las cosas que hemos visto en las clases anteriores. Otra de las ventajas que tiene es que pueden actualizarse los datos de manera automática al abrirse el libro de Excel o, teniéndolo abierto, refrescarse dado un número de segundos que especifiquemos. Por ejemplo, una aplicación que yo mismo me he hecho y que me resulta muy útil es la de tener un enlace a una página web con las cotizaciones del Ibex 35 de la bolsa española para poder controlar las acciones que tengo. Al menos, una vez al día lo abro y lo refresco… de esta forma me da un reporte personalizado de mis acciones, de las acciones a las que sigo y de cuáles están despuntando en el mercado de valores. ANÁLISIS DE HIPÓTESIS Las herramientas de análisis y previsión son una de las funcionalidades que menos uso tienen en Excel pero para las personas que se pasan el día haciendo modelización de datos son fundamentales una vez que las conocen y trabajan con soltura con las mismas. En este curso de Excel avanzado no pretendemos explicar todas con detalle pero sí que queremos explicaros su principal funcionamiento. Para acceder a estas funcionalidades ve a la pestaña Datos al grupo Previsión como puedes ver en la siguiente imagen:

Previsión Dada una serie de datos y una línea temporal, esta funcionalidad te permite hacer una predicción sobre la tendencia que seguirán los datos. Por ejemplo, podríamos obtener un gráfico como el siguiente:

y también podemos cambiar a gráfico de columnas

Una vez que le demos al botón crear y hayamos modificado las diferentes opciones con las que nos prové Microsoft Excel se generará una hoja de Excel con un gráfico y todos los datos de la serie que hayamos creado como puede verse en la siguiente imagen:

Esto nos permitirá jugar con los datos y de esta manera podremos tomar nuestras propias asunciones con las previsiones. Administrador de Escenarios

El administrador de escenarios es una herramienta muy potente cuando tenemos que tomar diferentes hipótesis y tenemos diferentes escenarios para estas hipótesis. Supongamos que estamos montando un negocio y queremos tener tres escenarios diferentes con las mismas hipótesis con valores distintos. Para ello usaremos el administrador de Escenarios donde crearemos cada uno de nuestros escenarios y daremos valores a las diferentes hipótesis (que estarán referenciadas a diferentes celdas). Por ejemplo, podemos tener: 

Escenario Negativo



Escenario Normal



Escenario Positivo

Y las hipótesis podrían ser: 

Crecimiento esperado



Precio del producto



Número de ventas en el primer mes de lanzamiento

Como puedes ver, la combinación de las diferentes hipótesis puede dar muy diferentes resultados en función de los valores y para ello creamos los escenarios como se puede ver en la siguiente imagen:

Utilizando el botón “Resumen” podremos ver una tabla en la que se resumen los diferentes resultados según cada uno de los valores de las hipótesis que se han utilizado en los escenarios. Buscar objetivo Esta funcionalidad es poco usada pero muy útil para no tener que hacer tanteos manuales. Nos sirve para identificar automáticamente un valor complejo sin tener que hacer aproximaciones. Por ejemplo, si queremos alcanzar un volumen determinado de ingresos un año en nuestro negocio pero no sabemos cuántas horas deberemos dedicar entonces usaremos la herramienta buscar objetivo. Por ejemplo, si queremos alcanzar un volumen de ingresos de 100.000 € al año y conocemos el precio que queremos poner a cada hora lo único que nos faltaría por saber es el número de horas que tendremos que trabajar. Para ello podremos utilizar dicha herramienta que nos hará el cálculo automáticamente. Tabla de datos

Las tablas de datos, son quizás, el elemento más complejo de Excel. Es similar al uso de los escenarios pero se calculan en una tabla de Excel y su cálculo es constante. Lo bueno de las tablas de datos es que puedes tener una o varias variables que modifican un resultado final y Excel constantemente actualiza los diferentes resultados de dicha fórmula para el conjunto de variables definidas. Si quieres aprender más sobre el uso de Tablas de datos te recomiendo que le eches un vistazo a este vídeo que es muy fácil de seguir aunque un poco largo. En el minuto 9 empieza a explicar cosas muy interesantes sobre tablas de datos. CLASE 5: MACROS DE EXCEL Esta clase de Excel en el curso de Excel avanzado es opcional. Esto es muy importante que lo sepas. Puedes hacer infinidad de cosas en Excel sin tocar las macros pero, si te interesa aprender un poco, sigue leyendo porque esto te dará un valor diferencial en tú conocimiento de Excel además de ahorrarte miles de horas de trabajo en el futuro si aprendes a programar tus propias macros en Excel. GRABAR UNA MACRO Todos los que hemos aprendido a programar macros en VBA siempre hemos empezado por el mismo paso: grabar una macro. Después de grabarla la hemos asociado a un botón y ahí la hemos dejado. Este paso inicial es el comienzo de un gran mundo. ¿Qué es grabar una macro? Grabar una macro en Excel no es nada más que grabar todos los pasos que vamos haciendo en Excel para después poder repetirlos a través de una acción automática -como hacer click en un botón-. Como ejemplo, vamos a copiar y pegar un rango de celdas en otra hoja. Después, añadiremos un botón para que esta opción la podamos repetir tantas veces como queramos. Para grabar una macro deberemos hacer click en el botón que se encuentra en la esquina inferior izquierda de la pantalla de Excel como podemos ver en un recuadro en rojo en la siguiente imagen.

Después haremos todas las acciones que queramos en Excel y finalizaremos la grabación de la macro haciendo click en el mismo botón pero ahora tendrá forma de cuadrado como vemos en la siguiente imagen:

Como previamente habremos asignado un nombre a la macro no nos será difícil asignarla a un botón. Para ello nos iremos a la pestaña Desarrollador (mostrar pestaña desarrollador) e insertaremos un botón en nuestra hoja haciendo click en el botón marcado en la imagen:

Después, asignaremos al botón, una vez dibujado en nuestra hoja, la macro que queremos que realice.

Y ya está, ya tenemos una macro asociada a nuestro libro de Excel. GUARDAR UN LIBRO DE EXCEL CON MACROS Por defecto, los libros de Excel no permiten guardar las macros así que tendremos que modificar el tipo de archivo antes de guardarlo. Para ello haremos click en el botón “Guardar como” y en el tipo de archivo, justo debajo del nombre, abriremos el desplegable para seleccionar el tipo de archivo como “Libro de Excel habilitado para macros” como puede verse en la siguiente imagen:

MODIFICAR UNA MACRO GRABADA

El siguientes paso que necesitarás dar en tú aprendizaje de Excel será el de aprender a modificar las macros que hayas grabado. Para ello necesitarás adquirir un buen “puñado” de conocimientos previamente. Las macros me dan miedo Si las macros te dan miedo ya es hora de que se lo pierdas. Hay gente que tarda una semana en entender más o menos bien todas las funcionalidades de las macros, todo depende del tiempo que le dediques o le puedas dedicar… eso sí, te garantizo que nadie que se haya puesto en serio a aprender a tardado más de una semana en crear sus propias macros así que ¡ánimo!. Ponte las pilas con las macros Como te decía, las macros no son difíciles pero requieren de un plan bien organizado y una persona que te vaya explicando poco a poco todo lo que necesita saber y no enredarte en cuestiones que te harán perder el tiempo y te despistarán. Por eso hemos creado el mejor curso gratuito de Excel para macros que puedes ver en esta web. Pincha en el siguiente enlace y empieza a aprender sobre macros en Excel poco a poco, a tú ritmo, sin pausa pero sin prisa. CURSO GRATIS DE MACROS DE EXCEL CLASE FINAL DEL CURSO DE EXCEL AVANZADO Espero que este curso de Excel avanzado te haya servido y, de verdad, hayas aumentado tú conocimiento sobre esta herramienta. Estoy seguro de que si lo has leído entero habrás obtenido un montón de ideas para que puedas mejorar en tú uso diario de Microsoft Excel.