כיצד להגדיר את Excel 2010 לוחות פיבוט

by טים פרדו

01 מתוך 15

תוצאה סופית

זוהי התוצאה הסופית של שלב אחר שלב הדרכה - לחץ על התמונה כדי לראות גרסה בגודל מלא.

יש כבר פער בין Microsoft Excel לפלטפורמת הבינה העסקית העליונה (BI) במשך שנים רבות. היתרונות של Microsoft Excel 2010 Pivot Table יחד עם כמה תכונות BI אחרות הפכו אותה לתחרה אמיתית עבור BI Enterprise. Excel שימש באופן מסורתי לניתוח עצמאי וכלי סטנדרטי שכולם מייצאים את הדו"חות הסופיים שלהם. המודיעין העסקי המקצועי נשמר באופן מסורתי עבור לקוחות כמו SAS, Business Objects ו- SAP.

Microsoft Excel 2010 (עם Excel 2010 ציר ציר) יחד עם SQL Server 2008 R2, SharePoint 2010 ו חינם Microsoft Excel 2010 התוספת על "PowerPivot" הביא בסופו של דבר אינטליגנציה עסקית גבוהה ופתרון הדיווח.

מדריך זה מכסה תרחיש ישר קדימה עם Excel 2010 PivotTable מחובר למסד הנתונים SQL Server 2008 R2 באמצעות שאילתת SQL פשוטה. אני גם משתמש Slicers עבור סינון חזותי אשר חדש ב- Excel 2010. אני יכסה טכניקות BI מורכבים יותר באמצעות Data Analysis ביטויים (DAX) ב PowerPivot עבור Excel 2010 בעתיד הקרוב. מהדורה זו של Microsoft Excel 2010 יכולה לספק ערך אמיתי עבור קהילת המשתמשים שלך.

02 מתוך 15

הכנס טבלת ציר

מקם את הסמן בדיוק במקום הרצוי בטבלת הצירים ולחץ על Insert טבלת ציר.

ניתן להוסיף טבלת ציר בחוברת עבודה חדשה או קיימת של Excel. ייתכן שתרצה לשקול למקם את הסמן שלך על כמה שורות מלמעלה. זה ייתן לך מקום עבור כותרת או מידע החברה במקרה שאתה משתף את גליון העבודה או להדפיס אותו.

פתח חוברת עבודה חדשה או קיימת של Excel 2010 ולחץ על התא שבו ברצונך שהפינה השמאלית העליונה של טבלת הצירים תהיה.
לחץ על הכרטיסייה הוספה ולחץ על התפריט הנפתח PivotTable בקטע טבלאות. בחר באפשרות PivotTable. פעולה זו תפעיל את טופס יצירת PivotTable שיח.

03 מתוך 15

חיבור ציר פיבוט ל- SQL Server (או מסד נתונים אחר)

צור את שאילתת SQL ולאחר מכן התחבר ל- SQL Server כדי להטביע את מחרוזת נתוני החיבור לגיליון האלקטרוני של Excel.

Excel 2010 יכול לאחזר נתונים מכל הגדולות RDBMS (Relational Database Management System) ספקי. מנהלי התקן של SQL Server צריכים להיות זמינים עבור החיבור כברירת מחדל. אבל כל תוכנות מסד הנתונים העיקריים לעשות ODBC (מסד נתונים פתוח קישוריות) נהגים כדי לאפשר לך ליצור את החיבור. בדוק באתר האינטרנט שלהם אם אתה צריך להוריד מנהלי התקנים של ODBC.

במקרה של הדרכה זו, אני מתחבר SQL Server 2008 R2 (SQL Express גרסה חופשית).

