xls - 사용자정의 셀서식, 셀스타일 편하게 설정하기
셀서식, 표시형식, 셀스타일
결국 같은 셀의 포맷팅이지만, 단어가 다르니, 개념을 살펴본다.
- 셀서식(cell format, format cells)
- 표시형식(number format)
- 사용자정의 표시형식(custom number format)
- 셀스타일(cell style)
Cell Format (셀서식)
├─ Number Format (표시형식)
│ └─ Custom Number Format (사용자정의 표시형식)
├─ Font
├─ Border
├─ Fill
├─ Alignment
└─ Protection
Cell Styles (셀스타일)
└─ Custom Cell Styles (사용자정의 셀스타일)
흔히 사용자정의 셀서식이라고 불리운 것은 사용자정의 표시형식이라고 해야 더 정확한 뜻인 셈이다. 응용 사례 위주로 살펴본다.
사용자정의 표시형식
기존에는 몇가지 서식에 대하여, 매크로 버튼을 만들어 사용하고 있었다.
불행하게도 이는 TEXT 함수 안에 적용하기가 어려워… 같은 셀 안에 두개의 서식을 사용하기가 어렵다.
+114
(+2%)
TEXT 함수를 써서 간단하게 해결할 수 있기는 하지만, 매번 표시형식을 직접 적어주는 건 매우 귀찮은 일이다.
이 문제를 해결하기 위해 몇 가지 방법을 고민해 보았다.
#1 VBA로 MDF 만들기
자주 쓰는 서식을 몇가지 MDF 로 저장해두고, 문자 값으로 반환하도록 하자.
숫자를 천단위나, 백만단위 등으로 표기하거나, 퍼센트를 기재해주는 방식이다. 양수나 음수를 구분할수 있도록 적어뒀다.
sample.bas
Function myfmt(fmtName As String) As String
Select Case UCase$(Trim(fmtName))
Case "일"
myfmt = "#,##0;[Red]-#,##0;-"
Case "천"
myfmt = "#,##0,;[Red]-#,##0,;-"
Case "백만"
myfmt = "#,##0,;[Red]-#,##0,;-"
Case "일_회"
myfmt = "#,##0_-;[Red]-#,##0_-;-_-"
Case "천_회"
myfmt = "#,##0,_-;[Red]-#,##0,_-;-_-"
Case "백만_회"
myfmt = "#,##0,,_-;[Red]-#,##0,,_-;-_-"
Case "일_회_기"
myfmt = "+#,##0_-;[Red]-#,##0_-;-_-"
Case "천_회_기"
myfmt = "+#,##0,_-;[Red]-#,##0,_-;-_-"
Case "백만_회_기"
myfmt = "+#,##0,,_-;[Red]-#,##0,,_-;-_-"
Case "퍼"
myfmt = "0%;[red]0%;-"
Case "퍼_기"
myfmt = "+0%;[red]-0%;-"
Case Else
myfmt = "@"
End Select
End Function
그런데 .. MDF를 사용자 함수를 XLAM 파일로 사용하더라도 내가 파일을 다른사람에게 전달을 하면 함수가 무용지물이 된다.
다른 방법을 찾아야 한다.
#2 이름정의로 서식 지정하기
이름정의는 다재다능하다. 특히 불필요한 참조시트나 테이블을 생성하고 싶지 않을 때 유용한데.. 서식을 반환하는 이름정의를 걸어두면 찾기도 편하고, 불필요한 함수를 호출할 필요도 없다.
사용하기는 무지 간단한데, 대상영역에 서식을 넣어주기만 하면 된다.
문제는 위와 비슷하지만 다른데, 같은 파일을 다른 사람에게 넘겨줄때는 문제가 아니지만, 내가 새로운 파일을 사용할 떄는 또 이름정의를 해야하는 수고로움이 있다.
간단히 매크로를 짜서 필요한 경우, 서식을 이름정의로 옮겨주면 된다. 셋을 한번만 짜두면 두고두고 사용이 가능하다는 장점이 있다.
내가 지정한 사용자 서식은 숫자나 백분율에 관한 부분이다.
sample.bas
Option Explicit
' 1) 여기에서 "사용자 서식 리스트"만 관리하면 됩니다.
' 형식: Array( Array("이름정의명", "서식문자열"), ... )
Private Function USER_FORMAT_LIST() As Variant
USER_FORMAT_LIST = Array( _
Array("_서식_숫자_일", "#,##0;[Red]-#,##0;-"), _
Array("_서식_숫자_천", "#,##0,;[Red]-#,##0,;-"), _
Array("_서식_숫자_백만", "#,##0,,;[Red]-#,##0,,;-"), _
Array("_서식_숫자_일_회계", "#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서식_숫자_천_회계", "#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서식_숫자_백만_회계", "#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서식_숫자_일_회계_기호", "+#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서식_숫자_천_회계_기호", "+#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서식_숫자_백만_회계_기호", "+#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서식_백분율", "0%;[red]0%;-"), _
Array("_서식_백분율_기호", "+0%;[red]0%;-") _
)
End Function
' 2) 실행하면 ActiveWorkbook에 이름정의로 등록(중복이면 덮어쓰기)
Public Sub Register_UserFormats_ToNames()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim items As Variant, it As Variant
Dim nm As String, fmt As String, refersToFormula As String
items = USER_FORMAT_LIST()
For Each it In items
nm = CStr(it(0))
fmt = CStr(it(1))
' 이름정의의 RefersTo는 "수식"이어야 하므로 텍스트는 ="..." 형태로 저장
' 큰따옴표(")가 서식문자열에 들어갈 경우를 대비해 이스케이프 처리
fmt = Replace(fmt, """", """""")
refersToFormula = "=""" & fmt & """"
' 중복이면 무조건 덮어쓰기: 있으면 삭제 후 재등록
On Error Resume Next
wb.Names(nm).Delete
On Error GoTo 0
wb.Names.Add Name:=nm, RefersTo:=refersToFormula
Next it
MsgBox "사용자 서식 리스트를 이름정의로 등록 완료", vbInformation
End Sub
프로시저를 실행하면 다음과 같이 사전 설정한 사용자서식이 이름정의에 등록된다.
TEXT 함수 사용시, 리스트형태로 사전 설정한 사용자 서식이 보이기 때문에, 헤깔릴 위험도 줄어든다.
적용된 결과는 다음과 같다.
TEXT 함수이기 때문에 CHAR(10)과 함께 두줄을 표기하고, 각 줄에 다른 서식을 적용하는 것도 가능하다.
#3 이름정의의 한계, 어쩔수 없이 사용해야하는 셀스타일
이름정의는 TEXT 함수를 쓰기 위함이었는데, TEXT 함수를 사용하니, 문제가 있다. 바로, TEXT 함수의 반환값이 TEXT가 되버린다는 것이다.
즉, 1000000 을 백만단위 표기로 바꾸면 1이 되는데, 표시형식 뿐 아니라 값까지 1이 되어버린다는 것이다. 즉, 연산을 하게 되면, 1백만이 아니라 1이 연산이 된다.
그래서…
맨 위의 예시처럼 두줄에 각각의 연산을 하는 상황이 아니라면 셀스타일에 등록을 하는 것이 낫다.
이 또한 귀찮으니, VBA에 추가를 하면 다음과 같다.
최종 프로시저는 Register_UserFormats_ToNames 가 된다.
이름이 길면 셀스타일에 나오지 않기 때문에 “서….” 로 시작하도록 축약해서 적었다.
sample.bas
Option Explicit
' 1) 여기에서 "사용자 서식 리스트"만 관리하면 됩니다.
' 형식: Array( Array("이름정의명", "서식문자열"), ... )
Private Function USER_FORMAT_LIST() As Variant
USER_FORMAT_LIST = Array( _
Array("_서_일", "#,##0;[Red]-#,##0;-"), _
Array("_서_천", "#,##0,;[Red]-#,##0,;-"), _
Array("_서_백만", "#,##0,,;[Red]-#,##0,,;-"), _
Array("_서_일_회", "#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서_천_회", "#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서_백만_회", "#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서_일_회_기", "+#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서_천_회_기", "+#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서_백만_회_기", "+#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서_날", "YYYY-MM-DD"), _
Array("_서_날_요", "YYYY-MM-DD(aaa)"), _
Array("_서_율", "0%;[red]0%;-"), _
Array("_서_율_기", "+0%;[red]0%;-") _
)
End Function
' 2) 실행하면 ActiveWorkbook에 이름정의로 등록(중복이면 덮어쓰기)
Public Sub Register_UserFormats_ToNames()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim items As Variant, it As Variant
Dim nm As String, fmt As String, refersToFormula As String
items = USER_FORMAT_LIST()
For Each it In items
nm = CStr(it(0))
fmt = CStr(it(1))
' 이름정의의 RefersTo는 "수식"이어야 하므로 텍스트는 ="..." 형태로 저장
' 큰따옴표(")가 서식문자열에 들어갈 경우를 대비해 이스케이프 처리
fmt = Replace(fmt, """", """""")
refersToFormula = "=""" & fmt & """"
' 중복이면 무조건 덮어쓰기: 있으면 삭제 후 재등록
On Error Resume Next
wb.Names(nm).Delete
On Error GoTo 0
wb.Names.Add Name:=nm, refersTo:=refersToFormula
Next it
MsgBox "사용자 서식 리스트를 이름정의로 등록 완료", vbInformation
Call Create_NumberOnly_Styles_From_Names
End Sub
Private Function BaseName(ByVal fullName As String) As String
If InStr(fullName, "!") > 0 Then
BaseName = Split(fullName, "!")(1)
Else
BaseName = fullName
End If
End Function
Private Function ExtractFormatString(ByVal refersTo As String) As String
Dim s As String
s = refersTo ' 예: ="#,##0,,"
If Left$(s, 2) = "=""" And Right$(s, 1) = """" Then
s = Mid$(s, 3, Len(s) - 3)
s = Replace(s, """""", """")
ExtractFormatString = s
Else
ExtractFormatString = ""
End If
End Function
Public Sub Create_NumberOnly_Styles_From_Names()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim n As Name
Dim styleName As String, fmt As String
Dim st As Style
For Each n In wb.Names
styleName = BaseName(n.Name)
If Left$(styleName, 3) = "_서_" Then
fmt = ExtractFormatString(n.refersTo)
If fmt = "" Then GoTo NextName
' 기존 스타일 삭제
On Error Resume Next
wb.Styles(styleName).Delete
On Error GoTo 0
' 스타일 생성
Set st = wb.Styles.Add(Name:=styleName)
' ?? 포함 항목 제어 (핵심)
With st
.IncludeAlignment = False
.IncludeFont = False
.IncludeBorder = False
.IncludePatterns = False ' Fill
.IncludeProtection = False
.IncludeNumber = True ' ← 이것만 포함
.NumberFormat = fmt
End With
End If
NextName:
Next n
MsgBox "Number만 포함된 스타일 생성 완료", vbInformation
End Sub
다음과 같이 셀 스타일에 등록이 된 것을 확인할 수 있다. 이제 이름정의로 TEXT 함수를 사용하거나, Custom 셀스타일을 적용할 수 있다.
사용을 위한 프로세스는 다음과 같다.
→ 엑셀 호출시 불러들일 xlam 파일을 xlstart 폴더에 저장 (이름 자유)
→ 이름이 macro.xlam 이면, 해당 파일에 위에 적은 코드를 신규 프로시저로 복사 후 저장, 리본메뉴에 버튼 등록.
→ 해당 파일이 엑셀 시작될때마다, 뜰 것이고, 필요한 workbook 활성화 상태에서 리본메뉴에서 버튼을 클릭하면 이름정의로 저장
#4 새문서에 셀스타일 자동 정의 하기
xlxt 파일을 사용해서 새문서에 자동으로 셀스타일을 적용하는 방식이다. 특히 셀스타일을 자주 건드려야하는 일을 하고 있다면 추천할만한 기능이다. 방법은 간단하다.
→ 새문서를 켠다.
→ 원하는 셀서식을 미리 저장한다.
→ XLXT 형식으로 저장을 한다.
→ XLSTART 폴더에 저장을한다.
홈 > 스타일 리본 메뉴에 아래쪽 화살표를 누르면 아래와 같이 나오는데, 새 셀 스타일을 눌러준다.
스타일 작성 창이 나오고, 내가 원하는 스타일을 작성해 준다. 방식은 셀서식 설정과 동일하며, 어떤 항목을 저장해줄지 선택도 가능하다.
저장을 했으면, XLTX파일로 저장을하고, XLSTART폴더를 찾아서 넣어준다. XLSTART 폴더는 아래 경로 정도에 있다.(버전마다 OFFICE12 인지 14인지 다를 것임)
저장을 할때는 book.xltx로 파일이름을 바꿔야한다는 점. 반드시 기억하자.
다시 엑셀 새문서를 만들어 보면 사용자 지정 스타일이 들어와 있음을 확인할 수 있다.
주의해야할 점은 새문서를 만들때만 나온다는 것이다. 기존 엑셀문서를 열기 하는 것이라면, 적용되지 않는다.
나는 회사에서 빈번하게 천단위나 백만단위로 표시 형식을 바꿀일이 있기 때문에, 스타일로 지정해 놓았다. 테두리를 사전 지정해서 테이블을 그릴 때 활용해도 괜찮을 것이다.
끝.
