Bài giảng Tin học ứng dụng - Chương 1: Các hàm thông dụng trong Excel

 Trường Đại học Vinh 
 Khoa Công nghệ Thông tin 
 ----------o0o------------ 
 Chương 1 
CÁC HÀM THÔNG DỤNG 
 TRONG EXCEL 
 2 
1.1 Giới thiệu 
1.2 Hàm toán học và lượng giác 
1.3 Hàm Logic 
1.4 Hàm thống kê 
1.5 Hàm xử lý dữ liệu kiểu chuỗi 
1.6 Hàm tìm kiếm và tham chiếu 
1.7 Hàm xử lý dữ liệu kiểu ngày 
 06/9/2015 3 
 Khởi động Microsoft Excel 
 ◦ Nháy đúp vào biểu tượng trên màn hình 
 ◦ Hoặc chọn Start All Programs Microsoft Office 
 Microsoft Excel 2010. 
 Kết thúc Microsoft Excel 
 ◦ Chọn biểu tượng Office buton ở góc trên bên trái màn hình 
 Close. 
 ◦ Chú ý: Nếu tệp chưa được ghi, máy yêu cầu ghi lại trước khi kết 
 thúc. 
 06/9/2015 4 
 Cửa sổ làm việc của Excel 
 ◦ Ribbon: Gồm các tab Home, Insert, Page Layout, Formulas, 
 ... và các công cụ khác. 
 ◦ Thanh công thức: Dùng để nhập/sửa dữ liệu. 
 Vùng làm việc 
 06/9/2015 5 
 Cửa sổ làm việc của Excel 
 ◦ Worksheet (sheet): là “bảng tính” gồm các ô (cells) dùng để 
 làm việc với dữ liệu. Một Worksheet chứa được 1,048,576 
 dòng và 16,384 cột. 
 ◦ Sheet tabs: Tên của các sheet hiển thị ở góc trái dưới của cửa 
 sổ workbook. 
 ◦ Workbook: Là một tệp để làm việc như: tính toán, vẽ đồ thị, 
 và lưu trữ dữ liệu. Một workbook có thể chứa nhiều sheet. 
 06/9/2015 6 
 Tạo mới tệp 
 ◦ Chọn File New (hoặc Ctrl + N) 
 Ghi tệp 
 ◦ Chọn File Save (hoặc Ctrl + S) 
 ◦ Nếu tệp chưa được đặt tên một cửa sổ xuất hiện: 
  File name: gõ tên tệp 
  Save as type: kiểu ghi 
  Chọn Save 
 06/9/2015 7 
 Ghi tệp với tên khác 
 ◦ Chọn File Save As 
 ◦ Chọn kiểu tài liệu một cửa sổ xuất hiện: 
  File name: gõ tên tệp 
  Save as type: Chọn kiểu ghi tệp (Excel workbook) 
  Chọn Save 
 Mở tệp đã có 
 ◦ Chọn File Open (hoặc Ctrl + O) 
 ◦ Xuất hiện một cửa sổ chọn đường dẫn chọn tệp cần mở 
 Open 
 06/9/2015 
 Đóng tệp 
 ◦ Chọn File Close 
 ◦ Nếu tệp chưa ghi xuất hiện cửa sổ: 
 ◦ Chọn Yes: ghi tệp 
 ◦ Chọn No: không ghi tệp 
 ◦ Chọn Cancel: hủy thao tác đóng tệp (không đóng tệp) 
 06/9/2015 
 Các khái niệm cơ bản trong bảng tính 
 ◦ Cell: là một ô trong bảng tính (sheet), mỗi cell được xác định 
 bởi 1 địa chỉ . Ví dụ: A2, C4. 
 ◦ Vùng: là tập hợp các ô. Một vùng được xác định bằng địa chỉ 
 của ô ở góc trên bên trái và ô góc dưới bên phải của vùng. Ví 
 dụ A2:A12, B3:D8. 
 ◦ Các dạng địa chỉ: 
  Địa chỉ tương đối: . Ví dụ: A2, C4. 
  Địa chỉ tuyệt đối: thêm dấu $ trước tên dòng/hoặc cột nếu 
 muốn cố định phần đó. Ví dụ: $A2, C$4, $A$2, $C$4. 
 06/9/2015 10 
 Các kiểu dữ liệu trong ô 
 ◦ Text (văn bản): gồm các chữ cái A Z, a z 
 ◦ Number (kiểu số): gồm các số 0 9, các dấu -, ., $ 
 ◦ Fomular (công thức): bắt đầu bằng dấu “=“ và địa chỉ 
 ◦ Date (ngày tháng): định dạng mm/dd/yy. 
 Nhập dữ liệu 
 ◦ Đưa con trỏ đến ô cần nhập. Nhập xong gõ Enter. 
 ◦ Văn bản/chữ số: gõ dữ liệu. 
 ◦ Công thức: bắt đầu bằng dấu “=“ và công thức 
 ◦ Ngày tháng: nhập theo dạng mm/dd/yy 
 06/9/2015 11 
 Sửa dữ liệu 
 ◦ Khi nhập sai, có thể sửa lại. 
 ◦ Đưa con trỏ đến ô cần sửa, nháy đúp chuột hoặc F2. 
 Xóa dữ liệu 
 ◦ Đưa con trỏ đến ô cần xóa và ấn phím delete. 
 06/9/2015 12 
 Chọn (đánh dấu) một vùng 
 ◦ Bấm chuột trái và kéo từ ô góc trên bên trái đến góc dưới 
 bên phải hoặc ngược lại. 
 ◦ Có thể dùng phím shift + các phím mũi tên 
 ◦ Ctrl+A: chọn tất cả bảng tính (sheet) 
 Chọn (đánh dấu) nhiều vùng 
 ◦ Chọn 1 vùng nhấn phím Ctrl chọn các vùng tiếp theo. 
 Chọn (đánh dấu) 1 hàng/cột 
 ◦ Click con trỏ chuột ở tên hàng/cột 
 06/9/2015 13 
 Xóa vùng dữ liệu 
 ◦ Chọn vùng cần xóa 
 ◦ Ấn phím delete 
 Sao chép vùng dữ liệu 
 ◦ Chọn vùng dữ liệu 
 ◦ Chọn biểu tượng copy (hoặc Ctrl + C) 
 ◦ Đưa con trỏ đến ô cần đặt dữ liệu 
 ◦ Chọn biểu tượng paste (hoặc Ctrl + V) 
 06/9/2015 14 
 Di chuyển vùng dữ liệu 
 ◦ Chọn vùng dữ liệu 
 ◦ Chọn biểu tượng Cut (hoặc ấn Ctrl + X) 
 ◦ Đưa con trỏ đến vị trí cần đặt dữ liệu 
 ◦ Chọn biểu tượng Paste (hoặc ấn Ctrl+V) 
 Chú ý 
 ◦ Khi sao chép hoặc di chuyển vùng dữ liệu, nếu dữ liệu là 
 công thức có địa chỉ tương đối, nó sẽ thay đổi theo địa chỉ 
 mới. 
 06/9/2015 15 
 Định dạng vùng dữ liệu 
 ◦ Chọn vùng dữ liệu, các thao tác định dạng vùng giống như 
 MS-Word. 
 06/9/2015 16 
 Chọn vùng dữ liệu 
 Chọn tab Home Format Format Cells... 
 ◦ Tab Number 
 06/9/2015 17 
 Chọn tab Home Format Format Cells... 
 ◦ Tab Alignment 
 06/9/2015 18 
 Chọn tab Home Format Format Cells... 
 ◦ Tab Font 
 06/9/2015 19 
 Chọn vùng dữ liệu 
 Chọn tab Home Format Format Cells... 
 ◦ Tab Border 
 06/9/2015 20 
 Có thể định dạng bảng theo các mẫu có sẳn 
 ◦ Chọn vùng dữ liệu 
 ◦ Chọn tab Home Format as Table chọn mẫu 
 06/9/2015 21 
 Chèn thêm bảng tính (worksheet) 
 ◦ Chọn trên thanh sheet tab 
 Đổi tên bảng tính (worksheet) 
 ◦ Nháy đúp chuột vào tên bảng tính và đổi tên 
 ◦ Hoặc nháy chuột phải vào tên bảng tính rename 
 Xóa bảng tính (worksheet) 
 ◦ Nháy chuột phải vào tên bảng tính delete 
 Ẩn/hiện bảng tính (worksheet) 
 ◦ Nháy chuột phải vào tên bảng tính hide /unhide 
 06/9/2015 22 
 Công thức 
 ◦ Bắt đầu bởi dấu = 
 ◦ Tiếp theo là các hằng số, địa chỉ ô, hàm số được nối với 
 nhau bởi các phép toán. 
 Các phép toán 
 ◦ Cộng +, trừ -, nhân *, chia /, luỹ thừa ^ 
  Thứ tự ưu tiên giống như các biểu thức toán học 
 ◦ Ví dụ: 
  = 10 + A3 
  = B3*B4 + B5/5 
  = 2*C2 + C3^4 – abs(C4) - sin(A2) 
 06/9/2015 23 
 ABS(number): giá trị tuyệt đối. 
 ◦ Ví dụ: ABS(-7) = 7 
 INT(number): lấy phần nguyên, loại phần thập phân. 
 ◦ Ví dụ: Int(9.234) = 9; 
 MOD(a,b): lấy phần dư của kết quả a chia cho b; lấy dấu của 
 b. 
 ◦ Ví dụ: Mod(17,3) = 2; Mod(17,-3)= -1 
 ROUND(number,num_digits): làm tròn số number với số 
 thập phân num_digits. 
 ◦ Ví dụ: =ROUND(-1.475, 2) = -1.48 
 06/9/2015 24 
 Hàm SUM: Tính tổng tất cả các số trong dãy số 
 ◦ Cú pháp: SUM(number1,number2,....) 
 ◦ Ví dụ: SUM(A2:A10) tính tổng các số từ ô A2 đến ô A10 
 06/9/2015 25 
 SIN (Hàm SIN) 
 Mô tả 
 Trả về sin của một góc đã cho. 
 Cú pháp 
 SIN(number) 
 Cú pháp hàm SIN có các đối số sau đây: 
 ◦ Number Bắt buộc. Góc tính bằng radian mà bạn muốn tìm 
 sin cho góc đó. 
 ◦ Chú thích: Nếu đối số của bạn tính bằng độ, hãy nhân nó 
 với PI()/180 hoặc dùng hàm RADIANS để chuyển đổi nó 
 thành radian. 
 06/9/2015 26 
06/9/2015 27 
 Mô tả 
Trả về arcsin, hay sin nghịch đảo của một số. arcsin là góc mà 
sin của nó là số. Góc được trả về được tính bằng radian trong 
phạm vi từ -pi/2 đến pi/2. 
 Cú pháp 
 ASIN(number) 
 Cú pháp của hàm ASIN có các đối số sau đây: 
Number Bắt buộc. Sin của góc mà bạn muốn và phải từ -1 đến 
1. 
 Chú thích 
Để biểu thị arcsin bằng độ, hãy nhân kết quả với 180/PI( ) hoặc 
dùng hàm DEGREES. 
 06/9/2015 28 
06/9/2015 29 
 Mô tả 
Trả về cosin của góc đã cho. 
 Cú pháp 
 COS(number) 
Cú pháp hàm COS có các đối số sau đây: 
 Number Bắt buộc. Góc tính bằng radian mà bạn muốn tính 
cosin cho nó. 
 Chú thích 
Nếu góc tính bằng độ, hãy nhân góc đó với PI()/180 hoặc dùng 
hàm RADIANS để chuyển đổi góc thành radian. 
 06/9/2015 30 
06/9/2015 31 
 Mô tả 
Trả về arccosin, hay cosin nghịch đảo, của một số. Arccosin là 
góc mà cosin của nó là số. Góc được trả về được tính bằng 
radian trong phạm vi từ 0 (không) đến pi. 
 Cú pháp 
 ACOS(number) 
 Cú pháp của hàm ACOS có các đối số sau đây: 
Number Bắt buộc. Cosin của góc mà bạn muốn và phải từ -1 
đến 1. 
 Chú thích 
Nếu bạn muốn chuyển đổi kết quả từ radian thành độ, hãy nhân 
nó với 180/PI() hoặc dùng hàm DEGREES. 
 06/9/2015 32 
06/9/2015 33 
 Hàm IF: Trả về giá trị đúng (TRUE) hoặc sai (FALSE). 
 ◦ Cú pháp: IF(logical_test,value_if_true,value_if_false) 
 ◦ Logical_test là điều kiện. 
 ◦ Value_if_true là giá trị được trả về nếu logical_test là TRUE. 
 ◦ Value_if_false là giá trị được trả về nếu logical_test là 
 FALSE. 
 ◦ VD: =IF(A3>=5,“Đỗ”,“Trượt”) 
 ◦ Có thể dùng các hàm IF lồng nhau để xác định nhiều điều 
 kiện. 
 06/9/2015 34 
 Hàm OR: Trả về TRUE nếu một trong các điều kiện là TRUE, 
 trả về FALSE nếu tất cả các điều kiện là FALSE 
 ◦ Cú pháp OR(logical_1,logical_2,...) 
 ◦ Logical_1, logical_2, là các điều kiện cần kiểm tra. 
 Hàm AND: Trả về kết quả TRUE nếu tất cả điều kiện đều 
 TRUE, trả về FALSE nếu một trong các điều kiện là FALSE. 
 ◦ Cú pháp: AND (logical_1,logical_2,...): 
 Hàm NOT(logical): Đảo ngược giá trị của logical 
 06/9/2015 35 
 Hàm COUNT: Đếm các ô có giá trị số. 
 ◦ Cú pháp COUNT(value1,value2,...): 
 ◦ Ví dụ: =COUNT(E3:E9) đếm các ô có giá trị số từ E3 E9 
 Hàm COUNTIF: Đếm số ô trong vùng theo điều kiện. 
 ◦ Cú pháp COUNTIF(range,criteria) 
 ◦ Range địa chỉ vùng. 
 ◦ Criteria là các tiêu chuẩn đếm. 
 ◦ Ví dụ: =COUNTIF(A2:A5,"apples") 
 ◦ Chú ý: Có thể sử dụng các ký tự đại diện như dấu ?, dấu * 
 trong tiêu chuẩn criteria. 
 06/9/2015 36 
◦ Hàm SUMIF: Tính tổng theo điều kiện 
  Cú pháp: SUMIF(range,criteria,sum_range) 
  Range là vùng dữ liệu cần kiểm tra. 
  Criteria là điều kiện tính. Nó có thể là kiểu số, biểu thức hoặc 
 chuỗi văn bản xác định các ô nào sẽ được tính tổng. Ví dụ: 
 criteria có thể là 32, "32", ">32", or "apples". 
  Sum_range là vùng để tính tổng nếu thỏa mãn điều kiện đưa 
 ra trong đối số criteria. 
 06/9/2015 37 
 Hàm AVERAGE: Tính giá trị trung bình. 
 ◦ Cú pháp: AVERAGE(number1,number2,...) 
 ◦ Ví dụ: = AVERAGE (A2:A10) 
 Hàm MAX: Tìm giá trị lớn nhất 
 ◦ Cú pháp: MAX(number1,number2,...) 
 ◦ Ví dụ: = MAX(A2:A10) 
 Hàm MIN: Tìm giá trị bé nhất 
 ◦ Cú pháp: MIN(number1,number2,...) 
 ◦ Ví dụ: = MIN(A2:A10) 
 Hàm RANK(): Thứ hạng (từ lớn đến nhỏ) của một giá trị 
 ◦ Cú pháp: RANK(number,ref,order) 
 06/9/2015 38 
 Tính độ lệch tuyệt đối 
 AVEDEV(number1, number2, ...) 
 Tính tổ hợp chập k của n 
 COMBIN(n,k) 
 06/9/2015 39 
 LEFT(“Chuỗi”, n): Cho n ký tự bên trái của chuỗi. 
 ◦ VD: =LEFT(“Khoa CNTT”,4) = “Khoa” 
 RIGHT(“Chuỗi”, n): Cho n ký tự bên phải của chuỗi. 
 ◦ VD: =RIGHT(“Khoa CNTT”,3) = “NTT” 
 MID(“Chuỗi”, m, n): Cho n ký tự tính từ ký tự thứ m. 
 ◦ VD: =MID(“Khoa CNTT”,2,6) = “Hoa CN” 
 UPPER(“Chuỗi”) : Chuyển thành chữ hoa 
 LOWER(“Chuỗi”): Chuyển thành chữ thường 
 LEN(“Chuỗi”): Lấy độ độ dài chuỗi 
 TRIM(“Chuỗi”): Cắt bỏ các ký tự trắng ở đầu và cuối 
 PROPER(“Chuỗi”): Chuyển các ký tự đầu từ thành ký tự hoa 
 06/9/2015 40 
 Hàm VLOOKUP dùng để tìm kiếm dữ liệu 
 VLOOKUP(lookup_value,table_array,col_index_num, 
 [range_lookup]) 
 Trong đó 
 ◦ lookup_value: Các giá trị để tìm kiếm trong cột đầu tiên 
 của bảng hoặc phạm vi. 
 ◦ table_array: Phạm vi ô có chứa dữ liệu. 
 ◦ col_index_num: Số thứ tự cột trong đối số table_array cần 
 lấy giá trị. 
 ◦ range_lookup: kiểu tham chiếu. 
 06/9/2015 41 
 Hàm DATE(year,month,day): trả về một dãy số liên tiếp biểu diễn một 
 ngày. 
 Hàm TIME(hour,minute,second): trả về số thập phân thể hiện đầy đủ về 
 thời gian. 
 Hàm NOW(): trả về ngày và giờ ở thời điểm hiện tại. 
 Hàm TODAY(): trả về ngày hiện tại. 
 Hàm DAY(“mm/dd/yy”): cho giá trị ngày. 
 Hàm MONTH(“mm/dd/yy”): cho giá trị tháng. 
 Hàm YEAR(“mm/dd/yy”): cho giá trị năm. 
 06/9/2015 42