블로그 이미지
주로 인재개발원 등의 사이버학습을 정리, 요약하는 상시학습 블로그입니다. 깨비형
« 2017/10 »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

Archive»

체험 블로그 마케팅 서비스 OLPOST

Category»

Notice»

Statistics Graph

 
 

1. Workbook, Worksheet 개체


1) 엑셀 개체의 종류

○ 엑셀 VBA에서 사용 가능 한 개체 : Workbook, Worksheet, Range 등이 대표적

▷ Workbook 개체 : 파일 열기, 파일 닫기, 파일 저장

▷ Range 개체 : 셀 선택하기, 셀 값 속성

▷ Worksheet 개체 : 워크시트 만들기, 워크시트 삭제하기, 워크시트 선택하기, 워크시트 보호하기


2) Workbook 개체의 주요 메서드

 메서드

 기능

 사용 예

 Activate

 통합문서 활성화

 Workbooks(“연습.xlsm”).Activate

 Open

 통합문서 열기

 Workbooks.Open Filename:=”C:\연습.xlsm”


▶ 그 외 주요 메서드

 메서드

기능 

사용 예 

Close 

통합 문서 닫기 

 Workbooks(“01_01_before.xlsm”).Close

 ☞‘01_01_before.xlsm’ 통합 문서를 닫습니다.

 PrintOut

통합 문서 인쇄 

 Workbooks("01_01_before.xlsm").PrintOut

☞ ‘01_01_before.xlsm’ 파일을 인쇄합니다.

 PrintPreview

통합 문서 인쇄

미리보기 

 Workbooks("01_01_before.xlsm").PrintPreview

☞ ‘01_01_before.xlsm’ 파일을 인쇄 미리 보기합니다.

Save 

통합 문서 저장 

 Workbooks("01_01_before.xlsm").Save

☞ ‘01_01_before.xlsm’ 파일을 저장합니다.

Save As 

통합 문서

다른 이름으로 저장 

 Workbooks("01_01_before.xlsm").SaveAs "C:\01_01_after.xlsm", Password:="1111"

☞ C 드라이브에 파일명‘01_01_after.xlsm’로 암호 ‘1111’을 설정, 저장 경로가 없으면 현재 문서가 저장된 같은 경로에 저장


3) Worksheet 개체의 주요 메서드

 메서드

 기능

 사용 예

Add

 워크시트 삽입

 Worksheets.Add

Select

 워크시트 선택

 Worksheets(“Sheet1”).Select


▶ 그 외 주요 메서드

 메서드

기능 

사용 예 

Add

새 워크시트 삽입

 Worksheets.Add

☞ 워크시트의 맨 앞에 새 워크시트를 삽입합니다.

 Worksheets.Add After:=Worksheets(Worksheets.Count)

☞ 워크시트의 맨 뒤에 새 워크시트를 삽입합니다.

Activate

여러 개의 워크시트 중

지정한 워크시트 활성화 

 Worksheets(1).Activate

☞ 첫 번째 워크시트를 활성화합니다.

Delete 

워크시트 삭제

 Worksheets(“Sheet1”).Delete

☞ 워크시트를 삭제합니다.

Protect

워크시트 보호 

 Worksheets(“Sheet1”).Protect Password:=”1111”

☞ 암호 ‘1111’을 설정하여 워크시트를 보호합니다.

UnProtect

워크시트 보호 해제 

Worksheets(“Sheet1”).Unprotect

☞ 워크시트 보호를 해제합니다.


▶ Worksheet 개체의 주요 속성

속성

 기능

 사용 예

Name

 워크시트 이름 반환

 Worksheets(1).Name

☞ 첫 번째 워크시트의 이름을 반환합니다.

 Worksheets(1).Name = “1월”

☞ 첫 번째 워크시트의 이름을 ‘1월’로 지정합니다.

Visible

워크시트 표시 여부 반환

 Worksheets(“Sheet1”).Visible = True

☞ Sheet1 워크시트를 표시합니다.

Worksheets(“Sheet1”).Visible = False

☞ Sheet1 워크시트를 숨깁니다.


2. Range 개체 


1) Range 개체

○ 워크시트에 포함된 각각의 셀을 다룰 때 사용하는 개체


① 주요 속성

 속성

 기능

 사용 예

 Offset

 지정한 행/열 번째 셀 참조

 Range(“A1”).Offset(1,2).Select

  CurrentRegion

 연속된 데이터 범위를 참조

 Range(“A1”).CurrentRegion.Select


▶ 그 외 주요 속성

 속성

기능 

사용 예 

Address

 Range 개체의 셀 주소 반환

Range(“A1”).Address

☞ [A1]셀의 셀 주소를 반환합니다. ($A$1) 

Cells

 지정한 행, 열 위치의 셀을 반환

 Cells(1, 1).Select

☞ 1행, 1열 즉, [A1]셀을 반환합니다.

Columns

 열 참조

 Columns(1).Select

☞ 첫 번째 열 즉, A열을 참조합니다.

Rows

 행 참조

 Rows.Select

☞ 전체 행을 선택합니다.

EntireColumn

 셀 범위의 열 전체를 참조

 ActiveCell.EntireColumn.Select

☞ 활성 셀의 열 전체를 참조합니다.

EntireRow

 셀 범위의 행 전체를 참조

 ActiveCell.EntireRow.Select

☞ 활성 셀의 행 전체를 참조합니다.

ColumnWidth

 열의 너비를 반환

 ActiveCell.ColumnWidth = 30

☞ 활성 셀의 너비를 30 pt로 지정합니다.

End

End(xlUp), End(xlDown), End(xlToLeft), End(xlToRight)

 지정한 범위에서 특정 방향(상,하,좌,우)으로 연속된 데이터 범위의 끝 셀을 반환

 Range(“A1”).End(xlDown).Select

☞ [A1]셀에서 아래쪽 방향의 끝 셀을 선택합니다.

 Range(“A1”).End(xlToLeft).Select

☞ [A1]셀에서 오른쪽 방향의 끝 셀을 선택합니다.

Formula

 수식을 반환 받거나 변경

 Range(“C1”).Formula=”=A1+B1”

☞ [C1]셀에 ‘=A1+B1’ 수식을 지정합니다.

NumberFormat

 지정한 범위의 숫자 서식을 반환 받거나 변경

 Range(“A1:A10”).NumberFormat=”#,##0원”

☞ [A1:A10]셀 범위의 숫자 서식을 ‘#,##0원’으로 지정합니다.

Value

 지정한 셀의 값을 반환받거나 변경합니다.

 Range(“A1”).Value=10

☞ [A1]셀의 값을 “10”으로 지정합니다.

WrapText

 ‘텍스트 줄 바꿈’을 설정합니다.

 Range(“A1”).WrapText=True

☞ [A1]셀의 텍스트 줄 바꿈 옵션을 설정합니다.


② 주요 메서드

 메서드

 기능

 사용 예

Select

지정한 셀 선택

 Range(“A1”).Select

ClearFormat

셀 서식 지우기

 Range(“A1:D10”).ClearFormats


▶ 그 외 주요 메서드

메서드

기능

사용 예

Activate

셀 활성화

 Range(“A1”).Activate

☞ [A1]셀을 활성화합니다.

AutoFit

지정한 범위에 있는 열의 너비 또는 행의 높이를 자동으로 조정

 Column(“1:5”).AutoFit

☞ [A:E]열의 너비를 자동으로 조정합니다.

Clear

셀 범위의 값, 수식, 서식을 모두 지움

 Range(“A1:D10”).Clear

☞ [A1:D10]셀 범위의 값, 수식, 서식을 모두 지웁니다.

ClearContents

셀 범위의 값, 수식을 지움

 Range(“A1:D10”).ClearContents

☞ [A1:D10]셀 범위의 값, 수식을 지웁니다.

Copy

셀 복사

 Range(“A1”).Copy

☞ [A1]셀을 복사합니다.

Cut

셀 잘라내기

 Range(“A1”).Cut

☞ [A1]셀을 잘라내기합니다.

Paste

클립보드의 값붙여넣기

 Range("A6").Select ActiveSheet.Paste

☞ 복사한 데이터를 활성시트의 [A6]셀에 붙여넣기합니다.

PasteSpecial

‘선택하여 붙여넣기’ 기능을 이용하여 클립보드의 값 붙여넣기

 Selection.PasteSpecial Transpose:=True

☞ 복사한 선택 영역을 행/열을 바꾸어 붙여넣기 합니다.

Find

지정한 범위에서 찾을 조건에 만족하는 첫 번째 셀 반환

 Range("a3").CurrentRegion.Column(1).Find("최소라").Select

☞ [A3]셀부터 연속된 셀 범위의 1번째 열에서 ‘최소라'를 찾아서 선택합니다.



3. Application 개체


1) Application 개체

○ 엑셀의 모든 개체 중 최상위 개체이면서 엑셀 자신을 의미


2) Application 개체의 주요 속성

속성

기능

사용 예

 StatusBar

 상태표시줄에 메시지 설정

 Application.StatusBar=”매크로 실행 중”

 WorkSheetFunction

 엑셀 함수 사용

Application.WorksheetFunction.Sum(Range(“A1:A10”))


▶ 그 외 주요 속성

속성

설명

사용 예 

CutCopyMode

복사나 잘라내기 상태 해제 

Application.CutCopyMode=False

☞ 복사나 잘라내기 상태를 해제합니다. 

DisplayAlerts

시스템 메시지의 표시 여부 설정 

Application.DisplayAlerts=False

☞ 시스템 메시지를 표시하지 않습니다.

메시지 박스에 응답 단추가 있는 경우 각 대화상자마다 기본으로 선택된 단추 항목을 자동으로 선택합니다. 

예를 들어, 삭제 여부를 묻는 메시지 박스가 표시된 경우 [예] 단추가 자동으로 선택됩니다. 

ScreenUpdating

화면 업데이트 여부 설정 

Application.ScreenUpdating=False

☞ 화면 업데이트 설정을 해제합니다.

(화면 업데이트 : 코드 실행에 따른 화면 변화 표시) 


▶ Application의 단축 접근자

속성

설명

 ActiveCell

 활성 셀 반환

 ActiveSheet

 활성 시트 반환

 ThisWorkbook

 현재 Workbook 개체 반환

 ActiveWorkbook

 활성 통합 문서 반환

 Selection

 선택된 개체 반환


3) Application 개체의 주요 메서드

 매서드

설명 

사용 예 

Quit 

엑셀 프로그램 종료 

Application.Quit

☞ 엑셀 프로그램을 종료합니다. 

InputBox 

Type에서 지정한 데이터를 입력할 수 있게 하는 입력 상자 표시

<Type>

0: 수식, 1: 숫자, 2: 텍스트,

3: 논리값(True, False),

