Rick은 차이점을 강조하기 위해 서로 비교해야하는 두 개의 워크 시트를 가지고 있습니다. 비교는 표시되는 내용이 아니라 각 셀의 수식을 비교해야합니다. 이런 식으로 Rick은 각 워크 시트에서 수식이 다른 부분을 발견하기를 원합니다.

수식을 비교하는 방법에는 여러 가지가 있습니다. 특정 버전의 Excel에서는 작업을 처리 할 수있는 비교 추가 기능에 액세스 할 수 있습니다. Office Professional Plus 2013 또는 Office 365 ProPlus를 사용하는 경우 스프레드 시트 비교 추가 기능을 사용할 수 있습니다.

이 추가 기능에 대한 정보는 여기에서 찾을 수 있습니다.

https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986

Office Professional Plus 2013 또는 Office 365를 사용하는 경우 Spreadsheet Inquire 추가 기능을 사용할 수도 있습니다. 이 추가 기능에 대한 정보는 다음 위치에 있습니다.

https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

모든 사람이 이러한 Excel 버전 중 하나를 사용하지 않거나 추가 기능을 사용하고 싶지 않을 수 있음을 인식하면 다른 작업을 수행 할 수 있습니다. Excel 2013 이상 버전에는 FORMULATEXT라는 편리한 워크 시트 함수가 있습니다. 이 함수를 사용하여 다음과 같은 방식으로 셀에 저장된 수식을 검색 할 수 있습니다.

=FORMULATEXT(A7)

그러면 A7 셀 (이 경우)에 포함 된 수식이 반환됩니다. 셀에 수식이 포함되어 있지 않으면 # N / A 오류가 반환됩니다. 이 동작을 사용하여 “비교 워크 시트”에 수식이 같은지 여부에 대한 표시기를 만들 수 있습니다. 새 워크 시트를 만들고 A1 셀에 넣으십시오.

=IF(FORMULATEXT(Sheet1!A1)=FORMULATEXT(Sheet2!A1),"","Different")

수식을 원하는만큼 아래로 그리고 맨 오른쪽으로 복사합니다. Sheet1과 Sheet2의 해당 셀 간의 차이점을 표시합니다.

FORMULATEXT는 Excel 2013에서 도입되었으므로이 접근 방식은 이전 버전의 Excel에서 작동하지 않습니다. 다른 버전을 사용하는 경우 (또는 Excel 2013을 사용하는 경우에도) 매크로를 사용하여 워크 시트 간의 차이점을 표시 할 수 있습니다. 사용할 수있는 많은 매크로 접근 방식이 있습니다. 다음은 비교를 수행하는 간단한 방법입니다.

Sub ComparaFormulas1()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range

Set Check = ActiveSheet     Set Master = Worksheets("Master")



For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 c.Interior.Color = RGB(255, 0, 0)

End If         End If     Next c End Sub

이 매크로를 사용하려면 비교할 통합 문서를 표시하십시오. “마스터”라는 워크 시트의 동일한 셀과 비교하려고한다고 가정합니다.

( “표준”워크 시트의 이름이 다른 경우 매크로에서 분명히 변경할 수 있습니다.) 현재 워크 시트의 모든 셀은 “마스터”워크 시트의 해당 셀과 비교됩니다. 셀에 수식이 포함되어 있고 해당 수식이 다른 경우 현재 워크 시트에서 셀의 배경색이 빨간색으로 변경됩니다.

이러한 접근 방식은 비교되는 워크 시트의 서식을 분명히 변경합니다. 서식을 변경하지 않고 단순히 차이점이있는 셀 목록을 원하는 경우 매크로에서 다음 변형을 사용할 수 있습니다.

Sub ComparaFormulas2()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range     Dim sTemp As String     Dim lDif As Long

Set Check = ActiveSheet     Set Master = Worksheets("Master")

sTemp = ""

lDif = 0

For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 lDif = lDif + 1                 sTemp = sTemp & vbCrLf & lDif & ": " & c.Address             End If         End If     Next c     If lDif > 0 Then         sTemp = "These were the differences" & vbCrLf & sTemp     Else         sTemp = "There were no differences"

End If     MsgBox sTemp End Sub

비교 범위를 허용하는 사용자 정의 함수 (UDF)를 만들 수도 있습니다. 그렇게하면 다양한 방법으로 사용할 수 있습니다.

Function CompareFormulas3(rng1 As Range, rng2 As Range)

Dim x As Long

If rng1.Count <> rng2.Count Then         'Range sizes do not match         CompareFormulas = CVErr(xlValue)

Else         CompareFormulas = True    ' Assume all the same         For x = 1 To rng1.Count             If rng1(x).Formula <> rng2(x).Formula Then                 'Formulas do not match                 CompareFormulas = False                 x = rng1.Count    ' No need to keep comparing             End If         Next x     End If End Function

두 워크 시트의 셀 범위에 동일한 수식이 있는지 확인하려는 경우 다음과 같이 사용할 수 있습니다.

=CompareFormulas3(Sheet1!A1:Z1000,Sheet2!A1:Z1000)

이 함수는 모든 셀에 동일한 수식이 있으면 TRUE를, 셀에 다른 수식이 있으면 FALSE를 반환하고, 두 범위의 크기가 같지 않으면 #Value 오류를 반환합니다.

차이점을 강조하려는 경우 조건부 서식 규칙 내에서 UDF를 사용할 수 있습니다. Sheet1의 셀에 조건부 서식을 적용한다고 가정하고 규칙이 수식을 사용해야한다고 지정하고이를 수식으로 사용합니다.

=NOT(CompareFormulas3(Sheet2!A1,A1))

Sheet1의 셀이 Sheet2의 해당 셀과 일치하지 않으면 조건부 서식 규칙에서 정의한 서식에 따라 서식이 지정됩니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶으면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (13400)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.