سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

همه چیز در مورد Slicer در اکسل

فیلتر در اکسل با استفاده از اسلایسر
۱/۵ - (۱ امتیاز)

فیلتر کردن اطلاعات با ابزار Slicer

یکی از ویژگی های گزارش یا داشبورد اکسل، Interactive بودن (تعاملی بودن)، هست. منظور از تعاملی بودن، این هست که بتونیم تغییرات مختلف رو در گزارش اعمال کنیم و به خروجی مورد نظرمون برسیم. قطعا یکی از ابزارهای اصلی اینکار “فیلتر” هست. همانطور که می دونید در اکسل ابزار فیلتر داریم (هم در Tableو هم در Pivot Table ) اما این ابزار محدودیت هایی هم دارد. مثلا اگر فیلتری رو انجام بدیم، مشخص نیست که چه شرط هایی را گذاشتیم و یا اینکه اگر بخواهیم که چندین Pivot Table رو به یک شکل فیلتر کنیم، با ابزار فیلتر امکان انجام این کار وجود ندارد.(بجز ماکرو).

در اکسل از نسخه ۲۰۱۰ به بعد ابزاری به نام Slicer برای فیلتر کردن اضافه شده و قابلیت اصلی آن در ساده سازی فرآیند فیلتر کردن هست. در این مقاله قصد داریم به معرفی این ابزار و نحوه کار با اون بپردازیم. در واقع Slicer یک ابزار گرافیکی برای فیلتر کردن هست که می تونید از اون در تمامی جداول، Pivot Tableها و حتی Pivot Chart ها و یا هر جایی که نیاز به فیلتر کردن داده ها وجود داره استفاده کنید.

مقایسه ابزار Slicer با ابزار فیلتر

همانطور که مشخص هست Slicer و فیلتر هر دو یک کار رو به شیوه های مختلف انجام میدن و با هم تفاوت هایی دارن که به آن ها می پردازیم:

  • از ابزار Slicer می تونیم همزمان برای فیلتر کردن چندین Pivot Table یا Pivot Chart استفاده کنیم اما ابزار فیلتر این امکان رو نداره.
  • ابزار فیلتر بر روی سطرها و ستون ها قرار می گیره و امکان جابه جایی در شیت رو نداره اما Slicer ها به راحتی قابل جابه جایی و حرکت دادن در صفحه هستن.
  • عملکرد ابزار Slicer در صفحه های لمسی خیلی بهتر و کاربر پسندتر از ابزار فیلتر هست.
  • ابزار Slicer نسبت به ابزار فیلتر فضای بیشتری رو در گزارش ها اشغال می کنه.
  • ابزار فیلتر رو میشه با استفاده از VBA خودکار کرد اما انجام این کار برای ابزار Slicer به مراتب پیچیده تر و سخت تر هست.

حالا که مزایا و معایب ابزار Slicer رو بررسی کردیم و تفاوت های اون رو با ابزار فیلتر متوجه شدیم، در ادامه مقاله نحوه کار با ابزار Slicer و تنظیمات اون رو توضیح میدیم.

افزودن ابزار Slicer در Pivot Table و Pivot Chart

برای انجام این کار اول از همه مراحل زیر رو طی می کنیم:

  • روی یکی از فیلدهای Pivot Table کلیک می کنیم؛
  • در صورتی که نسخه اکسل ۲۰۱۳، ۲۰۱۶ یا ۲۰۱۹ هست از تب Analyze و از زیرمجموعه Filters روی گزینه Insert Slicer کلیک می کنیم. در نسخه ۲۰۱۰ اکسل از تب Options گزینه Insert Slicer رو انتخاب می کنیم؛
  • پنجره ای با نام Insert Slicer مطابق شکل ۱ باز میشه که در اون لیست تمامی فیلدهای جدول (سر ستون ها) نمایش داده شده؛
  • از لیست نمایش داده، فیلد هایی که می خواهیم روی آن ها فیلتر انجام بشه رو انتخاب می کنیم.مطابق شکل ۱
  • دکمه OK رو می زنیم و مطابق شکل ۲ Slicer ها نمایش داده می شن.

اضافه کردن Slicer به Pivot Table

شکل ۱- اضافه کردن Slicer به Pivot Table

Slicer های "فروشنده" و "نام محصول"

شکل ۲- Slicer های “فروشنده” و “نام محصول”

همان طور که در شکل ۲ مشاهده می کنید به تعداد فیلدهایی که انتخاب کردیم Slicer ایجاد شده.

