[필기 풀이] 컴활2급 기출 2016년1회-32번 엑셀 주민등록번호 함수

컴퓨터활용능력 2016년도 1회 필기 기출 문제 32번을 풀이한 것이다. 

엑셀로 주민등록번호를 이용하여 남/여를 가리는 수식을 작성하였다. 바른 수식을 찾아보자.

32. [A1] 셀에 ‘851010-1234567’과 같이 주민등록번호가 입력되어 있을 때, 이 셀의 값을 이용하여 [B1] 셀에 성별을 ‘남’ 또는 ‘여’로 표시하고자 한다. 다음 중 이를 위한 수식으로 옳은 것은? (단, 주민등록번호의 8번째 글자가 1이면 남자, 2이면 여자임)

① =CHOOSE(MID(A1,8,1), "남","여")

② =HLOOKUP(A1, 8, B1)

③ =INDEX(A1, B1, 8)

④ =IF(RIGHT(A1,8)="1", "남", "여")

풀이 :

엑셀에서 주민등록번호로 남여를 구분하는 수식은 많이 만나고 또 필요하다. 이런 필요에 따라 컴퓨터활용능력에도 출제되었다. 작성된 주민등록번호로 남여를 가리는 수식을 분석해 보자.

[엑셀 실무] 주민등록번호로 한국 나이 계산하기 :

http://secstart.tistory.com/711

엑셀 주민번호로 남여 찾기 문제 풀이

주민번호에서 남여를 구분하는 방법은 주민등록번호 뒷자리 1번째 자리가 1이나 3이면, '남', 2나 4이면 '여'이다.

1번) =CHOOSE(MID(A1,8,1), "남","여")

엑셀 CHOOSE 함수와 MID 함수의 간략한 설명부터 보자. 

CHOOSE( 정수인덱스, 결과1, 결과2 ) : 인덱스가 반환하는 숫자의 결과를 반환한다.

MID( 문자열, 시작문자위치, 문자수 ) : 문자열의 시작 문자 위치부터 문자수만큼을 반환한다.

[그림1] 엑셀 주민번호 찾기 - CHOOSE

 

=MID( A1, 8, 1 ) : A1 셀의 값, '851010-1234567' 의 8번째 문자를 시작으로 1문자를 추출한다. 1 이 반환된다.

=CHOOSE( 1, "남", "여" ) : 첫번째 인수가 1이다. 따라서, 결과1 의 값인 '남' 이 반환된다.

[엑셀 모바일] CHOOSE 함수, 정수로 목록 조회 선택 참조하기 :

http://secstart.tistory.com/752

[엑셀 모바일] MID 함수로 요구 위치에서 텍스트 추출하기 :

http://secstart.tistory.com/737

2번) =HLOOKUP(A1, 8, B1)

엑셀 HLOOKUP 함수를 간략히 설명한다.

HLOOKUP( 찾을값, 테이블범위, 반환행, [찾는법] ) : '찾을값' 을 '테이블범위' 의 첫번째 행에서 찾는다. 이후, 검색된 열에서 '반환행' 의 값을 결과로 취한다.

[그림2] 엑셀 잘못된 주민번호 수식 - HLOOKUP

따라서, 보기의 수식은 처음부터 잘못되었다. HLOOKUP 함수를 사용한 것부터가 잘못된 것이다. HLOOKUP 함수는 문자열에서 문자를 찾는 함수가 아니라, 테이블 범위에서 셀 값을 찾는 함수이다. 그래서, 이 함수로는 남/여를 구분하는 1문자를 얻을 수는 없다.

또, 수식의 찾는값인 전체 주민등록번호를 8 이라는 숫자에서는 찾을 수 없다. 따라서, 수식의 결과는 #N/A 가 된다. 찾기는 HLOOKUP, 이 두 키워드로 말도 안되는 함정 보기를 만든 것이다.

[엑셀 모바일] HLOOKUP 함수로 수평 검색 후 지정 행 반환하기 :

http://secstart.tistory.com/755

3번) =INDEX(A1, B1, 8)

INDEX 함수를 간략히 배워본다.

INDEX( 배열범위, 행번호, [열번호] ) : 범위에서 행번호와 열번호로 셀 위치를 얻어 그 값을 반환한다.

[그림3] 엑셀 잘못된 주민번호 수식 - INDEX

INDEX의 첫번째 인수는 범위이다. 그런데, A1 셀은 하나의 셀이다. 따라서, 이 셀만으로 행번호와 열번호로 원하는 위치의 셀 값을 얻을 수 없다. INDEX 함수 역시, HLOOKUP 함수와 마찬가지로 범위에서 셀값을 얻는 함수라는 것을 기억하자.

또 하나 추가하면, 이 보기 수식은 B1 셀에 입력된다. 그런데, 행번호로 B1 셀이 사용되었다. 순환 참조가 발생하는 것이다. 잘못된 수식이다.

[엑셀 모바일] INDEX 함수 행 열 주소로 셀 참조하기 - 배열 형식 :

http://secstart.tistory.com/750

4번) =IF(RIGHT(A1,8)="1", "남", "여")

엑셀 IF 함수와 RIGHT 함수를 간략히 보자.

IF( 조건, 참, 거짓 ) : 조건을 만족한다면 참의 인수를, 거짓이라면 거짓 인수를 결과로 반환한다.

RIGHT( 텍스트, 숫자 ) : 텍스트의 가장 오른쪽에서부터 숫자 만큼의 갯수를 결과로 반환한다.

[그림4] 엑셀 잘못된 주민번호 수식 - IF RIGHT

=RIGHT( A1, 8 )="1" : A1 셀의 값, '851010-1234567' 의 오른쪽에서 8개의 문자를 얻는다. "-1234567" 을 "1" 과 비교하는 것이다. 서로 다른값이기에 결과는 FALSE 를 반환한다.

=IF( FALSE, "남", "여" ) : 거짓값이 조건이라, 거짓일때의 인수 "여" 가 결과로 반환된다.

이번 보기의 수식은 RIGHT 함수의 사용이 잘못된 것이다. 남/여의 구분을 하는 주민번호는 8번째 1자리 (숫자)문자이다. 그러나, RIGHT 함수로는 중간의 1자리 문자를 얻을 수 없다. 1번) 보기의 MID(A1,8,1) 이 중첩 사용되었다면 4번도 바른 수식이 된다.

[엑셀 모바일] 논리 함수 IF로 조건 비교후 참 거짓 반환 :

http://secstart.tistory.com/682

[엑셀 모바일] RIGHT 함수로 텍스트의 오른쪽에서 문자열 추출 :

http://secstart.tistory.com/736

이런 함수식 문제를 보다 빠르게 풀기 위해서는 필요없는 보기에 너무 큰 시간을 낭비하지 않아야 한다. 그 방법은 함수를 크게 분류하여 정답에서 멀어지는 보기를 버리는 것이다.

이번 문제는 텍스트에서 1문자를 얻어 비교해야 한다. 따라서 범위에서 셀을 찾는 HLOOKUP, INDEX 함수는 정답에서 제외된다. 1번)과 4번)이 정답 중 하나, 그런데 4번)의 IF 함수는 8문자를 1문자와 비교하고 있다. 오답..

1번)이 정답으로 남게되는 것이다.

주민번호 관련 추가 링크 :

[엑셀 문답] 주민등록번호에서 생년월일 추출하기 :

http://secstart.tistory.com/595

[엑셀 문답] 엑셀로 주민등록번호 감추기 :

http://secstart.tistory.com/610

[엑셀 실무] DATEDIF 함수로 주민번호 만 나이 구하기 :

http://secstart.tistory.com/720

정답 : 1번

[필기 풀이] 컴활2급 기출 2016년1회-31번 엑셀 이름 정의 중첩 함수 :

http://secstart.tistory.com/911

마늘빵.