컴퓨터활용능력 1급 자격증 / 엑셀,엑세스 정리내용

컴활이라고 불리는 국민 자격증을 공부하고 있다.

개발자에게 필수적으로 필요한 자격증은 없다고 생각한다.

이 자격증 또한 전혀 상관이 없다.

하지만 나는 개발을 공부하기 위해 다니던 학교를 그만두었다.

그래도 학사학위가 있어야 나중에 뭐라도 기회가 한 번이라도 더 올 것 같아

학점은행제로 학사학위를 취득하고 있다.

학점 취득의 방법 중 하나로 자격증 공부를 하고 있다.

자격증을 따면 학점을 주다니.

대학교 재학시절에는 전혀 몰랐던 새로운 정보들이 너무 많았다.

이래서 4년제 대학교와 독학사나 학점은행제로 취득한 학사학위는 인정을 받지 못하는 것 같다.

아무튼 컴활시험이 초등학교 때 취득했던 워드프로세서 1급과 같은 시험이라고 생각한 것이 내 실수였다.

생각보다 많이 어려웠고

방심해서 시험공부기간을 짧게 잡았던 결과 벌써 1회 낙방했다.

정신차리고 컴활에 대해 다시 제대로 알아보니

실기는 합격률이 20%밖에 안되는 대한상공회의소의 자존심인 시험이었던 것이다.

그래서 이번엔 토요일, 일요일 연달아 두 번 접수를 해놨다.

정말 피와 살인 돈이 빠져나가는 것 같아 너무 아까웠다.

한번에 합격했으면 안나갔을 돈이라 생각하니 더더욱 아까웠다.

하지만 이번에 취득못하면 영영 취득할 기회를 놓칠 것 같아 공부한 김에 얼른 취득하자라고 결심하고

틈틈히 시간을 내서 공부를 하고 있다.

내일 모레가 시험이다.

독학으로 공부하는 것은 이게 문제다.

자기가 하고 싶은 공부만 계속하게 되고 컴활같이 지겨운 반복작업의 공부는 점점 하지 않게 된다.

하루에 1회씩 풀자고 다짐했던 계획은 사라진지 오래고

하루에 몇 가지만 살펴보면 정말 공부를 많이 한 셈이 되었다.

그 결과 컴활을 떨어지고 나서 이후로 실력이 별로 달라지지 않았다.

작은 반도의 나라에서 자격증을 취득하기 위해 애쓰는 20대분들이 존경스럽다.

컴활 공부하면서 스스로 정리한 내용이다.

기초부터 차근차근 정리한 게 아니고 모의고사 풀어보면서

주로 나오는 함수들 헷갈리는 내용들 위주로 엑셀, 엑세스로구분하여 정리하였다.

독학으로 공부할 때는 시나공 책을 이용하였다.

CD도 함께 제공되는데 CD에 채점파일이 정말 유용했다.

시나공 한권이면 컴활 취득하는데 어려움은 없을 것 같다.

물론 시험 공부 기간은 넉넉히 잡고 방심하면 안된다.

컴활 1급 실기 정리.hwp

컴퓨터 활용능력 1급 실기 대비 함수 정리

 

