نحوه پیکربندی جداول محور اکسل 2010

01 از 15

نتیجه نهایی

فاصله بین مایکروسافت اکسل و سیستم عامل کسب و کار در سطح بالا برای سالهای زیادی وجود دارد. اصلاح جدول مایکروسافت اکسل 2010 با تعدادی دیگر از ویژگی های BI آن را یک رقیب واقعی برای BI است. اکسل به طور سنتی برای تجزیه و تحلیل مستقل و ابزار استاندارد که هر کس گزارش های نهایی خود را به آن ها ارسال می کند، مورد استفاده قرار می گیرد. هوش تجاری حرفه ای به طور سنتی برای SAS، Objects Business و SAP رزرو شده است.

مایکروسافت اکسل 2010 (با جدول ارجاع اکسل 2010) همراه با SQL Server 2008 R2، شیرپوینت 2010 و افزودنی افزونه مایکروسافت اکسل 2010 PowerPivot به یک راه حل عالی کسب و کار و گزارش کسب و کار منجر شده است.

این آموزش یک سناریو مستقیم را با یک اکسل 2010 PivotTable متصل به پایگاه داده SQL Server 2008 R2 با استفاده از پرس و جو ساده SQL را پوشش می دهد. من همچنین از Slicers برای فیلترینگ بصری استفاده می کنم که در اکسل 2010 جدید است. من در تکنولوژی PowerPivot برای Excel 2010، تکنیک های پیچیده تر BI را با استفاده از اشکال تجزیه تحلیل داده ها (DAX) پوشش خواهم داد. این آخرین نسخه مایکروسافت اکسل 2010 می تواند یک ارزش واقعی برای جامعه کاربر شما باشد.

02 از 15

جدول محوری را وارد کنید

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

• یک کتاب جدید و یا موجود اکسل 2010 را باز کنید و روی سلول کلیک کنید که در آن می خواهید گوشه بالا سمت چپ جدول پیوند را داشته باشید.
• روی برگه Insert کلیک کنید و در قسمت جداول PivotTable را کلیک کنید. PivotTable را انتخاب کنید این فرم محاوره ای Create PivotTable را راه اندازی خواهد کرد.

03 از 15

جدول پیوند را به SQL Server وصل کنید (یا سایر پایگاه داده ها)

اکسل 2010 می تواند داده ها را از تمام رگولاتورهای مهم RDBMS (سیستم مدیریت ارتباط پایگاه داده) بازیابی کند. درایورهای SQL Server باید به طور پیش فرض برای اتصال استفاده شوند. اما تمام نرم افزارهای پایگاه داده اصلی، سازندگان ODBC (Open Database Connectivity) را قادر می سازند تا ارتباط برقرار کنند. اگر شما نیاز به دانلود درایورهای ODBC دارید، وب سایت خود را بررسی کنید.

در مورد این آموزش، من با SQL Server 2008 R2 (نسخه رایگان SQL Express) ارتباط برقرار می کنم.

• A - فرم Create PivotTable اولین فرم در ایجاد اتصال به SQL Server است. "استفاده از یک منبع داده های خارجی" را انتخاب کنید و روی دکمه انتخاب اتصال کلیک کنید. محل مکانی که در آن جدول پیوند قرار داده می شود را ترک کنید مگر اینکه شما می خواهید یک برگه جدید ایجاد کنید و آن را در آنجا قرار دهید.
• B - فرم اتصالات موجود، هر گونه ارتباط در کتاب کار فعلی، در رایانه و شبکه شما در حال حاضر به آن متصل است. اتصالات موجود واقعا فقط فایل های متنی با اطلاعات اتصال مورد نیاز برای دسترسی به یک منبع داده خاص است. در مورد ما، ما قصد داریم یک منبع داده جدید ایجاد کنیم. روی دکمه Browse for More کلیک کنید.
• C - بر روی دکمه منبع جدید کلیک کنید Wizard Data Connection را راه اندازی می کند.
• D - Microsoft SQL Server را انتخاب کرده و روی Next کلیک کنید.
• E - نام سرور را وارد کنید و وارد حساب کاربری شوید. روش احراز هویت مناسب را انتخاب کنید. اگر مطمئن نیستید کدام روش برای استفاده، با مدیر پایگاه داده خود تماس بگیرید.
  • از تایید هویت ویندوز استفاده کنید: این روش از ورود به شبکه شما برای دسترسی به پایگاه داده های SQL Server استفاده می کند.
  • از نام کاربری و رمز عبور زیر استفاده کنید: این روش زمانی است که SQL Server توسط کاربران مستقل برای دسترسی به پایگاه داده پیکربندی شده است.
• F - در این مرحله، ما یک جدول را به عنوان نگهدارنده انتخاب می کنیم. ما قصد داریم جدول را با SQL سفارشی جایگزین کنیم که دقیقا همان اطلاعاتی را که ما در کارنامه اکسل می خواهیم ارائه می دهیم.
  • پایگاه داده ای را که می خواهید به آن وصل شوید را انتخاب کنید. در این مثال، ما با پایگاه داده نمونه AdventureWorks ارائه شده توسط مایکروسافت ارتباط برقرار می کنیم. اتصال به یک جدول خاص را بررسی کنید و اولین جدول را انتخاب کنید. به یاد داشته باشید، ما نمی خواهیم داده ها را از این جدول بازیابی کنیم.
  • روی Finish کلیک کنید که جادو را بست و شما را به کتاب کار بازگشت. ما برای جدول سفارشی SQL ما جدول جایگذاری را تغییر خواهیم داد.

شما به فرم Create PivotTable (A) بازگشته اید. روی OK کلیک کنید

04 از 15

جدول محوری به طور موقت به جدول SQL مرتبط است

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

05 از 15

خصوصیات باز کردن اتصال

قبل از اینکه ما شروع به انتخاب داده ها برای PivotTable کنیم، باید ارتباط را با SQL query تغییر دهیم. اطمینان حاصل کنید که در برگه گزینه ها قرار دارید و بر روی Change Data Source کشویی زیر بخش Data کلیک کنید. خواص اتصال را انتخاب کنید.

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

06 از 15

به روز رسانی خواص اتصال با پرس و جو

دستور Command را از جدول به SQL تغییر دهید و فرمان Text موجود را با SQL Query خود بازنویسی کنید. در اینجا پرس و جوی است که از پایگاه داده نمونه AdventureWorks ایجاد کردم:

SELECT Sales.SalesOrderHeader.SalesOrderID،
Sales.SalesOrderHeader.OrderDate،
Sales.SalesOrderHeader.ShipDate،
Sales.SalesOrderHeader.Status،
Sales.SalesOrderHeader.SubTotal،
Sales.SalesOrderHeader.TaxAmt،
Sales.SalesOrderHeader.Freight،
Sales.SalesOrderHeader.TotalDue،
Sales.SalesOrderDetail.SalesOrderDetailID،
Sales.SalesOrderDetail.OrderQty
Sales.SalesOrderDetail.UnitPrice،
Sales.SalesOrderDetail.LineTotal،
تولید. محصول. نام
Sales.vIndividualCustomer.StateProvinceName، Sales.vIndividualCustomer.CountryRegionName،
Sales.Customer.CustomerType،
Production.Product.ListPrice،
تولید. محصول. خط تولید،
Production.ProductSubcategory.Name AS ProductCategory
از Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID و
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
تولیدات. محصولات. دسته. محصولات. محصولات. دسته بندی

روی OK کلیک کنید

07 از 15

اخطار اتصال دریافت کنید

یک کادر محاوره ای اخطار مایکروسافت اکسل دریافت خواهید کرد. این به این دلیل است که ما اطلاعات اتصال را تغییر دادیم. وقتی ما در ابتدا اتصال را ایجاد کردیم، اطلاعات را در یک فایل خارجی .ODC (ODBC Data Connection) ذخیره کردیم. داده ها در کتاب کار همانند فایل .ODC بود تا زمانی که ما از نوع دستور جدول به نوع دستور SQL در مرحله 6 تغییر کردیم. هشدار به شما می گوید که داده ها دیگر همگام نیستند و مرجع به فایل خارجی در کتاب کار حذف خواهد شد. این خوب است روی بله کلیک کنید.

08 از 15

جدول پیوندی با SQL Query مرتبط است

این کار را با یک PivotTable خالی به کارگاه Excel 2010 بازمیگرداند. شما می توانید ببینید که زمینه های در حال حاضر در حال حاضر متفاوت است و مربوط به زمینه ها در پرس و جو SQL. حالا می توانیم فیلدهای را به PivotTable اضافه کنیم.

09 از 15

اضافه کردن زمینه به جدول محوری

