جداول اکسل و اسلایسرهای جداول (Table Slicers) – بخش2

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی بخش اول جداول اکسل و اسلایسرهای جداول (Table Slicers) پرداختیم، در این مقاله به ادامه آموزش Table Slicers می پردازیم.

استفاده از اسلایسر های جداول برای فیلتر کردن داده‌ها

در اکسل 2010، اسلایسرها برای ساده کردن یا فیلترکردن جداول محوری (PivotTables) معرفی شده بودند (برای جزئیات بیشتر، فصل 45: استفاده از جداول محوری و اسلایسرها برای نمایش داده‌ها را ببینید). از اسلایسرهاکه در اکسل 2013 معرفی شدند می‌توان برای فیلترکردن جداول استفاده کرد. مزیت این اسلایسرها آن است که شما می‌توانید با استفاده از آنها به‌سادگی تمامی فیلترهایی را که ردیف‌های قابل‌مشاهده را تعریف می‌کنند و کل محاسبات را دیده و بررسی کنید. مثلاً برای ایجاد فیلترهایی برای مثال فروش لوازم آرایشی (فایلی به نام Tablemakeuptotals.xlsx را ببینید) به‌سادگی در درون جدول کلیک کرده و بعد از تب Insert گزینه Slicer را از گروه گزینه‌های Filters انتخاب کنید. برای ایجاد اسلایسرهای ستون‌های Name، Product و Location به‌سادگی آنها را همان‌طور که در تصویر 14-26 نشان‌داده‌شده انتخاب کنید.

تصویر 14-26 ایجاد اسلایسرها برای ستون‌های Name، Product و Location

 این اسلایسرها در تصویر 15-26 نمایش‌داده‌شده‌اند. وقتی با فیلتر کننده‌ای کار می‌کنید می‌توانید فیلترها را با استفاده از کلیدهای Shift برای انتخاب موارد کنار هم و از کلید Ctrl برای انتخاب مواردی که کنار هم نیستند مورداستفاده قرار دهید. اسلایسرهای مورداستفاده ما تمامی فروش‌های شامل فروش رژلب‌های انجام شده توسط اشلی در شرق را فیلتر می‌کنند. توجه کنید که ردیف جمع کل و ردیف‌های قابل‌مشاهده کاملاً شبیه تصویر 13-26 هستند. کلیک کردن روی نماد قیف یک اسلایسر فیلتر را در آن ستون جدول پاک می‌کند.

تصویر 15-26 اسلایسرهای استفاده شده برای فیلترکردن یک جدول

با کلیک کردن به درون یک اسلایسر، به‌سادگی می‌توانید با نگاه‌داشتن کلید Ctrl اندازه آن را تغییر داد. همچنین می‌توان بر تب Slicer Tools Options در نوار منو کلیک کرد. سپس می‌توان بسیاری از ویژگی‌های آن از قبیل شکل اسلایسر، نام عنوان اسلایسر، تعداد ستون‌ها و اندازه آن را تغییر داد.

محاسبه ردیف جمع کل بر اساس اسلایسر ها

جداول کار چندان راحتی نیست. فایلی را به نام sumwithslicerstemp.xlsx در نظر بگیرید (از پوشه Templates همین فصل). در این فایل فروش سالیانه در ایالات متحده و کشورهای خارجی ارائه شده است. زیرمجموعه‌ای از داده‌ها (داده‌های ماه مارس تا ماه سپتامبر ناپدید هستند) در تصویر 16-26 نمایش‌داده‌شده‌اند. می‌خواهیم از این اسلایسر‌ها برای انتخاب هر زیرمجموعه سال‌ها استفاده کرده و فروش درون ایالات متحده و یا فروش‌های بین‌المللی را برگزیده و جمع کل فروش برای هر زیرمجموعه از داده‌ها را محاسبه کنیم. برای شروع تلاش می‌کنیم با واردکردن فرمول =SUM(Table1[[January]:[December]]) در سلول Q2 کل فروش‌های جدول را جمع بزنیم. اکنون درآمد کلی برابر 14.977.000 دلار به دست می‌آید. در قدم بعدی اسلایسر‌هایی برای ستون‌های Year و Type قرار داده و US و سال‌های 2010 و 2011 را انتخاب می‌کنیم.

تصویر 16-26 جمع زدن داده‌های یک جدول با استفاده از اسلایسر‌ها

همان‌طور که در تصویر 17-26 نشان‌داده‌شده هنوز جمع کل 14.977.000دلار را به دست آورده‌ایم. اما این درست نیست. مشکل اینجاست که اکسل ردیف‌های پنهان را نادیده نمی‌گیرد. اگر در سلول P4 فرمول =AGGREGATE(9,5,Table1[[January]:[December]]) را وارد کرده باشیم، هنگامی که فروش‌های بین‌المللی و سال‌های 2010 تا 2011 را با اسلایسر انتخاب می‌کنیم مقدار فروش کل صحیح یعنی 3.509.000 دلار را دست می‌آوریم. اولین آرگومان 9 در تابع AGGREGATE مشخص می‌کند که می‌بایست یک جمع را محاسبه کنیم و دومین آرگومان 5 به اکسل می‌گوید که ردیف‌های مخفی شده در اسلایسر منتخب را نادیده بگیرد. اگر داده جدید وارد کنید (مثلاً برای سال 2016) خواننده می‌تواند تأیید کند که اسلایسر سال اکنون سال 2016 را نشان خواهد داد.

تصویر 17-26 استفاده از اسلایسر ‌ها برای محاسبه جمع کل درآمد

ارجاع به بخشی از یک جدول در سایر بخش‌ های کاربرگ

فایلی به نام Tablestructure.xlsx مثال‌های زیادی در باره اینکه چگونه می‌توان وقتی بیرون از محدوده جدول کار می‌کنید به بخش‌هایی از جدول ارجاع داد نشان می‌دهد. این ارجاع‌ها اغلب ارجاع‌های ساختاری (تصویر 18-26 را ببینید) نامیده می‌شوند. وقتی که نام جدولی را در فرمولی وارد می‌کنید، ویژگی تکمیل خودکار یا AutoComplete نام ستون‌ها و مشخص‌کننده‌های زیر را برای انتخاب در اختیار قرار می‌دهد:

• Table Name تمامی سلول‌های درون جدول به‌استثنای سرعنوان‌ها و ردیف‌های جمع کل.
• #All تمامی سلول‌های درون جدول به همراه ردیف جمع کل (اگر وجود داشته باشد)
• #Data تمامی سلول‌های درون جدول به‌جز اولین ردیف و ردیف جمع کل.
• #Headers تنها ردیف سرعنوان
• #Totals تنها ردیف جمع کل. اگر ردیف جمع کلی نباشد، این فرمول محدوده سلول خالی را به ما بر می‌گرداند.
• @/#This Row تمامی مداخل جدول در ردیف حاضر (ردیفی که در آن هستیم) نماد@ در اکسل 2010 جایگزین [#This Row] برای مشخص‌کردن تمامی سلول‌های موجود در ردیف گردید.

یک ارجاع ستون (Column Reference) شامل تمامی سلول‌های یک ستون جدول می‌گردد به‌استثنای سرستون و یا مداخل ردیف جمع کل (در صورت موجود بودن)

در اینجا مثال‌هایی درمورد اینکه این مشخص‌کننده‌های جداول چگونه می‌توانند در فرمول‌ها استفاده شوند را ارائه می‌دهیم (کاربرگ Original در فایلی به نام Tablestructure.xlsx را ببینید)

