'엑셀'에 해당되는 글 11건

셀 분리 활용

데이터가 하나의 셀로 되어 있어서 두개로 분리를 해야 할 경우가 있습니다.

아래 VBA 코드를 가지고 msgbox Ubound(varTemp) 도 넣어서 F8 키를 눌러서 확인 등을 해보면 확실하게 알 수 있습니다.

하나하나 분리해야 할 때에는 For i Next 구문 대신에 rngC.Next.Resize(1,Ubound(varTemp)) = varTemp 를 넣어주면 됩니다. 

Sub 셀분리()

    Dim rngC As Range

    Dim rngAll As Range

    Dim i As Integer

    Dim varTemp

    Dim deLimiter, sTxt As String

    Set rngAll = Range([A2], Cells(Rows.Count, "A").End(3))

    deLimiter = " "     '// 구분자

    For Each rngC In rngAll

        varTemp = Split(rngC, deLimiter)  '// 구분자로 셀을 분리하여 varTemp 배열에 저장

        For i = LBound(varTemp) To UBound(varTemp) - 1   '// 배열의 가장 작은 숫자와 가장 큰 숫자를 추출

            '// i = 0 부터 시작

            sTxt = sTxt & varTemp(i) & deLimiter   '// For 문에서 지정된 것만큼 셀을 합쳐서 하나의 sTxt  로 만듬

        Next i

        rngC.Next = sTxt    '// 현재 셀 오른쪽에 sTxt 를 저장

        rngC.Offset(0, 2) = varTemp(UBound(varTemp))   '// 마지막 부분을 현재셀 오른쪽 2번째 셀에 기록

        sTxt = vbNullString     '// 값을 초기화

    Next rngC

End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

어때요?

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

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

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

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

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

엑셀 작업을 하다보면 셀을 자동으로 채워서 작업을 해야 하는 경우가 부지기수로 많답니다.

특히 번호를 붙여두면 여러모로 작업이 편리한 경우가 많아요

값이 존재하는 왼쪽이나 오른쪽에 자동번호 붙이기 같은 걸 하는 경우

아래 그림처럼 번호(No)를 쓰고 숫자 1과 2를 쓴 다음에 1 과 2가 들어간 셀을 선택하고 나서 우측 하단을 더블클릭한다.

C열의 마지막 값이 들어간 셀까지 자동으로 채우기가 순식간에 된다.

눌러서 셀복사도 해보고, 자동채우기도 해보시기 바랍니다. 어떤 변화가 생기는지 확인해보라는 의미입니다.

C열과 D열의 값이 존재하는 경우에는 번호 자동붙이기는 쉽게 해결이 가능합니다.

중간에 값이 하나가 비어 있을 경우에는 어떻게 되는지 직접 확인해보시기 바랍니다.

그런데.........

왼쪽과 오른쪽에 아무것도 없는 경우에는 마우스 더블클릭만으로 자동채우기가 될까요?

해보시면 움직이지 않는다는 걸 아시게 될 겁니다.

이 경우에는 마우스를 Drag 하여 끌고 죽 내리는 방법으로 해야 합니다.

마우스를 Drag 하여 끌어서 채우는 것은 1,3 으로 놓고 채우기를 하면 아래 그림처럼 채워집니다.

1월, 2월 채우기도 가능하죠.

채우기를 간단하게 하는건 참 쉬워요.

만약 채워야할 데이터가 5000개를 채워야 한다고 하면 어떻게 해야할까요?

계속 마우스를 Drag 한채 죽 아래로 내려야 할까요?

그렇게 한번 해보세요..

제가 여기서 설명드릴 방법은 ^^

틀고정 기능을 활용해서 Drag 하면 빠르고 쉽고 편하다는 겁니다.

이 정도면 데이터가 많은 경우에도 자동채우기 쉽게 할 수 있겠죠?

이번에는 엑셀 다루면서 알아두면 좋을 TIP 에 대해서 알아보겠습니다.

엑셀 작업을 하다보면 자주 사용하는 단축키는 아래 그림처럼 빼놓고 사용하는게 여러모로 편리하고 좋아서 여러분에게 소개합니다.

중요한 것은 본인이 작업을 하다가 자주 사용하는 단축키를 정해서 편하게 사용하면 되는 겁니다.

이밖에 그냥 외워두면 편리한 단축키로는

셀서식을 바로 불러쓰는 CTRL + 1

셀을 아래로 밀거나 오른쪽으로 밀고 싶을 땐

CTRL + PLUS KEY(+)

사실 이 키는 굳이 몰라도 워낙 자주 마우스 우클릭하여 금방 찾아서 사용하는 거라 굳이 몰라도 된다.

작업을 하는 경우에는 CTRL + D, CTRL + R 키를 눌러서 작업하는 것만 알아도 편하다.

