ITQ엑셀2007 강좌 1

내용설명은 2013년 4월 기출문제 A형입니다

  <문제> :   GE04-13A_문제.pdf

  <정답> :   GE04-13A_모범답안.xlsx

 

 

 

 

< 시트 이름 및 열 너비 등 기본 설정하기>

1. 시트 추가하기:Shift+F11키 누름(또는 Sheet3 우측 옆 워크시트 삽입 단추 클릭)

          * 시험에서는 시트 추가는 불필요함

2. 모든 시트 A열 열너비 1설정 하고 글꼴 및 맞춤하기

가. A열 열너비 1설정하기 : Sheet1이 선택된 상태에서 Shift키를 누르고 Sheet3클릭 (Shift+Sheet3)-그룹(또는 마우스가 시트명 위에 위치 우클릭 '모든시트선택'해도 됨)

나. A열 머리글 우크릭 열너비 1 설정

3. 글꼴 및 맞춤 설정하기 : 열이름과 행이름 교차점(모두선택) 클릭하고

기본 글꼴과 크기 설정, 맞춤에서 가로방향 가운데로 설정

4. 시트 이름바꾸기 : 그룹화된 시트 해제하고 각 시트명에서 우클릭하여 이름 바꾸기-제1 작업, 제2작업,...제4작업

5. 파일 저장하기 : 오피스단추 눌러 다른 이름으로 저장하기-내문서\ITQ폴더에 수험번호-자기이름으로 저장

 

[제1작업] 표 서식 작성 및 값 계산(240점)

<데이터 입력 및 셀 병합>

1. 데이터 입력 :

가. 제목 데이터 입력 : B4셀 선택 후 ‘도서번호’ 입력 후 Tab키(또는방향키->) 누르고 J4셀까지 입력(두줄 입력할 때에는 Alt+Enter 누르면 줄 바뀜)

나. B열 데이터 입력(영문 한글을 각각 따로 입력), C열~F열 데이터 입력

다. G열 날짜 데이터 입력 : 13-4-3 입력하면 2013-4-3로 입력됨(-또는/)

tip)ⅰ. G5열 데이터 입력 후 엔터키(G6열 선택이됨)하고 Ctrl+D 누르면 위의 셀 내용이 복사됨

ⅱ. 자동 채우기 기능(셀의 오른쪽 하단 까만 점 선택 후 드래그) 이용하여 채우기 한 후 부분적으로 수정하여도 됨 -자동 채우기 하면 일자가 하루씩 증가됨

라. F열 데이터 입력

2. 열 너비 자동 맞춤(C~E열 너비가 적어 데이터가 모두 보이지는 않음): C열~E열 머리글을 마우스로 드래그하여 블록으로 설정한 후 E열과 F열 경계선을 더블 클릭

3. 셀 병합 하기 : B13셀부터 D13셀 까지 드래그하여 블록 설정 후

가. 홈 텝 - 맞춤- 병합하고 가운데 맞춤 단추 클릭

나. B14셀은 B13셀에서 자동채우기 핸들로 드래그

다. F13:F14(:-연속되는 셀을 의미함)셀 병합하기

라. G13:I13 셀 병합하기

tip) 셀 병합할 부분을 Ctrl키를 누르고 모두 지정한 후 합하고 가운데 맞춤 단추 클릭하고 엔터키를 연속적으로 누른다

4. 나머지 데이터 입력

※ 주의: 제1작업 시트의 데이터는 제2,3,4작업에 영향을 미치므로 오타, 누락이 되지 않도록 확인해야 한다

 

<테두리선 설정 및 색 채우기>

1. 셀 테두리선 적용

B4:J14 드래그하여 블록 설정 후 홈 텝- 글꼴 그룹에서 테두리 펼침 버튼 클릭, ‘모든 테두리’ 클릭

2. 외곽의 굵은 테두리 선 적용

B4:J14 드래그하여 블록 설정 후 홈 텝- 글꼴 그룹에서 테두리펼침 버튼 클릭, ‘굵은 상자 테두리’ 클릭

3. 4행의 아래쪽과 13행의 위쪽 굵은 테두리선 적용

가.  B4:J4 드래그하여 블록 설정 후 홈 텝- 글꼴 그룹에서 테두리펼침 버튼 클릭, ‘굵은 상자 테두리’ 클릭

나. B13:J14 드래그하여 블록 설정 후 홈 텝- 글꼴 그룹에서 테두리펼침 버튼 클릭, ‘굵은 상자 테두리’ 클릭

4. F13셀의 대각선 적용하기 : F13셀 선택 후 Ctrl+1(셀서식 대화상자, 우클릭하여 셀서식을 선택해도 됨) 누른 후 ‘테두리’ 탭 선택하여 상향대각선과 하향대각선을 클릭하고 ‘확인’ 버튼 누름

5. 색채우기 : B4:J4, G14, I14셀을 Ctrl키 이용하여 블록으로 지정하고 홈 텝- 글꼴 그룹에서 ‘채우기 색’도구의 펼침 버튼 클릭하여 주황색 선택

 

<제목 도형 작성하기>

1. 1~3행 높이(22.5)설정 : 1~3행 머리글 드래그 하여 블록 설정 후 3행4행 머리글 경계선에 가져가 아래로 드래그 하면서 적당한 높이 맞춤(3행 높이가 동일하게 변경 됨), 블록해제는 아무셀이나 클릭하면 됨

2. 제목 도형 삽입 : 삽입 텝-일러스트레이션 그룹 - 도형에서 모서리가 둥근 직사각형 선택하여 적당한 크기로 드래그하여 도형을 그림

3. 제목(‘우리도서관4월 이용현황’)을 입력

4. 서식 설정 : Esc키를 눌러 도형을 선택한 후 홈 탭-글꼴 그룹에서 지정된 조건 설정, 글꼴-맑은 고딕, 24pt, 글자색: 검정, 굵게, 채우기 -노랑

5. 그림자 넣기 : 서식 탭 - 도형 스타일 그룹-도형 효과 - 그림자- 바깥쪽에서 오프셋 아래쪽 선택

 

< 결재란 만들기>

1. 결재란 만들기

가. 제 1작업시트에서 L16:L18 셀 합병하고 M16, N16, O16에 각각 담당, 팀장, 부장 입력함

나. 세로방향쓰기 : L16셀 선택 후 홈탭-맞춤 그룹 -방향도구에서 세로방향을 클릭

다. M17:M18셀 병합 후 자동채우기 핸들로 O18까지 드래그하여 N17:N18, O17:O18셀 합병

라. 테두리선 적용 : L16:O18 드래그하여 블록 설정 후 홈 텝- 글꼴 그룹에서 테두리펼침 버튼 클릭, ‘모든 테두리’ 클릭

2. 결재란  그림 복사하기 :

가. 결재란 블록 설정 후 우클릭하여 복사하기.

나. 제1작업 시트를 선택한 후 결재란을 붙일 적당한 위치를 마우스로 클릭하고 홈텝-클립보드 그룹-붙여넣기-그림 형식에서 '그림으로 붙여넣기' 클릭

3. 원본 결재란 삭제하기 : L16:O18 영역선택 후 우클릭하여 삭제

 

<유효성 검사>                                           동영상강좌 보기(여기 클릭)

유효성 검사는 입력된 데이터가 실제 유효한가 여부를 검사하는 것(예; 나이를 입력할 때 숫자가 아닌 문자가 입력되면 잘못된 자료로 인식하는 것)

1. H14셀 선택 후 데이터 탭-데이터도구 그룹-데이터 유효성 검사를 클릭하여 ‘데이터 유효성’ 대화상자에서 설정 탭-

가. ‘제한 대상’의 펼침 버튼 누름. 목록을 선택(목록은 시트에 입력된 데이터 목록을 이용하겠다는 의미)

나. 원본 영역을 마우스로 클릭하여 C5:C12셀을 드래그하여 블록 설정(절대참조$로 표시)하고 ‘확인’버튼 클릭

다. H4셀 선택하여 우측에 나타나는 펼침버튼 클릭하여 ‘여울물소리’ 선택(열경계선 더블클릭하여 열너비 조절)

 

<셀 서식을 이용하여 숫자 뒤에 문자 넣기>-사용자 지정 표시 형식

1. H5:H12셀 영역을 블록으로 설정하여 Ctrl+1(셀서식대화상자 호출)를 누름

