☰

Excel ピボット テーブルをカウントのパーセンテージで並べ替える

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 pivot-table sorting
Excel ピボット テーブルをカウントのパーセンテージで並べ替える

次の形式でタイムシート承認を示すソース データがあります (約 850 人の従業員と 200 人の管理者)。

  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

次のようにピボット テーブルを作成しました (未承認の割合は、ピボット テーブルの横にある数式です)。

                 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%

承認数で上位 5 人の最悪の承認者を取得するために並べ替える必要がありますが、5 人だけです。私の問題は次のとおりです。

  • ピボットテーブル「トップ10」の「No」列を使用すると、3つの5が区別されないため、6つの値が表示されます。
  • パーセンテージを追加して、最大-最小を % で並べ替え、次に最大-最小をカウントで並べ替え、最後に手動で上位 5 つを取得できるようにしました。5/5 (100%) の未承認は 5/8 (38%) よりも悪いため、% で並べ替える方法がわかりません。
  • ピボット テーブルの外部に数式として追加した場合 (上記のように)、Excel ではそれらのデータに基づいてピボット テーブルを並べ替えることができません。「ピボット テーブル レポートの一部を移動することはできません...」
  • テーブルに「親行の合計の%」として表示するデータを追加しても、カウントのみで並べ替えられます。

つまり、私が望むことを実現するにはどうすればよいか、誰か考えられますか?

                 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%

注: ピボット テーブルではなく countifs を使用して簡単に実行できますが、可能であればピボット テーブル形式を使用するのが理想的です。

ありがとう！

ルイーズ

答え1

興味深い課題です。問題には次のようなものがあります:

  • フィールド計算では、必要なものを得るのに十分な柔軟性がありません
  • 数値を合計の % として表示し、並べ替えることができるように見えますが、実際には基になる数値に基づいて並べ替えられます。

私はテーブルとピボット テーブルを利用するソリューションを持っています。もっと簡単なソリューションがあるかもしれません。手順は次のとおりです (Excel 2016 で実行)。

  1. 生データ内を選択します。「挿入」リボンを選択し、「表」をクリックします。
  2. 新しいテーブルに、%NotApprovedの計算を挿入します。
  3. 「表ツール」「デザイン」リボンを選択し、「ピボットテーブルで集計」をクリックします。
  4. 行としてマネージャー名、値として %NotApproved を使用して、単純なピボット テーブルを作成します。
  5. マネージャー名を%NotApprovedの降順で並べ替える

ここに例があります。以下は、質問で説明されているものと同様の 30 行の「生データ」のスニペットです...

ここに画像の説明を入力してください

「挿入」リボンを選択し、「表」をクリックします...

ここに画像の説明を入力してください

より適切なフォーマットのデータが取得されます。D1 を選択し、次に最後の列見出しを選択して「%No」と入力します。これにより、新しい見出しを持つ新しい列がテーブルに作成されます。セル D2 に次の数式を入力します...

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

Enter キーを押すと、テーブルに自動的に入力されます。この数式は次のことを行います。

  1. IF([@[TS Approved?]]="No",1,0)承認されたタイムシートが「いいえ」の場合、値 1 を取得します。
  2. COUNTIF([Manager Name],"="&[@[Manager Name]])この行のマネージャーがテーブルに何回表示されるかを決定します。
  3. 1 を 2 で割った結果を 100 倍した値

テーブルは次のようになります...

ここに画像の説明を入力してください

「テーブル ツール」「デザイン」リボンを選択し、「ピボット テーブルで集計」をクリックします。次のようにピボット テーブルを作成します...

ここに画像の説明を入力してください

...そしてそれを分類します...

ここに画像の説明を入力してください

...これを取得するには...

ここに画像の説明を入力してください

セットアップには多くの手順が必要に思えますが、テーブルを維持するのは非常に簡単で、これによりピボット テーブルが自動的に維持されます。

答え2

これは古い情報かもしれませんが、この問題の解決策を見つけたと思います。

  1. 最初のステップは、従来の右クリックで「値の表示方法」を選択し、「行の合計の%」を選択して、未承認の%を表示するだけです。
  2. 次に、「行ラベル」の近くにあるドロップダウンボタンをクリックします。
  3. 「承認されたTSの数」で「降順」を選択します
  4. 「その他の並べ替えオプション」を選択し、ダイアログボックスで「その他のオプション」をクリックします。
  5. 「レポートが更新されるたびに自動的に並べ替える」ボックスのチェックを外します（これが重要なステップです）
  6. 「選択した列の値」を「No」列の最初のセルとして選択します
  7. [OK]をクリックします
  8. 自動的に再更新するには、手順 2、4、5 を繰り返しますが、今回は「レポートが更新されるたびに自動的に並べ替える」をオンにします。

答え3

理由は分かりませんが、今朝の朝食で2つのことに気づきました...

  1. テーブルを使用するのは良いことですが、おそらく問題がさらに複雑になるだけです。
  2. %Unapproved は、マネージャーが担当するタイムシートの割合として計算しますが、すべての未承認タイムシートの割合として計算することもできます。

そこで、別の回答を投稿しようと思いました。

生のデータの横に、ヘッダー%Noとこの計算を下に記入します（そして下に記入します）。

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

この数式は、このタイムシートが未承認の場合、すべての未承認タイムシートの割合を計算します。

生データは次のようになります...

ここに画像の説明を入力してください

ピボット テーブルを作成し、%No で並べ替えます。

ここに画像の説明を入力してください

マネージャーが責任を負うタイムシートの % を % 未承認にしたい場合は、列 D で次の式を使用します。

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

関連情報