Bạn đang xem bài viết Cách Tìm Giá Trị Thứ N Trong Excel đượ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.
Để sắp xếp thứ tự tăng dần hay nhỏ dần trên Excel, chúng ta có thể sử dụng công cụ Sort có sẵn. Tuy nhiên cách này chỉ áp dụng với tài liệu có nội dung ngắn, đơn giản mà thôi. Trong trường hợp người dùng phải làm việc với bảng số liệu cần so sánh phức tạp, tìm giá trị lớn nhất, nhỏ nhất, hoặc dùng kết quả để tính toán vào công thức khác thì cần tới cách làm khác.
1. Hướng dẫn tìm mức lương cao thứ N
Bước 1:
Để tìm người có mức lương cao thứ 2 trong bảng trên, trước tiên nhấn vào ô hiện kết quả là ô D9 trong bảng rồi nhấn vào Formulas.
Tiếp đó nhấn vào mục Insert Function, hoặc ấn vào fx như hình bên dưới.
Bước 2:
Xuất hiện giao diện Insert Function để người dùng thiết lập các mục.
Or select a category: Chọn Statistical.
Select a function: Chọn LARGE.
Cuối cùng nhấn Ok để đồng ý.
Array nhập vùng dữ liệu muốn xếp hạng. Ở ví dụ trên là D2:D7.
K để nhập thứ muốn tìm là số 2 với người có mức lương cao thứ 2.
Trong bảng cũng sẽ xuất hiện kết quả xem trước để bạn được biết. Nhấn OK để nhập kết quả vào bảng dữ liệu.
Lưu ý với bạn đọc:
K sẽ là thứ hạng muốn tìm trong danh sách, mặc định thứ hạng lớn nhất sẽ là 1, lớn thứ 2 sẽ là số 2,…
Công thức cho việc tìm kếm giá trị lớn thứ k này là =LARGE(Array,k), với Array là vùng dữ liệu muốn tìm giá trị và k là thứ hạng.
2. Cách tính tổng lương 3 người cao nhất
Chúng ta sẽ sử dụng hàm LARGE 3 lần trong công thức rồi cộng lại. Công thức sẽ là =LARGE(D2:D7,1)+LARGE(D2:D7,2)+LARGE(D2:D7,3) và nhấn Enter.
Kết quả sẽ được tổng lương 3 người cao nhất như hình.
3. Cách tìm lương thấp nhấp trong bảng
Bước 1:
Tại ô nhập kết quả cho lương người thấp nhấn cũng nhấn vào Formulas, chọn Insert Function rồi thực hiện các thiết lập sau đâu.
Or select a category: Chọn Statistical.
Select a function: Chọn SMALL.
Nhấn Ok để thực hiện.
Array chọn vùng dữ liệu là D2:D7.
K nhập thứ hạng muốn tìm kiếm là thấp nhất với số 1.
Ngay lập tức bạn cũng sẽ nhìn thấy kết quả với số tiền lương thấp nhất trong bảng. Nhấn OK để thực hiện.
Như vậy bạn có thể dễ dàng tìm kiếm người có thứ hạng k trong bảng số liệu. Thực chất chúng ta sẽ sử dụng 2 hàm là hàm LARGE để tìm thứ hạng giá trị cao và hàm SMALL để tìm số liệu có thứ hạng thấp. Thứ tự số hạng sẽ dùng chung là 1, 2, 3,… trở đi.
Hàm Large Trong Excel Để Nhận Giá Trị Cao Nhất Thứ N
Bài viết này sẽ hướng dẫn bạn cách sử dụng hàm LARGE trong Excel với nhiều ví dụ về công thức.
Khi phân tích một tập hợp các số, thường có ý nghĩa khi tìm những số lớn nhất. Nhận được giá trị cao nhất thật dễ dàng với Hàm MAX. Khi nói đến việc nhắm mục tiêu một giá trị lớn nhất cụ thể, hãy giả sử 2 nd hoặc 3 rd số lớn nhất trong một tập dữ liệu, hàm LARGE có ích.
Hàm LARGE trong Excel
Hàm LARGE trong Excel được sử dụng để trả về giá trị lớn nhất thứ n từ một tập dữ liệu số. Ví dụ, nó có thể tính điểm cao nhất, 2 nd đơn hàng lớn nhất, 3 rd đặt kết quả, v.v.
Cú pháp bao gồm hai đối số, cả hai đều là bắt buộc:
LARGE (mảng, k)
Ở đâu:
Mảng – một phạm vi hoặc một mảng để tìm kiếm giá trị lớn nhất.
K – vị trí từ giá trị cao nhất đến giá trị lớn nhất thứ k trong tập dữ liệu.
LARGE được phân loại theo Chức năng thống kê. Nó 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.
3 điều cần biết về hàm LARGE
Trước khi chúng ta đi đến những điều thực tế hơn và bắt đầu xây dựng công thức của riêng mình, hãy chú ý đến 3 sự kiện đơn giản giải thích những điều cần thiết:
Hàm LARGE chỉ xử lý các giá trị số. Ô trống, bản vănvà hợp lý giá trị bị bỏ qua.
Nếu mảng chứa bất kỳ sai sót, một lỗi được trả lại.
Trong trường hợp mảng chứa n giá trị, LARGE (mảng, 1) sẽ trả về giá trị lớn nhất và LARGE (mảng, n) sẽ trả về giá trị nhỏ nhất.
Công thức LARGE cơ bản
Ở dạng cơ bản, một công thức LARGE trong Excel rất dễ xây dựng. Đối với đối số thứ nhất, bạn cung cấp một phạm vi giá trị số. Trong 2 nd đối số, bạn xác định vị trí từ lớn nhất đến trở lại.
=LARGE(B2:B10, 2)
Cách sử dụng công thức LARGE trong Excel – ví dụ
Và bây giờ, hãy xem xét các trường hợp sử dụng cụ thể hơn và xem hàm LARGE có thể hữu ích như thế nào.
Cách lấy giá trị N hàng đầu trong Excel
Để nhận các giá trị 3, 5, 10, v.v. lớn nhất bằng một công thức, hãy thực hiện các bước sau:
Nhập các vị trí quan tâm trong các ô riêng biệt. Những con số này sẽ được sử dụng làm k các giá trị.
Lập công thức LARGE áp dụng tuyệt đối tham chiếu phạm vi cho mảng ($ B $ 2: $ B $ 10 trong trường hợp của chúng tôi) và quan hệ tham chiếu ô cho k (D3).
Nhập công thức vào ô trên cùng, sau đó kéo công thức đó vào các ô nhắc nhở. Làm xong!
=LARGE($B$2:$B$10, D3)
Kéo nó qua E4, và bạn sẽ nhận được kết quả này:
Thay vì nhập các vị trí trên trang tính, bạn có thể sử dụng hàm ROWS với mở rộng phạm vi tham chiếu để tạo ra k giá trị tự động như được giải thích trong Công thức Excel để tìm N giá trị hàng đầu trong danh sách.
=LARGE($B$2:$B$10, ROWS(B$2:B2))
Cách tính tổng hoặc trung bình giá trị N lớn nhất
Đến Tổng n giá trị hàng đầu trong tập dữ liệu, bạn có thể sử dụng LARGE cùng với Sumproduct hoặc là Sum theo cách này:
SUMPRODUCT (LARGE ( mảng, {1,…, n }))
Hoặc là
Đến Trung bình cộng cao nhất n giá trị, kết hợp Average và các hàm LARGE:
Để xem nó hoạt động như thế nào trong thực tế, hãy tìm điểm trung bình của 3 điểm hàng đầu trong bảng mẫu của chúng tôi. Đối với điều này, chúng tôi đang sử dụng công thức này:
Để cộng 3 điểm cao nhất, công thức là:
=AVERAGE(LARGE(B2:B10, {1,2,3})) =SUM(LARGE(B2:B10, {1,2,3}))
Ghi chú. Trong trường hợp bạn sử dụng một dải ô thay vì một 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, tất cả các công thức có thể được hoàn thành như bình thường bằng cách nhấn Đi vào Chìa khóa.
Thông thường, hàm LARGE trả về một giá trị duy nhất dựa trên k con số. Trong các công thức này, chúng tôi cung cấp một mảng hằng số như {1,2,3} cho k đối số buộc nó trả về một mảng giá trị. Mảng đó chuyển đến hàm bên ngoài để được tính tổng hoặc tính trung bình.
Nhận dữ liệu được liên kết với giá trị lớn nhất thứ n
Index( return_array, Match (LARGE ( lookup_array, n), lookup_array, 0))
Ở đâu:
Return_array là một phạm vi để trích xuất các kết quả phù hợp.
Lookup_array là một dãy số để xếp hạng từ cao nhất.
N là vị trí của giá trị lớn nhất cần tìm kiếm.
Ví dụ: để lấy tên của một học sinh đã đạt thành tích tốt nhất trong các kỳ thi, hãy nhập công thức này vào F3:
Trong đó A2: A10 là mảng trả về (tên), B2: B10 là mảng tra cứu (điểm số) và D3 là vị trí từ lớn nhất.
Để tìm ra ai có 2 nd và 3 rd điểm cao nhất, sao chép forma sang F4 và F5:
=INDEX($A$2:$A$10, MATCH(LARGE($B$2:$B$10, $D3), $B$2:$B$10, 0))
Giải pháp này hoạt động tốt cho các giá trị duy nhất. Nếu tập dữ liệu của bạn chứa các số trùng lặp, “ràng buộc” trong xếp hạng có thể xảy ra, điều này sẽ tạo ra kết quả không chính xác. Để ngăn điều này xảy ra, hãy sử dụng công thức để xử lý các mối quan hệ.
Trong Excel 365, bạn có thể sử dụng các hàm mảng động để tìm ra một giải pháp đơn giản hơn nhiều có thể 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ọc N giá trị hàng đầu trong Excel.
Công thức LARGE để sắp xếp các số giảm dần
Ghi chú:
Để nhanh chóng sắp xếp danh sách các số trong Excel 365, bạn có thể sử dụng Hàm SORT. Trong Excel 2019, 2016 và các phiên bản trước đó không hỗ trợ mảng động, chúng tôi phải dựa vào hàm LARGE cũ tốt để sắp xếp giảm dần và Small để sắp xếp tăng dần.
Đối với ví dụ này, chúng tôi sẽ sắp xếp các số trong A2: A10 từ cao nhất đến thấp nhất. Để hoàn thành, chúng tôi sẽ cần hàm ROWS với tham chiếu phạm vi mở rộng để tăng k đối số bằng 1 với mọi hàng:
Công thức trên chuyển đến ô trên cùng (C2). Và sau đó, bạn kéo nó qua bao nhiêu ô có số trong danh sách ban đầu (trong trường hợp của chúng tôi là C2: C10):
Như bạn đã biết, ngày và giờ trong Excel là các giá trị số: ngày được lưu trữ dưới dạng số nguyên và lần dưới dạng số thập phân. Điều đó có ý nghĩa gì đối với chúng ta? Hàm LARGE tính toán các giá trị ngày và thời gian theo cách giống hệt với các số. Nói cách khác, công thức bạn đã sử dụng cho các số cũng sẽ hoạt động cho ngày và giờ!
=LARGE($A$2:$A$10, ROWS(A$2:A2))
Giả sử bạn có danh sách các ngày trong B2: B10, công thức sau sẽ trả về N ngày gần đây nhất, tùy thuộc vào số lượng ô bạn sao chép nó vào:
Công thức tương tự cũng có thể tìm thấy dài nhất 3 lần:
=LARGE($B$2:$B$10, ROWS(B$2:B2))
Ví dụ này cho thấy cách sử dụng cụ thể hơn của hàm LARGE trong Excel với ngày tháng.
Từ danh sách các ngày trong B2: B10, giả sử bạn muốn trả lại một ngày trong tương lai gần nhất với ngày hôm nay. Để hoàn thành nhiệm vụ, chúng tôi sẽ sử dụng COUNTIF và TODAY các hàm cùng nhau để tính giá trị cho k đối số của LARGE:
Để tìm ngày tiếp theo trừ một ngày, công thức là:
Hàm LARGE trong Excel không hoạt động
Một công thức LARGE có thể tạo ra lỗi #NUM! lỗi vì những lý do sau:
Công thức này hoạt động như thế nào:
Cung cấp mảng trống hoặc không chứa một giá trị số.
Các k giá trị là một số âm.
Các k giá trị lớn hơn số giá trị trong mảng.
=LARGE($B$2:$B$10, 4)
Cách Sửa Lỗi Excel Không Tìm Ra Giá Trị
Trong một số trường hợp khi sử dụng các hàm VLOOKUP, HLOOKUP hoặc MATCH để tìm kiếm giá trị trong một công thức mảng, nhưng người dùng nhận được thông báo lỗi #N/A. Lỗi này cho biết rằng hàm không thể tìm thấy giá trị trong Lookup array. Bài viết này chúng tôi sẽ hướng dẫn bạn cách sửa lỗi Excel không tìm ra giá trị.
Giả sử nếu bạn tìm thấy giá trị phù hợp hiển thị trong lookup array nhưng Excel vẫn không tìm thấy giá trị? Lúc này bạn cần làm gì để sửa lỗi Excel không tìm ra giá trị?
Trước tiên kiểm tra giá trị giữa các ô mà bạn cho là phù hợp có bằng nhau không.
=B1=E6
Nếu Excel coi các giá trị trong ô B1 và E6 là bằng nhau, công thức trên trả về giá trị là TRUE. Trong trường hợp này, bạn phải kiểm tra xem đã nhập hàm Lookup hoặc hàm Match chính xác hay chưa.
Nếu công thức trả về giá trị FALSE, chứng tỏ giá trị trong ô B1 và E6 là không bằng nhau.
– Nguyên nhân 1:
Có thể là do các ký tự không hiển thị, chẳng hạn như khoảng trống, dấu cách ở đầu hoặc cuối giá trị mà bạn tìm kiếm, hoặc trong các ô của Lookup array. Các ký tự này gây ra lỗi giá trị lookup value và giá trị của lookup array khác nhau.
Giải pháp sửa lỗi Excel không tìm ra giá trị
Trong trường hợp này giải pháp là loại bỏ các ký tự bổ sung từ các ô. Tất nhiên bạn có thể thực hiện điều này bằng tay, nhưng nếu muốn thay đổi nhiều ô, sử dụng hàm TRIM sẽ nhanh hơn rất nhiều:
1. Tạo một cột mới nằm cạnh cột có khoảng trống bổ sung trong đó.
2. Sử dụng hàm Trim trong cột mới để loại bỏ khoảng trống.
(ví dụ nếu dữ liệu gốc nằm trong ô E2, hàm =TRIM (E2) sẽ loại bỏ bất kỳ dấu cách ở đầu hoặc ở cuối dữ liệu).
3. Sao chép hàm Trim xuống toàn bộ cột giá trị.
5. Xóa cột có chứa hàm Trim.
Lưu ý hàm Trim loại bỏ ký tự dấu cách ở đầu và cuối chuỗi văn bản. Tuy nhiên nếu có các ký tự không hiển thị khác, bạn sẽ phải sử dụng hàm Excel Clean để thay thế hàm Trim.
– Nguyên nhân 2:
Nội dung các ô được so sánh có các kiểu định dạng dữ liệu khác nhau. Chẳng hạn ô có chứa giá trị lookup value được lưu trữ dưới định dạng số, trong khi các giá trị lookup array được lưu trữ dưới dạng văn bản.
=ISTEXT(B1)
=ISTEXT(E6)
Hàm ISTEXT trả về giá trị TRUE nếu ô chứa giá trị văn bản hoặc FALSE nếu chứa giá trị khác. Vì vậy nếu 2 công thức trên cho kết quả khác nhau, tức là giá trị của các ô B1 và E6 chứa các kiểu dữ liệu khác nhau.
Giải pháp sửa lỗi Excel không tìm ra giá trị
Buộc cả 2 dữ liệu phải sử dụng cùng một kiểu giá trị. Cho ví dụ nếu muốn cả 2 giá trị được lưu trữ dưới dạng text, bạn có thể chuyển đổi dữ liệu không phải dạng text sang dạng text, sử dụng công cụ Text To Columns của Excel:
1. Sử dụng chuột để chọn các ô mà bạn muốn chuyển đổi sang dạng text (chọn từng cột một).
2. Từ tab Data trên thanh Ribbon Excel, chọn tùy chọn Text to Columns.
Thao tác này để mở hộp thoại Text to Columns.
Dữ liệu trong các ô mà bạn chọn sẽ được lưu trữ dưới dạng text trong Excel và hàm của bạn có thể tìm kiếm được giá trị phù hợp.
Lưu ý: nếu muốn chuyển đổi cả 2 dữ liệu sang dạng số, bạn thực hiện các bước tương tự như ở trên và chọn định dạng dữ liệu General trong công cụ Text To Columns.
Như vậy trong bài viết này chúng tôi vừa hướng dẫn bạn cách sửa lỗi Excel không tìm ra giá trị. Bên cạnh lỗi không tìm ra giá trị thường gặp, bạn cũng có thể gặp phải lỗi font Excel, cách sửa lỗi font Excel cũng khá đơn giản mà chúng tôi đã chỉ ra trước đây giúp bạn có thể tiếp tục làm việc với Excel và thống kê, tính toán một cách chính xác nhất.
Ngoài ra, lỗi Excel không mở được cũng khiến hàng trăm người cảm thấy lo lắng khi mà dữ liệu của bạn tạo ra mất rất nhiều công sức, đừng lo, tham khảo cách sửa lỗi Excel không mở được có trên chúng tôi để tự mình khắc phục lỗi này.
Cách Tìm Giá Trị Gần Đúng Lớn Hơn Hoặc Nhỏ Hơn Trong Excel
Phân tích yêu cầu: từng giá trị của cột 1 (bảng 1) sẽ tìm giá trị gần đúng lớn hơn nhỏ nhất từ cột 2 và sau đó tham chiếu trả về giá trị của cột 3. Vậy để thực hiện, sẽ có 2 vấn đề cần giải quyết như sau:
Tìm giá trị gần đúng lớn hơn nhỏ nhất từ giá trị cột 1 trong cột 2
Từ giá trị có được từ cột 2, tìm ra giá trị tương ứng ở cột 3.
Tìm giá trị gần đúng lớn hơn ( hoặc nhỏ hơn)
Khi tìm giá trị lớn hơn hay nhỏ hơn trong Excel thì sử dụng hàm điều kiện IF để so sánh. Và tìm trong một Range, kết quả trả về có thể là nhiều hơn 1 giá trị. Vì vậy, từ hàm IF này bạn sẽ có là một mảng các giá trị.
Từ mảng trả về, có tiếp 2 trường hợp như sau:
Ngược lại, khi sử dụng so sánh <= giá trị so sánh thì sử dụng hàm MAX để lấy được giá trị gần đúng nhỏ hơn lớn nhất.
Kết hợp phân tích ở trên, mình xây dựng được 1 hàm như sau:
Kết quả hàm IF sẽ là một mảng gồm các giá trị lớn hơn hoặc bằng giá trị trong F12.
Hàm MIN sẽ lấy giá trị nhỏ nhất trong mảng trả về từ hàm IF. Tức là giá trị gần đúng lớn hơn nhỏ nhất.
Lưu ý Vì trong hàm có sử dụng kết quả trả về dạng mảng nên sau mỗi lần chỉnh sửa, bạn cần nhấn tổ phím Shift +Ctrl + Enter . Và cách nhận biết là hàm nằm trong dấu {…}.
Từ giá trị có được từ cột 2, tìm ra giá trị tương ứng ở cột 3
Khi đã có được giá trị gần đúng, bạn có thể sử dụng hàm VLOOKUP để dò tìm giá trị cột 3 theo cột 2.
Lưu ý Bạn thấy, mình đã di chuyển cột 3 ra sau cột 2 là vì VLOOKUP không dò tìm giá trị ngược. Cột kết quả trả về phải nằm sau cột giá trị dò tìm.
Cập nhật thông tin chi tiết về Cách Tìm Giá Trị Thứ N Trong Excel 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!