وبلاگ

گزارش گیری بین دو تاریخ در اکسل و جمع بین دو بازه

الان که داری این مطلب رو میخونی حتما شما هم جزو اون دسته از افرادی هستی کلی اطلاعات داخل اکسل ثبت کردی حالا میخوای از میان کلی اطلاعاتی که ثبت کردی، فقط تو بازه خاصی گزارش بگیری.مثلا میخوای بدونی از تاریخ X تا تاریخ Y چه میزان فروش داشتی یا چه عملیاتی در این بازه ثبت کردی.خب ما تو این پست قراره نحوه گزارش گیری بین دو تاریخ در اکسل و نحوه جمع بین دو تاریخ یا محدوده را یاد بگیریم.

اهداف این آموزش:

  1. نحوه گزارش گیری بین دو تاریخ در اکسل
  2. نحوه جمع اعداد بین دو تاریخ با تابع Sumifs
  3. نحوه جمع اعداد بین دو تاریخ با تابع Subtotal
جدول اطلاعات فروش در تاریخ های مختلف
شکل شماره 1 – جدول اطلاعات فروش در تاریخ های مختلف

استفاده از ابزار Filter برای گزارش گیری بین دو تاریخ

کاری که ما می خواهیم در بحث گزارش گیری بین دو تاریخ انجام بدیم این هست فقط فروش های از تاریخ 1398/01/01 تا تاریخ 1398/10/30رو بدست بیاریم. برای اینکار ابتدا محدوده موردنظر (اینجا محدوده B2 تا D17) را انتخاب کرده سپس از تب Data گزینه Filter را بزنید تا همانند شکل شماره 1، مثلث های کوچکی به عنوان ستون ها اضافه شود.

استفاده از ابزار Custom Filter برای گزارش گیری
شکل شماره 2 – استفاده از ابزار Custom Filter برای گزارش گیری بین دو تاریخ

حالا مثل شکل شماره 2، روی مثلت کنار سرستون تاریخ کلیک کنید سپس روی گزینه Text Filters کلیک کرده و گزینه Custom Filter را انتخاب کنید تا پنجره Custom AutoFilter باز شود(شکل شماره3).ما اینجا می توانیم انواع گزارش را با اعمال دو شرط بگیریم.

تنظیمات مربوط به محدوده گزارش گیری بین دو تاریخ در اکسل

تعیین شرایط گزارش گیری در ابزار فیلتر
شکل شماره 3 – تعیین شرایط گزارش گیری در ابزار فیلتر
  • is greater than or equal to : داده های بزرگتر یا مساوی 1398/01/01
  • is less than or equal to : داده های کوچکتر یا مساوی 1398/10/30

درواقع ما در تنظیمات بالا تعیین می کنیم که فقط داده هایی را برای من نشان بده که تاریخشان بزرگتر و مساوی 1398/01/01 و(And) کوچکتر مساوی 1398/10/30 باشد.در آخر OK را بزنید تا فیلتر اعمال شود.

نحوه جمع مبلغ های بین دو تاریخ در اکسل با تابع Sumifs

خب ما تا اینجا تونستیم با ابزار فیلتر گزارش گیری بین دو تاریخ را انجام دهیم.حالا قصد داریم ببینیم چطور میشه جمع مبلغ های بین دو تاریخ 1398/01/11 تا 1398/03/11 را با تابع Sumifs بدست آورد.ابتدا تاریخ 1398/01/11 را در سلول F3 و تاریخ 1398/03/11 را در سلول F5 وارد کنید (مطابق شکل شماره 4).

جدول جمع بین دو تاریخ
شکل شماره 4 – جدول جمع اعداد بین دو تاریخ

حال در سلول F8 فرمول زیر را بنویسید:

