엑셀 데이터, 서버 없이 SQL로 관리하는 획기적인 방법

복잡한 SQL 서버 설정, 관리 없이 엑셀 안에서 VBA 명령문으로 관계형 데이터베이스를 구축하고 관리하는 방법을 하나씩 살펴봅니다.

홈페이지 » 엑셀 데이터, 서버 없이 SQL로 관리하는 획기적인 방법

작성자 :
오빠두엑셀
최종 수정일 : 2022. 05. 26. 03:18
URL 복사
평점 남기기 : (11)

엑셀 데이터, 서버 없이 SQL로 관리하는 획기적인 방법 | 퀵 VBA

서버 없이 엑셀 데이터 SQL로 관리 목차 바로가기
영상 강의

큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [퀵VBA] 엑셀 VBA로 SQL 관계형 데이터 구축하기
    예제파일
  • [퀵VBA] 엑셀 VBA로 SQL 관계형 데이터 구축하기
    완성파일
  • [퀵VBA] 엑셀 VBA로 SQL 관계형 데이터 구축하기
    E-Book

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


매크로 DB 함수와 SQL을 사용하는 것의 차이점

SQL(Structured Query Language)은 관계형 데이터베이스를 관리하기 위한 관리체계이자 프로그램 언어로 전 세계 데이터베이스의 60% 이상이 SQL로 관리되며 그 비중은 점점 커 지고 있습니다. (나머지 40%는 No SQL 형태(예: Mongo DB, 등..) 으로 관리됩니다)

엑셀 데이터 베이스 사용 비중
2019년도 데이터 베이스 트렌드 (SQL : 60.5%, NoSQL : 39.5%)

이번 강의에서는 SQL 의 기능을 깊게 살펴보는 대신, "엑셀↔SQL 연동" vs. "매크로 DB함수 사용" 의 차이점과 그 장단점을 하나씩 알아보겠습니다.

매크로 DB 함수를 사용하면 무엇이 좋나요?

일반 실무자에게 SQL 서버를 직접 구축하고 데이터를 관리하는 것은 쉬운 일이 아닐 것 입니다. 물론 요즘은 구글 클라우드 플랫폼(GCP), 아마존 웹 서비스(AWS), 애져(AZURE)등 SQL 서버를 손쉽게 구축할 수 있는 다양한 클라우드 플랫폼이 있지만, 매월 들어가는 유지비를 고려한다면 러한 플랫폼을 사용하는 것 또한 일반 실무자에게는 부담이 될 수 밖에 없는데요.

엑셀 데이터베이스 GCP AWS AZURE
요즘은 다양한 클라우드 플랫폼을 통해 서버를 손쉽게 구축할 수 있습니다.

보통의 IT부서가 없는 중소기업에서 흔히 겪는 문제는, 이런식으로 SQL 서버를 직접 관리하기 어려운 상황에서 데이터베이스를 관리 할 수 있는 프로그램을 직접 개발하는 단계에서 발생합니다. 비용이나 시간적으로 제한이 있다보니 대부분의 과정을 직접 개발해야 하는데, Back-End(서버단)과 Front-End(사용자단)을 모두 제어할 수 있는 프로그램을 구축하는데 어려움을 겪게되죠.

이런 상황에서 우리가 현실적으로 가장 먼저 고려할 수 있는 방법은 엑셀 매크로 DB 함수를 활용하는 것입니다. 특히 예상되는 데이터베이스 크기가 100만행 이하라면 이번 강의에서 소개해드린 매크로 DB 함수를 활용하면 누구나 엑셀만으로 관계형 데이터베이스를 구축하고 손쉽게 프로그램을 제작할 수 있습니다.

  • IT부서가 없는 중소기업에서 매월 수만행 정도가 누적되는 데이터를 관리중이면서 프로그램을 직접 개발하기엔 비용/시간적으로 제한되는 경우
  • ERP 프로그램에서 다운받은 정규화 된 엑셀 자료를 손쉽게 관리할 수 있는 프로그램을 개발해야 할 경우
  • 프로그램을 동시에 사용하는 경우가 매우 드물며, 관리하는 데이터 크기가 100만행 이하로 예상되는 경우

하지만 쉽게 업무에 적용되는 만큼, 고려해야 할 부분도 몇가지 있는데요. 이를 요약하면 아래 표와 같습니다.

