Kako uporabljati VLOOKUP Excel? Excelova funkcija v Excelu za lutke in ne samo

Vsaka oseba, ki se ukvarja s številkami, bodisi zaradi poklicnega interesa ali preprosto za ljubezen do umetnosti, prva stvar je, da obvladuje kalkulator. Kaj je naslednje? Naslednji pa je iznajdljiv program za delo s številkami - Excel. Verjetno je nemogoče popolnoma spoznati ta program, pa tudi, da matematiko dobro poznamo. Vendar pa v programu obstajajo osnovne funkcije, funkcija Excel, saj vemo, da lahko z 90% zanesljivostjo govorimo o sposobnosti za delo s podatki na ravni samozavestnega uporabnika. Ena najpomembnejših funkcij v Excelu je VLOOKUP, ki je zelo vsestranski. V članku je opisano, kako uporabiti VLOOKUP v Excelu (za lutke). Seveda pa dojemanje teorije ne bo prineslo popolnega razumevanja procesa uporabe funkcije. Najprej je potrebno vaditi najprej na majhnih podatkovnih poljih, nato pa na tabelah vseh velikosti.

Bistvo in namen funkcije CDF v Excelu

Funkcija CDF prihrani veliko časa za strokovnjake, kot so ekonomisti, finančniki, računovodje, tehnologi. Opis funkcije VLOOKUP v Excelu in primer njegove uporabe je treba začeti z upoštevanjem situacije, s katero se pri svojem delu pogosto srečujejo strokovnjaki različnih vrst. Če obstajata dve tabeli, v katerih se pojavljajo enaki podatki, ki jih je mogoče definirati (npr. Imena blaga), pa se opredelitve zanje v različnih tabelah razlikujejo, na primer v eni je količina, v drugi pa cena. Hkrati je naloga združiti vse te podatke in narediti ustrezne izračune - v takih situacijah pomaga funkcija CDF. Da bi razumeli, kako VLOOKUP deluje v Excelu, sta spodaj predstavljeni dve tabeli. V vsaki od njih je koda izdelka. V eni je ustrezna količina, v drugi - cena. Za zamenjavo cen v prvi tabeli in uporabljenih CDF.

Ročna zamenjava podatkov med tabelami je velika izguba časa in truda delavca, napakam se ni mogoče izogniti. S pomočjo CDF-ja lahko hitro in enostavno najdete ustrezne vrednosti. Za poglobljeno razumevanje uporabe VLOOKUP v Excelu je potrebno razčleniti vse argumente funkcije.

Argumenti funkcij

Če želite uporabiti funkcijo, je potrebno stati v celici rezultata, na kartici izberite zavihek Formule - "Povezave in nizi" - "CDF". V celici se je pojavil napis "= VLR". Sedaj morate pravilno vnesti argumente funkcije. To lahko naredite s podpičjem neposredno v vrstici s formulami, vendar je za uporabnika novice bolj primerno, da to stori v pogovornem oknu funkcijskih argumentov.

Funkcija CDF ima štiri argumente - vrednost, ki jo iščete, tabelo, številko stolpca, intervalno iskanje. Potrebni so prvi trije, ki so v pogovornem oknu poudarjeni krepko. Razložimo, kaj so argumenti funkcije:

• iskana vrednost je tisto, kar iščemo;
• miza - kje iskati;
• številka stolpca - v katerem stolpcu za iskanje;
• intervalni pogled - razvrščeno.

Argument "iskana vrednost"

V prvem polju morate vnesti, kar potrebujete za iskanje. Obstaja lahko številka, besedilo, datum. Vnesete lahko absolutno vrednost, pritrdite referenco na želeno vrednost. Iskana vrednost naj bi bila teoretično prisotna v obeh tabelah. V zgornjem primeru je lahko taka vrednost koda izdelka. Da bi se izognili napakam, je bolje uporabiti povezavo.

Ko besedilo uporabljate kot želeno vrednost, mora biti v narekovajih. Ko se nanaša na celico z besedilom, narekovaji niso potrebni. Registracija ob vnosu besedila ni pomembna. Uporabi se lahko mehko iskanje, ki temelji na fragmentu besedila. Če želite to narediti, morate v narekovajih priložiti del besedila v znakih *.

Najpogostejša težava pri uporabi funkcije je različna oblika celic v obsegu. Če v polju prvega argumenta postavite povezavo na celico, kjer so podatki v besedilni obliki, in v iskalnem obsegu bodo podatki v številskem formatu, ne bodo našli primerjave z zunanjo identiteto vrednosti CDF.

Pretvorba v številsko obliko je enostavna - polje morate pomnožiti z enim.

Argument tabele

Tukaj morate vnesti obseg, kjer bo funkcija iskala prvo vneseno vrednost. Območje za iskanje vrednosti bo prvi stolpec izbranega območja. Vrednost, ki jo želite poiskati in postaviti kot rezultat, mora biti v stolpcu desno od stolpca za iskanje zadetkov. To je ena od ključnih pomanjkljivosti uporabe CDF: da bi lahko delali z njo, je treba tabelo pogosto obnoviti, tako da so zahtevani podatki na levi strani. Območje lahko vnesete tako ročno kot referenčno. V primeru je videti tako:

Razpon tukaj je desna tabela. Na levi strani je iskana vrednost v stolpcu na desni, kjer bo Excel iskal vrednost 3187849428, vrednost, ki jo je treba najti in nadomestiti kot rezultat formule. Da bi dobili natančen rezultat, je bolje, da določite obseg, ga izberete in pritisnete tipko F4, povezava do polja bo postala absolutna.

Argument "številka stolpca"

Tu je potrebno vnesti številko, v kateri je v stolpcu stolpec, od levega, potreben, da vzamemo vrednost za zamenjavo kot rezultat računa. V zgornjem primeru je to drugi stolpec, v nizu argumentov morate zapisati številko 2. Če bi med stolpci Koda in Cena obstajal še en stolpec, bi morali zapisati številko 3 in tako naprej.

Argument za predogled intervala

Polnjenje tega polja je neobvezno, vendar je lahko zelo pomembno. Obstaja lahko ena od dveh vrednosti - 1 (res) ali 0 (false). Večina uporabnikov meni, da je funkcija tega argumenta določiti natančnost ujemanja želenih vrednosti. To ni povsem pravilno. Če podate enoto, če obstajajo ponovitve v območju tabele, funkcija vrne zadnjo najdeno vrednost. V tem primeru bo funkcija VLR upoštevala vse vrednosti, ki so manjše ali enake tistim, ki so vnesene v polje "Zahtevana vrednost". Če funkcija najde večjo vrednost, vendar ne najde manjše ali enake vrednosti, bo ustvarila napako N / A.

Vidite lahko, da je v polje "Vrednost" vnesena številka 3187849425, v želenem območju ne obstaja nobena vrednost, program, kjer so vse vrednosti, ki so manjše ali enake želeni vrednosti, vrne vrednost, ki ustreza zadnji ustrezni oznaki v seznamu - 3187848593, katere cena je 2479,46 rubljev. Če polje za prikaz intervalov ostane prazno, bo funkcija delovala enako kot vrednost enote.

Ko vnesete vrednost 0 v polje argumenta, funkcija vrne samo vrednost, ki ustreza zahtevani vrednosti. Če je v iskalnem območju ponavljanje, bo funkcija izvedla prvo ujemanje. Ko uporabljamo formulo z argumentom 0, funkcija deluje precej dlje, toda bolj natančno.

Značilnosti uporabe CDF v več pogojih

Uporaba funkcije CDF je pogosto ustvarjalni proces, ki od uporabnika zahteva matematično razmišljanje. Pogosto je treba poiskati ujemanje ne za en stolpec, ampak za dva ali celo več. S pomočjo dodatnih dejanj lahko uporabite tudi funkcijo VLOOKUP. V obeh tabelah je potrebno ustvariti dodatne stolpce, kjer združiti podatke iz obravnavanih stolpcev. To lahko storite s funkcijo "CLUTCH" ali ikono "&".

Kako uporabljati funkcijo CDF, če so podatki na različnih listih

Kako uporabljati VLOOKUP v Excelu na različnih listih, v različnih datotekah? Pogosto uporabniki ustvarijo nekaj referenčnih knjig, s katerimi je potrebno primerjati in poiskati ustreznosti. Razlike pri delu v takih pogojih so v nekoliko drugačni obliki polja "Tabela" v oknu funkcijskih argumentov. Pred vstopom v obseg je potrebno zapisati številko lista (če so podatki v eni knjigi) ali ime datoteke (če podatki niso v eni knjigi). Če je v zgornjem primeru cenik kopiran v ločeno datoteko, ga pokličite "Cena" in s funkcijo CDF poiščite cene tam, bo izgledal takole:

Vse zgornje ukrepe lahko izvedete s povezavo. Morate postaviti kazalec v polje "Tabela", pojdite na želeno datoteko in z miško izberite obseg. Včasih se zgodi, da tabela ni vstavljena kot povezava v oknu. Potem morate narediti naslednje: odprite prvotno tabelo, kjer morate narediti izračune, nato pa v meniju "Datoteka" - "Odpri" poiščite drugo tabelo. Datoteke, odprte na ta način, delujejo nemoteno.

Vse zgornje ukrepe lahko izvedete s povezavo. Morate postaviti kazalec v polje "Tabela", pojdite na želeno datoteko in z miško izberite obseg. Včasih se zgodi, da tabela ni vstavljena kot povezava v oknu. Potem morate narediti naslednje: odprite prvotno tabelo, kjer morate narediti izračune, nato pa v meniju "Datoteka" - "Odpri" poiščite drugo tabelo. Datoteke, odprte na ta način, delujejo nemoteno.

Za tehtanje datoteke, v kateri so formule s CDF, s sklicevanjem na druge datoteke bo veliko več kot brez njih. To je lahko problematično, na primer prenos datotek. Da bi se izognili tem težavam, morate pretvoriti formulo v vrednosti. Na traku izberite podmeni "Podatki" in ukaz "Spremeni komunikacijo". Tudi če se podatki ne vzamejo iz druge datoteke, je vedno koristno zamenjati formule z vrednostmi - zaradi tega so izračuni bolj zanesljivi.

Spustni seznam za lažje delo s CDF

Funkcija VLR pogosto ne deluje, če pride do manjših neskladnosti podatkov. Ta dodatni prostor v besedilu, podatki, vneseni z napakami. Da bi se izognili vsem tem težavam, uporabite spustni seznam v Excelu, da vnesete vrednosti. Smiselno je začeti z delom s stalno ponavljajočimi se podatki. Če obstaja določena referenčna knjiga, ki se uporablja kot tabela za primerjavo, lahko primerjalno območje vzamemo kot podatke za spustni seznam in uporabimo za oblikovanje tabele, v katero bomo nato vstavili podatke v funkcijo CDF. Kazalec se nahaja v celici, kjer morate vnesti vrednost. Nadalje na traku je pododdelek "Podatki", izbran je ukaz "Preverjanje podatkov". V pogovornem oknu v polje "Podatkovni tip" vnesite vrednost "Seznam". V polju "Vir" se zabeleži obseg referenčne tabele. Spustni seznam se oblikuje. Zdaj, ko izpolnite tabelo, je zagotovljena popolna skladnost vrednosti.

Primer uporabe funkcije VLOOKUP v Excelu

