엑셀 모든 버전에서 가능한, VLOOKUP 함수 완벽 자동화 | 10초면 됩니다

피벗테이블과 엑셀 기본 함수만 활용해서, 여러 VLOOKUP 함수 보고서를 쉽고 빠르게 만드는 방법!🔥 (VBA 마스터 코드 제공)

# 함수및공식 # 엑셀기능 # VBA

작성자 :
오빠두엑셀
최종 수정일 : 2024. 03. 07. 02:19
URL 복사
메모 남기기 : (24)

엑셀 모든 버전에서 가능한, VLOOKUP 함수 완벽 자동화

엑셀 VLOOKUP 자동화 목차 바로가기
영상 강의

큰 화면으로 보기

예제파일 다운로드

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

  • 초보자를 위한, 엑셀 VLOOKUP 함수 자동화 보고서
    예제파일

.

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

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


VLOOKUP 함수에 참조할 목록 시트 만들기

피벗테이블의 보고서 필터 페이지 기능을 활용하면 VLOOKUP 함수로 검색할 목록을 여러 개의 시트로 편리하게 생성할 수 있습니다.

  1. 피벗테이블 만들기 : 예제파일에서 [급여 내역] 시트로 이동한 후, 급여내역이 작성된 범위를 선택합니다. 이후 [삽입] - [피벗테이블]을 클릭한 후, 새 워크시트에 새로운 피벗테이블을 생성합니다.

    엑셀-피벗테이블-만들기
    범위를 선택한 후, 새 워크시트에 피벗테이블을 생성합니다.
  2. 피벗테이블을 선택한 후, 피벗테이블 필드 목록에서 VLOOKUP 함수 검색에 사용할 필드인 '사번' 필드를 '필터 영역'으로 드래그합니다.

    VLOOKUP-함수-목록-필드-추가
    VLOOKUP 목록에 사용할 필드를 필터 영역에 추가합니다.
  3. 피벗테이블 필터 페이지 생성 : [피벗테이블 분석] - [옵션] 우측의 확장 버튼에서 [보고서 필터 페이지 표시] 버튼을 클릭합니다.

    피벗테이블-필터-페이지-표시
    피벗테이블 분석 - 옵션 - 보고서 필터 페이지 표시를 클릭합니다.
  4. 보고서 필터 페이지 표시 대화상자가 실행되면, '사번'을 선택한 후 [확인] 버튼을 클릭합니다.
    엑셀-VLOOKUP-필터-목록-선택
    필드를 선택한 후, [확인] 버튼을 클릭합니다.
  5. 각 사번으로 나누어진 시트가 한 번에 완성됩니다.
    엑셀-VLOOKUP-자동화-목록-시트-완성
    VLOOKUP 목록으로 사용할 시트가 완성되었습니다.
    오빠두Tip : 실무에 꼭 필요한 피벗테이블의 더 다양한 기능은, 피벗테이블 원데이 마스터 클래스에서 확인하세요!👇

각 항목별 VLOOKUP 보고서 생성하기

  1. VLOOKUP 보고서 서식 만들기 : 이전 단계에서 생성한 VLOOKUP 목록 시트에서, A1셀에 추가되어 잇는 피벗테이블을 모두 삭제합니다. 시트의 시작 셀을 선택한 후, Shift 키를 누른 상태로 목록의 마지막 시트를 클릭하면 연속된 시트가 한 번에 선택됩니다.

    엑셀-연속된-시트-선택
    VLOOKUP 함수에 사용할 목록 시트를 모두 선택합니다.
  2. 시트를 모두 선택한 상태에서, 시트 좌측 상단의 '전체 선택' 버튼을 클릭하고 Delete 키를 눌러 피벗테이블을 모두 제거합니다.

    엑셀-피벗테이블-일괄-삭제
    시트에 추가된 피벗테이블을 모두 제거합니다.
  3. 예제파일에서 [급여 명세서] 시트로 이동합니다. 시트 좌측 상단의 '전체 선택' 버튼을 클릭하여 급여 명세서 시트를 모두 선택한 후, 범위를 복사합니다.

    엑셀-시트-전체-선택
    VLOOKUP 함수 보고서 템플릿을 전체 선택 후, 복사합니다.
  4. VLOOKUP 함수의 목록으로 사용할 시트를 모두 선택한 후, 시트 좌측 상단의 '전체 선택' 버튼을 클릭하고 복사한 범위를 붙여넣기 합니다.

    엑셀-VLOOKUP-자동화-시트-템플릿
    VLOOKUP 목록 시트를 모두 선택한 후, 복사한 범위를 붙여넣기 합니다.
  5. VLOOKUP 함수 일괄 적용하기 : 시트를 모두 선택한 상태에서, 아래에 남겨드린 '시트명 출력' 공식을 복사한 후 VLOOKUP 함수가 사용된 셀에 공식을 붙여넣기합니다.
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

    엑셀-시트-명-불러오기-함수
    VLOOKUP 함수로 검색할 값이 입력된 셀에 시트명 출력 공식을 입력합니다.
  6. 공식을 입력하면, 각 시트이름을 참조하여 VLOOKUP 함수가 적용되면서 각 항목멸 VLOOKUP 보고서가 한 번에 완성됩니다.

    엑셀-VLOOKUP-함수-자동화
    공식을 입력하면 VLOOKUP 보고서가 한 번에 완성됩니다.
  7. 함수를 값으로 변경하기 : 함수가 사용된 보고서를 그대로 공유하면, #REF 오류가 발생하거나 보안상 문제가 생길 수 있으므로 보고서에 사용된 함수를 모두 값으로 변경합니다. 시트를 모두 선택한 후, 전체 범위를 선택하고 복사합니다.

    엑셀-함수-값으로-변경
    오류 및 보안상 문제를 예방하기 위해 보고서에 사용된 함수를 값으로 변경합니다.
  8. 단축키 Ctrl + Alt + V 를 눌러 선택하여 붙여넣기를 실행한 후, '값' 형태로 범위를 붙여넣기 하면 각 항목별 VLOOKUP 보고서가 완성됩니다.

    엑셀-선택하여-붙여넣기-값
    범위를 복사 - 선택하여 붙여넣기에서 값 형태로 붙여넣기 합니다.

