برنامه Excel 🔢 از قدرتمندترین ابزارهای تجزیه و تحلیل داده‌ها و مدیریت مجموعه بزرگی از اطلاعات است. شما می‌توانید با استفاده از VLOOKUP،  INDEX-MATCH، SUMIF و… زمان زیادی را پای تجزیه و تحلیل داده‌ها بگذارید. در صورتی که می‌خواهید در وقت خود صرفه‌جویی کنید، پیشنهاد می‌کنیم از Data Model استفاده کنید تا بتوانید رابطه بین دو جدول و جداول محوری را نشان دهید. در این مطلب از کلاسیک وب به آموزش ایجاد ارتباط بین چند یا دو جدول در اکسل می‌پردازیم.

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

?How to Create Relationships Between Multiple Tables Using Data Model in Excel

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

ایجاد ارتباط بین جداول در اکسل با Power Query و Power Pivot

برای ایجاد ارتباط بین دو جدول در اکسل به Power Pivot و Power Query نیاز دارید. برای اکسل ۲۰۱۰ باید افزونه Power Pivot را از سایت Microsoft دانلود کنید و روی رایانه خود نصب کنید. برای Power Query هم همین کار را انجام دهید.

در اکسل ۲۰۱۳ نیز Power Pivot در نسخه Office Professional Plus وجود دارد و کافی است آن را قبل از اولین استفاده فعال کنید. برای این کار Excel را باز کنید و روی Fie کلیک کنید. سپس روی Options بزنید. حال روی Add-ins کیک کنید. روی منوی Manage کلیک کنید و COM Add-ins را انتخاب کنید. روی Go کلیک کنید و تیک Microsoft Power Pivot for Excel را بزنید. برای Power Query هم همین مراحل را بروید.

در اکسل ۲۰۱۶ و جدیدترین نیز می‌توانید Power Pivot menu را در Ribbon پیدا کنید. Power Query نیز در سربرگ Data وجود دارد.

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

دیتا مدل چیست؟

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

Sample student data (فقط شامل داده می‌شود)

Sample student data 2 (مدل کامل)

می‌توانید نمونه جداول دیگر را هم از منابع دیگر مثل SQL Server، Microsoft Access، Excel workbooks و… دانلود کنید. سپس باید داده‌ها را قالب‌بندی کنید تا بتوانید از آن‌ها در اکسل استفاده کنید. برای این کار در اکسل ۲۰۱۶ و جدیدتر روی سربرگ Data کلیک کنید و New Query را انتخاب کنید. سپس یکی از راه‌های وارد کردن داده‌ها از منابع خارجی یا داخلی را بر حسب تناسب انتخاب کنید.

در Excel 2013 روی Power Query در Ribbon کلیک کنید. سپس Get External Data را انتخاب کنید. پس از آن، داده را وارد کنید. در پنجره Navigator می‌توانید جداول موردنظر را وارد کنید. تیک Select multiple items را بزنید تا بتوانید جداول را با هم وارد کنید.

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

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

تمامی سلول‌های حاوی داده را انتخاب کنید و داده‌های خود را در یک مدل جدول‌بندی کنید. سپس Ctrl+T را بگیرید.

حال کل جدول را انتخاب کنید و روی سربرگ Power Pivot کلیک کنید.

از بخش Tables، روی Add to Data Model کلیک کنید. اکسل روابط بین داده‌های مرتبط با در جدولی ایجاد می‌کند. برای این کار باید روابط اصلی و خارجی در جدول وجود داشته باشد. اکسل از اطلاعات مربوط به جدول وارد شده، به عنوان پایه ایجاد ارتباط بین جداول در یک دیتا مدل استفاده می‌کند.

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

برای ادغام دو جدول در اکسل با Data Model باید روابط بین جداول را ایجاد کنید تا بتوانید گزارشی بامعنا داشته باشید. برای هر جدول باید نام مشخصی بگذارید. مثل Class Number، Semester ID، Student ID و… .

حالت Diagram View پاور پیوت به شما اجازه می‌دهد تا برای ایجاد رابطه، فیلدهای مختلف را Drag و Drop کنید. برای ادغام جدول در اکسل و دیتا مدل مراحل زیرا را بروید:

در ورک بوک اسکل روی Power Pivot بزنید. از بخش Data Model روی Manage بزنید.

از بخش View که در سربرگ Power Pivot Home قرار دارد، روی Diagram View بزنید. سپس عناوین ستون جدول را که بر اساس نام جدول گروه‌بندی شده‌اند، مشاهده خواهید کرد.

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

در زیر Relationship در اکسل برای جداول را می‌توانید مشاهده کنید:

Table Students | Student ID to table Grades | Student ID

Table Semesters | Semester ID to table Grades | Semester

