• امروز : افزونه جلالی را نصب کنید.
  • برابر با : 27 - رمضان - 1442
  • برابر با : Saturday - 8 May - 2021
59262 215
0

تکنیک های جذاب اکسل – فیلتر پیشرفته

  • کد خبر : 151472
  • ۱۳ اردیبهشت ۱۴۰۰ - ۱۱:۰۶

در این مطلب آموزشی با آموزش نکته های جذاب و کاربردی اکسل، به سراغ ابزار فیلتر پیشرفته می رویم. به عنوان مثال، جدولی مطابق تصویر پایین داریم، تصمیم داریم بر اساس عنوان […]


در این مطلب آموزشی با آموزش نکته های جذاب و کاربردی اکسل، به سراغ ابزار فیلتر پیشرفته می رویم.

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

تکنیک های جذاب اکسل – فیلتر پیشرفته

 

قبل از شروع مراحل عملیاتی، برای فهم کلیات این آموزش، بهتر است خواسته های ما برای شرط گذاری در قالب سوال ارائه شود پس به این سوالات توجه کنید:

۱- چه رکوردهایی بر اساس عنوان “فروشنده” با مقدار “نادر” داریم؟

۲- چه رکوردهایی بر اساس عنوان “فروشنده” با مقدار “نادر” و همچنین عنوان “ماه” با مقدار “خرداد” داریم؟

در ادامه سوالات را خلاصه تر مطرح می کنیم.

۳- چه رکوردهایی برای “نادر”  در ماه  “خرداد” یا  برای “حامد” در ماه “اردیبهشت” داریم؟

۴- چه رکوردهایی برای “فروردین” با مقادیر فروش بین ۱۰ و ۵۰ وجود دارد؟

۵- چه رکوردهایی با اسامی ۳ حرفی بر اساس عنوان فروشنده وجود دارد؟

و یک سوال حرفه ای دیگر:

۶- چه رکوردهایی مقدار فروش آنها بر اساس فرمول، زوج است؟

 

خوب حالا به ترتیب و سریع در قالب تصاویر به سوالات پاسخ های عملی می دهیم.

۱- چه رکوردهایی بر اساس عنوان “فروشنده” با مقدار “نادر” داریم؟

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

سپس برای تعیین شرط می توانیم یک مقدار زیر هر عنوان تایپ کنیم،در پاسخ به سوال ۱ تنها کافی است برای عنوان “فروشنده” مقدار “نادر” را ثبت و بقیه عناوین را خالی رها کنیم.
در گام بعدی باید به اکسل بگوییم قرار است جدول ما بر اساس محدوده شرط فیلتر شود سپس این تغییرات بر روی همان جدول اعمال گردد یا به عنوان گزارشی مستقل در محل دیگری از شیت اکسل قرار گیرد.

یکی از سلول های جدول را انتخاب و ابزار ADVANCED FILTER را فعال می کنیم مطابق تصویر،به صورت خودکار  آدرس اولین سلول و آخرین سلول از جدول شناسایی می گردد بنابراین آدرس محدوده اطلاعاتی (سلول B6 تا (E15 در قسمت LIST RANGE ثبت می شود، علامت $ قبل از اجزای آدرس به معنای ثابت بودن آدرس در فیلتر پیشرفته است،اگر این علامت در اجزای آدرس وجود نداشته باشد محدوده آدرس نسبی می شود.

با چپ کلیک موس به قسمت CRITERIA RANGE می رویم تا آدرس محدوده شرط را انتخاب کنیم، بنابراین از سلول B2 تا D3  را با موس انتخاب می کنیم.

به قسمت ACTION توجه کنید گزینه اول به صورت پیش فرض فعال است،این گزینه به این معناست که عمل فیلترینگ بر روی همان جدول اعمال شود ولی اگر گزینه دوم انتخاب شود می توانیم در قسمت COPY TO سلولی در فضای خالی شیت اکسل انتخاب کنیم تا نتیجه عمل فیلترینگ از آن سلول شروع و گزارش ثبت شود.
البته گزینه ها را تغییر نمی دهیم و برای پایان عملیات بر روی گزینه OK کلیک می کنیم.

1620023776 855 تکنیک های جذاب اکسل – فیلتر پیشرفته1620023776 839 تکنیک های جذاب اکسل – فیلتر پیشرفته1620023776 140 تکنیک های جذاب اکسل – فیلتر پیشرفته1620023776 334 تکنیک های جذاب اکسل – فیلتر پیشرفته

 

۲- چه رکوردهایی بر اساس عنوان “فروشنده” با مقدار “نادر” و همچنین عنوان “ماه” با مقدار “خرداد” داریم؟

در واقع از ما خواسته شده است تا رکوردهایی استخراج شود که شامل مقادیر “نادر” و “خرداد” باشد،از مفهوم سوال مشخص است که وجود هر ۲ مقدار برای نمایش رکورد الزامی است، عبارت منطقی “و” در سوال نشانه ای برای این موضوع است.

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

1620023776 719 تکنیک های جذاب اکسل – فیلتر پیشرفته

در ادامه نکات اختصاصی هر سوال بیان می شود و تصویر خروجی ارائه می گردد.

۳- چه رکوردهایی برای “نادر”  در ماه  “خرداد” یا  برای “حامد” در ماه “اردیبهشت” داریم

عبارت منطقی “نادر” در ماه “خرداد” شامل ۲ مقدار الزامی است که باید در سطر اول محدوده شرط گذاری ثبت تا اکسل متوجه مفهوم “و” شود، همچنین برای عبارت منطقی دوم “حامد” در ماه “اردیبهشت” شامل ۲ مقدار الزامی است، اما در کل ۲ عبارت ترکیبی نسبت به هم الزامی نیستند.

عبارت “یا” نشانه ای برای این موضوع است، به همین دلیل در سطرهای مجزا، عبارت های منطقی را ثبت می کنیم تا اکسل متوجه غیرالزامی بودن ۲ عبارت منطقی نسبت به هم شود،بنابراین حتی اگر یکی از عبارت های منطقی برای هر سطری صادق باشد نتیجه نهایی نمایش آن سطر می باشد.

1620023776 851 تکنیک های جذاب اکسل – فیلتر پیشرفته

 

۴- چه رکوردهایی برای “فروردین” با مقادیر فروش بین ۱۰ و ۵۰ وجود دارد؟

برای پاسخ به این سوال، در واقع ۳ شرط الزامی داریم به این ترتیب:
۱) نادر  ۲) بزرگتر یا مساوی ۱۰  ۳ ) کوچکتر یا مساوی ۵۰
نکته مهم این است که باید عنوان “فروش” ۲ بار استفاده شود، بنابراین مطابق تصویر، در سطر اول محدوده شرطی، تمامی این موارد را مشخص می کنیم سپس از ابزار فیلتر پیشرفته بهره می گیریم.

1620023776 462 تکنیک های جذاب اکسل – فیلتر پیشرفته

 

۵- چه رکوردهایی با اسامی ۳ حرفی بر اساس عنوان فروشنده وجود دارد؟

در پاسخ به این سوال از علامت “؟” استفاده می کنیم، این علامت معادل یک “کاراکتر” است اگر آن را ۳ بار تکرار کنیم به این معناست که منظور ما اسامی ۳ حرفی است، البته باید قبل از وارد کردن مقدار شرط، ماهیت سلول مربوطه با استفاده از ابزار FORMAT CELLS به TEXT تغییر داده شود، حالا پس از علامت تساوی ۳ بار علامت “؟” را ثبت می کنیم، در پایان از ابزار فیلتر پیشرفته برای تهیه گزارش بهره می گیریم.

1620023776 865 تکنیک های جذاب اکسل – فیلتر پیشرفته1620023776 169 تکنیک های جذاب اکسل – فیلتر پیشرفته

 

۶- چه رکوردهایی مقدار فروش آنها بر اساس فرمول، زوج است؟

برای اعمال فرمول به عنوان شرط، کافی است یک سلول برای فرمول در نظر بگیریم و فرمول را برای اولین سلول از ستون فروش وارد کنیم، البته در این حالت باید نتیجه فرمول صحت(TRUE) و عدم صحت(FALSE)  یک بررسی را نشان دهد،از تابع ISEVEN برای بررسی زوج بودن عدد استفاده می کنیم،در هنگام استفاده از ابزار فیلتر پیشرفته در واقع سلول فرمول و یک سلول خالی بالاتر آن به عنوان محدوده شرط انتخاب می کنیم.

1620023777 770 تکنیک های جذاب اکسل – فیلتر پیشرفته1620023777 952 تکنیک های جذاب اکسل – فیلتر پیشرفته

 

توجه داشته باشید که  آدرس ورودی فرمول(E7) به صورت نسبی است و ابزار ADVANCED FILTER این آدرس را برای سایر سلول های ستون “تعداد فروش” تغییر می دهد.

سایر نکته ها :

۱- زمانی که عمل فیلتر انجام می شود در واقع سطرهایی که از شرط پیروی نمی کند در حالت مخفی قرار می گیرند.
۲- برای خارج کردن جدول از حالت فیلتر پیشرفته،مطابق تصویر بر روی گزینه CLEAR کلیک کنید.

1620023777 258 تکنیک های جذاب اکسل – فیلتر پیشرفته

۳- اگر سطرهایی با مقادیر تکراری داشته باشیم برای استخراج سطرهای یکتا باید تیک گزینه UNIQU RECORDS ONLY فعال شود.

۴- اگر بخواهیم خروجی فیلتر پیشرفته،شامل تمامی ستون های جدول نباشد ابتدا باید عناوین ستون های مورد نظر در محل جدید کپی شود و هنگام استفاده از ابزار  ADVANCED FILTER گزینه COPY TO ANOTHER LOCATION انتخاب و آدرس محدوده عناوین انتخابی در قسمت COPY TO ثبت شود.

1620023777 121 تکنیک های جذاب اکسل – فیلتر پیشرفته

در پایان امیدوارم این مطلب آموزشی برای شما مخاطب گرانقدر مفید واقع شده باشد. در حریم امن الهی خوشبخت و سلامت باشید.
 
نویسنده:
مهندس محمد‌ رضا سواری

 

لینک کوتاه : https://sobhanepress.ir/?p=151472

ثبت دیدگاه

مجموع دیدگاهها : 0در انتظار بررسی : 0انتشار یافته : ۰
قوانین ارسال دیدگاه
  • دیدگاه های ارسال شده توسط شما، پس از تایید توسط تیم مدیریت در وب منتشر خواهد شد.
  • پیام هایی که حاوی تهمت یا افترا باشد منتشر نخواهد شد.
  • پیام هایی که به غیر از زبان فارسی یا غیر مرتبط باشد منتشر نخواهد شد.