항목 매크로 DB 함수 엑셀↔SQL 연동
레코드 개수(행 개수) 1,048,576 행 이하 무제한
동시 편집 동시 편집 불가능 동시 편집 가능
처리 속도 보통 (100만행 처리시 1초 내외, 현업에서 사용시 지장 없을 정도의 처리속도) 매우 빠름(지연시간 거의 없음)
보안 취약 (파일 안에 데이터가 하드코딩되어 저장되므로 보안에 취약함) 우수 (서버 안에 데이터를 저장, IP 주소 별로 접근 권한 설정 가능)
개발 난이도 Back-End 와 Front-End 모두 쉽게 개발 가능 Back-End : ODBC 라이브러리를 통해 엑셀↔SQL 연동, SQL 쿼리문에 대한 이해 필요
Front-End : 쉽게 개발 가능
코드 안정성 엑셀 2010 이후 버전에서 안정하게 동작 SQL 및 윈도우 버전에 따라 오류가 발생할 수 있으며, 버전이 달라질 때 마다 코드 수정이 필요함
관리 수준 DB 개수가 10개 이상일 경우 관리에 어려움. 데이터 구조에 대한 전반적인 이해만 있으면 쉽게 관리 가능. DB 개수에 상관없이 쉽게 관리 가능. SQL에 대한 기본 지식 필요.

엑셀 매크로 DB 함수 사용시 주의사항

이번 강의에서 소개해드린 매크로 DB 함수는 실무에서 발생하는 대부분의 상황에 적용할 수 있습니다. 그리고 매크로 DB 함수를 사용하면 매우 빠르고 편리하게 엑셀 프로그램을 제작하고 보완 할 수 있습니다. 하지만 위 표에 적어드린 것처럼, 아래 2가지 제한사항을 사전에 반드시 확인해주세요.

  • 매크로 DB 함수는 1,048,576 행 이하의 데이터에서만 사용가능합니다. (*파워쿼리/파워피벗을 통한 데이터모델을 ODBC로 불러와 활용할수도 있습니다. 하지만 그럴 경우, 파워피벗/파워쿼리를 활용하는 것 보다 SQL을 활용하는 것이 훨씬 효율적입니다.)
  • 매크로 DB 함수는 엑셀 파일을 직접 참조하므로 동시에 여러명이 편집/수정할 수 없습니다. (*다른 통합문서를 참조하더라도, 엑셀은 동시에 여러명이 같은 통합문서를 실행할 수 없으므로 동시 편집/수정이 불가능합니다.)

매크로 DB 함수를 사용한 실전 예제는 아래 재고관리 프로그램 만들기 영상강의를 참고하세요.

매크로 DB 함수 전체 명령문

Option Explicit
Option Compare Text
'########################
' 특정 워크시트에서 앞으로 추가해야 할 최대 ID번호 리턴 (시트 DB 우측 첫번째 머릿글)
' i = Get_MaxID(Sheet1)
'########################
Function Get_MaxID(WS As Worksheet) As Long
With WS
    Get_MaxID = .Cells(1, .Columns.Count).End(xlToLeft).Value
    .Cells(1, .Columns.Count).End(xlToLeft).Value = .Cells(1, .Columns.Count).End(xlToLeft).Value + 1
End With
End Function
'########################
' 워크시트에 새로운 데이터를 추가해야 할 열번호 반환
' i = Get_InsertRow(Sheet1)
'########################
Function Get_InsertRow(WS As Worksheet) As Long
With WS:    Get_InsertRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1: End With
End Function
'########################
' 시트의 열 개수 반환 (이번 예제파일에서만 사용)
' i  = Get_ColumnCnt(Sheet1)
'########################
Function Get_ColumnCnt(WS As Worksheet, Optional Offset As Long = -1) As Long
With WS:    Get_ColumnCnt = .Cells(1, .Columns.Count).End(xlToLeft).Column + Offset: End With
End Function
'########################
' 시트에서 특정 ID 의 행 번호 반환 (-> 해당 행 번호 데이터 업데이트)
' i = get_UpdateRow(Sheet1, ID)
'########################
Function get_UpdateRow(WS As Worksheet, ID)
Dim i As Long
Dim cRow As Long
With WS
    cRow = Get_InsertRow(WS) - 1
    For i = 1 To cRow
        If .Cells(i, 1).Value = ID Then get_UpdateRow = i: Exit For
    Next
