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

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

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

اکثریت افراد وقتی از مایکروسافت اکسل استفاده می‌کنند، اغلب داده‌های جدید در آن وارد می‌نمایند. سپس به شکل دستی فرمول‌ها، قالب‌بندی‌ها و نمودارها را به‌روز می‌کنند. چه‌کار پر دردسری! قابلیت‌های جداول اکسل این کار پرمشقت را به خاطره‌ای درگذشته تبدیل کرده است.

سؤال‌هایی که در این فصل پاسخ داده خواهند شد:

• در کاربرگی اعدادی از کالاهای فروخته شده و سود کلی هر فروشنده را وارد کرده‌ایم و می‌توانیم به‌سادگی میانگین قیمت هر واحد فروخته شده توسط هر شخص را محاسبه کنیم. چگونه می‌توان قالب‌بندی مناسبی ایجاد کنیم که چنانچه داده‌های جدیدی وارد کنیم به شکل خودکار بر آنها کپی (اعمال) شود. همچنین آیا راه ساده‌ای برای کپی‌کردن خودکار فرمول‌ها به هنگام اضافه‌کردن داده‌های جدید وجود دارد؟
• در کاربرگی قیمت گاز طبیعی چندین سال را وارد نموده و نمودار خطی بسیار مناسبی برای نمایش تغییرات قیمت ماهیانه ایجاد کرده‌ایم. آیا می‌توان همه چیز را به‌گونه‌ای تنظیم کرد تا وقتی داده‌های قیمت‌های جدید گاز وارد می‌شود، نمودار به شکل خودکار به‌روز شود؟
• برای هر معامله، فروش مواردی چون نام فروشنده، تاریخ فروش، نام محصول، مکان فروش و حجم معامله را داریم. آیا می‌توان به‌سادگی مثلاً میزان کل فروش رژ لب در محدوده شرق توسط اشلی یا هالاگان را به شکلی موجز نمایش داد؟
• چگونه از ویژگی اسلایسرهای جداول (که در اکسل 2013 اضافه شده) استفاده کنیم تا به ما کمک کند داده‌ها را در یک جدول اکسل فیلتر (تکه‌تکه) کنیم؟
• چگونه می‌توان به‌سادگی در سایر بخش‌های کاربرگ به بخشی از یک جدول ارجاع داد؟
• آیا قالب‌بندی شرطی به شکل خودکار به داده‌های جدید اضافه شده به جدول اعمال می‌شود؟

پاسخ به سؤالات این فصل:

در کاربرگی اعدادی از کالاهای فروخته شده و سود کلی هر فروشنده را وارد کرده‌ایم و می‌توانیم به‌سادگی میانگین قیمت هر واحد فروخته شده توسط هر شخص را محاسبه کنیم. چگونه می‌توان قالب‌بندی مناسبی ایجاد کنیم که چنانچه داده‌های جدیدی وارد کنیم به شکل خودکار بر آنها کپی (اعمال) شود. همچنین آیا راه ساده‌ای برای کپی‌کردن خودکار فرمول‌ها به هنگام اضافه‌کردن داده‌های جدید وجود دارد؟

فایلی به نام Tableexampletemp.xlsx در پوشه Templates این فصل قرار گرفته است (تصویر 1-26 را مشاهده کنید) که حاوی واحدهای فروخته شده و داده‌های مربوط به سود هریک از شش فروشنده است. می‌دانیم که قرار است داده‌های جدیدی اضافه شود که این داده‌ها از ردیف دوازده آغاز می‌شوند. همچنین می‌خواهیم در ستون H میانگین قیمت (تعداد واحدها تقسیم بر سود) کسب شده توسط هر شخص را محاسبه کنیم؛ بنابراین می‌خواهیم فرمول مناسبی برای داده‌ها ایجاد کرده و کاری کنیم که فرمول میانگین قیمت با اضافه‌شدن داده‌های جدید به شکل خودکار بر آنها کپی شود (اعمال شود)

تصویر 1-26 داده‌های ایجاد یک جدول

