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

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

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

در این راهنما، نحوه ایجاد دو جدول رابطه‌ای در اکسل یا ادغام دو جدول در اکسل، شامل یک جدول اصلی (Master) و یک جدول جزئی (Detail)، شرح داده می‌شود. جدول اصلی حاوی اطلاعاتی نظیر شناسه مشتری (customer_id)، محصول (product)، کانال فروش (sales channel) و هزینه (cost) است. این جدول به عنوان جدول اصلی عمل کرده و به ندرت تغییر می‌کند.

آموزش اکسل از صفر تا صد

کلیک کنید

جدول جزئی شامل اطلاعاتی نظیر شناسه مشتری (customer_id)، نام (name) و کشور (country) است. داده‌های این جدول (جدول جزئی) اغلب تغییر می‌کنند. ارتباط بین این دو جدول از طریق ستون مشترک شناسه مشتری (customer_id) در هر دو جدول برقرار می‌شود.

جدول 1: جدول سفارش‌ها (Order table)

این جدول شامل فیلدهای شناسه مشتری (customer_id)، محصول (product)، کانال فروش (sales channel) و هزینه (cost) است.

جدول 2: جدول مشتریان (Customer table)

این جدول شامل فیلدهای شناسه مشتری (customer_id)، نام (name) و کشور (country) است.

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

روش 1: از طریق ایجاد جدول محوری (Pivot Table)

مرحله 1: به تب درج (Insert) در نوار ابزار (Ribbon) رفته و سپس جدول محوری (PivotTable) را انتخاب کنید.

مرحله 2: پنجره گفتگوی ایجاد جدول محوری (Create PivotTable) ظاهر می‌شود. در این پنجره و در تنظیمات پیوت تیبل در اکسل، جدول سفارش‌ها (orders table) را در بخش اول انتخاب کنید (در اینجا، محدوده داده‌های خود را در صفحه گسترده انتخاب کرده‌ایم). اطمینان حاصل کنید که گزینه افزودن این داده به مدل داده (add this data to the data model) علامت زده شده باشد و سپس روی OK کلیک کنید.

مرحله 3: همین فرآیند را برای جدول مشتریان (customer table) تکرار کنید. فیلدهای جدول محوری را بررسی کنید و به نمای همه (All view) بروید. هر دو جدول نمایش داده می‌شوند. از آنجا که ما از محدوده‌ها انتخاب کرده‌ایم، نام محدوده (در مورد ما range 2 و range 3) نمایش داده می‌شود.

مرحله 4: برای ساخت جدول محوری و ایجاد ارتباط بین جداول در اکسل، نام (name) را از جدول مشتریان (Customers table) انتخاب کنید و آن را در ناحیه ردیف‌ها (Rows area) قرار دهید. می‌توانیم با باز کردن جدول، علامت آن را بررسی کنیم یا فیلد را به ناحیه ردیف‌ها بکشانیم. هزینه (cost) را از جدول سفارش‌ها (Orders table) انتخاب کنید و آن را در ناحیه مقادیر (Values area) قرار دهید.

مرحله 5: صفحه فیلدهای جدول محوری (Pivot table fields) اعلانی را مبنی بر ارتباط بین جداول مورد نیاز است (Relationship between the tables is needed) نمایش می‌دهد. دو گزینه در دسترس است:

• می‌توانیم ارتباط بین جداول را ایجاد کنیم.
• می‌توانیم با کلیک روی تشخیص خودکار (Auto Detect)، اجازه دهیم اکسل حدس بزند.

مرحله 6: در حالت تشخیص خودکار (Auto Detect)، اکسل یک رابطه ایجاد می‌کند. برای بررسی، روی مدیریت رابطه (manage relationship) کلیک کنید. با کلیک روی تشخیص خودکار (Auto Detect)، ارتباطات ساده با جداول کوچک‌تر و نام فیلدهای سازگار ممکن است به صورت خودکار ایجاد شوند. پس از اتمام، روی دکمه بستن (close) کلیک کنید.

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

اگر بخواهیم ارتباط را خودمان، بدون اجازه دادن به اکسل برای ایجاد خودکار، ایجاد کنیم، روی دکمه ایجاد (create) در فیلدهای جدول محوری کلیک کنید.

مرحله 8: در این مرحله از آموزش ایجاد ارتباط بین جداول در اکسل منوی ویرایش رابطه (edit relationship) باز می‌شود.

• جدولی که شامل سفارش‌ها است را به عنوان جدول اصلی (range در مورد ما) انتخاب کنید.
• شناسه مشتری (Customer_id) را برای ستون (خارجی) (Column (Foreign)) انتخاب کنید.
• جدولی که شامل مشتریان است را به عنوان جدول مرتبط (range1 در مورد ما) انتخاب کنید.
• شناسه مشتری (Customer_id) را به عنوان ستون مرتبط (اصلی) (Related Column (Primary)) انتخاب کنید.

برای اتمام ایجاد رابطه، روی OK کلیک کنید.

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

روش 2: از طریق ایجاد رابطه بین دو جدول

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

آموزش فرمول‌ها و توابع کاربردی اکسل

کلیک کنید

مرحله 1: به تب داده (Data) در نوار ابزار (Ribbon) رفته و سپس به گروه ابزارهای داده (data tools group) بروید. روی دکمه روابط (relationships) کلیک کنید.

