Bài giảng Tin học văn phòng: Chương 1 - Hoàng Thanh Hòa (p2 CĐ Kinh tế đối ngoại)

Giáo viên: Hoàng Thanh Hòa

hthoa@cofer.edu.vn

Trang 2

hthoa@cofer.edu.vn

• Tạo lập công thức • Địa chỉ tương đối, tuyệt đối • Khái niệm hàm và cách sử dụng • Các nhóm hàm thông dụng trong Excel • Định dạng bảng tính

• Tạo công thức số học cơ bản

➢ Gõ dấu “=” trước công thức, ví dụ: =E1+F1 ➢ Nhấn Enter để kết thúc ➢ Nội dung công thức được hiển thị trên thanh

Formula bar

➢ Sử dụng cặp dấu “( )” để thay đổi độ ưu tiên của

Trang 3

hthoa@cofer.edu.vn

các phép toán trong công thức

• Các phép toán công thức số học

/

Phép chia

+

Phép cộng

^

Phép lũy thừa

-

Phép trừ

&

Phép nối chuỗi

*

Phép nhân

Trang 4

hthoa@cofer.edu.vn

• Các phép toán so sánh

Trang 5

hthoa@cofer.edu.vn

• Mức ưu tiên của các phép toán:

Trang 6

hthoa@cofer.edu.vn

➢ Phép toán trong ngoặc thực hiện trước ➢ Phép toán có độ ưu tiên cao thực hiện trước

• Nhận biết và sửa lỗi ➢ ####: không đủ độ rộng của ô để hiển thị, ➢ #VALUE!: dữ liệu không đúng theo yêu cầu của

công thức

➢ #DIV/0!: chia cho giá trị 0 ➢ #NAME?: không xác định được ký tự trong công

thức

Trang 7

hthoa@cofer.edu.vn

➢ #N/A: không có dữ liệu để tính toán ➢ #NUM!: dữ liệu không đúng kiểu số

• Địa chỉ tương đối

➢ Là địa chỉ có dạng , VD: A2, B3.. ➢ Khi sao chép đến vùng đích thì địa chỉ tại vùng

đích thay đổi tịnh tiến theo

• Địa chỉ tuyệt đối

➢ Là địa chỉ dạng <$cột $dòng>, VD: $A$2, $B$3… ➢ Khi sao chép đến vùng đich vẫn giữ nguyên địa chỉ

Trang 8

hthoa@cofer.edu.vn

ban đầu.

• Địa chỉ hỗn hợp

➢ Là địa chỉ có dạng <$cột dòng> hoặc ,

VD: $A2, B$3…

➢ Khi sao chép đến vùng đích thì chỉ có thành phần

không có dấu “$” thay đổi.

➢ Nhấn phím F4 để thay đổi từ địa chỉ tương đối ->

Trang 9

hthoa@cofer.edu.vn

tuyệt đối -> hỗn hợp.

• Địa chỉ tương đối: Là địa chỉ thay đổi trong thao tác

Trang 10

hthoa@cofer.edu.vn

copy công thức.

• Địa chỉ tuyệt đối: Là địa chỉ không bị thay đổi khi

Trang 11

hthoa@cofer.edu.vn

copy công thức.

$: Giữ cố định thành phần cột khi copy công thức. $: Giữ cố định dòng khi copy công thức.

Trang 12

hthoa@cofer.edu.vn

➢ Nhắp chọn ô chứa công thức muốn sao chép ➢ Đặt con trỏ chuột vào góc phải dưới của ô ➢ Biểu tượng chuột chuyển thành hình dấu thập mầu

đên nét đơn “+”

➢ Nhấn phím trái, kéo và di chuyển chuột theo dòng

Trang 13

hthoa@cofer.edu.vn

hoặc theo cột đến các ô cần sao chép

• Khái niệm:

➢ Hàm (function) là tên của một thao tác đã được

định nghĩa sẵn trong Excel.

➢ Tên hàm luôn đi kèm với một cặp dấu ngoặc đơn. ➢ Hàm có thể có một đối số (argument), nhiều đối số

Trang 14

hthoa@cofer.edu.vn

hoặc không có đối số, ví dụ: – INT(12.5) – SUM(2,5,10) – NOW()

• Cấu trúc hàm:

➢ (đối số 1, đối số 2,…, đối số n) ➢ Đối số có thể là

Trang 15

hthoa@cofer.edu.vn

o Giá trị kiểu số, xâu,… o Địa chỉ ô hoặc vùng ô o Một hàm khác

• Nhập công thức vào ô:

➢ Cách 1: gõ trực tiếp vào ô theo dạng =(đối số 1, đối số 2,…, đối số n) ➢ Cách 2:

Trang 16

hthoa@cofer.edu.vn

o Chuyển con trỏ ô về ô muốn nhập công thức o Trên thanh thực đơn chọn Formulas →…. o Chọn loại hàm cần thiết o Nhấn nút OK o Nhập các đối số cần thiết o Nhấn nút OK để hoàn tất

• Nhập công thức vào ô:

➢ Các đối số thường cách nhau bởi dấu “,” hoặc “;”

tùy theo thiết lập hệ thống của máy tính.

➢ Nếu đối số là một vùng địa chỉ thì cần ghi theo

dạng: địa chỉ ô góc trái phía trên:địa chỉ ô góc phải phía dưới.

➢ Khi sử dụng dấu () thì mở bao nhiêu dấu “(“ thì

Trang 17

hthoa@cofer.edu.vn

phải có bấy nhiêu dấu “)”

• Nhóm hàm xử lý số • Nhóm hàm xử lý chuỗi dữ liệu • Nhóm hàm ngày giờ • Nhóm hàm logic • Nhóm hàm dò tìm • Nhóm hàm chuyển đổi kiểu • Nhóm hàm có điều kiện

Trang 18

hthoa@cofer.edu.vn

1. Hàm giá trị tuyệt đối: • Cú pháp: =ABS(number) • Hàm trả về giá trị tuyệt đối của đối số • Number: là số cần trả về giá trị tuyệt đối • VD:

➢ ABS(2) → 2 ➢ ABS(-5) → 5 ➢ ABS(A2) → 7 (A2 đang chứa công thức = 3.5 x -2)

Trang 19

hthoa@cofer.edu.vn

2. Hàm tính tổng

• Cú pháp: =SUM (number 1, number 2,…) • Hàm trả về giá trị tổng của các đối số • Number: là các hằng hoặc địa chỉ tham chiếu ô,

miền.

• VD: Tổng lương ở ô E10 được tính theo công thức: =SUM(E4:E9) → 23

Trang 20

hthoa@cofer.edu.vn

3. Hàm trả về số nguyên

• Cú pháp: =INT (number) • Hàm trả về số nguyên nhỏ hơn gần nhất • Number: là các hằng hoặc địa chỉ tham chiếu ô,

miền.

• VD: INT(23.4) → 23

Trang 21

hthoa@cofer.edu.vn

INT(-23.4) → -24

4. Hàm lấy phần dư • Cú pháp: =MOD(number,divisor) • Hàm trả về số dư của phép chia • Với:

Trang 22

hthoa@cofer.edu.vn

➢ Number: số bị chia ➢ Divisor: số chia • VD: =MOD(23,10) → 3

5. Hàm tính lũy thừa • Cú pháp: =POWER(number,power) • Hàm trả về lũy thừa một số • Với:

➢ Number: Số cần tính lũy thừa ➢ power: số mũ

• VD: POWER(5, 2) = 25

Trang 23

hthoa@cofer.edu.vn

POWER(98.6, 3.2) = 2,401,077 POWER(4, 5/4) = 5.656854

6. Hàm tính tích các số • Cú pháp: =PRODUCT(number 1; number 2;….) • Hàm trả về tích của các đối số • VD:

Trang 24

hthoa@cofer.edu.vn

7. Hàm tính tổng các tích của các mảng dữ liệu • Cú pháp: =SUMPRODUCT(array1, array2, ...) • Hàm trả về tổng của tích các thành phần trong 2 mảng

cùng kích thước.

• VD:

SUMPRODUCT(A1:B3, C1:D3) = 156

• Cách tính:

156 = (3*2)+(4*7)+(8*6)+(6*7)+(1*5)+(9*3)

Trang 25

hthoa@cofer.edu.vn

8. Hàm làm tròn theo số chỉ định: • Cú pháp:=ROUND(number, num-digits) • Hàm trả về số đã được làm tròn theo chỉ định • Trong đó:

Trang 26

hthoa@cofer.edu.vn

➢ Number: Số cần làm tròn ➢ Num- digits: là số nguyên chỉ cách làm tròn với: >0: làm tròn đến số thập phân được chỉ định =0: làm tròn đến số nguyên gần nhất <0: làm tròn đến phần nguyên

9. Hàm làm tròn theo số chỉ định:

Trang 27

hthoa@cofer.edu.vn

➢ =ROUND(1234.5678, 3) → 1234.568 ➢ =ROUND(1234.5678, 2) →1234.57 ➢ =ROUND(1234.5678, 1) →1234.6 ➢ =ROUND(1234.5678, 0) →1235 ➢ =ROUND(1234.5678, -1) → 1230 ➢ =ROUND(1234.5678, -2) → 1200 ➢ =ROUND(1234.5678, -3) → 1000

10. Hàm tính giá trị trung bình

➢ Cú pháp: =AVERAGE(number1, number2…) ➢ Hàm trả về giá trị trung bình cộng của các đối số ➢ Number: là các hằng, địa chỉ tham chiếu đến ô,

Trang 28

hthoa@cofer.edu.vn

miền ➢ VD: =AVERAGE(4,10,7) → 7 =AVERAGE(E4:E9) →

11. Hàm tính số phần tử kiểu số

➢ Cú pháp: =COUNT(value1,value 2…) ➢ Hàm trả về số lượng ô có chứa các phần tử kiểu số ➢ Value: có thể chứa hay tham chiếu đến nhiều kiểu dữ liệu khác nhau, nhưng chỉ có những đối số mới được đếm

Trang 29

hthoa@cofer.edu.vn

➢ VD:

12. Hàm tính số ô có chứa giá trị

➢ Cú pháp: =COUNTA(value1,value 2…) ➢ Hàm trả về số lượng ô có chứa dữ liệu. ➢ Value: có thể chứa hay tham chiếu đến nhiều kiểu dữ liệu khác nhau, nhưng chỉ có những đối số mới được đếm

Trang 30

hthoa@cofer.edu.vn

➢ VD:

13. Hàm tìm giá trị lớn nhất

➢ Cú pháp: =MAX(number 1, number 2…) ➢ Hàm trả về giá trị lớn nhất trong các đối số ➢ Number:có thể là hằng hoặc địa chỉ tham chiếu ô,

miền ➢ VD:

Trang 31

hthoa@cofer.edu.vn

MAX(9,23,11) → 23 MAX(F5:F14) → 10

14. Hàm tìm giá trị nhỏ nhất

➢ Cú pháp: =MIN(number 1, number 2…) ➢ Hàm trả về giá trị nhỏ nhất trong các đối số ➢ Number: Có thể là hằng hay địa chỉ tham chiếu ô,

miền ➢ VD:

Trang 32

hthoa@cofer.edu.vn

=MIN(9,23,11) → 9 =MIN(F5:F14) → 4.5

15. Hàm xếp hạng

➢ Cú pháp: =RANK(number, ref, order) ➢ Hàm trả về thứ hạng của phần tử trong danh sách ➢ Trong đó:

+ number: là giá trị cần xếp hạng (VD: điểm 1 hs) + ref: bảng chứa các giá trị (VD: bảng điểm), có thể là mảng,

tham chiếu hoặc danh sách các số

+ order: Là 1 giá trị (0 or 1) cho biết cách thức sắp xếp: - Nếu order =0 thì số lớn có thứ hạng nhỏ - Nếu order =1 thì số lớn có thứ hạng lớn

Trang 33

hthoa@cofer.edu.vn

➢ VD hàm RANK:

Dựa vào cột tổng điểm, xếp hạng các thí sinh

Công thức tại ô E2:

Trang 34

hthoa@cofer.edu.vn

=RANK(D2,$D$2:$D$5,0) → 1

16. Hàm chuyển đổi số đo góc sang radian: • Cú pháp: =RADIANS(angle) • Hàm trả về số đo radian của một góc • Với: angle là số đo của góc tính theo độ • VD: ➢ =RADIANS(270) → 4.71239 (3𝜋/2) ➢ =RADIANS(180) → 3.14159 (𝜋) ➢ =RADIANS(90) → 1.570796 (𝜋/2)

Trang 35

hthoa@cofer.edu.vn

17. Hàm chuyển đổi số đo góc từ radian sang độ: • Cú pháp: =DEGREES(angle) • Hàm trả về số đo độ của một góc • Với: angle là số đo của góc tính theo radian • VD: ➢ =DEGREES (4.71239) (3𝜋/2) → 270 ➢ =DEGREES (3.14159) (𝜋) → 180 ➢ =DEGREES (1.570796 (𝜋/2) →90

Trang 36

hthoa@cofer.edu.vn

18. Hàm tính Sin của một số: • Cú pháp: =SIN(number) • Hàm trả về giá trị sine của một số • Number: là số đo góc tính theo radian • VD: =SIN(4.71239) → -1 ( Sine của góc 270°) =SIN(1.570796) → 1 ( Sine của góc 90°)

Trang 37

hthoa@cofer.edu.vn

19. Hàm tính Cosin của một số: • Cú pháp: =COS(number) • Hàm trả về giá trị Cosin của một số • Number: là số đo góc tính theo radian • VD: =COS(3,14159) → -1 ( Cosin của góc 180°) =COS(0) → 1 ( Cosin của góc 0°)

Trang 38

hthoa@cofer.edu.vn

20. Hàm tính Tang của một số: • Cú pháp: =TAN(number) • Hàm trả về giá trị Cosin của một số • Number: là số đo góc tính theo radian • VD:

=TAN(0.785398) → 1 ( Tang của góc 45°)

Trang 39

hthoa@cofer.edu.vn

=TAN(0.523598) → 0.577350 → 2 /2 ( Tang của góc 30°)

21. Hàm tính radian của một góc từ giá trị Sine: • Cú pháp: =ASIN(number) • Hàm trả về giá trị radian 1 góc nằm trong khoảng từ -𝜋/2 đến 𝜋/2 (arcsine) • Number: là Sine một góc • VD: =ASIN(0.5) → 0.523598 (𝜋/6) =ASIN(1) → 1.570796 (𝜋/2)

Trang 40

hthoa@cofer.edu.vn

22. Hàm tính radian của một góc từ giá trị Cosin: • Cú pháp: =ACOS(number) • Hàm trả về giá trị radian 1 góc nằm trong khoảng từ

0 đến 𝜋 (arccosine)

Trang 41

hthoa@cofer.edu.vn

• Number: là Cosin một góc • VD: =ACOS(-0.5) → 2.094395 (2𝜋/3) =ACOS(1) → 0

23. Hàm tính radian của một góc từ giá trị Tang: • Cú pháp: =ATAN(number) • Hàm trả về giá trị radian 1 góc nằm trong khoảng từ

Trang 42

hthoa@cofer.edu.vn

-𝜋/2 đến 𝜋/2 (arctang) • Number: là Tang một góc • VD: =ATAN(1.732050) → 1.0471975 (𝜋/3) =ATAN(1) → 0.7854 (𝜋/4)

1. Hàm đếm ký tự • Cú pháp: =LEN(text) • Hàm trả về số ký tự trong một chuỗi văn bản • Text: chuỗi văn bản cần đếm số ký tự • VD: =LEN(“Việt Nam”) →8 =LEN(A2) →8 (ô A2 chứa chuỗi “Việt Nam”)

Trang 43

hthoa@cofer.edu.vn

2. Hàm LEFT • Cú pháp: =LEFT(text,[num-chars]) • Hàm trích xuất phần bên trái một chuỗi • Với: ➢ Text: chuỗi văn bản cần trích xuất ký tự ➢ Num-chars: số ký tự cần trích ra • VD: =LEFT(“Việt Nam”,4) → Việt =LEFT(A2,7) → Việt Na (ô A2 chứa chuỗi “Việt Nam”)

Trang 44

hthoa@cofer.edu.vn

3. Hàm RIGHT • Cú pháp: =RIGHT(text,[num-chars]) • Hàm trích xuất phần bên phải một chuỗi • Với: ➢ Text: chuỗi văn bản cần trích xuất ký tự ➢ Num-chars: số ký tự cần trích ra • VD: =RIGHT(“Việt Nam”,3) → Nam =RIGHT(A2,7) → iệt Nam (ô A2 chứa chuỗi “Việt Nam”)

Trang 45

hthoa@cofer.edu.vn

4. Hàm MID • Cú pháp: =MID(text,start-num,[num-chars]) • Hàm trích xuất chuỗi con từ 1 chuỗi • Với: ➢ Text: chuỗi văn bản cần trích xuất ký tự ➢ Start: vị trí bắt đầu trích xuất từ bên trái ➢ Num-chars: số ký tự cần trích ra • VD: =MID(“Việt Nam vô địch”,6,3) → Nam =MID(A2,7,5) → am vô (ô A2 chứa chuỗi “Việt Nam vô địch”)

Trang 46

hthoa@cofer.edu.vn

5. Hàm FIND • Cú pháp: =FIND(find-text,within-text,[start-num]) • Hàm trả về vị trí của 1 chuỗi con trong chuỗi văn bản • Với: ➢ Find-text: chuỗi con cần tìm vị trí ➢ Within-text: chuỗi văn bản chứa chuỗi con cần tìm ➢ Start-num: vị trí bắt đầu tìm • VD: =FIND(“Nam”,“Việt Nam vô địch”,1) → 6 =FIND(“vô địch”,A2,1) → 10 (ô A2 chứa chuỗi “Việt Nam vô địch”)

Trang 47

hthoa@cofer.edu.vn

6. Hàm TRIM • Cú pháp: =TRIM(text) • Hàm xóa những khoảng trắng vô ích trong văn bản • Text: chuỗi văn bản • VD: =TRIM(“ Việt Nam”) → Việt Nam =TRIM(A2) → Việt Nam

Trang 48

hthoa@cofer.edu.vn

7. Hàm LOWER • Cú pháp: =LOWER(text) • Hàm chuyển text thành chữ in thường • Text: chuỗi văn bản • VD: =LOWER(“Học Hành”) → học hành

Trang 49

hthoa@cofer.edu.vn

8. Hàm UPPER • Cú pháp: =UPPER(text) • Hàm chuyển text thành chữ in hoa • Text: chuỗi văn bản • VD: =UPPER(“Học Hành”) → HỌC HÀNH

Trang 50

hthoa@cofer.edu.vn

9. Hàm PROPER • Cú pháp: =PROPER(text) • Hàm chuyển các chữ cái đầu thành in hoa, chữ khác

in thường

Trang 51

hthoa@cofer.edu.vn

• Text: chuỗi văn bản • VD: =PROPER(“họC hÀnh”) → Học Hành

10.Hàm SUBSTITUTE • Cú pháp: SUBSTITUTE(text, old_text, new_text

[,instance_num])

• Hàm thay thế một chuỗi bằng chuỗi khác ➢ text: chuỗi văn bản gốc, cần được xử lý ➢ old_text: chuỗi văn bản cần được thay thế ➢ new_text: chuỗi văn bản sẽ thay thế vào ➢ instance_num: số lần thay thế old_text bằng

Trang 52

hthoa@cofer.edu.vn

new_text

Hàm SUBSTITUTE • Ví dụ: đế thay số 2007 bằng 2008 trong câu

Expense Budget for 2007

• Dùng công thức như sau:

=SUBSTITUTE("Expense Budget for 2007", "2007", "2008") → Expense Budget for 2008

Trang 53

hthoa@cofer.edu.vn

11. Hàm REPLACE • Cú pháp: REPLACE(old_text, start_num, num_chars,

new_text)

• Hàm thay thế một chuỗi bằng chuỗi khác với số ký tự

được chỉ định ➢ old_text: chuỗi văn bản cần được xử lý ➢ start_num: vị trí bắt đầu chuỗi cần thay thế, tính từ bên

trái sang

➢ num_chars: số ký tự của chuỗi cần được thay thế ➢ new_text: chuỗi văn bản sẽ thay thế cho số ký tự đã chọn

Trang 54

hthoa@cofer.edu.vn

12.Hàm REPLACE • VD: với ô A1= “Chi phí ngân sách năm 2015”, để thay

số 2015 bằng số mới là 2014 ta có công thức:

Trang 55

hthoa@cofer.edu.vn

REPLACE(A1,FIND(“2015”,A1),LEN(“2015”),“2014”)

Trang 56

hthoa@cofer.edu.vn

13.Hàm CONCATENATE • Cú pháp: CONCATENATE(text 1, text 2,….) • Nối nhiều chuỗi lại với nhau. • VD: CONCATENATE(“Giải pháp Excel”; “ ”;“-”;“Công cụ tuyệt vời của bạn” → Giải pháp Excel –Công cụ tuyệt vời của bạn

• Những quy định khi nhập dữ liệu ngày tháng:

➢ Giá trị năm có giá trị từ 1900 đến 9999 ➢ Nếu nhập số năm với 2 con số thì:

o Nếu <30 thì cộng vào 2000 o Nếu >= 30 và <100 thì cộng vào 1900

➢ Nhập không đúng sẽ chuyển thành dạng text ➢ Tùy theo định dạng hệ thống mà ngày tháng nhập

Trang 57

hthoa@cofer.edu.vn

vào có các dạng hiển thị khác nhau

• Những quy định khi nhập dữ liệu ngày tháng:

➢ 24h ngày 31/12/1899 được chọn làm mốc tính

toán các giá trị ngày giờ

➢ Tất cả giá trị ngày giờ đều được quy đổi thành 1 giá

trị số để tính toán

➢ Giá trị số là số ngày cách mốc quy ước 31/12/1899 ➢ VD: 06/12/1999 quy ra 36500 ngày 28/05/2005 quy ra 38500 ngày

Trang 58

hthoa@cofer.edu.vn

1. Hàm DATE • Cú pháp: =DATE(year,month,day) • Hàm trả về giá trị ngày tháng • VD: =DATE(2008,1,1) → 01/01/2008 =DATE(108,1,1) → 01/01/2008 =DATE(2008,12,32) → 01/01/2009 =DATE(08,14,01) → 01/02/2009

Trang 59

hthoa@cofer.edu.vn

2. Hàm NOW • Cú pháp: =NOW() • Hàm trả về giá trị ngày tháng năm hiện hành • Tùy theo kiểu định dạng mà kết quả thể hiện khác

nhau

• VD: =NOW() → 09/16/14 với định dạng mm/dd/yy =NOW() → 16/09/2014 với định dạng dd/mm/yyyy

Trang 60

hthoa@cofer.edu.vn

3. Hàm DAY • Cú pháp: =DAY(serial-number) • Hàm trả về giá trị ngày trong biểu thức ngày tháng • Serial-number: là biểu thức ngày tháng • VD: =DAY(“01/02/2008”) → 1 =DAY(A2) → 31 (ô A2 chứa biểu thức 31/01) =DAY(39000) → 10

Trang 61

hthoa@cofer.edu.vn

4. Hàm MONTH • Cú pháp: =MONTH(serial-number) • Hàm trả về giá trị tháng trong biểu thức ngày tháng • Serial-number: là biểu thức ngày tháng • VD: =MONTH(“01/02/2008”) → 2 =MONTH(A2) → 1 (ô A2 chứa biểu thức 31/01) =MONTH(39000) → 10

Trang 62

hthoa@cofer.edu.vn

5. Hàm YEAR • Cú pháp: =YEAR(serial-number) • Hàm trả về giá trị năm trong biểu thức ngày tháng • Serial-number: là biểu thức ngày tháng • VD: =YEAR(“01/02/2008”) → 2008 =YEAR(A2) → 2014 (ô A2 chứa biểu thức 31/01) =YEAR(39000) → 2006

Trang 63

hthoa@cofer.edu.vn

6. Hàm HOUR • Cú pháp: =HOUR(serial-number) • Hàm trả về giá trị là số giờ lẻ chưa tròn 1 ngày (từ 0

đến 23)

• Serial-number: là biểu thức thời gian hoặc 1 số nào

đó chỉ số ngày (0

Trang 64

hthoa@cofer.edu.vn

• VD: =HOUR(“10:30 AM”) → 10 =HOUR(“10:30 PM”) → 22 =HOUR(0.5) →12

7. Hàm MINUTE • Cú pháp: =MINUTE(serial-number) • Hàm trả về giá trị là số phút chưa tròn 1 giờ (từ 0

đến 59)

Trang 65

hthoa@cofer.edu.vn

• Serial-number: là biểu thức thời gian • VD: =MINUTE(“10:30 AM”) → 30 =MINUTE(“10:45 PM”) → 45 =MINUTE(0.32) →40

8. Hàm SECOND • Cú pháp: =SECOND(serial-number) • Hàm trả về giá trị là số giây chưa tròn 1 phút (từ 0

đến 59)

Trang 66

hthoa@cofer.edu.vn

• Serial-number: là biểu thức thời gian • VD: =SECOND(0.32) →48

9. Hàm TIME • Cú pháp: =TIME(hour,minute,second) • Hàm trả về giá trị biểu thức thời gian • VD: =TIME(13,40,48)→ 1:40 PM =TIME(7,40,48) → 7:40 AM

Trang 67

hthoa@cofer.edu.vn

10. Hàm DATEDIF • Cú pháp: =DATEDIF(firstdate,enddate,option) • Hàm trả về một giá trị, là số ngày, số tháng hay số năm

giữa hai khoảng thời gian theo tùy chọn.

• Với:

- firstdate: là Ngày bắt đầu của khoảng thời gian cần tính toán - Enddate: là Ngày kết thúc của khoảng thời gian cần tính toán - Option: là tùy chọn, xác định kết quả tính toán sẽ trả về trong công thức:

Trang 68

hthoa@cofer.edu.vn

10. Hàm DATEDIF

+ "d": Hàm sẽ trả về số ngày giữa hai khoảng thời gian. + "m": Hàm sẽ trả về số tháng (chỉ lần phần nguyên) giữa

hai khoảng thời gian.

+ "y": Hàm sẽ trả về số năm (chỉ lần phần nguyên) giữa

hai khoảng thời gian.

+ "yd": Hàm sẽ trả về số ngày lẻ của năm (số ngày chưa

tròn năm) giữa hai khoảng thời gian.

+ "ym": Hàm sẽ trả về số tháng lẻ của năm (số tháng chưa

tròn năm) giữa hai khoảng thời gian.

+ "md": Hàm sẽ trả về số ngày lẻ của tháng (số ngày chưa

tròn tháng) giữa hai khoảng thời gian.

Trang 69

hthoa@cofer.edu.vn

11.Hàm WEEKDAY • Cú pháp: =WEEKDAY(serial_number, return_type) • Hàm trả về một giá trị, là số thứ tự của Ngày trong

tuần. • Với: - Serial_number : là một biểu thức thời gian,

Serial_number có thể là Number hay Value_date

- return_type : là tùy chọn để xác định kiểu giá trị sẽ

Trang 70

hthoa@cofer.edu.vn

trả về trong công thức

11.Hàm WEEKDAY - return_type : + 1 hoặc để trống : Ngày chủ nhật được xem là 1,

Ngày thứ bảy là 7.

Trang 71

hthoa@cofer.edu.vn

+ 2 : Ngày thứ hai được xem là 1, Ngày chủ nhật là 7. + 3 : Ngày thứ hai được xem là 0, Ngày chủ nhật là 6.

1. Hàm AND • Cú pháp: =AND(logical 1, logical 2…) • Hàm trả về kết quả đúng (TRUE) nếu tất cả các đối

số đều đúng

• Hàm trả về kết quả sai (FALSE) nếu ít nhất 1 đối số

sai.

Trang 72

hthoa@cofer.edu.vn

• Logical: là các biểu thức điều kiện • VD: =AND(A2>=20,A2<26)

2. Hàm OR • Cú pháp: =OR(logical 1, logical 2…) • Hàm trả về kết quả sai (FALSE) nếu tất cả các đối số

đều sai,

• Trả về kết quả đúng (TRUE) nếu ít nhất 1 đối số

đúng.

Trang 73

hthoa@cofer.edu.vn

• Logical là các biểu thức điều kiện • VD: =OR(A2>=20,B2<10)

3. Hàm điều kiện • Cú pháp: =IF(logical_test, value_if_true,

value_if_false)

• Hàm trả về kết quả là value_if_true nếu logical_test

có giá trị TRUE

• Hàm trả về kết quả là value_if_false nếu logical_test

có giá tri FALSE

Trang 74

hthoa@cofer.edu.vn

• VD: Công thứ ô C2 là: =IF(B2>=5,“đậu”,“rớt”)

3. Hàm điều kiện • Trong hàm IF, khi có nhiều điều kiện thì phải liên kết các điều

kiện này bằng hàm AND hoặc hàm OR

• Công thức tính kết quả tại ô F5: =IF(AND(D5+E5>=10,D5<>0,E5<>0),"Đậu","Rớt") =IF(OR(D5+E5<10,D5=0,E5=0),“Rớt”,“Đậu”)

Trang 75

hthoa@cofer.edu.vn

• Hàm IF có thể được viết lồng vào nhau khi có nhiều kết

quả trong phép tính

• Công thức tại ô E5:

Trang 76

hthoa@cofer.edu.vn

=IF(D5>=5,”Đậu”,IF(D5<=3,”Học lại”,”Thi lại”))

1. Hàm dò tìm theo cột • Cú pháp: =VLOOKUP(lookup_value,table_array,col_index_num,option_l ookup) • Hàm trả về giá trị của ô nằm trên cột nào đó thỏa mãn

điều kiện dò tìm

• Với: ➢ lookup_value: giá trị dò tìm ➢ Table_array: là bảng chứa giá trị cần để dò tìm ➢ Col_index: số thứ tự cột từ bên trái sang trong bảng ➢ Option_lookup: xác định kiểu dò tìm

Trang 77

hthoa@cofer.edu.vn

1. Hàm dò tìm theo cột • Option_lookup:

➢ Sắp xếp vùng đối chiếu chỉ nhận giá trị logic 0 hoặc 1,

nếu bỏ qua thì nhận giá trị 1

➢ Nếu để giá trị 0: dò tìm chính xác, lấy giá trị đầu tiên

tìm được, nếu không tìm thấy trả về #N/A

➢ Nếu để giá trị 1: kiểu dò tìm tương đối, nếu không

tìm thấy trả về giá trị nhỏ hơn gần nhất với giá trị cần dò tìm

Trang 78

hthoa@cofer.edu.vn

1. Hàm dò tìm theo cột • Ví dụ:

➢ Tìm điểm LT của sinh viên Cúc:

=Vlookup(“Cúc”,A1:C5,2,0)

➢ Tìm điểm TH của sinh viên Lan:

=Vlookup(“Lan”,A1:C5,3,0)

Trang 79

hthoa@cofer.edu.vn

2. Hàm dò tìm theo hàng • Cú pháp:

=HLOOKUP(lookup_value,table_array,row_index_num,optio n_lookup)

• Hàm trả về giá trị của ô nằm trên HÀNG nào đó thỏa mãn

điều kiện dò tìm

• Với:

➢ lookup_value: giá trị dò tìm ➢ Table_array: là bảng chứa giá trị cần để dò tìm ➢ row_index: số thứ tự hàng từ trên xuống trong bảng ➢ Option_lookup: xác định kiểu dò tìm

Trang 80

hthoa@cofer.edu.vn

2. Hàm dò tìm theo hàng • Ví dụ:

➢ Tìm điểm LT của sinh viên Cúc: =Hlookup(“Điểm LT”,A1:C5,4,0) ➢ Tìm điểm TH của sinh viên Lan: =Hlookup(“Điểm LT”,A1:C5,3,0)

Trang 81

hthoa@cofer.edu.vn

3. Hàm MATCH • Cú pháp:

=MATCH(lookup_value,lookup_array,match_type) • Hàm trả về vị trí của giá trị dò tìm trong cột (hàng) • Với:

➢ Lookup_value: là giá trị dùng để dò tìm, có thể là một số, một chuỗi, một giá trị logic hay một tham chiếu. ➢ Lookup_array: là vùng dò tìm, có thể là một cột hoặc

một dòng, hoặc một mảng các giá trị.

Trang 82

hthoa@cofer.edu.vn

➢ Match_type: là tùy chọn để xác định kiểu dò tìm.

3. Hàm MATCH • Match-type: nhận 3 giá trị tương ứng

➢ =0: dò tìm chính xác ➢ =1 (hoặc để trống): tìm vị trí của giá trị lớn nhất

nhỏ hơn giá trị cần tìm.

Trang 83

hthoa@cofer.edu.vn

➢ =-1: Tìm giá trị nhỏ nhất lớn hơn giá trị cần tìm

4. Hàm INDEX • Cú pháp: =INDEX(array,row_num,col_num) • Hàm trả về giá trị trong một bảng (mảng) • Với:

➢ Array: là một dãy ô hay một hằng mảng. ➢ Row_num: là số chỉ dòng của giá trị trong mảng cần

trả về.

➢ Col_num: là số chỉ cột của giá trị trong mảng cần trả

Trang 84

hthoa@cofer.edu.vn

về.

1. Hàm TEXT: • Cú pháp: TEXT(number,format-text) • Hàm chuyển đổi một số thành dạng văn bản (text) theo

định dạng được chỉ định

• Với:

➢ Number: là số cần chuyển đổi sang dạng text ➢ Format-text: kiểu định dạng số hoạc ngày tháng năm

• Một số kiểu định dạng:

➢ Định dạng số: ###,000 ; #,##0.00….. ➢ Định dạng ngày tháng: dd/MM/yy; d/m/yyyy;

dd/MM/yyyy hh:mm:ss….

Trang 85

hthoa@cofer.edu.vn

1. Hàm TEXT:

➢ TEXT(123,“$#,##0.00”) → $123.00 ➢ TEXT(1234,“#,##0.00”) → 1,234.00 ➢ TEXT(12345,“###,000”) → 12,345 ➢ TEXT(23/02/1988,“mm/dd/yy”) → 02/23/88 ➢ TEXT(23/02/1988,“dd/mm/yy hh:mm:ss”) →

Trang 86

hthoa@cofer.edu.vn

23/02/88 00:00

2. Hàm VALUE: • Cú pháp: VALUE(text) • Hàm chuyển một chuỗi đại diện cho một số thành

kiểu số

Trang 87

hthoa@cofer.edu.vn

• Text: phải là dạng số hoặc ngày tháng • VD: =VALUE(“$123.00”) →123 =VALUE(“01/15/14”) → 41654

1. Hàm SUMIF: • Cú pháp: SUMIF(range,criteria,sum-range) • Hàm trả về tổng của các ô trong một vùng thỏa mãn

điều kiện cho trước

Trang 88

hthoa@cofer.edu.vn

• Với: ➢ Range: dãy các ô có chứa điều kiện ➢ Criteria: điều kiện để tính tổng ➢ Sum-range: vùng chứa các ô cần tính tổng

2. Hàm COUNTIF: • Cú pháp: COUNTIF(range,criteria) • Hàm trả về số lượng các ô trong vùng thỏa mãn điều

kiện cho trước

Trang 89

hthoa@cofer.edu.vn

• Với: ➢ Range: dãy các ô để đếm ➢ Criteria: điều kiện để đếm

3. Hàm AVERAGEIF: • Cú pháp: AVERAGEIF(range,criteria,average-range) • Hàm trả về giá trị trung bình của các ô trong một vùng

thỏa mãn điều kiện cho trước

• Với: ➢ Range: dãy các ô có chứa điều kiện ➢ Criteria: điều kiện để tính giá trị trung bình ➢ Average-range: vùng chứa các ô cần tính giá trị trung

Trang 90

hthoa@cofer.edu.vn

bình

4. Hàm SUMIFS: • Cú pháp: = SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) • Hàm trả về tổng các ô trong một vùng thỏa nhiều

điều kiện cho trước

Trang 91

hthoa@cofer.edu.vn

• Với: ➢ Sum-range: Vùng chứa các ô cần tính tổng ➢ Criteria_range1: Vùng chứa điều kiện 1 ➢ Criteria1: Điều kiện 1

5. Hàm COUNTIFS: • Cú pháp: = COUNTIFS(range1, criteria1, range2, criteria2, ...) • Hàm đếm các ô trong vùng thỏa nhiều điều kiện cho

trước.

Trang 92

hthoa@cofer.edu.vn

• Với: ➢ Range 1: Vùng chứa điều kiện 1 ➢ Criteria1: Điều kiện 1

6. Hàm AVERAGEIFS: • Cú pháp: = AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) • Hàm trả về giá trị trung bình của các ô thỏa mãn

nhiều điều kiện cho trước.

Trang 93

hthoa@cofer.edu.vn

• Với: ➢ Average_range : Vùng cần tính giá trị trung bình. ➢ Criteria_range 1: Vùng chứa điều kiện 1. ➢ Criteria 1: Điều kiện 1

• Sau khi đánh dấu khối ô bảng tính cần định dạng, ta thực hiện lệnh [Menu] Home/Format/Format Cells (hoặc tổ hợp phím Ctrl -1) để tạo định dạng tổng thể. Excel xuất hiện hộp hội thoại bao gồm các thẻ định dạng riêng biệt:

Trang 94

hthoa@cofer.edu.vn

Trang 95

hthoa@cofer.edu.vn

• Định dạng số: Trong hộp Category chọn kiểu dữ liệu cần trình bày và chọn tiếp các thông số tương ứng với kiểu dữ liệu đã chọn: ➢ General: định dạng chung. Với kiểu định dạng này, dữ liệu kiểu ký tự gõ vào sẽ được canh hàng bên trái và dữ liệu kiểu số sau khi gõ vào bảng tính sẽ được canh hàng bên phải ô.

➢ Number: dùng để định dạng các số thông thường.

Chọn tiếp các thông số sau:

Trang 96

hthoa@cofer.edu.vn

• Định dạng số:

✓ Decimal places: khai báo số chữ số sau dấu

chấm thập phân.

✓ Use 1000 Separators(,): click chọn khi muốn

dùng dấu phẩy để phân cách hàng nghìn

✓ Negative numbers: chọn cách định dạng số âm.

Trang 97

hthoa@cofer.edu.vn

➢ Currency: Chọn cách hiện ký hiệu tiền tệ. ➢ Date: Chọn cách hiện giá trị ngày tháng.

• Định dạng số: ➢ Percentage: Chọn cách hiện ký hiệu phần trăm. ➢ Fraction: Chọn cách hiện giá trị phân số. ➢ Scientific: Chọn cách hiện số dưới dạng khoa học. ➢ Text: định dạng số theo kiểu ký tự. ➢ Special: định dạng theo mã vùng và số điện thoại ➢ Custom: tuỳ biến theo khuôn dạng được gõ trong

Trang 98

hthoa@cofer.edu.vn

hộp Type

• Excel cho phép ta hiển thị ngày tháng theo nhiều

Trang 99

hthoa@cofer.edu.vn

kiểu định dạng khác nhau. Ta có thể sử dụng những mẫu định dạng đã có sẵn hoặc có thể tự định nghĩa những kiểu định dạng mới theo ý thích. ➢ Sử dụng những mẫu định dạng có sẵn: Ở khung Category chọn mục Date, và chọn kiểu hiển thị bên khung Type. Excel thường hiển thị ngày tháng theo dạng tổng quát m/d/yy (tháng/ngày/năm)

➢ Sử dụng những định dạng tự định nghĩa: Nếu không vừa ý với các dạng có sẵn, ta có thể tạo ra một dạng hiển thị riêng bằng cách sau:

Trang 100

hthoa@cofer.edu.vn

Chọn mục Start/Control Panel xuất hiện hộp thoại Control Panel chọn vào biểu tượng xuất hiện hộp thoại sau:

Trang 101

hthoa@cofer.edu.vn

• Chọn vào nút

Trang 102

hthoa@cofer.edu.vn

Additional settings xuất hiện hộp thoại tiếp theo chọn vào tab Date thì hộp thoại có dạng sau:

• Trong khung Short date (định nghĩa kiểu thể hiện

kiểu ngày rút gọn)

➢ Short date sample: ví dụ về cách thể hiện định

dạng ngày đã được định nghĩa.

➢ Short date format: kiểu ngày đang được định

nghĩa, ta có thể click chọn vào đây để chọn kiểu định nghĩa mới hoặc có thể gõ trực tiếp vào khung này.

➢ Date separator: Dấu phân cách giữa ngày, tháng và

Trang 103

hthoa@cofer.edu.vn

năm (dấu - hoặc /)

• Trong khung Long date (định nghĩa kiểu thể hiện

kiểu ngày chi tiết) ➢ Long date sample: ví dụ về cách thể hiện định

dạng ngày đã được định nghĩa.

➢ Long date format: kiểu ngày đang được định

nghĩa, ta có thể click chọn vào đây để chọn kiểu định nghĩa mới hoặc có thể gõ trực tiếp vào khung này.

Trang 104

hthoa@cofer.edu.vn

Trang 105

hthoa@cofer.edu.vn

• Mục Horizontal: Dùng để canh chỉnh theo chiều ngang

➢ General: Trở về cách sắp xếp ngầm định ➢ Left: Canh trái các ô. ➢ Center: Canh giữa các ô. ➢ Right: Canh phải các ô. ➢ Justify: Canh đều hai bên các ô.

• Mục Vertical: Dùng để canh chỉnh theo chiều dọc

➢ Top: Ký tự hiện sát đỉnh ô ➢ Center: Ký tự hiện giữa ô. ➢ Bottom: Ký tự hiện ở đáy ô.

Trang 106

hthoa@cofer.edu.vn

• Mục Text control:Điều kiển dữ liệu trong khối ô

được chọn ➢ Wrap text: Khi click chọn thì độ rộng cột sẽ cố định, dữ liệu nhập vào tự động xuống hàng.

➢ Shrink to fit: khi click chọn vào mục này thì dữ liệu

trọng ô sẽ co lại vừa bằng kích thước của ô.

➢ Merge cells: Khi click chọn thì Excel sẽ sát nhập

các ô đã chọn lại thành một ô.

• Hộp Orientation: Dùng để chỉnh độ nghiêng của dữ

liệu

Trang 107

hthoa@cofer.edu.vn

Mọi chi tiết xin vui lòng liên hệ:

ThS. Hoàng Thanh Hòa

Email: hthoa@cofer.edu.vn Website: https://sites.google.com/site/thanhhoakhcb/ Di động: 0396935167