Sve tajne Excel funkcija CDF (VLOOKUP) tražiti podatke u tablici i preuzimati ih na drugu
Savjet Tehnologije / / December 19, 2019
Batyanov Denis o pravima gost autor objašnjava u ovom postu o tome kako pronaći podatke u jedan Excel spreadsheet-ovima i izdvojiti ih na drugo, i otkriva sve tajne vertikalnom funkciju gledanja.
Kada radite u nadmašiti Vrlo često postoji potreba za pronalaženje podataka u tablici i ukloniti ih u drugu. Ako ne znate kako to učiniti, a onda sam pročitao članak, ne samo ćete ga naučiti, ali i saznati pod kojim uvjetima moći ćete istisnuti maksimalne performanse sustava. Smatra većina visoko učinkovite tehnike koje treba koristiti u kombinaciji s funkcijom CDF.
Čak i ako imate godina korištenja funkcije CDF, s velikom vjerojatnošću ovaj članak će biti korisne za vas i neće ostaviti ravnodušnim. Ja, na primjer, kao što je IT-stručnjak, a zatim lider u IT, koristi VLOOKUP 15 godina, ali kako se nositi sa svim nijansama dogodilo baš sada, kad sam na profesionalnoj osnovi sam počela učiti Excel ljudi.
CDF - skraćenica od uvertikalni, itdinspekcija. Slično, VLOOKUP - vertikalni pretraživanja. Sam naziv funkcije ukazuje nam da pretražuje redovi stola (vertikalna - sortiranje linija i pričvršćivanje os), a ne u stupcima (horizontalno - sortiranje stupce i fiksiranje linija). Treba napomenuti da je CDF ima sestru - Ružno pače koje nikada neće postati labud - je funkcija Pgr (HLOOKUP). PGR, za razliku od CDF, stvara horizontalni pretraživanje, ali koncept Excel (a zapravo je koncept organiziranje podataka) znači da su vaši stolovi imaju mali broj stupaca i još mnogo toga linije. To je razlog zašto je potraga za gudače, moramo mnogo puta više od stupova. Ako vas
nadmašiti prečesto koriste HLOOKUP, vrlo je vjerojatno da postoji nešto što ne razumiješ u ovom životu.sintaksa
VLOOKUP ima četiri parametra:
PPS = ( 'ŠTO>; 'Kako>; 'Broj_stupca> [
- željenu vrijednost (rijetko) ili referenca na ćeliju sadrži željenu vrijednost (većini slučajeva);
Kladim sam da su mnogi od onih koji znaju CDF funkciju kao peeling, pročitajte opis četvrtog parametra može se osjećati neugodno jer oni koristi ga vidjeti na malo drugačiji način: obično je govor o točnom skladu s traženju (netočno ili 0) ili o istom rasponu gledanja (TRUE ili 1).
Sada je potrebno zategnuti i čitati sljedećem paragrafu nekoliko puta dok ne dobijete osjećaj za smisao onoga što je rečeno do kraja. Tu je važno svaku riječ. Primjeri će vam pomoći razumjeti.
Kao isto formule CDF radi naročito
- Tip formule I. Ako je zadnji parametar je izostavljen ili naveden kao 1, CDF očekuje da prvi stupac se razvrstava uzlaznim redoslijedom, tako da je pretraživanje će se zaustaviti na liniji tog neposredno prethodi red u kojem je vrijednost veća od željene. Ako se taj redak nije pronađen, vraća zadnji red raspona.
- Tip formule II. Ako je posljednja opcija naveden kao 0, tada je CDF sekvencijalno skenira prvu kolonu polja i odmah zaustavlja Traženje kada je prvi pronašli točno podudaranje s parametrom , inače kod pogreške vraća # N / A (# N / A).
formulama sheme
CDF tipa I
CDF tipa II
Posljedice za vrste formule
- Formule se mogu koristiti za distribuciju raspona vrijednosti.
- Ako je prvi stupac
sadrži duplikate i razvrstani ispravno, to će biti vraćen posljednji redova s dvostrukim vrijednostima. - Ako gledamo u vrijednosti očito više nego što može sadržavati prvi stupac, možete lako pronaći zadnji red stola, što može biti vrlo vrijedna.
- Ovaj pogled će vratiti pogreške # N / A tek kad se utvrdi vrijednost manja od ili jednaka do željenog.
- Shvatite da je formula vraća netočne vrijednosti, ako niz nije sortiran, vrlo teško.
Posljedice za vrste formule II
Ako je vrijednost nalazi u prvom stupcu nekoliko puta array, formula će odabrati prvi red za kasnije pretraživanje.
produktivnost VLOOKUP
Ste dosegnuli vrhunac u mjestu članka. Čini se, dobro, u čemu je razlika ako ja istaknuti kao zadnji parametar nula ili jedan? Uglavnom, sve je točka, naravno, nula, kao što je vrlo praktično: ne morate brinuti o razvrstavanje prvi stupac polja, može se vidjeti, našli vrijednost ili ne. Ali, ako imate popis od nekoliko tisuća formulama CDF (VLOOKUP), primijetit ćete da je tip CDF II teče polako. Obično se svi početi razmišljati:
- Trebam snažnije računalo;
- Trebam brzo formulu, na primjer, mnogi ljudi ne znaju o INDEX + MATCH (INDEX + MATCH), koji je navodno brži bijedan 5-10%.
A malo ljudi misle da čim početi koristiti CDF tip I i pružiti bilo koji način sortiranja prvi stupac kao brzina rada CDF će se povećati 57 puta. Pišem riječi - u pedeset sedam puta! U 57% i 5700%. Ova činjenica Provjerio sam sasvim pouzdano.
Tajna takvim brzim radnim leži u činjenici da se može primijeniti na sortiranog niza izuzetno učinkovit algoritam za pretraživanje, koji je poznat kao metoda pretraživanje po binarnom (bisekcija, dihotomija). Tako sam nekako CDF ga koristi, a CDF tipa II traži bez optimizacije u cjelini. Isto se odnosi na utakmicu (MATCH), koji uključuje parametar iste, kao i za funkciju View (traženje), koji radi samo na razvrstanih polja i aktivirana u Excelu za kompatibilnost s Lotus 1-2-3.
nedostaci formula
Nedostaci u CDF su očite: prvo, to traži samo prvi stup tog polja, a kao drugo, samo na desnoj ovom stupcu. I kao što znate, može se očekivati da je kolona koja sadrži sve potrebne informacije na lijevo od stupca u kojem tražimo. Bez ovog nedostatka je već spomenuto hrpa formula INDEX + MATCH (INDEX + MATCH), što je najviše fleksibilno rješenje za izdvajanje podataka iz tablice u odnosu na CDF (VLOOKUP) čini.
Neki aspekti primjene formule u stvarnom životu
traženje raspona
Klasična ilustracija rasponu pretraživanje - problem određivanja veličine Reda popuste.
Traži tekstualni nizovi
Naravno, CDF traži ne samo brojeve, ali i tekst. Treba imati na umu da je velika i mala slova formula ne razlikuje. Ako koristite zamjenske znakove, moguće je organizirati fuzzy pretraživanja. Postoje dvije zamjenski „?” - zamjenjuje bilo koji znak u tekstnom nizu „*” - zamjenjuje bilo koji broj ikakvih znakova.
Borba s prostorima
Često se postavlja se pitanje, kako riješiti problem dodatnih prostora u potrazi. Ako se pregledna tablica je još uvijek moguće da ih očistiti, prvi parametar formule CDF ne uvijek ovisi o vama. Stoga, ako je prisutan rizik od začepljenja stanice dodatni prostor, moguće je koristiti TRIM (trim) funkciju za čišćenje.
Drugačiji format podataka
Ako je prvi parametar funkcije CDF odnosi se na stanicu koja sadrži broj, ali koja je pohranjena u stanica kao tekst, a prvi stupac polja sadrži brojeve u ispravnom formatu, pretraživanje će biti neuspješan. Obrnuta situacija. Problem je lako riješiti parametar prijenosa 1 u traženom formatu:
PPS = (- D7; Proizvodi $ A $ 2: $ C $ 5;! 3; 0) - ako D7 sadrži tekst i tablice - brojeve;
= CDF (D7 i «»); Proizvodi $ A $ 2: $ C $ 5;! 3; 0) -, i obrnuto.
Usput, prevesti tekst u broju može biti nekoliko načina odaberite:
- Dvostruka Negativna -D7.
- Množenje jedinica D7 * 1.
- Dodavanje s nula D7 + 0.
- Izgradnja prvog stupnja, D7 ^ 1.
Prijevod teksta proizveden spajanjem s nekim prazan niz, što uzrokuje Excel pretvoriti tip podataka.
Kako suzbiti izdavanje # N / A
To je vrlo povoljno za napraviti sa IFERROR funkcije (IFERROR).
Na primjer: = IFERROR (CDF (D7; Proizvodi $ A $ 2: $ C $ 5;! 3; 0); «»).
Ako će CDF vratiti kod pogreške # N / A, onda će presresti IFERROR zamjena i parametar 2 (u ovom slučaju, prazan string), i Ako nema pogrešaka, ova funkcija će se pretvarati da to uopće nije, nego samo CDF, vraćena u normalu rezultat.
Array
Često se zanemaruje niz upućuje se apsolutno, a kada povlačenjem niz „lebdi”. Sjetite se da umjesto A2: C5 treba koristiti 2 $ A $: $ C 5 $.
Dobra ideja je da se referentni raspon na posebnom listu u radnoj knjizi. To ne bi nogama, te će se pohraniti.
Još dobro da objave polja kao imenovanog raspona.
Mnogi korisnici koriste prilikom određivanja strukture tipa niz A: C, što ukazuje na stupce u cijelosti. Ovaj pristup ima pravo na postojanje, budući da su pošteđeni od toga da pratimo na činjenicu da je vaš red sadrži sve konce. Ako dodate liniju na komad izvornog polja, raspon naveden kao A: C, neće morati prilagoditi. Naravno, ova sintaksa uzrokuje Excel potrošiti malo više posla nego s točnim navođenje područja, ali iznad glave podataka je zanemariv. Radi se o stotinki sekunde.
Pa, na rubu genijalnosti - na pitanje u obliku niza pametna tablica.
Korištenje stupac funkciju za određivanje kolonu za ekstrakciju
Ako je tablica u kojoj vam izvuci podatke pomoću CDF, ima istu strukturu kao i pregledne tablice, ali samo uključuje minimalni broj redova u wrt domeni može se koristiti kolona () funkciju za automatsko izračunavanje broja su povratno stupovi. U ovom slučaju sve CDF-formula će biti isti (prilagođen za prvi parametar, koji automatski mijenja)! Imajte na umu da je prvi parametar je apsolutna koordinata stupca.
Stvaranje kompozitni ključa & »|» i
Ako postoji potreba za pogledati više od jednog stupca u isto vrijeme, potrebno je napraviti kompozitni ključ za pretraživanje. Ako se vrati vrijednost nije tekst (kao i ovdje u slučaju polje „Code”), a numerička, za Tome je prikladan formula SUMIFS (SUMIFS) i sastavni stupcu ključnih neće biti potrebna na sve.
Ovo je moj prvi članak za Layfhakera. Ako ti se svidjelo, pa vas pozivam da posjetite moj siteI rado pročitati vaše komentare o tajnama pomoću VLOOKUP i slično. Hvala Vam. :)