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

el. 
10/19/2015 76 
 6 - Excel Option 
Tùy chỉnh công thức (Formulas) 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 77 
 Nội dung 
 1 
 1 Làm quen với môi trường làm việc Excel 2010 
 2 
 2 Các hàm số và phân tích dữ liệu với Excel 2010 
 1 
 6 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 78 
 Các khái niệm cơ bản 
Địa chỉ ô tính: thay thế cho nội dung ô tính. 
 Địa chỉ tương đối: cộtdòng. VD: A2, F25 
 Loại địa chỉ này sẽ bị thay đổi khi tham chiếu tới vùng đích theo 
 phương, chiều và khoảng cách tương ứng. 
 10/19/2015 Chương 5 - PM bảng tính điện tử MS EXCEL2007 79 
 Địa chỉ ô tính 
Địa chỉ tuyệt đối: $cột$dòng. VD: $L$1 
 Loại địa chỉ này giữ nguyên như vùng nguồn khi tham 
 chiếu đến vùng đích theo phương, chiều và khoảng cách 
 bất kỳ. 
 Địa chỉ hỗn hợp: $cộtdòng/cột$dòng. VD: $A2, B$1 
 Loại địa chỉ này sẽ giữ nguyên cột/dòng nếu trước đó có 
 dấu $, ngược lại sẽ thay đổi cột/dòng nếu trước đó không 
 có dấu $ khi ta tham chiếu đến vùng đích theo phương, 
 chiều khoảng cách tương ứng. 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 80 
 Các kiểu dữ liệu 
Trong mỗi ô tính chỉ có thể chứa 1 kiểu dữ liệu. Có 5 loại 
 kiểu dữ liệu dùng trong EXCEL: 
 . Kiểu số 
 . Kiểu ngày tháng 
 . Kiểu chuỗi 
 . Kiểu công thức: là dữ liệu được bắt đầu bằng dấu =, sau đó là 1 biểu thức 
 hoặc hàm. 
 . Kiểu mảng: là dãy ô tính thuộc cùng 1 dòng hoặc cùng 1 cột. Ví dụ: A4:A12. 
 Các công thức hay hàm dùng dữ liệu kiểu mảng thì phải nhấn: 
 Ctrl+Shift+Enter để khẳng định sau khi nhập xong. 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 81 
 4- 
 Các phép toán đối với từng kiểu dữ liệu 
 Kiểu số: ngầm định thì dữ liệu kiểu số luôn căn bám lề phải của ô 
 tính. 
 . Phép toán số học: + - * / ^ 
 . Phép toán logic: AND OR NOT 
 . Phép toán so sánh: = >= 
 Mức ưu tiên 3 phép toán trên theo thứ tự sau: 
 Dấu ngoặc đơn ( ) 1 (cao nhất) 
 Lũy thừa (^) 2 
 Nhân (*) và chia (/ ) 3 
 Cộng (+) và trừ (-) 4 
 Quan hệ 5 
 Logic 6 
 Nếu các phép toán trong một biểu thức có cùng mức ưu tiên thì sẽ thực 
 hiện từ trái sang phải. 
 10/19/2015 Chương 5 - PM bảng tính điện tử MS EXCEL2007 82 
 Các phép toán đối với từng kiểu dữ liệu 
Kiểu ngày tháng 
Là một bộ phận của dữ liệu kiểu số, trong dạng hiển thị có dấu phân cách ngày, 
tháng, năm (bằng dấu / hoặc – hoặc bằng chữ tiếng Anh). Ngầm định thì dữ liệu 
kiểu ngày tháng luôn căn bám lề phải của ô tính 
 . Phép toán cộng 
 . Phép toán trừ 
 . Phép toán quan hệ dựa trên nguyên tắc: ngày hôm sau lớn 
 hơn ngày hôm qua 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 83 
 Các phép toán đối với từng kiểu dữ liệu 
Kiểu chuỗi: là bắt đầu bằng chữ cái. Ở chế độ ngầm 
 định thì dữ liệu kiểu chuỗi luôn căn bám lề trái của ô 
 tính. 
 . Phép toán quan hệ, bao gồm: =, , ==, 
 . Phép toán ghép chuỗi: & 
 . Chú ý: nếu dùng phép toán & trên các dữ liệu kiểu số thì kết 
 quả cho ra là chuỗi chữ số. 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 84 
 Công thức trong excel 
Công thức trong Excel là một tập hợp các câu lệnh toán 
 học sử dụng để tính toán dữ liệu trong trang tính Excel. 
 Các công thức trong Excel thường bắt đầu với dấu “=”, 
 sau đó là các thành phần như toán tử, hàm, tham chiếu, 
 hằng số. Một công thức trong Excel có thể chứa một vài 
 hoặc tất cả các thành phần trên. Khi dữ liệu thay đổi thì 
 kết quả trong công thức cũng tự động thay đổi theo. 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 85 
 Các hàm cơ bản trong excel 
Hàm là công thức được xây dựng sẵn trong Excel dùng 
 để xử lý các thao tác tính toán. 
Dạng tổng quát của hàm: 
 Tên hàm (Danh sách các đối số) 
 . Tên hàm viết chữ hoa hoặc chữ thường. Tên hàm thường gắn 
 với ý nghĩa của các thao tác mà nó cung cấp. 
 . Danh sách các đối số là các giá trị đầu vào cung cấp dữ liệu để 
 hàm xử lý 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 86 
 Một số nhóm hàm thông dụng 
Financial: hàm tài chính 
Date & Time: hàm ngày tháng 
Math & Trig: hàm toán và lượng giác 
Statistical: hàm thống kê 
Lookup & Reference: hàm tìm kiếm và tham chiếu 
Database: hàm cơ sở dữ liệu 
Text: hàm xử lý văn bản 
Logic: hàm luận lý 
Information: hàm thông tin 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 87 
 Một số thông báo lỗi thường gặp 
Ký hiệu Ý nghĩa Những trường hợp gây lỗi 
 ##### Lỗi độ rộng - Khi cột thiếu độ rộng. 
 - Khi bạn nhập giá trị ngày tháng hoặc thời gian là số âm. 
