|
6일차
※ 마이크로프로세서의 설계 방식에 따른 구분
구분 | CISC | RISC |
목적 | 1개의 명령어로 최대의 동작 | 시간의 최소화 |
명령어 수 | 많음 | 적음 |
주소지정 | 복잡 | 간단 |
레지스터 | 적음 | 많음 |
처리속도 | 느림 | 빠름 |
전력 소모 | 많음 | 적음 |
생산 가격 | 고가 | 저가 |
프로그래밍 | 간단 | 복잡함 |
용도 | 일반 PC | 서버, 워크스테이션 |
배열수식 1-143
배열수식은 조건의 내용이 들어있는 데이터 전체를 가지고 수식을 입력하게됨.
p. 1-144
조건이 1개일 때 남자가 몇 명?
sum 함수 사용
=(조건1)*1
=(성별전체범위=“남”)*1
=SUM((조건1)*1)
=SUM((성별전체범위=“남”)*1) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*1)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,1)
=SUM(IF(성별전체범위=“남”,1)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",1))}
조건이 1개일 때의 합계구하기
성별이 남인 사람들의 금액 합계구하기
SUM 함수 이용 =SUM((조건1)*값을 구하는곳 범위)
=(성별전체범위=“남”)*값을 구하는곳 범위
=SUM((성별전체범위=“남”)*값을 구하는곳 범위) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*C2:C6)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,금액 전체범위)
=SUM(IF(성별전체범위=“남”,금액 전체범위)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",C2:C6))}
P.1-146
1번
=average(if(조건식, 값을 구할 범위지정))
1) =IF(A4=$A$11:$A$33,$C$11:$C$33) 먼저 입력 후
2) =AVERAGE(IF(A4=$A$11:$A$33,$C$11:$C$33))
average 함수를 맨 앞에 넣어주면 괄호()를 빼먹는 일을 줄일 수 있어요
3) 수식을 다 입력한 후에 배열 수식은 반드시
ctrl + shift + enter를 눌러서 수식의 맨 앞과 뒤에 중괄호 ( { } )가 생기도록 해주세요
개수 구할 때
1) sum 과 if 함수 이용시
=SUM(IF(A4=$A$11:$A$33,1))
2) sum 함수만 이용시
조건1개
=SUM((조건1)*1)
조건2개
=SUM((조건1)*(조건2))
조건이 1개일 때 남자가 몇 명?
sum 함수 사용
=(조건1)*1
=(성별전체범위=“남”)*1
=SUM((조건1)*1)
=SUM((성별전체범위=“남”)*1) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*1)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,1)
=SUM(IF(성별전체범위=“남”,1)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",1))}
조건이 1개일 때의 합계구하기
성별이 남인 사람들의 금액 합계구하기
SUM 함수 이용 =SUM((조건1)*값을 구하는곳 범위)
=(성별전체범위=“남”)*값을 구하는곳 범위
=SUM((성별전체범위=“남”)*값을 구하는곳 범위) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*C2:C6)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,금액 전체범위)
=SUM(IF(성별전체범위=“남”,금액 전체범위)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",C2:C6))}
P.144
1번
=average(if(조건식, 값을 구할 범위지정))
1) =IF(A4=$A$11:$A$33,$C$11:$C$33) 먼저 입력 후
2) =AVERAGE(IF(A4=$A$11:$A$33,$C$11:$C$33))
average 함수를 맨 앞에 넣어주면 괄호()를 빼먹는 일을 줄일 수 있어요
3) 수식을 다 입력한 후에 배열 수식은 반드시
ctrl + shift + enter를 눌러서 수식의 맨 앞과 뒤에 중괄호 ( { } )가 생기도록 해주세요
개수 구할 때
1) sum 과 if 함수 이용시
=SUM(IF(A4=$A$11:$A$33,1))
2) sum 함수만 이용시
조건1개
=SUM((조건1)*1)
조건2개
=SUM((조건1)*(조건2))
p.1-148
1. 결제방법,사용내역, 금액을 이용
1) 결제방법, 사용내역이 일치해야함. 조건이 2개임
=(조건1)*(조건2) => (A29=결제방법전체)*(B28=사용내역전체)
TIP : 조건1번 조건2번의 A29셀은 혼합참조로 가능, B28셀은 혼합참조 또는 절대참조로 만들 것.
2) =IF((A29=결제방법전체)*(B28=사용내역전체),금액전체범위)
3) =SUM(IF((A29=결제방법전체)*(B28=사용내역전체),금액전체범위))
4) =TEXT(SUM(IF((A29=결제방법전체)*(B28=사용내역전체),금액전체범위)),“#,##0,”)
5) 결과 : =TEXT(SUM(IF(($B$3:$B$25=$A29)*($C$3:$C$25=B$28),$D$3:$D$25)),"#,##0,") CTRL+SHIFT+ENTER
2. 결제방법과 금액을 이용
1) =IF(A29=결제방법전체,금액전체내용)
2) =LARGE(IF(A29=결제방법전체,금액전체내용),1) => 첫 번째로 큰 값 가져오기
3) =LARGE(IF(A29=결제방법전체,금액전체내용),1)-SMALL(IF(A29=결제방법전체,금액전체내용),1)
4) 결과 : =LARGE(IF($B$3:$B$25=A29,$D$3:$D$25),1)-SMALL(IF($B$3:$B$25=A29,$D$3:$D$25),1)
CTRL + SHIFT+ ENTER
3. 지역과 무주택을 이용하여 비율을 계산
REPT, IF, AVERAGE 배열수식
1) =IF(F27=지역전체범위, 무주택)
2) =AVERAGE(IF(F27=지역전체범위, 무주택))
3) =REPT(“■”, AVERAGE(IF(F27=지역전체범위, 무주택)))
정답 : =REPT("■",AVERAGE(IF($G$3:$G$23=F27,$J$3:$J$23)))
4. 현재예치금을 이용하여 각 범위에 해당하는 비율을 계산
비율 : 각 범위의 인원수/전체 인원수 * 100
FREQUENCY 함수
(1) 결과를 표시해야되는 셀들을 블록부터 씌운다.
(2) 함수입력
(3) CTRL + SHIFT + ENTER
1) K27:K31셀 블럭씌움
2) 각 범위의 인원수 =FREQUENCY(데이터범위, 구간범위)
=FREQUENCY(I3:I23,J27:J31)
3) 정답 : =FREQUENCY(I3:I23,J27:J31)/COUNT(I3:I23)*100
5. 행사내용, 인원을 이용하여 행사내용과 인원에 따른 빈도수
FREQUENCY(데이터범위,구간범위) IF 함수 이용한 배열수식
1) T4:T8셀 블록씌움
2) 데이터범위 : =IF(행사내용전체범위=T$3,인원전체범위)
3) =FREQUENCY(IF(행사내용전체범위=T$3,인원전체범위),$S$4:$S$8)
4) CTRL + SHIFT + ENTER
5) 정답 : =FREQUENCY(IF($N$3:$N$31=T$3,$O$3:$O$31),$S$4:$S$8)
6) T4:T8셀이 블록지정된 상태에서 채우기 핸들로 U4:U8셀에 수식이 들어가도록 채워준다.
6. 행사일과 월별행사건수를 구한 후 해당 개수만큼 “♥”를 영역에 반복하여 표시
IF, SUM, MONTH, REPT함수를 이용한 배열수식
1) 월 구하기 : =MONTH(행사일전체범위)
2) =IF(MONTH(행사일전체범위)=R12, 1)
3) =SUM(IF(MONTH(행사일전체범위)=R12, 1))
4) =REPT(“♥”,SUM(IF(MONTH(행사일전체범위)=R12, 1))) CTRL + SHIFT+ ENTER
5) 정답 : =REPT("♥",SUM(IF(MONTH($P$3:$P$31)=R12,1)))
1-152
수량과 단가를 이용하여 지역별 수량과 단가의 평균을 계산
지역은 물품코드의 앞에 두 글자를 이용할 것, AVERAGE, IF, LEFT함수를 이용한 배열수식
1) =LEFT(물품코드전체범위, 2)
2) =IF(LEFT(물품코드전체범위, 2)=$B24,수량전체범위)
3) =AVERAGE(IF(LEFT(물품코드전체범위, 2)=$B24,수량전체범위)) CTRL + SHIFT + ENTER
4) 정답 : =AVERAGE(IF(LEFT($B$3:$B$20,2)=$B24,C$3:C$20))
2. 물품코드와 판매금액을 이용하여 물품코드별 판매금액의 합계를 100000으로 나눈 몫만큼 “★”을 반복하여 표시
QUOTIENT, SUM, REPT 함수를 이용한 배열 수식
1) =SUM((물품코드전체범위=A29)*판매금액전체범위) =SUM(($B$3:$B$20=A29)*$E$3:$E$20)
2) =QUOTIENT(SUM(($B$3:$B$20=A29)*$E$3:$E$20),100000)
3) =REPT("★",QUOTIENT(SUM(($B$3:$B$20=A29)*$E$3:$E$20),100000)) CTRL+SHIFT+ENTER
3. 결제방법과 할부기간을 이용하여 할부기간과 결제방법별 카드 사용빈도를 계산
카드 사용 빈도수만큼 “★” 표시
1) =IF((할부기간전체범위=$G23)*(결제방법전체범위=H$22),1)
2) =SUM(IF((할부기간전체범위=$G23)*(결제방법전체범위=H$22),1))
3) =REPT(“★”,SUM(IF((할부기간전체범위=$G23)*(결제방법전체범위=H$22),1)))
4) CTRL+SHIFT+ENTER
5) 정답 : =REPT("★",SUM(IF(($G23=$K$3:$K$19)*(H$22=$I$3:$I$19),1)))
4. 모델명, 매출액, 결제방법을 이용하여 모델번호와 결제방법별 매출액의 평균
모델번호는 모델명의 뒤 글자임. AVERAGE, IF, RIGHT 함수를 사용한 배열수식
1) =RIGHT(모델명, 2)
2) =IF((RIGHT(모델명, 2)=$G29)*(결제방법전체범위=H$28),매출액전체범위)
3) =AVERAGE(IF((RIGHT(모델명, 2)=$G29)*(결제방법전체범위=H$28),매출액전체범위))
4) CTRL + SHIFT + ENTER
5) 정답 : =AVERAGE(IF((RIGHT($G$3:$G$19,2)=$G29)*(H$28=$I$3:$I$19),$H$3:$H$19))
5. 가입나이와 나이를 이용하여 나이대별 가입자수를 표시
가입자수가 0보다 큰 경우 계산된 값을 두 자리 숫자로 뒤에 “명”을 추가하여 표시하고, 그 외는 “미가입”으로 표시
표시 예 : 0 → 미가입, 7 → 7명
IF, FREQUENCY, TEXT 함수를 이용한 배열 수식
1) 결과를 구하는 곳 블록 씌우기 S4:S10셀 블럭지정
2) =FREQUENCY(데이터범위, 구간범위) =FREQUENCY(가입나이전체범위, R4:R10)
3) =IF(FREQUENCY(가입나이전체범위, R4:R10)=0,“미가입”,TEXT(FREQUENCY(가입나이전체범위, R4:R10),“0명”))
4) CTRL + SHIFT + ENTER
5) 정답 : =IF(FREQUENCY(M3:M33,R4:R10)=0,"미가입",TEXT(FREQUENCY(M3:M33,R4:R10),"0명"))
6. 가입나이, 코드, 가입기간을 이용하여 코드별 나이별 평균 가입기간을 계산
단, 오류발생시 공백으로 표시
AVERAGE, IF, IFERROR 함수를 배열 수식으로 계산
1) =IF(($Q15=코드전체범위)*(가입나이전체범위=>=R$13)*(가입나이전체범위<R$14),가입기간전체범위)
2) =AVERAGE(IF(($Q15=코드전체범위)*(가입나이전체범위=>=R$13)*(가입나이전체범위<R$14),가입기간전체범위))
3) =IFERROR(AVERAGE(IF(($Q15=코드전체범위)*(가입나이전체범위=>=R$13)*(가입나이전체범위<R$14),가입기간전체범위)),“”)
4) CTRL + SHIFT + ENTER
5) 정답
=IFERROR(AVERAGE(IF(($Q15=$N$3:$N$33)*($M$3:$M$33>=R$13)*($M$3:$M$33<R$14),$O$3:$O$33)),"")
153쪽
사용자정의 함수(Function~ End Function 프로시저) (실기 1문제, 필기1문제 for~next)
1) 엑셀에서 vba(비쥬얼베이직 어플리케이션)를 이용한 엑셀혼합 함수
2) 엑셀 -> vba Editor : Alt + F11
(vba Editor -> 엑셀 : Alt + Q)
3) 개발도구 - 비쥬얼베이직 - 삽입 - 모듈
주의 : 사용자정의함수는 반드시 모듈에 작성
4) Public Function 사용자정의함수(변수1,변수2,..)
프로그램을 작성
End Function
※ sub ~ end sub와
function ~ end function 차이점
---
변수선언 : 첫자는 문자로 시작되며 빈공백불가
1-153쪽
Public Function fn적립금(매출액, 적립률)
If 매출액 >= 1000000 Then
fn적립금 = 매출액 * (적립률 + 0.02)
Else
fn적립금 = 매출액 * 적립률
End If
End Function
----------------------------------------------------------------------------
Public Function fn배송비(판매금액, 등급)
If 판매금액 >= 100000 Or 등급 = "정회원" Then
fn배송비 = 1000
ElseIf 판매금액 >= 50000 Or 등급 = "준회원" Then
fn배송비 = 3000
Else
fn배송비 = 4000
End If
End Function
-----------------------------------------------------------------------------
Public Function fn가입상태(가입기간, 미납기간)
If 미납기간 >= 가입기간 Then
fn가입상태 = "해지예상"
ElseIf 미납기간 = 0 Then
fn가입상태 = "정상"
ElseIf 미납기간 > 2 Then
fn가입상태 = "휴먼보험"
Else
fn가입상태 = 미납기간 & "개월 미납"
End If
End Function
-------------------------------------------------------------------------
Public Function fn할인금액(사용내역, 관계, 금액)
If 사용내역 = "일반의료비" And (관계 = "본인" Or 관계 = "처" Or 관계 = "자") Then
fn할인금액 = 금액 * 0.8
ElseIf 사용내역 = "일반의료비" And (관계 = "부" Or 관계 = "모") Then
fn할인금액 = 금액 * 0.5
Else
fn할인금액 = ""
End If
End Function
---------------제어문
엑셀 =if(조건식, 참, 거짓)
vba
1) 단순 if문
if 조건식 then
참의값
end if
---
if 평균>=60 then
fn판정="합격"
end if
--
if 점수1>=40 and 점수2>=40 and 평균>=60 then
fn판정="합격"
end if
2)IF 구문
IF 조건식 Then
참
Else
거짓
End if
---
if 평균>=60 then
fn판정=“합격”
else
fn판정=“불합격”
end if
3) 다중 if구문
IF 조건1 Then
값1
Elseif 조건2 Then
값2
Elseif 조건3 Then
값3
Elseif 조건4 Then
값4
Else
값5
End if
예) 평균 90이상 수, 80이상이면 우, 70이상이면 미, 60이상이면 양, 그외 가
if 평균>=90 then
fn판정="수"
elseif 평균>=80 then
fn판정="우"
elseif 평균>=70 then
fn판정="미"
elseif 평균>=60 then
fn판정="양"
else
fn판정="가"
end if
예)
If 사용량 >= 400 Then
fn에너지요금결과 = 사용량 * 200
ElseIf 사용량 >= 300 Then
fn에너지요금결과 = 사용량 * 150
ElseIf 사용량 >= 200 Then
fn에너지요금결과 = 사용량 * 100
ElseIf 사용량 >= 100 Then
fn에너지요금결과 = 사용량 * 50
Else
fn에너지요금결과 = 사용량 * 300
End If
--------------------------------------------
※ 조건식에서 and, or사용법
if 조건식1 and 조건식2 then
참의값
else
거짓의값
end if
---------------------------------------------
※ 다중if문 대신에 select case문을 사용할 수 있다.
2) Select Case 구문
Select Case 비교할 값
Case 경우1
명령문1
Case 경우2
명령문2
Case 경우3
명령문3
Case Else
명령문4
End Select
예)
Select Case 사용량
Case is >=400
fn에너지요금 = 사용량 * 200
Case is >=300
fn에너지요금 = 사용량 * 150
Case is >=200
fn에너지요금 = 사용량 * 100
Case is >=100
fn에너지요금 = 사용량 * 50
Case Else
fn에너지요금 = 사용량 * 300
End Select
Select Case 직위
Case “부장”
fn기본급 = 2000000
Case “과장”
fn기본급 = 1500000
Case “대리”
fn기본급 = 1000000
Case else
fn기본급 = 800000
End Select
------------------반복 제어문
1) For ~ Next 구문 : For문 안의 지정 횟수만큼 명령문을 반복 실행한다.
For 변수=시작값 to 종료값 Step 증감값
명령문
Next
예)
▶ 1부터 10까지의 합
For i = 1 To 10 Step 1
sum=sum + i
Next
▶ 1부터 10까지의 짝수의 합
For i = 0 To 10 Step 2
even=even + i
Next
▶ 1부터 10까지의 홀수의 합
For i = 1 To 10 Step 2
odd=odd + i
Next
2) For Each ~ Next 구문 : 개체 집합이나 배열에 대한 명령 실행 시 반복, 처리하는 명령임.
For Each 변수
명령문
Next
예)
i="컴퓨터활용능력"
For Each My In Range("A1:A3")
My.Value = i
Next
3) Do While ~ Loop 문 : 반복 전에 조건을 판단하므로 처음 조건식이 거짓인 경우 명령문은 한 번도 실행되지 않는다.
Do While 조건식
명령문
Loop
예)
i=1
Do While i<=50
sum=sum+i
i=i+1
Loop
4) Do ~ Loop While 문 : 반복 전에 조건을 판단하지 않으므로 일단 명령문을 수행하고 조건을 판단한다.
Do
명령문
Loop While 조건식
예)
i=50
Do
sum=sum+i
i=i-1
Loop While i>=1
5) Do Until ~ Loop 문 : 조건식이 거짓일 경우 수행되므로 조건이 참일 때 반복을 중지한다. 반복 전에 조건을 판단하므로 처음 조건식이 참인 경우 명령문은 한 번도 실행되지 않는다.
Do Until 조건식
명령문
Loop
예)
h=1
Do Until h>=50
sum = sum + h
h = h + 1
Loop
--------------------With ~ end With 구문
-하나의 개체에 여러 가지 메서드나 속성을 변경할 수 있다.
-With 구문을 사용하면 프로그램의 길이를 줄일 수 있는 장점이 있다.
With 개체명
개체에 공통으로 적용할 메서드나 속성
End With
예)
Selection.Font.Name="돋움"
Selection.Font.Size=20
Selection.Font.Bold=True
Selection.Font.Italic=True
위와같은 내용을 with 문을 이용해서 아래와 같이 묶어줌.
With Selection.Font
.Name="돋움"
.Size=20
.Bold=True
.Italic=True
End With
-------------------------
MsgBox(메시지, [단추 유형],[대화상자 제목],[도움말 파일],[도움말 번호]) : 대화상자에 주어진 메시지를 출력해주는 명령
예) i=MsgBox("안녕하세요.^^")
i=MsgBox("안녕하세요.^^", 4, "인사")
MsgBox Date & “ ” & Time
반환값=InputBox(메시지,[대화상자 제목],[기본값],[가로위치],[세로위치],[도움말파일,도움말번호])
예) jj=InputBox("직위를 입력하세요","직위")
※ 마이크로프로세서의 설계 방식에 따른 구분
구분 | CISC | RISC |
목적 | 1개의 명령어로 최대의 동작 | 시간의 최소화 |
명령어 수 | 많음 | 적음 |
주소지정 | 복잡 | 간단 |
레지스터 | 적음 | 많음 |
처리속도 | 느림 | 빠름 |
전력 소모 | 많음 | 적음 |
생산 가격 | 고가 | 저가 |
프로그래밍 | 간단 | 복잡함 |
용도 | 일반 PC | 서버, 워크스테이션 |
배열수식 1-143
배열수식은 조건의 내용이 들어있는 데이터 전체를 가지고 수식을 입력하게됨.
p. 1-144
조건이 1개일 때 남자가 몇 명?
sum 함수 사용
=(조건1)*1
=(성별전체범위=“남”)*1
=SUM((조건1)*1)
=SUM((성별전체범위=“남”)*1) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*1)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,1)
=SUM(IF(성별전체범위=“남”,1)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",1))}
조건이 1개일 때의 합계구하기
성별이 남인 사람들의 금액 합계구하기
SUM 함수 이용 =SUM((조건1)*값을 구하는곳 범위)
=(성별전체범위=“남”)*값을 구하는곳 범위
=SUM((성별전체범위=“남”)*값을 구하는곳 범위) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*C2:C6)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,금액 전체범위)
=SUM(IF(성별전체범위=“남”,금액 전체범위)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",C2:C6))}
P.1-146
1번
=average(if(조건식, 값을 구할 범위지정))
1) =IF(A4=$A$11:$A$33,$C$11:$C$33) 먼저 입력 후
2) =AVERAGE(IF(A4=$A$11:$A$33,$C$11:$C$33))
average 함수를 맨 앞에 넣어주면 괄호()를 빼먹는 일을 줄일 수 있어요
3) 수식을 다 입력한 후에 배열 수식은 반드시
ctrl + shift + enter를 눌러서 수식의 맨 앞과 뒤에 중괄호 ( { } )가 생기도록 해주세요
개수 구할 때
1) sum 과 if 함수 이용시
=SUM(IF(A4=$A$11:$A$33,1))
2) sum 함수만 이용시
조건1개
=SUM((조건1)*1)
조건2개
=SUM((조건1)*(조건2))
조건이 1개일 때 남자가 몇 명?
sum 함수 사용
=(조건1)*1
=(성별전체범위=“남”)*1
=SUM((조건1)*1)
=SUM((성별전체범위=“남”)*1) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*1)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,1)
=SUM(IF(성별전체범위=“남”,1)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",1))}
조건이 1개일 때의 합계구하기
성별이 남인 사람들의 금액 합계구하기
SUM 함수 이용 =SUM((조건1)*값을 구하는곳 범위)
=(성별전체범위=“남”)*값을 구하는곳 범위
=SUM((성별전체범위=“남”)*값을 구하는곳 범위) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*C2:C6)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,금액 전체범위)
=SUM(IF(성별전체범위=“남”,금액 전체범위)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",C2:C6))}
P.144
1번
=average(if(조건식, 값을 구할 범위지정))
1) =IF(A4=$A$11:$A$33,$C$11:$C$33) 먼저 입력 후
2) =AVERAGE(IF(A4=$A$11:$A$33,$C$11:$C$33))
average 함수를 맨 앞에 넣어주면 괄호()를 빼먹는 일을 줄일 수 있어요
3) 수식을 다 입력한 후에 배열 수식은 반드시
ctrl + shift + enter를 눌러서 수식의 맨 앞과 뒤에 중괄호 ( { } )가 생기도록 해주세요
개수 구할 때
1) sum 과 if 함수 이용시
=SUM(IF(A4=$A$11:$A$33,1))
2) sum 함수만 이용시
조건1개
=SUM((조건1)*1)
조건2개
=SUM((조건1)*(조건2))
p.1-148
1. 결제방법,사용내역, 금액을 이용
1) 결제방법, 사용내역이 일치해야함. 조건이 2개임
=(조건1)*(조건2) => (A29=결제방법전체)*(B28=사용내역전체)
TIP : 조건1번 조건2번의 A29셀은 혼합참조로 가능, B28셀은 혼합참조 또는 절대참조로 만들 것.
2) =IF((A29=결제방법전체)*(B28=사용내역전체),금액전체범위)
3) =SUM(IF((A29=결제방법전체)*(B28=사용내역전체),금액전체범위))
4) =TEXT(SUM(IF((A29=결제방법전체)*(B28=사용내역전체),금액전체범위)),“#,##0,”)
5) 결과 : =TEXT(SUM(IF(($B$3:$B$25=$A29)*($C$3:$C$25=B$28),$D$3:$D$25)),"#,##0,") CTRL+SHIFT+ENTER
2. 결제방법과 금액을 이용
1) =IF(A29=결제방법전체,금액전체내용)
2) =LARGE(IF(A29=결제방법전체,금액전체내용),1) => 첫 번째로 큰 값 가져오기
3) =LARGE(IF(A29=결제방법전체,금액전체내용),1)-SMALL(IF(A29=결제방법전체,금액전체내용),1)
4) 결과 : =LARGE(IF($B$3:$B$25=A29,$D$3:$D$25),1)-SMALL(IF($B$3:$B$25=A29,$D$3:$D$25),1)
CTRL + SHIFT+ ENTER
3. 지역과 무주택을 이용하여 비율을 계산
REPT, IF, AVERAGE 배열수식
1) =IF(F27=지역전체범위, 무주택)
2) =AVERAGE(IF(F27=지역전체범위, 무주택))
3) =REPT(“■”, AVERAGE(IF(F27=지역전체범위, 무주택)))
정답 : =REPT("■",AVERAGE(IF($G$3:$G$23=F27,$J$3:$J$23)))
4. 현재예치금을 이용하여 각 범위에 해당하는 비율을 계산
비율 : 각 범위의 인원수/전체 인원수 * 100
FREQUENCY 함수
(1) 결과를 표시해야되는 셀들을 블록부터 씌운다.
(2) 함수입력
(3) CTRL + SHIFT + ENTER
1) K27:K31셀 블럭씌움
2) 각 범위의 인원수 =FREQUENCY(데이터범위, 구간범위)
=FREQUENCY(I3:I23,J27:J31)
3) 정답 : =FREQUENCY(I3:I23,J27:J31)/COUNT(I3:I23)*100
5. 행사내용, 인원을 이용하여 행사내용과 인원에 따른 빈도수
FREQUENCY(데이터범위,구간범위) IF 함수 이용한 배열수식
1) T4:T8셀 블록씌움
2) 데이터범위 : =IF(행사내용전체범위=T$3,인원전체범위)
3) =FREQUENCY(IF(행사내용전체범위=T$3,인원전체범위),$S$4:$S$8)
4) CTRL + SHIFT + ENTER
5) 정답 : =FREQUENCY(IF($N$3:$N$31=T$3,$O$3:$O$31),$S$4:$S$8)
6) T4:T8셀이 블록지정된 상태에서 채우기 핸들로 U4:U8셀에 수식이 들어가도록 채워준다.
6. 행사일과 월별행사건수를 구한 후 해당 개수만큼 “♥”를 영역에 반복하여 표시
IF, SUM, MONTH, REPT함수를 이용한 배열수식
1) 월 구하기 : =MONTH(행사일전체범위)
2) =IF(MONTH(행사일전체범위)=R12, 1)
3) =SUM(IF(MONTH(행사일전체범위)=R12, 1))
4) =REPT(“♥”,SUM(IF(MONTH(행사일전체범위)=R12, 1))) CTRL + SHIFT+ ENTER
5) 정답 : =REPT("♥",SUM(IF(MONTH($P$3:$P$31)=R12,1)))
1-152
수량과 단가를 이용하여 지역별 수량과 단가의 평균을 계산
지역은 물품코드의 앞에 두 글자를 이용할 것, AVERAGE, IF, LEFT함수를 이용한 배열수식
1) =LEFT(물품코드전체범위, 2)
2) =IF(LEFT(물품코드전체범위, 2)=$B24,수량전체범위)
3) =AVERAGE(IF(LEFT(물품코드전체범위, 2)=$B24,수량전체범위)) CTRL + SHIFT + ENTER
4) 정답 : =AVERAGE(IF(LEFT($B$3:$B$20,2)=$B24,C$3:C$20))
2. 물품코드와 판매금액을 이용하여 물품코드별 판매금액의 합계를 100000으로 나눈 몫만큼 “★”을 반복하여 표시
QUOTIENT, SUM, REPT 함수를 이용한 배열 수식
1) =SUM((물품코드전체범위=A29)*판매금액전체범위) =SUM(($B$3:$B$20=A29)*$E$3:$E$20)
2) =QUOTIENT(SUM(($B$3:$B$20=A29)*$E$3:$E$20),100000)
3) =REPT("★",QUOTIENT(SUM(($B$3:$B$20=A29)*$E$3:$E$20),100000)) CTRL+SHIFT+ENTER
3. 결제방법과 할부기간을 이용하여 할부기간과 결제방법별 카드 사용빈도를 계산
카드 사용 빈도수만큼 “★” 표시
1) =IF((할부기간전체범위=$G23)*(결제방법전체범위=H$22),1)
2) =SUM(IF((할부기간전체범위=$G23)*(결제방법전체범위=H$22),1))
3) =REPT(“★”,SUM(IF((할부기간전체범위=$G23)*(결제방법전체범위=H$22),1)))
4) CTRL+SHIFT+ENTER
5) 정답 : =REPT("★",SUM(IF(($G23=$K$3:$K$19)*(H$22=$I$3:$I$19),1)))
4. 모델명, 매출액, 결제방법을 이용하여 모델번호와 결제방법별 매출액의 평균
모델번호는 모델명의 뒤 글자임. AVERAGE, IF, RIGHT 함수를 사용한 배열수식
1) =RIGHT(모델명, 2)
2) =IF((RIGHT(모델명, 2)=$G29)*(결제방법전체범위=H$28),매출액전체범위)
3) =AVERAGE(IF((RIGHT(모델명, 2)=$G29)*(결제방법전체범위=H$28),매출액전체범위))
4) CTRL + SHIFT + ENTER
5) 정답 : =AVERAGE(IF((RIGHT($G$3:$G$19,2)=$G29)*(H$28=$I$3:$I$19),$H$3:$H$19))
5. 가입나이와 나이를 이용하여 나이대별 가입자수를 표시
가입자수가 0보다 큰 경우 계산된 값을 두 자리 숫자로 뒤에 “명”을 추가하여 표시하고, 그 외는 “미가입”으로 표시
표시 예 : 0 → 미가입, 7 → 7명
IF, FREQUENCY, TEXT 함수를 이용한 배열 수식
1) 결과를 구하는 곳 블록 씌우기 S4:S10셀 블럭지정
2) =FREQUENCY(데이터범위, 구간범위) =FREQUENCY(가입나이전체범위, R4:R10)
3) =IF(FREQUENCY(가입나이전체범위, R4:R10)=0,“미가입”,TEXT(FREQUENCY(가입나이전체범위, R4:R10),“0명”))
4) CTRL + SHIFT + ENTER
5) 정답 : =IF(FREQUENCY(M3:M33,R4:R10)=0,"미가입",TEXT(FREQUENCY(M3:M33,R4:R10),"0명"))
6. 가입나이, 코드, 가입기간을 이용하여 코드별 나이별 평균 가입기간을 계산
단, 오류발생시 공백으로 표시
AVERAGE, IF, IFERROR 함수를 배열 수식으로 계산
1) =IF(($Q15=코드전체범위)*(가입나이전체범위=>=R$13)*(가입나이전체범위<R$14),가입기간전체범위)
2) =AVERAGE(IF(($Q15=코드전체범위)*(가입나이전체범위=>=R$13)*(가입나이전체범위<R$14),가입기간전체범위))
3) =IFERROR(AVERAGE(IF(($Q15=코드전체범위)*(가입나이전체범위=>=R$13)*(가입나이전체범위<R$14),가입기간전체범위)),“”)
4) CTRL + SHIFT + ENTER
5) 정답
=IFERROR(AVERAGE(IF(($Q15=$N$3:$N$33)*($M$3:$M$33>=R$13)*($M$3:$M$33<R$14),$O$3:$O$33)),"")
153쪽
사용자정의 함수(Function~ End Function 프로시저) (실기 1문제, 필기1문제 for~next)
1) 엑셀에서 vba(비쥬얼베이직 어플리케이션)를 이용한 엑셀혼합 함수
2) 엑셀 -> vba Editor : Alt + F11
(vba Editor -> 엑셀 : Alt + Q)
3) 개발도구 - 비쥬얼베이직 - 삽입 - 모듈
주의 : 사용자정의함수는 반드시 모듈에 작성
4) Public Function 사용자정의함수(변수1,변수2,..)
프로그램을 작성
End Function
※ sub ~ end sub와
function ~ end function 차이점
---
변수선언 : 첫자는 문자로 시작되며 빈공백불가
1-153쪽
Public Function fn적립금(매출액, 적립률)
If 매출액 >= 1000000 Then
fn적립금 = 매출액 * (적립률 + 0.02)
Else
fn적립금 = 매출액 * 적립률
End If
End Function
----------------------------------------------------------------------------
Public Function fn배송비(판매금액, 등급)
If 판매금액 >= 100000 Or 등급 = "정회원" Then
fn배송비 = 1000
ElseIf 판매금액 >= 50000 Or 등급 = "준회원" Then
fn배송비 = 3000
Else
fn배송비 = 4000
End If
End Function
-----------------------------------------------------------------------------
Public Function fn가입상태(가입기간, 미납기간)
If 미납기간 >= 가입기간 Then
fn가입상태 = "해지예상"
ElseIf 미납기간 = 0 Then
fn가입상태 = "정상"
ElseIf 미납기간 > 2 Then
fn가입상태 = "휴먼보험"
Else
fn가입상태 = 미납기간 & "개월 미납"
End If
End Function
-------------------------------------------------------------------------
Public Function fn할인금액(사용내역, 관계, 금액)
If 사용내역 = "일반의료비" And (관계 = "본인" Or 관계 = "처" Or 관계 = "자") Then
fn할인금액 = 금액 * 0.8
ElseIf 사용내역 = "일반의료비" And (관계 = "부" Or 관계 = "모") Then
fn할인금액 = 금액 * 0.5
Else
fn할인금액 = ""
End If
End Function
---------------제어문
엑셀 =if(조건식, 참, 거짓)
vba
1) 단순 if문
if 조건식 then
참의값
end if
---
if 평균>=60 then
fn판정="합격"
end if
--
if 점수1>=40 and 점수2>=40 and 평균>=60 then
fn판정="합격"
end if
2)IF 구문
IF 조건식 Then
참
Else
거짓
End if
---
if 평균>=60 then
fn판정=“합격”
else
fn판정=“불합격”
end if
3) 다중 if구문
IF 조건1 Then
값1
Elseif 조건2 Then
값2
Elseif 조건3 Then
값3
Elseif 조건4 Then
값4
Else
값5
End if
예) 평균 90이상 수, 80이상이면 우, 70이상이면 미, 60이상이면 양, 그외 가
if 평균>=90 then
fn판정="수"
elseif 평균>=80 then
fn판정="우"
elseif 평균>=70 then
fn판정="미"
elseif 평균>=60 then
fn판정="양"
else
fn판정="가"
end if
예)
If 사용량 >= 400 Then
fn에너지요금결과 = 사용량 * 200
ElseIf 사용량 >= 300 Then
fn에너지요금결과 = 사용량 * 150
ElseIf 사용량 >= 200 Then
fn에너지요금결과 = 사용량 * 100
ElseIf 사용량 >= 100 Then
fn에너지요금결과 = 사용량 * 50
Else
fn에너지요금결과 = 사용량 * 300
End If
--------------------------------------------
※ 조건식에서 and, or사용법
if 조건식1 and 조건식2 then
참의값
else
거짓의값
end if
---------------------------------------------
※ 다중if문 대신에 select case문을 사용할 수 있다.
2) Select Case 구문
Select Case 비교할 값
Case 경우1
명령문1
Case 경우2
명령문2
Case 경우3
명령문3
Case Else
명령문4
End Select
예)
Select Case 사용량
Case is >=400
fn에너지요금 = 사용량 * 200
Case is >=300
fn에너지요금 = 사용량 * 150
Case is >=200
fn에너지요금 = 사용량 * 100
Case is >=100
fn에너지요금 = 사용량 * 50
Case Else
fn에너지요금 = 사용량 * 300
End Select
Select Case 직위
Case “부장”
fn기본급 = 2000000
Case “과장”
fn기본급 = 1500000
Case “대리”
fn기본급 = 1000000
Case else
fn기본급 = 800000
End Select
------------------반복 제어문
1) For ~ Next 구문 : For문 안의 지정 횟수만큼 명령문을 반복 실행한다.
For 변수=시작값 to 종료값 Step 증감값
명령문
Next
예)
▶ 1부터 10까지의 합
For i = 1 To 10 Step 1
sum=sum + i
Next
▶ 1부터 10까지의 짝수의 합
For i = 0 To 10 Step 2
even=even + i
Next
▶ 1부터 10까지의 홀수의 합
For i = 1 To 10 Step 2
odd=odd + i
Next
2) For Each ~ Next 구문 : 개체 집합이나 배열에 대한 명령 실행 시 반복, 처리하는 명령임.
For Each 변수
명령문
Next
예)
i="컴퓨터활용능력"
For Each My In Range("A1:A3")
My.Value = i
Next
3) Do While ~ Loop 문 : 반복 전에 조건을 판단하므로 처음 조건식이 거짓인 경우 명령문은 한 번도 실행되지 않는다.
Do While 조건식
명령문
Loop
예)
i=1
Do While i<=50
sum=sum+i
i=i+1
Loop
4) Do ~ Loop While 문 : 반복 전에 조건을 판단하지 않으므로 일단 명령문을 수행하고 조건을 판단한다.
Do
명령문
Loop While 조건식
예)
i=50
Do
sum=sum+i
i=i-1
Loop While i>=1
5) Do Until ~ Loop 문 : 조건식이 거짓일 경우 수행되므로 조건이 참일 때 반복을 중지한다. 반복 전에 조건을 판단하므로 처음 조건식이 참인 경우 명령문은 한 번도 실행되지 않는다.
Do Until 조건식
명령문
Loop
예)
h=1
Do Until h>=50
sum = sum + h
h = h + 1
Loop
--------------------With ~ end With 구문
-하나의 개체에 여러 가지 메서드나 속성을 변경할 수 있다.
-With 구문을 사용하면 프로그램의 길이를 줄일 수 있는 장점이 있다.
With 개체명
개체에 공통으로 적용할 메서드나 속성
End With
예)
Selection.Font.Name="돋움"
Selection.Font.Size=20
Selection.Font.Bold=True
Selection.Font.Italic=True
위와같은 내용을 with 문을 이용해서 아래와 같이 묶어줌.
With Selection.Font
.Name="돋움"
.Size=20
.Bold=True
.Italic=True
End With
-------------------------
MsgBox(메시지, [단추 유형],[대화상자 제목],[도움말 파일],[도움말 번호]) : 대화상자에 주어진 메시지를 출력해주는 명령
예) i=MsgBox("안녕하세요.^^")
i=MsgBox("안녕하세요.^^", 4, "인사")
MsgBox Date & “ ” & Time
반환값=InputBox(메시지,[대화상자 제목],[기본값],[가로위치],[세로위치],[도움말파일,도움말번호])
예) jj=InputBox("직위를 입력하세요","직위")
|