Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

CHNG I : LM QUEN VI MICROSOFT EXCEL
1. Khởi động v thoát
1.1 Cách khởi động
Tng tự nh phần mềm word, Khởi động chơng trình Excel bao gồm các cách
Cách 1: Chọn lệnh Start của Windows: Start - Programs- Microsoft Excel
Cách 2: Nhấn kép chuột lên biểu tợng của chơng trình Excel
Cách 3 : Nếu muốn mở nhanh một tệp van bản vừa soạn thảo gần đây nhất trên
máy tính đang làm việc có thể chọn Start - Document, chọn tệp văn bản Excel
cần mở.
1.1 Thoát khỏi chơng trình Excel
phía bên phải góc trái màn hình

Cách 1: Kích chuột vào nút
Cách 2 : Vào file- exit

Cách 3 : ấn tổ hợp phím Alt + F4 ( ấn đồng thời)
2. Nhập tiếng việt trong Excel
Giống nh trong Word, để nhập tiếng việt trong Excel máy bạn cần phải cài
phần mềm tiếng việt nh Vietkey, ABCĐồng thời cách nhập tiếng việt trong Excel
cũng giống nh trong Word.
Lu ý: Nếu bạn chọn font chữ Vn.time thì bạn phải chọn bảng mã TCVN3, Nếu bạn
chọn font chữ Times New Roman thì bạn phải chọn bảng mã Unicode thì bạn mới
nhập đợc tiếng việt.
Cách nhập tiếng việt trong Excel
Gõ

Đợc chữ

Gõ

Đợc chữ

aa

â

f

huyền

aw

ă

j

nặng

oo

ô

x

ngã

ow


ơ

s

sắc

w



r

hỏi

dd



3. LU V M TP TIN TRONG EXCEL
- Thc hin nh trong Word
- Mở File văn bản: Chn File \ Open (Ctrl + O)
- Lu file vn bn Vo File \ Save (Ctrl + S): Trng hp t tờn file mi
Vo File \ Save As (F12): i vi file ó c t tờn


-1-


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666


CHƯƠNG II : SỬ LÝ DỮ LIỆU DỮ LIỆU TRONG BẢNG TÍNH
Song song với giáo trình Microsoft Word, Trung Tâm Tin Học AP cũng soạn
thảo giáo trình về bảng tính Microsoft Excel. Mong rằng những kiến thức này sẽ thực
sự bổ ích cho các bạn mới làm quen với bộ Office của Microsoft cũng như với những
người làm công tác văn phòng.
Có thể một số người đã quá quen thuộc với bảng tính Excel cũng không hề biết
rằng bảng tính bạn đang sử dụng có tối đa là 256 cột và 65.536 dòng; số lượn
worksheet tối đa bạn có thể thêm được là 255 sheet. Với con số khổng lồ này thì việc
quản lý các bảng sẽ trở nên đơn giản hơn nhiều vì hoàn toàn có thể lưu được 255 bảng
nằm trên 255 sheet chỉ trong một file Excel.
I. CÁC KIỂU DỮ LIỆU TRONG BẢNG TÍNH
Khi làm việc với bảng tính bạn sẽ phải làm quen với rất nhiều kiểu dữ liệu,
nhưng tất cả các kiểu dữ liệu đó đều dựa vào 3 kiểu cơ bản: Kiểu số, Kiểu chữ và
Kiểu công thức.
1. Kiểu chữ ( Text)
- Dữ liệu kiểu chữ luôn nằm ở phía bên trái của ô tính (cell), nó bao gồm các
chữ cái, chữ số và các ký tự đặc biệt. Nếu một ô tính có dữ liệu số muốn chuyển sang
chữ thì phải có dấu nháy đơn(‘) ở trước ô đó.

- Toán tử : Kiểu dữ liệu chữ chỉ có một toán tử duy nhất là toán tử (&).
VD: Nối 2 chuỗi ký tự sau “ Trung tâm” và “Tin Học AP” kết quả : “Trung
tâm tin học AP”


-2-


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666


2. Kiểu số:
Dữ liệu kiểu số luôn nằm ở bên phải của ô tính. Các giá trị ngày tháng, thời
gian, tiền tệ, phần trăm… đều là dữ liệu kiểu số (có thể tính toán cộng, trừ, nhân,
chia).
Chú ý: giá trị ngày tháng nếu bạn nhập đúng (thường là tháng/ngày/năm) thì sẽ nằm ở
bên phải của ô, nếu nhập sai thì sẽ ở bên trái ô (tương đương với kiểu chữ)

- Đối với kiểu dữ liệu ngày
tháng, cách nhập vào trong
excel theo kiểu.
- ( Tháng/ngày/năm) hoặc
- (Ngày/tháng/năm)
- Là do sự định dạng trong
Control Penel.

Cách làm: Start\Settings\Control Penel Chọn
Hộp thoại hiện ra như sau:
Chọn

Chọn

Chọn kiểu nhập
Ngày tháng


-3-


Tr−êng quèc tÕ cambridgevietanh

§T: 04.22.630.666

3. Kiểu công thức:
Dữ liệu kiểu công thức là các dữ liệu bắt đầu bởi các dấu: =, +, -, * (thông
thường nhất là sử dụng dấu =).
- Các công thức tính toán trong kiểu dữ liệu:
Cộng

+

Trừ

-

Nhân

*

Chia

/

Phần trăm

%

- Dấu ngăn cách giữa các phần thập phân thường là dấu chấm (.), còn dấu ngăn
cách giữa các số hàng nghìn là dấu phẩy (,). (Ví dụ: 1000000 = 1,000,000; còn ½ = 0.5)
- Ngoài ra với kiểu công thức thì thường sẽ kết hợp với các hàm tính toán
(phần này sẽ được giới thiệu trong các bài tiếp theo)