8: Range 개체와 같은 셀 참조,

16: #N/A와 같은 오류 값, 

64: 값의 배열 

Application.InputBox(“사번을 입력하세요.”,”사원 검색”,Type:=2)

☞ 텍스트를 입력하는 입력 상자에 메시지 ‘사번을 입력하세요.’ 제목 표시줄에 ‘사원 검색’을 표시합니다.






  


저작자 표시 동일 조건 변경 허락
신고

1. 변수 선언 및 데이터 형식


1) 변수 및 상수

○ 변수 :  프로그램 실행 중 변하는 값을 저장할 수 있는 컴퓨터의 메모리 공간

○ 상수 :  변수처럼 변하는 값이 아닌 항상 같은 값을 저장하는 공간


2) 변수(상수) 선언

○ 변수나 상수를 사용하려면 미리 코딩 창의 Sub Procedure나 모듈 상단에 사용할 변수나 상수를 선언해 놓아야 함

○ 선언 형식

키워드 변수(상수)명 As 데이터 형식

- 키워드 : Dim, Public, Private 등으로 지정 가능

- 변수(상수명) : 첫 문자는 반드시 영어 또는 한글(공백, 마침표, !, @, &, $, #, % 특수문자 사용불가)

-Ÿ 데이터 형식 : 생략 시, 모든 형식의 데이터를 저장할 수 있는 Variant 데이터 형식이 자동으로 지정


3) 변수 선언에 필요한 키워드

① Dim

- Dim은 변수를 프로시저 내부나 모듈부에 선언 가능

- 선언된 위치에 따라 해당 변수를 사용할 수 있는 범위가 달라짐

② Private, Public

- 모듈부에만 선언 가능

- Private : 해당 모듈의 전체 프로시저에서 사용 가능하나, 다른 모듈에서는 사용불가

- Public : 전체 모듈의 모든 프로시저에서 사용 가능


※ 다양한 데이터 형식의 의미


 데이터 형식

크기
(Byte) 

범위 

논리

Boolean

2

 TRUE, False

숫자

Byte 

0~255 

Integer

-32,768~32,767 

Long

-2,147,438,648~2,147,438,648 

Currency

 -922,337,203,685,477.5808~922,337,203,685,477.5808

Decimal

12 

+/-79,228,162,514,264,337,593,543,950,335 (소수점 이하가 없는 경우)

+/-7.9228162514264337593543950335 (소수점 아래 28자리)

+/-0.0000000000000000000000000001(0이하 가장 작은 수)

Single

 -3.402823E38~-1.401298E-45(음수의 경우)

1.401298E-45~3.402823E38(양수의 경우)

Double

-1.79769313486232E308~-4.94065645841247E-324(음수)

4.94065645841247E-324~1.79769313486232E308(양수) 

날짜 

Date

100년 1월 1일 ~ 9999년 12월 31일 

문자 

String(가변 길이)

 10

0 ~ 20억 개 

String(고정 길이)

 문자열

길이

1~65536개 

개체 

Object

 

모두 

Variant(문자열)

22 

String(가변 길이) 데이터 형식과 동일 

Variant(숫자)

16 

Double 형 범위 내의 모든 숫자 


※ 변수 선언 예


 Dim i As Integer  ‘i’라는 이름의 정수를 저장하는 변수 선언
 Private 계약일 As Date  해당 모듈의 모든 프로시저에서 사용할 수 있는 ‘계약일’ 이라는 이름의 날짜를 저장하는 변수 선언
 Public 매출액 As Double  모든 모듈의 프로시저에서 사용할 수 있는 ‘매출액’이라는 이름의 숫자를 저장하는 변수 선언 
 Const 할인율 As Single  ‘할인율’ 이라는 이름의 숫자를 저장하는 상수 선언
 Dim 교통비  Dim 교통비 As Variant 와 같은 의미로 모든 데이터 형식을 저장할 수 있는 ‘교통비’라는 이름의 변수 선언



4) 변수와 상수에 값 할당


① 변수 값 할당 형식

변수 명 = 할당할 값


▷ 예시

Dim i AS Integer

i =1

i = Activesheet.Range(“A1”).Value


② 상수 값 할당 형식

Const 상수 명 AS Single = 할당할 값


5) 개체 변수

○ 변수에 값이 아닌 개체(셀 범위, 워크시트, 차트 등)를 할당


① 개체 변수 값 할당 형식

Dim 변수명 As 개체

Set 변수명 = 개체


▷ 예시(1)

Dim 매출실적 As Range

Set 매출실적 = Activesheet.Range(“A1:E50”)

▷ 예시(2)

Dim Sh1 As Worksheet

Set Sh1 =Worksheets(“실적관리”)


6) 변수의 수명

○ 프로시저 실행 시작 → 초기화, 프로시저 종료 → 값을 상실함

○ Static 키워드 : 프로시저를 실행해도 변수 값이 초기화되지 않음


7) 시스템 상수 (내장 상수)

○ 사용자가 선언하여 사용하는 상수 이외에 VBA와 엑셀에 미리 정의되어 있는 상수

○ VBA 상수 : 상수 앞에 ‘vb', 엑셀 개체 상수 :‘xl’가 표시됨

▷ 예) VBA에서 파랑색 사용 : vbBlue, 왼쪽 맞춤 지정 : xlLeft


 Msgbox의 구성 인수

▷ Prompt : 메시지 박스에 표시할 메시지입력

 Button Type : 메시지 박스에 표시할 버튼의 종류

▷ Title : 메시지 박스의 제목 표시줄에 표시할 텍스트

▷ Help File : 도움말 단추를 누를 때 표시할 도움말 파일 세부주소를 명시하는 곳

 Context : 도움말 파일의 연결할 항목 내용 번호


② Button Type 에 사용되는 상수 값



2. 조건문


1) 조건문 이란?

 지정한 조건에 만족할 때와 그렇지 않을 때, 반환하는 결과를 다르게 설정할 때 사용하는 명령문

예) 조건 : [지역] = ‘서울’

        참 : 교통비 ‘5만원’ 지급

     거짓 : 교통비 ‘10만원’ 지급


① IF문

 IF 문은 가장 기본적인 조건문으로 가장 많이 사용되며, 3가지 형식으로 사용 가능


가. 조건이 만족할 때만 실행

IF 조건식 Then

    조건식을 판단한 결과가 TRUE일 때 실행할 실행문

End If


예) 평균이 80 이상이면 ‘합격’ 출력

 Sub 합격여부1()

Sheet("조건문1").Select

If Range("e4").Valye >= 80 Then

Range("f4").Value = "합격"

End If

End Sub


나. 조건에 만족할 때와 조건에 만족하지 않을 때 다르게 실행

IF 조건식 Then

    조건식을 판단한 결과가 TRUE일 때 실행할 실행문

Else

    조건식을 판단한 결과가 FALSE일 때 실행할 실행문

End If


예) 평균이 80 이상이면 ‘합격’ 그렇지 않으면 '불합격' 출력

Sub 합격여부2()

Sheet("조건문1").Select

If Range("e4").Valye >= 80 Then

Range("f4").Value = "합격"

Else

Range("f4").Value = "불합격"

End If

End Sub



다. 2개 이상의 조건을 판단하여 각 조건의 판단 결과에 따라 다른 결과를 실행

IF 조건식1 Then

    조건식1의 판단 결과가 TRUE일 때 실행할 실행문

ElseIf 조건식2 Then

    조건식2의 판단 결과가 TRUE일 때 실행할 실행문

[ElseIf 문 조건식 만큼 반복]

Else

    지정한 조건식 모두를 만족하지 않았을 때 실행할 실행문

End If


예) 평균이 90 이상이면 ‘A’, 80점 이상이면, ‘B’, 70점 이상이면, ‘C’, 60점 이상이면, ‘D’, 나머지는 ‘F’ 표시


 Sub 학점1()

Sheet("조건문1").Select

If Range("e4").Valye >=90 Then

Range("g4").Value = "A"

ElseIf Range("e4").Valye >=80 Then

Range("g4").Value = "B"

ElseIf Range("e4").Valye >=70 Then

Range("g4").Value = "C"

ElseIf Range("e4").Valye >=60 Then

Range("g4").Value = "D"

Else

Range("g4").Value = "F"

End If

End Sub


※  IF 문의 조건 연산자

○ IF 문에서 조건식을 지정할 때 And, Or, Not 연산자를 사용 가능하다. 

 AND : 지정한 조건을 모두 만족하는 경우를 판단함

 OR : 지정한 조건 중, 하나만 만족해도 되는 경우를 판단함

 NOT : 지정한 조건 아닌 경우를 판단함


 Select Case문

▷ 조건의 판단 결과에 따라 다른 동작을 수행하는 조건문 

 여러 개의 조건을 처리해야 하는 경우 더 적합함


가. Select Case 문의 형식

Select Case 식 (또는 변수)

    Case조건1

        조건1이 만족할 때 실행할 실행문

    Case조건2

        조건2가 만족할 때 실행할 실행문

    Case조건3

        조건3이 만족할 때 실행할 실행문

    [Case 조건 반복]

    Case Else

        지정한 조건이 모두 만족하지 않았을 때 실행할 실행문

End Select


예)  평균이 90 이상이면 ‘A’, 80점 이상이면, ‘B’, 70점 이상이면, ‘C’, 60점 이상이면, ‘D’, 나머지는 ‘F’ 표시


 Sub 학점2()

Sheet("조건문2").Select

Select Case Range("e4")

    Case Is >= 90

        Range("g4") = "A"

    Case Is >= 80

        Range("g4") = "B"

    Case Is >= 70

        Range("g4") = "C"

    Case Is >= 60

        Range("g4") = "D"

    Case Else

        Range("g4") = "F"

End Select

End Sub


나. 조건을 지정하는 다양한 방법

▶ 숫자 값의 범위를 조건으로 지정하는 경우

- To 키워드를 사용

Select Case Range(“A1”).Value

    Case 1 To 10

        Msgbox “1~10 사이 값입니다.”

    Case11 To 20

        Msgbox “11~20 사이 값입니다.”

    CaseElse

        Msgbox “0~20 이외의 값입니다.”

End Select

▶ OR 조건을 지정하는 경우

- 조건에 쉼표(,) 사용

Select Case Range(“A1”).Value

    Case 1, 3, 5

        Msgbox “1, 3, 5 중 하나의 값입니다.”

Case 2, 4, 6

        Msgbox “2, 4, 6 중 하나의 값입니다.”

Case Else

        Msgbox “1,2,3,4,5,6 이외의 값입니다.”

End Select

▶ 비교 연산자를 사용하는 경우

- 연산자 앞에 Is 사용

Select Case Range(“A1”).Value

    Case Is >10

        Msgbox “10보다 큰 값입니다.”

