Bài giảng Excel - Chương 2: Hàm và biểu thức trong Excel
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Excel - Chương 2: Hàm và biểu thức trong Excel", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên
Tài liệu đính kèm:
- bai_giang_excel_chuong_2_ham_va_bieu_thuc_trong_excel.pdf
Nội dung text: Bài giảng Excel - Chương 2: Hàm và biểu thức trong Excel
- Chương 2. HÀM VÀ BIỂU THỨC TRONG EXCEL
- CÁC TỐN TỬ TRONG EXCEL 3/11/2004 Bài giảng Excel 2
- Hàm luận lý 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,D3<23) NOT (logic) Trả về TRUE nếu cĩ ít nhất một logic của nĩ là TRUE, trả về False nếu tất cả các logic là False 3/11/2004 Bài giảng Excel 3
- Hàm số học Hàm lấy giá trị tuyệt đối ABS(N) N là số / biểu thức số Ví dụ: =ABS(-25) kết quả 25 =ABS(5-149) kết quả 144 Hàm lấy căn SQRT(N) N là số / biểu thức số (N>0) Ví dụ: SQRT(25) kết quả 5 3/11/2004 Bài giảng Excel 4
- Hàm số học Hàm lấy phần nguyên INT(N) N là số / biểu thức số Ví dụ: =INT(236.26) kết quả 236 Hàm lấy phần dư phép chia nguyên N cho M MOD(N, M) Ví dụ: MOD(10;3) kết quả 1 Hàm lấy giá trị PI (3.141593) PI() Hàm mũ POWER(x ; y) Trả về x mũ y Ví dụ: =power(4 ; 2) kết quả 16 3/11/2004 Bài giảng Excel 5
- Hàm số học Hàm làm trịn lên ROUND(biểu thức số ; 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ẻ Ví dụ: =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 3/11/2004 Bài giảng Excel 6
- Hàm thống kê SUM (đối 1, đối 2, , đối n): cho 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): cho giá trị TBC c các số 3/11/2004 Bài giảng Excel 7
- Hàm thống kê MAX (đối 1, đối 2, , đối n): cho giá trị lớn nhất. MIN (đối 1, đối 2, , đối n): cho giá trị nhỏ nhất. 3/11/2004 Bài giảng Excel 8
- Hàm thống kê COUNT(đối1, đối2, ): đếm số lượng các ơ cĩ chứa số và các số trong các đối số. Các đối số là các số, ngày tháng, địa chỉ ơ, địa chỉ miền. Ví dụ 1 Ví dụ 2 3/11/2004 Bài giảng Excel 9
- Hàm thống kê COUNTA(value1; value2; .) Đếm số lượng các ơ chứa dữ liệu trong danh sách 3/11/2004 Bài giảng Excel 10
- 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 3/11/2004 Bài giảng Excel 11
- Hàm thống kê Khi thứ thự xếp bằng 1 Khi thứ thự xếp bằng 0 3/11/2004 Bài giảng Excel 12
- Hàm thống kê Hàm tính tổng của các tích SUMPRODUCT (array1; [array2]; ) = 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. 3/11/2004 Bài giảng Excel 13
- CÁC HÀM ĐIỀU KIỆN IF (bt logic, trị đúng, trị sai): Hiển thị trị đúng nếu BT logic cĩ g/t True Hiển thị trị sai nếu BT logic cĩ g/t False VD: =IF(A3>=5,“Đỗ”,“Trượt”) - Hàm IF cĩ thể viết lồng nhau. VD: = IF(C6 400,3,2)) - Hàm trên cho kết quả của phép thử sau: 1 nếu [dữ liệu trong ơ C6] ≤ 300 2 nếu 300 400 3/11/2004 Bài giảng Excel 14
- CÁC HÀM ĐIỀU KIỆN SUMIF (miền_đ/k, đ/k, miền_tổng): hàm tính tổng cĩ điều kiện Giả sử miền B2:B5 chứa các g/t tiền nhập 4 mặt hàng tương ứng 100, 200, 300, 400. Miền C2:C5 chứa tiền lãi tương ứng 7, 14, 21, 28 thì hàm SUMIF(B2:B5,“>160”,C2:C5) cho kết quả bằng 63 (=14+21+28) 3/11/2004 Bài giảng Excel 15
- 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 thoả mãn điều kiện. Ví dụ 1 Ví dụ 2 3/11/2004 Bài giảng Excel 16
- 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 của 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”,9,2) cho kết quả là chuỗi “Hà” 3/11/2004 Bài giảng Excel 17
- Hàm chuỗi Hàm tính chiều dài của chuỗi LEN (“chuỗi”) Trả là chiều dài trong chuỗi, tức là số ký tự cĩ trong chuỗi Ví dụ: =LEN (“Microsoft Excel”) kq: 15 Hàm đổi chuỗi thường LOWER (“chuỗi”) Ví dụ: =LOWER(“KIẾN ĂN CÁ”) kq: “kiến ăn cá” 3/11/2004 Bài giảng Excel 18
- Hàm chuỗi Hàm đổi chuỗi hoa UPPER (“chuỗi”) Ví dụ: =UPPER(“kiến ăn cá”) kq: “KIẾN ĂN CÁ” Hàm đổi các ký tự đầu của “word” thành hoa PROPER (“chuỗi”) Ví dụ: =PROPER(“kiến ăn cá”) kq: “Kiến Ăn Cá” 3/11/2004 Bài giảng Excel 19
- Hàm chuỗi Hàm xố khoảng trống thừa trong chuỗi TRIM (“chuỗi”) Ví dụ: =TRIM (“ Xin Chào ”) kq: “Xin chào” Hàm đổi chuỗi số thành trị số VALUE (“chuỗi số”) Ví dụ: =VALUE (“123”) kq: 123 3/11/2004 Bài giảng Excel 20
- Hàm kiểu ngày WEEKDAY(“chuỗi ngày”) Trả về giá trị Thứ trong tuần (từ 1 đến 7: chủ nhật đến thứ hai) Ví dụ: =WEEKDAY(“2/09/2001”) kq:1 TODAY () Trả về ngày hiện tại của hệ thống máy tính Ví dụ: =TODAY() kq: 10/05/2007 3/11/2004 Bài giảng Excel 21
- Hàm kiểu ngày YEAR (“chuỗi ngày”) Trả về số năm tương ứng (1900 đến 2078) Ví dụ: =YEAR(“24/12/2004”) kq: 2004 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” 3/11/2004 Bài giảng Excel 22
- 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 g/t với các g/t 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ố 3. VD: = VLOOKUP(E3, $E$12:$F$16, 2, True) - Nếu g/t tra cứu nhỏ hơn g/t 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 g/t trong cột đầu tiên của bảng phải được sắp xếp tăng dần. + Nếu g/t tra cứu khơng cĩ trong cột đầu tiên của bảng thì hàm sẽ tra cứu g/t trong bảng ≤ g/t tra cứu. 3/11/2004 Bài giảng Excel 23
- Hàm dị tìm 3/11/2004 Bài giảng Excel 24
- Hàm dị tìm HLOOKUP(g/t, bảng_g/t, 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. 3/11/2004 Bài giảng Excel 25
- 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 hay bằng lookup_value Lookup_ref: phải được sắp theo thứ tự tăng dần Nếu tất cả trong lookup_ref đều nhỏ hơn lookup_value thì hàm trả về #NA -1: Tìm giá trị nhỏ nhất lớn hơn hay bằng lookup_value Lookup_ref: phải được sắ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 3/11/2004 Bài giảng Excel 26
- Hàm dị tìm 3/11/2004 Bài giảng Excel 27
- Hàm dị tìm INDEX(array,row_num,column_num): Trả về giá trị của ô tại dòng và cột trong một dãy ô Pear 3/11/2004 Bài giảng Excel 28
- Hàm tài chính SLN(const, salvage, life) Cơng dụng: Tính khấu hao đối với một thiết bị cĩ chi phí ban đầu bởi const, giá trị cịn lại là cuối thời gian sống của thiết bị định bởi salvage và thời gian thiết bị định bởi life bằng cách chia đều khấu hao cho các năm. Cơng thức tính khấu hao: (const/life)-(salvage/life) Ví dụ: một thiết bị được mua với giá 40000, sau 5 năm trị giá của máy cịn lại 12000. Tính chi phí khấu hao hàng năm của thiết bị trên. SLN(40000,12000,5) = 5600 Tổng cộng khấu hao sau 5 năm là 5600*5 3/11/2004 Bài giảng Excel 29
- Hàm tài chính Hàm: SYD(const, salvage, life, nper) Cơng dụng: tính khấu hao tại một chu kỳ định bởi Nper bằng cách dùng phương pháp tính khấu hao huỹ kế (giảm dần theo từng năm) Cơng thức tính khấu hao ((cost - salvage)*(life-nper+1)*2)/(life*(life+1)) 3/11/2004 Bài giảng Excel 30
- Hàm tài chính Ví dụ: một thiết bi mới mua với giá 40000, sau 5 năm trị giá của máy cịn 12000 tính chi phí khấu hao hàng năm của thiết bị trên. Năm thứ 1 = SYD(40000, 12000, 5, 1)= 9333 Năm thứ 2 = SYD(40000, 12000, 5, 2)= 7467 Năm thứ 3 = SYD(40000, 12000, 5, 3)= 5600 Năm thứ 4 = SYD(40000, 12000, 5, 4)= 3733 Năm thứ 5 = SYD(40000, 12000, 5, 5)= 1867 Tổng cộng tiề khấu hao sau 5 năm = 28000 = (9333 + 7476 + 5600 + 3733 + 1867) 3/11/2004 Bài giảng Excel 31
- Hàm tài chính Hàm IRR : IRR(value, geuss) Cơng dụng: tính lãi suất thu nhâp bộ của một dãy số biểu thị thu nhập thường kỳ định bởi tham số value. Tham số guess (từ 0 đến 1) ước lượng tỉ lệ thu nhập cho một chu kỳ. 3/11/2004 Bài giảng Excel 32
- Hàm tài chính Ví dụ: Giả sử để kinh doanh 1 ngành hàng, bạn cần đầu tư 70000 và mong muốn thu nhập trong 5 năm đầu tiên với các giá trị lần lượt là 12000, 15000, 18000, 21000 và 26000 A B 1 NĂM SỐ TIỀN 2 -70000 3 1 12000 4 2 15000 5 3 18000 6 4 21000 7 5 26000 Lãi suất thu nhập nội bộ được tính bởi hàm IRR như sau: Sau 2 năm: = IRR(B2:B4) kết quả là -44.35% Sau 3 năm: = IRR(B2:B5) kết quả là -18.21% Sau 4 năm: = IRR(B2:B6) kết quả là -2.12% Sau 5 năm: = IRR(B2:B7) kết quả là -8.66% 3/11/2004 Bài giảng Excel 33
- Hàm tài chính Hàm PMT : PMT(Rate, Nper, Pv, Fv, Type) Cơng dụng: tính số tiền phải trả vào mỗi chu kỳ ứng với các đối số cho trước. Lãi suất rate, số chu kỳ nper, giá trị hiện tại Pv, giá trị tương lai Fv, kiểu thanh tốn Type. Nếu bỏ qua đối số Fv và tyoe thì Excel sẽ lấy giá trị mặc nhiên là 0. Ví dụ: Mua căn nhà trị giá 190000 và trả gĩp theo lãi suất cố định là 10%/năm trong vịng 30 năm. Như vậy hàng tháng phải trả số tiền là: =PMT(0.1/12,12*30,190000) kết quả -1667 (giá trị âm đĩ là số tiền phải trả ) 3/11/2004 Bài giảng Excel 34
- Hàm tài chính Hàm rate : rate(nper, pmt, Pv, Fv, Type, Guess) Cơng dụng: tính lãi suất hàng năm theo các đối số cho trước Nếu bỏ qua các đối số Fv và Type,guess thì excel sẽ lấy giá trị mặc nhiên là 0 Ví dụ: mua một chiếc xe trị giá 9000 và trả gĩp hàng tháng 800 trong 12 tháng. Vậy lãi suất là RATE(12,-800,9000) = 1.007% (tháng). Do đĩ lãi suất một năm sẽ là 1.007%*12 = 12.084% 3/11/2004 Bài giảng Excel 35
- Hàm tài chính Hàm FV: FV(rate, nper, pmt, Pv, Type) Cơng dụng: tính giá trị kết qủa vào cuối thời gian đầu tư Ví dụ: Số tiền bỏ ra ban đầu là 2000, sau đĩ bỏ thêm vào mỗi tháng 100% trong vịng 5 năm (60 tháng) lãi suất hàng năm là 8%. Giá trị kết quả đầu tư như sau: =FV(0.08/12,5*12,-100,-2000,1)=10376.36 3/11/2004 Bài giảng Excel 36
- Hàm tài chính Hàm PV: PV(Rate, Nper, Pmt, Fv, Type) Cơng dụng: Tính giá trị hiện tại ứng với số tiền khơng đổi PMT phải trả thường trong vịng Nper chu kỳ với lãi suất Rate. Hàm PV cho kết quả là tổng số tiền vào thời điểm hiện tại cĩ giá trị ứng với tồn bộ số tiền thanh tốn trong tương lai vào mỗi chu kỳ theo lãi suất Rate. Ví dụ: Dự định trả gĩp 1 xe hơi và trả gĩp hàng tháng là 220$. Trong vịng 4 năm với lãi suất vay là 9%. Như vậy số tiền phải trả gĩp ứng với hiện tại là: =PV(0.09/12, 4*12, -220) = 8840.65 3/11/2004 Bài giảng Excel 37