엑셀 고급 활용 이 연란.

Presentation on theme: "엑셀 고급 활용 이 연란."— Presentation transcript:

1 엑셀 고급 활용 이 연란

2 1.수식 및 함수 활용 계산을 위해서는 기본적으로 피연산자와 연산자 개념을 알아야 하며 피연산자와 연산자를 조합하여 수식을 완성할 수 있어야 하므로 수식을 만드는데 기본이 되는 수식 구조와 참조 개념 함수는 계산에 필요한 값을 미리 만들어 놓은 수식에 대입하여 계산한 결과 값을 변환해 주는 계산식이다. 함수를 사용하는 방법 및 실무

3 인원수 구하기COUNTA, COUNTBLANK 함수
통계함수 불참 인원수 : =COUNTBLANK(E3:E22) 총인원수 : =COUNTA(B3:B22) 함수_Counta_참가명단

4 조건 인원수 구하기 COUNTIF, COUNTIFS
통계함수 남자 인원수 : =COUNTIF(C3:C22,"=남") 여자 인원수 : =COUNTIF(C3:C22,"=여“) 참석 인원수 : =COUNTIFS($C$3:$C$22,G9,$E$3:$E$22, "O“)

5 반올림 및 5,1만원/1천원 개수 표시하기 수학삼각함수 반올림 : =ROUND(D4,-3)
5만원 : =QUOTIENT(E4,50000) 1만원 : =QUOTIENT(E4-F4*50000,10000) 1천원 : =QUOTIENT(E4-F4*50000-G4*10000,1000) 함수_Round_주급표

6 번호와 합계 구하기 수학삼각함수 번호 : =ROW()-13
합계금액 : =SUMPRODUCT(J14:J23,K14:K23)*1.1 함수_ROW_거래명세표

7 순위 및 날짜 구하기 통계함수 날짜함수 순위 : =RANK.EQ(E4,$E$4:$E$19,0)
평균순위 : =RANK.AVG(E4,$E$4:$E$19,0) 오늘 날짜 : =TODAY( ) 검진 종료일 : =EDATE(C4,12) 함수_Rank_교육평가표 함수_EDATE_건강검진

8 검진 및 어학 점수 논리함수 검진유무 : =IF($F$2>D4,"O","X")
건강검진유무 : =IF(E4="O",200000,0) 3. 어학 점수 : =IF(F4>=850,"3",IF (F4>=700,"2","1")) 함수_IF_건강검진 함수_IF중첩_인사고과

9 승격 대상자 구하기 논리함수 승격 대상자 : =IF(AND(D4="A",E4="A",G4+H4>=5),"승격대상자","") 함수_IFAND_인사고과

10 보험 계약 건수의 합계 계산하기 수학삼각함수 계약건수합계 : =SUMIF ($C$4:$C$23,F4,$D$4:$D$23)
지점/보험종류 합계 : =SUMIFS($D$4:$D$23,$B$4:$B$23,$F11,$C$4:$C$23,G$10) 함수_Sumif_보험계약

11 조건을 만족하는 금액과 수량 평균 계산하기 통계함수
월별 금액평균 : =AVERAGEIF($B$3:$B$51,H3,$F$3: $F$51) 제품종류 평균 : =AVERAGEIFS($D$3:$ D$51,$C$3:$C$5,I$8,$A$3:$A$51,$H9) 함수_Averageif_납품실적

12 오류 처리하기 논리함수 오류 : =IFERROR(I9,0) 함수_Iferror_납품실적

13 문자열 함수 문자함수 =LEFT(A2,2) =RIGHT(A2,3) =PROPER(D2) =MID(F2,4,2)
=SUBSTITUTE(F2,"(주)","주식회사") =UPPER(I2) =LOWER(K2) 문자열함수

