Ordenar la tabla dinámica de Excel por porcentaje de recuento

Question

Tengo datos de origen que muestran las aprobaciones de hojas de horas en el siguiente formato (para aproximadamente 850 empleados y 200 gerentes):

  Employee Name  Manager Name  TS Approved? 
  Employee 1     Manager 1     No
  Employee 2     Manager 2     Yes
  Employee 3     Manager 3     Yes
  Employee 4     Manager 1     No
  Employee 5     Manager 3     No

Hice una tabla dinámica de la siguiente manera (el% no aprobado es solo una fórmula que tengo al lado de la tabla dinámica):

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 2      6     10    16     38%
  Manager 3      7     18    25     28%
  Manager 4      5     8     13     38%
  Manager 5      5     4     9      56%
  Manager 6            3     3      0%
  Manager 7      5           5      100%

Necesito ordenar para obtener los 5 peores aprobadores por recuento, pero solo 5. Mis problemas son:

• Si uso la tabla dinámica 'Top 10' en la columna 'No', mostrará 6 valores ya que no diferencia entre los tres 5
• Intenté agregar el porcentaje para poder ordenar el más grande-el más pequeño en %, luego el más grande-el más pequeño en el conteo, luego simplemente tomar los 5 primeros manualmente, ya que 5/5 (100%) no aprobado es peor que 5/8 (38%) - pero no sé cómo ordenar por%.
• Si lo agrego como una fórmula fuera de la tabla dinámica (como arriba), Excel no me permitirá ordenar la tabla dinámica según esos datos. 'No se puede mover parte de un informe de tabla dinámica...'
• Si agrego los datos para que se muestren como "% del total de la fila principal" en la tabla, todavía solo se ordena según el recuento.

¿Alguien puede pensar cómo puedo hacer que haga lo que quiero, es decir?

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 3      7     18    25     28%
  Manager 2      6     10    16     38%
  Manager 7      5           5      100%
  Manager 5      5     4     9      56%
  Manager 4      5     8     13     38%
  Manager 6            3     3      0%

Nota: Puedo hacerlo con bastante facilidad usando countifs en lugar de una tabla dinámica, pero idealmente quiero el formato de tabla dinámica si es posible.

¡Gracias!

Luisa

Answer 1

Interesante desafío. Algunos de los problemas incluyen:

• Los cálculos de campo no tienen la suficiente flexibilidad para obtener lo que necesitas
• Aunque puede mostrar números como % del total, y parece que puede ordenarlos, en realidad ordena según los números subyacentes.

Tengo una solución que utiliza tablas y tablas dinámicas. Puede que haya una solución más sencilla disponible. Los pasos son (realizados en Excel 2016):

1. Seleccione dentro de sus datos sin procesar. Seleccione la cinta "Insertar" y haga clic en "Tabla"
2. En su nueva tabla, inserte un cálculo para %No aprobado
3. Seleccione la cinta "Diseño" "Herramientas de tabla" y haga clic en "Resumir con tabla dinámica"
4. Construya una tabla dinámica simple con el nombre del administrador como filas y % no aprobado como valores.
5. Ordene los nombres de los administradores en orden descendente por% no aprobado

Aquí hay un ejemplo. El siguiente es un fragmento de 30 filas de "datos sin procesar" similares a los descritos en su pregunta...

Seleccione la cinta "Insertar" y haga clic en "Tabla"...

Obtendrá datos mejor formateados. Seleccione D1, luego el encabezado de la última columna y escriba "%No"; esto crea una nueva columna en la tabla con un nuevo encabezado. En la celda D2, escriba la siguiente fórmula...

=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100

Cuando presionas Enter, se completa automáticamente en la tabla. Esta fórmula hace:

1. IF([@[TS Approved?]]="No",1,0)Si la hoja de horas aprobada es "No", obtenga un valor de 1.
2. COUNTIF([Manager Name],"="&[@[Manager Name]])Determina cuántas veces aparece el administrador de esta fila en la tabla.
3. Resultado de 1 dividido por el resultado de 2 por 100

La tabla ahora se ve así...

Seleccione la cinta "Diseño" "Herramientas de tabla" y haga clic en Resumir con tabla dinámica. Construya la tabla dinámica para que se vea así...

...y ordenarlo...

...para conseguir esto...

Aunque parece que hay muchos pasos para configurarlo, es bastante fácil mantener la tabla y esto mantiene automáticamente la tabla dinámica en mantenimiento.

Answer 2

Puede que esto sea antiguo, pero creo que encontré una solución para este problema.

1. El primer paso es simplemente mostrar el % no aprobado mediante el clic derecho convencional, luego "Mostrar valor como" y luego "% del total de la fila".
2. Luego haga clic en el botón desplegable cerca de "Etiquetas de fila".
3. Elija "Descendente" por "Cuenta TS Aprobada"
4. Seleccione "Más opciones de clasificación" y luego haga clic en "Más opciones" en el cuadro de diálogo.
5. Desmarque la casilla "Ordenar automáticamente cada vez que se actualiza el informe" (Este es el paso clave)
6. Elija "Valores en la columna seleccionada" para que sea la primera celda de la columna "No"
7. Haga clic en Aceptar
8. Para volver a actualizar automáticamente, repita los pasos 2, 4 y 5 pero esta vez marque "Ordenar automáticamente cada vez que se actualiza el informe".

Answer 3

No estoy seguro de por qué, pero me di cuenta de dos cosas durante el desayuno esta mañana...

1. Usar una tabla es bueno, pero quizás solo complica el problema.
2. Aunque calcula su % de no aprobados como el porcentaje de hojas de horas de las que es responsable el administrador, es posible que desee calcularlo como el porcentaje de todas las hojas de horas no aprobadas.

Entonces pensé en publicar una respuesta alternativa.

Al lado de sus datos sin procesar, coloque un encabezado %Noy este cálculo debajo (y complete).

=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100

La fórmula calcula, si esta hoja de horas no está aprobada, el porcentaje de todas las hojas de horas no aprobadas.

Tus datos sin procesar ahora se ven así...

Construya su tabla dinámica y ordene por %No.

Si aún desea que el %No aprobado sea el % de las hojas de horas de las que es responsable el gerente, utilice esta ecuación en la columna D.

=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100