
فرمول نویسی و حذف داده های فیلتر شده با استفاده فیلتر پیشرفته در اکسل
همونطور که قبلا گفتیم فیلتر کردن داده ها سریع ترین راه برای تهیه گزارش خیلی ساده از اطلاعات زیاد هست. اما مسائلی در این رابطه وجود داره که خیلی مورد بحث هست، برای همین موضوع فیلتر پیشرفته در اکسل رو میخوایم در این آموزش بگیم. مثلا حذف داده های فیلتر شده، انجام محاسبات رو داده های فیلتر شده، فرمول نویسی در محدوده فیلتر شده و …
فایل دیتابیس مورد استفاده در آموزش رو میتونید در انتهای صفحه دریافت کنید.
حذف داده های فیلتر شده
فرض کنید از بین داده های بانک اطلاعاتی، ردیف های خالی رو میخواید حذف کنید. بهترین و سریع ترین راه اینه که در قسمت فیلتر Blank رو انتخاب کنید و بعد هم ردیف های فیلتر شده رو پاک کنید. اما مسئله اصلی انتخاب ردیف های نمایان هست. یعنی طبق شکل ۱، ما میخوایم ردیف های ۴ ,۵ ,۹, ۲۲ ,۳۱ ,۳۲ ,۳۳,۴۳, ۶۶,۶۷ رو حذف کنیم. اما در صورتی که محدوده انتخاب شده رو Delete کنیم، کل ردیف های پنهان شده هم حذف میشن. چه باید کرد؟
شکل ۱- فیلتر پیشرفته در اکسل – انتخاب محدوده فیلتر شده
بعد از انتخاب ردیف ها، از مسیر زیر، گزینه Visible cells only رو انتخاب کنید.
Home/ Find & Select/ Go To Special/ Visible Cells Only
شکل۲- فیلتر پیشرفته در اکسل – انتخاب سلول های نمایان
حالا بر روی ردیف های انتخاب شده کلیک راست کرده و بر روی Delete کلیک کنید.(شکل ۳)
شکل ۳- فیلتر پیشرفته در اکسل – حذف ردیف های خالی
کلید میانبر برای انتخاب سلول های نمایان، Alt+; هست.
در ورژن های اخیر، خودبخود عملیات بر روی سلول های نمایان انجام میشه. اما باز هم برای اطمینان، حتما قبل از انجام هر عملیاتی، سلول های نمایان رو انتخاب کنید و بعد اقدام به انجام عملیات مورد نظر کنید.
انجام محاسبات بر روی داده های فیلتر شده
وقتی میخوایم روی داده های فیلتر شده محاسبات انجام بدیم، مثلا جمع داده های فیلتر شده رو حساب کنیم. نمیشه از تابع Sum استفاده کرد. چون تابع Sum کل محدوده انتخابی رو در نظر میگیره. برای این کار باید از تابع Subtotal استفاده کنیم. تابع Subtotal دو آرگومان داره:
Function_Num: عددی است که عملیات مورد نظر رو مشخص میکنه. مثلا عدد ۹، عملیات جمع زدن یا Sum رو انجام میده.
Ref: محدوده ی مورد نظر.
این تابع رو با یک مثال شرح میدم. به شکل ۴ توجه کنید. دو تابع sum و subtotal روی یک محدوده اعمال شده اند. می بینید که تابع sum کل اعداد ستون J رو جمع میزنه. اما تابع subtotal فقط داده های فیلتر شده (مربوط به شرکت CIE) رو جمع میزنه. پس هرگاه بخوایم روی داده های فیلتر شده محاسباتی انجام بدیم، حتما باید از تابع subtotal استفاده کنیم.
شکل ۴- فیلتر پیشرفته در اکسل – جمع داده های فیلتر شده
تابع subtotal ویژگی های خاص دیگه ای هم داره که در آینده به آنها پرداخته خواهد شد.
در این آموزش به نحوه انجام عملیات خاص بر روی داده های فیلتر شده پرداختیم. نکات ظریف و مهمی در این مقاله ارائه شد. در خصوص فیلتر مباحث زیاد هست که به امید خدا در آموزش های بعدی به آنها خواهیم پرداخت.
یکی از مسائلی که در زمان فیلتر اطلاعات پیش میاد، شماره گذاری خودکار و صحیح ردیف ها هست که در آموزش ۳ روش برای شماره ردیف خودکار در اکسل در موردش صحبت کردیم.
دانلود فایل این آموزش
با سلام
چطوری میشه فرمول subtotal رو با فرمول textjoin ترکیب کرد بطوری که وقتی فیلتر کردیم فرمول textjoin فقط به سلولهای فیلتر شده اعمال بشود؟
سلام
نمیشه
بجای فیلتر از فرمول نویسی استفاده کنید برای استخراج د اده ها
بعد روی اون textjoin بزنید
چطور میتونیم تابع فیلتر رو روی همون ستون اصلی ایجاد کنیم؟
مثل فیلتر اکسل ولی با فرمول
درود
نمیشه
در سلول یا داده دارید یا فرمول
هر دو رو نمیشه داشت
عالی عالی عالی
هیچ جا این نکته زیر رو نگفته بودن. واسه همین چند ساعت سر در گم شدم
بعد از انتخاب ردیف ها، از مسیر زیر، گزینه Visible cells only رو انتخاب کنید.
Home/ Find & Select/ Go To Special/ Visible Cells Only
سپاس فروان