Tìm giá trị lớn hơn gần nhất trong Excel
Show
Bài toán dưới đây sử dụng cách dò tìm gần đúng trong Excel khi bạn cần thực hiện dò giá trị nhưng giá trị ở bảng dò và giá trị dò không hoàn toàn giống nhau. Ở đây sử dụng 2 cách: 1 là sử dụng công thức mảng, 2 là sử dụng hàm Lookup kết hợp với hàm Len và SUBSTITUTE
Ví dụ chúng ta có danh sách các số tiêu chuẩn ở cột A, từ A2:A6. Trong danh sách các sốố này được sắp xếp theo thứ tự tăng dần. Xác định số nhỏ hơn gần nhất với giá trị số ở ô C3. Để thực hiện yêu cầu này, chúng ta có thể sử dụng 2 hàm là LOOKUP và VLOOKUP. Cách làm cụ thể như sau: Hàm LOOKUP là 1 hàm tìm kiếm cơ bản trong Excel, trong đó khi bảng dữ liệu chứa các giá trị cần tìm được sắp xếp theo thứ tự tăng dần thì chúng ta có thể tìm được số nhỏ hơn gần nhất với số cần tìm. Tại ô D3 chúng ta dùng công thức như sau: =LOOKUP(C3,$A$2:$A$6) Trong đó:
Kết quả của hàm là 200, là số nhỏ hơn 230 và gần nhất với số 230. Hàm VLOOKUP cũng là 1 hàm rất hay sử dụng trong Excel. Trong trường hợp này chúng ta có thể sử dụng hàm VLOOKUP như sau: E3=VLOOKUP(C3,$A$2:$A$6,1,1)
Khi sử dụng hàm VLOOKUP với range_lookup = 1 (hay TRUE) thì hàm sẽ tìm kiếm theo phương pháp tương đối, kết quả trả về là giá trị nhỏ hơn gần nhất với số cần tìm. Chỉ thực hiện được điều này khi:
Hai trường hợp trên chúng ta đều thấy danh sách Tiêu chuẩn cần phải được sắp xếp theo thứ tự tăng dần thì mới làm được. Vậy nếu như danh sách này không được sắp xếp thì sao? Để giải quyết yêu cầu này, chúng ta sử dụng 2 hàm LARGE và COUNTIF kết hợp với nhau. Để tìm được số nhỏ hơn gần nhất, chúng ta sẽ nghĩ theo hướng:
Khi đó ta có thể sử dụng hàm COUNTIF như sau:
Ý nghĩa của hàm này là:
Trong ví dụ trên, ta thấy kết quả bằng 2, tức là:
=> Để xác định cụ thể đó là số nào, chúng ta dùng thêm hàm LARGE Hàm Large là hàm giúp tìm số lớn thứ mấy ở trong 1 dãy số. Trong hàm COUNTIF ở trên, chúng ta đã xác định được số nhỏ hơn gần nhất nằm ở vị trí số lớn thứ mấy trong danh sách. Vì vậy sẽ rất đơn giản để áp dụng với hàm LARGE. Cách làm như sau:
Kết quả như sau: Xét ví dụ số khác: Rất thú vị phải không nào. Ngoài ra các bạn có thể tìm hiểu thêm một số bài viết có chủ đề tương tự: Hướng dẫn cách tìm giá trị lớn hơn gần nhất với giá trị so sánh Cách sử dụng hàm vlookup kết hợp hàm Match khi tìm theo nhiều cột chứa kết quả Hàm tìm kiếm VLOOKUP và HLOOKUP trong Excel Hướng dẫn 4 cách tìm giá trị lớn nhất và nhỏ nhất trong excel Cách tra cứu ngày lớn nhất hay giá trị thỏa mãn nhiều điều kiện Tìm giá trị gần đúng nhất bằng VBA
zik.vn – Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm Với sứ mệnh: “Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển zik.vn đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án… Zik.vn tự hào khi được đồng hành cùng:
Tìm hiểu ngay các khóa học của Zik.vn TẠI ĐÂY
Hi all
Cả nhà ai biết cách tìm một giá trị bằng hoặc lớn hơn gần nhất giá trị so sánh để chọn không, vì trong tính toán, phần nhiều để lựa chọn an toàn thì thường thiên về giá trị lớn hơn trong bảng so sánh tính toán. Vì vậy mới cần tìm giá trị trong bảng, có trị số bằng hoặc lớn hơn gần nhất với giá trị đem so sánh!
PS: Vlookup và Hlookup chỉ tìm giá trị nhỏ hơn gần nhất giá trị so sánh thôi! Lần chỉnh sửa cuối: 24/2/12
Hi all
Cả nhà ai biết cách tìm một giá trị bằng hoặc lớn hơn gần nhất giá trị so sánh để chọn không, vì trong tính toán, phần nhiều để lựa chọn an toàn thì thường thiên về giá trị lớn hơn trong bảng so sánh tính toán. Vì vậy mới cần tìm giá trị trong bảng, có trị số bằng hoặc lớn hơn gần nhất với giá trị đem so sánh! PS: Vlookup và Hlookup chỉ tìm giá trị nhỏ hơn gần nhất giá trị so sánh thôi! Vì dữ liệu đang được xếp tăng dần nên có thể dùng công thức mảng thế này
=INDEX($C$4:$W$4,,MATCH(TRUE,($C$5:$W$5>=$C$9),0))
Sao vẫn báo lỗi N/A vậy nhỉ?, bác xem giùm lại e chút!?
Kết thúc bằng Ctrl-Shift-Enter bạn nhé
Vì dữ liệu đang được xếp tăng dần nên có thể dùng công thức mảng thế này =INDEX($C$4:$W$4,,MATCH(TRUE,($C$5:$W$5>=$C$9),0))
=OFFSET($B$4,,MATCH(C9,$B$5:$W$5,1),,)
Nhờ các bác giúp trường hợp này với, em mày mò mãi mà không ra.
Bài này về topic hơi giống với topic của chủ thớt khác cái là các giá trị chạy không liên tục nên không áp dụng được.
Đề bài là so sánh một số cho sẵn cụ thể trường hợp này là ngày với một chuỗi các ngày và chọn giá trị lớn hơn gần nhất.
Cảm ơn các bác nhiều!
công thức mảng
=MIN(IF(B32:AE32>B149,B32:AE32))
nhấn Ctrl+Shift+Enter
Nhờ công thức của bác HieuCD em đã giải quyết được bài toán trước, tuy nhiên vấn đề mới đặt ra là:
Có nhiều tuyến giao hàng, mỗi tuyễn có lịch giao khác nhau, nên phải căn cứ vào tuyến giao và ngày kho ra đơn để tính xem ngày lớn hơn gần nhất theo lịch. Mong các bác chỉ giáo. Chi tiết em gửi trong file đính kèm.
Em cảm ơn!
Nhờ công thức của bác HieuCD em đã giải quyết được bài toán trước, tuy nhiên vấn đề mới đặt ra là:
Có nhiều tuyến giao hàng, mỗi tuyễn có lịch giao khác nhau, nên phải căn cứ vào tuyến giao và ngày kho ra đơn để tính xem ngày lớn hơn gần nhất theo lịch. Mong các bác chỉ giáo. Chi tiết em gửi trong file đính kèm.
Em cảm ơn! nhấn Ctrl+Shift+Enter
C151 =MIN(IF(OFFSET($B$2:$AE$2,MATCH(A151,$A$3:$A$147,0),)>B151,OFFSET($B$2:$AE$2,MATCH(A151,$A$3:$A$147,0),))) Một lần nữa cảm ơn bác HieuCD rất nhiều, em đã làm được rồi! |