Nội dung
Một trong những video được xem nhiều nhất trên kênh YouTube của tôi là video về Flash Fill trong Microsoft Excel. Bản chất của công cụ này là nếu bạn cần bằng cách nào đó chuyển đổi dữ liệu nguồn của mình, thì bạn chỉ cần bắt đầu nhập kết quả bạn muốn nhận được vào cột liền kề. Sau khi một số ô được nhập theo cách thủ công (thường là 2-3 ô là đủ), Excel sẽ “hiểu” logic của các phép biến đổi bạn cần và tự động tiếp tục những gì bạn đã nhập, hoàn thành tất cả công việc đơn điệu cho bạn:
Tinh hoa của hiệu quả. Nút “làm đúng” kỳ diệu mà tất cả chúng ta đều yêu thích, phải không?
Trên thực tế, có một công cụ tương tự như vậy trong Power Query - nó được gọi là Cột từ các ví dụ (Cột từ các ví dụ). Trên thực tế, đây là một trí tuệ nhân tạo nhỏ được tích hợp trong Power Query có thể nhanh chóng học hỏi từ dữ liệu của bạn và sau đó chuyển đổi nó. Chúng ta hãy xem xét kỹ hơn các khả năng của nó trong một số tình huống thực tế để hiểu nó có thể hữu ích ở đâu đối với chúng ta trong các tác vụ thực tế.
Ví dụ 1. Dán / cắt văn bản
Giả sử chúng ta có một bảng “thông minh” như vậy trong Excel với dữ liệu về nhân viên:
Tải nó vào Power Query theo cách tiêu chuẩn - bằng nút Từ Bảng / Phạm vi chuyển hướng Ngày (Dữ liệu - Từ Bảng / Phạm vi).
Giả sử chúng ta cần thêm một cột có họ và tên viết tắt cho mỗi nhân viên (Ivanov SV cho nhân viên đầu tiên, v.v.). Để giải quyết vấn đề này, bạn có thể sử dụng một trong hai phương pháp:
- nhấp chuột phải vào tiêu đề cột có dữ liệu nguồn và chọn lệnh Thêm cột từ các ví dụ (Thêm cột từ các ví dụ);
- chọn một hoặc nhiều cột có dữ liệu và trên tab Thêm một cột chọn một đội Cột từ các ví dụ. Tại đây, trong danh sách thả xuống, bạn có thể chỉ định xem tất cả hay chỉ các cột đã chọn cần được phân tích.
Sau đó, mọi thứ thật đơn giản - trong cột xuất hiện ở bên phải, chúng tôi bắt đầu nhập các ví dụ về kết quả mong muốn và trí tuệ nhân tạo được tích hợp trong Power Query sẽ cố gắng hiểu logic chuyển đổi của chúng tôi và tiếp tục tự nó tiếp tục:
Nhân tiện, bạn có thể nhập các tùy chọn chính xác vào bất kỳ ô nào của cột này, tức là không nhất thiết phải từ trên xuống và trong một hàng. Ngoài ra, bạn có thể dễ dàng thêm hoặc xóa các cột khỏi phân tích sau này bằng cách sử dụng các hộp kiểm trên thanh tiêu đề.
Hãy chú ý đến công thức ở đầu cửa sổ - đây là những gì Power Query thông minh tạo ra để nhận được kết quả chúng ta cần. Nhân tiện, đây là sự khác biệt cơ bản giữa công cụ này và Lấp đầy tức thì trong Excel. Việc điền ngay lập tức hoạt động giống như một “hộp đen” - chúng không cho chúng ta thấy logic của các phép biến đổi, mà chỉ đơn giản là đưa ra các kết quả được tạo sẵn và chúng ta coi đó là điều hiển nhiên. Ở đây mọi thứ đều minh bạch và bạn luôn có thể hiểu rõ ràng chính xác những gì đang xảy ra với dữ liệu.
Nếu bạn thấy rằng Power Query "bắt được ý tưởng", thì bạn có thể nhấn nút một cách an toàn OK hoặc phím tắt Ctrl+đăng ký hạng mục thi - một cột tùy chỉnh có công thức do Power Query phát minh sẽ được tạo. Nhân tiện, sau này có thể dễ dàng chỉnh sửa nó thành một cột được tạo thủ công thông thường (bằng lệnh Thêm một cột - Cột tùy chỉnh) bằng cách nhấp vào biểu tượng bánh răng ở bên phải của tên bước:
Ví dụ 2: Trường hợp như trong câu
Nếu bạn bấm chuột phải vào tiêu đề cột có văn bản và chọn lệnh Chuyển đổi (Biến đổi), sau đó bạn có thể thấy ba lệnh chịu trách nhiệm thay đổi thanh ghi:
Thuận tiện và tuyệt vời, nhưng trong danh sách này, ví dụ, cá nhân tôi luôn thiếu một lựa chọn nữa - trường hợp như trong câu, khi viết hoa (viết hoa) không trở thành chữ cái đầu tiên trong mỗi từ, mà chỉ là chữ cái đầu tiên trong ô, và phần còn lại của văn bản khi Điều này được hiển thị bằng chữ thường (nhỏ).
Tính năng còn thiếu này rất dễ thực hiện với trí thông minh nhân tạo Các cột từ các ví dụ - chỉ cần nhập một số tùy chọn để Power Query tiếp tục theo cùng một tinh thần:
Như một công thức ở đây, Power Query sử dụng một loạt các hàm Văn bản.Upper и Văn bản.Lower, chuyển đổi văn bản thành chữ hoa và chữ thường, tương ứng và các hàm Văn bản.Bắt đầu и Văn bản.Mid - tương tự của các hàm Excel LEFT và PSTR, có thể trích xuất một chuỗi con từ văn bản từ bên trái và từ giữa.
Ví dụ 3. Hoán vị các từ
Đôi khi, khi xử lý dữ liệu nhận được, cần phải sắp xếp lại các từ trong các ô theo một trình tự nhất định. Tất nhiên, bạn có thể chia cột thành các cột từ riêng biệt bằng dấu phân cách và sau đó dán nó lại theo thứ tự đã chỉ định (đừng quên thêm dấu cách), nhưng với sự trợ giúp của công cụ Cột từ các ví dụ mọi thứ sẽ dễ dàng hơn nhiều:
Ví dụ 4: Chỉ số
Một nhiệm vụ rất quan trọng khác là chỉ lấy ra các số (số) từ nội dung của ô. Như trước đây, sau khi tải dữ liệu vào Power Query, hãy chuyển đến tab Thêm một cột - Cột từ các ví dụ và điền vào một vài ô theo cách thủ công để chương trình hiểu chính xác những gì chúng ta muốn lấy:
Bingo!
Một lần nữa, bạn nên nhìn vào đầu cửa sổ để đảm bảo rằng Truy vấn đã tạo công thức một cách chính xác - trong trường hợp này, nó chứa một hàm Chữ. Lựa chọn, như bạn có thể đoán, trích xuất các ký tự đã cho từ văn bản nguồn theo danh sách. Sau đó, danh sách này, tất nhiên, có thể dễ dàng chỉnh sửa trong thanh công thức nếu cần.
Ví dụ 5: Chỉ văn bản
Tương tự như ví dụ trước, bạn có thể kéo ra và ngược lại - chỉ văn bản, xóa tất cả các số, dấu chấm câu, v.v.
Trong trường hợp này, một hàm đã có ý nghĩa trái ngược nhau được sử dụng - Text.Remove, sẽ xóa các ký tự khỏi chuỗi ban đầu theo một danh sách nhất định.
Ví dụ 6: Trích xuất dữ liệu từ cháo chữ và số
Power Query cũng có thể trợ giúp trong những trường hợp khó khăn hơn, khi bạn cần trích xuất thông tin hữu ích từ cháo chữ và số trong một ô, chẳng hạn như lấy số tài khoản từ mô tả mục đích thanh toán trên bảng sao kê ngân hàng:
Lưu ý rằng công thức chuyển đổi do Power Query tạo ra có thể khá phức tạp:
Để dễ đọc và dễ hiểu, nó có thể được chuyển đổi thành một hình thức lành mạnh hơn nhiều bằng cách sử dụng một dịch vụ trực tuyến miễn phí. Trình định dạng Power Query:
Điều rất hữu ích - tôn trọng những người sáng tạo!
Ví dụ 7: Ngày chuyển đổi
Công cụ Cột từ các ví dụ cũng có thể được áp dụng cho các cột ngày tháng hoặc ngày giờ. Khi bạn nhập các chữ số đầu tiên của ngày, Power Query sẽ hiển thị hữu ích danh sách tất cả các tùy chọn chuyển đổi có thể có:
Vì vậy, bạn có thể dễ dàng chuyển đổi ngày gốc sang bất kỳ định dạng lạ nào, chẳng hạn như “năm-tháng-ngày”:
Ví dụ 8: Phân loại
Nếu chúng ta sử dụng công cụ Cột từ các ví dụ vào một cột có dữ liệu số, nó hoạt động theo cách khác. Giả sử chúng tôi có kết quả kiểm tra nhân viên được tải vào Power Query (điểm có điều kiện trong phạm vi 0-100) và chúng tôi sử dụng cách tính điểm có điều kiện sau:
- Bậc thầy - những người đạt hơn 90 điểm
- Các chuyên gia - cho điểm từ 70 đến 90
- Người dùng - từ 30 đến 70
- Người mới bắt đầu - những người đạt điểm dưới 30
Nếu chúng tôi thêm một cột từ các ví dụ vào danh sách và bắt đầu sắp xếp các điểm phân cấp này theo cách thủ công, thì Power Query sẽ sớm chọn ra ý tưởng của chúng tôi và thêm một cột có công thức, trong đó các toán tử được lồng vào nhau if logic sẽ được thực hiện, rất giống với những gì chúng ta cần:
Một lần nữa, bạn không thể nhấn hết tình huống mà hãy nhấn vào OK và sau đó sửa các giá trị ngưỡng đã có trong công thức - theo cách này sẽ nhanh hơn:
Kết luận
Chắc chắn là một công cụ Cột từ các ví dụ không phải là một “viên thuốc ma thuật” và sớm hay muộn, sẽ có những tình huống không chuẩn hoặc đặc biệt là các trường hợp bị bỏ quên về một “trang trại tập thể” trong dữ liệu, khi Power Query sẽ thất bại và sẽ không thể giải quyết những gì chúng ta muốn một cách chính xác cho chúng tôi. Tuy nhiên, như một công cụ phụ trợ, nó là rất tốt. Thêm vào đó, bằng cách nghiên cứu các công thức mà anh ấy tạo ra, bạn có thể mở rộng kiến thức của mình về các hàm của ngôn ngữ M, ngôn ngữ này sẽ luôn hữu ích trong tương lai.
- Phân tích cú pháp văn bản với biểu thức chính quy (RegExp) trong Power Query
- Tìm kiếm văn bản mờ trong Power Query
- Điền vào Flash trong Microsoft Excel