II. CÁC LOẠI ĐỊA CHỈ
Mỗi ô tính (cell) đều có một địa chỉ riêng biệt để phân biệt và tính toán. Địa chỉ
của ô được đặt tên theo ký hiệu cột và số dòng tương ứng của ô đó. (Ví dụ: ô C3 là ở
cột C, dòng 3), địa chỉ của ô bạn sẽ nhìn thấy ở phía bên trái thanh Formular (thanh
công cụ ngay phía trên của bảng tính).
Có 4 loại địa chỉ ô mà bạn phải ghi nhớ: Dùng phím F4 để thay đổi giữa các loại
địa chỉ
- Địa chỉ tương đối: là địa chỉ thông thường mà bạn hay thấy, địa chỉ này sẽ thay đổi cả
cột cả dòng khi sao chép công thức (phần này sẽ giới thiệu về sau). Ký hiệu địa chỉ tương
đối là: tencottendong (Ví dụ: C3)
- Địa chỉ tuyệt đối dòng: là địa chỉ
có dòng không thay đổi nhưng cột
thay đổi. Ký hiệu của địa chỉ tuyệt
đối dòng là: tencot$tendong (ví dụ:
C$3 là địa chỉ tuyệt đối dòng 3)
- Địa chỉ tuyệt đối cột: là địa chỉ có
cột không thay đổi nhưng dòng thay
đổi. Ký hiệu của địa chỉ tuyệt đối
cột là: $tencottendong (ví dụ: $C3
là địa chỉ tuyệt đối cột C)



-4-


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666

- Địa chỉ tuyệt đối: là địa chỉ mà cả cột và dòng đều không thay đổi khi sao chép công

thức. Ký hiệu của địa chỉ này là: $tencot$tendong (ví dụ: $C$3 là địa chỉ tuyệt đối cả
cột C và dòng 3)
-Vùng địa chỉ: bạn sẽ phải sử dụng vùng địa chỉ rất nhiều khi làm bảng tính, vùng địa
chỉ này thể hiện bạn đang chọn từ ô nào đến ô nào. Ký hiệu vùng địa chỉ như sau:
tencot1tendong1:tencot2tendong2 (Ví dụ bạn đang chọn vùng từ ô C3 đến ô H5 thì
vùng địa chỉ sẽ là C3:H5)
III. CĂN CHỈNH, ĐỊNH DẠNG DỮ LIỆU TRONG BẢNG TÍNH
Nếu bạn đã sử dụng Microsoft Word thì hẳn các bạn cũng nắm được một số
kiến thức cơ bản về việc căn chỉnh văn bản. Vì chúng ta làm việc với bảng tính chủ
yếu là tính toán nên căn chỉnh không nhiều, nhưng cũng phải đủ để người khác xem
các bảng biểu của bạn có thể nắm được cấu trúc của nó.
Chỉnh sửa dữ liệu trong ô: có 3 cách sau
- Click đúp chuột trái vào ô cần sửa
- Chọn ô cần sửa rồi nhấn phím F2
- Chọn ô cần sửa và sửa nội dung của ô trên thanh Formular Bar
Nếu muốn căn chỉnh, định dạng cho phần nào thì trước tiên bạn phải bôi đen
phần đó. Các bước tiếp theo làm như sau:
1. Định dạng kiểu dữ liệu hiển thị:
Vào Format -> Cells… -> Number



-5-


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666

- Genaral: kiểu mặc định cơ bản khi bạn nhập vào.
- Number: kiểu số có phân biệt phần thập phân với

• Decimal places: số chữ số hiển thị sau phần thập phân
• Use 1000 Separator (,): có sử dụng dấu (,) ngăn cách giữa các phần nghìn hay không
• Negative numbers: định dạng cho phần số âm.
- Currency, Accounting: kiểu tiền tệ
• Symbol: kiểu tiền tệ các nước
- Date: kiểu ngày tháng
- Time: kiểu thời gian
- Percentage: kiểu phần trăm
- Fraction: kiểu phân số
- Scientific: kiểu số viết tắt
- Text: kiểu chữ
- Special: kiểu đặc biệt
- Custom: kiểu người dùng tự định dạng
Ví dụ: bạn muốn định dạng ngày tháng hiển thị (số hiển thị có thể khác với thực chất
số bạn nhập vào) ở dạng ngày/tháng/năm: định dạng trong Custom là dd/mm/yyyy (dday, m- month, y- year)
2. Căn chỉnh, định dạng dữ liệu trong ô:
Vào Format -> Cells… -> Alignment

- Text alignment: căn chỉnh lề cho dữ liệu
• Horizontal: căn chỉnh theo chiều ngang của ô
+ General: mặc định theo dữ liệu nhập vào là chữ hay số


-6-


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666

+ Left (Indent): căn theo bên trái ô

+ Center: căn vào giữa ô (chiều ngang)
+ Right (Indent): căn theo bên phải ô
+ Fill: lấp đầy ô bằng chính dữ liệu đã có trong ô
+ Justify: căn đều 2 bên
+ Center Across Selection: căn giữa theo vùng được chọn (vùng bôi đen)
• Vertical: Căn chỉnh theo chiều dọc
+ Top: căn theo mép trên của ô
+ Center: căn nằm ở giữa ô (chiều dọc)
+ Bottom: căn theo mép dưới ô
+ Justify: căn đều
+ Distributed: tự căn định dạng.
- Text control:
Wrap text: cho phép dữ liệu tự xuống dòng trong ô (nếu bạn muốn xuống dòng dữ
liệu theo ý mình thì đặt con trỏ trước vị trí cần xuống dòng và nhấn Alt + Enter)
Shrink to fit: tự động co nhỏ dữ liệu khi ô bị thu nhỏ lại
Merge cells: trộn ô
- Right-to-left: hướng viết văn bản
Context: tuỳ thuộc dữ liệu nhập vào
Left-to-right: viết từ trái sang phải
Right-to-left: viết từ phải sang trái
- Orientation: định hướng hiển thị văn bản nằm ngang hay nằm dọc theo ô, có thể chỉnh dữ
liệu nằm chéo bằng cách kéo trục text hoặc chọn độ quay của text ở ô Degrees phía dưới.
3. Định dạng Font
Vào Format -> Cells… -> Fonts
Phần này tương tự như Word với:
- Font: định dạng font
- Font style: định dạng kiểu
chữ nghiêng, đậm hay thường
- Size: định dạng cỡ chữ
- Underline: kiểu gạch chân

- Color: màu chữ
- Effects:
• Strikethrough: định dạng
gạch ngang giữa chữ
• Superscript: chỉ số trên
• Subscript: chỉ số dưới


-7-


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666

4. Định dạng viền ô, viền bảng:
Vào Format -> Cells… -> Border
- Presets: định vị trước cho viền
• None: không có viền
• Outline: định dạng đường
viền ngoài
• Inside: định dạng đường viền trong
- Border: định đạng dường viền
chi tiết với nét trên, nét dưới,
nét trái, nét phải, nét sổ dọc
giữa và nét sổ ngang giữa.
- Line: kiểu đường viền
• Style: các kiểu đường viền
• Color: màu cho đường viền
- Nếu muốn định dạng màu cho ô thì vào Format -> Cells… -> Patterns và chọn màu.
- Ngoài ra, nếu muốn định dạng nhanh bạn có thể sử dụng các chức năng sẵn có

trên thanh công cụ Formatting để định dạng

5. Xoá toàn bộ định dạng đã làm
Trong trường hợp bạn đã định dạng xong nhưng lại muốn xoá định dạng đi để làm
lại (xoá không mất dữ liệu) bạn làm như sau:
- Bôi đen toàn bộ dữ liệu muốn bỏ định dạng
- Vào Edit -> Clear -> Format
6. Cách thức tính toán và sao chép công thức trong Excel
a, Nhập công thức tính toán:
Nếu bạn muốn tính toán với giá
trị của ô nào thì sử dụng địa chỉ của ô đó
để tính toán
Ví dụ: Bạn muốn cộng 2 số ở
địa chỉ B5 và địa chỉ C5 với nhau thì
công thức tại ô cần đặt kết quả là
=C5+B5 (không nhất thiết phải nhớ địa
chỉ của các ô, bạn chỉ cần gõ dấu bằng
(=) sau đó click chuột vào ô B5, gõ tiếp
dấu cộng (+) rồi click chuột vào ô C5, cuối cùng nhấn Enter bạn sẽ có được kết quả)


-8-


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666

b, Sao chép công thức:
Nếu với một bảng biểu như
hình trên, bạn không phải tính cộng

cho từng dòng của bảng mà chỉ cần
nhập công thức ở một ô duy nhất trong
cột kết quả. Sau khi nhập xong công
thức và nhấn Enter, đưa chuột vào góc
dưới bên phải của ô (trỏ chuột xuất
hiện là dấu cộng màu đen) vừa nhập
giữ chuột trái vừa kéo lên trên hoặc
xuống dưới (có thể kéo theo hàng
ngang nếu kết quả nằm ở hàng ngang)
c. Sao chép kiểu đặc biệt
- Bạn thực hiện các phép tính
toán hay sử dụng các hàm, bạn muốn copy kết quả đó đến một vị trí khác trong bảng
tính hoặc một sheet khác. Nếu bạn chỉ copy bình thường thì sẽ không có được kết quả
theo ý mình, để thực hiện lệnh copy này chúng ta dùng kiểu copy đặc biệt.
- Thực hiện thao tác copy bình thường:
- Đưa con trỏ đến vùng cần sao chép, kích chuột phải vào Paste special…( hoặc
vào Edit\Paste special) hộp thoại xuất hiện như sau:
- Paste
+ All : Copy tất cả
+ Formulas : Copy công thức
+ Values : Copy giá trị
+ Formats: sao chép định dạng
+ Comments : Sao chép chú thích
+ Validation : Sao chép biểu thức quy
định tính hợp lệ của dữ liệu nhập
+ All except borders : sao chép tất
cả trừ đường viền
+ Column widths : sao chép độ rộng cột
+ Formulas and number formats
+ Values and number formats

- Operation : sao chép có sử dụng
toán tử tính toán
+ None : Không thực hiện tính toán
+ Add : Copy đồng thời thực hiện phép cộng
+ Subtract : Phép trừ
+ Multiply : Phép nhân
+ Divide : Copy đồng thời thực hiện phép chia
- Skip blanks : Sao chép bỏ qua những ô rỗng ( không có dữ liệu)
- Transpose : Sao chép chuyển định vị : Từ hàng thành cột hoặc từ cột thành hàng


-9-


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

CHNG III : GII THIU V S DNG CC
HM C BN TRONG EXCEL
Hàm (Function) đợc xem nh là các công thức định sẵn nhằm thực hiện các
tính toán chuyên biệt. Trên ô thực hiện hàm sẽ cho một giá trị hoặc một thông
báo lỗi. Excel có trên 300 hàm và đợc phân loại thành từng nhóm.
I. Hàm ngày tháng
1. DATE(year,month,day)
Chỉ ra ngày dạng số ứng với ngày tháng năm.
Ví dụ : =DATE(04,09,27) trả về 27-09-04
2. DAY(date)
Số ngày trong tháng của biến ngày tháng date.
Ví dụ : =DAY(27-09-04) trả về 27
=DAY("27-Sep") trả về 27

3. MONTH(date)
Số tháng trong năm của biến ngày tháng date.
Ví dụ : =MONTH(27-09-04) trả về 9
=MONTH("27-Sep") trả về 9
4. Time(hour, minute, second)
Chỉ ra thời gian dạng số.
Ví dụ : =TIME(19,5,14) trả về 19:05:14 hoặc 7:05 PM
5. WEEKDAY(date)
Chỉ ra số thứ tự của ngày trong tuần của biến ngày tháng date (Thứ Hai là
ngày thứ 1, Thứ Ba là ngày thứ 2, ..., Chủ Nhật là ngày thứ 7)
Ví dụ : =WEEKDAY(27-09-04) trả về 6
6. YEAR(date)
Số năm của biến ngày tháng date.
Ví dụ : =YEAR(27-09-04) trả về 2004
II. Hàm ký tự
1 LEFT(text, number)
- Text : Chuỗi ký tự
- Number : Số ký tự sẽ lấy ra trong chuỗi ký tự ( Text)
- Lấy number ký tự bên trái của text.
Ví dụ : =LEFT( Trung tâm tin học AP",5) trả về Trung
2 RIGHT(text, number)
- Text : Chuỗi ký tự
- Number : Số ký tự sẽ lấy ra trong chuỗi ký tự ( Text)


- 10 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666


-

Lấy number ký tự bên phải của text.
Ví dụ : =RIGHT("Trung tâm tin học AP",2) trả về AP
3 MID(text, numstart, numchar)
- Text : Chuỗi ký tự
- Numstart : Vị trí ký tự bắt đầu lấy ra trong chuỗi ký tự ( Text)
- Numchar : Số ký tự lấy ra kể từ vị trí bắt đầu (Numstart)
Trả lại numchar ký tự của text bắt đầu từ vị trí numstart. Ví dụ :
=MID("Trung tâm tin học AP",11,7) trả về tin học
Lu ý: Dấu cách trong hàm đợc tính khi lấy
4 PROPER(text) Chuyển các chữ cái đầu từ của text thành chữ viết hoa.
Ví dụ : =PROPER("trung tâm tin học ap") trả về Trung Tâm Tin Học Ap
Chú ý : nếu text là tiếng Việt, hàm này có thể sẽ cho kết quả sai.
Ví dụ : = PROPER("việt") trả về Việt
5 LOWER(text)
Chuyển text thành chữ thờng.
Ví dụ : =LOWER("EXCEL") trả về excel
6 UPPER(text)
Chuyển text thành chữ in hoa toàn bộ.
Ví dụ : =UPPER("excel") trả về EXCEL
7 VALUE(text)
Chuyển text sang số.
Ví dụ : =VALUE(RIGHT("Tel. 533332",6)) trả về 533332
III. Hàm số
1. ABS(x) :
Giá trị tuyệt đối của số x.
VD: =ABS(-5) cho kết quả: 5
2. COuntif(range, criteria):

Đếm số ô không rỗng thoả tiêu chuẩn cho trớc
- Range : Là tập hợp các ô mà ta muốn đếm
- Criteria:Tiêu chuẩn, có thể là số, chữ hoặc biểu thức, xác định xem sẽ đếm ô nào.
+ Phải để biểu thức trong dấu nháy kép
3. ROUND(x,n)
Làm tròn số x với độ chính xác đến con số thứ n
- Nếu n < 0 thì x đợc làm tròn đến chữ số thập phân thứ n
- Nếu n > 0 thì x đợc làm tròn đến chữ số bên trái thứ n của dấu (chấm) thập phân
Ví dụ : ô A1 chứa số 347 645.146, khi đó công thức
=ROUND(A1,2) trả về 347 645.15


- 11 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

=ROUND(A1,1) trả về 347 645.10
=ROUND(A1,-3) trả về 348 000 (làm tròn đến hàng nghìn)
4. SQRT(x): hàm căn bậc hai của x.
5. SUM(n1, n2, ..): tổng của các số n1, n2,..
6. SUMif(range,criteria,sum_range) : Cộng những ô thoả điều kiện nào đó.
Range
Là vùng ô để so sánh với Criteria
Criteria
Là điều kiện cộng, có thể là số, chữ hoặc biểu thức.
Quyêt định ô nào trong vùng Sum_Range sẽ đợc cộng
Sum_Range Là vùng ô sẽ đợc cộng
Các ô trong Sum_range chỉ đợc cộng nếu các ô tơng ứng

với nó trong Range thoả mãn Criteria
Hàm SUMIF chỉ tính tổng theo 1 điều kiện. Nếu cần tính tổng theo 2 điều kiện
trở lên phải dùng công thức mảng hoặc hàm DSMUM.
IV. Hàm Logic
1. AND(logic1,logic2,..)
Nhận giá trị TRUE (Đúng) nếu tất cả các biểu thức logic1, logic2,... đều là
TRUE, nhận giá trị FALSE (Sai) nếu có ít nhất một đối số là FALSE
Ví dụ : =AND(5>3,6>4) trả về TRUE
=AND(5>3,6<=4) trả về FALSE
2. IF(logical_test,value_if_true,value_if_false)
Trả lại giá trị ghi trong value_if_true nếu logical_test là TRUE và giá trị ghi trong
value_if_false trong trờng hợp ngợc lại. Hàm IF có thể lồng nhau đến 7 cấp.
3. NOT(logic) : hàm phủ định.
Ví dụ : = NOT(1+1>2) trả về TRUE
= NOT(1+1=2) trả về FALSE
4. OR(logic1,logic2,...)
Nhận giá trị TRUE nếu một trong các biểu thức logic1,logic2,.. là TRUE,
nhận giá trị FALSE nếu tất cả các biểu thức đó là FALSE.
Ví dụ : =OR(5>3,6<=4) trả về TRUE
=OR(5<3,6<=4) trả về FALSE
V. Hm thống kê
1 AVERAGE(num1, num2,..)
Tính trung bình cộng của các số num1, num2,... Ví dụ
: =AVERAGE(10,8,9,3) trả về 7.5
2 COUNT(Địa chỉ) : tính số các ô dữ liệu kiểu số trong miền Địa chỉ


- 12 -



Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

3 COUNTA(DC)
Tính số các ô không rỗng trong vùng DC.
4 MAX(num1,num2,..) : giá trị lớn nhất của các số num1,num2,..
5 MIN(num1,num2,..) : giá trị nhỏ nhất của các số num1,num2,..
6 RANK(số, Danh sách, Tuỳ chọn) : xác định thứ hạng của số so với chuỗi các
số trong danh sách, tức là xem số đó đứng thứ mấy trong chuỗi số
- xếp giảm dần nếu không có Tuỳ chọn hoặc Tuỳ chọn bằng 0 (Ví dụ 1).
- xếp tăng dần nếu Tuỳ chọn là một số lớn hơn 0 (Ví dụ 2).
Địa chỉ của Danh sách phải là tuyệt đối.

VI. Hm Tìm kiếm v Tham chiếu
1. vlookup(lookup_value, table_array, col_index_num, range_lookup)
- Lookup_value: Giá trị đợc tìm kiếm trên cột bên trái của Table_array
- Table_array: Vùng tìm kiếm hay còn gọi là bảng tra cứu, địa chỉ phải là tuyệt đối,
nên đặt tên cho vùng. Trong ví dụ dới thì Table_array là vùng $J$8:$K$11
- Col_index_num: Số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá trị trả về.
Số thứ tự cột này đợc tính từ trái sang phải, trong ví dụ dới thì số thứ tự cột mà hàm
sẽ lấy giá trị là 2.
- Range_lookup:Giá trị logic xác định việc tìm kiếm là chính xác hay gần đúng, nếu là:
+ True hay 1: Cột đầu tiên phải đợc sắp xếp tăng dần (khi đó có thể bỏ qua
tham số thứ 4 này). Khi không thấy sẽ lấy kết quả gần đúng, vì thế còn gọi là dò tìm
không chính xác.


- 13 -



Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

* VD : Dùng hàm Vlookup() để điền cột xếp loại, nếu bạn nhập công thức nh
hình minh họa ở ô H4, có nghĩa hàm sẽ tìm các giá trị gần đúng để điền và trong VD
này, công thức ở ô H4 mới là công thức đúng.
+ False hay 0 Cột đầu tiên không cần sắp xếp .Tìm chính xác, trả về
#N/A nếu không thấy.

2. hlookup(lookup_value,table_array,row_index_num,range_lookup)
Hàm này hoạt động giống nh hàm VLOOKUP, điểm khác là : Lookup_value
Giá trị đợc tìm kiếm trên hàng đầu tiên của Table_array Table_array Vùng tìm kiếm
viết thành hàng Row_index_num Số thứ tự hàng trong table_array, nơi HLOOKUP sẽ
lấy giá trị trả về



- 14 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

chơng iv : Quản trị dữ liệu
I. Các khái niệm
Cơ sở dữ liệu (CSDL) là tập hợp các dữ liệu đợc sắp xếp trên một vùng chữ
nhật (gồm ít nhất 2 hàng) của bảng tính theo quy định sau :

Hàng đầu tiên ghi các tiêu đề của dữ liệu, mỗi tiêu đề trên một cột. Các
tiêu đề này đợc gọi là trờng (field)

Từ hàng thứ hai trở đi chứa dữ liệu, mỗi hàng là một bản ghi (record)
- Chú ý : + Tên các trờng phải là dạng ký tự, không đợc dùng số, công thức,
toạ độ ô...).Nên đặt tên trờng ngắn gọn, không trùng lắp.
+ Không nên có miền rỗng trong CSDL
- Nếu định sắp xếp cho toàn bộ CSDL: về ô bất kỳ của nó. Nếu chỉ định sắp xếp cho
một số bản ghi : chọn miền dữ liệu cần đa vào sắp xếp. Xuất hiện hộp thoại Sort. Excel
có thể sắp xếp theo 3 khoá (điều kiện). Chọn (bấm nút chuột tại) của khung này. Xuất
hiện danh sách trải xuống ghi tên hoặc thứ tự các trờng. Chọn trờng cần thiết.
ii. sắp xếp dữ liệu
- Nếu định sắp xếp cho toàn bộ
CSDL: về ô bất kỳ của nó. Nếu chỉ định
sắp xếp cho một số bản ghi : chọn miền
dữ liệu cần đa vào sắp xếp. Xuất hiện
hộp thoại Sort. Excel có thể sắp xếp theo
3 khoá (điều kiện). Chọn (bấm nút chuột
tại)
của khung này. Xuất hiện danh
sách trải xuống ghi tên hoặc thứ tự các
trờng. Chọn trờng cần thiết.
Sau đây là ý nghĩa các mục :
- Sort by
- Then by
- Ascending
- Desending
- My List Has

