쇼핑몰을 하다보니 주문 내역, 판매 내역, 재고 관리 등에 엑셀을 자주 사용하게 된다.

가르치기만 할 때는 '나는 엑셀을 써먹을 일은 없겠구나'라고 생각했었는데 정작 내가 일을 시작하고 보니

엑셀 없이는 아무것도 못할 정도로 엑셀의 활용도는 어마무시했다 ㅋ

 

규모가 커지면 매달 10만원 내외의 비용을 들여 상품 관리, 주문 관리, 발송 관리, 고객 관리 등을 할 수 있는

솔루션을 사용하게 되겠지만, 아직까지는 하루 주문이 그닥 많지 않아 직접 만든 엑셀 파일로 잘 사용하고 있는 중~

나는 아직 주문이 적은 편이라 매크로는 아직 사용하지 않고, 엑셀 함수로만 모두 해결하고 있다.

 

이 글에서 언급되는 함수 중에 모르는 함수가 있다면 미리 함 보자~!

 

엑셀 함수 4 (찾기/참조 함수) by Y 

엑셀 함수 15 - ROW 함수 (엑셀 일련번호 만들기) by Y 

엑셀 함수 4 (찾기/참조 함수) by Y 

엑셀 함수 7 (논리 함수, IF 함수) by Y

 

 

 

일단 구매자, 상품, 전화번호, 주소 등 주문 내역을 아래와 같이 별도의 시트에 입력한다.

물론 주문 내역 시트의 매입가와 판매가는 단가표 시트에서 자동으로 가져와 입력되도록 되어 있다.

즉, 직접 기입하는 부분을 최소화하여 내가 할 일을 줄이는게 키포인트~!!

 

'주문내역' 시트

 

 

그럼 자동으로 아래 판매 내역 시트에 구매자, 상품 등의 필요 정보가 자동으로 입력되어 수수료, 쿠폰, 마일리지, 배송비 등의 공제액이 계산되어 정산 금액과 매출 수익까지 자동으로 계산되는 방식이다.

 

 

'판매내역' 시트

 

 

거기에 덧붙여 재고관리, 매입관리, 수익관리 시트도 자동으로 데이터가 변동되도록 만들었는데 어짜피 자동으로 채워지는 방식은 판매 내역과 같다.

이제부터 엑셀로 주문 관리 등을 할 수 있는 방법을 설명해보겠음~

 

 

 

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

 

 

 

※ 데이터를 표로 지정하기

 

주문은 시간이 갈수록 늘어날수밖에 없다.

이때 데이터를 '표 서식'을 지정하여 표로 만들고 표 이름을 지정하면 데이터가 아무리 늘어나도 수식의 참조 부분을 수정할 필요가 없다.

1. 표로 지정할 데이터를 범위 지정한 후 [홈]-[표 서식]에서 마음에 드는 표 서식을 선택한다.

2. [표 도구]-[디자인] 탭에서 '표 이름'을 지정해준다. 예) 주문내역

 

 

 

※ 단가표에서 매입가, 판매가 등의 데이터를 자동으로 가져오기

 

=IFERROR(VLOOKUP(주문내역[[#이 행],[상품명]],단가표,5,0)*주문내역[[#이 행],[수량]],"")

 

VLOOKUP(주문내역[[#이 행],[상품명]],단가표,5,0) 식의 의미는?

'단가표' 범위의 첫 번째 열에서 '주문내역' 표의 상품명을 찾아 다섯 번째 열의 값을 찾아준다.

나는 따로 '상품단가' 시트를 만들어 상품명부터 판매가까지의 범위를 '단가표'라는 이름으로 지정해두었다.

즉, 상품명에 해당하는 판매가를 찾아주는 식이다. 

 

VLOOKUP(주문내역[[#이 행],[상품명]],단가표,5,0)*주문내역[[#이 행],[수량]] 식의 의미는?

1개당 판매단가에 주문수량을 곱해 주는 식이다.

범위가 [[#이 행],[상품명]], [[#이 행],[수량]] 등으로 표시되는 이유는 표로 지정되어 있기 때문이다.

 

 

 

'단가' 시트

 

 

=IFERROR(VLOOKUP(주문내역[[#이 행],[상품명]],단가표,5,0)*주문내역[[#이 행],[수량]],"") 식의 의미는?

iferror 함수는 쓰는 이유는 판매가가 자동으로 입력되도록 하기 위해서다.

'=iferror(수식, "")' 식을 사용하면 수식이 정상적으로 계산되면 수식값이 표시되지만, 수식이 정상적으로 계산되지 않고 에러가 나는 경우 ""(공백)으로 채워주게 되어 시트가 한결 깔끔해진다.

 

 

iferror 함수를 사용하지 않을 경우(좌)/사용했을 경우(우)

 

 

 

 

※ 주문내역에서 데이터 자동으로 가져오기

 

=IF(INDEX(주문내역,ROW(B1),COLUMN(B1))<>"",INDEX(주문내역,ROW(B1),COLUMN(B1)),"")

이번에는 if 함수와 index 함수를 활용하여 작업을 자동화시킨 경우다.

 

INDEX(주문내역,ROW(B1),COLUMN(B1)) 식의 의미는?

이 식은 판매내역에서 입력한 식이므로 'ROW(B1)' 값은 1, 'COLUMN(B1)' 값도 1이 된다.

index(주문내역, 1, 1) 식은 주문내역의 1행, 1열에 있는 값을 반환하는 식이다.

 

=IF(INDEX(주문내역,ROW(B1),COLUMN(B1))<>"",INDEX(주문내역,ROW(B1),COLUMN(B1)),"") 식의 의미는?

주문내역의 1행에는 주문 날짜가 입력되어 있으므로 주문날짜가 공란("")이 아니라면(즉, 주문이 있다면)

2열의 값인 구매자의 이름(INDEX(주문내역,ROW(B1),COLUMN(B1)))을 가져오는 식이다.

 

'INDEX(주문내역,ROW(B1),COLUMN(B1))' 식만 사용해도 될걸 복잡하게 if 함수를 사용한 이유는

주문이 있을 경우에만 자동으로 데이터가 채워지도록 하여 깔끔하게 표시하기 위해서다.

(위의 두 수식 모두 미리 표 끝까지 복사하여 채워놓아도 주문이 없다면 아무것도 표시가 안되어 매우 깔끔한 작업을 할 수 있다)

 

 

 

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

 

 

 

이 강좌를 엑셀 강좌에 쓰지 않고 쇼핑몰 강좌에 쓰는 이유는 파일을 첨부할 수 없기 때문이다.

아무래도 장사 노하우가 스며있다보니 엑셀 강좌처럼 마구 공개할 수는 없었음^^;

하지만, 엑셀 함수에 대한 지식은 어느 정도 갖추고 있지만 활용할 수 있는 노하우가 필요한 분들에게는 이 글이 매우 유용할 거라 믿고 있음~^^

 

 

 

 
 
 
 

 

Posted by 두여자 Y&S

댓글을 달아 주세요

  1. vyfbrl

    주문내역 판매내역 재고관리에 데해서 좀더 자세히가르켜줄수잇나요? 제가차트를만들어야해서요ㅠㅠ

    2014.11.21 21:23 [ ADDR : EDIT/ DEL : REPLY ]
    • 글쎄요^^; 요새 너무 바빠서 블로그 활동도 잘 못하고 있어서요.
      차트에 관련된 강좌는 엑셀 강좌 보시면 될거 같네요.

      2014.11.22 03:52 신고 [ ADDR : EDIT/ DEL ]
  2. 주문번호는 머에요 저도차트만들어야 하는데 ㅜㅠ

    2015.06.05 19:24 [ ADDR : EDIT/ DEL : REPLY ]
    • 주문에 일련번호를 단겁니다.
      첫번째 주문이면 1, 두번째 주문이면 2...이런 식으로요.

      2015.06.16 09:40 신고 [ ADDR : EDIT/ DEL ]
  3. 비밀댓글입니다

    2015.10.02 18:28 [ ADDR : EDIT/ DEL : REPLY ]