[엑셀함수] QUOTIENT 함수, VLOOKUP 함수, COUNTIF 함수 활용하기

나는 지금 선생님이 되었다고 생각하고 인원수별 과목별 시험점수를 엑셀로 정리하고 싶다.

이제 엑셀로 정리해 보자.

학생별 과목별로 시험 점수를 직접 손으로 입력해 보았다.

만약 학생 수가 1,000명에 과목이 10개라면...??

물론 복붙이나 자동완성의 기능을 활용하면 시간이 줄 수 있지만... 

손과 시간이 많이 간다.

그럼 시험 점수는 하나씩 입력하는 방법뿐이지만,

이름과 과목만이라도 함수를 이용해 자동으로 입력할 순 없을까?

QUOTIENT 함수, VLOOKUP 함수, COUNTIF 함수 3개를 이용하면 가능하다.

1. 학생별, 과목별로 고유 번호 지급.

 

김태희 학생은 1번, 이영희 학생은 2번 이렇게 학생별로 고유 번호를 발급한다.

과목도 동일하다. 영어과목에 1번, 수학에 2번, 국어에 3번, 체육에 4번, 한자에 5번을 발급했다.

1번이 김태희 학생 번호와 영어 과목에서 사용하는 번호가 같다고 생각할 수 있지만,

학생 이름에 사용한 번호와 과목에 사용한 번호는 다른 것이다.

2. 함수를 위한 변수 생성.

1번 항목에서 발급한 번호를 참조해 이름과 과목을 출력하는 방식을 사용한다.

위 방법을 하기 위해서 3가지(목록, 이름_번호, 과목_번호) 컬럼을 추가 했다.

3. 목록에는 숫자 자동 채우기를 상용해 25번까지 생성.

이 목록 번호의 용도는 QUOTIENT 함수를 활용하기 위해 추가 했다.

4. 시험 종류별 학생 이름을 반복 출력하기 위한 QUOTIENT 함수 사용

학생 한명이 봐야할 시험의 종류가 5가지이다.

따라서 수식을 이용해 만들어야할 수는 학생 한명당 시험을 봐야 하는 수만큼 본인 번호를 출력하는 것이다.

그래서 김태희 학생 번호는 1번이 세로로 5회 출력되며 이는 모든 학생이 동일하다.

수식을 보자.

목록 번호에 -1 값을 해주고 최대 학생 번호만큼 나눈 나머지 값에 +1을 해주는 보정값을 추가한다.

이렇게 수식을 입력하면 학생 한명당 시험을 봐야 하는 수만큼 학생 번호가 반복된다

5. 학생 이름을 참조하기 위한 VLOOKUP 함수 사용

학생 고유 번호를 생성했으니 그 번호에 맞는 이름을 수식으로 불러와 보자.

여기서 표2는 학생별로 부여된 고유 번호가 지정된 표 이름이다.

6. 시험 과목 출력을 위한 COUNTIF 함수 사용

시험의 종류는 5가지로 1,2,3,4,5의 순서대로 번호가 반복 생성되어야 한다.

이를 위해서 학생 번호를 이용해 카운트 함수를 이용할 것이다.

수식을 해석하면 "머리글부터 현재 선택된 셀까지 N번 학생 번호가 몇개 있는가?"이다.

7. 과목 번호를 참조해 과목명 출력하기 - VLOOKUP 함수 사용

학생 번호를 참조해 이름을 출력하는 방식과 동일하다.

마지막으로 최종 완성된 내용을 확인해 보자.

수식 사용 여부를 구분하기 위해 색으로 구분했다.

이렇게 함수를 이용하면 아래와 같은 장점이 있다.

1. 학생 수가 늘어도 과목 수가 늘어도 표만 확장하면 값들이 자동으로 입력된다.

2. 데이터 입력시 실수가 줄어 든다.

3. 통계를 위한 표의 양식을 유지할 수 있다.