목차

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이 있는 마스크를 만들면 언제든지 뽑을 수 있습니다.

사회나 자연현상으로부터 수집하는 데이터의 분포를 파악하여 산포도로 정리하고는 합니다.

많은 수의 데이터를 파악하는데 도움이 되는 도수분포표(히스토그램 - Histogram)은 아래와 같은 모양으로 그려집니다.

주로 가로층은 계급(Level 혹은 숫자의 범위)와 세로축은 그 수량으로 파악합니다.

이런 히스토그램은 거의 주로 정규분포를 띌 때가 많습니다.

그래서 정규분포 곡선을 그려서 산포와 비교합니다.

대표적인 예로는 우리 공정산포를 보는 미니탭(Minitap)에서 찾을 수 있습니다.

미니탭에서는 공정산포를 표시할 때 히스토그램 그래프에서 해당 산포가 가지고 있는 정규분포를 함께 표현합니다.

오늘은 엑셀로 어떻게 방법을 사용하는지 알아보겠습니다.

< 선행해야할 것 >

오늘 사용법을 알려드리기는 할 것이지만, FREQUENCY와 NORM.DIST 함수를 사용할 예정입니다.

함수 자체에 포스팅을 한 적도 있으니 링크 참고하세요.

EXCEL 데이터를 히스토그램을 만드는 FREQUENCY 함수와 통계 차트 삽입기능

정규분포의 확률을 알려주는 NORM.DIST 함수는 무엇이고 사용하는 예시

< 차트 그리기 >

① 사용할 데이터를 정리합니다.

사용할 데이터를 정렬합니다.

AVERAGE 함수를 사용해서 평균을 STDEV.S 함수를 통해서 표본집단의 표준편차를 구해둡니다.

오늘 사용할 데이터는 야구 2024년 정규시즌 40 경기 이상 출전선수의 평균 안타수로 하겠습니다.

② 도수분포표의 계급을 정합니다.

계급을 결정하는 방법은 여러가지가 있으나 표준적인 방법이나 대세적으로 많이 쓰는 방법은 없습니다.

꼭 이거다 하는 방법이 없음으로 이번에는 임의로 10개 간격으로 15칸으로 설정해 보겠습니다.

③ Frequency 함수를 사용합니다.

Frequency 함수는 배열 함수로 사용해야 합니다.

오피스 365 이상에서는 그냥 배열함수로 사용이 되지만, 그 이전 버전에서는

Ctrl + Shift + Enter를 눌러줘야 합니다.

레벨을 bins array로 사용해야하며 DATA array는 원본 데이터를 사용합니다.

④ NORM.dist 함수를 사용합니다.

NORM.dist는 배열함수가 아닙니다. 평범하게 사용하면 됩니다.

NORM.dist(x, 평균, 표준편차, 옵션) : 범위의 시작값을 x로 하고 평균과 표준편차는 구해두었습니다.

옵션은 False : 확률밀도함수로 입력합니다.

모든 범위(계급)에 대해서 NORM.dist를 구해서 정렬하겠습니다.

⑤ 그래프를 그립니다.

수량은 막대그래프로 정규곡선은 꺽은선 그래프로 그립니다.

그리고는 어느정도 꾸미면 아래처럼 그려집니다.

아래 그래프는 동일한 데이터를 넣고 통계 프로그램인 Minitap에 넣고 계산한 결과입니다.

주변에 여러가지 계산값을 만들어주기는 하지만 그래프 모양 자체는 유사하게 그려지네요.

아마 디자인 감각을 살린다면 더 나은 그래프도 그릴 수 있을 껍니다.

십의 자리를 기준으로 숫자를 올리거나 내려주는 Round 함수가 있습니다.

하지만 항상 십의 단위로 물건을 묶는건 아닙니다.

시간은 60분이나 연필은 한다스 12개등이 대표적이지만 제품 포장에서는 7이나 27등 다양한 단위를 사용합니다.

이 때 사용하는 CEILING.MATH 함수를 소개합니다.

CEILING.MATH(number, [significance], [mode]) : 숫자를 다양한 단위 정수로 반올림합니다.

• number : 올림하고 싶은 원본숫자입니다.
• [significance] : 올림할 때 사용하는 배수입니다. 기본 값은 1 혹은 -1입니다.
• [mode] : 음수를 올림할 때 사용합니다. 기본 값이 0은 큰 쪽으로 올려서 0에 가까운 수가 되고, 1을 입력하면 작은 방향으로 올리고 절대값이 커지는 결과가 반환됩니다.

먼저 함수를 사용해 보겠습니다.

보이는데로  원하는 것처럼 숫자를 만들어줍니다.

이 함수는 꽤 쓸모가 많습니다.

예를 들면

• 예산을 계획시에 항목이 많을 경우 사용합니다.
  여러 항목이 소수점 단위로 짤라지는 10원단위 절삭을 할 때 씁니다.
  1원 단위로 거래가 어렵기 때문에 낭비가 발생하기 쉽고, 물건을 수만 혹은 수십만 취급해야 하는 큰 기업 등에서는 절삭을 안하면 예산이 부족한 사태가 발생할 수 있습니다.
• 물건을 주문할 단위로 포장되는 경우 사용합니다.
  한박스에 17개 들어간다든지 하는 경우인데, 정책적으로 그 단위가 아니면 안팔기도하고 무엇보다 그렇게 맞추는게 서로 가장 저렴할 때가 많습니다.
  좀 남아도 짧은 시간에 다시 사용할 예정이라면 구매를 할 때 판매처의 단위로 맞추는데 사용합니다.
• 시간관리를 할때도 사용합니다.
  인력의 시간을 작업을 (A + B + C)를 했을때 3.5 + 2.8 + 1.7로 하면 그냥 더하면 8시간에 하루면 될 것 같지만, 현실에는 각 작업의 전환시간이나 화장실 등 비효율적인 시간을 있어서 이걸 8시간으로 계획을 잡으면 리스크가 생깁니다.
  공장에서야 이런 시간까지 다 계획에 넣으려고 하는 시도가 있지만 영업업무나 이사짐 센터등 일이 다양하게 변하는 경우 일일히 다 계산할 수 없습니다.
  이걸 감안하기 위해 30분 단위라든가 등으로 쪼개서 계산하면 생산계획이 망가지는 사태를 막을 수 있습니다.
• 데이터 페키지로 사용할 때 사용합니다.
  데이터 페키지 계산할 때 데이터의 수량은 8이나 16, 256 식으로 명확하게 쪼개지는데 한 개 작업에서 사용하고 나면 다음 작업에서는 다음 페키지를 사용해야함으로 올림 계산이 필요합니다.

저도 쓰다보니 생각보다 많은 이 사용을 하네요.

그것 말고도 고려할 수 있는 경우는 많이 있습니다.

이렇게 다양한 분야에서 사용하는 함수인만큼 알아두시면 좋을 것 같습니다.

엑셀을 하다보면 보여지는 문자는 "서식정보"와 "내용"이 합쳐집니다.

즉 생긴거는 문자이지만 숫자일 수도 있고, 숫자 10이 "10권"으로 표시될 수도 있다는 뜻입니다.

특히 통계 프로그램을 통해 생성된 숫자나 외부파일을 복사해서 붙이는 경우 문자로 많이 붙여넣어집니다.

이런경우에 문자를 숫자로 고쳐주는 NUMBERVALUE 함수에 대해서 알아보겠습니다.

NUMBERVALUE(text, [decimal_separator], [group_separator]) : 텍스트를 원하는 형식에 숫자로 변환합니다.

