초코바

● 스크린팁

마우스 포인터를 색 위로 이동하면 색 이름이 텍스트로 표시되는 것을 의미한다.

색 위로 마우스를 올려도 풍선말이 안나오면 아래 순서를 따라하면 나오게 됩니다.

● 스크린팁 옵션 설정

파일 메뉴 > 옵션 > 일반 > 화면 설정 스타일 : 화면 설명에 기능 설명 표시

실습1 ) 입력을 넣어보고, 서식을 지정하면서 결과를 확인해보세요.

좀 더 자세히 소수점 부분 추가하여 설명하겠습니다.

0 (영) : 숫자의 자릿수가 서식에 지정된 자릿수보다 적으면 유효하지 않은 0을 표시한다.

설명 : 0000을 넣으면 강제로 4자리는 표시하게 된다.

0.000 으로 서식을 넣으면, 소수점 이하 3자리 까지는 무조건 표시해준다.

소수점 4자리 위치값이 5이상이면 반올림해서 3자리까지 표시해준다.

# : 0 과 동일한 규칙이지만, 입력한 숫자의 자릿수가 소수점 위쪽 또는 아래쪽에서 서식에 지정된 # 기호보다 적은 경우 추가로 0을 표시하지 않는다.

설명) #은 소수점 이하 3자리까지지만, 0.10 의 경우 소수 둘째자리가 0이면 표시하지 않게 된다.

소수점 3자리까지는 표시하되, 소수점 4자리가 5 이상이면 반올림하고 표시해준다. (서식 0, ? 와 동일)

? : 0과 동일한 규칙이지만 소수점 기준으로 각 방향에 유효하지 않은 0 대신 공백이 추가되어 열에서 소수점 기준으로 정렬된다.

설명) 소수점을 중심으로 정렬되는것을 볼 수 있다.

C5에서 123456.789553 이렇게 입력했지만, 서식에서 소수점 이하 ? 갯수는 4개이므로, 4개까지만 표시된다.

그리고 4개까지 표시하되, 다섯번째자리 수가 5이상이면 반올림해서 표시하게 된다.

ABS는 숫자의 부호 없이 숫자의 크기만을 나타내는 절대값을 계산할 때 사용합니다.

예를 들어, -4의 절대값은 4이고 +4의 절대값도 4입니다. 절대값은 특정 값과의 차이를 알아내서 비교할 때 많이 사용됩니다.

형식) ABS(인수) : 인수로 주어진 숫자의 절대값을 계산합니다.

● 아래 파일 다운 받아서 실습해 보세요

● 입력하고, 함수 적용 후, 결과값을 확인하세요.

정답)

E4 : ABS(E4) : [E4] 셀의 숫자에 절대값을 취한 값인 2가 [F4] 셀에 입력됩니다.

E7 : ABS(E7) : [E7] 셀의 숫자에 절대값을 취한 값인 5가 [F7] 셀에 입력됩니다.

@ SUMIF : 조건에 맞는 품목의 합계만 구하기

SUMIF 함수는 많은 자료 중에서 조건에 맞는 데이터만 찾아서 합계를 구하는 함수입니다. 조건이 적용될 범위에서 맞는 데이터를 찾아 합계를 구할 범위 중 같은 행에 있는 값들의 합계를 계산합니다.

형식) SUMIF(조건이 적용될 범위, 조건, 합계를 구할 범위)

☞ 조건이 적용될 범위에서 조건에 맞는 셀을 찾아 합계를 구할 범위 중 같은 행에 있는 값들의 합계를 구합니다.

● 실습파일을 다운 받아서 따라해 보세요.

● 입력하고, 함수 적용 후, 결과값을 확인하세요.

G4 : SUMIF(A4:A10,"컴퓨터",D4:D10) : [A4:A10] 영역에서 “컴퓨터”가 입력된 셀을 찾아, [D4:D10]영역의 같은 행에 있는 금액들의 합계인 7,500이 [G4]셀에 입력됩니다.

G5 : =SUMIF(A4:A10,"캠코더",D4:D10)

G6 : =SUMIF(A4:A10,"냉장고",D4:D10) : [A4:A10] 영역에서 “냉장고”가 입력된 셀을 찾아, [D4:D10] 영역의 같은 행에 있는 금액들의 합계인 2,750이 [G6]셀에 입력됩니다.

정답결과)

컴활1급 기출)

● 기출문제

각 항목에서 등급이 ‘고급형’인 제품의 판매량, 단가, 원가비율의 평균을 구하세요.

C9 : =SUMIF($B$3:$B$8,"고급형",C3:C8)/COUNTIF($B$3:$B$8,"고급형")

D9 : =SUMIF($B$3:$B$8,"고급형",D3:D8)/COUNTIF($B$3:$B$8,"고급형")

E9 : =SUMIF($B$3:$B$8,"고급형",E3:E8)/COUNTIF($B$3:$B$8,"고급형")

정답결과)

◇ 수식의 이해

=SUMIF($B$3:$B$8,"고급형",C3:C8)/COUNTIF($B$3:$B$8,"고급형")

▹SUMIF(B3:B8, “고급형”, C3:C8) : [B3:B8] 영역에서 “고급형”이 입력된 셀([B3], [B7])을 찾은 후 [C3:C8] 영역의 같은 행([C3], [C7])에 있는 판매량들의 값(35, 25)을 누적한 ‘60’이 계산됩니다.

▹COUNTIF($B$3:$B$8,"고급형") : [B3:B8] 영역에서 “고급형”이 입력된 셀([B3],[B7])의 개수인 ‘2’가 계산됩니다.

60/2의 결과인 30이 [C9] 셀에 입력됩니다.

COUNTIF 함수는 조건에 맞는 데이터의 개수를 구하는 함수입니다.

PRODUCT 함수는 인수로 주어진 숫자를 모두 곱하는 함수입니다.

인수는 1개에서 255개까지 지정할 수 있습니다.

형식) PRODUCT(인수1, 인수2, …) : 인수(인수1, 인수2, …)로 주어진 숫자들을 모두 곱합니다.

아래 파일을 다운로드해서, 아래 예제를 따라 해보세요.

실습1)

● 입력하고, 함수 적용 후, 결과값을 확인하세요.

정답)

D4 : =PRODUCT(A4:C4)

[A4:C4] 영역의 값을 모두 곱한 (5x10x1)값인 50이 [D4]셀에 입력됩니다.

기출문제)

판매량, 단가, 원가비율을 이용한 구입원가를 계산하여 표시하세요.

정답)

F2 : =PRODUCT(C2:E2)  or =PRODUCT(C2,D2,E2)

▷수식의 이해

=PRODUCT(C2,D2,E2)

[C2], [D2], [E2] 셀의 값을 모두 곱한 (35x1200x70%) 값인 29,400이 [F2] 셀에 입력됩니다.

▹인수가 오류값이거나 숫자로 변환될 수 없는 텍스트일 경우 오류가 발생합니다.

▹인수가 배열 또는 셀의 주소이면 해당 배열 또는 셀의 주소 영역에 있는 숫자만 계산됩니다.

▻ FV 함수 / Future Value

=FV(이자율, 총지급횟수, 정기납부액, 현재가치, 납입시점)

FV함수는 고정 이자율을 기반으로 투자의 미래가치를 계산하는 함수입니다.

쉽게말해서, 적금을 몇 년 후 이자와 함께 얼마를 받을 수 있는지 가르쳐 주는 함수입니다.

● 아래 파일을 다운로드 받아서 실습을 해보세요.

실습1)

정답)

D7 : =FV(D3/12,D4,-D5,0,0)

설명)

