Nội dung
Một trong những ưu điểm chính của bảng tính Excel là khả năng lập trình chức năng của một tài liệu cụ thể. Như hầu hết mọi người đều biết từ các bài học khoa học máy tính ở trường, một trong những thành phần chính cho phép bạn áp dụng điều này vào thực tế là các toán tử logic. Một trong số đó là toán tử IF, cung cấp cho việc thực hiện các hành động nhất định khi các điều kiện nhất định được đáp ứng.
Ví dụ: nếu giá trị khớp với một giá trị nhất định, thì một nhãn sẽ được hiển thị trong ô. Nếu không, nó khác. Chúng ta hãy xem xét công cụ hiệu quả này chi tiết hơn trong thực tế.
Hàm IF trong Excel (thông tin chung)
Bất kỳ chương trình nào, dù là nhỏ, nhất thiết phải chứa một chuỗi các hành động, nó được gọi là một thuật toán. Nó có thể trông như thế này:
- Kiểm tra toàn bộ cột A để biết số chẵn.
- Nếu một số chẵn được tìm thấy, hãy thêm các giá trị đó và các giá trị đó.
- Nếu không tìm thấy số chẵn thì hiển thị dòng chữ “không tìm thấy”.
- Kiểm tra xem số kết quả là số chẵn.
- Nếu có, sau đó thêm nó vào tất cả các số chẵn đã chọn trong đoạn 1.
Và ngay cả khi đây chỉ là một tình huống giả định, không cần thiết trong cuộc sống thực, thì việc thực hiện bất kỳ tác vụ nào cũng nhất thiết phải có sự hiện diện của một thuật toán tương tự. Trước khi sử dụng chức năng NẾU NHƯ, bạn cần có một ý tưởng rõ ràng trong đầu về kết quả bạn muốn đạt được.
Cú pháp của hàm IF với một điều kiện
Bất kỳ hàm nào trong Excel đều được thực hiện bằng công thức. Mẫu mà dữ liệu phải được truyền cho một hàm được gọi là cú pháp. Trong trường hợp của nhà điều hành IF, công thức sẽ ở định dạng này.
= IF (biểu_thức lôgic, giá_trị, giá_trị_giá_trị)
Hãy xem cú pháp chi tiết hơn:
- Biểu thức Boolean. Đây chính là điều kiện, sự tuân thủ hoặc không tuân thủ mà Excel kiểm tra. Cả thông tin dạng số và dạng văn bản đều có thể được kiểm tra.
- Giá trị_if_true. Kết quả sẽ được hiển thị trong ô nếu dữ liệu đang được kiểm tra đáp ứng các tiêu chí đã chỉ định.
- value_if_false. Kết quả được hiển thị trong ô nếu dữ liệu đang được kiểm tra không khớp với điều kiện.
Đây là một ví dụ cho rõ ràng.
Ở đây hàm so sánh ô A1 với số 20. Đây là đoạn đầu tiên của cú pháp. Nếu nội dung lớn hơn giá trị này, giá trị “lớn hơn 20” được hiển thị trong ô nơi công thức được viết. Nếu tình huống không tương ứng với điều kiện này - "nhỏ hơn hoặc bằng 20".
Nếu bạn muốn hiển thị một giá trị văn bản trong một ô, bạn phải đặt nó trong dấu ngoặc kép.
Đây là một tình huống khác. Để đủ điều kiện tham gia một kỳ kiểm tra, học sinh phải trải qua một kỳ kiểm tra. Các sinh viên đã cố gắng giành được các tín chỉ trong tất cả các môn học, và bây giờ người cuối cùng vẫn còn, điều này hóa ra là quyết định. Nhiệm vụ của chúng tôi là xác định học sinh nào được nhận vào các kỳ thi và học sinh nào không.
Vì chúng ta muốn kiểm tra văn bản chứ không phải số, đối số đầu tiên là B2 = ”khuyết điểm.”.
Cú pháp hàm IF với nhiều điều kiện
Thông thường, một tiêu chí không đủ để kiểm tra giá trị so với. Nếu bạn cần xem xét nhiều hơn một tùy chọn, bạn có thể lồng các hàm IF cái này vào cái kia. Sẽ có một số hàm được lồng vào nhau.
Để làm cho nó rõ ràng hơn, đây là cú pháp.
= IF (biểu_thức lôgic, giá_trị_if_true, IF (biểu_thức lôgic, giá_trị_giá_trị, giá_trị_if_false))
Trong trường hợp này, hàm sẽ kiểm tra hai tiêu chí cùng một lúc. Nếu điều kiện đầu tiên là đúng, giá trị thu được do hoạt động trong đối số đầu tiên sẽ được trả về. Nếu không, tiêu chí thứ hai được kiểm tra để tuân thủ.
Đây là một ví dụ.
Và với sự trợ giúp của một công thức như vậy (được hiển thị trong ảnh chụp màn hình bên dưới), bạn có thể phân tích thành tích của từng học sinh.
Như bạn có thể thấy, một điều kiện nữa đã được thêm vào đây, nhưng nguyên tắc không thay đổi. Vì vậy, bạn có thể kiểm tra một số tiêu chí cùng một lúc.
Cách mở rộng chức năng IF bằng toán tử AND và OR
Đôi khi, có một tình huống cần kiểm tra ngay lập tức xem có tuân thủ một số tiêu chí hay không và không sử dụng các toán tử logic lồng nhau, như trong ví dụ trước. Để làm điều này, hãy sử dụng hàm И hoặc chức năng OR tùy thuộc vào việc bạn cần đáp ứng một số tiêu chí cùng một lúc hay ít nhất một trong số chúng. Chúng ta hãy xem xét kỹ hơn các tiêu chí này.
Hàm IF với điều kiện AND
Đôi khi bạn cần kiểm tra một biểu thức cho nhiều điều kiện cùng một lúc. Đối với điều này, hàm AND được sử dụng, được viết trong đối số đầu tiên của hàm IF. Nó hoạt động như thế này: nếu a bằng một và a bằng 2, giá trị sẽ là c.
Hàm IF với điều kiện "HOẶC"
Hàm OR hoạt động theo cách tương tự, nhưng trong trường hợp này, chỉ một trong các điều kiện là đúng. Nhiều nhất có thể, có thể kiểm tra tối đa 30 điều kiện theo cách này.
Dưới đây là một số cách để sử dụng các hàm И и OR làm đối số hàm IF.
So sánh dữ liệu trong hai bảng
Tùy từng thời điểm có thể so sánh hai bảng tương tự nhau. Ví dụ, một người làm kế toán và cần so sánh hai báo cáo. Có những công việc tương tự khác, chẳng hạn như so sánh giá vốn của các lô hàng khác nhau, sau đó, đánh giá học sinh của các thời kỳ khác nhau, v.v.
Để so sánh hai bảng, hãy sử dụng hàm COUNTIF. Chúng ta hãy xem xét nó chi tiết hơn.
Giả sử chúng ta có hai bảng chứa thông số kỹ thuật của hai bộ chế biến thực phẩm. Và chúng ta cần so sánh chúng, và làm nổi bật sự khác biệt bằng màu sắc. Điều này có thể được thực hiện bằng cách sử dụng định dạng có điều kiện và hàm COUNTIF.
Bảng của chúng tôi trông như thế này.
Chúng tôi chọn phạm vi tương ứng với các đặc tính kỹ thuật của bộ xử lý thực phẩm đầu tiên.
Sau đó, nhấp vào các menu sau: Định dạng có điều kiện - tạo quy tắc - sử dụng công thức để xác định các ô được định dạng.
Dưới dạng công thức để định dạng, chúng ta viết hàm = COUNTIF (phạm vi để so sánh; ô đầu tiên của bảng đầu tiên) = 0. Bảng với các tính năng của bộ xử lý thực phẩm thứ hai được sử dụng làm phạm vi so sánh.
Bạn cần đảm bảo rằng các địa chỉ là tuyệt đối (có ký hiệu đô la phía trước tên hàng và cột). Thêm = 0 sau công thức để Excel tìm kiếm các giá trị chính xác.
Sau đó, bạn cần thiết lập định dạng của các ô. Để làm điều này, bên cạnh mẫu, bạn cần nhấp vào nút “Định dạng”. Trong trường hợp của chúng tôi, chúng tôi sử dụng điền vào, bởi vì nó là thuận tiện nhất cho mục đích này. Nhưng bạn có thể chọn bất kỳ định dạng nào bạn muốn.
Chúng tôi đã chỉ định một tên cột dưới dạng một phạm vi. Điều này thuận tiện hơn nhiều so với việc nhập phạm vi theo cách thủ công.
Hàm SUMIF trong Excel
Bây giờ chúng ta hãy chuyển sang các chức năng IF, điều này sẽ giúp thay thế hai điểm của thuật toán cùng một lúc. Đầu tiên là TÓM TẮT, mà cộng hai số thỏa mãn một điều kiện nhất định. Ví dụ, chúng tôi phải đối mặt với nhiệm vụ xác định số tiền phải trả mỗi tháng cho tất cả người bán. Đối với điều này, nó là cần thiết.
- Thêm một hàng với tổng thu nhập của tất cả người bán và nhấp vào ô sẽ chứa kết quả sau khi nhập công thức.
- Chúng tôi tìm thấy nút fx, nằm bên cạnh dòng công thức. Tiếp theo, một cửa sổ sẽ xuất hiện để bạn có thể tìm thấy chức năng cần thiết thông qua việc tìm kiếm. Sau khi chọn nhà điều hành, bạn cần nhấp vào nút “OK”. Nhưng nhập liệu thủ công luôn có thể thực hiện được.
11 - Tiếp theo, một cửa sổ để nhập các đối số của hàm sẽ xuất hiện. Tất cả các giá trị có thể được chỉ định trong các trường tương ứng và phạm vi có thể được nhập thông qua nút bên cạnh chúng.
12 - Đối số đầu tiên là một phạm vi. Tại đây, bạn nhập các ô mà bạn muốn kiểm tra xem có tuân thủ các tiêu chí hay không. Nếu nói về chúng tôi, đây là những vị trí của nhân viên. Nhập phạm vi D4: D18. Hoặc chỉ cần chọn các ô quan tâm.
- Trong trường "Tiêu chí", hãy nhập vị trí. Trong trường hợp của chúng tôi - "người bán". Là phạm vi tổng kết, chúng tôi chỉ ra những ô mà lương của nhân viên được liệt kê (điều này được thực hiện cả thủ công và chọn chúng bằng chuột). Nhấp vào “OK”, và chúng tôi nhận được tiền lương đã tính xong của tất cả nhân viên là người bán.
Đồng ý rằng nó rất tiện lợi. Không phải nó?
Hàm SUMIFS trong Excel
Hàm này cho phép bạn xác định tổng các giá trị đáp ứng nhiều điều kiện. Ví dụ, chúng tôi được giao nhiệm vụ xác định tổng mức lương của tất cả các quản lý làm việc tại chi nhánh phía Nam của công ty.
Thêm một hàng có kết quả cuối cùng và chèn công thức vào ô mong muốn. Để làm điều này, hãy nhấp vào biểu tượng chức năng. Một cửa sổ sẽ xuất hiện trong đó bạn cần tìm hàm TÓM TẮT. Tiếp theo, chọn nó từ danh sách và cửa sổ quen thuộc với các đối số sẽ mở ra. Nhưng số lượng các đối số này hiện đã khác. Công thức này giúp bạn có thể sử dụng vô số tiêu chí, nhưng số lượng đối số tối thiểu là năm.
Chỉ có thể chỉ định năm thông qua hộp thoại nhập đối số. Nếu bạn cần thêm tiêu chí, thì chúng sẽ phải được nhập thủ công theo cùng một logic như hai tiêu chí đầu tiên.
Hãy xem xét các đối số chính một cách chi tiết hơn:
- Phạm vi tổng kết. Các ô được tính tổng.
- Phạm vi điều kiện 1 - phạm vi sẽ được kiểm tra để tuân thủ một tiêu chí nhất định.
- Điều kiện 1 là điều kiện chính nó.
- Phạm vi tiêu chí 2 là phạm vi thứ hai sẽ được kiểm tra so với tiêu chí.
- Điều kiện 2 là điều kiện thứ hai.
Logic xa hơn cũng tương tự. Kết quả là, chúng tôi xác định mức lương của tất cả các quản lý của Chi nhánh phía Nam.
Hàm COUNTIF trong Excel
Nếu bạn cần xác định có bao nhiêu ô thuộc một tiêu chí nhất định, hãy sử dụng hàm ĐẾM. Giả sử chúng ta cần hiểu có bao nhiêu nhân viên bán hàng làm việc trong tổ chức này:
- Đầu tiên, hãy thêm một dòng chứa số lượng người bán. Sau đó, bạn cần nhấp vào ô mà kết quả sẽ được hiển thị.
- Sau đó, bạn cần nhấp vào nút “Chèn hàm”, có thể được tìm thấy trong tab “Công thức”. Một cửa sổ sẽ xuất hiện với danh sách các danh mục. Chúng ta cần chọn mục “Danh sách đầy đủ theo bảng chữ cái”. Trong danh sách, chúng tôi quan tâm đến công thức ĐẾM. Sau khi chúng tôi chọn nó, chúng tôi cần nhấp vào nút “OK”.
14 - Sau đó, chúng tôi có số lượng nhân viên bán hàng được tuyển dụng trong tổ chức này. Nó thu được bằng cách đếm số ô có từ “người bán” được viết. Mọi thứ đều đơn giản.
Hàm COUNTSLIM trong Excel
Tương tự với công thức TÓM TẮT, công thức này đếm số ô phù hợp với nhiều điều kiện. Cú pháp tương tự nhưng hơi khác so với công thức TÓM TẮT:
- Phạm vi điều kiện 1. Đây là phạm vi sẽ được kiểm tra theo tiêu chí đầu tiên.
- Điều kiện 1. Trực tiếp là tiêu chí đầu tiên.
- Phạm vi điều kiện 2. Đây là phạm vi sẽ được kiểm tra theo tiêu chí thứ hai.
- Điều kiện 2.
- Điều kiện phạm vi 3.
Và như vậy.
Vì vậy, hàm IF trong Excel - không phải là duy nhất, có nhiều loại khác của nó tự động thực hiện các hành động phổ biến nhất, điều này giúp đơn giản hóa đáng kể cuộc sống của một người.
Phần lớn là do chức năng IF Bảng tính Excel được coi là có thể lập trình được. Nó không chỉ là một chiếc máy tính đơn giản. Nếu bạn nghĩ về nó, thì hàm IF là nền tảng trong bất kỳ loại lập trình nào.
Vì vậy, nếu bạn học cách làm việc với một lượng lớn dữ liệu trong Excel, thì việc học lập trình sẽ dễ dàng hơn nhiều. Nhờ các toán tử logic, các lĩnh vực này thực sự có nhiều điểm chung, mặc dù Excel thường được các kế toán viên sử dụng nhiều hơn. Nhưng cơ chế làm việc với dữ liệu phần lớn giống nhau.
Chức năng ở tay phải IF và các biến thể của nó cho phép bạn biến một trang tính Excel thành một chương trình chính thức có thể hoạt động trên các thuật toán phức tạp. Hiểu cách hoạt động của chức năng IF là bước đầu tiên để học macro - bước tiếp theo để làm việc linh hoạt hơn với bảng tính. Nhưng đây đã là một cấp độ chuyên nghiệp hơn.