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

엑셀 도구 - 엑셀 데이터 행열 변환

첫번째 데이터 값을 행 타이틀로, 두번째 데이터 값을 열 값으로 변환

단순히 행,열을 그대로 변환하는것이 아니라 동일한 내용의 테이블 형태가 반복되는경우 첫번째를 타이틀, 두번째를 값으로 해석해서 변환

행1 값1

행2 값2

행3 값3

행1 값4

행2 값5

행3 값6

->

행1 행2 행3

값1 값2 값3

값4 값5 값6

작성자 : 유영락, u0rak@네이버, 정보시스템감리사
프로그램 문의는 받지 않습니다만 발전적인 의견은 환영합니다.
프로그램은 자유롭게 사용 가능합니다. 프로그램에 오류가 있을수 있습니다.
대상 문서가 열려 있거나, 오픈시 다이얼로그가 실행되는 경우에는 제대로 실행되지 않습니다.
업데이트는 https://blog.naver.com/u0rak, https://u0rak.tistory.com/ 를 통해 관리 예정입니다.
도움이 되었다면 후원은 감사히 받겠습니다(SC은행, 22420285094)

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

자료 목록에 대하여 일치 하는 자료 비교

​

작성자 : 유영락, u0rak@네이버, 정보시스템감리사

프로그램 문의는 받지 않습니다만 발전적인 의견은 환영합니다.

프로그램은 자유롭게 사용 가능합니다.

도움이 되었다면 후원은 감사히 받겠습니다(SC은행, 22420285094)

대상 문서가 열려 있거나, 오픈시 다이얼로그가 실행되는 경우에는 제대로 실행되지 않습니다.

엑셀 보이는 셀만 복사 기능은 찾기의 이동 옵션의 화면에 보이는 셀 기능을 사용하면 됩니다. 또는 단축키 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/

찾는단어(1번째), 찾는단어(2번째)열에 찾고자 하는 내용을 입력하면 해당 내용을 excel 문서내에서 검색
동일한 시트에서 검색

테이블정의서가 시트별로 구분되어 있는 경우, 테이블명과 컬럼명에 대한 검색시 사용

작성자 : 유영락, u0rak@네이버, 정보시스템감리사
프로그램 문의는 받지 않습니다만 발전적인 의견은 환영합니다.
프로그램은 자유롭게 사용 가능합니다. 프로그램에 오류가 있을수 있습니다.
대상 문서가 열려 있거나, 오픈시 다이얼로그가 실행되는 경우에는 제대로 실행되지 않습니다.
업데이트는 https://blog.naver.com/u0rak, https://u0rak.tistory.com/ 를 통해 관리 예정입니다.
도움이 되었다면 후원은 감사히 받겠습니다(SC은행, 22420285094)

a열에 찾고자 하는 내용을 입력하면 해당 내용을 ms-word, powerpoint, excel, pdf문서내에서 검색
pdf검색을 위해서는 acrobat pro가 설치되어 있어야 합니다.

​

작성자 : 유영락, u0rak@네이버, 정보시스템감리사

프로그램 문의는 받지 않습니다만 발전적인 의견은 환영합니다.

프로그램은 자유롭게 사용 가능합니다.

도움이 되었다면 후원은 감사히 받겠습니다(SC은행, 22420285094)

대상 문서가 열려 있거나, 오픈시 다이얼로그가 실행되는 경우에는 제대로 실행되지 않습니다.

b1열에 찾고자 하는 내용을 입력하면 해당 내용을 문서내에서 모두 검색
b1열은 정규식으로 해석
pdf검색을 위해서는 acrobat pro가 설치되어 있어야 합니다.

​

작성자 : 유영락, u0rak@네이버, 정보시스템감리사

프로그램 문의는 받지 않습니다만 발전적인 의견은 환영합니다.

프로그램은 자유롭게 사용 가능합니다.

도움이 되었다면 후원은 감사히 받겠습니다(SC은행, 22420285094)

대상 문서가 열려 있거나, 오픈시 다이얼로그가 실행되는 경우에는 제대로 실행되지 않습니다.

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