Bài giảng Tin học văn phòng - Bài 9+10: Các hàm trong Excel - Nguyễn Thị Phương Thảo

n
 HÀM TOÁN HỌC
• ROUND
 Công thức Mô tả Kết quả
 =ROUND(2.15, 1) Làm tròn 2,15 tới một vị trí thập phân ?2,2
 =ROUND(2.149, 1) Làm tròn 2,149 tới một vị trí thập phân ?2,1
 =ROUND(-1.475, 2) Làm tròn -1,475 tới hai vị trí thập phân ?-1,48
 Làm tròn 21,5 đến một vị trí thập phân về bên trái 
 =ROUND(21.5, -1) ?20
 của dấu thập phân
 =ROUND(626.3,-3) Làm tròn 626,3 về bội số gần nhất của 1000 ?1000
 =ROUND(1.98, -1) Làm tròn 1,98 về bội số gần nhất của 10 ?0
 =ROUND(-50.55, -2) Làm tròn -50,55 về bội số gần nhất của 100 ?-100
 HÀM TOÁN HỌC
• MAX
 – Là hàm trả về giá trị lớn nhất trong tập dữ liệu.
 – Cú pháp: MAX(number1, [number2], ...)
 • Number1,number2,.number255: giá trị số cần tìm giá trị lớn nhất
• MIN
 – Là hàm trả về giá trị nhỏ nhất trong tập dữ liệu
 – Cú pháp: MIN(number1, [number2], ...)
 • Number1,number2,.number255: giá trị số cần tìm giá trị nhỏ nhất
 HÀM TOÁN HỌC
• LARGE
 – Là hàm trả về giá trị lớn thứ k của tập giá trị.
 – Cú pháp: LARGE(array, k)
 • array: mảng hoặc phạm vi dữ liệu cần tìm giá trị lớn thứ k
 • k: vị trí (tính từ lớn nhất)
• SMALL
 – Là hàm trả về giá trị nhỏ thứ k trong tập dữ liệu
 – Cú pháp: SMALL(array, k)
 • array: mảng hoặc phạm vi dữ liệu cần tìm giá trị lớn thứ k
 • k: vị trí (tính từ nhỏ nhất)
 HÀM TOÁN HỌC
• RANK
 – Là hàm trả về thứ hạng của một số trong danh sách các số. Thứ hạng 
 của số là kích thước của nó trong tương quan với các giá trị khác trong 
 danh sách.
 – Cú pháp: RANK(number,ref,[order])
 • Number: Số mà bạn muốn tìm thứ hạng của nó
 • ref: Một mảng hoặc tham chiếu tới một danh sách các số
 • [order]: nếu là 0 hoặc bỏ qua lấy thứ hạng theo thứ tự giảm dần. 
 Ngược lại là theo thứ tự tăng dần
 HÀM TOÁN HỌC
• COUNT
 – Hàm đếm số ô chứa số và các số trong danh sách các đối số.
 – Cú pháp: COUNT(value1, [value2], ...)
 • value1: tham chiếu ô hoặc phạm vi muốn đếm số
 • [value2]: tối đa 255 mục , tham chiếu ô hoặc phạm vi bổ sung
 Dữ liệu Công thức Mô tả Kết quả
 08/12/08 Đếm số ô chứa số trong các
 =COUNT(A2:A7) 3
 ô từ A2 tới A7.
 Đếm số ô chứa số trong các
 19 =COUNT(A5:A7) 2
 ô từ A5 tới A7.
 22,24
 Đếm số ô chứa số trong các 
 TRUE =COUNT(A2:A7,2) 4
 ô từ A2 tới A7 và giá trị 2.
 #DIV/0!
 HÀM TOÁN HỌC
• COUNTIF
 – Hàm đếm số ô trong phạm vi xác định đáp ứng một tiêu chí nào đó.
 – Cú pháp: COUNTIF( range, criteria )
 • range: mảng hay tham chiếu chứa số
 • Criteria: tiêu chí
 Kết 
 Công thức Mô tả
 quả
 =COUNTIF(A2:A5,"t Số ô có chứa táo trong các ô từ A2 tới 
 ?2
 áo") A5.
 =COUNTIF(A2:A5,A Số ô có chứa đào trong các ô từ A2
 Dữ liệu Dữ liệu ?1
 4) tới A5.
 táo 32
 =COUNTIF(A2:A5,A
 Số ô có chứa cam và táo trong các ô 
 cam 54 3)+COUNTIF(A2:A5, ?3
 từ A2 tới A5.
 đào 75 A2)
 =COUNTIF(B2:B5,"> Số ô có giá trị lớn hơn 55 trong các ô 
 táo 86 ?2
 55") từ B2 tới B5.
 =COUNTIF(B2:B5,"< Số ô có giá trị khác 75 trong các ô từ 
 ?3
 >"&B4) B2 tới B5.
 HÀM TOÁN HỌC
• SUM
 – Hàm tính tổng các số.
 – Cú pháp: SUM(number1,[number2],...)
 • Number1, number2 number255: các số, mảng hoặc tham chiếu
• SUMIF
 – Hàm tính tổng các giá trị trong phạm vi đáp ứng tiêu chí xác định.
 – Cú pháp: SUMIF(range, criteria, [sum_range])
 • range: phạm vi ô cần đánh giá theo tiêu chí
 • criteria: Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc 
 hàm xác định
 • Sum_range: Các ô thực tế để cộng
 HÀM TOÁN HỌC
Nếu range là Và sum_range và Thì ô thực tế là
A1:A5 B1:B5 B1:B5
A1:A5 B1:B3 B1:B5
A1:B4 C1:D4 C1:D4
A1:B4 C1:C2 C1:D4
 HÀM TOÁN HỌC
Giá trị Tài sản Tiền hoa hồng Dữ liệu
$ 100.000,00 $ 7.000,00 $ 250.000,00 
$ 200.000,00 $ 14.000,00 
$ 300.000,00 $ 21.000,00 
$ 400.000,00 $ 28.000,00 
Công thức Mô tả Kết quả
 Tổng tiền hoa hồng cho các giá 
=SUMIF(A2:A5,">160000",B2:B5) $ 63.000,00 
 trị tài sản lớn hơn 160.000.
 Tổng các giá trị tài sản lớn hơn 
=SUMIF(A2:A5,">160000") $ 900.000,00 
 160.000.
 Tổng tiền hoa hồng cho các giá 
=SUMIF(A2:A5,300000,B2:B5) ?$ 21.000,00 
 trị tài sản bằng 300.000.
 Tổng tiền hoa hồng cho các giá 
=SUMIF(A2:A5,">" & C2,B2:B5) ?$ 49.000,00 
 trị tài sản lớn hơn giá trị tại C2.
 HÀM TOÁN HỌC
• SUMIFS
 – Hàm tính tổng các ô trong phạm vi đáp ứng nhiều tiêu chí.
 – Cú pháp: SUMIFS(sum_range, criteria_range1, criteria1, 
 [criteria_range2, criteria2], ...)
 • sum_range: các số, mảng hoặc tham chiếu cần cộng
 • criteria_range1: phạm vi thứ nhất dùng để đánh giá tiêu chí
 • criteria 1: Tiêu chí dưới dạng một số, biểu thức, tham chiếu ô
 HÀM TOÁN HỌC
• Ví dụ Số lượng Đã bán Sản phẩm Người bán hàng
 5 Táo 1
 4 Táo 2
 15 Atisô 1
 3 Atisô 2
 22 Chuối 1
 12 Chuối 2
 10 Cà rốt 1
 33 Cà rốt 2
 Công thức Mô tả Kết quả
 =SUMIFS(A2:A9, B2:B9, Cộng tổng số sản phẩm bán được bắt đầu 
 15
 "=A*", C2:C9, 1) bằng chữ "A" và do Người bán hàng 1 bán.
 =SUMIFS(A2:A9, B2:B9, Cộng tổng số sản phẩm (không bao gồm 
 30
 "Chuối", C2:C9, 1) Chuối) do Người bán hàng 1 bán.
 HÀM TOÁN HỌC
• SUMPRODUCT
 – Nhân các thành phần tương ứng trong các mảng đã cho và trả về tổng 
 của các tích số này.
 – Cú pháp: SUMPRODUCT(array1, [array2], [array3], ...)
 • array1, array2: mảng các số
 HÀM TOÁN HỌC
• AVERAGE
 – Trả về trung bình (trung bình cộng) của các đối số.
 – Cú pháp: AVERAGE(number1, [number2], ...)
 • number1, number2: Các số, tham chiếu ô hoặc phạm vi bổ sung 
 tính trung bình
 HÀM LOGIC
• IF
 – Hàm IF trả về một giá trị nếu điều kiện chỉ rõ định trị là TRUE, trả về một 
 giá trị khác nếu điều kiện đó định trị là FALSE.
 – Cú pháp: IF(logical_test, [value_if_true], [value_if_false])
 • Logical_test: Bất kỳ giá trị hoặc biểu thức nào có thể được định trị là 
 TRUE hoặc FALSE
 • Value_if_true: Giá trị trả về nếu đối số logical_test định trị là TRUE
 • Value_if_false: Giá trị trả về nếu đối số logical_test định trị là FALSE
 HÀM LOGIC
• Ví dụ
 Chi phí Thực tế Chi phí Dự kiến
 $1.500 $900
 $500 $900
 $500 $925
 Công thức Mô tả Kết quả
 Kiểm tra xem chi phí thực tế trong hàng 2
 =IF(A2>B2,"Vượt dự có lớn hơn chi phí dự kiến hay không. Trả Vượt Dự 
 toán","OK") về "Vượt Dự toán" vì kết quả của phép thử toán
 này là True.
 Kiểm tra xem chi phí thực tế trong hàng 3
 =IF(A3>B3,"Vượt Dự có lớn hơn chi phí dự kiến hay không. Trả 
 OK
 toán","OK") về "OK" vì kết quả của phép thử này là 
 False.
 HÀM LOGIC
• Ví dụ Điểm
 45
 90
 78
 CôngCông thứthứcc MôMô tảtả KếKếtt quảquả
 DùDùngng cácácc điềđiềuu kiệkiệnn IFIF lồlồngng đểđể gágánn 
 =IF(A2>89,"A",IF(A2>79,"B",=IF(A2>89,"A",IF(A2>79,"B", 
 điểđiểmm bằbằngng chữchữ vàvàoo điểđiểmm sốsố trongtrong ôô ?F
 IF(A2>69,"C",IF(A2>59,"D","F"))))IF(A2>69,"C",IF(A2>59,"D","F"))))
 AA22..
 DùDùngng cácácc điềđiềuu kiệkiệnn IFIF lồlồngng đểđể gágánn 
 =IF(A3>89,"A",IF(A3>79,"B",=IF(A3>89,"A",IF(A3>79,"B", 
 điểđiểmm bằbằngng chữchữ vàvàoo điểđiểmm sốsố trongtrong ôô ?A
 IF(A3>69,"C",IF(A3>59,"D","F"))))IF(A3>69,"C",IF(A3>59,"D","F"))))
 AA33..
 DùDùngng cácácc điềđiềuu kiệkiệnn IFIF lồlồngng đểđể gágánn 
 =IF(A4>89,"A",IF(A4>79,"B",=IF(A4>89,"A",IF(A4>79,"B", 
 điểđiểmm bằbằngng chữchữ vàvàoo điểđiểmm sốsố trongtrong ôô ?C
 IF(A4>69,"C",IF(A4>59,"D","F"))))IF(A4>69,"C",IF(A4>59,"D","F"))))
 AA44..
 HÀM LOGIC
• AND
 – Trả về TRUE nếu tất cả các đối số của hàm định trị là TRUE; trả về 
 FALSE nếu một hoặc nhiều đối số định trị là FALSE.
 – Cú pháp: AND(logical1, [logical2], ...)
 • Logical1, logical2: các điều kiện
 Công thức Mô tả Kết quả
 Tất cả các đối số là 
 =AND(TRUE, TRUE) TRUE
 TRUE
 =AND(TRUE, FALSE) Một đối số là FALSE FALSE
 Tất cả các đối số đều 
 =AND(2+2=4, 2+3=5) TRUE
 định trị là TRUE
 HÀM LOGIC
• OR
 – Trả về TRUE nếu bất kỳ đối số nào là TRUE, trả về FALSE nếu tất cả 
 các đối số là FALSE.
 – Cú pháp: OR(logical1, [logical2], ...)
 • Logical1, logical2: các điều kiện
 Công thức Mô tả Kết quả
 =OR(TRUE) Một đối số là TRUE TRUE
 Tất cả các đối số đều định 
 =OR(1+1=1,2+2=5) FALSE
 trị là FALSE 
 =OR(TRUE,FALSE,TRUE) Ít nhất một đối số là TRUE TRUE
 HÀM LOGIC
• NOT
 – Đảo nghịch giá trị của đối số của nó.
 – Cú pháp: NOT(logical)
 • Logical: Một giá trị hoặc biểu thức có thể được định trị là TRUE hoặc 
 FALSE.
 Công thức Mô tả Kết quả
 =NOT(FALSE) Nghịch đảo của FALSE TRUE
 Nghịch đảo của phương 
 =NOT(1+1=2) trình được định trị là FALSE
 TRUE 
 HÀM KÝ TỰ
• LOWER
 – Chuyển đổi chữ hoa trong chuỗi văn bản thành chữ thường.
 – Cú pháp: LOWER(text)
 • text: Văn bản muốn chuyển chữ hoa thành chữ thường.
• Ví dụ: LOWER("Đại học Thủy Lợi") =“đại học thủy lợi”
 • UPPER
 – Chuyển đổi văn bản thành chữ hoa.
 – Cú pháp: UPPER(text)
 • text: Văn bản muốn chuyển đổi thành chữ hoa.
 • Ví dụ: UPPER("Đại học Thủy Lợi") =“ĐẠI HỌC THỦY LỢI”
 HÀM KÝ TỰ
• CONCATENATE
 – Kết hợp tối đa 255 chuỗi văn bản thành một chuỗi văn bản duy nhất.
 – Cú pháp: CONCATENATE(text1, [text2], ...)
 • text: Văn bản muốn ghép chuỗi.
• MID
 – Trả về một số ký tự từ một chuỗi văn bản, bắt đầu từ một vị trí và số 
 lượng ký tự.
 – Cú pháp: MID(text, start_num, num_chars)
 • text: Văn bản muốn lấy ký tự.
 • start_num: Vị trí ký tự thứ nhất cần trích xuất. Bắt đầu là 1
 • num_chars: số ký tự cần trích xuất
 HÀM KÝ TỰ
• LEFT, RIGHT
 – Trả về một hoặc nhiều ký tự đầu tiên trong một chuỗi, dựa vào số ký tự
 chỉ định.
 – Cú pháp: LEFT(text, [num_chars])
 • text: Chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất.
 • [num_chars]: số ký tự muốn trích xuất
 • TRIM
 – Loại bỏ tất cả khoảng trống ra khỏi văn bản, chỉ để lại một khoảng trống 
 giữa các từ.
 – Cú pháp: TRIM(text)
 • text: Văn bản bạn muốn loại bỏ các khoảng trống.
 HÀM KÝ TỰ
• FIND, SEARCH
 – Định vị một chuỗi văn bản nằm trong chuỗi văn bản thứ hai và trả về số 
 của vị trí bắt đầu.
 – Cú pháp: FIND(find_text, within_text, [start_num])
 • Find_text: Văn bản muốn tìm.
 • Within_text: Văn bản có chứa văn bản muốn tìm
 • [start_num]: Chỉ rõ ký tự bắt đầu tìm tại đó
 Kết 
 Công thức Mô tả
 Dữ liệu quả
Miriam McGovern =FIND("M",A2) Vị trí của chữ "M" thứ nhất trong ô A2 ?1
 =FIND("m",A2) Vị trí của chữ "M" thứ nhất trong ô A2 ?6
 Vị trí của chữ "M" thứ nhất trong ô A2, 
 =FIND("M",A2,3) ?8
 bắt đầu từ ký tự thứ ba 
 HÀM KÝ TỰ
 Dữ liệu
 Sứ Cách điện #124-TD45-87
 Cuộn dây Đồng #12-671-6772
 Biến Trở #116010
Công thức Mô tả (Kết quả) Kết quả
 Trích văn bản từ vị trí 1 tới vị 
=MID(A2,1,FIND(" 
 trí "#" trong ô A2 (Sứ Cách ?
#",A2,1)-1)
 điện)
 Trích văn bản từ vị trí 1 tới vị 
=MID(A3,1,FIND("-
 trí "#" trong ô A3 (Cuộn dây ?
",A3,1)-1)
 Đồng)
=MID(A4,1,FIND(" Trích văn bản từ vị trí 1 tới vị 
 ?
0",A4,1)-1) trí "#" trong ô A4 (Biến Trở)
 HÀM KÝ TỰ
• REPLACE
 – Thay thế một phần của chuỗi văn bản, dựa vào số ký tự do bạn chỉ định, 
 bằng một chuỗi văn bản khác.
 – Cú pháp: REPLACE(old_text, start_num, num_chars, new_text)
 • old_text: Văn bản muốn thay thế một vài ký tự trong đó.
 • start_num: Vị trí của ký tự trong văn bản cũ muốn thay thế bằng văn 
 bản mới
 • num_chars: Số lượng ký tự trong văn bản cũ muốn thay thế
 • new_text: Văn bản sẽthay thế các ký tự trong old_text
 HÀM KÝ TỰ
 Dữ liệu
 abcdefghijk
 2009
 123456
Công thức Mô tả (Kết quả) Kết quả
 Thay thế năm ký tự trong 
 abcdefghijk bằng một ký tự * 
=REPLACE(A2,6,5,"*") ?abcde*k
 duy nhất, bắt đầu tại ký tự thứ 
 sáu (f).
 Thay thế hai chữ số cuối (09) 
=REPLACE(A3,3,2,"10") ?2010
 của 2009 thành 10.
 Thay thế ba ký tự đầu tiên của 
=REPLACE(A4,1,3,"@") 123456 bằng một ký tự @ duy ?@456
 nhất.
 HÀM KÝ TỰ
• EXACT
 – So sánh hai chuỗi văn bản và trả về TRUE nếu chúng hoàn toàn giống 
 nhau, FALSE nếu khác.
 – Hàm EXACT phân biệt chữ hoa, chữ thường nhưng bỏ qua khác biệt về 
 định dạng
 – Cú pháp: EXACT(text1, text2)
 • text1: Chuỗi văn bản thứ nhất .́
 • text2: Chuỗi văn bản thứ hai.
 Công thức Kết quả
 =EXACT(“word”,”word”) TRUE
 =EXACT(“Word”,”word”) FALSE
 =EXACT(“ word”,” word”) FALSE
 HÀM KÝ TỰ
• LEN
 – Hàm LEN về số ký tự trong một chuỗi văn bản.
 – Cú pháp: LEN(text)
 • text: Văn bản mà bạn muốn tìm độ dài của nó. Khoảng trống được 
 đếm là ký tự.
 HÀM XỬ LÝ NGÀY THÁNG
• DAY/MONTH/YEAR
 – Trả về ngày/tháng/năm của ngày tháng.
 – Cú pháp: DAY(serial_number)
 • serial_number: Ngày tháng.
 • HOUR/MINUTE/SECOND
 – Trả về giờ/phút/giây của một giá trị thời gian.
 – Cú pháp: HOUR (serial_number)
 • serial_number: Ngày tháng.
 HÀM XỬ LÝ NGÀY THÁNG
• TODAY
 – Trả về ngày hiện tại.
 – Cú pháp: TODAY()
 • NOW
 – Trả về ngày và thời gian hiện tại.
 – Cú pháp: NOW()
 HÀM TÌM KIẾM
• LOOKUP
 – Hàm LOOKUP trả về một giá trị từ một phạm vi gồm một cột hoặc một 
 hàng, hoặc từ một mảng.
 – Cú pháp: LOOKUP(lookup_value, lookup_vector, [result_vector])
 • lookup_value: giá trị tìm kiếḿ.
 • lookup_vector: Phạm vi tìm kiếm chỉ chứa một hàng hoặc một 
 cột
 • result_vector: Phạm vi lấy kết quả chỉ chứa một hàng hoặc một 
 cột
 Các giá trị trong lookup_vector phải được xếp theo thứ tự tăng dần: ..., 
 -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; nếu không, hàm LOOKUP có thể 
 trả về giá trị không chính xác
 HÀM TÌM KIẾM
• LOOKUP
 – Hàm LOOKUP trả về một giá trị từ một phạm vi gồm một cột hoặc một 
 hàng, hoặc từ một mảng.
 – Cú pháp: LOOKUP(lookup_value, lookup_vector, [result_vector])
 • lookup_value: giá trị tìm kiếḿ.
 • lookup_vector: Phạm vi tìm kiếm chỉ chứa một hàng hoặc một 
 cột
 • result_vector: Phạm vi lấy kết quả chỉ chứa một hàng hoặc một 
 cột
 Các giá trị trong lookup_vector phải được xếp theo thứ tự tăng dần: ..., 
 -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; nếu không, hàm LOOKUP có thể 
 trả về giá trị không chính xác
 HÀM TÌM KIẾM
 Frequency Màu
 4,14 màu đỏ
 4,19 màu cam
 5,17 màu vàng
 5,77 màu xanh lá cây
 6,39 màu xanh lam
Công thức Mô tả Kết quả
=LOOKUP(4.19, A2:A6, Tra cứu 4,19 trong cột A và trả về giá trị từ cột B trong 
 màu cam
B2:B6) cùng hàng.
=LOOKUP(5.75, A2:A6, Tra cứu 5,75 trong cột A, khớp với giá trị nhỏ nhất gần 
 màu vàng
B2:B6) nhất (5,17) và trả về giá trị từ cột B trong cùng hàng.
=LOOKUP(7.66, A2:A6, Tra cứu 7,66 trong cột A, khớp với giá trị nhỏ nhất gần 
 màu xanh lam
B2:B6) nhất (6,39), và trả về giá trị từ cột B trong cùng hàng.
 Tra cứu 0 trong cột A và trả về lỗi vì 0 nhỏ hơn giá trị 
=LOOKUP(0, A2:A6, B2:B6) #N/A
 nhỏ nhất (4,14) trong cột A.
 HÀM TÌM KIẾM
• VLOOKUP
 – hàm VLOOKUP để tìm cột đầu tiên của một phạm vi ô, sau đó trả về một 
 giá trị từ bất kỳ ô nào trên cùng hàng của phạm .vi
 – Cú pháp: VLOOKUP(lookup_value, table_array, col_index_num, 
 [range_lookup])
 • lookup_value: giá trị tìm kiếm trong cột đầu tiên của bảng hoặc 
 phạm ví.
 • table_array: Phạm vi ô có chứa dữ liệu
 • col_index_num: Số cột trong đối số table_array mà giá trị khớp 
 phải được trả về từ đó
 • [range_lookup]: là TRUE hoặc bị bỏ qua, một kết quả khớp 
 chính xác hoặc tương đối được trả về.
THỰC HÀNH