14 성별 및 생년월일 표시하기 성별 : =CHOOSE(MID(B4,8,1),"남","여","남","여")
찾기참조함수 논리함수 날짜함수 문자함수 성별 : =CHOOSE(MID(B4,8,1),"남","여","남","여") 2. 생년월일 : =IF(VALUE(MID(B4,8,1 ))>=3,DATE("20"&LEFT(B4,2),MID(B4,3,2),MID(B4,5,2)),DATE(LEFT(B4,2),MID(B4,3,2 ),MID(B4,5,2))) 함수_Choose_회원명부

15 날짜 사이의 간격(년, 월, 일) 구하기 날짜함수 근무기간 : =DATEDIF(D5,E5,"Y")&"년"에 이어서 ② =DATEDIF(D5,E5,"y")&"년"&DATEDIF(D5,E5,"y m")&"개월"&DATEDIF(D5,E5,"md")&"일“ 퇴직금 : =C5*30*((DATEDIF(D5,E5,"d")/ 365)) 함수_Datedif_퇴직금

16 신용 평가 등급 및 위험도 표시하기 찾기참조함수 신용평가등급: =HLOOKUP(B4,$H$4:$K$5,2,TRUE)
신용분류: =VLOOKUP(C4,$G$9:$I$12,2,FALSE) 위험도평가: =VLOOKUP(C4,$G$9:$I$12,3,FALSE) 함수_HVlookup_신용평가

17 비용계산하기 =INDEX(C5:L14,MATCH(D16,B5:B14,0),MATCH(F16,C4:L4,0)) 찾기참조함수
출장비지급표

18 매출성장평가 및 판정(사용자정의)

19 판매실적 성과급 (사용자정의)

20 2. 매크로 다루기 엑셀에서는 매크로를 만들기 위해서는 VBA(BISUAL BASIC FOR APPLICATION)을 이용하여 직접 프로그래밍하거나 매크로 기록 매크로 활용

21 매크로 대화상자 살펴보기 ① ② ③ ④ ① 매크로 이름 : 기록할 매크로 이름을 입력한다 매크로 이름은 첫 글자가 반드시 문자로 시작해야 하고 공백, ?, %, $ 등), 셀 주소는 사용할 수 없다. ② 바로 가기 키 : 매크로를 실행하는 바로 가기 키를 설정할 수 있으며 대소문자를 구별한다. ③ 매크로 저장 위치 : 자동 매크로가 기록될 위치를 개인용 매크로 통합 문서, 새 통합 문서, 현재 통합 문서 중에서 선택한다. ④ 설명 : 매크로에 대한 부연 설명을 입력한다.

22 매크로 실행하는 방법 살펴보기 매크로 실행하기 매크로 대화상자에서 실행하기: [개발도구]탭-[코드]그룹-[매크로]
바로 가기 키를 사용하여 실행 개체에 매크로 지정

23 매크로 편집하기 매크로 편집하기 비주얼 베이직 편집기(Visual Basic Editor) 화면 구성
[방법1] 매크로 대화상자에서 편집할 매크로 선택 후, [편집] 클릭 [방법2] [개발도구]탭-[코드]그룹-[Visual Basic] 클릭 [방법3] 단축키 <Alt>+<F11> 비주얼 베이직 편집기(Visual Basic Editor) 화면 구성 프로젝트 탐색기 창: 열려 있는 모 든 통합문서의 시트와 삽입된 모듈, 사용자 정의 폼이 표시됨. 속성 창: 선택된 개체의 속성 표시 코드 창: Visual Basic 코드를 편집 할 수 있는 공간 직접 실행 창: 코드 창에 작성한 프 로시저의 실행 결과를 미리 확인할 수 있는 곳

24 부분합 실행 매크로 상위점수 및 총고과점수

25 상위점수 및 총고과점수 영업이익률 증가

26 최대 및 최소판매대수 가중평균계산하기

27 한글 이름과 영문 이름 분리하기 내용 작성하기

28 중복 데이터 지우고 정리하기 내용 작성하기

29 문자를 날짜와 숫자 데이터로 변경하기 내용 작성하기

30 성적표 자동으로 인쇄하기 내용 작성하기

31 한 행의 데이터를 여러 행으로 분리하기 내용 작성하기