컴퓨터활용능력 실기 1급 엑셀 엑세스 함수 정리

요새 취업시즌이라 컴퓨터활용능력 1급 준비하시는 분들많이시죠??

실기는 필기보다 100배는 어려운거 같아요 진심..

너무어려워서 한번에는 못붙을 거 같습니다...

ㅜㅜ

 

그러니까 이렇게 함수라도 많이 외워서 시험장 한번 가봐야겠어요

컴활 1급 실기 공부하시는 분들을 위하여 정리파일올립니다

 

※ 컴활 1급 실기 엑셀 요약정리 ※

 

▶ 외부 데이터 가져오기

데이터-기타원본-Microsoft query / 파일위치는 C드라이브 OA폴더!

조건 다 기입하고 나서 편집기로 확인해보기

‘범위로 변환’ 얘기가 없으면 그대로 두기

 

▶ 고급필터

만약 조건이 논리식(true/false)일 경우, 조건필드명을 원본데이터필드명과 다르게 설정! ex) 조건1, 조건2

결과값을 문제에 줄 경우 제목줄 복사해서 붙여넣기

 

▶ 조건부서식

범위를 미리 설정한 후에, 조건부서식 지정 (제목블록설정X)

수식 입력할 때, 행전체 $A1 / 열전체 A$1 절대참조 넣어주기!

수식이 틀릴 경우를 대비해서 아무 셀에다가 먼저 식을 완성하고 식이 정답이라면 복사해서 조건부서식 칸에 붙여넣기! 셀에 적어뒀던 식은 다시 지우기~

ex) 면접 점수가 상위 10위까지인 전체 행에 대해 ~

=$H3>=large($H$3:$H$27,10)

 

▶ 참조 함수

1) Vlookup,Hlookup,lookup

Vlookup(찾는값, 찾는값포함된영역, 열번호, 범위인정여부)

Lookup(찾는값, 찾는값포함된영역, 결과값영역)

2) match

찾는값이 몇 번지에 있는지 판단

Match(찾는값, 찾는값이속할범위, 타입) => 타입 : 찾는값이 범위보다 큰지,작은지에 따라 1,-1

3) index

지정한 행과 열에 해당하는 값 출력

Index(결과출력범위필드미포함 찾을범위, 행위치, 열위치)

 

countif(조건찾을범위,">=90")=5 (셀주소랑 같이 쓸 때: ">="&A3)

ex) 학과성적, 어학테스트, 면접의 점수가 모두 80이상인 데이터를 표시하시오.

=countif(F3:H3,">=80")=3

sumifs(합할범위,조건찾을범위,">=90", ...)

 

데이터베이스 함수

Dxx(전체셀범위, 계산하고자하는 항목의 제목주소 혹은 열위치, 조건) =>조건 : 다른 셀에 별도로 입력

 

▶ 배열수식 : 조건쓸 때, 범위(절대번지)/해당셀(F4로 조정) Ctrl+Shift+Enter

=계산함수(if (조건, 계산범위))

=계산함수( (조건1)*(계산범위))

조건이 and 일때는 (조건1)*(조건2)

조건이 or일때는 (조건1)+(조건2)

 

 

Frequency함수 (절대주소X)

자체가 배열수식이므로, 결과범위(빈셀) 모두 선택한 후 바로 함수식 쓰기

Frequency(빈도구할 자료가 있는 주소범위, 빈도구할범위) Ctrl+Shift+Enter

 

▶ 피벗테이블

하위수준 표시여부 설정 : 각 필드 우클릭 - 확장/축소

 

▶ 매크로

매크로를 실행하기 전에는 무조건 커서를 결과가 나올 곳이 아닌 다른 곳에 놓는다.

1) 부분합 (블록설정 두번!!!!)

- 전체블록설정(필드포함)한 후, 정렬

- 다시 블록설정(필드포함) 후, 부분합 설정

- 부분합을 두 번 설정해야 될 경우에는 , 두번째 부분합을 설정 할 때 ‘새로운 값으로 대치’ 지정해제

2) 데이터표

- 계산식을 행과 열이 교차하는 셀에 지정

- 결과 표(필드불포함) 블록설정한 후, 데이터 표

- 행/열 입력 셀 : 변동하는 셀값이 행/열 인지에 따라, 기준표에서 변동하는 셀값에 해당하는 셀 지정

3) 데이터 통합

- 먼저 표에 레이블을 지정함

- 결과표(필드포함)를 블록설정하고 데이터 통합실행

- 참조영역(필드포함) 블록설정

- 첫 행, 왼쪽 열까지 체크(사용할 영역이 아니라는 뜻)

 

▶ 차트

제목 특정 셀과 연동 : 제목셀에 =[A1]

 

 

 

 

 

 

 

 

 

 

 

 

 

▶ 프로시저

* 콤보상자에 목록 값 입력하는 법 세 가지

1) 개체명.additem Date -5

개체명.additem Date -4 (additem : list상자/콤보상자에만!)

2) 개체명.rowsource=”A1:B5”

개체명.columncount=2

List품목명.RowSource = "'기타작업-1'!a3:b10"

