Đặt giới hạn số dòng kết quả của hàm FILTER

[htin1997]

Xin chào mọi người.
Như tiêu đề, em/con xin hỏi có cách nào để đưa ra giới hạn số dòng kết quả của hàm filter không ạ, vì muốn đặt công thức bên dưới kết quả hàm FILTER (do đưa lên chart nên cần range liên tục) và tránh được lỗi #SPILL.
Ví dụ: Chỉ muốn giới hạn 3 kết quả đầu tiên của hàm FILTER.

 

[befaint]

=index(công thức đã có, {1;2;3})

 

[VetMini]

=index(công thức đã có, {1;2;3})

Có 2 cách làm:
1. như công thức trên thì cả cụm thành ra một mảng. Nhưng lợi ở chỗ Filter chỉ tính 1 lần.
2. nếu dùng công thức Index(Công thức đã có, Row($a1)) để kéo xuống thì các ô độc lập với nhau. Nhưng bất lợi ở chỗ mỗi ô là tính lại hàm Filter.

 

[befaint]

Có 2 cách làm:
1. như công thức trên thì cả cụm thành ra một mảng. Nhưng lợi ở chỗ Filter chỉ tính 1 lần.
2. nếu dùng công thức Index(Công thức đã có, Row($a1)) để kéo xuống thì các ô độc lập với nhau. Nhưng bất lợi ở chỗ mỗi ô là tính lại hàm Filter.

Họ đang dùng Excel365 đó anh. Nên công thức mảng không cần phải nhập vào một vùng đâu, chỉ cần nhập công thức vào 1 ô thôi.

 

[VetMini]

Họ đang dùng Excel365 đó anh. Nên công thức mảng không cần phải nhập vào một vùng đâu, chỉ cần nhập công thức vào 1 ô thôi.

Thế nó không #SPILL khi bạn còn 2 ô sao?

 

[befaint]

Thế nó không #SPILL khi bạn còn 2 ô sao?

Nó vẫn là công thức trả về mảng thôi anh. Nên công thức đó sẽ trả về #SPILL nếu vùng trả kết quả đã có gì đó.

Nhưng giải quyết được yêu cầu đề ra là chủ động lựa chọn số lượng kết quả trả về.

 

[ptm0412]

Thế nó không #SPILL khi bạn còn 2 ô sao?

Nếu công thức mảng ở 365 ra kết quả n ô, nhưng chỉ có ít hơn n ô trống, sẽ bị lỗi #SPILL.
Kết quả ở đây là kết quả cuối cùng (3 ô) chứ không phải n ô như ban đầu, nên chỉ cần 3 ô trống. Ít hơn 3 vẫn lỗi

 

[VetMini]

Nó vẫn là công thức trả về mảng thôi anh. Nên công thức đó sẽ trả về #SPILL nếu vùng trả kết quả đã có gì đó.

Nhưng giải quyết được yêu cầu đề ra là chủ động lựa chọn số lượng kết quả trả về.

Nếu công thức mảng ở 365 ra kết quả n ô, nhưng chỉ có ít hơn n ô trống, sẽ bị lỗi #SPILL.
Kết quả ở đây là kết quả cuối cùng (3 ô) chứ không phải n ô như ban đầu, nên chỉ cần 3 ô trống. Ít hơn 3 vẫn lỗi

Ý tôi là nếu dùng mảng thì phải giới hạn mảng của mình bao lớn.
Khi 3 bị thâu lại còn 2 thì phải sửa công thức.
Nếu tách chúng ra, khi 3 thâu lại còn 2 thì cứ việc thâu.

Lưu ý rằng tôi không hề bảo "không nên" dùng mảng. Chính bài #3 tôi có nêu rõ cái hiệu suất của mảng. Và nếu tôi làm việc thì tôi dùng mảng. Chỉ là tôi không làm giống thớt thôi. Tự dưng một cụm tin tức n món mà chỉ trưng ra n-m món thì là tin tức không trọn vẹn. Trong tôn chỉ làm việc của tôi, tin tức chỉ nhìn thấy một nửa là tin tức không đáng tin cậy.

 

[ptm0412]

Trong tôn chỉ làm việc của tôi, tin tức chỉ nhìn thấy một nửa là tin tức không đáng tin cậy.

Tôi không biết tác giả muốn lấy 3 dòng đầu với mục đích gì, nhưng hiển thị top 5, top 10 trên báo cáo là bình thường

 

[VetMini]

Tôi không biết tác giả muốn lấy 3 dòng đầu với mục đích gì, nhưng hiển thị top 5, top 10 trên báo cáo là bình thường

Top N có sự sắp xếp, phân hạng.
Filter không ngầm chứa sự sắp xếp - trừ phi ta thêm phần sắp xếp, phân hạng.
Nguyên tắc cũng giống như trình bày kết quả của SQL Query (hay bất cứ một query chuẩn nào). Nếu không sắp xếp thì người đọc phải hiểu là mọi dòng có ưu tiên ngang nhau.

Chú: top 5 chưa chắc chỉ có 5 dòng.
Luật thống kê:
Khi làm thống kê, người ta phải phân biệt liên hệ dữ liệu dạng 1-1 và dạng 1-N
Ví dụ:
1. Liệt kê 5 điểm cao nhất trong môn Toán. Điểm là dạng 1-1 (điểm 10 chỉ có 1; điểm 9,5 chỉ có 1) cho nên đáp án chỉ có 5.
2. Liệt kê học sinh với 5 điểm cao nhất trong môn Toán. Học sinh đạt điểm là dạng 1-N (có thể có n học sinh đạt điểm 10), đáp án có thể nhiều hơn 5.
Nếu đề bài nói là "liệt kê 5 học sinh cao điểm nhất..." thì đề bài rõ ràng rằng đề bài phản thống kê.

 

[ptm0412]

Top N có sự sắp xếp, phân hạng.
Filter không ngầm chứa sự sắp xếp - trừ phi ta thêm phần sắp xếp, phân hạng.
Nguyên tắc cũng giống như trình bày kết quả của SQL Query (hay bất cứ một query chuẩn nào). Nếu không sắp xếp thì người đọc phải hiểu là mọi dòng có ưu tiên ngang nhau.

Tôi hiểu ý anh, chỉ phản biện những ý nhỏ:
Hàm Filter của Excel tham chiếu đến 1 vùng dữ liệu, nếu vùng dữ liệu đó đã được sắp xếp thì dù bản thân hàm filter không chứa việc sắp xếp cũng lấy được top n. Tất nhiên tôi vẫn đồng ý về top n theo lý thuyết thống kê mà anh trình bày.
Nếu vùng dữ liệu chưa sắp xếp thì phải dùng thêm hàm SORT bên trong hàm Filter.

Ghi chú: Đây là tôi nói chung chứ không nói về chủ đề này.