Inhoud
Bij het noemen van databases (DB) denk je natuurlijk als eerste aan allerlei modewoorden zoals SQL, Oracle, 1C of in ieder geval Access. Dit zijn natuurlijk zeer krachtige (en voor het grootste deel dure) programma's die het werk van een groot en complex bedrijf met veel gegevens kunnen automatiseren. Het probleem is dat zo'n vermogen soms gewoon niet nodig is. Uw bedrijf is misschien klein en met relatief eenvoudige bedrijfsprocessen, maar u wilt het ook automatiseren. En voor kleine bedrijven is dit vaak een kwestie van overleven.
Laten we om te beginnen de TOR formuleren. In de meeste gevallen moet een database voor boekhouding, bijvoorbeeld klassieke verkopen, in staat zijn om:
- houden in de tabellen informatie over goederen (prijs), afgeronde transacties en klanten en koppel deze tabellen aan elkaar
- comfortabel hebben invoerformulieren gegevens (met vervolgkeuzelijsten, enz.)
- automatisch enkele gegevens invullen gedrukte formulieren (betalingen, rekeningen, enz.)
- geef de nodige meldt om het hele bedrijfsproces te beheersen vanuit het oogpunt van de manager
Microsoft Excel kan dit allemaal met een beetje moeite aan. Laten we proberen dit te implementeren.
Stap 1. Initiële gegevens in de vorm van tabellen
We slaan informatie over producten, verkopen en klanten op in drie tabellen (op hetzelfde blad of op verschillende - het maakt niet uit). Het is van fundamenteel belang om er 'slimme tabellen' van te maken met automatisch formaat, zodat u er in de toekomst niet meer aan hoeft te denken. Dit doe je met het commando Opmaken als een tabel tab Home (Home - Opmaken als tabel). Op het tabblad dat dan verschijnt Constructeur (Ontwerp) geef tabellen beschrijvende namen in het veld Tafel naam voor later gebruik:
In totaal zouden we drie "slimme tabellen" moeten krijgen:
Houd er rekening mee dat de tabellen aanvullende verduidelijkende gegevens kunnen bevatten. Dus bijvoorbeeld onze Prijsbevat aanvullende informatie over de categorie (productgroep, verpakking, gewicht, etc.) van elk product en de tabel CLIËNT — stad en regio (adres, FIN, bankgegevens, enz.) van elk van hen.
tafel Verkoop zal later door ons worden gebruikt om voltooide transacties erin in te voeren.
Stap 2. Maak een gegevensinvoerformulier
Natuurlijk kunt u verkoopgegevens rechtstreeks in de groene tabel invoeren Verkoop, maar dit is niet altijd handig en leidt tot het verschijnen van fouten en typefouten vanwege de "menselijke factor". Daarom is het beter om een speciaal formulier te maken voor het invoeren van gegevens op een apart blad van zoiets als dit:
Gebruik in cel B3 de functie . om de bijgewerkte huidige datum-tijd te krijgen De TDATA (NU). Als er geen tijd nodig is, dan in plaats daarvan De TDATA functie kan worden toegepast VANDAAG (VANDAAG).
Zoek in cel B11 de prijs van het geselecteerde product in de derde kolom van de slimme tabel Prijs de functie gebruiken: VPR (VERT.ZOEKEN). Ben je het nog niet eerder tegengekomen, lees en bekijk dan eerst de video hier.
In cel B7 hebben we een vervolgkeuzelijst nodig met producten uit de prijslijst. Hiervoor kun je het commando Gegevens – Gegevensvalidatie (Gegevensvalidatie), specificeer als een beperking Lijst (Lijst) en voer vervolgens in het veld bron (Bron) link naar kolom Naam van onze slimme tafel Prijs:
Evenzo wordt een vervolgkeuzelijst met clients gemaakt, maar de bron is smaller:
=INDIRECT(“Klanten[Klant]”)
Functie INDIRECTE (INDIRECT) is in dit geval nodig, omdat Excel helaas geen directe links naar slimme tabellen in het veld Bron begrijpt. Maar dezelfde link "verpakt" in een functie INDIRECTE tegelijkertijd werkt het met een knal (meer hierover stond in het artikel over het maken van vervolgkeuzelijsten met inhoud).
Stap 3. Een macro voor verkoopinvoer toevoegen
Na het invullen van het formulier, moet u de ingevoerde gegevens aan het einde van de tabel toevoegen Verkoop. Met behulp van eenvoudige links vormen we een regel die direct onder het formulier moet worden toegevoegd:
Die. cel A20 heeft een link naar =B3, cel B20 heeft een link naar =B7, enzovoort.
Laten we nu een elementaire macro van 2 regels toevoegen die de gegenereerde string kopieert en toevoegt aan de tabel Sales. Druk hiervoor op de combinatie Alt + F11 of knop Visual Basic tab ontwikkelaar (Ontwikkelaar). Als dit tabblad niet zichtbaar is, schakel het dan eerst in de instellingen in Bestand – Opties – Lint instellen (Bestand — Opties — Lint aanpassen). Voeg in het geopende Visual Basic-editorvenster een nieuwe lege module in via het menu Invoegen – Module en voer daar onze macrocode in:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Kopieer de gegevensregel van het formulier n = Worksheets("Sales").Range("A100000").End(xlUp) . Rij 'bepaal het nummer van de laatste rij in de tabel. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'plakken in de volgende lege regel Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'wissen einde subformulier' Nu kunnen we een knop aan ons formulier toevoegen om de gemaakte macro uit te voeren met behulp van de vervolgkeuzelijst Invoegen tab ontwikkelaar (Ontwikkelaar — Invoegen — Knop):
Nadat je het hebt getekend, terwijl je de linkermuisknop ingedrukt houdt, zal Excel je vragen welke macro je eraan moet toewijzen - selecteer onze macro Toevoegen_Verkopen. U kunt de tekst op een knop wijzigen door er met de rechtermuisknop op te klikken en de opdracht te selecteren Tekst wijzigen.
Nu, na het invullen van het formulier, kunt u eenvoudig op onze knop klikken en de ingevoerde gegevens worden automatisch aan de tabel toegevoegd Verkoopen vervolgens wordt het formulier gewist om een nieuwe deal in te voeren.
Stap 4 Tabellen koppelen
Voordat we het rapport samenstellen, koppelen we eerst onze tabellen aan elkaar, zodat we later snel de verkoop per regio, klant of categorie kunnen berekenen. In oudere versies van Excel zou dit het gebruik van verschillende functies vereisen. VPR (VERT.ZOEKEN) voor het vervangen van prijzen, categorieën, klanten, steden, enz. aan de tabel Verkoop. Dit vraagt van ons tijd en moeite, en “vreet” ook veel Excel-bronnen. Vanaf Excel 2013 kan alles veel eenvoudiger worden geïmplementeerd door relaties tussen tabellen op te zetten.
Om dit te doen, op het tabblad Data (Datum) Klik Relaties (Relaties). Klik in het venster dat verschijnt op de knop creëren (nieuwe) en selecteer uit de vervolgkeuzelijsten de tabellen en kolomnamen waaraan ze gerelateerd moeten zijn:
Een belangrijk punt: de tabellen moeten in deze volgorde worden opgegeven, dwz gekoppelde tabel (Prijs) mag niet in de sleutelkolom (Naam) dubbele producten, zoals in de tabel gebeurt Verkoop. Met andere woorden, de bijbehorende tabel moet er een zijn waarin u naar gegevens zou zoeken met VPRals het zou worden gebruikt.
Natuurlijk is de tafel op dezelfde manier verbonden Verkoop met tafel CLIËNT op gemeenschappelijke kolom :
Na het instellen van de koppelingen kan het venster voor het beheren van koppelingen worden gesloten; u hoeft deze procedure niet te herhalen.
Stap 5. We bouwen rapporten op met behulp van de samenvatting
Laten we nu, om de verkoop te analyseren en de dynamiek van het proces te volgen, bijvoorbeeld een soort rapport maken met behulp van een draaitabel. Zet actieve cel op tabel Verkoop en selecteer het tabblad op het lint Invoegen – draaitabel (Invoegen — draaitabel). In het geopende venster zal Excel ons vragen naar de gegevensbron (dwz tabel Verkoop) en een plaats om het rapport te uploaden (bij voorkeur op een nieuw blad):
Het essentiële punt is dat het nodig is om het selectievakje in te schakelen Voeg deze gegevens toe aan het gegevensmodel (Voeg gegevens toe aan gegevensmodel) onderaan het venster zodat Excel begrijpt dat we niet alleen een rapport willen bouwen op de huidige tabel, maar ook alle relaties willen gebruiken.
Na het klikken op OK er verschijnt een paneel in de rechterhelft van het venster Draaitabelveldenwaar op de link te klikken? Allesom niet alleen de huidige te zien, maar alle "slimme tabellen" die in het boek staan in één keer. En dan, net als in de klassieke draaitabel, kunt u eenvoudig de velden die we nodig hebben van alle gerelateerde tabellen naar het gebied slepen FILTER, Rijen, Stolbtsov or Values - en Excel zal onmiddellijk elk rapport maken dat we nodig hebben op het blad:
Vergeet niet dat de draaitabel periodiek moet worden bijgewerkt (wanneer de brongegevens veranderen) door er met de rechtermuisknop op te klikken en de opdracht te selecteren Bijwerken en opslaan (Vernieuwen), omdat het dit niet automatisch kan doen.
Ook door een cel in de samenvatting te selecteren en op de knop te drukken Draaigrafiek (draaigrafiek) tab Analyse (Analyse) or parameters (Opties) u kunt de daarin berekende resultaten snel visualiseren.
Stap 6. Vul de printables in
Een andere typische taak van elke database is het automatisch invullen van verschillende gedrukte formulieren en formulieren (facturen, facturen, akten, enz.). Ik schreef al over een van de manieren om dit te doen. Hier implementeren we bijvoorbeeld het invullen van het formulier op rekeningnummer:
Er wordt aangenomen dat de gebruiker in cel C2 een nummer (rijnummer in de tabel) zal invoeren Verkoop, in feite), en dan worden de gegevens die we nodig hebben opgehaald met behulp van de al bekende functie VPR (VERT.ZOEKEN) en functies INDEX (INHOUDSOPGAVE).
- De functie VERT.ZOEKEN gebruiken om waarden op te zoeken en op te zoeken
- VLOOKUP vervangen door INDEX- en MATCH-functies
- Automatisch invullen van formulieren en formulieren met gegevens uit de tabel
- Rapporten maken met draaitabellen