ایجاد بانک اطلاعاتی در اکسل – راهنمای کاربردی

مطالب مجله فرادرس را در جستجوی گوگل سریعتر ببینید
در این نوشتار با توجه به مفهوم رابطه (Relation) که بین جدولهای یک بانک اطلاعاتی برقرار میشود، به ایجاد بانک اطلاعاتی در اکسل اقدام کرده و جدولها را هم به یکدیگر مرتبط میکنیم. در انتها به کمک جدول محوری (PivotTable) نیز یک گزارش به صورت جمعبندی روی همه جدولها خواهیم ساخت.
بانک اطلاعاتی در اکسل
یک بانک اطلاعاتی یا به عبارت دقیقتر، جدول در یک بانک اطلاعاتی، ساختاری برای ثبت و ذخیره سازی دادهها است. این ساختار به تهیه گزارش و سازماندهی اطلاعات کمک شایانی میکند. جدول در یک بانک اطلاعاتی، از سطر و ستونهایی تشکیل شده که هر یک به منظور خاص یا مفهوم مشخصی به کار میروند.
جدول 1: فیلدها و رکوردهای جدول استان
| نام استان | مساحت استان | نام مرکز استان | نام استاندار |
| فارس | 100 | شیراز | حمیدی |
| تهران | ۱۰۰ | تهران | قاسمی |
| مرکزی | ۱۰۰ | اراک | شیرازی |
| اصفهان | ۱۰۰ | اصفهان | جهاندار |
| خراسان جنوبی | ۴۵ | بیرجند | احمدی فر |
| مازندران | ۴۵ | ساری | اکبرپور |
| بوشهر | ۴۵ | بوشهر | شهشهانی |
به منظور ایجاد رابطه بین جدولهای درون اکسل، یک جدول دیگر نیز با همین شیوه ایجاد میکنیم. جدول دوم را براساس ویژگیهای شهرستانهای استانهای مختلف ایجاد کردهایم. همانطور که میبینید برای ساختن ارتباط بین جدولها، از یک ستون (فیلد) با مقدارهای مشابه استفاده کردهایم. البته لزومی ندارد که نام این فیلد مشترک در هر دو جدول یکسان باشد ولی برای راحتی بیشتر در درک رابطه هر فیلد مشترک در هر دو جدول را با یک نام انتخاب کردهایم.
جدول ۲: فیلدها و رکوردهای جدول شهرستان
| نام استان | نام شهرستان | جمعیت شهرستان |
| فارس | کازرون | 200 |
| فارس | فسا | ۳۲۰ |
| فارس | شیراز | ۴۳۰۰ |
| فارس | آباده | ۴۰۰ |
| مرکزی | دلیجان | ۱۲۰ |
| مرکزی | محلات | ۱۳۰ |
| مرکزی | اراک | ۲۴۰۰ |

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

نکته: ایجاد بانک اطلاعاتی در اکسل ممکن است مشکلات امنیتی یا ضعف دسترسی به منابع بانک را به همراه داشته باشد. ولی برای پایگاههای داده ساده و کم حجم که قرار است یک کاربر داشته باشند، استفاده از بانک اطلاعاتی در اکسل ساده بوده و البته با سرعت نیز ایجاد میشوند.
نامگذاری جدولها در طراح بانک اطلاعاتی اکسل
این بار به سراغ بخش نامگذاری نواحی اکسل رفته و برای این دو جدول نامهای مناسبی انتخاب میکنیم تا هنگام ایجاد رابطه و به کارگیری جدول محوری از آن اسامی بهره ببریم. از برگه Formula در بخش Defined Names، دستور Name Manager را کلیک میکنیم. در پنجره ظاهر شده، مطابق با تصویر ۳، اسامی مناسبی برای هر یک از جدولها ایجاد میکنیم. این کار را با اجرای دستور Edit به هنگام انتخاب هر یک از جدولها انجام میدهیم.

ایجاد رابطه برای بانک اطلاعاتی در اکسل
با توجه به دو جدول بالا، به دنبال پاسخ به یک سوال هستیم. جمعیت هر استان برحسب مجموع جمعیت شهرستانهای آن چقدر است؟ در حقیقت براساس رابطهای که بین هر دو جدول برقرار شده است، میخواهیم یک جمعبندی انجام دهیم.
به این منظور یک رابطه یک به چند بین جدول استان و جدول شهرستان ایجاد میکنیم. به یاد دارید که فیلد یا ستون مشترک در هر دو جدول، نام استان است. در نتیجه از این فیلد به عنوان رابط بین دو جدول استفاده خواهیم کرد.
دستور مربوط به ارتباط دو جدول در اکسل در برگه Data و در بخش Data Tools قرار گرفته است. کافی است روی دکمه Relationships کلیک کنید. تنظیمات را مطابق با تصویر ۴ برای دو جدول انجام میدهیم.

