[HỎI] Tính tổng có điều kiện này thì dùng công thức nào? (1 người xem)

[quoc_huy1702]

mình có vấn đề như hình bên dưới

Nhưng nghĩ mãi chưa ra làm sao để có kết quả là 1050

 

[mhung12005]

mình có vấn đề như hình bên dưới

Nhưng nghĩ mãi chưa ra làm sao để có kết quả là 1050

Với dữ liệu như vầy thì bạn dùng công thức này cho ô E15:

=SUMPRODUCT((D414<>D313)*(E4:E14))

 

[quoc_huy1702]

Bạn giải thích rõ nghĩa hơn về công thức này dùm mình với, Mình chưa hiểu tại sao lại phải là lệch cột so sánh D414<>D313

SUMPRODUCT((D414<>D313)*(E4:E14))

 

[mhung12005]

Bạn giải thích rõ nghĩa hơn về công thức này dùm mình với, Mình chưa hiểu tại sao lại phải là lệch cột so sánh D414<>D313

Làm vậy để tìm ra dòng đầu tiên trong nhóm nào đó trong trường số phiếu của bạn.
VD: Dòng đầu tiên trong nhóm 1 trường số phiếu là dòng 1 (D4), dòng đầu tiên trong nhóm 2 trường số phiếu là 5 (D8)... nhóm 3, 4 tương tự như vậy.

 

[ndu96081631]

Với dữ liệu như vầy thì bạn dùng công thức này cho ô E15:

Dùng cái này cho chắc:

=SUMPRODUCT(1/COUNTIF(D4:D14,D4:D14),E4:E14)

Dữ liệu bố trí lộn xộn cũng đúng luôn

 

[0167767]

Tôi thấy công thức của bạn hay tuyệt. Bạn giải thích ý nghĩa của con số 1 là sao, tại sao có con số 1 đó mà không phải là số 2,3,4...Cảm ơn.

 

[quoc_huy1702]

Mình vừa thấy 1 lỗi mà công thức này bỏ sót

Trong trường hợp các dòng trong khoảng mà trống thì ko tính dc;
Trong thực tế mình có 1 số dòng ở giữa như vậy.
Vậy phải sửa công thức như thế nào cho đúng trong những trường hợp như hình trên

 

[tungtung92]

Mình vừa thấy 1 lỗi mà công thức này bỏ sót

Trong trường hợp các dòng trong khoảng mà trống thì ko tính dc;
Trong thực tế mình có 1 số dòng ở giữa như vậy.
Vậy phải sửa công thức như thế nào cho đúng trong những trường hợp như hình trên

cách của m nè,mong b dễ hiểu,thanks

 

[mhung12005]

Mình vừa thấy 1 lỗi mà công thức này bỏ sót

Trong trường hợp các dòng trong khoảng mà trống thì ko tính dc;
Trong thực tế mình có 1 số dòng ở giữa như vậy.
Vậy phải sửa công thức như thế nào cho đúng trong những trường hợp như hình trên

Bạn dùng thử công thức này xem sao:

=SUM((IFERROR((1/COUNTIF($D$4:$D$14,$D$4:$D$14))*($D$4:$D$14<>""),0))*($E$4:$E$14))

Kết thúc Ctrl + Shift + Enter

 

[ndu96081631]

Bạn dùng thử công thức này xem sao:



Kết thúc Ctrl + Shift + Enter

Chi cho cực vậy? Tôi làm vầy:

=SUMPRODUCT(1/COUNTIF(D4:D15,D4:D15[COLOR=#ff0000]&""[/COLOR]),E4:E15)

 

[siwtom]

Tôi thấy công thức của bạn hay tuyệt. Bạn giải thích ý nghĩa của con số 1 là sao, tại sao có con số 1 đó mà không phải là số 2,3,4...Cảm ơn.

Cái này là mẹo thôi. Nhưng tất nhiên nó có "cơ sở lý luận"
Ta xét ví dụ cụ thể của bạn, th tổng quát tương tự.
Giả sử k1, k2, k3, k4 là số lần xuất hiện của số phiếu 1,2, 3,4 trong vùng D414, còn "số tiền chi" tương ứng là s1, s2, s3, s4.
Bạn cần tính s = s1 + s2 + s3 + s4.
Ta có sn = 1*sn = (1 / kn + 1 / kn + ... + 1 / kn)*sn (có kn phân số)
=> s = (1 / k1)*s1 + (1 / k1)*s1 + ... + (1 / k1)*s1 + ... +
(1 / k4)*s4 + (1 / k4)*s4 + ... + (1 / k4)*s4
Nhìn vào công thức trên thì bạn thấy về giá trị thì s = SUMPRODUCT(array1;E4:E14)
Với array1 có cấu trúc như sau:
1. Có cùng kích thước với E4:E14, tức cùng kích thước với D414
2. Giá trị tại ô tương ứng với sn là 1 / kn
Tức trong th của bạn thì array1 là {0,25;0,25;0,25;0,25;0,333;0,333;0,333;0,333;0,333;0,333;1}
Hay nói cách khác thì thay cho array1 thì bạn truyền vào vd. F4:F14 và các ô F4 --> F14 có các giá trị lần lượt là 0,25;0,25;0,25;0,25;0,333;0,333;0,333;0,333;0,333;0,333;1.
Nếu lúc này bạn có ở ô F15: = SUMPRODUCT(F4:F14;E4:E14) thì giá trị của E15 và F15 là như nhau và đều bằng s.
Bây giờ nếu bạn theo dõi quá trình tính của Excel thì bạn thấy trong các bước trung gian Excel có lập một array {0,25;0,25;0,25;0,25;0,333;0,333;0,333;0,333;0,333;0,333;1}.
Bạn chọn ô E15 --> mở thẻ Formulas --> mục Formula Auditing chọn biểu tượng fx - Evaluate formula --> click nhiều lần nút Evaluate bạn sẽ thấy hình 2.jpg

View attachment 87238

tức có array {0,25;0,25;0,25;0,25;0,333;0,333;0,333;0,333;0,333;0,333;1}
Nhưng trước đó thì Excel tạo một array {4;4;4;4;3;3;3;3;3;3;1} - hình 1.jpg

View attachment 87239

Tóm lại trong quá trình tính công thức có chứa 1 / COUNTIF thì Excel:
a. tạo array trung gian từ array nguồn mà tại ô có giá trị x thì thay bằng số lần xuất hiện của nó (kn)
b. tạo tiếp array từ array vừa tạo và thay giá trị mỗi ô (kn) bằng 1 / giá trị ô đó (1 / kn)
c. array kết quả trung gian này được dùng để tính kết quả cuối cùng.
Tức giá trị cuối cùng là =SUMPRODUCT(array_ketqua_trunggian;E4:E14)
= [(1 / k1)*s1 + (1 / k1)*s1 + ... + (1 / k1)*s1] + ... +
[(1 / kn)*sn + (1 / kn)*sn + ... + (1 / kn)*sn]
(ta nhóm các tích nhân với s1, s2, s3, s4 với nhau)
= k1*(1 / k1)*s1 + ... + kn*(1 / kn)*sn = s1 + ... + sn = s