Innhold
Når du nevner databaser (DB), er det første du tenker på, selvfølgelig alle slags buzzwords som SQL, Oracle, 1C eller i det minste Access. Dette er selvfølgelig veldig kraftige (og dyre for det meste) programmer som kan automatisere arbeidet til et stort og komplekst selskap med mye data. Problemet er at noen ganger er slik kraft rett og slett ikke nødvendig. Din bedrift kan være liten og med relativt enkle forretningsprosesser, men du ønsker også å automatisere den. Og det er for små bedrifter dette ofte handler om å overleve.
Til å begynne med, la oss formulere TOR. I de fleste tilfeller bør en database for regnskap, for eksempel klassisk salg, kunne:
- держать i tabellene informasjon om varer (pris), gjennomførte transaksjoner og kunder og knytte disse tabellene til hverandre
- ha det behagelig inndataskjemaer data (med nedtrekkslister osv.)
- automatisk fylle ut noen data trykte skjemaer (betalinger, regninger osv.)
- utstede det nødvendige rapporter å kontrollere hele forretningsprosessen fra lederens synspunkt
Microsoft Excel kan håndtere alt dette med litt innsats. La oss prøve å implementere dette.
Trinn 1. Startdata i form av tabeller
Vi vil lagre informasjon om produkter, salg og kunder i tre tabeller (på samme ark eller på forskjellige – det spiller ingen rolle). Det er grunnleggende viktig å gjøre dem om til "smarte tabeller" med automatisk størrelse, for ikke å tenke på det i fremtiden. Dette gjøres med kommandoen Formater som en tabell tab Hjemprodukt (Hjem — Formater som tabell). På fanen som da vises Constructor (Design) gi tabeller beskrivende navn i feltet Tabellnavn for senere bruk:
Totalt bør vi få tre "smarte tabeller":
Vær oppmerksom på at tabellene kan inneholde ytterligere avklaringsdata. Så for eksempel vår Prisinneholder tilleggsinformasjon om kategorien (produktgruppe, emballasje, vekt osv.) for hvert produkt, og tabellen kunde — by og region (adresse, TIN, bankinformasjon osv.) for hver av dem.
Bord Salg vil bli brukt av oss senere for å legge inn fullførte transaksjoner i den.
Trinn 2. Lag et dataregistreringsskjema
Du kan selvfølgelig legge inn salgsdata direkte i den grønne tabellen Salg, men dette er ikke alltid praktisk og medfører utseende av feil og skrivefeil på grunn av den "menneskelige faktoren". Derfor ville det være bedre å lage et spesielt skjema for å legge inn data på et eget ark av noe som dette:
I celle B3, for å få oppdatert gjeldende dato-klokkeslett, bruk funksjonen TDATA (NÅ). Hvis det ikke trengs tid, så i stedet TDATA funksjon kan brukes I DAG (I DAG).
I celle B11 finner du prisen på det valgte produktet i den tredje kolonnen i smarttabellen Pris ved hjelp av funksjonen VPR (VISNING). Hvis du ikke har vært borti det før, så les først og se videoen her.
I celle B7 trenger vi en nedtrekksliste med produkter fra prislisten. For dette kan du bruke kommandoen Data – Datavalidering (Datavalidering), spesifiser som en begrensning Liste (Liste) og skriv deretter inn i feltet kilde (Kilde) link til kolonne Navn fra vårt smarte bord Pris:
På samme måte opprettes en rullegardinliste med klienter, men kilden vil være smalere:
=INDIREKTE(“Kunder[Kunde]”)
Funksjon INDIREKTE (INDIREKTE) er nødvendig, i dette tilfellet, fordi Excel, dessverre, ikke forstår direkte lenker til smarttabeller i kildefeltet. Men den samme lenken "pakket inn" i en funksjon INDIREKTE samtidig fungerer det med et smell (mer om dette sto i artikkelen om å lage nedtrekkslister med innhold).
Trinn 3. Legge til en salgsoppføringsmakro
Etter å ha fylt ut skjemaet, må du legge til dataene som er lagt inn i det til slutten av tabellen Salg. Ved å bruke enkle lenker, danner vi en linje som skal legges til rett under skjemaet:
De. celle A20 vil ha en kobling til =B3, celle B20 vil ha en kobling til =B7, og så videre.
La oss nå legge til en 2-linjers elementær makro som kopierer den genererte strengen og legger den til salgstabellen. For å gjøre dette, trykk på kombinasjonen Alt + F11 eller knappen Visual Basic tab utvikler (Utvikler). Hvis denne fanen ikke er synlig, aktiver den først i innstillingene Fil – Alternativer – Båndoppsett (Fil — Alternativer — Tilpass bånd). I Visual Basic-redigeringsvinduet som åpnes, sett inn en ny tom modul gjennom menyen Sett inn – modul og skriv inn makrokoden vår der:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Kopier 'Kopier datalinjen fra skjemaet n = Worksheets("Salg").Range("A100000").End(xlUp) . Rad 'bestem nummeret på den siste raden i tabellen. Salgsregneark("Salg").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'lim inn i neste tomme linje Regneark("Input Form").Range("B5,B7,B9"). ClearContents 'clear end sub-skjema Nå kan vi legge til en knapp i skjemaet vårt for å kjøre den opprettede makroen ved å bruke rullegardinlisten innfelt tab utvikler (Utvikler – Sett inn – knapp):
Etter at du har tegnet den, holder du nede venstre museknapp, vil Excel spørre deg hvilken makro du må tilordne til den – velg vår makro Add_Sell. Du kan endre teksten på en knapp ved å høyreklikke på den og velge kommandoen Endre tekst.
Nå, etter å ha fylt ut skjemaet, kan du ganske enkelt klikke på knappen vår, og de angitte dataene vil automatisk bli lagt til tabellen Salg, og deretter tømmes skjemaet for å angi en ny avtale.
Trinn 4 Koble sammen tabeller
Før vi bygger rapporten, la oss koble sammen tabellene våre slik at vi senere raskt kan beregne salg etter region, kunde eller kategori. I eldre versjoner av Excel vil dette kreve bruk av flere funksjoner. VPR (VISNING) for å erstatte priser, kategorier, kunder, byer osv. til tabellen Salg. Dette krever tid og krefter fra oss, og «spiser» også mye Excel-ressurser. Fra og med Excel 2013 kan alt implementeres mye enklere ved å sette opp relasjoner mellom tabeller.
For å gjøre dette, på fanen Data (Dato) klikk relasjoner (Relasjoner). Klikk på knappen i vinduet som vises Opprett (ny) og velg fra rullegardinlistene tabellene og kolonnenavnene som de skal relateres til:
Et viktig poeng: tabellene må spesifiseres i denne rekkefølgen, dvs. koblet tabell (Pris) må ikke inneholde i nøkkelkolonnen (Navn) dupliserte produkter, slik det skjer i tabellen Salg. Den tilknyttede tabellen må med andre ord være en som du vil søke etter data ved hjelp av VPRhvis den ble brukt.
Selvfølgelig er bordet koblet sammen på en lignende måte Salg med bord kunde etter felles kolonne Kunde-:
Etter å ha satt opp lenkene, kan vinduet for administrasjon av lenker lukkes; du trenger ikke å gjenta denne prosedyren.
Trinn 5. Vi bygger rapporter ved å bruke sammendraget
Nå, for å analysere salg og spore dynamikken i prosessen, la oss lage for eksempel en slags rapport ved hjelp av en pivottabell. Sett aktiv celle til tabell Salg og velg fanen på båndet Sett inn – Pivottabell (Sett inn – pivottabell). I vinduet som åpnes vil Excel spørre oss om datakilden (dvs. tabellen Salg) og et sted å laste opp rapporten (helst på et nytt ark):
Det avgjørende poenget er at det er nødvendig å aktivere avmerkingsboksen Legg til disse dataene i datamodellen (Legg til data i datamodellen) nederst i vinduet slik at Excel forstår at vi ønsker å bygge en rapport ikke bare på gjeldende tabell, men også bruke alle relasjoner.
Etter å ha klikket på OK et panel vil vises i høyre halvdel av vinduet Pivottabellfelthvor du skal klikke på lenken Alleå se ikke bare den nåværende, men alle "smarttabellene" som er i boken samtidig. Og så, som i den klassiske pivottabellen, kan du ganske enkelt dra feltene vi trenger fra eventuelle relaterte tabeller inn i området filtre, Rader, Stolbtsov or Verdier – og Excel vil umiddelbart bygge enhver rapport vi trenger på arket:
Ikke glem at pivottabellen må oppdateres med jevne mellomrom (når kildedataene endres) ved å høyreklikke på den og velge kommandoen Oppdater og lagre (Forfriske), fordi den ikke kan gjøre det automatisk.
Også ved å velge hvilken som helst celle i sammendraget og trykke på knappen Pivotdiagram (Pivotdiagram) tab Analyse (Analyse) or parametere (Alternativer) du kan raskt visualisere resultatene beregnet i den.
Trinn 6. Fyll ut utskriftene
En annen typisk oppgave for enhver database er automatisk utfylling av forskjellige trykte skjemaer og skjemaer (fakturaer, fakturaer, handlinger, etc.). Jeg har allerede skrevet om en av måtene å gjøre dette på. Her implementerer vi for eksempel å fylle ut skjemaet etter kontonummer:
Det antas at i celle C2 vil brukeren legge inn et tall (radnummer i tabellen Salg, faktisk), og deretter hentes dataene vi trenger opp ved å bruke den allerede kjente funksjonen VPR (VISNING) og funksjoner INDEX (INDEKS).
- Hvordan bruke VLOOKUP-funksjonen til å slå opp og slå opp verdier
- Hvordan erstatte VLOOKUP med INDEX- og MATCH-funksjoner
- Automatisk utfylling av skjemaer og skjemaer med data fra tabellen
- Opprette rapporter med pivottabeller