작업 시간을 확 줄여주는 실무 함수

1. 7장 작업 시간을 확 줄여주는 실무 함수 찾기/참조 함수 정보 함수 재무 함수 데이터베이스 함수 논리 함수 통계 함수 수학/삼각 함수 텍스트 함수 날짜 및 시간 함수

2. 논리 함수 여러 조건에 맞는 값을 구하는IF함수의 중첩 여러 조건을 비교하는 조건식이면 AND, OR, NOT함수

3. 견적서 양식 만들기 논리 IF 중첩 함수 견적서는 제품을 공급하기 전에 공급자가 발주자에게 공급할 제품에 대한 내용과 비용을 기술하여 공급자에게 보내는 문서이다. 품목, 수량, 단가 등을 입력하면 자동으로 비용이 계산되는 견적서 양식을 IF 함수를 중첩해서 만들어본다 단가와 수량만 입력하면 자동으로 금액이 표시 되도록 하기 단가와 수량이 입력되기 전에는 금액을 공백으로 표시하기

4. 양식 컨트롤을 이용한 판매 금액 계산 논리 IF 중첩 함수와 양식 컨트롤 워크시트 문서에는 버튼, 옵션 단추, 체크 박스, 콤보 상자 등 양식 컨트롤을 삽입하고 셀과 연결할 수 있으며 컨트롤을 실행시켜 수식을 변경하거나, 특정 명령을 수행하는 등의 다양한 작업을 수행하게 할 수 있다. 제품별 판매 금액을 계산하는 표에 옵션단추 컨트롤을 3개를 삽입하여 각 옵션 단추를 클릭하면 할인율이 적용된 금액이 자동으로 계산되도록 중첩 IF 함수와 양식 컨트롤을 이용하여 판매금액을 계산해 보도록 한다 원하는 할인율을 선택하면 자동으로 할인율이 적용된 금액이 표시되도록 한다

5. 통계 함수 LARGE, SMALL함수

6. 통계 함수 배열 수식 • 배열 수식은 특정 범위의 배열과 배열, 배열과 셀, 배열과 비교 값끼리 비교하고 연산하는 수식 • 산술 연산자를 사용하면 연산된 결과 표시 • 비교 연산자를 사용하면 TRUE, FALSE가 값 표시 • 배열 수식은 수식 입력이 끝난 뒤에는 반드시 Ctrl+Shift+Enter 키를 눌러 앞서 입력한 수식이 배열 수식이라는 표시를 해주어야 한다. • 배열 수식이 만들어지면 배열 수식 앞뒤에는 { } 기호가 표시되며 배열 수식 일부는 수정하거나 삭제할 수 없다 = 배열1 * 배열2 입력 후 CTRL + SHIFT + ENTER 키

7. 통계 함수 FREQUENCY함수 사용 예 FREQUENCY(판매량, 구간수) 입력 후 CTRL + SHIFT + ENTER 키 구간 설명 : 999  : 판매량이 999 이하 1999  : 판매량이 1000부터 1999 사이 2999 : 판매량이 2000부터 2999 사이 공백 : 판매량이 3000 이상

8. 신입사원 교육 평가표 완성하기 통계 FREQUENCY, LARGE, SMALL 함수와 배열 수식 신입사원 교육 평가표를 참조하여 교육점수 평균이 상위/하위 5명의 점수를 구하고 교육점수 평균이 59이하, 60~69, 70~79, 80~89, 90 이상 구간별 빈도수를 FREQUENCY 함수를 사용하여 계산하고, 각 교육과목별 60점 이상의 인원수를 배열 수식을 사용하여 만들어 본다 FREQUENCY 함수로 평균 구간별 빈도수 구하기 LARGE 함수: 상위 5위 평균점수 SMALL 함수 : 하위 5위 평균점수 배열 수식으로 60점 이상 인원수 구하기

9. 수학/삼각 함수 곱하고 더하는 SUMPRODUCT함수 사용 예 SUMPRODUCT(수량배열, 단가배열) + + + X X X + + + X X X

10. 수학/삼각 함수 나머지를 구하는 MOD함수 무작위 난수를 만드는 RAND함수 RAND()*(b-a)+a b : 최대값, a : 최소값

11. 견적서 합계 금액 계산하기 수학/삼각 SUMPRODUCT 함수 발주자에게 보낼 견적서에서 수량과 공급 단가를 곱한 후 더한 공급가액에 부가세를 포함해서 합계 금액을 SUMPRODUCT 함수를 사용해서 계산한다 COUNTIF 함수를 이용해서 교육 차수별 인원수 구하기

12. 교육 차수 지정하기 수학/삼각 MOD 함수 회사에서 전사원을 대상으로 극기 훈련이나 정신 강화 교육 등을 진행한다. 1차수부터 3차수까지 사원들의 교육 차수를 정하려고 할 때 순서대로 차수를 입력하면 한 부서에 너무 많은 인원들이 한 번에 차출될 수 있으므로 MOD 함수를 사용하여 교육 참가 차수 지정하여 본다 COUNTIF 함수를 이용해서 교육 차수별 인원수 구하기 MOD 함수를 이용해서 교육 차수 1-3차수로 나눠 차출

13. 샘플용 테스트 제품 선정하기 수학/삼각 RAND 함수 제품의 샘플 코드를 무작위로 3개 추출하여 제품 테스트를 하려고 한다. RAND 함수를 사용하여 무작위 난수를 발생시킨 다음 3가지 상품을 추출하여 본다 . . . [F9] 키를 누를 때 마다 난수 발생

14. 텍스트 함수 일부 글자를 추출하는 LEFT, RIGHT, MID함수 사용 예 RIGHT(직급코드, 1) MID(직급코드, 4, 2) LEFT(직급코드, 2)

15. 텍스트 함수 표시 형식을 지정하는 TEXT함수 사용 예

16. 교통비 청구서 양식 완성하기 텍스트 TEXT, MID 함수 일반 회계 장부처럼 세금 계산서에 금액을 입력할 때 자릿수를 맞춰 한 칸에 하나씩 금액이 표시 되도록 MID ,TEXT 함수를 사용하여 교통비 청구서 양식을 완성한다

17. 날짜 및 시간 함수 날짜의 개념 1900년 1월 1일을 기준으로 9999년 12월 31일까지를 1~2958465라는 고유번호를 부여 셀 내용을 지우고,숫자 100을 입력 ▲ 날짜가 입력된 셀 ▲ 숫자 100이 날짜로 환산됨 시간의 개념 24시간을 1로 정의하고, 0과 1사이의 소수점으로 표시 [홈]-[편집]-[지우기]-[서식 지우기]메뉴 선택 ▲ 오후 12시가 입력된 셀 ▲ 서식을 지워, 숫자로 환산됨

18. 날짜 및 시간 함수 연, 월, 일의 YEAR, MONTH, DAY함수

19. 날짜 및 시간 함수 연, 월, 일의 간격을 계산 하는 DATEDIF함수

20. 날짜 및 시간 함수 요일을 숫자로 표시하는 WEEKDAY함수 근무 일수를 반환하는 NETWORKDAYS함수

21. 날짜 및 시간 함수 지정한 날짜의 개월 수를 계산하는 EDATE, EOMONTH함수

22. 임금 대장에 날짜 데이터 입력하기 날짜/시간 YEAR, EDATE, NETWORKDAYS, DATEDIF 함수 임금대장에는 1년간의 급여와 상여금의 내역을 정리하고 퇴직자가 발생하면 퇴직금 정산내역을 보여주는 문서이다. 출근일수는  NETWORKDAYS 함수로, 퇴직금 지급년도와 지급일은 YEAR, EDATE 함수로, 근속기간과 근속년수는 DATEDIF 함수를 사용하여 임금대장을 완성해 본다

23. 요일별 당직 수당 입력하기 날짜/시간 함수 WEEKDAY 함수 주말과 주중에 따라 차등을 두어 당직수당을 지급하려고 한다. 월~금요일에 당직을 서면 당직수당으로 20000을 지급하고 토~일요일에 당직을 서면 50000을 지급하도록 WEEKDAY 함수와 IF, OR 함수를 사용하여 당직수당을 표시하여 본다

24. 찾기/참조 함수 원하는 값을 찾아주는 VLOOKUP, HLOOKUP함수

