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(엑셀) 워크시트에 가로 세로로 입력된 데이터를 필요에 따라 가로와 세로를 바꾸는 행/열 바꾸기 기능을 사용하는 경우가 있다.

아래 그림과 같이 가로로는 분기별 데이터, 세로로는 이름이 적혀있는 데이터가 있다고 한다.


이를 가로로는 이름, 세로로는 분기가 표시된 형태로 바꾸려면 일반적으로 "선택하여 붙여넣기" 메뉴에 있는 '행/열 바꿈' 기능을 사용하게 된다.


이렇게 하면 간단하게 모양을 바꿀 수는 있지만, 원본 데이터 모양도 가지고 있는 필요가 있고, 각종 수식이 들어 있는 상태라면 가끔 곤혹스러울 때가 있다.

따라서 원본과 연결은 유지한 상태로 행/열이 바뀐 또 다른 표도 별도로 유지해야 할 필요가 있을 때에는 다음과 같은 순서를 따른다.

원본 데이터가 "Sheet1" 시트에 있다고 하고, 다른 시트의 A1에 아래와 같은 수식을 입력한다. 시트 이름은 상관이 없으나. 수식이 입력되는 셀의 위치(여기서는 A1)는 원본 시트의 위치와 같아야 한다.


INDEX 함수에 원본 데이터의 위치를 입력할 때에는 절대 주소를 사용한다. 입력을 마치면 아래와 같이 해당 위치의 값이 나타난다.


그 다음 A1 셀에서 채우기 핸들을 적당한 범위로 끌면 아래와 같이 행/열이 바뀐 표를 확인할 수 있다. 행 번호를 불러오는 ROW 함수와 열 번호를 불러오는 COLUMN 함수를 거꾸로 사용한 결과이다.


즉, 다른 시트의 같은 범위에 있는 데이터를 불러올 때 행과 열을 바꿔 가져오도록 한 것이다. 따라서 시트는 달라도 범위는 같아야 한다.

이를 같은 시트 내에서 구현하고 싶다면 불러오는 위치를 상황에 맞게 수정하면 된다. 아래 그림과 같이 같은 시트의 A10 셀을 기준으로 바뀐 표를 입력하고 싶다면 A10에 수식을 입력한다.


위와 같이 입력하면 아래와 같이 참조 범위가 잘못 되었다는 "#REF!" 메시지가 나온다.


수식을 아래와 같이 변경한다. 원본의 위치가 열은 같지만, 행이 현 위치보다 9칸 위로 올라가 있으므로 ROW 함수의 결과에서 9를 빼는 것이다.


이렇게 하면 제대로 나오는 것을 확인할 수 있다.

원본과 연결되어 있으므로, 원본의 내용이 바뀌면 새로운 표의 내용도 따라 바뀐다.

사용 함수  및 기능
- ROW()
- 채우기 핸들
- 정렬