1. 지금 적금 상품을 들면 입금한 금액의 2.3%의 이자율을 준다고 합니다. =FV(D3/12,

​(12로 나눈 이유는 월로 계산하기 위함입니다.)

2. 딱 1년만 우선 하기로 하고 =FV(D3/12,D4,

3. 월마다 10만 원씩 입금하기로 했습니다. =FV(D3/12,D4,-D5,

(여기서 중요한 것은 '-'입니다. 결과값이 양수로 나오기 위해서 마이너스를 해야 합니다.)

4. 참고로, 현재가치는 생략하면 '0'이 됩니다.

또한, 납입 시점의 '0(생략)'은 월 말, '1'은 월초 납입을 뜻합니다.

특별히 지정할 것이 없다면, 생략하시면 됩니다. =FV(D3/12,D4,-D5,0,0)

그래서 1년 후에 이자(미래 가치)를 12,731원을 더 받을 수 있다는 것을 예상하게 되었습니다.

☞ 처리 과정을 아래 그림을 참고하세요.

이자 :   E13 : =F22*2.3%/12

누계 :  F12 : =SUM(F11,D12,E12)

추가설명)

▷ 추가 설명

FV는 재무 함수 중 하나입니다. 미래 투자 상환금을 미리 알아보기 위해 필요한 인수들은 다음과 같습니다.

주의할 것은 Rate에 지정한 기간에 맞춰서 Nper기간을 정해야 합니다. Rate가 월별로 이자율을 지정했다면, Nper도 개월 수를 입력해야 합니다.

▸ Rate : 투자에 대한 기간별 이자율입니다. 연간 이자율이면 12로 나누어서 월별 이자율을 입력해야 합니다.

▸ Nper : 투자상환금 총 지급 기간 수입니다. 월별로 따지기 때문에 4년 만기 이면 4*12를 해야 합니다.

▸ Pv : 미래 지급액에 상응하는 현재가치를 말합니다.

▸ Pmt : 각 기간마다 지급액에 해당하여 월별 적립금입니다.

▸ Type : 1은 적금일 월초, 0은 월말로 합니다.

실습2)

만기금액을 FV함수를 이용하여 구하시오.

정답) E3 : =FV(B3/12,C3*12,D3)

기출문제)

'만기지급액'은 5년간 연이율 4%로 매월 초에 예금한 후 매월 복리로 계산되어 만기에 찾게 되는 예금액을 양수로 계산하여 표시하되, 백의 자리까지만 표시되도록 자리 올림하시오.

정답) E3 : =ROUNDUP(FV(4%/12,5*12,-D3,,1),-2)

SUMPRODUCT 함수는 인수로 주어진 배열의 각 해당 요소들을모두 곱한 후, 그 곱들의 합계를 반환하는 함수입니다.

인수로 사용하는 배열의 행수와 열수는 모두 같아야 합니다. 배열의 행수와 열수가 같지 않으면, ‘#VALUE!’ 오류값이 반환되고, 숫자가 아닌 항목은 0으로 처리됩니다.

형식) SUMPRODUCT(배열1, 배열2, …) : 배열1과 배열2를 곱한 후 결과를 모두 더하면 됩니다.

● 아래 파일을 다운로드 받고 실습을 해보세요.

실습)

정답)

=SUMPRODUCT(A4:A7,B4:B7) : [A4:A7] 영역의 값과 [B4:B7] 영역의 값을 다음과 같이 대응([A4] x [B4], [A5] x [B5], [A6] x [B6],

[A7] x [B7]) 되게 곱한 값의 합계인 100이 [B9] 셀에 입력됩니다.

2*3=6

4*4=16

6*5=30

8*6=48

6+16+30+48 = 100

● 기출문제 (컴퓨터활용능력 1급 기출 문제입니다. 풀어보시기 바랍니다)

기말수당을 계산하여 표시하시오.

