엑셀 같이 원래 장르가 뭐든 문서로 활용할 수 있는 프로그램에서는 검색기능은 필수입니다.

많은 프로그램처럼 엑셀도 영단어 find의 Ctrl + F를 사용하고 있습니다.

엑셀의 찾기 기능과 바꾸기(Ctrl + H) 기능이 붙어 있고 단축키는 달라도 같은 창이 뜹니다.

찾기 기능에서 문제가 있을 때 해결 방법에 대해서 알아보겠습니다.

1. 찾는 값이 수식일 때 

아래 그림처럼 분명하게 숫자가 있는데 검색을 못하는 경우가 있습니다.

이런 경우 때문에 Ctrl + F를 안쓰는 경우를 많이 보는데 주 원인은 수식임에 있습니다.

엑셀은 기본적으로 수식을 검색하도록 되어있습니다.

결과는 "15"라는 숫자로 표시되지만 수식은 아래처럼 "=C7+C6"으로 입력되어있습니다.

이 때 C7을 검색하면 검색이 되지만 15를 찾기 위해서는 옵션을 변경해야 합니다.

< 해결방법 >

① 찾기 및 바꾸기 하단의 "옵션" 단추를 클릭합니다.

② "찾는 위치"를 "값"으로 변경합니다.

③ 수식에 들어있더라도 결과값으로 찾습니다.

이것만 알아도 대부분 해결될 것 같습니다.

그런데 셀이 수식이 아닌데도 안찾아 진다

그럴 때는 셀이 보호되고 있을 확률이 높습니다.

2. 시트 보호 해제하기

상단 매뉴의 [검토] - [보호] - [시트 보호] 혹은 [통합 문서 보호]가 있습니다.

여기서는 다양한 기능에 Lock을 걸어서 사용에 제약을 거는 매뉴입니다.

이 매뉴에서 "잠긴 셀 선택"이 해제되어 있으면 검색기능을 활용할 수 없습니다.

<해결방법>

간단하고 당연하게 시트 보호기능을 꺼주면 됩니다.

만일 푸는 비밀 번호를 모를 때는 파일을 관리하는 관리자에게 물어봐야 할 것입니다.

3. 찾는 문자가 와일드 카드(~, *, ?) 일때

엑셀에서는 검색 와일드 카드를 제공합니다.

~는 부정을 *는 모두다 ?는 한글자에 한해서 모두다 입니다.

그런데 찾을 값 자체가 ? 글자다 이러면 검색 기능이 잘 동작하지 않습니다.

<해결방법>

이럴 때는 검색을 하는 글자 앞에 ~를 붙이면 됩니다.

통상적인 상황에서 ***를 검색하면 값이 있는 모든 셀을 대상으로 합니다.

앞에 ~를 붙여주면 단일 *** 글자를 검색합니다.

사방에서 번호를 찾을 수 있습니다.

전자제품이나, 학생번호, 시설번호 등등 다양한 일련번호를 사용합니다.

이런  번호에는 그 자체로도 어느정도 정보를 넣어서 만드는데 대충 아래 같은 것들을 넣습니다.

엑셀에 익숙하신 분들은 이런 형식에 일련번호에서 "제작날짜"만 추출하거나 하는 경우가 있을 것입니다.

(참고링크 : 엑셀(EXCEL) 글자 수로 짤라 추출하는 LEFT, RIGHT, MID함수로 텍스트 편집)

그런데 이건 형식이 똑같은 경우에만 사용할 수 있고 두개 이상의 형식이라면 어려워집니다.

밑의 예시에 모델명을 보면 회사가 달라 서로 다른 형식으로 번호가 부여되어 있습니다.

<< 사용할 예시 >>

A사는 모델 고유명이 앞의 4개 영문이고 "-"로 구별되어 있습니다.

B사는 앞의 3개의 영문명이 모델 고유명이고 " "(공백)으로 구별되어 있습니다.

이 때 앞의 모델 고유명만 뽑아오는 함수를 작성하겠습니다.

먼저, Find 함수와 IFERROR 함수에 대해서 알아야 합니다.

Find 함수 소개 링크

IFERROR 함수 소개 링크

<< 함수내용 >>

IFERROR 함수와 Find 함수를 조합한 것으로 내용은 

"=IFERROR(LEFT(C3,FIND("-",C3)-1),LEFT(C3,FIND(" ",C3)-1))"가 됩니다.

조금 복잡한 함수에 대해서 설명하겠습니다.

① FIND 함수 : 문자 안에서 "-"가 어디 있는지 찾아냅니다. "-"까지는 필요 없음으로 -1을 적용합니다.

② LEFT 함수 : 문자에서 "-" 바로 앞까지 출력하면 모델 고유명이 됩니다.

③ IFERROR 함수 : 다른 회사는 "-"를 쓰지 않기 때문에 ERROR가 발생합니다. 그럼 다음항으로 넘어갑니다.

④ FIND 함수(두번째) : 문자 안에서 " "를 찾습니다. " "은 필요없음으로 -1을 적용합니다.

⑤ LEFT 함수(두번째) : 문자에서 " " 바로 앞까지 출력하면 모델 고유명이 됩니다.

이 방식으로 3가지 4가지도 적용해서 진행할 수도 있습니다.

하지만 함수는 일괄적용이라서 동시에 두 가지 문자를 같이 사용한다던가, 여러 복잡한 형태가 이어지면 한계가 있습니다.

이건 좀 원본 데이터가 조금은 단순해야 사용할 수 있는 것이라고 생각해 주세요.

단순한 제 시트에서는 정상적으로 적용되는 것을 확인했습니다.

만일 복잡해지면 VBA나 임시시트를 활용하는등 방법을 찾아야합니다.

계속 복잡해지면 언젠가는 DB 개념도 나올 것입니다. 이때는 더이상 엑셀이 문제가 아니겠죠.

엑셀에서는 수식을 입력하거나 함수를 사용할 수 있는데 함수마다 제약이 있습니다.

한도를 넘기거나 제약을 어기게 되면 에러가 발생하여 오류 표시 문자가 발생합니다.

예를 들어 0으로 나누려고 하면 #DIV/0! 에러가 텍스트를 더하려고하면 #VALUE! 에러가 뜹니다.

오류를 피해가면서 문서를 작성해야 하겠지만 그게 어려운 경우도 있습니다.

위의 일간 생산량처럼 주말에는 일을 안하기 때문에 평균값을 계산하는 AVERAGE 함수가 에러가 납니다.

함수 내부에서 표본이 0개가 되어 0으로 나누려고 하기 때문입니다.

그냥 넘길 수도 있지만 이 문서가 다른 곳에 쓰이거나 할 때 보기 싫거나 다른 오류를 낼 수 있습니다.

그럴 때 사용할 수 있는 IFERROR 함수를 소개합니다.

IFERROR(value, value_if_error) : 수식에서 오류가 발생할 때 오류를 처리할 수 있습니다. 오류가 발생하지 않았을 때는 원래 수식을 반환합니다.

• value : 오류를 검사할 항목으로 단순한 숫자나 텍스트는 전부 참으로 처리합니다.
• value_if_error : value 항목이 오류일 때 반환할 값 혹은 수식입니다.
  에러 : #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!
• 엑셀의 수식이 오류로 반환할 때 작동하고 필수요소가 없다든지 하는 일반오류일 때는 동작하지 않습니다.

참과 거짓을 판별하는 if 함수들과는 다릅니다.

애시당초 엑셀에서는 거짓 False와 오류는 다른 개념이기도 하고요.

에러인 #VALUE!를 타이핑해서 입력한다고 에러로 판단하지는 않습니다.

< 예시 >

설명은 단순했지만 예시가 굉장히 다양합니다.

value_if_error 칸에 ""을 입력하면 빈칸으로 처리할 수 있고, 0을 넣어도 됩니다.

그외에도 다른 값, 참조, 수식을 실행시킬 수 있고 다중 IFERROR 문도 가능합니다.

엑셀에서는 보통 여러개의 시트를 사용해서 문서를 구성합니다. 

VBA에서 이름이 겹치는 경우 바로 에러를 발생하면서 종료되는데요.

시트 이름 겹치기 에러에 대해서 처리하는 방법에 대해서 알아보겠습니다.

일반 엑셀 인터페이스에서는 이름은 겹치지 않도록 되어있고 만일 겹치게 되면, 

"이미 사용되고 있는 이름입니다. 다른 이름을 선택해주세요."라는 메시지가 출력됩니다.

올바른 이름을 설정할 때까지 여기서 빠져나가지 못하게 되어 있습니다.

또 VBA에서는 '1004' 런타임 오류가 발생하였습니다. 라고 하면서 코드가 종료됩니다.

이 '1004' 런타임 에러는 꼭 회피 해야 하는 현상 중 하나입니다.

엑셀 내에서는 이름이 같은 시트를 허용하지 않고 예외처리도 안되게 되어 있습니다.

이번 포스팅에서 시트를 만들어서 이름을 할당할 때 중복되는 시트를 어떻게 처리하는지 알아보겠습니다.

1. 중복되는 시트가 있으면 지우고 새로 만들기 

깔끔하게 처리하는 방법입니다.

기존에 있는 시트에 예상하지 못한 작업물까지도 처리가 가능한 장점이 있습니다.

주로 임시로 발생하는 데이터를 저장하는 용도의 시트등에 사용가능합니다.

<< 코드 >>

Sub make_sheet()
' 시트 이름은 A로 지정하겠습니다.
On Error GoTo 만들기    '오류가 나면 만들기로 이동하기
    Set ws1 = ThisWorkbook.Sheets("A")
' 시트삭제시 나오는 경고 메시지 끄기
    Application.DisplayAlerts = False
        ThisWorkbook.Sheets("A").Delete
    Application.DisplayAlerts = True
만들기:
    Set wsTemp = ThisWorkbook.Sheets.Add
    wsTemp.Name = "A"
On Error GoTo 0 ' 오류 처리 종료

End Sub

① On Error GoTo 만들기 : 에러가 발생하면 "만들기" 항목으로 이동합니다.

② Set ws1 = ThisWorkbook.Sheets("A") : ws1이라는 개체에 시트 "A"를 할당합니다.

  - 시트 A가 있으면 아래줄로 이동합니다.

  - 시트 A가 없으면 오류가 남으로 "만들기"로 이동합니다.

③ Application.DisplayAlerts = False : 시트를 지울 때 뜨는 에러메시지를 잠시 끕니다.(사용자가 번거로움으로)

                                                            목적이 달성된 후에 다시 True로 변경합니다.

④ ThisWorkbook.Sheets("A").Delete : 시트 A를 지웁니다.

⑤ 만들기 : 

  - Set wsTemp = ThisWorkbook.Sheets.Add : 시트를 만듭니다.

  - wsTemp.Name = "A" : 만들어진 시트에 A라는 이름을 붙입니다.

결과 코드는 A라는 시트가 있으면 지우고 다시 만들고, 없으면 시트를 만들어 A라는 이름을 붙입니다.

2. 시트가 있으면 뒷 번호를 달아서 계속 만들기

자주 사용하는 방법입니다.

기존의 정보를 유지하면서 계속해서 시트를 만들어내서 정보의 손실이 없습니다.

다만 너무 많이 작업하면 늘어난 작업물을 따로 사용자가 삭제해야 합니다.

<< 코드 >>

Sub make_sheet_2()
' 시트 이름은 B로 지정하겠습니다.
' 워크시트 ws를 nothing으로 초기화 하기 위해서 선언
Dim ws As Worksheet

On Error Resume Next ' 오류 발생 하면 무시하고 다음 줄로 이동
    Set ws = ThisWorkbook.Sheets("B")
On Error GoTo 0 ' 오류 처리 종료

If Not ws Is Nothing Then
' 시트가 존재하면 IF문 실행하기
' 시트가 없을 때까지 DO - LOOP문 실행하기
    Do
        wsCount = wsCount + 1 ' 번호 올리기
        newPasteSheetName = "B" & wsCount   ' 번호 붙이기
        Set ws = Nothing
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(newPasteSheetName) ' 번호가 붙여져 있는 이름이 있는지 찾기
        On Error GoTo 0
    Loop While Not ws Is Nothing ' 있으면 다시 하기
Else
        newPasteSheetName = "B" ' 시트가 없으므로 이름 그대로 사용
    End If
    
' 새로운 시트 생성하여 이름 붙이기
    Set wsB = ThisWorkbook.Sheets.Add
    wsB.Name = newPasteSheetName

End Sub

① Dim ws As Worksheet : 워크시트 ws를 선언하면 ws가 nothing으로 초기화 되어있습니다.

                                           다른 방법으로 만들면 ws는 empty로 저장됨으로 뒤에 코드가 작동하지 않습니다.

② On Error Resume Next : 에러가 나면 무시하도록 합니다.

③ Set ws = ThisWorkbook.Sheets("B") : B라는 시트가 없으면 오류가 나고 무시함으로 ws는 계속 nothing입니다.

                                                                 B라는 시트가 잇으면 뭐라도 들어가게 됩니다.

④ If Not ws Is Nothing Then : ws가 있으면 동작하는 IF문입니다.(정확하게 nothing이 아니면 동작합니다.)

⑤ Do ~ Loop While Not ws Is Nothing : ws가 없을 때까지 반복합니다.(정확하게는 ws가 nothing 아니면 멈춤)

⑥ wsCount = wsCount + 1, newPasteSheetName = "B" & wsCount : 번호를 붙입니다.

⑦ Set ws = ThisWorkbook.Sheets(newPasteSheetName) : 번호가 붙은 시트가 있는지 확인합니다.

시트 이름이 없을 때 까지 번호를 붙여서 나아갑니다.

엑셀에서 허용하는 워크시트의 개수인 255개가 될때까지 반복해서 실행합니다.

오늘 사용하는 코드는 간간히 지인들에게 코드를 짜서 줄때 시트이름을 막 바꿔도 오류가 안나는 소스입니다.

제가 혼자 쓰는 경우에는 귀찮아서 작성하지 않다보니 자주 까먹고 다시 만들어야 하더군요.

이번 기회에 이렇게 정리해봅니다.

위에 이미 소스가 있어서 크게 예제가 필요하지는 않겠지만 첨부도 올려봅니다.

이제 얼마 안되서 문자를 사용하지 않는 시대가 올 것도 같습니다.

여전히 여러가지 상황에서 인쇄물은 필요한 것이고 격식을 가져야 하는 곳에서는 더욱 그렇습니다.

간략한 문서에 이름만 변경해서 출력하는 경우가 특히 그렇습니다.

이미 워드나 한글에서는 이 기능이 구현되어 있습니다.

하지만 오늘은 엑셀을 사용해보려고 했더니 딱히 쓸만한 기능이 안보이더군요.

그래서 VBA로 짜보려고 합니다.

< 소스코드 >

Sub Change_Value_Print()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim i As Integer
        
    FileNm = ThisWorkbook.Name '파일이름을 입력합니다.
        
    '워크시트 설정하기
    Set ws1 = Workbooks(FileNm).Sheets("Sheet1")
        
    Change_Value = ws1.Range("E4")
    Print_Sheet = ws1.Range("E5")
    Value_cell = ws1.Range("E6")
    Print_Range = ws1.Range("E7")
    
    Set ws2 = Workbooks(FileNm).Sheets(Print_Sheet)
       
    Set rng = ws1.Range(Change_Value)
    
    ' Sheet1의 지정한 범위를 반복합니다.
    For Each cell In rng
        '변경할 값을 입력하기
        ws2.Range(Value_cell).Value = cell.Value
        
        '입력한 출력범위 만큼만 출력하기
        ws2.Range(Print_Range).PrintOut
        
        '대기시간을 1초 추가하겠습니다.
        Application.Wait (Now + TimeValue("00:00:01")) ' 1초 대기
    Next cell
End Sub

소스코드 내에 있는 주석으로 설명은 충분하다고 생각됩니다.

코드를 사용하기 위해 Sheet1에는 아래와 같은 내용이 있습니다.

< 소스 사용하기 >

① 값을 입력할 범위, 출력할 시트 이름, 변경할 셀과 프린트할 범위를 입력합니다.

② 변경할 값을 입력합니다.

아래에서는 B14부터 아래로 B17까지 입력했습니다.

더 길게 적어도 되게 범위는 직접 적어두게 했습니다.

③ 시작 버튼을 누릅니다.

기본 프린터와 프린터 설정된 내용을 따릅니다.

테스트할 때 Print가 없이 Microsoft Print to PDF를 사용해도 정상 동작하더라고요.

대부분 환경에서 문제 없을 것이라고 생각됩니다.

설명으로는 잘 이해가 안될 수도 있습니다.

아래 파일을 공유하겠습니다.

한, 두번 직접 입력해보시면 충분할 것이라 판단됩니다.

소수점 이하의 자리가 없는 수를 정수라고하고 물건을 세는 등 일상생활에서 많이 사용합니다.

2개 단위로 짝을 이루는 수를 짝수, 짝을 이루지 못하는 수를 홀수라고 합니다.

이 짝수와 홀수를 출력하는 함수 두개를 알아보겠습니다.

1. ODD 함수와 EVEN 함수

ODD(number) : 가장 가까운 홀수로 올림한 수를 반환합니다.

EVEN(number) : 가장 가까운 짝수로 올림한 수를 반환합니다.

• number : 올림할 수입니다. 수자체는 양수든 음수든 상관없습니다.
• 텍스트를 입력하면 #value 오류를 반환합니다.
• 0에서 먼 값을 출력하게 됩니다.
• 이미 입력한 숫자가 짝수이거나 홀수이면 그냥 그 숫자를 그대로 반환합니다.

0에서 먼 값에 대해서는 양수는 큰 쪽으로 음수는 작은 쪽으로 움직인다는 것입니다.

절대값이 큰쪽으로 보면 됩니다.

ODD(2) = 3이 되고 ODD(-2) = -3이 되는 것입니다.

2. 짝수와 홀수의 쓰임처

얼핏 일상에서는 생각하기 힘들기는 하지만 생각보다 많은 사용처가 있습니다.

몇가지 예시를 소개해보겠습니다.

① 홀수로 변환할 때 

여러 모델이 있는 겨우, 부품이 파츠가 짝을 이루고 나서 손잡이 부분이 하나 필요하다고 합시다.

이때 디자인에 손잡이가 없으면 하나 제공해야합니다.

이럴때는 원래 홀수면 내버려두고, 홀수가 아니면 한개를 추가하게 됩니다.

② 짝수로 변환할 때 

포장지에는 효율적인 관리를 위해 짝수로 들어가야 하는 경우가 많습니다.

이런 경우 만일 홀수가 나오면 더미로 넣어야 함으로 하나 더 준비해야 합니다.

이런식으로 숫자가 고정적으로 사용되어야 하는 경우가 많이 있습니다. 

가장 가까운 홀짝을 찾는 것은 눈으로도 할 수 있지만 미리미리 양식에 적용해두면 실수를 줄일 수 있습니다.

위에서든 포장같은 경우 좀더 복잡한 상황에도 대응할 수 있는 MROUND 함수가 있기는 합니다.

MROUND 함수의 설명도 링크로 남겨두니 필요하면 클릭해주세요.

엑셀(EXCEL)의 배수로 반올림의 기준을 설정하는 MROUND 함수로 각종 자리 맞춤하기(0.5배수로 반올림하기)

목차

1. 엑셀로 그리는 법

2. VBA로 그리기(파일 포함)

지난번 포스트 : 고객 만족도 그래프 IPA(Importance-Performance Analysis)에 대한 설명

위 포스트에서 IPA 그래프에 대해서 간략하게 이야기 해봤습니다.

그런데 이 그래프가 전용툴을 쓰지 않으면 생각보다 그리기 까다로워 대충 만들게 됩니다.

IPA 그래프를 엑셀로 그리는 방법과 VBA로 만든 파일을 이 번 포스팅에서 올려보겠습니다.

1. 엑셀로 그리는 법

① 데이터를 먼저 준비합니다.

이건 사전에 조사해서 넣어야 합니다.

지표는 점수로 나타낼 수도 있고, 아니면 순위나 평균 등등 산출된 수치로도 가능합니다.

첫번째는 항목명, 두번째는 x축에 들어갈 성과지표, y축에는 중요도를 넣습니다.

② 빈 셀을 하나 선택하고 분산형 차트를 만듭니다.

주변에 데이터가 없는 빈셀을 하나 선택하고 분산형 차트를 만듭니다.

그럼 빈차트가 하나 생기게 되는데 여기에 오른쪽 클릭을 해서 데이터 선택으로 들어갑니다.

③ 데이터 "추가"를 선택합니다.

범례 항목(계열)에서 "추가"를 선택하연 "계열 편집"창이 열립니다.

그럼 준비한 계열이름 하나와 x, y 값을 하나씩 입력합니다.

하나만 입력하고 확인을 클릭합니다.

④ 하나씩 입력해서 항목 늘리기

전부 하나씩 "추가"를 클릭해서 입력해서 항목을 만듭니다.

그럼 아래 그림처럼 데이터의 목록이 생깁니다.

작업방법은 여러가지가 있겠지만 한방으로는 이렇게 만들기 힘들고 하나하나 입력하는 쪽이 편합니다.

서식을 넣어서 마무리하면 맨위에 있는 그래프처럼 됩니다.

