[ 응용 ] 엑셀 원본 자료에서 원하는 열의 값만 불러오기, 원하는 열만 불러온 자료로 가공하기

엑셀 파일을 다운로드하거나, 내가 만든 자료 중 일부만 보고자료로써

또는 제출자료로써 관리해야 할 때

일부 열의 자료만 별도로 정리해야 하는 경우가 있습니다.

 

예를 들면 내가 관리하는 자료에는 전화번호, 차량번호 등 

포함되어 있고, 그 자료가 업무에 꼭 필요하지만,

보고 자료에는 그러한 정보가 필요가 없을 수도 있습니다.

 

가끔 한두번이면 할만하지만,

주기적으로 보고 또는 제출하는 자료는 매 번 자료를 가공하기가 귀찮습니다.

 

오늘은 자동으로 원본 데이터의 원하는 열만 추출하여 

데이터를 가공하는 방법을 작성해 보겠습니다.

 

아래는 함수식을 입력했을 때 작동하는 모습입니다.

 

반응형

 자료준비

 

먼저 아래 그림과 같은 자료를 관리하고 있다고 가정하겠습니다.

이 원본자료가 있는 시트는 Sheet1입니다.

 

다른 시트에 보고자료로 별도 관리하기 위해

이름과 국어, 그리고 수학만 관리하려 합니다.

이 보고(제출)자료가 있는 시트는 Sheet2입니다.

 

 사용할 함수

 

INDEX() 함수와 MATCH() 함수, IFERROR() 함수

위 두 함수의 간단한 설명은 아래 링크를 참고하시면 됩니다.

※ Index() 함수   / ※ Match() 함수

 

 전체 함수식

 

아래는 사용한 전체 함수식입니다.

=INDEX(Sheet1!$A2:$D15, 0, MATCH(A$1, Sheet1!$A$1:$D$1, 0))

 

먼저 INDEX() 함수를 사용해

전체 데이터 범위에서 전체 열을 검색합니다.

INDEX 함수의 두 번째 인자가 0이라면

전체 열을 검색한다는 의미입니다.

아래 그림은 원본 데이터 즉, Sheet1의 데이터 범위 A2:D15입니다.

 

다음 INDEX() 함수의 세 번째 인자로 열번호를 확인해야 합니다.

세 번째 인자의 행번호를 확인하기 위해

MATCH() 함수를 사용합니다.

 

Sheet2의 이름. 즉, A1셀의 값이

Sheet1의 제목 범위에서. 즉, A1:D1 범위에서 

어디에 있는지 확인을 합니다.

 

이제 원본의 데이터 범위 A2:D15는 변하지 않는 범위이므로

절대참조로 바꾸어줍니다.

 

Sheet2의 A1셀은 자동 채우기 할 때 

앞에 알파벳만 바뀌므로 A$1으로 바꾸어 주고

 

역시나 Sheet1의 A1:D1 값도 바뀌지 않는 값이므로

절대참조로 바꾸어 줍니다.

 

728x90

 결과 확인하기

 

그럼 지금까지 결과는 아래 그림과 같이 됩니다.

 

이 상태에서 A2셀을 오른쪽으로 자동 채우기를 하면

아래 그림과 같이 결과가 나타납니다.

 

보고자료가 얼마나 될지 몰라 넉넉히 자동 채우기를 해 놓을 수도 있습니다.

하지만 에러 메시지가 거슬리므로

IFERROR() 함수를 사용해 에러 메시지가 보이지 않게 설정합니다.

=IFERROR(INDEX(Sheet1!$A2:$D15, 0, MATCH(A$1, Sheet1!$A$1:$D$1, 0)), "")

 

이렇게 되면 에러메시지는 보이지 않게 됩니다.

 

이 상태에서 D1셀에 수학을 입력하면

수학의 점수까지 표시가 됩니다.

 

다만 서식까지 불러올 수 있는 것은 아니기 때문에

서식설정은 별도로 해주어야 합니다.

 

여기서 영어를 수학으로 바꾸면

자동으로 수학의 값을 불러옵니다.

 

 마치며

 

만약 원본 데이터가 추가될 때마다 변경한 데이터도 바꾸고 싶다면

셀의 범위를 넉넉하게 지정하거나

원본 데이터를 표로 만들어 관리하는 방법이 있습니다.

 

저는 개인적으로 이런 함수식을 좋아합니다.

자주 쓰지는 않지만,

한번 설정해 놓으면 별도로 건드릴 것 없는 수식

 

일하면서 바쁜데 알고 있더라고 생각이 안 날 때도 많고,

일하기도 바쁜데 찾아보려면 시간도 걸리고....

 

한 번쯤 시간 내서 설정해 놓으면 좋은 함수식입니다. 

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기