배열수식

 
회사에서 SQL서버를 운용하시는 분은 접속해서 Query문으로 데이터를 쉽게 가져와서
가공이 가능하지만 이 팁 들은 순수하게 엑셀을 이용한 자료관리를 예제로 설명합니다.

저번 팁에 동적 이름 정의를 이용하여 데이터 영역을 확장하는 방법을 적어 놓았는데요.
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=265976

위 팁과 ADO(ActiveX Data Objects)를 이용하여 기존의 엑셀 화일 등을 열지 않고
바로 참조해서 데이터를 가져와서 챠트를 그리고 이 챠트의 참조 영역을 변화시켜서
챠트를 동적으로 관리하는 법을 순차적으로 구현한 것입니다. 이것을 잘 응용하시면
좋은 관리도구를 만드실 수 있을 것입니다.

우선 개발도구 > Visual Basic을 클릭하시고 도구 > 참조 메뉴에서
Microsoft ActiveX Data Objects 2.x Library를 선택합니다.
그리고 삽입에서 module을 선택하고 아래 코드들을 넣어줍니다.

우선 엑셀과 연결하는 방법과 가져올 필드(칼럼명)를 선택합니다.
예는 2014.xls화일의 RESULTS시트의 규격이 A0001의 로트와 자료1을 가져오는 경우입니다.
가져온 자료는 Data시트의 A2 셀부터 뿌려줍니다.?

<p>
Option Explicit

Sub ExtData()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

‘ 연결 문자열 : Excel 2003
sConnString = “Provider=Microsoft.Jet.OLEDB.4.0;excel 8.0;DATABASE=C:\2014.xls;”
‘ 연결 문자열 : Excel 2007
‘sConnString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myExcel2007file.xlsx;Extended
Properties=”Excel 12.0;HDR=YES”;”

‘ 새로운 연결과 레코드셋 설정
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

‘ 연결
conn.Open sConnString

Sheets(“Data”).Select

Range(“A2”, Range(“C2”).End(xlDown)).ClearContents

Set rs = conn.Execute(“select 로트, 자료1 from [RESULTS$] where 규격=’A0001’ “)

‘ 데이터 체크
If Not rs.EOF Then
‘ 데이터 복사
Sheets(“Data”).Range(“A2”).CopyFromRecordset rs
‘ 레코드셋 종료
rs.Close

Else
MsgBox “Error: No records returned.”, vbCritical

End If

‘ 연결 끊기와 메모리 비움
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub
</p>

한 로트에 대해 여러 자료가 있으므로 오직 하나의 로트만 가져와 Data시트의 D2부터 뿌려 줍니다.
VBA를 사용하지 않고 아래의 팁과 같이 여러 방법을 이용해 로트를 바로 가져올 수 있습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=267566

여기서는 ADO를 이용하기로 했으므로? 그냥 Query distinct문을 이용해서 직접 가져옵니다.

<p>
Sub ExtLot()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

‘ 연결 문자열 : Excel 2003
sConnString = “Provider=Microsoft.Jet.OLEDB.4.0;excel 8.0;DATABASE=C:\2014.xls;”
‘ 연결 문자열 : Excel 2007
‘sConnString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myExcel2007file.xlsx;Extended
Properties=”Excel 12.0;HDR=YES”;”

‘ 새로운 연결과 레코드셋 설정
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

‘ 연결
conn.Open sConnString

Sheets(“Data”).Select

Range(“A2”, Range(“C2”).End(xlDown)).ClearContents

Set rs = conn.Execute(“select distinct 로트 from [RESULTS$] where 규격=’A0001’ “)

‘ 데이터 체크
If Not rs.EOF Then
‘ 데이터 복사
Sheets(“Data”).Range(“A2”).CopyFromRecordset rs
‘ 레코드셋 종료
rs.Close

Else
MsgBox “Error: No records returned.”, vbCritical

End If

‘ 연결 끊기와 메모리 비움
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub
</p>

여기서 엑셀의 배열 수식을 이용해서 가져온 자료중에서 제일 큰 순서대로 4개를 가져오고
그 평균을 구해서 동적 챠트의 x bar -R 관리도에 응용해 Cp, Cpk값을 구해보기로 합니다.
아래 수식은 데이터 영역에서 로트영역을 확인해서 제일 큰 숫자를 가져오는 배열수식입니다.
2를 3으로 제일 마지막의 1을 2로 바꾸면 두번째로 큰 숫자를 가져옵니다. 배열 수식 입력법은
그냥 Enter가 아니라 Ctrl + Shift + Enter입니다. 대괄호는 자동적으로 들어갑니다.?

{=IF(ISNUMBER(LARGE(IF($D2=LotArea,DataArea),2)),LARGE(IF($D2=LotArea,DataArea),2),LARGE(IF
($D2=LotArea,DataArea),1))}

그리고 가져온 자료를 차트의 데이터 영역에 뿌려줍니다.

<p>
Sub Statics()

Dim sht As Worksheet

‘ 기존 데이터 삭제
Sheets(“Chart”).Activate
Range(“B2”, Range(“C2”).End(xlDown)).ClearContents

Application.ScreenUpdating = False

Set sht = Sheets(“Data”)

sht.Activate

‘ 데이터 복사
Range(“D2”, Range(“E2”).End(xlDown)).Select
Selection.Copy

Sheets(“Chart”).Activate

‘ 데이트 붙여넣기
Range(“B2”).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.ScreenUpdating = True

End Sub
</p>

그리고 동적 이름 정의와 다른 양식 컨트롤과 셀의 연결 방법과 더 구체적인 응용 방법은
첨부된 화일을 열어 보시면 쉽게 이해하실 수 있을 것으로 보입니다. 글로써 설명하기에는
제가 글 실력이 너무 없어서 첨부 화일로 대신합니다. ㅠㅠ

첨부 화일 : 20140427-ADO, 동적이름정의, 배열수식을 이용, 동적 차트 만들기