이 글에서는 VBA를 사용하여 Excel 워크시트의 수식을 제거하면서 값과 서식은 그대로 유지하는 방법을 알려드립니다. 먼저 선택한 셀 범위에서 수식을 제거하는 방법을 배우고, 이어서 전체 워크시트에서 수식을 제거하는 방법까지 단계별로 살펴보겠습니다.
핵심 코드 미리 보기
Sub Remove_Formulas_from_Selected_Range()
Dim Rng As Range
Set Rng = Selection
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub⧭ 코드 설명
- 이 코드는 Remove_Formulas_from_Selected_Range라는 이름의 매크로(Macro)를 생성합니다.
- 먼저 사용자가 선택한 범위 내 모든 셀의 값을 복사합니다.
- 그런 다음 서식은 그대로 유지한 상태로 해당 셀에 값을 붙여넣습니다.
- 결과적으로 선택한 범위에서 값과 서식을 유지하면서 모든 수식이 제거됩니다.
VBA로 값과 서식을 유지하며 수식 제거하는 2가지 방법
예시로 사용할 데이터는 Jupyter Group이라는 회사 직원들의 이름, 초기 연봉, 현재 연봉 정보입니다.
또한 워크시트의 별도 셀에는 평균 연봉, 최고 연봉자, 최저 연봉자 정보가 들어 있습니다.
여기서 각 직원의 현재 연봉은 초기 연봉보다 20% 증가한 금액입니다. 즉, D4 셀에는 다음 수식이 들어 있습니다.
=C4+(C4*20)/100
D5 셀에는 아래와 같은 수식이 들어 있으며, 이후 셀도 같은 방식으로 작성되어 있습니다.
=C5+(C5*20)/100
G7 셀에는 평균 연봉을 계산하는 다음 수식이 들어 있습니다.
=AVERAGE(D4:D13)
H7 셀에는 최고 연봉자를 찾는 수식이, I7 셀에는 최저 연봉자를 찾는 수식이 각각 입력되어 있습니다.
=INDEX(B4:D13,MATCH(MAX(D4:D13),D4:D13,0),1)
=INDEX(B4:D13,MATCH(MIN(D4:D13),D4:D13,0),1)
이제 VBA(Visual Basic Application)로 매크로를 만들어 이 워크시트에서 값과 서식은 그대로 두고 수식만 제거해 보겠습니다.
방법 1. 선택한 셀 범위에서 수식 제거하기
먼저 워크시트 전체가 아닌 특정 셀 범위에서만 수식을 제거하는 매크로를 만들어 보겠습니다. 예를 들어 직원 정보 영역(B4:D13)에서만 수식을 제거한다고 가정합니다.
⧭ VBA 코드:
Sub Remove_Formulas_from_Selected_Range()
Dim Rng As Range
Set Rng = Selection
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub⧪ 참고: 이 코드는 Remove_Formulas_from_Selected_Range라는 매크로를 생성합니다.
⧭ 실행 결과:
먼저 파일을 Excel 매크로 사용 통합 문서(xlsm) 형식으로 저장하세요. 그다음 수식을 제거할 셀 범위를 선택합니다.
여기서는 직원 정보 영역(B4:D13)에서만 수식을 제거할 것이므로 해당 범위를 선택했습니다.
그런 다음 Remove_Formulas_from_Selected_Range 매크로를 실행합니다. (매크로 실행 방법이 궁금하다면 관련 가이드를 참고하세요.)
매크로 실행 후 선택한 범위의 모든 수식이 제거되고, 값과 서식만 깔끔하게 남게 됩니다.
방법 2. 전체 워크시트에서 수식 제거하기
앞선 방법에서는 선택한 셀 범위의 수식만 제거했습니다. 이번에는 워크시트 전체에서 수식을 제거해 보겠습니다.
아래의 VBA 코드를 사용하면 됩니다.
⧭ VBA 코드:
Sub Remove_Formulas_from_the_Whole_Worksheet()
Sheet_Name = InputBox("Enter the Name of the Worksheet to Remove Formulas: ")
Dim Rng As Range
Set Rng = Sheets(Sheet_Name).Cells
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub⧪ 참고: 이 코드는 Remove_Formulas_from_the_Whole_Worksheet라는 매크로를 생성합니다.
⧭ 실행 결과:
워크시트로 돌아가 Remove_Formulas_from_the_Whole_Worksheet 매크로를 실행합니다.
그러면 수식을 제거할 워크시트 이름을 입력하라는 입력 상자(Input Box)가 나타납니다.
여기서는 Sheet2에서 수식을 제거할 것이므로 Sheet2를 입력했습니다.
확인(OK) 버튼을 클릭하면 워크시트 전체에서 수식이 제거되고 값과 서식만 남습니다.
직원들의 현재 연봉 열에 있던 수식이 사라집니다.
동시에 평균 연봉, 최고 연봉자, 최저 연봉자 셀의 수식도 함께 제거된 것을 확인할 수 있습니다.
꼭 기억해야 할 사항
이번 예제에서는 VBA 붙여넣기 옵션 중 xlPasteValuesAndNumberFormats를 사용했습니다. 이 옵션은 값을 붙여넣을 때 숫자 서식까지 함께 유지해 줍니다.
이 외에도 VBA에는 11가지 추가 붙여넣기 옵션이 있으며, 각 옵션마다 고유한 붙여넣기 방식을 수행합니다. 필요에 따라 적절한 옵션을 선택해 활용하시면 됩니다.
마무리
오늘 소개한 방법들을 활용하면 VBA를 통해 Excel 워크시트의 수식을 제거하면서도 값과 서식은 그대로 유지할 수 있습니다. 궁금한 점이 있다면 언제든지 질문해 주세요.
함께 읽으면 좋은 글
- Excel에서 수식 결과를 텍스트 문자열로 변환하는 7가지 방법
- Excel에서 수식 결과를 다른 셀에 넣는 4가지 일반적인 경우
- Excel에서 수식이 아닌 셀 값을 반환하는 3가지 쉬운 방법
- Excel에서 수식을 자동으로 값으로 변환하는 6가지 효과적인 방법
- Excel에서 숨겨진 수식을 제거하는 5가지 빠른 방법