Tìm kiếm câu trả lời trước khi đặt câu hỏi
Kết quả 1 đến 3 của 3

Chủ đề:
Cách sử dụng hàm SUMPRODUCT trong excel

  1. #1
    Gia nhập
    03-05-2012
    Bài gửi
    14
    Cảm ơn
    0
    Được cảm ơn 0 trong 0 bài

    Cách sử dụng hàm SUMPRODUCT trong excel

    I love hiephoiketoan.vn:3D_23:

  2. #2
    Gia nhập
    05-12-2011
    Bài gửi
    491
    Cảm ơn
    11
    Được cảm ơn 56 trong 36 bài
    Trích dẫn Gửi bởi thuhuong87 Xem bài viết
    Vì em đang tập tành làm excel nên có nhiều thứ em không biết, mong anh chị chỉ giúp cách dùng hàm sumproduct trong excel với ạ!
    Hi em,

    Hàm SumProduct:
    Cấu trúc SUMPRODUCT(array1,array2,array3, ...)
    Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,...

    Phép tính này cho phép chúng ta tính tổng của tích array1*array2*array3* ...array30.
    VD: A: Số lượng; B: Đơn giá
    A1 =2 B1=20 C1="Cam" D1="Giống lai"
    A2 =3 B2=10 C2="Bưởi" D2="Không"
    A3 =4 B3=25 C3="Cam" D3="Không"

    Bây giờ cần tính doanh thu của các loại hoa quả
    array1=A1:A3
    array2=B1:B3
    Công thức =SumProduct(A1:A3, B1:B3) = 170
    Bản chất công thức làm việc như thế này =A1*B1+A2*B2+A3*B3 kết quả là 170
    Nhắc lại về phép tính logic:
    Giá trị kiểu logic chỉ cho ra 1 trong 2 giá trị là TRUE/1, FALSE/0
    Phép toán logic:<, >, <>, =, >=, <=, Not()
    VD:
    2>3=False
    3>1=True
    4>3=True
    *) Logic và - AND
    =(2>3)*(3>1)*(4>3)=False*True*True=0*1*1=False/0 tương đương với hàm AND(2>3,3>1,4>3). Ít nhất một logic=False thì kết quả sẽ là False hay 0.
    * Logic hoặc - OR
    =(2>3)+(3>1)+(4>3)=False+True+True=0+1=True/1 tương đương với hàm OR(2>3,3>1,4>3). Ít nhất một logic=True thì kết quả sẽ là True hay 1.
    Lưu ý tổng của các giá trị là True=True=1).

    *) Tính tổng có nhiều điều kiện:
    Cách 1: dùng SUMPRODUCT
    Tính tổng doanh thu của loại là "Cam"
    =SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam")) hoặc =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) = 140
    Công thức tính như sau:
    =A1*B1*(C1="Cam")+A2*B2*(C2="Cam")+A3*B3*(C3="Cam" )
    =2*20*True+3*10*False+4*25*True
    =2*20*1+3*10*0+4*25*1= 140
    Cách 2: dung Công thức mảng - "Formula Array"
    =Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))
    Kết thức nhẫn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
    Xét trên từng dòng trong mảng (array)
    dòng1: (c1="Cam")=true nên lấy A1*B1=2*20
    dòng2: (c2="Cam")=false nên lấy 0 (theo cách của lấy của hàm IF)
    dòng3: (c3="Cam")=true nên lấy A3*B3=4*25
    Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20+0+2*25=140. Nếu trong công thức là hàm khác hàm SUM thì cách tính sẽ theo hàm đó.

    Như vậy có 2 cách tính:

    =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) và
    =Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))

    *) Vậy tại sao không dùng là =SUM(A1:A3*B1:B3*(C1:C3="Cam"))
    mà phải dùng hàm =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) ?


    Các bạn nhớ lại cấu trúc của SUM là
    SUM(number1,number2, ...)
    Còn SUMPRODUCT là
    SUMPRODUCT(array1,array2,array3, ...)
    number <> array

    Nếu SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER là đúng vì đối số của nó phải là mảng - Array.

    Nếu công thức =SUM(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER kết quả là #VALUE! -lỗi vì A1:A3 là một array chứ không phải là một number.

    Nếu nhấn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
    Xét trên từng dòng trong mảng (array)
    dòng1: A1*B1*(c1="Cam")=2*20*True=2*20*1
    dòng2: A2*B2*(c2="Cam")=3*10*False=3*10*0
    dòng3: A3*B3*(c3="Cam")=2*25*True=4*25*1

    Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20*1+3*10*0
    +4*25*1=140.

    Vậy vẫn dùng được =SUM(A1:A3*B1:B3*(C1:C3="Cam")) với điều kiện nhấn tổ hợp phím CTRL+SHIFT+ENTER


    Như vậy đến đây chúng ta có có 3 cách tính:

    =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím ENTER
    =SUM(IF(C1:C3="Cam",A1:A3*B1:B3,0)) nhấn phím CTRL+SHIFT+ENTER
    =SUM(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím CTRL+SHIFT+ENTER

    Chúng có thể kết hợp rất nhiều điều kiện vào trong hàm thông qua phép toán logic nhân-và- And, cộng - hoặc - Or.

    *) Dùng hàm SUMPRODUCT hay dùng SUM kết hợp CTRL+SHIFT+ENTER đều cho ra được kết quả như nhau chính là do phép toán logic của bạn.
    *) Hàm SUMPRODUCT chỉ có thể tính tổng theo nhiều điều kiện
    *) Công thức mảng - Formula Array ngoài việc tính tổng có nhiều điều kiện còn làm rất nhiều phép tính khác do cách sử dụng hàm mà thôi.


    Peace

  3. #3
    Gia nhập
    16-06-2011
    Tuổi
    35
    Bài gửi
    49
    Cảm ơn
    1
    Được cảm ơn 0 trong 0 bài
    Trích dẫn Gửi bởi ddnguyen Xem bài viết
    Hi em,

    Hàm SumProduct:
    Cấu trúc SUMPRODUCT(array1,array2,array3, ...)
    Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,...

    Phép tính này cho phép chúng ta tính tổng của tích array1*array2*array3* ...array30.
    VD: A: Số lượng; B: Đơn giá
    A1 =2 B1=20 C1="Cam" D1="Giống lai"
    A2 =3 B2=10 C2="Bưởi" D2="Không"
    A3 =4 B3=25 C3="Cam" D3="Không"

    Bây giờ cần tính doanh thu của các loại hoa quả
    array1=A1:A3
    array2=B1:B3
    Công thức =SumProduct(A1:A3, B1:B3) = 170
    Bản chất công thức làm việc như thế này =A1*B1+A2*B2+A3*B3 kết quả là 170
    Nhắc lại về phép tính logic:
    Giá trị kiểu logic chỉ cho ra 1 trong 2 giá trị là TRUE/1, FALSE/0
    Phép toán logic:<, >, <>, =, >=, <=, Not()
    VD:
    2>3=False
    3>1=True
    4>3=True
    *) Logic và - AND
    =(2>3)*(3>1)*(4>3)=False*True*True=0*1*1=False/0 tương đương với hàm AND(2>3,3>1,4>3). Ít nhất một logic=False thì kết quả sẽ là False hay 0.
    * Logic hoặc - OR
    =(2>3)+(3>1)+(4>3)=False+True+True=0+1=True/1 tương đương với hàm OR(2>3,3>1,4>3). Ít nhất một logic=True thì kết quả sẽ là True hay 1.
    Lưu ý tổng của các giá trị là True=True=1).

    *) Tính tổng có nhiều điều kiện:
    Cách 1: dùng SUMPRODUCT
    Tính tổng doanh thu của loại là "Cam"
    =SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam")) hoặc =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) = 140
    Công thức tính như sau:
    =A1*B1*(C1="Cam")+A2*B2*(C2="Cam")+A3*B3*(C3="Cam" )
    =2*20*True+3*10*False+4*25*True
    =2*20*1+3*10*0+4*25*1= 140
    Cách 2: dung Công thức mảng - "Formula Array"
    =Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))
    Kết thức nhẫn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
    Xét trên từng dòng trong mảng (array)
    dòng1: (c1="Cam")=true nên lấy A1*B1=2*20
    dòng2: (c2="Cam")=false nên lấy 0 (theo cách của lấy của hàm IF)
    dòng3: (c3="Cam")=true nên lấy A3*B3=4*25
    Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20+0+2*25=140. Nếu trong công thức là hàm khác hàm SUM thì cách tính sẽ theo hàm đó.

    Như vậy có 2 cách tính:

    =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) và
    =Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))

    *) Vậy tại sao không dùng là =SUM(A1:A3*B1:B3*(C1:C3="Cam"))
    mà phải dùng hàm =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) ?


    Các bạn nhớ lại cấu trúc của SUM là
    SUM(number1,number2, ...)
    Còn SUMPRODUCT là
    SUMPRODUCT(array1,array2,array3, ...)
    number <> array

    Nếu SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER là đúng vì đối số của nó phải là mảng - Array.

    Nếu công thức =SUM(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER kết quả là #VALUE! -lỗi vì A1:A3 là một array chứ không phải là một number.

    Nếu nhấn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
    Xét trên từng dòng trong mảng (array)
    dòng1: A1*B1*(c1="Cam")=2*20*True=2*20*1
    dòng2: A2*B2*(c2="Cam")=3*10*False=3*10*0
    dòng3: A3*B3*(c3="Cam")=2*25*True=4*25*1

    Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20*1+3*10*0
    +4*25*1=140.

    Vậy vẫn dùng được =SUM(A1:A3*B1:B3*(C1:C3="Cam")) với điều kiện nhấn tổ hợp phím CTRL+SHIFT+ENTER


    Như vậy đến đây chúng ta có có 3 cách tính:

    =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím ENTER
    =SUM(IF(C1:C3="Cam",A1:A3*B1:B3,0)) nhấn phím CTRL+SHIFT+ENTER
    =SUM(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím CTRL+SHIFT+ENTER

    Chúng có thể kết hợp rất nhiều điều kiện vào trong hàm thông qua phép toán logic nhân-và- And, cộng - hoặc - Or.

    *) Dùng hàm SUMPRODUCT hay dùng SUM kết hợp CTRL+SHIFT+ENTER đều cho ra được kết quả như nhau chính là do phép toán logic của bạn.
    *) Hàm SUMPRODUCT chỉ có thể tính tổng theo nhiều điều kiện
    *) Công thức mảng - Formula Array ngoài việc tính tổng có nhiều điều kiện còn làm rất nhiều phép tính khác do cách sử dụng hàm mà thôi.


    Peace
    Bài này em đọc thấy quen quen, nhờ bác chỉ giúp em cách kết hợp giữa hàm sumproduct với offset với ạ
    Đàn ông thành đạt thường hư, đàn bà hư thường thành đạt

Thông tin về chủ đề này

Users Browsing this Thread

Có 1 người đang xem chủ đề. (0 Hội viên và 1 khách)

Các chủ đề ngẫu nhiên

  1. So sánh hàm sumif và hàm sumproduct trong excel
    Bởi thuhuong87 trong diễn đàn Excel
    Trả lời: 1
    Bài viết cuối: 27-05-12, 10:43 AM
  2. Sử dụng hàm tài chính trong excel
    Bởi DIAMOND trong diễn đàn Excel
    Trả lời: 0
    Bài viết cuối: 18-07-11, 07:49 PM
  3. Lấy giá trị cột từ phải qua trái trong excel
    Bởi ngoctuyen trong diễn đàn Excel
    Trả lời: 3
    Bài viết cuối: 30-05-11, 01:22 PM
  4. Sắp xếp ngày tháng trong phần mềm kế toán excel
    Bởi ut_nho trong diễn đàn Excel
    Trả lời: 1
    Bài viết cuối: 18-05-11, 01:27 PM
  5. Phím tắt thông dụng trong excel
    Bởi Kid1412 trong diễn đàn Excel
    Trả lời: 2
    Bài viết cuối: 22-04-11, 12:35 PM

Những Hội viên đã đọc chủ đề này : 5

Tag của Chủ đề này

Đánh dấu và chia sẻ lên các trang sau

Đánh dấu và chia sẻ lên các trang sau

Quyền viết bài

  • Bạn Không thể gửi Chủ đề mới
  • Bạn Không thể Gửi trả lời
  • Bạn Không thể Gửi file đính kèm
  • Bạn Không thể Sửa bài viết của mình
  •