Macros

Macros en Excel

¿Qué es un macro en Excel?

Una macro es un pedacito de código programado en VBA, el lenguaje de programación de Microsoft. Una macro en Excel es la automatización de una tarea sencilla o compleja en Excel. Una macro muy típica es aquella que nos permite copiar información de una hoja a otra de Excel a través de un botón para crear un registro (por ejemplo). Básicamente, el pedacito de código previamente comentado puede ser cualquier operación que puedas hacer en Excel mediante botones (normalmente) u otros elementos. Aunque también puedes hacer que sean macros que “salten” automáticamente. Por ejemplo, cuando seleccionas una celda o cambia un valor de una fórmula. Cosas como cambiar formatos, eliminar columnas, añadir filas, crear fórmulas, pegar imágenes, ordenar datos, insertar datos… cualquier cosa.

Este pedacito de código, en VBA siempre tiene la siguiente forma (manera de escribirse):

Entre el Sub y el End Sub es donde escribiremos nuestro código por ejemplo como en este caso:

En la imagen anterior vemos dos líneas en verde, son los comentarios que van precedidos, para que VBA los entienda como tales, de una comilla simple.

En negro está escrita una línea de código que hará saltar el mensaje  <<Subscríbete a este blog, es gratis.>> cada vez que se ejecute una macro.

Para ejecutar la macro presionamos la tecla F5 de nuestro teclado mientras el cursor está situado en alguna línea de la macro. El resultado será el siguiente:

Esto, por ejemplo, nos podría dar otro tipo de mensaje que fuera útil al usuario del programa Excel que hemos creado, o a nosotros mismos.

Merece la pena conocer el atributo Public/Private de una Subrutina (macro). Antes de la palabra Sub de nuestra macro podemos poner la palabra Public o Private aunque, por defecto, VBA entiende que toda macro es del tipo Public con lo que ello conlleva y que te explicaré a continuación.

La definición de cada una de ellas sería:

Public: puede accederse a ella desde otro módulo y es visible desde la ventana de Desarrollador.

Private: es una macro que no puede llamarse desde otro módulo y tampoco desde la ventana de desarrollador.

Normalmente añadiremos el atributo Private cuando tengamos muchas macros y no nos apoyemos en algunas para ejecutar nuestro módulo pero que no son necesarias que sean visibles.

¿Qué cosas puedo hacer con una macro en excel?

En Excel podemos hacer macros para casi todo lo que se nos ocurra. La mayoría de las personas crean una macro en Excel para automatizar algunas tareas sencillas (o complejas) aunque su potencia es mucho mayor. Algunos ejemplos que puedes ver en esta web son:

• Copiar un rango de celdas de una hoja a otra
• Copiar datos de un libro de Excel a otro
• Copiar un gráfico de Excel a PowerPoint
• Crear una función de Excel que no existe

Tablas dinámicas

Tablas dinámicas en Excel

¿Qué es una tabla dinámica de Excel?

Para saber lo que es una tabla dinámica en excel , lo primero que hay que tener preparado en Excel será nuestra tabla de datos. Siempre habremos de partir de una tabla de datos donde la información se organiza por filas y las columnas son los diferentes atributos o características de nuestra información para trabajar con las tablas dinámicas.

En la siguiente imagen se puede ver la tabla de datos que usaremos en el modelo:

En la imagen vemos una serie de datos de ventas de comerciales en diferentes meses y de diferentes productos.

Si quisiéramos saber cómo organizar esta información por vendedor y producto podríamos hacer una serie de filtros, o cálculos con SUMAR.SI.CONJUNTO. Pero es mucho más rápido y fácil (cuando la información es masiva y hay muchos condicionantes) crear una tabla dinámica.

En el siguiente punto, encontrarás toda la información sobre cómo hacer tablas dinámicas.

Cómo crear una tabla dinámica en Excel

Para crear las tablas dinámicas en excel tienes que seguir los siguientes pasos:

Primero, en Excel hay que seleccionar toda la información de nuestros datos. Para ello recomiendo convertir nuestro rango donde encontramos nuestra información a una tabla de datos. ¿Cómo? Muy sencillo. Selecciona el rango y ve a Ventana Insertar >> Grupo Tablas >> Tabla

Una vez que hemos hecho esto la información de nuestra tabla quedará de la siguiente manera:

Esto nos permitirá, que al hacer crecer la información de nuestra tabla en número de filas, al refrescar las tablas dinámicas, todo el rango sea cogido por la tabla dinámica en cuestión.

Pero ahora sí, para crear una tabla dinámica en excel seleccionamos nuestra recién creada tabla y hacemos: Ventana Insertar >> Grupo Tablas >> Tabla Dinámica

Al hacer click en tabla dinámica aparecerá una ventana como la siguiente:

