Bạn có công thức Excel chứa nhiều hàm IF không? Khi bạn đi đến điểm với quá nhiều câu lệnh IF lồng nhau, bạn cần xem liệu toàn bộ mọi thứ có trở nên đơn giản hơn không với một hàm VLOOKUP đơn giản. Tôi đã thấy các công thức 1000 ký tự được đơn giản hóa thành công thức VLOOKUP 30 ký tự. Nó dễ dàng duy trì khi bạn phải thêm các giá trị mới sau này.
Một thời gian dài trước đây, tôi đã làm việc cho phó giám đốc kinh doanh tại nơi làm việc. Tôi luôn mô hình hóa một số chương trình tiền thưởng hoặc kế hoạch hoa hồng mới. Tôi đã trở nên khá quen thuộc với các kế hoạch hoa hồng với đủ loại điều kiện. Cái bên dưới khá thuần phục.
Cách tiếp cận thông thường là bắt đầu xây dựng công thức IF lồng nhau. Bạn luôn bắt đầu ở đầu cao hoặc cuối của dải. “Nếu doanh số trên $ 500K, thì chiết khấu là 20%; nếu không thì… ." Đối số thứ ba của hàm IF là một hàm IF hoàn toàn mới kiểm tra mức thứ hai: Nếu doanh số bán hàng trên 250 nghìn đô la, thì chiết khấu là 15%; nếu không thì,… "
Các công thức này ngày càng dài hơn khi có nhiều cấp độ hơn. Phần khó nhất của một công thức như vậy là nhớ có bao nhiêu dấu ngoặc đóng để đặt ở cuối công thức.
Mẹo thưởng nhỏ
Khớp các dấu ngoặc đơn
Excel xoay vòng qua nhiều màu khác nhau cho mỗi cấp độ mới của dấu ngoặc đơn. Trong khi Excel sử dụng lại màu sắc, nó chỉ sử dụng màu đen cho dấu ngoặc mở và cho dấu ngoặc đóng phù hợp. Khi bạn hoàn thành công thức bên dưới, chỉ cần tiếp tục nhập dấu ngoặc đơn đóng cho đến khi bạn nhập dấu ngoặc đơn màu đen.
Quay lại Mẹo Công thức lồng nhau
Nếu bạn đang sử dụng Excel 2003, công thức của bạn đã gần đến giới hạn. Trước đó, bạn không thể lồng nhiều hơn 7 hàm IF. Đó là một ngày tồi tệ khi quyền hạn bị thay đổi kế hoạch hoa hồng và bạn cần một cách để thêm hàm IF thứ tám. Ngày nay, bạn có thể lồng 64 hàm IF. Bạn không bao giờ nên làm điều này, nhưng thật vui khi biết rằng không có vấn đề gì khi lồng 8 hoặc 9.
Thay vì sử dụng hàm IF lồng nhau, hãy thử sử dụng cách sử dụng bất thường cho hàm VLOOKUP. Khi đối số thứ tư của hàm VLOOKUP thay đổi từ Sai thành Đúng, hàm không còn tìm kiếm đối sánh chính xác nữa.
Vâng, trước tiên hàm VLOOKUP cố gắng tìm một kết quả phù hợp chính xác. Nhưng nếu không tìm thấy kết quả phù hợp chính xác, thì Excel sẽ xếp vào hàng ít hơn những gì bạn đang tìm kiếm.
Hãy xem xét bảng dưới đây. Trong ô C13, Excel sẽ tìm kiếm một kết quả phù hợp với $ 28.355 trong bảng. Khi không thể tìm thấy 28355, Excel sẽ trả về chiết khấu được liên kết với giá trị nhỏ hơn. Trong trường hợp này, chiết khấu 1% cho mức $ 10K.
Khi bạn chuyển đổi các quy tắc sang bảng trong E13: F18, bạn cần bắt đầu từ cấp độ nhỏ nhất và tiến tới cấp độ cao nhất. Mặc dù nó không được đánh dấu trong các quy tắc, nhưng nếu ai đó có doanh số bán hàng dưới $ 10.000, chiết khấu sẽ là 0%. Bạn cần thêm hàng này làm hàng đầu tiên trong bảng.
Thận trọng
Khi bạn đang sử dụng phiên bản “True” của hàm VLOOKUP, bảng của bạn phải được sắp xếp tăng dần. Nhiều người tin rằng tất cả các bảng tra cứu phải được sắp xếp. Nhưng một bảng chỉ cần được sắp xếp trong trường hợp thực hiện một trận đấu gần đúng.
Điều gì sẽ xảy ra nếu người quản lý của bạn muốn một công thức hoàn toàn khép kín và không muốn thấy bảng tiền thưởng lệch sang bên phải? Sau khi xây dựng công thức, bạn có thể nhúng bảng ngay vào công thức.
Đặt công thức ở chế độ Chỉnh sửa bằng cách bấm đúp vào ô hoặc bằng cách chọn ô và nhấn F2.
Sử dụng con trỏ, chọn toàn bộ đối số thứ hai: $ E $ 13: $ F $ 18.
Nhấn phím F9. Excel sẽ nhúng bảng tra cứu dưới dạng một hằng số mảng. Trong hằng số mảng, dấu chấm phẩy cho biết hàng mới và dấu phẩy cho biết cột mới.
Bấm phím Enter. Sao chép công thức xuống các ô khác.
Bây giờ bạn có thể xóa bảng. Công thức cuối cùng được hiển thị bên dưới.
Cảm ơn Mike Girvin đã dạy tôi về dấu ngoặc đơn phù hợp. Kỹ thuật VLOOKUP được đề xuất bởi Danny Mac, Boriana Petrova, Andreas Thehos và @mvmcos.
Xem video
- Với chương trình hoa hồng, tiền thưởng hoặc chiết khấu theo từng cấp, bạn thường phải lồng các hàm IF của mình
- Giới hạn Excel 2003 là 7 câu lệnh IF lồng nhau.
- Bây giờ bạn có thể lồng 32, nhưng tôi không nghĩ bạn nên lồng 32
- Khi nào bạn sử dụng phiên bản đối sánh gần đúng của hàm VLOOKUP? Đây là lúc.
- Dịch chương trình giảm giá thành một bảng tra cứu
- Trong hầu hết các trường hợp, hàm VLOOKUP sẽ không tìm thấy câu trả lời.
- Đặt, True ở cuối sẽ yêu cầu VLOOKUP tìm giá trị nhỏ hơn.
- Đây là lần duy nhất bảng VLOOKUP phải được sắp xếp.
- Bạn không muốn bảng VLOOKUP lệch sang một bên? Nhúng nó vào công thức.
- F2 để chỉnh sửa công thức. Chọn bảng tra cứu. Nhấn F9. Đi vào.
Bản ghi video
Học Excel từ podcast, tập 2030 - Thay thế IF lồng nhau bằng hàm VLOOKUP!
Tôi sẽ podcasting toàn bộ cuốn sách này, nhấp vào "i" ở góc trên bên phải để truy cập danh sách phát!
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Được rồi, điều này thực sự phổ biến với chương trình hoa hồng hoặc chương trình chiết khấu hoặc chương trình tiền thưởng, nơi chúng ta có các cấp, các cấp khác nhau, phải không? Nếu bạn trên $ 10000, bạn được chiết khấu 1%, $ 50000 được chiết khấu 5%. Bạn phải cẩn thận khi xây dựng câu lệnh IF lồng nhau này, bạn bắt đầu nó ở đầu trên cùng nếu bạn đang tìm kiếm lớn hơn hoặc ở cuối dưới cùng nếu bạn đang tìm kiếm nhỏ hơn. Vì vậy, B10> 50000, 20%, nếu không thì IF khác, B10> 250000, 25%, v.v. Đây chỉ là một công thức dài và phức tạp, và nếu bảng của bạn lớn hơn, trong Excel 2003, bạn không thể lồng nhiều hơn 7 câu lệnh IF, chúng ta gần như gần đến giới hạn ở đây. Bây giờ bạn có thể lên 32 tuổi, tôi không nghĩ bạn nên đến 32 tuổi, và ngay cả khi bạn đang hét lên "Này, chờ đã,Còn về hàm mới vừa ra mắt trong Excel 2016, bản phát hành tháng 2 năm 2016, với hàm IFS thì sao? ” Vâng chắc chắn, ở đây có ít dấu ngoặc hơn, và 87 ký tự thay vì 97 ký tự, nó vẫn còn là một mớ hỗn độn, hãy loại bỏ điều này và làm điều đó với một hàm VLOOKUP!
Được rồi, đây là các bước, bạn thực hiện các quy tắc đó và bạn thực hiện chúng theo ý mình. Điều đầu tiên chúng tôi phải nói là, nếu bạn có doanh số $ 0, bạn được chiết khấu 0%, nếu bạn có doanh số $ 10000, bạn được chiết khấu 1%, nếu bạn có doanh số $ 50000, bạn được chiết khấu 5% . 100000 đô la, giảm giá 10%, 250000 đô la, giảm giá 15% và cuối cùng, bất cứ thứ gì> 500000 đô la đều được giảm giá 20%. Bây giờ rất nhiều lần, mỗi khi tôi nói về VLOOKUP, tôi nói mọi hàm VLOOKUP mà bạn từng tạo sẽ kết thúc bằng FALSE và mọi người sẽ nói "Chà, chờ một chút, phiên bản TRUE ở đó là gì?" Đó là đối với trường hợp chúng ta đang tìm kiếm một phạm vi, vì vậy chúng ta đang tìm kiếm giá trị này, tất nhiên, một VLOOKUP thông thường, 2, FALSE sẽ không tìm thấy 550000, sẽ trả về giá trị # N / A!Vì vậy, trong một trường hợp rất đặc biệt này, chúng tôi sẽ thay đổi FALSE đó thành TRUE và điều đó sẽ cho Excel biết "Hãy tìm 550000, nếu bạn không thể tìm thấy nó, hãy cung cấp cho chúng tôi giá trị nhỏ hơn." Vì vậy, nó mang lại cho chúng ta 20%, đó là những gì nó làm, được chứ? Và đây là lần duy nhất bảng VLOOKUP của bạn phải được sắp xếp, tất cả những lần khác với, FALSE, có thể theo cách bạn muốn. Và vâng, bạn có thể bỏ đi, TRUE, nhưng tôi luôn đặt nó ở đó chỉ để nhắc nhở bản thân rằng đây là một trong những hàm VLOOKUP cực kỳ nguy hiểm và tôi không muốn sao chép công thức này ở nơi khác. Được rồi, chúng tôi sẽ sao chép và dán các công thức đặc biệt.ổn thỏa? Và đây là lần duy nhất bảng VLOOKUP của bạn phải được sắp xếp, tất cả những lần khác với, FALSE, có thể theo cách bạn muốn. Và vâng, bạn có thể bỏ đi, TRUE, nhưng tôi luôn đặt nó ở đó chỉ để nhắc nhở bản thân rằng đây là một trong những hàm VLOOKUP cực kỳ nguy hiểm và tôi không muốn sao chép công thức này ở nơi khác. Được rồi, chúng tôi sẽ sao chép và dán các công thức đặc biệt.ổn thỏa? Và đây là lần duy nhất bảng VLOOKUP của bạn phải được sắp xếp, tất cả những lần khác với, FALSE, có thể theo cách bạn muốn. Và vâng, bạn có thể bỏ đi, TRUE, nhưng tôi luôn đặt nó ở đó chỉ để nhắc nhở bản thân rằng đây là một trong những hàm VLOOKUP cực kỳ nguy hiểm và tôi không muốn sao chép công thức này ở nơi khác. Được rồi, chúng tôi sẽ sao chép và dán các công thức đặc biệt.
Khiếu nại tiếp theo: người quản lý của bạn không muốn xem bảng tra cứu, anh ấy muốn nó "Chỉ cần loại bỏ bảng tra cứu đó." Được rồi, chúng ta có thể làm điều đó bằng cách nhúng bảng tra cứu, hãy xem đây, mẹo hay mà tôi nhận được từ Mike Girvin tại ExcelIsFun. F2 để đưa công thức vào chế độ Chỉnh sửa, và sau đó chọn rất cẩn thận chỉ phạm vi cho bảng tra cứu. Nhấn F9, và nó lấy bảng này và đặt nó trong danh pháp mảng, và sau đó tôi chỉ cần nhấn Enter như vậy. Sao chép nó xuống, Dán Công thức Đặc biệt, và sau đó tôi có thể tự do loại bỏ bảng tra cứu, tất cả chỉ tiếp tục hoạt động xuống hàng. Đây là một rắc rối lớn nếu bạn phải quay lại và chỉnh sửa nó, bạn phải thực sự nhìn chằm chằm vào nó với rất nhiều sự rõ ràng. Dấu phẩy có nghĩa là chúng ta sẽ chuyển sang cột tiếp theo, dấu chấm phẩy có nghĩa là chúng ta sẽ chuyển sang hàng tiếp theo, được rồi,nhưng về lý thuyết, bạn có thể quay lại đó và thay đổi công thức đó nếu một trong các số chuỗi thay đổi.
Được rồi, sử dụng hàm VLOOKUP thay vì câu lệnh IF lồng nhau là mẹo số 32 trên con đường đến với 40, “40 mẹo Excel hay nhất mọi thời đại”, bạn có thể có toàn bộ cuốn sách này. Nhấp vào chữ “i” ở góc trên bên phải, $ 10 cho sách điện tử, $ 25 cho sách in. Vì vậy, hôm nay chúng tôi đang cố gắng giải quyết phần thưởng hoa hồng theo cấp độ hoặc chương trình chiết khấu. Các IF lồng nhau thực sự rất phổ biến, hãy coi chừng, 7 là tất cả những gì bạn có thể có trong Excel 2003, ngày nay bạn có thể có 32, nhưng không bao giờ nên có 32. Vì vậy, khi nào sử dụng phiên bản MATCH gần đúng của VLOOKUP, đây là nó. Lấy chương trình giảm giá đó, lật ngược nó lại, lập bảng tra cứu bắt đầu từ số nhỏ nhất và đến số lớn nhất. Tất nhiên, hàm VLOOKUP sẽ không tìm thấy câu trả lời, nhưng bằng cách thay đổi hàm VLOOKUP thành TRUE ở cuối, nó sẽ yêu cầu nó tìm giá trị nhỏ hơn,đây là lần duy nhất bảng phải được sắp xếp. Nếu bạn không muốn nhìn thấy bảng đó, bạn có thể nhúng nó vào công thức bằng thủ thuật Mike Girvin, F2 để chỉnh sửa công thức, chọn bảng tra cứu, nhấn F9, rồi nhấn Enter.
Đượ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: Podcast2030.xlsx
