مقایسه فرمول ها در دو کاربرگ در اکسل

Question

ریک دو کاربرگ دارد که باید آنها را با یکدیگر مقایسه کند تا تفاوت ها را برجسته کند. مقایسه باید آنچه را که نمایش داده می شود، مقایسه نمی کند، بلکه باید فرمول های موجود در هر یک از سلول ها را مقایسه کند. به این ترتیب ریک امیدوار است که کشف کند که فرمول ها در هر کاربرگ کجا متفاوت هستند.

روش های مختلفی برای مقایسه فرمول ها وجود دارد. در نسخه‌های خاصی از اکسل، به یک افزونه مقایسه دسترسی دارید که می‌تواند این کار را برای شما انجام دهد. اگر از 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

با توجه به اینکه همه از یکی از این نسخه‌های اکسل استفاده نمی‌کنند یا ممکن است نخواهند از افزونه استفاده کنند، کارهای دیگری وجود دارد که می‌توانید انجام دهید. در اکسل 2013 و نسخه های بعدی یک تابع کاربرگ مفید به نام FORMULATEXT وجود دارد. می توانید از این تابع برای بازیابی فرمول ذخیره شده در یک سلول به این صورت استفاده کنید:

=FORMULATEXT(A7)

این فرمول موجود در سلول A7 (در این مورد) را برمی گرداند. اگر سلول حاوی فرمول نباشد، یک خطای #N/A برمی‌گرداند. شما می توانید از این رفتار برای ایجاد یک شاخص در یک "کاربرگ مقایسه" استفاده کنید که آیا فرمول ها برابر هستند یا خیر. فقط یک کاربرگ جدید ایجاد کنید و آن را در سلول A1 قرار دهید:

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

فرمول را تا جایی که می خواهید به سمت پایین و سمت راست کپی کنید. این تفاوت بین سلول های مربوطه در Sheet1 و Sheet2 را نشان می دهد.

به یاد داشته باشید که FORMULATEXT در اکسل 2013 معرفی شد، بنابراین این رویکرد در نسخه های قدیمی اکسل کار نمی کند. اگر از نسخه دیگری استفاده می کنید (یا حتی اگر از اکسل 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

برای استفاده از این ماکرو، کتاب کاری را که می خواهید مقایسه کنید نمایش دهید. فرض می کند که می خواهید با همان سلول ها در یک کاربرگ به نام "Master" مقایسه کنید. (در صورتی که کاربرگ استاندارد شما نام متفاوتی داشته باشد، واضح است که می توانید آن را در ماکرو تغییر دهید.) هر سلول در کاربرگ فعلی با سلول مربوطه در کاربرگ اصلی مقایسه می شود. اگر سلول ها حاوی فرمول هستند و آن فرمول ها متفاوت هستند، رنگ پس زمینه سلول در کاربرگ فعلی به قرمز تغییر می کند.

چنین رویکردی بدیهی است که قالب بندی کاربرگ مورد مقایسه را تغییر می دهد. اگر ترجیح می دهید قالب بندی را تغییر ندهید، اما در عوض به سادگی لیستی از سلول های دارای تفاوت را می خواهید، می توانید از تغییرات زیر در ماکرو استفاده کنید:

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 مطابقت نداشته باشد، طبق هر قالب‌بندی که در قانون قالب‌بندی شرطی تعریف کرده‌اید، قالب‌بندی می‌شوند.