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

اگر در نسخه‌های قدیمی‌تر از ۲۰۱۳ قرار بر انجام این کار بود مجبور بودیم با استفاده از توابع جستجو مانند VLOOKUP این محاسبات تودرتو را انجام دهیم، که این روش باعث کند شدن سرعت محاسبات در فایل مزبور می‌گردید. در نسخه‌های ۲۰۱۳ و ۲۰۱۶ ابزارهای متنوعی برای انجام این کار ارائه گردیده است.
یکی از این ابزارها افزونه قدرتمند Power Pivot می‌باشد که در گزارش گرفتن از بانک‌های اطلاعاتی بسیار قوی عمل می‌نماید و می‌تواند با ساختن مدل‌های اطلاعاتی در ترکیب با ابزار Pivot Table گزارش‌های تحلیلی بسیار متنوعی تولید نماید. البته مهارت در استفاده از این ابزارها نیاز به‌صرف وقت مناسب برای یادگیری آن‌ها دارد.

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

این روش توسط Ash Sharma و تیم قوی که در زمینه IT دارد و متخصص اکسل هستند ارائه گردیده است و خبر آن توسط استاد Bill Jelen در سایت Mrexcel نشر گردیده است.

دقت داشته باشید که این مدل با استفاده از ابزارهای موجود در نسخه ۲۰۱۶ ارائه و بسیار ساده تعریف گردیده است.

صورت سؤال:

ما می‌خواهیم بدانیم با توجه به جداول اطلاعاتی که در اختیار داریم، جمع هزینه‌های خانوارهای ۱ تا ۶ نفره به تفکیک کالاهای خوراکی و غیرخوراکی چه مبلغی می‌باشد؟

داده‌های مسئله:

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

جدول ۱ (جدول دسته کالا):
گروه کالاهای مختلف که در دسته کالاهای خوراکی و غیر‌خوراکی در جدول ذیل دسته‌بندی گردیده است.
در این مثال به رنگ سبز مشخص گردیده است.

جدول ۲ (جدول قلم کالا):
قلم کالاهای مختلف مورد استفاده خانواده که در کدام گروه کالا قرار دارد را مشخص می‌نماید. بین این جدول و جدول ۱ ستون «گروه کالا» مشترک می‌باشد.
در این مثال این جدول به رنگ آبی مشخص گردیده است.

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

جدول ۴ (کد خانوار):
در جدول ۴ تعداد نفرات خانواری که اطلاعات هزینه آن‌ها دریافت گردیده لیست شده است و تعداد نفرات آن در جلوی هر کد خانوار ثبت گردیده است. ستون مشترک بین این جدول و جدول ۳ ستون کد خانوار می‌باشد.
این جدول به رنگ زرد مشخص گردیده است.

توضیح مسئله:

طبق خواسته صورت سؤال، ما جمع هزینه‌های هر خانوار به تفکیک دسته کالا را نیاز داریم. با توجه به اینکه ما هزینه‌های هر خانوار را به تفکیک قلم کالا در اختیار داریم لذا می‌بایست از قلم کالا به دسته کالا برسیم.
بنابراین با توجه به جدول ۲ از قلم کالا به گروه کالا و با توجه به جدول ۱ از گروه کالا به دسته کالا می‌رسیم. از طرفی در صورت سؤال این اطلاعات را برای خانوارهایی که تعداد اعضای آنها بین ۱ تا ۶ نفر است نیاز داریم.
بنابراین از جدول ۴ باید تعداد اعضای هر خانوار را استخراج کنیم.

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

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

۱. باید ابتدا محدوده هر ۴ جدول را به Table تبدیل نماییم. بنابراین محدوده هر جدول را جداگانه انتخاب و از مسیر Insert > Table گزینه Table را کلیک می‌کنیم و یا از کلید میانبر Ctrl+T استفاده می‌نماییم.
۲. از مسیر منوی Data گزینه Relationships را کلیک کرده تا فرم Manage Relationships باز گردد.

۳. بر روی دکمه New از فرم Manage Relationships کلیک کرده تا فرم Create Relationships باز شود.
در فرم باز شده در قسمت Table و Related Table دو به دو جدول‌هایی را که نام‌گذاری کردیم و با یکدیگر ارتباط دارند را انتخاب و ستون مشترک بین آن‌ها را در قسمت Column و Related Column مشخص می‌نماییم.
۴. سپس یک سلول خالی را انتخاب کرده و از منوی Insert رفته و بر روی گزینه PivotTable کلیک می‌کنیم. با فعال کردن ابزار PivotTable شما می‌توانید جدول‌های مختلف را که در بین آنها ستون‌های مشترکی وجود دارد مشاهده نمایید.
۵. سپس با استفاده از ستون‌های مختلف زیرمجموعه این جدول‌ها می‌توانید گزارشاتی تهیه نمایید که با توجه به ارتباط بین ستون‌های مختلف جداول تولید می‌گردد.
در شکل ۸ منطقه زرد رنگ از جدول ۴ که با همین رنگ می‌باشد، منطقه سبز رنگ از جدول ۱ بوده و محاسبات از هزینه‌هایی که در جدول ۳ با رنگ نارنجی می‌باشد انتخاب شده است.

فایل پیوست

فایل پیوست حل مسئله فوق می‌باشد.

Download “Relationship” relationship.xlsx – 10025 بار دانلود شده است – 367,42 کیلوبایت

منابع

1- mrexcel.com