مرحله 2: پنجره گفتگوی مدیریت روابط (relationships manage) باز می‌شود. همه روابط موجود با وضعیت فعال (active) نمایش داده می‌شوند. می‌توانیم تمام گزینه‌ها از جمله ویرایش (edit)، فعال کردن (activate)، غیرفعال کردن (deactivate)، حذف (delete)، تشخیص خودکار (auto-detect) و غیره را انجام دهیم.

مرحله 3: پس از اتمام، روی بستن (close) کلیک کنید. این کار یک جدول با شناسه مشتری مشابه از هر دو جدول ایجاد می‌کند. این جدول، نام مشتری را از جدول مشتریان و کل هزینه را از جدول سفارش‌ها نمایش می‌دهد.

مزایای جداول رابطه‌ای

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

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

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

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

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

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

مثال عملی –V(X)LOOKUP در مقابل مدل داده

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

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

با VLOOKUP:

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

با فرض اینکه 30000 تراکنش دارید، باید 60000 فرمول VLOOKUP بنویسید تا این گزارش را ایجاد کنید!!!

با مدل داده:

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

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

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

• حالا به نوار داده بروید و روی دکمه روابط کلیک کنید.

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

• در صورت نیاز، روابط بیشتری اضافه کنید.

استفاده از روابط در گزارش‌ها و تحلیل‌های محوری:

1. هر جدولی را انتخاب و یک جدول محوری درج کنید (درج > جدول محوری، اطلاعات بیشتر در مورد جداول محوری).
2. مطمئن شوید که کادر انتخاب افزودن این داده به مدل داده را علامت زده باشید.

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

مثال: گزارش فروش دسته و جنسیت

• دسته را به برچسب‌های ردیف اضافه کنید.
• جنسیت را به برچسب‌های ستون اضافه کنید.
• مقدار را به مقادیر اضافه کنید.
• و گزارش شما آماده است!

نکاتی که هنگام استفاده از روابط باید در نظر داشته باشید

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

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

مزایای جداول محوری مبتنی بر مدل داده

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

آموزش اکسل مقدماتی نسخه 2021

کلیک کنید

• شمارش‌های متمایز: این عدد ساده اما اغلب محاسبه آن دشوار است که پس از داشتن محور مبتنی بر مدل داده، به راحتی قابل دریافت است. فقط به تنظیمات فیلد مقدار بروید و نوع خلاصه را به شمارش متمایز تغییر دهید.
• معیارها و DAX: هنگامی که یک مدل داده دارید، می‌توانید ویژگی‌های کامل Power Pivot را در کار برگ خود آزاد کنید. می‌توانید معیارها (با استفاده از زبان DAX) را ایجاد و مواردی را محاسبه کنید که در غیر این صورت با اکسل معمولی غیرممکن هستند.
• محورها از داده‌ها در فایل‌ها و پایگاه‌های داده دیگر: می‌توانید مدل داده را با قابلیت‌های Power Query ترکیب کرده تا محورها را از داده‌ها در مکان‌های دیگر ایجاد کنید. برای مثال، می‌توانید از داده‌های فروش در SAP با داده‌های مشتری در سیستم CRM یک محور بسازید.
• محورها از بیش از 1 میلیون ردیف داده: می‌توانید به مجموعه‌های داده بسیار بزرگ متصل شوید و با کمک مدل داده از آن‌ها محور بسازید.
• تبدیل جداول محوری به فرمول: هنگامی که یک جدول محوری مبتنی بر مدل داده دارید، می‌توانید آن را به مجموعه‌ای از فرمول‌ها تبدیل کنید. می‌توانید به این ویژگی از نوار تجزیه و تحلیل دسترسی داشته باشید. این کار محور شما را با دسته‌ای از فرمول‌های CUBE جایگزین می‌کند.

معایب مدل داده:

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

• سازگاری: ویژگی مدل داده و رابطه فقط در اکسل 2013 یا بالاتر موجود است. این بدان معناست که شما نمی‌توانید چنین گزارش‌های محوری را با افرادی که از نسخه‌های قدیمی‌تر اکسل استفاده می‌کنند، ایجاد یا به اشتراک بگذارید.
• عدم امکان گروه‌بندی داده‌ها: در جداول محوری معمولی، می‌توانید فیلدهای عددی، داده یا متن را گروه‌بندی کنید؛ اما با جداول محوری مدل داده، دیگر نمی‌توانید داده‌ها را گروه‌بندی کنید. باید یک جدول دیگر با نگاشت گروه ایجاد کنید و از آن به عنوان یک رابطه استفاده کنید.

پیشنهاد مطالعه: ایجاد فرم در اکسل با vba در چند گام ساده

سخن پایانی

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

سؤالات متداول:

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

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

آیا برای استفاده از مدل داده و روابط به دانش خاصی نیاز است؟

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

آیا می‌توان از مدل داده و روابط برای اتصال به منابع داده خارجی استفاده کرد؟

• بله با استفاده از Power Query، می‌توانید داده‌ها را از منابع مختلف مانند پایگاه‌های داده و فایل‌های دیگر به مدل داده خود اضافه کنید.

آیا محدودیت‌هایی در استفاده از مدل داده و روابط وجود دارد؟

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

آموزش اکسل با مکتب خونه

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