Kinh doanh thông minh trong Microsoft Excel
PowerPivot cho Excel là một phần bổ trợ cho Microsoft Excel . Nó cho phép người dùng tiến hành trí tuệ kinh doanh mạnh mẽ [BI] trong một môi trường quen thuộc.
PowerPivot là bản tải xuống miễn phí của Microsoft và cho phép người dùng làm việc với các tập dữ liệu cực lớn. Trước PowerPivot, loại phân tích này được giới hạn trong các công cụ BI của doanh nghiệp như SAS và Business Objects.
PowerPivot sử dụng một công cụ trong bộ nhớ có tên là VertiPaq. Động cơ SSAS này tận dụng tối đa bộ nhớ RAM có sẵn trong hầu hết các máy tính cá nhân ngày nay.
Hầu hết các cửa hàng CNTT được thử thách với các nguồn lực cần thiết để xây dựng một môi trường BI doanh nghiệp. PowerPivot di chuyển một số công việc này gần hơn với người dùng doanh nghiệp. Mặc dù có nhiều tính năng trong PowerPivot dành cho Excel, chúng tôi đã chọn năm tính năng mà chúng tôi cho là tuyệt vời nhất.
Mẹo: Bạn có thể tải xuống PowerPivot tại đây. Xem liệu bạn có đang sử dụng phiên bản Windows 32 bit hoặc 64 bit không nếu bạn không chắc chắn liên kết tải xuống nào để chọn từ trang web của Microsoft. Microsoft có cách cài đặt PowerPivot nếu bạn gặp sự cố.
Lưu ý: Dữ liệu PowerPivot chỉ có thể được lưu trong sổ làm việc sử dụng phần mở rộng tệp XLSX , XLSM hoặc XLSB .
01/05
Làm việc với các tập dữ liệu rất lớn
Trong Microsoft Excel, nếu bạn di chuyển xuống cuối trang tính, bạn sẽ thấy số hàng tối đa là 1.048.576. Điều này đại diện cho khoảng một triệu hàng dữ liệu.
Với PowerPivot cho Excel, không có giới hạn về số lượng hàng dữ liệu. Trong khi đây là một tuyên bố đúng, giới hạn thực tế dựa trên phiên bản Microsoft Excel bạn đang chạy và liệu bạn có đang xuất bản bảng tính của mình lên SharePoint 2010 hay không.
Nếu bạn đang chạy phiên bản Excel 64 bit , PowerPivot có thể báo cáo xử lý khoảng 2 GB dữ liệu, nhưng bạn cũng phải có đủ RAM để làm việc này suôn sẻ. Nếu bạn có kế hoạch xuất bản bảng tính Excel dựa trên PowerPivot của bạn lên SharePoint 2010, kích thước tệp tối đa cũng là 2 GB.
Điểm mấu chốt là PowerPivot cho Excel có thể xử lý hàng triệu bản ghi. Nếu bạn nhấn tối đa, bạn sẽ nhận được một lỗi bộ nhớ.
Nếu bạn muốn chơi với PowerPivot dành cho Excel bằng cách sử dụng hàng triệu bản ghi, hãy tải xuống Dữ liệu Mẫu Hướng dẫn PowerPivot dành cho Excel [khoảng 2,3 triệu bản ghi] có dữ liệu bạn cần cho Hướng dẫn về Sổ làm việc PowerPivot.
02 trên 05
Kết hợp dữ liệu từ các nguồn khác nhau
Đây phải là một trong những tính năng quan trọng nhất trong PowerPivot dành cho Excel. Excel luôn có thể xử lý các nguồn dữ liệu khác nhau như SQL Server , XML, Microsoft Access và thậm chí cả dữ liệu dựa trên web. Vấn đề xảy ra khi bạn cần tạo mối quan hệ giữa các nguồn dữ liệu khác nhau.
Có các sản phẩm của bên thứ ba có sẵn để trợ giúp việc này và bạn có thể sử dụng các hàm Excel như VLOOKUP để "nối" dữ liệu, các phương pháp này là không thực tế đối với các tập dữ liệu lớn. PowerPivot cho Excel được xây dựng để thực hiện tác vụ này.
Trong PowerPivot, bạn có thể nhập dữ liệu từ hầu như bất kỳ nguồn dữ liệu nào. Tôi đã tìm thấy rằng một trong những nguồn dữ liệu hữu ích nhất là Danh sách SharePoint. Tôi đã sử dụng PowerPivot cho Excel để kết hợp dữ liệu từ SQL Server và danh sách từ SharePoint.
Ghi chú: Bạn cần SharePoint 2010 để thực hiện công việc này, cùng với thời gian chạy ADO.Net được cài đặt trên môi trường SharePoint.
Khi bạn kết nối PowerPivot với danh sách SharePoint, bạn đang thực sự kết nối với Nguồn cấp dữ liệu. Để tạo Nguồn cấp Dữ liệu từ danh sách SharePoint, hãy mở danh sách và bấm vào dải Danh sách . Sau đó nhấp vào Xuất dưới dạng Nguồn cấp dữ liệu và lưu nó.
Nguồn cấp dữ liệu có sẵn dưới dạng URL trong PowerPivot dành cho Excel. Kiểm tra giấy trắng Sử dụng dữ liệu danh sách SharePoint trong PowerPivot [đây là tệp MS Word DOCX] để biết thêm thông tin về cách sử dụng SharePoint làm nguồn dữ liệu cho PowerPivot.
03 trên 05
Tạo mô hình phân tích trực quan hấp dẫn
PowerPivot cho Excel cho phép bạn xuất nhiều dữ liệu trực quan vào trang tính Excel của mình. Bạn có thể trả về dữ liệu trong PivotTable, PivotChart, biểu đồ và bảng [ngang và dọc], hai biểu đồ [ngang và dọc], bốn biểu đồ và một PivotTable được làm phẳng.
Sức mạnh xuất hiện khi bạn tạo một trang tính bao gồm nhiều đầu ra. Điều này cung cấp chế độ xem trang tổng quan về dữ liệu giúp phân tích thực sự dễ dàng. Ngay cả các giám đốc điều hành của bạn cũng có thể tương tác với trang tính của bạn nếu bạn xây dựng nó một cách chính xác.
Slicers, được xuất xưởng với Excel 2010, giúp cho dữ liệu được lọc trực quan trở nên đơn giản.
04/05
Sử dụng DAX để tạo các trường được tính toán cho dữ liệu cắt và xử lý
DAX [Biểu thức Phân tích Dữ liệu] là ngôn ngữ công thức được sử dụng trong các bảng PowerPivot, chủ yếu trong việc tạo các cột được tính toán. Xem Tham chiếu DAX của TechNet để biết tham khảo đầy đủ.
Tôi thường sử dụng các hàm ngày DAX để làm cho các trường ngày tháng hữu ích hơn. Trong Bảng Pivot thông thường trong Excel bao gồm trường ngày được định dạng đúng, bạn có thể sử dụng nhóm để bao gồm khả năng lọc hoặc nhóm theo năm, quý, tháng và ngày.
Trong PowerPivot, bạn cần phải tạo các cột này dưới dạng cột được tính toán để thực hiện điều tương tự. Thêm một cột cho mỗi cách bạn cần để lọc hoặc nhóm dữ liệu trong Bảng Pivot của bạn. Nhiều hàm ngày trong DAX giống như các công thức Excel, điều này làm cho việc này trở nên dễ dàng.
Ví dụ: sử dụng = YEAR [[ cột ngày ]] trong cột được tính toán mới để thêm năm vào tập dữ liệu của bạn trong PowerPivot. Sau đó, bạn có thể sử dụng trường YEAR mới này làm slicer hoặc nhóm trong Bảng tổng hợp của bạn.
05/05
Xuất bản Trang tổng quan thành SharePoint 2010
Nếu công ty của bạn giống như tôi, bảng điều khiển vẫn là công việc của nhóm CNTT của bạn. PowerPivot, khi kết hợp với SharePoint 2010, đặt sức mạnh của trang tổng quan vào tay người dùng của bạn.
Một trong những điều kiện tiên quyết để xuất bản biểu đồ và bảng điều khiển PowerPivot cho SharePoint 2010 là triển khai PowerPivot dành cho SharePoint trên trang trại SharePoint 2010 của bạn.
Kiểm tra PowerPivot dành cho SharePoint trên MSDN. Nhóm CNTT của bạn sẽ phải thực hiện phần này.
Luôn có nhiều cách để hoàn thành bất cứ điều gì trong Microsoft Excel. Nhưng con đường ngắn nhất sẽ dẫn chúng ta đi nhanh nhất cũng giống như trong excel có một số giải pháp không hiệu quả bằng những giải pháp khác. Gần đây, mình đã gặp phải một workbook mất hơn 20 phút để tính toán.
Khi mình bắt đầu tìm hiểu logic của bảng tính, mình tìm thấy một bảng tính tổng hợp có 60,000 hàng, 75 cột và hàng loạt các công thức VLOOKUP. Nếu bạn nghiên cứu Excel đủ nhiều, bạn chắc có lẽ cũng biết rằng con đường ngắn nhất để làm cho workbook Excel chậm đi là sử dụng hàm VLOOKUP.
Ngoài ra, khi nhắc tới VLOOKUP, mình lại nhớ ngay tới 1 điều mà 95% người dùng Excel cũng thường xuyên nhầm lẫn đó là giới hạn lưu trữ dữ liệu của Excel là 1,048,576 dòng, điều này hoàn toàn sai. Power Pivot [không phải Pivot Table] chính là đáp án mà bất kỳ ai cũng ước rằng mình biết ngay từ đầu khi sử dụng Excel. Xem giới thiệu nhanh về Power Pivot của Uniace qua video bên dưới.
MỘT MỚ HỖN LOẠN
Người tạo trang tính này đã sử dụng các công thức để kéo dữ liệu từ 20 trang tính khác vào một trang tính nguyên khối để nó có thể được đưa vào một bảng tổng hợp. Các công thức hoạt động nhưng không hiệu quả. Ví dụ: để điền các cột được gọi là Quota Jan, Quota Feb, Quota Mar, đến Quote Dec, công thức đã sử dụng 12 câu lệnh IF lồng nhau riêng biệt.
Lưu ý rằng các phần màu xanh lam đang thay đổi cột tra cứu được trả về bởi hàm VLOOKUP:
=IF[MONTH[C2]=1,VLOOKUP[A2,Quotas!$A$1:$M$9999,2,False], IF[MONTH[C2]=2,VLOOKUP[A2,Quotas!$A$1:$M$9999,3,False], IF[MONTH[C2]=3,VLOOKUP[A2,Quotas!$A$1:$M$9999,4,False], IF[MONTH[C2]=4,VLOOKUP[A2,Quotas!$A$1:$M$9999,5,False], IF[MONTH[C2]=5,VLOOKUP[A2,Quotas!$A$1:$M$9999,6,False], IF[MONTH[C2]=6,VLOOKUP[A2,Quotas!$A$1:$M$9999,7,False], IF[MONTH[C2]=7,VLOOKUP[A2,Quotas!$A$1:$M$9999,8,False], IF[MONTH[C2]=8,VLOOKUP[A2,Quotas!$A$1:$M$9999,9,False], IF[MONTH[C2]=9,VLOOKUP[A2,Quotas!$A$1:$M$9999,10,False], IF[MONTH[C2]=10,VLOOKUP[A2,Quotas!$A$1:$M$9999,11,False], IF[MONTH[C2]=11,VLOOKUP[A2,Quotas!$A$1:$M$9999,12,False], IF[MONTH[C2]=12,VLOOKUP[A2,Quotas!$A$1:$M$9999,13,False]]]]]]]]]]]].
Công thức lớn này đang cố gắng phát hiện cột nào của bảng VLOOKUP sẽ trả về. Dựa trên ngày trong cột C, các câu lệnh IF được dịch sang số tháng, sau đó chọn cột nào sẽ trả về từ bảng tra cứu. Hãy nhớ rằng, có 60.000 hàng nhân với 12 cột của công thức lớn này. Đó là 720,000 công thức VLOOKUP tìm kiếm trong một bảng có 10,000 hàng.
Sẽ đơn giản hơn nếu có một công thức VLOOKUP duy nhất và có một số logic bên trong đối số thứ ba để quyết định cột nào sẽ trả về. Trong trường hợp cụ thể này, logic cho đối số thứ ba là số tháng của ngày được tìm thấy trong C2 cộng với 1. Bạn có thể đơn giản hóa công thức thành:
=VLOOKUP [A2, Quotas! $ A $ 1: $ M $ 9999, MONTH [C2] + 1, False].
Điều gì sẽ xảy ra nếu chuyển đổi không đơn giản như vậy? Điều gì sẽ xảy ra nếu bạn đang cố gắng sử dụng tên vùng trong cột D để tìm một cột cụ thể trong bảng tra cứu? Nếu các tiêu đề trong bảng tra cứu chứa các tên vùng chính xác được tìm thấy trong hàng tiêu đề của bảng, bạn có thể sử dụng công thức MATCH để trả về số cột chính xác:
=VLOOKUP [A2, RegionTable! $ A $ 1 $ P $ 9999, MATCH [D2, RegionTable! $ 1: $ 1,0], False].
Nhưng ngay cả những giải pháp này cũng sẽ để lại 720.000 công thức VLOOKUP. Bạn vẫn sẽ có thời gian tính toán lại rất chậm.
Hơn nữa, bảng tổng hợp kết quả đã bị sai định dạng. Nhà phân tích này đã sử dụng đến việc thêm 12 trường Quota vào khu vực Row để chúng xuất hiện trong bảng tổng hợp. Bất kỳ lúc nào bạn có 12 cột hiển thị Quota [hoặc bất kỳ số nào] cho mỗi tháng, bạn có thể tạo bảng tổng hợp tốt hơn bằng cách giải nén dữ liệu gốc. Công cụ Power Query mới của Microsoft giúp bạn giải nén một cách nhanh chóng thông qua thao tác Unpivot.
Xem thêm: Cách lấy dữ liệu từ bảng này sang bảng khác trong Excel
THỜI ĐẠI CỦA BẢNG TỔNG HỢP MASTER ĐÃ SẮP KẾT THÚC
Trước đây, dữ liệu cho bảng tổng hợp phải ở một nơi. Bắt đầu từ Excel 2013, bạn có thể dễ dàng tạo bảng tổng hợp từ các bảng riêng biệt. Trước khi bạn tạo bảng tổng hợp, hãy làm theo các quy tắc đơn giản sau cho dữ liệu chính của bạn và cho mỗi bảng tra cứu:
- Chọn từng tập dữ liệu và nhấn Ctrl + T. Điều này xác định tập dữ liệu là một bảng. Đi tới trường Table trên tab Design và thay đổi tên mặc định [Table1] thành một cái gì đó mang tính mô tả.
- Đảm bảo rằng có một cột duy nhất trong bảng của bạn sẽ liên quan đến một cột duy nhất trong bảng khác. Trong Hình 1, cột G là một cột mới nối số khách hàng và tên tháng để tạo liên kết một cột với một bảng tra cứu.
H1. Power pivot là gì? – Thời đại của bảng tổng hợp master đã sắp kết thúc
- Tạo bảng tổng hợp từ bảng dữ liệu chính. Sử dụng Insert đến Pivot table. Trong hộp thoại Tạo Pivot table, hãy chọn hộp Add This Data to the Data model. [Xem Hình 2].
H2. Power pivot là gì? – Tạo bảng tổng hợp từ bảng dữ liệu chính
- Pivot table pane sẽ hiển thị các cột từ bảng gốc của bạn, nhưng một dòng phụ gần đầu bảng sẽ cung cấp các lựa chọn ACTIVE hoặc ALL [Xem Hình 3.] Chọn ALL, và bây giờ bạn có thể chọn từ bất kỳ bảng nào trong workbook. Lần đầu tiên bạn chọn một trường từ một bảng mới, ngăn Pivot Table Fields sẽ cảnh báo bạn rằng bạn cần xác định mối quan hệ giữa các bảng, như thể hiện trong Hình 4.
H3. Power pivot là gì? – Pivot table paneH4. Pivot Table Fields
5. Nhấn vào Create và xác định cột đơn có thể hoạt động như một trường khóa giữa hai bảng. [Xem Hình 5.]
H5. Nhấn vào Create và xác định cột đơn
Bây giờ bạn có thể kéo các trường từ các trang tính khác nhau vào bảng tổng hợp. Nếu bạn muốn kéo dài hàng tháng trên báo cáo, bạn có thể đặt trường Ngày vào vùng Cột thay vì kéo 12 trường khác nhau vào bảng tổng hợp.
Xem thêm: Hướng dẫn Power Query cơ bản cho người mới bắt đầu. [Phần I] và phần 2
MỞ RỘNG TÍNH TOÁN RA KHỎI GIỚI HẠN CỦA PIVOT TABLE THÔNG THƯỜNG
Khả năng kết nối nhiều bảng cùng lúc trong một mô hình dữ liệu để bạn có thể Pivot dễ dàng xuất hiện lần đầu tiên trong Excel 2010 và được cập nhật liên tục theo thời gian. Phiên bản Excel 365 đang là bản mới nhất với đầy đủ những cập nhật thú vị. Tóm lại, một công thức có 12 VLOOKUP có thể hoạt động, nhưng nó có thể làm chậm quá trình tính toán lại để thu thập thông tin.
Thay vào đó, sử dụng mô hình dữ liệu đa bảng có liên quan sẽ giải quyết được sự cố và làm cho trang tính có thể sử dụng được.
Ngoài ra, nếu bạn biết về Power Query, thì bạn có thể sử dụng tính năng Merge thay cho VLOOKUP. Xem thêm qua video bên dưới.
Cập nhật các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel