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일제가 얼마나 효과적인지 잘 보이네요.

목차

1. 단순 날짜 연산

2. DAYS, DAYS360 함수 - 기간을 계산

3. DATEDIF 함수 - 기간을 일 단위로 계산

4. EDATE, EOMONTH 함수 - 몇달이 지났는지 세는 함수

지난번에 날짜 서식의 기본에 대해서 포스팅했습니다.

서식에 대해서는 설명 드렸기 때문에 오른쪽링크 참고 하시면 됩니다.(날짜 서식과 기본 함수들)

하지만 단순히 연도, 월, 일자를 표기하는 것을 넘어서 기간을 계산하는 강력한 함수들도 있습니다.

이걸 사용하면 달력을 펴고 날짜를 일일히 셀 필요는 없다는 것이죠.

1. 단순 날짜 연산

지난번 포스팅에서도 설명을 했지만 날짜서식이라고 쳐도 기본적으로는 숫자입니다.

그래서 사칙연산이 먹힙니다. 중복이기는 하지만 잠시 집고 넘어가겠습니다.

2. DAYS, DAYS360 함수 - 기간을 계산

DAYS(end_date, start_date) : start_date에서 end_date 사이가 몇일인지 계산합니다.

• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• 윤년과 월에 따른 일자를 포함하여 계산함으로 편리합니다.
  특히 계산하고자 하는 날짜가 수백일이 되면 이 함수만큼 좋은게 없습니다.
• 이 함수는 2013버전에 추가되었습니다. 사용할 수 없을 수 있습니다.

텍스트 "2022-3-1"처럼 일부 날짜 형식은 알아서 변환합니다.

입력된 날짜 서식들은 그냥 빼기를 하는 것과 같은 결과가 나옵니다.

여러 엑셀 파일이나 홈페이지, 외부문서에서 값을 복사해서 붙여넣는 경우에는 날짜처럼 보이지만 텍스트처럼 인식되는 경우가 있는데 이럴때 편리합니다. 하지만 "3월 2일" 같은 형식의 텍스트는 인식하지 못하네요.

DAYS360(start_date,end_date,[method]) : 한달을 무조건 30일로 1년을 360일로 계산하는 방식에서 기간을 구합니다.

• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• [method] : TRUE는 유럽식, FALSE는 미국식입니다.
  TRUE : 종료일자가 31일인 경우에는 무시합니다.(30일로 계산합니다.)
  FALSE : 종료일자가 31일인 경우 포함합니다.(다음달 1일로 계산합니다.) - 기본값입니다.
• 윤년과 월에 따른 일자를 포함하지 않습니다..

아직 문서로 처리하던 시절에 일일히 30, 31일로 반영해서 계산하면 오류가 너무 심해서 전부 30일로 변환해서 처리했죠.

아직도 일부에서는 사용하는 방법인데 요즘은 컴퓨터가 다 계산해 주기때문에 오히려 이게 어렵습니다.

아래 예를 보면 잘 이해되실꺼에요.

DAYS360은 2월이나 3월을 전부 30일로 간주함으로 날짜가 지나도 일수가 안늘어나는 기간이 생기게 됩니다.

그때 처리방법이 True와 False로 나타냅니다.

3. DATEDIF 함수 - 기간을 일 단위로 계산

DATEDIF(start_date,end_date,unit) : 시작날짜(start_date)와 종료일자(end_date) 사이의 연, 월, 일을 반환한다.

• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• unit : 반환하려는 정보의 양식입니다.

기간의 차이를 "월"이나 "연도"로만 나타낼 수 있습니다.

예를 들어 unit YM을 이용하면 2002-03-08에서 2012-07-01은 3이 됩니다.(연도는 무시 개월만 표기)

엑셀 도움말에는 아직 최적화가 다 되있지 않다는 설명이 있습니다.

반드시 필요할 때만 사용하는 것이 좋겠네요.

4. EDATE, EOMONTH 함수 - 몇달이 지났는지 세는 함수

일자야 어째든 월이 중요한 경우가 있습니다.

입사하고 몇달이 지났는지 등등 말이죠.

그런 상황에서 사용하는 함수들입니다.

EDATE(start_date, months) : 일자(start_date)에서 달이 지난 만큼을 반환합니다.

