CaN (Computer & Network) Tips

Excel(엑셀)에서 1, 2, 3, 가나다, ABC 순으로 정렬되어 있거나 대소, 순서가 분명한 데이터들은 오름차순, 내림차순과 같은 정렬 기능을 사용하면 쉽게 기본 정렬에서 역순으로 순서를 바꿔 정리할 수가 있다.

하지만, 데이터를 다루다보면 이런 순서로는 정리가 되지 않고 꼭 정해진 차례를 따라야 하는 경우를 만나게 되는데, 이 글에서는 이런 데이터를 역순으로 정리할 때 사용할 수 있는 방법 세 가지를 소개한다.

첫 번째는 해당 데이터 목록의 각 항목에 일련번호를 할당한 다음, 오름차순이나 내림차순을 이용해 원하는 순서로 정렬하고, 할당한 일련번호를 삭제하는 방법이다.

다음 글들도 참고하면 좋다.

- "[Excel(엑셀)] 일련번호를 넣는 몇 가지 방법", 2007. 7. 25.

- "[Excel] 열의 순서를 정리하는 세 가지 방법", 2007. 7. 18.

- "[MS Excel 2007] 가나다 순이 아니라 셀 배경색이나 글꼴 색으로 정렬하기(Sorting)", 2008. 8. 6.

 

두 번째와 세 번째는 아래와 같이 INDEX, ROW, ROWS 함수를 이용하는 방법이다.

INDEX 함수 안에 ROW를 사용할 때에는 시작하는 위치와 전체 목록의 크기를 염두에 두어야 한다.

ROWS 함수를 이용하면 더 간편하다.

주로 첫 번째나 세 번째 방법을 추천한다. 

열 방향으로 정리할 때에도 같지만, 이때에는 ROW 함수가 아니라, COLUMN과 COLUMNS 함수를 이용하면 된다.

Excel(엑셀)에 입력한 데이터는 보통 같은 성격의 것(필드)이라면 주로 위에서 아래로 한 줄로 즉, 한 열로 정리하는 것이 일반적이다. 또, 데이터를 정리하고 관리하는 것이라면 이렇게 하는 것이 맞다. 그런데 이런저런 작업을 하다보면 모양이나 인쇄 등의 이유로 한 열로 정리된 데이터를 여러 열로 나누어 나열해야 할 때가 있다.

예를 들어, 아래 그림과 같이 A열에 위에서 아래로 입력된 데이터를 그 옆의 다른 테이블(표)처럼 여러 열로 정리하는 경우이다.

이렇게 정리할 때 보통은 다음 두 가지를 먼저 결정해야 한다.

- 몇 개의 열로 정리할 것인가?

- 데이터를 가로나 세로 중 어느 방향으로 입력할 것인가?

이런 작업을 할 때에는 위에서 예로 제시한 각각의 표 형태에 따라 다음과 같은 수식을 이용해 볼 수 있다.

같은 표에는 모든 셀에 같은 수식이 들어간다. 즉, 대표 셀에 하나의 수식을 만든 다음 필요한 만큼 복사하면 된다.

수식을 보면서 열 개수에 해당하는 숫자만 필요에 따라 조정하면 되고, 표가 어느 위치에 입력되는 지에 따라 기준 셀 들의 위치만 다시 잡아주면 된다. 또, 수식에 IF 등을 집어 넣어 좀 더 정교하고 더 실용적으로 만들 수도 있다.

다른 작업도 마찬가지이지만 다른 여러 가지 방법이 있을 수도 있다. 또, 대충 이 방법을 응용하면 다양한 형태의 보고서에 활용할 수 있을 것이다.

 

Excel(엑셀)의 통계 데이터 분석 도구 중에 "순위와 백분율(백분위수)"이라는 기능이 있다. 이 기능을 사용하면 원본 데이터 집합에 있는 각 값의 순위와 백분위를 보여 주는 표를 만들 수 있다. 이 결과표에 "포인트(Point)"라는 개념이 나오는데 이것 때문에 결과표를 제대로 활용하려면 한두 단계 작업을 더 해주어야 한다.

아래와 같은 원본 데이터가 있다고 하자.¹


아래 그림과 같이 데이터 분석 도구를 선택하고, "순위와 백분율" 기능을 불러온다.


순위를 매길 데이터 부분을 "입력 범위"에 입력한다. 데이터가 아래로 입력되어 있으므로 "데이터 방향"은 '열'이 된다.


열 방향의 데이터 입력 범위에서 첫째 행은 순위 산출에 포함되지 않는 열 제목(필드명, 이름표)이므로 "첫째 행 이름표 사용"을 체크한다.


결과표가 나타날 적당한 위치를 선택한다.


모든 설정을 마치고 [확인]을 누르면 아래와 같은 결과표가 나타난다.


결과표에 있는 "포인트"라는 항목은 순위가 매겨진 각 데이터가 원본 입력 범위 어디에 위치해 있는지를 표시해주는 일종의 이정표이다. 각 숫자는 해당 데이터가 원본 입력 범위에 몇 번째 행에 있는지를 표시해준다.


각 포인트에 해당하는 실제 이름을 결과표에 추가하기 위해 다음 그림과 같이 "이름" 열을 하나 추가해 준다.


F2 셀에 다음 그림과 같이 index 함수를 입력한다. $B$2:$B$6 부분은 미리 이름을 지정해두고 사용하면 편리하다. 마지막 인수인 "1"은 index 함수에 사용하는 원본 데이터의 열이 하나밖에 없으므로 생략해도 된다.


이 수식을 입력하면 이름을 찾아 보여준다.


다른 셀에도 같은 수식을 복사해 넣기 위해 F2 셀을 선택한 후 채우기 핸들을 더블 클릭한다.


빈 셀이 다 채워지면 "자동 채우기 옵션" 아이콘을 클릭하여 [서식 없이 채우기"를 선택해준다. 이렇게 하지 않으면 마지막 행의 테이블 테두리가 F2 셀의 모양(서식)을 따라가 없어져 버린다.