목차

• < 개요 >
• 1. Round 함수의 적용
• 2. 자리수 늘림 줄임으로 표시형식 변경하기
• 3. 셀서식에서 표시형식 변경하기

목차

• < 개요 >
• 1. Round 함수의 적용
• 2. 자리수 늘림 줄임으로 표시형식 변경하기
• 3. 셀서식에서 표시형식 변경하기

< 개요 >

엑셀을 익숙해지면 계산기를 대체할 정도로 편리한 연산능력이 있습니다.
책상에 계산기가 있어도 엑셀을 켜서 쓸 정도로 버릇이 되어 있습니다.
일상적인 사칙연산을 하다보면 소수점이 발생할 수 있는데 보기에 따라서 문서가 보기 힘들어질 수 있습니다.그래서 자주쓰는 양식에는 자리수를 지정해 둬서 보기 좋게 만들 수 있습니다.
이런 소수점을 처리하는 방법에 대해서 알아보겠습니다.

1. Round 함수의 적용

함수를 적용해서 특정 소수점 자리수 이하는 짤라 낼 수 있습니다.
반올림 함수인 ROUND 함수를 소개합니다.

ROUND(number, num_digits) : 숫자를 자리수(num_digits)까지 반올림 합니다.

• number : 대상 숫자입니다.
• num_digits : 표시할 자리수입니다. 양수이면 소수점이 아래로 내려가고 음수이면 정수단위로 올라옵니다.

예시를 들어서 설명하겠습니다. 아래를 자리수가 +일때와 -일때 표기가 편리하게 바뀝니다.

ROUND 함수는 숫자자체에 반올림을 시켜서 값이 변하기 때문에 계산을 하면 변경한 값으로 적용이 됩니다.
ROUNDUP과 ROUNDDOWN도 있는데 사용법이 같은 ROUND는 반올림하지만 UP은 올림을, DOWN은 내림을 함니다.

ROUNDUP(number, num_digits) : 숫자를 자리수(num_digits)까지 올림 합니다.

ROUNDDown(number, num_digits) : 숫자를 자리수(num_digits)까지 내림 합니다.

ROUND 계열 함수들은 연산을해서 숫자 자체를 변경합니다. 

계산을 여러번 하는 경우에는 마지막에 한번만 적용해 주는 것이 정확합니다.

2. 자리수 늘림 줄임으로 표시형식 변경하기

매뉴창에 [홈] - [표시형식] - [자리수 늘림], [자리수 줄임] 누르면 한자리씩 정리합니다.

엑셀은 기본 설정상태일 때는 숫자를 치면 자동으로 숫자 서식이 적용됩니다.
숫자 서식이 적용된 칸에 늘림, 줄임 버튼을 여러 번 사용하면 소수점 표시내용을 길거나 짧게 만들수 있습니다.
여러 범위를 드레그해서 "TAP"이나 "ENTER"로 하얀 범위를 옮길 수 있는데 이때는 하얀칸을 기준으로 합니다.
마우스로 드레그할 경우 중간 소수점 자리를 스스로 계산하니 예측하기 어려울 수 있습니다.
한자리로 맞춰주기 때문에 편리합니다.

이건 서식상의 표시만 달라지지 사실 숫자는 그대로 있습니다.

적용 이후에도 계산을 하면 반영이 됩니다.

숫자 서식인 "숫자", "통화", "회계", "백분율"에도 적용할 수 있습니다.

3. 셀서식에서 표시형식 변경하기

셀을 오른쪽 클릭하면 나타나는 [셀 서식] → [표시형식] → [소수 자리수]에서 변경할 수 있습니다.

마지막으로 설명했지만 셀서식에서 변경하는 것이 근본이고 가장 원하는 방식으로 변경할 수 있습니다.

이 역시 서식상의 표시만 달라지지 사실 숫자는 그대로 있습니다.

적용 이후에도 계산을 하면 반영이 됩니다.

숫자 서식인 "숫자", "통화", "회계", "백분율"에도 자리수를 정할 수 있습니다.

※ 엑셀 2013의 매뉴를 기반으로 하며 모니터 해상도에 따라 출력되는 아이콘의 모양이 다를 수도 있습니다.

다만 소속은 거의 같으니 매뉴를 따라가시면 기능을 금방 찾을 수 있을 것 같습니다.

판매량, 생산량, 매출 같은 데이터의 변화추세를 한눈에 보는 막대챠트를 자주사용합니다.
중요한 데이터를 가지고 그래프를 그릴때 허전해 보일때가 있습니다.
그래프를 강조하는 혼합형 차트를 그려봅시다.
그냥 보기만해도 혼합형이 선이있어서 오른쪽으로 가면 값이 커지는게 강조됩니다.

혼합형을 그리는 방법은 두가지 입니다.

1. 혼합형 바로 그리기

우선 테이터를 선택하고 [삽입] - [챠트] - [혼합형]를 그립니다.
막대형과 꺽은선으로 그리는 방식이 엑셀에서 자동으로 선정하는데 좀 특이합니다. 
세로인 경우 범위에서 오른쪽에 있는 것이 가로인경우 아래 있는 DATA가 꺽은 선형으로 분류됩니다.

DATA가 2개 : (막대) + (꺽은선)
DATA가 3개 : (막대) + (막대) + (꺽은선)
DATA가 4개 : (막대) + (막대) + (꺽은선) + (꺽은선)
DATA가 5개 : (막대) + (막대) + (막대) + (꺽은선) + (꺽은선)

2. 기존 차트에서 수정하기

또 다른 방법은 우선 다중 막대 그래프로 그리고 하나만 선택해서 다른 차트 기능을 활성화 시키면 됩니다.
사실 어렵지도 않고 원하는 데로 그릴 수 있기 때문에 이 방법을 더 많이 사용합니다.
[삽입] - [차트] - [꺽은선 그래프]로 변경합니다.

이제 그래프 하나에 두가지 서식이 적용되었습니다.
설정을 변경 할 때는 한번에 한가지 범주를 선택하고 설정하면 됩니다.

그럼 이렇게 설정할 수 있습니다.

기능적으로는 별로 어렵지 않습니다.

하지만 디자인은 다른 문제라 예쁘게 챠트를 그리기가 어렵더라고요.

그래도 의사표현을 잘하기 위한 팁은 X축과 Y축을 명확하게 명시하고 혼합형그래프를 사용하는 것입니다.

저는 미적감각이 거의 없는 사람이라서 가끔 구박받고는 합니다.
하지만 이렇게 표현하면 최소한 깔끔하면서 말하고자 하는 것을 깨끗하게 할 수 있겠죠
경험상 메시지가 명확해 진 것만으로도 불만은 많이 줄어들더라고요.

이 그래프는 똑같은 그래프를 두개 그려서 하나는 막대 하나는 꺽은선으로 만든 것입니다.

제 작품중에서는 그나마 보기 깔끔하더라고요

각자 상황에서 보기 좋은 차트를 연습해 봅시다.

엑셀을 여러가지 서식을 지원합니다.

년도와 월 일로 이루어진 날짜 서식은 주차를 컨트롤 할 수 있는 함수를 제공합니다.

1. 주차(Week) 세는 법

한해는 1주부터 51~2주로 이루어 져있습니다. 각 주차를 세는 방법을 보는 함수 먼저 설명하겠습니다. 

ISOWeeknum(DateTime) :  ISO 8601 날짜 및 시간 표준 정의에 따라서 날짜의 주차 번호를 지정합니다.

기본적으로 두 함수는 같은데 ISOWeeknum는 처음 목요일이 포함된 주차를 1주차로 칩니다.

그래서 1월 1일은 작년도 마지막 주차가 될 수 있습니다.

22년도를 예로 들겠습니다.

WeekNum(DateTime [, StartOfWeek ]) : 주차 수를 반환합니다.

