Giáo trình Excel nâng cao

Nội dung text: Giáo trình Excel nâng cao

1. Giáo trình Excel nâng cao Mục lục Lời nói đầu Bài 1:Hệ thống hóa kiến thức cơ bản về Excel I. Những thao tác đầu tiên với Exel - các thao tác cơ bản trong bảng tính - Xử lý dữ liệu trong bảng tính II. Hàm và công thức - Hàm trong Excel - Một số hàm cơ bản Bài 2: Làm bài tập kiểm tra trình độ sử dụng Excel Bài 3: Kỹ năng sử dụng hàm trong Excel - Định nghĩa hàm - Các hàm thông dụng - Các hàm nâng cao Bài 4: Phương pháp tổ chức bảng dữ liệu I. Các thao tác với cơ sở dữ liệu - Sắp xếp dữ liệu - Tạo tổng cấp dưới ( Subtotals ) - Lọc dữ liệu ( Fliter ) - Tạo cơ sở dữ liệu tổng hợp các cơ sở dữ liệu chi tiết II. Các hàm thông dụng trong Cơ sở dữ liệu Bài 5: Phương pháp kết nối, tổng hợp, kết xuất dữ liệu Bài 6: Trình bày, in ấn, lưu trữ, bảo mật, phân phối bảng số liệu, file Bài 7 : Marco và VBA trong Excel I. Marco trong Excel 1. Giới thiệu Marco 2. Tạo Marco 3. Gán một phím tắt cho Marco Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 1
2. Giáo trình Excel nâng cao - Hàng : Trỏ chuột vào vạch ngang d − ới số thứ tự hàng sao cho xuất hiện ╪ , kéo và thả vạch đó tại vị trí mới. • Nhiều cột / Nhiều hàng : - Cột : - Chọn một số ô của các cột. - Format → Colum → Width. - Gõ vào độ rộng mới cho các cột rồi OK - Hàng : - Chọn một số ô của các hàng. - Format → Row → Height. - Gõ vào chiều cao mới cho các hàng rồi OK b. Chèn thêm cột, hàng, ô • Cột : - Chọn khối là tên các cột (các chữ A, B, ) tại vị trí cần chèn, cần thêm bao nhiêu cột ta chọn bấy nhiêu. - Chọn Insert → Columns. Excel sẽ chèn thêm các cột trống và đẩy các cột được chọn sang phải • Hàng : - Chọn khối là số thứ tự của các hàng (các số 1, 2, ) tại vị trí cần chèn, cần thêm bao nhiêu hàng ta chọn bấy nhiêu. - Chọn Insert → Rows . Excel sẽ chèn thêm các hàng trống và đẩy các hàng được chọn xuống dưới. Kéo và thả vạch này tại vị trí mới để thay đaei độ rộng cột C Kéo và thả vạch này tại vị trí mới để thay đổi chiều cao hàng 4 • Ô : - Chọn khối là các ô tại vị trí Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 3
3. Giáo trình Excel nâng cao - Chọn các ô muốn cho đổi chỗ hay còn gọi là miền nguồn ( A2:A6 ) - Chọn biểu tượng Copy hoặc Ctrl+C - Đưa con trỏ về ô đầu tiên của miền dán ( C3 ). - Chọn mục Edit, Past Special, đánh dấu chọn ô Transpose - OK Chú ý : Miền nguồn và miền dán không được giao nhau ( không được có ô chung ) e. Ẩn, hiện cột, hàng Để tiện cho thao tác, trên các bảng tính, nhất là các bảng tính lớn, người ta thường cho ẩn các cột ( hoặc hàng ) không cần thiết. Khi nào cần lại cho chúng hiện trở lại. Cách làm như sau : - Chọn các cột ( hoặc các hàng ) cần ẩn đi. - Chọn Format, Column ( hoặc Row ). - Chọn Hide . Tại vị trí các cột ( hoặc hàng ) ẩn, xuất hiện đường kẻ dọc ( hoặc ngang ) đậm, các cột ( hoặc hàng) bị ẩn vẫn có tác dụng (vẫn sử dung để tính toán). Để cho chúng hiện trở lại, ta làm như : - Chọn các cột ( hoặc các hàng ) liền kề với chúng. Ví dụ cần cho các cột C, D, E hiện trở lại, ta chọn các cột đứng ngay trước và sau chúng : B, F; cần cho các hàng 4, 5, 6 hiện trở lại, ta chọn các hàng ở ngay trên và ngay dưới chúng : 3, 7. - Chọn Format, Column( hoặc Row ), Unhide f. Cố định cột, hàng tiêu đề - Ở các bảng tính lớn, khi cuộn xem hoặc nhập dữ liệu ở phần dưới thì không còn thấy tiêu đề cột của chúng ở hàng trên cùng, do đó rất dễ bị nhầm lẫn giữa cột nọ với cột kia. Tương tự như vậy, khi cuộn xem hoặc nhập dữ liệu ở bên phải thì không còn thấy Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 5
4. Giáo trình Excel nâng cao Chọn mục Number trong nhóm định dạng Category và nháy chọn ô Use 1000 Separator (,), chúng ta có thể thấy giá trị sô của ô đổi sang dạng 11,111.99 ở mục Sample Nhấn nút OK để kết thúc Chúng ta có thể định dạng để số 11111.99 được biểu diễn trên màn hình theo dạng 11111.99, tức là có khoảng trống giữa số hàng nghìn và số hàng trăm để dễ đọc hơn. Các bước thao tác ta làm như sau: Trong hộp thoại Format cell, ở thẻ number và trong nhóm định dạng Catergory ta chọn Custom. Sau đó nhập vào ô Type dãy ký tự #### ##0.00. Sau đó nhấn OK để kết thúc Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 7
5. Giáo trình Excel nâng cao d. Biểu diễn dữ liệu dạng phần trăm Trong một vài trường hợp, người dùng muốn biểu diễn một số dưới dạng phần trăm, ví dụ: giá trị 0.56 sẽ được hiển thị là 56% Các bước thực hiện như sau: Mở bảng tính mới, nhập số 0.5678 vào ô C1, Nhấn nút % trên thanh công cụ để có dạng biểu diễn 57% Nhấn nút trên thanh công cụ để có dạng biểu diễn 56.8% e. Thay đổi kiểu chữ, cỡ chữ, dạng chữ Trong MS – Excel, việc thay đổi kiểu chữ, cỡ chữ dạng in đậm / nghiêng / gạch chân được thực hiện theo cách đã làm trong MS – Word. - Format / Cells / Font - Trong Font Style chọn Italic để in nghiêng, chọn Bold để in đậm, chọn Bold Italic để in vừa nghiêng vừa đậm, chọn Regular để ký tự trở lại bình thường. - Trong U nderline chọn một kiểu gạch chân : None ( bỏ gạch chân ), Single ( gạch bằng nét đơn ), Double ( gạch bằng nét đôi ), Single Accounting ( gạch bằng nét đơn kiểu tài chính đến cuối mép phải của Font Font SizeColor Font Color ô ), Double Accounting ( gạch bằng nét đôi kiểu tài chính đến cuối mép phải của ô ) - Chọn Color để đổi mầu chữ. - Trong ô Effect : chọn Strikerthough ( gạch ngang ), Superscript ( số mũ ), Subscript ( chỉ số ) - Chọn Normal Font nếu muốn bỏ mọi trình bầy và lấy lại kiểu ngầm định Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 9
6. Giáo trình Excel nâng cao =MONTH ( "27- Sep" ) trả về 9 Time ( hour, minute, second ) Chỉ ra thời gian dạng số. Ví dụ : =TIME(19,5,14) trả về 19:05:14 hoặc 7: 05 PM WEEK DAY ( date ) Chỉ ra số thứ tự của ngày trong tuần của biến ngày tháng date (Thứ Hai là ngày thứ 1, Thứ Ba là ngày thứ 2, , Chủ Nhật là ngày thứ 7 ) Ví dụ : =WEEKDAY(27-09-04) trả về 6 YEAR ( date ) Số năm của biến ngày tháng date. Ví dụ : =YEAR ( 27-09-04 ) trả về 2004 5. Hàm tìm kiếm tham chiếu Vlookup ( lookup_value, table_array, col_index_num, r ange_lookup ) Lookup_value Giá trị được tìm kiếm trên cột bên trái của Table_array Table_array Vùng tìm kiếm hay cssn gọi là bảng tra cứu, địa chỉ phải là tuyệt đối, nên đặt tên cho vùng Col_index_num Số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá trị trả về Range_lookup Giá trị logic xác định việc tìm kiếm là chính xác hay gần đúng, nếu là: True hay 1 Cột đầu tiên phải được sắp xếp tăng dần (khi đó có thể bỏ qua tham số thứ 4 này). Khi không thấy sẽ lấy kết quả gần đúng, vì thế cssn gọi là dò tìm không chính xác. False hay 0 Cột đầu tiên không cần sắp xếp .Tìm chính xác, trả về #N/A nếu không thấy. Ví dụ : Bảng sau là kết quả thi của học sinh, dựa vào điểm Trung bình hãy xếp loại học sinh theo thang điểm : Kém 5 Trung bình 7 Khá 8 Giỏi 9.5 Xuất sắc Để dùng hàm VLOOKUP, ta cần thực hiện các bước sau : - Trong miền C15:D19 gõ vào thang điểm trên dưới dạng cột. Vì đây là cách dò tìm không chính xác (trong một khoảng) nên chỉ gõ vào cận dưới ( theo chiều tăng ) của mỗi loại. Như vậy : lookup_value là E3 (điểm Trung bình của học sinh thứ nhất) Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 11
7. Giáo trình Excel nâng cao lookup_value là E3 ( điểm Trung bình của học sinh thứ 1 ) = VLOOKUP(E3,$C$14: $D$19,2) table_array là miền $B$21: $F$22 ( miền địa chỉ tuyệt đối, không đưa cột tiêu đề A21: A22 vào ) row_index_num là 2 vì cần lấy giá trị của hàng Loại, hàng này có số thứ tự là 2 trong miền B21: F22 - Tại ô F3 điền vào công thức = HLOOKUP(E3,$B$21: $F$22,2), ta nhận được Trung bình - Copy công thức ở ô G3 xuống các ô từ G4 đến G12, Excel sẽ xếp loại cho các học sinh còn ĐỒ THỊ Khả năng biểu diễn số liệu bằng đồ thị của Excel rất phong phú. Các biểu đồ được cài đạt trên bảng tính tăng thêm sức hấp dẫn và thuyết phục của số liệu. 1. Tạo các kiểu biểu đồ, đồ thị khác nhau Mở bảng tính mới và nhập dữ liệu ở Sheet 1 theo hình dưới đây - Chọn biểu tượng ( ChartWizard ), con trỏ chuột trở thành dấu + Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 13
8. Giáo trình Excel nâng cao Chọn Next để chuyển sang bước thứ 4 Chọn Sheet 2 trong ô AS Object In để đặt biểu đồ kết quả Nhấn nút Finnish Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 15
9. Giáo trình Excel nâng cao BÀI TẬP KIỂM TRA TRÌNH ĐỘ SỬ DỤNG EXCEL BÀI TẬP THỰC HÀNH EXCEL BÀI THỰC HÀNH SỐ 1 Bước đầu học viên thực hiện các yêu cầu sau: * Định dạng lại dữ liệu ngày, số từ Menu Start / Settings / Control Panel / Regional Settings. * Khởi động Excel và thực hiện các thao tác về khối với thiết bị chuột: - Nhập vào ô A1 nội dung: Cộng hoà - Sao chép dữ liệu ô A1 sang các ô trong khối A2:C5 - Di chuyển khối dữ liệu A2: C5 đến vị trí D2: F5 - Thực hiện thao tác thay đổi độ rộng cột, độ cao hàng * Thực hiện một số thao tác định dạng: - Nhập vào ô A1 nội dung: Công ty thương mại dịch vụ - Đưa dòng Công ty thương mại dịch vụ vào nằm giữa khối A1: F1 - Điền một dãy số có thứ tự tăng dần từ 1 đến 10 - Nhập vào ô C4 con số: 200000, sau đó định dạng con số này theo dạng số tài chính. - Nhập vào ô D4: 01/12/2004 cho đúng với định dạng kiểu dd/mm/yyyy. Nhập vào bảng tính sau : Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 17
10. Giáo trình Excel nâng cao I. ĐỊNH NGHĨA HÀM Hàm là một thành phần của dữ liệu loại công thức và được xem là những công thức được xây dựng sẵn nhằm thực hiện các công việc tính toán phứp tạp. Dạng thức tổng quát: (Tham số 1, Thamsố 2, ) Trong đó: là tên qui ước của hàm, không phân biệt chữ hoa hay thường Các tham số: Đặt cách nhau bởi dấu "," hoặc ";" tuỳ theo khai báo trong Control Panel (xem phần khai báo môi trường - chương II) Cách nhập hàm: Chọn một trong các cách: - C1: Chọn lệnh Insert / Function - C2: Ấn nút Insert Function trên thanh công cụ - C3: Gõ trực tiếp từ bàn phím II. CÁC HÀM THÔNG DỤNG TRONG EXCEL ( xem phần 1 ) III. CÁC HÀM NÂNG CAO TRONG EXCEL 1.Hàm điều kiện IF - Chức năng:Thực hiện lựa chọn có điều kiện - Cú pháp : =IF(logical_test,value_if_true,value_if_false) Trong đó: Logical_test: Điều kiện tính toán value_if_false: Giá trị nhận khi điều kiện sai value_if_true: Giá trị nhận khi điều kiện đúng Có thể hiểu cách thực hiện hàm này như sau:Nếu ( điều kiện đúng ) thì ( thực hiện biểu thức 1 ) ngược lại thì ( thực hiện biểu thức 2) Ví dụ: Hãy nhập bảng tính sau vào ô sau đó hãy tính thưởng phạt cho mỗi cán bộ với điều kiện sau: Nếu số công >=22 thì thưởng 5% lương chính Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 19
11. Giáo trình Excel nâng cao Đỗ nếu điểm Toán >= 5 và môn tin >= 5 Hướng dẫn : AND(Điểm_Toán>=5,Điểm_tin>=5) Đỗ Trượt Nhập công thức vào ô E2 như sau : =IF(AND(C2>=5,D2>=5),"Đỗ","Trượt") b. Hàm OR: - Chức năng: cho kết quả là giá trị logic True ( Đúng) hoặc False ( Sai). Hàm nhận kết quả đúng khi một trong các biểu thức logic nhận giá trị đúng và ngược lại. Hàm OR ít khi dùng độc lập mà thường nằm trong các hàm khác làm chức năng đối số cho các hàm đó. - Cú pháp: =OR(logic1,logic2, ) Trong đó : Logic1, Logic 2, là các biểu thức logic cho giá trị True hoặc False Ví dụ: Hãy tính thưởng phạt nếu là nữ hoặc lương chính dưới 300000 thì thưởng 3% lương chính, nhập công thức vào ô F2 như sau Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 21
12. Giáo trình Excel nâng cao Để thực hiện các tính toán khác nhau và tạo nên giá trị cho khả năng toán học a. Hàm lấy giá trị tuyệt đối ABS: - Chức năng: cho giá trị là giá trị tuyệt đối của biểu thức số học - Cú pháp: = ABS(number) Trong đó: biểu thức số học có thể là một con số hay một biểu thức cho kết quả một con số. Ví dụ: Tính giá trị tuyệt đối của biểu thức (12/4-20); Nhập hàm = ABS(12/4-20) cho kết quả là 17; = ABS(-12) cho kết quả là 12; b. Hàm SQRT: - Công dụng: Trả về giá trị là căn bật hai của số n - Cú pháp: =SQRT(number) Trong đó: number là giá trị số hoặc địa chỉ ô chứa giá trị không âm - Ví dụ: SQRT(9) cho kết quả là 3 c. Hàm ROUND: - Cú pháp: =ROUND(m, n) - Công dụng: Làm tròn số thập phân m đến n chữ số lẻ. Nếu n dương thì làm tròn phần thập phân. Nếu n âm thì làm tròn phần nguyên. Ví dụ 1: ROUND (1.45,1) cho kết quả là 1.5 Ví dụ 2: ROUND (1.43,1) cho kết quả là 1.4 Ví dụ 3:ROUND (1500200,-3) cho kết quả là 1500000 Ví dụ 3:ROUND (1500200,-3) cho kết quả là 1501000 d. Hàm INT: - Công dụng: Trả về giá trị là phần nguyên của số thập phân n - Cú pháp: =INT(n) - Ví dụ: =INT(1.43) cho kết quả là 1 e. Hàm MOD: Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 23
13. Giáo trình Excel nâng cao - Cú pháp: =RIGHT(s, n) - Công dụng: Trích ra n ký tự của chuỗi s kể từ bên phải. - Ví dụ: RIGHT(“EXCEL”,2) cho kết quả là “EL” *Hàm LOWER Chức năng: Thực hiện đổi chuỗi ký tưh hoa ra chữ thường - Cú pháp: =LOWER(text) - trong đó text là chuỗi có dạng chữ viết hoa Ví dụ:=LOWER("Hà Nội") cho kết quả là chuỗi hà nội f. Hàm MID: - chức năng:Cho một số các ký tự từ một chuỗi văn bản - Cú pháp: =MID(text,start_num,num_char) Trong đó: Text là một chuỗi chứa các ký tự cần lấy Start_num là vị trí bắt đầu lấy num_char là vị trí bắt cần lấy - Ví dụ: MID(“EXCEL”,3,2) cho kết quả là “CE” g. Hàm LEN: - Công dụng: Trả về giá trị là chiều dài của chuỗi s. - Cú pháp: = LEN(text) Trong đó Text là chuỗi ký tự cần xem độ dài - Ví dụ: LEN(“EXCEL”)→5 h. Hàm TRIM: - Công dụng: Chuẩn hóa các khoảng trắng trong một văn bản bằng cách loại bỏ các khoảng trắng vô nghĩa. - Cú pháp: =TRIM(text) Trong đó: Text là chuỗi ngầm - Ví dụ: =TRIM(“ Cao bằng địa đầu ”) cho kết quả là "Cao bằng địa đầu" Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 25
14. Giáo trình Excel nâng cao =COUNT(C2:C6) cho kết quả là 5 c. Hàm COUNTIF: - Công dụng: Đếm số ô thỏa mãn điều kiện trong phạm vi. - Cú pháp: =COUNTIF(range,criteria) Trong đó: range là vùng khối chứa điều kiện Criteria là điều kiện - Ví dụ: Đếm những người có giới tính là nữ thì dùng công thức: =COUNTIF(D2:D6, “nữ”) cho kết quả là 4 Chú ý: Trừ trường hợp điều kiện là một con số chính xác thì các trường hợp còn lại đều phải bỏ điều kiện trong một dấu ngoặc kép. 4. Hàm tính tổng có điều kiện SUMIF: - Công dụng: Tính tổng những ô khi thỏa mãn điều kiện nào đó - Cú pháp: =SUMIF(Range,criteria,sum_range) Range:vùng chứa điều kiện, Criteria: chỉ ra điều kiện cần tính tổng sum_range: vùng cần tính tổng - Ví dụ: Tính tổng số công của những người có giới tính là nữ Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 27
15. Giáo trình Excel nâng cao + Nếu r=0 ( hoặc FALSE ) thì tìm giá trị chính xác bằng với n. Nếu không tìm thầy thì trả về lỗi #N/A ( lỗi không tìm thấy ) Ví dụ: Điền giá trị cho cột Phụ cấp trong bảng dưới dựa vào chức vụ và trả ở bảng 1 VLOOKUP(C2,$F$3:$G$7,2,0) + Nếu r=1 (hoặc TRUE) thì cột đầu tiên của khối phải được sắp xếp tăng dần và lúc đó nếu không tìm thấy giá trị chính xác với n sẽ lấy giá trị tương ứng gần của n. Ví dụ: Điền giá trị cho cột Xếp loại trong bảng sau dựa vào ĐTB và tra ở bảng Tra VLOOKUP(C2,$F$2:$G$5,2,1) b. Hàm HLOOKUP HLOOKUP có cú pháp và công dụng tương tự VLOOKUP nhưng được dùng trong trường hợp bảng tra được bố trí theo hàng ngang thay vì theo hàng dọc như Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 29
16. Giáo trình Excel nâng cao để khai thác, truy cập nhanh chóng. - Dòng đầu tiên dùng để chứa tên vùng tin hay còn gọi là trường (Field) của CSDL.Tên vùng tin phải là kiểu chuỗi và duy nhất. - Những dòng kế tiếp dùng để chứa nội dung CSDL. Mỗi hàng được gọi là mẩu tin hay bản ghi (Record) II. THAO TÁC VỚI CSDL: 1. Sắp xếp dữ liệu: - Chọn phạm vi cần sắp xếp - Chọn lệnh Data / Sort, xuất hiện hộp thoại: + Sort by: Chọn cột ( Field ) làm tiêu chí chính để sắp xếp. + Chọn kiểu sắp xếp: Ascending: Sắp xếp theo thứ tự tăng dần. Descerding: Sắp xếp theo thứ tự giảm giần. + Then by: Chọn cột làm tiêu chí sắp xếp phụ ( Excel sẽ sắp xếp dựa vào tiêu chí phụ này khi tiêu chí chính trong mục Sort by bị trùng ). + My List has: Tùy chọn cho dòng tiêu đề của CSDL: Header row: Chọn mục này khi trong phạm vi đang chọn có dòng tiêu đề ( không sắp xếp dòng đầu tiên ). No Header row: Chọn mục này khi trong phạm vi đang chọn không có dòng tiêu đề (sắp xếp tất cả các dòng). 2. Tạo tổng cấp dưới ( Subtotals ): Lệnh dùng để nhóm dữ liệu theo từng nhóm đồng thời chèn vào cuối mỗi Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 31
17. Giáo trình Excel nâng cao tượng lọc là các Menu DropDown. - Muốn lọc theo điều kiện ở cột nào thì kích chuột vào biểu tượng lọc của cột đó để chọn một trong các mục có sẵn. Trong đó gồm: + All: Cho hiển thị tất cả các bản ghi. + Top 10: Lọc một nhóm các bản ghi có giá trị lớn nhất hoặc nhỏ nhất trong cột đang xét. Mục này chỉ có giá trị đối với dữ liệu kiểu số. + Custom: Cho phép người sử dụng tự chọn điều kiện theo hộp thoại Chọn phép toán so sánh cần dùng trong hộp danh sách bên trái: - equals: bằng - does not equal: không bằng (khác) - is greater than: lớn hơn - is greater than or equal to: lớn hơn hoặc bằng - is less than: nhỏ hơn - is less than or equal to: nhỏ hơn hoặc bằng - begins with: bắt đầu bằng - does not begin with: không bắt đầu bằng - ends with: kết thúc bằng - does not end with: không kết thúcbằng - contains: chứa - does not contain: không chứa Gõ hoặc chọn giá trị làm điều kiện trong hộp danh sách bên phải Có thể kết hợp thêm một điều kiện lọc nữa bằng cách chọn tương tự trong hai hộp Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 33
18. Giáo trình Excel nâng cao Phòng ban K - Điều kiện kết hợp: + VÀ: gõ các điều kiện trên cùng một hàng Ví dụ: Để lọc các nhân viên thuộc phòng Kinh doanh và có Lương CB dưới 1.000.000 thì tạo bảng điều kiện như sau: Phòng ban Lương CB Kinh doanh <1.000.000 + HOẶC: gõ các điều kiện trên các hàng khác nhau Ví dụ: Để lọc các nhân viên thuộc phòng Kế toán hoặc Kinh doanh thì tạo bảng điều kiện như sau: Phòng ban Kế toán Kinh doanh * Thao tác lọc - Tạo bảng điều kiện (cách tạo đã trình bày ở trên) - Gọi lệnh Data – Filter – Advanced Filter. Xuất hiện hộp thoại : + Action: Chọn 1 trong 2 hành động sau: Filter the list, in-place: Kết quả lọc xuất hiện ngay trên CSDL gốc. Các dòng không thỏa mãn điều kiện sẽ bị ẩn Copy to another location: Kết quả lọc Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 35
19. Giáo trình Excel nâng cao + Function: Chọn hàm cần dùng để tổng hợp + Reference: Nhập hoặc dùng chuột để quét chọn và ấn nút Add lần lượt toạ độ các bảng chi tiết cần tổng hợp. + Top Row: Tạo dòng tiêu đề cho bảng tổng hợp. + Left Column: Tạo tiêu đề cột đầu tiên cho bảng tổng hợp. + Create link to source data: Tạo mối liên kết từ bảng tổng hợp đến các bảng chi tiết nhằm mục đích nếu có sự thay đổi trong các bảng dữ liệu chi tiết thì các dữ liệu liên quan trong bảng tổng hợp cũng tự thay đổi theo. III. CÁC HÀM TRONG CƠ SỞ DỮ LIỆU: 1. Đặc điểm chung của các hàm trong cơ sở dữ liệu: - Dạng tổng quát: (Database, Field, Criteria) - Các hàm sử dụng trong CSDL đều có 3 đối số: + Database: Là địa chỉ CSDL muốn thao tác + Field: Chỉ định cột nào trong Database sẽ được sử dụng cho việc tính toán trong hàm. Field: có thể được khai báo 2 cách: Cách 1: Khai báo bằng số thứ tự của cột trong CSDL ( cột đầu tiên bên trái của Database là cột 1 , cột kế tiếp là 2 ) Cách 2: Khai báo bằng tiêu đề cột ( đặt trong dấu ngoặc kép ) + Criteria: Là vùng chứa điều kiện tính toán. Vùng điều kiện phải được tạo trước Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 37
20. Giáo trình Excel nâng cao Có hai kiểu liên kết : - Liên kết bản sao của các ô trong worksheet - Liên kết dữ liệu 1. Liên kết bản sao của các ô trong worksheet - Để tạo ra khuôn dạng trang nhằm hiển thị dữ liệu từ nhiều worksheet và biểu đồ - Cho phép xây dựng hệ thống quản lý thông tin lấy dữ liệu từ nhiều nguồn khác nhau * Đặc điểm các bản sao của ô liên kết: - Có thể được mở ra và cập nhật một cách nhanh chóng. - Có thể định dạng được bằng các kỹ thuật định dạng thông thường giúp bản sao này đẹp hơn. - Có thể điều chỉnh kích cỡ và di chuyển không phụ thuộc vào các vị trí ô. - Các bản sao của ô liên kết và biểu đồ được in cùng với nhau nếu chúng xuất hiện cùng một trang. - Có thể được kết nối với các macro, do đó khi bản sao được chọn, macro sẽ thi hành * Nhược điểm các bản sao liên kết: - Nó không phải là ô thực sự, do đó không thể nhập dữ liệu vào đó . - Nó không thể được sử dụng trong phép tính toán. a. Tạo bản sao * Yêu cầu : hãy mở một bảng tính bất kỳ. sau đó hãy tạo một bản sao của ô liên kết * Hướng dẫn : Bước 1: Mở workbook nguồn Bước 2: Chọn vùng muốn chép. Bước 3: Edit / Copy hay nhấn tổ hợp phím Ctrl + C Bước 4: Mở workbook đích Bước 5: Chọn ô sẽ là góc trái trên của vủng nhận bản sao. Bước 6: Bấm phím Sheet trong khi chọn Edit / Paste Picture link. * Kết quả là xuất hiện một bản sao trên worksheet vừa chọn Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 39
21. Giáo trình Excel nâng cao B2: Chọn vùng các ô cung cấp thông tin B3: Edit /Copy hoặc bấm tổ hợp phím Ctrl+C B4: Kích hoạt Workbook đích để nhận dữ liệu B5: Chọn vị trí cần liên kết B6: Edit / Paste Specjal xuất hiện hộp thoại Paste Specjal B7: Bấm chọn nút lệnh Paste Link Màn hình xuất hiện dữ liệu liên kết b. Liên kết dữ liệu trong công thức B1: Mở các Workbook nguồn và đích B2: Kích hoạt Workbook đích B3: Nhập công thức ( Nếu dữ liệu thuộc Workbook nguồn. Hãy mở Workbook nguồn và chọn ô hay vùng dữ liệu phục vụ cho công thức tinh toán ) C.Mở các Workbook liên kết Khi Workbook được mở ra. Dữ liệu liên kết trong Workbook đích trong Workbook đích sẽ cập nhật ngay sau khi mở ra. Nếu Workbook nguồn chưa được mở ra khi Workbook đã được mở ra rồi, xuất hiện hộp thoại: - Bấm chọn Yes, Excel sẽ tụ đọc Workbook nguồn và cập nhật dữ liệu mới vào - Nếu chọn NO, Excel sẽ giữ nguyên dữ liệu cũ. d. Cố định các liên kết B1: Chọn các ô chứa công thức muốn cố định ' B2: Bấm tổ hợp phím Ctrl+C B3: Edit / Paste Special xuất hiện hộp thoại Paste Special B4: Chọn lựa Values 3. Tích hợp các Worksheet Khi thực hiện tích hợp các Worksheet, Excel sẽ thực hiện các phép toán tương tự Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 41
22. Giáo trình Excel nâng cao - Chọn Create Link to Source Data nếu muốn vùng đích được liên kết với vùng nguồn - Thao tác 7: Bấm chọn nút lệnh /OK * ĐIỀU CHỈNH CÁC LIÊN KẾT Mục đích: Thêm xóa các vùng nguồn mới cho danh sách - B1: Data /Consolidata - B2: Chọn vùng nguồn muốn điều chỉnh trong hộp danh sách All Reference. - B3: Xóa vùng nguồn : Bấm nút lệnh Delete - Thêm vùng nguồn:Xác định vùng nguồn chứa dữ liệu mới cần tích hợp - bấm nút lệnh Add. Hãy Xét ví dụ sau: Hãy tạo bảng tính theo 1 sheet bất kỳ: Hãy thay đổi tên sheet chứa bảng điểm học kỳ 1 là liên kết 1. Hãy tạo 1 bảng sau sang 1 sheet mới Hãy đặt chứa bảng điểm học kỳ 2 là liên kết 2 Hãy tính Điểm trung bình, xếp loại, xếp hạng cho các học sinh trong 2 học kỳ 1 và 2 Hãy liên kết 2 bảng trên thành 1 bảng đặt sang 1 sheet mới để tính tổng kết năm học Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 43
23. Giáo trình Excel nâng cao - Thao tác 2: Data / Consolidate xuất hiện hộp thoại Consolidate - Thao tác 3: Đặt con trỏ vào hộp Reference. Sau đó xác định vùng nguồn chứa dữ liệu cần tích hợp, sau đó bấm add Thao tác 4: Lặp lại thao tác 3 để đưa tất cả các vùng nguồn vào danh sách All Reference. Thao tác 5: Chọn hàm sử dụng trong quá trình tích hợp trong quá trình tích hợp Funtion Thao tác 6: (Có thể có hoặc không ) - Đánh dấu tùy chọn Top Row để copy nhãn dòng vào quá trình tích hợp - Đánh dấu tùy chọn Left Column để copy cột bên trái của vùng nguồn vào quá trình tích hợp. - Chọn Create Link to Source Data nếu muốn vùng đích được liên kết với vùng nguồn - Thao tác 7: Bấm chọn nút lệnh /OK BÀI 6 TRÌNH BÀY IN ẤN, LƯU TRỮ, BẢO MẬT, PHÂN PHỐI BẢNG SỐ LIỆU, FILE I. Xem trước khi in Để xem tổng thể trước khi in thì dùng lệnh File / Print Preview ( hoặc kích chuột vào nút Print Preview trên thanh công cụ Standard ) 1. Định dạng trang in Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 45
24. Giáo trình Excel nâng cao - Ngăn Header / Footer (hình trên) dùng để tạo tiêu đề đầu và chân trang: Các nút Custom Header và Custom Footer lần lượt dùng để tạo tiêu đề đầu và chân trang. Nếu chọn nút Custom Header thì xuất hiện hộp thoại để tạo tiêu đề đầu như sau: - chọn ngăn sheet: để chọn vùng in + Print Area: Địa chỉ vùng cần in trong bảng tính + Row to Repeat at top: Dòng cần lặp lại ở đầu mỗi trang + Column to repeat at left: Cột cần lặp lại bên trái ở mỗi trang in + Gridlines: In nội dung bảng tính có đường lưới + Row and column header: In cả tiêu đề cột và số thứ tự dòng + Black and white: Chỉ in trắng đen + Draft quality: Chế độ in lợt + Comments: In chú thích (None: Không in; At end of sheet: In chú thích ở cuối trang; As displayed on sheet: In như đang hiển thị trên sheet) + Cell errors as: Chỉ định in các ô bị lỗi (Displayed: In như hiển thị lỗi; Blank: Để trống; Thay các ô lỗi bằng dấu gạch; #N/A: Các ô bị lỗi thì in chữ #N/A) + Page order: Chỉ định thứ tự in các trang Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 47
25. Giáo trình Excel nâng cao Nếu bạn phải thường xuyên làm việc với những bảng tính của Excel thì vấn đề đặt ra là làm sao để bảo vệ những giá trị có trong đó để tránh bị sai sót hoặc mất mát ngoài ý muốn. Và để đáp ứng yêu cầu đó, Microsoft Excel cũng đã đưa ra tiện ích giúp để đặt mật mã cho file Excel. Trong Microsoft Excel, bạn có thể bảo vệ từng hàng, từng cột, hoặc một nhóm các hàng các cột trong từng worksheet hoặc toàn bộ file bằng cách đặt password lên từng mục bạn chọn, mà những ai không được phép không thể can thiệp vào file Excel đã được bảo vệ để chỉnh sửa. Bạn có thể đặt 2 dạng password: - Password để mở file: Nếu bạn đặt password theo dạng này, bạn phải điền đúng password để mở file, nếu không bạn sẽ không thể mở file đó. - Password để chỉnh sửa file: Nếu bạn đặt password theo dạng này, bạn phải điền đúng pasword mới có thể chỉnh sửa lại nội dung file, nhưng vẫn xem nội dung của file đó. Để đặt password cho tài liệu Ecxel, thực hiện theo các bước sau: - Click Tools / Options từ menu sổ xuống. Một cửa sổ mới xuất hiện, tại đây bạn chọn thẻ Security. Tại thẻ này, bên dưới mục ‘File encryption settings for this workbook’ bạn sẽ điền password đầu tiên vào ô ‘Password to open’. Password này có tác dụng để bảo vệ file của bạn chỉ được mở khi điền đúng password. Nếu bạn muốn sử dụng password để khóa chức năng chỉnh sửa file những vẫn có thể cho người khác xem nội dung file đó, bạn điền password vào hộp thoại ‘Password to modify’ ở bên dưới mục ‘Fire sharing settings to this workbook’. Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 49
26. Giáo trình Excel nâng cao - Trong mục Description mặc định là Macro recoded 01/01/2004 by User tức là gồm có ngày tháng tên người sử dụng, nếu muốn ta có thể thay đổi. - Để bắt đầu ghi ta nhấn OK. - Thực hiện các hành động để Macro ghi lại, các hành động này có thể là sự phối hợp nhiều lệnh trong Excel. - Để dừng ghi từ menu tools chọn Macro và kích stop Recording. 3. Gán một phím tắt cho macro: - Chọn menu tools, chọn Macro và kích Macros. - Hộp thoại macros xuất hiện, chọn tên macro nếu đã lưu trước đó để gán một phím tắt. - Kích vào nút Options, để hiển thị hộp thoại Macro Options. - Shortcut Key: là phím nóng dùng để gán cho macro, bằng cách này người dùng có thể nhanh chóng gọi chạy được macro mà không phải mất nhiều thao tác để gán phím nóng cho macro các phím nóng đều bắt đầu bằng chữ Crtl cộng với một kí tự khác, (vĩ dụ: nhập một kí tự vào sau hộp Ctrl + L để thực hiện macro thì ta chỉ việc nhập chữ Lvào ô trống bên cạnh phím tắt này sẽ được sử dụng để gọi Macro. - Kích Ok để trở về hộp thoại macro. - Kích vào cancel để bỏ qua hộp thoại. Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 51
27. Giáo trình Excel nâng cao - kích nút Record, hộp thoại Record Macro xuất hiện. - Từ mục macro name, nhập tên của Macro cần gọi, kích Ok. - Khi đó Macro được lưu lại một cách thông thường, kích vào nút lệnh, chọn Edit text từ menu pop_up. - Để chạy macro, kích vào nút vừa tạo. 7. Thay đổi tên button: - Kích đúp vào button, khi đó tên button được đánh dấu - Gõ vào một tên mới và kích vào vùng bảng tính. - Xoá button kích vào nút và ấn phím Delete. VBA trong Excel Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 53
28. Giáo trình Excel nâng cao * Vĩ dụ: nếu ta khai báo: Dim A As Integer 'khai báo biến A có kiểu số nguyên thì khi ta viết A=5 máy tính sẽ hiểu đấy là một lệnh gán, nó gán giá trị 5 cho biến A hoặc A=5+3 máy tính sẽ gán kết quả của phép cộng 5+3 cho biến A. Biểu thức "A=5" hoặc "A=5+3" được gọi là biểu thức gán. Trong biểu thức gán vế phải luôn được gán giá trị cho vế trái, soos liệu nguồn để bên phải biểu thức gán, vế bên trái có thể là biến nhớ hoặc là địa chỉ của ô trong Excel. 3.2 Lệnh rẽ nhánh a. Lệnh If: cú pháp If Then [các lệnh được thực hiện nếu điều kiện đúng ] Else [các lệnh được thực hiện nếu điều kiện sai] End If * Vĩ dụ: đoạn chương trình dưới đây cho phép nhập một số bất kỳ từ bàn phím và xác định xem số đó số âm hay số dương. Dim num As double Num = InputBox("Nhập vào một số bất kỳ ") If num>=0 Then MsgBox "số nhập vào là số dương!" Else MsgBox " Bạn đã nhập vào số âm!" End If * Chú ý: cấu trúc lệnh If được phép lồng nhau b. Lệnh Select Case: được phép rẽ nhiều nhánh cấu trúc lệnh : Select Case Case [ các lệnh, nếu biểu thức kiểm tra thỏa mãn giá trị thứ nhất] Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 55
29. Giáo trình Excel nâng cao Sơ đồ khối làm việc của lệnh while Đk sai ĐK stop Đk đúng Các lệnh * Vĩ dụ: sử dụng vòng lặp while tính tổng 20 số tự nhiên đầu tiên. Dim I, N, S As Integer N=20 S = 0 I = 1 While i [các lệnh nếu điều kiện đúng] [Exit Do] Loop Lệnh Do While có bổ sung khả năng dừng vòng lặp ở bất kỳ thời điểm nào mà người lệnh Exit Do. Trung tâm CNTT & TT Cao Bằng – CBITC - ĐT: 0263.955899 57