Hàm SUMIF: Có thể nào kết hợp nhiều điều kiện vào Criteria?

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi Quỳnh, 7 Tháng tư 2005.

64,133 lượt xem

  1. Quỳnh

    Quỳnh Thành viên sơ cấp

    Bài viết:
    22
    Đã được thích:
    0
    Nơi ở:
    Hà Nội

    Ví du:̣
    Em có 1 bảng tính gồm các cột:
    Cột A là Tên mặt hàng
    Cột B là Tên người bán
    Cột C là Số tiền phải thanh toán cho người bán.

    Thì công thức để tính tổng số tiền phải thanh toán cho người bán X là:

    =SUMIF(B2:B100,"X",C2:C100)

    Nhưng nếu muốn tính tổng số tiền phải thanh toán cho người bán X về mặt hàng A, thì phải làm thế nào?


     
    Last edited: 7 Tháng tư 2005
    #1
  2. handung107

    handung107 Thành viên thân thiết

    Bài viết:
    576
    Đã được thích:
    13
    Nơi ở:
    VN
    #2
  3. levanduyet

    levanduyet Welcome

    Bài viết:
    535
    Đã được thích:
    11
    Giới tính:
    Nam
    Nơi ở:
    HCM
    Chị handung107 và các bạn nhỉ !
    Làm sao mình tổ họp lại box EFC để có thể tra tài liệu dễ dàng nhỉ? Hiện tại nhiều khi mình chỉ vào trang đầu tiên của EFC mà thôi!?
    Lê Văn Duyệt
     
    #3
  4. vinhnl

    vinhnl Thành viên sơ cấp

    Bài viết:
    2
    Đã được thích:
    0
    Nơi ở:
    Hanoi
    Bạn dùng công thức mảng như sau:

    =SUM(IF("A"&"X"=A2:A100&B2:B100,C2:C100)) và nhấn tổ hợp phím CTRL+SHIFT+ENTER

    Khi bạn quay lại ô vừa điền công thức bạn sẽ thấy:

    {=SUM(IF("A"&"X"=A2:A100&B2:B100,C2:C100))}

    Có thể giải thích công thức trên như sau:

    Cộng bên cột C2:C100 các Ci thỏa mãn tổ hợp Ci & Bi = "A" & "X"
     
    #4
  5. Quỳnh

    Quỳnh Thành viên sơ cấp

    Bài viết:
    22
    Đã được thích:
    0
    Nơi ở:
    Hà Nội
    Cảm ơn chị HanDung, em đã đọc theo đường chỉ dẫn của chị và làm được rồi:

    {=SUM((A2:A100="A")*(B2:B100="X")*C2:C100)}
    Hoặc dùng:
    {=SUMPRODUCT((A2:A100="A")*(B2:B100="X")*C2:C100)}

    Bạn vinhnl: Tôi đã làm thử theo cách của bạn nhưng không hiểu sao không được?

    Gửi các bác: Em cũng mong như bác Levanduyet nói, làm sao để có thể tra cứu tài liệu một cách nhanh nhất. Bởi nhiều lúc như em mới tham gia cũng chưa có thời gian để đọc hết các thảo luận, nên không biết chúng ở đâu. Hoặc có khi đọc mà không... để ý. Hìhìhì.
     
    #5
  6. handung107

    handung107 Thành viên thân thiết

    Bài viết:
    576
    Đã được thích:
    13
    Nơi ở:
    VN
    Công thức mảng như bạn vinhnl giới thiệu cho quỳnh là đún gđấy, không hiểu sao bạn làm không được ? Bạn xem lại nhé.
    Bạn có thể đặt tên cho mảng đó, cho công thức dễ nhìn hơn. TD : A2:A100 là TenMH, B2:B100 là TenNB, C2:C100 là SoTien.

    Công thức mảng có thể viết gọn lại thành :
    1/ {SUM((TenMH="A")*(TenNB="X")*SoTien)}
    2/SUMPRODUCT((TenMH="A")*(TenNB="X")*SoTien)
    3/{SUM((TenMH&TenNB="A"&"X")*SoTien)}
    4/{SUM(IF(TenMH&TenNB="A"&"X",SoTien))}
    Cặp dấu {}, bạn không tự ý nhập mà chỉ nhập những gì bên trong cặp dấu này, rồi nhấn Ctrl+shift+Enter, Excel sẽ hiểu đây là công thức mảng và tự động điền cặp dấu này vào cho bạn
     
    #6
  7. Quỳnh

    Quỳnh Thành viên sơ cấp

    Bài viết:
    22
    Đã được thích:
    0
    Nơi ở:
    Hà Nội
    Ơ, đúng rồi. Em vừa thử làm lại công thức như bạn vinhnl hướng dẫn thì được rồi ạ. Cảm ơn các bác!
    Như vậy là có 4 cách để tính tổng theo nhiều điều kiện.
     
    #7
  8. donjuan_acc

    donjuan_acc Thành viên hoạt động

    Bài viết:
    37
    Đã được thích:
    1
    Nơi ở:
    TP HCM
    Tra loi bai cua Quynh ve sumif ket hop nhieu criteria

    Su dung cong thuc mang rat hay, o day, toi bo sung them 1 cach don gian nay:
    Ban tao them 1 cot moi (sau do hide) ket hop 2 cot "mat hang-nguoi ban", roi dung cot nay lam dieu kien cho ham sumif.

    Vi du:
    Cot A- mat hang : X, Y, Z.
    Cot B- nguoi ban : A, B, C.
    Toi tao them:
    Cot C- ket hop : X-A, Y-B, Z-C.
    Luc do :
    Cot D toi dung sumif voi dieu kien la cot C.

    Vai y nho goi den ban, chuc ban va moi nguoi toi CN vui ve!
     
    Last edited: 10 Tháng tư 2005
    #8
  9. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    Phong cách công thức mảng của chị Dung rất hay, tuy nhiên MaiKa vẫn chưa lĩnh hội hết được. Quỳnh bảo rằng có 4 cách thực hiện cái việc "ấy" của Quỳnh, nhưng đó mới chỉ là 4 cách chị biết thôi nhé. MaiKa thì lại hay dùng công thức mảng theo phong cách sau (lấy theo đề bài của Quỳnh luôn nhé?!):

    =sum(if($a$2:$a$100="A";if($b$2:$b$100="X";$c$2:$c$100;0);0)

    P/S: Trong Excel càng ít phải tạo thêm cột càng tốt, vì cái đó chính là sự sử dụng linh hoạt các hàm. Còn nếu trong những trường hợp bất khả kháng thì mới nên tạo thêm.
     
    #9
  10. Quỳnh

    Quỳnh Thành viên sơ cấp

    Bài viết:
    22
    Đã được thích:
    0
    Nơi ở:
    Hà Nội
    Chào Donjuan acc: Bắt tay cái nào, vì có cùng ý tưởng "nhớn" giống Quỳnh (tạo thêm cột). Tôi rất (rất rất) hay phải tính tổng theo nhiều điều kiện. Có rất nhiều việc phải dùng đến nó, nhất là khi chúng tôi thí điểm chương trình kế toán mới, thường xuyên phải kiểm tra xem số liệu tổng hợp ra biểu báo cáo có đúng không. Ngoài ra, còn nhiều nhu cầu khác nữa, mà phần mềm kế toán chưa đáp ứng được.
    Tôi cũng thường tạo thêm 1 cột kết hợp nhiều điều kiện. Hìhìhì, nhưng mà vẫn thấy không ổn, tôi vẫn muốn làm theo cách "chính thống" cơ. Thế nên mới hỏi thăm ở khắp mọi nơi.

    Hihihi, cảm ơn Maika. Đây đúng là cách thứ 5 rồi. Cách này có vẻ dài hơn của chị Handung107, nhưng nó đúng là cách thứ 5.
     
    #10
  11. handung107

    handung107 Thành viên thân thiết

    Bài viết:
    576
    Đã được thích:
    13
    Nơi ở:
    VN
    Công thức mảng của MaiKa không sai, nhưng nên sử dụng công thức mảng theo các dạng trên, vì nếu kết hợp nhiều điều kiện OR, thì công thúc mảng của MaiKa sẽ không thể hiện được. Dạng công thức mảng dễ sử dụng nhất vẫn là :
    {SUM((TenMH="A")*(TenNB="X")*SoTien)}
    Hay :
    SUMPRODUCT((TenMH="A")*(TenNB="X")*SoTien)
    Trong trường hợp điều kiện là OR (tính tổng theo TenMH là A hay TenNB là "X"), công thức trên sẽ thay đổi dấu * thành dấu + :
    {SUM(((TenMH="A")+(TenNB="X"))*SoTien)}
    Hay :
    SUMPRODUCT(((TenMH="A")+(TenNB="X"))*SoTien)
     
    #11
  12. vutienduc

    vutienduc Thành viên sơ cấp

    Bài viết:
    5
    Đã được thích:
    0
    Nơi ở:
    Ha Noi
    Dùng mảng sẽ tốn nhiều bộ nhớ lắm, nên dùng sumproduct hay hơn
     
    #12
  13. SA_DQ

    SA_DQ Thành viên thân thiết

    Bài viết:
    431
    Đã được thích:
    37
    Nơi ở:
    HCM city
    Dùng Hàm DSum(...;...;......) cũng sẽ được, thật đấy!
    Cụ thể hơn như sau:
    =Dsum(CSDL,Chuan,KQua)
    CSDL: Các cột chứa CSDL (được đặt tên CSDL)
    Chuan: Giống như trường hợp trích lọc dữ liệu
    KQua: Ô chứa kết quả số liệu

    Thử đi, Sẽ dễ hơn chúng ta tưởng nhiều. :0frown:
     
    Last edited: 28 Tháng bảy 2005
    #13
  14. workman

    workman Thành viên hoạt động

    Bài viết:
    372
    Đã được thích:
    0
    Nơi ở:
    Ho Chi Minh
    Đúng quá. DSum hoặc trong trường hợp này là tiện nhất và cover được nhiều điều kiện.
     
    #14
  15. SA_DQ

    SA_DQ Thành viên thân thiết

    Bài viết:
    431
    Đã được thích:
    37
    Nơi ở:
    HCM city
    Cảm ơn WM quá khen!

    Nếu thích các bạn tím hiểu hết các hàm của nó cũng hay, Chúc tốt lành sẽ đến với mọi người! :dzo: :atom:
     
    Last edited: 22 Tháng tám 2005
    #15
  16. new AQ

    new AQ Thành viên sơ cấp

    Bài viết:
    1
    Đã được thích:
    0
    Nơi ở:
    Vietnam
    Sorry New AQ nhầm. Nhwng mà cũng có thể dùng hàm concatenate để tạo ra 1 đối số dùng cho Criteria được
     
    Last edited: 4 Tháng tám 2005
    #16
  17. workman

    workman Thành viên hoạt động

    Bài viết:
    372
    Đã được thích:
    0
    Nơi ở:
    Ho Chi Minh
    Thực ra đó là hàm mảng, không phải CSDL. Ờ mà thực ra cũng tương tự, gọi là "tiểu" CSDL cũng được. :)
     
    #17
  18. metwametwa

    metwametwa Thành viên hoạt động

    Bài viết:
    20
    Đã được thích:
    0
    Nơi ở:
    hcm
    =SUM(IF(INDIRECT($H$2)=$C13,1,0)*IF(INDIRECT($G$2)>=$J$7,1,0)*IF(INDIRECT($G$2)<=$L$7,1,0)*INDIRECT($J$2))
    Trời ơi, em mới biết trang web này và thay hay hay ghe, em dot Excel lam, Có Bác nào nhiều kinh nghiệm chỉ cho em hàm này có y nghĩa gì được k a? Cám ơn nhiều nhiều!
     
    Last edited: 6 Tháng tám 2005
    #18
  19. workman

    workman Thành viên hoạt động

    Bài viết:
    372
    Đã được thích:
    0
    Nơi ở:
    Ho Chi Minh
    Công thức bạn nêu không rõ ràng nên chỉ có thể bàn loạn sơ sơ thế này thôi:

    Bạn đã biết hàm SUM và IF rồi. Tôi chỉ có một vài comments về cách sử dụng chúng thôi.
    - Hàm SUM: Tôi không thấy vai trò hàm SUM ở đây để làm gì cả. Bạn bỏ luôn hàm SUM thì về nguyên tắc kết quả vẫn như vậy thôi.
    - Hàm IF: về nguyên tắc cũng không cần trong công thức này. Bạn có thể bỏ luôn hàm IF cho đỡ rườm rà. Công thức giản lược sẽ trông như sau

    =(INDIRECT($H$2)=$C13)*(INDIRECT($G$2)>=$J$7)*(INDIRECT($G$2)<=$L$7)*INDIRECT($J$2)

    - Hàm INDIRECT: chắc là bạn cũng biết rồi. Nhắc lại một chút thôi: đây là hàm tham chiếu từ 1 ô sang 1 ô khác. VD: Tại ô H2 tôi gõ A13, thì hàm INDIRECT(H2) của bạn ở trên sẽ tham chiếu đến giá trị của ô A13.

    Bây giờ tôi diễn nôm toàn bộ công thức nhé: Ô bạn đang xét sẽ có giá trị bằng với tham chiếu của ô J2 nếu thỏa tất cả các điều kiện:
    a/ INDIRECT($H$2)=$C13
    b/ INDIRECT($G$2)>=$J$7 và
    c/ INDIRECT($G$2)<=$L$7

    bất kỳ điều kiện nào ở trên không thỏa mãn, giá trị sẽ bằng 0.

    (nghe diễn nôm còn rùng rợn khó hiểu hơn tự đọc công thức :) )

    Thế nhé. Chúc bạn vui.
     
    #19
  20. bethuong

    bethuong Thành viên sơ cấp

    Bài viết:
    2
    Đã được thích:
    0
    Nơi ở:
    quận bình thạnh
    Cac ban oi!giúp mình làm hàm sum ì với nhé
     
    #20

Chia sẻ trang này