آیا تا به حال با این چالش روبرو شده اید که داده های مهم شما در اکسل در چندین جدول مختلف پراکنده شده باشند. در این شرایط ممکن بوده که شما برای رسیدن به یک پاسخ واحد، مجبور به استفاده از فرمول های سنگین و پیچیده ای مثل VLOOKUP یا INDEX-MATCH باشید؟ اگر پاسخ شما به این پرسش مثبت است بدانید که تنها شما نیستید که با چنین مشکلاتی مواجه شده اید. در دنیای واقعی، داده ها هرگز در یک جدول ساده و منظم قرار نمی گیرند.

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

چرا نباید در اکسل همیشه از VLOOKUP استفاده کرد؟

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

کندی شدید فایل ها در اکسل

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

پیچیدگی فرمول ها

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

عدم انعطاف پذیری

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

مفهوم پایگاه داده در اکسل

قبل از شروع بحث باید بدانید که اکسل با دو مدل جدول اصلی سروکار دارد که در ادامه به بررسی این دو نوع جدول خواهیم پرداخت:

• جداول واقعیت یا Fact Tables ( جداول تراکنش)

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

• جداول ابعاد یا Dimension Tables (جداول مشخصات)

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

مفهوم کلید ( Key): پل ارتباطی بین جداول در اکسل

برای ارتباط برقرار نمودن میان جداول ما نیاز به یک شناسه مشترک تحت عنوان ID داریم. در جدول مرجع این شناسه باید به صورت یک شناسه یکتا یا Primary Key باشد و در جدول تراکنش این شناسه به صورت یک Fact است که عنصری قابل تکرار می باشد.

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

در ادامه مقاله Pivot Table ما به بررسی مسیر ایجاد ارتباط بین جداول در اکسل خواهیم پرداخت.

گام اول تبدیل داده ها به Table در اکسل

در اولین گام کاربر باید محدوده داده های خود را مشخص نماید. در ادامه این کاربر است که با انتخاب کلید میانبر Ctrl + T مسیر فوق را تسهیل می کند. همچنین شما می توانید از منوی Table Design یک نام مشخص برای جداول خود انتخاب نمایید. برای مثال از عنوان هایی مانند Sales_Table می توانید استفاده کنید.

گام دوم اضافه کردن جداول به Data Model

در ابتدا شما باید روی یکی از جداول کلیک نموده و به تب Insert بروید و Pivot Table را انتخاب کنید. نکته مهمی که در این بین توسط کاربران باید در نظر گرفته شود این است که حتما تیک گزینه Add this data to the Data Model را فعال نمایید.

گام سوم ایجاد رابطه ( Relationship )

در گام اول نیاز است تا به تب Data بروید و روی عبارت Relationship کلیک کنید. در ادامه نیز با انتخاب عبارت New مسیر را ادامه خواهیم داد. برای ادامه این مسیر شما باید نسبت به انتخاب جدول اول و ستون مشترک در قسمت Table و Column اقدام نمایید. همچنین شما باید جدول دوم و ستون متناظر آن را در قسمت Related Table و Related Column انتخاب کنید.

جادوی اکسل پیوت تیبل Pivot Table با داده های چند جدولی

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

یک مثال ساده و کاربردی

فرض کنید دو جدول دارید:

• جدول فروش: شامل SaleID، ProductID، CustomerID، Amount
• جدول محصولات: شامل ProductID، ProductName، Category

در این حالت با ایجاد رابطه بین ProductID در دو جدول، می توانید به راحتی گزارشی بسازید که در آن:

• ردیف ها = Category
• مقادیر = مجموع Amount

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

مزایای استفاده از ارتباط بین جداول در اکسل

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

• کاهش حجم فرمول ها در اکسل
• افزایش سرعت تحلیل ها
• مدیریت بهتر داده های بزرگ در اکسل
• کاهش احتمال خطا
• ساخت گزارش های پویا و حرفه ای در محیط اکسل
• هماهنگی بهتر با مدل های تحلیلی و Bl

استفاده از Pivot  Power برای کاربران حرفه ای

برای مدیریت میلیون ها ردیف داده در اکسل، می توانید از Pivot Power استفاده کنید. این ابزار به شما اجازه می دهد از زبان DAX برای انجام محاسبات بسیار پیچیده استفاده نماید و روابط بسیار گسترده را در این بین مدیریت نمایید.

DAX جیست؟

DAX یا Data Analysis Expressions زبانی برای ساخت فرمول های تحلیلی در اکسل و ابزارهای مایکروسافت مانند Power BI می باشد. با استفاده از DAX شما می توانید محاسباتی مانند جمع کل فروش، میانگین فروش، درصد سهم هر محصول از کل فروش، رشد ماهانه و مقایسه دوره ای را انجام دهید.

چرا Power Pivot اهمیت دارد؟

وقتی داده ها زیاد می شوند Pivot Table معمولی ممکن است برای برآورد نیازهای پیچیده عملکرد کافی از خود به نمایش نگذارند. در این شرایط بهتر است تا کاربران از ابزارهای قدرتمندی مانند Pivot  Power برای مدیریت فرایندها بهره ببرند. Pivot  Power این امکان را برای کاربر فراهم می نماید تا چندین جدول را به همدیگر متصل و برای مدیریت فرایندها از روابط پیچیده بهره گیرد. همچنین آنها می توانند به کمک این ابزار داده های بسیار حجیم را تحلیل نموده و شاخص های تحلیلی سفارشی بسازند.

نکات و ترفندهای طلایی برای جلوگیری از خطا

برای اینکه ارتباط بین جداول در اکسل بدون مشکل ایجاد شود، به این نکات باید دقت کنید:

تکرار در کلید اصلی:

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

تطابق نوع داده:

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

پاکسازی داده ها:

مقادیر خالی یا خطا در ستون های رابط باعث شکست ارتباط میان جداول می شوند.

استفاده از نام های واضح در جدول ها:

نام هایی مانند Customers_Table یا Orders_Table فهم ساختار فایل ها را برای این ابزار ساده تر می نماید.

یکپارچگی داده ها:

اگر دادها از منابع مختلف می آیند قبل از ساخت رابطه آن ها را استاندارد کنید.

اشتباهات رایج در ایجاد رابطه بین جداول

گاهی کاربران با وجود اجرای دقیق مراحل کار در محیط اکسل همچنان با خطاهایی روبرو می شوند. علت بروز این خطاها می تواند یکی از موارد زیر باشد:

• یکتا نبودن کلید در جدول مرجع

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

• تفاوت فرمت داده ها

مثلا یک ستون به صورت عددی و ستون دیگر به صورت متن ذخیره شده است.

• وجود فاصله اضافی یا کاراکترهای نامرئی

گاهی یک فاصله اضافه در انتهای شناسه باعث می شود دو مقدار مشابه یکسان تشخیص داده نشوند.

• ساختار نادرست جدول ها

اگر دادها در Table به صورت واقعی تبدیل نشده باشند امکان استفاده دقیق از Data Model کاهش خواهد یافت.

چه زمانی Relationship بهتر از VLOOKUP است؟

استفاده از ارتباط بین جداول یا Pivot Table به خصوص در شرایط زیر بسیار مناسب تر از  VLOOKUP است:

زمانی که حجم داده زیاد است.

وقتی چندین جدول مختلف دارید.

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

وقتی نیاز دارید از مدل داده ای و Pivot Table استفاده کنید.

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

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

جمع بندی

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

اگر تا امروز برای اتصال داده ها فقط از ابزارهای جستجو مانند VLOOKUP و فرمول های تکراری استفاده می نمودید، حالا زمان آن رسیده که از ابزارهای کاربردی مانند Pivot Table، Data Model و Relationship برای مدیریت یک کار دقیق و حرفه ای بهره ببرید. در نهایت باید بدانید که این مهارت نه تنها در اکسل، بلکه در دنیای تحلیل داده ها و هوش تجاری نیز برای شما بسیار ارزشمند خواهد بود.