Em muốn hỏi hàm SUMIF cho bảng tính tổng hợp (1 người xem)

[kutool]

em có bảng tính tổng hợp sau, nghĩ mãi mà chưa có lời giải hay (Vì ở bảng tính này cần 2 điều kiện là Mã NV và Tháng), xin nhờ các cao thủ

Thanks !

 

[dat_butmuc]

em có bảng tính tổng hợp sau, nghĩ mãi mà chưa có lời giải hay (Vì ở bảng tính này cần 2 điều kiện là Mã NV và Tháng), xin nhờ các cao thủ

Thanks !

Dùng tạm cái này;
Tại ô H3

=SUMPRODUCT(($A$2:$A$10=$G3)*($C$2:$C$10=H$2)*($D$2:$D$10))

Rồi fill rang phải, xuống...

 

[MinhCong]

em có bảng tính tổng hợp sau, nghĩ mãi mà chưa có lời giải hay (Vì ở bảng tính này cần 2 điều kiện là Mã NV và Tháng), xin nhờ các cao thủ

Thanks !

Bạn dùng hàm SUMPRODUCT nhé!

H3 =SUMPRODUCT(($A$2:$A$10=$G3)*($C$2:$C$10=H$2);$D$2:$D$10)

Thêm 1 hàm SUM kết hợp IF nữa đây (Đây là công thức mảng, khi nhập xong công thức kết thúc bằng tổ hợp phím Ctrl+Shift+Enter)

H3 =SUM(IF(($A$2:$A$10=$G3)*($C$2:$C$10=H$2);$D$2:$D$10;""))

 

[kutool]

Thanks mọi người đã giúp đỡ iem !

 

[le tin]

Bạn có thể dùng hàm SUMPRODUCT tại ô H3 và kéo công thức đến các ô cần tính
=SUMPRODUCT(1*($A$2:$A$100=$G3);1*($C$2:$C$100=H$1);$D$2:$D$100)

Lần sau nên gởi file excel lên nhờ giúp , đừng gởi bằng ảnh như vậy
Chúc vui

 

[ndu96081631]

Dựa vào hình minh họa, ta thấy dữ liệu đã được sort trước theo tháng, vì thế hoàn toàn có thể dùng SUMIF (kết hợp với name động)
Các bạn làm thử xem ---> Vì phương pháp này cho tốc độ nhanh hơn SUMPRODUCT
Ngoài ra xin lưu ý: Nếu đây là bài tập thì tôi không nói làm gì, còn như đây là dữ liệu thật tại cơ quan thì khuyên bạn nên dùng PivotTalbe, 1 phát ra kết quả luôn (khỏi công thức)

 

[concogia]

khuyên bạn nên dùng PivotTalbe, 1 phát ra kết quả luôn

Thầy Ndu ơi cho mình hỏi: PivotTalbe là ....thuốc gì và ....bán ở đâu mà : 1 phát ra kết quả luôn
Tài thật
Bài này bị xóa chắc
Mod thông cảm để chờ Thầy Ndu trả lời xong cho mình hãy xóa
Thân

 

[ndu96081631]

Thầy Ndu ơi cho mình hỏi: PivotTalbe là ....thuốc gì và ....bán ở đâu mà : 1 phát ra kết quả luôn
Tài thật
Bài này bị xóa chắc
Mod thông cảm để chờ Thầy Ndu trả lời xong cho mình hãy xóa
Thân

Là cái này nè

:


PivotTable nằm trong menu Data ấy
Ẹc... Ẹc...

 

[le tin]

Dựa vào hình minh họa, ta thấy dữ liệu đã được sort trước theo tháng, vì thế hoàn toàn có thể dùng SUMIF (kết hợp với name động)

Name động thì phải là 12 ( mỗi tháng 1 cái) , không nghĩ ra cách làm ít hơn , bạn NDU có cách gì không

 

[ndu96081631]

Name động thì phải là 12 ( mỗi tháng 1 cái) , không nghĩ ra cách làm ít hơn , bạn NDU có cách gì không

Gì mà đến 12 name ghê thế!
Dựa trên dữ liệu ban đầu của tác giả, tôi làm như sau:
- Đặt con trỏ tại bất ký nơi đâu trong cột H (điều này cực kỳ quan trọng)
-Đặt 1 name

Ma =OFFSET($A$2,MATCH(H$2,$C$2:$C$1000,0)-1,,COUNTIF($C$2:$C$1000,H$2),)

- Công thức cho cell H3

=SUMIF(Ma,$G3,OFFSET(Ma,,3))

- Kéo fill sang phải và xuống dưới
Dù là 1 tháng hay 12 tháng thì cũng chỉ cần 1 name như vậy thôi!

 

[nhatle_1404]

Bảng tính tổng hợp

Mình thì làm thêm 1 cột mã tổng hợp của bảng 1 rồi qua bảng 2 mình dùng hàm sumif. Chi tiết xem tệp đính kèm

 

[nhatle_1404]

Bảng tính tổng hợp

Mình thì làm thêm 1 cột mã tổng hợp của bảng 1 rồi qua bảng 2 mình dùng hàm sumif. Chi tiết như sau:
Chèn thêm 1 cột mới sau cột mã nhân viên và lập công thức C2=A2&B2
A B C D
1 MNV T MTH Số tiền
2 A 1 A1 100
3 B 1 B1 200
4 D 1 D1 150
5 A 2 A2 150
6 D 2 D2 250
7 D 2 D2 200
8 B 2 B2 100
9 C 2 C2 100
10 C 2 C2 150
11Tổng cộng 1400
Bảng 2 dùng hàm sumif:
I3=SUMIF($C$2:$D$10,$H3&I$2,$D$2:$D$10)

 

[ndu96081631]

Mình thì làm thêm 1 cột mã tổng hợp của bảng 1 rồi qua bảng 2 mình dùng hàm sumif. Chi tiết xem tệp đính kèm

Thêm cột phụ là chuyện ai cũng biết, có điều nếu dữ liệu của bạn cở 20.000 dòng, bạn phải thêm 20.000 cell công thức à?
Hic... Nó tăng thêm dung lượng file lên chắc cở 10 MB

 

[nhatle_1404]

Có lẻ vậy. Nhưng tại em quen dùng hàm sumif và dữ liệu không nhiều nên cũng không chú ý đến vấn đề đó

 

[ndu96081631]

Có lẻ vậy. Nhưng tại em quen dùng hàm sumif và dữ liệu không nhiều nên cũng không chú ý đến vấn đề đó

Thì tôi cũng dùng SUMIF cho bài này đấy thôi (bài số #10)
Có điều tôi dùng cách đặt name động chứ không dùng cột phụ! Cách này có 2 điểm lợi
- Như ta đã biết thì SUMIF bao giờ cũng có tốc độ tính toán nhanh hơn SUMPRODUCT ---> Đây là cái lợi thứ nhất
- Dùng name động thay vì cột phụ sẽ không làm cho file tăng thêm dung lượng ---> Đây là cái lợi thứ hai
Mấu chốt vấn đề ở đây là:
- Với n điều kiện thì sau khi qua 1 name động ta sẽ còn n-1 điều kiện ---> Ví dụ: bài toán tính tổng 4 điều kiện, ta dùng 3 name sẽ còn lại 1 điều kiện
- Dữ liệu bắt buộc phải được sort trước thì mới cho kết quả chính xác
Ngoài ra:
- Dữ liệu được sort trước luôn cho tốc độ tính toán nhanh hơn dữ liệu lộn xộn (đương nhiên y như xếp hàng đàng hoàng sẽ đi nhanh hơn thay vì chen lấn)

 

[nguyen053kn1]

anh có thể giải thích dùm em đoạn công thức của anh ko? =OFFSET(Sheet1!$A$2,MATCH(Sheet1!H$2,Sheet1!$C$2:$C$1000,0)-1,,COUNTIF(Sheet1!$C$2:$C$1000,Sheet1!H$2),)
Thanks anh nhiều

 

[Hong.Van]

anh có thể giải thích dùm em đoạn công thức của anh ko? =OFFSET(Sheet1!$A$2,MATCH(Sheet1!H$2,Sheet1!$C$2:$C$1000,0)-1,,COUNTIF(Sheet1!$C$2:$C$1000,Sheet1!H$2),)
Thanks anh nhiều

Cái này, trước hết bạn fải hiểu các hàm: OFFSET(), MATCH(), COUNTIF()
Sau đó tách cthức ra, ví dụ:
MATCH(Sheet1!H$2,Sheet1!$C$2:$C$1000,0)-1 -> cho kết quả?
hoặc COUNTIF(Sheet1!$C$2:$C$1000,Sheet1!H$2) -> cho kết quả?
Bạn có thể tham khảo các hàm này ở đây
http://www.giaiphapexcel.com/forum/...-Lookup-Function-Các-Hàm-dò-tìm-và-tham-chiếu