Ch08_함수에서 논리연산수행(3)_텍스트함수,재무함수

Section 05. 텍스트 함수

앞 절에서는 수학·통계 함수를 이용하여 판매금액을 집계하고 조건에 맞는 데이터를 분석하였다. 이번 절에서는 문자열에서 필요한 정보를 추출하고, 문자열의 형태를 정리하며, 여러 데이터를 하나의 문자열로 결합하는 텍스트 함수를 학습한다.

---

5.1 LEFT 함수 ― 왼쪽에서 문자 추출하기

LEFT 함수는 문자열의 왼쪽 첫 문자부터 지정한 개수만큼 문자를 추출한다.

 

=LEFT(text,[num_chars])

text는 원본 문자열이며 num_chars는 왼쪽에서 가져올 문자 수이다.

🧪 실습 01. 주문코드에서 지역코드 추출하기

A5에는 다음 주문코드가 있다.

DJN-26001

D5에 다음 수식을 입력한다.

=LEFT(A5,3)

결과는 다음과 같다.

DJN

수식의 처리 과정은 간단하다.

DJN-26001 → 왼쪽 3문자 → DJN

---

5.2 RIGHT 함수 ― 오른쪽에서 문자 추출하기

RIGHT 함수는 문자열의 오른쪽 끝에서 지정한 개수만큼 문자를 가져온다.

=RIGHT(text,[num_chars])

🧪 실습 02. 주문코드에서 일련번호 추출하기

현재 주문코드는 다음과 같은 구조를 갖는다.

DJN-26001
│   │ └─ 일련번호
│   └── 연도
└────── 지역코드

E5에 다음 수식을 입력한다.

=RIGHT(A5,3)

결과는

001

 

Expert Point

RIGHT의 결과인 "001"은 원본이 텍스트이므로 그대로 텍스트로 취급될 수 있다. 코드와 실제 숫자는 용도가 다르므로 코드를 무조건 숫자로 변환할 필요는 없다. 001을 상품번호나 주문번호로 사용한다면 앞의 0도 의미 있는 데이터이다.

---

5.3 MID 함수 ― 문자열의 중간 부분 추출하기

MID는 문자열의 특정 위치부터 원하는 문자 수만큼 추출한다.

 

=MID(text,start_num,num_chars)

인수	의미
text	원본 문자열
start_num	추출을 시작할 위치
num_chars	가져올 문자 수

 

🧪 실습 03. 주문코드에서 연도 추출하기

A5의 주문코드는

DJN-26001

이다.

문자의 위치를 세어 보면 다음과 같다.

D J N - 2 6 0 0 1
1 2 3 4 5 6 7 8 9

26은 5번째 문자에서 시작하여 2개의 문자이다. 따라서 F5에 입력한다.

=MID(A5,5,2)

결과는

26

이다. 이처럼 MID는 고정된 형식의 코드에서 중간 정보를 분리할 때 매우 유용하다.

---

5.4 LEN 함수 ― 문자열의 길이 검사하기

LEN 함수는 문자열에 포함된 문자의 개수를 반환한다.

=LEN(text)

🧪 실습 04. 주문코드의 문자 수 확인하기

G5에 다음과 같이 입력한다.

=LEN(A5)

DJN-26001은 총 9문자이므로 결과는

9

이다. LEN은 단순히 문자 수를 세는 것뿐 아니라 데이터의 형식이 올바른지 검사할 때 활용할 수 있다.

예를 들어 주문코드는 반드시 9문자여야 한다고 가정한다.

=IF(LEN(A5)=9,"정상","확인필요")

---

5.5 TRIM 함수 ― 불필요한 공백 정리하기

B열의 담당자 원본 데이터에는 외부 시스템에서 가져온 것처럼 불필요한 공백이 포함되어 있다고 가정한다.

TRIM 함수는 텍스트의 불필요한 공백을 정리할 때 사용한다.

=TRIM(text)

🧪 실습 05. 담당자 이름 정리하기

H5에 다음과 같이 입력한다.

=TRIM(B5)

예를 들어 원본 데이터가

"   임가은   "

이라면 결과는

"임가은"

으로 정리된다.

주의: TRIM은 일반적으로 단어 사이의 필요한 공백 하나는 유지하면서 불필요한 공백을 제거한다. 외부 시스템에서 가져온 데이터에는 일반 공백 외의 특수 공백 문자가 포함될 수도 있으므로 모든 종류의 비표준 공백이 TRIM 하나로 제거된다고 단정해서는 안 된다.

---

5.6 UPPER 함수 ― 영문을 대문자로 변환하기

C열에는 다음과 같이 영문지역이 소문자로 입력되어 있다.

daejeon
daegu
ulsan
seoul
busan

UPPER 함수는 영문자를 대문자로 변환한다.

=UPPER(text)

🧪 실습 06

I5에 입력한다.

=UPPER(C5)

결과:

daejeon → DAEJEON

아래쪽으로 자동 채우면

DAEGU
ULSAN
SEOUL
BUSAN

과 같이 변환된다.

---

5.7 LOWER 함수 ― 영문을 소문자로 변환하기

LOWER는 UPPER와 반대로 영문자를 소문자로 변환한다.

=LOWER(text)

🧪 실습 07

J5에 입력한다.

=LOWER(I5)

결과:

DAEJEON → daejeon

---

5.8 PROPER 함수 ― 영단어의 첫 글자를 대문자로

PROPER는 각 영단어의 첫 글자를 대문자로, 나머지 문자를 소문자로 변환한다.

=PROPER(text)

현재 시트에 K열 PROPER를 추가하여 실습하여 보자.

🧪 실습 08. 영문지역을 고유명사 형태로 변경하기

K5에 입력한다.

=PROPER(C5)

결과는 다음과 같다.

daejeon → Daejeon
daegu   → Daegu
ulsan   → Ulsan
seoul   → Seoul
busan   → Busan

지역명을 영문으로 출력하거나 보고서용 텍스트를 만들 때 UPPER, LOWER, PROPER 가운데 목적에 맞는 함수를 선택할 수 있다.

 

---

5.9 CONCAT 함수 ― 여러 문자열 연결하기

CONCAT 함수는 여러 셀이나 문자열을 하나의 문자열로 연결한다.

=CONCAT(text1,[text2],...)

현재 시트에 L열 CONCAT를 추가하여 실습한다.

🧪 실습 09. 담당자와 지역명 결합하기

담당자와 영문지역을 하나의 문자열로 결합하여 보자.

=CONCAT(TRIM(B5)," / ",PROPER(C5))

결과:

임가은 / Daejeon

여기에는 세 가지 텍스트 함수가 사용되었다.

CONCAT과 & 연산자

문자열은 &를 이용하여 연결할 수도 있다.

=TRIM(B5)&" / "&PROPER(C5)

결과는 동일하다.

임가은 / Daejeon

짧은 문자열을 연결할 때에는 &가 편리하고, 여러 텍스트나 범위를 연결하는 경우에는 CONCAT을 사용할 수 있다. 다만 CONCAT은 구분 기호를 자동으로 삽입하지 않는다. 데이터 사이에 쉼표나 /, - 등을 넣으려면 직접 지정하여야 한다.

---

Section 06. 재무 함수

재무 함수(Financial Functions)는 대출, 저축, 투자와 같이 시간에 따라 돈의 가치가 달라지는 문제를 계산하기 위한 함수이다. 대출금을 매월 얼마씩 갚아야 하는지, 일정 금액을 저축하면 미래에 얼마가 되는지, 매월 상환하는 금액 중 원금과 이자가 각각 얼마인지를 계산할 때 활용한다.

재무 함수에서는 특히 이율과 기간의 단위를 일치시키는 것이 중요하다. 연이율이 주어졌더라도 매월 납입하는 문제라면 월이율로 변환하여야 하며, 기간 역시 연수가 아닌 전체 납입 개월 수로 계산하여야 한다.

MOS Excel Expert에서는 재무 함수를 지나치게 많이 다루기보다 대출·투자에서 금리(rate), 기간(nper), 현재가치(pv), 미래가치(fv), 정기지급액(pmt)의 관계를 이해하는 것이 중요합니다. Microsoft는 MO-211의 Expert 통합 문서 사례로 상환표(amortization tables)를 명시하고 있으며, 고급 수식과 매크로 작성도 주요 평가 영역에 포함하고 있습니다.

핵심 재무 함수

함수	기능	핵심 활용
PMT	정기 상환액 계산	대출의 월 상환액
PV	현재가치 계산	미래 현금흐름의 현재 가치
FV	미래가치 계산	적금·투자의 미래 금액
NPER	기간 계산	상환에 필요한 개월 수
RATE	기간당 이율 계산	대출·투자의 이율
IPMT	특정 기간의 이자	상환표의 이자 부분
PPMT	특정 기간의 원금	상환표의 원금 부분

가장 중요한 원칙: 기간 단위를 맞춘다

예제에서는 다음 조건을 사용하였습니다.

대출원금 30,000,000원
연이율 4.8%
상환기간 5년
매월 말 상환

월 단위로 상환하므로 연이율과 기간도 월 단위로 변환하여야 합니다.

연이율 → 4.8%/12
기간   → 5*12

이 원칙이 재무 함수에서 가장 중요합니다.

---

1. PMT 함수 ― 정기 상환액 계산

PMT(Payment) 함수는 일정한 이자율을 적용하여 대출금을 일정 기간 동안 나누어 상환할 때 매회 지급하여야 하는 금액을 계산한다. 주택담보대출이나 학자금대출처럼 매월 일정한 금액을 상환하는 경우에 활용할 수 있다.

 

=PMT(rate,nper,pv,[fv],[type])

 

