Sum của 2 hàm Vlookup báo lỗi khi Vlookup tìm không có lookup_value, xin giúp đỡ (1 người xem)

[jazzbluephile]

Xin chào các anh chị trong diễn đàn,
mình đang làm bảng điểm tổng kết cuối năm, mình dùng hàm Sum để cộng hai giá trị do Vlookup tìm được từ 2 bảng điểm của 2 học kỳ rồi chia 2 cho điểm TB năm. tuy nhiên, vì HK1 và HK 2 có một số học sinh bỏ hoặc thêm mới nên lookup_value tìm không có, dẫn đến hàm sum báo lỗi ###. nếu thêm giá trị lookup_value cho bảng nào không có thì tạm khắc phục được nhưng làm như thế thì bảng điểm của HK đó sẽ bị thay đổi theo, nên không phải là biện pháp hữu hiệu.
mình nhờ các anh chị em giúp trường hợp này.

bảng điểm đính kèm, ACE download về xem và chỉ giúp nhé.
cảm ơn rất nhiều.

 

[Po_Pikachu]

Vậy thì nếu tìm không có thì cho nó bằng 0 thôi.
Thân.

 

[le phuong van]

Tìm lỗi

Xin chào các anh chị trong diễn đàn,
mình đang làm bảng điểm tổng kết cuối năm, mình dùng hàm Sum để cộng hai giá trị do Vlookup tìm được từ 2 bảng điểm của 2 học kỳ rồi chia 2 cho điểm TB năm. tuy nhiên, vì HK1 và HK 2 có một số học sinh bỏ hoặc thêm mới nên lookup_value tìm không có, dẫn đến hàm sum báo lỗi ###. nếu thêm giá trị lookup_value cho bảng nào không có thì tạm khắc phục được nhưng làm như thế thì bảng điểm của HK đó sẽ bị thay đổi theo, nên không phải là biện pháp hữu hiệu.
mình nhờ các anh chị em giúp trường hợp này.

bảng điểm đính kèm, ACE download về xem và chỉ giúp nhé.
cảm ơn rất nhiều.

Do cơ sở dữ liệu ở 2 học kỳ không đồng nhất nên dễ sinh lỗi:
VD: Thầy Phương Đạt trong HK1, HK2 nếu gõ điểm vào thì cũng vẫn không có điểm tb cho cả năm.
Lý do sau chữ "Phương " trong bảng HK1 có khoảng trống, còn sau chữ "Phương" bảng HK2 thì không, làm hàm Vlooup tìm không thấy nên cho là 0

 

[Ba Tê]

Xin chào các anh chị trong diễn đàn,
mình đang làm bảng điểm tổng kết cuối năm, mình dùng hàm Sum để cộng hai giá trị do Vlookup tìm được từ 2 bảng điểm của 2 học kỳ rồi chia 2 cho điểm TB năm. tuy nhiên, vì HK1 và HK 2 có một số học sinh bỏ hoặc thêm mới nên lookup_value tìm không có, dẫn đến hàm sum báo lỗi ###. nếu thêm giá trị lookup_value cho bảng nào không có thì tạm khắc phục được nhưng làm như thế thì bảng điểm của HK đó sẽ bị thay đổi theo, nên không phải là biện pháp hữu hiệu.
mình nhờ các anh chị em giúp trường hợp này.

bảng điểm đính kèm, ACE download về xem và chỉ giúp nhé.
cảm ơn rất nhiều.

Chỉnh sửa công thức một số cột trong Sheet Ni.N2 (Tô màu xanh).
Vì không biết một người chỉ có tên trong một HK thì phải làm sao, tạm thời để trống.
Các Sheet khác bạn tự nghiên cứu chỉnh sửa nhé!
--- Chú ý đọc bài #3, dữ liệu của Bạn dính vụ này đó, Bảng của tôi đã chỉnh lại rồi.

 

[jazzbluephile]

cảm ơn các bạn nhiều, đặc biệt là bạn Ba Tê.
tuy nhiên, ở chỗ bạn đặt DiemNi1=VLOOKUP(Ni.N2!$B13;nihk1;COLUMNS(Ni.N2!$B);0), mình thật sự không hiểu tại sao đang tìm giá trị điểm trên bảng Ni.hk1 mà lại chỉ qua bảng Ni.N2? với lại đặt cái cột (ở đây là P) như thế nào? nếu trên 1 sheet thì mình hiểu nhưng ở đây làm trên nhiều sheet nên không nắm bắt được. mong các bạn chỉ cho.
cảm ơn các bạn nhiều.

 

[Lê Duy Thương]

cảm ơn các bạn nhiều, đặc biệt là bạn Ba Tê.
tuy nhiên, ở chỗ bạn đặt DiemNi1=VLOOKUP(Ni.N2!$B13;nihk1;COLUMNS(Ni.N2!$B);0), mình thật sự không hiểu tại sao đang tìm giá trị điểm trên bảng Ni.hk1 mà lại chỉ qua bảng Ni.N2? với lại đặt cái cột (ở đây là P) như thế nào? nếu trên 1 sheet thì mình hiểu nhưng ở đây làm trên nhiều sheet nên không nắm bắt được. mong các bạn chỉ cho.
cảm ơn các bạn nhiều.

COLUMNS(Ni.N2!$B)Tức là đếm từ cột B đến p ở bên sheet Ni.N2 là bao nhiêu cột

 

[le phuong van]

Bỏ bớt cột phụ

Mình đã chỉnh lại một số công thức, nên đã bỏ được một số cột phụ, bạn xem ở sheet tăng.HK1, mình chưa hiểu cột AA dùng để làm gì? Nếu thấy không cần thì bỏ luôn.

 

[jazzbluephile]

COLUMNS(Ni.N2!$B)Tức là đếm từ cột B đến p ở bên sheet Ni.N2 là bao nhiêu cột

ở đây bạn Ba Tê đặt một công thức sẵn trên vùng define, nhưng công thức đó lại cho cả 1 bảng gồm rất nhiều column, vậy muốn đặt được công thức như thế thì phải làm thế nào? nếu đặt công thức cho từng column thì dễ hiểu, nhưng đây là đặt cho tất cả các column cần thiết! khó ở chỗ này đây.

 

[jazzbluephile]

cột AA là để tránh trường hợp xếp hạng loại thấp mà vị thứ lại cao, do điểm không đều. nó chỉ làm cho khoảng cách chênh lệch cao hơn để khi xếp hạng không bị tình trạng vị thứ cao mà xếp hạng học lực thấp.

 

[Ba Tê]

ở đây bạn Ba Tê đặt một công thức sẵn trên vùng define, nhưng công thức đó lại cho cả 1 bảng gồm rất nhiều column, vậy muốn đặt được công thức như thế thì phải làm thế nào? nếu đặt công thức cho từng column thì dễ hiểu, nhưng đây là đặt cho tất cả các column cần thiết! khó ở chỗ này đây.

Cái này thì chỉ là sử dụng địa chỉ tương đối và tuyệt đối, bạn làm ví dụ này và tự ngâm cứu nhé:
+ Ô A1, gõ công thức: =columns($A:A). Kéo sang ngang, xuống dưới... để xem kết quả. Xóa nó đi khi đã hiểu ra điều gì đó.
+ Chọn ô A1, đặt Name tại đây là GPE, Refers to: gõ =columns($A:A) - Add - OK.
Tại ô A1, gõ: =GPE, kéo sang ngang, xuống dưới... so với cách trên và ngâm cứu.
Mong rằng bạn sẽ "Ồ!..." như tôi cách đây 1 năm, khi chưa biết Name là gì.
Cảm ơn GPE!

 

[jazzbluephile]

mình đã làm theo chỉ dẫn của ban Ba Tê nhưng không hiểu ứng dụng chức năng GPE này như thế nào? xin bạn Ba Tê giải thích thêm cho mình biết với.
mình cũng xin bạn Ba Tê chỉ cho cách lập mấy cái name trong bảng điểm trên: DiemNi1 và DiemNi2, thật sự mình chưa hiểu cách làm của bạn, hic.

 

[yeudoi]

mình đã làm theo chỉ dẫn của ban Ba Tê nhưng không hiểu ứng dụng chức năng GPE này như thế nào? xin bạn Ba Tê giải thích thêm cho mình biết với.
mình cũng xin bạn Ba Tê chỉ cho cách lập mấy cái name trong bảng điểm trên: DiemNi1 và DiemNi2, thật sự mình chưa hiểu cách làm của bạn, hic.

Theo mình hiểu là thế này:
1. Anh Batê muốn chỉ cách bạn đặt name và hiểu công dụng của hàm =COLUMNS(Sheet1!$A:B)
Nói cách đơn giản là khi bạn muốn dùng công thức VD: Sum(A1:A10) thì để khỏi gõ công thức tại các ô bạn vào name và đặt tên là GPE ( hoặc tên gì cũng được miễn gợi nhớ). Sau đó vào lại ô và gõ = GPE ( tên bạn đã đặt) thì công thức đó vẫn hiểu như bạn gõ công thức. Còn nhiều tác dụng của name bạn có thể tìm trên diễn đàn.
2. Cách lập name DiemNi1 và DiemNi2. Bạn vào Insert/name/Define bấm vào DiemNi1 sẽ thấy nó đặt tên cho công thức: =VLOOKUP(Ni.N2!$B1,nihk1,COLUMNS(Ni.N2!$B:G),0)
Nếu tại sheets(" Ni.N2") ô E11 bạn thay công thức:

