Bài giảng Tin học - Chương 3: Microsoft Excel 2007

Chương 3. Microsoft Excel 2007 KHOA CÔNG NGHỆ THÔNG TIN KHOA CÔNG NGHỆ THÔNG TIN Slide 97 Mục đích  Trang bị cho sinh viên kiến thức cơ bản về bảng tính Microsoft Excel  Đối tượng học: Sinh viên năm 1  Số tiết lý thuyết: 12 tiết  Số tiết thực hành: 4 Tiết KHOA CÔNG NGHỆ THÔNG TIN Slide 98 Nội dung 3.1 Các thao tác với tệp 3.2 Lập và định dạng bảng tính 3.3 Lập công thức và các tính toán với hàm 3.4 Sắp xếp và lọc dữ liệu 3.5 In bảng tính KHOA CÔNG NGHỆ THÔNG TIN Giới thiệu  Excel 2007 là một phần mềm ứng dụng thuộc bộ ứng dụng Microsoft Ofice 2007.  Excel 2007 cho phép xử lý các bảng tính từ đơn giản đến phức tạp.  Excel 2007 hỗ trợ việc nhập khẩu dữ liệu từ nhiều nguồn khác nhau. Nó là thành công cụ báo cáo và phân tích số liệu hiệu quả. Slide 99 KHOA CÔNG NGHỆ THÔNG TIN Giới thiệu  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 2007.  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. Slide 100 KHOA CÔNG NGHỆ THÔNG TIN Giới thiệu  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. Slide 101 Vùng làm việc KHOA CÔNG NGHỆ THÔNG TIN Giới thiệu  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. Slide 102 KHOA CÔNG NGHỆ THÔNG TIN  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 Slide 103 Giới thiệu KHOA CÔNG NGHỆ THÔNG TIN 3.1 Các thao tác với tệp  Tạo mới tệp  Chọn Office Button  New (hoặc Ctrl + N)  Ghi tệp  Chọn Office Button  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 Slide 104 KHOA CÔNG NGHỆ THÔNG TIN  Ghi tệp với tên khác  Chọn Office Button  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 Office Button  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 Slide 105 3.1 Các thao tác với tệp KHOA CÔNG NGHỆ THÔNG TIN  Đóng tệp  Chọn Office Button  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) Slide 106 3.1 Các thao tác với tệp KHOA CÔNG NGHỆ THÔNG TIN 3.2 Lập và định dạng bảng tính  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: . Ví dụ: $A$2, $C$4.  Địa chỉ hỗn hợp: , . Ví dụ: $A2, C$4. Slide 107 KHOA CÔNG NGHỆ THÔNG TIN 3.2.1 Nhập dữ liệu vào bảng tính  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 -, ., $  Date (ngày tháng): định dạng mm/dd/yy.  Fomular (công thức): bắt đầu bằng dấu “=“ và địa chỉ  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 Slide 108 KHOA CÔNG NGHỆ THÔNG TIN  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. Slide 109 3.2.1 Nhập dữ liệu vào bảng tính KHOA CÔNG NGHỆ THÔNG TIN 3.2.2 Các thao tác với vùng dữ liệu  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  ấ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 Slide 110 KHOA CÔNG NGHỆ THÔNG TIN  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) Slide 111 3.2.2 Các thao tác với vùng dữ liệu KHOA CÔNG NGHỆ THÔNG TIN  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.  Đánh số thứ tự tự động Slide 112 3.2.2 Các thao tác với vùng dữ liệu KHOA CÔNG NGHỆ THÔNG TIN  Đị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. Slide 113 3.2.2 Các thao tác với vùng dữ liệu KHOA CÔNG NGHỆ THÔNG TIN 3.2.3 Định dạng dữ liệu trên bảng tính  Chọn vùng dữ liệu  Chọn tab Home  Format  Format Cells...  Tab Number Slide 114 KHOA CÔNG NGHỆ THÔNG TIN  Chọn tab Home  Format  Format Cells...  Tab Alignment Slide 115 3.2.3 Định dạng dữ liệu trên bảng tính KHOA CÔNG NGHỆ THÔNG TIN  Chọn tab Home  Format  Format Cells...  Tab Font Slide 116 3.2.3 Định dạng dữ liệu trên bảng tính KHOA CÔNG NGHỆ THÔNG TIN 3.2.4 Định dạng bảng tính  Chọn vùng dữ liệu  Chọn tab Home  Format  Format Cells...  Tab Border Slide 117 KHOA CÔNG NGHỆ THÔNG TIN  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 Slide 118 3.2.4 Định dạng bảng tính KHOA CÔNG NGHỆ THÔNG TIN 3.3 Công thức và hàm  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) Slide 119 KHOA CÔNG NGHỆ THÔNG TIN  Hàm (function)  Hàm được dùng trong công thức  Các hàm được chia thành các nhóm  Các hàm toán học  Các hàm thống kê  Các hàm xử lý chuỗi  Các hàm logic  Các hàm date, time  Các hàm tìm kiếm dữ liệu Slide 120 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm toán học  ABS(number): giá trị tuyệt đối.  Ví dụ: ABS(-7) = 7  INT(number): lấy phần nguyên  Ví dụ: Int(9.234) = 9; Int(-8.9) = -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)= -2  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 Slide 121 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm toán học  SQRT(number): căn bậc hai.  Pi(): lấy số pi = 3.14  RANK (x, vùng ô): cho thứ hạng của x trong vùng ô. Slide 122 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm toán học (tiếp)  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  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 điều kiện.  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 ô sẽ tính tổng nếu thỏa mãn điều kiện đưa ra trong đối số criteria. Slide 123 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm thống kê  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. Slide 124 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm thống kê (tiếp)  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) Slide 125 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm xử lý chuỗi  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”,6,2) = “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 Slide 126 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm logic  Hàm IF:  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. Slide 127 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm logic (tiếp)  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 Slide 128 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Một số hàm ngày tháng  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.  YEAR(“mm/dd/yy”): cho giá trị năm. Slide 129 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN  Hàm tìm kiếm dữ liệu  Hàm VLOOKUP dùng để tìm kiếm dữ liệu  VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])  VLOOKUP(X,BẢNG, CỘT THAM CHIẾU, CÁCH DÒ)  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: gõ giá trị là false.  Hàm HLOOKUP: Tương tự trên Slide 130 3.3 Công thức và hàm KHOA CÔNG NGHỆ THÔNG TIN 3.4 Sắp xếp và lọc dữ liệu  Sắp xếp (sort)  Dùng để sắp xếp dữ liệu tăng dần hoặc giảm dần  Sắp xếp 1 cột dữ liệu  Chọn cột cần sắp  Chọn tab Home  Sort & Filter  Sắp xếp tăng dần: Chọn “Sort A to Z” hoặc “Sort Smallest to Largest”  Sắp xếp giảm dần: Chọn “Sort Z to A” hoặc “Sort Largest to Smallest” Slide 131 KHOA CÔNG NGHỆ THÔNG TIN  Sắp xếp (sort)  Sắp xếp dữ liệu trên nhiều cột  Đánh dấu vùng sắp xếp  Chọn tab Data  Sort & Filter  Chọn cột sắp xếp đầu tiên  Kích Add Level, chọn cột sắp xếp tiếp theo Slide 132 3.4 Sắp xếp và lọc dữ liệu KHOA CÔNG NGHỆ THÔNG TIN  Lọc dữ liệu (Filter)  Dùng để lọc dữ liệu theo một tiêu chuẩn nào đó  Chọn các cột dữ liệu cần lọc  Chọn tab Data  Filter  Chọn mũi tên ở các cột dữ liệu để lọc  Thứ tự ưu tiên lọc theo các cột từ trái  phải  Hủy bỏ lọc đã thiết lập  Chọn tab Data  Sort & Filter  Clear Slide 133 3.4 Sắp xếp và lọc dữ liệu KHOA CÔNG NGHỆ THÔNG TIN  Lọc dữ liệu (Advanced)  Khái niệm:  Vùng Database  Vùng Criteria  Vùng Extract  Thao tác tìm kiếm, rút trích, xóa  Các dạng vùng tiêu chuẩn  Hủy bỏ lọc đã thiết lập  Chọn tab Data  Sort & Filter  Clear Slide 134 3.4 Sắp xếp và lọc dữ liệu KHOA CÔNG NGHỆ THÔNG TIN 3.5 In bảng tính  Định dạng trang in  Chọn tab Page Layout, định dạng như trong Word  Xem bảng tính trước khi in  Chọn Office button  Print  Prin Preview  In bảng tính  Chọn Office button  Print  Print Slide 135