1월 1일이 포함된 주를 연도의 첫 번째 주로 사용합니다. 

요일에 상관없이 12월 31일은 마지막주, 1월 1일은 올해 첫째주입니다.

• DateTime - 필수 항목입니다. 연산을 수행할 날짜/시간 값입니다.
• [StartOfWeek] - 선택 사항입니다. 주의 시작 요일을 Excel 코드 또는 StartOfWeek 열거로 입력할 수 있습니다.

WEEKDAY(serial_number,[return_type]) : 날짜에 해당하는 요일을 반환합니다. 기본값은 1(일요일)에서 7(토요일) 사이의 정수로 표시됩니다.

• serial_number : 입력할 날짜로 날짜 형식이여야 합니다.
• [return_type] : 선택 요소입니다. 반환 값 유형을 결정하는 숫자입니다.

2. 일자사이의 요일을 세는 함수

시작일과 마지막일 사이에 "??"요일이 몇개 있는지 세는 함수입니다.

계산을 위해 월요일(1) ~ 일요일(7)로 잡겠습니다.

=(WEEKNUM(마지막)-WEEKNUM(시작)-1)+IF(WEEKDAY(마지막,2)<=요일,1,0)+IF(WEEKDAY(시작,2)>=요일,1,0)

<설명>

일주일에 요일이 하나씩 있으니 (WEEKNUM(마지막)-WEEKNUM(시작)-1) 는 첫주와 마지막주를 뺀 주차의 수가 됩니다.

그리고 시작주와 마지막주에 요일이 포함되면 더하는지를 알기 위해 

IF(WEEKDAY(마지막,2)<=요일,1,0)+IF(WEEKDAY(시작,2)>=요일,1,0)가 포함됩니다.

목차

1. 시트의 종류

2. 시트의 활성화 - Activate

3. WorkSheet를 컨트롤 하는 명령어

엑셀은 3차원 혹은 4차원 데이터 체계입니다.

우선 행과 열로 보이는 2차원으로 시트가 이루어져 있고 시트가 모여서 3차원의 파일이 됩니다.

파일을 모아서 폴더를 형성하고 DATABASE로 이어 질 수 있겠지만 여기까지는 생각하지 말죠.

기본적으로 시트인터페이스는 쉽지만 VBA로 사용하는 법을 정리했습니다.

엑셀 인터페이스에서는 맨 하단에 시트의 정보가 표시됩니다.

VBA 개발도구 인터페이스에서는 좌측에 표시됩니다.

엑셀 인터페이스에서는 시트 이름이 표시되고

VBA 인터페이스 Microsoft Excel 개체에서는 Sheet1 (Sheet1) = Sheet(시트번호) (시트이름)으로 표기됩니다.

시트를 삭제하고 시트를 추가하면 해당 번호가 없어지지만 엑셀파일을 종료했다 다시 열면 그번호를 메꿔집니다.

예시

• 처음 : Sheet1, Sheet2, (Sheet3-삭제), Sheet4, Sheet5 ← 삭제를 해도 무시하고 이후 번호가 하나씩 올라갑니다.
• 파일을 껏다 다시 켜면 : Sheet1, Sheet2, Sheet4, Sheet5 ← 3이 없는 상태
• 이 상태에서 만들면 : Sheet1, Sheet2, Sheet4, Sheet5, Sheet3 ← 3이 다시 생겨요

1. 시트의 종류

VBA에서는 시트의 종류를 아래처럼 표시합니다.

2. 시트의 활성화 - Activate

특정 시트를 지정해서 작업을 할 수도 있고 작업을 할 시트를 미리 activate로 활성화 할 수 있습니다.

workbooks("파일이름").worksheets(1).activate        '시트번호 1을 활성화한다.
workbooks("파일이름").worksheets("설명").activate   '시트이름이 "설명"인 시트를 활성화한다.

3. WorkSheet를 컨트롤 하는 명령어

