5.2: Habilidades de mesa intermedia

Objetivos de aprendizaje

• Ordenar los datos de la tabla.
• Datos de tabla de clasificación personalizados.
• Aplicar opciones de clasificación de listas personalizadas.
• Filtrar datos de tablas usando filtros de criterios.
• Utilice la opción Filtro avanzado para filtrar los datos de la tabla.
• Analizar datos con tablas dinámicas y subtotales.

HABILIDADES DE MESA INTERMEDIA

Ordenar, filtrar y analizar datos con tablas dinámicas y subtotales

Clasificar

La clasificación es una de las herramientas más comunes para la gestión de datos. Al organizar los datos secuencialmente, la información se vuelve más significativa. La disposición de los registros en una secuencia específica se llama clasificación. Si ordenas por una columna esto se considera un solo tipo. Si necesita ordenar por más de una columna, esto se considera una clasificación personalizada.

El campo o los campos que seleccione para ordenar se denominan claves de ordenación. En Excel, puedes ordenar tu tabla por orden ascendente o descendente. Los datos en orden ascendente aparecen de menor a mayor, de más temprano a más reciente o alfabéticamente de la A a la Z. Los datos en orden descendente están ordenados por mayor a menor, más reciente a más temprana, o alfabéticamente de Z a A.

Excel ordenará un rango de datos que no esté en una tabla. Sin embargo, cuando se trabaja con grandes conjuntos de información, es aconsejable hacer de los datos una tabla para la integridad. Excel bloquea la fila de información creando un registro, así cuando se ordena, el registro permanece intacto, solo reorganizado. Por ejemplo, cuando ordena la tabla por apellido, todos los registros de cada fila se mueven juntos. Siempre es una buena idea guardar una copia de tu hoja de trabajo antes de aplicar las clases.

Hay varios lugares que puedes encontrar y usar herramientas de clasificación:

• Cuando crea una tabla por primera vez, Excel habilita automáticamente los botones de Autofiltro; una herramienta utilizada para ordenar, consultar y filtrar los registros de una tabla. Los botones de filtro aparecen a la derecha de los encabezados de columna. Al hacer clic en el botón de filtro, las opciones de clasificación aparecen en las opciones del menú.

Figura 5.23 Botones de Autofiltro

• En la pestaña Inicio, en el grupo Edición, haga clic en el botón 'Ordenar y filtrar' y luego haga clic en una de las opciones de clasificación del menú Ordenar y filtrar.

Figura 5.24 Menú de clasificación y filtro

• Desde la pestaña Datos, use los botones 'Ordenar de A a Z' o 'Ordenar de Z a A' o para múltiples niveles seleccione el botón Ordenar para abrir el diálogo Ordenar personalizado.

Figura 5.25 Opciones de clasificación de la ficha Datos

• Haga clic con el botón derecho en cualquier lugar de una tabla y, a continuación, seleccione Ordenar en el menú contextual para mostrar el submenú Ordenar.

Figura 5.26 Menú con el botón derecho

Completa una clasificación de un solo nivel siguiendo los pasos:

1. En el encabezado EmployeeID, haga clic en el botón de filtro.
2. Elija Ordenar de menor a mayor.

Usuarios de Mac: Haga clic en el botón Ascendente A-Z

Aviso Excel organiza en orden cronológico todos los datos de los empleados con base en el número de EmployeeID, sin embargo manteniendo cada registro juntos. También notará que el botón de filtro ahora muestra una flecha hacia arriba que denota una ordenación ascendente.

Figura 5.27 EmpleadoID Ordenar

Los siguientes pasos ordenarán los registros en orden descendente por Salario Actual utilizando la opción 'Ordenar de más grande a más pequeño' del botón de filtro.

1. Haga clic en el botón de filtro ubicado en el rubro Salario Actual.
2. Elija la opción Ordenar de mayor a menor en el menú.

Usuarios de Mac: haga clic en el botón “Descendente”

Observe que el tipo original ha sido anulado, y la información ahora está organizada con base en el mayor Salario Actual. Verás que la flecha pequeña en el filtro EmployeeID se ha ido, y una flecha que apunta hacia abajo para Orden descendente es visible en el botón de filtro Salario Actual.

Figura 5.28 Salario Actual Ordenar

Actualización de habilidades

Ordenar una columna

1. Haga clic en el filtro Haga clic en la flecha a la derecha del encabezado en la columna que desea ordenar.
2. Haga clic en la opción AZ ↑ o ZA↓ para ordenar sus datos por esa columna.

ORDENAR PERSONALIZ

Cuando necesites ordenar por más de un nivel, debes usar la opción Ordenar por encargo. Complete los siguientes pasos para organizar los datos por Tienda, Apellido, Salario Actual, todo en Orden Ascendente (A-Z).

1. Seleccione la pestaña Datos y haga clic en el botón Ordenar. Observe que la última columna ordenada por está listada. Cambie el nombre del encabezado de la columna desplegando el menú Ordenar por y seleccione Tienda.
2. Haga clic en Agregar nivel.

Usuarios de Mac: haz clic en el símbolo +

3. Haga clic en la flecha hacia abajo en la sección Luego por, y elija los nombres de encabezado de columna como se muestra a continuación en la Figura 5.29. Nota para hacer clic en Agregar nivel para agregar el siguiente encabezado de columna. El orden en que seleccione los encabezados determinará cómo se ordena la información de la tabla.

Figura 5.29 Cuadro de diálogo Ordenar

4. Una vez que seleccione Ordenar por encabezamientos de columna, elija el Ordenar seleccionando ordenar en orden ascendente (A-Z) para los campos Tienda y Apellido, y Menor a Mayor, para el campo Salario Actual.
5. Haga clic en Aceptar.

Observe que la información ahora está ordenada por tres niveles, por Tienda, cada empleado está organizado por Apellido, y Salario Actual en orden ascendente (de menor a mayor). Cada uno de los botones de filtro indica el orden con la flecha hacia arriba.

Figura 5.30 Visual de clasificación personalizada

Actualización de habilidades

Clasificación personalizada (clasificación de varios niveles)

1. Seleccione la pestaña Datos y haga clic en el botón Ordenar.
2. Elija Agregar nivel.
3. Haga clic en la flecha hacia abajo en el campo Columna y elija el encabezado de columna por el que desea ordenar.
4. Repita los pasos anteriores para agregar otro nivel y seleccione el siguiente encabezado de columna para ordenar.
5. El orden en que seleccione los encabezados determinará cómo se ordena la información de la tabla.

LISTA PERSONALIZADA

Al ordenar se pueden crear listas personalizadas que permitan ordenar por características que no ordenen alfabéticamente. Ejemplo, elementos de texto como alto, medio y bajo, o S, M, L, XL. Las fechas suelen requerir listas personalizadas para que pueda variar en la forma en que se ordenan los datos por días de la semana o meses del año.

En nuestro caso, queremos crear una lista personalizada que ordene nuestras tiendas, que no lo es, en orden ascendente o descendente. A la oficina de recursos humanos le gusta ordenar las tiendas en función del tamaño de la ubicación. La sede de la compañía se encuentra en Seattle y emplea a la mayor cantidad de personas. La siguiente ubicación más grande es San Diego etc. Siga los pasos a continuación para crear una lista personalizada ordenando las tiendas como se muestra a continuación:

Seattle

San Diego

Portland

San Francisco

 

Usuarios de Mac: Los pasos para crear una lista de clasificación personalizada son diferentes para Excel para Mac. Omita los pasos a continuación y siga los pasos alternativos a continuación Figura 5.34.

Siga los pasos a continuación para crear un pedido de lista personalizado:

1. Al hacer clic en la tabla, elija la pestaña Datos y haga clic en el botón Ordenar.
2. En la fila Ordenar por, haga clic en el menú desplegable en la columna Orden para el encabezado Tienda. Elija Lista Personalizada.

Figura 5.31 Cuadro de diálogo Lista personalizada

3. Haga clic en el cuadro Entradas de la lista: y escriba Seattle, y presione enter. Escriba el resto de las ubicaciones que se muestran en la Figura 5.32, presionando enter después de cada ubicación de tienda escrita. Una vez ingresadas todas las ubicaciones, haga clic en Agregar. Entonces elige Ok.

Figura 5.32 Cuadro de diálogo Entradas de lista personalizada

4. Verás la actualización de orden de la tienda. Haga clic en Aceptar para cerrar el cuadro de diálogo Ordenar.

Figura 5.33 Sort_Dialogue Box Orden de lista personalizada

Se aplica la clasificación personalizada y la tabla ahora se ordena por Tienda, usando el orden personalizado, luego el Apellido del empleado y luego por la columna Salario Actual.

Figura 5.34 Lista personalizada Ordenar Visual

Los usuarios de Mac alternan los pasos para crear una lista de clasificación personalizada:

1. Haga clic en la opción de menú Excel y elija Preferencias
2. Haga clic en el botón Lista personalizada
3. Escriba la lista de ciudades en el cuadro “Entradas de lista” como se muestra en la Figura 5.32 anterior, luego haga clic en el botón Agregar y cierre el cuadro de diálogo Lista personalizada
4. Haga clic en cualquier parte de la tabla y, a continuación, en la ficha Datos y haga clic en el botón Ordenar
5. Haga clic en el menú desplegable en la columna de pedidos para el encabezado Tienda. Elegir lista personalizada
6. Haga clic en la lista personalizada de ciudades que acaba de crear y luego haga clic en el botón Aceptar dos veces
7. Se aplica la clasificación personalizada y la tabla ahora se ordena por Tienda, usando el orden personalizado, luego el Apellido del empleado y luego por la columna Salario Actual. Véase la Figura 5.34 anterior.

Actualización de habilidades

Ordenar Lista Personalizada

1. Seleccione la pestaña Datos y haga clic en el botón Ordenar.
2. Haga clic en el menú desplegable en la columna de orden del campo que necesita crear una lista personalizada.
3. Elija Lista Personalizada.
4. Haga clic en el cuadro Listar entradas y escriba la lista personalizada deseada.
5. Luego haga clic en Agregar.
6. Haga clic en Ok.

FILTRAR DATOS

Si su hoja de trabajo contiene muchos datos, puede ser difícil encontrar información rápidamente. Aplicar Filtros es una manera eficiente y efectiva de mostrar solo la información necesaria. Por lo general, al filtrar está buscando en los datos información específica. En términos generales, estás buscando los datos en base a una pregunta, o en otras palabras, consultando los datos, y devolviendo solo la información que satisface la pregunta. El proceso de filtrado de registros basado en uno o más criterios de filtro se denomina consulta. El filtrado de datos oculta las filas cuyos valores no coinciden con los criterios de búsqueda. La información que no se muestra no se elimina, simplemente se oculta, y se volverá a mostrar quitando el filtro o aplicando un nuevo filtro.

Al igual que ordenar, las opciones de filtro se encuentran en el botón de filtro junto a cada nombre de campo. Al hacer clic en el botón de filtro, puede elegir qué valores en ese campo mostrar, ocultando las filas o registros que no coinciden con ese valor. El filtro le permite elegir mostrar solo aquellos registros que cumplan los criterios especificados, como el color, el número o el texto. En esta situación, los criterios se definen como; una regla lógica por la cual se prueban y eligen los datos.

Por ejemplo, puede filtrar la tabla para mostrar un nombre o elemento específico escribiéndolo en un cuadro de búsqueda. El nombre que seleccionó actúa como criterio para filtrar la tabla, lo que da como resultado que Excel muestre solo aquellos registros que coincidan con el criterio. Las casillas de verificación seleccionadas indican qué elementos aparecerán en la tabla. Por defecto, se seleccionan todos los elementos. Si anula la selección de un elemento del menú de filtro, se elimina del criterio de filtro. Excel no mostrará ningún registro que contenga el elemento sin marcar. Al igual que con las técnicas de ordenación anteriores, puede incluir más de una columna al filtrar haciendo clic en un segundo botón de filtro y tomando decisiones. Después de filtrar los datos, puede copiar, buscar, editar, formatear, trazar o imprimir los datos filtrados sin reorganizarlos ni moverlos.

Figura 5.35 Filtrar Buscar Menú

Complete los siguientes pasos y filtre los datos de acuerdo a cada consulta.

¿Cuántos empleados están en estado de Medio Tiempo (PT)?

1. Haga clic en el botón de filtro en el encabezado de la columna Estado del trabajo.
2. Haga clic en Seleccionar todo para anular la selección de opciones.
3. Haga clic en la casilla PT para mostrar solo los empleados de medio tiempo.
4. De la fila total, en la celda I108, elija la función Contar contar el número de empleados en estado PT.

La respuesta a la pregunta es que actualmente hay 11 empleados en un estado de horario PT. En la fila total se mostrarán los salarios corrientes totales de medio tiempo, y cuál será el incremento salarial proyectado para la ayuda a medio tiempo después de los ajustes de COLA

Figura 5.36 PT Filtro Visual

Uso de filtros de criterios

Los filtros creados se limitan a seleccionar registros para campos que coincidan con un valor específico o conjunto de valores. Para criterios más generales, puede usar filtros de criterios, que son expresiones que involucran fechas y horas, valores numéricos y cadenas de texto. Excel identificará qué criterios filtrar mostrar en función de la información de la columna. Por ejemplo, puedes filtrar los datos de los empleados para mostrar solo aquellos empleados contratados dentro de un rango de fechas específico. Observe los cambios de filtro de criterios a Filtros de fecha. Si estuviéramos viendo la columna Salario Actual, el filtro sería un Filtro de Números.

Usando filtros de criterios, siga los pasos a continuación para buscar empleados que hayan estado en la empresa por un período de tiempo específico.

Identificar a los empleados que hayan estado con la empresa entre 2013-2016.

1. Al hacer clic en la tabla, borre cualquier ordenación o filtro aplicado haciendo clic en la pestaña Datos. En el grupo Ordenar y filtrar elija el botón Borrar.
2. Haga clic en el botón Filtrar en la columna Fecha de contratación. Seleccione Filtros de fecha y elija los criterios Entre.

Usuarios de Mac: desmarca la casilla Seleccionar todo antes de elegir la opción Entre.

Figura 5.37 Menú de filtro de fecha

3. Buscar empleados con fecha de contratación entre 2013, y 2016. En la sección “es posterior o igual a” escriba 1/01/2013, y escribiendo en la sección “es anterior o igual a” tipo de sección 12/31/2016. Luego haga clic en Aceptar.

Usuarios de Mac: las secciones de Excel para Mac simplemente dicen “Después” y “Antes”

Figura 5.38 Filtro de fecha entre cuadro de diálogo

4. Ordenar la tabla filtrada de Más antiguo a más reciente por fecha de contratación.
5. En la sección de fila total, cuente los apellidos de los empleados aplicando la función de conteo en la celda B108.
6. En la fila total, seleccione la celda I108 y elija Ninguno para desactivar la función de conteo en la Columna Estado del Trabajo.

Observe la tabla fila total mostrar 47 empleados contratados entre las fechas especificadas. Estos empleados serán evaluados para un ajuste COLA.

Observe que el botón de filtro muestra un símbolo de filtro y una flecha hacia arriba que indica que la columna está filtrada y ordenada en orden ascendente.

Figura 5.39 Filtro y clasificación de fechas

REBADORAS

Otra forma de filtrar una mesa Excel es con rebanadoras. Las rebanadoras, en términos generales, son botones de filtro visual en los que puede hacer clic para filtrar los datos de la tabla. Las rebanadoras muestran la categoría filtrada actual, lo que facilita la comprensión de qué se muestra exactamente. Por ejemplo, una cortadora para el campo Tienda tendría botones para las ubicaciones de Seattle, San Diego, Portland y San Francisco.

Cuando se seleccionan los botones de corte, los datos se filtran para mostrar solo aquellos registros que coinciden con los criterios. Se pueden seleccionar varios botones al mismo tiempo, y una mesa puede tener múltiples rebanadoras, cada una vinculada a un campo diferente. Cuando se utilizan múltiples rebanadoras, Excel utiliza el operador lógico AND, por lo que los registros filtrados deben cumplir con todos los criterios indicados en la rebanadora. Al seleccionar varios botones en un Slicer, use la tecla Mayús para seleccionar nombres de campo adyacentes. Si los nombres de los campos no son adyacentes, utilice el método de selección no adyacente, presionando el botón CTL y seleccionando los nombres de campo necesarios.

Sigue los pasos a continuación para filtrar la tabla usando los botones visuales de Slicer.

1. Haga clic en el área de la tabla. En la pestaña Datos, elija Borrar para eliminar la clasificación y el filtro actuales aplicados a los datos.
2. Para hacer espacio para los botones de Slicer en la parte superior de la mesa, agregaremos 4 filas entre el título y el área de la mesa. Botón derecho en la celda A3. Elija Insertar. Seleccione Fila completa. Repita estos pasos hasta que el encabezado de la tabla comience en la fila A9.

Los usuarios de Mac deben mantener presionada la tecla CTRL y hacer clic en la celda A3 Después repita hasta que el encabezado de la tabla comience en la fila A9.

Figura 5.40 Filas añadidas

3. Haga clic de nuevo en el área de la tabla. Elija la pestaña Insertar. Haga clic en Rebanadora. Cuando se abra el cuadro de diálogo Insertar rebanadoras, haga clic en los nombres de los campos Almacenar y Estado del trabajo para mostrarlos como rebanadoras. Haga clic en Aceptar.

Figura 5.41 Caja de diálogo de rebanadora

 

4. Mueva y cambie el tamaño de las cajas Slicer para que quepan en el área aproximada de I1:J8 y K1:L8. Asegúrese de que los botones permanezcan visibles. A continuación se muestra un ejemplo visual.

Figura 5.42 Ejemplo de diseño de rebanadora

5. Desde la cortadora Store, haz clic en el botón San Diego. Observe los filtros de datos para mostrar solo los datos de San Diego.
6. Desde la cortadora Estado del Trabajo, haga clic en PT. Observe los filtros de datos para mostrar únicamente los datos de los empleados de PT en San Diego.

Figura 5.43 Solución de rebanadora

7. Regrese a la cortadora Store y elija Seattle y Portland. Tenga en cuenta que se necesita el método de selección no adyacente. Seleccione Seattle primero, luego mantenga presionado el botón Ctrl del teclado y luego seleccione Portland.

Usuarios de Mac: mantenga presionada la tecla Comando, no la tecla Ctrl antes de hacer clic en Portland.

8. Cambie la selección de rebanadora Estado del trabajo a FT.

Los resultados de la tabla muestran que hay 61 empleados de FT en Seattle y Portland. El Incremento Salarial Proyectado tras el ajuste COLA para la región Noroeste es de $150,465.80.

Figura 5.44 Solución de rebanadora no adyacente

FILTROS Avanzados

Los botones de filtro se limitan a combinar campos usando lógica avanzada o criterios complejos. Si los datos que desea filtrar requieren criterios complejos, puede utilizar el cuadro de diálogo Filtro avanzado. El filtro avanzado funciona de manera diferente al comando Filtro de varias maneras importantes:

• Muestra el cuadro de diálogo Filtro avanzado en lugar del menú Autofiltro.
• Escriba los criterios avanzados en un rango de criterios separado en una hoja de trabajo y por encima del rango de celdas o tabla que desea filtrar. Excel utiliza el rango de criterios separados en el cuadro de diálogo Filtro avanzado como fuente para los criterios avanzados.

Por ejemplo, buscó registros para empleados en las oficinas de Seattle y San Diego Y para empleados que trabajan en bases de tiempo completo, Y tienen un salario base entre los siguientes Rangos Salariales:

Figura 5.45 Criterios de filtro avanzados

Para ejecutar los criterios complejos mencionados anteriormente, siga los siguientes pasos:

1. En la hoja EmployeeData, haga clic en la tabla, luego seleccione la pestaña Datos y borre los filtros actuales seleccionando el botón Borrar.
2. Seleccione la pestaña Diseño de Herramientas de Mesa y desactive la Fila Total.
   Usuarios de Mac: simplemente haga clic en la pestaña Tabla y desactive la Fila Total
3. Seleccione la hoja Filtro avanzado. Clic en la celda A10. Ya se ingresaron los criterios mencionados en el ejemplo anterior para este ejercicio de filtro avanzado. A continuación, utilizará un filtro avanzado para copiar los registros que coincidan con estos criterios.
4. En la pestaña Datos, haga clic en el botón Avanzado. Se abre el cuadro de diálogo Filtro avanzado.
5. Haga clic en el botón de opción Copiar a otra ubicación para copiar registros coincidentes del rango de datos.
6. Haga clic en el cuadro Rango de lista para activarlo y luego navegue hasta la hoja EmpleadosDatos, haga clic en la celda A9 y, a continuación, mantenga presionada la tecla CTRL y MAYÚS y la BARRA ESPACIADORA para seleccionar toda la tabla. En el cuadro Rango de lista, verá Employee_DB [#All] en el cuadro de rango de lista.
   Usuarios de Mac: El atajo de teclado de “CTRL, MAYÚS, BARRA ESPACIADORA” no funciona en Exel para Mac. Debe hacer clic en la Celda A9, desplazarse hacia abajo hasta el final de los datos, mantener presionada la tecla Mayús y hacer clic en C ell L112 para seleccionar toda la tabla
7. Haga clic en, o presione la tecla de tabulación, para desplazarse al cuadro Rango de criterios.
8. En la hoja Filtro avanzado, seleccione A6:D8. ¡Verás 'Filtro avanzado'! Los criterios se completan en el cuadro de rango de criterios.
9. Haga clic en, o presione la tecla de tabulación, para desplazarse al cuadro Copiar a y, a continuación, haga clic en la celda A10 para especificar la ubicación para insertar los registros copiados. ¡Verás 'Filtro avanzado'! $A$10 en el cuadro Copiar a rango de criterios.

Figura 5.46 Cuadro de diálogo de filtro avanzado

10. Haga clic en Aceptar para copiar los registros que coincidan con los criterios de filtro avanzados. Guarde su trabajo.

Los resultados de búsqueda avanzada enumeran 7 empleados que cumplen con los criterios. De estos 7 empleados, solo 1 empleado de tiempo completo en San Diego tiene un salario actual entre $70,000 y $80.000 dólares, y 6 empleados de tiempo completo en Seattle tienen un salario actual entre $50,000 y $60.000 dólares.

Figura 5.47 Resultados del filtro avanzado

INSERTAR MESA

 

Revisemos otra distancia para convertir un rango de datos en una tabla.

1. Seleccione la hoja Filtro avanzado y haga clic en la celda A10.
2. En la pestaña Insertar, elija Tabla.
3. Aparecerá el cuadro de diálogo Crear tabla.
4. Asegúrese de que “Mi tabla tiene encabezados” esté seleccionado para que Excel reconozca los encabezados de columna.
5. Haga clic en Aceptar. Excel convierte nuestros datos de búsqueda anticipada en una tabla.
6. Ordene la tabla en orden ascendente (A-Z), por Tienda e ID de empleado, luego Apellido. Sugerencia: Haga clic en la pestaña Datos, haga clic en el botón Ordenar, agregue niveles para los tres campos.
7. Ajuste automático los anchos de columna y el alto de la fila para asegurarse de que la fila de rumbo esté visible.
8. Guarde su trabajo.

Excel convierte la información en una tabla y ordena en consecuencia:

Figura 5.48 Tabla de filtros avanzados

ANALIZAR DATOS DE HOJA DE TRABAJO

Introducción a las mesas pivotes

Otra forma de analizar la información de la tabla es con tablas dinámicas. Una tabla dinámica es una poderosa herramienta que calcula, resume y analiza datos de tablas para comparar, patrones y tendencias. Las tablas dinámicas se insertan directamente desde una tabla, vinculando los datos de la tabla. En términos generales, cuando pivotas sobre los datos de la tabla estás reorganizando la información de la tabla para revelar diferentes niveles de detalle que permiten analizar subgrupos específicos de información y resumir datos rápida y fácilmente sin tener que cambiar la estructura o el diseño del área original de la tabla.

Cuando extrae datos de tabla en una tabla dinámica, hay cuatro campos de área principal: Filas, Columnas, Valores y Filtros. Los campos Filas y Columnas pueden intercambiarse rápidamente para resumir los datos de diferentes maneras o para ejecutar nuevos informes en función de la pregunta o criterios que se formulen. El campo Valor son datos de la tabla que se pueden calcular, o que contienen valores que la tabla dinámica resumirá. El campo Valores tiene múltiples configuraciones para elegir cómo desea calcular los datos; SUMA, COUNT, PROMEDIO, MIN, MAX, e incluso puede mostrar los valores mostrados como un porcentaje del total, total de columna, total general, etc. Por último, es el área Filtros, que restringe la tabla dinámica para mostrar solo los valores que coinciden con los criterios especificados.

Cuatro áreas principales de tabla dinámica:

Figura 5.49 Cuatro áreas principales de tabla dinámica

En nuestra situación, que se muestra a continuación, crearemos una tabla dinámica para resumir los datos de los empleados para mostrar los aumentos salariales proyectados, tanto para empleados de medio tiempo (PT) como para empleados de tiempo completo (FT) para todas las ubicaciones de las tiendas.

Figura 5.50 Partes de una tabla dinámica

Siga los pasos a continuación para explorar y crear un informe de tabla dinámica.

1. Haga clic en la ficha EmpleadoDatos. Haga clic en cualquier parte del área de la tabla.
2. En la pestaña Insertar, elija Tabla dinámica.

Figura 5.51 Insertar tabla dinámica

3. En el cuadro de diálogo Crear tabla dinámica, asegúrese de que el informe de tabla dinámica se colocará en una nueva hoja de trabajo y haga clic en Aceptar.

Figura 5.52 Crear cuadro de diálogo de tabla dinámica

4. Observe que se inserta una nueva hoja (Hoja1), en la parte inferior del libro de trabajo, que contiene el área PivotTable1 y el cuadro de diálogo campos. Cambie el nombre por defecto (Hoja 1) a StorePT.

Figura 5.53 Ventana de tabla dinámica

5. Desde el panel Tabla dinámica, arrastre y suelte el encabezado Tienda a la sección Filas del área de campo Tabla dinámica.

Figura 5.54 Selección de filas de tabla dinámica

6. Desde la lista de campos de tabla dinámica, arrastre y suelte el encabezado Incremento Salarial Proyectado a la sección Valores.

Figura 5.55 Selección de valor de tabla dinámica

7. Arrastre y suelte el encabezado Estado del trabajo a la sección de campo Columnas. Observe la visualización de las categorías Estado del Trabajo. En este caso, mostrando empleados de Tiempo Completo (FT) y Medio Tiempo (PT).

Figura 5.56 Selección de columnas de tabla dinámica

Formateo de tablas dinámicas

Después de crear una tabla dinámica y agregar los campos que desea analizar, es posible que desee mejorar el informe para incluir rebanadoras, o gráficos y/o formatear los datos para que sea más fácil de leer y escanear en busca de detalles. Al hacer clic en el área de tabla dinámica verá aparecer una pestaña contextual en la cinta, que contiene Herramientas de tabla dinámica y dos pestañas específicas; Analizar y Diseñar. Usuarios de Mac: no hay una pestaña “Herramientas de tabla dinámica” pero verás dos pestañas llamadas: Análisis de tabla dinámica y Diseño. Solo son visibles cuando se ha hecho clic dentro del área de tabla dinámica.

La pestaña Analizar contiene herramientas específicamente para examinar datos, por ejemplo, la capacidad de insertar rebanadoras o gráficos dinámicos. La pestaña Diseño contiene herramientas que se refieren específicamente a cómo se muestran visiblemente la tabla y los datos. Por ejemplo, cuando tienes muchos datos en tu tabla dinámica, puede ser útil mostrar filas o columnas con bandas para facilitar el escaneo o resaltar datos importantes para que destaquen.

Siga los pasos a continuación para agregar formato a la tabla dinámica y agregar un gráfico gráfico.

1. Haga clic en la tabla dinámica. En las Herramientas de tabla dinámica, elija la pestaña Diseño.
2. En la galería Estilos de tabla dinámica seleccione el formato Azul Claro, Estilo Pivot Estilo Medio 2.

Figura 5.57 Pivote de estilo medio azul claro 2

3. Para dar formato a los números de la tabla dinámica, seleccione B5: D9. Haga clic en la pestaña Inicio. Aplica el formato de número de moneda y disminuye la posición decimal a cero decimales.

(El método alternativo al formato numérico en una tabla dinámica es expandir el menú en el campo de valor; Suma de Aumento Salarial Proyectado. Haga clic en Configuración del campo de valor. Elija Formato de Número y aplique la opción de formato de número deseada. Los usuarios de Mac deben hacer clic en el círculo pequeño con una “i” junto a “Suma de aumento salarial proyectado” en la sección Valores y luego hacer clic en el botón Número para cambiar el Formato de Número. )

¡AHORA CREEMOS UN PIVOTCHART!

4. Haga clic en la tabla dinámica. Haga clic en la pestaña Analizar. Elija el botón Gráfico PivotChart en la Cinta de opciones.
5. En los tipos de gráficos enumerados, elija Columna. Y seleccione la opción Columna agrupada 3D. Haga clic en Aceptar.

Usuarios de Mac: Solo hay disponible un gráfico básico de columnas 2D al hacer clic en el botón Gráfico Pivot. Para seleccionar un tipo de gráfico diferente, como la opción de columna agrupada 3D, debe hacer lo siguiente:

• • • Haga clic en el gráfico 2D que se acaba de insertar
    • Haga clic en la pestaña Diseño en la cinta
    • Haga clic en el botón Cambiar tipo de gráfico
    • Seleccione la opción Columna agrupada 3D

Figura 5.58 Gráfico 3D Clustered_Column

6. Mueva el gráfico por debajo del área de tabla dinámica. Redimensionar en consecuencia. Guarde su trabajo.

Anote los cambios de formato en el nuevo gráfico a continuación. Los botones “Estado del trabajo” y “Almacenar” son “filtros” de columna y fila para el Gráfico Pivot.

Usuarios de Mac: Excel para Mac no inserta estos cambios de formato dentro de un Gráfico Pivot. Puede agregar un título de gráfico haciendo clic en el botón “Agregar elemento de gráfico” en la pestaña Diseño. No es posible agregar los botones de “filtro de gráfico” como se muestra en la Figura 5.59. Los filtros en la tabla dinámica se pueden usar para filtrar también las columnas y filas en el Gráfico Pivot.

Figura 5.59 Solución de tabla dinámica

SUBTOTALES

Otra forma de resumir los datos es mediante el uso de subtotales. Analizar un amplio rango de datos suele incluir hacer cálculos sobre los datos. Puede resumir los datos aplicando funciones de resumen como COUNT, SUM y PROMEDIO a todo el rango organizado de información. Los subtotales, en general, son funciones de resumen aplicadas a partes de un rango de datos organizado.

Por ejemplo, puedes SUMAR Salarios Vigentes para empleados de cada ubicación de Tienda. Para subtotalizar la información los datos primero deben ser ordenados por el campo Tienda. Para los subtotales, el campo que ordena se denomina campo de control. Por ejemplo, si elige la ubicación de la tienda como su campo de control, todas las entradas de Seattle, San Diego, Portland y San Francisco se agruparán dentro del rango de datos. La función SUMA entonces se puede aplicar a SUMA los campos Salario Actual para cada ubicación de Tienda. Excel calcula y muestra el subtotal cada vez que cambia la ubicación de la tienda.

Se insertará una nueva fila que contenga un subtotal de esa ubicación en particular, y dondequiera que cambie el campo se mostrará un valor; un grupo de subtotales de registros. Excel actualiza el subtotal automáticamente cuando se cambia el campo de control. En teoría, al subtotalizar, está agregando una fila de cálculo al conjunto de datos. Agregar filas que totalicen información en el medio de una tabla comprometería la integridad de los datos en la tabla. Las herramientas de tabla verían el total como un registro, no un cálculo. Por lo tanto, la función Subtotal no se puede usar en tabulación, y solo se puede aplicar a un rango normal de datos. Debe convertir todas las tablas a un rango antes de subtotalizar.

Se pueden aplicar múltiples funciones dentro del mismo Subtotal. Por ejemplo, exploraremos cómo puedes SUMAR los Salarios Vigentes y también proporcionar el Salario Actual PROMEDIO para cada ubicación de Tienda dentro del mismo Subtotal. Nota Los datos del subtotal también pueden ser filtrados.

La mejor práctica al subtotalizar es seguir cuatro reglas:

Figura 5.60 Reglas de subtotal

Siga los pasos a continuación para Subtotalizar los Datos del Empleado y proporcionar un Salario Actual total por Tienda.

1. Seleccione la ficha Datos del empleado. Si es necesario, borre los filtros aplicados a los datos haciendo clic en la pestaña Datos y eligiendo la opción Borrar filtro.
2. En la pestaña Datos, elija el botón Ordenar. Ordene la ubicación de la tienda, usando el orden de lista personalizada preferido de Seattle, San Diego, Portland y San Francisco. Si la lista que configuramos anteriormente no está disponible escriba las entradas en el área de Entradas de lista. Elija Agregar y, a continuación, Aceptar.

Figura 5.61 Clasificación personalizada, Cuadro de diálogo Lista personalizada.

3. Elija la pestaña Diseño de Herramientas de Mesa. Usuarios de Mac: simplemente haga clic en la pestaña “Tabla”.

Seleccione “Convertir a rango”. Excel mostrará un mensaje preguntando si realmente quieres convertir la tabla de nuevo a un rango normal. Elija Sí.

Figura 5.62 Convertir a un rango

4. Haga clic en la pestaña Datos, en el grupo Esquema busque y seleccione el Comando Subtotal. (Observe que la fila de encabezado ya no tiene botones de filtros. Los datos se ven como una tabla pero no es una tabla. Las herramientas de tabla no están activas, y la información es un rango normal.)
5. En el cuadro de diálogo Subtotal, elija el campo Tienda en la sección “En cada cambio en. ” Para la “Función de Uso”, elija Suma, y solo verifique Sueldo Actual. Haga clic en Aceptar.

Figura 5.64 Cuadro de diálogo Subtotal

6. Observe que la columna Salario Actual está totalizada, por ubicación. Guarde su trabajo.

Figura 5.65 Solución subtotal

VISTA DEL SUBTOTAL

Las vistas de esquema, ubicadas en el panel lateral izquierdo, muestran estadísticas resumidas. La herramienta Contorno, con niveles, permite controlar la extensión de detalle que se muestra en la hoja de trabajo. La hoja de trabajo EmployeeData tiene tres niveles en el esquema de su rango de datos:

• Nivel 1, muestra solo los totales generales.
• Nivel 2, muestra el total gastado en cada Tienda.
• Nivel 3, muestra el salario total.

La Figura 5.66 anterior muestra el Esquema de Nivel 3, todos los detalles del empleado por ubicación de tienda. Al hacer clic en los botones de esquema ubicados a la izquierda de los números de fila, puede elegir la cantidad de detalles que desea ver en la hoja de trabajo. (Tenga en cuenta que los tres números de nivel están en la parte superior izquierda de la hoja de trabajo, justo debajo del cuadro Nombre).

Utilizará los botones de esquema para expandir y contraer diferentes secciones del rango de datos.

1. Haga clic en el nivel 1. Observe que muestra el Gran Total.
2. Haga clic en el nivel 2. Observe que se muestran los totales de todas las ubicaciones de las tiendas.

Figura 5.66 Subtotal Esquema Nivel 2

SUMANDO UN SUBTOTAL DENTRO DE UN

Como se mencionó al inicio de la sección, se pueden utilizar múltiples funciones dentro del mismo subtotal. Ahora exploraremos cómo puedes SUMAR los Salarios Vigentes y también proporcionar el Salario Actual Promedio para cada ubicación de Tienda dentro del mismo Subtotal.

1. Haga clic dentro de los datos del Subtotal, vaya al Esquema, haga clic en Nivel 3, para mostrar todos los datos del subtotal.
2. En la pestaña Datos y haga clic en Subtotal.
3. En el cuadro de diálogo Subtotal, seleccione el campo Tienda para la opción “En cada cambio en:”.
4. En la sección “Usar función:” seleccione para mostrar el Promedio.
5. Solo consulta el campo Sueldo Actual en la sección “Agregar subtotal a la sección:”. (Nota Excel comprobará por defecto algo en esta área. Desmarca cualquier otro campo.)
6. Desmarque “Reemplazar subtotales actuales”; no queremos reemplazar el subtotal actual sumando el Salario Actual.
7. Haga clic en Aceptar

Gráfico 5.67 Subtotal Dentro de Un Subtotal

8. Observe que cada ubicación está ahora subtotalizada mostrando el Salario Promedio y Total Actual. Excel también ha agregado 4to nivel al Esquema, contabilizando los Promedios. Guarde su trabajo.

Figura 5.68 Solución, subtotal dentro de un subtotal Principales conclusiones

Claves para llevar

• Una tabla se compone de un conjunto de datos que se organiza en columnas y filas que representan campos y registros, como la información de los empleados.
• Puede crear una tabla haciendo clic en dar formato al conjunto de datos como una tabla o utilizando la función Insertar tabla.
• Excel ofrece estilos de mesa preconstruidos y opciones para elegir para formatear una tabla.
• Puede agregar registros (filas) y nuestros campos (columnas) a una tabla. Luego puede ordenar para reorganizar sus datos.
• El encabezado congelado mantiene los encabezados de columna mostrados mientras se desplaza por los datos de su tabla.
• Puede utilizar las flechas de filtro en los encabezados de la tabla para ordenar por una sola columna. Al ordenar por más de un campo, utilice la opción Ordenar por encargo.
• Las clasificaciones de listas personalizadas se pueden usar cuando un campo necesita ser ordenado de una manera especial.
• Un slicer es un botón de filtro visual (objeto) que se utiliza para filtrar datos en una tabla de Excel. Cada valor único en el campo es un botón.
• Una tabla dinámica es una tabla interactiva que resume los datos de una fuente de datos, como un rango de datos o una tabla de Excel.
• La herramienta Subtotal incluye estadísticas resumidas para cada grupo de registros. Excel organiza subtotales utilizando un esquema que se puede expandir o contratar para ver u ocultar detalles sobre los datos.

“5.2 habilidades de mesa intermedia” por Hallie Puncochar, Portland Community College está licenciado bajo CC BY 4.0