پرس و جو SQL در مایکروسافت اکسل
SQL یک زبان برنامه نویسی محبوب است که در هنگام کار با پایگاه های داده (DB) استفاده می شود. اگر چه یک برنامه جداگانه برای عملیات پایگاه داده در مایکروسافت آفیس - دسترسی وجود دارد، اما اکسل همچنین می تواند با پایگاه داده کار کند و پرس و جوهای SQL را انجام دهد. بیایید ببینیم چگونه می توان یک پرس و جو مشابه را با روش های مختلف ایجاد کرد.
همچنین ببینید: چگونه برای ایجاد یک پایگاه داده در اکسل
محتوا
ایجاد یک پرس و جو SQL در اکسل
زبان پرس و جو SQL از آنالوگ ها متفاوت است در این حقیقت تقریبا تمام سیستم های مدیریت پایگاه داده مدرن با آن کار می کنند. بنابراین، کاملا تعجب آور نیست که چنین پردازشگر جدولی به عنوان Excel، که دارای بسیاری از توابع اضافی است، می تواند با این زبان نیز کار کند. کاربرانی که مهارت استفاده از SQL را با استفاده از اکسل می توانند بسیاری از جداول جداگانه جداگانه را سازماندهی کنند.
روش 1: از افزودنیها استفاده کنید
اما ابتدا، گزینه ای را در نظر بگیرید که می توانید یک query SQL از اکسل بدون استفاده از ابزار استاندارد ایجاد کنید، اما با استفاده از یک افزودنی شخص ثالث. یکی از بهترین افزونه هایی که این کار را انجام می دهند، مجموعه ابزار XLTools است که علاوه بر این ویژگی، میزکار دیگری را نیز فراهم می کند. با این حال، لازم به ذکر است که دوره آزاد استفاده از ابزار فقط 14 روز است و پس از آن باید مجوز خریداری کنید.
- پس از دانلود فایل افزودنی xltools.exe ، باید نصب آن را ادامه دهید. برای شروع نصب، روی دکمه سمت چپ ماوس روی پرونده نصب دوبار کلیک کنید. پس از آن یک پنجره راه اندازی خواهد شد که در آن شما باید موافقت خود را با توافقنامه مجوز برای استفاده از محصولات مایکروسافت - NET Framework 4 تأیید کنید. برای انجام این کار، فقط روی دکمه «پذیرش» در پایین پنجره کلیک کنید.
- پس از آن، نصب کننده فایل های مورد نیاز را بارگیری می کند و فرآیند نصب را آغاز می کند.
- بعد، یک پنجره باز می شود که در آن شما باید رضایت خود را برای نصب این افزونه تأیید کنید. برای انجام این کار روی دکمه «نصب» کلیک کنید.
- سپس فرایند نصب را مستقیما به افزونه خود آغاز می کند.
- پس از اتمام آن، یک پنجره باز خواهد شد که در آن گزارش می شود که نصب با موفقیت انجام شد. در پنجره مشخص شده، فقط روی دکمه «بستن» کلیک کنید.
- افزونه نصب شده است و حالا شما می توانید فایل اکسل را اجرا کنید که در آن شما نیاز دارید یک پرس و جو SQL را سازماندهی کنید. همراه با صفحه اکسل یک پنجره برای ورود به کد مجوز XLTools باز می شود. اگر کد دارید، باید آن را در فیلد مناسب وارد کنید و روی دکمه "OK" کلیک کنید. اگر می خواهید از نسخه رایگان برای 14 روز استفاده کنید، پس فقط باید روی دکمه "پروانه آزمایشی" کلیک کنید.
- وقتی یک مجوز آزمایشی را انتخاب می کنید، یک پنجره کوچک دیگر باز می شود که در آن شما نیاز دارید که نام و نام خانوادگی خود را (می توانید از نام مستعار) و ایمیل استفاده کنید. پس از آن، روی دکمه "شروع دوره آزمایشی" کلیک کنید .
- بعد ما به پنجره مجوز بازگشتیم. همانطور که می بینید، مقادیری که وارد کرده اید قبلا نمایش داده می شود. حالا شما فقط باید بر روی دکمه "OK" کلیک کنید.
- پس از انجام دستکاری های فوق، یک برگه جدید، "XLTools"، در کپی اکسل ظاهر می شود. اما عجله برای رفتن به آن نیست. قبل از اینکه یک پرس و جو ایجاد کنید، شما باید یک آرایه جدول را که با آن کار می کنید را تبدیل به یک جدول به اصطلاح "هوشمند" تبدیل کنید و نام آن را بدهید.
برای انجام این کار آرایه مشخص شده یا هر عنصر آن را انتخاب کنید. در حالی که در تب "Home" روی نماد "Format as table" کلیک کنید. این روبان در جعبه ابزار Styles قرار دارد . پس از آن، یک لیست از سبک های مختلف باز می شود. سبک مورد نظر خود را انتخاب کنید. این انتخاب بر روی عملکرد جدول تاثیر نمی گذارد، بنابراین انتخاب خود را فقط بر اساس تنظیمات صفحه نمایش بصری تنظیم کنید. - به دنبال این، یک پنجره کوچک راه اندازی می شود. این مختصات جدول را نشان می دهد. به عنوان یک قاعده، برنامه خود را "آدرس کامل آرایه" را بر می گرداند، حتی اگر تنها یک سلول را در آن انتخاب کنید. اما فقط در مورد، آن را با چک کردن اطلاعات موجود در زمینه دخالت نمی کند "تعیین محل داده های جدول" . همچنین اگر حقیقت در آرایه شما وجود داشته باشد، باید به جعبه کنار "Item Table with headers" توجه کنید. سپس بر روی دکمه "OK" کلیک کنید.
- پس از آن، کل محدوده مشخص شده به عنوان یک جدول قالب بندی می شود، که بر روی هر دو ویژگی آن (مثلا کشش) و صفحه نمایش بصری تاثیر می گذارد. جدول مشخص شده نامگذاری خواهد شد. برای به رسمیت شناختن آن و تغییر آن به صورت اراده، ما بر روی هر عنصر آرایه کلیک میکنیم. یک گروه اضافی از زبانه ها روی نوار نشان داده می شود - "کار با جداول" . به تب "طراح" ، واقع در آن بروید. در نوار در نوار ابزار «Properties» در قسمت «نام جدول» ، نام آرایه ای که برنامه به طور خودکار به آن اختصاص داده می شود، نشان داده می شود.
- اگر مورد نظر، کاربر می تواند این نام را به یک اطلاعات آموزنده تر تغییر دهد به سادگی با وارد کردن گزینه دلخواه به فیلد از صفحه کلید و فشار دادن کلید Enter .
- پس از آن، جدول آماده است و شما می توانید به طور مستقیم به سازمان درخواست. به زبانه "XLTools" بروید.
- پس از انتقال به نوار در بلوک ابزار "SQL Query ها" روی نماد "Run SQL" کلیک کنید.
- پنجره اجرای SQL query شروع می شود. در قسمت چپ خود، ورق سند و جدول موجود در درخت داده که پرس و جو از آن تشکیل می شود را مشخص کنید.
در پنجره سمت راست پنجره، که اکثر آن را اشغال می کند، خود SQL Editor است. لازم است کد برنامه را بنویسید. نام ستونهای جدول انتخاب شده در اینجا به طور خودکار نمایش داده می شود. انتخاب ستون ها برای پردازش با استفاده از دستور SELECT انجام می شود. لازم است تنها ستون هایی را که می خواهید فرمان مشخص شده را پردازش کنید در لیست قرار دهید.
بعد، متن فرمان را که می خواهید در مورد اشیاء انتخاب شده اعمال کنید، بنویسید. دستورات با استفاده از اپراتورهای خاص ساخته می شوند. اظهارات اساسی SQL عبارتند از:
- ORDER BY - مقادیر مرتب سازی؛
- JOIN - پیوستن به جداول؛
- GROUP BY - گروه بندی ارزش ها؛
- SUM - جمع بندی ارزش ها؛
- DISTINCT - حذف تکراری
علاوه بر این، در ساخت یک پرس و جو می توانید از اپراتورهای MAX ، MIN ، AVG ، COUNT ، LEFT و غیره استفاده کنید.
در قسمت پایین پنجره، باید دقیقا مشخص کنید که کدام نتیجه پردازش نمایش داده خواهد شد. این می تواند یک برگه جدید کتاب (به طور پیش فرض) یا یک محدوده خاص در صفحه فعلی باشد. در مورد دوم، شما باید سوئیچ را به موقعیت مناسب تغییر دهید و مختصات این محدوده را مشخص کنید.
پس از درخواست ساخته شده و تنظیمات مربوطه ساخته شده است، بر روی دکمه «اجرای» در پایین پنجره کلیک کنید. بعد از آن عملیات وارد شده انجام خواهد شد.
درس: جداول هوشمند در اکسل
روش 2: از ابزارهای Excel ساخته شده استفاده کنید
همچنین یک راه برای ایجاد یک پرس و جو SQL برای یک منبع داده انتخاب شده با استفاده از ابزار ساخته شده در اکسل وجود دارد.
- اجرای برنامه اکسل پس از آن، به تب "Data" بروید.
- در جعبه ابزار "Get External Data" که بر روی روبان قرار دارد، روی نماد "از سایر منابع" کلیک کنید. لیستی از گزینه های بیشتر مورد "از جادوگر اتصال داده ها" در آن را انتخاب کنید .
- جادوگر اتصال داده ها شروع می شود . در لیست انواع منبع داده، "ODBC DSN" را انتخاب کنید. پس از آن روی دکمه «بعدی» کلیک کنید.
- پنجره Data Connection Wizard باز می شود که در آن شما باید نوع منبع را انتخاب کنید. نام "MS Access Database" را انتخاب کنید . سپس بر روی دکمه "بعدی" کلیک کنید.
- یک پنجره ناوبری کوچک باز می شود که در آن باید به دایرکتوری محل پایگاه داده در فرمت mdb یا accdb بروید و فایل پایگاه داده مورد نیاز را انتخاب کنید. ناوبری بین درایوهای منطقی در قسمت ویژه "دیسک" انجام می شود . بین دایرکتوری ها، در منطقه مرکزی پنجره "راهنماها" ایجاد می شود . فایل های موجود در دایرکتوری فعلی در صفحه سمت چپ نمایش داده می شوند اگر آنها دارای MDB یا accdb هستند. این در این منطقه است که شما نیاز به انتخاب نام فایل، و سپس بر روی دکمه "OK" کلیک کنید.
- به دنبال این، یک پنجره برای انتخاب یک جدول در پایگاه داده مشخص شده راه اندازی می شود. در قسمت مرکزی، نام جدول دلخواه را انتخاب کنید (اگر چندین وجود دارد)، و سپس روی دکمه «بعدی» کلیک کنید.
- پس از آن پنجره پنجره ذخیره اطلاعات اتصال باز می شود. در اینجا اطلاعات اتصال پایه ای است که ما پیکربندی شده ایم. در این پنجره، فقط روی دکمه "پایان" کلیک کنید.
- در صفحه اکسل یک پنجره وارد کردن اطلاعات راه اندازی می شود. این امکان وجود دارد که در کدام فرم شما می خواهید اطلاعات ارائه شود:
- جدول ؛
- گزارش جدول محوری
- نمودار خلاصه
گزینه مورد نظر خود را انتخاب کنید فقط در زیر شما باید دقیقا مشخص کنید که کدام داده ها را قرار دهید: در یک ورق جدید یا در ورق کنونی. در مورد دوم، همچنین می توانید مختصات مکان را انتخاب کنید. به طور پیش فرض، داده ها بر روی صفحه فعلی قرار می گیرند. گوشه بالا سمت چپ شی وارد شده در سلول A1 قرار می گیرد.
بعد از اینکه تمام تنظیمات وارد شده مشخص شد، روی دکمه OK کلیک کنید.
- همانطور که می بینید، جدول از پایگاه داده به ورق منتقل می شود. سپس ما به تب "Data" بروید و بر روی دکمه "Connections" کلیک کنید، که روی نوار در جعبه ابزار با همین نام قرار می گیرد.
- پس از آن، اتصال به کتاب راه اندازی شده است. در آن ما نام پایگاه داده متصل شده قبلی را می بینیم. اگر چند پایگاه داده متصل وجود داشته باشد، یکی از مواردی را که لازم دارید انتخاب کنید و آن را انتخاب کنید. بعد از آن روی دکمه Properties ... در قسمت راست پنجره کلیک کنید.
- پنجره ویژگی های اتصال شروع می شود. آن را به تب "تعریف" انتقال دهید . در قسمت "متن فرمان" در پایین پنجره فعلی، دستور SQL را مطابق با نحو این زبان بنویسیم، که ما در مورد روش 1 به طور خلاصه صحبت کردیم. سپس بر روی دکمه "OK" کلیک کنید.
- پس از آن، یک بازگشت خودکار به پنجره اتصال کتاب انجام می شود. ما فقط می توانیم بر روی دکمه "به روز رسانی" در آن کلیک کنید. پایگاه داده با پرس و جو قابل دسترسی است، پس از آن پایگاه داده نتایج پردازش خود را به صفحه اکسل بازگرداند، به جدول قبلی که توسط ما منتقل شده است.
روش 3: اتصال به SQL Server
علاوه بر این، از طریق ابزار اکسل، امکان اتصال به SQL Server و ارسال درخواست به آن وجود دارد. ساخت یک پرس و جو از گزینه قبلی متفاوت نیست، اما اول از همه شما باید ارتباط خود را ایجاد کنید. بیایید ببینیم چگونه این کار را انجام دهیم
- اجرای برنامه اکسل و رفتن به تب "داده" . پس از آن، بر روی دکمه "از دیگر منابع" کلیک کنید، که بر روی روبان در جعبه ابزار "دریافت اطلاعات خارجی" قرار می گیرد . این بار از لیست کشویی گزینه "From SQL Server" را انتخاب کنید .
- اتصال به سرور پایگاه داده باز می شود. در قسمت «نام سرور» نام سرور ما را نشان می دهد که ما با آن ارتباط برقرار می کنیم. در گروه پارامترهای حسابداری، شما باید تصمیم بگیرید که چگونه ارتباط برقرار خواهد شد: با استفاده از احراز هویت ویندوز یا با وارد کردن یک نام کاربری و رمز عبور. ما سوئیچ را مطابق با تصمیم گیری قرار می دهیم. اگر گزینه دوم را انتخاب کرده اید، سپس علاوه بر فیلدهای مربوطه، شما باید یک نام کاربری و رمز عبور را وارد کنید. پس از تمام تنظیمات ساخته شده، بر روی دکمه "بعدی" کلیک کنید. پس از انجام این عمل، اتصال به سرور مشخص شده رخ می دهد. اقدامات بعدی برای سازماندهی یک پرس و جو پایگاه داده شبیه آنچه که در روش قبلی توصیف شده است.
همانطور که می بینید، در اکسل، پرس و جو SQL می تواند به عنوان با ابزار ساخته شده در برنامه سازماندهی شده و با کمک افزونه های شخص ثالث. هر کاربر می تواند گزینه ای را انتخاب کند که برای او مناسب تر است و برای حل یک کار خاص مناسب تر است. اگر چه، قابلیت های افزودنی XLTools، به طور کلی، هنوز هم تا حدودی پیشرفته تر از ابزار ساخته شده در اکسل است. ضعف اصلی XLTools این است که دوره استفاده آزاد از افزودنی محدود به تنها دو هفته تقویمی است.