Crear funciones personalizadas en Excel - UDF (User Define Function)

Microsoft Excel cuenta con más de 450 funciones, divididas en distintas categorías tales como Búsqueda y referencia, Lógicas, Matemáticas entre otras. En el siguiente artículo te mostraremos como crear tus propias funciones personalizadas (UDF - User Define Function) haciendo uso de código en VBA (Visual Basic for Applications), para adecuarlas a tus necesidades como usuario de Microsoft Excel.

Iniciamos activando el editor de VBA, desde la cinta de opciones de Microsoft Excel, click en la pestaña Programador, grupo Código, vamos por la opción Visual Basic, para luego crear un Módulo, vamos hacia la barra de menú del editor de VBA, click en la opción módulo, terminamos con la opción Módulo, a la mano derecha veras el módulo ya creado, listo para ingresar código de VBA y crear nuestras funciones personalizadas, como lo muestran las siguiente imágenes. 

Vamos a crear una función que calcule el incremento porcentual de una cantidad en un porcentaje en especifico, para esto procedemos a nombrar a nuestro Módulo como incremento_porcentual, colocamos este nombre en la venta de propiedades del editor de VBA, como lo muestra la siguiente imagen.

Continuamos con el código de VBA para crear nuestra función personalizada, usamos el comando function para nombrar la función y definir las variables, así como el tipo de datos que contendran como se muestra a continuación tanto en código VBA, como en imágenes.

• function incremento_porcentual

• function incremento_porcentual(cantidad as double, porcentaje as double) as double

• incremento porcentual = cantidad + cantidad * porcentaje / 100

El comando function nos permite definir nuestra función personalizada, luego pasamos a definir las variables cantidad y porcentaje, estableciendolas como tipos Double, para que estas acepten números decimales, finalmente definimos el core, el corazón de la operación para calcular el incremento porcentual como cantidad + cantidad * porcentaje / 100

Finalmente, podemos llamar a nuestra función =incremento_porcentual() desde la hoja de cálculo de Microsoft Excel, pudiendo ya utilizarla.

El siguiente vídeo muestra como crear funciones personalizadas (UDF - User Define Function) en Excel.

Indicadores de rendimiento en Microsoft Excel - KPI

Indicador de rendimiento, indicador de desempeño, KPI (Key Performance Indicator), que podemos traducir como indicador clave de rendimiento, el cual es una medida, un valor que determina el nivel de rendimiento de un determinado proceso dentro de una empresa u organización, es un elemento fundamental al momento de gestionar cualquier tipo de operación y poder determinar que tan cerca o lejos estamos de alcanzar los objetivos previamente establecidos;en este artículo de Hablamos Excel te mostraremos como crear un KPI básico, general sobre el desempeño de un grupo de vendedores con respecto a la cuota de venta establecida por el Departamento de Ventas haciendo uso de Microsoft Excel.

Comenzamos creando la pantilla que contendra la lista de vendedores, las ventas (monto en efectivo) alcanzadas por cada uno de ellos, así como la meta individual (mensual, anual), la cantidad que establecio la empresa como objetivo final para cada uno de sus vendedores; tenemos a un valor máximo (Meta individual) y un valor mínimo (Venta por empleado) que Microsoft Excel tomará para crear los indicadores de desempeño (KPI) para cada vendedor.

Seleccionamos los registros de la columna Ventas alcanzadas por empleado, el cual nos permitira crear los indicadores de desempeño para cada vendedor, tomando el valor mínimo y máximo como referencia, continuamos en la cinta de opciones, haciendo click sobre la opción Formato Condicional, seguimos con Conjunto de iconos, terminando en la opción 5 flechas de color, que permite representar los valores de las celdas seleccionadas.

También podrias ir por la opción Barra de datos, dentro de Formato Condicional, que mostrara el avance de cada vendedor con respecto a la cuota de venta (Valor Máximo) como barras que representan el valor de la celda, como muestra la siguiente imagen.

Debes tener en cuenta que este es un panel de indicadores de performance básico, que te va a permitir entender como gestinarlos de manera general y experimentar con tus propios datos, registros y KPI`s, estamos seguros que te será de utilidad. 

 

El siguiente vídeo te muestra como crear KPI`s en Microsoft Excel.

Vínculo dinámico entre SQL Server y Excel

El siguiente artículo del Blog Hablamos Excel muestra como crear un vínculo dinámico entre una hoja de cálculo en Microsoft Excel y una tabla en SQL Server, cada vez que se realice algún cambio, modificación, se inserte o elimine registros en la tabla en SQL Server dicho cambio se vera reflejado automáticamente en el archivo de Excel.