rate	기간당 이자율
nper	전체 납입 횟수
pv	현재가치, 즉 대출원금
fv	마지막 지급 후 원하는 잔액
type	0: 기간 말 지급, 1: 기간 초 지급

🧪 예제: 3,000만 원을 연 4.8% 이율로 5년 동안 매월 상환한다.

=-PMT(4.8%/12,5*12,30000000)

핵심: 월 단위 상환이므로 4.8%/12, 기간은 5*12로 계산한다.

---

2. PV 함수 ― 현재가치 계산

PV(Present Value) 함수는 미래에 지급하거나 받을 일정한 금액을 현재 시점의 가치로 환산한다. 예를 들어 앞으로 5년 동안 매월 60만 원을 지급하여야 한다면, 이러한 미래 지급액들이 현재 얼마의 가치에 해당하는지를 계산할 수 있다.

=PV(rate,nper,pmt,[fv],[type])

🧪 예제

=PV(4.8%/12,5*12,-600000,0)

여기서 -600000은 매월 지급하는 금액, 즉 현금 유출을 의미한다.

PV = 미래의 돈을 현재 가치로 계산

---

3. FV 함수 ― 미래가치 계산

FV(Future Value) 함수는 현재부터 일정 금액을 정기적으로 저축하거나 투자할 때 미래에 얼마가 되는지 계산한다. 적금이나 장기 투자 결과를 예측할 때 활용하기 좋은 함수이다.

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

🧪 예제

연 4.8%의 수익률을 가정하여 매월 50만 원씩 5년간 적립한다.

=FV(4.8%/12,5*12,-500000,0)

PV = 현재 가치 / FV = 미래 가치

두 함수의 관계를 함께 이해하여 두는 것이 좋다.

---

4. NPER 함수 ― 필요한 기간 계산

NPER(Number of Periods) 함수는 일정한 이율과 납입금액이 주어졌을 때 목표를 달성하는 데 필요한 기간을 계산한다.

=NPER(rate,pmt,pv,[fv],[type])

🧪 예제: 3,000만 원의 대출금을 연 4.8% 이율에서 매월 60만 원씩 상환한다.

=NPER(4.8%/12,-600000,30000000,0)

결과가 약 55.9라면 약 55.9개월이 필요하다는 의미이다.

PMT는 '얼마씩?', NPER는 '얼마 동안?'을 계산한다.

---

5. RATE 함수 ― 이자율 계산

RATE 함수는 전체 기간, 정기 지급액, 현재가치 등이 주어졌을 때 기간당 이자율을 역으로 계산한다.

=RATE(nper,pmt,pv,[fv],[type],[guess])

예를 들어 3,000만 원을 60개월 동안 매월 563,000원씩 상환하는 조건이라면 다음과 같이 계산할 수 있다.

=RATE(60,-563000,30000000,0)

월 단위 자료를 사용하였다면 결과 역시 월이율이라는 점에 주의한다.

---

6. IPMT 함수 ― 특정 회차의 이자 계산

대출금을 매월 상환할 때 상환금 전부가 원금을 갚는 데 사용되는 것은 아니다. 일부는 원금이고 나머지는 이자이다.

IPMT(Interest Payment)는 특정 회차의 상환금 가운데 이자에 해당하는 금액을 계산한다.

=IPMT(rate,per,nper,pv,[fv],[type])

첫 달의 이자를 계산하면 다음과 같다.

=-IPMT(4.8%/12,1,5*12,30000000)

여기서 1은 첫 번째 상환 회차를 의미한다.

---

7. PPMT 함수 ― 특정 회차의 원금 계산

PPMT(Principal Payment) 함수는 특정 회차의 상환금 가운데 실제로 원금을 갚는 데 사용되는 금액을 계산한다.

=PPMT(rate,per,nper,pv,[fv],[type])

첫 달의 원금 상환액은 다음과 같이 계산한다.

=-PPMT(4.8%/12,1,5*12,30000000)

따라서 PMT, IPMT, PPMT는 서로 연결하여 이해하여야 한다.

월 상환액 = 원금 상환액 + 이자 상환액 즉, 

PMT
 ├─ PPMT : 원금 부분
 └─ IPMT : 이자 부분

대출 상환표를 작성할 때 핵심이 되는 관계이다. 그리고 현금의 방향에 따라 지급액과 수령액의 부호가 반대가 된다

🔑 재무 함수 한눈에 정리

함수	질문으로 이해하기	주요 활용
PMT	매월 얼마씩 갚아야 하는가?	대출 상환액
PV	미래의 돈은 현재 얼마의 가치인가?	현재가치
FV	저축하면 미래에 얼마가 되는가?	적금·투자
NPER	얼마 동안 납입해야 하는가?	상환기간
RATE	적용된 이자율은 얼마인가?	금리 계산
IPMT	이번 회차의 이자는 얼마인가?	상환 이자
PPMT	이번 회차의 원금은 얼마인가?	원금 상환