엑셀 장부를 만들면 과정이 있습니다. 어느게 정답인지는 모르겠지만, 그래도 주문부터 집계까지 만들면서 필요한 양식과 수식을 알아보겠습니다.
목록
1. 장부를 만들 때 생각해야 하는 것
2. 첫 과정 일일 장부
3. 두 번째 집계표 양식
4. 세 번째 품목별 집계표와 수식
5. 엑셀의 단점 중 한가지
1. 장부을 만들때 생각해야 하는 것
하나의 장부에 처음부터 집계까지 넣을 수도 있지만, 이러면 복잡합니다. 특히 결재를 올려야 하는 직장에서는 한눈에 알기 쉽게 만드는 것이 중요합니다. 전문가처럼 갖은 수식을 사용하면서 양식이 복잡해지면 다른 사람은 알기 어렵습니다. 어려운 함수를 사용하고 이리저리 꼬아버리면 당최 무슨 말을 하는 건지 알 수 없습니다.
서류를 보여주면서 일일이 설명하면서 내 상사는 이정도도 모른다고 할지 모르지만, 이런 서류는 빵점입니다. 지금도 질문에 올라오는 엑셀 양식을 보면 가끔 이런저런 복잡한 양식이 보이기도 하는데요, 엑셀 수식이 한계가 있습니다. 하나의 페이지로 어렵고 복잡해 지면 페이지를 나누어서 만들면 알아보기 쉽습니다.
앞으로도 양식을 만드는 포스팅이 많을 겁니다. 오늘은 매입 내역을 기록하는 일일 매입 자료와 집계 양식을 소개합니다. 이것은 어디까지나 지금 조건으로 만든 것으로 다루는 내용이나 상황에 따라 다르겠죠. 요즈음은 바코드만 찍으면 종목, 품목별로 집계되기에 굳이 엑셀로 따로 작성할 필요도 없지만, 지금 가게 입출고는 바코드로 이루어지지 않습니다. 입고 역시 바코드 없이 입고되는 품목도 많고 판매도 일일이 바코드 찍기보다는 암산으로 계산도 많이 합니다. 그래서 매출은 총금액 외에는 품목별이나 특정 상품 판매 내용은 전혀 알 수 없고, 단지 매입은 자료가 남으니 나름대로 시간을 투자하며 매입 자료를 만들고 있습니다. 혹시 살펴보시고 응용해서 사용할 수 있는지 또는 품목별 합계를 구하는 수식, 또는 품목별 합계를 구하기 위한 사전 작업 등을 참고하면 되겠습니다.
2. 첫 과정 일일장부
일일 장부입니다. 이미지를 클릭하면 자세하게 보이나요. 아래는 부분마다 별도로 나누어서 보여드리겠습니다. 어떤 방법으로 입고가 이루어지더라도 엑셀 양식으로 작성합니다. 주문서 앞에 붙는 영문자는 매입처별 이니셜입니다. K라는 업체에서 2월 5일 주문하고 입고된 내용이죠.
입고 거래처 품목마다 일일이 입력합니다. 상품명에도 매입처 이니셜이 붙는데, 이것은 나중에 집계로 넘겼을 때 필요합니다. 다른 매입처에서도 같은 상품이 입고될 수도 있기에 구분하기 위해 상품 이름 앞에도 매입처를 표시합니다. 나중에 집계표를 보면 알 수 있는데요, 같은 상품이지만, 매입처마다 단가가 다를 수 있기에 구분해야 합니다. 사용자는 상품명, 단가, 수량까지 입력합니다. 여기서 단가는 판매 금액입니다.
입고를 체크하는 칸을 하나 만들었습니다. 이 칸은 주문하고 물건을 받았을 때 거래명세서를 보고 일일 주문서를 작성한다면 전혀 필요 없는 곳입니다. 이 칸이 필요한 곳은 사전에 주문 금액을 입금하지 않고 후불하는 거래처라면 필요합니다. 정기적으로 한 달에 한 번 또는 두 번 결재하는 거래처죠.
후불 거래처에 주문서를 작성해서 팩스를 보내주고 물건을 받을 때는 빠지는 품목이 있습니다. 그때 빠지는 품목을 표시하기 위해 넣었는데요, 다른 방법이라면 주문하고 입고되지 않은 품목은 일일 주문서에서 삭제해도 되는데요, 여기서는 주문 근거를 남겨두기 위해 입고되지 않은 품목을 살려두고 입고 여부를 별도로 표시하고 있습니다.
여기에 들어가는 0에는 조건부 서식을 넣었습니다. 입고가 확인되면 빈칸에 ㅇ을 입력하면 색깔이 들어가게 했죠. 색깔을 넣으면 눈에 띄여 빨리 찾을 수 있습니다. 반대로 비어 있으면 색깔을 넣어도 되겠죠. 조건부 서식은 이미지대로 적용하면 됩니다.
판매 금액은 수량 *단가이며, 이 양식에서 입고 단가와 참고 항목, 구분은 단가 자료에서 불러옵니다. 일정 자료가 모이면 집계로 넘깁니다. 자동으로 할 수도 있겠지만, 하나씩 수동으로 넘기고 있는데요, 집계표를 보겠습니다.
3. 두 번째 집계표 양식
집계표로 넘길 때는 하나씩 복사해서 집계표로 붙여 넣습니다. 붙여 넣을 때는 선택하여 붙여넣기를 클릭해서 값을 선택하고 아래 확인을 누릅니다. 그냥 붙여 넣기 하면 엉뚱한 셀 값이 들어갑니다.
일일 매입자료를 집계표로 넘긴 장면입니다.
앞서 일일 장부에서 거래처별 이니셜을 상품 이름에 붙인 이유를 알 수 있겠죠. 상품별로만 정렬하면 어떤 상품이 어떤 거래처에서 들어온 것인지 알 수 없습니다. 항상 같은 가격으로 들어오지 않으니까요. 구매처와 구분은 단가표에서 들어옵니다. 집계표에서는 붙여넣기만 하면 따로 할 것은 없습니다.
매입 집계자료에서 종목별 합계는 구분에 있는 종목을 의미하는데, 이 구분은 예를 들면 해산물, 육류 등으로 나눌 수 있고, 입고되는 양에 따라 육류에도 소고기, 돼지고기를 분류할 수도 있을 겁니다. 또 아마 그 이상이라면 자동 시스템을 도입하겠지만, 복잡하다면 구분하는 행을 하나 더 만들어 세분화할 수도 있죠. 이렇게 하면 복잡한 내용까지 손쉽게 파악할 수 있지만, 입력할 때는 구분 인자가 추가되는만큼의 시간도 고려해야 합니다.
집계표 뒷부분입니다. 일일 입고 자료에서 상품명부터 입고 확인까지 붙여 넣습니다. 여기서 단가에서 불러오는 것은 입고 단가, 비교가 되겠네요. 그리고 입고율에도 조건부서식이 들어있습니다. 적정 입고율을 정하고 그 이상 단가라면 표시가 되게끔 했는데요, 지금은 판매 금액에서 매입 금액이 60% 를 넘어서면 색상이 들어갑니다.
4. 세 번째 품목별 집계표와 수식
이제 품목별로 집계하는 방법입니다. 이렇게 분류가 되었다면 쉽죠. 앞서 알려드린 구분 행을 기준으로 수식을 넣으면 되니까요. 만약 이런 구분자를 넣지 않았다면 다른 방법으로 해결해야 합니다. 아래 첨부된 이전 포스팅, 특정 상품만 합계 구하는 수식 및 방법을 참고하세요.
구분자를 나열하고 그 품목별 구분 이니셜을 기준으로 합계를 뽑습니다. 지금 표시된 곳은 판매 금액입니다. 수식은 현재 장부 기준으로 =SUMIFS(Sheet1!$L$3:$L$2167,Sheet1!$C$3:$C$2167, E3)
E3(A1)을 C행에서 같은 값을 찾아서 L 열에 있는 숫자를 더하라는 명령입니다. 그 옆에 있는 매입 금액 집계 수식 역시 같은 의미죠. 다만 매입 금액은 O 행을 계산해야 합니다.
=SUMIFS(Sheet1!$O$3:$O$557, Sheet1!$C$3:$C$557, E3)
그리고 그 옆은 같은 종목을 다시 합산한 겁니다. 소고기 앞다리, 뒷다리로 분류했다면 같은 소고기끼리 합계입니다. 이건 그냥 셀 위치만 더해주면 되겠죠. 위에서 보여드린 장부는 첨부하니 천천히 살펴보세요.
5. 엑셀의 단점 중 한가지
그리고 주의할 점은 VLOOPUP 함수는 불러오는 자료가 열려있어야 합니다. 그렇지 않으면 제대로 값이 들어오지 않는데요, 일일 주문서, 집계표에 값이 안 나오면 단가 파일을 열면 됩니다. 그래도 값이 자동으로 불러오지 못하면 수식이 들어있는 셀을 클릭하고 더블 클릭하면 아래로 적용됩니다.
이 이야기는 단가나 또는 인사에서 인사정보 등의 파일을 별도로 만들면 편합니다. 변동이 있으면 해당 파일만 건드리면 되니까요, 하지만 엑셀의 단점은 하나의 문서에 계산이 많아지면 특히 VLOOPUP 함수 등은 제대로 불러오지 못합니다. 위의 집계표가 분기별만 모으면 제대로 계산이 되지 않습니다. 단가를 불러오지 못하는 거죠. 집계표를 열면 오른쪽 단가 계산 쪽에는 빈자리가 생깁니다.
수식은 제대로 들어있는데요, 모든 곳이 계산이 안 되는 것도 아닙니다. 어떤 곳은 계산이 되고, 그러니까 값이 나오고 또 어떤 곳은 계산이 되지 않아 쥐가 파먹는다고 하죠, 딱 그 꼴입니다. 어쩔 수 없습니다. 엑셀의 한계니까요. 이런 것을 해결하기 위해서는 하나의 엑셀 문서에 단가 시트를 넣어야 합니다. 위험하죠. 파일마다 정보가 들어가니까요. 인사 같으면 개인정보가 각 파일에 들어가야 하니까 별로 내키지 않습니다. 또 다른 방법은 있습니다. 매크로를 이용하는 건데요, 매크로는 혼자 사용하는 파일이라면 괜찮지만, 많은 사람이 사용하는 파일이라면 권하지 않습니다. 참고하세요.
0 댓글