Bài giảng Tin học 2 - Chương 6: Công thức và hàm trong Excel

1CHƯƠNG 6CÔNG THỨC VÀ HÀM TRONG EXCEL6.1. XÂY DỰNG MỘT CÔNG THỨC6.1.1. Cú pháp = (danh sách đối số)Trong đó:Tên hàm do Excel quy định, không phân biệt chữ hoa, chữ thườngCác đối số trong hàm được ngăn cách nhau bởi dấu phẩy (,) hoặc dấu chấm phẩy (;) (có thể thay đổi trong tùy chọn Control Panel\Regional And Language Option)Danh sách các đối số có thể là:Giá trị tự nhập cụ thểĐịa chỉ ô, địa chỉ vùngCác công thứcCó thể lồng nhiều hàm với nhau.2Các bước thực hiệnChọn vị trí nhập hàmNhập hàm: Cách 1: gõ dấu bằng (=) hoặc dấu cộng (+)gõ tên hàmnhập danh sách các đối số kết thúc bằng phím Enter36.1. XÂY DỰNG MỘT CÔNG THỨCCác bước thực hiệnChọn vị trí nhập hàmNhập hàm: Cách 2: Formular  Insert Functions hoặc chọn nhóm hàm trongChọn hàm cần nhậpNhập các đối số theo yêu cầuOK46.1. XÂY DỰNG MỘT CÔNG THỨC6.1. XÂY DỰNG MỘT CÔNG THỨC6.1.2. Các lỗi thông dụng56.1. XÂY DỰNG MỘT CÔNG THỨCMột số nhóm hàm thông dụng6Hàm tài chínhHàm logicHàm văn bảnHàm thời gianHàm tìm kiếm và tham chiếuHàm toán học và lượng giác6.2. SỬ DỤNG CÁC HÀM CÓ SẴN76.2.1. NHÓM HÀM THỐNG KÊ1. HÀM SUMChức năng: tính tổngCú pháp: Sum(number1, [number2], ...) Trong đó: + number 1, 2, có thể là:giá trị số địa chỉ ô, địa chỉ vùng chứa giá trị sốcông thức, hàm có giá trị số82. HÀM AVERAGEChức năng: tính trung bìnhCú pháp: Average(number1, [number2], ...) Trong đó: + number 1, 2, có thể là:giá trị số địa chỉ ô, địa chỉ vùng chứa giá trị sốcông thức, hàm có giá trị số94. HÀM MINChức năng: đưa ra giá trị lớn nhất.Cú pháp: Max (number1, [number2], ...) 10Trong đó: + number1, number2, có thể là: giá trị số; địa chỉ ô, địa chỉ vùng chứa giá trị số; công thức, hàm có giá trị sốChức năng: đưa ra giá trị nhỏ nhấtCú pháp: Min (number1, [number2], ...)3. HÀM MAX 5. HÀM COUNTChức năng: đếm số ô chứa dữ liệu kiểu sốCú pháp: Count(Value1,[Value2],...)11Chức năng: đếm số ô chứ dữ liệu (khác rỗng)Cú pháp: Counta(Value1,[Value2],...) 6. HÀM COUNTATrong đó: + value1, value2, có thể là: dữ liệu nhập vào; địa chỉ ô, địa chỉ vùng chứa dữ liệu; công thức, hàm7. HÀM COUNTBLANKChức năng: đếm số ô không chứa dữ liệu (ô trống, ô rỗng)Cú pháp: CountBlank(range)Trong đó: + range: là vùng cần đếm128. HÀM COUNTIFChức năng: Hàm đếm số ô chứa giá trị thỏa mãn điều kiện.Cú pháp: COUNTIF(Range, Criteria)Trong đó:Range: Vùng (danh sách) chứa các giá trị cần đếmCriteria: điều kiện đếmCó thể dùng ký tự đại diện như:dấu “?”: thay thế 1 ký tự;dấu “*”: thay thế nhiều ký tự 139. HÀM COUNTIFSChức năng: đếm số đối tượng thỏa mãn đồng thời nhiều điều kiệnCú pháp: COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, )Trong đó:range1, range2, range3, : vùng (dãy) các ô chứa giá trị cần đếm.criteria1, criteria2, criteria3, : điều kiện đếm tương ứng14Chức năng: Hàm tính tổng các đối tượng thỏa mãn điều kiệnCú pháp: SUMIF(range, criteria, [sum_range])Trong đó:Range: vùng (danh sách) chứa các giá trị tham chiếu theo điều kiệnCriteria: điều kiện tính tổngSum_range: vùng (danh sách) chứa các giá trị cần tính tổng. Nếu Sum_range trùng với Range thì có thể bỏ qua tham số này.10. HÀM SUMIF1511. HÀM RANKChức năng: Hàm xếp thứ hạngCú pháp: RANK(number; ref; [order])Trong đó:Number: là giá trị cần xếp thứ hạng trong vùng Ref: vùng chứa các giá trị cần được đánh giá xếp thứ hạng Order: kiểu xếp thứ hàng Order=0: xếp theo chiều giảm dần (giá trị mặc định) Order=1 : xếp theo chiều tăng dần166.2. SỬ DỤNG CÁC HÀM CÓ SẴN176.2.2. NHÓM HÀM LOGIC1. HÀM ANDChức năng: Kết hợp các biểu thức logic, trả về giá trị TRUE khi tất cả các biểu thức đều đúng, trả về giá trị FALSE khi có ít nhất một biểu thức sai.Cú pháp: AND(Logical1, [Logical2], )2. HÀM ORChức năng: Kết hợp các biểu thức logic, trả về giá trị TRUE khi có ít nhất một biểu thức đúng, trả về giá trị FALSE khi tất cả các biểu thức đều sai.Cú pháp: OR(Logical1, [Logical2], )3. HÀM NOTChức năng: Phép phủ định, đổi TRUE thành FALSE hoặc FALSE thành TRUECú pháp: NOT(Logical)Trong đó: + Logical: là biểu thức logic, thường chứa các phép so sánh.Bảng LogicVí dụ: B3=24 AND (B3>=23,B322)  True AND (B3>=23,B3=23,B322)  True OR (B3=23,B325,B3, =, , ) trả về giá trị logic. Nếu có nhiều biểu thức điều kiện thì sử dụng hàm AND hoặc ORvalue_if_true: giá trị trả về khi điều kiện đúngvalue_if_false: giá trị trả về khi điều kiện sai4. HÀM IF- Ví dụ 1:Điền giá trị cho cột KẾT QUẢ: + Nếu ĐIỂM TRUNG BÌNH >=5 là “Đỗ”, ngược lại là “Trượt”4. HÀM IFVí dụ 2: Điền giá trị cho cột HB + Nếu ĐTB>=8 180000 + Nếu ĐTB>=7 và ĐTB 0; mặc định là 1Chức năng: rút trích ra một số ký tự trong chuỗi, tính từ phải sangCú pháp: Right(text, [num_chars]) 2. HÀM LEFT 4. HÀM MIDChức năng: rút trích một số ký tự trong chuỗi, bắt đầu từ vị trí được chỉ ra tính từ trái sangCú pháp: Mid(text, start_num, num_chars) Trong đó: + text: chuỗi ký tự + start_num: số thứ tự của ký tự rút trích đầu tiên start_num >0 nếu start_num > độ dài của chuỗi thì kết quả trả về là rỗng + num_chars: số ký tự rút trích ra; num_chars > 0; 265. HÀM UPPERChức năng: chuyển tất cả các ký tự trong chuỗi thành ký tự hoaCú pháp: Upper(text)27Chức năng: chuyển tất cả các ký tự trong chuỗi thành ký tự thườngCú pháp: Lower(text)6. HÀM LOWER7. HÀM PROPERChức năng: (chuẩn hóa chuỗi) chuyển ký tự đầu tiên của từng từ trong chuỗi thành ký tự hoa. Các ký tự khác dạng chữ thường.Cú pháp: Proper(text)288. HÀM TRIMChức năng: (chuẩn hóa chuỗi) loại bỏ các ký tự trắng không cần thiết trong chuỗiCú pháp: Trim(text)9. HÀM SUBSTITUTEChức năng: thay thế một số ký tự trong chuỗi bằng một số ký tự khácCú pháp: Substitute(text, old_text, new_text [,instance_num]) Trong đó: + text: chuỗi ký tự gốc + old_text: chuỗi ký tự cũ cần được thay thế + new_text: chuỗi ký tự mới sẽ thay thế vào + instance_num: số lần thay thế , nếu bỏ qua thì tất cả chuỗi ký tự cũ sẽ được thay thế bằng chuỗi ký tự mới.2910. HÀM REPLACEChức năng: thay thế một số ký tự trong chuỗi bằng một số ký tự khác bắt đầu từ vị trí được chỉ ra.Cú pháp: Replace(old_text, start_num, num_chars, new_text) Trong đó: + text: chuỗi ký tự gốc + start_num: vị trí bắt đầu thay thế, nếu start_num>số ký tự của chuỗi gốc thì sẽ ghép chuỗi mới vào cuối chuỗi gốc + num_char: số ký tự thay thế + new_text: chuỗi ký tự mới sẽ thay thế vào3011. HÀM REPTChức năng: lặp lại một số ký tự với số lần cho trướcCú pháp: Rept(text, number_times) Trong đó: + text: ký tự được lặp + number_times: số lần lặp3112. HÀM SEARCHChức năng: tìm vị trí xuất hiện của một số ký tự trong một chuỗi văn bản, không phân biệt ký tự hoa hay ký tự thường.Cú pháp: Search(find_text, within_text, [start_num]) Trong đó: + find_text: chuỗi ký tự cần tìm + within_tex: chuỗi văn bản gốc chứa một số ký tự cần tìm + start_num: vị trí bắt đầu tìm, mặc định là 1 Nếu tìm không thấy hàm sẽ báo #Value!3213. HÀM VALUEChức năng: chuyển đổi kiểu dữ liệu từ chuỗi sang số.Cú pháp: Value(text) Trong đó: + text: chuỗi ký tự cần chuyển đổi33346.2. SỬ DỤNG CÁC HÀM CÓ SẴN6.2.4NHÓM HÀM TOÁN HỌC1. HÀM ABSChức năng: lấy giá trị tuyệt đối của một sốCú pháp: ABS(Number)2. HÀM SQRTChức năng: lấy giá trị căn bậc 2 của một sốCú pháp: SQRT(Number)3. HÀM FACTChức năng: tính giai thừa của một số. Giai thừa (n) = 1*2*3**nCú pháp: FACT(Number)Trong đó: + Number: là một số; địa chỉ ô chứa giá trị số; biểu thức hoặc hàm trả về giá trị số.4. HÀM INTChức năng: lấy phần nguyên của một số và số nguyên đó phải nhỏ hơn hoặc bằng number.Ví dụ: =INT(8.9) kết quả là 8, INT(-8.9) kết quả là -9.Cú pháp: INT(Number)5. HÀM MODChức năng: lấy phần dư của phép chia Number cho divisorCú pháp: MOD(Number, divisor)Trong đó: + Number: là một số; địa chỉ ô chứa giá trị số; biểu thức hoặc hàm trả về giá trị số. + divisor: số chiaVD: =MOD(23,4) kết quả là 3.6. HÀM ROUNDChức năng: làm tròn giá trị một số với chỉ số cho trước. Cú pháp: ROUND(Number, number_digits)Trong đó: + Number: giá trị số cần làm tròn + divisor: chỉ số làm tròn - Nếu divisor =n >0: làm tròn vị trí thứ n số phần thập phân. - Nếu divisor =n=0: làm tròn phần nguyên (mặc định) - Nếu divisor =n số cột trong table_array, hàm sẽ báo lỗi #REF! Bảng tham chiếu trong công thức nên đặt địa chỉ tuyệt đối.Chú ýChức năng: đưa ra vị trí của giá trị cần tìm trong vùng dữ liệu được chỉ raCú pháp: Match(lookup_value, lookup_array, [match_type])Trong đó: lookup_value: giá trị cần tìm lookup_array: vùng tìm kiếm, chỉ có thể trên một hàng hoặc một cột match_type: kiểu dò tìm, có ba giá trị sau:= 1(Less than): tìm trong khoảng nhỏ, vùng tìm kiếm sắp xếp tăng dần.= 0 (Exact match): tìm chính xác, vùng tìm kiếm không cần sắp xếp.= -1(Greater than): tìm trong khoảng lớn, vùng tìm sắp xếp giảm dần3. HÀM MATCHVí dụ54Chức năng: đưa ra giá trị của ô ứng với dòng và cột được chỉ ra trong bảng dữ liệu cần tìm kiếmCú pháp: Index(array, row_num,[column_num])Trong đó: array: vùng tìm kiếm row_num: stt của dòng trong bảng tìm kiếm column_num: stt của cột trong bảng tìm kiếm4. HÀM INDEXChức năng: đưa ra giá trị có số thứ tự trong danh sách các giá trị được chỉ raCú pháp: Choose(index_num,value1, [value2], )Trong đó: index_num: là số thứ tự của giá trị cần lấy ra trong danh sách value1, value2,: danh sách các giá trị, từ 1 đến 254.5. HÀM CHOOSE576.2. SỬ DỤNG CÁC HÀM CÓ SẴN6.2.7NHÓM HÀM TÀI CHÍNHGIỚI THIỆU CÁC THAM SỐrate: lãi suất %nper (number of periods): số kỳ (thời gian) đầu tưpmt (payment made each period): giá trị tài khoản đầu tư (phải trả) trong mỗi kỳpv (present value): giá trị tài khoản ban đầufv (future value): giá trị tài khoản tương laitype: kiểu thanh toán. =1: thanh toán đầu kỳ;= 0: thanh toán cuối kỳChú ý:Tài khoản bỏ ra mang giá trị âm, tài khoản nhận về mang giá trị dươngTham số nào nằm trong dấu [ ] là tham số tùy chọn (có thể nhập hoặc không)1. HÀM RATEChức năng: tính lãi suấtCú pháp: RATE (nper, pmt, pv, [fv], [type]) Ví dụ: Gửi tiền vào ngân hàng, cứ mỗi năm gửi $1000. Sau 5 năm nhận được số tiền là $6000. Tính lãi suất?=Rate (5, -1000, 0,6000,0)2. HÀM NPERChức năng: tính số kỳ đầu tưCú pháp: NPER (rate, pmt, pv, [fv], [type]) Ví dụ: Một dự án có mức đầu tư ban đầu là 200 triệu đồng. Sau đó cứ cuối mỗi năm đầu tư thêm 50 triệu đồng nữa. Với mức lãi suất là 35%/ năm. Hỏi sau bao lâu sẽ có được 1tỷ (1000 triệu)= Nper(35%, -50, -200, 1000, 0)3. HÀM PMTChức năng: tính giá trị tài khoản đầu tư (phải trả) theo định kỳCú pháp: PMT (rate, nper, pv, [fv], [type]) Ví dụ: Mua một xe máy theo hình thức trả góp với giá 27 triệu đồng, lãi suất là 6% / năm. Trả trong 18 tháng cả gốc và lãi vào cuối mỗi tháng. Tính số tiền người mua phải trả hàng tháng?= Pmt (6%/12, 18, 27, 0, 0)4. HÀM PVChức năng: tính giá trị tài khoản đầu tư (phải trả) ban đầuCú pháp: PV (rate, nper, pmt, [fv], [type]) Ví dụ: Đầu tư vào một công ty 270 triệu đồng trong 3 năm. Cuối năm thứ ba nhận được 300 triệu đồng, lãi suất 10%/năm. Xét đầu tư này có lợi hay không? = Pv(10%, 3, 0, 300, 0) = 225.39Đầu tư vào một công ty 230 triệu đồng trong 3 năm. Cuối mỗi năm nhận được 100 triệu đồng, lãi suất 10%/ năm. Xét đầu tư này có lợi hay không? = Pv (10%, 3, 100, 0,0) = 248.695. HÀM FVChức năng: tính giá trị tài khoản tương laiCú pháp: FV (rate, nper, pmt, [pv], [type]) Ví dụ: Đầu tư vào một dự án trong 2 năm, mỗi năm là 1000$, lãi suất 10%/năm. Tính số tiền nhận được sau 2 năm? = Fv(10%, 2, -1000, 0,0)Gửi tiết kiệm ban đầu là 100 triệu, mỗi tháng gửi thêm 2 triệu, lãi suất 10%/năm. Tính số tiền nhận được sau 2 năm, với hình thức thanh toán đầu kỳ? = Fv (10%/12, 24, -2, -100, 1)