VLOOKUP, 이름 정의, 데이터 유효성 검사, 조건부 서식을 이용해 목록에서 필요한 데이터만 추출해 문서 만들기

안녕하세요.

 

이메일로 유튜브 구독자가 질문을 하셨습니다.

 

신**님의 질문입니다.

 

안녕하세요.
짤막한 강좌 한정희 강사님.
 
먼저 유튜브로 엑셀강의 너무 잘 보고있습니다!
강사님 영상 보면서 엑셀 공부중입니다!
 
다름이 아니라 제가 회사에서 보고서를 작성해야 합니다.
보고서 표 항목마다 일일히 입력해야되는 작업인데요.
기존 자료를 보고표 항목에 간단하게 입력할 수 있는 방법이 있을까요?
 
예를 들어 보낸 자료를 보시면 인천광역시 어디 보고서면 자료에서 값을 일일히 찾아서 입력해야합니다ㅠ
지역 선택으로 보고서가 채워질 수 있는 방법이 있다면 알려주실 수 있으실까요?
저는 vlookup함수와 index함수를 사용해 봤는데
다 오류가 나더라구요ㅠㅠ
꼭 해결해야 하는 작업이라 강사님께 이렇게 메일보냅니다.
 
그럼 메일확인해주셔서 감사합니다.

 

메일에 첨부된 파일을 열어 보니 VLOOKUP 함수와 INDEX, MATCH 함수로 구현을 했는데, 오류가 난 모습이더군요.

업무를 담당하는 사람이 직접 엑셀 기능을 활용해 문제를 해결할 수 있으면 가장 좋습니다.

그러면 다른 업무에서도 배운 기능을 활용할 수 있죠.

다른 사람이 구현해 주면 딱 그 업무에만 쓸 수 있을 뿐이고 조금이라도 변화가 생기면 못쓰게 됩니다.

또 업무는 모르고 엑셀 기능만 아는 사람은 아예 문제를 해결할 수 없는 경우도 있습니다. 업무처리가 어떻게 되는지 모르기 때문인데, 업무 담당자와 여러 번 이야기를 나눠서 요구조건을 파익하고 해결방법을 찾아야 합니다.

 

업무에 사용하는 파일을 받아 보면 종종 느끼는 점이 있는데, 아주 복잡한 파일을 쓰고 있다는 점입니다.

행 방향으로도 많을 뿐만아니라 열도 AW열까지 49개 열을 사용하는 파일이었고 어떨 땐 휠씬 더 복잡하기도 합니다.

 

강좌를 작성하거나 강의할 때 사용하는 파일은 기능을 소개하기 위한 최소 내용만 있는 파일이라서 단순합니다.

그래서 가능하면 실무강좌에서는 진짜 업무에 사용하는 파일로 소개하려고 합니다.

 

첨부하신 파일은 외부에 공개할 수 없는 파일이라서 공공데이터포털(http://data.go.kr)에서 내용을 소개할 수 있는 형식의 데이터를 찾았습니다.

 

전국종량제봉투가격표준데이터(https://www.data.go.kr/data/15025538/standard.do)

XLS 파일을 다운로드해서 조금 변형을 했습니다.

파일 형식을 XLSX로 바꾸고, 열 너비를 보기 좋게 수정했으며 오름차순 정렬했습니다.

 

실습 파일 다운로드

전국종량제봉투가격.xlsx

0.09MB

가격표.xlsx

0.01MB

완성 파일 다운로드

완성.zip

0.19MB

 

전국종량제봉투가격.xlsx 파일은 전국 지자체별 종량제 봉투 가격 목록이 있는 파일입니다.

가격표.xlsx 파일은 전국종량제봉투가격 중 선택한 지자체 봉투 가격을 보여주는 파일입니다.

 

VLOOKUP 함수에서 사용할 검색 필드 만들기

먼저 전국종량제봉투가격 파일에서 필요한 작업을 합니다.

 

파일을 살펴보니 VLOOKUP 함수를 이용해 특정 지자체를 선택하려면 유일하게 구분되는 필드가 있어야 하는데, 중복되는 값이 많아 함수를 쓸 수 없습니다.

시도명은 당연히 안되고, 시군구명도 같은 이름으로 된 줄이 있기 때문에 해당사항 없습니다.

시도명+시군구명+종량제봉투종류+종량제봉투처리방식+종량제봉투용도+종량제봉투사용대상을 해서 새 필드를 하나 만들고 이 값을 기준으로 VLOOKUP 함수를 이용해 찾도록 합니다.

 

A열을 선택하고 [삽입]을 선택해 새 열을 추가합니다.

수식을 써서 여러 개 열을 하나로 합칩니다.

=B3&C3&D3&E3&F3&G3

채우기 핸들에서 더블클릭해서 아래로 모두 채웁니다.

범위 선택된 상태에서 마우스 오른쪽 클릭해서 [복사]를 선택합니다.

다시 마우스 오른쪽 클릭해서 [선택하여 붙여넣기-값]을 선택해 수식이 아닌 값이 되도록 만듭니다.

 

[A3:AA751] 셀 범위를 선택하고 [이름 상자]에 '목록'이라고 입력한 뒤 <Enter>를 눌러 이름 정의 합니다.

 

이름 정의할 데이터 정리 작업(중복 항목 제거)

이제 가격표 파일에서 작업을 합니다.

 

새 시트를 삽입합니다.

전국종량제봉투가격 파일 [B3:B751] 셀 범위를 복사해서 가격표 파일 Sheet2 시트 [A1] 셀에 [값]으로 붙여넣습니다.

[데이터]-[데이터 도구]-[중복된 항목 제거]를 선택해 중복 항목을 제거하고 고유 항목만 남깁니다.

[A1:A17] 셀 범위를 복사합니다.

[B1] 셀을 선택하고 마우스 오른쪽 클릭해 [선택하여 붙여넣기] 대화상자를 표시하고 [행/열 바꿈]을 선택합니다.

이제 A열은 필요없으니 마우스 오른쪽 클릭해서 [삭제]를 선택해 없앱니다.

 

전국종량제봉투가격 파일 [B3:C751] 셀 범위를 선택해 복사하고, 가격표 파일 Sheet2 시트 [S1] 셀에 [선택하여 붙여넣기-값]을 선택해 붙여넣습니다.

[중복된 항목 제거]를 선택해 중복 항목은 제거하고 고유 항목만 남깁니다.

[T1:T18] 셀 범위 강원도에 해당하는 부분만 범위 선택해서 [잘라내기]하고 [A1] 셀 강원도 아래인 [A2] 셀에 붙여넣기 합니다.

나머지 시군구명도 잘라내기해서 해당 시도명 아래 붙여넣습니다.

S열은 필요 없으니 삭제합니다.

 

전국종량제봉투가격 파일 [D3:G751] 셀 범위를 선택해 복사하고, 가격표 파일 Sheet2 시트 [R1] 셀을 선택하고 [선택하여 붙여넣기-값]으로 붙여넣습니다.

[R1] 셀에 커서를 두고 <Ctrl + Shift + 아래쪽 화살표>를 눌러 범위 선택합니다.

[데이터]-[데이터 도구]-[중복된 항목 제거]를 선택합니다.

[중복된 항목 제거 경고] 대화상자가 나타납니다.

이 경고창이 나타나는 이유는 [R1:R751] 셀 범위와 연속된 다른 셀들이 있는데, 다른 셀들을 같이 범위 선택하지 않았기 때문에 '혹시 범위를 잘못선택하지 않았니?' 하며 과한 친절을 베푼 것입니다.

'현재 선택 영역으로 정렬'을 선택하고 [중복된 항목 제거]를 클릭합니다.

[중복 값 제거] 대화상자에선 기본 상태에서 [확인]을 클릭합니다.

중복 값을 제외하고 5개 항목만 남았습니다.

같은 방법으로 S, T, U열도 중복 값을 제거합니다.

 

이름 정의하기

이제 이름 정의를 합니다.

[A1:Q1] 셀 범위를 선택하고 [이름 상자]에 '시도명'을 입력한 뒤 <Enter>를 누릅니다.

[A2] 셀에 커서를 두고 <Ctrl + Shift + 아래쪽 화살표>를 눌러 셀 범위를 선택하고 [이름 상자]에 [A1] 셀에 있는 시도명을 입력한 뒤 <Enter>를 누릅니다.

B~Q열까지 같은 방법으로 [이름 정의]를 합니다.

[R1:R5] 셀 범위를 선택하고 [이름 상자]에 '종량제봉투종류'를 입력한 뒤 <Enter>를 누릅니다.

이름 상자에 적는 이름은 전국종량제봉투가격 파일 [D1] 셀 필드명을 적었습니다.

[S1:S5] 셀 범위를 선택하고 [이름 상자]에 '종량제봉투처리방식'을 입력한 뒤 <Enter>를 누릅니다.

[T1:T2] 셀 범위를 선택하고 [이름 상자]에 '종량제봉투용도'를 입력한 뒤 <Enter>를 누릅니다.

 

[U1:U8] 셀 범위를 선택하고 [이름 상자]에 '종량제봉투사용대상'을 입력한 뒤 <Enter>를 누릅니다.

이제 이름 정의가 다 되었습니다.

이름 정의를 하는 중 이름을 잘못 적었거나 범위를 잘못 선택하고 이름을 정의했다면 [수식]-[정의된 이름]-[이름 관리자]를 선택해 수정할 수 있습니다.

이름 정의가 제대로 다 되었는지 확인할 수도 있습니다.

이름 정의에 실수가 있으면 겉으로는 잘 동작하는 듯 하지만 완전 잘못된 결과가 나올 수 있기 때문에 주의를 해서 정확하게 작업해야 합니다.

 

선택하기 편하도록 데이터 유효성 검사 기능으로 목록 나타내기

가격표 파일 Sheet1 시트로 가서 [B5] 셀에서부터 [데이터 유효성 검사] 기능을 설정합니다.

[B5] 셀에 커서를 두고 [데이터]-[데이터 도구]-[데이터 유효성 검사]를 누릅니다.

[데이터 유효성] 대화상자에서 [제한 대상]은 '목록', [원본]에는 =시도명을 입력하고 [확인]을 클릭합니다.

이제 [B5] 셀에 커서를 두면 오른쪽에 역삼각형이 표시되고, 역삼각형을 누르면 선택할 수 있는 항목이 나타나서 입력을 쉽게 할 수 있도록 도와줍니다.

[C5] 셀을 선택하고 [데이터]-[데이터 도구]-[데이터 유효성 검사]를 누릅니다.

[제한 대상]은 '목록', [원본]에는 =indirect($B$5)를 입력하고 [확인]을 클릭합니다.

$B$5는 [B5] 셀을 선택하면 자동으로 입력됩니다.

[C5] 셀 시군구명은 [B5] 셀에서 선택한 시도명에 따라 목록이 달라져야 합니다.

그래서 이름 정의할 때도 시도명에 해당하는 시군구명을 따로 시도명으로 이름 정의해 두었습니다.

INDIRECT 함수로 [B5] 셀에 나타나는 값을 이름으로 변환해서 [C5] 셀에 목록으로 나타나도록 만들었습니다.

이런 작업을 흔히 '이중 유효성 검사'라고 합니다.

 

2020.09.25 - 이중 유효성 검사 목록 만들기(데이터 유효성 검사, INDIRECT 함수)

이중 유효성 검사 목록 만들기(데이터 유효성 검사, INDIRECT 함수)

 

[D5] 셀에는 [원본]에 =종량제봉투종류를 입력하고 [확인]을 클릭합니다.

[E5] 셀에는 [원본]에 =종량제봉투처리방식을 입력하고 [확인]을 클릭합니다.

[F5] 셀에는 [원본]에 =종량제봉투용도를 입력하고 [확인]을 클릭합니다.

[F7] 셀에는 [원본]에 =종량제봉투사용대상을 입력하고 [확인]을 클릭합니다.

[데이터 유효성 검사] 기능을 이용해 사용자가 선택할 수 있도록 설정을 했습니다.

모두 첫번째 항목을 선택해 둡니다.

 

VLOOKUP 함수 작업

이제 VLOOKUP 함수를 이용해 값을 찾아 오도록 [B9] 셀부터 수식을 작성합니다.

[B9] 셀에 커서를 두고 =vl을 입력해 나타나는 함수 목록에서 VLOOKUP을 더블클릭해 선택하고 <Shift + F3>을 눌러 [함수 인수] 대화상자를 표시합니다.

첫번째 Lookup_value 인수는  [B5], [C5], [D5], [E5], [F5], [F7] 셀을 순서대로 선택하면서 문자연결연산자 &(앰퍼센트)를 중간 중간 넣어 하나의 문자열이 되도록 입력합니다.

 그런 다음 이 수식을 다른 셀에 복사할 것이기 때문에 입력된 내용을 범위 선택한 뒤 <F4>를 눌러 절대 참조합니다.

두번째 인수 Table_array 인수는 같은 폴더에 저장된 다른 파일의 정의된 이름을 써야 합니다.

[전국종량제봉투가격.xlsx]가격!목록을 입력합니다.

대괄호( [  ] ) 안에 파일명과 확장자까지 적고, 시트명을 적은 뒤 느낌표( ! )를 입력하고, 마지막에 정의된 이름을 적습니다.

세번째 Col_index_num 인수는 8이고, 네번째 인수 Range_lookup 인수는 0입니다.

작성한 수식입니다.

=VLOOKUP($B$5&$C$5&$D$5&$E$5&$F$5&$F$7,전국종량제봉투가격.xlsx!목록,8,0)

해당 값이 0입니다.

[C5] 시군구명을 '속초시', [E5] 종량제봉투처리방식을 '소각용', [F5] 종량제봉투용도를 '음식물쓰레기', [F7] 종량제봉투사용대상을 '가정용/사업용'으로 선택하면 50이 나옵니다.

값을 바꾸는 사이 [B9] 셀 결과값이 #N/A로 여러 번 표시됩니다.

이유는 [B5] 셀부터 선택한 조건에 해당하는 항목을 VLOOKUP 함수로 찾을 수 없었기 때문입니다.

목록에 데이터 자체가 없기 때문에 '값을 찾을 수 없다'라는 정상적인 오류메시지를 표시한 것입니다.

그냥 놔 두어도 상관없지만, 엑셀을 잘 모르는 사람이 보면 '오류 났다'고 투덜댈테니 IFERROR 함수로 처리를 합니다.

[B9] 셀을 더블클릭해 편집 상태로 전환하고 이퀄(=) 바로 뒤에 커서를 둔 상태에서 if까지 입력한 뒤 나타나는 함수 목록에서 IFERROR을 더블클릭해 선택합니다.

IFERROR 부분에 커서를 두고 <Shift + F3>을 눌러 [함수 인수] 대화상자를 표시합니다.

두번째 Value_if_error 인수에 0을 입력합니다.

작성한 수식입니다.

=IFERROR(VLOOKUP($B$5&$C$5&$D$5&$E$5&$F$5&$F$7,전국종량제봉투가격.xlsx!목록,8,0),0)

[B9] 셀을 복사하고 [C9:F9, B11:F11,B13,F13] 셀 범위를 한꺼번에 선택해서 붙여넣기 합니다.

[C9] 셀을 더블클릭해 편집상태로 전환하고 <Shift + F3>을 눌러 [함수 인수] 대화상자를 나타내고 세번째 인수 Col_index_num을 9로 바꿉니다.

이런 식으로 나머지 복사된 셀 수식도 1씩 증가된 값으로 수정합니다.

[F13] 셀 Col_index_num 인수는 22입니다.

[C5] 시군구명을 '강릉시', [E5] 종량제봉투처리방식을 '매립용', [F5] 종량제봉투용도를 '생활쓰레기', [F7] 종량제봉투사용대상을 '기타'로 선택하면 여러 셀에 값이 표시됩니다.

[B9] 셀을 복사해 [B15] 셀에 붙여넣고 <Shift + F3>을 눌러 [함수 인수] 대화상자 Value_if_error 인수에 0 대신 ""를 입력하고, Col_index_num 인수는 23으로 바꿉니다.

[B15] 셀 수식을 복사해서 [C15:F15] 셀 범위에 붙여넣기한 뒤 Col_index_num 인수를 1씩 증가된 값으로 수정합니다. 

[F15] 셀 Col_index_num 인수는 27입니다.

필요한 수식 작업은 모두 마쳤습니다.

 

조건부 서식으로 결과를 보기 좋게 만들기

지금 상태에선 보기가 불편하기 때문에 서식 작업을 합니다.

전국종량제봉투가격 파일 [H3:V751] 셀 범위를 선택하고, 선택한 영역 왼쪽 위 노란색 느낌표 표시를 눌러 [숫자로 변환]을 선택합니다. 그리고 저장합니다.

가격표 파일 Sheet2 시트 [B9:F15,B11:F11,B13:F13] 셀 범위를 한꺼번에 선택하고 [홈]-[스타일]-[조건부 서식]-[새 규칙]을 선택합니다.

'수식을 사용하여 서식을 지정할 셀 결정'을 선택하고 [다음 수식이 참인 값의 서식 지정]에 =B9<>0을 입력합니다.

B9는 [B9] 셀을 선택해 입력하는데, 셀을 선택하면 $B$9로 표시되니 <F4>를 3번 눌러 상대 참조로 바꿉니다.

[서식]을 클릭해서 [채우기] 탭에서 적당한 색을 선택합니다.

가격 부분에 0이 아닌 값이 나타나면 눈에 띄도록 채우기 색이 나타납니다.

 

작업을 완료했습니다.

 

작업을 완료하는데 쓰인 기능, VLOOKUP 함수, IFERROR 함수, 중복된 항목 제거, 이름 정의, 데이터 유효성 검사, 조건부 서식 기능 모두 소개된 것들입니다.

이 강좌에서는 기능들을 조합해 원하는 작업을 완료하는 방법을 소개했습니다.

 

필요하신 분께 도움이 되길 바랍니다.