'업무 능력 향상/엑셀함수배우기'에 해당되는 글 24건

중복값 찾기(Countif 함수 활용)

엑셀 중복값 찾기를 Countif 함수를 이용한 방법으로 설명해보겠습니다.

품목에 중복되는 것이 어느정도 되는지 카운트를 해보고 싶다면?

그냥 =COUNTIF($A$2:$A$23,A2) 이런식으로 값을 넣고 죽 넣으면 어떤 현상이 발생할까요?

C열과 D열에 각각 중복값 숫자를 구해보겠습니다.

Countif(조건범위,조건) 함수는 하나의 조건을 만족하는 셀의 개수를 구해주는 함수입니다.

C2셀에 =COUNTIF($A$2:$A$23,A2) 를 입력하고 엔터를 치면 아래 그림처럼 나옵니다.

조건범위는 변동되지 않도록 절대값으로 지정했습니다.

이번에는 조건범위를 시작셀은 $A$2 로 절대값으로 지정했는데

마지막셀은 상대값 A2 로 지정을 했네요..

어떤 변화가 일어날지 감이 잡히시나요?

일단 죽 마우스로 Drag 하여 내려서 수식으로 채웁니다.

두개를 비교해봤더니 서로 결과가 다르네요. 왜 그런지 아시겠어요?

C열은 조건범위가 절대값 이었고

D열은 조건범위가 절대값:상대값으로 해서 조건범위가 달라지게 만들었다는 점입니다.

"대체 무슨 소리야?" 하시는 분들을 위해 차근차근 설명해보겠습니다.

=COUNTIF($A$2:A2,A2)  --> 조건범위가 A2이고 조건이 A2 이므로 1개

=COUNTIF($A$2:A3,A3)  --> 조건범위가 A2:A3 이고 조건이 A3 이므로 A2에서 1개, A3개에서 1개 그래서 2개

=COUNTIF($A$2:A4,A4)  --> 조건범위가 A2:A4 이고 조건이 A4 인데, 필통은 처음 나오는 것이니까 1개

이제 감이 잡히죠?

이러한 조건식을 지정하면 처음 나오는 것은 개수가 1개로 결과를 돌려줍니다.

따라서, D열에서는 숫자가 1인 것만 찾아내면 되는 거죠.

수식을 값으로 변경하고 숫자가 1인 것만 찾고 나머지는 지우겠다고 하는 경우라면 이 방법이 가장 좋습니다.

필터를 걸어서 정렬을 한 다음에 구한 개수를 확인해보면 육안 비교가 되니 더 의미가 와닿을 겁니다.

COUNTIF 함수를 이용한 중복값 검사를 해봤습니다.

이 함수식은 유용하게 사용되는 경우가 많으니 잘 활용하시기 바랍니다.

FIND와 ISNUMBER 로 원하는 결과 도출

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

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

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

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

ISNUMBER(식) : 식이 숫자이면 참, 아니면 거짓을 반환합니다.

IF함수는 IF(조건식,참,거짓) 입니다.

조건식에 IF(ISNUMBER(식),참,거짓) 으로 표시하면 원하는 결과를 찾을 수 있습니다.

=IF(ISNUMBER(FIND("유도등",B2)),"피난시설",IF(ISNUMBER(FIND("소화기",B2)),"소화시설",IF(FIND("감지기",B2),"경보시설","")))

수식이 간단하네요.

그런데 만약 데이타가 엄청 많다면 이런 함수식 많이 써주면 속도문제, 용량문제가 발생할 수 있습니다.

이럴 경우에는 아래 VBA Code를 사용하면 좋습니다.

Search_Character.vbs

