Hướng dẫn thực hành MS-Excel 2003

Hướng dẫn thực hành MS-Excel 2003 BÀI 1. GIỚI THIỆU CỬA SỔ CHƯƠNG TRÌNH, QUẢN LÝ TỆP TIN, NHẬP, TÍNH TOÁN VÀ TRÌNH BÀY BẢNG TÍNH 1. Cửa sổ chương trình - Microsoft Excel là một phần mềm được thiết kế để nhập, tính toán các bảng số liệu bằng các công cụ hàm số và công thức. Chính tính linh hoạt trong tính toán của Excel làm nên sự khác biệt cơ bản giữa các bảng tính được thiết kế trên Excel so với các bảng tính được thiết kế trên Word. - Với mục đích tạo các tệp tin có nội dung là các bảng số liệu nên ngay từ đầu màn hình giao diện của Excel đã được thiết kế dưới dạng các bảng bao gồm cột và hàng. H1. Một góc màn hình giao diện của chương trình Dữ liệu được nhập vào các ô của bảng Ký hiệu cột Số TT của hàng Thanh công thức - Tên của tệp tin Excel luôn được mặc định là Book 1 (2,3,…) - Một Book bao gồm 255 Sheet (bảng tính), các Sheet được đặt tên từ Sheet 1 đến Sheet 255. Thông thường khi cài đặt chương trình hệ thống sẽ để hiển thị mặc định trên màn hình 3 Sheet, để có thêm các Sheet ta chèn từ menu Insert\ Worksheet. - Một Sheet bao gồm: F 256 Cột: Các cột được ký hiệu theo bảng chữ cái từ A đến IV F 65536 Hàng (dòng): Các hàng được đánh số từ 1 đến 65536 - Giao của Cột và Hàng trong bảng gọi là Ô + Ô được xác định bằng địa chỉ Ô, địa chỉ ô cho biết vị trí của ô trên bảng + Địa chỉ Ô được xác định bằng ký hiệu của cột và số thứ tự của hàng. Vd: Ô A1: Cột A Hàng 1 2. Quản lý tệp tin (Xem chương trình Word cơ bản) 3. Nhập Dl và Tính toán trong bảng tính a. Nhập Dữ liệu - Dữ liệu trong Excel được nhập vào các ô của Bảng. Để nhập Dl vào ô nào ta kích chuột vào ô đó và nhập từ bàn phím. - Có hai loại DL: + Dl dạng số (Number): Luôn được mặc định căn bên lề phải của ô. Bao gồm các loại DL sau: Số, Ngày tháng, Tiền tệ,…Vd: + Dl dạng chữ (Text): Luôn mặc định căn bên lề trái của ô. Vd: * Cy: w Nếu Dl dạng số (Number) bị nhập sai quy ước của hệ thống thì chương trình sẽ tự chuyển về định dạng chữ (Text), khi đó ta sẽ không thể thực hiện được các phép toán số học. w Để xem hoặc thay đổi các quy ước của hệ thống về nhập số liệu ta vào Start \ Control Panel\ Regional and Language Options\Regional Options\Customize: F Numbers: Xem hoặc thay đổi các quy ước nhập số F Curency: Xem hoặc thay đổi các quy ước nhập tiền tệ F Date: Xem hoặc thay đổi các quy ước nhập ngày tháng F Time: Xem hoặc thay đổi các quy ước nhập giờ w Một số sự khác biệt giữa quy ước mặc định của máy tính trong hộp thoại Regional Options với cách viết thông thường: Loại ký tự Quy ước trên máy Các viết thông thường Dấu thập phân (Decimal Symbol) Dấu chấm (.) Dấu phẩy (,) Dấu phân cách giữa các nhóm số Trăm, Ngàn, Triệu (Digit group Symbol) Dấu phẩy (,) Dấu chấm (.) Dấu phân cách giữa các đối số trong Hàm (List separator) Dấu phẩy (,) Dấu chấm phẩy (;) Ngày tháng Tháng/Ngày/Năm (mm/dd/yy) Ngày/Tháng/Năm (dd/mm/yy) Mốc điền H2. Đánh số TT tự động Ta có thể thay đổi các loại ký tự trên bằng cách xoá và nhập lại ký tự vào các vị trí tương ứng. b. Đánh số Thứ tự tự động trong bảng tính Excel * C1: - Nhập hai số đầu của danh sách số TT vào hai ô liền kề - Bôi đen hai ô số đó - Đưa trỏ chuột vào Mốc điền của vùng bôi đen và kéo * C2: - Nhập số bắt đầu của danh sách số TT - Bấm Enter - Kích hoạt lại ô số đó - Đưa trỏ chuột vào mốc điền - Bấm giữ Ctrl+ Bấm giữ chuột và kéo đến cuối danh sách H3. Hộp thoại đánh số TT tự động Chọn hướng đánh số TT tự động Nhập gía trị dừng Nhập bước nhẩy (công sai) * C3: - Nhập số bắt đầu của danh sách số TT - Bấm Enter - Kích hoạt lại ô số đó - Vào Edit\ Fill\ Series: + Tại Series in: Chọn hướng đánh số TT F Rows: Đánh số TT trên Hàng F Columns: Đánh số TT trên cột + Tại Step Value: Nhập giá trị nhẩy (công sai) + Tại Stop Value: Nhập giá trị dừng c. Các tính toán trong bảng tính - Mọi phép tính trong bảng tính Excel luôn bắt đầu bằng dấu bằng (=) hoặc dấu cộng (+) - Khi tính toán Excel thường dùng hai loại công cụ tính toán sau: + Các phép toán số học (+,-, *, /,…) + Các hàm số - Một số toán tử tính toán và toán tử so sánh thường dùng trong Excel: + Các toán tử tính toán: + - * / % ^ Cộng Trừ Nhân Chia Phần trăm Luỹ thừa + Các toán tử so sánh: = > >= < <= Bằng Không bằng Lớn hơn Lớn hơn hoặc bằng Nhỏ hơn Nhỏ hơn hoặc bằng H4. Chọn căn lề & xoay Dl trong Ô Chọn căn lề theo chiều ngang Ô Chọn căn lề theo chiều dọc Ô Bấm kéo để xoay chữ 4. Trình bày (định dạng) bảng tính a. Căn lề, đổi Font, đổ Mầu nền trong Ô - Bôi đen các ô cần định dạng - Format\ Cells (Ctrl+1): * Alignment: Căn lề và xoay chữ trong Ô + Horizontal: Chọn căn lề theo chiều ngang của ô + Vertical: Chọn căn lề theo chiều dọc ô *Cy: Để đưa Dl vào giữa Ô: Tại Horizontal và Vertical ta chọn Center + Orientation: Xoay chữ * Font: Thay đổi cỡ, mầu, kiểu dáng của chữ (Tương tự chương trình Word) H5. Thẻ lệnh Font để định dạng Font chữ Chọn tên Font Chọn kiểu đường gạch chân Chọn kiểu chữ Chọn cỡ chữ Chọn mầu của chữ H6. Thẻ lệnh kẻ đường viền bảng Chọn mầu đường viền Kích xoá đường viền Kích tạo đường bao ngoài Kích tạo đường phân cách ô trong bảng Tạo/ Huỷ các đường viền riêng lẻ trong bảng Chọn kiểu đường viền * Patterns: Chọn mầu nền Tại Color: Chọn mầu OK b. Kẻ bảng - Bôi đen vùng Dl cần kẻ bảng - Format\ Cells (Ctrl+1)\ Border + Tại Style: Chọn kiểu đường viền + Tại Color: Chọn mầu đường viền + Kích chọn Outline: Tạo đường bao ngoài + Sang ô Style chọn lại đường viền + Kích chọn Inside: Tạo đường phân cách ô OK * Cy: - Xoá đường viền bảng: Trong hộp thoại Format\ Cell\ Border\ Chọn None - Thay đổi một trong các đường viền bảng ta kích lên các nút lệnh trên vùng Border 5. Các định dạng khác a. Trộn Ô - Bôi đen các ô cần trộn - Kích lên nút lệnh Merge and Center trên thanh công cụ Formatting * Cy: - Excel chỉ cho phép trộn một ô chứa Dl với các ô trống. Nếu ta trộn hai ô cùng chứa Dl thì Dl trên ô thứ hai sẽ bị xoá. - Nếu phải trộn ô nhiều lần ta chỉ cần trộn lần đầu, những lần tiếp theo ta chỉ bôi đen và bấm phím F4. - Xoá trộn ô: + Bôi đen các ô cần xoá + Format\ Cell (Ctrl+1)\ Alignment: Xoá dấu tích tại ô Merge Cells hoặc kích chọn lệnh Merge and Center trên thanh công cụ Formatting lần nữa. b. Ngắt dòng trong Ô Trong bảng tính Excel nếu ta bấm Enter thì trỏ chuột sẽ nhẩy xuống ô của dòng kế tiếp trong bảng. Muốn ngắt dòng trong cùng một ô, ta làm như sau: + Đặt dấu chèn tại vị trí cần ngắt + Bấm giữ tổ hợp phím: Alt+ Enter c. Chỉnh sửa Dl trong ô Sau khi nhập Dl và ra khỏi ô, để quay lại ô chỉnh sửa ta làm như sau: - C1: w Nháy đúp chuột lên ô chứa Dl - C2: w Kích hoạt ô chứa Dl w Bấm F2 trên bàn phím - C3: w Kích hoạt ô chứa Dl w Đưa trỏ chuột lên thanh công thức và chỉnh sửa d. Xoá định dạng hoặc Dl trong ô - Bôi đen các ô cần xoá định dạng - Edit\ Clear: F All: Xoá cả nội dung và định dạng trong ô F Formats: Xoá định dạng và giữ lại nội dung F Contents: Xoá nội dung và giữ lại định dạng (Tương ứng với bấm phím Delete trên bàn phím) F Comments: Xoá các ghi chú trong ô 6. Khai báo mặc định Font chữ và số Sheet của tệp Nhập số Sheet Chọn Font chữ Chọn cỡ chữ H7. Hộp thoại khai báo các tuỳ chọn Font chữ và số Sheet - Tools\ Options\ General: + Tại Sheets in new workbook: Nhập số Sheet mặc định của tệp tin khi khởi động chương trình. + Tại Standard Font: Chọn Font chữ mặc định của chương trình. + Tại Size: Nhập cỡ chữ. OK - Tắt và khởi động lại chương trình Excel.BÀI 2. DI CHUYỂN, SAO CHÉP DỮ LIỆU CÁC THAO TÁC VỚI SHEET 1. Di chuyển, sao chép Dl a. Di chuyển (Cut) - Bôi đen vùng bảng chứa Dl cần di chuyển - C1: Đưa trỏ chuột lên đường viền của vùng bôi đen và bầm giữ chuột rồi kéo đến vị trí ô mong muốn (Chú ý: Trỏ chuột phải có hình mũi tên () khi di đến đường viền ô) - C2: + Edit\ Cut (Ctrl+ X hoặc kích lên lệnh Cut () trên thanh lệnh chuẩn Standard) + Kích hoạt ô đầu tiên của vùng bảng cần dán Dl + Edit\ Paste (Ctrl+ V hoặc kích lên lệnh Paste () trên thanh lệnh chuẩn Standard) b. Sao chép Dl (Copy) - Bôi đen vùng bảng chứa Dl cần sao chép - C1: Đưa trỏ chuột lên đường viền vùng bôi đen, bấm giữ phím Ctrl+ bấm giữ chuột và kéo đến vị trí mong muốn. - C2: + Edit\ Copy (Ctrl+ C hoặc kích lên lệnh Copy () trên thanh lệnh chuẩn Standard) + Kích hoạt ô đầu tiên của vùng bảng cần dán Dl + Edit\ Paste (Ctrl+ V hoặc kích lên lệnh Paste () trên thanh lệnh chuẩn Standard) 2. Các thao tác với Bảng tính (Sheet) a. Di chuyển, Sao chép Sheet (Cut và Copy) * C1: H8. Hộp thoại di chuyển, sao chép Dl Chọn tệp tin đích Chọn vị trí đặt bản sao Tích chọn để Sao chép Bấm giữ tên Sheet và kéo để di chuyển ( ). Trong quá trình di chuyển ta bấn giữ phím Ctrl để sao chép. * C2: - Kích chọn Sheet cần di chuyể n hoặc sap chép - Edit\ Move or Copy Sheet... (hoặc kích phải chuột lên tên Sheet cần di chuyển hoặc sao chép\ chọn Move or Copy) à Xuất hiện hộp thoại Move or Copy: + Tại To book: Chọn tệp tin đích (tệp sẽ lưu bản copy) + Tại Before sheet: Chọn vị trí đặt bản sao (đặt trước sheet nào?) + Chọn OK: Di chuyển + Tích chọn Create a copy\ Chọn OK: Sao chép b. Đổi tên Sheet - Kích chọn Sheet cần đổi tên H9a. Hộp lệnh ẩn Sheet H9b. Bảng ds các Sheet bị ẩn Kích chọn để ẩn Sheet Chọn để hiện lại Sheet Kích chọn Sheet cần hiện lại - Format\ Sheet\ Rename (hoặc kích phải chuột lên tên Sheet\ Chọn Rename hoặc kích đúp chuột lên tên Sheet) - Khi đó tên Sheet sẽ được bôi đen, ta xoá tên cũ và nhập tên mới của Sheet. c. Ẩn/ Hiện Sheet - Chọn Sheet cần ẩn - Format\ Sheet\ Hide * Hiện lại Sheet đã ẩn: - Format\ Sheet\ Unhide à Xuất hiện hộp thoại với danh sách Sheet đã bị ẩn, ta chọn tên Sheet cần hiện lại rồi chọn OK d. Chèn/ Xoá Sheet * Chèn: Insert\ Worksheet * Xoá: - Kích chọn sheet cần xoá - Edit\ Delete Sheet (hoặc kích phải chuột lên tên Sheet cần xoá\ chọn Delete) BÀI 3. CÁC THAO TÁC VỚI HÀNG, CỘT, Ô CỦA SHEET 1. Chèn Hàng/ Cột/ Ô a. Chèn Hàng - Bôi đen số hàng trên bảng tương ứng với số hàng cần chèn (bôi các hàng phía dưới vị trí cần chèn) - Insert\ Rows b. Chèn Cột - Bôi đen số cột trên bảng tương ứng với số cột cần chèn (bôi các cột bên phải vị trí cần chèn) - Insert\ Columns c. Chèn Ô - Bôi đen số ô trên bảng tương ứng với số ô cần chèn - Insert\ Cells à Xuất hiện hộp lệnh các tuỳ chọn khi chèn: + Shift cells right: Chèn ô và đẩy Dl của các ô trên vùng bôi đen sang phải + Shift cells down: Chèn ô và đẩy Dl của các ô trên vùng bôi đen xuống dưới + Entire row: Chèn các ô như hàng mới của bảng (T.ư với chèn hàng) + Entire column: Chèn các ô như cột mới của bảng (T.ư với chèn cột) 2. Xoá Hàng/ Cột/ Ô a. Xoá Hàng - Bôi đen các hàng cần xoá (bấm giữ chuột và kéo trên cột số TT hàng của Sheet) - Edit\ Delete b. Xoá Cột - Bôi đen các cột cần xoá (bấm giữ chuột và kéo trên dòng ký hiệu cột của Sheet) - Edit\ Delete c. Xoá Ô - Bôi đen các ô cần xoá - Edit\ Delete…. à Xuất hiện hộp lệnh các tuỳ chọn khi xoá: + Shift cells left: Xoá ô và đẩy Dl của các ô trên vùng bôi đen sang trái. + Shift cells up: Xoá ô và đẩy Dl của các ô từ phía dưới lên + Entire row: Xoá tất cả các ô trên hàng có ô bôi đen (T.ư với xoá hàng) + Entire column: Xoá tất cả các ô trên cột có ô bôi đen (T.ư với xoá cột) 3. Ẩn- hiện Hàng/ Cột - Bôi đen các ô trên Hàng/ Cột cần ẩn - Format: + Chọn Row\ Hide: Ẩn Hàng + Chọn Column\ Hide: Ẩn cột * Hiện lại Hàng/ Cột đã ẩn - Bôi đen ô trên Hàng/ Cột liền trước và liền sau của Hàng/ Cột đã ẩn - Format: + Chọn Row\ Unhide: Hiện lại hàng + Chọn Column\ Unhide: Hiện lại cột 4. Thay đổi kích cỡ của Hàng/ Cột * C1: Sử dụng chuột trên màn hình soạn thảo - Hàng: Đưa trỏ chuột lên đường phân cách giữa hai số TT hàng của Sheet và kéo - Côt: Đưa trỏ chuột lên đường phân cách giữa hai ký hiệu cột của Sheet và kéo * Cy: Để thay đổi đồng thời nhiều hàng/ cột có kích cỡ bằng nhau ta bôi đen các hàng/ cột trước khi kéo (bôi trên số TT hàng/ Ký hiệu cột) * C2: Thay đổi bằng Lệnh - Kích hoạt một ô trên hàng hoặc cột cần thay đổi kích cỡ - Format: + Chọn Row\ Height (): Nhập chiều cao của Hàng + Chọn Column\ Width (): Nhập độ rộng của Cột BÀI 4. ĐỊA CHỈ TƯƠNG ĐỐI, TUYỆT ĐỐI KHÁI NIỆM HÀM SỐ & HÀM SUM 1. Địa chỉ tương đối, tuyệt đối a. Địa chỉ tương đối - Khái niệm: Là địa chỉ ô nhập vào công thức mà khi sao công thức đến vị trí mới thì địa chỉ đó thay đổi tương ứng. - Ứng dụng: Được sử dụng khi thực hiện các phép toán mà Dl tính toán phải thay đổi trên các ô khác nhau mỗi khi ta sao công thức. H10. Ví dụ địa chỉ tương đối ô - Cách thiết lập: + Nhập dấu bằng (=) vào ô chứa kết quả + Kích (hoặc khoanh chọn các) ô chứa Dl cần tính Vd: Ở Hình 9. Giá trị trong công thức tại ô D3 được lấy từ ô B3 và ô C3. Khi ta sao công thức từ D3 xuống D4 địa chỉ B3 và C3 được tự động di chuyển xuống B4 và C4 tương ứng. b. Địa chỉ tuyệt đối (cố định) - Khái niệm: Là địa chỉ ô nhập vào công thức mà khi sao công thức đến vị trí mới thì địa chỉ đó không thay đổi (luôn cố định) - Ứng dụng: Được sử dụng khi ta lấy giá trị trên một ô hoặc phạm vi ô là A đem cộng trừ nhân chia hoặc so sánh với một ô hoặc phạm vi ô là B thì ta phải cố định địa chỉ của B. H11. Ví dụ địa chỉ tuyệt đối ô Đ/c tương đối ô Đ/c tuyệt đối ô - Địa chỉ tuyệt đối luôn được định dạng bằng dấu $ trước ký hiệu cột hoặc số TT hàng hoặc cả cột và hàng. - Phân loại: Gồm hai loại + Địa chỉ tuyệt đối ô Vd: $A$1 + Địa chỉ hỗn hợp: F Tương đối cột, tuyệt đối hàng Vd: A$1 F Tương đối hàng, tuyệt đối cột Vd: $A1 - Cách thiết lập + Nhập địa chỉ ô vào công thưc + Bấm phím F4: F 1 lần để cố định địa chỉ ô (cả cột và hàng) F 2 lần để cố định hàng tương đối cột F 3 lần để cố định cột tương đối hàng F 4 lần đưa địa chỉ ô về dạng tương đối * Cy: - Địa chỉ tương đối và tuyệt đối chỉ được nhắc đến khi phải sao công thức 2. Hàm số a. Khái niệm: Là công thức được dựng sẵn để thực hiện các phép toán chuyên biệt - Sử dụng hàm số để tính toán trong Excel làm nên sự khác biệt cơ bản giữa các bảng tính được tạo trên Excel với các bảng được tạo bằng các chương trình khác. - Excel 2003 cung cấp trên 300 hàm số khác nhau và được gom thành từng nhóm (nhóm hàm tính toán, nhóm hàm chuỗi, nhóm hàm logic,….) do đó ta có thể sử dụng để xử lý hầu hết các phép toán số học, chuỗi và các phép toán logic,… - Mỗi hàm số được xây dựng dưới dạng một khuân mẫu; mỗi hàm số chỉ dùng để tính cho một phép tính. Do đó ta phải nhớ dạng thức của hàm cũng như công dụng của từng hàm. b. Dạng thức chung của hàm: =Tên hàm(Đối số 1, Đối số 2,……..) Trong đó: + Tên hàm được sử dụng theo quy ước của Excel. Tên hàm không được viết tắt, không chứa dấu cách, không chứa ký tự đặc biệt. + Đối số của hàm có thể là giá trị chữ, giá trị số, địa chỉ ô hoặc một hàm số khác. Nếu đối số là ký tự chữ thì phải nhập trong cặp nháy kép (“ ”); nếu đối số là một hàm số khác thì không phải nhập dấu bằng (=) trước tên hàm. c. Nhập hàm và tính toán: - C1: Chèn hàm từ thư viện hàm số của chương trình + Kích hoạt ô kết quả Kích chọn tên nhóm hàm H12. Bảng danh mục các hàm số Kích chọn tên hàm + Insert\ Function (hoặc kích lên mũi tên của nút lệnh AutoSum () trên thanh lệnh chuẩn Standard\ Chọn lệnh More Functions….). à Xuất hiện hộp thoại chọn Hàm số: F Tại Or select a Category: Chọn nhóm hàm F Tại Select a function: Chọn tên hàm dùng để tính toán trong danh sách các hàm số tương ứng với nhóm hàm được chọn. OK H13. Hộp thoại khai báo đối số của hàm Nhập đối số à Xuất hiện hộp thoại nhập đối số của hàm: Ta nhập đối số vào các dòng soạn thảo tương ứng * Cy: - Tuỳ từng hàm có thể có một, hai hay nhiều hơn hai đối số - Đối số có thể được nhập trực tiếp vào các dòng soạn thảo hoặc đặt dấu chèn và khoanh chọn đối số từ bảng tính. - C2: Nhập hàm trực tiếp vào bảng tính (đây là cách được sử dụng phổ biến vì nó làm cho việc thực hiện phép tính nhanh chóng) + Kích hoạt ô kết quả + Nhập tên hàm và đối số theo cú pháp: =Tên hàm(Đ.số 1, Đ.số 2,….) 3. Hàm Sum * Công dụng: Tính tổng các gía trị số * Dạng thức: =Sum(Number1, Number2,….,Number30) Trong đó: Number1, Number2,…: Là các giá trị số, địa chỉ ô chứa giá trị số…. cần tính tổng. Cy: - Nếu các giá trị số cần tính tổng được nhập trên các ô liền kề nhau thì ta khoanh chọn địa chỉ ô của phạm vi bảng đó. Khi đó hàm Sum có dạng: =Sum(Number1: Number n) Vd: =Sum(5,4) hoặc =Sum(A1,B7) hoặc =Sum(B2:B9) - Với thao tác tính tổng trên cột ta có thể dùng lệnh tắt trên thanh lệnh chuẩn: + Bôi đen vùng Dl cần tính tổng + Kích lên nút lệnh AutoSum () trên Standard 4. Tạo chỉ số trên chỉ số dưới - Bôi đen ký tự cần tạo chỉ số - Format\ Cells (Ctrl+ 1) Tại Effect, chọn: F SuperScript: Tạo chỉ số trên F SubScript: Tạo chỉ số dưới 5. Chèn ký tự đặc biệt - Đặt dấu chèn vào vị trí - Insert\ Symbol: + Tại Font: Chọn Font chữ chứa ký tự đặc biệt (Windings, Webding, Symbol,…) + Kích chọn ký tự đặc biệt cần chèn + Insert\ Close 6. Định dạng số TT trong cặp ngoặc đơn - Đánh số TT - Bôi đen vùng số TT - Format\ Cells (Ctrl+1)\ Number: + Tại Category: Chọn Custom + Tại dòng soạn thảo Type nhập mã định dạng: (0) OK Bài 5. HÀM AVERAGE, MAX, MIN, RANK VÀ HÀM IF 1. Nhóm hàm thống kê a. Hàm Average * Công dụng: Tính trung bình cộng các giá trị số trong một phạm vi ô * Dạng thức: =Average(Number1, Number2,….,Number30) Trong đó: Number1,…, Number30: Là các giá trị số, địa chỉ ô chứa giá trị số,….cần tính trung bình Vd: =Average(5,3,4) (Tương ứng phép tính )= 4 b. Hàm Max * Công dụng: Tìm và trả về giá trị số lớn nhất trong các số đã cho * Dạng thức: =Max(Number1, Number2,….,Number30) Vd: =Max(5,3,4) Cho ta kết quả bằng 5 (số lớn nhất trong các số 5,3,4) c. Hàm Min * Công dụng: Tìm và trả về giá trị số nhỏ nhất trong các số đã cho * Dạng thức: =Min(Number1, Number2,….,Number30) Vd: =Min(5,3,4) Cho ta kết quả bằng 3 (số nhỏ nhất trong các số 5,3,4) 2. Hàm Rank * Công dụng: So sánh và trả về thứ bậc của một giá trị số trong một dãy số cho trước. Thường được dùng để xếp loại hoặc xếp thứ bậc của một nhóm người dựa trên một tiêu chí có thể định lượng. (ngày công, điểm thi,….) * Dạng thức: =Rank(Number,Ref,Order) Trong đó: Number: Giá trị số cần xếp thứ bậc Ref: Phạm vi so sánh Order: Hình thức xếp thứ bậc. Được nhập bằng một trong hai giá trị 0 hoặc 1 F Nhập 0 (Xếp thứ bậc giảm dần): Được sử dụng khi thứ bậc được đánh giá từ số lớn nhất trở xuống (Thường sử dụng khi xếp loại nhân viên dựa trên ngày công hoặc xếp loại học sinh dựa trên điểm trung bình) F Nhập 1 (Xếp thứ bậc tăng dần): Được sử dụng khi thứ bậc được đánh giá từ giá trị số nhỏ nhất trở lên (Thường sử dụng khi xếp loại cho các vận động viên của các môn thể thao tính giờ) 3. Hàm If * Công dụng: Là loại hàm Logic thường dùng thực hiện các phép thử điều kiện. Nếu điều kiện thoả mãn thì hàm nhận Giá trị 1 làm kết quả, ngược lại nếu điều kiện không thoả mãn thì hàm nhận Giá trị 2 làm kết quả. * Dạng thức: =If(Logical_test, Value_if_true,Value_if_false) Trong đó: Logical_test: Phép thử Logic (Điều kiện của hàm). Là một phép so sánh nên đối số này luôn chứa các toán tử so sánh (=,>,>=,<,<=,….) Value_if_true (Giá trị 1): Giá trị trả về của hàm nếu phép thử (Điều kiện) của hàm thoả mãn Value_if_false (Giá trị 2): Giá trị trả về của hàm nếu phép thử (Điều kiện) của hàm không thoả mãn à Ta có thể viết lại dạng thức của hàm: =If(Điều kiện,Gtrị1,Gtrị2) * Cy: - Giá trị 2 của hàm có thể chứa các hàm If con. Có n trường hợp ta sử dụng n-1 If (Số If luôn nhỏ hơn số trường hợp là 1 If và không vượt quá 7 hàm if) - Trường hợp cuối của hàm If không cần ghi tên hàm không cần ghi điều kiện, chỉ cần ghi kết quả trả về. - Kết thúc hàm If có bao nhiêu chữ “If” ta đóng bấy nhiêu ngoặc. ( Mỗi trường hợp thường là một cặp nguyên nhâ- kết quả: Nếu- thì) =If(Điều kiện,Gtrị,If(Điều kiện,Gtrị,If(Điều kiện,Gtrị,Gtrị2))) Cặp Nếu- Thì 1 Cặp Nếu- Thì 2 Cặp Nếu- Thì 3 TH cuối Nếu Thì Nếu Thì Nếu Thì Vd: Có 4 cặp Nếu- thì, hàm If có dạng: BÀI 6. HÀM AND VÀ HÀM OR * Công dụng: Là loại hàm Logic thường dùng để trợ giúp các phép thử điều kiện. Cụ thể là thường trợ giúp cho hàm If trong các trường hợp hàm If có điều kiện kép. * Dạng thức: =And(Logical1,Logical2,……Logical30) =Or(Logical1,Logical2,……Logical30) * Cy: - Khi kết hợp với hàm If, hàm And và hàm Or thường làm điều kiện của hàm If, các điều kiện của hàm If sẽ làm đối số của hàm And hoặc hàm Or. Trong đó: + Hàm If kết hợp với hàm And khi điều kiện của hàm If là điều kiện kép và các điều kiện phải đồng thời thoả mãn thì hàm If với nhận giá trị 1. Nói cách khác hàm If có từ hai điều kiện trở lên và là điều kiện đồng thời xảy ra- “Và”. + Hàm If kết hợp với hàm Or khi điều kiện của hàm là điều kiện kép và chỉ cần một trong các điều kiện thoả mãn thì hàm If nhận giá trị 1. Hay hàm If có từ hai điều kiện trở lên và là điều kiện không đồng thời- “Hoặc”. =If(And(Đk1,Đk2,…,Đk30),Gtrị1,Gtrị2) =If(Or(Đk1,Đk2,…,Đk30),Gtrị1,Gtrị2) Điều kiện Điều kiện - Khi kết hợp vơi hàm And và hàm Or, hàm If có dạng: µ Một số điểm rút ra khi tính toán bằng hàm If: 8 Bước 1: Nhận diện hàm Nếu có cặp Nguyên nhân- Kết quả (Nếu- Thì) ta chắc chắn phải dùng hàm If. 8 Bước 2: Đếm số trường hợp (số cặp Nếu- Thì) để xác định số hàm If cần sử dụng. 8 Bước 3: Kiểm tra trong từng trường hợp xem là điều kiện đơn hay điều kiện kép để xác định có phải dùng kết hợp với hàm And hoặc hàm Or không. 8 Bước 4: Nếu là điều kiện kép ta phải kiểm tra điều kiện đó đồng thời (và) hay không đồng thời (hoặc) để lựa chọn hàm trợ giúp And hay Or cho từng trường hợp. 8 Bước 4: Nhập hàm và tính toán. BÀI 7. NHÓM HÀM ĐẾM H14. Ví dụ hàm đếm 1. Hàm Count * Công dụng: Đếm ra số ô chứa giá trị số trong một phạm vi ô * Dạng thức: =Count(Value1,Value2,….Value30) Trong đó: Value1,…: Là các địa chỉ ô phải đếm Ví dụ: Tại H14, địa chỉ ô của bảng Dl là A1:C5 Để đếm trong phạm vi ô trên có bao nhiêu ô chứa số ta sử dụng hàm Count =Count(A1:C5) --> =5 (Trong phạm vi ô A1:C5 có năm ô chứa gtrị số không phân biệt số âm số dương, số nhỏ số lớn) 2. Hàm CountA * Công dụng: Đếm ra số ô không trống (ô chứa giá trị số hoặc chữ) trong một phạm vi ô * Dạng thức: =CountA(Value1,Value2,….Value30) Ví dụ: Tại H14, địa chỉ ô của bảng Dl là A1:C5 Để đếm trong phạm vi ô trên có bao nhiêu ô chứa giá trị ta sử dụng hàm CountA: =CountA(A1:C5) --> =10 (Trong phạm vi ô A1:C5 có năm ô chứa gtrị bao gồm cả số và chữ) 3. Hàm CountBlank * Công dụng: Đếm ra số ô trống trong một phạm vi ô. * Dạng thức: =CountBlank(Value1,Value2,….Value30) Ví dụ: Tại H14, địa chỉ ô của bảng Dl là A1:C5 Để đếm trong phạm vi ô trên có bao nhiêu ô trống ta sử dụng hàm CountBlank: =CountBlank(A1:C5) --> =5 (Trong phạm vi ô A1:C5 có năm ô trống) 4. Hàm CountIf * Công dụng: Đếm ra số ô chứa giá trị thoả mãn điều kiện cho trước * Dạng thức: =Countif(Range,Criteria) Trong đó: Range: Phạm vi ô cần đếm Criteria: Điều kiện là một giá trị bất kỳ. Lưu ý: Nếu điều kiện phải sử dụng các toán tử so sánh hoặc điều kiện là một giá trị thời gian thì ta phải nhập trong cặp ngoặc kép. Ví dụ: Tại H14, địa chỉ ô của bảng Dl là A1:C5 Để đếm trong phạm vi ô trên có bao nhiêu ô chứa giá trị là chữ “A” ta sử dụng hàm CountIf: =CountIf(A1:C5,“A”) --> =3 (Trong phạm vi ô A1:C5 có ba ô chứa chữ “A”) BÀI 8. NHÓM HÀM TÌM KIẾM LẤY GIÁ TRỊ TỪ BẢNG PHỤ LÊN BẢNG CHÍNH VÀ NHÓM HÀM TRÍCH KÝ TỰ 1. Nhóm hàm tìm kiếm và lấy giá trị từ bảng phụ lên bảng chính a. Hàm Vlookup (Vertical Lookup) * Công dụng: Là loại hàm tìm kiếm và lấy giá trị từ bảng phụ lên bảng chính. Bằng cách: Hàm lấy một giá trị trên bảng chính làm mã tìm kiếm đi so sánh với các giá trị trên Cột đầu tiên của bảng phụ. Khi tìm thấy giá trị làm mã trên cột đầu tiên của bảng phụ hàm sẽ lấy một giá trị tương ứng cùng hàng trên cột khai thác chỉ định. * Dạng thức: =Vlookup(Lookup_value,Table_array,Col_index_num,Lookup_type) Trong đó: Lookup_value: Giá trị làm mã tìm kiếm (có thể là Số, Chữ, Đ/c ô hoặc một hàm số khác). Là một giá trị được lấy trên bảng chính, phải giống hoặc gần giống với giá trị trên cột đầu tiên của bảng phụ. Table_array: Bảng phụ (bảng chứa giá trị cần lấy lên bảng chính), ta có thể khoanh chọn cả bảng hoặc bỏ qua tiêu đề cột. Lưu ý: Bảng phụ không nhất thiết phải được khoanh từ cột đầu tiên. Ta khoanh từ cột nào thì hàm sẽ nhận đó là cột đầu tiên của bảng phụ, chỉ cần giá trị trên cột đầu phải giống hoặc gần giống với giá trị làm mã (Lookup_value) Col_index_num: Số thứ tự của cột khai thác chỉ định- Cột chứa giá trị cần lấy lên bảng chính. Lookup_type: Loại tìm kiếm. Được nhập bằng một trong hai giá trị 0 hoặc1 F Nhập 0- Tìm kiếm chính xác: Được sử dụng khi giá trị làm mã tìm kiếm giống với giá trị trên cột đầu tiên của bảng phụ. F Nhập 1- Tìm kiếm gần đúng (tìm kiếm tương đối): Được sử dụng khi giá trị làm mã tìm kiếm gần giống với các giá trị có mặt trên cột đầu tiên của bảng phụ. Khi đó hàm sẽ lấy vị trí của giá trị lớn nhất thoả mãn điều kiện nhỏ hơn hoặc bằng giá trị làm mã tìm kiếm. H15a. Bảng chính H15b. Bảng phụ Mã tìm kiếm Đ/c bảng phụ Số TT cột khai thác Loại tìm kiếm (chính xác) Cột đầu tiên Cột khai thác Cy: Để tìm kiếm tương đối yêu cầu cột đầu tiên của bảng phụ phải được sắp xếp theo trật tự tăng dần. Ví dụ 1: Hàm Vlookup trong trường hợp tìm kiếm chính xác Ví dụ 2: Hàm Vlookup trong trường hợp tìm kiếm gần đúng (tương đối) H16a. Bảng chính Mã tìm kiếm Đ/c bảng phụ STT cột khai thác Loại tìm kiếm (gần đúng) Cột đầu tiên Cột khai thác H16a. Bảng phụ b. Hàm Hlookup (Horizontal Lookup) * Công dụng: Là loại hàm tìm kiếm và lấy giá trị từ bảng phụ lên bảng chính. Bằng cách: Hàm lấy một giá trị trên bảng chính làm mã tìm kiếm đi so sánh với các giá trị trên Hàng đầu tiên của bảng phụ. Khi tìm thấy giá trị làm mã trên hàng đầu tiên của bảng phụ, hàm sẽ lấy một giá trị tương ứng cùng cột trên hàng khai thác chỉ định. * Dạng thức: =Hlookup(Lookup_value,Table_array,Row_index_num,Lookup_type) Trong đó: Row_index_num: Số thứ tự của hàng khai thác chỉ định- Hàng chứa giá trị cần lấy lên bảng chính. (Các đối số khác tương tự hàm Vlookup) H17a. Bảng chính Mã tìm kiếm Đ/c bảng phụ STT của hàng khai thác Loại tìm kiếm (chính xác) H17a. Bảng phụ Hàng đầu tiên Hàng khai thác Ví dụ: Hàm Hlookup trong trường hợp tìm kiếm chính xác 2. Nhóm hàm trích ký tự a. Hàm Left * Công dụng: Dùng để trích ra n ký tự từ bên Trái của một xâu ký tự * Dạng thức: =Left(Text,Num_chars) Trong đó: Text: Xâu ký tự, địa chỉ ô của xâu ký tự có chứa ký tự cần trích Num_chars: Số ký tự cần trích Ví dụ: =Left("Baitap",3) hoặc =Left(A1,3) (Nếu ô A1 chứa xâu ký tự Baitap) à =Bai b. Hàm Right * Công dụng: Dùng để trích ra n ký tự từ bên Phải của một xâu ký tự. * Dạng thức: =Right(Text,Num_chars) Trong đó: Text: Xâu ký tự, địa chỉ ô của xâu ký tự có chứa ký tự cần trích Num_chars: Số ký tự cần trích Ví dụ: =Right("Baitap",3) c. Hàm Mid * Công dụng: Dùng để trích ra n ký tự trong một xâu ký tự (trích từ trái sang phải) * Dạng thức: =Mid(Text,Start_num,Num_chars) Trong đó: Text: Xâu ký tự, địa chỉ ô của xâu ký tự có chứa ký tự cần trích Start_num: Vị trí của ký tự bắt đầu trích Num_chars: Số ký tự cần trích Ví dụ: =Mid("Baitap",3,2) hoặc =Mid(A1,3,2) (Nếu ô A1 chứa xâu ký tự Baitap) à =it BÀI 9. NHÓM HÀM THỜI GIAN VÀ CÁCH TÍNH TUỔI 1. Nhòm hàm thời gian a. Hàm Year * Công dụng: Trích ra giá trị Năm trong chuỗi Dl thời gian dạng Ngày, tháng, năm * Dạng thức: =Year(Serial_number) Trong đó: Serial_number: Là giá trị thời gian, Đ/c ô chứa giá trị thời gian dạng Ngày, tháng, năm * Cy: Nếu ta nhập trực tiếp thời gian vào hàm thì phải nhập trong cặp ngoặc kép Ví dụ: =Year("5/25/2006") hoặc =Year(B1) (Nếu trong ô B1 có chứa chuỗi thời gian 5/25/2006) à =2006 (giá trị năm) b. Hàm Month * Công dụng: Trích ra giá trị Tháng trong chuỗi Dl thời gian dạng Ngày, tháng, năm * Dạng thức: =Month(Serial_number) Ví dụ: =Month("5/25/2006") hoặc =Month(B1) (Nếu trong ô B1 có chứa chuỗi thời gian 5/25/2006) à =5 (giá trị tháng) c. Hàm Day * Công dụng: Trích ra giá trị Ngày trong chuỗi Dl thời gian dạng Ngày, tháng, năm * Dạng thức: =Day(Serial_number) Ví dụ: =Day("5/25/2006") hoặc =Day(B1) (Nếu trong ô B1 có chứa chuỗi thời gian 5/25/2006) à =25(giá trị ngày) d. Hàm Date * Công dụng: Trả về định dạng thời gian dạng Ngày Tháng Năm của các giá trị số. * Dạng thức: =Date(Year,Month,Day) Trong đó: Year: Giá trị năm Month: Giá trị tháng Day: Giá trị ngày Ví dụ: =Date(1977,01,19) à = 01/19/1977 e. Hàm Today * Công dụng: Trả về giá trị thời gian của Ngày tháng hiện tại theo thiết lập của đồng hồ hệ thống máy tính. * Dạng thức: =Today() Ví dụ: =Today() à = 7/24/2006 Nếu đó là thời gian của đồng hồ hệ thống f. Hàm Now * Công dụng: Trả về gia trị thời gian ở thời điểm hiện tại theo thiết lập của đồng hồ hệ thống * Dạng thức: =Now() Ví dụ: =Now() à = 7/22/2006 15:25 *Cy: - Hàm Now và hàm Today là loại hàm đặc biệt, nó không có đối số nhưng ta vẫn phải nhập cặp ngoặc đơn. - Kết quả của hàm Now và hàm Today tuỳ thuộc vào thiết lập của đồng hồ hệ thống vì vậy trước khi tính toán ta phải tính thử, nếu kết quả của hàm không đúng với thời gian thực tại thì phải khai báo lại thời gian của hệ thống. Cách khai báo như sau: F Cách 1: Nháy đúp vào biểu tượng đồng hồ hệ thống trên thanh Taskbar ở góc dưới bên phải màn hình F Cách 2: Start\ Setting\ Control Panel\ Date and Time 2. Cách tính tuổi a. Tính tương đối =Năm hiện tại - Năm sinh à =Year(Today())-Year(Ngày,Tháng,Năm sinh) b. Tính chính xác Đảm bảo người được tính phải có đủ 365 ngày mới được tính 1 tuổi: =(Today()-Ngày tháng năm sinh)/365 à =Số tuổi Có thể số tuổi sẽ là một số thập phân. Nếu chỉ cần lấy số tuổi tròn (số nguyên) ta có thể dùng kết hợp với hàm Int. Khi đó công thức có dạng: =Int((Today()-Ngày tháng năm sinh)/365) H18: Hộp thoại khai báo định dạng thời gian Kích chọn để khai báo định dạng tuỳ ý của người dùng Nhập định dạng tuỳ ý của người dùng 3. Thay đổi kiểu hiển thị của giá trị thời gian trên trang in - Bôi đen vùng Dl thời gian cần thay đối - Format\ Cells\ Number: + Tại Category: Chọn Custom + Tại Type: Nhập định dạng thời gian mong muốn Ví dụ: Thời gian dạng ngày tháng: dd-mm-yy; mm-dd-yyyy, dd-mm-yyyy Thời gian dạng giờ phút: hh:mm:ss OK BÀI 10. HÀM INT VÀ HÀM MOD 1. Hàm Int * Công dụng: Trích ra phần nguyên của một giá trị thập phân dương hoặc làm tròn đến số nguyên gần nhất trước dấu thập phân nếu là một số thập phân âm. * Dạng thức: =Int(Number) Trong đó: Number: Là một phép chia có dư hoặc một giá trị thập phân Ví dụ: =Int(11/3) à =3 2. Hàm Mod * Công dụng: Trích ra phần dư của một phép chia có dư * Dạng thức: =Mod(Number,Divisor) Trong đó: Number: Số bị chia Divisor: Số chia Ví dụ: =Mod(11,3) à =2 H19: Hộp thoại khai báo định dạng tiền tệ Kích chọn để khai báo định dạng tiền tệ Kích chọn dấu tiền tệ cần chèn (Phép chia 11/3 bằng 3 còn dư 2) 3. Định dạng dấu tiền tệ trong các giá trị số - Bôi đen vùng Dl cần thay đổi định dạng - Format\Cells (Ctrl+1)\Number: + Tại Category, chọn: Currency + Tại Symbol: Chọn dấu tiền tệ OK * Thay đổi vị trí dấu tiền tệ - Bôi đen vùng Dl cần thay đổi vị trí dấu tiền tệ - Format\Cells (Ctrl+1)\Number: + Tại Category, chọn: Custom + Tại Type: Xoá dấu tiền tệ tại vị trí hiện tại và nhập vào vị trí mong muốn OK * Xoá dấu tiền tệ và các định khác đưa giá trị số về dạng số nguyên - Bôi đen vùng số liệu cần xoá định dạng - Format\ Cells\ Number\ General\ OK Hoặc sử dụng chổi quét định dạng: - Đưa trỏ chuột lên một ô trống (hoặc ô chứa số nguyên) - Kích chọn lệnh Format Painter - Bôi đen vùng số liệu cần xoá định dạng tiền tệ,… BÀI 11. CHÈN BIỂU ĐỒ * Công dụng: Biểu đồ là đối tượng hình ảnh để biểu diễn các giá trị số của một bảng số liệu một cách trực quan, thuận lợi cho việc so sánh đánh giá. * Thực hiện: Một biểu đồ được chèn dựa trên bảng số liệu có sẵn thường được thực hiện theo các bước với sự trợ giúp của chương trình. 1. Chèn biểu đồ - Bôi đen bảng Dl (bôi đen cả tiêu đề hàng và tiêu đề cột) H20. Hộp thoại chọn kiểu biểu đồ - Insert\ Chart hoặc kích lên nút lệnh Chart wizard ()trên thanh lệnh chuẩn Standard à Xuất hiện hộp thoại Chart type (H20): Chọn kiểu biểu đồ + Tại Chart Type: Kích chọn nhóm biểu đồ + Tại Chart sub- type: Chọn biểu đồ cần dùng Next H21. Hộp thoại chọn lại nguồn Dl à Xuất hiện hộp thoại Chart Source Data (H21) Tại hộp thoại này thường ta không phải khai báo. Tuy nhiên nếu dòng địa chỉ tại Data range không khớp với với vùng bảng Dl đã chọn ta có thể xoá và khoanh chọn lại từ bảng Dl. Next H22. Khai báo các tuỳ chọn trên BĐ à Xuât hiện hộp thoại Chart Options (H22): Khai báo các tuỳ chọn với biểu đồ (tuỳ loại biểu đồ mà có thể có 3 hoặc 6 thẻ lệnh): + Titles: Tên biểu đồ và tên các trục toạ độ + Axes: Tuỳ chọn ẩn/ hiện các trục toạ độ (biểu đồ cột) + Gridlines: Tuỳ chọn ẩn/ hiện các đường gióng giá trị (với biểu đồ cột) + Legend: Tuỳ chọn ẩn/ hiện khung ghi chú + Data Labels: Tuỳ chọn ẩn/ hiện các giá trị hoặc tên nhãn của giá trị trên biểu đồ + Data Table: Tuỳ chọn ẩn hiện bảng số liệu kèm theo biểu đồ. H23. Chọn vị trí đặt biểu đồ Next à Xuất hiện hộp thoại Chart Location (H23): Chọn vị trí đặt biểu đồ trên bảng tính: F As new sheet: Đặt biểu đồ trên bảng tính mới F As object in: Đặt biểu đồ trên bảng tính hiện tại Finish 2. Định dạng biểu đồ a. Khai báo lại một trong bốn bước chèn biểu đồ - Kích chọn biểu đồ - Chọn Menu Chart: + Chart Type…..: Chọn lại biểu đồ + Source Data…: Chọn lại giá trị biểu diễn trên biểu đồ + Chart Options…: Khai báo lại các tuỳ chọn H24. Hộp lệnh khai báo mầu nền đưòng viền Tên hộp thoại T.ư với vùng Dl ta khai báo định dạng Vùng lệnh khai báo đường viền Vùng lệnh khai báo mầu nền + Location…: Chọn lại vị trí đặt biểu đồ b. Định dạng mầu nền, đường viền của biểu đồ - Trên mỗi biểu đồ luôn có nhiều vùng Dl khác nhau, nếu để chuột lên vùng Dl đó trong giây lát sẽ hiện tên vùng cạnh trỏ chuột: + Chart Area: Vùng bao chứa toàn bộ biểu đồ + Chart Title: Tiêu đề của biểu đồ + Plot Area: Vùng chứa nguyên hình biểu đồ + Wall (3-D): Nền (phông) với biểu đồ không gian 3 chiều + Legend: Khung ghi chú - Để thay đổi đường viền và mầu nền cho vùng Dl nào ta kích đúp chuột lên vùng Dl đó hoặc kích phải chuột lên vùng Dl đó rồi chọn lệnh Format…. (Tên vùng). Ví dụ: Format Chart Area à Xuất hiện hộp thoại định dạng đường viền, mầu nền, Font chữ,… tương ứng với vùng Dl đó (H24) + Patterns: Khai báo đường viền và mầu nền ² Border: Khai báo các tuỳ chọn với đường viền C Style: Chọn kiểu đường viền C Color: Chọn mầu đường viền C Weight: Chọn kích cỡ đường viền C Tích chọn Shadow: Tạo đường viền bóng C Round Corners: Uốn cong đường viền tại các góc ² Area: Khai báo các tuỳ chọn với mầu nền C Chọn mầu nền từ bảng mầu hoặc kích chọn Fill Effect để đổ mầu nền có nhiều mầu C None: Xoá mầu nền + Font: Khai báo Font chữ + Number: Khai báo các tuỳ chọn với giá trị số (nếu đó là vùng Dl có chứa số) OK * Xoá biểu đồ khỏi bảng tính - Kích chọn biểu đồ - Bấm phím Delete trên bàn phím BÀI 12. CHỨC NĂNG SẮP XẾP DỮ LIỆU VÀ LỌC TỰ ĐỘNG TRONG BẢNG 1. Sắp xếp Dl (Sort) Kích chọn cột Dl cần sắp xếp Kích chọn cột Dl để sắp xếp khi Dl chọn trong Sort by trùng nhau. Kích chọn để sắp xếp tăng dần Kích chọn để sắp xếp giảm dần Kích chọn để cố định dòng tiêu đề cột của bảng Kích chọn để sắp xếp cả dòng tiêu đề cột của bảng H25. Hộp thoại sắp xếp Dl * Công dụng: Thay đổi trật tự của các ô trong cột theo chiều tăng dần hoặc giảm dần để các Dl giống nhau được di chuyển về liền kề nhau thành một nhóm, tạo thuận lợi cho việc tìm kiếm và tra cứu trong bảng. * Thực hiện: - Bôi đen bảng DL - Data\ Sort….: à Xuất hiện hộp thoại Sort + Tại Sort by: Chọn cột Dl cần sắp xếp + Tại Then by: Chọn cột Dl để sắp xếp khi Dl trong cột được chọn tại Sort by trùng nhau + Tích chọn: F Ascending: Sắp xếp Dl theo chiều tăng dần F Descending: Sắp xếp DL theo chiều giảm dần + Tại My list has, chọn: F Header Row: Cố định dòng tiêu đề cột của bảng khi sắp xếp F No Header Row: Sắp xếp cả dòng tiêu đề cột của bảng * Cy: - Tối đa ta có thể chọn ba điều kiện tương ứng với ba cột để sắp xếp - Khi chọn Ascending: Nếu cột Dl được sắp xếp là số thì chương trình sẽ sắp xếp từ số nhỏ đến số lớn, nếu là chữ thì chương trình sẽ sắp xếp từ Aà Z. Ngược lại với trường hợp ta chọn Descending. - Với những bảng có tiêu đề cột, tại vùng lệnh My list has ta thường chọn Header Row để cố định dòng tiêu đề cột của bảng. Nếu không theo trật tự sắp xếp tăng dần dòng tiêu đề có thể bị di chuyển xuống cuối bảng. 2. Lọc Dữ liệu * Công dụng: Trích ra các mẫu tin (ô Dl trên các dòng) thoả mãn các điều kiện cho trước. * Thực hiện: - Bôi đen bảng - Data\ Filter\ AutoFilter Khi đó trên các tiêu đề cột của bảng xuất hiện các mũi tên. Để lọc Dl thoả mãn điều kiện trên cột nào ta kích chuột lên tiêu đề của cột đó và chọn điều kiện tương ứng từ danh sách. - Sau khi lọc chỉ những Dl thoả mãn điều kiện được hiển thị trên bảng, ta có thể sử dụng lệnh Copy hoặc lệnh Cut để sao chép hoặc di chuyển những Dl thoả man điều kiện thành lập một bảng mới. - Để hiển thị bảng đầy đủ trở lại, ta chọn: Data\ Filter\ Show All - Để huỷ bỏ các mũi tên trên bảng Dl và trở về dạng bảng ban đầu, ta chọn: Data\ Filter\ AutoFilter * Cy: - Với chức năng lọc AutoFilter mỗi lần lọc ta chỉ lọc được Dl thoả mãn điều kiện trên một cột. Để lọc Dl thoả mãn nhiều điều kiện trên các cột khác nhau ta phải lọc nhiều lần. Kích chọn toán tử so sánh H26. Hộp thoại tuỳ chọn lọc Dl Kích chọn hoặc nhập trực tiếp điều kiện - Với những điều kiện sử dụng các toán tử so sánh hoặc điều kiện kép trên cùng một cột, ta phải kích chọn mũi tên trên cột chứa điều kiện chọn lệnh Custom à Xuất hiện hộp thoại Custom AutoFilter: + Tại các ô bên trái của hộp thoại ta chọn các toán tử so sánh + Tại các ô bên phải của hộp thoại ta chọn hoặc nhập các giá trị so sánh Nếu điều kiện lọc là điều kiện kép và cùng nằm trên một cột ta có thể chọn And cho điều kiện “Và” (điều kiện đồng thời xảy ra), Or cho điều kiện “Hoặc” (điều kiện không đồng thời xảy ra). + Một số toán tử so sánh trong hộp thoại Custom AutoFilter: Toán tử so sánh Ý nghĩa Equals Bằng Does not Equal Không bằng 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 với, bắt đầu bẳng,… Does not begin with Không bắt đầu bằng, không bắt đầu với,…. Ends with Kết thúc bằng, kết thúc với Does not end with Không kết thúc bằng, không kết thúc với Contains Có chứa, bao gồm Does not Contain Không chứa, không gồm BÀI 13. THIẾT KẾ TRANG IN VÀ IN BẢNG TÍNH Chọn hướng in H27. Hộp thoại tuỳ chọn Khổ giấy và hướng in Chọn khổ giấy 1. Thiết kế trang in - File\ Page Setup à Xuất hiện hộp thoại Page Setup, chọn: * Page: Khai báo khổ giấy và hướng in - Tại Orientation: Chọn hướng in F Portrait: In dọc trang giấy F Landscape: In ngang trang giấy - Paper size: Chọn khổ (cỡ) giấy A4 (21cm x 29.7cm) * Margin: Khai báo lề trang - Top: Lề đỉnh Bottom: Lề đáy Left: Lề trái Right: Lề phải - Header: Khoảng cách từ tiêu đề đầu trang đến mép đỉnh của trang in Footer: Khoảng cách từ tiêu đề cuối trang đến mép đáy của trang in H28. Hộp thoại khai báo lề trang in - Center on page: Chọn căn bảng giữ liệu vào giữa trang in F Horizontally: Căn giữa theo chiều ngang Kích và nhập tiêu đề đầu trang Kích và nhập tiêu đề cuối trang H29. Hộp thoại tạo tiêu đề đầu trang cuối trang F Vertically: Căn giữa theo chiều dọc * Header/Footer: Tạo tiêu đề đầu trang/ cuối trang - Kích chọn Custom Header…..: Nhập nội dung tiêu đề đầu trang - Kích chọn Custom Footer….. :Nhập nội dung tiêu để cuối trang Đặt dấu chèn và khoanh chọn vùng in H30. Hộp thoại tuỳ chọn vùng in Chọn lặp lại tiêu đề cột Chọn lặp lại tiêu đề hàng * Sheet: Khai báo các tuỳ chọn vùng In - Đặt dấu chèn lên dòng soạn thảo Print Area: Khoanh chọn vùng bảng Dl nếu chỉ in một phần của bảng Dl (Một vài Ô, vài Hàng hoặc vài Cột) - Tại Print Titles: Khai báo lặp lại tiêu đề Hàng và tiêu đề Cột của bảng khi bảng tràn sang các trang kế tiếp hoặc xuống dưới + Đặt dấu chèn vào dòng soạn thảo Rows to repeat at Top: Kích chuột vào dòng tiêu đề cột của bảng để lặp lại tiêu đề cột khi bảng dài và tràn xuống trang phía dưới. Kích chọn tên máy in H31. Hộp thoại tuỳ chọn In Kích chọn trang in Kích chọn khổ giấy và hướng in Nhập số bản in + Đặt dấu chèn vào dòng soạn thảo Columns to repeat at Left: Kích chuột vào cột tiêu đề hàng của bảng để lặp lại tiêu đề hàng khi bảng quá rộng và tràn sang các trang bên phải. OK 2. In bảng tính - File\ Print (Ctrl+ P) à Xuất hiện hộp thoại Print: + Tại Name: Chọn tên máy in (nếu cần) + Tại Print range: Chọn các trang cần in F All: In cả tệp tin (Tất cả các trang) F Pages: In các trang tuỳ chọn. Nhập số trang cần in vào From…..to… + Tại Number of Copies: Nhập số bản in Tích chọn Collate (hoặc huỷ dấu tích) để chuyển đổi giữa chế độ in sách và chế độ in thường + Tại Print what, chọn: F Selection: Chỉ in vùng bảng tính được lựa chọn (trong thẻ lệnh Sheet của hộp thoại Page setup) F Active Sheet (s): In tất cả Dl trên Sheet đang lựa chọn F Entire workbook: In Dl trên cả tệp tin đang soạn thảo + Kích chọn Properties: Khai báo khổ giấy và hướng in Lưu ý: Ta phải khai báo khổ giấy và hướng in tương ứng với khổ giấy và hướng in trong hộp thoại Page Setup OK BẢNG TỔNG HỢP CÁC HÀM SỐ ĐÃ HỌC TRONG CHƯƠNG TRÌNH EXCEL CƠ BẢN Stt Dạng thức (Cú pháp) của hàm Công dụng =Sum(Number1,Number2,…,Number30) Tính tổng các giá trị số =Average(Number1,Number2,…,Number30) Tính trung bình cộng các giá trị số trong một phạm vi ô =Max(Number1, Number2,….,Number30) Tìm và trả về giá trị số lớn nhất trong các số đã cho =Min(Number1, Number2,….,Number30) Tìm và trả về giá trị số nhỏ nhất trong các số đã cho =Rank(Number,Ref,Order) So sánh và trả về thứ bậc của một giá trị số trong một dãy số cho trước. =If(Logical_test, Value_if_true,Value_if_false) Hoặc =If(Điều kiện,Gtrị1,Gtrị2) Nếu kết hợp với hàm And: =If(And(Đk1,Đk2,…,Đk30),Gtrị1,Gtrị2) Nếu kết hợp với hàm Or: =If(Or(Đk1,Đk2,…,Đk30),Gtrị1,Gtrị2) Là loại hàm Logic thường dùng thực hiện các phép thử điều kiện. Nếu điều kiện thoả mãn thì hàm nhận Giá trị 1 làm kết quả, ngược lại nếu điều kiện không thoả mãn thì hàm nhận Giá trị 2 làm kết quả. =Count(Value1,Value2,….Value30) Đếm ra số ô chứa giá trị số trong một phạm vi ô =CountA(Value1,Value2,….Value30) Đếm ra số ô không trống (ô chứa giá trị số hoặc chữ) trong một phạm vi ô =CountBlank(Value1,Value2,….Value30) Đếm ra số ô trống trong một phạm vi ô =Countif(Range,Criteria) Đếm ra số ô chứa giá trị thoả mãn điều kiện cho trước =Vlookup(Lookup_value,Table_array,Col_index_num,Lookup_type) Là loại hàm tìm kiếm và lấy giá trị từ bảng phụ lên bảng chính khi giá trị trên bảng phụ được ghi trên Cột =Hlookup(Lookup_value,Table_array,Row_index_num,Lookup_type) Là loại hàm tìm kiếm và lấy giá trị từ bảng phụ lên bảng chính khi giá trị trên bảng phụ được ghi trên Hàng =Left(Text,Num_chars) Dùng để trích ra n ký tự từ bên Trái của một xâu ký tự =Right(Text,Num_chars) Dùng để trích ra n ký tự từ bên Phải của một xâu ký tự =Mid(Text,Start_num,Num_chars) Dùng để trích ra n ký tự trong một xâu ký tự (trích từ trái sang phải) =Year(Serial_number) Trích ra giá trị Năm trong chuỗi Dl thời gian dạng Ngày, tháng, năm =Month(Serial_number) Trích ra giá trị Tháng trong chuỗi Dl thời gian dạng Ngày, tháng, năm =Day(Serial_number) Trích ra giá trị Ngày trong chuỗi Dl thời gian dạng Ngày, tháng, năm =Date(Year,Month,Day) Trả về định dạng thời gian dạng Ngày Tháng Năm của các giá trị số =Today() Trả về giá trị thời gian của ngày tháng hiện tại theo thiết lập của đồng hồ hệ thống máy tính =Now() Trả về gia trị thời gian ở thời điểm hiện tại theo thiết lập của đồng hồ hệ thống =Int(Number) Trích ra phần nguyên của một giá trị thập phân dương hoặc làm tròn đến số nguyên gần nhất trước dấu thập phân nếu là một số thập phân âm. =Mod(Number,Divisor) Trích ra phần dư của một phép chia có dư =Value(Text) Trả về định dạng số cho một giá trị số có định dạng Text The End!