엑셀에서는 워크스프레드 프로그램에 문서형식으로 작성하는 많은 기능이 있습니다.

그 중에 하나가 그림을 삽입하거나 도형을 그리는 기능입니다.

그런데 그림을 삽입해서 문서를 편집하다보면 그림이 변하는 것을 볼 수 있습니다.

주로 셀을 삽입하거나 삭제하는 기능에 따라서 이동하거나 커지거나 작아집니다.

이런 기능은 문서의 편집과 함께 하는 것이라 보통은 좋은 기능입니다.

하지만 항상 편한건 아닙니다.

원하지 않을 때를 대비한 도형 속성을 변경해서 크기와 위치를 고정하는 기능을 소개합니다.

도형을 여러개 동시에 선택해서 할 수도 있지만, 기본적으로는 매번 설정해주어야 합니다.

[오른쪽 클릭] - [도형서식] - [도형 서식 / 개체 서식] - [도형 옵션] - [크기 및 속성] - [변하지 않음]을 선택합니다.

위치만 변함을 선택하면 크기는 변하지 않고 위치는 변합니다.

일단 이렇게 잠그고 나면 도형의 위치는 고정됩니다.

이게 좀 불편하게 일일히 설정해야 하는 것이 있습니다.

하지만 문서에 일괄적으로 들어가는 회사로그 같은 것에는 설정을 해둘 수도 있겠죠.

엑셀에서 좀 디폴트 옵션을 변하게 하는 시대가 빨리 왔으면 좋겠습니다.

엑셀 VBA를 작성하다보면 이 결과물이 어디에 쓰일지 다 예상 못할 수도 있습니다.

중요한 것은 DATA의 수와 끝을 파악하는 것이고요.

VAB에서 많이 사용하는 내용의 끝을 파악하는 방법에 대해서 알아 보겠습니다.

※ 엑셀 인터페이스에서 쓰는 Find 함수와 VBA에서 사용하는 Find 매소드는 별개임을 알려드립니다.

① B 행에서 내용이 있는 마지막 셀 찾기

find를 사용할 껀데 먼저 코드를 보도록 하겠습니다.

Sub FindLastColumn()

    Dim ws As Worksheet
    Dim lastCell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet5")
    
    'find를 사용해서 마지막 값을 찾음
    Set lastCell = ws.Columns("B").Find(What:="*", _
                                        After:=ws.Range("B1"), _
                                        LookIn:=xlFormulas, _
                                        LookAt:=xlPart, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlPrevious)
    
    If Not lastCell Is Nothing Then
        MsgBox "B행의 마지막 데이터는 " & lastCell.Address & "에 있습니다."
    Else
        MsgBox "B행에 데이터가 없습니다."
    End If
End Sub

Find의 사용법은 위 코드를 보면 알 수 있고 각 매개변수에 대해서 설명하겠습니다.

• What : 찾을 값으로 * 을 사용하였음으로 "모든 값"을 검색합니다.
• After : 검색을 시작할 위치입니다. 여기서 부터라고 보면 됩니다.
• LookIn : 검색할 기준입니다. 이 경우에는 수식으로 검색하는 것이 좋습니다.
      - xlFormulas : 수식을 기준으로 검색하는데 수식의 결과가 아닌, 수식 자체를 검색합니다. 
      - xlValues : 값을 검색합니다. 이 경우에는 수식의 결과를 기준으로 검색합니다.
      - xlComments : 셀에 추가된 주석을 검색합니다.
      - xlCommentsThreaded : Threaded Comments라는 Office365 버전부터 추가된 새로운 주석을 검색합니다.
• LookAt : (xlPart : 부분일치), (xlWhole : 전체일치)중 하나를 선택합니다.
• SearchOrder : 검색하는 영역입니다. (xlByRows : 행 기준), (xlByColumns : 열기준)
• SearchDirection : 검색하는 방향입니다. (xlNext : 앞에서 뒤로), (xlPrevious : 뒤에서 앞으로)
• (MatchCase) : 검색할 때 대소문자를 구별할지 선택합니다. 디폴트는 False(구별 안함)입니다.
• (MatchByte) : 더블 바이트 언어를 설치했을 때 사용합니다.
      - True : 더블 바이트 문자가 정확하게 더블 바이트와 일치할 경우만 검색
      - False : 더블 바이트 문자가 싱글 바이트와 일치해도 검색함