در لیست فیلد PivotTable، ProductCategory را به Area Row Labels، OrderDate به Area Columns برچسب ها و Area TotalDue to Values ​​بکشید. تصویر نتایج را نشان می دهد. همانطور که می بینید، فیلد تاریخ دارای تاریخ های فردی است بنابراین PivotTable یک ستون برای هر تاریخ منحصر به فرد ایجاد کرده است. خوشبختانه، اکسل 2010 دارای برخی از توابع ساخته شده است که به ما کمک می کند فیلدهای تاریخ را سازماندهی کنیم.

10 از 15

اضافه کردن گروه برای زمینه های تاریخ

تابع گروه اجازه می دهد تا ما را به سازماندهی تاریخ به سال، ماه، چهارم، و غیره. این کمک خواهد کرد خلاصه ای از داده ها و آن را برای کاربر راحت تر برای تعامل با آن. روی یکی از هدرهای ستون تاریخ کلیک راست کرده و Group را انتخاب کنید که فرم گروه بندی را به ارمغان می آورد.

11 از 15

گروه بندی با ارزش را انتخاب کنید

بسته به نوع داده ای که گروه بندی می کنید، فرم کمی متفاوت خواهد بود. اکسل 2010 به شما اجازه می دهد تا گروه ها، اعداد و داده های متن انتخاب شده را دسته بندی کنید. ما OrderDate را در این آموزش دسته بندی می کنیم تا فرم گزینه های مربوط به گروه بندی های تاریخ را نشان می دهد.

روی ماهها و سالها کلیک کنید و روی OK کلیک کنید.

12 از 15

جدول محوری توسط سالها و ماهها دسته بندی شده است

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

در این مرحله PivotTable بسیار مفید است. هر یک از فیلدها می تواند فیلتر شود، اما مشکل این است که یک نشانه بصری به عنوان وضعیت فعلی فیلتر وجود ندارد. همچنین، برای تغییر دید، چندین بار کلیک کنید.

13 از 15

درج Slicer (جدید در اکسل 2010)

برشگرها در اکسل 2010 جدید هستند. برشگرها اساسا معادل تنظیمات بصری فیلترهای موجود و ایجاد گزارش فیلترها در مورد موردی هستند که شما می خواهید بر روی آن فیلتر کنید در نمای PivotTable فعلی نیست. این چیز خوب در مورد Slicers این است که کاربر برای تغییر دیدگاه داده ها در PivotTable و ارائه شاخص های بصری به وضعیت فعلی فیلتر بسیار آسان می شود.

برای قرار دادن Slicers، روی برگه گزینه ها کلیک کنید و بر روی Insert Slicer از قسمت Sort & Filter کلیک کنید. Insert Slicer را انتخاب کنید که فرم Insert Slicers را باز می کند. به عنوان بسیاری از زمینه های که شما می خواهید در دسترس است را بررسی کنید. در مثال ما Years، CountryRegionName و ProductCategory را اضافه کردیم. شما ممکن است مجبور به قرار دادن Slicers که در آن شما آنها را می خواهم. به طور پیش فرض، تمام مقادیر انتخاب شده اند که به این معنی نیست که فیلترها اعمال نشده اند.

14 از 15

جدول محوری با ابزارهای کاربر دوستانه

همانطور که می بینید، Slicers تمام داده ها را به عنوان انتخاب شده نشان می دهد. دقیقا مشخص است که چه اطلاعاتی در نمای فعلی PivotTable برای کاربر مشخص است.

15 از 15

مقادیری را انتخاب کنید از Slicers که به روز رسانی جدول پیوند را انتخاب کنید

بر روی ترکیب های مختلفی از مقادیر کلیک کنید و ببینید که چگونه دید تغییرات PivotTable تغییر می کند. شما می توانید با استفاده از کلیدهای معمول مایکروسافت در Slicers استفاده کنید به این معنی که اگر شما می توانید از کنترل + کلیک برای انتخاب چندین مقادیر یا Shift + Click برای انتخاب طیف وسیعی از مقادیر استفاده کنید. هر Slicer مقادیر انتخاب شده را نشان می دهد که واقعا واضح است که وضعیت PivotTable از لحاظ فیلترها چیست. شما می توانید سبک های Slicers را اگر می خواهید با کلیک بر روی سبک های سریع در بخش Slicer در برگه گزینه ها کلیک کنید.

معرفی Slicers واقعا قابلیت استفاده از PivotTables را بهبود بخشید و اکسل 2010 را بسیار نزدیکتر به ابزار هوش تجاری حرفه ای کرده است. PivotTables در اکسل 2010 کمی بهبود یافته است و هنگامی که با PowerPivot جدید ایجاد می شود یک محیط تحلیلی بسیار کارآمد را ایجاد می کند.