Bài giảng Microsoft Excel
Th.s: Lê Thị Thúy Hoa
Giảng viên Bộ môn Tin học – Viện ĐH Mở HN
Email: lehoavn@gmail.com

Organize
Calculate
Chart
Analyze
Khởi động Excel
C1: Kích đúp chuột vào biểu tượng trên nền màn hình (Destop).
C2: Kích chuột vào biểu tượng của Excel trên thanh Microsoft Office Shortcut Bar ở góc trên bên phải nền màn hình.
C3: Menu Start/Programs/Microsoft Excel
…
Tìm hiểu cơ bản về Excel
Workbooks là một file excel. Mỗi workbook gồm nhiều worksheets.
Worksheets mỗi worksheet được xem như một trang trong workbook. Đây là nơi để bạn nhập và xử lý dữ liệu. Worksheets được thể hiện dưới dạng các dòng và các cột. Có tất cả 256 cột và 65,536 dòng trong mỗi worksheet.
Một cell (ô)là giao của một dòng và một cột.
Formula Bar (thanh công thức)Là một thanh công cụ, khi nhập dữ liệu vào một ô, dữ liệu đó sẽ xuất hiện trên Formula Bar.

Cửa sổ làm việc của Excel
Một số thao tác cần chú ý
Thêm/xoá hàng, cột, bảng tính:
Thêm một hàng vào bên trên hàng hiện tại: menu Insert/Rows
Thêm một cột vào bên trái cột hiện tại: menu Insert/Columns
Thêm một bảng tính (sheet): menu Insert/Worksheet
Xoá hàng hiện tại: menu Edit/Delete…Entire Row
Xoá cột hiện tại: menu Edit/Delete…Entire Column
Xoá bảng tính: menu Edit/Delete Sheet

Một số thao tác cần chú ý (2)
Nhập một dãy liên tục cách đều vào các ô liền kề nhau (không bắt buộc tăng 1 đ.vị), vd: nhập STT, nhập các tháng trong năm…:
Nhập 2 g/t đầu tiên của dãy vào 2 ô tương ứng
Bôi đen 2 ô vừa nhập
Thực hiện Drag như khi sao chép công thức
Đổi tên sheet: nháy chuột phải tại tên sheet ở góc trái dưới bảng tính, chọn Rename, gõ tên mới cho sheet rồi ấn Enter.
Định dạng
1) Thay đổi kích thước hàng/cột: 2 cách chính:
C1: Di chuột vào mép hàng/cột, con trỏ thành hình mũi tên 2 chiều, ấn giữ trái chuột, di đến vị trí mới rồi nhả chuột.
C2: Di chuột vào mép hàng/cột, kích đúp để được kích thước vừa khít.
Có thể ấn định kích thước hàng/cột bằng cách vào menu Format/Row/Height… và Format/Column/Width…
Định dạng (2)
2) Định dạng ô (Menu Format/Cells…)
- Chọn miền, vào menu Formar/Cells…
+ Tab Number: định cách hiển thị số
+ Tab Alignment: định cách chỉnh vị trí dữ liệu
+ Tab Font: định font chữ
+ Tab Border: định đường kẻ viền các ô
Các kiểu dữ liệu, nhập dữ liệu trong Excel
Dữ liệu kiểu ký tự
Dữ liệu kiểu số
Dữ liệu kiểu ngày tháng
Nhập dữ liệu kiểu ngày tháng
Thử để kiểm tra
Gõ một ngày >12. Ví dụ 15/2/07
Nếu tự động căn tráinhập tháng trước
Nếu tự động căn phảinhập ngày trước
Gõ đúng thứ tự
Định dạng dữ liệu kiểu ngày tháng
Chú ý: Nếu dữ liệu gõ sai, định dạng k có tác dụng gì
Dữ liệu kiểu công thức và hàm
Nhập ngày trước hay tháng trước
Địa chỉ ô và miền
Địa chỉ ô và địa chỉ miền chủ yếu được dùng trong các công thức để lấy dữ liệu tương ứng.
Địa chỉ ô bao gồm:
Địa chỉ tương đối: gồm tên cột và tên hàng. Ví dụ: A15, C43.
Địa chỉ tuyệt đối: thêm dấu $ trước tên cột và/hoặc tên hàng nếu muốn cố định phần đó. Ví dụ: $A3, B$4, $C$5.
Địa chỉ tương đối thay đổi khi sao chép công thức, địa chỉ tuyệt đối thì không.
Địa chỉ ô và miền (tiếp)
Miền là một nhóm ô liền kề nhau.
Địa chỉ miền được khai báo theo cách:
Địa chỉ ô trái trên : Địa chỉ ô phải dưới
Ví dụ: A3:A6 B2:D5
$C$5:$D$8
Chọn miền, cột, hàng, bảng
Chọn miền: kích chuột vào ô trái trên, giữ và di tới ô phải dưới, nhả chuột.
Chọn cả hàng: kích chuột vào ô tên hàng.
Chọn cả cột: kích chuột vào ô tên cột.
Chọn cả bảng tính: kích chuột vào ô giao giữa tên hàng và tên cột.
Nếu chọn nhiều miền rời nhau thì giữ phím Ctrl trong khi chọn các miền đó.
Khi cần lấy địa chỉ ô hoặc miền trong công thức thì không nên gõ từ bàn phím mà nên dùng chuột chọn để tránh nhầm lẫn.
Gõ địa chỉ tuyệt đối của ô và miền trong công thức: dùng phím F4
1. Các toán tử trong Excell
Toán tử là thể hiện của phép nhân chia, công trừ, phép so sánh lơn nhỏ …
Đã được định nghĩa sẵn trong Ms Excell
Được định nghĩa về cú pháp (cách viết cho các toán tử) trong Excell

