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

Offline Hà Nội 2014 tại Đồ Sơn-Hải Phòng
Chương trình từ thiện năm 2014 tại xã Tân Mỹ, huyện Trà Ôn, tỉnh Vĩnh Long




kết quả từ 1 tới 4 trên 4
40,609 lượt xem
  1. Lão già ham vui Hình đại diện của Tuanktcdcn
    Tham gia ngày
    Jun 2004
    Địa chỉ
    Hà Nội
    Tuổi
    37
    Bài gửi
    509
    Post Thanks / Like
    Tuanktcdcn vẫn chưa có mặt trong diễn đàn

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

    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.

    Đọc thêm bài viết
    http://webketoan.com/forum/showthrea...2110#post32110

    Thân chào!

    Xem thêm các bài viết mới nhất trong cùng chuyên mục này:


  2. Khu vực quảng cáo
    Tham gia ngày
    Always
    Địa chỉ
    Advertising world
    Tuổi
    2010
    Bài gửi
    Many

     

  3. Thành viên cấp cao Hình đại diện của handung107
    Tham gia ngày
    Aug 2004
    Địa chỉ
    VN
    Bài gửi
    582
    Post Thanks / Like
    handung107 vẫn chưa có mặt trong diễn đàn
    14-12-2005, 12:08 #2
    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))

    Còn nhiều điều lý thú khác về hàm này, hẹn các bạn sẽ tiếp tục sau nhé

  4. Thành viên cấp cao Hình đại diện của handung107
    Tham gia ngày
    Aug 2004
    Địa chỉ
    VN
    Bài gửi
    582
    Post Thanks / Like
    handung107 vẫn chưa có mặt trong diễn đàn
    Bài của khuenguyen.tran :

    Để 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

  5. Thanks happy0001 thanked for this post
  6. Thành viên cấp cao Hình đại diện của handung107
    Tham gia ngày
    Aug 2004
    Địa chỉ
    VN
    Bài gửi
    582
    Post Thanks / Like
    handung107 vẫn chưa có mặt trong diễn đàn
    15-12-2005, 13:21 #4
    Giả sử ta có 2 Sheet, một Sheet là DMMH có chứa mã MH và các số dư đầu kỳ. Tại Sheet này ta đặt 3 tên : TonMaMH cho cột chứa Mã MH, TonDauTG cho trị giá tồn đầu kỳ, TonDauSL cho số lượng tồn đầu kỳ

    Sheet thứ 2 là Sheet NhapXuatHH, Sheet này dùng để nhập các dữ liệu về Nhập Xuất HH phát sinh trong tháng
    Tại Sheet này ta có các cột sau : Cột K là Mã MH, cột M là Số Lượng Nhap, cột N là TGNhap, cột O là SLXuat, cột Q là TGXuat

    Tại Cell đầu tiên tính đơn giá vốn, giả sử là Cell K8, ta có công thức sau :
    =IF(OR(K8="",SUMIF(TonMaMH,K8,TonDauSL)=0),0,SUMIF (TonMaMH,K8,TonDauTG)/SUMIF(TonMaMH,K8,TonDauSL))

    Bắt đầu Cell K9, công thức sẽ trở thành :
    =IF(K9="",0,(SUMIF(TonMaMH,K9,TonDauTG)+SUMPRODUCT (($K$8:K8=K9)*($N$8:N8-$Q$8:Q8)))/(SUMIF(TonMaMH,K9,TonDauSL)+SUMPRODUCT(($K$8:K8=K9 )*($M$88-$O$8:O8))))

    Một cách khác để rút gọn công thức là đặt tên cho từng đoạn công thức nhu sau :
    Bạn đặt con trỏ ngay tại Cell đầu tiên áp dụng công thức, trong File gửi lên diễn đàn, tôi chọn Cell K8

    Đặt tên cho các công thức sau :

    SLDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$K8)*TonDauSL)
    TGDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$K8)*TonDauTG)

    Công thức trong Cell K8 sẽ trở thành :

    =IF(OR(K8="",SLDuDau=0),0,TGDuDau/SLDuDau)

    Bây giờ, ta đặt con trỏ tại Cell K9, và tiếp tục đặt tên cho công thức :

    SLDuCuoi = SLDuDau+SUMPRODUCT((NhapXuatHH!$K$8:K8=NhapXuatHH! K9 )*(NhapXuatHH!$M$88-NhapXuatHH!$O$8:O8))
    TGDuCuoi = TGDuDau+SUMPRODUCT((NhapXuatHH!$K$8:K8=NhapXuatHH! K9 )*(NhapXuatHH!$N$8:N8-NhapXuatHH!$Q$8:Q8))

    Công thức tại Cell K9 sẽ được viết thành :

    =IF(OR(K9="",SLDuCuoi=0),0,TGDuCuoi/SLDuCuoi)


Thread Information

Users Browsing this Thread

Hiện tại có 1 người đang truy cập đề tài này gồm: (0 thành viên và 1 khách)

Visitors found this page by searching for:

SUMPRODUCT

hàm sumproduct

sumproduct trong excelsumproduct trong excel 2007hàm sumproduct có điều kiệnhttp:www.webketoan.vnforumthreads11790-SUMPRODUCT-va-Cong-thuc-mang-Phep-tinh-co-nhieu-dieu-kienham sum producthàm product trong excel 2010cách sử dụng hàm sumproducthàm sum productsumproduct nhieu dieu kiencách dùng sumproducthàm sumproduct trong excelhướng dẫn sử dụng hàm sumproduct trong excel 2007hàm sumproduct trong excel 2010cách dùng hàm sumproductsumproduct in excelhàm sumproduct dùng để làm gìhàm sumproduct nhiều điều kiệnsử dụng hàm sumproduct trong excelsum product trong excel 2007HÀM SUMPRODUCTham sumproduct trong excel 2007cong thu excell trong ke toandem dieu kien co loc trung dung ham sumproduct