[엑셀] 자동으로 정렬되는 표 만들기-스크랩한 글

[하늬바람]

[엑셀 TIP #19] 자동으로 정렬되는 표 만들기, 특별한 비법

어메이징추어

공식

2만 팔로워

2017.02.18. 23:504,509 읽음 비밀글

통계

어메이징추어입니다.
엑셀의 정렬 기능, 알고 계신가요?
이미 알고 계신 분도 많겠지만, 데이터를 정렬하여 보여 줄 수 있는 편리한 기능입니다.

하지만 원본 데이터를 복사하여, 이 기능으로 순위 표를 만들고 계셨다면?
데이터를 고칠 때마다 수동으로 다시 정렬해야 하고... 이런 거, 조금 귀찮았죠?

그런 단점을 해결할 수 있는, 즉 원본 데이터를 바꿀 때마다 자동으로 갱신되는 표를 만드는 방법을 여기서 대공개!! 합니다.

위 그림에서 오른쪽 부분처럼 순위 표를 만들어 볼 것입니다.
이번에 소개할 "특별한 비법" 에서는 '정렬' 기능을 이용하지 않습니다.
정렬 기능과 관련된 함수를 이용할 것입니다.

자, 그럼 위 그림의 왼쪽과 같은 원본 데이터가 있다고 합시다.
출발해 볼까요?

원본 데이터는 위와 같습니다.
본격적으로 만들기 전에, 몇 가지 함수를 알아보겠습니다.

ROW 함수
수식 : =ROW(셀)
기능 : 셀 주소의 오른쪽의 숫자 부분을 표시합니다.
즉, =ROW(A4) 이면 4가 나온다는 것입니다.

RANK 함수
수식 : =RANK(값, 범위, (0 또는 1))
(여기서는 (0 또는 1)을 생략하여 =RANK(값, 범위) 처럼 하겠습니다. 참고로 0은 내림차순, 1은 오름차순을 의미합니다.)
기능 : 값이 범위에서 몇 등인지, 그 순위를 출력합니다. =RANK(값, 범위) 의 수식에서는 내림차순입니다.

1
원본 데이터 표의 바로 왼쪽에 위와 같이 =D4-ROW(A1)/1000 을 입력합니다.

D4는 원래 점수 값이고, 여기에서 '동점자 처리' 를 위해 임시적으로 약간의 값을 뺀 것입니다. ROW(A1)/1000 = 1/1000 = 0.001이므로, 순위에 변동이 없습니다. 이렇게 순위에 변동이 없을 정도의 값을 빼 주는 것이 포인트입니다.

수식을 복사하면 ROW 함수의 수식이 가리키는 셀도 이에 따라 이동하면서, ROW 함수의 값이 증가합니다.

2
그 셀의 오른쪽 아래 부분을 클릭한 후 아래쪽으로, 순위 표 끝까지 드래그합니다. 위와 같은 결과가 나타났죠?

자세히 보시면, 원래 점수에서 각각 0.001, 0.002, ..., 0.012를 뺀 것을 볼 수 있습니다. 점수는 1점 단위이므로 0.012 정도 차이는 동점자(100점, 80점, 73점)를 구분하는 것 빼고는 순위에 영향을 주지 않습니다.

3
그 왼쪽 열에 =RANK(B4, B$4:B$15) 를 입력해 줍니다. $를 붙이는 이유는 그 오른쪽의 '숫자' 를 고정하여 셀 범위가 바뀌지 않도록 하기 위해서입니다. 1번 과정의 마지막에서 '수식을 복사하면 수식이 가리키는 셀도 따라서 이동한다' 고 한 것, 기억하시죠? (단, B4에는 붙이지 마세요!)

즉, 2번에서 임시적으로 입력한 점수들 사이의 순위를 구하자! 는 것입니다.

4
마찬가지로 이렇게 구한 순위도 2번처럼 셀의 오른쪽 아래 끝부분을 클릭한 후 아래쪽으로 드래그하여 채워 줍니다.
99.996이 1등이고, 99.995가 2등! 동점자 사이에서도 '임시적으로' 등수가 갈린 모습입니다.

5
그 오른쪽에 순위표를 만들어 줍니다. 저는 위와 같은 모양으로 만들었습니다.

이제 또 하나의 함수를 소개합니다. 바로 VLOOKUP 함수!
수식 : =VLOOKUP(값, 범위, N, TRUE/FALSE)
(여기서는 정확한 값을 찾기 위하여 FALSE로 합니다. 즉 =VLOOKUP(값, 범위, N, FALSE))
기능 : 범위의 가장 왼쪽 열에서 값을 찾아서, 값이 있는 행의 왼쪽에서 N번째 셀의 값을 구합니다.

6
드디어 VLOOKUP 함수를 이용할 차례입니다.
이름 부분의 가장 위쪽 셀에 =VLOOKUP(ROW(A1), A$3:D$15, 3, FALSE) 를 입력합니다.

ROW(A1) : 수식을 복사함에 따라 값이 변합니다. 처음에는 1이니까 범위에서 1을 찾아라! 겠죠?
A$3:D$15 : 범위입니다. 원본 데이터와 그 왼쪽의 '임시 점수', 또 그 왼쪽의 '임시 점수에 대한 순위' 까지 포함합니다. (위 이미지의 빨간색 부분 참고)
3 : 이름은 선택한 범위의 왼쪽에서 3번째 열에 있는 값, 즉 그 가로줄의 왼쪽에서 3번째 값입니다.

7
6번의 수식을 그대로 복사한 다음(수식 입력줄을 드래그하여 복사하세요!)
엑셀 점수 부분의 가장 위쪽 셀에 붙여넣기한 후, FALSE 왼쪽의 3을 4로 바꿔 줍니다. 범위는 똑같고, 왼쪽에서 4번째 값인 '점수' 를 구한다는 차이점만 있습니다.

8
이름과 엑셀 점수 란을 드래그하여 2개 셀을 동시에 선택한 다음,
그 부분의 오른쪽 아래 끝부분을 클릭하여 아래쪽으로 쭉 드래그합니다.

결과는, 예상하시듯이 나머지 부분이 채워질 것입니다.

9
순위 부분에서 RANK 함수를 이용합니다.
순위 부분의 맨 위 셀에 =RANK(H4, H$4:H$15) 를 입력합니다.
H4는 엑셀 점수이고, H$4:H$15는 엑셀 점수가 쓰여 있는 부분의 범위! 이므로 엑셀 점수가 몇 등인지를 구하는 것이 됩니다.

10
이것도 마찬가지로 셀의 오른쪽 아래 부분 클릭 후 아래로 드래그하여 나머지를 채워 줍니다.

이제 동점자 처리까지 스마트~하게 되는! 순위 표가 완성되었습니다.
원본 자료를 바꾸면, 순위 표도 자동으로 바뀝니다. 원본 자료를 복사한 후 '정렬' 할 때는 느낄 수 없는 매력입니다. ^^

11
임시로 만들었던 부분을 제거해 줍니다. 글자 색을 배경 색과 똑같이 하거나, 열을 숨기면 됩니다. 열 전체를 선택한 후 [마우스 오른쪽 버튼] - [숨기기] 클릭!

이번 팁, 유용하게 활용하실 준비, 되셨나요?
다음에도 멋진 팁으로 찾아뵙겠습니다. 감사합니다.