Bài giảng Tin học căn bản - Microsoft Excel

1TIN HỌC CĂN BẢN Phần 4: Microsoft EXCEL Chương 1 Giới Thiệu Chương 2 Định Dạng Dữ Liệu Chương 3 Dữ Liệu Trong Excel Chương 4 Sử Dụng Hàm (Function) Chương 5 Cơ Sở Dữ Liệu 2Chương 1: Giới Thiệu 1. Giới thiệu Microsoft Excel là trong những phần mềm của bộ Office hãng Microsoft. Là một chương trình xử lý bảng tính hiện nay đựơc sử dụng rất thông dụng. 2. Khởi động Start  Program  Microsoft Office  Microsoft Office Excel Hoặc nhấp đôi vào biểu tượng Excel trên màn hình Desktop.(Mở Microsoft Excel) 3Giao Diện Excel Là một màn hình gồm các thanh công cụ và một bảng tính gồm các cột và các dòng. Trong Excel 2007, một Worksheet chứa được 16,384 cột và 1,048,576 dòng, thay vì 256 cột và 65,536 dòng của phiên bản cũ. Một ô: Được định nghĩa bởi vị trí cột và vị trí dòng Ví dụ: A5 là cột A và dòng thứ 5. Các Sheet: Mỗi sheet là một bảng tính. Mặc định gồm 3 Sheet. 4Các Thao Tác Cơ Bản a. Lưu Tập Tin B1: File Save (Ctrl + S) B2: Chọn nơi chứa tập tin và lưu bình thường. b. Mở Tập Tin B1: File Open (Ctrl + O) B2: Chọn nơi chứa và tập tin cần mở Open. c. Thoát C1: Click nút Close C2: File Exit C3: Alt + F4. Nếu Tập tin chưa lưu thì sẽ xuất hiện bảng thông báo: Yes: Lưu No: Không lưu 5Các Thao Tác Trên Sheet a. Chèn Thêm Sheet InsertWordSheet b. Đổi Tên Sheet Chuột phải vào sheet Rename  Gõ tên mới. c. Xoá Sheet Chuột phải vào sheet Delete d. Di Chuyển Sheet Click vào Sheet, giữ chuột và di chuyển e. Sao chép Giữ phím Ctrl trong quá trình di chuyển. 6Các Thao Tác Trên Ô a. Chọn Ô Chọn nhiều ô: Quét đen các ô cần chọn Chọn nhiều ô không liên tục Giữ phím Ctrl, Click chọn các ô muốn chọn. b. Nhập (trộn) nhiều ô thành một. Quét các ô cần nhập  Click biểu tượng Merge and Center (chữ a nhỏ). c. Nhập Cho nhiều Ô cùng một DL Click chọn các ô cần nhập Nhập DL  Ctrl+Enter. d. Xoá DL: Phím Delete 7Các Thao Tác Trên Cột/Hàng a. Thay đổi kích thước B1: Đưa chuột vào đường phân cách giữa các chữ cái hoặc các số B2: Giữ chuột và kéo. b. Chèn thêm cột/hàng phía trước một cột/hàng B1: Click vào ô muốn chèn phía trước B2: Insert Columns/Rows c. Xoá Cột/Hàng B1: Chọn cột/hàng cần xoá B2: Edit Delete Columns/Rows 8Chương 2: Định Dạng Dữ Liệu 1. Định dạng Ký Tự B1: Quét khối các ô cần định dạng B2: Chọn Font, size, color, under line 2. Định dạng vị trí DL trong ô B1: Quét khối các ô  Chuột phải vào khối  Format cells. B2: Click Thẻ Alignment chọn kiểu vị trí Horizontal: Chiều ngang (Left, Center, Right) Vectical: Chiều dọc (Top, Center, Bottom) Orientation: Xoay dữ liệu trong ô. 9Chọn phông chữ Chọn kích thước chữ Chọn màu chữ Gạch chân chữ Xem trước Menu Format/Cells… Tab Font Chọn kiểu chữ 10 Căn dữ liệu chiều ngang ô Xuống dòng vừa độ rộng ô Định hướng văn bản Thu nhỏ chữ vừa kích thước ô Menu Format/Cells… Tab Alignment Căn dữ liệu chiều dọc ô Nhập các ô liền kề thành 1 ô 11 3. Kẻ Khung B1: Quét khối các ô cần bao khung B2: Fortmat  Cells (Ctrl +1). B3: Click thẻ Border B4: Định dạng khung Style: Kiểu đường khung Color: Màu khung Chọn khung: None: Không hoặc bỏ khung. Outline: Khung bên ngoài. Inside: Đường nét bên trong 12 Không kẻ khung Màu đường kẻ Khung bao ngoài Menu Format/Cells… Tab Border Chọn kiểu đường kẻKhung bên trong Chọn từng đường kẻ khung 13 4. Màu Nền B1: Quét khối các ô cần màu nền B2: Format Cells… B3: Click thẻ Patterns Chọn màu nền. Hoặc có thể Click chọn biểu tượng Fill Color trên thanh công cụ. Tạo Khung dạng Đường Chéo B1: Tạo chữ thứ nhất  Alt+Enter  Gõ chữ thứ 2  Enter. B2: Click vào ô vừa tạo Fortmat Cells… B3: Click thẻ Border Click chọn khung đường chéo B4: Định dạng lại Dữ Liệu trong ô vừa tạo. 14 Menu Format/Cells… Tab Patterns B 2: Chọn màu cần chọn Bỏ chọn màu B 1: Chọ Patterns B 3: Chọ Ok để hoàn thành 15 5. Sao Chép-Di Chuyển a. Sao chép thông thường B1: Chọn khối cần sao chép Ctrl+C Hoặc Edit Copy. B2: Click vào ô cần đặt Ctrl+V Hoặc Edit Paste. b. Di chuyển B1: Chọn khối Ctrl+X Hoặc Edit Cut B2: Click vào ô cần đặt  Ctrl+V Hoặc Edit Paste 16 6. Sao Chép Dữ Liệu Dạng Công Thức B1: Tính ô đầu tiên bằng công thức B2: Đưa chuột xuống gốc dưới của ô vừa tính sao cho có dạng dấu cộng “+” B3: Giữ chuột và kéo xuống/ngang đến ô cuối cùng để sao chép. Chú ý: Trong quá trình tính, một ô DL được tính cho tất cả các ô khác thì ô DL đó phải được cố định bằng cách nhấn F4. 17 7. Định Dạng Dữ Liệu Số B1: Quét khối các ô DL cần định dạng B2: Format  Cells… B3: Click thẻ Number Number B4: Định dạng Decimal place: Số thập phân số lẻ Use 1000 Separator (,): Dấu phân cách hàng nghìn. Hoặc Click Custom: Chọn một kiểu định dạng trong khung Type Hoặc gõ kiểu định dạng vào khung. Ví dụ: 0.00” vnd”, 0.00” usd” 18 Kiểu hiển thị số Khung xem trước Số chữ số thập phân Sử dụng ký hiệu ngăn cách hàng nghìn Cách hiển thị số âm Chú giải Menu Format/Cells… Tab Number 19 8. Định Dạng DL Ngày B1: Format  Cells…  Click Number  Date B2: Chọn một kiểu định dạng Hoặc Click Custom  Định dạng vào khung type Ví dụ: dd/mm/yyyy, mm/dd/yyyy,… Định dạng DL Giờ: Click Time  Click chọn kiểu định dạng Hoặc gõ kiểu định dạng vào khung Type. Ví dụ: hh:mm:ss 20 Menu Format/Cells… Tab Date B2:Chọn Custom B3: Chọn ngày cần định dạng B4: Chọn Ok để hoàn thành B1: Chọn Number 21 9. Định Dạng Tiền Tệ-Phần Trăm (%) a. Định dạng tiền tệ B1: Quét khối các ô cần định dạng B2: Format Cells… NumberCurrency B3: Chọn kiểu tiền tệ trong khung Symbol Chọn số lẻ thập phân trong khung Decimal place. b. Định dạng phần trăm (%) B1: Quét khối B2:Format Cells… NumberPercentage Hoặc Click Custom  Gõ kiểu định dạng vào khung Type. 22 B1: Chọn Nmber B2: Chọn Currency B 3: Chọn dạng tiền tệ B 4: Chọn Ok để hoàn thành Menu Format/Cells…Tab Currency 23 B1: Chọn Nmber B 2: Chọn Percentage B 3: Chọn % cần định dạng B 4: Chọn Ok để hoàn thành Menu Format/Cells…Tab Percentage 24 10. Tạo Số Thứ Tự B1: Gõ số 1 vào ô đầu tiên B2: Đưa chuột vào góc dưới của ô vừa tạo sao cho có dạng dấu cộng “+”. B3: Giữ Ctrl, Giữ chuột và kéo xuống. Hoặc B1: Gõ số 1 vào ô đầu tiên Gõ số 2 vào ô thứ 2 B2: Quét khối 2 ô đã tạo B3: Đưa chuột vào góc dưới của khối  Giữ chuột và kéo xuống. 25 Chương 3 Dữ Liệu Trong Excel 1. Các kiểu dữ liệu a. Dữ liệu chuỗi (Text): Là kiểu DL chuỗi, được mặc định bên trái. Một số nếu đi kèm với Text thì cũng là chuỗi. Muốn xuống dòng trong một ô, ta gõ Alt+Enter b. Dữ liệu Số (Number)/ Date/ Time: Mặc định bên phải. Một DL số nếu quá lớn so với chiều rộng của ô thì sẽ có kiểu hiện thị là # hoặc 12E+ 26 2. Các Toán Tử a. Các phép tính cơ bản + : cộng - :Trừ *: nhân /: chia &: Phép toán nối chuỗi Ví dụ: le&“ “&minh le minh b. Các phép toán so sánh >, >=, (khác), ^ (mũ), % (phần trăm) Chú ý: Trước khi tính cho một ô nào đó, ta phải có dấu bằng “=“ phía trước. Một số lỗi khi lập công thức  ###:Khi các con số không đủ độ rộng để hiển thị đầy đủ.  # Name?:Lỗi này thường do gõ sai tên hàm.  #N/A: Lỗi này thường gặp khi không tìm thấy giá trị.  #DIV/0!:Lỗi chia cho 0. Phép chia cho 0 là không có nghĩa.  #VALUE!:Lỗi giá trị. Thường là do dùng hàm hoặc phép toán không thích hợp.  #REF!:Lỗi tham chiếu. Thường là công thức chứa địa chỉ ô mà ô đã bị xóa.  #NUM!:Lỗi do số vượt quá khả năng xử lý của Excel. 27 28 Chương 4 Sử Dụng Hàm (Function) a. Giới thiệu Hàm là một chương trình đã được viết và cài đặt sẵn nhằm xử lý, tính toán một mục đích nào đó. b. Cú pháp tổng quát Tên hàm(đối số 1, đối số 2,…) Đối số: có thể là một giá trị, một biểu thức, một phép toán so sánh hay một hàm khác,… 29 Một Số Hàm Thông Dụng 1. Hàm Sum: Tính Tổng =Sum(quét vùng DL cần tính) =Sum(số thứ 1, số thứ 2, số thứ 3,…) VD: Tính tổng số lượng 30 Một Số Hàm Thông Dụng 2. Hàm Average: Tính Trung bình =Average(quét vùng DL cần tính) =Average(số thứ 1, số thứ 2, số thứ 3,…) VD: Tính trung từ A1 đến A5 31 3. Hàm Count: Hàm Đếm DL kiểu số, ngày. =Count(quét vùng DL đếm) 4. Hàm CountA: Đếm tất cả các DL khác rỗng =CountA(quét vùng DL đếm). VD: Ví dụ 1 32 Các Hàm (tt) 5. Hàm Max: Tính giá trị cao nhất =Max(vùng DL tính) =Max(số thứ1, số thứ 2, …) 6. Hàm Min: Tính giá trị thấp nhất Tương tự hàm Max 33 7. Hàm Rank: Xếp Hạng. Vd: =Rank(ô so sánh, vùng DL F4, 0 hoặc 1) Ô so sánh: Thường ô đầu tiên của vùng DL cần XH 0: Xếp hạng theo kiểu giá trị lớn nhất sẽ hạng 1 1: Xếp hạng theo kiểu giá trị nhỏ nhất sẽ hạng 1 34 Nhóm Hàm Toán Học (tt) 3. Hàm Round: Hàm làm tròn Round(số hoặc phép tính,N) N: Làm tròn vị trí thứ N  N>0: làm tròn số lẻ thập phân thứ N  N=0: làm tròn số hàng Đơn vị  N=-1: làm tròn số hàng Chục  N=-2: làm tròn số hàng Trăm Ví dụ: Vd: Round(15345.37564;3)=15345.376 Round(15345.37564;0)=15345 Round(15345.37564;-2)=15300 35 Nhóm Hàm Toán Học 1. Hàm Int: Lấy phần nguyên của một số, một phép tính. Int(số hoặc phép tính) Ví dụ: Int(15.3756)=15 Int(15/7) = 2 2. Hàm Mod: Lấy số dư của phép chia Mod(số chia, số bị chia) Ví dụ: Mod(15,7) = 1 36 Nhóm Hàm Điều Kiện (Logic) 1. Hàm And: Hàm điều kiện VÀ And(đk1, đk2, đk3,…) Ý nghĩa: Vừa thoả đk1, vừa thoả đk2,… Ví dụ: VD: = AND (B3>=23,B3<25) 2. Hàm OR: Hàm điều kiện HOẶC/Hay OR(đk1, đk2, đk3,…) Ý nghĩa: Chỉ cần thoả 1 trong các đk là được Ví dụ: Or(5=6;5<6) thì kết quả là True (đúng). Vd: 37 3. Hàm IF: Hàm điều kiện so sánh Nếu IF (bt logic, trị đúng, trị sai): – Hiển thị trị đúng nếu BT logic có g/t True – Hiển thị trị sai nếu BT logic có g/t False – VD: Nhóm Hàm Điều Kiện (tt) 38 5. Hàm Countif: Đếm có điều kiện Countif(quét vùng DL chứa đk đếm, “đk đếm”). Vd: Nhóm Hàm Điều Kiện (tt) 6. Hàm Sumif: Tính Tổng có điều kiện (Tính tổng theo một loại nào đó) Sumif(quét vùng DL chứa đk , “đk”, quét vùng DL tính tổng ). Vd: Thông thường, vùng DL chứa đk và vùng DL tính tổng phải tuyệt đối, tức là khi quét vùng DL xong, phải nhấn F4. 39 Nhóm Hàm Ngày-Giờ 1. Hàm Now(): Lấy ngày và giờ của hệ thống 2. Hàm Day: Lấy giá trị ngày của ngày tháng năm Day(ngày tháng năm) 3. Hàm Month: Lấy giá trị Tháng Month(ngày tháng năm) 4. Hàm Year: Lấy giá trị Năm Year(ngày tháng năm) 5. Hàm Date: Chuyển thành ngày tháng năm Date(năm, tháng, ngày) Ví dụ: Date(2008,4,13)=13/04/2008 40 6. Hàm Today(): xác định ngày tháng năm hiện tại của hệ thống. 7. Hàm Hour: Lấy gía trị Giờ Hour(giờ phút giây) 7. Hàm Minute: Lấy giá trị Phút Minute(giờ phút giây) 8. Hàm Second: Lấy giá trị Giây Second(giờ phút giây) Ví dụ: Hour(10:20:30 – 8:30:30) = 1 (giờ) Minute(10:20:30 – 8:30:30) = 50 (phút) Nhóm Hàm Ngày-Giờ 41 Các Hàm Xử Lý Chuỗi 1. Hàm Left: Lấy N ký tự bên trái Left(chuỗi,N) Ví dụ: Lấy 2 ký tự đầu của ký hiệu 42 2. Hàm Right: Lấy N ký tự bên phải Right(chuỗi, N) Ví dụ: Dựa vào ký hiệu lấy 2 ký tự cuối 43 3. Hàm Mid: Lấy N ký tự từ vị trí M Mid(chuỗi, M, N) Ví dụ: 4 Ký tự thứ 3,4,5,6 là năm thành lập 44 4. Hàm Len: Lấy chiều dài của chuỗi Len(chuỗi) Ví dụ: Len(“abc 123”) = 7 5. Hàm Value: Chuyển đổi số dạng chuỗi thành số Value(chuỗi số) Ví dụ: Value(right(“abc123”,3)) = 123 (số) Left(“abcd123”,Len(“abcd123”) – 3) = abcd Các Hàm Xử Lý Chuỗi 45 Các Hàm Xử Lý Chuỗi 6. Hàm UPPER: Chuyển đổi chữ thường thành chữ hoa. UPPER ( chuỗi)  Ngoài ra còn có các hàm: PROPER ( Chuỗi): Dùng để chuyển kí tự đầu của từ thành chữ hoa. LOWER ( Chuỗi): Dùng để chuyển tất cả các kí tự của chuỗi sang chữ thường. 6. Hàm TRIM : Cắt bỏ các khoảng trắng trong chuỗi. Ví dụ: =Trim(“ Giáo Trình Tin Học A”) → Giáo Trình Tin Học A. Vd: 46 Các Hàm Dò Tìm 1. Hàm Vlookup: Là hàm lấy DL khi DL trên bảng 2 hiển thị dạng cột. Cú pháp: Vlookup(giá trị dò, quét bảng chứa DL cần lấy F4, vị trí cột lấy DL, chọn 0 hoặc 1) Giá trị dò là giá trị liên quan (giá trị giống nhau) giữa 2 bảng. Quét DL bảng Chưa DL phải quét từ giá trị dò. 1: Dò lấy giá trị tương đối. 0: Dò lấy giá trị tuyệt đối.  Ví dụ 47 •  Ví dụ:Tên rượu dựa vào mã rượu tra trong bảng 1 48 2. Hàm Hlookup: Là hàm lấy DL khi DL trên bảng 2 hiển thị theo hàng. Cú pháp: Hlookup(giá trị dò, quét bảng chứa DL cần lấy F4, vị trí hàng lấy DL, chọn 0 hoặc 1)  Ví dụ Các Hàm Dò Tìm 49 VD:Tên nước dựa vào mã nước tra trong bảng 2 50 3. Hàm Index: Là lấy một giá trị tại điểm giao của dòng và cột xác định trong một vùng dữ liệu. INDEX( Vùng dữ liệu,Dòng,cột) Nhóm Hàm Cơ Sở Dữ Liệu • Hàm DSUM: Tính tổng các giá trị trong cột của một vùng thỏa ĐK cho trước. • DSUM( Vùng thống kê, cột thống kê,ĐK Thống kê) •  Ví dụ: 51 2. Hàm DMAX: Tìm ô có giá trị lớn nhất trong một cột của vùng thống kê thỏa ĐK cho trước. DMAX( Vùng thống kê, cột thống kê,ĐK Thống kê)  Ví dụ: 3. Hàm DMIN: Tìm ô có giá trị nhỏ nhất trong một cột của vùng thống kê thỏa ĐK cho trước. DMAX( Vùng thống kê, cột thống kê,ĐK Thống kê)  Ví dụ: 52 Nhóm Hàm Cơ Sở Dữ Liệu 4. Hàm DAVERAGE: Tính trung bình các ô trong cột của vùng thống kê thỏa ĐK cho trước. DAVERAGE( Vùng thống kê, cột thống kê,ĐK Thống kê)  Ví dụ: 5. Hàm DCOUNT: Điếm số ô chứa giá trị số trong cột của vùng thống kê thỏa ĐK cho trước. DAVERAGE( Vùng thống kê, cột thống kê,ĐK Thống kê)  Ví dụ: 53 Chương 5 Cơ Sở Dữ Liệu Sắp xếp DL B1: Quét toàn bộ bảng chứa DL cả tiêu đề B2: Data  Sort… B3: Sort by: Chọn tiêu đề cột cần sắp xếp Chọn kiểu sắp xếp: Ascending: Tăng Descending: Giảm Nếu muốn chọn thêm tiêu đề cần sắp xếp, ta chọn ở phần Then By. 54 Rút Trích Dữ Liệu Mục đích: Rút ra nhứng DL thoả điều kiện nào đó. 1. Rút trích tự động B1: Click chọn ô trong vùng DL cần rút trích B2: Data  Filter  Auto Filter B3: Click Custom…  Xuất hiện hộp thoại B4: Chọn điều kiện gõ điều kiện Equals: So sánh bằng “=“ Does not equal: Không bằng Is greater than: so sánh lớn hơn > Is less than: so sánh nhỏ hơn < Begin with: được bắt đầu với… Có thể chọn thêm điều kiện And hoặc Or. 55 Rút Trích DL (tt) 2. Rút Trích Thoả Nhiều Điều Kiện B1: Tạo vùng điều kiện gồm hai phần Tiều đề đk: Copy tiêu đề từ dữ liệu Biểu thức điều kiện B2: Data Filter Advanced Filter B3: Click chọn mục O2 Copy to location… B4: List Range: Quét toàn vùng DL cả tiêu đề Criteria range: quét vùng điều kiện Copy to: Click vào ô trống bất kỳ 56 Vẽ Biểu Đồ - Cheøn Hình B1: Quét khối phần cần vẽ B2: Insert  Chart hoặc Click biểu tượng trên thanh công cụ B3: Chọn kiểu biểu đồ  Next  Next B4: Định dạng biểu đồ  Next  Finish. Chart Title: Đặt tiêu đề biểu đồ Category (x) axis: Tên tiêu đề trục ngang Value (Y) axis: Tên tiêu đề trục đứng Thẻ Data label Value: hiển thị giá trị từng thành phần Percentage: Hiển thị phần trăm Cheøn Hình: Gioáng nhö treân Word. 57 Tạo Điều Kiện Cho Một Vùng DL B1: Quét khối vùng DL cần hiển thị B2: Fortmat  Conditional Formatting B3: Chọn điều kiện trong khung số 2: Between: Giữa một khoảng giá trị nào đó. Equal to: Bằng Greater than: Lớn hơn Less than: Nhỏ hơn B4: Click Format  Định dạng  Ok. Ta có thể thêm điều kiện bằng cách Click Add để thêm.