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를 빼는 것이다.


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

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

일을 하다 보면 출장비 계산이나 기타 등등의 이유로 지도 상에서 두 지점 간의 직전 거리를 알아야 하는 경우가 있다. 유류비처럼 꼬불꼬불하더라도 실제 움직인 거리가 중요할텐데 아무튼 이런 정보가 필요할 때가 있다.

가장 간단한 방법은 PC에 설치한 지도 프로그램을 이용하거나 포털 사이트의 지도 서비를 이용하는 것이다. 아래는 각각 다음 과 네이버 에서 확인한 것이다.

위 예는 서울역에서 한강 대교 중간 지점 정도까지의 거리를 확인한 것이다. 대략 4.1에서 4.2km의 수치를 보여주고 있다.

이런 식으로 다수의 지점을 Excel(엑셀) 워크시트에 입력해 두고 필요한 지점끼리 짝을 지어 거리를 계산하는 방법 하나를 소개하고자 한다.

우선 Excel 워크시트에 원하는 지점 또는 주소의 위도와 경도를 입력해야 한다. 필요한 데이터를 알아내는 방법도 몇 가지 있지만 여기에서는 구글 맵 사이트를 이용한다.

구글 맵 사이트 로 이동한다.


[지도 검색] 창에 "서울역"을 입력하고 해당 위치를 찾는다.


위치를 표시하는 핀에서 마우스 오른쪽 버튼을 누르고, [지도 중앙으로 설정]을 선택한다.


해당 지점이 화면 가운데에 자리를 잡는다. 이렇게 해야 해당 지점의 정확한 좌표를 확인할 수 있다.


위치를 찾을 때 특정 이름을 사용할 수도 있지만, 번지까지 주소를 알고 있다면 주소를 입력해도 된다. 개인 주택 등 특정 이름이 없는 경우는 주소가 더 요긴하다.


해당 지점을 중앙에 놓았으면, 우측 상단의 "링크" 메뉴를 클릭하여 아래 그림과 같이 링크 정보를 불러온다.


"이메일 또는 메신저에 링크 붙여넣기" 항목에 보면 아래와 같이 좌표처럼 생긴 숫자를 찾을 수 있다. 이를 복사하고 Excel에 정리한다. 쉼표로 구분되어 있는데, 앞이 위도고 뒤가 경도이다.


다른 지점의 좌표도 위와 같은 방법으로 정리한다.


자신의 활용 목적에 맞게 자료를 정리하고 거리를 계산하는 수식을 이용하면 된다.


예를 들어, 위 시트에서 2행의 경우는 서울역에서 한강 대교 간의 거리를 계산하는 것이다. 상대 지점 열에 상대 지점의 번호를 입력하면 F열에 자동으로 지점명이 입력되도록 한다. index나 vlookup 함수를 사용하면 된다.

거리1과 거리2가 있는데, 거리1은 지구가 둥글다는 점을 감안한 거리 계산법이고, 거리2는 위도 간의 거리와 경도 간의 거리를 두 변으로 하는 직각 삼각형의 빗변의 길이를 구하는 피타고라스의 정리를 이용한 것이다. 이 공식을 더러 이용하는 경우도 있어서 적어둔 것이고, 거리1 방식을 추천한다. 서울역과 한강 대교 간의 거리는 4.13km로 다음이나 네이버의 결과와 유사하다.

거리1의 각 셀에 들어가는 공식의 형태는 아래와 같다.

=ACOS(COS(RADIANS(90-지점1의위도))*COS(RADIANS(90-지점2의위도))+SIN(RADIANS(90-지점1의위도)) *SIN(RADIANS(90-지점2의위도))*COS(RADIANS(지점1의경도-지점2의경도)))*6371

G2셀에 실제 입력된 내용:
=ACOS(COS(RADIANS(90-C2))*COS(RADIANS(90-INDIRECT("C"&E2+1)))+SIN(RADIANS(90-C2))*SIN(RADIANS(90-INDIRECT("C"&E2+1)))*COS(RADIANS(D2-INDIRECT("D"&E2+1))))*6371

이렇게 입력하면 E열 상대 지점 셀에서 다른 지점 번호로 바꾸더라도 자동으로 거리를 다시 계산할 수 있다.

H2셀에 실제 입력된 내용:
=SQRT((((C2-INDIRECT("C"&E2+1))*92)^2)+(((D2-INDIRECT("D"&E2+1))*114)^2))

92는 위도 한 단위의 실제 거리, 114는 경도 한 단위의 실제 거리를 표시한 것이다.

모든 수식은 첫 셀에 한 번만 입력한 후 아래로 복사해 사용하면 된다. 이런 방법을 바탕으로 다양한 형태의 양식에 사용할 수 있을 것이다.

참고로, 위 공식들은 예전에 어디서 찾아둔 것으로 비슷한 다른 공식이 여럿 있으므로 필요하다면 웹에서 찾아 다양하게 적용해 보면 좋을 것이다.

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

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


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


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


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


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


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


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


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


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


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


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


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