Xu Hướng 2/2023 # Hàm Small Của Excel Để Tìm Và Đánh Dấu Các Giá Trị Thấp Nhất # Top 4 View | Hoisinhvienqnam.edu.vn

Xu Hướng 2/2023 # Hàm Small Của Excel Để Tìm Và Đánh Dấu Các Giá Trị Thấp Nhất # Top 4 View

Bạn đang xem bài viết Hàm Small Của Excel Để Tìm Và Đánh Dấu Các Giá Trị Thấp Nhất được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

Trong hướng dẫn ngắn này, chúng ta sẽ nói về hàm SMALL của Excel, cách hoạt động và cách sử dụng nó để tìm số, ngày hoặc giờ nhỏ nhất thứ N.

Bạn cần tìm một vài số thấp nhất trong một trang tính? Điều này khá dễ thực hiện với tính năng Sắp xếp trong Excel. Bạn không muốn lãng phí thời gian vào việc sắp xếp lại dữ liệu của mình với mỗi lần thay đổi? Hàm SMALL sẽ giúp bạn nhanh chóng tìm ra giá trị nhỏ nhất, nhỏ nhất thứ hai, nhỏ nhất thứ ba, v.v.

Hàm SMALL của Excel

SMALL là một hàm thống kê trả về giá trị nhỏ nhất thứ n trong một tập dữ liệu.

Cú pháp của hàm SMALL bao gồm hai đối số, cả hai đều là bắt buộc.

Small (mảng, k)

Ở đâu:

Mảng – một mảng hoặc một dải ô để trích xuất giá trị nhỏ nhất.

K – một số nguyên cho biết vị trí từ giá trị thấp nhất trở về, tức là nhỏ nhất thứ k.

Hàm có sẵn trong tất cả các phiên bản Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và các phiên bản cũ hơn.

Công thức Small cơ bản trong Excel

Một công thức Small ở dạng cơ bản rất dễ xây dựng – bạn chỉ cần chỉ định phạm vi và vị trí từ mục nhỏ nhất để trả về.

Trong danh sách các số trong B2: B10, giả sử bạn muốn trích xuất 3 rd giá trị nhỏ nhất. Công thức đơn giản như sau:

=SMALL(B2:B10, 3)

Để giúp bạn kiểm tra kết quả dễ dàng hơn, cột B được sắp xếp theo thứ tự tăng dần:

4 điều bạn nên biết về hàm Small

Những lưu ý sử dụng sau đây sẽ giúp bạn hiểu rõ hơn về hoạt động của hàm SMALL và tránh nhầm lẫn khi xây dựng công thức cho riêng mình.

Bất kì ô trống, bản văn giá trị và hợp lý giá trị TRUE và FALSE trong mảng đối số bị bỏ qua.

Nếu mảng chứa một hoặc nhiều sai sót, một lỗi được trả lại.

Trong trường hợp có trùng lặp trong mảng, công thức của bạn có thể dẫn đến “ràng buộc”. Ví dụ: nếu hai ô chứa số 1 và hàm SMALL được cấu hình để trả về giá trị nhỏ nhất và số 2nd giá trị nhỏ nhất, bạn sẽ nhận được 1 trong cả hai trường hợp.

Giả sử n là số giá trị trong mảng, SMALL (mảng, 1) sẽ trả về giá trị thấp nhất và SMALL (mảng, n) sẽ chọn giá trị cao nhất.

Cách sử dụng hàm SMALL trong Excel – ví dụ về công thức

Và bây giờ, chúng ta hãy xem xét thêm một số ví dụ về hàm SMALL của Excel vượt ra ngoài cách sử dụng cơ bản của nó.

Tìm các giá trị 3, 5, 10, v.v.

Như bạn đã biết, hàm SMALL được thiết kế để tính giá trị thấp nhất thứ n. Ví dụ này cho thấy cách làm điều này hiệu quả nhất.

=SMALL($B$2:$B$10, D3)

Trong E3, công thức trích ra giá trị nhỏ nhất bằng cách sử dụng số trong D3 cho k tranh luận. Điều quan trọng là cung cấp các tham chiếu ô thích hợp để công thức sao chép chính xác trong các ô khác: tuyệt đối cho mảng và quan hệ cho k.

Bạn không muốn làm phiền gõ các cấp bậc theo cách thủ công? Sử dụng chức năng ROWS với mở rộng phạm vi tham chiếu để cung cấp k giá trị. Đối với điều này, chúng tôi thực hiện một tham chiếu tuyệt đối cho ô đầu tiên (hoặc chỉ khóa tọa độ hàng như B $ 2) và tham khảo tương đối cho ô cuối cùng:

=SMALL($B$2:$B$10, ROWS(B$2:B2))

Do đó, tham chiếu phạm vi mở rộng khi công thức được sao chép xuống cột. Trong D2, ROWS (B $ 2: B2) tạo ra 1 cho kvà công thức trả về chi phí thấp nhất. Trong D3, ROWS (B $ 2: B3) mang lại 2 và chúng tôi nhận được 2 nd chi phí thấp nhất, v.v.

Chỉ cần sao chép công thức qua 5 ô và bạn sẽ nhận được 5 giá trị dưới cùng:

Tính tổng N giá trị dưới cùng

Muốn tìm tổng các giá trị n nhỏ nhất trong một tập dữ liệu? Nếu bạn đã trích xuất các giá trị như được hiển thị trong ví dụ trước, giải pháp đơn giản nhất sẽ là TỔNG công thức như:

Hoặc bạn có thể tạo một công thức độc lập bằng cách sử dụng hàm SMALL cùng với GIỚI THIỆU:

SUMPRODUCT (Small ( mảng, {1,…, n}))

Để nhận tổng của 3 giá trị dưới cùng trong tập dữ liệu của chúng tôi, công thức có dạng sau:

=SUMPRODUCT(SMALL(B2:B10, {1,2,3}))

Hàm SUM sẽ cho kết quả tương tự:

Ghi chú. Nếu bạn dùng tham chiếu ô thay vì mảng hằng số cho k, bạn cần nhấn Ctrl + Shift + Enter để biến nó thành một công thức mảng. Trong Excel 365 hỗ trợ mảng động, SUM SMALL hoạt động như một công thức thông thường trong cả hai trường hợp.

=SUM(SMALL(B2:B10, {1,2,3}))

Trong một công thức thông thường, SMALL trả về một giá trị nhỏ nhất thứ k trong một phạm vi. Trong trường hợp này, chúng tôi cung cấp một mảng hằng số như {1,2,3} cho đối số k, buộc nó trả về một mảng gồm 3 giá trị nhỏ nhất:

Công thức này hoạt động như thế nào:

Hàm SUMPRODUCT hoặc SUM cộng các số trong mảng và xuất ra tổng. Đó là nó!

{29240, 43610, 58860}

Công thức INDEX MATCH SMALL để nhận các kết quả phù hợp nhỏ nhất

Trong trường hợp bạn muốn truy xuất một số dữ liệu được liên kết với giá trị nhỏ nhất, hãy sử dụng INDEX MATCH kết hợp với SMALL cho giá trị tra cứu:

INDEX( return_array, MATCH (Small ( lookup_array, n), lookup_array, 0))

Ở đâu:

Lookup_array là một phạm vi để tìm kiếm giá trị thứ n thấp nhất.

N là vị trí của giá trị nhỏ nhất cần quan tâm.

Ví dụ, để lấy tên của dự án có chi phí thấp nhất, công thức trong E3 là:

Trong đó A2: A10 là tên dự án, B2: B10 là chi phí và D3 là thứ hạng từ nhỏ nhất.

=INDEX($A$2:$A$10, MATCH(SMALL($B$2:$B$10, D3), $B$2:$B$10, 0))

Sao chép công thức vào các ô bên dưới (E4 và E5), và bạn sẽ nhận được tên của 3 dự án rẻ nhất:

Giải pháp này hoạt động tốt cho một tập dữ liệu không có bản sao. Tuy nhiên, hai hoặc nhiều giá trị trùng lặp trong một cột số có thể tạo ra “ràng buộc” trong xếp hạng, dẫn đến kết quả sai. Trong trường hợp này, hãy sử dụng tinh vi hơn một chút công thức để phá vỡ quan hệ.

Trong Excel 365, tác vụ này có thể được thực hiện với sự trợ giúp của các hàm mảng động mới. Ngoài việc đơn giản hơn nhiều, cách tiếp cận này tự động giải quyết vấn đề ràng buộc. Để biết chi tiết đầy đủ, vui lòng xem Cách lấy giá trị N dưới cùng trong Excel.

Ghi chú:

Sắp xếp các số từ thấp nhất đến cao nhất với một công thức

Tôi tin rằng mọi người đều biết cách đặt các con số theo thứ tự Sắp xếp Excel đặc tính. Nhưng bạn có biết cách thực hiện sắp xếp theo công thức không? Người dùng Excel 365 có thể làm điều đó một cách dễ dàng với Hàm SORT. Trong Excel 2019, 2016 và các phiên bản trước đó, SORT không hoạt động, than ôi. Nhưng có một chút niềm tin, và Small sẽ đến giải cứu 🙂

Giống như trong ví dụ đầu tiên, chúng tôi sử dụng hàm ROWS với tham chiếu phạm vi mở rộng để tăng k bởi 1 trong mỗi hàng nơi công thức được sao chép:

Nhập công thức vào ô đầu tiên, sau đó kéo công thức xuống bao nhiêu ô có giá trị trong tập dữ liệu gốc (trong ví dụ này là C2: C10):

=SMALL($A$2:$A$10, ROWS(A$2:A2))

Tiền boa. Xắp xếp giảm dần, sử dụng hàm LARGE thay vì SMALL.

Vì ngày và giờ cũng là giá trị số (trong hệ thống Excel nội bộ, ngày được lưu trữ dưới dạng số tuần tự và lần dưới dạng phân số thập phân), hàm SMALL cũng có thể xử lý chúng mà không cần thêm bất kỳ nỗ lực nào từ phía bạn.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, một công thức cơ bản mà chúng tôi đã sử dụng cho các con số cũng hoạt động tốt cho ngày và giờ:

Công thức Small để tìm 3 ngày sớm nhất:

=SMALL($B$2:$B$10, D2)

Công thức Small để nhận được 3 lần ngắn nhất:

Trong danh sách ngày tháng, giả sử bạn muốn tìm ngày gần nhất trước một ngày cụ thể. Điều này có thể được thực hiện bằng cách sử dụng hàm SMALL kết hợp với COUNTIF.