En esta pestaña vemos que el rango/tabla que ha seleccionado. En la siguiente parte nos permitirá elegir donde situaremos nuestra tabla dinámica. En este caso vamos a permitir que cree una hoja nueva y nos coloque la tabla dinámica ahí. Haciendo click en OK lo que veremos será lo siguiente: una nueva hoja con el espacio creado para una tabla dinámica genérica y a la derecha un display para ordenar la información de nuestra tabla dinámica.

Organizar una tabla dinámica en Excel

En la parte de la derecha vemos que en el recuadro superior se han almacenado todos los títulos de columna de nuestra tabla. Eligiendo el que queramos podremos arrastrarlo a una de las cajitas de abajo.

En la tabla dinámica de Excel ¿Qué son las cajitas de abajo? 

• Filtros de reporte: nos permitirá filtrar la tabla entera seleccionando uno o varios elementos de la lista del filtro que hayamos aplicado.
• Columnas: nos permitirá organizar nuestra información por columnas (podremos seleccionar uno o varios elementos de la colección)
• Filas: nos permite organizar nuestra información por filas (podremos seleccionar uno o varios elementos de la colección)
• Valores: serán los valores de cálculo. Podremos visualizar los valores como suma, máximo, media, contar valores…

En la siguiente imagen vemos los filtros que hemos aplicado nosotros:

Hemos colocado nuestras columnas para conseguir el siguiente efecto:

• Valores: Suma de las ventas (precio) conseguido según los filtros seleccionados.
• Filtro de reporte: Mes. De esta manera podremos seleccionar el mes del que queremos conocer los datos de ventas.
• Etiquetas de filas:  hemos puesto las etiquetas de los vendedores para poder ver lo que han vendido en el mes seleccionado.
• Etiquetas de columnas: veremos los productos. De esta manera se desglosará para cada vendedor los productos que ha vendido y la suma de su precio.

Esto, explicado así, es un poco abstracto, lo mejor será ver cómo queda la tabla dinámica Excel una vez que hemos hecho las diferentes selecciones:

En  la imagen anterior vemos como la información de ventas queda organizada para el mes de Enero según los vendedores y los diferentes productos. Además de esto, veremos las columnas de totales de ventas por productos y por vendedores (fila inferior y columna derecha respectivamente).

Como puedes ver, crear tablas dinámicas en Excel es mucho más sencillo de lo que habías pensado nunca y en cuanto le cojas el truco podrás ir a toda velocidad sacando interesantes conclusiones de tus análisis.

Por último, ¿cómo cambio el tipo de valores mostrados? ¿y si me viniera mejor ver cómo se reparten porcentualmente las ventas? Pues es muy sencillo, tan sólo tendremos que seguir los siguientes pasos:

• Hacemos clic sobre la flechita negra en el organizador de tablas dinámicas sobre los valores que queremos cambiar (en nuestro ejemplo el valor suma de precio).
• Elegimos la opción de “Configuración de campo de valor”.
• Elegimos la pestaña de “Mostrar valores como”.
• Del desplegable de “Mostrar valores como” elegimos “% de Gran Total”.

De esta manera el resultado obtenido será:

Función BUSCARV

Función BUSCARV en Excel

Use BUSCARV, una de las funciones de buscar y de referencia, cuando necesite buscar elementos de una tabla o un rango por fila. Por ejemplo, buscar un precio de un repuesto de automoción por el número de pieza.

En su forma más simple, la función BUSCARV indica lo siguiente:

=BUSCARV(Valor que desea buscar, rango en el que quiere buscar el valor, el número de columna en el rango que contiene el valor devuelto, Coincidencia exacta o Coincidencia aproximada indicado como 0/FALSO o 1/VERDADERO).

Hay cuatro partes de la información que necesita para crear la sintaxis de BUSCARV:

1. El valor que desea buscar, también conocido como el valor de búsqueda.
2. El rango donde se encuentra el valor de búsqueda. Recuerde que el valor de búsqueda debe estar siempre en la primera columna del rango para que BUSCARV funcione correctamente. Por ejemplo, si el valor de la búsqueda está en la celda C2, su rango debería empezar con C.
3. El número de columna del rango que contiene el valor devuelto. Por ejemplo, si especifica B2: D11 como el rango, B se debe contar como la primera columna, C como la segunda y así sucesivamente.
4. Opcionalmente, puede especificar VERDADERO si desea una coincidencia aproximada o FALSO si desea una coincidencia exacta del valor devuelto. Si no especifica nada, el valor predeterminado siempre será VERDADERO o la coincidencia aproximada.

Ahora coloque todas las respuestas anteriores de la siguiente forma:

= BUSCAR V(valor de búsqueda, rango que contiene el valor de búsqueda, el número de columna del rango que contiene el valor devuelto, opcionalmente especificar VERDADERO para una coincidencia aproximada o FALSO para una coincidencia exacta).

Estos son algunos ejemplos de BUSCARV:

Ejemplo 1

Ejemplo 2

Ejemplo 3

Ejemplo 4

Ejemplo 5

Fórmulas y funciones

Fórmulas y funciones en Excel

Para introducir cualquier fórmula o función siempre debemos comenzar por el signo = de manera que Excel pueda identificar que los datos introducidos no son textos o datos numéricos.

Fórmula =(c5+f6+b1+a3)^(1/2)

función= Raiz(c5+f6+b1+a3)

Las funciones son herramientas especiales que efectúan cálculos de diversa complejidad en un solo paso. Excel contiene funciones especializadas en cálculos matemáticos, financieros, funciones lógicas, etc.
Las fórmulas en Excel son expresiones que se utilizan para realizar cálculos o procesamiento de valores, produciendo un nuevo valor que será asignado a la celda en la cual se introduce dicha fórmula. En una fórmula, por lo general,   intervienen valores que se encuentran en una o más celdas de un libro de trabajo.

La utilización de las funciones, además de simplificar el desarrollo de modelos sobre hojas de cálculo, permite efectuar dichos cálculos sin conocer el procedimiento matemático, por ejemplo, podemos calcular el VAN (Valor Actual Neto) de una inversión aunque no recordemos la fórmula, simplemente utilizando la función correspondiente.

Elementos de las funciones en Excel:

Nombre:	es un término descriptivo abreviado, tal como SUMA, PROMEDIO, VNA, etc.
Argumentos:	son los datos que necesita la función para operar correctamente. Por ejemplo, la función SUMA tendrá como argumentos el rango de valores a sumar, en la función que calcula el VAN de una inversión, los argumentos serán el tipo de interés o coste de capital, así como el rango de los rendimientos obtenidos. Los argumentos se sitúan entre paréntesis y van separados por "punto y coma"(;).

Las funciones pueden introducirse

1. Escribiéndola directamente sobre la celda (si conocemos su sintaxis y los argumentos a ingresar)
2. Desde la barra de fórmulas, presionando f_x para insertar función
3. Desde la ficha Fórmulas en el fragmento Biblioteca de funciones, presionando el botón Insertar función.

Las indicaciones para escribir una fórmula son:

• Siempre comience a escribir con un signo de igual delante.
• En una fórmula puede haber uno o más operadores (aritméticos, de comparación, etc.).
• Toda fórmula necesita al menos dos valores combinados con operadores, para realizar una comparación y en base a eso realizar la comparación.
• Todos los valores de texto que deba devolver una fórmula como resultado deberán ir entre comillas.
• Los nombres de las funciones nunca llevan acentuación ni espacios.
• El nombre de la función puede estar escrito en mayúsculas o minúsculas, indistintamente.
• Los argumentos o valores que contiene una función siempre van entre paréntesis.
• Los argumentos se deberán separar con punto y coma.

La sintaxis de las fórmulas es:

=nombre_de_la_formula(argumento₁;argumento₂;argumento_n)

Referencias

Referencias en Excel

¿Qué es una referencia?. Una referencia a una celda podemos definirla como una llamada que hacemos al contenido de una celda estando situado en otra.

Todas las celdas de Excel vienen identificadas por la columna a la que pertenecen seguida de la fila en la que estan, con lo cual la primera celda de cualquier hoja de cálculo es la celda A1 y la última será la celda IV65536.

Tambien tenemos la posibilidad de trabajar con celdas que no se encuentran dentro de la misma hoja, para ello tenemos que poner el nombre de la hoja una exclamación y el nombre de la celda por ejemplo hoja1!A1.

Por último podemos tambien hacer referencia a una celda que esta en otra hoja que a su vez se encuentra en otro libro, a esto son lo que se le denomina referencias tridimensionales o 3D, para esto tenemos que poner el nombre del libro entre corchetes a continuación el nombre de la hoja una exclamación y el nombre de la celda, por ejemplo [libro.xls]hoja1!A1.

Estas son las tres maneras que tenemos de hacer referencia a una celda. Pero aparte de esto podemos decir que hay tres tipos:

• Referencias Relativas (Son aquellas que varián si cambia su posición)
• Referencias Absolutas (Son aquellas que se mantienen fijas independientemente de su posición)
• Referencias Mixtas o híbridas (Son aquellas en las que se mantiene fija o bien la columna o bien la fila)

Ejemplos de referencias

• Referencias relativas

En la anterior vemos que estamos situados en la celda B4 (la que tiene el sombreado amarillo) y hacemos una referencia relativa a la celda C10. Con lo cual si arrastramos dicha referencia hacia abajo incrementamos la referencia en filas, si arrastramos hacia la derecha incrementamos la referecia en columnas, hacia arriba decrementamos la referencia en filas y hacia la izquierda decrementamos la referencia en columnas.

Con lo cual, lo más importante es tener claro que:

• Si arrastramos hacia abajo incrementamos filas
• Si arrastramos hacia arriba decrementamos filas
• Si arrastramos hacia la derecha incrementamos columnas
• Si arrastramos hacia la izquierda decrementamos columnas

• Referencias absolutas

Como podemos ver en imagen anterior las referencias absolutas son aquellas en las cuales aparecen los signos del dolar delante de la columna y delate de la fila, se dice que cuando hacemos una referencia absoluta a una celda estamos fijando dicha celda puesto que da igual que arrastremos en un sentido o en otro que la referencia se mantiene constante, como se puede observar en la imagen anterior.

• referencias mixtas o híbridas

Las referencias mixtas o híbridas son aquellas en las cuales solo está inmovilizada la fila o la columna. La que esta inmovilizada es la que tiene el signo del dolar delante y esta se mantendrá constante

Formato de celdas

Formato de celdas en Excel

Para realizar el formato de celdas se utiliza la barra de herramientas de formato o se hace clic con el botón derecho del ratón en la celda que desea dar formato y elija la opción "Formato de celdas" según la siguiente imagen:

El cuadro de diálogo de la opción "Formato de celda" se divide en las siguientes partes: Número, Alineación, Fuente, Borde, Relleno y Protección.

La opción Número se subdivide en varias categorías, como se explica a continuación:

General - Esta opción es la predeterminada para cada celda. Los textos y números aparecerán de la forma que se introduzca. Ejemplo: 23,07 y 23.07, ambas serán aceptadas;

Número - Aquí tienes la posibilidad de dar formato a la celda como número y elegir las posiciones decimales deseadas;

La Moneda- Alterala celda para moneda con el signo de la moneda deseada. El estándar es el Real, pero es posible elegir el dólar (USD), por ejemplo.

Contabilidad - A diferencia del elemento anterior, a pesar de transformar la celda en la moneda deseada, en este tipo de formación se muestran valores cero como trazos, alineando todos los símbolos de moneda y decimales, y visualización de valores negativos entre paréntesis, facilitando a la contabilidad de la empresa .

Fecha y hora - Sirve para identificar la fecha o la hora del día en que se está realizando la hoja de cálculo. Las opciones más utilizadas son dd / mm / aaaa o mm / dd / aaaa.

Porcentaje - Cambia la celda al formato de porcentaje;

Fracción - Formatee la celda (número) como fracción;

Científico - Aquí es posible utilizar la forma científica (E de exponencial) en la celda deseada;

Texto - Marque esta selección cuando introduzca sólo texto y números sin formato específico;

Especial - En esta opción es posible formatear en cualquier tipo de número deseado e, incluso, crear nuevas formateadas.

La opción Alineación tiene 4 tipos de formato para su uso en el texto, son:

Alineación del texto -Selecciona el tipo de alineación del texto: a la izquierda, a la derecha, centralizado o justificado;

Orientación del texto -Escoge cómo el texto será presentado: en la vertical o en la horizontal o aún en algún ángulo intermedio entre los dos;

Control del texto -Romper el texto automáticamente, reduciéndolo para caber en la celda o bien, combinar las celdas seleccionadas;

Dirección del texto -Aquí es posible cambiar la dirección del texto, es decir, si comenzará de izquierda a derecha o de derecha a izquierda o en el contexto de contexto.

La opción Fuente tiene 5 tipos de formato posibles para la celda:

Subrayado -Permite elegir el tipo de subrayado o ningún tipo de subrayado;

Efectos -Cambia la celda para efectos tachado, sobrescrito y suscrito;

Estilo de la fuente -Formatea el contenido de la celda entre las opciones regular, cursiva, negrita o negrita en cursiva;

Tamaño de la fuente:Sirve para cambiar el tamaño de la fuente deseada;

Color -Cambia el color del texto o el número presente en la celda.

La opción Borde tiene 4 opciones para elegir:

Línea -Hace que la celda tenga un borde con un determinado tipo de línea entre las opciones disponibles;

Color -Permite elegir el color del borde;

Predeterminado -Aquí se prefiere el tipo de borde deseado en las celdas entre: ninguna, contorno completo o contorno interno;

Borde -Da formato a las celdas con otras opciones de borde.

La opción Relleno tiene 3 posibles formatos:

Color del fondo -Rellena la celda con el color elegido;

Color del estándar -El color del fondo de la celda se cambia a la textura o el color elegido;

Estilo del estándar -Puede elegir el patrón deseado entre las opciones disponibles;

La última opción,Protección, permite ocultar o bloquear una celda, si se desea;