در صورتی که Pivot Chart هم به شکل ۲ اضافه کنیم، Slicer هایی که برای Pivot Table ایجاد کردیم برای Pivot Chart هم کاربرد داره. یعنی با فیلتر کردن Slicer هایی که برای Pivot Table ساختیم، Pivot Chart هم فیلتر میشه.

برای ساخت Pivot Chart روی یک قسمت از PivotTable کلیک می کنیم و از تب Insert و زیرمجموعه Charts گزینه Pivot Chart رو مطابق شکل ۳ انتخاب می کنیم.

نحوه ساخت Pivot Chart

شکل ۳- نحوه ساخت Pivot Chart

در صورتی که بخواهیم برای Pivot Chart ، Slicer ایجاد بکنیم مراحل زیر رو طی می کنیم:

  • روی یک قسمت از Pivot Chart کلیک کنید؛
  • از تب Analyze و از زیر مجموعه Filters روی گزینه Insert Slicer کلیک می کنیم؛
  • از لیست نمایش داده شده، فیلد هایی که می خواهیم روی آن ها فیلتر انجام بشه رو انتخاب می کنیم؛
  • دکمه OK رو می زنیم. Slicer مطابق شکل ۴ ایجاد میشه.

اضافه کردن Slicer به Pivot Chart

شکل ۴- اضافه کردن Slicer به Pivot Chart

Slicer ای که برای Pivot Chart ساختیم برای Pivot Table هم کاربرد داره. یعنی فیلتری که روی Slicer شکل ۴ اعمال میشه علاوه بر فلیتر کردن PivotChart، PivotTable متصل به اون رو هم فیلتر می کنه.

Slicer شکل ۴ رو می تونیم در داخل محدوده نمودار و در کنار نمودار هم نمایش بدیم. برای اینکار مطابق ویدئو زیر محدوده نمودار رو به اندازه ابعاد Slicer بزرگتر می کنیم و Slicer رو داخل محدوده نمودار درگ می کنیم. در صورتی که بعد از درگ کردن Slicer به داخل محدوده نمودار، Slicer مخفی شد، روی محدوده نمودار کلیک راست می کنیم و گزینه Send To Back رو انتخاب می کنیم.

ایجاد اسلایسر برای نمودار

افزودن ابزارSlicer  در جدول (Table)

از نسخه ۲۰۱۳ اکسل به بعد علاوه بر Pivot Table در جدول هایی که با فرمت Table ایجاد شدن هم میشه از Slicer استفاده کرد که مراحل انجام کار به شرح زیر هست. (اگر با Table آشنا نیستید مقاله مربوط به Table رو مطالعه کنید):

  • روی یکی از سل های محدوده جدول کلیک می کنیم؛
  • از تب Insert و از زیرمجموعه Filters گزینه Slicer رو انتخاب می کنیم؛
  • از پنجره Insert Slicer مطابق شکل ۵ عناوین ستون هایی که می خواهیم آن ها را فیلتر کنیم انتخاب می کنیم؛
  • دکمه OK رو می زنیم. می بینیم که به تعداد عناوینی که انتخاب کردیم Slicer ایجاد شده.(مطابق شکل ۶)

اضافه کردن Slicer به Table

شکل ۵- اضافه کردن Slicer به Table

Slicer "نام محصول" در Table جهت فیلتر این ستون

شکل ۶- Slicer “نام محصول” در Table

حالا که با روش اضافه کردن ابزار Slicer در جدول، Pivot Table و Pivot Chart آشنا شدیم، در ادمه نحوه فیلتر کردن و انجام سایر تنظیمات رو در ابزار Slicer توضیح میدیم.

فیلتر کردن اطلاعات با Slicer

وقتی که Slicer رو ایجاد می کنیم همه گزینه های داخل Slicer یک رنگ هستند. برای فیلتر کردن کافیه روی هر گزینه ای که می خواهیم کلیک کنیم تا اون گزینه از حالت انتخاب خارج بشه و به رنگ روشن تر دربیاد با این کار گزینه مورد نظر از جدول یا نمودار حذف میشه و فقط داده های نشون داده می شن که در Slicer با رنگ پررنگ تر نمایش داده شدن. مطابق شکل ۷

فیلتر کردن داده ها در Slicer

شکل۷- فیلتر کردن داده ها در Slicer

