Đây là một ví dụ về tốc độ công thức khác. Giả sử rằng bạn phải thực hiện 12 cột của hàm VLOOKUP. Bạn có thể làm cho nó nhanh hơn bằng cách sử dụng một hàm MATCH và 12 hàm INDEX.
Trong hình sau, bạn sẽ phải thực hiện 12 hàm Vlookup cho mỗi số tài khoản. Hàm VLOOKUP rất mạnh, nhưng cần rất nhiều thời gian để tính toán.
Ngoài ra, công thức phải được chỉnh sửa trong từng ô khi bạn sao chép. Đối số thứ ba phải thay đổi từ 2 thành 3 cho tháng Hai, sau đó 4 cho tháng Ba, v.v.
Một cách giải quyết là thêm một hàng với số cột. Sau đó, đối số thứ 3 của hàm VLOOKUP có thể trỏ đến hàng này. Ít nhất bạn có thể sao chép cùng một công thức từ B4 và dán vào C4: M4 trước khi sao chép toàn bộ tập hợp.
Nhưng đây là một cách tiếp cận nhanh hơn nhiều. Thêm một cột B mới với Where? làm tiêu đề. Cột B chứa một hàm MATCH. Hàm này rất giống với hàm VLOOKUP: Bạn đang tìm giá trị trong A4 trong cột P4: P227. Số 0 ở cuối giống như Sai ở cuối hàm VLOOKUP. Nó chỉ định rằng bạn muốn một kết hợp chính xác. Đây là sự khác biệt lớn: MATCH trả về nơi giá trị được tìm thấy. Câu trả lời của 208 nói rằng A308 là ô thứ 208 trong phạm vi P4: P227. Từ quan điểm thời gian recalc, MATCH và VLOOKUP gần bằng nhau.
Tôi có thể nghe những gì bạn đang nghĩ. “Có ích gì khi biết thứ gì đó nằm ở đâu? Tôi chưa bao giờ có người quản lý gọi đến và hỏi, 'Khoản phải thu đó nằm ở hàng nào?' "
Mặc dù con người hiếm khi hỏi thứ gì đó ở hàng nào, nhưng hàm INDEX có thể sử dụng vị trí đó. Công thức sau đây yêu cầu Excel trả về mục thứ 208 từ Q4: Q227.
Khi bạn sao chép công thức này qua, mảng giá trị sẽ di chuyển trên bảng tra cứu. Đối với mỗi hàng, bạn đang thực hiện một hàm MATCH và 12 hàm INDEX. Hàm INDEX cực kỳ nhanh so với hàm VLOOKUP. Toàn bộ bộ công thức sẽ tính toán nhanh hơn 85% so với 12 cột của hàm VLOOKUP.
Xem video
- Giả sử rằng bạn phải thực hiện 12 cột của hàm VLOOKUP
- Cẩn thận sử dụng một ký hiệu đô la duy nhất trước cột của giá trị tra cứu
- Cẩn thận sử dụng bốn ký hiệu đô la cho bảng tra cứu
- Bạn vẫn đang mã hóa đối số cột thứ ba.
- Một giải pháp phổ biến là thêm một hàng ô trợ giúp với số cột.
- Một giải pháp khác kém hiệu quả hơn là sử dụng COLUMN (B2) bên trong công thức VLOOKUP.
- Tuy nhiên, thực hiện 12 hàm VLOOKUP cho mỗi hàng là rất kém hiệu quả
- Thay vào đó, hãy thêm một cột trợ giúp với tiêu đề WHERE và thực hiện một Kết hợp duy nhất.
- MATCH mất nhiều thời gian bằng VLOOKUP cho tháng Giêng.
- Sau đó, bạn có thể sử dụng 12 hàm INDEX. Chúng cực kỳ nhanh so với hàm VLOOKUP.
- INDEX sẽ trỏ đến một cột câu trả lời với $ trước các hàng.
- INDEX sẽ trỏ đến cột trợ giúp với $ trước cột.
Bản ghi video
Học Excel từ podcast, tập 2028 - Thay thế Nhiều VLOOKUP bằng một TRẬN ĐẤU!
Nhấp vào chữ "i" ở góc trên bên phải để truy cập danh sách phát, tôi sẽ podcasting toàn bộ cuốn sách này!
Xin chào, chào mừng bạn trở lại với netcast, tôi là Bill Jelen! Đó là một vấn đề kinh điển, chúng ta phải thực hiện VLOOKUP mỗi tháng một lần, phải không? Và bạn có thể cực kỳ cẩn thận ở đây về việc nhấn F4 3 lần để khóa cột đó xuống, sau đó nhấn F4 một lần để khóa toàn bộ hàng. Nhưng khi bạn đến điểm này, dấu, 2, FALSE rằng 2 được mã hóa cứng, và khi bạn sao chép nó qua, bạn sẽ phải chỉnh sửa 2 thành 3, phải không? Bây giờ, một cách không hiệu quả để làm điều này, một cách mà tôi không thích là sử dụng cột B1. Cột B1 tất nhiên là 2, nhưng khi bạn sao chép nó qua, thấy rằng nó sẽ thay đổi thành cột C1, là 3, nhưng hãy nghĩ về điều này, điều này liên tục tìm ra số cột nhiều lần. Vì vậy, những gì tôi thấy mọi người làm và tại sao, bạn biết đấy, thích nhiều hơn các cột, là chúng ta sẽ Ctrl-kéo nó,đặt các số 2-13 lên đó trong một ô trợ giúp, và sau đó, khi chúng tôi đến điểm này, chúng tôi đi lên và chỉ định số cột đó. Nhấn F4 2 lần để khóa nó xuống hàng, FALSE, v.v. Nhưng ngay cả với phương pháp đó, hàm VLOOKUP cực kỳ kém hiệu quả, bởi vì nó phải tìm kiếm qua tất cả các mục này ở đây cho đến khi tìm thấy A308 và đó là con số B4. Sau đó, khi nó chuyển sang C4, nó quên rằng nó vừa đi vừa xem, và nó bắt đầu lại từ đầu, được thôi. Vì vậy, bạn có một trong những hàm chậm nhất trong tất cả Excel, hàm VLOOKUP, FALSE được thực hiện lặp đi lặp lại cho cùng một mục.bởi vì nó phải tìm kiếm tất cả các mục này ở đây cho đến khi nó tìm thấy A308 và đó là con số B4. Sau đó, khi nó chuyển sang C4, nó quên rằng nó vừa đi vừa xem, và nó bắt đầu lại từ đầu, được thôi. Vì vậy, bạn có một trong những hàm chậm nhất trong tất cả Excel, hàm VLOOKUP, FALSE được thực hiện lặp đi lặp lại cho cùng một mục.bởi vì nó phải tìm kiếm tất cả các mục này ở đây cho đến khi nó tìm thấy A308 và đó là con số B4. Sau đó, khi nó chuyển sang C4, nó quên rằng nó vừa đi vừa xem, và nó bắt đầu lại từ đầu, được thôi. Vì vậy, bạn có một trong những hàm chậm nhất trong tất cả Excel, hàm VLOOKUP, FALSE được thực hiện lặp đi lặp lại cho cùng một mục.
Vì vậy, đây là cách nhanh hơn rất nhiều, chúng ta sẽ chèn một cột trợ giúp, và cột trợ giúp này tôi gọi là Ở đâu? Như A308 đang ở đâu vậy? Chúng ta sẽ sử dụng a = MATCH, tìm A308 ở hàng đầu tiên của bảng, nhấn F4 ở đó, 0 để có kết quả khớp chính xác, được rồi, nó cho chúng ta biết rằng “Này, nhìn xem, nó ở hàng, 6, như thế nào thật tuyệt phải không? ” Nhưng khi chúng tôi sao chép lại, hãy xem, nó luôn ở những nơi khác nhau. Được rồi, bây giờ trận đấu này diễn ra miễn là VLOOKUP tháng 1 diễn ra, thậm chí họ đã chết, nhưng đây là điều tuyệt vời. Từ đó, chúng ta không bao giờ phải thực hiện hàm VLOOKUP cho phần còn lại của hàng, chúng ta có thể thực hiện = INDEX, INDEX cho biết "Đây là một loạt các câu trả lời." Tôi sẽ đi đến ô tháng Giêng, và tôi sẽ rất cẩn thận ở đây, nhấn F4 2 lần để tôi khóa nó xuống 4:227,nhưng Q được phép thay đổi khi tôi di chuyển. Dấu phẩy, và sau đó nó muốn biết hàng nào, thì đó sẽ là câu trả lời trong B4, tôi sẽ nhấn F4 3 lần để lấy $ trước chữ B, được rồi, sao chép nó qua.
Công thức này, các công thức INDEX này, 12 công thức này sẽ xảy ra trong ít hơn thời gian cần thiết để thực hiện VLOOKUP tháng 2, được rồi. Nếu chúng ta đặt bộ đếm thời gian của Charles Williams vào điều này, toàn bộ điều này sẽ tính toán khoảng 14% thời gian của 12 VLOOKUP. Người quản lý của bạn không muốn xem mục Ở đâu? Tốt thôi, chỉ cần ẩn cột đó đi, mọi thứ tiếp tục hoạt động, được rồi, đây là một cách tuyệt vời để tăng tốc 12 tháng hoặc 52 tuần của VLOOKUP. Được rồi, mẹo này và rất nhiều mẹo khác đều có trong sách này. Nhấp vào chữ “i” ở góc trên bên phải, bạn có thể mua sách, sách điện tử $ 10, sách in $ 25.
Vì vậy, hôm nay chúng ta gặp sự cố trong đó 12 cột của hàm VLOOKUP, bạn có thể cẩn thận đặt $ vào, nhưng đối số thứ 3 đó vẫn phải được mã hóa cứng. Bạn có thể sử dụng cột (B2), tôi không thích điều đó, bởi vì có hàng trăm hàng * 12 cột đang tính toán điều đó lặp đi lặp lại. Tuy nhiên, chỉ cần sử dụng một ô trợ giúp liên tiếp, đặt các số từ 2-12 và chỉ vào đó, nó vẫn không hiệu quả, vì hàm VLOOKUP sau khi tìm ra tháng Giêng, nó phải bắt đầu lại từ đầu tháng Hai. Vì vậy, tôi khuyên bạn nên thêm một cột có tiêu đề "Ở đâu?" và thực hiện một TRẬN ĐẤU duy nhất ở đó. MATCH đó mất nhiều thời gian như hàm VLOOKUP cho tháng Giêng, nhưng sau đó 12 hàm INDEX sẽ mất ít thời gian hơn hàm VLOOKUP cho tháng Hai và bạn đã cắt bớt rất nhiều thời gian. Một lần nữa, hãy cẩn thận với $ trong hàm INDEX ở cả hai vị trí, một ngay trước các hàng,và cái còn lại trước các cột, một tham chiếu hỗn hợp trong cả hai.
Này, tôi muốn cảm ơn bạn đã ghé qua, chúng ta sẽ gặp lại bạn vào 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: Podcast2028.xlsx
