Bài giảng môn Tin học văn phòng - Bài 3: Hàm thống kê và công thức mảng - Thiều Quang Trung

BÀI 3 
HÀM THỐNG KÊ VÀ CÔNG THỨC MẢNG 
GV: Th.S. Thiều Quang Trung 
Bộ môn Khoa học cơ bản 
Trường Cao đẳng Kinh tế đối ngoại 
• Hàm thống kê 1 
• Khái niệm công thức mảng 2 
• Ưu điểm của công thức mảng 3 
• Hằng số mảng 4 
• Ứng dụng công thức mảng 5 
Nội dung 
2 GV. Thiều Quang Trung 
1. Hàm AVERAGE(number1,number2,...) 
Tính trung bình cộng các số number1, number2,... 
Nếu địa chỉ ô nằm trong đối số chứa kiểu ký tự, giá 
trị logic hoặc là các ô trống thì các giá trị này được 
bỏ qua. Các ô chứa giá trị bằng 0 đều được tính. 
Ví dụ: Nếu A1:A4 chứa các số 1,3,5,7 thì 
AVERAGE(A1:A4) bằng 4. 
3 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
2. Hàm MAX(number1, number2,...) 
Trả về số lớn nhất trong các đối số. 
Ví dụ: Nếu A1:A4 chứa các dữ liệu là 1,9,5,7 thì 
MAX(A1:A4) bằng 9. 
3. Hàm MIN (number1, number2,...) 
Trả về số nhỏ nhất trong các đối số. 
Ví dụ: Nếu A1:A4 chứa các dữ liệu là 9,1,5,7 thì 
MIN(A1:A4) bằng 1. 
 4 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
4. Hàm RANK(số, dãy số, loại thứ tự) 
Hàm sẽ trả về thứ tự của số trong dãy số, dãy số có 
thể là vùng giá trị hoặc địa chỉ của vùng dãy số cần 
xếp thứ tự. 
– Nếu loại thứ tự là 0 hoặc bỏ qua thì Excel sẽ xếp 
thứ bậc theo thứ tự giảm dần của dãy số. 
– Nếu loại thứ tự là một giá trị bất kỳ khác 0 thì 
Excel sẽ xếp thứ bậc theo thứ tự tăng dần của dãy 
số. 
– Ví dụ: Nếu A1:A4 chứa các dữ liệu là 3,1,5,7 thì 
RANK(A1,$A$1:$A$4,0) bằng 3, trong khi đó 
RANK(A1,$A$1:$A$4,1) bằng 2. 
5 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
5. Hàm COUNT(value1, value2,...) 
Trả về số lượng các ô có kiểu số trong các đối số. 
Ví dụ: Nếu A1:A4 chứa các dữ liệu là: 1, "test", 5, 7 
thì COUNT(A1:A4) bằng 3. 
6 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
6. Hàm COUNTIF(vùng địa chỉ, điều kiện) 
Trả về số lượng các ô trong vùng địa chỉ thỏa mãn 
điều kiện. 
Điều kiện phải đặt trong cặp dấu nháy kép "..." 
Ví dụ: Cho vùng địa chỉ như sau: 
hàm COUNTIF(A1:B3,">=4") bằng 5 
7 
Nhóm hàm thống kê 
A B 
1 9 3 
2 4 4 
3 7 5 
GV. Thiều Quang Trung 
7. Hàm COUNTIFS(vùng đk1, đk1, [vùng đk2, đk2], ) 
Đếm các ô trong vùng thỏa mãn 1 hoặc nhiều điều kiện. 
vùng đk1: bắt buộc, là một vùng các ô để so sánh với điều 
kiện đếm 
đk1: bắt buộc, là điều kiện để đếm, giá trị của đk1 có thể 
là số, biểu thức, cột tham chiếu, chuỗi 
vùng đk2, đk2,  là tùy chọn, không bắt buộc 
8 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
Nhóm hàm thống kê 
• Ví dụ hàm COUNTIF và COUNTIFS 
GV. Thiều Quang Trung 9 
A B C D E F G H 
1 Mã khách Loại phòng 
Kiểu thanh 
toán 
Ngày đến Ngày đi Số Tuần Số ngày Tiền Trả 
2 001 C Trả sau 27/08/2010 13/09/2010 2 3 1,300,000 
3 002 B Trả sau 31/08/2010 14/09/2010 2 0 1,600,000 
4 003 C Trả trước 05/09/2010 10/09/2010 0 5 500,000 
5 004 B Trả sau 29/09/2010 17/10/2010 2 4 2,240,000 
6 005 A Trả sau 17/09/2010 27/09/2010 1 3 1,600,000 
7 006 C Trả sau 22/09/2010 01/10/2010 1 2 700,000 
8 007 C Trả trước 09/10/2010 09/10/2010 0 0 0 
9 008 B Trả sau 18/10/2010 24/10/2010 0 6 960,000 
10 009 B Trả trước 19/10/2010 25/10/2010 0 6 960,000 
11 010 A Trả trước 12/10/2010 14/10/2010 0 2 400,000 
• Đếm các khách thuê phòng loại B 
 = COUNTIF(B2:B11,"B") 
• Đếm các khách thuê phòng loại B và kiểu trả sau: 
 =COUNTIFS(B2:B11,"B",C2:C11,"Trả sau") 
• Đếm các khách thuê phòng loại B và kiểu trả sau, có 
ngày đến trong tháng 9: 
 =COUNTIFS(B2:B11,"B",C2:C11,"Trả sau", 
D2:D11,">=2010/09/01",D2:D11,"<=2010/09/30") 
10 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
So sánh hàm COUNTIF() và COUNTIFS() 
COUNTIF() COUNTIFS() 
2 đối số bắt buộc: 
 range và criterial 
2 đối số đầu tiên là bắt 
buộc: range và criterial 
Các đối số còn lại là tùy 
chọn 
Đếm chỉ với một điều kiện Đếm với 1 hoặc nhiều điều 
kiện 
11 GV. Thiều Quang Trung 
8. Hàm SUM(n1,n2,...) 
Trả về tổng của các đối số n1, n2... 
Các đối số có thể thay bằng địa chỉ khối của vùng dữ 
liệu cần tính tổng. 
Ví dụ: 
SUM(3,5) bằng 8. 
SUM(A1:A5) sẽ trả về tổng giá trị của các ô từ A1 
đến A5. 
12 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
9. Hàm SUMIF(vùng điều kiện,điều kiện, vùng tổng) 
Trả về tổng giá trị số: nếu các ô trong vùng điều kiện thoả mãn 
điều kiện, hàm sẽ trả về tổng giá trị số trong các ô tương ứng 
trong vùng tổng. 
Chú ý: điều kiện phải đặt trong cặp dấu nháy kép "...". 
Ví dụ: SUMIF(A1:A3,">=6",B1:B3) bằng 8, giả sử có các dữ liệu 
ở địa chỉ sau: 
13 
Nhóm hàm thống kê 
A B 
1 9 3 
2 4 4 
3 7 5 
GV. Thiều Quang Trung 
10. Hàm SUMIFS(vùng tổng, vùng đk1, đk1, [vùng đk2, 
đk2], ) 
Tính tổng các ô trong thỏa mãn 1 hoặc nhiều 
điều kiện. 
vùng tổng: bắt buộc, là vùng các ô để tính tổng 
vùng đk1: bắt buộc, là vùng các ô để so sánh với điều kiện 
tính tổng 
đk1: bắt buộc, là điều kiện để tính tổng, giá trị của đk1 có thể 
là số, biểu thức, cột tham chiếu, chuỗi 
vùng đk2, đk2,  là tùy chọn, không bắt buộc 
14 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
Nhóm hàm thống kê 
• Ví dụ hàm SUMIF và SUMIFS 
GV. Thiều Quang Trung 15 
A B C D E F G H 
1 Mã khách Loại phòng 
Kiểu thanh 
toán 
Ngày đến Ngày đi Số Tuần Số ngày Tiền Trả 
2 001 C Trả sau 27/08/2010 13/09/2010 2 3 1,300,000 
3 002 B Trả sau 31/08/2010 14/09/2010 2 0 1,600,000 
4 003 C Trả trước 05/09/2010 10/09/2010 0 5 500,000 
5 004 B Trả sau 29/09/2010 17/10/2010 2 4 2,240,000 
6 005 A Trả sau 17/09/2010 27/09/2010 1 3 1,600,000 
7 006 C Trả sau 22/09/2010 01/10/2010 1 2 700,000 
8 007 C Trả trước 09/10/2010 09/10/2010 0 0 0 
9 008 B Trả sau 18/10/2010 24/10/2010 0 6 960,000 
10 009 B Trả trước 19/10/2010 25/10/2010 0 6 960,000 
11 010 A Trả trước 12/10/2010 14/10/2010 0 2 400,000 
• Tính tổng tiền trả cho các khách thuê phòng loại B 
 = SUMIF(B2:B11,"B",H2:H11) 
