Kaikki salaisuudet Excel-toiminto CDF (PHAKU) ja etsi taulukon tietoja ja hakea ne toiseen

Batyanov Denis oikeuksista vieras kirjailija selittää tässä viestissä siitä, miten löytää tietoja johonkin Excel ja purkaa ne toiseen, ja huomaa kaikki salaisuudet pystysuoran katselu toiminto.

Työskenneltäessä kunnostautua Hyvin usein on tarve löytää taulukon tietoja ja poistaa ne toiseen. Jos et tiedä miten se tehdään, sitten luin artikkelin, sinun ei vain oppia se, mutta myös selvittää millä edellytyksillä voit puristaa ulos mahdollisimman järjestelmän suorituskykyä. Pidetään kaikkein erittäin tehokas tekniikoita, joita tulisi käyttää yhdessä toiminta CDF.

Vaikka sinulla on vuosien käytön CDF-toiminto, suurella todennäköisyydellä tämä artikkeli on hyötyä sinulle ja ei jätä kylmäksi. I, esimerkiksi IT-asiantuntija, ja sitten johtava IT, käytetty PHAKU 15 vuotta, mutta käsitellä kaikkia vivahteita tapahtui juuri nyt, kun olen ammattimaisesti aloin opettaa Excel ihmisiä.

CDF - lyhenne sanoista sisäänpystysuora, jne.tarkastus. Vastaavasti, VLOOKUP - Vertical HAKU. Hyvin funktion nimi ehdottaa meille, että se etsii taulukon rivien (vertikaalinen - lajittelu linja ja vahvistamisesta akseli), eikä sarakkeita (horisontaalinen - lajittelu sarakkeet ja vahvistamisesta linja). On huomattava, että CDF on sisko - ruma ankanpoikanen, joka ei koskaan tule joutsen - on funktio PGR (VHAKU). PGR, toisin kuin CDF, tuottaa vaakasuora haku, mutta Excel konseptin (ja jopa käsite järjestämällä data) tarkoittaa, että taulukot on pieni määrä sarakkeita ja paljon muuta linjat. Siksi etsiä jouset, tarvitsemme monta kertaa enemmän kuin sarakkeita. Jos

kunnostautua liian usein VHAKU, on varsin todennäköistä, että siellä on jotain et ymmärrä tässä elämässä.

syntaksi

VLOOKUP on neljä parametrit:

PPS = ( ; ; [] ), Niin:

- haluttu arvo (harvinainen) tai viittaus soluun, joka sisältää halutun arvon (suurin osa tapauksista);

(!) - viittaus solualue (kaksiulotteinen matriisi), joka on ensimmäisessä sarakkeessa etsitään parametrin arvo ;

- sarakkeen numero on alueella, joka palautetaan;

- Tämä on erittäin tärkeä tekijä, joka vastaa kysymykseen, koska jos nouseva ensimmäisessä sarakkeessa välillä lajitellaan . Jos matriisi on järjestetty, me osoittavat, TRUE (TOSI) tai 1, muuten - EPÄTOSI (FALSE), tai 0. Jos jätät tämän parametrin, oletuksena, se on yhtä kuin 1.

Lyön vetoa, että monet niistä, jotka osaavat hilseilevä CDF-toiminto, lukemaan neljännen parametrin voi tuntua epämukavalta, koska ne käytetään nähdä sitä hieman eri tavalla: yleensä on puheen tarkkaa mukaisesti haku (EPÄTOSI tai 0), tai suunnilleen sama katselu alue (TOSI tai 1).

Nyt on tarpeen tiukentaa ja lukea seuraavan kappaleen muutaman kerran kunnes saat tuntuman merkitys, mitä on sanottu loppuun asti. On tärkeää jokaista sanaa. Esimerkit auttaa ymmärtämään.

Koska sama kaava CDF toimii erityisen

• Tyyppi, jolla on kaava I Jos viimeinen parametri on jätetty pois tai määritetty 1, CDF olettaa, että ensimmäinen sarake on järjestetty nousevaan järjestykseen, niin haku pysähtyy linjaa edeltää välittömästi rivi, jossa arvo on suurempi kuin haluttu. Jos tämä linja ei löydy, se palauttaa viimeisen rivin välillä.
  
• Tyypin, jolla on kaava II. Jos jälkimmäinen vaihtoehto on määritetty 0, sitten CDF käy peräkkäin läpi ensimmäisessä sarakkeessa array ja pysähtyy heti vaikka sellaista ensin löysi tarkka ottelu parametri muutoin virhekoodi palautetaan # N / A (# N / A).
  

kaavat järjestelmän

CDF tyyppi I

CDF Type II

Vaikutukset lajeja, joilla on kaava I

1. Kaavat voidaan käyttää jakauman arvojen.
2. Jos ensimmäinen sarake sisältää kaksoiskappaleet ja lajitellaan kunnolla, se palautetaan viimeinen krs päällekkäisiä arvoja.
3. Jos me etsiä arvon ilmeisesti enemmän kuin voitte olla ensimmäisessä sarakkeessa, voit helposti selvittää viimeisen rivin taulukon, joka voi olla varsin arvokkaita.
4. Tämän näkemyksen palauttaa virheen # N / A vain silloin, kun se löytää arvo on alle tai yhtä kuin haluttu.
5. Ymmärtää, että kaava palauttaa virheellisen arvon, jos joukko ei lajitella, melko vaikeaa.

Vaikutukset lajien kaavan II

Jos arvo löytyy ensimmäisessä sarakkeessa array useita kertoja, jolla on kaava valitsee ensimmäisen rivin myöhemminkin.

tuottavuus VLOOKUP

Olet saavuttanut huippunsa paikka artikkelin. Tuntuisi, no, mitä eroa, jos huomautan viimeisenä parametri nolla tai yksi? Periaatteessa kaikki piste tietenkin nolla, koska se on varsin käytännöllinen: ei tarvitse pelätä lajittelu ensimmäisessä sarakkeessa array, voi nähdä, löydetty arvo vai ei. Mutta jos sinulla on luettelo useita tuhansia kaavojen CDF (PHAKU), huomaat, että CDF tyypin II kulkee hitaasti. Se on yleensä kaikki alkaa ajatella:

• Tarvitsen tehokkaampi tietokone;
• Tarvitsen nopeasti kaava, esimerkiksi monet ihmiset tietävät INDEX + VASTINE (INDEX + Match), joka on oletettavasti nopeampi kurja 5-10%.

Ja harvat ihmiset ajattelevat, että heti kun alkaa käyttää CDF tyypin I ja tarjoa mitään tapa lajitella ensimmäisen sarakkeen nopeus CDF toiminta kasvaa 57 kertaa. Kirjoitan Hakusanat - viisikymmentäseitsemän kertaa! 57%: n ja 5700%. Tämä seikka Tarkistin varsin luotettavasti.

Salaisuus tällaisen työn nopeasti piilee siinä, että voit hakea lajitellut jono on äärimmäisen tehokas hakualgoritmi, joka tunnetaan binäärinen haku (bisection menetelmä, kahtiajako). Joten olen sellainen CDF käyttää sitä, ja CDF tyypin II pyrkii ilman optimointia yleensä. Sama koskee MATCH (MATCH), joka sisältää samaa parametria, ja myös toiminto Tarkkailu (HAKU), joka toimii vain järjestyksessä olevien taulukoiden ja aktivoitujen Excel yhteensopivuuden Lotus 1-2-3.

haitat kaava

Haitat CDF ovat ilmeiset: yhtäältä se pyrkii vain ensimmäinen sarake tämän taulukon, ja toiseksi, juuri oikealla tämän sarakkeen. Ja kuten tiedätte, voi hyvinkin olla, että pylväs, joka sisältää tarvittavat tiedot vasemmalla sarake, pyrimme. Puuttuu tämä puute on jo mainittu joukko kaavojen INDEX + MATCH (INDEX + MATCH), mikä tekee siitä joustava ratkaisu poimia tietoja taulukoista verrattuna CDF (PHAKU).

Joitakin näkökohtia kaavaa tosielämässä

aikavälihaussa

Klassinen esimerkki ilmoittavat haun - ongelman määrittämiseen tilauksen koosta alennuksia.

Hakea tekstiä

Tietenkin CDF tarkoituksena on paitsi numeroita, mutta myös tekstiä. On syytä pitää mielessä, että isot ja pienet kirjaimet kaava ei tehdä. Jos käytät yleismerkkejä, on mahdollista järjestää sumea haku. On kaksi jokerimerkki "?" - korvaa yksittäisen merkin merkkijono "*" - korvaa minkä tahansa määrän tahansa merkkiä.

Taistella tilat