(참조 http://link2me.tistory.com/entry/%EC%97%91%EC%85%80-%EB%8F%99%EC%9D%BC%ED%95%9C-%EC%85%80-%EB%82%B4%EC%9A%A9%EC%9D%84-%EC%97%AC%EB%9F%AC%EC%85%80%EC%97%90-%EB%B3%B5%EC%82%AC%ED%95%98%EB%8A%94-%EB%B0%A9%EB%B2%95 )

다른 이름으로 파일을 저장하고 싶다면 작업도중에 그냥 F12키를 눌러주면 된다.

단축키 몰라도 일하는데 크게 지장은 없습니다만 그래도 알아두면 여러모로 편하고 좋아서

제가 아는 방법을 소개해 봤습니다.

엑셀 자료를 찾다보면 두개의 셀이 동시에 일치하는 경우에 해당되는 값을 찾아서 가져오고자 할 경우가 있다.

이럴 경우 두개의 셀 옆에 하나의 셀처럼 &를 써서 만들어서 사용해도 되는데 좀 귀찮다.

손대지 않고 할 수 있는 방법이 있는지 찾아봤는데, 방법이 있어서 잘 되는지 확인하고 포스팅을 합니다.

엑셀 가지고 모든 걸 할 수 있는 건 아니지만 업무를 하면서 유용하게 활용할 수 있는 것 만으로도 정말 대단하다는 생각이 든다.

MATCH(lookup_value,lookup_array,match_type)

* lookup_value 가 하나의 셀값일 수도 있지만 여기서는 두개의 셀값을 마치 하나처럼 인식시키는 A2&B2 로 &로 묶어줄 수 있다.

* lookup_array 는 하나의 행만 표시하는 배열인게 대부분이지만

                         G2:G22&H2:H22 로 두개의 배열을 & 로 묶어서 처리가 가능하다.

                         값이 변하지 않게 구간설정을 하기 위해서는 모두 절대값 처리를 하면 된다. F4키룰 눌러서 처리하면 된다.

* match_type) : 0 --> 정확하게 일치하는 값을 찾아라

아래 그림에서 가져오고자 하는 값을 화살표로 표기했다.

품목과 코드 두개가 일치할 때 원하는 값을 뽑아서 가져오면 되는 거다.

아래처럼 MATCH(1,(G4:G22=A3)*(H4:H22=B3),0) 로 해서 처리하면 원하는 결과를 얻을 수 있다.

여기서 주의할 점은 그냥 엔터키를 치면 원하는 값이 나오지 않는다.

반드시 CTRL + SHIFT + Enter 키를 눌러야 한다

아래처럼 MATCH 함수값을 지정해도 결과는 동일하게 나온다.

마찬가지로 반드시 CTRL + SHIFT + Enter키를 입력해야 한다. 

두개의 셀이 일치하는 조건에 맞는 값을 가져오고자 할 경우에

INDEX, MATCH 함수를 사용하면 원하는 결과를 얻을 수 있다.

필요하신 분은 첨부파일 받아서 직접 눈으로 확인해보세요.. ^^ 

Index_match_array_sample.xlsx

현재 만들어진 자료를 손대지 않고서 작업을 하고자 할 경우에 VLOOKUP 함수로 다 해결할 수 없다는 아쉬움이 있습니다.

VLOOKUP 함수를 사용할 때 주의할 점은 VLOOKUP보다 왼쪽에 있는 값을 가져오려고 하는 건 불가능하다는 거다

왜냐구?! 비교할 값을 항상 배열의 첫구간으로 설정해야 한다는 거다

그래서 VLOOKUP 함수를 사용할 때는 비교하고자 하는 값이 왼쪽에 있으면 해당열 전체를 복사하여 오른쪽으로 옮기거나

복사를 하여야 한다.

그런데 이 표를 그대로 둔 상태에서도 VLOOKUP처럼 가져올 수 있는 함수가 있다.

그건 INDEX, MATCH 함수를 조합하여 사용하면 됩니다.

VLOOKUP 함수와 INDEX, MATCH 함수를 알면 업무를 하면서 원하시는 걸 쉽게 처리할 수 있습니다.

여기에 한가지 더 피봇까지 잘 다루면 참 편하게 원하는 결과를 도출할 수 있답니다.

그럼 다음번에는 INDEX, MATCH 함수를 다뤄보겠습니다.

엑셀 FIND 함수를 이용한 방법을 알아보겠습니다.

FIND 함수

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

  - FIND 함수는 대문자, 소문자를 구분하여 값을 찾는다. 한글, 영문 구분없이 문자 하나를 1로 인식한다. 

  - FINDB 함수는 대문자, 소문자를 구분하여 값을 찾는데, 영문 문자는 1, 한글 문자는 2로 인식한다.

  - 대문자, 소문자 구분없이 찾고자 한다면 SEARCH 함수를 이용하면 된다. 

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

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

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

FIND 함수는 찾는 값이 들어 있으면 찾는 값의 위치를 반환한다.

만약 찾는 값이 없으면 아래 그림처럼 에러를 표시한다. 

 여기에 COUNT 함수를 더 적용하면

'마포로' 라는 텍스트가 여러번 들어가면 1이상의 숫자로 표기됨

즉, 마포로 마포로 라는 텍스트가 포함되어 있다면 결과는 2를 표기

마포로 라는 텍스트가 없다면 0 을 표기 

셀에 찾는 값이 들어 있으면 "있음", 없으면 "없음"으로 표기하고 싶다면

IF(COUNT(FIND("마포로",E2))>0,1,0)

으로 적어준다.

끝까지 다 적용했으면  

수식 --> 값으로 변경해주는 작업을 해준다.  (http://jsk005.blog.me/150158476649 게시물 참조)

이제 FIND 필드기준으로 내림차순 정렬을 하면  

원하는 값을 찾아서 작업한 결과가 나온다.

엑셀 텍스트 정렬

작업을 하다보면 원하지 않게 아래 그림과 같은 결과가 나옵니다

그럼 어떻게 해야 할까요?

아래 적용된 함수를 보면 A셀과 B셀을 &로 묶어서 표기를 했다.

함수를 concatenate 함수를 사용해도 되지만 간단하게 아래처럼 활용하는 것도 편하다.

숫자부분을 2자리수로 표기되도록 하는 함수인 TEXT함수를 이용하면 된다.

전체 적용을 위해 3번 실행을 한다.