https://toast-story.tistory.com/18

엑셀은 날짜를 지원하는 서식이 있어서 편리하게 달력표기를 할 수 있습니다.

날짜서식에 대한건 위의 포스트 참고해주시면 되겠습니다.

기간을 계산하는 함수에 대해서는 이전에 있어서 좀더 복잡한 함수를 소개하려 합니다.

1. WORKDAY, WORKDAY.INTL 함수

휴일을 제외한 날짜를 계산하는 함수입니다.

바로 내용에 대해서 설명하겠습니다.

WORKDAY(start_date, days, [holidays]) : 시작일자(start_date)에서 날짜(days)가 지나고 난 후에 날짜를 계산합니다. 이때 휴일(holidays)는 제외합니다.

• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• [holidays] : 주말과는 달리 비정기적인 휴일입니다. 리스트로 만들어서 넣거나 {2023-01-07, 2023-05-18}등 문자열을 직접 입력할 수도 있습니다.
  
  

계산의 결과가 되는 날이 휴일인 경우에는 그것을 지난 평일을 출력합니다.

엑셀에서는 기본 공휴일이 없으니 공휴일 리스트는 달력을 보거나 인터넷에서 직접 넣어야 합니다.

WORKDAY.INTL(start_date, days, [weekend], [holidays]) : 시작일자(start_date)에서 날짜(days)가 지나고 난 후에 날짜를 계산합니다. 이때 휴일(holidays)과 주말을 제외합니다. 정기적인 휴일인 주말은 문자열이나 숫자로 변경할 수 있습니다.

• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• [weekend] : 주말일이고 전체 근무일 수에 포함되지 않은 정기적인 휴일인 주말을 지정하는 숫자나 문자열입니다.
• [holidays] : 주말과는 달리 비정기적인 휴일입니다. 리스트로 만들어서 넣거나 {2023-01-07, 2023-05-18}등 문자열을 직접 입력할 수도 있습니다.

오피스 2010 버전에서 추가된 함수입니다.

정기적인 휴일인 주말, [weekend]는 "주말문자열"과 "주말 숫자" 두가지 형식으로 넣을 수 있습니다.
주말문자열은 (월화수목금토일)을 "0000000"으로 나타낸 것으로 "0100000"은 화요일만 휴일이 됩니다.
주말숫자표는 아래와 같습니다.

함수가 복잡하니 사용예시를 들어 보이겠습니다.

근무일수로 600을 넘기려니 주 5일제와 6일제가 엄청나게 차이나네요.

2. NETWORKDAYS, NETWORKDAYS.INTL 함수

이 함수는 구하고자 하는 것만 다르지 workday 함수와 거의 같습니다.

따라서 설명도 중복과 비슷하게 되겠지만 목적이 다른만큼 약간 차이가 있습니다.

NETWORKDAYS(start_date, end_date, [holidays]) : 시작일자(start_date)와 마지막 날짜(end_date) 사이의 휴일을 제외하고 기간을 계산합니다.

• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• [holidays] : 주말과는 달리 비정기적인 휴일입니다. 리스트로 만들어서 넣거나 {2023-01-07, 2023-05-18}등 문자열을 직접 입력할 수도 있습니다.

위의 Wokday 함수는 기간이 지난 후의 날짜를 구하는 함수고 Holidays 함수는 날짜 사이의 기간을 구합니다.

휴일의 입력방법은 같습니다.

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) : 시작일자(start_date)와 마지막 날짜(end_date) 사이의 휴일과 정기적인 휴일인 주말을 제외하고 기간을 계산합니다.  정기적인 휴일인 주말은 문자열이나 숫자로 변경할 수 있습니다.

• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• [weekend] : 주말일이고 전체 근무일 수에 포함되지 않은 정기적인 휴일인 주말을 지정하는 숫자나 문자열입니다.
• [holidays] : 주말과는 달리 비정기적인 휴일입니다. 리스트로 만들어서 넣거나 {2023-01-07, 2023-05-18}등 문자열을 직접 입력할 수도 있습니다.

오피스 2010 버전에서 추가된 함수입니다. WORKDAY.INTL 함수와 비슷한 입력형식을 가집니다.

[weekend]는 "주말문자열"과 "주말 숫자" 두가지 형식으로 넣을 수 있습니다.
주말문자열은 (월화수목금토일)을 "0000000"으로 나타낸 것으로 "0100000"은 화요일만 휴일이 됩니다.
주말숫자표는 아래와 같습니다.

workday 함수와 사용예시 또한 비슷합니다.

이렇게 보면 주5일제가 얼마나 효과적인지 잘 보이네요.

SUM함수에 대해서 지난번에 집어 봤습니다. 간단한 상황이라면 SUMIF로 대부분 해결됩니다.
https://toast-story.tistory

 
컴퓨터에게 연산을 시킬 때는 최대한 단순한게 좋습니다.
그러나 상황에 따라서 2개 이상의 제약조건을 걸어 복잡해지져야 합니다.
그럴 때 사용하는 SUMIFS 함수입니다.
 

1. SUMIFS 함수

 
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 
 : 조건들을 입력하여 덧셈을 구합니다. 조건은 127개를 입력할 수 있어서 매우 긴 함수가 만들어질 수 있습니다.
최소한의 1개의 범위가 필요한데 어지간하면 많아도 3개 정도면 충분한데 그래도 함수가 많이 깁니다. 오류를 배제하기 위해 신중하게 만들어야 합니다.

• sum_range : 합계를 구할 셀의 범위입니다.
• criteria_range1 : 조건을 검사할 범위입니다. 범위에서 항목이 발견되면 sum_range에 있는 해당 값을 더합니다.
                            sum_range와 행 또는 열을 공유하여 오류가 나지 않도록 합시다.
• criteria1 : 조건입니다. 그냥 단일한 값이면 "=" 연산으로 계산하지만 다른 논리연산자도 입력할 수 있습니다.
• criteria_range2, criteria2, : 추가적인 조건입니다. criteria_range와 criteria의 순서로 연속적으로 입력하여야 합니다.

 
오른쪽의 표에 여러 부분에 조건을 거는 예시를 보여드리겠습니다.
 
① range에는 먼저 덧셈을 수행할 E4:E29 범위를 지정합니다.
② 조건 첫번째로 등급 D4:D29에서 A를 추출합니다. 텍스트를 직접 입력할 때는 쌍따움표 ""를 사용해서 입력합니다.
③ 이름인 C4:C29 안에서 "사과"를 셀번호 i4로 검색하도록 합니다.
④ 날짜항목에서는 "<="연산자를 &와 조건을 묶어서 "2월 8일 이전에"라는 조건식을 작성합니다.
⑤ 목록에서 "2월 8일 이전에 A 등급, 사과"의 수를 합하는 함수가 만들어졌습니다.
설명하자니 정말 길어지네요. 아래를 보겠습니다.
 

2. SUMPRODUCT 함수

 
이 함수는 본래 사용법과 배열함수로서 사용법이 있습니다.
배열함수로 사용하면 오늘의 주제이 조건합계를 구할 수 있습니다.
원래 사용법부터 알아보겠습니다. 기능은 "곱해서 더한다"입니다.
 
SUMPRODUCT(array1, [array2], [array3], ...) : 범위의 숫자들을 첫번째부터 곱해서 더합니다.
 
array1은 {1,2,3,4}이고 array2는 {5,6,7,8}입니다.
SUMPRODUCT로 이 두 배열을 연산하면 1*5 + 2*6 + 3*7 + 4*8이 됩니다.
단위변환 같은걸 할때 편리하게 사용할 수 있습니다.

 
배열함수로 사용하는 방법을 보겠습니다.
위에서 본 "2월 8일 이전에 A 등급, 사과"의 수를 합하는 함수를 SUMPRODUCT로 만들어 보겠습니다.
생각보다 간단한데요. SUMPRODUCT((조건1) * (조건2) *더할범위) 배열함수라서 Ctrl+Shift+Enter로 입력해야 합니다.
조건은 ()안에서 작성되어야 하고 열과 행이 맞아야 합니다. 그리고 조건 안에 오류가 하나도 없어야 합니다.
 

 
이 배열 함수는 하나씩 연산합니다. 
리스트를 하나씩 보면 "이름 = 사과" = trure(1), "등급 = A" = true(1)가 되니까 연산해서 1 x 1 x 20(갯수) = 20이 됩니다.
조건이 하나라도 틀리면 "이름 = 망고" = False(0)가 되서 0이 되기 때문에 더해지지 않습니다.
그럼 조건이 만족하는 수는 남게되고 결과적으로 조건합을 얻을 수 있습니다.
이런 원리로 SUMIFS와 같은 결과를 얻을 수 있습니다.
 

 
예전 버전의 엑셀에서는 SUMIF가 없었습니다.
SUMPRODUCT의 배열함수를 통해서 사용했었죠. 엑셀이 보다 대중화 되면서 SUMIFS가 업데이트 되었습니다.
따라서 지금은  SUMPRODUCT는 사용할 필요가 없습니다.
그래도 아직도 사용하시는 분들이 많아 한번 설명해 보았습니다.
새로 배우시는 분들이라면 SUNIF와 SUNIFS에 익숙해지는걸 추천합니다.
개인적으로는 좋아하거든요. 전혀다른 기능 두개가 합쳐서서 새로운 가능성이 보이는 것이 좋죠.
 
 
 
 

목차

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)
각 항목을 도움말을 그대로 사용했습니다.