Bài giảng Excel

BÀI 1: GIỚI THIỆU EXCEL
BÀI 2: LÀM QUEN VỚI EXCEL
BÀI 3: ĐỊNH DẠNG
BÀI 4: CÔNG THỨC VÀ HÀM TRONG EXCEL
BÀI 5: CÁC HÀM THÔNG DỤNG
BÀI 6: CƠ SỞ DỮ LIỆU
BÀI 7: IN ẤN
Nội dung
CHƯƠNG III: BẢNG TÍNH EXCEL
BÀI 1: GIỚI THIỆU EXCEL
1. Giới thiệu Excel:
2. Khởi động Excel:
C2: Chọn Start-Programs-Microsoft Excel
Là một phần trong bộ chương trình Microsoft Office. Là phần mềm xử lý bảng tính được dùng rất thông dụng hiện nay
Ngoài ra ta có thể khởi động Excel bằng nhiều cách khác nhau, tương tự như khởi động bất kỳ một chương trình nào khác trong Windows.
3. Màn hình Excel:
BÀI 1: GIỚI THIỆU EXCEL
4. Kết thúc phiên làm việc với Excel:
Thông thường sau mỗi phiên làm việc với Excel chúng ta phải lưu lại. Ta có thể thực hiện theo một trong các cách sau:
Cách 1: Chọn File => Save;

Cách 3: Nhấn tổ hợp phím Ctrl+S.
Khi lưu một tệp có tên ngầm định Book1,hộp thoại Save As xuất hiện để nhập tên tệp và chỉ ra ổ đĩa, thư mục lưu tệp đó.
Sau khi tệp sổ tính đã được lưu, ta kết thúc phiên làm việc với Excel bằng các cách sau:
* Để đóng tệp sổ tính, sử dụng lệnh File/Close.
* Để thoát khỏi Excel, chọn lệnh File/ Exit.
BÀI 1: GIỚI THIỆU EXCEL
Bảng tính (Sheet): Một bảng tính có trên 4 tỷ ô dữ liệu. Mỗi bảng tính được đặt tên là Sheet# (# là số thứ tự) hoặc một tên gọi cụ thể
I. Cấu tạo bảng tính Excel:
Sổ tay (Book) : Mỗi book gồm từ 1 đến 256 sheet riêng biệt (mặc nhiên là 3)
Cột (Column) : Là tập hợp những ô trong bảng tính theo chiều dọc. Có 256 cột trong bảng tính, được đánh thứ tự lần lượt bằng các mẫu tự A,B,...,Z,AA,AB,...,AZ,...,IA,IB,...,IV
BÀI 2: LÀM QUEN VỚI EXCEL
I. Cấu tạo bảng tính Excel (tiếp):
BÀI 2: LÀM QUEN VỚI EXCEL
Hàng (Row): là tập hợp những ô trong bảng tính theo chiều ngang. Có tổng cộng 65.536 hàng trong bảng tính, được đánh thứ tự lần lượt từ 1 đến 65536.
Ô (Cell): giao của một hàng và một cột gọi là một ô, mỗi ô có tên được xác định bởi tên của cột và tên hàng tạo ra nó.
Vùng bảng tính : Được xác định bởi nhiều ô đứng liên nhau, tên của vùng được xác định bởi tên của ô ở góc trên trái và ô ở góc dưới phải của vùng, phân cách nhau bởi dấu hai chấm (:)
Ví dụ: Ô C6 được tạo ra bởi cột C và dòng 6
Ví dụ: Vùng B5:E10
II. Làm việc với tập tin:
BÀI 2: LÀM QUEN VỚI EXCEL
1. Tạo tập tin mới:
2. Mở tập tin Excel có sẵn:
- Chọn ổ đĩa và thư mục chứa tập tin cần mở trong khung Look in, click chọn tập tin Excel và kích nút Open
3. Lưu tập tin:
- Chọn ổ đĩa và thư mục muốn lưu trong khung Save in, nhập tên trong khung File name, kích nút Save để lưu
III. Cách nhập dự liệu:
BÀI 2: LÀM QUEN VỚI EXCEL
Để nhập dữ liệu vào ô nào bạn kích chuột vào ô đó để chọn, hoặc dùng phím mũi tên để di chuyển đến ô chọn, tùy theo dữ liệu mà bạn có những cách nhập khác nhau, khi nhập xong gõ Enter hoặc dùng các phím mũi tên để di chuyển đến các ô khác.
1. Các kiểu dữ liệu:
Trong một ô chỉ có thể chứa một kiểu dữ liệu. dữ liệu trong ô phụ thuộc vào kí tự đầu tiên.
a. Dữ liệu kiểu số:
Kí tự đầu tiên gõ vào là số, mặt nhiên định dạng ở bên phải ô. Số âm gõ dấu trừ (-) ở phía trước.
b. Dữ liệu kiểu chuổi:
Kí tự đầu tiên là chữ, mặt nhiên định dạng ở bên trái ô. Nếu dữ liệu gõ vào là số, muốn máy hiểu là chuổi thì gõ dấu nháy đơn (‘) phía trước.
III. Cách nhập dự liệu:(tiếp)
BÀI 2: LÀM QUEN VỚI EXCEL
1. Các kiểu dữ liệu:(tiếp)
Nhập kiểu ngày tháng với dấu phân cách là gạch ngang (-) hay dấy (/). Ta có thể định dạng kiểu hiển thị trong phần Format cell
c. Dữ liệu kiểu ngày tháng:
d. Dữ liệu kiểu Công thức:
Là các phép toán hay các hàm
Khi nhập công thức, bạn phải nhập dấu bằng (=), cộng (+) hoặc dấu trừ (-) đứng trước công thức.
2. Chỉnh, sửa dữ liệu
- Chọn ô muốn chỉnh sửa, gõ phím F2 hoặc kích đúp chuột tại ô đó
- Có thể kích chọn ô muốn sửa, sau đó kích vào khung công thức để sửa dữ liệu.
3. Xóa dữ liệu
Chọn ô hoặc vùng muốn xóa, gõ phím Delete hoặc chọn EditClear
IV. Chọn vùng bảng tính:
BÀI 2: LÀM QUEN VỚI EXCEL
Trước khi định dạng bảng tính hoặc làm việc với một ô hay vùng bảng tính bạn cần phải chọn các đối tượng này trước
1. Chọn một ô:
- Phím: Di chuyển đến ô muốn chọn bằng các phím mũi tên
- Chuột: Click vào ô muốn chọn
* Khung màu đen cho biết ô đang được chọn
2. Chọn Vùng:
- Phím: Giữ Shift và dùng các phím mũi tên để mở rộng vùng chọn
- Chuột: Click và rê trên vùng muốn chọn.
3. Chọn một dòng (Row):
Kích vào tên dòng muốn chọn
4. Chọn một cột (Column):
Kích vào tên cột muốn chọn
IV. Chọn vùng bảng tính (tiếp):
BÀI 2: LÀM QUEN VỚI EXCEL
5. Chọn nhiều dòng hoặc nhiều cột liền nhau:
- Kích và rê trên dòng tiêu đề của dòng hoặc cột muốn chọn
6. Chọn nhiều dòng hoặc nhiều cột không liền nhau:
- Nhấn giữ phím Ctrl sau đó kích vào các tiêu đề của dòng hoặc cột muốn chọn.
7. Chọn các ô, vùng không liền nhau:
- Nhấn giữ phím Ctrl sau đó kích chọn các ô, vùng khác
8. Chọn cả bảng tính:
- Phím: Nhấn tổ hợp phím Ctrl +A
- Chuột: Click vào ô giao điểm của tiêu đề dòng và cột
BÀI 3: ĐỊNH DẠNG
I. Định dạng Font chữ:
BÀI 3: ĐỊNH DẠNG
1. Định dạng nhanh (bằng thanh định dạng Formatting):
- Nếu không thấy hiển thị thanh định dạng bạn cần chọn ViewToolbarFormatting.
- Kích mở khung Font để chọn kiểu chữ
- Kích mở khung Size để chọn cỡ chữ
- Các biểu tượng định dạng:Bold (chữ đậm); Italic(nghiêng); Underline(gạch dưới).
2. Định dạng cửa sổ Format:
- Chọn khối cần định dạng, dùng lệnh FormatCell… và kích chọn bảng Font.
- Sau khi chọn xong, click OK để đóng hộp thoại
II. Định dạng dữ liệu kiểu số, ngày tháng, tiền tệ:
1. Định dạng số:
- Chọn khối cần định dạng, dùng lệnh FormatCell… và kích chọn bảng Number, chọn tiếp Number trong khung Category.
- Sau khi chọn xong, click OK để đóng hộp thoại
2. Định dạng ngày tháng:
- Chọn khối cần định dạng, dùng lệnh FormatCell… và kích chọn bảng Number, chọn tiếp Date trong khung Category.
- Do không có dạng ngày tiếng Việt nên phải khai báo bằng cách:
Category: chọn Custom
Type: gõ dd/mm/yyyy
3. Định dạng tiền tệ:
Cũng giống như định dạng số nhưng:
- Ở khung Category chọn Currency.
- Chọn dạng tiền tệ trong khung Symbol
-Nếu muốn đưa một vào kí hiệu riêng chọn Custom trong khung Category
Nhập dạng cần thiết trong khung Type
VD: Nhập định dạng: #,##0
Số 1000 sẽ hiển thị: 1,000
III. Canh lề trong ô:
- Chọn khối cần định dạng, dùng lệnh FormatCell… và kích chọn bảng Alignment (Định dạng kiểu canh dữ liệu trong ô):
Text Alignment: kiểu canh biên dữ liệu
Horizontal / Vertical (theo chiều ngang / dọc)
Text Control: (Kiểm soát chữ)
Wrap text: Nhập được nhiều dòng trong một ô.
Shrink to fit: Tự co cụm để dữ liệu vừa đủ trong ô
Merge cells: Nối các ô đang chọn.
Orientation:
Hiệu chỉnh hướng của chữ
Chỉnh độ nghiêng trong khung Degrees
IV. Hiệu chỉnh dòng và cột, chèn bảng tính đổi tên bảng tính:
1. Thay đổi chiều rộng dòng, cột:
- Để thay đổi độ rộng của dòng hay cột nào, bạn có thể đưa chuột đến giao điểm trên thanh tiêu đề của dòng hay cột đó (trỏ chuột trở thành mũi tên hai chiều), kích và rê để thay đổi chiều rộng.
- Khi kích đúp vào giao điểm của thanh tiêu đề của cột, bạn sẽ có chiều rộng của cột bằng chiều rộng của ô có dữ liệu rộng nhất trong cột.
- Bạn cũng có thể chiều chỉnh chiều cao của cột và chiều rộng của dòng từ trình đơn Format (phải chọn dòng hoặc cột muốn điều chỉnh trước khi sử dụng trình đơn này)
+ FormatRowHeight: (Hiệu chỉnh chiều cao của dòng).
+ FormatRowAutofit: (Tự động hiệu chỉnh độ cao)
+ FormatRowHide: (Ẩn)
+ FormatRowUnHide: (hiện)
+ Có thể thực hiện tương tự như trình đơn FormatColumn để điều chỉnh độ rộng của cột.
2. Chèn dòng, cột:
Di chuyển ô chọn đến trước dòng hoặc cột muốn chèn và sử dụng trình đơn Insert:
- Chèn dòng: InsertRow
- Chèn cột: InsertColumn
Dòng hoặc cột được chèn sẽ đứng sau vị trí của ô chọn.
3. Xóa dòng, cột:
- Chọn dòng hoặc cột muốn xóa
- Chọn EditDelete
Nếu bấm Delete thì chỉ xóa được nội dung trong vùng chọn
4. Chèn bảng tính:
- Dùng lệnh Insertworksheet
5. Đổi tên bảng tính:
Kích đúp chuột vào tên Worksheet ở phía duới cửa sổ và sửa tên hoặc vào Format sheet rename hoặc kích phải chuột vào tên của trang bảng tính phía dưới của sổ chon Rename
6. Xóa bảng tính:
Edit delete sheet hoặc kích phải chuộc vào worksheet chọn delete
7. Sao chép bảng tính:
Editmove or copy … hoặc kích chuột phải vào tên worksheet cần sao chép hay di chuyển chọn Move or copy… xuất hiện của sổ:
-Chọn create a copy để sao chép, nếu không chọn create a copy là di chuyển
-Trong phần To book: chọn bảng tính nhận di chuyển hoặc sao chép
-Trong phần Before sheet vị trí đặt trang sao chép hoặc di chuyển là trước trang nào hoặc ở cuối trang
2. Di chuyễn dữ liệu
Chọn khối cần di chuyển
Click vào biểu tượng cut trên thanh công cụ hoặc EditCut (Ctrl+X)
Chuyển ô chọn đến vị trí đích
Click vào biểu tượng paste trên thanh công cụ hoặc EditPaste (Ctrl+V)
V. Di chuyển, sao chép dữ liệu:
Hoặc: Chọn vùng dữ liệu cần sao chép đưa trỏ chuột đến góc phải vùng dữ liệu trỏ chuột biến thành dấu cộng(+), kích và rê chuột về phía cần sao chép.
1. Sao chép dữ liệu:
Chọn khối cần sao chép
Click vào biểu tượng copy trên thanh công cụ hoặc EditCopy (Ctrl+C)
Chuyển ô chọn đến vị trí đích
Click vào biểu tượng paste trên thanh công cụ hoặc EditPaste (Ctrl+V)
3. Xóa dữ liệu
Chọn khối dữ liệu cần xoá , thực hiện một trong các cách sau:
+ Nhấn phím delete
+ Editclearcontents
+ Right clickclear contents
VI. Chép định dạng, ghi chú
1. Sao chép định dạng:
* Chỉ sao chép định dạng không sao chép nội dung của ô
+ Click vào ô hoặc click và drag lên khối cần sao chép
+ Chọn ô có định dạng cần sao chép
+ Click vào biểu tượng Format painter trên thanh công cụ
2. Chèn ghi chú: Ghi chú của một ô là khi ta đưa trỏ chuột đến góc trên phải của ô đó thì nội dung ghi chú sẽ hiện lên
* Xóa ghi chú: Kích phải chuộtDelete comment
+ Kích phải chuột Insert comment hoặc vào Insertcomment
+ Soạn thảo nội dung ghi chú
VII. Tạo khung và tô màu:
1. Tạo khung:
Trong Excel đã có sẵn đường kẽ của dòng và cột nhưng khi in ra giấy sẽ không thấy các đường này, muốn tạo khung viền cho các ô có thể thực hiện như sau:
- Chọn ô hoặc vùng muốn tạo khung.
- Mở trình đơn FormatCells và chọn bảng Border
- Sau khi chọn xong kích OK
Ngoài ra, để tạo khung nhanh bạn có thể sử dụng biểu tượng Border trên thanh định dạng
2. Tô màu:
- Chọn ô hoặc vùng muốn tô màu.
- Mở trình đơn FormatCells và chọn bảng Patterns
BÀI 4:
CÔNG THỨC VÀ HÀM TRONG EXCEL
I. Cách thiết lập công thức:
1. Địa chỉ tương đối:
Tất cả các ô hoặc vùng đều có tên, tên của ô hoặc vùng là địa chỉ của ô hoặc vùng đó
VD: A4 là tên của ô A4 và cũng là địa chỉ tương đối của ô này
A4:D8 là tên vùng và cũng là địa chỉ tương đối của vùng
* Khi sao chép công thức các địa chỉ tương đối trong công thức sẽ được thay đổi theo phương, chiều, khoảng cách.
2. Địa chỉ tuyệt đối:
Địa chỉ trong công thức khi đứng sau dấu $ sẽ trở thành địa chỉ tuyệt đối.
* Khi sao chép công thức địa chỉ tuyệt đối sẽ không thay đổi.
VD: $A$4 là địa chỉ tuyệt đối của ô A4
$A$4:$D$8 là địa chỉ tuyệt đối của vùng A4:D8
3. Địa chỉ hỗn hợp:
$A4 : Tuyệt đối cột, tương đối dòng.
A$4 : Tương đối cột, tuyệt đối dòng..
4. Cách thành lập công thức:
Công thức trong Excel là sự kết hợp các ô, số, vùng dữ liệu thông qua các toán tử. Khi nhập công thức bắt đầu bằng dấu bằng (=) hoặc các dấu +, -,...
VD: =Sum(A1:A5)+10
* Những báo lỗi thường gặp khi nhập công thức
+ #DIV/0! : Trong công thức có phép toán chia cho 0
+ #NUM!: Các số trong công thức không hợp lý
+ #REF!: Trong công thức có tham chiếu đến ô không hợp lý
+ #VALUE!: Trong công thức dùng sai kiểu dữ liệu.
+ #NAME!: Trong công thức dùng tên ô, vùng không hợp lý, hoặc kiểu dữ liệu chuổi không để trong dấu nháy đôi.
II. Tổng quát về hàm (Function)
Hàm được xem như những công thức định sẵn để thực hiện các yêu cầu tính toán riêng biệt.
Trên ô thực hiện, hàm sẽ cho kết quả là một giá trị, một chuỗi, hoặc một thông báo lỗi
Excel có khoảng hơn 300 hàm thuộc đủ mọi lĩnh vực như: Tài chính, thống kê, toán học, ngày tháng, thông tin,..
2. Cú pháp tổng quát của hàm
= (Đối số1, Đối số2, ..., Đối số n)
Tên hàm: sử dụng theo quy ước của Excel
Đối số: phần lớn hàm trong Excel đều có đối số. Đối số có thể là các trị số, chuỗi, toạ độ ô, tên vùng, công thức, những hàm khác
1. Hàm trong Excel:
Hàm: phải được bắt đầu bằng dấu =
Tên hàm: có thể nhập bằng chữ hoa,chữ thường nhưng không được viết tắt
Đối số có thể có hoặc không tuỳ theo hàm, nhưng phải được đặt trong hai dấu () và giữa các đối số được ngăn cách bởi dấu phẩy
Trong 1 hàm có thể chứa tối đa 30 đối số (nhưng không vượt quá 250 ký tự)
Trong hàm không được chứa khoảng trắng
Có thể sử dụng 1 hàm để làm đối số cho 1 hàm khác mà không cần nhập dấu = trước tên hàm đó
* Ghi chú:
3. Cách nhập hàm vào bảng tính
Nhập từ bàn phím
Chọn ô muốn đưa hàm vào, nhập dấu =
Nhập hàm từ bàn phím (tên, đối số theo đúng cú pháp quy định)
Lựa chọn trong bảng liệt kê tên hàm
Chọn ô muốn đưa hàm vào
Gọi lệnh Insert/Function, xuất hiện hộp thoại:
Or select a category: chọn nhóm hàm
Select a Function , chọn tên hàm
4. Các toán tử trong công thức Excel
Toán tử Số học: +, -, *, /, %,^(mũ)
Thứ tự ưu tiên:
Tính trong ngoặc trước, ngoài ngoặc tính sau
Lũy thừa (^)
Nhân hay Chia (* hay /)
Cộng hay Trừ (+ hay -)
Ví dụ: =2.5+5-(4+4*5)/8 = 4.5
Toán tử so sánh: >, <, >=, <=, =, <>
Cho kết quả là True hay False
Ví dụ: 240<350 ->True; 250=360-100 ->False
Toán tử liên kết chuỗi: &
Ví dụ: ”Da” & “ Nang” -> “Da Nang”
123 & 456 -> 123456
“Excel “ & 2000 -> ”Excel 2000”
5. Liên kết dữ liệu:
a. Liên kết dữ liệu giữa các bảng tính
Trong một Workbook bao gồm nhiều bảng tính (Sheet). Khi tạo công thức ta có thể sử dụng dữ liệu của nhiều bảng tính khác nhau. Muốn lấy dữ liệu của bảng tính khác ta cần nhập tên của bảng tính đó và kí tự ! trước địa chỉ vùng dữ liệu
Ví dụ: =D4+Sheet2!D4
b. Liên kết dữ liệu giữa các tập tin trong excel
+ Mở tập tin muốn liên kết trên cùng, để làm việc với tập tin nào bạn có thể chọn từ trình đơn Windows ên tập tin (hoặc chọn trên thanh Taskbar)
+ Muốn nhập địa chỉ ô ở tập tin khác vào công thức, nhập tên tập tin đó có cả phần mở rộng .XLS trong dấu ngoặc vuông [ ] và bảng tính(Sheet) tất cả đặt trong cặp dấu nháy đơn ‘ ‘ và kí tự ! đứng trước địa chỉ ô.
Ví dụ : = D4+’[ktra.xls]sheet1’!A1
6. Đánh số thứ tự:
Nhập 2 số nào đó vào 2 ô trên cùng 1 cột hoặc trên cùng 1 dòng
Chọn khối cả 2 ô
Đưa trỏ chuột đến góc dưới bên phải, xuất hiện dấu cộng kích và rê chuột theo cột hoặc dòng để sao chép. Chữ số của ô được sao chép đến sẽ có giá trị tăng hoặc giảm theo một công sai nhất định bằng hiệu của hai ô đầu tiên
Hoặc: EditFillSeries  Xuất hiện hộp thoại:
BÀI 5: CÁC HÀM THÔNG DỤNG
I. Nhóm hàm về số:
1. Hàm SQRT: Lấy căn bậc hai
Cú pháp =SQRT(số muốn lấy căn)
VD: =SQRT(16) Kết quả {4}
* Giả sử giá trị ô D4=9 thì : =SQRT(D4) Kết quả {3}
2. Hàm ABS: Lấy giá trị tuyệt đối:
Cú pháp =ABS(số muốn lấy trị tuyệt đối)
VD: =ABS(-15) Kết quả {15}
* Giả sử giá trị ô D4=-10 thì : =ABS(D4)-5 Kết quả {5}
3. Hàm INT: Làm tròn số, trả về giá trị nguyên nhỏ hơn gần nhất
Cú pháp =INT(giá trị số)
VD: =INT(10/3) =INT(3.9); =INT(3); INT(3.2) đều cho kết quả là 3
* Giả sử giá trị ô C4=-4 và D4=-0.2 thì : =INT(C4+D4) Kq là -5
4. Hàm MOD: Lấy giá trị còn dư của một phép chia
Cú pháp =MOD(Số bị chia, số chia )
VD: =MOD(16,3) Kết quả {1}
5. Hàm ROUND: Làm tròn số, có thể xác định bao nhiêu số lẻ
Cú pháp =ROUND(Số cần làm tròn, x )
VD: =ROUND(10/3,0) Kết quả {3}
(x là số cho biết muốn làm tròn đến bao nhiêu số lẻ)
=ROUND(10/3,2) Kết quả {3.33}
x >=0: làm tròn giá trị số theo các số lẻ (bên phải giá trị số)
x <0: làm tròn giá trị số đến các nguyên (bên trái số)
Ví dụ: =Round(12345.54321,2) : 12345.54
=Round(12345.54321,0) : 12346
=Round(12345.54321,-2) : 12300
* Lưu ý:
II. Nhóm hàm thống kê:
1. Hàm AVERAGE: Tính bình quân
Cú pháp = AVERAGE(Giá trị 1, giá trị 2, giá trị 3,...)
Tính giá trị bình quân của các giá trị trong ngoặc.
VD: =AVERAGE(5,6,7) Kết quả {6}
2. Hàm COUNT: đếm các ô có giá trị là số
Cú pháp = CUONT(Vùng hoặc giá trị 1, giá trị 2, ...)
Thông thường COUNT sẽ đếm có bao nhiêu giá trị kiểu số trong vùng cần đếm, điều này rất hữu ích khi cần đếm các ô dữ liệu.
3. Hàm COUNTA: đếm các ô khác rỗng
Cú pháp = COUNTA (Vùng hoặc giá trị 1, giá trị 2, ...)
COUNTA sẽ đếm các ô có chứa dữ liệu trong vùng cần xác định, không phân biệt kiểu dữ liệu
4. Hàm COUNTBLANK: đếm các ô rỗng
Cú pháp = COUNTBLANK (Vùng hoặc giá trị 1, giá trị 2, ...)
5. Hàm MAX: Tìm giá trị lớn nhất
Cú pháp =MAX (Vùng hoặc giá trị 1, giá trị 2, ...)
Tìm và lấy giá trị lớn nhất trong vùng
6. Hàm MIN: Tìm giá trị nhỏ nhất
Cú pháp =MIN (Vùng hoặc giá trị 1, giá trị 2, ...)
Tìm và lấy giá trị nhỏ nhất trong vùng
7. Hàm SUM: Tính tổng
Cú pháp = SUM (Vùng hoặc giá trị 1, giá trị 2, ...)
Tính tổng của các giá trị, nếu các giá trị đó có thể cộng được
VD: =SUM(5,6,7) Kết quả {18}
=SUM(A1:D4) Kết quả: tính tổng các ô chứa dữ liệu số trong vùng A1:D4
III. Nhóm hàm điều kiện:
1. Hàm IF: Trả về các giá trị cho trước với điều kiện nào đó
Cú pháp =IF(Điều kiện, giá trị 1, giá trị 2)
- Nếu điều kiện được thoả mãn, hàm nhận trị 1
- Nếu điều kiện không thoả mãn, hàm nhận trị 2
Ví dụ: =IF(DTB>=15,“Đậu”,“Rớt”)
2. Hàm SUMIF: Tính tổng có điều kiện
Cú pháp =SUMIF(Vùng điều kiện, điều kiện, vùng tính tổng
Nếu các ô trong vùng điều kiện thỏa mãn được điều kiện thì cộng các giá trị tương ứng trong vùng tính tổng
3. Hàm COUNTIF: Đếm có điều kiện
Cú pháp =COUNTIF(Vùng muốn đếm, điều kiện đếm)
Đếm các giá trị thỏa mãn điều kiện trong vùng muốn đếm
VD: Đếm có bao nhiêu điểm 9 trong vùng =COUNTIF(A1:A10,”=9”)
Chú ý: Có thể lấy ô nào đó đề làm điều kiện, và địa chỉ ô không cần để trong dấu ngoặc kép.
VD: Giả sử ô B3 chứa dữ liệu số là 9. =COUNTIF(A1:A10,B3)
IV. Nhóm hàm chuổi
1. Hàm LEFT: Lấy các ký tự bên trái
Cú pháp =LEFT(Dữ liệu chuỗi, số kí tự muốn lấy)
Lấy bao nhiêu kí tự của chuỗi đếm từ bên trái qua
Ví dụ: =LEFT(“TIN HOC”,3) kết quả {TIN}
2. Hàm RIGHT: Lấy các ký tự bên phải
Cú pháp = RIGHT(Dữ liệu chuỗi, số kí tự muốn lấy)
Lấy bao nhiêu kí tự chuỗi đếm từ bên phải qua
Ví dụ: =RIGHT(“TIN HOC”,3) kết quả {HOC}
3. Hàm MID: Lấy từ giữa các ký tự
Cú pháp = MID(Dữ liệu chuỗi, số bắt đầu, x kí tự)
Lấy x lí tự từ vị trí số bắt đầu
Ví dụ: =MID(“TIN HOC”,2,2) kết quả {IN}
4. Hàm UPPER: Đổi chuổi kí tự thành chữ IN HOA
Cú pháp = UPPER (Dữ liệu chuỗi)
Ví dụ: = UPPER (“tin hoc”) kết quả {TIN HOC}
5. Hàm LOWER: Đổi chuổi kí tự thành chữ thường
Cú pháp = LOWER (Dữ liệu chuỗi)
Ví dụ: = LOWER (“Tin HOC”) kết quả {tin hoc}
6. Hàm PROPER: Đổi kí tự đầu của các từ thành chữ In Hoa
Cú pháp = PROPER (Dữ liệu chuỗi)
Ví dụ: = PROPER (“tin học”) kết quả {Tin Hoc}
7. Hàm TRIM: Cắt bỏ kí tự trắng ở đầu và cuối chuỗi
Cú pháp =TRIM (Dữ liệu chuỗi)
Ví dụ: = TRIM (“ Tin học ”) kết quả {Tin hoc}
V. Nhóm hàm Logic:
1. Hàm AND:
Cú pháp = AND (Dữ kiện 1, dữ kiện 2,...)
Trả về giá trị đúng (true) nếu tất cả các dữ kiện đều đúng, trả về giá trị sai (false) nếu một trong các dữ kiện đó sai.
Ví dụ : = AND (5>3,2>0,-2>-8) Kết quả {True}
2. Hàm OR:
Cú pháp = OR (Dữ kiện 1, dữ kiện 2,...)
Trả về giá trị đúng (true) nếu có một trong các dữ kiện đó đúng, trả về giá trị sai (false) tất cả các dữ kiện đó sai.
Ví dụ : = OR (3>3,2>8,4>8,1>0) Kết quả {True}
3. Hàm NOT:
Cú pháp = NOT (Dữ kiện)
Trả về giá trị đúng (true) nếu dữ kiện sai và ngược lại
Ví dụ : = NOT(3>2) Kết quả {False}; = NOT(3<2) Kết quả {True}
VI. Nhóm hàm ngày tháng:
1. Hàm WEEKDAY: Tìm ra thứ trong tuần
Cú pháp = WEEKDAY(Dữ liệu ngày tháng)
Trả về giá trị thứ (từ 1 đến 7) của dữ liệu ngày
Ví dụ : =WEEKDAY (“02/09/2007”) Kết quả {1} là (chủ nhật)
2. Hàm TODAY: Điền ngày hiện hành
Cú pháp = TODAY ( )
Điền ngày hiện hành (lấy ngày theo ngày hệ thống máy tính)
Ví dụ : =DAY (“02/09/2007”) Kết quả {2}
3. Hàm DAY: Lấy giá trị ngày trong tháng
Cú pháp = DAY ( Dữ liệu ngày tháng)
Trả về thứ tự của ngày trong tháng
4. Hàm MONTH: Lấy giá trị tháng trong năm
Cú pháp = MONTH ( Dữ liệu ngày tháng)
Trả về giá trị tháng theo thứ tự số từ 1 đến 12
Ví dụ : =MONTH (“02/09/2007”) Kết quả {9}
VII. Nhóm hàm tìm kiếm:
1. Hàm VLOOKUP: Tìm và lấy giá trị theo cột
CP: = VLOOKUP(Giá trị tìm, vùng tham chiếu, chỉ số cột, cách dò)
Hàm đem giá trị tìm, tìm trong cột đầu tiên của vùng tham chiếu để xác định dòng cần tìm. Sau đó dựa vào chỉ số cột để xác định cột cần tham chiếu lấy giá trị.
- Nếu cách dò = 0 thì mảng giá trị không cần sắp xếp theo thứ tự. Nếu tìm không có trên cột thứ nhất của “Mảng giá trị” thì VLOOKUP báo lỗi #N/A (bất khả thi)
- Nếu cách dò = 1 thì mảng giá trị phải sắp xếp theo thứ tự. Nếu tìm không có trên cột thứ nhất của “Mảng giá trị” thì VLOOKUP sẽ chọn trên cột này phần tử lớn nhất nhỏ hơn giá trị tìm.
2. Hàm HLOOKUP: Tìm lấy giá trị theo dòng
CP: = HLOOKUP(Giá trị tìm,vùng tham chiếu, chỉ số dòng, cách dò)
Hàm đem giá trị tìm, tìm trong dòng đầu tiên của vùng tham chiếu để xác định cột cần tìm. Sau đó dựa vào chỉ số dòng để xác định dòng cần tham chiếu lấy giá trị.
Các tham số giải thích như hàm VLOOKUP
3. Hàm MATCH: trả về số thứ tự của dòng hoặc cột
Cú pháp : MATCH(Giá trị dò, danh sách dò, cách dò)
- Cách dò =0: bảng không cần sắp xếp theo thứ tự, dò không được trả về lỗi #N/A
- Cách dò =1: bảng phải được sắp xếp theo thứ tự tăng dần
- Cách dò =-1: bảng phải được sắp xếp theo thứ tự giảm dần
BÀI 6: CƠ SỞ DỮ LIỆU
1. Khái niệm cơ bản về Cơ sở dữ liệu
Cơ sở dữ liệu(CSDL) là tập hợp thông tin, dữ liệu được tổ chức theo cấu trúc dòng và cột để có thể liệt kê, truy tìm, xoá, rút trích những dòng dữ liệu thoả mãn một tiêu chuẩn nào đó nhanh chóng. Để thực hiện các thao tác này ta phải tạo ra các vùng Database, Criteria và Extract
Vùng Database là vùng CSDL gồm ít nhất hai dòng. Dòng đầu tiên chứa các tiêu đề cột, gọi là tên vùng tin(File name) của CSDL. Tên các vùng tin phải là dữ liệu kiểu chuổi và không được trùng lặp. các dòng còn lại chứa dữ liệu, mỗi dòng gọi là một mẫu tin CSDL(Record)
Vùng Criteria là vùng tiêu chuẩn chứa điều kiện để tìm kiếm, xóa, rút trích,… vùng này ít nhất hai dòng. Dòng đầu chứa tiêu đề. Các dòng còn lại chứa điều kiện.
Vùng Extract là vùng trích dữ liệu chứa các mẫu tin của vùng Database thoả điều kiện của vùng tiêu chuẩn. Vùng Extract cũng có dòng đầu tiên chứa tiêu đề muốn rút trích. Chỉ cần dùng vùng này trong trường hợp thực hiện thao tác rút trích, còn các thao tác tìm kiếm, xoá … không cần dùng đến vùng này.
Ví dụ:
2. Sắp xếp Cơ sở dữ liệu
Di chuyển ô hiện hành vào phạm vi vùng CSDL cần sắp xếp
Gọi lệnh Data / Sort, Excel sẽ tự động lựa chọn phạm vi thực hiện và hiển thị hộp thoại Sort:
My list has: Phạm vi vùng CSDL có dòng tiêu đề (Header row) hoặc không có dòng tiêu đề (No header row)
Sort by (tiêu chuẩn chính): khai báo vùng tin làm cơ sở cho việc sắp xếp
Ascending: sắp xếp theo chiều tăng dần
Descending: sắp xếp theo chiều giảm dần
Then by (tiêu chuẩn phụ): thực hiện tiếp việc lựa chọn trường (khi cần sắp xếp những bản ghi bị trùng lắp ở tiêu chuẩn chính)
Chú ý: tiêu chuẩn phụ thứ hai chỉ được dùng đến khi có những bản ghi bị trùng lắp ở tiêu chuẩn chính và tiêu chuẩn phụ thứ nhất
OK: thực hiện việc sắp xếp
3. Trích lọc dữ liệu:
Khi muốn tìm kiếm hay liệt kê các mẫu tin(Record) theo một yêu cầu nào đó() trên Database đó. Trong trường hợp này ta dùng phương pháp trích lọc(Filter). Có hai phương pháp trích lọc.
a. Phương pháp trích lọc tự động(Autofilter )
Kích vào nút mũi tên để chọn các giá trị của cột cần xác định điều kiện trích lọc. khi đó các mẫu tin thoả điều kiện sẽ được hiển thị còn các mẫu tin không thoả sẽ được ẩn đi
Đưa con trỏ vào vùng trích lọc (hoặc chọn vùng trích lọc)
Vào trình đơn DataFilterAutofilter
Khi đó các nút mũi tên sẽ xuất hiện ở các cột trong vùng cơ sở dữ liệu
Muốn hiển thị lại toàn bộ mẫu tin chọn trình đơn DataFilterShow all
Muốn bỏ trích lọc: chọn DatafilterAutofilter một lần nữa
b. Phương pháp lọc chi tiết (Advance filter)
Thay vì dùng AutoFilter (lọc tự động), ta có thể dùng Advanced Filter (lọc theo những yêu cầu phức tạp)
- Chọn DataFiler Advanced Filter xuất hiện hộp thoại:
+ Filter the list, in-place:các mẫu tin đã trích lọc sẽ xuất hiện trong vùng dữ liệu này
+ Nếu chọn unique records only: Dữ liệu lọc ra nếu có các dòng trùng nhau chỉ hiển thị một dòng
+ Nhập vùng CSDL trong khung List range,
+ Nhập vùng điều kiện trong khung Criteria range
+ Chọn OK để thực hiện trích lọc
4. Phương pháp rút trích:
- Sao chép dòng tên các trường ra làm 2 dòng ở khu vực ngoài cơ sở dữ liệu, dòng 1 cánh dòng 2 ít nhất là 1 dòng, nên cánh 2 dòng
- Trong hộp thoại Advance filter chọn Copy to another location
- List range: Chỉ định khu vực chứa dữ liệu
- Chọn OK để thực hiện trích lọc
Để việc rút trích đạt hiệu quả nên chuẩn bị những công việc sau:
- Vào trình đơn Tool/Option, chọn Transistion, trong khung Sheet Option, chọn hộp kiểm tra Transistion Formular Evaliation, chọn OK
- Đưa vệt sáng vào vùng CSDL, chọn Data/Filter/Advanced Filter.
- Criteria range, Chỉ vùng điều kiện gồm 2 hoặc 3 dòng: Dòng đầu chứa tên các trường, và các dòng chứa điều kiện.
* Nếu các điều kiện nằm trên một dòng đó là điều kiện hội
* Nếu các điều kiện nằm trên hai dòng đó là điều kiện hoặc
5. Các dạng tiêu chuẩn:
a. Tiêu chuẩn số:
Ô điều kiện có kiểu số
Ví dụ: tiêu chuẩn SO CON bằng 2
b. Tiêu chuẩn chuổi
Ô điều kiện có kiểu chuổi
Ví dụ: tiêu chuẩn CHUC VU là “GĐ”
Trong ô điều kiện có thể chứa kí tự đại diện * hoặc ?
Ví dụ:
c. Tiêu chuẩn so sánh
Ô điều kiện chứa toán tử so sánh kèm với giá trị so sánh. Các toán tử so sánh gồm >,>=,<,<=,=,<>
Ví dụ:
d. Tiêu chuẩn công thức:
Ô điều kiện có công thức.
- Trong trường hợp có sử dụng tiêu chuẩn này cần lưu ý:
Ô tiêu đề của vùng tiêu chuẩn phải là một tiêu đề khác với các tiêu đề trong vùng CSDL
- Trong ô điều kiện phải lấy địa chỉ của ô trong mẫu tin đầu tiên để so sánh
Ví dụ: Tiêu chuẩn là kí tự đầu tiên của Ho Tên khác “H”
Công thức trong ô điều kiện là: =LEFT(A3,1)<>”H”
e. Liên kết tiêu chuẩn
Có thể tìm kiếm, xóa hay rút trích các mẫu tin trong vùng CSDL bằng cách giao hay hội của nhiều điều kiện khác nhau:
Nếu các ô điều kiện khác cột có tính chất giao
Nếu các ô điều kiện khác dòng có tính chất hội
Ví dụ:
Đây là vùng tiêu chuẩn thể hiện điều kiện:
SO CON bằng 1 và CHUC VU là NV hay SO CON >2 và CHUC VU là GD
5. Sử dụng hàm cơ sở dữ liệu :
Cú pháp chung của các hàm CSDL:
Tên-hàm(Database,Field,Criteria)
Database: Vùng CSDL
Field: Tên tiêu đề cột hay địa chỉ ô chứa tiêu đề cột hoặc thứ tự cột ( bắt đầu từ 1) của vùng CSDL
Criteria: Vùng tiêu chuẩn(Điều kiện )
5.1. Sử dụng hàm cơ sở dữ liệu :
a. DSUM: Hàm tính tổng trên cột (Field) của vùng dữ liệu tại những mẫu tin thoả mãn điều kiện
Ví dụ: Cho biết tổng lương của các nhân viên thuộc phòng tổ chức( TC)
= DSUM(A2:D9,”Lương”,C11:C12)
Hoặc = DSUM(A2:D9,D2,C11:C12)
Hoặc = DSUM(A2:D9,4,C11:C12)
b. DMAX: Cho giá trị lớn nhất trên cột của vùng dữ liệu tại những dòng thoả mãn điều kiện vùng tiêu chuẩn
Ví dụ: Cho biết nhân viên lớn tuổi nhất của phòng TC
= DMAX(A2:D9,2,C11:C12)
= DMAX(A2:D9,”Tuổi”,C11:C12)
= DMAX(A2:D9,B2,C11:C12)
c. DMIN: Cho giá trị nhỏ nhất trên cột của vùng dữ liệu tại những dòng thoả mãn điều kiện vùng tiêu chuẩn
Ví dụ: cho biết nhân viên nhỏ tuổi nhất của phòng TC
= DMIN(A2:D9,B2,C11:C12)
= DMIN(A2:D9,2,C11:C12)
= DMIN(A2:D9,”Tuổi”,C11:C12)
d. DAVERAGE: Cho giá trị trung bình trên cột của vùng dữ liệu tại những dòng thoả mãn điều kiện vùng tiêu chuẩn
Ví dụ: Tính lương bình quân của những người thuộc phòng tổ chức
= DAVERAGE(A2:D9,4,C11:C12)
e. DCOUNT: Hàm này tính số phần tử kiểu số trên cột của vùng dữ liệu tại những dòng thoả mãn điều kiện vùng têu chuẩn
Ví dụ: Tại ô D11 chứa chuổi “Tuổi”, ô D12 chứa số 26
Tính số người có độ tuổi bằng 26
= DCOUNT(A2:D9,2,D11:D12)
f. DCOUNTA: Hàm này tính số phần tử khác trống trên cột của vùng dữ liệu tại những dòng thoả mãn điều kiện
g. Tổng hợp số liệu theo nhóm:
Trên một CSDL có nhu cầu tổng hợp dữ liệu theo nhóm ví dụ như mặt hàng, theo ngày, theo đơn vị … Tính năng Subtotal của Excel dùng để phân tích các nhóm dữ liệu đồng thời chèn vào cuối mỗi nhóm những dòng thống kê, tính toán và một dòng tổng kết ở cuối phạm vi
Các bước thực hiện:
+ Sắp xếp CSDL theo khoá là cột cần thực hiện tổng hợp thống kê (Datasort)
+ Đưa con trỏ vào vùng CSDL. Chọn lệnh DataSubtotals  xuất hiện hộp thoại:
+ Trong mục At each change in: chọn vùng tin cần tạo nhóm tổng hợp
+ Trong mục Use function: chọn hàm cần tính toán/ thống kê
+ Trong mục Add Subtotal to: chọn những vùng tin cần tính toán/ thống kê
+ Chọn Ok hoặc gỏ Enter để thống kê
+ Nếu huỷ bỏ tính năng tổng hợp, thống kê theo nhóm trong CSDL vừa làm chọn Datasubtotal rồi chọn Remove All
6. Biểu đồ :
6.1. Giới thiệu biểu đồ
Biểu đồ là sự biểu diễn các con số, dữ liệu bằng hình ảnh, để người đọc nắm bắt được các thông tin một cách trực quan
Khối dữ liệu: Để tạo được biểu đồ cần có ít nhất 2 ô dữ liệu số khác nhau. Thông thường dòng hoặc cột dữ liệu số sẽ đi kèm với dòng dữ liệu chuổi, các ô chuổi này được gọi là tiêu đề của nhóm dữ liệu số (catelogy lable)
Các thành phần của biểu đồ:
Chart title: Tiêu đề của biểu đồ
X title: Tiêu đề trục x
Y title: Tiêu đề trục y
Data series: Đường biểu diễn của biểu đồ
Legend: Dãy dữ liệu so sánh
Catelogy lable: Tiêu đề dữ liệu số
6.2. Các bước tạo biểu đồ :
Bước 1: Chọn khối dữ liệu muốn tạo biểu đồ hoặc di chuyển ô chọn đến vùng này
Bước 2: Kích vào biểu tượng Chart Wizard trên thanh công cụ hoặc InsertChart. Xuất hiện hộp thoại:
Bước 3: Chọn loại biểu đồ trong khung Chart type, chọn dạng biểu đồ trong khung Chart Sub-type, sau khi chọn xong kích vào nút Next
Bước 4: Xuất hiện hộp thoại :
Trong hộp thoại này biểu đồ mẫu đã được tạo, nếu không đồng ý với các thông số trong biểu đồ có thể định lại vùng dữ liệu trong khung Data range, chọn dạng biểu đồ theo dòng hoặc theo cột trong mục Series in, sau đó chọn Next
Bước 5: Xuất hiện hộp thoại:
Trong hộp thoại này nhập tiêu đề trong khung Chart title, và các tiêu đề khác cột ,dòng, sau đó chọn Next
Bước 6: Xuất hiện hộp thoại
Trong hộp thoại này chọn vị trí đặt biểu đồ As new sheet: ở bảng tính mới, As object in: ở bảng tính hiện hành. Chọn Finish để kết thúc việc tạo biểu đồ
6.3. Hiệu chỉnh biểu đồ :
Muốn hiệu chỉnh lại đối tượng nào trong biểu đồ kích chuột vào đối tượng đó
- Di chuyển: Click and drag đối tượng
- Thay đổi kích cở: Sau khi kích chọn, đưa trỏ chuột đến một trong các góc của đối tượng click and drag để thay đổi
Định dạng kiểu chữ và màu nền:
Chọn các đối tượng: Formatselected…và thực hiện chọn các yếu tố định dạng ví dụ như font, màu nền (Patterns)
- Xoá đối tượng: Kích phải chuột lên đối tượng chọn clear hoặt chọn đối tượng gỏ phím Delete
BÀI 7
IN ẤN
I. Định dạng trang in :
Thực hiện lệnh FileSetup, xuất hiện hộp thoại:
II. Xem trước khi in :
Dùng lệnh FilePrint Preview, một hộp thoại xuất hiện cùng trang văn bản:
III. In dữ liệu của bảng tính :
Chọn File Print (Ctrl + P) hoặc kích vào biểu tượng Print trên thanh công cụ
Khai báo các thông tin cần thiết trong hộp thoại Print như:
+ Print range: Phạm vi in: All : tất cả, From.. To… : từ trang …đến trang
+ Copies: số bản in
+ Selection: in vùng được chọn, Selected Sheet: in những bảng tính được chọn, Entire Workbook: in toàn bộ bảng tính có dữ kiện