Làm thế nào để tổ chức nhiều hàm IF trong Excel

01 trên 06

Hàm Nested IF hoạt động như thế nào

Tính hữu ích của hàm IF có thể được mở rộng bằng cách chèn hoặc lồng nhiều hàm IF bên trong nhau.

Hàm IF lồng nhau tăng số lượng điều kiện có thể có thể được kiểm tra và tăng số lượng hành động có thể được thực hiện để đối phó với các kết quả này.

Các phiên bản Excel gần đây nhất cho phép 64 hàm IF được lồng vào nhau, trong khi Excel 2003 và trước đó chỉ cho phép bảy.

Hướng dẫn hàm Nesting IF

Như thể hiện trong hình trên, hướng dẫn này chỉ sử dụng hai hàm IF để tạo công thức sau tính toán số tiền khấu trừ hàng năm cho nhân viên dựa trên mức lương hàng năm của họ.

Công thức được sử dụng trong ví dụ được hiển thị bên dưới. Hàm IF lồng nhau hoạt động như đối số value_if_false cho hàm IF đầu tiên.

= NẾU (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

Các phần khác nhau của công thức được phân tách bằng dấu phẩy và thực hiện các tác vụ sau:

1. Phần đầu tiên, D7, kiểm tra xem lương của nhân viên có nhỏ hơn $ 30,000 hay không
2. Nếu có, phần giữa, $ D * 3 * D7 , nhân với mức lương bằng tỷ lệ khấu trừ là 6%
3. Nếu không, hàm IF thứ hai: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) kiểm tra hai điều kiện sau:
   • D7> = 50000 , kiểm tra xem tiền lương của nhân viên có lớn hơn hoặc bằng 50.000 đô la hay không
   • Nếu có, $ D $ 5 * D7 nhân mức lương với tỷ lệ khấu trừ là 10%
   • Nếu không, $ D $ 4 * D7 nhân tiền lương với tỷ lệ khấu trừ là 8%

Nhập dữ liệu hướng dẫn

Nhập dữ liệu vào các ô C1 đến E6 của trang tính Excel như trong hình trên.

Dữ liệu duy nhất không được nhập vào thời điểm này chính là hàm IF nằm trong ô E7.

Đối với những người không cảm thấy thích gõ, dữ liệu và hướng dẫn sao chép nó vào Excel có sẵn tại liên kết này.

Lưu ý: Hướng dẫn sao chép dữ liệu không bao gồm các bước định dạng cho trang tính.

Điều này sẽ không can thiệp vào việc hoàn thành hướng dẫn. Trang tính của bạn có thể trông khác với ví dụ được hiển thị, nhưng hàm IF sẽ cho bạn kết quả tương tự.

02/06

Bắt đầu hàm IF lồng nhau

Mặc dù có thể chỉ cần nhập công thức hoàn chỉnh

