Tìm lớn thứ n với tiêu chí & nhỏ thứ n với tiêu chí trong excel
Trong bài viết này, chúng ta sẽ học cách Tìm nhỏ nhất thứ n với tiêu chí & lớn thứ n với tiêu chí từ bảng đã cho trong Excel.
Tình huống:
Nói một cách đơn giản, trong khi làm việc với các số trong dữ liệu số, đôi khi được đưa ra một điều kiện tức là khi chúng ta cần tìm kiếm giá trị cao thứ 5 đã cho. Bạn có thể thực hiện giải pháp cho vấn đề này một cách dễ dàng bằng cách sử dụng các hàm excel như được giải thích bên dưới.
Lớn thứ n với tiêu chí
Làm thế nào để giải quyết vấn đề?
Đối với bài viết này, chúng tôi sẽ được yêu cầu sử dụng liên kết: / logic-công thức-excel-big-function [hàm LARGE] `. Bây giờ chúng ta sẽ tạo công thức cho các hàm này. Ở đây chúng ta được đưa ra một bảng và chúng ta cần tìm giá trị lớn thứ n trong phạm vi với các tiêu chí đã cho.
Công thức chung:
{ = LARGE ( IF ( c_range = "value" , range ) , n )}
c_range: giá trị phạm vi tiêu chí: phạm vi giá trị khớp tiêu chí: mảng kết quả n: lớn thứ n Lưu ý: Không đặt dấu ngoặc nhọn theo cách thủ công. Đây là công thức mảng, phải sử dụng Ctrl + Shift + Enter thay vì chỉ Enter, nơi nó trả về lỗi #NUM! lỗi.
Ví dụ:
Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới.
Ở đây chúng tôi có thông tin chi tiết về đơn hàng có ngày đặt hàng, khu vực và giá đặt hàng.
Chúng tôi cần tìm giá đặt hàng lớn thứ 5 từ miền Đông. Ở đây phạm vi được cung cấp bằng cách sử dụng công cụ excel phạm vi được đặt tên.
vùng (C3: C16)
giá (D3: D16)
Đầu tiên, chúng ta cần tìm các giá trị lớn thứ năm bằng cách sử dụng hàm LARGE và sau đó phép tính tổng được thực hiện trên 5 giá trị đó. Bây giờ chúng ta sẽ sử dụng công thức sau để lấy tổng
Sử dụng công thức:
{ = LARGE ( IF ( region = G5 , price ) , F5 )}
Giải thích:
LARGE (\ {58,41; 303,63; FALSE; 153,34; 82,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
-
liên kết: / logic-công thức-excel-lớn-hàm [LARGE]
hàm lấy mảng có tất cả phạm vi giá như được hiển thị ở trên và trả về giá trị lớn thứ 5 từ mảng như được hiển thị trong ảnh chụp nhanh bên dưới.
Bạn có thể xem công thức trong hộp công thức như hình minh họa ở trên. Sử dụng Ctrl + Shift + Enter để nhận kết quả.
Như bạn có thể thấy công thức mảng trả về giá lớn thứ 5 là $ 97,72 có vùng Đông.
Bạn cũng có thể cấp dữ liệu mảng dưới dạng mảng, tiêu chí trong dấu ngoặc kép và giá trị n trực tiếp vào hàm thay vì sử dụng tham chiếu ô hoặc phạm vi được đặt tên.
Sử dụng công thức:
{ = LARGE ( IF ( C3:C16 = "West" , D3:D16 ) , 3 )}
Sử dụng Ctrl + Shift + Enter
Bạn có thể thấy công thức hoạt động tốt khi tìm giá trị lớn thứ n có tiêu chí trong bảng.
Thấp nhất thứ n với tiêu chí
Làm thế nào để giải quyết vấn đề?
Công thức chung:
{ = SMALL ( IF ( c_range = "value" , range ) , n )}
c_range: giá trị phạm vi tiêu chí: phạm vi giá trị khớp tiêu chí: mảng kết quả n: lớn thứ n Lưu ý: Không đặt dấu ngoặc nhọn theo cách thủ công. Đây là công thức mảng, phải sử dụng Ctrl + Shift + Enter thay vì chỉ Enter, nơi nó trả về lỗi #NUM! lỗi.
Ví dụ:
Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới.
Tại đây chúng tôi có thông tin chi tiết về đơn hàng có ngày đặt hàng, khu vực và giá đặt hàng.
Chúng tôi cần tìm giá đặt hàng nhỏ thứ n từ miền Đông. Ở đây phạm vi được cung cấp bằng cách sử dụng công cụ excel phạm vi được đặt tên.
vùng (C3: C16)
giá (D3: D16)
Đầu tiên, chúng ta cần tìm các giá trị nhỏ nhất hoặc thấp nhất thứ năm bằng cách sử dụng hàm SMALL và sau đó thao tác tra cứu được thực hiện trên các giá trị.
Bây giờ chúng ta sẽ sử dụng công thức sau để lấy tổng
Sử dụng công thức:
{ = SMALL ( IF ( region = G5 , price ) , F5 )}
Giải thích:
LARGE (\ {58,41; 303,63; FALSE; 153,34; 82,84; FALSE; 520,01; FALSE; 177; FALSE; FALSE; 57,97; 97,72; FALSE}), F5)
hàm lấy mảng có tất cả phạm vi giá như được hiển thị ở trên và trả về giá trị lớn thứ 5 từ mảng như được hiển thị trong ảnh chụp nhanh bên dưới.
Bạn có thể xem công thức trong hộp công thức như hình minh họa ở trên. Sử dụng Ctrl + Shift + Enter để nhận kết quả.
Như bạn có thể thấy, công thức mảng trả về giá nhỏ nhất thứ 5 là $ 153,34 có vùng Đông.
Bạn cũng có thể cấp dữ liệu mảng dưới dạng mảng, tiêu chí trong dấu ngoặc kép và giá trị n trực tiếp vào hàm thay vì sử dụng tham chiếu ô hoặc phạm vi được đặt tên.
Sử dụng công thức:
{ = SMALL ( IF ( C3:C16 = "west" , D3:D16 ) , 3 )}
Sử dụng Ctrl + Shift + Enter
Bạn có thể thấy công thức hoạt động tốt khi bạn tìm thấy giá trị lớn thứ n có tiêu chí trong bảng.
Dưới đây là một số lưu ý quan sát được hiển thị bên dưới.
Ghi chú:
-
Công thức chỉ hoạt động với số.
-
Không đặt dấu ngoặc nhọn theo cách thủ công. Đây là công thức mảng, phải sử dụng Ctrl + Shift + Enter thay vì chỉ Enter, nơi nó trả về lỗi #NUM!
lỗi. . Giá trị của không được lớn hơn số giá trị tiêu chí hoặc nó trả về lỗi #NUM! Lỗi.
-
Tiêu chí IF không khớp với công thức trả về lỗi.
-
Mảng đối số phải có cùng độ dài với hàm khác.
Hy vọng bài viết này về cách Tìm lớn nhất thứ n với tiêu chí & nhỏ nhất thứ n với tiêu chí trong excel là giải thích. Khám phá thêm các bài viết về công thức tra cứu trong Excel tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]