Hỗ trợ công thức trong bảng KHSX

[tohoangdung]

Em có bảng kế hoạch nhập nguyên liệu, trong sheet "demand", em lập công thức:
=SUMPRODUCT(bom!$H3:$EH3,INDIRECT("'"&"FG"&"'!"&"$AK$"&COLUMN()-2&":$FK$"&E$1)) thì báo lỗi #VALUE! (xem hình).

Có 01 bạn hỗ trợ sửa công thức trên thành:
=SUMPRODUCT(bom!$H3:$EH3*(+N("haha")=0)*INDIRECT("'FG'!AK"&(COLUMN()-2)&":FK"&(COLUMN()-2))) chạy trong Office 365 thì ra kết quả đúng, nhưng chạy trong Office 2019 64 bit thì hiện kết quả là #VALUE!

Dữ liệu sẽ được lấy Tổng tích từ sheet "bom" (bom!$H3:$EH3) và sheet "FG" (từ AK3:FK33)

Nhờ các bác giúp xử lý công thức hoặc dùng công thức khác để ra kết quả đúng, và có thể chạy được trong Office 2019 ạ (em có đính kèm file). Em xin cảm ơn!

---

 

[VetMini]

365 ra kết quả mà các phiên bản cũ hơn không ra thì khả năng 95% là công thức mảng. Phải gõ Ctrl+Shift+Enter.

 

[tohoangdung]

365 ra kết quả mà các phiên bản cũ hơn không ra thì khả năng 95% là công thức mảng. Phải gõ Ctrl+Shift+Enter.

Mình đã nhấn Ctrl+Shift+Enter, cũng không ra kết quả.

 

[VetMini]

Hàm Column trả về một mảng chứ không phải một trị, dẫu cho mảng ấy chỉ có một phần tử.
Công thức của bạn dùng hàm column cho nên Indirect bị hổng giò.

Sửa lại, gói Column bên trong Index để nó chuyển thành trị đơn (scalar).
INDEX(Column(),1,1)

Chú thích:
Tôi không hiểu sao 365 của bạn lại ra kết quả với hàm ở bài #1. Phiên bản 365 của tôi cũng ra #Value! như phiên bản 2019.
Tuy nhiên, 365 có cách lấy 1 trị đầu tiên của mảng không cần Index: đặt dấu @ trước hàm Column:
@Column()

 

[tohoangdung]

Hàm Column trả về một mảng chứ không phải một trị, dẫu cho mảng ấy chỉ có một phần tử.
Công thức của bạn dùng hàm column cho nên Indirect bị hổng giò.

Sửa lại, gói Column bên trong Index để nó chuyển thành trị đơn (scalar).
INDEX(Column(),1,1)

Chú thích:
Tôi không hiểu sao 365 của bạn lại ra kết quả với hàm ở bài #1. Phiên bản 365 của tôi cũng ra #Value! như phiên bản 2019.
Tuy nhiên, 365 có cách lấy 1 trị đầu tiên của mảng không cần Index: đặt dấu @ trước hàm Column:
@Column()

Đây ạ, Office 365 cho ra kết quá, nhưng Office 2019 thì ra #VALUE!

 

[VetMini]

Cái công thức ấy dùng hàm N() để chuyển range thành mảng trước. Vì vậy nó có thể cặp với cái con toán kế là mảng của Indirect.
Các phiên bản trước 365 không có khả năng tự hóa mảng như vậy. Hàm Index là cách uyển chuyển nhất để chuyển đổi qua lại giữa range, mảng, và trị đơn (scalar).

Excel 2013:

 

[tohoangdung]

Cái công thức ấy dùng hàm N() để chuyển range thành mảng trước. Vì vậy nó có thể cặp với cái con toán kế là mảng của Indirect.
Các phiên bản trước 365 không có khả năng tự hóa mảng như vậy. Hàm Index là cách uyển chuyển nhất để chuyển đổi qua lại giữa range, mảng, và trị đơn (scalar).

Excel 2013:
View attachment 281415

Vâng, em hiểu rồi, em cảm ơn anh nhiều!

 

[nghiemcongdien]

Vâng, em hiểu rồi, em cảm ơn anh nhiều

Mình không giúp được về công thức nhưng mình thấy sao bạn không cải tiến cái cấu trúc BOM nhỉ.

Mã sản phẩm

Sản phẩm

ĐVT

Mã vật tư

Vật tư

ĐVT vật tư

Số lượng

 

[tohoangdung]

Mình không giúp được về công thức nhưng mình thấy sao bạn không cải tiến cái cấu trúc BOM nhỉ.

Mã sản phẩm

Sản phẩm

ĐVT

Mã vật tư

Vật tư

ĐVT vật tư

Số lượng

Mình chưa biết cải tiến như nào, tại cũng mới chuyển qua làm Kế hoạch, chưa biết nhiều, khó tới đâu thì mình tự tìm hiểu thêm.

 

[tohoangdung]

Em kiểm tra lại công thức thì từ ngày 1 đến ngày 17 cho ra kết quả đúng, nhưng từ ngày 18 trở về sau thì kết quả không đúng.
Trong file em có tô vàng tại các sheet demand, bom,và FG để mọi người dễ quan sát. Nếu làm thủ công thì lọc ngày 18 trong sheet FG xem cần sản xuất ra những mã thành phẩm nào, copy mã, qua sheet bom tìm kiếm mã thành phẩm vừa copy cần bao nhiêu nguyên liệu, ở đây trong sheet bom là 4 cái, số lượng thành phẩm cần làm của ngày 18 trong sheet FG là 960 cái. Vậy nguyên liệu FKOM-A011KKKZ cần sử dụng trong ngày 18 ở sheet demand là 4 x 960 = 3840 cái, nhưng công thức trong file thì hiện kết quả là 6768 cái => SAI.
Công thức này có nhược điểm nữa là giả sử 1 dòng nào đó trong các sheet không đúng vị trí hiện tại thì ra kết quả SAI.

Nhờ các thầy sửa công thức giúp em ạ.

 

[Phan Thế Hiệp]

Công thức này có nhược điểm nữa là giả sử 1 dòng nào đó trong các sheet không đúng vị trí hiện tại thì ra kết quả SAI.

Lời khuyên:
1/ Do dữ liệu của bạn tương đối lớn, nên nhờ anh em viết code VBA thì tốt hơn, tránh nặng file mỗi khi sử dụng.
2/ Dùng công thức muốn kết quả đúng khi các vị trí dòng / cột không cố định thì phải so khớp, nhưng phải đụng đến mảng, và kết quả thể hiện trên nhiều dòng nhiều cột trong sheet "demand" nên sẽ làm trì trệ máy. Công thức dưới đây tôi đã cố hạn chế ít nhiều việc ghép mảng để so khớp, tuy nhiên hiện tượng chậm vẫn sẽ có. Nếu bạn thích thì cứ thử, như sau tại sheet "demand":

E4=SUMPRODUCT(SUMIF(bom!$H$2:$EH$2,FG!$C$3:$C$133,INDEX(bom!$H$3:$H$1600,-LOOKUP(,-ROW($1:$1600)/($A4=bom!$A$3:$A$1600)/($B4=bom!$B$3:$B$1600)/($C4=bom!$C$3:$C$1600))))*INDEX(FG!$D$3:$AH$133,,MATCH(E$3,FG!$D$2:$AH$2,)))

Enter. Fill qua phải, rồi xuống.

Thân

 

[tohoangdung]

Lời khuyên:
1/ Do dữ liệu của bạn tương đối lớn, nên nhờ anh em viết code VBA thì tốt hơn, tránh nặng file mỗi khi sử dụng.
2/ Dùng công thức muốn kết quả đúng khi các vị trí dòng / cột không cố định thì phải so khớp, nhưng phải đụng đến mảng, và kết quả thể hiện trên nhiều dòng nhiều cột trong sheet "demand" nên sẽ làm trì trệ máy. Công thức dưới đây tôi đã cố hạn chế ít nhiều việc ghép mảng để so khớp, tuy nhiên hiện tượng chậm vẫn sẽ có. Nếu bạn thích thì cứ thử, như sau tại sheet "demand":

E4=SUMPRODUCT(SUMIF(bom!$H$2:$EH$2,FG!$C$3:$C$133,INDEX(bom!$H$3:$H$1600,-LOOKUP(,-ROW($1:$1600)/($A4=bom!$A$3:$A$1600)/($B4=bom!$B$3:$B$1600)/($C4=bom!$C$3:$C$1600))))*INDEX(FG!$D$3:$AH$133,,MATCH(E$3,FG!$D$2:$AH$2,)))

Enter. Fill qua phải, rồi xuống.

Thân

Em cảm ơn anh, công thức của anh cho kết quả đúng, mà công nhận dữ liệu file này cũng lớn nên file chạy chậm. Em sẽ quan tâm đến lời khuyên của anh.