3 EXCEL

MỤC LỤC

4.1. GIỚI THIỆU EXCEL ............................................................................ 2

4.1.1. Các thao tác với bảng tính ............................................................................ 2 4.1.2. Cấu trúc bảng tính ......................................................................................... 3 4.1.3. Các kiểu dữ liệu ............................................................................................. 3 4.1.4. Các kiểu tham chiếu ...................................................................................... 4



4.2. CÁC THAO TÁC VỚI BẢNG TÍNH ................................................... 5

4.2.1. Di chuyển trong bảng tính và giữa các bảng tính ......................................... 5 4.2.2. Chọn các ô trong bảng tính ........................................................................... 5 4.2.3. Sao chép, chuyển dữ liệu ............................................................................... 5 4.2.4. Nhập dữ liệu vào bảng tính ........................................................................... 6 4.2.5. Xoá, sửa dữ liệu............................................................................................. 6 4.2.6. Chèn ô, hàng, cột ........................................................................................... 6 4.2.7. Xóa ô, hàng, cột ............................................................................................. 7



4.3. ĐỊNH DẠNG BẢNG TÍNH .................................................................. 8

4.3.1. Thay đổi độ rộng cột, chiều cao hàng ........................................................... 8 4.3.2. Đặt màu nền .................................................................................................. 8 4.3.3. Vẽ khung ........................................................................................................ 8 4.3.4. Định dạng số ................................................................................................. 9 4.3.5. Định dạng văn bản ........................................................................................ 9 4.3.6. Canh dữ liệu trong ô ..................................................................................... 9



4.4. SỬ DỤNG HÀM TRONG EXCEL ..................................................... 11

4.4.1. Khái niệm .................................................................................................... 11 4.4.2. Một số hàm thường dùng ............................................................................. 11



4.5. QUẢN TRỊ DỮ LIỆU .......................................................................... 16

4.5.1. Khái niệm về cơ sở dữ liệu: ......................................................................... 16 4.5.2. Sắp xếp dữ liệu: ........................................................................................... 16 4.5.3. Lọc, rút trích dữ liệu: .................................................................................. 17 4.5.4. Các dạng tiêu chuẩn: .................................................................................. 20



4.6. HOÀN THIỆN TÀI LIỆU ................................................................... 23

4.6.1. Các thiết lập trang in: ................................................................................. 23



4.6.2. Xem trước khi in: ......................................................................................... 26 4.6.3. In tài liệu: .................................................................................................... 27



4.1. GIỚI THIỆU EXCEL + Khởi động vào Excel Thực hiện Click chuột vào nút Start  Program Files  Microsoft Excel hoặc Click chuột vào biểu tượng Microsoft Excel trên màn hình Desktop. Kết quả sẽ được một màn hình giao diện như sau (Hình 4.1):

Thanh công định dạng Formatting Thanh tiêu đề Thanh thực đơn Thanh công cụ chuẩn Standard



Thanh công thức



Thanh cuộn



Vùng lập bảng tính

Các bảng tính Thanh trạng thái



Hình 4.1. Màn hình giao diện bảng tính Excel 4.1.1. Các thao tác với bảng tính  Thanh công thức: - Hiển thị toạ độ (địa chỉ), nội dung của ô hiện hành. Nếu ô hiện hành chứa kết quả của một phép tính, công thức của phép tính đó sẽ được hiện lên cho phép thay đổi, sửa chữa...  Thanh trạng thái: - Thông báo các chế độ hoạt động của bàn phím: Numlock, Caplock... - Thông báo các chế độ làm việc của Excel:



+ Ready: Sẵn sàng chờ nhập dữ liệu hoặc thực hiện lệnh. + Edit: Chế độ chỉnh sửa dữ liệu. + Point: Tham chiếu đến một địa chỉ ô. + Enter: Đang nhập dữ liệu vào ô.  Vùng làm việc: (cửa sổ bảng tính) - Hiển thị một phần bảng tính với những đường kẻ lưới phân cách các ô.  Con trỏ ô: - Trong mọi thời điểm luôn tồn tại một con trỏ ô. Con trỏ ô có thể di chuyển khắp bảng tính, dùng chỉ ra ô sẽ chịu tác động bởi các lệnh của Excel. Ô có con trỏ gọi là ô hiện hành, địa chỉ và nội dung ô hiện hành luôn hiện trên thanh công thức.  Các nhãn hàng, nhãn cột: - Hiện các tên cột và các số thứ tự dòng của bảng tính. Cho phép người sử dụng dựa vào đó xác định chính xác địa chỉ các ô. 4.1.2. Cấu trúc bảng tính  Sổ làm việc: (Workbook) - Một Workbook gồm nhiều nhất 255 bảng tính, mỗi bảng đều được phân biệt bởi từ Sheet1 đến Sheet 255 (ngầm định là 3). Mỗi Workbook được lưu trên đĩa dưới một file có đuôi XLS.  Bảng tính: (Sheet) - Một bảng tính gồm các ô, hàng, cột. Cho phép nhập dữ liệu và thực hiện các yêu cầu tính toán.  Cột: (Column) - Tập hợp các ô trong bảng tính được liệt kê theo chiều dọc. Có tổng cộng 256 cột trong một bảng. Mỗi cột được gán một tên theo thứ tự chữ cái (A - Z, AA AZ, BA - BZ, IV).  Hàng: (Row) - Hàng là một tập hợp các ô theo chiều ngang. Có tổng cộng 65536 hàng trong một bảng tính. Mỗi hàng được gán một số thứ tự từ 1 đến 65536.  Ô: (Cell) - Ô là giao điểm giữa hàng và cột. Mỗi ô có một địa chỉ duy nhất được xác định bởi tên cột và số thứ tự hàng (ví dụ: A5; B9...). 4.1.3. Các kiểu dữ liệu  Kiểu số: - Là kiểu dữ liệu được sử dụng chủ yếu trong bảng tính, bao gồm: + Các kí số: 0 - 9.



+ Các dấu + , - , chấm thập phân (.), phẩy ngăn cách hàng nghìn (,).  Kiểu chữ: (văn bản, chuỗi) - Dùng thể hiện văn bản trong bảng tính, bao gồm tất cả các kí tự: 0 - 9, a z...  Kiểu ngày, giờ: - Dùng quản lí những thông tin về thời gian. Kiểu ngày, giờ phải được nhập theo dạng thức đã lựa chọn trong Control Panel của Windows. Nếu nhập không đúng, Excel sẽ tự động chuyển sang kiểu chuỗi.  Kiểu công thức: - Phải bắt đầu bởi dấu = hay +. - Kết quả công thức được hiển thị trong ô, bản thân công thức sẽ xuất hiện trên thanh công thức. - Một công thức bao gồm các toán tử và các toán hạng. a) Toán hạng: - Bao gồm các dữ liệu kiểu số, kiểu chuỗi (phải đặt trong cặp nháy kép), kiểu ngày, giờ, địa chỉ ô, các hàm. b) Toán tử: - Toán tử tính toán: + , - , * , / , ^ (luỹ thừa): dùng với dữ liệu kiểu số. - Toán tử liên kết: & (dùng liên kết chuỗi) - Toán tử so sánh: = , , > , = , =5, “Nhanh”, “Chậm”) Kết quả là: {=Nhanh} 2. SUMIF(vùng 1, “điều kiện”, vùng 2): Tính tổng các ô trong vùng 2 tương ứng với các ô vùng 1 đã thoả mãn điều kiện. Dữ liệu trong vùng B4:B7 là 3, 6, 8, 6 Dữ liệu trong vùng C4:C7 là 6, 9, 9, 4 =SUMIF(B4:B7,“>5”,C4:C7) Kết quả là: {=22}



Hình 4.9 Minh họa ví dụ về sử dụng nhóm hàm điều kiện



Tính tổng giá trị các ô trong vùng C4:C7 sao cho các giá trị tương ứng trong vùng B4:B7 phải lớn hơn 5, như vậy các ô có giá trị 6, 8, 6 trong vùng B4:B7 thoả mãn điều kiệnl là lớn hơn 5, tương ứng với các ô có giá trị 9, 9, 4 trong vùng C4:C7,  kết quả tổng bằng 22. 3. COUNTIF(vùng, "điều kiện"): Đếm các ô thoả mãn điều kiện trong vùng. (Lấy dữ liệu của B4:B7 ở ví dụ trên – Hình 4.9) =COUNTIF(B4:B7, “5, E3=”A”), “Đ”, “H”) Nếu B3 lớn hơn 5 và (AND) E4 là A thì cho kết quả là “Đ”, ngược lại kết quả là “H”. 2. OR(các biểu thức điều kiện): Trả về giá trị “Hoặc” logic (True hay False) của các biểu thức điều kiện. =IF(OR(B35, NOT(C3=0)), “Đ”, “H”) Nếu B3 lớn hơn 5 và (AND) C3 không (NOT) bằng 0 thì kết quả là “Đ”, ngược lại sẽ cho kết quả là “H”.



Hình 4.10. Minh họa ví dụ về sử dụng nhóm hàm logic 4.4.2.7. Nhóm hàm tìm kiếm: 1. VLOOKUP(x, vùng tham chiếu, n, 0): Tìm giá trị x ở cột thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở cột thứ n. (Hình 4.11) Dựa vào bảng mã hàng điền tên thích hợp vào cột tên hàng của bảng phiếu giao hàng. Lập công thức cho ô C13: = VLOOKUP(B13, $A$2:$B$5, 2, 0) {kết quả=Sắt} Tìm một giá trị bằng giá trị của ô B13 trong cột thứ nhất của vùng A2:B5 và lấy giá trị tương ứng ở cột thứ 2 (kết quả =Sắt). .



Hình 4.11. Minh họa ví dụ về sử dụng nhóm hàm tìm kiếm



* Trong công thức nên đặt giá trị tuyệt đối cho địa chỉ vùng để không bị thay đổi khi sao chép.



2. HLOOKUP(x, vùng tham chiếu, n, 0): Tìm giá trị x ở dòng thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở dòng thứ n. (Xem hình bên): Dựa vào bảng giá để điền vào cột giá của bảng phiếu giao hàng. Lập công thức cho ô D13: =HLOOKUP(B13, $A$7:$D$8, 2, 0) {=100} Tương tự như hàm VLOOKUP nhưng tìm theo dòng thay vì cột



4.5. QUẢN TRỊ DỮ LIỆU 4.5.1. Khái niệm về cơ sở dữ liệu: * Trên một cơ sở dữ liệu dạng bảng của Excel ta có thể: Lọc (Filter), xoá (Delete) rút trích (Extract) những dòng dữ liệu thoả mãn một tiêu chuẩn nào đó. Để thực hiện các thao tác này cần tạo ra những vùng sau: 1. Vùng dữ liệu (Database): Là vùng cơ sở dữ liệu gồm ít nhất 2 dòng (Row). Dòng đầu tiên chứa các tiêu đề cột (Field name), các dòng còn lại chứa dữ liệu gọi là mẩu tin (Record). 2. Vùng tiêu chuẩn (Criteria): Là vùng tiêu chuẩn chứa điều kiện để tìm kiếm, xoá, rút trích hay trích lọc. Vùng này gồm ít nhất 2 dòng. Dòng đầu chứa tiêu đề (Field name), các dòng còn lại chứa điều kiện.



Hình 4.12. Minh họa về cơ sở dữ liệu



3. Vùng rút trích (Extract): Là vùng trích dữ liệu chứa các mẩu tin của vùng dữ liệu (Database) thoả điều kiện của vùng tiêu chuẩn. Vùng rút trích (Extract) cũng có dòng đầu tiên chứa các tiêu đề muốn rút trích.



4.5.2. Sắp xếp dữ liệu: - Đưa con trỏ ô vào vùng dữ liệu cần sắp xếp. - Thực hiện Data / Sort, hộp thoại Sort xuất hiện: - Chọn cột cần sắp xếp trong hộp Sort by. Chọn Ascending để sắp xếp tăng dần. Chọn Descending để sắp xếp giảm dần. - Nếu muốn sắp xếp cho 2 hay 3 cột cùng lúc, chọn chúng trong các hộp Then by còn lại. - Chọn Header row để xác nhận chỉ sắp xếp các dòng chứa thông tin (các bản ghi) trong vùng dữ liệu (không sắp xếp dòng tiêu đề). - Chọn No header row để sắp xếp cả dòng tiêu đề. - Nhấn OK.



Hình 4.13. Hộp thoại xắp xếp dữ liệu



4.5.3. Lọc, rút trích dữ liệu: - Khi muốn tìm kiếm hay liệt kê các mẩu tin (Record) theo một yêu cầu nào đó. Trong trường hợp này ta dùng phương pháp lọc (Filter) hoặc phương pháp rút trích dữ liệu (Extract). 4.5.3.1. Lọc tự động: - Đưa con trỏ ô vào vùng cần lọc (hoặc chọn vùng cần lọc). - Thực hiện Data\Filter\Autofilter. - Khi đó các nút mũi tên sẽ xuất hiện tại tiêu đề các cột trong vùng cơ sở dữ liệu.



Hình 4.14a. Minh họa ví dụ lọc dữ liệu tự động



- Nhấn chuột vào nút mũi tên để chọn các giá trị của cột cần xác định điều kiện lọc. Khi đó các mẩu tin thoả điều kiện sẽ được hiển thị còn các mẩu tin không thoả sẽ bị dấu (hide) đi. - Muốn hiển thị lại các mẩu tin, chọn Data\Filter\Show all. - Muốn bỏ chế độ lọc, chọn Data\Filter\Autofilter một lần nữa. * Trong danh sách còn có những lựa chọn khác: - All: Hiện trở lại tất cả các bản ghi. - Top 10: Xuất hiện hộp thoại Top 10 AutoFilter cho phép lọc ra một số bản ghi chứa giá trị cao (Top) hoặc thấp (Bottom).



Hình 4.14b. Hộp thoại thiết lập lọc dữ liệu theo các chỉ tiêu lựa chọn có sẵn + Chọn Top hay Bottom trong hộp thứ nhất. + Thay đổi số bản ghi cần lấy trong hộp thứ hai. + Hộp thứ ba cho phép xác định số bản ghi cần lấy theo số đếm bình thường (Items) hay theo đơn vị phần trăm (Percent).



- Custom: Xuất hiện hộp thoại Custom AutoFilter cho phép lọc theo các điều kiện phức hợp.



Hình 4.14c. Hộp thoại đặt lọc dữ liệu theo điều kiện * Nếu có một điều kiện lọc thì chỉ cần sử dụng hai hộp danh sách bên trên như sau: + Chọn một toán tử so sánh trong hộp danh sách bên trái: - equals = - does not equal - is greater than > - is greater than or equal to >= - is less than 6 - Các toán tử so sánh: > : Lớn hơn >= : Lớn hơn hoặc bằng : Không bằng Hình 4.17b. Minh họa ví dụ về sử dụng Ví dụ: Cho biết danh sách các các tiêu chuẩn để trích rút dữ liệu sinh viên có điểm văn > 6. 4.5.4.4. Tiêu chuẩn công thức: - Ô điều kiện có kiểu công thức, trường hợp này cần lưu ý 2 yêu cầu sau:



+ Ô tiêu đề của vùng tiêu chuẩn phải khác với tất cả các tiêu đề của vùng dữ liệu. + Trong ô điều kiện phải lấy địa chỉ của ô trong mẩu tin đầu tiên (sau dòng tiêu đề hay là dòng thứ 2 của vùng dữ liệu) để so sánh (ví dụ: Các ô B3, C3, D3). Ví dụ: Trích lọc danh sách các sinh viên có tổng điểm > 15. Tổng =Sum(B3:D3)>15 Trong vùng điều kiện tên tiêu đề đặt là Tổng (khác với các tiêu đề vùng dữ liệu), công thức trong ô điều kiện được xác lập là =Sum(B3:D3)>15. Kết quả như hình bên. Hình 4.17c. Minh họa ví dụ về sử dụng các tiêu chuẩn để trích rút dữ liệu



4.5.4.5. Tiêu chuẩn liên kết: - Có thể lọc, xoá hay rút trích các mẩu tin trong vùng dữ liệu bằng phép giao (And) hay phép hợp (Or) của nhiều điều kiện khác nhau. - Phép AND: Nếu các ô điều kiện cùng dòng. - Phép OR: Nếu các ô điều kiện khác dòng. Ví dụ 1: Trích lọc danh sách sinh viên có điểm Văn > 5 và điểm Địa > 5. Văn >5 Văn >6 6 Địa >5



Ví dụ 2: Trích lọc danh sách sinh viên có điểm Văn lớn hơn hoặc bằng 6.



Hình 4.18. Minh họa ví dụ về sử dụng các tiêu chuẩn liên kết AND, OR để đặt lọc dữ liệu



4.6. HOÀN THIỆN TÀI LIỆU 4.6.1. Các thiết lập trang in: Thực hiện File/Page Setup, xuất hiện hộp thoại Page Setup gồm 4 tab:



Hình 4.19a. Hộp thoại thiết lập trang in - về khổ giấy, hướng giấy 4.6.1.1. Tab Page: - Portrait: In bảng tính theo chiều dọc trang giấy. - Landscape: In bảng tính theo chiều ngang trang giấy. - Paper size: Chọn khổ giấy (thường là khổ A4). - Print quality: Chọn độ phân giải khi in (độ phân giải càng cao bản in càng đẹp). - First page number: Xác định số trang bắt đầu. 4.6.1.2. Tab Margins: (Xác định các lề cho trang in)



Hình 4.19b. Hộp thoại thiết lập trang in - về lề - Top: Thay đổi lề trên trang giấy. - Bottom: Thay đổi lề dưới trang giấy. - Left: Thay đổi lề trái trang giấy. - Right: Thay đổi lề phải trang giấy. - Header: Xác định khoảng cách cho tiêu đề đầu. - Footer: Xác định khoảng cách cho tiêu đề cuối. - Horizontally: Căn bảng tính giữa trang giấy theo chiều ngang. - Vertically: Căn bảng tính giữa trang giấy theo chiều dọc. 4.6.1.3. Tab Header/Footer: (Tạo tiêu đề đầu và cuối cho trang in)



Hình 4.19c. Hộp thoại thiết lập trang in - về tiêu đề Sử dụng hộp Header và hộp Footer để chọn một kiểu tiêu đề có sẵn. Hoặc có thể nhấn vào nút Custom Header hay nút Custom Footer để tự tạo tiêu đề đầu và tiêu đề cuối. Hộp thoại Header hay Footer tương ứng sẽ hiện ra gồm 3 phần: + Left Section: Phần bên trái tiêu đề. + Center Section: Phần giữa của tiêu đề.



Hình 4.20. Hộp thoại thiết đặt tiêu đề trên của trang in (Header) + Right Section: Phần bên phải tiêu đề. - Người sử dụng có thể nhập bất kì văn bản nào cho mỗi phần. Ngoài ra có thể nhấn vào các nút phía trên của hộp thoại để thêm những thông tin đặc biệt vào các phần của tiêu đề: Thay đổi font chữ. Chèn số trang. Chèn tổng số trang. Chèn ngày tháng năm hiện hành. Chèn giờ hiện hành. Chèn tên file. Chèn tên bảng tính. 4.6.1.4. Tab Sheet:



Hình 4.21. Hộp thoại thiết lập các thuộc tính cho trang in - Print area: (Vùng in) Nhập địa chỉ vùng cần in vào hộp. - Rows to repeat at top: Chọn hàng cần lặp lại trên đỉnh mỗi trang in. - Columns to repeat at left: Chọn cột cần lặp lại bên trái mỗi trang. - Gridlines: Xác nhận in cả các đường lưới. - Black and while: Chỉ in hai màu đen và trắng. - Draft quality: In chất lượng thấp nhưng giảm được thời gian in. - Row and column headings: In cả các nhãn hàng, nhãn cột. - Down, then over: In các trang theo thứ tự từ trên xuống dưới. - Over, then down: In các trang theo thứ tự từ trái sang phải. 4.6.2. Xem trước khi in: Chọn File/Print Preview hoặc nhấn nút Print Preview trên thanh công cụ. Màn hình Preview hiện ra cho phép xem toàn thể từng trang bảng tính.



Hình 4.22. Màn hình kết hiển thị trang in khi thực hiện lện xem trước khi in Sử dụng các nút: - Next: - Previous: - Zoom: - Print: - Setup: - Margins:



Tới trang tiếp theo. Về trang trước. Phóng to / thu nhỏ trang in. In. Mở hộp thoại Page Setup. Làm xuất hiện các dấu định lề quanh trang giấy. Dùng chuột kéo các dấu này để thay đổi các lề của trang. - Page Break Preview: Cho phép thay đổi dấu ngắt trang bằng cách kéo chuột. - Close: Thoát khỏi chế độ xem trước khi in. - Help: Hiện màn hình trợ giúp.



4.6.3. In tài liệu: Thực hiện File/Print hoặc chọn biểu tượng Print xuất hiện hộp thoại Print:



trên thanh công cụ,



Hình 4.23. Hộp thoại thiết lập máy in và trang in - Chọn kiểu máy in trong hộp Name. - Chọn All để tất cả các trang. - Chọn Page(s): chỉ in những trang được chỉ định trong hai hộp From và To. - Selection: Chỉ in vùng được chọn (bôi đen). - Entire workbook: In tất cả các bảng tính trong file. - Active sheet(s): Chỉ in bảng tính hiện hành. - Number of copies: Xác định số bản sao. - Collate: Cho phép in từng bộ bản sao riêng biệt. Nhấn OK để thực hiện in.