ویدئوی آموزش ساخت Table در اکسل

ما در «دوره آموزش اکسل کاربردی» فرســاران، به صورت مفصلی Table را درس می‌دهیم اما از آنجایی که برخی از فراگیران دوره آموزش پاورکوئری و آموزش اکسل پیشرفته فرساران با TABLE‌ آشنا نبودند، یک ویدئوی آموزشی به این مقاله اضافه شد که در آن نکات اصلی ساخت TABLE در اکسل گفته می شود.

البته نکات و ریزه کاری‌های TABLEهای اکسل بیش از آن چیزی است که در این ویدئو بیان می‌شود و هدف این ویدئو فقط نکات اصلی «ساخت» TABLEها است.


و این ابزار آنقدر اساسی و مهم است که تمامی فایلهایی که طراحی می‌کنم حتما بر اساس Table ها طراحی می شود و در دوره‌های فرساران به فراگیران می‌گویم:

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

بدون تردید یکی از کاربردهای اصلی Excel ، محلی باشید برای ذخیره داده‌‎‌ها .به اصطلاح علمی تر یعنی از Excel بعنوان Data Base استفاده می‌شود. Data Base ها قابلیت‌هایی دارند که Excel ذاتا فاقد آنها است . بعنوان مثال یک سطر در DB کاملا یکپارچه است و امکان ندارد که در هنگام Sort قسمتی آن جابجا شود و شاید بدانید که اگر در Excel یک ستون خالی در یک لیست داده ها داشته باشید هنگام Sort کردن ، حتما داده های شما بهم می ریزد.

هنگام فیلتر کردن در Excel نیز سطرهای خالی باعث ناپیوستگی می‌شوند. این گونه استفاده و محدودیت‌های Excel باعث بوجود آمدن ابزاری به نام List در Excel 2003 شد که بعلت قابلیت کمی که داشت اصلا استفاده نمی‌شد ( به همین دلیل جالب است بدانید که در VBA برای اشاره به Table از Object به نام ListObject استفاده می‌شود.) . در Excel 2007 مایکروسافت تصمیم گرفت که قسمتی از قابلیت DB ها را در Excel قرار دهد بنابراین Table را اضافه کرد.


Table چیست؟

ابزاری است که برای یکپارچه کردن و مدیریت داده‌های مرتبط بهم در Excel. 

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


مزایای TABLE کردن لیست در اکسل

توجه: این قسمت از مقاله با هوش مصنوعی تولید شده است و سپس ما آن‌ها را تکمیل‌تر کرده‌ایم تا برای شما واضح گردد:

مرتب‌سازی و فیلتر خودکار:

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

که البته این نکته مهمی در TABLE نیست و همیشه در اکسل با زدن کلید CTRL + SHIFT + L می‌توانیم فیلتر را فعال کنیم.

فرمول‌های دینامیک (Dynamic Formulas):

با استفاده از جداول، فرمول‌ها به صورت خودکار با تغییر اندازه جدول (افزایش یا کاهش داده‌ها) به‌روز می‌شوند. این موضوع باعث صرفه‌جویی در زمان و جلوگیری از اشتباهات ناشی از به‌روز نبودن فرمول‌ها می‌شود.
از اسامی ستون‌ها به جای مراجع سلول (A1, B1) استفاده می‌کند که خوانایی و درک فرمول‌ها را افزایش می‌دهد.

قابلیت توسعه‌پذیری خودکار:

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

قالب‌بندی خودکار و زیبا:

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

مرجع‌های ساختاریافته (Structured References):

یکی از ویژگی‌های برجسته جداول اکسل، استفاده از مرجع‌های ساختاریافته است. به‌جای استفاده از مراجع سلولی، شما می‌توانید از نام ستون‌ها در فرمول‌های خود استفاده کنید، که این امر باعث می‌شود فرمول‌ها خواناتر و کاربردی‌تر باشند.
قابلیت‌های خلاصه‌سازی (Summarization):

