Keval có một bàn hơn 5.000 sinh viên được phân bổ trên 22 khóa học. Đối với tên của mỗi học sinh trong mỗi hàng của bảng này được chỉ ra khóa học mà học sinh đã đăng ký. Keval muốn, trên một trang tính khác, đặt tên khóa học vào ô A1 và sau đó hiển thị Excel, bắt đầu từ hàng 3, tất cả sinh viên trong khóa học đó, như được lấy ra từ danh sách. Anh ấy tự hỏi nếu có cách nào để làm điều này với một công thức.

Giải pháp tốt nhất cho vấn đề này sẽ phụ thuộc phần lớn vào cách tổ chức dữ liệu nguồn của bạn. Nếu bạn chỉ có hai cột (tên sinh viên và tên khóa học), thì có thể bạn thậm chí không cần sử dụng trang tính thứ hai. Thay vào đó, bạn có thể nhận được bằng cách sử dụng khả năng lọc của Excel. Chỉ cần lọc nội dung của cột có chứa tên khóa học và bạn có thể dễ dàng giới hạn nội dung được hiển thị cho chỉ những sinh viên đó trong khóa học.

Nếu bạn phải có thông tin xuất hiện trên trang tính thứ hai, tốt nhất có thể sử dụng công thức mảng để trích xuất học sinh. Đặt khóa học mong muốn vào ô A1 rồi nhập công thức sau vào ô A3:

=IF(COUNTIF(Sheet1!$B$1:$B$5000,$A$1)<ROW()-2,"", INDEX(Sheet1!$A$1:$A$5000,SMALL(IF(Sheet1!$B$1:$B$5000=$A$1, ROW(Sheet1!$B$1:$B$5000)),ROW()-2)))

Hãy nhớ — đây là một công thức duy nhất và bạn cần nhập công thức đó vào ô bằng cách nhấn Ctrl + Shift + Enter. Sao chép công thức xuống dưới vào đủ ô mà nó phải chứa lớp lớn nhất của bạn. Công thức cũng giả định rằng dữ liệu nguồn trong các hàng từ 1 đến 5000; nếu đây không phải là trường hợp, bạn sẽ muốn sửa đổi công thức để phản ánh phạm vi thích hợp.

Một cách tiếp cận khác là tạo PivotTable dựa trên danh sách sinh viên / khóa học. Tất cả những gì bạn cần làm là đảm bảo rằng cả trường tên khóa học và tên sinh viên đều nằm trong vùng “hàng” của PivotTable. Đặt trường tên khóa học đầu tiên và trường tên sinh viên thứ hai, và bạn sẽ kết thúc với danh sách tất cả các khóa học có các sinh viên dưới mỗi tên khóa học.

Nếu dữ liệu nguồn của bạn có thông tin bổ sung được liên kết với nó (chẳng hạn như điểm, địa chỉ, bài tập về sách, v.v.), thì bạn có thể muốn xem xét làm việc với một chương trình cơ sở dữ liệu thực tế, chẳng hạn như Access. Bạn sẽ có thể thao tác và trích xuất dữ liệu bằng các công cụ vượt trội hơn so với các công cụ trong Excel.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12346) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: