Ovo je još jedan primjer brzine formule. Recimo da morate napraviti 12 stupaca VLOOKUP-a. To možete učiniti bržim pomoću jedne funkcije MATCH i 12 INDEX.
Na sljedećoj slici morat ćete obaviti 12 VLOOKUP funkcija za svaki broj računa. VLOOKUP je moćan, ali treba puno vremena za izračun.
Osim toga, formula se mora uređivati u svakoj ćeliji dok kopirate. Treći argument mora se promijeniti s 2 na 3 za veljaču, zatim 4 za ožujak i tako dalje.
Jedno zaobilazno rješenje je dodavanje retka s brojevima stupaca. Zatim, 3. argument VLOOKUP-a može ukazivati na ovaj redak. Barem možete kopirati istu formulu iz B4 i zalijepiti u C4: M4 prije kopiranja cijelog kompleta.
Ali ovdje je puno brži pristup. Dodajte novi stupac B s Gdje? kao naslov. Stupac B sadrži funkciju MATCH. Ova je funkcija vrlo slična VLOOKUP-u: Vrijednost tražite u A4 u stupcu P4: P227. 0 na kraju je poput False na kraju VLOOKUP-a. Određuje da želite točno podudaranje. Evo velike razlike: MATCH se vraća tamo gdje je vrijednost pronađena. Odgovor 208 kaže da je A308 208. ćelija u rasponu P4: P227. Iz vremenske perspektive podsjetnika, MATCH i VLOOKUP približno su jednaki.
Mogu čuti što mislite. “Kakva je korist znati gdje se nešto nalazi? Nikad me menadžer nije nazvao i pitao: 'U kojem je redu to potraživanje?' "
Iako se ljudi rijetko pitaju u kojem je redu nešto, funkcija INDEX može koristiti taj položaj. Sljedeća formula kaže Excelu da vrati 208. stavku iz Q4: Q227.
Dok kopirate ovu formulu, niz se vrijednosti kreće kroz tablicu pretraživanja. Za svaki redak radite jednu funkciju MATCH i 12 INDEX. Funkcija INDEX je nevjerojatno brza u usporedbi s VLOOKUP-om. Cjelokupni skup formula izračunat će 85% brže od 12 stupaca VLOOKUP-a.
Gledaj video
- Recimo da morate napraviti 12 stupaca VLOOKUP-a
- Pažljivo upotrijebite znak jednog dolara prije stupca vrijednosti pretraživanja
- Pažljivo upotrijebite znakove za četiri dolara za tablicu pretraživanja
- Još uvijek teško kodirate argument trećeg stupca.
- Jedno uobičajeno rješenje je dodavanje reda pomoćnih stanica s brojem stupca.
- Još jedno manje učinkovito rješenje je upotreba COLUMN (B2) unutar formule VLOOKUP.
- No, izvođenje 12 VLOOKUPA za svaki redak vrlo je neučinkovito
- Umjesto toga, dodajte pomoćni stupac s naslovom WHERE i napravite jedan Match.
- UTAKMICA traje koliko i VLOOKUP za siječanj.
- Tada možete koristiti 12 INDEX funkcija. Oni su nevjerojatno brzi u usporedbi s VLOOKUP-om.
- INDEX će ukazati na jedan stupac odgovora s $ prije redaka.
- INDEX će ukazati na pomoćni stupac s $ ispred stupca.
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2028 - Zamjena mnogih VLOOKUP-ova jednim MATCH-om!
Pritisnite to "i" u gornjem desnom kutu da biste došli do popisa za reprodukciju, ja ću podcasting cijelu ovu knjigu!
Hej, dobrodošao natrag na netcast, ja sam Bill Jelen! Pa to je klasičan problem, moramo raditi VLOOKUP jednom za svaki mjesec, zar ne? I ovdje možete biti nevjerojatno oprezni kada pritisnete F4 3 puta da biste to zaključali do stupca, a zatim pritisnete F4 nakon zaključavanja cijelog retka. Ali kad dođete do ove točke, 2, FALSE da 2 je teško kodirano, a dok to kopirate, morat ćete urediti 2 u 3, zar ne? Sad, jedan neučinkovit način da to učinim, način koji mi se ne sviđa je korištenje stupca B1. Stupac B1 je naravno 2, ali dok to kopirate, pogledajte da će se promijeniti u stupac C1, koji je 3, ali razmislite o tome, ovo stalno iznova otkriva broj stupca. Dakle, ono što vidim da ljudi rade i zašto, znate, preferiraju više od stupaca, jest da ćemo to Ctrl povući,stavite brojeve 2-13 tamo gore u pomoćnu ćeliju, a zatim, kad dođemo do ove točke, idemo gore i odredimo taj broj stupca. Pritisnite F4 dva puta da biste ga zaključali na red,, FALSE i tako dalje. Ali čak i s tom metodom, VLOOKUP je nevjerojatno neučinkovit, jer ovdje mora pretražiti sve ove stavke dok ne pronađe A308 i to je slika B4. Kad se zatim pomakne na C4, zaboravi da je samo pogledao i krene ispočetka, u redu. Dakle, imate jednu od najsporijih funkcija u cijelom Excelu, VLOOKUP, FALSE koji se iznova i iznova izvodi za istu stavku.jer ovdje mora pretražiti sve ove stavke dok ne pronađe A308 i to je slika B4. Kad se zatim pomakne na C4, zaboravi da je samo pogledao i krene ispočetka, u redu. Dakle, imate jednu od najsporijih funkcija u cijelom Excelu, VLOOKUP, FALSE koji se iznova i iznova izvodi za istu stavku.jer ovdje mora pretražiti sve ove stavke dok ne pronađe A308 i to je slika B4. Kad se zatim pomakne na C4, zaboravi da je samo pogledao i krene ispočetka, u redu. Dakle, imate jednu od najsporijih funkcija u cijelom Excelu, VLOOKUP, FALSE koji se iznova i iznova izvodi za istu stavku.
Dakle, evo puno, puno bržeg puta, umetnut ćemo pomoćni stupac, a ovaj pomoćni stupac zovem Gdje? Kao u A308? Upotrijebit ćemo = MATCH, potražiti A308 u prvom redu tablice, pritisnuti F4 tamo,, 0 za točno podudaranje, u redu, govori nam da "Hej, vidi to, to je u redu, 6, kako to je strašno? " Ali dok kopiramo, vidite, stalno je na različitim mjestima. Dobro, sada ovaj meč traje onoliko koliko traje siječanjski VLOOKUP, tamo su čak i mrtvi, ali ovdje je nevjerojatna stvar. Odatle nikada ne moramo raditi VLOOKUP za ostatak reda, mogli bismo samo = INDEX, INDEX kaže "Evo niza odgovora." Otići ću do ćelija u siječnju i vrlo ću pažljivo ovdje pritisnuti F4 2 puta, tako da zaključam na 4: 227,ali Q se smije mijenjati dok se krećem. Zarez, a zatim želi znati koji redak, pa to će biti odgovor u B4, pritisnut ću F4 3 puta da dođem do $ prije B, u redu, kopirajte to.
Ova formula, ove INDEX formule, ovih 12 dogodit će se za manje od vremena potrebnog za izradu VLOOKUP-a u veljači, u redu. Ako na to stavimo timer Charlesa Williamsa, cijela će ova stvar izračunati oko 14% vremena 12 VLOOKUP-ova. Vaš menadžer ne želi vidjeti Gdje? U redu, samo sakrijte tu kolonu, sve nastavlja raditi, u redu, ovo je prekrasan način da ubrzate 12 mjeseci ili 52 tjedna VLOOKUPA. U redu, ovaj savjet i još toliko savjeta nalaze se u ovoj knjizi. Kliknite "i" u gornjem desnom kutu tamo, možete kupiti knjigu, e-knjigu od 10 USD, 25 USD za tiskanu knjigu, u redu.
Dakle, danas smo imali problem u kojem u 12 stupaca VLOOKUP-a možete pažljivo staviti $, ali taj treći argument još uvijek mora biti čvrsto kodiran. Možete koristiti stupac (B2), nisam ljubitelj toga, jer postoji stotine redaka * 12 stupaca gdje se to izračunava iznova i iznova. Samo upotrijebite pomoćnu ćeliju u nizu, stavite brojeve 2-12 i ukažite na to, iako je to još uvijek neučinkovito, jer VLOOKUP nakon što shvati siječanj, mora početi na početku za veljaču. Stoga preporučujem dodavanje stupca s naslovom "Gdje?" i tamo napravite jednu utakmicu. Taj MATCH traje koliko i VLOOKUP za siječanj, ali tada će 12 INDEX funkcija trebati manje vremena nego VLOOKUP za veljaču, a vi ste skratili čitav niz vremena. Opet, oprezno s $ u funkciji INDEX na oba mjesta, jedno neposredno ispred redaka,a drugi prije stupaca, mješovita referenca u oba.
Hej, želim vam zahvaliti što ste navratili, vidimo se sljedeći put za još jedan prijenos od!
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Podcast2028.xlsx