• (SearchFormat) : 검색 형식입니다.

즉 위의 코드는 ws.Columns("B") 로 범위를 제한해서,

B1부터 시작하여 해당 행(xlByRows)에서 뒤에서 앞으로(xlPrevious) 검색하여 처음 만나는 값의 위치를 찾습니다.

② 셀에서 마지막 범위 찾기

거의 같습니다. 똑같이 Find를 사용합니다.

Sub FindLastCell()
    Dim ws As Worksheet
    Dim lastCell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet5")
    
    ' 마지막 데이터를 찾음
    Set lastCell = ws.Cells.Find(What:="*", _
                                 After:=ws.Cells(1, 1), _
                                 LookIn:=xlFormulas, _
                                 LookAt:=xlPart, _
                                 SearchOrder:=xlByRows, _
                                 SearchDirection:=xlPrevious)
    
    If Not lastCell Is Nothing Then
        MsgBox "워크시트의 마지막 데이터는 " & lastCell.Address & "에 있습니다."
    Else
        MsgBox "워크시트에 데이터가 없습니다."
    End If
End Sub

처음나온 코드에서 범위를 행으로 제한했던 ws.Columns("B")를 전체 대상의 ws.Cells로 변경한 것입니다.

SearchOrder는 "xlByRows"로 하면 가장 아래 값을 끝으로 "xlByColumns"를 하면 가장 오른쪽에 있는 셀을 선택합니다.

③ 처음부터 끝까지 선택하기

이제 응용편입니다.

끝의 셀을 선택한다면 처음셀로 찾을 수 있죠.

두 셀을 찾아서 전체 선택을 하는 코드입니다.

이 것 역시 간간히 쓰는데, 그때그때 작성하려니 실수가 많아 오늘 기회로 정리합니다.

Sub SelectallContentCell()
    Dim ws As Worksheet
    Dim firstCell As Range
    Dim dataRange As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet5")
    
    ' 처음 셀 찾기
    Set firstCell = ws.Cells.Find(What:="*", _
                                  After:=ws.Cells(1, 1), _
                                  LookIn:=xlFormulas, _
                                  LookAt:=xlPart, _
                                  SearchOrder:=xlByRows, _
                                  SearchDirection:=xlNext)
    
    ' 마지막 셀 찾기
    Set lastCell = ws.Cells.Find(What:="*", _
                                 After:=ws.Cells(1, 1), _
                                 LookIn:=xlFormulas, _
                                 LookAt:=xlPart, _
                                 SearchOrder:=xlByRows, _
                                 SearchDirection:=xlPrevious)
            
        Set dataRange = ws.Range(firstCell, lastCell)
        dataRange.Select
    
    If Not firstCell Is Nothing Then
        MsgBox "선택된 범위는 " & dataRange.Address
    Else
        MsgBox "워크시트에 데이터가 없습니다."
    End If
End Sub

결과입니다.

셀도 선택을 잘하고 매세지도 잘 출력됩니다.

< 목차 >

1. 끝으로 이동하기

2. 끝까지 선택하기

많은 데이터가 있는 표를 위에서 작업하다 보면 처음과 마지막으로 이동할 일이 많습니다.

엑셀에서는 그럴 때 사용하도록 Ctrl + 방향키(←,→,↑,↓)를 눌러서 끝에서 끝까지 이동할 수 있습니다.

무슨 게임하는 것 같아서 처음에는 익숙해지지 않았을 때는 마우스에 많이 의존하지만,

일단 적응되면 손에서 놓을 수가 없습니다.

1. 끝으로 이동하기

VBA에서도 이 기능을 수행하는 명령어가 있습니다.

• xlDown : 시작 위치에서 아래방향으로 데이터가 끝나는 셀로 바로 이동합니다. 
• xlUp  : 시작 위치에서 위 방향으로 데이터가 끝나는 셀로 바로 이동합니다. 
• xltoRight : 시작 위치에서 오른쪽 방향으로 데이터가 끝나는 셀로 바로 이동합니다. 
• xltoLeft  : 시작 위치에서 왼쪽 방향으로 데이터가 끝나는 셀로 바로 이동합니다. 
• 시작 위치가 데이터가 없는 빈 셀일 경우에는 반대로 처음 데이터가 있는 셀로 이동합니다.
• 빈 셀인 경우 그 방향에 데이터가 하나도 없으면 끝까지 이동합니다.

사용하는 방법은 아래와 같습니다.

간단함으로 모두 묶어서 하나의 소스에 담아서 설명하겠습니다.

Sub MoveCell()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ws.Range("B28").End(xlDown).Select '아래 방향으로
    ws.Range("B28").End(xlUp).Select '위 방향으로
    ws.Range("B28").End(xltoRight).Select '오른쪽 방향
    ws.Range("B28").End(xltoLeft).Select '왼쪽 방향으로

End Sub

보시면 이해될 것이라고 생각합니다.

셀 B28 에서 아래, 위, 오른쪽, 왼쪽으로 이동하는 소스입니다.

2. 끝까지 선택하기

위에 처럼 이동하는 것으로도 편리하지만 전체 선택하기라는 편리한 기능도 있습니다.

일반 엑셀 인터페이스에서는 Ctrl + Shift + 방향키(←,→,↑,↓) 입니다.

이 기능을 수행하는 코드도 설명 드리겠습니다.

소스로 따지면 약간 길어지는 합니다.

하지만 어렵지는 않으니 사용해 보면 바로 알 수 있을 것 같습니다.

Sub SelectRange()

    Dim ws As Worksheet
    Dim dataRange As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' A1부터 아래 방향으로 데이터가 있는 마지막 셀까지 범위 선택
    Set dataRange = ws.Range("A1", ws.Range("A1").End(xlDown))
    dataRange.Select

    ' A1부터 오른쪽 방향으로 데이터가 있는 마지막 셀까지 범위 선택
    Set dataRange = ws.Range("A1", ws.Range("A1").End(xlToRight))
    dataRange.Select

    ' A1에서 시작해 데이터가 있는 끝까지 범위를 설정
    Set dataRange = ws.Range("A1", ws.Range("A1").End(xlToRight).End(xlDown))
    dataRange.Select
    
End Sub

굉장히 자주 사용하는 코드로 사용자가 입력해서 계속 길어지는 엑셀에 VBA를 적용할 때 사용하기도 합니다.

잘 사용하면 순환문 하나 정도 뺄 수도 있게 만드는 편리한 코드입니다.

저도 이렇게 포스팅 해놓고 수시로 보러고 작성하는 것이랍니다.

보시는 분께도 참고가 되었으면 좋겠습니다.

엑셀의 내용은 행과 열의 표로 이루어져 있습니다.

하지만 그 안에도 "표 - Table"라는 영역을 다시 만들 수 있다는 것을 알고 계신가요?

자동서식을 사용할 수도 있고 표 전체에 대한 필터 기능도 사용할 수 있습니다.

이 필터를 슬라이서라고하는데 빠르고 편리합니다. 장점을 좀 알아보면,

• 직관적이고 사용하기가 쉽습니다.
• 필터 기능임에도 한 문서에 서로 다른 범위를 표로 지정해 여러개 설치할 수 있습니다.
• 슬라이서를 만든 후에 표를 편집하면 슬라이서에도 자동으로 반영됩니다.

슬라이서를 만들고 사용하는 것에 대해서 알아보겠습니다.

① 범위를 먼저 표로 만들기

범위를 지정하고 상단매뉴의 [홈] - [스타일] - [표서식]을 찾습니다.

의무적으로 표서식을 넣어야 하지만 저는 지금에 표의 스타일을 변경하기 싫습니다.

그래서 "표 스타일 새로 만들기"를 선택하고 아무것도 하지 않고 확인을 누릅니다.

그럼 사용자 지정 - 표 스타일1이 생기게 되고 선택하면 표로 만들 범위를 물어봅니다.

선택한 영역이 "표 만들기"에 들어가 있으니 이상이 없으면 확인을 눌러서 표로 만들면 됩니다.

② 슬라이서 삽입하기

이제 지정한 범위가 "표 - Tabel"이 되었습니다.

여기에 커서를 올려두면 상당 매뉴에 테이블 디자인이 활성화 됩니다.

이 항목 안에 [테이블 디자인] - [외부 표 데이터] - [슬라이서 삽입]을 선택하면 됩니다.

③ 슬라이서를 설정합니다.

"슬라이서 삽입 버튼"을 클릭하면 머리말을 고르는 매뉴가 활성화 됩니다.

"시도별"을 골라서 활성화 시켰더니 그에 맞는 슬라이더가 생성되었습니다.

④ 슬라이서를 사용해 봅시다.

이제 드디어 오늘의 목적인 슬라이서를 사용할 차례입니다.

슬라이서는 필터처럼 사용할 수 있습니다.

그리고 Ctrl 키를 이용해서 여러개를 선택할 수도 있고 두개의 슬라이서를 적용할 수도 있습니다.

아래 GIF - 움짤을 보면 사용법을 알 수 있을 거라 생각합니다.

자주 사용하는 파일에는 이것만큼 편리한 필터기능이 잘 없습니다.

엑셀으로 많은 데이터를 처리하시는 분들에게는 슬라이서 기능에 익숙해 지실 것을 추천합니다.

엑셀에서는 행과 열을 나누어 데이터를 표로 만드는데 특화된 스프레드 워크시트를 제공합니다.

이 시트를 한 파일에 255개까지 제공함으로 데이터 작성에 폭이 넓어 집니다.

아직 한번도 이걸 다 채워보지는 못했지만 40~50개 정도의 시트를 사용한 적은 있습니다.

그럼 하단의 워크시트 목록을 좌로 우로 움직이면서 필요한 시트를 한참 찾았던 기억이 있습니다.

정말 찾기 어렵더라고요.

아직 엑셀에서 명료하고 편리한 검색기능은 지원하고 있지 않습니다.

다만 그래도 조금은 편한 방법을 소개해 봅니다.

① 활성화 매뉴 불러서 이동하기

하단의 시트 목록 좌측에서는 "<  >" 처럼 생긴 화살표가 있습니다.

처음에는 비활성화 되어 있다가, 시트의 수가 한눈에 볼수 없을 만큼 길어지면 활성화 됩니다.

그리고 활성화 시킨 상태에서는 오른쪽 클릭하면 시트를 목록으로 볼 수 있습니다.

원하는 시트를 더블클릭하면 바로 이동할 수 있습니다.

정말 시트가 많을 때 이 기능으로 충분합니다.

② 하이퍼링크 사용하기

셀을 클릭하고 오른쪽 클릭을 해도되고 [상단매뉴] - [삽입] - [링크]를 클리하여 하이퍼 링크를 설정할 수 있습니다. 

여기서 "현재 문서"를 선택하여 시트 명을 누릅니다.

하이퍼링크 만들기로 시트리스트에 참조를 걸어두면 편리하게 사용할 수 있습니다.

시트가 수십개인 대형 엑셀파일을 아무리 쉽게 생각해도 간단하게 만든 것은 아닐 껍니다.

처음부터 천천히 이 표를 만들어 둔다면 만드는데 무리가 아닐껍니다.

③ 시트 함수 활용하기

이건 정확하게 이동하거나하는 건 아니고 시트를 관리하기 위해사용하는 함수입니다.

참고용이라고 보면 되겠네요.

office365 이상의 최신번전에서 동작합니다. 

Sheets() : 이 문서의 시트 총 갯수를 반환합니다. 여러명이 작성한 문서라 시트 수가 명확하지 않을 때 좋습니다.

Sheet([value - refer]) : 이 함수가 참조하고 있는 셀에 시트 번호를 호출합니다. 시트 번호는 매크로를 작성할 때 사용하면 편리한 값으로 VBA를 쓰는 경우 추천합니다.

엑셀을 사용해서 파일정리를 하고는 합니다.

사실 인터넷을 검색하면 저보다 잘 만드는 분이 많고 배포한 것도 있으니 그 프로그램을 사용하면 대부분 해결됩니다.

대량의 파일을 취급하다 보면 나만의 상황을 만나기 쉽고, 그럴 때를 위해 사용하는 이름 불러오기를 소개합니다.