3) with cmb부서명

.additem “영업1팀”

.additem “영업2팀”

end with

* listindex: 콤보상자, 목록상자에서 번지 기억, 0번부터 시작

첫 번째 항목이 선택되도록하기! 개체명. Listindex = 0

* target/셀지정.activate : 커서가 있는 곳으로 셀포인터 이동

target/셀지정.font.bold = true

* 컨트롤명 = true : 초기값으로 체크

* 메세지 박스 표시 후, 폼을 종료

MsgBox format(Time, "ampm HH시 MM분“) & ”폼을 종료합니다.“, , ”폼 종료“

Unload Me

 

MsgBox Me.caption & "폼을 종료합니다.“

Unload Me

 

MsgBox Time, , "폼 종료“

Unload Me

 

* 입력되어있는 행이 몇 행인지

MsgBox "전체 입력 건수는 " & [셀주소].CurrentRegion.Rows.Count - 1 & "건입니다."

 

▶ IF 함수

If 조건 then

else if 조건 then

else

End if

 

▶ Select 함수

Select case 개체명

Case 조건

Case else

End select

만약 날짜형으로 사용할 경우에는 양쪽에 # 넣을 것

Ex) if 입고일>=#2016-10-19# then ~

▶ 엑셀 함수

 

▶ 날짜/시간함수- WEEKDAY(날짜 문자열, 종류) : 날짜 문자열의 요일 일련번호를 구함   → 1 : 일요일을 1로 시작, 2 : 월요일을 1로 시작, 3: 월요일을 0으로 시작- DAY360(시작일, 종료일, 방식) : 1년을 360일(12달*30일)로 가정하고, 시작일과 종료일 사이의 일수를 구함  → 방식 : TRUE는 해당 월의 30일로 대치하고, FALSE는 다음 달의 1일로 계산한다. 생략하면 FALSE로 지정한다.

- DAY(날짜 문자열) : 주어진 달의 날짜를 1에서 31사이의 숫자로 구함

- DATEVALUE(날짜 문자열) : 문자열로 입력한 날짜에 해당하는 일련 번호를 구함  → "1900-01-01"이 일련 번호 1임, D-DAY를 구할 때 사용하면 편하다.- EDATE(시작 날짜, 개월 수) : 시작 날짜에 개월 수를 더한 날짜(EDATE)의 일련 번호를 구함- EOMONTH(시작 날짜, 개월 수) : 시작 날짜에 개월 수를 더한 달의 마지막 날짜(EOMONTH)의 일련 번호를 구함- NETWORKDAYS(시작 날짜, 끝 날짜, [휴일]) : 휴일을 제외한 시작 날짜와 끝 날짜 사이의 업무일 수를 구함- WEEKNUM(날짜, 요일을 결정할 숫자) : 특정 날짜의 주 번호를 반환함- WORKDAY(시작 날짜, 날짜 수, [휴일]) : 시작 날짜에 날짜 수에서 주말이나 휴일을 제외한 평일 수를 적용한 날짜(WORKDAY)의 일련 번호를 구함- YEARFRAC(시작 날짜, 끝 날짜, 날짜 계산 기준) : 일 년 중 시작 날짜와 끝 날짜 사이의 날짜 수가 일 년 중 차지하는 비율(YEARFRAC)을 구함  → 날짜 계산 기준: 0 또는 생략 (한 달을 30일, 1년의 일수 360), 1 (한 달을 실제, 1년의 일수 실제),                            2 (한 달을 실제, 1년의 일수 360), 3 (한 달을 실제, 1년의 일수 365),                            4 (한 달을 30, 1년의 일수 360)

 

 

 

 

 

 

▶ 논리 함수- IF(조건식, 값1, 값2) : 조건식이 참이면 값1을 표시, 거짓이면 값2를 표시- AND(조건1, 조건2, ...) : 모든 조건이 참이면 TRUE, 나머지는 FALSE를 표시- OR(조건1, 조건2, ...) : 모든 조건이 거짓이면 FALSE, 나머지는 TRUE를 표시- NOT(논리식) : 논리식의 결과를 역으로 표시- IFERROR(수식, 값) : 수식에서 오류가 발생할 경우 지정한 값을 반환하고, 그렇지 않으면 수식 결과를 반환함

