[Tablas dinamicas]: Un caso práctico

Había una vez una empresa de nombre Meteored en la que el encargado de Recursos Humanos (llamémoslo Ortíz) me lanzó su "Ortíz challenge":

Dada la siguiente lista de cientos de filas, en la cual están registradas todas las veces en la que los empleados pasan su access card por el scanner de cualquiera de las puertas de la empresa, en todo el mes, Ortíz requería saber si se podía extraer un resumen con las horas de entrada y salida de cada día del mes, de cada empleado, para determinar el número de retardos de cada uno.

Primeras 20 filas de la base de datos de origen

 ¿Cómo procederías tú? ¿Qué harías? (Una pista, en el título de la nota).

Antes de proceder con la solución, cabe recordar que para Excel las fechas y horas son en realidad números con formato. Las fechas son números enteros y las horas son números decimales. Por ejemplo, las 12:00 horas son en realidad un valor 0.5 (es decir, la mitad del día) con formato de horas; análogamente, las 18:00 son un 0.75 con formato de horas, las 06:00 son un 0.25 and so on. En esta nota  y en esta vimos más a detalle este tema.

Ok, veamos cómo se soluciona. Si observamos nuevamente la base de datos de Ortíz, veremos que se trata de una tabla de datos organizados en columnas, sin ninguna clase de resumen ni encabezados de fila. Solo datos y encabezados de columna. Esto la vuelve una candidata perfecta para ser utilizada en una tabla dinámica. Entonces, rápidamente vamos a la pestaña Insertar, Tabla dinámica, Aceptar. Como deseamos un resumen por empleado y por fecha, movemos los campos de Apellidos y Nombre al área de filas y el campo Fecha al área de columnas. Luego, con la tabla dinámica seleccionada, vamos a la pestaña Diseño, Diseño de informe, Mostrar en formato tabular. En seguida volvemos a dar clic derecho y desactivamos Subtotal "Apellido". Nos queda así:

Diseño inicial de la tabla dinámica

¿Y las horas de entrada y salida...?

Como ya sabemos que las horas son un número decimal de cada día, es válido suponer que el valor mínimo de cada día corresponde a la hora de entrada y el valor máximo a la hora de salida. Con esto en mente, agregamos el campo Hora al área de Valores.

Hemos agregado el campo Hora al área de valores

Vemos que por default Excel agrega el campo resumido con la operación Suma, lo cual en este caso no nos sirve, ya que queremos que nos devuelva el valor mínimo. Por lo tanto damos clic derecho sobre el área de Valores, Configuración de campo de valor..., Operación MÍN (puede venir como Resumir campo de valor por:, Mín.). Aprovechamos para cambiar el Nombre del campo (o Nombre del origen) a "Entrada".

Luego, le damos clic al botón Configuración de campo de valor..., Formato de número, Hora, Aceptar, Aceptar.

Ya va tomando forma la tabla. Para agregar ahora la hora de salida, vamos a la lista de campos de la tabla y volvemos a arrastrar el campo Hora al área de datos. A continuación aplicamos una configuración similar a este campo, pero ahora aplicando operación MAX y como Nombre de campo, "Salida".

Finalmente quitamos las columnas de total de columnas y total general ya que no son de interés (por esta ocasión). Para ello damos clic derecho sobre la tabla dinámica, Opciones de tabla dinámica, pestaña Totales y filtros y desactivamos las dos primeras opciones, Aceptar.

El resultado final:

Prueba superada

Y así es como superamos el reto. Si el lector desea poner en práctica lo aprendido en esta nota, en este enlace puede descargar una base de datos similar a la que utilicé.

Por último, comentarles que ya estaré incursionando en YouTube. En el primer vídeo del canal está la resolución completa del presente ejercicio para quienes hayan quedado con alguna duda:

Saludos.

  

Cómo crear una encuesta online con Excel

Hoy veremos cómo crear una encuesta en Excel.

Lo primero que tenemos que hacer es ingresar a nuestra cuenta de OneDrive, o bien a cualquier producto de Microsoft y luego desde ahí a OneDrive. En mi caso primero ingresé a mi cuenta de Outlook.com, luego fui al botón de aplicaciones (el de los nueve puntos, en la esquina superior izquierda) y clic en OneDrive:

Ingresando a OneDrive

Una vez en nuestra cuenta de OneDrive, entramos al menú de aplicación y seleccionamos Files (Archivos), luego damos clic en el botón New (Nuevo) y seleccionamos Excel Survey (Encuesta Excel).

Creamos una nueva encuesta en Excel

Con esto se abrirá la versión online de Excel así como un sencillo asistente para editar nuestra encuesta. En primer lugar ingresamos un título y una descripción de la encuesta, y escribimos la primera pregunta de la misma:

Ingresando nombre y descripción a la encuesta

Con cada pregunta que agreguemos se abrirá un cuadro de diálogo a la derecha para editar y configurar la pregunta:

Aquí configuramos la primera pregunta

En la opción Response type (tipo de respuesta) vemos que Excel nos ofrece siete opciones: Texto, Párrafo de texto, Número, Sí/No, Fecha, Hora y Opción múltiple, de las cuales seleccionaremos la que mejor se adapte a nuestras necesidades, cada una con sus propias opciones de formato. Por ejemplo, si seleccionamos el tipo Número, en el cuadro Formato aparecerán las opciones de Número fijo de decimales, Porcentaje o Contabilidad:

Configurando una respuesta de tipo numérica

Pero si seleccionamos el tipo Opción múltiple o Choice (la última opción), en vez del cuadro de diálogo Formato nos aparecerá una lista donde tendremos que ingresar las diferentes opciones de respuesta entre las que podrá elegir el entrevistado.

Para agregar otra pregunta damos clic en Add New Question. Al terminar de ingresar todas nuestras preguntas podemos echar un vistazo previo antes de compartir, dando clic en Save and View; o bien, podemos compartir inmediatamente la encuesta dando clic en Share Survey. Si optamos por lo primero:

Cómo abrir la vista previa

Veremos lo mismo que verán todas las personas a quienes les compartamos la encuesta. Si todo está correcto, ya podemos compartir nuestra Excel Survey dando clic en Share survey. O podemos regresar y volver a editar clicando Edit Survey:

Vista previa

Al compartir, Excel nos generará automáticamente un link que podemos compartir por cualquier medio (WhattsApp, e-mail, etc.), que llevará a todos nuestros destinatarios a la encuesta; es decir, les abrirá la ventana que vimos en la imagen anterior.

Este link lo podemos compartir de cualquier manera

Lo más interesante es que en forma paralela esta funcionalidad nos generará un archivo en formato Excel el cual se irá llenando automágicamente con las respuestas de cada uno de los destinatarios (importante considerar si incluiremos o no una pregunta para obtener el nombre o ID de cada participante; si la omitimos recibiremos respuestas anónimas). Este archivo estará almacenado en nuestros archivos de OneDrive; para abrirlo simplemente le damos doble clic.
Si posteriormente queremos volver a editar la encuesta, desde la carpeta de OneDrive le damos clic derecho, Survey, Edit survey:

Cómo editar la encuesta desde la carpeta de archivos de OneDrive

Existen muchas situaciones donde puede ser útil el uso de encuestas. Por ejemplo, un capacitador puede distribuir preguntas después o al final de cada clase para comprobar el aprendizaje de los alumnos. Un encargado de Control de calidad puede comprobar la satisfacción de sus clientes, incluso de manera anónima, simplemente sin preguntar nombres o ID. Mas, como hemos visto, no es necesario (casi nunca) recurrir a aplicaciones de terceros...

Excel, siempre Excel.

¡Saludos!

Retomando el blog

Pues sí. Casi sin darme cuenta han pasado ya ¡siete! años y dos nuevas versiones de Excel desde mi última publicación en este blog, en la cual vimos una cuestión sobre la validación de datos. En ese entonces me escribían muchos estudiantes que ahora ya serán todos unos profesionistas hechos y derechos...

Todavía recuerdo el "trauma" que nos hizo pasar Microsoft con la nueva y revolucionaria interfase de la versión 2010, dejando en el olvido a los clásicos menús de 2003. (Sí, el cambio fue comenzó con 2007, pero nadie compró esa versión ¿correcto?).

También en estos años me encontré con un pirata que copió el nombre de Excel Total para su propia página, a pesar de que varias veces le solicité de buena manera que lo cambiara, pues yo lo vengo utilizando desde 2008 con licencia de Creative Commons. Así pues... que Dios se lo pague.

En fin, comencemos de nuevo con las publicaciones.

¡Saludos!

Validación de datos dependiente

El problema es el siguiente: Dada una lista de validación, queremos limitar los valores mostrados en ella, dependiendo del valor de otra celda. Es decir, si en la celda A2 selecciono la categoría Frutas, quiero que solo aparezcan frutas en la lista desplegable de B2:



... pero si selecciono Verduras en A2, la lista de B2 solo debe mostrar verduras. Una forma de lograrlo es combinando la validación de datos con la función INDIRECTO y un par de nombres. Comenzamos nombrando nuestra lista principal. En una zona vacía de la hoja escribimos los valores que deben aparecer en la lista ("Frutas" y "Verduras"), seleccionamos ambos valores (sin el encabezado) y escribimos "categorias" en el cuadro de nombres (a la izquierda de la barra de fórmulas). Enter.



Ahora definimos nuestras listas auxiliares, cuyos encabezados deben ser exactamente iguales a los valores de la primera lista.

 

Escribimos los valores de cada lista y definimos los nombres Frutas y Verduras de la misma forma que hicimos con las categorías.

 

Procedemos ahora con la validación en sí. Selecionamos las celdas en las que escribiremos las categorías, vamos a Datos - Validación... Permitir: Lista y en en cuadro Origen escribimos la fórmula: =categorias. Aceptar.



Solo nos falta crear la validación dependiente. Seleccionamos las celdas donde aplicaremos ésta, nuevamente vamos a Datos - Validación... Permitir: Lista y en el cuadro Origen escribimos el signo igual y la función INDIRECTO, referenciándola a la primera celda de la columna de Categorías, A2 en este caso: =INDIRECTO(A2). Aceptar.



En este punto, si la celda A2 está vacía veremos el siguiente mensaje:



Damos clic en Sí para continuar.

Probemos el funcionamiento de las listas. En A2 seleccionemos el valor Frutas, y en B2 demos clic en la flecha de la lista desplegable. Si hicimos todo correctamente, la lista mostrará los valores Fresa, Manzana, Sandía, Pera y Kiwis, como en la primera imagen.