Case Is <30

        Msgbox “30보다 작은 값입니다.”

Case Else

End Select



3. 반복문


1) 반복문이란?

○ 작성한 코드를 반복해서 실행할 때 사용하는 명령문

예) 합격 여부를 마지막 영역까지 반복 출력하고자 할 때


 For Next 문

▷ 지정한 횟수만큼  코드를 반복해서 실행하고자 할 때 사용

  

For 카운터변수 = 시작수 To 끝수 [Step 증감값]

        반복해서 실행할 실행문

Next [카운터변수]


예) 평균이 80 이상이면 ‘합격’, 그렇지 않으면 ‘불합격’

                          <코드1>                                                              <코드2>

Sub 합격여부1()

'For Next 문

 Dim i As Integer


 Sheet("반복문1").Select

 For i = 4 To 13 Step 1

 If Range("e" & i).Value >= 80 Then

 Range("f" & i).Value = "합격"

 Range("e" & i).Font.Color = vbBlue

 Else

 Range("f" & i).Value = "불합격"

 Range("e" & i).Font.Color = vbRed

 Enf If

 Next i

 End Sub

 

 Sub 합격여부1_1()

'For Next 문

 Dim i As Integer

 Dim rCnt As Integer


rCnt = Range("b4").CurrentRegion.Rows.Count +2

 Sheet("반복문1").Select

 For i = 4 To rCnt

 If Range("e" & i).Value >= 80 Then

 Range("f" & i).Value = "합격"

 Range("e" & i).Font.Color = vbBlue

 Else

 Range("f" & i).Value = "불합격"

 Range("e" & i).Font.Color = vbRed

 Enf If

 Next i

 End Sub


       For Each Next문

 셀 영역,워크시트, 여러 통합 문서처럼 개체들의 집합(컬렉션)의 개별 요소에 대한 반복 작업을 실행 해야 할 때 사용(예 : 워크시트를 반복하면서 각 시트명을 셀에 입력하는 경우, 선택한 범위의 각 셀을 순환하면서 원하는 셀 서식을 지정하는 경우)


For Each 개체 변수 In 컬렉션

        실행문

Next


③ Do Loop 문

 Do While Loop : 지정한 조건이 만족하는 동안 실행문을 반복

 Do Until Loop : 지정한 조건이 만족할 때까지 실행문을 반복


Do While 조건식

        실행문

Loop




저작자 표시 동일 조건 변경 허락
신고

1. VBA 구성요소 


  1) VBE(Visual Basic Editor)

○ VBE 란?

▷ Visual Basic Editor의 약자

▷ 엑셀을 자동화하기 위해 필요한 코드 개발 및 오류 수정 등의 작업을 진행할 수 있는 편집기

○ 실행 방법

▷ [개발 도구] 탭 → [코드] 그룹 → [Visual Basic] 아이콘 클릭 

▷ Alt + F11


  2) VBE 구성요소

 메뉴와 도구모음  VBE에서 사용할 수 있는 명령을 메뉴와 도구 모음으로 제공하는 영역
 프로젝트 탐색기  현재 열려있는 파일과 각 파일에 속해 있는 모든 항목(워크시트, 폼, 모듈 등)이 계층 구조로 표시되는 영역
 속성 창  [프로젝트 탐색기]에서 선택한 개체의 속성을 지정하는 곳
 코드 창  VBA 코드를 작성하는 곳
 직접 실행창  직접 실행 창은 간단한 실행 결과를 바로 확인할 때 사용하는 창
 ‘?1+2’를 입력하고 [Enter]키를 누르면 결과 3 표시


  3) VBE 환경 설정

▷ VB 편집기 사용자는 VB 편집기의 환경을 원하는 대로 설정 가능

(코드 창의 글꼴, 크기, 색상 설정, 코드 작성 시 표시되는 목록이나 설명의 표시 설정, 열리는 창 들의 도킹 여부 등)

○ 설정 방법

▷[도구] → [옵션] 메뉴를 선택, 각 탭에서 원하는 옵션 지정 가능



 탭 구분

 설명

 [편집기] 탭  직접 코드를 작성할 때 필요한 부가적인 정보에 대한 표시여부 설정
 [편집기 형식]   탭 코드 창에 나타나는 글자의 속성 지정
 [일반] 탭   일반적인 옵션 사항 설정  (기본값으로 사용하는 것이 일반적)
 [도킹] 탭   각 창의 도킹 여부 설정


  4) VBA 기본 구조

▷ 개체 : VBA 프로그램의 각 구성요소, 개체는 계층 구조를 가짐



① Application (응용 프로그램)

▷ 현재 실행 중인 프로그램인 엑셀 자체를 의미

▷ VBA 코드의 최상위 계층 의미


② Project

▷ 하나의 통합 문서에 작성되는 모든 VBA 코드 내용

▷ Workbook (통합문서), Worksheet, Module, 폼 등으로 구성

③ Module

▷ Procedure의 집합, 표준 모듈과 폼 모듈, 클래스 모듈로 구분됨


 표준 모듈  - 워크시트 모듈(Sheet로 표시되는 모듈), ThisWorkbook 모듈, 공용 모듈이 존재함 

 - 워크시트 모듈 : 워크시트마다 자동으로 각각 하나씩 만들어지고

    ThisWorkbook 모듈 : 통합 문서 즉, 엑셀 파일에 하나가 만들어 짐

 폼 모듈  - 사용자 정의 폼을 디자인하고, 사용자 정의 폼의 컨트롤에 이벤트 프로시저를 작성하는 모듈
 클래스 모듈  - 개체를 새롭게 정의해서 사용할 수 있도록 작성하는 모듈

 - 개체의 속성, 메서드, 이벤트를 정의하는 모듈


▶ 모듈 삽입 : Project에 표준 모듈을 삽입하려면 [삽입] → [모듈] 메뉴 활용


▶ 모듈 이름 정의 : 삽입된 모듈의 이름은 [속성] 창의 [이름] 속성에서 지정

④ Procedure

▷ 특정 작업을 실행하기 위해 모여진 실행문의 집합

▷ 실행 방법에 따라 Sub Procedure, Function Procedure, Property Procedure로 구분 됨.


 Sub Procedure  - 작성한 코드를 순차적으로 실행하는 Procedure 

 - 매크로 기록기를 사용하여 기록된 엑셀 작업을 순차적으로 실행하는 매크로도 Sub Procedure로 작성됨

 Function Procedure  - 엑셀의 함수 기능을 수행하는 Procedure 

 - 엑셀에서 제공하지 않는 함수를 직접 Function Procedure를 작성하여 워크시트에서 함수를 사용하듯이 사용 가능

 - Sub Procedure처럼 작성한 코드를 순자적으로 실행하지만, Sub Procedure와 다른 점은 실행문의 결과 값을 Function 이름에 반환


▶ Procedure 작성 방법

① 코드 창 임의의 위치에 클릭한 다음 [삽입] → [프로시저] 메뉴 선택


② 프로시저의 이름, 형식, 범위를 지정한 후, <확인> 단추 클릭


 - Public : 다른 모듈의 프로시저에서 해당 Procedure를 호출 가능

 - Private : 해당 프로시저가 속한 모듈 내의 다른 프로시저에서만 해당 프로시저를 호출 가능


⑤ User Form (사용자 정의 폼)

▷ 자료의 입출력을 효과적으로 하기 위한 양식(폼)



  5) Procedure의 구성요소

(1) 개체 (Object)

▷ 엑셀, 통합문서, 통합 문서를 구성하는 워크시트, 셀 등을 모두 개체로 인식

▷ 대표적 개체 : Application, Workbook, Worksheet, Range 등


(2) 컬렉션 (Collection)

▷ 개체들의 집합, 일반적으로 개체 이름에 복수형 ‘s’를 붙여 표현

[ 통합문서의 첫 번째 시트 지칭하기]

  

  방법 1 : Sheets(1), WorkSheets(1)

  방법 2 : Sheets(“Sheet1”), WorkSheets(“Sheet1”)


(3) 속성 (Property)

▷ 개체가 가지는 특성으로 셀의 주소, 글꼴, 글꼴 색, 워크시트의 이름 등이 모두 개체가 가지는 속성

▷ 개체와 속성 사이에는 ‘.(점)’을 찍어 구분

예) 첫 번째 워크시트의 이름을 의미하는 코드 : Sheet1.Name


▷ 개체와 속성 입력 방법 : 개체.속성 = 속성 값

 첫 번째 워크시트의 이름을 1월로 지정하는 코드 → Sheet1.Name = “1월”

  [A1]셀에 ‘10’을 입력하는 코드 → Range(“a1”).Value = 10


(4) 메서드 (Method)

▷ 개체가 실행할 수 있는 동작

▷ 워크시트 삽입, 삭제, 특정 셀을 선택, 내용 지우기 등의 동작이 메서드

▷ 개체와 메서드 사이에 ‘.(점)’으로 구분

예) 개체와 메서드 입력 예시

   워크시트 삽입하는 코드 → Sheets.Add

    [A1]셀 선택하는 코드 → Range(“A1”).Select


(5) 이벤트 (Event)

▷ 개체가 일으키는 사건

▷ 통합문서를 열거나 닫을 때, 워크시트가 활성화 될 때, 워크시트의 특정 셀이 변경될 때


  6) Sub Procedure 실행

○ [표준] 도구 모음의 [Sub/사용자 정의 폼 실행(F5)] 버튼을 클릭 / [F5]키 활용



2. 이벤트 프로시저 


  1) 이벤트 프로시저란?

○ 개체에 특정 이벤트가 발생할 때 실행되는 프로시저

예) 통합 문서가 열릴 때 특정 시트를 자동 활성화

특정 시트가 선택되면, 시트의 내용을 수정하지 못하도록 시트 보호


  2) 작성 방법

① 이벤트를 실행하는 개체 선택하고, 해당 개체가 발생시키는 이벤트 선택 



② [프로시저 목록]에서 원하는 이벤트 선택


- 삽입된 이벤트 프로시저에 해당 이벤트가 발생할 때 실행할 실행문 작성


  3) 개체 별 주요 이벤트

○ 통합 문서 주요 이벤트

 이벤트

 설명

 Activate  통합문서가 활성화될 때 발생
 BeforeClose  통합문서를 닫기 전에 발생
 BeforePrint  통합문서를 인쇄하기(미리보기 포함) 전에 발생
 BeforeSave  통합문서를 저장하기 전에 발생
 DeActivate  통합문서가 비 활성화될 때 발생
 NewSheet  통합문서에 새 워크시트를 삽입할 때 발생
 Open  통합 문서를 열 때 발생


○ 통합 문서 주요 이벤트 중 모든 워크시트에 공통으로 적용되는 이벤트

▷ 모든 워크시트에 공통으로 적용되는 이벤트를 작성하려면 통합 문서 이벤트 중 Sheet로 시작하는 이벤트를 사용

