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

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

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

ردیف سرستون‌ها

بالاترین ردیف یک جدول (Header) عنوان هر ستون را شامل می‌شود. در این ردیف تنها می‌توان مقادیر متنی وارد کرد و وارد کردن فرمول مجاز نیست.

داده‌ها

تمامی سلول‌های حاوی داده و فرمول، سلول‌های داده هستند.

ردیف‌ها

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

ستون‌ها

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

ردیف کل

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

نحوه ایجاد جدول در اکسل

برای کشیدن جدول در اکسل، پس از وارد کردن داده‌های مد نظر خود در اکسل، به دلخواه روی یکی از سلول‌های صفحه کلیک کنید و سپس در سربرگ «Insert» روی گزینه «Table» بزنید.

به جای کلیک بر گزینه Table در نوار ابزار اکسل، می‌توانید میانبر صفحه کلید Ctrl+T

  را بزنید.

اکسل به طور خودکار و هوشمند داده‌هایی که در فایل خود وارد کرده‌اید، جدولی را استخراج می‌کند و با خط‌چین آن‌ها را نشان می‌دهد. اگر داده‌ها به اشتباه انتخاب شدند، می‌توانید به طور دستی در پنجره «Create Table» داده‌های دلخواه خود را انتخاب کنید.

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

اگر در داده‌های انتخابی ردیف اول شامل عنوان هر ستون است در جدول کریت Table، تیک گزینه «My table has headers» و سپس OK را بزنید.

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

اگر تیک گزینه My table has headers را نزنید، اکسل یک ردیف به عنوان سرستون به جدول شما اضافه می‌کند.

بعد از ایجاد جدول، اگر روی هر یک از سلول‌های جدول بزنید، سربرگ Table Design در نوار ابزار اکسل نمایان می‌شود.

این سربرگ شامل تمامی ابزارهایی است که برای شخصی‌سازی و Design جدول خود به آن‌ها نیاز دارید. اگر روی هر سلولی به جز سلول‌های جدول کلیک کنید، این سربرگ ناپدید می‌شود.

مراحل رسم جدول در اکسل را شرح دادیم، در ادامه به توضیح تنظیمات جدول در اکسل می‌پردازیم.

مخفی کردن ردیف سرستون

برای مخفی کردن عناوین ستون‌ها تیک هدررا را بردارید خاموش کردن این گزینه منوی فیلتر در سرستون را نیز خاموش می‌کند.

همانطور که در تصویر زیر می‌بینیدِ، سرستون‌های جدول مخفی شده اند.

نمایش ردیف کل

به طور پیش‌فرض ردیف کل در انتهای جدول نمایش داده نمی‌شود. با زدن تیک گزینه «Total Row» را این ردیف در جدول شما نمایش داده خواهد شد.

وقتی برای اولین بار ردیف کل را در جدول خود وارد کنید، کلمه Total در اولین ستون جدول در این ردیف نوشته می‌شود. بسته به تشخیص هوشمند اکسل در آخرین ستون جدول، تعداد یا جمع ستون نمایش داده می‌شود.