Activamos el SSMS (SQL Server Management Studio), vamos a trabajar con la base de datos Northwind, seleccionamos la tabla Products y ejecutamos las siguiente sentencias SQL para observar los registros contenidos en la mencionada tabla; use Nortwind (activa la BBDD), select * from Products (muestra todos los registros de la tabla Products.

En la cinta de opciones vamos por la pestaña Datos, click en Obtener Datos, aparecera un submenú indicando Desde una base de datos, que nos llevará  la opción Desde una base de datos SQL Server, como lo muestra la siguiente imagen.

Se activara la ventana Base de datos SQL Server donde ingresaremos el nombre del servidor (instancia SQL Server) y el nombre de la base de datos (Northwind), esto nos llevara a una segunda ventana de donde seleccionamos Windows, usar mis credenciales actuales, click en Conectar, como lo muestra la siguiente imagen.

A continuación, se mostrara la ventana Navegador, desde donde selecionaremos la tabla Products que forma parte de la base de datos Northwind, click sobre el botón Conectar.

A partir de aquí, ya contaremos con todos los registros de la tabla Products en nuestra hoja de cálculo de Excel, desde la cual cada vez que el administrador de la base de datos Northwind realice algún cambio sobre la tabla, el usuario de Microsoft Excel podra realizar la actualización respectiva.

El siguiente vídeo muestra como establecer un vínculo dinámico entre SQL Server y Excel

Importar registros desde Access a Excel

Una de las tareas básicas en el manejo de aplicaciones Desktop (escritorio) es importar registros entre programas, el siguiente artículo te muestra paso a paso, del proceso de importar registros entre el gestor de base de datos Microsoft Access y tu hoja de cálculo favorita (de hecho la única) Microsoft Excel.

A continuación mostramos la base de datos Access que vamos a importar hacia Excel, es una tabla simple, formada por dos columnas ID_GENERO y GENERO_MSC, como lo muestra la siguiente imagen.

Nos dirigimos a la cinta de opciones de Microsoft Excel, vamos por la opción Datos, en el grupo Obtener y transformar datos, hacemos click en el icono Obtener datos, se mostraran un submenú que señala Desde una base de datos, click en la opción Desde una base de datos Access.

Se mostrara la ventana Importar Datos, donde seleccionaremos el archivo de Access llamado Music Store (Music Store.accdb), se mostrara una ventana rotulada como Navegador, donde se muestra la base de datos (Music Store) y las dos tablas que la componen, seleccionamos la tabla Generos y hacemos click en Cargar para llevar los registros de Access a la hoja de cálculo.

Finalmente, terminamos importando los registros de la base de datos de Microsoft Access,desde la Tabla GENEROS  hacia la hoja de cálculo de Microsoft Excel, cabe señalar que al manejar sólo 21 registros contenidos en la tabla, el proceso de importación de datos es veloz, muy veloz, esto cambiaria si trabajamos con miles o millones de registros.

Descarga el archivo de Access mostrado en el artículo

MUSIC STORE.accdb

Crear un Histograma de frecuencias en Microsoft Excel

El Histograma es un gráfico estadístico que nos permite representar la distribución de frecuencias de una variable continua, podemos mencionar ejemplos tales como la altura de un grupo de personas, su peso en Kg o la cantidad de miembros de una familia.

Cabe señalar que el Histograma forma parte de las 7 herramientas básicas de la calidad, éstas son un conjunto de técnicas y herramientas gráficas utilizadas para encontrar soluciones a problemas relacionados con la calidad en empresas u organizaciones, el siguiente artículo muestra como crear un Histograma de frecuencias haciendo uso de Microsoft Excel. 

A cotinuación, creamos una tabla de distribución de frecuencias estableciendo las siguientes columnas: Peso en Kg (Intervalos), Marca de clase, Frecuencia absoluta y frecuencia acumulada, debemos indicar el tamaño de la muestra es de 20 unidades estadísticas, como lo muestra la siguiente imagen.

Nos posicionamos en una celda vacía de la hoja de cálculo, en la cinta de opciones vamos por la pestaña INSERTAR, grupo Gráficos, hacemos click en el icono Insertar Gráfico de columnas o de barras, como lo muestra la siguiente imagen.

Luego de seleccionar el gráfico respectivo, se activara la pestaña Diseño, vamos por la opción Modificar Datos, aparecera la ventana Seleccionar origen de datos, donde hacemos click en la opción Agregar, se mostrara la ventana Modificar Serie, la cual cuenta con dos opciones Nombre de la serie (escribiremos HISTOGRAMA) y Valores de la serie, donde colocaremos los registros asignados a la columna Frencuencia Acumulada. 

Continuamos en la venta selección de origen de datos, modificando las etiquetas del eje horizontal, al seleccionar los registros asignados a la columna Marca de Clase.

Terminamos la creación del Histograma de frecuencias al seleccionar todas las barras del gráfico y hacer click sobre una de ellas, aparecera la área de configuración Formato de punto, para juntar las barras y eliminar el espacio entre ellas colocamos como en la sección ancho del rango, el número 0, esto nos permitira crear el Histograma como lo muestra la siguiente imagen.

El siguiente vídeo muestra como crear un Histograma y poligono de frecuencias en Microsoft Excel.

15 atajos de teclado para Microsoft Excel

Los atajos de teclado (keyboard shortcuts) consisten en una tecla o grupo de teclas que deben ser ejecutadas al mismo tiempo para activar un comando en específico, toda aplicación, todo software contiene dentro de su estructura un conjunto de atajos de teclado que permiten el manejo del aplicativo a través del teclado, dejando de lado el omnipresente mouse (ratón), el siguiente artículo se enfoca en 30 atajos de teclado para Microsoft Excel,estos atajos de teclado pueden ser aplicados en las versiones 2010, 2013, 2016 y 2019 de Excel, estamos seguro que te serán de utilidad para mejorar tu manejo de la hoja de cálculo.

1.- CTRL + A

La combinación de teclas CTRL + A muestra el cuadro de diálogo Abrir.

2.- CTRL + B

La combinación de teclas CTRL + B muestra el cuadro de dialogo Buscar.

3.- CTRL + E

La combinación de teclas CTRL + E selecciona todas las celdas de la hoja actual.

4.- CTRL + G

La combinación de teclas CTRL + G guarda el libro de trabajo (workbook)

5.- CTRL + I

La combinación de teclas CTRL + I muestra el cuadro de diálogo Ir a.

6.- CTRL + T

La combinación de teclas CTRL + T muestra el cuadro de diálogo Crear Tabla.

7.- CTRL + U

La combinación de teclas CTRL + U crea un nuevo libro de trabajo.

8.- CTRL + SHIFT + U

La combinación de teclas CTRL + SHIFT + U amplia o contrae la barra de fórmulas.

9.- CTRL + F1

La combinación de teclas CTRL + F1 muestra o oculta la cinta de opciones.

10.- CTRL + 1

La combinación de teclas CTRL + 1 muestra el cuadro de diálogo formato de celdas.

11.- CTRL + 9

La combinación de teclas CTRL + 9 oculta las filas seleccionadas.

12.- CTRL + 0

La combinación de teclas CTRL + 0 oculta las columnas seleccionadas.

13.- ALT + F8

La combinación de teclas ALT + F8 abre el cuadro de diálogo Macro.

14.- ALT + F11

La combinación de teclas ALT + F11 abre el editor de VBA (Visual Basic for Applications).

15.- SHIFT + F3

La combinación de teclas SHIFT + F3 muestra el cuadro de diálogo Insertar una función.

El siguiente vídeo muestra 15 atajos de teclado para Microsoft Excel

Crear formulario en Microsoft Excel sin usar código en VBA

El uso de formularios en Microsoft Excel nos permite contar con una herramienta de ingreso de registros a la hoja de cálculo, cuando la usamos como base de datos, pero que pasa si tu experiencia con VBA (Visual Basic for Applications) no es muy amplia que digamos, y te cuesta crear el formulario, el siguiente artículo muestra como crear un formulario en Excel sin hacer uso de código en VBA, sólo utilizando las herramientas (built-in) que nos proporciona Excel.

Nos dirigimos hacia la barra de herramientas de acceso rápido, que se encuentra en la esquina superior derecha de nuestra libro de Excel (Workbook), vamos por la opción Más Comandos, esto nos llevara hacia la ventana Opciones de Excel - Barra de herramientas de acceso rápido; en la lista de menú desplegable Comandos Disponibles en:, seleccionamos todos los comandos, esto nos permitira seleccionar la opción formulario, terminamos agregando la opción al hacer click en el botón Agregar, como lo muestran las siguiente imágenes.

En la barra de herramientas de acceso rápido ya podemos apreciar la opción formulario, para la demostración trabajamos con una tabla compuesta por seis campos: CODIGO, CATEGORIA,ARTICULO, PRECIO COMPRA, PRECIO VENTA Y GANANCIA; compuesta por diecinueve registros, posicionamos el cursos sobre cualquiera de las celdas que componen la tabla y click en el botón formulario; aparecera la venta respectiva con todos los campos configurados, este formulario nos permitira agregar, elliminar, buscar y establecer criterios para ingresar nuevos registros sin necesidad de usar código de VBA (Visual Basic for Applications), como lo muestra la siguiente imagen.

El siguiente vídeo muestra como crear un formulario en Excel sin hacer uso de código en VBA