5일차.hwp
5일차
----데이터베이스 함수(엑세스에서 1~2문제 출제) 1-89쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
p.134 정보함수
ISBLANK(값) : 공백 셀이면 TRUE
ISERROR(값) : 에러값이면 TRUE
ISERR(값) : #N/A 오류를 제외한 오류의 값일 경우 TRUE
ISEVEN(값) : 숫자가 짝수이면 TRUE
ISODD(값) : 숫자가 홀수이면 TRUE
ISNUMBER(값) : 숫자값이면 TRUE
ISTEXT(값) : 텍스트값이면 TRUE
=IF(ISBLANK(C4)," ", VLOOKUP(C4,$I$4:$K$13,3,0))
=IF(ISERROR(E4*F4)," ",E4*F4)
1. 규격, 단가 : 품명이 공백일 경우에는 공백으로 처리하고 품명이 공백이 아니면, vlookup 함수를 이용하여 제품목록에서 찾아 표시하시오.
1) =VLOOKUP(연결값, 참조의전체범위, 열 번호, 연결방법)
연결값은 값을 구하는 곳의 줄 중에 한곳을 이용하여야 한다.
참조의 전체범위는 데이터 제목을 포함해도 되고 안해도 되는데 연결값부분의 내용이 들어있는 곳부터 블록지정을 해야된다.
=VLOOKUP(C4,$I$4:$K$13,2,0)
2) 만약에 C4셀이 공백이면...공백으로 표시하고 그렇지 않으면 VLOOKUP함수의 내용을 표시
규격 =IF(ISBLANK(C4),"",VLOOKUP(C4,$I$4:$K$13,2,0))
단가 =IF(ISBLANK(C4),"",VLOOKUP(C4,$I$4:$K$13,3,0))
2. 공급가액은 IF와 ISERROR 함수를 이용하여 수량*단가의 계산식에 오류가 발생하면 공백으로 처리하고, 오류가 없으면 수량*단가를 계산하여 표시하시오.
1) = 수량*단가를 한 후 채우기 핸들로 드래그해보면 오류가 나오는 것을 확인할 수 있다.
2) =IF(ISERROR(수량*단가),“”,수량*단가)처럼 하고 드래그하면 오류가 나오지 않음.
=IF(ISERROR(E4*F4),"",E4*F4)
배열수식 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.145
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-147쪽
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-149
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)
150쪽
사용자정의 함수(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("직위를 입력하세요","직위")