컴활 1급 실기 #4. 고급필터, 조건부서식, AND조건 OR조건, LEFT RIGHT MID 함수, Rank EQ Rank AVG 함수, LARGE SMALL 함수

* 고급필터와 조건부서식
범위선택
고급필터 - 머리글까지 포함
조건부서식 - 머리글 포함하면 답이 틀림

 

1. 고급필터
데이터 -고급

목록범위 : 표전체(머리글 포함)
조건범위 : 조건 =~
복사위치 : 결과

* 문제 확인 후 조건 타이핑 하고 조건 만들기 > 머리글 넣어서 드래그, 데이터-고급 123 순서대로 간다.

* 공통에는 F4 붙고, 개별엔 안붙는다

*기출2 고급필터
뒤에 2글자가 "HD"이고
Right(E2,2)="HD"

매출액이 전체매출액의 평균보다 큰 행?
I2>AVERAGE($I$2:$I$37))

=AND(RIGHT(E2,2)="HD",I2>AVERAGE($I$2:$I$37))

 

2. 조건부서식(머리글 없음)
판매량이 상위 3위 이거나 하위 3위 이내인 행전체에 글꼴스타일, 글꼴색 적용해라

ㄱ. 머리글 밑열에 = 수식 쓰기(마지막에 잘라내기 할거임) *영어 앞에 $붙이는 것 주의*

ㄷ. 머리글 빼고 드래그 > 홈- 조건부서식- 새규칙 - 수식을 사용해 서식 지정할 셀 결정 클릭- 서식 복붙

ㄹ. 글꼴색, 채우기 등 지정

*조건부서식
개별적 평균 = 개별적범위 = 절대참조 X
전체 평균= 공통범위= 절대참조(고정)
조건부서식 기준셀에는 앞에 $붙여주기 예) $H3

인공지능,빅데이터분석,로봇공학,사물인터넷,3D프린팅의 평균이 95이상
> 개별적 평균이라 절대참조는 아니지만, 기준셀이라서 $붙여줘야함
$D3:$H3

*기출6
조건부서식
=OR($C3>=4,AVERAGE($D3:$H3)>=95) 메뉴모음 드래그해서 잘라내기
머리글빼고 드래그 > 홈- 조건부서식- 새규칙 - 수식을 사용하려 서식 지정할 셀 결정 클릭- 서식 복붙

 

3. AND 조건 & OR조건

AND : 이면서, 이고

OR : 또는, 이거나


4. LEFT RIGHT MID 함수 = " "(텍스트함수라 =" " 이렇게 큰따옴표 붙여줘야함)

LEFT(문자열, 문자개수) : 왼쪽, 처음, 앞, 맨 앞에서부터 문자추출

RIGHT(문자열, 문자개수) : 오른쪽, 뒤, 마지막에서부터 문자추출

MID(문자열, 시작위치, 문자개수) : 시작위치부터 문자추출 / 예) 3번째부터시작해 2글자

*뒤에 2글자가 "HD"인 함수 조건식
=Right(E2,2)="HD"

기출5
아이디번호 2번째 글자가 0인 조건식
=MID(A3,2,1)="0"

MID(ID번호,2번째글자,1개만가져옴)="0"(글자가 0이고)
: "아이디번호 2번째 글자가 0이고"

5. RANK.EQ, RANK.AVG

RANK(내점수,전체점수,0or1)

RANK.EQ(일반적으로 알고있는거)
내가 전체(이범위)중에 몇등이냐?
높아야 일등 주는거 0
낮아야 일등주는거 1
*똑같이 3등일때 3등 2명 주고 ..

RANK.AVG (중간값으로 나눠줌)
1,2, 3등동률 4등없고 5등나옴
4등으로 다주고 5등으로 넘어가는거

*기출4
=AND(RANK.EQ(K4,$K$4:$K$33,0)>=6,RANK.EQ(K4,$K$4:$K$33,0)<=15)

RANK.EQ(내점수,전체범위F4,큰게좋으면0/ 작은게좋으면1)


6. LARGE SMALL 함수(상위와 하위)
10~50개 중
상위 2개 = 2번째로 큰값 이상인것= 40,50 2개
내점수 >= Large(전체점수-F4,N)

하위 n개 = N번째로 작은 값 이하
내점수 <= Small(전체점수-F4,N)

*F4 =고정=절대참조= 언제? 공통범위일때

기출6
상위10위 이내
내점수 >=Large(전체범위-F4,10(몇번째)) : 전체에서 몇번째로 큰지
Rank.eq(내점수,전체범위,0)<=10

기출5 사물인터넷이 전체 사물인터넷의 하위 5위 이내에 포함되는 행만을 대상으로 설정해라
G3<=SMALL($G$3:$G$26,5))

G3(사물인터넷)<=(이내)SMALL(하위)($G$3:$G$26(전체),5(5번째))
: 내점수가 전체에서 5번째 작은값보다 하위냐?