با استفاده از Slicer اطلاعاتی که در جدول Hide شدن رو هم می تونیم فیلتر کنیم. برای مثال در ویدئو زیر با وجود اینکه ستون “نام محصول” Hide شده (ستون A) اما با استفاده از Slicer می تونیم اسامی محصولاتی که می خواهیم رو فیلتر کنیم و تغییرات رو در بقیه ستون های جدول مشاهده کنیم.

فیلتر اطلاعات پنهان شده توسط Slicer

در مواقعی که بیش از یک Slicer برای جدول تعریف میشه در صورتی که در یک Slicer داده ای رو فیلتر کنیم ممکنه در Slicer بعدی بعضی از باکس ها رنگشون تغییر کنه (نسبت به بقیه باکس ها کم رنگ تر بشن). دلیل این مسئله این هست که بر اساس فیلتری که در Slicer اول انجام دادیم یه سری از داده ها درSlicer  دوم وجود ندارن. برای مثال فرض کنید در جدول شکل ۸ که دو تا Slicer “نام محصول” و “فروشنده” تعریف کردیم در صورتی که در Slicer “فروشنده” اسم “اصغری” رو فیلتر کنیم، در Slicer “نام محصول” باکس “یخچال” کم رنگ تر میشه و این یعنی فروشنده به نام”اصغری” فقط تلویزیون، جاروبرقی و مایکروفر فروخته (مطابق بانک اطلاعاتی فروش شکل ۸) و هیچ داده ای از فروش یخچال براش ثبت نشده.

تاثیر فیلتر کردن Slicer فروشنده به روی Slicer نام محصول

شکل ۸- تاثیر فیلتر کردن Slicer فروشنده به روی Slicer نام محصول

فیلتر کردن چند مورد به طور همزمان در Slicer

برای اینکه بتونیم چند مورد در یک Slicer به طور همزمان انتخاب کنیم، سه روش زیر وجود داره:

  • با نگه داشتن دکمه Ctrl بر روی مواردی که می خواهیم در Slicer کلیک می کنیم.
  • روی دکمه Multi-Select مطابق شکل ۹ کلیک می کنیم و بعد از فعال شدن این دکمه، مواردی که می خواهیم رو از Slicer انتخاب می کنیم.
  • کلید میانبر دکمه Multi-Select ترکیب دو کلید Alt + S هست. برای این کار روی Slicer کلیک می کنیم و بعد دو کلید Alt + S رو همزمان با هم می زنیم. آیکون Multi-Select به حالت فعال در میاد و بعد موارد مورد نظرمون رو انتخاب می کنیم. برای غیر فعال کردن Multi-Select دوباره دو کلیدAlt + S رو با هم می زنیم.

فعال کردن گزینه Multi Select جهت فیلتر چندین گزینه در Slicer

شکل ۹- فعال کردن گزینه Multi Select در Slicer

حذف فیلتر از Slicer

برای حذف کردن فیلتر اعمال شده روی Slicer مطابق شکل ۱۰ روی علامت فیلتر کلیک می کنیم تا غیر فعال بشه (رنگ فیلتر کم رنگ تر میشه و علامت ضربدر هم پاک میشه).

حذف فیلتر Slicer

شکل ۱۰- حذف فیلتر Slicer

روش دیگه برای حذف فیلتر این هست که روی Slicer کلیک می کنیم و همزمان کلیدهای Alt + C استفاده می کنیم و به این شکل فیلتر از Slicer حذف میشه.

جا به جا کردن Slicer

برای جا به جا کردن Slicer نشانگر موس روی Slicer نگه می داریم تا به صورت شکل ۱۰ در بیاد و بعد با درگ کردن Slicer  رو به محل مورد نظر در همون شیت منتقل می کنیم.

تغییر علامت نشانگر موس برای درگ کردن Slicer

شکل ۱۱- تغییر علامت نشانگر موس برای درگ کردن Slicer

تغییر ابعاد Slicer

برای اینکار دو روش وجود داره:

  • با استفاده از نشانگر موس گوشه های Slicer رو به طرفین درگ می کنیم تا به اندازه دلخواه تبدیل بشه.
  • Slicer رو انتخاب می کنیم (مطابق شکل ۱۱) و از تب Slicer Tools Options و در زیرمجموعه Buttons ابعاد باکس های داخل Slicer و از زیرمجموعه Size ابعاد Slicer رو تغییر میدیم. در صورتی که بیش از یک Slicer داشته باشیم و بخواهیم ابعاد همه دقیقا یک اندازه باشه، با استفاده از این روش می تونیم تنظیمات رو دقیق انجام بدیم به این صورت که در قسمت های Buttons و Size همه Slicer ها اعداد یکسان رو وارد می کنیم. علاوه بر تغییر اندازه، در زیرمجموعه Buttons در قسمت Columns می تونیم تنظیم کنیم که باکس ها در چندتا ستون قرار بگیرن که در صورتی تعداد باکس ها زیاد باشن می تونیم با تنظیم این قسمت باکس ها در رو دو ستون یا بیشتر نمایش بدیم .مطابق شکل ۱۲

