[컴활 1급 실기]- 엑셀

[엑셀]

 

[문제 1] 기본작업(15점)

- 고급 필터

- 조건부 서식

- 시트 보호 

- 통합 문서 보기

- 페이지 레이아웃

 

 

- 고급필터 (AND, OR) 

* '주문코드'가 "SN"으로 시작하고, '주문가격'이 전체 주문가격의 평균보다 크거나 같은 데이터

-> LEFT, AVERAGE, AND 함수 사용

LEFT(텍스트, 개수)

AVERAGE(인수 1, 인수 2, 인수 3...)

AND(조건 1, 조건 2, 조건 3...)

 

조건

=AND(LFFT(A2, 2)="SN", C2>=AVERAGE(AVERAGE($C$2:$C$15)))

평균값 -> 안에 변하지 않아 

그래서 F4 눌러 

 

* '주문일'의 년도가 2017보다 크고 2020보다 작고, '구분'이 "현금"이 아닌 데이터

-> AND, YEAR 함수 사용

AND(조건 1, 조건 2, 조건 3......)

YEAR(날짜)

 

조건

=AND( YEAR (B2)>2017 , YEAR  (B2) <2020 , J2<>"현금" )

<>: 아니다 라는 의미

 

* '주문가격'이 상위 10위 이내이고 4월, 5월, 6월의 값이 모두 60 이상인 데이터 

-> AND, RANK.EQ, COUNTIF 함수 사용

and(조건1, 조건2, 조건3...)

countif (조건 범위, "조건")

rank.eq(인수(내 점수), 범위(전체 범위: 무조건 $), 옵션)

0(생략): 내림차순/ 높은 수 1등 

1(0이외의 값): 오름차순/ 낮은 수 1등  

 

조건

=AND(  RANK.EQ(C2, $C$2:$C$15, 0)<=10 , countif( D2:F2 ">=60" ) =3 )

 

* '주문가격'이 가장 크거나 '6월' 중 가장 작은 데이터

-> MAX, MIN, OR 함수 사용

 

MAX (범위 $)

MIN (범위 $)

범위에는 무조건 $

 

조건

= OR ( C2= max ($C$2:$C$15) , F2 = min ($F$2:$F$15) ) 

 

* '주문코드'가 "4"로 끝나고 '구분'이 "현금"  또는 "할부"인 데이터

-> AND, OR, RIGHT 함수 사용 

 

조건

= AND (RIGHT (A2, 1) = "4",  OR ( J2="현금" , J2="할부" ) )

RIGHT 함수: 텍스트 함수! -> 문자 추출

--> 4를 "4" 로 표시 

 

* '주문코드'의 다섯 번째 글자가 "1"이고, '결제일'의 월이 9 또는 10인 데이터

-> AND, OR, MID, MONTH 함수 사용

 

조건

= AND ( MID ( A2, 5, 1 ) = "1" , OR ( MONTH ( K2 ) = 9, MONTH (K2) = 10 ) )

 

* '학생코드'의 네 번째 글자가 5 이하이고, '구분'이 "중"으로 끝나는 데이터

-> AND, RIGHT, MID 함수 사용

 

조건

= AND ( MID (A2, 4, 1) <= 5, RIGHT ( D2 , 1 ) = "중" )

 

* '이름'에 "주" 자를 포함하고, '구분'이 "여고" 또는 "남고"인 데이터

-> AND, OR, FIND 함수 사용 

 

조건

= AND ( FIND ("주", B2) >= 1, OR ( D2 = "여고",  D2 = "남고" ) ) 

 

* '총점'이 상위 2위 이내이거나 하위 2위 이내인 데이터

-> LARGE, SMALL, OR 함수 사용

 

조건

= OR ( H2 >= LARGE ($H$2: $H$15, 2) , H2 <= SMALL ($H$2: $H$15, 2) )

 

* '총점'이 가장 크거나 가장 작은 데이터

-> MAX, MIN, OR 함수 사용

 

조건

= OR ( H2 = MAX ($H$2: $H$15), H2 = MIN ($H$2: $H$15) )

범위는 변하지 않으니까 F4 누르기! 

 

*'국어', '영어', '수힉'이 모두 70점 이상이고, '총점'이 상위 5위 이내인 데이터

-> AND, RANK.EQ, COUNTIF 함수 사용

 

rank.eq(내 점수, 범위 $, 옵션)

0 (생략) - 내림차순 (높은 숫자 1등) 

1 (0 이외의 값)  - 오름차순 (낮은 숫자 1등)

 

조건

=AND( COUNTIF ( E2:G2, ">=70" )=3 , RANK.EQ ( H2, $H$2: $H$15) <=5 )

 

* '등록일'이 10일 이전이거나 '국어' 점수가 '영어' 점수보다 작은 데이터

-> OR, DAY 함수 사용

 

조건

= OR ( DAY (C2) <= 10, E2 < F2 )

 

* '제품코드'의 마지막 글자가 짝수이고, '판매시작일'의 년도가 2019 또는 2020인 데이터

-> AND, OR, ISEVEN, RIGHT, YEAR 함수 사용

 

조건

= AND ( ISEVEN (RIGHT(A2, 1) ), OR ( YEAR ( C2 ) =2019, YEAR ( C2 ) =2020 ) )

 

* '구분'이 "식품"이 아니고, '1차', '2차, '3차'가 모두 "완판"인 데이터

-> AND, COUNTA 함수 사용

 

조건

= AND ( B2 <>"식품" , COUNTA ( E2:G2) = 3 )

 

* '3차'가 공백이 아니면서 '판매종료일'이 '판매시작일'의 3개월 후 날짜보다 작은 데이터

-> ISBLANK, EDATE, NOT, AND 함수 사용

 

EDATE(시작날짜, 개월수)

 

조건

= AND ( NOT ( ISBLANK (G2) ) , D2< EDATE (C2, 3) )

 

* '상품코드'가 "PB"로 시작하여 "RL"로 끝나고, '거래금액'이 전체 거래 금액 평균보다 작은 행인 데이터를 표시하되, '구매자', '상품코드', '종류', '수량', '거래금액' 필드만 표시하시오.

 

조건

= AND ( LEFT ( B3, 2 ) = "PB", RIGHT ( B3, 2 ) = "RL", H3 < AVERAGE ( $H$3:$H$29 ) )

구매자 상품코드 종류 수량 거래금액 

 

* '차량번호'의 네 번째 글자가 "하" 또는 "호"이고, '주문코드'의 뒤에 두 글자가 20 이상인 데이터를 표시하시오.

-> RIGHT, MID, AND, OR 함수 사용

 

조건

= AND ( OR ( MID (D4, 4,1) = "하", MID (D4, 4,1) = "호" ), RIGHT ( C4, 2 )*1 >= 20 ) 

*1하고 날짜로 된 걸 일반으로 변경  

 

- 조건부 서식 (행전체, 열전체) 

행전체: $A1

열전체: A$1

 

* 학생코드'가 "R"로 시작하면서 '총점'이 '총점'의 전체 평균을 초과하는 행 전체

-> LEFT,  AVERAGE, AND 함수 사용

 

서식 지정: AND ( LEFT ( $A2, 1) = "R", $I2 > AVERAGE ($I$2:$I$15) )

 

* '이름'의 전체 글자수가 3보다 크거나 같고, '국어', '영어', '수학', '과학'이 모두 70점 이상인 행 전체 

 

서식지정: AND ( LEN ($B2)>=3, COUNTIF ($E2:$H2 , ">=70")=4 )

 

* '학생코드'의 오른쪽 세 글자가 200 이상이고, '구분'이 "여고"가 아닌 행 전체 

-> RIGHT, AND, VALUE 함수 사용

RIGHT 함수: 텍스트 함수

VALUE: 문자를 숫자로 바꿔주세요

 

서식 지정: AND ( VALUE (RIGHT ($A2, 3) ) >=200, $D2<>"여고" )

 

* '등록일'이 홀수 달이고 2020년 1월 1일 이후인 행 전체

-> MONTH, MOD, AND, DATE 함수 사용

 

서식 지정: AND ( MOD ( MONTH ( $C2), 2) = 1, $C2>= DATE(2020, 1, 1) )

 

* 행 번호가 짝수이고 '과학' 점수가 '총점'보다 높은 행 전체

-> ISEVEN, ROW, AND 함수 사용

 

서식 지정: AND ( ISEVEN ( ROW ( ) ), $H2>$I2 ) 

 

* 학생코드'의 오른쪽 세 글자를 100으로 나눈 몫이 홀수이고, '총점'이 전체 총점의 80% 이상인 행 전체

-> AND, ISODD, QUOTIENT, RIGHT, PERCENTILE 함수 사용 

 

서식 지정: AND (  ISODD ( QUOTIENT ( RIGHT ($A2, 3) , 100 ) ), $I2 >= PERCENTILE ( $I$2: $I$15, 0.8 ) )

 

* '총점'의 상위 세 번째 값보다 크거나 하위 세 번째 값보다 작은 행 전체

-> LARGE, SMALL, OR 함수 사용

 

서식 지정: OR ( $I2>LARGE($I$2:$I$15, 3) , $I2<SMALL( $I$2:$I$15, 3 ) ) 

 

* '총점'이 상위 3위 이내이거나 하위 3위 이내인 행 전체

-> RANK.EQ, OR 함수 사용

 

RANK. EQ ( 내점수, 범위, 옵션 ) 

0 생략 / 높은 수 1등- 내림차순

1  0이외의 값 / 낮은 수 1등- 오름차순 

 

서식지정:  OR ( RANK.EQ($I2, $I$2: $I$15 ) <= 3, RANK.EQ($I2, $I$2: $I$15 , 1) <= 3

범위는 변하지 X

 

* '등록일'이 가장 빠른 날짜와 가장 늦은 날짜의 행 전체

-> OR, MAX, MIN 함수 사용 

 

서식지정: OR ( $C2 = MIN ( $C$2: $C$15 ), $C2=MAX ( $C$2: $C$15 ) )

 

* '구분'이 "남고"이고 '등록일'의 요일이 '화요일'이나 '토요일'인 행 전체

-> OR, WEEKDAY, AND 함수 사용

-> WEEKDAY 함수는 '월요일'이 1이 되도록 작성

 

AND ( 구분, OR ( 화, 토 ) ) 

1 (생략) 1일~7토

2 1월~7일 / 1월, 2화, 3수, 4목, 5금, 6토, 7일 

3 0월~ 6일

 

서식지정: AND($D2="남고", OR (WEEKDAY ($C2, 2) = 2, WEEKDAY ( $C2, 2 ) = 6 ) )

 

* '학생코드'에 18이 포함되거나 '국어'가 95 이상인 행 전체 

-> OR, IFERROER, SEARCH 함수 사용 

 

SEARCH ( 찾을텍스트, 문자열, 시작위치) 

#VALUE! FALSE

1,2,3,4... TRUE

 

서식지정: OR ( IFERROR ( SEARCH ( 18, $A2 ) , FALSE ), $E2 >= 95 )

 

* 열 번호를 3으로 나눈 나머지가 홀수이면서 [A1:I1] 영역의 끝나는 글자가 "학"인 열 전체

-> COLUMN, MOD, ISODD, AND, RIGHT 함수 사용

 

= AND ( ISODD ( MOD ( COLUMN ( ), 3) ), RIGHT ( A$1, 1 ) = "학" )

 

- 페이지 레이아웃

1) 용지방향: 내용이 용지의 가로 방향으로 출력되도록 지정함

2) 페이지 가운데 맞춤: 내용이 인쇄 용지의 가운데에 출력되도록 지정함

3) 머리글: 매 페이지 상단의 오른쪽 영역에 시스템의 현재 날짜를 표시함

4) 바닥글: 매 페이지 하단의 가운데 영역에 페이지 번호를 표시함

5) 인쇄 영역: [B2:D42] 영역만 인쇄되도록 지정함

6) 인쇄 제목: 2행이 페이지마다 반복하여 인쇄되도록 지정함

7) 행/ 열 머리글: 행과 열의 머리글이 인쇄되도록 지정함

8) 페이지 나누기: [B2:D22] 영역은 1페이지, [B23:D42] 영역은 2페이지에 인쇄되도록 지정함