괴산의 총 32개 학교에서 메일로 각기 다른 형식의 엑셀파일을 보내오고, 이를 취합하여 수발주를 한다. 농산물은 대개 이전 달의 말 경에 다음 달 전체의 발주를 보낸다. 하지만, 고기류는 정해진 날짜없이 계속해서 발주를 낸다.

대표 메일(gsfood12@daum.net)로 오며, '학교급식 발주' 메일함으로 자동으로 들어온다. 이 메일들을 gmail 계정(countryfarmer0504@gmail.com)으로 가져온다.
서로다른 양식, 서로다른 날짜에 발주 데이터가 첨부파일 형식으로 온다. 이를 취합하여 별도의 엑셀파일에 입력해야 한다.

필요한 파일들이 있다.

양식 엑셀 파일을 피벗테이블을 사용하여 년도/분기/월 별로 통계를 내야 발주량을 예측할 수 있다.

'행' 목록에 '발주' 항목을 추가하면, '연/분기' 라는 항목이 추가로 생긴다. 그리고 피벗테이블 상에 행 레이블을 선택 후, 오른쪽 버튼을 누르면, 나오는 '그룹' 이라는 메뉴를 선택한다. '월' 을 추가한다.

안녕하세요. 괴산군통합먹거리지원센터입니다.
발주하신 품목은 일정에 차질없이 납품하겠습니다
발주주셔서 감사합니다

쌀의 경우, 한 달에 2번 발주한다. 1 파레트를 도정하면, 60~70 포(10kg 기준)가 나온다. 발주량은 보통 '실제 수요량 + 60~70포(1파레트)' 를 주문한다.

학교에서 고기 발주가 들어오면, 위의 발주양식에 추가하여 담당자에게 메일을 보낸다.

제목 : 친환경 돈육 추가신청(동인초)
본문 : 동인초 돈육 추가신청 내역입니다.
첨부파일 : 한축식품통보양식_summary.xlsx

한살림 담당자(박경준 과장)으로부터 메일이 오면, 첨부파일을 출력한다. PDF 파일 형식인데, 알PDF 프로그램을 사용해서, OCR 형태로 변환한다. 그리고 아래 순서대로 정렬한다.

1. 거래명세서(액수 있는 것)
2. 거래명세서(액수 없는 것)
3. 등급판정확인서 : 거래명세서의 LOT 번호를 이력번호와 대조하여 확인한다
4. 도축검사 증명서 : 거래명세서의 LOT 번호를 이력번호와 대조하여 확인한다
5. 인증서 : 거래명세서의 LOT 번호를 https://mtrace.go.kr 에 접속하여 입력하여, 생산자의 이름을 보고 확인한다

위 5장을 합쳐 'A서류' 라고 하겠다.

매월의 말일 즈음에 행정실과 영양사에게 아래와 같이 메일을 보낸다. 첨부해야할 파일은 총 6가지이다.

1. 납세증명서
2. 소독필증
3. 지방세납세증명서
4. 4대보험 완납증명서
5. 소독증명서_한축식품
6. 보건증

위의 파일은 모두 스캔하여 PDF 파일 형태로 첨부한다. 메일 본문 내용은 아래와 같다.

제목 : 괴산군먹거리통합센터 보건증, 소독필증 등 관련서류 송부드립니다

괴산군통합먹거리지원센터입니다.
2022년4월 정산관련 서류를 첨부하여 송부하오니 업무참조 바랍니다.

참고로 영양사에게는 소독필증(한축식품 포함)과 보건증만 발송한다.

매월 말이 되면, 학교 행정실에서 계약을 해달라고 연락이 온다. 학교마다 계약 방법이 다르다.

각각 위의 사이트에 접속해서 공인인증서를 사용하여 로그인한다.

사이트에 접속해서 '전자계약현황' 을 클릭한다. '공급업체' 탭을 선택하고 공인인증서 로그인을 한다. 비번은 gsfood6424& 이다.
좌측에 '전자계약현황 - 계약현황' 을 클릭하면 계약을 요청하는 건이 표시된다.

'초안수용 - 서명하기 - 서명하기 - 붉은색' 을 클릭한다.

계약상태가 '수요기관 서명대기중' 으로 변경된다.

사이트에 접속해서 '나라장터 인증서 로그인' 을 클릭한다. 공인인증서를 사용해서 로그인 한다. 비번은 gsfood6424& 이다. 로그인 후에 오른쪽 상단의 '문서함'을 클릭한다.
현재 진행 중인 계약 건이 표시된다. 클릭하고 나서, '접수 - 응답서 - 작성 -인지세 체크 - 저장 - 납세증명조회체크 - 송신 - 승인' 순으로 진행한다.
이후 상대방 학교에서 계약확인을 하면 완료가 된다.

1년에 3회 또는 4회 공공급식회의를 통해 앞으로의 3개월 또는 4개월 동안 공급할 친환경 품목과 단가를 결정한다. 여기서 결정된 품목과 단가는 차후 군청에서 공문의 형태로 학교에 전송된다. 따라서 변경되는 품목과 단가의 학교쪽 발주에 대해서는 문제될 것이 없다.
하지만, 어린이집과 유치원의 경우는 다르다. 학교처럼 단일화된 발주시스템을 가지고 있지 않기 때문에 변경된 품목과 단가가 적힌 발주서(엑셀 파일)를 메일로 보내줘야 한다.

친환경품목주문서양식_어린이돌봄기관_.xlsx 파일을 수정해야 한다. 파일을 열고, '친환경품목가격이력' 탭에 입력된 마지막 줄에 추가된 품목과 단가를 입력한다.

기존의 품목의 단가가 변경되었을 경우에도 기존 데이터를 수정하지 않고, 새롭게 추가 입력해야 한다. '등록일'은 가격이 납품되는 달의 첫 날로 지정한다.
추가했다면 '주문양식' 탭으로 와서 추가된 품목을 입력할 차례다. 여기서 입력할 품목은 새롭게 추가된 것들이다. 예를 들어 기존의 공급하던 '백미'의 단가가 변경되었다면, 입력할 필요가 없다. '딸기' 품목이 새롭게 추가되었다면, 맨 아랫줄에 입력한다.
'품명'과 '단위'를 제대로 입력해야 단가가 자동 출력된다. 그럼에도 불구하고 출력이 안된다면, 왼쪽 상단의 날짜가 '등록일' 이전인지 확인하자.

간단하게 테스트를 하고 메일을 보낸다.

수신: 괴산군내 어린이돌봄기관장
참조: 급식관련 담당자님, 초중고 영양사(선생님)
발신: 괴산군먹거리통합지원센터

1. 이는 2022년2차 학교급식가격결정위원회(이하 위원회) 결정 사항과 관련된 사항입니다.
2. 학교를 제외한 어린이 돌봄기관의 경우 급식관련 전산 인프라가 없는 관계로 수발주 소통에 혼선이 발생하여 위원회에서 결정된 가격을 반영한 수발주 양식을 아래와 같이 송부하오니 업무참조 바랍니다.

- 아 래 -

1. 양식설명

1) 첨부양식 왼쪽 상단의 연녹색란에 “4-1”,“5-1”,“6-1”,“7-1” 이렇게 날짜를 입력하시면

해당월의 요일과 해당월의 계약단가가 자동으로 표시됩니다

2) 회색음영부분은 함수가 심어져 있습니다 가능한 수정하지 마시고

녹색부분에 숫자나 문자를 입력하시면 합계가 자동계산됩니다

3) 첨부내용은 가상의 사례를 제가 입력해 놓은 것입니다.

* 어린이 돌봄기관의 경우 휴일날 주문사례, 더불어 금번에는 월별로 가격변동되는

품목이 다소 있어 첨부양식을 공유하오니 업무에 활용해 주시기 바랍니다. 감사합니다.

각 학교별로 입력이 이뤄져야하며, 아래와 같이 정보가 입력되어야 한다.

입력 후에는 거래명세서(액수가 있는)를 구매입력을 위해 별도로 보관한다. 학교에 보내는 것은 (액수가 없는)거래명세서다.
판매입력 후, ERP 거래명세서를 1부(고기) 출력해서 A서류의 가장 윗쪽에 놓는다(6장).

ERP 양식 판매입력.xlsx 을 사용한다. 앞서 발주 데이터를 저장한 '친환경물품주문서_먹거리집계용_진행중' 파일의 '구매요청' 탭에 있는 데이터를 저장한다.
입력 시, 오른쪽에 변환된 데이터가 출력될 것이다. 이것을 판매입력하면 된다.

엑셀파일에 입력했다면, 이제 ERP 상에 구매입력을 해야 한다. 수량이 많기 때문에 엑셀파일을 이용하여 일괄로 입력할 것이다.
거래처는 '농업회사법인 한살림축산식품(유)' 으로 지정하고, 구매한 품목을 입력하면 된다.

'Z:\HDD1\2022\3.생산\03 학교급식\02 단가산정자료\5,6,7,8월' 아래의 '(내부용_결정가격)5,6,7,8월 단가심의자료_(매입가,공급가)_(수수료5%,7%,15%,3%)_220418.xlsx' 파일을 참조한다.
제주도에서 매입하는 품목을 제외한 것들은 '매입가'에 적혀있는 숫자를 적용하고, 생드르에 매입하는 것들은 '생드르매입가' 탭을 참조한다.

