구글 스프레드시트 순위표 만들기 ｜UNIQUE, RANK, TRANSPOSE 함수 사용법

오늘 만들어 볼 것은 바로 순위표 입니다.

 

반복되는 내용이 있는 자료에서 UNIQUE 를 활용해 중복값 없이 리스트업하고

TRANCPOSE 를 이용해 그 리스트를 가로로 정렬시키고

RANK 를 활용해서 리스트 값의 순위를 매겨보겠습니다.

 

UNIQUE 가 기억이 안나시는 분은 다녀오세용 ▼

 

2023.02.01 - [스프레드시트 자동화 시리즈/엑셀 & 구글 시트 공통 함수] - 구글 스프레드시트 검색 함수 완전정리｜VLOOKUP·HLOOKUP·UNIQUE 활용법

구글 스프레드시트 검색 함수 완전정리｜VLOOKUP·HLOOKUP·UNIQUE 활용법

 

 

우선 어떤 상황을 가정해볼까요...흠..

영업 자료를 정리한다고 생각해볼까요?

영업 직원 5명이 있고, 각각의 일매출들이 있다고 치고

3~4월의 매출을 정리하고 순위를 매겨보겠습니다.

 

우선 가상의 자료를 먼저 만들어보죠.

(자료 만드는게 젤 귀찮;;;)

 

 

우선 이런식으로 자료를 만들었습니다.

 

제가 앞선 포스트에서 설명드렸듯, 함수를 생각하기 전에

함수가 적용될 틀을 먼저 만드는 것이 순서라고 했습니다.

 

그렇다면 통계를 내고 순위를 매길 표를 먼저 만들어 보겠습니다.

 

1행에서 10행까지 드래그해서 마우스 우클릭하고

'위에 10행 삽입'을 눌러서 상단에 공간을 확보합니다.

그리고나서 표를 만들어줍니다.

 

자료를 아래에 두고 상단에 통계용 표를 만들어두면

자료를 지속적으로 추가하면서 실시간 통계를 볼 수 있습니다.

 

 

우선 이렇게 만들어 놓습니다.

자 이제 사원 이름을 채울건데요.

예제에는 4명 밖에 없으니 직접 써도 되지만

인원이 30명 정도 되면 unique를 이용해 나열하는 것이 편합니다.

 

그러면 자료가 있는 곳 오른쪽에

반복없이 사원들 이름을 나열하고, 각 사원들의 총매출을 계산할 수있는

계산표를 먼저 하나 만들어 보겠습니다.

그리고 월별 매출도 추출할 예정이기 때문에, month 함수를 사용하여 '월' 표시 열도 만들어 주겠습니다.

 

 

 

먼저 월을 추출한 열을 먼저 만들어 줬는데요.

=month(대상 셀) 함수를 사용했습니다.

보통은 자동으로 아래 셀들에 함수가 채워지게끔 되지만,

그게 안될 경우 직접 드래그할 필요없이

숫자 3이 있는 셀을 선택하고 오른쪽 아래 파란 동그라미 부분을 더블클릭하면

C열에 자료가 채워져 있는 행까지 알아서 복사가 됩니다.

 

 

 

=UNIQUE(범위) 함수를 사용해서

사원 이름을 중복없이 리스트업 했습니다.

여기까지는 쉬워요.

 

그 다음은 이제 사원별 총 매출을 계산하겠습니다.

예상되시죠?

 

=SUMIF($B$11:$B,F11,$C$11:$C)

 

이렇게 함수가 들어갈건데

이제 $의 의미는 하시죠?

함수를 아래로 드래그하거나 오른쪽 아래 모서리 더블클릭을 해서

아래 셀들에 자동으로 채워지게 했을 때

$가 없으면 B12, B13 이렇게 숫자가 자동으로 변경이 됩니다.

$를 넣어줌으로써 범위를 고정시키는 겁니다.

 

이제 총 매출도 뽑아냈습니다.

 

그럼 이제 상단의 표를 만들어 볼게요.

상단의 표는 모든 영업사원을 나열하지는 않고,

총 매출이 500만원 이상인 사원만 나열할거예요.

그리고 세로가 아니라 가로로 나열하겠습니다.

 

 

일단 총 매출 500만원 이상 사원리스트 추출 함수식은

 

=TRANSPOSE(UNIQUE(FILTER(F11:F,G11:G>4999999)))

 

입니다.

 

생소한 함수가 보이죠???

 

TRANSPOSE

가로, 세로 바꿔주는거라고 생각하시면 될거같아요.

 

FILTER(범위, 조건)

원하는 조건으로 필터링 하는 함수입니다.

 

아 그러면 이제 리스트는 됐구요.

 

 

총 매출을 불러와볼게요.

총매출은 그냥 VLOOKUP 쓰시면 됩니다.

 

 

아 여기서 하나 아셔야 할 점은

구글 스프레드시트에서는 G11:G 와 같은 범위 지정이 가능한데

엑셀에서는 G11:G1000 처럼 꼭 행값을 넣어줘야합니다!

 

자 다음은 월별 매출 살펴볼게요.

우리는 미리 월을 추출 해놨죠?

여기서는 SUMIFS(합계범위, 조건1기준, 조건1값, 조건2기준, 조건2값...) 을 쓰면 됩니다.

 

 

 

=SUMIFS($C$11:$C,$B$11:$B,B$1,$D$11:$D,$A4)

 

이 정도는 이제 쉽죠 ?? ㅋㅋㅋㅋ

 

이제 RANK 함수로 순위를 매겨볼게요!

 

 

=RANK(B3,$B$3:$D$3)

 

=RANK(대상 값,비교 값 범위)

 

RANK 함수는 총 3가지가 있는데요

RANK, RANK.EQ, RANK.AVG

 

 

사실 RANK와 RANK.EQ는 저는 별다른 점은 모르겠어요.

 

AVG 는 

데이터세트에서 지정된 값의 순위를 반환합니다.

데이터세트에 동일한 값의 항목이 두 개 이상인 경우, 항목의 평균 순위가 반환됩니다.

 

반응형

 

항목의 평균순위가 무슨 말이냐면,

1등이 2명이면 원래는 1위 2위가 되야하는 수 잖아요?

그러면 1+2 /2 이렇게 계산을 해요.

그러면 1.5 니까 이건 2로 치는거.

그래서 만약에 1등이 3명이면

1+2+3(=6)/3 이건 2 잖아요? 그럼 이것도 2등이 3명이 되는거.

1등이 4명이다?

1+2+3+4(10)/4 이러면 2.5니까 3등이 4명이 되는겁니다.

이해되시죵??

 

EQ 는

데이터세트에서 지정된 값의 순위를 반환합니다.

데이터세트에 동일한 값의 항목이 두 개 이상인 경우, 항목의 최고 순위가 반환됩니다.

이건 그냥 동일 순위가 2명이 있으면 원래 1등 2등이어야 하는데 2명이니까

최고 순위인 1등을 주고, 3등 값이 3명이면 원래는 3,4,5등 이어야하니까

최고값이 3등을 주고 5까지 비우고 그 다음이 6등이 됩니다.