[구글 스프레드 시트 활용] 출퇴근 시간 기록 문서 만들기-1(ARRAYFORMULA, INDIRECT, DATEVALUE, EOMONTH 함수 활용하여 날짜 채우기)

 

오늘 구글 스프레드 시트 활용은 출퇴근 시간 기록하는 시트를 만드는 방법입니다.

프리랜서이거나 재택근무 시 업무시간을 기록하기 좋은 문서입니다.

 

전체 통계 시트, 월별 시트로 구성해보겠습니다.

 

우선 월별 시트를 만들어줍니다.

총 6가지 항목을 입력해줍니다.

 

날짜, 출근여부, 출근시간, 퇴근시간, 연차, 비고

 

 

그리고 적절히 마음에 드는 서식으로 꾸며줍니다.

 

 

 

 

 

그럼 이제 날짜를 입력해봅니다.

2020-01-01이라고 입력할 수도 있지만

이 경우 12월까지 셀을 복사할 경우 괴로워집니다.

1달치만 필요한 경우에는 드래그해서 만들면 됩니다.

 

 

그런데 2월, 3월도 필요하다면?

1~12월까지 모든 날짜가 필요하다면?

 

행 방향으로 긁으면 되지!!

 

응 아니야 안돼

 

 

좌절할 필요 없습니다.

구글느님은 늘 답을 주시니까요.

여러 가지 함수를 활용하면 최소한의 노력으로 날짜 채우기를 성공할 수 있습니다.

아래의 함수를 날짜의 첫 번째 셀에 입력합니다.

 

=ARRAYFORMULA(TEXT(ROW(INDIRECT(""&DATEVALUE("2020-01-01")&":"&DATEVALUE("2020-01-31"))),"yyyy년 mm월 dd일 dddd"))

이 함수를 보는 여러분과 나의 맘

 

함수는 헤치지 않으니 침착하게 뜯어봅니다.

 

ARRAYFORMULA 함수

=ARRAYFORMULA(배열 수식)

으로 사용되는 함수입니다.

간단히 말해서, 괄호 안의 수식의 값의 결과가 다양할 경우 여러 행/열에 자동으로 결과를 반환해주는 함수입니다.

 

예를 들어서,

위와 같은 표가 있을 경우, 

=ARRAYFORMULA(A1:A10+B1:B10)

식을 사용한다면 아래와 같이 표시됩니다.

 

수식을 적용하려는 두 개의 범위 및 크기가 동일해야 적용되는 함수라는 것만 명심하면

활용도가 무궁무진한 함수입니다.

 

 

TEXT 함수

=TEXT(숫자, "서식패턴")

으로 사용하는 함수입니다.

입력한 숫자를 지정한 서식으로 바꿔주는 함수입니다.

이때, 서식에 대한 패턴을 아는 것이 중요합니다.

여러 가지가 있지만 오늘 활용할 '날짜'에 대해서만 알아봅니다.

 

연도: yyyy

월: mm

일: dd

요일(약어,ex.화): ddd

요일: dddd

 

그렇다면

=TEXT(202001,"yyyy-mm")

함수를 입력하면

2020-01

이라는 값이 나올까요?

 

아닙니다.

앞의 인수도 날짜 형식으로 표시된 숫자여야만 함수가 적용됩니다.

2020-01

이라는 값이 나오기 위한 TEXT 함수는

=TEXT(DATE(2020,1,1),"yyyy-mm")

또는

=TEXT(DATEVALUE(2020.01.01),"yyyy-mm")

입니다.

 

그래서 날짜를 채워주는 위의 ARRAYFORMULA로 시작하는 함수식에서,

TEXT 함수의 괄호 하위에서 쉼표 이전의 값은 모두 첫 번째 인수를 위한 식이고,

쉼표 이후 큰따옴표 안에 들어있는 

"yyyy년 mm월 dd일 dddd"

값이 두 번째 인수인 서식 패턴입니다.

 

INDIRECT 함수

=INDIRECT(참조할 셀 또는 텍스트,A1)

으로 사용하는 함수입니다.

(A1은 생략 가능합니다. 셀 표기 방식이 A1인지, R1C1인지를 확인하는 인수인데,

다음에 기회가 되면 설명하도록 하겠습니다.)

 

참조할 셀에 입력되어있는 텍스트를 이름으로 인식한 후,

입력되어 있는 이름값에 해당하는 위치에서 데이터를 가지고 오는 함수입니다.

무슨 소리인지 이해가 가지 않습니다.

 

A1 셀에 "안녕"이라는 내용이 있고,

B1 셀에는 A1 이라고 셀 주소를 표기해놓았습니다.

이때 다른 셀에 

=INDIRECT(B1)

라는 함수를 입력하면 아래와 같은 결과가 나옵니다.

 

A1셀의 값을 호출합니다.

B1셀에 입력된 A1이라는 텍스트를 A1셀을 지칭하는 이름으로 인식한 후,

A1셀이 가지고 있는 데이터를 반환한 것입니다.

셀 주소를 반환하는 다른 함수와 함께 쓰기 좋습니다.

 

오늘 사용할 식에서는 어떻게 활용되냐면,

&연산을 활용하여 열의 첫 번째 값과 마지막 값의 내용을 지정한 후, ROW 함수로 행 번호를 반환하여

TEXT 함수를 사용할 범위(ex. A1:A30)를 만들어줍니다.

결과적으로 ARRAYFORMULA 함수가 해당 범위 내에 계산식에 해당하는 모든 값을 각각의 열에 반환하게 됩니다.

 

INDIRECT(""&DATEVALUE("2020-01-01")&":"&DATEVALUE("2020-01-31"))

 

"" 안에는 결과값이 입력될 열의 알파벳을 입력하면 되는데,

굳이 입력하지 않아도 됩니다.

알파벳을 입력하면 아래와 같은 형태가 됩니다.

 

INDIRECT("B"&DATEVALUE("2020-01-01")&":B"&DATEVALUE("2020-01-31"))

 

그럼 이제 &연산으로 결합된 DATAVALUE 함수를 알아보겠습니다.

 

DATAVALUE 함수

=DATAVALUE(날짜문자열)

인수값을 날짜 서식으로 표시하는 함수입니다.

DATAVALUE 함수를 사용할 때 주의할 점은, 날짜 문자열을 입력할 때

구분자를 사용하여 날짜 값을 나타내야 한다는 점입니다.

예를 들어 2020년 01월 01일이라면

"2020/01/01"

"2020-01-01"

"2020.01.01"

등 날짜 표기에 사용되는 형식으로 입력해야 합니다.

 

DATE 함수와 다른 점은,

DATE 함수는 콤마로 연결된 세 가지 인수를 날짜 서식으로 반환하는 식이고,

DATEVALUE 함수는 날짜 형식으로 입력된 텍스트를 날짜 서식으로 반환해주는 식이라는 것입니다.

 

그러니까 위의 함수 식은

B:B 범위 내에서, 2020-01-01부터 2020-01-31까지의 숫자를 반환하라는 소리가 됩니다.

 

 

그럼 실제 시트에 최종 함수를 적용해봅니다.

 

 

 

 

 

이제 12월까지 셀을 늘릴 경우,

다시 2020-02-01을 입력한 후 28일까지 드래그할 필요 없이

날짜의 첫 번째 셀의 함수식만 변경해주면 알아서 채워지게 됩니다.

 

=ARRAYFORMULA(TEXT(ROW(INDIRECT(""&DATEVALUE("2020-02-01")&":"&DATEVALUE("2020-02-28"))),"yyyy년 mm월 dd일 dddd"))

 

이것저것 많이 시도해봤는데 위의 함수식을 활용하는 게 가장 간단합니다.

각 월의 마지막 날을 알아야 하는게 단점이라고 할 수 있습니다.

 

하는 김에 그 문제도 한번 고쳐보겠습니다.

 

 

기준이 될 월초값을 적어줍니다.

 

그리고 수식을 이렇게 바꿔보겠습니다.

 

=ARRAYFORMULA(TEXT(ROW(INDIRECT(""&DATEVALUE(B2)&":"&EOMONTH(B2,0))),"yyyy년 mm월 dd일 dddd"))

 

EOMONTH 함수

=EOMONTH(날짜,개월수)

입력한 날짜의 마지막 날을 반환하는 함수입니다.

개월 수 인수 영역에 0을 입력하면 해당 월의 마지막 날,

1을 입력하면 다음 달의 마지막 날,

-1을 입력하면 이전 월의 마지막 날을 뱉어주는 함수입니다.

 

=EOMONTH(2020-01-01,0)

의 식이

2020-01-31

의 값을 저절로 뱉어주게 됩니다.

 

그럼 이전의 함수를 EOMONTH를 적용한 함수로 변경해보도록 하겠습니다.

 

 

갓벽 그자체

 

 

여기까지 보시느라 수고하셨습니다.

 

월간 시트 작성은 다음 포스팅에서 이어집니다.