23/06/2011
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. 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ê, tài chính, logic, xử lý chu ỗi ký t ự, ngày tháng … D ạng thức tổng quát của hàm:
Chương 4
=
Danh sách đối số: có th ể là các tr ị số, dãy các ô, địa chỉ ô, tên vùng, công thức, tên hàm. Ví dụ: =SUM(D7; D13:D14; 10; 2+D16)
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ác hàm số có thể lồng nhau. Ví dụ:
=IF(AND(A2=10; A3>=8);“G”;IF(A2<7;“TB”;“K”))
Chú ý: Hàm ph ải bắt đầu bởi dấu bằng (=), tên hàm không phân bi ệt chữ thường và ch ữ hoa. Đối số phải đặt trong ngo ặc đơn ( ), gi ữa các đối số phân cách nhau bởi dấu chấm phẩy (hoặc dấu phẩy).
HÀM TRONG EXCEL
3. Hàm INT 2.1. HÀM SỐ HỌC 1. Hàm ABS Công dụng: Làm tròn một số xuống số nguyên gần nhất. Công dụng: Lấy trị tuyệt đối của một số. Công thức: Công thức: là số muốn lấy trị tuyệt đối. là số muốn làm tròn.
Công thức
Kết quả
number Ví dụ
=ABS(5-7)
2
number Ví dụ
Công thức =INT(1.5)
2. Hàm SQRT
=INT(-1.5)
Kết quả 1 -2
Công dụng: Tính căn bậc 2 của một số 4. Hàm PI
SQRT viết của từ SQUARE ROOT: Căn bậc 2.
Công thức: Công dụng: Trả về số pi chính xác đến 15 số: 3.141592653589790 số thực bất kỳ. Nếu number là số âm, Công thức:
Công thức
Kết quả
=SQRT(100)
10
number hàm trả về lỗi #NUM!. Ví dụ Sử dụng hàm PI() trong các công thức lượng giác.
1
Bùi Thành Khoa
23/06/2011
6. Hàm ROUND 5. Hàm MOD Công dụng: Làm tròn m ột số đến ph ần ký s ố do Công dụng: Lấy phần dư sau khi chia m ột số cho bạn đưa ra. số chia. Công thức: Công thức: số muốn làm tròn. số ký số muốn làm tròn số bị chia số chia.
Giải thích làm tròn về bên phải cột thập phân
làm tròn đến số nguyên gần nhất
number: num_digits:
num_digits > 0 = 0 < 0
làm tròn về bên trái cột thập phân
number divisor Lưu ý! (cid:216) Nếu divisor bằng zero, hàm trả về lỗi #DIV/0! (cid:216) Hàm Mod có thể biểu diễn qua hàm Int theo công thức: MOD(n; d) = n - d*INT(n/d)
Công thức =ROUND(2.15; 1)
Kết quả 2.2
=ROUND(2.149; 1)
Ví dụ
Công thức =MOD(10;3)
Kết quả 1
2.1
=ROUND(21.5; -1)
Ví dụ
=MOD(8;2)
0
20 (làm tròn đến hàng đơn vị)
7. Hàm SUM 8. Hàm SUMPRODUCT Công dụng: Nhân các ph ần tử tương ứng trong các mảng với nhau và trả về tổng của chúng. Công dụng: Tính tổng các đối số. Công thức: Công thức: argument1, argument2,… có thể là các hằng, địa
array1, array2,.... có thể có từ 2 đến 30 mảng chỉ ô, miền. Ví dụ: cùng kích thước.
Kết quả
Công thức
=SUM(3; 2)
=SUM("5“; 15; TRUE)
5 21 (Giá tr ị text được chuy ển sang số, giá tr ị logical TRUE được chuyển sang số 1)
=SUM(A2:A5)
40 55
=SUM(A2:A4;15)
Lưu ý! Nếu các mảng giá trị không cùng kích thước hàm sẽ trả về lỗi #VALUE! Một phần tử bất kỳ trong mảng không phải là số thì coi là zero.
Ví dụ: sumproduct.xls
2
Bùi Thành Khoa
23/06/2011
9. Hàm AVERAGE 10. Hàm MAX Công dụng: Trả về giá trị lớn nhất của các giá trị kể Công dụng: Tính trung bình cộng của các đối số. Công thức: cả kiểu số, chữ, logic. Công thức:
number1, number2 có thể có từ 1 đến 30 đối số
value1,value2,... có thể có từ 1 đến 30 giá trị muốn
cần tính trung bình cộng. tìm giá trị lớn nhất.
Lưu ý! Các đối số có th ể là s ố, tên, m ảng ho ặc vùng tham chiếu. Các thành phần của vùng tham chi ếu, mảng giá tr ị nếu là kiểu chuỗi thì chuyển thành 0. Nếu đối số là giá tr ị logic TRUE thì được hi ểu là 1, FALSE hiểu là 0.
Kết quả 1 (TRUE chuyển thành 1)
Lưu ý! Nếu các đối số là giá tr ị logic thì TRUE được hiểu là 1, FALSE hiểu là 0. Ví dụ: Công thức =MAX(-2;-5;0;TRUE) Ví dụ: =AVERAGE(5;10;TRUE;FALSE) cho giá trị 4.
11. Hàm MIN Công dụng: Trả về giá trị nhỏ nhất của các giá trị kể
cả kiểu số, chữ, logic. Công thức:
value1,value2,... có thể có từ 1 đến 30 giá trị muốn
tìm giá trị lớn nhất.
Kết quả
0 (FALSE, chuyển thành 0)
Ví dụ: Tính thuế thu nhập cá nhân. thue-thu-nhap.xls Ghi chú: Trong Excel có s ẵn tính n ăng tính toán nhanh như sau: Trước tiên đánh dấu vùng cần tính toán rồi right click vào thanh tr ạng thái của Excel s ẽ hi ện menu có các mục chọn là các giá trị cần tính: None: huỷ giá trị đã tính. Average: tính trị trung bình. Count: đếm số ô. Count Nums: chỉ đếm số ô chứa giá trị số. Max: tìm giá trị lớn nhất. Min: tìm giá trị nhỏ nhất. Sum: tính tổng các giá trị. Tiếp theo ch ỉ cần click một mục tương ứng thì kết quả sẽ được hiển thị ngay trên thanh trạng thái. Lưu ý! Nếu các đối số là giá tr ị logic thì TRUE được hiểu là 1, FALSE hiểu là 0. Ví dụ: Công thức =MIN(21;5;0.5;TRUE;FALSE )
3
Bùi Thành Khoa
23/06/2011
2. Hàm OR 2.2. HÀM ĐIỀU KIỆN 1. Hàm AND
Công dụng: Trả về TRUE nếu một trong các điều kiện là TRUE, trả về FALSE nếu tất cả các điều kiện là FALSE. Công dụng: Trả về kết qu ả TRUE nếu tất cả điều kiện đều TRUE, trả về FALSE nếu một trong các điều kiện là FALSE. Công thức: Công thức:
logical_1, logical_2 là các điều ki ện cần ki ểm logical_1, logical_2 là các điều ki ện cần ki ểm
tra. Ví dụ:
Công thức
Kết quả
Công thức
Kết quả
tra. Ví dụ:
TRUE
=AND(TRUE; FALSE)
FALSE
=OR(TRUE; FALSE; FALSE)
=AND(2+2=4;2+3=5)
TRUE
=OR(2+2=5; 2+3<5)
FALSE
=AND(1
TRUE
=OR(1>A2; A2<100)
TRUE
4. Hàm IF 3. Hàm NOT Công dụng: Tr ả về một giá tr ị nếu điều ki ện là Công dụng: Trả về ph ủ định của một bi ểu th ức đúng, trả về một giá trị khác nếu điều kiện là sai. Logic. Công thức: Công thức:
: điều kiện để xét. logical là một biểu thức điều kiện kiểu logic.
value_if_true: giá tr ị tr ả về nếu logical_test là
value_if_false: giá tr ị tr ả về nếu logical_test là
Công thức
Kết quả
logical_test
TRUE.
FALSE.
=NOT(FALSE)
1 (TRUE)
=NOT(1+1=2)
0 (FALSE)
Ví dụ:
Lưu ý!
Có thể có 7 hàm IF được lồng vào nhau để tạo nên
công thức phức tạp hơn. Các điều kiện phải có giá trị
là TRUE hoặc FALSE.
4
Bùi Thành Khoa
23/06/2011
• IF(Logical_test,Value_if_true,Value_if_false)
• Ví dụ:
• IF(A1>2,”A”,”B”)
• Nếu A1 lớn hơn 2, giá trị trả về là A
• Nếu A1 nhỏ hơn hay bằng 2, giá trị trả về là B
5. Hàm SUMIF
Công dụng: hàm trả về giá trị tổng của những
phần tử được chọn trong vùng Sum_Range,
những phần tử này được chọn tương ứng với
những dòng của vùng Range có giá trị thoả
mãn điều kiện của Criteria
• Thành tiền=Số lượng x Đơn giá
Công thức:
range là dãy ô muốn tính toán. Nó có thể là dãy ô chứa điều
kiện ho ặc dãy ô v ừa ch ứa điều kiện, vừa ch ứa các giá tr ị để
tính tổng.
criteria là điều ki ện để tính t ổng có th ể là số, bi ểu th ức,
hoặc kiểu văn bản ví dụ: 5,“<=25","CPU"
sum_range là dãy giá tr
ị cần tính t ổng. Nếu không có
• Nếu MÃ HÀNG bắt đầu bằng B và SỐ LƯỢNG lớn hơn 20
thì giảm THÀNH TIỀN 10%
• =IF(AND(LEFT(A2,1)="B",B2>20),B2*C2*90%,B2*C2)
sum_range thì range là dãy chứa giá trị để tính tổng.
(cid:151) Ví dụ SUMIF
6. Hàm COUNTIF
Công dụng: COUNTIF(Range, Criteria): hàm
trả về giá trị đếm những phần tử của vùng
Range thoả mãn điều kiện của Criteria
Công thức:
range là dãy ô muốn đếm.
criteria là điều ki ện: số, bi ểu thức logic, hay ki ểu
chữ. Ví dụ: 10, ">=10",“5"
Ví dụ:
Giá trị vùng A1:A4 bằng 100, 200, 300, 400
=COUNTIF(A1:A4,“>160”) sẽ cho giá trị 3
◦ Các giá trị vùng A1:A4 bằng 100, 200, 300, 400
◦ Các giá trị vùng B1:B4 bằng 7, 4, 21, 28
◦ =SUMIF(A1:A4,">160",B1:B4) có kết quả là 63
5
Bùi Thành Khoa
23/06/2011
2. Hàm COUNTA và COUNTIF
a) COUNTA
2.3. HÀM THỐNG KÊ
1. Hàm COUNT
Công dụng: Đếm số ô có dữ liệu (không ph ải là ô rỗng) trong vùng tham
chiếu.
Công dụng: Đếm số ô d ữ li ệu ki ểu số trong vùng tham chiếu.
Công thức:
Công thức:
,value2,... có thể có từ 1 đến 30 vùng giá tr ị mà bạn muốn
value1,value2,... có thể có từ 1 đến 30 vùng giá
value1
đếm số ô chứa dữ liệu kiểu số.
Lưu ý:
Để đếm số ô ch ứa dữ liệu kiểu số dùng hàm COUNT, để đếm ô rỗng dùng
hàm COUNTBLANK.
Ví dụ:
=COUNTA(-2;"VTD“;5;8) {kết quả 4}
B) COUNTIF
Công thức
COUNTIF (X1,X2,…, điều_kiện) hay COUNTIF(mi ền, điều_kiện)
=COUNT(A2:A7)
Đếm số lượng giá trị thoả mãn điều kiện
Kết quả
3
2
=COUNT(A5:A7)
=COUNT(A2:A7,2)
4
COUNTIF(C3:C11,">=5"): Số ô có giá trị ≥5 trong C3:C11
COUNTIF(C3..C11,”5”): Số ô có giá trị = 5 trong C3..C11
trị mà bạn muốn đếm số ô chứa dữ liệu kiểu số.
Lưu ý: Các tham số có th ể là s ố, ngày tháng, ho ặc
chuỗi đại diện cho số đều được tính. Giá trị lỗi, hoặc
chuỗi không thể chuyển đổi sang số được bỏ qua.
Ví dụ:
3. Hàm RANK 2.4. HÀM CHUỖI
1. Hàm LEFT Công dụng: Tìm vị thứ của một số trong dãy số.
Công thức: Công dụng: Trích bên trái m ột chu ỗi một ho ặc nhiều ký tự dựa vào số ký tự được chỉ định. Công thức:
number giá trị mà bạn cần tìm vị thứ.
ref là mảng hoặc vùng tham chi ếu đến một danh sách
giá trị kiểu số. Những giá trị không phải là số được bỏ qua.
là chuỗi cần trích ký tự
là ký t ự mà b ạn cần trích bên trái
order: phương thức sắp xếp.
– order = 0, hoặc bỏ qua thì số lớn nhất có vị trí nhỏ nhất 1
– order = 1 thì số nhỏ nhất có vị trí nhỏ nhất 1.
text
num_chars
chuỗi text.
Lưu ý:
Lưu ý! Nếu 2 số cùng vị thứ thì vị thứ tiếp theo được
bỏ qua. Ví dụ: Có 2 giá trị ở vị thứ số 1 thì sẽ không có
vị thứ số 2 mà chỉ có vị thứ số 3.
num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về
toàn bộ chuỗi text.
num_chars nếu bỏ qua thì mặc định là 1.
Ví dụ: ham-rank.xls
Ví dụ 1:
=LEFT("ABCD“; 2) {k
ết quả : "AB"}
6
Bùi Thành Khoa
23/06/2011
Ví dụ 2:
Ví dụ 2:
“Trà”, G fi
“Café, T fi
“Gạo”
=IF(LEFT(B3;1)=“C”;”Cafe”;IF(LEFT(B3;1)=“T”;”Trà”;”Gạo”))
Dựa vào cột Mã hàng điền vào c ột Tên hàng theo ch ữ đầu
của Mã hàng: C fi
Công thức ô C3:
là chuỗi cần trích ký tự
2. Hàm RIGHT 3. Hàm MID Công dụng: Trích bên ph ải một chu ỗi một ho ặc nhiều ký tự dựa vào số ký tự được chỉ định. Công thức: Công dụng: Trích một chuỗi con từ một chuỗi text,
bắt đầu từ vị trí start_num với số ký tự được chỉ định
num_chars. Công thức:
là ký t ự mà b ạn cần trích bên trái
: vị trí bắt đầu trích lọc chuỗi con trong text.
text: chuỗi hoặc tham chiếu đến chuỗi.
text
num_chars
chuỗi text.
Lưu ý:
start_num
num_chars: số ký tự của chuỗi mới cần trích từ chuỗi text.
Lưu ý:
start_num > chiều dài chuỗi text thì hàm trả về chuỗi rỗng.
start_num < 1 hàm trả về lỗi #VALUE!
num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về
toàn bộ chuỗi text.
num_chars nếu bỏ qua thì mặc định là 1.
ết quả : “thu”}
ết quả : "CD"}
Ví dụ:
=RIGHT("ABCD",2) {k
Ví dụ:
=MID(”mùa thu Hà Nội”; 5; 3) {k
7
Bùi Thành Khoa
23/06/2011
6. Hàm UPPER 4. Hàm LEN Công dụng: Chuyển tất cả các ký t ự trong chu ỗi thành ký tự hoa. Công dụng: Tính độ dài (số ký tự) của một chuỗi.
Công thức: Công thức:
text: chuỗi văn bản cần chuyển định dạng.
text: nội dung mà bạn cần xác định độ dài
Ví dụ: =LEN("ABCD") {k ết quả là 4}
Ví dụ:
=UPPER("Trung tam") {kết quả: "TRUNG TAM"}
5. Hàm LOWER 7. Hàm PROPER Công dụng: Đổi tất cả các ký t ự trong chu ỗi sang Công dụng: Chuyển ký t ự đầu tiên c ủa mỗi từ chữ thường. thành chữ hoa. Công thức: Công thức:
text
: chuỗi văn bản cần chuyển định dạng.
text
: chuỗi văn bản cần chuyển định dạng.
Ví dụ:
=PROPER("trung tam”) {k ết quả : “Trung Tam”} Ví dụ: =LOWER("TRUNG Tam") {k ết qu ả : “trung
tam”}
8. Hàm TRIM
Ví dụ:
ết quả : }
8
5
5
=FIND("e","MS. Excel 6.0") {k
=FIND("E","MS. Excel 6.0") {kết quả : }
=FIND("Excel","MS. Excel 6.0") {k ết quả : }
Giả sử giá trị trong ô A1 là chuỗi “Lê Văn Hùng”
=LEFT(A1;Find(" ";A1)-1) {k
ết quả: }
“Lê”
Công dụng: Xóa tất cả các ký t ự tr ắng của chu ỗi
trừ những khoảng đơn dùng để làm kho ảng cách bên
trong chuỗi. Công thức:
10. Hàm SEARCH
text: chuỗi cần xóa các ký tự trắng.
Ví dụ: =TRIM(" Thủ đô Hà Nội ")
{kết quả: "Thủ đô Hà Nội"}
9. Hàm FIND Tương tự hàm FIND nhưng không phân biệt chữ HOA
và thường.
Công dụng: cho vị trí c ủa chu ỗi con find_text
trong chuỗi text bắt đầu tìm từ vị trí start_num, nếu
bỏ qua start_num nó cho giá trị bằng 1. Hàm này phân
biệt chữ HOA và thường. Công thức:
8
Bùi Thành Khoa
23/06/2011
11. Hàm SUBSTITUTE 2.5. HÀM TÌM KiẾM
1. Hàm VLOOKUP
Công dụng: Thay th ế một chu ỗi cụ th ể bên trong
chuỗi bằng chuỗi khác. Dùng SUBSTITUTE khi mu ốn
thay thế một chuỗi cụ thể. Công thức:
Công dụng: Dò tìm một giá tr ị ở cột đầu tiên bên trái
của 1 bảng dữ liệu. Nếu tìm th ấy sẽ trả về giá tr ị ở cùng
trên dòng với giá trị tìm thấy trên cột mà bạn chỉ định.
Công thức: text: chuỗi văn bản cần thay thế nội dung.
lookup_value: giá trị dùng để tìm kiếm, nó có th ể là một giá
trị, một tham chiếu hay một chuỗi ký tự.
table_array: vùng ch ứa dữ liệu cần tìm. Đây là bảng dữ liệu
phụ có nội dung thường cố định để lấy dữ liệu.
Các giá trị ở cột đầu tiên có th ể là giá trị số, chuỗi ký tự,
hoặc logic.
Nếu range_lookup là TRUE (hay 1) thì giá trị ở cột đầu
tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần.
(cid:151) Ví dụ 1
old_text:
nội dung bên trong chu ỗi text cần thay th ế.
new_text: chuỗi văn bản mới để thay chuỗi cũ instance_num:
chỉ định thay thế ở lần mà tìm th ấy chuỗi old_text trong chuỗi
text. Nếu bỏ qua thì s ẽ thay th ế tất cả các old_text được tìm
thấy trong chuỗi text.
Ví dụ 1:
=SUBSTITUTE("CD001";"CD";"CDRW";1) {k ết quả: CDRW001}
=SUBSTITUTE("DVD002";"0";"A") {k ết quả: DVDAA2}
Ví dụ 2: Tách riêng tên và họ lót. tach Ho va ten.xls
B
C
D
E
F
M
2
3
5
N
5
7
2
O
7
1
7
row_index_num: số thứ tự cột trên bảng dữ liệu phụ mà dữ
liệu bạn cần lấy. Giá trị trả về nằm trên cột bạn chỉ định này
và ở dòng mà hàm tìm thấy giá trị dò tìm lookup_value.
range_lookup: giá trị logic được chỉ định muốn VLOOKUP
tìm kiếm chính xác hay là t ương đối. Nếu range_lookup là
TRUE (hay 1) hàm sẽ trả về kết quả tìm kiếm tương đối; khi
đó nếu lookup_value lớn hơn ph ần tử cuối cùng trong danh
sách, xem nh ư tìm th ấy ở ph ần tử cu ối cùng. N ếu
range_lookup là FALSE (hay 0) hàm tìm ki ếm chính xác,
nếu không có trả về lỗi #N/A! (Not Available: bất khả thi).
P
2
9
9
Lưu ý:
10
11
12
13
14
Nếu lookup_value nhỏ hơn giá tr ị nhỏ nhất trong cột
đầu tiên c ủa bảng dữ li ệu ph ụ, VLOOKUP tr ả về lỗi
#N/A!.
VLOOKUP xuất phát t ừ vertical lookup : dò tìm theo
◦ =VLOOKUP(“N”,$B$10:$D$13,2,FALSE)
◦ =VLOOKUP(“P”,$B$10:$D$13,2,FALSE)
◦ =VLOOKUP(“N”,$B$10:$D$13,3,FALSE)
◦ =VLOOKUP(“P”,$B$10:$D$13,3,FALSE)
phương đứng, hay theo cột.
Ví dụ:
ham-vlookup.xls
9
Bùi Thành Khoa
23/06/2011
(cid:151) Ví dụ 2
2. Hàm HLOOKUP
=VLOOKUP(A2,$B$12:$C$14,2,FALSE)
A
C
D
B
TÊN DV
HỌ TÊN
DV
1
Ban GĐ
Tạ Minh Hải
A
2
Phạm Thái
B
Mọi nguyên t ắc ho ạt động của hàm HLOOKUP
(Horizontal Look Up ) gi ống nh ư hàm VLOOKUP
(Vertical Look Up), chỉ khác là hàm VLOOKUP dò
tìm ở cột bên trái, tham chi ếu số li ệu ở các cột bên
phải, còn hàm HLOOKUP dò tìm ở hàng trên cùng,
tham chiếu số liệu ở các hàng phía dưới.
3
Ng. Biểu
C
4
ham-Hlookup.xls
Ng. Thanh
C
Ví dụ:
5
Lê Sơn
A
6
Kim Liên
B
7
DANH MỤC ĐƠN VỊ
8
DV
TÊN ĐƠN VỊ
9
A
Ban GĐ
10
B
P. Tổ chức
11
C
P. Tài vụ
12
2.6. HÀM NGÀY THÁNG
=HLOOKUP(A2,$B$11:$D$12,2,FALSE)
(cid:151) Ví dụ
A
B
C
D
DV
TÊN DV
HỌ TÊN
1
Ban GĐ
Tạ Minh Hải
A
2
Phạm Thái
B
3
Ng, Biểu
C
4
Ng. Thanh
C
5
Lê Sơn
B
6
Kim Liên
7
Excel hỗ trợ tính toán ngày tháng cho Windows và
Macintosh. Windows dùng hệ ngày b ắt đầu từ 1900.
Macitosh dùng hệ ngày bắt đầu từ 1904. Tài liệu này
được diễn giải theo hệ ngày 1900 dành cho Windows.
Hệ th ống ngày gi ờ Excel ph ụ thu ộc vào thi ết lập
trong Regional Options của Control Panel . Mặc
định là h ệ th ống của Mỹ "Tháng/Ngày/Năm"
(M/d/yyyy). Bạn có th ể sửa lại thành hệ th ống ngày
của VN "Ngày/Tháng/Năm" (dd/MM/yyyy).
8
9
DANH MỤC ĐƠN VỊ
10
DV
11
C
B
A
TÊN ĐV
Ban GĐ
P. Tổ chức
P. Tài vụ
12
13
14
Khi bạn nhập một giá tr ị ngày tháng không h ợp lệ
nó sẽ tr ở thành một chu ỗi văn bản. Công th ức tham
chiếu tới giá trị đó sẽ trả về lỗi.
10
Bùi Thành Khoa
23/06/2011
1. Hàm WEEKDAY 2. Hàm TODAY Công dụng: Trả về th ứ tự của ngày trong tu ần của Công dụng: Trả về ngày hiện tại trong hệ thống của định dạng ngày tháng chỉ ra. máy tính. Công thức: Công thức:
3. Hàm DAY
serial_number: chuỗi số đại diện ngày tháng cần tìm.
return_type: Xác định kiểu giá trị trả về. Cụ thể:
return_type = 1 (hoặc không nhập): hàm WEEKDAY trả về
1 là Sunday (Chủ nhật), 7 là Saturday (Thứ 7).
return_type = 2: WEEKDAY trả về 1 là Monday (Thứ 2), 7
là Sunday (Chủ nhật).
Công dụng: Trả về th ứ tự của ngày từ chu ỗi ngày tháng. Công thức:
dạng chuỗi số kiểu ngày.
ết quả: 3}
serial_number:
Ví dụ:
=DAY(“15/10/2008") {K
ết quả: 15}
Ví dụ:
=WEEKDAY("02/09/2008") {K
Vậy ngày lễ Quốc khánh năm 2008 rơi vào ngày th ứ Ba trong
tuần.
ết quả: 10}
4. Hàm MONTH 6. Hàm DAY360 Công dụng: Trả về thứ tự của tháng từ giá tr ị kiểu ngày tháng. Công thức: Công dụng: Trả về số ngày giữa 2 giá trị ngày tháng
dựa trên cơ sở một năm có 360 ngày (12 tháng, m ỗi
tháng 30 ngày). Công thức: dạng chuỗi số kiểu ngày. serial_number:
Ví dụ: =MONTH(“15/10/2008") {K
5. Hàm YEAR Công dụng: Trả về năm của một giá tr ị hoặc chuỗi
start_date
, end_date: hai mốc bắt đầu và kết thúc
để tính s ố ngày. Nếu start_date lớn hơn end_date hàm s ẽ
trả về số âm.
đại diện cho ngày tháng. Ví dụ 1: Công thức:
serial_number: dạng chuỗi số kiểu ngày.
ết quả: 2008}
Ví dụ 1: =YEAR(“15/10/2008") {K
ết quả: 331}
Ví dụ 2:
=DAYS360(A3;B3) {K
Ví dụ 2: Ham-ngaythang.xls
11
Bùi Thành Khoa
23/06/2011
CÁC KHÁI NIỆM VỀ VÙNG
DATABASE, CRITERIA VÀ EXTRACT
¢ Trên một cơ sở dữ liệu dạng bảng của
Excel ta có thể:
(cid:151) Lọc (Filter)
(cid:151) Xoá (Delete)
(cid:151) Rút trích (Extract)
¢ Theo một tiêu chuẩn nào đó
¢ Để thực hiện được các thao tác này ta
phải tạo các vùng sau:
CHƯƠNG 5
CƠ SỞ DỮ LIỆU TRÊN
BẢNG TÍNH
1. CÁC KHÁI NIỆM
2. THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH
3. CÁC DẠNG VÙNG TIÊU CHUẨN
4. CÁC HÀM DATABASE
(cid:151) Dòng đầu chứa tiêu đề cột (FieldName)
CÁC KHÁI NIỆM VỀ VÙNG DATABASE,
CRITERIA VÀ EXTRACT CÁC KHÁI NIỆM VỀ VÙNG DATABASE,
CRITERIA VÀ EXTRACT
¢ Vùng Database: là vùng CSDL g ồm ít nhất hai dòng Vùng
Criteria Vùng Database
B
C
D
E
F
G
H
(cid:151) Các dòng còn lại chứa dữ liệu gọi là mẫu tin (Record)
¢ Ví dụ
A
TT
HO
TEN
CV
SO CON
LUONG
1
¢ Vùng Criteria: là vùng tiêu chu ẩn chứa điều kiện để tìm kiếm, xoá
Tran Van
Nam
TP
3
2400000
CV
2
1
hay rút trích, vùng này có ít nh ất hai dòng
Le Quy
Hien
NV1
2
1300000
TP
3
2
(cid:151) Dòng đầu chứa tiêu đề cột
Pham Thi
Bich
TP
1500000
4
3
(cid:151) Các dòng còn lại chứa điều kiện
Do Hoang
Nam
NV2
1
2500000
5
4
Nguyen Thanh
Hai
NV1
3
1500000
6
5
¢ Vùng Extract: là vùng trích dữ liệu chứa các mẫu tin của vùng
7
Database thoả điều kiện vùng Criteria
8
(cid:151) Vùng Extract được thức hiện với thao tác rút trích
9
TT
TEN
(cid:151) Thao tác Xoá, Lọc không dùng đến vùng này
10
1
Nam
¢ Field: là tiêu đề của cột cần tính toán
11
2
Bich
(cid:151) Tính Tổng lương -> Field là ô chứa từ Lương trong Database
Vùng Extract
12
Bùi Thành Khoa
23/06/2011
CÁC KHÁI NIỆM VỀ VÙNG DATABASE,
CRITERIA VÀ EXTRACT
CÁC KHÁI NIỆM VỀ VÙNG DATABASE,
CRITERIA VÀ EXTRACT
CÁC DẠNG VÙNG TIÊU CHUẨN
¢ Tiêu chuẩn số
CÁC DẠNG VÙNG TIÊU CHUẨN
¢ Tiêu chuẩn so sánh
(cid:151) Ô điều kiện chứa toán tử so sánh kèm với giá trị so
SO CON
2
¢ Tiêu chuẩn chuỗi
(cid:151) Trong ô điều kiện có thể chứa các ký tự đại diện
SO CON
¢ * Đại diện cho một nhóm ký tự bất kỳ
¢ ? Đại diện cho một ký tự bất kỳ
sánh. Các toán tử gồm
¢ > lớn hơn
¢ < nhỏ hơn
¢ >= lớn hơn hoặc bằng
¢ <= nhỏ hơn hoặc bằng
¢ = bằng
¢ <> khác
>2
TEN TEN NAM H*
CÁC KHÁI NIỆM VỀ VÙNG DATABASE,
CRITERIA VÀ EXTRACT
CÁC KHÁI NIỆM VỀ VÙNG DATABASE,
CRITERIA VÀ EXTRACT
CÁC DẠNG VÙNG TIÊU CHUẨN
¢ Tiêu chuẩn công thức
CÁC DẠNG VÙNG TIÊU CHUẨN
¢ Liên kết tiêu chuẩn
(cid:151) Ô điều kiện có kiểu công th ức. Trong trường hợp sử
dụng tiêu chuẩn này cần lưu ý
¢ Ô tiêu đề vùng tiêu chuẩn phải khác tất cả các tiêu đề
vùng Database
(cid:151) Có thể lọc, xoá hay rút trích các mẫu tin trong Database
bằng cách giao (AND) hay hội (OR) của nhiều điều kiện
khác nhau
¢ Nếu các ô điều kiện có tính chất giao (và)
¢ Nếu các ô điều kiện có tính chất hội (hoặc)
¢ Trong ô điều kiện phải lấy địa chỉ của ô trong mẫu tin
đầu tiên (dòng thứ hai trong Database) để so sánh
(cid:151) Ví dụ: tiêu chuẩn ký tự đầu của TEN khác “H”
SO CON CHUC VU SO CON=2 và
CHUC VU=NV1
>2 2 NV1
TP SO CON>2 và
CHUC VU=TP Dòng
tiêu đề XYZ
FALSE
Công thức:
=LEFT(C2,1)<>”H”
13
Bùi Thành Khoa
23/06/2011
CÁC KHÁI NIỆM VỀ VÙNG DATABASE,
CRITERIA VÀ EXTRACT
ỨNG DỤNG 1:
THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH (1)
CÁC DẠNG VÙNG TIÊU CHUẨN
¢ Tiêu chuẩn trống
¢ Lọc (Filter)
(cid:151) Di chuyển con trỏ vào một ô bất kỳ của
(cid:151) Nếu trong ô điều kiện không có dữ liệu, tiêu chuẩn là tuỳ ý
(cid:151) Ví dụ
vùng Database
(cid:151) Chọn menu Data/Filter/Advance Filter
¢ Action: Filter the list, in-place
¢ List range: chọn địa chỉ vùng Database
¢ Criteria: chọn địa chỉ vùng Criteria
¢ Unique record only: lọc bỏ những dòng
trùng nhau chỉ hiển thị 1 dòng
(cid:151) Thể hiện điều kiện: =OR(SOCON=2, CHUCVU=TP)
SO CON CHUC VU
2 TP
SO CON=2 hoặc CHUC VU=TP
¢ Muốn bỏ việc lọc các mẩu tin
(cid:151) Chọn menu Data/Filter/Show All
THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH (2)
THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH (3)
¢ Rút trích
¢ Xoá
(cid:151) Di chuyển con trỏ vào một ô bất kỳ
(cid:151) Di chuyển con trỏ vào một ô bất kỳ của vùng
của vùng Database
Database
(cid:151) Chọn menu Data/Filter/Advance
(cid:151) Chọn menu Data/Filter/Advance Filter
Filter
(cid:151) Sau khi các dòng thoả mãn điều kiện được trình
bày
¢ Action: Copy to another location
¢ List range: chọn địa chỉ vùng Database
(cid:151) Chọn các dòng nay và chọn menu Edit/Delete
¢ Criteria: chọn địa chỉ vùng Criteria
Row
¢ Copy to: nhập địa chỉ dòng đầu vùng
Extract (Số ô = số ô vùng List range )
(cid:151) Chọn menu Data/Filter/Show All để hiển thị lại
¢ Unique record only
các dữ liệu bị dấu đi trong thao tác lọc
14
Bùi Thành Khoa
23/06/2011
ỨNG DỤNG 2.
CÁC HÀM DATABASE
CÁC HÀM DATABASE
¢ DSUM(, , )
¢ DMAX(, , )
(cid:151) Tính giá trị lớn nhất trên cột Field
của vùng Database thoả mãn điều kiện
vùng Criteria
(cid:151) Hàm tính tổng trên cột Field của vùng
Database thoả mãn điều kiện của vùng
Criteria
(cid:151) Ví dụ: Tính tổng lương của những người có
số con bằng 2
DSUM(A1:E7,E1,G2:G3)
B
C
D
E
F
G
A
¢ DMIN(, , )
(cid:151) Tính giá trị nhỏ nhất trên cột Field
của vùng Database thoả mãn điều kiện
vùng Criteria
TT
TEN
1
SO CON
TUOI
LUONG
1
Nam
2
2
26
5200
15260 SO CON
¢ DCOUNT(, ,
2
Bay
3
1
25
2500
2
3
Hong
4
3
26
6800
)
(cid:151) Đếm số phần tử kiểu số trên cột
4
Sau
5
2
27
4860
5
Chin
6
1
30
6300
Field của vùng Database thoả mãn
điều kiện vùng Criteria
6
Khoi
7
2
26
5200
8
CÁC HÀM DATABASE
¢ DCOUNTA(, ,
)
(cid:151) Đếm số phần tử khác rỗng trên cột Field
của vùng Database thoả mãn điều kiện vùng
Criteria
¢ DAVERAGE(, ,
)
(cid:151) Tính giá trị trung bình trên cột Field
HỎI VÀ ĐÁP
của vùng Database thoả mãn điều kiện vùng
Criteria
15
Bùi Thành Khoa
23/06/2011
CÁC LOẠI BIỂU ĐỒ
¢ Trong Excel có thể dựa vào dữ
¢ Có nhiều loại biểu đồ:
(cid:151) Column: biểu đồ dạng cột
(cid:151) Bar: biểu đồ dạng khối
(cid:151) Line: biểu đồ dạng đường kẻ
(cid:151) Pie: biểu đồ dạng hình tròn
(cid:151) XY: biểu đồ dạng hàm số
(cid:151) Area: biểu đồ dạng miền
(cid:151) …
¢ Ứng với mỗi loại biểu đồ, có thể
liệu trên Worksheet đề tạo các
biểu đồ
CHƯƠNG 6
VẼ BIỂU ĐỒ
1. CÁC LOẠI BIỂU ĐỒ
2. CÁC THÀNH PHẦN CƠ BẢN TRONG
BIỂU ĐỒ
3. VẼ BIỂU ĐỒ
CÁC THÀNH PHẦN CƠ BẢN TRONG
BIỂU ĐỒ
VẼ BIỂU ĐỒ
chọn nhiều dạng khác nhau
¢ Chọn vùng dữ liệu cần vẽ biểu đồ trên
Worksheet
¢ Chọn meu Insert/Chart hoặc chọn biểu
tượng trên thanh công cụ
Tiêu đề
Tiêu đề trục Y
Tiêu đề trục X Chú thích
16
Bùi Thành Khoa
23/06/2011
VẼ BIỂU ĐỒ
VẼ BIỂU ĐỒ
¢ Tab Titles
(cid:151) Chart title: tiêu
đề của biểu đồ
(cid:151) Category (X) axis:
tiêu đề trục X
(cid:151) Category (Y) axis:
tiêu đề cột Y
¢ Tab Legend
(cid:151) Show legend:
ẩn/hiện chú thích
(cid:151) Placement: Vị trí
chú thích
VẼ BIỂU ĐỒ
¢ As new sheet: tạo biểu đồ trên một
sheet riêng
¢ As object in: đưa biểu đồ vào
worksheet hiện tại
HẾT CHƯƠNG 6
17
Bùi Thành Khoa
23/06/2011
CÁC THÀNH PHẦN TRONG MỘT TRANG IN
Top Margin
HEADER
Right Margin
Left Margin
CHƯƠNG 7
IN ẤN
1. CÁC THÀNH PHẦN TRONG MỘT TRANG IN
2. IN BẢNG BIỂU
FOOTER
Bottom Margin
CÁC THÀNH PHẦN TRONG MỘT TRANG IN
IN BẢNG BIỂU
¢ Tab Page: định cấu
¢ Header: tiêu đề đầu mỗi trang
¢ Footer: tiêu đề cuối mỗi trang
¢ Margin
hình chung
(cid:151) Portrait: định dang
in dọc theo giấy
(cid:151) Left: khoảng cách từ lề trái đến nội
(cid:151) Landscape: định
dung trang in
(cid:151) Right: khoảng cách từ lề phải đến
nội dung trang in
dang in ngang theo
giấy
(cid:151) Top: khoảng cách từ lề trên đến nội
dung trang in
(cid:151) Bottom: khoảng cách từ lề dưới đến
nội dung trang in
Chọn menu File/Page Setup:
18
Bùi Thành Khoa
23/06/2011
IN BẢNG BIỂU
IN BẢNG BIỂU
¢ Tab Margins: định
¢ Tab Header/Footer:
định dạng tiêu đề đầu
trang và cuối trang
(cid:151) Trong Header và
Footer chứa một số
tiêu đề chuẩn.
(cid:151) Chọn None trong
Header và Footer:
không tạo tiêu đề
trang
lề trang in
(cid:151) Top
(cid:151) Bottom
(cid:151) Left
(cid:151) Right
(cid:151) Header: định khoảng
cách từ biên giấy
đến nội dung tiêu
đề đầu trang
(cid:151) Custom Header…: tạo
Header theo ý muốn
(cid:151) Custom Footer…: tạo
Footer theo ý muốn
(cid:151) Footer: định khoảng
cách từ biên giấy
đến nội dung tiêu
đề cuối trang
IN BẢNG BIỂU
IN BẢNG BIỂU
¢ Định tiêu đề bằng cách nhập trực tiếp
¢ Tab Sheet: định
vào Left section, Center section,
Right section.
¢ Sử dụng các icon đề định dạng tiêu đề
(cid:151) Print title: xác
định dòng hay
cột lặp lại trên
mỗi trang
vùng muốn in
(cid:151) Print area: xác
định địa chỉ
vùng in
19
Bùi Thành Khoa
23/06/2011
HẾT CHƯƠNG 7
20
Bùi Thành Khoa
TRUE
=OR(1>A2; A2<100)
TRUE
4. Hàm IF 3. Hàm NOT Công dụng: Tr ả về một giá tr ị nếu điều ki ện là Công dụng: Trả về ph ủ định của một bi ểu th ức đúng, trả về một giá trị khác nếu điều kiện là sai. Logic. Công thức: Công thức:
: điều kiện để xét. logical là một biểu thức điều kiện kiểu logic.
value_if_true: giá tr ị tr ả về nếu logical_test là
value_if_false: giá tr ị tr ả về nếu logical_test là
Công thức
Kết quả
logical_test TRUE. FALSE.
=NOT(FALSE)
1 (TRUE)
=NOT(1+1=2)
0 (FALSE)
Ví dụ:
Lưu ý! Có thể có 7 hàm IF được lồng vào nhau để tạo nên công thức phức tạp hơn. Các điều kiện phải có giá trị là TRUE hoặc FALSE.
4
Bùi Thành Khoa
23/06/2011
• IF(Logical_test,Value_if_true,Value_if_false)
• Ví dụ:
• IF(A1>2,”A”,”B”)
• Nếu A1 lớn hơn 2, giá trị trả về là A • Nếu A1 nhỏ hơn hay bằng 2, giá trị trả về là B
5. Hàm SUMIF Công dụng: hàm trả về giá trị tổng của những phần tử được chọn trong vùng Sum_Range, những phần tử này được chọn tương ứng với những dòng của vùng Range có giá trị thoả mãn điều kiện của Criteria
• Thành tiền=Số lượng x Đơn giá
Công thức:
range là dãy ô muốn tính toán. Nó có thể là dãy ô chứa điều kiện ho ặc dãy ô v ừa ch ứa điều kiện, vừa ch ứa các giá tr ị để tính tổng.
criteria là điều ki ện để tính t ổng có th ể là số, bi ểu th ức,
hoặc kiểu văn bản ví dụ: 5,“<=25","CPU"
sum_range là dãy giá tr
ị cần tính t ổng. Nếu không có
• Nếu MÃ HÀNG bắt đầu bằng B và SỐ LƯỢNG lớn hơn 20
thì giảm THÀNH TIỀN 10%
• =IF(AND(LEFT(A2,1)="B",B2>20),B2*C2*90%,B2*C2)
sum_range thì range là dãy chứa giá trị để tính tổng.
(cid:151) Ví dụ SUMIF
6. Hàm COUNTIF Công dụng: COUNTIF(Range, Criteria): hàm trả về giá trị đếm những phần tử của vùng Range thoả mãn điều kiện của Criteria Công thức:
range là dãy ô muốn đếm. criteria là điều ki ện: số, bi ểu thức logic, hay ki ểu
chữ. Ví dụ: 10, ">=10",“5" Ví dụ:
Giá trị vùng A1:A4 bằng 100, 200, 300, 400 =COUNTIF(A1:A4,“>160”) sẽ cho giá trị 3
◦ Các giá trị vùng A1:A4 bằng 100, 200, 300, 400 ◦ Các giá trị vùng B1:B4 bằng 7, 4, 21, 28 ◦ =SUMIF(A1:A4,">160",B1:B4) có kết quả là 63
5
Bùi Thành Khoa
23/06/2011
2. Hàm COUNTA và COUNTIF
a) COUNTA
2.3. HÀM THỐNG KÊ 1. Hàm COUNT
Công dụng: Đếm số ô có dữ liệu (không ph ải là ô rỗng) trong vùng tham
chiếu.
Công dụng: Đếm số ô d ữ li ệu ki ểu số trong vùng tham chiếu.
Công thức:
Công thức:
,value2,... có thể có từ 1 đến 30 vùng giá tr ị mà bạn muốn
value1,value2,... có thể có từ 1 đến 30 vùng giá
value1 đếm số ô chứa dữ liệu kiểu số. Lưu ý: Để đếm số ô ch ứa dữ liệu kiểu số dùng hàm COUNT, để đếm ô rỗng dùng hàm COUNTBLANK.
Ví dụ:
=COUNTA(-2;"VTD“;5;8) {kết quả 4}
B) COUNTIF
Công thức
COUNTIF (X1,X2,…, điều_kiện) hay COUNTIF(mi ền, điều_kiện)
=COUNT(A2:A7)
Đếm số lượng giá trị thoả mãn điều kiện
Kết quả 3 2
=COUNT(A5:A7)
=COUNT(A2:A7,2)
4
COUNTIF(C3:C11,">=5"): Số ô có giá trị ≥5 trong C3:C11 COUNTIF(C3..C11,”5”): Số ô có giá trị = 5 trong C3..C11
trị mà bạn muốn đếm số ô chứa dữ liệu kiểu số. Lưu ý: Các tham số có th ể là s ố, ngày tháng, ho ặc chuỗi đại diện cho số đều được tính. Giá trị lỗi, hoặc chuỗi không thể chuyển đổi sang số được bỏ qua. Ví dụ:
3. Hàm RANK 2.4. HÀM CHUỖI 1. Hàm LEFT Công dụng: Tìm vị thứ của một số trong dãy số. Công thức: Công dụng: Trích bên trái m ột chu ỗi một ho ặc nhiều ký tự dựa vào số ký tự được chỉ định. Công thức:
number giá trị mà bạn cần tìm vị thứ. ref là mảng hoặc vùng tham chi ếu đến một danh sách giá trị kiểu số. Những giá trị không phải là số được bỏ qua.
là chuỗi cần trích ký tự
là ký t ự mà b ạn cần trích bên trái
order: phương thức sắp xếp. – order = 0, hoặc bỏ qua thì số lớn nhất có vị trí nhỏ nhất 1 – order = 1 thì số nhỏ nhất có vị trí nhỏ nhất 1.
text num_chars chuỗi text. Lưu ý:
Lưu ý! Nếu 2 số cùng vị thứ thì vị thứ tiếp theo được bỏ qua. Ví dụ: Có 2 giá trị ở vị thứ số 1 thì sẽ không có vị thứ số 2 mà chỉ có vị thứ số 3.
num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. num_chars nếu bỏ qua thì mặc định là 1.
Ví dụ: ham-rank.xls
Ví dụ 1: =LEFT("ABCD“; 2) {k
ết quả : "AB"}
6
Bùi Thành Khoa
23/06/2011
Ví dụ 2:
Ví dụ 2:
“Trà”, G fi
“Café, T fi
“Gạo”
=IF(LEFT(B3;1)=“C”;”Cafe”;IF(LEFT(B3;1)=“T”;”Trà”;”Gạo”))
Dựa vào cột Mã hàng điền vào c ột Tên hàng theo ch ữ đầu của Mã hàng: C fi Công thức ô C3:
là chuỗi cần trích ký tự
2. Hàm RIGHT 3. Hàm MID Công dụng: Trích bên ph ải một chu ỗi một ho ặc nhiều ký tự dựa vào số ký tự được chỉ định. Công thức: Công dụng: Trích một chuỗi con từ một chuỗi text, bắt đầu từ vị trí start_num với số ký tự được chỉ định num_chars. Công thức:
là ký t ự mà b ạn cần trích bên trái
: vị trí bắt đầu trích lọc chuỗi con trong text.
text: chuỗi hoặc tham chiếu đến chuỗi.
text num_chars chuỗi text. Lưu ý:
start_num num_chars: số ký tự của chuỗi mới cần trích từ chuỗi text. Lưu ý:
start_num > chiều dài chuỗi text thì hàm trả về chuỗi rỗng. start_num < 1 hàm trả về lỗi #VALUE!
num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. num_chars nếu bỏ qua thì mặc định là 1.
ết quả : “thu”}
ết quả : "CD"}
Ví dụ: =RIGHT("ABCD",2) {k
Ví dụ: =MID(”mùa thu Hà Nội”; 5; 3) {k
7
Bùi Thành Khoa
23/06/2011
6. Hàm UPPER 4. Hàm LEN Công dụng: Chuyển tất cả các ký t ự trong chu ỗi thành ký tự hoa. Công dụng: Tính độ dài (số ký tự) của một chuỗi. Công thức: Công thức:
text: chuỗi văn bản cần chuyển định dạng.
text: nội dung mà bạn cần xác định độ dài
Ví dụ: =LEN("ABCD") {k ết quả là 4}
Ví dụ: =UPPER("Trung tam") {kết quả: "TRUNG TAM"}
5. Hàm LOWER 7. Hàm PROPER Công dụng: Đổi tất cả các ký t ự trong chu ỗi sang Công dụng: Chuyển ký t ự đầu tiên c ủa mỗi từ chữ thường. thành chữ hoa. Công thức: Công thức:
text
: chuỗi văn bản cần chuyển định dạng.
text
: chuỗi văn bản cần chuyển định dạng.
Ví dụ: =PROPER("trung tam”) {k ết quả : “Trung Tam”} Ví dụ: =LOWER("TRUNG Tam") {k ết qu ả : “trung tam”}
8. Hàm TRIM
Ví dụ:
ết quả : }
8 5 5
=FIND("e","MS. Excel 6.0") {k =FIND("E","MS. Excel 6.0") {kết quả : } =FIND("Excel","MS. Excel 6.0") {k ết quả : } Giả sử giá trị trong ô A1 là chuỗi “Lê Văn Hùng” =LEFT(A1;Find(" ";A1)-1) {k
ết quả: }
“Lê”
Công dụng: Xóa tất cả các ký t ự tr ắng của chu ỗi trừ những khoảng đơn dùng để làm kho ảng cách bên trong chuỗi. Công thức:
10. Hàm SEARCH
text: chuỗi cần xóa các ký tự trắng. Ví dụ: =TRIM(" Thủ đô Hà Nội ") {kết quả: "Thủ đô Hà Nội"}
9. Hàm FIND Tương tự hàm FIND nhưng không phân biệt chữ HOA và thường.
Công dụng: cho vị trí c ủa chu ỗi con find_text trong chuỗi text bắt đầu tìm từ vị trí start_num, nếu bỏ qua start_num nó cho giá trị bằng 1. Hàm này phân biệt chữ HOA và thường. Công thức:
8
Bùi Thành Khoa
23/06/2011
11. Hàm SUBSTITUTE 2.5. HÀM TÌM KiẾM 1. Hàm VLOOKUP
Công dụng: Thay th ế một chu ỗi cụ th ể bên trong chuỗi bằng chuỗi khác. Dùng SUBSTITUTE khi mu ốn thay thế một chuỗi cụ thể. Công thức:
Công dụng: Dò tìm một giá tr ị ở cột đầu tiên bên trái của 1 bảng dữ liệu. Nếu tìm th ấy sẽ trả về giá tr ị ở cùng trên dòng với giá trị tìm thấy trên cột mà bạn chỉ định.
Công thức: text: chuỗi văn bản cần thay thế nội dung.
lookup_value: giá trị dùng để tìm kiếm, nó có th ể là một giá trị, một tham chiếu hay một chuỗi ký tự. table_array: vùng ch ứa dữ liệu cần tìm. Đây là bảng dữ liệu phụ có nội dung thường cố định để lấy dữ liệu.
Các giá trị ở cột đầu tiên có th ể là giá trị số, chuỗi ký tự, hoặc logic. Nếu range_lookup là TRUE (hay 1) thì giá trị ở cột đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần.
(cid:151) Ví dụ 1
old_text: nội dung bên trong chu ỗi text cần thay th ế. new_text: chuỗi văn bản mới để thay chuỗi cũ instance_num: chỉ định thay thế ở lần mà tìm th ấy chuỗi old_text trong chuỗi text. Nếu bỏ qua thì s ẽ thay th ế tất cả các old_text được tìm thấy trong chuỗi text. Ví dụ 1: =SUBSTITUTE("CD001";"CD";"CDRW";1) {k ết quả: CDRW001} =SUBSTITUTE("DVD002";"0";"A") {k ết quả: DVDAA2} Ví dụ 2: Tách riêng tên và họ lót. tach Ho va ten.xls
B
C
D
E
F
M
2
3
5
N
5
7
2
O
7
1
7
row_index_num: số thứ tự cột trên bảng dữ liệu phụ mà dữ liệu bạn cần lấy. Giá trị trả về nằm trên cột bạn chỉ định này và ở dòng mà hàm tìm thấy giá trị dò tìm lookup_value. range_lookup: giá trị logic được chỉ định muốn VLOOKUP tìm kiếm chính xác hay là t ương đối. Nếu range_lookup là TRUE (hay 1) hàm sẽ trả về kết quả tìm kiếm tương đối; khi đó nếu lookup_value lớn hơn ph ần tử cuối cùng trong danh sách, xem nh ư tìm th ấy ở ph ần tử cu ối cùng. N ếu range_lookup là FALSE (hay 0) hàm tìm ki ếm chính xác, nếu không có trả về lỗi #N/A! (Not Available: bất khả thi).
P
2
9
9
Lưu ý:
10 11 12 13 14
Nếu lookup_value nhỏ hơn giá tr ị nhỏ nhất trong cột đầu tiên c ủa bảng dữ li ệu ph ụ, VLOOKUP tr ả về lỗi #N/A!.
VLOOKUP xuất phát t ừ vertical lookup : dò tìm theo
◦ =VLOOKUP(“N”,$B$10:$D$13,2,FALSE) ◦ =VLOOKUP(“P”,$B$10:$D$13,2,FALSE) ◦ =VLOOKUP(“N”,$B$10:$D$13,3,FALSE) ◦ =VLOOKUP(“P”,$B$10:$D$13,3,FALSE)
phương đứng, hay theo cột. Ví dụ:
ham-vlookup.xls
9
Bùi Thành Khoa
23/06/2011
(cid:151) Ví dụ 2
2. Hàm HLOOKUP
=VLOOKUP(A2,$B$12:$C$14,2,FALSE)
A
C
D
B
TÊN DV
HỌ TÊN
DV
1
Ban GĐ
Tạ Minh Hải
A
2
Phạm Thái
B
Mọi nguyên t ắc ho ạt động của hàm HLOOKUP (Horizontal Look Up ) gi ống nh ư hàm VLOOKUP (Vertical Look Up), chỉ khác là hàm VLOOKUP dò tìm ở cột bên trái, tham chi ếu số li ệu ở các cột bên phải, còn hàm HLOOKUP dò tìm ở hàng trên cùng, tham chiếu số liệu ở các hàng phía dưới.
3
Ng. Biểu
C
4
ham-Hlookup.xls
Ng. Thanh
C
Ví dụ:
5
Lê Sơn
A
6
Kim Liên
B
7
DANH MỤC ĐƠN VỊ
8
DV
TÊN ĐƠN VỊ
9
A
Ban GĐ
10
B
P. Tổ chức
11
C
P. Tài vụ
12
2.6. HÀM NGÀY THÁNG
=HLOOKUP(A2,$B$11:$D$12,2,FALSE)
(cid:151) Ví dụ
A
B
C
D
DV
TÊN DV
HỌ TÊN
1
Ban GĐ
Tạ Minh Hải
A
2
Phạm Thái
B
3
Ng, Biểu
C
4
Ng. Thanh
C
5
Lê Sơn
B
6
Kim Liên
7
Excel hỗ trợ tính toán ngày tháng cho Windows và Macintosh. Windows dùng hệ ngày b ắt đầu từ 1900. Macitosh dùng hệ ngày bắt đầu từ 1904. Tài liệu này được diễn giải theo hệ ngày 1900 dành cho Windows. Hệ th ống ngày gi ờ Excel ph ụ thu ộc vào thi ết lập trong Regional Options của Control Panel . Mặc định là h ệ th ống của Mỹ "Tháng/Ngày/Năm" (M/d/yyyy). Bạn có th ể sửa lại thành hệ th ống ngày của VN "Ngày/Tháng/Năm" (dd/MM/yyyy).
8
9
DANH MỤC ĐƠN VỊ
10
DV
11
C
B
A
TÊN ĐV
Ban GĐ
P. Tổ chức
P. Tài vụ
12
13
14
Khi bạn nhập một giá tr ị ngày tháng không h ợp lệ nó sẽ tr ở thành một chu ỗi văn bản. Công th ức tham chiếu tới giá trị đó sẽ trả về lỗi.
10
Bùi Thành Khoa
23/06/2011
1. Hàm WEEKDAY 2. Hàm TODAY Công dụng: Trả về th ứ tự của ngày trong tu ần của Công dụng: Trả về ngày hiện tại trong hệ thống của định dạng ngày tháng chỉ ra. máy tính. Công thức: Công thức:
3. Hàm DAY
serial_number: chuỗi số đại diện ngày tháng cần tìm. return_type: Xác định kiểu giá trị trả về. Cụ thể:
return_type = 1 (hoặc không nhập): hàm WEEKDAY trả về 1 là Sunday (Chủ nhật), 7 là Saturday (Thứ 7). return_type = 2: WEEKDAY trả về 1 là Monday (Thứ 2), 7 là Sunday (Chủ nhật).
Công dụng: Trả về th ứ tự của ngày từ chu ỗi ngày tháng. Công thức:
dạng chuỗi số kiểu ngày.
ết quả: 3}
serial_number: Ví dụ: =DAY(“15/10/2008") {K
ết quả: 15}
Ví dụ: =WEEKDAY("02/09/2008") {K Vậy ngày lễ Quốc khánh năm 2008 rơi vào ngày th ứ Ba trong tuần.
ết quả: 10}
4. Hàm MONTH 6. Hàm DAY360 Công dụng: Trả về thứ tự của tháng từ giá tr ị kiểu ngày tháng. Công thức: Công dụng: Trả về số ngày giữa 2 giá trị ngày tháng dựa trên cơ sở một năm có 360 ngày (12 tháng, m ỗi tháng 30 ngày). Công thức: dạng chuỗi số kiểu ngày. serial_number: Ví dụ: =MONTH(“15/10/2008") {K
5. Hàm YEAR Công dụng: Trả về năm của một giá tr ị hoặc chuỗi
start_date , end_date: hai mốc bắt đầu và kết thúc để tính s ố ngày. Nếu start_date lớn hơn end_date hàm s ẽ trả về số âm.
đại diện cho ngày tháng. Ví dụ 1: Công thức:
serial_number: dạng chuỗi số kiểu ngày.
ết quả: 2008}
Ví dụ 1: =YEAR(“15/10/2008") {K
ết quả: 331}
Ví dụ 2:
=DAYS360(A3;B3) {K Ví dụ 2: Ham-ngaythang.xls
11
Bùi Thành Khoa
23/06/2011
CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT
¢ Trên một cơ sở dữ liệu dạng bảng của
Excel ta có thể: (cid:151) Lọc (Filter) (cid:151) Xoá (Delete) (cid:151) Rút trích (Extract)
¢ Theo một tiêu chuẩn nào đó ¢ Để thực hiện được các thao tác này ta
phải tạo các vùng sau:
CHƯƠNG 5 CƠ SỞ DỮ LIỆU TRÊN BẢNG TÍNH 1. CÁC KHÁI NIỆM 2. THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH 3. CÁC DẠNG VÙNG TIÊU CHUẨN 4. CÁC HÀM DATABASE
(cid:151) Dòng đầu chứa tiêu đề cột (FieldName)
CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT ¢ Vùng Database: là vùng CSDL g ồm ít nhất hai dòng Vùng Criteria Vùng Database
B
C
D
E
F
G
H
(cid:151) Các dòng còn lại chứa dữ liệu gọi là mẫu tin (Record)
¢ Ví dụ A
TT
HO
TEN
CV
SO CON
LUONG
1
¢ Vùng Criteria: là vùng tiêu chu ẩn chứa điều kiện để tìm kiếm, xoá
Tran Van
Nam
TP
3
2400000
CV
2
1
hay rút trích, vùng này có ít nh ất hai dòng
Le Quy
Hien
NV1
2
1300000
TP
3
2
(cid:151) Dòng đầu chứa tiêu đề cột
Pham Thi
Bich
TP
1500000
4
3
(cid:151) Các dòng còn lại chứa điều kiện
Do Hoang
Nam
NV2
1
2500000
5
4
Nguyen Thanh
Hai
NV1
3
1500000
6
5
¢ Vùng Extract: là vùng trích dữ liệu chứa các mẫu tin của vùng
7
Database thoả điều kiện vùng Criteria
8
(cid:151) Vùng Extract được thức hiện với thao tác rút trích
9
TT
TEN
(cid:151) Thao tác Xoá, Lọc không dùng đến vùng này
10
1
Nam
¢ Field: là tiêu đề của cột cần tính toán
11
2
Bich
(cid:151) Tính Tổng lương -> Field là ô chứa từ Lương trong Database
Vùng Extract
12
Bùi Thành Khoa
23/06/2011
CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT
CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT
CÁC DẠNG VÙNG TIÊU CHUẨN ¢ Tiêu chuẩn số
CÁC DẠNG VÙNG TIÊU CHUẨN ¢ Tiêu chuẩn so sánh
(cid:151) Ô điều kiện chứa toán tử so sánh kèm với giá trị so
SO CON
2
¢ Tiêu chuẩn chuỗi
(cid:151) Trong ô điều kiện có thể chứa các ký tự đại diện
SO CON
¢ * Đại diện cho một nhóm ký tự bất kỳ ¢ ? Đại diện cho một ký tự bất kỳ
sánh. Các toán tử gồm ¢ > lớn hơn ¢ < nhỏ hơn ¢ >= lớn hơn hoặc bằng ¢ <= nhỏ hơn hoặc bằng ¢ = bằng ¢ <> khác
>2
TEN TEN NAM H*
CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT
CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT
CÁC DẠNG VÙNG TIÊU CHUẨN ¢ Tiêu chuẩn công thức
CÁC DẠNG VÙNG TIÊU CHUẨN ¢ Liên kết tiêu chuẩn
(cid:151) Ô điều kiện có kiểu công th ức. Trong trường hợp sử
dụng tiêu chuẩn này cần lưu ý ¢ Ô tiêu đề vùng tiêu chuẩn phải khác tất cả các tiêu đề
vùng Database
(cid:151) Có thể lọc, xoá hay rút trích các mẫu tin trong Database bằng cách giao (AND) hay hội (OR) của nhiều điều kiện khác nhau ¢ Nếu các ô điều kiện có tính chất giao (và) ¢ Nếu các ô điều kiện có tính chất hội (hoặc)
¢ Trong ô điều kiện phải lấy địa chỉ của ô trong mẫu tin đầu tiên (dòng thứ hai trong Database) để so sánh
(cid:151) Ví dụ: tiêu chuẩn ký tự đầu của TEN khác “H”
SO CON CHUC VU SO CON=2 và CHUC VU=NV1
>2 2 NV1 TP SO CON>2 và CHUC VU=TP Dòng tiêu đề XYZ FALSE
Công thức: =LEFT(C2,1)<>”H”
13
Bùi Thành Khoa
23/06/2011
CÁC KHÁI NIỆM VỀ VÙNG DATABASE, CRITERIA VÀ EXTRACT
ỨNG DỤNG 1: THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH (1)
CÁC DẠNG VÙNG TIÊU CHUẨN
¢ Tiêu chuẩn trống
¢ Lọc (Filter)
(cid:151) Di chuyển con trỏ vào một ô bất kỳ của
(cid:151) Nếu trong ô điều kiện không có dữ liệu, tiêu chuẩn là tuỳ ý (cid:151) Ví dụ
vùng Database
(cid:151) Chọn menu Data/Filter/Advance Filter
¢ Action: Filter the list, in-place
¢ List range: chọn địa chỉ vùng Database
¢ Criteria: chọn địa chỉ vùng Criteria
¢ Unique record only: lọc bỏ những dòng
trùng nhau chỉ hiển thị 1 dòng
(cid:151) Thể hiện điều kiện: =OR(SOCON=2, CHUCVU=TP)
SO CON CHUC VU 2 TP
SO CON=2 hoặc CHUC VU=TP
¢ Muốn bỏ việc lọc các mẩu tin
(cid:151) Chọn menu Data/Filter/Show All
THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH (2)
THAO TÁC LỌC, XOÁ VÀ RÚT TRÍCH (3)
¢ Rút trích
¢ Xoá
(cid:151) Di chuyển con trỏ vào một ô bất kỳ
(cid:151) Di chuyển con trỏ vào một ô bất kỳ của vùng
của vùng Database
Database
(cid:151) Chọn menu Data/Filter/Advance
(cid:151) Chọn menu Data/Filter/Advance Filter
Filter
(cid:151) Sau khi các dòng thoả mãn điều kiện được trình
bày
¢ Action: Copy to another location ¢ List range: chọn địa chỉ vùng Database
(cid:151) Chọn các dòng nay và chọn menu Edit/Delete
¢ Criteria: chọn địa chỉ vùng Criteria
Row
¢ Copy to: nhập địa chỉ dòng đầu vùng
Extract (Số ô = số ô vùng List range )
(cid:151) Chọn menu Data/Filter/Show All để hiển thị lại
¢ Unique record only
các dữ liệu bị dấu đi trong thao tác lọc
14
Bùi Thành Khoa
23/06/2011
ỨNG DỤNG 2. CÁC HÀM DATABASE
CÁC HÀM DATABASE
¢ DSUM(
¢ DMAX(
(cid:151) Hàm tính tổng trên cột Field của vùng Database thoả mãn điều kiện của vùng Criteria
(cid:151) Ví dụ: Tính tổng lương của những người có
số con bằng 2
DSUM(A1:E7,E1,G2:G3)
B
C
D
E
F
G
A
¢ DMIN(
TT
TEN
1
SO CON
TUOI
LUONG
1
Nam
2
2
26
5200
15260 SO CON
¢ DCOUNT(
2
Bay
3
1
25
2500
2
3
Hong
4
3
26
6800
)
(cid:151) Đếm số phần tử kiểu số trên cột
4
Sau
5
2
27
4860
5
Chin
6
1
30
6300
Field của vùng Database thoả mãn điều kiện vùng Criteria
6
Khoi
7
2
26
5200
8
CÁC HÀM DATABASE
¢ DCOUNTA(
)
(cid:151) Đếm số phần tử khác rỗng trên cột Field
của vùng Database thoả mãn điều kiện vùng Criteria
¢ DAVERAGE(
)
(cid:151) Tính giá trị trung bình trên cột Field
HỎI VÀ ĐÁP
của vùng Database thoả mãn điều kiện vùng Criteria
15
Bùi Thành Khoa
23/06/2011
CÁC LOẠI BIỂU ĐỒ
¢ Trong Excel có thể dựa vào dữ
¢ Có nhiều loại biểu đồ: (cid:151) Column: biểu đồ dạng cột (cid:151) Bar: biểu đồ dạng khối (cid:151) Line: biểu đồ dạng đường kẻ (cid:151) Pie: biểu đồ dạng hình tròn (cid:151) XY: biểu đồ dạng hàm số (cid:151) Area: biểu đồ dạng miền (cid:151) …
¢ Ứng với mỗi loại biểu đồ, có thể
liệu trên Worksheet đề tạo các biểu đồ
CHƯƠNG 6 VẼ BIỂU ĐỒ 1. CÁC LOẠI BIỂU ĐỒ 2. CÁC THÀNH PHẦN CƠ BẢN TRONG BIỂU ĐỒ 3. VẼ BIỂU ĐỒ
CÁC THÀNH PHẦN CƠ BẢN TRONG BIỂU ĐỒ
VẼ BIỂU ĐỒ
chọn nhiều dạng khác nhau
¢ Chọn vùng dữ liệu cần vẽ biểu đồ trên
Worksheet
¢ Chọn meu Insert/Chart hoặc chọn biểu
tượng trên thanh công cụ
Tiêu đề
Tiêu đề trục Y
Tiêu đề trục X Chú thích
16
Bùi Thành Khoa
23/06/2011
VẼ BIỂU ĐỒ
VẼ BIỂU ĐỒ
¢ Tab Titles
(cid:151) Chart title: tiêu đề của biểu đồ
(cid:151) Category (X) axis:
tiêu đề trục X
(cid:151) Category (Y) axis:
tiêu đề cột Y
¢ Tab Legend
(cid:151) Show legend:
ẩn/hiện chú thích (cid:151) Placement: Vị trí
chú thích
VẼ BIỂU ĐỒ
¢ As new sheet: tạo biểu đồ trên một
sheet riêng
¢ As object in: đưa biểu đồ vào
worksheet hiện tại
HẾT CHƯƠNG 6
17
Bùi Thành Khoa
23/06/2011
CÁC THÀNH PHẦN TRONG MỘT TRANG IN
Top Margin
HEADER
Right Margin
Left Margin
CHƯƠNG 7 IN ẤN 1. CÁC THÀNH PHẦN TRONG MỘT TRANG IN
2. IN BẢNG BIỂU
FOOTER
Bottom Margin
CÁC THÀNH PHẦN TRONG MỘT TRANG IN
IN BẢNG BIỂU
¢ Tab Page: định cấu
¢ Header: tiêu đề đầu mỗi trang ¢ Footer: tiêu đề cuối mỗi trang ¢ Margin
hình chung (cid:151) Portrait: định dang in dọc theo giấy
(cid:151) Left: khoảng cách từ lề trái đến nội
(cid:151) Landscape: định
dung trang in
(cid:151) Right: khoảng cách từ lề phải đến
nội dung trang in
dang in ngang theo giấy
(cid:151) Top: khoảng cách từ lề trên đến nội
dung trang in
(cid:151) Bottom: khoảng cách từ lề dưới đến
nội dung trang in
Chọn menu File/Page Setup:
18
Bùi Thành Khoa
23/06/2011
IN BẢNG BIỂU
IN BẢNG BIỂU
¢ Tab Margins: định
¢ Tab Header/Footer:
định dạng tiêu đề đầu trang và cuối trang (cid:151) Trong Header và
Footer chứa một số tiêu đề chuẩn. (cid:151) Chọn None trong
Header và Footer: không tạo tiêu đề trang
lề trang in (cid:151) Top (cid:151) Bottom (cid:151) Left (cid:151) Right (cid:151) Header: định khoảng cách từ biên giấy đến nội dung tiêu đề đầu trang
(cid:151) Custom Header…: tạo Header theo ý muốn (cid:151) Custom Footer…: tạo Footer theo ý muốn
(cid:151) Footer: định khoảng cách từ biên giấy đến nội dung tiêu đề cuối trang
IN BẢNG BIỂU
IN BẢNG BIỂU
¢ Định tiêu đề bằng cách nhập trực tiếp
¢ Tab Sheet: định
vào Left section, Center section, Right section.
¢ Sử dụng các icon đề định dạng tiêu đề
(cid:151) Print title: xác định dòng hay cột lặp lại trên mỗi trang
vùng muốn in (cid:151) Print area: xác định địa chỉ vùng in
19
Bùi Thành Khoa
23/06/2011
