ترفندهای کامپیوتری

قبلا در مطلبی نحوه استفاده از توابع جمع شرطی در اکسل شرح داده شده بود. توضیحات ارائه شده تنها برای یک شیت جوابگو می‌باشد و اگر بخواهید برای چند شیت اینکار را انجام دهید باید از فرمول‌های دیگری استفاده کنید. در ادامه مطلب با پی‌سی‌ترفند همراه باشید تا با این فرمول‌ها آشنا شوید.

روش اول) استفاده از تابع Sumif در هر شیت و محاسبه مجموع آن‌ها

با نگه‌داشتن کلید کنترل، شیت‌های مدنظرتان را انتخاب کنید. با اینکار شیت‌ها در یک گروه قرار می‌گیرند. در همین حالت در آخرین شیت انتخاب شده، در یک سلول خالی علامت مساوی را تایپ کنید و فرمول Sumif را بنویسید. پس از تکمیل فرمول‌نویسی، کلید اینتر را بفشارید تا این فرمول در تمام شیت‌ها درج شود. حال با استفاده از روش‌های گفته در این مطلب، نتایج تابع Sumif که در یک سلول مشابه در همه شیت‌ها است را با هم جمع کنید.

روش دوم) تابع Sumproduct

مثال زیر را در نظر بگیرید. می‌خواهیم مجموع اعداد ستون H در سه شیت ۱ تا ۳، البته با شرطی که در ستون G حرف A باشد را محاسبه کنیم و نتیجه آن در شیت ۴ درج شود.

برای اینکار می‌توان از فرمول زیر استفاده کرد. البته باید در شیت ۴، نام شیت‌ها تایپ شود (A2:A4):

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2,INDIRECT("'"&A2:A4&"'!H2:H5")))

خروجی رابطه فوق برابر می‌شود با ۱۵۰.

منبع: xl-central.com

روش سوم) تعریف تابع جدید SUMIF3D

با استفاده از ماکرونویسی می‌توان تابع جدیدی به توابع موجود اکسل اضافه کرد تا محدودیت تابع Sumif را نداشته باشد.

برای ساخت ماکرو کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

Option Explicit
'pctarfand.ir & tarfandha.blog.ir'
Function SUMIF3D( _
    CritRng As Range, _
    Crit As Variant, _
    SumRng As Range, _
    ParamArray ArgList() As Variant)
    Dim Arg As Variant
    Dim wkb As Workbook
    Application.Volatile 
    If IsMissing(ArgList) Then
        SUMIF3D = CVErr(xlErrValue)
        Exit Function
    End If
    Set wkb = Application.Caller.Parent.Parent
    For Each Arg In ArgList
        SUMIF3D = SUMIF3D + _
            WorksheetFunction.SumIf(wkb.Sheets(Arg).Range(CritRng.Address), _
            Crit, wkb.Sheets(Arg).Range(SumRng.Address))
    Next Arg   
End Function

=SUMIF3D(A2:A5,A2,B2:B5,"Sheet1","Sheet2","Sheet3")

=SUMIF3D(A:A,A2,B:B,"Sheet1","Sheet2","Sheet3")

منبع با تغییر: xl-central.com