안녕하세요. 이지이지입니다.
이번 포스팅에서는 엑셀로 만든 (선택과목별) 출석부 양식을 공유하겠습니다.
공유할 출석부 양식은 위의 이미지에서처럼,
학생들이 수강할 과목의 데이터를 입력하면 자동으로 과목별 출석부가 만들어지는 파일입니다.
방과후학교나, 특기적성, 맞춤형 특강 등등 학생들의 선택으로 이루어지는 수업의 출석부 제작에 유용합니다.
매크로 사용을 가급적 자제하려고 본 파일은 배열수식을 이용해 만들었습니다.
본 포스팅은 크게 두 부분으로 나뉘며,
첫 번째 파트는 본 파일의 사용법(매우 간단합니다.),
두 번째 파트는 본 파일에 사용된 이름(정의)과 함수에 대한 설명입니다.
본 파일에도 간단히 사용방법을 도형안에 넣어 두었으니 참고하시면 되겠습니다.
| 출석부 사용방법 |
본 파일에는 [기초 데이터], [출석부] 두 개의 시트가 있습니다.
먼저 [기초 데이터] 시트의 사용방법입니다.
- A1 셀에는 제목을 입력합니다. 입력하지 않으셔도 되고, 아무 제목이나 입력하셔도 됩니다.
- A열과 B열에 반드시 학번과 이름을 입력하여야 합니다. 가운데에 열을 삽입하거나 열을 삭제하지 말아주세요.
→ 학생 수에는 제한이 없습니다.
- C2, D2, E2 ........... 셀에는 과목명을 입력합니다. 과목명과 과목명 사이에 빈 셀이 있으면 안됩니다.
→ 과목 수에는 제한이 없습니다.
- 각 과목별로 신청을 한 학생에게는 반드시 1로 표시하여야 합니다.
다음은 [출석부] 시트의 사용방법입니다.
- L2셀에서 과목명을 선택하면 순번, 학번, 이름이 자동으로 입력됩니다.
- 과목명은 반드시 L2셀에 들어가야 합니다. 만약 L2셀 이외에 과목명이 표시되기를 원한다면,
① 원하는 셀(ex. A2)에 과목명을 입력합니다.
이 때 과목명은 반드시 [기초 데이터] 시트에 있는 과목명 중 하나와 일치하여야 합니다.
② Ctrl + F3를 눌러 이름 관리자 창을 띄웁니다.
③ 이름 중 "선택과목"을 선택하고, 참조 대상 부분에서 $L$2를 과목명이 표시될 셀 주소(ex. $A$2)로 바꿉니다.
- A~C열은 고정입니다. 열을 삽입하거나 열을 삭제하지는 말아주세요.
- 선택과목별 학생 수는 1,000명까지 가능합니다. 한 과목을 1,000명이 듣는 경우는 없겠죠?
만약 1,000명 이상을 표현하고 싶다면 위의 이름 관리자 창에서 참조 대상 부분의 1,000이라고 되어 있는 부분을 수정하면 됩니다.
- D열부터는 자유롭습니다. 셀 테두리 등등 모두 원하는 대로 수정하여 사용하면 되겠습니다.
이상 출석부 사용방법에 대한 설명은 모두 끝이 났습니다.
이제 본 파일에서 사용된 수식을 알고 싶은 분만 끝까지 따라오시면 되겠습니다.
| 이름(정의), 함수 설명 |
본 파일에서는 두 가지의 이름이 사용되었습니다.
1. "과목명": 선택 과목들의 이름입니다. [출석부] 시트의 L2셀에 데이터 유효성 검사를 하기 위한 것으로 OFFSET 함수를 통해 동적으로 범위가 지정되어 있습니다.
2. "선택과목": [출석부] 시트에서 선택한 과목의 데이터를 추출하기 위한 이름으로 OFFSET 함수를 통해 동적으로 범위가 지정되어 있습니다.
먼저 "과목명"에 대한 이름 정의입니다.
=OFFSET('기초 데이터'!$C$2,0,0,1,COUNTA('기초 데이터'!$2:$2)-2)
- OFFSET 함수는 참조셀에서 지정된 행과 열만큼 떨어진 곳의 특정한 셀 하나 또는 여러 영역을 가져올 수 있습니다.
① reference: 참조셀: '기초 데이터'!$C$2
② Rows: 참조셀로부터 떨어질 행의 수: 0
③ Cols: 참조셀로부터 떨어질 열의 수: 0
④ Height: 참조셀에서 행과 열만큼 떨어진 곳으로부터 가져올 행의 수: 1
⑤ Width: 참조셀에서 행과 열만큼 떨어진 곳으로부터 가져올 열의 수: COUNTA('기초 데이터'!$2:$2)-2
①~③ $C$2로부터 0,0만큼 떨어지면 → $C$2
④ $C$2에서 1행만큼의 영역이므로 → $C$2
⑤ 2행 전체($2:$2)에서 비어있지 않은 셀의 개수에서 2만큼을 뺀(앞의 학번, 이름을 빼 줌) 열
→ $C$2에서부터 오른쪽으로 비어 있지 않은 모든 셀이 "과목명"에 포함됨.
다음은 "선택 과목"에 대한 이름 정의입니다.
=OFFSET('기초 데이터'!$C$3,0,MATCH(출석부!$L$2,과목명,0)-1,1000,1)
- '기초 데이터'!$C$3: 참조셀을 $C$3로 합니다.
- 0: 행은 참조셀로부터 떨어지지 않습니다. → $C
- MATCH(출석부!$L$2,과목명,0)-1
① 출석부!$L$2: [출석부] 시트의 L2셀에 입력된 값이
② "과목명" 중에 있는 과목과
③ 정확히 일치하면 몇 번째에 위치하는지 값을 반환하라.
④ -1: ③에서 나온 숫자에서 1을 뺀만큼 [기초 데이터]시트의 $C$3로부터 이동하여라.
※ 만약 [출석부] 시트의 L2셀을 영어로 하였다면,
① 영어
②~③: 3
④: 2
→ $C$3로부터 0,2 만큼 이동하면 $E$3가 됩니다.
- 1000: $E$3에서부터 1,000개의 아래에 있는 모든 행을 영역으로 합니다. → $E$3:$E$1002
-1: 한 개의 열만 영역으로 하므로 위에서 변동 없습니다.
마지막으로 [출석부] 시트의 B7셀에 사용된 배열수식에 대해 설명드리겠습니다.
(C7셀은 B7셀의 수식에서 열만 조정해주면 되고, A7셀은 워낙 쉬운 수식이라 생략합니다.)
[출석부] 시트 B7셀의 수식 입력줄을 보면 다음과 같이 수식이 적혀 있습니다.
{=IFERROR(OFFSET('기초 데이터'!$A$1,SMALL(IF(선택과목=1,ROW(선택과목)),ROW(A1))-1,0),"")}
가장 앞의 IFERROR은 오류 처리를 위한 것으로 IFERROR 부분을 생략하고 OFFSET 함수만 보면 다음과 같습니다.
{=OFFSET('기초 데이터'!$A$1,SMALL(IF(선택과목=1,ROW(선택과목)),ROW(A1))-1,0)}
수식을 보면 { }로 닫혀 있습니다.
엑셀에서 중괄호는 배열수식을 의미합니다.
배열수식을 적용하기 위해서는 수식을 입력후 Ctrl + Shift + Enter를 눌러야 합니다.
나머지 셀은 평소처럼 B7셀에서 채우기 핸들을 드래그하여 채우면 됩니다.
함수를 설명드리기 위해 먼저 B7셀을 선택한 후,
[수식]-[수식 계산]을 눌러 수식 계산 창을 띄웁니다.
수식 계산은 "계산"을 누를 때마다, 한 단계씩 차례대로 이루어집니다.
기울임체로 표시되는 부분은 이전 단계에서 계산된 내용을 나타내며,
밑줄로 표시되는 부분은 이번 단계에서 계산될 부분을 나타냅니다.
선택과목으로 영어를 선택했다고 가정하고 한 단계씩 살펴보면,
① 선택과목
→ {0;1;0;1;0;1.....}로 계산되어 나옵니다.
영어 과목에서 2(김유신), 4(강감찬), 6(홍길동), 8(박지성)...이 선택했기 때문입니다.
② IF({0;1;0;1;0;1.....}=1
→ IF(FALSE;TRUE;FALSE;TRUE;FALSE;TRUE ...}로 계산됩니다. 0이면 FALSE, 1이면 TRUE가 반환되었습니다.
③ ROW(선택과목)
→ ROW('기초 데이터'!$E$3:$E$1002)가 계산되었습니다. 선택과목을 영어로 했기 때문입니다.
④ ROW('기초 데이터'!$E$3:$E$1002)
→ {3;4;5;6;7;8...}이 반환되었습니다. $E$3:$E$1002까지의 행번호가 반환된 것입니다.
⑤ IF({FALSE;TRUE;FALSE;TRUE...},{3;4;5;6...})
→ {FALSE;4;FALSE;6;FALSE;8...}이 반환되었습니다. TRUE 이었던 부분에 행 번호가 입력되었습니다.
⑥ ROW(A1)
→ {1}이 반환되었습니다. A1의 행 번호이므로 당연히 1이 반환됩니다.
→ ROW(A1)은 SMALL 함수에 포함된 것으로 SMALL은 데이터에서 K번째 작은 수를 구하게 됩니다.
→ ROW(A1)은 [출석부] 시트의 B7, ROW(A2)는 B8, ROW(A3)는 B9... 셀에 위치하므로 첫 번째 작은 수부터 n번째 작은 수까지 차례로 채워지게 되는 것입니다.
⑦ SMALL({FALSE;4;FALSE;6....},{1}
→ FALSE가 아닌 것 중에 1번째로 작은 수인 4를 반환합니다.
⑧ {4}-1 → 3을 반환합니다.
⑨ OFFSET('기초 데이터'$A$1,{3},0),
→ [기초 데이터]시트의 A1셀로 부터 행이 3만큼 열이 0만큼 떨어진 셀은 A4셀이므로 1102를 반환하게 됩니다.
자, 이렇게 출석부 파일에 사용된 이름과 함수를 모두 알아보았습니다.
매크로를 사용하는게 차라리 더 편할 듯 ㅡㅡ;;;
어쨌든 선택과목 출석부 양식이 필요하셨던 분에게는 도움이 되었으면 좋겠습니다.
오늘 포스팅은 여기까지입니다.~~~~
본 포스팅에 사용된 마지막 배열수식은 다음 블로그를 참고하였습니다.
배열수식
지난번 엑셀 강좌의 마지막은 배열수식으로 하려 했습니다만, 저도 잘 모르고, 이것을 실제 써 먹을 수 있는 일이 얼마나 있을까해서 망설였습니다. 그러다 뜻밖에 시간이 나게 된 오늘 배열수
flogsta.tistory.com
댓글