표시형식 탭의 ‘범주’에서 ‘사용자지정’을 선택하고 ‘#,##0’를 선택하여 #,##0의 끝 부분을 마우스로 클릭하고 “회”을 입력하고 ‘확인’ 버튼.

2. 데이터 오른쪽 정렬 : H5:h12이 블록 설정된 상태에서 홈탭-맞춤그룹의 오른쪽 정렬 버튼 클릭

 

<이름 정의>

이름정의란 특정 영역에 애칭을 붙여두는 것-이름을 클릭하면 입력된 해당 영역이 선택됨

1. E5:E12영역에 대하여 ‘이용형태’로 이름정의 : E5:E12셀 영역을 블록으로 설정하여 이름상자 클릭하여 ‘이용형태’입력 후 엔터키 누름

(확인-D18셀 선택 이름상자 펼침 버튼을 클릭하여 나나탄 이름 중 이용형태를 선택하면 블록이 설정된 것 확인됨)

 

※ choose와 텍스트 함수에 대하여

1. choose 함수

=choose(인수, 첫 번째, 두 번째,.....,254번째) : 인수는 1~254까지 숫자로 입력하고, 인수가 1이면 첫 번째, 2일 때에는 두 번째......출력됨

2.텍스트 함수(문자 추출 함수)

가. LEFT : 텍스트문자열(text)의 시작 지점부터 지정한 수(Num_chars)만큼의 문자를 반환

=left(text, Num_chars) text-텍스트 문자열, Num_chars-왼쪽에서 추출할 문자수 지정

나. RIGHT : 텍스트문자열(text)의 끝 지점부터 지정한 수(Num_chars)만큼의 문자를 반환

=rjght(text, Num_chars)

다. MID : 텍스트문자열(text)에서 지정한 위치(Start_num)부터 지정한 개수(Num_chars) 만큼 문자를 추출

=mid(text, Start_num, Num_chars)

 

연습파일 :

choose함수와_텍스트 함수.xls

 

 

<분류>

1. I5:I12셀 블록으로 영역 지정후 함수 마법사 실행하여 choose함수 선택

index_nim : left(B5,1), value1 : "소설”, value2 : "에세이”, value3 : "시집” 입력하고 Ctrl+Enter키 누름

=choose(left(b5,1),"소설”,“에세이”,“시집”)

 

※ if 함수에 대하여

IF(조건, 참값, 거짓값) -조건이 참이면 참값부분, 거짓이면 거짓값부분을 표시

형식 =IF(Logical_test, Value_if_true, Value_if_false)

Logical_test : true나 false로 판정될 값이나 식

Value_if_true : Logical_test가 true일 때 돌려주는 값

Value_if_false : Logical_test가 false일 때 돌려주는 값

 

연습파일 :

IF와_다중IF함수.xls

 

 

 

<반납일자>

1. J5:J12셀 영역 블록 지정 후 함수마법사에서 IF함수 선택

Logical_test : B5="도우미 대출”, , Value_if_true : G5+7,

Value_if_false : if(B5="도서관 대출”, G5+5, G5)

 

=IF(B5="도우미 대출”,G5+7,if(B5="도서관 대출”, G5+5, G5))

 

※ 데어터베이스 함수에 대하여

1. 데이터베이스는 여러 사람에 의해 공유되어 사용될 목적으로 통합 관리되는 정보의 집합

2. 엑셀은 중간에 빈 행이나 빈 열이 들어가지 않는 연속된 셀들의 모임을 하나의 데어터베이스로 자동 인식. 따라서 일부 데이터만 이용하여 데이터베이스 기능을 사용할 때에는 반드시 영역을 지정해야 한다

3. 필드(Field)-열방향으로 모아 놓은 자료, 필드명- 필드의 이름

레코드(Record)-행방향으로 모아 놓은 자료

4. 데이터베이스함수는 함수명은 첫글자가 D로 시작한다. DSUM,DAVERAGE, DCOUNT,

DCOUNTA, DMAX, DMIN등

형식 : =데이터베이스함수명(전체범위, 찾을 열필드, 조건 범위)

 

<도서관 방문의 평균 대출횟수>

E13셀 선택후 함수마법사 실행하여 daverage함수 선택

=Daverage(Database, Field, Criteria)

Database : B4:J12, Field : H4, Criteria : E4:E5 입력 후 ‘확인’ 단추 클릭

 

<차량 도서관 이용 횟수>

E14셀 선택 후 함수마법사 실행 countif 함수 선택

=countif (Range,Criteria)

Range : 이용형태, Criteria : 차량 도서관

 

<최다 대출횟수(누적)>

J13셀 선택 후 함수마법사 실행 Max 함수 선택

=Max(Number1,Number2)

Number1-최대값을 구하려는 수 최대 255까지, 또는 범위 지정도 가능

Number1 : H5:H12 입력 후 ‘확인’ 버튼

 

※ VLOOKUP함수

지정한 범위 중 첫 번째 열에서 찾을 값을 검색하여 찾은 후 지정한 열의 같은 행에 있는 데이터를 반환해주는 함수(주의: 찾고자 하는 값이 들어 있는 데이터가 범위의 첫 번째 열이여야 한다)

=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)

