Category»

Notice»

Statistics Graph
티스토리 모바일 블로그

 
 

1. 사용자 정의 함수 작성


1) 사용자 함수의 개념

○ 계산할 때 사용할 수 있도록 VBA를 통해서 사용자가 직접 제작하는 함수


2) 사용자 정의 함수 작성 방법

○ 사용자 정의 함수 = User Define Function, Function Procedure로 작성


▶ Function Procedure

 Function 함수명(인수)
    실행문

    ……

    함수명 = 결과값

 End Function


▶ 사용자 정의 함수의 예

 Function 주문수량(주문일, 판매평균, 재고량)
     Dim 수량 As Integer
     If Application.WorksheetFunction.Weekday(주문일) = 6 Then

          수량 = (판매평균 * 4) - 재고량

     Else

          수량 = (판매평균 * 2) - 재고량

     End If


     If 수량 < 0 Then

          주문수량 = 0

     Else

          주문수량 = 수량

     End If

 End Function




3) 사용자 정의 함수 활용 방법

○ [함수 마법사] 대화상자 실행 → [사용자 정의] 범주 선택


  


2. 사용자 정의 함수 설명 및 범주 지정


1) 사용자 정의 함수 관리하기

○ 사용자 정의 함수가 어떤 값을 계산하는 함수인지 설명해주는 도움말지정 가능

○ 함수의 범주를 ‘사용자 정의’가 아닌 다른 범주로 분리 가능


▶ MacroOptions 메서드의 내부 형식

 Application.MacroOptions (Macro, Description, , , HasShortcutKey, ShortcutKey, Category)

  • Macro : 매크로 이름 또는 사용자 정의 함수 이름
  • Description : 매크로 설명 지정
  • HasShortcutKey : TRUE 이면 매크로에 바로 가기 키가 할당되고, 

                         FALSE이면 매크로에 바로 가기 키가 할당되지 않음

  • ShortcutKey : HasShortcutKey가 TRUE 일 경우에 필수 요소로 바로 가기 키
  • Category : 기존의 매크로 함수 범주(재무, 날짜/시간, 수학/삼각, 사용자 정의 등)를 지정하는 정수로, 기본 제공 범주에 설정되어 있는 정수를 사용하거나 사용자 지정 범주에 대한 문자열도 지정 가능


▶ MacroOptions 메서드의 내부 형식 작성 예시


2) 추가 기능으로 사용자 정의 함수 사용하기

○ 추가 기능 : 엑셀 통합 문서가 열릴 때 숨겨진 파일로 함께 열려 어느 문서에서든 추가 기능에 있는 함수를 사용할 수 있게 하는 기능


▶ 추가기능으로 저장

▷ 다른 이름으로 저장 대화상자에서 파일 형식을 [Excel 추가 기능(*.xlam)] 으로 선택하여 저장


▶ 적용 단계

① [개발 도구] 탭 → [추가 기능] 그룹 → [추가 기능] 아이콘 클릭

② [추가 기능] 대화상자의 [사용 가능한 추가 기능] 목록에서 [사용자 정의 함수] 선택

  





저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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)

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






  


저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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




저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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  워크시트 내의 셀을 선택할 때 발생





저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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]키 누름




 


저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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) 빠른실행 도구 모음에 추가

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





저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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






저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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

 

 


저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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]셀의 텍스트에서 앞 뒤의 공백 문자 삭제 → 첫 부분 공백 삭제


 

 


저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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




저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 

티스토리 툴바