Bảng các toán tử
Bảng các toán tử
Hàm
Cú pháp chung của một hàm:


Tên hàm: Theo quy định của excel
Gọi hàm( sử dụng hàm )phải tuân theo nguyên tắc:
Đủ đối số
Đúng kiểu dữ liệu tương ứng cho từng đối số
Đúng thứ tự


Tên hàm( danh sách các đối số )
Hàm
Excel có rất nhiều hàm số sử dụng trong các lĩnh vực: toán học, thống kê, logic, xử lý chuỗi ký tự, ngày tháng …
Hàm số được dùng trong công thức.
Trong hàm có xử lý các hằng ký tự hoặc hằng xâu ký tự thì chúng phải được bao trong cặp dấu “ ”
Có thể nhập hàm số bằng cách ấn nút Paste Function fx trên Toolbar, rồi theo hướng dẫn ở từng bước.
Công thức
Công thức:
bắt đầu bởi dấu =
sau đó là các hằng số, địa chỉ ô, hàm số được nối với nhau bởi các phép toán.

Ví dụ: = 10 + A3
= B3*B4 + B5/5
= 2*C2 + C3^4 – ABS(C4)
= SIN(A2)
Một số hàm cơ bản
Hàm xử lý chuỗi
Hàm đếm số ký tự của chuỗi
Cú pháp:
LEN(x)
VD: =LEN(“Hà Nội”)
cho kết quả là 6
Hàm cắt trái
Cú pháp:
LEFT(x, n)
X: Chuỗi ký tự
N: số ký tự cần cắt
Chức năng: Cắt n ký tự bên trái của chuỗi.
N>=0
Nếu khuyết tham số nn=1
Nếu n>len(x) lấy toàn bộ chuỗi x
VD: =LEFT(“Hà Nội”,2)
cho kết quả là chuỗi “Hà”

Hàm xử lý chuỗi
Hàm cắt phải
Cú pháp
RIGHT(x, n)
X: Chuỗi ký tự
N: số ký tự cần cắt
Chức năng: Cắt n ký tự bên phải của chuỗi.

- N>=0
- Nếu khuyết tham số nn=1
- Nếu n>len(x) lấy toàn bộ chuỗi x

VD: =right(“ Hà Nội”,3)
cho kết quả là chuỗi “Nội”

Hàm xử lý chuỗi
Hàm cắt trong
Cú pháp
MID(x,s,n)
X: Chuỗi ký tự
S: Ký tự bắt đầu cắt
N: số ký tự cần cắt
Chức năng: Cắt n ký tự kể từ ký tự thứ s của chuỗi.

- s, n>=0

- Nếu s>len(x) trả về chuỗi rỗng

VD: =mid(“ Hà Nội”,4,1)
cho kết quả là chuỗi “N”

Hàm xử lý thời gian( 1 )
NOW(): Cho ngày và giờ ở thời điểm hiện tại.
TODAY(): Cho ngày hiện tại.
DAY(“mm/dd/yy”): Cho giá trị ngày.
VD: =DAY(“11/25/80”)
cho kết quả là 25
MONTH(“mm/dd/yy”): Cho giá trị tháng.
VD: =MONTH(“11/25/80”)
cho kết quả là 11
Hàm xử lý thời gian( 2 )
YEAR(“mm/dd/yy”): Cho giá trị năm.
VD: =YEAR(“11/25/80”)
cho kết quả là 1980
Hàm Year thường được dùng để tính tuổi khi biết ngày sinh:
Hàm xử lý thời gian( 3 )
MINUTE(): Trả về số phút của đối số
SECOND(): Trả về số giây của đối số.
VD: =minute(“9:15:04”)
cho kết quả là 15
=second(“9:15:04”)
cho kết quả là 04
=MINUTE("9:22:17"-"9:15:04")
cho kết quả là 7
Hàm tính toán số học
ABS(x): cho giá trị tuyệt đối của x.
VD: ABS(5)=ABS(-5)=5
INT(x): cho giá trị là phần nguyên của x.
VD: Int(5/3)=1
Int(-8/3)
Hàm làm tròn
Cú pháp
ROUND(x,n)
- x: Số cần làm tròn
- Nếu n>=0 làm tròn đến n ký tự đằng sau dấu phẩy
- Nếu n<0 làm tròn đến n ký tự đằng trước dấu phẩy
VD: =Round(3.567,2)
Round(1.578.625,-3)
Hàm tính toán số học
MAX (x1, x2, …, xn): cho giá trị lớn nhất.




MIN (x1, x2, …, xn): cho giá trị nhỏ nhất.

Hàm tính toán số học
Hàm tính tổng
CF:
SUM (x1, x2, …, xn):
Chức năng: Tính tổng của các số x1, x2, …, xn.

Hàm tính trung bình cộng
CF:
AVERAGE (x1, x2, …, xn):
Chức năng: Tính trung bình cộng của các số x1, x2, …, xn.

Hàm tính toán số học
Hàm chyển chữ số về số
CF:
VALUE (“x”)
Hàm đếm: COUNT(ds1, ds2,…)
Trả về số các ô chứa dữ liệu kiểu số, kiểu ngày hoặc giờ
Hàm đếm: COUNTA(ds1, ds2,…)
Trả về số các ô chứa dữ liệu.
Hàm logic
Biểu thức logic là gì?
Là một biểu thức mà có kết quả chỉ là True hoặc False
AND (btlg1,btlg2,…btlgn):
Hàm AND chỉ đúng khi tất cả các btlg có giá trị đúng.
VD: = AND (B3>=23,B3<25)


OR (btlg1,btlg2,…btlgn):
Hàm OR chỉ sai khi tất cả các đối số có giá trị sai.
VD: = OR (D3>=25,D3<23)
Hàm điều kiện
IF (bt logic, trị đúng, trị sai):
Hiển thị trị đúng nếu BT logic có g/t True
Hiển thị trị sai nếu BT logic có g/t False
VD: =IF(A3>=5,“Đỗ”,“Trượt”)
Hàm điều kiện
- Hàm IF có thể viết lồng nhau.
VD: = IF(C6<=300,1,IF(C6>400,3,2))











- Hàm trên cho kết quả của phép thử sau:
nếu [dữ liệu trong ô C6]  300
nếu 300 < [dữ liệu trong ô C6]  400
nếu [dữ liệu trong ô C6] > 400