هر یک از سلول‌های ردیف کل، شامل منویی از انواع محاسبات تجمعی هستند. انتخاب هر یک از این گزینه‌ها روی داده‌های آن ستون محاسبات مربوط را انجام می‌دهد. تمامی این محاسبات بر اساس تابع Subtotal عمل می‌کنند، از این تابع، توابع مختلفی ستخراج می‌شود. برای مثال =SUBTOTAL(109,[Total])

  مجموع ستون Total را حساب می‌کند. آرگمان اول تابع ( function_num

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

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

مسیر یادگیری اکسل با فرادرس

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

اگر مایلید تمامی فیلم‌های آموزشی اکسل فرادرس را یکجا مشاهده کنید، روی لینک زیر کلیک کنید:

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

اگر در جدول شما ستون اول یا آخر از اهمیت خاصی برخوردار است، می‌توانید با زدن تیک گزینه «First Column» برای ستون اول و گزینه «Last Column» برای ستون آخر، هر یک از این دو ستون را به دلخواه برجسته کنید.

نتیجه برجسته شدن این ستون‌ها را در تصویر زیر مشاهده می‌کنید.

رنگی کردن یکی در میان سطر یا ستون جدول

باندگذاری یا تغییر رنگ سطر و ستون‌های یک جدول به صورت یکی در میان می‌تواند انتقال داده و خواندن جدول را آسان‌تر کند. در اکسل به طور پیش‌فرض تمامی جدول‌ها در ردیف‌ها باندگذاری می‌شوند، اما می‌توانید در تنظیمات جدول در اکسل، باندگذاری را به دلخواه تغییر دهید.

برای مثال در تصویر بالا باندگذاری به شرح زیر تنظیم شده است:

1. بدون باندگذاری
2. باندگذاری ردیف‌ها
3. باندگذاری ستون‌ها
4. باندگذاری ردیف‌ها و ستون‌ها

برای تنظیم هر یک از حالات بالا در بخش «Table Style Options» از دو گزینه «Banded Rows» برای بندگذاری ردیف‌ها و «Banded Columns» رای باندگذاری ستون‌ها استفاده کنید.

گزینه فیلتر در سرستون‌ها

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

تنظیمات موجود در این منوی کشویی را بر اساس نامگذاری تصویر بالا در زیر شرح داده‌ایم:

1. Sort A to Z: مرتب کردن داده‌های ستون به صورت صعودی از بالا به پایین
2. Sort Z to A: مرتب کردن محتوای ستون به صورت نزولی از بالا به پایین
3. Sort by Color: مرتب کردن بر اساس رنگ سلول
4. Text Filter: اعمال فیلتر وایلد کارت 
5. در نظر نگرفتن مقادیر دلخواه ز داده‌های موجود در ستون برای فیلتر کردن

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

گاهی اوقات منوی کشویی فیلتر در سرستون‌ها اضافه است و قصد دارید برای مرتب‌تر شدن جدول نمایش این منو را خاموش کنید. برای این کار تیک گزینه «Filter Button» را در بخش Table Style Options خاموش کنید. برای این کار می‌توانید از میانبر صفحه کلید Ctrl + Shift + L

  نیز استفاده کنید.

نامگذاری جدول

نام جدول به طور پیش فرض Table در نظر گرفته می‌شود. در صورت وجود چند جدول در فایل، به صورت Table2، Table1 و غیر از هم تمیز داده می‌شوند. برای تغییر نام جدول ورودی «Table Name» را در بخش تنظیمات جدول در اکسل در سربرگ Table Design ویرایش کنید.

اگر قصد دارید نام بیش از یک جدول را در فایل خود تغییر دهید، در سربرگ «Formula» روی گزینه «Name Manager» بزنید.

پنجره‌ای باز می‌شود که المان‌های موجود در فایل شما را نشان می‌دهد. برای نمایش جدول‌ها روی گزینه «Filter» بزنید و «Table Names» را انتخاب کنید. با این کار تمامی جدول‌هایی که در فایل خود دارید نشان داده می‌شود. سپس جدول دلخواه خود را از داخل لیست انتخاب کنید و روی گزینه «Edit» بزنید تا بتوانید نام آن را اصلاح کنید.

بعد از انجام تغییرات دلخواه در پنجره «Edit Name» روی OK بزنید تا تغییرات اعمال شود.

قوانین نامگذاری جدول‌ها

حین نامگذاری جدول نکات زیر را در نظر داشته باشید«

• من باید با یک حرف _

    یا

    شروع شود.

• نام هر جدول باید با جدول دیگر متفاوت باشد.
• بین ۱ تا ۲۵۵ کاراکتر داشته باشد.
• تنها شامل حروف اعداد، _

  یا نقطه باشد.

• شامل فاصله یا علائم خاص نباشد.
• آدرس هیچ سلولی نباشد.
• C و c یا R و r نامیده نشود.

دقت کنید اکسل بین حروف بزرگ و کوچک انگلیسی برای نامگذاری تفاوتی قائل نیست، لذا دو نام «table1» و «Table1» از نظر اکسل با یکدیگر فرقی ندارند.

نامگذاری ستون‌ها

برای نامگذاری ستون‌ها قوانین سختگیرانه بالا وجود ندارد و تنها کافیست نامی بین ۱ تا ۲۵۵ کاراکتر انتخاب کنید.

انتخاب یک جدول با استفاده از نام آن

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

تغییر سایز جدول

جدول را انتخاب کنید و در سربرگ Table Design روی گزینه «Resize Table» بزنید.

پنجره Resize Table باز می‌شود که در این پنجره می‌توانید به صورت دستی تعداد سلول‌های موجود در جدول را به دلخواه تغییر دهید یا با استفاده از نشانگر روی صفحه، بازه جدید سلول‌ها را انتخاب کنید. برای استفاده از نشانگر باید روی دکمه کنار نوار موجود در این پنجره بزنید.

روش ساده‌تری نیز برای تغییر سایز جدول وجود دارد. تمامی جدول‌ها در اکسل در گوشه سمت راست پایین علامت کوچکی دارند که با کلیک و کشیدن ین علامت می‌توان سایز جدول را به اندازه دلخواه تغییر داد.

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

اضافه شدن هوشمند سطر یا ستون

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

قابلیت‌های خودکار و هوشمند اکسل علی‌رغم سرعتی که می‌توانند به کار ما ببخشند، گهگاه استفاده از اکسل را پیچیده و آزاردهنده می‌کنند. برای خاموش کردن قابلیت تکرار خودکار فرمول در ستون به سربرگ «File» بروید و روی «Options» بزنید تا پنجره مربوط باز شود. در این پنجره در منوی سمت چپ روی «Proofing» بزنید و سپس با زدن روی دکمه «AutoCorrect Options» پنجره «AutoCorrect» را باز کنید.

در این پنجره تیک «Include new rows and columns in table» را بردارید تا قابلیت افزودن خودکار سطح و ستون به جدول غیرفعال شود.

اضافه کردن سطر و ستون

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

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

دقت کنید جهت صفحه در اکسل به طور پیش فرض از چپ به راست است، در این تنظیم آخرین ستون جدول در واقع اولین ستون از سمت راست خواهد بود. اگر در سربرگ «Page Layout» در بخش «Sheet Options» جهت صفحه را از راست به چپ کرده باشید آخرین ستون جدول اولین ستون از سمت چپ خواهد بود.

برای مثال برای اضافه کردن یک ستون در میان جدول، روی یکی از سلول‌هایی که در سمت راست این ستون قرار دارند کلیک راست کنید و در منوی باز شده روی Insert بزنید. سپس گزینه «Table Columns to the Left» را انتخاب کنید.

اگر قصد دارید بین ردیف دوم و سوم جدول سه ردیف اضافه کنید، سه سلول از ردیف های سوم چهارم و پنجم انتخاب کنید، سپس روی سلول‌های انتخاب شده راست کلیک کنید و پس از انتخاب Insert روی «Table Rows Above» بزنید.

دقت کنید امکان اضافه کردن همزمان چندین ستون یا ردیف به انتهای جدول وجود ندارد و باید تک به تک اضافه شوند.

حذف سطر یا ستون

مشابه اضافه کردن سطر و ستون که در بخش قبل آن را توضیح دادیم با راست کلیک روی هر سلول نتخاب گزینه «Delete» می‌توان سطر یا ستونی را که سلول انتخاب شده به آن‌ها تعلق دارد، از جدول حذف کرد.

انتخاب یک ستون

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

همینطور اگر یکی از سلول‌های جدول انتخاب شده باشد با میانبر Ctrl + Space

  روی صفحه کلید ستون مربوط به آن سلول به طور کامل انتخاب می‌شود.

انتخاب یک سطر

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

همینطور اگر یکی از سلول‌های جدول انتخاب شده باشد با میانبر Shift + Space

  روی صفحه کلید ردیف مربوط به آن سلول به طور کامل انتخاب می‌شود.

انتخاب داده‌های یک جدول

برای انتخاب سلول‌هی داده در جدول، مشابه دو روش قبل با حرکت دادن نشانگر روی گوشه سمت چپ بالای اولین سلول در سمت چپ بالای جدول، نشانگر به یک فلش رو به راست پایین تغییر شکل می‌دهد. در این حالت با کلیک چپ کل سلول‌های حاوی داده در جدول انتخاب می‌شوند.

این کار با میانبر صفحه کلید Ctrl + Shift + Space

  یا Ctrl + A

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

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

جابجا کردن جدول در اکسل

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

خلاصه سازی با جدول محوری

برای خلاصه سازی جدول با جدول محوری، یکی از سلول‌های جدول را انتخاب کنید و در بخش تنظیمات جدول در اکسل در سربرگ Table Design، روی «Summarize with PivotTable» بزنید تا یک جدول محوری اضافه شود. این گزینه دقیقاً مثل گزینه «PivotTable» در سربرگ Insert عمل می‌کند و هیچ تفاوتی ندارد. در مجله فرادرس به معرفی کامل جداول محوری در اکسل پرداخته‌ایم برای اطلاعات بیشتر می‌توانید به این مطلب مراجعه کنید.

حذف مقادیر تکراری در جدول

یکی از سلول‌های جدول را انتخاب کنید و در سربرگ Table Design روی «Remove Duplicates» بزنید تا پنجره مربوط باز شود در این پنجره می‌توانید مقادیر تکراری را حذف کنید. این گزینه دقیقاً مثل «Remove Duplicates» در سربرگ «Data» عمل می‌کند و هیچ تفاوتی ندارد. پیش‌تر به طور کامل راجع به «حذف مقادیر تکراری در اکسل» توضیح داده‌ایم برای اطلاعات بیشتر می‌توانید مطلب مربوط را مطالعه کنید. همین طور فرادرس فیلم آموزشی رایگانی برای آموزش حذف سلول‌های خالی اکسل تهیه کرده است.

خارج کردن داده ها از فرمت جدول در اکسل

اگر مایل نیستید داده‌های شما در یک جدول در اکسل تعریف شوند و تنظیمات جدول در اکسل برای شما کارآمد نیست، می‌توانید محتوای جدول را به داده‌های معمولی اکسل تبدیل کنید. برای این کار پس از انتخاب کردن یکی از سلول‌های جدول، در سربرگ Table Design گزینه «Convert to Range» را انتخاب کنید.

با این کار پنجره‌ای باز می‌شود. در این پنجره Yes را انتخاب کنید.

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

اگر مایلید فرمت ظاهری جدول نیز از بین برود و به فرمت پیش فرض اکسل بازگردد، تمامی سلول‌های مد نظر را انتخاب کنید و در سربرگ Home در «Clear» روی «Clear Formats» بزنید.

با این کار فرمت ظاهری داده‌ها نیز به حالت پیش فرض اکسل تغییر پیدا می‌کند.

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

برش دهنده‌ها (Slicer) کنترل کننده‌های تعاملی هستند که برای فیلتر کردن محتوای جدول استفاده می‌شوند. استفاده از برش دهنده‌ها از فیلترهای موجود در سرستون‌ها آسان‌تر است چرا که به طور ثابت در صفحه باقی می‌مانند. برای وارد کردن برش دهنده در تنظیمات جدول در اکسل در سربرگ Table Design گزینه «Insert Slicer» را انتخاب کنید.

در پنجره باز شده تیک عنوان ستونی را بزنید که قصد دارید برای آن برش دهنده اضافه کنید.

برش دهنده در صفحه ظاهر می‌شود و به شما امکان فیلتر محتوای ستون را می‌دهد.

شخصی‌سازی سبک جدول

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

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

گزینه دیگر برای تغییر سبک جدول گزینه «Format as Table» در سربرگ «Home» است. با وجود اینکه سبک‌ها با نام متفاوتی ارائه شده‌اند، اما محتوای گالری دقیقاً یکیست.

ایجاد سبک دلخواه

اگر سبک‌های موجود در گالری اکسل برای جدول رای شما کافی نیست می‌توانید سبک دلخواه خود را بسته به نیاز ایجاد کنید برای این کار پس از باز کردن کامل گالری سبک‌ها در تنظیمات جدول در اکسل، روی گزینه «New Table Style» بزنید. پنجره باز می‌شود که شامل المان‌هایی است که می‌توانید آن‌ها را به دلخواه شخصی‌سازی کنید.

با انتخاب المان‌هایی که در نام آن‌ها عبارت «Stripe» ذکر شده است، به طور پیش فرض به طور یکی در میان در سطر یا ردیف اعمال می‌شوند. با کلیک روی این المان‌ها نمودار کشویی در سمت راست جدول باز می‌شود که می‌توان تنظیم آن را دو تا در میان، سه تا در میان و غیر کرد. این عدد به طور پیش فرض روی یک تنظیم شده است.با انتخاب هر یک از المان‌ها  زدن روی دکمه فرمت پنجره‌ای باز می‌شود که می‌توانید در آن ویژگی‌های آن المان را به دلخواه تعیین کنید.

در این پنجره امکان تغییر رنگ فونت سایز و غیره را خواهید داشت.

در تصویر زیر پیش نمایش جدول با اعمال زمینه (Fill) سبز و کادر (Border)، با تنظیمات مختلف نمایش داده شده است.

ارجاع ساختار یافته

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

در حالت عادی اگر داده‌ها در شبه‌جدول وارد شده باشند، برای آدرس‌دهی سلول‌ها و ستون‌های جدول باید از آدرس‌دهی معمول اکسل در صفحه استفاده کرد برای مثال در تصویر زیر برای آدرس‌دهی ستون سوم جدول عبارت =D3:D12

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

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

اما اگر در این مثال، داده‌ها در یک جدول واقعی تعریف شوند می‌توان است عبارت =tblSales[Rep]

  برای ارجاع‌دهی استفاده کرد. در این عبارت بخش «Sales» نام جدول است.

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

ارجاع به ردیف انتخاب شده

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

=table_name[@]
=tblSales[@]

ارجاع به ردیف انتخاب شده از یک ستون

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

=table_name[@column_name]
=tblSales[@OrderDate]

ارجاع ردیف انتخاب شده از چندین ستون

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

=table_name[@[from_column_name]:[to_column_name]]
=tblSales[@[OrderDate]:[Region]]

ارجاع به داده‌های جدول

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

=table_name
=tblSales

کد دوم در زیر آورده شده است.

=table_name[#Data]
=tblSales[#Data]

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

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

=table_name[[#Headers],[#Data]]
=tblSales[[#Headers],[#Data]]

ارجاع به داده‌های جدول و ردیف کل

برای ارجاع به داده‌های جدول و ردیف کل، خط اول کد زیر را وارد کنید و نام جدول خود را مشابه خط دوم کد در فرمول اصلاح کنید.

=table_name[[#Data],[#Totals]]
=tblSales[[#Data],[#Totals]]

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

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

=table_name[#All]
=tblSales[#All]

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

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

=table_name[column_name]
=tblSales[OrderDate]

کد دوم در زیر آورده شده است.

=table_name[[#Data],[column_name]]
=tblSales[[#Data],[OrderDate]]

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

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

=table_name[[#Headers],[#Data],[column_name]]
=tblSales[[#Headers],[#Data],[OrderDate]]

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

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

=table_name[[#Data],[#Totals],[column_name]]
=tblSales[[#Data],[#Totals],[OrderDate]]

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

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

=table_name[[#All],[column_name]]
=tblSales[[#All],[OrderDate]]

ارجاع به چندین ستون جدول

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

=table_name[[from_column_name]:[to_column_name]]
=tblSales[[OrderDate]:[Region]]

ارجاع به ردیف سرستون‌ها

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

=table_name[#Headers]
=tblSales[#Headers]

ارجاع به یک سرستون مشخص

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

=table_name[[#Headers],[column_name]]
=tblSales[[#Headers],[OrderDate]]

ارجاع به سرستون چند ستون

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

=table_name[[#Headers],[from_column_name]:[to_column_name]]
=tblSales[[#Headers],[OrderDate]:[Region]]

ارجاع به ردیف کل

برای ارجاع به ردیف کل جدول، خط اول کد زیر را وارد کنید و نام جدول خود را مشابه خط دوم کد در فرمول اصلاح کنید.

=table_name[#Totals]
=tblSales[#Totals]

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

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

=table_name[[#Totals],[column_name]]
=tblSales[[#Totals],[OrderDate]]

ارجاع به ردیف کل چند ستون

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

=table_name[[#Totals],[from_column_name]:[to_column_name]]
=tblSales[[#Totals],[OrderDate]:[Region]]

مقایسه شبه جدول و جدول واقعی در اکسل

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

تصویر زیر نیز همان داده‌ها را در یک جدول واقعی ارائه کرده است:

در ادامه با روش‌های مختلف ارجاع عملیات مختلفی را روی این دو جدول روی ستون «Rep» انجام می‌دهیم. سه روش ارجاع شامل ارجاع مستقیم به آدرس سلول (Cell Method)، ارجاع مستقیم به آدرس بازه‌ای از سلول‌ها (Range Method) و ارجاع ساختار یافته (Structured Reference Method) است. همونطور که پیش‌تر ذکر شد ارجاع ساختار یافته تنها برای جدول معنی دارد.

حذف ردیف‌های ۶ تا ۹

در تصویر زیر باز ردیف ۶ تا ۹ را در ستون Rep شبیه جدول حذف کردیم. نتیجه حذف در پایین تصویر قابل مشاهده است.

در تصویر زیر از هر سه روش برای حذف ردیف ۶ تا ۹ ستون Rep استفاده کردیم نتیجه این کار را می‌توانید در پایین تصویر مشاهده کنید. همونطور که مشاهده می‌کنید، ارجاع به آدرس سلول پس از حذف دچار خطای #REF!

  شده است، اما روش‌های دیگر ارجاع‌دهی بدون خطا به‌روز‌رسانی شده‌اند.

وارد کردن چهار ردیف پایین ردیف پنجم

همانطور که مشاهده می‌کنید در شبه جدول ارجاع‌دهی با آدرس سلول بدون مشکل به‌روز‌رسانی شده است در حالی که ارجاع با آدرس‌ بازه‌ای از سلول‌ها، برای چهار ستون اضافه، داده صفر را وارد ستون می‌کند.

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

وارد کردن یک داده جدید به انتهای ستون

همونطور که در تصویر زیر مشاهده می‌کنید با وارد کردن یک داده جدید در پایین ستون Rep، هیچ کدام از دو روش اجرا متناسب با تغییر اعمال شده به‌روز‌رسانی نمی‌شوند.

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

نتیجه‌گیری

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

تکرار خودکار فرمول در ستون

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

برای خاموش کردن قابلیت تکرار خودکار فرمول در ستون به سربرگ File بروید و روی Options بزنید تا پنجره مربوط باز شود. در این پنجره در منوی سمت چپ روی Proofing بزنید و سپس با زدن روی دکمه AutoCorrect Options پنجره AutoCorrect را باز کنید.

در این پنجره تیک «Fill formulas in tables to create calculated columns» را بردارید تا قابلیت تکرار خودکار فرمول در ستون جدول خاموش شود.

ارجاع با قابلیت پر کردن خودکار

پر کردن خودکار یا «AutoComplete» ارجاع به ‌بخش‌های مختلف جدول را به مراتب آسان‌تر می‌کند. با استفاده از این قابلیت نیازی به تایپ دستی تمام فرمول نیست. به راحتی با وارد کردن علامت تصاویر و وارد کردن نام جدول تمامی پیشنهادهای ممکن برای شما لیست می‌شود. نام کامل ارجاع ساختار یافته نیز در این لیست وجود دارد.

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

بعد از یافتن عبارت مد نظر در لیست پیشنهاد‌ها، روی دکمه «Tab» روی صفحه کلید بزنید تا فرمول با عبارت انتخاب شده تکمیل شود.