'재고I - 기타이동 - 불량처리 - 불량처리입력' 메뉴에 입력해야 재고가 조정된다. 소분 담당자가 매일 작업 후 올리는 엑셀 파일(Z:\HDD1\2022\4.공급유통\01 학교급식\90 급식취합현황\학교급식_소포장_피킹_배송.xlsx)의 '작업일지' 탭에 있는 데이터를 참조한다.
이를 ERP 양식에 맞는 양식으로 변경해야 한다. 소분작업로스.xlsx '작업일지' 탭의 왼쪽에 데이터를 그대로 복사하면, 'ERP 양식' 오른쪽에 데이터가 변환되어 출력된다. 프로젝트 코드와 불량유형코드를 수정한 후, ERP 에 입력한다.

'급식품 구매예정량 통계표' 라는 제목의 엑셀파일로 발주서가 온다. school_request.xls 표 형태의 바둑판 양식인데, 이를 보고 일일이 입력하기가 번거롭다. 좀더 편하게 작업하기 위해 몇가지 방법을 여기에 적는다.

1. 엑셀 파일을 연다.
2. 가로로는 '식품명(단위)' 셀 선택 후, 달의 마지막 날까지 드래그하여 선택한다. 세로로는 마지막 품목까지 선택한다. 여기서 총금액 부분은 뺀다.
3. 복사하고, 새로운 시트에 붙여넣기를 한다.
4. '값'만 붙여넣기를 하고 잘못된 부분이 없는지 확인한다. 먼저 '식품속성'으로 수정한다. 날짜와 날짜 사이에 빈칸이 있다면, 삭제하여 빈칸이 없도록 한다(21,29 옆의 셀을 삭제한다).
5. 'Ctrl + a' 로 표 전체선택 후, 'Ctrl + t' 버튼을 눌러 '표만들기' 메뉴 그리고 '머리글 포함' 을 체크한다.
6. '데이터' 메뉴의 '테이블/범위에서' 를 클릭한다. Power Query 프로그램이 실행될 것이다.
7. 'Null' 값을 0으로 채워야 한다. 'Ctrl + a' 로 전체 선택을 하고, '변환 - 값 바꾸기' 를 통해 0 으로 수정한다(찾을 값:null, 바꿀항목:0).
8. '식품명, 규격, 식품속성 설명' 셀을 전체선택하고, '열 피벗해제 - 다른 열 피벗해제' 를 선택한다.
9. '특성' 셀의 이름을 '날짜' 로 변경하고 속성을 '정수'로 바꾼다.
10. '파일' 메뉴의 '닫기 및 다음으로 로드' 를 선택한다.
11. '데이터 가져오기' 창에서 '새 워크시트'를 선택하고 '확인'을 누른다.

이후 필요한 셀들을 수정한다.

1. '날짜' 셀을 가장 왼쪽(A열)으로 옮긴다.
2. '날짜수정' 셀(B열)을 만들고 '날짜자동입력양식' 시트의 값을 복사한다. 날짜와 날짜수정의 값이 일치하는지 확인한다.
3. '주문처' 셀(C열)을 만들고, 학교명을 입력 후 전체 채운다.
4. '식사시간' 셀(D열)을 만들고, 식사시간을 입력한다.
5. '식품명(단위)' 셀(E열) 옆에 새로운 셀을 하나 만든다(F열). 이름은 '상품'으로 지정한다. 이 셀로 식품명을 취합 엑셀에서 사용하는 품목명으로 변경해야 한다.
6. 새로운 시트(sheet3)를 만든다. A열에는 발주서에 적힌 식품명을 적고, B열에는 이에 대응되는 취합 엑셀 품목명을 적는다.
7. 다시 처음 시트로 돌아와서, F열에 함수값을 입력한다(=VLOOKUP(E4,Sheet3!$A$2:$B$15,2,FALSE)). H열 이름을 '값' 에서 '수량'으로 변경한다.
8. 전체 셀을 필터를 걸고, '수량' 셀에서 0 체크해제한다.
9. '식품명(단위)' 셀을 숨긴다.
10. '날짜수정, 주문처, 식사시간, 상품' 열까지 복사하여, 취합 엑셀에 붙여넣는다.

학교와 어린이집 발주양식이 다르다. 조금더 복잡하다. kindergarten_request.xlsx 양식을 보자.

