Bài giảng Cơ sở tin học 2 - Microsoft Excel

dữ liệu: Các kiểu: Accounting: Tài chính. Date: Ngày tháng. Time: Thời gian. Percentage: Phần trăm. Text: Chữ Kiểu dữ liệu: Kiểu Custom: Kiểu do người dùng tự định nghĩa Nhập vào kiểu định dạng Alignment: Trình bày dữ liệu theo chiều ngang (Horizontal) và dọc (Vertical)(General – theo dữ liệu, Left – canh lề trái,Right – canh lề phải,… Top – Chữ viết lên cao,Center - chữ viết ở giữa, Bottom - chữ viết ởđáy ô) Wrap text - Gói gọn dữ liệu trong ô Shrink to fit – Tự động thay đổi kích cỡ chữ cho vừa ôMerge Cells – Trộn các ô đã chọn thành 1 ô. Text direction - chiều viết chữ (Left-to-Right: từ trái sang phải,…) Orientation - Hướng viết chữ (tính theo độ) Font: Kiểu phông chữ Nghiêng, đậm, … Kích thước Kiểu gạch chân Một số lựa chọn khác… Xem trước Border: Kiểu đường viền Màu sắc Đường viền nào hiện, nút sẽ chìm Patterns: Màu tô Mẫu tô Sử dụng thanh công cụ định dạng: Các biểu tượng và ý nghĩa của chúng trên thanh công cụ định dạng trong Excel tương đối giống Word. Di chuyển chuột lên trên từng nút rồi chờ vài giây để có được Tooltips. Hãy chọn ô hay miền trước khi kích hoạt chức năng tương ứng trên thanh công cụ định dạng. 2.9- Ghi chú cho ô: Right Click vào ô/các ô cần ghi chú: Chọn Insert Comments Gõ ghi chú vào(Để xóa ghi chú: chọn hộp ghi chú và nhấn Delete, hoặc R-C vào ô, chọn Delete Comment) 3- HÀM TRONG EXCEL: Công thức Bắt đầu bởi dấu “=“ Gồm: Địa chỉ, hằng, miền,… Toán tử Hàm VD: = A1+A2-B2 = SIN(A1) + COS(B2) = LN(A5) Thanh công thức (Formula bar): Có thể sử dụng thanh công thức để nhập công thức cho tiện. Ví dụ: =G3+G3*F3+J3-H3-I3 2.1- Khái niệm hàm: Các công thức tính toán được xây dựng trước. Phục vụ các tính toán thông dụng. Cú pháp: Tên hàm (danh sách đối số) Đối số được phân cách bởi dấu phẩy (hoặc dấu chấm phẩy) Ví dụ: =rank(x, range, order) Đối số có thể là giá trị, địa chỉ, hằng,… 3.2- Nhập công thức và hàm: Nhập trực tiếp vào ô Sử dụng thanh công thức Kích chuột vào biểu tượng fx để mở hộp thoại chọn hàm. Select a category: loại hàm. Select a function: chọn hàm. 3.3- Một số hàm thông dụng: 3.3.1- Hàm về thời gian: Hàm DATE: Cú pháp: DATE(year,month,day) Ý nghĩa: Trả về dãy số liên quan đến ngày.+ Year: có thể từ 1 đến 4 con số. + Month: nếu >12, Month sẽ được hiểu là tháng thứ (Month – 12)Vd: DATE(2008,14,2) cho kết quả là:February 2, 2009. 3.3.1- Hàm về thời gian: Hàm DATE: (tt) + Day: là con số xác định ngày của tháng. Nếu Day > số ngày trong tháng thì day sẽ được hiểu là ngày thứ (Day -30) hoặc (Day - 31), (Day - 28), (Day - 29) (tùy vào tháng, năm).Vd: DATE(2008,1,35) cho kết quả là: February 4, 2008 Ví dụ về hàm DATE: =DATE(A2,B2,C2) sẽ cho kết quả là:1/1/2008 hoặc 39448 (tùy vào cách Format Cells). 3.3.1- Hàm về thời gian: Hàm DAY: Cú pháp: DAY(serial_number) Ý nghĩa: Trả về số thứ tự Ngày (1..31) trong dãy số liên quan đến ngày serial_number. 3.3.1- Hàm về thời gian: Ví dụ về hàm DAY: =DAY(A2) sẽ cho kết quả là 15. 3.3.1- Hàm về thời gian: Hàm NOW: Cú pháp: NOW() Ý nghĩa: Trả về Ngày, Giờ hiện hành. Hàm TODAY: Cú pháp: TODAY() Ý nghĩa: Trả về Ngày, Tháng, Năm hiện hành.(Tùy vào cách Format Cells) 3.3.1- Hàm về thời gian: Hàm MONTH: Cú pháp: MONTH(serial_number) Ý nghĩa: Trả về số thứ tự Tháng (1..12) trong dãy số liên quan đến ngày serial_number. 3.3.1- Hàm về thời gian: Ví dụ về hàm MONTH: =MONTH(A2) sẽ cho kết quả là 4. 3.3.1- Hàm về thời gian: Hàm YEAR: Cú pháp: YEAR(serial_number) Ý nghĩa: Trả về Năm trong dãy số liên quan đến ngày serial_number. 3.3.1- Hàm về thời gian: Ví dụ về hàm MONTH, YEAR: =YEAR(A2) sẽ cho kết quả là 2008. 3.3.1- Hàm về thời gian: Hàm WEEKDAY: Cú pháp: WEEKDAY(serial_number,return_type) Ý nghĩa: Trả về ngày trong tuần trong dãy số serial_number liên quan đến ngày. Return_type:= 1 hoặc bỏ trống: Kiểu trả về là 1 (Sunday) đến 7 (Saturday), giống như các phiên bản trước của Microsoft Excel.= 2: Kiểu trả về là 1 (Monday) đến 7 (Sunday).= 3: Kiểu trả về là 0 (Monday) đến 6 (Sunday). 3.3.1- Hàm về thời gian: Ví dụ về hàm WEEKDAY: =WEEKDAY(A2) sẽ cho kết quả là 5. =WEEKDAY(A2,2) sẽ cho kết quả là 4. =WEEKDAY(A2,3) sẽ cho kết quả là 3. 3.3.1- Hàm về thời gian: Hàm HOUR: Cú pháp: HOUR(serial_number) Ý nghĩa: Trả về Giờ trong dãy số serial_number liên quan đến giờ. 3.3.1- Hàm về thời gian: Ví dụ về hàm HOUR: =HOUR(A2) sẽ cho kết quả là 3. =HOUR(A3) sẽ cho kết quả là 15. =HOUR(A4) sẽ cho kết quả là 15. 3.3.1- Hàm về thời gian: Hàm MINUTE: Cú pháp: MINUTE(serial_number) Ý nghĩa: Trả về Phút trong dãy số liên quan đến giờ serial_number. 3.3.1- Hàm về thời gian: Ví dụ về hàm MINUTE: =MINUTE(A2) sẽ cho kết quả là 48. 3.3.1- Hàm về thời gian: Hàm SECOND: Cú pháp: SECOND(serial_number) Ý nghĩa: Trả về Giây trong dãy số liên quan đến giờ serial_number. 3.3.1- Hàm về thời gian: Ví dụ về hàm SECOND: =SECOND(A2) sẽ cho kết quả là 18. =SECOND(A3) sẽ cho kết quả là 0. 3.3.1- Hàm về thời gian: 3.3.2- Hàm về ký tự: Hàm LEFT: Cú pháp: LEFT(text,num_chars) Ý nghĩa: Trích ra bên trái chuỗi text một số num_chars ký tự. Num_chars >= 0. Nếu Num_chars > chiều dài của text thì kết quả trả về là cả chuỗi text. Nếu bỏ trống num_chars, mặc nhiên xem như là 1. Ví dụ về hàm LEFT: =LEFT(A2,4) sẽ cho kết quả là Sale. =LEFT(A3) sẽ cho kết quả là S. Hàm RIGHT: Cú pháp: RIGHT(text,num_chars) Ý nghĩa: Trích ra bên phải chuỗi text một số num_chars ký tự. Num_chars >= 0. Nếu Num_chars > chiều dài của text thì kết quả trả về là cả chuỗi text. Nếu bỏ trống num_chars, mặc nhiên xem như là 1. 3.3.2- Hàm về ký tự: Ví dụ về hàm RIGHT: =RIGHT(A2,5) sẽ cho kết quả là Price. =RIGHT(A3) sẽ cho kết quả là r. Hàm MID: Cú pháp: MID(text,start_num,num_chars) Ý nghĩa: Trích trong chuỗi text, bắt đầu tại vị trí start_num, một số num_chars ký tự. (Vị trí của ký tự đầu tiên từ trái sang trong chuỗi text được tính là 1) Nếu start_num > chiều dài của text thì kết quả trả về là chuỗi “” (rỗng). Nếu start_num chiều dài text, kết quả trả về là các ký tự đến cuối chuỗi text. 3.3.2- Hàm về ký tự: Ví dụ về hàm MID: =MID(A2,1,5) sẽ cho kết quả là Fluid. =MID(A2,7,20) sẽ cho kết quả là Flow. =MID(A2,20,5) sẽ cho kết quả là chuỗi “” (rỗng). Hàm TRIM: Cú pháp: TRIM(text) Ý nghĩa: Xóa trong chuỗi text tất cả các khoảng trống (khoảng cách) dư thừa (đầu, giữa và cuối chuỗi), các từ cách nhau đúng 1 khoảng trống. 3.3.2- Hàm về ký tự: Ví dụ về hàm TRIM: =TRIM(“ DH BC Ton Duc Thang ”) sẽ cho kết quả là “DH BC Ton Duc Thang”. Hàm LEN: Cú pháp: LEN(text) Ý nghĩa: Trả về số ký tự có trong chuỗi text. 3.3.2- Hàm về ký tự: Ví dụ về hàm LEN: =LEN(A2) 9. =LEN(A3) 0. Hàm VALUE: Cú pháp: VALUE(text) Ý nghĩa: Chuyển chuỗi text ở dạng số sang số tương ứng. Text có thể được định dạng: số, ngày, giờ. 3.3.2- Hàm về ký tự: Ví dụ về hàm VALUE: =VALUE(“$1,000”) sẽ trả về 1000. =VALUE("16:48:00") - VALUE("12:00:00")sẽ trả về 0.2 hoặc 4:48:00 Hàm TEXT: Cú pháp: TEXT(value,format_text) Ý nghĩa: Chuyển số value sang chuỗi theo kiểu định dạng format_text. Format_text là con số được định dạng theo các kiểu trong hộp thoại Category. (Vào Format/Cells..., chọn kiểu dữ liệu là Custom) 3.3.2- Hàm về ký tự: Ví dụ về hàm TEXT: =TEXT(A2, "$0.00") sẽ trả về $2800.00 3.3.3- Hàm về toán học: ABS(X) Giá trị tuyệt đối của X ABS(4) = ABS(-4) = 4 ABS(-4.5) = 4.5 INT(X) Làm tròn xuống tới một số nguyên gần nhất với X INT(-4.45) = -5 INT( 4.55) = 4 CEILING (X,N) Trả về số nhỏ nhất ≥ X và chia hết cho N N ở đây có thể coi là sai số CEILING (4.27, 0.1) = 4.3 FLOOR (X,N) Trả về số lớn nhất ≤ X và chia hết cho N FLOOR (4.27, 0.1) = 4.2 ROUND(X,N) Làm tròn X N: số chữ số sau dấu thập phân ROUND(4.27, 1) = 4.3 ROUND(-4.27, 0) = - 4 ROUND(16.27, -1) = 20 TRUNC(X, [N]) Phần nguyên của X N: số chữ số sau dấu thập phân TRUNC(-4.45) = TRUNC(-4.45, 0) = - 4 TRUNC(11.276, 2) = 11.27 TRUNC(16.276, -1) = 10 3.3.3- Hàm về toán học: Một số hàm toán học và lượng giác: COS(X) COSIN của X (radian) ACOS(X) ARCCOS của X SIN(X) SIN của X TAN(X) TANG của X LOG10(X) Logarit cơ số 10 của X LN(X) Logarit Neper của X PI() 3.14159… RANDIANS (độ) Chuyển từ đơn vị độ sang đơn vị Radian DEGREES(radian) Chuyển từ Radian sang độ Một số hàm toán học và lượng giác: EXP(X) eX SQRT(X) Căn bậc 2 của X MOD(X,Y) X mod Y RAND() Trả về số ngẫu nhiên trong khoảng (0,1) Một số hàm toán học và lượng giác: SUM(X1,X2,…) Tổng dãy số X1,X2,… SUM(miền) Tổng các số trong miền Ví dụ: SUM(A1:A9) SUM(B2..B15) Một số hàm toán học và lượng giác: SUMIF(miền kiểm tra, điều kiện, miền tổng) Tính tổng các phần tử trong miền tổng với điều kiện phần tử tương ứng trong miền kiểm tra thoả mãn điều kiện Miền kiểm tra điều kiện Miền tính tổng Ví dụ: B6: Số lượng cam Dùng công thức: SUMIF(A1:A5, “Cam”, B1:B5) NOT(Logical) AND(Logical1, Logical2,…) OR(Logical1, Logical2,…) IF(Logical_test, value_if_true, value_if_false) Nếu điều kiện Logical_test đúng, trả về giá trị value_if_true Nếu điều kiện sai, trả về giá trị value_if_false value_if_false có thể là một hàm IF khác Ví dụ: IF(B2>5, “Đạt yêu cầu”, “Không đạt”) IF(B2>=8, “Giỏi”, IF(B2= lookup_value.Lookup_array cần phải được sắp xếp giảm dần:TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., Nếu Match_type được bỏ qua (khuyết), mặc nhiên xem là 1. Ví dụ: =MATCH(39,B2:B5,1) sẽ trả về 2. Vì không tìm thấy 39, nên ô chứa giá trị 38 (, =, <=) 5.3. Lọc dữ liệu (tt): Các tiêu chuẩn đặt trên cùng hàng được thự hiện đồng thời (điều kiện and (và)) Các tiêu chuẩn đặt trên cùng cột được thực hiện không đồng thời (điều kiện or (hoặc)) 5.3. Lọc dữ liệu (tt): Ví dụ: 5.3. Lọc dữ liệu (tt): Ví dụ (tt): 5.3. Lọc dữ liệu (tt): Ví dụ (tt): 5.3. Lọc dữ liệu (tt): Miền tiêu chuẩn so sánh gián tiếp: Cho phép đưa vào các tiêu chuẩn để so sánh dữ liệu hoặc một phần dữ liệu trong 1 trường với 1 giá trị nào đó. Quy tắc tạo miền như sau: Hàng đầu ghi tiêu đề cho các tiêu chuẩn. Tiêu đề này có thể đặt bất kỳ nhưng không được trùng với tên trường nào. Từ hàng thứ 2 trở đi ghi các tiêu chuẩn so sánh, mỗi tiêu chuẩn là 1 công thức. Công thức này phải chứa địa chỉ của mẩu tin đầu tiên. Kết quả thực hiện công thức này là 1 giá trị Logic (True hoặc False) 5.3. Lọc dữ liệu (tt): Ví dụ: