LARGE / SMALL
LARGE(vùng, k)SMALL(vùng, k)Lấy số lớn thứ k hoặc nhỏ thứ k trong một dãy. Đây là hàm dựng bảng Top 10 khách hàng, Top 5 mặt hàng bán chậm, danh sách năm khoản nợ lớn nhất — làm được ngay trên báo cáo mà không phải đụng vào thứ tự của dữ liệu gốc.
Các đối số
| Đối số | Bắt buộc? | Ý nghĩa |
|---|---|---|
| vùng | Bắt buộc | Vùng số đem ra xếp. Ô chữ và ô trống bị bỏ qua nên số phần tử thật có thể ít hơn số dòng của bảng. |
| k | Bắt buộc | Thứ hạng cần lấy: LARGE với k=1 là lớn nhất, SMALL với k=1 là nhỏ nhất. k lớn hơn số phần tử hoặc k nhỏ hơn 1 thì trả về #NUM!. Kéo công thức xuống thì trỏ vào cột số thứ tự, hoặc dùng ROW()-1 cho tự tăng. |
Ví dụ
=LARGE($D$2:$D$500, 1)Doanh số cao nhất; đổi số 1 thành 2, 3 là ra các hạng kế tiếp
Kết quả: 128.000.000
=LARGE($D$2:$D$500, ROW()-1)Đặt ở dòng 2 rồi kéo xuống, tự ra Top 1, Top 2, Top 3 không phải sửa số
Kết quả: 96.400.000
=INDEX($B$2:$B$500, MATCH(LARGE($D$2:$D$500, 1), $D$2:$D$500, 0))Lấy TÊN khách đứng đầu chứ không chỉ con số doanh thu
Kết quả: Công ty TNHH An Phát
Lưu ý khi dùng
- LARGE(vùng, 1) chính là MAX, SMALL(vùng, 1) chính là MIN. Chỉ nên dùng hai hàm này khi cần từ hạng thứ hai trở đi.
- Cần Top n theo điều kiện thì Excel 365 dùng SORT lồng FILTER gọn hơn nhiều, và ra thẳng cả dòng thông tin chứ không chỉ một con số.
- SMALL rất hay dùng để lấy hạn thanh toán gần nhất còn hiệu lực: SMALL của cột ngày sau khi đã lọc bỏ ngày đã qua.
Bẫy hay dính
Hai khách có doanh số bằng nhau thì cặp LARGE ghép MATCH luôn trả về CÙNG MỘT TÊN cho cả hai dòng của bảng Top: tên bị lặp và một khách biến mất khỏi bảng xếp hạng, trong khi cột số tiền vẫn đúng nên rất khó nhận ra. Chữa bằng một cột phụ cộng thêm một lượng cực nhỏ theo số dòng để phá hoà, hoặc dùng SORTBY trên Excel 365.