통판정리시 유용한 엑셀수식 및 조건부서식

엑셀 따로 배운 적 없고 그냥 필요할때 그때그때 찾아서 쓰고 있어서
더 효율적인 방법이 있을지도 모르지만
혹시 필요하신 분 계실까 하여 써봅니다.
엑셀을 대충은 알고 계셔야 합니다!
(엑셀이 뭔지 안다/자동합계를 사용할 수 있다/자동 채우기를 사용할 수 있다/수식의 셀 번호를 내 파일에서 해당하는 셀 번호로 바꿔야 한다는 것 정도는 안다 등등..)

1. 구글스프레드로 데이터 받기
제가 통판을 구글폼으로 받는 이유는 구글스프레드로 바로 연동되기 때문입니다.
네이버 셀도 사용방법은 비슷할 것으로 생각됩니다.
다만 저는 구글스프레드 플러그인으로 자동 메일 발송을 사용할 때가 있기 때문에 요걸로...

아무튼 폼으로 통판신청을 받을때,
책 종류에 따른 구입권수를 '숫자로만' 표시하도록 하여 입력받았습니다.
(숫자로만 표시하도록 하는 옵션은 폼 작성시 적용할 수 있습니다)

또한 입금액을 작성하는 란을 두었습니다. 실제 입금해야 하는 금액과 맞는지 확인하기 위한 용도입니다.
(물론 정말 계좌로 들어온 금액과 일치하는지 여부는 직접 확인해야 합니다)

그러면 대충 이 비슷하게 들어왔을 것입니다.


2. 실제 입금받아야 하는 금액 계산하기

2-1. 배송비 산출

입금받아야 하는 금액은 책가격x권수+배송비이므로,
배송비가 모두 같다면 관계없지만 권수에 따른 차등이 있다면
일단 배송비 열을 추가하고 IF함수를 사용해서 배송비를 산출해보도록 해요.

1~3권: 3000원
4~6권: 3500원
7권이상: 4000원

IF함수는 
IF(조건식,"참","거짓")
형식으로 되어 있는데,
설정한 조건에 들어맞을 경우 앞의 값, 아닐 경우 뒤의 값을 반환합니다.

파일의 수식을 보시면 다중IF함수를 사용해서 책권수가 6을 넘으면(7부터는) 4000원, 
아닐경우 다시 IF함수를 사용해서 책권수가 3을 넘으면(4부터는) 3500원, 아닐 경우 3000원이 되도록
작성한 것을 보실 수 있습니다.
조건이 2개라면 그냥 =IF(B2+C2+D2>3,"3500","3000") 부분만 응용해서 쓰시면 되겠습니다.

첫번째 행에 식을 작성하고(저의 경우는 2번째 행)
셀 오른쪽 밑의 작은 네모를 붙잡고 밑으로 죽 당기면 수식이 자동으로 채워집니다.


2-2. 금액 합산


=(B2*3500+C2*4000+D2*2500+E2)
(캡쳐에는 3번째 행이지만 2행에 해당하는 식으로 썼습니다)

평범한 계산 수식입니다.
책A가 3500원, 책B가 4000원, 책C가 2500원이기 때문에
책 종류별로 권수*가격+E열의 배송비를 더했습니다.
이렇게 전체 입금받아야 할 금액이 나온 셈입니다.

그런데 길동5님과 길동6님의 입금해야 할 금액과 실제 입금액이 차이가 납니다!


3. 조건부 서식으로 입금오류 잡아내기
  
메뉴의 서식-조건부 서식으로 들어가면 오른쪽과 같은 창이 뜹니다.

범위에 적용: 
서식을 적용할 범위를 선택합니다.
저는 입금해야 할 금액을 표시한 H열에 서식이 적용되도록 했습니다.

다음의 경우 셀에 서식 지정:
드롭다운 메뉴에서 '맞춤 수식'을 선택합니다.
그리고 그 아래에 수식을 넣습니다.
=H2-I2>0
(이렇게 범위의 가장 윗 행에 해당하는 셀 번호로 수식을 만들면 자동으로 범위 내 다른 셀에도 수식이 적용됩니다)

서식 지정 스타일:
원하는 대로 지정하시면 됩니다. 저는 셀이 빨갛게 표시되도록 했습니다.

이렇게 하면
입금해야 할 금액에서 입금액을 뺀 금액이 0초과일 경우, 즉 입금이 덜 되었을 경우
셀이 빨갛게 표시됩니다.

반대로 입금이 더 많이 되었을 경우는,

다른 규칙 추가를 누르고
=H2-I2<0
같은 방식으로 부등호 방향만 다르게 해서 녹색 서식을 지정해 보았습니다.

길동 5,6,9님께는 추가 입금 요청을 드리고
길동 13,14,16님께는 차액을 거슬러 드려야 한다는 것을 한눈에 알 수 있습니다.


+++구글폼으로 받는 응답은 스프레드시트에 실시간 반영이 되는데,
연동되어있는 스프레드시트에 열을 추가해서 저렇게 수식 등을 넣어도 차후 응답도 정상적으로 들어옵니다.
다만 이미 기록된 값을 바꾸어버리면 바뀐 채로 남아있기에 중요한 값들을 건드리지 않도록 조심해주시고,
행은... 무서워서 안 건드려 봤습니다<

============================

통판이란게 은근 자잘한 사고도 많고 신경쓸게 많은 일이라...
조금이라도 과정을 자동화하는데 도움이 되면 좋겠네요ㅠㅁㅠ
금액 틀린 걸 눈으로 잡아내는 것도 상당히 힘들어서
저 스스로는 조건부 서식이 매우.. 큰.. 도움이.. 되었기에... 어설픈 팁이라도 공유하고 싶었습니다ㅠㅠ

질문은.. 받지 않습니다 죄송합니다 저도 엑셀 잘 모릅니다<




더블세터 아크릴챰 수량조사 그린그림



아래 글을 읽어 주시고,
구입하고 싶다! 하시는 분은 덧글을 달아주세요..!

*디자인과 색상은 조금 달라질 수 있습니다.

*예약이 아닌, 제작갯수를 결정하기 위한 수량조사입니다.

*만일 제작 최소수량을 채우지 못하면.. 제작이 중지됩...됩...흐엉 

*가격은 총단가가 나와 봐야 알겠지만 6~7천원으로 예상합니다. 리본과 눈꽃챰 등 부자재 가격에 의해 단가가 너무 높아지면 판매시 부자재 있음/없음 옵션을 둘 생각입니다.

*판매는 12월 19일 하이큐 세터 온리전(세터뎐)+통판으로 이루어질 예정입니다.

*꼭 구입하고 싶으신 분만 덧글을 달아주세요!

감사합니다!!!



검은방 승범태현 책 '자작나무 숲에서 당신을 잃어버렸다' 수량조사 받습니다.(~8/27) 그린그림

8월 27일 수요일 자정까지 수량조사 받습니다.

댓글로 권수를 달아주시면 됩니다.
(ex)1권이요!)
꼭 구매 의향이 있으신 분만 참여 부탁드려요!

따로 예약은 받지 않습니다. 
행사 종료 후 통판 예정 있습니다.

==========================================

참가행사: 동네 페스타 검은도시2(8월 30일 양재 AT센터)
부스위치: 현5
부스명: A Seeker

------------------------------------------

책정보: 승범태현, 전연령가, 시리어스, 검은방4 이후 시점  

A5 DP, 무선제본 예정이며
펑크가 안 날 시(...) 페이지는 66p 가량입니다(본편 62p 이상)
가격은 견적 봐서 결정하겠지만 6~7천원 될 것 같습니다.



아래에 샘플이 있습니다.

샘플보기(검은방3 이후 네타 주의)

피마새 엘시x정우 책 '공중산책' 통판 받습니다. 그린그림


샘플은 이 포스팅에서! ▶http://ecli.egloos.com/4892369
통판 신청은 여기서! http://me2.do/F3bMob5e

통판 신청 기간은 8월 17일까지이며, 자세한 내용은 링크를 확인해 주세요!
잘 부탁드립니다!



피마새 엘시정우책 수량조사 그린그림


8월 10일 한국 판타지소설 온리전 '판소리'에 나올 예정인
피를 마시는 새 엘시x정우 팬북 '공중산책'의 수량조사를 받습니다.
지금 상태로는 남은 원고 톤은 커녕 먹칠이나 제대로 해서 가져가면 다행인 진행 속도이지만
펑크...안 내고 싶습니다.....흫헣허허 

부스위치: 거13 
부스명: A Seeker
사양: A5 DP본 20p 전연령가 
가격: 아마도 3천원

그럼 구매를 원하시는 분은 간단히 덧글 부탁드려요.(ex)한권이요~)
감사합니다.
수량조사 기간은.. 제가 당일 출력을 할 예정이니(...) 10일 새벽까지 계속 받습니다.

*샘플은 이어지는 페이지가 아닙니다.

샘플 보기

그림정리(2014년 4월~6월 12일) 그린그림

2014년 4월부터 지금까지 트위터에 올린 그림 모음. 길어서 숨김.
가로방향 그림들은 아마 누르면 크게 뜰 것 같습니다<

이어지는 내용

그림정리(2014 1~3월) 그린그림

2014년 1월부터 3월까지의 그림입니다.
트위터에 있는 걸 긁어와서 화질이 좀 안 좋을지도...

이어지는 내용

선가드 팬북 '불새의 희망!' 수량조사 받습니다.(종료) 그린그림

구입을 원하시는 분은 이 포스팅에 덧글을 남겨주세요.
ex) 1권이요~

***수량조사 언저리만 뽑아갈 예정이니 구입하실 분들은 꼭! 수량조사에 참여해주시면 감사하겠습니다.





시백혜연으로 혜연이 생일 축하(회색도시 네타有) 그린그림

시백혜연으로 혜연이 생일을 축하합니다.
스크롤압박 주의

이어지는 내용

크리스 앤더슨군의 존경하는 교수님(웹공개) 그린그림

4년전의 매우짧은 창작원고(4p) 공개합니다.
그냥 갑자기... 공개하고 싶어서...



이어지는 내용

1 2 3 4 5 6 7 8 9 10 다음



방명록

용알

Adopt one today!Adopt one today!Adopt one today!Adopt one today!