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

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

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

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

④ 주민등록 생년월일 - 생년월일을 구할 영역에 대해 블록 설정 후 수식입력줄에 =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)

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

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

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

- 대화상자가 나타나면 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 셀이 포함된 열의 값을,,,,

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

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

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

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

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

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+ 확인

- [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. 소수점 이하 자리수를 버린다

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

- 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. 그렇지 않으면 맨 끝부분의 결과값을 표시한다

- 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으로 지정, 정확하게 일치하는 내용

- 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을 열 번호로 하여 데이터를 찾는다

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

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

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

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

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

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

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

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

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

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

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

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