: cột u tiên nhất trong khoá sắp xếp.
: cột u tiên thứ hai và thứ ba trong khoá sắp xếp
: sắp xếp tăng dần
: sắp xếp giảm dần

: Header Row (hoặc No Header Row): miền dữ
liệu chứa (hoặc không chứa) hàng tiêu đề.

* Nút Option
- Case Sensitive : phân biệt chữ hoa với chữ thờng
- Orientation
:
Sort Top To Bottom : sắp xếp các dòng trong CSDL
Sort Left To Right
: sắp xếp các cột trong CSDL
Chọn OK để bắt đầu sắp xếp.


- 15 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

Hình trên là hộp thoại Sort với các thông số để danh sách đợc xếp theo vần A, B, C
của Tên, những bản ghi trùng tên xếp ngời có điểm Trung bình cao lên trên, những
bản ghi trùng điểm Trung bình xếp ngời có điểm Tin cao lên trên.
III. Lọc dữ liệu
1. Lọc tự động (AutoFilter)
- Chọn miền dữ liệu định lọc (kể cả hàng tiêu đề).
- Data/Filter/AutoFilter,
Excel tự động chèn những mũi tên vào bên phải của các tên trờng.
- Chọn

tại cột chứa dữ liệu dùng làm tiêu chuẩn để lọc (ví dụ tại cột Xếp loại).


- Chọn một trong các mục tại Menu :
[All]

: Hiện toàn bộ các bản ghi

[Blanks]

: Chỉ hiện các bản ghi trống

[Nonblanks] : Chỉ hiện các bản ghi không trống
[Custom ...]

: Dùng các toán tử so sánh (sẽ đợc trình bày chi tiết
trong phần tiếp theo)

Phần còn lại là danh sách giá trị của các bản ghi trong CSDL tại cột đó. Khi cần
lọc các bản ghi theo một giá trị cụ thể nào đó chỉ cần chọn giá trị đó trong Menu (ví
dụ chọn Trung bình).
Dùng các toán tử so sánh
Khi chọn mục này xuất hiện hộp thoại sau với 2 khung nhỏ để ghi tiêu chuẩn so
sánh : Ví dụ ta muốn đa dữ liệu ( Bản ghi) ở cột xếp loại là Giỏi và Xuất sắc ta thực
hiện nh sau.


- 16 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666


- Ta chọn Custom hộp thoại hiện ra nh hình dới

- ở dòng tiêu chuẩn thứ nhất chọn equals ( có nghĩa là =) và giỏi
- ở tiêu chuẩn thứ 2 ta chọn equals ( có nghĩa là =) và Xuất sắc
- Toán tử liên kết giữa 2 điều kiện này là phép OR
- Kích OK để thực hiện, Kết quả nh hình dới

2. Lọc nâng cao (Advanced Filter)
Advanced Filter dùng để tìm các bản ghi thoả mãn các điều kiện phức tạp hơn.
Chức năng lọc nâng cao này ứng với với các tiêu chuẩn so sánh gián tiếp, bắt buộc
phải dùng miền tiêu chuẩn. Các bớc nh sau :
- Tạo miền tiêu chuẩn.
- Chọn miền dữ liệu định lọc
- Data, Filter
- Chọn Advanced Filter, ý nghĩa các mục trong hộp thoại Advanced
Filter nh sau :


- 17 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

Action :
Filter the List, in place

: Lọc tại chỗ (ngay tại vị trí của CSDL chỉ
hiện các bản ghi thoả mãn tiêu chuẩn lọc)

Copy to Another Location
: Trích các bản ghi đạt tiêu chuẩn lọc sang miền
khác của bảng tính, địa chỉ của miền này đợc
xác định tại khung Copy to
List Range
: Địa chỉ miền dữ liệu nguồn đem lọc
Criteria Range
: Địa chỉ miền tiêu chuẩn
Copy to
: Địa chỉ miền đích để chứa các bản ghi đạt tiêu chuẩn lọc)
Unique Record Only : Chỉ hiện 1 bản ghi trong số các bản ghi trùng nhau.
Ví dụ : Để lọc ra danh sách những ngời có điểm Excel = 9, Hình minh hoạ dới

Copy to

- Kết quả thu đợc:



- 18 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

Chú ý
Khi trích lọc dữ liệu với 2 tiêu chuẩn trở lên, khi tạo vùng điều kiện tuân theo
nguyên tắc: Cùng hàng là phép và(AND), khác dòng là phép hoặc (OR).
IV. Các hm cơ sở dữ liệu
1. Chức năng

Dùng để trả lại một giá trị từ CSDL theo một điều kiện nào đó. CSDL sau
đây dùng để minh hoạ cho các ví dụ.
2. Một số hàm cơ sở sữ liệu
- Phần này chỉ giới thiệu để các bạn tham khảo, phần này sẽ đợc trình bày rõ
và sâu trong Excel nâng cao.
DSUM (database, field, criteria)
Tính tổng trên một cột (field) của CSDL (database) thoả mãn điều kiện
ghi trong miền tiêu chuẩn (criteria)
DAVERAGE (database, field, criteria)
Tính giá trị trung bình cộng trên một cột (field) của CSDL (database)
thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria)
DMAX (database, field, criteria)
Tính giá trị lớn nhất trên một cột (field) của CSDL (database) thoả mãn
điều kiện ghi trong miền tiêu chuẩn (criteria)
DMIN (database, field, criteria)
Tính giá trị nhỏ nhất trên một cột (field) của CSDL (database) thoả mãn
điều kiện ghi trong miền tiêu chuẩn (criteria)
DCOUNT (database, field, criteria)
Đếm số bản ghi của CSDL (database) trên cột số (field) thoả mãn điều kiện ghi
trong miền tiêu chuẩn (criteria)
DCOUNTA (database, field, criteria)
Đếm số ô không rỗng của cột bất kỳ (field) thoả mãn điều kiện ghi trong miền
tiêu chuẩn (criteria). Khác với DCOUNT, trong công thức của hàm này có thể ghi số
thứ tự hoặc tên của trờng bất kỳ nào của CSDL.
V. Tổng kết theo nhóm
a.Tổng kết theo một loại nhóm (SubTotal) :
hức năng mới này của Excel
cho phép tạo các dòng tổng kết trong
một CSDL.SubTotal sẽ chèn tại các vị trí
cần thiết các tính toán thống kê theo

yêu cầu của ngời sử dụng. Các bớc
thực hiện nh sau :
1/ Sắp xếp dữ liệu theo trờng cần tạo
SubTotal (ở ví dụ trên là trờng Xếp loại).
2/ Chọn Data, Subtotals, xuất hiện hộp thoại Subtotal.


- 19 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

Trong đó :
- At Each Change in : Chọn trờng mà theo trờng này, tại mỗi vị trí thay đổi,
Excel sẽ chèn vào một dòng Tổng kết - tức là dòng thực hiện các phép tính (ở ví dụ trên
là trờng Xếp loại, ta sẽ tính tổng số học sinh xếp loại khác nhau, dùng hàm count)
- Use Function : Chọn hàm để tính toán tổng kết dữ liệu. Hàm ngầm định là
SUM. ở ví dụ trên dùng hàm Count để đếm số lợng của một số trờng.
- Add SubTotal to : Chọn các trờng cần tính toán (ở ví dụ trên là tính số
lợng học sinh xếp loại Xuất sắc, loại giỏi, loại khá...)

- Nhìn vào bảng kết quả ta thấy đợc:
+ Tổng số học sinh loại giỏi là : 5
+ Tổng số học sinh loại khá là : 4
+ Tổng số học sinh loại xuất sắc là : 1
+ Tổng số các học viên là : 10
Các tuỳ chọn khác :
Replace Current SubTotal : Khi tạo dòng tổng kết mới dòng này sẽ thay thế
dòng tổng kết cũ. Theo ngầm định các dòng tổng kết sẽ nối tiếp nhau.

- Page Break Between Group : Chèn dấu ngắt trang tại mỗi vị trí có dòng
SubTotal (tức là đa mỗi nhóm sang một trang riêng biệt).
- Summary Below Data : Đặt dòng tổng kết ở cuối mỗi nhóm. Nếu bỏ dấu
dòng này sẽ đợc đa lên trớc mỗi nhóm.

Remove All : Huỷ bỏ mọi SubTotal đã thực hiện.
b - Tổng kết theo nhiều loại nhóm (PivotTable - Bảng Tổng hợp) :
Chức năng này của Excel cho phép tự động hoá quá trình tổng kết theo nhiều
loại nhóm, phân tích và đánh giá số liệu mà sau đây chúng tôi gọi là


- 20 -


Tr−êng quèc tÕ cambridgevietanh
§T: 04.22.630.666

B¶ng Tæng hîp.
B¶ng sau lµ mét CSDL vÒ doanh thu cña mét c¬ quan kinh doanh gåm 2 cöa
hµng ký hiÖu lµ Sè 1 vµ Sè 2.

1
2
3
4
5
6
7
8
9

10
11

A

B

C

D

E

Ngµy
29-08-97
29-08-97
30-08-97
30-08-97
30-08-97
03-09-97
03-09-97
03-09-97
04-09-97
04-09-97

Nh©n viªn
H−¬ng
Lan
Chi
Nga

V©n
Chi
Lan
Nga
Chi
V©n

Cöa hµng
Sè 1
Sè 1
Sè 1
Sè 2
Sè 2
Sè 1
Sè 1
Sè 2
Sè 1
Sè 2

S¶n phÈm
KÑo
B¸nh
B¸nh
Møt
KÑo
B¸nh
KÑo
KÑo
Møt
B¸nh


TiÒn
20
10
40
15
25
30
45
20
10
35

Tõ CSDL trªn, tæng hîp theo tõng cöa hµng, tõng nh©n viªn vµ tõng ngµy cho
b¶ng Tæng hîp sau:

Cöa hµng

(All)

Sum of TiÒn
Nh©n viªn
Chi

Chi Total
H−¬ng
H−¬ng Total
Lan
Lan Total
Nga


Ngµy
03-09-97
04-09-97
30-08-97
29-08-97
03-09-97
29-08-97
03-09-97
30-08-97

Nga Total
V©n

04-09-97
30-08-97

V©n Total
Grand Total



S¶n phÈm
B¸nh

KÑo

Møt

Grand Total


30
0
40
70
0
0
0
10
10
0
0
0

0
0
0
0
20
20
45
0
45
20
0
20

0
10
0

10
0
0
0
0
0
0
15
15

30
10
40
80
20
20
45
10
55
20
15
35

35
0
35

0
25
25


0
0
0

35
25
60

115

110

25

- 21 -

250


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

- Việc sử dụng bảng tổng kết trên giúp chúng ta theo dõi, quản lý dữ liệu một cách
tổng hợp, khoa học và thực hiện đối với dữ liệu lớn.
- Qua bảng tổng kết trên ta thấy rõ kết quả làm việc của từng nhân viên, từ đó ta có
thể so sánh các nhân viên với nhau để thấy nhân viên nào làm việc tốt hay không tốt.
Các khái niệm cơ bản
ở bảng CSDL trên, tiêu đề của các cột là các "trờng" (field): Ngày, Nhân viên,
Cửa hàng, Sản phẩm và Tiền. Các trờng này sẽ tham gia vào việc xây dựng bảng

Tổng hợp này.
Bảng Tổng hợp đợc chia làm 4 vùng :
Page Field : Toàn bộ dữ liệu đợc tổng kết theo từng nhóm (Item) của trờng này.
Page Field luôn nằm ở phía trên của bảng Tổng hợp. Trong ví
dụ này, Page Field là Cửa hàng gồm 2 nhóm Số 1 và Số 2.
Row Field : Mỗi nhóm dữ liệu của trờng này đợc tổng kết trên một dòng, vì vậy
đợc gọi là "Row". Nếu số Row Field nhiều hơn 1, PivotTable
sẽ tổng kết các trờng này theo kiểu lồng nhau theo thứ tự từ trên xuống dới. Trong
ví dụ này, có 2 Row Field là Nhân viên và Ngày, trờng Nhân viên đợc tổng kết trớc
rồi mới đến trờng Ngày.
Column Field : Mỗi nhóm dữ liệu của trờng này đợc tổng kết trên một cột, vì
vậy đợc gọi là "Column". Nếu số Nhóm này nhiều hơn 1, PivotTable sẽ tổng
kết các nhóm này theo thứ tự từ trái qua phải. Trong ví dụ này, các Nhóm đợc tổng
kết thành các cột (trờng) theo thứ tự ABC của tên nhóm: Bánh, Kẹo Mứt.
Data Field : vùng chính của Bảng Tổng hợp ghi kết quả của một phép toán. Trong ví
dụ này, số liệu cuả trờng Tiền đợc đa vào và phép toán tổng kết là lấy tổng (SUM).
Tạo mới một Bảng Tổng hợp
- Chọn miền dữ liệu kể cả tiêu đề của các cột (ví dụ A1:E11)
- Chọn mục Data, PivotTable
Hộp thoại PivotTable Wizard step 1 of 4 xuất hiện.
- Chọn Microsoft Excel List of Database
- Chọn Next
Hộp thoại PivotTable Wizard step 2 of 4 xuất hiện. Nếu cần, sửa lại địa chỉ của
miền dữ liệu bằng cách gõ trực tiếp hoặc dùng chuột chọn lại.
- Chọn Next. Xuất hiện hộp thoại PivotTable Wizard Step 3 of 4.
- Chọn các trờng để đa vào các vùng của bảng bằng cách kéo và thả tên
trờng ở bên phải hộp thoại tới các vùng đợc ấn định trong bảng.
ở ví dụ này chúng tôi đa vào các mũi tên (chỉ hớng kéo thả) để ta đọc tiện
Kéo trờng
Cửa hàng

Nhân viên
Ngày
Sản phẩm
Tiền


thả tại vùng
PAGE
ROW
ROW
COLUMN
DATA
- 22 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

Sau thao tác trên, hộp thoại có dạng:
Chọn Next để chuyển sang bớc
PivotTable Wizard Step 4 of 4.
Trong hộp PivotTable Starting Cell ta
trái trên) của bảng Tổng hợp (ví dụ : nếu ta
Sheet1!$A$13). Nếu không, bảng Tổng hợp
sau Sheet hiện tại).

tiếp theo. Xuất hiện hộp thoại
chỉ ra địa chỉ của ô đầu tiên (góc
chọn ô A13, Excel sẽ tự điền vào
sẽ đợc để ở Sheet mới (tiếp ngay


Trong hộp PivotTable Name ta gõ vào tên của bảng Tổng hợp (trờng hợp cần
tạo nhiều bảng Tổng hợp cho một CSDL), ví dụ Tổng kết Bán hàng.
ý nghĩa các lựa chọn khác nh sau :
Grand Total For Columns: Tạo thêm cột tổng cho mỗi số liệu của Column Field.
Grand Total For Rows : Tạo thêm hàng tổng cho mỗi số liệu của Row Field.
Save Data With Table Layout : Bảng Tổng hợp đợc lu trữ cùng với Format đợc tạo.
AutoFormat Table :Tự động tạo khuôn cho Bảng Tổng hợp
Điều chỉnh Bảng Tổng hợp
Thay đổi vị trí các trờng : Trong Bảng Tổng hợp kéo và thả tên trờng tại vị trí
mong muốn. ở ví dụ trên, có thể đổi chỗ cho hai trờng Nhân viên và Ngày bằng cách
kéo trờng Nhân viên về bên phải trờng Ngày và thả tại đó.


- 23 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

Bổ sung các trờng :
- Chọn ô bất kỳ của Bảng Tổng hợp
- Chọn mục Data, PivotTable
- Thực hiện lại các bớc nh khi tạo mới bảng Tổng hợp để bổ sung trờng vào
các vị trí mong muốn.
Xoá trờng : Trong Bảng Tổng hợp kéo tên trờng cần xoá ra khỏi bảng.
Sửa tên trờng : Đa con trỏ ô về trờng cần thiết trong Bảng Tổng hợp và tiến
hành sửa tên nh sửa dữ liệu của bảng tính
Xoá trờng : Trong Bảng Tổng hợp kéo tên trờng cần xoá ra khỏi bảng.
Sửa tên trờng : Đa con trỏ ô về trờng cần thiết trong Bảng Tổng hợp và tiến

hành sửa tên nh sửa dữ liệu của bảng tính

- Chọn hàm tơng ứng danh sách Summarize by
- Chọn OK
Tự động điều chỉnh Bảng Tổng hợp khi dữ liệu gốc thay đổi
Sau khi sửa số liệu của CSDL
- Chọn ô bất kỳ của Bảng Tổng hợp
- Chọn mục Data, Refresh Data



- 24 -


Trờng quốc tế cambridgevietanh
ĐT: 04.22.630.666

Chơng v : đồ thị
Đồ thị (graph) là mô hình toán học, đợc sử dụng để diễn tả sự phân bố của các
đại lợng dới dạng hình ảnh.
Trong excel, một công cụ tơng đối mạnh dùng để thể hiện mối quan hệ, hay so
sánh giữa các dữ liệu với nhau đó là đồ thị.
Đồ thị bao gồm một trục đánh dấu các mốc và các cột biểu diễn giá trị tại các
mốc của các đại lợng.
I. Các bớc vẽ mới một đồ thị
Chọn vùng dữ liệu cần vẽ đồ thị. (chọn cả vùng nhãn của dữ liệu)
Kích chuột vào menu Insert, chọn Chart. (Hoặc kích vào biểu tợng Chart trên
thanh công cụ)
Hộp thoại Chart Wizard sẽ xuất hiện.


* Chart Type (kiểu đồ thị):
o Column: dạng cột dọc.
o Bar: dạng thanh ngang.
o Line: dạng đờng.
o Pie: bánh tròn.
o XY: Đờng, trục X là số.
o Area: dạng vùng.
o Doughtnut: băng tròn.
o Radar: Toạ độ cực.
o Surface: dạng bề mặt.
o Bubble: dạng bong bóng.
o Stock: 3 dãy (cao, thấp, khớp).


- 25 -