저는 특히 폴더 안의 폴더 안의 폴더의 파일도 뽑아야 하는 경우가 있어요.

< 예제 파일 >

예제 파일을 먼저드립니다.

경로명 옆에 폴더 이름을 적고 실행 버튼을 누르기만 하면 되기 때문에 사용은 쉬울 것 같습니다.

< VBA 소스 코드 >

VBA는 File과 Folder를 호출하면 거의 필요한 대부분의 정보를 Windows에서 받아옵니다.

그래서 코드의 구조는 간단합니다.

Sub All_File_List()

    ' 폴더 경로를 지정합니다.
    Dim folderPath As String
    Dim ws As Worksheet
    Dim nextRow As Long
        
    folderPath = ThisWorkbook.Sheets(1).Cells(2, 3).Value '① 경로명을 불러옵니다.
    Set ws = ThisWorkbook.Sheets(1) '② 데이터를 넣을 시트를 지정합니다.
    
    '③ 먼저 실행한 이름들을 다 지워줍니다 + 필요한 내용을 입력합니다.
    
    ThisWorkbook.Sheets(1).Range("C5", Range("E5").End(xlDown)).Select
    Selection.Clear
    
    ThisWorkbook.Sheets(1).Range("C5") = "경로명"
    ThisWorkbook.Sheets(1).Range("D5") = "파일이름"
    ThisWorkbook.Sheets(1).Range("E5") = "용량"
    
    nextRow = 1
    
    '④ 이름을 불러오는 함수를 호출합니다.
    Call ListFiles(folderPath, ws, nextRow)

End Sub

Sub ListFiles(folderPath As String, ws As Worksheet, ByRef nextRow As Long)

    Dim folder As Object
    Dim file As Object
    Dim FSO As Object
    
    '⑤ FSO에 파일 시스템 객체를 불러내고 변수명 Folder에 정보를 입력합니다.
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folder = FSO.GetFolder(folderPath)

    '⑥ 폴더에 파일을 불러내서 한줄씩 씁니다.
    For Each file In folder.Files
        ws.Cells(nextRow + 5, 3).Value = file.ParentFolder.Path ' A열에 파일 경로
        ws.Cells(nextRow + 5, 4).Value = file.Name ' B열에 파일 이름
        ws.Cells(nextRow + 5, 5).Value = Format(file.Size / 1024, "#,##0") & " KB" ' C열에 파일 크기 (KB로 변환)
        nextRow = nextRow + 1
    Next file

    '⑦ 하위 폴더의 숫자만큼 다시 재귀적으로 호출합니다.
    Dim subfolder As Object
    For Each subfolder In folder.SubFolders
        Call ListFiles(subfolder.Path, ws, nextRow)
    Next subfolder

End Sub

경로명을 쓰는 C2 칸에 작성하면 그 경로 아래 모든 파일을 찾아서 리스트로 만들어 줍니다.

경로와 폴더 그리고 용량까지 만들어 봤습니다.

< 코드의 동작순서 >

VBA 메크로가 동작하는 순서는 아래와 같습니다.

① 경로명을 불러옵니다.

② 데이터를 넣을 시트를 지정합니다.

③ 먼저 실행한 이름들을 다 지워줍니다 + 필요한 내용을 입력합니다.

④ 이름을 불러오는 함수를 호출합니다. - ListFiles(경로명, 작업할 워크시트, 줄정보)

⑤ 변수명 "FSO"에 파일 시스템 객체를 불러내고 변수명 "Folder"에 정보를 입력합니다.

⑥ 폴더에 파일을 불러내서 한줄씩 씁니다.

⑦ 하위 폴더의 숫자만큼 다시 재귀적으로 호출합니다.

   여기가 오늘의 핵심 재귀 호출입니다.

   → 폴더 아래 있는 파일을 다 적고,

   → 서브 폴더를 찾습니다. 서브폴더가 있으면 서브폴더 이름으로 다시 함수를 호출합니다.

   → 더 서브 폴더가 없으면 다시 For문으로 돌아와서 끝났는지 확인합니다.

이런 식으로 하기 때문에 폴더명을 불러오는 순서는 아래와 같습니다.

엑셀은 칸칸으로 만들어진 값들의 집합입니다.

