Nội dung
Nếu ít nhất bạn đã quen thuộc với các cụm từ thông dụng, thì bạn không cần phải quảng cáo chúng. Nếu bạn không hiểu rõ về chủ đề này, thì biểu thức chính quy (Biểu thức chính quy = RegExp = “regexps” = “thường lệ”) là một ngôn ngữ trong đó, sử dụng các ký tự và quy tắc đặc biệt, các chuỗi con cần thiết được tìm kiếm trong văn bản, chúng được trích xuất hoặc được thay thế bằng văn bản khác. Đây là một công cụ rất mạnh mẽ và đẹp mắt, thứ tự cường độ cao hơn tất cả các cách làm việc khác với văn bản.
Tôi đã mô tả chi tiết và với một loạt các ví dụ từ cuộc sống về cách bạn có thể thêm hỗ trợ biểu thức chính quy vào Excel bằng cách sử dụng các macro đơn giản - nếu bạn chưa đọc bài viết này, tôi thực sự khuyên bạn nên đọc nó trước khi tiếp tục. Bạn sẽ khám phá ra rất nhiều điều mới mẻ, tôi đảm bảo 🙂
Tuy nhiên, câu hỏi vẫn còn bỏ ngỏ - làm thế nào để thêm khả năng sử dụng biểu thức chính quy trong Power Query? Power Query, tất nhiên, tự nó tốt và có thể làm được nhiều việc với văn bản (cắt, dán, làm sạch, v.v.), nhưng nếu bạn có thể vượt qua nó bằng sức mạnh của các biểu thức chính quy, nó sẽ chỉ là một quả bom.
Thật không may, không có chức năng tích hợp nào để làm việc với RegExps trong Power Query và trợ giúp và hỗ trợ kỹ thuật chính thức của Microsoft sẽ trả lời phủ định câu hỏi này. Tuy nhiên, có một cách để giải quyết hạn chế này 🙂
Bản chất của phương pháp
Ý tưởng chính là đơn giản để hổ thẹn.
Trong danh sách các khả năng Power Query được tích hợp sẵn, có một chức năng Trang web. Mô tả về chức năng này trên trang trợ giúp chính thức của Microsoft là cực kỳ ngắn gọn:
Được dịch, điều này sẽ là: “Trả về nội dung của tài liệu HTML được chia nhỏ thành các cấu trúc thành phần của nó, cũng như phần trình bày của tài liệu hoàn chỉnh và nội dung của nó sau khi các thẻ đã bị xóa.” Mô tả quá, một cách thẳng thắn.
Thông thường, chức năng này được sử dụng khi nhập dữ liệu từ web và tự động được thay thế, chẳng hạn như khi chúng ta chọn trên tab Ngày Lệnh Từ trên mạng (Dữ liệu - Từ web). Chúng tôi cung cấp cho hàm một trang web làm đối số và nó trả về nội dung của nó cho chúng tôi dưới dạng bảng, trước đó đã xóa tất cả các thẻ.
Điều mà trợ giúp KHÔNG nói là ngoài ngôn ngữ đánh dấu HTML chức năng Trang web hỗ trợ các tập lệnh JavaScript, hiện đã phổ biến trên các trang web trên Internet. Và đến lượt nó, JavaScript luôn có thể hoạt động với các biểu thức chính quy và có các hàm tích hợp sẵn cho RegExps! Vì vậy, để triển khai các biểu thức chính quy trong Power Query, chúng ta sẽ cần cung cấp các hàm Web.Page như một đối số cho một chương trình JavaScript nhỏ sẽ thực hiện tất cả công việc cho Power Query.
Nó trông như thế nào trong JavaScript thuần túy
Có rất nhiều hướng dẫn chi tiết về cách làm việc với các biểu thức chính quy trong JavaScript trên Internet (ví dụ: một, hai).
Nói một cách ngắn gọn và đơn giản, mã JavaScript sẽ trông như thế này:
Đây:
- var str = 'Thanh toán hóa đơn 123 và 789 cho xúc xích'; - tạo một biến str và gán cho nó văn bản nguồn mà chúng tôi sẽ phân tích.
- var pattern = / d + / gi; - tạo một biểu thức chính quy và đặt nó vào một biến Belt Hold.
Biểu thức bắt đầu bằng dấu gạch chéo (/).
Ví dụ, chính biểu thức ở đây là d+ là viết tắt của bất kỳ dãy chữ số nào.
Thông qua phân số sau biểu thức, có các tham số tìm kiếm bổ sung (công cụ sửa đổi) - chúng có thể được chỉ định theo bất kỳ thứ tự nào:
- g - có nghĩa là tìm kiếm toàn cục, tức là sau khi tìm thấy khớp, bạn không nên dừng lại mà hãy tiếp tục tìm kiếm cho đến hết văn bản. Nếu công cụ sửa đổi này không được đặt, thì tập lệnh của chúng tôi sẽ chỉ trả về kết quả phù hợp đầu tiên (123)
- i - tìm kiếm mà không liên quan đến chữ cái
- m - tìm kiếm nhiều dòng (được sử dụng khi văn bản nguồn được chia thành nhiều dòng)
- var result = str.match (pattern) .join (';'); - thực hiện tìm kiếm trong văn bản nguồn (str) bởi biểu thức chính quy đã cho (Belt Hold) và đặt kết quả vào một biến kết quả, nối chúng bằng dấu chấm phẩy bằng lệnh tham gia
- document.write (kết quả); - hiển thị nội dung của biến kết quả
Cũng lưu ý rằng chuỗi văn bản (không bao gồm biểu thức chính quy) trong JavaScript được đặt trong dấu nháy đơn, không phải dấu ngoặc kép như trong Power Query hoặc VBA.
Ở đầu ra, tập lệnh này sẽ cung cấp cho chúng ta kết quả là tất cả các số được tìm thấy trong văn bản nguồn:
123, 789
Khóa học ngắn hạn về JavaScript đã kết thúc, cảm ơn tất cả các bạn. Hy vọng bạn hiểu được logic 🙂
Nó vẫn là để chuyển cấu trúc này sang Power Query.
Chức năng Tìm kiếm và Trích xuất Văn bản bằng Biểu thức Thông thường trong Power Query
Chúng tôi làm như sau:
1. Mở Excel và tạo một Power Query trống mới trong tab Dữ liệu - Nhận dữ liệu / Tạo yêu cầu - Từ các nguồn khác - Yêu cầu trống (Dữ liệu - Nhận dữ liệu / Truy vấn mới - Từ các nguồn khác - Truy vấn trống). Nếu bạn có phiên bản cũ của Excel 2010-2013 và Power Query mà bạn chưa tích hợp sẵn, nhưng đã được cài đặt dưới dạng một bổ trợ riêng biệt, thì tất cả điều này sẽ có trên tab Truy vấn nguồnVà không Ngày.
2. Trong cửa sổ trống của trình soạn thảo truy vấn mở ra, trong bảng điều khiển bên phải, hãy nhập ngay tên của hàm trong tương lai của chúng ta (ví dụ: fxRegExpTrích xuất)
3. Hãy chuyển đến tab Xem - Trình chỉnh sửa nâng cao (Xem - Trình chỉnh sửa nâng cao), chúng tôi xóa toàn bộ mã M của yêu cầu trống và dán mã của siêu chức năng của chúng tôi vào đó:
Xem tay của bạn:
Trong dòng đầu tiên, chúng tôi nói rằng hàm của chúng tôi sẽ có ba đối số văn bản: txt - văn bản gốc đang được phân tích, biểu thức chính quy - mẫu biểu thức chính quy, phân định - Ký tự phân cách để hiển thị kết quả.
Tiếp theo chúng ta gọi hàm Trang web, tạo thành mã JavaScript được mô tả ở trên trong đối số của nó. Chúng tôi dán và thay thế các đối số biến của chúng tôi vào mã.
Miếng:
[Dữ liệu] {0} [Trẻ em] {0} [Trẻ em] {1} [Văn bản] {0}
… Cần thiết để “lọt” vào bảng với kết quả chúng ta cần. Vấn đề là chức năng Trang web kết quả là nó tạo ra một số bảng lồng nhau lặp lại cấu trúc của một trang web. Nếu không có đoạn mã M này, hàm của chúng ta sẽ xuất ra:
… Và chúng tôi sẽ phải nhấp vào từ đó nhiều lần Bàn, liên tiếp “lọt” vào các bảng con lồng nhau trong các cột Trẻ em:
Thay vì tất cả dấu ngoặc kép này, chúng tôi ngay lập tức chỉ ra trong mã hàm của chúng tôi bảng và cột nào được lồng vào nhau (bản văn) chúng tôi cần.
Đây, trên thực tế, là tất cả những bí mật. Nó vẫn còn để nhấn nút Kết thúc trong cửa sổ trình soạn thảo nâng cao, nơi chúng tôi đã chèn mã của mình và bạn có thể tiến hành một cách ngon lành nhất - hãy thử chức năng của chúng tôi tại nơi làm việc.
Dưới đây là một vài ví dụ về hạt giống.
Ví dụ 1. Lấy số và ngày tài khoản từ mô tả thanh toán
Chúng tôi có một bảng sao kê ngân hàng với mô tả (mục đích) các khoản thanh toán, trong đó bạn cần lấy số và ngày của các hóa đơn đã thanh toán thành các cột riêng biệt:
Chúng tôi tải bảng vào Power Query theo cách tiêu chuẩn thông qua Dữ liệu - Từ Bảng / Phạm vi (Dữ liệu - Từ Tcó thể / RThiên thần).
Sau đó, chúng tôi thêm một cột được tính toán với hàm của chúng tôi thông qua Thêm cột - Chức năng tùy chỉnh cuộc gọi (Thêm cột - Gọi chức năng tùy chỉnh) và nhập các đối số của nó:
Dưới dạng một biểu thức chính quy (đối số biểu thức chính quy) mẫu chúng tôi sử dụng:
(d {3,5}|d {2} .d {2} .d {4})
… Dịch sang tiếng người có nghĩa là:
số từ 3 đến 5 chữ số (số tài khoản)
or
các đoạn có dạng “số 2 bit - điểm - số 2 bit - điểm - số 4 bit”, nghĩa là, ngày có dạng DD.MM.YYYY.
Là một ký tự phân cách (đối số phân định) nhập dấu chấm phẩy.
Sau khi nhấp vào OK hàm ma thuật của chúng tôi phân tích tất cả dữ liệu ban đầu theo biểu thức chính quy của chúng tôi và tạo một cột cho chúng tôi với các số và ngày tháng tìm thấy của các hóa đơn:
Nó vẫn còn để phân tách nó bằng dấu chấm phẩy bằng cách sử dụng lệnh Trang chủ - Cột Tách - Theo Dấu phân cách (Trang chủ - Chia cột - Theo dấu phân cách) và chúng tôi đạt được những gì chúng tôi muốn:
Sắc đẹp, vẻ đẹp!
Ví dụ 2: Trích xuất địa chỉ email từ văn bản
Giả sử chúng ta có bảng sau làm dữ liệu ban đầu:
… Từ nơi chúng ta cần lấy ra các địa chỉ email được tìm thấy ở đó (để rõ ràng, tôi đã đánh dấu chúng bằng màu đỏ trong văn bản).
Như trong ví dụ trước, chúng tôi tải bảng vào Power Query theo cách tiêu chuẩn thông qua Dữ liệu - Từ Bảng / Phạm vi (Dữ liệu - Từ Tcó thể / RThiên thần).
Sau đó, chúng tôi thêm một cột được tính toán với hàm của chúng tôi thông qua Thêm cột - Chức năng tùy chỉnh cuộc gọi (Thêm cột - Gọi chức năng tùy chỉnh) và nhập các đối số của nó:
Phân tích cú pháp địa chỉ email là một nhiệm vụ khó khăn hơn và có một loạt các biểu thức chính quy ở các mức độ khác nhau là cơn ác mộng để giải quyết nó. Tôi đã sử dụng một trong những tùy chọn đơn giản - không lý tưởng, nhưng khá hiệu quả trong hầu hết các trường hợp:
[w |. | -] * @ w *. [w |.] *
Như dấu phân cách (phân định) bạn có thể nhập dấu chấm phẩy và dấu cách.
Nhấp vào OK và chúng tôi nhận được một cột có địa chỉ e-mail được trích xuất từ văn bản gốc "cháo":
Ma thuật!
PS
Như câu nói: "Không có điều gì tốt mà không thể tốt hơn được nữa." Power Query tự nó rất tuyệt và khi kết hợp với các biểu thức chính quy, nó mang lại cho chúng ta sức mạnh hoàn toàn phi thực tế và tính linh hoạt trong việc xử lý bất kỳ dữ liệu văn bản nào. Tôi hy vọng một ngày nào đó Microsoft sẽ bổ sung hỗ trợ RegExp trong các bản cập nhật Power Query và Power BI và tất cả những bước nhảy trên với tambourine sẽ trở thành dĩ vãng. Vâng, bây giờ, có.
Tôi cũng muốn nói thêm rằng việc chơi với các biểu thức chính quy trên trang web https://regexr.com/ - ngay trong trình chỉnh sửa trực tuyến sẽ rất tiện lợi. Có trong phần Mô hình cộng đồng Có một số lượng lớn các mùa thường xuyên được làm sẵn cho tất cả các dịp. Thử nghiệm - tất cả sức mạnh của biểu thức chính quy giờ đây là dịch vụ của bạn trong Power Query!
- Biểu thức chính quy (RegExp) là gì và cách sử dụng chúng trong Excel
- Tìm kiếm văn bản mờ trong Power Query
- Lắp ráp các bảng từ các tệp khác nhau bằng Power Query