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

۶۹۳۵ بازدید
آخرین به‌روزرسانی: ۱۶ خرداد ۱۴۰۲
زمان مطالعه: ۸ دقیقه
ایجاد بانک اطلاعاتی در اکسل — راهنمای کاربردی

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

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

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

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

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

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

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

نام استانمساحت استاننام مرکز استاننام استاندار
فارس100شیرازحمیدی
تهران۱۰۰تهرانقاسمی
مرکزی۱۰۰اراکشیرازی
اصفهان۱۰۰اصفهانجهاندار
خراسان جنوبی۴۵بیرجنداحمدی فر
مازندران۴۵ساریاکبرپور
بوشهر۴۵بوشهرشهشهانی

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

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

نام استاننام شهرستانجمعیت شهرستان
فارسکازرون200
فارسفسا۳۲۰
فارسشیراز۴۳۰۰
فارسآباده۴۰۰
مرکزیدلیجان۱۲۰
مرکزیمحلات۱۳۰
مرکزیاراک۲۴۰۰

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

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

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

create table in excel
تصویر ۱: ایجاد بانک اطلاعاتی در اکسل

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

tables in excel
تصویر ۲: جدول های بانک اطلاعاتی در اکسل برای استان و شهرستان

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

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

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

Name Manager
تصویر ۳: تغییر نام برای جدول‌های بانک اطلاعاتی در اکسل

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

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

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

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

دستور مربوط به ارتباط دو جدول در اکسل در برگه Data و در بخش Data Tools قرار گرفته است. کافی است روی دکمه Relationships کلیک کنید. تنظیمات را مطابق با تصویر ۴ برای دو جدول انجام می‌دهیم.

relations between tables
تصویر ۴: ایجاد رابطه بین دو جدول بانک اطلاعاتی در اکسل

در ادامه مطلب به کمک رابطه ایجاد شده، یک «گزارش محوری» (PivotTable) ایجاد می‌کنیم که پاسخ سوال مورد نظر (جمعیت هر استان) را محاسبه می‌کند.

ایجاد گزارش برای بانک اطلاعاتی در اکسل

در نوشتار دیگری از مجله فرادرس با ویژگی‌های گزارش محوری در اکسل ۲۰۱۹ آشنا شده‌اید. ولی در اینجا به علت آنکه با دو جدول در کاربرگ، یک رابطه ایجاد کرده‌ایم، از این رابطه یا مدل ارتباطی برای ساختن جدول محوری بهره خواهیم برد.

برای ایجاد جدول محوری، گام‌های زیر را بر می‌داریم.

  • سلول فعال را در یکی از جدول‌ها، قرار می‌دهیم.
  • از برگه Insert در بخش Tables، دستور PivotTable را اجرا می‌کنیم.
  • تنظیمات پنجره ظاهر شده را مطابق با تصویر ۵ انجام می‌دهیم.
pivot table dialog box
تصویر ۵: پنجره تعریف جدول محوری در اکسل
  • با فشردن دکمه OK با کاربرگی مطابق به تصاویر ۶ مواجه خواهیم شد و تنظیمات را مطابق با تصویر ۷، اجرا می‌کنیم.
  • نتیجه اجرای جدول محوری را بر طبق سلیقه خود با گزینه‌های بخش style، رنگ‌ آمیزی کرده و به دلخواه خود در می‌آوریم.

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

PivotTable Field
تصویر ۶: معرفی فیلدهای جدول محوری برای بانک اطلاعاتی در اکسل

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

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

Field settings in PivotTable
تصویر ۷: پنجره تنظیمات جدول محوری برای نمایش جمع در هر استان

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

برای آنکه این جمع را در ابتدا یا بخش سرگروه هر استان مشاهده کنیم، از برگه دوم یعنی (Layout & Print) کمک خواهیم گرفت. اگر گزینه‌های این پنجره را مطابق با تصویر ۸ انجام دهید، مجموع جمعیت برای هر استان، در سطر مربوط به هر استان، ظاهر خواهد شد و ما در نهایت به پاسخ مورد نظر خواهیم رسید.

Field settings Dialog Box
تصویر ۸: پنجره تنظیمات برای چاپ و چیدمان جدول محوری

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

pivot table report
تصویر ۹: گزارش نهایی جدول محوری از دو جدول مرتبط

به این موضوع نیز توجه داشته باشید که هر یک از برچسب‌های مربوط به جدول محوری را می‌توانید ویرایش کرده و به دلخواه خود در آورید. برای مثال در جدول مربوط به تصویر ۹، مقدار سلول در آدرس A3 به نام «استان/استاندارد» تغییر یافته است. همچنین در سلول B3 نیز عبارت «مجموع جمعیت» را ثبت کرده‌ایم.

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

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

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

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

بر اساس رای ۲۲ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
مجله فرادرس
۱ دیدگاه برای «ایجاد بانک اطلاعاتی در اکسل — راهنمای کاربردی»

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

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *