엑셀을 통해서 일 평균 구하기 및 활용 방법

엑셀을 통하여 일 평균 구하기 및 활용 방법

엑셀로 실무에서 매출을 정리할 때 제조업체 별로 혹은 제품별로 정렬해서 합계를 요약하면 보기에 정말 좋습니다. 하나하나 작업자가 표를 만들고 계산하고 정리하게 되면 많은 노력과 시간이 들어가게 되죠 하지만 엑셀에서 작성해 놓은 표만 있다면 알아서 정렬해 주고 판매 수익 요약까지 해 주는 기능이 있으니 그것이 바로 스프레드시트 부분합 기능입니다. 부분합 기능을 위한 정렬, 삭제까지 바로 소개해 드리도록 드리겠습니다. 먼저 다음의 예시를 위한 표를 준비해 보았습니다.

제조사가 있고, 제조사가 공개한 제품들이 있습니다. 판매 수량과 가격, 합계 금액까지 모두 나와 있는 매출표입니다. 제조업체 별로 보기 좋게 작성했으면 좋겠지만 그렇지 못한 경우가 해마다 있을 수 있습니다.

OFFSET함수 응용

다음은 height와 width에 관하여 알아보겠습니다. OFFSETreference, rows, cols, height, width 부분은 offset함수를 실시하는 데 필요한 변수는 아니므로 입력을 필요하면 하고 아니면 안해도 된다라는 말입니다. height 반환되는 참조의 높이단위 행 수입니다. height는 양수여야 합니다. width 반환되는 참조의 너비단위 열 수입니다.

width는 양수여야 합니다. 배열로 진행되다. 보니 OFFSET만 사용하실 때는 Ctrl + Shift + Enter 로 눌러주셔야 합니다.

2 특정 문자열이 포함된 경우 색상 넣기

예를 들어, 물건 명단이 있는 스프레드시트 시트에서 프로모션이라는 단어가 포함된 물건 명에는 파란색으로 색깔을 넣고 싶습니다. 1 요구하는 문자열을 조건으로 지정합니다. ISNUMBERSEARCH프로모션, A2 2 조건에 맞는 서식을 만들고 셀에 함수 색상 넣기를 적용합니다. ISNUMBERSEARCH프로모션, A2 서식 푸른색 이렇게 하면 프로모션이 포함된 물건 명에만 해당 셀의 색깔이 변경됩니다.

OFFSET함수 다른 함수와 결합하여 스프레드시트 자동화

저희는 사실 세로보다는 월별을 가로로 두는 경우가 많을 것 같습니다. 그렇다면 width 부분만 살펴보도록 하겠습니다. 현재 offset함수를 sum으로 감싸주었습니다. 보통 한 줄에 대해서만 쓸 예정이므로 OFFSETB4, 0, 0, 1, 변경될 숫자 로 작성해주시면 됩니다. =OFFSET($B$4, 0, 0, 1, 변경될 숫자)

이곳에서 앞에 row 와 col이 0인 경우 생략가능하며, height와 width도 1인 경우는 생략가능합니다.

따라서, OFFSETB4, , , , 변경될 숫자 이런 식으로 작성도 가능합니다. 예시의 경우 N3셀에 숫자를 변경하면 자동으로 값이 바뀌도록 해두었습니다.

일반적인 매출현황 집계표

보통 월별로 데이터를 정렬한 뒤 끝에 몇월 합계처럼 보여주는 형태를 사용하고 계시리라 생각됩니다. 월이 변경될 때마다. SUM의 범위를 해마다 귀찮게 바꿔주어야 하는게 은근히 귀찮습니다. 이럴 때 3월 합계 부분의 숫자만 5로 바꿔주시면 자동으로 5월 합계5월 누적합계가 계산될 수 있도록 수식을 배워보겠습니다. 수식 결과만 궁금하신 분들께선 맨 아래로 내려주시면 확인 가능합니다.

OFFSET함수 기본

먼저 OFFSET함수에 관하여 알아보겠습니다. OFFSETreference, rows, cols, height, width 이라고 입력하면 괄호안에 영어가 나옵니다. reference의 경우 참조할 셀을 물어보는 겁니다. rows 행을 어떠한 방식으로 움직일지 양수이면 아래로 음수이면 위로 cols 열을 어떠한 방식으로 움직일지 양수이면 오른쪽으로 음수이면 왼쪽으로 그림으로 한번 보겠습니다. 체스나 장기의 말을 움직인다고 생각하시면 편할 것 같습니다.

현재 스프레드시트 E8셀을 기준으로 OFFSET 함수를 작성하였습니다. 오른쪽으로 한칸 걸어가는 명령어는 OFFSETE8, 0, 1입니다.

부분합 삭제

부분합 삭제 역시 너무 간단합니다. 다시 부분합으로 들어가면 됩니다. 상단 메뉴에서 데이터 개요 부분합을 선택해 들어가 줍니다. 물론 부분합 설정이 된 표를 선택해 준 상태에서 말이죠. 부분합을 눌러서 나온 창을 보시면 밑에 모두 제거가 있습니다. 모두 제거를 선택해 주시면 삭제가 됩니다. 이번 시간에는 스프레드시트 부분합 정렬 요약 및 삭제 팁까지 소개해 드리도록 보았습니다. 이외에도 여러가지 팁들이 궁금하신 분들은 아래를 참조해 주시면 감사하겠습니다.

자주 묻는 질문