Gần đây hơn, chúng ta đã thảo luận về việc sử dụng hàm FILTER.XML để nhập dữ liệu XML từ Internet - nhiệm vụ chính mà hàm này thực hiện. Tuy nhiên, trên đường đi, một công dụng bất ngờ và tuyệt vời khác của chức năng này đã xuất hiện - để nhanh chóng phân chia văn bản dính thành các ô.
Giả sử chúng ta có một cột dữ liệu như sau:
Tất nhiên, để tiện theo dõi, tôi xin chia thành các cột riêng biệt: tên công ty, thành phố, đường phố, số nhà. Bạn có thể làm điều này theo nhiều cách khác nhau:
- Sử dụng Văn bản theo cột từ tab Ngày (Dữ liệu - Văn bản thành cột) và đi ba bước Trình phân tích cú pháp văn bản. Nhưng nếu dữ liệu thay đổi vào ngày mai, bạn sẽ phải lặp lại toàn bộ quá trình một lần nữa.
- Tải dữ liệu này vào Power Query và chia nó ở đó, sau đó tải nó trở lại trang tính, sau đó cập nhật truy vấn khi dữ liệu thay đổi (điều này đã dễ dàng hơn).
- Nếu bạn cần cập nhật nhanh chóng, thì bạn có thể viết một số công thức rất phức tạp để tìm dấu phẩy và trích xuất văn bản giữa chúng.
Và bạn có thể làm điều đó một cách thanh lịch hơn và sử dụng hàm FILTER.XML, nhưng nó liên quan gì đến nó?
Hàm FILTER.XML nhận như đối số ban đầu của nó một mã XML - văn bản được đánh dấu bằng các thẻ và thuộc tính đặc biệt, sau đó phân tích cú pháp nó thành các thành phần của nó, trích xuất các đoạn dữ liệu mà chúng ta cần. Mã XML thường trông giống như sau:
Trong XML, mỗi phần tử dữ liệu phải được đặt trong các thẻ. Thẻ là một số văn bản (trong ví dụ trên là người quản lý, tên, lợi nhuận) được đặt trong dấu ngoặc nhọn. Các thẻ luôn đi theo cặp - mở và đóng (có thêm dấu gạch chéo ở đầu).
Hàm FILTER.XML có thể dễ dàng trích xuất nội dung của tất cả các thẻ mà chúng ta cần, chẳng hạn như tên của tất cả người quản lý và (quan trọng nhất) hiển thị tất cả chúng cùng một lúc trong một danh sách. Vì vậy, nhiệm vụ của chúng ta là thêm các thẻ vào văn bản nguồn, biến nó thành mã XML phù hợp cho việc phân tích tiếp theo bằng hàm FILTER.XML.
Nếu chúng tôi lấy địa chỉ đầu tiên từ danh sách của mình làm ví dụ, thì chúng tôi sẽ cần chuyển nó thành cấu trúc này:
Tôi đã gọi thẻ văn bản mở và đóng toàn cầu tvà các thẻ đóng khung mỗi phần tử là s., nhưng bạn có thể sử dụng bất kỳ chỉ định nào khác - điều đó không quan trọng.
Nếu chúng tôi xóa thụt lề và ngắt dòng khỏi mã này - nhân tiện, hoàn toàn, tùy chọn và chỉ được thêm vào để rõ ràng, thì tất cả điều này sẽ biến thành một dòng:
Và nó đã có thể được lấy tương đối dễ dàng từ địa chỉ nguồn bằng cách thay thế dấu phẩy trong đó bằng một vài thẻ sử dụng chức năng THAY THẾ (THAY THẾ) và dán biểu tượng & ở đầu và cuối của thẻ mở và thẻ đóng:
Để mở rộng phạm vi kết quả theo chiều ngang, chúng tôi sử dụng hàm tiêu chuẩn GIAO THÔNG (VẬN CHUYỂN), gói công thức của chúng tôi trong đó:
Một tính năng quan trọng của toàn bộ thiết kế này là trong phiên bản mới của Office 2021 và Office 365 có hỗ trợ mảng động, không cần cử chỉ đặc biệt nào để nhập liệu - chỉ cần nhập và nhấp vào đăng ký hạng mục thi - bản thân công thức chiếm số lượng ô mà nó cần và mọi thứ đều hoạt động với một tiếng nổ. Trong các phiên bản trước, khi chưa có mảng động, trước tiên bạn cần phải chọn đủ số lượng ô trống trước khi nhập công thức (bạn có thể dùng lề) và sau khi tạo công thức, hãy nhấn phím tắt Ctrl+sự thay đổi+đăng ký hạng mục thiđể nhập nó dưới dạng công thức mảng.
Một thủ thuật tương tự có thể được sử dụng khi tách văn bản dính với nhau thành một ô thông qua dấu ngắt dòng:
Sự khác biệt duy nhất với ví dụ trước là thay vì dấu phẩy, ở đây chúng tôi thay thế ký tự ngắt dòng Alt + Enter vô hình, có thể được chỉ định trong công thức bằng cách sử dụng hàm CHAR với mã 10.
- Sự tinh tế khi làm việc với ngắt dòng (Alt + Enter) trong Excel
- Chia văn bản theo các cột trong Excel
- Thay thế văn bản bằng SUBSTITUTE