사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 수식을 이용해 정렬하기

 

 

 

 

 

특정 범위의 데이터를 정렬할 필요가 있다면 [데이터] - [정렬 및 필터] 그룹 - [오름차순 정렬] 이나 [내림차순정렬]을 이용한다. 하지만 정렬 기능은 동일한 위치에 결과를 반환하기 때문에 데이터 표시 순서를 다시 원래대로 되돌려야 할 때는 불편하다. 수식을 사용하면 다른 위치에 값을 정렬하여 표시할 수 있어야 편리하다.

 

수식을 이용한 정렬.xlsx

0.01MB

 

위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다.

 

 

[A2:A12] 범위의 제품명을 수식을 이용해 [C2:C12] 범위에 오름차순으로 정렬해 보자.

 

 

오름차순으로 값을 정렬하기 위해 [C2]셀을 선택하고 아래의 수식을 작성한 다음 [Ctrl + Shift + Enter]을 입력한다. [C2] 셀의 채우기 핸들을 [C12] 셀까지 드래그해서 수식을 복사한다.

 

=INDEX($A$2:$A$12,MATCH(ROW(A1),COUNTIF($A$2:$A$12,"<"&$A$2:$A$12)+1,0))

 

이 수식은 설명한 것과 같이 오름차순으로 정렬된 값을 반환한다. 만약 내림차순으로 정렬된 값을 반환하려면 COUNTIF 함수의 비교 연산자를 작다(<)에서 크다(>)로 수정하면 된다. 이 수식을 제대로 이해하려면 MATH 함수 구성을 잘 알아야 한다. MATCH 함수는 다음 세 가지 인수로 구성되어야 있다.

 

찾을 값 : ROW(A1)

 

범위 : COUNTIF($A$2:$A$12,"<"&$A$2:$A$12)+1

 

찾을 옵션 : 0

 

위 구성은 COUNTIF(A2:A12,"<"&A2:A12)+1 배열에서 1을 반환하는 정확한 위치를 찾으라는 의미이다. COUNTIF(A2:A12,"<"&A2:A12)+1은 [A2:A12] 범위에 있는 값들의 순위를 배열로 반환한다. 이 수식은 다음과 같이 수식을 하나로 표현한 것이다.

 

COUNTIF(A2:A12,"<"&A2)+1

COUNTIF(A2:A12,"<"&A3)+1

:

COUNTIF(A2:A12,"<"&A12)+1

 

그러면 각 셀의 값보다 작은 값이 몇 개 있는지 계산하여 1을 더하면 작은 값 순서대로 순위가 매겨진다. ROW(A1) 함수로 1~n까지의 셀 위치 값을 구하면 오름차순으로 정렬한 값의 위치를 확인할 수 있다. 이렇게 MATCH 함수로 확인된 위치를 INDEX 함수로 [A2:A12] 범위에서 찾아 값을 반환하면 정렬된 결과가 반환되는 것이다.