이게 상당히 귀찮다는 생각이 들었습니다.

특히 항목수가 많아지고 일상적으로 그린다면 뭔가 하나 있으면 좋겠죠

2. VBA로 그리기(파일 포함)

간략하게 만들었습니다.

세로로 작성해야 하고 첫 줄에는 제목, 둘째는 X축 세번째는 Y축에 들어갈 데이터를 선택합니다.

갯수는 상관 없이 선택한 상태에서 옆의 버튼을 누릅니다.

그려진 파일에는 기본적인 서식은 추가했습니다.

디자인은 알아서 해서 사용하도록 합시다.

이 정도도 많이 편하다고 생각합니다.

그럼 코드를 알아보겠습니다.

Sub CreateIPA()
    Dim rng As Range
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim series As series
    Dim i As Integer
    
    '선택한 영역을 rng 변수에 저장
    Set rng = Selection
    Set ws = ActiveSheet
    
    '선택된 영역의 첫 줄을 제목으로, 두 번째 줄을 X 값으로, 세 번째 줄을 Y 값으로 사용
    Dim titleRange As Range
    Dim xValuesRange As Range
    Dim yValuesRange As Range
    
    Set titleRange = rng.Columns(1)
    Set xValuesRange = rng.Columns(2)
    Set yValuesRange = rng.Columns(3)
    
    '차트 객체를 추가
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=50, Height:=225)
    
    '분산형 차트 그리기
    With chartObj.Chart
        .ChartType = xlXYScatter
        .HasTitle = True  '제목 설정하기
        .ChartTitle.Text = "IPA 그래프 그리기"
        .Axes(xlCategory, xlPrimary).HasTitle = True   'x축 제목 설정하기
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "성과(Performance)"
        .Axes(xlValue, xlPrimary).HasTitle = True   'y축 제목 설정하기
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "중요도(Importance)"
        
        '각 열을 반복하며 범주 추가
        For i = 1 To titleRange.Rows.Count
            Set series = .SeriesCollection.NewSeries    '새로운 범주 설정하기
            series.Name = titleRange.Cells(i, 1).Value  '항목 하나의 제목을 입력하기
            series.xValues = xValuesRange.Cells(i, 1).Value '항목 하나의 x 값을 입력하기
            series.Values = yValuesRange.Cells(i, 1).Value  '항목 하나의 y 값을 입력하기
            series.MarkerStyle = xlMarkerStyleCircle '표식 모양 동그라미
            series.MarkerSize = 10 ' 표식 크기 10
            series.HasDataLabels = True '레이블 만들기
            series.DataLabels.ShowValue = False
            series.DataLabels.ShowSeriesName = True '레이블에 항목제목 적용하기
            series.DataLabels.Position = xlLabelPositionAbove '레이블을 위로 설정
        Next i
    End With
    
    ' 메모리를 비워서 정리
    Set chartObj = Nothing
    Set rng = Nothing
    Set titleRange = Nothing
    Set xValuesRange = Nothing
    Set yValuesRange = Nothing
    Set series = Nothing
End Sub

<코드 동작 순서>

선택한 객체인 Selection으로 rng 변수에 할당합니다.

선택한 행별 데이터를 첫번째 두번째 세번째로 titleRange, xValuesRange, yValuesRange 변수에 배열로 입력합니다.

분산형 차트의 코드는 xlXYScatter입니다.

그런 후 for 문을 사용해서 각각의 데이터로 항목을 만들고 주변에 명령어로 서식을 입력합니다.

코드가 조금 길지만 서식 항목이 많아서 그렇지 별거 없습니다.

엑셀에서 함수를 사용하면 셀에서는 결과값이 보여줍니다.

함수내용이 보고 싶으면 셀을 클릭하면 함수창에 나타납니다.

그외도 수식 표시 기능등이 있지만 함수를 셀에 내용으로 작성할 때가 있습니다.

FORMULATEXT(reference) : 셀에 사용되는 함수와 내용에 대해서 표시합니다.

• reference : 사용하려고 하는 셀에 대한 것입니다. 단일 셀만 받아들이고 범위를 사용할 때는 배열함수를 써야 합니다.
• 함수를 반환할 때는 등호(=)를 포함합니다. right 함수등을 활용해서 삭제할 수 있습니다.

등호(=)와 함수가 참조한 범위를 포함해서 내용을 전체 다 반환합니다.

이 함수는 Office 365 이상의 버전에만 사용할 수 있는 함수입니다.

그 이전에 버전에서 사용하기 위한 VBA 소스를 하나 소개합니다.

Cell 메소스의 fomula 속성을 사용하면 되는 간단한 함수입니다.

코드를 소개합니다.

VBA 편집창을 열 때는 엑셀에서 사용 중에 ALT + F11 단축키를 사용하면 편집기가 열립니다.

그리고는 왼쪽의 프로젝트를 오른쪽 클릭해서 모듈을 새로 만들어서 아래 내용을 복사해 붙여 넣으면 됩니다.

Function GetFunctionName(cell As Range) As String
    Dim formula As String
    Dim funcName As String
    
    ' 셀에 수식이 있는지 확인
    If cell.HasFormula Then
        formula = cell.formula
        
        ' 함수 이름 추출
        funcName = Split(Mid(formula, 2), "(")(0)
        GetFunctionName = funcName
    Else
    	'수식이 없으면 내용을 그대로 가져옵니다.
		GetFunctionName = cell.Value
	End If
End Function

사용자함수로 "GetFunctionName" 라는 이름의 함수를 추가합니다.

셀에 수식이 있으면 함수이름만 가져오고 아니면 그 값을 그대로 출력합니다.

어려운 함수는 아닙니다.

사용이 쉬운 함수이지만 작성한 파일을 첨부하겠습니다.

혹시 필요하신 분은 가져다 쓰시면 됩니다.

엑셀에서는 기본적으로 주석이나 설명기능을 제공합니다.

가장 많이 사용하는 것은 [검토] - [메모] 기능일 것입니다.

이건 잘 알려진 것처럼 셀에 설명의 더하거나 주의사항을 입력하는 것입니다.

메모를 남기면 셀 오른쪽 상단에 빨간 세모로 표시합니다.

1. 윗주 기능과 입력하기

일반적인 메모보다는 내부에 표시하고 그래서 덜 쓰이는 윗주라는 기능도 있습니다.

셀에 쓰여져 있는 글자에 설명을 더하는 방식입니다.

상단매뉴의 [홈] - [글꼴] - [윗주] 기능의 아이콘도 있습니다.

여기서 입력해주면 글자위에 작은 영역이 더 생겨서 내용을 입력합니다.

엑셀의 수 많은 기능 중에서도 당당하게 메인에 나와있는데요.

요즘 시대에 윗주를 많이 사용하나 싶은데요. 문화권에 따라 다른게 있는 모양입니다.

윗주를 사용하면 텍스트 위에 글을 쓸수 있도록 나옵니다.

셀 내용을 편집 중일 때는 "윗주 편집"를 클릭해서 확인해 보겠습니다.

글 위에 저렇게 쓸 수 있고 다 입력하고 enter를 누르면 사라집니다.

셀을 선택하고 "윗주 필드 표시"를 클릭하면 윗주가 나타납니다.

이렇게 숨겨놓듯이 거의 보이지 않다가 필요할 때만 보는 기능입니다.

그리고 전용 단축키까지 있는데, Alt + Shift + ↑ 입니다.

셀에 내용을 입력하고 있을 때만 동작합니다.

단축키가 있는게 적은데 엑셀 개발자들은 상당히 중요한 기능이라고 생각한 모양입니다.

윗주는 단어마다 따로 넣을 수 있으니 입려되는 곳을 선택하고 입력하면 됩니다.

또 "윗주 설정" 기능을 통해서 위치를 왼쪽, 오른쪽 혹은 중앙정렬을 할 수도 있습니다.

윗주 필드 표시는 셀마다 따로 할 수도 있습니다.

2. 윗주 전용 함수 PHONETIC

메인에 있는 기능이고 단축키도 가졌으니 전용함수 역시 하나 있습니다.

PHONETIC(reference) : 윗주 글자를 모두 가져옵니다.

• reference : 대상 셀이나 범위를 선택합니다. 범위내의 모든 윗주를 가져옵니다.
• 윗주가 있는 경우 글자에서 윗주를 가져오고 아니면 그냥 본문을 가져옵니다.

특이한 함수입니다. 윗주는 단어단위로 사용할 수 있기 때문에,

아래처럼 한셀에 "aaa bbb"라고 쓰여 있어도 aaa는 윗주가 있고 bbb는 없는 경우에 윗주가 있는 단어만 적용됩니다.

나머진 그대로 표시합니다.

오히려 CONCAT 함수(구 CONCATENATE 함수) 처럼 윗주가 없는 경우 그냥 단어를 붙어주는 역할을 합니다.

윗주기능은 내용은 넣고 싶지만 항상 보는 글자는 아닌 경우 사용할 수 있고요.

아니면 발음과 표기가 다르거나 한자처럼 읽기 힘들 때도 사용할 수 있습니다.

편집하다 보면 글자가 깨지는 경우가 있습니다.

반드시 아래내용을 먼저 쓰고 한번에 윗주를 편집하는 쪽을 추천합니다.

목차

1. 이진연산 : AND(&), OR(|), XOR(^)

2. 이진연산을 수행하는 함수

3. 이진연산의 사용하는 예시

흔히 우리주위에서 볼 수 있는 것처럼 사람은 10진수 체계에 익숙하지만 컴퓨터는 2진수로 세상을 이해합니다.

컴퓨터의 경우 아예 모든 숫자와 문자를 포함한 정보를 다 결국 2진수로 이해하고 있죠.

보통 의식하지 못하지만 스마트 폰으로 간단한 덧셈 뺄셈을 하더라도 컴퓨터 자체는 좀 길게 돌아가서 수행합니다.

프로그램을 할 때라든가 대량의 연산을 할때는 에러를 막기 위해 이진수 자체를 이용한 연산을 사람이 직접 시도합니다.

오늘은 이런 이진 연산의 종류와 엑셀로 수행하는 방법을 알아보겠습니다.

1. 이진연산 : AND(&), OR(|), XOR(^)

숫자는 이진수로 나타낼 수 있습니다.

같은 자리의 수에 AND와 OR 연산자를 취하는 방법입니다.

먼저 예를 들어서 보여드리는게 쉽습니다.

< AND(&) 연산 >

64 & 227는 아래와 같습니다.

먼저 64(01000000)와 227(11100011)을 이진수로 변환합니다.

둘 다 1인 것은 하나이고 그 위치만 1을 취하면 (01000000 = 64) 입니다.

0과 연산하면 무조건 0으로 반환합니다.

< OR(|) 연산 >

102 | 162는 아래와 같습니다.

다시 102(01100110)과 227(10100010)을 이진수로 변환합니다.

OR은 둘 중 하나라도 1인 위치에서는 1을 반환합니다.

하나라도 1이 있으면 무조건 1입니다.

이 경우에는 (11100110 = 230)이 됩니다.

< XOR(^) 연산 >

78 ^ 170는 아래와 같습니다.

연산자 ^ 지수함수에서는 사용하는 승 표시와 같음으로 사용할 때 주의해야 합니다.

이번에도 78(01001110)과 170(10101010)으로 변환합니다.

XOR은 하나가 1, 하나가 0이면 1을 반환합니다.

나머지는0으로 처리됨으로 (11100100 = 228)이 됩니다.

2. 이진연산을 수행하는 함수

엑셀에서 이진연산을 수행하는 함수는 bitAND, bitOR, bitXOR입니다.

함수의 기능과 사용은 간단합니다.

• bitAND (number1, number2, ...) : 이진연산 AND를 수행합니다.
• bitOR (number1, number2, ...) : 이진연산 OR를 수행합니다.
• bitXOR (number1, number2, ...) : 이진연산 XOR를 수행합니다.

인수인 number의 숫자는 10진수를 사용해야 하고 10진수로 반환합니다.

2²⁸-1까지 수행할 수 있고, 정수만 사용가능합니다.

3. 이진연산의 사용하는 예시

컴퓨터는 이진연산을 이용하면 빠르고 에러가 거의 없이 사용됩니다.

다양한 분야에서 사용되는데 예를 들어 0과 만나면 0을 반환하는 AND연산자를 통하면 마스크로 쓸 수 있습니다.

원래 사용할 숫자 : 11010101

마스크 (M)           : 11110000

이렇게 하면 숫자 & 마스크(M)을 사용하면 오른쪽 4자리 수만 사용할 수 있습니다.(1101)

왼쪽 4개는 무조건 0으로 버려지게 됩니다.

대량의 정규화된 데이터베이스 중에서 추출할 자리 수에만 1이 있는 마스크를 만들면 언제든지 뽑을 수 있습니다.