8일차.hwp
8일차
배열수식 1-144
배열수식은 조건의 내용이 들어있는 데이터 전체를 가지고 수식을 입력하게됨.
p. 143
조건이 1개일 때 남자가 몇 명?
1. sum 함수 사용=(조건1)*1
=(성별전체범위=“남”)*1
=SUM((조건1)*1)=SUM((성별전체범위=“남”)*1) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*1)}
2. SUM, IF 함수 이용=IF(성별전체범위=“남”,1)=SUM(IF(성별전체범위=“남”,1)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",1))}
조건이 1개일 때의 합계구하기
성별이 남인 사람들의 금액 합계구하기
1. SUM 함수 이용 =SUM((조건1)*값을 구하는곳 범위)=(성별전체범위=“남”)*값을 구하는곳 범위=SUM((성별전체범위=“남”)*값을 구하는곳 범위) CTRL + SHIFT + ENTER{=SUM((B2:B6="남")*C2:C6)}
2. SUM, IF 함수 이용=IF(성별전체범위=“남”,금액 전체범위)=SUM(IF(성별전체범위=“남”,금액 전체범위)) CTRL + SHIFT + ENTER{=SUM(IF(B2:B6="남",C2:C6))}
P.147
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-149쪽
1번
1) 조건을 먼저 입력 =(조건1)*(조건2) => =(과목명전체범위=I4셀의과목명)*(학과전체범위=J3셀의학과명)
=($I4=$A$3:$A$23)*(J$3=$C$3:$C$23)
2) 이제 맨 앞에 SUM( ) 입력
=SUM(($I4=$A$3:$A$23)*(J$3=$C$3:$C$23))
3) CTRL + SHIFT + ENTER
2번
1) =IF($I12=$C$3:$C$23,D$3:D$23)
2) =AVERAGE(IF($I12=$C$3:$C$23,D$3:D$23))
3) CTRL + SHIFT + ENTER
1-151
1번
1) =IF(A3=$A$8:$A$23,$D$8:$D$23)
2) =MAX(IF(A3=$A$8:$A$23,$D$8:$D$23))
3) CTRL + SHIFT + ENTER
2번
1) 분류코드가 ‘SA’인 자료에 대해서 업무코드별 종사자수의 합계를 표시 업무코드는 업무구분의 마지막 3문자로 계산
=(E3=RIGHT($B$8:$B$23,3))*("SA"=$A$8:$A$23)*$C$8:$C$23
2)
=SUM((E3=RIGHT($B$8:$B$23,3))*("SA"=$A$8:$A$23)*$C$8:$C$23)
3) CTRL + SHIFT + ENTER
SUM 함수를 이용하여 분류코드의 인원수를 구하세요 배열수식임
=SUM((조건1)*1)
152쪽
사용자정의 함수(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 차이점
---
변수선언 : 첫자는 문자로 시작되며 빈공백불가
---------------제어문
엑셀 =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
------------------반복 제어문
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, "인사")
반환값=InputBox(메시지,[대화상자 제목],[기본값],[가로위치],[세로위치],[도움말파일,도움말번호])
예) jj=InputBox("직위를 입력하세요","직위")