ایجاد جدول به شما اجازه می‌دهد که تحلیل‌ها و قالب‌بندی‌های شما به هنگام ورود داده‌های جدید به شکل خودکار به‌روز شوند. کار را با انتخاب محدوده داده حاضر (E5:G11) به همراه سرستون‌ها شروع می‌کنیم. سپس در تب Insert در نوار منو روی گزینه Table کلیک می‌کنیم و یا کلیدهای Ctrl+T را فشار می‌دهیم. پس از انتخاب گزینه My Table Has Headers (ممکن است خودش به شکل پیش‌فرض انتخاب شده باشد) و کلیک بر روی دکمه OK می‌بینیم که محدوده جدول (E5:G11) به شکل بسیار مناسبی قالب‌بندی شده است.

این روند قالب‌بندی هر زمان که داده‌ای به جدول وارد شود به شکل خودکار اعمال می‌گردد. وقتی که بر روی جدولی کار می‌کنیم بسیاری از اشکال جداول و ویژگی‌های آن در تب Design در اختیار ما قرار می‌گیرند (تصویر 2-26 را ببینید) تب Design تنها وقتی که سلول فعال درون جدولی باشد قابل دیدن است. می‌توانید سبک قالب‌بندی خاصی را که می‌خواهید به هنگام اضافه‌کردن داده‌های جدید به آنها اعمال شود را از این بخش انتخاب کنید.

تصویر 2-26 گزینه‌های انتخابی طراحی جدول

توجه کنید که همان‌طور که در تصویر 3-26 می‌بینید، سرستون‌ها دارای پیکان‌های پایین رو هستند. این پیکان‌ها به‌عنوان فیلترهایی استفاده می‌شوند که می‌توان از آنها برای مرتب کردن یا فیلترکردن جدول استفاده کرد (جریان فیلترکردن جداول را به‌تفصیل در همین فصل مورد بررسی قرار خواهیم داد.)

تصویر 3-26 سرستون‌ها با پیکان‌های پایین رو

 به سلول‌های جدول انتخاب شده (که شامل سرستون‌ها نیز می‌شوند) به شکل پیش‌فرض به نام Table 1 داده می‌شود. این نام را با استفاده از گزینه‌های Properties در تب Design به Sales تغییر می‌دهیم. چنانچه بر تب Formulas و سپس بر گزینه Name Manager (در گروه گزینه‌های Defined Names) کلیک کنید می‌توانید ببینید که محدوده E6:G11 به نام Sales تغییر نام داده شده است.

زیبایی این مفهوم محدوده (و جدول) آن است که این محدوده به شکلی پویا گسترش پیدا می‌کند تا ردیف‌های جدیدی را که به انتهای جدول و ستون‌های جدیدی را که به سمت راست جدول اضافه شده را پوشش دهد. در فصل 22 بنام تابع OFFSET از تابع OFFSET استفاده کردیم تا محدوده‌ای پویا را ایجاد کنیم اما استفاده از قابلیت‌های جدول باعث می‌شود انجام‌دادن این کار ساده‌تر و سریع‌تر به یک‌چشم برهم زدن انجام شود.

