Các thuật ngữ “Power Query”, “Power Pivot”, “Power BI” và các “quyền hạn” khác ngày càng xuất hiện nhiều hơn trong các bài báo và tài liệu về Microsoft Excel. Theo kinh nghiệm của tôi, không phải ai cũng hiểu rõ ràng điều gì ẩn sau những khái niệm này, cách chúng được kết nối với nhau và cách chúng có thể giúp một người dùng Excel đơn giản.
Hãy làm rõ tình hình.
Truy vấn nguồn
Trở lại năm 2013, một nhóm các nhà phát triển được tạo đặc biệt trong Microsoft đã phát hành một phần bổ trợ miễn phí cho Excel. Truy vấn nguồn (các tên khác là Data Explorer, Get & Transform), có thể làm được nhiều việc hữu ích cho công việc hàng ngày:
- Tải lên dữ liệu trong Excel từ gần 40 nguồn khác nhau, bao gồm cơ sở dữ liệu (SQL, Oracle, Access, Teradata…), hệ thống ERP công ty (SAP, Microsoft Dynamics, 1C…), dịch vụ Internet (Facebook, Google Analytics, hầu hết mọi trang web).
- Thu thập dữ liệu từ Các tập tin tất cả các kiểu dữ liệu chính (XLSX, TXT, CSV, JSON, HTML, XML…), cả đơn lẻ và hàng loạt - từ tất cả các tệp trong thư mục được chỉ định. Từ sổ làm việc Excel, bạn có thể tự động tải xuống dữ liệu từ tất cả các trang tính cùng một lúc.
- Làm sạch dữ liệu đã nhận từ “rác”: cột hoặc hàng bổ sung, số lần lặp lại, thông tin dịch vụ trong “tiêu đề”, khoảng trắng thừa hoặc ký tự không in được, v.v.
- Đưa dữ liệu vào gọi món: chữ hoa đúng, số dưới dạng văn bản, điền vào khoảng trống, thêm "nắp" chính xác của bảng, phân tích cú pháp văn bản "dính" thành các cột và dán lại, chia ngày tháng thành các thành phần, v.v.
- bằng mọi cách có thể biến đổi bảng, đưa chúng về dạng mong muốn (lọc, sắp xếp, thay đổi thứ tự cột, chuyển vị, thêm tổng, mở rộng bảng chéo thành phẳng và thu gọn lại).
- Thay thế dữ liệu từ bảng này sang bảng khác bằng cách khớp một hoặc nhiều tham số, tức là hàm thay thế tốt VPR (Vlookup) và các chất tương tự của nó.
Power Query được tìm thấy trong hai phiên bản: dưới dạng bổ trợ riêng cho Excel 2010-2013, có thể được tải xuống từ trang web chính thức của Microsoft và là một phần của Excel 2016. Trong trường hợp đầu tiên, sau khi cài đặt, một tab riêng biệt sẽ xuất hiện trong Excel:
Trong Excel 2016, tất cả chức năng của Power Query đã được tích hợp sẵn theo mặc định và nằm trên tab Ngày (Ngày) như một nhóm Nhận và chuyển đổi (Nhận và chuyển đổi):
Khả năng của các tùy chọn này là hoàn toàn giống nhau.
Đặc điểm cơ bản của Power Query là tất cả các hành động nhập và chuyển đổi dữ liệu được lưu trữ dưới dạng truy vấn - một chuỗi các bước trong ngôn ngữ lập trình Power Query nội bộ, được gọi ngắn gọn là “M”. Các bước luôn có thể được chỉnh sửa và phát lại bất kỳ số lần nào (truy vấn làm mới).
Cửa sổ Power Query chính thường trông giống như sau:
Theo tôi, đây là tiện ích bổ sung hữu ích nhất được liệt kê trong bài viết này cho nhiều người dùng. Rất nhiều tác vụ mà bạn phải bóp méo kinh khủng với các công thức hoặc viết macro giờ đây đã được thực hiện dễ dàng và đẹp mắt trong Power Query. Có, và với việc cập nhật kết quả tự động sau đó. Và xem xét nó miễn phí, xét về tỷ lệ chất lượng giá cả, Power Query đơn giản là không có đối thủ cạnh tranh và là thứ tuyệt đối phải có đối với bất kỳ người dùng Excel trung cấp nào ngày nay.
powerpivot
Power Pivot cũng là một phần bổ trợ cho Microsoft Excel, nhưng được thiết kế cho các tác vụ hơi khác. Nếu Power Query tập trung vào việc nhập và xử lý, thì Power Pivot chủ yếu cần thiết để phân tích phức tạp với lượng lớn dữ liệu. Theo ước tính đầu tiên, bạn có thể coi Power Pivot như một bảng tổng hợp ưa thích.
Các nguyên tắc chung khi làm việc trong Power Pivot như sau:
- Chúng tôi là người đầu tiên tải dữ liệu trong Power Pivot - 15 nguồn khác nhau được hỗ trợ: cơ sở dữ liệu chung (SQL, Oracle, Access…), tệp Excel, tệp văn bản, nguồn cấp dữ liệu. Ngoài ra, bạn có thể sử dụng Power Query làm nguồn dữ liệu, giúp phân tích gần như ăn tạp.
- Sau đó giữa các bảng được tải kết nối được cấu hình hoặc, như họ nói, được tạo ra Mô hình dữ liệu. Điều này sẽ cho phép trong tương lai xây dựng báo cáo trên bất kỳ trường nào từ các bảng hiện có như thể nó là một bảng. Và không có VPR nữa.
- Nếu cần, các phép tính bổ sung sẽ được thêm vào Mô hình Dữ liệu bằng cách sử dụng các cột được tính toán (tương tự như cột có công thức trong “bảng thông minh”) và các biện pháp (một giá trị tương tự của trường được tính toán trong phần tóm tắt). Tất cả điều này được viết bằng ngôn ngữ nội bộ Power Pivot đặc biệt được gọi là DAX (Phân tích dữ liệu eXpressions).
- Trên trang tính Excel, theo Mô hình Dữ liệu, các báo cáo mà chúng tôi quan tâm được xây dựng dưới dạng bảng xoay và sơ đồ.
Cửa sổ Power Pivot chính trông giống như sau:
Và đây là cách Mô hình Dữ liệu trông như thế nào, tức là tất cả các bảng được tải với các mối quan hệ đã tạo:
Power Pivot có một số tính năng làm cho nó trở thành một công cụ duy nhất cho một số tác vụ:
- Trong Power Pivot không giới hạn dòng (như trong Excel). Bạn có thể tải các bảng có kích thước bất kỳ và dễ dàng làm việc với chúng.
- Power Pivot rất giỏi trong việc nén dữ liệu khi tải chúng vào Model. Một tệp văn bản gốc 50MB có thể dễ dàng chuyển thành 3-5MB sau khi tải xuống.
- Vì Power Pivot “ẩn mình” trên thực tế có một công cụ cơ sở dữ liệu chính thức, nó đối phó với lượng lớn thông tin rất nhanh. Cần phân tích 10-15 triệu bản ghi và xây dựng một bản tóm tắt? Và tất cả những điều này trên một máy tính xách tay cũ? Không vấn đề gì!
Thật không may, Power Pivot vẫn chưa được bao gồm trong tất cả các phiên bản Excel. Nếu bạn có Excel 2010, bạn có thể tải xuống miễn phí từ trang web của Microsoft. Nhưng nếu bạn có Excel 2013-2016, thì tất cả phụ thuộc vào giấy phép của bạn, bởi vì. trong một số phiên bản, nó được bao gồm (ví dụ: Office Pro Plus) và trong một số phiên bản thì không (Office 365 Home, Office 365 Personal, v.v.) Bạn có thể đọc thêm về điều này tại đây.
Bản đồ điện
Tiện ích bổ sung này xuất hiện lần đầu tiên vào năm 2013 và ban đầu được gọi là GeoFlow. Nó nhằm mục đích trực quan hóa dữ liệu địa lý, tức là thông tin số trên bản đồ địa lý. Dữ liệu ban đầu để hiển thị được lấy từ cùng một Mô hình Dữ liệu Power Pivot (xem đoạn trước).
Bạn có thể tải xuống phiên bản demo của Power Map (gần như không khác với phiên bản đầy đủ) có thể được tải xuống lại hoàn toàn miễn phí từ trang web của Microsoft. Phiên bản đầy đủ được bao gồm trong một số gói Microsoft Office 2013-2016 cùng với Power Pivot - ở dạng nút Bản đồ 3D chuyển hướng Chèn (Chèn - bản đồ 3D):
Các tính năng chính của Power Map:
- Maps có thể là cả phẳng và đồ sộ (hình cầu).
- Bạn có thể sử dụng một số các loại hình ảnh hóa (biểu đồ, biểu đồ bong bóng, bản đồ nhiệt, điền diện tích).
- Bạn có thể thêm đo thời gian, tức là tạo hoạt ảnh cho quá trình và xem nó phát triển.
- Bản đồ được tải từ dịch vụ Bing Maps, tức là Bạn cần kết nối internet rất nhanh để xem. Đôi khi có những khó khăn với việc nhận dạng chính xác các địa chỉ, bởi vì. tên trong dữ liệu không phải lúc nào cũng khớp với tên của Bing Maps.
- Trong phiên bản Power Map đầy đủ (không phải demo), bạn có thể sử dụng bản đồ có thể tải xuống, ví dụ, để hình dung khách đến trung tâm mua sắm hoặc giá các căn hộ trong tòa nhà dân cư ngay trên sơ đồ tòa nhà.
- Dựa trên hình ảnh địa lý đã tạo, bạn có thể tạo video trực tiếp trong Power Map (ví dụ) để chia sẻ chúng sau này với những người chưa cài đặt bổ trợ hoặc đưa chúng vào bản trình bày Power Point.
chế độ xem quyền lực
Được giới thiệu lần đầu tiên trong Excel 2013, phần bổ trợ này được thiết kế để làm cho dữ liệu của bạn trở nên sống động với các biểu đồ, biểu đồ, bản đồ và bảng tương tác. Đôi khi các điều khoản được sử dụng cho việc này. bảng điều khiển (bảng điều khiển) or bảng điều khiển (phiếu ghi điểm). Điểm mấu chốt là bạn có thể chèn một trang tính đặc biệt không có ô vào tệp Excel của mình - một trang chiếu Power View, nơi bạn có thể thêm văn bản, hình ảnh và nhiều loại hình trực quan khác nhau dựa trên dữ liệu của bạn từ Mô hình Dữ liệu Power Pivot.
Nó sẽ trông giống như thế này:
Các sắc thái ở đây là:
- Dữ liệu ban đầu được lấy từ cùng một nơi - từ Mô hình Dữ liệu Power Pivot.
- Để làm việc với Power View, bạn cần cài đặt Silverlight trên máy tính của mình - công cụ tương tự của Flash (miễn phí) của Microsoft.
Nhân tiện, trên trang web của Microsoft, có một khóa đào tạo rất tốt về Power View trong.
Power BI
Không giống như những phần trước, Power BI không phải là một phần bổ trợ cho Excel mà là một sản phẩm riêng biệt, là một bộ công cụ để phân tích và trực quan hóa doanh nghiệp. Nó bao gồm ba yếu tố chính:
1. Máy tính để bàn Power BI - một chương trình để phân tích và trực quan hóa dữ liệu, bao gồm tất cả các chức năng của Power Query và các tiện ích bổ sung Power Pivot + các cơ chế trực quan hóa được cải tiến từ Power View và Power Map. Bạn có thể tải xuống và cài đặt miễn phí từ trang web của Microsoft.
Trong Power BI Desktop, bạn có thể:
- Tải dữ liệu từ hơn 70 nguồn khác nhau (như trong Power Query + các đầu nối bổ sung).
- ràng buộc bảng để tạo mô hình (như trong Power Pivot)
- Thêm các phép tính bổ sung vào dữ liệu với các biện pháp и các cột được tính toán trên DAX (như trong Power Pivot)
- Tạo dữ liệu đẹp dựa trên báo cáo tương tác với các kiểu hình ảnh hóa khác nhau (rất giống với Power View, nhưng thậm chí còn tốt hơn và mạnh mẽ hơn).
- Xuất bản đã tạo báo cáo trên trang Dịch vụ Power BI (xem điểm tiếp theo) và chia sẻ chúng với đồng nghiệp. Hơn nữa, có thể trao các quyền khác nhau (đọc, chỉnh sửa) cho những người khác nhau.
2. Dịch vụ trực tuyến Power BI - nói một cách đơn giản, đây là trang web mà bạn và mỗi người dùng trong công ty của bạn sẽ có “hộp cát” (không gian làm việc) của riêng họ, nơi bạn có thể tải lên các báo cáo được tạo trong Power BI Desktop. Ngoài việc xem, nó thậm chí còn cho phép bạn chỉnh sửa chúng, tái tạo gần như tất cả các chức năng của Power BI Desktop trực tuyến. Bạn cũng có thể mượn các hình ảnh trực quan riêng lẻ từ các báo cáo của người khác tại đây, thu thập các trang tổng quan của chính tác giả của bạn từ chúng.
Nó trông giống như thế này:
3. Điện BI di động là ứng dụng dành cho iOS / Android / Windows để kết nối với Dịch vụ Power BI và xem (không chỉnh sửa) các báo cáo và trang tổng quan đã tạo ngay trên màn hình điện thoại hoặc máy tính bảng của bạn một cách thuận tiện. Bạn có thể tải về (hoàn toàn miễn phí) tại đây.
Ví dụ: trên iPhone, báo cáo được tạo ở trên trông giống như sau:
Và tất cả điều này trong khi vẫn duy trì tính tương tác và hoạt ảnh + bị giam cầm khi chạm và vẽ trên màn hình bằng bút. Rất thoải mái. Do đó, thông tin kinh doanh luôn sẵn sàng cho tất cả những người chủ chốt của công ty vào bất kỳ lúc nào và ở bất kỳ đâu - chỉ cần truy cập Internet.
Các gói định giá Power BI. Power BI Desktop và Mobile đều miễn phí và hầu hết các tính năng của Dịch vụ Power BI cũng miễn phí. Vì vậy, để sử dụng cá nhân hoặc sử dụng trong một công ty nhỏ, bạn không cần phải trả một xu nào cho tất cả những điều trên và bạn có thể an toàn tiếp tục chương trình Miễn phí. Nếu bạn muốn chia sẻ báo cáo với đồng nghiệp và quản lý quyền truy cập của họ, bạn sẽ phải truy cập BÚP BÊ ($ 10 mỗi tháng cho mỗi người dùng). Còn một số nữa không cao cấp - dành cho các công ty lớn (> 500 người dùng) yêu cầu dung lượng lưu trữ và máy chủ riêng biệt cho dữ liệu.
- Biểu đồ Project Gantt trong Excel với Power Query
- Cách tạo cơ sở dữ liệu trong Excel bằng Power Pivot
- Hình ảnh hóa chuyển động dọc theo tuyến đường trên bản đồ trong Power Map