Sortieren Sie die Excel-Pivot-Tabelle nach dem Prozentsatz der Anzahl

Question

Ich habe Quelldaten, die die Genehmigungen von Arbeitszeitnachweisen im folgenden Format zeigen (für etwa 850 Mitarbeiter und 200 Manager):

  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

Ich habe eine Pivot-Tabelle wie folgt erstellt (der Prozentsatz nicht genehmigter Fälle ist lediglich eine Formel, die ich neben der Pivot-Tabelle habe):

                 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%

Ich muss sortieren, um die fünf schlechtesten Genehmiger nach Anzahl zu erhalten – aber nur fünf. Meine Probleme sind:

• Wenn ich die Pivot-Tabelle „Top 10“ in der Spalte „Nein“ verwende, werden 6 Werte angezeigt, da nicht zwischen den drei 5er-Werten unterschieden wird.
• Ich habe versucht, den Prozentsatz hinzuzufügen, damit ich nach „Größte-Kleinste“ nach % sortieren konnte, dann nach „Größte-Kleinste“ nach Anzahl und dann einfach die obersten 5 manuell nehmen konnte – da 5/5 (100 %) nicht genehmigt schlechter ist als 5/8 (38 %) – weiß aber nicht, wie ich nach % sortieren soll.
• Wenn ich es als Formel außerhalb der Pivot-Tabelle hinzufüge (wie oben), kann ich die Pivot-Tabelle in Excel nicht anhand dieser Daten sortieren. „Sie können Teile eines Pivot-Tabellenberichts nicht verschieben …“
• Wenn ich die Daten so hinzufüge, dass sie in der Tabelle als „% der Summe der übergeordneten Zeilen“ angezeigt werden, werden sie trotzdem nur nach der Anzahl sortiert.

Kann sich jemand vorstellen, wie ich es dazu bringen kann, das zu tun, was ich will?

                 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%

Hinweis: Ich kann dies ganz einfach mit „Zählenwenn“ statt mit einer Pivot-Tabelle tun, möchte aber idealerweise, wenn möglich, das Pivot-Tabellenformat.

Danke schön!

Louise

Answer 1

Interessante Herausforderung. Einige der Probleme sind:

• Feldberechnungen bieten nicht genügend Flexibilität, um das zu erreichen, was Sie benötigen
• Obwohl Sie Zahlen als Prozentsatz der Gesamtsumme anzeigen können und es so aussieht, als ob Sie danach sortieren könnten, erfolgt die Sortierung tatsächlich nach den zugrunde liegenden Zahlen.

Ich habe eine Lösung, die Tabellen und Pivot-Tabellen verwendet. Möglicherweise gibt es eine einfachere Lösung. Die Schritte sind (durchgeführt in Excel 2016):

1. Wählen Sie Ihre Rohdaten aus. Wählen Sie das Menüband „Einfügen“ und klicken Sie auf „Tabelle“.
2. Fügen Sie in Ihrer neuen Tabelle eine Berechnung für %NotApproved ein.
3. Wählen Sie das Menüband „Tabellentools“ „Entwurf“ und klicken Sie auf „Mit Pivot-Tabelle zusammenfassen“.
4. Erstellen Sie eine einfache Pivot-Tabelle mit dem Managernamen als Zeilen und „%NotApproved“ als Werten.
5. Sortieren Sie die Managernamen in absteigender Reihenfolge nach %NotApproved

Hier ist ein Beispiel. Das Folgende ist ein Ausschnitt aus 30 Zeilen „Rohdaten“, ähnlich wie in Ihrer Frage beschrieben ...

Wählen Sie das Menüband „Einfügen“ und klicken Sie auf „Tabelle“ ...

Sie erhalten besser formatierte Daten. Wählen Sie D1, dann die letzte Spaltenüberschrift und geben Sie „%No“ ein – dadurch wird eine neue Spalte in der Tabelle mit einer neuen Überschrift erstellt. Geben Sie in Zelle D2 die folgende Formel ein ...

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

Wenn Sie die Eingabetaste drücken, wird es automatisch in die Tabelle eingetragen. Diese Formel bewirkt Folgendes:

1. IF([@[TS Approved?]]="No",1,0)Wenn das genehmigte Stundenkonto „Nein“ lautet, erhalten Sie den Wert 1.
2. COUNTIF([Manager Name],"="&[@[Manager Name]])Bestimmt, wie oft der Manager in dieser Zeile in der Tabelle vorkommt.
3. Ergebnis aus 1 geteilt durch das Ergebnis aus 2 mal 100

Die Tabelle sieht jetzt so aus ...

Wählen Sie „Tabellentools“, „Entwurf“ im Menüband und klicken Sie auf „Mit Pivot-Tabelle zusammenfassen“. Erstellen Sie die Pivot-Tabelle so, dass sie wie folgt aussieht …

... und sortiere es ...

... um dies zu bekommen ...

Obwohl die Einrichtung scheinbar viele Schritte umfasst, ist die Wartung der Tabelle ziemlich einfach und die Pivot-Tabelle wird automatisch gepflegt.

Answer 2

Das ist vielleicht schon alt, aber ich glaube, ich habe eine Lösung für dieses Problem gefunden.

1. Der erste Schritt besteht darin, einfach den Prozentsatz nicht genehmigter Einträge mit einem herkömmlichen Rechtsklick anzuzeigen, dann auf „Wert anzeigen als“ und dann auf „% der Zeilensumme“.
2. Klicken Sie dann auf die Dropdown-Schaltfläche neben „Zeilenbeschriftungen“.
3. Wählen Sie „Absteigend“ nach „Anzahl der genehmigten TS“
4. Wählen Sie „Weitere Sortieroptionen“ und klicken Sie dann im Dialogfeld auf „Weitere Optionen“.
5. Entfernen Sie das Häkchen aus dem Kontrollkästchen „Bei jeder Aktualisierung des Berichts automatisch sortieren“ (Das ist der entscheidende Schritt)
6. Wählen Sie "Werte in ausgewählter Spalte" als erste Zelle der Spalte "Nein"
7. OK klicken
8. Um eine automatische erneute Aktualisierung durchzuführen, wiederholen Sie die Schritte 2, 4 und 5, aktivieren Sie dieses Mal jedoch „Bei jeder Aktualisierung des Berichts automatisch sortieren“.

Answer 3

Ich weiß nicht genau, warum, aber mir sind heute Morgen beim Frühstück zwei Dinge aufgefallen ...

1. Die Verwendung einer Tabelle ist gut, macht das Problem aber möglicherweise nur komplizierter.
2. Obwohl Sie Ihren „% Nicht genehmigt“-Anteil als Prozentsatz der Zeitnachweise berechnen, für die der Manager verantwortlich ist, möchten Sie ihn möglicherweise als Prozentsatz aller nicht genehmigten Zeitnachweise berechnen.

Deshalb dachte ich, dass ich eine alternative Antwort poste.

Fügen Sie neben Ihren Rohdaten eine Überschrift %Nound darunter diese Berechnung ein (und füllen Sie sie aus).

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

Die Formel berechnet, wie hoch der Prozentsatz aller nicht genehmigten Stundennachweise ist, falls dieser Stundennachweis nicht genehmigt ist.

Ihre Rohdaten sehen jetzt so aus ...

Erstellen Sie Ihre Pivot-Tabelle und sortieren Sie nach %No.

Wenn Sie dennoch möchten, dass „% Nicht genehmigt“ dem Prozentsatz der Arbeitszeitnachweise entspricht, für die der Manager verantwortlich ist, verwenden Sie diese Gleichung in Spalte D.

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