엑셀 데이터 필터링 함수 :: Filtered_DB 명령문

데이터가 입력된 배열(DB)를 조건에 따라 실시간으로 필터링하는 Filtered_DB의 사용법 및 동작원리를 살펴봅니다.

작성자 :
오빠두엑셀
최종 수정일 : 2022. 04. 20. 03:16
URL 복사
메모 남기기 : (33)
엑셀 데이터 실시간 필터링 함수 Filtered_DB 명령문_R
by

엑셀 데이터 필터링 함수 :: Filtered_DB 명령문 사용법 총정리

엑셀 Filtered_DB 명령문 목차 바로가기
  1. 예제파일 다운로드
  2. 명령문 상세 설명
  3. Filtered_DB 명령문 전체 코드
  4. 명령문 실전 사용예제
  5. 동작원리 단계별 알아보기
요약

엑셀 Filtered_DB는 데이터가 입력된 배열(DB)를 실시간으로 필터링하는 사용자 지정 함수입니다.

명령문 구문
Array = Filtered_DB ( DB, 조건, [열번호], [일치옵션] )
사용된 인수 및 변수 알아보기
인수 설명
DB
[Variant]		 필터링 할 데이터가 입력된 배열(DB) 입니다. Get_DB 함수를 사용하면 시트 안에 입력된 데이터를 배열로 받아올 수 있습니다.
조건
[Variant]		 필터링 할 조건입니다. 연산자(>,<,=)와 와일드카드(*,?)를 사용할 수 있습니다.
열번호
[Long, 선택인수]		 배열에서 필터링 할 열 번호입니다. 열번호가 빈칸일 경우 배열의 모든 값을 대상으로 필터링합니다. 기본값은 빈칸입니다.
일치옵션
[Boolean, 선택인수]		 정확히 일치 여부입니다. TRUE일 경우 조건과 정확히 일치하는 경우만 필터링합니다. 기본값은 FALSE 입니다.
패치노트
  • 2021.07.21
    : 비어있는 DB를 입력할 경우, 오류 대신 비어있는 DB를 반환하도록 명령문 수정
  • 2021.08.19
    : 같지 않음 (<>) 조건으로도 필터링 할 수 있도록 명령문 개선

예제파일 다운로드

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

상세 설명

엑셀 Filtered_DB 함수는 데이터가 입력된 배열(DB)을 조건에 따라 필터링한 뒤, 필터링 된 결과를 배열로 반환하는 사용자 지정 함수입니다. Filtered_DB 함수는 다른 DB 관련 함수와 함께 여러 엑셀 프로그램을 제작할 때 매우 유용하게 사용할 수 있습니다.

Filtered_DB 함수는 데이터베이스 관련 VBA 함수 중 하나입니다. 더 다양한 DB 관련 함수는 아래 링크를 참고해주세요.

엑셀 데이터베이스 관련 VBA 명령문 사용법

Filtered_DB 함수에 입력하는 조건은 1개만 입력가능하며 AND, OR 조건으로 동시에 필터링 할 수 없습니다. 만약 DB에 여러 조건을 적용하려면 Filtered_DB 함수를 여러번에 걸쳐 사용해야 하며, AND 조건으로만 필터링 할 수 있습니다.

함수의 마지막인수는 [정확히일치] 옵션이며 기본값은 유사일치입니다. 따라서 조건으로 "이"를 입력하면 DB에서 이를 포함하는 모든 값을 필터링합니다.

실전 사용 예제
  1. 직원정보 DB에서 나이가 20 이상인 직원 필터링 
    '직원정보 : 직원ID | 직원명 | 부서 | 직급 | 나이
Dim DB As Variant
DB = Get_DB(ThisWorkbook.Worksheets("직원정보"))
DB = Filtered_DB(DB, ">=20", 5)
'직원정보DB에서 나이가 20 이상인 직원만 필터링됩니다.
  2. 직원정보 DB에서 성이 이씨이고 나이가 20 이상인 직원 필터링 
    '직원정보 : 직원ID | 직원명 | 부서 | 직급 | 나이
Dim DB As Variant
 
DB = Get_DB(ThisWorkbook.Worksheets("직원정보"))
DB = Filtered_DB(DB, "이*")
DB = Filtered_DB(DB, ">=20", 5)
'직원정보에서 성이 이씨이고 나이가 20 이상인 직원을 필터링합니다.

엑셀 데이터 필터링 함수, Filtered_DB 명령문 동작원리

Filtered_DB 명령문 전체 코드
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'수정 및 배포 시 출처를 반드시 명시해야 합니다.
 
'■ Filtered_DB 함수
'■ 서로 다른 두 시트를 연결합니다. FromWS의 첫번째 필드는 반드시 고유값(ID)이 입력되어야 합니다.
'■ 사용방법
'Array = Filtered_DB(Get_DB(Sheet1),">=200")
'■ 인수 설명
'_____________DB                  : 데이터를 필터링 할 원본 DB 입니다.
'_____________Value             : 필터링 할 조건입니다.
'_____________FilterCol         : [선택인수] 필터링 할 검색 열입니다. 빈칸일 경우 전체 열을 대상으로 필터링합니다.
'_____________ExactMatch   : [선택인수] 정확히 일치 여부입니다. 기본값은 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
 
'<-- 21.08.19 수정 : DB 비어있을 시, 오류 대신 비어있는 DB 반환 -->
If IsEmpty(DB) Then Filtered_DB = DB: Exit Function
 
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
 
    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
 
'<-- 21.08.19 수정 : 제외조건(<>)으로 필터링 가능하도록 수정 -->
    If Operator <> "" And Operator <> "<>" And 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
            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
            If Operator = "<>" Then
                Value = Right(Value, Len(Value) - 2)
                For i = 1 To cRow
                     If Not 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
        Else
            If Operator = "<>" Then
            Value = Right(Value, Len(Value) - 2)
                For i = 1 To cRow
                    If Not 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) = 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
    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
 
Function Get_DB(WS As Worksheet, Optional NoID As Boolean = False, Optional IncludeHeader As Boolean = False) As Variant
 
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'수정 및 배포 시 출처를 반드시 명시해야 합니다.
 
'■ Get_DB 함수
'■ 지정한 시트의 값을 배열로 반환합니다. 시트의 값은 반드시 A1셀에서 시작해야 합니다. 머릿글 우측으로 ID 값이 없을 경우 NoID를 TRUE로 사용합니다.
'■ 사용방법
'Array = Get_DB(ThisWorkBook.WorkSheets("시트명"), TRUE)
'▶ 인수 설명
'_____________WS                     : 배열로 변환할 시트 개체입니다.
'_____________NoID                  : 머릿글 우측에 신규 ID값이 없을 경우, TRUE로 사용합니다. 기본값은 FALSE 입니다.
'_____________IncludeHeader   : True일 경우 배열에 머릿글을 포함합니다. 기본값은 FALSE 입니다.
'###############################################################
 
Dim cRow As Long
Dim cCol As Long
Dim offCol As Long
 
If NoID = False Then offCol = -1
 
With WS
    cRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    cCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + offCol
    Get_DB = .Range(.Cells(2 + Sgn(IncludeHeader), 1), .Cells(cRow, cCol))
End With
 
End Function
명령문 동작원리 단계별 알아보기
  1. 변수를 선언하고 Dictionary 개체를 설정합니다. 
    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")
  2. 결과값으로 반환 할 전체 배열(vArr)와 필터링 조건을 적용할 임시배열(filterArr)를 생성합니다. 
    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
  3. 조건으로 연산자(<,>,=)가 포함되어 있을 경우 명령문에 맞게 조건문을 변경합니다. 
        If 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
  4. 입력한 조건에 따라 DB를 필터링합니다. 
    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 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
  5. 출력할 배열(vResult)을 생성하고 배열 안에 값을 입력 후 함수 결과값으로 배열을 반환한 뒤 명령문을 종료합니다. 
        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
5 14 투표
게시글평점
33 댓글
Inline Feedbacks
모든 댓글 보기
33
0
여러분의 생각을 댓글로 남겨주세요.x