Vấn đề về kết hợp hàm SUMPRODUCT((INDIRECT(LEFT*,LEN(*..............)

[mr.quyét7]

Tại vùng C22:C24 của tkb.xls thì mình có tên môn và tên giáo viên và mình muốn đếm theo điều kiện là nếu “Cn.Tới” lên lớp môn “ GDTC” thì sẽ hiện lên là 1 ở ô mình đặt công thức.
Do lười ko muốn đếm thủ công nên mình đã sử dụng công thức. Vậy mình viết ra đây các bạn xem giúp mình nhé:
Điều kiện: Đếm số lần “Cn.Tới” lên lớp môn “GDTC”
I. Cách đơn giản: COUNTIFS([tkb2013.xls]lht!$C$22:$P$24,"*Tới*",[tkb 2013.xls]lht'!$C$21:$P$23,"*GDTC*")
Nhưng nếu làm như công thức trên thì mỗi lần có sự chỉnh sửa mình sẽ rất mệt. Ví dụ như nếu ĐD8A không nằm tại vùng C22:C24 thì mình sẽ phải dùng lệnh replace L. Và đấy mới chỉ là 1 môn học, mà có những tận hơn 30 môn học và 60 giáo viên thêm 24 lớp học. Do đó mình đã tham khảo 1 số nguồn trên internet, có cả 4r GPE thì mình đã quyết định sử dụng hàm sumproduct
II. Sumproduct
Mình đã nhập dữ liệu vào conglich.xls
1. Tại ô G15: nhập text “C101”
2. Tại ô G16: nhập text “B105”
3. Tại ô K6: nhập text [tkb2013.xls]lht!$C$22:$C$24
4. Tại ô K7: nhập text [tkb2013.xls]lht!$C$21:$C$23
Và tại ô G17 nhập công thức:

SUMPRODUCT((INDIRECT(LEFT(K6,LEN(K6)))=INDIRECT(G16)*(INDIRECT(LEFT(K7,LEN(K7)))=INDIRECT(G15))))

Và mình phân tích công thức trên như sau:
1. ((INDIRECT(LEFT(K6,LEN(K6))): link về mảng $C$22:$C$24 của tkb2013.xls
2. INDIRECT(G16): trả về tham chiếu của ô B105 tức : “Cn.Tới”
3. ((INDIRECT(LEFT(K6,LEN(K6))): link về mảng $C$21:$C$23 của tkb2013.xls
4. INDIRECT(G16): trả về tham chiếu của ô C102 tức : “GDTC”
Và kết quả của ra là ………….#VALUE! L
Vậy ai có phân tích và chỉ dùm mình lỗi ở công thức trên được ko
File đính kèm ở dưới, các bạn giúp đỡ mình với nhé!.

 

[dhn46]

Đọc File của bạn mà lung tùng bèng quá. Híc
Tạm giải giải thich cho bạn như sau
Công thức của bạn

=SUMPRODUCT((INDIRECT(LEFT(K6,LEN(K6)))=INDIRECT(G16)*(INDIRECT(LEFT(K7,LEN(K7)))=INDIRECT(G15))))

Cần sửa như sau:
1 - Dòng Môn của tkb2013.xls nằm trong dòng 23 do đó bạn cần sửa ô K7 là

[tkb2013.xls]lht!$C$23:$P$23

2 - Dòng Giáo viên của tkb2013.xls nằm trong dòng 24 do đó bạn cần sửa ô K7 là

[tkb2013.xls]lht!$C$24:$P$24

3 - Tại C102 bạn để môn là: GDTC* => Cái dấu * để là gì? => Bỏ đi (nếu muốn để thì phải xử lý trong công thức vì file tkb2013 của bạn không có môn GDTC*
4 - Sau khi sửa các vấn đề trên bạn dùng công thức:

=SUMPRODUCT((LEFT(INDIRECT(K7),4)=INDIRECT(G15))*(INDIRECT(K6)=INDIRECT(G16)))

Đây là công thức tối giản cho môn GDTC còn các môn khác bạn hãy tùy biến hàm Left trong công thức!

 

[mr.quyét7]

Cảm ơn bạn nhiều lắm