Nội dung
Tôi đã viết về cách bạn có thể nhanh chóng dán văn bản từ nhiều ô thành một và ngược lại, phân tích một chuỗi văn bản dài thành các thành phần. Bây giờ chúng ta hãy xem xét một nhiệm vụ gần hơn nhưng phức tạp hơn một chút – cách dán văn bản từ nhiều ô khi đáp ứng một điều kiện cụ thể nhất định.
Giả sử chúng ta có cơ sở dữ liệu về khách hàng, trong đó một tên công ty có thể tương ứng với một số email khác nhau của nhân viên công ty đó. Nhiệm vụ của chúng tôi là thu thập tất cả các địa chỉ theo tên công ty và ghép chúng lại (được phân tách bằng dấu phẩy hoặc dấu chấm phẩy) để tạo, ví dụ: danh sách gửi thư cho khách hàng, tức là nhận được kết quả đầu ra như:
Nói cách khác, chúng ta cần một công cụ sẽ dán (liên kết) văn bản theo điều kiện – một chức năng tương tự TỔNG HỢP (TỔNG HỢP), nhưng đối với văn bản.
Phương pháp 0. Công thức
Không thanh lịch lắm, nhưng là cách dễ nhất. Bạn có thể viết một công thức đơn giản để kiểm tra xem công ty ở hàng tiếp theo có khác với công ty trước đó hay không. Nếu không khác thì dán địa chỉ tiếp theo cách nhau bằng dấu phẩy. Nếu nó khác, thì chúng tôi “đặt lại” số tiền tích lũy, bắt đầu lại:
Nhược điểm của phương pháp này là rõ ràng: từ tất cả các ô của cột bổ sung thu được, chúng ta chỉ cần những ô cuối cùng cho mỗi công ty (màu vàng). Nếu danh sách lớn thì để chọn nhanh chúng, bạn sẽ phải thêm một cột khác bằng hàm DLSTR (LEN), kiểm tra độ dài của chuỗi tích lũy:
Bây giờ bạn có thể lọc ra những cái đó và sao chép địa chỉ cần dán để sử dụng tiếp.
Cách 1. Hàm vĩ mô của việc dán theo một điều kiện
Nếu danh sách ban đầu không được sắp xếp theo công ty thì công thức đơn giản ở trên không hoạt động nhưng bạn có thể dễ dàng thực hiện bằng một hàm tùy chỉnh nhỏ trong VBA. Mở Visual Basic Editor bằng cách nhấn phím tắt Alt + F11 hoặc sử dụng nút Visual Basic chuyển hướng nhà phát triển (Nhà phát triển). Trong cửa sổ mở ra, chèn một mô-đun trống mới thông qua menu Chèn - Mô-đun và sao chép văn bản chức năng của chúng tôi ở đó:
Hàm MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = "," các phần dán không bằng nhau - chúng ta thoát ra với lỗi Nếu SearchRange.Count <> TextRange.Count Sau đó MergeIf = CVERr(xlErrRef) Exit Function End If 'đi qua tất cả các ô, kiểm tra điều kiện và thu thập văn bản trong biến OutText For i = 1 To SearchRange. Cells.Count Nếu SearchRange.Cells(i) Thích Điều kiện thì OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'hiển thị kết quả không có dấu phân cách cuối cùng MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End chức năng
Nếu bây giờ bạn quay lại Microsoft Excel thì trong danh sách các hàm (nút fx trong thanh công thức hoặc tab Công thức - Chèn hàm) sẽ có thể tìm thấy chức năng của chúng tôi Hợp nhất nếu trong danh mục Đã xác định người dùng (Đã xác định người dùng). Các đối số của hàm như sau:
Cách 2. Nối văn bản theo điều kiện sai
Nếu chúng ta thay thế ký tự đầu tiên ở dòng thứ 13 của macro = đến toán tử khớp gần đúng Như, thì có thể thực hiện việc dán bằng cách khớp không chính xác dữ liệu ban đầu với tiêu chí lựa chọn. Ví dụ: nếu tên công ty có thể được viết bằng nhiều biến thể khác nhau thì chúng ta có thể kiểm tra và thu thập tất cả chúng bằng một chức năng:
Các ký tự đại diện tiêu chuẩn được hỗ trợ:
- dấu hoa thị (*) – biểu thị bất kỳ số lượng ký tự nào (bao gồm cả sự vắng mặt của chúng)
- dấu chấm hỏi (?) – là viết tắt của bất kỳ ký tự đơn nào
- Dấu thăng (#) – viết tắt của bất kỳ một chữ số nào (0-9)
Theo mặc định, toán tử Like phân biệt chữ hoa chữ thường, tức là hiểu, chẳng hạn như “Orion” và “orion” là các công ty khác nhau. Để bỏ qua chữ hoa chữ thường, bạn có thể thêm dòng này vào đầu mô-đun trong trình soạn thảo Visual Basic Tùy chọn So sánh văn bản, sẽ chuyển sang dạng không phân biệt chữ hoa chữ thường.
Bằng cách này, bạn có thể tạo các mặt nạ rất phức tạp để kiểm tra các điều kiện, ví dụ:
- ?1##??777RUS – chọn tất cả biển số xe vùng 777, bắt đầu bằng 1
- LLC* – tất cả các công ty có tên bắt đầu bằng LLC
- ##7## – tất cả các sản phẩm có mã kỹ thuật số gồm năm chữ số, trong đó chữ số thứ ba là 7
- ????? – tất cả tên của năm chữ cái, v.v.
Cách 3. Hàm macro dán văn bản theo hai điều kiện
Trong công việc có thể sẽ gặp vấn đề khi bạn cần liên kết văn bản nhiều hơn một điều kiện. Ví dụ: hãy tưởng tượng rằng trong bảng trước của chúng tôi, một cột nữa có thành phố đã được thêm vào và việc dán không chỉ được thực hiện đối với một công ty nhất định mà còn đối với một thành phố nhất định. Trong trường hợp này, chức năng của chúng tôi sẽ phải được hiện đại hóa một chút bằng cách thêm một kiểm tra phạm vi khác vào nó:
Hàm MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'ký tự phân cách (có thể thay thế bằng dấu cách hoặc ; v.v.) e.) 'nếu phạm vi xác thực và dán không bằng nhau, thoát ra với lỗi Nếu SearchRange1.Count <> TextRange.Count Hoặc SearchRange2.Count <> TextRange.Count Then MergeIfs = CVERr(xlErrRef) Thoát Chức năng Kết thúc Nếu 'đi qua tất cả các ô, kiểm tra tất cả các điều kiện và thu thập văn bản vào biến OutText For i = 1 To SearchRange1.Cells.Count Nếu SearchRange1.Cells(i) = Condition1 Và SearchRange2.Cells(i) = Condition2 Thì OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'hiển thị kết quả không có dấu phân cách cuối cùng MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Nó sẽ được áp dụng theo cách tương tự – chỉ có các đối số bây giờ cần được chỉ định thêm:
Phương pháp 4. Nhóm và dán trong Power Query
Bạn có thể giải quyết vấn đề mà không cần lập trình trong VBA nếu bạn sử dụng phần bổ trợ Power Query miễn phí. Đối với Excel 2010-2013, bạn có thể tải xuống tại đây và trong Excel 2016, nó đã được tích hợp sẵn theo mặc định. Trình tự các hành động sẽ như sau:
Power Query không biết cách làm việc với các bảng thông thường, vì vậy bước đầu tiên là biến bảng của chúng ta thành một bảng “thông minh”. Để làm điều này, hãy chọn nó và nhấn tổ hợp Ctrl+T hoặc chọn từ tab Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng). Trên tab sau đó xuất hiện xây dựng (Thiết kế) bạn có thể đặt tên bảng (tôi để lại tiêu chuẩn Bảng 1):
Bây giờ hãy tải bảng của chúng ta vào phần bổ trợ Power Query. Để thực hiện việc này, trên tab Ngày (nếu bạn có Excel 2016) hoặc trên tab Power Query (nếu bạn có Excel 2010-2013), hãy bấm Từ cái bàn (Dữ liệu - Từ bảng):
Trong cửa sổ soạn thảo truy vấn mở ra, chọn cột bằng cách nhấp vào tiêu đề Công ty và nhấn nút ở trên Nhóm (Nhóm theo). Nhập tên của cột mới và loại hoạt động trong nhóm – Tất cả các dòng (Tất cả các hàng):
Nhấp vào OK và chúng tôi nhận được một bảng nhỏ gồm các giá trị được nhóm cho mỗi công ty. Nội dung của các bảng hiển thị rõ ràng nếu bạn nhấp chuột trái vào nền trắng của các ô (không phải trên văn bản!) Trong cột kết quả:
Bây giờ, hãy thêm một cột nữa, trong đó, bằng cách sử dụng hàm, chúng ta sẽ dán nội dung của các cột Địa chỉ vào mỗi bảng nhỏ, phân tách bằng dấu phẩy. Để thực hiện việc này, trên tab Thêm cột chúng tôi nhấn Cột tùy chỉnh (Thêm cột - Cột tùy chỉnh) và trong cửa sổ hiện ra nhập tên cột mới và công thức ghép bằng ngôn ngữ M tích hợp trong Power Query:
Lưu ý rằng tất cả các hàm M đều phân biệt chữ hoa chữ thường (không giống như Excel). Sau khi nhấp vào OK chúng tôi nhận được một cột mới với các địa chỉ được dán:
Nó vẫn còn để loại bỏ cột đã không cần thiết Địa chỉ bảng (nhấp chuột phải vào tiêu đề) Xóa cột) và tải kết quả lên trang tính bằng cách nhấp vào tab Trang chủ - Đóng và tải xuống (Trang chủ - Đóng và tải):
Sắc thái quan trọng: Không giống như các phương thức (hàm) trước đó, các bảng từ Power Query không được cập nhật tự động. Nếu trong tương lai sẽ có bất kỳ thay đổi nào trong dữ liệu nguồn, thì bạn sẽ cần nhấp chuột phải vào bất kỳ vị trí nào trong bảng kết quả và chọn lệnh Cập nhật & Lưu (Làm mới).
- Cách chia chuỗi văn bản dài thành nhiều phần
- Một số cách để dán văn bản từ các ô khác nhau vào một
- Sử dụng toán tử Like để kiểm tra văn bản dựa trên mặt nạ