• start_date : 시작 날짜입니다. 날짜는 DATE 함수를 사용하거나 날짜 서식을 참조하거나 입력합니다.
• months : start_date 전이나 후의 개월 수입니다. 앞으로의 날짜는 양수로, 지나간 날짜는 음수로 입력하면 됩니다.
• 30일이나 31일짜리 달을 무시하고 몇달 지난 날짜를 반환합니다.

EOMONTH(start_date, months) : 일자(start_date)에서 달이 지난 만큼의 월 마지막 날을 반환합니다.

• start_date : 시작 날짜입니다. 날짜는 DATE 함수를 사용하거나 날짜 서식을 참조하거나 입력합니다.
• months : start_date 전이나 후의 개월 수입니다. 앞으로의 날짜는 양수로, 지나간 날짜는 음수로 입력하면 됩니다.
• 30일이나 31일짜리 달을 무시하고 month에 입력된 숫자만큼 지난 달의 마지막날을 반환합니다.
• 설명이 어려워서 그렇지 유용한 함수로 몇일에 입사를 하든지 월급 나오는 날은 같을 경우 등에서 사용됩니다.

단순 숫자를 서식에 따라서 표현할 수 있게 하는 것은 엑셀의 가장 중요한 기능 중 하나입니다.

그 중 날짜는 우리가 데이터를 구별하고 정리하는데 가장 중요한 요소 중 하나입니다.

셀서식의 날짜나 매뉴에 표시형식이 있지만 자동으로 날짜서식으로 변환되기도합니다.

자동으로 넣는것이 가장 유용한데 "2/3"이라고 입력하면 자동으로 (올해) 2월 3일이라고 변환됩니다.

아래처럼 셀서식을 이용하거나 매뉴의 보기형식을 변경할 수도 있습니다.

1. 날짜 서식

엑셀은 숫자 "1"을 1900년 1월 1일로 해서 "2958465"를 9999년 12월 31일까지 날짜 서식을 통해 지원합니다.

강제로 이전이나 이후를 표기하는 건 가능하지만 기간이 넘어가면 날짜 서식을 지원하지는 않습니다.

예를 들어, 숫자 45000이 날짜가 되면 2023년 03월 15일이 됩니다.

그럼 년도(YEAR) 2023, 월(MONTH) 03, 일(DAY) 15이라는 속성을 가지게 되고 함수를 이용해서 속성들을 따로 표기하거나 연산하는 등의 제어를 할 수 있습니다.

윤년과 연도를 포함하여 달력이 잘 반영되어 있습니다.

그래서 날짜 서식에 사칙연산도 적용됩니다.(곱셈과 나눗셈은 위험하고 더하고 빼기만 사용하겠습니다.)

2. 날짜 서식 함수

TODAY() : 오늘 날짜를 출력합니다. 이 함수는 인수가 없습니다. ()안이 비어있어야 합니다.

시스템 상의 오늘날짜가 출력됨으로 엑셀파일을 내일 다시 열면 갱신되어 있습니다.

매일매일 작성하는 엑셀문서에서 사용할 수 있습니다.

DATE(year,month,day) : 숫자들을 날짜 서식의 날짜로 반환합니다.

• =DATE(2021,10,17) → 2021년 10월 17일이 됩니다.
• 연도(year) : 네 자리 숫자로 이루어진 것이 좋습니다. 8이라고 입력하면 엑셀 버전에 따라 1908이나 2008이 될 수 있습니다. "2000+숫자"로 입력하는 것도 좋습니다.
• 월(month) : 1~12월을 입력합니다. 넘는 수를 넣으면 다음해로 넘어갑니다. 예를 들어 14를 넣으면 12+2로 연도가 넘어갑니다. date(21,14,08) = 22년 2월 8일이 됩니다. 0을 입력하면 작년 12월로 넘어갑니다.(음수는 더 전으로 계산합니다.)
• 일(day) : 1~31을 입력합니다. 해당 월의 마지막날을 넘어가면 다음달로 알아서 계산합니다. 0을 입력하면 지난달의 마지막날이 출력됩니다.(음수는 더 전으로 돌아갑니다.)

그 외에 날짜 서식에서 필요한 숫자만 얻을 수 있는 함수입니다.

YEAR(serial_number) : 날짜 서식에서 연도만 반환합니다.

MONTH(serial_number) : 날짜 서식에서 월만 반환합니다.

DAY(serial_number) : 날짜 서식에서 일자만 반환합니다.

데이터에는 평균이 중요한 경우가 많습니다.

수학적으로는 몰라도 그것만으로 우리가 생각하는 중심을 결정할 수 없는 경우가 많습니다.

평균, 중위, 최빈을 알면 분석할 때 보다 정확하게 정보를 해석할 수 있습니다.

엑셀의 도움말에서는 이렇게 설명하네요.

◾ AVERAGE(평균) :  산술 평균이며 여러 수를 더한 다음 더한 수의 개수로 나누어 계산됩니다. 예를 들어 2, 3, 3, 5, 7, 10의 평균은 30을 6으로 나눈 5입니다.
◾ MEDIAN(중앙값) : 숫자 그룹의 중간 수입니다. 즉, 숫자의 절반에는 중위보다 큰 값이 있으며, 숫자의 절반에는 중위보다 작은 값이 있습니다. 예를 들어 2, 3, 3, 5, 7 및 10의 중위는 4입니다.
◾ MODE(최빈값) : 숫자 그룹에서 가장 많이 나타나는 숫자입니다. 예를 들어 2, 3, 3, 5, 7, 10의 최빈값은 3입니다.

계속해서 평균, 중위, 최빈 값을 알아보는 함수를 정리하겠습니다.

1. 중위값 구하기(MEDIAN)

MEDIAN(number1, [number2], ...) : 범위 혹은 숫자들의 중위값을 구합니다.

• 중위값은 많은 수 중에서 순위가 중간인 것을 이야기합니다.
  숫자의 절반은 중위보다 크고 나머지 반은 중위보다 작아집니다.
  {1,2,3,4,5,100,108,120,123} → 중위값은 5
  이 값은 편측으로 크거나 작은 값이 있어도 영향을 받지 않습니다.
  한쪽으로 기울기 쉬운 DATA의 중심을 파악하는데 유용합니다.
• 떨어져 있는 서로 다른 범위나 숫자를 하나하나 입력할 경우 255개의 목록을 입력할 수 있습니다.
• 수들이 짝수개 일 경우에는 중간에 오는 두 수의 평균 - (A+B)/2을 반환합니다.
• 텍스트, 논리, 빈칸은 무시하고 연산하며 0은 계산합니다.

2. 최빈값 구하기(MODE)

모드 함수는 지금도 사용할 수 있지만 최근 버전에는 MODE.MULT와 MODE.SNGL로 나누어 있습니다.

MODE.SNGL(number1,[number2],...) : 데이터의 최빈값을 구합니다. 예전의 MODE와 같습니다.

• 최빈값은 가장 자주나오는 값입니다. 모든 수가 똑같은 빈도를 가지면 #N/A 오류가 발생합니다.
  가장 자주 마주칠 것이라고 기대할 수 있는 DATA를 구합니다.
• 떨어져 있는 서로 다른 범위나 숫자를 하나하나 입력할 경우 255개의 목록을 입력할 수 있습니다.
• 텍스트, 논리, 빈칸은 무시하고 연산하며 0은 계산합니다.
•  

MODE.MULT((number1,[number2],...) : 배열이나 데이터 범위에서 빈도수가 가장 높은 값의 세로 배열을 반환합니다.

설명이 어려운데요. 쉽게 말하면, 최빈값이 여러 개일 경우 전부 찾아 배열형식 보관합니다.

배열은 index를 통해서 원하는 수를 찾을 수 있습니다.

아래 예시를 보겠습니다. 목록(B2:B11) - {1, 1, 2, 2, 3, 3, 4, 5, 6, 7}, 1, 2, 3이 똑같이 2개씩 들어 있습니다.

즉 모든 최빈값이 저장되고 INDEX를 통해서 하나씩 꺼낼 수 있습니다.

3. 평균값 구하기

AVERAGE(number1, [number2], ...) : DATA들의 평균을 구합니다.

우리가 흔히 알고 있는 가장 평범한 평균입니다.

평균을 구한다는 것 자체는 매우 단순합니다. 그래도 평균차제가 많이 사용하니 자주쓰는 함수입니다.

그래서 그런지 여기에는 편리한 파생함수가 몇개 있습니다.

AVERAGEA(number1, [number2], ...) : DATA들의 산술평균을 구합니다.

평균하고 수식은 같은데 텍스트를 처리하는 방식이 다릅니다.

텍스트와 0을 똑같이 계산합니다.

AVERAGE는 텍스트가 있으면 없는걸로 계산을 하는데 여기는 0으로 치니 값에 영향을 줍니다.

AVERAGEIF(range, criteria, [average_range]) : 조건을 만족하는 값들의 평균을 구합니다.

• range : 조건을 검사할 범위입니다.
• criteria : 조건입니다. 특정값이랑 같거나 논리연산자를 사용해서 설정할 수 있습니다.
• average_range : 평균을 계산하는 데 사용할 실제 셀 집합입니다. 지정하지 않으면 range가 사용됩니다.

조건을 정해서 평균을 구하는 것입니다.

SUM이나 COUNT에도 if 파생함수가 있고 사용법이 거의 비슷합니다.

검사할 range에서 사과를 찾아서 같은 열의 숫자를 대상으로 평균을 구합니다.

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) : 조건을 만족하는 값들의 평균을 구하는데 여러가지 조건을 입력할 수 있습니다.

• average_range : 평균을 계산할 숫자들의 범위입니다.
• criteria_range1, criteria_range2, … : 조건을 검사할 값들의 범위입니다. criteria_range1은 필수 요소이고, 이후의 criteria_range는 선택 요소입니다. 1개는 필수이고 이후 128개까지 입력할 수 있습니다.
• criteria1, criteria2, ... : 조건입니다. 검사할 범위에 있는 값들을 하나씩 검사합니다. criteria1은 필수 요소이고, 이후의 criteria는 선택 요소입니다. 숫자, 식, 셀 참조 또는 텍스트 형식의 128개 이하의 평균을 구할 셀을 정의합니다. 

AVERAGEIF 함수의 확장판입니다. 여러개의 조건을 넣을 수 있습니다.

아래에 보면 2월 6일 이전에 사과, A등급의 평균 갯수를 구합니다.

중위값, 최빈값, 평균값이 모두 일치하는 상태를 정상분포라고 합니다.

이들이 멀어지는 모양이나 정도를 보고 DATA를 분석할 수 있습니다.

예를 들어 아래는 경력 1년 이하 사회 초년생의 연봉수준입니다.(고용노동부)

중위값인 2,800만원이니 예로 3,000만원은 높은 수준을 받는다고 생각해도 되겠습니다.

하지만 평균은 3,300만원이니 좋은 조건에 일자리를 구한다면 많이 올릴 수 있다는 뜻입니다.

혹은 상위 25%가 많은 연봉을 독차지 한다고 해석하기도 할 것입니다.

목차

1. COUNT 함수

2. COUNTA 함수

3. COUNTBLANK 함수

4. COUNTIF 함수

5. COUNTIFS

엑셀이 기본적으로 DATABASE 역할을 하는 건 표형식으로 되어 있기 때문입니다.
이런 표의 구성요소들을 세는 Count 함수는 자주 사용하는 통계함수 입니다.
Count 함수가 중요한 만큼 파생함수도 많으니 하니씩 알아보겠습니다.

1. COUNT 함수

 
COUNT(value1, [value2], ...) : 숫자가 있는 셀의 개수와 인수 목록에 포함된 숫자 개수를 셉니다.
포함된 범위의 숫자들을 셉니다.
 
엑셀에서는 날짜나 시간이 숫자를 표현하는 형식만 바꾼것이라서 이것들도 포함합니다.
하지만 논리의 결과 참(1)이나 거짓(0)을 숫자로 보지는 않습니다.
여러가지 범위를 입력 할 수 있는데 서로다른 범위는 최대 255개를 입력할 수 있습니다.
 

2. COUNTA 함수

COUNTA(value1, [value2], ...) : 범위에서 비어 있지 않은 셀의 개수를 셉니다.
범위내에 빈칸을 제외한 모든 것을 셉니다.
데이터의 수를 세기 때문에 특정 리스트가 있을 때 목록의 규모를 구할 때 사용합니다.
사용 빈도와 응용범위가 굉장히 높은 함수입니다. 
 

3. COUNTBLANK 함수

COUNTBLANK(범위) : 셀 범위의 빈 셀 수를 계산합니다. 

말그대로 빈칸의 수를 셉니다. 공백을 조사하거나 값이 없는 것을 셀 때 사용합니다.
0이 들어가 있는데 숨겨져 있는 것은 세지 않습니다.
IF 함수등을 통해서 ""을 출력한 경우는 셉니다.
  

4. COUNTIF 함수

 
COUNT 형제 중 핵심인 COUNTIF 함수입니다.
 
COUNTIF(range, criteria) : 기준을 충족하는 셀들의 개수를 계산합니다.
 

• range : 셈을 수행할 범위입니다.
• criteria : 조건입니다. 단일 값과 같은 값을 세거나, "<"와 같은 논리연산자를 &연산자와 조합해서 사용할 수 있습니다.

 
조건을 만족하는 항목을 숫자이든 텍스트든 수를 세기 때문에 활용도가 높습니다.
리스트를 정리하는 데 사용할 수 있는데 아래의 예를 보겠습니다.
 
이름 행의 종류를 조건으로 하여 사과나 망고의 입고 횟수를 계산할 수 있습니다.
이후 이름이 추가되더라고 간단하게 리스트를 입력 할 수 있습니다.
"논리 연산자 & 숫자" 라는 방식으로 논리연산자를 사용가능합니다.
">="&15 이라고 조건을 작성하면 "15개 이상"이라는 뜻으로 연산합니다.
"<>"&0 이라고 조건을 작성하면 0을 제외하고 세라는 뜻으로 연산합니다.

>, <, >=, <=, <>, = 연산자 모두 같은 방법으로 사용가능합니다.
 

5. COUNTIFS

 
COUNTIF 함수의 확장판인 COUNTIFS입니다.
기능은 비슷한 확장판이기는 해도 용법이 조금 달라서 따로 사용법을 익혀야 합니다.
 
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
 : 여러범위에서 조건을 만족시키는 행을 구합니다. 범위인 RANGE들은 행이나 열중 하나는 통일 되어야 합니다.
그래서 모든 조건을 만족시키는 행들이나 열들의 수를 세는데 "AND" 방식으로 적용된다고 보면 됩니다.

• criteria_range1 :  관련 조건을 평가할 첫 번째 범위입니다.
• criteria1 : 조건을 입력합니다. 예를 들어 criteria를 32, ">32", B4, "사과", "32" 등으로 지정할 수 있습니다.
• criteria_range2, criteria2, ... : 추가 지정할 범위와 조건입니다. 범위와 조건의 쌍을 최대 127쌍 사용할 수 있습니다.

 
아래에 범위들인 3개가 열은 각각 달라도 행을 같은것을 볼 수 있습니다.
이렇게 배치하면 ① 사과이면서 ② A 등급이고 ③ 15이상의 수를 가지는 리스트의 수가 됩니다.
가로세로가 바껴도 엑셀이 알아서 상황을 판단합니다.
시간이나 날짜를 사용해서 보다 복잡한 연산도 수행할 수 있습니다.
 

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에 익숙해지는걸 추천합니다.
개인적으로는 좋아하거든요. 전혀다른 기능 두개가 합쳐서서 새로운 가능성이 보이는 것이 좋죠.
 
 
 
 

가격이나 갯수를 데이터를 분석할 때 가장 기본적인 분석은 합계를 구하는 것입니다.

엑셀을 배운다면 첫날 배우게 되는 SUM 함수는 그래서 자세한 내용은 넘어가는 경우가 많습니다.

기능을 좀 구체적으로 정리하고 파생함수인 SUMIF에 대해서 알아보겠습니다.

기본적으로 "="으로 시작해서 셀의 사칙연산을 입력할 수 있는데 이걸 쉽게 하는게 SUM입니다.

1. SUM 함수

SUM(value) : 입력된 모든 값들의 합을 구합니다. 셀이나 범위 혹은 숫자를 입력할 수 있습니다.

범위는 넣는 것에 관해서 엑셀 도움말에서는 아래와 같이 설명합니다.

=SUM(A2:A6)은 =A2+A3+A4+A5+A6보다 입력 오류가 발생할 가능성이 낮습니다.

함수는 장점이 하나 더 있는데 범위 내에 문자 있는 경우 자동으로 배제 한다는 것입니다.

그런데 텍스트를 넣으면 오류가 나서 실수로 입력한 데이터를 발견하는게 좋을 수 도 있습니다.

상황에 따라서 선택이 될 수 있습니다.

SUM 함수는 SUM(A1:A10,D1:D10,20,21,"5",True) 이런식으로 입력하면 모든 숫자를 다 더합니다.

"5"는 숫자 5로 서식이 자동변환되며 TRUE = 1로 쳐서 더합니다.

쉼표를 이용한 다양한 범위는 20개까지 입력할 수 있습니다.

2. SUMIF 함수

단순히 덧셈을 수행하는 것이 아닌 조건을 가지고 합연산을 수행합니다.

규칙을 입력하는 방법을 알면 쉽게 사용할 수 있습니다.

SUMIF(range, criteria, [sum_range]) : 지정한 조건을 충족하는 범위값을 합산합니다.

• range : 검사조건을 수용할 범위입니다. crieria의 조건을 검사합니다.
• criteria : 검사 조건입니다. 특정 값과 같거나(=), 크고 작은(>, <) 논리 연산자를 수행합니다.
• [sum_range] : 합계 연산을 실행할 범위입니다.(합을 구할 데이터들), 비워두면 range와 같은것으로 계산합니다.

왼쪽의 표에서 과일의 종류별로 합산하는 표가 오른쪽에 있습니다.

range인 이름 B4:B29 범위에서 Criteria의 종류가 같은 것을 고릅니다.

그럼 엑셀이 sum_range에서 같은 위치를 찾아서 더합니다.

따라서 range와 sum_range는 동일한 행 혹은 열 구성을 가져야 오류가 없이 계산이 됩니다.

숫자범위를 가지고 합을 연산하는 방법도 있습니다.

2월 3일 이전의 합계를 계산하도록 하는데 조건을 쌍따움표를 사용해서 입력해야 합니다.

"<="&조건 : & 연사자를 이용해서 논리연산자와 조건을 연결해야 작동합니다.

range의 날짜범위 B4:B29에서 G4날짜보다 작은 날짜만 더 합니다.

이 때도 조건에 맞는 행의 sum_range의 같은 행을 찾아서 더합니다.

SUM함수 같은 경우 많은 분들이 이미 많이 사용하고 있습니다.

그래도 정확한 사용법은 한번 알아보면 좋습니다.

연산식은 응용을 하면 사용법이 다양해 지고 효율적으로 문서를 작성할 수 있습니다.

문서가 도움이 되었으면 좋겠습니다.

정리해둔 데이터에서 필요한 값을 찾는 VLOOKUP과 HLOOKUP 함수가 있습니다.

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

VLOOKUP과 HLOOKUP으로 충분한 경우가 많지만 실제로는 좀 더 복잡한 상황이 많습니다.

아직까지 엑셀에서는 조회함수로 사용가능한 함수 중 MATCH와 INDEX를 조합이 가장 유용합니다.

1. 기본적인 함수의 설명에 대해 알아보겠습니다.

MATCH(lookup_value, lookup_array, [match_type]) : 범위(lookup_array)에서 lookup_value의 위치를 반환합니다.

• lookup_value : lookup_array에서 찾으려는 값입니다.
• lookup_array : 필수 요소입니다. 검색할 셀 범위입니다
• [match_type] : 선택 사항입니다. 숫자 -1, 0 또는 1입니다.
                         -1 : 작거나 같은 값 중에서 최대값을 찾습니다.
                         0 : 정확한 값을 찾습니다.
                         +1 : 크거나 같은 값 중에서 최소값을 찾습니다.

INDEX(array, row_num, [column_num]) : 범위(array)내에서 원하는 위치에 있는 값을 반환합니다.

• array : 배열 상수나 셀 범위입니다.
• row_num : 값을 반환할 배열의 행을 선택합니다.
• [column_num] : 선택 요소입니다. 값을 반환할 배열의 열을 선택합니다.

두 함수 다 단독으로도 많이 사용됩니다.

index는 배열이나 행렬을 반환하는 모든 상황에서 사용하는 유용한 함수입니다.

match는 어째꺼나 목록에서 값을 찾는데 유용합니다.

2. Match와 index의 조합

기본적인 사용법은 아래와 같습니다.

index의 범위 array에는 출력할 값을 match의 범위는 찾는 값을 입력합니다.

주의할 점을 각 범위가 같은 행이나 같은 열에서 시작해야 합니다.

match는 B행에서 "서울특별시"의 위치가 몇번째인지 찾고 index는 "2015.01"의 범위내에서 값을 출력합니다.

여기에서 가로 줄도 조건에 넣을 수 있습니다.

index에서 행과 열을 모두 찾는 기능을 사용하면 큰 범위에서도 사용합니다.

match를 두개 넣어서 복잡한 함수가 됩니다.

3. 두 가지 이상 조건 입력하기

조건 하나만으로 충분히 강력한 함수지만 두가지 이상의 검색식을 가지는 배열함수라는 사용법이 있습니다.

정확하게는 match함수를 배열함수로 변경하는 것입니다. 사용법은 아래와 같습니다.

최근 버전의 엑셀에서는 배열함수를 자동으로 인식해서 평범하게 사용할 수 있습니다.

하지만 예전의 엑셀버전에서는 함수를 입력할때는 ctrl + shift + enter를 입력해야 합니다.

index의 검색와 match의 기능을 둘다 활용하면 복잡한 상황에서도 응용이 여러가지로 가능함니다.

하지만 다중함수를 너무 남용하면 오류가 나기 쉽습니다.

1. 검색할 값이 리스트에 없는 경우, 오타이거나 "."이나 의도하지 않는 공백문자가 들어가있을 수 있습니다.
2. 범위가 맞지 않는 경우, index와 match의 범위가 어긋나기 쉽습니다.
3. 배열함수가 정확하게 입력되지 않았을 때  ctrl + shift + enter로 입력해야 합니다.

오류가 발생하면 위의 사항을 확인해 보기바랍니다.

혹시 장기적으로 써야하는 문서에서 작성했다면 시물레이션으로 몇번 확인을 해보고 사용하는게 좋습니다.

표 형식으로 데이터를 정리해 두기는 하지만 특정하나의 DATA를 찾을 경우도 있습니다.

이때 사용할 수 있는 Vlookup함수과 Hlookup 함수에 대해서 소개하려고 합니다.

기능에 대해서 설명을 해보려면 좀 어렵게 적혀 있습니다.

VLOOKUP : 이 함수는 테이블의 첫 번째 열에 지정된 조회 값의 값을 기반으로 테이블의 지정된 열에서 값을 검색하는 데 사용됩니다.
HLOOKUP : 이 함수는 테이블의 첫 번째 행에 지정된 조회 값의 값을 기반으로 테이블의 지정된 행에서 값을 검색하는 데 사용됩니다.

VLOOKUP과 HLOOKUP은 행과 열만 바꼈을 뿐 같은 함수입니다.

기본적으로 VLOOKUP으로 구조를 먼저 설명하겠습니다.

VLOOKUP(lookup_value, table_arry, col_index_num, [range_lookup])

• lookup_value : 찾는 값입니다. 문자와 숫자도 모두 가능합니다.
• table_arry : 조회값이 있는 범위입니다. 찾는 값(lookup_value)이 맨 왼쪽에 있어야 동작 합니다.
• col_index_num : 출력할 값의 열 번호입니다.
• [range_lookup] : 찾는 값이 정확하게 일치하기를 원하면 False를 유사할 경우는 True를 입력합니다.
                           기본값은 True이나 문자를 기반으로 검색할 경우에는 False를 꼭 입력해야 합니다.

기능에 대해서 알아보면 아래과 같습니다.

표에서 가장 왼쪽에서 값을 찾은 다음에 오른쪽에 몇개 떨어진 값을 찾습니다.

위의 지역명과 숫자를 변경하면 서울의 2012년이나 대구의 2007년도 쉽게 찾을 수 있겠죠.

HLOOKUP(lookup_value, table_arry, col_index_num, [range_lookup])

HLOOKUP은 VLOOKUP에서 열이 행으로 변경되고 나머지는 동일 합니다.

• lookup_value : 찾는 값입니다. 문자와 숫자도 모두 가능합니다.
• table_arry : 조회값이 있는 범위입니다. 찾는 값(lookup_value)이 맨 위쪽에 있어야 동작 합니다.
• col_index_num : 출력할 값의 행 번호입니다.
• [range_lookup] : 찾는 값이 정확하게 일치하기를 원하면 False를 유사할 경우는 True를 입력합니다.
                           기본값은 True이나 문자를 기반으로 검색할 경우에는 False를 꼭 입력해야 합니다.

주의할 점이 있는데 리스트에 행이 병합되어 있으면 병행된 범위의 왼쪽 상단만 값이 있습니다.

그리고 범위 내에 똑같은 값이 여러개 있는 경우 가장 위나 왼쪽에 값만 한번 찾습니다.

그래서 사번이나 코드등 고유번호를 사용해야 하는 점이 있습니다.

조건이 좀 까다로워 보여도 익숙해지면 사용하기 편리합니다.

엑셀의 기본함수 중에서 많이 쓰이는 함수 축에 듭니다.

최근 버전의 엑셀에는 XLOOKUP이라고 VLOOKUP과 HLOOKUP을 합쳐둔 함수도 제공합니다.

엑셀 VBA에서 파일명을 변경하면 좋겠다고 생각을 해봤습니다.

그런데 폴더안에 폴더(서브폴더)에 접근하는데 고생을 좀 했습니다.

항상 코드를 짜고나서 다시 보면 쉽더라고요. 다음에도 사용하기 위해서 소스를 남겨 두겠습니다.

1. 소스 코드와 설명

'파일명 빼오기
Sub GetFileName()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim subfolder As Object
    Dim path As String
            
    path = ThisWorkbook.path '이 파일이 있는 경로를 추출합니다.
    fileNm = ThisWorkbook.Name '이 엑셀 파일의 이름입니다.
    
    Workbooks(fileNm).Worksheets("sheet1").Cells.Clear
    'Sheet1의 모든 내용을 전부 지웁니다.
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    '파일을 읽거나 쓸 수 있는 파일 시스템 오브젝트를 구성합니다.
    Set folder = fso.GetFolder(path)
	'폴더들의 경로를 저장합니다.
    
    For Each file In folder.Files 'Folder 배열내에 모든 파일이 끝날 때까지
        Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = path & "\" & file.Name
        Cells(Rows.Count, 8).End(xlUp).Offset(1, 0) = path & "\" & file.Name
        '한칸씩 내려가면서 파일명을 작성 똑같은리스트를 2번 만듭니다.
    Next file

    For Each subfolder In folder.subfolders
    'Subfoler들의 리스트를 엽니다.
        GetFile subfolder
    'GetFile 함수를 호출합니다.
    Next subfolder
End Sub



'------------------------------------------------------------------------------
'서브폴더의 파일명을 불러오는 소스
Sub GetFile(flder As Object)
    Dim file As Object
    Dim subfolder As Object
    
    path = flder
    '서브 폴더의 주소를 기록합니다.

    For Each file In flder.Files
        Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = path & "\" & file.Name
        Cells(Rows.Count, 8).End(xlUp).Offset(1, 0) = path & "\" & file.Name
        '똑같이 파일들을 불러옵니다.
    Next file

    For Each subfolder In flder.subfolders
        GetFile subfolder
        '하위폴더 안에 하위폴더가 있으면 다시 시작합니다. 여기가 핵심이네요.
    Next subfolder
End Sub

'------------------------------------------------------------------------------

'파일명 바꾸기
Sub Rename()
    Dim lastRow As Long
    Dim oldName As String
    Dim newName As String
    Dim path As String

    path = ThisWorkbook.path
    fileNm = ThisWorkbook.Name
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To lastRow - 1
    
        oldName = Cells(i + 1, 1).Value
        'A열에서 예전이름을 가져옴
        newName = Cells(i + 1, 8).Value
        'H열에 새로운 이름을 가져옵니다.
        If Right(oldName, Len(fileNm)) <> fileNm Then
        '파일명이 엑셀 파일명과 같으면 동작하지 않습니다.
            Name oldName As newName
           '파일병 변경하기
        End If
    Next i
End Sub

2. 코드의 동작 

 - 파일은 같은 폴더에 있도록 위치합니다.

 - 메크로 파일의 초기 모습니다. 파일이름을 불러오면 똑같은 리스트가 A와 H 열에 작성이 됩니다.

3. 파일이름 변경하기

수동으로 파일이름을 바꾸고 파일이름 변경하는 소스를 실행합니다.

파일이름이 바뀌는걸 확인했습니다.

4. 추가 팁

사용하는 열고 있는 파일명과 이름이 똑같으면 실행하지 않도록 합니다.

        If Right(oldName, Len(fileNm)) <> fileNm Then
            Name oldName As newName
        End If

엑셀은 실행중에 같은 폴더에 ~$"파일이름".xlsm이라는 임시파일을 형성합니다.

이건 접근이 안되서 그냥 동작시키면 오류가 납니다.

그걸 방지하기 위함이라고 보면 됩니다.