=IF(OR(ISNA(DiemNi1),ISNA(DiemNi2)),"",ROUND((DiemNi1+DiemNi2)/2,1))

Bằng công thức

=IF(OR(ISNA(VLOOKUP(Ni.N2!$B11,nihk1,COLUMNS(Ni.N2!$B:G),0)),ISNA(VLOOKUP(Ni.N2!$B11,nihk2,COLUMNS(Ni.N2!$B:K),0))),"",ROUND((DiemNi1+DiemNi2)/2,1))

Bạn sẽ thấy nó cho kết quả giống nhau.

 

[jazzbluephile]

cho hỏi là, vì dụ trong ct DiemNi1=VLOOKUP(Ni.N2!$B11;nihk1;COLUMNS(Ni.N2!$B:Q);0) thì đặt name từ sheet nào? tại sao lại tham chiếu qua Columns trên sheet Ni.N2! mà không phải là sheet Ni.HKI? chẳng hạn: DiemNi1=VLOOKUP(Ni.N2!$B11;nihk1;COLUMNS(Ni.HKI!$B:Q);0).

cụ thể là mình muốn hỏi trong bảng điểm đó, mình phải đặt name từ sheet nào? và chỉ tham chiếu columns ở sheet nào?

Mình đã hoàn thành bảng điểm theo chỉ dẫn của các bạn, chỗ columns mình làm có hơi khác (theo cách hiểu của mình), không biết đúng sai thế nào. nhờ các bạn kiểm tra lại giúp.

cảm ơn rất nhiều.

 

[leducnhu]

Vậy thì nếu tìm không có thì cho nó bằng 0 thôi.
Thân.

Bac Pikachu ơi, Trong Vlookup nếu ko tìm thấy giá trị thì trả về số không (cho bằng 0) như thế nào hả bác? Em có bài toán tương tự nhưng ko biết xử lý thế nào???

 

[nghiaphuc]

Bac Pikachu ơi, Trong Vlookup nếu ko tìm thấy giá trị thì trả về số không (cho bằng 0) như thế nào hả bác? Em có bài toán tương tự nhưng ko biết xử lý thế nào???

Công thức sẽ có kiểu như vầy nè: =IF(ISNA(VLOOKUP(Gì gì đó)),0,VLOOKUP(Gì gì đó))

 

[xoanvinh]

Xin chào các anh chị!
Em là thành viên mới gia nhập, em chưa hiểu rõ về hàm tìm : VLOOKUP và HLOOKUP,và khi em dò tìm thì ra kết quả nhưng khi sao chép công thức cho các ô tiếp theo lại không được, thường hay bị báo lỗi như:#REF!.
Các anh chị giúp em với nhé, em cảm ơn nhiều!

 

[hoamattroicoi]

Xin chào các anh chị!
Em là thành viên mới gia nhập, em chưa hiểu rõ về hàm tìm : VLOOKUP và HLOOKUP,và khi em dò tìm thì ra kết quả nhưng khi sao chép công thức cho các ô tiếp theo lại không được, thường hay bị báo lỗi như:#REF!.
Các anh chị giúp em với nhé, em cảm ơn nhiều!

Hàm VLOOKUP

Chức năng: Hàm VLOOKUP là hàm dò tìm theo cột, sẽ trả về giá trị của một ô nằm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm.

Cú pháp hàm: VLOOKUP(lookup_value,table_array,col_index_num,option_lookup)

- Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm trong cột đầu tiên của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.

- Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (I,j >=1), trong đó cột thứ nhất của bảng dò tìm sẽ được dùng để dò tìm.

- Col_index_num: là số thứ tự của cột (tính từ trái qua phải) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Col_index_num phải >=1 và <= số cột lớn nhất có trong bảng dò tìm, ngược lại hàm sẽ trả về #VALUE! hoặc #REF.

- Option_lookup: là tùy chọn xác định kiểu dò tìm, có 2 kiểu dò tìm:

• True hoặc 1 hoặc để trống: là kiểu dò tìm tương đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm.
• False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, hàm sẽ trả về #N/A
• Bạn kiểm tra lại địa chỉ tham chiếu và cột trả về trong hàm vlookup nhé, còn nếu vẫn không được thì chuyển file lên diễn đàn mình sửa cho!