Kỹ thuật xử lý dữ liệu trong Excel: Bảng đăng ký khách đến thăm, Phương pháp INDEX + MOD + LARGE
Hôm nay, chúng tôi sẽ chia sẻ một câu hỏi về kỹ thuật xử lý dữ liệu trong Excel
Bài đưa ra là: “Tôi muốn tự động tạo bảng xếp hạng số lần khách đến thăm theo thời gian thực từ bảng đăng ký. Liệu có thể làm được không?”
Mô tả bài toán
Trong bảng dữ liệu trên, chúng ta cần đếm có điều kiện ở cột C và sau đó sắp xếp theo số lần khách đến thăm, rồi lấy tên của tư vấn viên bất động sản tương ứng.
Chúng tôi gọi đây là bài toán “Thống kê nhóm và sắp xếp”, với ba thách thức chính:
1. Phải đếm có điều kiện, nhưng danh sách tư vấn viên không có sẵn, nên cần lấy dữ liệu trực tiếp từ bảng. Nếu không sử dụng cột phụ, việc đếm này không đơn giản.
2. Các tư vấn viên xuất hiện nhiều lần, số lần khách đến thăm có thể trùng nhau, nhưng danh sách kết quả cần loại bỏ trùng lặp và chỉ hiển thị mỗi tư vấn viên một lần.
3. Sắp xếp theo số lượt đến thăm và lấy đúng tên tư vấn viên tương ứng.
Bài toán này có nhiều cách giải tùy theo phiên bản Excel. Trong Excel 2019 trở xuống, có thể dùng phương pháp INDEX + MOD + LARGE.
1. Phương pháp INDEX + MOD + LARGE
Phương pháp này chỉ sử dụng các hàm Excel cũ, nhưng có thể xử lý vấn đề sắp xếp và loại bỏ trùng lặp một cách hiệu quả. Tuy nhiên, nó khá phức tạp và cần phân tích từng bước.
Công thức chính
=INDEX($C$C,MOD(LARGE(COUNTIF($C$2:$C$300, $C$2:$C$300) *
(COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)), $C$2:$C$300) = 1) +
ROW($C$2:$C$300)%ROW()-2),1)*10000)
Giải thích công thức
1. Đếm số lần xuất hiện của từng tư vấn viên
COUNTIF($C$2:$C$300, $C$2:$C$300)
Công thức này đếm số lần xuất hiện của từng tư vấn viên trong danh sách.
2. Xác định vị trí xuất hiện đầu tiên của mỗi tư vấn viên
COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)), $C$2:$C$300) = 1
Công thức này kiểm tra từng dòng để xác định tư vấn viên xuất hiện lần đầu tiên.
3. Loại bỏ trùng lặp
Nhân hai kết quả trên với nhau để chỉ giữ lại số lần xuất hiện đầu tiên.
4. Sắp xếp theo số lần đến thăm
LARGE(…, ROW() - 2)
Dùng hàm LARGE để lấy giá trị lớn nhất thứ k, tức là sắp xếp dữ liệu theo số lượt khách đến thăm.
5. Lấy tên tư vấn viên tương ứng
INDEX(C:C, …)
Cuối cùng, dùng INDEX để lấy tên tư vấn viên có số lần khách đến thăm cao nhất.
2. Tổng kết
Các bước để giải quyết bài toán:
✅ Bước 1: Đếm số lần xuất hiện của từng tư vấn viên.
✅ Bước 2: Xác định lần xuất hiện đầu tiên để loại bỏ trùng lặp.
✅ Bước 3: Gắn thêm thông tin vị trí dòng để sắp xếp đúng.
✅ Bước 4: Dùng LARGE để lấy số lần xuất hiện lớn nhất.
✅ Bước 5: Dùng INDEX để lấy tên tư vấn viên tương ứng.
Lưu ý: Đây là một công thức phức tạp, nhưng nếu thực hành nhiều lần, bạn sẽ hiểu rõ hơn về cách hoạt động của nó. Trong các phiên bản Excel mới hơn, có những công thức đơn giản hơn để giải quyết bài toán này, chúng tôi sẽ chia sẻ trong bài viết tiếp theo!
Bài đưa ra là: “Tôi muốn tự động tạo bảng xếp hạng số lần khách đến thăm theo thời gian thực từ bảng đăng ký. Liệu có thể làm được không?”
Mô tả bài toán
Trong bảng dữ liệu trên, chúng ta cần đếm có điều kiện ở cột C và sau đó sắp xếp theo số lần khách đến thăm, rồi lấy tên của tư vấn viên bất động sản tương ứng.
Chúng tôi gọi đây là bài toán “Thống kê nhóm và sắp xếp”, với ba thách thức chính:
1. Phải đếm có điều kiện, nhưng danh sách tư vấn viên không có sẵn, nên cần lấy dữ liệu trực tiếp từ bảng. Nếu không sử dụng cột phụ, việc đếm này không đơn giản.
2. Các tư vấn viên xuất hiện nhiều lần, số lần khách đến thăm có thể trùng nhau, nhưng danh sách kết quả cần loại bỏ trùng lặp và chỉ hiển thị mỗi tư vấn viên một lần.
3. Sắp xếp theo số lượt đến thăm và lấy đúng tên tư vấn viên tương ứng.
Bài toán này có nhiều cách giải tùy theo phiên bản Excel. Trong Excel 2019 trở xuống, có thể dùng phương pháp INDEX + MOD + LARGE.
1. Phương pháp INDEX + MOD + LARGE
Phương pháp này chỉ sử dụng các hàm Excel cũ, nhưng có thể xử lý vấn đề sắp xếp và loại bỏ trùng lặp một cách hiệu quả. Tuy nhiên, nó khá phức tạp và cần phân tích từng bước.
Công thức chính
=INDEX($C$C,MOD(LARGE(COUNTIF($C$2:$C$300, $C$2:$C$300) *
(COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)), $C$2:$C$300) = 1) +
ROW($C$2:$C$300)%ROW()-2),1)*10000)
Giải thích công thức
1. Đếm số lần xuất hiện của từng tư vấn viên
COUNTIF($C$2:$C$300, $C$2:$C$300)
Công thức này đếm số lần xuất hiện của từng tư vấn viên trong danh sách.
2. Xác định vị trí xuất hiện đầu tiên của mỗi tư vấn viên
COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)), $C$2:$C$300) = 1
Công thức này kiểm tra từng dòng để xác định tư vấn viên xuất hiện lần đầu tiên.
3. Loại bỏ trùng lặp
Nhân hai kết quả trên với nhau để chỉ giữ lại số lần xuất hiện đầu tiên.
4. Sắp xếp theo số lần đến thăm
LARGE(…, ROW() - 2)
Dùng hàm LARGE để lấy giá trị lớn nhất thứ k, tức là sắp xếp dữ liệu theo số lượt khách đến thăm.
5. Lấy tên tư vấn viên tương ứng
INDEX(C:C, …)
Cuối cùng, dùng INDEX để lấy tên tư vấn viên có số lần khách đến thăm cao nhất.
2. Tổng kết
Các bước để giải quyết bài toán:
✅ Bước 1: Đếm số lần xuất hiện của từng tư vấn viên.
✅ Bước 2: Xác định lần xuất hiện đầu tiên để loại bỏ trùng lặp.
✅ Bước 3: Gắn thêm thông tin vị trí dòng để sắp xếp đúng.
✅ Bước 4: Dùng LARGE để lấy số lần xuất hiện lớn nhất.
✅ Bước 5: Dùng INDEX để lấy tên tư vấn viên tương ứng.
Lưu ý: Đây là một công thức phức tạp, nhưng nếu thực hành nhiều lần, bạn sẽ hiểu rõ hơn về cách hoạt động của nó. Trong các phiên bản Excel mới hơn, có những công thức đơn giản hơn để giải quyết bài toán này, chúng tôi sẽ chia sẻ trong bài viết tiếp theo!
