'업무 능력 향상'에 해당되는 글 240건

Worksheet Object 의 이해

자료출처 : http://blog.daum.net/kdy88/8403

우선 워크시트 오브젝트의 중요한 구성원(속성, 메서드 등)들을 살펴보겠습니다.

워크시트 오브젝트는 쉽게 말해 워크시트 그 자체를 의미하므로 워크시트의 이름을 바꾸거나 셀에 어떤 값을 입력하고자 할 때 위의 메서드 또는 속성을 이용하면 됩니다.

위의 내용은 출처를 밝힌 블로그에서 가져왔구요. 아래 내용은 간단하게 제가 별도로 설명을 하겠습니다.

현재 엑셀 작업을 하는 Sheet 명을 가져온다든가, 다른 Sheet 명을 가져오거나 다른 시트명에 기록을 해야 하는 등의 여러가지로 Sheet 를 다루는 일이 있을 겁니다.

이런 경우를 자유자재로 사용하기 위해서 알아야 할 Object(객체)라고 이해하시면 됩니다.

Sub ActiveSheetName()

Dim ActiveSht As String

ActiveSht = ActiveSheet.Name   '//활성화된 Sheet 이름

Msgbox "현재 작업하는 시트는 " & ActiveSht &" 입니다"

End Sub

실행 결과

워크시트를 추가

워크시트를 입력하고 점(.)을 찍으면 자동으로 뭘 선택할 것인지 나옵니다.

Sub Sht_Add()
    Worksheets.Add

End Sub
를 해주면 결과가

그림 보니까 Sheet1 앞에 추가되었군요.

그렇다면 Sheet1 다음에 추가를 하고 싶다면 ???
Sub Sht_Add()
    Worksheets.Add After:=Worksheets(1), Count:=2
End Sub

Sheet5 와 Sheet6 이 생성된 것은 Sheet4를 생성했다가 지웠는데도 이렇게 생성되었네요.

하지만 Sheet4를 생성한 적이 없다면 Sheet4 와 Sheet5 가 생성되었을 겁니다.

Sheet를 이동하고 싶을 때

Sub Sht_Move()
    Worksheets("Sheet6").Move After:=Worksheets("Sheet3")
End Sub

Sheet6 를 Sheet3 다음으로 이동하라는 건데 이동된거 그림에 보이죠?

다른 시트를 선택하고 싶은 경우

Sub Sht_Select()
    Worksheets("Sheet2").Select
End Sub

Sheet 명을 직접 적어주는게 헷갈리지 않고 좋습니다.

위에 나온 표를 보면서 그림들을 비교하면서 살펴보면 이해하는데 빠릅니다.

삭제에 대한 건 굳이 얘기 안해도 아시겠죠?

Sub DeleteSheet()

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

End Sub

텍스트로 된 숫자를 값으로 변경하는 쉬운 방법

엑셀을 다루다보면 자료가 값으로 된 숫자가 아니라 텍스트로 된 숫자로 표기된 경우가 있습니다.

주로 시스템에서 뽑아낸 값이 텍스트로 되어 있죠.

시스템에서 뽑아낸 데이터 값이 많은 경우에는 방법을 모르면 상당한 노가다를 할 수 있습니다.

좀 편하고 쉽게 하는 방법으로 두가지가 있습니다.

하나는 고정틀을 이용하는 방법이고 또하는 텍스트분할 방법입니다.

고정틀을 이용하는 방법

아무리 길어도 고정틀을 설정하고 나서 원하는 셀까지 한꺼번에 이동하는 건

END키를 누르고 화살표키를 움직여서 전체를 설정할 수 있습니다.

텍스트 분할을 이용하는 방법

이런 방법이 가장 편하고 빠르게 원하는 결과를 얻을 수 있습니다.

첨부파일 올려두니 받아서 직접 한번 해보세요.

옆셀에다가 =VALUE(B1) 이라고 직접 함수를 이용하여 값을 바꿔보는 것도 한번 해보시구요.

엑셀이 제공하는 기능을 조금만 편리하게 사용하면 정말 편하게 만들 수 있답니다.

http://link2me.tistory.com/250

이건 VBA를 이용한 방법입니다. 순식간에 변경되어 버립니다.

매크로를 이용하면 좀 더 편하게 작업할 수 있습니다.

Text2Number.xlsx

[엑셀] IF 문은 대문자 소문자를 구별할까?

여러분 엑셀에서 가장 많이 사용하는 IF 문을 가지고 서로 같다는 걸 찾는데 과연 이게 대문자 소문자까지 정확하게 찾을까요?

한번 살펴보겠습니다.

그림 보시면 엑셀 IF문은 대문자와 소문자 구별을 못합니다. 의문 생기시는 분은 직접 해보세요.

IF 문가지고 두 셀이 정확하게 일치하는지 여부를 체크하는 건 잘못이라는 거 이제 아셨죠?

그럼 어떤 걸로 해결해야 할까요?

두 셀이 정확하게 일치하는지 검사하는 함수는 EXACT 함수입니다.

아래 EXACT 함수를 적용해서 본 것은 전부 FALSE 를 되돌려 주었군요.

수식이 얼른 눈에 안들어오시죠?

IF 함수와 EXACT 함수를 같이 써주어, 일치하면 1, 불일치하면 0으로 값을 반환하도록 했어요.

두개의 셀이 정확하게 일치하는지 여부를 가장 쉽게 확인하는 방법에 대해 알아봤습니다.

엑셀에서 두 셀이 정확하게 일치하는지 여부를 체크하는 함수를 알아두시면 업무에 여러모로 편리합니다.

굳이 외우지 않더라고 제 블로그 기억하셨다가 오셔서 검색으로 찾아보시면 됩니다.

늘상 엑셀을 다루지 않기 때문에 저도 배운걸 나중에 다시 활용하기 위해 정리할 때 좀 더 시간 투자해서 정리를 해두는 겁니다.

VLookup 함수만 알아도 엑셀 다루는 업무가 엄청 편해지는데요.

VLookup 함수를 다루다보니 용량이 너무 큰 경우에는 파일 사이즈가 장난 아니게 커지는데다 속도 저하문제가 너무 심해서 이걸 해결할 목적으로 PC의 RAM(메모리)를 더 많이 늘렸었습니다. 그런데도 불구하고 함수를 많이 사용하면 역시나 속도 저하 문제가 심하고 응답도 늦더군요..

그래서 가능한 방법을 찾아보니 VLookup 함수 VBA 를 이용한 방법이 있더군요. 사용해보니 너무 편하고 좋네요.

속도저하 문제도 해결되고 용량문제도 해결됩니다.

VLookup 함수에 대한 이해가 부족하신 분은 http://link2me.tistory.com/29 게시물을 먼저 읽어보면 도움 되실 겁니다.

VBA 라고 하니까 너무 어렵다고 생각하고 아예 엄두도 안내시는 분들 많으실 겁니다.

제가 설명드린 거 찬찬히 보시고 첨부된 샘플 보시고 샘플만 몇가지 값만 수정하시면 금방 이해되시고 얼마든지 쉽게 누구나 다 이용하실 수 있다는 겁니다. 초보자 분들 용기를 내십시요

구하고자 하는 결과는 C열에 표시하는 것이라면.....

VBA 함수는 아래와 같습니다.

수정해서 사용할 부분이 어디인지 아시겠죠?

범위(Range) 지정하는 첫 시작셀 지점

테이블의 범위 지정

테이블에서 몇번째 열의 값을 가져올 것인가 하는 것 지정

가져온 걸 어디에 뿌려줄 것인가를 지정

* 다른 시트에 있는 자료를 table_array 로 할 경우 변수 선언 방법은

Set table_array = Worksheets("Table_array").Range("A2:C16") 처럼 직접 마지막 셀을 적어주는게 가장 확실하겠죠. 하지만 셀이 변경될 경우에는 잘못된 참조가 될 수도 있습니다.

이걸 방지하려면 Set table_array = Worksheets("Table_array").Range("A2:C" & Cells(Rows.Count,"C").End(3).Row) 라고 변수선언을 해주면 됩니다.

이렇게 하면 함수식이 전부 사라지고 바로 결과값이 나오므로 파일 사이즈가 커지지 않고 속도도 빠르게 처리됩니다.

1번은 시작셀 A2를 지정했고, 중간에 또 나오는 값 A가 있는데 이것도 같은 열의 값이어야 합니다.

2 번에 보는 바와 같이 같은 sheet 내에서 처리하는 방법, 다른 sheet 에 있는 걸 처리하는 방법, 다른 파일에서 가져오는 방법을 다 표기해두었습니다. 다른 파일에 있는 걸 가져오려면 반드시 Ctrl + O 로 파일을 열어서 가져와야 합니다.  

(엑셀을 분리하여 띄우는 방법으로 하면 참조가 안될 수 있습니다)

첨부된 vba 된 확장자 파일을 다운로드 받아서 텍스트 에디터로 여세요.
그리고 엑셀에서 Alt + F11 키를 누르세요. 그러면 아래와 같은 화면이 나타납니다. 

텍스트 에디터로 연 코드를 복사하여 여기에 붙여넣기를 하시면 됩니다.

이렇게 하시면 VLoolup 함수만 사용해서 하는 것보다 훨씬 속도면에서 빠릅니다.

단, 주의 사항은 잘못지정하면 되돌리기를 할 수 없다는 점을 명심하셔야 합니다.

표시될 결과값을 잘못 열을 지정하면 기록된 값이 순식간에 새로운 결과값으로 업데이트되고 되돌릴 수가 없어집니다.

처음에 하실 때에는 반드시 백업파일을 하나 만드신 다음에 하시기 바랍니다.

함수 몇가지와 더불어 VLookup 함수 다룰 때 VBA 를 이용하면 편하므로 한번 이용해보세요. 

VLookup_VBA_code.vbs

Vlookup_VBA_sample.xlsm

이상으로 VLookup 함수를 VBA를 이용하여 하는 방법에 대해 알아봤습니다.

엑셀 VBA 참고하면 좋은 사이트 소개

엑셀 VBA 를 배우는데 도움이 되는 사이트를 소개합니다.

기본적인 개념을 설명이 잘 된 사이트는

엑셀러 권현욱님의 사이트 입니다. VBA 함수에 대한 기본 개념과 더불어 풍부한 예제가 많습니다.

단점이라면 풍부한 예제를 일일이 다운로드 받아서 봐야 한다는 점과 검색으로 쉽게 찾을 수 없다는 점입니다.

http://www.iexceller.com/

니꾸님 블로그는 정말 설명도 잘 되어 있어요.

차근 차근 배운다면 분명 많은 도움이 되실 겁니다. 그러나 엑셀로 하루종일 업무를 다루시는 분이 아니라면 차근 차근 배운다는 건 쉬운 일이 아니죠. 난 그냥 필요한 거 도움만 받으면 되니까 하시는 분들은 그냥 이 블로그에서 검색만 잘 눌러서 원하는 것이 있으면 활용하면 좋겠죠.

http://rosa0189.blog.me/

하나를 하더라도 최선을님의 블로그에도 좋은 정보들이 매우 많습니다.

2011년도 네이버 파워지식IN 으로 선정되신 분이십니다.

차근 차근 둘러볼 시간이 없다면 언제든지 검색을 눌러서 찾으면 원하는 걸 쉽게 찾을 수 있답니다.

http://blog.naver.com/heesung2003

엣마님 블로그

http://blog.naver.com/prologue/PrologueList.nhn?blogId=atmyhome

이상이 제가 아는 VBA 에 도움이 되는 사이트 입니다.

앞으로도 더 많은 정보를 알게되면 추가로 업데이트를 해두겠습니다.

엑셀 VBA 가져다 활용하는 방법 소개

엑셀 함수를 사용하여 처리하다보면 Line(행)이 작은 건 편하고 좋은데 처리할 행이 1만 행 정도 된다거나 그 이상 되는 건 처리하는데 정말 시간이 너무 많이 걸립니다.

그리고 함수를 적용하면 속도가 장난 아니게 느려집니다.

이럴 때 VBA(Visual Basic for Application) 를 잘 알아두면 업무가 엄청 편하고 좋습니다.

VBA는 거의 프로그램이나 마찬가지라서 프로그램에 대한 기본적인 이해가 동반되어야 합니다.

그러나 다른 사람들이 만들어 둔 걸 활용하거나, 그냥 가져다가 사용하는 방법만 알아도 업무에 도움이 많이 되실 겁니다...

먼저 주의사항 부터 말씀드리겠습니다.

엑셀은 실수를 하면 되돌리기 기능이 있어서 되돌리면 됩니다.

그러나 VBA는 되돌리기 기능을 제공하지 않습니다. 반드시 백업을 해두고 나서 작업을 하는게 좋습니다.

엑셀이 띄워진 상태에서 ALT + F11 키를 눌러주세요.

그러면 아래 그림처럼 나옵니다.

VBA 를 직접 작성하거나 다른 사람이 만든 VBA 코드를 복사하여 붙이기를 하고 나면

ALT + F11 키를 눌러주면 창이 닫혔다 열렸다 할 겁니다.

붙여넣기가 되었으면 sub 연습() End Sub 사이에 커서를 놓고 F5 키를 누르면 실행됩니다.

엑셀시트는 반드시 실행시킬 시트에 두고 하세요..

VBA 를 얼마나 잘 다루르냐는 배우는 정도에 따라 다르겠지만 간단한 것 정도 배우는 것도 정말 도움이 많이 됩니다.

VBA 어렵다고 생각하지 마시고 용기를 내서 배우는게 업무를 하면서 밤을 세워서 할 것인가 그냥 한두시간만에 뚝딱 끝내고 편하게 업무를 할 것인가가 결정됩니다.

[VBA 입문] 매크로 실행버튼 만들기

엑셀 함수만 이용하다가 VBA를 배우면서 알게 된 걸 짧은 지식이지만

저도 참조하고 저보다 더 초보자인 분들이 이해하기 쉽게 조금씩 포스팅을 하고자 합니다.

엑셀 VBA 함수 지정되어 있는지 여부는 Alt + F11 키를 눌러보면 알 수 있다.

매크로가 지정된 상태로 저장된 것은 확장자가 xlsm 으로 되어 있다.

엑셀 버전이 낮은 경우에는 xls 로 되어 있기도 한다.

이런 버튼을 만드는 방법은 아래 그림 순서대로 따라하면 만들 수 있다

이렇게 하시고 나면 아래 그림처럼 아이콘이 하나 더 추가된다.

매크로 버튼을 만들 일이 있을 때 이 아이콘을 눌러서 실행하면 된다.

모양이 별로 이쁘지 않으므로 보통은 [삽입] 에서 도형을 선택해서 만든다.

단추1 이라고 버튼이 하나 만들어졌네다. 눌러보면 에러만 나온다.

왜냐하면 매크로와 연결이 안되어서다.

매크로 버튼 만들어서 사용하면 여러모로 편리하다.

단축키 지정하는 것도 해봤지만 눈에 보이게 버튼 만들어서 사용하는게 훨씬더 편하다.

처음에는 VBA 정말 어려워서 엄두도 나지 않을 수 있다.

기본적인 이해가 매우 중요하다. 그러나 기본적인 이해라는게 쉬운 것은 아니므로 고수들이 만들어둔 VBA 에서 필요한 부분만 수정해서 이용하면서 배운다.

이런 버튼 만들지 않고 그냥

에서 추가해도 된다.

도형을 추가한 다음에 마우스 우클릭 누르면

매크로 지정을 눌러서 매크로 함수랑 연결해주면 다음부터는 버튼만 클릭하면 된다.

버튼을 만들지 않을 경우에는 F5 키를 누르면 실행된다.

[엑셀] sumproduct 함수 알아보자

sumproduct 함수는 배열범위끼리 곱하고 합계를 구하는 함수입니다.

단가와 수량을 곱한 것의 총 합계를 구하라

라고 한다면 가격이라고 필드를 하나 추가해서 단가 X 수량 = 가격 이라는 값을 구해서 원하는 결과를 얻어도 되지만 그냥 sumproduct 함수를 써서 결과를 얻을 수 있습니다

함수식은 =SUMPRODUCT(D2:D16,E2:E16) 로 지정하면 됩니다. 여기서 반드시 행과 열은 일치된 것끼리 곱하기를 하셔야 합니다.

D2:D16 (15개)에 대응되는 것을 E3:E16 (14개)이라고 하면 #VALUE 라고 에러가 발생합니다.

D2:D16 (15개)에 대응되는 것을 E3:E17 (15개)로 한줄 밀려서 지정하면  

D2 X E3 + D3 X E4 처럼 밀려서 곱하기하고 합을 하므로 값은 구해집니다만 결과가 다르게 나옵니다.

이번에는 조건을 걸어서 조건에 맞는 것만 구해보겠습니다.

수량이 15개 이상인 것만 찾아서 단가 X 수량의 총합을 구하라

라고 한다면 

여기서 배열의 조건식 E2:E16 >= 15 의 의미는 조건식과 일치하면 True 인 1을 돌려주고, 조건식과 틀리면 False인 0 을 반환합니다.

다시 말해서 E2 가 15보다 큰가? 맞다면 1을 반환하고, 뒤의 E2와 값을 곱하라(*)  

이렇게 배열범위안에 있는 각 셀의 조건 충족 여부를 확인하면서 값을 구하는 것입니다.

이번에는 품목이 독서대인 것의 단가 X 수량의 합을 구하라

여기서 한가지 더 살펴보겠습니다.

품목이 독서대인 것은 몇개인가? 를 육안으로 보면 총 4개 입니다.

함수식으로 구하는 건 =SUMPRODUCT((C2:C16="독서대")*1)

뒤에 1을 곱해주면 됩니다.

조건이 2개 이상의 다중조건식은 어떻게 하는지 이해되시죠?

공급처는 '동아' 이고 품목은 '독서대' 인 것이 몇개인지 구하는 식은 =SUMPRODUCT((B2:B16="동아")*(C2:C16="독서대")*1) 

공급처가 '동아'이고 품목은 '독서대' 인 것의 단가 X 수량의 합을 구하라 고 하면

조건 두가지와 구할 영역을 곱해(*)주면 된다는 것 이해되시죠?

배열의 조건은 더 다양하게 설정이 가능합니다. 

도움이 좀 되셨기를 ^^

이번에 알아볼 함수는 OFFSET 함수입니다.

주로 자동화 처리나 차트 그릴때 유용하게 사용하는 함수라고 하는데 아직 챠트 그리는 것까지 해보지는 않았습니다.

OFFSET 함수의 의미가 뭔지 살펴보겠습니다.

이것만 봐서는 무슨 의미인지 이해가 잘 안되시죠?

B5 셀에 함수 내용이 위 그림과 같다고 합시다.

어떤 의미인지 분석해보면....

기준점은 B2 셀이구요. 1 은 행을 의미한다고 했죠? 현재 파란색이 있는 행은 0, 한행 아래행은 1 로 이해를 하시면 됩니다.

기준점과 동일한 행이면 0 이고 0부터 숫자를 카운트하여 아래로 이동하거나 위로 이동한다.

열의 값이 2로 되어 있죠? 기준셀을 기준으로 해서 0, 1, 2, 3 이렇게 숫자를 카운트 하시면 됩니다.

높이와 너비는 Option 인데요

높이와 너비가 있을 경우에는 SUM 을 해주지 않으면 에러가 발생합니다.

=OFFSET(B2,1,2)는 단순하게 이동된 지점의 셀값을 의미하지만 높이와 너비가 있는 경우에는  

=OFFSET(B2,1,2,2,3) 으로만 표기를 하면 에러가 발생합니다.

위 그림은 행의 이동을 직접 숫자로 기입하지 않고 상대적인 값을 이용하고자 셀을 지정했네요.

이동할 행과 열에 값이 직접 들어가면 행과 열의 숫자를 대입해보고 어느정도 기준점이 이동하는지를 파악해야 합니다.

위 그림은 높이는 1로 두고 너비를 가변으로 변하도록 만들어서 SUM을 하도록 만든 경우입니다.

OFFSET 함수 조건식을 어떻게 주느냐에 따라 다양한 형태를 만들어 낼 수가 있습니다. 

다양한 함수식과 결합하여 원하는 결과를 반환할 수 있답니다.

VLOOKUP 함수를 적용해도 되고 COUNTA 등 다양한 함수와 결합하여 원하는 결과를 얻을 수 있습니다. 