تغییر اندازه Slicer و باکس های Slicer بااستفاده از تب Slicer Tools Options

شکل ۱۲- تغییر اندازه Slicer و باکس های Slicer بااستفاده از تب Slicer Tools Options

تنظیم قرار گرفتن باکس های Slicer در دو ستون

شکل ۱۳- تنظیم قرار گرفتن باکس های Slicer در دو ستون

ثابت کردن مکان Slicer در یک شیت

بعضی از مواقع به خصوص در طراحی داشبورها لازم هست که Slicer هایی که ایجاد کردیم در مکانی مشخص ثابت بمونن برای اینکار مراحل زیر رو طی می کنیم:

  • روی Slicer کلیک راست می کنیم و از لیست نمایش داده شده گزینه Size and Properties رو انتخاب می کنیم. پنجره ای با نام Format Slicer در سمت راست شیت باز می شه؛
  • در پنجره Format Slicer و از زیرمجموعه Properties گزینه Don’t move or size with cells box رو انتخاب می کنیم. مطابق شکل ۱۳

با اینکار در صورتی که سطرها یا ستون ها حذف یا اضافه بشن، فیلد اطلاعاتی از جدول حذف بشه یا هر تغییر دیگری در شیت داده بشه،Slicer  حرکت نخواهد کرد.

ثابت کردن Slicer

شکل ۱۴- ثابت کردن Slicer

قطع ارتباط Slicer با Pivot Table

در صورتی که بخواهیم ارتباط بین یک Slicer رو با Pivot Table که به اون متصل هست قطع کنیم مراحل زیر رو طی می کنیم:

  • روی یکی از سل های Pivot Table کلیک می کنیم؛
  • در نسخه ۲۰۱۳، ۲۰۱۶ و ۲۰۱۹ از تب Analyze و از زیرمجموعه Filter روی گزینه Filter Connections کلیک می کنیم. در نسخه ۲۰۱۰ اکسل از تب Options و از زیر مجموعه Insert Slicer گزینه Slicer Connections رو انتخاب می کنیم.
  • در پنجره Filter Connections اسامی Slicer هایی که به Pivot Table متصل هستند نمایش داده شده. برای حذف ارتباط بین Slicer و Pivot Table تیک مربوط به نام Slicer رو بر می داریم و دکمه OK رو می زنیم. مطابق شکل ۱۵

در صورتی که در شکل ۱۵ تیک مربوط به Slicer “فروشنده” رو برداریم و بعد Slicer فروشنده رو فیلتر کنیم، مشاهده می کنیم که در Pivot Table هیچ تغییری ایجاد نمیشه چون ارتباطش با Slicer فروشنده حذف شده.

دقت کنید که با این کار Slicer حذف نشده و فقط ارتباطش با Pivot Table قطع شده و هر زمان که بخواهیم می تونیم با فعال کردن تیک نام Slicer از پنجره Filter Connections دوباره ارتباط رو ایجاد کنیم. این امکان برای مواقعی که یک Slicer به چندین Pivot Table متصل هست می تونه کاربردی باشه.

حذف ارتباط Slicer با Pivot Table

شکل ۱۵- حذف ارتباط Slicer با Pivot Table

حذف کردن Slicer

برای حذف Slicer یک روش این هست که Slicer رو انتخاب کنیم و دکمه Delete رو بزنیم و روش دوم اینکه روی Slicer کلیک راست کنیم و از لیست نمایش داده شده گزینه Remove <Slicer Name> (مثلا “نام محصول” Remove) رو انتخاب کنیم.

تغییر الگوی (Style) ظاهری Slicer

برای ایجاد نمای ظاهری زیبا و کاربر پسند برای Slicer ها هم می تونیم از طرح های پیش فرض اکسل استفاده کنیم و هم طرح مورد نظر خودمون رو ایجاد بکنیم.

برای استفاده از طرح های پیش فرض اکسل روی Slicer کلیک می کنیم و از تب Slicer Tool Options و از زیر مجموعه Slicer Styles الگوی مورد نظرمون رو انتخاب می کنیم. مطابق شکل ۱۶

