1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Tìm hiểu thêm.

SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện trong excel

Thảo luận trong 'Tin học căn bản' bắt đầu bởi admin, 15/5/09. Trả lời: 0 Xem: 15,502.

Chia sẻ trang này

  1. admin

    admin Administrator
    Thành viên BQT Administrator Super Moderator New Member

    Tham gia ngày:
    8/5/09
    Bài viết:
    3,019
    Đã được thích:
    9
    Mình xin phân tích cách dùng SumProduct và Công thức mảng.

    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.

    Thân chào!
     
    Tags: Không có

Chia sẻ trang này

Users Viewing Thread (Users: 0, Guests: 0)