تابع Subtotal

[mr.mahlouji]

يكي از مشكلاتي كه اكثر كاربران با آن روبروهستند ، جمع زدن يا ميانگين گرفتن و ... از فايلهايي است كه فيلتر ميشوند. كاربران ميتوانندبا استفاده آسان و روان از اين تابع بهره لازم را ببرند.
اين تابع زيرجمع مقادير يك ليست يا يك پايگاه داده را به ما برميگرداند و به فيلتر كردن حساس است.
اين تابع داراي 11 تابع دروني (مطابق شكل )مي باشد.
براي راهنمايي بهتر ، هر آرگومان با رنگ مجزايي نشان داده شده.



به فايل ضميمه مراجعه شود.

[Amir Mohsenpour]

با تشکر از استاد گرامی جناب آقای محلوجی

اضافه می نمایم که در اکسل می توان از خود Option های موجود در صفحه نیز برای بدست آوردن Subtotal استفاده کرد بدین صورت که بطور مثال جدولی را که جناب آستاد محلوجی ارسال کرده اند انتخاب کرده و بر اساس ستونی که می خواهیم Subtotal آنرا بدست آوریم Sort می کنیم سپس از گزینه Data بر روی Subtotal کلیک می کنیم و در پنجره باز شده در قسمت اول ستون مورد نظر بفرض فروشگاه را انتخاب می نمائیم.
در قسمت بعدی نوع عملیات مدنظر مثلاً Sum و در قسمت اخر نیز اینکه Subtotal چه ستونی باید محاسبه شود را انتخاب می نمائیم مثلاً مبلغ.
سپس با کلیک بر روی Ok می توانیم Subtotal را بر اساس گزینه های درخواستی مشاهده نمائیم.
این روش Subtotal جزء به جزء خریدها از فروشگاهها را به شما ارائه خواهد داد.

[mrexcel]

با استفاده از اين ويژگي هاي subtotal ميتوان يكي از مشكلات موجود در تهيه ليستها و ويرايش ان را حل نمود و ان مرتب شدن شماره رديف هاي كه براي يك جدول گذاشته ايم در هنگام فيلتر كردن است
مثلا nv ستون aشماره زده ايم
if(b1<>"";subtotal(3;$b$1:b1);"")1
اين تابع را اگر در ستون شماره رديف بنويسيم در هنگام فيلتر كردن شماره رديف ها را مرتب مينمايد

[Amir Mohsenpour]

نمونه نهائی را در فایل اکسل برای دوستان عزیز ضمیمه نموده ام

[mr.mahlouji]

با سلام
من واقعا از همه شما سپاسگزارم و افتخار ميكنم كه كوچك و شاگرد شما هستم.
واقعا از راهنمايي هاي شما كمال تشكر را دارم و اميدوارم همان گونه كه بنده از راهنمايي هاي شما استفاده مفيدي ميبرم ، دوستان هم نهايت استفاده را از راهنمايي هاي شما ببرند.
كوچك شما

[mrexcel]

يكي ديگر از نكات تابع subtotal اين است كه اگر در بين اطلاعاتما رديف هاي hide گردند و ما بخواهيم بر روي رديف هاي كه hide شده محاسبات انجام دهيم در محاسبات به جاي اعداد 1تا 11 از 101تا 111 استفاده مينماييم

[mr.mahlouji]

بله مرسي از راهنمايي بجاي شما

[mrexcel]

در هر جايي كه انجام يك عمل با مبحث فيلتر سئوال شد اولين گزينه كه به ذهنتان ميرسد اين باشد كه آيا ميتوان از subtotal و if استفاده نمود .به عنوان مثال
در صورتي كه اطلاعاتي را فيلتر كنيم و ستوني كه به عنوان شماره رديف قرار داديم مجددا شماره گذاري گردد.
رديف بالاي رديف هاي كه مخفي شده است رنگي گردد (conditional formating )
اين تابع را هميشه با اين نكته بياد بياوريم

[mr.mahlouji]

با سلام
بسيار متشكرم از توضيحات شما ، اگر امكان دارد فايلي بعنوان مثال در اين خصوص ضميمه بفرماييد.

[bokhi]

باسلام

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

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

[amator]

همونطور که توی تصویر پست 4 دیده میشه، در قسمت input range آدرس مطلق "L1:L11" قید شده
شما هر آیتمی ر که از فروریز انتخاب کنین عدد مربوط به ردیف اون آیتم رو براتون برمیگردونه. شما میتونین بنابر سلیقه اعداد 1تا11 یا توابع مربوطه رو در دامنه فوق وارد کنین.
اگر بخواهید محاسبات مربوط به اطلاعات hide شده رو هم داشته باشین بایستی عدد 100 رو با عدد ارجاع شده از combo box که در فرمول مورد استفاده قرارگرفته جمع کنین.

[mr.mahlouji]

با سلام به استاد بزرگوار جناب اكبريان
لطف كرديد ، پاسخ داديد ، من چند روزي بود ، كه سعادت نداشتم كه حضور داشته باشم.

[mrexcel]

در تابع subtotoal اگر تمرين بفرمائيد متوجه ميشويد كه ارگوماني دارد كه اعداد 1 تا 11 را ميگيرد و نوع محاسبات را با توجه به اين اعداد مشخص مينمايد - مثلا 1 ميانگين يا 9 جمع ميشود . خروجي كمبو باكش ها هم عدد ميباشد و به يك سلول ارجاع داده ميشود در اين حالت اگر خروجي كمبو باكس را به سلولي ارجاع دهيم و ارگومان فرمول subtotal را نيز به ان سلول متصل نماييم اين خروجي حاصل ميشود كه با تغيير كمبو باكس نوع عمليات تغيير ميكند

[mostafahtsm]

سلام
میتونید از لینک زیر فیلم آموزش کاربرد subtotal در اکسل را مشاهده کنید.

کاربرد Subtotal در اکسل (http://www.kanazspid.ir/acc/accountingeducation/6-%DA%A9%D8%A7%D8%B1%D8%A8%D8%B1%D8%AF-Subtotal-%D8%A7%DA%A9%D8%B3%D9%84-%D8%AF%D8%B1-%D8%AD%D8%B3%D8%A7%D8%A8%D8%AF%D8%A7%D8%B1%DB%8C.h tml)

[meysam741]

سلام خدمت دوستان گرامي
متأسفانه فايل اولي كه گذاشتيد ، مربوط به subtotal نيست، فال دوم هم نتونستم باز كنم.
اگه ممكنه يك بار ديگه فايل رو قرار بديد.

با سپاس

[Pbapba]

میشه فایل نمونه قرار بدید

با استفاده از اين ويژگي هاي subtotal ميتوان يكي از مشكلات موجود در تهيه ليستها و ويرايش ان را حل نمود و ان مرتب شدن شماره رديف هاي كه براي يك جدول گذاشته ايم در هنگام فيلتر كردن است
مثلا nv ستون aشماره زده ايم
if(b1<>"";subtotal(3;$b$1:b1);"")1
اين تابع را اگر در ستون شماره رديف بنويسيم در هنگام فيلتر كردن شماره رديف ها را مرتب مينمايد

[حسام بحرانی]

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