Bài giảng Tin học đại cương - Chương 4: Phần mềm bảng tính điện tử MS Excel 2010 - Ngô Thùy Linh

NAME! Sai tên - Nhập sai tên một hàm số. 
- Dùng những ký tự không được phép trong công thức. 
- Nhập một chuỗi trong công thức mà không đặt trong dấu “ 
”. 
- Trong công thức không có dấu 2 chấm : trong dãy địa chỉ ô. 
Một số thông báo lỗi thường gặp 
10/19/2015 
89 Chương 4 - PM bảng tính điện tử MS EXCEL2010 
Ký hiệu Ý nghĩa Những trường hợp gây lỗi 
#N/A Lỗi dữ liệu - Không đồng nhất dữ liệu 
- Giá trị trả về không tương thích từ các hàm dò tìm 
#REF! Sai vùng tham 
chiếu 
- Xóa những ô đang được tham chiếu bởi công thức. 
- Dán những giá trị tạo ra từ công thức lên chính vùng 
tham chiếu đó. 
- Tham chiếu đến 1 ứng dụng không thể chạy được. 
#NUM! Lỗi dữ liệu 
kiểu số 
- Dùng một đối số không phù hợp trong công thức. 
- Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được 
kết quả trả về. 
- Dùng hàm trả về 1số quá lớn/nhỏ so với khả năng 
tính toán của Excel 
#NULL! Lỗi dữ liệu 
rỗng 
 Dùng một dãy toán tử không phù hợp 
