컴활1급실기 요약(2020년 엑셀정리)

안녕하세요 3월 시험일정이 다 취소되어서 4월달에나 시험을 볼수있는데요

남은 기간 완벽하게 준비하시라고 컴활실기 요약본 준비해봤습니다

엑셀

part.1 기본 작업 (25점 - 4문제)

 외부 데이터 가져오기 (10점)

 

> 초과

< 미만

>=이상 (날짜에서는 이후) ex) 2019년 이후 >=2019-01-01

<=이하 (날짜에서는 이전) ex) 2019년 이전 <=2019-12-31

<> 제외한, 같지 않다, ~아닌

= 같은, 같다

AND : 이고, 이면서, 중에서

OR : 이거나, 또는 , ~와

*동 : 동으로 끝나는 문자

홍* : 홍으로 시작하는 문자

*길* : 길을 포함하는 문자

고급 필터(5점)

  절대로 조건수식에서는 같은 필드명을 쓰면 안됨 ★

1) 조건수식은 대표문자 사용불가 -> 대신 left, right, mid등 함수를 이용하라 알려준다.

2) 비교할 필드의 첫번째 셀주소를 수식으로 이용

3) 비교할 평균값이 전체평균보다(average), 전체합(sum)보다 이상인 경우

※ 대표문자사용못하는 함수식 

if문의 조건식, 조건부서식의 조건식, 고급필터의 조건식, 배열수식의 조건식

조건부 서식(5점)

1) 머릿글행을 제외한 전체 레코드 범위를 설정(문제에서 범위 제시)

2) 비교할 필드의 첫번째 셀주소를 수식으로 이용한다.

3) 비교할 필드의 첫번째 셀주소는 혼합참조 열고정으로 이용 : $열행 (반드시 그런것은 아님 -> 혼합참조 행고정도 문제 출제)

4) '비교할필드의 전체평균보다... 크거나 같고' 문제가 출제가 되면 오른쪽에 있는 함수의 범위는 반드시 고정시켜야 한다.

조건부서식 자주나오는 함수정리

1.LEFT,RIGHT,MID 함수 사용 시 “ ” 붙여주기 ( 또는 value , *1 하기)

2. AVERAGE 함수 ex) =D7>=AVERAGE($D$7:$D$11)

3. LARGE/RANK 함수

상위 3위 ex) D7>=LARGE($D$7:$D$11,3)　

하위 3위 ex) D7<=SMALL($D$7:$D$11,3)

4. RANK 함수

상위 3위 =RANK(D7,$D$7:$D$11,0)<=3 // 0일 경우 상위 값

하위 3위 =RANK(D7,$D$7:$D$11,1)<=3 // 1일 경우 하위 값

5. MONTH/YEAR 함수

6. COUNTIF 함수 ex) <김씨/임씨 성을 가진 사람의 인원수가 20 이상인 행>

=COUNTIF($D7:$D9,">=20")=2 // 만약 보기가 김씨/임씨/박씨 3개면 마지막에 =3 을 한다.

7. ISODD(나눈값이 홀수면 true)

QUOTIENT(나눈 몫의 정수부분 반환) ex) =ISODD(QUOTIENT($F8,100)) 

 -보호나 페이지 레이아웃 여기는 워낙 쉽고 크게 실수할만한 부분이 없어서 생략-

피벗테이블 (10점 - 부분점수 x)

 <참고사항>

1. 천단위 구분 기호

-셀 서식 - 사용자 지정 : #,##0 : 3자리마다 천단위 기호 표시

: #,##0,“천원” : 2500000 일 경우 2,500천원

: 양수;음수;0;텍스트

- 쉼표 스타일 : 홈 - 표시형식 - 쉼표스타일

- 셀 서식 - 숫자 : 천단위 구분기호 (,) 사용

- 빈 셀을 ‘**’ 표시 ( [피벗테이블 옵션] - [레이아웃 및 서식] - [빈 셀 표시] 에 ** 작성 )

매크로 (10점)

 - 의식적으로 셀 포인터 데이터 영역 밖에 위치한 후 매크로 시작 / 수식 작성 후 데이터 영역 밖에 위치한 후 기록 중지할것

  

유형 1. 목표값 찾기

 < 수식셀 / 찾는값 / 값을 바꿀 셀>

ex) 수학평균 [A1] 이 98점이 되려면 남학생 수학성적 [A6]이 얼마가 되야 하는가 ?

 너무 고민하지 말고 (수식셀 = [A1]) // (찾는 값 = 98) // (값을 바꿀 셀 = [A6])

그냥 문제의 지문 순서대로 설정하면 됩니다.

  

유형 2. 데이터 통합

 - 조건에 맞게 설정 후 (사용할 레이블 - [ 첫행 ,왼쪽 열 ] 체크 꼭 하기  )

- 기록 중지 전 셀 포인터 위치 주의

   

유형 3. 부분합

 - 오름차순 / 내림차순 정렬 후 부분합 실행

- 부분합 추가시 새로운 값으로 대치 꼭 체크

- 기록 중지 전 셀 포인터 위치 주의

 

유형 4. 데이터 표

 - 행*열 수식 설정 한다

- 데이터 표 전체를 블록 설정 한다.

- 행 입력 셀 / 열 입력 셀 수식 입력한다.

- 기록 중지 전 셀 포인터 위치 주의  

Part 4 -기타작업 (25점)

 차트(10점)

 - 데이터 표 // 세로 축 ,세로보조 축 // 레이블 값 // 제목 위치 // 범례 위치 // 그림자// 테두리 주의 

- 문제 지시 사항에 별도로 지시가 없어도 확인결과에 맞게 작업해야 합니다.

 

VBA 프로시저(15점)

 문제 1. (자주 출제되는 유형)

무조건 1번문제는 이문제가 나옵니다 

다음과 같이 vbe모드로 활성화된다.

폼이름.Show

ex) Private Sub cmd사원입력_Click()

 신입사원입력.Show

Private Sub UserForm_Initialize() << UserForm_Initialize 꼭  확인하고 타이핑

 -시트에 영역이 있을 경우 : Cmb교수명.RowSource = "J4:J8"

 

-다른 시트에 있는 경우 : Cmb교수명.RowSource = "학과명!J4:J8"

(공백,숫자,특수문자 가 포함될 경우 "‘학과명-1’!J4:J8" -> 시트명에 ‘ ’ 표시)

 -시트에 없을 경우 : Lst학과.AddItem "경영학과"

 -목록상자에 열이 2개있을 경우 : Lst과목강사.Colunmcount=2

 - 수강이 선택 되도록 해라 : opt수강 = true 

■ 메세지 박스의 2가지 사용법

▶ 단순 사용법

msgbox "메세지 내용",아이콘모양+확인란,"메세지타이틀"

-- 아이콘모양 4가지

1) vbCritical : 경고

2) vbInformation : 정보

3) vbExclamation : 알림

4) vbQuestion : 물음

 

-- 확인란

1) VbOkonly : 확인 -> 기본값으로 생략가능

2) VbYesNoCancel : 예, 아니오, 취소

3) VbYesNo : 예,아니오

4) VbOkonlyCancel : 확인, 취소

 

--msgbox에 자주사용되는 함수

1) date : 시스템날짜

2) now : 시스템날짜및시간

3) time : 시스템시간

4) Me.Name : 자기자신 폼이름

이런유형으로 나옴 - ex)폼 활성화시[A1]시트에 글자를 나타내라.

- Font 속성 : [A1].Font.Bold = true (글꼴 굵게)

: [A1].Font.Name = "궁서체“ (글꼴 모양)

: [A1].Font.Size = 12 (글꼴 크기)

: [A1].Font.Italic = true (기울임 )

▶ 매개변수 사용법

dim a as integer

 a=msgbox("메세지 내용",아이콘모양+확인란,"타이틀내용")

 if a=vbyes then

 msgbox "수고하셨습니다"

 unload me

end if

         ※ VBA 2번 문제는 과감하게 버리시는게 추천합니다 ※ 

            계산문제 한문제 더 맞출 생각하시는게 낫습니다.

★ 계산문제를 마지막에 배치한 이유는 많이들 아시겠지만 여기서 세개 이상 맞춰주셔야 합격가능성이 올라갑니다.

배열수식 ()가 상당히 헤깔리기 때문에 완벽하게 숙지하셔야 실수 안하고 문제 푸실수 있습니다.

■ sum을 이용한 합계 -> max(최대값), large(큰값)

▶ 조건식1개

1) =(조건식)*값을구할범위

2) =sum((조건식)*값을구할범위)

  -> =max((조건식)*값을구할범위)

  -> =large((조건식)*값을구할범위,2)

3) Ctrl + Shft + Enter

▶ 조건식2개 이상

1) =(조건식1)*(조건식2)*값을구할범위

2) =sum((조건식1)*(조건식2)*값을구할범위)

  -> =max((조건식)*값을구할범위)

  -> =large((조건식)*값을구할범위,2)

3) Ctrl + Shft + Enter

■ sum을 이용한 개수

▶ 조건식1개

1) =(조건식)*1

2) =sum((조건식)*1)

3) Ctrl + Shft + Enter

▶ 조건식2개 이상 -> '*1' 생략가능

1) =(조건식1)*(조건식2)*1 -> =(조건식1)*(조건식2)

2) =sum((조건식1)*(조건식2)*1) -> =sum((조건식1)*(조건식2))

3) Ctrl + Shft + Enter

■ sum,if를 이용한 합계

▶ 조건식1개

1) =if(조건식,값을구할범위)

