Access DB를 만들어서 엑셀과 연동시키고 싶으신 분들을 위하여
이페이지를 진행한다
엑셀시트를 여러장 활용하거나 통합문서를 여러개 만들어서 VBA로 이렇게,저렇게
엮어서 구성을 할수는 있겠지만
Access라는 뻔뜻한 물건이 사용하기를 기다려주는데
엑셀만 갖고 모든 것을 처리하려고 하는 것은 좀 미련한 짓이다
그리고 Access를 활용하면 대량의 정보를 불편없이 VBA로 코딩하는 량을 많이 줄여 준다
예를 들어서 , 그림과 같이
동물병원에서 진료기록을 기록하는 DB를 구축한다고 치자
고객은 고양이도 갖고 있을수도 있고 강아지도 갖고 있을수 있고
또 여러마리의 애완동물을 갖고 있을수 있을 것이다
그래서 애완동물테이블이 하나 있고
고객테이블이 하나 있다
어떤고객이 어느날, 몇시에 어느 애완동물을 데리고 진료를 받으러 왔는지
기록을 하게 될것이고
이런 영업을 하게 되는 과정이 기록되는 것이 고객과 애완동물 테이블을 참조하는
진료테이블이 만들어 질수 있게 되는 것이다
그러면 정보의 관계가 입체화된다
각 애완동물별로 분석이 될수 있고
각 고객별로 분석이 될수도 있고
또 전체 진료기록에 대한 분석도 볼수 있고
이것이 [애완동물목록]+[고객목록]+[진료기록목록]으로 분산이 되어 관리가 될수 있다
이때 동물목록중에 A라는 강아지를 삭제한다고 치면
엑셀의 테이블이라고 치면 A라는 강아지가 사용자의 실수로 삭제되면 그냥 그것으로 끝이다
진료기록테이블상에 연결이 되었던 A라는 강아지의 정보는 행방불명
쓰레기 정보가 쌓이게 된다
정보의 일관성이 없어지게 된다
하지만 Access같은 정식 DB프로그래밍에서는 이런 것을
자동통제한다, 물론 설정을 해놓아야 하겠지..
그래서 Access를 비롯한
거의 모든 현대의 DB는 관계형DB(Relational DataBase System)라고 부르는 것이다
그런 각도로 세상을 보면
모든 것이 관계형 DB의 구축대상이 되는 정보들이다
도대체 엑셀범위상의 테이블과 DB상의 테이블은 어떤 성질이 있는지
가장 기본적인 것을 보기 위한 시뮤레이션을 해보도록 하자
아래의 코드를 실행하면
워크시트에 테이블이 만들어졌다
위의 동물병원의 진료진행테이블이 하나로 만들어졌다
Sub createXLFlatTable()
Dim shtX As Worksheet
Dim iRow As Integer
Dim arrPets As Variant
Dim oCol As New Collection
Dim varX As Variant
Dim sPhone As String
Dim iTreatType As Integer
On Error Resume Next
Const PET_Types As String = "Cat,Dog,Cat,Dog,Dog,Snake"
arrPets = Split(PET_Types, ",")
Set shtX = Worksheets.Add
With shtX
.Range("A1").Resize(, 12) = Array("일자", "시간", "애완동물명", "애완동물생일", "애완동물건강상태", "애완동물타입", "고객명", "고객주소1", "고객주소2", "고객전화번호", "진료내용", "진료비")
For iRow = 2 To 200
With .Range("A" & iRow).EntireRow
.Cells(1) = DateSerial(2016, Int(Rnd() * 12 + 1), Int(Rnd() * 30 + 1))
.Cells(2) = TimeSerial(9 + Int(Rnd() * 8) + 1, Application.WorksheetFunction.Floor(Int(Rnd() * 60) + 1, 5), 0)
.Cells(3) = arrPets(Int(Rnd() * 6)) & "_" & Int(Rnd() * 5) + 1
.Cells(4) = DateSerial(Year(.Cells(1)) - Int(Rnd() * 5 + 1), Int(Rnd() * 12) + 1, Int(Rnd() * 30) + 1)
.Cells(5) = Array("A", "B", "C", "D", "E")(Int(Rnd() * 4))
.Cells(6) = Split(.Cells(3), "_")(0)
.Cells(7) = .Cells(3) & "_주인"
.Cells(8) = .Cells(3) & "_주인 주소_1"
.Cells(9) = .Cells(3) & "_주인 주소_2"
sPhone = "010-" & Int(Rnd() * 1000) + 1000
oCol.Add .Cells(7).Value & "|" & sPhone, .Cells(7).Value & "|" & sPhone
If Err.Number = 0 Then
.Cells(10) = sPhone
Else
.Cells(10) = getPhone(.Cells(7).Value, oCol)
Err.Clear
End If
iTreatType = Int(Rnd() * 5) + 1
.Cells(11) = "진료타입_" & iTreatType
.Cells(12) = Choose(iTreatType, 50000, 80000, 100000, 150000, 180000)
End With
Next
.usedRange.Columns.AutoFit
End With
End Sub
Function getPhone(sVal As String, oCol As Collection)
Dim varX As Variant
For Each varX In oCol
If Split(varX, "|")(0) = sVal Then
getPhone = Split(varX, "|")(1)
Exit Function
End If
Next
getPhone = ""
End Function
위의 코드로 시트상에 만들어진 테이블을 보면 느끼시는 것이 없으신지..
아마도 엑셀테이블만 다루어 왔다면 아무런 감각도 없을 것이다
테이블이 이렇지, 뭐 별다른 것이 뭐있나??!!
뭔소리를 하는지..원!!!
이라고 하시면 그것이 발전을 할수 있는 기회가 된다
위의 테이블의 내용을 다시 DB의 테이블 같이 만들어 보자
그러면서 DB테이블과 엑셀테이블(Flat Table)의 차이를 보면서...
아하!!! 하시는 기회가 되면 이 페이지를 계속 볼수 있을 것이고
그렇지 않으시면 이 페이지는 나중에 보시고, 다른 엑셀관련 페이지만 보시면 된다
아무튼..뜻이 있는 분들은 계속 보시고
위의 코드로 만들어낸 시트를 분석해 보면
아래의 그림과 같이 검정색부분을 제외하고는 모두 중복되는 정보들이 널려있다
바로..이 중복되는 정보가 별도의 기본테이블로 만들어 두어야할 대상이 되는 테이블이다
맨위에서 테이블이 3개로 연결되었으나, 여기에서는 테이블이 하나 더 추가 된셈이다
이것을 엑셀시트에서 분산 시켜서 별도의 테이블로
DB의 관계형구조형식으로 시뮤레이션 해보도록 하자
위의 엑셀의 Flat테이블에서는 고객과 동물, 즉 고객 한명이 하나의 애완동물을
갖고 있는 것으로 했지만 현실에서는
고객이 애완동물을 하나만 갖고 있지 않을 것이다
두마리도 갖고 있을수 있고, 세마리도 갖고 있을수도 있고
이것을 감안한 분산된 테이블을 만들어 보자
이것이 관계형테이블과 근접한 시뮤레이션이다
Sub createRelationalTables()
Dim iRow As Integer
Dim arrPets As Variant
Dim shtPets As Worksheet
Dim shtCustomers As Worksheet
Dim shtTreatTypes As Worksheet
Dim shtMain As Worksheet
Const PETS As String = "애완동물테이블"
Const CUSTOMERS As String = "고객테이블"
Const TREAT_TYPES As String = "진료타입테이블"
Const MAIN As String = "진료진행테이블"
Const PET_NUMS As Integer = 14
Const CUSTOMER_NUMS As Integer = 11
Const TREAT_NUMS As Integer = 5
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(PETS).Delete
Worksheets(CUSTOMERS).Delete
Worksheets(TREAT_TYPES).Delete
Worksheets(MAIN).Delete
Application.DisplayAlerts = True
Set shtPets = Worksheets.Add
shtPets.Name = PETS
Set shtCustomers = Worksheets.Add
shtCustomers.Name = CUSTOMERS
Set shtTreatTypes = Worksheets.Add
shtTreatTypes.Name = TREAT_TYPES
Set shtMain = Worksheets.Add
shtMain.Name = MAIN
Const PET_Types As String = "Cat,Dog,Cat,Dog,Dog,Snake"
arrPets = Split(PET_Types, ",")
''///////////고객테이블
With shtCustomers
.Range("A1").Resize(, 5) = Array("CustomerID", "고객명", "주소1", "주소2", "전화번호")
For iRow = 2 To CUSTOMER_NUMS + 1
With .Range("A" & iRow).EntireRow
.Cells(1) = iRow - 1
.Cells(2) = "고객명_" & Chr(63 + iRow)
.Cells(3) = .Cells(3) & "_주소1"
.Cells(4) = .Cells(3) & "_주소2"
.Cells(5) = "010-" & Int(Rnd() * 1000) + 1000
End With
Next
.usedRange.Columns.AutoFit
End With
'//////////// 애완동물테이블
With shtPets
.Range("A1").Resize(, 6) = Array("PetID", "CustomerID", "동물명", "생일", "건강상태", "타입")
For iRow = 2 To PET_NUMS + 1
With .Range("A" & iRow).EntireRow
.Cells(1) = iRow - 1
.Cells(2) = Choose(iRow - 1, 10, 1, 4, 3, 2, 5, 6, 8, 7, 9, 10, 11, 2, 3)
.Cells(3) = arrPets(Int(Rnd() * 6)) & "_" & Int(Rnd() * 5) + 1
.Cells(4) = DateSerial(Year(Date) - Int(Rnd() * 6 + 1), Int(Rnd() * 12) + 1, Int(Rnd() * 30) + 1)
.Cells(5) = Array("A", "B", "C", "D", "E")(Int(Rnd() * 4))
.Cells(6) = Split(.Cells(3), "_")(0)
End With
Next
.usedRange.Columns.AutoFit
End With
'///////진료타입테이블
With shtTreatTypes
.Range("A1").Resize(, 3) = Array("TreatID", "진료명", "금액")
For iRow = 2 To TREAT_NUMS + 1
With .Range("A" & iRow).EntireRow
.Cells(1) = iRow - 1
.Cells(2) = "진료타입_" & Chr(63 + iRow)
.Cells(3) = Choose(iRow - 1, 50000, 80000, 100000, 150000, 180000)
End With
Next
.usedRange.Columns.AutoFit
End With
'///// 진료관리테이블- 메인
With shtMain
.Range("A1").Resize(, 6) = Array("workID", "일자", "시간", "애완동물ID", "고객ID", "진료타입ID")
For iRow = 2 To 200
With .Range("A" & iRow).EntireRow
.Cells(1) = iRow - 1
.Cells(2) = DateSerial(2016, Int(Rnd() * 12 + 1), Int(Rnd() * 30 + 1))
.Cells(3) = TimeSerial(9 + Int(Rnd() * 8) + 1, Application.WorksheetFunction.Floor(Int(Rnd() * 60) + 1, 5), 0)
.Cells(4) = Int(Rnd() * PET_NUMS) + 1
.Cells(5) = Application.WorksheetFunction.VLookup(.Cells(4), shtPets.Range("A1").CurrentRegion, 2)
.Cells(6) = Int(Rnd() * TREAT_NUMS) + 1
End With
Next
.usedRange.Columns.AutoFit
End With
End Sub
위와 같이 실행하면 4장의 시트에 분산된 테이블이 그림과 같이 각각 만들어진다
처음에 만든 테이블은 하나의 테이블에 중복되는 것을 죄다 때려넣은 하나의 테이블이지만
두번째것은 DB테이블을 엑셀시트에 그대로 만들어 본 것이다
입체적이지 않은가?
이런 테이블들이 모여서 여러분들이 하는 어떤 일이던지 발생하는 정보를
입체적인 관계를 구성하게 되는 것이다
또한 중복되는 정보를 싸악 빼버린 엑기스정보만 모아 놓은 셈이다
그리고 모든 테이블의 핵심은 ID즉 키값이 존재하여야 하는 것이다
이 키값으로 테이블과 테이블간의 관계를 구성하는 것이다
워크시트함수에서 참조함수를 수도 없이 들어 보았을 것이다
아하..참조함수가 실은 이런 테이블간의 관계때문에 필요하게 되는 것이로구나?!
라는 것을 좀더 깊이 이해하게 되는 셈이된다
메인테이블격인 [진료관리테이블]에 보면..
날짜와 시간값을 제외하고는 죄다 키값으로 표현되어 있다
이것을 어떻게 하라는 것인가??
의아하다..
참조함수를 사용하여 또 하나의 시뮤레이션을 해보자
[진료관리테이블]만 진료회수만큼 행이 계속추가 된다
그런데 사용자가 관련된 정보 애완용테이블을 숫자로 입력하고,
숫자로 된 내용만 볼수는 없을 것이다
그래서 DB에서는 Query 테이블이라는 것이 만들어지게 된다
기록은 단순하게 각각의 참조 ID만 기록하지만
관리하는 사람의 눈에는 실제의 데이타가 보여야 할것이고
그래서 Query 테이블이라는 것이 있는 것이다
이 Query테이블을 흉내내어서 엑셀시트에 또 만들어 보자
엑셀에서는 테이블에서 참조를 하는 일을 하는 대표적참조함수가 VLOOKUP이라는 함수가 있다
위에서 만든 ID만 있는 [진료관리테이블]을 아래와 같이 함수를 사용하여 재구성할수 있을 것이다
이렇게 보면 사람이 보고 인식할수 있는 의미있는 정보를 참조하여 가져온 것이다
위와 같이 정보를 참조하여 가져오기 위하여 VBA로 하자면..
Set rMainTbl = getTable(shtQuery)
Set rPetTbl = getTable(shtPets)
Set rCustomerTbl = getTable(shtCustomers)
Set rTreatTbl = getTable(shtTreatTypes)
For iRow = 1 To 2
If iRow = 1 Then
With rMainTbl.Rows(1)
With .Cells(rMainTbl.Columns.Count).Offset(, 1).Resize(, 10)
.Value = Array("동물명", "생일", "건강상태", "타입", "고객명", "주소1", "주소2", "전화번호", "진료명", "금액")
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
With .Offset(1)
'' 동물 테이블참조
.Cells(1).Formula = "=VLOOKUP(" & .Cells(1).Offset(, -3).Address(False, True) & "," & rPetTbl.Worksheet.Name & "!" & rPetTbl.Address & ",3,FALSE)" ' 동물명
.Cells(2).Formula = "=VLOOKUP(" & .Cells(2).Offset(, -4).Address(False, True) & "," & rPetTbl.Worksheet.Name & "!" & rPetTbl.Address & ",4,FALSE)" ' 생일
.Cells(2).NumberFormat = "yyyy/mm/dd"
.Cells(3).Formula = "=VLOOKUP(" & .Cells(3).Offset(, -5).Address(False, True) & "," & rPetTbl.Worksheet.Name & "!" & rPetTbl.Address & ",5,FALSE)" ' 건강상태
.Cells(4).Formula = "=VLOOKUP(" & .Cells(4).Offset(, -6).Address(False, True) & "," & rPetTbl.Worksheet.Name & "!" & rPetTbl.Address & ",6,FALSE)" ' 타입
'' 고객 테이블참조, 동물의 주인 ID를 얻어서 고객테이블에서 가져 온다
.Cells(5).Formula = "=VLOOKUP(" & .Cells(5).Offset(, -6).Address(False, True) & "," & rCustomerTbl.Worksheet.Name & "!" & rCustomerTbl.Address & ",2,FALSE)" ' 고객명
.Cells(6).Formula = "=VLOOKUP(" & .Cells(6).Offset(, -7).Address(False, True) & "," & rCustomerTbl.Worksheet.Name & "!" & rCustomerTbl.Address & ",3,FALSE)" ' 주소1
.Cells(7).Formula = "=VLOOKUP(" & .Cells(7).Offset(, -8).Address(False, True) & "," & rCustomerTbl.Worksheet.Name & "!" & rCustomerTbl.Address & ",4,FALSE)" ' 주소2
.Cells(8).Formula = "=VLOOKUP(" & .Cells(8).Offset(, -9).Address(False, True) & "," & rCustomerTbl.Worksheet.Name & "!" & rCustomerTbl.Address & ",5,FALSE)" ' 전화번호
'' 진료타입 테이블참조
.Cells(9).Formula = "=VLOOKUP(" & .Cells(9).Offset(, -9).Address(False, True) & "," & rTreatTbl.Worksheet.Name & "!" & rTreatTbl.Address & ",2,FALSE)" ' 진료명
.Cells(10).Formula = "=VLOOKUP(" & .Cells(10).Offset(, -10).Address(False, True) & "," & rTreatTbl.Worksheet.Name & "!" & rTreatTbl.Address & ",3,FALSE)" ' 금액
'' 전체범위수식채우기
.AutoFill .Resize(shtMain.UsedRange.Rows.Count - 1)
'' 참조 ID열 색상표시
.Resize(shtMain.UsedRange.Rows.Count - 1).Columns(1).Offset(, -3).Resize(, 3).Interior.ColorIndex = 6
End With
End With
End With
Else
End If
Next
위의 내용을 변수등을 사용하여 좀더 간략하게 할수도 있겠지만,
일부러 VLOOKUP함수를 강조하기 위하여
널어 놓았다
위와 같은 작업을 데이타베이스인 억세스에서는 별 수고 없이 Query테이블이라는 것이
제공되기때문에 편리한 것이다
아하..그래서 정보가 많아지고 복잡해지면 DB를 사용하겠다고 하는구나?!!
위의 VLOOKUP으로 재구성한 테이블은 바로 억세스에서는 Query테이블이라고 하면 좋을 것이다
정보가 복잡하지도 않고 량도 별로 많지도 않은데 DB를 쓰겠다고 하면
그것도 문제이지만,
정보가 넘치고 감당하기 힘들게 쌓인다면 DB, 그것도 오피스에 붙박이로 항상 같이
쫓아다니는 억세스를 활용할 일이다
위의 내용 아래 화일을 다운 받으셔서 이해를 도우시기 바랍니다
***[LOG-IN]***