1. 발주서를 다운받아, '단위', '단가' 항목이 있는 C,D 셀을 삭제한다.
2. '갈비(돈육)' 부터 시작하는 돈육 항목(B열)을 모두 선택한다. '병합하고 가운데 맞춤 - 셀분할' 을 선택한다. 셀이 보일 것이다.
3. '백미(유)' 셀의 윗칸(B4)을 선택하고, 월의 마지막까지 드래그하여 선택한다. 아래로는 마지막 품목까지 드래그 후 복사(Ctrl + c) 한다.
4. 새 시트를 하나 만들고 붙여넣는다. 이 때 옵션은 '값 및 숫자 서식' 을 선택한다.
5. '백미(유)' 셀(A2) 부터 A열의 마지막 셀을 선택한다. '찾기 및 선택 - 이동' 선택한다. 옵션을 누르고, '빈 셀(K)'을 선택 후 확인. 빈 셀만 선택된다.
6. 선택한 셀에서 마우스 왼쪽버튼, 팝업메뉴에서 '삭제'를 선택한다. '행 전체'를 선택한다.
7. A1 셀에 '상품' 이라고 입력한다.

이후부터는 위와 비슷하다.

1. 'Ctrl + a' 로 표 전체선택 후, 'Ctrl + t' 버튼을 눌러 '표만들기' 메뉴 그리고 '머리글 포함' 을 체크한다.
2. '데이터' 메뉴의 '테이블/범위에서' 를 클릭한다. Power Query 프로그램이 실행될 것이다.
3. 'Null' 값을 0으로 채워야 한다. 'Ctrl + a' 로 전체 선택을 하고, '변환 - 값 바꾸기' 를 통해 0 으로 수정한다(찾을 값:null, 바꿀항목:0).
4. '상품' 셀을 전체선택하고, '열 피벗해제 - 다른 열 피벗해제' 를 선택한다.
5. '특성' 셀의 이름을 '날짜' 로 변경하고 속성을 '정수'로 바꾼다.
6. '파일' 메뉴의 '닫기 및 다음으로 로드' 를 선택한다.
7. '데이터 가져오기' 창에서 '새 워크시트'를 선택하고 '확인'을 누른다.

이후 필요한 셀들을 수정한다.

1. '날짜' 셀을 가장 왼쪽(A열)으로 옮긴다.
2. '날짜수정' 셀(B열)을 만들고 '날짜자동입력양식' 시트의 값을 복사한다. 날짜와 날짜수정의 값이 일치하는지 확인한다.
3. '주문처' 셀(C열)을 만들고, 학교명을 입력 후 전체 채운다.
4. '식사시간' 셀(D열)을 만들고, 식사시간을 입력한다.
5. F열 이름을 '값' 에서 '수량'으로 변경한다.
6. 전체 셀을 필터를 걸고, '수량' 셀에서 0 체크해제한다.
7. '날짜수정, 주문처, 식사시간, 상품' 열까지 복사하여, 취합 엑셀에 붙여넣는다.

1순위는 괴산에서 도축한 고기, 2순위는 충북산, 3순위는 국내산이므로 같은 부위의 타지역 고기가 있으면 그것으로 납품할 것. 이 방법이 다른 부위로 변경 시 발생하는 차액 문제를 없앨 수 있어 권장한다.

흙살림 이수빈(010-4266-9800)에게 연락하여 발주한다.

셀의 입력된 값 중에 특수문자(~)가 있는지 확인하고, 이를 삭제하고 (-)로 수정한다.

4개월 한번씩 학교급식 품목단가 회의를 통해 품목이 추가될 수 있다. 이때 해야할 작업들이 있다.

1. ERP 거래처 및 품목 등록
2. 발주 취합 엑셀시트 업데이트
3. 유치원 발주서 엑셀시트 업데이트

여기서는 2번, 3번 과정을 중점적으로 설명한다. 참고로 3번 엑셀시트는 유치원 및 몇몇 학교들에게 발주 전에 공유되어야 한다.

발주서 취합 시트를 열어 '상품자료' 시트에 추가된 품목들을 입력해야 한다.

1. 가장왼쪽 테이블의 아래에 입력한다.

등록일은 입력된 품목이 적용되는 시작날짜를 지정하면된다. 단가는 학교에 공급되는 단가를 적는다. 이때 월마다 가격이 변동된다면, 매월 적어주어야 한다. 9월에는 5100원, 10월에는 6000원이 적용된다.

2. X열에 있는 상품테이블에도 추가한다. 마지막 줄에 '(무)포도(100g이상)' 를 입력한다.

3. AH열에 있는 테이블에도 추가한다.

이후, '수식 - 이름관리자' 메뉴를 클릭한다. 창이 뜨면, '품목단위' 라고 적힌 항목을 클릭한다. 참조대상(R)이 표시되는데, 여기서 테이블의 영역을 증가시켜주면 된다. 수정 후에는 'V'체크 아이콘을 클릭한다.

4. 제대로 적용되었는지 '구매요청' 시트에 데이터를 입력한다.

유치원 발주서 엑셀시트를 열어서, '친환경품목가격이력' 시트를 선택한다. 테이블의 맨 아래에 추가된 품목정보를 입력한다.

구글 스프레드시트의 이곳 을 사용한다.