함수		유형	설명
IFERROR(A,B)		IFERROR(수식, 값)	A라는 수식이 오류이면 값을, 오류가 아니면 수식 값을 반환
DCOUNTA(A,B,C)		DCOUNTA(범위, 열 번호, 조건)	A라는 범위에서 C라는 조건에 맞는 것들 중 B라는 열 번호에서 비어 있지 않은 셀의 개수를 반환(열 번호 대신 필드명 or 셀 주소 사용 가능) *DSUM, DAVERAGE ...
SUMPRODUCT(A, B, ..)		SUMPRODUCT(배열1, 배열2, ...) ex)=SUMPRODUCT(D25:F:25, B21,D21)	A라는 배열과 B라는 배열의 각 항목들의 곱을 구하고 각 항목들의 합을 구한다. ex)=D25*B21+E25*C21+F25*D21 cf) 연속된 숫자의 배열을 입력할 때는 { } 로 묶어준다.
SUMIF(A,B,C)		SUMIF(조건이 적용될 범위, 조건, 합계를 구할 범위)	A라는 범위에서 B조건에 맞는 C범위의 값들을 모두 더한다. (=AVERAGEIF) cf) 조건 설정 시 : ">="&A3
COUNTIF(A,B)		COUNTIF(범위, 값)	A라는 범위에서 B에 해당하는 값을 가진 셀의 개수를 반환. B에 부등호를 사용하려면 큰 따옴표를 사용해야 한다.
SUMIFS(A,B,C,D,E,..)		SUMIFS(합계를 구할 범위, 조건1범위, 조건1, 조건2범위, 조건2, ..)	여러 개의 조건에 맞는 합을 구한다. (=AVERAGEIFS) “>=”&A11 -> 이것처럼 부등호는 등호보다 먼저 그리고 “ ” 와 & 로 셀 연결
ISEVEN, ISODD			짝수, 홀수 판별 -> TRUE, FALSE 로 산출
OFFSET(A,B,C,D,E)		OFFSET(셀, 거리, 거리, 높이, 너비)	A라는 셀로부터 행으로 B만큼, 열로 C만큼 움직인 셀부터 D만큼의 높이와 E만큼의 너비에 해당하는 범위를 산출.
INDEX(A,B,C)		INDEX(범위, 행 번호, 열 번호)	A라는 범위에서 B행 C열에 있는 값을 반환. 다른 함수가 B, C 역할을 한다.
MATCH(A,B,C)		MATCH(찾을값, 범위, 옵션)	C옵션 (-1 : 찾을값보다 크거나 같은 값 중 가장 작은 값 -> 내림차순으로 정렬되어야 한다, 0 : 찾을값과 정확히 일치하는 값, 1 : 찾을값보다 작거나 같은 값 중에서 가장 큰 값 -> 오름차순 정렬), 행 번호 도출
VLOOKUP(A,B,C,D)		VLOOKUP(찾을값, 범위, 열 번호, 옵션)	B라는 범위의 첫 번째 열에서 A값과 같은 값을 찾은 후 그 해당 행에서 지정된 열 번호 위치에 있는 데이터를 반환. A값과 정확히 일치 ->FALSE, 근사값->TRUE or 생략
HLOOKUP(A,B,C,D)		HLOOKUP(찾을값, 범위, 행번호, 옵션)	B라는 범위의 첫 번째 행에서 찾을값 A와 같은 데이터를 찾은 후 그 해당 열에서 행 번호에 맞는 데이터를 반환. A값과 정확히 일치 ->FALSE, 근사값->TRUE or 생략
CHOOSE(A,B,C, ..)		CHOOSE(인수, 첫 번째, 두 번째, ..)	인수 A가 1이면 B를, 2면 C를, ... 반환.
MID(A, B, C)		MID(텍스트, 시작위치, 개수)	A라는 텍스트에서 B번째부터 C만큼 추출한다.
LARGE(A,B)		LARGE(범위, N번째)	A라는 범위에서 N번째로 큰 데이터 추출
MAX		MAX((조건1)*(조건2)*(최대값을 구할 범위))	범위에서 조건1, 조건2를 동시에 만족하는 데이터 반환
UPPER(A)		UPPER(값)	값에 해당하는 문자를 모두 알파벳 대문자로 표현.
LOOKUP(A,B,C)		LOOKUP(찾을값, 도출할 값 범위, 찾을값이 위치한 범위)	C라는 범위에서 A라는 값이 갖은 행OR열번호
VALUE(A)		VALUE(텍스트)	A라는 숫자 형식의 텍스트를 숫자로 변환.
REPT(A,B)		REPT(텍스트, 숫자)	"A" 문자이니 큰 따옴표로 묶어준다. A라는 텍스트를 B만큼 반복하여 반환. B에는 일반 숫자보다 함수가 대신 들어가 계산한 값을 결과로 반복하는 패턴.
IF포함 배열수식		=AVERAGE(IF((조건)*(조건),(조건에 맞는 계산할 범위))	배열 수식에서 AND조건은 * 로, OR조건은 +로.
금융함수	매월불입금	PMT(연이율/12,기간*12,대출금)	A/12, B(월), -C(매월 납입하는 금액), E : 매월 초 납입 = 1, 월말에 납입 = 0 or 생략
	투자가치	FV(연이율/12,기간*12,예금액)
	현재가치	PV(연이율/12,기간*12,예금액)
사용자 정의 함수		selet case / case is / ... / case is / case else / end select
		if then / elseif then / else / end if

 

< Excel > 헷갈리는 부분 정리

 

I. 블록 지정을 해야 하는가

- 블록 지정을 하지 않고 데이터 영역 안에 셀 포인터를 놓으면 되는 것들.

1. 정렬

2. 고급필터 = 단, 조건은 블록지정을 통해서 한다.

3. 차트작성 = 데이터 영역 밖에 셀 포인터를 두고 차트 마법사 열기

 

- 블록 지정을 해야 하는 것

1. 셀 서식 지정

2. 부분합 = 열 레이블 포함해서 모든 데이터 영역을 블록 지정

cf) 부분합을 하기 전 반드시 정렬이 선행되어야 한다.

3. 조건부 서식 = 서식이 적용될 곳만 한다. -> 열 레이블은 지정X

4. 자동 필터 = 열 레이블만 블록 지정

5. 데이터 표 (가상분석) = 수식이 입력된 셀부터 열 레이블과 데이터 영역이 입력될 곳 까지 블록 지정

6. 데이터 통합 = 데이터를 통합할 영역을 블록 지정 후 ‘통합’데이터 상자 열기.

= 통합할 데이터가 있는 레이블만 블록 지정한다.

 

II. 함수

1. D들어간 함수는 필드 명까지 포함해서 블록 지정을 한다. (조건을 따로 입력한다.)

2. ~IF 함수는 조건에 큰따옴표 “” 를 해준다.

3. 배열에서 모든 셀에 같은 값을 나타나게 하려면 입력할 셀을 블록지정하고 컨 쉬 엔

4. 사용자 정의 함수에서 if 사용 -

5. MID 함수가 도출하는 값은 텍스트 형식이다. *1을 해주어야 숫자 형식으로 반환된다. 주의!

 

III. 기타

1. 고급 필터에서 김으로 시작하는 값 추출 -> 김* // 김으로 끝나는 값 추출 -> right 함수 이용.

2. 피벗 테이블에서 값에 해당하는 부분 위치 조정할 것.

3. 셀 서식 지정에서 ; 로 여러 가지 조건을 넣을 수 있고 0일 경우 조건은 ;;“” 으로 처리한다.(세미콜론 두 번)

4. 부분합을 두 가지 경우에 대해서 지정할 때 두 번째 경우 영역을 다시 지정해주어야 한다.

5. 고급 필터에서 수식을 이용한 조건을 지정할 때 절대참조 필수 그리고 조건 필드명은 조건으로!

6. 피벗테이블은 완전 일치해야 한다.

= 셀 서식 지정할 때에는 숫자에만, 그룹은 개개별로 지정해도 무방, 그룹 후 목록 순서도 맞아야한다.

7. 천의 자리까지만 표시 0, (셀 서식 사용자 정의로 하는 방법이 있고 표시 형식을 통해 하는 방법이 있다.)

문제에 나와 있는 그대로 해야 한다.

8. 조건 적용 시 시작하는 문자 적용할 때 LEFT 함수를 사용해야 한다. AA*을 사용하면 결과 값이 다르게 도출.

9. weekday 는 주어진 날짜를 요일로 환산하여 숫자로 반환 월요일부터 1 일요일은 7

10. 외부데이터 가져오기 할 때 조건에 맞는 행만 가져올 때에 “또는”이라는 조건이 있으면

데이터로 돌리기 전에 쿼리 편집에 들어가서 또는 조건으로 변경해서 데이터를 되돌리기를 해야 한다.

11. 외부데이터 가져오기 중 날짜를 기준으로 행 옵션을 설정할 때,

예를 들면 특정 달에 해당하는 열만 가져올 때 부등호로 그 달에 해당하도록 만든다.

12. TEXT() 함수 사용 시 format에 “ ” 로 묶어준다.

엑 세 스 정 리

I. 속성시트 탭별 주요 기능 정리

1. 형식 탭

- 기본 보기(연속 폼, 기본폼) / 스크롤 막대 / 레코드 선택기 / 탐색 단추 / 최대화, 최소화 단추 보이기 설정

- 중복 내용 숨기기(여백 영역 아래에 위치) / 인쇄 설정 -> 페이지 바꿈(구역 전/후)

2. 데이터 탭

- 추가/편집/삭제 가능 여부 / 레코드 원본 설정 -> ... 누르면 쿼리작성기 -> 필드추가(바운드)

- 컨트롤 원본 -> 바운드시키기

3. 기타 탭

- 컨트롤 명 설정 / 팝업 설정 / 모달 설정(폼이 열려 있을 경우 다른 작업을 수행할 수 없음 -> 모달설정 / 탭 정지 설정

+ 외부데이터 가져오기에서 첫 번째 행은 필드 이름임 -> 체크

 

II. 필드 속성 탭별 주요 기능 정리

1. 일반

- 입력마스크 문제에서 반드시 값을 입력하라는 말은 입력마스크에 내포되어 있으므로 필수 기능 그대로 유지

- 입력마스크 문제에서 데이터 입력자리 문제 : 000-0000;;#

-> ;_ = _자리에 0이 오면 -,/,= 과 같은 것도 데이터로 저장 // 1이나 공백이면 입력된 데이터만 저장

-> ;# 은 데이터가 입력될 자리에 #이 나타나있음.

- 형식 문제에서 0“만원” -> 숫자 뒤에 텍스트 삽입

- 입력되도록 과 표시되도록 의 차이를 조심할 것.

- 유효성 검사 규칙

1) 글자 길이 = len , 포함하지 않는 값 = instr

2) 1~99 = >=1 And <=99

3) Like "A-???" = A-로 시작하는 5개 문자

 

2. 조회

- 콤보상자 설정 시 행 원본 ... 으로 들어가서 쿼리를 작성해줘야 한다.

- 도서대여 테이블의 도서코드에 대해 / 도서목록 테이블의 도서코드와 도서명이 콤보 상자의 형태로 나타나도록.

=> 도서대여 테이블의 도서코드에 대해서만 콤보상자 지정 후

행 원본 ...으로 가서 도서목록 테이블의 도서코드와 도서명에 대해서 쿼리 작성

- 그러므로 열 너비를 지정할 때에는 두 가지에 대해 콤보상자를 지정한 것이므로 세미콜론으로 두 가지에 대해 수정.

- 바운드 열 = 필드에 저장할 것. 위와 같은 경우면 1일 때는 도서코드 2일 때는 도서명

- 콤보상자 또는 목록상자에서 쿼리에 행 원본에 추가한 필드를 보이지 않게 하려면

쿼리 상에서 표시체크를 해제하는 것이 아니라 열 너비를 0으로 지정하여 보이지 않게 한다.

 

 

III. 폼

1. DSum("대여금액","대여내역입력","도서명='귀천'")

+) Dlookup(“거래처명”,“거래처”,“거래처코드=txt거래처코드”)

-> 납품내역 폼의 txt거래처명 컨트롤에는 txt거래처코드에 해당하는 거래처명이 표시되도록 구현하시오.

= txt거래처명 컨트롤은 입력하는 곳 //

= Dlookup(도출할 값, txt거래처코드의 테이블(거래처), 조건)

