Xử lý nhiều điều kiện trong một công thức IF. Bài viết này so sánh ba phương pháp khác nhau.
Khi bạn cần thực hiện một phép tính có điều kiện, hàm IF chính là câu trả lời. Nếu thì khác. Trong hình sau, IF đơn giản tính toán tiền thưởng nếu doanh số của bạn là $ 20.000 trở lên.
Nhưng điều gì sẽ xảy ra khi hai điều kiện cần được đáp ứng? Hầu hết mọi người sẽ lồng một câu lệnh IF bên trong một câu lệnh khác, như thế này:
Nhưng điều này sẽ vượt khỏi tầm tay nếu bạn có nhiều điều kiện cần phải đáp ứng. Hàm AND sẽ rút ngắn và đơn giản hóa công thức. = AND (Kiểm tra, Kiểm tra, Kiểm tra, Kiểm tra) sẽ chỉ Đúng nếu tất cả các phép thử logic đều Đúng. Ví dụ sau đây cho thấy một công thức ngắn hơn với cùng kết quả.
Nếu bạn thích VÀ, bạn có thể tìm thấy cách sử dụng cho HOẶC và KHÔNG. = HOẶC (Kiểm tra, Kiểm tra, Kiểm tra, Kiểm tra) sẽ là Đúng nếu bất kỳ một trong các phép thử logic nào là Đúng. KHÔNG sẽ đảo ngược một câu trả lời. =NOT(True)là sai. =NOT(False)là đúng. Nếu bạn từng phải làm một cái gì đó lạ mắt như NAND, bạn có thể KHÔNG (VÀ (Kiểm tra, Kiểm tra, Kiểm tra, Kiểm tra)).
Thận trọng
Mặc dù Excel 2013 đã giới thiệu XOR như là một Hoặc độc quyền, nhưng nó không hoạt động theo cách mà kế toán mong đợi. =XOR(True,False,True,True)là Đúng vì những lý do quá phức tạp để giải thích ở đây. XOR thực sự đếm xem bạn có một số lẻ giá trị True hay không. Kỳ quặc. Kỳ cục thật.
Mẹo thưởng
Sử dụng Boolean Logic
Tôi luôn đề cập đến IF trong các buổi hội thảo của mình. Và tôi luôn hỏi mọi người sẽ giải quyết vấn đề hai điều kiện như thế nào. Kết quả thường giống nhau; 70-80% người sử dụng IF lồng nhau và 20-30% sử dụng AND. Chỉ một lần, ở Virginia, một phụ nữ từ Price Waterhouse đưa ra công thức này:
Nó hoạt động. Nó đưa ra câu trả lời tương tự như các công thức khác. Tính tiền thưởng .02 * B4. Nhưng sau đó nhân phần thưởng đó với các phép thử logic trong ngoặc đơn. Khi bạn buộc Excel nhân một số với True hoặc False, True sẽ trở thành 1 và False trở thành 0. Mọi số nhân với 1 là chính nó. Bất kỳ số nào nhân với 0 là 0. Nhân tiền thưởng với các điều kiện đảm bảo rằng chỉ những hàng đáp ứng cả hai điều kiện mới được trả.
Nó là mát mẻ. Nó hoạt động. Nhưng nó có vẻ khó hiểu khi bạn lần đầu tiên nhìn thấy nó. Câu nói đùa của tôi trong buổi hội thảo luôn là, "Nếu bạn sắp nghỉ việc vào tháng tới và bạn ghét đồng nghiệp của mình, hãy bắt đầu sử dụng công thức này."
Xem video
- Hàm IF đơn giản nhất là
=IF(Logical Test,Formula if True, Formula if False) - Nhưng bạn phải làm gì nếu bạn phải kiểm tra hai điều kiện?
- Nhiều người sẽ làm
=IF(Test 1, IF(Test 2, Formula if True, False), False) - Điều này sẽ khó sử dụng nếu 3, 5, 17 điều kiện!
- Thay vào đó, hãy sử dụng
=IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) - Nếu bạn thích VÀ, hãy cân nhắc HOẶC, KHÔNG cho các tình huống khác
- NAND có thể được thực hiện với NOT (AND ())
- NOR có thể được thực hiện với NOT (OR ())
- Hãy cẩn thận khi sử dụng XOR vì kết quả không như bạn mong đợi
Bản ghi video
Học Excel từ podcast, tập 2025 - Nhiều điều kiện trong IF!
Tôi sẽ podcast toàn bộ cuốn sách này, chữ “i” ở góc trên bên phải sẽ đưa bạn đến danh sách phát cho tất cả các podcast đó!
Được rồi, chúng ta sẽ bắt đầu với trường hợp IF đơn giản nhất trên thế giới, Phó chủ tịch bán hàng giả tưởng đến với chúng ta và nói "Này, tháng này bất kỳ ai có doanh số hơn 20000 đô la sẽ được thưởng 2%." Được rồi, vì vậy hàm IF có ba phần: Một bài kiểm tra logic cho kết quả "B4 có> 20000 đô la không?" Dấu phẩy, sau đó phải làm gì nếu nó là ĐÚNG? Nếu nó là TRUE, .02 * B4, dấu phẩy, điều gì sẽ xảy ra nếu nó là FALSE? Chà, nếu bạn chưa kiếm được 20000 đô la, thì không có tiền thưởng nào cho bạn, bằng không, được thôi. Ctrl + Enter để sao chép điều đó xuống và bạn thấy rằng chúng tôi chỉ có tiền thưởng cho các hàng trên $ 20000, hàng này đóng, nhưng vẫn không có tiền thưởng, được thôi. Bây giờ, chưa bao giờ có một kế hoạch tiền thưởng nào lại đơn giản như vậy, đúng không, luôn có nhiều quy tắc, vì vậy ở đây, chúng ta phải kiểm tra xem liệu doanh thu có> $ 20000 VÀ nếu phần trăm tổng lợi nhuận> 50%.
Được rồi, và nếu bạn biết cách giải quyết vấn đề này, hãy nghĩ xem bạn sẽ giải quyết vấn đề này như thế nào, được rồi, và tôi dự đoán rằng nhiều bạn sẽ nói "Chà, chúng ta sẽ bắt đầu với một Câu lệnh IF, và sau đó có một câu lệnh IF khác như thế này, các IF lồng nhau. " Và sau đó, có một vài người trong số các bạn đang nói "Chà, chúng ta hãy thực hiện một câu lệnh IF, và ngay sau đó, ngay bên trong dấu ngoặc đơn, chúng ta chuyển sang một hàm khác được gọi là AND." Và sau đó, có một cách để làm điều này mà không có IF nào cả. Được rồi, vì vậy tôi sẽ xem xét nó qua những điều đó, vì vậy đây là cách đầu tiên, cách phổ biến nhất, IF lồng nhau OK, đây là cách hầu hết mọi người sẽ làm điều này.
= IF, hãy làm bài kiểm tra đầu tiên, xem liệu doanh thu ở B4 có> 20000 đô la hay không, nếu có thì hãy thực hiện IF khác, xem phần trăm tổng lợi nhuận có> 0,05 không? Nếu đó là ĐÚNG, thì cả hai điều kiện đều ĐÚNG, chúng ta có thể thực hiện .02 * B4, nếu không thì không có tiền thưởng. OK, nhưng chúng ta chưa hoàn thành, hãy đóng dấu ngoặc đơn bên trong, dấu phẩy, sau đó nếu thử nghiệm đầu tiên không đúng, không có tiền thưởng, hãy nhấn Ctrl + Enter để sao chép. Bạn thấy rằng chỉ những hàng cao hơn $ 20000 trở lên phần trăm tổng lợi nhuận là 50% mới nhận được tiền thưởng. Được rồi, trước đây, đã từng có giới hạn khủng khiếp này, nơi bạn không thể lồng nhiều hơn 7 câu lệnh IF. Đó là một ngày đau khổ, đặc biệt nếu bạn chỉ bổ sung từ từ các điều kiện trong nhiều tháng, và cuối cùng bạn có một điều kiện có 7 và bạn cần thêm điều kiện thứ 8. Được rồi, hôm nay bạn có thể lên 32, tôi không nghĩ bạn nên 32nhưng nếu bạn thực sự cần tăng từ 7 đến 8, thì đây là một điều tốt. Vì vậy, đây là phương pháp tiếp cận câu lệnh IF lồng nhau, khi tôi thực hiện các cuộc hội thảo trực tiếp của mình, khoảng một nửa phòng đang làm việc này, nhưng có rất nhiều cách tốt hơn để thực hiện.
= IF và sau đó ngay lập tức đi vào một hàm được gọi là AND, vì vậy bên trong AND, chúng tôi đặt tất cả các kiểm tra: Doanh thu> 20000, dấu phẩy, là phần trăm lợi nhuận gộp> .5. Nếu có nhiều bài kiểm tra hơn, hãy tiếp tục đặt dấu phẩy với các bài kiểm tra bổ sung, và sau đó kết thúc, mọi thứ cuối cùng phải là ĐÚNG để kết thúc là ĐÚNG. Vì vậy, nếu chúng ta đến thời điểm này nếu kết thúc là TRUE, doanh thu .02 *, ngược lại là 0, đó là công thức ngắn hơn, dễ nhập hơn, bạn nhận được kết quả tương tự, cuộc sống thật tuyệt.
Được rồi, trong số tất cả các buổi hội thảo mà tôi đã làm trong 15 năm qua, chỉ có một lần có người bước vào và đánh tôi bằng công thức điên rồ này. Cô ấy nói "Hãy nhìn xem, chúng tôi sẽ làm = 0,02 * doanh thu, như vậy." Được rồi, hãy tính toán tiền thưởng, tôi như "Chà, chờ đã, sẽ đắt đấy, bạn sẽ chia tiền thưởng cho mọi người." cô ấy giống như "Chờ đã, tôi đã không xong, lần, và sau đó trong ngoặc đơn, chúng tôi sẽ đặt từng điều kiện, do đó doanh thu> 20000 lần, phần trăm tổng lợi nhuận trong ngoặc đơn> .5." Được rồi, và đây là những gì sẽ xảy ra là chúng tôi tính toán tiền thưởng, sau đó các kết quả này đánh giá là TRUE hoặc FALSE. Và khi chúng ta buộc Excel nhân một số TRUE hoặc FALSE với một số, TRUE sẽ trở thành 1, bất kỳ thứ gì * 1 là chính nó, FALSE trở thành 0! Được rồi, những gì chúng ta có ở đây là 2% * doanh thu * 1 * 0, bất cứ thứ gì * 0 = 0, đúng không,để xóa tiền thưởng. Đây là logic Boolean, TRUE * TRUE, 1 * 1 = 1, nếu một trong hai chúng là FALSE hoặc tất cả chúng đều FALSE, điều đó sẽ được đánh giá là 0 và chúng tôi nhận được cùng một kết quả. Tôi có nghĩ bạn nên chuyển sang cái này không? Không, điều đó thật khó hiểu, trừ khi bạn rời công việc vào tuần tới và bạn ghét đồng nghiệp của mình, thì hãy chuyển sang việc này, được rồi.
Nếu bạn thích hàm AND, có các hàm khác hoặc kiểm tra xem có điều kiện nào là ĐÚNG không, vì vậy điều này hoặc điều này hoặc điều này, nó sẽ trả về TRUE. NOT sẽ đảo ngược TRUE thành FALSE và FALSE thành TRUE, điều này rất hữu ích khi bạn đang cố gắng thực hiện các khái niệm Boolean của NAND hoặc NOR. NAND là viết tắt của not-and, nó là TRUE khi ít nhất một điều kiện là FALSE, được thôi. Vì vậy, nếu không ai trong số họ là ĐÚNG, thì điều đó thật tuyệt, nếu một vài trong số đó là ĐÚNG, điều đó thật tuyệt, nhưng ngay khi tất cả chúng đều ĐÚNG, thì chúng tôi không trả tiền. NOR là viết tắt của not-or, có nghĩa là không có điều kiện nào là ĐÚNG - nếu điều này xảy ra, hoặc điều này xảy ra, hoặc điều này xảy ra, bạn sẽ không có tiền thưởng. Và sau đó XOR, bây giờ hãy cẩn thận với cái này, nó đã được giới thiệu trong Excel 2013, và nó không làm những gì chúng tôi như kế toán nghĩ rằng nó nên làm.Exclusive-hoặc có nghĩa là chỉ một trong các thử nghiệm là ĐÚNG và nó hoạt động khi có hai điều kiện. Nhưng đối với các kỹ sư điện, họ đang làm việc này theo cặp đôi, vì vậy nó không cho kết quả như bạn có thể nghĩ.
Được rồi, đây là Bài kiểm tra 1, Bài kiểm tra 2, Bài kiểm tra 3, Bài kiểm tra 4, ba trong số đó là ĐÚNG và XOR cho biết “Có chính xác một trong những bài này ĐÚNG không?” Và khi chúng tôi làm điều này XOR, nó nói "Có, chính xác một trong những điều đó là ĐÚNG." và đó là bởi vì hàm Excel đang sao chép hoạt động của một con chip rất phổ biến được sử dụng trong kỹ thuật điện, tôi biết, thật là sốc, đúng không? Bạn nghĩ rằng Excel chỉ dành cho kế toán, nhưng các kỹ sư cũng sử dụng Excel, và họ rõ ràng đã thêm XOR cho họ chứ không phải cho kế toán. Vì vậy, cách đánh giá điều này, khi họ nhìn vào hai điều đầu tiên, “Một trong hai điều này có ĐÚNG không? Đúng!" Được rồi, vì vậy chúng tôi nhận được là ĐÚNG, và sau đó họ lấy câu trả lời từ XOR của câu này và so sánh với câu ĐÚNG, “Một trong 2 câu này có ĐÚNG không? Không, 2 trong số đó là ĐÚNG, vì vậy điều đó trở thành SAI! ” Sau đó, họ nhận câu trả lời đó và XOAY nó với câu cuối cùng,vì vậy họ đang làm điều này theo cặp, phải không? “Một trong 2 điều này có ĐÚNG không? Đúng!" Được rồi, đó là cách chúng tôi hiểu. Nó chỉ ra rằng những gì nó thực sự đang làm, kỹ thuật điện là điều này đếm nếu một số lẻ đầu vào là ĐÚNG. Không nhất thiết hữu ích cho kế toán, những người đang mong đợi nó thực hiện những gì XOR trong tiếng Anh có nghĩa là.
Được rồi, rất nhiều mẹo hay trong cuốn sách này, những nội dung hữu ích và thậm chí cả cuộc thảo luận kinh khủng về NAND và XOR và những thứ tương tự. Mua sách, bạn sẽ nắm trong tay tất cả các mẹo này. Tóm tắt lại từ hôm nay: Hàm IF đơn giản nhất, kiểm tra logic = IF, phải làm gì nếu nó ĐÚNG, phải làm gì nếu nó SAI, nhưng nếu bạn có 2 điều kiện, rất nhiều người lồng các câu lệnh IF, nhưng hãy tưởng tượng nếu bạn có 3 , 5, hoặc 17 điều kiện để làm tổ. AND sẽ giải quyết điều đó, rút ngắn một chút, vì vậy nếu bạn thích AND, cũng có HOẶC hoặc KHÔNG, bạn có thể làm NAND, bạn có thể làm NOR, nhưng hãy cẩn thận khi sử dụng Excel 2013 XOR mới đó, kết quả có thể không như bạn chờ đợi.
Được rồi, tôi muốn cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp bạn lần sau với một netcast khác từ!
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2025.xlsx
