Xin giúp hàm Vlookup và If (1 người xem)

[vnproc]

Mình có trường hợp tính định mức cho các xe. Mình kết hợp hàm vookup và if nhưng sao vẫn bị báo lổi NAME.
Mong các bạn giúp. Cảm ơn rất nhiều.

 

[Hoàng Trọng Nghĩa]

Mình có trường hợp tính định mức cho các xe. Mình kết hợp hàm vookup và if nhưng sao vẫn bị báo lổi NAME.
Mong các bạn giúp. Cảm ơn rất nhiều.

Lỗi Name là vì:

=VOOKUP(TRIM(A2),$N$2:$Q$10,IF(AND(D2="KHOC",E2="KHOB"),4),0)

Thay vì:

=VLOOKUP(TRIM(A2),$N$2:$Q$10,IF(AND(D2="KHOC",E2="KHOB"),4),0)

 

[ndu96081631]

Mình có trường hợp tính định mức cho các xe. Mình kết hợp hàm vookup và if nhưng sao vẫn bị báo lổi NAME.
Mong các bạn giúp. Cảm ơn rất nhiều.

Báo lỗi NAME thì bạn phải nghĩ ngay đến việc mình GÕ SAI TÊN
trong Excel có hàm nào tên là VOOKUP không bạn ?
Mà dù sửa lại cho đúng thì công thức của bạn cũng sai luôn.. vì đây là bài toán dò tìm nhiều điều kiện, không thể VLOOKUP được. Phải vầy:

=LOOKUP(2,1/($N$2:$N$10=A2)/($O$2:$O$10=D2)/($P$2:$P$10=E2),$Q$2:$Q$10)

 

[Hoàng Trọng Nghĩa]

Tôi lại có một thắc mắc cho bạn:

Với hàm IF trong công thức của bạn:

IF(AND(D2="KHOC",E2="KHOB"),4)

Nếu điều kiện đúng, thì số cột dò tìm sẽ là 4, còn điều kiện sai, nó sẽ cho ra số cột nào?

Giả sử nó ra kết quả là FALSE (tương đương với 0), vậy chắc chắn hàm sẽ bị lỗi.

 

[vnproc]

Báo lỗi NAME thì bạn phải nghĩ ngay đến việc mình GÕ SAI TÊN
trong Excel có hàm nào tên là VOOKUP không bạn ?
Mà dù sửa lại cho đúng thì công thức của bạn cũng sai luôn.. vì đây là bài toán dò tìm nhiều điều kiện, không thể VLOOKUP được. Phải vầy:

=LOOKUP(2,1/($N$2:$N$10=A2)/($O$2:$O$10=D2)/($P$2:$P$10=E2),$Q$2:$Q$10)

Mình gõ nhầm. Chính xác cái em cần. Nhưng khi số xe không có ở bảng định mức thì cho nó bằng 0 thì sao Anh ?

 

[ndu96081631]

Mình gõ nhầm. Chính xác cái em cần. Nhưng khi số xe không có ở bảng định mức thì cho nó bằng 0 thì sao Anh ?

Thì dùng COUNTIF để kiểm tra:

=[COLOR=#ff0000]IF(COUNTIF($N$2:$N$10,A2)[/COLOR],LOOKUP(2,1/($N$2:$N$10=A2)/($O$2:$O$10=D2)/($P$2:$P$10=E2),$Q$2:$Q$10)[COLOR=#ff0000],0)[/COLOR]

 

[vnproc]

Thì dùng COUNTIF để kiểm tra:

=[COLOR=#ff0000]IF(COUNTIF($N$2:$N$10,A2)[/COLOR],LOOKUP(2,1/($N$2:$N$10=A2)/($O$2:$O$10=D2)/($P$2:$P$10=E2),$Q$2:$Q$10)[COLOR=#ff0000],0)[/COLOR]

Cảm ơn Anh rất nhiều.

 

[Hoàng Trọng Nghĩa]

Thì dùng COUNTIF để kiểm tra:

=[COLOR=#ff0000]IF(COUNTIF($N$2:$N$10,A2)[/COLOR],LOOKUP(2,1/($N$2:$N$10=A2)/($O$2:$O$10=D2)/($P$2:$P$10=E2),$Q$2:$Q$10)[COLOR=#ff0000],0)[/COLOR]

Với Excel 2007 thì dùng IFERROR vào các trường hợp này là được mà Thầy! Như thế sẽ gọn hơn.

=[COLOR=#0000ff]IFERROR([/COLOR]LOOKUP(2,1/($N$2:$N$10=A2)/($O$2:$O$10=D2)/($P$2:$P$10=E2),$Q$2:$Q$10)[COLOR=#0000ff],0)[/COLOR]

 

[ndu96081631]

Với Excel 2007 thì dùng IFERROR vào các trường hợp này là được mà Thầy! Như thế sẽ gọn hơn.

=[COLOR=#0000ff]IFERROR([/COLOR]LOOKUP(2,1/($N$2:$N$10=A2)/($O$2:$O$10=D2)/($P$2:$P$10=E2),$Q$2:$Q$10)[COLOR=#0000ff],0)[/COLOR]

Cá nhân tôi chẳng bao giờ thích bẫy lỗi kiểu này (cả trong VBA và công thức).... Đương nhiên vẫn loại trừ trường hợp bất khả kháng
TRUY TÌM TẬN GỐC những lỗi có thể xảy ra để triệt tiêu mới là điều nên làm
(Cái thằng IFERROR ấy chẳng qua là hàm rút gọn của IF(ISERROR(....) nó sẽ gọi LOOKUP 2 lần đấy nhé!)

 

[Hoàng Trọng Nghĩa]

Cá nhân tôi chẳng bao giờ thích bẫy lỗi kiểu này (cả trong VBA và công thức).... Đương nhiên vẫn loại trừ trường hợp bất khả kháng
TRUY TÌM TẬN GỐC những lỗi có thể xảy ra để triệt tiêu mới là điều nên làm
(Cái thằng IFERROR ấy chẳng qua là hàm rút gọn của IF(ISERROR(....) nó sẽ gọi LOOKUP 2 lần đấy nhé!)

Em lại nghĩ khác, trong cơ chế hàm của Excel nó không gọi hàm đến 2 lần đâu mà nó replace chuỗi lỗi ra giá trị nếu lỗi.

Ví như: =IFERROR(XXX,"LỖI")

Thay vì kết quả là #N/A, #Value, #D/0 v.v... thì nếu Err.Number > 0 nó thay những chuỗi báo lỗi đó bằng "LỖI" vậy thôi.

 

[ndu96081631]

Em lại nghĩ khác, trong cơ chế hàm của Excel nó không gọi hàm đến 2 lần đâu mà nó replace chuỗi lỗi ra giá trị nếu lỗi.

Ví như: =IFERROR(XXX,"LỖI")

Thay vì kết quả là #N/A, #Value, #D/0 v.v... thì nếu Err.Number > 0 nó thay những chuỗi báo lỗi đó bằng "LỖI" vậy thôi.

Hổng gọi hàm lên thì Nghĩa nghĩ bằng cách nào nó biết hàm ấy đang bị lỗi?
Logic vấn đề quá đơn giản, ví dụ đối với VLOOKUP nhé: IFERROR(VLOOKUP(....):
- Đầu tiên phải chạy VLOOKUP
- Nếu lỗi thì trả về giá trị 1
- Nếu không lỗi thì lấy ngay VLOOKUP
Vậy đường nào nó cũng phải VLOOKUP, có phải là thừa không?
Ở đây đang nói VLOOKUP còn đở (vì hàm này tương đối nhẹ) chứ còn LOOKUP như bài số 6 thì đó là 1 công thức mảng, lần nào cũng phải gọi lên có phải là kém hiệu quả không?
-----------------
Tôi nói thế vì thấy nhiều trường hợp người dùng xài ISERROR hoặc IFERROR quá thừa. Ví dụ chỉ cần bẫy lỗi cell <> rổng thế này:
=IF(A1="", "", VLOOKUP(A1, bảng dỏ, 2,0))
thì lại đi dùng
=IF(IsError(VLOOKUP(A1, bảng dỏ, 2,0)),"",VLOOKUP(A1, bảng dỏ, 2,0))
hoặc
=IFERROR(VLOOKUP(A1, bảng dỏ, 2,0),"")
Đây là cách dùng hàm VÔ TRÁCH NHIỆM

 

[Hoàng Trọng Nghĩa]

Hổng gọi hàm lên thì Nghĩa nghĩ bằng cách nào nó biết hàm ấy đang bị lỗi?
Logic vấn đề quá đơn giản, ví dụ đối với VLOOKUP nhé: IFERROR(VLOOKUP(....):
- Đầu tiên phải chạy VLOOKUP
- Nếu lỗi thì trả về giá trị 1
- Nếu không lỗi thì lấy ngay VLOOKUP
Vậy đường nào nó cũng phải VLOOKUP, có phải là thừa không?
Ở đây đang nói VLOOKUP còn đở (vì hàm này tương đối nhẹ) chứ còn LOOKUP như bài số 6 thì đó là 1 công thức mảng, lần nào cũng phải gọi lên có phải là kém hiệu quả không?

Hồi trước ta chưa có hàm IFERROR nên ta phải cho nó chạy trước 1 lần để xét lỗi, nếu không lỗi thì mới cho chạy thật 1 lần nữa. Nay có hàm IFERROR thì không phải như cơ chế ta làm nữa mà nó duyệt tại chỗ luôn!

Nó hoạt động như sau:

Hàm VLOOKUP chạy từ đầu Table_Array đến cuối, nếu tìm đúng thì nó cho kết quả, nếu tìm không ra, tức là lỗi (#N/A). Nếu không dùng IFERROR thì nó sẽ có lỗi như thế, nhưng nếu dùng IFERROR thì khi hàm báo lỗi, thì nó chuyển mã lỗi đó thành giá trị mà ta đặt nếu bị lỗi mà không cần phải gọi hàm VLOOKUP đến 2 lần.

Tôi nói thế vì thấy nhiều trường hợp người dùng xài ISERROR hoặc IFERROR quá thừa. Ví dụ chỉ cần bẫy lỗi cell <> rổng thế này:
=IF(A1="", "", VLOOKUP(A1, bảng dỏ, 2,0))

Nếu chỉ là bẫy A1="" thôi thì Thầy lại quá chủ quan, giả sử A1 có giá trị mà giá trị đó lại không có trong bảng dò tìm thì cũng sẽ bị lỗi #N/A.

 

[ndu96081631]

Hồi trước ta chưa có hàm IFERROR nên ta phải cho nó chạy trước 1 lần để xét lỗi, nếu không lỗi thì mới cho chạy thật 1 lần nữa. Nay có hàm IFERROR thì không phải như cơ chế ta làm nữa mà nó duyệt tại chỗ luôn!

Nó hoạt động như sau:

Hàm VLOOKUP chạy từ đầu Table_Array đến cuối, nếu tìm đúng thì nó cho kết quả, nếu tìm không ra, tức là lỗi (#N/A). Nếu không dùng IFERROR thì nó sẽ có lỗi như thế, nhưng nếu dùng IFERROR thì khi hàm báo lỗi, thì nó chuyển mã lỗi đó thành giá trị mà ta đặt nếu bị lỗi mà không cần phải gọi hàm VLOOKUP đến 2 lần.

Cơ chế của nó đây:

Function IfError2k3([B]Value[/B] As Variant, Value_if_Error As Variant) As Variant
  IfError2k3 = IIf(IsError(Value), Value_if_Error, Value)
End Function

Tức đàng nào cũng phải gọi hàm chính, tức là biến Value (để biết có lỗi hay không)
Trong khi đôi lúc chí cần động tác nhỏ (IF(A1="", "", VLOOKUP(...)) ) là giải quyết nhanh gọn
Vậy cái nào hiệu quả hơn?
------------------
Với bất kỳ ai làm việc với Excel, tôi không khuyến khích dùng các hàm bẫy lỗi mà nên tìm ra tận gốc vấn đề ở đâu để triệt tiêu ---> Thế mới là hiệu quả
-------------------
Minh họa bằng bài toán thực tế:
Cách 1:
- Kiểm tra xem có vé hay không
- Có vé: Cho lên tàu
- Không có vé: không cho lên tàu
Cách 2:
- Cho tất cả lên tàu:
- Kiểm tra có vé: Ở lại
- Kiểm tra không có vé: đuổi xuống
===> Cách nào hay hơn nhỉ?

 

[vnproc]

Trường hợp tính tấn của sản phẩm theo bảng danh mục thì dùng sao Anh?

 

[ndu96081631]

Trường hợp tính tấn của sản phẩm theo bảng danh mục thì dùng sao Anh?

Tính vào chổ nào trong file của bạn vậy?

 

[vnproc]

Chổ màu gạch cột K đó anh. Nó tham chiếu ở O1419.

 

[ndu96081631]

Chổ màu gạch cột K đó anh.

Trời đất ơi! Lại phải ĐOÁN nữa sao? (hôm nay là ngày gì không biết)
Bạn không nói cách tính, ai biết đường đâu mà lần
Nếu tính bằng tay thì cell K2, K3, K4, K5 sẽ bằng bao nhiêu?

 

[vnproc]

Nó sẽ là :

K2=4600
K3=3200
K4=8000
K5=5500

 

[vodoi2x]

Chổ màu gạch cột K đó anh. Nó tham chiếu ở O1419.

Đoán đại, thử phương án này xem sao

tại K2
=SUM($F2:$J2/1000*TRANSPOSE($P$15:$P$19))

copy công thức trên và bấm Ctrl+Shift+Enter để kết thúc công thức

copy cho cho K3,K4,...

tính theo tấn chắc vậy

 

[ndu96081631]

Nó sẽ là :

K2=4600
K3=3200
K4=8000
K5=5500

Dùng cái này xem:

=SUMPRODUCT(SUMIF($O$15:$O$19,$F$1:$J$1,$P$15:$P$19)*$F2:$J2)