Chương 2
Các vấn đề xử lý căn bản trong Excel
GV: Nguyễn Thị Thùy Liên
Email: lien.nguyenthithuy@phenikaa-uni.edu.vn
Các toán tử trong Excel
Toán tử
Tên
Ví dụ
Kết quả
Cộng
= 2+2
4
+
Trừ
= 4-2
2
-
Nhân
=4*2
8
*
Chia
=4/2
2
/
Mũ
=4^2
16
^
=8>7
TRUE
So sánh
>,>=,<,<=,=,<>
Nối
= “Việt” & “Nam”
Việt Nam
&
Tham chiếu
= Sum(B2:B5)
:
Ngăn cách
=Sum(B5:B9,D4:D9)
, hoặc ;
Số thập phân hay ngăn cách vùng
Phần trăm
7*2%
0.14
%
Tin học ứng dụng
2
Sử dụng hàm trong Excel
❖ Hàm (function) trong Excel là một tổ hợp các công thức đã được xây dựng sẵn nhằm thực hiện các tính toán hay xử lý chuyên biệt nào đó.
❖ Hàm được đặc trưng bởi tên hàm, dấu đóng ngoặc và danh
sách tham số.
❖ Tên hàm là các tên chuẩn do Excel quy định
❖ Dấu mở, đóng ngoặc bắt buộc phải có. Đối số là các dữ
liệu đầu vào để tính toán hay xử lý có thể có hoặc không. Nếu có nhiều đối số thì ngăn cách bởi dấu “,”, nếu dấu “,” sử dụng làm dấu thập phân thì dấu ngăn cách là dấu “;”
Tin học ứng dụng
3
Sử dụng hàm trong Excel
❖ Nhập hàm vào bảng tính:
▪ C1: Nhập hàm trực tiếp vào ô kết quả:
= tenham(danh sách các tham số)
▪ C2: Nhập hàm từ giao diện Insert function
Tin học ứng dụng
4
Một số hàm logic
❖ AND (đối 1, đối 2,…đối n): phép VÀ, là hàm logic chỉ đúng khi
tất cả các đối số có giá trị đúng.
Các đối số là các hằng, biểu thức logic
VD: AND(B3>=23, B3<25)
❖ OR (đối 1, đối 2,…đối n): phép HoẶC, là hàm logic, chỉ sai khi
tất cả các đối có giá trị sai. VD: =OR(D3>=25, D3<23)
❖ NOT(logic): Trả về TRUE nếu logic là FALSE và ngược lại
Tin học ứng dụng
5
Hàm số học
❖ABS(N): Hàm lấy giá trị tuyệt đối: N là số/ biểu thức số
▪ VD: = ABS(-25) kết quả 25
ABS(5-149) kết quả 144
❖SQRT(N): Hàm lấy căn: N là số/ biểu thức số (N>0)
▪ VD: =SQRT(25) kết quả 5
❖INT(N): Hàm lấy phần nguyên: N là số/ biểu thức số
▪ VD: =INT(236.35) kết quả 236
Tin học ứng dụng
6
Hàm số học
❖MOD(N,M): Hàm lấy phần dư phép chia N cho M
▪ VD: =MOD(10,3) kết quả 1
❖PI(): Hàm lấy giá trị PI (3.141593)
❖POWER(X,Y): Hàm mũ, trả về x mũ y
▪ VD: =POWER(4,2) kết quả 16
Tin học ứng dụng
7
Hàm số học
❖ROUND(biểu thức số, N): Hàm làm tròn lên
▪ N>0: làm tròn bên phải cột thập phân
▪ N<0: làm tròn bên trái cột thập phân
▪ N=0: làm tròn, không lấy số lẻ
• VD:
=ROUND( 35123.376 ,2)
kết quả 35123.38
=ROUND( 35123.376 ,-3)
kết quả 35000
=ROUND( 35123.376 ,0)
kết quả 35123
Tin học ứng dụng
8
Hàm thống kê
❖ SUM(đối 1, đối 2,…đối n):
trả về tổng của các đối số,
Các đối số là các hằng, địa chỉ ô, miền
❖ AVERAGE(đối 1, đối 2,…đối n):
Trả về giá trị trung bình cộng của
các đối số
Tin học ứng dụng
9
Hàm thống kê
❖MAX(đối 1, đối 2,…đối n)
Trả về giá trị lớn nhất
❖MIN(đối 1, đối 2,…đối n)
Trả về giá trị nhỏ nhất
Tin học ứng dụng
10
Hàm thống kê
❖COUNT(đối 1, đối 2,…) đếm số lượng các ô chứa số
và các đối số trong các đối số.
▪ Các đối số là các số, ngày tháng, địa chỉ ô, địa chỉ
miền
Tin học ứng dụng
11
Hàm thống kê
❖COUNTA(value1; value 2;…)
Đếm số lượng các ô chứa dữ liệu trong danh sách
❖COUNTBLANK(value1; value 2;…)
Đếm số lượng các ô không chứa dữ liệu trong danh sách
Tin học ứng dụng
12
Hàm thống kê
❖ RANK (number, ref, order)
Trả về thứ hạng của số number trong một danh sách các
đối số ref
▪ Number: là số muốn tìm hạng của nó
▪ Ref: là một dãy hay một tham chiếu đến một danh
sách các đối số
▪ Order: là số chỉ định cách đánh hạng:
• Nếu order =0 hoặc không ghi thì số lớn được xếp
hạng trước
• Nếu order khác 0 thì số nhỏ được xếp hạng trước
Tin học ứng dụng
13
Hàm thống kê
Tin học ứng dụng
14
Hàm thống kê
❖SUMPRODUCT (array 1; array 2;..)
Hàm tính tổng của các tích
= 3*2 + 4*7+ 8*6 + 6*7 + 1*5 + 9*3
Tin học ứng dụng
15
Các hàm điều kiện
❖IF(bt logic, giá trị đúng, giá trị sai)
▪ Trả về giá trị đúng nếu BT logic TRUE
VD: = IF(A3>5, “Đỗ”, “Trượt”)
▪ Trả về giá trị sai nếu BT logic FALSE
VD: =IF(C6<=300,1,IF(C6>400,3,2))
▪ Hàm IF có thể viết lồng nhau
Tin học ứng dụng
16
Các hàm điều kiện
❖SUMIF( miền dk, dk, miền tính tổng)
❖Hàm tính tổng có điều kiện
Giả sử miền B2:B5 chứa các gt tiền nhập 4 mặt hàng tương ứng 100, 200, 300, 400. Miền C2:C5 chứa tương ứng 7, 14, 21,28 thì hàm tiền lãi SUMIF(B2:B5,”>160”,C2:C5) cho kết quả : 63
= 14+21+28
Tin học ứng dụng
17
Các hàm điều kiện
❖COUNTIF(miền đếm, điều kiện)
❖Đếm số lượng các ô trong miền đếm thỏa mãn điều
kiện
Tin học ứng dụng
18
Hàm chuỗi
❖ LEFT(“chuỗi ký tự”, n): Cho n ký tự bên trái của chuỗi
▪ VD: = LEFT(“Gia Lâm – Hà Nội”,7)
Cho kết quả là chuỗi “Gia Lâm”
❖ RIGHT(“chuỗi ký tự”, n): Cho n ký tự bên phải chuỗi
▪ VD: =RIGHT(“Gia Lâm – Hà Nội”,6)
Cho kết quả là chuỗi “Hà Nội”
❖ MID(“chuỗi ký tự”, m , n): Cho n ký tự tính từ ký tự thứ
m của chuỗi.
▪ VD: = MID(“Gia Lâm – Hà Nội”,11,2)
Cho kết quả là chuỗi “Hà”
Tin học ứng dụng
19
Hàm chuỗi
❖LEN(“chuỗi”): Hàm tính chiều dài chuỗi
Kết quả : 16
▪ VD: = LEN(“Hà Nội, Việt Nam”)
❖LOWER(“chuỗi”): Hàm đổi chuỗi thường
Kết quả: “hà nội mùa thu”
▪ VD: =LOWER(“HÀ NỘI MÙA THU”)
Tin học ứng dụng
20
Hàm chuỗi
❖UPPER(“chuỗi”): Hàm đổi chuỗi hoa
VD: =UPPER(“hà nội mùa thu”)
kết quả: “HÀ NỘI MÙA THU”
❖PROPER(“chuỗi”): Hàm đổi các ký tự đầu của word
thành chữ hoa
VD: =PROPER(“hà nội mùa thu”)
kết quả: “Hà Nội Mùa Thu”
Tin học ứng dụng
21
Hàm chuỗi
❖TRIM(“chuỗi”): Hàm xóa khoảng trống thừa trong
chuỗi.
VD: =TRIM(“ Xin chào”)
kêt quả: “Xin chào”
❖VALUE(“chuỗi số”): Hàm đổi chuỗi số thành trị số
VD: =VALUE(“123”)
kết quả: 123
Tin học ứng dụng
22
Hàm kiểu ngày (date)
❖WEEKDAY(“chuỗi ngày”): Trả về giá trị Thứ trong
tuần (từ 1 đến 7: chủ nhật đến thứ hai)
VD: =WEEKDAY(“2/9/2012”)
kết quả : 7
❖TODAY(): Trả về ngày hiện tại của hê thống máy
tính
VD: = TODAY()
Kết quả : 10/05/2012
Tin học ứng dụng
23
Hàm kiểu ngày
❖YEAR (“chuỗi ngày”): Trả về số năm tương ứng
(1900 đến 2078)
VD: = YEAR(“24/12/2012”) kq: 2012
❖DAYS360(“ngày bắt đầu” ; “ngày kết thúc”)
Trả về tổng số ngày từ “ngày bắt đầu” đến “ngày kết
thúc”
Tin học ứng dụng
24
Hàm dò tìm
❖ VLOOKUP( trị_tra_cứu, bảng_tra_cứu, cột_lấy_dữ_liệu, [True/False]): tra cứu gt với các gt trong cột đầu tiên của bảng và hiển thị dữ liệu tương ứng trong bảng tra cứu nằm trên cột ở đối số thứ 3. VD: = VLOOKUP(E3, $E$12:$F$16, 2, True) Nếu gt tra cứu nhỏ hơn gt 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 gt trong cột đầu tiên của bảng phải được sắp xếp tăng
dần
+ Nếu gt tra cứu không có trong cột đầu tiên của bảng thì
hàm sẽ tra cứu gt trong bảng <= gt tra cứu
Tin học ứng dụng
25
Hàm dò tìm
Tin học ứng dụng
26
Hàm dò tìm
❖HLOOKUP(gt, bảng_gt, hàng_lấy d.liệu, [1/0]):
hàm tra cứu theo hàng, tương tự hàm VLOOKUP
❖ISNA(value): Trả về giá trị TRUE nếu gặp lỗi #N/A,
ngược lại trả về FALSE.
Tin học ứng dụng
27
Hàm dò tìm
❖ MATCH(lookup_value, lookup_ref, type)
▪ Lookup_value: giá trị cần tìm, giá trị này có thể là : chuỗi, số của
một ô chứa dữ liệu là số hay chuỗi.
▪ Lookup_ref: phạm vi vùng dữ liệu (chỉ một dòng hay cột) ▪ Type: các kiểu so sánh ▪ 1: +Tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value +Lookup_ref: phải được sắp xếp theo thứ tự tăng dần +Nếu tất cả trong lookup_ref đều lớn hơn lookup_value thì hàm trả về #NA
•
-1:+ Tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value + Lookup_ref: phải được sắp xếp theo thứ tự giảm dần + Nếu tất cả trong lookup_ref đều nhỏ hơn lookup_value thì hàm trả về #NA
• 0 : dò tìm chính xác đúng, nếu không trả về #N/A
Tin học ứng dụng
28
Hàm dò tìm
Tin học ứng dụng
29
Hàm dò tìm
❖INDEX( array, row_num, column_num):
Trả về giá trị tại dòng và cột trong một dãy ô
Tin học ứng dụng
30
Một số lỗi thường gặp trong Excel
❖#####: Lỗi độ rộng.
▪ Dùng chuột kéo cột ra cho có độ rộng phù hợp. ▪ Khi giá trị ngày tháng hoặc thời gian nhập vào là
số âm cũng phát sinh lỗi này.
❖#VALUE! : Lỗi giá trị.
▪ Công thức được nhập vào một chuỗi trong khi hàm
yêu cầu là một số hoặc một giá trị logic.
▪ Do đang nhập hoặc chỉnh sửa các thành phần của
công thức mà vô tình nhấn Enter.
▪ Do nhập quá nhiều tham số cho toán tử hoặc một
hàm
Tin học ứng dụng
31
Một số lỗi thường gặp trong Excel
❖#DIV/0!: Lỗi chia cho 0.
▪ Do nhập vào công thức số chia là 0 hoặc số chia trong công thức là một tham chiếu đến ô trống.
❖#NAME! : Lỗi sai tên
▪ Do dùng những hàm không thường trực trong
Excel như EDATE,EMONTH…Khi cần dùng phải vào menu Tools | Add-ins đánh dấu chọn vào tiện ích Analysis ToolPak ▪ Do nhập sai tên một hàm ▪ Dùng ký tự không được phép trong công thức
Tin học ứng dụng
32
Một số lỗi thường gặp trong Excel
❖#N/A: Lỗi dữ liệu
▪ Giá trị trả về không tương thích với các hàm dò tìm
như VLOOKUP, HLOOKUP..
▪ Không đồng nhất dữ liệu khi sử dụng địa chỉ mảng
❖#FEF!: Sai vùng tham chiếu
▪ Khi xóa những ô đang được tham chiếu công thức.
❖#NUM !: Lỗi dữ liệu kiểu số
▪ Dùng một đối số không phù hợp trong công thưc,
sử dụng đối số là dữ liệu kiểu số
❖#NULL!: Lỗi dữ liệu rỗng
Tin học ứng dụng
33
Công thức mảng
❖Khái niệm:
▪ Một mảng là một tập hợp các phần tử có quan hệ
hoặc độc lập với nhau
▪ Trong Excel, một mảng có thể là một mảng một
chiều hoặc một mảng hai chiều. Chiều của mảng ở đây chính là chỉ các dòng và cột của mảng
Tin học ứng dụng
34
Công thức mảng
❖Công thức mảng là một công thức được bao bọc bởi
cặp dấu {} sau khi nhấn tổ hợp phím Ctrl+Shift+Enter
❖Kết quả của công thức mảng có thể trả về một hoặc
nhiều ô
❖Nếu trường hợp nhập vào công thức mà quên nhấn tổ hợp phím Ctrl+Shift+Enter công thức sẽ trả về giá trị không đúng hay thông báo lỗi #VALUE!
Tin học ứng dụng
35
Công thức mảng trả kết quả về một vùng
❖Cách thực hiện
▪ Chọn một vùng để trả về kết quả
▪ Nhập công thức cần tính
▪ Nhấn tổ hợp phím Ctrl +Shift +Enter
▪ Ví dụ:
Tin học ứng dụng
36
Công thức mảng trả kết quả về một ô
❖Được sử dụng khi cần tính kết quả trả về một ô nhưng
dựa vào nhiều mảng số liệu
❖Cách thực hiện
▪ Nhập công thức cần tính vào ô trả về kết quả
▪ Nhấn tổ hợp phím Ctrl+Shift+Enter
Tin học ứng dụng
37
Mảng 1 chiều
❖Mảng một chiều là một hàng (mảng ngang) hay cột
(mảng dọc)
❖Các phần từ trong mảng một chiều (mảng ngang) được cách biệt nhau bởi dấu “,” và trong mảng dọc được cách biệt nhau bởi dấu “;”
❖Ví dụ {1,2,3,4} : mảng ngang
{10;20;30;40}: mảng dọc
Tin học ứng dụng
38
Mảng 2 chiều
❖Mảng 2 chiều là một hình chữ nhật bao gồm nhiều
hàng và nhiều cột
❖Tương tự như mảng một chiều, ta sử dung các dấu “,” để ngăn cách các phần tử trong cùng một hàng và dấu “;” để ngăn cách các hàng.
❖Ví dụ:
Tin học ứng dụng
39
Hằng mảng
❖Mảng các hằng số được xem là một hằng mảng và có
thể đặt tên cho nó
❖Một hằng mảng sẽ không chứa các công thức, các
hàm và các giá rị có chứa dấu $, dấu phảy, dấu chấm phảy
❖Ví dụ:
Tin học ứng dụng
40
Đặt tên cho 1 mảng
❖Vào Formular > Nhóm Define Names > Define Name
hoặc nhấn Ctrl + F3
❖Xuất hiện hộp thoại
▪ Đặt tên
▪ Nhấn OK
Tin học ứng dụng
41
Đặt tên cho 1 mảng
Tin học ứng dụng
42
Đặt tên cho 1 mảng
Tin học ứng dụng
43
Các thao tác với công thức mảng
❖Tạo một mảng từ các giá trị trong một dãy
❖Tạo một hằng mảng từ các giá trị trong một dãy
❖Chọn một dãy công thức mảng
❖Hiệu chỉnh một công thức
❖Mở rộng hoặc thu hẹp công thức mảng nhiều ô
Tin học ứng dụng
44
Tạo một mảng từ các giá trị trong một dãy
Tin học ứng dụng
45
Tạo một hằng mảng từ các giá trị trong một dãy
❖B1: Tạo một mảng từ các giá trị trong một dãy
Tin học ứng dụng
46
Tạo một hằng mảng từ các giá trị trong một dãy
Nhấn F2, rồi nhấn F9
❖B2: Ngắt sự liên kết với dữ liệu nguồn
Tin học ứng dụng
47
Hiệu chỉnh một công thức
❖Không thay đổi nội dung của bất kỳ phần tử nào được
tạo nên từ công thức mảng
❖Không thể xóa các phần tử được hình thành từ công
thức mảng
❖Không thể chèn hàng/cột trong vùng có sử dụng công
thức mảng
Tin học ứng dụng
48
Hiệu chỉnh một công thức
❖Muốn hiệu chỉnh công thức mảng:
▪ Chọn tất cả các ô trong dãy mảng
▪ Nhấn F2
▪ Hiệu chỉnh xong
▪ Nhấn Ctrl +Shift +Enter
Tin học ứng dụng
49
Công thức mảng trả kết quả về một ô
•{=SUM(ISTEXT(A1:D5)*1)} •{=SUM(ISTEXT(A1:D5)+0)} •{=SUM(--ISTEXT(A1:D5))} •True*1=1 •False*1=0 •X*1=X •X+0=X •--X=X
❖1 Đếm các ô chứa dữ liệu Text trong một dãy
Tin học ứng dụng
50