Presentation is loading. Please wait.

Presentation is loading. Please wait.

직업기초능력 대한상공회의소 충북인력개발원 정보보안과 IoT엔지니어링과정 ( – )

Similar presentations


Presentation on theme: "직업기초능력 대한상공회의소 충북인력개발원 정보보안과 IoT엔지니어링과정 ( – )"— Presentation transcript:

1 직업기초능력 대한상공회의소 충북인력개발원 정보보안과 IoT엔지니어링과정 (2017. 3. 7 – 12. 28)
26번 김 용 선

2 1. 함수-1(날짜/텍스트) 시스템 상의 오늘 날짜에서 연도를 구하고 C3 셀의 연도를 구해서 날짜를 표시하라 ① 나이 - 나이를 구할 영역에 대해 블록 설정 후 수식입력줄에 =YEAR((TODAY())-YEAR(C3) 를 입력하고 Ctrl+Enter - D3:D12 영역이 블록 설정된 상태에서 홈 → 표시형식 → “일반” 을 선택

3 - 탄생요일을 구할 영역에 대해 블록 설정 후 수식입력줄에 =CHOOSE( 를 입력하고 수식입력줄의 함수마법사( )를 클릭
② 탄생요일 - 탄생요일을 구할 영역에 대해 블록 설정 후 수식입력줄에 =CHOOSE( 를 입력하고 수식입력줄의 함수마법사( )를 클릭 - 대화상자가 나타나면 Index_num에 Weelday(C3,2)를 입력, Value1에 월요일tab화요일tab수요일tab목요일tab금요일tab토요일tab 일요일 을 입력한 후 Ctrl+Enter C3 셀의 요일을 1-7까지의 숫자로 표시 두번째 인수를 “2”로 표시하면 월요일이 1,,,,,일요일은 7이 된다

4 - 작업시간을 구할 영역에 대해 블록 설정 후 수식입력줄에 =HOUR( 를 입력하고 수식입력줄의 함수마법사( )를 클릭
③ 작업시간 - 작업시간을 구할 영역에 대해 블록 설정 후 수식입력줄에 =HOUR( 를 입력하고 수식입력줄의 함수마법사( )를 클릭 - Serial_number 에 G3-F3 을 입력 후 Ctrl+Enter G3에서 F3의 시간을 뺀 후 시(HOUR)만 구하라

5 ④ 주민등록 생년월일 - 생년월일을 구할 영역에 대해 블록 설정 후 수식입력줄에 =DATE( 를 입력하고 Ctrl + A - year에 LEFT(I3,2), momth에 MID(I3,3,2), day에 MID(I3,5,2)를 입력한 후 ctrl+Enter =DATE(LEFT(I3,2), MID(I3,3,2), MID(I3,5,2)) I3 셀의 왼쪽에서 두자리 I3 셀의 3번째 자리부터 두자리 I3 셀의 5번째 자리부터 두자리 ---- 반환된 숫자 두자리씩 년도, 월, 일로 입력하라 MID(I3,3,2)

6 - 차트를 그릴 영역을 선택 후 수식입력줄에 =REPT( 를 입력하고 Ctrl + A
⑤ 김진아 출근시간 차트 - 차트를 그릴 영역을 선택 후 수식입력줄에 =REPT( 를 입력하고 Ctrl + A - text에 ■, Number_times에 HOUR(F3)을 입력한 후 Enter =REPT(“■”,HOUR(F3)) F3 셀의 시간에 해당하는 수만큼 “■” 문자가 표시된다

7 - 기준을 입력할 영역을 선택 후 수식입력줄에 =CONCATENATE( 를 입력하고 Ctrl + A
⑥ 기준 - 기준을 입력할 영역을 선택 후 수식입력줄에 =CONCATENATE( 를 입력하고 Ctrl + A - text1에 MONTH(TODAY()), text2에 “월 기준”을 입력한 후 Enter =CONCATENATE( F3 셀의 시간에 해당하는 수만큼 “■” 문자가 표시된다

8

9 함수-2(수학/삼각,통계, 논리값, 데이터) ① 셀 서식부터 지정합니다.(소수점 2자리까지 표시될 구역에 대해 선택)
- Ctrl 키를 이용하여 [E5:E16], [F5:F14], [J16] 영역을 선택 - 홈 → 표시형식 → “백분율” 을 선택한 후 소수 자리수를 “2”로 지정하고 확인

10 - 대화상자가 나타나면 Array1에 $E$3:$H$3를 입력, Array2 E5:H5 를 입력한 후 Ctrl+Enter
② 가중치를 적용하여 점수를 계산합니다 - [I5:I14] 영역에 대해 블록 설정 후 수식 입력줄에 =SUMPRODUCT( 를 입력하고 수식입력줄의 함수마법사( )를 클릭 - 대화상자가 나타나면 Array1에 $E$3:$H$3를 입력, Array2 E5:H5 를 입력한 후 Ctrl+Enter 1. 대응되는 값끼리의 곱을 모두 더해라 2. $E$3:$H$3 셀과 3. E5:H5 셀이 포함된 열의 값을,,,,

11 - [J5:J14] 영역에 대해 블록 설정 후 수식 입력줄에 =RANK( 를 입력하고 수식입력줄의 함수마법사를 클릭
③ 중급순위를 계산합니다 - [J5:J14] 영역에 대해 블록 설정 후 수식 입력줄에 =RANK( 를 입력하고 수식입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 Number에 G5를 입력, Ref에는 G5:G14 를 절대주소(F4키)로 입력한 후 Ctrl+Enter 1. 순위를 구해라 2. G5의 3. $G$5:$G$14의 범위에서 ,,,,

12 ④ 초급의 두 번째 점수를 구합니다(이름 정의 사용)
- E15 셀에서 수식 입력줄에 =LARGE( 를 입력하고 수식 입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 Array에 F5:F14를 입력, K에는 2 를 입력한 후 Enter 1. 큰 놈을 구해라 2. F5:F14의 범위에서 3. 2번째로 ,,,,

13 - E16 셀에서 수식 입력줄에 =ROUND( 를 입력하고 수식 입력줄의 함수마법사를 클릭
⑤ 중급의 평균을 구합니다 - E16 셀에서 수식 입력줄에 =ROUND( 를 입력하고 수식 입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 Number에 Average(G5:G14)를 입력, Num_digits에는 2 를 입력한 후 Enter 1. [G5:G14] 범위의 평균을 구해서 2. 소수점 이하 2자리까지 반올림하여 표시해라

14 - J15 셀에서 수식 입력줄에 =COUNTIF( 를 입력하고 수식 입력줄의 함수마법사를 클릭
⑥ 컴맹 10% 이상을 구합니다 - J15 셀에서 수식 입력줄에 =COUNTIF( 를 입력하고 수식 입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 Range에 E5:E14를 입력, Criteria에는 >=10% 를 입력한 후 Enter 1. [E5:E14] 범위에서 2. >=10% 와 일치하는 셀의 갯 수를 구하고 3. 구한 갯 수와 “그룹” 문자열을 하나의 문자열로 결합해라

15 - J16 셀에서 수식 입력줄에 =DAVERAGE( 를 입력하고 수식 입력줄의 함수마법사를 클릭
⑦ 연령그룹의 고급 평균을 구합니다 - J16 셀에서 수식 입력줄에 =DAVERAGE( 를 입력하고 수식 입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 Database에 B4:J14를 입력, Field에는 H4 를, Criteria에는 B4:B5를 입력한 후 Enter 1. [B4:J14] 범위에서 2. B4:B5 조건 범위에 해당하는 3. H4 열의 평균을 구해라

16

17 5. 함수-3(찾기/참조) ① 개조여부를 찾습니다
1. = choose(right(b5,1),”안함”,”부분개조”,”개조”) 2. B5 셀 문자열에서 오른쪽 1번째가 3. 1이면 “안함”, 2이면 “부분개조”, 3이면 “개조” ① 개조여부를 찾습니다 - B5:B13까지 선택하여 수식입력줄에 =CHOOSE( 를 입력하고 함수마법사를 클릭 - Index_Num에 Right(B5,1)를 입력하고 Value1, 2, 3에 차례로 안함, 부분개조, 개조 를 입력하고 Ctrl+ 확인

18 - [I5:I13] 영역에 대해 블록 설정 후 수식 입력줄에 =HLOOKUP( 를 입력하고 수식입력줄의 함수마법사( )를 클릭
② 중계수수료를 계산합니다 - [I5:I13] 영역에 대해 블록 설정 후 수식 입력줄에 =HLOOKUP( 를 입력하고 수식입력줄의 함수마법사( )를 클릭 - 대화상자가 나타나면 Lookup에 E5, Table Array에 $B$17:$E$18를 입력, Low에 2, Range에 0 을 입력한 후 Ctrl+Enter 입력된 수식을 수정하기 위해 [I5:I13] 영역에 대해 블록 설정 후 수식 입력줄에 =TRUNC(HLOOKUP(E5,$B$17:$E$18,2,0)*G5)를 입력 후 Ctrl+Enter $B$17:$E$18의 범위에서 E5와 같은 내용의 열을 찾아 2번째 행(수수료율)의 내용을 반환한다 (마지막 인수를 0으로 지정- 정확하게 일치되도록) 2. G5셀을 곱한 후에 3. 소수점 이하 자리수를 버린다

19 - D15에서 수식 입력줄에 =VLOOKUP( 를 입력하고 수식입력줄의 함수마법사를 클릭
③ 현시세(이름정의 사용) - D15에서 수식 입력줄에 =VLOOKUP( 를 입력하고 수식입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 lookup에 C15를, Table에 중개실적을, Col에 5, Range에 0을 입력한 후 Ctrl+Enter =VLOOKUP(C15,중개실적,5,0) C15 셀과 동일한 데이터를 중개실적으로 정의 된 [B5:G13]영역에서 찾아 3. 찾은 데이터의 5번째 열(현시세)을 표시한다 4. 마지막 인수를 0으로 하면 정확하게 일치하는 내용을 찾는다

20 - E15 셀에서 수식 입력줄에 =VLOOKUP(C15,B5:G13,6,0) 를 입력하고
④ 실거래가를 구합니다 - E15 셀에서 수식 입력줄에 =VLOOKUP(C15,B5:G13,6,0) 를 입력하고 - 등호기호(=)만 제외한 부분을 복사한 후 Delet 로 지운다 - =IF(를 입력하고 함수마법사를 클릭한 후 - 대화상자가 나타나면 (ISERROR(VLOOKUP(C15,B5:G13,6,0))를 입력하고 - true에는 ”없음”,false에는 VLOOKUP(C15,B5:G13,6,0)를 입력한 후 Enter =IF(ISERROR(VLOOKUP(C15,B5:G13,6,0))”없음”,VLOOKUP(C15,B5:G13,6,0) 1. C15와 동일한 데이터를 [B5:G13]에서 찾아 6번째 열(실거래가)을 표시한다 2. 그 결과로 오류가 발생하면 TRUE, 정상적인 계산을 하면 FALSE를 반환한다 3. TRUE로 반환되면 “없음”을 표시하고 4. 그렇지 않으면 맨 끝부분의 결과값을 표시한다

21 - H14 셀에서 수식 입력줄에 =MATCH( 를 입력하고 수식 입력줄의 함수마법사를 클릭
⑤ 현시세가 가장 낮은 물건명 위치를 구합니다 - H14 셀에서 수식 입력줄에 =MATCH( 를 입력하고 수식 입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 =MATCH(MIN(F5:F13),F5:F13,0을 입력한 후 Enter =MATCH(MIN(F5:F13),F5:F13,0 1. [F5:F13] 범위에서 가장 작은 값이 2. F5:F13범위에서 3. 몇 번째 위치에 있는지 위치 번호를 반환한다 4. 마지막 인수를 0으로 지정, 정확하게 일치하는 내용

22 - I15 셀에서 수식 입력줄에 =INDEX( 를 입력하고 수식 입력줄의 함수마법사를 클릭
⑥ 실거래가가 최고인 물건명을 구합니다 - I15 셀에서 수식 입력줄에 =INDEX( 를 입력하고 수식 입력줄의 함수마법사를 클릭 - 대화상자가 나타나면 첫 번째 항목 클릭 후 확인, - 대화상자에 Array를 B5:B13, Row를 match(max(G5:G13),G5:G13,0) column을 1로 입력한 후 Enter =index(B5:B13,match(max(G5:G13),G5:G13,0),1) 1. B5:B13의 범위에서 2. G5:G13 범위의 가장 큰 값이 G5:G13 범위의 몇 번째 위치에 있는지 위치 번호를 반환하여(마지막 인수 0으로 지정), 행 번호로 사용하고, 3. 1을 열 번호로 하여 데이터를 찾는다

23

24 6. 목표값 찾기/고급 필터/표 서식 ① 레드와인 평균 수입 단가를 찾습니다
=Daverage(b2:h12,h2,e2:e3) 1. 전체 범위인 데이터베이스에서 2. 수입단가의 열에 대하여 3. 분류가 레드인 것들의 평균을 구하시오 ① 레드와인 평균 수입 단가를 찾습니다 - H13 셀에서 수식입력줄에 =DAVERAGE( 를 입력하고 함수마법사를 클릭 - Database에는 전체의 범위를 설정 B2:H12, Field에는 H2(수입단가), criteria는 E2:E3(분류의 레드)을 입력한 후 확인

25 ※ 화이트와인의 평균 수입 단가를 H14셀에 찾아보세욤

26 ② 목표값 찾기를 실행합니다. - 뽀마르의 수입단가를 삭제합니다 - 평균 수입 단가가 기록된 셀(H13)에서 메뉴의 데이터- 데이터도구-가상분석-목표값 찾기 - 수식셀에 H13, 찾는 값은 450(임의의 숫자), 값을 바꿀 셀에는 삭제했던 수입단가가 있는 H6을 입력 후 확인 - “답을 찾았습니다.”라는 메시지가 표시되면 확인을 클릭하고 찾은 값을 반영한다. 목표값은 450으로 정해져 있을 때 뽀마르와인의 수입 단가가 얼마이어야 하는가를 찾아냅니다. 그렇다면 목표값은 500으로 할 때 까스델리와인의 수입 단가가 얼마이어야 하는가를 찾아보세요. 목표값은 700으로 할 때 마르살라와인의 수입 단가가 얼마이어야 하는가를 찾아보세요.

27 ③ 고급필터 - 조건의 위치를 지정하기 위해 B2와 G2셀을 복사하여 B15와 C15에 복사합니다 - 제품코드에 “R”이 포함되어 있는 것만 필터링 하기 위해 B16셀에는 *R*을 입력 - 1,000 Box 이상 판매된 것만 필터링 하기 위해 C16셀에 >=1000을 입력합니다 - 전체의 영역인 B2:H12를 블록 설정한 후 데이터-정렬 및 필터-고급 - 대화상자가 나타나면 다른 장소에 복사를 클릭하고 - 목록 범위에는 B2:H12를, 조건 범위에는 B15:C16, 복사 위치에 B18을 지정한 후 확인

28 ※ 제품 코드에 “R”이 들어가면서 칠레와인만 필터링 하시오
칠레산 레드와인만 필터링 하시오

29 ④ 표 서식을 변경합니다. - B18:H21 영역을 블록 설정한 후 채우기 없음 아이콘 클릭 - 홈스타일표 서식 [표 스타일 보통 3]을 선택한다

30

31 7. 중복데이터 제거 및 필터 ① 열너비까지 복사하기 위해 [A1]셀은 선택한 후, 홈붙여넣기의 아래쪽 삼각형선택하여 붙여넣기열너비 클릭확인

32 ② 데이터부분 모두 [B2:H12]를 선택한 후, 데이터탭데이터도구그룹중복된 항목 제거 클릭
- 중복된 항목 제거 대화상자에서 모두 선택 취소”생산지”만 클릭하여 확인 - 중복된 값이 모두 제거되고 고유한 값만 그대로 유지됩니다.

33 ③ 자동필터를 실행합니다. - 중복 데이터를 제거하고 고유한 값만 남은 상태에서 데이터부분 전체를 선택하면 각 항목별 삼각형이 생깁니다. - 판매의 필터 단추를 클릭한 후 숫자 필터”해당 범위” 메뉴를 선택합니다. - [사용자 지정 자동 필터] 대화상자에서 “>=“에는 500을 입력, “<=“ 에는 1100을 입력하고 확인 - 모두를 선택하여 복사하고, 선택하여 붙여넣기 메뉴 중 행/열 바꿈을 클릭하고 확인

34 8. 정렬 및 부분합 ① 다른 시트로 옮겨 데이터부분을 모두 붙여넣기 한다.
- 모두 선택하여 데이터정렬 및 필터정렬을 선택 - 대화상자가 나타나면 열을 “분류”로 선택하고 정렬은 “사용자 지정 목록”탭의 항목에 레드(엔터)화이트(엔터)디저트를 추가 확인

35 ② 데이터 모든 부분을 선택하여 데이터윤곽선부분합을 클릭
- 대화상자에서 그룹화할 항목에는 “분류”, 사용할 함수에는 “개수”, 부분합 계산 항목에는 “제품코드”만 선택하고 확인

36 ③ 그룹별 개수가 계산된 상태에서 다시 데이터윤곽선부분합을 클릭
- 사용할 함수는 “평균”으로 변경하고, 부분합 계산 항목에 “수입단가(단위:달러)”만 선택하고, 새로운 값으로 대치를 해제하여 확인

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57


Download ppt "직업기초능력 대한상공회의소 충북인력개발원 정보보안과 IoT엔지니어링과정 ( – )"

Similar presentations


Ads by Google