VLOOKUP excel - A kezdők Guide szintaktikai és példák
Ma kezdődik a cikksorozat leírja az egyik leghasznosabb tulajdonsága az Excel - CDF (FKERES). Ez a funkció, ugyanabban az időben, az egyik a legösszetettebb és legkevésbé érthető.
Ebben a leírásban megpróbálom a CDF bemutatni az alapjait a legegyszerűbb nyelv, hogy a tanulási folyamat a tapasztalatlan felhasználók a lehető legvilágosabban. Ezen felül fogjuk vizsgálni néhány példa az Excel képletek, amelyek bemutatják a leggyakoribb felhasználási a CDF funkciót.
FKERES Excel - általános leírást és a szintaxis
Tehát, mi az CDF. Nos, először is, ez egy függvény az Excel. Mit csinál? Ő keres egy adott értéket, és visszaadja a megfelelő értéket egy másik oszlopban. Technikailag szólva, keresse meg a CDF értékét az első oszlopban az előre meghatározott tartomány, és visszaadja az eredményt egy másik oszlopban az ugyanabban a sorban.
A szokásos alkalmazás VLOOKUP keresések az adatbázis egy egyedi azonosítót, és lekéri az adatbázisból néhány információt társítva.
Az első betű a neve a CDF funkció (FKERES) a függőleges, eszközt (V ertical). Rajta, meg tudja különböztetni a CDF a PGR (HLOOKUP), amely megkeresi az értéket a felső sorban a tartomány - D vízszintes (H ORIZONTÁLIS).
Syntax VLOOKUP
CDF (FKERES) függvény itt szintaxisa a következő:
Mint látható, a CDF funkció a Microsoft Excel egy 4 paramétert (vagy érvelés). Az első három - van szükség, az utolsó -, ha szükséges.
- keresési_érték (keresési_érték) - olyan érték, amelyet meg kell keresni.
Ha az érték kisebb, mint a legkisebb érték az első oszlopban a megtekintési tartomány, a CDF funkció hibaüzenetet # N / A (# H / W).
- tábla_tömb (táblázat) - két vagy több oszlopot az adatok.
Emlékezz CDF funkció mindig keresi az értéket az első oszlopban megadott tartományon az érv tábla_tömb (táblázat). A szkennelési tartomány különböző lehet az adatok, például szöveges, dátum, szám, logikai érték. karakterek nem érzékenyek a funkció, azaz a felső és kisbetűk tartják ugyanaz.
Tehát a képlet fog keresni az érték 40, a sejtek A2 A15. mivel az A - az első oszlop tartományban A2: B15, meghatározott argumentum tábla_tömb (táblázat):
- oszlopszám (oszlopszám) - oszlopának számát egy előre meghatározott tartományban, ahonnan kerül vissza az értéket a illeszkedő szöveget.
A bal szélső oszlopban az előre meghatározott tartományban - egy második oszlop 1. - 2. Ez a harmadik oszlop - 3, és így tovább. Most el tudja olvasni a teljes képlet:
Formula néz ki az értéket 40 tartományban A2: A15 és visszaadja a megfelelő értéket a B oszlopban (mert B - a második oszlop a tartományban A2: B15).
Ez a paraméter nem kötelező, de nagyon fontos. Később ez a bemutató a CDF, megmutatom neked néhány példát, hogy hogyan készül a képlet, hogy megtalálják a pontos és közelítő mérkőzést.
Példák működnek CDF
Remélem VLOOKUP lett egy kicsit világosabb az Ön számára. Most nézzük meg néhány példát a CDF képletek valós adatokkal.
Hogyan kell használni a CDF, végezzen keresést másik lapon az Excel
A gyakorlatban, a képlet a funkciója a CDF ritkán használják Adatkereséshez ugyanarra a lapra. Leggyakrabban, akkor keresni és letölteni a megfelelő értékeket a másik lapot.
Ahhoz, hogy a CDF. Keresés másik lapon a Microsoft Excel, akkor kell az érv tábla_tömb (táblázat), hogy meghatározza a lap neve felkiáltójellel, majd egy cellatartományt. Például, a következő képlet azt jelzi, hogy a tartomány A2: B15 van a lapon nevű Munka2.
Természetesen a nevét a lap nem feltétlenül kézzel kell bevinni. Kezdje el beírni a képletet, és amikor a tábla_tömb érv (lásd a táblázatot), váltson a kívánt oldalt, és húzza az egeret a kívánt cellatartományt.
A képlet képernyőképnek alábbi, megkeresi szöveg „termék 1” oszlopában (ez az első oszlop A2 tartomány: B9) az árakon lapon.
= FKERES (! "1. termék", árak $ A $ 2: $ B $ 9,2, HAMIS)
= CDF (! "1. termék" ára $ A $ 2: $ B $ 9, 2, HAMIS)
Ne feledje, hogy ha keres szöveges értékek csatolnia kell azt idézőjelbe ( „”), ahogy általában lenni szokott az Excel képleteket.
Mert tábla_tömb érv (a táblázatban), kívánatos, hogy mindig abszolút linkeket (a jel $). Ebben az esetben a keresési tartományt változatlan marad, ha a képletet másolni más sejteket.
Keresés másik munkafüzet használatával CDF
Ahhoz VLOOKUP dolgozott a két Excel munkafüzeteket, akkor meg kell adnia a munkafüzet nevét zárójelben neve előtt a lap.
Például, a képlet az alábbi, amely úgy néz ki, a értéke 40, a lapot regiszterben Sheet2 Numbers.xlsx:
Itt van egy egyszerű módja annak, hogy hozzon létre egy Excel képlet a CDF. amely utal egy másik munkafüzet:
- Nyissa fel a könyveket. Nem szükséges, de a könnyebb létrehozni egy formula. Nem akarjuk, hogy adja meg a munkafüzet nevét kézzel? Ezen kívül védi meg a véletlen elírás.
- Kezdje el beírni a funkció a CDF. de amikor érvek tábla_tömb (lásd a táblázatot), átvált egy másik munkafüzetet, és válassza ki a jobb keresési tartományt.
A képernyőkép alább látható, látható a képletet, amely a keresési tartomány meg egy munkafüzetet lap PriceList.xlsx az árak.
CDF akkor is működik, amikor bezárja a munkafüzetet, amelyben a keresést, és a képlet sáv lesz a teljes elérési utat a munkafüzetet fájlt az alábbiak szerint:
Ha a név a munkafüzet vagy munkalap terek, akkor helyezze azt idézőjelbe:
Hogyan kell használni a névvel ellátott tartomány vagy táblázatot képletek a CDF
Ha azt tervezi, hogy egy sor keresési funkciók a CDF. akkor létrehozhat egy elnevezett tartományt, és adja meg a nevét a képlet érvként tábla_tömb (lásd a táblázatot).
Ahhoz, hogy hozzon létre egy elnevezett tartományt, csak válassza ki a cellát, és adja meg a megfelelő nevet a Név mezőben. A bal oldalon a képlet bar.
Most akkor írj ide egy képletet az áru ára Product Search 1:
= FKERES ( "termék 1", Termékek 2)
PPS = ( "termék 1"; Products; 2)A legtöbb nevek dolgoznak tartományok teljes Excel munkafüzetet, így nincs szükség adja meg a nevét a lap a tábla_tömb érv (lásd a táblázatot), még akkor is, ha a képletet, és a keresési tartományt külön oldalon a könyv. Ha vannak különböző könyvek, meg kell adnia a nevét, a munkafüzet neve előtt tartományban, például így:
= FKERES ( "termék 1", PriceList.xlsx! Products, 2)
= FKERES ( "termék 1"; PriceList.xlsx Termékek ;! 2)Ha átalakítani egy cellatartományt egy teljes Excel táblázatkezelő, az alábbi parancsot Table (Táblázat) fülre Insert (Beszúrás), akkor a kiosztás az egér tartományban, a Microsoft Excel automatikusan hozzáadja a képlet oszlop nevét (vagy a tábla nevét, ha ki az egész táblázatot).
Kész képlet a következőképpen néz ki:
= FKERES ( "termék 1", Table46 [[Termék]: [Ár]], 2)
PPS = ( "termék 1"; Table46 [[Termék]: [Ár]]; 2)Vagy még ez:
= FKERES ( "termék 1", Table46,2)
PPS = ( "termék 1"; Table46; 2)Ha elnevezett tartományok, linkek vezetnek a cellában van, nem számít, hol másol funkció CDF belül a munkafüzetet.
Helyettesítő karakterek használata a képletekben a CDF
Mint sok más feladatot is CDF, akkor a következő helyettesítő:
- A kérdőjel (?) - helyettesít egyetlen karaktert helyettesít.
- A csillaggal (*) - helyettesít bármilyen karaktersor.
Helyettesítő karakterek használata a CDF funkció hasznos lehet sok esetben, például:
- Ha nem emlékszik pontosan a kívánt szöveget találni.
- Ha szeretne találni egy szót, amely része a sejt tartalmát. Tudd, hogy a VPR keresi a cella tartalmát, teljes egészében, ha az opció Match egész cella tartalmát (teljes sejtes) a szabványos Excel Solver.
- Amikor egy sejt tartalmaz extra szóközöket a sor elején vagy végén a tartalmat. Ebben a helyzetben, akkor hosszú zavarba, megpróbálja megérteni, hogy miért a képlet nem működik.
1. példa: keresünk a szöveg elején vagy végén bizonyos szimbólumokat
Tegyük fel, hogy szeretné megtalálni egy adott ügyfél az adatbázisban, az alábbiak szerint. Nem emlékszem a nevére, de tudja, hogy kezd a „ack”. Itt van egy formula tökéletesen megbirkózni ezzel a feladattal:
Most, hogy már biztos megtalálta a pontos nevet, akkor használja ezt ugyanazt a képletet, hogy megtalálják a kifizetett összeg az ügyfél. Ez elég ahhoz, hogy változtatni a harmadik érv az FKERES függvény megszámlálja a jobb oldali oszlopban. Ebben az esetben, egy C oszlopában (3. tartományban):
Íme néhány példa a helyettesítő karakterek:
Mi megtaláljuk a nevét végződő „ember”:
Találunk egy nevet, hogy kezdődik a „hirdetés” és végződő „fia”:
Keresse az első név a listán, amely az 5 karakter:
= FKERES ($ A $ 2 "": $ C $ 11,1, HAMIS)
PPS = (; $ A $ 2 "": $ C $ 11; 1; HAMIS)Ahhoz, hogy FKERES helyettesítő működik megfelelően, akkor mindig HAMIS (FALSE), mint a negyedik érv. Ha a keresési tartományt egynél több értéket, amely megfelel a keresési kifejezés helyettesítő, az első érték talált vissza.
2. példa: Mi össze a karakterek és a cellahivatkozások a képletekben a CDF
Most adjuk járjon egy kicsit bonyolultabb példát, hogyan kell elvégezni a keresést a CDF funkció értékének egy bizonyos sejt. Képzeljük el, hogy az A oszlopban van egy lista a licenc kulcsok, és a B oszlopban a nevek, amely rendelkezik az engedélyt. Ezen kívül van egy darab (több karakter) egy engedély kulcs a cellában C1, és azt szeretné, hogy megtalálják a tulajdonos nevét.
Ezt meg lehet tenni a csak egy ilyen formula:
Ez a képlet megkeresi érték a C1A cellában egy előre meghatározott tartományban, és visszaadja a megfelelő érték a B oszlopban Megjegyezzük, hogy az első érv, használjuk a-jel karakter () előtt és után a cellahivatkozást összekapcsolni szövegfüzért.
Amint az az alábbi ábrán, a CDF függvény a megadott értéket „Jeremy Hill”, mert a licenc kulcs tartalmaz egy szekvenciát, a szimbólumok a C1A cellában.
Megjegyezzük, hogy a tábla_tömb érv (táblázat) a képen a tetején egy tábla (Table7) megadása helyett egy cellatartományt. Így tettünk az előző példában.
Pontos vagy hozzávetőleges mérkőzés VLOOKUP
Kezdésként nézzük meg, hogy a Microsoft Excel értünk pontos és közelítő véletlen.
Például, az alábbi képlet hibát jelez # N / A (# H / D), ha a tartomány A2: A15 kérte, hogy 4:
Hogy jobban megértsük megválasztásának fontosságára TRUE (IGAZ) vagy FALSE (HAMIS), lehetővé teszi, folytassa néhány képletek a funkciója a CDF és nézd meg az eredményt.
1. példa: keresés pontos egyezést használó WRT
Hogyan emlékszik, hogy megtalálják a pontos egyezés, a negyedik érv az FKERES függvény kell hamis (FALSE).
Nézzük vissza az asztalhoz, már az első példa, és megtudja, milyen állat lehet mozgatni sebességgel 50 mérföld per óra. Úgy vélem, hogy ez egy ilyen formula nem okoz Önnek gondot:
Felhívjuk figyelmét, hogy a keresési tartomány (A oszlop) tartalmaz két érték 50 - sejtekben A5 és A6. A képlet értékét adja vissza sejt B5. Miért? Mert ha keres egy pontos egyezés VLOOKUP használja az első érték, amely egybeesik a kívánt elemet.
2. példa: A FKERES, hogy megtaláljunk egy hozzávetőleges mérkőzés
Ez nagyon fontos, mert a CDF függvény a következő legmagasabb érték a beállított, majd a keresés leáll. Ha elhanyagolják, hogy megfelelően rendezve, az ügy a tény felett, hogy kapsz nagyon furcsa eredmények, vagy egy hibaüzenet # N / A (# N / A).
Most már használhatja az alábbi képlet:
= FKERES (69, $ A $ 2: $ B $ 15,2, TRUE) vagy = VLOOKUP (69, $ A $ 2: $ B $ 15,2)
= CDF (69; $ A $ 2: $ B $ 15; 2, TRUE) vagy = CDF (69; $ A $ 2: $ B $ 15, 2)Mint látható, szeretném megtudni, hogy milyen sebességgel az állatok legközelebb a 69 mph. És milyen eredménnyel visszamentem VLOOKUP:
Mint látható, a képlet visszaadja az eredményt Antelope (antilop), a sebesség 61 mérföld per óra, bár a lista is Gepard (Cheetah), azaz a sebesség 70 mérföld per óra, és 70 közelebb áll 69, mint 61, nem igaz? Miért történik ez? Mert VLOOKUP keresésekor hozzávetőleges mérkőzést visszaadja a legnagyobb érték, amely nem haladja meg a kívánatos.
Remélem, ezek a példák némi fényt a munkát a CDF funkció az Excel, és többé nem nézni, mint egy kívülálló. Most ez nem akadályozza meg röviden ismételjük meg a fő pontokat megtanultuk az anyagot annak érdekében, hogy jobban biztosítsa a memóriában.
CDF Excel -, hogy emlékszem!
Kapcsolódó cikkek