Công thức chung
=LARGE[IF[range=criteria,values],n]
Mục đích chính của công thức này là tìm ra giá trị lớn thứ n. Đó là giá trị trong cơ sở dữ liệu đáp ứng một tiêu chí cụ thể
Tiến trình
Công thức này sử dụng các hàm LARGE và IF. Đầu tiên, Bạn cần cung cấp phạm vi và một số nguyên thay cho giá trị thứ n. Điều này sẽ lấy giá trị thứ n từ phạm vi. Để đáp ứng các tiêu chí, bạn cần áp dụng công thức IF. Nếu không, chức năng LARGE sẽ được áp dụng cho mọi dữ liệu và nó sẽ không đáp ứng các tiêu chí. Do đó, việc sử dụng hàm IF cung cấp một bài kiểm tra logic để đáp ứng các tiêu chí
Thiết lập dữ liệu
Ví dụ sau chứa cơ sở dữ liệu thông tin sinh viên. Cột A, B và C có tên của học sinh, giới tính và tuổi
Để tìm học sinh lớn tuổi thứ hai, chúng ta cần
- Chuyển đến ô G4.
- Gán công thức ______4_______ cho G4.
- Nhấn CTRL + SHIFT + ENTER để áp dụng công thức cho G4.
Ở đây, tiêu chí là sinh viên phải là nữ. Ô G4 có tuổi của nữ sinh lớn thứ 2 tên là Flora Daisy là 13 .
Excel đã làm việc với các con số rất đơn giản. Ví dụ: chúng ta có thể tìm giá trị lớn thứ n thỏa mãn một điều kiện. Các hàm IF và LARGE giúp chúng ta đạt được điều này mà chúng ta đã học trong bài học này
Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn một ứng dụng đơn giản của công thức hoặc hàm. Nếu bạn muốn tiết kiệm hàng giờ nghiên cứu và giảm bớt sự thất vọng, hãy dùng thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút.
Bạn có thể tính giá trị lớn nhất bằng hàm Max, nhưng để tính giá trị lớn thứ hai hoặc thứ ba, bạn cần sử dụng hàm LARGE. Nếu dữ liệu của bạn không có trùng lặp, vui lòng sử dụng công thức bên dưới
=LỚN[A2. A9, 2]
Bạn có thể thay đổi phạm vi dữ liệu A2. A9 đến phạm vi của riêng bạn. Tuy nhiên, công thức sẽ không hoạt động khi bạn làm việc trên tập dữ liệu bao gồm các bản sao
Thí dụ. Bạn có dữ liệu trong bảng bên dưới với dải dữ liệu từ 2 đến 5 và có hai giá trị lớn nhất là 5
1. Sử dụng Hàm LỚN
Hàm LARGE trong excel có thể trả về một số từ danh sách các số sau khi chúng tôi sắp xếp nó theo thứ tự giảm dần. Chúng ta hãy xem cách áp dụng chức năng này để tìm giá trị lớn thứ hai với tiêu chí
bước
- Đầu tiên, chuyển đến ô F7 và chèn công thức sau
=LARGE[IF[C4:D4=F5,C5:D10],2]
- Bây giờ, nhấn Enter và thao tác này sẽ tính giá Bộ công cụ gia đình lớn thứ hai trong F7
🔎 Công thức hoạt động như thế nào?
- NẾU[C4. D4=F5,C5. D10] . Phần này trả về một mảng các giá trị ô và giá trị ô FALSE.
- =LỚN[NẾU[C4. D4=F5,C5. D10],2]. Phần này của công thức trả về giá trị cuối cùng là 119.
Đọc thêm. Cách sử dụng hàm lớn VBA trong Excel [4 ví dụ phù hợp]
2. Áp dụng chức năng TỔNG HỢP
Hàm AGGREGATE trong excel cho chúng ta khả năng thực hiện các phép tính tổng hợp như COUNT, AVERAGE, MAX, v.v. Hàm này cũng bỏ qua mọi hàng ẩn hoặc lỗi. Chúng tôi sẽ sử dụng chức năng này để tìm giá trị lớn thứ hai với các tiêu chí cụ thể. Thực hiện theo các bước dưới đây để làm điều này
bước
- Để bắt đầu, nhấp đúp vào ô F7 và nhập công thức bên dưới
=AGGREGATE[14,6,[C5:D10]/[C4:D4=F5],2]
- Tiếp theo, nhấn phím Enter và bạn sẽ nhận được giá bộ dụng cụ sân khách lớn thứ hai
Đọc thêm. Cách sử dụng hàm lớn trong Excel với tiêu chí [4 cách phù hợp]
3. Sử dụng chức năng SUMPRODVEL
Hàm TÓM TẮT trong excel trước tiên nhân phạm vi giá trị và sau đó đưa ra tổng của các phép nhân đó. Chúng ta có thể sử dụng hàm này cùng với hàm LARGE để tìm giá trị lớn thứ hai với tiêu chí
bước
- Để bắt đầu phương pháp này, bấm đúp vào ô F7 và chèn công thức bên dưới
=SUMPRODUCT[LARGE[[B5:B10=F5]*[C5:D10],2]]
- Tiếp theo, nhấn phím Enter và do đó, thao tác này sẽ tìm giá trị lớn thứ hai cho bộ quần áo bóng đá Man City bên trong ô C10
🔎 Công thức hoạt động như thế nào?
- [B5. B10=F5]*[C5. D10] . Phần này của công thức trả về một mảng các giá trị cao nhất trong danh sách và các giá trị khác là 0.
- LỚN[[B5. B10=F5]*[C5. D10],2] . Phần này cho giá trị 80 là giá trị lớn thứ hai.
- =TÓM TẮT[LỚN[[B5. B10=F5]*[C5. D10],2]]. Phần này trả về giá trị cuối cùng là 80 trong trường hợp này.
Đọc thêm. Cách tìm giá trị tra cứu lớn nhất trong Excel [3 cách dễ dàng]
4. Sử dụng mã VBA
Nếu bạn đã quen với VBA trong excel, bạn có thể tìm giá trị lớn thứ hai với tiêu chí chỉ bằng vài cú nhấp chuột. Hãy để chúng tôi xem làm thế nào để làm điều này
bước
- Đối với phương pháp này, hãy chuyển đến tab Nhà phát triển và chọn Visual Basic
- Bây giờ, chọn Chèn trong cửa sổ VBA và nhấp vào Mô-đun
- Tiếp theo, gõ công thức bên dưới vào cửa sổ mới
Sub SecondLargest[]
Range["F7"].FormulaArray = "=LARGE[IF[C4:D4=F5,C5:D12],2]"
End Sub- Sau đó, mở macro từ tab Nhà phát triển bằng cách nhấp vào Macro
- Bây giờ, trong cửa sổ Macro, hãy chọn macro Lớn thứ hai và nhấp vào Chạy
- Do đó, mã VBA sẽ tính toán giá trị cao thứ hai từ tất cả các bộ dụng cụ đi bên trong ô F7
Đọc thêm. Cách tra cứu giá trị lớn nhất tiếp theo trong Excel [4 cách dễ dàng]
Cách tìm 5 giá trị và tên hàng đầu với tiêu chí trong Excel
Trong phương pháp này, chúng ta sẽ xem các bước chi tiết để tìm 5 giá trị và tên hàng đầu với các tiêu chí trong excel
bước
- Để bắt đầu, nhấp đúp vào ô E5 và nhập công thức bên dưới
________số 8_______
- Sau đó, nhấn phím Enter và sao chép công thức này vào các ô bằng Fill Handle
- Do đó, điều này sẽ tìm thấy 5 giá trị hàng đầu cho bộ dụng cụ gia đình
🔎 Công thức hoạt động như thế nào?
- ROWS[$E$5. $E5] . Phần này cho giá trị là 1.
- =LỚN[$C$5. $C$10,ROWS[$E$5. $E5]]. Phần này trả về giá trị cuối cùng là 5 mức giá bộ dụng cụ gia đình cao nhất.
Đọc thêm. [Đã sửa. ] Sắp xếp từ lớn nhất đến nhỏ nhất không hoạt động trong Excel
Những điều cần ghi nhớ
- Bạn có thể sử dụng phím tắt ALT+F11 để mở cửa sổ VBA và ALT+F8 để mở cửa sổ Macro
- Lưu ý rằng hàm LARGE bỏ qua các ô trống hoặc chứa các giá trị TRUE hoặc FALSE trong đó
- Nếu không có giá trị số, hàm này có thể trả về lỗi #NUM. Kết quả là lỗi
Phần kết luận
Tôi hy vọng rằng bạn có thể áp dụng các phương pháp mà tôi đã trình bày trong hướng dẫn này về cách tìm giá trị lớn thứ hai với tiêu chí trong excel. Như bạn có thể thấy, có khá nhiều cách để đạt được điều này. Vì vậy, hãy khôn ngoan lựa chọn phương pháp phù hợp với hoàn cảnh của bạn nhất. Nếu bạn gặp khó khăn trong bất kỳ bước nào, tôi khuyên bạn nên xem lại chúng một vài lần để xóa mọi sự nhầm lẫn. Cuối cùng, để tìm hiểu thêm các kỹ thuật excel, hãy theo dõi trang web ExcelDemy của chúng tôi. Nếu bạn có bất kỳ câu hỏi nào, xin vui lòng cho tôi biết trong phần bình luận