Kako uporabljati VLOOKUP v Excelu, vsak uporabnik sam rešuje, na podlagi praktičnih nalog v svojem delu. Ob upoštevanju vseh zgoraj navedenih odtenkov lahko prejšnji primer pripeljemo do njegovega logičnega konca in izdelamo priročen in jasen algoritem za uporabo VLR funkcije v praksi. Torej obstaja ocena stroškov za rezervne dele za popravilo opreme. Na voljo je tudi cenik rezervnih delov. Ekonomist podjetja mora najti stroške nadomestnih delov za popravila.

Najprej morate pravilno napisati formulo.

Tako se bo s funkcijo VLOOKUP prikazala cena, ki ustreza kodi v prvi vrstici. Treba je pomnožiti formulo navzdol, za to pa izberite celico s formulo in povlecite kvadratek v desnem kotu celice. V stolpcu D je funkcija vrnila cene, ki ustrezajo oznakam. Formule je treba nadomestiti z vrednostmi. To naredite tako, da izberete polne celice stolpca D, jih kopirate in jih prilepite kot vrednosti. Nato morate ustvariti stolpec "Sum", kjer vnesete formulo za produkt količine in cene, nato pa uporabite seštevanje, da povzamemo stroške.

To je primer, kako VLOOKUP deluje v Excelu.

Napake pri uporabi funkcije CDF

V začetni fazi uporabe namesto želenih vrednosti funkcija pogosto označuje različne vrste napak. Vedeti, kaj pomeni napaka, je pravi način, da ga hitro popravimo. Najpogosteje vrnjene napake:

• "N / A" - najpogostejša vrsta napake. Lahko se zgodi iz več razlogov.

1. Stolpec, na katerem išče ujemanje, je napačno lociran (mora biti levi). Če pride do situacije, v kateri je želena vrednost levo od območja tekme, je treba tabelo pretvoriti. Na primer, kopirajte želeni stolpec in ga prilepite na desno stran iskalnega območja.
2. Napaka "N / A" se lahko vrne, če območje iskanja ni fiksno, medtem ko vlečete formulo WFD.
3. Če je natančno iskanje podano z argumentom Interval View (nastavljena je številka 0), se vrne napaka "N / A", če v dveh tabelah ni natančnega ujemanja.
4. Argument Interval Viewing je nastavljen na najbližjo vrednost (1 je označen ali polje ni napolnjeno), in razpon, po katerem se izvaja iskanje, ni razvrščen. V primeru netočnega iskanja je potrebno razvrstiti skrajni levi stolpec iskalnega območja.
5. Primerjani podatki imajo dodatne presledke (za njihovo odstranitev lahko uporabite funkcijo "SIFPROBELS", ki jo uporabite za tabelo in za želeno vrednost), drugačno obliko, dodatne ponudbe. Za zapisovanje enakih vrednosti v obe tabeli je smiselno uporabiti spustni seznam.

• "LINK" - ta napaka se pogosto pojavi, če je številka stolpca nepravilno določena, če toliko stolpcev ni v izbranem območju. V takih primerih je treba zapomniti, da je številka odložena, štetje od levega stolpca izbranega razpona, ne pa tabele kot celote.
• "NAME" - napaka se pogosto vrne, ko je besedilo vneseno v "Zahtevano vrednost". Besedilo mora biti napisano v narekovajih.

Poznavanje uporabe VLOOKUP-a v Excelu, kot vse ostalo v tem programu, prihaja s prakso. Sama študija teorije namesto jasnosti bo verjetno prinesla kaos v razumevanju določene operacije v programu. Pri proučevanju katere koli funkcije programa Excel je vedno bolj učinkovito, če se uporabnik zanese na določeno, čeprav majhno, primerno tablično računalnik. To vam omogoča boljše razumevanje bistva analize, utrditev pridobljenega znanja. Torej s funkcijo CDF v Excelu. Z veliko teorijo praktična uporaba kaže, da ni tako kompleksna, kot je uporabna.