Bài giảng Bảng tính điện tử MS Excel - Nội dung 3: Tính toán trong bảng. Các hàm mẫu trong Excel (Tiết 2) - Ngô Thùy Linh

30 
29 
28 
27 
26 
25 
24 
23 
22 
21 
20 
19 
18 
17 
16 
15 
14 
13 
12 
11 
10 
09 
08 
07 
06 
05 
04 
03 
02 
01 
00 
TÍNH TOÁN Trong BẢNG-CÁC HÀM MẪU Trong EXCEL 
GV: NGÔ THÙY LINH 
CHỦ ĐỀ 5: BẢNG TÍNH ĐIỆN TỬ MS EXCEL 
(TIẾT 2) 
NỘI DUNG 3:  
NỘI DUNG 
HÀM COUNTIF 
HÀM SUMIF 
HÀM RANK 
CỦNG CỐ 
2 
Sumif 
1 
3 
Rank 
1 
Countif 
1. Cú pháp 
2. Chức năng 
Nội dung thảo luận nhóm 
1. Cú pháp 
2. Chức năng 
1. Cú pháp 
2. Chức năng 
7 . HÀM COUNTIF: ĐẾM GIÁ TRỊ THỎA MÃN ĐIỀU KIỆN 
RANGE 
CRITERIA 
a. CÚ PHÁP: 
=COUNTIF (RANGE , CRITERIA ) 
=COUNTIF (PHẠM VI , ĐIỀU KIỆN ) 
b . CÔNG DỤNG: 
Đếm số ô thỏa mãn điều kiện trong phạm vi. 
 Điều kiện là một con số chính xác hoặc địa chỉ thì không đặt trong nháy kép. Các trường hợp còn lại điều kiện đặt trong nháy kép. 
Điều kiện không phân biệt chữ hoa chữ thường 
Điều kiện là tham chiếu ô : Phải đặt toán tử trong dấu ngoặc kép và thêm ký hiệu (&) trước tham chiếu ô. Ví dụ: =COUNTIF(D2:D9,”>”&D3) 
Hàm COUNTIF hỗ trợ các toán tử logic : (>, , =) 
Sử dụng các ký tự đại diện : ? : Đại diện 1 ký tự, * : Đại diện nhiều ký tự 
Haøm CountIf 
Giải thích : 
Range : một dãy các ô cần đếm, giá trị các ô có thể là số, ngày, text , hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua không đếm. 
C riteria : điều kiện để đếm (số, chuỗi,tham chiếu ô, biểu thức, ). 
Chú ý: 
Trường hợp 1: Hàm COUNTIF cho văn bản và số (chính xác tuyệt đối) 
Haøm CountIf 
= COUNTIF (D2: D9,5)   đếm các ô trong vùng D2:D5 có giá trị 5 
= COUNTIF (C2: C15, “Táo”) . 
  Đếm các ô trong vùng C2:C15 có giá trị là Táo 
Haøm CountIf 
 Trường hợp 2: Hàm COUNTIF đếm lớn hơn, nhỏ hơn hoặc bằng 
Điều kiện 
Ví dụ về công thức 
Giải thích 
lớn hơn 
=COUNTIF(A2:A10,”> 5″) 
Đếm các ô trong đó giá trị lớn hơn 5. 
nhỏ hơn 
=COUNTIF(A2:A10,”<5″) 
Đếm các ô có giá trị nhỏ hơn 5. 
bằng 
=COUNTIF (A2:A10,”= 5″) 
Đếm các ô trong đó giá trị bằng 5. 
không bằng 
=COUNTIF(A2:A10,”5″) 
Đếm các ô trong đó giá trị không bằng 5. 
lớn hơn hoặc bằng 
=COUNTIF(C2:C8, “> = 5”) 
Đếm các ô trong đó giá trị lớn hơn hoặc bằng 5. 
nhỏ hơn hoặc bằng 
=COUNTIF(C2:C8,”<=5″) 
Đếm các ô trong đó giá trị nhỏ hơn hoặc bằng 5. 
Haøm CountIf 
Trường hợp 3: Hàm COUNTIF sử dụng các kí tự đại diện 
= COUNTIF (C2: C10, “Mr*”)  – tính các ô bắt đầu bằng “Mr”. 
= COUNTIF (C2: C10, “* ed”)  – tính các ô kết thúc bằng chữ “ed ”. 
= COUNTIF (D2: D9, “??own”)  – đếm số ô kết thúc bằng chữ “own” và có chính xác 5 ký tự trong các ô từ D2 đến D9 
Điều kiện 
Công thức ví dụ 
Diễn giải 
Đếm một ngày cụ thể 
=COUNTIF(B2:B10,” 6/1/2020″) 
Đếm số ô trong vùng B2:B10 có ngày 6/1/2020 
Đếm ngày lớn hơn hoặc bằng một ngày khác 
=COUNTIF(B2:B10,”>= 6/1/2020″) 
Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng 6/1/20 20 
Đếm ngày lớn hơn hoặc bằng ngày trong một ô khác trừ đi x ngày 
=COUNTIF(B2:B10,”>=”&B2-“7”) 
Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng ngày trong ô B2 trừ đi 7 ngày 
Haøm CountIf 
Trường hợp 4: Sử dụng hàm COUNTIF với ngày: 
Ví dụ 1: Cho bảng lương sau: 
Đếm số nhân viên có Chức vụ là TP 
 =COUNTIF(E3:E12, “TP”) 
Đếm số nhân viên có họ Trần 
Đếm số nhân viên có ngày làm việc trên 25 ngày 
=COUNTIF(B3:B12, “Trần*”) 
=COUNTIF(G3:G12,”>25”) 
=COUNTIF(F3:F12, 2500) 
Đếm số nhân viên có Lương CB là 2500 
Đếm số nhân viên có Chức vụ là TP 
 =COUNTIF(E3:E12, “TP”) 
Đếm số nhân viên có họ Trần 
Đếm số nhân viên có ngày làm việc trên 25 ngày 
=COUNTIF(B3:B12, “Trần*”) 
=COUNTIF(G3:G12,”>25”) 
=COUNTIF(F3:F12, 2500) 
Đếm số nhân viên có Lương CB là 2500 
Ví dụ 1: Cho bảng lương sau: 
8. HÀM SUMIF ( Tính tổng thỎA mãn theo điều kiện)  
RANGE 
CRITERIA 
SUM 
RANGE 
a. CÚ PHÁP: 
= SUMIF (RANGE , CRITERIA , SUM RANGE ) 
=SUMIF (VÙNG ĐIỀU KIỆN , ĐIỆU KIỆN , VÙNG TÍNH TỔNG ) 
b . CÔNG DỤNG 
Hàm dùng để tính tổng có điều kiện. Chỉ những ô nào trên vùng chứa điều kiện thoả mãn điều kiện thì sẽ tính tổng những ô tương ứng trên vùng cần tính tổng. 
Haøm SumIf 
Giải thích : 
 Range: Vùng dữ liệu có chứa điều kiện cần tính tổng. 
Criteria: Là điều kiện cần tính tổng 
Sum_range : Vùng cần tính tổng thoã mãn theo điều kiện. 
 Kết quả của hàm là tính tổng các giá trị thoã mãn theo điều kiện . 
Chú ý: Tương tự hàm COUNTIF 
 Điều kiện là một con số chính xác hoặc địa chỉ thì không đặt trong nháy kép. Các trường hợp còn lại điều kiện đặt trong nháy kép. 
Điều kiện không phân biệt chữ hoa chữ thường 
Điều kiện là tham chiếu ô : Phải đặt toán tử trong dấu ngoặc kép và thêm ký hiệu (&) trước tham chiếu ô. 
H ỗ trợ các toán tử logic : (>, , =) 
Sử dụng các ký tự đại diện : ? : Đại diện 1 ký tự, 
 * : Đại diện nhiều ký tự 
1. Tính tổng lương cho cho nhân viên có giới tính là Nam 
2. Tính tổng thực lĩnh cho nhân viên có lương >7,000,000 
Ví dụ 2 
Ví dụ 2: 
9. HÀM RANK: XẾP THỨ HẠNG CHO DANH SÁCH 
NUMBER 
REF 
[ORDER] 
a. CÚ PHÁP: 
= RANK( NUMBER , REF , [ORDER] ) 
= Rank(Số cần xác định thứ hạng , Vùng quy chiếu , Số đặc tả cách xếp hạng ) 
b. CÔNG DỤNG: 
Hàm RANK là hàm trả về thứ hạng của một số trong danh sách các số, thứ hạng của một số là thứ hạng giá trị của nó so với các giá trị khác trong danh sách số 
Haøm Rank 
Giải thích : 
 Number: Là giá trị cần xếp vị thứ bao nhiêu trong dãy dữ liệu 
 Ref: Vùng dữ liệu chứa giá trị Number để xếp vị thứ 
 Order : C ó hai giá trị là 0 hay 1, Mặc định là 0. 
Nếu Order=1 thì giá trị nhỏ nhất đựơc xếp thứ nhất 
Nếu Order=0 thì giá trị lớn nhất được xếp thứ nhất 
 Kết quả: Là giá trị đứng thứ mấy trong dãy số trên . 
Haøm Rank 
1. Xếp thứ hạng cho danh sách theo cột Lương giảm dần 
=RANK(D4,$D$4:$D$8,0) 
Ví dụ 3. Cho bảng tính sau: 
Thể lệ : 
Mỗi đội thi thực hành bài tập excel trên máy tính. Trong thời gian 5 phút đội nào có kết quả đúng nhiều nhất và nhanh nhất sẽ là đội chiến thắng 
TĂNG TỐC 
PHIẾU HỌC TẬP 
Xếp hạng danh sách theo tiêu chí điểm trung bình giảm dần 
CỦNG CỐ BÀI HỌC 
01 
02 
03 
= RANK( NUMBER , REF , [ORDER] ) 
XẾP THỨ HẠNG CHO DANH SÁCH 
= SUMIF (RANGE , CRITERIA , SUM RANGE ) 
TÍNH TỔNG CÓ ĐIỀU KIỆN 
=COUNTIF (RANGE , CRITERIA ) 
ĐẾM CÓ ĐIỀU KIỆN 
Thể lệ : 
Mỗi đội lần lượt trả lời câu hỏi. Tr ư ớc khi trả lời mỗi đội quay vào ô may mắn để chọn điểm. Nếu trả lời sai bị mất điểm đội khác có quyền trả lời và đ ư ợc điểm. 
VỀ ĐÍCH 
Points 
Spin Wheel 
10 
20 
30 
40 
50 
60 
70 
80 
Team 1 
Team 2 
Team 3 
Team 4 
VỀ ĐÍCH 
30 
29 
28 
27 
26 
25 
24 
23 
22 
21 
20 
19 
18 
17 
16 
15 
14 
13 
12 
11 
10 
09 
08 
07 
06 
05 
04 
03 
02 
01 
00 
1 
2 
3 
4 
5 
6 
7 
8 
9 
A. =SUMIF(C2:C14,”<5”, D2:D14) 
B. =SUMIF(D2:D14,<5,C2:C14) 
C. =SUMIF(D2:D14,”<5 năm”,C2:C14) 
D. = SUMIF(D2:D14,”<5”,C2:C14) 
Tính Tổng thực lĩnh của nhân viên không phải phòng Kinh doanh: 
 =Sumif( E6:E15, “Kinh doanh” ,G6:G15) 
điền vào chỗ trống ? 
Đếm số học sinh học lực Khá trong danh sách có công thức là =COUNTIF(D1:D11,Giỏi). Theo em công thức này: 
Đúng 
Sai 
Trong cú pháp hàm: 
=RANK(NUMBER, REF, [ORDER]) 
thì đối số REF là: 
 A. Cách xếp thứ hạng 
 B. Số cần biết thứ hạng 
 C. Vùng quy chiếu 
 D. Các ý trên đều sai 
Căn cứ vào kết quả của cột thứ hạng em hãy cho biết công thức tại ô C2 sẽ là 
=RANK(C2, $C$2:$C$7,0) 
= RANK(C2, $C$2:$C$7,1) 
Công thức tính số nhân viên có ngày nghỉ trong tháng là: .. 
Từ khóa