화면에서 여러개의 칸에 값을 입력하다 보면 금방 한페이지가 차는데요.

아무것도 안 만진 초기 설정으로 행은 8칸, 열은 43칸이 A4 한 장 분량입니다.

직접 쓰다보면 금방 다 찹니다.(특히 가로는 엄청 빨리 차요)

그런데 인쇄를 나누어하면 보기가 싫어서 한페이지 하려고 많이 노력합니다.

오늘은 한 페이지에 출력하는 방법들을 알아 보겠습니다.

① 페이지 설정에서 여백과 배율 조정하기

인쇄를 할 때 여러가지 설정을 거의 "페이지 설정" 에서 다 할 수 있습니다.

인쇄 창 혹은 [상단 배뉴바] - [페이지 레이아웃] - [오른쪽 아래 ↘ 버튼]을 클릭합니다.

"확대/축소 배율"에서는 작업할 때는 똑같이 보이지만, 인쇄할때는 작거나 크게 변경할 수 있습니다.

"여백"에서는 말그대로 여백을 조절할 수 있습니다.

여백을 너무 작게 만들면 프린터에서 실제로는 인쇄를 할 때 짤리거나 번질 수도 있습니다.

또 종이를 보관할 때 파일할 공간을 조금 남겨놔야 하는 부분을 고려해서 설정하도록 합시다.

② 자동 맞춤하기

위의 "페이지 설정"에서 너무 "배율" 항목에 "자동 맞춤"이 있습니다.

자동 맞춤을 통해서 1페이지에 들어가게 하면 자동으로 확대/축소 배율을 맞추어 줍니다.

다만 자동이다 보니 때때로 너무 과하게 배율이 들어가서 오히려 보기 싫어지는 경우가 있습니다.

이걸 좀 조심할 필요가 있습니다. 예제의 문서는 92%로 자동이라도 깔끔하게 되었습니다.

보기가 싫으면 수동으로 여백과 배율을 정밀하게 조정해서 마무리 하셔도 됩니다.

위의 예시에서 보면 자동 맞춤을 설정하면 한페이지에 맞춰집니다.

그런 후 페이지 설정에 다시 들어가면 92%로 변경된 것을 알 수 있습니다.

③ 페이지 나누어 미리 보기에서 조정하기

[상단 매뉴] - [보기] - [통합 문서 보기] - [페이지 나누어 미리 보기]를 선택하면 엑셀의 창이 변경됩니다.

페이지 나누어 미리보기 인터페이스에서는 인쇄 경계선이 파란색으로 표시되는데 이걸 움직일 수 있습니다.

움직여서 원하는 범위를 설정하면 됩니다.

이 상태에서 인쇄가 되는 영역은 컬러 안되는 영역은 회색조로 나누어 구별합니다.

이걸 참고해가면서 범위를 조정하면 됩니다.

그외에도 이 인터페이스 상태에서 셀을 범위로 선택한 뒤에 마우스 오른쪽 클릭하면 추가 기능이 활성화 됩니다.

• 페이지 나누기 삽입 : 선택된 구간부터 새로운 페이지로 나눕니다.
• 페이지 나누기 모두 원래대로 : 사용자가 설정한 것을 취소합니다. 처음의 자동설정으로 돌아갑니다.
• 인쇄 영역 설정 : 선택된 곳만 인쇄하고 나머지는 비 인쇄 상태로 만듭니다.
• 인쇄 영역에 추가 : 지금 선택된 곳에서 추가로 여기까지 선택합니다. 복잡한 경우 매번 선택한 영역을 새로운 페이지로 설정합니다.
• 페이지 설정 : "페이지 설정 " 매뉴를 오픈합니다.

한 페이지에 인쇄하는 것은 중요한 옵션입니다.

엑셀의 자료를 회의에 사용하거나 출력해가서 어딘가에서 보면서 작업을 할 때 많이 활용됩니다.

오늘 포스팅이 많이 도움되었으면 좋겠습니다.

엑셀의 표를 예쁘게 보기 좋게 정리하려면 병합기능이 필요할 때가 많습니다.

같은 글자를 여러개 쓰는 것보다는 중앙에 잘 정렬된 경우에 많이 사용합니다. 

하지만 여러 셀을 선택하는 경우 아래와 같은 상황이 됩니다.

병합된 셀을 포합해서 선택할 때 하나만 선택되지 않습니다.

드래그해서 선택할 때 혹은 "Ctrl + SPACE : 행선택", "Shift + SPACE : 열선택" 단축키를 사용할 때 발생합니다.

이것도 무시하면 무시가능하기도 하지만 어쩔 때는 조금 짜증나기도 합니다.

이 상황을 피할 수 있는 기능을 소개합니다.

< 선택 영역의 가운데로 사용하기 >

기능의 사용법은 간단합니다.

① 해당 범위를 선택하고 

② 오른쪽 클릭 또는 단축키 Ctrl + 1를 사용해서 셀 서식을 오픈한다.

③ 위치는 [셀 서식] - [맞춤] 탭 - [텍스트 맞춤] - 드롭 박스 : 선택영역의 가운데로 를 선택하면 됩니다.

안타깝지만 현재로서는 가로만 지원하는 기능입니다.

그래도 알고 있으면 상당히 응용할 때가 많습니다.

적용결과를 보겠습니다.

어떠신가요 보기만 해도 시원하지 않나요?

< 선택 영역의 가운데로 제약사항 >

이 좋은 기능에도 약간 제약은 있습니다.

① 다른 칸에 내용이 들어가면 안됩니다.

    정확하게는 빈 칸위주로 다시 정렬하지만 이건 셀 병합도 못하는 거니 큰 문제는 안됩니다.

② 셀 색과 선을 각각 입력해야 합니다.

    이 경우 색을 따로 따로 입력하는건 장점이 될 수도 있고요. 대각선을 못 긋는 사소한 단점이 있습니다.

큰 문제는 아니라고 봅니다.

잘 알려지지 않아 자주 사용하는 기능은 아니지만, 알면 편리한 기능이라고 생각합니다.

가로만 적용할 것이 아니라 하루 빨리 세로에도 적용해주는 날이 왔으면 좋겠습니다.

마무리로 처음 사용했던 예시에 적용해 보았습니다.

어떠신가요? 저는 이 기능을 몰랐을 때는 상당히 많이 고생했습니다.

알고 있는 지금도 남이 쓰는 엑셀 파일은 어쩔 수 없어서 일일히 병합을 풀어서 사용하지만요.

엑셀 조회함수 Vlookup과 Hlookup으로 필요한 DATA를 찾자

VLOOKUP은 조회 부분에서는 가장 좋은 함수는 아니지만 가장 많이 쓰는 함수가 아닐까 합니다.

그만큼 다양한 에러를 지원하고 있습니다.

오늘은 VLOOKUP을 사용하다가 발생하는 에러의 여러가지 원인을 알아보겠습니다.

① #N/A 에러

이 에러는 찾는 값이 없을 때 발생됩니다.

VLOOKUP은 첫번째 열에서 찾는 값을 검색하지만 여기에 없는 경우 발생하는 에러입니다.

꺼꾸로 생각하면 "찾는 값이 없다"는 사실도 정보이기는 합니다.

하지만 의도한 결과가 아닌 경우 오타등을 확인해야 합니다.

위에처럼 검색을 "서울"로 했지만 목록에서는 "서울특별시"라고 검색했기 때문에 정확하지 않아 못찾습니다.

이 에러는 VLOOKUP의 네번째 변수를 TRUE로 할 경우 발생하지 않습니다.

② #REF! 에러

VLOOKUP에서 지정한 테이블의 범위를 넘어갈때 발생합니다.

테이블의 열이 10개 뿐이지만 11을 입력하면 발생하는 에러입니다.

이 에러가 발생하면 숫자를 정확하게 입력했는지와 테이블의 범위를 잘 설정했는지 봐야 합니다.

위의 표처럼 테이블에 14번째가 없는데 찾으라고 하면 발생하는 숫자입니다.

③ 표시 형식이 입력되어 있는 경우

VLOOKUP만이 아니라 모든 조회함수가 공통적으로 가지고 가는 문제입니다.

엑셀에서는 [셀 서식] - [표시형식] - [사용자 지정]에서 보이는 형식을 변경할 수 있습니다.

함수는 변경한 형식이 아니라 입력한 값을 검색합니다.