#VALUE! Lỗi giá trị - Nhập vào 1 chuỗi trong khi hàm yêu cầu nhập 1 số/ 1 giá 
 trị logic. 
 - Đang nhập 1 số hoặc các đối số mà nhấn Enter. 
 - Nhập quá nhiều tham số cho một toán tử hoặc một hàm 
 trong khi chúng chỉ dùng một tham số. 
 - Nhập vào công thức số chia là 0. 
 #DIV/0! Lỗi chia cho 
 0 - Số chia trong công thức là một tham chiếu đến ô trống. 
 #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ỉ ô. 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 88 
 Một số thông báo lỗi thường gặp 
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 - Xóa những ô đang được tham chiếu bởi công thức. 
 chiếu - 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 - Dùng một đối số không phù hợp trong công thức. 
 kiểu số - 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 Dùng một dãy toán tử không phù hợp 
 rỗng 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 89 
 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 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 90 
 Nhóm hàm toán học (Math & trig) 
 HÀM VÍ DỤ 
 FACT(number) = FACT(4) 
 Tính giai thừa của number Trả về kết quả 24 
 INT(number) = INT(123.7) 
 Lấy phần nguyên của number Trả về kết quả 123 
 MOD(number,divisor) = MOD(23,5) 
 Phần dư của phép chia number cho divisor Trả về kết quả 3 
 SQRT(number) =SQRT(144) 
 Căn bậc hai của number Trả về kết quả 12 
 PRODUCT(number1, number2,...) =PRODUCT(9,8,7) 
 Tích các số number1, number2,... Trả về kết quả 504 
 SUM(number1, number2,...) =SUM(15.8,35.6) 
 Tổng các số number1, number2,... Trả về kết quả 51.4 
 ROUND(number, num_digits) = ROUND(35.67, 1) 
 Làm tròn số number với num_digits chữ số phần thập phân Trả về kết quả 35.7 
 LN(number) =LN(1) 
 Tính ln Trả về kết quả 1 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 91 
 Nhóm hàm luận lý (Logical) 
 HÀM VÍ DỤ 
 AND(logical1,logical2,..) =AND(3=0) Trả về kết quả TRUE 
 =AND(3>7,2>=0) Trả về kết quả FALSE 
 Cho trị TRUE nếu mọi đối số là TRUE, ngược lại 
 cho trị là FALSE 
 OR(logical1,logical2,..) =OR(3>7, 2>=0) 
 Cho trị TRUE nếu bất kỳ đối số nào là TRUE, cho Trả về kết quả TRUE 
 trị là FALSE nếu tất cả đối số là FALSE 
 NOT(logical) =NOT(6<5) 
 Cho trị logic ngược lại với trị logic của đối số Trả về kết quả TRUE 
 IF(logical_test, value_if_true, value_if_false) Giả sử giá trị ô A1=5 và tại ô B1 ta điền hàm 
 Hàm cho kết quả là value_if_true nếu logical_test =IF(A1>=5, "Trung bình”, "Kém”) 
 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. Trả về kết quả "Trung bình”. 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 92 
 Nhóm hàm xử lý chuỗi (Text) 
 HÀM VÍ DỤ 
 LEFT(text, num_chars) = LEFT(“Tin học ứng dụng”, 7) 
 Giữ lại num_chars ký tự bên trái chuỗi text Trả về kết quả “Tin học” 
 RIGHT(text, num_chars) =RIGHT(“Tin học ứng dụng”,8) 
 Giữ lại num_chars ký tự bên phải chuỗi text Trả về kết quả “ứng dụng” 
 MID(text, start_num,num_chars) =MID(“Tin học ứng dụng”,4,3) 
 Trả về num_chars ký tự kể từ ký tự thứ start_num của chuỗi Trả về kết quả “học” 
 LEN(text) = LEN(“Tin học ứng dụng”) 
 Độ dài chuỗi Trả về kết quả 16 
 PROPER(text) =PROPER(“Tin học ứng dụng”) 
 Trả về chuỗi có chữ in hoa ở đầu mỗi từ Trả về kết quả “Tin Học Ứng Dụng” 
 UPPER(text) =UPPER(“Tin học”) 
 Đổi thành chuỗi in hoa Trả về kết quả “TIN HỌC” 
 LOWER(text) LOWER(“HA NOI”) = “ha noi” 
 Đổi thành chuỗi in thường 
 VALUE(text) VALUE(“0834”) = 834 
 Đổi chuỗi các chữ số thành dãy các con số 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 93 
 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 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 94 
 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, Cho vị trí tương đối của lookup_value trong dãy 
 match_type) 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, Tìm kiếm và trả về giá trị có trong dãy 
 result_vector) 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 Tìm giá trị lookup_value ở dòng đầu tiên trong 
 ex_num,range_lookup) 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 Tìm giá trị lookup_value ở cột đầu tiên trong 
 _num,range_lookup) 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. 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 95 
 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. 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 98 
 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. 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 99 
 Ví dụ 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 100 
 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. 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 101 
 Ví dụ 
Tính tổng thu nhập theo từng nhóm giới tính 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 102 
 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 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 103 
 Ví dụ 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 104 
 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. 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 105 
 Bảng tham chiếu chéo - Pivot Table 
Lựa chọn CSDL 
Insert - PivotTable/ PivotChart 
 10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 106 
 Cấu trúc bảng PivotTable 
 Mức tổng hợp dữ 
 liệu cao nhất Phân tích 
 Report Filter 
 dữ liệu 
 Column Labels 
 Mức tổng Thống 
 Row kê/tín
 hợp dữ ∑ 
 Labels Values 
 liệu cao h toán 
 thứ nhì trên 
 dữ liệu 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 107 
 Ví dụ 
 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 đó 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 108 
 Ví dụ 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 109 
 Biểu diễn dữ liệu bằng biểu đồ 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 110 
 Phân tích xu hướng dữ liệu 
Sparkline 
Trendline 
10/19/2015 Chương 4 - PM bảng tính điện tử MS EXCEL2010 111