엑셀 2007 고급활용 교육기간 : 2010. 7. 8 교육시간 : 13:30~17:30 교육기관 : TG정보기술원 강사명 : 류 정남 Homepage : http://aircool.tistory.com

함수와 매크로를 이용한 실무예제 재직증명서 발급하기 급여 계산서 만들기 거래처별 미수금 현황

재직 증명서 발급하기(1) 엑셀에서 미리 만들어진 사원 목록을 활용해 해당 사원의 재직 증명서 자동으로 발급하기 과정 사원 목록 작성하기 사원명부 : 목록의 첫 행으로 각 열의 이름 한꺼번에 정의하기 [수식]-[정의된 이름]-[이름정의] 사번 입력 제한하기 데이터 유효성 검사 설정->특정 범위의 값만 입력하도록 제한하기/오류메세지 보여주기 [데이터]-[데이터 도구]-[데이터 유효성 검사] 재직 증명서 양식 그리기 수식으로 재직증명서 완성하기 VLOOKUP: 배열의 첫 열에서 값을 검색하여, 지정한 열의 같은 행에서 데이터를 돌려준다.

재직 증명서 발급하기(2) 수식으로 재직증명서 완성하기 인쇄 영역 설정하기 TODAY() : 현재 날짜를 서식으로 표시 YEAR() : 1900~9999 이내의 정수로 일정 날짜의 연도 표시 TEXT(값, 형식) : 값을 지정한 형식으로 표시한 다음 텍스트 데이터로 변환 TEXT(C9,"yyyy년 m월 d일") 인쇄 영역 설정하기 [페이지 레이아웃]-[페이지 설정]-[인쇄영역]

급여 계산서 만들기(1) 사원의 직위와 호봉, 기본급, 각종 수당, 각종 공제 내역등이 포함되어 있는 급여계산서 만들기 과정 급여 기준 시트에서 이름정의 [C3:G3]-직위표, [C4:G8]-기본급, [C11:G12]-직책수당표, [C15:E17]-수당표 급여 계산하기 INDEX함수와 MATCH 함수로 기본급 찾기 =INDEX(기본급,E4,MATCH(D4,직위,0)) MATCH함수 범위에서 지정된 값으 찾아 상대 위치를 반환하는 함수 =match(lookup_value, lookup_array, match_type) loookup_value는 일치하는 값을 찾으려는 인수/lookup_array는 일치하는 값을 찾으려는 범위 match_type은 -1, 0, 1중 하나로 지정하며 생략하면 1로 처리. 이때 false(0)로 지정하면 반드시 일치하는 값 INDEX함수 배열형과 참조형이 있고, 배열형의 경우 범위에서 지정한 행과 열에 있는 셀의 값을 구하는 함수 =index(array, row_num,column_num) array는 참조되는 영역/row_num는 행번호를 지정/column는 열 번호를 지정

급여 계산서 만들기(2) HLOOKUP함수로 직책 수당 구하기/시간외 수당 구하기 =HLOOKUP(D4,직책수당표,2,0) =H4* HLOOKUP(H4,수당표,3) HLOOKUP 함수 : 범위의 첫 번째 행에서 지정한 값을 찾은 다음 지정한 행에서 같은 위치에 있는 값 구하기 HLOOKUP(값, 범위, 행, 방법) 방법: 0-일치하는 값, 1또는 생략-작거나 값은 값 중 최대값 가족수당 : 1인에 50,000원/5인 초과시 5인까지만 지급 =MIN(j4,5)*50000 의료보험 : 기본급의 3.5% 국민연금 : 기본급의 9% 세금 : 기본급의 2.2%(10원 이하는 반올림, 100원 단위로 계산) =ROUND(F4*2.2%,-2)

급여 계산서 만들기(3) 콤보 상자로 사원 이름 선택하기 개인급여 명세표 만들기 [개발도구]-[삽입]-[콤보상자] 입력범위 : 급여대상시트 [B4:B28] 셀 연결 : E2 (이름정하기:위치) 개인급여 명세표 만들기 급여대장 시트의 목록 이름 한꺼번에 만들기 [수식]-[정의된이름]-[선택영역에서 만들기]=>첫행 각 항목에 함수 적기 =INDEX(부서, 위치) = INDEX(직위, 위치) INDEX(지급합계, 위치) INDEX(공제합계, 위치) …

거래처별 미수금 현황(1) 복잡하고 반복되는 거래처별 미수금 현황을 한번에 처리하게 해 주는 매크로 기능 이용 VBA, 매크로 기록도구 사용 과정 개발도구 탭 표시 오피스 단추( )-엑셀옵션-리본메뉴에 개발도구 탭 표시 매크로 보안설정 [개발도구]-[코드]-[매크로 보안] => 매크로 설정-모든 매크로 제외(알림표시)로 설정 목록 시트의 거래처/목록 이름 만들기 거래처명, 제품명에 데이터 유효성 검사 설정/미수금 조건‘ >0’ 미수금 소계/건수 구하기 = SUBTOTAL(function_num, ref1, ref2, ...) : 목록이나 데이터베이스의 부분합 구하기 function_num : 1-평균, 2-개수 … 9-합계

거래처별 미수금 현황(2) 매크로 기록 도구 이용 매크로 편집하기 VBA로 매크로 편집하기 고급필터 과정을 매크로 기록기로 기록 전체 데이터 표시 과정을 매크로로 기록 양식 컨트롤 단추를 이용해 매크로 연결하기 매크로 편집하기 [개발도구]-[매크로]-[편집] VBA로 매크로 편집하기 거래처별 미수금현황 데이터에서 미수금 데이터가 검색될때마다 몇 건이 검색되었는지 메시지 상자 표시 MsgBox “미수금이 “ & Range(“j4”).Value & “건 검색되었습니다.”