Cách nhận tất cả các kết quả phù hợp ở các cột khác nhau
Bài viết này sẽ nói về cách lấy tất cả các giá trị khớp từ một bảng và truy xuất chúng trong các ô khác nhau. Điều này tương tự như tra cứu nhiều giá trị.
Công thức chung
{=INDEX(names,SMALL(IF(groups=group_name,ROW(names)-MIN(ROW(names))+1),COLUMNS(expanding ranges))), “--List Ends--”)}
Quá nhiều hàm và biến !!!. Hãy xem những biến này là gì.
Tên: Đây là danh sách các tên.
Nhóm: Danh sách nhóm mà những tên này cũng thuộc về.
Group_name: tham chiếu của tên nhóm.
Ví dụ: Trích xuất tên của nhân viên trong các cột khác nhau theo công ty của họ.
Giả sử bạn có một bảng nhân viên được nhóm theo công ty của họ. Cột đầu tiên chứa tên nhân viên và cột thứ hai chứa tên công ty.
Bây giờ chúng ta cần lấy tên của từng nhân viên trong các cột khác nhau tùy theo công ty của họ. Nói cách khác, chúng ta cần tách chúng ra.
Ở đây, tôi đã đặt tên A2: A10 là Nhân viên và B2: B10 là Công ty, để công thức đó dễ đọc.
Viết công thức mảng này trong F2. Sử dụng CTRL + SHIFT + ENTER để nhập công thức này.
{=INDEX(Employee,SMALL(IF(Company=$E2,ROW(Employee)-MIN(ROW(Employee))+1),COLUMNS($E$1:E1))), “--List Ends--”)}
Sao chép công thức này trong tất cả các ô. Nó sẽ trích xuất từng tên riêng lẻ trong các cột khác nhau theo nhóm của họ.
Như bạn có thể thấy trong hình trên, mỗi nhân viên được tách biệt trong các ô khác nhau.
Vậy, công thức này hoạt động như thế nào?
Để hiểu công thức, hãy xem công thức trong G2 là = IFERROR (INDEX (Nhân viên, NHỎ (IF (Công ty = $ E3, ROW (Nhân viên) -MIN (ROW (Nhân viên)) + 1), COLUMNS ($ E $ 1: F2))), “- Danh sách Kết thúc–“)
Phần này trả về một mảng các chỉ mục và sai cho tên công ty trong $ E3, chứa “Rankwatch”.
\ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Làm sao? Hãy phá bỏ nó từ bên trong.
Ở đây, chúng tôi khớp tên công ty trong $ E3 với mỗi giá trị trong phạm vi Công ty (Công ty = $ E3).
Điều này trả về một mảng đúng và sai. \ {FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}.
Phần này trả về Phần này trả về một mảng các chỉ mục bắt đầu từ 1 đến số nhân viên \ {1; 2; 3; 4; 5; 6; 7; 8; 9}. Hàm if thay đổi các giá trị chỉ TRUE, lần lượt cho chúng ta \ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Công thức hiện tại được đơn giản hóa thành = IFERROR (INDEX (Nhân viên, SMALL (\ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}, COLUMNS ($ E $ 1: F2))), “- Danh sách Kết thúc– “). Như chúng ta biết hàm small trả về giá trị nhỏ nhất thứ n từ một mảng. COLUMNS ($ E $ 1: F2) trả về 2. Hàm SMALL trả về giá trị nhỏ thứ hai từ mảng trên, là 4.
Bây giờ công thức được đơn giản hóa = IFERROR (INDEX (Nhân viên, 4), “- Danh sách Kết thúc–“). Bây giờ, hàm INDEX chỉ đơn giản trả về tên thứ tư từ mảng nhân viên cho chúng ta “Sam”.