컴퓨터 활용 2급 실기 데이타 유효성 검사와 match 함수, index 함수 사용하기

역시, 엑셀의 꽃은 함수가 아닐까 합니다. 그러나..이 함수가 정말 어렵네요. 종류도 어마어마하고, 종류별로 사용하는 방법도 무궁무진하고~ 이리저리 조합해서 수식을 만들수도 있고....  사무실에선, 딱 정해준 함수가 없으니까, 아는 한도 내에서 함수들을 이용해 궁리궁리해서 수식을 만들어 쓸 수 있습니다. 하지만!! 컴퓨터 활용 실기 시험에선, 사용할 함수를 정해주죠...그걸 가지고 문제에 만족하는 결과값을 찾아야 하므로 자주 출제되는 함수의 사용방법은 숙지를 하고 있어야 합니다.

 

 

이번에 연습을 하다보니 데이터유효성검사와 match 함수를 이용한 문제가 있더군요. 어라...데이타 유효성 검사는 뭐지? 라는 의문과 함께..... 아리송한 이 문제를 정리해 놓아야 겠다는 생각이 들더군요. 잘 활용할 수 있다면, 자료를 만들때에도 훨씬 수월하게 만들 수 있을 함수 Match 함수~  지금부터 포스팅 해봅니다.

 

연습문제>>

제품명을 유효성검사로 제품명의 목록을 C30에 표시하고, C31에 유효성 검사로 1사분기, 2사분기, 3사분기, 4사분기가 목록으로 나오도록 유효성 검사를 하세요. MATCH 함수를 이용해서 D30에 제품명의 행번호를 나타내고, D31에 분기의 열번호를 나타내세요. D30의 행번호와 D31의 열번호에 해당되는 매출내역을 C33에 INDEX 함수를 이용하여 나타내세요. (인천광역시 시민사이버교육센터 자료 퍼옴)

 

 

 

문제에서 보면, 데이타 유효성 검사를 이용해 c30에 제품의 목록을 표시하라고 합니다.  

 

우선 커서를 C30에 놓은 후, 데이타 > 데이타 유효성 검사 버튼을 누릅니다~

 

 

 

 

 데이터 유효성 검사를 클릭합니다. 클릭을 하면, 데이타 유효성 검사 메뉴가 떠요.

 

 

클릭을 하시고, 설정에서 제한대상을 "목록"으로 변경합니다. 원본부분은 리스트를 작성할 값이 있는 곳, B4..BF27 을 지정해 주고 확인을 누릅니다.

 

 

위와 같이 제품 리스트를 C30 에 채울 수 있습니다.  

 

 

 

이번에는 분기에 해당하는 열의 부분을 유효성 검사를 통해 C31에 넣습니다. 데이터 유효성 검사를 누른후, 제한대상을 목록으로 변경하고, 원본을 C3..F3으로 지정한 후 확인을 누릅니다.

 

위와 같이 목록을 C31에 넣을 수 있습니다.

 

유효성 검사를 완료한 후, MATCH 함수를 이용해 셀의 위치를 확인할 수 있습니다.

 

커서를 D30에 놓고, MATCH 함수를 찾으면, 위와 같은 인수입력란이 나옵니다. LOOKUP_VALUE 란에는 찾을 값, 즉 데이터 유효성 검사에서 찾아놓은 제품명이 있는 곳이 되겠죠. 그래서 C30을 입력합니다. LOOKUP_ARRAY는 리스트가 있는 셀의 범위를 말합니다. 셀 범위는 B4..B27이 됩니다. MATCH_TYPE은 1,0, -1 이 있습니다. 0은 정확히 '일치', 1은 '보다작음', -1은 '보다큼' 이라는데, 안써줘도 무방합니다.  이렇게 입력하면, 행의 위치를 제품명이 들어 있는 행의 위치를 알려주게 됩니다.

 

 

요런식으로 입력하면 되요~  D31에도 MATCH 함수를 이용하여 열의 위치를 알려줍니다.

 

 

 확인 누르면, 아래와 같이 D30과 D31에 C30과 C31에 표시된 제품명과 분기의 행과 열의 위치가 표시됩니다.

 

이렇게 제품명과 분기의 위치를 파악하면, INDEX 함수를 이용해, 지정 셀의 데이타를 추출할 수 있게 됩니다.  

INDEX 함수의 인수는 Array, Row_num, Column_num 이 있습니다. Array는 데이타의 범위 입니다. 여기서 데이타의 범위는 제품명(행)과 분기(열) 안의 데이타(매출내역)를 말합니다. 따라서 여기에선 c4..f27이 데이타의 범위가 됩니다. Row_num은 행의 위치를 말하며, 여기선 D30에서 match 함수로 구해놓은 값을 정해주면 됩니다. 유효성 검사로 리스트의 제품명을 바꿔 설정할때마다, d30의 값은 변합니다. Column_num은 열의 위치이며, d31의 값을 읽어줍니다. 이렇게 적어주고, 확인~

 

 

제품명을 우미 피넛샌드 (데이타의 1행), 분기를 1사분기 (데이타의 1열)로 설정하면, 그 값인 15,836 이 매출내역에 출력되는 것을 볼 수 있습니다.

 

어렵지만, 보면 볼수록 매력적인 엑셀 MATCH 함수와, INDEX 함수 그리고 잘 알면 쉽게 쓸수 있는 데이타 유효성 검사 였습니다~