فال حافظ

ترفند دوم: تابع SUBTOTAL یا +ALT

اگر جمع داده ها را بذاریم بعد از این که داده ها را فیلتر کردیم بعد بیایم زیر داده ها Auto Sum یاALT+ = را بزنیم، سیستم به جای تابع SUM از تابع SUBTOTAL استفاده می کند و موارد فیلتر شده را در این تابع در نظر نمی گیرد.

ترفند سوم: نکته تابع SUBTOTAL

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

برای اینکارALT+A+C را میزنیم و داده ها از فیلتر خارج می شوند. یعنی با این کار تمام فیلتر ها را حذف می کنیم.
حالا اگر ما از تابع SUBTOTAL استفاده کردیم و بخواهیم داده ها را پنهان کنیم چه اتفاقی برای جمع داده ها خواهد افتاد.
اگر الان جمع داده ها را نگاه کنیم مبلغ 749.45 طبق جدول زیر خواهد بود .

حالا محدوده را انتخاب کرده با کلیک راست گزینه Hide را میزنیم و داده های ما پنهان می شود.

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

حالا چی کار می توانیم بکنیم ؟

می آییم به جای عدد 9 در فرمول تابع SUBTOTAL است همان طور که در زیر می بینید

با پاک کردن عدد 9 تابع SUBTOTAL پنجره ای مطابق زیر باز می گردد.

اینجا اگر ببینید هرکدام از توابع ما یک عدد دارد، ازعدد 1 تا عدد 11 نسبت به ردیف های پنهان حساس نیستند ولی از عدد 101 به بعد نسبت به ردیف های پنهان شده حساس هستند.

یعنی ما برای SUM که عدد 9 می باشد باید عدد 109 را انتخاب کنیم

حالا می بینید که فقط جمع اعداد شامل مواردی است که اینجا داریم و می بینیم.

ترفند چهارم: ظاهر شدن ردیف های پنهان

این که اگر ما ردیف های را که پنهان کردیم را بیایم گزینه فیلتر رو باز کنم بدون این که عملیاتی انجام دهیم

فقط گزینه OK رو بزنیم خواهید دید که ردیف های پنهان شده ظاهر می گردد

و حتی اگر فایل ما قفل باشد ولی فایل ما امکان دسترسی فیلتر را به ما داده باشد، با این کار ردیف های پنهان ظاهر می شود.

نکته :

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

مثلا ستون F و E را پنهان کنیم.

حالا بخواهیم جمع دو ستون باقی مانده را با تابع SUM بنویسیم که با زدن ALT+= جمع داده ها رو میبینیم.

حالا خواهید دید که چه اتفاقی خواهد افتاد، اکسل آن داده هایی هم که پنهان هست را در جمع نیز تاثیر داده است.

در این جا اکسل هیچ راه حل مستقیمی در این ارتباط ندارد.

که بیاید فقط دو داده مورد نظر را جمع کند، یعنی مثل مورد قبلی بتوان از تابع SUBTOTAL و یا از توابع دیگر استفاده کرد و جواب درست را بدست آورد امکان پذیر نیست.

یعنی حتی اگر از SUBTOTAL استفاده کرد باز هم همان نتیجه را به ما نشان می دهد. پس راه حل چی هست؟
در این جا ما مجبوریم یک تابع خودمان برای اسکل باALT+ F11 تعریف کنیم. این تابعی که ما تعریف کردیم و کدهاش رو نوشتیم

و اگر شما این کدها را بنوسید تابعی به نام SumVisible به اکسل شما اضافه می کند و بعد از آن می توانید از این تابع استفاده کنید و این تابع برای شما پاسخگو خواهد بود.
و حالا اگر شما بخواهید این کدها را در اکسل بنویسید. بایدALT+F11 را بزنید و بعد گزینه Insert را بزنید

و یک ماژول(Module) ایجاد کنید و توی صفحه خالی ایجاد شده کدهای مورد نظر را بنوسید.
خوب حالا می توانیم تابع SumVisible را تست کنیم.

در سلول مورد نظر می نویسم مساوی SumVisible پرانتز باز محدوده را انتخاب و Enter را میزنیم. خواهیم دید که دقیقا فقط داده هایی که ظاهر هستند را برای ما جمع زده است.

منبع: محمود بنی اسدی