▷ ‘기말수당’은 개설강좌, 초과강의, 학생지도별 가중치 (C2:E3)를 곱한 값들의 합으로 계산

정답 ) =SUMPRODUCT($C$3:$E$3, C6:E6)

※ 수식의 이해

=SUMPRODUCT($C$3:$E$3, C6:E6)

[C3:E3] 영역의 값과 [C6:E6] 영역의 값을 다음과 같이 대응되게 곱한 값의 합계인 1,530,000이[F6] 셀에 입력됩니다.

※ 자격증 준비하시는 분은 자주 반복하여 익히시면 됩니다.

엑셀을 처음 하시거나, 자주 안하시는 분은 어렵게 느껴질 수 있습니다. 자주 반복적으로 하면

다른 함수들도 쉽게 익히게 됩니다.


엑셀 공부하시는 분은 사무자동화산업기사, 컴활 1급, 2급 준비해보세요. 국가자격증을 위주로 먼저 따면 됩니다.

(컴활은 컴퓨터활용능력) 여러 자격증이 있지만, 우선순위로 놓으면 국가자격증이 제일 도움 됩니다.

민간자격증은 제일 우선순위를 아래에 놓으시기 바랍니다. 이왕이면 시간과 돈을 투자하는데, 가장 도움되고, 중요한 자격증을 먼저 따는게 좋습니다.

@ ROUNDUP

ROUNDUP 함수는 숫자를 지정한 자릿수로 자리올림하여 표시하는 함수입니다. 예를 들어 ‘ROUNDUP(35.6713 , 2)’라면 35.6713을 소수 셋째 자리에서 자리올림하여 소수 이하 둘째 자리까지 표시하므로 35.68이 됩니다.

☞ 단, 일의 자리 위치를 0으로 잡고 해석하면 쉽다. 해당 자리까지는 표시하되,

표시하는 숫자 뒤에서 up은 무조건 반올림해주고, down은 무조건 내림하여 0이 됩니다.

형식) ROUNDUP(인수, 올림 자릿수) : 인수에 대하여 지정한 자릿수로 올림합니다.

● 파일을 다운로드하여 아래 실습을 따라해보세요.

● 입력하고, 함수 적용 후, 결과값을 확인하세요.

정답)

G3 : =ROUNDUP(F3,0) : [F3] 셀의 값 76.667을 소수 이하 첫째 자리에서 자리올림하여 소수 이하 0자리까지 표시하므로 77이 [G3]셀에 입력됩니다.

해당 위치에서 자릿수가 올림된다. 뒤의 숫자가 5이상 또는 이하이든 상관없이 해당 자릿수가 올림하게 된다.

기출문제)

‘만기지급액’은 5년간 연이율 4%로 매월 초에 예금한 후 매월 복리로 계산되어 만기에 찾게 되는 예금액을 양수가 되게 계산하되, 백의 자리까지만 표시되도록 자리 올림하시오.

▻ FV 함수

=FV(이자율, 총지급횟수, 정기납부액, 현재가치, 납입시점)

FV 함수는 미래가치(매월 일정한 금액을 불입하였을 경우 만기일에 받을 원금과 이자를 계산)를 구하는 함수입니다.

☞ 정답) =ROUNDUP(FV(4%/12,5*12,-D3,,1),-2)

※ 수식의 이해

=ROUNDUP(FV(4%/12,5*12,-D3,,1),-2)

FV(4%/12,5*12,-D3,,1) : 연이율이므로 12로 나눠 월이율로 맞춥니다.

5*12 : 기간이 년 단위이므로 12를 곱해 월로 맞춥니다.

-D3 : 매월 불입할 금액으로 결과값이 양수로 나오도록 음수로 입력합니다.

✡내 생각 : 앞으로 입금을 해야할 금액이므로, 결과가 음수로 나오므로, -를 곱해야 양수로 나온다.

기준이 미래이므로, 앞으로 입금해야할 금액이므로 음수로 나온다.