이벤트 

 설명

 SheetActivate  통합문서 내 워크시트를 선택할 때 발생
 SheetBeforeDoubleClick  통합문서 내 워크시트에서 더블클릭 할 때 발생
 SheetBeforeRightClick  통합문서 내 워크시트에서 마우스 오른쪽 버튼을 클릭할 때 발생
 SheetCalculate  통합문서 내 워크시트에서 계산이 되는 경우에 발생
 SheetChange  통합문서 내 워크시트에서 값이 수정될 때 발생
 SheetDeActivate  통합문서 내 워크시트가 비활성화 될 때 발생
 SheetSelectionChange  통합문서 내 워크시트 내의 셀을 선택할 때 발생


○ 워크시트 주요 이벤트

 이벤트

설명 

 Activate  워크시트가 활성화될 때 발생
 BeforeDoubleClick  워크시트에서 더블클릭할 때 발생

 BeforeRightClick

 워크시트에서 마우스 오른쪽 버튼을 클릭할 때 발생
 Calculate  워크시트에서 계산이 되는 경우 발생
 Change  워크시트의 값이 수정될 때 발생
 DeActivate  워크시트가 비활성화될 때 발생
 SelectionChange  워크시트 내의 셀을 선택할 때 발생





저작자 표시 동일 조건 변경 허락
신고

1. 부서별 실적 집계 매크로


  1) 매크로로 자동화할 작업 이해하기

  매크로를 잘 활용하기 위해서는 직접 해보지 않으면 배우기가 힘들다.

  그래서 이번에는 부분합 기능을 사용하여 부서별 상반기, 하반기, 충 매출액의 합을 자동으로 계산하는 매크로를 작성하는 것을 배워보자.

  아래과 같이 천천히 따라하면서 매크로 활용법을 익혀보자.


○ 원본 데이터 상태


○ 매크로 자동화를 완료한 상태


  2) 부분합 따라하기

가. 정의

▷ 데이터베이스의 특정 필드를 기준으로 합계, 평균, 최대, 최소, 개수등을 자동으로 계산해 주는 기능을 말한다.


나. 부분합 수행하기

① 부분합 계산 전에 부분합의 기준이 되는 필드로 먼저 정렬이 되어 있어야 함

i) 먼저 정렬하기 위해 아무 필드에서나 커서를 위치하고,


ii) [데이터] 탭 - [정렬 및 필터] 그룹 - [정렬] 아이콘을 선택한다.


iii) 정렬 기준을 부분합의 기준이 될 부서로 지정하고 확인 버튼을 클릭한다.


iv) 정렬을 마친 상태


② 이제 본격적으로 부분합을 구해보자.

i) [데이터] 탭 → [윤곽선] 그룹 → [부분합] 아이콘 클릭하여 실행


ii) 매뉴박스가 나오면 아래와 같이 체크하고 확인을 누르면 된다.


※ 각 항목에 대한 설명은 아래 표를 참고하면 된다.

 옵   션

 설              명

 ①그룹화할 항목   부분합 계산의 기준이 되는 필드를 지정
 이 필드를 기준으로 데이터가 정렬되어 있어야 함 
 ②사용할 함수  부분합을 계산할 함수를 선택
 합계, 평균, 개수, 최대, 최소 등 여러 가지 함수 사용 가능 
 ③부분합 계산 항목  선택한 필드의 부분합 계산
 ④새로운 값으로 대치   선택 : 부분합 실행 시 기존 부분합을 새로운 부분합 계산 값으로 대치
 해제 : 기존 부분합에 새로운 부분합을 추가 
 ⑤그룹 사이에서 페이지
 나누기 
 부분합이 계산된 그룹 사이에 자동으로 페이지 나누기를 삽입함 
 ⑥데이터 아래에 요약 표시   선택 : 부분합 계산 행을 데이터 아래에 표시
 해제 : 부분합 계산 행을 데이터 위에 표시
 ⑦모두 제거 

 부분합 제거 기능
 부분합을 제거하면 부분합과 함께 목록에 삽입된 윤곽 및 페이지 나누기도

 모두 제거됨 


③ 부분합 요약이 완료되면, 데이터 윤곽기호를 사용하여 데이터 표시 방법 변경 가능


다. 부분합 제거하기

[데이터] 탭 → [윤곽선] 그룹 → [부분합] 아이콘 클릭 한뒤 부분합 대화상자에서 [모두 제거]를 클릭


  3) 실적 집계 매크로 작성 단계 정리하기 

① [개발 도구] 탭 → [코드] 그룹 → [매크로 기록] 아이콘 클릭


② [데이터] 탭 → [윤곽선] 그룹 → [부분합] 아이콘 클릭, 

부서별로 상반기, 하반기, 총 매출액 합계를 계산하는 부분합 작업 실행 



③ [개발 도구] 탭 → [코드] 그룹 → [기록 중지] 아이콘 클릭


④ [삽입] 아이콘 → [양식 컨트롤] 범주의 [단추] 아이콘 사용하여 매크로 실행 단추 작성 


⑤  버튼위에 마우스를 올려 오른쪽 버튼을 클릭하고 버튼을 떼면 아래와 같이 편집가능한 상태로 변하는데 원하는 버튼 이름으로 바꾼다.


⑥ 버튼에 마우스 오른쪽 버튼으로 클릭한 뒤 아래와 같이 [먀크로 지정] 항목을 선택하여 아까 작성한 매크로를 지정해 주면 된다.




2. 데이터 조회 매크로


  1) 매크로로 자동화 할 작업 이해하기

  고급 필터 기능 사용하여 조건 영역에서 조건을 지정한 뒤 [조회] 단추를 클릭하면 데이터가 검색되고, 다시 [모두표시] 단추를 클릭하면 필터를 해제하고 모든 레코드를 자동으로 표시하기



  2) 고급 필터 

가. 필터란?

○ 다량의 데이터에서 조건에 만족하는 데이터만 뽑아 보는 기능 

○ [데이터] 탭 – [필터] 그룹 – [필터]나 [고급] 아이콘 활용 



나. 고급 필터 사용 방법

① 필터 할 조건을 조건 영역에 입력

② 데이터베이스 내부 임의의 셀을 선택

③ [데이터] 탭 → [정렬 및 필터] 그룹 → [고급] 아이콘을 클릭


④ [고급 필터] 대화상자가 표시되면 원하는 필터 옵션을 지정하고, <확인> 단추 클릭



※ 각 항목에 대한 설명은 아래 표를 참고하면 된다.

옵  션 

설          명 

 ①결과   현재 위치에 필터 : 데이터베이스가 있는 현재 위치에 필터 결과 표시
 다른 장소에 복사 : 필터 결과를 [복사 위치]에서 지정한 위치에 표시 
 ②목록 범위   데이터를 필터 할 데이터베이스 전체 범위 지정 
 ③조건 범위   조건이 입력된 셀 범위 지정 

 ④복사 위치 

 필터된 결과를 복사할 위치 지정 

 ⑤동일한 레코드는

 하나만 

 선택하면 필터 결과에 중복된 레코드가 있는 경우 결과가 하나만 표시됨 


⑤ 조건에 만족하는 데이터가 필터 되고, 행 머리글이 파랑색으로 표시됨


⑥ 필터 결과 제거 : [데이터] 탭 → [정렬 및 필터] 그룹 → [지우기] 아이콘


  

  3) 데이터 조회 매크로 작성 단계 정리하기

① [개발 도구] 탭 → [코드] 그룹 → [매크로 기록] 아이콘 클릭


② 고급 필터 기능을 사용하여 데이터 필터 작업 실행



③ [개발 도구] 탭 → [코드] 그룹 → [기록 중지] 아이콘 클릭


④ [개발 도구] 탭 → [컨트롤] 그룹 → [삽입] 아이콘, [양식 컨트롤] 범주의 [단추] 아이콘을 사용하여 매크로 실행 단추 작성



3. 매크로 관리


  1) 매크로 코드 확인하기

① [개발 도구] 탭 → [코드] 그룹 → [매크로] 아이콘 클릭


② [매크로] 대화상자에서 코드를 확인하고자 하는 매크로를 선택하고 [편집] 버튼클릭


③ Visual Basic 편집기 창이 실행되고 기록한 매크로 코드가 표시


  

  2) 매크로 코드 수정하기

고급필터 적용한 매크로에 필터 적용한 뒤에 '조회가 완료되었습니다.' 라는 메시지 박스 추가


아래와 같이 End Sub 바로 윗 줄에 빈줄을 추가하여

MsgBox "조회가 완료되었습니다." 라는 구문을 입력한다.


아래 고급필터 매크로인 조회 버튼을 클릭하면 고급필터가 적용되고 아래와 같이 메시지 박스가 추가된 것을 확인할 수 있다.


 [매크로가 끝난 뒤 커서를 특정 셀에 두고 싶을 때 추가되는 줄]

   Range("원하는 셀주소").Select


  예) 위 화면에서 커서를 B6에 두고 싶을 때 →  Range("B6").Select

           

  3) 매크로 삭제하기

① [매크로] 아이콘 사용하여 삭제

i) [개발 도구] 탭 → [코드] 그룹 → [매크로] 아이콘 클릭


ii) [매크로] 대화상자가 나타나면 삭제하고자 하는 매크로를 선택하고 [삭제] 버튼 클릭


② Visual Basic 편집기 창에서 코드 삭제

i)[개발 도구] 탭 → [코드] 그룹 → [Visual Basic] 아이콘 클릭 


ii) Visual Basic 편집기 창에서 작성되어 있는 매크로 코드(Sub Procedure)를 선택한 후 [Delete]키 누름




 


저작자 표시 동일 조건 변경 허락
신고

1. 매크로와 VBA

 

  1) 매크로

○ 매크로란 : 반복되는 엑셀 작업을 기록해 두었다가 필요할 때마다 단축키를 누르거나 버튼만 누르면 해당 작업을 자동으로 실행할 수 있는 기능(매크로 기능은 엑셀에만 있는 것이 아니가 다양한 프로그램에 내재되어 있어 하나의 프로그램에서 그 기능을 배워두면 쉽게 타 프로그램에서도 응용이 가능하다.)

○  사용 예 : 아래와 같이 항상 제목으로 사용하는 서식을 매크로로 기록해두면 해당 매크로만 실행해도 글꼴과 크기, 스타일 및 색을 지정된 서식으로 변경해준다.

 

 

  2) VBA 란?

○ 매크로 기능 사용 시 → Visual Basic 코드(프로그래밍 언어)로 자동 기록 됨

○ VBA : Visual Basic 코드를 사용자의 필요에 따라 수정(코딩) 하는 것

