엑셀-함수로 데이터 검색하기

2008-11-05     PC사랑
이달의 포인트
RANK 함수 범위 내에서 해당되는 값의 순위를 구하는 함수로, 형식은RANK(순위를 구할 수, 순위를 구할 범위, 정렬방식)이고 정렬방식을 정하지 않으면 내림차순으로 순위를 구한다.

IF 함수 엑셀에서 가장 많이 이용하는 함수 중의 하나로 조건에 따라서결과가 달라져야 할 때 쓴다. 함수 형식은 IF(조건식, 조건이 참일 경우 처리 결과, 조건이 거짓일 경우 처리 결과)이다.

VLOOKUP 함수 어떤 기준표에서 특정 값을 찾을 때 유용하게 쓰는 함수다. 기준표의 가장 왼쪽 열에서 특정 값을 찾아, 정한 열에서 같은 행에있는 값을 표시하는 함수로, 형식은 VLOOKUP(기준표에서 찾을 값, 기준표, 기준표에서 결과 값의 열 위치, 논리 값)이다.

셀 범위에 이름 지정 셀 범위를 함수나 수식 등에 이용할 때 범위의 이름을 정해 작성할 수 있다. 셀 주소를 대신 하는 것으로 항상 절대주소로용되고 의미있는 문자로 표시되므로 알파벳과 숫자로 표시되는 셀 주소 보다 함수식을 이해하기 쉽다.

유효성 검사 셀에 입력되는 데이터의 오류를 방지하려고 입력되는 데이터 종류와 범위를 정할 수 있다. 유효성 검사에서 지정된 데이터가 아닌 경우에 오류 메시지가 표시된다.
 
1. 과제물 점수와 시험 점수를 더해서 총점을 구하려면 총점을 구할첫 번째 셀을 선택하고 홈 탭→편집 그룹→합계 버튼을 클릭한다. 예제에서는I8셀에서 작업한다.
 
2. I8셀에‘=SUM(D8:H8)’이라는 수식이 자동으로 표시되는데 총점을구할 범위가 정확하므로 엔터 키를 누른다. I8셀에 총점이 정확하게 계산되면 나머지 사람들의 총점도 구해보자. I8셀을 선택하고 셀의 오른쪽아래에있는채우기핸들에 마우스포인터를놓고두번클릭한다.
 
3. 총점을구할마지막셀까지I8셀의수식이복사되면서나머지교육생들의 총점도 구해진다. 이때 I8셀의 위쪽 부분의 테두리가 복사되면서 테두리가 점선에서 실선으로 변경되므로 점선을 유지하려면 복사한 뒤에 나오는스마트태그의내림버튼을클릭하고‘서식없이채우기’를누른다.
 
4. 총점이 계산된 모든 셀들의 테두리가 실선에서 점선으로 변경되면서보기좋게서식을유지한다.
 
5. 순위를 구하려면 홈 탭→편집 그룹의 합계 버튼을 클릭하고‘함수 추가’를고른다.
 
6. 함수 마법사의‘함수 선택’항목에서 RANK를 클릭하고‘확인’버튼을누른다.
 
7. ‘함수 인수 창의 number 항목엔 순위를 구할 교육생의 총점이 있는셀 주소를 넣는다. ref 항목에는 총점의 전체 범위를 절대주소로 넣는다.
order 항목은내림차순으로 순위를구할때는값을빼도된다.
 
8. 나머지 교육생들의 순위를 구하려면 J8셀의 수식을 복사하고 스마트태그에서‘서식없이채우기’를고른다.
 
9. 수료여부는 총점이 140 이상이면‘수료’, 140점 이하면‘미수료’로표시한다. K8셀을 선택하고 함수 마법사에서 If 함수를 고른다. If 함수 인수 창에서 Logical_Test에는‘I8>=140’이라고 쓴다. Value_if_true에는‘수료’, Value_if_false에는‘미수료’라고쓴다.
 
10. K8셀의 수식을 복사해서 K열에 수료와 미수료가 표시되면 역시 스마트태그를이용해테두리는복사하지않고수식만복사한다.
 
11. 교번이 입력된 범위를 정한다.(여기서는 B8:B23를 범위로 정한다.) 워크시크 왼쪽 위의‘이름’을 클릭하고‘교번’이라고 넣고 엔터 키를 누른다. B8:B23 범위에‘교번’이라고이름이지정된다.
 
12. B8:K23까지 범위를 선택하고 역시‘이름’에‘자료’라고 쓰고 엔터 키를눌러이름을정한다.
 
13. 개인 성적표를 출력할 시트를 고른다. 교번은 전체 성적표 시트에 있는 것만 표시해 보자. 유효성 검사를 적용해 E5셀을 선택해 데이터→ 데이터도구그룹→데이터 유효성검사→데이터 유효성검사를누른다.
 
14. ‘데이터 유효성 검사’창의‘설정’탭에서‘제한 대상’에‘목록’, 원본에‘=교번’이라고넣는다. 확인버튼을클릭한다.
 
15. 유효성 검사가 적용된 셀을 클릭하면 범위이름이‘교번’인 범위가 데이터가 표시된다. 교번 내의 데이터가 아닌 데이터를 쓰면 오류 표시가뜨며입력되지않는다.
 
16. 총점을 구할 셀인 E7셀을 고르고, 함수 마법사에서 VLOOKUP 함수를 선택하고, VLOOKUP 함수 인수 창에서 Lookup_value에는‘교번’이입력된 셀을 고른다. Table_array에는‘자료’, Col_index_num에는 자료범위에서 총점이 위치한 열인‘8’을 넣는다. Range_lookup에서는 교번과정확하게일치되는값을표시하려고 0을 넣는다.
 
17. 순위를 구할 셀에는‘=VLOOKUP(E5,자료,9,0)’, 수료여부를 구할 셀에는‘=VLOOKUP(E5,자료,10,0)’을 입력한다. ‘성적표’라고 입력되어있던 D3셀에‘=VLOOKUP(E5,자료,2,0) & 성적표’를 입력하면 교육생의이름도표시된다.