Giáo trình Bảng tính điện tử MS Excel
LỜI MỞ ĐẦU
Chương trình đào tạo "Tin học văn phòng chuyên nghiệp" được Công ty
Điện toán và truyền số liệu (VDC) xây dựng và phát triển theo các chuẩn quốc tế về
ứng dụng công nghệ thông tin (CNTT) cho các đối tượng người dùng (end user).
Chương trình cung cấp kiến thức về các khái niệm của CNTT, các kỹ năng sử dụng
máy tính cá nhân và các phần mềm ứng dụng chung ở mức từ cơ bản đến nâng cao.
Tài liệu học tập và giáo trình bằng tiếng Việt, luôn được cập nhật các công nghệ
mới nhất, được cung cấp miễn phí cho học viên khi đăng ký học trên lớp hoặc đăng
ký thi học phần.
Chương trình "Tin học văn phòng chuyên nghiệp" gồm 7 học phần, mỗi
học phần cung cấp cho học viên một nội dung kiến thức cụ thể, độc lập. Hệ thống
thi sát hạch hoàn toàn độc lập với quá trình học trên lớp. Khi đăng ký thi sát hạch,
học viên được phát miễn phí tài liệu học tập, học viên có thể tự học và tham dự kỳ
thi sát hạch mà không cần phải đến lớp học. Học viên phải thực hiện một bài thi
thực hành trên máy tính trong vòng 120 phút và một bài thi lý thuyết trực tuyến trên
mạng trong 60 phút. Điểm đạt yêu cầu là 70/100 điểm cho bài thi thực hành và
60/100 điểm cho bài thi lý thuyết. Các học phần này gồm:
1- Các khái niệm cơ bản về CNTT
2- Sử dụng hệ điều hành và quản lý máy tính
3- Soạn thảo văn bản (MS Word)
4- Bảng tính điện tử (MS Excel)
5- Kỹ năng trình diễn (MS PowerPoint)
6- Cơ sở dữ liệu (MS Access)
7- Ứng dụng của mạng LAN và mạng Internet trong công việc
Chương trình “Tin học văn phòng chuyên nghiệp” đầu tiên được khai
giảng vào ngày 15/08/2005, có các lớp học sáng, chiều và tối, tuần học 3 buổi. Các
học viên sẽ được tư vấn đầy đủ về nội dung học, thời gian học, hình thức thi…Mọi
chi tiết xin liên hệ:
TRUNG TÂM ĐÀO TẠO CÔNG NGHỆ THÔNG TIN
Toà nhà Fafilm, Tầng 4, số 19 Nguyễn Trãi, Thanh Xuân, Hà Nội
2.10. Bảo vệ ô 50
BÀI TẬP CHƯƠNG 2 52
Chương 3 55
HÀM TRONG EXCEL 55
3.1. Quy tắc sử dụng hàm 55
3.2. Nhập hàm vào bảng tính 56
3.3. Một số hàm thường dùng 58
3.3.1. Hàm ngày tháng 58
3.3.2. Hàm ký tự 58
3.3.3. Hàm toán học 60
3.3.4. Hàm Logic 64
3.3.5. Hàm thống kê 65
3.3.6. Hàm Tìm kiếm và Tham chiếu 69
BÀI TẬP CHƯƠNG 3 73
Chương 4 77
5
Giáo trình Bảng tính điện tử MS Excel
BIỂU DIỄN ĐỒ THỊ TRONG EXCEL 77
4.1. Các bước tạo đồ thị mới 77
4.2. Thiết lập lại đồ thị 80
4.3. Chỉnh sửa đồ thị 80
BÀI TẬP CHƯƠNG 4 85
Chương 5 86
QUẢN TRỊ DỮ LIỆU 86
5.1. Khái niệm cơ bản 86
5.2. Tạo cơ sở dữ liệu trong Excel 86
Hình 5.2 87
5.3. Sử dụng form để nhập liệu và tìm kiếm bản ghi dữ liệu 87
5.4. Sắp xếp dữ liệu 88
5.5. Lọc dữ liệu 89
7.4.1. Chuyển các kết quả của Excel cho Word 126
7.4.2. Chuyển bảng đã gõ bằng Word cho Excel 127
BÀI TẬP THỰC HÀNH ÔN TẬP 128
7
Giáo trình Bảng tính điện tử MS Excel
8
Giáo trình Bảng tính điện tử MS Excel
Chương 1
CÁC KHÁI NIỆM VÀ CHỨC NĂNG CƠ BẢN
Bảng tính Excel là một công cụ tiện ích để thực hiện công việc thống kê, kế
toán trong một đơn vị, doanh nghiệp. Nó cho phép nhanh chóng thực hiện các phép
tính thông thường để kết xuất dữ liệu. Đồng thời còn có nhiều tính năng nâng cao
khác như: tạo những đồ thị, biểu đồ minh hoạ số liệu, tạo biểu mẫu phân tích tổng
kết, khai thác hệ thống cơ sở dữ liệu….
1.1. Khởi động
Trên máy tính của bạn đã cài đặt bộ chương trình ứng dụng Microsoft Office.
Để khởi động Microsoft Excel, ta có thể thực hiện theo một số cách sau:
• Trên màn hình Desktop, nếu có biểu tượng Microsoft Excel thì kích đúp
chuột vào biểu tượng đó để khởi động chương trình
Hình 1.1: Biểu tượng chương trình Excel trên màn hình Desktop
• Hoặc kích chuột vào Start Programs Microsoft Excel như hình 1.2
Hình 1.2
1.2. Màn hình
a. Các thành phần của màn hình Excel
9
Giỏo trỡnh Bng tớnh in t MS Excel
Mn hỡnh ca Excel l mt ca s ó c phúng to v trụng gn ging mn
hỡnh ca Word, bao gm cỏc thnh phn sau (Hỡnh 1.3)
Hỡnh 1.3
Thanh tiờu (Title bar): dũng trờn cựng ca mn hỡnh, khi mi khi ng
hiển thị tên của các bảng tính (khi chúng chưa được đặt tên, tại đây ghi Sheet1,
Sheet2, , Sheet16). Bên trái là các nút chuyển tới các bảng tính.
• Thanh cuộn Dọc (Vertical scroll bar), Thanh cuộn Ngang (Horizontal scroll
bar) giống như trong Word.
• Cửa sổ Bảng tính (Worksheet Window) chiếm phần lớn màn hình, dùng để
nhập dữ liệu, tính toán, vẽ đồ thị,…
b. Cấu trúc bảng tính
• Bảng tính (Sheet): Trong hình trên ta có một trang bảng tính trắng sau khi
khởi động Excel. Như vậy, một bảng tính gồm nhiều ô, có thể có trên 4 triệu ô, mỗi
ô chứa một dữ liệu như một đơn vị nhỏ nhất. Có thể nhập dữ liệu hoặc tính toán đối
với dữ liệu trong các ô.
• Sổ bảng tính (Workbook): Nhiều bảng tính tạo thành một sổ bảng tính. Một
sổ bảng tính có thể gồm từ 1 đến 255 bảng tính (sheet) riêng biệt. Trong hình vẽ
trên ta có một sổ bảng tính trắng vừa được tạo ra gồm 3 bảng tính, được đặt tên mặc
định lần lượt là sheet1, sheet2, sheet3. Có thể đặt tên cho các bảng tính này bằng
cách: nhấn đúp chuột vào thẻ tên bảng rồi gõ tên mới. Hoặc có thể nhấn phải chuột
để xuất hiện bảng chọn nóng, chọn rename rồi gõ tên mới. Một sổ bảng tính được
ghi thành một tệp (file). Theo mặc định có tên phần mở rộng là *. XLS
• Cột (Column): Là tập hợp các ô trong bảng tính theo chiều dọc được đánh
thứ tự bằng chữ cái (từ trái sang phải bắt đầu từ A, B, C, AA, AB đến IV, tổng số
có 256 cột). Ngoài cùng bên trái là nút chọn (đánh dấu khối) toàn bộ bảng tính.
• Hàng (Row): Là tập hợp các ô trong bảng tính theo chiều ngang được đánh
thứ tự bằng số từ 1 đến 16.384.
• Ô (Cell): Là giao của một cột và một hàng. Địa chỉ của ô được xác định bằng
cột trước, hàng sau, ví dụ C4, AB25.
• Vùng (Domain): Là một tập hợp những ô kế cận nhau tạo thành một hình chữ
nhật. Một vùng được xác định bởi tọa độ ô đầu (góc bên trái) và tọa độ ô cuối (góc
dưới phải).
11
Giáo trình Bảng tính điện tử MS Excel
gọi hàm khác.
Sort Ascending: Sắp xếp tăng
Sort Descending: Sắp xếp giảm
Chart Wizard: Vẽ đồ thị
Drawing: Vẽ hình
Zoom Control: Phóng to thu nhỏ bảng tính
Help: Trợ giúp
Comma Style: Dấu tách nghìn, triệu, tỷ
Percent Style: Nhân với 100 và điền dấu %
Center Across Columns: Căn vào giữa của nhiều ô
Increase Decimal: Tăng thêm một số thập phân
Font Color: Màu chữ
Currency Style: Điền dấu tiền tệ
Decrease Decimal: Giảm đi một số thập phân
Boders: Đường viền
Color: Màu nền
Giáo trình Bảng tính điện tử MS Excel
g. Ý nghĩa các thành phần của thanh công thức (Formular)
Hình 1.6
1.3. Ra khỏi Excel
Thực hiện theo một trong các cách sau :
- Vào thực đơn File Exit
hoặc
- Ấn tổ hợp phím Alt + F4
1.4. Các thao tác với bảng tính
a. Lưu (ghi) bảng tính lên đĩa
Chọn biểu tượng Save hoặc vào thực đơn File Save. Nếu đây là lần đầu tiên
thực hiện thao tác này với bảng tính, ta phải đặt tên cho bảng tính bằng cách gõ tên
tệp cần đặt trong hộp File Name theo quy tắc đặt tên tệp của DOS. Excel sẽ tự gán
kiểu .XLS cho nó. Tên bảng tính sẽ xuất hiện trên thanh tiêu đề của cửa sổ.
d. Chèn thêm một bảng tính
Thông thường sổ bảng tính thường hiển thị mặc định ba bảng tính. Nếu muốn
chèn thêm các bảng tính, ta thực hiện như sau:
- Vào thực đơn Insert Worksheet
hoặc
- Kích phải chuột trên thanh thẻ tên bảng tính, chọn Insert.
e. Xoá bớt một bảng tính
Muốn xoá bảng tính nào không cần dùng nữa, ta thực hiện như sau:
- Vào thực đơn Edit Delete Sheet
hoặc
- Đặt con trỏ chuột vào bảng tính cần xóa, kích phải chuột, chọn Delete sheet
g. Đổi tên bảng tính
Thông thường tên mặc định của bảng tính là sheet1, sheet2, Để đáp ứng yêu
cầu công việc, ta có thể thực hiện đổi tên bảng tính như sau:
- Kích đúp chuột vào thẻ tên (tức là vào tên bảng tính, sau đây chúng ta quy
ước gọi là thẻ tên) trên thanh thẻ tên.
hoặc
- Vào thực đơn Format Sheet Rename
hoặc
- Đặt con trỏ chuột vào thẻ tên cần đổi, kích phải chuột, chọn Rename (Hình
1.9)
Hình 1.9
16
Giáo trình Bảng tính điện tử MS Excel
- Gõ vào tên mới, ấn phím Enter
h. Sao chép / di chuyển một bảng tính
Cách 1: Giữ phím Ctrl trong khi kéo thả thẻ tên tại một thẻ tên khác (Sheet
khác). Nếu không giữ Ctrl bảng tính sẽ được chuyển đi.
Cách 2: Vào thực đơn Edit Move or Copy Sheet. Chọn vị trí đặt bảng tính
hiện tại trước bảng tính nào trong khung Before Sheet. Nếu đánh dấu chọn vào
Để bỏ tình trạng bảo vệ:
- Vào thực đơn Tools Protection Unprotect Sheet hay Unprotect
Workbook.
- Nếu có mật khẩu, phải gõ vào, nếu đúng ta mới thực hiện các thao tác với
bảng tính.
m. Chọn nhiều bảng tính
- Liền kề: Kích chuột vào thẻ tên đầu, ấn phím Shift trong khi kích chuột vào
thẻ tên cuối.
- Cách nhau: ấn phím Ctrl trong khi lần lượt kích chuột vào các thẻ tên.
- Để bỏ việc chọn một bảng tính nào: ấn phím Ctrl trong khi kích chuột vào thẻ
tên của bảng tính đó.
1.5. Quản lý màn hình hiển thị
a. Công cụ Zoom
Công cụ Zoom cho phép phóng to hoặc thu nhỏ màn hình hiển thị theo tỷ lệ
phần trăm. Để thực hiện ta làm như sau:
- Vào thực đơn View Zoom
- Chọn tỷ lệ phóng to thu nhỏ phù hợp theo mục đích hiển thị.
b. Thanh công cụ
Để quản lý các thanh công cụ hiển thị trên màn hình cửa sổ Excel, ta thực hiện
như sau:
- Vào thực đơn View Toolbars
18
Giáo trình Bảng tính điện tử MS Excel
- Muốn hiển thị thanh công cụ nào thì kích chuột vào tên thanh công cụ đó để
xuất hiện dấu
- Muốn đóng thanh công cụ đang mở trên màn hình hiển thị thì kích chuột vào
thanh công cụ đó, khi đó dấu tích sẽ không còn nữa.
Nếu trên màn hình hiển thị không có thanh công thức hoặc thanh trạng thái, ta
thực hiện như sau:
- Vào thực đơn View Formular Bar, khi đó xuất hiện dấu thì
bng tớnh l Bng lng
- M li tp Bi tp1, nhp bng d liu sau vo sheet 2, t tờn bng tớnh l
Giỏ th
STT Loại thẻ Mệnh giá Thời lợng Thời hạn
1 Thẻ KM 1 30.000 đồng 3 giờ 50 ngày
2 Thẻ KM 2 50.000 đồng 6 giờ 50 ngày
3 Thẻ 100.000 100.000 đồng 13 giờ 500 ngày
4 Thẻ 200.000 200.000 đồng 28 giờ 500 ngày
5 Thẻ 300.000 300.000 đồng 45 giờ 500 ngày
6 Thẻ 500.000 500.000 đồng 80 giờ 500 ngày
20
Giỏo trỡnh Bng tớnh in t MS Excel
Chng 2
X Lí D LIU TRONG BNG TNH
2.1. Cỏc kiu d liu
Trong mi ụ ch cú th cha mt kiu d liu. Kiu d liu ca ụ ph thuc vo
ký t u tiờn gừ vo. Cỏc kiu d liu trong mt ụ c phõn ra nh sau:
a. Dng chui (Text)
- Bt u bi cỏc ch cỏi a n z hoc A n Z
- Nhng d liu chui dng s nh: s in thoi, s nh, mó s, v.v khi nhp
vo phi bt u bng du nhỏy n () v khụng cú giỏ tr tớnh toỏn.
- Theo mc nh, d liu dng chui c cn sang trỏi ụ.
b. Dng s (Number)
Bt u bi :
- Cỏc s t 0 n 9.
- Cỏc du +, - , (, *, $ (hoc mt du n v tin khỏc tu thuc vo vic t cỏc
thụng s quc t ca Windows).
- Theo mc nh, d liu dng s c cn sang phi ụ.
c. Dng cụng thc (Formulas)
Bt u bi cỏc du = hoc du +. Sau khi n phớm Enter cụng thc nhp
• Theo mặc định, dữ liệu dạng ngày tháng đuợc căn sang phải ô.
2.2. Các toán tử trong công thức
a. Toán tử số
+ céng
- trõ
* nh©n
(vÝ dô = 10*50 cho kÕt qu¶ 500)
/ chia = 126/3
cho kÕt qu¶
42
^ luü thõa = 5^2
cho kÕt qu¶
25
% phÇn tr¨m = 50%*600 cho kÕt qu¶ 300
Thứ tự ưu tiên của các phép toán như sau: luỹ thừa trước rồi đến nhân chia và
sau cùng mới đến cộng trừ. Các phép toán cùng mức ưu tiên (như nhân chia hoặc
cộng trừ) thực hiện từ trái sang phải. Muốn thay đổi thứ tự ưu tiên, dùng các cặp
ngoặc tròn, toán tử trong cặp ngoặc ở sâu nhất sẽ được thực hiện trước. Ví dụ: các ô
22
Giáo trình Bảng tính điện tử MS Excel
A1, B1, C1 chứa các số 2, 3, 4, nếu trong ô D1 gõ =A1+B1*C1 sẽ được kết quả 14,
gõ =(A1+B1)*C1 sẽ được kết quả 20.
b. Toán tử chuỗi
• Toán tử &: Nối chuỗi
Ví dụ: =”Tin “&”hoc”, được kết quả là chuỗi “Tin học”
c. Toán tử so sánh
Toán tử Ý nghĩa
> lớn hơn
>= lớn hơn hoặc bằng
< nhỏ hơn
- Đánh dấu khối miền cần điền dữ liệu (ví dụ A1:B5)
- Gõ vào dữ liệu (ví dụ số 2000)
- Kết thúc việc nhập bằng cách ấn tổ hợp phím Ctrl + Enter. Kết quả là: miền
A1:B5 sẽ được điền kín bởi số 2000.
c. Dữ liệu trong các ô tuân theo một quy luật
Chuỗi số với bước nhảy là 1
- Đưa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để đánh số thứ tự
cho một số ô bắt đầu từ 1, ta gõ 1.
- Trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen, ấn phím Ctrl trong khi
kéo và thả chuột tại ô cuối của miền (từ đây về sau ta gọi thao tác này là điền tự
động (AutoFill). Kết quả ta được chuỗi số 1, 2, 3,
Chuỗi số với bước nhảy bất kỳ:
24
Giáo trình Bảng tính điện tử MS Excel
- Đưa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để có chuỗi số
chẵn ta gõ 2 vào một ô nào đó.
- Về ô dưới (hoặc bên phải) của miền, gõ vào số tiếp theo, ví dụ 4.
- Đánh dấu khối 2 ô này, trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen,
kéo và thả chuột tại ô cuối của miền.
Kéo dấu + và thả tại ô A7, ta sẽ được chuỗi số 2, 4, 6, 8, 10, 12, 14 tại miền
A1:A7. (Hình 2.2)
Hình 2.2
Chuỗi Ngày tháng tăng
- Đưa con trỏ về ô đầu tiên của miền, gõ vào ngày tháng năm bắt đầu.
- Trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen, kích và giữ phải chuột,
kéo thả tại ô cuối miền.
- Trong thực đơn tắt chọn:
Fill Days:
®Ó t¨ng 1 ngµy (vÝ dô : 15/04/1999, 16/04/1999, 17/04/1999)
Fill Months:
C2*D2
6. Ấn tổ hợp phím Ctrl + Enter. Tại các ô từ E2 đến E7, Excel cho kết quả tính
Lương của từng người.
Ta cũng có thể làm như sau:
- Bỏ qua bước 1
- Thực hiện các bước từ 2 đến 5, sau bước 5 chọn nút trên thanh công thức
(hoặc ấn phím Enter)
- Để tính Lương cho những người còn lại, đưa con trỏ về ô E2, điền tự động
cho tới ô E7.
Tính tỷ lệ phần trăm Lương của từng người so với Tổng Lương:
- Đưa con trỏ về ô F2;
27
Copyright: Tài liệu đại học ©