엑셀(excel) 함수 tip 37 - (code 및 char함수로 일련번호 매기기)
엑셀(excel)작업시 일련번호를 매기는 경우가 대부분입니다. 모든 데이터는 전체의 수를 알수 있도록 첫번째 행머리글에 일련번호를 삽입을 합니다. 일련번호를 삽입하다 보면 중간에 빈셀이 있는 경우 또는 셀병합이 되어있는 경우에는 자동채우기가 되지 않습니다. 이때에도 자동일련번호를 매길 수 있는 함수가 바로 code 및 char 함수입니다.
▶ 문자와 코드번호
하단과 같이 CODE함수와 CHAR함수를 사용하기 위해서는 문자와 코드의 조합을 알아야 합니다. 숫자, 영어대문자, 영어소문자에 대한 코드번호가 각각 있습니다. CODE함수로 문자를 코드번호로, CHAR함수를 이용하새 코드번호를 문자로 나타낼수 있습니다.
▶ROW 함수로 일련번호 매기기
보통 일련번호를 매길 경우에 하단처럼 셀의 최 상부에 1을 기록한 후 셀을 선택하여 하단으로 드래그 하면 1값이 복사되기 때문에 하단도 1로 채워집니다. 따라서 첫번째셀과 두번째셀에 1,2를 각각 기록 후 두셀을 선택하여 하단으로 드래그 하면 보시는 것처럼 일련번호가 순서대로 매겨집니다.
하단은 ROW함수를 이용하여 일련번호를 매긴 결과입니다. B3셀에 입력된 ROW(A1)의 결과값은 1입니다. A1셀은 열에서 첫번째이기 때문에 1입니다. RAW(A2)의 값은 2가됩니다. 하단부위는 3,4,5,6,....순으로 나갑니다.
▶ CODE 및 CHAR함수로 영문 일련번호 매기기
[입력하고자 하는 셀 선택(C3:C10) - 함수삽입(=CHAR(CODE("A1")+ROW(A1)-1) ) - Ctrl + enter]
하단의 수식을 보면 복잡한것 같지만 실은 단순합니다. 영문으로 일련번호를 매기기 위하여 사용된 함수는 CHAR, CODE, ROW함수입니다. 사용된 함수값은 =CHAR(CODE("A1")+ROW(A1)-1) 입니다. 이를 해석해 보면 CODE("A")는 65, ROW(A1)은 1, 따라서위의 식은 = CHAR(65+1-1)가 되어 A값이 표시가 됩니다. 하단셀은 CODE("B")가 되어 66+1-1로 B가 됩니다. 만약 일련번호를 소문자로 매길려면 CODE("a")로 A를 소문자인 a로 하면 됩니다.
▶ 병합된 셀 일련번호 매기기
[입력하고자 하는 셀 선택(E3:E9) - 함수입력( =COUNTA($D$3:D3) ) - Ctrl + enter]
COUNTA함수(☞관련글 보러가기)는 비어있지 않은 셀의 갯수를 구하는 함수입니다. 따라서 여기에 사용된 함수식은 ( =COUNTA($D$3:D3) ) 입니다. D3셀을 $D$3 절대주소로 지정해 준 이유는 하단으로 드래그시 그 값이 변하면 되지 않기 때문입니다. 셀의 값을 절대주소로 변경할 경우에는 F4기능키를 이용하면 됩니다. 즉 D3를 셀에 입력 후 F4기능키를 클릭하면 됩니다. 예를 들어 E5셀의 일련번호 값이 2인 이유는 ( =COUNTA($D$3:D4) ) D3에서 D4 사이에서 비어있지 않은 셀은 2개이기 때문에 2가 됩니다. 즉 병합된 셀은 비어있지 않은 셀로 인식을 합니다.
▶ 같은 데이터(과목)로 일련번호 매기기
[입력하고자 하는 셀 선택(E3:E9) - 함수입력 ( =COUNTIF($F$3:F3,F3) ) - Ctrl + enter]
COUNTIF함수는 조건에 맞는 수를 구하는 함수입니다(☞관련글 보러가기) 따라서 여기에 입력된 함수( =COUNTIF($F$3:F3,F3) )에 대한 의미를 분석해 보면 $F$에서F3까지 F3와 같은 것은 1개 이기에 1입니다. =COUNTIF($F$3:F7,F7)가 2가 나온 이유는 $F$에서F7까지 F3와 같은 것(국어)는 2개 이기에 2입니다
