Xử lý bài toán: lookup không tìm thấy giá trị đúng sẽ lấy giá trị trước đó

[incantesimo]

Chào cả nhà, hiện tại mình đang gặp phải 1 vấn đề như sau :
Giả sử có bảng lãi suất (đính kèm file phái dưới) bao gồm dòng ngày áp dụng lãi suất ( ví dụ ngày 15/8/09, ngày 09/10/09, ngày 22/10/09,...) và cột kỳ hạn gửi ( ví dụ: 1 tháng. 2 tháng . ... 12 tháng). Bài toán đặt ra là nếu KH gửi ngày 21/8/09 và kỳ hạn là 5 tháng thì áp dụng lãi suất nào trong bảng trên biết rằng nếu ngày gửi không có trong bảng thì áp dụng của ngày trước đó (trong trường hợp này là ta phải lấy lãi suất của ngày 15/8).
Mình đã dùng hàm các Hlookup và Vlookup hoặc Index và match phối hợp với IF để dò tìm nhưng ra kết quả N/A. Mình chưa biết có hàm nào trong excel dò tìm trong khoảng nếu ra giá trị N/A thì sẽ tiếp tục lấy giá trị theo điều kiện không các bạn?
Mong nhận được giải pháp của các bạn.

 

[duongdv]

Tôi sử dụng hàm Index() kết hợp với hàm Match().
Bạn xem trong file đính kèm. Xem đã đúng ý bạn chưa nhé

 

[incantesimo]

Cám ơn bạn duongdv rất nhiều,
Bạn cho mình hỏi luôn tại sao trong công thức này của bạn: =INDEX($B$1:$D$21,MATCH(F10,KH,0),MATCH(G10,$B$1:$D$1,1)) , 2 hàm match dùng cách dò khác nhau(0,1) nó ý nghĩa như thế nào vậy bạn? Lúc mình làm công thức index và match thì mình chỉ sử dụng cách dò là 0 tức là False mang ý nghĩa tìm trị đầu tiên trong danh sách và không xếp theo thứ tự. Vậy còn giá trị 1 là True nó ý nghĩa xếp theo thứ tự phải không bạn? Trong công thức trên giá trị nào quyết định cho việc lấy lãi suất của ngày 15/8 sau khi dò không thấy lãi suất ngày 21/8.
Câu hỏi ngòai lề là ngoài 2 cách dò bằng 0,1 hàm match còn giá trị nào không. Mình tham khảo tài liệu thường thấy có 0,1 thôi
Thanks!

 

[duongdv]

Cám ơn bạn duongdv rất nhiều,
Bạn cho mình hỏi luôn tại sao trong công thức này của bạn: =INDEX($B$1:$D$21,MATCH(F10,KH,0),MATCH(G10,$B$1:$D$1,1)) , 2 hàm match dùng cách dò khác nhau(0,1) nó ý nghĩa như thế nào vậy bạn? Lúc mình làm công thức index và match thì mình chỉ sử dụng cách dò là 0 tức là False mang ý nghĩa tìm trị đầu tiên trong danh sách và không xếp theo thứ tự. Vậy còn giá trị 1 là True nó ý nghĩa xếp theo thứ tự phải không bạn? Trong công thức trên giá trị nào quyết định cho việc lấy lãi suất của ngày 15/8 sau khi dò không thấy lãi suất ngày 21/8.
Câu hỏi ngòai lề là ngoài 2 cách dò bằng 0,1 hàm match còn giá trị nào không. Mình tham khảo tài liệu thường thấy có 0,1 thôi
Thanks!

Bạn xem lại cấu trúc của hàm Match() là hiểu ngay.

Hàm MATCH

Chức năng: Hàm Match là hàm dò tìm, sẽ trả về vị trí của giá trị dò tìm trong vùng dò tìm (một dòng hoặc một cột).

Cú pháp: MATCH(lookup_value,lookup_array,match_type)

- Lookup_value: là giá trị dùng để dò tìm, lookup_value có thể là một số, một chuỗi, một giá trị logic hay một tham chiếu.

- Lookup_array: là vùng dò tìm, có thể là một cột hoặc một dòng, hoặc một mảng các giá trị.

- Match_type: là tùy chọn để xác định kiểu dò tìm. Có 3 tùy chọn là -1,0,1:

• match_type = 0: Hàm sẽ dò tìm chính xác giá trị lookup_value trong lookup_array, nếu không tìm thấy hàm sẽ báo lỗi #N/A.
• match_type = 1(hoặc để trống): Hàm sẽ dò tìm giá trị lớn nhất trong lookup_array mà có giá trị <= giá trị của lookup_value. Trường hợp này, các phấn tử trong lookup_array phải được sắp xếp từ nhỏ đến lớn (..-2,-1,0,1,2…A,B,C..Z…False, True).
• match_type = -1: Hàm sẽ dò tìm giá trị nhỏ nhất trong lookup_array mà có giá trị >= giá trị của lookup_value. Trường hợp này, các phấn tử trong lookup_array phải được sắp xếp từ lớn đến nhỏ ( True, False, Z…A,..2,1,0,-1,-2..).

Lưu ý: Nếu match_type = 0, lookup_value là một chuỗi ký tự thì bạn có thể dùng ký tự * hoặc ? Để đại diện cho một vài ký tự trong chuỗi dò tìm lookup_value ( * đại diện cho một vài ký tự, ? Đại diện cho 1 ký tự).

Lưu ý: Để cảm ơn bài viết của ai bạn chỉ cần nhấn vào nút Cảm ơn là được

 

[CDung_K47]

Các bạn cho mình hỏi: Có cách nào để cấu trúc hàm sẽ trả về giá trị là 0 khi nó dò không thấy giá trị đã định (giá trị đó không có trong vùng mà ta muốn dò).

 

[dongducnhiem]

Các bạn cho mình hỏi: Có cách nào để cấu trúc hàm sẽ trả về giá trị là 0 khi nó dò không thấy giá trị đã định (giá trị đó không có trong vùng mà ta muốn dò).

Bạn dùng ISNA() phù hợp cho Ex 2003-2010, ví dụ đang dùng VLOOKUP
=IF(ISNA(VLOOKUP()),0,VLOOKUP())
------------
2007 trở lên dùng =IFERROR()

 

[CDung_K47]

Cám ơn bạn nhé, mình đang bị vướng chỗ này: giá trị ô = index(...) + index(...)+....Nếu những giá trị index đều dò được thì giá trị ô sẽ là tổng những giá trị đó, nhưng vấn đề là nếu 1 trong các số hạng index đó dò không thấy nó sẽ trả về #N/A, khi đó giá trị ô của mình cũng biến thành #N/A luôn. Vậy có cách nào để nếu dò không ra thì cho giá trị index(...) đó bằng 0 không? hoặc bỏ qua số hạng đó và vẫn cộng những giá trị đã tìm được?

 

[dongducnhiem]

Cám ơn bạn nhé, mình đang bị vướng chỗ này: giá trị ô = index(...) + index(...)+....Nếu những giá trị index đều dò được thì giá trị ô sẽ là tổng những giá trị đó, nhưng vấn đề là nếu 1 trong các số hạng index đó dò không thấy nó sẽ trả về #N/A, khi đó giá trị ô của mình cũng biến thành #N/A luôn. Vậy có cách nào để nếu dò không ra thì cho giá trị index(...) đó bằng 0 không? hoặc bỏ qua số hạng đó và vẫn cộng những giá trị đã tìm được?

Thì giống như trên thay cụm Vlookup() thành cụm Index() ... hay cụm nào đó bị báo lỗi là #N/A

 

[CDung_K47]

Mình làm được rồi, cám ơn bạn nhiều nhé!

 

[bebo021999]

Cám ơn bạn nhé, mình đang bị vướng chỗ này: giá trị ô = index(...) + index(...)+....Nếu những giá trị index đều dò được thì giá trị ô sẽ là tổng những giá trị đó, nhưng vấn đề là nếu 1 trong các số hạng index đó dò không thấy nó sẽ trả về #N/A, khi đó giá trị ô của mình cũng biến thành #N/A luôn. Vậy có cách nào để nếu dò không ra thì cho giá trị index(...) đó bằng 0 không? hoặc bỏ qua số hạng đó và vẫn cộng những giá trị đã tìm được?

Một cách khác:
=NOT(ISERROR(INDEX(A)))*INDEX(A)+NOT(ISERROR(INDEX(B))*INDEX(B))
Tức là nếu INDEX(A) lỗi, ISERROR() trả về 1, NOT() trả về 0, lúc đó 0*INDEX(A) sẽ bằng 0