• در سلول C15 فرمول =COUNTA(Table1[#All]) به ما رقم 55 را برمی‌گرداند چرا که جدول دارای 55 مدخل است.
• در سلول C16 فرمول =COUNTA(Table1) رقم 45 را به ما برمی‌گرداند چرا که سرعنوان و ردیف جمع کل حساب نشده‌اند. در سلول C17 فرمول =COUNTA(Table[#Data]) رقم 45 را به ما برمی‌گرداند چرا که ارجاع به محدوده سلول D5:H13 بوده است.
• در سلول C18 فرمول =COUNTA(Table1[#Headers]) رقم 5 را برمی‌گرداند چرا که ارجاع تنها به‌ردیف سرعنوان (محدوده D4:H18) بوده است.
• در سلول C19 فرمول =SUM(Table1[Q1]) عدد 367 را به ما می‌دهد چرا که فرمول مداخل موجود در محدوده E5:E13 را جمع می‌زند.
• در سلول C20 فرمول =SUM(Table1[#Totals]) تمامی ارقام ردیف جمع کل را جمع زده و عدد 1340 را برمی‌گرداند که جمع کل تمام مداخل جدول است.
• در سلول C21 فرمول =SUM(Table1[[#Data],[Q1]:[Q3]]) تمامی ارقام داده را که در ستون‌های محدوده Q1:Q3 هستند را به شکلی فراگیر (سلول‌های E5:G13) جمع می‌زند؛ بنابراین نام ستون‌ها با یک دونقطه از هم جدا شده و شامل تمامی ارقام داده بین آن دو و همچنین نام ستون پیش از دو نقطه و نام ستون بعد از دونقطه است.
• در سلول B8 فرمول =SUM(Table1[@]) تمامی ارقام ردیف 8 را به شکل (41+28+49+40), 158 جمع می‌زند.

البته تمامی این فرمول‌ها به هنگامی که داده‌های جدید به جدول اضافه شوند به‌روز می‌گردند.

تصویر 18-26 ارجاعات ساختاری

اعمال قالب‌ بندی شرطی به شکل خودکار به داده‌ های جدید اضافه شده

بله ویژگی قالب‌بندی شرطی درون اکسل به شکلی خودکار شامل داده‌های جدید جدول (تصویر 19-26 را ببینید) نیز می‌شود. همان‌طور که خواهید دید اطمینان حاصل کردن از اینکه قالب‌بندی شرطی ایجاد شده با گزینه Use A Formula به هنگام اضافه‌کردن داده‌های جدید به جدول به طور خودکار به‌روز شود کار پردردسری است.

تصویر 19-26 گسترش قالب‌بندی شرطی به داده‌های جدید جدول به شکل خودکار

برای تصویرکردن این موضوع در کاربرگ Original در فایلی به نام TableStructure.xlsx قالب‌بندی شرطی ای برای مشخص‌کردن بیشترین فروش‌های Q1 در ستون E اعمال می‌کنیم. همان‌طور که در تصویر 18-26 نشان‌داده‌شده، ارقام ردیف‌های 7، 12 و 13 به رنگ قرمز مشخص شده‌اند. در کاربرگ Add Biggersale رقم 90 را در سلول E14 وارد کرده‌ایم. همان‌طور که در تصویر 19-26 نشان‌داده‌شده، این رقم بزرگ‌ترین ورودی ستون محسوب شده و فوراً قرمز می‌شود. سلول E7 دیگر به رنگ قرمز نیست چرا که دیگر یکی از سه عدد بزرگ در ستون E جدول محسوب نمی‌شود.

اکنون فرض کنید که می‌خواهید بزرگ‌ترین رقم در هریک از ستون‌های فایلی به نام Formattablesfinal.xlsx را که در تصویر 20-26 نشان‌داده‌شده را مشخص کنید.

تصویر 20-26 مشخص‌کردن بزرگ‌ ترین رقم در هر یک از ستون‌ ها

این قالب‌بندی را به‌راحتی با گزینه Use A Formula درست کردیم، اما اگر بخواهیم قالب‌بندی به‌ردیف جدید که به جدول اضافه شده اعمال شود، می‌بایست کادر محاوره‌ای New Formattin Rule(در تب Home و زیرگروه گزینه‌های Styles روی گزینه Conditional Formatting و سپس New Rule کلیک کنید)را همان‌طور که در تصویر 21-26 می‌بینید پر کنید.

تصویر 21-26 با این تنظیمات مطمئن خواهیم شد که وقتی داده‌های جدیدی به جدول وارد می‌شود، همچنان بزرگ‌ترین رقم موجود در ستون مشخص خواهد شد.

وقتی که format Values را انتخاب کردید، فرمول =F8=MAX(F$8:F13) را در آن وارد کنید. ترفند اصلی اینجا آن است که پیش از رقم 13 علامت دلار ($) را قرار ندهید. چنانچه F$13 را وارد کنید، اکسل قالب‌بندی را چنان قفل می‌کند که به ردیف‌هایی پس از ردیف 13 اعمال نشود. حالا ردیف دیگری از داده‌ها که حاوی اعدادی بزرگ هستند را به جدول اضافه کنید تا ببینید چگونه این قالب‌بندی به شکل خودکار هرگاه ردیف داده‌های جدید اضافه می‌کنید آن را به‌روز می‌نماید.

مسئله‌های این فصل

فایلی به نام Singers.xlsx حاوی فهرستی از ترانه‌های خوانده شده توسط خواننده‌های مختلف و زمان هریک از ترانه‌ها (به تخمین دقیقه) است. کاربرگ خود را به‌گونه‌ای تنظیم کنید که تعداد کل ترانه‌های خوانده شده توسط امینم و متوسط طول زمان هر ترانه را محاسبه کند. البته فرمول‌های شما می‌بایست با اضافه‌شدن داده‌های جدید به طور خودکار به‌روز شوند.

فایلی به نام Tableexample.xlsx را به‌گونه‌ای تنظیم کنید که رتبه هریک از فروشندگان بر اساس درآمد کل و تعداد واحدهای فروخته شده در کاربرگ قرار گیرند. البته چنانچه داده‌های جدیدی وارد شوند، رتبه‌های شما می‌بایست به‌صورت خودکار به‌روز شوند. احتمالاً برای شما مناسب‌تر است که از تابع Rank.EQ استفاده کنید. دستور زبان تابع Rank عبارت است از =RANK.EQ(number,array,0). این تابع رتبه‌های عددی را در آرایه‌ای از محدوده‌ها به دست می‌آورد که rank=1 بزرگ‌ترین عدد آنها است.

فایلی به نام Lookupdata.xlsx حاوی شماره محصولات و قیمت‌های آنها است. کاربرگ را به‌گونه‌ای تنظیم کنید که با واردکردن شماره محصول کاربرگ شما قیمت آن را نمایش دهد. البته هنگامی که محصولات جدیدی معرفی می‌شود، فرمول شما می‌بایست به‌روز شده و همچنان جواب بدهد.

فایلی به نام Productlookup.xlsx حاوی فروش محصولات در هر روز از هفته است. فرمولی تنظیم کنید که فروش هر محصول در روزی معین را برگرداند. البته چنانچه داده‌های محصولات جدیدی وارد شوند، فرمول شما می‌بایست قادر باشد فروش‌های آن محصولات را نیز محاسبه نماید.

فایلی به نام Tablepie.xlsx حاوی اطلاعات فروش محصولات مختلفی است که در فروشگاه کوچکی فروخته شده است. می‌خواهید نمودار دایره‌ای را تنظیم کنید که این داده‌های فروش را به شکل خلاصه نمایش دهد. البته چنانچه رده‌های محصولات جدیدی به فروشگاه اضافه شوند، این نمودار دایره‌ای می‌بایست به شکل خودکار داده‌های جدید را در خود نمایش دهد.

فایلی به نامTablexnpvdata.xlsx جریان نقدی دریافت شده توسط کسب‌وکاری کوچک را فهرست کرده است. فرمولی تنظیم کنید که ارزش خالص کنونی (NPV) تمامی جریان‌های نقدی (از پنجم ژانویه 2007) را محاسبه نماید. نرخ نزول بانکی سالیانه را ده درصد فرض کنید. البته چنانچه جریان‌های نقدی جدید وارد شوند، فرمول شما می‌بایست به شکل خودکار آنها را محاسبه نماید.

فایلی به نام Nikedata.xlsx حاوی سود فروش فصلی شرکت Nike است. نموداری از فروش شرکت Nike تهیه کنید که به شکل خودکار حاوی داده‌های سود فروش جدید نیز بشود.

از داده‌های موجود در فایلی به نام Tablemakeuptempt.xlsx(از پوشه Templates همین فصل) تعداد کل واحدهای فروخته شده و درآمد فروش برق لب یا رژلب‌های فروخته شده توسط Jen یا Ashley در محدوده شرق را مشخص نمایید.

فایلی به نام Closet.xlsx حاوی نام افراد و دستمزدهای آنها است. کاربرگی تنظیم کنید که با واردکردن هر رقم نام شخصی که نزدیک‌ترین میزان دستمزد را به آن رقم داشته باشد پیدا کند. کاربرگ شما می‌بایست در صورت اضافه‌شدن نام‌های جدید یا حذف شدن نام‌های قدیمی همچنان این هدف را دنبال نماید.

فایلی به نام Adagency.xlsx حاوی دستمزد و سن کارمندان یک شرکت تبلیغاتی است. صفحه گسترده‌ای تنظیم کنید که میانگین دستمزد و سن هریک از کارمندان این شرکت را محاسبه کند. البته این محاسبات چنانچه شرکت کارمند جدیدی استخدام کند یا کارمندی اخراج کند می‌بایست به شکل خودکار به‌روز شوند.

فایلی به نام Problem11data.xlsx حاوی اطلاعات آماری بازیکنان خط حمله NFL است. نام بازیکن خط حمله‌ای در سلول I2 وارد می‌شود. فرمولی تنظیم کنید که تمامی ردیف داده‌های مربوط به بازیکن خط حمله وارد شده در سلول I2 را مشخص نماید. چنانچه ردیف جدیدی از داده‌ها به کاربرگ وارد شود، فرمول شما می‌بایست به طور خودکار به‌روز شود.

فایلی به نام Problem 12data.xlsx در یک سلول حاوی نام ایالت و جمعیت آن است. از فرمول اکسل استفاده کنید تا ارقام جمعیت را در ستون جداگانه‌ای درج کنید. اگر ردیف جدیدی وارد شود، فرمول شما می‌بایست به طور خودکار بر داده‌های جدید نیز اعمال شود. می‌توانید فرض کنید که نام ایالات شامل بیش از دو حرف است.

فایلی به نام Problem13data.xlsx حاوی داده‌های فروش 2000 شرکت است. فرمولی بنویسید که تعداد شرکت‌های بانکی و کسری از درآمد کسب شده توسط بانک‌هایی که حداقل بیست میلیارد دلار درآمد دارند را محاسبه نماید. چنانچه ردیف جدیدی از داده اضافه شود، پاسخ‌های شما می‌بایست به‌روز شوند.