Сортировка сводной таблицы 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

Я сделал сводную таблицу следующим образом (% unapproved-это просто формула, которую я имею рядом с сводной таблицей):

                 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. Мои проблемы:

  • если я использую сводную таблицу "Top 10" в столбце "No", она покажет 6 значений, поскольку она не различает между тремя 5s
  • я пробовал добавлять процент, чтобы я мог Сортировать по величине-мелкие На %, затем Большой-маленький по кол-ву, то просто возьмите топ-5 manully - с 5/5 (100%) неутвержденные-это хуже, чем 5/8 (38%) - но не знаю как отсортировать %.
  • если я добавлю его в виде формулы с помощью сводной таблицы (как показано выше), Excel не позволит мне сортировать сводную таблицу на основе этих данных. 'Невозможно переместить часть отчета сводной таблицы....
  • если я добавляю данные как "% от общего числа родительских строк " в таблице, он по-прежнему сортируется только по count

кто-нибудь знает как я могу заставить его делать то, что я хочу, т. е.?

                 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, а не сводную таблицу, но в идеале хочу формат сводной таблицы, если это возможно.

спасибо!

Louise

5
задан Louise
источник

2 ответов

Интересные задачи. Некоторые из вопросов включают в себя:

  • поле расчеты не имеют достаточной гибкости, чтобы получить то, что вам нужно
  • хотя вы можете отображать числа как % от общего числа, и кажется, что вы можете сортировать по нему - он действительно сортирует по основным номерам.

у меня есть решение, которое использует таблицы и сводную таблицу. Возможно, есть более простое решение. Шаги (в Excel 2016):

  1. выберите внутри ваших исходных данных. Выберите ленту" вставить " и нажмите "таблица"
  2. в новой таблице вставьте вычисление для %NotApproved
  3. выберите ленту" инструменты Таблицы "" дизайн "и нажмите" суммировать с сводной таблицей"
  4. построить простую сводную таблицу с именем менеджера в виде строк и %NotApproved в качестве значений.
  5. сортировка имен менеджеров в порядке убывания по %NotApproved

вот пример. Следующий фрагмент 30 строк "необработанных данных", аналогичных описанным в вашем вопросе ...

enter image description here

выберите ленту" вставить "и нажмите "таблица"...

enter image description here

вы лучше отформатированные данные. Выберите 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

таблица теперь выглядит так ...

enter image description here

выберите ленту "инструменты Таблицы" "дизайн" и нажмите на суммировать с сводной таблицей. Постройте сводную таблицу, чтобы выглядеть следующим образом ...

enter image description here

... и разобраться ...

enter image description here

... чтобы получить это ...

enter image description here

хотя кажется, что много шагов, чтобы настроить, это довольно легко поддерживать таблицу, и это автоматически сохраняет сводную таблицу поддерживается.

0
отвечен OldUgly 2016-04-24 06:42:10
источник

не знаю, почему, но я понял две вещи за завтраком этим утром ...

  1. использование таблицы-это хорошо, но, возможно, только усложняет задачу.
  2. хотя вы вычисляете %неутвержденных как % табелей, за которые отвечает менеджер, вы можете рассчитать его как % от всех неутвержденных табелей.

поэтому я решил опубликовать альтернативный ответ.

рядом с необработанными данными поместите заголовок %No и этого Расчет ниже (и заполнить вниз).

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

формула вычисляет, если это расписание не утверждено, процент всех неутвержденных расписаний.

ваши исходные данные теперь выглядят так ...

enter image description here

постройте сводную таблицу и отсортируйте по %No.

enter image description here

Если вы все еще хотите, чтобы %Unapproved был % от расписаний, за которые отвечает менеджер, используйте это уравнение в колонке D.

=IF(C2="No",1,0)/COUNTIF($B:$B,"="&B2)*100
0
отвечен OldUgly 2016-04-24 16:25:56
источник