Microsoft Excel에서 작업하다 보면 특정 셀이 언제, 어떻게 변경되었는지 추적해야 하는 경우가 종종 있습니다. 엑셀 수식과 VBA 코드를 활용하면 복잡한 설정 없이도 셀 변경 이력을 빠르고 편리하게 확인할 수 있습니다. 이 글에서는 수식을 이용해 엑셀에서 셀 변경 사항을 추적하는 방법을 단계별로 자세히 알아보겠습니다.
아래 링크에서 실습용 워크북을 다운로드하여 직접 따라 해볼 수 있습니다.
방법 1. CELL 함수로 마지막 변경 셀 추적하기 (3단계)
대형 워크시트를 다루다 보면 가장 최근에 수정한 셀이 어디인지 찾기 어려울 때가 많습니다. 또한 여러 명이 함께 작업하는 문서에서는 동료가 마지막으로 어떤 셀을 수정했는지 알아야 할 필요도 있습니다. 이럴 때 엑셀의 CELL 함수를 사용하면 마지막으로 작업한 셀을 손쉽게 추적할 수 있습니다.
여기서는 직원들의 근무 시간(Work Hours)과 일당(Daily Pay)이 담긴 데이터셋이 있다고 가정해 보겠습니다. 데이터 중 하나를 수정하고 그 변경 사항을 추적하는 과정을 아래 순서대로 진행해 보겠습니다.
1단계: 셀 주소를 추적하는 수식 만들기
- 먼저 마지막으로 변경된 셀 주소를 표시할 셀을 선택합니다. 여기서는 G4 셀을 선택합니다.
- 그다음 아래 수식을 입력합니다.
=CELL("address")이 수식에서 address는 CELL 함수의 info_type(정보 유형) 인수입니다.
2단계: 셀 내용을 추적하는 수식 입력하기
- 이번에는 마지막으로 변경된 셀의 내용을 추적할 셀을 선택합니다. 여기서는 G5 셀입니다.
- 다음 수식을 입력합니다.
=CELL("contents")여기서 contents 역시 CELL 함수의 info_type 인수입니다.
3단계: 파일 주소를 추적하는 수식 만들기
- 마지막으로 파일 주소를 표시할 셀을 선택합니다. 여기서는 G8 셀입니다.
- 해당 셀에 아래 수식을 입력합니다.
=CELL("filename")여기서 filename은 CELL 함수의 info_type 인수입니다.
💡 결과 해석:
모든 수식을 적용한 후, C5 셀에 있는 Adam의 근무 시간을 10에서 12로 변경해 보겠습니다. 그러면 다음과 같은 변화가 나타납니다.
- 첫째, G4 셀에 마지막으로 수정한 셀 주소인 $C$5가 표시됩니다.
- 둘째, G5 셀에는 마지막으로 입력된 값인 12가 표시됩니다.
- 셋째, G6 셀은 파일 위치가 변하지 않았기 때문에 그대로 유지됩니다.
방법 2. VBA 코드로 수식 셀의 변경 사항 추적하기
이번에는 하나의 값을 수정했을 때 여러 개의 셀이 연달아 바뀌는 경우를 생각해 보겠습니다. 어떤 값 하나가 변경되었을 때 구체적으로 어느 셀들이 영향을 받는지 셀 색상으로 시각적으로 추적하고, 확인 후에는 색상을 지우고 싶다면 VBA 매크로가 가장 효과적인 방법입니다.
아래 예제 데이터셋에서는 시급 중 하나라도 변경되면 여러 셀 값이 함께 변경됩니다. 여기서 1교대 시급(1st Class Hourly Pay)을 수정할 때 어떤 셀이 바뀌는지 추적하고, 변경된 셀은 노란색으로 표시하려고 합니다. 아래 단계를 따라 해보세요.
진행 단계:
- 먼저 아래 스크린샷처럼 Reset(초기화) 버튼과 Track Change(변경 추적) 버튼으로 사용할 도형 두 개를 삽입합니다.
- 다음으로 ALT + F11 키를 눌러 VBA 편집기 창을 엽니다.
- 작업 중인 Sheet 4(또는 해당 시트)를 마우스 오른쪽 버튼으로 클릭합니다.
- 삽입(Insert) > 모듈(Module)을 선택합니다.
- 아래 코드를 복사해서 모듈의 빈 공간에 붙여넣습니다.
'This code will delete conditional formatting for all cells
Sub DeleteFormat()
Cells.FormatConditions.Delete
End Sub
'This Code will Track the Cell Changes
Sub TrackCellChange()
For Each sheetw In ActiveWorkbook.Worksheets
sheetw.Cells.FormatConditions.Delete
Err.Clear
On Error Resume Next
x = sheetw.Cells.SpecialCells(xlCellTypeFormulas, 23).Count
If Err.Number = 0 Then
For Each cell In sheetw.Cells.SpecialCells(xlCellTypeFormulas, 23)
cell.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=" & cell.Value
cell.FormatConditions(cell.FormatConditions.Count).SetFirstPriority
With cell.FormatConditions(1).Interior
.Color = 2552550
.TintAndShade = 0
End With
cell.FormatConditions(1).StopIfTrue = False
Next cell
End If
Next sheetw
End Sub
이 코드에서 DeleteFormat() 서브 프로시저는 통합 문서의 모든 조건부 서식을 삭제하는 매크로를 생성합니다. 그리고 TrackCellChange() 서브 프로시저는 값이 변경된 셀의 색상을 바꾸는 또 다른 매크로를 만듭니다. 워크시트의 모든 셀을 순회하기 위해 For 루프를 사용했으며, 코드의 2552550이라는 숫자는 RGB 함수 기준으로 노란색(Yellow)을 의미합니다.
- 코드를 저장한 뒤 엑셀 파일로 돌아갑니다.
- Reset 도형을 마우스 오른쪽 버튼으로 클릭하고 매크로 지정(Assign Macro)을 선택합니다.
- 매크로 지정 대화 상자에서 DeleteFormat 매크로를 선택합니다.
- 확인(OK)을 클릭합니다.
- 같은 방식으로 Track Change 도형에 TrackCellChange 매크로를 지정합니다.
- 이후 Track Change 버튼을 클릭합니다.
- 이제 1교대 시급을 변경해 봅니다. 여기서는 $20에서 $25로 바꿔 보겠습니다.
- 그러면 값이 변경된 C8, C11, C14 셀이 자동으로 노란색으로 표시됩니다.
- 변경된 셀의 색상을 지우고 싶다면 Reset 버튼을 클릭하세요. 아래와 같이 원래 상태로 초기화됩니다.
알아두면 좋은 팁
- CELL 함수는 가장 최근에 변경된 셀 하나만 추적할 수 있다는 점에 유의하세요.
- CELL 함수를 적용한 후에는 수식 탭의 조건식 감시 창(Watch Window)에 G4, G5, G6 셀을 추가하면 변경 사항을 실시간으로 모니터링할 수 있습니다.
- 실습 파일을 다운로드하면 파일 위치가 달라 아래 스크린샷처럼 #N/A 오류가 표시될 수 있습니다. 이 경우 수식이 들어 있는 셀을 클릭한 후 ENTER 키만 누르면 간단히 해결됩니다.
마치며
지금까지 엑셀에서 CELL 함수와 VBA 코드를 활용해 셀 변경 사항을 추적하는 두 가지 방법을 살펴봤습니다. 이 가이드가 여러분이 찾던 해답이 되었기를 바랍니다. 궁금한 점이 있다면 아래에 댓글로 남겨주세요. 더 많은 엑셀 활용 팁이 궁금하다면 저희 웹사이트 ExcelDemy를 방문해 보세요.
함께 읽으면 좋은 글
- 엑셀에서 누가 무엇을 수정했는지 확인하는 방법 (6가지 쉬운 방법)
- [해결됨]: 엑셀에서 변경 내용 추적이 회색으로 비활성화될 때 (3가지 빠른 해결책)
- 엑셀에서 변경 내용 추적 기능 활성화하기 (사용자 지정 옵션 포함)
- [해결됨]: 엑셀에서 변경 내용 추적이 표시되지 않을 때 (간단한 해결 방법)