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

فهرست مطالب این نوشته
997696

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

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

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

بالاترین ردیف یک جدول (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» روی صفحه کلید بزنید تا فرمول با عبارت انتخاب شده تکمیل شود.

اصلاح خودکار برای وارد کردن فرمول ارجاع ساختار یافتهجدول

برای مشاهده در ابعاد بزرگ‌تر، روی تصویر کلیک کنید.

source

توسط expressjs.ir