Cách dùng hàm SumProduct và Công thức mảng

Cách dùng hàm 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.




Theo Nguyễn Duy TuânGPE

Read more

Công thức đếm Excel ROWS, COUNTBLANK, COUNTA, COUNT, COUNTIF

Công thức đếm Excel ROWS, COUNTBLANK, COUNTA, COUNT, COUNTIF

Ta có mảng dữ liệu A1:B10 có tên là Data. Mảng này sẽ có những ô trống, những ô chứa giá trị Text, số, những ô lỗi #N/A, #DIV/0, #NAME?, #NULL!, #VALUE!, #REF!. Ta lần lượt thực hiện các công thức đếm sau :


1/ Đếm tổng số ô :

Số ô có trong mảng dữ liệu Data A1:B10 là:

=ROWS(Data)*COLUMNS(Data)
2/Đếm các ô trống :

=COUNTBLANK(Data)

Hàm COUNTBLANK cũng đếm các ô chứa một công thức vốn trả về một chuỗi rỗng. TD : Công thức sau sẽ trả về một chuỗi rỗng nếu giá trị trong ô A1 lớn hơn 5. Nếu ô A1 đáp ứng điều kiện này, hàm COUNTBLANK sẽ đếm ô đó

=IF(A1>5,””,A1)

Bạn cũng có thể đếm số ô trống trong cột A bằng công thức sau :

=COUNTBLANK(A:A)

Và số ô trống trên tòan bộ Sheet1 :

=COUNTBLANK(Sheet1!1:65536)

Dĩ nhiên, bạn phải nhập công thức trên một Sheet khác ngọai trừ Sheet1, nếu không nó sẽ tạo ra tham chiếu vòng

Lưu ý : Hàm COUNTBLANK sẽ không đếm các ô chứa giá trị 0, ngay cả bạn hủy tùy chọn Zero Value trong Tools/Option
3/Đếm các ô không trống :

=COUNTA(Data)

Hàm COUNTA đếm các giá trị Text hoặc các giá trị Logic (TRUE hoặc FALSE). Nếu một ô chứa công thức trả kết quả về một chuỗi rỗng, hàm COUNTA cũng đếm luôn ô này
4/Đếm các ô số :

=COUNT(Data)

Các ô chứa giá trị logic (TRUE hay FALSE) không được xem là ô số. Hàm COUNT cũng đếm các ô chứa giá trị thời gian hay ngày tháng
5/Đếm các ô không phải Text :

{=SUM(IF(ISNONTEXT(Data),1))}
6/Đếm các ô Text :

{=SUM(IF(ISTEXT(Data),1))}
7/Đếm các giá trị Logic :

{=SUM(IF(ISLOGIC(Data),1))}

Nếu muốn biết các công thức mảng trên họat động ra sao, các bạn có thể tham khảo thêm ở bài “Mảng và công thức mảng”.
8/Đếm các giá trị lỗi trong một dãy :

Excel có 3 hàm để giúp bạn quyết định xem ô có chứa giá trị lỗi hay không :

– ISERROR : Trả về TRUE nếu ô chứa bất kỳ giá trị lỗi (#N/A, #VALUE, #REF!, #DIV/0!, #NUM!, #NAME?, hoặc #NULL!)

-ISERR : Trả về TRUE nếu ô chứa bất kỳ giá trị lỗi ngọai trừ #N/A

-ISNA : Trả về TRUE nếu ô chứa giá trị lỗi #N/A. Bạn có thể sử dụng các hàm này trong công thức mảng để đếm giá trị lỗi trong một dãy, tùy nhu cầu của bạn

{=SUM(IF(ISERROR(Data),1))}

{=SUM(IF(ISERR(Data),1))}

{=SUM(IF(ISNA(Data),1))}

Nếu bạn muốn đếm cụ thể các lỗi, bạn có thể sử dụng hàm COUNTIF

=COUNTIF(Data,”#DIV/0!”)

Trên đây là một số thí dụ về các công thức đếm cơ bản, lần sau, chúng ta sẽ phối hợp các hàm, các công thức mảng để thực hiện các công thức đếm và tính tổng nâng cao.

Read more

Các công thức Excel để tính tổng Sum, Sumif

Các công thức Excel để tính tổng Sum, Sumif

Phần trên, chúng ta đã đề cập về vấn đề tạo ra các công thức để đếm, bây giờ, chúng ta sẽ phát triển thêm các công thức dùng để tính tổng. Về nguyên tắc cơ bản, chúng không khác nhau là mấy, một số công thức chỉ cần thay COUNT (để đếm) bằng SUM (tính tổng) là được. Bạn hãy cùng tôi điểm qua các trường hợp ấy nhé.


1/ Tính tổng các ô trong một dãy : Giả sử dãy có tên là Data

=SUM(Data)


Bạn cũng có thể sử dụng cột hay hàng để làm đối số cho hàm SUM.


Td : = SUM(A:A) Hay = SUM(Sheet1!1:65536)


Để tránh tham chiếu vòng, công thức này không được xuất hiện trên Sheet1


Hàm SUM có thể lấy đến 30 đối số. Công thức sau đây sẽ trả về tổng của 5 dãy không nằm kề nhau :


=SUM(A1:A9, C1:C9, G1:G9, I1:I9, S1:S9)


Hàm SUM rất đa năng, các đối số của nó có thể là các giá trị số, ô, dãy, biểu diễn Text của các số (vốn được thông dịch là các giá trị), các giá trị logic và ngay cả các hàm được nhúng. TD :


=SUM(B1,5,”6″,,SQRT(4),A1:A5,TRUE)


Công thức này vốn là một công thức hòan tòan hợp lệ, chứa tất cả lọai đối số sau đây, được liệt kê ở đây theo thứ tự được biểu diễn :

– Một tham chiếu ô

-Một giá trị thực hiện

-Một chuỗi trông giống như một giá trị

-Một đối số bị thiếu

-Một biểu thức vốn sử dụng một hàm khác

– Một tham chiếu dãy

-Một giá trị logic TRUE


2/ Tính tổng tích lũy :


Nếu bạn muốn tại cột C hiển thị tổng tích lũy của các giá trị trong một dãy của cột B (B2:Bx), bạn hãy đặt công thức sau tại Cell C2 :


=SUM(B$2:B2)


Khi sao chép công thức xuống phía dưới, thí dụ tại Cell C8, công thức sẽ là :


=SUM(B$2:B8)


Đối số của hàm SUM luôn bắt đầu từ hàng 2 đến hàng hiện hành. Để cho công thức không bị lỗi đối với các hàng trống không nhập dữ liệu vào, bạn có thể sử dụng thêm hàng IF :


=IF(B2<>””,SUM(B$2:B2),””)


7/Tính tổng của N số hạng thấp nhất trong dãy Data


{=SUM(SMALL(Data,ROW(INDIRECT(“1:N”))))}


8/Tổng những số hạng ở những hàng cách nhau :


{=SUM((Data)*(MOD(ROW(Data)-ROW(A1);2)=0))}


9/Tổng những số chẵn trong dãy :


{=SUM(IF(MOD(Data;2)=0;Data;0))}


10/ Tổng những số lẻ trong dãy :


{=SUM(IF(MOD(Data;2)<>0;Data;0))}


11/Tổng những số hạng ở những hàng thứ 5, 10, 15 của dãy :


{=SUM((Data)*(MOD(ROW(data)-ROW(A1);5)=0))}


12/Tổng mỗi giá trị thứ n trong một dãy :


{=SUM((Data)*(MOD(ROW(data)-ROW(A1);n)=0))}


{=SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(Data)))-1,n)=0,Data,””))}


13/Tổng các giá trị được làm tròn :


Khi các ô trong một dãy được làm tròn 2 chữ số chẳng hạn, bạn thường thấy ô TC thể hiện một số không chính xác, do các giá trị thật khi được cộng vẫn là những con số thập phân với cả hàng chục chữ số bổ sung sau dấu phẩy. Do đó, hoặc bạn phải dùng hàm ROUND cho mỗi ô được cộng, hoặc bạn phải dùng công thức mảng cho ô TC như sau :


{=SUM(ROUND(B4:B10,2))}


14/ Tổng có điều kiện dựa vào một tiêu chuẩn :


Thường sử dụng là hàm SUMIF.


-Tổng các giá trị âm : Td: Tổng các giá trị âm trong dãy Data


=SUMIF(Data,”<0″)


– Tổng các giá trị dựa vào một dãy khác : Td : Tổng số lượng các mặt hàng có mã là AA


=SUMIF(MaMH,”AA”,SoLuong)

Read more

Sách Lean Excel Top Functions Quick Reference Guide with 500 Examples

Sách Lean Excel Top Functions Quick Reference Guide with 500 Examples

Learn how to use more than 100 of the most useful functions in Microsoft Excel!  This is an introductory level book perfect for beginner or advanced Excel users who want to quickly know the correct function to use.  Also included is a link to an example workbook with over 500 examples explaining how each function works and why the function may return an error.  Most of the functions are available since Excel 97 but some are only available after Excel 2007. 




Download: Lean Excel Top Functions Quick Reference Guide with 500 Examples

Read more