🎃 Grandes descuentos en libros en línea, eformaciones y vídeos*. Código CALABAZA30. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Aprenda el lenguaje VBA
  3. Gestión de empleados: explotación de los datos en bruto
Extrait - Aprenda el lenguaje VBA y conviértase en un experto en Excel (versiones 2019, 2021 y Microsoft 365)
Extractos del libro
Aprenda el lenguaje VBA y conviértase en un experto en Excel (versiones 2019, 2021 y Microsoft 365) Volver a la página de compra del libro

Gestión de empleados: explotación de los datos en bruto

Fórmulas avanzadas de Excel: descripción de ejemplo

Descripción general del ejemplo

 Abra el libro Enunciado_2-ABC.xlsx.

Este ejemplo contiene los datos de los empleados de una empresa de TI en los años N y N-1. Esta lista se presenta como una tabla de datos que contiene la lista de todos los empleados con su información. Hay 3000 empleados en la empresa en el año N. Estos 3000 empleados no son necesariamente los mismos que los del año N-1, ya que, entre los dos años, algunos se fueron y otros llegaron.

A la empresa le gustaría obtener más visibilidad sobre su política salarial y conocer mejor a sus empleados. El objetivo es, por tanto, tener una visión completa y resumida de la situación de los empleados.

Esta empresa de TI incluye las cuatro áreas siguientes:

  • Técnico

  • Funcional

  • Negocio

  • Funciones transversales

Cada empleado se asocia a un área y tiene un rango (que va desde el rango R1, el más bajo, hasta el rango R12, el más alto). La combinación de un rango y una especialidad define una posición. Por ejemplo, un empleado con un rango R5 en el área Funcional tiene la posición Consultor funcional júnior.

Los empleados se identifican solo por la combinación de los valores Apellido, Nombre y Fecha de nacimiento, que no está repetida.

El objetivo de este ejemplo será combinar los diferentes datos presentes en el libro de trabajo con el fin de obtener información más relevante sobre los empleados y representaciones visuales de los datos.

Información general sobre el libro

El libro del ejemplo se divide en tres hojas:

  • La primera hoja, Empleado, contiene la información de los empleados del año N.

Columna

Campo

Descripción

A

Nombre

Nombre del empleado.

B

Apellido

Apellido del empleado.

C

Sexo

Sexo del empleado: M para masculino, F para femenino.

D

Fecha de nacimiento

Fecha de nacimiento en formato DD/MM/AAAA.

E

Rango

Rango expresado como coeficiente comprendido entre R1 y R12.

F

Área

Área de los empleados dentro de la empresa: funcional, técnico, negocio y funciones transversales.

G

Salario bruto anual

Salario del empleado expresado como importe bruto anual.

H

Prima del año en curso

Bonificación recibida por el empleado durante el año en curso.

I

Antigüedad

El período transcurrido, expresado en años, que un empleado lleva trabajando dentro de una empresa hasta el año N.

  • La hoja Áreas incluye el cargo asociado con un rango y un área. Tiene la forma de una tabla de Excel denominada MatrizRango. He aquí la representación de la tabla (rango A1:E13).

Rango

Técnico

Funcional

Funciones transversales

Negocio

R1

Desarrollador principiante

Analista principiante

Agente

Principiante en negocio

R2

Desarrollador júnior

Analista júnior

Agente veterano

Consultor júnior de negocio

R3

Desarrollador

Analista

Agente operacional

Consultor júnior de negocio

R4

Desarrollador veterano

Analista veterano

Agente experto

Consultor júnior de negocio

R5

Consultor técnico júnior

Consultor funcional júnior

Asistente de supervisor

Consultor de negocio

R6

Consultor técnico

Consultor funcional

Supervisor

Consultor veterano de negocio

R7

Consultor técnico sénior...

Fórmulas avanzadas de Excel: conceptos del curso

Lista desplegable en una celda - Validación de datos

Una lista desplegable en una celda permite seleccionar un valor para esa celda de entre una lista de valores propuestos. Esta posibilidad está integrada en la función de validación de datos.

 Para acceder a esta función, seleccione un rango de celdas. En la pestaña Datos, se puede acceder al botón Validación de datos.

images/VBA_003.png

La validación de datos implica que los datos están limitados a un valor o formato específico; se trata de una limitación y no de una ayuda para el usuario.

En el cuadro de herramientas Validación de datos, hay tres pestañas disponibles:

  • Configuración;

  • Mensaje de entrada;

  • Mensaje de error.

images/VBA_004.png
  • La pestaña Configuración permite establecer el formato y/o el valor esperados en la celda. Esto se presenta en tres niveles: el formato esperado, el operador y el valor esperado. Por ejemplo, puede establecer un formato de número entero entre 100 y 200.

images/VBA_005.png

Puede definir la longitud del texto. Por ejemplo, puede limitar la longitud del texto a 10 caracteres:

images/VBA_006.png

La lista desplegable se puede escribir de tres maneras distintas:

  • Introducir una lista de valores sucesivos separados por un punto y coma: Valor1; Valor2; ...; ValorN.

  • Introducir una referencia de celdas =A1:A4 que contenga los valores.

  • Introducir una referencia denominada =MiReferencia que contenga los valores.

images/VBA_007.png

En el campo Origen, no se puede introducir una referencia de celdas en una hoja distinta de la actual, pero es posible omitir esta limitación con una referencia con nombre.

  • La pestaña Mensaje de entrada se refiere a una pequeña ventana asociada a la celda. Incluye un título y un mensaje. Se puede activar cuando la celda está seleccionada o mantenerla permanentemente visible a través de la casilla de verificación Mostrar mensaje de entrada al seleccionar la celda.

images/VBA_008.png

El resultado es el siguiente:

images/VBA_009.png

La pestaña Mensaje de error se utiliza para calificar el mensaje de error que aparecerá si la entrada no es correcta en relación con el formato esperado. Si no se configura, igualmente hay un mensaje predeterminado.

images/VBA_010.png

Cuando se introduce un valor que no coincide con lo esperado, este es el resultado:

images/VBA_011.png

Fórmula de búsqueda

BUSCARH / BUSCARV / BUSCARX

Las funciones BUSCARV y BUSCARH permiten obtener una coincidencia entre dos matrices a partir de sus encabezados con el fin de proporcionar un valor de la matriz de destino.

images/02SOB07-REL.png

La fórmula BUSCARH corresponde a la búsqueda horizontal y permite buscar elementos en una tabla o un rango por columna.

La fórmula BUSCARV corresponde a la búsqueda vertical y permite buscar elementos en una tabla o rango por fila.

La sintaxis es la siguiente:

  • BUSCARH(valor_buscado; matriz_tabla; indicador_filas; [rango])

  • BUSCARV(valor_buscado; matriz_tabla; indicador_columnas; [rango])

  • BUSCARX(valor_buscado; tabla_buscada, tabla_devuelta; [si_no_se_encuentra], [modo_de_coincidencia]; [modo_de_búsqueda])

Los argumentos para estas funciones son los siguientes:

  • El valor buscado (cabecera de las matrices común a ambas tablas).

  • La matriz o el valor se busca en la columna de encabezado (tabla 1).

  • La fila/columna en la que se recupera el valor dentro de la matriz (tabla 1).

  • Valor booleano (VERDADERO o FALSO) para averiguar...

Fórmulas avanzadas de Excel: realizar el ejemplo

Este ejemplo nos permitirá sintetizar la información incluida en la hoja de cálculo Empleado. Utilizaremos la información de las hojas EmpleadoN-1 y Áreas para complementar los datos de la hoja de Empleado.

images/VBA_047.png

 Abra el archivo Enunciado_2-ABC.xlsx, que contiene las hojas Empleado, EmpleadoN-1 y Áreas. Las principales acciones se desarrollan en la hoja EmpleadoN-1.

Datos extraídos de un archivo CSV

Para simplificar el intercambio de datos entre varios sistemas de información, a menudo es útil extraer datos a través de archivos en el formato denominado «CSV» o «Comma-separated-values».

Estos archivos se encuentran en un formato de texto llamado «abierto» cuya particularidad es representar en formato de texto los datos de una tabla.

Cada fila de este archivo corresponde a una fila de la tabla.

Las columnas de este archivo están separadas por separadores que pueden ser, por lo general, coma o punto y coma (versión española).

Para nuestro ejemplo, tenemos archivos CSV con punto y coma como separador.

Asimismo, para la hoja de cálculo con nombre:

  • Empleado: es posible utilizar dos archivos; el archivo titulado Anexo_2-ABC_Empleado_1000.csv corresponde a los datos de una empresa de 1000 empleados, y el titulado Anexo_2-ABC_Empleado_3000.csv, el del ejemplo principal de este curso, con 3000 empleados y cuyas primeras 7 líneas reproducimos a título informativo:

images/VBA_017b.png
  • EmpleadoN-1: también es posible utilizar dos archivos. De hecho, respectivamente, puede elegir entre el primer archivo titulado esta vez Anexo_2-ABC_EmpleadoN1_1000.csv y el segundo, titulado Annexo_2-ABC_EmpleadoN1_3000.csv y cuyas primeras 7 líneas son las siguientes:

images/VBA_018b.png

Estos archivos, con la extensión CSV, son ampliamente utilizados porque siguen siendo compatibles con la mayoría de los programas de gestión (de tipo ERP, CMS para el diseño de sitios web) y ofimática (como una hoja de cálculo de Excel precisamente), y permiten el intercambio de datos intersistemas.

La ventaja de utilizar este tipo de archivos es que, a menudo, se extraen de una exportación de los últimos datos actualizados del sistema de gestión centralizado utilizado en la mayoría de las empresas.

Por lo tanto, es posible copiar esta «exportación en CSV» a Excel con objeto de actualizar la(s) hoja(s) de cálculo utilizada(s) para generar, por ejemplo, tablas dinámicas y gráficos, paneles de mandos u otros... y esto con datos fiables porque están actualizados.

Cómo importar datos desde un archivo CSV

Este es uno de los pasos posibles; a priori el más adecuado para nuestro ejemplo porque no implica ninguna modificación en la configuración del libro de Excel para recuperar los datos.

 Previamente, active la hoja en la que se deben actualizar los datos, que es, en nuestro caso, la hoja Empleado del archivo Enunciado_2-ABC.xlsx.

 A continuación, seleccione solo los datos que desea editar en su futura tabla. Para nuestro caso, pulse simultáneamente la tecla Ctrl y la letra E para seleccionar toda la tabla, ya que aquí se van a modificar todos los datos de esta última.

 Presione la tecla Supr o haga...

Indicadores clave y compartidos: descripción del ejemplo

Descripción general del ejemplo

El objetivo de este ejemplo es partir de la tabla consolidada creada en la primera parte para exponer los indicadores clave. Es interesante, pues, abordar las diferentes funcionalidades para poner el valor las cifras clave de la tabla incluida en la hoja Empleado.

Información general sobre el libro

El archivo Enunciado_2-DEF.xlsx es equivalente al archivo Corrección_2-ABC.xlsx. Este ejemplo es una continuación de la primera parte, por lo que contiene rangos con nombre constantes.

Si prefiere utilizar archivos dinámicos, es decir, con rangos con nombre variables, puede utilizar el archivo Enunciado_2-DEF_3000.xlsx para la versión con 3000 empleados (equivalente al archivo Corrección_2-ABC_3000.xlsx) o el archivo Enunciado_2-DEF_1000.xlsx para la versión con 1000 empleados (equivalente al archivo Corrección_2-ABC_1000.xlsx).

Funciones

En primer lugar, calcularemos las estadísticas salariales:

  • Calcularemos el rango de un salario en relación con todos los salarios.

  • Calcularemos el primer y noveno decil de salarios para identificar el 10 % de los salarios más altos/más bajos.

  • Resaltaremos los salarios más altos usando el formato condicional.

  • Mostraremos en un gráfico resumen todos los salarios.

Indicadores clave y compartidos: conceptos del curso

Funciones de Excel

De forma predeterminada, Excel ofrece muchas funciones estadísticas. Nuestro objetivo no es detallar todas las funciones estadísticas, sino aprender a usarlas.

¿Dónde se encuentran las funciones?

Las funciones se encuentran en la pestaña Fórmulas y están ordenadas por categorías: Usadas recientemente, Financieras, Lógicas, Texto, Fecha y hora, etc.

images/VBA_080.png

Es posible buscar una función a través del botón Insertar función; sin embargo, la búsqueda de funciones rara vez conduce al resultado deseado. Por lo tanto, para insertar una función, se recomienda buscar por tema.

¿Cómo interpretarlas?

Tomemos el ejemplo de una búsqueda vertical:

 Escriba = BUS.

Aparece la lista de funciones:

images/VBA_081.png

 Con las teclas del teclado Flecha arriba y Flecha abajo, colóquese en la función deseada; luego, con la tecla Tab, confirme su selección.

La función se muestra con sus argumentos.

images/VBA_082.png

 En la etiqueta informativa, haga clic en el nombre de la función para mostrar la ayuda en línea, incluidos los argumentos asociados.

images/VBA_083.png

Los argumentos entre corchetes no son obligatorios, como en este ejemplo [rango].

¿Cómo analizarlas?

En la pestaña Fórmulas, el grupo Auditoría de fórmulas se utiliza para analizarlas y, en particular, para identificar posibles problemas:

images/VBA_084.png

Rastrear precedentes/Rastrear dependientes muestra flechas que representan los vínculos entre las celdas implicadas en una fórmula. Quitar flechas borra estas flechas entre celdas.

