Prihranimo se od rutinskega dela z uporabo funkcije CDF v Excelu

Zagotovo so se mnogi aktivni uporabniki urejevalnikov preglednic v Excelu občasno ukvarjali s situacijami, v katerih je bilo potrebno nadomestiti vrednosti iz ene tabele v drugo. Predstavljajte si, da je v skladišče prišel določen predmet. Na razpolago imamo dve datoteki: eno s seznamom imen blaga, drugo - cenik tega izdelka. Ko odprete cenik, ugotovimo, da je v njem več položajev in da se ne nahajajo v zaporedju, ki je v datoteki s seznamom imen. Malo verjetno je, da bi se katerikoli izmed nas raje zamislil, da bi ročno preveril datoteke in prenesel cene iz enega dokumenta v drugega. Seveda je v primeru, ko gre za 5–10 pozicij, mehanski vnos podatkov povsem mogoč, a kaj, če število imen presega 1000? V tem primeru nam bo Excel in njegova čarobna funkcija CDF (ali vlookup, če govorimo o angleški različici programa) pomagala pri obvladovanju monotonih del.

VPR omogoča delo z velikimi tabelami, prenos podatkov iz enega v drugega

Kaj je CDF in kako ga uporabljati?

Torej bo na začetku našega dela pri pretvarjanju podatkov iz ene tabele v drugo primerno narediti majhen pregled funkcije CDF. Kot ste verjetno že razumeli, vam vlookup omogoča prenos podatkov iz ene tabele v drugo, s čimer polnite celice, ki jih potrebujemo samodejno. Da bi funkcija CDF delovala pravilno, bodite pozorni na prisotnost združenih celic v glavah tabele. Če jih imate, jih boste morali razbiti.

Recimo, da moramo izpolniti tabelo naročil s podatki iz „Cenik“

Zato se soočamo z nalogo - prenesti cene obstoječih izdelkov na tabelo z njihovimi imeni in izračunati skupne stroške vsakega izdelka. Za to moramo izvesti naslednji algoritem:

  1. Najprej vnesite Excelovo preglednico v želeno obliko. V pripravljeno podatkovno matrico dodajte dva stolpca z imeni »Cena« in »Cena«. Za celice v območju novo oblikovanih stolpcev izberite denarno obliko.
  2. Zdaj aktivirajte prvo celico v bloku "Cena" in pokličite "Čarovnik za funkcije". To lahko storite tako, da pritisnete gumb “fx”, ki se nahaja pred formulo ali pa držite tipko “Shift + F3”. V pogovornem oknu, ki se odpre, poiščite kategorijo »Povezave in nizi«. Tu nas ne zanima nič drugega kot funkcija CDF. Izberite ga in kliknite »V redu«. Mimogrede, treba je povedati, da lahko funkcijo VLOOKUP prikličete prek zavihka »Formule«, v spustnem seznamu, v katerem je tudi kategorija »Povezave in nizi«.
  3. Po aktiviranju CDF-ja boste videli okno s seznamom argumentov izbrane funkcije. V polje »Zahtevana vrednost« morate vnesti obseg podatkov iz prvega stolpca tabele s seznamom prejetega blaga in njegovo količino. To pomeni, da morate Excelu povedati, kaj naj natančno najde v drugi tabeli in prenesete na prvo.
  4. Ko je prvi argument označen, lahko greste na drugo. V našem primeru je drugi argument tabela s cenikom. Postavite kazalec miške v polje argumenta in se pomaknite na seznam cen. Ročno izberite območje s celicami na območju stolpcev z imeni komercialnih izdelkov in njihovo ceno. Določite Excel, katere vrednosti želite preslikati funkcije VLOOKUP.
  5. Da Excel ne bi bil zmeden in se nanašal na podatke, ki jih potrebujete, je pomembno, da popravite povezavo, ki ste jo dobili. To naredite tako, da v polju Tabela označite želene vrednosti in pritisnete tipko F4. Če je vse narejeno pravilno, se na zaslonu prikaže znak $.
  6. Sedaj gremo na polje argumenta Število strani in ga nastavimo na vrednost “2”. Ta blok vsebuje vse podatke, ki jih želite poslati v našo delovno tabelo, zato je pomembno, da je vrednost Interval View dodeljena napačna vrednost (nastavite položaj na FALSE). To je potrebno, da funkcija VLR deluje samo z natančnimi vrednostmi in ne okrog njih.

Izpolnite formulo za prvi stolpec.

Zdaj, ko so končana vsa potrebna dejanja, jih lahko potrdimo samo s pritiskom na gumb »OK«. Takoj, ko se spremenijo podatki v prvi celici, bomo morali uporabiti funkcijo CDF za celoten Excelov dokument. Da bi to naredili, je dovolj, da VLOOKUP razširimo preko celotnega stolpca »Cena«. To lahko storite tako, da povlečete spodnji desni kot celice s spremenjeno vrednostjo na dno stolpca. Če se je vse izteklo in podatki spremenili, kot smo potrebovali, lahko nadaljujemo z izračunom skupne vrednosti našega blaga. Za izvedbo tega dejanja moramo najti produkt dveh stolpcev - »Količine« in »Cene«. Ker Excel vsebuje vse matematične formule, se izračun lahko zagotovi v "Formi Bar" z znano "fx" ikono.

Pomembna točka

Zdi se, da je vse pripravljeno in VLOOKUP se je soočil z našo nalogo, vendar ni bilo tam. Dejstvo je, da je v stolpcu »Cena« funkcija CDF še vedno aktivna, dokaz tega dejstva je prikaz slednjega v formularni vrstici. To pomeni, da sta obe naši mizi povezani med seboj. Takšen tandem lahko pripelje do dejstva, da se pri spremembi podatkov v tabeli s ceno spremenijo tudi informacije v naši delovni datoteki s seznamom blaga.

Tej situaciji se je najbolje izogniti z delitvijo dveh tabel. To naredimo tako, da izberemo celice, ki so v območju stolpca Cena, in jo kliknemo z desno miškino tipko. V odprtem oknu izberite in aktivirajte možnost »Kopiraj«. Po tem, ne da bi odstranili izbiro iz izbranega celičnega področja, znova pritisnite desno miškino tipko in izberite možnost »Posebno lepljenje«.

Če omogočite to možnost, se na zaslonu odpre pogovorno okno, v katerem morate potrditi polje poleg kategorije »Vrednost«. Potrdite svoja dejanja s klikom na gumb »V redu«.

Vrnemo se v našo formularno vrstico in preverimo prisotnost aktivne funkcije VLOOKUP v stolpcu Cena. Če na mestu formule vidite samo številske vrednosti, potem se je vse izteklo in funkcija CDF je onemogočena. To pomeni, da je povezava med dvema Excelovima datotekama prekinjena in ni nevarnosti nenačrtovanih sprememb ali izbrisov podatkov, ki so priloženi tabeli s cenikom. Zdaj lahko varno uporabite tabelarni dokument in ne skrbite, kaj se bo zgodilo, če je cenik zaprt ali premaknjen na drugo mesto.

Povlecite oznako v spodnjem desnem kotu celice, da uporabite formulo za celotno tabelo.

Kako primerjati dve tabeli v Excelu?

S pomočjo funkcije CDF lahko v nekaj sekundah primerjate več različnih vrednosti, da bi na primer primerjali, kako so se spremenile cene obstoječega izdelka. V ta namen morate registrirati VLOOKUP v praznem stolpcu in povezati funkcijo s spremenjenimi vrednostmi, ki so v drugi tabeli. Najboljše od vsega, če se stolpec "Nova cena" nahaja takoj za stolpcem "Cena". Takšna odločitev vam bo omogočila, da bodo spremembe cen bolj primerne.

Sposobnost dela z več pogoji

Še ena nedvomna prednost funkcije VLOOKUP je njena sposobnost delovanja z več parametri, ki so del vašega izdelka. Če želite poiskati izdelek po dveh ali več značilnostih, morate:

  1. Ustvarite dva (ali, če je potrebno, več) iskalnih izrazov.
  2. Dodajte nov stolpec, kateremu bodo med delom funkcije dodani vsi drugi stolpci, po katerih se izdelek išče.
  3. V dobljenem stolpcu po zgoraj opisanem algoritmu vpišemo formulo VLOOKUP, ki nam je že znana.

V zaključku je treba povedati, da vzdrževanje Excelovih funkcij kot CDF močno poenostavlja delo s tabelaričnimi informacijami. Ne bojte se uporabiti VLOOKUP pri delu z ogromno količino podatkov, kajti ne glede na to, kako se izvajajo, je načelo funkcije vedno enako. Vse kar morate storiti je pravilno opredeliti njene argumente.