Transcript 첨부화일 : 컴활 엑셀 프로시저 (888634 Bytes)

컴퓨터활용능력1급
엑셀 프로시저
비호전산교육학원
053-853-5554
http://www.bihocom.com
1. CurrentRegion ( 파일 : CurrentRegion.xls )
 문제 이해
 폼에 입력되어 있는 데이터를 항상 엑셀 표
의 마지막 위치에 입력하고자 한다.
데이터 입력 순서
 데이터 양의 변화에 따라 입력되는 위치가
변경이 된다.
 즉, 데이터를 입력하여야 하는 행 번호를 구
하는 작업이다.
코드 : [기준셀].CurrentRegion.Rows.Count
 기준셀에서 주변을 참조하여 데이터가
있다면 범위에 포함이 되고, 없다면 범위
에 포함을 하지 않는다.
 A16셀 “결제기준”을 기준으로 오른쪽으
로는 데이터가 입력되어 있지만 주변에
는 데이터가 없다. 따라서 데이터가 있는
범위를 지정하여 보면 오른쪽 범위가 되
고 해당 범위의 행의 개수를 반환하므로
결과는 1이다.
A
B
C
결제종류
카드/은행명
금액
D
15
16
17
한번의 데이터를 입력하였을 경우
 만약 아래에 데이터가 입력하게 되면 범
위는 변하게 된다. 이 때 데이터의 범위를
지정하면 다음과 같고 행의 개수는 2개이
므로 결과는 2를 반환한다.
A
B
C
16
결제종류
카드/은행명
금액
17
신용카드
비씨카드
50,000
15
18
D
코드 : [기준셀].CurrentRegion.Rows.Count + α
 속성의 결과는 표 내부에서 데이터를 입력할
행 번호를 나타낸다.
 엑셀에서 실제 표가 있는 위치까지의 오차는
반영을 하지 않았다. 오차의 행 수는 속성 결
과에 더하여 계산한다.
속성결과
1
입력할 행번호
17
예) 17-1=16행이 실제 엑셀에서 표의 위치
16행이 표의 위치
 표의 위치는 데이터 입력에 따라 변하지 않으
므로 상수를 더해 주면 된다.
입력행 = [A16].CurrentRegion.Rows.Count + 16 → 표의 위치 행 지정
↓
입력행번호
↓
표에서 입력 행 지정
입력행은 1과 16을 더해 17이라는 데이터가 입력된다.
즉, 변수값을 이용하여 입력 데이터 행을 지정할 수 있다.
코드 : Cells( 행번호 , 열번호 ) = 컨트롤이름
 프로시저에서는 오른쪽에 있는 항목을 왼쪽으로 대입하는 개념이다.
 입력될 행은 데이터의 양에 따라 변한다. 따라서 앞에서 계산된 입력행이 행번호로 사용되
면 데이터 양에 따른 행변화를 구현할 수 있게 된다.
 그리고 항목에 따른 열번호는 항상 같으므로 해당 열을 상수로 입력하면 된다.
예) 금액 데이터를 엑셀에 입력하고자 한다. 컨트롤의 이름은 ‘txt금액’ 이다.
→ 금액은 엑셀에서 항상 C열에 입력이 된다. 따라서 열번호는 항상 3이다.
행은 데이터 양에 따라 값이 변하므로 앞에서 계산한 입력행을 입력한다.
Cells( 입력행 , 3 ) = txt금액
문) <결제화면> 폼의 ‘결제(cmd결제)’ 버튼을 클릭하면 폼에 입력된 결제종류, 선택(cmb선택), 금액
(txt금액)의 값이 [표1]에 입력되도록 작성하시오.
▶단, 결제종류는 선택된 결제종류의 caption 속성을 이용
▶시트에 입력될 때 숫자로 인식되도록 하기 위하여 금액은 ‘txt금액’에 1을 곱함
※ 데이터를 추가하면 항상 마지막 데이터 다음에 입력되어야 함
<코드>
Private Sub cmd결제_Click()
입력행 = [A16].CurrentRegion.Rows.Count + 16
If Opt은행 = True Then
Cells(입력행 , 1) = Opt은행.Caption
Else
Cells(입력행 , 1) = Opt신용.Caption
End If
 ‘Opt은행’ 옵션단추 컨트롤은 선택되었을 때 True 값
