ایجاد و رسم جدول در اکسل — به زبان ساده + فیلم آموزشی رایگان

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

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

جدول در اکسل و کاربردهای آن

به منظور مدیریت و تحلیل دسته‌ای از اطلاعات به هم مرتبط، جدول در اکسل بسیار کارا عمل می‌کند. در حقیقت ساختار جدول در اکسل به منظور بررسی اطلاعاتی ایجاد شده که به صورت یک بانک اطلاعاتی (Database) هستند.

فیلم آموزش رسم نمودار در اکسل – طراحی انواع نمودار و تنظیمات در فرادرس

کلیک کنید

نکته: «جدول در اکسل» (Excel Tables) را نباید با «جدول داده» (Data Table) که به عنوان بخشی از تحلیل‌های «اگر-آنگاه» (What-if) عمل می‌کند، اشتباه گرفت.

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

یکی از ویژگی‌های مهم برای جدول در اکسل نام‌گذاری سطرها و ستون‌ها است. به این ترتیب فرمول‌های ایجاد شده برای جدول، خواناتر بوده و منظور از محاسبات بهتر درک می‌شود.

اجزا و عناصر یک جدول

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

• سطر عنوان (Header Row): به طور پیش‌فرض، جدول در اکسل شامل سطر عنوان است. منظور از سطر عنوان، اولین سطری از جدول اطلاعاتی که شامل اسامی ستون‌ها است و مقدارها در آن ثبت نشده است. به طور معمول، هنگام ایجاد یک جدول در اکسل ستون‌های این سطر به همراه دکمه فیلتر (Filter) و مرتب‌سازی (Sort) ظاهر می‌شوند. البته این امکان نیز وجود دارد که سطر عنوان را در جدول، فعال یا غیرفعال کرد.

• سطرهای متمایز (Banded Rows): متمایز ساختن سطرهای جدول به خوانایی جدول و تشخیص سطرها کمک بسیاری زیادی می‌کند. بخصوص بهتر است برای جدول‌های طولانی، اطلاعات مرتبط با یکدیگر، هنگام دنبال کردن یک سطر، گم شوند. تمایز رنگ سطرها از پدید آمدن چنین مشکلی، جلوگیری می‌کنند. تصویر زیر نمونه‌ای از سطرهای متمایز شده جدول در اکسل را نشان می‌دهد. البته باز هم می‌توان ایجاد چنین حالتی را برای نمایش جدول، فعال یا غیرفعال کرد.

• ستون‌های محاسباتی (Calculated Columns): با ورود یک تابع یا فرمول در ستون جدول در اکسل (مثلا جمع یا تابع SUM) می‌توانید یک ستون محاسباتی ایجاد کنید. به محض انجام این کار برای مثلا سلول دوم ستون آخر جدول، در بقیه سطرهای این ستون نیز همان فرمول یا محاسبه کپی شده و محاسبات صورت می‌گیرد. تصویر ۴، نمونه‌ای از یک ستون محاسباتی را نشان می‌دهد.

نکته: برای ایجاد ستون محاسباتی احتیاجی نیست که یک ستون جدید به جدول در اکسل اضافه کنید، فقط با درج فرمول یا تابع در روبروی سطرهای جدول در اکسل به طور خودکار ستون محاسباتی به جدول اضافه می‌شود.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

همانطور که در تصویر ۴، مشاهده می‌کنید، اسامی ستون‌های جدول در تابع sum به صورت زیر نوشته شده است.

=sum(table1@[Qtr1]:{Qtr2]] )

• سطر جمع کل (Total Row): به طور معمول، این سطر آخرین سطر جدول در اکسل را تشکیل می‌دهد و به کمک یک تابع یا فرمول، جمع‌بندی روی سطرهای بالایی بر هر ستون صورت می‌گیرد. فرمولی یا تابعی که برای هر یک از ستون‌های این سطر به کار می‌رود، ممکن است متفاوت باشد. اکسل اجازه می‌دهد نوع محاسبه را از بین توابع پرکاربرد یا تابع دلخواه خود انتخاب کنید. کافی است روی یکی از ستون‌های این سطر، دکمه جمع خودکار (Autosum) را با علامت $$\Sigma$$ در برگه Home انتخاب کنید. تصویر زیر به معرفی این سطر پرداخته است.

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

=SUBTOTAL(109, [Midwest])

منظور از کد ۱۰۹ در این تابع، همان جمع (sum) است. بهتر است برای اطلاع از کدهای توابع محاسبه شده در SUBTOTAL به مطلب تابع جمع بندی AGGREGATE در اکسل — راهنمای کاربردی مراجعه کنید.