▶ 데이터베이스 함수- DSUM(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터의 합계를 구함- DAVERAGE(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터의 평균을 구함- DCOUNT(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터의 숫자 개수를 구함- DCOUNTA(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터에서 공백이 아닌 데이터의 개수를 구함- DMAX(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터의 최대값을 구함- DMIN(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터의 최소값을 구함- DSTDEV(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터의 표준편차를 구함- DVAR(데이터베이스, 필드명, 조건) : 조건에 맞는 데이터의 분산을 구함- DGET(데이터베이스, 필드명, 조건) : 조건에 맞는 고유한 데이터를 추출함  → 조건에 맞는 레코드가 없으면 [#VALUE!], 조건에 맞는 레코드가 여러 개이면 [#NUM!]으로 오류값 반환

- DPRODUCT(데이터베이스, 필드명, 조건) : 조건에 일치하는 값들을 곱함

 

 

 

 

 

▶ 문자열 함수- LEN(텍스트) : 텍스트의 문자 수를 구함 - LEFT(텍스트, 문자수) : 텍스트의 왼쪽에서 지정한 문자수만큼 텍스트를 추출함 - MID(텍스트, 시작 위치, 문자수) : 텍스트의 시작 위치에서부터 지정한 문자수만큼 텍스트를 추출함 - RIGHT(텍스트, 문자수) : 텍스트의 오른쪽에서 지정한 문자수만큼 텍스트를 추출함 - LOWER(텍스트) : 텍스트를 소문자로 변환함 - UPPER(텍스트) : 텍스트를 대문자로 변환함 - PROPER(텍스트) : 텍스트를 첫 문자만 대문자로 변환하고, 나머지는 소문자로 변환함 - REPLACE(텍스트1, 시작 위치, 문자수, 텍스트2) : 텍스트1의 시작 위치로부터 해당 문자수만큼 텍스트2로 바꿈 - SUBSTITUTE(테스트1, 변경할 텍스트, 새로운 텍스트, 변경할 텍스트가 몇 째) : 텍스트1에서 몇 번째 변경할 텍스트를 새로운 텍스트로 바꿈

- VALUE(텍스트) : 텍스트를 숫자로 변환함 - TEXT(숫자 값, 표시 형식) : 숫자 값을 표시 형식을 지정하여 텍스트로 변환함 - FIXED(수치, 소수점 이하 자릿수, 콤마 표시 여부) : 수를 반올림하여 텍스트로 만듦  → FALSE(또는 생략) : 콤마를 천 단위마다 표시, TRUE : 콤마를 표시하지 않음 - CONCATENATE(텍스트1, 텍스트2, ...) : 여러 텍스트를 한 텍스트로 합침 - EXACT(텍스트1, 텍스트2) : 대/소문자를 구분하여 텍스트 값에서 다른 텍스트 값을 찾음  → 셀과 셀이 같은 대문자이거나 소문자이면 TRUE, 다르면 FALSE, 대문자와 소문자 구분함 - FIND(찾을 텍스트, 찾을 텍스트를 포함한 텍스트) : 대/소문자를 구분하여 텍스트 값에서 다른 텍스트 값을 찾음 - REPT(반복할 텍스트, 반복할 횟수) :  텍스트를 반복할 횟수만큼 표시 - SEARCH(찾을 텍스트, 찾을 텍스트를 포함한 텍스트) : 텍스트 값에서 다른 텍스트 값을 찾아 시작 위치를 구함(대/소문자 구분안함)

 

 

 

▶ 수학과 삼각 함수- SUM(인수 1, 인수 2, ...) : 인수들의 합을 구함- ROUND(인수, 자릿수) : 인수를 자릿수로 반올림한 숫자를 구함- ROUNDUP(인수, 자릿수) : 인수를 자릿수로 올림 한 숫자를 구함- ROUNDDOWN(인수, 자릿수) : 인수를 자릿수로 내림한 숫자를 구함  → -2 : 십의 자리를 반올림, -1 : 일의 자리를 반올림, 0 : 소수점 첫째 자리를 반올림, 1: 소수점 둘째 자리를 반올림, - SUMIF(범위, 조건, 합계 범위) : 범위에서 조건을 검사하여 합계 범위에 해당하는 셀 합계를 구함  → 조건은 "<=10"처럼 따옴표로 묶어줌- SUMIFS(합계를 구할 범위, 조건 범위 1, 조건 1, 조건 범위 2, 조건 2, ...) : 여러 조건에 만족하는 셀의 합계를 구함- MOD(인수, 제수) : 인수를 제수로 나눈 결과의 나머지를 구함- PRODUCT(수치 1, 수치 2, ...) : 수치를 모두 곱한 결과를 구함  → (수치 1*수치 2*수치 3)- SUMPRODUCT(배열 1, 배열 2, ...) : 수치 배열에 각각 대응하는 요소의 곱을 구하고, 그 결과의 합을 구함  → SUMPRODUCT({2,3,4},{6,7,8})=(2*6+3*7+4*8)- RAND() : 0 이상 1 미만인 무작위 랜덤 숫자 산출  → 0~10 범위 숫자를 산출하려면 RAND()*10, 1~100 범위 숫자를 산출하려면 RAND()*100 - RANDBETWEEN(최소치, 최대치) : 최소치와 최대치 사이의 숫자를 무작위 랜덤으로 산출 - PI() : 원주율 파이 π를 구함  → 5*2*PI()=반지름 5인 원의 둘레, POWER(5,2)*PI()=반지름 5인 원의 면적 - MDETERM(배열) : 배열의 행렬식을 구함 - MINVERSE(배열) : 배열의 역행렬을 구함 - MMULT(배열 1, 배열 2) : 두 배열의 행렬 곱을 구함

- QUOTIENT(피제수, 제수) : 나눗셈 몫의 정수 부분을 반환한다.

 

▶ 재무 함수  → rate : 정기 이율, per : 상환 회차, nper : 상환할 총 횟수, pv : 현재가치, fv : 미래가치, type : 납입시점 * 영문으로 어떤 것을 입력하라는지 뜨기 때문에 알아두면 편함- FV(이율, 납입횟수, 정기 납입액, [현재가치], [납입시점]) : 정기적으로 일정 기간 동안 은행에 적립할 때의 미래가치를 구함(흔히 알고 있는 적금을 말함), 적금을 몇 년 후 이자와 함께 얼마를 받을 수 있는지 가르쳐 주는 함수  → FV(5%/12, 24, -100000, 0, 1) : 정기 납입액은 결괏값을 양수로 얻기 위해 -를 붙여줌, 현재가치를 생략하면 0이 됨, 납입시점은 0(생략) 은 월 말, 1 은 월초 납입을 뜻함- PMT(이율, 불입 총 횟수, 현재가치, [미래가치], [납입시점]) : 일정 금액을 대출받았거나 투자했을 때 정기적으로 매월 또는 매년 납입하거나 수령할 금액을 구함  → PMT(3%/12, 24, 0, -1000000,0) : 이율이 3% 일 때 2년 동안 1000000을 갚으려면 매달 입금해야 할 금액- IPMT(이율, 이자를 계산할 기간, 총 납입기간, 현재가치, [미래가치], [납입시점]) : 일정 금액을 정기적으로 납입할 때 일정한 이자율이 적용되는 투자에 대해 주어진 기간 동안의 이자를 계산  - PPMT(이율, 상환할 회차, 상환 총 횟수, 현재가치, [미래가치], [납입시점]) : 정기적인 상환 기간과 일정한 이율의 대출금이나 투자액에 대한 원금 상환액을 계산- PV(이율, 납입횟수, 정기 납입액, [미래가치], [납입시점]) : 매월이나 매년 일정한 금액을 일정 기간 동안 지불해주는 연금이나 보험의 지급 총액에 대한 현재가치를 구함, 미래에 받을 금액을 지금 받을 경우 얼마인지 계산  → PV(5%/12, 24*12, -100000, 0, 0) : 이율이 5% 일 때 매월 말 100000원씩 24년 동안 지급해 주는 연금의 현재가치- NPV(할인율, 값 1, 값 2, ...) : 특정한 금액을 투자하고 매월 일정한 수입이 보장될 때 해당 투자의 현재가치를 구함  → 지급액은 음수, 수입은 양수, NPV(10%,-100,50,60,70) : 10%의 할인율로 100을 투자하고, 3년 동안 50, 60, 70의 연간 수입을 얻는다면 3년 후의 현재가치

 

 

 

 

 

 

 

 

 

 

▶ 통계 함수 - AVERAGE(인수1, 인수2, ...) : 인수들의 평균값을 구함 - AVERAGEA(인수1, 인수2, ...) : 문자열이나 논리값 등이 있는 인수들에서 평균값을 구함 → AVERAGE(1, 2, 3)=1+2+3/3=2 이고, AVERAGEA(1, TRUE, 2, FALSE, 3, TRUE)=1+2+3/6=1 이다 - AVERAGEIF(범위, 조건, 평균을 구할 범위) : 조건을 만족하는 모든 셀의 평균을 구함 - AVERAGEIFS(평균범위, 조건범위1, 조건1, 조건범위2, 조건2, ...) : 여러 조건을 만족하는 모든 셀의 평균을 구함 - MAX(인수1, 인수2, ...) : 인수들 목록 중 최대값을 구함 - MAXA(값1, 값2, 값3, ...) : 숫자, 텍스트, 논리 값 등 인수 목록에서 최대값을 구함  → TRUE는 1, FALSE는 0 으로 반환 - MIN(인수1, 인수2, ...) : 인수들 목록 중 최소값을 구함 - LARGE(배열, K) : 배열에서 K번째로 큰 값을 구함 - SMALL(배열,K) : 배열에서 K번째로 작은 값을 구함 - RANK(값, 참조 영역, 순위 결정 방법) : 참조 영역에서 값의 순위를 구함  → 0 이나 생략 : 내림차순으로 순위 결정(높은 점수가 1등), 0 이 아닌 값 :  오름차순으로 순위 결정(낮은 점수가 1등)- VAR(표본의 범위) : 표본의 범위에서 분산을 구함 - STDEV(표본의 범위) : 표본의 범위에서 표준편차를 구함 - MEDIAN(인수1, 인수2, ...) : 인수들의 중간값을 구함 - MODE(인수1, 인수2, ...) : 인수들 중 가장 많이 나오는 최빈값을 구함 - COUNT(인수1, 인수2, ...) : 인수들에서 숫자가 들어 있는 개수를 구함 - COUNTA(인수1, 인수2, ...) : 인수들에서 공백을 제외한 인수의 개수를 구함 - COUNTBLANK(범위) : 범위에서 공백인 셀의 개수를 구함 - COUNTIF(조건 범위, 조건) : 조건 범위에서 조건에 맞는 셀의 개수를 구함 - COUNTIFS(평균 범위, 조건범위1, 조건1, 조건범위2, 조건2, ...) : 여러 조건을 만족하는 모든 셀의 평균을 구함 - FREQUENCY(데이터 배열, 조건 배열) : 데이터의 도수 분포를 구함 (Ctrl+Shift+Enter로 완성)- PERCENTILE(배열, K) : 배열(범위)에서 K번째 백분위수 값을 구함  → K는 0에서 1사이의 소수나 퍼센트값으로 전달▶ 찾기와 참조 함수 - VLOOKUP(검색값, 범위, 열번호, [검색 유형]) : 범위의 첫 열에서 검색값을 찾아, 지정한 열번호에서 같은 행에 있는 값을 표시 - HLOOKUP(검색값, 범위, 열번호, [검색 유형]) : 범위의 첫 행에서 검색값을 찾아, 지정한 행번호에서 같은 열에 있는 값을 표시 - LOOKUP(검색값, 검사범위, 대응범위) : 검사범위에서 검색값을 찾아 대응범위에서 같은 위치에 있는 값을 표시  → LOOKUP(검색값, 배열) : 배열에서 첫째 행이나 열에서 검색값을 찾아 마지막 행이나 열의 같은 위치에 있는 값을 표시 - TRANSPOSE(배열) : 배열의 수평/수직 방향을 서로 바꾸어 나타나게 함 (Ctrl+Shift+Enter로 완성)- INDEX(범위, 행 번호, 열 번호, 참조 영역 번호) : 표나 범위의 값이나 값에 대한 참조 영역을 구함  → 보통  INDEX(범위, 행 번호, 열 번호)로 나옴 - OFFSET(기준, 행수, 열수, [높이], [폭]) : 기준으로부터 행 또는 열 수 만큼 떨어진 곳에 있는 특정 높이와 너비의 참조 영역을 표시 - CHOOSE(인덱스 번호, 값1, 값2, ...) : 인덱스 번호에 위치한 값을 구함  → 값1= 1, 값2= 2, 값3= 3 - MATCH(검사값, 검사범위, [검사유형]) : 검사값을 검사범위에서 검색하여 대응하는 값이 있는 경우 상대적 위치를 나타냄 - ADDRESEE(행번호, 열번호, 참조유형) : 행 및 열 번호가 지정되었을 때 워크시트에서 셀의 주소를 확인함 - AREAS(참조) : 참조 영역에 있는 영역 수를 반환 - COLUMN(참조) : 참조의 열 번호를 반환 - COLUMNS(배열) : 참조의 열 수를 반환 - INDIRECT(참조할 텍스트) : 텍스트 문자열로 지정한 셀 주소를 돌려줌  → 처음 지정한 위치만 인식, 범위를 지정하고 "" 따옴표가 있어야 함 - ROW(참조) : 참조의 행 번호를 반환 - ROWS(참조) : 참조의 행 수를 반환

 

 

▶ 정보 함수 - ISBLANK(검사 대상) : 검사 대상 셀 이 공백 실인지를 조사  → 공백 설이면 TRUE, 아니면 FALSE - ISERROR(값) : 값이 오류인지 확인하고 TRUE 또는 FALSE를 반환함  → 오류 메시지가 나오면 TRUE로 반환 - ISEVEN(숫자) : 숫자가 짝수이면 TRUE, 홀수이면 FALSE를 반환함 - ISLOGICAL(값) : 값이 논리값이면 TRUE를 반환, 내용 중 하나라도 틀리면 FALSE를 반환 - ISNONTEXT(값) : 값이 텍스트가 아니면 TRUE를 반환함 - ISNUMBER(값) : 값이 숫자이면 TRUE를 반환함 - ISODD(숫자) : 숫자가 홀수이면 TRUE를 반환함 - ISTEXT(값) : 값이 텍스트이면 TRUE를 반환함 - N(값) : 숫자가 아닌 값은 숫자로, 날짜 값은 일련번호로, TRUE 값은 1, 그 외의 값은 0으로 반환함 - TYPE(값) : 값의 유형을 나타내는 수를 구함  → 값이 숫자 면 1, 텍스트 면 2, 논리값이면 3, 오류 값이면 16으로 반환함

 

 

 

 

 

 

 

 

 

▶ 배열 수식 - 조건을 나열할 때 AND 조건은 *, OR 조건은 +를 사용 - 조건이 만족하면 TRUE, 만족하지 않으면 FALSE를 반환 - TRUE는 1, FALSE는 0으로 변환되어 계산에 사용할 수 있음 - 배열 수식은 Ctrl+Shift+Enter를 눌러 완성◎ 합계- SUM((조건 1)*값을 구할 범위), SUM((조건 1)*(조건 2)*값을 구할 범위)- SUM(IF(조건, 값을 구할 범위)), SUM(IF((조건 1)*(조건 2), 값을 구할 범위))◎ 개수- SUM((조건)*1),  SUM((조건 1)*(조건 2))- SUM(IF(조건, 1)), SUM(IF((조건 1)*(조건 2),1))- COUNT(IF(조건 1, 1)), COUNT(IF((조건 1)*(조건 2),1))

◎ 평균- AVERAGE(IF(조건 1, 값을 구할 범위)), AVERAGE(IF((조건 1)*(조건 2), 값을 구할 범위))◎ 최댓값- MAX((조건 1)*값을 구할 범위), MAX((조건 1)*(조건 2)*값을 구할 범위)- MAX(IF((조건 1), 값을 구할 범위)), MAX(IF((조건 1)*(조건 2), 값을 구할 범위))◎ N 번째 큰 값- LARGE((조건 1)*값을 구할 범위, N), LARGE((조건 1)*(조건 2)*값을 구할 범위, N)- LARGE(IF((조건 1), 값을 구할 범위), N), LARGE(IF((조건 1)*(조건 2), 값을 구할 범위),N)◎ 행(열)에 값을 찾을 때- INDEX(범위, MATCH(찾을 값, 찾을 값을 포함한 범위, 0)

 

 

 

 

 

 

 

 

 

 

 

 

※ 컴활 1급 실기 엑세스 정리 ※

☞ 테이블

▶ 데이터 형식

텍스트 : 255

메모 : 65,535

숫자 : 바이트 (0-255 자) ex) 해당 필드에 100이하의 숫자가 입력될 수 있도록

 

▶ 형식

양수;0;음수

;남[파랑];여[빨강]

! : 왼쪽정렬 / ~ : 오른쪽정렬

 

▶ 입력마스크

입력형식;문자저장여부;기본값

숫자 : 0(필수), 9(선택)

영문자: L(필수), ?(선택)

영문자or숫자: A(필수), a(선택)

대문자: > , 소문자: <

ex) 000-0000;0;_  : 000-0000 입력마스크, ;0 '-' 기호도 저장(생략시 기호저장안함), ;_ 입력전 공백자리에 _로 표시

* 입력된 내용은 모두 ‘*’형태로 표시되도록 입력마스크를 설정하시오. - Password

 

▶ 유효성검사규칙 ("=" 사용 안함)

- Between 값1 and 값2 : 값1에서 값2까지 (값1 >= and <=값2)- Like "김*" : 김으로 시작하는 값

Like "*시" : 시로 끝나는 값

Like "*이*" : 이를 포함하는 값

Like "* *" : 공백을 포함하는 값- Not Like "* *" : 공백을 포함하지 않는 값  → * 글자제한없음, ? 글자수 제한 (?? : 두글자, ??? : 세글자)- Not in (select 전화번호 from 대여)- In(a,b,c) : a,b,c 중 하나

- Len([거래처코드])=4

- Len([주민등록번호])-InStr([주민등록번호],"-")=7

해당 필드에 공백문자가 입력되지 않도록

: instr([필드명]," ")=0 => 공백까지 문자수는 0개, 필드명에 공백넣지마라

 

 

 

 

 

* 필드 이름을 변경하지 않고, ‘새로운 필드명’으로 표시되도록 설정 = 캡션 - 새로운 필드명

* 새 레코드 추가시 기본적으로 0이 입력되도록 설정 = 기본값 - 0

* ‘필드명’ 필드를 기준으로 내림차순 정렬되도록 테이블 속성을 설정

= 속성시트 - 정렬기준 - 필드명 DESC

* 2가지 값만을 가질 수 있도록 데이터 형식을 설정 = YES/NO 형식

* 기본적으로 오늘 날짜의 다음날이 입력되도록 설정(DateAdd, Date)

=DateAdd("d",1,Date())

 

☞ 폼

date() : 날짜

now() : 날짜 + 시간

Format(Now(),"yyyy-mm-dd aaa hh:nn:ss ampm")

aaa : (월) , aaaa : 월요일

ampm : 오전/오후

weekday : 1(일)

ex) 형식 탭 : yyyy-##-##

 

▶ 레코드 원본

Me.recordsource="select 필드명 from 테이블 where 조건" (보통 필드명에는 *가 들어간다)

 

▶ 컨트롤원본

Lcase() : 소문자 , Ucase() : 대문자

 

* 오름차순, 내림차순 정렬

- Me.orderby = "필드명 asc/desc" (띄어쓰기 꼭 해야함!)

Me.orderbyon = true

- Select * from 테/쿼명 orderby 판매번호 asc/desc

 

dateadd, datediff 함수

=dateadd("yyyy/m/d", 더할값, date())

=datediff("yyyy/m/d", 시작일, date())

W- 요일

 

=String(Int(Count(*)/10),"★") & "(" & Count(*) & "개)"

(int: 정수로 반환)

 

=iif([page] mod 2=1,

엑셀이랑 다르게, mod를 나누기 기호처럼!

 

Dxx("결과계산필드명","테이블명/쿼리명(폼레코드원본참조)","조건")

Dcount("*","테이블명","소매점명=txt소매점명")

Dsum("대여금액","대여내역입력","도서명='귀천'" (조건에 문자일 때, ' ' 아니면 컨트롤명이라고 생각함)

* 폼이 가동되면 커서가 "txt컴활" 컨트롤에 나타나있도록 속성 시트를 설정하여라

-> 그 컨트롤의 속성 시트로 들어가 탭 인덱스 : 0 (첫번째 탭)

* 폼의 크기 조정불가 : 폼 - 형식 - 테두리스타일 - 가늘게

 

▶ 조건부서식

식이 [상호] like "*산업*"

날짜가 짝수날인 경우 Day([필드명]) mod 2 =0

 

☞ 보고서

* 빈 공간에 ★이 반복하여 표시되도록 설정하시오. -> 컨트롤 속성의 형식에서 @*★

* 그룹별로 일련번호가 표시되도록 설정하시오 -> 컨트롤 원본 : =1, 누적합계 : 그룹

* 출발일자의 연도와 월에 대해서 오름차순 : 정렬 -식 - year(출발일자) & month(출발일자)

 

☞ 쿼리

▶ 필드 조건

* 합계가 제일 많은 거래처를 표시

(select max(합계) from 쿼리명)

>=(select avg(합계) from 쿼리명)

 

- 입력값 이상만 생성

>=[횟수를 입력하세요]

 

Like "*" & [문자일부를 입력하세요] & "*"

 

- not in(select 필드명 from 테이블)

- is null : 다측의 관계설정 되어있는 필드

- ~수 : 다측의 아무필드 - 요약 - 개수

 

* 삭제쿼리

 

'사원정보'테이블의 부서코드중에서 '부서'테이블의 부서코드에 없는 행을 삭제! 실행시, 사원정보 테이블만 있어야함. (사원정보 테이블의 행을 삭제)

 

* Switch(조건1, 값1, 조건2, 값2....)

ex) =Switch([합계]>=500000,"상",[합계]>=200000,"중",[합계]<200000,"하")

 

조건 나머지는 빈칸으로 (요약 : 식)

 

새 테이블로 생성 : 쿼리만들고, 테이블 만들기

 

* DateSerial(년,월,일) : 숫자를 날짜형식으로 바꿈

DateSerial(Left([발령예정일],4),Mid([발령예정일],5,2),Mid([발령예정일],7,2))

 

▶ 이벤트프로시저

* 주로 사용되는 SQL 구문

Docmd.runSQL " "

추가 : insert into 테이블명(필드1, 필드2) values(값1, 값2)

재계산: update 테이블명 set 변경내용 where 조건

검색 : select 필드명 from 테이블명 where 조건

삭제 : delete 필드명 from 테이블명 where 조건

 

ex)테이블1에 기록되지 않은 항목 테이블2에서 삭제

Docmd.RunSQL "Delete * from 테이블2 where 관계설정된필드 not in(select 관계설정된필드 from 테이블1)

 

- Me.RecordSource = "Select * from 강좌 W here 강좌명 ='" &txt찾기 & "'" - Me.RecordsetClone.FindFirst "제품코드 = '" &txt조회 &"'"  Me.Bookmark = Me.RecordsetClone.Bookmark - Domcd.Openreport "동아리회원명부 ",acviewpreview, ,"동아리명 ='" &txt동아리명 & "'" → acviewpreview 는 인쇄미리보기, acnormal 은 폼 보기

- Docmd.Openreport "구매금액보고서 ",acviewpreview, ,"year(구매일자)=" & year(cmb날짜조회) & "month(구매일자 )=" & month(cmb 날짜조회 )- Docmd.Openreport "수강신청현황 ",acviewpreview,,"성명 ='" &combo 학번 .column(1) &"'"- Docmd.Close acform, "수강신청현황폼 "- Docmd.Openreport "월간소비내역",acviewpreview,,"금액 >=100000 and 금액 <=200000"- Docmd.Openform "제품명폼",acNormal,,"제품번호 ='" &cmb 제품번호 & "'"- Docmd.Openform "학과별정보 ",acNormal,,"학과 ='" &txt 학과 & "'",acFormReadOnly- Docmd.Gotocontrol "t 성명 "

 

caption : 레이블 / 명령단추

ex) lab지역합계.Caption = cmb지역코드&"합계"

ex) txt합계 = DSum("매출수량", "매출관리", "지역코드=cmb지역코드")

 

컨트롤명.tapstop = false : 탭 정지, 아니오

컨트롤명.ControlTipText = 필드명 or "문자"

ex) 고객이름에 마우스 포인터 위치하면, 직업이 나오도록!

고객이름.controlTipText = 직업

 

컨트롤명.setfocus cf) 엑셀 : select ex) Range("A1").Select

컨드롤명.fontsize = 11

컨트롤명.FontBold = True

 

* 만약 콤보상자에서의 두번째 열 내용을 표시되도록 설정하려면

이벤트 프로시저를 작성할 때 ,

cmb학과명. column(1) <- 두번째 열

 

Me.recordsetclone.findfirst "조건" ("=" 안씀, "고객이름 = '" & txt이름 & "'")

Me.bookmark = Me.recordsetclone.bookmark

me.Recalc (폼의 모든 계산 컨트롤 갱신)

 

문자열일때 : Me.filter = "필드명 ='" & 컨트롤명 & "'"

숫자일 때 : Me.filter = "필드명 =" & 컨트롤명

날짜일 때 : Me.filter = "필드명 = #" & 컨트롤명 & "#"

문자열 일부 : Me.filter = "필드명 like '*" & 컨트롤명 & "*'" (필드명 like '*컨트롤명*')

 

Docmd.Requery : 데이터 갱신

Docmd.openQuery "쿼리명"

Docmd.openform "폼이름",acNormal,,조건

Docmd.openreport "보고서이름,acviewpreview,,조건

 

* GotoControl -> 프로시저의 SetFocus랑 똑같음

 

▶ MSG BOX - msgbox 교사명 & "님의 총 급여는" &format(dsum("급여 ","원아종합 ","교사명 ='" &txt 교사명 & "'"), "#,###")- a = msgbox("내용 ",vbYesNo,For m.name &"폼의 원본보기 ")  If a = vbYes then,  docmd.opentable "학생 "  end if - a = msgbox("삭제 ?",vbYesNo,"삭제여부판단")  If a = vbYes then  docmd.runsql "delete * from 구매 where 사번 =txt 사번"   me.requery   end if - a = inputbox("날짜를 입력하시오","날짜조회")  docmd.openreport "구매금액 보고서 ",acviewpreview,"구매일자 =#" &aa &"#"  docmd.close acform, "사원정보"

 

▶ 매크로

WHERE 조건문

하위폼: 필터명 = [폼이름].form![컨트롤명]

외부폼: 필터명 = [forms]![폼이름]![컨트롤명]

필터명 = "문자"

Month([판매일자])=Month(DateValue([Forms]![판매현황]![txt판매일자]))

- applyfilter 의 where : 과목명 like "*" &[forms]![폼이름 ]![cmb 과목명] &"*"  openreport 의 where : 반번호 =[forms]![반정보 ]![cmb 조회]

 

* 사용한 필터를 모두 제거하여, 모든 레코드를 표시

- ShowAllRecords

 

* Applyfilter : 필터이름 무시, where 조건문 적기!

 

출력파일명.확장자

 

1. 데이터를 추가할 수 있도록 레코드선택기의 위치를 이동 시킬 것

Gotorecord

2. 업무현황조회 폼에서 기능이 수행되도록

Openform

 

* 코드작성기

(Gotorecord : 레코드 선택기 위치 이동

Docmd.gotorecord 개체타입, "개체명", 이동할 위치

ex) docmd.gotorecord acDataForm,"폼이름",acNewRec

 

▶ 엑세스 함수 - Count(*) : 개수 - Sum(필드명) : 합계 - Avg(필드명) : 평균 - Max(필드명) :  최대값 - Min(필드명) : 최소값 - IIF(조건, 참값, 거짓값) : 조건에 맞으면 참값, 아니면 거짓값 - INSTR(필드명, "찾는 문자열") : 찾는 문자열이 몇 번째인지 표시 - STRING(반복횟수, "문자") : 문자를 지정한 수만큼 반복 - FORMAT(값, "형식") : 값을 형식처럼 표시 - INT(값) : 값을 정수로 표시 - LEN(문자열) : 문자열 내 문자수 반환 - LENB(텍스트) : 텍스트를 저장하는데 필요한 바이트 수 - Lcase(텍스트) : 소문자로 바꿈 - Ucase(텍스트) : 대문자로 바꿈 - IN("A", "B", "C") : A or B or C , 지정한 값 중 하나를 쓰라 - TRIM(텍스트) : 텍스트의 좌우공백 제거 - LTRIM(텍스트) : 텍스트의 왼쪽공백제거 - RLTRIM(텍스트) : 텍스트의 오른쪽공백제거 - Strcomp(텍스트1, 텍스트2) : 텍스트1과 텍스트2를 비교하여 값 표시 - Replaced(텍스트1, 텍스트2, 텍스트3) : 텍스트1에서 텍스트2를 찾아 텍스트3으로 바꿈 - Space(인수) : 인수만큼 공백 표시 - DATEADD(형식, 더할값, 입력일) : 입력일을 형식에 맞게 더함 - DATEDIFF(형식, 시작일, 나중일) : 두 날짜 사이의 차이값 - DATEPART(형식, 날짜) : 형식에 맞는 날짜를 반환 - DATESERIAL(년, 월, 일) : 지정한 년, 월, 일 값을 날짜형으로 반환  → yyyy : 년, m : 월, d : 일, w(aaaa) : 요일, ampm : 오전오후, h : 시, n : 분, s : 초 - DATEVALUE(문자열) : 문자열을 날자로 바꿈 - DSUM("구할필드", "테이블/쿼리", "조건") : 테이블/쿼리에서 조건에 맞는 필드를 표시 - DAVG("구할필드", "테이블/쿼리", "조건") : 테이블/쿼리에서 조건에 맞는 필드를 표시 - DLOOKUP("찾아야할필드", "테이블/쿼리", "조건") : 테이블/쿼리에서 조건에 맞는 필드를 표시 - Switch(조건1, 값1, 조건2, 값2, ...) : 조건에 따른 값 표시 - Choose(인수, 첫번째, 두번째, ...) : 인수가 1이면 첫번째를 2이면 두번째를 표시