OFFSET 의 기능을 VBA 에서 유용하게 사용되더군요.

사실 먼저 자동 매크로를 이용하여 원하는 작업을 해보고 나서 단순 반복을 줄이기 위해서 FOR NEXT 문, IF문을 사용하고 액셀 객체를 연결해서 작업을 하도록 만들 때 상대적인 개념은 매우 유용하게 사용되더군요.

엑셀을 다루다보면 셀에 동일한 값을 1000 개 이상 채울 수 있을 경우도 있을 겁니다.

이럴 경우 일일이 마우스를 Drag 하여 채우기를 하는 건 정말 비효울적인 방법입니다.

간단하게 할 수 있는 방법을 소개하니 한번만 해보시면 금방 아실 겁니다.

아래 그림처럼 B3000 이라고 직접 입력하고 엔터키를 침

이제 틀고정을 선택하여 아래처럼 해주세요

이렇게 하면 쉽게 원하는 값을 채울 수가 있습니다.

더 간단한 방법일 수도 있는 팁 한가지는 Ctrl + D 를 눌러도 됩니다.

궁금한 사항은 댓글 달아주시면 답변 드리겠습니다.

유용한 단축키

엑셀을 사용하다보면 단축키를 알아두면 편리한게 있습니다.

대부분은 마우스로 다 해결 가능하지만 알아두면 좀 더 편리한 걸 제 나름대로 정리해보고 있는 중입니다.

더 도움이 될 단축키는 추가 수정하여 올리겠습니다. 

엑셀을 다루다보면 요긴하게 사용할 함수들을 알아둘 필요가 있다.

앞으로 유용하게 사용할 함수들을 공부하면 알기 쉽게 정리하고 추가할 생각이다.

셀에 나온 내용중에서 특정구간을 유용하게 이용하고자 하는 경우에는

concatenate 함수, mid 함수를 사용하여 원하는 내용을 발췌 하면 좋다.

셀의 내용중에 불필요하게 들어간 공백을 제거하고 싶다면 

trim(A1) 함수를 이용하면 된다.

선택하여 붙여넣기 - 값 을 편하게 입력하려면....

복사하기 : Ctrl + C

붙여넣기 : Alt + HVV  (값 붙여넣기)

중복검사

내용 : 특정구간에 데이터가 1000개가 있고, 다른 셀에 특정값이 있을 때 다른 셀의 값이 특정구간에 존재하는지 여부 검사

함수 : IF(COUNTIF(조건구간,비교하고자하는 셀),"중복","신규")

자체셀에서 중복 값, 문장 찾아내기

IF(COUNTIF($A$3:$A$8,A4)>1,"중복","")

이건 일단 오름차순 정렬을 먼저하고 하는게 중복값 비교도 되고 용이하다.

VLOOKUP(찾을 값,배열,반환할 값의 열이 몇번째인가, FALSE)

첫번째 열에서 일치하는 값을 찾은 다음, 오른쪽 몇번째 값을 가져와라

다시 말하면, 배열에서 찾을 값과 일치하는 행을 반환한다. (행을 찾는다)

** 배열은 첫번째 열

    배열보다 왼쪽에 있는 값은 반환이 불가능

찾는 값보다 왼쪽에 있는 배열도 찾기를 원한다면 INDEX/MATCH 함수를 사용하라!!!

INDEX(원하는 값 조건범위 문자열, MATCH(찾는값,찾을 조건범위 문자열,0))

조건을 만족하는 하나의 값만 출력 가능

INDEX(array,행,열)

열의 값을 입력하지 않으면 자동으로 1열이 반환된다. 즉 이 경우에는 INDEX(array,행)

행을 찾으려면 MATCH 함수를 이용한다. 

MATCH 함수는 지정한 값의 첫번째 위치값을 반환받는 함수

MATCH함수는 지정한 값을 배열에서 찾아 상대 위치를 구해준다.

  * match(현재 SHHET의 찾고자 하는 값, 찾고자 하는 값과 동일 값이 들어간 (타 SHEET) 배열, 0) 처럼 입력해야 함

     index 에서 찾는 배열은 macth 함수에서 사용하는 배열보다 더 넓은 범위의 배열

Vlookup 과 Match 함수를 사용하여 다른 곳의 값을 참조하는 경우 꼭 알아둬야 할 것은 

해당 함수가 대/소문자를 구별하지못하기 때문에.. 

대/소문자를 구별해야 할 경우 이 두 함수를 사용하지 말아야 한다는 것

만약 N/A 라는 것이 나오는 것을 나오지 않게 하고 싶다면

IFERROR(INDEX($A$2:$B$41,MATCH(G3,$B$2:$B$41,0),1),"") 와 같이 IFERROR 함수를 사용하라 

FIND 함수

FIND(찾고자 하는 값, 찾는 값이 들어간 셀,1) = 찾는 값이 들어간 셀의 시작점 위치를 반환

원하는 게 있는 것지 여부라면 =COUNT(FIND({"지우개","연필","볼펜"},B3))

피벗 작업을 위한 거라면 IF(COUNT(FIND({"지우개","연필","볼펜"},B3))>0,1,0)

 * 찾는 값이 하나라도 들어가면 1로 표기하고, 안들어가 있으면 0으로 표기하라

만약 값을 기록하도록 하고 싶다면 MID함수랑 같이 사용한다.

LEFT(찾는 값이 들어간 셀, 시작점에서의 숫자길이) 
   LEFT(A1, VALUE(FIND("-",A1,1)-1)) : 구분자 "-"를 기준으로 왼쪽의 값을 표시하라 
MID(찾는 값이 들어간 셀,시작점,시작점부터의 숫자길이) 

   MID(찾는 값이 들어간 셀, FIND(찾고자하는 값, 찾는 값이 들어간 셀,1), 숫자길이)
RIGHT(찾는 값이 들어간 셀, 오른쪽에서부터의 숫자길이) 
   RIGHT(A1,LEN(A1)-FIND("-",A1,1)

SUBSTITUTE(찾는 값이 들어간 셀, old text, new text, instance num)

  * instance num : 몇번째 old text 를 new text 로 바꿀 것인지 지정하는 숫자, 생략도 가능 

예제 : =MID(A2,FIND("ㅜ",SUBSTITUTE(A2," ","ㅜ",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1),100)

  의미 해석 : 공백으로 떨어져 있는 값중에서 마지막 공백의 오른쪽 값을 반환하라

  FIND 함수를 통해 시작점 위치를 찾는다. 시작점으로부터 길이는 잘 모르지만 최대한 길게 잡아 100으로 설정했다.

  찾고자 하는 값이 공백이 몇번째 공백인지 여부를 찾기 위해서

  특정 값으로 대체하고 LEN 함수를 이용하여 intance num 값의 위치를 찾는다. 

특정기호로 된 것을 구분하는 것은

 데이터 -> 텍스트나누기 함수를 이용하면 편하다

 가령 구분자가 > 로 된 경우가 여러개 존재하는 경우 나누고자 하는 수만큼 열을 생성한 다음에  

 이 함수를 사용하면 쉽게 나누어진다. 

자리수를 4자리로 표현하면서 0001, 0002 등로 나오도록 하려면....

text(셀값,"0000") 로 지정하면 된다.

다중조건으로 값이 일치하는 것을 찾고자 할 경우에는 해결이 쉽지 않으면 두개의 셀을 하나의 셀처럼 인식시켜 비교하는 것도 방법이다.

C2&D2

길이가 일정하지 않는 것은 오름차순 정렬을 하면 제대로 정렬이 안된다.

A1

A10

A100

뭐 이런식으로 정렬이 되니 불편하다.

이건 텍스트 분리하기를 해서 하나로 다시 합치는 방법을 사용해보자

RIGHT(B2,LEN(B2)-1)  : 왼쪽 1개를 제외하고 나머지를 모두 기입하라

이것을 가지고 TEXT 셀값 맞추기를 하면 된다. TEXT(RIGHT(B2,LEN(B2)-1,"00000")

특정 셀이 숫자인지, 문자인지 여부 판별하는 식으로 자막 공백제거

=IF(ISNUMBER(I15)=TRUE,"A",IF(ISTEXT(I15)=TRUE,"B","C"))

색기준 정렬

 정렬필터에서 색기준으로 정렬하면 된다.

 특정단어가 들어간 필드만 색깔을 넣고자 할 경우에는 조건부서식을 활용하면 된다.

공백 없애기

  홍길동, 홍 길동 등으로 입력값이 서로 다를 때 비교하기가 모호해질 때 공백을 없애는 함수는

  =substitute(a1, " ", "") 이렇게 쓰면 문자열내의 모든 공백이 제거됨

  문자열 앞뒤 공백 제거는 =trim(a1) 함수를 사용

 셀에 보면 숫자인데 텍스트로 되어 있어서 실제로는 숫자로 인식 안되는 경우가 있다.

이럴 경우 숫자로 인식시키는 방법은

=VALUE(C1) 처럼 텍스트를 숫자로 인식시키는 함수를 사용한 다음에 다른 셀에 값만 붙여넣기를 하면 된다. 

출처 : 엑셀 하루에 하나씩 카페

1.해당 엑셀 시트에서 Alt+F11을 누르거나, 도구-매크로-'Visual Basic Editor"를 실행합니다.
2. VBA편집기가 나오면, 메뉴바에서 "삽입-모듈"을 실행합니다.
3. 하얀 백지화면이 나오면 아래 코드를 그대로 복사해다가 붙여넣습니다.
4. Alt+F11을 눌러 다시 원래의 워크시트로 돌아오십니다.
5. 일반 워크시트 함수와 똑같이 사용하시면 됩니다.

** 단점은 띄어쓰기가 된 걸 인식하지 못한다는 것이다.

    그래서 편법으로 " " 공백문자를 인식하도록 하는" "를 추가했다.

    두개의 조건문에 모두 넣으니 인식이 안되길래 한번씩 사용하는 걸로 하고 두번에 걸쳐서 자료를 추출했더니

    원하는 결과값이 얻어졌다. 심봤다!!!!!!!!!!

아래 함수를 직접 만들어주신 분께 정말 감사드립니다 

Function CutText(sText As String, Optional LanguageType As Integer = 1) As Variant

' ----------------------------------------------------------------------------------------

' 설명 : 인수로 전달한 sText 에서 LanguageType 의 값에 따라 지정한

'        형식의 텍스트만 분리해서 전달합니다.

'         LanguageType 의 사용값

'         1 : 숫자

'         2 : 영어 : 띠어쓰기 인식하도록 " " 추가하고, ' 인식하도록 추가

'         3 : 한글

'         4 : 한자

' 작성일 : 2005 / 9 / 20

' ----------------------------------------------------------------------------------------

    Dim sCut As String

    Dim sTMP As String

    Dim i As Integer

    Application.Volatile

    If LanguageType > 4 Then

        CutText = CVErr(xlErrNA)    '#N/A 오류를 반환

        Exit Function

    End If

    For i = 1 To Len(sText)  

        sCut = Mid(sText, i, 1)  

        Select Case sCut

            Case 0 To 9

                If LanguageType = 1 Then sTMP = sTMP & sCut

            Case "a" To "z", "A" To "Z", " ", "'"

                If LanguageType = 2 Then sTMP = sTMP & sCut

            Case "가" To "힣", "ㄱ" To "ㅎ", "ㅏ" To "ㅣ"

                If LanguageType = 3 Then sTMP = sTMP & sCut

            Case Else

                If LanguageType = 4 Then

                    If Asc(sCut) >= -13663 And Asc(sCut) < 0 Then sTMP = sTMP & sCut

                End If

        End Select  

    Next

    CutText = sTMP  

End Function

FIND 함수를 활용하는 법을 설명을 했는데 업무에 편리하게 활용하기에는 좀 부족한 면이 있는 거 같아서 좀 더 정리해서 올립니다.

그냥 간단하게 값을 입력하여 찾는 방법은 다 아는 것이니 굳이 설명드리지 않겠습니다.

오늘 설명드릴 사항은 배열을 이용하여 찾기를 해보겠습니다.

무슨 말이냐면

관광명소 중에서 '조선', '고려'와 연관된 말이 들어간 셀을 찾아서 표시하라

라는 내용이 있다고 칩시다.

그런데 다음에는 '신라', '백제' 라는 말이 들어간 셀을 찾아서 표시하라

라고 한다면 어떻게 할까요?

이런 식으로 찾아야 할 값을 나열식으로 적어주는 방법도 있지만

이 경우에는 값을 추가하거나 변경해야 할 경우에는 엄청 불편합니다.

이걸 간단하게 해결하는 방법은

배열식을 이용하는 방법입니다.

배열서식으로 만들어서 작업을 하면 배열값을 늘리거나 줄이면서 작업하면 편리하게 작업이 가능하므로 매우 편리합니다.

배열 참조식은 별도로 다른 sheet 에다가 놓고 작업을 하는 편이 보기도 좋고 여러모로 좋습니다. 

FIND 함수, IF 함수, VLOOKUP함수, 피벗테이블을 적절하게 잘 활용하면 원하는 작업을 편하게, 빠르게 하실 수 있답니다.

자료 정리 하는 것중에 어려운 건 샘플 만들어서 하는 거네요 ㅠㅠㅠ

허접한 샘플이지만 파일 첨부했습니다.  

FIND_array_sample.xlsx

중복값을 찾는 걸 어떻게 찾아낼까요?

가장 손쉬운 방법이 조건부 서식을 활용한 방법입니다.

조건부 서식을 활용하면 여러모로 편리합니다.

어제 중복자료를 체크해야 할 사항이 있어서 조건부서식을 활용하여 요긴하게 써먹었습니다.

품목에서 중복된 값을 찾아보는 방법을 그림을 죽 설명했으니 보시면 이해가 금방되실 겁니다.

중복체크할 영역을 블럭설정을 합니다.

이제 조건부서식을 이용하여 중복값을 선택합니다.

중복을 설정하고 나면 아래처럼 표시가 됩니다.

같은 것끼리 정렬을 이용하여 정리를 해보면...

이렇게 나옵니다.

중복값 제거를 바로 하면 옆에 있는 셀과의 관련성이 잘못된 것이 있을 수도 있는 걸 모를 수가 있으니 중복값 검사를 한 다음에

중복값 제거를 하는게 좋습니다.

엑셀을 하다보면 원하지 않게 공백이 있어서 정렬을 해서 비교를 해도 중복자료가 있는지 없는지 파악하기 힘들때가 있습니다.

이럴 때 유용하게 사용하는 함수가 공백제거 함수인 trim 함수 입니다.

trim 함수는 문장의 앞뒤의 공백만 제거합니다. 즉 문장 사이의 띄어쓰기는 처리 하지 않습니다.

B열 8행 보시면 앞의 공백이 제거된 거 보이시죠?

문장을 입력하다보면 한칸띄기를 해야 하는데 두칸띄기가 되어 있는 경우가 생기기도 하는데요

이럴 때는 아래처럼 Ctrl + H를 눌러서 스페이스바를 두번 눌러주고, 변경할 내용에는 스페이스바를 한번만 하고 나서

찾기를 한 다음 변경하기를 해주시면 됩니다.

아주 간단한 내용인데 경우에 따라서는 잘 생각나지 않아서

필요할 때 도움이 되실수도 있어서 적어봤습니다.

엑셀을 사용하다보면 영문으로 넣었는데 자동으로 특정글자가 한글로 변경되어 할 때마다 짜증스러울 때가 있죠..

그렇다고 한영변환을 아예 막아버리자니 그렇고~~

이런경우에는 간단하게 설정해서 사용하시면 됩니다.

이런 경우에는 아래 순서대로 따라서 하시면 됩니다.

설명한 그림은 엑셀 2010 이지만 2007 등 다른 것도 찾으시면 메뉴는 거의 비슷한 곳에서 찾으실 수 있을 겁니다.

자동고침 옵션에서 한/영 자동고침을 완전히 해제를 해버리면 잘못하여 한글로 입력하고 있다고 생각하고 입력중일때 영문입력을 하고 있을때 완전히 지우고 새로 입력을 해야 하겠죠..

그래서 자동으로 변환되는 단어가 나오면 그 단어만 추가를 해주는 것이 좋습니다.

자동변환이 되지 않도록 입력값과 결과값을 동일하게 넣어줍니다.

확인을 다 하셨으면 이제 확인을 눌러주세요..

아래는 수식 자동고침을 할 값을 하나 추가를 해봤습니다.

MS워드에서는 --> 를 입력하면 자동으로 화살표 방향키로 바뀝니다.

동일(유사)하게 하려고 값을 넣어본 것입니다.

엑셀을 편하게 사용하기 위해서 좀 더 편리하고 간단한 팁을 추가해봤습니다.

위 그림처럼 테이블(자료)에서 원하는 걸 찾아서 값을 변경하고 싶다면 어떻게 해야할까요?

방법은 여러가지가 있을 수 있습니다.

오늘은 조건부 서식을 이용해서 한번 해보겠습니다.

블럭설정은 첫번째 지정하고 싶은 셀에서 Shift 누른상태에서 마우스로 마지막 셀을 선택해서 누르면 됩니다.

그 상태에서 Ctrl + D를 눌러주면 값이 변경되는 걸 확인하실 수 있습니다.

조건부 서식 텍스트포함은 어떤 특정한 글자가 들어간 것만 찾아서 색깔별 정렬을 해서 원하는 결과를 찾을 때도 매우 유용합니다.

다른 방법으로 해본 다면

값 대체하는 substitute 함수를 이용하는 방법입니다.

이렇게 한 다음에 값붙여넣기를 하고 나서 원래 수량 열은 지우고 값대체 열에 '수량'으로 변경만 해도 됩니다.

방법은 다양하게 해볼 수 있겠지요..

자주 사용하는 IF함수 사용법이랑 다른 함수들을 적절히 조합하면 됩니다.

직접 해보실 분을 위해 첨부파일 첨부합니다.

Excel_조건부서식.xlsx

엑셀 피벗을 뭘로 설명하면 좋을까 곰곰히 생각해보다가 로또당첨번호를 이용해서 분석해보면 어떨까 하는 생각이 들어서 로또당첨번호를 조회해봤더니 친절하게 정리해서 올려주신 분이 있어서 이 자료를 가지고 정리를 해볼까 합니다.

VLOOKUP 을 알면 여러가지 작업을 하는데 매우 편리합니다.

피벗작업하는 방법을 알면 분석하는데 여러가지로 편리합니다.

번호는 지난주인 2013년 3월 30일날 추첨한 번호까지 확인해서 추가했답니다.

번호는 총 6 개인데 번호가 뒤죽박죽 섞여 있군요.

피벗을 돌려서 각 번호별로 몇개나 당첨번호가 나왔는지 확인해보는 작업을 해보겠습니다.

눈으로 일일이 확인한다는 건 그야말로 힘든 일이겠죠?

가장 먼저 할일은 표 전체를 블럭설정한다.

새워크 시트에 피봇작업을 하도록 지정하면 아래 그림이 나온다.

여기서 구하고자 하는 걸 구한다.

로또번호는 무작위로 6개 이므로, 여기서는 각 열에 해당하는 걸 각각 구해야 한다.

즉, 번호1 열에 나열된 숫자가 몇개씩인지를 일단 구한다.

 여기서 왜 2등번호 개수를 지정했느냐고 따지지 마시길 ^^

다른 걸 가져다가 놓아도 결과를 동일합니다 

값을 구했으면 복사를 해서 값붙여넣기로 다른 시트에 추가를 한다.

다시 다른열을 작업하고 피벗결과값을 복사하여 다른 시트에 값붙여넣기를 한다.

이런식으로 6개의 열 모두를 피벗으로 결과를 얻어낸다.

이제는 결과로 얻어진 표를 가지고 블럭을 설정하여 다시 피벗 작업을 한다.

지금까지 가장 많은 당첨번호 숫자는 아래와 같군요

전체적으로 보면 가장 적게 나온 숫자는 58번 나왔네요.

가장 많이 나온 숫자는 40번이 90회 나왔구요..

어때요?

로또당첨번호로 피벗을 돌려보니 좀 와닿나요?

회사에서 일하는 걸로 하면 금방 자료 올리기도 편하지만 보안이라 불가~~

그래서 어떤 샘플을 찾아서 설명을 해야 하나 하는 고민이 되었답니다.

작업했던 샘플 파일 올리니 직접 해보실 분은 해보세요

로또당첨번호 이용한 피벗작업.xlsx