= NẾU (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

vào ô E7 của trang tính và có nó hoạt động, thường dễ sử dụng hộp thoại của hàm để nhập các đối số cần thiết.

Sử dụng hộp thoại sẽ phức tạp hơn một chút khi nhập các hàm lồng nhau vì hàm lồng nhau phải được nhập. Hộp thoại thứ hai không thể mở được để nhập bộ đối số thứ hai.

Đối với ví dụ này, hàm IF lồng nhau sẽ được nhập vào dòng thứ ba của hộp thoại làm đối số Value_if_false .

• Kể từ khi bảng tính tính khấu trừ hàng năm cho một số nhân viên, công thức sẽ được nhập lần đầu tiên vào một ô E7 sử dụng tham chiếu ô tuyệt đối cho tỷ lệ khấu trừ và sau đó sao chép nó vào ô E8: E11.

Các bước hướng dẫn

1. Bấm vào ô E7 để biến nó thành ô hiện hoạt. - vị trí cho công thức IF lồng nhau.
2. Nhấp vào tab Công thức của dải băng
3. Nhấp vào biểu tượng Logical để mở danh sách thả xuống chức năng.
4. Nhấp vào IF trong danh sách để hiển thị hộp thoại của hàm.

Dữ liệu được nhập vào các dòng trống trong hộp thoại tạo thành các đối số của hàm IF.

Các đối số này cho biết hàm đang kiểm tra và các hành động cần thực hiện nếu điều kiện là đúng hoặc sai.

Tùy chọn phím tắt hướng dẫn

Để tiếp tục với ví dụ này, bạn có thể

• nhập các đối số vào hộp thoại như được hiển thị trong hình ở trên và sau đó chuyển đến bước cuối cùng bao gồm việc sao chép công thức thành các hàng từ 7 đến 10;
• hoặc làm theo thông qua năm trang tiếp theo cung cấp hướng dẫn chi tiết và giải thích cho việc nhập ba đối số.

03/06

Nhập đối số Logical_test

Đối số Logical_test luôn là một so sánh giữa hai mục dữ liệu. Dữ liệu này có thể là số, tham chiếu ô , kết quả của công thức hoặc thậm chí dữ liệu văn bản.

Để so sánh hai giá trị, Logical_test sử dụng toán tử so sánh giữa các giá trị.

Trong ví dụ này, có ba mức lương xác định khấu trừ hàng năm của nhân viên.

• ít hơn $ 30,000
• từ 30.000 đô la đến 49.999 đô la
• $ 50.000 trở lên

Hàm IF đơn có thể so sánh hai mức, nhưng mức lương thứ ba yêu cầu sử dụng hàm IF lồng nhau thứ hai.

Việc so sánh đầu tiên sẽ là giữa tiền lương hàng năm của nhân viên, nằm trong ô D, với mức lương ngưỡng là 30.000 đô la.

Vì mục đích là để xác định xem D7 có nhỏ hơn $ 30,000 hay không, toán tử Less Than "<" được sử dụng giữa các giá trị.

Các bước hướng dẫn

1. Nhấp vào dòng Logical_test trong hộp thoại
2. Nhấp vào ô D7 để thêm tham chiếu ô này vào dòng Logical_test
3. Nhấn phím ít hơn "<" trên bàn phím
4. Nhập 30000 sau ký hiệu nhỏ hơn
5. Bài kiểm tra logic hoàn thành nên đọc: D7 <30000

Lưu ý: Không nhập dấu đô la ($) hoặc dấu phẩy (,) bằng 30000.

Một thông báo lỗi không hợp lệ sẽ xuất hiện ở cuối dòng Logical_test nếu một trong hai biểu tượng này được nhập cùng với dữ liệu.

04/06

Nhập đối số Value_if_true

Đối số Value_if_true báo cho hàm IF biết phải làm gì khi Logical_test là đúng.

Đối số Value_if_true có thể là công thức, một khối văn bản, giá trị , tham chiếu ô hoặc ô có thể để trống.

Trong ví dụ này, khi dữ liệu trong ô D7 nhỏ hơn $ 30,000. Excel nhân mức lương hàng năm của nhân viên trong ô D7 với tỷ lệ khấu trừ 6% trong ô D3.

Relative vs. Absolute Cell Relferences

Thông thường, khi một công thức được sao chép sang các ô khác, các tham chiếu ô tương đối trong công thức thay đổi để phản ánh vị trí mới của công thức. Điều này giúp dễ dàng sử dụng cùng một công thức ở nhiều vị trí.

Đôi khi, tuy nhiên, có tham chiếu ô thay đổi khi một hàm được sao chép sẽ dẫn đến lỗi.

Để ngăn chặn các lỗi này, các tham chiếu ô có thể được tạo thành Tuyệt đối ngăn chúng thay đổi khi chúng được sao chép.

Tham chiếu ô tuyệt đối được tạo bằng cách thêm dấu đô la xung quanh tham chiếu ô thông thường, chẳng hạn như $ D $ 3 .

Thêm dấu hiệu đô la được thực hiện dễ dàng bằng cách nhấn phím F4 trên bàn phím sau khi tham chiếu ô đã được nhập vào hộp thoại.

Trong ví dụ, tỷ lệ khấu trừ nằm trong ô D3 được nhập dưới dạng tham chiếu ô tuyệt đối vào dòng Value_if_true của hộp thoại.

Các bước hướng dẫn

1. Nhấp vào dòng Value_if_true trong hộp thoại
2. Bấm vào ô D3 trong trang tính để thêm tham chiếu ô này vào dòng Value_if_true
3. Nhấn phím F4 trên bàn phím để biến D3 thành tham chiếu ô tuyệt đối ($ D $ 3)
4. Nhấn phím hoa thị ( * ) trên bàn phím - dấu hoa thị là biểu tượng phép nhân trong Excel
5. Nhấp vào ô D7 để thêm tham chiếu ô này vào dòng Value_if_true
6. Dòng Value_if_true đã hoàn thành sẽ đọc: $ D $ 3 * D7

Lưu ý : D7 không được nhập dưới dạng tham chiếu ô tuyệt đối vì nó cần thay đổi khi công thức được sao chép vào ô E8: E11 để nhận số tiền khấu trừ chính xác cho từng nhân viên.

05/06

Nhập hàm IF lồng nhau làm đối số Value_if_false

Thông thường, đối số Value_if_false cho hàm IF biết phải làm gì khi Logical_test là sai, nhưng trong trường hợp này, hàm IF lồng nhau được nhập làm đối số này.

Bằng cách đó, các kết quả sau sẽ xảy ra:

• Đối số Logical_test trong hàm IF lồng nhau ( D7> = 50000 ) kiểm tra tất cả các mức lương không nhỏ hơn $ 30,000.
• Đối với những khoản lương lớn hơn hoặc bằng $ 50,000 đối số Value_if_true nhân với tỷ lệ khấu trừ 10% trong ô D5.
• Đối với tiền lương còn lại - những người lớn hơn $ 30,000 nhưng ít hơn $ 50,000 - đối số Value_if_false nhân với tỷ lệ khấu trừ 8% trong ô D4.

Các bước hướng dẫn

Như đã đề cập ở phần đầu của hướng dẫn, một hộp thoại thứ hai không thể được mở để nhập hàm lồng nhau, vì vậy nó phải được nhập vào dòng Value_if_false.

Lưu ý: các hàm lồng nhau không bắt đầu bằng dấu bằng - mà đúng hơn là với tên hàm.

1. Nhấp vào dòng Value_if_false trong hộp thoại
2. Nhập hàm IF sau
   NẾU (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Nhấn OK để hoàn tất hàm IF và đóng hộp thoại
4. Giá trị của $ 3,678,96 sẽ xuất hiện trong ô E7 *
5. Khi bạn bấm vào ô E7, hàm hoàn chỉnh
   = NẾU (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   xuất hiện trong thanh công thức phía trên trang tính

* Vì R. Holt kiếm được hơn 30.000 đô la nhưng dưới 50.000 đô la mỗi năm, công thức $ 45,987 * 8% được sử dụng để tính khấu trừ hàng năm của mình.

Nếu tất cả các bước đã được theo dõi, ví dụ của bạn phải khớp với hình ảnh đầu tiên trong bài viết này.

Bước cuối cùng liên quan đến việc sao chép công thức IF đến các ô E8 đến E11 bằng cách sử dụng nút điều khiển điền để hoàn tất bảng tính.

06 trên 06

Sao chép Hàm IF lồng nhau bằng cách sử dụng Fill Handle

Để hoàn thành bảng tính, công thức chứa hàm IF lồng nhau cần được sao chép vào các ô từ E8 đến E11.

Khi chức năng được sao chép, Excel sẽ cập nhật các tham chiếu ô tương đối để phản ánh vị trí mới của hàm trong khi giữ tham chiếu ô tuyệt đối giống nhau.

Một cách dễ dàng để sao chép các công thức trong Excel là với Trình xử lý Điền.

Các bước hướng dẫn

1. Bấm vào ô E7 để biến nó thành ô hiện hoạt .
2. Đặt con trỏ chuột lên ô vuông màu đen ở góc dưới cùng bên phải của ô hiện hoạt. Con trỏ sẽ thay đổi thành dấu cộng "+".
3. Nhấp vào nút chuột trái và kéo thanh điều khiển điền xuống ô E11.
4. Nhả nút chuột. Các ô E8 đến E11 sẽ được lấp đầy với các kết quả của công thức như trong hình trên.