Bài giảng Excel 2003

TIN HỌC TRONG GIẢNG DẠY VÀ NGHIÊN CỨU KHOA HỌC Mã số: QGBB-511Số học phần: 3 (LT:1,5; TH: 1,5)Giảng viên: TS. Trần Văn Hùng TS. Đào Thái Lai Viện Chiến lược và Chương trình Giáo dụcPhần IV Excel 2003 4. 1 Khái quát về ExcelLà phần mềm công cụ trong bộ Microsoft officeThích hợp với công tác kế toán, văn phòngTính tương thích cao, làm cơ sở trung gian cho nhiều cơ sở dữ liệu Tổ chức dữ liệu dưới dạng bảng biểu như bảng lương, bảng kế toán, bảng thanh toán, bảng thống kê, dự toán. Nhiều công cụ toán học mạnh xử lý bảng biểu thống kê, tính toán tài chính, giải các bài toán logic và tối ưuCó nhiều công cụ mạnh như vẽ biểu đồ, hình minh họaCó nhiều công cụ hỗ trợ tự động hóa nhiều quá trình Có thể lập trình trong ExcelKhởi động Excel:	- Start  Programs  Microsoft office  Microsoft Excel 	- Nháy vào biểu tượng Excel trong Microsoft office shortcut bar4.2 Màn hình làm việc của Excel4.2 Màn hình làm việc của ExcelNameboxFormulaTitleTask PaneMenucolumnRowForrmattingScroll bar BorderTrang tínhStatus barDrawingStandard4.2 Màn hình làm việc của Excel Title bar: tiêu đề của bảng tính.Menu barThanh StandardThanh FormattingThanh Formula Thanh Drawing Workbook: cửa sổ chứa nội dung tệp. Tên mặc định là Book#. Phần mở rộng định sẵn là XLS. Row border, Row columnRow: hàng, đánh số thứ tự. Max – 65536 dòng Column: cột, đánh số A, B, ..,Z, AA, AB,.., IV. Max 256 cột. trị mặc định của cột: 9 ký tự.Ô (Cell): giao của dòng và cột. địa chỉ của ô: vd C6. Độ cao (Height) mặc định:12.75 Pt. Sheet: một trang tính. Workbook có mặc định 3 sheet, tối đa 255 Sheet. Điều chỉnh số Sheet trong Workbook bằng lệnh Tools/Options/General, chọn số lượng Sheet.Có thể điều khiển kiểu, cỡ Font ngầm địnhScroll bar: thanh cuộn dọc và ngangStatus bar: tình trạng hiện hành của hệ thống. 4.2 Màn hình làm việc của Excel 4.3 Tác nghiệp với dữ liệu Nhập dữ liệu 	Phần I Phần II Chú thích thêmLàm việc với hàng, cộtĐiền dãy số tự độngSao chép, xóa, Cut dữ liệu trong vùngNhập, sao chép công thứcSao chép với Paste specialDi chuyển vùng dữ liệu Hủy bỏ thao tác vừa thực hiệnĐiền dãy số tự động Là điền một dãy số vào một hàng ô hay một cột ô. Gõ giá trị bắt đầu vào ô đầu. Dùng lệnh Edit/Fill/Series Chọn dòng (cột)Chọn Type: Linear- cộng trị bước nhảy, Growth- nhân trị bước nhảy, Date – theo dạng ngày, AutoFill – điền tự độngStep Value: Trị số bước nhảy, Stop Value: Trị kết thúc Về AutoFill Demo4.4 Các kiểu dữ liệuKiểu số: chứa các số từ 0 đến 9 và dấu +, -Kiểu chuỗi (Text): Ký tự đầu tiên từ A đến Z và một số ký tự canh biên. Ký tự số trở thành ký tự chuỗi khi thêm vào trước nó dấu ‘Kiểu công thức: Ký tự đầu là = Trong ô hiển thị không phải công thức mà là giá trị của công thức. Các toán tử sử dụng trong công thức: cộng +, trừ -, nhân *, chia /, lũy thừa ^, phần trăm %, nối chuỗi &, so sánh: bằng =, không bằng ,.. Địa chỉ tương đối: ví dụ: A1, B2, C4, D3. Khi sao chép địa chỉ tham chiếu sẽ thay đổi theo phương, chiều và khoảng cách. vd: C1 chứa công thức = A1*B1. Khi sao chép sang ô C2 thì C2 sẽ = A2*B2Địa chỉ tuyệt đối. Có dạng $$. Khi sao chép nội dung trong ô sẽ giữ nguyênĐịa chỉ hỗn hợp: Khi 1 dấu $ không có trước hàng(cột). Tại vùng đích, địa chỉ tham chiếu sẽ bị thay đổi tương ứng theo hàng (cột).Di chuyển dữ liệu vùng: chọn vùng Edit/Cut  Chọn ô trái vùng đích  Edit  Paste (hoặc Ctrl+V)Dán giá trịDùng để sao chép một dãy công thức và chỉ dán những giá trị tại vùng đích Các bước Chọn dãy ô sao chép Di chuyển đến vị trí mới Nháy vào Edit  Paste special Trong hộp thoại Paste Special chọn ValueChọn value2.5 Các thao tác trên tệpLưu trữ Workbook với lệnh Save và Save As (Giống như trong Word) | biểu tượng Save.Mở tệp Workbook có sẵn: Lệnh File/Open hoặc biểu tượng Open. Truy cập vào ổ đĩa  thư mụcTạo một Workbook mới: Lệnh File New | biểu tượng New trên thanh công cụĐóng tệp Workbook: lệnh File/Close.Bảo vệ bảng tínhBảo vệ toàn bộ tệp: FileSave As Tools  General Options nhập Password và ConfirmBảo vệ Sheet: Tools  Protection  Protect SheetCơ sở dữ liệu trên bảng tínhF1:F2 vùng CriteriaA1: D8 Vùng DatabaseA10: D13 vùng ExtractTìm kiếm bản ghi thỏa mãn điều kiệnDi chuyển con trỏ vào vùng DatabaseThực hiện lệnh Data/ Filter/Avanced Filter Chọn Filter the list, in-Place Điền Đ/c vùng Database, vùng Criteria vào các hộp List Range và Criteria Range Đánh dấu các ô, đ/c sẽ tự động điền vào các hộp thoại trên KQ: chỉ hiển thị các ô thỏa mãn điều kiện. Muốn hiện lại toàn bộ: Data/Filter/Show AllRút các bản ghi thỏa mãn Đ/k sang vùng khácThực hiện giống như tìm kiếm bản ghi thỏa mã điều kiện. khác biệt ở chỗ trong hộp thoại Advanced FilterChọn Copy to Another location Vào Đ/c của vùng đích trong hộp Copy to Lựa chọn Unique Records only chỉ hiển thị 1 bản ghi giống nhau.Xóa các bản ghi thỏa mãn điều kiệnChỉ cần vùng dữ liệu và vùng CriteriaThực hiện các bước như tìm kiếm đối với tiêu chuẩn cần xóa.Nhận được các bản ghi cần xóa.Edit/Delete/Entire Row để xóa các dòng trênData/Filter/Show all để hiện lại những bản ghi không cần xóa.Các dạng vùng tiêu chuẩn Tiêu chuẩn số hay chuỗi: như vùng F1: F2 ở vd trên tiêu chuẩn so sánh: chứa toán tử và giá trị so sánh Tiêu chuẩn công thức: - Tiêu đề ô tiêu chuẩn phải  tên trong database - Trong ô điều kiện lấy đ/c ô đầu tiên để so sánh Liên kết tiêu chuẩn: khác cột – toán tử And khác dòng – toán tử Or= Left(B2,1)=“M” Lọc nhờ AutoFilterAutoFilter cho phép tự động lọc theo một tiêu chuẩn nhất định có sẵn trong Database.Dùng Data/Filter /AutoFilter. Xuất hiện trong mọi cột. nháy vào đó trong cột DON VI, xuất hiện một bảng chọn như ở cột E. ta có thể chọn các đơn vị, All, Top 10 hoặc CustomTính năng lập dàn trong Excel Excel cho phép bạn che dấu hay hiển thị chi tiết một bảng tính Chọn dòng (cột ) cần lập dàn Vào Data  Group and outline  Group Chọn Rows Nháy vào dấu để dấu hoặc mở rộng dàn.3. Một số hàm toán học trong ExcelExcel có một số hàm mẫu dùng tiện lợi và đơn giản VD: = A3+A4+A5 thay bằng Sum(A3:A5)Các hàm số học:	- ABS(N): cho giá trị tuyệt đối của biểu thức	- SQRT(N): căn bậc 2 của số N	- INT(N): cho phần nguyên của biểu thức	- PI(): cho giá trị của số Pi	- Các hàm: MODE, ROUND, AVERAGE, MAX, MIN, COUNTA(số ô chứa trị trong DS)3. Một số hàm toán học trong ExcelCác hàm ngày tháng:DAY, MONTH, YEARCác hàm Logic: IF (biểu thức Logic, biểu thức 1, biểu thức 2) AND (ĐK1, ĐK2,)OR (ĐK1, ĐK2,)NOT (Điều kiện)	4. Định dạng dữ liệu4.1 Thay đổi kích thước dòng và cột Đặt chuột vào đường ngăn cột (hàng) và rê chuột Đổi độ rộng nhiều cột (hàng): Lệnh Format/Column /Width hộp thoại Column xuất hiện. cho tham số cột (đối với hàng cũng tương tự) Định dạng dữ liệu số Định dạng dữ liệu sốMột số khi nhập vào ô được tự động canh lề phảiHiển thị phụ thuộc: Lớp (Category) và dạng. Các lớp gồm: Number, date, Time, Percent,Trong lớp lại có nhiều dạng như: M/D/YY, D-MMM-YY,Cách thực hiện định dạng: Lệnh Format  cell, chọn Number, chọn số chữ số sau dấu phảy, dấu ngăn cách 1000, kiểu biểu hiện số âm.Có thể dùng các nút Currency Style, Percentage Style, Comma Style, Increase/Decrease Decimal trên thanh Formatting để định dạng lại dữ liệu số4.4 Hiệu chỉnh vị trí của dữ liệu trong ô4.4 Hiệu chỉnh vị trí của dữ liệu trong ô Khung Horizontal: điều chỉnh dữ liệu theo chiều ngang: Center, Right, Fill (điền toàn bộ ô bởi ký tự có trong ô), Justify, Center Across Select.Khung Vertical: Điều chỉnh dữ liệu theo chiều dọc: Top, Bottom, Center, JustifyÔ kiểm Wrap text: cố dịnh độ rộng cột, dàn dữ liệu ra nhiều dòngÔ Shrink to fit: cố lèn dữ liệu vào ôMerge Cells: hợp các ô thành 1 ô.Orientation: quay dữ liệu4.5 Định dạng Font Font Type Font Style Font Size Underline ColorÔ Preview để xem qua hiển thị 4.6 Tạo các đường kẻ ô các đường viền: None, Outline, Inside Các kiểu nét vẽ các nét Border Màu đường kẻ4.6 Tạo các đường kẻ ôChọn vùng ô, lệnh Format/cells/trong hộp thoại Format cells chọn BorderNút outline: vẽ đường viền bao, Inside: vẽ các đường kẻ trong ô, none: hủy bỏ các đường kẻTám nút trong khung Border kẻ các đường ngang dọc, chéo trong vùng ô, ở giữa các nút là mẫu tác dụng của các nútKhung Style: chọn 1 trong 13 mẫu nét kẻ.Bật tắt lưới ô của bảng tính: Tools/Options/View dùng dấu kiểm để bật tắt Gridline. 4.7. Định dạng nền dữ liệuCó thể tô nền các ô theo mẫu tô và màu tô. Chọn vùng > Format/cells > chọn Patterns > xử lý hộp màu4.8 Sắp xếp dữ liệuSắp xếp dữ liệu trong khối ô đã chọnThực hiện theo hàng ngang hoặc dọcSắp xếp tối đa theo 3 khóaData/Sort > chọn khóa thư nhất/kiểu khóa (tăng giảm) >chọn khóa thứ 2/kiểu khóaNếu chỉ sắp xếp theo hàng hay cột thì chỉ cần vào Dât/Sort/Options > chọn Sort Top to Bottom (cho hàng) hay Sort Left to right (cho cột)5. Vẽ biểu đồXét vd về thu nhập của 1 đơn vị NN(đơn vị tính ngàn đồng)5.1 Các bước tạo biểu đồChọn vùng, VD chọn A1:B8Nháy vào biểu tượng Wizard trên thanh công cụNhận hộp thoại Chart Wizarrd – step 1 of 4 Chart TypeThực hiện theo từng bước để chọn: kiểu biểu đồ, các nhãn tiêu đề, thuyết minh, trục X và trục Y, Legend, chọn các màu và hướng quay cho trục X, YChọn nơi sẽ đặt biểu đồ và có thể di chuyển biểu đồ trong 1 Sheet.Sau đây là minh họa thực hiện các bước.5.1 Các bước tạo biểu đồ Chọn vùng (A1:B8) Nháy vào biểu tượng Wizard trên thanh công cụ Xuất hiện trang Chart Wizard – Step 1 of 4 – chart Type NextBước 1Bước 2Cho nguồn dữ liệu và cách sắp xếpBước 3Tên biểu đồTên trục xTên trục YBước 4 Chọn nơi đặt biểu đồ: tại Sheet hiện hành hoặc Sheet mới. Sau có thể dùng Kéo-thả chuột để di chuyển đến nơi cần thiết và chỉnh sửa6. In bảng tính ra giấyĐịnh dạng trang bằng lệnh File/ Page setup	- Mục Page để định chế độ in ngang hoặc dọc	- Mục Margine để định lề trang in	- Header/Footer: định tiêu đề đầu trang cuối tr.	- Mục Sheet: Lựa chọn in lưới (gridlines), vùng in (Print Area), Thứ tự trang in (Page Order) - không nên thay đổiTrước khi in nên xem qua bằng Print Preview