Giáo trình Excel - Viện Đại Học Mở

. 
TRUNC(x) 
Cắt bỏ phần thập phân của số x để chỉ lấy phần nguyên. 
Ví dụ : = TRUNC(2.789) trả về 2 
 = TRUNC(-2.4) trả về -2, trong khi đó = INT(-2.4) trả về -3 
3.4. Hàm Logic 
AND(logic1,logic2,..) 
Nhận giá trị TRUE (Đúng) nếu tất cả các biểu thức logic1, logic2,... đều 
là TRUE, nhận giá trị FALSE (Sai) nếu có ít nhất một đối số là FALSE 
Ví dụ : =AND(5>3,6>4) trả về TRUE 
 =AND(5>3,6<=4) trả về FALSE 
FALSE( ) 
Cho giá trị logic FALSE. 
IF(logical_test,value_if_true,value_if_false) 
Trả lại giá trị ghi trong value_if_true nếu logical_test là TRUE và giá trị 
ghi trong value_if_false trong tr−ờng hợp ng−ợc lại. Hàm IF có thể lồng 
nhau đến 7 cấp. 
Ví dụ : Giả sử trong ô B4 ghi tuổi của một ng−ời, khi đó công thức 
 = IF(B4>=16,"Ng−ời lớn","Trẻ em") cho kết quả là Ng−ời lớn 
nếu tuổi từ 16 trở lên, là Trẻ em nếu tuổi d−ới 16 
Để kiểm tra xem ô D2 chứa số nào dùng 
 = IF(D2>0,"Số d−ơng", IF(D2=0,"Số không",Số âm")) 
NOT(logic) : hàm phủ định. 
Ví dụ : = NOT(1+1>2) trả về TRUE 
 = NOT(1+1=2) trả về FALSE 
OR(logic1,logic2,...) 
Nhận giá trị TRUE nếu một trong các biểu thức logic1,logic2,.. là 
TRUE, nhận giá trị FALSE nếu tất cả các biểu thức đó là FALSE. 
Ví dụ : =OR(5>3,6<=4) trả về TRUE 
 =OR(5<3,6<=4) trả về FALSE 
Khoa CNTT - VĐH Mở Excel 35 
TRUE( ) : nhận giá trị logic TRUE. 
3.5. Hàm thống kê 
AVERAGE(num1, num2,..) 
Tính trung bình cộng của các số num1, num2,... 
Ví dụ : =AVERAGE(10,8,9,3) trả về 7.5 
COUNT(Địa chỉ) : tính số các ô dữ liệu kiểu số trong miền Địa chỉ 
Ví dụ : Các ô từ A1 đến E5 chứa các giá trị sau : 
 A B C D E 
1 STT Tên sách Số l−ợng Đơn giá Thành tiền 
2 1 Excel 5.0 10 15000 150000 
3 2 Word 6.0 15 14000 210000 
4 3 Access 5 12000 60000 
5 Cộng 30 420000 
khi đó, công thức 
= COUNT(A1:E5) trả về 14 
COUNTA(DC) 
Tính số các ô không rỗng trong vùng DC. 
Ví dụ : = COUNTA(A1:E5) trả về 23 
 (có 2 ô rỗng là A5 và D5) 
LARGE(DC,k) : Phần tử lớn thứ k trong vùng DC. 
Ví dụ : = LARGE(E1:E5,1) trả về 420000 
 = LARGE(E1:E5,3) trả về 150000 
MAX(num1,num2,..) : giá trị lớn nhất của các số num1,num2,.. 
Ví dụ : = MAX(E1:E5) trả về 420000 
MIN(num1,num2,..) : giá trị nhỏ nhất của các số num1,num2,.. 
Ví dụ : = MIN(E1:E5) trả về 60000 
MODE(DC) : trả lại giá trị hay gặp nhất trong vùng DC. 
Ví dụ : = MODE(1,2,4,5,2) trả về 2 
RANK(số, Danh sách, Tuỳ chọn) : xác định thứ hạng của số so với chuỗi 
các số trong danh sách, tức là xem số đó đứng thứ mấy trong chuỗi số 
- xếp giảm dần nếu không có Tuỳ chọn hoặc Tuỳ chọn bằng 0 (Ví dụ 1). 
- xếp tăng dần nếu Tuỳ chọn là một số lớn hơn 0 (Ví dụ 2). 
Địa chỉ của Danh sách phải là tuyệt đối. 
Khoa CNTT - VĐH Mở Excel 36 
Ví dụ : 1. Giả sử ở các ô E3:E12 ghi điểm trung bình của các thí sinh, 
để xếp thứ căn cứ vào điểm trung bình, tại ô F3 nhập 
=RANK(E3,$E$3:$E$12) hoặc =RANK(E3,$E$3:$E$12,0), 
sau đó copy công thức này xuống các ô từ E4 đến E12. 
Ví dụ : 2. Giả sử ở các ô B3:B12 ghi thời gian chạy 100 m của các vận 
đọng viên, để xếp thứ căn cứ vào thời gian, tại ô C3 nhập 
=RANK(B3,$B$3:$B$12,1), sau đó copy công thức này 
xuống các ô từ C4 đến C12. Có thể thay số tham số thứ 3 
bằng một số bất kỳ khác 1. 
SMALL(DC,k) : phần tử nhỏ thứ k trong vùng DC. 
Ví dụ : = SMALL(E1:E5,1) trả về 60000 
 = SMALL(E1:E5,3) trả về 210000 
3.6. Hàm Tìm kiếm và Tham chiếu 
vlookup(lookup_value, table_array, col_index_num, range_lookup) 
Lookup_value Giá trị đ−ợc tìm kiếm trên cột bên trái của Table_array 
Table_array Vùng tìm kiếm hay còn gọi là bảng tra cứu, địa chỉ phải là 
tuyệt đối, nên đặt tên cho vùng (xem trang 11 về cách đặt 
tên). 
Col_index_num Số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá 
trị trả về 
Range_lookup Giá trị logic xác định việc tìm kiếm là chính xác hay gần 
đúng, nếu là: 
True hay 1 Cột đầu tiên phải đ−ợc sắp xếp tăng dần (khi đó có 
thể bỏ qua tham số thứ 4 này). 
 Khi không thấy sẽ lấy kết quả gần đúng, vì thế còn gọi 
là dò tìm không chính xác. 
False hay 0 Cột đầu tiên không cần sắp xếp .Tìm chính xác, trả về 
#N/A nếu không thấy. (Xem ví dụ ở trang 72) 
Ví dụ : Bảng sau là kết quả thi của học sinh, dựa vào điểm Trung bình 
hãy xếp loại học sinh theo thang điểm : 
Kém 5 Trung bình 7 Khá 8 Giỏi 9.5 Xuất sắc 
Để dùng hàm VLOOKUP, ta cần thực hiện các b−ớc sau : 
- Trong miền C15:D19 gõ vào thang điểm trên d−ới dạng cột. Vì đây là 
cách dò tìm không chính xác (trong một khoảng) nên chỉ gõ vào cận d−ới 
(theo chiều tăng) của mỗi loại. 
Nh− vậy : 
lookup_value là E3 (điểm Trung bình của học sinh thứ nhất) 
table_array là miền $C$15: $D$19 (miền địa chỉ tuyệt đối, không đ−a 
hàng tiêu đề C14: D14 vào) 
Khoa CNTT - VĐH Mở Excel 37 
col_index_num là 2 vì cần lấy giá trị của cột Loại, cột này có số thứ tự là 
2 trong miền $C$14: $D$19. 
range_lookup là 1 hoặc TRUE đều đ−ợc, có thể bỏ qua tham biến này 
- Tại ô G3 điền vào công thức = VLOOKUP(E3,$C$14: $D$19,2) ta nhận 
đ−ợc Trung bình 
- Copy công thức ở ô G3 xuống các ô từ G4 đến G12, Excel sẽ xếp loại cho 
các học sinh còn lại. 
 A B C D E F G 
2 TT Tên Toán Tin Trung bình Xếp thứ Xếp loại 
3 1 Hùng 4 7 5.5 
4 2 Bình 6 8 7.0 Khá 
5 3 Vân 8 9 8.5 Giỏi 
6 4 Bình 9 10 9.5 Xuất sắc 
7 5 Doanh 5 8 6.5 Trung bình
8 6 Loan 5 4 4.5 Kém 
9 7 Anh 9 6 7.5 Khá 
10 8 Thu 3 5 4.0 Kém 
11 9 Khánh 6 7 6.5 Trung bình
12 10 Ngân 10 8 9.0 Giỏi 
13 
14 Điểm Loại 
15 0 Kém 
16 5 Trung bình 
17 7 Khá 
18 8.5 Giỏi 
19 9.5 Xuất sắc 
20 
21 Điểm 0 5 7 8.5 9.5 
22 Loại Kém Trung bình Khá Giỏi Xuất sắc 
hlookup(lookup_value, table_array, 
row_index_num,range_lookup) 
Hàm này hoạt động giống nh− hàm VLOOKUP, điểm khác là : 
Lookup_value Giá trị đ−ợc tìm kiếm trên hàng đầu tiên của Table_array 
Table_array Vùng tìm kiếm viết thành hàng 
Row_index_num Số thứ tự hàng trong table_array, nơi HLOOKUP sẽ lấy 
giá trị trả về 
- Trong miền A21: F22 ta gõ vào thang điểm trên d−ới dạng hàng, chú ý 
chỉ gõ vào cận d−ới (theo chiều tăng) của mỗi loại. 
Nh− vậy : 
lookup_value là E3 (điểm Trung bình của học sinh thứ 1) 
= VLOOKUP(E3,$C$14: $D$19,2) 
Khoa CNTT - VĐH Mở Excel 38 
table_array là miền $B$21: $F$22 (miền địa chỉ tuyệt đối, không đ−a 
cột tiêu đề A21: A22 vào) 
row_index_num là 2 vì cần lấy giá trị của hàng Loại, hàng này có số thứ tự 
là 2 trong miền B21: F22. 
- Tại ô F3 điền vào công thức = HLOOKUP(E3,$B$21: $F$22,2), ta nhận 
đ−ợc Trung bình 
- Copy công thức ở ô G3 xuống các ô từ G4 đến G12, Excel sẽ xếp loại cho 
các học sinh còn lại. 
3.7. Hàm Cơ sở dữ liệu 
Xem mục IX. 4. Các hàm CSDL (trang 55) 
v. đồ thị 
Khả năng biểu diễn số liệu bằng đồ thị của Excel rất phong phú. Các 
biểu đồ đ−ợc cài đặt trên bảng tính tăng thêm sức hấp dẫn và thuyết phục 
của số liệu. 
1. Các b−ớc tạo đồ thị mới 
1 - Chọn miền dữ liệu cần đ−a vào vẽ đồ thị, ví dụ B2:F6, chú ý chọn cả 
hàng tiêu đề 1992, 1993,... và cột tên các bảng tính để làm nhãn cho 
đồ thị. 
1 -Chọn biểu t−ợng (ChartWizard), con trỏ chuột trở thành dấu + 
2 -Xác định miền hình chữ nhật tại vùng trống của bảng tính để đ−a đồ thị 
vào bằng cách trỏ chuột vào đỉnh trái trên của nó, ấn và giữ nút trái đồng 
thời kéo chuột về đỉnh phải d−ới, thả nút chuột, xuất hiện hộp thoại 
ChartWizard - Step 1 of 5 
3 -Nếu cần, sửa lại địa chỉ của miền dữ liệu đ−a vào vẽ đồ thị trong 
khung Range bằng cách gõ trực tiếp hoặc dùng chuột chọn miền dữ 
liệu cần thiết (xem trang 13) 
Khoa CNTT - VĐH Mở Excel 39 
ý nghĩa các nút trong hộp thoại nh− sau : 
Cancel : huỷ bỏ việc vẽ đồ thị 
Next : chuyển sang b−ớc tiếp theo 
Back : quay trở lại b−ớc tr−ớc 
Finish : tự động thực hiện tất cả các b−ớc cho đến kết thúc 
Nếu chọn Next, xuất hiện hộp thoại ChartWizard - Step 2 of 5 với 
15 kiểu đồ thị của Excel (9 nhóm loại hai chiều và 6 nhóm loại 3 
chiều). 
5 -Chọn một kiểu đồ thị (ví dụ 3-D Column), có thể gõ ký tự có gạch 
chân của kiểu cần thiết rồi ấn ↵ .Chú ý rằng loại Pie chỉ áp dụng cho 
một dãy số, và th−ờng dùng để so sánh một với tất cả các yếu tố, ví 
dụ so sánh số hàng bán đ−ợc của 1 quý với cả năm. 
Chọn Next, xuất hiện hộp thoại ChartWizard - Step 3 of 5 với các 
dạng của kiểu đồ thị đ−ợc chọn. 
Khoa CNTT - VĐH Mở Excel 40 
6 -Chọn một dạng cho đồ thị, nếu dùng bàn phím, gõ số thứ tự của dạng 
cần chọn (ví dụ gõ số 4). 
 Chọn Next, xuất hiện hộp thoại ChartWizard - Step 4 of 5 với đồ 
thị của miền dữ liệu đ−ợc chọn. 
7 -Nếu cần thay đổi, sửa các thông số sau đây cho đồ thị : 
Data Series in : Vẽ dữ liệu theo hàng (Rows) hay Cột (Columns) 
Use First Colums (Rows): Sử dụng Cột (Hàng) thứ ... 
for Category [X] Axis Labels : làm tiêu đề cho trục X 
for Legend Text : làm văn bản cho chú thích. 
 Chọn Next, xuất hiện hộp thoại ChartWizard - Step 5 of 5. 
8 -Điền các thông số sau : 
Add a Legend : (Yes/No) : Có bổ sung chú thích của các dãy số liệu vào 
đồ thị không ? 
Khoa CNTT - VĐH Mở Excel 41 
Chart Titles : Tiêu đề chung của đồ thị 
Axis Titles : Tiêu đề của các trục 
Category [X] : Tiêu đề cho trục X 
Value [Z] : Tiêu đề cho trục Z (trong không gian 3 chiều) 
Sau các b−ớc trên, một đồ thị sẽ đ−ợc đ−a vào bảng tính tại vị trí đã định. 
2. Thiết lập lại đồ thị 
1 -Chọn đồ thị bằng cách trỏ chuột vào trong lòng nó và bấm nút trái. 
Xuất hiện khung bao quanh đồ thị với các chấm vuông ở các góc và 
giữa các cạnh. 
2 -Chọn biểu t−ợng ChartWizard, Excel lần l−ợt đ−a ta trở lại các b−ớc 
đã nêu trên. 
Khoa CNTT - VĐH Mở Excel 42 
3. Chỉnh sửa đồ thị 
a - Chỉnh sửa các đối t−ợng : 
1 -Trỏ chuột vào trong lòng đồ thị và nháy đúp, xuất hiện khung chữ 
nhật có cạnh là các sọc chéo với chấm vuông hoặc một cửa sổ riêng. 
2 -Để sửa kiểu đồ thị chọn Format, Chart Type 
Để sửa các đối t−ợng khác, nháy đúp tại đối t−ợng cần thiết (ví dụ tiêu 
đề chung của đồ thị), xuất hiện khung chữ nhật với chấm vuông tại 
các góc và giữa các cạnh. Chọn Format. Nội dung mục đầu tiên của 
menu dọc tuỳ thuộc việc tr−ớc đó đối t−ợng nào đ−ợc chọn, nh− 
trong v