Sử dụng hàm SUMPRODUCT và Công thức mảng

Thảo luận trong 'Excel căn bản' bắt đầu bởi AnhExcel, 27/7/06.

  1. AnhExcel

    AnhExcel New Member Hội viên mới

    Được cảm ơn:
    0
    Điểm thành tích:
    0
    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.


    (St)
     
  2. AnhExcel

    AnhExcel New Member Hội viên mới

    Được cảm ơn:
    0
    Điểm thành tích:
    0
    ưu điểm của hàm SUMPRODUCT trong việc tính tổng nhiều điều kiện

    A / Bạn không cần nhấn tổ hợp Ctrl+Shift+Enter mỗi lần sửa đổi công thức

    B / Hàm SUMPRODUCT thường tính toán nhanh hơn công thức mảng khoảng 5-10%

    Theo www.decisionmodels.com thì khi bạn tính tổng nhiều điều kiện :

    1/ Sử dụng Conditional Sum Wizard : Đây là một add-in của Excel và là cách dễ dàng nhất : 744 giây trên máy laptop 500MHz

    {=SUM(IF($A$4:$A$10003=$A4,IF($L$4:$L$10003="AA",$ D$4:$D$10003),0))}

    2/ Sử dụng công thức mảng :

    {=SUM(($A$4:$A$10003=$A4)*($L$4:$L$10003="AA")*($D $4:$D$10003))}

    Công thức này tốn khoảng 723 giây, vẫn còn quá chậm

    3/Sử dụng hàm SUMPRODUCT :

    =SUMPRODUCT(($A$4:$A$10003=$A4)*($L$4:$L$10003="AA ")*($D$4:$D$10003))

    Nhanh hơn công thức mảng, cần khoảng 711 giây

    4/ Nếu bạn đặt dữ liệu tại một Sheet khác và thực hiện hàm SUMPRODUCT tại Sheet khác, kết quả tính toán sẽ giảm xuống còn 89 giây

    =SUMPRODUCT((Data!$A$4:$A$10003=$A4)*(Data!$L$4:$L $10003="AA")*(Data!$D$4:$D$10003))

    5/Sử dụng dãy động :

    Bạn thử đặt tên cho các dãy Data!$A$4:$A$10003, Data!$L$4:$L$10003, Data!$D$4:$D$10003, và đây là các Dynamic Range, công thức tính toán sẽ giảm còn 0.673 giây.

    Chẳng biết thế nào, vì tôi chưa kiểm chứng, bạn nào đã thử rồi xin cho biết, nhưng qua đây, chúng ta có một sự so sánh nhỏ cho các trường hợp để hiểu thêm vấn đề làm cách nào để Excel có thể tính toán nhanh hơn, phải không các bạn ?

    C/ Sử dụng hàm SUMPRODUCT sẽ thuận lợi hơn SUMIF khi cho công thức liên kết từ một Workbook khác :

    Trong trường hợp dữ liệu ở 2 Workbook khác nhau, nếu bạn dùng hàm SUMIF để tính toán, khi Workbook nguồn không mở đồng thời thì những Cell có hàm SUMIF sẽ báo lỗi VALUE, trong khi đó, dùng hàm SUMPRODUCT sẽ không bị lỗi này

    TD : Bạn đặt mã HH và số dư đầu kỳ tại Sheet DMHH trong một Workbook khác gọi là Data, dãy A2:A20 gồm các Mã HH, dãy B2:B20 là số lượng tồn đầu kỳ

    Và bạn dùng công thức sau để lấy số dư đầu kỳ cho từng loại HH trong Sheet NXT, cột A được nhập Mã HH, cột B là số tồn đầu kỳ
    B2 = SUMIF('[Data.xls]DMHH'!$A$2:$A$20,A2,'[Data.xls]DMHH'!$B$2:$B$20)
    Khi bạn không mở Workbook Data, các công thức trong Cell B2 sẽ bị lỗi ngay
    Ngược lại, nếu bạn dùng hàm SUMPRODUCT trogn trường hợp này sẽ không bị lỗi
    B2=SUMPRODUCT(('[Data.xls]DMHH'!$A$2:$A$20=A2)*('[Data.xls]DMHH'!$B$2:$B$20))

    (Nguồn: Webketoan)
     
    Last edited by a moderator: 7/8/06
  3. AnhExcel

    AnhExcel New Member Hội viên mới

    Được cảm ơn:
    0
    Điểm thành tích:
    0
    Ví dụ về Sumproduct

    Để hiểu hàm SUMPRODUCT làm việc như thế nào, chúng ta sẽ xem thí dụ dưới đây :
    =SUMPRODUCT((A1:A5="AA")*(B1:B5="N")*(C1:C5))
    Tạm hiểu là số lượng nhập của mặt hàng AA, với cột A là mã MH, cột B là cột cho cho biết nhập (N) hay xuất (X), cột C là cột số lượng

    A1:A5 = {AA, BB,AA,AA,AA}
    B1:B5 = {X,X,N,N,X}
    C1:C5 = {3,4,2,1,4}

    1 / Phần đẩu tiên của công thức (A1:A5="AA") sẽ kiểm tra mặt hàng nào là AA và cho giá trị là TRUE, còn lại là False. Như vậy công thức này sẽ tạo ra mảng : (A1:A5 = "AA") = {True, False, True, True, True}

    2/ Tương tự cho mảng B1:B5 với giá trị là "N" sẽ cho mảng sau :
    (B1:B5 = "N") = {False, False, True, True, False}

    3/ Và mảng C1:C5 = {3,4,2,1,4}

    Bây giờ, chúng ta có 3 mảng trên, hàm SUMPRODUCT làm việc trên các mảng số (number) nhưng ở đây chúng ta có 2 mảng (True/False). Nhưng khi chúng ta thực hiện phép nhân (*), chúng ta sẽ có mảng số. Vì True*True =1 và True*False =0, do đó, khi nhân 2 mảng ((A1:A5 = "AA")*(B1:B5 = "N")) với nhau, chúng ta có mảng sau :
    ((A1:A5 = "AA")*(B1:B5 = "N")) = {0, 0, 1, 1, 0}
    Và nhân 3 mảng :
    ((A1:A5 = "AA")*(B1:B5 = "N")*(C1:C5)) = {0, 0, 2, 1, 0}

    Hàm SUMPRODUCT là hàm tính tổng của phép nhân 3 mảng với nhau, do đó nó sẽ tính tổng của mảng sau :
    SUMPRODUCT((A1:A5 = "AA")*(B1:B5 = "N")*(C1:C5)) = SUM{0,0,2,1,0} và cho kết quả là 3

    Từ đây, chúng ta có nhận xét như sau :

    - Khi chúng ta thực hiện chuyển giá trị True / False thành 1/0, chúng ta cũng có thể thực hiện những phép tính sau :
    * Nhân mảng True/False với giá trị 1 :
    =SUMPRODUCT((A1:A5 = "AA")*1,(B1:B5 = "N")*1,(C1:C5))
    Hay :
    = SUMPRODUCT(1*(A1:A5 = "AA"),1*(B1:B5 = "N"),(C1:C5))
    Hay :
    =SUMPRODUCT((A1:A5 = "AA")^1,(B1:B5 = "N")^1,(C1:C5))
    * Cộng thêm số 0 :
    = SUMPRODUCT((A1:A5 = "AA")+0,(B1:B5 = "N")+0,(C1:C5))
    *Cách hay nhất là chúng ta thực hiện 2 dấu trừ liên tiếp (--) :
    = SUMPRODUCT(--(A1:A5 = "AA"),--(B1:B5 = "N"),(C1:C5))

    Hiểu như thế, chúng ta thấy rằng việc thực hiện hàm SUMPRODUCT với đối số là một mảng duy nhất có thể thực hiện được
    =SUMPRODUCT((Đk1)*(Đk2))
    sẽ được hiểu là : Đk 2 = một mảng tương ứng với các giá trị 1
    =SUMPRODUCT (1*Đk1) hay SUMPRODUCT (--(ĐK1))
    Cụ thể hơn ta có thể thực hiện hàm sau :
    =SUMPRODUCT(--(A1:A5="AA")) để đếm các giá trị "AA" có trong mảng A1:A5, giống hàm COUNTIF

    Cấu trúc của hàm SUMPRODUCT như vậy là đã rõ ràng, nhưng trong 5 cách viết, chúng ta sẽ sử dụng cách nào ? Theo bài trên thì :
    1/SUMPRODUCT((A1:A5="AA"),(B1:B5="N"),(C1:C5))
    2/SUMPRODUCT((A1:A5="AA")*(B1:B5="N")*(C1:C5))
    3/SUMPRODUCT(--(A1:A5="AA"),--(B1:B5="N"),(C1:C5))
    4/SUMPRODUCT((A1:A5="AA")*1,(B1:B5="N")*1,(C1:C5))
    5/SUMPRODUCT((A1:A5="AA")+0,(B1:B5="N")+0,(C1:C5))
    Theo www.decisionmodels.com thì
    -Sử dụng -- sẽ nhanh hơn +0 hay *1
    -Sử dụng --, nếu trong mảng C1:C5 có lẫn giá trị Text, hàm vẫn bỏ qua giá trị này mà không báo lỗi
    -Phép (,) sẽ nhanh hơn dấu (*), và phép (*) sẽ gây ra lỗi nếu trong dãy tổng có lẫn giá trị Text

    (Nguồn: Webketoan)
     
    Last edited by a moderator: 7/8/06
  4. HAUNGUYENDL8

    HAUNGUYENDL8 New Member Hội viên mới

    Được cảm ơn:
    0
    Điểm thành tích:
    0
    Ðề: Sử dụng hàm SUMPRODUCT và Công thức mảng

    :thodai::thodai::thodai::thodai:[PDF][/PDF]Anh Excel giúp dùm em với em có một bảng thống kê sản lượng sản xuất theo máy. em cần tính ra trong 1 ngày có bao nhiêu máy sản xuất ra hàng "AGNI" em dùng thử công thức của a nhưng kết quả cho là 0.

    mh tên hàng Mã Số máy Màu sắc(hàng)
    060 Robinhood VI01001
    060 Robinhood VI01002 PIN
    060 Robinhood VI01002 BLU
    060 Robinhood VI01002 GRN
    060 Robinhood VI01002 ORA
    060 Robinhood VI01002 PUR
    060 Robinhood VI01002 GRNL
    060 Robinhood VI01003
    060 Robinhood VI01004 PIN
    060 Robinhood VI01004 BLU
    060 Robinhood VI01004 GRN
    060 Robinhood VI01004 ORA
    060 Robinhood VI01004 PUR
    060 Robinhood VI01004 GRNL
    060 Robinhood VI01005
    060 Robinhood VI01006 PIN
    060 Robinhood VI01006 BLU
    060 Robinhood VI01006 GRN
    060 Robinhood VI01006 ORA
    060 Robinhood VI01006 PUR
    060 Robinhood VI01006 GRNL
    060 Robinhood VI01007
    060 Robinhood VI01008
    060 Robinhood VI01009 PIN
    060 Robinhood VI01009 BLU
    060 Robinhood VI01009 GRN
    060 Robinhood VI01009 ORA
    060 Robinhood VI01009 PUR
    060 Robinhood VI01009 GRNL
    060 Robinhood VI01010
    060 Robinhood VI01011 PIN
    060 Robinhood VI01011 BLU
    060 Robinhood VI01011 GRN
    060 Robinhood VI01011 ORA
    060 Robinhood VI01011 PUR
    060 Robinhood VI01011 GRNL
    120 GUCCI VI01011 RED
    120 GUCCI VI01011 BLU
    120 GUCCI VI01011 GRN
    120 GUCCI VI01011 PUR
    060 Robinhood VI01012
    005 Classic II VI01013
    005 Classic II VI01014 RED
    005 Classic II VI01014 BLU
    005 Classic II VI01014 GRN
    005 Classic II VI01014 ORA
    005 Classic II VI01015
    005 Classic II VI01016 RED
    005 Classic II VI01016 BLU
    005 Classic II VI01016 GRN
    005 Classic II VI01016 ORA
    005 Classic II VI01017
    028 AGNI VI01018 RED
    028 AGNI VI01018 BLU
    028 AGNI VI01018 GRN
    028 AGNI VI01018 YEL
    028 AGNI VI01018 ORA
    028 AGNI VI01018 PIN
    107 Sherwood VI01019 GRN
    107 Sherwood VI01019 BLU
    107 Sherwood VI01019 RED
    107 Sherwood VI01019 Blue Water
    107 Sherwood VI01020 GRN
    107 Sherwood VI01020 BLU
    107 Sherwood VI01020 RED
    107 Sherwood VI01020 Blue Water
    028 AGNI VI01021 RED
    028 AGNI VI01021 BLU
    028 AGNI VI01021 GRN
    028 AGNI VI01021 YEL
    028 AGNI VI01021 ORA
    028 AGNI VI01021 PIN
    028 AGNI VI01022 RED
    028 AGNI VI01022 BLU
    028 AGNI VI01022 GRN
    028 AGNI VI01022 YEL
    028 AGNI VI01022 ORA
    028 AGNI VI01022 PIN
    018 vision ng VI01024 ORA
    018 vision ng VI01024 RED
    018 vision ng VI01024 BLU
    018 vision ng VI01024 GRN
    018 vision ng VI01024 PUR
    018 vision ng VI01025
    002 C1C35 VI01026 RED
    002 C1C35 VI01026 BLU
    002 C1C35 VI01026 GRN
    002 C1C35 VI01026 PUR
    002 C1C40 VI01026
    005 Classic II VI01030
    005 Classic II VI01031 RED
    005 Classic II VI01031 BLU
    005 Classic II VI01031 GRN
    005 Classic II VI01031 ORA
    005 Classic II VI01032
    005 Classic II VI01033 RED
    005 Classic II VI01033 BLU
    005 Classic II VI01033 GRN
    005 Classic II VI01033 ORA
    120 GUCCI VI01034 RED
    120 GUCCI VI01034 BLU
    120 GUCCI VI01034 GRN
    120 GUCCI VI01034 PUR
    120 GUCCI VI01035
    002 C1C40 VI01036 RED
    002 C1C40 VI01036 BLU
    002 C1C40 VI01036 GRN
    002 C1C40 VI01036 PUR
    127 Taj lite VI01036 RED
    127 Taj lite VI01036 BLU
    127 Taj lite VI01036 GRN
    127 Taj lite VI01036 PUR
    070 Ultra Clean VI01037 RED
    070 Ultra Clean VI01037 BLU
    070 Ultra Clean VI01037 GRN
    070 Ultra Clean VI01037 ORA
    127 Taj lite VI01039
     
  5. levanninh

    levanninh Member Hội viên mới

    Được cảm ơn:
    0
    Điểm thành tích:
    6
    Ðề: Sử dụng hàm SUMPRODUCT và Công thức mảng

    bạn đưa dữ liệu đó khó hiểu quá
    up file lên đi bạn
    nhưng đọc đề bài của bạn mình thấy có 2 kiểu hiểu khác nhau
    giả sử có máy a SX được 5sp ,máy B sản xuất được 4 sp ,máy C SX được 2sp
    Cách hiểu thứ 1 là có 3 loại máy (theo mình dùng hàm countif)
    cách hiểu thứ 2 là có 11 sp được SX(dùng hàm sumif)
    mình nghĩ chắc bạn theo ý 1
     
  6. be09

    be09 Member Hội viên mới

    Được cảm ơn:
    7
    Điểm thành tích:
    8
    Ðề: Sử dụng hàm SUMPRODUCT và Công thức mảng

    Bạn có thể sử dụng Pivot Table để phân tích dữ liệu thì rất nhanh, nếu chứa nhiều dòng khi sử dụng hàm thì dung lượng tăng rất lớn, khi mở File nó sẽ chạy rất chậm, bạn có thể xem thử file của bạn tôi đã làm, tải File theo link sau:
    Su dung Pivot Table.xls
     
    Chỉnh sửa cuối: 22/11/11
  7. chipcoi1989

    chipcoi1989 New Member Hội viên mới

    Được cảm ơn:
    0
    Điểm thành tích:
    0
    Ðề: Sử dụng hàm SUMPRODUCT và Công thức mảng

    Câu hỏi của bạn khá giống của mình và chị be09 có câu trả lời rất hay tuy nhiên để làm được thì mình vẫn chưa biết cách. nếu bạn đã làm được thì pm mình nhé
     
  8. ht lyly

    ht lyly Lùn - Mập - Sún ^^ Hội viên mới

    Được cảm ơn:
    17
    Điểm thành tích:
    8
    Ðề: Sử dụng hàm SUMPRODUCT và Công thức mảng

    anh be09 không phải chị nhé b :xinloinhe:
     
  9. be09

    be09 Member Hội viên mới

    Được cảm ơn:
    7
    Điểm thành tích:
    8
    Ðề: Sử dụng hàm SUMPRODUCT và Công thức mảng

    Anh đã có hướng dẫn cho em cách sử dụng PivotTable tại bài 7 của Topic này:

    http://www.danketoan.com/forum/ui-ng-dui-ng-excel-ke-toan/cac-pro-giup-em-voi.209486.html
     

Chia sẻ trang này

XenForo Add-ons by Brivium ™ © 2012-2013 Brivium LLC.