جداول به‌صورت خودکار توابعی مانند SUM, AVERAGE, COUNT و غیره را ارائه می‌دهند که می‌توانید به‌راحتی آن‌ها را به ستون‌ها اعمال کنید. این توابع به‌طور خودکار با اضافه یا حذف داده‌ها به‌روز می‌شوند.

یکپارچگی با Pivot Table:

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


حفظ تمامیت داده‌ها:

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


سرصفحه ثابت در هنگام اسکرول کردن:

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

قابلیت تبدیل سریع به نمودارهای اکسل:

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


و اما مهمترین خاصیت Table

یک Table یک Dynamic Range است.

یعنی یک محدوده‌ای که به شکل خودکار بزرگ یا کوچک می‌شود.

یکی از بزرگترین دغدغه‌های کاربران حرفه‌ای Excel ساختن chart و Pivot Table  ای است که هنگامیکه سطری به محدوده‌ی اطلاعات اضافه می‌شود، به شکل خودکار در Chart  یا نتیجه Pivot Table نیز اضافه شود. که اینکار، این مشکل، این خواسته با معجزه ، معجزه‌ای به نام Table به شکل 100% مرتفع شده است. در Excel 2003 کاربران با تکنیک‌هایی مانند Offset + CountA  این کار را با محدودیت‌هایی رفع می‌کردند اما در Excel 2007 کافیست که هنگام ساخت Chart یا Pivot محدوده آنرا قبلا به شکل Table در آورده باشید.


چگونه یک محدوده اکسل را TABLE کنیم؟

برای ساخت یک Table کافیست که در جایی در لیست خود کلیک کنید و سپس کلید CTRL+T  یا CTRL+L را بزنید البته از Insert-->Table هم می شود یک Table را ساخت.


محدودیت‌های Table

می‌توان گفت که Table هیچ محدودیتی ایجاد نمی کند اما بهتر است بدانید که در Table ها نمی توانید Merge کنید که البته این موضوع بسیار حائز اهمیت است و استفاده از Merge در لیست ها(فهرست) ها کاملا غیر مجاز است زیرا باعث می شود که بسیاری از فرمانها با اشکال مواجه شوند.

تنها گزینه ای که بعد از Table کردن غیر فعال می شود Share کردن یک فایل Excel است یعنی دیگر نمی توان چندین نفر به طور همزمان روی فایل کار کنند . توجه داشته باشید که این Share کردن با اینکه یک فایل را در شبکه برای استفاده دیگران قرار دهید کاملا متفاوت است. توجیه این موضوع می تواند این باشد که اگر شما به Table که همزمان توسط چندین نفر استفاده شود احتیاج دارید بروید سراغ Access که ذاتا این قابلیت را دارد. Table+Share=Access

چطور در یک فرمول، آدرس ستونی را در یک Table‌ مطلق کنیم؟

باید بگویم که به صورت کاملا اتفاقی و سعی و خطا این تکنیک را یافتم چون در آن زمان (اکسل ۲۰۰۷)‌ در سایت انگلیسی هم اشاره‌ای به این موضوع نشده بود.
مطلق و یا نسبی بودن آدرس ستون در یک فرمول بستگی به نوع کپی کردن آن دارد: 
اگر فرمول را با Copy و سپس Paste‌ کنید، تمامی آدرس‌ها مطلق خواهد بود. 
اگر فرمول را با استفاده از Fill Handle (همان مربع کوچکی که در پایین سلول است)، کپی کنیم، آدرس‌ها نسبی خواهند شد.

در تصویر زیر می‌توانید این کپی کردن را ببیند:

مطلق و نسبی کردن آدرس ستون در فرمول نویسی تیبل های اکسل

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

کافی است که فرمول زیر را بنویسیم و سپس آن را با Fill Handle‌ کپی کنیم:

