Bài giảng thi chứng chỉ tin học

CHƯƠNG TRÌNH

BẢNG TÍNH ĐIỆN TỬ
MS-EXCEL
NỘi DUNG CHƯƠNG TRÌNH
Bài 1: Làm quen với MS-Excel 2000.
Bài 2: Soạn thảo nội dung bảng tính.
Bài 3: Thao tác định dạng.
Bài 4: Công thức và hàm.
Bài 5: Biểu đồ và đồ thị.
Bài 6: Hoàn thiện trang bảng tính và in ấn.
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
1.1. Khởi động Excel
C1: Nhấn chuột vào nút Start  Programs  Microsoft Excel
C2: Nhấn chuột vào nút trên thanh Microsoft Office
C3: Nhấn đúp chuột vào biểu tượng trên màn hình Desktop.
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
 Giao diện của Excel
 Bảng tính của MS-Excel
Tệp bảng tính (Workbook): Bao gồm nhiều bảng tính
+ Có phần mở rộng ngầm định là .XLS
+ Một tệp bảng tính theo ngầm định có 3 bảng tính:
Là Sheet1, Sheet2, Sheet3
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
- Bảng tính (Sheet): Bảng tính bao gồm các hàng (Row)
và các cột (Column)
Một bảng tính có 256 cột được đánh số theo bảng chữ cái từ A, B, C,…, AA, AB, AC, …, IU, IV.Và có 65536 dòng được đánh số theo số thứ tự từ: 1, 2, 3, …,65536
- Ô tính - cell
Ô là giao của một cột và một dòng.
Tên của ô được xác định bởi cột trước,dòng sau.
Ví dụ: B3, AA2
- Tại một thời điểm chỉ có một ô hiện thời
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
1.2. Tạo bảng tính mới
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
- Nhấn chuột vào biểu tượng New  trên thanh
công cụ chuẩn (hoặc ấn Ctrl + N )
- Nhắp chuột vào thực đơn lệnh File  New --> OK
1.3. Ghi lưu bảng tính vào ổ đĩa.
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
C1: File --> Save (hoặc Save as)
C2: Nhắp chuột vào biểu tượng  trên thanh công cụ
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
1.4. Ghi lưu bảng tính theo kiểu tệp tin khác.
- File -- Save as
1.6. Đóng bảng tính và thoát khỏi Excel
a. Đóng bảng tính:
File --> Close
b. Thoát khỏi Excel
C1: File --> Exit
C2: Nháy chuột vào nút điều khiển đóng cửa sổ
1.5- Mở một hoặc nhiều bảng tính
- File --> Open (Ctrl+O)
Bài 1: LÀM QUEN VỚI MS-EXCEL 2000
Bài 2
SOẠN THẢO NỘI DUNG BẢNG TÍNH
1- Nhập và hiệu chỉnh dữ liệu
2- Thao tác chọn dòng, cột
3- Thao tác sao chép, di chuyển, xoá dữ liệu
4- Các thao tác thêm, bớt, độ rộng với dòng, cột
5- Thao tác với các trang bảng tính
6- Sử dụng tiện ích sắp xếp và lọc dữ liệu
2.1.Nhập và hiệu chỉnh dữ liệu
a. Nhập DL:
- Nháy chuột vào ô cần nhập --> Nhập dữ liệu
- Kết thúc nhập bằng ấn Enter
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
Nhập dữ liệu kiểu số: Dữ liệu kiểu số bao gồm các con số 0-9 % + - , . E. Nếu nhập số âm thi ta gõ dấu “-” trước số đó , nếu nhập số có phần thập phân ta sử dụng dấu “.” để ngăn cách với phần nguyên.
Ví dụ: -1234; 45.67
b. Hiệu chỉnh dữ liệu
- Nháy chuột vào ô cần hiệu chỉnh --> Ấn F2 để hiệu chỉnh
- Kết thúc bằng ấn Enter
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
Nhập dữ liệu kiểu ngày tháng:
Nhập ngày tháng kiểu Anh: Tháng-ngày-năm
Phân cách giữa ngày tháng năm là dấu “-” hoặc dấu “/”
Ví dụ: 04/25/2005 hoặc 12-15-2005
Nhập dữ liệu kiểu văn bản:
Dữ liệu kiểu văn bản bao gồm các ký tự A-Z, các ký tự đặc biệt. Nếu trong công thức muốn sử dụng dữ liệu kiểu văn bản ta phải đặt trong dấu nháy kép. VD: “Lên lớp”
2.2. Chọn dòng, cột, vùng bảng tính
a. Chọn dòng, cột:
- Nháy chuột vào số thứ tự của cột hay của dòng cần chọn
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
b. Chọn vùng bảng tính
- Nháy và giữ nút chuột trái vào ô đầu tiên của vùng cần chọn sau đó rê chuột đến ô cuối cùng của vùng
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
2.3. Điền số thứ tự tự động
B1: Gõ giá trị bắt đầu vào ô đầu tiên
B2: Nhấn và giữ phím Ctrl
B3: Đưa trỏ chuột vào góc phải dưới của ô vừa gõ STT
B4: Khi con trỏ chuột có hình dấu cộng màu đen thì nhấn
và kéo chuột để tạo STT.
2.4. Các thao tác sao chép, di chuyển, xoá dữ liệu
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
a. Sao chép, di chuyển:
B1: Chọn vùng dữ liệu cần sao chép, di chuyển
B2: Edit --> Copy: Sao chép
Cut: Di chuyển
B3: Đặt con trỏ tại vị trí cần dán kết quả
B4: Edit --> Paste
b. Xoá dữ liệu
B1: Chọn vùng dữ liệu cần xoá
B2: Ấn phím Delete để xoá
2.5. Các thao tác thêm/bớt, độ rộng cột, hàng
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
a. Thêm cột, hàng:
B1: Nháy chuột vào số thứ tự của cột hoặc dòng
cần chèn
B2: Insert --> Row: Chèn hàng
--> Column: Chèn Cột
b. Thay đổi độ rộng của cột, hàng
B1: Đưa con trỏ chuột đến đường biên bên phải của tên cột hoặc đường biên bên dưới của tên hàng cần thay đổi độ rộng.
B2: Khi con trỏ chuột có dạng hoặc thì
nhấn và giữ nút chuột trái rồi rê theo chiều mũi tên
2.6. Các thao tác với bảng tính
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
b. Đổi tên bảng tính
- Nháy chuột phải vào tên bảng tính cần đổi tên
- Rename --> Gõ tên --> Enter
c. Xoá bảng tính
- Nháy chuột phải vào tên bảng tính cần xoá
- Chọn Delete
a. Chèn thêm bảng tính
- Nháy chuột phải vào vị trí tên bảng tính cần chèn
- Chọn Insert --> OK
2.7. Sắp xếp và lọc dữ liệu
a. Sắp xếp dữ liệu
B1: Chọn vùng dữ liệu cần sắp xếp
B2: Chọn Data  Sort
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
+ Sort By: Chọn cột cần sắp xếp
+ Ascending: Sắp xếp tăng dần
+ Descening: Sắp xếp giảm dần
B3: Chọn OK
b. Lọc dữ liệu
- Nháy chuột vào vùng bảng tính cần đặt lọc
- Data --> Filter --> AutoFilter
- Nháy chuột vào mũi tên ở cột dữ liệu để
chọn điều kiện lọc:
+ All: lấy tất cả
+ Top 10...: Lấy 10 dòng đầu tiên
+ Custom: Lọc theo điều kiện chúng ta đặt
BÀI 2: SOẠN THẢO NỘI DUNG BẢNG TÍNH
Bài 3
THAO TÁC ĐỊNH DẠNG
1- Định dạng dữ liệu dạng số
2- Định dạng dữ liệu dạng ngày
3- Định dạng Ký tự
4- Căn chỉnh văn bản
5- Gộp/tách ô và Tô viền bảng tính
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
3.1. Định dạng dữ liệu dạng số
- Chọn vùng dữ liệu cần định dạng
- Format  Cells  Chọn thẻ Number
- Chọn mục Number trong khung Category
+ Decimal: Gõ số chữ số thập phân
+ Use 1000 Separator(,):Sử dụng dấu
phảy làm dấu phân cách hàng nghìn
+ Negative numbers: Chọn cách hiển
thị số âm
 Chọn OK
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
3.2. Định dạng dữ liệu dạng ngày tháng
- Chọn vùng dữ liệu cần định dạng
- Format  Cells  Chọn thẻ Number
- Chọn mục Custom trong khung Category
Type: Chọn kiểu hoặc gõ
trực tiếp vào ô Type
- Chọn OK
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
3.3. Định dạng ký tự
- Chọn vùng dữ liệu cần định dạng
- Format  Cells  Chọn thẻ Font
+ Font: Chọn phông chữ
+ Fon style: Chọn kiểu chữ
+ Size: Kích thước chữ
+ Color: Màu chữ
+ Under line: Chọn kiểu
Gạch chân
OK
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
3.4. Căn chỉnh văn bản
- Chọn vùng dữ liệu cần định dạng
- Format  Cells  Chọn thẻ Alignment
+Horizontal: Điều chỉnh dữ liệu
theo chiều ngang (có thể sử
dụng 4 biểu tượng căn chỉnh
trên thanh công cụ).
+Vertical: Điều chỉnh dữ liệu
trong ô theo chiều dọc
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
+ Wrap text: Nếu chọn, khi dữ liệu vượt quá chiều rộng
của cột sẽ tự động xuống dòng
+ Merge cells: Nối ô
+ Orientation: Điều chỉnh hướng của văn bản
 OK
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
3.5. Gộp/ bỏ gộp ô và tô viền cho bảng
a. Gộp/bỏ gộp ô
- Chọn các ô cần gộp, bỏ gộp
- Format  Cells  Chọn thẻ Alignment
- Đánh dấu (hoặc bỏ) ở mục Merge cells
 OK