فرض کنید می‌خواهید در سلول D15 سود کل را محاسبه کنید. کار را با تایپ فرمول SUM(S آغازمی کنیم. سپس اکسل به شما گزینه‌ای برای تکمیل خودکار مدخل محدوده جدول Sales را ارائه می‌دهد. در اینجا ویژگی AutoComplete را با انتخاب جدول Sales (با یک‌بار کلیک کردن بر روی آن و فشار دادن دکمه جهت پایین) و فشار کلید Tab اجرا می‌کنیم. سپس وقتی فرمول =SUM(Sales را می‌بینیم و علامت کروشه ] را تایپ می‌کنیم، خاصیت Formula AutoComplete یا تکمیل خودکار فرمول گزینه‌هایی برای تکمیل فرمول با سرستون از جدول Sales ارائه می‌دهد.

می‌توانید فرمول خود را به این شکل =SUM(Sales[Revenue]) تکمیل کرده تا همان‌طور که در تصویر 4-26 می‌بینید سود کل را به میزان 155.480 دلار محاسبه نماید. بعدها در همین فصل مثالی از انتخاب مداخل در کادر محاوره‌ای AutoComplete که با نمادهای عددی (#) آغاز می‌شوند را خواهید دید.

تصویر 4-26 سود کل داده‌های اصلی

چنانچه ردیف جدید داده‌ها اضافه شود، داده‌های این ردیف به شکل خودکار درون فرمول محاسبه می‌شوند. برای نمایش این ایده می‌توانید داده‌های جدیدی به‌ردیف 12 اضافه کنید: همان‌طور که در تصویر 5-26 می‌بینید، آماندا 400 واحد از محصولات را به قیمت 5000 دلار فروخته است. توجه کنید که سود کل به میزان 5000 دلار بالا رفته و به رقم 160.480 رسیده است.

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

تصویر 5-26 داده‌های جدید در ردیف 12 به جدول اضافه شده است.

حالا فرض کنید که می‌خواهید در ستون H قیمت کسب شده توسط هر فروشنده از هر واحد را محاسبه کنید. برای انجام این کار در سلول H5 به‌عنوان سرستون عبارت Unit Price را تایپ کرده و سپس در سلول H6 فرمول =Sales[ را تایپ کنید. در لیست AutoComplete روی گزینه Revenue دو بار کلیک کرده و بعد یک کروشه بسته را تایپ کنید. این فرمول حالا به شکل =SALES[Revenue] است. حالا یک علامت اسلش (/) را تایپ کرده و از خاصیت Formula AutoComplete استفاده کنید تا فرمول را به این شکل =SALES[Revenue]/Sales[Units] تکمیل کنید.

در اینجا اتفاقی شگفت‌انگیز رخ می‌دهد. همان‌طور که در تصویر 6-26 می‌بینید، اکسل به طرزی خودکار فرمول‌ها را تا انتهای جدول در سلول H12 کپی می‌کند. اگر به هریک از سلول‌های ستون H بروید، فرمول به شکل [@Revenue]/[@Units] ظاهر می‌شود. البته درک فرمول [@Revenue]/[@Units] بسیار آسان تر از =G6/F6 است. این فرمول را می‌توان به این شکل تفسیر کرد که هر چه در ردیف حاضر در ستون Revenue است را بردار و آن را بر هر چه در ردیف حاضر در ستون Units هست تقسیم کن.

تصویر 6-26 کپی خودکار فرمول قیمت واحدها

چنانچه هرجایی از جدول کلیک کنید، تب Table Tools Design در نوار منو ظاهر می‌شود و به شما انتخاب‌هایی شامل گزینه‌های زیر ارائه می‌دهد (تصویر 2-26 را ببینید)

• از گزینه Change Table Name می‌توان برای تغییر نام یک جدول استفاده کرد. نام جدول را از Table 1(نام پیش‌فرض) به Sales تغییر داده‌ایم. این گزینه در گروه گزینه‌های Properties یافت می‌شود.
• گزینه Resize Table ردیف‌ها یا ستون‌هایی را به جدول اضافه یا از آن کم می‌کند تا محدوده جدول را مشخص نماید. این گزینه در گروه گزینه‌های Properties یافت می‌شود.
• گزینه Remove Duplicates ردیف‌هایی را که حاوی اطلاعات تکراری هستند را حذف می‌کند. مثلاً انتخاب تنها ستون Name در کادر محاوره‌ای Remove Dupilcates به ما اطمینان می‌دهد که یک نام بیش از یک‌بار ظاهر نخواهد شد. انتخاب ستون‌های Names و Units به ما اطمینان می‌دهد که نام و یک محصول دو بار تکرار نخواهد شد و به همین روال. این گزینه در گروه گزینه‌های Tools یافت می‌شود.
• گزینه Convert to Range محدوده جدول را به سلول‌هایی معمولی تبدیل کرده و ساختار جدولی آن را از بین می‌برد. این گزینه در گروه گزینه‌های Tools یافت می‌شود.
• چنانچه گزینه Header Row انتخاب شود، ردیف سرستون‌ها را نشان می‌دهد. اگر این گزینه انتخاب نشود، ردیف سرستون‌ها نمایش داده نمی‌شود. این گزینه در گروه گزینه‌های Table Style Options یافت می‌شود.
• گزینه Total Row در این فصل در قسمت‌های بعدی توضیح داده خواهد شد.
• چنانچه گزینه First Column انتخاب شود، قالب‌بندی خاصی را به اولین ستون جدول اعمال خواهد کرد.
• چنانچه گزینه Last Column انتخاب شود، قالب‌بندی خاصی به آخرین ستون از جدول اعمال می‌شود.
• چنانچه گزینه Banded Rows انتخاب شود، به ردیف‌های زوج جدول قالب‌بندی‌های متفاوتی با ردیف‌های فرد اعمال خواهد شد.
• چنانچه گزینه Banded Columns انتخاب شود، به ستون‌های فرد در جدول قالب‌بندی‌های متفاوتی با ستون‌های زوج اعمال خواهد شد.
• در قسمت Table Styles می‌توان از هریک از قالب‌بندی‌های جداول نشان‌داده‌شده در گروه، قالب‌بندی موردنظر را انتخاب کرد. البته اگر جدول گسترش پیدا کرده و یا کوچک‌تر شود، قالب‌بندی موردنظر نیز خود را به شکل مناسب با آن وفق خواهد داد.

در کاربرگی قیمت گاز طبیعی چندین سال را وارد نموده و نمودار خطی بسیار مناسبی برای نمایش تغییرات قیمت ماهیانه ایجاد کرده‌ایم. آیا می‌توان همه چیز را به‌گونه‌ای تنظیم کرد تا وقتی داده‌های قیمت‌های جدید گاز وارد می‌شود، نمودار به شکل خودکار به‌روز شود؟

در فایلی به نام Gasprice507.xlsx کاربرگ Original حاوی قیمت‌های گاز طبیعی در هر هزار فوت از ژوئیه سال 2002 تا دسامبر سال 2004 است. (تصویر 7-26 را ببینید) همان‌طور که پیش‌ازاین توضیح دادیم، می‌توانید محدوده B5:C34 (حاوی ماه‌ها و قیمت‌ها) را انتخاب کرده و کلیدهای Ctrl+T را فشار دهید تا جدولی برای این محدوده ایجاد نمایید. سپس می‌توانید با کلیک بر روی گزینه Insert Line Or Area Chart در گروه گزینه‌های Chart در تب Insert، نمودار خطی برای نمایش این داده‌ها ایجاد کرده و نوع چهارم این نمودارهای خطی یعنی Line With Markers را انتخاب کنید. نمودار خطی ای که انتخاب کرده‌ایم در تصویر8-26 نمایش‌داده‌شده است.

تصویر 7-26 داده‌های قیمت گاز در سال‌های: 2002 تا 2004

تصویر 8-26 نمودار خطی داده‌های قیمت گاز از سال 2002 تا 2004

سپس این کاربرگ را (با راست کلیک روی نام آن، کلیک بر گزینه Move Or Copy Sheet و سپس کلیک بر گزینه Make a Copy) کپی کرده و قیمت گاز در طول ماه ژوئیه سال 2006 را به آن اضافه می‌کنیم. (داده‌های جدید اکنون به‌ردیف 53 درج می‌شوند) این کاربرگ جدید را Newdata نام‌گذاری می‌کنیم. توجه کنید که نمودار خطی این کاربرگ به شکل خودکار به‌روز می‌شود تا شامل داده‌های جدید هم بشود. (تصویر 9-26 را ببینید)

تصویر 9-26 نمودار قیمت گاز که اکنون داده‌های سال‌های 2002 تا 2006 را نمایش می‌دهد.

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

برای هر معامله فروش، مواردی چون نام فروشنده، تاریخ فروش، نام محصول، مکان فروش و حجم معامله را داریم. آیا می‌توان به‌سادگی مثلاً میزان کل فروش رژ لب در محدوده شرق توسط اشلی یا هالاگان را به شکلی موجز نمایش داد؟

فایلی به نام Tablemakeuptemp.xlsx حاوی معاملات فروش است. (تصویر 10-26) اطلاعات زیر در هریک از معاملات موجود هستند: شماره معامله، نام فروشنده، تاریخ، نام محصول، تعداد واحدهای فروخته شده، میزان دلار کسب شده و مکان فروش. اگر این داده‌ها را به شکل جدولی قالب‌بندی کنیم، می‌توانیم یک ردیف جمع کل برای ستون واحدها و دلارها ایجاد نموده و بعد از پیکان‌های فیلتر کننده استفاده کرده تا کاری کنیم که این ردیف کل شامل مجموعه‌ای از معاملات دلخواهمان باشد. برای شروع روی هرجایی که داده وجود دارد کلیک کرده و با فشردن کلیدهای Ctrl+T جدولی ایجاد می‌کنیم. کادر محاوره‌ای Create Table محدوده E3:K1894 را انتخاب کرده است. روی دکمه Ok کلیک می‌کنیم.

توجه کنید چنانچه جدول را پیمایش کنید، سرعنوان آن همچنان قابل‌مشاهده خواهد بود. درحالی‌که سلولی را در جدول انتخاب کرده‌ایم، گزینه Total Row را در گروه گزینه‌های Table Style Options در تب Design را انتخاب می‌کنیم. اکسل به شکل پیش‌فرض تعداد کل ردیف‌های جدول را در سلول K1895 وارد می‌کند که می‌توانید آن را پاک کنید. حالا بر سلول I1895 (ستون واحدهای فروش رفته) و J1895 (ستون دلارها) کلیک می‌کنیم، سپس بر پیکان سمت راست هر یک از سلول‌ها کلیک کرده و بعد گزینه Sum را انتخاب می‌کنیم. این کار باعث می‌شود تمامی ارقام ستون‌های I و J جدول جمع زده شوند؛ بنابراین در حال حاضر سود کل برابر است با 239.912.67 دلار وتعداد 17.707 واحد فروخته شده‌اند. (تصویر 11-26 و فایلی به نامTablemakeuptotals.xlsx را مشاهده کنید)

تصویر 10-26 داده‌های فروش لوازم‌ آرایش

جدول 11-26 جمع کل سود و واحدهای فروخته شده

برای اینکه جمع کل تنها نمایش‌دهنده فروش رژ لب در محدوده شرق توسط اشلی یا هالاگان باشد، روی پیکان در سلول F3 (در سمت راست سرستون Name) کلیک می‌کنیم. گزینه Select All را از حالت انتخاب خارج می‌کنیم تا هیچ نامی انتخاب نشود. آنگاه گزینه‌های Ashley و Hallagan را انتخاب کرده (تصویر 12-26 را ببینید) و روی Ok کلیک کنید. پس از آن روی پیکان سرستون Product کلیک کرده و گزینه Lipstick را انتخاب کنید، سپس روی پیکان سرستون Location کلیک کرده و گزینه East را انتخاب کنید.

از همین روش برای نمایش تنها رژلب در ستون Product و نمایش تنها محدوده شرق در ستون Location استفاده کنید. حالا می‌بینید که تمامی داده‌ها با معیارهای فیلتر موردنظر در تصویر 13-26 و فایلی به نام Tablemakeuptotals.xlsx در پوشه Program Files مرتب و نمایش‌داده‌شده‌اند می‌بینید که اشلی و هالاگان 564 واحد از رژلب در محدوده شرق به مبلغ کل 1.716.56 دلار به فروش رسانده‌اند. این ویژگی فیلترکردن جداول کار محاسبه جمع کل هریک از مجموعه ردیف‌ها در کاربرگ‌های اکسل را به یک‌چشم به‌هم‌زدن انجام می‌دهد. اگر تمایل داشته باشید می‌توانید ردیف‌های شامل فروش‌های رژلب توسط اشلی و هالاگان را در محدوده شرق کپی کرده و آنها را در جای دیگری قرار دهید.

تصویر 12-26 فیلترکردن نام‌ها در جدول

تصویر 13-26 فیلترکردن جمع کل گزینشی برای واحدها و سودها

در بخش دوم به ادامه آموزش Table Slicers می پردازیم.