Innehåll
När man nämner databaser (DB) är det första som kommer att tänka på, naturligtvis, alla möjliga modeord som SQL, Oracle, 1C eller åtminstone Access. Naturligtvis är detta mycket kraftfulla (och för det mesta dyra) program som kan automatisera arbetet i ett stort och komplext företag med mycket data. Problemet är att ibland behövs sådan kraft helt enkelt inte. Ditt företag kan vara litet och med relativt enkla affärsprocesser, men du vill också automatisera det. Och det är för små företag som det ofta handlar om överlevnad.
Till att börja med, låt oss formulera TOR. I de flesta fall bör en databas för bokföring, till exempel klassisk försäljning, kunna:
- hålla i tabellerna information om varor (pris), genomförda transaktioner och kunder samt koppla dessa tabeller till varandra
- ha bekvämt inmatningsformulär data (med rullgardinslistor, etc.)
- automatiskt fylla i vissa uppgifter tryckta blanketter (betalningar, räkningar etc.)
- utfärda det nödvändiga rapporter att styra hela affärsprocessen från chefens synvinkel
Microsoft Excel kan hantera allt detta med lite ansträngning. Låt oss försöka implementera detta.
Steg 1. Inledande data i form av tabeller
Vi kommer att lagra information om produkter, försäljning och kunder i tre tabeller (på samma ark eller på olika – det spelar ingen roll). Det är fundamentalt viktigt att förvandla dem till "smarta tabeller" med automatisk storlek, för att inte tänka på det i framtiden. Detta görs med kommandot Formatera som en tabell fliken Hem (Hem — Formatera som tabell). På fliken som då dyker upp Konstruktör (Design) ge tabeller beskrivande namn i fältet Tabellnamn för senare användning:
Totalt borde vi få tre "smarta tabeller":
Observera att tabellerna kan innehålla ytterligare förtydligande data. Så till exempel vår Prisinnehåller ytterligare information om kategori (produktgrupp, förpackning, vikt etc.) för varje produkt och tabellen Klient — Stad och region (adress, TIN, bankuppgifter etc.) för var och en av dem.
Bord Försäljning kommer att användas av oss senare för att lägga in genomförda transaktioner i den.
Steg 2. Skapa ett datainmatningsformulär
Självklart kan du lägga in försäljningsdata direkt i den gröna tabellen Försäljning, men detta är inte alltid bekvämt och medför uppkomsten av fel och stavfel på grund av den "mänskliga faktorn". Därför skulle det vara bättre att skapa ett speciellt formulär för att mata in data på ett separat ark av något så här:
I cell B3, för att få uppdaterad aktuell datum-tid, använd funktionen TDATA (NU). Om tid inte behövs, då istället TDATA funktion kan tillämpas I DAG (I DAG).
I cell B11, hitta priset på den valda produkten i den tredje kolumnen i den smarta tabellen Pris med funktionen VPR (VISNING). Om du inte har stött på det tidigare, läs först och titta på videon här.
I cell B7 behöver vi en rullgardinslista med produkter från prislistan. För detta kan du använda kommandot Data – Datavalidering (Datavalidering), ange som en begränsning Lista (Lista) och skriv sedan i fältet Källa (Källa) länk till kolumn Namn från vårt smarta bord Pris:
På samma sätt skapas en rullgardinslista med klienter, men källan kommer att vara smalare:
=INDIREKT(”Kunder[Kund]”)
Funktion INDIREKT (INDIREKT) behövs, i det här fallet, eftersom Excel, tyvärr, inte förstår direktlänkar till smarta tabeller i fältet Källa. Men samma länk "inpackad" i en funktion INDIREKT samtidigt fungerar det med råge (mer om detta fanns i artikeln om att skapa rullgardinslistor med innehåll).
Steg 3. Lägga till ett försäljningsingångsmakro
Efter att ha fyllt i formuläret måste du lägga till de uppgifter som har angetts i det i slutet av tabellen Försäljning. Med enkla länkar kommer vi att skapa en rad som ska läggas till precis under formuläret:
De där. cell A20 kommer att ha en länk till =B3, cell B20 kommer att ha en länk till =B7, och så vidare.
Låt oss nu lägga till ett 2-radigt elementärt makro som kopierar den genererade strängen och lägger till den i försäljningstabellen. För att göra detta, tryck på kombinationen Alt + F11 eller knapp Visual Basic fliken utvecklare (Utvecklaren). Om den här fliken inte är synlig, aktivera den först i inställningarna Arkiv – Alternativ – Ribbon Setup (Arkiv — Alternativ — Anpassa menyfliksområdet). I Visual Basic-redigeringsfönstret som öppnas, infoga en ny tom modul genom menyn Infoga – Modul och skriv in vår makrokod där:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Kopiera 'Kopiera dataraden från formuläret n = Worksheets("Sales").Range("A100000").End(xlUp) . Rad 'bestäm numret på den sista raden i tabellen. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'klistra in i nästa tomma rad Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'clear end sub form Nu kan vi lägga till en knapp i vårt formulär för att köra det skapade makrot med hjälp av rullgardinsmenyn Insert fliken utvecklare (Utvecklare — Infoga — Knapp):
När du har ritat den, håller du ner vänster musknapp, frågar Excel vilket makro du behöver tilldela det – välj vårt makro Add_Sell. Du kan ändra texten på en knapp genom att högerklicka på den och välja kommandot Ändra text.
Nu, efter att ha fyllt i formuläret, kan du helt enkelt klicka på vår knapp, så läggs de angivna uppgifterna automatiskt till i tabellen Försäljning, och sedan rensas formuläret för att gå in i ett nytt avtal.
Steg 4 Länka tabeller
Innan vi bygger rapporten, låt oss länka samman våra tabeller så att vi senare snabbt kan beräkna försäljningen per region, kund eller kategori. I äldre versioner av Excel skulle detta kräva användning av flera funktioner. VPR (VISNING) för att ersätta priser, kategorier, kunder, städer etc. i tabellen Försäljning. Detta kräver tid och ansträngning av oss, och "äter" också en hel del Excel-resurser. Från och med Excel 2013 kan allt implementeras mycket enklare genom att sätta upp relationer mellan tabeller.
För att göra detta, på fliken Data (Datum) klick förhållande (Relationer). Klicka på knappen i fönstret som visas Skapa (ny) och välj från rullgardinslistorna de tabeller och kolumnnamn som de ska relateras till:
En viktig punkt: tabellerna måste anges i denna ordning, dvs länkad tabell (Pris) får inte innehålla i nyckelkolumnen (Namn ) dubbletter av produkter, som det händer i tabellen Försäljning. Med andra ord måste den associerade tabellen vara en i vilken du skulle söka efter data med hjälp av VPRom den användes.
Naturligtvis är bordet kopplat på ett liknande sätt Försäljning med bord Klient efter gemensam kolumn Kund:
Efter att ha ställt in länkarna kan fönstret för hantering av länkar stängas; du behöver inte upprepa denna procedur.
Steg 5. Vi bygger rapporter med hjälp av sammanfattningen
Nu, för att analysera försäljning och spåra dynamiken i processen, låt oss skapa, till exempel, någon form av rapport med hjälp av en pivottabell. Ställ in aktiv cell till tabell Försäljning och välj fliken på menyfliksområdet Infoga – Pivottabell (Infoga — pivottabell). I fönstret som öppnas kommer Excel att fråga oss om datakällan (dvs tabell Försäljning) och en plats för att ladda upp rapporten (helst på ett nytt ark):
Det viktiga är att det är nödvändigt att aktivera kryssrutan Lägg till dessa data till datamodellen (Lägg till data i datamodellen) längst ner i fönstret så att Excel förstår att vi vill bygga en rapport inte bara på den aktuella tabellen, utan även använda alla relationer.
Efter att ha klickat på OK en panel visas i den högra halvan av fönstret Pivottabellfältvar man klickar på länken Allaatt se inte bara den nuvarande, utan alla "smarta tabeller" som finns i boken på en gång. Och sedan, som i den klassiska pivottabellen, kan du helt enkelt dra de fält vi behöver från alla relaterade tabeller till området Filter, rader, Stolbtsov or Värden – och Excel kommer omedelbart att skapa alla rapporter vi behöver på arket:
Glöm inte att pivottabellen behöver uppdateras regelbundet (när källdata ändras) genom att högerklicka på den och välja kommandot Uppdatera & spara (Uppdatera), eftersom det inte kan göra det automatiskt.
Också genom att markera valfri cell i sammanfattningen och trycka på knappen Pivotdiagram (pivotdiagram) fliken Analys (Analys) or parametrar (Alternativ) du kan snabbt visualisera resultaten som beräknas i den.
Steg 6. Fyll i utskriftsmaterialen
En annan typisk uppgift för en databas är att automatiskt fylla i olika utskrivna formulär och formulär (fakturor, fakturor, handlingar, etc.). Jag har redan skrivit om ett av sätten att göra detta. Här implementerar vi till exempel att fylla i formuläret efter kontonummer:
Det antas att i cell C2 kommer användaren att ange ett nummer (radnummer i tabellen Försäljning, faktiskt), och sedan hämtas informationen vi behöver upp med den redan bekanta funktionen VPR (VISNING) och funktioner INDEX (INDEX).
- Hur man använder VLOOKUP-funktionen för att slå upp och slå upp värden
- Hur man ersätter VLOOKUP med funktionerna INDEX och MATCH
- Automatisk ifyllning av blanketter och blanketter med data från tabellen
- Skapa rapporter med pivottabeller