Có thể sử dụng nút trên thanh công cụ để nối ô
BÀI 3:THAO TÁC ĐỊNH DẠNG BẢNG TÍNH
b. Tô viền cho bảng:
- Bôi đen vùng bảng cần tô viền
- Format  Cells  Chọn thẻ Border
+ None: Không tô viền
+ Outline: Tô viên xung quanh
+ Inside: Tô các đường dọc,
ngang bên trong bảng
+ Border: Tô từng đường viền
riêng lẻ
+ Style: Chọn kiểu đường viền
+ Color: Màu đường viền
 OK
Bài IV
CÔNG THỨC VÀ HÀM
1- Tạo công thức cơ bản
2- Địa chỉ tuyệt đối và địa chỉ tương đối
3- Thao tác với các hàm
BÀI 4: CÔNG THỨC VÀ HÀM
4.1. Tạo công thức cơ bản
a.Tạo công thức số học cơ bản
Các địa chỉ ô kết hợp với nhau bởi các toán tử tạo thành công thức.
Ví dụ: = (A3+B3)/5
 Các toán tử sử dụng trong công thức:
+, -, *, /, ^, %, =, >, <, >=, <=, <>
 Cách nhập công thức:
+ Nháy chuột vào ô cần nhập công thức
+ Nhập dấu ký tự =, sau đó nhập nội dung
công thức
+ Nhấn Enter để kết thúc
BÀI 4: CÔNG THỨC VÀ HÀM
b. Nhận diện các thông báo lỗi
##### : Lỗi do độ rộng cột không đủ
#VALUE : Lỗi do sử dụng dữ liệu hoặc toán tử trong
công thức sai.
#DIV/0! : Lỗi do chia một số cho 0
#NAME? : Lỗi do sử dụng tên sai trong công thức
#N/A: Lỗi do không có dữ liệu để tính toán
#NUM! : Lỗi do sử dụng dữ liệu không đúng kiểu số
BÀI 4: CÔNG THỨC VÀ HÀM
4.2. Địa chỉ tuyệt đối và địa chỉ tương đối
a. Địa chỉ tương đối
Là địa chỉ có dạng: Cột trước dòng sau
Ví dụ: A4, D2, AB4
Khi sử dụng địa chỉ này trong công thức, nếu ta thực hiện sao chép công thức thì địa chỉ này sẽ thay đổi theo vị trí ô
BÀI 4: CÔNG THỨC VÀ HÀM
b. Địa chỉ tuyệt đối
Là địa chỉ có dạng: $Tên cột $Tên dòng
Ví dụ: $A$4, $D$2, $AB$4
Khi sử dụng địa chỉ này trong công thức, nếu ta thực hiện sao chép công thức thì địa chỉ này không bị thay đổi
BÀI 4: CÔNG THỨC VÀ HÀM
c. Địa chỉ hỗn hợp
Là địa chỉ có một thành phần là tuyệt đối, phần còn lại là tương đối
Ví dụ: $A4, D$2, $AB4
Khi sử dụng địa chỉ này trong công thức, nếu ta thực hiện sao chép công thức thì thành phần tuyệt đối không thay đổi, còn thành phần tương đối sẽ thay đổi theo vị trí ô
BÀI 4: CÔNG THỨC VÀ HÀM
d. Địa chỉ vùng
Là địa chỉ được tạo bởi địa chỉ ô trên cùng bên trái và ô dưới cùng bên phải của vùng thông qua dấu hai chấm
Ví dụ: A4:A8, $D$2:$E$6, $A1: C$1
BÀI 4: CÔNG THỨC VÀ HÀM
e. Sao chép công thức
- Chọn ô chứa công thức cần sao chép
- Đưa con trỏ chuột đến góc dưới bên phải của ô, khi đó con trỏ chuột có dạng hình dấu cộng màu đen
- Kéo di chuột theo dòng (hoặc cột) đi qua vùng ô cần sao chép
4.3.1. Giới thiệu về hàm
- Hàm có thể tham gia như một phép toán trong công thức.
- Dạng chung của hàm:
=Tên hàm(Đối số1, đối số 2,...)
Trong đó:
+ Tên hàm: là các hàm có sẵn trong Excel
+ Đối số: có thể là giá trị số, địa chỉ ô, địa chỉ vùng hoặc có thể là một hàm.
Ví dụ: =Sum(A3,D3:F3)
BÀI 4: CÔNG THỨC VÀ HÀM
4.3. Thao tác với các hàm
4.3.2. Các hàm thường dùng
Hàm SUM(): Tính tổng
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= Sum(Đối số 1, đối số 2, ...)
- Ví dụ:
= Sum(B4:F4)
=Sum(4, 5,7)  kết quả: 16
4.3.2. Các hàm thường dùng
Hàm AVERAGE(): Tính trung bình cộng
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= Average(Đối số 1, đối số 2, ...)
- Ví dụ:
= Average(B4:F4)
= Average(7, 9,5)  Kết quả: 7
4.3.2. Các hàm thường dùng
Hàm MAX(), MIN(): Tìm giá trị lớn nhất, nhỏ nhất
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= Max(Đối số 1, đối số 2, ...)
= Min(Đối số 1, đối số 2, ...)
- Ví dụ:
= Max(B4:F4)
= Max(5,7,5,6)  Kết quả: 7
=Min(C2, F6:F9)
4.3.2. Các hàm thường dùng
Hàm COUNT(): Đếm số ô chứa giá trị số
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= Count(Đối số 1, đối số 2, ...)
- Ví dụ:
= Count(B4:F4)
= Count(4, “Tp”,7,8,”PP”)  Kết quả: 3
4.3.2. Các hàm thường dùng
Hàm COUNTA(): Đếm số ô chứa dữ liệu
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= COUNTA(Đối số 1, đối số 2, ...)
- Ví dụ:
= COUNTA (B4:F4)
=COUNTA(4, “Tp”,7,8,”PP”)  Kết quả: 5
4.3.2. Các hàm thường dùng
Hàm LEFT(), RIGHT(): Lấy ký tự bên trái, bên phải
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= Left(Chuỗi ký tự, n): Lấy n ký tự bên trái của chuỗi
ký tự
= Right(Chuỗi ký tự, n): Lấy n ký tự bên phải của chuỗi
ký tự
- Ví dụ:
= Left(“Lớp học 112CP”,3)  Kết quả: Lớp
= Right(“Lớp học 112CP”,3)  Kết quả: 2CP
4.3.2. Các hàm thường dùng
Hàm MID(): Lấy ký tự giữa
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= Mid(Chuỗi ký tự, n1,n2): Lấy n2 ký tự của chuỗi
ký tự và bắt đầu từ ký tự thứ n1
- Ví dụ:
= Mid(“Lớp học 112CP”,4,7)  Kết quả: học 112
4.3.2. Các hàm thường dùng
 Hàm AND(), OR(): Hàm Logic “và”, “hoặc”
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= And(Điều kiện 1, điều kiện 2...)  trả về giá đúng
khi tất cả các điều kiện đều đúng. Và ngược lại.
= Or(Điều kiện 1, điều kiện 2...)  trả về giá đúng
khi 1 trong các điều kiện là đúng. Và ngược lại.
- Ví dụ:
= AND(7>4,6>5,3<1)  Kết quả: Sai
= Or(7>4,6>5,3<1)  Kết quả: Đúng
4.3.2. Các hàm thường dùng
Hàm IF(): Hàm so sánh
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
= IF(Điều kiện, gt1, gt2)  trả về Giá trị gt1
khi điều kiện đúng, trả về giá trị gt2 khi điều kiện sai.
gt1,gt2 có thể là giá trị cụ thể, một biểu thức hoặc một hàm
Ví dụ: = IF(C2>5,” Đỗ”,”Trượt”)
4.3.2. Các hàm thường dùng
Sử dụng các hàm làm điều kiện cho hàm IF():
BÀI 4: CÔNG THỨC VÀ HÀM
Sử dụng các hàm IF() lồng nhau:
4.3.2. Các hàm thường dùng
Hàm VLOOKUP(), HLOOKUP: Hàm tìm kiếm
BÀI 4: CÔNG THỨC VÀ HÀM
- Dạng thức:
=VLOOKUP(GT tìm, Vùng TK, Cột TC, Ct)
Trong đó:
+ GT tìm: là ô, biểu thức chứa giá trị cần tìm kiếm
+ Vùng TK: Là vùng tìm kiếm, địa chỉ vùng là tuyệt đối
+ Cột TC: Là số thứ tự của cột ở vùng tìm kiếm mà ta
cần lấy kết quả tìm kiếm.
+ CT: Là cách tìm kiếm: Có thể là 0 hoặc 1
 Là 1: Cột trái của vùng tìm kiếm phải sắp xếp
 Là 0: Cột trái của vùng tìm kiếm không cần sắp xếp
