유영락-정보시스템 감리도구를 통해 생산성 향상

VLOOKUP 대신 INDEX, MATCH 를 사용하면 좋은 경우를 살펴보자.

1. 기준 열 왼쪽에 있는 값을 출력할 때

특정 ID에 해당하는 PRICE 는 VLOOKUP으로 쉽게 출력할 수 있다.

그럼 특정 PRICE에 해당하는 ID는?

VLOOKUP 으로는 이 문제를 해결할 수 없다. VLOOKUP은 왼쪽 열을 기준으로 오른쪽에 있는 데이터를 출력하기 때문.

하지만 MATCH - INDEX 라면 어렵잖게 해결할 수 있다.

앞서와 마찬가지로 F4에 있는 값이 D:D 중 어디에 있는지 알아내고

A열의 4번째라고 알려주면 된다.

직접 열을 지정하므로 왼쪽에 있는 데이터도 얼마든지 출력할 수 있다.

2. 두 가지 이상의 조건으로 결과를 출력할 때

2017/08/24 - [엑셀] - [엑셀] 두 가지 조건으로 VLOOKUP 사용하기 에서 이야기했듯

MATCH - INDEX 로는 두 가지 이상의 조건으로 결과를 출력할 수 있다.

가격이 1,940,000원인 에어컨의 id를 출력하기 위해서는 다음과 같이 수식을 구성하면 되겠다.

3. 여러 셀의 데이터를 동시에 가져올 때

예를 들어 위와같이 ID 1, 4, 9 에 해당하는 데이터를 전부 불러와야 할 경우

일단 VLOOKUP으로 접근해 보자. 커서가 있는 셀의 수식은 다음과 같이 구성하면 되겠다.

이제 오른쪽과 아래까지 모두 채워보자.

잘 된 것 같아 보이지만 자세히 보면 함정이 있다.

PRICE는 모두 1,770,000이며

ID 9 에 해당하는 에어컨의 VER과 PRICE역시 잘못되어 있다.

이런 일이 생기는 이유는 H4셀에서 F2를 눌러보면 알 수 있다.

H4의 수식은 B:B에서 "TV"를 찾아 C열에 해당하는 데이터를 가져오라는 것.

PRODUCT 열에 TV에 해당하는 항목이 하나이므로 여기까지는 어찌 넘어가지만

PRICE 에 해당하는 I4셀의 수식은 C:C에서 "1"을 찾아 D열에 해당하는 데이터를 가져오라고 입력되어 있을 것이다.

모든 PRICE가 1,770,000인 이유다.

에어컨의 경우 역시 H6셀의 수식은 B:B에서 "에어컨" 을 찾아 C열에 해당하는 데이터를 가져오라고 입력되어 있을 것이고

VLOOKUP 은 위에서부터 일치하는 값을 찾으므로 ID = 9 번이 아닌 ID = 3 번의 데이터를 가져오게 된다.

MATCH - INDEX 로 시도해보자.

일단 A열에서 ID에 해당하는 값을 찾는다.

이제 B열의 2번째라고 알려주면 되겠다.

이제 수식을 옆으로 복사하기 전에 두 가지만 고정하자.

1) ID를 판별하는 부분은 항상 A열로 바뀌지 않으므로, 수식의 A:A 부분에서 F4를 눌러 해당 부분을 고정한다.

2) ID를 입력받는 F4셀 역시 아래로는 움직여도 되나 오른쪽으로 움직여서는 안되므로 해당 부분에서 F4를 세 번 눌러 F부분만 고정한다.

완성이다. 오른쪽과 아래로 채워 보자.

의도한대로 데이터를 잘 가져오는 것을 볼 수 있다.

4. 수식 깨짐 방지

VLOOKUP의 가장 큰 문제는 원 데이터가 변화할 때 수식이 깨질 우려가 있다는 것이다. 

위 시트의 A열과 B열 사이에 한 열이 추가된다면 어떻게 될까?

수식의 결과값이 제대로 나오지 않는 것을 볼 수 있다.

애초 A:B 였던 범위는 다행히 A:C 로 잘 변화했으나

두 번째 데이터를 가져오라는 "2" 라는 숫자는 자동으로 변하지 않는다. 

따라서 원 시트에 열 추가, 삭제가 일어날 경우 VLOOKUP은 제대로 동작하지 않는다.

MATCH - INDEX 는 다를까?

마찬가지로 A열과 B열 사이에 열을 하나 추가해 보자.

VLOOKUP과는 달리 임의로 입력한 숫자가 없고 모두 셀 주소로 구성되어 있기 때문에

셀의 변화가 자동으로 수식에 적용된다. 따라서 수식이 깨지지 않는다.

특히 네 번째 이유 때문에, 오래 사용해야 할 시트일수록 VLOOKUP 보다는 MATCH - INDEX 를 사용하여 수식을 구성하는 편이 낫다.

VLOOKUP 함수 역시 빠르게 데이터를 참조하는 데는 간편하게 사용할 수 있으므로, 용도에 맞게 사용하면 좋을 것이다.

출처: https://lightblog.tistory.com/206

엑셀 보이는 셀만 복사 기능은 찾기의 이동 옵션의 화면에 보이는 셀 기능을 사용하면 됩니다. 또는 단축키 Alt + ; 를 사용하면 됩니다.

엑셀 보이는 셀만 복사

화면에 보이는 셀만 복사를 하려면 어떻게 해야 할까요? 보통 데이터가 많을 때 특정 셀을 숨기기로 숨기고 작업을 하는 경우가 많습니다.

이 때 숨긴 셀도 같이 복사가 됩니다. 예를 들어 아래 그림의 경우 5번에서 10번 행이 숨겨져있지만, 복사시 같지 붙여넣기가 됩니다.

이 경우 먼저 복사할 영역을 블록 지정합니다. 그리고 찾기 및 선택 메뉴에서 이동 옵션(S)을 클릭합니다.

화면에 보이는 셀만(Y) 메뉴를 체크하고 확인을 누릅니다. 이러면 숨긴 영역을 제외한 영역만 블록지정이 됩니다. 이 상태에서 Ctrl + c 단축키를 눌러서 셀을 복사합니다.

새로운 시트 등에 데이터를 Ctrl + v 등으로 붙여넣기를 하면, 숨긴 영역의 셀은 복사되지 않고, 보이는 셀만 복사가 됩니다.

숨겨진 영역이 제외되고 화면에 표시된 부분만 복사되었습니다.

또는 복사할 영역을 선택하고 단축키 Alt + ; 키를 누르면 보이는 부분만 블록지정이 됩니다.

이 상태에서 복사 및 붙여넣기를 진행하면 같은 방식으로 동작됩니다.

출처: https://dasima.xyz/%EC%97%91%EC%85%80-%EB%B3%B4%EC%9D%B4%EB%8A%94-%EC%85%80%EB%A7%8C-%EB%B3%B5%EC%82%AC-%ED%95%98%EB%8A%94-%EB%B0%A9%EB%B2%95/

Function indexOf(cell, textToFind)

    'cell의 값을 가져옴
    Dim val
    val = Range(cell, cell).Value



    'cell의 값 1번째 글자부터 textToFind 값 찾기
    Dim idx
    idx = InStr(1, val, textToFind, vbTextCompare)



    '인덱스 값을 리턴
    indexOf = idx

End Function



'========================================



Function lastIndexOf(cell, textToFind)
    'cell의 값을 가져옴
    Dim val
    val = Range(cell, cell).Value

    'cell의 값 뒤에서부터(-1) textToFind 값 찾기
    Dim idx
    idx = InStrRev(val, textToFind, -1, vbTextCompare)

    '인덱스 값을 리턴
    lastIndexOf = idx
End Function

'========================================



참고) inStr 함수

앞에서부터 문자열을 찾는 함수. 사용방법은 다음과 같다.

inStr(찾기시작위치, 대상텍스트, 찾을문자열, 텍스트비교방식)



참고 2) inStrRev 함수

뒤에서부터 문자열을 찾는 함수. 사용방법은 다음과 같다.

inStrRev(대상텍스트, 찾을문자열, 찾기시작위치, 텍스트비교방식)


출처: https://blog.naver.com/PostView.naver?blogId=bb_&logNo=221513849269&parentCategoryNo=&categoryNo=84&viewDate=&isShowPopularPosts=false&from=postView 

여러 엑셀 파일 합치기 유틸 모음

1. 저녁이 있는 엑셀

https://blog.naver.com/exceltool

2. 엑셀머지

https://xlworks.net/xlmerge-excel-file-merge/

3. RDBMerge

https://www.rondebruin.nl/win/addins/rdbmerge.htm

숫자로 입력된 데이터를 한글로 변환

=TEXT($C$2, "[dbnum4]")

=NUMBERSTRING($C$2,1)

=TEXT($C$2, "[dbnum2]")

=NUMBERSTRING($C$2,2)

=TEXT($C$2, "[dbnum4]#")

=NUMBERSTRING($C$2,3)

엑셀 시트 내용 합치기

시트에서 마우스 오른쪽 코드보기

아래 코드 입력후 f5 실행

--엑셀 전체를 그대로...
Sub SheetUnit()
    Dim i As Integer
    Dim ShtA As Worksheet
    Dim rngB As Range
    
    'Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    'Sheets(2).Activate

    Set ShtA = Sheets(1)
    For i = 2 To Sheets.Count
        Set rngB = ShtA.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        Sheets(i).UsedRange.Copy rngB
    Next i
End Sub

-- 영역 끊기면 복사 안함
Sub Combine()
    Dim J As Integer
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    For J = 2 To Sheets.Count
        Sheets(J).Activate
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub

Sub 시트명가져오기_Click()

   

Dim sht As Worksheet

Dim i As Integer

 

'전체 시트를 하나를 읽는다.

For Each sht In Worksheets

'선택한 셀 기준으로 행을 증가하면서

ActiveCell.Offset(i, 0) = sht.Name

 

'다른방법 : 행/열 설정시 반드시 1이상의 값을 지정해야 한다.

'ActiveSheet.Cells(i, 1) = sht.Name

i = i + 1

Next sht

 

End Sub