엑셀 쇼핑몰 판매량 유닛별 평균 - 시간대별 자동 판매평균 구하기

오늘은 어떤 분의 질문을 받았습니다. 인터넷 쇼핑몰 같은 것을 운영하시는 분 같은데, 제품이 판매되는 양을 유닛별로 엑셀표에 한달간 시간대별로 정리해놓았습니다. 이것을 데이터로 한달간 각 시간대에 같은 요일에 판매되는 제품의 평균을 자동으로 구하는 함수식에 대해 문의하셨네요. 사실 이런 질문들은 직접 데이터가 있는 그리고 작성하고 싶은 평균의 데이터 시트까지 같이 엑셀로 주시면 편한데, 사진으로 보내주셔서 편집해서 작업하고 직접 만드는데 조금 시간이 걸리네요. (다른 분들도 혹시 엑셀에 대한 문의를 다른 분께 하신다면 엑셀파일을 같이 보내주시는게 훨씬 해결하기가 쉽습니다. ) 하지만 이 부분을 엑셀 함수로 잘 짜는 방법만 알게 된다면 매달 평균을 수작업으로 만드는 시간을 한번에 줄일 수 있으니 아래 자세하게 설명한 내용과 중간중간 있는 꿀팁들을 보시고 도움이 되셨으면 좋겠습니다. 

 

 

평균 판매량을 구하기 위해서는 일단 기본인 평균을 구하는 방법을 알아야 하는데 다들 아시죠? 요일별로 평균은,

---

평균 = 이번달 해당 요일의 판매량의 합계 / 해당 요일의 이번달 개수

---

이렇게 구하면 간단하지만 이것을 자동화 해서 각 요일별로 해당 시간의 평균을 구하기 위해서는

먼저 각 요일별 그 시간대의 판매량의 합계를 구하고

두번째로 해당 요일이 이번달에 몇 번있는지를 자동으로 구해내면 이번 질문은 쉽게 해결할 수 있습니다. 하나씩 방법을 알려드릴테니 그대로 작업 따라해보시면 본인이 사용하는 엑셀에 쉽게 응용하실 수 있을꺼예요. (마지막에는 완성된 엑셀파일도 올려드릴께요. )

 

1. 해당 요일별 판매량의 합계

 

 

오늘 요일별, 시간대별 평균을 구할 데이터 입니다. 각 시간대별로 요일을 매칭한다해서 브이룩 함수나 index match 같은 것을 사용하기에는 요일 값이 계속 중복되기 때문에 사용하기 힘들어 보입니다. 이럴때 판매량의 합계라는 부분에 힌트를 얻어서 sum 관련 함수를 사용하면 가능할 것같은 느낌이 살며시 드네요. sum 관련 함수는 여러가지가 있는데, 

 

엑셀 sum 함수들

 

뭐가 이렇게 많나요? 이것들 다 아실필요는 없고 엑셀에서 자주 쓰이는 것은 주로 sum, sumif, sumproduct 정도인데 가장 기본이 되는 그냥 합계 sum 외에 오늘처럼 조건식으로 합계를 구할 때 꼭 알고 계시면 편리할 함수는 바로 위에 보여진 sumif 함수입니다. 

 

주어진 조건에 맞는 합계 구하기

 

오늘 데이터에서 먼저 필요한 것은 요일별로 "쇼핑몰에서 판매된 제품의 수량의 합계" 입니다. 이것을 sumif 함수를 이용해서 예를 들면 "이번달의 요일중에서 월요일에 해당하는 판매량 값을 찾아서 그 값들을 모두 더햇!" 이라는 함수를 sumif를 이용해서 만들어 주시면 됩니다. 

 

 

여기에 사용할 인수 즉 함수의 값은 3개를 넣어주면 되는데 첫번째는 어떤 것이 있는 내용을 합계를 할 것인가? 즉 어떤 우리가 필요한 어떤 요일의 범위를 지정해주면 되고, 두번째는 그러면 이 영역에서 너가 원하는 값. 즉 요일의 값을 넣어주면됩니다. 요일값을 직접 넣어줘도 되지만 자동으로 데이터를 구하기 위해서는 월요일의 값이 있는 셀을 선택해줍니다. 마지막으로 세번째는 해당하는 요일에서 합계를 하고 싶은 값의 범위를 지정해주면 됩니다. 이 방법을 가지고 직접 월요일의 합계를 내는 작업 간단히 동영상으로 보여드릴께요. 

 

월요일만 합계내기

이렇게 하니 월요일만 합계를 만드는 수식을 넣었으니 이제는 각 요일별로도 합계를 작업할 수 있는 기초가 되었는데, 뒤에 자동적으로 각 요일별로 합계를 만들기 위해서는 간단한 팁이 하나더 필요한데 그건 전체 자동화를 할때 다시 말씀드리겠습니다. 일단 각 요일별 합계를 구했으니 이제는 이 월요일이 이번달에 몇 번인지만 구해서 나누어 주면 되겠죠?

 

2. 해당 요일의 갯수 자동으로 구하기

 

위에서 월요일의 합계를 구했으니 이번달에 월요일은 5번 들어갔네? 그래서 그냥 5로 나누어 주면 문제는 해결되지만 그 다음 화요일은 이번달에 4번 들어갔는데 그때마다 나누어 주는 숫자를 일일이 바꾸면 이건 자동화 프로그램에 대한 예의가 아니죠. ^^; 그래서 엑셀에서 어떤한 것의 개수를 자동으로 구하는 함수는 count 함수를 이용해서 자동화해볼께요. 이 count 함수에도 종류가 참 많아요. 

 

 