sheet.Add (Before or After, Count, Type) : 엑셀의 시트를 추가한다.

• "Before" or "After" : 몇번째 시트 전에 혹은 후에 추가할지 선택
• Count : 몇 개의 Sheet를 만들지 선택, 입력하지 않으면 1개 추가
• Type : 워크 시트의 종류를 지정한다.

Set Sheet = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(2), Count:=3)
'시트번호 2번 뒤에 시트 3개를 만든다.

여러개를 만들면 sheet3, sheet2, sheet1 순서로 만들고 마지막의 sheet3가 활성화됩니다.

expression.Copy (Before, After) : 엑셀의 시트를 카피한다.

• "Before" or "After" : 몇번째 시트 전에 혹은 후에 추가할지 선택

Worksheets("Sheet1").Copy After:=ActiveWorkbook.Worksheets(3)
'시트이름 sheet1을 3번 시트 뒤에 카피한다.

expression.Delete : 시트를 지운다.(DisplayAlerts을 변경해서 묻는 창을 뜨게 할 지 선택가능)

Application.DisplayAlerts = False  '시트 삭제시 묻는 창을 안뜨게 한다.
worksheets("sheet1").delete        'sheet1을 삭제한다.

expression.FillAcrossSheets (Range, Type) : 동일한 값을 여러시트에 붙입니다.

• Range : 어디에 있는 값을 붙여 넣을 것인지를 선택
• [Type] : 선택사항으로 어떻게 입력할 지를 선택한다.

Sheets.FillAcrossSheets Worksheets(1).Range("A1:C5")
'sheet1의 "A1:C5" 범위를 모든 시트에 붙여 넣는다.

[Type] 은 아래과 같습니다.

expression.Move (Before or After) : 시트를 이동한다. "Before" or "After"로 몇번째 시트 전에 혹은 후에 추가할지 선택

expression.Select (Replace) : 시트를 선택한다. Replace가 True면 단일선택, False는 중복 선택

expression.name : 시트의 이름을 불러오거나 변경한다.

a = worksheets(1).name               '1번 sheet의 이름을 불러온다.
worksheets("설명").name = "변경"     '설명 시트의 이름을 변경으로 변경한다.

expression.visible = 0 or 1 : 0이면 숨기기, 1이면 숨기기 취소

expression.protect or unprotect : protect면 시트를 보호하고, unprotect는 시트를 보호하지 않는다.

expression.next or previous.select : 지정한 시트의 전 혹은 후의 시트를 선택합니다.

목차

1. 상관관계에 대해서

2. Fitting 하기

3. 상관계수 R2에 대해서

4. Linest 함수

5. Linest 함수의 추가요소(R2 값 얻기)

1. 상관관계에 대해서

높은 품질의 제품은 정확한 계측기술로 얻을 수 있다는게 개인적인 생각입니다.
하지만 비용을 적게 들이는 평가 기술을 개발해야 제품에 적용할 수 있습니다.

그러기 위해서는 여러가지 요인의 상관성을 분석을 해야 하는데 회귀분석이라고 합니다.
그러나 저는 이 과정을 Fitting이라고 부릅니다. 이걸 잘하면 비용 절감을 효율적으로 이룰 수 있습니다.
상황은 보통 아래와 같습니다.

CASE ①. 외부 계측기로 1시간 측정해야 하는 A와 내부에서 계측 가능한 B
CASE ②. 완성품으로 알 수 있는 A와 단품에서 알 수 있는 B

B는 상대적으로 비용이 적게 드는 평가 항목이니 상관성을 잘 확보하면 A를 측정하는 것과 같은 효과를 낼 수 있습니다.

2. Fitting 하기

예시 DATA 를 분산형 그래프로 그려 보겠습니다.

보통의 경우 A가 더 정확하지만 B로 진행할때 절약이 됩니다.
아래 DATA를 분산형 그래프로 그리면 A가 증가하면 B도 증가한다는 것을 알 수 있습니다.
그런데 정확하게 얼마가 들어가느냐를 알기 위해서 추세선을 그립니다.

수식을 챠트에 표시하게 하면 "Y=2.1223x + 2.3716" 이라는 방정식을 얻었습니다.

또 상관계수 R2 = 0.9523도 얻을 수 있습니다.
실험의 오차가 있겠지만 그래도 B는 A의 2배가 약간 넘는 것으로 보이네요.

3. 상관계수 R2에 대해서

또 R2 값이 주어지는데 이는 실제 실험한 DATA가 이 추세선에서 벗어 나는 정도입니다.
RMS방식으로 완벽하게 일치하면 값이 1로 나옵니다.
기준이야 적용하기 마련이지만 0.95이상이면 A와 B는 충분히 "상관있다"고 결론 내릴 수 있습니다.

1차 함수, 즉 선형성만 확인하는 경우가 많습니다. 하지만 상황에 따라서 다차방적식을 고려해야 하죠.
추세선 속성에서 [다항식]을 선택하면 차수를 높일 수 있습니다.

추세선 옵션 아래에서 "R-제곱 값을 차트에 표시"하면 얻을 수 있습니다.

3차까지 높였을 때 0.96까지 R2가 상승하는 것을 알 수 있습니다.
다만 4차 이후에는 변곡점의 모양으로 봤을때 큰수에서 신뢰성이 떨어집니다.

사실 차수가 높아지면 숫자가 커질때 문제가 될 수 있습니다.
고차 방정식의 경우 ① X 축을 0~1까지 스케일링 해서 사용할 수 있을때 혹은
② DATA의 크기가 한도가 있고 거의 거기까지 실험을 했을 때 사용할 수 있습니다.

이것을 못할 땐 그냥 선형방정식만 보고 판단하는게 더 나을 수 있다는 것이죠.

4. Linest 함수

그래프를 일일히 그리지 않고 바로 숫자로 분석하는 Linest 함수를 소개합니다.

LINEST(known_y's, [known_x's], [const], [stats])

• known_y's : 필수 요소입니다. y = mx + b 식에서 이미 알고 있는 y 값의 집합입니다.
• [known_x's] : 선택 요소입니다. y = mx + b 식에서 이미 알고 있는 x 값의 집합입니다.
  입력하지 않으면 1, 2, 3, 4, … 이 들어갑니다.
• [const] : b가 True이면 정상적으로 계산 False면 0으로 고정됩니다.
• [stats] : 선택요소로 True면 추가 통계값을 반환합니다.

Linest는 2개이상의 결과값을 반환합니다.
=LINEST(C23:C32,B23:B32,1,0) 라는 수식을 적으면 "Y=2.125x + 2.354" 의 2개 숫자를 반환합니다.
그래서 INDEX 함수를 추가로 사용해야 합니다. 아래 표를 보시면 INDEX의 활용법을 알수 있습니다.

다항식을 적용하면 좀더 길어집니다.
12차까지 적용할 수 있으면 하는 방법은 

LINEST(known_y's, [known_x's]^{1,2,3, ... , 12}, [const], [stats])
이 다차방정식으로 들어가면 여러가지 솔루션을 마련할 수 있습니다.

예시 : =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,0),1)

5. Linest 함수의 추가요소(R2 값 얻기)

LINEST(known_y's, [known_x's], [const], [stats])
Linest의 구성중 [stats]가 TRUE(1)를 알아 보겠습니다.

[stats]을 1이나 True로 입력하면 입체적인 DATA가 됩니다.

예시 : =LINEST(C21:C30,B21:B30^{1,2,3},1,1)

이렇게 되면 2차 행렬 형식으로 index를 작성하면 됩니다.
여기서 우리가 가장 궁금한 R2는 (3,1)입니다. 이걸 가장 많이 사용합니다.
입력 방법 : =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,1),3,1)
각 항목을 도움말을 그대로 사용했습니다.