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

[handung107]

Tìm hiểu về Mảng và công thức mảng

Phần 1 : Mảng một chiều, mảng hai chiều và hằng mảng
1/Mảng một chiều : Ta có thể xem mảng một chiều là một hàng (mảng ngang ) hay một cột (mảng thẳng đứng)
Các phần tử trong một mảng một chiều (mảng ngang ) được cách biệt nhau bằng một dấu phẩy, và trong mảng thẳng đứng được cách nhau bằng dấu chấm phẩy
Td : {1,2,3,4,5} (mảng ngang ) và {10;20;30;40;50} (mảng thẳng đứng)
Hay {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} (phần tử là Text dạng chuỗi)
Để hiển thị một mảng một chiều 5 phần tử theo dạng ngang hay thẳng đứng, ta cần 5 ô liên tục trên cùng một hàng hay một cột
Td : Ta chọn 5 ô từ A1:E1, nhập : ={1,2,3,4,5} và nhấn Ctrl+Shift+Enter
Hoặc chọn 5 ô từ A1:A5, nhập : ={1;2;3;4;5} và nhấn Ctrl+shift+Enter
Các phần tử trong mảng sẽ lần lượt được nhập vào 5 ô liên tục theo thứ tự trong mảng
Nếu trong mảng chỉ có 5 phần tử nhưng ta chọn 6 ô để nhập mảng thì ô thứ 6 sẽ cho giá trị #N/A
2/Mảng hai chiều :
Mảng hai chiều là một hình chữ nhật bao gồm nhiều hàng và nhiều cột
Tương tự như mảng một chiều, ta sử dụng các dấu phẩy để ngăn cách các phần tử trong cùng một hàng và dấu chấm phẩy để ngăn cách các phần tử trong cùng một cột
Td : {1,2,3,4;5,6,7,8;9,10,11,12}
Để hiển thị mảng này, ta chọn vùng A1 : D3, nhập :
={1,2,3,4;5,6,7,8;9,10,11,12} và nhấn Ctrl+Shift+Enter
Cũng vậy, nếu bạn nhập một mảng vào một dãy vốn có các ô nhiều hơn các phần tử mảng, Excel sẽ hiển thị #N/A trong các ô trống còn lại
3/Hằng mảng :
Bạn có thể tạo một hằng mảng, đặt cho nó một cái tên, sau đó sử dụng mảng được đặt tên này cho công thức.
Td : Ta có hằng mảng như sau : {1,0,1,0,1}. Công thức sau sử dụng hàm SUM với hằng mảng đứng trước là đối số của nó. Công thức trả về tổng của các giá trị trong mảng (ở đây là 3). Công thức này sử dụng mảng là đối số nhưng không phải là công thức mảng : =SUM({1,0,1,0,1}). Công thức này cùng kết quả với công thức sau : =SUM(1,0,1,0,1)
Vào thời điểm này, có thể bạn chưa thấy ưu điểm của việc sử dụng hằng mảng.
Công thức sau sử dụng hai hằng mảng :
=SUM({1,2,3,4}*{5,6,7,8}).
Công thức này sẽ tạo ra một mảng mới như sau :{5,12,21,32}
Sau đó, mảng này lại là đối số cho hàm SUM và cho ra kết quả là 70
Bạn sẽ thấy công thức trên tương tự như công thức sau :
=SUM(1*5,2*6,3*7,4*8)
Một hằng mảng sẽ không chứa các công thức, các hàm, các giá trị có chứa dấu dollar, dấu phẩy, chấm phẩy...Sau đây là một hằng mảng không hợp lệ :
{SUM(3,2),$56,12,5%}
Việc đặt tên cho hằng mảng được thông qua hộp thọai Insert/Name/Define
Tên của mảng đặt tại hộp Name : DayNames
Tại hộp Refers to ta đặt dấu ={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}
Nếu bây giờ, ta chọn dãy A1:G1 và nhập công thức mảng sau :{=DayNames}, ta sẽ thấy các phần tử của mảng lần lượt hiện ra trong các ô này.
Để chuyển mảng này thành mảng dọc, ta dùng công thức :
={TRANSPOSE(DayNames)}
Để truy cập từng phần tử riêng lẻ của mảng, ta dùng hàm :
=INDEX(DayNames,4) sẽ cho kết quả là Wed.

 

[handung107]

Làm việc với công thức mảng

--------------------------------------------------------------------------------

1/ Tạo một mảng từ các giá trị trong một dãy :

Giả sử ta có một số dữ liệu được nhập vào A1:C4. Ta chọn dãy D8:F11, và nhập vào công thức mảng : {=A1:C4}. Mảng D8:F11 bây giờ sẽ chứa những dữ liệu liên kết với mảng A1:C4, mọi thay đổi giá trị bất kỳ trong A1:C4, thì ô tương ứng trong D8:F11 cũng phản ánh sự thay đổi theo

2/Tạo một hằng mảng từ các giá trị trong một dãy :

Ở trên, ta đã có công thức mảng trong D8:F11 liên kết với các ô trong mảng A1:C4, bây giờ ta sẽ cắt đứt sự liên kết này để tạo ra một hằng mảng gồm các giá trị trong A1:C4. Để thực hiện, chúng ta làm như sau :
-Chọn mảng D8:F11
-Nhấn F2 để hiệu chỉnh công thức mảng
-Nhấn F9 để chuyển đổi các tham chiếu ô thành các giá trị
-Nhấn Ctrl+Shift+Enter để nhập lại công thức mảng
Ta sẽ thấy các giá trị của hằng mảng ở trên thanh công thức

3/Chọn một dãy công thức mảng :

Ta có thể chọn bằng tay, hoặc dùng Edit/Go to (hoặc nhấn F5), nhấp nút Special, sau đó chọn Current array, nhấp OK để đóng hộp thọai

4/ Hiệu chỉnh một công thức mảng :

-Bạn khôn gthể thay đổi nội dung của bất kỳ ô nào vốn tạo nên một công thức mảng
-Bạn không thể xóa các ô vốn hình thành của một công thức mảng (nhưng bạn có thể xóa tòan bộ một mảng)
-Bạn không thể chèn các ô mới vào một dãy mảng (nghĩa là chèn hàng và chèn cột)
Để hiệu chỉnh công thức mảng, bạn chọn tất cả các ô trong dãy mảng, kích họat thanh công thức hay nhấn F2, Excel sẽ lọai bỏ các dấu ngoặc, và khi bạn hiệu chỉnh xong, bạn sẽ nhấn Ctrl+Shift+Enter để kết thúc

 

[handung107]

Công thức mảng một ô và công thức mảng nhiều ô

--------------------------------------------------------------------------------

A/ Làm việc với công thức mảng một ô :

1/ Đếm các ký tự trong một dãy : (bạn có thể tham khảo File ví dụ của levanduyet)
Giả sử bạn có một dãy ô, chứa các dữ liệu dạng Text từ A1:A14, bây giờ bạn cần đếm tổng số ký tự có trong dãy trên. Nếu không dùng công thức mảng, bạn sẽ phải dùng một cột phụ, thí dụ cột B từ B1:B14.
Tại B1, bạn nhập công thức B1=LEN(A1) và sao chép công thức xuống đến B14. Sau đó, tại B15, bạn dùng công thức tính tổng : =SUM(B1:B14)
Để thay thế cho tất cả công việc trên, ta dùng công thức mảng ngay tại Cell A15 {=SUM(LEN(A1:A14)}. công thức mảng sử dụng hàm LEN để tạo ra một mảng mới (trong bộ nhớ) gồm số ký tự trong mỗi ô của dãy rồi sau đó, tính tổng của dãy.

2/Đếm các ô Text trong một dãy :

Công thức mảng sau đây dùng hàm IF để kiểm tra từng ô một trong dãy. Sau đó, nó tạo ra một mảng mới (có cùng kích cỡ và các chiều với mảng gốc) gồm các số 0 và 1 phụ thuộc vào ô đó có chứa dữ liệu Text hay không. Mảng mới này được chuyển sang hàm SUM để tính tổng các ô trong mảng và cho kết quả là số ô Text được đếm trong dãy
{=SUM(IF(ISTEXT(A1 : D15),1,0))}
Hay : {=SUM(ISTEXT(A1 : D15)*1)}

3/Đếm các ô lỗi trong một dãy :

Tương tự công thức trên, ta dùng công thức sau để đếm ô có lỗi trong một dãy :
{=SUM(IF(ISERROR(A1 : D15),1,0))}
Hay : {=SUM(IF(ISERROR(A1 : D15),1))}
{=SUM(ISERROR(A1 : D15)*1)}

4/Tính tổng một dãy có chứa các lỗi:

Bạn thấy đó, hàm SUM của Excel không họat động nếu bạn dùng để tính tổng một dãy có chứa một hoặc nhiều giá trị lỗi (#DIV/0! hoặc #N/A). Công thức mảng sau đây trả về tổng của một dãy ngay cả dãy này có chứa các lỗi
{=SUM(IF(ISERROR(A1 : D15),"",A1 : D15)}

5/Tính tổng 3 giá trị nhỏ nhất trong một dãy :

Công thức sau đây trả vế tổng của 3 giá trị nhỏ nhất trong một dãy có tên là Data (đặt tên mảng, các bạn tham khảo trong phần "Tên và nhãn trong công thức").
{=SUM(SMALL(Data, {1,2,3}))}
Hàm sử dụng một hằng mảng làm đối số thứ hai cho hàm SMALL. Hàm SMALL được tính tóan 3 lần với 3 đối số thứ hai khác biệt là 1, 2, 3 tương ứng với các giá trị nhỏ nhất, nhỏ thứ hai, và nhỏ thứ ba trong dãy. Sau đó, kết quả tạo ra một mảng mới gồm 3 số hạng nhỏ nhất trong dãy và hàm SUM sẽ tính lại tổng của mảng mới này.

5/Tính tổng 3 giá trị lớn nhất trong một dãy :

Tương tự, bạn có thể tính tổng của 3 giá trị lớn nhất trong một dãy như sau :
{=SUM(LARGE(Data, {1,2,3}))}

6/Tính tổng n giá trị lớn nhất trong một dãy :

{=SUM(LARGE(Data,ROW(INDIRECT("1:n"))))} (n : giá trị bạn muốn tính )

Hàm ROW(INDIRECT("1:n")) sẽ tạo ra một mảng gồm các đối số từ 1 đến n và hằng mảng này sẽ được dùng làm đối số thứ hai cho hàm LARGE như đã giải thích ở phần trên

Trên đây là một số thí dụ đơn giản về công thức mảng cho một ô, nghĩa là ta chỉ chọn một ô và nhập vào đó công thức mảng, sau đó sao chép sang các ô khác. Còn công thức mảng nhiều ô nghĩa là ta chọn cùng một lúc nhiều ô và nhập vào đó cùng một công thức mảng. Chúng ta sẽ tiếp tục trong những kỳ sau

 

[handung107]

Công thức mảng một ô & công thức mảng nhiều ô (tt)

--------------------------------------------------------------------------------

7/Tạo một mảng các số nguyên liên tục :

Ở phần 6, các bạn chú ý hàm {=ROW(INDIRECT("1:n")} sẽ cho ra 1 dãy số gồm các số nguyên liên tục từ 1 đến n. Và đây cũng chính là công thức mảng giúp bạn nhập số thứ tự vào 1 dãy chọn trước. Ta thử xem hàm này họat động ra sao nhé
Hàm ROW trong Excel trả kết quả về một số hàng. Nếu ta nhập công thức mảng : {=ROW(1:12)} vào một dãy A1:A12 nằm dọc có 12 ô, ta sẽ nhận được một mảng gồm các số nguyên liên tục từ 1 đến 12. Nhưng nếu ta chèn thêm một hàng vào dãy trên nằm ở trên A1, ta sẽ thấy công thức trên trở thành {=ROW(2:13)} và mảng của ta sẽ có 12 số nguyên liên tục từ 2 đến 13.
Do đó, ta phải sử dụng kết hợp với hàm INDIRECT. Hàm INDIRECT lấy đối số là một chuỗi Text. INDIRECT("1:12") sẽ luôn luôn cho kết quả là (1:12), vì Excel không thể điều chỉnh được các tham số chuỗi trong hàm INDIRECT được. Sự kết hợp này luôn đảm bảo cho hàm {=ROW(INDIRECT("1:12")} cho kết quả là một mảng gốm các số từ 1 đến 12 trong mọi trường hợp

8/Tìm một giá trị trung bình lọai trừ giá trị zero :

Giả sử ta muốn tính giá trị trung bình doanh thu trong một năm, nhưng trong năm, ta có 2 tháng doanh số =0. Giá trị doanh thu hàng tháng (lọai trừ tháng không có DT) được đặt trong cột B5:B16. Ta lập công thức mảng như sau :
{=AVERAGE(IF(B5:B16)<>0,B5:B16)}.
Công thức này sẽ tạo ra một mảng mới gồm các giá trị khác 0, và hàm AVERAGE sử dụng mảng mới này làm đối số của nó. Bạn cũng có thể sử dụng công thức tương đương sau, nếu không muốn dùng công thức mảng :
=SUM(B5:B16)/COUNTIF(B5:B16,"<>0")

9/Lọai bỏ các công thức trung gian :

Một trong những ưu điểm của việc sử dụng công thức mảng là lọai trừ được những công thức trung gian. Giả sử ta có cột A là tên Mặt Hàng, cột B là Giá Vốn, cột C là Giá Bán và cột D tạm gọi là Lãi.
Ta có mảng dữ liệu từ hàng thứ 2 D2=C2-B2 và sao chép công thức đến hàng thứ 10.
Sau đó, ta tính tổng D11=SUM(D2 : D10).
Nếu ta dùng công thức mảng, ta sẽ tính ngay tại một ô nào đó, TD : C11
C11 :{=SUM(C2:C10-B2:B10)}
Nếu muốn xem, mặt hàng nào lãi nhiều nhất, ta dùng : {=MAX(C2:C10-B2:B10)}
và mặt hàng nào lãi ít nhất sẽ là : {=MIN(C2:C10-B2:B10)}. Ta hầu như lọai bỏ được cột trung gian là cột D

 

[handung107]

Công thức mảng một ô & công thức mảng nhiều ô (tt)

--------------------------------------------------------------------------------

10/ Xác định 1 giá trị cụ thể có trong 1 dãy không ?

Để xác định một giá trị cụ thể có trong dãy đó không, ta thường sử dụng chức năng Edit / Find. Nhưng ta vẫn có thể làm việc này chỉ với công thức mảng. Các bạn có thể tham khảo công thức trong File của bạn Levanduyet
Giả sử ta có một DS các tên từ A4:C13. Ta dùng ô B2 để nhập tên mà ta muốn kiểm tra xem có trong mảng không ? Ta đặt tên B2 là TheName và mảng tên là NameList. Nếu tìm thấy, sẽ cho kết quả tại Cell E4 là "Name in the List", và nếu không có là "Name not Found".
Công thức trong E4 là :
{=IF(OR(TheName=NameList),"Name in the List","Name not Found")}
Công thức này so sánh TheName với mỗi ô trong dãy NameList. Nó tạo ra một mảng mới gồm các giá trị True và False. Hàm OR trả về True nếu bất kỳ một trong các giá trị trong mảng mới là True. Hàm IF sử dụng kết quả này để xác định thông báo nào được hiển thị
Đơn giản hơn, ta có thể dùng : {=OR(TheName=NameList)} sẽ cho kết quả True nếu tên được tìm thấy và False nếu tên không tìm thấy

11/ So sánh 2 dãy :

Điều kiện so sánh là 2 dãy phải có cùng một kích cỡ và cùng chiều với nhau
Ta gọi 2 dãy này là MyData và YourData. Công thức mảng sau đây sẽ cho kết quả có bao nhiêu số phần tử khác nhau trong 2 dãy
{=SUM(IF(MyData=YourData,0,1))}
Công thức này sẽ tạo ra một mảng mới có cùng kích cỡ với 2 mảng đang được so sánh. Hàm IF sẽ lấp đầy mảng này bằng các phần tử 0, 1 (0 nếu có một phần tử khác nhau được tìm thấy và 1 nếu các phần tử của 2 dãy giống nhau). Sau đó, hàm SUM sẽ trả về tổng của các giá trị 0 trong mảng (số phần tử khác nhau trong 2 dãy)
Ta có thể đơn giản hóa công thức trên như sau :
{=SUM(1*(MyData<>YourData))}

 

[handung107]

Hàm tự tạo trả về 1 mảng như một kết quả lọc

--------------------------------------------------------------------------------

P/S : Đây là bài của SA_DQ viết cho Box Excel của WKT

CSDL là trường cấp 2 -3 tại: http://webketoan.com/forum/showthread.php?t=17161 ( ở đây trường MaTinh - cột 7) các bạn tự thêm vô giúp)
Function Loc4DK(Sh1 As Object, Lop As String)
1 Dim K_Qua(1 To 30, 1 To 4) As Variant
2 Dim jZ As Integer, zJ As Integer
3 Application.ScreenUpdating = 0
For jZ = 1 To 999
5 If Sh1.Cells(jZ, 6) = Lop And Sh1.Cells(jZ, 5) = 0 And Mid(Sh1.Cells(jZ, 4), 4, 2) > "09" And Sh1.Cells(jZ, 7) = "08" Then
6 zJ = zJ + 1
7 K_Qua(zJ, 2) = Sh1.Cells(jZ, 3): K_Qua(zJ, 3) = Sh1.Cells(jZ, 4)
8 K_Qua(zJ, 4) = Sh1.Cells(jZ, 6): K_Qua(zJ, 1) = Sh1.Cells(jZ, 1)
9 End If
10 Next jZ
11 Loc4DK = K_Qua
End Function
Để nhận được kết quả ta chọn từ ô O1:R30 tại sheet 'S1' & nhập hàm tự tạo: =Loc4DK( A2:G989; K1) lên thanh CT & kết thúc bằng tổ hợp 3 fím sẽ cho kết quả các em HS nam ngụ tại TP HCM (có mã tỉnh = '08), có ngày sinh thuộc quí 4 theo lớp chọn từ ComboBox sẵn có trên S1 của CSDL.
Có nghĩa dùng Combo sẽ lọc được danh sách HS lần lượt từng lớp thoả 3 Đ/K còn lại nêu trên.

 

[handung107]

Hàm 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

 

[handung107]

Bài viết của Tuanktcdcn

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.

 

[handung107]

Tham khảo thêm bài viết của Tuanktcdcn

Fomula Array-Công thức mảng trên EXCEL

--------------------------------------------------------------------------------

Hôm nay mình sẽ phân tích về công thức mảng, giải thích các phép toán quan trọng của EXCEL và cũng là thực hiện lời hứa của TuanKTCDCN trên một chuyên mục của Webketoan. Để hiểu tốt bạn nên kết hợp vừa đọc vừa làm ngay trên máy.

To handung107 8 công thức tính của bạn mình giải thích kết hợp với phân tích về công thức mảng như sau:

1) Giải thích các hàm

+ Hàm SMALL(VUNG,P): Cho ra giá trị ở vị trí P trong VUNG theo thứ tự từ thấp đến cao mặc dù VUNG chưa được sắp xếp.
VD A1=4,A2=3,A3=5
SMALL(A1:A3,3) = 5 (Vì nếu A1:A3 được sắp xếp tăng dần thì 5 sẽ ở vị trí thứ 3)
SMALL(A1:A3,2) = 4 (Vì nếu A1:A3 được sắp xếp tăng dần thì 4 sẽ ở vị trí thứ 2)
SMALL(A1:A3,1) = 3 (Vì nếu A1:A3 được sắp xếp tăng dần thì 4 sẽ ở vị trí thứ 1)
+ Hàm LARGE(VUNG,P): Cho ra giá trị ở vị trí P trong VUNG theo thứ tự từ cao đến thấp mặc dù VUNG chưa được sắp xếp.
VD A1=4,A2=3,A3=5
LARGE(A1:A3,3) = 3 (Vì nếu A1:A3 được sắp xếp giảm dần thì 3 sẽ ở vị trí thứ 3)

+ Hàm INDIRECT(TextRef) cho ra giá trị kiểu dạng Text thành dạng tham chiếu
VD INDIRECT("A1")=A1
Giả sử A1=4
Nếu bạn dùng SUM("A1") kết quả là #VALUE!. Lỗi vì sai kiểu, đối số của SUM phải là dạng số hoặc các tham chiếu.
SUM(A1) = 4 đúng vì đúng kiểu
SUM(INDIRECT("A1")) = 4 đúng vì INDIRECT("A1") =A1, lúc đó EXCEL hiểu là SUM(A1)=>đúng kiểu.
+ Hàm ROW([THAMCHIEU]) nếu không có THAMCHIEU ROW() = dòng con trỏ hiện thời, còn nếu có sẽ cho ra dòng của tham chiếu ROW(A5)=5, ROW(2:2)=2, ROW(5:5)=5.
+ Hàm MOD(SCHIA,SBCHIA). Trả về giá trị 0 khi phép chia là chẵn,<>0 nếu lẻ (ứng dụng để tìm năm nhuận).
VD: MOD(6,3)=0, MOD(7,3)=1, MOD(8,3)=2

2) Công thức mảng
Qua một ví dụ nhỏ bạn sẽ hiểu qua về Formula Array
Giả sử cột A là SLG, B là DGIA. Cần tính tổng giá trị (Tổng thành tiền)
A1=4;B1=2
A2=3;B2=3
A3=5;B3=2
=SUM(A1:A3*B1:B3) Nhấn CTRL+SHIFT+ENTER
{=SUM(A1:A3*B1:B3)}=27 (4*2+3*3+5*2=27)
Theo cách phân tích của Formula Array thì EXCEL sẽ chạy con trỏ lần lượt tự dòng đầu tiên trong vùng dữ liệu A1:B1 đến dòng cuối của vùng là A3:B3. Mỗi một dòng khi con trỏ chạy qua Excel thực hiện công thức A1*B1, đến dòng thứ 2 là (A1*B1)+(A2*B2) và đến dòng thứ 3 là (A1*B1)+(A2*B2)+(A3*B3). EXCEL sẽ thực hiện tính từng dòng trong CSDL theo công thức chỉ định và tích luỹ lại theo phép tổng (SUM) và ra kết quả.

Nếu bạn không dùng tổ hợp phím CTRL+SHIFT+ENTER thì gí trị ở cell sẽ là #VALUE! vì sai kiểu.
Trên chính là phân tích về Fomula Array mà EXCEL thực hiện. Từ đây chúng ta sẽ tự lập cho mình những công thức tính có kết hợp nhiều diều kiện, trong khi bạn lập bạn nên có cách tư duy như: tính tổng vùng TIEN nếu như NGAY là Thứ 7, công thức sẽ là SUM(IF(WEEKDAY(NGAY)=7,TIEN,0)).
Với công thức trên EXCEL sẽ cộng số TIEN ở dòng tương ứng là Thứ 7, nếu không phải cộng 0. Bạn cứ hình dung trình tự khi EXCEL phân tích công thức trên từng dòng và tích luỹ lại theo phép toán tuỳ vào bạn dung hàm gì bạn sẽ hiểu. Trong VD tren là tổng vì dung hàm SUM bên ngoài.

3) Phép tính Logic
"Trái" so sánh với "Phải"
So sánh có thể là <,>,<>,=,>=,<=, Not
2>3=FALSE
3<5=TRUE

TRUE=1
FALSE=0
TRUE+TRUE+....=1
TRUE*FALSE*....=0
(2>3)+(3<5)=FALSE+TRUE=TRUE=1 TĐ OR((2>3),(3<5))
(2>3)*(3<5)=FALSE*TRUE=FALSE=0 TĐ AND((2>3),(3<5))


4) Giải thích các bài của Handung107
Các công thức trên đều là Formula Array
Bạn đọc kỹ 3 phần trên bạn sẽ dễ hiểu!

1.Tính tổng của N số hạng thấp nhất trong dãy A1:A60
{=SUM(SMALL(A1:A60,ROW(INDIRECT("1:N"))))}
Bạn xem lại 2 hàm SMALL và INDIRECT.

Giả sử cho N=3 có nghĩa là sẽ tính tổng của 3 số thấp nhất trong vung A1:A60
Khi EXCEL tính dòng 1 sẽ là (SMALL(A1:A60,ROW(INDIRECT("1:1"))))=Số thấp thứ 1
Khi EXCEL tính dòng 2 sẽ là: Số thấp thứ 1+(SMALL(A1:A60,ROW(INDIRECT("2:2"))))=(Số thấp thứ 1+Số thứ thứ 2)
Khi EXCEL tính dòng 3 sẽ là (Số thấp thứ 1+Số thứ thứ 2)+(SMALL(A1:A60,ROW(INDIRECT("3:3"))))=(Số thấp thứ 1+Số thứ thứ 2+Số thứ thứ 3)
Đó chính là tổng của 3 số hạng thấp nhất. Nếu là N EXCEL sẽ chạy từ dòng 1 đến dòng N và tính như trên.
(Số thấp thứ 1+Số thấp thứ thứ 3
Tại sao A1:A60 vẫn giữ nguyên? Vì đối số đầu của SMALL phải là vùng SMALL(VUNG,P)

2.Tính tổng của N số hạng cao nhất trong dãy A1:A60
{=SUM(LARGE(A1:A60,ROW(INDIRECT("1:N"))))}
Tương tự như 1) thay SMALL=LARGE =>ngược lại

3.Tổng những số hạng ở những hàng cách nhau :
{=SUM((A1:A60)*(MOD(ROW(A1:A60)-ROW(A1);2)=0))}
ROW(A1)=1 (đã giải thích)
Theo cách phân tích của Formula Array EXCEL thực hiện công thức từng dòng 1->60 như sau:
Dòng 1 là (A1)*(MOD(ROW(A1)-ROW(A1),2)=0) Kế quả= A1*0=0
Dòng 2 là (A2)*(MOD(ROW(A2)-ROW(A1),2)=0) Kế quả= A2*1=A2
Dòng 3 là (A3)*(MOD(ROW(A3)-ROW(A1),2)=0) Kế quả= A2*0=0
....
Dòng n là (An)*(MOD(ROW(n)-ROW(A1),2)=0) Kế quả= An*0=An (nếu N chẵn)
EXCEL tính tổng và tích luỹ mỗi dòng=0+A2+0+...+0/An
Câu 4,5,6 giải thích tương tự
Trong hàm MOD(SCHIA,SBCHIA) bạn thay SBCHIA thành 5 hay 0 sẽ có ýa nghĩa khacks nhau.

Câu 7,8 là phép tính có nhiều điều kiện (từ 2 ĐK trở lên) bạn vận dụng phép tính Logic
Lg1+Lg2+..+Lgn=OR(Lg1,Lg2,..,Lgn)=TRUE khi ít nhất 1 Lg =True
Lg1*Lg2*..*Lgn=AND(Lg1,Lg2,..,Lgn)=TRUE khi tất cả Lg =True

Trong Formula Array không cho phép bạn dùng tuỳ tiện AND, OR và NOT

7.Tổng những số hạng dựa trên điều kiện AND :
{=SUM(IF((A1:A60>50)*(B1:B60="HH01");C1:C60;0))}
Tính tổng các giá trị trong vùng C1:C60 nếu như giá trị dòng tương ứng của vùng A1:A60>50 và B1:B60="HH01"
Phân tích theo EXCEL
Dòng 1 là If((A1>50)*(B1="HH01"),C1,0))=C1 nếu (A1>50)*(B1="HH01")=TRUE, 0 nếu FALSE
Dòng 2 là If((A2>50)*(B2="HH01"),C2,0))=C2 ........
.....
Dòng 60 .......

Vì hàm bọc ngoài là SUM nên EXCEL sẽ tính tổng tích luỹ lại khi sang dòng mới.

Cách 2 :
{=SUM((A1:A60>50)*(B1:B60="HH01")*C1:C60;0)}

Dòng 1 là If((A1>50)*(B1="HH01")*C1,0))=1*C1 nếu (A1>50)*(B1="HH01")=TRUE, 0*C1 nếu FALSE
Dòng 2 là If((A2>50)*(B2="HH01"),C2,0))=C2 ........

8. Tổng những số hạng dựa trên điều kiện OR :
{=SUM((A1:A60>50)*((B1:B60="HH01")+(B1:B60="HH02") )*(C1:C60))}
Cách giải thích như câu 7

Trong công thức, phần ngăn cách đối số có máy là ( có máy là (,).
VD IF(Tien>100;"Thưởng";"Không")
Chuẩn phải là IF(Tien>100,"Thưởng","Không")
Các bạn vào Control Panel\Regional Setting\Number chỉnh List, thoát khỏi Excel , mở lại là OK

Chúc các bạn có thêm những sáng tạo để thêm sự hăng say công việc!

 

[handung107]

Nói thêm về ư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))

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é

 

[handung107]

Áp dụng hàm SUMPRODUCT để tính ĐG bình quân gia quyền liên hoàn

--------------------------------------------------------------------------------

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$8:M8-$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$8:M8-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)