Tìm kiếm các từ khóa trong văn bản

Tìm kiếm từ khóa trong văn bản nguồn là một trong những nhiệm vụ phổ biến nhất khi làm việc với dữ liệu. Hãy xem xét giải pháp của nó theo nhiều cách bằng cách sử dụng ví dụ sau:

Giả sử rằng bạn và tôi có một danh sách từ khóa – tên của các nhãn hiệu xe hơi – và một bảng lớn chứa tất cả các loại phụ tùng thay thế, trong đó các mô tả đôi khi có thể chứa một hoặc một số nhãn hiệu như vậy cùng một lúc, nếu phụ tùng phù hợp với nhiều hơn một. thương hiệu xe hơi. Nhiệm vụ của chúng tôi là tìm và hiển thị tất cả các từ khóa được phát hiện trong các ô lân cận thông qua một ký tự phân tách nhất định (ví dụ: dấu phẩy).

Phương pháp 1. Truy vấn nguồn

Tất nhiên, trước tiên, chúng tôi biến các bảng của mình thành động (“thông minh”) bằng cách sử dụng phím tắt Ctrl+T hoặc lệnh Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng), đặt tên cho chúng (ví dụ Temи Phụ tùng) và tải từng cái một vào trình soạn thảo Power Query bằng cách chọn trên tab Dữ liệu - Từ Bảng / Phạm vi (Dữ liệu - Từ Bảng / Phạm vi). Nếu bạn có các phiên bản cũ hơn của Excel 2010-2013, trong đó Power Query được cài đặt dưới dạng một phần bổ trợ riêng biệt, thì nút mong muốn sẽ nằm trên tab Truy vấn nguồn. Nếu bạn có phiên bản Excel 365 hoàn toàn mới, thì nút Từ Bảng / Phạm vi được gọi là có ngay bây giờ Với những chiếc lá (Từ Trang tính).

Sau khi tải từng bảng trong Power Query, chúng tôi quay lại Excel bằng lệnh Trang chủ - Đóng và tải - Đóng và tải vào… - Chỉ tạo kết nối (Trang chủ — Đóng & Tải — Đóng & Tải vào… — Chỉ tạo kết nối).

Bây giờ hãy tạo một yêu cầu trùng lặp Phụ tùngbằng cách nhấp chuột phải vào nó và chọn yêu cầu trùng lặp (Truy vấn trùng lặp), sau đó đổi tên yêu cầu sao chép kết quả thành Kết quả và chúng tôi sẽ tiếp tục làm việc với anh ấy.

Logic của các hành động như sau:

1. Trên tab Nâng cao Thêm một cột chọn một đội Cột tùy chỉnh (Thêm cột - Cột tùy chỉnh) và nhập công thức = Thương hiệu. Sau khi nhấp vào OK chúng tôi sẽ nhận được một cột mới, trong đó trong mỗi ô sẽ có một bảng lồng nhau với danh sách các từ khóa của chúng tôi – thương hiệu ô tô:

   

2. Sử dụng nút có mũi tên kép trong tiêu đề của cột đã thêm để mở rộng tất cả các bảng lồng nhau. Đồng thời, các dòng có mô tả về phụ tùng thay thế sẽ nhân với bội số của số lượng nhãn hiệu và chúng tôi sẽ nhận được tất cả các kết hợp cặp có thể có của “nhãn hiệu phụ tùng thay thế”:

   

3. Trên tab Nâng cao Thêm một cột chọn một đội Cột có điều kiện (Cột điều kiện) và đặt điều kiện để kiểm tra sự xuất hiện của từ khóa (thương hiệu) trong văn bản nguồn (phần mô tả):

   

4. Để làm cho trường hợp tìm kiếm không nhạy cảm, hãy thêm đối số thứ ba vào thanh công thức theo cách thủ công So sánh.OrdinalIgnoreCase đến chức năng kiểm tra sự xuất hiện Văn bản.Contains (nếu thanh công thức không hiển thị, thì nó có thể được bật trên tab Đánh giá):

   

5. Chúng tôi lọc bảng kết quả, chỉ để lại những cái trong cột cuối cùng, tức là khớp và loại bỏ cột không cần thiết Sự xuất hiện.
6. Nhóm các mô tả giống hệt nhau bằng lệnh Nhóm theo chuyển hướng Chuyển đổi (Chuyển đổi - Nhóm theo). Là một hoạt động tổng hợp, chọn Tất cả các dòng (Tất cả các hàng). Ở đầu ra, chúng tôi nhận được một cột có các bảng chứa tất cả các chi tiết cho từng phụ tùng thay thế, bao gồm cả nhãn hiệu của các nhà sản xuất ô tô mà chúng tôi cần:

   

