Obsah
Při zmínce o databázích (DB) vás jako první samozřejmě napadnou nejrůznější hlášky jako SQL, Oracle, 1C nebo alespoň Access. Samozřejmě se jedná o velmi výkonné (a z velké části drahé) programy, které dokážou zautomatizovat práci velké a složité společnosti se spoustou dat. Potíž je v tom, že někdy taková síla prostě není potřeba. Vaše firma může být malá a s relativně jednoduchými obchodními procesy, ale také ji chcete automatizovat. A právě pro malé firmy jde často o přežití.
Pro začátek zformulujme TOR. Ve většině případů by databáze pro účetnictví, například klasické tržby, měla umět:
- udržovat v tabulkách informace o zboží (ceně), uskutečněných transakcích a zákaznících a tyto tabulky vzájemně propojit
- mít pohodlí vstupní formuláře data (s rozevíracími seznamy atd.)
- automaticky vyplní některé údaje tištěné formuláře (platby, účty atd.)
- vydat potřebné Zprávy kontrolovat celý podnikový proces z pohledu manažera
To vše Microsoft Excel s trochou snahy zvládne. Zkusme to implementovat.
Krok 1. Počáteční data ve formě tabulek
Informace o produktech, prodejích a zákaznících budeme ukládat do tří tabulek (na stejném listu nebo na různých – na tom nezáleží). Zásadně důležité je přeměnit je na „chytré stoly“ s automatickou velikostí, abyste na to v budoucnu nemysleli. To se provádí příkazem Formátovat jako tabulku Karta Domů (Domů — Formátovat jako tabulku). Na kartě, která se poté zobrazí Stavitel (Design) dejte tabulkám popisné názvy v poli Název tabulky pro pozdější použití:
Celkem bychom měli získat tři „chytré tabulky“:
Upozorňujeme, že tabulky mohou obsahovat další upřesňující údaje. Tedy například naše Cenaobsahuje další informace o kategorii (skupina produktů, balení, hmotnost atd.) každého produktu a tabulku Klient — město a region (adresa, DIČ, bankovní spojení atd.) každého z nich.
Tabulka Prodej použijeme později k zapsání dokončených transakcí do něj.
Krok 2. Vytvořte formulář pro zadávání dat
Údaje o prodeji můžete samozřejmě zadávat přímo do zelené tabulky Prodej, ale to není vždy vhodné a znamená to výskyt chyb a překlepů způsobených „lidským faktorem“. Proto by bylo lepší vytvořit speciální formulář pro zadávání dat na samostatném listu něčeho takového:
V buňce B3, chcete-li získat aktualizované aktuální datum a čas, použijte funkci TDATA (NYNÍ). Pokud není potřeba čas, pak místo toho TDATA funkci lze použít DNES (DNES).
V buňce B11 najděte cenu vybraného produktu ve třetím sloupci chytré tabulky Cena pomocí funkce VPR (VYHLEDAT). Pokud jste se s tím ještě nesetkali, tak si nejprve přečtěte a podívejte se na video zde.
V buňce B7 potřebujeme rozevírací seznam s produkty z ceníku. K tomu můžete použít příkaz Data – Validace dat (Ověření dat), zadejte jako omezení Seznam (Seznam) a poté zadejte do pole Zdroj (Zdroj) odkaz na sloupec Příjmení z našeho chytrého stolu Cena:
Podobně se vytvoří rozevírací seznam s klienty, ale zdroj bude užší:
=NEPŘÍMÉ(“Zákazníci[Klient]”)
funkce NEPŘÍMÝ (NEPŘÍMÝ) je v tomto případě potřeba, protože Excel bohužel nerozumí přímým odkazům na chytré tabulky v poli Zdroj. Ale stejný odkaz „zabalený“ do funkce NEPŘÍMÝ přitom to funguje s třeskem (více o tom bylo v článku o vytváření rozbalovacích seznamů s obsahem).
Krok 3. Přidání makra pro záznam prodeje
Po vyplnění formuláře je potřeba doplnit údaje do něj zadané na konec tabulky Prodej. Pomocí jednoduchých odkazů vytvoříme řádek, který se přidá přímo pod formulář:
Tito. buňka A20 bude mít odkaz na =B3, buňka B20 bude mít odkaz na =B7 atd.
Nyní přidáme 2řádkové základní makro, které zkopíruje vygenerovaný řetězec a přidá jej do tabulky Prodej. Chcete-li to provést, stiskněte kombinaci Alt + F11 nebo tlačítko Visual Basic Karta vývojka (Vývojář). Pokud tato karta není viditelná, povolte ji nejprve v nastavení Soubor – Možnosti – Nastavení pásu karet (Soubor — Možnosti — Přizpůsobit pás karet). V okně editoru jazyka Visual Basic, které se otevře, vložte pomocí nabídky nový prázdný modul Vložit – Modul a zadejte tam náš kód makra:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Zkopírujte datový řádek z formuláře n = Worksheets("Sales").Range("A100000").End(xlUp) . Řádek 'určuje číslo posledního řádku v tabulce. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'vložte do dalšího prázdného řádku Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'vymazat koncový dílčí formulář Nyní můžeme do formuláře přidat tlačítko pro spuštění vytvořeného makra pomocí rozevíracího seznamu Vložit Karta vývojka (Vývojář — Vložit — Tlačítko):
Poté, co jej nakreslíte, přidržením levého tlačítka myši se vás Excel zeptá, jaké makro mu musíte přiřadit – vyberte naše makro Add_Sell. Text na tlačítku můžete změnit tak, že na něj kliknete pravým tlačítkem a vyberete příkaz Změnit text.
Nyní, po vyplnění formuláře, stačí kliknout na naše tlačítko a zadané údaje se automaticky doplní do tabulky Prodeja poté se formulář vymaže pro zadání nové dohody.
Krok 4 Propojení tabulek
Než sestavíme sestavu, propojme naše tabulky dohromady, abychom později mohli rychle vypočítat tržby podle regionu, zákazníka nebo kategorie. Ve starších verzích Excelu by to vyžadovalo použití několika funkcí. VPR (VYHLEDAT) pro dosazení cen, kategorií, zákazníků, měst atd. do tabulky Prodej. To od nás vyžaduje čas a úsilí a také „sežere“ spoustu zdrojů Excelu. Počínaje Excelem 2013 lze vše implementovat mnohem jednodušeji nastavením vztahů mezi tabulkami.
Chcete-li to provést, na kartě Data (Datum) cvaknutí souvislost (Vztahy). V okně, které se zobrazí, klikněte na tlačítko Vytvořit (Nový) a vyberte z rozevíracích seznamů tabulky a názvy sloupců, podle kterých by měly souviset:
Důležitý bod: tabulky musí být specifikovány v tomto pořadí, tj. propojená tabulka (Cena) nesmí obsahovat ve sloupci klíče (Příjmení) duplicitní produkty, jak je tomu v tabulce Prodej. Jinými slovy, přidružená tabulka musí být taková, ve které byste hledali data pomocí VPRpokud by byla použita.
Stůl je samozřejmě zapojen podobným způsobem Prodej s tabulkou Klient podle společného sloupce Zákazník:
Po nastavení odkazů lze okno pro správu odkazů zavřít; tento postup nemusíte opakovat.
Krok 5. Pomocí souhrnu vytváříme sestavy
Nyní, abychom analyzovali prodeje a sledovali dynamiku procesu, vytvořte například nějaký druh sestavy pomocí kontingenční tabulky. Nastavte aktivní buňku na tabulku Prodej a vyberte kartu na pásu karet Vložit – kontingenční tabulka (Vložit — kontingenční tabulka). V okně, které se otevře, se nás Excel zeptá na zdroj dat (tj. tabulku Prodej) a místo pro nahrání přehledu (nejlépe na novém listu):
Důležité je, že je nutné zaškrtávací políčko povolit Přidejte tato data do datového modelu (Přidat data do datového modelu) v dolní části okna, aby Excel pochopil, že chceme sestavu sestavit nejen na aktuální tabulce, ale také využít všechny vztahy.
Po kliknutí na tlačítko OK v pravé polovině okna se objeví panel Pole kontingenční tabulkykde kliknout na odkaz Zobrazit vševidět nejen tu aktuální, ale všechny „chytré tabulky“, které jsou v knize najednou. A pak, stejně jako v klasické kontingenční tabulce, můžete jednoduše přetáhnout pole, která potřebujeme z jakýchkoli souvisejících tabulek do oblasti Filtr, Řádky, Stolbtsov or Hodnoty – a Excel okamžitě vytvoří na listu jakoukoli zprávu, kterou potřebujeme:
Nezapomeňte, že kontingenční tabulku je třeba pravidelně aktualizovat (při změně zdrojových dat) kliknutím pravým tlačítkem myši na ni a výběrem příkazu Aktualizovat a uložit (Obnovit), protože to nemůže udělat automaticky.
Také výběrem libovolné buňky v souhrnu a stisknutím tlačítka Kontingenční graf (Kontingenční graf) Karta Analýza (Analýza) or parametry (Možnosti) v něm vypočítané výsledky můžete rychle vizualizovat.
Krok 6. Vyplňte tiskopisy
Dalším typickým úkolem každé databáze je automatické vyplňování různých tiskových formulářů a formulářů (faktury, faktury, úkony atd.). O jednom ze způsobů, jak toho dosáhnout, jsem již psal. Zde implementujeme například vyplnění formuláře podle čísla účtu:
Předpokládá se, že do buňky C2 uživatel zadá číslo (číslo řádku v tabulce Prodej, ve skutečnosti), a pak jsou data, která potřebujeme, vytažena pomocí již známé funkce VPR (VYHLEDAT) a funkce INDEX (INDEX).
- Jak používat funkci VLOOKUP k vyhledání a vyhledání hodnot
- Jak nahradit VLOOKUP funkcemi INDEX a MATCH
- Automatické vyplňování formulářů a formulářů daty z tabulky
- Vytváření sestav pomocí kontingenčních tabulek