در این صفحه به مقالات آموزشی تب Data درسترسی خواهید داشت، برای دیدن مقالات تب مورد نظر را انتخاب کنید تا لینکهای مرتبط در دسترس شما باشند.
گزینه Get Data در گروه Get & Transform Data از تب Data اکسل، نقطه شروع ورود به دنیای Power Query است؛ جایی که میتوان دادهها را از منابع مختلف وارد کرد، آنها را پاکسازی و تبدیل نمود، و در نهایت برای تحلیل آماده ساخت.
🔍 مقدمهای بر Get Data در اکسل
گزینه Get Data به شما این امکان را میدهد که دادهها را از منابع متنوعی مانند فایلهای Excel، CSV، پایگاههای داده، وبسایتها، پوشهها و سرویسهای ابری وارد محیط اکسل کنید. این دادهها ابتدا وارد محیط Power Query میشوند، جایی که میتوانید آنها را فیلتر، گروهبندی، ادغام، تقسیم و پاکسازی کنید، سپس به شیت اکسل منتقل نمایید.
📊 جدول منابع قابل اتصال از طریق Get Data
| نوع منبع | مسیر اتصال | توضیحات |
|---|---|---|
| Excel Workbook | Get Data > From File > From Workbook | وارد کردن داده از فایلهای اکسل دیگر |
| Text/CSV | Get Data > From File > From Text/CSV | مناسب برای فایلهای خروجی نرمافزارها یا دیتابیسها |
| Folder | Get Data > From File > From Folder | ترکیب چند فایل مشابه در یک پوشه (مثلاً چند گزارش فروش ماهانه) |
| Web | Get Data > From Other Sources > From Web | وارد کردن داده از صفحات وب یا APIها |
| SQL Server | Get Data > From Database > From SQL Server | اتصال مستقیم به پایگاه داده سازمانی |
| SharePoint Folder | Get Data > From Online Services > From SharePoint Folder | دریافت داده از منابع ابری سازمانی |
| Azure Services | Get Data > From Azure | اتصال به منابع داده در فضای ابری مایکروسافت |
| Recent Sources | Get Data > Recent Sources | دسترسی سریع به منابعی که اخیراً استفاده شدهاند |
⚙️ ویژگیهای کلیدی Get Data
- اتصال مستقیم و پایدار به منابع داده
- قابلیت بهروزرسانی خودکار دادهها با یک کلیک (Refresh)
- پیشنمایش دادهها قبل از وارد کردن
- ورود به محیط Power Query Editor برای پاکسازی و تبدیل دادهها
- پشتیبانی از منابع داده پیچیده و چندگانه
🎯 کاربردهای عملی
- ساخت داشبوردهای فروش با دادههای ماهانه از پوشهها
- اتصال به دیتابیس SQL برای گزارشگیری لحظهای
- وارد کردن دادههای قیمت ارز از وبسایتهای مالی
- ترکیب چند فایل CSV برای تحلیل جامع
گروه Queries & Connections در تب Data اکسل، مرکز مدیریت تمام پرسوجوها (queries) و اتصالهای دادهای (connections) است که از طریق Power Query یا منابع خارجی وارد فایل اکسل شدهاند. این گروه برای بررسی، ویرایش، بهروزرسانی و حذف دادههای واردشده استفاده میشود.
🔗 جدول گزینههای گروه Queries & Connections در اکسل
| گزینه | کاربرد | توضیحات |
|---|---|---|
| Queries & Connections | نمایش پنل مدیریت | با کلیک روی این گزینه، پنلی در سمت راست باز میشود که تمام پرسوجوها و اتصالهای فعال در فایل را نشان میدهد. |
| Refresh All | بهروزرسانی همه دادهها | تمام دادههایی که از منابع خارجی وارد شدهاند (مثل فایل CSV، پایگاه داده، وب) را با یک کلیک بهروزرسانی میکند. |
| Properties (در پنل) | تنظیمات پرسوجو | با کلیک روی هر پرسوجو در پنل، میتوانید نام، توضیح، زمانبندی بهروزرسانی و سایر ویژگیها را تنظیم کنید. |
| Edit (در پنل) | ویرایش پرسوجو | باز کردن Power Query Editor برای اعمال تغییرات روی دادهها (فیلتر، گروهبندی، ادغام و…). |
| Delete (در پنل) | حذف پرسوجو یا اتصال | حذف کامل پرسوجو یا اتصال از فایل اکسل. این کار دادههای واردشده را نیز حذف میکند. |
🧠 نکات کاربردی:
- اگر چندین منبع داده در فایل دارید، پنل Queries & Connections به شما دید کامل و ساختاریافته میدهد.
- گزینه Refresh All برای داشبوردهای پویا بسیار حیاتی است، چون با یک کلیک تمام دادهها را بهروز میکند.
- از طریق Properties میتوانید تنظیم کنید که دادهها هنگام باز شدن فایل بهصورت خودکار بهروزرسانی شوند.
- اگر دادهای از منبعی مثل SQL Server یا وب وارد کردهاید، اتصال آن در بخش Connections نمایش داده میشود.
🎯 کاربردهای عملی:
- مدیریت چند پرسوجو در یک فایل مالی یا فروش
- بررسی سریع وضعیت اتصال به منابع خارجی
- بهروزرسانی خودکار دادهها در گزارشهای هفتگی یا ماهانه
- حذف پرسوجوهای قدیمی برای سبکسازی فایل
🎓 سناریوی آموزشی: مدیریت دادههای آموزش اکسل با Queries & Connections
فرض کن یک فایل اکسل داری که شامل دادههای زیره:
| نام دوره | مدرس | تاریخ شروع | تعداد جلسات | سطح | قیمت |
|---|---|---|---|---|---|
| اکسل مقدماتی | احمدی | 1404/01/15 | 8 | مبتدی | 950,000 |
| اکسل پیشرفته | رضایی | 1404/02/01 | 10 | حرفهای | 1,450,000 |
| داشبوردسازی | کریمی | 1404/03/10 | 6 | متوسط | 1,200,000 |
🧭 مراحل استفاده از Queries & Connections
| مرحله | ابزار | توضیح |
|---|---|---|
| وارد کردن دادهها | Get Data > From Workbook | دادهها رو از فایلهای مختلف اکسل یا CSV وارد کن. هر بار یک Query جدید ساخته میشه. |
| ویرایش پرسوجوها | Queries & Connections > Edit | با کلیک روی هر پرسوجو، وارد Power Query میشی و میتونی ستونها رو فیلتر، گروهبندی یا اصلاح کنی. مثلاً فقط دورههای سطح “حرفهای” رو نمایش بدی. |
| ترکیب دادهها | Combine > Append Queries | اگر چند فایل آموزشی داری، میتونی همه رو با Append ترکیب کنی و یک جدول جامع بسازی. |
| بهروزرسانی دادهها | Refresh All | اگر فایلهای منبع تغییر کنن (مثلاً قیمتها)، با یک کلیک همه دادهها بهروز میشن. |
| مدیریت اتصالها | Properties | میتونی تنظیم کنی که دادهها هنگام باز شدن فایل بهصورت خودکار بهروزرسانی بشن یا فقط دستی. |
| حذف پرسوجوهای قدیمی | Delete | اگر دورهای حذف شده، میتونی پرسوجوی مربوط به اون رو حذف کنی تا فایل سبکتر بشه. |
📌 نکته حرفهای:
اگر دادههای آموزشی از منابع مختلف مثل فرمهای ثبتنام، فایلهای CSV یا گزارشهای مالی میان دوره یا استفاده از Queries & Connections باشه، باعث میشه همه چیز منظم، قابل کنترل و قابل بهروزرسانی باشه.
گروه Sort & Filter در تب Data اکسل ابزارهایی را برای مرتبسازی و فیلتر کردن دادهها فراهم میکند که به شما کمک میکند اطلاعات را سریعتر تحلیل، دستهبندی و استخراج کنید. این گروه برای مدیریت دادههای حجیم و جداول کاربردی بسیار حیاتی است.
🔍 جدول گزینههای گروه Sort & Filter در اکسل
| گزینه | کاربرد | توضیحات |
|---|---|---|
| Sort Ascending (A → Z) | مرتبسازی صعودی | دادهها را از کوچک به بزرگ یا از A تا Z مرتب میکند. مناسب برای اعداد، تاریخها یا متن. |
| Sort Descending (Z → A) | مرتبسازی نزولی | دادهها را از بزرگ به کوچک یا از Z تا A مرتب میکند. |
| Sort | مرتبسازی پیشرفته | باز کردن پنجره تنظیمات برای مرتبسازی چندسطحی (مثلاً ابتدا بر اساس نام، سپس تاریخ). امکان انتخاب ستون، نوع داده، ترتیب و رنگ سلول وجود دارد. |
| Filter | فعالسازی فیلتر | افزودن دکمههای فیلتر به سرستونها برای انتخاب دادههای خاص. میتوان بر اساس مقدار، رنگ، شرایط عددی یا متنی فیلتر کرد. |
| Clear | حذف فیلترها | پاکسازی تمام فیلترهای اعمالشده و نمایش کامل دادهها. |
| Reapply | اعمال مجدد فیلتر | در صورت تغییر دادهها، این گزینه فیلترهای قبلی را دوباره اعمال میکند. |
🎯 نکات حرفهای برای استفاده از Sort & Filter
- برای مرتبسازی چندمرحلهای، همیشه از گزینه Sort استفاده کنید و سطوح مختلف را اضافه نمایید.
- فیلتر کردن بر اساس رنگ سلول یا فونت برای داشبوردهای رنگی بسیار کاربردی است.
- اگر دادهها تغییر کردهاند، با Reapply فیلترها را بهروز کنید.
- برای حذف کامل فیلترها و نمایش همه دادهها، از Clear استفاده کنید.
گروه Data Tools در تب Data اکسل مجموعهای از ابزارهای قدرتمند برای مدیریت، اصلاح و تحلیل دادههاست. این گروه به شما کمک میکند دادههای تکراری را حذف کنید، ستونها را تقسیم یا ترکیب کنید، اعتبارسنجی انجام دهید و دادهها را بهصورت حرفهای کنترل کنید.
🧰 جدول گزینههای گروه Data Tools در اکسل
| گزینه | کاربرد | توضیحات |
|---|---|---|
| Text to Columns | تقسیم متن به چند ستون | دادههای متنی را بر اساس جداکننده (مثل کاما، فاصله، یا تب) به چند ستون تقسیم میکند. بسیار کاربردی برای فایلهای CSV یا دادههای واردشده از فرمها. |
| Flash Fill | پر کردن هوشمند | با نوشتن الگوی دلخواه در یک سلول، اکسل بهصورت خودکار الگو را در سلولهای دیگر اعمال میکند. مناسب برای استخراج نام، تاریخ، کد و… |
| Remove Duplicates | حذف دادههای تکراری | ردیفهای تکراری را بر اساس یک یا چند ستون حذف میکند. برای پاکسازی دادههای ثبتنام، فروش یا لیست مشتریان بسیار مفید است. |
| Data Validation | اعتبارسنجی دادهها | محدود کردن ورودی سلولها به نوع خاصی از داده (مثلاً فقط عدد، تاریخ، یا لیست کشویی). مناسب برای فرمهای ورود اطلاعات و داشبوردهای تعاملی. |
| Consolidate | تجمیع دادهها | ترکیب دادهها از چند محدوده یا شیت مختلف با استفاده از توابعی مثل SUM، AVERAGE، COUNT. مناسب برای گزارشهای چندمنبعی. |
| Relationships | ایجاد ارتباط بین جداول | تعریف روابط بین جداول مختلف برای استفاده در مدلهای داده و Pivot Table پیشرفته. مشابه عملکرد Power BI. |
🎯 نکات حرفهای برای استفاده از Data Tools
- برای فایلهایی که از منابع مختلف وارد شدهاند، ابتدا از Remove Duplicates و Text to Columns استفاده کن تا دادهها تمیز شوند.
- Flash Fill ابزاری سریع و هوشمند برای تبدیل دادههای خام به ساختار دلخواه است، بدون نیاز به فرمولنویسی.
- با Data Validation میتونی فرمهای حرفهای بسازی که فقط دادههای معتبر قبول میکنن.
- اگر چند جدول مرتبط داری، با Relationships میتونی مدل دادهای بسازی و تحلیلهای چندبعدی انجام بدی.
گروه Forecast در تب Data اکسل ابزارهایی برای پیشبینی روندهای آینده بر اساس دادههای گذشته فراهم میکند. این ابزارها بهویژه برای تحلیلهای مالی، فروش، بودجهبندی و برنامهریزی بسیار کاربردی هستند.
📈 مقدمهای بر Forecast در اکسل
گروه Forecast از قابلیتهای آماری اکسل استفاده میکند تا با تحلیل دادههای تاریخی، روند آینده را تخمین بزند. این پیشبینیها بر پایه مدلهای رگرسیون خطی یا هموارسازی نمایی انجام میشوند و بهصورت نمودار یا جدول قابل نمایش هستند.
🧮 جدول گزینههای گروه Forecast در تب Data
| گزینه | کاربرد | توضیحات |
|---|---|---|
| Forecast Sheet | ایجاد شیت پیشبینی | با انتخاب یک سری زمانی (مثلاً فروش ماهانه)، اکسل یک نمودار و جدول پیشبینی برای دورههای آینده ایجاد میکند. |
| Create Forecast Worksheet | ساخت شیت پیشبینی | مشابه Forecast Sheet، اما با تنظیمات بیشتر مثل انتخاب مدل، بازه زمانی، سطح اطمینان و نحوه نمایش. |
| Confidence Interval | تعیین سطح اطمینان | مشخص میکند پیشبینی با چه درصدی از اطمینان ارائه شود (مثلاً 95٪). |
| Timeline Range | محدوده زمانی | انتخاب بازه زمانی دادههای ورودی برای پیشبینی. |
| Values Range | محدوده مقادیر | انتخاب ستون دادههایی که باید پیشبینی شوند (مثلاً ستون فروش). |
| Forecast End Date | تاریخ پایان پیشبینی | تعیین تا چه تاریخی پیشبینی انجام شود (مثلاً 6 ماه آینده). |
🎯 نکات حرفهای برای استفاده از Forecast
- دادههای ورودی باید بهصورت سری زمانی باشند (مثلاً فروش ماهانه یا بازدید روزانه).
- برای دقت بیشتر، دادهها باید بدون نوسانهای شدید یا مقادیر گمشده باشند.
- نمودار پیشبینی شامل ناحیهای به نام Confidence Band است که محدوده احتمالی تغییرات را نشان میدهد.
- Forecast Sheet بهصورت خودکار از مدلهای آماری استفاده میکند، اما برای کنترل بیشتر میتوان از توابع مثل
FORECAST.LINEARیاFORECAST.ETSاستفاده کرد.
📘 منابع فارسی برای یادگیری بیشتر
گروه Outline در تب Data اکسل ابزاری قدرتمند برای سازماندهی و نمایش سلسلهمراتبی دادههاست. این ابزار به شما اجازه میدهد دادههای پیچیده را بهصورت گروهبندیشده نمایش دهید، آنها را جمع یا باز کنید، و ساختار گزارشهای چندسطحی را بهصورت پویا مدیریت نمایید.
🧭 مقدمهای بر Outline در اکسل
Outline برای مواقعی کاربرد دارد که دادهها دارای ساختار سلسلهمراتبی هستند؛ مثلاً گزارش فروش ماهانه برای چند شعبه، یا دادههای مالی برای چند بخش. با استفاده از این ابزار میتوان بخشهای مختلف داده را گروهبندی کرد و با یک کلیک آنها را جمع یا باز کرد، بدون حذف یا فیلتر کردن اطلاعات.
📋 جدول گزینههای گروه Outline در تب Data
| گزینه | کاربرد | توضیحات |
|---|---|---|
| Group | گروهبندی دادهها | انتخاب چند ردیف یا ستون و تبدیل آنها به یک گروه قابل جمع/باز شدن. مناسب برای دستهبندی دادهها. |
| Ungroup | حذف گروهبندی | حذف گروهبندی ایجادشده و بازگرداندن دادهها به حالت اولیه. |
| Auto Outline | گروهبندی خودکار | اکسل بهصورت خودکار دادهها را بر اساس ساختار موجود گروهبندی میکند (در صورتی که دادهها ساختار مناسبی داشته باشند). |
| Subtotal | درج جمع جزئی | افزودن جمع، میانگین یا شمارش برای هر گروه از دادهها. بهصورت خودکار گروهبندی نیز انجام میشود. |
| Clear Outline | حذف کامل Outline | حذف تمام گروهبندیها و جمعهای جزئی از شیت. |
| Show Detail / Hide Detail | نمایش یا پنهانسازی جزئیات | باز یا جمع کردن گروهها برای نمایش یا پنهانسازی دادههای جزئی. این گزینهها معمولاً با علامتهای + و – در کنار شیت نمایش داده میشوند. |
🎯 نکات حرفهای برای استفاده از Outline
- برای استفاده از Group، ابتدا دادهها را مرتبسازی کنید تا ساختار منطقی داشته باشند.
- Subtotal همزمان گروهبندی و محاسبه را انجام میدهد؛ برای گزارشهای مالی بسیار کاربردی است.
- با استفاده از Auto Outline میتوان سریعاً ساختار سلسلهمراتبی را ایجاد کرد، البته اگر دادهها بهدرستی طراحی شده باشند.
- Outline با Pivot Table متفاوت است؛ این ابزار بیشتر برای نمایش و مدیریت دادهها در خود شیت استفاده میشود.