A - טופס יצירת PivotTable הוא הטופס הראשון ביצירת החיבור ל- SQL Server. בחר "השתמש במקור נתונים חיצוני" ולחץ על הלחצן 'בחר חיבור'. השאר את המיקום שבו יוצב טבלת הצירים, אלא אם כן ברצונך ליצור גליון עבודה חדש ולמקם אותו שם.
B - הטופס 'חיבורים קיימים' מפרט את כל החיבורים בחוברת העבודה הנוכחית, במחשב וברשת שאליה אתה מחובר כעת. חיבורים קיימים הם באמת רק קבצי טקסט עם מידע החיבור הדרוש כדי לגשת למקור נתונים מסוים. במקרה שלנו, אנחנו הולכים ליצור מקור נתונים חדש. לחץ על הלחצן עיון לקבלת מידע נוסף.
C - לחץ על הלחצן New Source יפעיל את אשף חיבור הנתונים.
D - בחר Microsoft SQL Server ולחץ על הבא.
- הזן את שם השרת ואת פרטי הכניסה. בחר את שיטת האימות המתאימה. אם אינך בטוח באיזו שיטה להשתמש, פנה למנהל מסד הנתונים.
- השתמש באימות Windows: שיטה זו משתמשת בהתחברות לרשת כדי לגשת למסדי נתונים של SQL Server.
- השתמש בשם המשתמש ובסיסמה הבאים: שיטה זו משמשת כאשר שרת SQL הוגדר עם משתמשים עצמאיים לצורך גישה למסדי נתונים.
F - בשלב זה, אנחנו הולכים לבחור שולחן כמציין מיקום. אנחנו הולכים להחליף את השולחן עם SQL מותאם אישית שיספק בדיוק את הנתונים שאנחנו רוצים בחוברת העבודה של Excel שלנו.
- בחר את מסד הנתונים אליו תתחבר. בדוגמה זו, אנו מתחברים למסד הנתונים של AdventureWorks שסופק על-ידי Microsoft. בדוק את התחבר לטבלה מסוימת ובחר את הטבלה הראשונה. זכור, איננו מתכוונים לאחזר נתונים מטבלה זו.
- לחץ על סיום שיסגור את האשף ויחזיר אותך לחוברת העבודה. אנו נשנה את טבלת המיקום של שאילתת SQL המותאמת אישית שלנו.

תוחזר לטופס יצירת טופס PivotTable (A). לחץ על אישור.

04 מתוך 15

טבלת ציר מחובר באופן זמני ל - SQL

PivotTable מחובר ל- SQL Server עם טבלת מציין המיקום.

בשלב זה, התחברת לטבלת מציין המיקום ויש לך PivotTable ריק. אתה יכול לראות בצד שמאל היו PivotTable יהיה בצד ימין יש רשימה של שדות זמינים.

05 מתוך 15

מאפייני חיבור פתוח

טופס מאפייני חיבור פתוח.

לפני שנתחיל בבחירת נתונים עבור PivotTable, עלינו לשנות את החיבור לשאילתת SQL. ודא שאתה נמצא בכרטיסייה 'אפשרויות' ולחץ על 'החלפת מקור נתונים' מתוך הקטע 'נתונים'. בחר מאפייני חיבור.

זה מעלה את הטופס מאפייני חיבור. לחץ על הכרטיסייה הגדרה. זה מראה לך את פרטי החיבור עבור החיבור הנוכחי ל- SQL Server. למרות שהוא מתייחס לקובץ חיבור, הנתונים מוטבעים למעשה בגיליון האלקטרוני.

06 מתוך 15

עדכון מאפייני חיבור עם שאילתה

שינוי טבלה לשאילתת SQL.

לשנות את סוג הפקודה מטבלה ל- SQL ולהחליף את טקסט הפקודה הקיים עם שאילתת SQL. הנה השאילתה שיצרתי מתוך מסד הנתונים של AdventureWorks לדוגמה:

בחר Sales.SalesOrderHeader.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
מכירה.
מכירות.סליימרר,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt,
מכירות .SalesOrderHeader.Freight,
מכירה.
Sales.SalesOrderDetail.SalesOrderDetailID,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.LineTotal,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
ממכירות
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN הפקה.מוצר על Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer פועל
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INN JOIN
Sales.vIndividualCustomer על Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INN JOIN
Production.ProductSubcategory על ייצור. Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

לחץ על אישור.

07 מתוך 15

קבל את החיבור

לחץ על כן לחיבור אזהרה.

תופיע תיבת דו-שיח של Microsoft Excel Warning. הסיבה לכך היא ששינינו את פרטי החיבור. כאשר במקור יצרנו את החיבור, הוא שמר את המידע בקובץ חיצוני. ODC (חיבור נתונים של ODBC). הנתונים בחוברת העבודה היו זהים לקובץ ה- ODC עד שהשתנינו מסוג פקודה טבלה לסוג פקודת SQL בשלב # 6. האזהרה אומרת לך שהנתונים אינם מסונכרנים עוד, וההתייחסות לקובץ החיצוני בחוברת העבודה תוסר. זה בסדר. לחץ על כן.

08 מתוך 15

טבלת ציר מחובר ל - SQL Server עם שאילתה

PivotTable מוכן להוסיף נתונים.

פעולה זו מחזירה את חוברת העבודה של Excel 2010 עם PivotTable ריק. ניתן לראות שהשדות הזמינים שונים כעת ומתאימים לשדות בשאילתת SQL. כעת אנו יכולים להתחיל להוסיף שדות ל- PivotTable.

09 מתוך 15

הוסף שדות לטבלת ציר

הוסף שדות ל- PivotTable.

ברשימת השדות PivotTable, גרור את ProductCategory לאזור 'שורות תוויות', באזור OrderDate לאזורי עמודות וערך TotalDue to Values. התמונה מציגה את התוצאות. כפי שניתן לראות, לשדה התאריך יש תאריכים נפרדים, כך שה- PivotTable יצר עמודה עבור כל תאריך ייחודי. למרבה המזל, Excel 2010 יש כמה פונקציות מובנות כדי לסייע לנו לארגן שדות תאריך.

10 מתוך 15

הוסף קיבוץ עבור שדות תאריך

הוסף קבוצות עבור שדה תאריך.

פונקציית הקבצה מאפשרת לנו לארגן תאריכים לשנים, חודשים, רבעונים וכו 'זה יעזור לסכם את הנתונים ולהקל על המשתמש לקיים אינטראקציה עם זה. לחץ לחיצה ימנית על אחד כותרות העמודות תאריך ובחר קבוצה אשר מעלה את הטופס קיבוץ.

11 מתוך 15

בחר קיבוץ לפי ערכים

בחר פריטים מקובצים עבור שדה תאריך.

בהתאם לסוג הנתונים שאתה מקבץ, הטופס ייראה קצת שונה. Excel 2010 מאפשר לך לקבץ תאריכים, מספרים ונתוני טקסט נבחרים. אנו מקבצים OrderDate במדריך זה כך שהטופס יציג אפשרויות הקשורות לקבוצות תאריך.

לחץ על חודשים ושנים ולחץ על אישור.

12 מתוך 15

לוח מקובץ לפי שנים וחודשים

שדות התאריך מקובצים לפי שנים וחודשים.

כפי שניתן לראות בתמונה לעיל, הנתונים מקובצים לפי שנה ראשונה ולאחר מכן לפי חודש. לכל אחד יש סימן פלוס ומינוס המאפשר לך להתרחב ולהתמוטט בהתאם לאופן שבו אתה רוצה לראות את הנתונים.

בשלב זה, PivotTable הוא די שימושי. כל אחד מהשדות ניתן לסנן אבל הבעיה היא שאין רמז חזותי למצב הנוכחי של המסננים. כמו כן, זה לוקח כמה לחץ כדי לשנות את התצוגה.

13 מתוך 15

הכנס את Slicer (חדש ב- Excel 2010)

הוסף Slicers כדי PivotTable.

Slicers הם חדשים ב- Excel 2010. Slicers הם בעצם המקבילה של מסננים חזותיים הגדרה של השדות הקיימים ויצירת מסננים דוח במקרה שהפריט שברצונך לסנן ב לא בתצוגה הנוכחית PivotTable. זה דבר נחמד על Slicers זה הופך להיות קל מאוד עבור המשתמש לשנות את התצוגה של הנתונים ב PivotTable, כמו גם מתן אינדיקטורים חזותיים למצב הנוכחי של המסננים.

כדי להכניס את Slicers, לחץ על הכרטיסייה אפשרויות ולחץ על Insert Slicer מתוך הקטע מיין וסנן. בחר Insert Slicer אשר פותח את הטופס Insert Slicers. בדוק כמה שיותר שדות כפי שאתה רוצה להיות זמין. בדוגמה שלנו, הוספתי שנים, CountryRegionName ו- ProductCategory. ייתכן שיהיה עליך למקם את Slicers שבו אתה רוצה אותם. כברירת מחדל, כל הערכים נבחרים שמשמעותם שלא הוחלו מסננים.

14 מתוך 15

טבלת ציר עם

Slicers להקל על המשתמשים לסנן את PivotTables.

כפי שאתה יכול לראות, את Slicers להציג את כל הנתונים שנבחרו. זה מאוד ברור למשתמש בדיוק מה הנתונים בתצוגה הנוכחית של PivotTable.

15 מתוך 15

בחר ערכים מתוך Slicers אילו עדכונים ציר הטבלה

בחר שילובים של Slicers כדי לשנות את התצוגה של הנתונים.

לחץ על שילובים שונים של ערכים ולראות כיצד התצוגה של השינויים PivotTable. ניתן להשתמש בלחיצה טיפוסית של Microsoft ב Slicers כלומר אם אתה יכול להשתמש Control + לחץ כדי לבחור ערכים מרובים או Shift + לחץ כדי לבחור טווח של ערכים. כל Slicer מציג את הערכים שנבחרו מה שהופך אותו ממש ברור מה מצב PivotTable הוא במונחים של מסננים. אתה יכול לשנות את סגנונות של Slicers אם אתה רוצה על ידי לחיצה על תפריט מהיר סגנונות למטה בסעיף קטע של הכרטיסייה אפשרויות.

המבוא של Slicers שיפר באמת את השימושיות של PivotTables והזיז את Excel 2010 הרבה יותר קרוב להיות כלי מודיעין עסקי מקצועי. PivotTables השתפרו לא מעט ב- Excel 2010 וכאשר בשילוב עם PowerPivot החדש יוצר סביבה ביצועים גבוהים מאוד אנליטית.