+) Dmax("승진시험점수“,”사원정보“,”직위=‘사원’“)

= 최대값을 구할 범위(도출할 값의 범위), 테이블 명or쿼리명, 조건(조건은 큰따옴표로 묶어 주고 레코드 내용은 작은따옴표로)

2. =LCASE () - 소문자로 / =UCASE() - 대문자로

3. 고유값 “예” - 중복되지 않게 DISTINCT

4. 전체 데이터를 대상으로 일련번호 형태로 표시하려면 ‘누적합계’ 속성에서 ‘모두’를 선택

[데이터]탭 누적합계 그룹별로 -> ‘그룹’ 선택

5. 매개변수쿼리 작성, 조건 입력 시 []로 묶기.

6. 쿼리 작성 시, 드래그로 해결되지 않는 필드 즉 수식을 입력하여 만드는 필드의 경우 필드명에 직접 수식 입력.

= 필드명: 수식 이런 식으로.

7. 컨트롤 원본에 MOD 함수 사용 시 A MOD B 형식으로 쓴다. ( MOD(A,B)가 아니다.)

8. 바운드 시키는데 다른 테이블의 레코드를 바운드 시키려면

일단 행 원본 유형을 테이블/쿼리로 변경한 담에 콤보 상자 설정하는 것처럼 필드를 선택해주면 된다.

9. 합계를 구하고 합계가 0인 경우 0을 표시하려면 형식에 0

10. 폼에서 조건부 서식을 적용할 때, 식이 탭에서 =으로 시작하지 않는다.

11. 조회 속성 설정할 때 일치와 포함하는 이 두가지의 차이를 명확히 구분하여 적용하기 LIKE 와 = 의 차이임.

IV. 처리 - Docmd 정리

1. 인쇄 미리보기

Docmd.Openreport. "보고서 명“, acViewPreview , , "고객이름 = ‘” & cmb고객이름 & “’”

2. 입력한 고객이름과 고객 폼에 있는 고객이름이 같은 폼 열기

Docmd.Openform. "폼 명“,acNormal , , "고객이름 = ‘” & cmb고객이름 & “’”

-> , , 사이에는 표시할 내용을 제한하는 필터 이름이 입력되는 위치.

3. 닫기

Docmd.close

Docmd.close acform, "닫을 폼 명“

4. Msg Box ( aa=MsgBox("[문구]“, vbYesNo + vbDefaultButton2, "대화상자제목”) )Dim aa 로 변수 선언 후

vbOKOnly : <확인>단추만

vbOKCancel : <확인><취소> 단추

vbYesNoCancel : <예><아니오> 단추

+ vbDefaultButton1 : 기본적으로 첫 번째 버튼이 선택되도록.

 

V. 쿼리

1. 포함하지 않은 쿼리 -> not in(select 상품코드 from 판매현황)

2. 포함하지 않는 쿼리 -> is null

2. 매개변수쿼리 만들 때 조건은 []로 묶어준다.

 

VI. 기타

1. instr(필드명,찾을 문자)=0 개수를 도출하는 함수이다. 필드에 속한 레코드 중 찾을 문자가 없게 한다.

응용>> len(A) - instr(A,-) = 7 이면 A라는 필드 값에서 - 다음으로 7개는 무조건 입력.

3. 레코드 정렬은 !:왼쪽 정렬, ~:오른쪽 정렬

4. 오늘날짜 다음날 DateAdd("d",1,date()) 출력형식, 더할 값, 시작 값

5. Datediff(A, B, C) (형식, 시작날짜, 마지막날짜) "yyyy","어제“,”오늘“

6. switch(조건, 산출 값, 조건, 산출 값, ... )

7. string(개수, 반복할 문자) vs Rept(반복할 문자, 개수)

8. 등호가 아닌 부등호부터.

9. 보고서 속성 시트에서 형식에 @*() @ 자리에는 문자 데이터가 입력 * 은 다음에 오는 문자를 셀 너비만큼 반복하여 채운다.(())

 

단기속성 프로시저

=>전략 : 폼 실행시키기와 초기화 시 콤보상자 or 목록상자 만들기로 묶여있는 한 문제를 맞춘다.

그리고 종료 문제가 따로 있으면 그것도 맞추도록 한다.

1. 폼 실행시키기 -> 폼 명.show

 

2. 콤보상자 & 목록상자 만들기

cmb0000.rowsource = "A0:Z:99" 이렇게

lst0000.rouwsource = " "

lst0000.additem "ㅁㅁㅁ“

lst0000.additem "ㅂㅂㅂ“

 

3. 기본값 설정하기

cmb0000 = " "

lst0000 = lst0000.List(0,0)

 

4. 옵션 초기값 설정하기

1) 폼에서 옵션의 목록들을 살펴본다

opt메일 = true

 

5. 폼 종료하기 / 전체 수 표현

msgBox "전체 입력 건수는 “ & [a3].currentregion.Rows.count -1 & "건입니다”

