Công thức mảng, SUMPRODUCT, Lập công thức trong Excel
Đã có rất nhiều người dùng Excel gặp phải những bài toán thống kê phức tạp, tính tổng, đếm các chỉ tiêu có nhiều điều kiện ràng buộc. Excel cũng cũng cấp các hàm SUMIF, nhóm hàm Database: DSUM, DCOUNT,...Nhưng có lẽ tính "cơ động" của chúng vẫn làm bạn chưa ứng ý. Bài viết dưới đây tôi giới thiệu các bạn 2 phương pháp để thực hiện phép tính tổng có nhiều điều kiện.
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
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.
Tác giả: Hoàng Mạnh Hà - Nguyễn Đình Cường
Lớp Tin 24
Site:NgoiNhaChungNet.Com
Vui lòng giữ tên site và tác giả nếu dùng lại tài liệu dưới đây
----------------------------------------
Đây có lẽ chỉ là 1 bài tập lớn chứ chưa thành chương trình hoàn chỉnh, thích hợp cho các bạn sinh viên tham khảo.
chú ý, font tác giả dùng là: .VnUniverse nên bạn phải cài font này vào thư mục font trong Control pannel trước khi xem.
Nếu không down được file đính kèm, vui lòng vào trang sau: Download
Em có một số vấn đề cần giúp đỡ như sau ;
1. Làm cách nào gán nội dung của 1 textbox cho 2 trường trong 2 bản khác nhau ? VD : em co 2 bản là phiếu nhập và kho trong form em cho nhap liệu rồi khi nhấn nút sẽ cập nhật các trường vào cả bản kho và bản phiếu nhập. Em thử nhiều cách rồi mà không thấy hiệu quả
2. Em có 1 subform hiển thị danh sách hàng. làm cách nào khi nhấn nút thì subform hiển thị 1 datasheet trống để nhập thêm hàng, khi nhấn ok thì trở về hiển thị danh sách đã cập nhật.
Xin các bác giúp đỡ em cám ơn nhiều
MICROSOFT ACCESS
Copyright by : Trần Phúc Hậu
Tháng 10/2003
Do quá trình thực hiện không nhiều nên có những lỗi , mong các bạn thông cảm và góy ý cho tôi .
Email : tranphuchau@hopthu.com
Website : http://www.tranphuchau.fx.to