Listas Desplegables en Microsoft Excel

Las listas desplegable en Microsoft Excel, nos permiten mejorar la eficiencia en el trabajo dentro de nuestra hoja de cálculo, permitiendonos seleccionar un registros (dato) dentro de un conjunto de posibles opciones, reduciendo tiempos y sobre todos evitando errores involuntarios por parte del usuario al seleccionar opciones de una lista en especifico.

Trabajaremos con una tabla compuesta por tres campos: NOMBRE, FECHA DE NACIMIENTO, EDAD y siete registros, al crear la lista desplegable, los registros que serán mostrados en formato de lista, pueden estar dentro de la hoja de cálculo en la que estamos trabajando o fuera de la misma; otras hojas de cálculo que forman parte de nuestro libro de trabajo (Workbook). Posicionaremos el cursor dentro de la celda donde queremos que se muestre la lista desplegable, para luego dirigirnos a la cinta de opciones, pestaña Datos, grupo Herramientas de Datos, click en la opción Validación de Datos, como muestran las siguientes imágenes.

Luego de hacer click en la opción Validación de datos, aparecerá un ventana con el mismo nombre, vamos por las opciones Lista para generar las listas desplegables, continuamos con la opción Origenes, donde seleccionaremos el rango de celdas que continen los registros a mostrar en la lista desplegable, como muestran las siguientes imágenes.

Nos mostrará la lista desplegable en la celda que indiquemos, para que afecte a una rango de celdas en especifico, utilizaremos el control de relleno para tener disponible la lista desplegable en las celdas que inferiores como muestra la siguiente imagen.

El siguiente vídeo muestra cómo crear una lista desplegable en Microsoft Excel

Crear mapas en Microsoft Excel

La creación de mapas para Microsoft Excel es una tarea relativamente fácil, en el siguiente artículo te mostraremos cómo a través de un par de clicks y configuraciones que te toman un par de segundos, podras crear representaciones gráficas para registros relacionados a países, que Excel transformará en mapas en nuestra hoja de cálculo.

Trabajaremos con una tabla compuesta por dos campos (columnas) PAISES y PRESUPUESTO, una lista de países y el dinero asignado a cada uno de ellos, seleccionaremos la tabla en su conjunto, columnas y registros, para dirigirnos a la cinta de opciones, pestaña INSERTAR, grupo GRAFICOS, click sobre la opción MAPAS (Insertar gráfico de mapas) que procederá a crear un mapa basado en la lista de países de nuestra tabla, como se muestra en imágenes.

Aunque estamos mostrando los aspectos básicos de la creación de mapas en Microsoft Excel, podemos señalar que podemos modificar la apariencia de muestro mapa, tanto en los colores asignados por defecto para cada uno de los países asignadoa nuestra tabla, así como el estilo de presentación, vamos hacia la cinta de opciones, pestaña DISEÑO, vamos por las opciones CAMBIAR COLORES o ESTILO DE DISEÑO, como lo muestra la siguiente imagen.

El siguiente vídeo muestra cómo crear mapas en Microsoft Excel

10 Funciones Básicas de Microsoft Excel

La hoja de cálculo más usada a nivel mundial tanto en el ámbito personal como profesional, es sin lugar a dudas Microsoft Excel, la aplicación de ofimática cuenta con cientos de funciones, organizadas en distintas categorías como estadísticas, financieras, lógicas, matemáticas entre otras, el siguiente artículo te mostrará 10 funciones básicas de Excel, para que tomes tus primeros pasos en el dominio de esta importante aplicación de escritorio.

1.- Suma (=suma)

La función =suma() suma todos números en un rango de celdas.

2.- Producto (=producto)

La función =producto() multiplica todos los números especificados como argumentos.

3.- Promedio (=promedio)

La función =promedio() devuelve el promedio (media arimética) los cuales pueden ser números, referencias, matrices o referencias que contengan números.

4.- Max(=max)

La función =max(), devuelve el valor máximo de una lista de valores, omitiendo los valores lógicos y el texto.

5.- Min(=min)

La función =min(), devuleve el valor mínimo de una lista de valores, omitiendo los valores lógicos y el texto.

6.- Contar(=contar)

La función =contar() cuenta el número de celdas de un rango que contienen números.

7.- Contara(=contara)

La función =contara() cuenta el número de celdas no vacías de un rango.

8.- Mediana(=mediana)

La función =mediana() devuleve la mediana o el número central de un conjunto de números.

