Lỗi hàm sumproduct--Nhờ các bác trợ giúp

[ali3340tc]

Tôi có 1 file như đính kèm, Cột Mã hàng là tên các mã hàng nhập theo từng lần một. Cột đánh giá là cột ghi lại kết quả kiểm tra cho từng lần về của các mã hàng khác nhau. Các bác cho tôi hỏi:

1- Tại sao sử dụng hàm sumproduct kết hợp cùng Indirect và address thì bị lôĩ (như trong file) trong khi đó nêú tách riêng phần address ra và cũng sử dụng sumproduct & Indirect thì lại không bị lỗi?

2- Mục đích của bài này là: Nếu 1 mã hàng sau khi kiểm tra xong bị NG, dùng hàm để phát hiện được 4 lô hàng của mã đó (trước lần nhập hiện tại) bị bao nhiêu lần NG. Nếu với đề bài như vậy thì có thể dùng hàm nào để tính nhanh gọn hơn, không phải nhập cột phụ...

3- Trong file đính kèm, ở cột phụ 1 là mã hàng đó được nhập về lần thứ bao nhiêu. Tôi để tính ngược lại 4 lô hàng gần nhất tôi phải dùng điều kiện: If(D8-4>=1,4,if(D8-3>=1,l...)) để dò tìm ngược lại (có những mã hàng về chưa đủ 5 lần thì chỉ tính tất cả các lần về). Có cách nào để không phải dò tìm kiểm thủ công như vậy không?

Mong các bác giúp đỡ. Xin cảm ơn.

 

[bebo021999]

=SUMPRODUCT((INDIRECT(ADDRESS(ROW(),2)&":"&ADDRESS(MATCH(B8&(D8-IF(D8-4>=1,4,IF(D8-3>=1,3,IF(D8-2>=1,2,IF(D8-1>=1,1,0))))),$E$8:E8,0)+7,2))=B8)*(INDIRECT(ADDRESS(ROW(),3)&":"&ADDRESS(MATCH(B8&(D8-IF(D8-4>=1,4,IF(D8-3>=1,3,IF(D8-2>=1,2,IF(D8-1>=1,1,0))))),$E$8:E8,0)+7,3))="NG"))

Thú thật là từ khi kết bạn với Excel, tôi mới thấy một công thức liên hiệp quốc "khủng" như vậy, toàn những hàm khó nhai và khó nuốt nối với nhau. Để mà phân tích đúng sai của công thức này, thì tôi chịu chết.
Tuy nhiên theo yêu cầu của bạn, tôi đề xuất dùng công thức này, khỏi cần cột phụ hay "liên hiệp quốc" gì cả:
Tại H8:

=(C8="NG")*MIN(5,SUMPRODUCT(($B$7:B7=B8)*($C$7:C7="NG"))+1)

Thấy bạn cũng có ý tìm tòi học hỏi, tôi xin chia sẻ bạn về cách dùng công thức này như sau:
SUMPRODUCT(($B$7:B7=B8)*($C$7:C7="NG"))+1: Đếm tất cả các dòng phía trên thỏa 2 điều kiện: cùng mã hàng dòng hiện tại và "NG"
MIN(5,SUMPRODUCT()) : Kết quả nào nhỏ hơn hoặc bằng 5 thì lấy
C8="NG" : Nếu TRUE thì trả về 1, không thì về 0

 

[ali3340tc]

Thú thật là từ khi kết bạn với Excel, tôi mới thấy một công thức liên hiệp quốc "khủng" như vậy, toàn những hàm khó nhai và khó nuốt nối với nhau. Để mà phân tích đúng sai của công thức này, thì tôi chịu chết.

Cảm ơn bác đã giúp đỡ. Do trình excel còn hạn chế rất nhiều nên hiện tại tôi chỉ nghĩ được làm sao ra kết quả là phang vào luôn chứ chưa thể tính xem đường nào ngắn nhất và gần nhất. Vì vậy mong các bác giúp đỡ thêm.

 

[ali3340tc]

Tôi đã tìm ra lỗi của công thức. Đó là thay đoạn Address(row()....) thành Address(rows($1:8)....
Về hàm giúp đỡ của bạn Bebo..., tôi đã áp dụng thử nhưng không đúng theo yêu cầu của đầu bài. Trong đầu bài yêu cầu: Nếu phát hiện NG thì sẽ truy lại 4 lần nhập gần lần bị NG nhất xem trong 5 lần (4 lần trước và lần này) có bao nhiêu lô bị NG. Công thức của bebo chỉ đáp ứng được việc truy lại các lô NG. Mong các bác tiếp tục giúp đỡ để công thức ngắn hơn và không phải lập cột phụ.

 

[bebo021999]

Tôi đã tìm ra lỗi của công thức. Đó là thay đoạn Address(row()....) thành Address(rows($1:8)....
Về hàm giúp đỡ của bạn Bebo..., tôi đã áp dụng thử nhưng không đúng theo yêu cầu của đầu bài. Trong đầu bài yêu cầu: Nếu phát hiện NG thì sẽ truy lại 4 lần nhập gần lần bị NG nhất xem trong 5 lần (4 lần trước và lần này) có bao nhiêu lô bị NG. Công thức của bebo chỉ đáp ứng được việc truy lại các lô NG. Mong các bác tiếp tục giúp đỡ để công thức ngắn hơn và không phải lập cột phụ.

Trong công thức có so sánh MIN(5,SUMPRODUCT) rồi, nghĩa là lấy tối đa 5 lần. Đưa công thức vào file mẫu thấy khớp mà, bị lệch chỗ nào vậy bạn?

 

[ali3340tc]

Trong file ví dụ của mình, hãy để ý ở ô B18 nhé: Nếu mình thay giá trị đánh giá ở ô C17 với mã a, đánh giá là OK, vậy thì tại ô B18, số lần bị NG phải là 4 lần (gồm có: B18, B16, B14 và B13 bị NG) còn B17 là OK. Tuy nhiên theo công thức của bạn thì tại ô B18 vẫn là 5 lần. Có thể yêu cầu của mình mô tả hơi khó hiểu một chút. Mong bạn giúp đỡ thêm nhé.

 

[bebo021999]

OK, giả sử dòng 17 là OK, theo yêu cầu của bạn, nếu đến B18 gặp NG thì đếm ngược lại 5 lần bị NG là: 11,13,14,16 và 18.
Đúng không?

 

[ali3340tc]

Không phải vậy bác à: 2- Mục đích của bài này là: Nếu 1 mã hàng sau khi kiểm tra xong bị NG, dùng hàm để phát hiện được 4 lô hàng của mã đó (trước lần nhập hiện tại) bị bao nhiêu lần NG. Nếu với đề bài như vậy thì có thể dùng hàm nào để tính nhanh gọn hơn, không phải nhập cột phụ...
Điều đó có nghĩa là tính ngược từ lô bị NG thêm 4 lô (có thể NG, có thể OK) để xem trong tổng cộng 5 lô gần nhất của mã hàng đó có bao nhiêu lô bị NG.

 

[vodoi2x]

Không phải vậy bác à: 2- Mục đích của bài này là: Nếu 1 mã hàng sau khi kiểm tra xong bị NG, dùng hàm để phát hiện được 4 lô hàng của mã đó (trước lần nhập hiện tại) bị bao nhiêu lần NG. Nếu với đề bài như vậy thì có thể dùng hàm nào để tính nhanh gọn hơn, không phải nhập cột phụ...
Điều đó có nghĩa là tính ngược từ lô bị NG thêm 4 lô (có thể NG, có thể OK) để xem trong tổng cộng 5 lô gần nhất của mã hàng đó có bao nhiêu lô bị NG.

+ Không cần cột phụ
+ xét cả dòng trống nếu có (hiện không có)

Dùng công thức này cho dòng 8

=IF(C8<>"NG","",SUMPRODUCT(($B$8:$B8=B8)*($C$8:$C8="NG")*(ROW($B$8:$B8)>=LARGE(($B$8:$B8=B8)*ROW($B$8:$B8),MIN(5,COUNTA($B$8:$B8))))))

Copy cho các dòng còn lại...

 

[ali3340tc]

+ Không cần cột phụ
+ xét cả dòng trống nếu có (hiện không có)

Dùng công thức này cho dòng 8

=IF(C8<>"NG","",SUMPRODUCT(($B$8:$B8=B8)*($C$8:$C8="NG")*(ROW($B$8:$B8)>=LARGE(($B$8:$B8=B8)*ROW($B$8:$B8),MIN(5,COUNTA($B$8:$B8))))))

Copy cho các dòng còn lại...

Cảm ơn bác nhiều. Như vậy là đúng với cái tôi đang cần rồi. Cảm ơn các bác đã giúp đỡ.