Lookup_value : 찾으려는 값,

Table_array : 데이터를 검색하고 추출하려는 표,

Col_index_num : 값을 추출할 열번호를 지정,

Range_lookup:정확하게 일치하는 것 -0. 비슷하게 일치하는 것 -1

 

연습파일 :

HV_LOOKUP.xls

 

 

<저자 구하기>

1. J14셀 선택 후 함수마법사 실행 vlookup 함수 선택

Lookup_value : M14, Table_array : C5:H12, Col_index_num : 2, Range_lookup :0

 

<조건부 서식>

조건부 서식이란 '주어진 조건을 충족하는 셀(또는 영역)에 사용자가 지정한 서식을 설정'하는 기능

1. 데이터 막대를 이용한 조건부 서식

가. 조건부 서식을 설정할 영역을 범위로 지정

나 홈 탭- 스타일 그룹에 있는 '조건부 서식-데이터 막대'에 마우스 포인터를 올려 놓으면 6가지의 막대 종류가 나타남. 원하는 막대 종류를 클릭하면 셀에 막대가 삽입

2. 색조를 사용한 조건부 서식 지정

http://www.iexceller.com/MyXls/Excel_2007/Excel_2007_03.asp#bm_04

3. 수식을 이용한 조건부 서식

가. 범위를 지정하고 '조건부 서식-새 규칙'을 선택

나. '수식을 사용하여 서식을 지정할 셀 결정'을 선택하고, '다음 수식이 참인 값의 서식 지정'란에 '=mod(row(),2)=1'이라는 수식을 입력. '서식' 버튼을 클릭하여 적당한 색상을 지정한 다음 '확인' 버튼을 클릭하면 한 줄 건너마다 지정한 색으로 음영 처리가 됨.

4. 조건부 서식 지우기

'조건부 서식-규칙 지우기'에서 '선택한 셀의 규칙 지우기', '시트 전체에서 규칙 지우기', '이 표에서 규칙 지우기', '이 피벗 테이블에서 규칙 지우기' 중 하나를 선택하면 설정된 서식들이 한꺼번에 지워짐

 

** 엑셀2007 조건부 서식에서 수식을 이용하여 행 전체 글꼴 파랑색으로 적용하는 법?

B5:J12 선택후 조건부서식→셀 강조 규칙→기타규칙→새 서식 규칙에서 새 서식 규칙 대화상자가 나타면 '규칙 유형 선택'항목에서 '수식을 사용하여 서식을 지정할 셀 결정'를 선택한 후 '다음 수식이 참인 값의 서식 지정'에 조건(예시 =$G5>=2000)을 입력하고 '서식'버튼을 눌러 글꼴- 파랑색을 선택함 

상위_GE07-13A.pdf

0.42MB

ITQ자격시험(엑셀2007) 강의 내용.hwp

0.45MB

choose함수와_텍스트 함수.xls

0.02MB

상위_GE07-13A.xlsx

0.03MB

GE04-13A_문제.pdf

0.48MB

GE04-13A_모범답안.xlsx

0.02MB

HV_LOOKUP.xls

0.02MB

기술ppt14-기계요소1.ppt

4.61MB

IF와_다중IF함수.xls

0.02MB