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 tháng 11 năm 2023 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 ExcelSMALL 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 2023, Excel 2023, Excel 2013, Excel 2010 và các phiên bản cũ hơn.
Công thức Small cơ bản trong ExcelMộ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 SmallNhữ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ứcVà 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ùngMuố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ấtTrong 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ứcTô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 2023, 2023 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 2023 (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 độngNhư 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ách Sử Dụng Hàm Small Trong Excel Để Tìm Và Đánh Dấu Các Giá Trị Dưới Cùng
Bạn cần tìm một vài số thấp nhất trong một trang tính, ví dụ như tìm 5 sản phẩm có doanh thu thấp nhất. Thật ra, bạn hoàn toàn có thể liệt kê các giá trị thấp nhất trong Excel với tính năng Sort. Nhưng bạn không muốn lãng phí thời gian của mình 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? Nếu vậy, hàm SMALL là một lựa chọn phù hợp. Công thức SMALL sẽ giúp bạn nhanh chóng tìm ra giá trị nhỏ nhất, nhỏ thứ hai, nhỏ thứ ba,… trong chuỗi dữ liệu của mình.
Hàm SMALL của Excel Cú pháp cơ bản của hàm SMALLSMALL 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(array, k)
Trong đó:
Array – 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 SMALL có sẵn trong tất cả các phiên bản Excel dành cho Office 365, Excel 2023, Excel 2023, Excel 2013, Excel 2010 và ở cả các phiên bản cũ hơn.
Để hiểu hơn về cách sử dụng cơ bản của công thức SMALL, bạn hãy quan sát ví dụ đơn giản ngay bên dưới.
Trong danh sách các số ở B2: B10, giả sử bạn muốn trích xuất giá trị nhỏ nhất thứ 3. Hãy nhập 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 – trong hình bên dưới – đã được sắp xếp theo thứ tự tăng dần:
Những điều cần lưu ý khi sử dụng hàm SMALL
Mọi ô trống, giá trị văn bản và giá trị logic TRUE và FALSE trong đối số mảng đều bị bỏ qua.
Nếu mảng chứa một hoặc nhiều lỗi, công thức sẽ trả về error.
Trong trường hợp có các giá trị 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à giá trị nhỏ nhất thứ 2, 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 (array, 1) sẽ trả về giá trị thấp nhất và SMALL (array, n) sẽ chọn giá trị cao nhất.
Những lưu ý 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.
Choáng ngợp bởi quá nhiều điều cần lưu ý cho một công thức đơn giản? Đừng lo lắng, Gitiho có thể giúp bạn trở thành Bậc thầy Excel chỉ trong 16 giờ, với các bài học được sắp xếp hợp lý, hỗ trợ trực tiếp 24/7 từ giảng viên. Giúp bạn nhanh chóng sở hữu bộ kỹ năng tin học văn phòng hữu ích nhất.
Cách sử dụng hàm SMALL trong Excel – ví dụ về công thứcVà 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 để biết thêm các ứng dụng thú vị khác của nó.
Tìm các giá trị thấp thứ 3, 5, 10,… trong mảngNhư 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 hiệu quả nhất để xuất các giá trị thấp đó.
=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 đối số k . Điều quan trọng là bạn phải 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à tương đối cho k .
Bạn không muốn nhập các số n thủ công? Hãy sử dụng hàm ROWS với phạm vi tham chiếu có thể mở rộng để cung cấp giá trị k .Chúng ta tạo tham chiếu tuyệt đối cho ô đầu tiên (hoặc chỉ khóa tọa độ hàng như B$2) và tham chiếu tương đối cho ô cuối cùng:
=SMALL($B$2:$B$10, ROWS(B$2:B2))
Do đó, phạm vi tham chiếu được mở rộng ra khi công thức được sao chép xuống cột. Trong D2, ROWS (B $ 2: B2) tạo ra 1 cho k và công thức trả về giá trị thấp nhất. Trong D3, ROWS (B $ 2: B3) trả kết quả là 2 và chúng ta nhận được giá trị thấp nhất thứ 2,… Chỉ cần sao chép công thức qua 5 ô và bạn sẽ nhận được 5 giá trị thấp nhất của mảng.
Tính tổng N giá trị dưới cùngBạn cầ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à công thức SUM 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 hàm SUMPRODUCT :
SUMPRODUCT(SMALL(array, {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}))
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 ta cung cấp một hằng số mảng là {1,2,3} cho đối số k, buộc nó trả về một mảng có 3 giá trị nhỏ nhất: {29240, 43610, 58860}
Hàm SUMPRODUCT hoặc SUM cộng các số trong mảng và xuất ra tổng.
Hàm SUM sẽ cho kết quả tương tự:
=SUM(SMALL(B2:B10, {1,2,3}))
Lưu ý: Nếu bạn sử dụng tham chiếu ô thay vì hằng số mảng 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 này.
Công thức này hoạt động như thế nào:
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.
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 kết hợp hàm INDEX MATCH cổ điển với SMALL như sau:
= INDEX ( return_array , MATCH (SMALL ( lookup_array , n ), lookup_array , 0))
Trong đó:
Giải pháp này hoạt động tốt cho một tập dữ liệu không có các giá trị trùng lập. Có 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 công thức phức tạp hơn một chút để phá vỡ các ràng buộc này.
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 giúp công thức trở nên đơ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.
Ví dụ, để lấy tên của dự án có chi phí thấp nhất, công thức trong ô E3 là:
=INDEX($A$2:$A$10, MATCH(SMALL($B$2:$B$10, D3), $B$2:$B$10, 0))
Trong đó A2: A10 là phạm vi có tên dự án, B2: B10 là chi phí và D3 là thứ hạng.
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 có chi phí thấp nhất:
Ghi chú:
Sắp xếp các số từ thấp nhất đến cao nhất với công thứcTôi tin rằng mọi người đều biết cách sắp xếp các số theo thứ tự với tính năng Sort trong Excel. 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 mới. Trong Excel 2023, 2023 và các phiên bản trước đó, hàm SORT này không hoạt động, nhưng bạn có thể sử dụng hàm SMALL để giải bài toán này.
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 x 1 trong mỗi hàng nơi công thức được sao chép:
=SMALL($A$2:$A$10, ROWS(A$2:A2))
Nhập công thức vào ô đầu tiên, sau đó kéo công thức xuống các ô có giá trị trong tập dữ liệu gốc (trong ví dụ này là C2: C10). Bạn dùng hàm SMALL để sắp xếp các giá trị tăng dần và hàm LARGE để sắp xếp các giá trị giảm dần.
Công thức SMALL cho ngày và giờ trong ExcelVì ngày và giờ cũng là các giá trị số (trong Excel, ngày được lưu trữ dưới dạng số tuần tự và thời gian dưới dạng phân số thập phân), nê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.
Tìm ngày xa nhất hoặc khoản thời gian ngắn nhấtNhư 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ờ:
=SMALL($B$2:$B$10, D2)
Công thức Small để tìm 3 ngày xa nhất:
Công thức SMALL để nhận được 3 khoản thời gian ngắn nhất:
Tìm một ngày trước đó gần nhất với ngày hôm nay hoặc ngày cụ thể
Ý tưởng chung là đếm số ngày nhỏ hơn ngày mục tiêu bằng COUNTIF.
Sau đó số đếm này chính xác là những gì hàm SMALL cần cho đối số k .
Trong danh sách ngày tháng bên dưới, giả sử bạn muốn tìm ngày gần nhất trước một ngày cụ thể. Bạn có thể sử dụng hàm SMALL kết hợp với COUNTIF .
Nếu ngày 1 tháng 8 năm 2023 (ngày mục tiêu trong E1) xuất hiện trong tập dữ liệu của chúng ta, thì đó sẽ là ngày lớn thứ 7 trong danh sách. Do đó, có sáu ngày nhỏ hơn nó. Có nghĩa là, ngày nhỏ thứ 6 là ngày trước đó gần với ngày mục tiêu nhất. 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): = COUNTIF(B2:B10, “
Sau đó, cắm số đếm vào đối số thứ 2 của hàm SMALL: =SMALL(B2:B10, 6)
Để lấy ngày nhỏ thứ 5, chúng ta lấy kết quả của COUNTIF trừ đi 1,…
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:
=SMALL(B2:B10, COUNTIF(B2:B10, “
Chọn phạm vi mà bạn muốn đánh dấu các giá trị dưới cùng. Trong ví dụ này là B2: B10. Nếu bạn muốn đánh dấu toàn bộ hàng, hãy chọn A2: B10.
Trong hộp thoại New Formatting Rule, chọn Use a formula to determine which cells to format.
Trong hộp Format values where this formula is true, 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 cần đượ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 cần đá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), phạm vi là tham chiếu tuyệt đối ($ B $ 2: $ B $ 10).
Nhấp vào nút Format 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.
Hàm SMALL của bạn không hoạt động?
Mảng trống hoặc không chứa một giá trị số.
Các k giá trị nhỏ hơn không (do lỗi đánh máy) hoặc vượt quá số giá trị trong mảng.
Để trích xuất hai ngày trước ngày trong ô E1, công thức là:
=SMALL(B2:B10, COUNTIF(B2:B10, “
Để tìm một ngày trong quá khứ gần nhất với này hôm nay, hãy sử dụng hàm TODAY cho tiêu chí của COUNTIF:
=SMALL(B2:B10, COUNTIF(B2:B10, “
Để 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ể bọc hàm IFERROR bên ngoài công thức chính.
=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, “
Cách hoạt động của các công thức này như sau:
Để 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:
Cách đánh đấu các giá trị dưới cùng trong ExcelNhư 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! , vì những lý do sau:
Small If Công Thức Để Tìm Giá Trị Thấp Nhất Thứ N
Bạn đang cố gắng tìm cách nhận giá trị dưới cùng dựa trên một hoặc nhiều điều kiện? Chỉ cần sử dụng hàm SMALL cùng với IF. Hướng dẫn này chỉ ra cách xây dựng một giải pháp hoạt động và giải thích logic bên trong của nó, để bạn có thể dễ dàng giải mã công thức và điều chỉnh theo nhu cầu của mình.
Microsoft Excel có một số hàm để thực hiện các phép tính “có điều kiện” như MAXIFS, MINIFS, AVERAGEIF, và những thứ tương tự. Rất tiếc, hàm IF SMALL không tồn tại. Tuy nhiên, không có gì ngăn cản bạn xây dựng công thức của riêng mình để tìm giá trị nhỏ nhất thứ n với tiêu chí. Nếu bạn không quen thuộc với Excel Small chức năng, sau đó bạn có thể muốn bắt đầu với những điều cơ bản và đọc hướng dẫn được liên kết ở trên trước.
Công thức IF Small trong ExcelĐể nhận giá trị thấp nhất thứ n phù hợp với tiêu chí bạn chỉ định, bạn có thể sử dụng công thức chung sau:
Ở đâu n là 1 st, 2 nd, 3 rd, v.v. giá trị thấp nhất để trả lại.
Để công thức hoạt động chính xác, bạn nên nhập nó dưới dạng công thức mảng bằng cách nhấn Ctrl + Shift + Enter các phím đồng thời. Khi bạn làm điều này, Excel sẽ bao quanh công thức trong dấu ngoặc nhọn như được hiển thị trong ảnh chụp màn hình bên dưới. Trong Excel 365, nó cũng hoạt động như một công thức thông thường do hỗ trợ mảng động.
=SMALL(IF($B$2:$B$15="art", $C$2:$C$15), $E3)
Công thức này trả về giá trị nhỏ nhất Nghệ thuật điểm F3. Sao chép nó xuống thông qua F5, và bạn sẽ nhận được 2 nd và 3 rd kết quả thấp nhất.
Để thuận tiện, bạn cũng có thể nhập tên của các đối tượng mục tiêu trong các ô xác định trước (F2 – Nghệ thuật và G2 – Khoa học), và tham chiếu các ô đó làm tiêu chí:
=SMALL(IF($B$2:$B$15=F$2, $C$2:$C$15), $E3)
Nếu thiết kế trang tính của bạn không cung cấp n , bạn có thể tạo chúng trực tiếp trong công thức bằng cách sử dụng hàm ROWS với tham chiếu phạm vi mở rộng như sau:
=SMALL(IF($B$2:$B$15=E$2,$C$2:$C$15), ROWS(A$2:A2))
Do sử dụng khéo léo tham chiếu tuyệt đối và tương đối, tham chiếu phạm vi sẽ tự động mở rộng khi công thức được sao chép vào các ô bên dưới. Trong E3, ROWS (A $ 2: A2) tạo ra n bằng 1 và công thức trả về điểm nhỏ nhất cho Nghệ thuật. Trong E4, tham chiếu thay đổi thành A $ 2: A3 khiến ROWS trả về 2, vì vậy chúng tôi nhận được 2 nd điểm nhỏ nhất, v.v.
Trong trường hợp không tìm thấy giá trị nhỏ nhất thứ n với các tiêu chí được chỉ định, công thức SMALL IF sẽ trả về lỗi #NUM. Để bẫy lỗi này và thay thế nó bằng bất kỳ giá trị nào bạn thấy thích hợp (“-” trong trường hợp của chúng tôi), bạn có thể sử dụng Hàm IFERROR:
=IFERROR(SMALL(IF($B$2:$B$15=$F$2, $C$2:$C$15), $E3), "-")
Từ một mảng cung cấp cho 1 st đối số, hàm SMALL trả về giá trị nhỏ nhất thứ n được chỉ định trong 2 nd tranh luận. Vấn đề là chúng ta không muốn mọi giá trị trong mảng được xử lý mà chỉ xử lý điểm của một môn học nhất định. Để giới hạn mảng ở Nghệ thuật giá trị, chúng tôi nói với Hàm IF để so sánh danh sách (B2: B15) với đối tượng mục tiêu:
Công thức này hoạt động như thế nào:
Bởi vì kiểm tra logic được thực hiện trên một mảng số, kết quả cũng là một mảng, trong đó các số đại diện cho Nghệ thuật điểm và FALSE đánh giá bất kỳ điểm nào khác:
IF($B$2:$B$15="Art", $C$2:$C$15)
Vì hàm SMALL bỏ qua lỗi (và bất kỳ thứ gì khác không phải là số), kết quả là giá trị nhỏ nhất thứ n từ Nghệ thuật điểm trong mảng trên.
{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}
Excel SMALL IF với nhiều tiêu chíĐể tạo công thức IF NHỎ để đánh giá một số điều kiện, hãy chọn một trong các cách tiếp cận sau.
Cung cấp nhiều tiêu chí bằng cách sử dụng câu lệnh IF lồng nhau:
Nhân các biểu thức logic:
Xin lưu ý rằng cả hai đều là công thức mảng, vì vậy đừng quên nhấn Ctrl + Shift + Enter để hoàn thành chúng đúng cách. Trong Excel 365, chúng cũng sẽ hoạt động như các công thức thông thường.
Để kiểm tra công thức “trong trường”, chúng tôi sẽ mở rộng bảng mẫu của mình với Trường học và nhập 2 tiêu chí vào các ô riêng biệt như hình bên dưới.
Bây giờ, chúng ta có tất cả các tham số cần thiết cho công thức NẾU NHỎ:
Criteria_range1 – danh sách các môn học (B2: B15)
Criteria_range2 – các loại trường (C2: C15)
Tiêu chí2 – trường mục tiêu (G2)
Giá trị – điểm (D2: D15)
N – số 1 đến 3 trong F6, F7 và F8
Đặt các đối số lại với nhau, chúng ta nhận được các công thức sau:
=SMALL(IF($B$2:$B$15=$G$1, IF($C$2:$C$15=$G$2, $D$2:$D$15)), F6)
Nhập một trong hai vào G6, kéo nó qua G8 và bạn sẽ nhận được kết quả sau.
=SMALL(IF(($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2), $D$2:$D$15), F6)
Về bản chất, cả hai công thức đều sử dụng hàm IF để kiểm tra nhiều điều kiện, do đó chỉ những giá trị mà tất cả các điều kiện là TRUE mới nhận được vào mảng đối số của hàm SMALL.
Cách hoạt động của các công thức này:
IF lồng nhau:
Trong bài kiểm tra logic của hàm IF đầu tiên, chúng tôi so sánh danh sách các đối tượng với mục tiêu ( Nghệ thuật): $ B $ 2: $ B $ 15 = $ G $ 1. Kết quả của thao tác này là một mảng các giá trị TRUE và FALSE, trong đó TRUE tương ứng với Nghệ thuật giá trị trong cột B:
Câu lệnh IF thứ hai kiểm tra ô nào trong phạm vi C2: C15 khớp với tiêu chí trong G2 ( Trẻ em), và cũng trả về một mảng TRUE và FALSE:
{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
Cho value_if_true đối số của IF thứ hai, chúng tôi cung cấp điểm (D2: D15). Điều này đảm bảo rằng chỉ những mục có TRUE trong hai mảng trên mới “sống sót”; tất cả các điểm khác được thay thế bằng các giá trị FALSE:
{FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE}
Mảng cuối cùng này chuyển đến hàm SMALL, từ đó nó trả về giá trị thấp nhất thứ n.
{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;FALSE;FALSE;125}
Nhân tiêu chí:
Công thức thứ hai kiểm tra cả hai điều kiện trong một phép thử logic duy nhất, trong đó phép toán nhân hoạt động như toán tử AND.
Khi các phần tử của hai mảng TRUE / FALSE được nhân lên, các giá trị logic được chuyển đổi thành 1’s (TRUE) và 0’s (FALSE). Khi nhân với 0 cho không, mảng kết quả có 1 cho dữ liệu đáp ứng cả hai tiêu chí:
Hàm IF đánh giá mảng 1 và 0 này trong bài kiểm tra logic và chuyển điểm tương ứng với 1 thành NHỎ.
{0;1;0;1;0;0;1;1;0;1;0;0;0;1}
Công thức IF SMALL với nhiều tiêu chí OrVí dụ trước cho thấy cách tìm các giá trị dưới cùng dựa trên nhiều tiêu chí bằng cách sử dụng logic AND, tức là khi tất cả các điều kiện được thỏa mãn. Nhận giá trị nhỏ nhất thỏa mãn bất kì trong số các điều kiện, bạn cần xây dựng công thức IF SMALL với logic Or. Đối với điều này, hãy cộng các tiêu chí thay vì nhân chúng.
Ví dụ: hãy chọn điểm thấp nhất trong hai môn học khác nhau, giả sử Lịch sử và Văn chương. Về mặt nếu Excel, công thức sẽ trả về điểm nhỏ nhất thứ n nếu Môn học là một trong hai Lịch sử HOẶC LÀ Văn chương.
Với các môn ở B2: B15 và điểm C2: C15, đây là công thức để trả về điểm thấp nhất:
Tất nhiên, bạn có thể nhập các tiêu chí và n số trong các ô riêng biệt, hoàn thành công thức bằng cách nhấn Ctrl + Shift + Enter và nhận được kết quả này:
Kiểm tra danh sách đối tượng theo 2 tiêu chí khác nhau sẽ cho ra 2 mảng giá trị TRUE và FALSE. Việc thêm các phần tử của các mảng đó sẽ tạo ra mảng 1 và 0, trong đó số 1 tương ứng với các mục đáp ứng ít nhất một điều kiện (bất kể điều kiện nào) và các số không tương ứng với các mục không đáp ứng bất kỳ điều kiện nào. Mảng cuối cùng này đi đến kiểm tra logic của IF:
Công thức này hoạt động như thế nào:
Hàm IF đánh giá từng phần tử của mảng trên và chuyển điểm tương ứng với 1 cho hàm SMALL, từ đó nó chọn giá trị thứ n được chỉ định:
{0;1;0;1;0;0;1;1;0;1;0;1;0;1}
Cách tìm giá trị nhỏ nhất lớn hơn 0Mặc dù tập dữ liệu của bạn đôi khi có thể chứa một số giá trị 0, không phải lúc nào cũng có ý nghĩa khi đưa những số không đó vào kết quả công thức. Để loại bỏ tất cả các giá trị 0, chúng tôi sẽ sử dụng công thức NHỎ NHỎ đã quen thuộc với tiêu chí “không bằng 0”.
{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}
Công thức 1. SMALL IF không bằng 0Để nhận giá trị nhỏ nhất thứ n bỏ qua số 0, hãy sử dụng công thức mảng chung sau:
Giả sử một vài học sinh trượt một số môn thi và bị 0 điểm cho các môn đó. Để có 3 điểm thấp nhất lớn hơn 0, công thức là:
Các bạn nhớ bấm CTRL + Shift + Enter để hoàn thành nó một cách chính xác.
Công thức này đi đến ô trên cùng (E2), trong đó nó sẽ trích xuất điểm thấp nhất. Và sau đó, bạn kéo công thức xuống qua hai ô khác để trích xuất 2 nd và 3 rd điểm thấp nhất. Hàm ROWS (A $ 2: A2) tự động tạo n số, vì vậy bạn không cần nhập chúng vào bất kỳ đâu trong trang tính.
Để tìm giá trị nhỏ nhất thứ n lớn hơn 0 dựa trên tiêu chí, hãy đặt tiêu chí bổ sung theo cách sau:
Kéo công thức sang bên phải và bạn sẽ nhận được công thức nhỏ nhất Khoa học điểm quá:
Lọc các giá trị nhỏ nhất dựa trên tiêu chíLogic của công thức giống như trong các ví dụ trước. Sự khác biệt là bạn sử dụng Chức năng FILTER thay vì IF để áp dụng các tiêu chí.
Giải pháp này chỉ hoạt động trong các bản dựng gần đây của Excel 365, nơi các hàm mảng động có sẵn.
Công thức 1. Tìm giá trị đáy thứ n với một điều kiệnNếu chỉ có một điều kiện được đáp ứng, bạn có thể nhận được giá trị nhỏ nhất thứ n với công thức này:
Đối với tập dữ liệu mẫu của chúng tôi, công thức như sau:
=SMALL(FILTER($C$2:$C$15, $B$2:$B$15=F$2), $E3)
Trong đó B2: B15 là phạm vi tiêu chí (danh sách các môn học), C2: C15 là các giá trị (điểm số), F2 là tiêu chí (chủ đề quan tâm) và E3 là điểm số nhỏ nhất thứ n để trả về.
Công thức 2. Nhận giá trị nhỏ nhất thứ n với nhiều tiêu chíĐể kiểm tra nhiều điều kiện, đây là công thức để sử dụng:
Giả sử bạn đang tìm điểm thấp nhất thứ n trong một môn học cụ thể (G1) trong số các học sinh của một trường nhất định (G2), công thức là:
=SMALL(FILTER($D$2:$D$15, ($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2)), $F6)
Trong đó B2: B15 là danh sách các môn học, C2: C15 là các loại trường, D2: D15 là điểm số và F6 là giá trị nhỏ nhất thứ n để trả về.
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 ExcelTheo 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ườngNế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ấtNế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 ExcelBấ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 ExcelNế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 ExcelNhư 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
Những 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, chúng tôi 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”, “”)
Trong công thức tìm giá trị khác biệt chỉ có một độ lệch nhỏ trong tham chiếu ô thứ hai nhưng lại tạo ra một sự khác biệt lớn:
Tìm hàng duy nhất / khác biệt trong ExcelTheo 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:=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, “Distinct row”, “”)
Tìm các giá trị duy nhất / khác biệt trong Excel phân biệt chữ hoa và chữ thườngNế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ấtNế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 ExcelBấ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:
3. Trong cửa sổ hộp thoại Duplicate Values, chọn Unique trong hộp bên tay trái và chọn định dạng mong muốn trong hộp bên phải, sau đó nhấn OK.
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.
Ảnh chụp màn hình sau minh họa quy tắc làm nổi bật các hàng dựa trên số liệu riêng biệt trong cột A:
Cách làm nổi bật các hàng duy nhất trong ExcelNế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
Làm nổi bật các hàng khác biệt (các hàng duy nhất cùng với 1 lần xuất hiện đại diện cho những hàng trùng lặp) =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1
Cách nhanh và dễ dàng để tìm và đánh dấu các giá trị duy nhất trong ExcelNhư 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.
Trong phần cuối cùng của hướng dẫn hôm nay, chúng tôi sẽ sử dụng tiện ích Duplicate Remover cho Excel. Xin đừng nhầm lẫn bởi tên của công cụ này. Ngoài các hồ sơ trùng lặp, add-in có thể hoàn toàn xử lý các mục duy nhất và khác biệ một cách nhanh chóng. 1. Chọn bất kỳ ô nào trong bảng mà bạn muốn tìm các giá trị duy nhất và nhấn vào nút Duplicate Remover trên tab Ablebits Data trong nhóm Dedupe.
Trình hướng dẫn sẽ chạy và toàn bộ bảng sẽ được chọn tự động. Vì vậy, chỉ cần nhấp vào Next để tiếp tục bước tiếp theo.
Mẹo. Khi sử dụng add-in lần đầu tiên, bạn nên kiểm tra hộp Create a backup copy. 2. Tùy thuộc vào mục tiêu của bạn, hãy chọn một trong các tùy chọn sau, sau đó bấm Next: Unique (Duy nhất) Unique +1st occurrences (distinct) (Duy nhất và khác biệt)
3. Chọn một hoặc nhiều cột mà bạn muốn kiểm tra các giá trị. Trong ví dụ này, chúng ta cần tìm các tên duy nhất dựa trên các giá trị trong 2 cột (First name and Last name), do đó ta sẽ chọn cả hai.
Mẹo. Nếu bảng của bạn có tiêu đề, hãy chọn My table has headers. Và nếu bảng của bạn có thể có ô trống, phải kiểm tra lại phần Skip empty cells. Cả hai tùy chọn đều nằm ở phần trên của cửa sổ hộp thoại và thường được chọn theo mặc định.
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
Copy to another location – Sao chép đến vị trí khác
Nhấn Finish và xem kết quả:
Nếu việc tìm kiếm các giá trị trùng lặp và duy nhất trong Excel là một phần phổ biến trong công việc hàng ngày của bạn thì hy vọng rằng bài viết này có nhiều phần bổ ích, giúp công việc của bạn thuận lợi hơn. Cảm ơn các bạn đã theo dõi bài viết này.
Công Thức Hàm Large If Trong Excel Để Tìm Giá Trị Lớn Nhất
Hướng dẫn cho biết cách sử dụng công thức hàm LARGE IF trong Excel với một hoặc một số tiêu chí.
Trong khi làm việc với dữ liệu số trong Excel, bạn có thể thường muốn tìm các số lớn nhất. Trong một tập dữ liệu lớn, có thể cần phải thu hẹp kết quả bằng cách sử dụng một hoặc nhiều tiêu chí. Tin xấu là hàm LARGEIF không tồn tại trong Excel. Tin tốt là bạn có thể dễ dàng xây dựng công thức LARGE IF của riêng mình 🙂
Công thức LARGE IF cơ bản trong ExcelĐể nhận giá trị lớn nhất thứ n trong tập dữ liệu với điều kiện, bạn có thể sử dụng hàm LARGE và IF cùng nhau:
Trong trường hợp n là 1 st , 2 nd , 3 thứ vv giá trị cao nhất để trở lại.
Xin lưu ý rằng đó là một công thức mảng phải được nhập bằng cách nhấn Ctrl + Shift + Enter các phím đồng thời. Trong Excel 365, do hỗ trợ mảng động , nó có thể được nhập bình thường bằng cách nhấn Đi vào Chìa khóa.
Để xem công thức đang hoạt động, vui lòng xem xét ví dụ sau.
=LARGE(IF($B$2:$B$15=F$2, $C$2:$C$15), $E3)
Trong đó B2: B15 là danh sách các môn học ( tiêu chí_ dải ô), C2: C15 là điểm số ( giá trị ) và E3 là n .
Tất nhiên, bạn có thể mã hóa tên môn học trong công thức (ví dụ: $ B $ 2: $ B $ 15 = “Khoa học”), nhưng việc sử dụng tham chiếu ô làm cho giải pháp linh hoạt hơn.
Công thức trên chuyển đến F3, sau đó bạn kéo nó xuống 2 hàng và 2 cột sang bên phải. Nhờ sử dụng khéo léo các tham chiếu ô tuyệt đối và hỗn hợp , công thức sẽ tự động điều chỉnh khi được sao chép sang các ô khác, cho phép bạn nhận được tất cả kết quả chỉ trong một lần!
Nếu cần, bạn thậm chí có thể làm mà không cần gõ n số trên trang tính. Thay vào đó, hãy tạo chúng trực tiếp trong công thức bằng cách nhúng hàm ROWS với tham chiếu phạm vi mở rộng như sau:
=LARGE(IF($B$2:$B$15=E$2,$C$2:$C$15), ROWS(A$2:A2))
Cơ chế mở rộng phạm vi được giải thích chi tiết trong ví dụ này (công thức Excel để tìm các giá trị hàng đầu 3, 5, 10, v.v.) và ở đây tôi sẽ chỉ hiển thị kết quả:
Trong trường hợp không tìm thấy giá trị lớn nhất cụ thể với điều kiện nhất định, công thức sẽ trả về lỗi #NUM. Để ngăn điều này xảy ra, hãy bọc công thức IF LARGE bên trong hàm IFERROR và thay thế lỗi bằng bất kỳ giá trị nào bạn thấy phù hợp, ví dụ: bằng dấu “-” hoặc “Không tìm thấy”:
Công thức này hoạt động như thế nào:
Vì IF thực hiện kiểm tra logic trên một mảng số, đầu ra cũng là một mảng, trong đó điểm Lịch sử được biểu thị bằng số và tất cả các điểm khác bằng giá trị FALSE:
IF($B$2:$B$15=$F$2, $C$2:$C$15)
Hàm LARGE bỏ qua lỗi trong mảng và tìm giá trị lớn nhất thứ n cụ thể trong số các điểm Lịch sử .
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;247;FALSE;FALSE;235;FALSE;210;FALSE;FALSE}
Excel LARGE IF với nhiều tiêu chíĐể kiểm tra một số điều kiện trong một công thức, hãy sử dụng một trong các phương pháp sau:
Lồng nhiều câu lệnh IF vào nhau:
Nhân một số biểu thức phạm vi = tiêu chí:
Trong các phiên bản Excel trước động, cả hai phải được nhập dưới dạng công thức mảng bằng cách nhấn Ctrl + Shift + Enter. Trong Excel 365, chúng cũng có thể hoạt động như các công thức thông thường.
=LARGE(IF($B$2:$B$15=$G$1, IF($C$2:$C$15=$G$2, $D$2:$D$15)), F6)
Ở đâu:
=LARGE(IF(($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2), $D$2:$D$15), F6)
B2: B15 là các nhóm (c riteria_range1 )
G1 là nhóm mục tiêu (c riteria1 )
C2: C15 là danh sách các môn học (c riteria_range2 )
G1 là đối tượng đích (c nghi thức1 )
D2: D15 là điểm số ( giá trị )
Nhập công thức vào một trong hai công thức trong G6 và sao chép nó qua G8. Kết quả, bạn sẽ đạt 3 điểm Khoa học lớn nhất trong bảng A.
Khi thích hợp, bạn có thể đặt tiêu chí trực tiếp vào công thức như sau:
Nhưng hãy nhớ điều này sẽ yêu cầu cập nhật công thức mỗi khi tiêu chí thay đổi.
=LARGE(IF(($B$2:$B$15="A") * ($C$2:$C$15="Science"), $D$2:$D$15), F6)
Trong các công thức trên, chúng tôi sử dụng câu lệnh IF để kiểm tra hai tiêu chí khác nhau, vì vậy chỉ những giá trị mà cả hai điều kiện đều ĐÚNG mới được đưa vào mảng của hàm LARGE.
Công thức 1 (IF lồng nhau):
Cách hoạt động của các công thức này:
Kiểm tra logic của câu lệnh IF đầu tiên so sánh danh sách các nhóm với nhóm đích: $ B $ 2: $ B $ 15 = $ G $ 1. Kết quả của bài kiểm tra là một mảng các giá trị TRUE và FALSE, trong đó TRUE đại diện cho nhóm A và FALSE bất kỳ nhóm nào khác:
Theo cách tương tự, IF thứ hai so sánh danh sách các môn học với mục tiêu ( Khoa học ) và trả về một mảng TRUE và FALSE khác:
{TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}
Đối với các mục có TRUE trong cả hai mảng, công thức IF lồng nhau trả về điểm số ( value_if_true ). Các mục không đáp ứng tiêu chí được biểu thị bằng các giá trị FALSE:
{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
Mảng cuối cùng này được chuyển cho hàm LARGE, từ đó nó chọn giá trị lớn nhất thứ n.
Công thức 2 (nhân tiêu chí):
{287;FALSE;275;FALSE;FALSE;FALSE;FALSE;FALSE;237;FALSE;FALSE;FALSE;190;FALSE}
Trong công thức này, chúng tôi đánh giá hai tiêu chí khác nhau trong một phép thử logic duy nhất. Phép nhân chuyển đổi các giá trị logic thành 1 (TRUE) và 0 (FALSE). Và bởi vì nhân với 0 luôn cho không, nên mảng kết quả có 1 cho các mục đáp ứng cả hai tiêu chí:
Hàm IF đánh giá mảng 1 và 0 và chuyển điểm tương ứng với 1 thành LARGE.
Công thức IF LARGE với nhiều tiêu chí (HOẶC logic){1;0;1;0;0;0;0;0;1;0;0;0;1;0}
Để công thức IF LARGE hoạt động với logic HOẶC, tức là khi điều kiện này hoặc điều kiện đó là ĐÚNG, hãy tính tổng các biểu thức phạm vi = tiêu chí thay vì nhân chúng:
Với tiêu chí 1 ( Văn học ) ở F1 và tiêu chí 2 ( Lịch sử ) ở F2, công thức như sau:
Trong đó B2: B15 là các môn học, C2: C15 là điểm số và E6 là giá trị lớn nhất thứ n để trả về.
Hoàn thành công thức bằng cách nhấn Ctrl + Shift + Enter các phím với nhau và bạn sẽ nhận được kết quả này:
=LARGE(IF(($B$2:$B$15=$F$1) + ($B$2:$B$15=$F$2), $C$2:$C$15), E6)
Trong công thức mảng, phép toán cộng hoạt động giống như toán tử OR :
Công thức này hoạt động như thế nào:
Hàm IF kiểm tra tất cả các phần tử của mảng trên và chuyển điểm số tương ứng với 1 cho hàm LARGE:
FILTER n giá trị hàng đầu dựa trên tiêu chí{0;1;0;1;0;0;1;1;0;1;0;1;0;1}
Trong Excel 365, có một cách nữa để lấy N giá trị hàng đầu với các điều kiện. Cái hay của cách tiếp cận này là không giống như các công thức mảng CSE truyền thống, các công thức động được hoàn thành theo cách thông thường bằng cách nhấnĐi vàoChìa khóa. Hơn nữa, công thức chỉ cần được nhập vào một ô và tự động điền trên toàn bộ phạm vi tràn .
{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}
Công thức 1. Lọc n giá trị hàng đầu với một điều kiệnĐể tìm các giá trị lớn nhất dựa trên điều kiện, hãy sử dụng công thức chung sau:
Trong đó n là số mục nhập hàng đầu cần trích xuất.
Ví dụ: để đạt được 3 điểm cao nhất trong một môn học nhất định, công thức trong E4 là:
Trong đó C2: C15 là điểm, B2: B15 là đối tượng và F1 là đối tượng quan tâm. Bởi vì công thức chỉ được nhập trong một ô, bạn không cần bận tâm về việc khóa các phạm vi và ô có tham chiếu tuyệt đối.
Đối với đối số mảng của FILTER, cung cấp toàn bộ bảng (A2: C15).
Trong đối số sort_index của SORT, chỉ ra số cột để sắp xếp theo ( cột thứ 3 trong trường hợp của chúng tôi).
Phần bên trái của biểu thức chọn các điểm lớn hơn hoặc bằng điểm cao thứ 3 trong nhóm đối tượng:
Công thức này hoạt động như thế nào:
Tại đây, hàm IF sẽ kiểm tra xem Chủ đề có bằng mục tiêu trong F1 ( Khoa học ) hay không và chuyển điểm tương ứng thành LARGE. Tất cả các điểm khác được thay thế bằng FALSE:
{FALSE;190;FALSE;230;FALSE;237;FALSE;FALSE;252;268;FALSE;275;FALSE;287}
Và hàm FILTER giải quyết thành:
Trong đó 1 tương ứng với điểm lớn hơn hoặc bằng 268 trong Khoa học .
Hàm SORT với đối số s ort_order được đặt thành -1 sắp xếp các điểm số này theo thứ tự giảm dần và đổ kết quả vào E4: E6.
FILTER(C2:C15, {0;0;0;0;0;0;0;0;0;1;0;1;0;1})
Công thức 2. Lọc n giá trị hàng đầu với nhiều tiêu chí ANDĐể lọc các giá trị cao nhất có hai hoặc nhiều điều kiện, bạn cần đánh giá một số biểu thức logic thay vì một:
Ví dụ: để có được 3 điểm cao nhất trong số các sinh viên của một nhóm cụ thể (G1) trong một môn học cụ thể (G2), bạn có thể sử dụng công thức này:
Trong đó B2: B15 là các nhóm, C2: C15 là các đối tượng và D2: D15 là điểm.
Để đánh giá một số tiêu chí bằng cách sử dụng logic hoặc, chúng tôi đã thêm các biểu thức logic trong công thức LARGE IF . Cách tiếp cận này cũng hoạt động cho công thức FILTER:
Trong bảng mẫu của chúng tôi, để lấy 3 điểm cao nhất trong Văn học (F1) hoặc Lịch sử (F2), hãy xây dựng các tiêu chí theo cách này:
Trong đó B2: B15 là các môn học và C2: C15 là điểm số.
Các ví dụ trước cho thấy cách lọc N giá trị hàng đầu với một hoặc nhiều điều kiện. Nếu bạn cần tìm một giá trị cụ thể, chẳng hạn như số cao nhất thứ 2 hoặc thứ 3 trong tập dữ liệu, thì bạn có thể sử dụng công thức LARGE FILTER đơn giản hơn nhiều.
Công thức 1. Tìm giá trị cao nhất thứ n với điều kiệnĐối với một điều kiện, công thức cơ bản này sẽ hoạt động:
Ví dụ, để đạt điểm cao thứ 3 trong môn Văn , hãy sử dụng một trong các công thức sau:
Giải pháp này chỉ hoạt động trong Excel 365. Trong các phiên bản khác, chức năng FILTER không khả dụng.
Trong đó B2: B15 là các đối tượng, C2: C15 là điểm, F1 là đối tượng quan tâm và F2 là điểm lớn nhất thứ n để trả về.
Công thức 2. Lọc giá trị lớn nhất thứ n sử dụng nhiều tiêu chí ANDĐể đánh giá hai hoặc nhiều tiêu chí bằng cách sử dụng logic AND, đây là công thức để sử dụng:
=LARGE(FILTER($C$2:$C$15, $B$2:$B$15="Literature"), 3)
Giả sử bạn đang tìm kiếm điểm cao nhất thứ 3 (G3) trong môn Văn (G2) trong nhóm B (G1). Cung cấp các tham chiếu tương ứng cho công thức:
=LARGE(FILTER($C$2:$C$15, $B$2:$B$15=$F$1), $F$2)
Và bạn sẽ nhận được kết quả này:
Công thức 3. Nhận giá trị lớn nhất thứ n bằng cách sử dụng nhiều tiêu chí ORĐể kiểm tra nhiều tiêu chí bằng logic OR, công thức là:
Nó sẽ kiểm tra xem điều kiện 1 hoặc điều kiện 2 là ĐÚNG và xuất ra kết quả:
=LARGE(FILTER($C$2:$C$15, ($B$2:$B$15=$F$1)+($B$2:$B$15=$F$2)), $F$3)
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!