Tính tổng với rất nhiều điều kiện @@

[Srosro]

Xin chào các PRO

Em có 1 vấn đề lớn khi tính tổng theo nhiều điều kiện liên quan

Mà em không ứng dụng Sumifs trong trường hợp này được

Mong các anh chị pro giúp em nhé. Xin Cảm Ơn.

Bảng em làm việc thì nó lằng nhăng hơn thế này 1 chút nhưng em tóm lược vấn đề thì như ở bên dưới (Em làm chấm công)

Ở cột loại 1: kết quả trả về cần là nếu Thường là A và Đêm có giá trị >0 thì tính tổng số thời gian của tất cả các ngày đó
Ở cột loại 2: kết quả trả về cần là nếu Thường là A và Đêm có giá trị =0 thì tính tổng số thời gian của tất cả các ngày đó
Ở cột loại 3: kết quả trả về cần là nếu Thường là B thì tính tổng số thời gian của tất cả các ngày đó

Em có gửi file đính kèm

 

[batman1]

Ở cột loại 1: kết quả trả về cần là nếu Thường là A và Đêm có giá trị >0 thì tính tổng số thời gian của tất cả các ngày đó
Ở cột loại 2: kết quả trả về cần là nếu Thường là A và Đêm có giá trị =0 thì tính tổng số thời gian của tất cả các ngày đó
Ở cột loại 3: kết quả trả về cần là nếu Thường là B thì tính tổng số thời gian của tất cả các ngày đó

Nếu đúng cấu trúc như thế thì:
C3

=SUMIFS(G3:CS3,F3:CR3,"A",H3:CT3,">0")

D3

=SUMIFS(G3:CS3,F3:CR3,"A",H3:CT3,"")

E3

=SUMIF(F3:CR3,"B",G3)

Kéo xuống dưới

Các công thức chỉ tính cho dữ liệu 1 tháng. Nếu là tính cho vd. cả năm thì thay đổi thích hợp CR3, CS3 và CT3.

 

[VetMini]

Chúc mừng bác lên Pi A Ô rồi nhé

 

[Srosro]

Nếu đúng cấu trúc như thế thì:
C3

=SUMIFS(G3:CS3,F3:CR3,"A",H3:CT3,">0")

D3

=SUMIFS(G3:CS3,F3:CR3,"A",H3:CT3,"")

E3

=SUMIF(F3:CR3,"B",G3)

Kéo xuống dưới

Các công thức chỉ tính cho dữ liệu 1 tháng. Nếu là tính cho vd. cả năm thì thay đổi thích hợp CR3, CS3 và CT3.

Đa tạ bác,

Nhưng nếu sẵn lòng bác có thể giải thích nguyên lý của em nó được không ạ?

Cái này nó liên quan đến mảng phải không bác

Em cũng muốn học luôn.

Thực sự em không nghĩ nó lại vẫn dùng Sumif và chỉ cần sửa lại 1 chút về vùng như thế là được đâu ạ?

nhưng đa tạ bác rất nhiều khi đã giúp em giải quyết được vấn đề <3

 

[batman1]

Đa tạ bác,

Nhưng nếu sẵn lòng bác có thể giải thích nguyên lý của em nó được không ạ?

Cái này là SUMIFS bình thường mà. sum_range và criteria_range chỉ phải thỏa mẵn điều kiện là có cùng số dòng và số cột. Thế thôi. Chúng không bắt buộc phải "bắt đầ và kết thúc trong cùng một cột". Chúng không buộc phải nằm ở 2 dòng hay 2 cột khác nhau. Vị trí tương đối của chúng so với nhau là hoàn toàn tùy ý.
Vd. công thức cho C3. Trong criteria_range F3:CR3 thì cứ cách 3 cột mới thỏa điều kiện = "A" - các cột có chỉ số 3k+1 với k = 1, 2, ..., trong criteria_range H3:CT3 cũng cứ cách 3 cột mới có 0 hoặc > 0 - các cột có chỉ số 3m+1 với m = 1, 2, .... Vậy những cột thỏa cả 2 đk là "A" và ">0", cũng là những cột cần SUM, cũng có chỉ số 3n+1, với n là những số tự nhiên không liên tục. Tất nhiên chỉ số cần SUM 3n+1 là tính cho G3:CS3, chỉ số 3n+1 thỏa 2 đk là tính cho F3:CR3 và H3:CT3. Khó hiểu ở đây?

Chắc bạn quen với trường hợp các range nằm ở các cột khác nhau và bắt đầu từ cùng 1 dòng, hoặc ở các dòng khác nhau bắt đầu từ cùng 1 cột.

 

[Srosro]

Cái này là SUMIFS bình thường mà. sum_range và criteria_range chỉ phải thỏa mẵn điều kiện là có cùng số dòng và số cột. Thế thôi. Chúng không bắt buộc phải "bắt đầ và kết thúc trong cùng một cột". Chúng không buộc phải nằm ở 2 dòng hay 2 cột khác nhau. Vị trí tương đối của chúng so với nhau là hoàn toàn tùy ý.
Vd. công thức cho C3. Trong criteria_range F3:CR3 thì cứ cách 3 cột mới thỏa điều kiện = "A" - các cột có chỉ số 3k+1 với k = 1, 2, ..., trong criteria_range H3:CT3 cũng cứ cách 3 cột mới có 0 hoặc > 0 - các cột có chỉ số 3m+1 với m = 1, 2, .... Vậy những cột thỏa cả 2 đk là "A" và ">0", cũng là những cột cần SUM, cũng có chỉ số 3n+1, với n là những số tự nhiên không liên tục. Tất nhiên chỉ số cần SUM 3n+1 là tính cho G3:CS3, chỉ số 3n+1 thỏa 2 đk là tính cho F3:CR3 và H3:CT3. Khó hiểu ở đây?

Chắc bạn quen với trường hợp các range nằm ở các cột khác nhau và bắt đầu từ cùng 1 dòng, hoặc ở các dòng khác nhau bắt đầu từ cùng 1 cột.

Giờ em hiểu tại sao các bác viết code VBA ầm ầm rồi,

Các bác toán học thật là cao siêu =))~

Bác nói em không hiểu hẳn nhưng cũng hiểu mang máng rồi ạ

Em VÔ CÙNG cảm ơn ạ

Bài đã được tự động gộp: 4/3/20

Chắc bạn quen với trường hợp các range nằm ở các cột khác nhau và bắt đầu từ cùng 1 dòng, hoặc ở các dòng khác nhau bắt đầu từ cùng 1 cột.

Cái này thì đúng luôn rồi ạ. Nên lúc em làm em bị cấn quá

 

[batman1]

Bác nói em không hiểu hẳn nhưng cũng hiểu mang máng rồi ạ

Tôi thử giải thích lại.
SUMIFS chỉ yêu cầu sum_range và criteria_range có cùng số cột và cùng số dòng. sum_range và criteria_range có thể ở bất kỳ chỗ nào trên sheet, độc lập, riêng rẽ với nhau. Vì vị trí của chúng không quan trọng, cái quan trọng là "mảng" giá trị của chúng.

Excel sẽ làm như sau:
1. lấy những ô đầu tiên của những criteria_range, bất luận những criteria_range này nằm ở đâu trên sheet, và kiểm tra xem chúng có thỏa điều kiên hay không. Nếu thỏa thì lấy ô đầu tiên của sum_range vào tổng.
2. lấy những ô thứ 2 của những criteria_range, bất luận những criteria_range này nằm ở đâu trên sheet, và kiểm tra xem chúng có thỏa điều kiên hay không. Nếu thỏa thì lấy ô thứ 2 của sum_range vào tổng.
3. lấy những ô thứ 3, 4... của những criteria_range, bất luận những criteria_range này nằm ở đâu trên sheet, và kiểm tra xem chúng có thỏa điều kiên hay không. Nếu thỏa thì lấy ô thứ 3, 4, ... của sum_range vào tổng. Cứ thế cho tới ô cuối cùng.

Ở trên khi nói tới các ô 1, 2, 3, 4, ... là nói tới những ô ở cùng vị trí so với ô ở góc trái bên phải của mỗi range.

Trong trường hợp công thức ở C3

=SUMIFS(G3:CS3,F3:CR3,"A",H3:CT3,">0")

1. lấy ô đầu tiên của F3:CR3 và H3:CT3, tức lấy giá trị của F3 và H3, và kiểm tra xem có thỏa điều kiện F3 = A và H3 > 0 hay không. Nếu thỏa thì lấy ô đầu tiên của G3:CS3, tức lấy giá trị của G3, vào tổng.

2. lấy ô thứ 2 của F3:CR3 và H3:CT3, tức lấy giá trị của G3 và I3, và kiểm tra xem có thỏa điều kiện G3 = A và I3 > 0 hay không. Nếu thỏa thì lấy ô thứ 2 của G3:CS3, tức lấy giá trị của H3, vào tổng.

3. lấy ô thứ 3 của F3:CR3 và H3:CT3, tức lấy giá trị của H3 và J3, và kiểm tra xem có thỏa điều kiện H3 = A và J3 > 0 hay không. Nếu thỏa thì lấy ô thứ 3 của G3:CS3, tức lấy giá trị của I3, vào tổng.

4. cứ như thế cho tới ô cuối cùng: lấy ô cuối cùng của F3:CR3 và H3:CT3, tức lấy giá trị của CR3 và CT3, và kiểm tra xem có thỏa điều kiện CR3 = A và CT3 > 0 hay không. Nếu thỏa thì lấy ô cuối cùng của G3:CS3, tức lấy giá trị của CS3, vào tổng.

Từ trên bạn thấy là vị trí tương đối của sum_range và criteria_range với nhau không quan trọng. Và chúng có thể nằm ở bất cứ nơi đâu. Chúng chỉ phải thỏa điều kiện là có cùng số dòng và cùng số cột.

Chính vì vị trí của 3 range là không quan trọng, chỉ những giá trị của 3 range đó mới quan trọng, nên nếu có khó khăn về hình dung thì bạn có thể: copy các giá trị của F3:CR3 sang sheet mới hoặc cùng sheet bắt đầu từ A1, copy các giá trị của H3:CT3 sang sheet mới hoặc cùng sheet bắt đầu từ A2, copy các giá trị của G3:CS3 sang sheet mới hoặc cùng sheet bắt đầu từ A3. Bây giờ thì A1:CM1 là range cho điều kiện "A", A2:CM2 là range cho điều kiện ">0", và A3:CM3 là range cho tổng.