Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách nối các giá trị ô dựa trên tiêu chí bằng cách sử dụng Hàm TEXTJOIN trong Excel và Google Trang tính.
Hàm TEXTJOIN
Người dùng Excel 2022+ có quyền truy cập vào Hàm TEXTJOIN, hàm này hợp nhất nhiều chuỗi văn bản với nhau, tách từng chuỗi bằng một dấu phân cách được chỉ định.
Ghi chú: Người dùng Google Trang tính có thể sử dụng Hàm TEXTJOIN, nhưng có cách nhập công thức mảng hơi khác. Xem phần sau về chủ đề này.
Ví dụ này sẽ sử dụng Hàm TEXTJOIN và IF trong công thức mảng để tạo chuỗi văn bản được phân tách bằng dấu phẩy của Tên người chơi liên quan đến một Đội giá trị của màu đỏ
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
Người dùng Excel 2022 sẽ cần nhập công thức này dưới dạng một hàm mảng bằng cách nhấn CTRL + SHIFT + ENTER. Người dùng các phiên bản Excel mới hơn không cần phải làm theo bước này.
Để giải thích công thức này đang làm gì, hãy chia nó thành các bước:
Đây là công thức cuối cùng của chúng tôi:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
Đầu tiên, các giá trị phạm vi ô được thêm vào công thức:
| 1 | = TEXTJOIN (",", TRUE, IF ({"Red"; "Blue"; "Blue"; "Red"; "Blue"; "Red"} = "Red", {"A"; "B"; "C"; "D"; "E"; "F"}, "")) |
Tiếp theo danh sách Đội tên được so sánh với giá trị màu đỏ:
| 1 | = TEXTJOIN (",", TRUE, IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
Hàm IF thay thế các giá trị TRUE bằng Tên người chơivà FALSE các giá trị với “”
| 1 | = TEXTJOIN (",", TRUE, {"A"; ""; ""; "D"; ""; "F"}) |
Sau đó, Hàm TEXTJOIN kết hợp tất cả các giá trị của mảng thành một chuỗi văn bản. Chúng tôi đã hướng dẫn hàm bỏ qua các giá trị trống và thêm văn bản “,” vào giữa mỗi giá trị. Điều này tạo ra kết quả cuối cùng:
| 1 | = "A, D, F" |
TextJoin If - trước Excel 2022
Vì Hàm TEXTJOIN không khả dụng trước phiên bản Excel 2022, chúng tôi cần giải quyết vấn đề này theo một cách khác. Hàm CONCATENATE có sẵn nhưng không lấy phạm vi ô làm đầu vào hoặc cho phép hoạt động mảng và vì vậy chúng tôi bắt buộc phải sử dụng cột trợ giúp với Hàm IF để thay thế.
Ví dụ tiếp theo này cho thấy cách sử dụng cột trợ giúp để tạo chuỗi văn bản được phân tách bằng dấu phẩy của Tên người chơi liên quan đến một Đội giá trị của Màu đỏ:
| 1 | = IF (C3 = "Đỏ", B3 & ",", "") & D4 |
Bước đầu tiên trong ví dụ này là sử dụng Hàm IF để sao chép điều kiện của Đội ngũ = Màu đỏ. Nếu điều kiện được đáp ứng, Tên người chơi giá trị được viết và theo sau bởi dấu phân cách đã chọn “,“:
| 1 | = IF (C3 = "Đỏ", B3 & ",", "") |
Tiếp theo, chúng ta có thể tạo một cột xây dựng danh sách các giá trị này vào một ô bằng cách tham chiếu ô bên dưới nó:
| 1 | = D3 & E4 |
Công thức này sử dụng ký tự & để nối hai giá trị với nhau. Lưu ý rằng Hàm CONCATENATE có thể được sử dụng để tạo chính xác cùng một kết quả, nhưng phương thức & thường được ưu tiên hơn vì nó ngắn hơn và giúp rõ ràng hơn về hành động mà công thức đang thực hiện.
Sau đó, hai cột trợ giúp này có thể được kết hợp thành một công thức:
| 1 | = IF (C3 = "Đỏ", B3 & ",", "") & D4 |
Sau đó, một ô tóm tắt có thể tham chiếu đến giá trị đầu tiên trong Danh sách người chơi cột trợ giúp:
| 1 | = D3 |
Nếu giá trị cuối cùng “,” là không bắt buộc, một Hàm IF lồng nhau bổ sung sẽ được thêm vào công thức:
| 1 | = IF (C3 = "Đỏ", IF (D4 = "", B3, B3 & ","), "") & D4 |
Phần bổ sung của công thức là Hàm IF bên dưới:
| 1 | IF (D4 = "", B3, B3 & ",") |
Phần này của công thức kiểm tra giá trị của ô bên dưới nó. Nếu nó trống, thì ô này phải là ô cuối cùng Tên người chơi trong danh sách và tên được viết không có dấu phân cách. Nếu ô bên dưới chứa một giá trị, thì ô này không phải là ô cuối cùng Tên người chơi trong danh sách và dấu phân cách “,” được thêm vào.
TextJoin If trong Google Sheets
Các công thức này hoạt động tương tự trong Google Trang tính như trong Excel, ngoại trừ Hàm ARRAYFORMULA được yêu cầu sử dụng trong Google Trang tính để đánh giá kết quả chính xác. Điều này có thể được thêm tự động bằng cách nhấn các phím CTRL + SHIFT + ENTER trong khi chỉnh sửa công thức:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Red", B3: B8, "")))) |
