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

Excel에서 n번째 문자 찾기 

Excel의 텍스트 문자열에서 n번째로 나타나는 특정 문자를 찾는 것은 문자열을 구문 분석하거나 특정 구분 기호 또는 패턴을 기반으로 정보를 추출해야 하는 데이터 분석에 특히 유용할 수 있습니다. 예를 들어, 텍스트 문자열에서 문자 "-"가 두 번째 또는 세 번째로 나타나는 것을 찾아보겠습니다. 이 작업을 효율적으로 수행할 수 있는 간단한 기술을 보여 드리겠습니다.

수식을 사용하여 텍스트 문자열에서 n번째 문자 찾기

n번째 문자 발생을 찾는 공식을 만들 수 있습니다. 다음과 같이 하십시오:

1. 결과를 얻으려는 셀에 다음 수식을 입력하거나 복사하십시오.

=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))

2. 그런 다음 수식 채우기를 아래로 드래그하여 수식을 다른 셀에 채우고 문자의 두 번째 위치가 한 번에 표시됩니다. 스크린샷을 참조하세요.

사용자 정의 함수를 사용하여 텍스트 문자열에서 문자의 n번째 발생 찾기

이 섹션에서는 UDF를 만들고 사용하여 Excel에서 n번째 문자 발생을 찾는 방법을 살펴보고 데이터 처리를 최적화하는 데 도움이 되는 단계별 가이드를 제공합니다.

1. ALT + F11 키를 눌러 응용 프로그램 용 Microsoft Visual Basic 창이 열립니다.
2. 삽입 > 모듈을 클릭하고 모듈 창에 다음 매크로를 붙여 넣습니다.

    Function FindN(sFindWhat As String, _
   sInputString As String, N As Integer) As Integer
   'Updateby Extendoffice
   Dim J As Integer
   Application.Volatile
   FindN = 0
   For J = 1 To N
   FindN = InStr(FindN + 1, sInputString, sFindWhat)
   If FindN = 0 Then Exit For
   Next
   End Function

    

    
3. 그런 다음 vba 창을 닫습니다. 워크시트로 돌아가서 다음 수식을 셀에 입력한 다음 채우기 핸들을 아래로 끌어 수식을 다른 셀에 채웁니다. 스크린샷을 참조하세요.

   =FindN("-",A2,3)

    

   

출처: https://ko.extendoffice.com/documents/excel/1052-excel-find-nth-occurrence.html

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