9월 22 2015
엑셀(EXCEL) – 엑셀에서 실시간 철자 검사(Spelling Check)하면서 입력하는 방법
요사이 워드보다 더 워드 같은 엑셀을 사용하여 내용, 표, 그래프 등을 배치, 자유자재로 쓸 수 있는 엑셀을
선호하는 분들이 많으신 것 같더군요. 마이크로소프트의 Office Suite에서 Word는 실시간으로 철자 체크가
가능하지만 엑셀에서는 아직까지는 되지않는 것으로 알고 있습니다.
그래서 열심히 구걸했습니다. 언제나 세상에는 필요한 사람이 있고 그것을 해결하는 천재적인 분들이 많이
계시더군요. 외국 자료라 한글에 대한 옵션 부분이 들어가 있지 않아서 제가 수정해서 팁으로 올립니다.
딱히 설명할 내용도 없습니다. 시트의 셀의 내용이 변하면 전체를 Spellcheck Engine을 사용해서 True이면
그냥 입력하고 False이면 그 부분을 붉은색으로 글꼴을 바꾸는 것입니다. 다른 형식들을 지정해도 상관이
없지만 철자를 교정하고 나도 형식지정된 부분이 남아서 색깔이나 형식 등 하나만을 사용해서 표시를 해야
수정 후 원래 형식대로 돌아갈 수 있습니다.
그래서 별도로 전체 시트에 대해 철차 체크를 하는 루틴을 만들어 두었습니다. 실시간이 아닌 기존 입력된
시트의 철자 체크를 하시려면 Spell_Correction Routine을 한 번 실행하시면 오타나 잘못된 부분을 붉은색으로
변경해 줍니다. 물론 예시로 올린 그림에서 왼쪽 상단의 맞춤법검사 버튼을 눌러 실시간으로 확인하면서
수정할 수 있지만 이 루틴을 돌리면 틀린 부분을 바로 알 수 있어 수작업으로 바로 바로 바꿀때 사용하세요.
VBA Editor 여시고 Module 하나 삽입하시고 아래 코드 붙여넣기 합니다.
Option Explicit
Sub View_Color_Spell_Check_Err(SelRng As Range)
Dim Rng As Range
Dim arr() As String
Dim i As Long, j As Long
With Application.SpellingOptions
.IgnoreCaps = True
.IgnoreFileNames = True
.IgnoreMixedDigits = True
.KoreanCombineAux = True ‘ 이 부분 보조용언 붙여쓰기 추가
.KoreanProcessCompound = True ‘ 복합명사 처리 추가
End With
For Each Rng In SelRng
If Not Rng.HasFormula And VarType(Rng.Value) = vbString Then
arr = Split(Replace(Rng.Value, Chr(160), ” “), ” “)
j = 1
Rng.Font.ColorIndex = xlColorIndexAutomatic
For i = 0 To UBound(arr)
If Not Application.CheckSpelling(Word:=arr(i)) Then
Rng.Characters(j, Len(arr(i))).Font.ColorIndex = 3
End If
j = j + 1 + Len(arr(i))
Next i
End If
Next Rng
End Sub
Sub Spell_Correction()
Dim Rng As Range, SelRng As Range
Dim arr() As String
Dim i As Long, j As Long
With Application.SpellingOptions
.IgnoreCaps = True
.IgnoreFileNames = True
.IgnoreMixedDigits = True
.KoreanCombineAux = True ‘ 이 부분 보조용언 붙여쓰기 추가
.KoreanProcessCompound = True ‘ 복합명사 처리 추가
End With
‘ 현재 시트의 입력된 부분 선택
Set SelRng = ActiveSheet.UsedRange
‘ 셀에 지정된 형식 리셋
SelRng.ClearFormats
For Each Rng In SelRng
If Not Rng.HasFormula And VarType(Rng.Value) = vbString Then
arr = Split(Replace(Rng.Value, Chr(160), ” “), ” “)
j = 1
Rng.Font.ColorIndex = xlColorIndexAutomatic
For i = 0 To UBound(arr)
If Not Application.CheckSpelling(Word:=arr(i)) Then
Rng.Characters(j, Len(arr(i))).Font.ColorIndex = 3
End If
j = j + 1 + Len(arr(i))
Next i
End If
Next Rng
End Sub
그리고 문서 입력용 시트(예, Sheet1을 VBA Editor에서 더블 클릭하시고 아래 코드를 붙여 넣습니다.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Call View_Color_Spell_Check_Err(Target)
End Sub
이제 셀에 입력을 할 때 마다 맞춤법 검사를 하면서 오타나 복합명사, 보조용언이 잘못되면 셀이 단어가
아닌 문장이라도 잘못된 부분을 붉은색으로 보여줍니다. 색상을 필요하시면 ColorIndex를 바꾸어 가면서
편한 색상으로 사용하시면 됩니다.
위 코드를 보시면 아시겠지만 Sheet1의 내용이 변하면 지속적으로 체크를 하므로 대용량으로 삽입, 삭제 등의
변경이 일어나면 컴퓨터가 느려지므로 순수하게 Sheet1은 입력, 철자 체크용으로 사용 후 새 시트로 이동해서
문서 작업을 하시면 좋겠습니다.
제가 직접 만들 것이 아니라 팁으로 올리기는 그렇지만 수정, 보완을 해서 올리고 이 글을 읽는 또 다른 분이
조금이라도 업무에 도움이 되었으면 하는 마음에 올립니다.
첨부 화일 : 20150921-엑셀에서 실시간 철자 검사(Spelling Check)
10월 2 2015
엑셀(EXCEL) – 두 영역 비교하여 같은 값 추출 및 시트별로 분리
추석 연휴로 인해 며칠 지나지 않았는데도 벌써 내일 주말이 되었습니다. 별다른 일도 없고 해서
저번 아질게에 올라온 다중 영역 필터링에 대해 글을 쓰게 되었는데 아질게에 아무리 찾아보아도
관련 글이 보이지 않네요. 이제 늙으니 기억력에 문제가 생기는 …
저번에 팁란에 각 시트를 통합하고 그 통합된 시트에서 특정 데이터를 추출하는 법을 올렸습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=286941
(엑셀(EXCEL) – 시트 통합, 월간년간보고서 작성 및 특정 자료(대리점) 추출)
이제 그 반대로 두 영역의 데이터를 상호 비교하여 같은 것이 있으면 추출하는 방법을 보겠습니다.
추출할 데이터가 몇 가지 되지 않으면 그냥 필터링을 해서 추가하고 또 필터링해서 추가하고 하면
되지만 예제에는 속도를 위해 몇 가지 데이터밖에 넣지를 않았지만 비교할 데이터가 몇백개 이상
넘어가면 수작업으로는 무리가 있습니다.
이제 VBA Editor 여시고 Module하나 만드시고 아래 코드를 복사하여 붙여넣기합니다.
코드 중간중간에 주석 달아놓았으니 필요한 부분을 수정, 첨삭하시어 사용하기 바랍니다.
한 루틴은 데이터를 비교하여 한 시트에 모아 주는 루틴이고 한 루틴은 각 데이터별로 시트를
만들어 분리해 주는 루틴입니다.
Option Explicit
Sub Ext_Same_Value()
Dim cnt As Integer
Dim rngCell As Range, rngTgt As Range
Dim rngR As Range, rngRef As Range
Dim tgt As Worksheet
‘ 경고 메시지 금지 및 속도를 위해 업데이트 중지
Application.DisplayAlerts = False
Application.ScreenUpdating = False
‘ 추출해서 붙여넣기할 데이터 시트가 기존에 있으면 삭제
For Each tgt In Worksheets
If tgt.Name = “ExtData” Then
tgt.Delete
End If
Next tgt
‘ 새 시트를 워크시트 제일 마지막에 ExtData라는 이름으로 추가
Set tgt = Worksheets.Add(after:=Worksheets(Worksheets.Count))
tgt.Name = “ExtData”
‘ 추출할 데이터 영역과 추출할 참조 영역 설정
Set rngTgt = Worksheets(“Data”).Range(“B:B”).SpecialCells(xlTextValues)
Set rngRef = Worksheets(“Ref”).Range(“A:A”).SpecialCells(xlTextValues)
‘ 두 영역을 순환하면서 같은 데이터가 있으면 추출 시트에 복사
For Each rngCell In rngTgt
For Each rngR In rngRef
If rngCell = rngR Then
tgt.Range(“A1”).Offset(cnt, 0).Value = rngCell.Offset(0, -1).Value
tgt.Range(“A1”).Offset(cnt, 1).Value = rngCell.Offset(0, 0).Value
tgt.Range(“A1”).Offset(cnt, 2).Value = rngCell.Offset(0, 1).Value
tgt.Range(“A1”).Offset(cnt, 3).Value = rngCell.Offset(0, 2).Value
cnt = cnt + 1
Exit For
End If
Next rngR
Next rngCell
‘ 보기 좋게 자동 칼럼 맞춤
tgt.Columns.AutoFit
‘ 경고 메시지 및 업데이트 갱신
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sub Ext_Same_Value_To_Sheet()
Dim cnt As Long
Dim rngCell As Range, rngTgt As Range
Dim rngR As Range, rngRef As Range
Dim tgt As Worksheet
‘ 데이터 영역과 참조 영역 선택
Set rngTgt = Worksheets(“Data”).Range(“B:B”).SpecialCells(xlTextValues)
Set rngRef = Application.InputBox(“분리할 참조 영역 선택”, Type:=8)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets(“Ref”).Activate
For Each rngR In rngRef
‘ 추출할 데이터의 시트가 있으면 삭제
For Each tgt In Worksheets
If tgt.Name = rngR Then
tgt.Delete
End If
Next tgt
‘ 추출할 데이터의 시트를 데이터 이름으로 추가
Worksheets.Add after:=ActiveSheet
ActiveSheet.Name = rngR
‘ 제목행 만듦
With Range(“A1”)
.Offset(0, 0).Value = Sheets(“Data”).Range(“A1”).Offset(0, 0).Value
.Offset(0, 1).Value = Sheets(“Data”).Range(“A1”).Offset(0, 1).Value
.Offset(0, 2).Value = Sheets(“Data”).Range(“A1”).Offset(0, 2).Value
.Offset(0, 3).Value = Sheets(“Data”).Range(“A1”).Offset(0, 3).Value
End With
‘ 순환하면서 같은 데이터가 있으면 새로 만든 시트에 추가
For Each rngCell In rngTgt
If rngR = rngCell Then
cnt = cnt + 1
With Range(“A1”)
.Offset(cnt, 0).Value = rngCell.Offset(0, -1).Value
.Offset(cnt, 1).Value = rngCell.Offset(0, 0).Value
.Offset(cnt, 2).Value = rngCell.Offset(0, 1).Value
.Offset(cnt, 3).Value = rngCell.Offset(0, 2).Value
End With
End If
Next rngCell
cnt = 0
Columns.AutoFit
Next rngR
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
분리해야할 참조 영역이 정해져 있지 않아 특정 데이터 영역에서 추출해야하는 경우에는 아래의
팁을 응용하여 분리할 참조 영역을 중복되지않게 추출하고 그 추출된 데이터를 이용하여 두 영역을
비교하여 필요한 작업을 하시면 추출 관련해서는 이 두 팁을 이용하면 못할 것이 없을 것입니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=286263
(엑셀(EXCEL) – 임의 영역 중복항목 제거 루틴을 응용한 다대다(多對多) 항목 역전개(기준항목
변경))
첨부 화일 : 20151002-두 영역 비교하여 같은 값 추출 및 시트별로 분리
By vinipapa • 무른모 • 0 • Tags: 시트 분리, 엑셀, 영역비교 추출, 팁