@ "시" 라고 하면 모든 텍스트 뒤에 "시"라는 글자가 들어갑니다.

그럼 조회함수에서는 이 "시"를 포함해서는 검색이 안됩니다.

"입력한 그 글자를 검색하는 것"이 모든 조회함수의 기본입니다.

④ 병합될 셀인 경우

또 엑셀 조회함수의 약점인데 셀이 병합된 경우 첫번째 셀에 글자가 들어가고 나머지는 공백처리됩니다.

그래서 조회하는 테이블에 병합된 셀은 가능하면 없는게 좋습니다.

평소 작성할 때 검색할 데이터에는 병합을 사용하지 않는게 버릇이 되어 있으면 좋습니다.

병합된 셀은 아무리 커도 맨 처음에 한 칸에만 글자가 들어간 것으로 처리됩니다.

⑤ 찾는 값이 범위에 두개 이상 있는 경우

찾는 값이 두 개 이상 있는 경우에는 무조건 위, 왼쪽의 첫번째 값을 찾습니다.

VLOOKUP을 사용할 때는 맨 처음 열은 고유키 같이 하나만 있는 값을 사용할 것을 추천합니다.

데이터를 만들 때 학생들의 이름은 겹칠 수 있으니, 학번이나 주민 번호를 사용하는 것과 같이요.

그게 아니고 두 개 이상의 값을 모두 다루고 싶으면 좀 복잡한 구조로 만들어야 합니다.

엑셀을 처음 배울 때 조회함수로 VLOOKUP을 배웠던 것으로 기억합니다.

그때는 이거 하나면 모든 업무를 다 해결할 수 있지 않을까 생각했죠.

하지만 Index - Match 함수, SUMPRODUCT의 배열함수 등등을 알면서 점점 안쓰게 되더라고요.

그러나 여전히 VLOOKUP은 강력하고 편리한 조회함수임이 분명합니다.

알면 알수록 강력해지는 함수임으로 이번 기회에 여러가지로 소개해 봤습니다.

엑셀에서는 조회함수나 찾기 기능 같은 숫자나 문자를 찾는 방법이 많이 있습니다.

오늘은 그 중에서 꽤 같단한 축에 드는 EXACT 함수와 그 사용법에 대해서 알아보겠습니다.

< EXACT 함수 >

EXACT(text1, text2) : text1과 text2가 같은지 다른지를 판단하여 같은 경우에는 TRUE를 다른 경우에는 FALSE를 반환합니다.

• text1, text2 : 필요한 인수입니다. EXACT 함수는 두 가지의 글자만을 판단합니다. 이 인수는 글자가 될 수도 있고 숫자가 될 수도 있습니다.
  인수에 보통은 한가지 값만 입력해야 합니다.
• EXACT 함수는 대/소문자를 구분합니다.
• 서식이 다른 것은 무시합니다.

EXACT 함수의 가장 큰 특징은 대/소문자를 구분하는 것입니다.

Match, Find, Vlookup등등 많은 엑셀의 함수들이 대/소문자의 차이를 무시합니다.

그리고 *, ~, !의 조회함수 와일드 카드가 통하지 않습니다.

if 등으로 대체할 수 있을 것 같지만 필요할 때 가끔은 사용하는 함수입니다.

< 사용 예시 >

아래 표처럼 심플하게 사용할 수 있습니다.

< 응용하기 : 목록에서 찾기 >

그냥 하나하나 비교하기는 심심할 수 있습니다.

배열함수를 사용하면 목록에서 값이 있는지 검색할 수 있습니다.

아래 표가 사용예시입니다.

OR 함수를 이용해서 하나라도 같으면 TRUE, 모두다 다르면 FALSE라는 결과를 나타내줍니다.

배열함수를 사용할 때는 중괄호{} 는 직접 타이핑해서 넣는게 아닙니다.

수식 내용만 입력하고 Ctrl + Shift + Enter를 누르면 자동으로 정리됩니다.

다른 많은 함수들 역시 이 기능을 가지고 있는 데다가 더 편리한 것들이 많습니다.

EXACT 함수의 존재가치는 대/소문자 구별에 있습니다.

기억해 두어서 나쁘지는 않은 함수입니다.