Option Explicit
Sub Search_Character()
    Dim rngAll As Range         '// 검사할 구간 변수
    Dim rngTarget As Range      '//대상 범위 지정변수
    Dim rngC As Range
    Dim oldTime As Single       '// 걸린 시간 구하는 변수 지정
  
    Application.ScreenUpdating = False  '//화면 업데이트 일시 정지

      '// 이걸 지정해주어야 매우 빠르게 처리함
    oldTime = Timer     '// 시간 변수 설정
    Set rngAll = Range([B2], Cells(Rows.Count, "B").End(3))

      '// 검사할 열이 B열인 경우, B2열 시작점, 끝점은 자동감지
    Set rngTarget = Range([D2], Cells(Rows.Count, "D").End(3))

      '// 결과를 보여줄 열 범위 지정
    rngTarget.Clear    '// 셀 초기화
  
    For Each rngC In rngAll
        If InStr(rngC.Value, "유도등") > 0 Then
            rngC.Offset(0, 2).Value = "피난시설"
        ElseIf InStr(rngC.Value, "소화기") > 0 Then
            rngC.Offset(0, 2).Value = "소화시설"
        ElseIf InStr(rngC.Value, "감지기") > 0 Then
            rngC.Offset(0, 2).Value = "경보시설"
        Else
            rngC.Offset(0, 2).ClearContents
        End If
    Next rngC
  
    Set rngAll = Nothing '// 변수 초기화
    MsgBox "총 " & Format(Timer - oldTime, "#0.00") & " : 초 소요"
End Sub

예제 샘플 파일입니다.

FIND_ISNUMBER.xlsm

[엑셀] 데이터베이스 함수 DSUM 알아보자

데이터베이스 함수는 앞에 D(Database)를 포함하고 있으니 D가 들어간 함수가 데이터베이스 함수라고 이해하시면 좀 쉽겠네요.

도대체 데이터베이스가 뭐야? 인터넷 검색해봤더니 '특정 조직의 응용업무에 공동으로 사용하기 위하여 운영상 필요한 데이터를 중복을 최소화하여 컴퓨터 기억 장치내에 모아 놓은 집합체' 라고 나오네요.

보통 엑셀에서 다루는 데이터라고 해봐야 많아봐야 몇만 LINE(행) 입니다.

하지만 전문적으로 데이터를 다루는 데이터베이스 툴은 어마어마한 양의 데이터를 쉽고 편하게 다룰 수 있도록 만들어져 있습니다.

공짜로 사용할 수 있는 데이터베이스 툴도 있고 상용 데이터베이스 TOOL도 있습니다.

오라클, MYSQL, MSSQL 등 이런 걸 들어보셨을 겁니다. 이런 TOOL들이 데이터베이스 TOOL입니다.

블로그에 작성하는 글도 모두 데이터베이스에 저장된다고 보면 됩니다.

데이터를 저장할 때 등록한 날짜도 기록하고, 작성자가 누군지도 기록하고, 작성하는 내용이 뭔지도 기록하고요.

테그달기도 기록 등등 구분할 수 있는 걸 각각 구분하는 걸 필드라고 이해하시면 됩니다.

그런 각각의 필드에 기록된 정보중에서 내가 필요로 하는 값만 뽑아내보자....

아래 그림을 보시고 그냥 단순하게 이걸 표라고 이해하지 말고 데이터베이스라고 이해를 하셔야 합니다.

여기서는 행이 몇행 안되지만 이 행이 수만 LINE 이라고 가정해보고 접근해봅시다.

날짜, 공급처, 품목, 단가, 수량, 가격이 바로 필드(Field)라는 구분자 입니다.  

그 아래 2행부터는 실제 데이터가 저장되는 값이구요.

오늘 다룰 DSUM 함수의 의미를 알아보겠습니다.

보통 문제가 이렇게 나옵니다.

공급처가 동아이고 수량이 20개 이상인 가격의 합계를 구하라.

여기서 조건은 뭘까요?

필드명이 공급처이고 데이터는 동아 AND 필드명 수량이고 데이터는 20개 이상인 것이 조건입니다. 

가격의 합계를 구하라 는 조건을 만족하는 가격 필드의 데이터만 합산하라는 뜻입니다.

그럼 그림을 다시 그려서 살펴보겠습니다.

데이터베이스는 표 전체 구간입니다.

필드는 몇번째 열인가? 6번째 열이군요. 그럼 6을 써주면 됩니다. 아니면 직접 필드명에 해당되는 F1 을 직접 써도 되구요. 

조건은 AND 조건도 있고 OR 조건도 있습니다.

조건식을 표현할 때 AND 조건과 OR 조건은  

