Công thức Excel tìm các giá trị hàng đầu 3, 5, 10 trong cột hoặc hàng

Với hướng dẫn này, bạn sẽ học cách tìm các giá trị 3, 5, 10 hoặc n hàng đầu trong tập dữ liệu và truy xuất dữ liệu phù hợp.

Bạn muốn xác định giá trị N cao nhất hoặc thấp nhất trong một cột hoặc một hàng? Điều đó nghe có vẻ như là một điều khá dễ dàng để làm. Cần trả lại không chỉ các giá trị mà cả tên của chúng? Ồ, đó có thể là một thách thức. Có một hoặc nhiều tiêu chí cần được đáp ứng. Hmm, điều đó thậm chí có thể ?! Đúng, tất cả điều này có thể được thực hiện với các công thức Excel và hướng dẫn này chỉ ra cách thực hiện.

Công thức Excel để tìm các giá trị hàng đầu 3, 5, 10, v.v.

Để nhận được N giá trị cao nhất trong danh sách, hãy sử dụng hàm LARGE và ROWS cùng nhau theo cách này:

Large (giá trị, ROWS (A $ 2: A2))

Ví dụ, để tìm hàng đầu 3 giá trị trong B2: B12, bạn nhập công thức dưới đây vào ô trên cùng của phạm vi nơi bạn muốn kết quả xuất hiện (D2), sau đó kéo nó qua D4:

=LARGE($B$2:$B$12, ROWS(A$2:A2))

Lên đầu trang 5 giá trị, sao chép cùng một công thức vào 5 ô.

Để tìm 10 giá trị hàng đầu trong một cột, sao chép công thức vào 10 ô.

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

Các Large hàm so sánh tất cả các giá trị số trong một phạm vi được cung cấp cho 1^st tranh luận (Array) và trả về giá trị lớn nhất dựa trên vị trí được chỉ định trong 2^nd tranh luận (k).

Hàm ROWS với tham chiếu phạm vi mở rộng như ROWS (A $ 2: A2) tạo ra k giá trị tự động. Để tạo một tham chiếu như vậy, chúng tôi khóa tọa độ hàng trong ô đầu tiên (A $ 2) và sử dụng tham khảo tương đối cho ô cuối cùng (A2).

Trong ô trên cùng nơi bạn nhập công thức (D2 trong ví dụ này), ROWS (A $ 2: A2) tạo 1 cho k, nói với LARGE để trả về giá trị lớn nhất. Khi được sao chép vào các ô bên dưới, tham chiếu phạm vi sẽ mở rộng thêm 1 hàng khiến k đối số tăng lên 1. Ví dụ: trong D3, tham chiếu thay đổi thành A $ 2: A3. Hàm ROWS đếm số hàng trong A $ 2: A3 và trả về 2 cho k, vì vậy hàm LARGE xuất ra giá trị 2^nd giá trị lớn nhất.

Xin lưu ý rằng chúng tôi đã sử dụng A $ 2: A2 chỉ để thuận tiện vì dữ liệu của chúng tôi bắt đầu ở hàng 2. Bạn có thể sử dụng bất kỳ ký tự cột nào và bất kỳ số hàng nào cho tham chiếu phạm vi mở rộng, giả sử A $ 1: A1 hoặc C $ 1: C1.

Công thức Excel để nhận các giá trị dưới cùng 3, 5, 10, v.v. trong Excel

Để tìm giá trị N thấp nhất trong danh sách, công thức chung là:

Small(giá trị, ROWS (A $ 2: A2))

Trong trường hợp này, chúng tôi sử dụng Small hàm để trích xuất giá trị nhỏ nhất thứ k và hàm ROWS với tham chiếu phạm vi mở rộng để tạo ra k con số.

Ví dụ: để tìm giá trị N dưới cùng trong bảng bên dưới, hãy sử dụng công thức sau:

=SMALL($B$2:$B$12, ROWS(A$2:A2))

Nhập nó vào ô trên cùng, rồi kéo xuống nhiều ô nếu cần.

Công thức Excel để tìm N giá trị hàng đầu trong một hàng

Nếu dữ liệu của bạn được sắp xếp theo chiều ngang theo hàng, thì bạn có thể sử dụng các công thức chung sau để tìm giá trị cao nhất hoặc thấp nhất:

Nhận các giá trị hàng đầu trong một hàng:

Large (giá trị, COLUMNS (A $ 2: A2))

Nhận các giá trị dưới cùng trong một hàng:

Small(giá trị, COLUMNS (A $ 2: A2))

Logic của công thức giống như trong ví dụ trước với sự khác biệt là bạn sử dụng hàm COLUMNS thay vì ROWS để “cấp” k giá trị LỚN và NHỎ.

Giả sử bảng của bạn liệt kê kết quả của 5 vòng cho mỗi người tham gia như thể hiện trong hình dưới đây. Bạn muốn tìm 3 giá trị hàng đầu trong từng hàng.

Để hoàn tất, hãy nhập công thức sau vào ô phía trên bên phải (trong trường hợp của chúng tôi là B10), sau đó kéo công thức xuống và sang bên phải:

=LARGE($B2:$F2, COLUMNS($A1:A1))

Để sao chép công thức một cách chính xác, chúng tôi sử dụng tham khảo hỗn hợp cho mảng đối số LARGE chỉ khóa tọa độ cột ($ B2: $ F2).

Để tìm 3 giá trị dưới cùng trong mỗi hàng, bạn có thể sử dụng công thức NHỎ tương tự:

=SMALL($B$2:$H$2, COLUMNS($A2:A2))

Cách lấy các kết quả phù hợp với N giá trị lớn nhất

Trong trường hợp bạn muốn truy xuất dữ liệu liên quan đến các giá trị hàng đầu, hãy sử dụng Công thức INDEX MATCH cùng với LARGE làm giá trị tra cứu:

Index(return_array, Match (LARGE (lookup_array, k), lookup_array, 0))

Ở đâu:

• Return_array là một phạm vi để trích xuất dữ liệu được liên kết (đối sánh).
• Lookup_array là một phạm vi để tìm kiếm các giá trị lớn nhất.
• K là vị trí có giá trị cao nhất cần tìm.

Trong bảng dưới đây, bạn có thể tìm thấy 3 giá trị hàng đầu bằng cách sử dụng phương pháp sau.

Để trích xuất 3 kết quả hàng đầu, công thức trong E3 là:

=LARGE($B$2:$B$12, D3)

Bởi vì các thứ hạng được nhập trong các ô riêng biệt, nên hàm ROWS không cần thiết trong trường hợp này – chúng tôi chỉ cần tham chiếu ô chứa k giá trị (D3).

Để lấy tên, công thức trong F3 là:

=INDEX($A$2:$A$12, MATCH(LARGE($B$2:$B$12, D3), $B$2:$B$12, 0))

Trong đó A2: A12 là tên (return_array), B2: B12 là kết quả (lookup_array) và D3 là vị trí từ lớn nhất (k).

Theo cách tương tự, bạn có thể nhận được các kết quả phù hợp giá trị N dưới cùng. Đối với điều này, chỉ cần sử dụng chức năng Small thay vì Large.

Để có 3 kết quả dưới cùng, công thức trong E3 là:

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

Để kéo các tên, công thức trong F3 là:

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

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

Hàm LARGE nhận giá trị lớn nhất thứ k và chuyển nó đến lookup_value lập luận của Match.

Ví dụ, trong F3, chúng tôi đang tìm kiếm 1^st giá trị lớn nhất, là 5,57. Vì vậy, sau khi thay thế hàm LARGE bằng đầu ra của nó, công thức giảm thành:

=INDEX($A$2:$A$12, MATCH(5.57, $B$2:$B$12, 0))

Hàm MATCH xác định vị trí tương đối của 5,57 trong B2: B12, là 9. Số này chuyển đến row_num lập luận của Index, đơn giản hóa công thức hơn nữa:

=INDEX($A$2:$A$10, 9)

Cuối cùng, hàm INDEX trả về giá trị từ 9^{thứ tự} hàng trong phạm vi A2: A12, là “Nick”.

Công thức XLOOKUP

Người đăng ký Microsoft 365 có thể đạt được kết quả tương tự bằng cách sử dụng XLOOKUP chức năng:

=XLOOKUP(LARGE($B$2:$B$12, D3), $B$2:$B$12, $A$2:$A$12)

Trong trường hợp này, LARGE trả về số lớn thứ k trực tiếp đến XLOOKUP làm giá trị tra cứu.

So với công thức INDEX MATCH, cú pháp này đơn giản hơn nhiều. Tuy nhiên, hãy nhớ rằng XLOOKUP chỉ khả dụng trong Excel 365. Trong Excel 2019, Excel 2016 và các phiên bản trước đó, công thức này sẽ không hoạt động.

Cách tìm các giá trị hàng đầu có trùng lặp

Cách tiếp cận được sử dụng trong ví dụ trước hoạt động tốt đối với tập dữ liệu chỉ có các số duy nhất trong cột tra cứu. Các bản sao có thể dẫn đến kết quả sai. Ví dụ, nếu 1^st và 2^nd các số lớn nhất giống nhau, hàm LARGE sẽ trả về cùng một giá trị cho mỗi số, điều này được thiết kế mong đợi. Nhưng vì MATCH trả về vị trí của kết quả phù hợp đầu tiên được tìm thấy, công thức sẽ xuất kết quả khớp đầu tiên hai lần như được hiển thị trên hình ảnh bên dưới:

Để “phá vỡ mối quan hệ” và khắc phục sự cố, chúng ta cần một hàm MATCH phức tạp hơn:

=INDEX($A$2:$A$12, MATCH(1, ($B$2:$B$12=LARGE($B$2:$B$12, D2)) * (COUNTIF(F$1:F1, $A$2:$A$12)=0), 0))

Trong tất cả các phiên bản ngoại trừ Excel 365, nó chỉ hoạt động như một công thức mảng. Vì vậy, các bạn nhớ bấm Ctrl + Shift + Enter để hoàn thành công thức một cách chính xác.

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

Ở đây, hàm MATCH được cấu hình để tìm kiếm số 1, là giá trị tra cứu. Mảng tra cứu được xây dựng bằng cách sử dụng logic sau:

Thứ nhất, tất cả các số được so sánh với giá trị được trả về bởi LARGE:

$B$2:$B$12=LARGE($B$2:$B$12, D2)

Thứ hai, COUNTIF với một tham chiếu phạm vi mở rộng kiểm tra xem một mục nhất định đã có trong danh sách hàng đầu hay chưa. Hãy chú ý rằng phạm vi mở rộng bắt đầu ở hàng trên (F1) để tránh tham chiếu vòng tròn.

COUNTIF(F$1:F1, $A$2:$A$12)=0

Kết quả của các phép toán trên là hai mảng giá trị TRUE và FALSE, được nhân với nhau. Ví dụ, trong F3, chúng ta có các mảng sau:

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

Trong mảng đầu tiên, có hai giá trị TRUE tương ứng với 5,57 (hạng 1 và 2) – mục 3 và 9. Nhưng trong mảng thứ hai, mục 3 là FALSE vì tên này (Brian) đã có trong danh sách. Phép toán nhân thay đổi các giá trị logic TRUE và FALSE thành 1 và 0 tương ứng. Và bởi vì nhân với 0 luôn cho không, chỉ có mục 9 “sống sót”:

{0;0;0;0;0;0;0;0;1;0;0}

Hàm MATCH tìm kiếm “1” trong mảng này và trả về vị trí tương đối của nó (9), đó là Nick.

Ghi chú. Giải pháp này ngụ ý rằng cột trả về (Tên trong trường hợp của chúng tôi) chỉ chứa các giá trị duy nhất.

Cách tìm các giá trị hàng đầu trong Excel với các tiêu chí

Để nhận được các giá trị hàng đầu phù hợp với điều kiện nhất định, hãy thể hiện tiêu chí của bạn với sự trợ giúp của Hàm IF và lồng chúng vào bên trong các công thức đã thảo luận trong các phần trước.

Ví dụ, hãy tìm 3 kết quả hàng đầu trong một nhóm nhất định. Để hoàn thành, chúng tôi nhập nhóm mục tiêu vào F1 và nhập các cấp từ 1 đến 3 trong E5: E7 (vui lòng xem hình ảnh bên dưới).

Để giải nen 3 kết quả hàng đầu, nhập công thức dưới đây vào F5 và kéo nó qua F7:

=LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5)

Để đảm bảo rằng hàm LARGE chỉ xử lý kết quả trong nhóm mục tiêu, chúng tôi xây dựng một câu lệnh IF so sánh danh sách các nhóm với F1.

Để có được tên, sao chép công thức này trong G5 đến G7:

=INDEX($A$2:$A$12, MATCH(LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5), IF($B$2:$B$12=$F$1, $C$2:$C$12), 0))

Ở đây, chúng tôi sử dụng tổ hợp INDEX MATCH LARGE đã quen thuộc nhưng mở rộng nó với hai phép thử logic:

LARGE (IF ($ B $ 2: $ B $ 12 = $ F $ 1, $ C $ 2: $ C $ 12), E5) – IF kiểm tra xem một nhóm có khớp với nhóm mục tiêu trong F1 hay không. Các kết quả phù hợp đi vào mảng mà từ đó hàm LARGE chọn giá trị cao nhất dựa trên thứ hạng trong E5. Kết quả trở thành giá trị tra cứu cho MATCH.

IF ($ B $ 2: $ B $ 12 = $ F $ 1, $ C $ 2: $ C $ 12) – IF lọc lại các nhóm không liên quan, vì vậy chỉ những kết quả thuộc nhóm mục tiêu mới được đưa vào mảng tra cứu, nơi MATCH tìm kiếm cho giá trị tra cứu.

đó là công thức mảng cần được nhập bằng cách nhấn Ctrl + Shift + Enter đồng thời. Do khả năng của Excel 365 xử lý các mảng nguyên bản, bạn chỉ cần nhấn phím Enter trong phiên bản này là đủ.

Trong Excel 365, bạn có thể thực hiện các kiểm tra logic tương tự bên trong XLOOKUP và nhận được kết quả giống hệt nhau:

=XLOOKUP(LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5), IF($B$2:$B$12=$F$1, $C$2:$C$12), $A$2:$A$12)

Tiền boa. Để tìm giá trị dưới cùng với tiêu chí, chỉ cần thay thế LARGE bằng SMALL trong các công thức trên.

Cách LỌC các giá trị trên cùng hoặc dưới cùng trong Excel

Trong Excel 365, có một cách đơn giản hơn để tìm giá trị N lớn nhất bằng cách sử dụng các hàm mảng động chẳng hạn như SORT và FILTER.

SORT (FILTER (dữ liệu, con số> = LARGE (con số, n)), sort_index, -1)

Ở đâu:

• Dữ liệu là bảng nguồn không bao gồm tiêu đề cột.
• Số là các giá trị số để xếp hạng.
• N là số mục hàng đầu để giải nén.
• Sort_index số lượng của cột để sắp xếp.

Ví dụ: để lọc 3 bản ghi hàng đầu trong tập dữ liệu của chúng tôi, công thức sẽ như sau:

=SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, 3)), 2, -1)

Trong trường hợp này, chúng tôi đặt n thành 3 vì chúng tôi đang trích xuất 3 kết quả hàng đầu và sort_index thành 2 vì các số ở cột thứ hai.

Cái hay của công thức này là bạn chỉ cần nhập nó vào một ô và Excel sẽ tự động điền kết quả vào nhiều ô nếu cần (khái niệm này được gọi là phạm vi tràn).

Để trích xuất 3 kết quả thấp nhất, công thức là:

=SORT(FILTER(A2:B12, B2:B12<=SMALL(B2:B12, 3)), 2, 1)

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

Ở đây, chúng tôi sử dụng BỘ LỌC chức năng lọc dữ liệu nguồn dựa trên các tiêu chí bao gồm trong 2^nd tranh luận.

Để nhận các giá trị hàng đầu, chúng ta xây dựng biểu thức logic để kiểm tra xem một số nhất định có lớn hơn hoặc bằng số cao thứ 3 trong danh sách hay không: B2: B12> = LARGE (B2: B12, 3).

Để nhận các giá trị dưới cùng, chúng tôi kiểm tra xem một số có nhỏ hơn hoặc bằng số thấp thứ 3 hay không: B2: B12 <= SMALL (B2: B12, 3).

Kết quả của kiểm tra logic là một mảng các giá trị TRUE và FALSE, được sử dụng để lọc – chỉ các mục nhập tương ứng với TRUE mới được đưa vào mảng cuối cùng:

=SORT({"Aiden",5.51;"Brian",5.57;"Nick",5.57}, 2, -1)

Như bạn có thể thấy ở trên, hàm FILTER xuất các mục theo thứ tự xuất hiện trong phạm vi nguồn. Để sắp xếp kết quả, chúng tôi đặt công thức FILTER bên trong SẮP XẾP chức năng và thiết lập thứ tự sắp xếp đối số -1 (giảm dần) hoặc 1 (tăng dần).

Đó là cách tìm các giá trị hàng đầu trong Excel. Dịch vụ kế toán tại Hà Nội – KetoanMVB cảm ơn bạn đã đọc và mong được gặp bạn trên blog của chúng tôi vào tuần tới!

Source link

The post Công thức Excel tìm các giá trị hàng đầu 3, 5, 10 trong cột hoặc hàng appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.

source https://ketoanmvb.com/cong-thuc-excel-tim-cac-gia-tri-hang-dau.html