Excel là truy vấn sức mạnh thú vị
Power query là công cụ chuyển đổi dữ liệu tuyệt vời. Nó cho phép bạn nhập và chuyển đổi dữ liệu một cách dễ dàng, đồng thời giúp tạo các quy trình mạnh mẽ và có thể lặp lại cho dữ liệu của bạn Show
Nó sẽ giúp bạn tiết kiệm thời gian và công sức nếu bạn bỏ ra một khoảng thời gian nhỏ để học nó Phần tốt nhất là nó được tích hợp ngay trong Excel 2016 trở lên. Đây cũng là công nghệ tương tự được sử dụng trong Power BI, vì vậy bạn đang học hai kỹ năng dữ liệu theo yêu cầu cùng một lúc Nếu bạn chưa nghe nói về power query và những điều tuyệt vời mà nó có thể làm hoặc bạn muốn hiểu rõ hơn về nó, thì hãy xem trước Hướng dẫn đầy đủ của tôi về Power Query Dưới đây là một số mẹo và thủ thuật để giúp bạn tận dụng tối đa công cụ cực kỳ hữu ích và dễ dàng này Video hướng dẫnXóa các bước cho đến khi kết thúcĐôi khi, khi bạn đang xây dựng một truy vấn có nhiều bước, cuối cùng bạn sẽ đi sai hướng. Nếu bạn có thể xác định chính xác truy vấn của mình bắt đầu sai ở bước nào thì bạn có thể xóa bước đó và tất cả các bước sau đó để bắt đầu lại (không bắt đầu lại hoàn toàn) Trong ngăn cửa sổ Các bước đã áp dụng, nhấp chuột phải vào bước đầu tiên bạn muốn xóa rồi chọn Xóa cho đến khi kết thúc từ menu Thao tác này sẽ xóa bước đó và tất cả các bước truy vấn sau bước đó Xem trước hoặc điều hướng đến đối tượng bảngMột cột trong truy vấn của bạn có thể chứa các đối tượng bảng. Nếu trường hợp này xảy ra, các mục hàng của cột sẽ hiển thị Bảng văn bản và biểu tượng kiểu dữ liệu Bạn có thể xem trước bảng hoặc điều hướng đến bảng trong truy vấn tùy thuộc vào vị trí bạn nhấp vào trong ô Nếu bạn nhấp vào vùng trống của ô chứa đối tượng bảng, thì bạn sẽ thấy bản xem trước của bảng bên dưới truy vấn của mình Khi đưa con trỏ chuột vào chữ Table trong ô sẽ chuyển thành biểu tượng bàn tay trỏ. Khi bạn nhấp vào phần này của truy vấn, nó sẽ thêm một bước điều hướng vào truy vấn điều hướng đến bảng cụ thể đó Nhấp đúp chuột để chỉnh sửa truy vấnViệc chỉnh sửa truy vấn có thể được thực hiện theo một số cách khác nhau từ ngăn cửa sổ Truy vấn & Kết nối
Cách nhanh nhất là nhấp đúp chuột trái vào truy vấn. Thao tác này sẽ mở trình chỉnh sửa truy vấn trên truy vấn đã chọn Tự động thêm chỉ mục hàng vào bất kỳ truy vấn nào được tải vào bảngBạn có thể thêm cột chỉ mục vào bất kỳ truy vấn nào thông qua trình chỉnh sửa truy vấn nguồn, nhưng cũng có thể thêm hàng chỉ mục vào truy vấn đã được tải vào bảng theo cách đặc biệt. Chọn một ô trong đầu ra của bảng và chuyển đến tab Dữ liệu và nhấn nút Thuộc tính Lưu ý rằng tùy chọn Thuộc tính được tìm thấy trong menu bấm chuột phải của ngăn Truy vấn & Kết nối sẽ mở cửa sổ Thuộc tính Truy vấn chứ không phải cửa sổ Thuộc tính Dữ liệu Ngoài cần thiết cho mẹo này Điều này sẽ mở menu Thuộc tính dữ liệu ngoài và bạn có thể kiểm tra tùy chọn Bao gồm số hàng và nhấn nút Ok Bây giờ, lần tới khi bạn làm mới truy vấn được tải vào bảng của mình, một cột mới có tên _RowNum sẽ xuất hiện dưới dạng cột ngoài cùng bên trái và sẽ chứa chỉ mục cho số hàng bắt đầu từ 0 Thay đổi tùy chọn tải mặc địnhKhi bạn nhấn nút Đóng & Tải cho truy vấn lần đầu tiên, truy vấn nguồn sẽ tải dữ liệu vào bảng Excel trong sổ làm việc và không thêm dữ liệu vào mô hình dữ liệu. Đây là cài đặt tải mặc định trong truy vấn nguồn Nếu bạn chỉ muốn tải dữ liệu dưới dạng kết nối hoặc cần tải dữ liệu đó vào mô hình dữ liệu để sử dụng với trục nguồn sau này, thì bạn cần chọn Đóng & Tải vào thay thế và chọn các tùy chọn này mỗi khi bạn tạo một truy vấn mới Nếu bạn thấy hầu hết các truy vấn mà bạn tạo chỉ cần là một kết nối hoặc cần tải vào mô hình dữ liệu, thì bạn có thể thay đổi cài đặt tải mặc định. Chuyển đến tab Dữ liệu, sau đó chọn Nhận dữ liệu, sau đó mở Tùy chọn truy vấn Trong cài đặt Tải dữ liệu toàn cầu, chọn tùy chọn Chỉ định cài đặt tải mặc định tùy chỉnh rồi chọn hoặc bỏ chọn các tùy chọn mong muốn
Hiển thị Phông chữ Đơn cách trong Trình soạn thảo Truy vấnĐôi khi khó có thể biết liệu mỗi mục trong một cột dữ liệu có cùng độ dài ký tự hay không. Không phải tất cả các ký tự đều có cùng chiều rộng khi sử dụng phông chữ trình soạn thảo truy vấn mặc định. Điều này có thể khiến bạn khó biết liệu bạn có thể tách một trường theo độ dài ký tự một cách nhất quán cho toàn bộ cột hay không Thay đổi phông chữ thành bộ ký tự đơn cách bằng cách chuyển đến tab Xem và chọn tùy chọn Đơn cách. Điều này sẽ giúp bạn dễ dàng xem liệu mọi thứ có xếp hàng theo số lượng ký tự hay không Điều hướng các cột bằng các phím mũi tênBạn có thể chọn một cột bằng cách nhấp vào tiêu đề cột của nó. Cột sẽ chuyển sang màu lục nhạt khi được chọn và sau đó bạn sẽ có thể thực hiện các phép biến đổi khác nhau trên cột bằng cách nhấp chuột phải vào tiêu đề cột hoặc sử dụng bất kỳ lệnh biến đổi nào từ dải băng Khi một cột được chọn, bạn có thể điều hướng đến các cột khác bằng các phím mũi tên trái hoặc phải. Trên thực tế, bạn thậm chí không cần chọn cột trước, chỉ cần nhấn phím mũi tên phải và cột đầu tiên sẽ được chọn Thủ thuật tương tự có thể được thực hiện với các hàng dữ liệu. Khi một hàng được chọn bằng cách nhấp vào tiêu đề hàng, bạn có thể điều hướng đến các hàng khác bằng các phím mũi tên lên hoặc xuống. Một lần nữa, không cần chọn hàng trước, chỉ cần nhấn phím mũi tên xuống và hàng đầu tiên sẽ được chọn Nếu bạn chọn một ô trong một cột, bạn có thể sử dụng phím tắt Ctrl + Space để chọn toàn bộ cột. Bạn cũng có thể sử dụng phím tắt Ctrl + A để chọn toàn bộ bảng. Giống như trong một bảng tính Excel thông thường Nhận xét trong ngôn ngữ công thức Power QueryBạn có thể làm được nhiều việc với truy vấn nguồn chỉ từ giao diện người dùng mà không cần chạm vào bất kỳ mã M nào được tạo ở hậu trường. Nếu bạn bắt đầu khám phá mã M bằng trình chỉnh sửa nâng cao (tab Xem rồi đến Trình chỉnh sửa nâng cao), thì có thể bạn sẽ muốn thêm nhận xét vào mã của mình Bạn có thể làm điều này theo hai cách Bạn có thể tạo chú thích một dòng bằng cách bắt đầu dòng bằng dấu gạch chéo kép // Bạn có thể tạo nhận xét nhiều dòng bằng cách bắt đầu dòng đầu tiên của nhận xét bằng dấu gạch chéo /* theo sau là dấu sao, sau đó kết thúc dòng cuối cùng của nhận xét bằng dấu */ theo sau là dấu gạch chéo lên Thật không may, bất kỳ nhận xét nào bạn thêm vào trình chỉnh sửa nâng cao sẽ chỉ hiển thị trong trình chỉnh sửa nâng cao. Bạn sẽ không thể nhìn thấy chúng trong thanh công thức khi điều hướng qua các bước được áp dụng cho truy vấn của bạn Thêm nhận xét mà không cần mở Trình chỉnh sửa nâng caoCó thể thêm nhận xét mà không cần mở trình chỉnh sửa nâng cao. Chọn bước từ các bước đã áp dụng mà bạn muốn thêm nhận xét. Thêm nhận xét của bạn vào cuối bước bằng một trong hai loại nhận xét và nhấn nút Enter Nhận xét sẽ hiển thị trong thanh công thức cho đến khi bạn chuyển sang một bước khác hoặc truy vấn khác. Khi bạn quay lại, nó sẽ không hiển thị trong thanh công thức nữa, nhưng nó sẽ vẫn ở đó trong mã M và hiển thị trong trình chỉnh sửa nâng cao Thêm Nhận xét Vẫn Hiển thị trong Thanh Công thứcTôi đã nói rằng các nhận xét chỉ hiển thị từ trình chỉnh sửa nâng cao, nhưng có một cách hack để chúng hiển thị trong thanh công thức Viết nhận xét của bạn bên trong một công thức thay vì ở cuối bước. Bạn sẽ cần sử dụng các ký tự mở đầu /* và đóng */ chú thích vì chú thích sẽ cần phải được đóng để phần còn lại của công thức không được coi là một phần của chú thích Điều này sẽ vẫn hiển thị trong thanh công thức khi điều hướng qua các bước được áp dụng cho truy vấn của bạn Thêm Nhận xét với Thuộc tính Bước Truy vấnBạn có thể thêm nhận xét vào bất kỳ bước truy vấn nào được liệt kê trong ngăn cửa sổ các bước được áp dụng bằng cách sử dụng cửa sổ thuộc tính của truy vấn. Nhấp chuột phải vào truy vấn và chọn Thuộc tính từ menu Bạn sẽ có thể thêm mô tả cho bước. Mô tả này sẽ xuất hiện dưới dạng nhận xét một dòng phía trên mã M cho bước trong trình chỉnh sửa nâng cao Bạn cũng có thể thay đổi tên của bước từ cửa sổ này. Điều này cũng sẽ thay đổi các tham chiếu cho bước trong trình chỉnh sửa nâng cao Tạo một danh sách tuần tựTạo một đối tượng danh sách tuần tự trong truy vấn nguồn thật dễ dàng khi bạn biết cách. Thêm một cột tùy chỉnh với công thức sau cho danh sách bắt đầu từ 5 và kết thúc ở 10
Điều này thậm chí sẽ hoạt động bằng cách tham chiếu các cột thay vì các số được mã hóa cứng tĩnh. Sử dụng công thức tương tự như sau trong đó [Bắt đầu] và [Kết thúc] là hai cột số nguyên trong truy vấn của bạn
Nếu cột Bắt đầu hoặc Kết thúc là số thập phân, biểu thức sẽ dẫn đến lỗi Nếu số Bắt đầu lớn hơn số Kết thúc, biểu thức sẽ dẫn đến một danh sách trống Tránh phân biệt chữ hoa chữ thường với các bộ lọcTruy vấn nguồn phân biệt chữ hoa chữ thường. Điều này có thể gây ra lỗi trong quá trình phân tích dữ liệu của bạn nếu bạn không cẩn thận Nếu bạn lọc ra mục Bàn phím khỏi dữ liệu của mình và sau đó mục này hiển thị dưới dạng bàn phím trong dữ liệu nguồn thì truy vấn của bạn sẽ không lọc ra phiên bản chữ thường Điều này có thể tránh được bằng cách chuyển đổi một cột thành chữ hoa trước khi áp dụng bất kỳ bộ lọc nào Chúng tôi cũng có thể giữ lại trường hợp ban đầu trong dữ liệu của mình bằng cách sử dụng phép biến đổi UPPERCASE được tìm thấy trong tab Thêm cột. Điều này sẽ tạo một cột mới với các giá trị chữ hoa trong khi vẫn giữ nguyên cột ban đầu Bây giờ chúng ta có thể áp dụng bất kỳ bộ lọc nào trên phiên bản chữ hoa của cột và sau đó xóa cột sau. Điều này cho phép chúng tôi áp dụng bộ lọc phân biệt chữ hoa chữ thường mà không thay đổi chữ hoa chữ thường trong dữ liệu của chúng tôi Nhấp đúp chuột để đổi tên cộtCó thể bạn sẽ đổi tên rất nhiều cột trong sự nghiệp truy vấn nguồn của mình, vì vậy bạn sẽ muốn tìm hiểu cách nhanh nhất để thực hiện việc đó. Nhấp đúp chuột trái vào tiêu đề cột sẽ cho phép bạn nhanh chóng thay đổi tên cột Đổi tên cột bằng phím tắtBạn có thể đổi tên cột bằng phím tắt. Cái này chậm hơn một chút, nhưng đáng để biết đối với những người thích bàn phím hơn chuột. Với cột đã chọn, nhấn F2 trên bàn phím và bạn có thể đổi tên tiêu đề cột Điều hướng thông qua các phụ thuộc truy vấn phức tạpTính năng này có thể là cứu cánh khi sổ làm việc của bạn bắt đầu bị quá tải với các truy vấn. Có thể dễ dàng quên truy vấn nào đang sử dụng nguồn dữ liệu nào và truy vấn nào phụ thuộc lẫn nhau Chuyển đến tab Xem và nhấn lệnh Phụ thuộc truy vấn để mở trình xem phụ thuộc truy vấn. Điều này sẽ cung cấp cho bạn một bản trình bày trực quan về tất cả các truy vấn trong sổ làm việc cùng với các tiền lệ và phụ thuộc của chúng Có rất nhiều thứ đang diễn ra trong cửa sổ Phụ thuộc truy vấn
Đi sâu xuống một giá trị duy nhấtXem chi tiết bất kỳ mục nào trong truy vấn của bạn. Nhấp chuột phải vào ô và chọn Drill Down Tùy thuộc vào dữ liệu của bạn, đây có thể là một giá trị, danh sách hoặc bảng. Điều này có thể hữu ích để tạo một giá trị duy nhất từ dữ liệu của bạn mà bạn có thể tham chiếu trong phép tính cột tùy chỉnh Bật tải dữ liệu nhanh trong Power QueryNhanh hơn luôn luôn tốt hơn, phải không? . Chuyển đến tab Dữ liệu và nhấn nút Lấy dữ liệu, sau đó mở Tùy chọn truy vấn Chuyển đến cài đặt Tải dữ liệu toàn cầu và chọn hộp Tải dữ liệu nhanh. Các truy vấn của bạn sẽ mất ít thời gian hơn để tải nhưng Excel có thể không phản hồi trong khi tải Tắt tính năng Loại dữ liệu tự động phát hiệnKhi bạn nhập dữ liệu vào Excel bằng truy vấn nguồn, Excel sẽ đoán loại dữ liệu của mỗi cột dựa trên vài trăm hàng đầu tiên. Sau đó, Power query sẽ tự động tạo các bước truy vấn để tăng hàng đầu tiên thành tiêu đề cột (đối với tệp CSV) và thay đổi loại dữ liệu Nếu bạn muốn kiểm soát việc lựa chọn loại dữ liệu và không để excel tự động tạo các bước này cho bạn, thì bạn có thể tắt tùy chọn này Để mở các tùy chọn truy vấn, hãy chuyển đến tab Dữ liệu, sau đó Lấy dữ liệu rồi chọn Tùy chọn truy vấn Chuyển đến cài đặt Tải dữ liệu sổ làm việc hiện tại và chọn hộp Tự động phát hiện các loại cột và tiêu đề cho các nguồn không có cấu trúc Lần tới khi tạo truy vấn, bạn sẽ không thấy bất kỳ bước nào ngoài bước nguồn Khám phá Thư viện Hàm của Power QueryNgôn ngữ mã M truy vấn nguồn là ngôn ngữ chức năng. Điều này có nghĩa là mã M chủ yếu được xây dựng với các chức năng thực hiện những việc cụ thể dựa trên đầu vào của chúng. Viết mã M trong power query cũng giống như xây dựng bằng Lego, có nhiều loại mảnh Lego khác nhau và mỗi mảnh được thiết kế cho một mục đích cụ thể Mã M đi kèm với nhiều lựa chọn chức năng để sử dụng. Trên thực tế, bạn có thể khám phá toàn bộ thư viện các hàm có sẵn từ bên trong trình chỉnh sửa truy vấn nguồn Tạo một truy vấn trống mới, vào tab Data nhấn lệnh Get Data rồi chọn From Other Sources rồi chọn Blank Query. Bây giờ trong thanh công thức, hãy nhập =#shared và nhấn Enter Sau đó, truy vấn nguồn sẽ hiển thị tất cả các mục có sẵn cho sổ làm việc. Điều này sẽ bao gồm tất cả các chức năng truy vấn nguồn Nếu bạn nhấp vào bất kỳ chức năng nào được liệt kê, bạn sẽ được đưa đến hướng dẫn trợ giúp nhỏ cho chức năng đó
Tách truy vấn thành hai phầnCó thể truy vấn của bạn quá dài hoặc có thể bạn muốn sử dụng phần đầu tiên của truy vấn làm nguồn trong các truy vấn khác. Nếu trường hợp này xảy ra, bạn có thể chia truy vấn thành hai phần Nhấp chuột phải vào bước mà bạn muốn chia truy vấn, sau đó chọn Trích xuất trước đó từ menu. Thao tác này sẽ trích xuất các bước trước bước đã chọn thành một truy vấn mới. Sau đó, bạn sẽ được nhắc đặt tên cho truy vấn mới Sau đó, các bước còn lại sẽ tham chiếu truy vấn mới làm nguồn của nó Sao chép và dán truy vấn vào sổ làm việc mớiNếu bạn cần sử dụng lại truy vấn nguồn từ sổ làm việc trước đó, bạn có thể dễ dàng sao chép và dán nó từ sổ làm việc cũ sang sổ làm việc mới Trong cửa sổ Truy vấn & Kết nối của sổ làm việc cũ, chọn truy vấn bạn muốn sao chép, sau đó nhấp chuột phải và chọn Sao chép từ menu. Bây giờ điều hướng đến sổ làm việc mới và nhấp chuột phải vào cửa sổ Truy vấn & Kết nối và chọn Dán từ menu Thao tác này sẽ sao chép tất cả các truy vấn đã chọn cùng với tất cả các truy vấn mà chúng tham chiếu Nếu bất kỳ truy vấn nào được sao chép hoặc truy vấn mà chúng phụ thuộc vào, tham chiếu một bảng trong sổ làm việc cũ thì những truy vấn đó sẽ hiển thị thông báo lỗi Tải xuống không hoàn tất. Bạn sẽ cần sao chép riêng các bảng đó vào sổ làm việc mới để khắc phục sự cố này Nhấp đúp chuột để đổi tên một truy vấnBạn có thể nhanh chóng đổi tên bất kỳ truy vấn nào từ ngăn danh sách truy vấn ở phía bên trái của trình chỉnh sửa truy vấn. Nhấp đúp chuột trái vào truy vấn mà bạn muốn đổi tên, sau đó nhập tên mới và nhấn Enter để xác nhận thay đổi Bấm đúp để Ẩn hoặc Hiển thị Dải băng Trình soạn thảo Truy vấnNếu bạn thấy dải băng trong trình soạn thảo truy vấn nguồn gây cản trở, bạn có thể ẩn. Nhấp đúp chuột trái vào bất kỳ tab ruy-băng nào để ẩn ruy-băng. Nhấp đúp chuột trái vào tab một lần nữa để hiển thị dải băng Đúng, đây là thủ thuật tương tự từ dải băng sổ làm việc thông thường nhưng nó cũng có thể được sử dụng trong trình chỉnh sửa truy vấn nguồn Vô hiệu hóa phát hiện mối quan hệĐây là một mẹo khác có khả năng tăng tốc các truy vấn truy vấn sức mạnh của bạn Khi bạn tải dữ liệu vào mô hình dữ liệu từ truy vấn nguồn, Excel sẽ cố gắng tìm và xây dựng mối quan hệ giữa dữ liệu mới và các bảng khác trong mô hình dữ liệu. Bước xử lý bổ sung này sẽ làm chậm truy vấn của bạn Nếu bạn luôn tải dữ liệu của mình vào mô hình dữ liệu thì bạn có thể muốn tắt tính năng này để tiết kiệm thời gian. Chuyển đến tab Dữ liệu, sau đó nhấn nút Lấy dữ liệu và mở Tùy chọn truy vấn Trong cài đặt Tải dữ liệu sổ làm việc hiện tại, hãy bỏ chọn tùy chọn Tạo mối quan hệ giữa các bảng khi thêm vào Mô hình dữ liệu lần đầu tiên. Điều này sẽ ngăn Excel tạo mối quan hệ giữa các bảng trong mô hình dữ liệu Excel Power Query có tốt không?Tuyệt vời cho người dùng Excel nhưng đôi khi có thể chậm
. Nó có thể kết nối với nhiều nguồn khác nhau và cung cấp các tính năng chuẩn bị dữ liệu cơ bản như lọc, thêm/xóa cột và cột được tính toán.
Tại sao Power Query tốt hơn Excel?Power BI có thể kết nối với số lượng lớn nguồn dữ liệu, trong khi khả năng kết nối của Excel bị hạn chế. Ngoài ra, không giống như Excel, Power BI có thể dễ dàng sử dụng từ thiết bị di động. Power BI xử lý nhanh hơn Excel . Bảng điều khiển Power BI hấp dẫn, tương tác và tùy chỉnh trực quan hơn so với trong Excel.
Các truy vấn nguồn có hữu ích không?Power Query trong Excel là giải pháp cho mọi vấn đề về dữ liệu lộn xộn của bạn. Nó có thể thực hiện rất nhiều chuyển đổi dữ liệu hữu ích sẽ giúp làm sạch dữ liệu của bạn để dữ liệu sẵn sàng được sử dụng cho các phân tích sâu hơn
Power Query có được tích hợp trong Excel không?Trải nghiệm Power Query khả dụng trong tất cả các phiên bản Windows độc lập Excel 2016 trở lên và các gói đăng ký Microsoft 365 trên tab Dữ liệu trong nhóm Nhận & chuyển đổi . Bảng sau đây hiển thị các tính năng cơ bản và nâng cao có sẵn theo phiên bản. |