Tài liệu exel - Dùng cho sinh viên học tin học cơ sở ở các trường đại học cao đẳng

h công cụ. II.2. Sắp xếp dữ liệu theo nội dung của hai hoặc nhiều cột Khoa CNTT Giáo trình Excel 88 1. Kích một ô trong danh sách muốn sắp xếp. 2. Thực hiện Data / Sort, xuất hiện hộp hội thoại Sort. 3. Trong các hộp Sort by và Then by kích chọn tên các cột muốn sắp xếp. 4. Chọn các tuỳ chọn khác nếu cần thiết và kích OK. Trong My list has có hai tuỳ chọn: Header row - trong vùng dữ liệu vừa chọn có dòng tiêu đề, No header row - trong vùng dữ liệu vừa chọn không có dòng tiêu đề. Thông thường, nên để trạng thái chọn ngầm định của máy. Lặp lại các bước 2- 4 nếu cần thiết. Ghi chú: • - Nếu cột ta chỉ định trong hộp Sort by các giá trị trùng nhau, ta có thể sắp xếp các giá trị này bằng cách chỉ định cột khác trong hộp Then by đầu tiên. Nếu các giá trị trong cột thứ hai được sắp xếp này trùng nhau, ta có thể chỉ định cột thứ ba trong hộp Then by thứ hai. • - Nếu ta cần sắp xếp nhiều hơn ba cột, hãy sắp xếp những cột ít quan trọng nhất trước. Ví dụ như, nếu chúng ta muốn sắp xếp danh sách cán bộ theo các cột Lớp, Chức vụ, Họ đệm và Tên thì phải sắp xếp hai lần. Đầu tiên, chọn cột Tên trong hộp Sort by và kích nút OK. Lần thứ hai, kích Lớp trong hộp Sort by, kích Chức vụ trong hộp Then by thứ nhất, chọn Descending và kích Họ đệm trong hộp Then by thứ hai và kích OK. Kết quả có dạng như sau: Họ đệm Tên Chức vụ Lớp Nguyễn Hoàng Hà Lớp trưởng 10A Trần Hồng Hà Lớp phó 10A Vũ Thanh Thủy Lớp phó 10A Phạm Hoàng Hải Lớp trưởng 10B Phạm Tuấn Hùng Lớp phó 10B Vũ Văn Thanh Lớp phó 10B Vũ Hồng Thắng Lớp trưởng 10C Trần Thanh Hà Lớp phó 10C Vũ Hồng Thủy Lớp phó 10C Khoa CNTT Giáo trình Excel 89 Phạm Thanh Tùng Lớp trưởng 10D Đặng Mạnh Hùng Lớp phó 10D Hoàng Minh Thanh Lớp phó 10D II.3. Sắp xếp xếp dữ liệu theo nội dung của hàng 1. Kích một ô trên danh sách dữ liệu muốn sắp xếp. 2. Thực hiện Data / Sort. 3. Kích nút Options, xuất hiện hộp hội thoại Sort Options. 4. Dưới Orientation, kích Sort left to right, và sau đó kích OK. 5. Trong các hộp Sort by và Then by kích các hàng muốn sắp xếp. II.4. Sắp xếp theo tháng, ngày hoặc theo thứ tự do người dùng tạo II.4.1. Tạo danh sách theo thứ tự do người dùng tạo (custom list) Nếu muốn sắp xếp dữ liệu theo một thứ tự khác thứ tự ngầm định của máy, chúng ta phải tự tạo một danh sách theo thứ tự do mình đặt ra. Ví dụ như, nếu sắp xếp cột Xếp loại trong một bảng điểm tổng kết theo thứ tự tăng dần ngầm định của Excel, thì kết quả sẽ đươc sắp theo thứ tự sau: Gioi, Kem, Kha, TB. Tuy nhiên, để hợp lí hơn, chúng ta phải tự tạo danh sách theo thứ tự: Gioi, Kha, TB, Kem và sắp xếp bảng điểm theo thứ tự đó. Hãy làm như sau: 1. Nếu ta đã nhập vào bảng tính danh sách các phần tử theo thứ tự riêng của mình thì hãy chọn danh sách đó. Ví dụ, hãy gõ vào các ô A1:A4 các giá trị sau Gioi, Kha, TB, Kem và chọn bốn ô này. 2. Thực hiện Tools / Options, kích mục Custom Lists. Khoa CNTT Giáo trình Excel 90 3. Để sử dụng danh sách đã chọn, kích nút Import. Nếu chưa thực hiện bước 1, hãy gõ danh sách mới bằng cách kích New list trên hộp Custom lists và sau đó gõ các phần tử của danh sách trong hộp List entries. Các phần tử cách nhau bởi dấu phảy hoặc dấu ENTER. Kích nút Add. Ghi chú: • Một danh sách tự tạo có thể chứa chuỗi kí tự hoặc chuỗi kí tự xen lẫn các con số. Nếu muốn tạo danh sách chỉ chứa các con số, đầu tiên chọn đủ số ô trống chứa các phần tử của danh sách. Thực hiện Format / Cells, và kích mục Number. Áp dụng kiểu định dạng Text cho các ô trống và sau đó gõ danh sách các số trong các ô vừa định dạng. II.4.2. Sắp xếp theo tháng, ngày hoặc theo thứ tự của danh sách tự tạo 1. Chọn một ô trong vùng hoặc toàn bộ vùng dữ liệu muốn sắp xếp. 2. Thực hiện Data / Sort. 3. Kích Options. 4. Dưới First key sort order, kích danh sách tự tạo và kích OK. 5. Kích chọn bất kì lựa chọn khác nếu cần thiết. Ghi chú: Khoa CNTT Giáo trình Excel 91 • Trật tự sắp xếp theo thứ tự của danh sách tự tạo chỉ áp dụng cho cột được chỉ định trong hộp Sort by. Để sắp xếp nhiều cột theo thứ tự của danh sách tự tạo hãy sắp xếp từng cột riêng biệt. Ví dụ, để sắp xếp theo các cột A và B, theo thứ tự này, đầu tiên sắp xếp theo cột B, và sau đó chỉ định thứ tự của danh sách tự tạo bằng cách sử dụng hộp hội thoại Sort Options. Tiếp theo, sắp xếp theo danh sách của cột A. Khoa CNTT Giáo trình Excel 92 Câu hỏi và bài tập 1. Nêu các thao tác sắp xếp dữ liệu theo một cột, theo nhiều cột và theo thứ tự của danh sách tự tạo. 2. Mở file QLHS.XLS, kích hoạt sheet DSCB và thực hiện những thao tác đã trình bày trong phần lí thuyết. 3. Kích hoạt sheet TUYENSINH và làm các công việc sau: • a. Sắp xếp lại theo thứ tự tăng dần của cột Trúng tuyển. • b. Sao chép danh sách những người trúng tuyển sang vị trí mới và sắp xếp lại theo thứ tự giảm dần của Tổng điểm. • c. Đặt tên vùng chứa kết quả trúng tuyển là Trungtuyen1. • d. Chuyển về vùng DSTS. • e. Chuyển sang xem kết quả tại vùng Trungtuyen1. 4. Ghi các thay đổi vào đĩa và đóng file. 5. Kích hoạt một sheet mới, tạo một bảng mới có dạng như sau Báo cáo Tuyển sinh Sbd Họ và tên Ngày sinh Nơi sinh Miền Tổng điểm Trúng tuyển ... Làm tiếp các việc sau: a. Nhập vào danh sách tối thiểu 10 thí sinh. Yêu cầu nhập các cột sau: Sbd (số báo danh), Họ và tên, Ngày sinh, Nơi sinh, Miền (Bắc, Trung hoặc Nam) và Tổng điểm (từ 0 đến 30). Khoa CNTT Giáo trình Excel 93 b. Cột Trúng tuyển được điền theo yêu cầu sau: - Có nếu thí sinh ở miền Bắc và miền Nam và Tổng điểm lớn hơn hoặc bằng 21 điểm hoặc nếu thí sinh ở miền Trung và có Tổng điểm lớn hơn hoặc bằng 20 điểm. - Không cho các trường hợp còn lại. Gợi ý: dùng công thức sau =IF (OR (AND (OR (Miền="Bắc", E2="Nam"), Tổng điểm>=21), AND (Miền="Trung", Tổng điểm>=20)), "Có", "Không") c. Sắp xếp lại danh sách theo thứ tự các miền Bắc, Trung, Nam. Nếu giá trị các miền trùng nhau thì sắp xếp theo giá trị cột Trúng tuyển. Nếu cùng giá trị cột Trúng tuyển, hãy sắp xếp theo giá trị của cột Tổng điểm. e. Đổi tên sheet hiện thời thành BCTTUYEN và ghi kết quả vào đĩa. Khoa CNTT Giáo trình Excel 94 Bài 9: Tạo biểu đồ I. Các phần tử cơ bản của biểu đồ Khi muốn so sánh các dữ liệu có mối quan hệ với nhau chúng ta nên dùng biểu diễn mối tương quan này qua các biểu đồ để có cái nhìn trực quan và sinh động hơn. Để vẽ biểu đồ bắt buộc phải có danh sách dữ liệu trên bảng tính. Sau đây là một số phần tử cơ bản của biểu đồ: - Chuỗi dữ liệu (data series): là các hàng hoặc các cột chứa các dữ liệu cần so sánh với nhau. - Kiểu đồ thị (chart type): các hình thức biểu diễn đồ thị khác nhau theo dạng cột (Column), đường (line) hoặc diện tích (area), ... - Nhãn chung của biểu đồ (Chart Labels): là tên chung của cả biểu đồ. - Nhãn trục (axis labels): các nhãn trên trục biểu đồ. - Chú giải (Legend): dùng để phân biệt giữa các chuỗi dữ liệu khác nhau. Ngoài ra còn một số phần tử khác nữa sẽ được trình bày tiếp trong các phần sau. II. Tạo biểu đồ ngầm định theo một bước Kiểu biểu đồ ngầm định của Microsoft Microsoft Excel là biểu đồ cột. Có hai cách tạo biểu đồ ngầm định. Cách 1: Chọn dữ liệu muốn biểu diễn thành đồ thị. Nhấn F11. Dữ liệu sẽ được biểu diễn dưới dạng biểu đồ cột và nằm trong một sheet riêng biệt có tên ngầm định là Chart n (n=1, 2, 3, ...). Ví dụ: Giả sử ta có danh sách dữ liệu biểu diễn tỉ lệ học sinh giỏi, khá, trung bình, kém như sau: Lớp Xếp loại 10A 10B 10C 10D Khoa CNTT Giáo trình Excel 95 Giỏi 30% 20% 24% 19% Khá 50% 55% 60% 58% TB 20% 25% 14% 21% Kém 0% 0% 2% 2% Để so sánh tỉ lệ xếp loại hoặc sinh giữa các lớp, chúng ta chọn vùng dữ liệu trên và nhấn phím F11. Kết quả có dạng như sau: Cách 2: • Chọn dữ liệu muốn biểu diễn thành đồ thị. • Kích nút Default Chart . Đồ thị tạo ra được nhúng vào bảng tính chứa dữ liệu. Trong trường hợp không nhìn thấy nút này, hãy làm như sau: 1. Thực hiện Tools / Customize. Xuất hiện hộp hội thoại, kích chuột vào mục Commands. 2. Trong hộp Categories kích chọn Charting. 3. Dùng chuột kéo lệnh Default Chart thả vào một vị trí bất kì trên thanh công cụ chuẩn (Standard toolbar). Khoa CNTT Giáo trình Excel 96 Ví dụ: sau khi đã cài được nút Default Chart trên thanh công cụ chuẩn, chọn vùng dữ liệu nói trên và kích vào nút này, kết quả có dạng: III. Cách tạo đồ thị nhờ Chart Wizarrd Chart Wizard là công cụ hướng dẫn chúng ta tạo đồ thị từng bước qua các hộp hội thoại. Các bước tạo đồ thị được tiến hành như sau: 1. Chọn các ô chứa các dữ liệu muốn xuất hiện trên biểu đồ. Các dữ liệu này có thể nằm cạnh nhau hoặc nằm cách nhau. Nếu muốn các nhãn cột và nhãn hàng xuất hiện trong biểu đồ hãy chọn cả chúng. Ví dụ, để so sánh tỉ lệ xếp loại hoặc sinh của các lớp 10A, và 10D, chúng ta hãy chọn các cột thứ nhất, thứ hai và thứ năm trong bảng dữ liệu nói trên bằng cách giữ phím CRTL và chọn các cột nói trên. Xem hình vẽ sau: Khoa CNTT Giáo trình Excel 97 2. Kích nút Chart Wizard . Xuất hiện hộp hội thoại Chart Wizard - Step 1 of 4 - Chart Types. Hộp hội thoại này cho phép chúng ta chọn kiểu đồ thị (Chart type) và một kiểu đồ thị con (Chart sub -type) của nó. Muốn xem trước mẫu đồ thị sẽ được tạo, hãy kích vào nút Press and hold to view sample. Trong ví dụ này, chúng ta sẽ chọn kiểu Line và một kiểu biểu đồ con của nó như hình vẽ sau: 3. Kích vào nút Next, xuất hiện tiếp hộp hội thoại Chart Wizard - Step 2 of 4 - Chart Source Data sau: Khoa CNTT Giáo trình Excel 98 Trong hộp hội thoại này cho phép chọn lại vùng dữ liệu trong hộp Data range (nếu cần thiết). Nếu chọn Rows, chuỗi dữ liệu sẽ được so sánh theo hàng. Nếu chọn Columns, chuỗi dữ liệu sẽ được so sánh theo cột. Trong ví dụ của chúng ta, hãy giữ nguyên các thông số như hộp hội thoại. 4. Kích vào nút Next, xuất hiện tiếp hộp hội thoại Chart Wizard - Step 3 of 4 - Chart Options. Trong hộp hội thoại này cho phép chọn các tuỳ chọn khác nhau cho biểu đồ. Trong bài này, chúng ta chỉ quan tâm tới các thông số trong mục Titles. - Chart title: cho phép gõ nhãn chung của biểu đồ. - Categery (X) axis: cho phép gõ nhãn của trục x. - Value (Y) axis: cho phép gõ nhãn của trục y. Xem kết quả ở hình mẫu bên phải của đồ thị. Trong ví dụ của chúng ta, hãy gõ theo hướng dẫn trên hộp hội thoại (có thể gõ tiếng Việt có dấu trong hộp này, nhưng chúng ta sẽ không nhìn thấy dấu tiếng Việt trong hộp hội thoại. Tiếng Việt chỉ hiển thị đúng ở ngoài đồ thị sau khi chúng ta định dạng lại font tiếng Việt cho chúng). 5. Kích vào nút Next, xuất hiện tiếp hộp hội thoại Chart Wizard - Step 4 of 4 - Chart Location. Trong hộp hội thoại này cho phép chọn vị trí đặt biểu đồ. Khoa CNTT Giáo trình Excel 99 - As new sheet: đặt biểu đồ ở sheet riêng với tên ngầm định do Excel đặt (Chart1, Chart2, ...) hoặc chúng ta tự gõ lại tên mới trong hộp này. - As object in: đặt biểu đồ trong sheet hiện thời, hoặc một sheet khác do chúng ta chọn bằng cách kích chuột vào mũi tên bên phải hộp này và chọn tên sheet đích. Trong ví dụ của chúng ta, giữ nguyên các thông số trong hộp hội thoại. 6. Kích nút Finish. Kết quả có dạng như sau: Khoa CNTT Giáo trình Excel 100 Câu hỏi và bài tập 1. Nêu các phần tử cơ bản của một biểu đồ. 2. Thực tập lại ví dụ đã nêu trong phần lí tuyết. 3. Lập bảng tổng kết số học sinh nữ và nam trong từng khổi 10, 11, 12. Tính tỉ lệ số học sinh nữ và nam của từng khối so với tổng số học sinh trong toàn trường. Vẽ biểu đồ biểu diễn sự so sánh này. 4. Lập bảng tổng kết số học sinh giỏi và khá từng khổi 10, 11, 12. Tính tỉ lệ học sinh giỏi, khá của từng khối so với số học sinh giỏi, khá trong toàn trường. Vẽ biểu đồ biểu diễn sự so sánh này. Khoa CNTT Giáo trình Excel 101 Bài 10: Định dạng trang và In ấn I. Định dạng trang I.1. Định dạng trang Công việc chuẩn bị in ấn bảng tính bao gồm các phần việc được thực hiện từ lệnh File/Page Setup. Trong hộp hội thoại Page Setup ta có thể thực hiện các công việc sau: Vào các thông số in bao gồm 4 loại thông số: 1. Page: Các thông số trang giấy in. 2. Margins: Các thông số lề trang in. 3. Header/Footer: Tiêu đề trang in (nếu cần thiết). 4. Sheet: Các thông số về dữ liệu cần in trên bảng tính. Nút Print Preview dùng để kiểm tra trước khi in chính thức ra giấy in. Nút Print dùng để ra lệnh in. Nút Option dùng để đặt lại các thông số máy in. Khoa CNTT Giáo trình Excel 102 Nút OK đóng cửa sổ Page Setup. Các thông số in sẽ được mô tả cụ thể dưới đây. I.1.1. Page • Orientation: hướng in. • Scaling: tỷ lệ in. Có hai lựa chọn sau: o Adjust to: tỷ lệ in so với dạng chuẩn. o Fit to: dãn/nén vùng in trong số trang được chỉ ra bởi các thông số về chiều rộng (wide) và chiều dài (tall). • Paper Size: kích thước giấy in. • Print Quality: chất lượng in. • First Page Number: số trang bắt đầu cần đánh số. I.1.2. Margins • Các thông số lề Top, Bottom, Left, Right: lề trên, dưới, trái, phải. • From Edge/Header, Footer: khoảng cách từ mép trang giấy đến tiêu đề trang. • Center on Page: in vào giữa trang theo chiều: Horizontally (ngang), Vertically (dọc). I.1.3. Header/Footer Các thông số về tiêu đề được đưa vào tự động hoặc bằng tay khi nhấn các nút Customize Header hoặc Customize Footer. Khoa CNTT Giáo trình Excel 103 • Left section, Center section, Right section: vị trí trình bày tiêu đề trong trang. • Các nút phía trên dùng để: trình bày font chữ, chèn số trang, tổng số trang, ngày giờ hệ thống. I.1.4. Sheet • Print Area: chọn vùng dữ liệu cần in. • Print Titles: tiêu đề hàng, cột của dữ liệu, trong đó: o Row to repeat at top: tiêu đề hàng. Các hàng được in ra ở mọi trang. o Column to repeat at left: tiêu đề cột. Các cột được in ra ở mọi trang. • Print: lựa chọn kiểu in o Row and Column Heading: in tiêu đề hàng, cột của bảng tính. o Gridlines: in ra lưới của các ô bảng tính. o Comment: in chú thích. o Draft Quality: in nhanh (bỏ qua format). Khoa CNTT Giáo trình Excel 104 o Black and white: in đen trắng. • Page Order: thứ tự in. o Down, then over: in từ trên xuống dưới trước, sau đó mới sang ngang. o Over, then down: in sang ngang trước, sau đó mới xuống dưới. II. In Công việc in trong EXCEL được thực hiện bằng lệnh File/Print. Hộp hội thoại Print xuất hiện. • Printer: tên của máy in, kiểu máy in, ... • Nút Properties: chọn các thông số của máy in như khổ giấy, hướng giấy, chế độ phân giải màu, chất lượng in, ... • Page Range: lựa chọn vùng in. o All: in toàn bộ các trang. o Pages From...To: in hạn chế các trang từ...đến trang ... • Print What: o Selection: chỉ in vùng được chọn. o Selected Sheet: in các sheet đang làm việc. o Entire Workbook: in toàn bộ Workbook. • Copies: số lượng bản in. • Print to file: được chọn nếu muốn văn bản ra file. • Nút Preview: dùng để xem tổng quát bảng tính trước khi in chính thức ra giấy in. Khoa CNTT Giáo trình Excel 105 Sau khi đã chọn xong các thông số in kích chuột tại nút OK hoặc nhấn Enter để bắt đầu công việc in. Khoa CNTT Giáo trình Excel 106 Câu hỏi và bài tập 1. Thực hành lại tất cả các thao tác chuẩn bị in ấn trong bài và xem kết quả ở chế độ Print Preview. 2. Mở file BD10A.xls, mở sheets cài đặt cỡ giấy in là A4, font chữ chính trong bảng là Times New Roman và cỡ chữ nhỏ nhất là 12. Xem kết quả ở chế độ Print Preview. Nếu dữ liệu nằm trên hai trang giấy, hãy đặt lại chế độ in sao cho chúng nằm khít trên một trang in. 3. Làm tương tự với file BD10B.xls. 4. Hãy đặt chế độ in sao cho sau khi in ra, các bảng điểm của lớp 10A và 10B đều có tên sheet và ngày tháng in nằm ở góc trái phần tiêu đề trên, dòng chữ "Trường PTTH ABC" nằm ở góc phải tiêu đề trên và số trang nằm ở giữa phần tiêu đề dưới theo mẫu sau: Khoa CNTT Giáo trình Excel 107 Ôn tập I. Một số thao tác cơ bản với file dữ liệu Khởi động Excel : Start /Programs /Microft Excel. Thoát khỏi Excel: File / Exit hoặc Alt + F4. Mở file mới: File /New Ghi file vào đĩa: File /Save Ghi file hiện thời vào đĩa với tên khác: File / Save As. Gọi file cũ: File /Open. II. Nhập, sửa, xoá dữ liệu II.1. Nhập hằng: • Chọn ô trống. • Gõ dữ liệu • Nhấn Enter. II.2. Nhập công thức: • Chọn ô trống • Gõ dấu bằng (=) • Gõ biểu thức • Nhấn Enter. II.3. Sửa toàn bộ dữ liệu • Chọn ô cần sửa • Gõ dữ liệu mới • Nhấn Enter. Khoa CNTT Giáo trình Excel 108 II.4. Sửa một phần dữ liệu: • Chọn ô cần sửa • Nhấn F2 hoặc kích chuột lên dòng nhập dữ liệu • Chuyển con trỏ text tới vị trí cần sửa, xoá bớt hoặc chèn thêm dữ liệu. • Nhấn Enter. II.5. Xoá dữ liệu • Chọn ô cần xoá dữ liệu • Nhấn phim Delete. III. Các thao tác với hàng, cột, ô III.1. Chọn hàng, cột ô • Kích chuột vào tiêu đề hàng nếu chọn cả hàng, vào tiêu đề cột nếu chọn cả cột, vào ô nếu chọn ô. • Chọn các hàng, cột, ô liền nhau: chọn phần tử đầu khối. Giữ Shift và kích chuột vào phần tử đối diện ở cuối khối. • Chọn các hàng, cột, ô rời rạc: chọn một phần tử. Giữ Ctrl và kích chọn các phần tử còn lại. III.2. Chèn thêm hàng, cột, ô • Chọn số hàng, cột, ô cần chèn. • Kích nút phải chuột, chọn Insert hoặc dùng Insert => Rows (hoặc Columns hoặc Cells). III.3. Huỷ thêm hàng, cột, ô • Chọn số hàng, cột, ô cần huỷ. • Kích nút phải chuột, chọn Delete hoặc dùng Edit => Delete. Khoa CNTT Giáo trình Excel 109 III.4. Thay đổi độ rộng cột • Chọn các cột cần điều chỉnh độ rộng • Đặt trỏ chuột ở mép phải vùng tiêu đề và rê chuột hoặc dùng thực đơn Format / Column / Width. III.5. Thay đổi độ cao hàng • Chọn các hàng cần điều chỉnh độ cao • Đặt trỏ chuột ở mép dưới vùng tiêu đề và rê chuột hoặc dùng thực đơn Format / Row / Hight. IV. Các thao tác với một bảng tính IV.1. Kích hoạt bảng tính • Kích chuột vào tên sheet trên danh sách các sheet. • Dùng các nút chuyển , , , • Kích nút phải tại vùng các nút chuyển và kích vào tên sheet cần thiết. IV.2. Các thao tác với sheet • Chọn sheet • Kích nút phải chuột vào tên sheet và chọn các lệnh sau: Rename: đổi tên bảng tính Insert: chèn thêm bảng tính Delete: xoá bảng tính Move or copy: di chuyển hoặc sao chép bảng tính. Khoa CNTT Giáo trình Excel 110 V. Một số hàm thông dụng V.1. Các hàm thống kê Average(number1, number2, ...): tính giá trị trung bình của các số. Max(number1, number2, ...): tính giá trị lớn nhất của các số. Min(number1, number2, ...): tính giá trị nhỏ nhất của các số. Sum(number1, number2, ...): tính tổng các số. Count(number1, number2, ...): đếm các ô chứa số. Rank(number, ref, order): trả lại số thứ tự trong danh sách số. V.2. Các hàm đổi kiểu Text(value, format_text): đổi các giá trị số thành text. Value(Text): chuyển các text đại diện cho giá trị số thành số. V.3. Các hàm kí tự Left(text,num_chars): trả lại một số kí tự được chỉ định từ bên trái chuỗi kí tự. Right(text,num_chars): trả lại một số kí tự được chỉ định từ bên phải chuỗi kí tự. Mid(text,start_num,num_chars): trả lại một số kí tự từ một vị trí xác định. Upper(text): chuyển text thành chữ hoa. Lower(text): chuyển text thành chữ thường. Proper(text): chuyển chữ cái đầu từ và các kí tự ngay sau kí tự đặc biệt thành thành chữ hoa. Trim(text): xoá bỏ tất cả các kí tự trống thừa. V.4. Các hàm toán học SQRT(number): hàm căn bậc 2. ABS(number): lấy giá trị tuyệt đối. Round(number,num_digit): làm tròn số. V.5. Các hàm logic If(logical_test,value_if_true, value_if_ false): hàm trả lại giá trị đúng nếu biểu thức logic nhận giá trị TRUE và ngược lại. Khoa CNTT Giáo trình Excel 111 And(logical1, logical2, ...): hàm nhận giá trị TRUE nếu tất cả các đối số nhận giá trị TRUE. OR(logical1,logical2, ...): hàm nhận giá trị FALSE nếu tất cả các đối số nhận giá trị FALSE. V.6. Cách sử dụng hộp Function Insert /Function. VI. Sao chép và di chuyển dữ liệu VI.1. Sao chép dữ liệu • Dùng nút trái chuột: chọn dữ liệu gốc. Giữ Ctrl và rê chuột tới đích. • Dùng nút phải chuột: chọn dữ liệu gốc. Dùng nút phải chuột rê tới đích và chọn một trong các lựa chọn sau trong thực đơn ngắn: Copy Here - dán kết quả sao chép tại đây, Copy as Value Only- chỉ sao chép giá trị của dữ liệu gốc, Copy as Format Only- chỉ sao chép định dạng của dữ liệu gốc, Link Here - liên kết với các ô chứa dữ liệu gốc, Shift Down and Copy - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng xuống dưới, Shift Right and Copy - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng sang phải, Cancel - huỷ việc sao chép. • Dùng bộ đệm + Chọn dữ liệu gốc. + Edit / Copy + Chọn một ô vùng đích + Edit / Paste hoặc Edit / Paste Special. VI.2. Di chuyểndữ liệu • Dùng nút trái chuột: chọn dữ liệu gốc. Rê chuột tới đích. • Dùng nút phải chuột: chọn dữ liệu gốc. Dùng nút phải chuột rê tới đích và chọn một trong các lựa chọn sau trong thực đơn ngắn: Move Here - dán kết quả di Khoa CNTT Giáo trình Excel 112 chuyển tại đây, Shift Down and Move - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng xuống dưới, Shift Right and Move - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng sang phải, Cancel - huỷ việc di chuyển. • Dùng bộ đệm + Chọn dữ liệu gốc. + Edit / Cut + Chọn một ô vùng đích + Edit / Paste. VII. Cơ sở dữ liệu Cơ sở dữ liệu : là một vùng hình chữ nhật gồm các hàng và các cột. Mỗi cột tương ứng với một trường. Hàng nhãn cột là tên các trường. Từ hàng thứ hai trở đi tương ứng với một bản ghi. VIII. Sắp xếp dữ liệu - Kích một ô trong vùng dữ liệu - Thực hiện Data / Sort. IX. Biểu đồ - Chọn vùng dữ liệu - Nhấn F11 hoặc kích nút Chart Default hoặc làm theo hướng dẫn của Chart Wizard. X. Định dạng cho dữ liệu và in ấn Định dạng cho dữ liệu: Format / Cells. Cài đặt trang giấy in: Format / Page Setup. Xem trước khi in: File / Print Preview. Khoa CNTT Giáo trình Excel 113 In: File / Print. Khoa CNTT Giáo trình Excel 114 Câu hỏi và bài tập 1. Hãy phân biệt sự khác nhau giữ việc xoá một file, xoá một bảng tính và xoá dữ liệu. 2. Hãy phân biệt sự khác nhau giữa việc đặt tên file và đặt tên cho bảng tính. 3. Mở file BD10A.xls, sao chép bảng tính HKI-10A và đổi tên bản sao thành HKII- 10A. Hãy tự vào điểm học kì II cho các bạn lớp 10A, trong đó số điểm hệ số 1 của các bạn không giống nhau. Chẳng hạn như, bạn thứ nhất chỉ có một điểm hệ số 1, bạn thứ 4 lại có 3 điểm hệ số 1, ... Sau đó tính cột điểm TB cho các bạn chỉ bằng một công thức và hoàn thành mốt các cột còn lại. Trình bày bảng điểm cho đẹp và in ra giấy hoặc xem ở chế độ Print Preview. 4. Mở file mới, ghi vào đĩa với tên là QLSACH.XLS 5. Tạo bảng dữ liệu theo mẫu sau: Stt Ngày Tên sách Nhà xb Loại Đơn giá Số lượng Thành tiền 1 2/2/99 Doremon –1 Kim đồng Truyện Tranh 6500 5 2 2/2/99 Tập tô lớp 3,t1 Giáo dục Sgk 1200 10 3 2/15/99 Tiếng Việt lớp 3,t1 Giáo dục Sgk 3500 15 4 2/15/99 Doremon –1 Kim đồng Truyện Tranh 6500 25 5 2/15/99 Tập tô lớp 3,t1 Giáo dục Sgk 1200 20 6 2/15/99 Tiếng Việt lớp 4,t1 Giáo dục Sgk 3700 9 7 3/7/99 Doremon –2 Kim đồng Truyện Tranh 6500 30 8 3/20/99 Tiếng Việt lớp 3,t1 Giáo dục Sgk 3500 25 9 3/20/99 Tập tô lớp 3,t1 Giáo dục Sgk 1200 8 10 3/20/99 Tiếng Việt lớp 5,t1 Giáo dục Sgk 4000 20 Yêu cầu: - Nhập dữ liệu cho các cột Stt, Ngày, Tên sách, Nhà Xuất bản (Giáo dục, Kim đồng, ...), Loại (SGK, Truyên Tranh, Tiểu thuyết,...), Đơn giá và Số lượng. Nhập ít nhất 10 bản ghi theo mẫu. - Cột Thành tiền được tính theo công thức sau: Khoa CNTT Giáo trình Excel 115 Thành tiền = đơn giá * số lượng nếu số lượng nhỏ hơn 10. Thành tiền = đơn giá * số lượng *(1 - 5%) nếu số lượng từ 10 đến 20. Thành tiền = đơn giá * sốlượng*(1-10%) nếu số lượnglớn hơn hoặc bằng 20. 6. Đặt tên cho sheet hiện thời là BANHANG 7. Sao chép sang sheet mới, sắp xếp theo giá trị tăng dần của tên nhà xuất bản. Nếu giá trị của nhà xuất bản trùng nhau thì sắp xếp theo giá trị giảm dần của cột Thành tiền. Đặt tên vùng dữ liệu vừa sao chép sang là Bansao1. Đặt tên sheet hiện thời là TONGHOP. 8. Dùng chức năng Sutotal để tính tổng thành tiền của từng nhà xuất bản và tổng chung. Sao chép kết quả sang vùng khác của Sheet TONGHOP và đặt tên cho vùng kết quả là TH.NHAXB. Kết quả có dạng như sau: Nhà xb Thành tiền Giáo dục Total 276525 Kim đồng Total 354250 Grand Total 630775 Khoa CNTT Giáo trình Excel 116 MỤC LỤC Bài 1: Làm quen với Excel.....................................................................................1 I. Tổng quan về Excel.............................................................................................1 I.1. Giới thiệu Excel .............................................................................................1 I.2. Đặc điểm của Excel 2000 ..............................................................................1 II. Khởi động và thoát khỏi Excel.........................................................................2 II.1. Khởi động Excel: ..........................................................................................2 II.2. Thoát khỏi Excel:..........................................................................................2 III. Màn hình làm việc của Excel ..........................................................................3 III.1. Màn hình Excel............................................................................................3 III.2. Định dạng màn hình Excel ..........................................................................4 IV. Giới thiệu bảng tính của Excel .......................................................................5 IV.1. Workbook ....................................................................................................5 IV.2. Worksheet....................................................................................................5 Câu hỏi và bài tập...................................................................................................7 Bài 2: Nhập dữ liệu vào bảng tính và các thao tác với tệp ................................8 I. Các kiểu dữ liệu trong Excel..............................................................................8 I.1. Kiểu hằng: .....................................................................................................8 I.1.1. Text: ........................................................................................................8 I.1.2. Number ...................................................................................................9 I.2. Kiểu công thức...............................................................................................9 I.3. Kiểu logic và các lỗi ......................................................................................9 II. Cách nhập chữ tiếng Việt ...............................................................................10 Khoa CNTT Giáo trình Excel 117 II.1. Kiểu gõ VNI:...............................................................................................10 II.2. Kiểu gõ TELEX: .........................................................................................10 III. Nhập và chỉnh sửa dữ liệu ............................................................................10 III.1. Quy tắc chung............................................................................................10 III.2. Một số thủ thuật nhập dữ liệu nhanh ........................................................11 III.3. Gõ dữ liệu kiểu text....................................................................................13 III.4. Gõ dữ liệu kiểu số......................................................................................14 III.5. Gõ dữ liệu kiểu ngày tháng .......................................................................14 IV. Các phương tiện sửa và xoá ..........................................................................15 IV.1. Sửa dữ liệu ................................................................................................15 IV.2. Thay toàn bộ dữ liệu cũ bằng dữ liệu hoàn toàn mới ...............................15 IV.3. Sửa một phần nội dung trong ô.................................................................16 IV.4. Xoá dữ liệu ................................................................................................16 V. Cách di chuyển con trỏ ở trong bảng tính ....................................................16 V.1. Di chuyển con trỏ ô bằng chuột .................................................................17 V.2. Di chuyển con trỏ ô bằng bàn phím...........................................................17 V.3. Dùng hộp hội thoại GoTo ..........................................................................18 VI. Các thao tác với file dữ liệu...........................................................................18 VI.1. Ghi file hiện thời vào đĩa ..........................................................................18 VI.2. Ghi file hiện thời vào đĩa với tên khác......................................................20 VI.3. Gọi một file có sẵn từ đĩa ra màn hình .....................................................21 VI.4. Tạo một file mới ........................................................................................22 VI.5. Đóng file hiện thời ....................................................................................23 Câu hỏi và bài tập.................................................................................................24 Khoa CNTT Giáo trình Excel 118 Bài 3: Các thao tác với hàng, cột, ô trong một bảng tính ................................26 I. Định dạng cột, dòng, ô......................................................................................26 I.1. Thay đổi độ rộng cột....................................................................................26 I.1.1. Dùng chuột............................................................................................26 I.1.2. Dùng thực đơn ......................................................................................26 I.2. Thay đổi độ cao hàng ..................................................................................26 I.2.1. Dùng chuột............................................................................................26 I.2.2. Dùng thực đơn ......................................................................................27 I.3. Nối và tách ô:...............................................................................................27 II. Cách chọn hàng, cột và ô ................................................................................28 II.1. Chọn hàng...................................................................................................28 II.2. Chọn cột......................................................................................................28 II.3. Chọn ô.........................................................................................................28 III. Cách chèn hàng, cột và ô...............................................................................28 III.1. Chèn hàng..................................................................................................28 III.2. Chèn cột.....................................................................................................29 III.3. Chèn ô........................................................................................................29 IV. Huỷ hàng, cột và ô..........................................................................................30 IV.1. Huỷ hàng ...................................................................................................30 IV.2. Huỷ cột ......................................................................................................30 IV.3. Huỷ ô .........................................................................................................31 V. Các thao tác với các bảng tính trong một Workbook..................................32 V.1. Kích hoạt một bảng tính.............................................................................32 V.2. Đổi tên bảng tính........................................................................................32 V.3. Chèn thêm bảng tính mới ...........................................................................33 Khoa CNTT Giáo trình Excel 119 V.4. Xoá bảng tính .............................................................................................33 V.5. Di chuyển hoặc sao chép một bảng tính ....................................................34 Câu hỏi và bài tập.................................................................................................35 Bài 4: Sử dụng công thức và các hàm tính toán cơ bản...................................37 I. Sử dụng Công thức trong Excel ......................................................................37 I.1. Các toán tử ..................................................................................................37 I.1.1. Toán tử số: ............................................................................................37 I.1.2. Toán tử chuỗi ........................................................................................37 I.1.3. Toán tử so sánh .....................................................................................37 I.2. Nhập công thức vào bảng tính.....................................................................38 I.2.1. Quy tắc chung .......................................................................................38 I.2.2. Công thức đơn giản...............................................................................38 I.3. Địa chỉ tương đối và tuyệt đối .....................................................................40 I.3.1. Địa chỉ tương đối: .................................................................................40 I.3.2. Địa chỉ tuyệt đối:...................................................................................40 I.4. Liên kết dữ liệu ............................................................................................41 I.4.1. Liên kết dữ liệu giữa các bảng tính.......................................................41 I.4.2. Liên kết dữ liệu giữa các tập tin Excel: ................................................41 II. Khái niệm hàm trong Excel: ..........................................................................41 II.1. Nhóm hàm số ..............................................................................................42 II.1.1. Hàm SQRT ..........................................................................................42 II.1.2. Hàm ABS.............................................................................................42 II.1.3. Hàm ROUND ......................................................................................43 II.2. Nhóm hàm thống kê ....................................................................................43 II.2.1. Hàm Average.......................................................................................43 II.2.2. Hàm Max .............................................................................................44 II.2.3. Hàm MIN.............................................................................................44 Khoa CNTT Giáo trình Excel 120 II.2.4. Hàm SUM............................................................................................45 II.2.5. Hàm COUNT.......................................................................................46 II.2.6. Hàm RANK .........................................................................................47 II.3. Nhóm hàm đổi kiểu.....................................................................................48 II.3.1. Hàm TEXT ..........................................................................................48 II.3.2. Hàm VALUE.......................................................................................48 II.4. Nhóm hàm kí tự...........................................................................................49 II.4.1. Hàm LEFT...........................................................................................49 II.4.2. Hàm RIGHT ........................................................................................49 II.4.3. Hàm MID.............................................................................................50 II.4.4. Hàm UPPER........................................................................................50 II.4.5. Hàm LOWER ......................................................................................51 II.4.6. Hàm PROPER .....................................................................................51 II.4.7. Hàm TRIM ..........................................................................................52 II.5. Nhóm hàm điều kiện ...................................................................................52 II.5.1. Hàm IF.................................................................................................52 II.5.2. SUMIF.................................................................................................53 II.5.3. COUNTIF............................................................................................53 II.6. Nhóm hàm ngày tháng................................................................................54 II.6.1. Date......................................................................................................54 II.6.2. WEEKDAY.........................................................................................54 II.6.3. DAY.....................................................................................................54 II.6.4. MONTH...............................................................................................55 II.6.5. YEAR ..................................................................................................55 II.7. Nhóm hàm logic..........................................................................................55 II.7.1. Hàm And..............................................................................................55 II.7.2. Hàm OR...............................................................................................56 II.7.3. Hàm NOT ............................................................................................56 II.8. Nhóm hàm tìm kiếm ....................................................................................57 II.8.1. Hàm VLOOKUP .................................................................................57 Khoa CNTT Giáo trình Excel 121 II.8.2. Hàm HLOOKUP .................................................................................58 II.9. Cách sử dụng hộp Function .......................................................................58 Câu hỏi và bài tập.................................................................................................60 Bài 5: Sao chép và di chuyển dữ liệu..................................................................62 I. Sao chép .............................................................................................................62 I.1. Sao chép dữ liệu...........................................................................................62 I.2. Dùng chuột...................................................................................................62 I.2.1. Dùng nút trái chuột: ..............................................................................62 I.2.2. Dùng nút phải chuột..............................................................................63 I.3. Dùng bộ đệm (clipboard) ............................................................................64 I.4. Sao chép bình thường:.................................................................................64 I.5. Dán đặc biệt.................................................................................................65 I.6. Sao chép sang vùng lân cận nhờ AutoFill (tự động điền dữ liệu)...............66 II. Di chuyển dữ liệu.............................................................................................66 II.1. Dùng chuột .................................................................................................67 II.1.1. Dùng nút trái chuột ..............................................................................67 II.1.2. Dùng nút phải chuột ............................................................................68 II.2. Dùng bộ đệm (clipboard) ...........................................................................68 II.3. Di chuyển bình thường: ..............................................................................69 III. Xoá dữ liệu: ....................................................................................................69 IV. Sao chép định dạng, chèn ghi chú ................................................................70 IV.1. Sao chép định dạng:..................................................................................70 IV.2. Chèn ghi chú (Comment): .........................................................................70 IV.3. Chỉnh sửa và xoá ghi chú: ........................................................................70 Khoa CNTT Giáo trình Excel 122 Câu hỏi và bài tập.................................................................................................71 Bài 6: Định dạng cho dữ liệu...............................................................................72 I. Định dạng cho dữ liệu kiểu số..........................................................................72 II. Định dạng ngày tháng.....................................................................................73 III. Định dạng vị trí ..............................................................................................74 III.1. Định dạng kiểu chữ cho dữ liệu ................................................................76 III.2. Tạo đường viền ô.......................................................................................77 III.3. Tô màu .......................................................................................................77 Bài 7: Cơ sở dữ liệu trong excel và cách đặt tên cho vùng dữ liệu ..................80 I. Khái niệm ..........................................................................................................80 II. Cách tạo một danh sách trên bảng tính ........................................................81 II.1. Kích thước và vị trí của danh sách.............................................................81 II.2. Các nhãn của cột (column labels) ..............................................................81 II.3. Nội dung của hàng và cột ...........................................................................81 III. Cách đặt tên cho vùng dữ liệu và truy cập tới vùng đã đặt tên ................82 III.1. Cách đặt tên cho một ô hoặc một vùng ô ..................................................82 III.2. Đặt tên cho các ô bằng cách sử dụng các nhãn hàng và cột có sẵn.........83 III.3. Truy cập tới vùng đã đặt tên......................................................................83 Bài 8: Tìm kiếm và sắp xếp dữ liệu ....................................................................85 I. Tìm kiếm và thay thế ........................................................................................85 I.1. Tìm kiếm: .....................................................................................................85 I.2. Thay thế: ......................................................................................................85 Khoa CNTT Giáo trình Excel 123 II. Sắp xếp dữ liệu ................................................................................................86 II.1. Sắp xếp dữ liệu tăng hoặc giảm dần theo nội dung của một cột................87 II.2. Sắp xếp dữ liệu theo nội dung của hai hoặc nhiều cột...............................87 II.3. Sắp xếp xếp dữ liệu theo nội dung của hàng ..............................................89 II.4. Sắp xếp theo tháng, ngày hoặc theo thứ tự do người dùng tạo..................89 II.4.1. Tạo danh sách theo thứ tự do người dùng tạo (custom list) ................89 II.4.2. Sắp xếp theo tháng, ngày hoặc theo thứ tự của danh sách tự tạo ........90 Bài 9: Tạo biểu đồ.................................................................................................94 I. Các phần tử cơ bản của biểu đồ ......................................................................94 II. Tạo biểu đồ ngầm định theo một bước ........................................................94 III. Cách tạo đồ thị nhờ Chart Wizarrd ............................................................96 Bài 10: Định dạng trang và In ấn .....................................................................101 I. Định dạng trang ..............................................................................................101 I.1. Định dạng trang.........................................................................................101 I.1.1. Page ....................................................................................................102 I.1.2. Margins ...............................................................................................102 I.1.3. Header/Footer ....................................................................................102 I.1.4. Sheet ...................................................................................................103 II. In .....................................................................................................................104 Câu hỏi và bài tập...............................................................................................106 Ôn tập ..................................................................................................................107 I. Một số thao tác cơ bản với file dữ liệu..........................................................107 II. Nhập, sửa, xoá dữ liệu...................................................................................107 II.1. Nhập hằng: ...............................................................................................107 Khoa CNTT Giáo trình Excel 124 II.2. Nhập công thức: .......................................................................................107 II.3. Sửa toàn bộ dữ liệu...................................................................................107 II.4. Sửa một phần dữ liệu: ..............................................................................108 II.5. Xoá dữ liệu................................................................................................108 III. Các thao tác với hàng, cột, ô .......................................................................108 III.1. Chọn hàng, cột ô .....................................................................................108 III.2. Chèn thêm hàng, cột, ô............................................................................108 III.3. Huỷ thêm hàng, cột, ô..............................................................................108 III.4. Thay đổi độ rộng cột ...............................................................................109 III.5. Thay đổi độ cao hàng ..............................................................................109 IV. Các thao tác với một bảng tính...................................................................109 IV.1. Kích hoạt bảng tính.................................................................................109 IV.2. Các thao tác với sheet .............................................................................109 V. Một số hàm thông dụng ................................................................................110 V.1. Các hàm thống kê.....................................................................................110 V.2. Các hàm đổi kiểu......................................................................................110 V.3. Các hàm kí tự ...........................................................................................110 V.4. Các hàm toán học ....................................................................................110 V.5. Các hàm logic ..........................................................................................110 V.6. Cách sử dụng hộp Function .....................................................................111 VI. Sao chép và di chuyển dữ liệu.....................................................................111 VI.1. Sao chép dữ liệu......................................................................................111 VI.2. Di chuyểndữ liệu .....................................................................................111 VII. Cơ sở dữ liệu ...............................................................................................112 Khoa CNTT Giáo trình Excel 125 VIII. Sắp xếp dữ liệu..........................................................................................112 IX. Biểu đồ ..........................................................................................................112 X. Định dạng cho dữ liệu và in ấn.....................................................................112