Bài giảng Tin học đại cương - Chương 5: Bảng tính điện tử Microsoft Excel

 tính (tt) 
4/ Sao chép hay di chuyển Worksheet: 
Cách 1: Dùng chuột kéo và thả (nếu giữ thêm phím Ctrl trong khi kéo và thả thì thực hiện thao tác sao chép) 
Cách 2: Kích chuột phải lên Worksheet muốn di chuyển và chọn Move or Copy 
Dùng để sao chép 
BÀI 3  ĐỊNH DẠNG BẢNG TÍNH 
I/ Định dạng dữ liệu kiểu số 
Cần chỉnh các thông số thích hợp trong Regional Options. 
Chọn vùng bảng tính cần định dạng. 
Vào menu Format -> Cells 
Đứng ở Tab Number. 
Chọn mục Number. 
Decimal places: Số chữ số thập phân. 
Use 1000 Separator (,): Sử dụng dấu ngăn cách hàng ngàn. 
 Negative numbers: Các cách thể hiện cho số âm. 
 Định dạng kiểu ngày tháng: Chọn dd/mm/yyyy trong Custom 
II/ Định dạng dữ liệu kiểu ngày tháng và tiền tệ 
Kiểu ngày tháng: Chọn định dạng dd/mm/yyyy trong mục Custom của Tab Number. 
Kiểu tiền tệ: Chọn định dạng #,### [$ VNĐ] trong mục Custom của Tab Number. 
III/ Định dạng ô chứa văn bản 
Vào menu Format -> Cells 
Đứng ở Tab Alignment. 
III/ Định dạng ô chứa văn bản (tt) 
Horizotal: Các định dạng lề canh cho dữ liệu trong ô theo chiều nằm ngang. 
Vertical: Các định dạng lề canh cho dữ liệu trong ô theo chiều thẳng đứng. 
Orientation: Xác định hướng nghiêng cho dữ liệu trong ô. 
Wrap text: Tự động ngắt dòng trong trường hợp độ rộng của ô nhỏ hơn chiều dài của chuỗi. 
Shrink to fit: Tự động điều chỉnh cỡ chữ (Không thay đổi Size chữ) trong trường hợp độ rộng của ô nhỏ hơn chiều dài của chuỗi. 
Merge cells: Trộn nhiều ô lại thành một ô. (Hoặc có thể kích chuột lện biểu tượng gộp ô 	trên thanh định dạng). 
BÀI 4  CÔNG THỨC VÀ HÀM 
I/ Tạo công thức cơ bản 
Công thức được tạo ra nhằm mục đích để tính toán và trả lại giá trị cho bảng tính. 
Phải nhập dấu = trước mọi công thức. 
Thanh công thức 
II/ Các toán tử trong công thức 
Toán tử liên kết chuỗi: & 
Nối chuỗi với chuỗi: “Tin”&” Học” -> Tin Học 
Nối số với số: 123&456 -> 123456 
Nối số với chuỗi: “Excel “&2000 -> Excel 2000 
Nối chuỗi với một biểu thức số học: 
	 “Phim “&2002/2&”đêm” -> Phim 1001 đêm 
Nối chuỗi với hàm: “Tổng số là “&Sum(10;20) -> Tổng số là 30 
Toán tử so sánh: >, =, 
Các biểu thức so sánh luôn cho kết quả là một giá trị logic (True hay False) 
Ví dụ: 240 True 
	 250=360-100 -> False 
III/ Các loại địa chỉ 
Địa chỉ tương đối: Loại địa chỉ này khi sao chép công thức sẽ bị thay đổi địa chỉ dòng hoặc địa chỉ cột. 
Địa chỉ tuyệt đối: Sẽ không bị thay đổi địa chỉ dòng và địa chỉ cột khi sao chép công thức. Kí hiệu địa chỉ tuyệt đối: $Cột$Dòng. 
Địa chỉ hỗn hợp: 
Tuyệt đối dòng: Sẽ không bị thay đổi địa chỉ dòng. Kí hiệu: Cột$Dòng. 
Tuyệt đối cột: Địa chỉ cột không thay đổi. Kí hiệu: $CộtDòng. 
IV/ Các hàm cơ bản trên Excel 
Sau khi tính toán hàm sẽ trả về một giá trị (số, chuỗi,) 
Dạng thức chung của hàm: 
	 (Đối số 1, đối số 2,..., đối số n) 
Một hàm có thể có một đối số, nhiều đối số, hoặc có hàm không có đối số nào. 
Các đối số trong hàm ngăn cách nhau bởi dấu phẩy (,) hay dấu chấm phẩy (;) là tùy thuộc vào việc thiết đặt trong Regional Options của mỗi máy. 
Ví dụ: =SUM(10;C8;A2:C6) 
Tên hàm 
Các đối số 
1/ Khái niệm đối số (hay tham số) 
Đối số có thể là: 
Một giá trị kiểu số (Ví dụ: 6) 
Một chuỗi ký tự (Ví dụ: “TP”) 
Một địa chỉ ô hay vùng địa chỉ ô (Ví dụ: A2 hay A3: E9) 
Tên vùng (Ví dụ: NKCNO) 
Biểu thức đại số (Ví dụ: 2*3+6/4) 
Những hàm khác. 
2/ Các hàm toán học 
Hàm SUM: 
Cú pháp: =SUM(đối số 1; đối số 2;;đối số n) 
Công dụng: Tính tổng các đối số. 
Chú ý: Đối số phải là một giá trị kiểu số, có tối đa 30 đối số. 
Ví dụ: =SUM(10;20;30) -> 60 
Hàm ABS 
Cú pháp: =ABS(đối số) 
Công dụng: Hàm sẽ trả về giá trị tuyệt đối của đối số. 
Ví dụ: =ABS(-17) -> 17 
Hàm INT 
Cú pháp: =INT(đối số) 
Công dụng: Trả về phần nguyên của đối số. 
Ví dụ: =INT(15,456) -> 15 
2/ Các hàm toán học (tt) 
Hàm MOD: 
Cú pháp: =MOD(đối số 1; đối số 2) 
Công dụng: Trả về số dư khi thực hiện phép chia đối số 1 cho đối số 2. 
Ví dụ: =MOD(10;3) -> 1 
Hàm ROUND 
Cú pháp: =ROUND(Tham số; Vị trí) 
=0: Làm tròn tại vị trí dấu thập phân. 
>0: Làm đến số chữ số lẽ được chỉ định. 
<0: Làm tròn ở phần nguyên với số ký số bạn chỉ ra. 
Công dụng: Trả về giá trị của tham số sau khi đã làm tròn 
Ví dụ: 
=Round(65,617;0) -> 66 
=Round(65,617;2) -> 65,62 
=Round(65,617;-2) -> 100 
2/ Các hàm toán học (tt) 
Hàm SQRT 
Cú pháp: =SQRT(đối số) 
Công dụng: Trả về căn bậc 2 của đối số. 
Ví dụ: =SQRT(9) -> 3 
Chú ý: Nếu đối số là số âm hàm sẽ trả về lỗi #NUM! 
3/ Các hàm luận lý 
Hàm IF: 
Cú pháp: =IF(logical_test;value_if_true;value_if_false) 
Logical_test: điều kiện để xét, logical có thể là kết quả của một hàm luận lý như AND, OR,... 
Value_if_true: giá trị trả về nếu điều kiện logical_test là TRUE. 
Value_if_false: giá trị trả về nếu điều kiện logical_test là FALSE. 
Công dụng: Nếu biểu thức logic đúng thì hàm IF trả về đối số thứ nhì , ngược lại nếu biểu thức logic sai thì hàm IF trả về đối số thứ 3. 
Chú ý: 
Có thể có 7 hàm IF được lồng vào nhau để tạo nên công thức phức tạp hơn. 
Biểu thức điều kiện phải có giá trị là TRUE hoặc FALSE, nếu không hàm IF trả về giá trị #VALUE 
3/ Các hàm luận lý (tt) 
Hàm IF: (tt) 
Ví dụ: =IF(3>5;“Sai”;“Đúng”)=? 
Hàm AND: 
Cú pháp : =AND(logical_1,logical_2,...) 
logical_1,logical_2,... là các đều kiện cần kiểm tra. 
Công dụng: Trả về kết quả TRUE nếu tất cả điều kiện đều TRUE, trả về FALSE nếu một trong các điều kiện FALSE. 
Ví dụ: 
=AND(1>0 ; 2>=2 ; 4 FALSE 
=AND(1>0 ; 2>=2 ; 4 TRUE 
Hàm OR: 
Cú pháp: =OR(logical_1,logical_2,...) 
Công dụng: Trả về TRUE nếu một trong các điều kiện là TRUE. Trả về FALSE nếu tất cả các điều kiện là FALSE. 
3/ Các hàm luận lý (tt) 
Hàm OR: (tt) 
Ví dụ: 
=OR(1>5 ; 2>=3 ; 4 FALSE 
=OR(1>0 ; 2>=3 ; 4 > 5 ; 4 TRUE 
Hàm NOT: 
Cú pháp: =NOT(logical) 
Công dụng: Trả về phủ định của một biểu thức Logic 
Ví dụ: 
=NOT(2=2+1) -> TRUE 
=NOT(2 FALSE 
4/ Các hàm xử lý chuỗi 
Hàm LEFT: 
Cú pháp: =LEFT(text;num_chars) 
text là chuỗi cần trích ký tự. 
num_chars là ký tự mà bạn cần trích bên trái chuỗi text . 
Công dụng: Trích bên trái một chuỗi một hoặc nhiều ký tự dựa vào số ký tự mà bạn chỉ định. 
Chú ý: 
num_chars không phải là số âm. 
num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. 
num_chars nếu bỏ qua thì mặc định là 1. 
Ví dụ: =Left(“Trung tam tin hoc”; 5 ) -> Trung 
Hàm Right: Tương tự hàm Left nhưng sẽ rút trích số ký tự bên phải của chuỗi. 
4/ Các hàm xử lý chuỗi (tt) 
Hàm MID: 
Cú pháp: =MID(text;start_num,num_chars) 
Text: là chuỗi hoặc tham chiếu đến chuỗi. 
start_num: vị trí bắt đầu trích lọc chuỗi con trong text. 
num_chars: số ký tự của chuỗi mới cần trích từ chuỗi text. 
Công dụng: Trích một chuỗi con từ một chuỗi text , bắt đầu từ vị trí start_num với số ký tự được chỉ định num_chars. 
Chú ý: 
start_num: lớn hơn chiều dài chuỗi text thì hàm trả về chuỗi rỗng “” 
start_num: nhỏ hơn 1 hàm trả về lỗi #VALUE! 
num_chars: âm MID trả về lỗi #VALUE! 
Ví dụ: =MID(“Trung tam tin hoc”;11;7) -> tin hoc 
4/ Các hàm xử lý chuỗi (tt) 
Hàm LEN: 
Cú pháp: =LEN(Chuỗi số) 
Công dụng: Trả về độ dài (số ký tự) của chuỗi số. 
Hàm VALUE: 
Cú pháp: =VALUE(Chuỗi số) 
Công dụng: Đổi chuỗi số ra thành dữ liệu kiểu số. 
5/ Các hàm thống kê 
Hàm AVERAGE: 
Cú pháp: =AVERAGE(number1;number2;...) 
number1,number2, có thể có từ 1 đến 30 số mà bạn cần tính trung bình cộng. 
Công dụng: Tính trung bình cộng của các đối số. 
Hàm COUNT: 
Cú pháp: =COUNT(value1;value2;...) 
value1,value2,... có thể có từ 1 đến 30 vùng giá trị mà bạn muốn đếm số ô chứa dữ liệu kiểu số. 
Công dụng: Đếm số ô dữ liệu kiểu số trong vùng tham chiếu. 
Hàm MIN: 
Cú pháp: =MIN(number1;number2;...) 
Công dụng: Trả về số nhỏ nhất trong các đối số truyền vào. 
5/ Các hàm thống kê (tt) 
Hàm MAX: Tương tự hàm MIN nhưng se trả về số lớn nhất trong các đối số truyền vào. 
Hàm RANK: 
Cú pháp: =RANK(number; ref; order) 
number giá trị mà bạn cần tìm vị thứ. 
ref là mảng hoặc vùng tham chiếu đến một danh sách giá trị kiểu số. Những giá trị không phải là số được bỏ qua. 
order: phương thức sắp xếp: 
order = 0, hoặc bỏ qua thì số lớn nhất có vị trí nhỏ nhất (Sắp xếp giam). 
order = 1 thì số nhỏ nhất có vị trí nhỏ nhất (Sắp xếp tăng). 
Công dụng: Tìm vị thứ của một số trong dãy số. 
5/ Các hàm thống kê (tt) 
Hàm COUNTIF: 
Cú pháp: =COUNTIF(Dãy kiểm tra; Điều kiện) 
Vùng kiểm tra: Là một vùng ô. 
Điều kiện: Là điều kiện để đếm, có thể là hằng số hay địa chỉ của ô hay dạng thức “>16000”. 
Công dụng: Hàm Countif trả về một số bằng cách tìm trong Dãy kiểm tra có bao nhiêu ô thỏa mãn điều kiện. 
Hàm SUMIF: 
Cú pháp: =SUMIF(Dãy kiểm tra; Điều kiên; Dãy tính tổng) 
Dãy tính tổng: Tùy theo cách tính toán mà có thể có dãy tính tổng hay không. 
Công dụng: Tính tổng của dãy tính tổng thoả dãy kiểm tra đối với điều kiện. 
6/ Các hàm ngày tháng 
Hàm TODAY: 
Cú pháp: =TODAY() 
Công dụng: Trả về ngày hiện tại trong hệ thống của bạn. Nếu định dạng ô là General trước khi hàm nhập công thức, kết quả trả về ở định dạng ngày tháng. 
Hàm DATE: 
Cú pháp: =DATE(year; month; day) 
Công dụng: Trả về một chuỗi hoặc một số thể hiện một ngày tháng đầy đủ. Nếu định dạng ô là General trước khi nhập hàm thì kết quả trả về là chuỗi ngày tháng. 
6/ Các hàm ngày tháng (tt) 
Hàm DAY: 
Cú pháp: =DAY(serial_number) 
serial_number dạng chuỗi số tuần tự của ngày cần tìm. Ngày tháng này nên nhập bằng hàm DATE hoặc kết quả trả về từ hàm khác. 
Có thể bị lỗi nếu bạn nhập serial_number là một chuỗi dạng văn bản. 
Công dụng : Trả về thứ tự của ngày từ chuỗi ngày tháng. 
Hàm Month và hàm Year : Tương tự như hàm Day nhưng sẽ cho giá trị tháng và năm. 
7/ Các hàm tìm kiếm & tham chiếu 
Hàm VLOOKUP: 
Cú pháp: = VLOOKUP ( Giá trị dò tìm ; Bảng chứa giá trị dò tìm ; Cột trả về của bảng chứa giá trị dò tìm ; Kiểu dò tìm ) 
VLOOKUP xuất phát từ vertical lookup : dò tìm theo phương đứng, hay theo cột. 
Kiểu dò tìm: Dùng kiểu dò tìm là 0 
Công dụng: Dò tìm một giá trị ở cột đầu tiên bên trái của một bảng chứa giá trị dò tìm. Nếu tìm thấy sẽ trả về giá trị ở cùng trên cùng một dòng với giá trị tìm thấy trên cột mà bạn chỉ định. Hàm VLOOKUP thường dùng để điền thông tin vào bảng dữ liệu từ bảng dữ liệu phụ. 
Hàm HLOOKUP: Tương tự như hàm VLOOKUP nhưng lúc này dò tìm theo dòng. 
7/ Các hàm tìm kiếm & tham chiếu (tt) 
Hàm INDEX: 
Cú pháp: =INDEX(array; row_num; column_num) 
array: Là một mảng . 
row_num: Chỉ số dòng cần trả về giá trị. 
colum_num: Chỉ số cột cần trả về giá trị. 
Công dụng: Trả về một giá trị nằm bên trong một mảng dựa vào chỉ số dòng và cột. 
Hàm MATCH: 
Cú pháp: =MATCH(lookup_value; lookup_array; match_type) 
Lookup_value: Là giá trị cần tìm trong bảng giá trị. 
Lookup_array: Là một dãy ô liên tục để tìm kiếm giá trị. 
Match_type: Dùng kiểu dò tìm là 0. 
Công dụng: Trả về vị trí (chỉ mục) của một giá trị từ một dãy giá trị. 
BÀI 6  CƠ SỞ DỮ LIỆU TRÊN EXCEL 
I/ Khái niệm về Cơ sở dữ liệu 
Tập hợp các thông tin về một đối tượng được lưu trữ trên máy tính, nhằm phục vụ cho công việc quản lý hay truy xuất đối tượng đó, được gọi là cơ sở dữ liệu (CSDL). 
Một CSDL bao gồm một hoặc nhiều bản ghi (Record) dữ liệu khác nhau, trong mỗi bản ghi có một hay nhiều trường dữ liệu (Field). 
II/ Tổ chức vùng điều kiện 
Muốn rút trích dữ liệu theo một điều kiện nào đó từ CSDL của Excel thì trước hết phải tạo vùng điều kiện. 
Vùng điều kiện phải có tối thiểu là một dòng và một cột. Được tổ chức như sau: 
Nếu chỉ có một điều kiện (điều kiện đơn): 
Nếu có nhiều điều kiện cho cùng một trường mang tính chất hoặc : 
Field 
Điều kiện 
Field 
Điều kiện 1 
Điều kiện 2 
II/ Tổ chức vùng điều kiện 
Nếu điều kiện kết hợp với nhiều trường mang tính chất và : 
Nếu điều kiện kết hợp với nhiều trường mang tính chất hoặc: 
Field1 
Field2 
Điều kiện 1 
Điều kiện 2 
Field1 
Field2 
Điều kiện 1 
Điều kiện 2 
III/ Rút trích dữ liệu 
Đặt con trỏ vào vùng dữ liệu cần rút trích. 
Vào menu Data -> Filter -> Advanced Filter 
 Filter the list, in-place: Kết quả rút trích sẽ xuất hiện ngay trên CSDL ban đầu. 
 Copy to another location: Chọn nơi khác để thể hiện kết quả rút trích. 
 List range: Chọn vùng CSDL ban đầu. 
 Criteria range: Chọn vùng điều kiện rút trích. 
 Copy to: Chọn nơi cần thể hiện kết quả rút trích. 
IV/ Tìm kiếm/Lọc dữ liệu 
Vào menu Data -> Filter -> Autofilter -> Tất cả các trường trong CSDL sẽ được thể hiện dưới dạng một Combo Box gồm một danh sách các giá trị được lọc tự động trong các giá trị hiện tại nó đang chứa. Thông qua các Combo Box này, người dùng có thể tìm kiếm hoặc trích lọc thông tin một cách nhanh chóng. 
BÀI 7  IN ẤN VÀ BẢO VỆ BẢNG TÍNH 
I/ Cài đặt trang in 
Vào menu File -> Page Setup...Tab Page 
In theo chiều dọc 
In theo chiều ngang 
Tỉ lệ in 
Thu nhỏ nội dung để in vào số trang quy định 
I/ Cài đặt trang in (tt) 
Tab Margin: 
 Top: lề trên 
 Left: Lề trái 
 Right: Lề phải 
 Bottom: Lề dưới 
 Header: Khoảng cách từ lề trên giấy đến tiêu đề đầu. 
 Footer: Khoảng cách từ lề trên giấy đến tiêu đề đầu. 
Canh giữa nội dung theo chiều ngang 
Canh giữa nội dung theo chiều dọc 
I/ Cài đặt trang in (tt) 
Tab Header/Footer: 
Chọn tiêu đề đầu trang từ các lựa chọn có sẵn 
Chọn tiêu đề chân trang từ các lựa chọn có sẵn 
Tiêu đề đầu trang do người sử dụng quy đinh 
Tiêu đề chân trang do người sử dụng quy đinh 
I/ Cài đặt trang in (tt) 
Tab Header/Footer: 
 Print Area: Vùng dữ liệu cần in. 
 Row to repeat at top: In lại tiêu đề dòng. 
 Columns to repeat at left: In lại tiêu đề cột. 
 G irdlines: In đ ường l ưới của Excel . 
 Black and White: Chỉ in phần văn b ản , không in m àu nền. 
 Draft quality: Không in các đường viền do người sử dụng kẻ. 
 Row and column headings: In tiêu đề d òng và cột. 
 Down, then over: In từ ̀ trên xuố n g dưới , sau đó qua phải. 
 Over, then down : In từ trái sang phải , sau đó xuống dưới. 
II/ Xem trước khi in 
Vào menu File -> Print Preview 
II/ Xem trước khi in 
Thanh công cụ Print Preview: 
Phóng to, thu nhỏ 
Sang trang tiếp 
Thực hiện in 
Về trang trước 
Định dạng lại trang in 
Hiển thị các đường lề 
Hiển thị chế độ ngắt trang 
Đóng cửa sổ 
Trợ giúp 
III/ Bảo vệ bảng tính 
Đây là một tiện ích của Excel giúp cho bảng tính của người sử dụng không bị người khác sửa đổi công thức hoặc không xem được công thức. 
Để bảo vệ Worksheet nào thì đứng ở Worksheet đó. Vào menu Tools -> Protection -> Protect Sheet... Điền mật khẩu bảo vệ... 
Khi đã bảo vệ bằng cách như trên thì người khác sẽ không thể sửa đổi được công thức trên trang bảng tính (vẫn có thể xem được công thức). 
Để cho người khác không thể xem được công thức trên ô hay trên thanh công thức thì trước khi làm thao tác như trên hãy chọn những ô nào muốn ẩn công thức và vào menu Format -> Cells... Qua tab Protection , check vào mục Hidden. 
HỌC PHẦN TIN HỌC ĐẠI CƯƠNG 
ĐẾN ĐÂY LÀ KẾT THÚC!...