필드명 이름을 적는 줄과 필드명 아래줄에 구하고자 하는 조건을 적습니다. (필드명은 실제 존재하는 필드명을 써주어야 함) 

이때 구하려는 조건을 같은줄에 적으면 AND 조건이구요. 다른 줄에 적으면 OR 조건입니다.

그럼 DSUM 함수를 써서 AND 조건과 OR 조건을 만족하는 합계를 한번 구해보도록 하겠습니다.

다른 예제를 하나 더 살펴볼까요?

소속이 부산이고 준우승횟수가 5회 이상인 기수들의 우승횟수의 합을 구하라. 

먼저 조건식을 구해야 합니다.

노란색 표시부분이 조건식입니다.

그리고 조건을 만족하는 DSUM 함수를 수식에 맞게 써주면 됩니다. 

데이터베이스 함수에는 DSUM 함수만 있는게 아니고

DAVERAGE 함수는 데이터베이스 조건을 만족하는 평균을 구하는 함수

DCOUNT 함수는 데이터베이스 조건을 만족하는 숫자가 들어있는 셀의 갯수를 구하는 함수 

DCOUNTA 함수는 데이터베이스 조건을 만족하는 숫자/문자가 들어있는 셀의 갯수를 구하는 함수 

DMAX 함수는 데이터베이스 조건을 만족하는 최대값을 구하는 함수 

DMIN  함수는 데이터베이스 조건을 만족하는 최소값을 구하는 함수 

가 있습니다. 

DSUM_sample.xlsx

[엑셀] Subtotal 함수

Subtotal 함수는 자동필터와 같이 사용하면 유용한 함수 입니다.

피벗테이블 다루는 걸 알면 굳이 Subtotal 함수를 사용하지 않아도 유용하게 원하는 걸 얻을 수도 있습니다.

숨겨진 값 무시하는 건 딱 100 숫자가 더 많아요..

하지만 외울 필요 전혀 없어요...  

함수사용은 원하는 필드에 =SUBTOTAL( 를 치고나면 옵션을 선택하는 창이 자동으로 떠요.

그런다음에 원하는 구간 마우스로 지정하면 설정됩니다.

함수사용은

subtotal(인수,조건범위)

아래 그림처럼 자동필터를 지정해놓은 상태에서 SUM 을 한 값과 SUBTOTAL 함수를 지정하여 합계를 지정한 값을 비교해 보겠습니다.

품목이든, 날짜든, 공급처든 원하는 걸 눌러서 선택하면 되는데요.

여기서는 그림 자르기 편하게 그냥 품목으로 설명하겠습니다.

아래 2번 항목에서 독서대만 선택하겠습니다.

독서대만 선택했더니 .....

행번호가 1, 2, 5, 12, 16 번호가 보이는군요.

즉 필요한 번호만 보이는 것입니다.

그리고 SUBTOTAL 함수를 적용한 값은 변경된 것이 보이시죠?

육안으로 가격을 더해서 계산해도 같은 값인 걸 알 수 있을 겁니다.

하지만 전체 적용된 SUM 의 값은 변동이 없습니다.

이처럼 원하는 부분항목의 값을 구하고자 할 때 유용하게 사용할 수 있는게 SUBTOTAL 함수입니다.

좀더 그림을 통해서 공급처도 변경해보고 날짜도 변경해보겠습니다.

위 그림을 보시면 공급처에도 필터가 적용되었고, 품목에도 필터가 적용되었습니다.

SUMIFS 함수처럼 두개 이상의 조건을 만족하는 값을 구하고자 하는 것도 쉽게 값을 구할 수 있습니다.

5월에 동아에서 공급한 것의 가격을 구해보겠습니다.

필더를 날짜, 공급처를 각각 원하는 것만 체크해주면 됩니다.

SUM 과 SUBTOTAL 함수를 같이 놓고 비교를 해봤는데 이해가 쉽게 되었는지 모르겠네요..

숨겨진 값 무시와 포함이 제대로 안된다는 분이 계셔서 그 부분도 그림으로 포함합니다.

합계를 구하는데 숨겨진 값은 무시하라는 조건을 하려면 9 가 아닌 109로 변경을 하면 됩니다.

값을 9로도 해보고 109로도 해보면 어떤 변화가 발생하는지 확인이 됩니다.

합계뿐만 아니라 다른 것도 유용하게 사용할 수 있는게 옵션 조정을 할 수 있도록 만든게 SUBTOTAL 함수 이므로 알아두면 여러모로 편리합니다.

Subtotal_sample.xlsx

첨부파일 첨부하니 연습해보실 분은 직접 해보세요..

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

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

한번 살펴보겠습니다.

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

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

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

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

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

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

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

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

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

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

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

[엑셀] 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문을 사용하고 액셀 객체를 연결해서 작업을 하도록 만들 때 상대적인 개념은 매우 유용하게 사용되더군요.

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

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

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

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) 처럼 텍스트를 숫자로 인식시키는 함수를 사용한 다음에 다른 셀에 값만 붙여넣기를 하면 된다. 

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

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

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

무슨 말이냐면

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

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

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

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

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

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

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

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

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

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

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

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

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

FIND_array_sample.xlsx

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

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

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

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

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

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

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

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

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

이렇게 나옵니다.

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

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

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

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

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

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

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

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

다른 방법으로 해본 다면

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

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

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

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

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

Excel_조건부서식.xlsx

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

어때요?

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

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

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

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

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

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

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

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

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

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 함수를 다뤄보겠습니다.

인터넷에 VLOOKUP만 치면 주루룩 수없이 많은 설명과 강좌들이 나온다.

회사에서 VLOOKUP 쓰면 된다는 얘기를 들은지는 참 오래되었다.

그런데 어떻게 쓰는 것인지 개념 이해하는데 잘 이해가 안되었다고 해야 하나?

그래서 개념을 이해하고 활용하는 방법에 대해서 정리를 해볼까 한다.

현재 사용하는 엑셀버전은 2010 버전이다. 하지만 낮은 버전에서도 다 되는 기능이니 2010 버전용이라고 생각하진 말아주세요.

하고 싶은 사항

단가표로 정리된 항목을 참조해서 원하는 값을 쉽게 구하고자 할 때

찾고자 하는 데이터가 너무 많은데 그중에서 서로 같은 것만 찾아서 표기하고 싶을 때

등에 주로 활용된다.

위 그림을 보자.

단가를 일일이 찾아서 적자니 노가다 작업이다. 만약 우측에 표기된 단가표가 수백줄이라도 된다고 해보자.  

그 수많은 단가표에서 원하는 것만 쏙 골라서 왼쪽 표에 나오는 몇개만 표기를 하고 싶은데 어떻게 해야 할까?

(설명을 위해 참조할 표를 같은 SHEET 에 넣어둔 것임. 보통은 다른 SHEET 또는 다른 파일에서 있다) 

우리는 이럴 경우 VLOOKUP 함수를 이용하면 원하는 결과를 쉽게 찾아낼 수 있다.

그럼 VLOOKUP 함수 개념이 뭔지 이해를 해보자.

그러면 함수식은 어떻게 쓰는가?

일단 쉽게 이해하기 위해서 엑셀에서 제공하는 함수식으로 설명할때니 한번 보면 이해가 될 것이다.  

(이해를 돕기 위해서 첨부파일로 죽 설명을 해두었으니 이 그림을 보면서 고대로 따라해보면 될거다.)

왜 여기서 절대값으로 표기되도록 변경하느냐고요?

가져올 값을 편하게 죽 가져오기 위해서는 해당되는 표의 영역은 변경되면 안되니까요

F4키를 여러번 누르다보면 절대값, 상대값이 바뀌는 걸 확인할 수 있다.

어떤 경우에는 $G7:$I21 로 해서 값을 주어야 할 경우도 생긴다.

절대값, 상대값은 적절하게 사용하면 유용한 경우가 많다. 

Table_array(배열)에 쓰여진 값을 보면 절대값으로 변경된 것을 알 수 있다.

좀 숙달되면 몇번째열? 음 2번째 열이지 그럼 2, FLASE는 0을 선택하면 되지 하는 걸 이해하니까

2,0 을 바로 선택해 버리게 된다. 

Range_lookup 을 생략하거나 True로 하면 100% 일치하는 것이 아닌 비슷한 것을 찾아서 결과를 반환한다.

이제 가져온 값을 확인해보자

이제 이해가 되셨나요?

정리를 하자면  

VLOOKUP(조건 비교하고자 하는 셀, 조건이 들어간 데이터 배열, 선택한 배열의 몇번째 열을 가져올 것인가 지정, FALSE(정확하게 일치하는 값만 가져와라))  

 ㅇ 조건이 들어간 배열은 타 SHEET, 타 엑셀파일 등에 있는 SHEET 도 포함하여 지정할 수 있음 

여기까지만 하면 90점짜리입니다. 한가지 더 마무리를 해줘야 힙니다. 

보통 VLOOKUP 함수를 사용하는 분들은 데이터량이 엄청 많아서 사용할 겁니다.

그러므로 VLOOKUP 함수를 사용해서 원하는 결과를 얻었으면 수식을 값으로 변경하는 작업까지 해줘야만

로딩속도, 파일사이즈 등이 작아집니다. (시험보는 분한테는 해당 없는 내용, 시험은 수식을 물어보는 사항)

수식으로 남아 있는 걸 값으로 변경하는 작업이다.

이런 걸 습관 들이시면 업무하는데 훨씬 유용합니다.

값으로 변경하고 나면 소팅(정렬)하기도 쉽고 여러모로 분석하는데 좋습니다.

첨부파일 올리니 참고하실 분은 받아서 해보시기 바랍니다.

Vlookup_sample.xlsx

이런 개념 알고 나면 엑셀이 참 쉬워집니다.

그런데 자료량이 너무 많으면 VLookup 함수 사용하는 것보다는 VLookup VBA 를 이용하는 편이 좋습니다.

버튼 한번만 클릭하면 바로 원하는 결과가 몇초 안걸리고 나옵니다.

파일사이즈 커질 일도 없구요..

이것도 알고 싶으신 분은 VBA 기초 카테고리에 나온 VLookup VBA 를 보시면 개념은 금방 이해될 겁니다.

엑셀에서 날짜 관련 함수를 잘 다루면 유용하게 사용할 수가 있습니다.

TODAY() 함수는 시스템에 설정된 오늘 날짜는 표시해준다

셀에 =TODAY() 를 넣어주면 2013-03-28 이란 날짜를 반환해준다

NOW() 함수는 시스템에 설정된 오늘날짜와 현재 시간을 표시해준다.

셀에 =NOW()를 넣어주면 2013-03-28 08:10 이란 날짜와 시간을 반환해준다

WEEKDAY(날짜, 옵션) 함수는

날짜에 해당하는 요일을 숫자로 반환해준다.

일요일은 1, 월요일은 2, 토요일은 7을 반환

옵션 2를 사용하면, 월요일 1, 화요일 2, 수요일 3, 목요일 4, 금요일 5, 토요일 6, 일요일 7 을 반환

여기서 잠시 월요일을 표시해주는 팁을 알아보자.

숫자로 돌려주지 않고 문자로 돌려받기 위해서 CHOOSE 함수를 사용하거나 텍스트함수를 이용하는 방법을 알아봤는데요

WEEKNUM(TODAY()) 함수는 오늘이 일년중 몇번째 주인지 알려주는 함수 이다.

셀에다 =WEEKNUM(2013-05-12) 라고 입력을 하면 25 이라고 값을 반환해준다

우리가 단순히 시작일과 종료일을 구하고자 할 때는 두가지 방법이 있다.

그냥 뒷날짜에서 앞날짜를 빼주기만 해도 되고

DATEDIF(시작일, 종료일, 단위) 함수를 사용해도 된다.

DATEDIF 함수는 시작일부터 종료일 사이의 기간을 '지정한 단위'로 구한다.

'지정한 단위'는 'Y' : 연도수 구하기

                      'M' : 총개월수 구하기

                      'D' : 총일수 구하기

                     'YM' : 연도수를 제외한 나머지 월 수만 구하기

                     'MD' : 연도수 제외하고 월수 제외한 순수한 일자만 구하기

예제를 가지고 좀 더 확인을 해본다면....

