Bài 5: Biểu thức và hàm 5.1. Biểu thức số học.
5.1.1. Khái niệm biểu thức (expression) và ứng dụng của biểu thức
Biểu thức là một tập hợp các toán tử và toán hạng theo quy tắc số học bình thường.
Biểu thức trả về kết quả là sự tính toán kết hợp giữa các toán tử và toán hạng. Biểu thức
trong Excel có chứa các địa chỉ ô liên quan. Mỗi địa chỉ ô này chúng ta coi đó là một toán
hạng, ngoài ra trong biểu thức có thể chứa các hàm (function), mỗi hàm này cũng là một
toán hạng. Khi sử dụng địa chỉ ô liên quan, chúng ta nên chú ý đến địa chỉ tương đối, tuyệt
đối của ô trong biểu thức. Với địa chỉ ô tương đối, các quy tắc khi sao chép công thức được
thực hiện, còn địa chỉ ô tuyệt đối thì giữ nguyên.
Công thức: Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các
công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức
để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ
tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại
nhiều lần. Vậy công thức có các thành phần gì? Công thức trong Excel được nhận dạng là
do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.
Các toán tử trong công thức: Toán tử Chức năng Ví dụ Kết quả + Cộng =3+3 3 cộng 3 là 6 - Trừ =45-4 45 trừ 4 còn 41 * Nhân =150*.05 150 nhân 0.50 thành 7.5 / Chia =3/3 3 chia 3 là 1
2 lũy thừa 4 thành 16 Lấy căn bậc 4 ^ Lũy thừa =2^4 =16^(1/4) của 16 thành 2 26
Nối chuỗi “Lê” và “Thanh” lại thành & Nối chuỗi =”Lê” & “Thanh” “Lê Thanh”
Ví dụ ô A1=3, ô B1=6 Kết quả: = Bằng =A1=B1 FALSE
Ví dụ ô A1=3, ô B1=6 Kết quả: > Lớn hơn =A1>B1 FALSE
Ví dụ ô A1=3, ô B1=6 Kết quả: < Nhỏ hơn =A1TRUE Lớn hơn hoặc
Ví dụ ô A1=3, ô B1=6 Kết quả: >= =A1>=B1 bằng FALSE Nhỏ hơn hoặc
Ví dụ ô A1=3, ô B1=6 Kết quả: <= =A1<=B1 bằng TRUE
Ví dụ ô A1=3, ô B1=6 Kết quả: <> Khác =A1<>B1 TRUE Dấu cách các tham , =Sum(A1,B1)
Ví dụ ô A1=3, ô B1=6 Kết quả: 9 chiếu :
Tham chiếu mảng =Sum(A1:B1)
Ví dụ ô A1=3, ô B1=6 Kết quả: 9 khoảng
Trả về các ô giao =B1:B6 A3:D3
Trả về giá trị của ô trắng giữa 2 vùng
Thứ tự ưu tiên của các toán tử: Toán tử Mô tả Ưu tiên
: (hai chấm) (1 khoảng trắng) , (dấu phẩy) Toán tử tham chiếu 1 – Số âm (ví dụ –1) 2 % Phần trăm 3 27 ^ Lũy thừa 4 * và / Nhân và chia 5 + và – Cộng và trừ 6 & Nối chuỗi 7
= < > <= >= <> So sánh 8
5.1.2. Tạo biểu thức số học đơn giản bằng cách sử dụng các phép tính số học (cộng, trừ, nhân, chia)
Dưới đây là một số biểu thức đơn giản. Chúng ta sẽ gặp lại rất nhiều các biểu thức
chứa trong các công thức ở những phần tiếp theo. - (A2+A3)^2 - MOD(3,2)*$A$4 - IF(AND(A2,B4),TRUE,FALSE)
5.1.3. Hiểu các lỗi gặp phải khi sử dụng biểu thức
Excel có 7 giá trị lỗi của công thức gồm: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! và #VALUE!
Lỗi #DIV/0!: là do công thức thực hiện phép chia cho không. Lỗi #DIV/0! có thể
xuất hiện trong các tình huống sau:
- Các tham chiếu dẫn đến một ô rỗng hoặc một ô chứa giá trị bằng 0. Bạn hãy
kiểm tra các ô được tham chiếu trực tiếp và gián tiếp trong công thức để tìm ra nguyên nhân.
- Đối với một bảng tính có yêu cầu người dùng nhập dữ liệu vào. Nếu công thức
của bạn đòi hỏi một phép chia với một trong các ô rỗng tạm thời, nó sẽ hiển thị
#DIV/0! dưới dạng kết quả. Và điều này có thể gây khó chịu cho người dùng.
Bạn có thể giải quyết vấn đề này bằng cách yêu cầu Excel không thực hiện phép
chia nếu ô được sử dụng làm số chia là 0. Bạn có thể thực hiện điều này với hàm
IF(logical_test, value_if_true, value_if_false). Ví dụ: bạn xem công thức =
A2/B2; để khắc phục lỗi #DIV/0! xuất hiện khi ô B2 là rỗng (hoặc bằng 0) bạn
nên sửa công thức trên như sau =IF(B2=0; “”; A2/B2).
- Nhập một đối số không thích hợp trong một số hàm. Ví dụ: hàm Mod(number,
divisor) trả về #DIV/0! nếu đối số thứ hai là 0.
Lỗi #N/A: là do công thức không thể trả về một kết quả hợp lệ. Lỗi #N/A có thể
xuất hiện khi bạn sử dụng một đối số không thích hợp (hoặc nếu bạn bỏ qua một đối số
bắt buộc) trong một hàm. Ví dụ: hàm VLOOKUP(lookup_value; table_array; 28
col_index_num; range_lookup) sẽ trả về giá trị #N/A nếu lookup_value không có trong
table_array với range_lookup là 0; để khắc phục lỗi #N/A trong trường hợp này bạn có thể dùng hàm IFERROR(VLOOKUP(lookup_value; table_array; col_index_num; range_lookup); “”).
Lỗi #NAME?: Lỗi #NAME? là do Excel không thể nhận biết một cái tên mà bạn
sử dụng trong công thức. Lỗi #NAME? có thể xuất hiện trong các tình huống sau:
- Nhập sai chính tả (nhập không đúng) một tên dãy.
- Dùng một tên dãy mà bạn chưa định nghĩa.
- Nhập sai chính tả (nhập không đúng) một tên hàm.
- Sử dụng một hàm thuộc một Add-in chưa cài đặt.
- Sử dụng một chuỗi mà không đặt chúng trong một cặp dấu nháy kép
- Nhập một tham chiếu dãy mà vô tình bỏ sót dấu hai chấm (:)
Đây đều là các lỗi về cú pháp, do đó sửa chúng nghĩa là phải kiểm tra kỹ lại công
thức, hiệu chỉnh tên dãy hoặc tên hàm bị nhập sai, hoặc chèn thêm các dấu nháy kép, dấu
hai chấm bị thiếu… Bên cạnh đó, phải đảm bảo rằng đã định nghĩa các tên dãy được sử
dụng và đã cài đặt các Add-in thích hợp cho những hàm mà bạn dùng.
Lỗi #NULL!: là một trường hợp lỗi rất riêng biệt trong Excel. Lỗi #NULL! xuất
hiện khi bạn dùng toán tử giao (một khoảng trắng) trên hai dãy không giao nhau (không có
ô chung). Ví dụ: dãy A1:B2 và dãy C3:D4 không có ô chung nào cả nên công thức
=SUM(A1:B2 C3:D4) sẽ trả về #NULL! dưới dạng kết quả. Bạn hãy kiểm tra lại các tọa
độ dãy để đảm bảo rằng chúng chính xác. Ngoài ra, việc kiểm tra còn để xem có phải
nguyên nhân làm cho hai dãy trong công thức của bạn không còn giao nhau có phải là một
trong hai dãy đã bị di chuyển hay không.
Lỗi #NUM!: là do có vấn đề gì đó với một con số trong công thức. Lỗi #NUM! có
thể xuất hiện trong các tình huống sau:
- Nhập đối số không hợp lệ trong một hàm toán học hay một hàm lượng giác.
- Sử dụng sự lặp đi lặp lại mà Excel không thể tính được kết quả.
Lỗi #REF!: là do công thức của bạn chứa một tham chiếu ô không hợp lệ. Lỗi
#REF! có thể xuất hiện trong các tình huống sau:
- Bạn xóa một ô mà công thức tham chiếu đến. Bạn cần phải trả lại ô đó vào bảng
tính bằng lệnh Undo hoặc điều chỉnh tham chiếu của công thức.
- Công thức của bạn tham chiếu đến một địa chỉ ô không hiện hữu như là ô B0.
Điều này có thể xảy ra nếu bạn cắt hoặc sao chép một công thức đang sử dụng
các tham chiếu tương đối và dán nó theo một cách nào đó tạo ra một địa chỉ ô
không hợp lệ. Ví dụ: công thức của bạn đang tham chiếu đến ô B1; nếu bạn cắt
hay sao chép ô chứa công thức này và dán nó cao hơn 1 hàng thì khi đó tham
chiếu B1 trở thành tham chiếu B0; đây là một tham chiếu không hiện hữu. 29
Lỗi #VALUE!: là do bạn đã sử dụng một đối số không thích hợp trong một hàm.
Lỗi #VALUE! có thể xuất hiện trong các tình huống sau:
- Bạn sử dụng nhầm kiểu dữ liệu. Ví dụ: lẽ ra phải nhập một giá trị số, bạn lại
nhập vào một giá trị chuỗi hay lẽ ra phải sử dụng một ô đơn lẻ, bạn lại sử dụng một tham chiếu dãy.
- Bạn sử dụng một giá trị vượt quá khả năng xử lý của Excel. 5.2. Hàm
Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng
nào đó. Việc sử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời
gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm
nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều
đối số, và các đối số có thể là bắt buộc hoặc tự chọn.
Giả sử cần xác định giá trị lớn nhất trong một dãy nào đó. Một công thức không
thể cho bạn kết quả mà không sử dụng hàm. Chúng ta sẽ sử dụng công thức mà chứa hàm
MAX để trả lại kết quả giá trị lớn nhất trong dãy từ B3:B8 là =MAX(A1:D100)
5.2.1. Ý nghĩa và cách sử dụng các hàm thống kê Hàm COUNT:
- Công dụng: Đếm số ô trong danh sách
- Cú pháp: COUNT(value1, value2, …)
- Các tham số: value1, value2,…: các giá trị (số, chuỗi, logic,…)
- Ví dụ: =COUNT(A15:A22) trả về kết quả là 3 Hàm COUNTA:
- Công dụng: Đếm số ô có chứa giá trị (không rỗng) trong danh sách
- Cú pháp: COUNTA(value1, value2, …)
- Tham số: value1, value2,…: các giá trị (số, chuỗi, logic,…);
- Ví dụ: COUNTA(A15:A22) trả về kết quả là 6 30 Hàm COUNTBLANK:
- Công dụng: Đếm các ô rỗng trong một vùng
- Cú pháp: COUNTBLANK(range)
- Tham số: range: Vùng cần đếm
- Ví dụ: COUNTBLANK(A15:A22) trả về kết quả là 2 Hàm MAX:
- Công dụng: Trả ra số lớn nhất trong danh sách
- Cú pháp: MAX (value1, value2, …)
- Tham số: value1, value2,…: các giá trị số.
- Ví dụ: Max(1,5,9,4,9,2,4,5) trả về kết quả là 9 Hàm MIN:
- Công dụng: Trả ra số nhỏ nhất trong danh sách
- Cú pháp: MIN(value1, value2, …)
- Tham số: value1, value2,…: các giá trị số.
- Ví dụ: Min(1,5,9,4,9,2,4,5) trả về kết quả là 1 Hàm AVERAGE:
- Công dụng: Tính trung bình cộng các giá trị trong danh sách
- Cú Pháp: AVERAGE(number1, number2, …)
- Tham số: number1, number2, …: các giá trị số
- Ví dụ: AVERRAGE(1,2,3,4,5,6,7) trả về kết quả là 4 Hàm AVERAGEA:
- Công dụng: Tính trung bình cộng của các giá trị, bao gồm cả những giá trị logic
(TRUE mang giá trị 1 và FALSE là 0).
- Cú pháp: AVERAGEA (number1, number2, …)
- Tham số: number1, number2, …: các giá trị số
- Ví dụ: =AVERAGEA(1,1,TRUE,TRUE,FALSE) trả về kết quả là 0,8 Hàm AVERAGEIF
- Công dụng: Tính trung bình cộng của các giá trị trong danh sách theo một điều kiện
- Cú pháp: AVERAGEIF(range, criteria, [avegare_rang]) :
- Tham số: range: là vùng xét điều kiện; criteria: là giá trị để so sánh với các giá
trị trong vùng điều kiện; [avegare_rang] : vùng tính trung bình
- Ví dụ: Tính trung bình Đơn Giá cùa Tên Hàng là Sắt Phi 6;
=AVERAGEIF(B25:B36,”Sắt Phi 6″,E25:E36) trả về kết quả là 15833.33333 Hàm AVERAGEIFS: 31
- Công dụng: Tính trung bình cộng của các giá trị trong danh sách theo nhiều điều kiện
- Cú pháp: AVERAGEIFS(average_range, criteria_range1, criteria1,
criteria_range2, criteria2, …)
- Tham số: average_range là vùng tính trung bình; criteria_range1: vùng xét điều
kiện thứ nhất; criteria1: giá trị điều điện thứ nhất; criteria_range2: vùng xét điều
kiện thứ hai; criteria2: giá trị điều điện thứ hai
- Ví dụ: Tính trung bình Đơn Giá cùa Tên Hàng là Sắt Phi 6 thuộc khu vực TNB;
=AVERAGEIFS(E25:E36,B25:B36,”Sắt Phi 6″,C25:C36,”TNB”) trả về kết quả là 18500 Hàm SUM:
- Công dụng: Tính tổng các giá trị trong danh sách
- Cú pháp: SUM (number1, number2, …)
- Tham số: number1, number2, …: các giá trị số
- Ví dụ: sum(1,2,3,4,5,6,7,8,9) trả về kết quả là 45 Hàm SUMIF:
- Công dụng: Tính tổng của các giá trị trong một mảng theo một điều kiện
- Cú pháp: SUMIF (range, criteria1)
- Tham số: range: là vùng xét điều kiện; criteria: là giá trị để so sánh với các giá
trị trong vùng điều kiện; [sum_rang] : vùng tính tổng
- Ví dụ: Tính tổng giá trị thành tiền của Ciment Hà Tiên;
=SUMIF(B25:B36,”Ciment Hà Tiên”,F25:F36) trả về kết quả là 2169540000 Hàm SUMIFS:
- Công dụng: Tính tổng của các giá trị trong một mảng theo nhiều điều kiện
- Cú pháp: SUMIFS (sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
- Tham số: sum_range là vùng tính tổng; criteria_range1: vùng xét điều kiện thứ
nhất; criteria1: giá trị điều điện thứ nhất; criteria_range2: vùng xét điều kiện thứ
hai; criteria2: giá trị điều điện thứ hai
- Ví dụ: Tính tổng giá trị thành tiền của Ciment Hà Tiên bán ở khu vực Miền Trung;
=SUMIFS(F25:F36,B25:B36,”Ciment Hà Tiên”,C25:C36,”M.TRUNG”) trả về kết quả là 510240000 Hàm COUNTIF:
- Công dụng: Đếm số ô thỏa một điều kiện cho trước bên trong một dãy
- Cú pháp: COUNTIF(range, criteria) 32
- Tham số: range: vùng xét điều kiện: criteria: giá trị điều kiện
- Ví dụ: Đếm các đơn hàng có Số Lượng >2000;
=COUNTIF(D25:D36,”>2000″)trả về kết quả là 10 Hàm COUNTIFS:
- Công dụng: Đếm số ô thỏa nhiều điều kiện cho trước
- Cú pháp: COUNTIFS(range1, criteria1, range2, criteria2, …)
- Tham số: range1: vùng xét điều kiện thứ nhất; criteria1: giá trị điều kiện thứ nhất;
range2: vùng xét điều kiện thứ hai; criteria2: giá trị điều kiện thứ hai
- Ví dụ: Đếm các đơn hàng có Số Lượng >2000 và thuộc khu vực HCM;
=COUNTIFS(D25:D36,”>2000″,C25:C36,”HCM”) trả về kết quả là 2
5.2.2. Ý nghĩa và cách sử dụng hàm luận lý Hàm IF:
- Chức năng: Trả về một giá trị nếu điều kiện có giá trị TRUE, và một giá trị khác
nếu điều kiện có giá trị FALSE.
- Cú pháp: IF(logical_test, [value_if_true], [value_if_false])
- Tham số: logical_text: Biếu thức giá trị hay công thức có thể trả về true hoăc
false; value_if_true: Giá trị trả về nếu biếu thức hay giá trị ở tham số logical_text
là true; value_if_false: Giá trị trả về nếu biếu thức hay giá trị ở tham số logical_text là false. Hàm AND:
- Chức năng: Trả về giá trị TRUE nếu tất cả các đối số là đúng; trả về giá trị
FALSE nếu có một hay nhiều đối số là sai.
- Cú pháp: AND(logical1, [logical2], …)
- Tham số: logical: có thể có từ 1 đến 255 biểu thức được xét xem đúng (True) hay sai (False). Hàm OR:
- Chức năng: Trả về giá trị TRUE nếu có một hay nhiều đối số là đúng; trả về giá
trị FALSE nếu tất cả các đối số là sai.
- Cú pháp: OR(Logical1, [logical2], …)
- Tham số: logical: có thể có từ 1 đến 255 biểu thức được xét xem đúng (True) hay sai (False). Hàm NOT:
- Chức năng: Kết quả TRUE nếu biểu thức logic là FALSE và ngược lại. - Cú pháp: NOT(logical) 33
- Tham số: logical: có thể có từ 1 đến 255 biểu thức được xét xem đúng (True) hay sai (False). Hàm IFERROR:
- Chức năng: Trả về một giá trị đã xác định nếu công thức có lỗi, hoặc trả về kết
quả của công thức nếu công thức đó không có lỗi. Thường dùng IFERROR() để
bẫy lỗi trong các công thức.
- Cú pháp: IFERROR(value, value_if_error)
- Tham số: Value: Là một biểu thức hoặc một công thức cần kiểm tra có lỗi hay
không; Value_if_error: Giá trị trả về nếuvalue gây ra lỗi, là các loại lỗi sau đây:
#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, hoặc #NULL!.
5.2.3. Ý nghĩa và cách sử dụng các hàm thời gian, ngày tháng Hàm TODAY:
- Công dụng: trả về ngày hiện tại trong máy tính - Cú pháp: Today ()
- Tham số: không có tham số. Hàm NOW:
- Công dụng: trả về ngày và giờ hiện tại trong máy tính - Cú pháp: NOW()
- Tham số: không có tham số Hàm DATE:
- Công dụng: nhập vào ngày tháng năm theo đúng định dạng của máy tính
- Cú pháp: DATE(year, month, day)
- Tham số: Year: nhập số năm; Month: nhập số tháng; Day: nhập số ngày
- Ví dụ: DATE(2016,9,10) trả về kết quả là 10/9/2016 Hàm DAY:
- Công dụng: trả về ngày trong tháng (1-31)
- Cú pháp: DAY(serial_number)
- Tham số: serial_number: giá trị là ngày tháng năm
- Ví dụ: DAY(42623) trả về kết quả là 10 Hàm MONTH:
- Công dụng: trả về tháng trong năm (1-12)
- Cú pháp: MONTH(serial_number)
- Tham số: serial_number: giá trị là ngày tháng năm
- Ví dụ: MONTH(DATE(2016,9,10) trả về kết quả 9; MONTH(42623) trả về kết quả 9 34 Hàm YEAR:
- Công dụng: trả về năm
- Cú pháp: DAY(serial_number)
- Tham số: serial_number: giá trị là ngày tháng năm
- Ví dụ: YEAR(DATE(2016,9,10) trả về kết quả 2016; YEAR(42623) trả về kết quả 2016 Hàm TIME:
- Công dụng: nhập vào giờ phút giây theo đúng định dạng của máy tính
- Cú pháp: Time(hour, minute, second)
- Tham số: Hour: nhập vào giờ; Minute: nhập vào phút; Second: nhập vào giây
- Ví dụ: =TIME(6,15,15) trả về kết quả 6:15:15 AM Hàm HOUR:
- Công dụng: trả về giờ (0 – 23)
- Cú pháp: HOUR(serial_number)
- Tham số:serial_number: giá trị là giờ phút giây
- =HOUR(TIME(6,15,15)) trả về kết quả 6; =HOUR(0.25) trả về kết quả 6 Hàm MINUTE:
- Công dụng: trả về số phút (0- 59)
- Cú pháp: MINUTE(serial_number)
- Tham số:serial_number: giá trị là giờ phút giây
- Ví dụ: =MINUTE(TIME(6,15,15)) trả về kết quả 15; =MINUTE(0.3) trả về kết quả 12 Hàm SECOND:
- Công dụng: trả về số giây (0- 59)
- Cú pháp SECOND(serial_number)
- Tham số: serial_number: giá trị là giờ phút giây
- Ví dụ: =MINUTE(TIME(6,15,15)) trả về kết quả 15 ; =SECOND(0.305) trả về kết quả 12 Hàm WEEKDAY:
- Công dụng: trả về 1 con số đại diện cho 1 ngày trong tuần (1-7) theo định dạng
- Cú pháp: WEEKDAY(serial_number, [return_type])
- Tham số: serial_number: giá trị ngày tháng năm; [return_type]: định dạng quy định của thứ
- Ví dụ: =WEEKDAY(DATE(2016,9,10),1) trả về kết quả 7 35
5.2.4. Ý nghĩa và cách sử dụng các hàm văn bản Hàm UPPER:
- Chức năng: Chuyển một chuỗi viết thường thành viết hoa. - Cú pháp: UPPER(Text)
- Tham số: Text: chuỗi muốn chuyển.
- Ví dụ: UPPER(“abcd”) trả về kết quả là ABCD. Hàm LOWER:
- Chức năng: Chuyển một chuỗi viết hoa thành viết thường. - Cú pháp: LOWER(Text)
- Tham số: Text: chuỗi muốn chuyển.
- Ví dụ: LOWER(“ABCD”) trả về kết quả abcd Hàm TRIM:
- Chức năng: Cắt bỏ những khoảng trắng thừa trong một chuỗi. - Cú pháp: TRIM(Text) - Tham số: Text: chuỗi.
- Ví dụ: TRIM(“ A B C “) trả về kết quả là A B C. Hàm PROPER:
- Chức năng: Chuyễn những chữ cái đầu của từ trong một chuỗi thành viết hoa. - Cú pháp: PROPER(Text)
- Tham số: Text: chuỗi muốn chuyển.
- Ví dụ: PROPER(“nguyen van an”) trả về kết quả là Nguyen Van An Hàm LEFT:
- Chức năng: Cắt và trả về chuỗi ký tự bên trái của chuỗi.
- Cú pháp: LEFT(Text,[num_chars])
- Tham số: Text: chuỗi; num_chars : số ký tự muốn cắt từ bên trái.
- Ví dụ: LEFT(“ABCD”,2) trả về kết quả là AB Hàm MID:
- Chức năng: Cắt và trả về chuỗi ký tự ở giữa của chuỗi.
- Cú pháp: MID(Text, start_num, num_chars)
- Tham số: Text: chuỗi; Start_num: Số vị trí bắt đầu cắt; Num_chars: Số ký tự
muốn cắt bắt đầu từ start_num.
- Ví dụ: MID(“ABCD”,2,1) trả về kết quả là B Hàm RIGHT:
- Chức năng: Cắt và trả về chuỗi ký tự bên phải của chuỗi. 36
- Cú pháp: RIGHT(Text,[num_chars])
- Tham số: Text: chuỗi; Num_chars : Số ký tự muốn cắt từ bên phải.
- Ví dụ: RIGHT(“ABCD”,2) trả về kết quả là CD Hàm LEN:
- Chức năng: Trả về tổng số độ dài của một chuỗi. - Cú pháp: LEN(Text)
- Tham số:Text: chuỗi muốn đếm số ký tự.
- Ví dụ: LEN(“ABCD”) trả về kết quả là 4. Hàm FIND:
- Chức năng: Trả về số vị trí bắt đầu của chuỗi mình cần tìm và có phân biệt chữ hoa và chữ thường.
- Cú pháp: FIND(Find_text, within_text, [start_num])
- Tham số: Find_text: chuỗi cần tìm; Within_text: chuỗi; Start_num: Bắt đầu tìm
từ vị trí từ bên trái chuỗi trở đi.
- Ví dụ: FIND(“e”,”MS Excel”,1) trả về kết quả là 7 Hàm VALUE:
- Chức năng: Chuyển một số chuỗi thành số value. - Cú pháp: VALUE(Text)
- Tham số: Text: số kiểu chuỗi.
- Ví dụ: VALUE(“4”) trả về kết quả là 4 Hàm SEARCH:
- Chức năng: Trả về vị trí bắt đầu của chuỗi mình cần tìm và không phân biệt chữ hoa và chữ thường.
- Cú pháp: SEARCH(Find_text, within_text, [start_num])
- Tham số: Find_text: chuỗi cần tìm; Within_text: chuỗi; Start_num: Bắt đầu tìm
từ vị trí từ bên trái chuỗi trở đi.
- Ví dụ: SEARCH(“e”,”MS Excel”,1) trả về kết quả là 4 Hàm REPLACE:
- Chức năng: Thay thế một chuỗi bắt đầu bằng số thứ tự ký tự truyền vào.
- Cú pháp: REPLACE(old_text, start_num, num_chars, new_text)
- Tham số: Old_text: Chuỗi cũ; Start_num: Bắt đầu thay thế từ ký tự thứ mấy;
Num_chars: Số ký tự cần thay thế; New_text: Chuỗi mới thay thế.
- Ví dụ: REPLACE(“2009”,3,2,”10”) trả về kết quả là 2010 Hàm SUBSTITUTE:
- Chức năng: Tìm kiếm và thay thế một chuỗi cũ thành chuỗi mới. 37
- Cú pháp: SUBSTITUTE(Text, old_text, new_text, [instance_num])
- Tham số: Text: chuỗi; Old_text: chuỗi cũ; New_text: chuỗi mới thay thế cho
chuỗi cũ; Instance_num: Số ký tự thứ bao nhiêu được tìm thấy trong chuỗi.
- Ví dụ: SUBSTITUTE(“d@vid s@m”,”@”,”a”,2) trả về kết quả là d@vid sam Hàm TEXT:
- Chức năng: Chuyển một số thành dạng chuỗi theo định dạng được chỉ định.
- Cú pháp: Text(value, format_text)
- Tham số: Value: Giá trị; Format_text: Kiểu định dạng.
- Ví dụ: Text(“123000”,”#,## [$VNĐ]”) trả về kết quả là 123,000 VNĐ
5.2.5. Ý nghĩa và cách sử dụng các hàm toán học và lượng giác Hàm ABS:
- Công dụng: trả về giá trị tuyệt đối của số đó - Cú pháp: ABS(number)
- Tham số: Number: số cần tính
- Ví dụ: ABS(-33) trả về kết quả là 33 ; ABS(33) trả về kết quả là 33 Hàm SQRT:
- Công dụng: Tính căn bậc hai của 1 số - Cú pháp: SQRT(number)
- Tham số: Number: số cần tính
- Ví dụ: =SQRT(25) trả về kết quả là 5 Hàm POWER:
- Công dụng: Tính lũy thừa
- Cú pháp: POWER(number,power)
- Tham số: Number: là số cơ sở (số thực); Power: là số mũ
- Ví dụ: =POWER(2,3) trả về kết quả là 8 Hàm ROUND:
- Công dụng: Làm tròn 1 số thập phân đến n chữ số sau dấu phẩy
- Cú pháp: ROUND(number, num_digits)
- Tham số: Number: số cần làm tròn; num_digits: số chữ số thập phân muốn làm tròn
- Ví dụ: làm tròn không lấy chữ số thập phân nào =ROUND(123.456,0) trả về kết quả là 123 Hàm MOD:
- Công dụng: Trả về phần dư của phép chia 2 số
- Cú pháp: MOD(number,divisor) 38
- Tham số: Number: số bị chia; Divisor: số chia
- Ví dụ: =MOD(20,3) trả về kết quả là 2 Hàm INT:
- Công dụng: lấy phần nguyên của 1 số thực - Cú pháp: INT(number)
- Tham số: Number: số thực muốn lấy phần nguyên
- Ví dụ: =INT(123.456) trả về kết quả là 123 Hàm ODD:
- Công dụng: Làm tròn lên đến giá trị số nguyên lẻ gần nhất - Cú pháp: ODD(number)
- Tham số: Number: là giá trị cần làm tròn.
- Ví dụ: =ODD(122) à 123 ; =ODD(123) trả về kết quả là 123 Hàm RAND:
- Công dụng: Trả về một số ngẫu nhiên giữa 0 và 1 - Cú pháp: RAND()
- Tham số: không có tham số
5.2.6. Ý nghĩa và cách sử dụng các hàm dò tìm và tham chiếu Hàm MATCH:
- Chức năng: Trả về vị trí của một giá trị dòng (hoăc cột) trong một dãy giá trị.
- Cú pháp: MATCH(lookup_value, lookup_array, [match_type])
- Tham số: Lookup_value: Giá trị dò; Lookup_array: Bảng dò; Match_type: Kiểu dò. Hàm INDEX:
- Chức năng: Trả về giá trị tương ứng với tọa độ dòng và cột.
- Cú pháp: INDEX(Array, row_num, [column_num])
- Tham số: Array: Bảng dò; Row_num: Chỉ số dòng; Column_num: Chỉ số cột. Hàm LOOKUP:
- Chức năng: trả về một giá trị từ một vùng dữ liệu gồm 1 cột hoặc 1 hàng, hoặc từ một mảng.
- Cú pháp:LOOKUP(lookup_value, array)
- Tham số: lookup_value: giá trị cần tìm kiếm trong một mảng, lookup_value có
thể là số, văn bản, giá trị logic, tên hoặc tham chiếu tới một giá trị; array: vùng
tìm kiếm bao gồm các ô có chứa văn bản, số hoặc giá trị logic mà các bạn muốn tìm lookup_value trong đó. Hàm VLOOKUP: 39
- Chức năng: Dò tìm một hàng (row) chứa giá trị cần tìm ở cột đầu tiên (bên trái)
của một bảng dữ liệu, nếu tìm thấy, sẽ tìm tiếp trong hàng này, và sẽ lấy giá trị
ở cột đã chỉ định trước.
- Cú pháp: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Tham số:Lookup_value: Giá trị dò; Table_array: Bảng dò (dạng cột);
Col_index_num: Cột cần tìm; Range_lookup: Kiểu dò (True-False). Hàm HLOOKUP:
- Chức năng: Dò tìm một cột (column) chứa giá trị cần tìm ở hàng đầu tiên (trên
cùng) của một bảng dữ liệu, nếu tìm thấy, sẽ tìm tiếp trong cột. này, và sẽ lấy
giá trị ở hàng đã chỉ định trước.
- Cú pháp: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- Tham số: Lookup_value: Giá trị dò; Table_array: Bảng dò (dạng cột);
Row_index_num: Dòng cần tìm; Range_lookup: Kiểu dò (True-False). 40