스프레드시트 14 강 1. 스프레드시트의개요 ⑴ 스프레드시트 (Spreadsheet) 란? 1 기존의계산기와수작업으로수행했던수치계산및통계작업을컴퓨터에서할수있도록해주는계산전용소프트웨어로표계산프로그램또는전자계산서프로그램이라한다. 2 종류 :MS EXCEL, 칼크, LOTUS 1-2-3, Quattro Pro 등 ⑵ 스프레드시트의기능 1 계산기능 : 다양한종류의연산자와내장함수등을이용하여각종계산 ( 연산 ) 을수행할수있다. 2 차트작성기능 : 입력된자료를이용하여여러형태의차트 ( 그래프 ) 를작성한다. 3 문서작성기능 : 워드프로세서를사용하지않고도다양한서식의문서를작성할수있다. 4 데이터베이스기능 : 입력된데이터의관리및분석기능으로자료의정렬, 분류, 보고, 요약등이가능하다. 5 매크로기능 : 자주사용하는작업을하나의키에등록시켜반복적으로사용할수있다. 2. 엑셀의기초 ⑴ 화면구성 1 수식입력줄 : 셀에입력된내용이나수식등이표시되며, 데이터를입력하거나편집할수있는공간이다. 2 워크시트 (Worksheet): 많은셀로이루어진실제적인작업이이루어지는곳으로, 문서를작성하는종이역할을한다. 워크시트는행 (65, 536행 ) 과열 (256열) 로구성되어있다. 최대255개의시트가가능하며, 여러개의워크시트로구성된파일을통합문서라고한다. - 1 -
3 셀 (Cell): 워크시트를구성하는단위로행과열이교차되는부분이며데이터나수식을입력하는곳이다. 셀주소는셀의열머리글자와행숫자를합한것으로A1(A열의 1행 ), B10(B열의 10행 ) 등으로표시한다. 4 셀포인터 : 셀의가장자리에굵은선으로표시된선택된셀을의미한다. 5 시트탭 : 통합문서에포함된시트들의이름을나타내는곳으로, 시트탭을선택한후, 시트의 [ 삽입 ], [ 삭제 ], [ 이름바꾸기 ], [ 이동 / 복사 ] 등의작업이가능하다. 6 이름상자 : 선택된셀의주소나특정범위에정의된셀이름이표시된다. ⑵ 셀선택 ⑶ 데이터입력 ⑷ 데이터채우기 일정범위의숫자나문자자료를자동으로채워주는기능으로채우기핸들을드래그한 다. - 2 -
3. 수식과함수 ⑴ 수식 1 수식 : 셀주소, 상수및연산자를사용하여나타낸식을말한다. 2 연산자 : 산술, 비교, 텍스트, 참조연산자가있다. 3 셀참조 : 수식에사용되는셀주소의표현방법 상대참조 : 셀에입력된수식을다른셀로복사하면셀주소가자동으로변경된다. 예 ) =SUM(A1:A10) 절대참조 : 셀에입력된수식을다른셀로복사하여도셀주소는변경되지않는다. 예 ) =AVERAGE($A$1:$A$10) 혼합참조 : 셀에입력된수식이상대참조와절대참조가혼합되어있는형태이다. 예 ) =$A1+A$10 4 같은통합문서에서의셀참조 : 같은파일내의다른시트에서셀을참조하는경우 시트이름! 셀주소 로지정한다. 예 ) =Sheet1!$A1+Sheet2!A1 5 다른통합문서에서의셀참조 : 다른파일에서셀을참조할경우 [ 파일이름 ] 시트이름! 셀주소 로지정한다. 예 ) =[ 본사판매실적 ]Sheet1!$A1+[ 제1영업소판매실적 ]Sheet1!A10 6 셀에서의오류표시 #####: 셀에셀너비보다큰숫자, 날짜, 또는시간이있거나셀에계산결과가음수인날짜와시간이있을때발생하며, 열너비를조절하거나표시형식을바꾸어해결가능 #DIV/O!: 수식에서값을 0으로나누려고할때발생 #N/A: 함수나수식에사용할수없는값을지정했을때발생 #VALUE!: 잘못된인수나피연산자를사용하거나수식자동고침기능으로수식을고칠수없을때발생 #NUM!: 수식이나함수에숫자에관련된문제가있을때발생 #REF!: 셀참조가유효하지않을때발생 - 3 -
⑵ 함수 지정된순서나구조에따라인수라는특정값을사용하여계산하는미리정의된수식을말한 다. 기타알아두어야할함수들 : MONTH(), DATE(), NOW(), HLOOKUP(), VLOOKUP() 4. 차트의작성 ⑴ 차트작성 1 차트 : 워크시트의데이터를쉽게파악할수있도록그래프로표현한것이다. 2 차트작성 :[ 삽입 ]-[ 차트 ] 또는차트마법사 ( ) 를이용한다. 3 차트의종류 : 데이터내용에따라막대, 원, 꺽은선등의그래프를선택한다. - 4 -
⑵ 차트편집 1 원본데이터를수정하면자동재계산기능에의해차트에반영된다. 2 차트구성요소중수정, 변경하려는부분을마우스로더블클릭하거나바로가기메뉴에서해당대화상자를불러내어작업한다. 5. 고급기능 (1) 부분합 데이터를주어진조건에의해그룹화하고, 그룹화된데이터별로합계나평균, 표준편차등 을구할수있는기능이다. - 5 -
부분합을하기위해서는반드시부분합을구할필드를기준으로정렬되어야한다. (2) 필터원하는데이터만선택하여표시하는기능이다. 자동필터 : 원래의데이터위치에만데이터를추출할수있고엑셀에서제공하는조건을사용한다. 고급필터 : 원래의데이터와다른위치에도추출한결과를표시할수있고, 사용자가정하는조건을자유롭게적용시킬수있다. 1. 다음작성조건을고려하여스프레드시트 ( 엑셀 ) 문서를작성하였다. 이문서에사용된함 수식으로옳지않은것은? (2005 학년도수능 ) 1 [B8] 셀 : =MAX(B3:B6) 2 [D9] 셀 : =MIN(D3:D6) 3 [E3] 셀 : =D3/$D$7 4 [F3] 셀 : =RANK(D3, D$3:D$6) 5 [G3] 셀 : =IF(C3<B3, 증가, IF(C3>B3, 감소, 유지 )) - 6 -
2. 그림은스프레드시트 ( 엑셀 ) 에서부분합을실행하여나타난결과이다. 부분합을바르게계산 하기위한작업내용을 < 보기 > 에서고른것은? (2006 학년도 9. 학력평가 ) < 보기 > ᄀ. ( 가 ) 는 판매량 으로설정한다. ᄂ. ( 나 ) 는 합계 로설정한다. ᄃ. ( 다 ) 는 월별 과 판매금액 으로설정한다. ᄅ. 부분합을실행하기전에 월별 을기준으로정렬한다. 1 ᄀ, ᄂ 2 ᄀ, ᄅ 3 ᄂ, ᄃ 4 ᄂ, ᄅ 5 ᄃ, ᄅ 3. 스프레드시트 ( 엑셀 ) 문서에서다음 [ 수식 ] 을적용하여수식채우기작업을하던중 ( 가 ) 와같은오류가발생하였다. 오류원인과오류수정을위한 C3셀의수식을바르게짝지은것은? (2006학년도수능 ) [ 수식 ] 응답비율 [C3:C10] : 응답자수 / 총응답자수 [B10] 100 오류원인 1 수식에사용된숫자가너무큼 2 수식에사용할수없는문자열이있음 3 수식에사용할수없는문자열이있음 4 참조범위오류로빈셀이나 0으로나눔 5 참조범위오류로빈셀이나 0으로나눔 수식 =B3/$B10*100 =B3/$B10*100 =B3/$B$10*100 =B3/$B10*100 =B3/$B$10*100-7 -
4. 다음스프레드시트 ( 엑셀 ) 문서의 E3 셀에서사용된함수식을순서도로작성하였다. 작성된순서 도에서 ( 가 )~( 다 ) 에들어갈내용을바르게짝지은것은? (2006 학년도수능 ) ( 가 ) ( 나 ) ( 다 ) 1 D3셀 우수 D3셀 보통 D3셀 부진 2 D3 셀 부진 D3 셀 우수 D3 셀 보통 3 E3 셀 우수 E3 셀 보통 E3 셀 부진 4 E3 셀 보통 E3 셀 우수 E3 셀 부진 5 E3 셀 부진 E3 셀 우수 E3 셀 보통 5. 다음은 [ 조건 ] 에따라스프레드시트 ( 엑셀 ) 를활용하여작성한문서이다. 물음에답하시오. [ 조건 ] 원리금 = 원금 +( 원금 이자율 ) [D5:D7]: 자동채우기실행 [C9]: 함수를사용하여현재월표시 위 [ 조건 ] 에따라 [C9], [D7] 셀에사용된수식으로옳은것은? (2006 학년도수능 ) - 8 -
[6 번 ~7 번 ] 다음은 [ 조건 ] 에따라스프레드시트 ( 엑셀 ) 에서작성한문서이다. 물음에답하시오. (2009 학년도 6. 학력평가 ) [ 조건 ] 매입단가 [C4 : C7] : 함수를사용하여표시 매출액 = 수량 판매금액 매출이익 = 매출액 매입단가 수량 6. 위워크시트의 [C5] 와 [G5] 셀에사용된수식으로옳은것은? [C5] [G5] 1 =HLOOKUP(B5,B10:F11,2,FALSE) =E5-C5*D5 2 =VLOOKUP(B5,B10:F11,2,FALSE) =F5-C5*D5 3 =HLOOKUP(B5,$B$10:$F$11,2,FALSE) =E5-C5*D5 4 =VLOOKUP(B5,$B$10:$F$11,2,FALSE) =F5-C5*D5 5 =HLOOKUP(B5,$B$10:$F$11,2,FALSE) =F5-C5*D5 7. 위차트에대한설명으로옳지않은것은? 1 범례를품목아래로이동할수있다. 2 축제목금액과품목의글꼴을변경할수있다. 3 차트의참조범위는 [B3:B8], [F3:F8], [G3:G8] 이다. 4 축서식을이용하여눈금단위를 400에서 300으로변경할수있다. 5 [D6] 의값을 50으로바꾸면모자품목의매출액과매출이익이바뀐다. - 9 -
스프레드시트의기능 계산, 차트작성, 문서작성, 데이터베이스, 매크로기능등 함수의사용 SUM, COUNT, COUNTIF, AVERAGE, RANK, IF, MAX, MIN, AND, OR, ROUND, DAY, VLOOKUP, HLOOKUP, MONTH, NOW 등 셀참조방법 상대참조 복사할때참조된셀도따라서이동한다. 예 ) =SUM(A1:A10) 절대참조 혼합참조 복사할때참조된셀은고정되어있다. 예 ) =SUM($A$1:$A$10) 복사할때참조된셀은행, 열가운데한쪽만고정되어있다. 예 ) =SUM($A1:$A10), =SUM(A$1:A$10) 고급기능 : 부분합, 필터기능 1.5 2.4 3.5 4.3 5.2 6.5 7.3-10 -