태그: 클라우드오피스, 구글드라이브, 스프레드시트, 가계부 만들기, 설문지 만들기, 함수, SUM, FILTER, YEAR, MONTH, IFERROR, 클라우드 문서 양식
지난 포스팅에 이어서 이번에는 "통계"시트 화면을 만들어 보겠습니다.
먼저 가계부에서 사용될 스프레드시트명부터 정하겠습니다.
가계부 내역은 시트명을 "Data"로 변경하고, 통계화면은 "통계"라고 시트명을 추가하세요.
시트명 정리가 끝났다면 이제 본격적으로 "통계"시트를 만들어 보겠습니다.
먼저 위에 동영상을 보시고 따라서 만들어 보세요.
동영상을 보시고 모두 만드셨나요?
그럼 사용된 함수에 대해서 하나씩 설명드리겠습니다.
먼저 SUM함수는 합계를 반환하는 함수로서 "Data"시트에 입력된 수입과 지출금액의 합계를 구하고자 사용했고 계정별, 월별로 구해야 하기에 FILTER함수를 같이 적용했습니다.
FILTER함수는 조건을 넣어서 데이터를 필터링할 때 사용합니다.
그래서 FILTER함수에 계정과 월을 조건절로 넣어서 조건과 일치하는 값만 구할 수 있는 것입니다.
FILTER함수 사용방법: FILTER(범위, 조건1, [조건2, ...])
1) 범위지정: 필터링할 데이터는 수입금액의 합계를 구하기 위해서 "Data"시트에서 E열(수입금액)을 지정합니다.
2) 조건1(Data!$G:$G=B$2): 해당 계정의 합계를 구하기 위해 B2셀(계정명)과 "Data"시트에서 G열(계정명)을 조건 1로 넣습니다.
3) 조건2(YEAR(Data!$B:$B)=$A$2): 해당 년도의 합계를 구하기 위해 A2셀(연도)과 "Data"시트에서 B열(날짜)을 조건 2로 넣습니다.
4) 조건3(MONTH(Data!$B:$B)=$A4): 해당 월의 합계를 구하기 위해 A4셀(월)과 "Data"시트에서 B열(날짜)을 조건 3로 넣습니다.
5) 위에 조건1~3까지 모두 일치한 "Data"시트에서 E열(수입금액)의 값만 필터링하게 됩니다.
(Tip: [Data!G:G=B2]를 [Data!$G:$G=B$2] 이렇게 함수식에 공통적으로 "$"기호를 넣은 이유는 셀을 복사해서 붙여넣기 할 때 수식을 변하지 않고 고정시키기 위함이며, 만일 "$"을 넣지 않으면 붙여넣기 후 모두 수정해줘야 함)
IFERROR함수 사용방법: IFERROR(값, [오류인_경우_값])
IFERROR함수를 사용하는 이유는 FILTER함수의 경우 필터링 한 값이 없으면 에러가 발생합니다.
이런경우를 대비해서 IFERROR함수를 사용해주면 에러표시를 방지할 수 있습니다.
IFERROR함수는 여러 함수를 복합적으로 사용할 경우 사용해주면 아주 유용한 함수입니다.
SUM함수 사용방법: SUM(값1, [값2, ...])
1) 합계를 구할 값 또는 셀을 지정합니다. (여기서는 필터링 한 값을 넣었습니다.)
이렇게 B4셀에 함수를 입력했으면 나머지 C4~M15셀에 복사해서 붙여넣기 하게되면 범위와 월에 대한 셀의 알파벳과 숫자만 바뀌고 "$"기호를 넣은 곳에는 바뀌지 않을 것입니다.
붙여넣기 한 후 양식과 비교해 보시기바랍니다.
이렇게 함수로 수입금액을 자동으로 집계될 수 있게 넣었기 때문에 "Data"시트에 가계부 데이터가 입력될 때 마다 모든 합계가 통계화면에서 자동으로 합산해서 보여집니다.
이번에는 계정별과 월별 총 합계를 구하는 함수를 넣어보겠습니다.
B16셀에 B4:B15까지 합산하는 SUM함수를 사용합니다.
나머지도 위에 그림과 같이 모두 SUM함수를 사용해서 함수식을 입력하면 됩니다.
지금까지 SUM, FILTER, YEAR, MONTH, IFERROR 함수를 사용해서 가계부 통계화면을 만들었습니다.
아래는 가계부 만들기에서 사용된 함수입니다. 아래 함수는 잘 메모하셨다가 다른 스프레드시트 문서를 만들때 활용하시기 바랍니다.
- SUM: 합계를 구하는 함수
- FILTER: 지정된 조건을 충족하는 열 또는 행만 반환하여 원본 범위의 필터링 버전을 반환
- YEAR: 주어진 날짜에 지정된 연도를 반환
- MONTH: 특정 날짜에 해당하는 연도의 월을 숫자 형식으로 반환
- IFERROR: 인수의 값이 오류여부에 따라 지정한 값을 반환 (오류체크 용도)
이번 포스팅 도움이 되셨나요?
도움이 되셨다면 사용하시는 SNS에 공유해주는 센스^^ (블로그 상단에 SNS버튼 이용)
[참조]
가계부 입력폼: 링크
가계부 양식: 링크
먼저 가계부에서 사용될 스프레드시트명부터 정하겠습니다.
가계부 내역은 시트명을 "Data"로 변경하고, 통계화면은 "통계"라고 시트명을 추가하세요.
시트명 정리가 끝났다면 이제 본격적으로 "통계"시트를 만들어 보겠습니다.
먼저 위에 동영상을 보시고 따라서 만들어 보세요.
그럼 사용된 함수에 대해서 하나씩 설명드리겠습니다.
먼저 SUM함수는 합계를 반환하는 함수로서 "Data"시트에 입력된 수입과 지출금액의 합계를 구하고자 사용했고 계정별, 월별로 구해야 하기에 FILTER함수를 같이 적용했습니다.
FILTER함수는 조건을 넣어서 데이터를 필터링할 때 사용합니다.
그래서 FILTER함수에 계정과 월을 조건절로 넣어서 조건과 일치하는 값만 구할 수 있는 것입니다.
FILTER함수 사용방법: FILTER(범위, 조건1, [조건2, ...])
1) 범위지정: 필터링할 데이터는 수입금액의 합계를 구하기 위해서 "Data"시트에서 E열(수입금액)을 지정합니다.
2) 조건1(Data!$G:$G=B$2): 해당 계정의 합계를 구하기 위해 B2셀(계정명)과 "Data"시트에서 G열(계정명)을 조건 1로 넣습니다.
3) 조건2(YEAR(Data!$B:$B)=$A$2): 해당 년도의 합계를 구하기 위해 A2셀(연도)과 "Data"시트에서 B열(날짜)을 조건 2로 넣습니다.
4) 조건3(MONTH(Data!$B:$B)=$A4): 해당 월의 합계를 구하기 위해 A4셀(월)과 "Data"시트에서 B열(날짜)을 조건 3로 넣습니다.
5) 위에 조건1~3까지 모두 일치한 "Data"시트에서 E열(수입금액)의 값만 필터링하게 됩니다.
(Tip: [Data!G:G=B2]를 [Data!$G:$G=B$2] 이렇게 함수식에 공통적으로 "$"기호를 넣은 이유는 셀을 복사해서 붙여넣기 할 때 수식을 변하지 않고 고정시키기 위함이며, 만일 "$"을 넣지 않으면 붙여넣기 후 모두 수정해줘야 함)
IFERROR함수 사용방법: IFERROR(값, [오류인_경우_값])
IFERROR함수를 사용하는 이유는 FILTER함수의 경우 필터링 한 값이 없으면 에러가 발생합니다.
이런경우를 대비해서 IFERROR함수를 사용해주면 에러표시를 방지할 수 있습니다.
IFERROR함수는 여러 함수를 복합적으로 사용할 경우 사용해주면 아주 유용한 함수입니다.
SUM함수 사용방법: SUM(값1, [값2, ...])
1) 합계를 구할 값 또는 셀을 지정합니다. (여기서는 필터링 한 값을 넣었습니다.)
이렇게 B4셀에 함수를 입력했으면 나머지 C4~M15셀에 복사해서 붙여넣기 하게되면 범위와 월에 대한 셀의 알파벳과 숫자만 바뀌고 "$"기호를 넣은 곳에는 바뀌지 않을 것입니다.
붙여넣기 한 후 양식과 비교해 보시기바랍니다.
이렇게 함수로 수입금액을 자동으로 집계될 수 있게 넣었기 때문에 "Data"시트에 가계부 데이터가 입력될 때 마다 모든 합계가 통계화면에서 자동으로 합산해서 보여집니다.
B16셀에 B4:B15까지 합산하는 SUM함수를 사용합니다.
나머지도 위에 그림과 같이 모두 SUM함수를 사용해서 함수식을 입력하면 됩니다.
지금까지 SUM, FILTER, YEAR, MONTH, IFERROR 함수를 사용해서 가계부 통계화면을 만들었습니다.
아래는 가계부 만들기에서 사용된 함수입니다. 아래 함수는 잘 메모하셨다가 다른 스프레드시트 문서를 만들때 활용하시기 바랍니다.
- SUM: 합계를 구하는 함수
- FILTER: 지정된 조건을 충족하는 열 또는 행만 반환하여 원본 범위의 필터링 버전을 반환
- YEAR: 주어진 날짜에 지정된 연도를 반환
- MONTH: 특정 날짜에 해당하는 연도의 월을 숫자 형식으로 반환
- IFERROR: 인수의 값이 오류여부에 따라 지정한 값을 반환 (오류체크 용도)
이번 포스팅 도움이 되셨나요?
도움이 되셨다면 사용하시는 SNS에 공유해주는 센스^^ (블로그 상단에 SNS버튼 이용)
[참조]
가계부 입력폼: 링크
가계부 양식: 링크
댓글
댓글 쓰기