Bài giảng Các hàm mẫu trong Excel

30292827262524232221201918171615141312111009080706050403020100TÍNH TOÁN Trong BẢNG-CÁC HÀM MẪU Trong EXCELGV: NGÔ THÙY LINHCHỦ ĐỀ 5: BẢNG TÍNH ĐIỆN TỬ MS EXCEL(TIẾT 2)NỘI DUNG 3: NỘI DUNGHÀM COUNTIFHÀM SUMIFHÀM RANKCỦNG CỐ2Sumif13Rank1Countif1. Cú pháp2. Chức năngNội dung thảo luận nhóm1. Cú pháp2. Chức năng1. Cú pháp2. Chức năng7. HÀM COUNTIF: ĐẾM GIÁ TRỊ THỎA MÃN ĐIỀU KIỆNRANGECRITERIAa. CÚ PHÁP:=COUNTIF(RANGE, CRITERIA)=COUNTIF(PHẠM VI, ĐIỀU KIỆN)b. CÔNG DỤNG:Đếm số ô thỏa mãn điều kiện trong phạm vi. Điều kiện là một con số chính xác hoặc địa chỉ thì không đặt trong nháy kép. Các trường hợp còn lại điều kiện đặt trong nháy kép.Điều kiện không phân biệt chữ hoa chữ thườngĐiều kiện là tham chiếu ô: Phải đặt toán tử trong dấu ngoặc kép và thêm ký hiệu (&) trước tham chiếu ô. Ví dụ: =COUNTIF(D2:D9,”>”&D3)Hàm COUNTIF hỗ trợ các toán tử logic: (>, , =) Sử dụng các ký tự đại diện: ? : Đại diện 1 ký tự, * : Đại diện nhiều ký tựHaøm CountIfGiải thích : Range: một dãy các ô cần đếm, giá trị các ô có thể là số, ngày, text, hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua không đếm.Criteria: điều kiện để đếm (số, chuỗi,tham chiếu ô, biểu thức, ). Chú ý: Trường hợp 1: Hàm COUNTIF cho văn bản và số (chính xác tuyệt đối)Haøm CountIf= COUNTIF (D2: D9,5)  đếm các ô trong vùng D2:D5 có giá trị 5= COUNTIF (C2: C15, “Táo”).  Đếm các ô trong vùng C2:C15 có giá trị là TáoHaøm CountIf Trường hợp 2: Hàm COUNTIF đếm lớn hơn, nhỏ hơn hoặc bằngĐiều kiệnVí dụ về công thứcGiải thíchlớn hơn=COUNTIF(A2:A10,”> 5″)Đếm các ô trong đó giá trị lớn hơn 5.nhỏ hơn=COUNTIF(A2:A10,”5″)Đếm các ô trong đó giá trị không bằng 5.lớn hơn hoặc bằng=COUNTIF(C2:C8, “> = 5”)Đếm các ô trong đó giá trị lớn hơn hoặc bằng 5.nhỏ hơn hoặc bằng=COUNTIF(C2:C8,”=6/1/2020″)Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng 6/1/2020Đếm ngày lớn hơn hoặc bằng ngày trong một ô khác trừ đi x ngày=COUNTIF(B2:B10,”>=”&B2-“7”)Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng ngày trong ô B2 trừ đi 7 ngàyHaøm CountIfTrường hợp 4: Sử dụng hàm COUNTIF với ngày:Ví dụ 1: Cho bảng lương sau:Đếm số nhân viên có Chức vụ là TP =COUNTIF(E3:E12, “TP”)Đếm số nhân viên có họ TrầnĐếm số nhân viên có ngày làm việc trên 25 ngày =COUNTIF(B3:B12, “Trần*”)=COUNTIF(G3:G12,”>25”)=COUNTIF(F3:F12, 2500)Đếm số nhân viên có Lương CB là 2500Đếm số nhân viên có Chức vụ là TP =COUNTIF(E3:E12, “TP”)Đếm số nhân viên có họ TrầnĐếm số nhân viên có ngày làm việc trên 25 ngày =COUNTIF(B3:B12, “Trần*”)=COUNTIF(G3:G12,”>25”)=COUNTIF(F3:F12, 2500)Đếm số nhân viên có Lương CB là 2500Ví dụ 1: Cho bảng lương sau:8. HÀM SUMIF (Tính tổng thỎA mãn theo điều kiện) RANGECRITERIASUM RANGEa. CÚ PHÁP:=SUMIF(RANGE, CRITERIA, SUM RANGE)=SUMIF(VÙNG ĐIỀU KIỆN, ĐIỆU KIỆN, VÙNG TÍNH TỔNG)b. CÔNG DỤNGHàm dùng để tính tổng có điều kiện. Chỉ những ô nào trên vùng chứa điều kiện thoả mãn điều kiện thì sẽ tính tổng những ô tương ứng trên vùng cần tính tổng. Haøm SumIfGiải thích : Range: Vùng dữ liệu có chứa điều kiện cần tính tổng.Criteria: Là điều kiện cần tính tổngSum_range : Vùng cần tính tổng thoã mãn theo điều kiện. Kết quả của hàm là tính tổng các giá trị thoã mãn theo điều kiện.Chú ý: Tương tự hàm COUNTIF Điều kiện là một con số chính xác hoặc địa chỉ thì không đặt trong nháy kép. Các trường hợp còn lại điều kiện đặt trong nháy kép.Điều kiện không phân biệt chữ hoa chữ thườngĐiều kiện là tham chiếu ô: Phải đặt toán tử trong dấu ngoặc kép và thêm ký hiệu (&) trước tham chiếu ô. Hỗ trợ các toán tử logic: (>, , =) Sử dụng các ký tự đại diện: ? : Đại diện 1 ký tự, * : Đại diện nhiều ký tự1. Tính tổng lương cho cho nhân viên có giới tính là Nam 2. Tính tổng thực lĩnh cho nhân viên có lương >7,000,000Ví dụ 2Ví dụ 2: 9. HÀM RANK: XẾP THỨ HẠNG CHO DANH SÁCHNUMBERREF[ORDER]a. CÚ PHÁP:=RANK(NUMBER, REF, [ORDER])=Rank(Số cần xác định thứ hạng, Vùng quy chiếu, Số đặc tả cách xếp hạng)b. CÔNG DỤNG:Hàm 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 một số là thứ hạng giá trị của nó so với các giá trị khác trong danh sách sốHaøm RankGiải thích : Number: Là giá trị cần xếp vị thứ bao nhiêu trong dãy dữ liệu Ref: Vùng dữ liệu chứa giá trị Number để xếp vị thứ Order: Có hai giá trị là 0 hay 1, Mặc định là 0. Nếu Order=1 thì giá trị nhỏ nhất đựơc xếp thứ nhất Nếu Order=0 thì giá trị lớn nhất được xếp thứ nhất Kết quả: Là giá trị đứng thứ mấy trong dãy số trên. Haøm Rank1. Xếp thứ hạng cho danh sách theo cột Lương giảm dần=RANK(D4,$D$4:$D$8,0)Ví dụ 3. Cho bảng tính sau:Thể lệ : Mỗi đội thi thực hành bài tập excel trên máy tính. Trong thời gian 5 phút đội nào có kết quả đúng nhiều nhất và nhanh nhất sẽ là đội chiến thắngTĂNG TỐCPHIẾU HỌC TẬPXếp hạng danh sách theo tiêu chí điểm trung bình giảm dầnCỦNG CỐ BÀI HỌC010203=RANK(NUMBER, REF, [ORDER])XẾP THỨ HẠNG CHO DANH SÁCH=SUMIF(RANGE, CRITERIA, SUM RANGE)TÍNH TỔNG CÓ ĐIỀU KIỆN=COUNTIF(RANGE, CRITERIA)ĐẾM CÓ ĐIỀU KIỆNThể lệ : Mỗi đội lần lượt trả lời câu hỏi. Trước khi trả lời mỗi đội quay vào ô may mắn để chọn điểm. Nếu trả lời sai bị mất điểm đội khác có quyền trả lời và được điểm. VỀ ĐÍCHPointsSpin Wheel1020304050607080Team 1Team 2Team 3Team 4VỀ ĐÍCH30292827262524232221201918171615141312111009080706050403020100123456789A. =SUMIF(C2:C14,”Kinh doanh” ,G6:G15)điền vào chỗ trống ?Đếm số học sinh học lực Khá trong danh sách có công thức là =COUNTIF(D1:D11,Giỏi). Theo em công thức này:ĐúngSaiTrong cú pháp hàm: =RANK(NUMBER, REF, [ORDER]) thì đối số REF là: A. Cách xếp thứ hạng B. Số cần biết thứ hạng C. Vùng quy chiếu D. Các ý trên đều saiCăn cứ vào kết quả của cột thứ hạng em hãy cho biết công thức tại ô C2 sẽ là=RANK(C2, $C$2:$C$7,0)= RANK(C2, $C$2:$C$7,1)Công thức tính số nhân viên có ngày nghỉ trong tháng là: ..Từ khóa