Unload Me

 

6. 포커스 이동하기

cmb0000.SetFocus

 

7. 워크시트 활성화(Activate)

[셀 명] = “______”(활성화 될 경우 셀에 값을 입력)

VII. 조회는 딱 세 가지이다. 이벤트 프로시저 3가지 암기.

1. 필터기능

Me.Filter = “000 =” & AAA

Me.FilterOn = True(False일 경우엔 Filter 속성을 해제하는 것) ------입력된 값과 같은 정보를 표시할 때 이용.

ex) Me.Filter = "물품코드='" & cmb물품 & "'"

Me.FilterOn = True

-->> 왜 물품코드 인가 = 물품코드와 물품명이 행 원본으로 설정되어있고 바운드 열값이 1이므로

1) txt제품명 컨트롤에 제품명을 입력하고 버튼을 클릭하면 제품명이 txt제품명의 값과 같은 제품의 정보를 찾아 표시하라.

= “제품명=‘” & txt제품명 & “’”

2) 가입일이 txt조회에 입력된 값과 같은 레코드만 표시

= “가입일 =#” & txt조회 & “#”

 

2. Recordsource 속성

Me.RecordSource = “SELECT * FROM (테이블명) where (필드명) like '*" & AAA & "*'"

1) 담당자 이메일 주소가 txt이메일 에 입력된 글자를 포함하는 제품의 정보를 찾아 표시

= Me.RecordSource="SELECT * FROM 제품별판매현황 where 담당자이메일 like '*" & txt이메일 & “*‘”

2) 도서명 필드에 txt찾기에 입력된 문자를 포함하는 도서의 정보를 찾아 표시

= Me.RecordSource="SELECT * FROM 도서 where 도서명 like '*" & txt찾기 & “*‘”

 

3. RecordsetClone / BookMark 속성 / FindFirst 메소드

Me.RecordsetClone.FindFirst “000 =” & AAA

Me.Bookmark = Me.RecordsetClone.Bookmark ------입력한 값과 같은 값의 정보를 표시

ex)

1) 판매번호가 txt판매번호의 값과 같은 제품의 정보를 찾아 표시

= Me.RecordsetClone.findfirst "판매번호 =“ & txt판매번호

Me.Bookmark = Me.RecordsetClone.Bookmark

 

※ 문자열 연결하기 ※

0. 필드의 데이터를 연결하기 위해서 필드명을 사용할 때는 문자로 인식하지 않고 데이터로 인식한다.

필드명이 문자이어도 일단 작은따옴표를 하지 않는다.

그리고 그 필드의 데이터 속성(문자, 숫자, 날짜)에 따라서 다음의 경우가 나뉜다.

1. 컨트롤 데이터 형식이 문자일 경우 작은따옴표로 컨트롤명을 둘러싸 줘야한다.

그에 따라서 컨트롤명과 작은따옴표를 이어줄 ‘앰퍼샌드’(&)가 필요하다.

[ = ‘” & 컨트롤명 & “’” ]

2. 숫자일 경우엔 아무것도 필요 없다. 단지 앞의 내용과 연결시켜주기 위해 ‘앰퍼샌드’ 한 번이 필요하다.

[ = ” & 컨트롤명 ]

3. 날짜일 경우엔 샵(#)으로 컨트롤명을 둘러싸 줘야한다.

문자의 경우와 마찬가지로 이 경우도 ‘앰퍼샌드’를 동반한다.

[ = #” & 컨트롤명 & “#” ]

4. Like를 사용할 경우 ‘에스테리스크’(*)를 동반한다.

그리고 문자열의 경우에만 Like가 가능하기에 문자 경우처럼 작은따옴표를 동반한다.

[ = ‘*” & 컨트롤명 & “*’” ]

-> ~을 포함하는 이라는 조건이 붙을 때 사용한다.

주의! = 따옴표 사이에는 공백(띄어쓰기)이 있으면 안 되고 앰퍼샌드 앞뒤로 공백이 필요하다!

 

컴퓨터 활용능력 시험을 대비하는 사람들에게 많은 도움이 되었으면 좋겠다!

-..-