Bài giảng Excel

Bài 1: Các khái niệm cơ bản trong Exel

I- Màn hình giao tiếp của Exel gồm các thành phần sau:
II- Cấu trúc của bảng tính
Giới thiệu Worksheet
Bảng tính gồm nhiều dòng và nhiều cột
- Dòng (Row): bảng tính có 65536 dòng được tính bằng số 1, 2, ..65536.
- Cột (Column): Bảng tính có 256 cột được đánh bằng ký tự A, B, C.
- Ô (Cell): là giao của một dòng với một cột
- Vùng (Range): Là một tập hợp ô dạng hình chữ nhật.
Di chuyển trong Worksheet
Tại một thời điểm luôn luôn tồn tại một con trỏ ô trong bảng tính. Khi nhập dữ liệu vào công thức hay thao tác lệnh sẽ tác động đến địa chỉ tại vị trí của con trỏ ô. Các phím di chuyển con trỏ ô thông dụng gồm:
lên một dòng. xuống một dòng
Sang phải một cột , Sang trái một cột
PgUp: lên một trang, PgDn: Xuống một trang
Alt+PgUp: Sang trái một trang
Alt+PgDn: Sang phải một trang
Home: Về đầu dòng
Ctrl+Home: Về đầu trang bang tinh
Alt +Enter: Xuống dòng

2. Thao tác trên Worksheet
Đổi tên worksheet hiện hành: Tên chuẩn của mỗi worksheet là Sheet 1, Sheet 2.Muốn đổi tên wordsheet ta thực hiện lệnh: Format/ Sheet / Rename và nhập tên mới trong Sheet Tab.
Chèn thêm một worksheet: Insert/Worksheet
Huỷ worksheet hiện hành: Edit/Delete Sheet

III. Các kiểu dữ liệu
Kiểu số
Kiểu chuỗi
Kiểu công thức:
- Các toán tử (+, -, *, /)
- Toán tử chuỗi (&)
- Toán tử so sánh (>, < , >=, <=, <>)
IV. Thao tác trên vùng
Chọn vùng, cột, dòng
a) Chọn vùng: dùng chuột hoặc bàn phím
b) Chọn dòng: Nhấp/di chuột tại đường biên trái của worksheet
c) Chọn cột: Nhấp/ di chuột tại đường biên trên của worksheet.
d) Chọn toàn bảng tính: nhấp tại giao điểm của đường biên trên và đường biên dưới
2. Xoá dữ liệu vùng
Chọn vùng muốn xoá/Delete
3. Chép dữ liệu vùng
Chọn vùng nguồn
Edit/Copy (Ctrl + C)
Chọn vùng đích / Edit / Paste (Ctrl + V)
THAO TÁC ĐỊNH DẠNG
Thay đổi khổ rộng cột, chiều cao dòng
Thay đổi cột
- Chọn cột cần thay đổi
- Format / Column / Width


2. Thay đổi dòng
Format / Row / Height

II. Định dạng hiện số
B1: Chọn vùng chứa dữ
liệu để định dạng số
B2: Format / Cells / Number

II. Định vị trí dữ liệu trong ô
B1: Chọn vùng dữ liệu
B2: Format / Cells / Alignment
+ Orientation: Hướng trình bày
+Text Control:
* Wrap text: DL được trình
bày nhiều hàng
* Shrink to fit: Thu nhỏ kích
thước ô
* Merger Cell: Trộn ô
IV. Định dạng ký tự
B1: Chọn vùng chứa dữ liệu
B2: Format / Cells / Font

V. Kẻ khung
- B1: Chọn vùng để kẻ khung
B2: Format / Cells/ Border

VI. Định dạng nền dữ liệu
B1: Chọn vùng cần định dạng
B2: Format / Cells / Parterns

THAO TÁC DỮ LIỆU
Chép dữ liệu
Chép toàn bộ dữ liệu
C1: - Chọn vùng dữ liệu muốn chép
- Edit / Copy (Ctrl + C)
C2: - Chọn vùng dữ liệu gồm vùng nguồn và vùng đích
- Edit / Fill / Down (Up, Right, Left)
2.Chép một thành phần của dữ liệu
- Chọn vùng dữ
- Edit / Copy (Ctrl + C)
- Edit / Paste Special / Hộp hội thoại xuất hiện
+ Formulas: chép công thức trong vùng nguồn
+ Value:Chép giá trị thể hiện
+ Format: Chép phần định dạng
+ All: Chép bình thường
III. Sắp xếp dữ liệu
Chọn vùng dữ liệu muốn sắp xếp
Data / Sort
Chọn cột và thứ tự sắp xếp
+ Ascending: tăng dần
+ Descending: giảm dần
Trong My list has chọn Header row nếu vùng chọn gồm luôn tiêu đề cột và ngược lại.
Click OK để hoàn tất thao tác sắp xếp


V. Chèn dòng, cột, ô
Chèn cột
- Chọn các cột muốn chèn
- Insert / Column
2. Chèn dòng
- Chọn các dòng muốn chèn
- Insert / Row
3. Chèn ô
-Chọn vùng muốn chèn
- Insert/ Cells
VI. Huỷ bỏ dòng, cột, vùng
Huỷ bỏ cột
- Chọn các cột muốn huỷ
- Edit / Delete
Huỷ bỏ dòng
- Chọn các dòng muốn huỷ
- Edit / Delete
3. Huỷ bỏ vùng
- Chọn vùng muốn huỷ bỏ
- Edit / Delete
Bài 4: Các hàm cơ bản
Cú pháp chung các hàm
- Tất cả các hàm trong Microsoft Exel đều có chung một cú pháp như sau:
=Tên hàm (Các tham biến)
- Để xem các hàm, ta Click vào biểu tượng fx trên Standard Toolbar, hoặc vào Insert / Function (Shift+ F3)
- Nhập hàm từ bàn phím: Trước khi nhập hàm ta phải gõ dấu "="
II. Các hàm cơ bản
1. Nhóm hàm số
a. =ABS(Number)
Hàm trả về giá trị tuyệt đối của một số Number
VD: Ô B1 có công thức: =2+ABS(-5.2). Khi đó giá trị trong ô sẽ là 7.2
b. =INT(Number)
Hàm này trả về phần nguyên của một số Number
VD: Giá trị trong ô B1 là -3.2
- Công thức trong ô C1 là: =INT(B1), giá trị sẽ là -4.
- CT trong ô D1 là: =INT(ABS(B1)), giá trị sẽ là 3

c. =MOD(Number, divisor)
Hàm trả về giá trị phần dư của một phép chia số Number cho số divisor (số chia)
VD: Mod(11,3)=2
d. =ROUND(Số làm tròn, Cách làm tròn)
Hàm trả về giá trị đã được làm tròn của số Number tại vị trí thứ làm tròn.
VD: Round(368.592645,2)=368.59
Round(12.687,1)=12.7


2. Nhóm hàm thống kê
a. =AVERAGE(Giá trị 1, giá trị 2. giá trị n)
Hàm trả về giá trị trung bình cộng của các số
VD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là:
=Average(B1:B3)=5
Chú ý: Hàm này không tính các phần tử trống (các ô trống là ô không có dữ liệu)
b. =COUNT(Vùng)
Hàm trả về số phần tử kiểu số
VD: Count(2, "ab",5,4)=3
c. =COUNTA(Vùng)
Hàm trả về số phần tử khác rỗng trong vùng
VD: =Counta(2, "ab",5,4)=4
d. =MAX(Giá trị 1, giá trị 2.giá trị n)
Hàm trả về giá trị lớn nhất của các giá trị
VD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là: =Max(B1:B3)=8
e.= MIN(Giá trị 1, giá trị 2.giá trị n)
Hàm trả về giá trị nhỏ nhất của cá giá trị
VD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là: =Min(B1:B3)=3
f. =SUM(Giá trị 1, giá trị 2 .giá trị n)
Hàm trả về tổng của các giá trị
VD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là: =Sum(B1:B3)=15

3. Nhóm hàm chuỗi
a. =LEFT(Chuỗi, Số kí tự trích)
Hàm trả về chuỗi con của chuỗi kí tự được trích từ trái sang phải
VD: =Left("Trần Văn Nam",8)="Trần Văn"
b. =RIGHT(Chuỗi, Số kí tự trích)
Hàm trả về chuỗi con của chuỗi kí tự được trích từ phải sang trái
VD: =Right("Trần Văn Nam",3)="Nam"
c. =MID(Chuỗi, Số bắt đầu, Số kí tự trích)
Hàm trả về chuỗi con của chuỗi kí tự được trích từ vị trí bắt đầu và dài bằng số kí tự trích
VD: =Mid("Trần Văn Nam",6,3)="Văn"
d. =UPPER(Chuỗi)
Hàm chuyển các kí tự trong chuỗi sang chữ in hoa
VD: Uper("thuy tien") = "THUY TIEN"
e. =LOWER(Chuỗi)
Hàm chuyển các kí tự trong chuỗi sang chữ thường
VD: Lower("THUY TIEN") = "thuy tien"
f. =PROPER(Chuỗi)
Hàm chuyển tất cả các chữ cái đầu của mỗi từ sang chữ in hoa
VD: Proper("trần thị thu hà") = "Trần Thị Thu Hà"
g. =TRIM(Chuỗi)
Hàm trả về chuỗi kí tự mà các kí tự trắng nằm ở đầu và cuối chuỗi được cắt bỏ
VD: Trim(" Thuỷ Tiên ") = "Thuỷ Tiên"
h. =LEN(Chuỗi)
Cho biết độ dài của chuỗi kí tự (kể cả kí tự trắng)
VD: =Len("Xin chao ban")=12
i. =REPT(Chuỗi, Số lần lặp)
Hàm lặp lại chuỗi dữ liệu trong hàm theo số lần chỉ định
VD: =REPT("Good day!",2) = "Good day! Good day!"

4. Nhóm hàm ngày, tháng
a.=DATE(Năm, tháng, ngày)
Hàm cho kết quả ứng với ngày, tháng, năm
VD: =Date(93,08,25) = 25/08/1993 nếu ô chứa hàm định dạng dd/mm/yyyy
b. =DAY(Chuỗi ngày tháng năm)
Hàm trả về ngày trong tháng
VD: =Day(10/12/1985) = 10
c.=NOW( )
Cho kết quả ngày giờ hiện hành
VD: =Now() = 20/10/2006 8:00 am
d.= TODAY( )
Hàm trả về ngày hiện hành
VD: =Today() = 20/10/2006
e. =MONTH(Chuỗi ngày tháng năm)
Trả về tháng trong ô dữ liệu ngày tháng
VD: =Month(10/12/2000) = 12
f. =YEAR(Chuỗi ngày tháng năm)
Trả về nam trong o du lieu ngay thang
VD: =Year(10/12/2000) = 2000
5. Hàm đổi kiểu dữ liệu
=TEXT( Giá trị số, Cách định dạng)
Hàm biển đổi số thành chuỗi số theo định dạng hợp lệ
VD: =Text(12345.67, "#,###") & "USD" = 12,345.67USD
b. =VALUE(Chuỗi)
Hàm biến đổi chuỗi thành số
VD: =Value("123") = 123
6. Nhóm hàm Logic
1. =AND(Biểu thức1, Biểu thức 2..)
- Hàm trả về giá trị "và" của biểu thức Logic
Nếu tồn tại ít nhất một biểu thức có giá trị FALSE thì hàm AND cho giá trị FALSE nếu không cho giá trị TRUE
VD:= And(2<3,5<8,6>5) trả về true
=And(2>3,5<8,6>5) trả về false
2. =OR(Biểu thức 1, Biểu thức 2.)
Trả về giá trị đúng nếu bất kì biểu thức nào trong hàm đúng
VD: Or(2>3,5<8) trả về giá trị TRUE
3. =NOT(Biểu thức)
Trả về giá trị phủ định của biểu thức
VD: =Not(3>2) trả về FALSE
=Not(2>3) trả về TRUE

Bài 5: các hàm cấu trúc điều khiển
Nhóm hàm điều kiện
1. =IF(Biểu thức điều kiện, KQ 1, KQ 2)
Hàm trả về biểu thức đúng khi thoả mãn điều kiện, ngược lại hàm trả về giá trị sai.
VD: =If(A1>2, "A", "B")
- Nếu A1 lớn hơn 2, giá trị trả về là "A"
-Ngược lại giá trị trả về sẽ là "B"
Chú ý: Có thể két hợp các hàm IF với nhau để kiểm tra cho nhiều trường hợp nhưng không vượt quá 254 kí tự.


2. =COUNTIF(Vùng điều kiện, Điều kiện)
Hàm trả về giá trị đếm những phần tử của vùng điều kiện thoả mãn điều kiện
VD: Các giá trị vùng A1, A2, A3, A4 lần lượt là 100, 200, 300, 400. Khi đó
=Countif(A1:A4, ">160") = 3
3. =SUMIF(Vùng dò, Điều kiện, Vùng lấy tổng số)
Hàm tính tổng những ô trong vùng lấy tổng số, những phần tử này được chọn ứng với những dòng của vùng dò có giá trị thoả mãn với vùng lấy tổng số
VD: Các giá trị vùng A1, A2, A3, A4 lần lượt là 100, 200, 300, 400
Các giá trị vùng B1:B4 là: 7, 14, 21, 18. Khi đó
=Sumif(A1:A4, ">160",B1:B4) = 63
II. Nhóm hàm tìm kiếm
=VLOOKUP(Giá trị tìm, Vùng cần tìm,cột trả về,0) Hàm tìm giá trị ngoài bảng (bắt đầu từ cột đầu tiên của bảng tham chiếu), tìm được tại cột thứ bao nhiêu sẽ trả về giá trị ở bảng chi tiết (Cột đầu tiên được đếm là 1). Trong đó:
- Giá trị tìm: có thể là 1 chuỗi. Số, toạ độ ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay chuỗi.
- Vùng cần tìm: Phạm vi khối dữ liệu dùng để so sánh với giá trị tìm.
- cột trả về: Cột trả về của hàm là cột thứ bao nhiêu trong bảng tham chiếu.
VD:
2. =HLOOKUP(Giá trị tìm, Vùng cần tìm,Dòng trả về,0)
Hàm tìm giá trị ngoài bảng (bắt đầu từ dòng đầu tiên của bảng tham chiếu), tìm được tại tại dòng thứ bao nhiêu sẽ trả về giá trị ở bảng chi tiết (dòng đầu tiên được đếm là 1). Trong đó:
- Giá trị tìm: có thể là 1 chuỗi. Số, toạ độ ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay chuỗi.
- Vùng cần tìm: Phạm vi khối dữ liệu dùng để so sánh với giá trị tìm.
- cột trả về:dòng trả về của hàm là cột thứ bao nhiêu trong bảng tham chiếu.
III. Nhóm hàm cơ sở dữ liệu
=DSUM(Vùng cơ sở dữ liệu, Cột tham chiếu, Vùng tiêu chuẩn)
Hàm tính tổng những mẩu tin thoả mãn điều kiện trên cột khai thác trong cơ sở dữ liệu
2. =DAVERAGE(Vùng cơ sở dữ liệu, Cột tham chiếu, Vùng tiêu chuẩn)
Hàm tính trung bình cộng cua nhung mẩu tin thoả mãn điều kiện trên cột khai báo trong cơ sở dữ liệu.
3. =DCOUNT(Vùng cơ sở dữ liệu, Cột tham chiếu, Vùng tiêu chuẩn)
Hàm đếm tổng số ô của những mẩu tin thảo mãn điều kiện trên cột khai thác trong một cơ sở dữ liệu
4. =DMAX(Vùng cơ sở dữ liệu, Cột tham chiếu, Vùng tiêu chuẩn)

Hàm tìm trị số cao nhất của những mẩu tin thoả mãn điều kiện trên cột khai thác trong một cơ sở dữ liệu
5. =DMIN(Vùng cơ sở dữ liệu, Cột tham chiếu, Vùng tiêu chuẩn)
Hàm tìm trị số thấp nhất của những mẩu tin thoả mãn điều kiện trên cột khai thác trong một cơ sở dữ liệu.
Giải thích ý nghĩa các hàm:
Vùng cơ sở dữ liệu: Phạm vi khối cơ sở dữ liệu cần khai thác
Cột tham chiếu: Số cột chứa dữ liệu cần khai thác trong khối cơ sở dữ luệu, được tính từ trái sang phải và bắt đầu từ số 1
Vùng tiêu chuẩn: Phạm vi khối điều kiện
Một số thông báo lỗi cần biết
Bài 6: Cơ sở dữ liệu trên bảng tính
Thao tác lọc, xoá và rút trích
1. Lọc
- Di chuyển con trỏ chuột vào một ô bất kì trong vùng dữ liệu (kể cả tiêu đề)
- Data / Filter / Advanced Filter
+ Trong Action: chọn nút chọn Filter the List, In-place
+ Trong List range: nhập địa chỉ vùng CSDL
+ Trong Criteria range: nhập địa chỉ vùng điều kiện.
+ Nếu chọn Unique record only: DL lọc ra nếu có những dòng trùng nhau sẽ trình bày chỉ 1 dòng.
- OK
2. Xoá
Thực hiện các bước giống thao tác lọc.
Sau khi các dòng thoả mãn điều kiện được trình bày, chọn các dòng này và thực hiện lệnh Edit / Delete Row
Thực hiện lệnh Data / Filter / Show all để trình bày trở lại các mẩu tin bị dấu đi trong thao tác lọc.
3. Rút trích
- Di chuyển con trỏ chuột vào 1 ô bất kì
- Data / Filter / Advanced Filter
+ Trong Action chọn Copy to another location
+ Trong List range: nhập địa chỉ vùng DL
+ Trong Criteria range: nhập địa chỉ vùng điều kiện
+ Trong Copy to: nhập địa chỉ dòng đầu vùng trích DL.
- OK.
bài 7: vẽ biểu đồ
Các loại biểu đồ
Trong Exel, có thể dựa vào dữ liệu trên worksheet để tạo các biểu đồ. Các biểu đồ trong Exel có rất nhiều loại:
Column: cột
Bar: Khối
Line: Đường kẻ
Pie: Hình tròn
XY: Hàm số
Area: Miền
.
ứng với mỗi loại biểu đồ, ta có thể chọn nhiều dạng khác nhau. VD ứng với kiểu Column


II. Các thành phần cơ bản trong biểu đồ
Value (Y) Axis
Title
Category (X) Axis
Legend
Title: Tiêu đề chung của biểu đồ
Value (Y) Axis: tiêu đề chung của trục tung
Category (X) Axis: tiêu đề chung của trục hoành
Legend: Chú thích minh họa
III. Vẽ biểu đồ
Trên Worksheet đã có sẵn một vùng dữ liệu. Di chuyển con trỏ vào vùng dữ liệu có sẵn trên Worksheet và thực hiện lệnh Insert / Chart hoặc Click vào biểu tượng Chart trên thanh công cụ Standard.

Chọn loại (Chart Type) và dạng (Chart sub-type) biểu đồ (VD chọn loại Column). Click nút Next sang bước kế tiếp. Hộp hội thoại Step 2 xuất hiện
Trong Data range xác định vùng dữ liệu muốn vẽ. Click nút Next sang bước kế tiếp. Hộp hội thoại Step 3 xuất hiện
Xác định tiêu đề và chú thích:
Titles để xác định tiêu đề
- Tiêu đề của biểu đồ nhập trong Chart title
- Tiêu đề trục hoành nhập trong Category (X) axis
- Tiêu đề trục tung nhập trong Value (Y) axis
Legend để bật hay tắt hoặc hiển thị chú thích minh họa
Sau đó Click Next để sang bước kế tiếp. Hộp hội thoại Step 4 xuất hiện.
Nếu muốn đưa biểu dồ vào worksheet hiện hành chọn As object in.
Nếu muốn tạo biểu đồ trên một sheet riêng chọn As new sheet.
Finish.