Visi noslēpumi Excel funkciju CDF (VLOOKUP), lai meklētu datu galda un ielādēt tos uz citu

Batyanov Denis par tiesībām viesu autors izskaidro šo ziņu par to, kā atrast datus vienā Excel izklājlapas un iegūt tos uz citu, un atklāj visus noslēpumus vertikālajā skata funkciju.

Strādājot izcelties Ļoti bieži ir nepieciešams, lai atrastu datus tabulā, un noņemt tos uz citu. Ja jūs nezināt, kā to darīt, tad es izlasīju rakstu, jūs ne tikai uzzināt, bet arī uzzināt, ar kādiem nosacījumiem jūs varēsiet izspiest maksimālu sistēmas veiktspēju. Uzskata par visaugstāk efektīvas metodes, kas jāizmanto kopā ar funkciju CDF.

Pat ja jums ir gadu lietošanas, CDF funkciju, ar lielu varbūtību šis raksts būs noderīga, lai jūs, un neatstās vienaldzīgu. Es, piemēram, kā IT speciālists, un tad līderis IT, izmanto VLOOKUP 15 gadus, bet Lai risinātu ar visām niansēm notika tikai tagad, kad es esmu profesionālā kārtā man sāka mācīt Excel cilvēki.

CDF - saīsinājums uzVertikālā, ucpārbaude. Tāpat VLOOKUP - Vertikālā LOOKUP. Ļoti nosaukums funkcija norāda mums, ka tas meklē rindas no tabulas (vertikālā - šķirošanas līnijas un nosaka asi), un nevis slejās (horizontālais - šķirošana kolonnas un fiksācijas līnija). Jāatzīmē, ka CDF ir māsa - neglīts pīlēns, kas nekad kļūt gulbis - ir funkcija AAR (HLOOKUP). AAR, pretstatā CDF, rada horizontālu meklēšanu, bet Excel koncepciju (un arī jēdziens organizēt datu) nozīmē, ka jūsu tabulas ir neliels kolonnu skaits un daudz ko citu līnijas. Tieši tāpēc meklēt virknes, mums daudzas reizes vairāk nekā kolonnām. Ja jūs

izcelties pārāk bieži izmanto HLOOKUP, tas ir diezgan iespējams, ka tur ir kaut kas jūs nesaprotat šajā dzīvē.

sintakse

VLOOKUP ir četri parametri:

PPS = ( ; ; [] ), Tad:

- vēlamā vērtība (reti) vai atsauce uz šūnu, kas ietver vēlamo vērtību (vairumā gadījumu);

(!) - atsauce uz šūnu diapazonu (divdimensiju masīvs), kas atrodas pirmajā kolonnā tiks meklēti parametra vērtību ;

- kolonnas numurs diapazonā, kura tiks atgriezta;

- Tas ir ļoti svarīgs parametrs, kas atbild uz jautājumu, kā tad, ja augošā pirmo aili diapazona ir sakārtots . Ja masīvs ir sakārtots, mēs norādīt pareizi (true) vai 1, pretējā gadījumā - viltus (viltus) vai 0. Ja jūs izlaist šo parametru, pēc noklusējuma, tas ir vienāds ar 1.

Varu derēt, ka daudzi no tiem, kas zina CDF funkciju kā pīlings, izlasiet aprakstu ceturtā parametra var justies neērti, jo viņi ko izmanto, lai aplūkotu to nedaudz savādāk: parasti ir runa par precīzu saskaņā ar meklēšanu (nepatiesu vai 0), vai arī par to pašu skata diapazons (TRUE vai 1).

Tagad tas ir nepieciešams, lai pastiprinātu un lasīt nākamo punktu dažas reizes, līdz jūs saņemsiet sajūta, lai izpratnē par to, kas ir pateikts līdz galam. Ir svarīgi, lai katru vārdu. Piemēri palīdzēs saprast.

Kā to pašu formulu, CDF darbojas īpaši

• Type of formula I. Ja pēdējais parametrs ir izlaists vai noteikta kā 1, tad CDF sagaida, ka pirmā kolonna ir sakārtoti augošā secībā, tāpēc Meklēšana apstāsies pie līnijas, kas tieši pirms rindu, kurā vērtība ir lielāka nekā vēlamais. Ja šī līnija nav atrasts, tas atgriež pēdējo rindu diapazonā.
  
• Type of formula II. Ja tā opcija ir norādīta kā 0, tad CDF secīgi skenē pirmo aili masīva un nekavējoties apstājas meklēt, kad pirmo reizi konstatēts precīzo atbilstību parametra , pretējā gadījumā kļūdas kods tiek atgriezta # N / A (# N / A).
  

formulas shēmas

CDF Type I

CDF Type II

Ietekme uz sugām formulu I

1. Formulas var izmantot diapazona vērtību izplatīšanu.
2. Ja pirmā kolonna ir dublikātus un sakārtoti pareizi, tas tiks atgriezts pēdējo rindu ar divkāršo vērtību.
3. Ja mēs skatāmies uz vērtību, protams, vairāk, nekā jūs varat ietvert pirmo aili, jūs varat viegli atrast pēdējo rindu tabulā, kas var būt diezgan vērtīga.
4. Šis skats atgriezīsies kļūdas # N / A tikai tad, ja tā konstatē, vērtība ir mazāks par vai vienāds ar vēlamo.
5. Saprast, ka formula atgriež nepareizu vērtību, ja jūsu masīvs netiek šķiroti, diezgan grūti.

Ietekme uz sugām formulu II

Ja vērtība ir atrodama pirmajā slejā masīva vairākas reizes, formula izvēlēsies pirmo rindu vēlāk atgūt.

Produktivitāte VLOOKUP

Jūs esat sasniedzis kulmināciju vietā rakstu. Šķiet, arī, kāda ir atšķirība, ja man norādīt kā pēdējais parametrs nulle vai viens? Būtībā, viss punkts, protams, nulles, jo tas ir diezgan praktisks: nav jāuztraucas par šķirošanu pirmo aili masīva, var redzēt, atrada vērtību vai ne. Bet, ja jums ir saraksts ar vairākiem tūkstošiem formulas CDF (VLOOKUP), jūs ievērosiet, ka CDF II tipa iet lēni. Tas parasti viss sāk domāt:

• Man vajag vairāk jaudīgu datoru;
• Man vajag ātri formula, piemēram, daudzi cilvēki zina par INDEX + MATCH (INDEX + MATCH), kas ir it kā ātrāk nožēlojamu 5-10%.

Un daži cilvēki domā, ka, tiklīdz sākat izmantojot CDF tipa un sniegt jebkādu veids, kā sakārtot pirmo kolonnu kā ātrumu CDF operācijas palielināsies 57 reizes. Es rakstīt vārdus - piecdesmit septiņas reizes! 57% un 5700%. Šis fakts Es pārbaudīju diezgan ticami.

Noslēpums Šādu ātru darba slēpjas faktā, ka jūs varat pieteikties uz šķirotas masīvs ir ļoti efektīvs meklēšanas algoritmu, kas ir pazīstams kā bināro meklēšanu (sadalīšana divās daļās metodi, dihotomija). Tāpēc es veida CDF to izmanto, un CDF II tipa cenšas bez optimizēšanu. Tas pats attiecas arī uz spēles (atbilstība), kas ietver to pašu parametru, un arī uz funkciju Vērojot (LOOKUP), kas darbojas tikai atlasītām masīvu un aktivizēts Excel saderību ar Lotus 1-2-3.

trūkumi formula

Trūkumi CDF ir acīmredzama: pirmkārt, tā lūdz tikai pirmo aili šī masīva, un, otrkārt, tikai pa labi no šīs slejas. Un, kā jūs zināt, tas var būt, ka kolonna, kas satur nepieciešamo informāciju pa kreisi no kolonnas, kurā mēs meklējam. Neko šo trūkumu jau minēja ķekars formulas INDEX + MATCH (INDEX + MATCH), kas padara to ļoti elastīgu risinājumu, lai iegūtu datus no tabulām, salīdzinot ar CDF (VLOOKUP).

Daži aspekti piemērojot formulu, kas reālajā dzīvē

Range meklēšana

Klasisks piemērs no meklēšanas diapazonā - problēma lieluma noteikšanu pasūtījuma atlaides.

Meklēt teksta virknes

Protams, CDF cenšas ne tikai ciparus, bet arī tekstu. Jāpatur prātā, ka reģistrjutīgi formula netiek izdalīts. Ja jūs izmantojat aizstājējzīmes, ir iespējams organizēt izplūdušo meklēšanu. Ir divi aizstājējzīmes "?" - aizstāj jebkuru vienu rakstzīmi teksta virkne "*" - aizstāj jebkuru skaitu jebkuru rakstzīmes.

Cīņa ar atstarpēm