• Tính tổng tiền trả cho các khách thuê phòng loại B và 
kiểu trả sau: 
 =SUMIFS(H2:H11,B2:B11,"B",C2:C11,"Trả sau") 
• Tính tổng tiền trả cho các khách thuê phòng loại B và 
kiểu trả sau, có ngày đến trong tháng 9: 
 =SUMIFS(H2:H11,B2:B11,"B",C2:C11,"Trả 
sau",D2:D11,">=2010/09/01",D2:D11,"<=2010/09/30") 
16 
Nhóm hàm thống kê 
GV. Thiều Quang Trung 
So sánh hàm SUMIF() và SUMIFS() 
SUMIF() SUMIFS() 
Tính tổng chỉ với một điều 
kiện 
Tính tổng với 1 hoặc nhiều 
điều kiện 
Các ô tính tổng (vùng tổng) 
là đối số cuối cùng trong 
hàm SUMIF() và là tùy chọn 
(không bắt buộc) 
Các ô tính tổng (vùng tổng) 
là đối số đầu tiên của hàm 
SUMIFS(), và là bắt buộc 
phải có 
17 GV. Thiều Quang Trung 
Các khái niệm công thức mảng 
• Công thức mảng (Array Formulas) là công thức có 
thể thực hiện nhiều phép tính đối với một hoặc 
nhiều mục trong mảng 
• Mảng là một hàng hoặc một cột các ô chứa giá trị, 
hoặc kết hợp nhiều hàng và nhiều cột các ô chứa giá 
trị 
• Khi nhập công thức mảng, cần nhấn tổ hợp phím 
Ctrl+Shift+Enter để hoàn tất công thức => Khi đó 
công thức sẽ được bao bọc bởi hai dấu ngoặc {} 
18 GV. Thiều Quang Trung 
Các khái niệm 
• Ví dụ: 
Công thức mảng {=SUM(B2:D2*B3:D3)} tương đương 
với công thức: B2*B3 + C2*C3 + D2*D3 
19 GV. Thiều Quang Trung 
Các khái niệm 
• Công thức mảng có thể trả về nhiều kết quả 
hoặc một kết quả duy nhất => Công thức 
mảng bao gồm nhiều ô được gọi là công thức 
đa ô và công thức mảng trong một ô duy 
nhất được gọi là công thức đơn ô 
20 GV. Thiều Quang Trung 
Các khái niệm 
• Công thức mảng trả về kết quả nhiều ô 
21 GV. Thiều Quang Trung 
Các khái niệm 
• Công thức mảng trả về kết quả một ô: 
22 GV. Thiều Quang Trung 
Các khái niệm 
• So sánh với hàm SUMPRODUCT: 
23 GV. Thiều Quang Trung 
Ưu điểm của công thức mảng 
• Nhất quán => giúp đảm bảo độ chính xác 
24 GV. Thiều Quang Trung 
Ưu điểm của công thức mảng 
• An toàn => không thể xóa ô của công thức 
mảng đa ô 
25 GV. Thiều Quang Trung 
Ưu điểm của công thức mảng 
• Kích cở tập tin nhỏ hơn => loại trừ những 
công thức trung gian 
26 GV. Thiều Quang Trung 
Hằng số mảng 
• Hằng số mảng là một thành phần của công 
thức mảng, được nhập bằng cách nhập danh 
sách các mục, sau đó đặt danh sách này trong 
một cặp dấu ngoặc nhọn ({ }), ví dụ: 
={1,2,3,4,5}. Sau đó nhấn Ctrl+Shift+Enter để 
hoàn tất công thức 
27 GV. Thiều Quang Trung 
Hằng số mảng 
• Nếu phân cách các mục bằng dấu phẩy (,) => 
sẽ tạo ra mảng ngang (hàng) 
28 GV. Thiều Quang Trung 
Hằng số mảng 
• Nếu phân cách các mục bằng dấu chấm phẩy 
(;) => sẽ tạo ra mảng dọc (cột) 
29 GV. Thiều Quang Trung 
Hằng số mảng 
• Để tạo mảng hai chiều => phân cách các mục 
trong mỗi hàng bằng dấu phẩy và phân cách 
các hàng bằng dấu chấm phẩy 
30 GV. Thiều Quang Trung 
Hằng số mảng 
• Hàm TRANSPOSE để chuyển đổi hàng thành 
cột và ngược lại 
31 GV. Thiều Quang Trung 
Hằng số mảng 
• Tính bình phương mảng 
32 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Tìm doanh số của 3 tháng thấp nhất 
• Công thức mảng {=SUM(SMALL(A2:K2,{1,2,3}))} 
tương đương với = SMALL(A2:K2,1) + 
SMALL(A2:K2,2) + SMALL(A2:K2,3) = 10 + 15 + 20 
33 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Loại trừ lỗi #DIV/0! 
34 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Tổng hợp theo nhiều điều kiện của mảng 
35 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Thay cho hàm đếm có nhiều điều kiện COUNTIFS 
36 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Thay cho hàm SUMPRODUCT 
37 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Đếm số ô rỗng trong bảng tính 
• Dùng công thức mảng: 
{=SUM(IF(ISBLANK(range), 1, 0))} với range là 
vùng dữ liệu cần kiểm tra. 
38 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Đếm số ô chứa những giá trị không phải là 
kiểu số 
• Dùng công thức mảng: 
{=SUM(IF(ISNUMBER(range), 0, 1))} với range 
là vùng dữ liệu cần kiểm tra. 
39 GV. Thiều Quang Trung 
Ứng dụng công thức mảng 
• Đếm số ô bị lỗi 
• Dùng công thức mảng: 
{=SUM(IF(ISERROR(range), 1, 0))} với range là 
vùng dữ liệu cần kiểm tra. 
40 GV. Thiều Quang Trung 
GV. Thiều Quang Trung 41 
Câu 1: Cho bảng số liệu sau: 
Công thức SUMIF(D31:D35, >=50000, C31:C35) 
cho kết quả là: 
a. 17 
b. 25 
c. 13 
d. Báo lỗi vì công thức sai 
 A B C D 
30 Họ và tên Chức vụ Thâm niên Lương 
31 Nguyễn Văn An TP 8 100000 
32 Nguyễn Văn Bê TP 5 50000 
33 Trần Kim Chi PP 4 55000 
34 Lê Thùy Dung KT 6 45000 
35 Lương Thế Vinh NV 2 30000 
GV. Thiều Quang Trung 42 
Câu 2: Cho bảng số liệu sau: 
Công thức COUNTIF(A31:C35, ">=50000") cho kết 
quả là: 
a. 17 
b. 13 
c. 0 
d. Báo lỗi vì công thức sai 
 A B C D 
30 Họ và tên Chức vụ Thâm niên Lương 
31 Nguyễn Văn An TP 8 100000 
32 Nguyễn Văn Bê TP 5 50000 
33 Trần Kim Chi PP 4 55000 
34 Lê Thùy Dung KT 6 45000 
35 Lương Thế Vinh NV 2 30000 
GV. Thiều Quang Trung 43 
Câu 3: Cho bảng số liệu sau: 
Công thức SUMIF(D31:D35, ">=50000", D31:D35) 
cho kết quả là: 
a. 205000 
b. 155000 
c. 0 
d. báo lỗi vì công thức sai 
 A B C D 
30 Họ và tên Chức vụ Thâm niên Lương 
31 Nguyễn Văn An TP 8 100000 
32 Nguyễn Văn Bê TP 5 50000 
33 Trần Kim Chi PP 4 55000 
34 Lê Thùy Dung KT 6 45000 
35 Lương Thế Vinh NV 2 30000 
GV. Thiều Quang Trung 44 
Câu 4: Cho bảng số liệu sau: 
Công thức SUMIFS(D31:D35,C31:C35,">4", 
B31:B35,”>TP") cho kết quả là: 
a. 205000 
b. 155000 
c. 150000 
d. 0 
 A B C D 
30 Họ và tên Chức vụ Thâm niên Lương 
31 Nguyễn Văn An TP 8 100000 
32 Nguyễn Văn Bê TP 5 50000 
33 Trần Kim Chi PP 4 55000 
34 Lê Thùy Dung KT 6 45000 
35 Lương Thế Vinh NV 2 30000 
GV. Thiều Quang Trung 45