7. Để trích xuất điểm cho từng phần, hãy thêm một cột được tính toán khác trên tab Thêm một cột - Cột tùy chỉnh (Thêm cột - Cột tùy chỉnh) và sử dụng một công thức bao gồm một bảng (chúng nằm trong cột của chúng tôi Chi tiết) và tên của cột được trích xuất:

   

8. Chúng tôi nhấp vào nút có mũi tên kép trong tiêu đề của cột kết quả và chọn lệnh Trích xuất giá trị (Trích xuất giá trị)để xuất tem với bất kỳ ký tự phân cách nào bạn muốn:

   

9. Loại bỏ một cột không cần thiết Chi tiết.
10. Để thêm vào bảng kết quả các phần đã biến mất khỏi bảng, nơi không tìm thấy nhãn hiệu nào trong phần mô tả, chúng tôi thực hiện quy trình kết hợp truy vấn Kết quả với yêu cầu ban đầu Phụ tùng nút Kết hợp chuyển hướng Trang Chủ (Trang chủ — Hợp nhất các truy vấn). Kiểu kết nối - Tham gia bên ngoài bên phải (Nối ngoài phải):

    

11. Tất cả những gì còn lại là loại bỏ các cột thừa và đổi tên-di chuyển các cột còn lại – và nhiệm vụ của chúng ta đã được giải quyết:

    
    

Phương pháp 2. Công thức

Nếu bạn có phiên bản Excel 2016 trở lên, thì vấn đề của chúng ta có thể được giải quyết một cách rất nhỏ gọn và thanh lịch bằng cách sử dụng hàm mới PHỐI HỢP (VĂN BẢN THAM GIA):

Logic đằng sau công thức này rất đơn giản:

• Chức năng TÌM KIẾM (TÌM THẤY) lần lượt tìm kiếm sự xuất hiện của từng nhãn hiệu trong phần mô tả hiện tại của bộ phận và trả về số sê-ri của ký hiệu, bắt đầu từ đó nhãn hiệu được tìm thấy hoặc lỗi #VALUE! nếu thương hiệu không có trong mô tả.
• Sau đó sử dụng chức năng IF (NẾU) и EOSHIBKA (BÁC SĨ) chúng tôi thay thế các lỗi bằng một chuỗi văn bản trống “”, và số thứ tự của các ký tự bằng chính tên thương hiệu.
• Mảng kết quả gồm các ô trống và các nhãn hiệu được tìm thấy được tập hợp thành một chuỗi duy nhất thông qua một ký tự phân cách đã cho bằng cách sử dụng hàm PHỐI HỢP (VĂN BẢN THAM GIA).

So sánh hiệu suất và bộ đệm truy vấn Power Query để tăng tốc

Để kiểm tra hiệu suất, hãy lấy một bảng gồm 100 mô tả phụ tùng thay thế làm dữ liệu ban đầu. Trên đó, chúng tôi nhận được các kết quả sau:

• Thời gian tính toán lại theo công thức (Phương pháp 2) – 9 giây. khi lần đầu tiên bạn sao chép công thức vào toàn bộ cột và 2 giây. lặp đi lặp lại (có thể ảnh hưởng đến bộ đệm).
  
• Thời gian cập nhật của truy vấn Power Query (Phương pháp 1) tệ hơn nhiều – 110 giây.
  

Tất nhiên, phần lớn phụ thuộc vào phần cứng của một PC cụ thể và phiên bản Office đã cài đặt cũng như các bản cập nhật, nhưng tôi nghĩ bức tranh tổng thể là rõ ràng.

Để tăng tốc truy vấn Power Query, hãy đệm bảng tra cứu Tem, bởi vì nó không thay đổi trong quá trình thực hiện truy vấn và không cần phải liên tục tính toán lại nó (như Power Query trên thực tế). Đối với điều này, chúng tôi sử dụng chức năng Bảng.Bộ đệm từ ngôn ngữ Power Query tích hợp sẵn M.

Để làm điều này, hãy mở một truy vấn Kết quả và trên tab Đánh giá Nhấn nút Trình chỉnh sửa nâng cao (Xem - Trình chỉnh sửa nâng cao). Trong cửa sổ mở ra, thêm một dòng có biến mới đánh dấu 2, đây sẽ là phiên bản đệm của thư mục ô tô của chúng tôi và sử dụng biến mới này sau trong lệnh truy vấn sau:

Sau khi tinh chỉnh như vậy, tốc độ cập nhật của yêu cầu của chúng tôi tăng gần 7 lần – tối đa 15 giây. Một điều hoàn toàn khác 🙂

• Tìm kiếm văn bản mờ trong Power Query
• Thay thế hàng loạt văn bản bằng công thức
• Thay thế hàng loạt văn bản trong Power Query bằng chức năng List.Accumulate