Usein kysymys tulee esille, miten ratkaista ongelma ylimääräiset välilyönnit hakuun. Jos hakutaulukko on vielä mahdollista puhdistaa ne, ensimmäinen parametri on kaava CDF ei aina riipu teistä. Näin ollen, jos riski tukkeutumisen solujen lisätilaa on läsnä, se on mahdollista käyttää TRIM (TRIM) toiminto puhdistusta.

Eri tietomuoto

Jos ensimmäisen parametrin funktio CDF viittaa soluun, joka sisältää numeron, mutta joka on tallennettu solun tekstinä, ja ensimmäisessä sarakkeessa array sisältää numerot oikeassa muodossa, haku on epäonnistunut. Päinvastaisessa tilanteessa. Ongelma ratkeaa helposti siirtää parametri 1 vaadittuun muotoon:

PPS = (- D7; Tuotteet $ 2 A $: $ C $ 5;! 3; 0) - jos D7 sisältää tekstin ja taulukon - numerot;

= CDF (D7 & «»); Tuotteet $ 2 A $: $ C $ 5;! 3; 0) - ja päinvastoin.

Muuten, kääntää tekstin määrä voi olla monella tavalla, valitse:

• Kaksoiskielto -D7.
• Kertomisyksikkö D7 * 1.
• Lisäksi nolla D7 + 0.
• Rakentamisen ensimmäisen asteen D7 ^ 1.

Tekstin kääntäminen on tuotettu kytkemällä tyhjän joka saa Excel muuntaa datan tyyppi.

Miten tukahduttaa liikkeeseen # N / A

Se on erittäin kätevä tehdä IFERROR toiminto (IFERROR).

Esimerkiksi: = IFERROR (CDF (D7; Tuotteet $ 2 A $: $ C $ 5;! 3; 0); «»).

Jos CDF palauttaa virhekoodin # N / A, sitten se sieppaa IFERROR korvike ja parametri 2 (tässä tapauksessa tyhjä merkkijono), ja Jos mitään virhettä tapahtunut, tämä toiminto teeskennellä, että se ei ole lainkaan, vaan ainoastaan ​​CDF, normalisoitunut tulos.

Array

Usein unohdetaan joukko viitataan ehdoton, ja kun vetämällä joukko "kelluu". Muista, että A2: n sijaan: C5 tulisi käyttää $ 2 A $: $ C $ 5.

Hyvä idea on sijoittaa viitematriisista erilliselle arkille työkirjan. Se ei saa allensa, ja se on tallennettu.

Vieläkin hyvä ilmoitus array kuin nimetty alue.

Monet käyttäjät käytetään, kun määritellään taulukontulostusyksikön rakenteen tyyppi A: C, mikä osoittaa, sarakkeet kokonaan. Tämä lähestymistapa on oikeus olla olemassa, koska olet säästynyt tarvitse seurata, että levyjärjestelmän sisältää kaikki jouset. Jos lisäät rivin pala alkuperäisen array, alue määritellään A: C, ei tarvitse säätää. Tietenkin tämä syntaksin saa Excelin viettää hieman enemmän työtä kuin tarkka osoitus alue, mutta tiedot yläpuolella on merkityksetön. Puhumme sekunnin sadasosia.

No, partaalla nero - myöntämään muodossa array Smart table.

Käyttäen sarake toiminto määritetään uuttokolonnin

Jos taulukko, johon purat tiedot käyttäen CDF, on sama rakenne kuin hakutaulukkoa, mutta vain käsittää minimaalinen määrä rivien WRT domeeni voidaan käyttää sarake () funktio laskien automaattisesti numerot hyödynnettävissä sarakkeita. Tässä tapauksessa kaikki CDF-kaava on sama (säädetään ensimmäistä parametria, joka muuttaa automaattisesti)! Huomaa, että ensimmäinen parametri on absoluuttisessa koordinaatistossa sarakkeeseen.

Luominen komposiitti avaimen ja »|» &

Jos on tarpeen tarkastella enemmän kuin yksi sarake kerrallaan, on tarpeen tehdä komposiitti avain hakusanalla. Jos palautettu arvo ei ole tekstiä (kuten tässä tapauksessa kenttään "Code"), ja numeerinen, että Tämä tuli kätevä kaavan SUMIFS (SUMIFS) ja kiinteä avain sarake ei vaadita ollenkaan.

Tämä on ensimmäinen artikkeli Layfhakera. Jos pidit siitä, niin minä teitä vierailemaan sivustoniJa mielellään lukea kommentteja salaisuuksia käyttäen VLOOKUP- ja vastaavat. Kiitos. :)