Hàm tìm kiếm
Hàm tìm kiếm
VLOOKUP (GTTK, VùngTK, cột_cần tìm, [0/1])
GTTK:
Thuộc bảng CSDL
Đã có
VùngTK:
Thuộc bảng phụ
Cột đầu tiên chứa GTTK
Chứa cột cần tìm
Địa chỉ tuyệt đối
Cột_cần tìm:
Là số thứ tự của cột cần lấy trong vùng tìm kiếm

Hàm tìm kiếm
VLOOKUP (GTTK, VùngTK, cột_cần tìm, [0/1])

VD: = VLOOKUP(E3, $E$12:$F$16, 2, 1)
- Nếu g/t tra cứu nhỏ hơn g/t nhỏ nhất trong cột đầu tiên của bảng thì trả về lỗi #N/A.
- Nếu đối số thứ 4 bằng True (hoặc 1):
+ Các g/t trong cột đầu tiên của bảng phải được sắp xếp tăng dần.
+ Nếu g/t tra cứu không có trong cột đầu tiên của bảng thì hàm sẽ tra cứu g/t trong bảng  g/t tra cứu.
Hàm tìm kiếm
HLOOKUP(GTTK, VùngTK, dòng_cần tìm, [0/1])
Hàm tra cứu theo hàng, tương tự hàm VLOOKUP
Cơ sở dữ liệu (CSDL)1) Khái niệm
CSDL gồm các trường (field) và bản ghi (record).
Trường là một cột CSDL, mỗi trường biểu thị một thuộc tính của đối tượng và có kiểu dữ liệu nhất định.
Bản ghi là một hàng dữ liệu.
Dòng đầu của miền CSDL chứa các tên trường, gọi là dòng tiêu đề. Các dòng tiếp sau là các bản ghi.
2) Sắp xếp - Menu Data/Sort
Cách thao tác:
Chọn bảng DL. Chọn Menu Data/Sort…
Nếu chỉ sắp xếp theo một cột
Chọn cột cần sắp xếp/
Chọn Cột thứ nhất
[Chọn cột thứ hai]
[Chọn cột thứ ba]
Sắp xếp tăng dần
Sắp xếp giảm dần
Dòng đầu là tên trường (ko sắp xếp)
Ko có dòng tên trường (sắp xếp cả dòng đầu)
Xếp từ trên xuống dưới
Xếp từ trái sang phải
3) Tìm kiếm (Lọc dữ liệu)Menu Data/Filter
Mục đích: Lấy ra những bản ghi (thông tin) thoả mãn điều kiện nhất định.
Có thể lọc theo 2 cách:
AutoFilter: Excel hỗ trợ điều kiện lọc. Dùng trong trường hợp điều kiện lọc chỉ tác động vào một cột trong bảng DL.
Advanced Filter…: người sử dụng tự định điều kiện lọc. Dùng trong trường hợp điều kiện lọc tác động vào hai cột trong bảng DL trở lên.

a) Lọc dữ liệu dùng AutoFilter
Chọn miền CSDL gồm cả dòng tên trường
Menu Data/Filter/AutoFilter, ô tên trường có đầu mũi tên thả xuống của hộp danh sách
Kích chuột vào đó, có danh sách thả xuống:
All: để hiện lại mọi bản ghi
Top 10…: các giá trị lớn nhất
Custom…: tự định điều kiện lọc
Các giá trị của cột
Lọc dữ liệu dùng AutoFilter (tiếp)
Nếu chọn Custom… sẽ hiện hộp thoại Custom AutoFilter để người sử dụng tự định điều kiện lọc:
b) Lọc dữ liệu dùng Advanced Filter
B1: Tạo vùng điều kiện:
Dòng đầu ghi tên trường để tạo điều kiện, copy từ tên trường CSDL.
Các dòng tiếp dưới ghi điều kiện theo các cột tương ứng. Các điều kiện cùng dòng là phép AND, các điều kiện khác dòng là phép OR.
VD với miền CSDL như trên:
Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 =400
Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 >150
Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 150Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 >150 hoặc trong tháng 2 200
Lọc dữ liệu dùng Advanced Filter (tiếp)
B2: Thực hiện lọc
Vào menu Data/Filter/Advanced Filter…
Hiện KQ lọc ngay tại miền dữ liệu
Hiện KQ lọc ra nơi khác
Chọn vùng CSDL
Chọn vừa khít vùng điều kiện( b1)
Chọn vùng hiện KQ
Chỉ hiện 1 bản ghi trong số những KQ trùng lặp
Đồ thị
Chọn miền dữ liệu vẽ đồ thị, chú ý chọn cả 1 tiêu đề hàng và 1 tiêu đề cột đối với các đồ thị kiểu Column, Line và Pie.
Bấm nút Chart Wizard trên Toolbar hoặc vào menu Insert/Chart…  Hộp thoại Chart Wizard hiện ra giúp tạo đồ thị qua 4 bước:
Định kiểu đồ thị
Định dữ liệu
Các lựa chọn: tiêu đề, các trục, chú giải …
Chọn nơi hiện đồ thị
Bước 1: Định kiểu đồ thị
Chọn kiểu đồ thị có sẵn:
+ Column: cột dọc
+ Line: đường so sánh
+ Pie: bánh tròn
+ XY: đường tương quan
Chọn một dạng của kiểu đã chọn
Bước 2: Định dữ liệu
Miền DL vẽ đồ thị
Chọn DL vẽ đồ thị theo hàng hoặc theo cột
Tiêu đề hàng hiện tại đây
Tiêu đề cột làm chú giải
Bước 3: Các lựa chọn - Tab TitlesTiêu đề đồ thị và tiêu đề các trục
Nhập tiêu đề đồ thị
Nhập tiêu đề trục X
Nhập tiêu đề trục Y
Bước 3: Các lựa chọn - Tab LegendChú giải
Hiện/ẩn chú giải
Vị trí đặt chú giải
Chú giải
Bước 3: Các lựa chọn - Tab Data LabelsNhãn dữ liệu
Không hiện
Hiện nhãn và phần trăm
Nhãn dữ liệu
Hiện g/t
Hiện phần trăm
Hiện nhãn
Bước 4: Định nơi đặt đồ thị
Đồ thị hiện trên 1 sheet mới
Đồ thị hiện trên 1sheet đã tồn tại
Khi đồ thị đã được tạo, có thể:
Chuyển đồ thị tới vị trí mới bằng phương thức Drag & Drop.
Thay đổi kích thước đồ thị bằng cách kích chuột vào vùng trống của đồ thị để xuất hiện 8 chấm đen ở 8 hướng, đặt chuột vào chấm đen, giữ trái chuột và di tới kích thước mong muốn rồi nhả chuột.
Thay đổi các thuộc tính của đồ thị (tiêu đề, chú giải, …) bằng cách nháy chuột phải vào vùng trống của đồ thị và chọn Chart Options… Thao tác tiếp theo như bước 3 ở trên.
Thay đổi các thuộc tính của các thành phần đồ thị (font chữ, tỷ lệ các trục, màu sắc nền,…) bằng cách nháy chuột phải vào thành phần đó và chọn Format …
Thay đổi thuộc tính trục đồ thị* Thay đổi tỷ lệ trên trục
Giá trị nhỏ nhất
Giá trị lớn nhất
Khoảng cách các điểm chia
Chuột phải
trên trục,
chọn Format
Axis
Thay đổi thuộc tính trục đồ thị* Thay đổi vị trí hiển thị dữ liệu
Đối với đồ thị dạng Line, nhiều khi đồ thị vẽ xong như trên nhưng vẫn chưa chính xác vì các mốc thời gian không nằm đúng điểm chia trên trục X, do lựa chọn mặc định của Excel.
Thay đổi thuộc tính trục đồ thị* Thay đổi vị trí hiển thị dữ liệu
Để sửa đổi chỉ cần bỏ lựa chọn mặc định của Excel như hình trên là được. Tuy đơn giản nhưng cần nhớ vì hầu như 100% SV làm báo cáo TN mắc phải lỗi này mà không biết sửa.