macros en Microsoft Excel

En este video aprenderemos a crear macros para automatizar procesos repetitivos.

A la siguiente matriz le daremos cierto formato y cómo este formato es el que usaremos en el resto de tablas bien vale la pena grabar una macro para hacer esto una vez y luego ejecutarla y obtener el mismo resultado.

Entonces desde el menú vista, nos vamos a las opciones de macros, clic en grabar macro, le damos un nombre a la macro y hacemos clic en aceptar, ahora todo lo que hagamos excel lo grabará en subrutinas, empezamos entonces en darle un poco de formato a esta tabla.

Al finalizar nos vamos al menú vista de nuevo y hacemos clic en detener macro. Nos vamos a otra hoja donde tenemos otra tabla con un formato similar al de la tabla de la hoja anterior pero con distintos datos. Entonces aquí ejecutaremos la macro que hemos creado. Desde el menú vista vamos a macros, ver macros, buscamos la macro con el nombre que establecimos, clic en ejecutar y la macro se ejecutará de acuerdo a las instrucciones que tiene.

Muy importante, para que las macros funcionen, el libro debe estar habilitado para macros. Hacemos esto desde el menú archivo, guardar como seleccionamos libro de excel habilitado para macros, si se fijan tiene la extensión xlsm.

graficos dinamicos en Microsoft Excel

Para crear un gráfico dinámico seguimos básicamente los mismos pasos que una tabla dinámica, seleccionamos el origen de datos y establecemos cuáles serás las columnas, filas, filtros y valores que aparecerán en dicho gráfico.

Entonces seleccionamos la matriz, desde el menú insertar seleccionamos la opción gráfico dinámico, para este ejemplo seleccionaremos la primera opción de gráficos y luego podemos configurar el gráfico como lo hacemos con una tabla dinámica.

Vamos a poner en leyenda el campo país, en filas el campo vendedor y en valores la cantidad. También aplicaremos un filtro por producto.

Ahora podemos ver el gráfico de unidades vendidas de un producto por cada vendedor en cada país.

tabla dinamica en Microsoft Excel

En este video crearemos una tabla dinámica para obtener el siguiente resultado: queremos ver el total de unidades vendidas de un vendedor de un producto por país.

Para ello seleccionamos toda la matriz, nos vamos al menú insertar, y hacemos clic en tabla dinámica, marcaremos la opción que nos genere la tabla dinámica en una nueva hoja.

Ahora debemos configurarla, podemos obtener distintos resultados dependiendo de las combinaciones que hagamos con el panel de campos de tabla dinámica y con opciones como columnas, filtros, filas y valores.

Entonces pondremos en columnas país y producto, en filas será el vendedor y por último en valores pondremos cantidad.

Ahora aplicaremos un filtro para ver estos mismos datos pero queremos ver un filtro por precio unitario de los productos.

Entonces arrastramos el campo precio unitario espacio de filtros. Ahora seleccionamos los precios y podremos hacer este tipo de filtros.

funcion bdmax bdmin bdpromedio bdproducto bdsuma en Microsoft Excel

En este video veremos como utilizar las funciones BDMAX, BDMIN, BDPROMEDIO, BDSUMA y BDPRODUCTO