count, counta, countif 같은 함수들은 자주 사용되는 함수이기에 어떤 기능을 하는지 알아두면 편리한데 오늘은 이 중에서 조건이 있는 항목의 개수를 구할 때 사용하는 함수 countif 를 사용해서 월요일이 몇번 들어갔는지 만들어보겠습니다. 조금전에 보여드린 합계의 내용에 이제는 countif 함수를 같이 아래와 같이 넣어볼께요. 

 

countif 함수 자동화 적용

 

이렇게 적용을 하니 나누어주는 요일의 갯수를 자동으로 계산해줘서 이제는 10:00~11:00 사이의 이번달 월요일 평균 판매량을 자동으로 구했어요. 그러면 이것을 일주일로 자동으로 적용해주면 되겠죠? 복사해서 옆으로 전체 붙여넣기를 해볼께요. 

 

 

컥! 이렇게 하니 데이터를 지정한 범위도 한칸씩 앞으로 밀려서 다른 데이터 값이 들어가네요. 이럴 때 지정한 데이터 값을 고정시키는 엑셀의 가장 중요한 팁. 함수를 복사하여도 데이터 지정 범위가 자동으로 변하지 않도록 고정시키는 엑셀의 특수문자를 사용해서 간단히 작업해보겠습니다. 

 

엑셀 지정한 범위를 고정시키는 특수문자 달러표시 "$"

 

엑셀에서는 "$" 달러표시를 셀의 위치값 앞에 두면 이 셀의 위치는 절대 고정으로 인지하고 복사해서 붙여넣어도 이 범위는 절대 움직이지 않습니다. 

이제 우리가 작업하는 표로 돌아와서 이 표에서는 요일의 범위와 10:00~11:00 의 시간대의 판매량이 픽스된 데이터이기에 이 범위에 대해서 달러표시를 삽입하여주면 나머지 요일에도 똑같은 데이터로 판매량의 평균값을 구할 수 있습니다. 위 작업한 데이터에 "$" 표시를 넣어줘서 제대로 요일별로 데이터를 뽑아볼께요. 

 

이렇게 작업하니 이제는 요일별로 판매량의 평균을 자동으로 나타내게 되었습니다. 전부에 달러표시를 넣어주는 것이 아니라 sumif 중간의 인수인 요일은 순서대로 변해야 하는 값이기에 달러표시를 하지 않아주는 것은 잊지마세요. 이제는 마지막이네요. 끝으로 각 시간대 별로 확장해서 판매량의 자동평균을 복사해서 붙여넣기해서 만들어주면 되겠네요. 

 

 

그런데 이런 아래로 전부를 복사하니 에러메세지가 뜨네요. 뭔가가 잘못된 기분이 드는군요. 

 

자동 수식 적용 팁~

 

요일별로는 자동화가 되었는데, 시간대별로 복사하니 오류가 나오네요. 오류의 내용을 보니

 

 

0으로 나누기 오류라고 하네요. 그렇다면 countif 함수의 값이 0이 나왔다는 의미인데, 이것들도 셀을 더블클릭해서 어떻게 수식이 적용되었는지 확인해보니,

 

 

한 행식 데이터 적용구역이 자동으로 내려갔네요. 이럴때는 약간 수동으로 월요일 값의 함수를 일차적으로 작업해주고 전체 각 요일로 자동화 수식을 넣어주는 것이 좋습니다. 이제 오늘의 마무리가 되는 것 같네요. 이번달의 각 요일별, 각시간별 판매량 자동평수식을 정확히 넣는 꿀팁을 발라놓은 동영상을 이제 만들어볼께요. (함수 인수 한 곳만 손대주면 간단히 자동화가 마무리 됩니다. )

 

이번달 시간별, 요일별 판매량 평균구하기

이렇게 하면 한번에 간단히 함수를 이용해서 이번달의 시간대별로 해당요일의 쇼핑몰 물건 판매량을 쉽게 자동으로 구할 수 있습니다. 제가 단계적으로 동영상을 작업해서 보여드렸는데, 이렇게 단계별로 하나씩 직접 작업해보시고 나서 아래에 제가 첨부해드릴 이번동영상에 적용한 엑셀서식을 보시면 아주 쉽게 이해가 되고 응용까지도 잘 하실 수 있게 되기에 이런식으로 오늘 포스팅 작업을 진행했네요. 보시는 분들에게 도움이 되었으면 참 좋겠습니다. 감사합니다. 

요일별 자동평균구하기.xlsx

0.01MB

 

 

아패 포스팅들도 함께 보시면 도움이 되는 꿀팁들이 곳곳에 있으니 참조하시면 좋겠습니다. 고맙습니다. 

2022.01.18 - [PC 활용] - 엑셀 대쉬 삭제 - 전화번호, 주민번호, 카드번호, 사업자 번호 하이픈 기호 지우기

엑셀 대쉬 삭제 - 전화번호, 주민번호, 카드번호, 사업자 번호 하이픈 기호 지우기

2022.01.18 - [PC 활용] - 엑셀 전화번호 하이픈 자동입력 방법 2가지로 쉽게 작업하세요.

엑셀 전화번호 하이픈 자동입력 방법 2가지로 쉽게 작업하세요.

2022.01.07 - [PC 활용] - 엑셀 5 또는 0.5 단위 반올림, 올림, 내림 함수 서식을 쉽게 사용하는 방법

엑셀 5 또는 0.5 단위 반올림, 올림, 내림 함수 서식을 쉽게 사용하는 방법

2022.01.10 - [PC 활용] - 노가다를 줄이는 엑셀 빈칸 자동 채우기 방법 2가지

노가다를 줄이는 엑셀 빈칸 자동 채우기 방법 2가지