Jeśli masz duży arkusz roboczy, wiele funkcji WYSZUKAJ.PIONOWO może zacząć spowalniać działanie. Czy masz powolny arkusz z powodu WYSZUKAJ.PIONOWO? Mówię o arkuszu roboczym, którego obliczenia wymagają 40 sekund lub 4 minut. W dzisiejszym artykule niesamowita formuła z dwoma WYSZUKAJ.PIONOWO wykorzystująca wyszukiwanie zakresu rozwiąże problem.
WYSZUKAJ.PIONOWO to stosunkowo droga funkcja. Gdy szukasz dokładnego dopasowania, Excel musi przeglądać tabelę przeglądową po jednym wierszu na raz.
Skoroszyt, którego używam dzisiaj, wykonuje 7000 WYSZUKAJ.PIONOWO w tabeli zawierającej 116 000 pozycji. Na naprawdę szybkiej 64-bitowej maszynie z 8 rdzeniami czas przeliczania wynosi 3,01 sekundy.
Jednym ze sposobów ulepszenia funkcji WYSZUKAJ.PIONOWO jest przeniesienie najlepiej sprzedających się elementów na górę tabeli przeglądowej. Uzyskaj raport dotyczący 100 najlepiej sprzedających się przedmiotów i przenieś je na górę listy. Sortowanie według popularności skraca czas przeliczania do 0,369 sekundy. To osiem razy szybciej niż pierwszy wynik.
Ale jest sposób, aby jeszcze bardziej przyspieszyć. Gdy tworzysz swoją funkcję WYSZUKAJ.PIONOWO, gdy dojdziesz do czwartego argumentu, aby wybrać Fałsz, istnieje inna opcja, która jest rzadko używana. Excel mówi, że „True” wykonuje „przybliżone dopasowanie”. To wcale nie jest poprawne. Gdyby zespół Excela był uczciwy, wytłumaczyłby, że True „często podaje poprawną odpowiedź, ale w innych przypadkach, bez żadnego ostrzeżenia, zamieścimy tam złą odpowiedź. Mam nadzieję, że nie masz nic przeciwko powtórzeniu swoich danych Komisji Papierów Wartościowych i Giełd ”.
Jasne, jest odpowiedni czas na użycie True. Zobacz ten artykuł. Ale byłoby naprawdę źle użyć True, gdy próbujesz zrobić dokładne dopasowanie.
Jeśli spróbujesz użyć wartości True dla dokładnego dopasowania, często uzyskasz właściwą odpowiedź. Ale gdy szukanego elementu nie ma w tabeli, program Excel poda wartość z innego wiersza. To jest ta część, która sprawia, że „Prawda” nie jest początkiem dla wszystkich w księgowości. Zamknięcie nigdy nie jest poprawne w rachunkowości.
Uwaga
Nauczyłem się następującej sztuczki od Charlesa Williamsa. Jest czołowym światowym ekspertem w dziedzinie szybkości arkuszy. Jeśli masz powolny zeszyt ćwiczeń, zatrudnij Charlesa Williamsa na pół dnia konsultacji. Potrafi znaleźć wąskie gardła i przyspieszyć twój arkusz roboczy. Znajdź Charlesa na http://www.decisionmodels.com.
Podczas gdy ja i wszyscy księgowi odrzucamy argument „Prawdziwy” funkcji WYSZUKAJ.PIONOWO z powodu nieprzewidywalności, Charles Williams opowiada się za Prawdą. Wskazuje, że prawda jest znacznie szybsza niż fałsz. Setki razy szybciej. Przyznaje, że czasami otrzymujesz złą odpowiedź. Ale potrafi poradzić sobie z błędnymi odpowiedziami.
Charles faktycznie chce, żebyś zrobił dwie VLOOKUP. Najpierw wykonaj WYSZUKAJ.PIONOWO i zwróć kolumnę 1 z tabeli. Sprawdź, czy wynik jest tym, czego szukałeś w pierwszej kolejności. Jeśli ten wynik się zgadza, to wiesz, że wykonanie prawdziwej funkcji WYSZUKAJ.PIONOWO jest bezpieczne, aby zwrócić inną kolumnę z tabeli:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Pozornie wydaje się to szalone. Aby użyć metody Charlesa, musisz wykonać dwa razy więcej funkcji WYSZUKAJ.PIONOWO. Ale kiedy mierzysz czas obliczeń dla tej metody, jest on 35 razy szybszy niż normalna funkcja WYSZUKAJ.PIONOWO.
Zauważ, że chociaż większość tabel przeglądowych nie musi być sortowana, jeśli używasz True jako czwartego argumentu, tabela musi być sortowana. Aby uzyskać 7-minutową dyskusję na temat tego, jak wersja True funkcji WYSZUKAJ.PIONOWO przeskakuje przez tabelę wyszukiwania, zobacz http://mrx.cl/TrueVLOOKUP.
Dziękuję Charlesowi Williamsowi za nauczenie mnie tej funkcji i Scottowi St. Amantowi za nominację do 40 najlepszych wskazówek.
Obejrzyj wideo
- WYSZUKAJ.PIONOWO używane z Fałszem to powolna funkcja
- Sortowanie danych od A do Z nie przyspiesza funkcji
- Sortowanie według popularności może przyspieszyć działanie
- Przełączanie się na WYSZUKAJ.PIONOWO z Prawdą jest szybsze, ale zgłosi nieprawidłową odpowiedź, jeśli element nie zostanie znaleziony
- Aby złagodzić problem, wykonaj WYSZUKAJ.PIONOWO (A2, Tabela, 1, Prawda), aby najpierw sprawdzić, czy wynik to A2
- WYSZUKAJ.PIONOWO 14000 (prawda) i 7000 JEŻELI działa szybciej niż WYSZUKAJ.PIONOWO 7000 (fałsz)
Transkrypcja generowana automatycznie
- Naucz się programu Excel z podcastu
- odcinek 2031 szybszy podgląd Jestem
- podcasting wszystkich wskazówek zawartych w tej książce
- kliknij I w prawym górnym rogu
- aby dostać się do listy obserwowanych
- witaj z powrotem w mr. hutnik obsada
- Jestem Bill Jelen. Zrobiłem to
- wideo, zanim będzie jednym z moich ulubionych
- sztuczki, jeśli dobrze wyglądasz
- mieć gwiazdę vlookup biorąc 30 40 50
- sekundy, cztery minuty, wiesz wszystko
- spodoba ci się ten film, jeśli twój
- vlookup stick jedną sekundę, po prostu kliknij Dalej
- i przejdź do następnego filmu II mam plik
- vlookup tutaj, patrzy w stół
- z 115 000 pozycji wykonując 7000 vlookup
- użyjemy Charlesa Williamsa
- z szybkiego kodu Excela, aby zobaczyć, jak długo
- trzeba zrobić to vlookup w porządku cztery
- punkt zero dziewięć sekund to jest
- typowe vlookup z przecinkiem false w miejscu
- koniec i to wszystko wydarzyło się bo długo
- dawno temu zostałem zwabiony przez jakiegoś faceta
- Twitter, który powiedział, że byłoby lepiej, gdyby
- posortowałbyś swoją tablicę przeglądową a
- wysyłając powiedziałem, że nie, to wcale nie jest prawda
- nie ma znaczenia, czy pójdziemy do
- wysyłanie lub malejąco lub całkowicie
- losowy vlookup musi po prostu spojrzeć
- od przedmiotu do przedmiotu, a więc kiedy my
- posortuj tabelę, aby zobaczyć, że faktycznie zajmuje
- dłuższe cztery przecinek osiem cztery sekundy tak
- wiesz, że to nieprawda, że sortowanie plików
- stół przyspieszy, ale
- naprawdę coś, co może sprawić, że to pójdzie
- szybciej, gdybyś mógł sortować według
- popularność, jeśli możesz zdobyć to, co najlepsze
- sprzedawanie przedmiotów na górze listy
- nawet ty znasz swoją pierwszą pięćdziesiątkę, którą znasz
- jakie są Twoje 50 najlepiej sprzedających się przedmiotów
- umieść je na początku listy i
- obserwuj, że przez kilka sekund spada do 0,36
- sekund dziesięciokrotna poprawa czasu
- używając sortowania według popularności, teraz kilka
- lata temu miałem szczęście być
- zaprosił do Amsterdamu na prezentację na
- Szczyt Excela tam i nie jest
- na większości moich seminariów jestem tylko ja
- właśnie tam były dwa utwory, więc pokój a
- i pokój B i skończyłem w pokoju
- rozmowa o vlookupach i innych w pokoju
- zgadnij, kto siedział w tym pokoju to
- czy Charles Williams był w porządku i Charles
- tutaj jest
- jego imię jest wymieniane w
- ścianę, żeby podszedł, żeby to obejrzeć
- ogląda moje małe demo tam, gdzie idę
- od czterech sekund do 0,36 sekundy he
- potem podchodzi do mnie i mówi, że stawiam
- jesteś z tego całkiem zadowolony
- poprawa
- Mówię tak, to jest pełny namiot
- poprawa teraz Charles Charles ma rozszerzenie
- obsługa szybkiego Excela naszego modelu decyzyjnego
- modele decyzyjne są ograniczone
- pół dnia przeanalizuje twój zeszyt ćwiczeń
- a on twierdzi, że daje to sto
- razy szybciej w prawo, znajdzie plik
- wąskie gardła Annette i Charles Charles
- pochodzi od, jak mówi, spójrz na przecinek fałsz
- że ty i twoi znajomi księgowi jesteście
- robienie tego jest najwolniejszą rzeczą w programie Excel
- jeśli zrobiłbyś przecinek, prawda to
- tysiąc razy szybciej, a potem Charles
- mówi, że następna klauzula jest, jeśli tak nie jest
- naprawdę ważne, teraz mówi, że czasami tak
- źle, poczekaj Charles, nie
- rozumiem, że czasami jest księgowy
- źle to nie starter, którego nie akceptujemy
- czasami jest źle i czas
- że to źle, przecinek jest prawdziwy, kiedy
- robisz przecinek, prawda, idziemy patrzeć
- dla P 3 2 2 1 1 i nie został znaleziony
- po prostu dadzą ci ten przedmiot
- mniej w porządku i nic nie powiedzą
- nie mogliśmy tego znaleźć, oni po prostu
- po prostu dadzą ci Adama
- tylko mniej, że to niedopuszczalne i
- Charles mówi dobrze, co możemy
- wyobraź sobie, że przeoczyłeś 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
- wzrost, możesz przełączyć się na vlookup
- z prawdą, ale zgłosi błąd
- odpowiedz, jeśli przedmioty nie zostały znalezione, więc jesteśmy
- właściwie zrobię dwa vlookupy a
- dwa w kolumnie jeden przy stole i
- zobaczmy, czy otrzymamy z powrotem dwa jeśli
- można bezpiecznie przejść do środka
- wspólna kolumna, aby w przeciwnym razie mieć
- jeśli stwierdzenie mówi, że nie znaleziono w porządku
- o, hej, dzięki Charles Williams za
- ucząc mnie tej niesamowitej sztuczki i
- dzięki za zatrzymanie się zobaczy
- następnym razem na kolejny rzut z sieci
- MrExcel
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2031.xlsm