• text : 숫자로 표시될 문자입니다. 함수가 숫자로 해석할 수 없는 경우 #VALUE! 에러를 반환합니다.
• [decimal_separator] : 소숫점을 확인합니다. text안에서 decimal_separator를 찾아서 똑같은게 있다면 그 오른쪽에 있는 숫자는 소수점 이하로 표시합니다. 같은 decimal_separator가 2개 이상이면 오류(#VALUE!)가 발생합니다.
• [group_separator] : 구분표시입니다. text안에서 group_separator 를 찾아서 똑같은게 있다면 무시합니다.
• 빈칸은 자동으로 무시합니다. 3 0 0 0은 3000으로 자동으로 변환됩니다.

인수에 대한 설명이 어렵습니다.

하지만 동작이 쉬운 함수로 예를 들어서 보면 명확합니다.

=NUMBERVALUE("1서43장55","장","서") 라고 하면 결과는 "143.55"가 됩니다.

"서"는 뭔가 구분기호로 생각해서 없애버리고 "장"은 소수점으로 빼버린 겁니다.

문자로 표기되어 있고 중간에 공백이 있어서 연산이 안되는 왼쪽에 비해,

NUMBERVALUE로 변환한 식은 계산이 바로됩니다.

요즘은 사실 엑셀에서 어지간한 텍스트 형식의 숫자는 연산이 바로 지원되게 만들어져 있습니다.

텍스트 형식으로 '34 + '55을 하면 그냥 89라고 결과가 뜹니다.

스마트하게 만들어지는 추세죠.

언제가 NUMBERVALUE 함수는 장기적으로 없어지는 과거의 유물이 될 수도 있겠네요.

하지만 숫자가 텍스트가 되어서 문제가 생기는 경우는 언제든지 있습니다.

텍스트로 표시된 숫자는 거의 대부분 칸의 왼쪽 위에 초록색 삼각형이 있어 알 수 있습니다.

하지만 이것도 예외는 언제든지 있어서 혼돈될 때가 있습니다.

외부 프로그램으로 CSV 파일을 만들거나, 혹은 복사해서 붙일 때는 텍스트로 적용되었는지 아닌지를 확인할 필요가 있습니다.

목차

1. FORECAST.ETS.CONFINT 함수

2. 함수 사용 예시

현실에서 사용하는 데이터들은 불연속적으로 보이고 패턴이 없어 보입니다.

이런 데이터를 분석하기 위해 몇가지 방법을 사용하는데,

• 이동평균법 : 과거 데이터의 평균을 이용해서 데이터를 분석하기 쉽게 만들기
• 지수평활법 : 수준(Level), 추세(Trend), 계절성(Seasonality)로 미래정보를 예상
• ETS 모델 : 지수평활법에 Error를 추가하여 불확실성을 최소화

같은 방법이 있습니다.

엑셀에서는 FORECAST.ETS 함수로 이 ETS 예측 솔루션을 제공합니다.

그래도 미래예측이 다 그렇지만, 많은 불확실성을 가지고 있습니다.

불확실성을 계산하는 파생 함수인 CONFINT 함수를 알아보겠습니다.

1. FORECAST.ETS.CONFINT 함수

FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation]) :ETS 함수로 계산한 날짜의 예측한 값에 대한 신뢰구간을 반환합니다. 예측된 결과 95%에 반경에 속할 것이라는 것을 계산할 수 있습니다. 반대로 말하면 이 반경이 좁을 수록 ETS로 예상한 값이 정확도가 높다고 할 수 있습니다.

• target_date : 예측하려는 시점의 숫자입니다. 날짜 혹은 시간, 숫자일 수 있습니다.
  원본데이터의 시간인 timeline에서 몇 단계 멀어져 있는지 계산해서 수행합니다.
  이 시간이 timeline 안쪽인 경우에는 #NUM!의 에러를 출력합니다.
• values : 측정한 과거의 값들입니다. 범위로 입력해야하고 정확하고 많은 데이터가 필요합니다.
• timeline : 측정한 값들의 시간 데이터입니다. 일정한 간격의 값으로 구성되어야 하지만 꼭 정렬될 필요는 없습니다.
  함수가 자동으로 시간순으로 정렬하며 줍니다.
  여기에 계산이 불가능한 텍스트등이 포함되면 #NUM을 출력됩니다. values와 같은 개수여야 합니다.
• [confidence_level] : 구간의 신뢰도 수준을 나타내는 값으로 0 ~ 1 사이의 숫자입니다. 입력하지 않는 기본값은 0.95입니다.
• [seasonality] : 계절성을 입력하는 항목으로 선택 요소입니다.
  1은 기본값으로 엑셀 함수가 자동으로 계절성을 계산하게 합니다.
  0은 계절성이 없는 모델을 사용하여 계산하는 선형모델을 사용합니다.
  양의 정수를 입력하면 사용자가 입력한 seasonality를 사용해서 계산합니다.
  그 외의 모든 값에 #NUM 오류가 반환됩니다. 지원되는 최대 seasonality는 8,760(1년 동안의 시간)입니다.
  해당 숫자보다 seasonality가 높으면 #NUM! 오류가 반환됩니다.
• [data_completion] : 계측된 데이터에 빈값을 어떻게 할지 결정하는 선택요소입니다. 이 함수에서는 최대 30%의 누락지점을 매꾸는 알고리즘이 지원됩니다.
  1 : 기본값으로 가까운 지점의 평균을 사용해서 누락된 DATA를 계산합니다.
  0 : 누락된 지점을 0으로 간주하는 알고리즘입니다.
• [aggregation] : timelie 요소간에 일정한 단계가 필요한데 중복되는 타임 스탬프가 있을 수 있습니다. aggregation은 이 값들을 어떻게 처리할지 결정합니다.
  1 : 기본값으로 그 값들을 AVERAGE를 계산합니다.
  2 : SUM으로 계산합니다.
  3 : Count로 계산합니다.
  4 : CountA로 계산합니다.
  5 : 최소값(Min)으로 계산합니다.
  6 : 최대값(Max)으로 계산합니다.
  7 : 중위값(Median)으로 계산합니다.

2. 함수 사용 예시

함수는 범위를 반환하기 때문에 ETS로 구한값을 더하거나 빼면 됩니다.

EST ± CONFINT를 적용하면 해당 신뢰구간을 구할 수 있습니다.

과거 실측기온을 대상으로  ETS를 이용해서 내년 기온을 예상해 봤습니다.

일단 보이지는 않지만 1960년부터 지금까지 서울의 월평균 기온을 구한것입니다.

예상값 선에서 CONFINT로 구한 값이 위아래로 범위를 만들고 있습니다.

95% 신뢰구간임에도 이 범위는 ± 3 ℃ 가까운 굉장히 큰 것으로 기후 예상이 어렵다는 것을 알 수 있습니다.

내년에 진짜 이대로 가는지 보는 것도 좋을 것 같습니다.

ETS함수로 먼미래까지도 계산할 수 있습니다.

예상하는 미래를 좀더 멀리해서 2030년까지 보겠습니다.

시간에 따른 오차가 점점 커지는 것을 볼 수 있습니다.

먼 미래가 예상하기 힘든 점이 반영되는 것입니다.

그리고 아주 약간이지만 중심치가 +로 가는 것이 있습니다.

그건 아무래도 지구 온도가 올라가는 것이 반영된 것 같습니다.

ETS 계산법은 기온을 예상하기 위한 수많은 데이터가 없이 통계적으로도 어느정도 예상할 수 있는 장점이 있습니다.

그냥 봐서는 이해가 어려울 수 있으니 예시 파일을 올립니다.

여기에는 Microsoft사의 Learn 홈페이지에 가져온 내용도 포함됩니다.

< 지수평활법과 ETS 관련 포스팅 >

01. 시계열 분석에서 미래를 예측하는 이동평균법(SMA), 지수평활법(ES)으로 미래값을 예상하고 풀어보기

02. 지수평활법으로 데이터를 분석할 때 계절성과 주기를 파악하는 방법(Exponential Smoothing의 Seasonality)

03. 시계열 예측에서 지수평활법의 기본 모델(Additive)과와 확장 형태인 감쇠 (Damped) 모델들의 정의

04. 시계열 예측에서 계절성에 따른 추세가 변하는 승법적 모델과(Multiplicative Model), 감쇠하는 승법적 모델(Damped Multiplicative Model)

05. 상황에 맞게 지수평활법과 ETS 모델(Exponential Triple Smoothing)을 사용해서 시계열 예측을 수행하기

06. 지수평활법에서는 사용하는 계수(α, β, γ)를 최소제곱법으로 구하는 방법(엑셀 VBA 구하기)

07. 엑셀(EXCEL)로 지수평활법의 ETS 모델을 사용하는 FORECAST.ETS 함수와 파생함수인 SEASONALITY / STAT 함수

08. 엑셀(EXCEL)의 FORECAST.ETS 함수의 오차를 계산하는 CONFINT 함수(지수평활법을 사용하는 미래 예측 함수) - 현재글

링크

목차

1. FORECAST.ETS 함수

2. FORECAST.ETS.SEASONALITY 함수

3. FORECAST.ETS.STAT 함수

4. 함수 적용해 보기

엑셀에서는 확장형 삼중 지수평활법인 ETS 알고리즘을 사용하여 기존의 관측값을 기반으로 미래값을 예측합니다.

데이터는 연속적인 시간에 따른 값으로 일정한 시간 간격으로 형성되어야 합니다.

또한 예측하는 값도 연속적인 시간간격으로 예측합니다.

원본 DATA가 빠짐없이 시간간격으로 측정되며 상세하고 오차가 적을 수록 예측이 정밀하도록 설계되어 있습니다.

1. FORECAST.ETS 함수

FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) : 확장형 지수평활법인 ETS 모델로 미래값을 예측합니다.

이 계열 함수의 기본값을 많은 파생값과 옵션을 포함합니다.

• target_date : 예측하려는 시점의 숫자입니다. 날짜 혹은 시간, 숫자일 수 있습니다.
  원본데이터의 시간인 timeline에서 몇 단계 멀어져 있는지 계산해서 수행합니다.
  이 시간이 timeline 안쪽인 경우에는 #NUM!의 에러를 출력합니다.
• values : 측정한 과거의 값들입니다. 범위로 입력해야하고 정확하고 많은 데이터가 필요합니다.
• timeline : 측정한 값들의 시간 데이터입니다. 일정한 간격의 값으로 구성되어야 하지만 꼭 정렬될 필요는 없습니다.
  함수가 자동으로 시간순으로 정렬하며 줍니다.
  여기에 계산이 불가능한 텍스트등이 포함되면 #NUM을 출력됩니다. values와 같은 개수여야 합니다.
• [seasonality] : 계절성을 입력하는 항목으로 선택 요소입니다.
  1은 기본값으로 엑셀 함수가 자동으로 계절성을 계산하게 합니다.
  0은 계절성이 없는 모델을 사용하여 계산하는 선형모델을 사용합니다.
  양의 정수를 입력하면 사용자가 입력한 seasonality를 사용해서 계산합니다.
  그 외의 모든 값에 #NUM 오류가 반환됩니다. 지원되는 최대 seasonality는 8,760(1년 동안의 시간)입니다.
  해당 숫자보다 seasonality가 높으면 #NUM! 오류가 반환됩니다.
• [data_completion] : 계측된 데이터에 빈값을 어떻게 할지 결정하는 선택요소입니다. 이 함수에서는 최대 30%의 누락지점을 매꾸는 알고리즘이 지원됩니다.
  1 : 기본값으로 가까운 지점의 평균을 사용해서 누락된 DATA를 계산합니다.
  0 : 누락된 지점을 0으로 간주하는 알고리즘입니다.
• [aggregation] : timelie 요소간에 일정한 단계가 필요한데 중복되는 타임 스탬프가 있을 수 있습니다. aggregation은 이 값들을 어떻게 처리할지 결정합니다.
  1 : 기본값으로 그 값들을 AVERAGE를 계산합니다.
  2 : SUM으로 계산합니다.
  3 : Count로 계산합니다.
  4 : CountA로 계산합니다.
  5 : 최소값(Min)으로 계산합니다.
  6 : 최대값(Max)으로 계산합니다.
  7 : 중위값(Median)으로 계산합니다.
  
  

2. FORECAST.ETS.SEASONALITY 함수

FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation]) : 관측된 데이터가 일정간격으로 패턴이 반복되는 것을 계절성(Seasonality)라고 합니다.

ETS에서 사용하는 계절성의 주기를 계산해주는 함수입니다.

ETS 함수에서 계절성을 자동으로 구해서 계산하기는 하지만 따로 뽑아서 확인할 필요가 있습니다.

미래 예측 분야에서는 감각적으로 사람이 한번 확인하는 것이 아직 중요합니다.

• values : 계절성을 파악하려는 데이터의 값들입니다. timeline에 대응하는 값들의 범위 입니다.
• timeline : 값이 배열된 시간의 데이터입니다. 시간들 사이에는 일정한 간격이 필요합니다. 범위의 데이터들이 꼭 순차적으로 정렬될 필요없이 함수가 알아서 정렬을 합니다. 0을 입력하면 #NUM! 오류를 출력하고 문자를 입력하면 해당 데이터는 무시하고 계산합니다.
• [data_completion] : 계측된 데이터에 빈값을 어떻게 할지 결정하는 선택요소입니다. 이 함수에서는 최대 30%의 누락지점을 매꾸는 알고리즘이 지원됩니다.
  1 : 기본값으로 가까운 지점의 평균을 사용해서 누락된 DATA를 계산합니다.
  0 : 누락된 지점을 0으로 간주하는 알고리즘입니다.
• [aggregation] : timelie 요소간에 일정한 단계가 필요한데 중복되는 타임 스탬프가 있을 수 있습니다. aggregation은 이 값들을 어떻게 처리할지 결정합니다.
  1 : 기본값으로 그 값들을 AVERAGE를 계산합니다.
  2 : SUM으로 계산합니다.
  3 : Count로 계산합니다.
  4 : CountA로 계산합니다.
  5 : 최소값(Min)으로 계산합니다.
  6 : 최대값(Max)으로 계산합니다.
  7 : 중위값(Median)으로 계산합니다.

3. FORECAST.ETS.STAT 함수

FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation]) : ETS 모델을 계산할 때는 몇가지 계수를 계산해야 합니다. FORECAST.ETS 함수는 그것을 자동을 계산해서 결과에 반영합니다.

.STAT 함수의 경우 그 값들을 사용자가 볼 수 있게 하나하나 출력하는 함수입니다.

• values : ETS 모델에 적용할 DATA들이고 timeline에 대응되는 값들입니다.
• timeline : 값이 배열된 시간의 데이터입니다. 시간들 사이에는 일정한 간격이 필요합니다. 범위의 데이터들이 꼭 순차적으로 정렬될 필요없이 함수가 알아서 정렬을 합니다. 0을 입력하면 #NUM! 오류를 출력하고 문자를 입력하면 해당 데이터는 무시하고 계산합니다.
• Statistic_type : 함수에서 반환할 값들을 구합니다. 필수요소임으로 꼭 사용자가 선택하여 입력하여야 합니다.
  1 : EST 알고리즘의 α 값으로 수준(Level)의 매개 변수입니다.
  2 : EST 알고리즘의 β 값으로 추세(Trend)의 매개 변수입니다.
  3 : EST 알고리즘의 γ 값으로 계절성(Seasonality)의 매개 변수입니다.
  4 : MASE 메트릭 값입니다. 예측정확도의 측정값이 절대 배율 오차메트릭을 반환합니다.
  5: SMAPE 메트릭 값입니다. 백분율 오류를 기반으로 하는 SMAPE  측정값을 반환합니다.
  6 : MAE 메트릭 값입니다. 백분률 오류를 기반으로 하는 MAE 측정값을 반환합니다.
  7 : RMSE 메트릭 값입니다. 백분률 오류를 기반으로 하는 RMSE 측정값을 반환합니다.
  8 : 기록된 timeline의 단계 크기를 반환합니다.