End With
End Function
 
 
'########################
' 특정 시트의 DB 정보를 배열로 반환 (이번 예제파일에서만 사용)
' Array = Get_DB(Sheet1)
'########################
Function Get_DB(WS As Worksheet, Optional NoID As Boolean = False, Optional IncludeHeader As Boolean = False) As Variant
 
Dim cRow As Long
Dim cCol As Long
Dim offCol As Long
 
If NoID = False Then offCol = -1
 
With WS
    cRow = Get_InsertRow(WS) - 1
    cCol = Get_ColumnCnt(WS, offCol)
    Get_DB = .Range(.Cells(2 + Sgn(IncludeHeader), 1), .Cells(cRow, cCol))
End With
 
End Function
 
'########################
'특정 시트에서 지정한 ID의 필드 값 반환 (이번 예제파일 전용)
' Value = Get_Records(Sheet1, ID, "필드명")
'########################
Function Get_Records(WS As Worksheet, ID, Fields)
 
Dim cRow As Long: Dim cCol As Long
Dim vFields As Variant: Dim vField As Variant
Dim vFieldNo As Variant
Dim i As Long: Dim j As Long
 
 
cRow = Get_InsertRow(WS) - 1
cCol = Get_ColumnCnt(WS)
 
If InStr(1, Fields, ",") > 0 Then vFields = Split(Fields, ",") Else vFields = Array(Fields)
ReDim vFieldNo(0 To UBound(vFields))
 
With WS
    For Each vField In vFields
        For i = 1 To cCol
            If .Cells(1, i).Value = Trim(vField) Then vFieldNo(j) = i: j = j + 1
        Next
    Next
 
    For i = 2 To cRow
        If .Cells(i, 1).Value = ID Then
            For j = 0 To UBound(vFieldNo)
                vFieldNo(j) = .Cells(i, vFieldNo(j))
            Next
            Exit For
        End If
    Next
 
Get_Records = vFieldNo
 
End With
 
End Function
 
'########################
' 시트에 새로운 레코드 추가 (반드시 첫번째 값은 ID, 나머지 값 순서대로 입력)
' Insert_Record Sheet1, ID, 필드1, 필드2, 필드3, ..
'########################
Sub Insert_Record(WS As Worksheet, ParamArray vaParamArr() As Variant)
 
Dim cID As Long
Dim cRow As Long
Dim vaArr As Variant: Dim i As Long: i = 2
 
With WS
    cRow = Get_InsertRow(WS)
    If InStr(1, .Cells(1, 1).Value, "ID") > 0 Then
        cID = Get_MaxID(WS)
        .Cells(cRow, 1).Value = cID
        For Each vaArr In vaParamArr
            .Cells(cRow, i).Value = vaArr
            i = i + 1
        Next
    Else
        For Each vaArr In vaParamArr
            .Cells(cRow, i - 1).Value = vaArr
            i = i + 1
        Next
    End If
 
End With
 
End Sub
'########################
' 시트에서 ID 를 갖는 레코드의 모든 값 업데이트 (반드시 첫번째 값은 ID여야 하며, 나머지 값을 순서대로 입력)
' Update_Record Sheet1, ID, 필드1, 필드2, 필드3, ...
'########################
Sub Update_Record(WS As Worksheet, ParamArray vaParamArr() As Variant)
 
Dim cRow As Long
Dim i As Long
Dim ID As Variant
 
If IsNumeric(vaParamArr(0)) = True Then ID = CLng(vaParamArr(0)) Else ID = vaParamArr(0)
 
With WS
    cRow = get_UpdateRow(WS, ID)
 
    For i = 1 To UBound(vaParamArr)
        If Not IsMissing(vaParamArr(i)) Then .Cells(cRow, i + 1).Value = vaParamArr(i)
    Next
 
End With
 
End Sub
'########################
' 시트에서 ID 를 갖는 레코드 삭제
' Delete_Record Sheet1, ID
'########################
Sub Delete_Record(WS As Worksheet, ID)
 
Dim cRow As Long
 
If IsNumeric(ID) = True Then ID = CLng(ID)
 
With WS
    cRow = get_UpdateRow(WS, ID)
    .Cells(cRow, 1).EntireRow.Delete
End With
 
End Sub
 
'########################
' 배열의 외부ID키 필드를 본 시트DB와 연결하여 해당 외부ID키의 연관된 값을 배열로 반환
' Array = Connect_DB(Get_DB(Sheet1),2,Sheet2, "필드1, 필드2, 필드3")
'########################
Function Connect_DB(DB As Variant, ForeignID_Fields As Variant, FromWS As Worksheet, Fields As String, Optional IncludeHeader As Boolean = False)
 
Dim cRow As Long: Dim cCol As Long
Dim vForeignID_Fields As Variant: Dim vForeignID_Field As Variant
Dim ForeignID As Variant
Dim vFields As Variant: Dim vField As Variant
Dim vID As Variant: Dim vFieldNo As Variant
Dim Dict As Object
Dim i As Long: Dim j As Long
Dim AddCols As Long
 
 
cRow = UBound(DB, 1)
cCol = UBound(DB, 2)
If InStr(1, Fields, ",") > 1 Then
    AddCols = Len(Fields) - Len(Replace(Fields, ",", "")) + 1
    vFields = Split(Fields, ",")
Else
    AddCols = 1
    vFields = Array(Fields)
End If
 
ReDim Preserve DB(1 To cRow, 1 To cCol + AddCols)
 
Set Dict = Get_Dict(FromWS)
vID = Dict("ID")
 
ReDim vFieldNo(0 To UBound(vFields))
 
For Each vField In vFields
    For i = 1 To UBound(vID)
        If vID(i) = Trim(vField) Then vFieldNo(j) = i: j = j + 1
    Next
Next
 
If InStr(1, ForeignID_Fields, ",") > 0 Then vForeignID_Fields = Split(ForeignID_Fields, ",") Else vForeignID_Fields = Array(ForeignID_Fields)
 
For Each vForeignID_Field In vForeignID_Fields
    For i = 1 To cRow
        If IncludeHeader = True And i = 1 Then ForeignID = "ID" Else ForeignID = DB(i, Trim(vForeignID_Field))
        If Dict.Exists(ForeignID) Then
            For j = 1 To AddCols
                DB(i, cCol + j) = Dict(ForeignID)(vFieldNo(j - 1))
            Next
        End If
    Next
Next
 
Connect_DB = DB
 
End Function
'########################
' 특정 배열에서 Value를 포함하는 레코드만 찾아 다시 배열로 반환
' Array = Filtered_DB(Array, "검색값", False)
'########################
Function Filtered_DB(DB, Value, Optional FilterCol, Optional ExactMatch As Boolean = False) As Variant
 
Dim cRow As Long
Dim cCol As Long
Dim vArr As Variant: Dim s As String: Dim filterArr As Variant:  Dim Cols As Variant: Dim Col As Variant: Dim Colcnt As Long
Dim isDateVal As Boolean
Dim vReturn As Variant: Dim vResult As Variant
Dim Dict As Object: Dim dictKey As Variant
Dim i As Long: Dim j As Long
Dim Operator As String
 
Set Dict = CreateObject("Scripting.Dictionary")
 
If Value <> "" Then
    cRow = UBound(DB, 1)
    cCol = UBound(DB, 2)
    ReDim vArr(1 To cRow)
    For i = 1 To cRow
        s = ""
        For j = 1 To cCol
            s = s & DB(i, j) & "|^"
        Next
        vArr(i) = s
    Next
 
    If IsMissing(FilterCol) Then
        filterArr = vArr
    Else
        Cols = Split(FilterCol, ",")
        ReDim filterArr(1 To cRow)
        For i = 1 To cRow
            s = ""
            For Each Col In Cols
                s = s & DB(i, Trim(Col)) & "|^"
            Next
            filterArr(i) = s
        Next
    End If
 
    '수정 Or Left(Value, 2) = "<>" 추가
    If Left(Value, 2) = ">=" Or Left(Value, 2) = "<=" Or Left(Value, 2) = "=>" Or Left(Value, 2) = "=<" Or Left(Value, 2) = "<>" Then
        Operator = Left(Value, 2)
        If IsDate(Right(Value, Len(Value) - 2)) Then isDateVal = True
    ElseIf Left(Value, 1) = ">" Or Left(Value, 1) = "<" Then
        Operator = Left(Value, 1)
        If IsDate(Right(Value, Len(Value) - 1)) Then isDateVal = True
    Else: End If
 
    If Operator <> "" Then
        If isDateVal = False Then
            Select Case Operator
                Case ">"
                    For i = 1 To cRow
                        If CDbl(Left(filterArr(i), Len(filterArr(i)) - 2)) > CDbl(Right(Value, Len(Value) - 1)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn
                    Next
                Case "<"
                    For i = 1 To cRow
                        If CDbl(Left(filterArr(i), Len(filterArr(i)) - 2)) < CDbl(Right(Value, Len(Value) - 1)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn Next Case ">=", "=>"
                    For i = 1 To cRow
                        If CDbl(Left(filterArr(i), Len(filterArr(i)) - 2)) >= CDbl(Right(Value, Len(Value) - 2)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn
                    Next
                Case "<=", "=<"
                    For i = 1 To cRow
                        If CDbl(Left(filterArr(i), Len(filterArr(i)) - 2)) <= CDbl(Right(Value, Len(Value) - 2)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn
                    Next
                Case "<>"
                    If ExactMatch = False Then
                        For i = 1 To cRow
                            If Not (filterArr(i) Like "*" & Right(Value, Len(Value) - 2) & "*") Then
                                vArr(i) = Left(vArr(i), Len(vArr(i)) - 2)
                                vReturn = Split(vArr(i), "|^")
                                Dict.Add i, vReturn
                            End If
                        Next
                    Else
                        For i = 1 To cRow
                            If Not (filterArr(i) Like Right(Value, Len(Value) - 2) & "|^") Then
                                vArr(i) = Left(vArr(i), Len(vArr(i)) - 2)
                                vReturn = Split(vArr(i), "|^")
                                Dict.Add i, vReturn
                            End If
                        Next
                    End If
            End Select
        Else
            Select Case Operator
                Case ">"
                    For i = 1 To cRow
                        If CDate(Left(filterArr(i), Len(filterArr(i)) - 2)) > CDate(Right(Value, Len(Value) - 1)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn
                    Next
                Case "<"
                    For i = 1 To cRow
                        If CDate(Left(filterArr(i), Len(filterArr(i)) - 2)) < CDate(Right(Value, Len(Value) - 1)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn Next Case ">=", "=>"
                    For i = 1 To cRow
                        If CDate(Left(filterArr(i), Len(filterArr(i)) - 2)) >= CDate(Right(Value, Len(Value) - 2)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn
                    Next
                 Case "<=", "=<"
                    For i = 1 To cRow
                        If CDate(Left(filterArr(i), Len(filterArr(i)) - 2)) <= CDate(Right(Value, Len(Value) - 2)) Then: vArr(i) = Left(vArr(i), Len(vArr(i)) - 2): vReturn = Split(vArr(i), "|^"): Dict.Add i, vReturn Next End Select End If Else If ExactMatch = False Then For i = 1 To cRow If filterArr(i) Like "*" & Value & "*" Then vArr(i) = Left(vArr(i), Len(vArr(i)) - 2) vReturn = Split(vArr(i), "|^") Dict.Add i, vReturn End If Next Else For i = 1 To cRow If filterArr(i) Like Value & "|^" Then vArr(i) = Left(vArr(i), Len(vArr(i)) - 2) vReturn = Split(vArr(i), "|^") Dict.Add i, vReturn End If Next End If End If If Dict.Count > 0 Then
        ReDim vResult(1 To Dict.Count, 1 To cCol)
        i = 1
        For Each dictKey In Dict.Keys
            For j = 1 To cCol
                vResult(i, j) = Dict(dictKey)(j - 1)
            Next
            i = i + 1
        Next
    End If
 
    Filtered_DB = vResult
Else
    Filtered_DB = DB
End If
 
End Function
 
'########################
' 특정 시트의 DB 정보를 Dictionary로 반환 (이번 예제파일에서만 사용)
' Dict = GetDict(Sheet1)
'########################
Function Get_Dict(WS As Worksheet) As Object
 
Dim cRow As Long: Dim cCol As Long
Dim Dict As Object
Dim vArr As Variant
Dim i As Long: Dim j As Long
 
Set Dict = CreateObject("Scripting.Dictionary")
 
With WS
    cRow = Get_InsertRow(WS) - 1
    cCol = Get_ColumnCnt(WS)
 
    For i = 1 To cRow
        ReDim vArr(1 To cCol - 1)
        For j = 2 To cCol
            vArr(j - 1) = .Cells(i, j)
        Next
        Dict.Add .Cells(i, 1).Value, vArr
    Next
End With
 
Set Get_Dict = Dict
 
End Function

1. 시트에 입력된 데이터를 DB 배열로 변환

Get_DB 함수 구문
Dim DB As Variant
DB = Get_DB ( 시트, [ID없음], [머릿글포함] )

Get_DB 함수는 시트 안에 입력된 데이터를 배열로 반환하는 함수입니다. 데이터를 범위대신 배열로 받아오면, 처리속도가 빨라질 뿐만 아니라, 데이터를 가공하는데 많은 이점을 챙길 수 있습니다.

Get_DB 함수가 참조하는 시트의 데이터는 반드시 아래 규칙을 지켜서 작성되어야 합니다.

엑셀 GET_DB 함수 사용법
DB 함수를 사용할 데이터 관리 규칙
  1. 데이터는 A1셀에서 시작합니다.
  2. 머릿글은 1행에 1줄로 작성합니다.
  3. 병합된 셀이 없어야 합니다.
  4. ID(고유값)이 있을 경우, 반드시 첫번째 열에 입력되어야 하며 DB 오른쪽으로 신규 ID가 입력된 셀이 있어야 합니다.

Get_DB 함수를 사용하면 시트 안에 입력된 데이터가 배열로 반환되며, 반환된 배열은 Connect_DB 함수(관계생성) 와 Filtered_DB 함수(필터링), 또는 ArrayToRng 함수(범위출력)에 사용할 수 있습니다.

Get_DB 명령문 사용예제

Sub Get_DB_Test()
 
Dim DB As Variant
 
DB = Get_DB(ThisWorkbook.Worksheets("주문상세"))
'주문상세 시트에 입력된 데이터를 배열로 반환합니다.
End Sub

2. DB 와 다른 시트가 연결된 관계형 DB 생성

Connect_DB 함수 구문
Dim DB As Variant
DB = Get_DB ( 시트, [ID없음], [머릿글포함] )
DB = Connect_DB ( DB, ID번호, 연결할시트, 불러올필드, [머릿글포함] )

Connect_DB 함수는 기존 배열에서 ID를 참조하여 다른 시트가 연결된 DB를 생성하는 함수입니다. 예를 들어, [급여내역]에 입력된 직원ID를 참조하여 [급여내역] - [직원정보] 가 연결된 관계형 DB를 만들거나, [매출내역]에 입력된 제품ID를 참조하여 [매출내역] - [제품정보]가 연결된 관계형 DB를 만들 수 있습니다.

Connect_DB 함수로 연결되는 시트의 데이터는 Get_DB 함수와 동일한 규칙으로 작성되어야 합니다. 연결된 시트는 반드시 ID열을 포함해야 하며, ID는 반드시 첫번째 열에 입력되어야 합니다.

Connect_DB 명령문 사용예제

Option Explicit
 
Sub Connect_DB_Test()
 
Dim DB As Variant
 
DB = Get_DB(ThisWorkbook.Worksheets("주문상세"))
'주문상세 시트에 입력된 데이터를 배열로 반환합니다.
 
DB = Connect_DB(DB, 2, ThisWorkbook.Worksheets("주문목록"), "주문번호,생성시간")
'주문상세 시트 2번째 열에 입력된 주문ID를 참조하여 주문목록시트에서 주문번호와 생성시간 데이터를 연결합니다.
 
End Sub

3. 입력한 조건에 맞춰 DB를 필터링

Filtered_DB 함수 구문
Dim DB As Variant
DB = Get_DB ( 시트, [ID없음], [머릿글포함] )
DB = Filtered_DB ( DB, 조건, [열번호], [일치옵션] )

Filtered_DB 함수는 입력한 조건에 따라 배열을 필터링한 뒤, 필터링 된 배열을 반환하는 함수입니다. Filtered_DB에 입력하는 조건은 하나만 입력 가능합니다. 만약 하나의 DB에 여러 조건을 동시에 적용해야 된다면, Filtered_DB 함수를 여러번 사용합니다.

Filtered_DB 명령문 사용예제

Sub Filtered_DB_Test()
 
Dim DB As Variant
 
DB = Get_DB(ThisWorkbook.Worksheets("주문상세"))
'주문상세 시트에 입력된 데이터를 배열로 반환합니다.
 
DB = Filtered_DB(DB, ">=" & 20000, 5)
'주문상세 시트의 5번째 열(가격)이 20000 이상인 항목만 필터링합니다.
 
End Sub

4. 시트에 새로운 레코드 추가

Insert_Record 함수 구문
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("고객명")
Insert_Record WS, "값1", "값2", "값3", ...

Insert_Record 함수는 시트에 입력된 데이터에 새로운 레코드를 추가하는 함수입니다.

Insert_Record 함수로 새로운 레코드가 추가되는 시트의 데이터는 아래 규칙을 지켜 관리되어야 합니다.

Insert_Record 예제
ID가 연속된 순번일 경우, 데이터 우측에서 신규 ID 값을 관리합니다.
  1. 데이터는 A1셀에서 시작합니다.
  2. 머릿글은 1행에 1줄로 작성합니다.
  3. 병합된 셀이 없어야 합니다.
  4. ID(고유값)이 있을 경우, 반드시 첫번째 열에 입력되어야 하며 DB 오른쪽으로 신규 ID가 입력된 셀이 있어야 합니다.
  5. 고유값이 있을 경우, 고유값의 머릿글은 반드시 "ID"라는 문자를 포함해야 합니다. ID가 연속된 숫자일 경우 데이터의 오른쪽으로 새롭게 추가될 ID 순번을 같이 관리해야 합니다.

Insert_Record 명령문 사용예제

Sub Insert_Record_Test()
 
Dim WS As Worksheet
 
Set WS = ThisWorkbook.Worksheets("고객명")
Insert_Record WS, "ABC", "김하늘", "010-1234-1234", "서울시 강남구 대치동", Date
'고객명 시트에 새로운 고객정보를 추가합니다.
 
End Sub

5. 시트에서 레코드 삭제

Delete_Record 함수 구문
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("고객명")
Delete_Record WS, ID, [행번호여부], [ID열]

Delete_Record 함수는 시트에 입력된 레코드를 삭제하는 함수입니다.

행번호여부를 TRUE로 입력하면 ID 대신 행 번호를 참조하여 레코드를 삭제합니다. ID열에 열 번호를 입력하면 지정한 열에서 ID를 찾아 레코드를 삭제합니다. ID열을 입력하지 않을 경우, 첫번째 열에서 ID를 찾아 레코드를 삭제합니다.

Delete_Record 명령문 사용예제

Sub Delete_Record_Test()
 
Dim WS As Worksheet
 
Set WS = ThisWorkbook.Worksheets("고객명")
Delete_Record WS, 4
'고객명 시트에서 ID가 4인 고객정보를 삭제합니다.
 
End Sub

6. 특정 레코드를 찾아 업데이트

Update_Record 함수 구문
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("고객명")
Update_Record WS, ID, "값1", "값2", "값3", ...

Update_Record 함수는 시트에 입력된 데이터베이스에서 지정한 ID를 찾아 데이터를 업데이트하는 함수입니다. ID는 반드시 데이터 첫번째 열에 입력되어야 합니다.

만약 ID로 중복값이 있을 경우, Update_Record 함수는 위에서부터 첫번째로 일치하는 레코드를 찾아 값을 업데이트 합니다.

Update_Record 명령문 사용예제

Sub Update_Record_Test()
 
Dim WS As Worksheet
 
Set WS = ThisWorkbook.Worksheets("고객명")
Update_Record WS, 4, "APPLE", "이민수", "010-1234-1234", "서울시 강남구 대치동", Date
'고객명 시트에서 ID가 4인 고객정보를 갱신합니다.
 
End Sub
4.8 11 투표
게시글평점
guest
14 댓글
Inline Feedbacks
모든 댓글 보기
14
0
여러분의 생각을 댓글로 남겨주세요.x