여기서 근속기간 구하는 함수는 =DATEDIF(A43,B43,"y")&"년 "&DATEDIF(A43,B43,"ym")&"개월 "&DATEDIF(A43,B43,"md")&"일"

 한글 나오는 "년", "개월", "일"을 어떻게 표현했는지 보면 알 수 있다.

 & 로 묶어서 표현하는 것은 간단하면서도 매우 유용하게 사용하는 것이니 알아두면 유용하다.

NETWORKDAYS(시작일, 종료일, 휴일) 함수는 두 날짜(시작일, 종료일) 사이의 평일 수를 반환. 휴일을 지정하면 지정한 휴일수 만큼 제외됨

휴일은 달력보고 평일이면서 공휴일로 된 날짜만 별도로 찾아서 표시를 했음

시작일과 종료일 사이에 실제로 근무한 일수는 얼마인지 알고 싶을 경우에 사용하는 함수라고 보시면 됩니다.

별도 휴일 지정하지 않고 간단하게 평일수를 구한다음에 공휴일만 달력에서 찾아도 되겠죠.

날짜에서 년도를 추출하는 함수, 날짜에서 월을 추출하는 함수, 날짜에서 일자를 추출하는 함수는

이다.

입사일로부터 근속기간 구하는 함수는

=DATEDIF(입사일,TODAY(),"M")&"개월"

엑셀을 다루면서 접하는 오류들입니다.

무슨 말인지 알아두면 오류 발생할 때 찾기가 쉽겠죠?

오류값 표시

1) ##### : 셀에 입력된 숫자값이 너무 커 셀 안에 나타낼 수 없을 경우 또는 날짜와 시간이 음수 값일 때 발생한다

2) #VALUE! : 잘못된 인수나 피연산자를 사용했을 때 발생한다. 예를 들어 계산식에 문자가 입력된 셀이 포함될 경우에 발생한다

3) #DIV/0! : 수식에서 값을 0으로 나누려고 할 때 발생한다.

4) #NAME? : 엑셀에서 인식할 수 없는 문자열을 수식에 사용했을 경우나 수식에서 참조하고 있는 이름을 삭제하였을 때 발생한다

5) #N/A : 함수나 수식에 사용할 수 없는 값을 지정했을때 또는 함수에서 부적당한 인수를 사용했을 때 발생한다

6) #REF! : 셀 참조가 유효하지 않거나 참조된 셀이 삭제된 경우에 발생한다

7) #NUM! : 수식이나 함수에 숫자와 관련된 문제가 있을 때 발생한다

8) #NULL! : 교차하지 않는 두 영역의 교점을 지정했을 때 또는 교차하지 않는 두영역을 교차하는 것으로 지정할 때 발생한다

엑셀을 이용하다보면 소팅(Sorting)을 하지 않고 순서를 알고 싶을 때가 있죠.

이럴 경우에 RANK 함수를 이용하면 됩니다.

ORDER 는 지정하지 않으면 내림차순이 default 값이다.

order : 0 -- 내림차순,  1 --- 오름차순

  큰 값부터 내려가는 건 내림차순 정렬

  작은 값부터 올라가는 건 오름차순 정렬 

IF문은 조건식이 참이면 참을 보여주고, 거짓이면 거짓을 보여주는 함수이다. 

상황에 따라 제외시켜보기도 하고 포함시켜 보기도 해야 한다면 이렇게 하면 된다.

조건문 IF를 사용해서 조건문이 0 이면 빈칸으로 만들고, 값이 0이 아니면 순위를 넣어라.

만약 - 값도 있는데 + 값인 경우만 하고 싶다면 조건문을 RANK함수 안에 범위에다가 넣어주면 된다.

제외라는 필드에 값이 0으로 바뀌면 합계 필드 값도 같이 0으로 변경되도록 수식을 지정해준것과

총계는 제외 필드 여부와는 관계없이 값이 변경되지 않는다.

이럴 경우 순위 지정을 값이 없는 건 빠지도록 하고 싶다면 .....

부족하지만 파일을 첨부로 올리니 적용된 수식을 한번 해보시면 도움이 되실 겁니다.

rank.xlsx