[엑셀강좌:57] 실무 : 견적서, 거래명세표, 세금계산서 만들기 (4 견적서)

 이제 하단에 품목을 선택해서 견적을 완성해 본다.

 품목중 본체와 모니터 키보드, 마우스는 고정되어 있고 드롭다운을 통해 선택되며
 19 열 부터는 임의로 내용을 입력할 수 있도록 하였다.

 본체는 모델을 선택하면 우측의 규격이 해당 모델의 규격대로 변경되며
 단가도 자동으로 호출해서 금액을 계산하여야 한다.

 모니터 키보드 마우스도 역시 드롭다운을 통해 선택하여야 하며
 규격은 해당 모델의 규격을 적당하게 편집 (말이되게)해서 표시되며 역시 단가를 가져와서 계산한다.
 모니터 키보드 마우스의 수량은 본체의 수량에 따라 자동 설정되도록 하였다.

 우선 본체 선택을 구현해 보자.
 B9 셀 상단에 그림과 같이 콤보박스를 그려놓고 컨트롤 서식으로 들어가자.

   위에서 처럼 입력범위는 부품가격표 시트에서 이름정의한 본체목록으로 설정하고
   셀연결은 C9 으로 설정한다.  C 열은 굉장히 좁은 열로 그냥 콤보박스에서 선택한 각 품목의 컬럼값을 보관하기 위한
   장소이다.   보이거나 출력이 되지 않도록 문자색을 백색으로 설정하였다.
   콤보박스의 속성탭에서 개체인쇄를 해지 시켜 출력시 콤보박스가 나타나지 않도록 한다.

   이제 본체의 콤보박스를 눌르면

   위와 같이 본체목록이 리스트로 나오게 되며 이중 한가지를 선택하면 B9 에 해당 본체 모델명이 뜨게 된다.
    B9 은 콤보박스에서 선택한 컬럼번호가 들어가는 C9의 값을INDEX() 함수를 이용해서 모델명을 표시하도록 하였다.
    즉 =INDEX(본체목록,C9)  으로 본체목록 영역에서 콤보박스에서 선택한 행의 숫자의 값을 가져 왔다.
    
    다음은 D9의 메인보드 규격을 표시하는 것을 보자.
   ="MainBoard : " & INDEX(본체,$C$9,MATCH("M Board",본체스펙,0))
    이다.

    간단히 말하면 "MainBoard : " 라는 문자와 해당 본체의 M Board 열의 값을 연결하라는 말이다.
    본체의 규격의 데이터 영역은 "본체" 라고 이름 정의 되어 있으므로
    INDEX(본체, 컬럼, 로우) 함수를 써서 해당 데이터를 가져왔다.
    이때 컬럼값은 콤보박스를 선택했을 때 C9 에 들어 있으므로 절대번지로 $C$9으로 지정해 주면 되고
    로우(열) 값은 MATCH(찾을값,영역,구분) 의 함수를 사용해서 구했다.

    찾을 영역은 "본체스펙"이라고 이름 정의된 본체의 로우헤딩(열제목행) 영역이고
    해당 영역에서 "M Board" 라는 데이터가 몇번째 열에 있는가를 조사해서 해당 열(로우) 값을 가져오게 했다.
    구분은 0 (false) 를 주어 완전히 일치하는 자료를 찾게 했다.

    이렇게 선택된 본체 모델에 해당하는 M Board 의 값을 가져와 앞의 "MainBoard : " 와 & 연결자로 연결했다.

    다음 아래의 CPU, Speed, Ram, 등등의 스펙도 마찬가지로 MATCH() 함수내의 찾을값만 변경시켜
    해당되는 데이터를 가져와 만들었다.

    마찬가지로 F9의 단가도 MATCH() 함수를 이용해서 'Price" 열의 값을 찾고 INDEX() 함수를 이용해서
    해당 모델의 금액을 불러와 표시하고 금액은 수량 * 단가 (E9 * F9) 의 식으로 산출 하였다.

    다음 모니터, 키보드, 마우스 역시 콤보박스을 이용해서 모델을 찾도록 하였으며
    반환되는 행의 값은 C 열에 숨겨 놓았다.
    출력되는 모델 위치는 콤보박스 뒤에 있어서 화면에는 보이지 않으나 출력시에는 출력되도록 하였다.
  
    각 품목의 모델이 선택되면 규격에는 각 모델의 종류, 크기, 규격 등을 적당히 연결해서 표현 하도록 하였다.

    키보드의 규격(D17)을 보면
   =INDEX(키보드,C17,MATCH("Kind",키보드스펙,0)) & " " & INDEX(키보드,C17,MATCH("SPEC",키보드스펙,0))
    이 처럼 해당 품목의 선택된 모델의 필요한 규격을 문자열 연결자 & 를 사용해서 표현하였다.

    역시 각 선택된 모델의 단가도 MATCH() 함수와 INDEX() 함수을 이용해서 불러오도록 했고
    수량은 본체의 수량을 따라 가도록 하였다.

    19행 밑으로는 아무거나 첨가할 품목을 수기로 작성하도록 되어 있고 각 품목의 금액은 밑의 합계 금액에
    합산하도록 하였다.

    다음 30열 (그림에서는 20열)의 합계금액 (세액별도/세액포함) 의 A30 셀의 내용을 보자.

   앞쪽에 옵션단추(래디오버튼) 두개가 있다.  하나는 세액포함 하나는 세액 별도이다.
   이 두개를 선택하면 옆의 합계금액 (.... ) 의 괄호 속 내용이 바뀌게 된다.

   이 옵션단추의 선택된 값 (1 아니면 2)은 C31 에 들어가게 되며 역시 숨겨져 있다.
   옵션 단추는 그냥 컨트롤 서식에서 값이 어디에 들어 갈 것인가(셀연결)만 지정해 주면 된다.
   두 단추의 셀연결 위치는 동일해야 한다.

    D30 의 내용을 보자.
    =IF(C31=1,"합계금액 (세액포함)", "합계금액 (세액별도)")    이다.
 
    즉 옵션단추의 작동으로 인해 설정된 C31 의 값이 1이면 D30 은 "합계금액(세액포함)" 이 표시되고
    C31 의 값이 2 이면 D30 은 "합계금액(세액별도)" 의 값이 표시되게 된다.

    이 C31 의 값은 거래명세표와 세금계산서의 부가세 계산에 결정적 역활을 한다.

    미리 설명하면 세액포함의 경우라면 매출금액은 합계금액 / 1.1 이고 세액은 합계금액 - 매출금액이 된다.
    만약 세액별도의 경우라면 매출금액은 합계금액이 되고 세액은 매출금액 * 0.1 이 된다.


  영업관리 샘플. :  Sales.xlsx