bảng tính excel

Chương V.
Bảng t?nh excel

I. Đại cương v? bảng t?nh
1.Khái niệm bảng t?nh
a.V? dụ: Lập 1 danh sách điểm thi học kỳ của lớp gồm các cột: Họ, tên, Ngày sinh, M1, M2, M3, Tổng điểm, X?p Loại.
Đây là 1 danh sách được lập dưới dạng 1 bảng. ở đ?:
+Mỗi dòng (tr? dòng đầu lấy làm tiêu đ? cột t) là các thông tin v? một học sinh; Mỗi cột là một thông tin v? các học sinh.
+ Dữ liệu độc lập (bắt buộc đưa vào) gồm: Họ, Tên, Ngày sinh,M1, M2, M3
+Dữ liệu phụ thuộc (sẽ t?nh toán nhờ dữ liệu độc lập): Tổng điểm, x?p loại.
b.Khái niệm bảng t?nh
- Dùng quản l? dữ liệu dưới dạng bảng
- Bảng t?nh là 1 bảng hình chữ nhật được chia thành các ô bởi các lưới đường song song với cạnh của bảng hình chữ nhật
- Các ô theo vệt ngang là dòng, các dòng được đánh số: 1,2,3...65536
- Các ô theo vệt dọc là cột, các cột được đánh số: A, B, ...IV (256 cột)
- Ô: Giao của dòng với cột là ô, tên ô: gh?p tên cột, dòng; v? dụ: A1, F10, ...
2. Khởi động EXCEL

Trong WIN95, 98, 2000 ... c? dùng một trong 3 cách sau:
Cách 1: K?ch chuột lần lượt vào Start/Program/Microsoft excel
Cách 2: K?ch chuột vào chữ X trong biểu tượng nằm g?c phải bên trên màn hình.
Cách 3: K?ch đúp chuột vào biểu tượng trên màn hình n?n.
3. Cấu trúc bảng t?nh excel



- Mỗi sổ tay (WorkBook) gồm 1 đ?n 256 sheet (ngầm đ?nh 3 sheet) ,
- Mỗi bảng t?nh (sheet) là 1 trang bảng t?nh như trên, n? gồm 254 cột, 65536 dòng, hơn 16 triệu ô.
- Mỗi Workbook sẽ được ghi vào 1 tệp; số lương tệp Workbook được mở không giới hạn, chỉ phụ thuộc vào bộ nhớ của máy.
- Cột (columns) c? độ rộng ngầm đ?nh 9 k? tự (c? thể thay đổi t? 0 - 255 k? tự)
- Dòng (Rows) c? độ cao ngầm đ?nh 12.75 chấm điểm (c? thể thay đổi t? 0 - 409)
- Ô (Cell) là giao của một cột và một dòng; được xác đ?nh bởi đ?a chỉ gh?p tên cột, tên dòng. C? 3 loại đ?a chỉ ô:
+ Đ?a chỉ tương đối: A1
+ Đ?a chỉ tuyệt đối: $A$1
+ Đ?a chỉ hỗn hợp: $A1, A$1
4. Di chuyển con trỏ và nhập dữ liệu
- Con trỏ ô: là con trỏ làm việc, c? màu sẫm hơn, chỉ tồn tại duy nhất 1 con trỏ làm việc
- Con trỏ soạn thảo: là 1 vach | nhấp nháy trong ô làm việc, dùng để nhập dữ liệu
- Di chuyển con trỏ ô:
+Dùng 4 ph?m m?i tên: di chuyển đ?n các ô gần
+ Dùng Page UP, Page Down: chuyển trang màn hình
+ Dùng CTRL +Home: V? A1
+ Dùng chuột: k?ch chuột vào ô nào ô đ? trở thành ô làm việc

5. Khối ô (vùng - Range) , chọn khối ô, vi?t đ?a chỉ khối ô
- Khối ô: là tập hợp những ô li?n k? nhau tạo thành hình chữ nhật, khối ô c? thể là 1 ô, 1 cột, 1 dòng, một nh?m ô hoặc toàn bộ bảng t?nh.
- Đ?a chỉ khối ô được xác đ?nh bởi toạ độ ô đầu hcn (g?c trên bên tráig) và toạ độ ô cuối hcn (g?c dưới bên phảig) ; Cách vi?t A1:B10
- Chọn khối ô:
+Chọn 1 khối ô: Đặt chuột ở ô đầu khối, k?ch rê đ?n ô cuối khối
+Chọn nhi?u khối ô đồng thời: Chọn khối ô thứ 1, ấn giữ CTRL đồng thời k?ch rê chuột để chọn khối thứ 2,... (thường dùng khi vẽ đồ th?t).
6. Thay đổi độ rộng cột, độ cao dòng
Di trỏ chuột ở đường biên bên phải tên cột, hoặc ph?a dưới tên dòng, khi chuột thành  thì k?ch rê.
7. Ra khỏi bảng t?nh
K?ch chuột vào menu File/exit
II. Lập bảng t?nh và cất bảng t?nh vào đĩa
1. Các kiểu dữ liệu và cách nhập
V? dụ: Bảng t?nh gồm các cột Họ, Tên, Ngày sinh, M1, M2, M3, Tổng, X?p loại,..
ở v? dụ trên dữ liệu của các cột Họ, Tên là kiểu văn bản, Ngày sinh là kiểu Ngày tháng, M1, M2, M3 là kiểu số, Tổng, X?p loại là kiểu công thức.
a. Kiểu văn bản ( Text)
- Được gõ các k? tự c? trên bàn ph?m
- Tối đa được 255 k? tự, ngầm đ?nh thấy 9 k? tự, n?u ô bên cạnh không c? dữ liệu thì các k? tự “tràn” sang và vẫn nhìn thấy.
- Dữ liệu sau khi nhập vào ô sẽ sát trái (ngầm đ?nh)
b. Dữ liệu số (Number )
- Chỉ gõ các số t? 0 - 9 , +, -, .
- Dữ liệu sau khi nhập vào ô sẽ sát phải
c. Dữ liệu kiểu ngày D ( date )
- Gõ ngày tháng hợp lệ, theo qui đ?nh: mm/dd/yyyy hoặc dd /mm/yyyy
- Dữ liệu sau khi nhập vào ô sẽ sát phải
d. Dữ liệu công thức D (Formula)
- Bắt đầu là dấu =, sau đ?n biểu thức t?nh toán vi?t theo qui đ?nh của excel
- Dữ liệu nhập vào ô sẽ sát phải n?u k?t quả t?nh là số, sát trái n?u k?t quả là văn bản.
- Nội dung ô công thức chứa biểu thức t?nh toán mà ta gõ vào, nhưng hình thức hiện ra là k?t quả t?nh toán của biểu thức đ?.
- Khi các ô dữ liệu trong công thức thay đổi, thì giá tr? t?nh toán trong ô công thức c?ng thay đổi theo.
2. Hiệu chỉnh dữ liệu
- N?u dữ liệu ngắn: muốn sửa ô nào đưa con trỏ vào ô gõ đ? lên
- N?u dữ liệu dài: để con trỏ vào ô ấn F2 rồi nhìn và sửa dữ liệu trên thanh công thức, xong ấn Enter.
3. Các ph?p toán và cách vi?t biểu thức trong EXCEL
- Các ph?p t?nh số học: +, -, *, /
- Các ph?p so sánh: =,>,>=,<,<=, <>
- Các hàm t?nh toán (sẽ x?t cụ thể sau)
V? dụV: SUM(C2,D2), AVERAGE(C2,D2)
- Biểu thức: Là sự k?t hợp giữa các hằng, đ?a chỉ ô, các hàm với các ph?p toán và dấu (, ).
C? 2 loại biểu thức: Biểu thức t?nh toán và biểu thức logic (đi?u kiện)
V? dụ: =(C2+D2)/2
=D2>9
4. Ghi bảng t?nh vào tệp, đọc bảng t?nh t? tệp ra
- Ghi vào: File/Save (đuôi ngầm đ?nh của tệp là XLS)
- Đọc ra: File/Open
III. Một số thao tác trên bảng t?nh
ở trên ta đã x?t khái niệm khối ô, cách chọn (đánh dấu) khối ô, vi?t đ?a chỉ khối ô, bây giờ ta hãy thực hiện các thao tác với các khối ô đ?.
1. Các thao tác sao ch?p, di chuyển, xoá dữ liệu của khối ô
a. Xoá dữ liệu khối ô
- Đánh dấu khối ô
- ấn ph?m Delete
b. Di chuyển dữ liệu khối ô
- Đánh dấu khối ô
- K?ch Edit/Cut
- Đặt con trỏ ô ở nơi cần chuyển đ?n
- K?ch vào Edit/Paste
c. Sao ch?p dữ liệu khối ô
- Đánh dấu khối ô
- K?ch Edit/Copy
- Đặt con trỏ ô ở nơi cần sao ch?p đ?n
- K?ch vào Edit/Paste
2. Sao ch?p ô công thức với đ?a chỉ tương đối, tuyệt đối
a. V? dụ

V? dụ 1:
A B C D E F
1 Họ Tên Ngày sinh Lương PC Tổng
2 Lê Hà 1/2/67 390 120 510
3 Đỗ§ TúT 2/3/56 425 220 645
4 Nguyễn An 3/4/78 520 320 840
Ô F2 sẽ vi?t công thức t?nh Tổng: =D2 + E2
Sau đ? ta copy ô F2 cho các ô F3S, F4,...--> Công thức ở các ô này sẽ đúng
V? dụ 2V:
A B C D E F
1 Hệ số 1.40
2 Họ Tên Ngày sinh Lương PC Tổng
3 Lê Hà 1/2/67 390 120 510
4 Đỗ§ TúT 2/3/56 425 220 645
5 Nguyễn An 3/4/78 520 320 840
Ô F3 sẽ vi?t công thức t?nh Tổng: =D3*$E$1 + E3
Sau đ? ta copy ô F3 cho các ô F4S, F5,...--> Công thức ở các ô này sẽ đúng do ta đã tuyệt đối ô $E$1
b. Nhận x?t:
Khi sao ch?p ô công thức: đ?a chỉ tương đối trong công thức sẽ bi?n đổi để phù hợp với v? tr? tương đối của ô được sao ch?p đ?n, còn đ?a chỉ tuyệt đối thì giữ nguyên.
c. Cách sao ch?p:
Cách 1: Copy bình thường như cách ở trên
Cách 2: (do thường sao ch?p 1 ô tới các ô li?n k?)
+ Đặt con trỏ ô vào ô công thức
+ Di chuột ở g?c phải dưới ô công thức, khi chuột thành + thì k?ch rê đi các ô li?n k?
3. Sao ch?p đặc biệt (sao ch?p giá tr? của khối dữ liệu là công thức)
V? dụ: ở bảng dữ liệu trên cần sao ch?p sang 1 bảng mới gồm các cột Họ, Tên, Tổng. Cách làm:
+Sao ch?p cột Họ, Tên sang bảng mới: làm sao ch?p bình thường
+Sao ch?p cột Tổng đặt bên cạnh, bằng cách:
- Đánh dấu cột Tổng
- Chọn Edit/Copy
- Đặt con trỏ ô ở nơi cần đ?n (bên canh cột Tên)
- Chọn Edit/Paste Special/Values
4. Đi?n dãy số c? quy luật
- Gõ giá tr? đầu vào ô thứ 1
- Gõ giá tr? thứ 2 vào ô thứ 2
- Đánh dấu 2 ô
- Di chuột ở g?c phải dưới của khối ô, khi chuột thành + thì k?ch rê đi các ô ti?p theo
IV. Một số hàm thông dụng trong t?nh toán
1. Dạng tổng quát của hàm và cách dùng
+ Dạng tổng quát: Tên hàm (đối số)
Đối số c? thể là: hằng số, đ?a chỉ ô, khối ô, biểu thức, tên 1 hàm khác
V? dụ: Sum(A2,B2)
+ Cách dùng: hàm thường được dùng trong biểu thức t?nh toán. Hàm được ch?n vào v? tr? trong biểu thức bằng cách:
Cách 1: Tại v? tr? con trỏ trong biểu thức gõ trực ti?p hàm cần nhập vào
Cách 2: Tại v? tr? con trỏ trong biểu thức cần nhập hàm, hãy vào lần lượt:
Insert/ Function/ Chọn tên hàm cần nhập
2. Các nh?m hàm thông dụng
a. Nh?m hàm số học và thống kê
- Max(n1, n2,...ni): Cho giá tr? lớn nhất trong các ni
ở đ? các ni c? thể là: hằng số, đ?a chỉ ô, khối ô c? chứa số
- Min(n1, n2,...ni): Cho giá tr? nhỏ nhất trong các ni
- SUM(n1, n2,...ni): Cho tổng các ni
- AVERAGE(n1, n2,...ni): Cho trung bình cộng của các ni
- PRODUCT(n1, n2,...ni): Cho t?ch các ni
- COUNT(n1, n2,...ni): Đ?m các dữ liệu số trong danh sách
- COUNTA(n1, n2,...ni): Đ?m các dữ liệu cả số lẫn k? tự trong danh sách
V? dụ: COUNT(1,ha,4) cho k?t quả 2
COUNTA(1,ha,4) cho k?t quả 3
- ABS(x) =|x|
- INT(x) = phần nguyên của x
- MOD(x,y)= số dư của x /y
- SQRT(x) = căn bậc hai của x
- EXP(x) = ex
b. Nh?m hàm thời gian
- TODAY()cho ngày tháng hệ thốngc
- YEAR(dl kiểu ngày) cho năm (4 số)
- MONTH(dl kiểu ngày) cho tháng
- DAY(dl kiểu ngày) cho ngày
- DATE(yyyy,mm,dd): cho giá tr? kiểu ngày
- WEEKDAY(dl kiểu ngày, kiểu) cho thứ trong tuần;
ở đ?: kiểu = 1 thì trong tuần sẽ t?nh thứ Hai:2,...thứ Bảy:7, chủ nhật:1
kiểu = 2 thì trong tuần sẽ t?nh thứ Hai:1,...thứ Bảy:6, chủ nhật:7
kiểu = 3 thì trong tuần sẽ t?nh thứ Hai:0,...thứ Bảy:5, chủ nhật:6

V? dụ: Xem t? ngày sinh ra đ?n nay là bao nhiêu ngày
= TODAY()-DATE(1984,02,25)

c.Nh?m hàm văn bản
- UPPER(x): đổi xâu k? tự X thành chữ in hoa
- LEFT(X,m): Đưa ra 1 xâu con t? xâu X gồm m k? tự kể t? bên trái
- RIGHT(X,n): Đưa ra 1 xâu con t? xâu X gồm n k? tự kể t? bên phải
- LEN(X): cho độ dài xâu k? tự X

d. Nh?m hàm Logic
a b ab a  b
0 0 0 0
0 1 0 1
1 0 0 1
1 1 1 1
- AND(đk1, đk2,...): cho giao của các đk.
V? dụ: + đtb >9 và Đt >8 thì x?p Giỏi: AND(đtb>9, Đt>
+ Đưa ra những người sinh nhật vao ngay hôm nay:
AND(day(Today()=Day(d2),Month(today()=Month(d2))
- OR(đk1, đk2,...): cho hợp của các đk
V? dụ: Đưa ra người c? ĐT hoặc ĐL >=9: OR(ĐT>=9, ĐL>=9)
Giá tr? 1 n?u BTĐK đúng
- IF(BTĐK, giá tr? 1, giá tr? 2) =
Giá tr? 2 n?u BTĐK sai
V? dụ: 1. X?p loại học sinh lên lớp, ở lại dựa vào ĐTB
= IF(ĐTB>=5,”lên lớp”, “ở lại”)
2. X?p loại học sinh vào 4 loại: Y?u, TB, Khá, Giỏi dựa vào ĐTB
=IF(ĐTB<5,”Y?u”,IF(ĐTB<7,”TB”,IF(ĐTB<9,”Khá”,”Giỏi”)))
Qua 2 v? dụ trên, nhận x?t: số hàm IF lồng nhau của mỗi trường hợp.
3. XL lên lớp: ĐTB >=5 hoặc ĐTB >=4 và ĐT >7; còn lại là ở lại
=IF(OR(DTB>=5,AND(DTB>=4,DT>7)),”lên lớp”, “ở lại”)

V. Đ?nh dạng dữ liệu (trình bày) bảng t?nh
1. Thay đổi cấu trúc bảng
a. Ch?n thêm ô, cột, dòng
- Đánh dấu ô (cột, dòng) ở ph?a dưới
hoặc bên phải v? tr? cần ch?n
- Chọn Insert/Cell, xuất hiện hộp thoại:



N?u chọn: +Shift cell Right: đẩy khối ô đánh dấu sang phải khi ch?n
+ Shift cell Down: đẩy khối ô đánh dấu xuống dưới khi ch?n + Entire Row: ch?n dòng trống ph?a trên khối ô đánh dấu
+ Entire Colum: ch?n cột trống ph?a bên trái khối ô đánh dấu
b. Xoá ô, cột, dòng
- Đánh dấu ô (cột, dòng) cần xoá
- Chọn Edit/Delete, xuất hiện hộp thoại:





N?u chọn: +Shift cell Left: xoá ô và dồn sang trái
+ Shift cell Up: Xoá ô và dồn lên trên
+ Entire Row: Xoá dòng đánh dấu
+ Entire Colum: Xoá cột đánh dấu
2. Đ?nh dạng dữ liệu kiểu số, ngày
- Chọn (đánh dấu) vùng dữ liệu kiểu số hoặc ngày
- Chọn Format/Cell, xuất hiện bảng:

- Trong bảng này chọn nút Number
+ Trong Category: Chọn Number n?u đ?nh dạng số
Chọn Date n?u đ?nh dang ngày
Giả sử chọn Date, ta c? hộp thoại:

+ Chọn kiểu đưa ra cho kiểu ngày tháng ở bảng bên phải, n?u không c? thì chọn Custom để tự đ?nh nghĩa kiểu đưa ra.
+Chọn OK
3. Đ?nh dạng dữ liệu k? tự
- Đánh dấu vùng dữ liệu
- Chọn Format/Cell, xuất hiện bảng như trên,
- Trong bảng này chọn nút Font

+ Chọn phông, kiểu, c? chữ,... (giống ở Word)
+ K?t thúc k?ch OK
4. Đi?u chỉnh dữ liệu trong ô
- Đánh dấu vùng dữ liệu
- Chọn Format/Cell, xuất hiện bảng như trên,
- Trong bảng này chọn nút Alignment, c? hộp thoại ti?p:


ở đ?, n?u chọn:
+Horizontal: Để căn dữ liệu ngang ô như sau:
General: giữ nguyên dữ liệu như khi đưa vào
Left: Căn thẳng m?p trái
Center: Căn vào giữa
Right: Căn thẳng m?p phải
Justify: Căn đ?u 2 bên
Fill: Làm đầy ô các k? tự đã c?
Center acrosse Selection: Đưa vào giữa khối ô
+ Vertical: Để căn dữ liệu dọc ô
Top: Sát trên; Bottom: Sát dưới
Center: Vào giữa Justify: đ?u trên dưới
+ Orientation: Chọn hình thức trải dữ liệu
+ Wrap Text: Cuốn dữ liệu xuống dòng khi đ?n l? bên phải ô.
5. Tạo đường kẻ cho khối ô
- Đánh dấu khối ô
- Chọn Format/Cell xuất hiện hộp thoại trên
- Chọn nút Border, c? hộp thoại ti?p:

+ Chọn đường vi?n xung quang hoặc ở giữa các ô ở mục Preset
+ Chọn đường vi?n t?ng ph?a trong mục Border
+ Chọn kiểu đường vi?n trong mục Line - Style
+ Chọn OK
* C? thể dùng thanh công cụ để tạo đường vi?n nhanh hơn
VI. Vẽ biểu đồ
1. Các bước vẽ biểu đồ
Bước 1: Chọn dữ liệu để vẽ và chọn kiểu biểu đồ
- Đánh dấu các vùng dữ liệu cho trục OX, OY
- K?ch nút lệnh trên thanh công cụ hoặc chọn Insert/chart -> ra bảng 1
- Chọn kiểu biểu đồ ở bảng 1 (Step 1)

- Chọn Next -- > ra bảng 2 (Step 2)
Bước 2: Xác đ?nh lại mi?n dữ liệu để vẽ biểu đồ ở bảng 2 (Step 2)

- Chọn Next -> ra bảng 3 (Step 3)

Bước 3: Chọn các tuỳ chọn của biểu đồ ở bảng 3 (Step 3)



- Đi?n tên đồ th? ở ô Chart title
- Đi?n tên trục OX ở ô Category (X) Axis
- Đi?n tên trục OY ở ô Value (Y) Axis
- Chọn Next -> ra bảng 4 (Step 4)

Bước 4: Xác đ?nh v? tr? đặt biểu đồ ở bảng 4 (Step 4)


- N?u k?ch chuột vào As new sheet : Đặt biểu đồ trên trang t?nh mới, c? tên là Chart n (n là 1 sốn)
- N?u k?ch chuột vào As object in : Đặt biểu đồ trong trang t?nh, trang t?nh được xác đ?nh bởi Sheet n
- K?ch vào Finish -> ra biểu đồ như sau:



2. Hiệu chỉnh biểu đồ
a. Di chuyển, thay đổi k?ch c? biểu đồ
- Đánh dấu biểu đồ
- K?ch rê chuột trên biểu đồ để thay đổi v? tr?
- K?ch rê chuột ở một trong các nút đen bao quanh biểu đồ để thay đổi k?ch c?
b. Thay đổi Font chữ, kiểu, c? chữ, màu chữ trong biểu đồ
- Đánh dấu biểu đồ
- Chọn Font, kiểu, c? chữ, màu chữ trên thanh công cụ hoặc trong menu
c. Thay đổi kiểu, vùng dữ liệu, các tiêu đ?,.. biểu đồ
Đánh dấu Biểu đồ /K?ch vào Chart/ sau đ? chọn:
- Chart type : Thay đổi kiểu biểu đồ
- Source data: Thay đổi vùng dữ liệu để vẽ
- Chart Option: Thay đổi các tiêu đ? cho biểu đồ và các trục
- Location: Thay đổi nơi đặt biểu đồ
• Chú ?: Khi lưu bảng t?nh vào tệp sẽ đồng thời lưu luôn cả biểu đồ.




VII. Cơ sở dữ liệu trên bảng t?nh
1. Khái niệm cơ sở dữ liệu trong bảng t?nh
V? dụ:
stt Họ và tên 15phút 1 ti?t TBKT học kỳ TB mônkì1
1 Tạ Quang Cường 9 9 9.0 9 9.0
2 Nguyễn Anh D?ng 7 7 7.0 8 7.3
3 Chu Thuỳ Dương 8 6 6.7 8 7.1
4 Nguyễn Hùng D?ng 10 10 10.0 10 10.0
5 Nguyễn Hữu Đại 10 10 10.0 8 9.3
Một bảng dữ liệu trên trang t?nh được tổ chức thành các hàng và các cột.
Theo thuật ngữ của CSDL thì mỗi hàng (tr? hàng đầu) sẽ là một bản ghi, mỗi cột là một trường.
Hàng đầu ghi tiêu đ? của mỗi cột sẽ là tên mỗi trường.
2. Sắp x?p dữ liệu
- Đánh dấu khối dữ liệu cần SX (gồm dòng tên trường (tên cột đầu biểu) và các bản ghi (các dòng dữ liệu))
- Chọn Data/Sort
Trong hộp thoại trên chọn:
+K?ch vào Header Row
+ Chọn khoá SX thứ 1 ở hộp SORT BY (là tiêu đ? cột cần sx), và chi?u SX của n? (A : tăng, D: giảm)
+ N?u c? khoá SX thứ 2, 3 thì chọn tương tự ở hộp THEN BY
+ K?t thúc k?ch vào OK
3. Tìm ki?m dữ liệu (lọc)
a. Bằng Autofilter
Bước 1: Đánh dấu vùng dữ liệu (gồm dòng tiêu đ? cột và các dòng dữ liệu)
Bước 2: Vào DATA /FILTER/AUTO FILTER
Bảng t?nh xuất hiện các ô m?i tên cạnh tiêu đ? cột:
Bước 3: Lọc t?ng đi?u kiện:
K?ch chuột vào ô m?i tên bên cạnh tên cột dùng làm đi?u kiện lọc, đưa ra bảng chọn:







+ N?u chọn CUSTOM, đưa ra bảng ti?p:



+ Chọn mức so sánh ở Show rows where:
Equals = is less than or equal to <=
Does not equal <> Begins with Bắt đầu bằng chữ cái
is greater than > Does not begin with Không bắt đầu bằng..
is greater than or equal to >= Ends with K?t thúc bằng
is less than < Does not End with Không k?t thúc bằngK

Gõ (với số) hoặc chọn (với chữ) giá tr? so sánh ở ô bên cạnh
V? dụ: - Đưa ra những người c? TBKT >=9
- Đưa ra những người cơ TBKH >=8, 1 ti?t >9
- Đưa ra những người c? TBKT t? 7 đ?n 9 (dùng AND)
b. Bằng Advanced Filter
Bước 1: Tạo vùng đi?u kiện như sau:
- Dòng 1: chứa tên các tiêu đ? cột giống ở vùng dữ liệu (nên copy sang)
- Dòng 2 (hoặc các dòng sau nữa dùng để ghi những đi?u kiện theo yêu cầu): Nhập các đi?u kiện ở ngay ph?a dưới tên tiêu đ? cột ở vùng đi?u kiện
Bước 2: Đặt con trỏ ô vào vùng dữ liệu và chọn lênh: Data/filter/Advantced Filter -> Xuất hiện hộp thoại:

Bước 3: Lựa chọn yêu cầu thực hiện trên khung Action:
- Filter the List, in – place: Để hiển th? k?t quả lọc ngay trên vùng dữ liệu khai thác.
- Copy to another location: Để sao ch?p k?t quả lọc vào vùng sẽ được chỉ ra trên bảng t?nh ở Copy to.
Bước 4: Khai báo phạm vi các vùng trong hộp thoại:
- List range: nhập đ?a chỉ (hay k?ch rê phạm vi) vùng dữ liệu khai thác
- Criteria range: Nhập đ?a chỉ (hoặc k?ch rê) vùng đi?u kiện.
- Copy to: Nhập đ?a chỉ (hoặc k?ch rê) vùng chứa k?t quả.
Bước 5: Chọn OK trong hộp thoại.
V? dụ


Ghi chú: Cách vi?t các đi?u kiện trong vùng đi?u kiện:

Giả sử c? bảng dữ liệu:

stt Họ và tên 15phút 1 ti?t TBKT
1 Tạ Quang Cường 9 9 9.0
2 Nguyễn Anh D?ng 7 7 7.0
3 Chu Thuỳ Dương 8 6 6.7
4 Nguyễn Hùng D?ng 10 10 10.0
Vùng đi?u kiện:

stt Họ và tên 15phút 1 ti?t TBKT
Nguyễn 7
>=9
Vùng k?t quả:

stt Họ và tên 15phút 1 ti?t TBKT
1 Tạ Quang Cường 9 9 9.0
2 Nguyễn Anh D?ng 7 7 7.0
4 Nguyễn Hùng D?ng 10 10 10.0

4. Một số hàm dùng với CSDL
Qui ước: VDL: Vùng dữ liệu cần x?t
C: Cột cần x?t
VDK: Vùng đi?u kiện
- DSUM(VDL,C,VDK): T?nh tổng các ô ở cột cần x?t thoả mãn đi?u kiện

- DAVERAGE(VDL,C,VDK): T?nh trung bình cộng các ô trên cột cần x?t thoả mãn đi?u kiện

- DCOUNT(VDL,C,VDK): Đ?m các ô trên cột cần x?t thoả mãn đi?u kiện (N?u cột cần x?t là số)

- DCOUNTA(VDL,C,VDK): Đ?m các ô trên cột cần x?t thoả mãn đi?u kiện (N?u cột cần x?t là văn bản)

- DMAX(VDL,C,VDK): Đưa ra số lớn nhất trong các ô trên cột cần x?t thoả mãn đi?u kiện

- DMIN(VDL,C,VDK): Đưa ra số nhỏ nhất trong các ô trên cột cần x?t thoả mãn đi?u kiện




V? dụ:


* Lưu ? khi tạo vùng đi?u kiện
- N?u đi?u kiện là hằng số thì tiêu đ? đi?u kiện trùng với tiêu đ? cột dữ liệu làm đi?u kiện (nên copy tên của cột lấy làm đi?u kiện xuốngn).
- N?u đi?u kiện là biểu thức (hàm,..) thì tiêu đ? đi?u kiện phải khác với tiêu đ? cột dữ liệu lấy làm đi?u kiện (không nên copy tên của cột lấy làm đi?u kiện xuống).
V? dụV: Đ?m những người c? chữ cái đầu của Họ và tên là N, vùng đi?u kiện ta lập như sau:
đầuhọ
=Left(B2,1)=”N”

5. Tìm ki?m bằng VLOOKUP, HLOOKUP
a. Hàm VLOOKUP
VLOOKUP(Giá tr? tìm, Vùng cần tìm, Cột cần lấy): Thực hiện việc tìm ki?m một giá tr? trong cột đầu tiên của vùng cần tìm và cho k?t quả tương ứng trong cột cần lấy (cột chỉ đ?nh).



b. Hàm HLOOKUP
HLOOKUP(Giá tr? tìm, Vùng cần tìm, Dòng cần lấy): Thực hiện việc tìm ki?m một giá tr? trong dòng đầu tiên của vùng cần tìm và cho k?t quả tương ứng trong dòng cần lấy (dòng chỉ đ?nh).

* Chú ? khi dùng 2 hàm Vlookup, Hlookup:
ở hàm Vlookup: Vùng cần tìm c? cột thứ nhất được sắp x?p tăng dần (t? trên xuống dưới)
ở hàm Hlookup: Vùng cần tìm c? dòng thứ nhất được sắp x?p tăng dần (t? trái sang phải).

V? dụ:







6. Tổng hợp dữ liệu theo nh?m

V? dụ: Danh sách SV gồm các trường: Họ tên, Ngày sinh, Lớp, Khối, Điểm TB, Học bổng ->T?nh ti?n học bổng cho t?ng khối, t?ng lớp đặt dưới t?ng lớp, khối
a. Các bước thực hiện
b1. Sắp x?p dữ liệu của bảng với kh?a là các trườngc? thể tổng hợp theo nh?m
b2. Đặt con trỏ vào vùng CSDL
b3. Vào menu Data / Subtotal -> xuất hiện hộp thoại



- Chọn cột căn cứ tạo nh?m trong At each change in
- Chọn hàm t?nh t?an trong mục Use Function
- Chọn các cột sẽ thêm Subtotal vào trong Add Subtotal to
b4. K?ch OK

b. V? dụ


c. Chú ?
- Các nút cho ph?p chọn các cấp của các nh?m

- Nhấn nút : để thu nhỏ nh? m, chỉ hiện dòng tổng hợp của nh?m
- Nhấn nút mở rộng để xem các dòng chi ti?t trong nh?m

Trong hộp thoại Subtotal, các nút chọn c? ? nghĩa:
 Replace current subtotal: bật / tắt việc thay th? dữ liệu trong thao tác Subtotal mới cho dữ liệu của Subtotal c?
 Page break between groups: bật / tắt việc tự động ch?n ngắt trang vào giữa các nh?m
 Summary below data: bật / tắt việc tạo dòng tổng hợp đặt ngay dưới t?ng nh?m dữ liệu

VIII. In bảng t?nh
Ta thường gặp hai trường hợp:
- Phân trang tự động (n?u cả trang t?nh là 1 danh sách)
- Phân trang bằng tay (n?u trang t?nh gồm nhi?u danh sách)

1. Đặt trang in tự động
- Đánh dấu toàn danh sách
- Vào File/Page setup



+ K?ch chọn nút Page: Để chọn k?ch c?, hướng trang in
- Portrait: in dọc - Landscape: in ngang
- Paper size: chọn c? giấy
+ K?ch chọn nút Margin: để đặt l? trang in
+K?ch chọn nút Header/footer: để tạo tiêu đ? đầu, cuối trang in

- Mẫu tiêu đ? đầu, cuối được chọn ở bảng sau:


+ K?ch chọn nút Sheet: để tạo cột /dòng đầu cho mọi trang in:



- Trong Print area: gõ đ?a chỉ tuyệt đối của bảng dữ liệu, V? dụ: $A$1:$E$10
-Trong Rows To repeat at top: gõ các dòng mà ta muốn in trong mọi trang in theo qui cách: $tên dòng:$ tên dòng
V? dụV: $1:$ 2. Nghĩa là mọi trang in đ?u in 2 dòng 1, 2 ở đầu bảng.
- Trong Colums To repeat at left: gõ các cột mà ta muốn in trong mọi trang in theo qui cách: $ tên cột:$ tên cột, V? dụ: $A:$ A. Nghĩa là mọi trang in đ?u in cột A ở cột đầu.( It dùng n?u số cột không quá nhi?u quá khổ giấy)

2. Đặt trang in bằng tay
a. Đặt trang
- Đặt con trỏ ô ở g?c phải dưới của trang dự đ?nh,
- Vào Insert/Page Break

b. Huỷ đặt trang
- Đặt con trỏ ô ở g?c phải dưới của trang
- Vào Insert/ Remove Page Break
3. In trang t?nh
Sau khi đã đặt trang in (tự động hoặc bằng tay) và tạo các tiêu đ?, đánh số trang (n?u cần) xong ta bật máy in, lắp giấy và thực hiện việc in, bằng cách chọn menu: File / Print -> xuất hiện hộp thoại:


Trong hộp thoại trên ta chọn:
+ All: để in ra tất cả các trang
+ Pages: để in ra trang cụ thể, bằng cách gõ số trang vào From (in t? trang), vào To (đ?n trang)
+ Cuối cùng k?ch vào OK.


BÀI T?P EXCEL

Bài 1.
Để quản l? điểm của 1 lớp, mỗi học sinh c? các y?u tố sau: họ và tên, ngày sinh, điểm của các môn như Toán, L?, Hoá, Sinh, Văn, Sử, Đ?a,... Điểm của mỗi môn được t?nh với hệ số nào đ?. T?ng học kỳ thầy giáo phải t?nh điểm trung bình cho t?ng học sinh (giả sử với 1 số môn) như sau:
Danh sách điểm các môn của lớp 12A
Stt Họ và tên NS Toán L? Hoá Văn Tb
1 Lê Văn An 20/3/84 8 8 9 9 8.5
... ...
1. Xác đ?nh kiểu dữ liệu của các cột, các thông tin độc lập và thông tin phụ thuộc
2. Lập biểu bảng gồm các cột, trình bày như trên và vào dữ liệu (vào các dữ liệu độc lập) cho 10 học sinh. Ghi vào tệp.
3. T?nh điểm TB cho mỗi học sinh. Ghi lại
Bài 2. B
Hàng tháng, t?ng học kỳ thày giáo cần thống kê số lượng và tỉ lệ phần trăm học sinh của 1 lớp thuộc các loại dưới trung bình (DTB), trung bình (TB), khá (KH) và giỏi (G). Trước h?t thày giáo phải kẻ một bảng như hình sau và đ?m số lượng (SL) đối với t?ng loại để ghi vào:
Học kỳ I
DTB TB KH G
Tháng TS SL TL SL TL SL TL SL TL
09 50 4 15 20 11
10 50 3 16 17 14
11 50 5 12 18 15
12 50 6 10 19 15
1. Hãy lập 1 bảng như trên, ghi vào tệp
2. T?nh tỉ lệ (TL) của mỗi loại trong t?ng tháng (TL = SL/TS). Ghi lại tệp

Bài 3.
Một k? toán phải t?nh lương cho các cán bộ, họ lập một bảng gồm các cột: Stt, Họ và tên, Ngày công (NC), Lương ch?nh (LC) t?nh theo đơn v? ngàn đồng, Ti?n thưởng (TT), trợ cấp (TC), Tổng số (TS). Theo mẫu sau:
Stt Họ và tên NC LC TT TC TS
1 Trần An Bình 24 450
2 . . .
1. Lập một bảng như trên và vào dữ liệu cho khoảng 5 người. Ghi vào tệp
2. T?nh ti?n thưởng theo số ngày công: TT=NC*5 (5 ngàn đồng)
3. T?nh trợ cấp theo mức phần trăm lương ch?nh được hưởng, giả sử là 30% lương ch?nh.
4. T?nh tổng số ti?n được lĩnh của mỗi người. Ghi lại tệp
Bài 4.
Một HTX giao ruộng cho xã viên canh tác. Họ lập một bảng để nắm tình hình thu thu?. Bảng này c? các cột sau: Số thứ tự, Họ và tên chủ hộ, diện t?ch đất hạng 1, diện t?ch đất hạng 2, diện t?ch đất hạng 3, mức thu? hàng năm cho mỗi hạng đất. Tổng cộng thu? phải đ?ng hàng năm đối với mỗi hộ và đối với cả HTX trên mỗi hạng đất và toàn bộ. (Mức thu thu? t?nh trên một đơn v? diện t?ch chỉ phụ thuộc vào hạng đất)
1. Lập bảng t?nh để t?nh thu? cho 5 xã viên, ghi vào tệp
2. T?nh tổng cộng thu? phải đ?ng của mỗi xã viên
3. T?nh tổng thu? của mỗi hạng đất và của toàn bộ (để ở hàng cuối)
(Lưu ?: Mức thu? của 3 hạng đất để ở 3 ô cố đ?nh, khi t?nh thu? của các hạng đất dùng đ?a chỉ tuyệt đối)

Bài 5.
Một danh sách lớp gồm các cột: Họ và tên, ngày sinh, điểmToán, điểm Văn, điểm Ngoại ngữ, điểm TB (ĐTB), x?p loại, ghi chú.
1. Lập bảng và vào dữ liệu cho khoảng 5 học sinh (các cột dữ liệu phụ thuộc: ĐTB, x?p loại, ghi chú sẽ t?nh toán và đi?n bởi các câu hỏi sau)
2. T?nh điểm trung bình cho mỗi học sinh
3. X?p loại cho mỗi học sinh dựa vào điểm trung bình:
N?u ĐTB <5 x?p loại Không đạt; N?u ĐTB >=5 x?p loại Đạt
4. Hãy lập công thức t?nh để cột Ghi chú sẽ được tự động ghi dấu * đối với các học sinh c? ĐTB cao nhất (c? thể c? nhi?u học sinh cùng c? ĐTB cao nhất), còn các học sinh khác ghi dấu - . (Gợi ?: tạo ra một ô phụ ghi ĐTB cao nhất để so sánh)
*5. Thêm cột Sinh chủ nhật để ghi những người sinh vào Chủ nhật. Bi?t rằng ngày 31/12/1996 là ngày thứ Ba. Sử dụng các hàm th?ch hợp để tự động vi?t vào cột này chữ CN đối với những người sinh đúng vào chủ nhật. (Gợi ?: Do ngày 31/12/1996 là ngày thứ ba nên ngày 5/1/1997 là Chủ nhật, vậy tất cả các ngày chênh với ngày 5/1/1997 một số ngày chia h?t cho 7 đ?u là Chủ nhật (dùng hàm ABS, hàm MOD và hàm IF để vi?t công thức cần thi?t) .

Bài 6.
Lập một bảng t?nh gồm những thông tin sau: Họ, Tên, Ngày sinh, số con, Lương ch?nh, phụ cấp, Thu nhập, Ghi chú.
1. Vào dữ liệu cho 5 người
2. T?nh thu nhập cho mỗi cán bộ (Thu nhập = Lương ch?nh + phụ cấp)
3. Ghi chú sẽ ghi với đi?u kiện như sau:
- Những người sinh t? 1936 trở v? trước thì ghi V? Hưu
- Những người sinh sau 1936 thì ghi Làm Việc.
4. Vẽ đồ th? thể hiện sự tương quan mức thu nhập giữa các cán bộ
5. Sắp x?p bảng t?nh sao cho cột Thu nhập theo chi?u giảm dần.
6. Đ?m số người c? số con t? 2 trở xuống, k?t quả để ở ô A10
7. Chỉ ra mức thu nhập cao nhất trong cơ quan, k?t quả để ở ô B10. Sau đ? đ?m tổng số những người c? mức thu nhập cao nhất, k?t quả để ở ô B12.
8. T?nh tổng lương của những người V? hưu để ở ô C14; tổng lương của những người Làm việc để ở ô D14.
9. Lọc và đưa ra danh sách gồm những người c? lương ch?nh >=425 và số con <=2.
10. Lọc và đưa ra 1 danh sách gồm những người V? hưu, 1 danh sách gồm những người Làm việc.

Bài 7.
1. Lập một bảng theo mẫu sau:
Danh sách khách hàng vay ti?n ti?t kiệm
Ngày trả: 12/6/97
% lãi quá hạn /ngày: 0.0002
Tên khách Đối tượng Ngày vay Ngày đ?n hạn Ti?n vay Ti?n lãi Ti?n lãi quá hạn Tổng cộng
Thanh Hà 1 12/6/96 12/6/97 2000000
Anh Xuân 2 20/2/94 15/6/96 1500000
Hà An 2 15/3/95 20/6/96 3000000
Anh Tuấn 1 30/6/95 10/4/96 2500000
Xuân Thao 3 12/6/96 12/6/97 1000000


2. T?nh cột ti?n lãi như sau:
- N?u đối tượng 1 thì ti?n lãi = Ti?n vay *(Ngày đ?n hạn - ngày vay) * 0.001
- Các đối tượng khác ti?n lãi = Ti?n vay *(Ngày đ?n hạn - ngày vay) * 0.002
3. T?nh ti?n lãi quá hạn như sau:
Ti?n lãi quá hạn =Ti?n vay *(Ngày trả-Ngày đ?n hạn) * % Lãi quá hạn.
4. T?nh ti?n tổng cộng = Ti?n vay + ti?n lãi + ti?n lãi quá hạn.
5. Sắp x?p ti?n vay theo chi?u giảm dần.
6. Đ?m số người thuộc đối tượng 1, k?t quả để ở ô A14.
7. T?nh tổng ti?n vay của các đối tượng loại 2 hoặc loại 1, k?t quả để ở ô B15.
8. T?nh tổng ti?n Tổng cộng của những người c? ngày vay vào năm 1995, k?t quả để ở ô C15
8. Vẽ đồ th? so sánh ti?n Tổng cộng và Ti?n lãi của t?ng khách hàng.
9. Đưa ra số ti?n vay cao nhất, ?t nhất trong các khách hàng
10. Lọc ra một danh sách gồm các đối tượng loại 1 c? ngày vay sau 1L/1/1996.

Bài 10.
1. Lập một bảng t?nh và trình bày như mẫu sau:


2) Xác đ?nh điểm ưu tiên cho mỗi người như sau:
XLHTập
ĐiểmUT
A 1
B 0.5
C 0
3) T?nh Điểm TB như sau: Điểm TB =(Toán *2+Văn *2+L? +Sinh+Ngoại Ngữ+ĐiểmUT)/7

4) K?t quả x?p như sau:
N?u ĐiểmTB <5 x?p loại Y?u; 5<=ĐiểmTB<6.5 x?p loại TB; 6.5<=Điểm TB <8 x?p loại Khá; ĐiểmTB >=8 x?p loại Giỏi.
5) Sắp x?p danh sách sao cho Điểm TB theo chi?u giảm dần
6) Đi?n số thứ tự tự động vào mục STT
7) Đ?m số người ở lớp 12A2, k?t quả để ở ô B12
Đưa ra 1 dánh sách gồm những người x?p loại học tập là A và c? Điểm TB >=7
9) Vẽ biểu đồ so sánh điểm TB của mỗi học sinh
Bài 11.
Danh sách SV được cấp học bổng năm học 200... –200 ...

Ngày hiện tại: (cho ngày cụ thể hôm nay)
Stt Họ và tên Ngày sinh MãUT Môn 1 Môn 2 Điểm TB Ti?n HB Ghi chú
Nguyễn Lâm A1 8 9
Lê Tú Nam A2 7 9
Đoàn ThuThuỷ B2 8 7
Lâm Tú Trinh B1 7 9
Phạm Th? Hoa C2 9 8
Lã B?ch Ngọc C1 8 8
Hoàng Ti?n A2 9 7
Lê Th? Lan B1 6 5
1. Nhập dữ liệu theo bảng trên (tự cho ngày tháng năm sinh cho phù hợp độ tuổi hiện nay đang học Đại học)
2. Đi?n số thứ tự cho cột STT
3. T?nh điểm TB như sau: Điểm TB = (Môn 1 + Môn 2)+ Điểm UT
ở đ? Điểm UT cho mỗi người được t?nh như sau:
N?u Mã UT =”A” thì Điểm UT là 1; Mã UT = “B” thì Điểm UT =0.5; Mã UT =”C” thì Điểm UT =0.
4. Ti?n HB (ti?n học bổng) t?nh như sau:
N?u Điểm TB >=9 và tuổi <=20 thì học bổng là 500000
N?u 9>Điểm TB >=8 và 22>=tuổi >20 thì học bổng là 300000
Còn lại không cấp học bổng.
Lưu ?: Tuổi = Năm hiện tại – Năm sinh
5. T?nh tổng học bổng của các học sinh c? Mã UT với các chữ cái đầu là ”A”; ”B”;”C” để k?t quả vào 3 ô: A12, B12, C12.
6. Đ?m xem c? bao nhiêu học sinh c? điểm TB t? 9 trở lên và c? môn 1>=8
7. Tr?ch ra 1 vùng khác trên bảng t?nh: Những học sinh c? ti?n học bổng =500000 và môn 1>=9.
8. Đi?n vào cột Ghi chú: Những ai sinh nhật vào ngày hôm nay thì ghi “SN”, n?u không ghi “-“. (Sau đ? thử 1 vài người trong danh sách c? sinh nhật vào hôm nay)
9. Sắp x?p danh sách trên sao cho cột Ti?n HB giảm dần n?u Ti?n HB trùng nhau thì sắp x?p Điểm TB tăng dần.
10. Vẽ đå thÞ so s¸nh iÓm 2 m«n cña mçi häc sinh.

Hay đấy ,nhưng còn nh74ng chương khác đâu ? và phông chữ lỗi nữa.