جستجو

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

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

خلاصه آمار

تبليغات

۶

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

اکسل ماکرونویسی

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

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

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

 

برای مطلع شدن از جدیدترین مطالب سایت، کانال‌ پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:

          

سوالات خود در زمینه ورد و اکسل و ... را می‌توانید از طریق ایمیل و تلگرام با ما مطرح کنید.

آدرس ایمیل: tarfandha.blog@gmail.com

آی‌دی تلگرام: pctarfand_admin@

نظرات (۶)

  1. سلام ببخشید اگر تعداد شیتها زیاد بود و نخواستیم دستی نام شیتها را وارد کنیم چه تغییری میتوان در فرمول =SUMIF3D(A:A,A2,B:B,"Sheet1","Sheet2","Sheet3") ایجاد کرد؟؟؟؟
    • پاسخ:

      سلام
      با استفاده از روش های گفته شده در این سایت لیست نام شیت ها را استخراج کنید.
      اگر میخواهید این نام ها را به هم با " بچسبانید روش اول گفته شده در این مطلب برایتان مفید است.
      موفق باشید.
  2. سلام
    برای فرمول sumproduct یه فایل دقیقا مشابه فایل نمونه شما مطابق تصویر بالا درست کردم و فرمول را کپی کردم ولی کار نمیکنه
  3. سلام
    اگر مثلا سه شیت حقوق افراد در سه ماه مختلف باشد که آدرس سلول هر شیت با هم متفاوت است آیا راه حلی برای جمع این شیت ها از روی اسم های مشابه وجود دارد؟
    • پاسخ:

      سلام
      مطلب فوق همینکار را انجام می دهد.
  4. سلام ، میخاستم از 4 عدد 2 تا از بیشترینا با هم جمه بشه ، فرمولش چی میشه
    • پاسخ:

      سلام
      از تابع Large استفاده کنید که در این سایت آموزش داده شده است.
      موفق باشید.
  5. سلام ، میخاستم از 5یا 4 عدد 3 تا از بیشترینا با هم جمه بشه ، فرمولش چی میشه ،
    ممون
    • پاسخ:

      سلام
      بالاخره 4 یا 5 عدد؟

ارسال نظر

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

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

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

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

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

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

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

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