[엑셀] 수식으로 데이터 정렬하기 (배열수식)

말그대로 정렬기능을 사용하지 않고 수식을 사용해서 정렬을 하는 방법입니다. 실무에서 많이 쓰이는 기능은 아니지만 배열수식을 이해하기 위해 좀 재미있는 방법으로 셀의 값들을 정렬해 보겠습니다.

수식으로 범위를 정렬하기 위해서는 배열수식과 COUNTIF 함수, MATCH 함수, INDEX 함수에 대한 이해가 필요합니다.

※ 배열수식 만들기

수식 입력 후 Ctrl + Shift + Enter 합니다. 그러면 수식 {=수식} 처럼 처음과 끝에 대괄호 { }가 생성됩니다.

수식으로 데이터 정렬하기

C4셀 수식 {=INDEX($B$4:$B$17,MATCH(ROW(1:1),COUNTIF($B$4:$B$17,"<="&$B$4:$B$17),0))}

☞ 수식설명

1. COUNTIF 함수 : 목차에서 본인보다 작거나 같은 값의 개수를 구함

2. MATCH 함수 : ROW 함수에서 반환되는 1에서 부터 순차적으로 lookup_value의 위치를 반환

3. INDEX 함수 : 목차범위에서 MATCH 함수 반환 값의 위치의 값을 반환

※ 위 설명으로 배열함수를 이해하기는 좀 힘든 부분이 있습니다. 그래서 아래 그림으로 좀 더 상세히 설명 드리겠습니다. 배열함수를 쉽게 이해 하려면 아래 처럼 배열을 직접 나열 해보는게 좋습니다.

C4셀 수식 {=INDEX($B$4:$B$17,MATCH(ROW(1:1),COUNTIF($B$4:$B$17,"<="&$B$4:$B$17),0))}을 순차적으로 분해하여 E, F G열에 값 배열

☞ 배열수식 설명(아래 수식은 E~G열에 설명을 위해 넣은 수식 입니다.)

1. COUNTIF 함수

COUNTIF($B$4:$B$17,"<="&$B$4:$B$17) : COUNTIF 함수를 사용해서 자신의 행보다 작은 텍스트가 들어가 있는 셀의 개수 ("가방" 보다 작거나 같은 값은 1 )

2. MATCH 함수

MATCH(ROW(1:1),$E$4:$E$17,0) : lookup_value 를 ROW(1:1) 함수를 사용하여 행이 1칸씩 내려갈때마다 1씩 증가함. COUNTIF의 결과값을 1~14 까지 위치값을 반환

3. INDEX 함수

INDEX($B$4:$B$17,F4) : 목차에서 MATCH 값에 상응하는 위치의 값을 반환

이해 되시나요? 배열수식도 쉽지는 않지만 함수가 무려 4개가 사용되어 설명도 쉽지 않습니다.

아래첨부파일을 참조하세요. ^^

관련글 보기

COUNTIF 함수

첨부파일 보기

수식으로 데이터 정렬하기.xlsx