فرادرسآموزش فیلتر پیشرفته در اکسل – از صفر تا صد
با اینکه نرم‌افزار اکسل قابلیتی داخلی برای اعمال فیلتر روی داده دارد اما ممکن است با حجم بالایی از دادهها مواجه بوده یا به فیلتر پیشرفته‌تری نیاز داشته باشید. در ادامه می‌بینیم که نحوه کار با فیلتر پیشرفته در اکسل چگونه است و چطور می‌تواند ما را در مدیریت دادههای زیاد یاری کند.
برنامه اکسل، یکی از بهترین نرم‌افزارهای مدیریت داده در سراسر جهان است. شما می‌توانید با کدنویسی و استفاده از توابع مختلف در این برنامه، در کمترین زمان دادهها را مرتب و بررسی کنید. برای آشنایی بیشتر با اکسل و نحوه یافتن و کار با توابع می‌توانید از مقاله «آموزش اکسل» کمک بگیرید.
فیلتر در اکسل برای چه کاری است؟
تابع Filter در اکسل به شما امکان می‌دهد طیف وسیعی از دادهها را بر اساس معیارهای انتخابی، فیلتر کنید. به عنوان مثال، در شرایطی که نیاز به دیدن تمام محصولات یک برند خاص دارید می‌توانید با فیلتر کردن نام مربوطه، فقط دادههای متعلق به آن را در مجموعه خود ببینید.
نحوه تنظیم محدوده شرط چگونه است؟
قبل از به کار بردن فیلتر پیشرفته در اکسل باید محدودهای را برای شرایطی که داریم، تنظیم کنیم. برای شروع میتوانیم چند ردیف جدید بالای دادههای خود بسازیم. بعدا امکان افزودن ردیفهای بیشتر وجود دارد. توجه داشته باشید که به ردیفی برای افزودن تیتر و یک ردیف خالی بین شرط و داده نیاز خواهیم داشت. تیتر ستونهای خود را در ردیف بالایی وارد میکنیم.
این تیترها باید با دادهها هماهنگ باشند، چون قرار است برای فیلتر کردن بر اساس شرط از آن‌ها استفاده شود. تصویری که در ادامه می‌بینید، مجموعه داده مثالی است که در این آموزش به کار خواهیم برد.
سپس پنج ردیف به بالای داده خود اضافه میکنیم. یک ردیف برای تیترها داریم، سه ردیف برای شرط و یک ردیف را خالی گذاشته‌ایم. تیترها را از جدول اصلی کپی و در بالاترین ردیف پیست میکنیم و جدول مانند تصویر می‌شود.
محدوده شرط در فیلتر اکسل چیست؟
محدوده شرط برای فیلتر پیشرفته در اکسل، گروهی از سلولها در یک شیت است که در آن، قوانین فیلتر کردن دادهها را تعیین میکنید. محدوده شرط باید دارای قالبی خاص با سلولهای تیتر و سلولهای حاوی شرط باشد.
- مطالب پیشنهادی برای مطالعه:
- تغییر نام داده ها در نمودار اکسل — آموزش گام به گام + فیلم آموزشی رایگان
- ساخت هدر در اکسل — راهنمای تصویری ایجاد و تنظیم + فیلم آموزشی رایگان
- چگونه اکسل یاد بگیریم؟ – راهنمای یادگیری آسان و سریع
- چگونه با محدوده‌های دارای نام در اکسل کار کنیم؟ (+ دانلود فیلم آموزش گام به گام)
- مدیریت ستون ها در اکسل — راهنمای جامع (+ دانلود فیلم آموزش گام به گام)
وارد کردن شرط برای فیلتر پیشرفته در اکسل
برای وارد کردن شرط فیلتر پیشرفته در اکسل باید از قالب ="=variable" استفاده کنیم. اولین علامت مساوی، رشته را شروع می‌کند و شرط، درون «علامت نقل قول» ("") قرار می‌گیرد. امکان استفاده از علامت‌های مقایسه‌ای معمولی مانند موارد زیر نیز درون شرط وجود دارد:
- برابر با تهران: ="=تهران"
- غیر از تهران: ="<>تهران"
- کمتر از 100: ="<100"
- بزرگ‌تر مساوی 100: =">=100"
وقتی شروط را به این شکل درون سلول می‌نویسیم، اکسل آن را به قالبی که برای فیلتر نیاز دارد، تبدیل می‌کند.
نحوه استفاده از فیلتر تک شرط و تک ستون اکسل
بهترین راه برای شروع آموزش فیلتر پیشرفته در اکسل این است که مثالی ساده با تک شرط و تک ستون را در نظر بگیریم. در ادامه می‌خواهیم داده را بر اساس کد منطقه «2B» فیلتر کنیم. برای انجام این کار به ستون کد منظقه می‌رویم و شرط زیر را برای بررسی برابری با «2B»، در اولین سلول زیر تیتر می‌نویسیم:
1="=2B"
سپس سلولی را در مجموعه داده خود برمی‌گزینیم و در زبانه Data، گزینه Advanced را در بخش Sort & Filter، می‌زنیم.
در کادری که باز می‌شود، با انتخاب محل نمایش دادههای فیلتر شده، شروع خواهیم کرد. میتوانیم یکی از دو گزینه In Place یا Another Location را در این بخش برگزینیم. اگر گزینه دوم را زدیم، مکان مورد نظر را در کادر Copy to، وارد میکنیم.
سپس باید سلولهای مورد نظر را در کادر List Range بنویسیم. البته اکسل این سلولها را به طور خودکار وارد می‌کند، اما باید مطمئن شویم که درست هستند.
در مرحله بعد محدوده سلولها را در کادر Criteria Range وارد میکنیم. میتوانیم این کار را با کلیک درون کادر و سپس استفاده از نشانگر ماوس برای کشیدن محدوده در صفحه، انجام دهیم. باید سلولهای اضافی خالی (جایی که شرط قرار دارد) و سلولهای ستون برچسب را در این قسمت برگزینیم. اگر ردیفهای خالی را اضافه کنیم، به احتمال زیاد نتایج فیلتر درست نشان داده نخواهند شد.
تیک گزینه Unique records only را در صورتی می‌زنیم که تنها نیاز به فیلتر کردن دادههای خاص داشته باشیم.
بعد از OK کردن و بستن این کادر میتوانیم دادههای فیلتر شده را ببینیم. اگر گزینه In Place را زده باشیم، تمام دادههای غیر منطبق با شرط، پنهان خواهند شد. در نهایت نتیجه مانند تصویر زیر است.
استفاده از فیلتر پیشرفته در اکسل برای چند شرط و تک ستون
ممکن است فیلتر پیشرفته در اکسل را برای چند شرط روی یک ستون نیاز داشته باشیم. به این منظور در مثال زیر می‌خواهیم مجموعه داده را برای دو شرط کد منطقه «1B» و «2B»، فیلتر کنیم. برای انجام این کار به ستون کد منطقه می‌رویم و شروط زیر را در دو ردیف جداگانه می‌نویسیم.
1="=1B"1="=2B"نتیجه باید چیزی شبیه به عکس زیر باشد:
سپس سلولی را درون مجموعه داده انتخاب میکنیم و از زبانه Data، گزینه Advanced را می‌زنیم. مراحل را مانند قبل انجام می‌دهیم، با این تفاوت که محدوده شرط را به شکلی می‌نویسیم که شرط دوم را نیز شامل شود.
نهایتا در نتیجه خواهیم دید که مجموعه داده، برای نمایش هر دو کد انتخابی، فیلتر می‌شود.
نحوه به کار بردن فیلتر پیشرفته برای چند شرط و روی چند ستون در اکسل
در ادامه نحوه استفاده از چندین شرط برای فیلتر پیشرفته را می‌بینیم. در این حالت امکان استفاده از شروط OR یا AND را داریم. مثلا میتوانیم مجموعه داده را با شرط برابری کد منطقه با «1A» و «فروشنده1» فیلتر کنیم در صورتی که هر دو شرط درست باشند.
استفاده از فیلتر پیشرفته در اکسل با شرط درست بودن تمام معیارها
برای فیلتر کردن با شرط AND، باید هر دو شرط را در یک ردیف و بلافاصله پس از برچسب مربوطه، وارد کنیم. به این منظور، دستور زیر را در سلول پایین تیتر «کد منطقه» می‌نویسیم:
1="=1A"سپس دستور بعدی را زیر تیتر فروشنده وارد خواهیم کرد:
1="=فروشنده1"باید به چیزی مانند تصویر زیر برسیم:
بعد مانند قبل، سلولی را در محدوده داده انتخاب میکنیم و گزینه Advanced را از زبانه Data، می‌زنیم. برای این نوع فیلتر باید محدوده شرط را تغییر دهیم، چون دو ردیف 1 و 2 را شامل می‌شود. باقی موارد را نیز تنظیم کرده و دکمه OK را فشار می‌دهیم.
نهایتا نتیجه نشان داده می‌شود. توجه داشته باشید که قرار دادن معیارها در یک ردیف، نشان دهنده عملگر AND است. بنابراین حتی اگر دو «فروشنده1» داشتیم، فقط کد مکان «1A» در فیتلر لحاظ می‌شد.
در شرایط درست بودن هریک از شروط
در ادامه باز هم از چند شرط برای فیلتر پیشرفته در اکسل استفاده میکنیم اما این بار شرط OR را به کار می‌بریم. به این منظور باید شروط خود را در دو ردیف جداگانه قرار دهیم. پس کد زیر را در اولین سلول بعد از تیتر «کد منطقه»، می‌نویسیم:
1="=1B"سپس کد بعدی را زیر تیتر «فروشنده» اما در ردیف پایین‌تر وارد میکنیم:
1="=فروشنده1"نتیجه شبیه به تصویر زیر می‌شود:
سپس مانند قبل گزینه Advanced را می‌زنیم، محدوده‌های مورد نظر را انتخاب کرده و دکمه OK را فشار می‌دهیم تا فیتلر اعمال شود.
همان‌طور که می‌بینید، دو فیلد در نتیجه نشان داده می‌شوند. یکی برای شرط «فروشنده1» و دیگری برای شرط «1B».
نکته کلیدی استفاده از چند شرط برای فیلتر پیشرفته در اکسل این است که جهت به کار بردن شرط AND، باید معیارهای مورد نظر را درون یک ردیف بنویسم و در صورت استفاده از شرط OR، باید معیارها را در دو ردیف جداگانه قرار دهیم. برای جمع زدن مقادیر فیلتر شده نیز می‌توانید از تابع Subtotal یا سایر روش‌هایی که برای جمع ستون در اکسل هستند، کمک بگیرید.
در صورتی که تمام و هریک از شروط درست باشند
برای مثال نهایی، می‌خواهیم فیلتر پیچیده‌تری را با استفاده از معیارهای AND و OR همراه با عملگر مقایسه‌ای متفاوت، به کار ببریم. به این منظور، مجموعه داده را برای کد منطقه «1A» و «فروشنده1» یا فروش بیشتر از 50000 عدد، فیلتر خواهیم کرد.
برای انجام این کار، کدهای زیر را در سلولهای مربوطه وارد میکنیم:
1="=1A"1="=فروشنده1"سپس کد زیر را در ردیف پایینی می‌نویسیم:
1=">50000"نتیجه مانند تصویر زیر می‌شود:
سپس گزینه Advanced را می‌زنیم و کادرهای مربوطه را پر میکنیم.
نهایتا جدول، مانند تصویر زیر فیلتر خواهد شد. در این قسمت، دو ردیف حاوی شرط AND و یک ردیف برای شرط OR داریم. فیلتر نیز حاوی 4 فیلد است که یکی برای دو شرط اول و سه تای بعدی برای شرط تعداد فروش هستند.
سخن پایانی
نرم‌افزار اکسل، برنامه‌ای عالی همراه با ابزارهای کاربردی برای بررسی و ذخیره دادهها است. یکی از قابلیت‌های این برنامه، فیلتر کردن دادهها برای نمایش اطلاعاتی خاص است. در این مقاله دیدیم که فیلتر پیشرفته در اکسل چیست و چگونه می‌تواند به ما کمک کند. مراحل مختلف استفاده از این ویژگی را همراه با آموزش تصویری بررسی کردیم و شروط مختلف را برای هرکدام در نظر گرفتیم. به این شکل حتی در صورت داشتن حجم داده بالا و شروط پیچیده نیز می‌توانید اطلاعات مورد نظر خود را با چند کلیک به دست بیاورید.