▷ Visual Basic for Application의 약자 → 특정 Application (Excel, Access, Word와 같은 응용프로그램)에서만 사용할 수 있는 VB(Visual Basic) 코드를 의미. 예) 엑셀 → 엑셀 VBA, 액세스 → 액세스 VBA

 

(1) VB (Visual Basic)

▷ 프로그래밍 언어로 단어의 의미처럼 아주 쉽게 작성 가능

▷ 영어 문장과 같은 형태로 구성되어 이해가 수월함

: Microsoft 사에서 개발된 오피스 제품(Excel, Access, Word, PowerPoint 등)에서 매크로를 기록할 때 Visual Basic을 사용하도록 되어 있음

 

  3) 매크로와 VBA를 사용하여 할 수 있는 일

(1) 반복 실행되는 엑셀 작업을 자동화
(2) 엑셀에서 제공되지 않는 새로운 기능 생성
(3) 엑셀에서 제공하지 않는 함수 생성
(4) 전문적인 업무 프로그램을 개발

 

  4) 매크로 작성 준비 사항

(1) 리본 메뉴에 [개발 도구] 탭 표시(엑셀2007 기준입니다)

▷ [오피스 단추] - [Excel 옵션] - [기본 설정] - [리본 메뉴에 개발 도구 탭 표시] - [확인] 을 선택


▷ 그러면 다음과 같이 리본 메뉴에 개발도구 탭이 생기는 것을 볼 수 있음


(2) 매크로가 포함된 통합 문서 저장

▷ 엑셀 문서에 매크로가 기록된 경우 →‘매크로 사용 통합 문서(*.xlsm)’형식으로 저장

▷ [오피스 단추] -  [다른 이름으로 저장] 항목 → [매크로 사용 통합 문서] 항목


(3) 매크로 보안 설정

▷  [오피스 단추] - [Excel 옵션] - [보안 센터] - [메크로 설정] - [모든 메크로 포함] - [확인] 을 선택

 


 2. 매크로 기록 및 실행

 

  1) 매크로 기록 단계

① 매크로로 기록할 엑셀 작업 정리

② 매크로 기록 시작 명령

③ 엑셀 작업 실행

④ 매크로 기록 중지

 

   2) 매크로 기록 방법

①-1. 기록 시작 : [개발 도구] 탭 → [코드] 그룹 → [매크로 기록] 아이콘 클릭


①-2. [보기] 탭 → [매크로] 그룹 → [매크로 기록] 아이콘 클릭


② [매크로 기록] 대화상자 표시 → 각 항목 지정 → <확인> 버튼 클릭, 매크로 기록 시작


  매크로 이름 영문, 숫자, 한글로 지정하되 첫 글자에 “_” 나 숫자를 사용할 수 없고 공백 . , ! “ # & ( ) + ~ 등의 특수문자를 사용할 수 없음


  바로 가기 키 알파벳 소문자 a~z, 대문자 A~Z를 지정할 수 있고, 대문자로 지정할 경우 Shift 키를 누르고 알파벳을 입력하면 왼쪽에 표시되어 있던 Ctrl이 Ctrl+Shift로 자동으로 변경됨


  매크로 저장 위치

 ① 개인용 매크로 통합 문서 : 매크로를 저장하면 현재 매크로를 기록하고 있는 문서뿐 아니라 모든 통합 문서에서 사용 가능

② 새 통합 문서 : 매크로를 저장하면 [새 문서]를 만들 때 새 문서에 매크로가 포함되어 있음

③ 현재 통합 문서 : 매크로를 저장하면 현재 매크로를 기록하는 통합 문서에서만 매크로를 사용 가능


③ 기록 중지

▷ [개발 도구] 탭 → [코드] 그룹 → [기록 중지] 클릭

▷ [보기] 탭 → [매크로] 그룹 → [매크로 기록] 아이콘 클릭


 Tip : 개인용 매크로 통합 문서
<개인용 매크로 통합 문서>

  개인용 매크로 통합 문서에 매크로를 저장하면 ‘PERSONAL.XLSB’ 라는 ‘개인용 매크로 통합 문서’가 자동으로 생성되어 그 파일에 매크로가 저장된다.
  PERSONAL.XLSB는 엑셀 문서가 열릴 때 숨김 파일로 함께 열린다. 그렇게 숨겨진 상태로 열린 PERSONAL.XLSB 파일에 저장된 매크로를 다른 엑셀 통합 문서에서 사용할 수 있는 것이다. 
  PERSONAL.XLSB 파일의 저장 경로는 ‘C:\Users\<사용자이름>\AppData\Roaming\Microsoft\Excel\XLSTART’이다.


  3) 매크로 실행 방법

(1) 바로 가기 키 : 매크로 기록 시 지정한 [바로 가기 키]를 눌러 매크로 실행

(2) 매크로 명령

① [개발 도구] 탭 → [코드] 그룹 → [매크로 보기] 클릭

② [매크로] 대화상자에서 실행할 매크로 선택 후 [실행] 버튼 클릭


(3) 양식 단추

① [개발 도구] 탭 → [컨트롤] 그룹 → [컨트롤 삽입] 아이콘→ [양식 컨트롤] 범주 → [단추]

② [매크로 지정] 대화상자에서 실행할 매크로를 선택한 후 <확인> 클릭

 tip! _ 양식 단추의 위치 및 크기 고정 시키기

양식 단추는 행/열 작업에 따라 위치와 크기가 변한다. 
예를 들어, 양식 단추가 위치한 열의 너비를 늘리한 단추의 너비도 함께 늘어나게 된다. 
그러므로 워크시트에서 행이나 열 관련 작업을 해도 단추가 영향을 받지 않고 항상 같은 위치와 크기로 존재하게 하려면 다음처럼 속성을 지정해주어야 한다.

① 양식 단추에서 마우스 오른쪽 단추를 클릭하고 [컨트롤 서식]을 선택한다.
  

② [속성] 탭에서 [변하지 않음]을 설정한다.


(4) 그리기 도형

① [삽입] 탭 → [일러스트레이션] 그룹 → [도형] 아이콘 클릭, 원하는 도형을 워크시트에 작성


② 도형에서 마우스 오른쪽 버튼을 클릭하고 [매크로 지정] 클릭


③ [매크로 지정] 대화상자에서 실행할 매크로를 선택하고, [확인] 버튼 클릭


(5) 빠른실행 도구 모음에 추가

- [빠른 실행 도구 모음] 영역에 아이콘을 추가하여 실행 가능





저작자 표시 동일 조건 변경 허락
신고

1. 유가증권의 수익률 구하기


  1) YIELD 함수

○ 개념

- 정기적으로 이자를 지급하는 유가증권의 수익률을 반환하는 함수

- 주로 채권의 수익률을 계산할 때 사용

○ 구문 =YIELD(Settlement, Maturity, Rate, Pr, Redemption, Frequency, [Basis])

○ 인수

<필수 요소>

- Settlement : 유가증권의 결산일, 유가증권이 매수자에게 매도된 발행일 다음 날

- Maturity : 유가증권의 만기일, 유가증권이 만기가 되는 날짜

- Rate : 유가증권의 연간 확정 금리

- Pr : 액면가 $100당 유가증권 가격

- Redemption : 액면가 $100당 유가증권의 상환액

- Frequency : 연간 이자 지급 횟수

<선택 요소>

- Basis : 선택 요소, 날짜 계산 기준

0 또는 생략

1

2

미국 30/360

실제/실제 

실제/360 

실제/365 

유럽 20/360 


  2) YIELDMAT 함수

○ 개념 : 만기 시 이자를 지급하는 유가증권의 연 수익률을 반환하는 함수

○ 구문 =YIELDMAT(Settlement, Maturity, Issue, Rate, Pr, [Basis])

○ 인수

<필수 요소>

- Settlement : 유가증권의 결산일, 유가증권이 매수자에게 매도된 발행일 다음 날

- Maturity : 유가증권의 만기일, 유가증권이 만기가 되는 날짜

- Issue : 유가증권의 발행일

- Rate : 유가증권의 연간 확정 금리

- Pr : 액면가 $100당 유가증권 가격

<선택 요소>

- Basis : 선택 요소, 날짜 계산 기준


  3) YIELDDISC 함수

○ 개념 : 할인된 유가증권의 연 수익률을 반환하는 함수

○ 구문 =YIELDDISC(Settlement, Maturity, Pr, Redemption, [Basis])

○ 인수

<필수 요소>

- Settlement : 유가증권의 결산일, 유가증권이 매수자에게 매도된 발행일 다음 날

- Maturity : 유가증권의 만기일, 유가증권이 만기가 되는 날짜

- Pr : 액면가 $100당 유가증권 가격

- Redemption : 액면가 $100당 유가증권의 상환액

<선택 요소>

- Basis : 선택 요소, 날짜 계산 기준


<사용 예시>

- [G7]셀의 경우 : 결산일이 [B16]셀, 만기날짜가 [D7]셀 → [E7]셀의 이율 

              → $100 당 가격은 [C7]셀, 상환액은 [F7]셀 → 년간 2회 정기 이자 지급 

              → 수익률 24.94%

- [H7]셀의 경우 : 결산일이 [B16]셀, 만기날짜가 [D7]셀, 발행일이 [B7]셀 

              → [E7]셀의 이율 → $100 당 가격은 [C7]셀 → 연 수익률 24.16%

- [I7]셀의 경우 : 결산일이 [B16]셀, 만기날짜가 [D7]셀 → $100 당 금액은 [C7]셀,

 상환액이 [F7]셀 → 할인 증권의 연 수익률 15.09%



2. 감가상각액 구하기


  1) SLN 함수

○ 개념 : 단위 기간 동안 정액법에 의한 자산의 감가상각액을 반환하는 함수

○ 구문 =SLN(Cost, Salvage, Life)

○ 인수

▷ Cost : 필수 요소, 자산의 초기 취득가액

▷ Salvage : 필수 요소, 감가상각 완료 시의 가치, 즉 자산의 잔존 가치

▷ Life : 필수 요소, 자산의 총 감가상각 기간 수, 즉 자산의 수명 연수


  2) DB 함수

○ 개념 : 정률법을 사용하여 특정 기간 동안 자산의 감가상각을 반환하는 함수

- 정률법 : 고정 비율로 감가상각을 계산하는 방법

- 사용 수식 : (구입 비용 - 이전 기간의 총 감가상각) * Rate

○ 구문 =DB(Cost, Salvage, Life, Period, [Month])

○ 인수

▷ Cost : 필수 요소, 자산의 초기 취득가액

▷ Salvage : 필수 요소, 감가상각 완료 시의 가치, 즉 자산의 잔존 가치

▷ Life : 필수 요소, 자산의 총 감가상각 기간 수, 즉 자산의 수명 연수

▷ Period : 필수 요소, 감가상각을 계산할 기간, Life와 같은 단위 사용

▷ Month : 선택 요소, 첫 해의 개월 수, 생략하면 12로 간주


  3) DDB 함수

○ 개념 : 이중 체감법이나 기타 방법을 사용하여 지정된 기간의 감가상각액을 계산하는 함수

- 이중 체감법 : 상각 비율을 달리하여 감가상각을 계산하는 방법

○ 구문 =DDB(Cost, Salvage, Life, Period, [Factor])

○ 인수

▷ Cost : 필수 요소, 자산의 초기 취득가액

▷ Salvage : 필수 요소, 감가상각 완료 시의 가치, 즉 자산의 잔존 가치

▷ Life : 필수 요소, 자산의 총 감가상각 기간 수, 즉 자산의 수명 연수

▷ Period : 필수 요소, 감가상각을 계산할 기간, Life와 같은 단위 사용

▷ Factor : 선택 요소, 잔액이 감소하는 비율, 생략하면 2(이중 체감법)로 간주


<사용 예시>

- [H5]셀의 경우 : 구입 시 가격이 [C2]셀 → 잔존가치는 [D2]셀 → 수명 연수는 [E2]셀인 자산의 첫 감가상각액 → 108,400(정액법은 (구입비용-잔존가치)한 값을 그냥 수명으로 나눠준다.)

- [I5]셀의 경우 : 구입 시 가격이 [C2]셀 → 잔존가치는 [D2]셀 → 수명 연수는 [E2]셀 

→ 첫 해 남은 개월은 '12-[B2]셀의 월'인 자산

→ [G5]셀의 기간의 감가상각액 → 184,223

- [J5]셀의 경우 : 구입 시 가격이 [C2]셀 → 자존가치는 [D2]셀 → 수명 연수는 [E2]셀인 자산 

→ [G5]셀의 기간의 감가상각액 → 255,200


 

3. 경과이자 및 할인율 구하기


  1) DISC 함수

○ 개념 : 유가증권의 할인율을 반환하는 함수

○ 구문 =DISC(Settlement, Maturity, Pr, Redemption, [Basis])

○ 인수

▷ Settlement : 필수 요소, 유가증권의 결산일, 유가증권이 매수자에게 매도된 발행일 다음 날

▷ Maturity : 필수 요소, 유가증권의 만기일, 유가증권이 만기가 되는 날짜

▷ Pr : 필수 요소, 액면가 $100당 유가증권 가격

▷ Redemption : 필수 요소, 액면가 $100당 유가증권의 상환액

▷ Basis : 선택 요소, 날짜 계산 기준


  2) ACCRINTM 함수

○ 개념 : 만기에 이자를 지급하는 유가증권의 경과이자를 반환하는 함수

○ 구문 =ACCRINTM(Issue, Settlement, Rate, Par, [Basis])

○ 인수

<필수 요소>

- Issue : 유가증권의 발행일

- Settlement : 유가증권의 결산일

- Rate : 유가증권의 연간 확정 금리

- Par : 유가증권의 액면가, 지정하지 않으면 기본적으로 1,000 사용

<선택 요소>

- Basis : 선택 요소, 날짜 계산 기준


  3) ACCRINT 함수

○ 개념 : 정기적으로 이자를 지급하는 유가증권의 경과이자를 반환하는 함수

○ 구문 =ACCRINT(Issue, First_interest, Settlement, Rate, Par, Frequency, [Basis], [Calc_method])

○ 인수

<필수요소>

- Issue : 유가증권의 발행일

- First_interest : 유가증권의 최초 이자 지급일

- Settlement : 유가증권의 결산일, 유가증권이 매수자에게 매도된 발행일 다음 날

- Rate : 유가증권 발행일의 이율

- Par : 유가증권의 액면가, 지정하지 않으면 기본적으로 1,000 사용

- Frequency : 연간 이자 지급 횟수

<선택요소>

- Basis : 날짜 계산 기준

- Calc_method : Settlement 날짜가 First_interest 날짜 이후일 때 총 경과이자를 계산할 방법을 지정하는 논리값

·Ÿ TRUE(1)로 지정하면 Issue에서 Settlement 사이의 총 경과이자 반환 

· FALSE(0)으로 지정하면 First_interest에서 Settlement 사이의 총 경과이자 반환

·Ÿ 입력하지 않으면 기본값인 TRUE 사용


<사용 예시>

- [F2]셀의 경우 : 결산일 '2011-07-01', 만기날짜 [D2]셀 → $100 당 가격 [E2]셀, 상환액 [C2]셀 

→  할인율 5.7%

- [G2]셀의 경우 : 발행일 [B2]셀, 만기날짜 [D2]셀 → 액면가 [E2]셀 → 이율 [F2]셀 

→ 경과이자 386,855

- [B15]셀의 경우 : 발행일 [B12]셀, 이자의 최초지급일 [C12]셀 → 액면가 [D12]셀 

→ 이율 [E12]셀 → 년간 이자 지급 회수 1 

→  유가증권의 [B4]셀의 날짜에 지급되는 경과이자 → 129,033






저작자 표시 동일 조건 변경 허락
신고

1. 투자의 미래 가치 구하기

 

  1) FV 함수

○ 개념 : 일정 금액을 정해진 기간 예치, 정기적으로 불입하여 일정한 이자율을 적용되는 투자의 미래 가치를 계산하는 함수

○ 구문 =FV(Rate,Nper,Pmt,[Pv],[Type])

○ 인수

<필수 요소>
    - Rate: 기간별 이율
    - Nper :총 납입 기간 수
    - Pmt : 정기적으로 적립하는 금액, 전 기간 동안 변경되지 않음
            Pmt를 생략하면 Pv 인수를 반드시 포함해야 함

<선택 요소>

- Pv : 현재 가치 (앞으로 납입할 일련의 금액이 갖는 현재 가치의 총합)
      생략하면 0으로 간주되며 Pmt 인수를 반드시 포함해야 함
- Type : 납입 시점을 나타내는 숫자
         기간 말 → 0, 기간 초 → 1, 생략하면 0으로 간주

 

  2) FVSCHEDULE 함수

○ 개념

▷ 초기 원금에 일련의 복리 이율을 적용했을 때의 예상 금액을 반환하는 함수
▷ 투자액에 다양한 이율을 적용했을 때의 예상 금액 계산 가능

○ 구문 =FVSCHEDULE(Principal, Schedule)

○ 인수

▷ Principal : 필수 요소, 현재 가치

▷ Schedule : 필수 요소, 적용할 이율로 구성된 배열

 

<사용 예시>

- [B4]셀의 경우 : [B2]셀의 금액 → [D3]셀의 연도를 F3:G12에서 찾아서 약정이율(4.7%)을 가져옴 → [D2]셀의 기간 → 15,097,834

- [D11]셀의 경우 : [B7]셀의 금액 → [F3:G12]의 이율로 → [D7]셀의 기간만큼 예치한 미래 가치 → 15,111,979

- [B16]셀의 경우도 위와 마찬가지이다. 

 


2. 투자 기간 및 대출 불입액 구하기

 

  1) NPER 함수

○ 개념 : 정기적으로 일정 금액을 불입하고 일정한 이율을 적용되는 투자에 대한 투자 기간 수를 반환하는 함수

○ 구문 =NPER(Rate,Pmt,Pv,[Fv],[Type])

○ 인수

<필수 요소>

- Rate: 기간별 이자율
- Pmt : 각 기간의 납입액, 전 기간 동안 변경되지 않음
       Pmt에는 기타 비용과 세금을 제외한 원금과 이자 포함
- Pv : 현재 가치 (앞으로 납입할 일련의 금액이 갖는 현재 가치의 총합)

<선택 요소>

- Fv : 미래 가치 (최종 납입 후의 현금 잔고), 생략하면 0으로 간주
      예) 대출금의 미래 가치는 0
- Type : 납입 시점을 나타내는 숫자
        기간 말 → 0, 기간 초 → 1, 생략 → 0

 

  2) PMT 함수

○ 개념 : 정기적, 고정적인 지급액과 일정한 이율이 적용되는 대출금의 상환액을 계산하는 함수

○ 구문 =PMT(Rate, Nper, Pv, [Fv], [Type])

○ 인수

<필수 요소>

- Rate : 기간별 이자율
- Nper : 대출금의 총 상환 횟수
- Pv : 현재 가치 (앞으로 납입할 일련의 금액이 갖는 현재 가치의 총합)

<선택 요소>

- Fv : 미래 가치 (최종 납입 후의 현금 잔고), 생략하면 0으로 간주
- Type : 납입 시점을 나타내는 숫자
  기간 말 → 0, 기간 초 → 1, 생략 → 0

 

<사용 예시>

- [B6]셀의 경우 : [B4]셀의 이율로 → 매월 [B5]셀의 금액만큼 적립하여 [B3]셀의 금액에 도달하는 기간

- [E6]셀의 경우 : [E4]셀의 이율로 → [E5]셀의 금액을 예치하여 [B3]셀의 금액에 도달하는 기간

- [H6]셀의 경우 : [H4]셀의 이율로 → 매월 [H5]셀의 금액을 갚아나가면 [H3]셀의 대출금을 다 갚는 기간

- [B13]셀의 경우 : [B11]의 이율로 → [B12]셀의 기간만큼 →[B10]셀의 금액을 만들려면 매월 불입할 금액은 →  140,403

- [E13]셀의 경우 : [E10]셀의 금액 → [E11]셀의 이율로 [E12]셀의 기간에 반납할 경우 회당 반납액 → 893,305

 

 
3. 원금과 이자 계산하기

 

  1) PPMT 함수

○ 개념 : 정기적으로 일정 금액을 지불하고 일정 이율이 적용되는 투자액에 대해 주어진 기간 동안의 원금 상환액을 반환하는 함수

○ 구문 =PPMT(Rate, Per, Nper, Pv, [Fv], [Type])

○ 인수

<필수 요소>

- Rate: 기간별 이율
- Per : 납입 회차, 1에서 nper 사이의 범위로 지정
- Nper : 총 납입 기간 수
- Pv : 현재 가치 (앞으로 납입할 일련의 금액이 갖는 현재 가치의 총합)

<선택 요소>

- Fv : 미래 가치 (최종 납입 후의 현금 잔고), 생략하면 0으로 간주
- Type : 납입 시점을 나타내는 숫자
        기간 말 → 0, 기간 초 → 1, 생략 → 0

  2) IPMT 함수

○ 개념 : 일정 금액을 정기적으로 납입하고 일정한 이율이 적용되는 투자에 대해 주어진 기간 동안의 이자를 계산하는 함수

○ 구문 =IPMT(Rate, Per, Nper, Pv, [Fv], [Type])

○ 인수

<필수 요소>

- Rate: 기간별 이율
- Per : 납입 회차, 1에서 nper 사이의 범위로 지정
- Nper : 총 납입 기간 수
- Pv : 현재 가치 (앞으로 납입할 일련의 금액이 갖는 현재 가치의 총합)

<선택 요소>

- Fv : 미래 가치 (최종 납입 후의 현금 잔고), 생략하면 0으로 간주
- Type : 납입 시점을 나타내는 숫자
        기간 말 → 0, 기간 초 → 1, 생략 → 0

 

<사용 예시>

- [B5]셀의 경우 : [C2]셀의 금액 → [D2]셀의 이율, [B2]셀의 기간에 거쳐 상환 → [A5]셀 회수  → 상환액 중 원금 → 7,306,408

- [C5]셀의 경우 : [C2]셀의 금액 → [D2]셀의 이율, [B2]셀의 기간에 거쳐 상환 → [A5]셀 회수  → 상환액 중 이자 → 6,800,000

- [F5]셀의 경우 : [C2]셀의 금액 → [D2]셀의 이율, [B2]셀의 기간에 거쳐 상환 할 경우 회당 상환액 → 14,106,408

※ PMT=PPMT+IPMT

 

 


저작자 표시 동일 조건 변경 허락
신고

1. 데이터 형식 변환하기


  1) WON 함수

○ 개념 : 지정된 자리에서 반올림하고 통화 형식을 사용하여 숫자를 텍스트로 변환하는 함수

○ 구문 =WON(Number, [Decimals])

○ 인수

▷ Number : 필수 요소, 숫자·숫자가 들어 있는 셀에 대한 참조·숫자를 계산하는 수식

▷ Decimals : 선택 요소, 소수점 이하 자릿수

음수 → Number는 소수점 위에서 반올림

            생략 → 2로 간주


  2) FIXED 함수

○ 개념 : 숫자를 지정된 자릿수에서 반올림, 마침표와 쉼표를 사용하여 실수 형식으로 지정, 결과를 텍스트로 반환하는 함수

○ 구문 =FIXED(Number, [Decimals], [No_commas])

○ 인수

▷ Number : 필수 요소, 반올림하여 텍스트로 변환할 숫자

▷ Decimals : 선택 요소, 소수점 이하 자릿수

음수 → Number는 소수점 위에서 반올림, 생략 → 2로 간주

▷ No_commas : 선택 요소

FALSE ·생략 → 반환되는 텍스트에 쉼표 포함

TRUE → 반환되는 텍스트에 쉼표 미포함


  3) VALUE 함수

○ 개념 : 숫자를 나타내는 텍스트 문자열을 숫자로 변환하는 함수

○ 구문 =VALUE(Text)

○ 인수

▷ Text : 필수 요소, 변환할 텍스트가 있는 셀 참조, 따옴표로 묶인 텍스트


  4) TEXT 함수

○ 개념 : 특수 서식 문자열을 사용하여 표시 형식을 지정할 수 있도록 숫자 값을 텍스트로 변환하는 함수

○ 구문 =TEXT(Value, Format_text)

○ 인수

▷ Value : 필수 요소, 숫자 값·숫자 값으로 계산되는 수식·숫자 값이 포함된 셀에 대한 참조

▷ Format_text : 필수 요소, 따옴표로 묶인 텍스트 문자열의 숫자 형식

예) "m/d/yyyy" 또는 "#,##0.00"


<사용 예시>

- [D5]셀의 경우 : [C5]셀 → 'aaaa' 표시 형식 → 텍스트 화요일

- [J5]셀의 경우 : 매출액($E$5:$E$14)에서 [I5]셀(첫) 번째 큰 수를 찾아서 6자리에서 반올림하고 원 표시를 하라는 것

가장 큰 매출액은 35,400,000이지만 반올림하면 35,000,000이 된다.

- [J8]셀의 경우 : [J5:J7]셀의 평균값을 구하고 6자리에서 반올림하라는 것(배열수식에 주의)

     실제 구해진 평균은 25,666,666.7인데 6자리에서 반올림하면 26,000,000이 된다.

 

 

2. 특정 값 구하기


  1) CHAR 함수

○ 개념 : 코드 번호에 해당되는 문자를 반환하는 함수

○ 구문 =CHAR(Number)

○ 인수

▷ Number : 필수 요소, 원하는 문자를 지정하는 코드 번호


  2) CODE 함수

○ 개념 : 텍스트의 첫 번째 문자에 대한 숫자 코드를 반환하는 함수

- 코드는 컴퓨터에서 사용하는 문자 집합에 따라 달라질 수 있음

○ 구문 =CODE(Text)

○ 인수

▷ Text : 필수 요소, 첫 번째 문자의 코드를 구하려는 텍스트

 

  3) LEN · LENB 함수

○ 개요

▷ LEN 함수 : 텍스트 문자열의 문자 수를 반환하는 함수

▷ LENB 함수 : 텍스트 문자열에서 문자를 나타내는데 사용되는 바이트 수를 반환하는 함수

○ 구문 =함수명(Text)

○ 인수

▷ Text : 필수 요소,  길이를 확인하려는 문자열, 공백도 문자로 계산

 

<사용 예시>

- [F4]셀의 경우

▷ LEN 함수 : [C4]셀의 글자수 → 20

▷ LENB 함수 : [C4]셀의 바이트수 → 29

▷ RIGHT 함수의 Num_chars 인수 : 20*2-29-3 → 8

▷ [F4]셀의 경우 : [C4]셀의 오른쪽부터 8 글자 → 3011-203

- [C17]셀의 경우 : CODE 함수

                  [A17]셀의 첫 글자 → ①의 코드 → 43239

 

 

 

3. 대소문자 변환 및 텍스트 결합과 제거


  1) 대소문자를 변환하는 함수

○ 개요

▷ LOWER 함수 : 텍스트 문자열의 대문자를 모두 소문자로 변환하는 함수

▷ PROPER 함수 : 단어의 첫 번째 문자, 문자가 아닌 문자 다음에 오는 영문자 → 대문자

나머지 문자 → 소문자

▷ UPPER 함수 : 텍스트 문자열의 소문자를 모두 대문자로 변환하는 함수

○ 구문 =함수명(Text)

○ 인수

▷ Text : 필수 요소, 대소문자를 변환할 문자열

텍스트에서 글자가 아닌 문자(숫자, 기호 등)는 변환되지 않음


  2) 텍스트를 제거하는 함수

○ 개요

▷ CLEAN 함수 : 인쇄할 수 없는 문자를 텍스트에서 모두 삭제하는 함수

▷ TRIM 함수 : 단어 사이에 있는 공백 하나만 남기고 텍스트의 공백을 모두 삭제하는 함수

○ 구문 =함수명(Text)

○ 인수

▷ Text : 필수 요소, 인쇄할 수 없는 글자 또는 공백을 제거할 텍스트

 

  3) 텍스트를 결합하는 CONCATENATE 함수

○ 개요 : 최대 255개의 텍스트 문자열을 하나의 텍스트 문자열로 결합

결합된 항목은 텍스트, 숫자, 셀 참조 또는 이러한 항목의 조합일 수 있음

○ 구문 =CONCATENATE(Txt1, [Text2], ...)

○ 인수

▷ Text1 : 필수 요소, 연결할 첫 번째 텍스트 항목

▷ Text2,... : 선택 요소, 추가 텍스트 항목

 

<사용 예시>

- [G4]셀의 경우

▷ PROPER 함수

[D4]셀의 문자열을 첫 문자는 대문자로, 나머지는 모두 소문자로 바꾸어 반환 → Plan

▷ LOWER 함수

[E4]셀의 문자열을 모두 소문자로 바꾸어 반환 → sta

▷ Plan+sta+09+7421 → Plansta097421

-  [H4]셀의 경우

▷ CLEAN 함수 : [F4]셀의 텍스트에서 인쇄되지 않는 문자 삭제 → 줄바꿈 기호 삭제

▷ TRIM 함수 : [F4]셀의 텍스트에서 앞 뒤의 공백 문자 삭제 → 첫 부분 공백 삭제


 

 


저작자 표시 동일 조건 변경 허락
신고

1. 특정 위치의 텍스트 추출하기


  1) 양 끝의 문자열을 추출하는 함수

○ 첫 번째 문자부터 추출하는 함수

▷ LEFT 함수 : 텍스트 문자열의 첫 번째 문자부터 지정한 문자 수만큼 문자를 반환하는 함수

▷ LEFTB 함수 : 텍스트 문자열의 첫 번째 문자부터 지정한 바이트 수만큼 문자를 반환하는 함수

○ 끝 문자부터 추출하는 함수

▷ RIGHT 함수 : 텍스트 문자열의 마지막 문자부터 지정된 개수의 문자를 반환하는 함수

▷ RIGHTB 함수 : 텍스트 문자열의 마지막 문자부터 지정된 길이의 문자를 반환하는 함수

○ 함수 구문

▷ LEFT · RIGHT 함수 구문 =함수명(Text,[Num_chars])

▷ LEFTB · RIGHTB 함수 구문 =함수명(Text,[Num_bytes])

○ 인수

▷ Text : 필수 요소, 추출할 문자가 들어 있는 텍스트 문자열

▷ Num_chars : LEFT · RIGHT 함수의 선택 요소, 함수로 추출할 문자 수를 지정

- Num_chars는 0이거나 0보다 커야 함

-Ÿ Num_chars가 문자열의 길이보다 길면 텍스트 전체를 반환

-Ÿ Num_chars를 생략하면 1로 간주됨 

- Num_bytes : LEFTB · RIGHTB 함수의 선택 요소, 함수로 추출할 바이트 수를 지정


  2) MID 함수

○ 개요 : 문자열의 지정된 위치에서부터 지정한 만큼 문자 수를 반환하는 함수

○ 구문 =MID(Text, Start_num, Num_chars)

○ 인수

▷Ÿ Text : 필수 요소, 추출할 문자가 들어 있는 텍스트 문자열

▷Ÿ Num_chars : 필수 요소, 함수로 추출할 문자 수를 지정

- Num_chars는 0이거나 0보다 커야 함

- Num_chars가 문자열의 길이보다 길면 텍스트 전체를 반환

- Num_chars를 생략하면 1로 간주됨

▷Ÿ Start_num : 필수 요소, 추출할 첫 문자의 위치


<사용 예시>

- [B4]셀의 경우 : LEFT 함수, [A4]셀에서 왼쪽에서 두 글자 → PR

- [C4]셀의 경우 : RIGHT 함수, [A4]셀에서 오른쪽에서 네 글자  → 2010

- [D4]셀의 경우 : MID 함수, [A4]셀에서 네 번째 글자부터 네 글자  → A001



2. 텍스트 비교 또는 찾기


  1) EXACT 함수