=SUMIFS(Data[بهار], Data[[نام]:[نام]], "*ب*")

با دقت در فرمول قبل می‌توانید ببیند که اگر بخواهیم یک ستون از Table را در هنگام کپی کردن به صورت مطلق درآوریم باید آدرس را به صورت [ [name]:[col name] ]  بنویسیم.

منبع مقاله: وب سایت مایکروسافت

شما هم تجربه یا دیدگاه خود را بنویسید:

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

متن ساده

  • تگ‌های HTML مجاز نیستند.
  • خطوط و پاراگراف‌ها بطور خودکار اعمال می‌شوند.
کد امنیتی
حمیدرضا (تایید نشده) در تاریخ چهارشنبه, 1399/11/08 - 21:44 نوشته:

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

حامد صمدی (تایید نشده) در تاریخ دوشنبه, 1394/07/20 - 23:20 نوشته:

من برای اینکه این مشکل برطرف شود و با فیلتر کردن باز هم شماره ردیف صحیح باقی بماند از روش زیر استفاده میکنم:

در ستون که قرار است شماره ردیف را  داشته باشیم به عنوان مثال ستون B مد نظر ماست. در سلول B2 فرمول زیر را وارد می کنم.

=SUBTOTAL(3;$C$2:C2)

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

مثلا اگر ما تعداد 200 ردیف کل داده ما باشد در سلول B201 فرمول به صورت زیر خواهد شد. فرض اینکه سطر 1 سر ستون باشد.

=SUBTOTAL(3;$C$2:C201)

که حاصل فرمول عدد 200 خواهد شد.

شماره 3 در تابع  SUBTOTAL گزینه COUNTA می باشد یعنی شمارش سلول های غیر خالی (سلول که در آن مقدار داشته باشد اعم از حروف اعداد فرمول و ...) در ستون C و با فرض اینکه در ستون C تمام سلولهایی که در محدوده مورد استفاده ما می باشد (یعنی ردیف 1 تا 201)  حاوی داده هستند. لازم بذکر است باید ستونی انتخاب شود که در همه ردیفها حاوی داده باشد و حتی یک سلول خالی هم نداشته باشیم در غیر اینصورت اشتباه محاسبه خواهد شد.

مصطفوی (تایید نشده) در تاریخ یکشنبه, 1393/11/05 - 14:14 نوشته:

سلام

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

مثل Insert>Entire row در حالت عادی اکسل

ممنون

فرشید میدانی در تاریخ سه شنبه, 1394/07/21 - 08:35 نوشته:

برای اضافه کردن یک سطر در انتهای Table لازم نیست کار خاصی انجام شود، به محض تایپ کردن یک مقدار ، Table به صورت خودکار بسط پیدا می کند به این خاصیت اصطلاحا Auto Expand می گویند.

البته اگر Total Row داشته باشید این خاصیت کار نخواهد کرد و باید مانند Word و .. در آخرین سلول جدول باشید و کلید TAB را بزنید.

البته برای درج یک سطر در وسط یک Table کافیست که بر روی هر سلولی از Table ، راست کلیک کرده و گزینه Insert را بزنید.

YASHA (تایید نشده) در تاریخ دوشنبه, 1393/04/02 - 18:55 نوشته:

 زمانی که ردیفی در اکسل درست میکنم و شماره گذاری میکنم هنگامی که از یک ستون دیگر فیلتر میگیرم معمولا شماره ردیف ها قاطی میشود از چه فرمولی میتونم کاری کنم که هنگام فیلتر ستون های دیگر شماره ردیفم به همان ترتیب قبل باشد.مثلا 1 2 3 4 5 بعد از فیلتر ستون دیگر ممکن بشه 1 3 5 .میخواهم همان 1 2 3 را بدهد؟

فرشید میدانی در تاریخ سه شنبه, 1393/04/03 - 11:08 نوشته:

لطفا سوالات خود را در انجمن مطرح نمایید.