I. CÁC HÀM VỀ SỐ
1. INT(number)
Làm tròn number xuống đến số nguyên gần nhất
Ví dụ : INT(5.9) 5
INT(-4.3) -5
2. MOD(number, divisor)
Number và divisor phải là số nguyên
Hàm trả về phần dư của phép chia nguyên number cho divisor
Ví dụ : MOD(26,3) 2
3. MAX(number1, number2, …)
Trả về giá trị lớn nhất trong các số number1, number2, …
Ví dụ : MAX(15, 7, 12) 15
4. MIN(number1, number2, …)
Trả về giá trị nhỏ nhất trong các số number1, number2, …
Ví dụ : MIN(15, 7, 12) 7
5. ROUND(number, num_digits)
Làm tròn number đến vị trí xác định bởi num_digits…
num_digits=-3 làm tròn đến hàng ngàn
num_digits=-2 làm tròn đến hàng trăm
num_digits=-1 làm tròn đến hàng chục
num_digits=0 làm tròn đến hàng đơn vị
num_digits=1 làm tròn đến 1 số lẻ
num_digits=2 làm tròn đến 2 số lẻ
…
Ví dụ : A=57483.1652
ROUND(A,0) 57483
ROUND(A,1) 57483.2
ROUND(A,2) 57483.17
ROUND(A,-1) 57480
ROUND(A,-2) 57500
ROUND(A,-3) 57000
6. AVERAGE(number1, number2, …)
Trả về trị trung bình của các số number1, number2, …
Ví dụ : AVERAGE(9, 4, 5) 6
ROUND(AVERAGE(7,3,6),1) 5.3
7. SUM(number1, number2, …)
Trả về tổng của number1, number2, …
Ví dụ : SUM(15,2,3) 20
8. VALUE(text)
Chuyển chuỗi text dạng số thanh số để có thể tính toán
II. CÁC HÀM VỀ CHUỖI
1. LEFT(text, num_chars)
Trả về num_chars ký tự của chuỗi text tính từ trái
Ví dụ : LEFT(“TRUNG TAM TIN HOC”,9) TRUNG TAM
2. RIGHT(text, num_chars)
Trả về num_chars ký tự của chuỗi text tính từ phải
Ví dụ: RIGHT(“TRUNG TAM TIN HOC”,7) TIN HOC
3. MID(text, start_num, num_chars)
Trả về num_chars ký tự của chuỗi text tính từ vị trí start_num
Ví dụ : MID(“TRUNG TAM TIN HOC”,5,7) G TIN H
4. UPPER(text)
Trả về text với tất cả ký tự chuyển thành chữ hoa
Ví dụ : UPPER(“Trung Tam Tin Hoc”) TRUNG TAM TIN HOC
5. LOWER(text)
Trả về text với tất cả ký tự chuyển thành chữ thường
Ví dụ : LOWER(“Trung Tam Tin Hoc”) trung tam tin hoc
6. PROPER(text)
Trả về text với tất cả ký tự chuyển thành chữ thường, các ký tự đầu mỗi từ là chữ hoa
Ví dụ: PROPER(“TRUNG TAM TIN HOC”) Trung Tam Tin Hoc
7. TRIM(text)
Cắt bỏ các khoảng trắng thừa: đầu chuỗi, cuối chuỗi, giữa 2 từ chỉ còn 1 khoảng trắng
Ví dụ: TRIM(“TRUNGTAMTINHOC”) TRUNG TAM TIN HOC
III. CÁC HÀM LUẬN LÝ
1. AND(logical1, logical2, …)
Trả về TRUE nếu tất cả các biểu thức luận lý logical1, logical2, … đều có giá trị TRUE, trả về FALSE nếu chỉ cần 1 biểu thức luận lý logical trả về FALSE
Ví dụ: AND(12>=5, 8+4/2<9, 32=31+1) FALSE
AND(12>=5, 8+4/2>9, 32=31+1) TRUE
2. OR(logical1, logical2, …)
Trả về TRUE nếu chỉ cần 1 biểu thức luận lý logical trả về TRUE, nếu tất cả các biểu thức luận lý có giá trị FALSE thì trả về FALSE
Vd: OR(12=5, 8+4/2<9, 32>=31+1) FALSE
OR(12=5, 8+4/2<9, 32=31+1) TRUE
3. NOT(logical)
Trả về TRUE nếu logical có giá trị FALSE và ngược lại
Ví dụ: NOT(10>5) FALSE
IV. CÁC HÀM ĐIỀU KIỆN
1. IF(logical_test, value_if_true, value_if_false)
Định trị biểu thức logical_test. Nếu là TRUE thì thực hiện biểu thức value_if_true, ngược lại thực hiện value_if_false
Ví dụ : IF(A5<20, “Nho hon 20”, “Lon hon hay bang 20”)
Nếu nội dung ô A5 nhỏ hơn 20 thì trả về chuỗi “Nho hon 20”, ngược lại trả về chuỗi “Lon hon hay bang 20”
2. COUNTIF(range, criteria)
Trả về 1 số nguyên là số ô nằm trong khối range mà thỏa mãn điều kiện criteria
Trong đó range là địa chỉ khối, criteria là điều kiện là số, biểu thức hay chuổi như 32, “32”, “>=30”, “apples”
Giả sử khối A1:A4 chứa lần lượt các giá trị 100, 200, 300, 400
COUNTIF(A1:A4, “>150”) 3
3. SUMIF(range, criteria, sum_range)
Cộng dồn những ô của sum_range mà dòng đó thỏa mãn điều kiện của criteria
P Tổng số ngày công của Tổ 1: =SUMIF(B4:B8, 1, D4:D8) 63
P Tổng số nhân viên của Tổ 1: =COUNTIF(B4:B8,1) 2
P Tổng số nhân viên có ngày công trên 21 =COUNTIF(D4:D8, “>21”) 3
V. CÁC HÀM DÒ TÌM
=VLOOKUP(đ/c ô,đ/c bảng tra,cột kết quả,0/1)
Hàm sẽ đem ô đi tra trong cột 1 của bảng tra khi tìm thấy hàm sẽ dừng lại chiếu sang cột kết quả và cho giá trị ở cột kết quả đó.
Khái niệm tìm thấy phụ thuộc vào đối số thứ 4
P Nếu đối số thứ 4 = False ( hoặc 0) thì hàm sẽ tìm chính xác có nghĩa là hàm sẽ dừng lại ở giá trị đúng bằng giá trị của ô
P Nếu đối số thứ 4 = True (hoặc 1) hàm sẽ tìm trong phạm vi có nghĩa là nó sẽ dừng lại ở giá trị lớn nhất chưa vượt quá giá trị của ô, lúc này cột 1 của bảng tra phải được sắp xếp theo chiều tăng dần.
Chú y : Nếu không có đối số thứ 4 Excel sẽ hiểu là True
Nếu dò tìm không thành công hàm sẽ cho kết quả #N/A (Not Available - Bất khả thi)
Ví dụ : Từ cột điểm của BẢNG ĐIỂM và BẢNG TRA (ở dưới) ta phải cập nhật cột Xếp loại và cột Học bổng gài các công thức sau vào 2 ô C3 và D3
C3: =Vlookup(B3,$F$3:$H$7,2)
hoặcVlookup(B3, $F$3:$H$7,2,True)
D3 : =Vlookup(B3, ,$F$3:$H$7,3)
hoặc =Vlookup(B3, ,$F$3:$H$7,3,True)
Bạn sẽ sử dụng hàm Vlookup để giải quyết BÀI TẬP 6
2 =HLOOKUP(địa chỉ ô, địa chỉ bảng tra,hàng kết quả,n) :
Hàm Hlookup có ý nghĩa như hàm Vlookup nhưng hàm sẽ được sử dụng trong trường hợp bảng tra được bố trí theo hàng như sau
| Điểm | 0 | 5 | 7 | 9 | 10 |
| Xếp loại | Kém | TB | Kha | Gioi | XS |
| Học bổng | 0 | 0 | 50000 | 80000 | 100000 |
Hàm sẽ đem ô đi tra trong hàng 1 của bảng tra khi tìm thấy hàm sẽ dừng lại chiếu xuống hàng kết quả và cho giá trị ở hàng kết quả đó.
3 =MATCH(đ/c ô,đ/c bảng tra)
(đối với hàm này bảng tra chỉ là 1 cột hoặc 1 hàng)
Hàm sẽ đem ô đi tra trong bảng và cho biết số thứ tự của ô mà hàm tìm thấy trong bảng.
Khái niệm tìm thấy ở đây cũng có ý nghĩa giống như trong hàm Vlookup.
Số thứ tự của ô (1,2,3,...) được tính từ trên xuống nếu bảng là cột và từ trái qua phải nếu bảng là hàng.
Ví dụ : =Match(B3,F3:F7)=2
=Match(B7,F3:F7)=4
4 =INDEX(Bảng,Hàng,Cột)
Bảng : đ/c của bảng
Hàng : STT của hàng trong bảng
Cột : STT của cột trong bảng
Hàm cho kết quả là gi trị của ô giao nhau giữa stt hàng và stt cột trong bảng
VI. CÁC HÀM CƠ SỞ DỮ LIỆU
1. DSUM(database, field, criteria)
Tìm những mẫu tin trong Database mà thỏa mãn tiêu chuẩn Criteria, gi trị tương ứng của các mẫu tin này ở cột Field sẽ được cộng dồn và trả về
Database: Là danh sách bao gồm cả tiêu đề cột
Field: Địa chỉ của cột cộng dồn
Criteria: Vùng điều kiện, cách thiết đặt giống như Advanced Filter
2. DMAX(database, field, criteria)
Tìm những mẫu tin trong Database mà thỏa mãn tiêu chuẩn Criteria, gi trị tương ứng của các mẫu tin này ở cột Field sẽ được so sánh với nhau, giá trị nào lớn nhất sẽ được trả về
3. DMIN(database, field, criteria)
Tìm những mẫu tin trong Database mà thỏa mãn tiêu chuẩn Criteria, gi trị tương ứng của các mẫu tin này ở cột Field sẽ được so sánh với nhau, giá trị nào nhỏ nhất sẽ được trả về
4. DAVERAGE(Database, Field, Criteria)
Tìm những mẫu tin trong Database mà thỏa mãn tiêu chuẩn Criteria, gi trị tương ứng của các mẫu tin này ở cột Field sẽ được tính trung bình cộng và trả về
0 nhận xét:
Đăng nhận xét