Určite mnohí aktívni používatelia editora tabuľkového procesora programu Excel sa občas museli zaoberať situáciami, v ktorých bolo potrebné nahradiť hodnoty z jednej tabuľky do druhej. Len si predstavte, určitá položka sa dostala do vášho skladu. Máme k dispozícii dva súbory: jeden so zoznamom názvov prijatých tovarov, druhý - cenník tohto výrobku sám. Po otvorení cenníka zistíme, že v ňom je viac pozícií a nie sú umiestnené v sekvencii, ktorá je v súbore so zoznamom mien. Takmer nikoho z nás sa nebude páčiť myšlienka kontrolovať oba súbory a prenášať ceny z jedného dokumentu do druhého manuálne. Samozrejme, v prípade, že ide o 5-10 pozícií, je mechanický vstup dát celkom možný, ale čo keď počet mien presahuje 1000? V tomto prípade nám program Excel a jeho čarovná funkcia CDF (alebo vlookup, ak hovoríme o anglickej verzii programu) nám pomôžu vyrovnať sa s monotónnou prácou.
VPR vám umožňuje pracovať s veľkými tabuľkami a prenášať dáta z jedného do druhého
obsah
- 1 Čo je CDF a ako ju používať?
- 2 Dôležitý bod
- 3 Ako porovnávať dve tabuľky v programe Excel?
- 4 Schopnosť pracovať s niekoľkými podmienkami
Čo je CDF a ako ju používať?
Takže na začiatku našej práce na konvertovaní dát z jednej tabuľky do druhej, bude vhodné urobiť malý prehľad o funkcii CDF. Ako ste už pravdepodobne pochopili, vlookup vám umožňuje prenášať dáta z jednej tabuľky do druhej, čím vyplníte bunky, ktoré potrebujeme automaticky. Aby funkcia CDF fungovala správne, venujte pozornosť prítomnosti zlúčených buniek v hlavičkách tabuľky. Ak nejaké existujú, musíte ich zlomiť.
Predpokladajme, že je potrebné vyplniť "tabuľku objednávok" s údajmi z "cenníka"
Takže sme čelili úlohe - presunúť ceny existujúcich tovarov do tabuľky s ich menami a vypočítať celkové náklady každého produktu. Aby sme to urobili, musíme vykonať nasledujúci algoritmus:
- Najprv prineste tabuľku programu Excel do požadovanej podoby. Pridajte do matice zozbieraných údajov dva stĺpce s názvami "Cena" a "Cena". Vyberte pre bunky v rozsahu novo vytvorených stĺpcov, menový formát.
- Teraz aktivujte prvú bunku v bloku "Cena" a zavolajte "Sprievodca funkciami". Môžete to urobiť stlačením tlačidla "fx" umiestneného pred riadkom vzorca alebo stlačením kombinácie klávesov "Shift + F3". V dialógovom okne, ktoré sa otvorí, vyhľadajte kategóriu "Odkazy a matice".Tu sa nezaujíma nič iného ako funkcia CDF. Vyberte ho a kliknite na tlačidlo "OK". Mimochodom, malo by sa povedať, že funkciu VLOOKUP možno zavolať prostredníctvom karty "Formuláre", v rozbaľovacom zozname, kde sa nachádza aj kategória "Odkazy a matice".
- Po aktivácii CDF sa zobrazí okno so zoznamom argumentov zvolenej funkcie. V poli "Požadovaná hodnota" musíte zadať rozsah údajov obsiahnutý v prvom stĺpci tabuľky so zoznamom prijatých tovarov a ich množstvom. To znamená, že musíte Exceloviet povedať, čo presne by malo nájsť v druhej tabuľke a preniesť na prvú.
- Po označení prvého argumentu môžete prejsť na druhý argument. V našom prípade je druhým argumentom tabuľka s cenníkom. Umiestnite kurzor myši do poľa argumentov a presuňte sa na zoznam cien. Ručne vyberte rozsah s bunkami umiestnenými v oblasti stĺpcov s názvami komerčných produktov a ich cenou. Zadajte program Excel, ktoré hodnoty chcete mapovať funkcie VLOOKUP.
- Aby sa program Excel nemohol zamieňať a odvolávať sa na údaje, ktoré potrebujete, je dôležité opraviť odkaz, ktorý mu bol daný. Ak to chcete urobiť, zvýraznite požadované hodnoty v poli Table a stlačte kláves F4. Ak je všetko vykonané správne, na obrazovke by sa mal objaviť znak $.
- Teraz ideme do poľa argumentu číslo stránky a nastavíme ho na hodnotu "2". V tomto bloku sú všetky údaje, ktoré chcete poslať do našej pracovnej tabuľky, a preto je dôležité priradiť Interval View (nastaviť pozíciu na hodnotu FALSE). Toto je potrebné, aby funkcia CDF fungovala len s presnými hodnotami a nie zaokrúhlila.
Pridajte vzorec pre prvý stĺpec.
Teraz, keď boli všetky potrebné kroky dokončené, môžeme ich potvrdiť iba stlačením tlačidla "OK". Akonáhle dôjde k zmene údajov v prvej bunke, budeme musieť použiť funkciu CDF na celý dokument programu Excel. Za týmto účelom jednoducho šírite VLOOKUP okolo celého stĺpca "Cena". Môžete to urobiť presunutím pravého dolného rohu článku s upravenou hodnotou do úplnej dolnej časti stĺpca. Ak bolo všetko spracované a dáta sa menili podľa potreby, môžeme pristúpiť k výpočtu celkovej hodnoty nášho tovaru. Ak chcete vykonať túto akciu, musíme nájsť produkt dvoch stĺpcov - "Množstvá" a "Ceny". Vzhľadom na to, že program Excel obsahuje všetky matematické vzorce, výpočet je možné poskytnúť v "Formule Bar" pomocou známej ikony "fx".
Dôležitý bod
Zdá sa, že všetko je pripravené a VLOOKUP sa vyrovnal s našou úlohou, ale nebolo to tam.Faktom je, že v stĺpci "Cena" je funkcia CDF stále aktívna, dôkazom tejto skutočnosti je zobrazenie tejto skutočnosti v poli vzorca. To znamená, že obe naše tabuľky sú navzájom prepojené. Takýto tandem môže viesť k tomu, že keď sa údaje v tabuľke s cenovými zmenami zmení aj informácie obsiahnuté v našom pracovnom súbore so zoznamom tovaru.
Táto situácia je najlepšie vyhnúť rozdelením dvoch tabuliek. Ak to chcete urobiť, musíme vybrať bunky, ktoré sa nachádzajú v rozsahu stĺpca Cena, a kliknite pravým tlačidlom myši. V okne, ktoré sa otvorí, vyberte a aktivujte možnosť "Kopírovať". Potom bez toho, aby ste odstránili výber z vybranej oblasti buniek, znova stlačte pravé tlačidlo myši a zvoľte možnosť "Vložiť špeciálne".
Aktivovaním tejto možnosti sa na obrazovke otvorí dialógové okno, v ktorom je potrebné začiarknuť políčko vedľa kategórie "Hodnota". Akciu potvrďte kliknutím na tlačidlo "OK".
Vrátime sa do nášho formulára a skontrolujte prítomnosť aktívnej funkcie VLOOKUP v stĺpci Cena. Ak namiesto vzorca uvidíte iba číselné hodnoty, potom sa všetko ukázalo a funkcia CDF je vypnutá.To znamená, že spojenie medzi dvoma súbormi programu Excel je rozbité a neexistuje žiadna hrozba neplánovaných zmien alebo odstránení údajov pripojených k tabuľke s cenníkom. Teraz môžete bezpečne použiť tabuľkový dokument a nebojte sa, čo sa stane, ak sa cenník uzavrie alebo presunie na iné miesto.
Presuňte značku v pravom dolnom rohu bunky a použite vzorec na celú tabuľku.
Ako porovnávať dve tabuľky v programe Excel?
Pomocou funkcie CDF môžete porovnať niekoľko rôznych hodnôt za pár sekúnd, aby ste napríklad porovnali, ako sa zmenili ceny existujúceho produktu. Aby ste to dosiahli, je potrebné zaregistrovať VLOOKUP do prázdneho stĺpca a spojiť funkciu so zmenenými hodnotami, ktoré sú v inej tabuľke. Najlepšie zo všetkých, ak stĺpec "Nová cena" bude umiestnený hneď za stĺpcom "Cena". Takéto rozhodnutie vám umožní porovnať cenové zmeny.
Schopnosť pracovať s niekoľkými podmienkami
Ďalšou nepochybnou výhodou funkcie VLOOKUP je jej schopnosť pracovať s viacerými parametrami, ktoré sú vlastné vášmu produktu. Ak chcete nájsť produkt podľa dvoch alebo viacerých charakteristík, musíte:
- Vytvorte dve podmienky (alebo ak je to potrebné, viac) pre vyhľadávanie.
- Pridať nový stĺpec, v ktorom proces funkcie bude pridaná do všetkých ostatných stĺpcov, ktoré sú vyhľadávané produktu.
- Vo výslednom stĺpci, podľa vyššie uvedeného algoritmu, zavedieme známej vzorec pre funkcie VLOOKUP.
Na záver treba povedať, že zachovanie funkcií programu Excel, napríklad PPS, je výrazne uľahčiť prácu s tabuľkových dát. Nebojte sa použiť VLOOKUP pre prácu s veľkými objemami dát, pretože bez ohľadu na to, ako boli navrhnuté tak, princíp funkcia je vždy rovnaký. Všetko, čo musíte urobiť, je správne definovať jeho argumenty.
