Đang chưa có ý tưởng viết bài thì thằng bạn nhắn tin và nhờ mình giúp tạo hàm Excel với yêu cầu: Tìm giá trị gần đúng lớn hơn nhỏ nhất trong một Range và dựa theo kết quả tìm tiếp giá trị tương ứng. Bạn xem qua hình sau để thấy rõ về yêu cầu:
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:
- Dấu so sánh trong hàm IF, bạn để là >= giá trị so sánh. Thì để lấy được giá trị gần đúng lơn hơn nhỏ nhất thì bạn cần sử dụng hàm MIN.
- Ngược lại, khi sử dụng so sánh =F12;K$12:K$30]]:
- 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.
Như vậy, với yêu cầu bài toán thì mình đã sử dụng 3 hàm [IF, MIN, VLOOKUP] để thực hiện. Kết quả phản hồi từ thằng bạn là đúng. Và mình hy vọng bài viết này cũng giúp bạn giải quyết được vấn đề tương tự đang gặp phải.
Chúc bạn vui và thành công!
Trong trường hợp như file đính kèm thì dùng công thức nào để dò tìm giá trị gần nhất vối giá trị cho trước trong A1;
Xin cảm ơn các ACE trả lời câu hỏi!
Thử công thức này xem sao:Trong trường hợp như file đính kèm thì dùng công thức nào để dò tìm giá trị gần nhất vối giá trị cho trước trong A1;
Xin cảm ơn các ACE trả lời câu hỏi!
=INDEX[$G$2:$G$17,MATCH[A1,$G$2:$G$17,1]+[A1>=[INDEX[$G$2:$G$17,MATCH[A1,$G$2:$G$17,1]+1]+INDEX[$G$2:$G$17,MATCH[A1,$G$2:$G$17,1]]]/2]]
Hy vọng là có công thức khác ngắn gọn hơn, nhìn công thức dài ngoằng mà phát ớn!
Quy luật để tìm gần nhất của bạn? 30= trung bình cộng của 2 giá trị đó thì lấy giá trị lớn & ngược lại Mình tham gia một công thức:Trong trường hợp như file đính kèm thì dùng công thức nào để dò tìm giá trị gần nhất vối giá trị cho trước trong A1;
Xin cảm ơn các ACE trả lời câu hỏi!
Cảm ơn các thay gianh thoi gian chia se vấn đề của mình do bận quá không theo doi het các comment. hôm nay mới quay lại thay đông vui quá. Minh đang sử dụng công thức của bac Nghiaphuc đáp ứng được ứng dụng của minh. nhưng bị vấn đề khi số tìm là biên trên trên 250 thì ko tìm được 250. và muốn yêu cầu thêm khi số tìm là ngoài range thì lấy giá trị tại biên đấy. vd: 260 trả về 250 hoặc -80 trả về -75=OFFSET[$G$1,IF[$A$1=[INDEX[$G$2:$G$17,MATCH[A1,$G$2:$G$17,1]+1]+INDEX[$G$2:$G$17,MATCH[A1,$G$2:$G$17,1]]]/2]]
Hy vọng là có công thức khác ngắn gọn hơn, nhìn công thức dài ngoằng mà phát ớn!Xin cảm ơn!
Video liên quan