★ ROUNDUP 함수 추가 설명

☃ ROUNDDOWN, ROUNDUP / 암기요법

단, 일의 자리 위치를 0으로 잡고 해석하면 쉽다. 해당 자리까지는 표시하되,

표시하는 숫자 뒤에서 up은 무조건 반올림해주고, down은 무조건 내림하여 0이 됩니다.

형식) ROUNDDOWN(수치, 자릿수) : 무조건 해당 자릿수 뒤는 버린다.

해당 자릿수는 나타내주되, down은 해당 자릿수 뒤에서 내림합니다.

-소수점 이하

78325.67429 =ROUNDDOWN(A1,4) 78325.67420

78325.67429 =ROUNDDOWN(A1,3) 78325.67400

해당 자릿수는 나타내주되, down 함수이므로, 해당 자릿수 뒤는 내림하여 0으로 처리한다.

78325.67429 =ROUNDDOWN(A1,-1) 78320

-1위치까지는 보여주되, down이므로 뒤는 버린다.

78325.67429 =ROUNDDOWN(A1,-2) 78300

-2위치까지는 보여주되, down이므로 뒤는 버린다.

ROUNDUP(수치, 자릿수) : 무조건 해당 자릿수를 올린다.

78325.67429 =ROUNDUP(A1,4) 78325.6743

4위치까지는 보여주되, up 함수이므로 해당 자릿수 뒤에서 무조건 반올림 업해준다.

78325.67429 =ROUNDUP(A1,3) 78325.67500

3위치까지는 보여주되, up 함수 이므로 해당 자릿수 뒤에서 무조건 반올림 업해준다.

78325.67429 =ROUNDUP(A1,-1) 78330

-1위치인 2 까지는 보여주되, up 함수이므로 해당 자릿수 뒤에서 무조건 반올림 업해준다.

78325.67429 =ROUNDUP(A1,-2) 78400

-2위치인 3 까지는 보여주되, up함수이므로 해당 자릿수 뒤에서 무조건 반올림 업해준다.

● FV함수 설명

★ FV 함수 : 재무함수 중 하나이다. 고정 이자율을 기반으로 투자의 미래 가치를 계산합니다.

FV를 정기적인 납입액 또는 단일 일괄 지불에 사용할 수 있습니다.

구문

FV(rate,nper,pmt,[pv],[type])

FV의 인수에 대한 자세한 설명과 연부금 함수에 대한 자세한 내용은 PV를 참조하세요.

FV 함수 구문에는 다음과 같은 인수가 사용됩니다.

• rate    필수 요소입니다. 기간별 이자율입니다.

• nper    필수 요소입니다. 총 납입 기간 수입니다.

• pmt    필수 요소입니다. 각 기간의 납입액으로서 전 기간 동안 일정합니다. 일반적으로 pmt에는 기타 비용과 세금을 제외한 원금과 이자가 포함됩니다. pmt를 생략할 경우 pv 인수를 반드시 지정해야 합니다.

• pv    선택 요소입니다. 일련의 미래 지급액에 상응하는 현재 가치의 개략적인 합계입니다. pv를 생략하면 0으로 간주되며 이 경우 pmt 인수를 반드시 포함해야 합니다.

• type    선택 요소입니다. 납입 시점을 나타내는 숫자로서 0 또는 1입니다. 생략하면 0으로 간주됩니다.

주의

• rate와 nper를 지정할 때는 동일한 단위를 사용해야 합니다. 연 이자율 12%의 4년 만기 대출금을 매월 상환한다면 rate에 대해 12%/12, nper에 대해 4*12를 사용합니다. 대출금을 매년 상환한다면 rate에 대해 12%, nper에 대해 4를 사용합니다.

모든 인수에 대해 저축금과 같이 지불하는 금액은 음수로 표시합니다. 배당금과 같이 받을 금액은 양수로 표시합니다.