나는 영업사원의 매출을 기록한 스프레드시트를 가지고 있습니다.
A B C D
1 ID Name Product Sales($)
2 1 John Smith Toaster 250
3 2 Dina Caan Kettle 450
4 1 John Smith Kettle 450
5 1 John Smith Kettle 250
6 2 Dina Caan Toaster 250
7 3 Peter Hues TV 5400
8 3 Peter Hues Radio 480
9 2 Dina Caan Radio 100
10 4 Ralph Do TV 890
11 4 Ralph Do Radio 100
12 5 Ian Poe Watch 750
13 6 Tina Hood TV 450
14 7 Peter Maan Watch 99
제목 ID Name TotalSales($)를 사용하여 총 매출 기준으로 상위 n명(예: 3명)의 영업사원을 표시하는 F 형식의 수식 기반 테이블을 원합니다.
피벗 테이블을 사용하여 이 작업을 쉽게 수행한 다음 상위 n개를 필터링할 수 있지만 이 경우에는 수식을 사용하고 싶습니다.
편집: 결과 테이블에서 제품이 제거되었습니다.
답변1
Excel의 새로운 동적 배열 수식을 사용하면 피벗 테이블을 작성하지 않고도 결과를 얻을 수 있지만 몇 가지 도우미 셀이 필요합니다. 다음 스크린샷을 고려해보세요.
F2의 수식은 =UNIQUE(B2:B14)복사되지 않습니다.
G2의 수식은 =SUMIFS($D$2:$D$14,$B$2:$B$14,UNIQUE(B2:B14))복사되지 않습니다.
총 판매량 기준으로 순위가 매겨진 상위 3명의 판매원은 =INDEX(SORTBY(F2#,G2#,-1),{1;2;3})복사되지 않은 I2의 공식을 사용하여 파생될 수 있습니다.
동적 배열은 수식이 요구하는 한 "유출"되며 동적 배열의 수식 셀을 참조하면 해당 유출이 상속됩니다. 즉, 다음에 새로운 영업 사원이 있으면 F열의 고유 이름 목록을 업데이트할 필요가 없습니다. 수식이 자동으로 이를 수행합니다.
답변2
최고 매출을 얻으려면 다음 배열 수식을 사용하세요.
=LARGE(MODE.MULT(IF(MATCH($B$2:$B$14,B:B,0)=ROW($B$2:$B$14),SUMIFS(D:D,B:B,$B$2:$B$14)*{1,1})),ROW(1:1))
배열 수식이므로 편집 모드를 종료할 때 Enter 대신 Ctrl-Shift-Enter로 확인해야 합니다.
ID와 이름을 얻으려면 다른 배열 수식을 사용하여 이를 활용할 수 있습니다.
=INDEX(A:A,MATCH($I2,SUMIFS($D:$D,$B:$B,$B$2:$B$14),0)+1)
또한 Ctrl-Shift-Enter가 필요한 경우 계속해서 복사하세요.