ایجاد یک جدول در اکسل

گام‌های زیر به منظور ایجاد یک جدول در اکسل لازم است. با طی کردن این گام‌ها می‌توانید به سرعت یک جدول ایجاد کنید.

1. اطلاعات جدول را با در نظر گرفتن اسامی ستون‌ها و سطرها در یک ناحیه پیوسته از کاربرگ، وارد کنید.
2. ناحیه اطلاعاتی مورد نظر را انتخاب کنید.
3. از برگه Home فهرست Format as Table را در بخش styles اجرا کنید.
4. از بین الگو‌های معرفی شده، یکی را انتخاب کنید.
5. در پنجره Format as Table، ناحیه اطلاعاتی را تایید کرده و در صورت لزوم گزینه My table has header را فعال کنید تا اکسل سطر اول جدول اطلاعاتی را به صورت سطر عنوان، در نظر بگیرد.
6. در بخش Table Tools و گزینه Design که یک برگه جدید است، می‌توانید خصوصیات اصلی جدول اکسل مانند نام جدول را تغییر دهید.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

در تصویر ۶، نمونه‌ای از پنجره Format as Table را مشاهده می‌کنید.

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

جدول در اکسل امکاناتی را در اختیار کاربر قرار می‌دهد که کارایی انجام محاسبات و عملیات را بالا می‌برد. برای مثال می‌توان به مواردی که در زیر فهرست شده‌اند، اشاره کرد:

• استفاده از آدرس مراجع ساخت‌یافته: همانطور که در فرمول‌های نوشته شده در قسمت قبل مشاهده کردید، ارجاع به سلول‌های پیش‌نیاز براساس آدرس آن‌ها صورت نمی‌گیرد. هنگامی که جدول را روی ناحیه اطلاعاتی اعمال می‌کنید، سطرها و ستون‌های معرفی شده در جدول، براساس اسامی ستون و سطرها، نام‌گذاری شده و از این نام‌ها برای ارجاع به سلول‌های پیش‌نیاز یا ناحیه‌ای از کاربرگ، استفاده می‌شود.
• اطمینان نسبت به یکپارچگی اطلاعات: از آنجایی که ساختار جدول در اکسل به مشابه یک پایگاه داده عمل می‌کند، یکپارچگی در مقادیر آن رخ می‌دهد. برای مثال می‌توانید به کمک «اعتبارسنجی داده‌ها» (Data Validation) مقادیر یک ستون از جدول در اکسل را محدود کرده و کاربر را وادار کنید که در محدوده مورد نظرتان، مقادیر را وارد کند.

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

فرض کنید قرار است به اطلاعات جدول در اکسل سطرها یا ستون‌‌هایی اضافه کنید. این کار بسیار ساده صورت می‌گیرد. کافی است به گوشه پایین جدول توجه کنید یک دکمه با علامت $$\bf \lrcorner$$ در سلول انتهایی جدول ظاهر شده است. با تکنیک کشیدن و رها کردن می‌توانید تعداد سطرها و ستون‌های جدول در اکسل را افزایش دهید.

فیلم آموزش جداول محوری در اکسل یا پیوت تیبل Pivot Tables در فرادرس

کلیک کنید

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

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

نکته: توجه داشته باشید که جدول در اکسل را برای کارپوشه‌هایی که بین دیگر کاربران به صورت اشتراکی استفاده می‌شوند، نمی‌توان ایجاد کرد.

هر گاه لازم باشد تغییراتی روی جدول در اکسل ایجاد کنید، بهتر است از طریق برگه ابزارهای جدول (Table Tools‌)‌ و گزینه طراحی (Design) اقدام کنید. این برگه زمانی که جدول ایجاد شده و یکی از سلول‌های آن فعال باشد، قابل مشاهده است. دستورات مربوط به این برگه در تصویر ۹ دیده می‌شود.

برای مثال اگر می‌خواهید جدول را به صورت یا شکل یک ناحیه از کاربرگ تبدیل کنید دستور Convert to range را اجرا کرده تا جدول به صورت یک ناحیه در‌آید. واضح است که در این صورت نمی‌توانید از امکاناتی که جدول برایتان ایجاد کرده بود، استفاده کنید. البته فرمول‌ها و رنگ‌آمیزی سلول‌ها باقی خواهند ماند ولی علامت فیلتر و ویژگی‌های اصلی جدول از بین خواهند رفت. همچنین نام‌گذاری‌های مربوط به سطر و ستون‌ها نیز به آدرس عادی اکسل (A1) باز خواهد گشت.

خلاصه و جمع‌بندی

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