VBA 코드로 각 시트를 엑셀파일/PDF로 저장하기

이전 단계에서 생성한 각 항목별 VLOOKUP 보고서는 완성된 VBA 매크로 코드를 복사/붙여넣기 하여 각 시트를 엑셀파일 및 PDF 보고서로 나누어 편리하게 저장할 수 있습니다.

  1. 완성된 VLOOUP 보고서를 모두 선택한 후, 우클릭 - [이동/복사]를 클릭합니다.

    엑셀-시트-이동-복사
    완성된 VLOOKUP 보고서를 모두 선택한 후, 우클릭 - 이동/복사를 선택합니다.
  2. [이동/복사] 대화상자가 실행되면, 새 통합문서로 선택한 시트를 이동합니다.
    엑셀-새-통합문서-시트-옮기기
    새 통합문서로 선택한 시트를 옮깁니다.
    오빠두Tip : 지금 만든 보고서는 일회성이므로, '복사본 만들기'는 체크하지 않아도 괜찮습니다.
  3. 새 통합문서로 선택한 시트를 모두 옮긴 후, 단축키 Alt + F11 를 동시에 눌러 매크로 편집기를 실행합니다.
    엑셀-VBA-편집기-실행
    ALT + F11 을 동시에 눌러 매크로 편집기를 실행합니다.
    오빠두Tip : 만약 Alt + F11 단축키가 동작하지 않을 경우, 리본메뉴를 우클릭 - [리본메뉴 사용자 지정] 에서 '개발도구'를 활성화한 후, 개발도구 탭에서 'Visual Basic' 버튼을 클릭해서 매크로 편집기를 실행합니다.
  4. 매크로 편집기가 실행되면, [삽입] - [모듈]을 클릭하여 새 모듈을 추가한 후, 아래 링크에 남겨드린 VBA 매크로 코드 전문을 복사하여 모듈에 붙여넣기 합니다.
    엑셀-모듈-여러-시트-저장
    삽입 - 모듈로 새 모듈을 추가한 후, 완성된 VBA 코드를 모듈에 붙여넣기합니다.
    Public Enum ePrintMargin: xlNone = 0: xlNarrow = 1: xlNormal = 2: xlWide = 3: End Enum
    Public Enum ePaperSize: xlA4 = 9: xlA3 = 8: xlLetter = 1: xlA5 = 11: End Enum
     
    '-------------------------------------------------
     
    Sub 엑셀파일로저장()
    '여기를 선택 후, F5키를 누르면 각 시트를 나누어 저장합니다.
     
    Dim Path As String: Path = GetFolderPath
    If Path = "" Then MsgBox "올바른 폴더 경로를 선택하세요.": Exit Sub
     
    Save_EachWS Path
     
    MsgBox "파일 저장을 완료했습니다."
     
    End Sub
     
    Sub PDF로저장()
    '여기를 선택 후 F5키를 누르면 각 시트를 PDF 파일로 저장합니다.
     
    Dim Path As String: Path = GetFolderPath: Dim WS As Worksheet
    Dim 우측머리글 As String: Dim 좌측머리글 As String: Dim 좌측바닥글 As String: Dim 우측바닥글 As String
    Dim 가로중앙맞춤 As Boolean: Dim 세로중앙맞춤 As Boolean: Dim 인쇄방향 As XlPageOrientation
    If Path = "" Then MsgBox "올바른 폴더 경로를 선택하세요.": Exit Sub
     
    For Each WS In ThisWorkbook.Worksheets
        '설정영역
        좌측머리글 = WS.Name
        우측머리글 = "&D / &T"
        좌측바닥글 = "본 페이지의 무단복제를 금합니다."
        우측바닥글 = "&P / &N 페이지"
        가로중앙맞춤 = True
        세로중앙맞춤 = True
        인쇄방향 = xlPortrait  '가로방향 : xlLandScape
     
        WS.Activate
        Page_Setup WS, WS.Name, 우측머리글, 좌측바닥글, 우측바닥글, , , 가로중앙맞춤, 세로중앙맞춤, , 인쇄방향
        Rng_To_Pdf WS.UsedRange, WS.Name, Path, , , , False
    Next
     
    MsgBox "PDF 저장을 완료했습니다."
     
    End Sub
     
    '----------------------------------------------------
     
    Sub Save_EachWS(Optional SavePath As String, Optional SheetName As String, Optional isOverWrite As Boolean = False, Optional ExcludeSheets As String)
     
    '###############################################################
    '오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
    '▶ Save_EachWS 함수
    '▶ 각 시트를 각각의 통합문서로 나누어 저장합니다.
    '▶ 인수 설명
    '_____________SavePath      : 시트를 나누어 통합문서로 저장할 폴더경로입니다. 기본값은 실행중인 통합문서와 동일한 경로입니다.
    '_____________isOverWrite   : 덮어쓰기 여부입니다. 기본값은 False 입니다.
    '_____________ExcludeSheets : 시트 나누기에서 제외할 시트입니다. 쉼표로 나누어 여러개 시트를 지정할 수 있습니다.
    '▶ 보조 명령문 안내 : FileExist, FileSequence 함수
    '###############################################################
     
    Dim WS As Worksheet
    Dim fPath As String
    Dim vSheets As Variant: Dim vSheet As Variant
     
    If SavePath = "" Then SavePath = Application.ActiveWorkbook.Path
    If ExcludeSheets <> "" Then vSheets = Split(ExcludeSheets, ",")
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     
    For Each WS In ThisWorkbook.Sheets
        If ExcludeSheets <> "" Then
            For Each vSheet In vSheets
                If WS.Name = Trim(vSheet) Then
                    GoTo Pass:
                End If
            Next
        End If
     
        WS.Copy
        If Right(SavePath, 1) <> "\" Then SavePath = SavePath & "\"
        fPath = SavePath & WS.Name & ".xlsx"
        If FileExists(fPath) And isOverWrite = False Then
            fPath = FileSequence(fPath)
        End If
        Application.ActiveWorkbook.SaveAs FileName:=fPath
        If SheetName <> "" Then Application.ActiveWorkbook.Worksheets(1).Name = SheetName
        Application.ActiveWorkbook.Close True
    Pass:
    Next
     
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
     
    End Sub
     
    Public Function FileExists(ByVal path_ As String) As Boolean
     
    '########################################################
    '입력한 파일경로에 파일 존재여부를 확인합니다.
    'https://www.oppadu.com/vba-fileexists-함수/
    '########################################################
     
        FileExists = (Dir(path_, vbDirectory) <> "")
     
    End Function
     
     
    Private Function FileSequence(FilePath As String, Optional Sequence As Long = 1, Optional Delimiter As String = "-") As String
     
    '###############################################################
    '오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
    '▶ FileSequence 함수
    '▶ 기존 파일이 존재할 경우 순번을 매겨 파일을 저장합니다.
    '▶ 인수 설명
    '_____________FilePath  : 확장자를 포함한 파일 경로입니다.
    '_____________Sequence  : 순번을 매길 시작번호입니다.
    '_____________Delimiter : 파일명과 순번을 구분할 구분자입니다.
    '▶ 보조 명령문 안내 : FileExist 함수
    '###############################################################
     
    Dim Ext As String: Dim Path As String: Dim newPath As String
    Dim Pnt As Long
     
    Pnt = InStrRev(FilePath, ".")
    Path = Left(FilePath, Pnt - 1)
    Ext = Right(FilePath, Len(FilePath) - Pnt + 1)
     
    newPath = Path & Delimiter & Sequence & Ext
     
    Do Until FileExists(newPath) = False
        Sequence = Sequence + 1
        newPath = Path & Delimiter & Sequence & Ext
    Loop
     
    FileSequence = newPath
     
    End Function
     
    Function GetFolderPath()
     
     
    Dim FldrPicker As FileDialog
    Dim fPath As String
     
      Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
     
      With FldrPicker
        .Title = "시트를 저장할 폴더를 선택하세요."
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Function
        fPath = .SelectedItems(1) & "\"
      End With
     
    GetFolderPath = fPath
     
    End Function
     
     
    Public Function GetDesktopPath(Optional BackSlash As Boolean = True)
     
    Dim oWSHShell As Object
     
    Set oWSHShell = CreateObject("WScript.Shell")
     
    If BackSlash = True Then
        GetDesktopPath = oWSHShell.SpecialFolders("Desktop") & "\"
    Else
        GetDesktopPath = oWSHShell.SpecialFolders("Desktop")
    End If
     
    Set oWSHShell = Nothing
     
    End Function
     
     
    Function ValidFileName(ByVal FileName As String) As Boolean
     
    Dim Arr As Variant: Dim Val As Variant
    Dim Pnt As Long
     
    Arr = Array("/", "\", ":", "*", "?", """", "<", ">", "|")
     
    If InStr(1, FileName, ":\") > 0 Then
        Pnt = InStrRev(FileName, "\")
        FileName = Right(FileName, Len(FileName) - Pnt)
    End If
     
    ValidFileName = True
     
    For Each Val In Arr
        If InStr(1, FileName, Val) > 0 Then ValidFileName = False: Exit Function
    Next
     
    End Function
     
    Function getPrintMargin(eValue As ePrintMargin) As Variant
     
    '// 설정된 eNum 값으로 페이지 여백설정을 위한 값을 배열로 나열합니다.
     
    Select Case eValue
    Case 0
    getPrintMargin = Array(0.05, 0.05, 0.05, 0.05, 0.1, 0.1)
    Case 1
    getPrintMargin = Array(0.25, 0.25, 0.75, 0.75, 0.3, 0.3)
    Case 2
    getPrintMargin = Array(0.7, 0.7, 0.75, 0.75, 0.3, 0.3)
    Case 3
    getPrintMargin = Array(1, 1, 1, 1, 0.5, 0.5)
    End Select
     
    End Function
     
    Sub Page_Setup(WS As Worksheet, Optional LHead As String = "", Optional RHead As String = "&D / &T", _
    Optional LFoot As String = "본 페이지의 무단복제를 금합니다.", Optional RFoot As String = "&P / &N 페이지", _
    Optional eMargin As ePrintMargin = xlNarrow, _
    Optional HFit As Boolean = True, Optional VFit As Boolean = False, _
    Optional HCenter As Boolean = True, Optional VCenter As Boolean = False, _
    Optional eOrient As XlPageOrientation = xlPortrait, Optional eSize As ePaperSize = xlA4)
     
    Dim pSetup As String
    Dim varMargin As Variant
    Dim lngOrient As Integer
     
    '// 인쇄설정 업데이트 중단 (속도증가)
    Application.PrintCommunication = False
     
    '// 인쇄여백값을 받아옵니다.
    varMargin = getPrintMargin(eMargin)
     
    '// 인쇄용지 방향을 설정합니다.
    If eOrient = xlPortrait Then
    lngOrient = 1
    Else
    lngOrient = 2
    End If
     
    '// ExecuteExcel4Macro 의 Page.Setup 명령문 실행을 위한 문구를 입력합니다.
    Head = """&L" & LHead & "&R" & RHead & """"     '// 페이지 머릿말입니다.
    Foot = """&L" & LFoot & "&R" & RFoot & """"     '// 페이지 꼬릿말입니다.
    pLeft = varMargin(0)                            '// 왼쪽여백
    pRight = varMargin(1)                           '// 오른쪽여백
    Top = varMargin(2)                              '// 윗여백
    Bot = varMargin(3)                              '// 아래여백
    Head_margin = varMargin(4)                      '// 머릿말여백
    Foot_margin = varMargin(5)                      '// 꼬릿말여백
    Hdng = 0                                        '// 행/열반복 출력여부 0 = 반복출력안함 1 = 반복출력
    Grid = False                                    '// 눈금선출력여부
    Notes = False                                   '// 메모출력여부
    H_cntr = HCenter                                '// 가운데정렬
    V_cntr = VCenter                                '// 중앙정렬
    Orient = lngOrient                              '// 문서방향, 1 = 세로 2 = 가로
    Paper_size = eSize                              '// 용지크기
    Pg_num = 1                                      '// 페이지 시작번호
    Pg_order = 1                                    '// 페이지번호 순서, 1 = 위-아래-우 2 = 좌-우-아래
    Quality = ""                                    '// 인쇄품질 (dot-per-inch로 입력) (공백 = 자동)
    bw_cells = False                                '// 흑백인쇄여부, TRUE = 글자/테두리 검정,배경 흰색 FALSE = 색깔
    pScale = 100                                    '// 축소/확대비율 또는 TRUE (Fit to Page)
     
    '// 여백을 없음으로 설정할 경우 머릿말/꼬릿말을 삭제하여 인쇄영역과 겹치지 않도록 합니다.
    If eMargin = xlNone Then
    Head = """"""
    Foot = """"""
    End If
     
     
    '// ExecuteExcel4Macro 명령문을 실행합니다.
    pSetup = "PAGE.SETUP(" & Head & ", " & Foot & ", " & pLeft & ", " & pRight & ", " & Top & ", " & Bot & ", "
    pSetup = pSetup & Hdng & ", " & Grid & "," & H_cntr & "," & V_cntr & "," & Orient & ","
    pSetup = pSetup & Paper_size & "," & pScale & ","
    pSetup = pSetup & Pg_num & "," & Pg_order & "," & bw_cells & "," & Quality & ","
    pSetup = pSetup & Head_margin & "," & Foot_margin & "," & Notes & ")"
     
     
    Application.ExecuteExcel4Macro pSetup
     
    '// ExecuteExcel4Macro에서는 '한 페이지에 행/열 맞추기' 기능이 지원되지 않습니다.
    '// 따라서 시트의 PageSetup 속성으로 '페이지 행/열 맞추기 기능을 설정합니다.
    With WS.PageSetup
    If HFit = True Then
    .FitToPagesWide = 1
    Else
    .FitToPagesWide = False
    End If
     
    If VFit = True Then
    .FitToPagesTall = 1
    Else
    .FitToPagesTall = False
    End If
    End With
     
    '// 인쇄설정 업데이트
    Application.PrintCommunication = True
     
    End Sub
     
    '####################################################
    '모듈을 추가한 뒤 복사/붙여넣기 하세요.
    '####################################################
     
    Sub Rng_To_Pdf(rngSelect As Range, _
                    Optional FileName As String = "pdf출력", _
                    Optional SavePath As String = "", _
                    Optional DocProperty As Boolean = True, _
                    Optional PrintArea As Boolean = False, _
                    Optional OpenPdf As Boolean = False, _
                    Optional AddSequence As Boolean = True)
     
    Dim WS As Worksheet
    Dim FilePath As String
     
    Set WS = rngSelect.Parent
     
    If SavePath = "" Then SavePath = GetDesktopPath
     
    FilePath = SavePath & FileName & ".pdf"
     
    If ValidFileName(FilePath) = False Then MsgBox ("올바른 파일명을 사용하세요"): Exit Sub
     
    If AddSequence = True Then
        FilePath = FileSequence(FilePath, 1)
    End If
     
    rngSelect.ExportAsFixedFormat xlTypePDF, FilePath, xlQualityStandard, DocProperty, PrintArea, , , OpenPdf
     
    End Sub
  5. 스크롤을 위로 올려서 매크로 시작 부분으로 이동한 후, 원하는 출력 방식에 따라 '엑셀 파일로 저장' 또는 'PDF로 저장' 명령문을 선택합니다. 이후 매크로 편집기 상단의 재생버튼을 클릭하거나, 단축키 F5를 눌러 매크로를 실행합니다.

    엑셀-매크로-실행
    엑셀 파일로 저장 또는 PDF로 저장을 선택 후 매크로를 실행합니다.
  6. 폴더 선택창이 나오면, 각 시트를 엑셀 파일 또는 PDF로 나누어 저장할 폴더를 선택 후 [확인] 버튼을 클릭합니다.

    엑셀-폴더-선택-VBA
    폴더 선택창이 나오면 파일을 저장할 폴더 경로를 선택합니다.
  7. 잠시만 기다리면, 안내창이 나오면서 엑셀 또는 PDF 파일이 선택한 폴더에 저장된 것을 확인할 수 있습니다.

    엑셀-PDF-저장-완료
    VLOOKUP 자동화 보고서가 엑셀 또는 PDF 파일로 일괄 저장됩니다.
4.7 14 투표
게시글평점
24 댓글
Inline Feedbacks
모든 댓글 보기
24
0
여러분의 생각을 댓글로 남겨주세요.x