01 trên 15
Kết quả cuối cùng
Đã có một khoảng cách giữa Microsoft Excel và nền tảng kinh doanh thông minh hàng đầu (BI) trong nhiều năm. Các cải tiến Bảng Excel của Microsoft Excel 2010 cùng với một vài tính năng BI khác đã khiến nó trở thành đối thủ thực sự của BI doanh nghiệp. Excel có truyền thống được sử dụng để phân tích độc lập và công cụ chuẩn mà mọi người xuất báo cáo cuối cùng của họ vào. Chuyên nghiệp kinh doanh thông minh có truyền thống được dành riêng cho thích của SAS, Đối tượng kinh doanh và SAP.
Microsoft Excel 2010 (với Bảng tổng hợp Excel 2010) cùng với SQL Server 2008 R2, SharePoint 2010 và tiện ích bổ sung miễn phí Microsoft Excel 2010 "PowerPivot" đã dẫn đến một giải pháp báo cáo và báo cáo kinh doanh cao cấp.
Hướng dẫn này bao gồm một kịch bản thẳng về phía trước với PivotTable Excel 2010 được kết nối với cơ sở dữ liệu SQL Server 2008 R2 bằng cách sử dụng truy vấn SQL đơn giản. Tôi cũng đang sử dụng Slicers để lọc hình ảnh mới trong Excel 2010. Tôi sẽ trình bày kỹ thuật BI phức tạp hơn bằng cách sử dụng Biểu thức phân tích dữ liệu (DAX) trong PowerPivot cho Excel 2010 trong tương lai gần. Bản phát hành mới nhất của Microsoft Excel 2010 có thể cung cấp giá trị thực cho cộng đồng người dùng của bạn.
02 trên 15
Chèn bảng tổng hợp
Bạn có thể chèn Bảng tổng hợp vào sổ làm việc Excel mới hoặc hiện có. Bạn có thể muốn xem xét định vị con trỏ của mình xuống một vài hàng từ trên cùng. Điều này sẽ cung cấp cho bạn không gian cho một thông tin tiêu đề hoặc công ty trong trường hợp bạn chia sẻ bảng tính hoặc in ra.
- Mở một sổ làm việc Excel 2010 mới hoặc hiện có và bấm vào ô mà bạn muốn ở góc trên cùng bên trái của Bảng Pivot.
- Bấm vào tab chèn và bấm vào danh sách thả xuống PivotTable trong phần bảng. Chọn PivotTable. Điều này sẽ khởi chạy biểu mẫu hộp thoại Tạo PivotTable.
03 trên 15
Kết nối bảng Pivot với SQL Server (hoặc cơ sở dữ liệu khác)
Excel 2010 có thể truy xuất dữ liệu từ tất cả các nhà cung cấp RDBMS (Hệ thống quản lý cơ sở dữ liệu quan hệ) lớn. Trình điều khiển SQL Server sẽ có sẵn cho kết nối theo mặc định. Nhưng tất cả phần mềm cơ sở dữ liệu chính đều làm cho trình điều khiển ODBC (Open Database Connectivity) cho phép bạn thực hiện kết nối. Kiểm tra trang web của họ nếu bạn cần tải xuống trình điều khiển ODBC.
Trong trường hợp của hướng dẫn này, tôi đang kết nối với SQL Server 2008 R2 (phiên bản SQL Express miễn phí).
- A - Tạo biểu mẫu PivotTable là biểu mẫu đầu tiên trong việc tạo kết nối đến SQL Server. Chọn "Sử dụng nguồn dữ liệu ngoài" và nhấp vào nút Chọn kết nối. Để lại vị trí nơi Pivot Table sẽ được đặt trừ khi bạn muốn tạo một trang tính mới và đặt nó ở đó.
- B - Biểu mẫu Kết nối hiện có liệt kê mọi kết nối trong sổ làm việc hiện tại, trên máy tính của bạn và mạng mà bạn hiện đang kết nối. Các kết nối hiện tại thực sự chỉ là các tệp văn bản có thông tin kết nối cần thiết để truy cập vào một nguồn dữ liệu cụ thể. Trong trường hợp của chúng ta, chúng ta sẽ tạo một nguồn dữ liệu mới. Nhấp vào nút Duyệt tìm kiếm thêm.
- C - Nhấn vào nút Nguồn mới sẽ khởi chạy Trình hướng dẫn kết nối dữ liệu.
- D - Chọn Microsoft SQL Server và nhấn Next.
- E - Nhập tên máy chủ và đăng nhập vào thông tin đăng nhập. Chọn phương thức xác thực phù hợp. Nếu bạn không chắc chắn nên sử dụng phương thức nào, hãy liên hệ với quản trị viên cơ sở dữ liệu của bạn.
- Sử dụng Xác thực Windows: Phương pháp này sử dụng thông tin đăng nhập mạng của bạn để truy cập cơ sở dữ liệu SQL Server.
- Sử dụng tên người dùng và mật khẩu sau: Phương pháp này được sử dụng khi SQL Server đã được cấu hình với người dùng độc lập để truy cập cơ sở dữ liệu.
- F - Trong bước này, chúng ta sẽ chọn một bảng như một trình giữ chỗ. Chúng tôi sẽ thay thế bảng bằng SQL tùy chỉnh sẽ cung cấp chính xác dữ liệu chúng tôi muốn trong sổ làm việc Excel của chúng tôi.
- Chọn cơ sở dữ liệu mà bạn sẽ kết nối đến. Trong ví dụ này, chúng tôi đang kết nối với cơ sở dữ liệu mẫu AdventureWorks do Microsoft cung cấp. Kiểm tra Kết nối với một bảng cụ thể và chọn bảng đầu tiên. Hãy nhớ rằng, chúng tôi sẽ không lấy dữ liệu từ bảng này.
- Nhấp vào Kết thúc sẽ đóng trình hướng dẫn và đưa bạn trở lại bảng tính. Chúng tôi sẽ trao đổi bảng giữ chỗ cho truy vấn SQL tùy chỉnh của chúng tôi.
Bạn sẽ được trả về biểu mẫu Tạo PivotTable (A). Nhấp vào OK.
04 trên 15
Bảng tổng hợp tạm thời được kết nối với bảng SQL
Tại thời điểm này, bạn đã kết nối với bảng giữ chỗ và bạn có một PivotTable trống. Bạn có thể thấy bên trái là PivotTable và bên phải có một danh sách các trường có sẵn.
05 trên 15
Mở thuộc tính kết nối
Trước khi chúng ta bắt đầu chọn dữ liệu cho PivotTable, chúng ta cần thay đổi kết nối đến truy vấn SQL. Đảm bảo bạn đang ở trên tab Tùy chọn và nhấp vào Thay đổi nguồn dữ liệu thả xuống từ phần Dữ liệu. Chọn Thuộc tính kết nối.
Điều này sẽ trả về biểu mẫu Thuộc tính kết nối. Nhấp vào tab Định nghĩa. Điều này cho bạn thấy thông tin kết nối cho kết nối hiện tại đến SQL Server. Trong khi nó tham chiếu đến tệp kết nối, dữ liệu thực sự được nhúng trong bảng tính.
06 trên 15
Cập nhật thuộc tính kết nối với truy vấn
Thay đổi Loại lệnh từ Bảng thành SQL và ghi đè lên Văn bản Lệnh hiện tại bằng Truy vấn SQL của bạn. Đây là truy vấn tôi đã tạo từ cơ sở dữ liệu mẫu AdventureWorks:
CHỌN 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,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
FROM 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.Khách hàng BẬT
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
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 =
Production.ProductSubcategory.ProductSubcategoryID
Nhấp vào OK.
07 trên 15
Nhận cảnh báo kết nối
Bạn sẽ nhận được một hộp thoại Cảnh báo Microsoft Excel. Điều này là do chúng tôi đã thay đổi thông tin kết nối. Khi chúng ta tạo kết nối ban đầu, nó đã lưu thông tin trong một tệp .ODC bên ngoài (ODBC Data Connection). Dữ liệu trong sổ làm việc cũng giống như tệp .ODC cho đến khi chúng ta thay đổi từ một kiểu lệnh bảng thành kiểu lệnh SQL trong Bước # 6. Cảnh báo cho bạn biết rằng dữ liệu không còn đồng bộ và tham chiếu tới tệp bên ngoài trong sổ làm việc sẽ bị xóa. Điều này là OK. Nhấp vào Có.
08 trên 15
Bảng tổng hợp được kết nối với SQL Server với truy vấn
Điều này sẽ trở lại sổ làm việc Excel 2010 với một PivotTable trống. Bạn có thể thấy rằng các trường có sẵn bây giờ khác nhau và tương ứng với các trường trong truy vấn SQL. Bây giờ chúng ta có thể bắt đầu thêm các trường vào PivotTable.
09 trên 15
Thêm trường vào bảng tổng hợp
Trong danh sách trường PivotTable, kéo ProductCategory vào khu vực nhãn hàng, OrderDate đến khu vực nhãn cột và khu vực TotalDue to Values. Hình ảnh hiển thị kết quả. Như bạn có thể thấy, trường ngày tháng có các ngày riêng lẻ nên PivotTable đã tạo một cột cho mỗi ngày duy nhất. May mắn thay, Excel 2010 có một số chức năng được tích hợp sẵn để giúp chúng tôi tổ chức các trường ngày tháng.
10 trên 15
Thêm nhóm cho trường ngày
Chức năng Nhóm cho phép chúng tôi tổ chức ngày thành năm, tháng, quý, v.v. Điều này sẽ giúp tóm tắt dữ liệu và giúp người dùng tương tác với dữ liệu dễ dàng hơn. Nhấp chuột phải vào một trong các tiêu đề cột ngày và chọn Nhóm sẽ hiển thị biểu mẫu Nhóm.
11 trên 15
Chọn nhóm theo giá trị
Tùy thuộc vào loại dữ liệu bạn đang nhóm, biểu mẫu sẽ trông hơi khác một chút. Excel 2010 cho phép bạn nhóm ngày, số và dữ liệu văn bản đã chọn. Chúng tôi đang nhóm OrderDate trong hướng dẫn này để biểu mẫu sẽ hiển thị các tùy chọn liên quan đến các nhóm ngày.
Bấm vào tháng và năm và bấm OK.
12 trên 15
Bảng tổng hợp được nhóm theo năm và tháng
Như bạn có thể thấy trong hình trên, dữ liệu được nhóm theo năm đầu tiên và sau đó theo tháng. Mỗi thẻ có dấu cộng và dấu trừ cho phép bạn mở rộng và thu gọn tùy thuộc vào cách bạn muốn xem dữ liệu.
Tại thời điểm này, PivotTable là khá hữu ích. Mỗi trường có thể được lọc nhưng vấn đề là không có một đầu mối trực quan nào về trạng thái hiện tại của các bộ lọc. Ngoài ra, phải mất một vài cú nhấp chuột để thay đổi chế độ xem.
13 trên 15
Chèn Slicer (Mới trong Excel 2010)
Slicer là mới trong Excel 2010. Slicer về cơ bản là tương đương với bộ lọc thiết lập trực quan của các trường hiện có và tạo Bộ lọc Báo cáo trong trường hợp mục mà bạn muốn lọc không nằm trong dạng xem PivotTable hiện tại. Điều tốt đẹp về Slicer là nó trở nên rất dễ dàng cho người dùng thay đổi quan điểm của dữ liệu trong PivotTable cũng như cung cấp các chỉ báo trực quan về trạng thái hiện tại của các bộ lọc.
Để chèn Slicer, nhấp vào tab Options và nhấp vào Insert Slicer từ phần Sort & Filter. Chọn Insert Slicer để mở biểu mẫu Chèn Slicer. Kiểm tra bao nhiêu trường bạn muốn có sẵn. Trong ví dụ của chúng tôi, tôi đã thêm năm, CountryRegionName và ProductCategory. bạn có thể phải định vị Slicers ở nơi bạn muốn. Theo mặc định, tất cả các giá trị được chọn có nghĩa là không có bộ lọc nào được áp dụng.
14 trên 15
Bảng tổng hợp với người dùng thân thiện Slicers
15 trên 15
Chọn giá trị từ Slicers Cập nhật Pivot Table
Bấm vào các kết hợp khác nhau của giá trị và xem cách xem PivotTable thay đổi. Bạn có thể sử dụng nhấn Microsoft điển hình trong Slicers có nghĩa là nếu bạn có thể sử dụng Control + Click để chọn nhiều giá trị hoặc Shift + Click để chọn một dãy giá trị. Mỗi Slicer hiển thị các giá trị đã chọn, điều này làm cho nó thực sự rõ ràng về trạng thái của PivotTable trong điều kiện của các bộ lọc. Bạn có thể thay đổi kiểu của Slicer nếu bạn muốn bằng cách nhấp vào thả xuống Styles nhanh trong phần Slicer của tab Options.
Việc giới thiệu Slicers đã thực sự cải thiện khả năng sử dụng của PivotTables và đã di chuyển Excel 2010 gần hơn với việc trở thành một công cụ thông minh nghiệp vụ chuyên nghiệp. PivotTables đã cải thiện khá nhiều trong Excel 2010 và khi kết hợp với PowerPivot mới tạo ra một môi trường phân tích hiệu suất rất cao.