Obsah
Pri zmienke o databázach (DB) vám samozrejme ako prvé napadnú všelijaké buzzwordy ako SQL, Oracle, 1C alebo aspoň Access. Samozrejme, ide o veľmi výkonné (a väčšinou drahé) programy, ktoré dokážu automatizovať prácu veľkej a zložitej spoločnosti s množstvom dát. Problém je, že niekedy takáto sila jednoducho nie je potrebná. Vaša firma môže byť malá a s relatívne jednoduchými obchodnými procesmi, no zároveň ju chcete automatizovať. A práve pre malé firmy ide často o prežitie.
Na začiatok si sformulujme TOR. Vo väčšine prípadov by databáza pre účtovníctvo, napríklad klasický predaj, mala byť schopná:
- udržiavať v tabuľkách informácie o tovare (cena), uskutočnených transakciách a zákazníkoch a tieto tabuľky navzájom prepojiť
- mať pohodlie vstupné formuláre údaje (s rozbaľovacími zoznamami atď.)
- automaticky vyplniť niektoré údaje tlačené formuláre (platby, účty atď.)
- vydať potrebné Správy kontrolovať celý obchodný proces z pohľadu manažéra
Microsoft Excel to všetko s trochou snahy zvládne. Skúsme to implementovať.
Krok 1. Počiatočné údaje vo forme tabuliek
Informácie o produktoch, predajoch a zákazníkoch budeme uchovávať v troch tabuľkách (na rovnakom hárku alebo na rôznych – na tom nezáleží). Je zásadne dôležité premeniť ich na „inteligentné stoly“ s automatickou veľkosťou, aby ste na to v budúcnosti nemysleli. To sa vykonáva pomocou príkazu Formátovať ako tabuľku pútko Domov (Domov – Formátovať ako tabuľku). Na karte, ktorá sa potom zobrazí staviteľ (Dizajn) daj tabuľkám v poli popisné názvy Názov tabuľky na neskoršie použitie:
Celkovo by sme mali dostať tri „inteligentné tabuľky“:
Upozorňujeme, že tabuľky môžu obsahovať ďalšie objasňujúce údaje. Tak napríklad náš Cenaobsahuje ďalšie informácie o kategórii (skupina produktov, balenie, hmotnosť atď.) každého produktu a tabuľku Zákazník — mesto a región (adresa, DIČ, bankové údaje atď.) každého z nich.
Tabuľka Predaj budeme neskôr používať na zadávanie uskutočnených transakcií do neho.
Krok 2. Vytvorte formulár na zadávanie údajov
Údaje o predaji môžete samozrejme zadať priamo do zelenej tabuľky Predaj, ale to nie je vždy vhodné a spôsobuje to výskyt chýb a preklepov spôsobených „ľudským faktorom“. Preto by bolo lepšie vytvoriť špeciálny formulár na zadávanie údajov na samostatnom hárku niečoho takého:
Ak chcete získať aktualizovaný aktuálny dátum a čas, použite funkciu v bunke B3 TDATA (TERAZ). Ak nie je potrebný čas, potom namiesto toho TDATA funkciu možno použiť DNES (DNES).
V bunke B11 nájdite cenu vybraného produktu v treťom stĺpci inteligentnej tabuľky Cena pomocou funkcie VPR (VLOOKUP). Ak ste sa s tým ešte nestretli, tak si najprv prečítajte a pozrite si video tu.
V bunke B7 potrebujeme rozbaľovací zoznam s produktmi z cenníka. Na to môžete použiť príkaz Údaje – Validácia údajov (Overovanie dát), špecifikujte ako obmedzenie zoznam (zoznam) a potom zadajte do poľa zdroj (Zdroj) odkaz na stĺpec Meno z nášho inteligentného stola Cena:
Podobne sa vytvorí rozbaľovací zoznam s klientmi, ale zdroj bude užší:
=PRIAME(“Zákazníci[Klient]”)
Funkcie NEPRIAME (NEPRIAME) je v tomto prípade potrebný, pretože Excel, žiaľ, nerozumie priamym odkazom na inteligentné tabuľky v poli Zdroj. Ale ten istý odkaz „zabalený“ do funkcie NEPRIAME zároveň to funguje s ofinou (viac o tom bolo v článku o vytváraní rozbaľovacích zoznamov s obsahom).
Krok 3. Pridanie makra položky predaja
Po vyplnení formulára je potrebné doplniť údaje do neho zadané na koniec tabuľky Predaj. Pomocou jednoduchých odkazov vytvoríme riadok, ktorý sa pridá priamo pod formulár:
Tie. bunka A20 bude mať prepojenie na =B3, bunka B20 bude mať prepojenie na =B7 atď.
Teraz pridajme 2-riadkové základné makro, ktoré skopíruje vygenerovaný reťazec a pridá ho do tabuľky Predaj. Ak to chcete urobiť, stlačte kombináciu Alt + F11 alebo tlačidlo Visual Basic pútko vývojka (vývojár). Ak táto karta nie je viditeľná, povoľte ju najskôr v nastaveniach Súbor – Možnosti – Nastavenie pásu (Súbor — Možnosti — Prispôsobiť pás s nástrojmi). V okne editora Visual Basic, ktoré sa otvorí, vložte nový prázdny modul cez ponuku Vložiť – Modul a zadajte tam náš kód makra:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Skopírujte riadok údajov z formulára n = Worksheets("Sales").Range("A100000").End(xlUp) . Riadok 'určuje číslo posledného riadku v tabuľke. Pracovné hárky predaja("Predaj").Cells(n + 1, 1).Prilepiť špeciálne Prilepiť:=xlPasteValues''prilepiť do ďalšieho prázdneho riadku Pracovné hárky("Formulár vstupu").Rozsah ("B5,B7,B9"). ClearContents 'vymazať koncový formulár Teraz môžeme do nášho formulára pridať tlačidlo na spustenie vytvoreného makra pomocou rozbaľovacieho zoznamu Vložiť pútko vývojka (Vývojár — Vložiť — Tlačidlo):
Po jeho nakreslení, podržaním ľavého tlačidla myši, sa vás Excel opýta, ktoré makro mu musíte priradiť – vyberte naše makro Add_Sell. Text na tlačidle môžete zmeniť tak, že naň kliknete pravým tlačidlom myši a vyberiete príkaz Zmeniť text.
Teraz, po vyplnení formulára, stačí kliknúť na naše tlačidlo a zadané údaje sa automaticky pridajú do tabuľky Predaja potom sa formulár vymaže, aby ste mohli zadať novú dohodu.
Krok 4 Prepojenie tabuliek
Pred vytvorením prehľadu prepojme naše tabuľky, aby sme neskôr mohli rýchlo vypočítať predaj podľa regiónu, zákazníka alebo kategórie. V starších verziách Excelu by to vyžadovalo použitie niekoľkých funkcií. VPR (VLOOKUP) na dosadenie cien, kategórií, zákazníkov, miest atď. do tabuľky Predaj. Vyžaduje si to od nás čas a námahu a tiež „požiera“ veľa zdrojov programu Excel. Počnúc Excelom 2013 je možné všetko implementovať oveľa jednoduchšie nastavením vzťahov medzi tabuľkami.
Ak to chcete urobiť, na karte dátum (Dátum) cvaknutie súvislosť (vzťahy). V zobrazenom okne kliknite na tlačidlo vytvoriť (Nový) a vyberte z rozbaľovacích zoznamov tabuľky a názvy stĺpcov, podľa ktorých by mali súvisieť:
Dôležitý bod: tabuľky musia byť špecifikované v tomto poradí, tj prepojená tabuľka (Cena) nesmie obsahovať v stĺpci kľúč (Meno) duplicitné produkty, ako je to v tabuľke Predaj. Inými slovami, priradená tabuľka musí byť taká, v ktorej by ste hľadali údaje pomocou VPRak by bola použitá.
Samozrejme, stôl je spojený podobným spôsobom Predaj so stolom Zákazník podľa spoločného stĺpca Zákazník:
Po nastavení odkazov je možné zatvoriť okno pre správu odkazov; tento postup nemusíte opakovať.
Krok 5. Vytvárame prehľady pomocou súhrnu
Teraz, aby sme analyzovali predaj a sledovali dynamiku procesu, vytvorme napríklad nejaký druh prehľadu pomocou kontingenčnej tabuľky. Nastaviť aktívnu bunku na tabuľku Predaj a vyberte kartu na páse s nástrojmi Vložiť – kontingenčná tabuľka (Vložiť – kontingenčná tabuľka). V okne, ktoré sa otvorí, sa nás Excel opýta na zdroj údajov (tj tabuľku Predaj) a miesto na odovzdanie prehľadu (najlepšie na novom hárku):
Dôležitým bodom je, že je potrebné začiarknuť políčko Pridajte tieto údaje do dátového modelu (Pridať údaje do dátového modelu) v spodnej časti okna, aby Excel pochopil, že chceme zostavu zostaviť nielen na aktuálnej tabuľke, ale použiť aj všetky vzťahy.
Po kliknutí na OK v pravej polovici okna sa zobrazí panel Polia kontingenčnej tabuľkykde kliknúť na odkaz Všetkovidieť nielen tú aktuálnu, ale naraz všetky „inteligentné tabuľky“, ktoré sa v knihe nachádzajú. A potom, ako v klasickej kontingenčnej tabuľke, môžete jednoducho presunúť polia, ktoré potrebujeme, z akýchkoľvek súvisiacich tabuliek do oblasti filter, riadky, Stolbtsov or hodnoty – a Excel okamžite vytvorí akúkoľvek správu, ktorú potrebujeme na hárku:
Nezabudnite, že kontingenčnú tabuľku je potrebné pravidelne aktualizovať (pri zmene zdrojových údajov) kliknutím na ňu pravým tlačidlom myši a výberom príkazu Aktualizovať a uložiť (Obnoviť), pretože to nemôže robiť automaticky.
Taktiež výberom ľubovoľnej bunky v súhrne a stlačením tlačidla Kontingenčný graf (Kontingenčný graf) pútko Analýza (Analýza) or parametre (Možnosti) v ňom vypočítané výsledky si môžete rýchlo vizualizovať.
Krok 6. Vyplňte tlačové materiály
Ďalšou typickou úlohou každej databázy je automatické vypĺňanie rôznych tlačív a tlačív (faktúr, faktúr, úkonov a pod.). O jednom zo spôsobov, ako to urobiť, som už písal. Tu implementujeme napríklad vyplnenie formulára podľa čísla účtu:
Predpokladá sa, že do bunky C2 používateľ zadá číslo (číslo riadku v tabuľke Predajv skutočnosti) a potom sa potrebné údaje vytiahnu pomocou už známej funkcie VPR (VLOOKUP) a funkcie INDEX (INDEX).
- Ako používať funkciu VLOOKUP na vyhľadávanie a vyhľadávanie hodnôt
- Ako nahradiť funkciu VLOOKUP funkciami INDEX a MATCH
- Automatické vypĺňanie formulárov a formulárov údajmi z tabuľky
- Vytváranie zostáv pomocou kontingenčných tabuliek