Indhold
Når man nævner databaser (DB), er det første, der kommer til at tænke på, selvfølgelig alle mulige buzzwords som SQL, Oracle, 1C eller i det mindste Access. Det er selvfølgelig meget kraftfulde (og dyre for det meste) programmer, der kan automatisere arbejdet i en stor og kompleks virksomhed med en masse data. Problemet er, at nogle gange er sådan magt simpelthen ikke nødvendig. Din virksomhed kan være lille og med relativt simple forretningsprocesser, men du vil også gerne automatisere den. Og det er for små virksomheder, at det ofte er et spørgsmål om overlevelse.
Til at begynde med, lad os formulere TOR. I de fleste tilfælde bør en database til bogføring, for eksempel klassiske salg, kunne:
- holde i tabellerne oplysninger om varer (pris), gennemførte transaktioner og kunder og knytte disse tabeller til hinanden
- have det behageligt input formularer data (med rullelister osv.)
- automatisk udfylde nogle data trykte formularer (betalinger, regninger osv.)
- udstede det nødvendige rapporter at styre hele forretningsprocessen fra lederens synspunkt
Microsoft Excel kan håndtere alt dette med en lille indsats. Lad os prøve at implementere dette.
Trin 1. Indledende data i form af tabeller
Vi gemmer oplysninger om produkter, salg og kunder i tre tabeller (på samme ark eller på forskellige – det er lige meget). Det er grundlæggende vigtigt at omdanne dem til "smarte borde" med automatisk størrelse, for ikke at tænke på det i fremtiden. Dette gøres med kommandoen Formater som en tabel fanen Home (Hjem — Formater som tabel). På den fane, der så vises Constructor (Design) giv tabeller beskrivende navne i feltet Tabelnavn til senere brug:
I alt skulle vi få tre "smarte borde":
Bemærk venligst, at tabellerne kan indeholde yderligere præciserende data. Så for eksempel vores Prisindeholder yderligere oplysninger om kategorien (produktgruppe, emballage, vægt osv.) for hvert produkt og tabellen Klient — by og region (adresse, TIN, bankoplysninger osv.) for hver af dem.
Bordlampe Salg vil blive brugt af os senere til at indtaste gennemførte transaktioner i den.
Trin 2. Opret en dataindtastningsformular
Du kan selvfølgelig indtaste salgsdata direkte i den grønne tabel Salg, men dette er ikke altid praktisk og medfører udseendet af fejl og slåfejl på grund af den "menneskelige faktor". Derfor ville det være bedre at lave en speciel formular til indtastning af data på et separat ark af noget som dette:
I celle B3 skal du bruge funktionen for at få den opdaterede aktuelle dato-klokkeslæt TDATA (NU). Hvis der ikke er brug for tid, så i stedet TDATA funktion kan anvendes I DAG (I DAG).
I celle B11 skal du finde prisen på det valgte produkt i tredje kolonne i smart-tabellen Pris ved hjælp af funktionen VPR (OPLYSNING). Hvis du ikke er stødt på det før, så læs og se først videoen her.
I celle B7 har vi brug for en rulleliste med produkter fra prislisten. Til dette kan du bruge kommandoen Data – Datavalidering (Data validering), angiv som en begrænsning Liste (Liste) og indtast derefter i feltet Kilde (Kilde) link til kolonne Navn fra vores smarte bord Pris:
På samme måde oprettes en rulleliste med klienter, men kilden vil være smallere:
=INDIREKTE(“Kunder[Kunde]”)
Funktion INDIREKTE (INDIREKTE) er nødvendig, i dette tilfælde, fordi Excel, desværre, ikke forstår direkte links til smarte tabeller i feltet Kilde. Men det samme link "indpakket" i en funktion INDIREKTE samtidig fungerer det med et brag (mere om dette var i artiklen om oprettelse af drop-down lister med indhold).
Trin 3. Tilføjelse af en salgspostmakro
Efter at have udfyldt formularen, skal du tilføje de indtastede data til slutningen af tabellen Salg. Ved hjælp af simple links danner vi en linje, der skal tilføjes lige under formularen:
De der. celle A20 vil have et link til =B3, celle B20 vil have et link til =B7, og så videre.
Lad os nu tilføje en 2-linjers elementær makro, der kopierer den genererede streng og tilføjer den til salgstabellen. For at gøre dette skal du trykke på kombinationen Alt + F11 eller knap Visual Basic fanen developer (Udvikler). Hvis denne fane ikke er synlig, så aktiver den først i indstillingerne Fil – Indstillinger – Opsætning af bånd (Fil — Indstillinger — Tilpas bånd). Indsæt et nyt tomt modul gennem menuen i Visual Basic-editorvinduet, der åbnes Indsæt – Modul og indtast vores makrokode der:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Kopier 'Kopiér datalinjen fra formularen n = Worksheets("Salg").Range("A100000").End(xlUp) . Række 'bestem nummeret på den sidste række i tabellen. Salgsregneark("Salg").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'indsæt i den næste tomme linje Arbejdsark("Input Form").Range("B5,B7,B9"). ClearContents 'klar ende underform Nu kan vi tilføje en knap til vores formular for at køre den oprettede makro ved hjælp af rullelisten indsatte fanen developer (Udvikler — Indsæt — Knap):
Når du har tegnet det, og hold venstre museknap nede, spørger Excel dig, hvilken makro du skal tildele den – vælg vores makro Tilføj_Sælg. Du kan ændre teksten på en knap ved at højreklikke på den og vælge kommandoen Skift tekst.
Nu, efter at have udfyldt formularen, kan du blot klikke på vores knap, og de indtastede data vil automatisk blive tilføjet til tabellen Salg, og derefter ryddes formularen for at indgå en ny aftale.
Trin 4 Sammenkædning af tabeller
Inden vi bygger rapporten, lad os linke vores tabeller sammen, så vi senere hurtigt kan beregne salg efter region, kunde eller kategori. I ældre versioner af Excel ville dette kræve brug af flere funktioner. VPR (OPLYSNING) for at erstatte priser, kategorier, kunder, byer osv. til tabellen Salg. Dette kræver tid og kræfter fra os, og "spiser" også en masse Excel-ressourcer. Fra og med Excel 2013 kan alt implementeres meget mere enkelt ved at opsætte relationer mellem tabeller.
For at gøre dette, på fanen data (Dato) klik forbindelser (Relationer). Klik på knappen i det vindue, der vises Opret (ny) og vælg fra rullelisten de tabeller og kolonnenavne, som de skal relateres til:
Et vigtigt punkt: tabellerne skal angives i denne rækkefølge, dvs. linket tabel (Pris) må ikke indeholde i nøglekolonnen (Navn) duplikatprodukter, som det sker i tabellen Salg. Med andre ord skal den tilknyttede tabel være en, hvori du vil søge efter data vha VPRhvis det blev brugt.
Selvfølgelig er bordet forbundet på samme måde Salg med bord Klient efter fælles kolonne Kunden:
Efter opsætning af links kan vinduet til administration af links lukkes; du behøver ikke at gentage denne procedure.
Trin 5. Vi bygger rapporter ved hjælp af resuméet
Lad os nu, for at analysere salg og spore dynamikken i processen, oprette en form for rapport ved hjælp af en pivottabel. Indstil aktiv celle til tabel Salg og vælg fanen på båndet Indsæt – Pivottabel (Indsæt — pivottabel). I det vindue, der åbnes, vil Excel spørge os om datakilden (dvs. tabel Salg) og et sted at uploade rapporten (helst på et nyt ark):
Det afgørende er, at det er nødvendigt at aktivere afkrydsningsfeltet Tilføj disse data til datamodellen (Tilføj data til datamodel) nederst i vinduet, så Excel forstår, at vi ikke kun vil bygge en rapport på den aktuelle tabel, men også bruge alle relationer.
Efter at have klikket på OK et panel vises i højre halvdel af vinduet Pivottabelfelterhvor du skal klikke på linket Alleat se ikke kun den nuværende, men alle de "smarte tabeller", der er i bogen på én gang. Og så, som i den klassiske pivottabel, kan du blot trække de felter, vi skal bruge, fra alle relaterede tabeller ind i området filtre, Rækker, Stolbtsov or Værdier – og Excel vil øjeblikkeligt bygge enhver rapport, vi har brug for, på arket:
Glem ikke, at pivottabellen skal opdateres med jævne mellemrum (når kildedataene ændres) ved at højreklikke på den og vælge kommandoen Opdater & Gem (Opdater), fordi den ikke kan gøre det automatisk.
Også ved at vælge en hvilken som helst celle i oversigten og trykke på knappen Pivot-diagram (pivotdiagram) fanen Analyse (Analyse) or parametre (Muligheder) du kan hurtigt visualisere resultaterne beregnet i den.
Trin 6. Udfyld printables
En anden typisk opgave for enhver database er den automatiske udfyldning af forskellige udskrevne formularer og formularer (fakturaer, fakturaer, handlinger osv.). Jeg har allerede skrevet om en af måderne at gøre dette på. Her implementerer vi for eksempel udfyldelse af formularen efter kontonummer:
Det antages, at brugeren i celle C2 vil indtaste et tal (rækkenummer i tabellen Salg, faktisk), og så trækkes de data, vi har brug for, op ved hjælp af den allerede velkendte funktion VPR (OPLYSNING) og funktioner INDEX (INDEKS).
- Sådan bruger du VLOOKUP-funktionen til at slå op og slå værdier op
- Sådan erstatter du VLOOKUP med funktionerne INDEX og MATCH
- Automatisk udfyldning af formularer og formularer med data fra tabellen
- Oprettelse af rapporter med pivottabeller