SUMIFS(D3:D17,B3:B17,”>=”&F3,B3:B17,”<="&F5)=

نحوه جمع مبلغ های بین دو تاریخ در اکسل با تابع Subtotal

تابع Subtotal تابعی می باشد که به فیلتر شده محدوده حساس می باشد.یعنی اگر شما با این تابع بخواهید جمع، شمارش،مینیمم،ماکزیمم و… رو محاسبه کنید، اگر اون محدوده رو فیلتر کنید محاسبات فقط بر روی محدوده ای انجام می شود که در فیلتر قابل مشاهده می باشد.ابتدا شما تابع زیر را در سلول F8 بنویسید:

=SUBTOTAL(9,D3:D17)

توضیح فرمول تابع SUBTOTAL:

تابع SUBTOTAL از 11 تابع پشتیبانی می کند که برخلاف سایر توابع بجای نوشتن اسم تابع، شماره تابع استفاده می شود.اگر به فرمول بالا دقت کنید در اولین آرگومان عدد 9 را نوشتیم که معرف تابع SUM می باشد.خب حالا عمل فیلتر را مثل همان روش اول که گفتیم اعمال کنید.خواهید دید که با هربار فیلتر کردن محدوده، فقط جمع مبلغ هایی محاسبه می شود که در فیلتر قابل مشاهده می باشد.

نکته:

اگر با ابزار Table آشنا باشید تمام این عملیات را براحتی با ابزار Table هم می توانید انجام دهید.


منبع:

support.office.com

support.office.com/total-the-data-in-an-excel-table

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

8 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • سلام لطفاً فرمول مورد استفاده جهت جمع بازه تاریخی مورد نظر در صورتی که جدول اطلاعات در یک شیت باشد و جدول نهایی در شیت دیگر را ارائه دهید. باتشکر از پاسخگویی سریعتان

    • سلام. وقت بخیر

      برای مثال میتونید از فرمول نویسی زیر استفاده کنید :


      =SUMIFS(D:D,A:A,">="&E7,A:A,"<="&F7)

  • سلام. دوستان من یه یوزر فرم دارم که الان بر اساس نام که از یک کامبوباکس انتخاب میشه اطلاعات مورد نیاز رو در یه لیست باکس نشون میده، حالا میخوام این اطلاعات بر اساس یک بازه تاریخ نشان داده بشه.لطفا راهنمایی کنید

    • سلام. وقت بخیر

      راحت ترین راه این است که ابتدا یک ماکرو ضبط کنید و با ابزار Filter یا ابزار Table بین دو بازه از تاریخ عمل فیلتر رو انجام دهید.سپس کدهای ماکرو رو ویرایش کنید.یعنی جایی که تاریخ داخل کد هستش به یک سلول یا TextBox ارجاع بدید تا تاریخ های شروع و پایان رو از اونجا بخونه و گزارش رو بسازه.

      • سلام. چرا من این کار رو انجام میدم اما نمیشه
        این هم کدی که نوشتم
        Private Sub CommandButton1_Click()
        Dim lasrrow As Long
        Dim lasrrow1 As Long
        Dim x As Long
        Dim y As Integer
        Dim n As Integer

        Sheet3.Select
        Sheet3.Range(“a1”).Select
        Sheet3.Range(“a1:j10000”).ClearContents
        Sheet3.Range(“j1”) = “˜ÇјÑÏãåäÏÓ:”
        Sheet3.Range(“h1”) = ComboBox1.Text
        Sheet3.Range(“g1”) = “ÊÇÑíÎ ÒÇÑÔ:”
        Sheet3.Range(“f1”) = “=today()”
        Application.ScreenUpdating = False

        For i = 1 To ListBox1.ListCount
        For j = 0 To 9
        Cells(i + 1, j + 1).Value = ListBox1.List(i – 1, j)
        Next j
        Next i

        Application.ScreenUpdating = True
        Range(“I2”).Select
        Selection.AutoFilter
        ActiveSheet.Range(“$A$1:$J$9”).AutoFilter Field:=9, Criteria1:=TextBox1, _
        Operator:=xlAnd, Criteria2:=TextBox2

        Unload Me
        Sheet3.PrintPreview
        End Sub

        • سلام. وقت بخیر

          کدوم خط خطا میده…؟

          واضح تر توضیح بدید که با چه خطایی مواجه میشه

          • Range(“I2”).Select
            Selection.AutoFilter
            ActiveSheet.Range(“$A$1:$J$9”).AutoFilter Field:=9, Criteria1:=TextBox1, _
            Operator:=xlAnd, Criteria2:=TextBox2

            توضیح: کارکرد یه سری از افراد رو دارم که در یک شیت ذخیره هستند و از یک کامبوباکس یه نام انتخاب میکنم که دیتا رو بر اساس این نام در یک لیست باکس نشون میده، الان میخوام یه بازه زمانی هم تعریف کنم که کارکرد یک فرد رو در بازه زمانی مشخص شده بهم نشون بده و پرینت بگیرم.
            حالا مشکل اینجاست، چون اینکار رو نتونستم انجام بدم کد بالا رو نوشتم که اول همه کارکرد مربوط به فرد که توی لیست باکس نشون میده رو توی یه شیت کپی میکنه و بعد از طریق فیلتر اون بازه رو انتخاب کنه و پرینت بگیره، اما متاسفانه این قسمت از کد عمل نمیکنه. اگر راه بهتری هم هست راهنمایی کنید ممنون میشم(ترجیحا اگه بشه که این فیلتر توی همون لیست باکس انجام بشه)

          • سلام. وقت بخیر

            ممکنه تو محتوا یا محدوده فیلتر مشکل داشته باشه.

            راه پیشنهادی من این هست که محدوده خودتون رو به Table تبدیل کنید و با ابزار ماکرو یکبار شرط خودتون رو ضبط کنید و از اون کد استفاده کنید.

دیدگاهتان را بنویسید