9.- Moda.uno(=moda.uno)

La función =moda.uno() devuelve el valor más frecuente o repetitivo de una matriz o de un rango de datos.

10.- Redondear(=redondear)

La función =redondear() redondea una cantidad al número de decimales especificado.

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

Crear alertas de vencimiento para inventarios en Microsoft Excel

Cuando trabajamos con inventarios, en especial aquellos que contienen productos próximos a caducar o vencer, estamos seguros que nos serviria de mucho "alertas" en nuestras hojas de cálculo, en nuestra tablas de que este o aquel producto ya vencio, te mostraremos como hacer uso de las funciones =hoy() y =si() en combinación con el formato condicional para lograrlo.

Trabajaremos con una tabla que contiene tres campos: PRODUCTO, FECHA VENCIMIENTO y SITUACION, donde tendremos una lista de items, de productos perecibles, seguidos de su respectiva fecha de caducidad para terminar indicando su estado: PENDIENTE o VENCIDO.

Para crear la alerta debemos hacer uso de la función =hoy(), que nos permitira establecer la fecha actual, la cual sera la base para determinar si un producto está a punto de vencer, como se aprecia en la siguiente imagen.

Nos posicionamos debajo de la celda situación, procediendo a hacer uso de la función =si(), indicando: =SI(I6<$I$2;"VENCIDO";"PENDIENTE"), si la fecha de vencimiento del producto es menor a la fecha actual, se debe mostrar el mensaje VENCIDO, caso contrario se indicara como PENDIENTE, como lo muestra la siguiente imagen.

Siendo el resultado final, el que se aprecia en imágenes bajo la columna SITUACION podemos ver dos estados el de PENDIENTE y VENCIDO, tomando como base la fecha actual y la fecha de caducidad de cada producto.

Se podria hacer uso del Formato Condicional en la cinta de opciones para hacer esa diferencia entre ambos estados, vamos por la opción Reglas para resaltar celdas, según el Texto que contiene, indicando que aquellas celdas que contengan el texto VENCIDO, se muestren con un relleno rojo claro, como se muestra en imágenes.

Mostrandose ese color rojo oscuro, que mencionamos líneas arriba, para aquellas celdas que contengan la palabra VENCIDO, la alerta de vencimiento no mantendria al tanto de aquello productos por vencer haciendo nuestra labor mucho más eficiente.

El siguiente vídeo muestra como crear alertas de fecha de vencimiento para inventarios en Excel

Convertir PDF a Excel usando Power Query

El trabajar con archivos en formato PDF es muy conveniente y útil para el manejo de distintos tipos de datos, registros e información, hasta que tienes que trabajar con otros formatos como Excel, Word o Power Point; el siguiente artículo del Blog Hablamos Excel te mostrará como convertir un archivo en formato PDF a Microsoft Excel, sin necesidad de usar aplicaciones externas, para lograrlo haremos uso de Power Query para realizar la conversión de formatos.

Trabajaremos con un archivo PDF, de hecho creamos dicho archivo haciendo uso de Microsoft Word, el archivo PDF en cuestión tiene las siguentes características: 4 campos (columnas) y seis registros, es una estructura muy básica, pero que nos servira para mostrar el proceso de conversión de PDF a Excel.

Tenemos el archivo PDF que vamos a convertir, ahora nos dirigimos hacia la cinta de opciones de Microsoft Excel, pestaña Datos,opción Obtener Datos, aparecerá un submenú, vamos por Desde un archivo, terminando en Desde Texto / CSV, esto nos permitira cargar el archivo PDF, pero debemos tener en cuenta en seleccionar la opción Todos los archivos en la esquina inferior derecha de la ventana Importar Datos, como los muestran las siguientes imágenes.

Al cargar el archivo PDF, aparecera la interfaz de Power Query lista para transformar el archivo debemos hacer click sobre el nombre del mismo archivo_pdf para dirigirnos al área de edición, una vez allí hacemos click en la celda segunda celda de la columna Data, como lo muestra las siguiente imágenes para poder apreciar la tabla, el archivo PDF, sus columnas y registros en conjunto, terminando haciendo click en la opción Cerrar y cargar para llevar nuestros registros a Excel y terminar con el proceso de conversión de PDF a Excel.

La siguiente imagen te muestra el resultado final de conversión, ta tienes la tabla, los registros desde el archivo PDF en la hoja de cálculo de Microsoft Excel.

El siguiente vídeo muestra como convertir un archivo PDF a Excel.

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