을 가진다.
 조건으로 ‘Opt은행 = True’라고 지정하면 선택하면
Opt은행이 선택되었을 때 참이라는 결과를 나타내고,
만약 거짓이라면 선택하지 않은 것으로 다른 하나의
옵션단추 Opt신용이 선택된 결과를 나타낸다.
Cells(입력행 , 2) = cmb선택
 컨트롤의 속성을 값으로 반환하기 위해서는 이름에
마침표(.)와 함께 속성의 이름을 입력하면 가져올 수
있다.
Cells(입력행 , 3) = txt금액 * 1
 문제에 제시한 것처럼 컨트롤에 1을 곱하여 준다.
End Sub
2. ListIndex ( 파일 : ListIndex.xls )
 문제 이해
 폼의 콤보상자나 목록상자에서 선택된 데이터에 따라 엑셀 데이터를 조회하고자 한다.
 상자에서 선택 데이터가 변경되면 엑셀에서 조회하는 대상의 행 번호도 변경이 된다.
 즉, 선택되는 데이터에 따라 엑셀에서 검색될 행 번호를 구하는 작업이다.
코드 : 컨트롤이름.ListIndex
선택 데이터에 따라
반환값이 달라진다.
선택값
반환값
마우스
0
모니터
1
키보드
2
프린터
3
CD-ROM
4
CPU
5
FDD
6
HDD
7
 ListIndex 속성은 콤보상자나 목록상자의 목록에서 데이터를 선택하였을 경우 해당 데이터의
순번을 데이터로 반환하여 준다.
 ListIndex 속성의 값은 0부터 시작한다. 즉, 첫번째 데이터는 0을, 두번째 데이터는 1을 데이터
로 반환한다.
 그림을 참조하면 폼의 콤보상자에서 ‘키보드’를 선택하였을 때 해당 데이터는 목록에서 3번째
에 위치하고 있으므로 결과값은 2를 반환한다.
코드 : 컨트롤이름.ListIndex + α
엑셀에서 표의 위치
ListIndex 결과가 0부터 시작
 속성의 결과는 엑셀 표에서 조회할 행 번호를 나타낸다. 하지만 실제 표에서 조회할 데이터가
위치한 행의 번호와는 차이가 발생한다.
 속성 결과가 2이고 실제 엑셀에서 조회할 해당 데이터가 위치하고 있는 행은 6이라면 4만큼의
오차가 발생한다. 이는 CurrentRegion 속성과 마찬가지로 엑셀에서의 표의 위치 오차와
ListIndex 속성이 0부터 반환하는 특징에서 발생하는 오차이다.
조회행 = cmb제품명.ListIndex + 4
코드 : 컨트롤이름 = Cells( 행번호 , 열번호 )
 이번에는 CurrentRegion과는 달리 엑셀의 데이터를 컨트롤에 표시하는 구조이다.
 선택된 데이터에 따라 조회 행 번호가 변경되므로 행번호는 앞에서 계산한 조회행 변수가
사용이 되고, 열번호는 해당 항목에 따른 열은 항상 일치하므로 상수로 입력이 되어 진다.
예) ‘키보드’를 조회하였을 때 생산단가 데이터 표시하기. 컨트롤의 이름은 ‘txt생산단가’ 이다.
→ 생산단가는 엑셀 표에서 항상 B열에 있다. 그러므로 열번호는 2이다.
조회하는 제품에 행은 선택된 데이터의 순번에 따라 변하므로 조회행 변수가
사용이 된다.
txt생산단가 = Cells( 조회행 , 2 )
문) <제품보유현황폼>에서 제품명(cmb제품명)을 선택한 후 <조회(cmb조회)>버튼을 클릭하면 워
크시트에 입력된 해당 제품의 데이터를 폼에 표시하는 프로시저를 작성하시오. (ListIndex 사용)
<코드>
조회행 = cmb제품명.ListIndex + 4
txt생산단가 = Cells(조회행, 2)
 ListIndex 속성을 사용하여 조회행을 계산한다.
txt생산량 = Cells(조회행, 3)
 각 컨트롤에 입력할 데이터를 조회행을 행번호로 사
용하여 엑셀의 데이터를 가져와 입력한다.
txt불량품수 = Cells(조회행, 4)
txt재고량 = Cells(조회행, 5)
txt총보유량 = Cells(조회행, 6)
 열번호는 조회를 할 때마다 항상 같은 열에 있으므로
조회 항목이 있는 열의 번호를 상수로 입력한다.
3. 복합 문제 ( 파일 : 복합.xls )
 문제 이해
 이번에는 CurrentRegion과 ListIndex 속성을 둘 다 사
용하여 해결하는 문제이다.
 폼의 데이터를 엑셀에 입력하는 형태가 아니라 셀에
서 데이터를 조회하여 다른 셀로 입력하는 형태이다.
 즉, 목록상자에서 선택하여 조회할 행 번호와 등록을
클릭했을 때 엑셀에 입력할 행 번호를 따로 만들어야
하는 구조이다.
셀에서 다른 셀로 바로 데이터 입력
코드 : 컨트롤이름.ListIndex + α
 목록상자에서 상품을 선택하였을 때 첫 데이터부터
값을 0부터 순서대로 반환한다.
 만약 ‘수박’을 선택하였다면 위에서 4번째 있으므로
반환되는 값은 3이다.
 엑셀의 오른쪽 표을 참조하여 ‘수박’이 8번째 행에
있는 것을 알 수 있다.
 속성값은 3이고 조회행은 8이므로 5만큼의 오차가
발생한다.
 따라서, 조회행의 코드는 다음과 같다. 이 때, 목록상
자의 컨트롤의 이름은 ‘Lst품목’이다.
조회행 = Lst품목.ListIndex + 5
코드 : [기준셀].CurrentRegion.Rows.Count + α
 기준셀은 입력 표에서 첫번째 셀인 [B5] 셀이다.
 이미 2개의 데이터가 입력되어 있으므로 [B5]셀부터 범위를 지정하
여 보면 [B5:F7]까지 된다. 따라서 속성의 결과는 행의 개수인 3이 반
환된다.
 데이터는 8번째 행에 입력이 되어야 하므로 5만큼의 오차가 있다.
입력행 = [B5].CurrentRegion.Rows.Count + 5
데이터 입력 행
코드 : Cells( 행번호 , 열번호 ) = Cells( 행번호 , 열번호 )
 엑셀의 데이터를 조회하여 다른 셀에 데이터를 입력하는 구조이다.
 오른쪽에는 조회행을 사용한 셀의 주소가, 왼쪽에는 데이터를 입력받을 셀의 주소가 입력되
어야 한다.
 예를 들면, 상품코드 데이터는 H열에 위치한다. H는 8열이므로 조회행의 열번호는 8이다.
 상품코드가 입력되어야 하는 열은 B열이다. B는 2열이므로 입력행의 열번호는 2이다.
Cells( 입력행 , 2 ) = Cells( 조회행 , 8 )
표의 데이터를 다른 표로 입력
입력 표
조회 표
문) <매출입력> 폼의 등록(cmd등록) 버튼을 클릭하면 상품코드, 구분, 상품명, 수량(spn수량), 금액
을 계산하여 [표1]에 입력되도록 작성하시오.
▶ 금액은 단가와 수량의 곱으로 계산하시오.
<코드>
조회행 = Lst품목.ListIndex + 5
입력행 = [B5].CurrentRegion.Rows.Count + 5
Cells(입력행 , 2) = Cells(조회행 , 8)
Cells(입력행 , 3) = Cells(조회행 , 9)
Cells(입력행 , 4) = Cells(조회행 , 10)
Cells(입력행 , 5) = spn수량.Value
 ListIndex 를 사용하여 조회행을 계산하고,
CurrentRegion 으로 입력행을 따로 계산한다.
 조회행을 오른쪽에, 입력행을 왼쪽에 적용하
여 모든 항목에 대해 셀 입력 구조를 작성한다.
 판매수량에는 셀의 데이터를 참조할 수 없다.
따라서, ‘spn수량’ 컨트롤의 스핀버튼을 이용
하여 변경된 컨트롤 Value 속성값을 입력한다.
 금액은 단가와 수량의 곱으로 계산한다.
Cells(입력행 , 6) = Cells(조회행 , 11) * spn수량.Value
 ‘spn수량’의 스핀버튼은 위, 아래 버튼을 클릭하면 컨트롤
의 Value 속성의 값이 증가, 감소하는 특징이 있다. 따라서,
스핀버튼의 Value 속성의 값을 이용하여 수량과 같은 데
이터를 입력할 수 있다.