Bỏ bảng phụ để tính số Phần sữa dựa vào tổng 2 cột tăng ca

[maikhoi]

Hiện mình đang tính số phần sữa (cột HD) dựa trên bảng phụ từ cột HE đến cột II, bảng phụ này là tổng 2 cột tăng ca tương ứng mỗi ngày, có cách nào bỏ bảng phụ, mà vẫn tính được số phần sữa không ah. Dùng công thức thôi ah.
Cảm ơn sự troqj giúp ah

 

[HieuCD]

Hiện mình đang tính số phần sữa (cột HD) dựa trên bảng phụ từ cột HE đến cột II, bảng phụ này là tổng 2 cột tăng ca tương ứng mỗi ngày, có cách nào bỏ bảng phụ, mà vẫn tính được số phần sữa không ah. Dùng công thức thôi ah.
Cảm ơn sự troqj giúp ah

HC5 =SUMPRODUCT(--TEXT(SUMIF(OFFSET(E5,,ROW($1:$31)*5-4,,2),">0"),"[>=4]\2;[>=2]\1;\0"))

Copy xuống . . .

 

[maikhoi]

HC5 =SUMPRODUCT(--TEXT(SUMIF(OFFSET(E5,,ROW($1:$31)*5-4,,2),">0"),"[>=4]\2;[>=2]\1;\0"))

Copy xuống . . .

anh ơi kết quả theo từng dòng có người đúng, có người không ah, anh xem giúp em nhé. anh có thề giải thích hàm text, row() x5 đi ah, em không hiểu ah

 

[HieuCD]

anh ơi kết quả theo từng dòng có người đúng, có người không ah, anh xem giúp em nhé. anh có thề giải thích hàm text, row() x5 đi ah, em không hiểu ah

Bảng phụ của bạn có cột tính sai, bạn nên kiểm tra lại
ROW($1:$31)*5: Có 31 ngày, mỗi ngày có 5 cột
Hàm Text khá đa năng và phức tạp, bạn nên xem phần hướng dẫn của Microsoft.
Định dạng theo 2 điều kiện "[>=4]\2;[>=2]\1;\0" : >=4 tính 2, >=2 tính 1 còn lại là 0

 

[maikhoi]

Bảng phụ của bạn có cột tính sai, bạn nên kiểm tra lại
ROW($1:$31)*5: Có 31 ngày, mỗi ngày có 5 cột
Hàm Text khá đa năng và phức tạp, bạn nên xem phần hướng dẫn của Microsoft.
Định dạng theo 2 điều kiện "[>=4]\2;[>=2]\1;\0" : >=4 tính 2, >=2 tính 1 còn lại là 0

Cảm ơn anh! em hiểu rồi ah

 

[Phuocam]

Hiện mình đang tính số phần sữa (cột HD) dựa trên bảng phụ từ cột HE đến cột II, bảng phụ này là tổng 2 cột tăng ca tương ứng mỗi ngày, có cách nào bỏ bảng phụ, mà vẫn tính được số phần sữa không ah. Dùng công thức thôi ah.
Cảm ơn sự troqj giúp ah

.
Trong file, bạn có dùng công thức mảng:

=SUM(IF(HE5:II5>=4,2,IF(HE5:II5>=2,1,0)))

Muốn không dùng bảng phụ, có thể sửa thành:

=SUM(IF($E$4:$EY$4="CĐ",IF(F5:EZ5+G5:FA5>=4,2,IF(F5:EZ5+G5:FA5>=2,1,0))))
.

 

[nttrung_proneu]

HC5 =SUMPRODUCT(--TEXT(SUMIF(OFFSET(E5,,ROW($1:$31)*5-4,,2),">0"),"[>=4]\2;[>=2]\1;\0"))

Copy xuống . . .

Anh ơi giải thích giúp em công thức này một chút ạ: 1. Tại sao phải dùng dấu -- để đưa kết quả dạng boolean về 0, 1 (vì e thấy đây đều là phép tính cho ra kết quả số); 2. Tại sao phải dùng hàm sumif cho điều kiện ">0" mà không thể dùng hàm sum trực tiếp
Em cảm ơn anh!

 

[LmoovoenX]

Anh ơi giải thích giúp em công thức này một chút ạ: 1. Tại sao phải dùng dấu -- để đưa kết quả dạng boolean về 0, 1 (vì e thấy đây đều là phép tính cho ra kết quả số); 2. Tại sao phải dùng hàm sumif cho điều kiện ">0" mà không thể dùng hàm sum trực tiếp
Em cảm ơn anh!

Để mình trả lời hộ anh Hiếu:
1. Các hàm dạng sum sẽ bỏ qua dữ liệu không phải là số, nên phải sử dụng -- để biến dữ liệu từ dạng chuỗi (kết quả hàm text luôn là dữ liệu kiểu chuỗi) về số.
2. Nếu sử dụng hàm sum mà không phải sumif, nó sẽ tính tổng tất cả các thành phần mà hàm offset trả về, rồi mới tính tiếp đến điều kiện của hàm text. do đó kết quả sẽ luôn là 2 hoặc 1 hoặc 0. Khi dùng sumif như trên, kết quả sẽ trả về dạng mảng. Hàm text sẽ so sánh các phần tử của mảng với điều kiện "[>=4]\2;[>=2]\1;\0", trả về kết quả theo yêu cầu.
Nếu muốn sử dụng sum trong trường hợp này, bạn có thể dùng subtotal với tham số 9.
SUMPRODUCT(--TEXT(SUBTOTAL(9,OFFSET(E5,,ROW($1:$31)*5-4,,2)),"[>=4]\2;[>=2]\1;\0"))
Anh @HieuCD có bổ sung thêm gì không ạ?

 

[LmoovoenX]

=SUMPRODUCT((SUBTOTAL(9,OFFSET(E5,,ROW($1:$31)*5-4,,2))>={4,2})*{1,1})

Có thể dùng công thức như trên nhé.
Giải thích: Hàm subtotal tạo 1 mảng như cột HE đến II trong bảng của bạn.
Sau đó lấy mảng này lần lượng so sánh với 4 và 2. Có 3 trường hợp xảy ra.
1. Số trong mảng lớn hơn hoặc bằng 4, (tương tự như ngày 13, 17 của dòng 17). Lúc này kết quả của phép so sánh là {true,true}
2. Số trong mảng chỉ lớn hơn hoặc bằng 2, (tương tự như ngày 2, 0 của dòng 17). Lúc này kết quả của phép so sánh là {false,true}
3. Nhỏ hơn 2, Lúc này kết quả của phép so sánh là {false,false}
Sau đó lấy mảng vừa so sánh được nhân lần lượt theo cột với {1,1}.
TH 1: {true,true}*{1,1} = {1,1}. Tổng = 2 theo yêu cầu >=4 trả kết quả 2.
TH 2: {false,true}*{1,1} = {0,1}. Tổng = 1 theo yêu cầu >=2 trả kết quả 1.
TH 3: {false,false}*{1,1} = {0,0}. Tổng = 0 theo yêu cầu <2 trả kết quả 0.
Cuối cùng dùng sumproduct để cộng các thành phần lại là ra kết quả yêu cầu.

 

[nttrung_proneu]

Để mình trả lời hộ anh Hiếu:
1. Các hàm dạng sum sẽ bỏ qua dữ liệu không phải là số, nên phải sử dụng -- để biến dữ liệu từ dạng chuỗi (kết quả hàm text luôn là dữ liệu kiểu chuỗi) về số.
2. Nếu sử dụng hàm sum mà không phải sumif, nó sẽ tính tổng tất cả các thành phần mà hàm offset trả về, rồi mới tính tiếp đến điều kiện của hàm text. do đó kết quả sẽ luôn là 2 hoặc 1 hoặc 0. Khi dùng sumif như trên, kết quả sẽ trả về dạng mảng. Hàm text sẽ so sánh các phần tử của mảng với điều kiện "[>=4]\2;[>=2]\1;\0", trả về kết quả theo yêu cầu.
Nếu muốn sử dụng sum trong trường hợp này, bạn có thể dùng subtotal với tham số 9.
SUMPRODUCT(--TEXT(SUBTOTAL(9,OFFSET(E5,,ROW($1:$31)*5-4,,2)),"[>=4]\2;[>=2]\1;\0"))
Anh @HieuCD có bổ sung thêm gì không ạ?

=SUMPRODUCT((SUBTOTAL(9,OFFSET(E5,,ROW($1:$31)*5-4,,2))>={4,2})*{1,1})

Có thể dùng công thức như trên nhé.
Giải thích: Hàm subtotal tạo 1 mảng như cột HE đến II trong bảng của bạn.
Sau đó lấy mảng này lần lượng so sánh với 4 và 2. Có 3 trường hợp xảy ra.
1. Số trong mảng lớn hơn hoặc bằng 4, (tương tự như ngày 13, 17 của dòng 17). Lúc này kết quả của phép so sánh là {true,true}
2. Số trong mảng chỉ lớn hơn hoặc bằng 2, (tương tự như ngày 2, 0 của dòng 17). Lúc này kết quả của phép so sánh là {false,true}
3. Nhỏ hơn 2, Lúc này kết quả của phép so sánh là {false,false}
Sau đó lấy mảng vừa so sánh được nhân lần lượt theo cột với {1,1}.
TH 1: {true,true}*{1,1} = {1,1}. Tổng = 2 theo yêu cầu >=4 trả kết quả 2.
TH 2: {false,true}*{1,1} = {0,1}. Tổng = 1 theo yêu cầu >=2 trả kết quả 1.
TH 3: {false,false}*{1,1} = {0,0}. Tổng = 0 theo yêu cầu <2 trả kết quả 0.
Cuối cùng dùng sumproduct để cộng các thành phần lại là ra kết quả yêu cầu.

Cảm ơn anh vì lời giải thích rất rõ ràng! Tháng mới chúc anh may mắn thuận lợi trong công việc cũng như trong cuộc sống! ^^

 

[nttrung_proneu]

Anh ơi cho em hỏi thêm một chút ạ, sao cùng công thức với 0 1 2 thì đúng mà em đổi thành 15000 18000 30000 như này lại sai nhỉ? (có phần lẻ trồi lên phía sau)

 

[Phuocam]

Nhiều công thức hàn lâm, cái này đơn giản nhất:

{=SUM(IF($F$4:$EZ$4="TC 1.5",(F5:EZ5+G5:FA5>=4)+(F5:EZ5+G5:FA5>=2)))}

.

 

[Phuocam]

Anh ơi cho em hỏi thêm một chút ạ, sao cùng công thức với 0 1 2 thì đúng mà em đổi thành 15000 18000 30000 như này lại sai nhỉ? (có phần lẻ trồi lên phía sau)

Dùng dấu \ trươc số 0:

=TEXT(B9,"[>=4]3\0\0\0\0;[>=2]18\0\0\0;\15\0\0\0")

.

 

[nttrung_proneu]

Dùng dấu \ trươc số 0:

=TEXT(B9,"[>=4]3\0\0\0\0;[>=2]18\0\0\0;\15\0\0\0")

.

Ồ hay quá, cảm ơn anh nhiều!

 

[longtay1111]

Dùng dấu \ trươc số 0:

=TEXT(B9,"[>=4]3\0\0\0\0;[>=2]18\0\0\0;\15\0\0\0")

.

Cách dùng hàm Text của bác thật là siêu việt, chưa bao giờ thấy. Cả ngày hôm qua em lên GG tìm cách dùng hàm TEXT như của bác mà không sao tìm được. Nếu bác có đường Links hoặc tài liệu về hàm Text với cách sử dụng như trên thì cho em xin được không ạ để em hiểu và ứng dụng cho nhiều trường hợp khác, chứ em lên Google tìm mờ mắt cả ngày mà không có cách sử dụng hàm Text như vậy.

Em chân thành cảm ơn bác rất nhiều.

 

[bebo021999]

Nếu bác có đường Links hoặc tài liệu về hàm Text với cách sử dụng như trên thì cho em xin được không ạ để em hiểu và ứng dụng cho nhiều trường hợp khác, chứ em lên Google tìm mờ mắt cả ngày mà không có cách sử dụng hàm Text như vậy.

Xem cô thư ký của mình giải thích nè:

 

[longtay1111]

Xem cô thư ký của mình giải thích nè:


View attachment 292957

Dạ, em hiểu rồi ạ, em chân thành cảm ơn bác rất nhiều.