Một số thông báo lỗi thường gặp 
Nhóm hàm ngày (Date & Time) 
HÀM 
NOW() Cho biết ngày, giờ hiện thời 
TODAY() Cho biết ngày hiện thời 
DAY(serial_number) Cho biết ngày của serial_number 
MONTH(serial_number) Cho biết tháng của serial_number 
YEAR(serial_number) Cho biết năm của serial_number 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 90 10/19/2015 
Nhóm hàm toán học (Math & trig) 
HÀM VÍ DỤ 
FACT(number) 
Tính giai thừa của number 
= FACT(4) 
Trả về kết quả 24 
INT(number) 
Lấy phần nguyên của number 
= INT(123.7) 
Trả về kết quả 123 
MOD(number,divisor) 
Phần dư của phép chia number cho divisor 
= MOD(23,5) 
Trả về kết quả 3 
SQRT(number) 
Căn bậc hai của number 
=SQRT(144) 
Trả về kết quả 12 
PRODUCT(number1, number2,...) 
Tích các số number1, number2,... 
=PRODUCT(9,8,7) 
Trả về kết quả 504 
SUM(number1, number2,...) 
Tổng các số number1, number2,... 
=SUM(15.8,35.6) 
Trả về kết quả 51.4 
ROUND(number, num_digits) 
Làm tròn số number với num_digits chữ số phần thập phân 
= ROUND(35.67, 1) 
Trả về kết quả 35.7 
LN(number) 
Tính ln 
=LN(1) 
Trả về kết quả 1 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 91 10/19/2015 
Nhóm hàm luận lý (Logical) 
HÀM VÍ DỤ 
AND(logical1,logical2,..) 
Cho trị TRUE nếu mọi đối số là TRUE, ngược lại 
cho trị là FALSE 
=AND(3=0) Trả về kết quả TRUE 
=AND(3>7,2>=0) Trả về kết quả FALSE 
OR(logical1,logical2,..) 
Cho trị TRUE nếu bất kỳ đối số nào là TRUE, cho 
trị là FALSE nếu tất cả đối số là FALSE 
=OR(3>7, 2>=0) 
Trả về kết quả TRUE 
NOT(logical) 
Cho trị logic ngược lại với trị logic của đối số 
=NOT(6<5) 
Trả về kết quả TRUE 
IF(logical_test, value_if_true, value_if_false) 
Hàm cho kết quả là value_if_true nếu logical_test 
có giá trị là TRUE, ngược lại hàm cho kết quả 
value_if_false nếu logical_test có giá trị là FALSE. 
Giả sử giá trị ô A1=5 và tại ô B1 ta điền hàm 
=IF(A1>=5, "Trung bình”, "Kém”) 
Trả về kết quả "Trung bình”. 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 92 10/19/2015 
Nhóm hàm xử lý chuỗi (Text) 
HÀM VÍ DỤ 
LEFT(text, num_chars) 
Giữ lại num_chars ký tự bên trái chuỗi text 
= LEFT(“Tin học ứng dụng”, 7) 
Trả về kết quả “Tin học” 
RIGHT(text, num_chars) 
Giữ lại num_chars ký tự bên phải chuỗi text 
=RIGHT(“Tin học ứng dụng”,8) 
Trả về kết quả “ứng dụng” 
MID(text, start_num,num_chars) 
Trả về num_chars ký tự kể từ ký tự thứ start_num của chuỗi 
=MID(“Tin học ứng dụng”,4,3) 
Trả về kết quả “học” 
LEN(text) 
Độ dài chuỗi 
= LEN(“Tin học ứng dụng”) 
Trả về kết quả 16 
PROPER(text) 
Trả về chuỗi có chữ in hoa ở đầu mỗi từ 
=PROPER(“Tin học ứng dụng”) 
Trả về kết quả “Tin Học Ứng Dụng” 
UPPER(text) 
Đổi thành chuỗi in hoa 
=UPPER(“Tin học”) 
Trả về kết quả “TIN HỌC” 
LOWER(text) 
Đổi thành chuỗi in thường 
LOWER(“HA NOI”) = “ha noi” 
VALUE(text) 
Đổi chuỗi các chữ số thành dãy các con số 
VALUE(“0834”) = 834 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 93 10/19/2015 
Nhóm hàm thống kê (Statistical) 
HÀM Ý NGHĨA 
MIN(number1,[number2],) Trả về giá trị nhỏ nhất trong các đối số 
MAX(number1,[number2],) Trả về giá trị lớn nhất trong các đối số 
AVERAGE(number1,[number2],) Trả về giá trị trung bình của các đối số 
COUNT(value1,[value2],) Đếm số lượng các ô kiểu số trong miền ô 
COUNTA(value1,[value2],) Đếm số lượng các ô khác rỗng có trong miền ô 
COUNTBLANK(range) Đếm số lượng các ô trống trong miền ô 
COUNTIF(range,criteria) 
Đếm số lượng các ô thoả điều kiện criteria đưa 
ra có trong miền ô range 
SUM(number1,[number2],) Tính tổng các giá trị số trong miền ô 
SUMIF(range,criteria,[sum_range]) 
Tính tổng các giá trị số trong sum_range thoả 
điều kiện criteria có trong miền ô chứa điều 
kiện range 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 94 10/19/2015 
Nhóm hàm dò tìm và tham chiếu (Lookup and reference) 
HÀM Ý NGHĨA 
INDEX(Array,Row_num,Column_num) Cho nội dung ô tính trong miền ô dò tìm Array, ô 
tính này có chỉ số dòng là Row_num và chỉ số cột 
là Column_num 
MATCH(lookup_value,lookup_array, 
match_type) 
Cho vị trí tương đối của lookup_value trong dãy 
dò tìm lookup_array (dãy chỉ là dòng hay cột) 
tương ứng với kiểu tìm match_type. 
LOOKUP(lookup_value,lookup_vector, 
result_vector) 
Tìm kiếm và trả về giá trị có trong dãy 
result_vector ở vị trí tương ứng với lookup_value 
trong dãy dò tìm lookup_vector. 
HLOOKUP(lookup_value,table_array,row_ind
ex_num,range_lookup) 
Tìm giá trị lookup_value ở dòng đầu tiên trong 
miền ô dữ liệu table_array có chứa lookup_value. 
Nếu tìm thấy sẽ trả về giá trị ô tương ứng ở dòng 
thứ row_index_num. Nếu không tìm thấy sẽ đưa 
ra thông báo #N/A (Not Available). Range_lookup 
là tham số tuỳ chọn. 
VLOOKUP(lookup_value,table_array,col_index
_num,range_lookup) 
Tìm giá trị lookup_value ở cột đầu tiên trong 
miền ô dữ liệu table_array có chứa lookup_value. 
Nếu tìm thấy sẽ trả về giá trị ô tương ứng ở cột 
thứ col_index_num. Nếu không tìm thấy sẽ đưa ra 
thông báo #N/A (Not Available). Range_lookup là 
tham số tuỳ chọn. 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 95 10/19/2015 
Nhóm hàm cơ sở dữ liệu (Database) 
TÊN_HÀM_CSDL (database,field,criteria) 
 Database: Vùng CSDL là toàn bộ hoặc một phần CSDL ban 
đầu. 
 Field: Tên trường (thông qua địa chỉ ô chứa tên trường) hoặc 
tương ứng là cột thuộc tính thứ n trong vùng CSDL đã chọn. 
 Criteria: Vùng tiêu chuẩn là miền ô chứa tên trường và biểu 
thức chứa điều kiện của các bản ghi chịu tác động của hàm. 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 98 10/19/2015 
Một số hàm CSDL 
Tên hàm Ý nghĩa 
DAVERAGE(database,field,criteria) Tính trung bình các giá trị thuộc tên trường/cột n 
trong vùng CSDL theo điều kiện được diễn tả ở vùng 
tiêu chuẩn. 
DMAX/DMIN(database,field,criteria) Lấy ra giá trị lớn nhất/nhỏ nhất thuộc tên trường/ 
cột n trong vùng CSDL theo điều kiện được diễn tả ở 
vùng tiêu chuẩn. 
DGET(database,field,criteria) Lấy ra giá trị thuộc tên trường/ cột n trong vùng 
CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn. 
DCOUNT(database,field,criteria) Đếm số lượng các giá trị kiểu số thuộc tên trường/ 
cột n trong vùng CSDL theo điều kiện được diễn tả ở 
vùng tiêu chuẩn. 
DCOUNTA(database,field,criteria) Đếm số lượng ô không trống thuộc tên trường/ cột n 
trong vùng CSDL theo điều kiện được diễn tả ở vùng 
tiêu chuẩn. 
DSUM(database,field,criteria) Tính tổng cộng các giá trị thuộc tên trường/ cột n 
trong vùng CSDL theo điều kiện được diễn tả ở vùng 
tiêu chuẩn. 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 99 10/19/2015 
Ví dụ 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 100 10/19/2015 
Phân nhóm dữ liệu (SUBTOTAL) 
Bước 1: tiến hành sắp xếp dữ liệu (tăng dần hoặc giảm 
dần) trên trường cần phân nhóm dữ liệu. 
Bước 2: lựa chọn vùng CSDL. 
Bước 3: Data – Subtotal. 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 101 10/19/2015 
Ví dụ 
Tính tổng thu nhập theo từng nhóm giới tính 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 102 10/19/2015 
Liên kết dữ liệu (tính năng GROUP and OUTLINE) 
Tình huống 1: Người dùng chủ động thực hiện liên 
kết các dòng/cột liền kề vào thành từng nhóm 
 Bước 1: (chỉ thực hiện khi muốn liên kết các dòng dữ liệu vào 
từng nhóm): sắp xếp dữ liệu trên trường sẽ phân nhóm dữ 
liệu. 
 Bước 2: chọn các dòng/ cột dữ liệu cần liên kết trong một 
nhóm. 
 Bước 3: chọn Data – Group – Group 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 103 10/19/2015 
Ví dụ 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 104 10/19/2015 
Liên kết dữ liệu (tính năng GROUP and OUTLINE) 
Tình huống 2: Excel sẽ tự động thực hiện liên kết 
cột khi phát hiện thấy dữ liệu trên các cột liền kề có 
liên quan lẫn nhau qua công thức/hàm 
 Bước 1: Đưa chuột đến ô bất kì trong vùng dữ liệu. 
 Bước 2: Nhấn chọn Data - Group - Auto OutLine => sẽ tự động 
xuất hiện các liên kết dòng/cột theo các cấp có thể được. 
Chú ý: nếu trong CSDL không có mối liên kết giữa các cột 
liền kề thì Excel sẽ từ chối thực hiện tính năng Auto 
OutLine. 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 105 10/19/2015 
Bảng tham chiếu chéo - Pivot Table 
Lựa chọn CSDL 
Insert - PivotTable/ PivotChart 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 106 10/19/2015 
Cấu trúc bảng PivotTable 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 107 10/19/2015 
Mức tổng hợp dữ 
liệu cao nhất 
Thống 
kê/tín
h toán 
trên 
dữ liệu 
Phân tích 
dữ liệu 
Report Filter 
Row 
Labels 
Column Labels 
∑ Values 
Mức tổng 
hợp dữ 
liệu cao 
thứ nhì 
Ví dụ 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 108 10/19/2015 
Với mỗi Tên đơn vị vay, 
cho biết Ngày vay và 
Thời hạn vay tương ứng 
cùng với Tổng số tiền 
phải trả của đơn vị vay đó 
Ví dụ 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 109 10/19/2015 
Biểu diễn dữ liệu bằng biểu đồ 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 110 10/19/2015 
Phân tích xu hướng dữ liệu 
Sparkline 
Trendline 
Chương 4 - PM bảng tính điện tử MS EXCEL2010 111 10/19/2015