안녕하세요 짤막한 강좌 한정희 강사입니다.
2021년~2023년 개정판까지 두 차례 EBS 컴활 1급 실기 수험서 편집 교정 작업을 했습니다.
애착을 가지고 재밌게 작업했지만 혼자 하는 작업이 아니기 때문에 아쉬웠던 부분도 있었습니다. 그래서 블로그와 유튜브 채널을 통해 제가 원하는 방향으로 집필하고 강의를 진행해 보려 합니다.
2024년 상반기에 1급 실기를 마무리할 목표로 계획하고 있습니다.
컴활 자격증을 준비하고 합격을 목표로 하는 많은 분들에 이 강의가 도움이 되었으면 합니다.
첫 번째 강의는 고급 필터입니다. 이번에 풀이할 문제는 대한상공회의소에서 샘플로 제공하는 A, B형 문제로 컴활 1급 실기를 학습하면서 고급 필터 문제가 어려웠다면 이번 강의를 집중해서 보시기 바랍니다.
고급 필터 문제는 지시사항에 맞게 조건식을 작성해 조건에 일치하는 레코드(행)를 필터 하는 문제입니다. 함수를 사용해 조건식을 작성하는 것이 문제 풀이에 핵심이죠!
대부분 조건식은 AND 또는 OR 함수를 시작으로 조건식 두 개를 작성합니다.
고급 필터는 워크 시트에 직접 조건을 입력해야 합니다. 첫 행에는 조건에 해당하는 데이터베이스의 필드명을 입력하고 두 번째 행에 조건을 입력합니다. 하지만 조건을 논리식으로 작성하는 경우 결과는 논리값(TRUE 또는 FALSE)이므로 데이터베이스에는 해당되는 필드가 없습니다.
그래서 필드명을 생략하거나 임의의 값으로 필드명을 입력해야 합니다. 대부분 필드명을 조건으로 사용하므로 이 강의에서도 조건으로 사용하겠습니다.
샘플A] ‘기본작업-1’ 시트에서 다음과 같이 고급 필터를 수행하시오. (5점)
▶ [B3:T31] 영역에서 ‘출석수’가 출석수의 중간값보다 작거나 ‘6/9’일이 빈 셀인 행에 대하여 ‘학년’, ‘반’, ‘이름’, ‘6/9’, ‘출석수’ 열을 순서대로 표시하시오.
▶ 조건은 [V3:V4] 영역에 입력하시오. (ISBLANK, OR, MEDIAN 함수 사용)
▶ 결과는 [X3] 셀부터 표시하시오.
실습 파일
1. 먼저 [V3] 셀에 조건을 입력한 후 [V4] 셀에 조건식을 작성하겠습니다.
샘플 A는 두 개의 조건으로 구성되어 있고 조건1과 조건2가 ~하거나로 연결되어 있어 OR 조건이 됩니다. OR는 두 조건 중 하나 이상이 참인 경우 해당 행을 필터링합니다.
2. [V4] 셀에 =OR( 함수를 먼저 입력한 후 출석수[T4]가 출석수의 중간값MEDIAN($T$4:$T$31)보다 작은지 조건을 비교합니다. 인수를 구분하는 (,)쉼표를 입력하고 이번에는 빈 셀인지 확인하는 ISBLANK( 함수를 중첩해 6/9[S4] 셀을 참조한 후 6/9[S4]이 빈 셀인지 비교합니다.
※ 함수 인수의 괄호는 여는 괄호와 닫는 괄호가 일치해야 합니다. 색상을 보면 쉽게 구분할 수 있습니다.
3. 지시 사항에 맞게 [X] 셀부터 결과가 표시되도록 학년, 반, 이름, 6/9, 출석수를 입력합니다. 그러면 조건에 맞는 데이터의 모든 필드가 표시되는 것이 아니고 입력한 필드만 표시됩니다.
단, 필드명을 입력할 때 오타를 주의해야 합니다. 그래서 데이터에서 필드명을 복사하는 방법을 추천합니다.
학년, 반, 이름 필드명 범위를 선택한 후 <Ctrl>을 눌러 6/9, 출석수 필드명을 선택하고 <Ctrl + C>를 눌러 복사합니다. 지시사항에 맞게 [X3] 셀부터 <Ctrl + V>를 눌러 붙여 넣습니다.
4. 데이터에서 임의의 셀을 선택한 후 [데이터] 탭 - [정렬 및 필터] 그룹 - [고급]을 선택합니다.
5. 6.의 그림과 같이 [고급 필터] 대화 상자가 실행되고 목록 범위가 자동으로 지정됩니다.
6. 조건 범위에는 [V3:V4] 셀 범위를 지정합니다. 그리고 다른 장소에 복사를 선택해 복사 위치를 활성화시킨후 복사 위치에는 [X3:AB3] 셀 범위를 지정하고 [확인]을 누릅니다.
7. [X] 셀부터 조건에 맞는 결과가 표시됩니다.
컴활 1급 실기를 첫 도전하는 분들을 위해 함수식을 풀어서 설명하고 고급 필터의 내부 동작을 실습을 통해 확인해 보겠습니다. 위 내용을 이해하셨다면 이 부분은 생략하고 샘플 2 문제로 바로 넘어가면 됩니다.
다시 조건부터 보겠습니다.
1. 출석수가 출석수의 중간값보다 작은 경우
수식] =T4<MEDIAN($T$4:$T$31)
MEDIAN 함수는 숫자 범위에서 중간값을 구합니다.
출석수[T4]가 출석수 범위에서 중간 값MEDIAN(T4:$T$31)보다 작은지 비교한 후 수식을 복사합니다. 출석수가 중간값보다 작으면 TRUE, 그렇지 않으면 FALSE 결과를 구합니다.
2. ‘6/9’일이 빈 셀인 행인 경우
수식] =ISBLANK(T4)
ISBLANK 함수는 참조하는 셀이 비어있으면 TRUE, 그렇지 않으면 FALSE를 반환합니다.
6/9[S4] 셀이 빈 셀인지 확인한 후 빈 셀이면 TRUE, 그렇지 않으면 FALSE 결과를 구합니다.
3. 조건1과 조건2 중에 하나 이상이 참인 경우
수식] =OR(V4,W4)
OR 함수는 하나 이상의 조건이 참이면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환합니다.
[V4,W4] 셀 중 하나 이상이 TRUE 이면 TRUE 결과를 구합니다.
4. [V3:X3] 셀 범위를 블록 지정한 후 <Ctrl + Shift + L>을 누르면 필터 단추가 표시됩니다. [X] 열의 필터 단추를 눌러 (모두 선택)에 체크 표시를 해제하고 TRUE에 체크 표시를 한 후 [확인]을 누릅니다.
조건에 맞는 행이 필터링 되었습니다. 조건을 세부적으로 풀어 풀이해 봤는데 조건식이 조금 쉬워졌나요?
샘플 B] ‘기본작업-1’ 시트에서 다음과 같이 고급 필터를 수행하시오. (5점)
▶ [B2:G43] 영역에서 ‘작업사항’이 공백이 아니면서 ‘작업사항’이 ‘품절도서’가 아닌 행에 대하여 ‘입력 일자’, ‘신청자이름’, ‘서명’, ‘저자’, ‘작업사항’ 열을 순서대로 표시하시오.
▶ 조건은 [I2:I3] 영역에 입력하시오. (AND, ISBLANK, NOT 함수 사용)
▶ 결과는 [I7] 셀부터 표시하시오.
실습 파일
1. [I2] 셀에 조건을 입력한 후 [I3] 셀에 조건식을 작성하겠습니다.
샘플 B는 두 조건이 ~하면서로 연결되어 있어 AND 조건이 됩니다. AND는 두 조건이 모두 참인 경우 해당 행을 필터링합니다.
2. =AND( 함수를 입력한 후 NOT(ISBLANK(G3))을 입력해 작업사항이 공백이 아닌지 확인하는 수식을 작성합니다. 쉼표를 입력한 후 G3<>"품절도서"를 입력해 작업사항[G3]이 품절도서가 아닌지 두 번째 조건식을 작성하고 AND 함수의 인수 괄호를 닫습니다.
※ NOT 함수의 인수 괄호와 ISBLANK 함수의 인수 괄호를 열었으니 인수 작성이 완료되면 괄호를 반드시 닫아야 합니다. 여는 괄호와 닫는 괄호는 반드시 짝이 맞아야 합니다.
3. 입력일자, 신청자이름 필드명을 먼저 복사해 [I7] 셀부터 붙여 넣고 서명, 저자 필드명을 복사해 [K7] 셀부터 붙여 넣습니다. 마지막으로 작업사항을 붙여 넣습니다. 그러면 지시사항에 맞게 순서대로 필드명이 표시됩니다.
4. 데이터 범위에서 임의의 셀을 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [고급]을 선택합니다. 목록 범위는 자동으로 입력되죠? 조건 범위에는 [I2:I3] 셀 범위를 지정하고 다른 장소에 복사를 선택한 후 복사 위치에는 [I7:M7] 셀 범위를 지정하고 [확인]을 누릅니다.
5. 열 너비를 자동으로 맞춰 보기 좋게 마무리 합니다.
※ 열 너비 조정은 반드시 해야하는 작업은 아닙니다.
샘플 B도 함수 작성법을 상세히 설명하고 내부적으로 동작하는 방법도 실습을 통해 소개하겠습니다. 함수 사용법이 아직 어렵다면 아래 내용을 보세요.
1. NOT 함수는 인수의 결과가 TRUE이면 FALSE를 FALSE이면 TRUE를 반환합니다. NOT 함수에 ISBLANK 함수를 중첩해 [G3] 셀이 공백인지 확인한 후 공백(TRUE)이면 반대의 결과인 FALSE를 반환하도록 수식을 작성하면 작업사항[G3]이 공백이 아니면이라는 조건식이 됩니다.
수식] =NOT(ISBLANK(G3))
2. <>은 같지 않다 연산자로 =G3<>"품절도서" 수식은 작업사항[G3]이 품절도서가 아닌 조건이 됩니다. 수식을 작성할 때 조건이 문자열인 경우 큰따옴표로 묶어 입력해야 합니다.
3. AND 함수는 두 조건이 모두 참이면 TRUE를 반환하는 함수입니다. 그래서 작업사항[G3]이 공백이 아니면서 작업사항[G3]이 품절도서가 아닌 두 조건이 모두 일치하면 TRUE 반환합니다.
수식] =AND(I3,J3)
4. [I2:K2] 셀을 블록지정한 후 <Ctrl + Shift + L>을 눌러 필터를 적용한 후 [K2] 셀의 필터 단추를 눌러 (모두 선택)에 체크 표시를 해제하고 TRUE에 체크 표시를 한 후 [확인]을 누릅니다.
5. 조건식을 하나씩 작성해보고 결과까지 확인해봤습니다.
고급 필터 풀이는 어떠셨나요? 설명이 길어져도 첫 강의라 상세하게 소개를 했습니다. 합격하는 방법은 다양한 문제 유형을 풀이해 보는 수밖에 없습니다. 더 많은 고급 필터 기출문제로 또 찾아뵙겠습니다.
컴활 1급 실기 합격을 위해 응원합니다. 파이팅!
Have a nice day.
'자격증 > 컴활 실기' 카테고리의 다른 글
| 컴활 1급 실기 엑셀] 고급 필터 3 (0) | 2024.03.11 |
|---|---|
| 컴활 1급 실기 엑셀 ] 고급 필터 2 (0) | 2024.03.04 |
| 컴퓨터활용능력 2급 실기] B형 연습문제 - 최신 기출 문제 (0) | 2021.06.02 |
| 컴퓨터활용능력 2급 실기] A형 연습문제 - 최신 출제 기준 (0) | 2021.05.30 |