2) 합계 : =sum(if(조건식,값을구할범위))

   평균 : =average(if(조건식,값을구할범위))

   최대 : =max(if(조건식,값을구할범위))

   최소 : =min(if(조건식,값을구할범위))

   큰값 : =large(if(조건식,값을구할범위),k)

   작은값 : =small(if(조건식,값을구할범위),k)

   개수 : =count(if(조건식,값을구할범위))

3) Ctrl + Shift + Enter

▶ 조건식2개 이상

1) =(조건식1)*(조건식2),값을구할범위

2) =if((조건식1)*(조건식2),값을구할범위)

3) =sum(if((조건식1)*(조건식2),값을구할범위))

4)  Ctrl + Shift + Enter

■ sum,if를 이용한 개수

▶ 조건식1개

1) =if(조건식,1)

2) =sum(if(조건식,1))

3) Ctrl + Shift + Enter

 

▶ 조건식2개 이상

1) =(조건식1)*(조건식2),1

2) =if((조건식1)*(조건식2),1)

3) =sum(if((조건식1)*(조건식2),1))

4)  Ctrl + Shift + Enter

■ round(반올림)=fixed(반올림한 후 문자값으로 기억), roundup(올림), rounddown(버림)=trunc

1) =round(4376.738,2) -> 4376.74 (,2는 셋째자리에서 반올림 둘째자리까지)

2) =round(4376.738,1) -> 4376.7

3) =round(4376.738,0) -> 4377

4) =round(4376.738,-1) -> 일의 자리에서 반올림 => 4380

5) =round(4376.738,-2) -> 십의 자리에서 반올림 => 4400

6) =round(4376.738,-3) -> 백의 자리에서 반올림 => 4000

■ frequency명령 -> 계급구간의 빈도수(개수)를 구하는 배열함수 ★

1) 빈도수구하는 범위를 반드시 먼저설정한다. 

  -> frequency명령은 세로배열로만 구한는 함수임

2) =frequency(데이터배열구간,계급구간)

3) 반드시 Ctrl + Shift + Enter

 --계급구간 입력방법★

0~59 59

60~69 69

70~79 79

80~89 89

90~100 100

■ index와 match함수(행열의 일련번호) / index와 offset함수(행열의 일련번호)

▶ index

1) =index(행열의범위,행의위치값,열의위치값)

2) =index(범위,위치값)-> 주로 배열수식에서 사용(반드시 그런것 아님)

 ▶ match (행,열의 위치값)

1) =match(검색값,검색범위,옵션) 

2) 옵션 : 1 또는 true(유사일치, 작은값) -> 검색값이 숫자

         0 또는 false(정확히 일치) -> 검색값이 문자

         -1은 : 1도 아니고 0도 아닌것 중에서 큰값으로 정렬되어 있을 때 (내림차순 정렬 되어있을 때)

■ vlookup(수직찾기), hlookup(수평찾기), lookup(찾기)

=vlookup(검색값,테이블범위,취할열,옵션)

1) 검색값(lookup value) : 테이블범위의 첫번째열에서 찾을 값

   시험에서는 검색값과 첫번째열에값을 일치시켜주는 작업이 필요(다양한 함수를 이용 ; left, rigth, mid, sumprduct,..)

2) 테이블범위 : 채우기 핸들링 하므로 절대참조를 사용

3) 취할열(column index number) : 테이블범위의 열의 일련번호

4) 옵션 : 검색값이 문자이면 정확히 일치 false(또는 0) -> 생략불가

  검색값이 숫자이고, 테이블범위의 숫자값이 오름차순 정렬되어 있을 때 유사일치(근사값) 

  true(또는 1) -> 생략가능

=lookup(검색값, 검색범위,결과범위)

1) 검색범위은 단일 범위

2) 결과범위도 단일 범위로 사용

※ 검색값과 검색범위의 값의 속성은 일치시켜야 한다. (숫자=숫자, 문자=문자)

Hlookup(검색값,테이블범위,취할행,옵션) 이고 다양한 문제를 푸셔서 정확한

쓰임을 아시는것이 중요합니다.

단독으로 나오지않고 match or max,left,Light,mid 이런 함수와 연동해서 나오는 편입니다.

■ iferror함수

=iferror(계산식,에러값)

1) 계산식의 결과가 에러가 아니면 그 결과를 나타내고

   (계산식의 결과가 true이면 그 결과를 나타내고)

2) 계산식의 결과가 에러이이면 에러값을 취한다.

3) 에러값은 다양한 값이 올수 있다 : 공백(""), 또 다른 함수가 나올수 있다.

요약인데도 이정도 양입니다 상공회의소의 '자존심' 이라 불리는 만큼 만만한 자격증이 절대 아닙니다

기출 20회 이상 풀어본 경험으로는 여기 범주 안에서 다 나온다고 확신합니다.

배열수식 같은 경우 완벽하게 숙지하시고 가셔야 합격하실수 있으실 겁니다.

※무료강의로는 유튜브에서 기풍쌤 기분 강의 많이 참고했고 교재는 이기적으로 공부하고 정리하였습니다.