엑셀 VBA 낙찰(입찰) 예정가격 구하기

입찰공고문중

예정가격은 지방자치단체 원가계산 및 예정가격작성요령 과 전자입찰특별유의서에 따라 개찰전에 기초금액에 ±3% 범위내에서 입찰집행관이 랜덤정렬방식으로 15개의 복수예비가격을 확정하고, 다수 추첨된 복수예비가격 4개를 산술 평균한 금액으로 합니다.


15개중 4개를 취하는 조합(Combination)의 수 : 15C4 = 15P4 / 4! = 15!/(4!*(15 - 4)!)

1365

1) 조합된 수(1365가지)의 나열

2) 산술평균값을 구한다 : (P1+P2+P3+P4) / 4

3) 다수추첨 : 빈도(발생횟수)가 최다인 평균값을 구한다


각각의 일이 엑셀을 이용하여 한다고 하여도 수작업으로 한다면
15개의 예비가격중 4개를 선택하여 나열하는 일이 경우의 수가 많기 때문에
상당한 수고가 드는일이고
또 빈도를 세는 일도 만만치 않은 시간과 품이 드는 일이 될것이다

1) 조합된 수의 나열
    - 아래의 엑셀파일을 다운받은후 오픈한다
      

조합데이타출력.xlsm

    - "Data" Sheet를 열어 예정가격 15개를 입력한다
    - VBA창을 열고 프로젝트창에서 모듈을 찾아 해당모듈(Module1)을 더블클릭
    - 첫번째 라인(Sub GetRow)에 커서를 위치
    - F5를 눌러 실행(또는 실행메뉴를 눌러 Sub/사용자 정의폼실행)
    - Sheet1을 열어보면 1365개의 조합이 만들어져 있슴을 확인할 수 있다

2) 산술평균값을 구한다 : (P1+P2+P3+P4) / 4
    - Sheet1의 E1 셀 선택
    - 수직Scroll을 이용하여 1365열로 이동
    - 마우스 커서를 E1365에 위치 -> Shift+Click
    - =SUM(A1:D1)/4*90% Ctrl+Enter   
       ※※ 90%까지 입력후 반드시 Ctrl+Enter!!! 
       부가세 포함된 예정가이므로 부가세 10%제외
   
3) 다수추첨 : 빈도(발생횟수)가 최다인 평균값을 구한다
    - 2)가 완료된 상태에서 Ctrl+C
    - Sheet2로 이동하여 A2 셀 선택 -> 마우스 오른쪽 클릭 ->선택하여 붙여넣기 ->
       -> 값에 체크 -> 확인
    - 마우스오른쪽 클릭 ->셀서식 -> 표시형식 ->숫자 -> 소수자릿수 0, 1000단위 체크
       -> 확인
    - 리본메뉴에서 데이타 탭 선택 -> 정렬 (오름차순)

    - 부분합 이용(데이타 탭) 
       . 셀 A1에 "발생횟수"라고  입력
       .  데이타탭의 부분합 클릭
       . 사용할 함수에서 개수 선택 -> 확인
       . 부분합 그룹선택에서 2번째 선택 - > 셀 A1에  "예상가격" 입력 
       . 발생횟수 최다인 것을 발생횟수 열에서 찾는다
    
    - 부분합결과물을 다른 쉬트로 복사한후 데이타 정렬하여 찾기
      . 결과물 Selection
      . Ctrl+G -> 옵션 -> '화면에 보이는 셀만 ' 체크 -> 확인 
      . Ctrl+C -> Sheet3으로 이동 -> 셀 A1선택 -> Ctrl+V
      . 데이타 선택 -> 열B로 정렬 -> 최다 발생횟수 확인

    - 발생횟수의 분포를 확인하기 위한 차트 그리기
       . Sheet2(부분합 쉬트)로 이동 -> A열 전체선택 -> Ctrl+F -> 바꾸기 탭 선택
          -> 찾을내용 '개수' 입력 -> 확인
       . 셀 A1 선택
       . 삽입메뉴에서 차트 선택 - > 분산형 4번째(직선및표식이있는분산형) 선택
       . 차트가 생성되면 해당차트위에 커서위치 -> 오른쪽마우스 클릭 -> 데이터선택
       . 최종데이타로 이동하여 커서위치하고 Shift+Click -> 데이터원본 선택 창에서 확인
       . 분포에서 최다발생횟수 확인