Сортировать сводную таблицу Excel по проценту количества

Question

У меня есть исходные данные, показывающие утверждение табелей учета рабочего времени в следующем формате (примерно для 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» в столбце «Нет», она покажет 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 вместо сводной таблицы, но в идеале хотелось бы иметь формат сводной таблицы, если это возможно.

Спасибо!

Луиза

Answer 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

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

Выберите «Инструменты таблиц» «Конструктор» и щелкните «Сводка с помощью сводной таблицы». Создайте сводную таблицу, чтобы она выглядела так...

... и отсортировать его ...

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

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

Answer 2

Возможно, это устарело, но я думаю, что нашел решение этой проблемы.

1. Первый шаг — просто отобразить % неутвержденных с помощью обычного щелчка правой кнопкой мыши, затем «Показать значение как», затем «% от общей суммы строки».
2. Затем нажмите на кнопку раскрывающегося списка рядом с «Метки строк».
3. Выберите «По убыванию» по «Количество одобренных TS»
4. Выберите «Дополнительные параметры сортировки», затем нажмите «Дополнительные параметры» в диалоговом окне.
5. Снимите флажок «Сортировать автоматически при каждом обновлении отчета» (Это ключевой шаг)
6. Выберите «Значения в выбранном столбце» в качестве первой ячейки столбца «Нет».
7. Нажмите «ОК».
8. Для автоматического повторного обновления повторите шаги 2, 4 и 5, но на этот раз установите флажок «Сортировать автоматически при каждом обновлении отчета».

Answer 3

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

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

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

Рядом с исходными данными разместите заголовок %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