جستجو

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

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
ربات پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_bot

خلاصه آمار

تبليغات

پربحث ترين ها

۰

استفاده از جمع شرطی برای چند شیت در اکسل

اکسل

استفاده از جمع شرطی برای چند شیت در اکسل

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

روش اول) استفاده از تابع 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

از محیط ماکرونویسی خارج شوید و به محیط اکسل برگردید (Alt+Q).

مثال زیر را در نظر بگیرید:

برای مثال فوق در شیت سوم بجای علامت سوال، می‌توان یکی از دو تابع زیر را استفاده کرد.

در واقع این توابع، مجموع اعداد محدوده B2:B5 در سه شیت مشخص شده را محاسبه می‌کنند البته در صورتیکه در محدوده A2:A5 عبارت موجود در سلول A2 یعنی حرف B وجود داشته باشد.

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

یا

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

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

توجه: در تابع SUMIF3D نام شیت‌ها باید بین علامت نقل قول " " قرار گیرد. همچنین ترتیب نام‌ها اهمیت ندارد.

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

 

عضویت در خبرنامه:

نظرات (۰)
هیچ نظری هنوز ثبت نشده است

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی
X بستن