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

بانک اطلاعاتی یا پایگاه داده، ابزاری برای نظم دهی و ثبت داده‌ها، براساس یک ساختار ثابت و مشخص است. بسیاری از نرم‌افزارهای پایگاه داده مثل «مایکروسافت اکسس» (MS-Access) یا «اوراکل» (Oracle) و «مای اس کیو ال» (My SQL)، از «جدول» (Table) برای ذخیره‌سازی و ثبت داده‌ها در پایگاه داده یا بانک اطلاعاتی استفاده می‌کنند. در مقابل نرم‌افزارهای «صفحات گسترده» (Spread Sheets) مثل «مایکروسافت اکسل» (MS-Excel)، در ذات خود از جدول یا جدول‌های متعددی تشکیل شده‌اند ولی برای ورود و ذخیره سازی داده‌ها، کاربر را به ساختار مشخصی محدود نمی‌کنند. اگر ساختار سطری و ستونی را در این نرم‌افزارها به شکلی تعیین کنیم که با شیوه بانک‌های اطلاعاتی مطابقت داشته باشد، دست به ایجاد بانک اطلاعاتی در اکسل زده‌‌ایم. در این متن به همین موضوع پرداخته و به آموزش ایجاد بانک اطلاعاتی در اکسل خواهیم پرداخت.

• مقاله پیشنهادی: بانک اطلاعاتی چیست و چطور آن را یاد بگیریم ؟ — راهنمای شروع به زبان ساده

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

برای آشنایی بیشتر با مفهوم بانک اطلاعاتی یا پایگاه داده و خصوصیات اصلی آن نوشتارهای پایگاه داده و اصطلاحات کاربردی آن — به زبان ساده و رابطه در پایگاه داده | به زبان ساده را مطالعه کنید. همچنین خواندن مطالب رابطه در اکسس — راهنمای کاربردی و آموزش اکسس رایگان | به زبان ساده و کامل نیز خالی از لطف نیست.

بانک اطلاعاتی در اکسل

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

هر ستون از جدول اطلاعاتی در این حالت، یک «فیلد» (Field) نامیده شده و اقلام اطلاعاتی را مشخص می‌کند. مقدار هر یک از این اقلام نیز در سطرهای جدول قرار گرفته که در اصطلاح، «رکورد» (Record) را تشکیل می‌دهد. به این ترتیب اگر در اکسل نیز به همین شیوه برای ثبت اطلاعات اقدام کنیم، به ایجاد جدول بانک اطلاعاتی در اکسل دست زده‌ایم. دو جدول ۱ و ۲ که در ادامه معرفی شده‌اند را در نظر بگیرید. این دو جدول، اطلاعات مربوط به استان و شهرستان‌های کشور را در خود جای داده‌اند. به علت جلوگیری از «افزونگی» (Redundant) اطلاعات، ویژگی‌های شهرستان و استان‌ها را در دو جدول جداگانه ثبت و ذخیره کرده‌ایم زیرا اطلاعات و ویژگی‌های هر «هویت» (Entity) باید در یک جدول ثبت شود.

جدول 1: فیلدها و رکوردهای جدول استان

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

جدول ۲: فیلدها و رکوردهای جدول شهرستان

اگر دقت بیشتری داشته باشیم متوجه می‌شویم که نام استان در جدول استان‌ها می‌تواند «کلید اصلی» (Primary Key) باشد، زیرا مقدار آن منحصر به فرد بوده و برای ثبت رکورد مربوط به یک استان ثبت مقدار برای آن الزامی است.

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

به منظور معرفی هر یک از این جدول‌ها به عنوان یک پایگاه داده یا بانک اطلاعاتی در اکسل، از دستور table استفاده می‌کنیم. برای انجام این کار ناحیه مربوط به جدول اول را از کاربرگ انتخاب کرده و از برگه Insert در بخش Tables دستور table را اجرا می‌کنیم. به تصویر ۱، توجه کنید. برای نگهداری و ثبت اطلاعات مربوط به استان‌های کشور، ساختاری به این شکل در اکسل مناسب است. در این صورت ایجاد بانک اطلاعاتی در اکسل آغاز می‌شود.

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

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

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

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

به این ترتیب جدول اول به نام «استان» و جدول دوم نیز به نام جدول «شهرستان» نام‌گذاری می‌شوند. در گام بعدی می‌خواهیم این دو جدول را به کمک یک «رابطه یک به چند» (One to Many) به یکدیگر متصل کرده و یک گزارش از هر استان‌ و شهرستان‌های مربوط به آن تهیه کنیم.

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

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

فیلم آموزش جداول محوری در اکسل یا پیوت تیبل Pivot Tables + گواهینامه در فرادرس

کلیک کنید

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

دستور مربوط به ارتباط دو جدول در اکسل در برگه 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 نیز عبارت «مجموع جمعیت» را ثبت کرده‌ایم.

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

کلیک کنید

نکته: البته محاسبه جمعیت استان به کمک جدول شهرستان به تنهایی نیز قابل انجام بود. کافی بود با تابع Subtotal برای ستون جمعیت شهرستان، مجموع را محاسبه کرده و با فیلتر کردن هر استان، مجموع جمعیت آن استان را برحسب جمع جمعیت شهرستان، بدست می‌آوردیم.

خلاصه و جمع‌بندی

در این نوشتار به نحوه ایجاد بانک اطلاعاتی در اکسل آشنا شدیم. همانطور که دیدید، اگر ساختار «سطری و ستون» (Field- Record) را در کاربرگ‌ها ایجاد کنیم، یک بانک اطلاعاتی در اکسل بوجود آورده‌ایم.  در بخش دوم، به منظور استفاده از ابزارهای مرتب‌سازی و فیلتر کردن، این نواحی اطلاعاتی در کاربرگ‌ها را به صورت یک جدول در اکسل در آوردیم. همچنین در انتهای متن، به نحوه ارتباط و ایجاد رابطه یک به چند برای بانک اطلاعاتی در اکسل پرداختیم. تهیه گزارش از چنین بانک‌های اطلاعاتی رابطه‌ای نیز توسط جدول محوری مورد توجه قرار گرفته و روش ایجاد آن مرور و آموزش داده شد.

به این نکته نیز توجه داشته باشید که اکسل برای ثبت اطلاعات به صورت سطری، محدودیت داشته و قادر به ثبت فقط ۱٫048٫576 سطر است. در نتیجه برای ایجاد جدول‌هایی بزرگتر از این حد، بهتر است از بانک‌های اطلاعاتی دیگر مثل اکسس یا نرم‌افزارهای حرفه‌ای بانک اطلاعاتی یا پایگاه داده مثل اوراکل یا MySQL استفاده کرد.