tartalom
Az adatbázisok (DB) említésekor természetesen mindenféle hívószó jut eszünkbe, mint az SQL, Oracle, 1C vagy legalábbis az Access. Természetesen ezek nagyon erős (és többnyire költséges) programok, amelyek képesek automatizálni egy nagy és összetett, sok adatot tartalmazó cég munkáját. Az a baj, hogy néha egyszerűen nincs szükség ilyen teljesítményre. Lehet, hogy az Ön vállalkozása kicsi és viszonylag egyszerű üzleti folyamatokkal rendelkezik, de szeretné automatizálni is. És ez a kis cégeknél gyakran túlélés kérdése.
Kezdésként fogalmazzuk meg a TOR-t. A legtöbb esetben egy könyvelési adatbázisnak, például a klasszikus értékesítéseknek képesnek kell lennie:
- tartani a táblázatokban az árukra (árra), a teljesített tranzakciókra és a vevőkre vonatkozó információkat, és összekapcsolja ezeket a táblázatokat egymással
- kényelmes legyen beviteli űrlapok adatok (legördülő listákkal stb.)
- automatikusan kitölteni bizonyos adatokat nyomtatott űrlapok (kifizetések, számlák stb.)
- adja ki a szükséges jelentések hogy a teljes üzleti folyamatot a vezető szemszögéből irányítsuk
A Microsoft Excel mindezt egy kis erőfeszítéssel meg tudja kezelni. Próbáljuk meg ezt megvalósítani.
1. lépés: Kiindulási adatok táblázatok formájában
A termékekről, eladásokról és vásárlókról három táblázatban tároljuk az információkat (ugyanazon a lapon vagy különböző – mindegy). Alapvetően fontos, hogy ezeket „intelligens asztalokká” alakítsuk automatikus méretezéssel, hogy a jövőben ne gondoljunk rá. Ez a paranccsal történik Formázás táblázatként lap Kezdőlap (Főoldal — Táblázat formázása). Az ezután megjelenő lapon építész (Tervezés) adjon a táblázatoknak leíró neveket a mezőben Tábla neve későbbi használatra:
Összesen három „okos asztalt” kell kapnunk:
Felhívjuk figyelmét, hogy a táblázatok további pontosító adatokat tartalmazhatnak. Tehát például a miénk Ártovábbi információkat tartalmaz az egyes termékek kategóriájáról (termékcsoport, csomagolás, tömeg stb.), valamint a táblázat Vásárló — mindegyik városa és régiója (cím, TIN, banki adatok stb.).
Táblázat Értékesítés később felhasználjuk majd a befejezett tranzakciók bevitelére.
2. lépés: Hozzon létre egy adatbeviteli űrlapot
Az értékesítési adatokat természetesen közvetlenül a zöld táblázatba is megadhatja Értékesítés, de ez nem mindig kényelmes, és az „emberi tényező” miatti hibák és elírások megjelenésével jár. Ezért jobb lenne egy speciális űrlapot készíteni az adatok beviteléhez egy ilyen külön lapon:
A B3 cellában a frissített aktuális dátum-idő lekéréséhez használja a függvényt A TDATA (MOST). Ha nincs szükség időre, akkor inkább A TDATA funkció alkalmazható MA (MA).
A B11 cellában keresse meg az intelligens táblázat harmadik oszlopában a kiválasztott termék árát Ár funkció használatával VPR (VLOOKUP). Ha még nem találkozott vele, akkor először olvassa el és nézze meg a videót itt.
A B7 cellában szükségünk van egy legördülő listára az árlistából származó termékekkel. Ehhez használhatja a parancsot Adatok – Adatérvényesítés (Adatok – Érvényesítés), adja meg megkötésként Lista (Lista) majd írja be a mezőbe forrás (Forrás) link az oszlophoz Név okos asztalunkról Ár:
Hasonlóképpen létrejön egy legördülő lista az ügyfelekkel, de a forrás szűkebb lesz:
=KÖZVETETT("Ügyfelek[Ügyfél]"
Funkció KÖZVETETT (KÖZVETETT) Ebben az esetben azért van szükség, mert az Excel sajnos nem érti az intelligens táblákra mutató közvetlen hivatkozásokat a Forrás mezőben. De ugyanaz a hivatkozás egy függvénybe „csomagolva”. KÖZVETETT ugyanakkor lendületesen működik (erről bővebben a tartalommal rendelkező legördülő listák létrehozásáról szóló cikkben volt szó).
3. lépés: Értékesítési bejegyzés makró hozzáadása
Az űrlap kitöltése után a táblázat végére fel kell venni az abban megadott adatokat Értékesítés. Egyszerű hivatkozások segítségével létrehozunk egy sort, amelyet közvetlenül az űrlap alá kell hozzáadni:
Azok. Az A20 cella a =B3-hoz, a B20-as cellához a =B7-hez fog kapcsolódni, és így tovább.
Most adjunk hozzá egy 2 soros elemi makrót, amely lemásolja a generált karakterláncot, és hozzáadja az értékesítési táblához. Ehhez nyomja meg a kombinációt Alt + F11 vagy gombot Visual Basic lap fejlesztő (Fejlesztő). Ha ez a lap nem látható, először engedélyezze a beállításokban Fájl – Beállítások – Szalagbeállítás (Fájl — Beállítások — Szalag testreszabása). A megnyíló Visual Basic szerkesztő ablakban a menün keresztül szúrjon be egy új üres modult Beszúrás – Modul és oda írjuk be a makrókódunkat:
Sub Add_Sell() Worksheets("Input Form").Tartomány("A20:E20").Copy 'Az adatsor másolása az űrlapról n = Worksheets("Sales").Tartomány("A100000").End(xlUp) . Sor 'határozza meg a táblázat utolsó sorának számát. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues\'illessze be a következő üres sorba Worksheets("Input Form").Tartomány("B5,B7,B9"). ClearContents 'clear end alűrlap Most hozzáadhatunk egy gombot az űrlapunkhoz a létrehozott makró futtatásához a legördülő lista segítségével betétlap lap fejlesztő (Fejlesztő – Beszúrás – Gomb):
A rajzolás után az egér bal gombjának nyomva tartásával az Excel megkérdezi, hogy melyik makrót kell hozzá rendelni – válassza ki a makrónkat Add_Sell. A gomb szövegét úgy módosíthatja, ha rákattint a jobb gombbal, és kiválasztja a parancsot Szöveg módosítása.
Most, az űrlap kitöltése után, egyszerűen kattintson a gombunkra, és a megadott adatok automatikusan bekerülnek a táblázatba Értékesítés, majd az űrlap törlődik az új ügylet megadásához.
4. lépés Táblázatok összekapcsolása
A riport összeállítása előtt kapcsoljuk össze táblázatainkat, hogy később gyorsan ki tudjuk számolni az eladásokat régiónként, vásárlónként vagy kategóriánként. Az Excel régebbi verzióiban ehhez több függvény használatára lenne szükség. VPR (VLOOKUP) árak, kategóriák, ügyfelek, városok stb. helyettesítésére a táblázatban Értékesítés. Ez időt és erőfeszítést igényel tőlünk, emellett rengeteg Excel-erőforrást is „megeszik”. Az Excel 2013-tól kezdve a táblák közötti kapcsolatok beállításával minden sokkal egyszerűbben megvalósítható.
Ehhez a lapon dátum (Dátum) kettyenés kapcsolat (Kapcsolatok). A megjelenő ablakban kattintson a gombra Teremt (új) és a legördülő listákból válassza ki azokat a táblázatokat és oszlopneveket, amelyekhez kapcsolni kell őket:
Egy fontos szempont: a táblákat ebben a sorrendben kell megadni, azaz linkelt tábla (Ár) nem tartalmazhat a kulcs oszlopban (Név) duplikált termékeket, ahogy az a táblázatban is történik Értékesítés. Más szavakkal, a társított táblának olyannak kell lennie, amelyben adatokat keres VPRha használnák.
Természetesen az asztal is hasonló módon kapcsolódik Értékesítés asztallal Vásárló közös oszlop szerint Vevő:
A hivatkozások beállítása után a hivatkozások kezelésére szolgáló ablak bezárható; ezt az eljárást nem kell megismételnie.
5. lépés: Az összefoglaló segítségével jelentéseket készítünk
Most az értékesítés elemzéséhez és a folyamat dinamikájának nyomon követéséhez hozzunk létre például valamilyen jelentést egy pivot tábla segítségével. Állítsa be az aktív cellát táblázatba Értékesítés és válassza ki a fület a szalagon Beszúrás – PivotTable (Beszúrás – Pivot Table). A megnyíló ablakban az Excel rákérdez az adatforrásra (pl. táblázat Értékesítés) és a jelentés feltöltésének helye (lehetőleg új lapon):
A lényeg az, hogy engedélyezni kell a jelölőnégyzetet Adja hozzá ezeket az adatokat az adatmodellhez (Adatok hozzáadása az adatmodellhez) az ablak alján, hogy az Excel megértse, hogy nem csak az aktuális tábláról szeretnénk jelentést készíteni, hanem az összes kapcsolatot is használni szeretnénk.
Miután rákattintott OK egy panel jelenik meg az ablak jobb felében Pivot tábla mezőihol kell kattintani a linkre Minden termékhogy ne csak az aktuálisat lássuk, hanem az összes „okos táblát”, ami a könyvben van, egyszerre. Ezután, mint a klasszikus pivot táblában, egyszerűen áthúzhatja a szükséges mezőket bármely kapcsolódó táblából a területre. Szűrő, Sorok, Sztolbcov or Értékek – és az Excel azonnal elkészíti a lapon a szükséges jelentéseket:
Ne felejtse el, hogy a pivot táblát időnként frissíteni kell (ha a forrásadatok megváltoznak) a jobb gombbal kattintva és kiválasztva a parancsot. Frissítés és mentés (Frissítés), mert ezt nem tudja automatikusan megtenni.
Valamint az összefoglaló bármelyik cellájának kijelölésével és a gomb megnyomásával Pivot diagram (Pivot diagram) lap Elemzés (Elemzés) or paraméterek (Lehetőségek) gyorsan megjelenítheti a benne számított eredményeket.
6. lépés: Töltse ki a nyomtatható adatokat
Bármely adatbázis másik jellemző feladata a különféle nyomtatott nyomtatványok, nyomtatványok (számlák, számlák, okiratok stb.) automatikus kitöltése. Ennek egyik módjáról már írtam. Itt megvalósítjuk például az űrlap számlaszám szerinti kitöltését:
Feltételezzük, hogy a C2 cellában a felhasználó beír egy számot (a táblázat sorszámát Értékesítés, sőt), majd a már ismert függvény segítségével előhúzzuk a szükséges adatokat VPR (VLOOKUP) és funkciók INDEX (INDEX).
- Hogyan kell használni a VLOOKUP függvényt értékek megkeresésére és kikeresésére
- A VLOOKUP helyettesítése INDEX és MATCH függvényekkel
- Űrlapok és űrlapok automatikus kitöltése a táblázatból származó adatokkal
- Jelentések készítése kimutatások segítségével