Table Classes | Class Number to table Grades | Class ID

شما می‌توانید برای ایجاد Connection در اکسل، دو ستون با Value متفاوت را ایجاد کنید. اگر موارد تکراری وجود داشته باشد با خطا زیر مواجه خواهید شد:

در Diagram View روابط، از یک طرف * و در طرف دیگر ۱ را مشاهده خواهید کرد. این خطا به معنای وجود رابطه One-to-many در بین جداول است. در ادیتور Power Pivot روی سربرگ Design کلیک کنید. سپس روی Manage Relationships کلیک کنید تا بتوانید متوجه روابط ایجاد شده بشوید.

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

مدلسازی داده ها در اکسل با استفاده از Excel Data Model و ایجاد PivotTable

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

در ادیتور Power Pivot روی سربرگ Home کلیک کنید. از Ribbon روی PivotTable کلیک کنید. از میان New Worksheet و Existing Worksheet یکی را انتخاب کنید.

روی OK بزنید. اکسل یک PivotTable به سمت راست اضافه خواهد کرد که Field List را نشان می‌دهد. در ادامه می‌توانید یک نمای کلی از جدول Pivot ایجاد شده با استفاده از دیتا مدل را برای نمونه‌ای که پیشتر آوردیم، مشاهده کنید. از این بخش می‌توانید برای ایجاد نمودارها و جداولی با داده‌های بزرگ هم استفاده کنید.

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

ایجاد ارتباط بین دو جدول در اکسل: مثال دوم

برای یادگیری بهتر شما مثال دیگری در رابطه با ایجاد ارتباط بین دو جدول در اکسل می‌آوریم. در این مثال دو جدول به نام Orders و Customers داریم. جدول Orders شامل Customer ID، Product Sold و… می‌شود. جدول Customers شامل Name و Country برای Customer ID می‌شود. حال اگر بخواهیم داده‌های Orders را بر اساس Name و Country تحلیل کنیم، باید چه کنیم؟

به سربرگ Insert بروید و Pivot Table را از بخش Tables انتخاب کنید.

از منوی Create PivotTable، جدول Orders را انتخاب کنید. تیک Add this data to the Data Model را بزنید. OK را بزنید. این کار را برای جدول Customers هم انجام دهید.

سپس باید بتوانید هر دو جدول را در PivotTable Fields و بخش All مشاهده کنید. می‌توانید یکی از جداول Pivot را حذف کنید زیرا ما تنها به یک مورد نیاز داریم و داده‌ها در دیتا مدل باقی خواهند ماند.

جدول Pivot را با Name از جدول Customers و بخش Rows بسازید. بخش Values را هم با Total از جدول Orders ایجاد کنید.

فیلد Name را از جدول Customers به بخش Rows بکشید. روی فلش کنار نام جدول کلیک کنید تا بتوانید فیلدها را مشاهده کنید. این کار را برای Total هم انجام دهید و از Orders به Values بکشید. سپس اعلانی با پیام Relationships between tables may be needed نمایان خواهد شد. از این بخش Auto-Detect را انتخاب کنید و اجازه دهید اکسل روابط بین جداول را حدس بزند. می‌توانید خودتان هم آنان را Create کنید.

ادغام دو جدول در اکسل و ایجاد روابط

برای ایجاد ارتباط بین چند یا دو جدول در اکسل اگر Auto-Detect را انتخاب کردید، اکسل روابط را بررسی و ایجاد می‌کند. می‌توانید روی Manage Relationships بزنید یا Close را انتخاب کنید. سپس می‌توانید نتیجه را مشاهده کنید و روابط را که در جداولی کوچک‌تر نمایان می‌شوند، بررسی کنید.

اگر Create را انتخاب کردید، منوی Create Relationship نمایان خواهد شد. Orders را در بخش Table انتخاب کنید. سپس Customer ID را از بخش Column (Foreign) انتخاب کنید. Customers را از بخش Related Table انتخاب کنید. Customer ID را از بخش Related Column (Primary) انتخاب کنید. درنهایت، OK را بزنید.

حال جدول محوری شما شامل Name از جدول Customers و  Total از جدول Orders می‌شود.

می‌توانید قبل از ایجاد جدول محوری، روابط را ایجاد کنید. این کار را باید از سربرگ Data یا Analyze انجام دهید.

پنجره Manage Relationships باز خواهد شد. در این بخش می‌توانید آنان را Select یا انتخاب کنید تا بتوانید ویرایششان کنید یا حذف، فعال یا غیرفعالشان کنید. برای ایجاد ارتباط بین چند یا دو جدول در اکسل هم می‌توانید روی New بزنید و ارتباط جدیدی ایجاد کنید.

نظرات و پیشنهادات شما؟

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