4.3.2. Các hàm thường dùng
Ví dụ: = VLOOKUP(left(G6,1),$B$17:$D$19,2,1)*F6
BÀI 4: CÔNG THỨC VÀ HÀM
4.3.2. Các hàm thường dùng
Hàm SUMIF(): Tính tổng theo điều kiện
BÀI 4: CÔNG THỨC VÀ HÀM
Dạng thức:
= SumIf(Vùng đối chiếu, Điều kiện, Vùng TT)
+ Điều kiện: Có thể là một hằng số, địa chỉ một ô, một biểu thức
+ Vùng đối chiếu: là vùng (cột) chứa giá trị đối chiếu với điều kiện
+ Vùng TT: Là vùng (cột) ta cần tính tổng
- Ví dụ:
Bài 5
BIỂU ĐỒ VÀ ĐỒ THỊ
1- Tạo biểu đồ, đồ thị
2- Chỉnh sửa biểu đồ, đồ thị
5.1. Các bước tạo biểu đồ, đồ thị
BÀI 5: CÔNG THỨC VÀ HÀM
B1: Chọn kiểu biểu đồ:
- Bôi đen vùng bảng tính cần tạo biểu đồ
- Insert --> Chart (hoặc nháy chuột vào )
5.1. Các bước tạo biểu đồ, đồ thị
BÀI 5: CÔNG THỨC VÀ HÀM
B2: Xác định miền dữ liệu để vẽ biểu đồ:
- Chính là vùng ta đã bôi đen ở B1
- Chọn kiểu phân tích dữ liệu:
+ Rows: Theo hàng
+ Columns: Theo cột
5.1. Các bước tạo biểu đồ, đồ thị
BÀI 5: CÔNG THỨC VÀ HÀM
B3: Thêm các tiêu đề cho biểu đồ:
- Chọn thẻ Titles
5.2. Chỉnh sửa biểu đồ, đồ thị
BÀI 5: CÔNG THỨC VÀ HÀM
a. Thêm tiêu đề chú thích:
- Nháy chuột phải vào biểu đồ
- Chọn Chart Options --> Chọn thẻ Titles
- Chỉnh sửa các tiêu đề --> OK
b. Thay đổi dạng biểu đồ:
- Nháy chuột phải vào biểu đồ
- Chọn Chart Type --> Chọn dạng biểu đồ
- Chọn OK
c. Thay đổi kích thước biểu đồ:
- Nháy chuột vào biểu đồ
- Kéo thả chuột tại các mốc định vị trên đường
biên của biểu đồ
Bài 6
HOÀN THIỆN TRANG BẢNG TÍNH
VÀ IN ẤN
1- Định dạng trang in
2- In ấn văn bản
6.1. Định dạng trang in
BÀI 6: CÔNG THỨC VÀ HÀM
a. Thay đổi lề trang in:
- File --> Page Setup
- Chọn thẻ Margins
+ Top, Bottom: Trên, dưới
+ Left, Right: Trái, phải
+ Header, Footer: Tiêu đề
đầu trang, cuối trang
--> OK
6.1. Định dạng trang in
BÀI 6: CÔNG THỨC VÀ HÀM
b. Thay đổi khổ giấy, hướng in:
- File --> Page Setup
- Chọn thẻ Page
+ Portrait: In dọc
+ Landscape: In ngang
+ Paper size: Chọn khổ giấy
--> OK
6.1. Định dạng trang in
BÀI 6: CÔNG THỨC VÀ HÀM
c. Thêm tiêu đề trang in:
- File --> Page Setup
- Chọn thẻ Header/Footer
6.2. In ấn văn bản
BÀI 6: CÔNG THỨC VÀ HÀM
a. Xem tài liệu trước khi in:
- File --> Print Preview
- Nháy chuột vào nút Close để về soạn thảo
b. In ấn tài liệu:
- File --> Print
Kết thúc Modul 4
Xin cảm ơn!