Slicer Style

شکل ۱۶- Slicer Style

در صورتی که بخواهیم الگوی جدیدی ایجاد کنیم مراحل زیر رو طی می کنیم:

  • از زیر مجموعه Slicer Styles گزینه New Slicer Style رو انتخاب می کنیم مطابق شکل ۱۶
  • در پنجره New Slicer Style مطابق شکل ۱۷ برای الگوی جدید یک اسم انتخاب می کنیم.
  • در قسمت Slicer Element تعیین می کنیم که برای چه قسمتی از Slicer (مثلا برای باکس های Slicer یا کل Slicer) می خواهیم فرمت تعریف کنیم که مطابق شکل ۱۷ گزینه Whole Slicer رو انتخاب می کنیم.
  • روی دکمه Format کلیک می کنیم. پنجره ی Format Slicer Element مطابق شکل ۱۸ نمایش داده میشه. در این قسمت در تب های Font، Border و Fill تنظیمات رنگ و فونت دلخواه رو انجام میدیم و دکمه OK رو می زنیم.
  • در پنجره New Slicer Style دکمه OK رو می زنیم و مشاهده می کنیم که مطابق شکل ۱۹ الگوی تعریف شده در قسمت Slicer Style و زیر مجموعه Custom اضافه شده.

New Slicer Style

شکل ۱۷- New Slicer Style

Format Slicer Elemen

شکل ۱۸- Format Slicer Element

نمایش الگوی جدید در Slicer Style

شکل ۱۹- نمایش الگوی جدید در Slicer Style

نکته:
الگوی جدیدی که برای Slicer ایجاد کردیم فقط در فایل اکسلی که روی آن کار می کنیم ذخیره می شه یعنی اگر یک فایل اکسل جدید باز کنیم در قسمت Slicer Style الگویی که تعریف کردیم نمایش داده نمیشه. در صورتی که بخواهیم الگوی جدید در هر فایل اکسلی که باز می کنیم هم نمایش داده بشه باید فایل اکسلی که در اون الگوی جدید ساختیم به صورت Excel Template (*.xltx file) ذخیره کنیم و هر بار که می خواهیم فایل اکسل جدید باز کنیم از این Template استفاده کنیم.

 

در این مقاله سعی کردیم ابزار مفید و کاربردیه Slicer رو برای فیلتر کردن اطلاعات معرفی کنیم که ظاهر زیبا و انعطاف پذیر و کارکردبیشتری نسبت به فیلتر معمولی داره. با مطالعه این مقاله می تونید از این به بعد گزارش ها و داشبورهای زیباتر و مفیدتری بسازین.

کلیدواژه : پیوت
آواتار
127

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • نوید ۲۰ بهمن ۱۳۹۹ / ۱:۴۷ ب٫ظ

    درود
    من یک سوالی دارم به این شکل هستش سناریو من :
    یک لیست لینک ها روی دارم برای دانلود کردن توسط اکسل که فرض بگیرید روزانه ۱۰۰ تا url از این رو می خواهم با استفاده از VBA یا هر مایکرویی که شما صلاح بدانید این ها را به صورت جداگانه در یک فولدر مشخص با یک آدرس مشخص (که brows شدنش رو از من بخوادش قبل از دانلود ) به من بدهد البته اگر نیاز به خطاهای مختلفی همچون اینولید یو آر ال یا حجم ناکافی در دیسک نیمه تمام بودن دانلود یا قطع بودن نتوورک و … هم باشد خالی از لطف نیستش اما چیز بسیار مهمی که برای من می باشد نام فایلی هستش که میخواهم از داخل لینک مربوطه دریافت کنه نه نام سروری فایل دقت بفرمایید بر روی سرور یک نام می گذارند اون نام مد نظر بنده نیستش میخوام ری نیم بشه به نام مثلا سر تیتر یک کتاب و نام نویسنده هایش اگر می دانید کمک کنید
    با تشکر

    • سامان چراغی ۲۴ اسفند ۱۳۹۹ / ۲:۴۱ ب٫ظ

      سلام
      وقت بخیر
      سناریو شما خودش یک پروژه هست که در قالب کامنت نمیگنجه.
      میتونید سوالتون رو در گروه تلگرام مطرح کنید و با کمک دوستان حل کنید و یا اینکه به ایمیل Info[@]excepedia.net درخواست انجام پروژه بدید و ما براتون انجام بدیم.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان