Bài giảng phần 3: Microsoft excel

PHẦN 3: MICROSOFT EXCEL CHƯƠNG I: SƠ LƯỢC VỀ EXCEL I. GIỚI THIỆU: Excel là một trong những phần mềm bảng tính điện tử được sử dụng rộng rãi trong công tác văn phòng và quản lý. Chức năng chính của bảng tính là thực hiện các tính toán. Ngoài ra, bảng tính còn được sử dụng thao tác trên các dữ liệu, tổ chức lưu trữ dữ liệu và phân tích dữ liệu theo yêu cầu của người sử dụng. Bên cạnh đó, excel còn tổ chức biểu diễn dl dưới dạng đồ thị. II. GIAO DIỆN CỦA EXCEL: Thanh nội dung của ô Địa chỉ ô hiện hành Worksheet (trang bảng tính) 255 worksheet Tên Hàng (row) 165536 hàng Tên Cột (column) 256 cột AZ, AAAZ,…,HAHZ,IAIV WorkBook (.xls) Ô (cell) B8 III. CÁC THAO TÁC CƠ BẢN: 1. Tạo mới một tập tin: File \ New (Ctrl – N) 2. Mở 1 tập tin có sẵn trên đĩa: File \ Open (Ctrl – O). 3. Chọn khối và đối tượng: 4. Điều chỉnh Kích thước dòng, cột: III. CÁC THAO TÁC CƠ BẢN (tt): 5. Kiểu dữ liệu – cách nhập và chỉnh sửa dl: Kiểu chuỗi: A – Z, a – z, 0 – 9 và một số ký hiệu khác. Kiểu chuỗi tự động canh trái ô. Các phép toán liên quan: & là phép ghép chuỗi. Kiểu số: 0 –9.có thểnhập các ký hiệu như tiền tệ, %, phân cách hàng ngàn, phân cách thập phân, dạng khoa học đi kèm những con số nhưng phải theo đúng dạng của window. Kiểu số tự động canh phải ô. Các phép toán: +, -, *, /, (), ^ (lũy thừa), &, , >=, =, . III. CÁC THAO TÁC CƠ BẢN (tt): 5. Kiểu dữ liệu(tt): Kiểu ngày giờ: thực chất là kiểu số, chỉ số thứ tự kể từ ngày 1/1/1900. Cách nhập ngày giờ được qui định trong Control Panel. Kiểu hiển thị ngày giờ được qui định trong Format \ Cells \ Number. Các phép toán: giống kiểu số. Kiểu công thức: đây là kiểu dl quan trọng nhất, nó bắt đầu bởi dấu =, sau đó là các phép toán hoặc công thức. Kiểu logic: TRUE, FALSE. III. CÁC THAO TÁC CƠ BẢN (tt): . Nhập và sửa dữ liệu: Nhập dữ liệu: Chỉnh sửa dữ liệu: C1: nhấn F2. C2: chỉnh sửa trên formular bar. 6. Đổi tên Sheet: Chọn sheet cần đổi tên. Fortmat \ Sheet \ Rename. (click phải chọn Rename). 7. Lưu workbook: (File \ Save) 8. Thoát cửa sổ Excel (File \ Exit) CHƯƠNG II: CÔNG THỨC & HÀM I. KHÁI NIỆM: 1. Địa chỉ ô trong công thức: Địa chỉ tương đối: địa chỉ tương đối của tham chiếu ô trong công thức là địa chỉ sẽ tự động thay đổi khi ta sao chép công thức sang vị trí khác. (A5) Địa chỉ tuyệt đối: là địa chỉ không thay đổi khi ta sao chép công thức sang vị trí khác. ($A$5). Địa chỉ hỗn hợp: là loại địa chỉ trong đó chỉ cố định dòng hoặc cột. ($A5, A$5) I. KHÁI NIỆM (tt): 2. Thông báo lỗi khi nhập sai công thức, hàm: #DIV/0! : giá trị chia cho 0. #N/A : (No value is Available) tham chiếu đến ô hay khối không chứa dl. Thường xảy ra trong các hàm dò tìm. #NAME? : sử dụng tên khối chưa được định nghĩa trong công thức. Thường do nhập sai tên hàm. #NULL! : lỗi này xảy ra khi bạn sử dụng một chức năng kết hợp 2 vùng dl nhưng thực tế chúng không thể kết hợp được. #NUM! : trị số không hợp lệ. #REF! : lỗi tham chiếu đến địa chỉ không hợp lệ. #VALUE! : lỗi khi bạn sử dụng sai kiểu dl hoặc giá trị trong công thức. Thường xảy ra khi bạn nhân một giá trị kiểu số cho một giá trị kiểu chuỗi. II.HÀM: (FUNCTIONS) 1. Hàm ngày giờ: Cách nhập ngày giờ tùy thuộc vào chế độ cài trong Control Panel DATE(year,month,date): trả về ngày ứng với year,month,date. DAY(serial_number): trả về ngày ứng với serial_number. MONTH(serial_number): trả về tháng ứng với serial_number. YEAR(serial_number): trả về năm ứng với serial_number II.HÀM: (FUNCTIONS) 1. Hàm ngày giờ (tt): TIME(giờ,phút,giây): trả về giờ, phút. HOUR(serial_number): trả về giờ ứng với serial_number MINUTE(serial_number): trả về số phút ứng với serial_number. SECOND(serial_number): trả về số giây ứng với serial_number WEEKDAY(“chuỗi ngày”,kiểu kq): trả về số thứ tự của ngày trong tuần ứng với “chuỗi ngày”. Kiểu kq: 1:kiểu mặc nhiên, hàm trả về 1(chủ nhật)->7(thứ bảy). 2: trả về 1(thứ 2)->7(chủ nhật) 3: trả về 0(thứ 2)->6(chủ nhật) NOW(): trả về ngày giờ hiện hành. TODAY(): Trả về ngày hiện hành II.HÀM: (FUNCTIONS) 2. Hàm toán học: ROUND(x,n): làm tròn số “x” đến kí số thứ n. n có thể (+) hoặc (-). Nếu là số dương thì hàm làm tròn từ dấu chấm thập phân sang phải, ngược lại sang trái.(n kí số). ABS(x): cho giá trị tuyệt đối của số “x”. INT(x): cho phần nguyên nhỏ hơn hoặc =x. (không làm tròn). MOD(x,y): cho số dư của phép chia số nguyên x cho y. SUM(x1, x2,…,xn): tính tổng của các số x1,x2,..xn PRODUCT(x1,x2,…,xn): hàm trả về kq của phép nhân liên tục từ x1->xn. với n): hàm chuyển số x thành chuỗi có dạng ứng với . REPLACE(chuỗi,m,n,): cắt chuỗi n ký tự bắt đầu từ ký tự thứ m và thay bằng chuỗi . II.HÀM: (FUNCTIONS) 5. Hàm luận lý: IF(,,): hàm trả về nếu có giá trị đúng; ngược lại, hàm trả về . Có thể sử dụng If lồng nhau. AND(,,…): cho kq là TRUE nếu tất cả các biểu thức logic là đúng; ngược lại, cho giá trị FALSE. OR(,,…):cho kq là TRUE nếu 1 trong các biểu thức logic là đúng, nếu tất cả đều sai thì hàm cho trị là FALSE. NOT(): cho kq là TRUE nếu là FALSE, ngược lại cho trị là FALSE. II.HÀM: (FUNCTIONS) 6. hàm dò tìm: CHOOSE(x,bt1,bt2,…,btn): trả về giá trị bt (với n,,[,TRUE/false]): hàm dùng giá trị tìm trên cột đầu tiên theo chiều từ trên xuống, nếu tìm thấy thì hàm trả về giá trị của ô nằm trên dòng tương ứng tại cột n của . Kiểu dò TRUE,1,không ghi: Cột đầu tiên trong bàng dò sx tăng dần Dò tìm xấp xỉ FALSE, 0: Cột đầu tiên trong bảng dò không cần sx Dò tìm chính xác II.HÀM: (FUNCTIONS) 6. hàm dò tìm (tt): HLOOKUP(,,[,TRUE/false]): hàm dùng giá trị tìm trên dòng đầu tiên của theo chiều từ trái sang phải, nếu tìm thấy thì hàm trả về giá trị của ô nằm trên cột tương ứng tại dòng n của . (các thông số tương tự như thông số của VLOOKUP). INDEX(,,): cho giá trị là ô nằm trên và tương ứng trong . Dòng đầu là dòng 1, cột đầu là cột 1. II.HÀM: (FUNCTIONS) 6. hàm dò tìm (tt): MATCH(,,[kiểu]): tìm trong , nếu tìm thấy hàm sẽ trả về một số chỉ số thứ tự của giá trị xuất hiện trong dãy. [kiểu]=1: là giá trị mặc nhiên có thể bỏ qua. Dãy sx tăng dần Dò tìm xấp xỉ [kiểu]=0: Dãy không cần sx Dò tìm chính xác [kiểu]=-1: Dãy sx giảm dần Dò tìm xấp xỉ II.HÀM: (FUNCTIONS) 7. Hàm tài chính: các đối số: rate: lãi suất vay tại từng thời điểm, hoặc phần trăm chiết khấu hoặc lợi tức trên vốn đầu tư. Nper: số lượng các kỳ (như tổng số kỳ thanh toán trong mỗi năm, mỗi tháng, mỗi ngày…) Pmt: số tiền trả cho mỗi kỳ. Pv: trị giá hiện thời, là giá trị tiền gộp cho đầu tư hay cho vay. Fv: trị giá tương lai là số tiền của việc đầu tư trong một khoảng thời gian ở tương lai. Type: hình thức thanh toán khi kết thúc hoặc bắt đầu kỳ. Có thể có giá trị là 0 hoặc là i , mặc định là 0. II.HÀM: (FUNCTIONS) 7. Hàm tài chính (tt) FV(rate, nper, pmt, pv, type): trả về giá trị tương lai của các khoảng đầu tư giống nhau trong nhiều chu kỳ. PV(rate, nper, pmt, fv, type): trả về giá trị hiện tại của các khoảng đầu tư giống nhau trong nhiều chu kỳ. RATE( nper, pmt, pv, fv, type): tính lãi suất tiền / tiền vay. PMT (rate, nper, pv, fv, type): tính số tiền phải trả hàng kỳ cho những khoảng nợ vay / số tiền nhận được hàng kỳ của những khoản cho vay. NPER (rate, pmt, pv, fv, type): trả về số chu kỳ phải trả lãi / nhận lãi của các khoản đầu tư. CHƯƠNG III: ĐỊNH DẠNG BẢNG TÍNH CANH LỀ CHO DỮ LIỆU: (Format \ Cells \ thẻ Alignment) II. ĐỊNH DẠNG FONT CHỮ:(Format \ Cells \ thẻ Font) III. ĐỊNH DẠNG SỐ:(Format \ Cells \ thẻ Number) III. ĐỊNH DẠNG SỐ (tt):(Format \ Cells \ thẻ Number) # : thay thế cho một ký số bất kỳ, số 0 không hiển thị. 0 : thay thế cho 1 ký số bắt buộc phải có. % : số với tỉ lệ % , : phân cách hàng ngàn. . : số với dấu chấm thập phân. [thẻ màu] [điều kiện] $ : tiền tệ “chuỗi văn bản” () hoặc - : số âm _( : khoảng cách lề *: lấp đầy các và canh phần bên trái dấu * sang lề trái và phần bên phải dấu * sang lề bên phải của ô. D , dd, ddd, dddd M,mm,mmm, mmmm Yy, yyyy A/P: am hay pm IV. KẺ KHUNG CHO DL:(Format \ Cells \ thẻ Border) V. TẠO NỀN (PATTERNS)(Format \ Cells \ Thẻ Patterns) VI. TẠO CHÚ THÍCH Ô:(Insert \ Comment) VII. SAO CHÉP DL: 1. Edit \ Past: 2. Edit \ Past Special: VIII. XÓA Edit \ Clear Edit \ Delete IX. SAO CHÉP HAY DI CHUYỂN CÁC SHEET: C1: S/d Menu: Edit \ Move or Copy Sheet. C2: s/d chuột. XI. CHÈN DL VỚI LỆNH INSERT: Chèn dòng: Insert \ Rows Chèn cột: Insert \ Columns Chèn ô: Insert \ Cells Chèn sheet: Insert \ WorkSheet CHƯƠNG IV: QUẢN TRỊ CƠ SỞ DỮ LIỆU TRONG EXCEL I. KHÁI NIỆM CSDL trong Excel là một bảng gồm nhiều cột gọi là trường (fields), và nhiều dòng (records). Mỗi trường có một tên duy nhất gọi là tên trường (fieldname), chính là dòng đầu tiên trong bảng. Khi tạo CSDL trong Excel cần lưu ý: Các mẩu tin trong CSDL nên thực hiện liên tục. Tên của các trường phải nhập trong từng ô riêng và phải là duy nhất trong worksheet. Không có ô trống trong các tên trường. II. SORT: Chức năng Sort cho phép bạn sx lại dl theo thứ tự tăng dần (ascending) hay giảm dần (descending) theo nd của cột. Cách thực hiện: Chọn 1 ô trong csdl hoặc chọn cả bảng dl. Data \ Sort  hộp thoại Sort III. FILTER: Lọc ra các mẫu tin thỏa một đk nào đó. 1. AutoFilter: Đưa con trỏ ô vào một ô bất kì trong csdl (hoặc đánh dấu chọn toàn bộ csdl. Data \ Filter \ Autofilter III. FILTER(tt): 2. AdvancedFilter: Cách lập bảng đk lọc: Nhập đk lọc vào dưới tên trường tương ứngcủa vùng tiêu chuẩn. Đk trên cùng một dòng  phép And Đk trên các dòng khác nhau  phép Or. Đk là ô trống: tất cả các mẩu tin đều thoã. Cách thực hiện: Lập bảng đk lọc. muốn đặt kq lọc tại sheet nào thì kích hoạt sheet đó. Data \ Filter \ AdvancedFilter V. SUBTOTAL: VI. CONSOLIDATE: