Tìm hiểu về các công thức logic như các câu lệnh IF nổi tiếng đại diện cho trái tim của Excel.
Các hàm Excel, hoặc các công thức, nằm ở trung tâm của khả năng sâu của ứng dụng. Hôm nay chúng ta sẽ giải quyết các câu lệnh IF, một chuỗi các lệnh xác định liệu một điều kiện có được đáp ứng hay không. Giống như một câu hỏi có-không, nếu điều kiện được chỉ định là đúng, Excel trả về một giá trị do người dùng xác định và, nếu sai, nó trả về giá trị khác.
Câu lệnh IF cũng được biết đến như một công thức logic: IF, sau đó, khác. Nếu một cái gì đó là sự thật, sau đó thực hiện điều này, khác / khác làm điều đó. Ví dụ, nếu trời mưa, sau đó đóng cửa sổ, khác / khác rời khỏi cửa sổ đang mở.
Cú pháp [hoặc cấu trúc câu, có nghĩa là cách thức các lệnh được tổ chức trong công thức] của câu lệnh IF của IF là: = IF [logic_test, value_if true, value_if_false]. Các câu lệnh IF được sử dụng trong tất cả các ngôn ngữ lập trình và, mặc dù cú pháp có thể thay đổi đôi chút, hàm này cung cấp các kết quả tương tự.
Ghi nhớ: Học các hàm / công thức của Excel và cách chúng hoạt động là các bước đầu tiên hướng tới việc sử dụng Visual Basic, ngôn ngữ lập trình hướng sự kiện của Microsoft. Chúng ta sẽ bắt đầu với ba câu lệnh IF dễ dàng, sau đó chuyển sang câu lệnh IF lồng nhau và câu lệnh IF đa hàm. Cuối cùng, chúng tôi sẽ thích thú với COUNTIF, SUMIF, AVERAGEIF và IF với các ký tự đại diện.
1. Thông báo quá hạn
Trong bảng tính này, ngày đến hạn thanh toán của khách hàng được liệt kê trong cột A, trạng thái thanh toán được hiển thị trong cột B và tên công ty của khách hàng nằm trong cột C. Kế toán công ty nhập ngày mà mỗi thanh toán đến, tạo bảng tính Excel này . Người kế toán nhập một công thức trong cột B để tính toán khách hàng nào quá 30 ngày quá hạn, sau đó gửi thông báo trễ cho phù hợp.
A. Nhập công thức: =TODAY[]trong ô A1, hiển thị dưới dạng ngày hiện tại.
B. Nhập công thức: =IF[A4-TODAY[]>30, “Past Due”, “OK”]vào ô B4.
Trong tiếng Anh, công thức này có nghĩa là: Nếu ngày trong ngày hôm nay trừ ô A4 là lớn hơn 30 ngày, sau đó nhập các từ 'Quá Hạn' trong ô B4, khác / khác nhập từ 'OK'. Sao chép công thức này từ B4 đến B5 đến B13.
Chứng nhận Bảo vệ Oregon là một bài kiểm tra Pass / Fail yêu cầu những người tham gia phải đạt được một số bằng cấp tối thiểu để vượt qua. Điểm số của ít hơn 70 phần trăm thất bại, và những điểm số lớn hơn, vượt qua. Cột A liệt kê tên của người tham gia; cột B cho thấy điểm số của họ; và cột C hiển thị cho dù họ đã vượt qua hay thất bại khóa học. Thông tin trong cột C đạt được bằng cách sử dụng câu lệnh IF.
Khi công thức được nhập, bạn có thể tiếp tục sử dụng lại bảng tính này mãi mãi. Chỉ cần thay đổi tên vào đầu mỗi quý, nhập điểm mới vào cuối mỗi quý và Excel tính toán kết quả.
A. Nhập công thức này trong ô C4: =IF[B410000, C4, “No bonus”]sau đó sao chép từ D4 đến D5 đến D13. Công thức này sao chép tỷ lệ phần trăm từ cột C để bán hàng lớn hơn $ 10.000 hoặc các từ 'Không có Tiền thưởng' cho doanh thu dưới $ 10.000 vào cột D.
C. Nhập công thức này trong D15 di động: =SUM[D4:D13]. Công thức này tính tổng số tiền thưởng cho tháng hiện tại.
Câu lệnh IF lồng nhau
Ví dụ này sử dụng câu lệnh IF “lồng nhau” để chuyển đổi điểm số Toán thành các điểm chữ. Cú pháp cho câu lệnh IF lồng nhau là: IF data là true, sau đó thực hiện điều này; Dữ liệu IF là đúng, sau đó làm điều này; Dữ liệu IF là đúng, sau đó làm điều này; Dữ liệu IF là đúng, sau đó làm điều này; nếu không thì hãy làm điều đó. Bạn có thể lồng tối đa bảy hàm IF.
Tên của học sinh được liệt kê trong cột A; số điểm trong Cột B; và các điểm chữ trong cột C, được tính toán bởi câu lệnh IF lồng nhau.
A. Nhập công thức này vào ô C4:, =IF[B4>89,”A”,IF[B4>79,”B”,IF[B4>69,”C”,IF[B4>59,”D”,”F”]]]] sau đó sao chép từ C4 đến C5 qua C13.
Lưu ý: Mọi dấu ngoặc đơn mở, dấu ngoặc đơn trái trong công thức phải có dấu ngoặc đơn phù hợp, đã đóng. Nếu công thức của bạn trả về lỗi, hãy đếm các dấu ngoặc đơn của bạn.
Ví dụ này sử dụng một câu lệnh IF lồng nhau khác để tính toán nhiều phần trăm hoa hồng dựa trên thang trượt, sau đó tổng số tiền hoa hồng cho tháng đó. Cú pháp cho câu lệnh IF lồng nhau là: IF data là true, sau đó thực hiện điều này; Dữ liệu IF là đúng, sau đó làm điều này; Dữ liệu IF là đúng, sau đó làm điều này; nếu không thì hãy làm điều đó. Tên của nhân viên bán hàng được liệt kê trong cột A; tổng doanh thu hàng tháng của mỗi người trong cột B; và hoa hồng nằm trong cột C, được tính toán bằng một câu lệnh IF lồng nhau, sau đó được tính tổng ở cuối cột đó [trong ô C15].
A. Nhập công thức này vào ô C4:, =IF[B4 = 51,6,3, NẾU [B12> = 26,7,25, NẾU [B12> = 11,8, NẾU [ B12> = 1,8,5, ””]]]]]]]
Lưu ý không có khoảng trắng trong công thức này. Sao chép công thức đến mức cần thiết trong cột C để áp dụng cho tất cả các sản phẩm và số lượng bán buôn trong cột A và B.
H. Khi đơn đặt hàng hoàn tất, hãy tạo một dòng tổng: Trong cột A [trong trường hợp này là A20], hãy nhập TOTAL Số tiền Hoá đơn . Trong cột C [trong trường hợp này là C20], sử dụng hàm SUM để tính tổng cột.
Bây giờ, tất cả các công thức được đặt ra, tất cả những gì bạn phải làm mỗi tuần [hoặc tháng] là nhập số lượng mới vào cột B và Excel thực hiện phần còn lại. Nếu giá thay đổi, chỉ cần sửa đổi ma trận ở đầu bảng tính và tất cả các công thức và tổng số khác sẽ tự động điều chỉnh.
Trong ví dụ này, chúng ta sẽ lồng ghép hai câu lệnh IF và chèn hàm SUM tất cả vào cùng một công thức. Mục đích là để xác định xem các mục tiêu của cá nhân có nhỏ hơn, bằng hoặc lớn hơn giới hạn hàng tháng của công ty hay không.
A. Nhập số tiền giới hạn hàng tháng bằng B1, có thể thay đổi mỗi tháng để phản ánh kỳ vọng của công ty.
B. Nhập các tiêu đề sau trong các ô từ A3 đến G3: Mục tiêu , Qtr 1 , Qtr 2 , 3d Qtr , Qtr 4 , Mục tiêu hoặc Cap? và Tiền thưởng .
C. Nhập số tiền mục tiêu của năm người tiếp thị hàng đầu trong các ô từ A4 đến A8.
D. Nhập tổng số hàng quý của từng cá nhân vào các ô từ B4 đến E8.
Đối với nhân viên đạt được mục tiêu của họ, tiền thưởng là 10% số tiền mục tiêu. Đối với nhân viên vượt quá giới hạn hàng tháng, tiền thưởng là 25% doanh thu thực tế của họ. Sử dụng các công thức này để xác định số tiền thưởng.
E. Nhập công thức này vào các ô từ F4 đến F8: = IF [A4 B1, SUM [B4: E4,0]]]
F. Nhập công thức này vào các ô từ G4 đến G8: = NẾU [F4 = B1, F4 * 25%, 0]]
Trong Excel, nếu bạn cố gắng chia bất kỳ số nào bằng 0, thông báo lỗi mà Excel hiển thị là: # DIV / 0!
Vì vậy, phải làm gì nếu bạn có một cơ sở dữ liệu lớn đòi hỏi một phép tính dựa trên sự phân chia, và một số ô có chứa số không? Sử dụng công thức câu lệnh IF sau đây để giải quyết vấn đề này.
A. Để theo dõi số tiền thu được mỗi tháng của nhân viên tại các quỹ gây quỹ của Friar Tuck, mở bảng tính và tạo các tiêu đề sau: Đô la trong A1, đô la trên mỗi nhân viên trong B1, đô la còn lại trong C1.
B. Nhập một số đô la trong cột A và số nhân viên làm việc mỗi ngày trong cột B. Cột B sẽ bao gồm một số số không vì một số ngày, không ai làm việc [nhưng đóng góp vẫn được gửi qua thư].
C. Nhập công thức này từ C2 đến C11: = IF [B2 0, A2 / B2,0]. Bằng cách sử dụng câu lệnh IF để ngăn Excel phân chia bất kỳ số tiền nào thu được bằng 0, chỉ các số hợp lệ được tính toán.
D. Bạn có thể sử dụng một câu lệnh IF khác để định vị các số không trong cột B, sau đó tổng số đô la tương ứng trong cột A, sau đó sẽ được chia đều cho tất cả các nhân viên. Sao chép công thức này trong D2 đến D11: = IF [B4 = 0, A4,0].
E. Tiếp theo, di chuyển con trỏ đến B12 và nhập công thức này = MAX [B2: B11] để xác định số lượng công nhân lớn nhất trong cột B, là tám.
F. Di chuyển con trỏ đến D12 và nhập công thức này: = SUM [D2: D11] / B12. Câu trả lời là $ 392,50 cho mỗi nhân viên trong số tám nhân viên làm việc tại các quỹ gây quỹ của Friar Tuck.
COUNTIF, SUMIF, AVERAGEIF, & IF có ký tự đại diện
COUNTIF là câu lệnh IF đếm nội dung của một dải ô dựa trên tiêu chí rõ ràng [mà bạn chỉ định] được chứa trong phạm vi ô đó. Cú pháp rất đơn giản: công thức, phạm vi, tiêu chí: = COUNTIF [phạm vi, tiêu chí].
Trong ví dụ này, chúng tôi sẽ sử dụng COUNTIF để đếm các ô trong phạm vi cụ thể lớn hơn> số đã chọn, nhỏ hơn
A. Nhập một số trong các ô từ A4 đến A16.
B. Trong ô E4, nhập công thức này: = COUNTIF [$ A $ 4: $ A $ 16, "> 1610"]
C. Sau đó nhập số "lớn hơn" trong ô F4, vì vậy bạn có thể xác minh rằng công thức hoạt động.
D. Sao chép E4 xuống từ E5 đến E11, sau đó thay đổi lớn hơn giá trị thành một số khác.
A. Nhập 13 số vào cột B [B4: B16].
B. Trong ô G4, nhập công thức này: = COUNTIF [$ B $ 4: $ B $ 16, " = 1610"]; Nhỏ hơn hoặc bằng: = COUNTIF [$ A $ 4: $ A $ 16, " = 9/1/2018"]; = COUNTIF [$ A $ 4: $ A $ 12, "> = 2/6/2018"]; = COUNTIF [$ A $ 4: $ A $ 12, "> = 12/7/2018"]; = COUNTIF [$ A $ 4: $ A $ 12, "> = 5/14/2018"]; và = COUNTIF [$ A $ 4: $ A $ 12, "> = 8/9/2018"].
G. Nhập các công thức sau vào cột E "Chuỗi văn bản đơn", E4, E5, E6, E7 và E8:
= COUNTIF [$ B $ 4: $ B $ 12, "= Hoa hồng"];
= COUNTIF [$ B $ 4: $ B $ 12, "= Daisies"];
= COUNTIF [$ B $ 4: $ B $ 12, "= Hoa tulip"];
= COUNTIF [$ B $ 4: $ B $ 12, "= Lillies"]; và
= COUNTIF [$ B $ 4: $ B $ 12, "= Daffodils"]
H. Nhập các công thức sau vào cột G "Nhiều chuỗi văn bản", G4, G5, G6, G7 và G8:
= COUNTIF [$ B $ 4: $ B $ 10, "= Hoa hồng"] + COUNTIF [$ B $ 4: $ B $ 12, "Daisies"];
= COUNTIF [$ B $ 4: $ B $ 10, "= Hoa hồng"] + COUNTIF [$ B $ 4: $ B $ 12, "Hoa tulip"];
= COUNTIF [$ B $ 4: $ B $ 10, "= Hoa tulip"] + COUNTIF [$ B $ 4: $ B $ 12, "Daffodils"];
= COUNTIF [$ B $ 4: $ B $ 10, "= Lillies"] + COUNTIF [$ B $ 4: $ B $ 12, "Daisies"]; và
= COUNTIF [$ B $ 4: $ B $ 10, "= Lillies"] + COUNTIF [$ B $ 4: $ B $ 12, "Hoa tulip"]
I. Nhập các công thức sau vào cột I "Đô la", I4, I5, I6, I7 và I8:
= COUNTIF [C4: C12, " = 100"];
= COUNTIF [$ C $ 4: $ C $ 12, "