Mostrar fórmulas muestra la fórmula en lugar de su resultado en la hoja de Excel. Es decir: en lugar de tener el resultado de la suma, se muestra la fórmula =SUMA(A1:A4).

La evaluación de la fórmula a través del botón Evaluar fórmula permite validar los diferentes pasos de una fórmula.

Tomemos, por ejemplo, una fórmula que prueba el valor en A1: aquí es igual a 4.

=SI(A1=1;"El valor es 1";SI(A1=2;"El valor es 2";SI(O(A1=3;A1=4); 
"El valor es 3 o 4";"Otro valor"))) 

Al hacer clic en Evaluar fórmula, esta opción le permite probar todas las condiciones una por una, como en las capturas de pantalla siguientes. Al hacer clic en Evaluar fórmula, se prueba la primera condición A1=1; luego la correspondiente al resultado falso, a saber: A1=2; luego el O(A1=3;A1=4).

images/VBA_085.png
images/VBA_086.png

He aquí el resultado de la fórmula:

images/VBA_087.png

El botón Comprobación...

Indicadores clave y compartidos: realización del ejemplo

 Primero, abra el archivo que ha elegido:

  • Ya sea Enunciado_2-DEF.xlsx para los rangos con nombre constantes.

  • ya sea Enunciado_2-DEF_3000.xlsx o Enunciado_2-DEF_1000.xlsx para archivos dinámicos.

Los archivos Enunciado_2-DEF_3000.xlsx o Enunciado_2-DEF_1000.xlsx son idénticos, pero tienen rangos de datos dinámicos.

Configuración de la tabla

Como primer paso, crearemos una tabla de Excel para facilitar el trabajo con los datos que contiene la hoja Empleado.

 En la hoja Empleado de Enunciado_2-DEF.xlsx o en la hoja BaseN de Enunciado_2-DEF_3000.xlsx o Enunciado_2-DEF_1000.xlsx, vaya a la pestaña Insertar y haga clic en el botón Tabla.

Aparecerá la ventana Crear tabla.

 Seleccione el rango correspondiente a toda la tabla y active la casilla La tabla tiene encabezados

images/VBA_101.png

 Confirme pulsando Aceptar.

Aparece la pestaña Diseño de tabla.

 En el grupo Propiedades, cambie el nombre de la tabla a TablaEmpleado.

images/VBA_102.png

El rango de datos $A$1:$N$3001 o $A$1:$I$3001 para archivos dinámicos ahora es una tabla de Excel llamada TablaEmpleado.

Para actualizar los datos de la hoja BaseN de archivos dinámicos, desde que se inserta una tabla (TablaEmpleado, recordemos) será necesario:

- Seleccionar todas las filas desde la fila 2, pero no seleccionar la primera, para no eliminar TablaEmpleado.

- Eliminar todas las filas seleccionadas, haciendo clic con el botón derecho y eligiendo Eliminar.

A continuación, consulte el procedimiento para importar datos desde un archivo CSV que hemos visto con anterioridad, en la sección Cómo importar datos desde un archivo CSV.

Fórmulas estadísticas

A partir de las fórmulas estadísticas, calcularemos tres indicadores sobre los salarios actuales. 

Conocer el rango salarial

Mostraremos en la columna O la información del rango salarial. Esto corresponde a la posición del importe del salario en relación con el total de los salarios. El salario más alto tendrá el rango 1, el más bajo tendrá el rango 3000.

Para determinar el rango del salario, la función JERARQUIA permite conocer la posición de un valor dentro de un rango. Su uso es relativamente intuitivo:

= JERARQUIA(valor;rango) 

Esta fórmula dará la posición del salario entre los 3000 salarios de los empleados.

Para aplicar esta fórmula:

 Active la hoja Empleado.

 Elimine la protección de la hoja si es necesario para archivos dinámicos (contraseña enivba).

 En la celda O1, escriba Jerarquía como encabezado de columna.

Al hacer esto, la columna se agregará inmediatamente a la tabla TablaEmpleado, excepto para archivos dinámicos, donde solo se actualiza el encabezado.

 En la celda O2, escriba =JERARQUIA y luego elija la celda del salario, H2.

Se mostrará de la siguiente manera: [@[Salario Bruto Anual]]

 En el caso de los archivos dinámicos: deberá elegir el rango denominado EmpleadoColH. Se mostrará así: EmpleadoColH y no [@[Salario Bruto Anual]].

 Escriba ;

 Aún en la fórmula JERARQUÍA, elija el rango (conjunto de celdas) que se tomará para calcular el rango (posición del salario) seleccionando...