MS 엑셀에서 유의(유념)할 것.. 셀서식

vlookup 함수 등 많은 함수들을 활용할 줄 아는 것은 좋은데,
기본적인 부분을 잘 알아야지 나중에 흔들리지 않는다..
한마디로 기본기가 있어야 한다. 오늘은 기본기에 대하여 설명. ^^>

일단 엑셀의 기본기 하면 두가지 정도가 되겠는데,
하나는 절대좌표, 상대좌표, 혼합좌표.
하나는 셀서식. 오늘 알아볼 것은 바로 셀서식.

A 명단과 B 명단을 비교하는데,
A 명단은 이전달 명단, B 명단은 이번달 명단.
두 명단을 비교해서 탈퇴와 신규를 가려내는 것이 오늘의 문제.

일단 방법론은 이렇다.
B 명단에서 각각의 인원에 대하여 A 명단과 대조하여 A 명단에 있으면 '기존' 인원, 없으면 '신규' 인원.
A 명단에서 각각의 인원에 대하여 B 명단과 대조하여 B 명단에 있으면 '기존' 인원, 없으면 '탈퇴' 인원.

이제 하나하나 작업해 보자. A 명단과 B 명단에서 서로 동일한 인원을 찾는 것은 주민번호로 한다.
아래 내용이 A 명단, 다시 말해 이전달 명단.

아래 내용이 B 명단, 다시 말해 이번달 명단. 맨 처음 작업한 분이 A 명단과 B 명단을 가나다 순으로 정렬은 해 두었다.
그러나, 데이타를 눈으로 일일이 확인하면 시간이 다 간다. 그리고 실수도 하게 마련이다.
이 작업을 하는데 정확히 딱 5분도 안 걸렸는데 말이다. 데이타는 두 워크시트를 합쳐서 591명이다.

먼저, A 명단과 B 명단은 별도의 워크 시트에 저장되어 있었다.
각 워크 시트 명단의 G 칼럼 영역에 A 명단은 '이전', B 명단은 '현재' 라고 입력하자.
설마 각 셀마다 하나씩 입력하는 것은 아니겠죠..?
당연히 영역을 선택한 다음에 글자를 입력하고 <Ctrl> <Enter> 하면 한번에 입력이 완료된다.

다음, vlookup 함수를 사용하여 각 영역을 비교한다.
이번에는 일일이 가르쳐 주지 않고 함수 사용법만 알려 준다. ^^>
=VLOOKUP(C2, 'B 명단'!$C$3:$G$293, 5, FALSE)
'A 명단' 워크시트 의 C2 셀 (주민번호)을
'B 명단' 워크시트의 $C$3 셀부터 $G$293 셀까지의 범위에서 (값을 가져오는 5번째를 범위에 잡아야 하는 것에 유의)
5번째 값을 가져오는데 ($C 칼럼이 1번째 값인 것에 유의),
FALSE 는 범위를 허용하느냐의 여부이다. FALSE 는 당연히 범위를 허용하지 않고 정확히 같아야 한다는 것.
다시 말해 위 함수는 이전 명단의 주민번호를 현재 명단에서 찾아서
동일한 것이 나오면 5번째 값이 '현재' 라는 문자열을 값으로 설정한다는 것이다.
가져오는 값과 범위를 설정할때.. 가져오는 값은 6번째 인데, 범위는 5번째 칼럼까지만 설정하는 분이 있다.
이러면 값을 가져올 수 없어 대략 낭패다. ㅠㅠ
그리고 범위의 첫번째 칼럼이 1번인데 그 오른쪽을 1번으로 착각하여 가져오는 값의 순번을 틀리는 분이 있는데,
이런 경우에도 이상한 값을 가져오므로 대략 낭패다. ㅠㅠ

$C$3:$G$293 이라고 절대좌표를 사용하는 이유는,
C2 다음의 주민번호는 C3, C4, .. 이런식인데,
상대좌표나 혼합좌표를 쓰면 비교하는 대상범위가 달라지기 때문이다.
예를들어 워크시트 'A 명단' 의 C2 에 워크시트 'B 명단' 의 C3:G293 라고 입력하였다면,
'A 명단' 워크시트의 C3 에서는 'B 명단' 워크시트의 C4:G294 가 되어서..
'B 명단' 워크시트의 C3 가 대상범위에서 제외된다. 당연히 결과값이 틀어질 수 있다.
따라서 엑셀에서는 절대좌표를 쓰느냐, 상대좌표 또는 혼합좌표를 쓰느냐에 따라 결과가 달라질 수 있는 것이다.

이런식으로 워크시트 'B 명단' 에 대해서도 vlookup 을 사용하면 된다.
그런데, 명단이 동일하지 않아서 '신규 인원' 과 '탈퇴 인원' 이 있기 때문에..
이들에 대해서는 오류가 나기 마련이다. 이것을 보완하기 위해서는 오류가 났을때 어떻게 하라는 조치가 필요하다.
if 문과 iserror 라는 함수를 이용하면 된다.
=if( iserror(vlookup함수문), "", vlookup함수문)
vlookup함수문은 위에서 설명한 것을 그대로 쓰면 된다.
iserror 함수는 값에 오류가 있으면 true 를 오류가 없으면 false 값을 가져온다.
if 문은 첫번째 인자가 수식, 두번째는 수식이 true 인 경우, 세번째는 수식이 false 인 경우 해당 문장을 값으로 설정한다.
자 이렇게 하면 오류는 '신규 인원' 이나 '탈퇴 인원' 에는 공백값이 들어가게 된다.
그런데 여기서 좀더 재미있게 하려면 그냥 "" 를 사용할 것이 아니라 상황에 맞게 "신규", "탈퇴" 라고 하면 되겠다.
'A 명단' 워크시트에는 =if( iserror(vlookup함수문), "탈퇴", vlookup함수문) 이라 하고,
'B 명단' 워크시트에는 =if( iserror(vlookup함수문), "신규", vlookup함수문) 이라 하자.

자,  이제 거의 다 되었다.
그런데, 처음 기본 데이타를 작성한 분이 실수를 했다.
바로 주민번호에서 실수가 있었다.
숫자의 경우 '일반' 서식이면 일정 수치를 넘어설때 '7.77777E+12' 와 같이 값을 보여주게 된다.
당연히 한쪽에는 '텍스트' 서식이고 다른 쪽은 '일반' 서식이었다면 vlookup 함수가 이 둘을 다르게 볼 것이고.
'기존 인원' 으로 분류해야 함에도 불구하고 한쪽에선 '탈퇴 인원', 다른쪽에선 '신규 인원' 으로 체크된다.
사실 오늘은 이것을 설명하는 것이 포인트인데 서설이 길었다.

지금 즉시 엑셀 프로그램을 켜고 A1 셀에 자신의 주민번호를 입력하고 엔터키를 눌러보자.
'7.77777E+12' 과 비슷하게 표시가 될 것이다. '열너비' 가 작아서 그렇다고요..? 열너비를 넓혀 보세요. ^^>
'일반' 서식일 때는, 눈으로 보기에 정확히 어떤 내용이 입력된 것인지 알기 어려운 상황이 발생한다.
이렇게 입력된 내용이 엉뚱하게 나오지 않도록 하기 위해서는 '텍스트 서식' 으로 설정하는 것이 필요하다.
이를테면, 숫자 '0001234' 를 '일반' 서식에 입력하면 '1234' 만 나오는데, 실제값은 '0001234' 여야 한다는 식으로..
실제 작업을 하다보면 '일반' 서식은 초보자에게는 좋은데 중급 이상의 사용자에게는 불편하다는 것을 알 수 있다.
또 하나 예를 들면, '3/4' 라고 입력하면 '일반' 서식에서 '3월 4일' 로 인식을 한다.
그러나 일자를 입력하려고 한 것이 아니었다면 대략 낭패다.
'일반' 서식은 '텍스트' 서식으로 변경할 필요가 있다.

자, 그렇다면.. 셀서식을 '텍스트' 로 변경해 보자.
주민번호가 입력되어 있는 A1 셀을 마우스 오른쪽 버튼으로 클릭하고 '셀 서식(F)...' 를 클릭한다.

이제 아래 셀서식 창에서 '표시 형식' 을 '일반' 이 아니라 '텍스트' 로 바꾸고 확인 버튼을 클릭한다.

큰소리 치더니만 아직도 '7.77778E+12' 가 나오지 않느냐 반문하실 분도 있겠다.
이것은 A1 셀에 들어 있는 값에 '일반' 셀서식이 설정되었다 뿐이지,
값을 '텍스트' 에 맞게 정확히 바꾸지는 않았기 때문이다.
눈썰미가 있는 분은 아셨겠지만 아래 그림을 보면 보통의 '7.77778E+12' 가 아니다.
아까는 오른쪽으로 정렬되어 있었는데 지금은 왼쪽으로 정렬되어 있는 것이 증거다.
'일반' 셀서식에서 숫자를 입력하면 오른쪽 정렬,
문자를 입력하면 '왼쪽' 정렬이 되는 것을 기억하시려나..?

그렇다면 A1 셀을 클릭하고 키보드의 'F2' 키를 누른 다음 다시 'Enter' 키를 눌러보라.
이제, '7777777777777' 이라고 왼쪽으로 정렬되어 나온다.
그래도 미심쩍다..? A1 셀 왼쪽 끝에 세모 표시가 되지 않느냔 말이다.

이것은 해당 값이 '일반' 으로 있을때는 엑셀이 자기(?) 맘대로 알아서 생각하면 되는데,
'텍스트' 로 바꾸다 보니 이거 정말 텍스트인지 알 수 없다는 것이다.
엑셀 입장에서는 숫자로만 구성된 A1 셀의 값이 숫자인지 텍스트인지 구별하기 어려워서 표시를 해 놓는 것이다.
숫자만 입력해 놓고 '텍스트' 셀서식으로 변경하는 것이 엑셀 입장에서는 편치 않기 때문이다.
A1 셀을 클릭하면 아래 그림과 같이 나오고 여기서 '숫자로 변환(C)' 을 선택하면,
다시 '일반' 서식으로 전환이 된다. 당연히 변환한 뒤의 모습인 '7.77778E+12' 로 표시된다.

데이타의 양이 한둘이면 이런식으로 <F2> <Enter> 를 일일이 해 주면 되는데 데이타가 많다면 어떻게 해야 할까..?
방법이 있다. B1 셀에 다음과 같이 입력해 보라.
=TEXT(A1,"0")
text 함수의 두번째 인자에 "0" 을 사용하였다는 것을 유의하자. 다른 숫자를 입력하면 입력한 숫자만 표시된다. ^^>
이제 A1 셀에 '텍스트 서식' 의 숫자 '7777777777777' 이 있든 '일반 서식' 의 숫자 '7.77778E+12' 이든,
'텍스트 서식' 이 반영되지 않고 살짝 설정해 놓기만 한 무늬만 숫자 '7.77778E+12' 이든 어떻든 간에..
우리가 바꾸려고 하는 '텍스트 서식' 의 숫자 '7777777777777' 이 B1 셀이 들어가 있다.
이제 나머지 셀들에 대해서도  text 함수를 사용하여 알맞게 변경해 놓으면 되겠다.

이상.
도움이 필요한 분은 질문을 댓글로 달아 주세요.