در ادامه مطلب به کمک رابطه ایجاد شده، یک «گزارش محوری» (PivotTable) ایجاد میکنیم که پاسخ سوال مورد نظر (جمعیت هر استان) را محاسبه میکند.
ایجاد گزارش برای بانک اطلاعاتی در اکسل
برای ایجاد جدول محوری، گامهای زیر را بر میداریم.
- سلول فعال را در یکی از جدولها، قرار میدهیم.
- از برگه Insert در بخش Tables، دستور PivotTable را اجرا میکنیم.
- تنظیمات پنجره ظاهر شده را مطابق با تصویر ۵ انجام میدهیم.

- با فشردن دکمه OK با کاربرگی مطابق به تصاویر ۶ مواجه خواهیم شد و تنظیمات را مطابق با تصویر ۷، اجرا میکنیم.
- نتیجه اجرای جدول محوری را بر طبق سلیقه خود با گزینههای بخش style، رنگ آمیزی کرده و به دلخواه خود در میآوریم.
برای هر استان، علاوه بر نام استان، نام استاندار را هم به جدول محوری اضافه کردهایم تا نقش یا شان نزول رابطه بین دو جدول را مشخص کرده باشیم. در خروجی نیز نام استان، سپس نام استاندارد و بعد اسامی شهرستانها و جمعیت شهرستانها دیده میشود. جمع جمعیت هر شهرستان نیز همانطور که میبینید، نتیجه حاصل از اجرای جدول محوری است، جمعبندی جمعیت براساس استانها صورت گرفته است. البته با دوبار کلیک روی هر استان، ریز جمعیت شهرستانهای آن در یک کاربرگ جدید ظاهر خواهد شد.

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

از برگه اول یعنی Subtotals & Filters گزینههای مطابق با تصویر ۷ را انتخاب خواهیم کرد. توجه داشته باشید که چون در جدول محوری گزینه جمع (SUM) برای محاسبه به کار رفته است، نمیتوان از قسمت custom نوع محاسبه دیگری را انتخاب کرد و به این ترتیب انتخاب Automatic، همان تابع جمع را برای استانها در نظر میگیرد.
برای آنکه این جمع را در ابتدا یا بخش سرگروه هر استان مشاهده کنیم، از برگه دوم یعنی (Layout & Print) کمک خواهیم گرفت. اگر گزینههای این پنجره را مطابق با تصویر ۸ انجام دهید، مجموع جمعیت برای هر استان، در سطر مربوط به هر استان، ظاهر خواهد شد و ما در نهایت به پاسخ مورد نظر خواهیم رسید.

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

به این موضوع نیز توجه داشته باشید که هر یک از برچسبهای مربوط به جدول محوری را میتوانید ویرایش کرده و به دلخواه خود در آورید. برای مثال در جدول مربوط به تصویر ۹، مقدار سلول در آدرس A3 به نام «استان/استاندارد» تغییر یافته است. همچنین در سلول B3 نیز عبارت «مجموع جمعیت» را ثبت کردهایم.
نکته: البته محاسبه جمعیت استان به کمک جدول شهرستان به تنهایی نیز قابل انجام بود. کافی بود با تابع Subtotal برای ستون جمعیت شهرستان، مجموع را محاسبه کرده و با فیلتر کردن هر استان، مجموع جمعیت آن استان را برحسب جمع جمعیت شهرستان، بدست میآوردیم.
خلاصه و جمعبندی
به این نکته نیز توجه داشته باشید که اکسل برای ثبت اطلاعات به صورت سطری، محدودیت داشته و قادر به ثبت فقط ۱٫048٫576 سطر است. در نتیجه برای ایجاد جدولهایی بزرگتر از این حد، بهتر است از بانکهای اطلاعاتی دیگر مثل اکسس یا نرمافزارهای حرفهای بانک اطلاعاتی یا پایگاه داده مثل اوراکل یا MySQL استفاده کرد.
مطالب مجله فرادرس را در جستجوی گوگل سریعتر ببینید













عالیست مشرح تر بشه بهتره