25. 찾기/참조 함수 VLOOKUP 사용 예 B2셀의 제품코드를 H3:J9범위에서 찾아 그 줄의 2번째 열에 있는 값을 가져옴 상품 코드를 상품 코드표에서 찾음

26. 찾기/참조 함수 HLOOKUP 사용 예 B4셀의 수량을 F3:L4범위에서 찾아 그 줄의 2번째 행에 있는 값을 가져옴 수량을 수량별 할인율표에서 찾음

27. 찾기/참조 함수 행/열 번호로 원하는 데이터 값을 찾는 INDEX함수 INDEX 함수 사용 예

28. 찾기/참조 함수 행/열 번호를 찾는 MATCH함수 MATCH 함수 사용 예 [직급 표] 를 참조하여 ‘부장’ 값이 있는 행 번호 검색

29. 찾기/참조 함수 목록 값을 번호로 검색하는 CHOOSE함수와 행/열 번호를 알려주는 ROW, COLUMN함수

30. 입/출고 상품 내역 비교하기 찾기/참조 VLOOKUP 함수와 IFERROR 함수 상품이 입고된 내역과 출고된 내역을 비교하여 입출고 상품 내역서를 완성해 본다. [출고 상품 내역]에서 상품명은 [입고 상품 내역] 표를 참조해서 표시하고 [입출고 상품 내역] 표에서 출고 수량은 [출고 상품 내역] 표를 참조해서 표시한다. [출고 상품 내역] 표를 참조해서 출고되지 않은 상품은 “미출고”라고 표시하고 셀 색을 채워 강조한다

31. 지역/직급별 출장비표를 참조하여 출장비 내역서 만들기 찾기/참조 Index, Match 함수 [지역/직급별 출장비] 표를 참조하여 출장비 내역서의 일일 출장비가 표시되도록 INDEX 함수와 MATCH 함수를 사용하여 출장비 내역서를 완성해 본다 [지역/직급별 출장비] 표를 참조하여 일일 출장비 계산

32. 회원 가입 명단에서 일련번호, 성별 입력하기 찾기/참조 ROW, MID, CHOOSE 함수 신규 회원 가입 명단에 일련번호를 입력하고 신규 회원의 주민번호를 기준으로 성별을 표시하려 한다. 1900대에서 현재까지의 주민번호를 가정하여 MID와 CHOOSE 함수를 사용하여 성별을 구해본다 주민번호의 7번째 자리에 따라 1900년대: 1-남자 2-여자, 2000년대: 3-남자 4-여자, 1899년 이전 : 9-남자 0-여자

33. 정보 함수 셀 값의 오류나 수식의 오류를 검사하는ISxxx정보 함수

34. 상품 입고 내역서에 나타난 오류 수정 정보 ISERROR, ISNUMER 함수 상품 코드표에 상품 코드가 없는 경우 #N/A 오류 표시가 난다. 그리고 수량을 입력할 때 실수로 숫자 뒤에 쉼표를 입력했더니 금액을 계산하는 수식에 #VALUE! 오류가 나타난다. ISERROR와 ISNUMERIC 함수를 써서  #N/A 오류에는 “코드오류”라고 #VALUE! 오류에는 “수량오류”라고 표시해본다 IF, ISNUMER 함수를 사용하여 #VALUE! 오류에 ‘수량오류’ 표시 IF, ISERROR 함수를 사용하여 #N/A 오류를 ‘코드오류’ 표시

35. 재무 함수 재무에 필요한 FV, PMT, IMPT, PPMT, PV, NPER, RATE함수

36. 대출금 상황액과 이자율 계산하기 재무함수 재무 함수를 PMT, FV, PV, NPER, RATE 함수를 사용하여 대출금 상환액, 정기 적금 만기금, 연금의 현재 가치, 납입 기간, 이자율을 계산해본다(기준 시점은 월말)

37. 데이터베이스 함수 데이터베이스 함수

38. 도서 조회 화면 완성하기 데이터베이스-DGET 함수 도서목록 전체 데이터베이스에서 각 도서제목을 선택하면 해당 작가, 출판사, 가격, 할인율 등을 조회하는 화면을 DGET 함수를 사용하여 만들어 본다 DGET 함수를 사용하여 도서 조회