HU

<프로시저_VBA>

(1) [개발도구]-[컨트롤]-[디자인모드] 에서 컨트롤을 더블클릭하면 VBA 편집모드가 나타난다.

(2) 폼 나타나게 하기 : 폼이름.show

(3) 폼이 초기화되면 지출내역(cmb지출내역) 목록에 ‘i2:i8’ 영역의 값이 표시되도록 프로시저 작성

VBA 편집모드의 프로젝트 브라우저에서 폼을 더블클릭 후

폼의 위에서 더블클릭하면

VBA 편집할 수 있는 창이 나타난다. 여기서 초기화했을 때의 이벤트를 만들 것이므로 오른쪽 위의 목록 중 Initialize를 고른다. 그러면 위와 같은 함수가 하마 생기고, 이 안에서 작업한 코드는 폼이 초기화(initialize) 되었을 때 발생한다.

Private Sub UserForm_Initialize()

    cmb지출내역.RowSource = "i2:i8"

End Sub

콤보상자명.rowsource=”범위” : rowsource는 콤보상자 목록을 나타내는 속성이다.

(4) 또는, 폼이 초기화되면 지출내역 목록에 개발자가 지정한 목록이 입력되는 프로지서 작성

*콤보박스에서의 엑셀->컨트롤

Private Sub UserForm_Initialize()

    cmb지출내역.AddItem "관리비"

    cmb지출내역.AddItem "보험료"

    cmb지출내역.AddItem "적금"

    cmb지출내역.AddItem "교통비"

    cmb지출내역.AddItem "식대"

    cmb지출내역.AddItem "기타"

End Sub

콤보상자명.additem “값” : 콤보상자에 들어갈 목록을 직접 작성할 때 사용한다.

(5) frm지출내역 폼의 입력(cmd입력) 단추를 클릭하면 폼에 입력된 지출일자(txt일자), 지출내역(cmb지출내역), 상세내역(txt상세내역), 지출금액(txt금액)을 표시하도록 프로시저 작성.

*컨트롤->엑셀 데이터

Private Sub cmd입력_Click()

    r = [a3].Row + [a3].currentRegion.Rows.Count

    Cells(r, 1) = txt일자

    Cells(r, 2) = cmb지출내역

    Cells(r, 3) = TXT상세내역

    Cells(r, 4) = txt금액

    txt일자 = ""

    cmb지출내역 = ""

    TXT상세내역 = ""

    txt금액 = ""

End Sub

*[a3].row : A3의 행번호를 받고, 반환되는 값은 3이다.

*[a3].currentregion : a3에 연결된 (표) 영역을 지정. A3:c9까지라면 [a4].currentregion도 [c7].currentregion도 같은 영역을 나타낸다.

*[a3].currentregion.rows.count : a3과 연결된 영역의 행 수를 반환한다. A3:c9라면 9-3+1=7을 반환할 것이다. 따라서 폼에서 입력한 데이터는 a10:c10에 입력된다.

*[a3].CurrentRegion.Rows만 쓸 것우 컴파일 오류.

*Cells(r,1)은 r행 1열의 셀을 가리킨다.

(6) 폼 닫기

Private Sub cmd종료_Click()

    Unload Me

End Sub

(7) 메시지박스(msgbox) 보이기

Private Sub UserForm_Click()

    If cmb종류 = "BC카드" Or cmb종류 = "삼성카드" Then

        MsgBox "3개월 무이자 할부 행사 기간입니다." & Time

    End If

End Sub

*Time은 현재 시간을 보여준다.

*MsgBox cmb종류&"는 3개월 무이자 할부 행사 기간입니다." & Time

만약 메시지박스에 변수를 넣으려면 큰 따옴표 밖에 &연산자와 함께 넣는다.

(8) 지출일자가 변경되면 위크시트의 지출내역과 지출액이 폼에 표시되도록 프로시저 작성

*엑셀데이터->컨트롤

Private Sub Cmb지출일자_Change()

    r = [a3].Row + Cmb지출일자.ListIndex + 1

    txt지출내역 = Cells(r, 2)

    txt지출액 = Cells(r, 3)

End Sub

Listindex : 목록에서 선택한 값의 행 번호로 0부터 시작한다. (그래서 +1해줌.)

(9) 체크박스 핸들링

If chk인증 = True Then

        Cells(r, 6) = "친환경인증"

    Else

        Cells(r, 6) = ""

    End If

초기화는 아래와 같다.

    chk인증 = False

(10) 콤보박스 인덱스를 사용한 초기화

cmb제조방법.ListIndex = 0

20. 목표값찾기

(1) 이걸 지정하려면 수식같은걸로 연결되어있어야 한다.

(2)

확인누르면 데이터가 바뀌고, 취소누르면 목표값을 확인만 하고 데이터는 바뀌지 않는다.

21. 시나리오 관리자

(1)

[추가]하면 바로 시나리오를 추가할 수 있다.

시나리오의 셀이름 바꾸기

왼쪽위의 해당 셀이름이 나타나는 칸에 바꾸고자하는 이름을 넣고 엔터.

22. 차트

(1) 보조 세로축 : 해당 그래프를 선택하여 [데이터 게열 지정]-[보조축] 선택

(2) 레이블 : 선택하여 하나만 레이블 나오게 하기 / 레이블 위치

(3) 축 제목

(4) 범례

(5) 차트 영역 서식

(6) 그림 영역 서식

(7) 축 옵션 : 주 단위_고정 / 최소 최대값

(8) 차트제목을 셀에 연결하려면 텍스트 편집이 아닌 위치를 변경할 수 있도록 차트제목을 클릭하여 수식입력줄에 이퀄과 함께 연결할 셀을 넣는다.

1. 데이터 표 : 전체 데이터 중 일부분의 데이터를 변화시켜 수식의 결과가 어떻게 변하는지 보여줌.

(1) 연결시켜야 함 : =을 이용

(2) [가상분석] > [데이터표]

2. 데이터 통합 : 하나 이상의 작업 영역의 데이터를 저장해서 하나로 통합하여 ‘요약’한다.

(1) “통합기능을 이용하여” 라고 문제에 나옴.

(2)

* 범위주의 : 고객번호는 지정하지 않고, 첫행, 왼쪽열은 계산이 아닌 기준으로 사용하도록 하기위해 체크한다.

1. 피벗테이블

(1) 2007버전 필드삽입 : 피벗테이블 도구 > 옵션 > 도구 > 수식(2007)

2010버전 필드삽입 : 피벗테이블 도구 > 분석 > 계산 > 필드, 항목 및 집합

(2) 보고서 섯기 설정 : (맨위에 아래방향 화살표 눌러서) 빠른 실행 도구 모음 사용자 지정 > 기타 명령 > 사용자 지정 > [많이 사용하는 명령]을 [모든 명령]으로 > [자동서식] 추가

(3) 맨위에 필터가 들어가있을 때 피벗 테이블 위치 정할 때 그거까지 세면 안됨

(4) 행방향비율(2007) / 상위 행 합계 비율(2010) : 행 방향의 비율을 나타냄

2. 부분합

(1) 먼저 어느 기준으로 정렬되어 있어야한다.

(2)

3. 매크로

(1) [보기]-[매크로]-[매크로 기록]-기록시작됨 또는 도형오른클릭 [매크로지정](2급)

[개발도구]-[코드]-[매크로](1급)

(2007버전) 오피스단추 > 엑셀 옵션 > 기본설정 > 엑셀에서 가장 많이 사용하는 옵션 > [리본 메뉴에 개발도구 탭 표시] 체크 > [개발도구] > [컨트롤] > [삽입]

(2010버전) 엑셀 옵션 > 리본 사용자 지정 > 개발도구 체크

(2) 매크로 지정할 때 데이터표나 데이터 통합시, 출력될 표의 범위잡는거 주의

그리고 매크로 기록 중지하기 전에 표에 블록 잡힌거 풀고할 것!!!

4. 자동 필터

<사용자 정의 함수>

(1) alt+f11 -> [삽입]-[모듈]

(2) if문

Public function

fn단가(구분)

if 구분 = “성인” then

    fn단가=5000

elseif

else

end if *주의 끝내줘야함

(3) select case문

Public function

fn단가(구분)

select case 변수값

    case =”A”

        fn단가=50000

    case else

        fn단가<=0

end select

* 값이 숫자일 때 50이상 – case is >=50

30이상 50이하 – case 30 to 50

<배열함수의 여러 형식>

(1) ctrl+shift+enter

(2) 두 개 이상의 조건을 AND 표기 시 배열 수식 {=함수명((배열조건식1)*(배열조건식2))} ? {=SUM(IF((조건식1)*(조건식2),합계구할범위))}

  *if안에 합계구할 범위가 들어간다.

(3) 두 개 이상의 조건을 OR 표기 시 배열 수식 {=함수명((배열조건식1)+(배열조건식2))} ? {=SUM(IF((조건식1)+(조건식2),합계구할범위))}

(4) 조건식이 하나인 경우 괄호 표시 안해도 무방하다. ={함수명(배열조건식,~)} ?

{=SUM(IF(조건식, 합계구할범위))}

and가 *, or가 +

1. 개수

(5) Sum 함수 이용한 배열 수식

=SUM((조건1)*(조건2))

(6) Sum 함수 , IF 함수 이용한 배열 수식

=SUM(IF((조건1)*(조건2),1))

(7) Count함수, IF함수 이용한 배열 수식

= COUNT(IF((조건1) * (조건2),1))

(8) 조건이 1개일 때 배열 수식

= SUM(IF(조건,1))

= SUM((조건) * 1)

= COUNT(IF(조건,1))

2. 합계

(9) 조건이 1개일 때 배열 수식

=SUM((조건)*합계를 구하는 범위)

=SUM(IF(조건,합계를 구하는 범위))

(10) 조건이 2개일 때 배열 수식

= SUM((조건1)*(조건2)* 합계를 구하는 범위)

= SUM(IF((조건1)*(조건2), 합계를 구하는 범위))

3.평균

(11) 조건이 1개일 때 배열 수식

=AVERAGE(IF(조건,평균을 구하는 범위))

(12) 조건이 2개일 때 배열 수식

=AVERAGE(IF((조건1)*(조건2), 평균을 구하는 범위))

4. MAX

(13) 조건이 1개일 때 배열 수식

=MAX((조건) * 최대값을 구할 범위)

=MAX(IF(조건,최대값을 구하는 범위))

(14) 조건이 2개일 때 배열 수식

=MAX((조건1)*(조건2)*최대값을 구하는 범위)

5. MIN

6. MEDIAN

Q. 다원명별 2010년도의 최대 판매량

{=LARGE(IF(($J$2:$J$21=$A25)*($F$2:$F$21=2010),$K$2:$K$21),1)}

Q. 제조방법별 가격의 평균

{=TRUNC(AVERAGE(IF(($D$3:$D$21=$D25),$H$3:$H$21)),0)}

Q. 분류 제품코드, 가격을 이용하여 분류별 최고가 제품의 제품코드를 표시하시오.

{=INDEX($A$4:$A$26,MATCH(MAX(($B$4:$B$26=$D30)*$D$4:$D$26),$D$4:$D$26,0))}

*인강에서는 match의 두번째 파라미터에도 ($B$4:$B$26=$D30)* $D$4:$D$26라고 썼는데 걍 범위만 해도 나옴.

*index의 첫번째파라미터에 A열만이 아닌 A~L열까지 포함해서 세번째 파라미터인 열넘버에 1을 포함했다.

Q. 업종별 관할사업소별 전월사용금액의 합계

{=SUM(($C$3:$C$22=B$25)*($B$3:$B$22=$A26)*$G$3:$G$22)}

Q. 검침일별 사용량이 가장 많은 고객의 고객번호

{=INDEX($A$3:$A$22,MATCH(MAX(($E$3:$E$22=$A33)*$D$3:$D$22),$D$3:$D$22,0))}

Q. 판매수량의 하위 4번째까지의 평균을 계산하시오.

{=AVERAGE(IF(SMALL($H$11:$H$36,4)>=$H$11:$H$36,$H$11:$H$36))}

* $H11로끝내면 안됨. 전체 행을 표시해줄 것

1. 찾기참조함수

vlookup(찾을 데이터, 기준표table_array, 보여줄 값의 열번호, boolean) - 세로. boolean이 true면 유사한 값, false면 정확히 일치..

hlookup

index(제목없는 table_array, 행, 열) = 해당 값

match(찾을 값, 제목없는 lookup_array, num) – num : 1은 오름, 0은 똑같은 값, -1은 내림차순.

*lookup-array는 길게 한 줄

choose(1,"일","월","화","수","목","금","토","일") = 일

2. 데이터베이스 함수

dsum(제목포함 table_array, 더할 열의 index, 제목포함 찾을 값)

  =DSUM(B3:F9,3,C13:C14)

daverage(db, index, value)

dcount - 숫자 셈

dcounta - 데이터 셈

damx

dmin

dproduct

3. 재무함수 - 파라미터는 모두 월롤 변환하여 계산

FV 함수(투자의 미래가치 구하는 함수) =FV(이자, 기간, 금액, 현재가치, 납입시점)

fv(연이율(%)/12,기간(년)*12, -매달저축액(지출이므로 마이너스)) - 해당 기간이 지났을 떄 얼마가 저축되는지. 납입시점은 0이나 생략일 경우 말일, 1일일 경우 1을 입력

PMT 함수(정기적인 상환금액 구하는 함수) =PMT(이자, 기간, 현재가치,미래가치,납입시점)

pmt(이율/12, 대출기간(월), +금액(플러스)) - 결과가 마이너스. 매달 얼마씩 납입해야하는지.

PV 함수(현재 가치 구하는 함수) =PV(이자,기간, 금액,미래가치,납입시점)

pv(이율/12, 기간(월), -매달납부액) -

1. 통계함수

AVERAGEIF(조건적용범위,조건,평균구할범위)

AVERAGEIFS(평균범위, 조건1범위, 조건1, 조건2범위, 조건2,….)

count - 숫자 셈

counta - 빈칸 뺴고 다 셈

countblank - 빈칸 셈

countif(조건범위, 조건)

countifs(조건범위1, 조건1, 조건범위2, 조건2, ...)

max / min / large / small

rank(대상, 범위) - 0이 내림, 1이 올림.

Median(범위) - 중간값

2. 삼각함수

SUM

sumif(조건범위, 조건, 합계범위)

SUMIFS(합계범위, 조건 범위1,”조건1”,조건범위2,”조건2”…)

product

sumproduct

round(숫자, 자릿수) - 자릿수 3은 소수 넷쨰자리에서 반올림, -1은 첫째자리에서 반올림

rounddown - 자릿수 3은 넷째자리에서 버림. -1은 첫째자리에서 버림.

roundup

int - 정수로 표기(소수x)

abs - 절댓값

mod(숫자,나누는 수) - 나머지 계산

3.논리값함수

and

or

if

not

iferror(5/0,"에러발생")

4. 텍스트함수

left

right

mid("string", 3, 1) = r

concatenate(st1, st2, st3, ...)

len("string") = 6

lower - 전부 소문자로

upper - 대문자

proper - 맨 앞글자만 대문자로

trim - 공백제거

rept("d",2) = dd

text("2013-05-05","yyyymmmdd") = 2013May05

text("2013-05-05","mmm.dd.yy") = May.05.13

substitute("KOREe","e","A") = KOREA

find(찾을거, 문자열) - find("3","123") = 3

5. 날짜함수

year("2007-05-27") = 2007

month = 5

day = 27

date(13,05,27) = 1913-05-27

date(2013,05,27) = 2013-05-27  -> 표시형식변경에서 일반으로 하면 일련번호가 나온다.

datedif(입사일, 퇴사일+1,"Y") = 3년 ->"년"은

=DATEDIF(C39,D39,"y")&"년"&DATEDIF(C39,D39,"ym")&"개월"&DATEDIF(C39,D39+1,"md")&"일"

today() - 인수가 없다. 결과는 2016-11-30

now() - 인수가 없다. 결과는 2016-11-30 21:53

days360(입사일, 퇴사일) = 1137 일

workday("2015-04-01",6) - 토,일 제외하고 5일 지난 날짜의 일련번호

eomonth("2015-04-01",1) - 1개월이 지난 5월의 마지막 날짜의 일련번호

edate("2015-04-01", 1) - 1개월이 지난 5월 1일의 일련번호

weekday("2015-04-01") - 날짜에 해당하는 용리을 숫자로 표시한다.

  -1또는 생략 : 일요일이 1

  -2 : 월요일이 1

  -3 : 월요일 0

1. 외부데이터 가져오기

데이터>기타원본>Microsoft Query>MS Access Database

2. 고급 필터

* 사용자정의 필터의 경우 이 제목은 복붙하면 안되고 임의로 지어줘야한다.

(1) =LEFT(A3,1)<>"M"

(2) =E3<AVERAGE(E3:E25)

(3) =OR(LEFT(A3,3)="1-2",AND(AVERAGE(D3:E3)>=100,AVERAGE(D3:E3)<200))

-> (1)나 (3)같은 식에서는 array로 선택하면 안된다.

3. 조건부 서식

드래그 - 제목 포함x -> 홈>조건부서식>새규칙

(1) 수식사용 - $표시 주의

=AND(OR(LEFT($A3,1)="H",LEFT($A3,1)="K"),$D3=2009)