엑셀 이름관리자, 데이터유효성 검사 및 VLOOKUP 정리_(1/2)_2021.06.02

2003년 강원도 인제군 남면에서 군생활을 할 적에 포병대에서 근무를 하였습니다. 주특기는 FDC(Fire Dance Club)였지만 작전과에서 행정병으로 복무를 하였습니다. 덕분에 한글작업은 마우스없이 작업하는 단계까지 올라갔지만 그밖의 프로그램은 어린아이 수준이었습니다. 사회에 나가서는 엑셀이나 파워포인트를 많이 이용하는 것에 놀랐습니다. 기능도 많아 어제한 작업을 잊어버리고 다시 인터넷을 찾아 해매는 적도 많았습니다. 요즘도 엑셀작업을 할때 잊혀진 기억에 힘들어 합니다. 다시는 잊지 않도록 주요 기능을 정리하도록 하겠습니다.

「데이터유효성 검사」는 쉽게 정리하면 정해진 데이터값만 표시하도록 설정하는 기능입니다. 이를 실행하기 위해선 「이름관리자」 분류 작업을 선행해야 합니다. 예를 들어보면 서울에는 강남구, 구로구, 마포구.. 부산에는 사하구, 남동구, 동구 등의 지자체의 지명을 기입하여 서울에서 부산의 지차체의 지명이 안나오도록 하는 작업입니다.

최종 목표는 장미반의 철수 점수와 평균값을 쉽게 표시하도록 하는것입니다.

 이름관리자 설정하기

1) 반 별 이름 정리하기

2) 이름관리자 설정하기 : 저는 「반」,「장미반」,「들꽃반」,「모란반」,「물망초반」 이름을 설정하였습니다. 「반」항목에는 장미반, 들꽃반, 모란반, 물망초반을 설정하였으며, 「장미반」항목에는 영희, 철수, 미애.. 등을 설정하였습니다. → 「A4」 사진 참조

 

 데이터유효성 검사 설정하기

1) 아래 표의 I10열부터 O14열까지 수식으로 채울려고 합니다. 그러기 위해 「반」과 「이름」항목을 데이터유효성검사로 설정해야 합니다.

2) 데이터유효성 검사 설정 하기

「B4」번째 사진에서 보듯이 「데이터유효성」 창의 「원본」 항목에 「=INDIRECT($I$2)」 문구를 기입해야 합니다. 만약 「A4」번째 사진에서 『「이름관리자」 설정 → 「반」』의 내용을 설정하지 않았다면 아래 「B6」사진처럼 「데이터유효성」 창의 「원본」 항목에 I3~I6열의 구간을 설정해야 합니다.

3) 이름 항목에 데이터유효성 검사 설정하기

사진 「B4」와 「B7」 이 다른 부분은 크게 두곳 입니다.

구  분	셀 선택 구분	원본(S) 항목에 문구기입
사진 「B4」	I10 ~ I14	=INDIRECT($I$2)
사진 「B7」	J10	=INDIRECT($I10)
이  유	「B4」 : 고정된 값 「B7」 : 설정 후 J11~J14까지 복사 붙여넣기	「B4」 : 고정된 값 「B7」 : J10의 수식을 J11~J14까지 붙여넣기를 해야 하므로 $표시는 열에만 표시

엑셀에서 수백만가지 기능이 있다고 합니다. 엑셀을 만든 사람도 과연 모든 기능을 사용할 수 있는지 궁금하기도 합니다.

엑셀은 한번 설정해 놓으면 쉽게 사용할 수 있지만 설정하기까지가 복잡하고 어렵게 보여 다가가기가 어렵습니다. 그래서 어떤분들은 하나씩 찾아서 복사, 붙여넣기를 하는 분도 있습니다. 하지만 그렇게 수작업으로 하면 실수도 생기고 나중에 데이터양이 많아지면 정리하기도 더 어려워 집니다. 처음에 힘들어도 나중을 위해 여러 설정을 하면 관리하기도 쉬워집니다. 지난 5월 7일에 올린 가계부 엑셀표에도 이름관리자와 데이터유효성 설정으로 쉽게 만들수 있었습니다.

☞ 2편 : VLOOKUP 및 필터링 정리 ☜