ایجاد بانک اطلاعاتی در اکسل و اتصال آن به دیتابیس (و نکات)

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

برای اطمینان از در امان ماندن محتویات پایگاه داده تان، این اتصال باید با ورود به سیستم SQL تأیید شود. این اتصال را می توان برای به تجدید یا Refresh کردن محتوا از پایگاه داده SQL دوباره به وجود آورد.

حتما دانلود کنید: آموزش صفر تا صد اکسل با 20 درس رایگان 

چه طور یک اتصال اکسل ایجاد کنیم؟

• مایکروسافت اکسل را باز کنید.
• تب Data را انتخاب کنید.

• تب Data را در اکسل انتخاب کنید.
• بر روی گزینه Get Data کلیک کنید.
  • “From Database ” را انتخاب کنید.
• از پایگاه داده، SQL Server را انتخاب نمایید.
• نام سرور SQL را وارد کنید.
  • در صورت تمایل می‌توانید نام پایگاه داده را در اینجا وارد کنید.
  • در غیر این صورت، در مرحله بعدی می توانید پایگاه داده را انتخاب کنید.

• اعتبارنامه ای که قرار است استفاده کنید را مشخص نمایید.
  • “Use my current credentials” گزینه پیش فرض است. با این انتخاب از قسمت لاگین ویندوز تان که برای اتصال به ویندوز استفاده کرده اید، استفاده می شود.
  • “Use Alternate Credentials” از لاگین ویندوزِ متفاوتی نسبت به گزینه فعلی استفاده می کند.
  • برای استفاده از SQL Database Credentials، گزینه Database را در سمت چپ انتخاب کنید.
  • برای استفاده از لاگین Microsoft365، گزینه account Microsoft را در سمت چپ انتخاب کنید.

• روی گزینه Connect کلیک کنید.
• پایگاه داده و جدولی که می خواهید برای پرس و جو یا همان کوئری (Query) استفاده کنید را انتخاب کنید.
  • برای جستجوی جدول می توانید از نوار جستجو کمک بگیرید .
  • می ‌توانید کادر “Select Multiple Items”را علامت بزنید تا بیش از یک جدول اضافه شود.

• حالا می توانید:

1. نحوه بارگیری یا Load داده ها را انتخاب کنید؛ یعنی یا Load که داده ها را در جدول اکسل قرار می دهد و یا Load to که به شما امکان می دهد تا داده ها را به جای جدول به عنوان یک کوئری بارگیری کنید. “Load To” از نظر گزارش گیری پیچیده تر است.
2. شما می توانید انتقال داده ها یا “Transform Data ” را انجام دهید؛ این گزینه ویرایشگر Power Query Microsoft را باز می کند و به شما امکان می دهد تا فیلد ها را اضافه یا حذف کنید یا نوع داده ها را تغییر داده و کلی کار دیگر انجام دهید.

• بعد از کلیک بر روی گزینه “Load” ؛ داده ها در یک جدول اکسل بارگذاری می شود.

• دانلود آموزش صفر تا صد ورد
• دانلود آموزش صفر تا صد پاورپوینت
• دانلود آموزش صفر تا صد اکسس

نحوه تجدید اتصال اکسل به پایگاه داده SQL

برای تجدید اتصال اکسل:

• تب Data را انتخاب کنید.
• Refresh را انتخاب کنید.
• Refresh: کوئری فعلی که در آن هستید را تجدید می کند.
• Refresh All: همه پرس و جو های موجود در Workbook را تجدید می کند.

این موارد رو بلد باش!

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

مفاهیم اولیه

1. بانک اطلاعاتی (Database): مجموعه‌ای از داده‌ها که به صورت سازمان‌دهی شده ذخیره و مدیریت می‌شوند. در اکسل، جداول داده‌ها به صورت ساده می‌توانند نقش بانک اطلاعاتی را ایفا کنند.

2. دیتابیس خارجی: پایگاه داده‌ای مثل SQL Server، MySQL، یا Access که به صورت حرفه‌ای برای مدیریت حجم زیادی از داده‌ها استفاده می‌شود.

3. 

   با این آموزش اکسل صفر تا صد اکسل، رو توی کمترین زمان ممکن یاد بگیر.بهترین پک آموزش اکسل در ایران همین الان خرید و دانلود کنید!

   اتصال به دیتابیس: با استفاده از ابزارهای داخلی اکسل یا کدنویسی (VBA)، می‌توانید اکسل را به یک دیتابیس متصل کنید.

مرحله ۱: ایجاد بانک اطلاعاتی در اکسل

1. طراحی جدول داده‌ها:

   • ستون‌های جدول باید نمایانگر ویژگی‌های داده باشند.
   • مثال:
     | شماره کارمند | نام | بخش | حقوق | |--------------|-----------|-----------|------| | 1 | علی | فروش | 5000 | | 2 | زهرا | بازاریابی | 6000 |

2. قالب‌بندی جدول:

   • تمام داده‌ها را انتخاب کنید.
   • به تب Insert بروید و روی Table کلیک کنید.
   • مطمئن شوید گزینه "My table has headers" انتخاب شده باشد.

3. نام‌گذاری جدول:

   • روی جدول کلیک کنید.
   • در تب Table Design، یک نام مناسب (مثل EmployeeData) به جدول بدهید.

مرحله ۲: اتصال اکسل به یک دیتابیس خارجی

۱. پیش‌نیازها

• نصب و راه‌اندازی یک دیتابیس خارجی (مثل MySQL، SQL Server، یا Access).
• درایور ODBC مربوط به دیتابیس شما نصب شده باشد.

۲. اتصال به پایگاه داده

1. ابزار Get Data در اکسل:

   • به تب Data بروید و روی Get Data کلیک کنید.
   • مسیر زیر را دنبال کنید:
     Get Data > From Database > From SQL Server Database
     یا From Other Sources > From ODBC (برای دیتابیس‌های دیگر مثل MySQL).

2. وارد کردن اطلاعات اتصال:

   • نام سرور و دیتابیس را وارد کنید.
   • اگر نیاز به احراز هویت دارید، نام کاربری و رمز عبور را وارد کنید.

3. انتخاب جداول:

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

مرحله ۳: ارسال داده‌ها از اکسل به دیتابیس خارجی

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

ارسال با استفاده از VBA:

1. فعال‌سازی کتابخانه‌های VBA:

   • در محیط VBA (Alt + F11)، به Tools > References بروید.
   • کتابخانه Microsoft ActiveX Data Objects (ADO) را فعال کنید.

2. نمونه کد برای ارسال داده‌ها به دیتابیس:

   Sub InsertDataToDatabase() Dim conn As Object Dim rs As Object Dim connStr As String Dim sqlQuery As String ' ایجاد اتصال به پایگاه داده connStr = "Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;User ID=USERNAME;Password=PASSWORD;" Set conn = CreateObject("ADODB.Connection") conn.Open connStr ' ساخت دستور SQL برای درج داده‌ها sqlQuery = "INSERT INTO TableName (Column1, Column2) VALUES ('Value1', 'Value2')" conn.Execute sqlQuery ' بستن اتصال conn.Close Set conn = Nothing End Sub

   در کد بالا:

   • SERVER_NAME: نام یا آدرس سرور دیتابیس.
   • DATABASE_NAME: نام دیتابیس.
   • TableName: نام جدول در دیتابیس.

مرحله ۴: بروزرسانی خودکار داده‌ها در اکسل

۱. اتصال پویا به دیتابیس:

• با استفاده از ابزار Query Editor در اکسل، می‌توانید داده‌ها را به‌صورت پویا بروزرسانی کنید.

۲. ماکرو برای بروزرسانی خودکار:

• کدی بنویسید که داده‌ها را به‌طور خودکار از دیتابیس دریافت کند:

• این ماکرو تمام اتصالات داده‌ای در اکسل را بروزرسانی می‌کند.

نکات و اصول مهم

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

2. امنیت داده‌ها: یادتان باشد برای اتصال به دیتابیس از نام کاربری و رمز عبور امن استفاده کنید. حتما حتما اطلاعات حساس را رمزنگاری کنید.

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

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

آموزش کدنویسی در اکسل از صفر تا صد با 9 درس رایگان