☰

개수 백분율을 기준으로 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'을 사용하면 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%

참고: 피벗 테이블보다는 countif를 사용하여 쉽게 수행할 수 있지만 가능하다면 피벗 테이블 형식을 사용하는 것이 이상적입니다.

감사합니다!

루이스

답변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. "아니요" 열의 첫 번째 셀로 "선택한 열의 값"을 선택합니다.
  7. 확인을 클릭하세요
  8. 자동으로 다시 업데이트하려면 2, 4, 5단계를 반복하되 이번에는 "보고서가 업데이트될 때마다 자동으로 정렬"을 선택하세요.

답변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

관련 정보