○ 개요 : 두 문자열을 비교하여 정확하게 일치 → TRUE 반환, 일치하지 않으면 → FALSE 반환

- 대/소문자를 구분하지만 서식 차이는 무시

- 문서에 입력되는 텍스트 검사 가능

○ 구문 =EXACT(Text1, Text2)

○ 인수

▷ Text1 : 필수 요소,  첫 번째 텍스트 문자열

▷ Text2 : 필수 요소, 첫 번째 문자열과 일치하는지 비교할 두 번째 텍스트 문자열


  2) FIND · SEARCH 함수

○ 개요 : 문자열에서 특정 텍스트 문자를 검색한 후, 첫 문자를 기준으로 몇 번째 위치에 있는지 숫자로 반환하는 함수

- FIND 함수 : 대/소문자를 구분하고 와일드 문자 사용 불가 

- SEARCH 함수 : 대/소문자를 구분하지 않으며 와일드 문자 사용 가능

○ 함수 구문 =함수명(Find_text, Within_text, [Start_num])

○ 인수

▷ Find_text : 필수 요소, 찾을 텍스트

▷ Within_text : 필수 요소, Find_text 인수에서 찾으려는 텍스트를 포함하는 문자열

▷ Start_num : 선택 요소, Within_text 인수에서 찾기 시작할 문자의 위치

-Ÿ Within_text에서 첫 문자의 문자 번호는 1 

-Ÿ Start_num을 생략하면 1로 간주


<사용 예시>

- [F4]셀의 경우 : @ 문자 앞의 아이디의 글자 수를 구하는 수식

FIND 함수의 결과값 → [D4]셀에서 '@'의 위치 검색 → 8 

※ 아이디의 글자 수 → 8-1 → 7

- [G4]셀의 경우 : EXACT 함수, [E4]셀과 [C4]의 세 번째 부터 오른쪽에서 네 글자를 비교

→ 둘 다 1129 → 부적합

※ G4셀은 신청 비밀번호가 주민번호 중 세 번째부터 오른쪽으로 네 글자와 같으면 "부적합", 그렇지 않으면 "적합"으로 표시하라는 수식



3. 텍스트의 문자 바꾸기


  1) REPLACE 함수

○ 개요 : 지정한 문자 수에 따라 텍스트 문자열의 일부를 다른 텍스트 문자열로 바꾸는 함수

- 문자열의 특정 위치에 있는 텍스트를 바꿀 때 사용

○ 구문 =REPLACE(Old_text, Start_num, Num_chars, New_text)

○ 인수

▷ Old_text : 필수 요소, 일부 문자를 바꿀 텍스트

▷ Start_num : Old_text에서 New_text로 바꿀 문자의 위치

▷ Num_chars : Old_text에서 New_text로 바꿀 문자의 수

▷ New_text : Old_text에 바꿔 넣을 새 문자열


  2) SUBSTITUTE 함수

○ 개요 : 문자열에서 Old_text를 New_text로 바꾸는 함수. 문자열의 특정 텍스트를 바꿀 때 사용

○ 구문 =SUBSTITUTE(Text, Old_text, New_text, [Instance_num])

○ 인수

▷ Text : 필수 요소, 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트

▷ Old_text : 필수 요소, 바꿀 텍스트

▷ New_text : 필수 요소, Old_text를 대신할 텍스트

▷ Instance_num: 선택 요소, Text에서 몇 번째에 있는 Old_text를 New_text로 바꿀 것인지를 지정하는 수

-Ÿ Instance_num를 지정하면 해당하는 위치에 있는 Old_text만 바뀜

-Ÿ Instance_num를 지정하지 않으면 모든 Old_text가 New_text로 바뀜


<사용 예시>

- [E4]셀의 경우 : [C4]셀 값이 "대리"인지 확인 → 대리 아님 → [D4] 값을 그대로 가져옴

※ [E4]셀은 [C4]셀 값이 "대리"이면 [D4]셀의 앞 두 자리를 "D1"으로 고쳐라는 것.

- [G4]셀의 경우 : [F4]셀에서 "("를 찾아서 모두 빈칸("")으로 바꾸고, ")"를 찾아서 모두 "-"로 바꿔라는 것

(02)9231-2054 → 02-9231-2054




저작자 표시 동일 조건 변경 허락
신고

1. 데이터 유형 검사하기


  1) IS 함수의 개요

○ 정의 : IS로 시작되는 정보 함수. 값의 유형을 검사하고 그 결과에 따라 TRUE · FALSE를 반환하는 함수

○ 구문 =함수명(Value) 

○ 인수

▷ Value : 필수 요소, 테스트 할 값, 빈 셀, 오류, 논리값, 텍스트, 숫자, 참조 값 또는 이러한 항목을 가리키는 이름


  2) IS 함수의 종류


함수

TRUE를 반환하는 경우 

ISBLANK

  값이 빈 셀을 참조하는 경우

ISERR

  값이 #N/A(사용할 수 없는 값)를 제외한 오류 값을 참조하는 경우

ISERROR

  값이 임의의 오류 값을 참조하는 경우

ISLOGICAL

  값이 논리값을 참조하는 경우

ISNA

  값이 #N/A(사용할 수 없는 값) 오류 값을 참조하는 경우

ISNONTEXT

  값이 텍스트가 아닌 항목을 참조하는 경우

  빈 셀을 참조하는 경우 → TRUE를 반환

ISNUMBER

  값이 숫자를 참조하는 경우

ISREF

  값이 참조를 참조하는 경우

ISTEXT

  값이 텍스트를 참조하는 경우


<사용 예시>

[G4]셀의 경우 : [F4]셀 데이터 유형이 문자인지 비교 → False → D4/F4 → 259.1

※ G4셀의 함수 구문은 F4가 텍스트면 그대로, 텍스트가 아니면 D4/F4를 가져오라는 수식

[I4] 셀의 경우 : [H4]셀 데이터가 있는 지 확인 → True → "A" 또는 "B"인지 확인 → True

"통과"

※ I4셀은 친절도열 조사(비어있으면 "재조사", A나 B는 "통과", 나머지는 "미달"로 표시하라는 수식)




2. 파생된 참조 영역 사용하기


  1) OFFSET 함수

○ 정의 : 셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환하는 함수 

- 반환되는 참조는 단일 셀 또는 셀 범위 

- 반환할 행 및 열 수 지정 가능

○ 구문 =OFFSET(Reference, Rows, Cols, [Height], [Width])

○ 인수

▷ Reference : 필수 요소, 기본 참조 영역으로 셀 또는 인접한 셀 범위

▷ Rows : 필수 요소, 기본 참조 영역의 첫 행과 출력할 영역의 첫 행 사이의 간격

양수 → 아래로 지정, 음수 → 위로 지정 가능

예) Rows 인수로 5 사용 : 기본 참조 영역보다 5행 아래로 지정됨 

▷ Cols : 필수 요소, 기본 참조 영역의 첫 열과 출력할 영역의 첫 열 사이의 간격

양수 → 오른쪽으로 지정, 음수 →왼쪽으로 지정

예) Cols 인수로 5 사용 : 기본 참조 영역보다 5열 오른쪽으로 지정됨

▷ Height : 선택 요소, 반환되는 참조의 높이(행 수), 양수만 지정 가능

▷ Width : 선택 요소, 반환되는 참조의 너비(열 수), 양수만 지정 가능

○ 특징

▷ Rows 및 Cols 오프셋이 워크시트 가장자리 위를 참조하는 경우 #REF! 오류 값을 반환

▷ Height 또는 Width를 생략하면 높이나 너비가 Reference와 같은 것으로 간주

▷ OFFSET은 셀을 실제로 이동하거나 선택을 변경하지 않으며 단지 참조를 반환


<사용 예시>

[B19]셀의 경우 : 기준이 되는 A2셀에서 행으로(아래로) 1칸, 열로(오른쪽으로) 1칸 이동한 B3셀부터 높이(아래) 6칸, 폭이(오른쪽) 5칸이 되는 범위를 모두 더하라는 식이다.



3. 행과 열을 바꾸어 표시하기


  1) TRANSPOSE 함수

○ 개념 : 세로 셀 범위 → 가로 범위, 가로 셀 범위→ 세로 범위로 바꾸어 반환하는 함수 

배열 수식으로 입력해야함.

이를 입력하는 범위의 행 및 열 수는 각각 범위의 열 및 행 수와 동일해야 함

워크시트에서 배열의 가로와 세로 방향 변환 가능

○ 구문 =TRANSPOSE(Array)

○ 인수

▷ Array : 필수 요소, 행과 열을 바꿀 워크시트의 셀 범위 또는 배열 

▷ 배열의 첫 번째 행 → 새 배열의 첫 번째 열

▷ 배열의 두 번째 행 → 새 배열의 두 번째 열


<사용 예시>

[B14:H15]셀의 경우 : [F4:G10] 셀 범위 → 행/열 변환

  


4. 응용하기


 ○ 동적 범위 이름 지정하기

  이름 정의에서 OFFSET 함수를 사용하면 됩니다. 

  이 때, 포인트가 되는 부분은 셀 범위의 전체 행 수를 지정하는 Height 인수입니다. 

  여기서는 A열에 제목이 입력되어 있으므로 B열에 데이터가 입력된 셀의 개수를 COUNTA 함수를 사용해서 구한 후 인수로 사용하면 됩니다. 

  따라서 Reference 인수는 데이터 목록이 시작되는 '$A$3', 시작할 행과 열 위치는 [A3]에서 변경이 없으므로 Rows와 Cols 인수값은 둘 다 '0'이 됩니다. 데이터의 행 수인 Height는 COUNTA($B:$B)로 구하고, Width는 데이터 목록의 열이 모두 8개이므로 '8'이 됩니다. 

  이 수식은 이름 정의에서 사용할 것이므로 셀 참조는 모두 절대 번지로 지정해야 합니다. 따라서 이름 정의에서 참조 대상으로 사용할 수식은 '=OFFSET($A$3,0,0,COUNTA($B:$B),8)'이 됩니다. 



① [수식] 탭을 선택한 후, [정의된 이름] 그룹에서 [이름 정의] 아이콘을 클릭합니다.



② [새 이름] 대화상자에서 '이름'에 '데이터'를 입력하고 '참조 대상'에 '=OFFSET($A$3,0,0,COUNTA($B:$B),8)'을 입력한 후, <확인> 단추를 클릭합니다.



③ 데이터베이스 함수를 사용한 [J9]셀에서 데이터베이스 범위로 입력된 'A3:H16' 부분을 정의된 이름인 '데이터'로 변경합니다.



④ 다음과 같이 15번 데이터를 추가하면 이름으로 정의된 범위가 자동으로 변경되어 추가된 데이터가 수식에 적용되는 것을 확인할 수 있습니다.



 




저작자 표시 동일 조건 변경 허락
신고