Transcript 엑셀의 수식/함수

스프레드시트 정의 및 기능
정의
셀로 구성된 워크시트에 필요한 자료를 입력한 후 사용자가 원하는 계산,
데이터 정리, 도표, 그래프 작성 등의 다양한 형태에 맞도록 개발된 응용
프로그램.
기능
① 계산기능 : 데이터의 합계, 평균, 표준 편차 등을 계산.
② 도형 기능 : 데이터를 막대나 선, 원형 등의 차트로 표현하거나, 그림
삽입이 가능.
③ 문서 작성 기능 : 각종 표, 서식, 그림 등을 이용하여 다양한 문서를 작성.
④ 데이터 베이스 기능 : 입력한 데이터에 대한 특정 조건의 검색이나 자료
정렬.
⑤ 그래프 작성 기능 : 입력한 데이터와 수식으로 여러 유형의 그래프 작성.
⑥ 매크로 기능 : 반복되는 작업을 미리 기억시켜 놓아 쉽게 처리 가능.
엑셀의 화면 구성
엑셀의 데이터 종류
문자 데이터
수치 데이터
셀의 왼쪽에 입력.
셀의 오른쪽에 입력.
숫자를 문자처럼 취급되도록 자료를
입력해야 하는 경우 ‘를 입력 후
숫자를 입력.
0~9까지의 숫자, 콤마, 소수점, 부호
(+,-), 지수(e), %, $ 등의 기호를 함께
사용.
셀의 폭보다 긴 문자 자료가 입력된
경우 오른쪽 셀에 걸쳐서 표시되거나
잘려서 표시.
등호(=)로 시작하는 수식.
수식에는 숫자, 셀주소, 연산자, 함수
등을 포함.
셀의 폭보다 긴 수치 자료가 입력된
경우 “#” 여러 개로 표시되거나
지수형식으로 표시.
엑셀의 데이터 입력 및 수정
데이터 입력
①
②
③
④
⑤
⑥
⑦
⑧
⑨
특수 문자 : 한글 자음을 입력한 후 한자키
날짜 : 2009-03-20, 또는 2009/03/20 과 같이 입력
시간 : 시, 분, 초를 콜론(:)으로 구분
분수 : 숫자 0을 입력한 후 한 칸 띄고 입력 (예: 0 1/2)
수식 : 항상 등호(=)로 시작
한자 : 한글을 입력한 후 한자키
한 셀에 두 줄 이상 데이터 입력 : [Alt] + [Enter]
오늘 날짜 입력 : [Ctrl] + [;]
현재 시간 입력 : [Ctrl] + [Shift] + [;]
데이터 부분수정
① 셀을 더블클릭 후 수정
② [F2]를 누른 후 수정
③ 수식입력줄 클릭 후 수정
엑셀의 메모 및 셀 삽입/삭제
메모
[삽입] – [메모] 또는 바로가기 메뉴 [메모삽입].
빈 셀에도 메모 삽입 가능.
오른쪽 상단에 빨강색 삼각형 표시.
메모 삭제 시에는 바로가기 메뉴 [메모삭제].
메모가 삽입된 셀을 이동, 복사 시 메모도 함께 이동, 복사 됨.
셀 삽입/삭제
① 셀 삽입/삭제 : 기준이 되는 셀 선택 후 삽입/삭제 메뉴 선택
② 행(열) 삽입 : 삽입하려는 위치의 행이나 열의 범위를 설정하고 삽입
(선택한 행의 위에, 선택한 열의 왼쪽에 삽입)
③ 너비/높이 조절 : 행 머리글, 열 머리글 사이의 경계선에서 드래그,
또는 메뉴 사용
엑셀의 워크시트 편집
워크시트 이름 변경
시트 탭을 더블클릭
[서식]-[시트]-[이름 바꾸기] 또는 바로가기 메뉴 [이름 바꾸기]
워크시트 삽입
[삽입]-[워크시트] 또는 바로가기 메뉴 [삽입]
선택한 시트 탭의 왼쪽에 삽입
워크시트 이동/복사
이동 : 해당 시트 탭을 드래그
복사 : 해당 시트 탭을 [Ctrl] + 드래그
워크시트 삭제
시트 탭에서 바로가기 메뉴 [삭제]
삭제한 워크시트는 복원이 불가능
엑셀의 자동 채우기
같은 데이터 채우기 : 시작 셀에 데이터를 입력한 다음, 자동 채우기 핸들을
드래그.
연속된 데이터 채우기 : 인접한 두 셀에 연속된 데이터를 입력한 후 블록으로
지정하고 자동 채우기 핸들을 드래그.
숫자 연속 채우기 : 시작 셀에 숫자를 입력 후 [Ctrl] + 자동 채우기 핸들 드래그.
(1씩 증가)
엑셀의 표시형식
셀 서식 - 표시형식
서식코드
의미
서식지정
표시
#
유효하지 않은 0은 표시 안됨.
#.##
0
유효하지 않은 자릿수를 0으로 표시.
#.000
#.0#
1,000단위를 표시
#,###
#,
문자 삽입 기능
@님 합격! ‘홍길동’ 입력 → 홍길동님 합격!
yy
yyyy
연도를 00~99
연도를 1900~2007
m
mm
mmm
mmmm
월을
월을
월을
월을
d
dd
ddd
dddd
일을
일을
일을
일을
aaa
aaaa
요일을 한글로 일~월
요일을 한글로 일요일~월요일
,(쉼표)
@
1~31
01~31
Sun~Sat
Sunday~Saturday
100 → 100.
8.9 → 8.900
1223.4567 → 1223.46
12000 → 12,000
12000 → 12
1~12
01~12
Jan~Dec
January~December
엑셀의 수식/함수
주소 지정 방법
콜론(:) : 연속된 영역 → =SUM(A1:A5) : A1셀부터 A5셀까지의 합계를 구함.
콤마(,) : 지정된 셀만 → =SUM(A1,A5) : A1셀과 A5셀의 합계를 구함.
수식
엑셀의 수식/함수
날짜/시간 함수
DATE
=date(년,월,일)
년, 월, 일을 이용하여 특정날짜를 표시
TODAY
=today( )
현재 컴퓨터시스템의 날짜를 표시
NOW
=now( )
현재 컴퓨터의 날짜와 시간을 표시
YEAR
=year(날짜)
연도에 해당하는 값(데이터) 표시
MONTH
=month(날짜)
월에 해당하는 값(데이터)을 표시
DAY
=day(날짜)
일에 해당하는 값(데이터)을 표시
WEEKDAY
=weekday(날짜,유형)
해당하는 요일 번호를 표시
TIME
=time(시,분,초)
시,분,초를 이용하여 특정시간을 표시
HOUR
=hour(시간)
시에 해당하는 값(데이터)을 표시
MINUTE
=minute(시간)
분에 해당하는 값(데이터)을 표시
SECOND
=second(시간)
초에 해당하는 값(데이터)을 표시
엑셀의 수식/함수
논리 함수
IF
=IF(조건,참값,거짓값)
조건이 참이면 참값을 거짓이면 거짓값을 구함.
(국어가 90점 이상이면 합격 아니면 탈락)
AND
=and(조건1,조건2,조건3..)
조건을 모두 만족하는 값
OR
=or(조건1,조건2,조건3...)
조건중 하나만 만족해도 되는 값
NOT
=not(조건)
논리식의 결과 값을 반대로 표시
엑셀의 수식/함수
수학/삼각 함수
SUM
=sum(숫자(1),숫자(2)...)
지정된 셀 범위의 합계를 구하는 함수
SUMIF
=sumif(조건범위,조건,찾을범위)
조건을 만족하는 
값의 합계를 구함
ROUND
=round(인수, 자릿수)
지정된 자릿수로 반올림한 숫자를 구함
ROUNDDOWN
=rounddown(인수, 자릿수)
지정된 자릿수로 내림한 숫자를 구함
ROUNDUP
=roundup(인수,자릿수)
지정된 자릿수로 올림한 숫자를 구함
INT
=int(인수)
소수점이하 절삭하고 정수로 내림
ABS
=abs(숫자)
절대값을 구함
SQRT
=sqrt(양수)
양수의 제곱근을 구함
(숫자가 음수이면 오류값 표시)
POWER
=power(숫자(1),숫자(2))
거듭제곱한 결과
MOD
=mod(인수,제수)
나머지를 구하여 표시
엑셀의 수식/함수
통계 함수
AVERAGE
=average(숫자(1),숫자(2)...)
평균을 구하는 함수
MAX
=max(숫자(1),숫자(2)...)
지정된 범위 안에서 최대값을 구함
MIN
=min(숫자(1),숫자(2)...)
지정된 범위 안에서 최소값을 구함
RANK
=rank(값,참조영역,순위결정방법)
값-순위대상 셀 주소
참조영역-순위비교 참조 범위(절대참조)
순위결정방법-0또는 생략(내림차순)
1(오름차순)
LARGE
=large(셀범위,K번째)
범위에서 K번째로 큰 값
SMALL
=small(셀범위, K번째)
범위에서 K번째로 작은 값
COUNT
=count(숫자(1),숫자(2)…)
숫자의 개수를 세어줌
COUNTA
=counta(범위1,범위2…)
셀 범위 중 비어있지 않은 셀의 개수
COUNTBLANK
=countblank(셀범위)
공백개수를 구하여 표시
COUNTIF
=countif(범위,조건)
범위에서 조건을 만족하는 개수
엑셀의 수식/함수
문자열 함수
MID
=mid(문자열,시작위치,자릿수)
주어진 문자열중 시작위치부터 지정된 문자를 구함
LEFT
=left(문자열,자릿수)
주어진 문자열중 왼쪽부터 지정된 문자를 구함
RIGHT
=right(문자열, 자릿수)
주어진 문자열중 오른쪽부터 지정된 문자를 구함
LOWER
=lower(문자열)
입력된 문자열을 모두 소문자로표시
UPPER
=upper(문자열)
입력된 문자열을 모두 대문자로 표시
PROPER
=proper(문자열)
문자열에서 첫단어만 대문자로 표시
TRIM
=trim(문자열)
양쪽 공백을 제거
LEN
=len(문자열)
문자열의 길이 출력
엑셀의 정렬
정렬 [데이터] – [정렬]
정렬 기준은 최대 3개까지 지정, 기본적으로 행단위로 정렬.
정렬 방식 : 오름차순과 내림차순
영문 대/소문자를 구분하여 정렬할 수 있음
오름차순 : 숫자 > 문자 > 논리값 > 오류값 > 빈셀 순
(빈 셀은 내림차순으로 정렬 시에도 맨 마지막에 정렬)
문자는 오름차순 시 특수문자, 한글, 영문자(소문자, 대문자)순으로 정렬됨.
엑셀의 자동 서식/조건부 서식
자동 서식
미리 만들어 놓은 서식 모음을 이용하여 원하는 유형을 선택하여 쉽고
빠르게 서식을 적용.
적용할 서식 : 표시 형식, 글꼴, 맞춤, 테두리, 무늬, 너비/높이
실행 : [서식] – [자동 서식]
조건부 서식
조건에 맞는 특정 셀이나 행 전체에 서식을 적용하는 기능.
셀의 값이 변경되어 조건을 만족하지 않으면 적용된 서식이 해제됨.
조건은 3개까지 지정, 조건별로 다른 서식을 적용할 수 있음.
실행 : [서식] – [자동 서식]
엑셀의 자동 서식/조건부 서식
조건부 서식 예제
숫자 값중 70 미만인 숫자에는 “취소선과
굵게”셀 서식을 지정하는 조건부서식
홈런이 20이상이면 해당 하는 행의
글꼴스타일을‘굵은 기울임꼴’로 셀
서식을 지정하는 조건부서식
◆ 행 전체를 조건부 서식을 지정시
- 조건을 ‘수식’으로 지정
→ 조건에 해당되는 셀을 기준으로
=$F4>=20
으로 입력 함.
엑셀의 자동필터/고급필터
자동필터 [데이터]–[필터]-[자동필터]
단순한 비교 조건을 사용하여 간단한 데이터 추출 작업에 사용.
자동필터 목록단추를 이용하여 쉽고 빠르게 필터 조건을 설정할 수 있음.
데이터 목록에 반드시 필드명(열 이름표)이 있어야 함.
자동필터를 사용하여 추출한 결과는 원본 목록의 위치에 표시됨.
두 개 이상의 필드(열)에 조건이 설정된 경우 AND 조건으로 결합됨.
엑셀의 자동필터/고급필터
고급필터 [데이터]–[필터]-[고급필터]
복잡한 조건을 사용하거나 여러 필드를 결합하여 조건을 지정할 경우 사용.
원본 데이터와 다른 위치에 추출된 결과를 표시할 수 있음.
가장 먼저 워크시트에 조건을 입력해야 함.
조건을 같은 행에 입력하면 AND로, 다른 행에 입력하면 OR로 결합됨.
< 고급필터 조건 >
구분
금액
지출
>=10000
구분
금액
지출
▶ 구분이 지출이고 금액이 10000이상인 것
▶ 구분이 지출이거나 금액이 10000이상인 것
>=10000
엑셀의 차트
차트 구성요소 및 서식
차트종류 : 이중축 혼합형
차트제목 : 컴퓨터 성적현황
범례 : 상, 하, 좌, 우, 모서리 이동가능.
데이터계열마다 막대의 색상 변경 가능.
해당 데이터계열에만 데이터 레이블 ‘값’
표시가능.
X(항목)축 제목 : 이름
Y(값)축 제목 : 성적
보조Y(값)축 제목 : 총점
Y(값)축 최소값:0, 최대값:90, 주단위:10
보조Y(값)축 최소값:0, 최대값:300, 주단위:5
Y(값)축 주눈금선 표시.
이진수와 이진 연산
수를 적는 방법
2진법 : 0,1
→ 2개
8진법 : 0,1,2,3,4,5,6,7
→ 8개
10진법 : 0,1,2,3,4,5,6,7,8,9
→ 10개
16진법 : 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F
→ 16개
1010(2)
← 밑(Base)
이진수와 이진 연산
진법
2진수 → 101(2) = 1×22+0×21+1×20
8진수 → 234(8) = 2×82+3×81+4×80
10진수 → 456(10) = 4×102+5×101+6×100
16진수 → 3A7(16) = 3×162+A×161+7×160
이진수와 이진 연산
10진법과 각 진법간의 관계 1
18(10) 을 2진수, 8진수, 16진수로 나타내면?
8 18
2 18
2
9
0
2
2
4
1
2
2
0
0
22(8)
1
10010(2)
16 18
2
1
12(16)
2
이진수와 이진 연산
10진법과 각 진법간의 관계 2
각 2진수, 8진수, 16진수를 10진수로 나타내면?
10010(2) = 1×24+0×23+0×22+1×21+0×20
= 18(10)
22(8) = 2×81+2×80 =18(10)
12(16) = 1×161+2×160 =18(10)
12(5) = 1×51+2×50 =7(10)
이진수와 이진 연산
2진수로 변경하는 또 다른 방법
2의 제곱을 자리수에 대응
…
27
26
25
24
23
22
21
20
↓
↓
↓
↓
↓
↓
↓
↓
64 32
16
8
4
2
1
128
이진수와 이진 연산
8진법과 16진법간의 변환! 어느 방법을 이용할 것인가?
10진수
8진수
2진수
16진수
이진수와 이진 연산
2진법과 각 8진법, 16진법간의 관계
2진수 3자리 ☞ 8진수 1자리
100
4
010
2
2진수
8진수
2진수 4자리 ☞ 16진수 1자리
1010
A
0010
2
2진수
16진수
이진수와 이진 연산
8진법과 16진법간의 변환(10진수로 변환, 2진수로 변환)예
22(8) ☞ (
?
)(16)
2
0
1
1
2
0
0
1
2
← 8진수
0
← 2진수
← 16진수
이진수와 이진 연산
8진법과 16진법간의 변환(10진수로 변환, 2진수로 변환)예
101.01(2) ☞ (
?
)(16)
← 8진수
← 16진수
← 10진수
이진수와 이진 연산
2진수의 덧셈
110(2) +101(2) = ?
6
+5
110
+ 101
1
011
+1
11
← 자리올림
+1
1011
← 자리올림
이진수와 이진 연산
상이한 진수간의 연산
66(8) +101(2) = (
?
)(16)
이진수와 이진 연산
2진수의 뺄셈
컴퓨터는 뺄셈을
할 수 없다!?
이진수와 이진 연산
2진수의 뺄셈
보수 : 보충해주는 수를 의미하며
컴퓨터에서 음수를 표현하기 위한 수의 표현 방법으로
뺄셈을 용이하게 하기 위해 사용한다.
* 10진법에서 4의 보수는? 6
* 9진법에서 4의 보수는? 5
이진수와 이진 연산
2진수의 뺄셈 표현 (8bit 일 경우)
+7
-7
부호화된
절대값
0000 0111
1000 0111
1의보수
0000 0111
1111 1000
2의보수
0000 0111
1111 1001