Bieži tiek uzdots jautājums, kā atrisināt šo problēmu, papildu telpu meklēšanā. Ja meklēt-up tabulu joprojām ir iespējams tīrīt tos, pirmais parametrs formulu CDF ne vienmēr ir atkarīgs no jums. Tāpēc, ja risks aizsprostošanos šūnām papildus telpa ir klāt, ir iespējams izmantot TRIM (TRIM) funkciju tīrīšanai.

Atšķirīgs datu formāts

Ja pirmais parametrs funkcija CDF attiecas uz šūnu, kas satur skaitli, bet kas tiek glabāta šūnas kā tekstu, un pirmā sleja masīva satur ciparus pareizā formātā, meklēšana tiks neveiksmīgs. Pretēja situācija. Problēma ir viegli atrisināt, pārsūtīšanas parametra 1 uz vajadzīgajā formātā:

PPS = (- D7; Produkti $ A $ 2: $ C $ 5;! 3; 0), - ja D7 satur tekstu un tabulu - numuru;

= CDF (D7 un «»); Produkti $ A $ 2: $ C $ 5;! 3; 0) - un otrādi.

Starp citu, lai tulkot tekstu numuru, var būt vairāki veidi, izvēlieties:

• Double Negatīvs -D7.
• Multiplication vienība D7 * 1.
• Papildinājums ar nulles D7 + 0.
• No pirmās pakāpes D7 ^ 1 konstrukcija.

Tulkojums no teksta, ko saslēgšana ar tukšu virkni, kas izraisa Excel pārvērst datu tipu.

Kā apspiest izsniegšanas # N / A

Tas ir ļoti ērti darīt ar IFERROR funkciju (IFERROR).

Piemēram: = IFERROR (CDF (D7; Produkti $ A $ 2: $ C $ 5;! 3; 0); «»).

Ja CDF atgriezīs kļūdas kodu # N / A, tad tas būs pārtvert IFERROR aizstājēju un parametru 2 (šajā gadījumā, tukša virkne), un Ja nav kļūdas, šī funkcija tiks izlikties, ka tā nav vispār, bet tikai CDF, atjaunot normālu rezultāts.

Array

Bieži aizmirst tiek veikts absolūtā masīvs atsauces, un tad, kad velkot masīvs "pludiņiem". Atcerieties, ka tā vietā, A2: C5 jāizmanto $ A $ 2: $ C $ 5.

Laba ideja ir ievietot atsauces masīva uz atsevišķas lapas darbgrāmatā. Tas nav get zem kājām, un tas ir saglabāts.

Vēl laba ideja paziņojumu masīva kā nosaukts diapazonā.

Daudzi lietotāji tiek izmantoti, kad norādot struktūru A masīva tips: C, norādot kolonnas pilnībā. Šī pieeja ir tiesības pastāvēt, jo jūs saudzējuši no tā, lai sekotu tam, ka jūsu masīvs satur visas virknes. Ja jūs pievienot līniju uz kādu sākotnējo masīvs, diapazons norādīts kā A: C, nebūs pielāgot. Protams, šis sintakse izraisa Excel pavadīt mazliet vairāk darba, nekā ar precīzu norādi uz diapazona, bet dati gaisvadu ir niecīgs. Mēs runājam par sekundes simtdaļas.

Nu, par draudēja ģēnijs - uz jautājuma formā masīva smart tabula.

Izmantojot kolonnu funkciju, norādot ekstrakcijas kolonnā

Ja tabula, kurā jums iegūt datus, izmantojot CDF, ir tāda pati struktūra kā meklēt-up tabulu, bet tikai ietver minimālu skaitu rindu WRT jomā var izmantot kolonnu () funkciju, lai automātiski aprēķinātu skaitu atlīdzināmi kolonnas. Šādā gadījumā visi CDF-formula būs vienādi (koriģēts pirmo parametru, kas automātiski maina)! Ņemiet vērā, ka pirmais parametrs ir absolūtais koordinātu kolonnas.

Radot saliktu atslēga ar & »|» &

Ja ir nepieciešams apskatīt vairāk nekā vienā kolonnā laikā, tas ir nepieciešams, lai saliktu atslēgu meklēšanu. Ja atgrieztā vērtība nav teksts (kā šeit, ja laukā "kods"), un ciparu, lai Tas nāca ar ērtu formulas SUMIFS (SUMIFS) un neatņemamu atslēgas kolonnas nebūtu vajadzīgs vispār.

Šis ir mans pirmais raksts par Layfhakera. Ja jums patika, tad es aicinu jūs apmeklēt mana vietneUn labprāt lasītu komentārus par noslēpumiem, izmantojot VLOOKUP un tamlīdzīgi. Paldies. :)