안녕하세요 짤막한 강좌 한정희 강사입니다.
지난 포스팅에 이어 고급 필터 기출문제를 풀이하겠습니다.
자격증을 취득하는 방법은 다양한 문제를 풀이해 보면서 유형에 익숙해져야 합니다.
포스팅마다 2문제씩 풀이하겠습니다.
지난번에 포스팅한 고급 필터 1을 학습하려면 아래 링크를 클릭하면 됩니다.
컴활 1급 실기 엑셀] 고급 필터 1
안녕하세요 짤막한 강좌 한정희 강사입니다. 2021년~2023년 개정판까지 두 차례 EBS 컴활 1급 실기 수험서 편집 교정 작업을 했습니다. 애착을 가지고 재밌게 작업했지만 혼자 하는 작업이 아니기
hantip.net
기출 1] ‘기본작업-1’ 시트에서 다음과 같이 고급 필터를 수행하시오. (5점)
▶ [A3:I30] 영역에서 ‘상품명’이 '정기'로 시작하고 '성별'이 '남'이거나, '지점명'이 '종로'이고 '불입액(월)'이 100,000원 이상인 행에 대하여 '고객명', '가입일', '상품명', '불입액(월)', '이체일', '만기금액' 열을 순서대로 표시하시오.
▶ 조건은 [K3:K4] 영역에 입력하시오. (LEFT, OR, AND 함수 사용)
▶ 결과는 [K8] 셀부터 표시하시오.
실습 파일
1. [K3] 셀에 조건을 입력한 후 [K4] 셀에 조건식을 작성하겠습니다.
기출 1은 조건1과 조건2가 ~이거나로 연결되어 있어 OR 조건이 됩니다.
각 조건은 두 개 조건으로 구성되어 있고, 조건1을 보면 ~하고로 연결되어 있습니다. 그러면 AND 조건이 됩니다.
※ AND는 조건이 모두 참이면 해당 행을 필터링하고 OR는 하나 이상의 조건이 참이면 해당 행을 필터링합니다.
2. [K4] 셀에 =OR( 함수를 입력합니다.
조건1: AND( 함수를 중첩한 후 LEFT( 함수를 중첩하고 상품명[D4]의 왼쪽부터 2개 문자를 추출해 정기인지 조건을 비교합니다. 쉼표를 입력해 인수 사이를 구분하고 이번에는 성별[B4] 셀이 남인지 조건을 비교합니다.
조건2: AND( 함수를 중첩하고 지점명[E4]이 종로인지 조건을 비교한 후 쉼표를 입력하고 불입액(월)[F4]이 100000 이상인지 조건을 비교합니다.
※ 인수의 괄호를 신경 써 닫습니다.
3. 지시 사항에 맞게 [K8] 셀 부터 결과가 표시되도록 고객명, 가입일, 상품명, 불입액(월), 일체일, 만기금액필드명을 선택해 복사한 후 [K8] 셀부터 붙여 넣습니다.
4. [표1]에서 임의의 셀을 선택한 후 [데이터] 탭 - [정렬 및 필터] 그룹 - [고급]을 선택합니다.
[고급 필터] 대화 상자가 실행되고 목록 범위가 자동으로 지정됩니다. 조건 범위에는 [K3:KD4] 셀 범위를 지정합니다.
다른 장소에 복사를 선택한 후 활성화된 복사 위치에 [K8:P8] 셀 범위를 지정하고 [확인]을 누릅니다.
5. [K8] 셀부터 조건에 맞는 결과가 표시됩니다.
컴활 1급 실기를 첫 도전하는 분들을 위해 함수식을 풀어서 설명하고 고급 필터의 내부 동작을 실습을 통해 확인해 보겠습니다. 앞서 풀이한 내용을 이해했다면 이 부분은 생략하고 기출 2로 바로 넘어가면 됩니다.
다시 조건부터 보겠습니다.
1. ‘상품명’이 '정기'로 시작하고 '성별'이 '남'인 경우
수식] =AND(LEFT(D4,2)="정기",B4="남")
상품명[D4]의 왼쪽부터 2개 문자를 추출LEFT(D4,2)한 값이 정기이고 성별이 남B4="남"이면 AND 함수는 TRUE를 반환합니다.
맨 오른쪽 그림이 결과입니다.
2. '지점명'이 '종로'이고 '불입액(월)'이 100,000원 이상인 경우
수식] =AND(E4="종로",F4>=100000
지점명[E4]이 종로E4="종로"이고 불입액(월)[F4]이 100000 이상F4>=100000인 두 조건이 모두 참이면 AND 함수는 TRUE를 반환합니다.
맨 오른쪽 그림이 결과입니다.
3. 조건1과 조건2 중에 하나 이상이 참인 경우
수식] =OR(K4, L4)
OR 함수는 하나 이상의 조건이 참이면 TRUE를 반환하는 함수로 [K4, L4] 셀 중 하나 이상이 TRUE 이면 TRUE 결과를 구합니다.
[K3:M3] 셀 범위를 선택한 후 <Ctrl + Shift + L>을 누르면 필터 단추가 표시됩니다.
[M] 열의 필터 단추를 눌러 (모두 선택)에 체크 표시를 해제하고 TRUE에 체크 표시를 한 후 [확인]을 누릅니다.
조건에 맞는 행이 필터링 되었습니다. 복잡한 수식이지만 풀어서 익히니 이해가 조금 쉬워졌죠?
기출 2] ‘기본작업-1’ 시트에서 다음과 같이 고급 필터를 수행하시오. (5점)
▶ [A3:H29] 영역에서 ‘고과점수’가 상위 5위 이내이면서 '파견일자'가 2026년 이후인 행에 대하여 '성명', '연령', '학력', '고과점수', '파견일자' 열을 순서대로 표시하시오.
▶ 조건은 [I3:I4] 영역에 입력하시오. (YEAR, LARGE, AND 함수 사용)
▶ 결과는 [K7] 셀부터 표시하시오.
실습 파일
1. 먼저 [I3] 셀에 조건을 입력한 후 [I4] 셀에 조건식을 작성하겠습니다.
기출 2는 조건1과 조건2가 ~이면서로 연결되어 있어 AND 조건이 됩니다.
※ AND는 조건이 모두 참이면 해당 행을 필터링합니다.
2. =AND( 함수를 입력합니다.
조건1: 고과점수[F4]가 고과점수 상위 5위LARGE($F$4:$F$29,5) 이내인지 조건을 비교합니다.
LARGE 함수는 k 번째 큰 값을 구하는 함수로 LARGE($F$4:$F$29,5) 수식은 고과점수[$F$4:$F$29] 범위에서 5번째 큰 값을 구합니다.
※ 5번째 큰 값보다 크거나 같으면 1~5위 사이(이내)의 고과점수가 됩니다.
조건2: 인수를 구분하는 쉼표를 입력하고 YEAR( 함수를 중첩한 후 파견일자[G4]에서 연도를 구해 2026 이상인지 조건을 비교합니다.
※ 2026년 이후는 2026 이상(크거나 같다)의 경우가 됩니다.
3. [표1]에서 성명, 연령, 학력, 고과점수, 파견일자 필드명을 복사해 [I7] 셀부터 붙여 넣습니다.
4. [표1]에서 임의의 셀을 선택한 후 [데이터] 탭 - [정렬 및 필터] 그룹 - [고급]을 선택합니다.
[고급 필터] 대화 상자가 실행되고 목록 범위가 자동으로 지정됩니다.
조건 범위에는 [I3:I4] 셀 범위를 지정합니다.
다른 장소에 복사를 선택한 후 활성화된 복사 위치에 [I7:M7] 셀 범위를 지정하고 [확인]을 누릅니다.
5. 조건에 맞는 결과가 표시되었습니다.
함수식을 풀어서 설명하고 고급 필터의 내부 동작을 실습을 통해 확인해 보겠습니다. 위 내용을 이해했다면 이 부분은 생략하면 됩니다.
=LARGE(Array, k): Array 인수에서 k 번째로 큰 값을 구합니다.
고과점수[F4:F29] 셀 범위에서 5번째 큰 값을 구합니다. 95점이네요. 각 고과점수가 5번째 큰 고과점수F4>=$I$2인지 비교합니다. 수식을 복사해 보면 고과점수 95 이상은 TRUE로 표시됩니다.
※ [I2] 셀은 절대 참조한 후 수식을 복사해야지 셀이 고정되어 변하지 않습니다.
=YEAR(Serial_num): 날짜(Serial_num 인수)에서 연도를 구합니다.
파견일자[G4] 셀의 연도를 구하면 2025가 구해집니다.
파견일자에서 구한 연도가 2026년 이후(2026 이상 또는 크거나 같다)인지 조건을 비교합니다. 수식을 복사하면 2026년 이후 파견일자는 TRUE로 표시됩니다.
두 조건이 모두 일치하는 행을 필터링해보겠습니다.
=AND(I4,J4)를 입력해 두 인수가 모두 TRUE 이면 TRUE 결과를 표시합니다.
컴활 1급 실기 고급 필터 문제는 AND 또는 OR 함수를 사용해 2개의 조건을 제시해 조건에 일치하는 행을 필터링하는 문제입니다.
고급 필터 문제를 놓치지 않고 점수를 모두 획득하려면 출제되는 함수를 모두 익혀두어야 합니다.
다양한 기출문제를 풀이해 보면 좋은 점수를 얻을 수 있습니다.
좋은 결과 얻으시길 바랍니다.
'자격증 > 컴활 실기' 카테고리의 다른 글
| 컴활 1급 실기 엑셀] 고급 필터 3 (0) | 2024.03.11 |
|---|---|
| 컴활 1급 실기 엑셀] 고급 필터 1 (0) | 2024.02.28 |
| 컴퓨터활용능력 2급 실기] B형 연습문제 - 최신 기출 문제 (0) | 2021.06.02 |
| 컴퓨터활용능력 2급 실기] A형 연습문제 - 최신 출제 기준 (0) | 2021.05.30 |