VLOOKUP este minunat și funcția mea preferată
Nu numai că aceste tabele facilitează reîmprospătarea datelor, ci și facilitează citirea formulelor! Singurul lucru pe care trebuie să-l faceți este să apăsați Ctrl + T înainte de a scrie formula.
Să ne întoarcem la formula VLOOKUP de sus. De această dată, convertiți tabelul cu articole și tabelul de cumpărare într-un tabel Excel cu Ctrl + T chiar de la început! Pentru a face lucrurile mai ușoare, dați fiecărui tabel un nume prietenos folosind fila Instrumente de masă:
Acum introduceți din nou VLOOKUP fără a face nimic diferit decât faceți în mod normal, formula dvs. în C2 este acum =VLOOKUP((@Item),Items,2,0)în loc de =VLOOKUP(B2,$E$5:$F$10,2,0)!
Chiar dacă tabelul Elemente este pe o foaie de lucru diferită, formula este aceeași, în loc de cea mai puțin lizibilă =VLOOKUP(B2,Items!$A$2:$B$7,2,0).
(@Item) din formulă se referă la celula din coloana Articol din acest tabel (în același rând cu formula) și, prin urmare, este aceeași în întreaga coloană. Și Elemente se referă la întregul tabel cu articole (fără anteturi). Cel mai bun dintre toate, nu trebuie să introduceți nimic din toate acestea. Odată ce acesta este un tabel, Excel va plasa aceste nume în formula dvs. pe măsură ce selectați celulele / intervalele!
Să facem acest pas mai departe. Adăugați o altă coloană în tabelul Vânzări pentru a calcula veniturile cu formula =(@Price)*(@Qty). Dacă doriți acum să calculați venitul total, formula este =SUM(Sales(Revenue)); ceea ce este foarte ușor de înțeles, indiferent unde sunt datele sau câte rânduri acoperă!
Urmăriți videoclipul
- VLOOKUP este minunat și funcția mea preferată
- VLOOKUP haters se plânge că este fragilă din cauza celui de-al treilea argument
- Dacă se modifică forma tabelului dvs. de căutare, răspunsurile se pot modifica
- O soluție este înlocuirea celui de-al treilea argument cu MATCH
- Dar imaginați-vă să faceți un MATCH pentru 1000 de rânduri de VLOOKUP
- Transformați tabelul de căutare într-un tabel înainte de a efectua VLOOKUP
- Referința tabelului structurat se va gestiona dacă se modifică forma tabelului
- În plus, nu necesită repetarea meciului
- Peter Albert a trimis acest sfat
Transcriere video
Aflați Excel pentru Podcast, Episodul 2003 - Referințe lizibile
Nu uitați să vă abonați la lista de redare XL. Voi fi podcast pe toată această carte.
Bine sfatul de astăzi de la Peter Albert. Peter Albert. Acum să vorbim despre VLOOKUP. Sunt un mare fan VLOOKUP. Pentru mine VLOOKUP este linia de demarcație. Dacă puteți face VLOOKUP-uri, orice altceva din Excel va fi ușor pentru dvs. Așadar, VLOOKUP ne permite să căutăm prețul din acel tabel și vom vorbi despre VLOOKUP mai târziu.
Deci, copiați acest lucru și totul funcționează bine, dar trebuie să vă spun. Le-am văzut. Am vorbit cu ei. I-am cunoscut. Există VLOOKUP haters acolo. Oamenii care urăsc dacă vă uitați în sus și ce alte reclamații sunt că este atât de fragil, acel al treilea argument, în care am spus că vrem a treia coloană, că dacă cineva ar decide mai târziu că avem nevoie de un nou câmp aici, poate ca dimensiunea . În regulă, în primul rând, pare să existe un fel de eroare în care Excel nu recalculează totul. Lasă-mă să anulez, să anulez și apoi să refac. Iată-ne. Este ciudat, am raportat acest lucru echipei Excel, dar vedeți că acolo unde obținem preț, acum capătă culoare, pentru că a fost greu codificat să spună că vor a treia coloană. Bine și ceea ce fac oamenii pentru a rezolva acest lucru este un lucru nebun cu = MATCH.Căutați cuvântul Preț în primul rând al tabelului, F4,0 și asta ne va spune că prețul în acest moment este a patra coloană. Așa că vor face de fapt = VLOOKUP. Căutăm A104, în acest tabel. F4 și apoi, în loc de a codifica cu greu numărul patru, fac un MATCH și MATCH-ul va fi blocat la preț. Deci F4, de două ori pentru a pune $ înainte de 1 și va fi căutat prin primul rând al mesei. Hopa, F4 de două ori, virgulă, a ratat virgula. Bine, apăsați F4 aici, virgulă 0 pentru o potrivire exactă la meci și apoi virgula cade pentru o potrivire exactă la VLOOKUP. Da și hei, acest lucru funcționează excelent și aici am doar șase dintre ele, așa că nu este mare lucru.în acest tabel. F4 și apoi, în loc să codeze hard numărul patru, fac un MATCH și MATCH-ul va fi blocat la preț. Deci F4, de două ori pentru a pune $ înainte de 1 și va fi căutat prin primul rând al mesei. Hopa, F4 de două ori, virgulă, a ratat virgula. În regulă, apăsați F4 aici, virgulă 0 pentru o potrivire exactă la meci și apoi virgula cade pentru o potrivire exactă la VLOOKUP. Da și hei, acest lucru funcționează excelent și aici am doar șase dintre ele, așa că nu este mare lucru.în acest tabel. F4 și apoi, în loc de a codifica cu greu numărul patru, fac un MATCH și MATCH-ul va fi blocat la preț. Deci, F4, de două ori pentru a pune $ înainte de 1 și va căuta prin primul rând al mesei. Hopa, F4 de două ori, virgulă, a ratat virgula. Bine, apăsați F4 aici, virgulă 0 pentru o potrivire exactă la meci și apoi virgula cade pentru o potrivire exactă la VLOOKUP. Da și hei, acest lucru funcționează excelent și aici am doar șase dintre ele, așa că nu este mare lucru.Bine, apăsați F4 aici, virgulă 0 pentru o potrivire exactă la meci și apoi virgula cade pentru o potrivire exactă la VLOOKUP. Da și hei, acest lucru funcționează excelent și aici am doar șase dintre ele, așa că nu este mare lucru.Bine, apăsați F4 aici, virgulă 0 pentru o potrivire exactă la meci și apoi virgula cade pentru o potrivire exactă la VLOOKUP. Da și hei, acest lucru funcționează excelent și aici am doar șase dintre ele, așa că nu este mare lucru.
Vedeți dacă introduc unul nou, acesta se va regla automat și va continua să obțină prețul, dar imaginați-vă dacă ați avut o mie de VLOOKUP-uri și fiecare VLOOKUP va reface acel meci pentru a afla că prețurile din a cincea coloană sau a patra coloană. E oribil. Tabelele rezolvă pur și simplu această problemă. Deci, iată tabelul meu VLOOKUP, fie cu mult înainte să fac ceva, voi merge aici și CTRL T pentru a-l transforma într-un tabel real. Vor numi tabelul 1, dar îl voi numi ProductTable, toate un singur cuvânt, fără spații: ProductTable. Deci, acum are un nume. Bine, așa că acum avem un tabel numit ProductTable. Apoi venim aici și spunem că vom face = INDEX al acestor prețuri. Ce preț ne dorim? Vrem rezultatul din meciul A104 în aceste articole. Potrivire exactă, închideți parantezele pentru INDEX.Aceasta face doar un singur meci. Nu face un meci și o VLOOKUP. Un fel de, va fi mult, mult mai rapid. Copiați-l. Bine și apoi mai târziu, dacă introducem dimensiunea, așa că introduceți coloana, dimensiunea totul continuă să funcționeze, deoarece caută coloana numită Preț și să spunem că, dacă schimbăm acest lucru în Preț de listă, acea formulă va fi rescrisă. Corect, mult, mult mai sigur, mai sigur.
Bine, atâtea trucuri grozave în tabele. Consultați această carte de la Kevin Jones și Zach Barresse pe Excel Tables. Tot felul de trucuri acolo și tot ceea ce facem podcasturi în august și septembrie se află în această carte plină de blocaje. Plus multă distracție. Glume Excel. Cocktail-uri Excel. Tweeturi Excel. Aventuri Excel. Ambalat în culori. Verifică, cumpără această carte. Aș aprecia cu adevărat.
Bine episodul de astăzi. VLOOKUP este minunat și este funcția mea preferată, dar există urâtori VLOOKUP care se plâng că este fragil din cauza acelui al treilea argument, dacă forma tabelului tău VLOOKUP se schimbă, răspunsurile se vor schimba. O soluție este înlocuirea acelui al treilea argument cu un MATCH, dar jeez, imaginați-vă să faceți un MATCH pentru o mie de rânduri de VLOOKUP. Deci, transformați VLOOKUP într-un tabel înainte de a face VLOOKUP. Referințele la tabelul de structură se vor gestiona dacă se modifică forma tabelului. În plus, nu faci un VLOOKUP și un meci. Doar o singură potrivire împreună cu un INDEX și INDEX reprezintă fulger, fulger rapid.
Mulțumim lui Peter Robert pentru acest sfat și vă mulțumim că ați trecut pe aici. Ne vedem data viitoare, pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2003.xlsx