Usaremos BDMAX para calcular la venta más alta y BDMIN la venta más baja de un vendedor. Entonces escribimos =BDMAX( seleccionamos la matriz de datos, el nombre del campo que será el total y luego el criterio que el vendedor que tenemos al lado.

Para la función BDMIN, BDPROMEDIO y BDSUMA usaremos los mismos parámetros que la función BDMAX pero obtendremos la venta menor, el promedio y total de ventas respectivamente.

Para la función BDPRODUCTO haremos un pequeño cambio en los criterios, siempre usaremos la misma base de datos, multiplicaremos los totales del producto 42 vendido en Francia con cantidad mayores a 10

funcion bdcontar y bdcontara en Microsoft Excel

En este video veremos un ejemplo de como usar la función BDCONTAR para encontrar el número de facturas emitidas de un determinado país y  BDCONTARA para obtener la cantidad de unidades vendidas de un producto en un determinado país

Escribimos =BDCONTAR(seleccionamos toda el área de la tabla incluyendo los encabezados, ahora el nombre del campo en este caso es el encabezado “Factura”, y por último el criterio el cual será el país Alemania. Podríamos cambiar el nombre del país para así obtener el resultado correspondiente.

Ahora usaremos la función BDCONTARA siempre seleccionando toda la tabla, luego el campo en este caso es el país y el criterio es el código 42 debido a que ese producto es el que queremos evaluar.

Autofiltros en Microsoft Excel

En este video aprenderemos a hacer algunos filtros para realizar algunas tareas y también para observar los datos de una mejor forma.

Tenemos la siguiente tabla y por ejemplo queremos conocer de este detalle de facturas, todos los productos vendidos originarios de Alemania. Entonces nos ubicamos en cualquier parte de la tabla. Desde el menú Inicio no dirigimos a la sección modificar y hacemos clic en ordenar y filtrar. En los encabezados de cada columna veremos un botón con un triangulo invertido que nos permitirá filtrar los datos de distintas formas dependiendo del valor de cada celda en la columna que seleccionemos. Primero mostremos el detalle de factura de los productos de Alemania, seleccionemos el país, desmarcamos todas las opciones, ahora solo seleccionamos Alemania, aprovecharemos y cambiaremos el nombre del país a español porque aparece en inglés.

Ahora veremos las facturas de productos alemanes que hayan vendido más de 25 unidades, nos dirigimos a la columna cantidad, ubicamos el mouse en la opción filtros de números, mayor que, 25, clic en aceptar. Bueno, así podemos hacer distintos filtros dependiendo de nuestras necesidades.

Seguridad en libros y hojas de calculo en Microsoft Excel

En este video, haremos uso de algunas opciones disponibles en excel para proteger con contraseña tanto la hoja de cálculo como el libro en sí.

Lo primero que haremos será proteger el libro para que no se pueda abrir si no se introduce  la contraseña que establezcamos. Nos dirigimos al menú Archivo, Información, clic en la opción proteger libro, establecemos una contraseña, nos pedirá confirmación y damos clic en aceptar. Podemos cerrar y abrir el archivo para comprobar que nos solicita contraseña para abrir el mismo. Podemos remover la contraseña siguiendo exactamente los mismos pasos y dejamos vacío la casilla de contraseña.

Otra opción disponible es proteger solamente la hoja de cálculo en lugar de todo el libro. Para esto nos dirigimos al menú revisar, seleccionamos proteger hoja de cálculo. Nos solicitará establecer una contraseña para tal fin y además, veremos algunas opciones que si las marcamos, las personas que usen este libro de excel podrán realizar esas operaciones en nuestra hoja de cálculo a pesar que hayamos establecido una contraseña. Escribimos una contraseña, nos pide confirmación y clic en aceptar. Como vemos en pantalla, no podemos hacer ninguna modificación.

Funciones de busqueda, buscarv, buscarh en Microsoft Excel

En esta oportunidad veremos funciones de búsqueda como buscarv y buscarh. Buscarv hace búsquedas verticales en una matriz y buscarh lo hace de forma horizontal.

Primero veremos la función buscarv la cual usaremos para buscar datos de una factura que está en una tabla presentada en formato vertical. Escribimos =BUSCARV( el valor buscado es el numero de factura, luego seleccionamos donde hará la búsqueda la función, indicamos la columna de dónde obtendrá el resultado y le indicamos también que queremos que el dato de la factura coincida exactamente con el que encuentra en la celda.

Haremos lo mismo para encontrar el producto y el total únicamente cambiando el numero de columna de dónde obtendrá el resultado.

Otra función que tenemos de búsqueda es la función buscarh que realiza búsquedas horizontales. En esta ocasión buscaremos los mismos datos de otra factura pero utilizando esta función.

Escribimos =BUSCARH( valor buscado, la matriz, el número de fila donde aparece el resultado y también que obtenga el dato donde coincida exactamente la celda con nuestra búsqueda.

Copiamos esta función para encontrar el producto y total y solo cambiamos el número de fila de donde obtendremos los datos.

Graficos en Microsoft Excel

Los gráficos como ustedes saben, son herramientas visuales que nos permiten presentar o digerir la información más fácilmente para un posterior análisis. Los más comunes son el gráfico de barras, el circular y el de anillos.

Por supuesto, hacer uno de estos u otros gráficos en excel es muy fácil, primeramente debemos seleccionar el origen de datos, luego seleccionar el gráfico y finalmente si así lo decidimos, podemos personalizar estos gráficos.

Primeramente haremos un gráfico de barra para presentar las ventas de 5 productos. Seleccionamos la columna de los productos, así como también la columna o celdas donde aparece la información numérica que será desplegada en el gráfico; desde el menú insertar seleccionamos el gráfico de barras. Como se puede observar, existen múltiples opciones para modificar cada gráfico, como los colores, estilo de letra, formato, etc de como se muestra cada gráfico.

Los mismos datos ahora los mostraremos en un gráfico circular y de anillos.

Primero haremos el gráfico circular por lo cual seleccionamos siempre el origen de datos, nos dirigimos al menú insertar y seleccionamos el gráfico circular. Al igual que con el gráfico de barra, podemos personalizarlo de acuerdo a nuestras necesidades.

Por último, seleccionamos nuevamente los datos, siempre vamos al menú insertar y seleccionamos el gráfico de anillos. Si es necesario, podemos utilizar las opciones disponibles para una personalización del mismo.

Funciones Lógicas en Microsoft Excel

 En este video veremos como usar algunas funciones lógicas en excel.

Primero iniciaremos con la función si condicional, aplicaremos un 20% de descuento si la cantidad de producto vendida es mayor a 20, escribimos =SI( e4 > 20, entonces 20%, sino 0), copiamos la fórmula.

Ahora usaremos la función Y, esta función es la conjunción que devuelve un valor verdadero si todas las evaluaciones dan como resultado verdadero. Escribimos entonces =Y(A4=”Brasil”, e4>10) si está condición se cumple retornará un valor verdadero.

Tenemos también la función NO que usamos cuando se desee asegurar de que un valor no es igual a otro. La sintaxis es muy sencilla, simplemente escribimos =NO( y por ejemplo seleccionamos la celda H para obtener un resultado contrario al devuelto por la función Y.

Por último usaremos la función O la cual es la disyunción que devuelve un verdadero siempre y cuando una de las condiciones sea verdadero

escribimos =O( seleccionamos la celda A4="Francia", A4="Alemania") copiamos y listo.

Funciones de Fecha y Hora en Microsoft Excel

Vamos a utilizar funciones de fecha, de una fecha dada obtendremos un entero que indicará el día, mes y año. Escribimos =DIA( y la celda, =MES( y luego =AÑO(

También podemos obtener mediante una función la fecha y ahora del sistema operativo en una celda, escribimos =AHORA(). AHORA nos muestra la fecha y hora pero tenemos otra función con la que podemos obtener únicamente la fecha =HOY().

Por último de las fechas de las ventas daremos un descuento adicional a las ventas realizadas los días lunes, sin importar la fecha. Para ello utilizaremos la función día de semana que tiene como sintaxis una celda y un número entero que sirve para especificar qué día de la semana será el primer día, por default el primer día es domingo, sin embargo, para nuestro ejemplo diremos que el primer día de la semana será Lunes. También usaremos una función condicional para aplicar el descuento en la misma celda. Ok, procedemos y escribimos primero la función condicional =SI( ahora la prueba lógica, escribimos la función DIASEM( indicamos la celda, escribimos el número 2 para que excel sepa que la semana inicia el Lunes, por lo que cuando la fecha sea lunes nos devolverá como resultado un número 1, luego el descuento del 30% sino 0. Ahora copiamos la formula y listo.

Funciones Numericas en Microsoft Excel

En este video veremos como utilizar funciones numéricas como producto, entero y valor absoluto.

Primeramente calcularemos el total de ventas multiplicando el precio unitario por la cantidad de unidades vendidas, por lo cual escribiremos =PRODUCTO( e indicaremos las celdas o rango de celdas, procedemos a copiar la fórmula también.

Ahora vamos a utilizar la función ENTERO que se utiliza para redondear un número hasta el entero inferior más próximo, los resultados los ubicaremos en la columna N, escribimos =ENTERO( y como siempre seleccionamos la celda de origen de datos.

Por último también usaremos una función para obtener el valor absoluto de un número, es decir, un número sin “signo”, escribimos =ABS( seleccionamos la celda de origen y obtenemos el valor absoluto de ese dato.

Funciones de Texto básicas en Microsoft Excel

Vamos a aplicar las funciones de texto más comunes. Para comenzar, vamos a obtener la última letra del código que aparece en las celdas de la columna B, para ello escribimos =DERECHA( seleccionamos la celda, luego indicamos el número de caracteres que tomaremos a partir del final del texto hacia la izquierda y listo copiamos la fórmula para obtener un resultado completo.

Lo siguiente que haremos será obtener la letra inicial de la empresa que aparece en las celdas de la columna C, entonces escribimos =IZQUIERDA( seleccionamos la celda, luego indicamos el número de caracteres que tomaremos a partir del inicio del texto hacia la derecha y listo.

Ahora vamos a hacer que todos los nombres de contactos aparezcan en mayúsculas, escribimos =MAYUSC( e indicamos la celda origen de datos). El ejemplo nos pide que los títulos de los contactos aparezcan en minúsculas, escribimos =MINUSC( celda y listo.

Finalmente utilizaremos la función valor para convertir a número los números que están como texto en la columna A, escribimos =VALOR( y la celda en cuestión.

Función Pagoprin en Microsoft Excel

A continuación usaremos la función pagoprin para calcular cuánto es el capital que estamos pagando en una determinada cuota de un préstamo que hayamos recibido o proporcionado.

Ocupamos entonces la función pagoprin, escribimos =PAGOPRIN, seleccionamos la tasa dividida entre 12 para conocer la tasa mensual, luego período correspondiente al mes que vamos a calcular, el plazo en años lo multiplicamos por 12 para obtener el total de meses y el monto del principal.

Bloqueamos las celdas que no queremos que se cambien cuando copiemos la fórmula.

De esa manera podemos calcular el monto del capital que pagamos o recibimos de un determinado financiamiento siempre y cuando la tasa sea constante.

Función Pagoint en Microsoft Excel

En este vídeo utilizaremos la función pagoint que nos da como resultado el interés pagado en un período específico a una tasa de interés constante.

Escribimos =PAGOINT( la tasa anual dividida entre 12 para conocer la tasa mensual, periodo en este caso el mes que vamos a calcular, lo siguiente es el número total de pagos que para este ejemplo lo tenemos en años por lo que lo multiplicamos por 12 para saber el número en meses y finalmente el valor total del préstamo.

Le ponemos los símbolos de moneda a algunas celdas para que solo se modifique la celda de los meses porque copiaremos la fórmula para saber el interés pagado de cada mes.

Y al igual que la función pago, el resultado aparece negativo porque es un flujo de salida.

Función Pago en Microsoft Excel

La función pago que veremos en esta oportunidad, calcula el monto de pago de un préstamo basado en pagos y tasa de interés constante.

Como vemos en este ejemplo tenemos los siguiente datos como lo son el principal de 300 mil córdobas, el interés o tasa que son 9.75% anual a un plazo de 5 años. Para saber cuánto pagaremos mensualmente utilizaremos la función pago.

Escribimos =PAGO( seleccionamos la tasa de interés y la dividimos entre 12 porque queremos saber la tasa mensual, debido a que la tasa presentada de 9.75% es anual, luego seleccionamos el plazo en años que son 5 y los multiplicamos por 12 para obtener el plazo en meses y finalmente seleccionamos el monto principal.

La sintaxis de la función pago también tiene dos campos más como valor futuro y tipo los cuales son opcionales y en esta oportunidad no los usaremos.

Presionamos enter y obtenemos un resultado en negativo, precisamente porque es un flujo de salida, no obstante si no desean que aparezca el dato de manera negativa, pueden cambiar el formato y aparecerá como lo necesitan. 

Funciones Básicas en Microsoft Excel

En esta oportunidad veremos cómo usar funciones básicas como suma, promedio contar etc que serán seguramente funciones que emplearán con frecuencia en un libro de excel.

Primeramente vamos a calcular el total de unidades vendidas. Para ello escribimos =SUMA( y seleccionamos el rango de unidades vendidas en el periodo indicado.

Para el máximo de ventas escribimos =MAX( indicando el rango de celdas. La obtener el mínimo escribimos =MIN( con el rango de celdas correspondiente. Para conocer el promedio de ventas escribimos =PROMEDIO( y finalmente para saber cuantos días vendimos, escribimos =CONTAR( y seleccionamos todas las fechas de la tabla.

Excel provee muchas opciones para acceder a todas las funciones que tiene disponible, aquí hemos mostrado como escribiendo el nombre de la función e indicando un rango de celdas obtendremos los resultados correspondientes, sin embargo, también podemos acceder a las funciones de la siguiente manera:

Haciendo clic en insertar función, seleccionamos una categoría de función, luego la función como tal, seguimos su sintaxis y listo.