HU

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로끝내면 안됨. 전체 행을 표시해줄 것