• [seasonality] : 계절성을 입력하는 항목으로 선택 요소입니다.
  1은 기본값으로 엑셀 함수가 자동으로 계절성을 계산하게 합니다.
  0은 계절성이 없는 모델을 사용하여 계산하는 선형모델을 사용합니다.
  양의 정수를 입력하면 사용자가 입력한 seasonality를 사용해서 계산합니다.
  그 외의 모든 값에 #NUM 오류가 반환됩니다. 지원되는 최대 seasonality는 8,760(1년 동안의 시간)입니다.
  해당 숫자보다 seasonality가 높으면 #NUM! 오류가 반환됩니다.
• [data_completion] : 계측된 데이터에 빈값을 어떻게 할지 결정하는 선택요소입니다. 이 함수에서는 최대 30%의 누락지점을 매꾸는 알고리즘이 지원됩니다.
  1 : 기본값으로 가까운 지점의 평균을 사용해서 누락된 DATA를 계산합니다.
  0 : 누락된 지점을 0으로 간주하는 알고리즘입니다.
• [aggregation] : timelie 요소간에 일정한 단계가 필요한데 중복되는 타임 스탬프가 있을 수 있습니다. aggregation은 이 값들을 어떻게 처리할지 결정합니다.
  1 : 기본값으로 그 값들을 AVERAGE를 계산합니다.
  2 : SUM으로 계산합니다.
  3 : Count로 계산합니다.
  4 : CountA로 계산합니다.
  5 : 최소값(Min)으로 계산합니다.
  6 : 최대값(Max)으로 계산합니다.
  7 : 중위값(Median)으로 계산합니다.

4. 함수 적용해 보기

제가 임의로 만들어서 ETS 함수를 적용해 보겠습니다.

임의로 만든 숫자는 sin 함수에 8을 곱해서 45개를 주기로 하는 계절성을 만들었고,

exp로 감쇄하는 추세를 만들었습니다.

아래 결과를 보면 계절성의 주기는 44개로 계산했고 예상값의 경우 거의 일치하는 모양입니다.

하지만 수학적으로 만든 값이라도 예상값과 약간의 오차가 생깁니다.

수학적으로 타당한 함수이기는 하지만 완전히 믿는 것은 어렵습니다.

< 지수평활법과 ETS 관련 포스팅 > 

01. 시계열 분석에서 미래를 예측하는 이동평균법(SMA), 지수평활법(ES)으로 미래값을 예상하고 풀어보기

02. 지수평활법으로 데이터를 분석할 때 계절성과 주기를 파악하는 방법(Exponential Smoothing의 Seasonality)

03. 시계열 예측에서 지수평활법의 기본 모델(Additive)과와 확장 형태인 감쇠 (Damped) 모델들의 정의

04. 시계열 예측에서 계절성에 따른 추세가 변하는 승법적 모델과(Multiplicative Model), 감쇠하는 승법적 모델(Damped Multiplicative Model)

05. 상황에 맞게 지수평활법과 ETS 모델(Exponential Triple Smoothing)을 사용해서 시계열 예측을 수행하기

06. 지수평활법에서는 사용하는 계수(α, β, γ)를 최소제곱법으로 구하는 방법(엑셀 VBA 구하기)

07. 엑셀(EXCEL)로 지수평활법의 ETS 모델을 사용하는 FORECAST.ETS 함수와 파생함수인 SEASONALITY / STAT 함수 - 현재글

08. 엑셀(EXCEL)의 FORECAST.ETS 함수의 오차를 계산하는 CONFINT 함수(지수평활법을 사용하는 미래 예측 함수) 

한글은 한자어를 사용해서 개념적으로 어려운 단어를 만들고 음절을 생략하는 식으로 약어를 만듭니다.

영어에는 단어를 쓰고 난 후에 각 단어의 첫 영자를 따서 약어를 만듭니다.

축구에 관련된 각종 행정을 집행하는 단체를 말할 때는,

• 한글 : 대한 축구협회 → 축협
• 영어 : Football Association → FA, 한국 축구협회는 Korea Football Association → KFA

같은 방식입니다.

이런 기능은 기본 함수로 있을법한데 없는 모양입니다.(그냥 제가 모르는 건지도요. ㅎㅎㅎ)

