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

Descripción general del ejemplo

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:

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:

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.

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:

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

La lista desplegable se puede escribir de tres maneras distintas:

El resultado es el siguiente:

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.

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

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.

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:

Los argumentos para estas funciones son los siguientes:

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:

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.

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.

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:

Aparece la lista de funciones:

La función se muestra con sus argumentos.

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:

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).

He aquí el resultado de la fórmula:

El botón Comprobación...

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.

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.

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: