To kolejny przykład szybkości formuły. Powiedz, że musisz zrobić 12 kolumn funkcji WYSZUKAJ.PIONOWO. Możesz to przyspieszyć, używając jednej funkcji MATCH i 12 funkcji INDEX.
Na poniższym rysunku będziesz musiał wykonać 12 funkcji WYSZUKAJ.PIONOWO dla każdego numeru konta. WYSZUKAJ.PIONOWO jest potężne, ale wykonywanie obliczeń zajmuje dużo czasu.
Ponadto formuła musi być edytowana w każdej komórce podczas kopiowania. Trzeci argument należy zmienić z 2 na 3 dla lutego, następnie 4 dla marca i tak dalej.
Jednym ze sposobów obejścia tego problemu jest dodanie wiersza z numerami kolumn. Następnie trzeci argument funkcji WYSZUKAJ.PIONOWO może wskazywać na ten wiersz. Przynajmniej możesz skopiować tę samą formułę z B4 i wkleić do C4: M4 przed skopiowaniem całego zestawu.
Ale tutaj jest znacznie szybsze podejście. Dodaj nową kolumnę B z opcją Gdzie? jako nagłówek. Kolumna B zawiera funkcję MATCH. Ta funkcja jest bardzo podobna do funkcji WYSZUKAJ.PIONOWO: szukasz wartości w A4 w kolumnie P4: P227. 0 na końcu jest jak fałsz na końcu WYSZUKAJ.PIONOWO. Określa, że chcesz uzyskać dokładne dopasowanie. Oto duża różnica: PODAJ.POZYCJĘ zwraca tam, gdzie została znaleziona wartość. Odpowiedź 208 mówi, że A308 to 208-ta komórka w zakresie P4: P227. Z perspektywy czasu przeliczania, PODAJ.POZYSK. I WYSZUKAJ.
Słyszę, co myślisz. „Po co wiedzieć, gdzie coś się znajduje? Nigdy nie miałem menedżera dzwoniącego i pytającego: „W jakim rzędzie jest to należność?” ”
Chociaż ludzie rzadko pytają, w którym wierszu coś się znajduje, funkcja INDEKS może użyć tej pozycji. Poniższa formuła nakazuje programowi Excel zwrócenie 208. pozycji z 4 kw .: 227.
Podczas kopiowania tej formuły w poprzek tablica wartości przesuwa się po tabeli przeglądowej. Dla każdego wiersza wykonujesz jedną funkcję MATCH i 12 funkcji INDEX. Funkcja INDEKS jest niesamowicie szybka w porównaniu z WYSZUKAJ.PIONOWO. Cały zestaw formuł obliczy 85% szybciej niż 12 kolumn funkcji WYSZUKAJ.PIONOWO.
Obejrzyj wideo
- Powiedz, że musisz zrobić 12 kolumn funkcji WYSZUKAJ.PIONOWO
- Ostrożnie użyj pojedynczego znaku dolara przed kolumną wartości wyszukiwania
- Ostrożnie użyj czterech znaków dolara w tabeli przeglądowej
- Nadal na stałe wpisujesz argument trzeciej kolumny.
- Typowym rozwiązaniem jest dodanie wiersza komórek pomocniczych z numerem kolumny.
- Innym mniej wydajnym rozwiązaniem jest użycie KOLUMNY (B2) w formule WYSZUKAJ.PIONOWO.
- Jednak wykonanie 12 funkcji WYSZUKAJ.PIONOWO dla każdego wiersza jest bardzo nieefektywne
- Zamiast tego dodaj kolumnę pomocniczą z nagłówkiem WHERE i wykonaj pojedyncze dopasowanie.
- MATCH trwa tak długo, jak WYSZUKAJ.PIONOWO w styczniu.
- Możesz wtedy użyć 12 funkcji INDEKSU. Są one niesamowicie szybkie w porównaniu do funkcji WYSZUKAJ.PIONOWO.
- INDEKS wskaże pojedynczą kolumnę odpowiedzi ze znakiem $ przed wierszami.
- INDEKS będzie wskazywał kolumnę pomocniczą z $ przed kolumną.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2028 - Zastąpienie wielu funkcji WYSZUKAJ.PIONOWO jednym MATCH!
Kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania. Będę podcastował całą książkę!
Hej, witaj ponownie w netcastie, jestem Bill Jelen! Cóż, to klasyczny problem, musimy wykonywać WYSZUKAJ.PIONOWO raz na miesiąc, prawda? Możesz tutaj być niesamowicie ostrożny, naciskając klawisz F4 3 razy, aby zablokować to w kolumnie, a następnie naciskając klawisz F4, gdy zablokujesz cały wiersz. Ale kiedy dojdziesz do tego punktu, 2, FALSE, które 2 jest zakodowane na stałe, a gdy skopiujesz to w poprzek, będziesz musiał zmienić 2 na 3, prawda? Teraz, jednym nieefektywnym sposobem, aby to zrobić, sposobem, którego nie lubię, jest użycie kolumny B1. Kolumna B1 to oczywiście 2, ale kiedy skopiujesz to w poprzek, zobacz, że zmieni się na kolumnę C1, która jest 3, ale pomyśl o tym, to jest ciągłe obliczanie numeru kolumny w kółko. Widzę więc, że ludzie robią i dlaczego, wiesz, wolą bardziej niż kolumny, przeciągniemy to z wciśniętym klawiszem Ctrl,umieść liczby 2-13 w komórce pomocniczej, a następnie, kiedy dojdziemy do tego punktu, podchodzimy do góry i określamy numer tej kolumny. Naciśnij klawisz F4 2 razy, aby zablokować go w wierszu, FALSE i tak dalej. Ale nawet przy tej metodzie WYSZUKAJ.PIONOWO jest niesamowicie nieefektywne, ponieważ musi przeszukać wszystkie te elementy tutaj, aż znajdzie A308 i to jest wynik B4. Kiedy następnie przechodzi do C4, zapomina, że po prostu poszedł i spojrzał, i zaczyna się od nowa, w porządku. Masz więc jedną z najwolniejszych funkcji w całym programie Excel, funkcja WYSZUKAJ.PIONOWO, FAŁSZ jest wykonywana w kółko dla tego samego elementu.ponieważ musi przeszukać wszystkie te elementy, aż znajdzie A308 i to jest wynik B4. Kiedy następnie przechodzi do C4, zapomina, że po prostu poszedł i spojrzał, i zaczyna się od nowa, w porządku. Masz więc jedną z najwolniejszych funkcji w całym programie Excel, funkcja WYSZUKAJ.PIONOWO, FAŁSZ jest wykonywana w kółko dla tego samego elementu.ponieważ musi przeszukać wszystkie te elementy, aż znajdzie A308 i to jest wynik B4. Kiedy następnie przechodzi do C4, zapomina, że po prostu poszedł i spojrzał, i zaczyna się od nowa, w porządku. Masz więc jedną z najwolniejszych funkcji w całym programie Excel, funkcja WYSZUKAJ.PIONOWO, FAŁSZ jest wykonywana w kółko dla tego samego elementu.
Oto o wiele, dużo szybszy sposób, zamierzamy wstawić kolumnę pomocniczą i nazywam ją kolumną pomocniczą Gdzie? Jak gdzie do cholery jest A308? Użyjemy = MATCH, poszukaj A308 w pierwszym wierszu tabeli, naciśnij klawisz F4, 0, aby uzyskać dokładne dopasowanie, w porządku, mówi nam, że „Hej, spójrz na to, jest w wierszu, 6, jak niesamowite czy to jest? ” Ale kiedy kopiujemy, widzicie, jest to cały czas w różnych miejscach. W porządku, teraz ten mecz trwa tak długo, jak styczniowe WYSZUKAJ.PIONOWO, nawet nie żyją, ale oto niesamowita rzecz. Stamtąd nigdy nie musimy wykonywać WYSZUKAJ.PIONOWO dla reszty wiersza, moglibyśmy po prostu wykonać = INDEKS, INDEKS mówi „Oto tablica odpowiedzi”. Mam zamiar przejść do komórek stycznia i zamierzam bardzo ostrożnie nacisnąć F4 2 razy, więc zablokuję go na 4: 227,ale Q może się zmieniać, gdy się poruszam. Przecinek, a potem chce wiedzieć, który wiersz, cóż, to będzie odpowiedź w B4, naciśnę F4 3 razy, aby uzyskać $ przed B, w porządku, skopiuj to w poprzek.
Ta formuła, te formuły INDEKSU, te 12 wydarzy się w czasie krótszym niż czas potrzebny na wykonanie funkcji WYSZUKAJ.PIONOWO w lutym. Jeśli ustawimy na tym licznik czasu Charlesa Williamsa, cała ta sprawa obliczy około 14% czasu 12 VLOOKUP. Twój menedżer nie chce widzieć strony Gdzie? Dobrze, po prostu ukryj tę kolumnę, wszystko działa, w porządku, to piękny sposób na przyspieszenie 12 miesięcy lub 52 tygodni VLOOKUP. W porządku, ta wskazówka i wiele innych wskazówek znajdują się w tej książce. Kliknij „i” w prawym górnym rogu, możesz kupić książkę, e-book za 10 USD, 25 USD za książkę drukowaną, w porządku.
Więc dzisiaj mieliśmy problem z 12 kolumnami WYSZUKAJ.PIONOWO, możesz ostrożnie wstawić $, ale wtedy ten trzeci argument nadal musi być zakodowany. Możesz użyć kolumny (B2), nie jestem jej fanem, ponieważ są setki wierszy * 12 kolumn, w których obliczam to w kółko. Po prostu użyj komórki pomocniczej w rzędzie, wpisz cyfry 2-12 i wskaż na to, ale nadal jest nieefektywna, ponieważ WYSZUKAJ.PIONOWO po obliczeniu stycznia, musi zacząć od początku lutego. Dlatego polecam dodanie kolumny z nagłówkiem „Gdzie?” i zrobić tam pojedynczy MECZ. To PODEJŚCIE trwa tak długo, jak WYSZUKAJ.PIONOWO w styczniu, ale 12 funkcji INDEKSU zajmie mniej czasu niż WYSZUKAJ.PIONOWO w lutym. Ponownie, uważaj na znak $ w funkcji INDEKS w obu miejscach, jeden tuż przed wierszami,a drugi przed kolumnami, mieszane odniesienie w obu z nich.
Hej, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2028.xlsx
