Nếu bạn có một trang tính lớn, nhiều VLOOKUP có thể bắt đầu làm mọi thứ chậm lại. Bạn có một trang tính chậm do VLOOKUP? Tôi đang nói một trang tính cần 40 giây hoặc 4 phút để tính toán. Trong bài viết hôm nay, một công thức tuyệt vời với hai hàm VLOOKUP sử dụng tra cứu phạm vi sẽ giải quyết vấn đề.
VLOOKUP là một hàm tương đối đắt tiền. Khi bạn đang tìm kiếm kết hợp chính xác, Excel phải xem qua bảng tra cứu từng hàng một.
Sổ làm việc mà tôi đang sử dụng hôm nay đang thực hiện 7000 VLOOKUP thành một bảng gồm 116.000 mục. Trên một máy 64-bit thực sự nhanh với 8 lõi, thời gian sửa lại là 3,01 giây.
Một cách để cải thiện hàm VLOOKUP là di chuyển các mặt hàng bán chạy nhất lên đầu bảng tra cứu. Nhận báo cáo về 100 mặt hàng bán chạy nhất và chuyển những mặt hàng đó lên đầu danh sách. Sắp xếp theo mức độ phổ biến cải thiện thời gian xác minh lại thành 0,369 giây. Điều này nhanh hơn tám lần so với kết quả đầu tiên.
Nhưng có một cách để tăng tốc mọi thứ hơn nữa. Khi bạn đang xây dựng hàm VLOOKUP của mình, khi bạn đến đối số thứ tư để chọn Sai, có một tùy chọn khác hiếm khi được sử dụng. Excel cho biết “Đúng” thực hiện một “kết hợp gần đúng”. Điều này không đúng chút nào. Nếu nhóm Excel trung thực, họ sẽ giải thích rằng True “thường xuyên đưa ra câu trả lời đúng, nhưng những lần khác, nếu không có bất kỳ cảnh báo nào, chúng tôi sẽ đánh trượt câu trả lời sai trong đó. Tôi hy vọng bạn không phiền khi gửi lại các con số của mình cho Ủy ban Chứng khoán và Giao dịch. "
Chắc chắn, có một thời gian thích hợp để sử dụng True. Xem bài viết này. Nhưng sẽ thực sự không tốt nếu sử dụng True khi bạn đang cố gắng thực hiện một đối sánh chính xác.
Nếu bạn cố gắng sử dụng True cho một đối sánh chính xác, bạn sẽ nhận được câu trả lời đúng trong rất nhiều thời gian. Nhưng khi mục bạn đang tìm không có trong bảng, Excel sẽ cung cấp cho bạn giá trị từ một hàng khác. Đây là phần làm cho “True” không phải là điểm khởi đầu cho tất cả mọi người trong ngành Kế toán. Đóng không bao giờ đúng trong Kế toán.
Ghi chú
Tôi đã học được mẹo sau đây từ Charles Williams. Anh ấy là chuyên gia hàng đầu thế giới về tốc độ trang tính. Nếu bạn có một cuốn sổ làm việc chậm, hãy thuê Charles Williams trong nửa ngày tư vấn. Anh ấy có thể tìm ra điểm nghẽn và làm cho bảng tính của bạn nhanh hơn. Tìm Charles tại http://www.decisionmodels.com.
Trong khi tôi và tất cả các nhân viên kế toán bác bỏ lập luận "Đúng" của hàm VLOOKUP vì tính không thể đoán trước, thì Charles Williams lập luận cho Đúng. Ông chỉ ra rằng True nhanh hơn False rất nhiều. Nhanh hơn hàng trăm lần. Anh ấy thừa nhận rằng đôi khi bạn nhận được câu trả lời sai. Nhưng anh ấy có một cách để đối phó với những câu trả lời sai.
Charles thực sự muốn bạn thực hiện hai hàm VLOOKUP. Đầu tiên, hãy thực hiện một hàm VLOOKUP và trả về cột 1 từ bảng. Xem kết quả có đúng như những gì bạn đã tìm kiếm ngay từ đầu hay không. Nếu kết quả đó khớp, thì bạn biết là an toàn khi thực hiện hàm VLOOKUP thực để trả về một số cột khác từ bảng:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Về mặt nó, điều này có vẻ điên rồ. Để sử dụng phương pháp của Charles, bạn phải thực hiện gấp đôi số hàm VLOOKUP. Tuy nhiên, khi bạn căn thời gian tính toán cho phương pháp này, nó nhanh hơn 35 lần so với hàm VLOOKUP thông thường.
Lưu ý rằng mặc dù hầu hết các bảng tra cứu không cần phải được sắp xếp, nhưng khi bạn đang sử dụng True làm đối số thứ tư, thì bảng phải được sắp xếp. Để thảo luận 7 phút về cách phiên bản True của VLOOKUP nhảy qua bảng tra cứu, hãy xem http://mrx.cl/TrueVLOOKUP.
Cảm ơn Charles Williams đã dạy tôi tính năng này và cảm ơn Scott St. Amant vì đã đề cử nó cho top 40 mẹo.
Xem video
- VLOOKUP khi được sử dụng với False là một hàm chậm
- Sắp xếp dữ liệu AZ không tăng tốc chức năng
- Sắp xếp theo mức độ phổ biến có thể tăng tốc chức năng
- Chuyển sang VLOOKUP bằng True nhanh hơn nhưng nó sẽ báo sai câu trả lời nếu không tìm thấy mục
- Để giảm thiểu sự cố, hãy thực hiện hàm VLOOKUP (A2, Table, 1, True) để xem kết quả có phải là A2 trước không
- 14000 VLOOKUP (Đúng) và 7000 IF chạy nhanh hơn 7000 VLOOKUP (Sai)
Bản ghi được tạo tự động
- Học Excel từ Podcast
- tập 2031 nhanh hơn vlookup tôi
- podcasting tất cả các mẹo trong cuốn sách này
- nhấp vào I ở góc trên cùng bên phải
- để vào danh sách theo dõi
- chào mừng trở lại với mr. diễn viên hutnik
- Tôi là Bill Jelen Tôi đã làm việc này
- video trước khi nó là một trong những video yêu thích của tôi
- thủ thuật nếu bạn có giao diện nếu bạn
- có sao vlookup lấy 30 40 50
- giây bốn phút bạn biết bất cứ điều gì
- bạn sẽ thích video này nếu bạn
- vlookup stick một giây chỉ cần nhấp vào Tiếp theo
- và chuyển sang video tiếp theo II có một
- vlookup ở đây nó đang nhìn vào bảng
- trong số 115.000 mặt hàng có 7000 vlookup như vậy
- chúng tôi sẽ sử dụng một số Charles Williams
- từ mã Excel nhanh để xem nó dài bao lâu
- cần thực hiện vlookup này nhé bốn
- điểm không chín giây đó là
- vlookup điển hình với dấu phẩy sai ở
- kết thúc và tất cả điều này xuất hiện vì lâu
- từ lâu, tôi đã bị một số người dụ dỗ về
- Twitter đã nói rằng điều đó sẽ tốt hơn nếu
- bạn sẽ sắp xếp bảng tra cứu của mình
- gửi tôi đã nói không, điều đó không đúng chút nào
- nó không quan trọng cho dù chúng ta đi một
- gửi hoặc giảm dần hoặc hoàn toàn
- ngẫu nhiên vlookup chỉ cần đi tìm
- từ mặt hàng này sang mặt hàng khác và như vậy khi chúng tôi
- sắp xếp bảng xem nó thực sự mất
- còn bốn phẩy tám bốn giây vậy
- bạn biết không đúng khi sắp xếp
- bảng sẽ làm cho nó đi nhanh hơn nhưng
- thực sự là điều có thể làm cho nó đi
- nhanh hơn nếu bằng cách nào đó bạn có thể sắp xếp theo
- sự nổi tiếng nếu bạn có thể đạt được điều tốt nhất
- bán các mặt hàng ở đầu danh sách
- thậm chí bạn biết năm mươi hàng đầu của bạn mà bạn biết
- 50 mặt hàng bán chạy nhất của bạn là gì
- đưa những thứ đó lên đầu danh sách và
- xem trong giây giảm xuống 0,36
- giây, cải thiện gấp mười lần về thời gian
- sử dụng sắp xếp theo mức độ phổ biến bây giờ hey một vài
- nhiều năm trước, tôi đủ may mắn để được
- được mời đến Amsterdam để trình bày tại một
- Hội nghị thượng đỉnh Excel ở đó và nó không giống như
- hầu hết các cuộc hội thảo của tôi nơi chỉ có tôi
- ngay có hai bài hát nên phòng a
- và phòng B và tôi đã ở trong phòng được
- nói về vlookup và hơn thế nữa trong phòng
- đoán xem ai đã ngồi trong phòng đó
- Charles Williams có ổn không và Charles
- đây
- tên của anh ấy được đề cập thông qua
- bức tường nên anh ấy đến xem nó
- xem bản demo nhỏ của tôi ở đó nơi tôi đến
- từ bốn giây đến 0,36 giây anh ta
- đến gặp tôi sau đó anh ấy nói tôi cá
- bạn khá hài lòng với điều đó
- cải tiến
- Tôi nói vâng, đó là một cái lều đầy
- cải tiến bây giờ Charles Charles có
- dịch vụ của Excel nhanh chóng, mô hình quyết định của chúng tôi
- các mô hình quyết định giới hạn chúng tôi đang ở
- nửa ngày anh ấy sẽ phân tích sổ làm việc của bạn
- và anh ấy tuyên bố làm cho nó một trăm
- nhanh hơn nhiều lần, anh ấy sẽ tìm thấy
- tắc nghẽn Annette và Charles Charles
- xuất phát từ việc anh ấy nói hãy nhìn vào dấu phẩy sai
- rằng bạn và những người bạn kế toán của bạn là
- làm điều đó là điều chậm nhất trong Excel
- nếu bạn thực hiện một dấu phẩy đúng thì đó là một
- nhanh hơn hàng nghìn lần và sau đó là Charles
- cho biết Điều khoản tiếp theo này là nếu nó không
- thực sự quan trọng anh ấy nói bây giờ đôi khi nó là
- sai rồi, đợi đã Charles, bạn không
- hiểu một kế toán đôi khi là
- sai là không bắt đầu, chúng tôi không chấp nhận
- đôi khi nó sai và thời gian
- rằng nó sai, dấu phẩy đúng khi
- bạn đang làm một dấu phẩy đúng là chúng ta đi tìm
- cho một P 3 2 2 1 1 và nó không được tìm thấy
- họ sẽ đưa cho bạn món đồ chỉ
- ít ổn hơn và họ sẽ không nói
- bạn, chúng tôi không thể tìm thấy nó, họ chỉ
- họ sẽ chỉ cho bạn Adam
- chỉ bớt rằng điều đó không thể chấp nhận được và
- Charles nói tốt đây là những gì chúng ta có thể
- hãy tưởng tượng nếu bạn thực hiện một vlookup của P 3 2
- 2 1 1 into just column G ask for the
- first column comma true and see if what
- you get back is what you were looking
- for if what you get back is what you're
- looking for then you know it's safe to
- go to the second vlookup if it's not
- what you were looking for then you have
- an if statement there that says not
- found alright so we do a vlookup of a2
- into the table comma 1 see if it's equal
- to 82 if it is then it's safe to go on
- and do the second vlookup otherwise they
- not found I said Charles do you realize
- I'm doing 7000 vlookups and now you're
- gonna be doing 7 14000 vlookups and 7000
- if statements I I said you really think
- this is gonna be faster sure I said well
- I'll bet you a pint alright so here we
- go remember the vlookup that all of us
- are doing with the comma falls 4.0 9
- seconds the sort by popularity which is
- kind of hard to do
- Oh point three seconds here we go here's
- Charles Williams are you ready look at
- that point zero four four one hundredths
- of a second from four seconds down to
- four one hundreds of a second imagine if
- you had a spreadsheet that was taking
- for 40 seconds to calculate and how much
- faster would be using this to vlookup
- method it's an amazing trick and yeah I
- guess I stole the trick and put it in
- the book although I put a great cartoon
- version of Charles Williams in there
- saying he's the fastest guy in all of
- Excel you can buy this tip and all of
- the other tips in this book click that I
- at the top right hand corner
- all right so recap vlookup when used
- with false it's a slow function sorting
- the data a disease does not speed up the
- function unless you sell a lot of things
- to begin with a and B sorting by
- popularity does it's about a tenfold
- tăng bạn có thể chuyển sang vlookup
- với đúng nhưng nó sẽ báo sai
- trả lời nếu các mục không được tìm thấy vì vậy chúng tôi
- thực sự sẽ thực hiện hai lần tra cứu vlookup a
- hai trong cột một trên bảng và
- xem liệu những gì chúng ta nhận lại có phải là hai nếu
- nó là an toàn để làm vlookup trong
- cột chung để có một
- nếu tuyên bố nó nói rằng không tìm thấy tất cả đúng
- ồ này, cảm ơn Charles Williams vì
- dạy tôi mẹo tuyệt vời đó và
- cảm ơn bạn đã ghé qua sẽ thấy
- lần sau bạn cho một truyền trực tuyến khác từ
- MrExcel
Tải tập tin
Tải file mẫu tại đây: Podcast2031.xlsm