어떤 영자에서 이렇게 이니셜을 추출해주는 방법을 알아보겠습니다.

1. VBA 소스코드

VBA로 함수를 만드는 법을 먼저 알아보겠습니다.

① Alt + F11 혹은 시트에 오른쪽 클릭 후 소스보기를 선택합니다.

② 화면 왼쪽에 프로젝트창에서 시트부분에 마우스 오른쪽 클릭하여 삽입 → 모듈을 선택합니다.

③ 새로 만들어진 모듈에 입력합니다.

입력해야 하는 소스코는 아래와 같습니다.

< 소스코드 >

Function getinitial(inputValue As Variant) As String
    Dim words() As String
    Dim abbreviation As String
    Dim i As Integer
    Dim stopWords As Variant
    Dim word As String
    
    '① 이니셜에서 제외하고 싶은 문자를 선택합니다.
    stopWords = Array("of", "to", "for", "and", "the", "a", "an", "in", "on", "with")
    
    '② 셀에서 값을 불러왔다면 이 구문이 작동합니다.
    If TypeOf inputValue Is Range Then
        inputValue = inputValue.Value
    End If
    
    
    '③ 공백 " " 을 기준으로 글자를 나눕니다.
    words = Split(inputValue, " ")
    abbreviation = ""
    

    For i = LBound(words) To UBound(words)
        '④ 머리글자만 따서 글자를 변수 "abbreviation"에 누적하여 입력합니다.
'--------------------------------------------------------------------------------
        word = LCase(words(i))
        If IsError(Application.Match(word, stopWords, 0)) Then
            abbreviation = abbreviation & UCase(Left(words(i), 1))
        End If
'--------------------------------------------------------------------------------

        '⑤ 제외하고 싶은 글자가 없다면 위의 문구를 주석처리하고 아래만 사용합니다.
        'abbreviation = abbreviation & UCase(Left(words(i), 1))
    Next i
    
    getinitial = abbreviation
End Function

위에 주석처리를 해 두었지만 동작 순서는 다음과 같습니다.

① 이니셜에서 제외하고 싶은 문자를 선택합니다.

② 셀에서 값을 불러왔다면  따로 복사해서 변수에 붙여 넣습니다.

③ 공백 " " 을 기준으로 글자를 나눕니다.

④ 머리글자만 따서 글자를 변수 "abbreviation"에 누적하여 입력합니다.(대문자로 만듭니다.)

⑤ 제외하고 싶은 글자가 없다면 if문을 사용하지 않습니다.

이 소스를 입력하고 저장하면 본 구문에서  getinitial 함수라는 새로운 함수가 만들어집니다.

2. 사용하는 법 보기

이제 새로 추가한 함수를 살펴보도록하겠습니다.

정상적으로 동작하는 경우 보통 함수처럼 빠른 완성을 사용할 수 있습니다.

복사해서 붙여넣을 수도 있고 정상적으로 작동합니다.

영문으로 풀었을때 of는 제외되는 것까지 확인되네요

3. 배열함수로 만들기

VBA는 조금 어렵게 느껴질 수도 있습니다.

이 기능은 기본 함수의 콤비네이션으로 만들 수도 있습니다.

똑같은 기능으로 만들 수는 없고 제한적으로 이용합니다.

=UPPER(LEFT(A1, 1)) & UPPER(MID(A1, FIND(" ", A1) + 1, 1)) & UPPER(MID(A1, FIND(" ", A1, FIND(" ", A1) + 1) + 1, 1)) & UPPER(MID(A1, FIND(" ", A1, FIND(" ", A1, FIND(" ", A1) + 1) + 1) + 1, 1))

위의 함수는 4개로 이루어진 단어에만 작동합니다.

예를 들어, aa bb cc dd → ABCD로 동작합니다.

이렇게 복잡한 함수는 VBA로 만드는 쪽이 더 간편하고 유연합니다.

VBA에 거부감이 있어 함수로 사용하는 분은 이참에 한번 가벼운 사용을 배워보기를 추천드립니다.