Bài giảng Tin học đại cương Chương 6 Phần mềm bảng tính

Click to edit Master title style Click to edit Master text styles Second level Third level Fourth level Fifth level 12/16/2013 Chương 5 - Phần mềm trình chiếu ‹#› Chương 5 - Phần mềm trình chiếu 1 CHƯƠNG 6PHẦN MỀM BẢNG TÍNH Bài giảng Tin học đại cương Bộ môn Tin học cơ sở - Khoa CNTT Trường ĐH Ngân hàng TP.HCM Nội dung Khái niệm phần mềm bảng tính (PMBT) Phần mềm bảng tính MS Excel Nhập và hiệu chỉnh nội dung bảng tính Định dạng và thay đổi cấu trúc BT Sử dụng hàm trong Excel Quản trị cơ sở dữ liệu Biểu đồ Một số chức năng khác Chương 5 - Phần mềm trình chiếu 2 1. Khái niệm PMBT Ứng dụng tương tác tổ chức và phân tích dữ liệu dưới dạng bảng. Bảng tính đầu tiên cho máy vi tính:VisiCalc (1979). Các phần mềm bảng tính thông dụng Lotus 1-2-3 (Lotus software – IBM). MS Excel (Microsoft). Calc (OpenOffice). Google Docs (Google). Chương 5 - Phần mềm trình chiếu 3 Các tính năng cơ bản Hỗ trợ lập công thức. Tự động tính lại. Thư viện hàm. Quản trị cơ sở dữ liệu. Biểu đồ. Macro. Chương 5 - Phần mềm trình chiếu 4 2. PMBT MS Excel Ứng dụng bảng tính phát triển cho MS Windows và Mac OS X. Được tích hợp trong bộ công cụ MS Office. Phiên bản đầu cho Windows: MS Excel 2.0 (1967). Phiên bản cuối: MS Excel 2013. Phiên bản phổ biến hiện nay: MS Excel 2010. Chương 5 - Phần mềm trình chiếu 5 Giao diện MS Excel 2010 Phong cách tương tự như Word và PowerPoint. Vùng làm việc: Bảng => Hàng, cột, Ô. Thanh công thức: Địa chỉ & nội dung/ công thức trong ô hiện hành. Thanh Tab: Lựa chọn trang bảng tính. Chương 5 - Phần mềm trình chiếu 6 Chương 5 - Phần mềm trình chiếu 7 Cấu trúc sổ bảng tính Tập tin bảng tính => sổ bảng tính. Sổ bảng tính => trang bảng tính. Số trang ban đầu: được ấn định sẵn (chế độ mặc định). Số trang tối đa: 255 (Excel 97 - 2003), chỉ bị giới hạn bởi bộ nhớ (Excel 2007 – 2010).. Chuyển trang hiện hành: sử dụng thanh Tab. Chương 5 - Phần mềm trình chiếu 8 Trang, hàng, cột Tên mặc định: Sheet1, Sheet2, … (có thể thay đổi). Được chia thành nhiều hàng, cột (65,536 hàng, 256 cột với Excel 97 – 2003; 1,048,576 hàng, 16,384 cột với Excel 2010). Đánh số hàng, cột: Hàng: 1 … 65536 ... Cột: A … IV ... Chương 5 - Phần mềm trình chiếu 9 Ô, vùng Ô: giao điểm hàng, cột (đơn vị cơ sở). Vùng: khối ô (nhiều hàng, cột) trong bảng. Địa chỉ ô: (trang hiện hành). [] (trang khác). Địa chỉ vùng: : Chương 5 - Phần mềm trình chiếu 10 Nội dung bảng tính Nội dung các ô: Chuỗi văn bản. Con số Ngày tháng, thời gian. Giá trị luận ký. Công thức. Các đối tượng chèn thêm (biểu tượng, hình ảnh, sơ đồ, biểu đồ,…). Chương 5 - Phần mềm trình chiếu 11 Thiết lập chế độ mặc định Chương 5 - Phần mềm trình chiếu 12 Công cụ: Hộp thoại Excel Option. Một số thao tác cơ bản: Đặt font chữ và số trang mặc định. Đặt chế độ lưu trữ tự động. Tùy chỉnh thanh công cụ Quick Access. Cài đặt các công cụ Add-Ins. Chương 5 - Phần mềm trình chiếu 13 Hộp thoại Excel Option Làm việc với tập tin Tập tin bảng tính: kiểu mặc định .xls (Excel 97 – 2003), .xlsx (Excel 2007 – 2010). Các thao tác cơ bản: tương tự như Word và PowerPoint. Chương 5 - Phần mềm trình chiếu 14 3. Nhập và hiệu chỉnh nội dung bảng tính Các nguyên tắc cơ bản. Xây dựng công thức. Sử dụng địa chỉ tương đối và địa chỉ tuyệt đối. Sử dụng công cụ tự động lấp đầy. Kiểm soát dữ liệu nhập với tính năng Data Validation. Chương 5 - Phần mềm trình chiếu 15 Các nguyên tắc cơ bản Mỗi ô bảng tính chứa một giá trị (DL nhập/ KQ tính toán) thuộc một kiểu xác định (chuỗi ký tự, số, ngày tháng, thời gian, giá trị luận lý). Excel tự xác định kiểu cho các dữ liệu nhập. Trường hợp nhập nhằng: cần nhập dữ liệu theo các qui tắc nhập dữ liệu tường minh. Chương 5 - Phần mềm trình chiếu 16 Các qui tắc nhập liệu cơ bản Chuỗi ký tự: Gõ nhập bình thường hoặc bắt đầu bởi dấu nháy đơn (“’”). Số: gõ nhập bình thường (hoặc bắt đầu bởi các dấu “+”, “-”, “=“). Ngày tháng, thời gian: Nhập theo dạng được hệ thống qui định. Giá trị luận lý: TRUE/FALSE. Công thức: Bắt đầu bởi các dấu “+”, “-”, “=“. Chương 5 - Phần mềm trình chiếu 17 CHÚ Ý ! Excel tự động loại bỏ các chữ số 0 vô nghĩa trước các số. Các giá trị số có độ dài lớn hơn chiều rộng ô sẽ được hiển thị dưới dạng chuỗi dấu “#”. Mọi chuỗi nhập bắt đầu bởi dấu “+”, “-”, “=” đều được hiểu là số hoặc công thức. Chương 5 - Phần mềm trình chiếu 18 Chương 5 - Phần mềm trình chiếu 19 Gõ dấu “’” trước chuỗi ký tự có dạng tựa con số hoặc công thức ! Ví dụ: Bảng danh sách nhân viên Nhập thông tin nhân viên: Mã NV: 000104 Họ tên: Nguyễn Văn Dũng Ngày sinh: 16/06/2982 Số ĐT: 0906123456 Gõ dòng ghi chú bắt đầu bởi dấu gạch đầu dồng “-”. Tham khảo Chương 5 - Phần mềm trình chiếu 20 Xây dựng công thức Bắt đầu bởi dấu “=”, “+” , “-”. Thành phần: Hằng (số, chuỗi, ngày tháng, luận lý). Địa chỉ ô. Hàm. Chương 5 - Phần mềm trình chiếu 21 Xây dựng công thức (tt) Toán tử: Toán tử số: +, -, *, /, ^. Toán tử nối chuỗi: &: . Toán tử ngày tháng, thời gian: +, -. Ví dụ: Formulas.xlsx Chương 5 - Phần mềm trình chiếu 22 Sử dụng ĐC tương đối và ĐC tuyệt đối Có thể sử dụng các thao tác chép – dán (hoặc cắt – dán) để tạo ra các khối dữ liệu và công thức tương tự. Chương 5 - Phần mềm trình chiếu 23 Chương 5 - Phần mềm trình chiếu 24 - Địa chỉ ô trong công thức thay đổi ra sao khi di chuyển ? Chương 5 - Phần mềm trình chiếu 25 - Tùy thuộc vào loại địa chỉ được sử dụng. Địa chỉ tương đối Là các địa chỉ thông thường, tự động thay đổi tương tứng khi sao chép, di chuyển công thức. Ví dụ: Chép công thức trong ô D5 sang ô E6. [D5] = B5 * C5 => [E6] = C6 * D6 Chương 5 - Phần mềm trình chiếu 26 Địa chỉ tương đối Là các địa chỉ cố định, không thay đổi khi sao chép, di chuyển công thức. Có thể cố định hàng, cột hay cả hai trong địa chỉ ô. Ví dụ: Chép công thức trong ô E5 sang ô E66. [E5] = D5 * $E$2 => [E6] = D6 * $E$2 Chương 5 - Phần mềm trình chiếu 27 Ví dụ ứng dụng Lập bảng tính tiền bán hàng cho một cửa hàng tại sân bay quốc tế. Số tiền mỗi loại hàng được tính theo JPY và được qui đổi sang VND theo tỷ giá qui định. Xem Hoa don.xlsx Chương 5 - Phần mềm trình chiếu 28 Sử dụng công cụ tự động lấp đầy (AutoFill) Cho phép tạo nhanh chuỗi giá trị tăng hoặc giảm theo mẫu (hai giá trị đầu trong chuỗi). Ví dụ ứng dụng: Tạo dãy số thứ tự (1, 2, 3, …). Tạo dãy giá trị tuần tự (0, 50, 100, 150, …). Chương 5 - Phần mềm trình chiếu 29 Chương 5 - Phần mềm trình chiếu 30 Sử dụng chức năng AutoFill Kiểm soát dữ liệu nhập với Data Validation Chỉ cho phép nhập một số kiểu dữ liệu nhất định => hạn chế sai sót khi nhập liệu. Công cụ: Hộp thoại Data Validation. Chương 5 - Phần mềm trình chiếu 31 Chương 5 - Phần mềm trình chiếu 32 Kiểm soát dữ liệu nhập với Data Validation (tt) Các dữ liệu được phép nhập Mọi giá trị. Các loại số. Số thập phân. Các phần tử trong danh sách. Ngày tháng/thời gian. Chuỗi ký tự độ dài giới hạn. Dạng chỉ định . Chương 5 - Phần mềm trình chiếu 33 Kiểm soát dữ liệu nhập với Data Validation (tt) Phạm vi số/ngày tháng/thời gian/độ dài chuỗi: Between/not between (min, max). Equal to/Not equal to (value). Greate than/Less than (value). Greate than or equal to (value). Less than or equal to (value). Chương 5 - Phần mềm trình chiếu 34 Ví dụ ứng dụng Thiết lập Data Validation kiểm soát mã loại hàng và số lương hàng bán (sheet Hoa don). Qui tắc kiểm tra: Mả hàng: lấy từ Danh sách loại hàng (sheet Loai hang). Số lượng: Số thập phân lớn hơn 0. Xem Data Validation.xlsx Chương 5 - Phần mềm trình chiếu 35 4. Định dạng và thay đổi cấu trúc bảng tính Làm việc với trang bảng tính Làm việc với các hàng và cột Làm việc với các ô và vùng Chương 5 - Phần mềm trình chiếu 36 Làm việc với trang bảng tính Các thao tác cơ bản: Chèn trang mới. Đổi tên trang. Xóa trang bảng tính. Sao chép, di chuyển trang Ẩn/hiện trang. Tạo nền trang. Chương 5 - Phần mềm trình chiếu 37 Làm việc với các hàng và cột Các thao tác cơ bản: Thêm hàng, cột. Xóa hàng, cột. Ẩn/hiện hàng, cột. Thay đổi kích thước hàng, cột. Chương 5 - Phần mềm trình chiếu 38 Làm việc với ô và vùng Các thao tác cơ bản: Định dạng ô. Trộn và tách ô. Đặt tên và quản lý tên vùng. Dán đặc biệt. Tạo ghi chú. Định dạng có điều kiện. Xóa các thành phần trong ô. Chương 5 - Phần mềm trình chiếu 39 Định dạng ô Công cụ: Hộp thoại Fotmat Cell/các lệnh trên thẻ Home. Các nhóm định dạng Địng dạng số. Canh ô. Định dạng font chữ. Tạo khung. Chọn màu nền. Chế độ bảo vệ. Chương 5 - Phần mềm trình chiếu 40 Định dạng số (Number) Các lựa chọn: General : Dạng tổng quát Number : Số thập phân Currency : Dạng tiền tệ Date/Time : Ngày tháng/Thời gian. Percentage: Dạng phần trăm Text : Dạng văn bản Custom : Dạng tùy ý Chương 5 - Phần mềm trình chiếu 41 42 Ví dụ Cho 4 số + 134.567, -1234.567, 0.123 và 0. Hiển thị các số trên theo các dạng khác nhau. Xem Number.xlsx Chương 5 - Phần mềm trình chiếu 43 Canh ô (Aligmrnt) Bao gồm: Canh ngang (General, Left, Right, Center, Fill, Justified, …). Canh dọc (Top, Bottom, Center, Justify, …). Độ thụt (Indent). Chiều chữ (Orrientation). Cho phép xuống hàng, trộn ô, tự động thu nhỏ cỡ chữ. Chương 5 - Phần mềm trình chiếu 44 45 Trộn và tách ô Thực hiện tương tự như trong Word. Các cách thực hiện: Sử dụng hộp thoại Fotmat Cell (The Alignment). Sử dụng thực đơn pop-up. Sử dụng thanh công cụ (Lệnh Home | Alignment | Merge & Center). Chương 5 - Phần mềm trình chiếu 46 Ví dụ ứng dụng Tạo mẫu bảng Tổng kết năm học 2012 - 2013. Xem Tong ket nam hoc.xlsx Chương 5 - Phần mềm trình chiếu 47 Đặt tên và quản lý tên vùng Excel cho phép đặt tên và quản lý tên cho các vùng ô. Tên vùng  địa chỉ tuyệt đối vùng ô. Các thao tác cơ bản: Đặt tên vùng. Thay đổi tên vùng, địa chỉ vùng. Xóa tên vùng. Công cụ: Lệnh Define Name/Name Management. Chương 5 - Phần mềm trình chiếu 48 Chương 5 - Phần mềm trình chiếu 49 Dán đặc biệt Cho phép sao chép một số thành phần nào đó của một vùng ô. Có thể thực hiện một số tính toán khi sao chép. Công cụ: Lệnh Paste Special. Chương 5 - Phần mềm trình chiếu 50 51 Ví dụ ứng dụng Cho bảng . Yêu cầu: Ghép cột Họ và cột Tên thành một cột duy nhất Họ và tên. Chuyển bảng Tiêu chuẩn xếp loại học lực từ dạng hàng sang dạng cột. Cộng thêm 0.5 điểm cho môn M1. Chép mẫu (định dạng) bảng Tổng kết cuối năm sang sheet Sheet2. Chương 5 - Phần mềm trình chiếu 52 Tạo ghi chú. Ghi chú: chuỗi văn bản được hiển thị khi đưa con trỏ tới ô có ghi chú. Công dụng: Làm rõ thêm nội dung, ý nghĩa ô. Tạo, xóa ghi chú: Lệnh Insert Comment/Delete Comment (thực đơn pop-up/ngăn lệnh Review | Comments). Chương 5 - Phần mềm trình chiếu 53 Định dạng có điều kiện Định dạng được áp dụng khi thỏa mãn điều kiện nhất định. Công cụ: Lệnh Conditional Formatting (Thẻ Home | Cells).. Ví dụ: Cho bảng danh sách điểm thi. Yêu cầu: Tô màu nền các ô điểm thi và điểm tổng kết dưới 5. Xem Conditional Formatting.xlsx Chương 5 - Phần mềm trình chiếu 54 Chương 5 - Phần mềm trình chiếu 55 Chương 5 - Phần mềm trình chiếu 56 Xóa các thành phần trong ô Công cụ: Lệnh Clear (thẻ Home | Cells). Các lựa chọn xóa: Clear All: Xóa hết . Clear Formats: Xóa định dạng. Clear Content: Xóa nội dung. Clear Comments: Xóa ghi chú. Clear Hyperlink: Xóa hyperlink. Chương 5 - Phần mềm trình chiếu 57 5. Sử sụng hàm trong Excel Giới thiệu tổng quan Đưa hàm vào công thức Một số loại hàm thông dụng Chương 5 - Phần mềm trình chiếu 58 Giới thiệu tổng quan Hàm: công thức được xây dựng sẵn thực hiện công việc tính toán. Excel có thư viện hàm phong phú với nhiều loại hàm. Cú pháp chung của hàm: Tên_hàm (Ds tham số) Người sử dụng có thể viết thêm các hàm mới bằng ngôn ngữ VBA. Chương 5 - Phần mềm trình chiếu 59 Đưa hàm vào công thức Chọn một trong hai cách: Gõ nhập trực tiếp từ bàn phím. Sử dụng chức năng Insert function. Excrl đưa ra các thông báo giúp đỡ khi nhập hàm. Chương 5 - Phần mềm trình chiếu 60 Chương 5 - Phần mềm trình chiếu 61 Chương 5 - Phần mềm trình chiếu 62 Nhập tham số hàm trên hộp thoại Insert Function Một số hàm thông dụng Hàm số học và lượng giác. Hàm ký tự. Hàm ngày tháng & thời gian. Hàm thống kê. Chương 5 - Phần mềm trình chiếu 63 Hàm số học và lượng giác ABS() INT() MOD(, ) PRODUCT(, [số 2],…) RAND() ROUND(, ) SQRT() Chương 5 - Phần mềm trình chiếu 64 Hàm số học và lượng giác SUM(, [số 2],…) SUMPRODUCT (, [vùng 2],…) PI() COS() SIN() TAN() Chương 5 - Phần mềm trình chiếu 65 Ví dụ Sử dụng hàm ROUND để làm tròn số X tới hàng nghìn và hàng phần trăm. Cho số lượng và đơn giá các loại hàng mua, tính tổng số tiền phải trả. Tính số xe 12 chỗ phải huy động để chở các đoàn khách du lịch. Xem Math Functions.xlsx Chương 5 - Phần mềm trình chiếu 66 Hàm ký tự LEFT(, [n]) RIGHT(, [n]) MID(, , ) UPPER() LOWER() PROPER() LEN() TRIM() FIND(, ,[]) Chương 5 - Phần mềm trình chiếu 67 Ví dụ Cho bảng danh sách hàng nhập/xuất tại một công ty với mỗi lô hàng được đánh một mã lô hàng như sau: Ký tự 1: N/X (Nhập/Xuất). Ký tự 2 – 4: Mã loại hàng. Ký tự 5 – 8: Mã nhà sản xuất. Cho biết loại (nhập/xuất), mã loại hàng, mã nhà SX của từng lô hàng. Chương 5 - Phần mềm trình chiếu 68 Ví dụ Cho danh sách địa chỉ email của các SV trong lớp. Tách tên tài khoản và tên miền trong các địa chỉ được nêu. Xem Ds hang nhap-xuat.xlsx. Chương 5 - Phần mềm trình chiếu 69 Hàm ngày tháng & thời gian NOW() TODAY() DATE(, , ) DAY() MONTH() YEAR() WEEKDAY(, [kiểu]) Chương 5 - Phần mềm trình chiếu 70 Hàm chuyển đổi kiểu VALUE() TEXT(, []) DATEVALUE () Chương 5 - Phần mềm trình chiếu 71 Hàm luận lý NOT() AND(, [biểu thức logic2],…) OR(, [biểu thức logic2],…) IF(, [gt1], [gt2]) Chương 5 - Phần mềm trình chiếu 72 Ví dụ Một cơ quan bảo hiểm xét chế độ nghỉ hưu cho các CBCNV đủ tuổi hưu (nam từ 60 tuổi, nữ từ 55 tuổi trở lên). CBCNV đủ tuổi hưu có số năm công tác từ 25 năm trở lên được lãnh lương hưu hàng tháng, ngược lại, được lĩnh trợ cấp một lần. Cho biết KQ xét nghỉ của các CBCNV. Xem xet nghi hưu.xlsx. Chương 5 - Phần mềm trình chiếu 73 Hàm thống kê MAX(, [gt2],…) MIN(, [gt2],…) AVERAGE(, [gt2],…) AVERAGA(, [gt2],…) AVERAGEIF(, , [vùng tính toán]) COUNT(, [n2],…) COUNTA(, [n2],…) COUNTBLANK() Chương 5 - Phần mềm trình chiếu 74 Hàm thống kê (tt) SUMIF(, , [vùng tính toán]) SUMIFS (, , , [Vùng_ĐK2, điều kiện 2],…) RANK(, , [cách xếp]) Chương 5 - Phần mềm trình chiếu 75 Ví dụ Cho danh mục hàng xuất tháng 10/2013 của một công ty (mã loại hàng, số lượng, đơn giá, thành tiền). Lập bảng báo cáo thống kê tổng lượng hàng bán, tổng doanh số, giá bán bình quân của từng loại hàng. Xem Bao cao thong ke.xlsx. Chương 5 - Phần mềm trình chiếu 76 Hàm tìm kiếm và tham chiếu VLOOKUP(, , , [cách tìm]) HLOOKUP(, , , [cách tìm]) INDEX(, , ) Các kiểu dò tìm trong VLOOKUP và HLOOKUP: Dò tìm chính xác. Dò tìm xấp xỉ. Chương 5 - Phần mềm trình chiếu 77 Ví dụ Căn cứ vào mã loại hàng, dò tìm tên hàng, đơn vị, đơn giá của các loại hàng trong bảng danh mục loại hàng (sheet HOA DON). Căn cứ vào điểm tổng kết, tìm loại học lực của sinh viên trong bảng tiêu chuẩn xếp loại học lực (sheet TONG KET). Xem VLOOHUP.xlsx. Chương 5 - Phần mềm trình chiếu 78 Hàm thông tin ISBLANK () ISTEXT () ISNONTEXT () ISNUMBER () ISERR () ISERROR() ISNA () Chương 5 - Phần mềm trình chiếu 79 Ví dụ Cho danh sách khách thuê phòng tại một khách sạn. Đánh dấu “X” vào cột Ghi chú đối với các khách hàng thuê phòng từ lần thứ 2 trở lên. Xem Khach san.xlsx. Chương 5 - Phần mềm trình chiếu 80 6. Quản trị cơ sở dữ liệu Tổng quan về CSDL trong Excel Một số thao tác CSDL thông dụng Hàm cơ sở dữ liệu Chương 5 - Phần mềm trình chiếu 81 Tổng quan về CSDL trong Excel Cơ sở dữ liệu: tập hợp các dữ liệu được tổ chức, lưu trữ theo một cách thức nào đó cùng một tập các phép toán để thao tác, sử dụng. Excel: CSDL phẳng (bảng 2 chiều). Chương 5 - Phần mềm trình chiếu 82 Cấu trúc CSDL Excel Dòng: Mẫu tin (Records). Cột: Trường (Fields). Dòng 1: Dòng tên trường. Lưu ý: Bảng không chứa các ô được trộn. Các ô tên trường không được để trống hoặc trùng giá trị. Chương 5 - Phần mềm trình chiếu 83 Chương 5 - Phần mềm trình chiếu 84 Chương 5 - Phần mềm trình chiếu 85 Hidden Ưu điểm Không đòi hỏi kiến thức chuyên sâu về CSDL. Thuận lợi cho đa số người dùng. Phạm vi sử dụng rộng rãi. Có nhiều khả năng phân tích. Chương 5 - Phần mềm trình chiếu 86 Khuyết điểm: Kích thước lưu trữ có giới hạn. Xử lý chậm. Không có khả năng thiết lập mối quan hệ. Không đảm bảo sự nhất quán và toàn vẹn dữ liệu. Hạn chế việc sử dụng đồng thời. Chương 5 - Phần mềm trình chiếu 87 Một số thao tác cơ bản Sắp xếp dữ liệu . Lọc dữ liệu. Tính tổng phụ. Hợp nhất dữ liệu. Lập báo cáo tổng hợp. Tách nội dung ô thành nhiều cột. Chương 5 - Phần mềm trình chiếu 88 Sắp xếp dữ liệu Sắp xếp các dòng CSDL theo thứ tự tăng (hoặc giảm) của một số cột. Công cụ: Lệnh Sort (thẻ Data | Sort & Filter). Chương 5 - Phần mềm trình chiếu 89 Chương 5 - Phần mềm trình chiếu 90 Lọc dữ liệu Thực hiện trích lọc dữ liệu thỏa những điều kiện được định trước. Các phương pháp lọc: Lọc tự động (AutoFilter). Loc nâng cao (Advanced Filter). Chương 5 - Phần mềm trình chiếu 91 Lọc tự động Phương pháp lọc đơn giản, dễ thực hiện. Công cụ: Lệnh Home | Editing | Sort & Filter | Filter hđặc Data | Filter. Nguyên tắc: Lần lượt lọc theo từng cột (hiển thị thực đơn và chỉ định điều kiện lọc cho từng cột). Chỉ sử dụng cho các điều kiện lọc không quá phức tạp. Chương 5 - Phần mềm trình chiếu 92 Ví dụ1 Cho CSDL Thống kê bán hàng (tập tin AutoFilter). Sử dụng chức năng AutoFilter, thực hiện các thao tác: Lọc các dòng chi tiết hai loại hàng A và B bán trong ngày 1/6/2013. Lọc các dòng chi tiết bán hàng có doanh số từ 1.000 tới dưới 2.000 USD. Xem AutoFilter.xlsx. Chương 5 - Phần mềm trình chiếu 93 Ví dụ 2 Cho danh sách điểm tổng kết cuối năm của các SV. Thực hiện: Cho biết DS các SV có tên bắt đầu bằng chữ “H” hoặc chữ “N”. Cho biết danh sách các SV khá, giỏi có điểm môn D1 từ 7 điểm trở lên. Xem TKCN.xlsx. Chương 5 - Phần mềm trình chiếu 94 Lọc nâng cao Cho phép lọc dữ liệu với các điều kiện lọc phức tạp. Cần tạo vùng điều kiện lọc (Criteria) trước khi thực hiện lọc nâng cao. Các kiểu vùng điều kiện lọc: Vùng điều kiện dạng so sánh. Vùng điều kiện dạng công thức. Chương 5 - Phần mềm trình chiếu 95 Vùng điều kiện dạng so sánh Gồm nhiều dòng, cột. Dòng 1: Tên các trường (sao chép hoặc tham chiếu với toán tử “-”). Các dòng kế tiếp theo: Điều kiện ràng buộc. Điều kiện mô tả trong các ô cùng hàng được liên kết bởi toán tử AND, điều kiện nêu trong các hàng khác nhau => toán tử OR. Chương 5 - Phần mềm trình chiếu 96 Vùng điều kiện dạng công thức Gồm nhiều dòng, cột. Dòng 1: Tên cột điều kiện (không trùng với các tên trường). Các dòng kế tiếp theo: Điều kiện (tham chiếu tới mẫu tin đầu tiên của CSDL).. Quan hệ giữa các dòng, ô trong vùng ĐK: tương tự như vùng ĐK dạng so sánh. Chương 5 - Phần mềm trình chiếu 97 Thựcc hiện lọc Sử dụng lệnh Data | Advanced Filte: Chỉ định vùng CSDL. Chỉ định vùng điều kiện. Chỉ định vùng chứa kết quả. Đặt tùy biến loại bỏ các dòng trùng lặp. Chương 5 - Phần mềm trình chiếu 98 Chương 5 - Phần mềm trình chiếu 99 Ví dụ Thực hiện lại các yêu cầu được nêu trong hai ví dụ trước với chức năng Advanced Filter. Xem AdvencedFilter.xlsx. Xem TKCN.xlsx. Chương 5 - Phần mềm trình chiếu 100 Tính tổng phụ Thực hiện việc thống kê nhóm với các hàm thống kê như: Sum, Count, Average, Max, Min… Cần sắp xếp lại CSDL theo thứ tự tăng của trường được dùng để phân nhóm trước khi thống kê nhóm. Công cụ: Lệnh Data | Subtotal. Chương 5 - Phần mềm trình chiếu 101 Chương 5 - Phần mềm trình chiếu 102 Chương 5 - Phần mềm trình chiếu 103 Hợp nhất dữ liệu Tổng hợp số liệu trên nhiều bảng. Ví dụ: Lập bảng tổng hợp doanh số quý từ các bảng số liệu doanh số tháng. Lập bảng tổng hợp doanh số công ty từ các bảng số liệu doanh thu của các cửa hàng. Công cụ: Lệnh Consolidate (thẻ Data). Chương 5 - Phần mềm trình chiếu 104 Lập báo cáo tổng hợp Tóm tắt dữ liệu, công cụ: tạo báo cáo từ CSDL Excel hay nguồn dữ liệu từ bên ngoài. Công cụ: Lệnh Insert | Pivot Table | Pivot Table. Ví dụ: Chương 5 - Phần mềm trình chiếu 105 Chương 5 - Phần mềm trình chiếu 106 Chương 5 - Phần mềm trình chiếu 107 Tách nội dung ô thành nhiều cột Tách chuỗi ký tự nội dung ô thành các từ ghi trong các cột liên tiếp. Ví dụ: Tách cột Họ tên thành các cột chứa họ, họ lót và tên(mỗi cột chứa một từ trong họ tên). Công cụ: Lệnh Data | Data Tools | Text to Columns. Chương 5 - Phần mềm trìn