Với danh sách các ngày trong B2: B10 và ngày mục tiêu trong E1, công thức sau sẽ trả về một ngày trước đó gần với ngày mục tiêu nhất:

Để trích xuất một ngày hai ngày trước ngày trong E1, tức là một ngày trước đó nhưng một ngày, công thức là:

=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)) =SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1)

Để tìm một ngày trong quá khứ gần nhất với ngày hôm nay, sử dụng Hàm TODAY cho tiêu chí của COUNTIF:

Tiền boa. Để tránh lỗi trong trường hợp không tìm thấy ngày phù hợp với tiêu chí của bạn, bạn có thể kết thúc Hàm IFERROR xung quanh công thức của bạn, như sau:

=SMALL(B2:B10, COUNTIF(B2:B10, "<"&TODAY()))

Ý tưởng chung là đếm số ngày nhỏ hơn ngày mục tiêu bằng COUNTIF. Và số lượng này chính xác là những gì hàm SMALL cần cho k tranh luận.

Để hiểu rõ hơn về khái niệm này, chúng ta hãy xem xét nó từ một góc độ khác:

=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1), "Not Found")

Nếu ngày 1 tháng 8 năm 2020 (ngày mục tiêu ở E1) xuất hiện trong tập dữ liệu của chúng tôi, thì đó sẽ là ngày 7 thứ tự ngày lớn nhất trong danh sách. Do đó, có sáu ngày nhỏ hơn nó. Có nghĩa là 6 thứ tự ngày nhỏ nhất là ngày trước đó gần nhất với ngày mục tiêu.

Cách hoạt động của các công thức này:

Vì vậy, trước tiên, chúng tôi tính toán xem có bao nhiêu ngày nhỏ hơn ngày trong E1 (kết quả là 6):

Và sau đó, cắm số đếm vào 2 nd đối số của Small:

Để có được ngày trước nhưng một ngày (là 5 thứ tự ngày nhỏ nhất trong trường hợp của chúng tôi), chúng tôi trừ 1 từ kết quả của COUNTIF.

COUNTIF(B2:B10, "<"&E1)

Cách tô sáng các giá trị dưới cùng trong Excel

=SMALL(B2:B10, 6)

Chọn phạm vi mà bạn muốn đánh dấu các giá trị dưới cùng. Trong trường hợp của chúng tôi, các số ở B2: B10, vì vậy chúng tôi chọn nó. Nếu bạn muốn đánh dấu toàn bộ hàng, hãy chọn A2: B10.

bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng.

bên trong Định dạng các giá trị trong đó công thức này đúng , hãy nhập một công thức như sau:=B2<=SMALL($B$2:$B$10, 3)Trong đó B2 là ô ngoài cùng bên trái của phạm vi số sẽ được kiểm tra, $ B $ 2: $ B $ 10 là toàn bộ phạm vi và 3 là n giá trị dưới cùng để đánh dấu.Trong công thức của bạn, hãy lưu ý đến các loại tham chiếu: ô ngoài cùng bên trái là tham chiếu tương đối (B2) trong khi phạm vi là tham chiếu tuyệt đối ($ B $ 2: $ B $ 10).

Nhấn vào định dạng và chọn bất kỳ định dạng nào bạn thích.

Bấm OK hai lần để đóng cả hai cửa sổ hộp thoại.

Làm xong! 3 giá trị dưới cùng trong cột B được đánh dấu:

Để biết thêm thông tin, vui lòng xem Định dạng có điều kiện trong Excel dựa trên công thức.

Hàm SMALL của Excel không hoạt động

Như bạn vừa thấy từ các ví dụ của chúng tôi, việc sử dụng hàm SMALL trong Excel khá dễ dàng và bạn không gặp khó khăn gì với nó. Nếu công thức của bạn không hoạt động, rất có thể đó sẽ là lỗi #NUM! , có thể xảy ra vì những lý do sau:

Mảng trống hoặc không chứa một giá trị số.

Các k giá trị nhỏ hơn 0 (một lỗi đánh máy ngớ ngẩn có thể khiến bạn mất hàng giờ khắc phục sự cố!) hoặc vượt quá số giá trị trong mảng.

Các Giá Trị Khác Biệt Và Duy Nhất Trong Excel: Cách Tìm Kiếm, Lọc, Lựa Chọn Và Đánh Dấu

Trong bài viết lần này, Học Excel Online sẽ chỉ ra cho các bạn cách hiệu quả nhất để tìm kiếm, chọn lọc và đánh dấu các giá trị duy nhất và khác biệt trong Excel.

Giá trị duy nhất là các mục xuất hiện trong tập dữ liệu duy nhất một lần.

Giá trị khác biệt là tất cả các mục khác nhau trong danh sách, nghĩa là giá trị duy nhất và lần xuất hiện thứ nhất của các giá trị nhân bản.

Tìm các giá trị duy nhất trong một cột

Để tìm các giá trị khác biệt và duy nhất trong một danh sách, bạn sử dụng một trong các công thức sau đây, trong đó A2 là ô đầu tiên và A10 là ô cuối cùng có dữ liệu.

Cách tìm các giá trị duy nhất trong Excel:

=IF(COUNTIF($A$2:$A$10, $A2)=1, “Unique”, “”)

Cách tìm các giá trị khác biệt trong Excel:

=IF(COUNTIF($A$2:$A2, $A2)=1, “Distinct”, “”)

Tìm hàng duy nhất / khác biệt trong Excel

Theo cách tương tự, bạn có thể tìm các hàng duy nhất trong bảng Excel dựa trên các giá trị trong 2 cột trở lên. Trong trường hợp này, bạn cần phải sử dụng hàm COUNTIFS thay vì COUNTIF để đánh giá các giá trị trong một vài cột (có thể đánh giá được tới 127 phạm vi/ cặp tiêu chuẩn trong một công thức đơn).

Ví dụ: để tìm tên duy nhất hoặc khác biệt trong danh sách, hãy sử dụng các công thức sau:

Công thức để tìm các hàng duy nhất:

=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2)=1, “Unique row”, “”)

Công thức để tìm các hàng khác biệt:

Tìm các giá trị duy nhất / khác biệt trong Excel phân biệt chữ hoa và chữ thường

Nếu bạn đang làm việc với một bộ dữ liệu trong trường hợp có vấn đề, bạn cần một công thức mảng phức tạp hơn một chút.

Tìm các giá trị duy nhất phân biệt chữ hoa và chữ thường:

=IF(SUM((-EXACT($A$2:$A$10,A2)))=1,”Unique”,””)

Tìm các giá trị riêng biệt phân biệt chữ hoa và chữ thường:

=IF(SUM((-EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)

Cách lọc các giá trị duy nhất và khác biệt trong Excel

Để xem riêng các giá trị duy nhất hoặc khác biệt trong danh sách, chúng ta cần lọc chúng ra bằng cách thực hiện các bước sau.

Cách chọn các giá trị khác biệt / độc nhất

Nếu bạn có một danh sách các giá trị duy nhất / khác biệt tương đối nhỏ, bạn chỉ cần chọn nó theo cách thông thường bằng cách dùng chuột. Nếu danh sách được lọc có hàng trăm hoặc hàng nghìn hàng, bạn có thể sử dụng một trong số các phím tắt để tiết kiệm thời gian.

Để nhanh chóng, bạn chọn danh sách duy nhất hoặc khác biệt bao gồm tiêu đề cột, lọc giá trị duy nhất, nhấp vào bất kỳ ô nào trong danh sách duy nhất, sau đó nhấn Ctrl + A.

Để chọn các giá trị khác biệt hoặc duy nhất mà không có tiêu đề cột, hãy lọc giá trị duy nhất, chọn ô đầu tiên với dữ liệu và nhấn Ctrl + Shift + End để mở rộng vùng chọn vào ô cuối cùng.

Mẹo. Trong một số trường hợp hiếm hoi, chủ yếu là các trường hợp làm việc với dữ liệu quá lớn, các phím tắt trên có thể sẽ chọn cả những ô hiển thị và không hiển thị. Để khắc phục điều này, nhấn Ctrl + A hoặc Ctrl + Shift + End đầu tiên, và sau đó nhấn Alt +; Để chỉ chọn các ô hiển thị, bỏ qua hàng ẩn.

Sao chép các giá trị duy nhất hoặc khác biệt đến vị trí khác

Để sao chép danh sách các giá trị duy nhất sang vị trí khác, chỉ cần thực hiện theo các bước sau:

– Chọn các giá trị được lọc bằng chuột hoặc các phím tắt đã đề cập ở trên. – Nhấn Ctrl + C để sao chép các giá trị đã chọn. – Chọn ô trên cùng bên trái trong dải đích (nó có thể nằm trên cùng một tờ hoặc khác tờ), và nhấn Ctrl + V để dán giá trị.

Cách làm nổi bật các giá trị duy nhất và khác biệt trong Excel

Bất cứ khi nào bạn cần làm nổi bật bất cứ điều gì trong Excel dựa trên một điều kiện nhất định, thì cần áp dụng tính năng định dạng có điều kiện.

Đánh dấu các giá trị duy nhất trong một cột (quy tắc built-in)

Cách nhanh nhất và dễ dàng nhất để làm nổi bật các giá trị duy nhất trong Excel là áp dụng quy tắc định dạng có điều kiện cho sẵn:

Mẹo. Nếu bạn không hài lòng với bất kỳ định dạng nào được thiết lập trước, hãy nhấp vào Custom Format…(mục cuối cùng trong danh sách thả xuống) và đặt màu và/ hoặc phông chữ theo ý thích của bạn.

Như bạn thấy, làm nổi bật các giá trị duy nhất trong Excel là một trong những nhiệm vụ vô cùng đơn giản. Tuy nhiên, quy tắc được xây dựng trong Excel chỉ hoạt động cho các mục xuất hiện trong danh sách duy nhất một lần. Nếu bạn cần làm nổi bật các giá trị khác biệt – gồm 1 lần xuất hiện đại diện cho những giá trị trùng lặp – bạn sẽ phải tạo quy tắc của riêng mình dựa trên công thức. Bạn cũng cần phải tạo một quy tắc tùy chỉnh để đánh dấu các hàng duy nhất dựa trên các giá trị trong một hoặc nhiều cột.

Đánh dấu các giá trị duy nhất và khác biệt trong Excel (quy tắc tùy chỉnh)

Để làm nổi bật các giá trị duy nhất hoặc khác biệt trong một cột, hãy chọn dữ liệu nhưng không bao gồm tiêu đề cột (Hẳn là bạn cũng không muốn làm nổi bật tiêu đề cột) Và tạo ra một quy tắc định dạng có điều kiện với một trong các công thức sau.

Đánh dấu các giá trị duy nhất

Để làm nổi bật các giá trị xuất hiện trong một danh sách duy nhất một lần, sử dụng công thức sau:

=COUNTIF($A$2:$A$10,$A2)=1

Với A2 là ô đầu tiên và A10 là ô cuối cùng của phạm vi áp dụng.

Đánh dấu các giá trị khác biệt Để làm nổi bật tất cả các giá trị khác nhau trong một cột, tức là giá trị duy nhất và 1 lần xuất hiện đại diện cho những giá trị trùng lặp, hãy làm theo công thức sau:

=COUNTIF($A$2:$A2,$A2)=1

Với A2 là ô trên cùng của dải.

Cách tạo quy tắc dựa trên công thức

Để tạo một quy tắc định dạng có điều kiện dựa trên công thức, hãy thực hiện theo các bước sau:

Đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất / khác biệt trong một cột

Để đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất trong một cột cụ thể, hãy sử dụng các công thức cho các giá trị duy nhất và khác biệt mà chúng ta đã sử dụng trong ví dụ trước, nhưng áp dụng quy tắc của bạn cho toàn bộ bảng chứ không phải là một cột đơn.

Cách làm nổi bật các hàng duy nhất trong Excel

Nếu bạn muốn làm nổi bật các hàng dựa trên các giá trị trong 2 hoặc nhiều cột, hãy sử dụng chức năng COUNTIFS cho phép chỉ định một số tiêu chí trong một công thức.

Làm nổi bật các hàng duy nhất =COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)=1

Cách nhanh và dễ dàng để tìm và đánh dấu các giá trị duy nhất trong Excel

Như bạn đã thấy, Microsoft Excel cung cấp khá nhiều tính năng hữu ích có thể giúp bạn xác định và đánh dấu các giá trị duy nhất trong bảng tính của bạn. Tuy nhiên, tất cả những giải pháp đó khó có thể gọi là trực quan và dễ sử dụng vì chúng đòi hỏi phải ghi nhớ một số công thức khác nhau. Tất nhiên, Với các chuyên gia Excel thì việc ghi nhớ chẳng có gì khó khăn cả. Nhưng đối với những người dùng Excel thì có một cách nhanh chóng và đơn giản để tìm các giá trị duy nhất trong Excel.

Chọn một trong các thao tác sau để thực hiện trên các giá trị đã tìm thấy:

Highlight unique values with color – Đánh dấu các giá trị duy nhất dùng màu sắc

Select unique values – Chọn giá trị duy nhất

Identify in a status column – Xác định trong cột trạng thái

Công Thức Excel Max If Để Tìm Giá Trị Lớn Nhất Với Các Điều Kiện

Bài viết cho thấy một vài cách khác nhau để có được giá trị tối đa trong Excel dựa trên một hoặc một số điều kiện mà bạn chỉ định.

Trong hướng dẫn trước của chúng tôi, chúng tôi đã xem xét các sử dụng phổ biến của Hàm MAX được thiết kế để trả về số lớn nhất trong bộ dữ liệu. Tuy nhiên, trong một số trường hợp, bạn có thể cần đi sâu vào dữ liệu của mình hơn nữa để tìm giá trị tối đa dựa trên các tiêu chí nhất định. Điều này có thể được thực hiện bằng cách sử dụng một vài công thức khác nhau và bài viết này giải thích tất cả các cách có thể.

Công thức Excel MAX IF

Cho đến gần đây, Microsoft Excel không có chức năng MAX IF tích hợp để có được giá trị tối đa dựa trên các điều kiện. Trước đây, họ đã giới thiệu MAXIFS và bây giờ, người dùng Excel 2019 và Excel 2016 có kèm theo đăng ký Office 365 có thể thực hiện tối đa điều kiện một cách dễ dàng.

Trong Excel 2013 và các phiên bản trước đó, bạn vẫn phải tạo riêng cho mình công thức mảng bằng cách kết hợp hàm MAX với câu lệnh IF:

{= MAX (NẾU ( tiêu chí= = tiêu chí, Phạm vi tối đa))}

Để xem công thức MAX IF chung này hoạt động như thế nào trên dữ liệu thực, vui lòng xem xét ví dụ sau. Giả sử, bạn có một bảng với kết quả nhảy xa của nhiều sinh viên. Bảng bao gồm dữ liệu cho ba vòng và bạn đang tìm kiếm kết quả tốt nhất của một vận động viên cụ thể, Jacob nói. Với tên sinh viên trong A2: A10 và khoảng cách trong C2: C10, công thức có hình dạng này:

=MAX(IF(A2:A10="Jacob", C2:C10))

Hãy nhớ rằng một công thức mảng phải luôn luôn được nhập bằng cách nhấn Ctrl + Shift + Enter Phím đồng thời. Kết quả là, nó được tự động bao quanh với dấu ngoặc nhọn như hiển thị trong ảnh chụp màn hình bên dưới (nhập niềng răng bằng tay sẽ không hoạt động!).

Tôi là bảng tính thực tế, sẽ thuận tiện hơn khi nhập tiêu chí vào một số ô, để bạn có thể dễ dàng thay đổi điều kiện mà không cần thay đổi công thức. Vì vậy, chúng tôi nhập tên mong muốn trong F1 và nhận được kết quả sau:

=MAX(IF(A2:A10=F1, C2:C10))

Công thức này hoạt động như thế nào

Trong bài kiểm tra logic của hàm IF, chúng tôi so sánh danh sách các tên (A2: A10) với tên đích (F1). Kết quả của hoạt động này là một mảng TRUE và FALSE, trong đó các giá trị TRUE biểu thị các tên khớp với tên đích (Jacob):

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

Cho giá trị_ if_true đối số, chúng tôi cung cấp kết quả nhảy xa (C2: C10), vì vậy nếu kiểm tra logic đánh giá thành TRUE, số tương ứng từ cột C được trả về. Các giá trị_ if_false đối số bị bỏ qua, nghĩa là sẽ chỉ có giá trị FALSE trong đó điều kiện không được đáp ứng:

{FALSE;FALSE;FALSE;5.48;5.42;5.57;FALSE;FALSE;FALSE}

Mảng này được đưa đến hàm MAX, trả về số lượng tối đa bỏ qua các giá trị FALSE.

Công thức MAX IF với nhiều tiêu chí

Trong trường hợp khi bạn cần tìm giá trị tối đa dựa trên nhiều điều kiện, bạn có thể: Sử dụng báo cáo IF lồng nhau để bao gồm các tiêu chí bổ sung:

Hoặc xử lý nhiều tiêu chí bằng cách sử dụng thao tác nhân:

Giả sử bạn có kết quả nam và nữ trong một bảng duy nhất và bạn muốn tìm ra bước nhảy dài nhất trong số các cô gái ở vòng 3. Để hoàn thành, chúng tôi nhập tiêu chí đầu tiên (nữ) vào G1, tiêu chí thứ hai (3) trong G2 và sử dụng các công thức sau để tính giá trị tối đa:

=MAX(IF(B2:B16=G1, IF(C2:C16=G2, D2:D16))) =MAX(IF((B2:B16=G1)*(C2:C16=G2), D2:D16))

Vì cả hai đều là công thức mảng, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn thành chúng một cách chính xác.

Như được hiển thị trong ảnh chụp màn hình bên dưới, các công thức tạo ra kết quả tương tự, do đó, việc sử dụng cái nào là vấn đề sở thích cá nhân của bạn. Đối với tôi, công thức với logic Boolean dễ đọc và xây dựng hơn – nó cho phép thêm bao nhiêu điều kiện bạn muốn mà không cần lồng các hàm IF bổ sung.

Những công thức này hoạt động như thế nào

Công thức đầu tiên sử dụng hai hàm IF lồng nhau để đánh giá hai tiêu chí. Trong thử nghiệm logic của câu lệnh IF đầu tiên, chúng tôi so sánh các giá trị trong cột Giới tính (B2: B16) với tiêu chí trong G1 (“Nữ”). Kết quả là một mảng các giá trị TRUE và FALSE trong đó TRUE đại diện cho dữ liệu phù hợp với tiêu chí:

{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE}

Theo cách tương tự, hàm IF thứ hai kiểm tra các giá trị trong cột Round (C2: C16) theo tiêu chí trong G2.

Cho value_if_true đối số trong câu lệnh IF thứ hai, chúng tôi cung cấp kết quả nhảy xa (D2: D16) và bằng cách này, chúng tôi nhận được các mục có TRUE trong hai mảng đầu tiên ở các vị trí tương ứng (ví dụ: các mục có giới tính là “nữ” và làm tròn là 3):

{FALSE; FALSE; FALSE; FALSE; FALSE; 4.63; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 4.52}

Mảng cuối cùng này đi đến hàm MAX và nó trả về số lớn nhất.

Công thức thứ hai đánh giá các điều kiện tương tự trong một thử nghiệm logic đơn và hoạt động nhân giống như toán tử AND:

Khi các giá trị TRUE và FALSE được sử dụng trong bất kỳ hoạt động số học nào, chúng sẽ được chuyển đổi thành 1 và 0, tương ứng. Và bởi vì nhân với 0 luôn cho số không, mảng kết quả chỉ có 1 khi tất cả các điều kiện là TRUE. Mảng này được đánh giá trong bài kiểm tra logic của hàm IF, trả về khoảng cách tương ứng với các phần tử 1 (TRUE).

MAX IF không có mảng

Nhiều người dùng Excel, bao gồm cả tôi, có thành kiến ​​với các công thức mảng và cố gắng loại bỏ chúng bất cứ khi nào có thể. May mắn thay, Microsoft Excel có một vài chức năng xử lý mảng nguyên bản và chúng ta có thể sử dụng một trong các chức năng đó, cụ thể là TÓM TẮT, như một loại “trình bao bọc” xung quanh MAX.

Công thức MAX IF chung không có mảng như sau:

Đương nhiên, bạn có thể thêm nhiều cặp phạm vi / tiêu chí nếu cần.

Để xem công thức hoạt động, chúng tôi sẽ sử dụng dữ liệu từ ví dụ trước. Mục đích là để có được cú nhảy tối đa của một vận động viên nữ ở vòng 3:

=SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))

Công thức này được cạnh tranh với một bình thường Đi vào tổ hợp phím và trả về kết quả giống như công thức MAX IF của mảng:

Nhìn kỹ hơn vào ảnh chụp màn hình ở trên, bạn có thể nhận thấy rằng các bước nhảy không hợp lệ được đánh dấu bằng “x” trong các ví dụ trước có 0 giá trị trong các hàng 3, 11 và 15 và phần tiếp theo giải thích lý do.

Công thức này hoạt động như thế nào

Như với công thức MAX IF, chúng tôi đánh giá hai tiêu chí bằng cách so sánh từng giá trị trong các cột Giới tính (B2: B16) và Vòng (C2: C16) với các tiêu chí trong các ô G1 và G2. Kết quả là hai mảng giá trị TRUE và FALSE. Nhân các phần tử của mảng trong cùng một vị trí sẽ chuyển TRUE và FALSE thành 1 và 0 tương ứng, trong đó 1 đại diện cho các mục đáp ứng cả hai tiêu chí. Mảng nhân thứ ba chứa kết quả nhảy xa (D2: D16). Và bởi vì nhân với 0 sẽ cho 0, chỉ các mục có 1 (TRUE) trong các vị trí tương ứng tồn tại:

{0; 0; 0; 0; 0; 4.63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}

Trong trường hợp Phạm vi tối đa chứa bất kỳ giá trị văn bản nào, phép toán nhân trả về lỗi #VALUE do toàn bộ công thức sẽ không hoạt động.

Hàm MAX lấy nó từ đây và trả về số lớn nhất đáp ứng các điều kiện đã chỉ định. Mảng kết quả bao gồm một phần tử duy nhất {4,63} đi đến hàm SUMPRODVEL và nó xuất ra số lượng tối đa trong một ô.

Ghi chú. Do logic cụ thể của nó, công thức hoạt động với các cảnh báo sau:

Phạm vi nơi bạn tìm kiếm giá trị cao nhất phải chỉ chứa các số. Nếu có bất kỳ giá trị văn bản nào, hãy #VALUE! lỗi được trả lại.

Công thức không thể đánh giá điều kiện “không bằng 0” trong tập dữ liệu âm. Đến tìm giá trị tối đa bỏ qua số không, sử dụng công thức MAX IF hoặc hàm MAXIFS.

Công thức Excel MAX IF với logic OR

Để tìm giá trị tối đa khi bất kì trong số các điều kiện đã chỉ định được đáp ứng, hãy sử dụng công thức MAX IF đã quen thuộc với logic Boolean, nhưng thêm các điều kiện thay vì nhân chúng.

Ngoài ra, bạn có thể sử dụng công thức không phải là mảng sau:

Ví dụ: chúng ta hãy tìm ra kết quả tốt nhất trong vòng 2 và 3. Xin lưu ý rằng trong ngôn ngữ Excel, tác vụ được tạo theo cách khác: trả về giá trị tối đa nếu vòng là 2 hoặc 3.

Với các vòng được liệt kê trong B2: B10, kết quả trong C2: C10 và tiêu chí ở F1 và H1, công thức diễn ra như sau:

=MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))

Nhập công thức bằng cách nhấn Ctrl + Shift + Enter tổ hợp phím và bạn sẽ nhận được kết quả này:

Giá trị tối đa với cùng điều kiện cũng có thể được tìm thấy bằng cách sử dụng công thức không phải là mảng này:

=SUMPRODUCT(MAX(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))

Tuy nhiên, chúng ta cần thay thế tất cả các giá trị “x” trong cột C bằng các số 0 trong trường hợp này vì SUMPRODVEL MAX chỉ hoạt động với dữ liệu số:

Những công thức này hoạt động như thế nào

Công thức mảng hoạt động chính xác giống như MAX IF với logic AND ngoại trừ việc bạn tham gia các tiêu chí bằng cách sử dụng thao tác bổ sung thay vì nhân. Trong các công thức mảng, phép cộng hoạt động như toán tử OR:

Việc thêm hai mảng TRUE và FALSE (kết quả của việc kiểm tra các giá trị trong B2: B10 theo các tiêu chí trong F1 và H1) tạo ra một mảng 1 và 0 trong đó 1 đại diện cho các mục mà điều kiện là TRUE và 0 đại diện cho các mục trong đó cả hai điều kiện là FALSE. Kết quả là, hàm IF “giữ” tất cả các mục trong C2: C10 ( value_if_true) với bất kỳ điều kiện nào là TRUE (1); các mục còn lại được thay thế bằng FALSE vì value_if_false đối số không được chỉ định.

Công thức không mảng hoạt động theo cách tương tự. Sự khác biệt là thay vì kiểm tra logic của IF, bạn nhân các phần tử của mảng 1 và 0 với các phần tử của mảng kết quả nhảy xa (C2: C10) ở các vị trí tương ứng. Điều này vô hiệu hóa các mục không đáp ứng bất kỳ điều kiện nào (có 0 trong mảng đầu tiên) và giữ các mục đáp ứng một trong các điều kiện (có 1 trong mảng đầu tiên).

MAXIFS – cách dễ dàng để tìm giá trị cao nhất với các điều kiện

Người dùng Excel 2019 và Excel cho Office 365 không gặp rắc rối trong việc thuần hóa mảng để xây dựng công thức MAX IF của riêng họ. Các phiên bản Excel này cung cấp hàm MAXIFS được chờ đợi từ lâu, giúp tìm giá trị lớn nhất với điều kiện chơi của trẻ.

Trong đối số đầu tiên của MAXIFS, bạn nhập phạm vi cần tìm giá trị tối đa (D2: D16 trong trường hợp của chúng tôi) và trong các đối số tiếp theo, bạn có thể nhập tối đa 126 cặp phạm vi / tiêu chí. Ví dụ:

=MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)

Như được hiển thị trong ảnh chụp màn hình bên dưới, công thức đơn giản này không có vấn đề gì với việc xử lý phạm vi chứa cả giá trị số và văn bản:

Để biết thông tin chi tiết về chức năng này, vui lòng xem Hàm MAXIFS với các ví dụ về công thức.

Bài tập thực hành để tải về

Tìm Giá Trị Lớn Nhất – Nhỏ Nhất Bằng Hàm Max – Min Trong Microsoft Excel

Khi học nhập môn lập trình, gặp bài toán tìm số lớn nhất – nhỏ nhất trong một mảng số bao gồm nhiều số, hàng, cột hoặc cả hàng và cột là mình phải viết nhiều hàm như hàm if..then, so sánh mới tìm ra được.

Nhưng may quá, Excel đã có sẵn hàm MAX để tìm giá trị lớn nhất và tìm giá trị nhỏ nhất với hàm MIN. Ngoài ra, Excel còn 2 hàm MINA và MAXA cũng có chức năng tương tự nhưng có thể tìm được giá trị lớn nhất – nhỏ nhất trong mảng có chứa các giá trị LOGIC (TRUE/ FALSE).

Tìm giá trị lớn nhất bằng hàm MAX

Mô tả:

Là hàm số học, có kết quả trả về là giá trị lớn nhất của các đối số, các Cells hoặc các Range (vùng dữ liệu).

Cú pháp:

=MAX(number 1,number 2,...)

Trong đó:

Number1, number2,... là các số, Cells, Range. Tối đa bạn có thể sử dụng đến number255.

Chú thích:

Nếu đối số là các Cells hay Range tham chiếu, chỉ các số trong các Cells hay Range tham chiếu đó mới được dùng. Các ô trống, giá trị LOGIC hoặc văn bản trong các Cells hay Range tham chiếu bị bỏ qua.

Nếu các đối số không chứa số, hàm MAX trả về 0 (không).

Các đối số là văn bản hay giá trị lỗi không thể chuyển đổi thành số sẽ khiến xảy ra lỗi.

Ví dụ:

HàmMô tả=MAX(29,12,45, 32,25,5,19,39,) Hàm tìm giá trị lớn nhất giữa các đối số là số xác định.=MAX(C10,B3, E6,D3,C6,A6,B10,D10) Hàm tìm giá trị lớn nhất giữa các Cells chứa giá trị số – Excel tự bỏ qua Cells chứa giá trị không phải là số.=MAX(A2:A11) Hàm tìm giá trị lớn nhất của một Range trong 1 cột.=MAX(C7,A2:A11) Hàm tìm giá trị lớn nhất giữa 1 Cells và một Range.=MAX(A2:A11, C2:C11,E2:E11) Hàm tìm giá trị lớn nhất giữa nhiều Range với nhau.=MAX(A:A, C:C,D:D) Hàm tìm giá trị lớn nhất giữa các toàn bộ các cột.=MAX(B2,A3:E3) Hàm tìm giá trị lớn nhất giữa 1 Cells và 1 Range trong 1 hàng.=MAX(A2:E2,A5:E5, A7:E7,A9:E9,A11:E11) Hàm tìm giá trị lớn nhất giữa các Range trong các hàng.=MAX(A2:E11) Hàm tìm giá trị lớn nhất của 1 Range gồm hàng và cột.=MAX(A:E) Hàm tìm giá trị lớn nhất của 1 Range là toàn bộ các cột.

Tìm giá trị nhỏ nhất bằng hàm MIN

Mô tả:

Về cơ bản thì hàm MIN giống với hàm MAX nhưng thay vì hàm MAX tìm giá trị lớn nhất thì hàm MIN sẽ tìm giá trị nhỏ nhất của các đối số, các Cells hoặc Range.

Cú pháp:

=MIN(number 1,number 2,...)

Ví dụ:

★ Thay hàm MAX trong ví dụ trên thành hàm MIN thì kết quả sẽ trả về giá trị nhỏ nhất của các đối số, các Cells hoặc Range.

Mô tả:

Nếu trong các Cells hoặc Range cần tìm giá trị lớn nhất – nhỏ nhất có chứa các giá trị LOGIC như TRUE/ FALSE thì hàm MIN, MAX sẽ bỏ qua các giá trị này trong kết quả, vậy làm sao để tìm được giá trị lớn nhất – nhỏ nhất có chứa giá trị LOGIC?

➤ Câu trả lời: Khi đó bạn có thể dùng hàm MINA, MAXA thay cho hàm MIN, MAX.

Về chức năng và tính chất của hàm MINA, MAXA cũng như hàm MIN, MAX nhưng khi sử dụng hàm MINA, MAXA thì Excel định trị giá trị  FALSE là 0 và TRUE là 1.

Ví dụ:

Cập nhật thông tin chi tiết về Hàm Small Của Excel Để Tìm Và Đánh Dấu Các Giá Trị Thấp Nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!